I recently had to map out the MS SQL Server table fields of the inTouch Point-of-sale (POS) application ‘Profile Setup’ line-by-line – for a Qlik report. To save the next developer the headache, I’ve open-sourced* the full schema mapping guide.

The following is a mapping of the Profile Access options on the tabs, ‘Admin’ and ‘POS’, to the fields of table PROFILE_ACCESS:

Admin > Capture Float : CAPTURE_FLOAT
Admin > Cheque for Cash : CHQ_FOR_CASH
Admin > Drawer Pickup : DRAWER_PICKUP
Admin > Global Enquiry : VOUCHER_DETAILS
Admin > Globals Setup : GLOBALS_SETUP
Admin > Laybye Details Setup : LAYBYE_DETAILS
Admin > Operator Setup : OPERATOR_SETUP
Admin > Paid Outs : PAID_OUTS
Admin > Profile Setup : PROFILE_SETUP
Admin > Reports Configuration : REPORTS_CONFIG
Admin > Reprint Any : REPRINT_ANY
Admin > Salesman Cashup : SALESMAN_CASHUP
Admin > Salesman Setup : SALESMAN_SETUP
Admin > Shift End : SHIFT_END
Admin > Slip Details Setup : SLIP_DETAILS
Admin > Voucher for Cash : VOUCHER_FOR_CASH
Admin > Workstation Setup : WORKSTATION_SETUP
Admin > X – Reading : X_READING
Admin > Z – Reading : Z_READING
POS > Account Payment : ACCOUNT_PAYMENT
POS > Account Sales : ACCOUNT
POS > Admin : ADMIN
POS > Cash Sales : CASH_SALE
POS > Cashup variance : CASHUP_VARIANCE
POS > Change Floors : CHANGE_FLOORS
POS > COD Sales : COD_SALE
POS > Cost Price on Search : SEARCH_CP
POS > Credit Autorization : CREDIT_AUTHORIZATION
POS > Custom Orders : WORK_ORDER_CERTIFICATE
POS > Delete COD : COD_DELETE
POS > Delete Laybye : LAYBYE_DELETE
POS > Delete Quote : QUOTE_DELETE
POS > Discount Floors : DISCOUNT_FLOORS
POS > Edit COD : COD_EDIT
POS > Edit Laybye : LAYBYE_EDIT
POS > Edit Quote : QUOTE_EDIT
POS > Error Correct : CORRECT
POS > Global Discount : GLOBAL_DISCOUNT
POS > GP Line Enquiry : GP_ENQUIRY
POS > Green Fees : WORK_ORDER_DELETE
POS > Hold Sale : HOLD_SALE
POS > Invoice Quote : QUOTE_INVOICE
POS > Laybye Sales : LAYBYE_SALE
POS > Line Item Discount : LINE_ITEM_DISCOUNT
POS > Negative Quantity : NEG_QTY
POS > New Account Sale : ACCOUNT_SALE
POS > New COD : COD_NEW
POS > New Laybye : LAYBYE_NEW
POS > New Quote : QUOTE_NEW
POS > No Print : NO_PRINT
POS > No Sale Drawer Open : NO_SALE_CASH_DRAWER_OPEN
POS > Open Refunds : REFUND_OPEN
POS > Price Override : PRICE_OVERRIDE
POS > Print Quote : WORK_ORDER
POS > Quotes : QUOTE
POS > Recall Sale : RECALL_SALE
POS > Refunds : REFUND
POS > Repairs : WORK_ORDER_HOURS
POS > Reprint Last : REPRINT_LAST
POS > Settlement Discount : SETTLEMENT_DISCOUNT
POS > Stock Search : STOCK_SEARCH
POS > Tender COD : COD_TENDER
POS > Tender Floors : TENDER_FLOORS
POS > Tender Laybye : LAYBYE_TENDER
POS > Trade-Ins : TRADE_IN
POS > Transfer Sale : WORK_ORDER_INVOICE
POS > Void Line : VOIDLINE
POS > Void Sale : VOID_SALE
POS > Vouchers : VOUCHER
POS > Zero SOH Sales : ZERO_SOH_SALES

Herewith the Menu Access options mapped to the fields of table MENU_ACCESS:

Customer Transactions > Auto Billing > Auto Billing : CUSTOMERS_BILLING
Customer Transactions > Auto Billing > Invoicing – Reversals : CUST_AB_REVERSE
Customer Transactions > Automatic Payments > Automatic Payments : ACC_TRANS_AUTO_PAYMENT
Customer Transactions > Automatic Payments > Legacy Payments : ACC_TRANS_AP_LEGACY
Customer Transactions > Automatic Payments > Payments (New) : ACC_TRANS_AP
Customer Transactions > Custom Orders > Capture : CUSTOM_ORDERS_CAPTURE
Customer Transactions > Custom Orders > Custom Orders : CUSTOM_ORDERS
Customer Transactions > Custom Orders > Reports : CUSTOM_ORDERS_REPORTS
Customer Transactions > Delivery Notes > Capture : CUST_DELIVERY_NOTES_CAPTURE
Customer Transactions > Delivery Notes > Delivery Notes : CUST_DELIVERY_NOTES
Customer Transactions > Delivery Notes > Edit : CUST_DELIVERY_NOTES_EDIT
Customer Transactions > Delivery Notes > Reprint : CUST_DELIVERY_NOTES_REPRINT
Customer Transactions > Invoices > Invoices : ACC_TRANS_INVOICES
Customer Transactions > Invoices > Orders Only : INVOICING_ORDERS_ONLY
Customer Transactions > Merge Quotes / Online Order Allocation > Merge Quotes / Online Order Allocation : MERGE_QUOTES
Customer Transactions > Merge Quotes / Online Order Allocation > Store Allocation Functionality : CUST_ONLINE_ALLOCATION
Customer Transactions > Repairs > Capture : REPAIRS_CAPTURE
Customer Transactions > Repairs > Repairs : REPAIRS
Customer Transactions > Repairs > Reports : REPAIRS_REPORTS
Customer Transactions > Transactions > Adjust Subsidy : CUST_ADJUST_LOYALTY
Customer Transactions > Transactions > Batch Payments : ACC_TRANS_BATCH_PAYMENT
Customer Transactions > Transactions > Blanket Charges : CUSTOMERS_BLANKET
Customer Transactions > Transactions > Courier Shipments : SHIPPING
Customer Transactions > Transactions > Discount Item Payments : ACC_TRANS_DISC_PAYMENT
Customer Transactions > Transactions > Invoice Collections : CUST_INVOICE_COLLECTION
Customer Transactions > Transactions > Journals : CUSTOMERS_JOURNALS
Customer Transactions > Transactions > Load Auto On Hold : CUST_AUTO_HOLD
Customer Transactions > Transactions > Open Item Debtors : CUSTOMERS_OIDEBTORS
Customer Transactions > Transactions > Overtime Capturing : OVERTIME_WORKED
Customer Transactions > Transactions > POS : CUSTOMERS_POS
Customer Transactions > Transactions > Renew Contracts /Upgrade Contracts : ACC_TRANS_RENEW_CONTRACTS
Customer Transactions > Transactions > Requests : CUSTOMERS_REQUESTS
Customer Transactions > Transactions > Split Payments : ACC_TRANS_SPLIT_PAYMENT
Customer Transactions > Transactions > Transactions : CUSTOMERS_TRANS
Customer Transactions > Transactions > VAT Inclusive Payments : ACC_RANS_VAT_PAYMENT
Customer Transactions > Transactions > Vouchers : CUSTOMERS_VOUCHERS
Customer Transactions > Transactions > Workshop Bookings : WORKORDERS_SCHEDULE
Customers > Account Settings > Account Settings : CUST_MAINT_SETTINGS
Customers > Account Settings > Disable Bill Master : CUST_MAINT_BILL_MASTER
Customers > Account Settings > Enable Status Change on Lockdown : CUST_MAINT_LOCKDOWN_STATUS_CHANGE
Customers > Bank Details > Bank Details : CUST_MAINT_BANK_DETAILS
Customers > Bank Details > Disable Masking : CUST_BANK_NOMASK
Customers > Customer Profiling > Customer Profiling : CRM_ACCOUNTS
Customers > Customer Profiling > Delete : CRM_ACCOUNTS_DELETE
Customers > Customer Profiling > Export : CRM_ACCOUNTS_EXPORT
Customers > Customer Profiling > Insert : CRM_ACCOUNTS_INSERT
Customers > Customer Profiling > Mainteance : CRM_ACCOUNTS_MAINT
Customers > Customers Menu > Account Delete : ACCOUNT_DELETE
Customers > Customers Menu > Accounts (New) : CUSTOMERS_ACCOUNTS
Customers > Customers Menu > Accounts Contracts : ACCOUNT_CONTRACTS
Customers > Customers Menu > Allow Card No Update : CUST_ENQ_CARD_UPDATE
Customers > Customers Menu > Allow Maintenance Wizard : CUST_ENQ_MAINT
Customers > Customers Menu > Auto Billing : ACCOUNT_AUTOBILLING
Customers > Customers Menu > Card Access Points : CUST_MAINT_CARD_ACCESS
Customers > Customers Menu > Create Multiple : ACCOUNT_MULTIPLE
Customers > Customers Menu > Customer Notes : CUST_ENQ_NOTES
Customers > Customers Menu > Customers Menu : CUSTOMERS
Customers > Customers Menu > Delivery Address : CUST_MAINT_D_ADDRESS
Customers > Customers Menu > Documents : CUST_MAINT_DOCUMENTS
Customers > Customers Menu > Enquiry : CUSTOMERS_ENQUIRY
Customers > Customers Menu > Frequency Enquiry : CUST_ENQ_FREQUENCY
Customers > Customers Menu > Groups : CUST_SMAN_SETUP
Customers > Customers Menu > History : CUST_ENQ_HISTORY
Customers > Customers Menu > Interest : CUST_MAINT_INTEREST
Customers > Customers Menu > Membership : CUST_MAINT_MEMBERSHIP
Customers > Customers Menu > New Account : ACCOUNT_NEW
Customers > Customers Menu > Other : CUST_MAINT_OTHER
Customers > Customers Menu > Other Functions : CUST_MAINT_OTHER_FUNCTIONS
Customers > Customers Menu > POS Setup : CUST_MAINT_POS
Customers > Customers Menu > Postal Address : CUST_MAINT_P_ADDRESS
Customers > Customers Menu > Prepaid Discounts (Aloha only) : ACC_MAINT_PREPAID
Customers > Customers Menu > Print Options : CUST_MAINT_PRINT
Customers > Customers Menu > Relations : CUST_MAINT_RELATIONS
Customers > Customers Menu > Transaction Enquiry : CUST_ENQ_TRANSACTIONS
Customers > Relations Management > Call Edits/Delete : CRM_CALL_EDIT
Customers > Relations Management > Log Calls : CUST_CRM_CALLS
Customers > Relations Management > Reasons Setup : CUST_CRM_SETUP
Customers > Relations Management > Relations Management : CUST_CRM
Customers > Relations Management > Reports : CRM_REPORTS
Customers > Reports > Analysis : CUST_REPORTS_1
Customers > Reports > Member Reports : CUST_REPORTS_3
Customers > Reports > Members Other : CUST_REPORTS_2
Customers > Reports > Reference : CUST_REPORTS_4
Customers > Reports > Reports : CUSTOMERS_TRANS_REPORTS
Customers > Reports > Statements : CUST_REPORTS_5
Customers > Reports > Transactions : CUST_REPORTS_6
General Ledger > General Ledger > Cashbook : GL_CASHBOOK
General Ledger > General Ledger > General Ledger : GL
General Ledger > General Ledger > Reports : GL_REPORTS
General Ledger > General Ledger > Setup : GL_SETUP
General Ledger > Journals > Adjust : GL_JOURNALS_ADJUST
General Ledger > Journals > Delete : GL_JOURNALS_DELETE
General Ledger > Journals > Journals : GL_JOURNALS
General Ledger > Journals > New : GL_JOURNALS_NEW
General Ledger > Journals > Post : GL_JOURNALS_POST
General Ledger > Journals > Recall : GL_JOURNALS_RECALL
General Ledger > Journals > Save : GL_JOURNALS_SAVE
Globals > Globals Menu > Backup : BACKUP_UTIL
Globals > Globals Menu > Bank Statement Import : BANK_STATEMENT_IMPORT
Globals > Globals Menu > Business History : BUSINESS_HISTORY
Globals > Globals Menu > Communications : COMMS
Globals > Globals Menu > Document Upload/Download : DOCUMENT_UPLOAD_DOWNLOAD
Globals > Globals Menu > Enquiry : GLOBALS_ENQUIRY
Globals > Globals Menu > Globals Menu : GLOBALS
Globals > Globals Menu > Hide all Costs and Profit : HIDE_ALL_COSTS
Globals > Globals Menu > Reprints : REPRINTS
Globals > Globals Menu > Setup : GLOBALS_SETUP
Globals > Globals Menu > Xero Accounting Integration : XERO_INTEGRATION
Globals > Period Ends / Backup > Day End : PE_DAY_END
Globals > Period Ends / Backup > Month End : PE_MONTH_END
Globals > Period Ends / Backup > Period Ends / Backup : GLOBALS_PERIOD_ENDS
Globals > Period Ends / Backup > Week End : PE_WEEK_END
Globals > Period Ends / Backup > Year End : PE_YEAR_END
Globals > Reports > Global (Restaurant Only) : GLOBALS_REPORTS_GL
Globals > Reports > Reports : GLOBALS_REPORTS
Globals > Reports > Store Specific (Multi Store Only) : GLOBALS_REPORTS_SS
Globals > Utilities > Access Control / Time Attendance : TIME_ATTENDANCE
Globals > Utilities > Advanced Options (Restaurant Only) : GLOBAL_ADVANCED
Globals > Utilities > Aloha Sales Import : ALOHA_SALES_IMPORT
Globals > Utilities > Aloha Sales Reversal : ALOHA_SALES_REVERSAL
Globals > Utilities > Aloha Stock Import : ALOHA_STOCK_IMPORT
Globals > Utilities > DOS Integration : IMPORTEXPORT
Globals > Utilities > e-Destiny Gift Card Enquiry : GC_ENQUIRY
Globals > Utilities > E-mail Queue : EMAIL_QUEUE
Globals > Utilities > Excel Extracts : EXCEL_EXTRACTS
Globals > Utilities > Export Utilities : EXPORT_UTIL_1
Globals > Utilities > iVeri Credit Card Export : ACC_IVERI_EXPORT
Globals > Utilities > Purge Temp Files : PURGE
Globals > Utilities > Rebuild Consolidation Table : REBUILD_CONSOL
Globals > Utilities > Store Communications Portal : COMMS_PORTAL
Globals > Utilities > Transaction Recovery : TRAN_RECOVERY
Globals > Utilities > Utilities : UTILITIES
Globals > Utilities > Voucher Utility : VOUCHER_UTIL
Halo > Halo Campaign Management > Adjust Giftcard Balances : HALO_GC_ADJUST
Halo > Halo Campaign Management > Adjust Giftcard Expiry Dates : HALO_REWARD_ADJ_EXPIRY
Halo > Halo Campaign Management > Bulk Giftcard Transactions : HALO_GC_BULK_TRANSACTION
Halo > Halo Campaign Management > Delete Communications : HALO_COMMS_DELETE
Halo > Halo Campaign Management > Edit Adhoc Campaign : HALO_ADHOC_CAMPAIGNS_EDIT
Halo > Halo Campaign Management > Edit Campaign : HALO_CAMPAIGNS_EDIT
Halo > Halo Campaign Management > Edit Reward : HALO_REWARDS_EDIT
Halo > Halo Campaign Management > Giftcard Enquiry : HALO_GIFTCARD_ENQUIRY
Halo > Halo Campaign Management > Halo Campaign Management : HALO_MANAGEMENT
Halo > Halo Campaign Management > New Adhoc Campaign : HALO_ADHOC_CAMPAIGNS_NEW
Halo > Halo Campaign Management > New Canpaign : HALO_CAMPAIGNS_NEW
Halo > Halo Campaign Management > New Reward : HALO_REWARDS_NEW
Halo > Halo Campaign Management > Reports : HALO_REPORTS
Halo > Halo Campaign Management > Send Communications : HALO_COMMS_SEND
Halo > Halo Campaign Management > Settings : HALO_SETUP
Halo > Halo Campaign Management > : HALO_CLIENT
Headoffice > Headoffice Menu > Global Enquiry : HEADOFFICE_GLOBAL_ENQUIRY
Headoffice > Headoffice Menu > Headoffice Menu : HEADOFFICE
Headoffice > Headoffice Menu > Import : HEADOFFICE_IMPORT
Headoffice > Headoffice Menu > Periods Table : HEADOFFICE_PERIODS
Headoffice > Headoffice Menu > Queries/Filters : HEADOFFICE_QUERY
Headoffice > Headoffice Menu > Reports : HEADOFFICE_REPORTS
Headoffice > Stock Enquiry > Detail : HO_GE_DETAIL
Headoffice > Stock Enquiry > Stock Enquiry : HEADOFFICE_STOCK_ENQUIRY
Headoffice > Stock Enquiry > Summary : HO_GE_SUMMARY
Headoffice > Store Setup > Store Setup : HEADOFFICE_STORES
Headoffice > Store Setup > Store Supplier Item Linking : HO_LOC_SET
Mailshot > Mailshot > Mailshot Menu : MAILSHOT
Mark-it > Mark-it > Delete Templates : EPISYS_DELETE
Mark-it > Mark-it > Design Templates : EPISYS_DESIGN
Mark-it > Mark-it > Mark-it : EPISYS
POS BackOffice > POS Backoffice Menu > Account Journal Reasons : SETUP_JOURNAL_REASONS
POS BackOffice > POS Backoffice Menu > Audits : POS_AUDITS
POS BackOffice > POS Backoffice Menu > Discount Reasons : POS_DISCOUNT_REASON_SETUP
POS BackOffice > POS Backoffice Menu > Foreign Currency Setup : POS_FOREX
POS BackOffice > POS Backoffice Menu > Operator Cashup : POS_ZALL
POS BackOffice > POS Backoffice Menu > POS Backoffice Menu : POS
POS BackOffice > POS Backoffice Menu > POS Listings : POS_LISTING
POS BackOffice > POS Backoffice Menu > Reports : POS_REPORTS
POS BackOffice > POS Backoffice Menu > Stock Adjustment Reasons : SETUP_STOCK_ADJ_REASONS
POS BackOffice > POS Backoffice Menu > Surveys : Survey_Setup
POS BackOffice > POS Backoffice Menu > Workshop Technicians : TECH_SETUP
POS BackOffice > Sales People > Budgets : POS_SALES_PEOPLE_BUDGETS
POS BackOffice > Sales People > Maintenance : POS_SALES_PEOPLE_MAINT
POS BackOffice > Sales People > Sales People : POS_SALEPEOPLE
Reports > Reports > Advanced Options (Restaurant Only) : REPORT_ADVANCED
Reports > Reports > Customers (Customised) : REPORT_WRITER_ACC
Reports > Reports > Globals (Customised) : REPORT_WRITER_GLOBALS
Reports > Reports > Graphs : REPORTS_GRAPHS
Reports > Reports > Headoffice (Customised) : REPORT_WRITER_HO
Reports > Reports > Report Writer /Design Templates : REPORT_WRITER
Reports > Reports > Stock (Customised) : REPORT_WRITER_STOCK
Reports > Reports > Supplier (Customised) : REPORT_WRITER_SUPPLIERS
Stock > Maintenance > Allow Description Change : STOCK_DESC_CHANGE
Stock > Maintenance > Automatic Purchase Order Lead Time : STK_ORDER_LEAD
Stock > Maintenance > Automatic Purchase Order Schedule : STK_ORDER_SHED
Stock > Maintenance > Create New Items : STOCK_MAINT_NEW
Stock > Maintenance > Delete Items : STK_ITEM_DELETE
Stock > Maintenance > Group Setup : STOCK_GROUP_SETUP
Stock > Maintenance > Import Utility : STK_IMPORT_UTIL
Stock > Maintenance > Matrix Setup : STOCK_MATRIX_SETUP
Stock > Maintenance > Min/Max Maintenance : STK_MIN_MAX
Stock > Maintenance > Sundry Charges : STK_MAINT_SUNDRY_CHARGES
Stock > Reports > Branch Transfer Reports : STOCK_REPORTS_TRANSFERS
Stock > Reports > Group Integrity Check : STOCK_REPORTS_GROUP_INTEGRITY
Stock > Reports > Item Analysis : STOCK_REPORTS_ITEM_ANALYSIS
Stock > Reports > Markdown Reports : STOCK_REPORTS_MD
Stock > Reports > Pending Reports : STOCK_REPORTS_PENDING
Stock > Reports > Promotion Reports : STOCK_REPORTS_PROMO
Stock > Reports > Purchases Reports : STOCK_REPORTS_PURCHASES
Stock > Reports > Reference Lists : STOCK_REPORTS_REFERENCE
Stock > Reports > Reports : STOCK_REPORTS
Stock > Reports > Saved Transactions : STOCK_REPORTS_SAVED
Stock > Reports > Stock Write Off Reports : STOCK_REPORTS_SWO
Stock > Reports > Sundry Reports : STOCK_REPORTS_SUNDRIES
Stock > Stock Menu > Advanced Options (Restaurant Only) : STOCK_ADVANCED
Stock > Stock Menu > Communications : STOCK_COMMUNICATIONS
Stock > Stock Menu > Enquiry : STOCK_ENQUIRY
Stock > Stock Menu > Labels : STOCK_LABELS
Stock > Stock Menu > Maintenance : STOCK_MAINT
Stock > Stock Menu > Multi Stock Edit Utility : STOCK_MULTI_EDIT
Stock > Stock Menu > Stock Menu : STOCK
Stock – Item Types & Pricing > Main > Active Price : STK_MAINT_ACTIVE_PRICE
Stock – Item Types & Pricing > Main > Average Cost : STK_MAINT_AVG_COST
Stock – Item Types & Pricing > Main > Cellular Contract : STK_MAINT_TYPE_17
Stock – Item Types & Pricing > Main > Deposit : STK_MAINT_TYPE_3
Stock – Item Types & Pricing > Main > ELF : STK_MAINT_TYPE_22
Stock – Item Types & Pricing > Main > External : STK_MAINT_TYPE_5
Stock – Item Types & Pricing > Main > Finance Option : STK_MAINT_TYPE_20
Stock – Item Types & Pricing > Main > Last Cost : STK_MAINT_LAST_COST
Stock – Item Types & Pricing > Main > Last RSP : STK_MAINT_PRICE_8
Stock – Item Types & Pricing > Main > Linked : STK_MAINT_TYPE_7
Stock – Item Types & Pricing > Main > Manufacture Kits : STK_MAINT_TYPE_10
Stock – Item Types & Pricing > Main > Master Matrix : STK_MAINT_TYPE_12
Stock – Item Types & Pricing > Main > Matrix Item : STK_MAINT_TYPE_13
Stock – Item Types & Pricing > Main > Normal : STK_MAINT_TYPE_1
Stock – Item Types & Pricing > Main > Open : STK_MAINT_TYPE_2
Stock – Item Types & Pricing > Main > Price 1 : STK_MAINT_PRICE_1
Stock – Item Types & Pricing > Main > Price 2 : STK_MAINT_PRICE_2
Stock – Item Types & Pricing > Main > Price 3 : STK_MAINT_PRICE_3
Stock – Item Types & Pricing > Main > Price 4 : STK_MAINT_PRICE_4
Stock – Item Types & Pricing > Main > Price 5 : STK_MAINT_PRICE_5
Stock – Item Types & Pricing > Main > Price 6 : STK_MAINT_PRICE_6
Stock – Item Types & Pricing > Main > Price 7 : STK_MAINT_PRICE_7
Stock – Item Types & Pricing > Main > Promotion Price : STK_MAINT_PRICE_9
Stock – Item Types & Pricing > Main > Recipe Planning Item : STK_MAINT_TYPE_29
Stock – Item Types & Pricing > Main > Roadworthy Certificate : STK_MAINT_TYPE_21
Stock – Item Types & Pricing > Main > Selling Kit (Hamper) : STK_MAINT_TYPE_8
Stock – Item Types & Pricing > Main > Subscription : STK_MAINT_TYPE_9
Stock – Item Types & Pricing > Main > Sundry : STK_MAINT_TYPE_6
Stock – Item Types & Pricing > Main > Trade In : STK_MAINT_TYPE_4
Stock Maintenance > SKU > : STK_MAINT_SKU
Stock Maintenance > Stock Maintenance > AB Audits : STK_AB_AUDIT
Stock Maintenance > Stock Maintenance > Allow Obsolete Item Status Override : STK_OBSOLETE_UNDO
Stock Maintenance > Stock Maintenance > Alternate Description : STK_MAINT_ALT_DESC
Stock Maintenance > Stock Maintenance > Attributes : STK_MAINT_ATTRIB
Stock Maintenance > Stock Maintenance > Barcode : STK_MAINT_BARCODE
Stock Maintenance > Stock Maintenance > Bills Of Materials Tab : STK_MAINT_BOM
Stock Maintenance > Stock Maintenance > Card Access Points : STK_MAINT_CARD_ACCESS
Stock Maintenance > Stock Maintenance > Description : STK_MAINT_DESC
Stock Maintenance > Stock Maintenance > Force Use of Item Setup Checklist : STK_MAINT_CHECKLIST
Stock Maintenance > Stock Maintenance > Groups Tab : STK_MAINT_GROUP
Stock Maintenance > Stock Maintenance > Indicators Tab : STK_MAINT_INDICATORS
Stock Maintenance > Stock Maintenance > Invoice Comments : STK_MAINT_INV_COMMENTS
Stock Maintenance > Stock Maintenance > Item Type : STK_MAINT_TYPE
Stock Maintenance > Stock Maintenance > Levels Tab : STK_MAINT_LEVELS
Stock Maintenance > Stock Maintenance > Linked Items Tab : STK_MAINT_LINKS
Stock Maintenance > Stock Maintenance > List Costs Tab : STK_MAINT_LC
Stock Maintenance > Stock Maintenance > Matrix Items Tab : STK_MAINT_MATRIX
Stock Maintenance > Stock Maintenance > Modifiers Tab : STK_MAINT_MODIFIER
Stock Maintenance > Stock Maintenance > Non Available Time Setup : STK_NON_AVAILABLE
Stock Maintenance > Stock Maintenance > Notes Tab : STK_MAINT_NOTES
Stock Maintenance > Stock Maintenance > Online Store Details : STK_MAINT_ONLINE
Stock Maintenance > Stock Maintenance > Pack Size Descriptor : STK_MAINT_PACK
Stock Maintenance > Stock Maintenance > Pricing Tab : STK_MAINT_PRICING
Stock Maintenance > Stock Maintenance > Stock Group Location Setup : STKGRP_LOC_SET
Stock Maintenance > Stock Maintenance > Stock Location Setup : STK_LOC_SET
Stock Maintenance > Stock Maintenance > Stock Picture : STK_MAINT_PIC
Stock Maintenance > Stock Maintenance > Supplier Tab : STK_MAINT_SUPPLIER
Stock Maintenance > Stock Maintenance > Tax : STK_MAINT_TAX
Stock Transactions(1) > Claims Resolution > Claims Resolution : STK_TRAN_CLAIM_RESOLUTION
Stock Transactions(1) > Claims Resolution > Document Upload : STK_TRAN_CLAIM_DOC_UPLOAD
Stock Transactions(1) > Goods Received > Arrival of Goods : STK_GRV_ARRIVAL
Stock Transactions(1) > Goods Received > Cannot create New GRV : STOCK_TRAN_DISALLOW_NEW_GRV
Stock Transactions(1) > Goods Received > Cannot Post GRV : STOCK_TRAN_NO_POST_GRV
Stock Transactions(1) > Goods Received > Goods Received : STOCK_TRANS_GRV
Stock Transactions(1) > Goods Received > New : ST_GRV_NEW
Stock Transactions(1) > Goods Received > Pre Owned Purchase : STK_TRAN_TRADEIN
Stock Transactions(1) > Goods Received > Recall : ST_GRV_RECALL
Stock Transactions(1) > IBT In > Cannot create New : STOCK_TRAN_DISALLOW_NEW_IBTI
Stock Transactions(1) > IBT In > IBT In : ST_IBTI
Stock Transactions(1) > IBT In > New : ST_IBTI_NEW
Stock Transactions(1) > IBT In > Recall : ST_IBTI_RECALL
Stock Transactions(1) > IBT Out > Cannot create New : STOCK_TRAN_DISALLOW_NEW_IBTO
Stock Transactions(1) > IBT Out > Delete : STOCK_TRANS_IBTO_DEL
Stock Transactions(1) > IBT Out > IBT Out : ST_IBTO
Stock Transactions(1) > IBT Out > New : ST_IBTO_NEW
Stock Transactions(1) > IBT Out > Recall : ST_IBTO_RECALL
Stock Transactions(1) > IBT Request > Bulk Delete : STK_BULK_IBTR_DELETE
Stock Transactions(1) > IBT Request > Cannot Post IBTR : STOCK_TRAN_NO_POST_IBTR
Stock Transactions(1) > IBT Request > IBT Request : ST_IBTR
Stock Transactions(1) > IBT Request > IBT Request Alerts : STK_IBTR_ALERTS
Stock Transactions(1) > IBT Request > Import from file : STK_IBT_REQ_IMPORT
Stock Transactions(1) > IBT Request > New : ST_IBTR_NEW
Stock Transactions(1) > IBT Request > Recall : ST_IBTR_RECALL
Stock Transactions(1) > Inter Branch Transfers > IBT Journal : ST_IBTJOURNAL
Stock Transactions(1) > Inter Branch Transfers > IBT Variances : ST_IBTVARIANCES
Stock Transactions(1) > Item Exchange > Item Exchange : STOCK_TRANSACTIONS_EXCHANGE
Stock Transactions(1) > Item Exchange > New : ST_ITEMEX_NEW
Stock Transactions(1) > Item Exchange > Recall : ST_ITEMEX_RECALL
Stock Transactions(1) > Purchase Orders > Adjust : ST_PO_ADJUST
Stock Transactions(1) > Purchase Orders > Bulk PO Date Adjust : STK_PO_DATE_ADJ
Stock Transactions(1) > Purchase Orders > Bulk Purchase Orders : STK_TRAN_PO_BULK
Stock Transactions(1) > Purchase Orders > Bulk Recall : STK_TRAN_PO_BULK_RECALL
Stock Transactions(1) > Purchase Orders > Cannot Post PO : STOCK_TRAN_NO_POST_PO
Stock Transactions(1) > Purchase Orders > Delete : STOCK_TRANS_PO_DEL
Stock Transactions(1) > Purchase Orders > Distribute Purchase Orders : STK_TRAN_PO_DISTRIB
Stock Transactions(1) > Purchase Orders > Force Comments on PO : STK_TRAN_PO_COMMENTS
Stock Transactions(1) > Purchase Orders > New : ST_PO_NEW
Stock Transactions(1) > Purchase Orders > Purchase Order Event Tracking : STK_PO_CONFIRMATION
Stock Transactions(1) > Purchase Orders > Purchase Orders : STOCK_TRANS_PO
Stock Transactions(1) > Purchase Orders > Recall : ST_PO_RECALL
Stock Transactions(1) > Purchase Orders > Reinstate : STK_TRAN_PO_REINSTATE
Stock Transactions(1) > Purchase Orders > Reorder Report and Auto Reorder : STK_TRANS_REORDER
Stock Transactions(1) > Purchase Orders > Special Purchase Orders : SPECIAL_PO
Stock Transactions(1) > Transactions > Product Enrichment : STK_TRAN_PRODUCT_ENRICHMENT
Stock Transactions(1) > Transactions > Sage Transaction Approvals : STK_SAGE_APPROVE
Stock Transactions(1) > Transactions > Void Transaction in Business History : BO_VOID_TRAN
Stock Transactions(1) > > : STK_TRAN_BULK_IIT_RECALL
Stock Transactions(1) > > : STK_TRAN_GRV_SERIAL
Stock Transactions(2) > Bulk Inter Item Transfers > Alerts : STK_BOMR_ALERTS
Stock Transactions(2) > Bulk Inter Item Transfers > Bulk Inter Item Transfers : STK_TRAN_BULK_IIT
Stock Transactions(2) > Bulk Inter Item Transfers > Cancel : BOM_REQUEST_CANCEL
Stock Transactions(2) > Bulk Inter Item Transfers > Import : BOM_REQUEST_IMPORT
Stock Transactions(2) > Bulk Inter Item Transfers > New : BOM_REQUEST_NEW
Stock Transactions(2) > Bulk Inter Item Transfers > Process : BOM_REQUEST_PROCESS
Stock Transactions(2) > Bulk Promotions > Bulk Promotions : ST_BUNDLEPROMO
Stock Transactions(2) > Bulk Promotions > New : ST_BUNDLEPROMO_NEW
Stock Transactions(2) > Bulk Promotions > Recall : ST_BUNDLEPROMO_RECALL
Stock Transactions(2) > Consuption Issue > Consuption Issue : STOCK_TRAN_CONS_ISSUE
Stock Transactions(2) > Consuption Issue > New : ST_CI_NEW
Stock Transactions(2) > Consuption Issue > Recall : ST_CI_RECALL
Stock Transactions(2) > Consuption Issue > Reverse : ST_CI_REVERSE
Stock Transactions(2) > Inter Item Transfers > Inter Item Transfers : STOCK_TRANS_IIT
Stock Transactions(2) > Inter Item Transfers > New : ST_IIT_NEW
Stock Transactions(2) > Inter Item Transfers > Recall : ST_IIT_RECALL
Stock Transactions(2) > Main > Consumption Invoice and Write Off : STK_CONS_INV_WO
Stock Transactions(2) > Main > Demo Stock Transfer : DEMO_STOCK
Stock Transactions(2) > Main > Design Templates : STOCK_TRAN_TEMPLATES
Stock Transactions(2) > Main > Halo Voucher Campaigns : HALO_VOUCHER_CAMPAIGNS
Stock Transactions(2) > Main > Inter Item Transfers at Specific Cost : STK_TRAN_IIT_COST
Stock Transactions(2) > Main > Manual Item Entry : DEMO_STOCK_MANUAL
Stock Transactions(2) > Main > Reprint : STK_TRAN_REPRINT
Stock Transactions(2) > Main > Stock Average Cost Adjustments : STOCK_TRANS_SWO_COST
Stock Transactions(2) > Main > Workorder Stock Issue : WORKORDERS_STOCK_ISSUE
Stock Transactions(2) > Manufacture BOM / Bulk Portions > Manufacture BOM / Bulk Portions : STOCK_TRAN_MAKE_BOM
Stock Transactions(2) > Manufacture BOM / Bulk Portions > New : ST_MANUF_NEW
Stock Transactions(2) > Manufacture BOM / Bulk Portions > Recall : ST_MANUF_RECALL
Stock Transactions(2) > Mark Downs > Mark Downs : STOCK_TRANS_MD
Stock Transactions(2) > Mark Downs > New : ST_MD_NEW
Stock Transactions(2) > Mark Downs > Recall : ST_MD_RECALL
Stock Transactions(2) > Mix and Match Promotions > Mix and Match Promotions : ST_MMPROMO
Stock Transactions(2) > Mix and Match Promotions > New : ST_MMPROMO_NEW
Stock Transactions(2) > Mix and Match Promotions > Recall : ST_MMPROMO_RECALL
Stock Transactions(2) > Price Adjustments > New : ST_PA_NEW
Stock Transactions(2) > Price Adjustments > Price Adjustments : STOCK_TRANS_PRICE_ADJ
Stock Transactions(2) > Price Adjustments > Recall : ST_PA_RECALL
Stock Transactions(2) > Promotions > Import : STK_TRANS_PROMO_IMPORT
Stock Transactions(2) > Promotions > New : ST_PROMO_NEW
Stock Transactions(2) > Promotions > Promotions : STOCK_TRANS_PROMO
Stock Transactions(2) > Promotions > Recall : ST_PROMO_RECALL
Stock Transactions(2) > Return to Supplier > New : ST_RTS_NEW
Stock Transactions(2) > Return to Supplier > Recall : ST_RTS_RECALL
Stock Transactions(2) > Return to Supplier Request > Document Upload : STK_TRAN_RTS_REQUEST_DOC_UPLOAD
Stock Transactions(2) > Return to Supplier Request > New : STK_TRAN_RTS_REQUEST_NEW
Stock Transactions(2) > Return to Supplier Request > Recall : STK_TRAN_RTS_REQUEST_RECALL
Stock Transactions(2) > Return to Supplier Request > Return to Supplier Request : STK_TRAN_RTS_REQUEST
Stock Transactions(2) > Stock Adjustments > New : ST_SWO_NEW
Stock Transactions(2) > Stock Adjustments > Recall : ST_SWO_RECALL
Stock Transactions(2) > Stock Adjustments > Stock Adjustments : STOCK_TRANS_WO
Stock Transactions(2) > Stock Take > Enquiry : STK_ST_TAKE_ENQUIRY
Stock Transactions(2) > Stock Take > Hide Theoretical SOH in Adjust Count Stock : STK_TAKE_ADJ_NO_SOH
Stock Transactions(2) > Stock Take > Initialise All : STOCK_TAKE_INIT_ALL
Stock Transactions(2) > Stock Take > Stock Take : STOCK_TRANS_STOCK_TAKE
Stock Transactions(2) > Swill > Adjust Swill : STK_SWILL_ADJ
Stock Transactions(2) > Swill > Swill : STK_SWILL
Suppliers > Maintenance > Delete All List Costs : SUPPLIER_DEL_LC
Suppliers > Maintenance > Maintenance : SUPPLIERS_MAINT
Suppliers > Maintenance > Supplier Location Setup : SUPP_LOC_SET
Suppliers > Suppliers Menu > Advanced Options (Restaurant Only) : SUPPLIER_ADVANCED
Suppliers > Suppliers Menu > Enquiry : SUPPLIERS_ENQUIRY
Suppliers > Suppliers Menu > GL Export : SUPPLIERS_ACCPAC
Suppliers > Suppliers Menu > Reports : SUPPLIERS_REPORTS
Suppliers > Suppliers Menu > Suppliers Menu : SUPPLIERS
Suppliers > Transactions > Journals : SUPPLIERS_JOURNALS
Suppliers > Transactions > Payments : SUPPLIERS_PAYMENTS
Suppliers > Transactions > Transactions : SUPPLIERS_TRANSACTIONS

*This mapping is an independent reference guide compiled through empirical testing for integration and reporting purposes. It is not affiliated with, sponsored by, or endorsed by the software vendors. Use at your own risk.

When bringing companies’ systems together, ‘duplications’ in master data are likely to occur.

Take for example the following three StockCodes and Descriptions:

StockCode	Description
001	LG TV 42 inch LCD
ABC	TV LG (42 lcd)
A01	LG TV 32″ LED 32LJ500D

For a human it is easy to see that the first two products refer to the same thing.  A simple ‘exact’ comparison (WHERE InvMaster.Description = #InvMaster.Description) will fail, so one will have to use a method to show ‘most likely’ matches.

I’ve chosen a ‘word match and score’ technique because of the programming simplicity and (small) size of my data set (+-2000 lines).
(Machine learning algorithms are more suited for much larger data sets)

Using the example above, I create the following ‘base’ table:

StockCode	Word
001	LG
001	TV
001	42
001	INCH
001	LCD
ABC	TV
ABC	LG
ABC	42
ABC	LCD
A01	LG
A01	TV
A01	32
A01	32LJ500D

Note the standardisation, using a nested REPLACE containing
– UPPER
– elimination of sequential spaces (delimiter in this case)
– removing of ‘special’ characters and punctuation: ‘&’, ‘-‘, ‘.’, ‘”‘, ‘/’, ‘\’, ‘(‘ and ‘)’.

I break up each string into words by looping the following insert into my (say) ‘StockCodeWordList’ table:

SELECT StockCode,
SUBSTRING(Description, 1, CASE WHEN CHARINDEX(‘ ‘, Description) = 0 THEN 100 ELSE CHARINDEX(‘ ‘, Description) END) AS [Word]

The next step is to ‘score’ StockCodes based on the amount of ‘word matches’ with a specific (Source) Description.

I do this by looping the following statement:

UPDATE dbo.StockCodeWordList SET Score =0; — Reset for a new match exercise
DECLARE @DescString VARCHAR(100) = (SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(UPPER(Description),’-‘,”),’&’,”),’ ‘,’ ‘),’ ‘,’ ‘),’ ‘,’ ‘),’ ‘,’ ‘) FROM dbo.InvMaster WHERE StockCode = @SourceStockCode);

DECLARE @Counter INT = 0;
WHILE @Counter < 10 — Assuming there’s a maximum of 10 ‘words’ in a Description
BEGIN
UPDATE dbo.StockCodeWordList SET Score = Score+1
WHERE [Word] = (SELECT REPLACE(REPLACE(SUBSTRING(@DescString, 1, CASE WHEN CHARINDEX(‘ ‘, @DescString) = 0 THEN 100 ELSE CHARINDEX(‘ ‘, @DescString) END),'(‘,”),’)’,”))

SET @DescString = (SELECT CASE WHEN CHARINDEX(‘ ‘, @DescString) = 0 THEN NULL
ELSE LTRIM(SUBSTRING(@DescString, CHARINDEX(‘ ‘, @DescString)+1, 100)) END);
SET @Counter = @Counter+1
END

Using StockCode: 001 Description: ‘LG TV 42 inch LCD’ as Source, will produce the following result:

StockCode: ABC Description: TV LG (42 lcd) Score: 4

StockCode: A01 Description: LG TV 32″ LED 32LJ500D Score: 2

I was tasked to project manage the incorporation of a newly acquired company into our group.

Apart from arranging the infrastructure changes, systems setups and training required, I wrote the SQL (Jobs) to migrate data from Pastel Evolution to Syspro.

Herewith a link between some of the main Syspro tables and their counterparts in Pastel Evolution:

Syspro	Evolution
ApBank	BankMain
ApInvoice	InvNum, PostAP
ApSupplier	Vendor
ArCustomer	Client
ArInvoice	InvNum, PostAR
ArMasterSub	Client.MainAccLink
ArTrnDetail	_btblInvoiceLines
GenMaster	Accounts
InvMaster	StkItem, _etblUnits
InvWarehouse	WhseStk
InvWhControl	WhseMst
LotDetail	_etblLotTracking
SalArea	Areas
SalSalesperson	SalesRep
TblCurrency	Currency

A very handy Evolution table worth mentioning is TrCodes. Link this with PostAP and PostAR to provide you with the whole spectrum of AP and AR postings.

Presenting Syspro’s BOMs in a ‘flattened’ (or ‘exploded’) format in Excel or in SQL Server is very handy for doing/checking Costings or doing Advanced Trail Kits.

My initial attempt at flattening BOMs was to use the TSQL recursive loop method:

WITH FlatBom(ParentPart, Component, BomLevel, RequiredQty)
AS (
— Anchor
SELECT StockCode AS ‘ParentPart’,
StockCode AS ‘Component’,
1,
CAST(0 AS DECIMAL (12,6))
FROM dbo.InvMaster
WHERE StockCode = ‘YourParentStockCode’ — Insert your Parent item – or list of Parent items – in here

UNION ALL

— Recursive call
SELECT BOM.ParentPart AS ‘ParentPart’,
BOM.Component AS ‘Component’,
BomLevel + 1,
QtyPer
FROM FlatBom CTE
JOIN(SELECT
ParentPart AS ‘ParentPart’,
Component AS ‘Component’,
QtyPer
FROM dbo.BomStructure
WHERE Route = ‘1’ AND ParentPart IS NOT NULL AND ParentPart <> ‘ ‘ –Note Route
) AS BOM
ON CTE.Component = BOM.ParentPart
)

SELECT * FROM FlatBom

 

But I found this method to be extremely slow – especially when one has thousands of multilevel BOMs.  A whole lot of articles on the Net confirms this – and discourages the use of recursive loops.

 

As simple (and much faster) alternative is to use a (temp) table with an insert for each of your BOM levels:

SELECT ParentPart AS [Parent], 1 AS [BomLevel], ParentPart, Component, QtyPer AS [RequiredQty]
INTO #FlatBom
FROM dbo.BomStructure
WHERE ParentPart = ‘YourParentStockCode’ — Insert your Parent item – or list of Parent items – in here
AND Route = ‘1’;

INSERT INTO #FlatBom(Parent, BomLevel, ParentPart, Component, RequiredQty)
SELECT t.Parent, 2, b.ParentPart, b.Component, b.QtyPer
FROM dbo.BomStructure b
INNER JOIN #FlatBom t ON t.Component = b.ParentPart
WHERE Route = ‘1’;

INSERT INTO #FlatBom(Parent, BomLevel, ParentPart, Component, RequiredQty)
SELECT t.Parent, 3, b.ParentPart, b.Component, b.QtyPer
FROM dbo.BomStructure b
INNER JOIN #FlatBom t ON t.Component = b.ParentPart AND t.BomLevel = 2
WHERE Route = ‘1’
AND t.BomLevel = 2;

INSERT INTO #FlatBom(Parent, BomLevel, ParentPart, Component, RequiredQty)
SELECT t.Parent, 4, b.ParentPart, b.Component, b.QtyPer
FROM dbo.BomStructure b
INNER JOIN #FlatBom t ON t.Component = b.ParentPart AND t.BomLevel = 3
WHERE Route = ‘1’
AND t.BomLevel = 3;

…                                             — Repeat, for your MAX BomLevel times

SELECT * FROM #FlatBom

 

I would suggest including the ScrapPercentage and other attributes in your flattening script to make (e.g.) Costings and Trail Kits more accurate.

 

 

Generally, I found that it is difficult to answer the question:  “How far are we with the stock take?”

The most commonly used, quick-and-dirty measures are to

1.   Look at the bottom-line value (or unit) variance on your Syspro or custom report.  (Least accurate method)
2.   Count the number of StockCodes counted (captured) vs. Total number of StockCodes (in the stock take file)
3.   Count the number of Bins counted (captured) vs. Total number of Bins in the Stock Take file (or at least, the ones with QtyOnHand > 0)

But none of these measures provide an indication of how much of the physical warehouse (space) has been covered.

The following method might be helpful, assuming you make use of Syspro’s MultiBin functionality:

For the purpose of explanation, let’s assume you have (as shown in Figure 1 below):

•  2 x Racks, numbered ‘1’ and ‘2’
•  each Rack is 3 x levels high, numbered (level) ‘1’, ‘2’ and ‘3’
•  each Rack is 2 x Bins ‘deep’, numbered ‘A’ and ‘B’

 

 

 

 

 

 

 

 

 

 

So you have 12 x Bins in total, numbered (say) <Rack number><Level number><Depth>:  11A, 12A, 13A, 11B, 12B, 13B and 21A, 22A, 23A, 21B, 22B, 23B

Import data from Syspro into Excel using the following Query:

SELECT Bin, MAX(NumTimesCaptured) AS [Count] FROM dbo.InvStockTake

(one can add filters for specific Warehouses, etc.)

Use Excel’s VLOOKUP function – and Conditional Formatting – to set up the simple visualization below (Figure 2, query data in column A and B).

 

 

 

 

 

 

 

 

 

 

The formula in (say) Cell F3 – which can be copied to the rest of the ‘Bin’ cells (columns F to H):

=IF(ISNA(VLOOKUP($D3&F$2&$E3,$A:$C,2,FALSE)),””,IF(VLOOKUP($D3&F$2&$E3,$A:$C,2,FALSE)=0,$D3&F$2&$E3,VLOOKUP($D3&F$2&$E3,$A:$C,2,FALSE)))

The visualization tells us the following:

•  (At least one) StockCode in Bin ’11A’ has been counted once.  I used Conditional formatting to show these Bins as ‘green’ or ‘ok’.
• Level 2 of Rack 1 has not been counted yet – or these bins are empty.
• (At least one) StockCode in Bin ’13A’ has been counted twice.  This is handy if you’d like to see if a Bin was ‘double checked’ or if everything has to be double counted (e.g. two separate teams).
• ‘Column’ or Depth ‘B’ of Rack 2 has not been counted – or the whole ‘Column’ in Rack 2 is empty.
• Additionally, the Excel formula also caters for instances where the Bin does not exist in Syspro – these calls will be empty (blank). (Not shown in this example)

Hope it helps!

I’ve recently implemented Assets in Syspro 7 and would like to share some of what I learnt:

 

Start with Branches, Cost Centers and Asset Groups

If you need to produce financial statements for various Branches to Cost Center level, make sure your (structured) GlCodes cater for this level of granularity.  You’ll use these GlCodes in the integration setup.

 

Bulk create GlCodes

I used the  AssetIntegDet table, which I designed in Excel first, as the basis to see which (new) GlCodes were required.  Since I had to create over 860 new GlCodes, I uploaded the GlCode and Description into a SQL Server temp table and bulk inserted the GlCodes using a ‘standard’ Expense GlCode as template.

 

Coding of Assets

Of the various Asset numbering/coding methodologies available, I decided to use:  Asset Type  + Acquisition year + Acquisition month + a sequential number.   e.g. ‘PM201703001’ for the 1st ‘Plant and Machinery’ item acquired in March 2017.  This way, one may move an asset to any Branch and/or Cost Center and/or Location – without changing the asset number.

 

Excel Asset master and acquisition book

Initially I thought I’d write SQL code to ‘transfer’ asset master data (including acquisitions) from the ‘test’ to ‘live’ environment (ensuring a quick take-on) – but since a whole set of tables are touched during asset creation and acquisition, I decided to rather use the Syspro V7 ‘copy-and-paste’ method.  It turned out to be a very quick and audit-able method.   Even if you’re doing over 2500 assets in one go.  (It took me around 40 minutes from start to end, basically just waiting for Syspro to write amendment journals, etc.)  I suggest keeping the asset master and acquisition sheets in exactly the same (sorted by Asset number) order.

 

Method to ‘reset’ Assets

During the test phase, it helped a lot to have a method to ‘reset’ everything and start from scratch.  I used a SQL SP that deletes the relevant Asset and GL tables.

 

Use SQL SPs for reporting

I had to report (in Excel) asset transactions and values on around 35 x Branches, 38 x Cost Centers and 242 Locations.  Instead of ‘hard coding’ these, using a parameterised SP saved several hours.

 

Best time:  start of new financial year

Its just best for balances and change management.

 

As long as n is strictly less than 100:

RIGHT(100+n,2)

(Yes, now it seems obvious..  🙂
Kudos to Steve Kass

 

I had a situation whereby a Syspro 6.0 MRP run grouped all the Raw Material requirements into the ‘Current’ demand bucket – not spreading the demand over the next few months – as was specified in the Finished Goods lines’ Forecast – and subsequently calculated in the MRP Demand (MrpRequirement table).

For example, if the forecast of Finished Goods item ‘Bicycle’ is

Month	Forecast Qty
Jan	200
Feb	100
Apr	300
May	50
Jun	200

and one can easily build 300 Bicycles a month, then one would expect the Raw Material demand of (say) ‘Handle Bar’ (where one Bicycle has only one Handle Bar, assuming no Outstanding Purchase Orders and no Stock On Hand or WIP) would be the same as above:

Month	Demand Qty
Jan	200
Feb	100
Apr	300
May	50
Jun	200

But in this particular case, the demand for ‘Handle Bar’ was calculated by MRP as:

Month	Demand Qty
Jan	850

 

I immediately compared all the Manufacturing and Requirements Planning setups, InvMaster (of Finished Goods and Raw Material item), BomStructure and even InvWarehouse fields with that of a working system – but found no differences.

I then noticed in the Finished Goods Pegging (Requirements Planning Query) that all Suggested Jobs had a (calculated) Start Date of 2000 days before the Demand (Forecast) date!  I initially thought that this was coming from the (InvMaster) Lead times – but after having Syspro calculate Lead times and then forcing test times,  it made no difference.

Eventually, it turned out to be the ‘Time Requirements’ from the BomOperations in each of the Finished Goods BOM Setups (see screenshot below).

I had to set the ‘Fixed elapsed time’ = ‘Yes’ (by setting BomOperations.TimeCalcFlag=’N’), ‘Elapsed Time’ = 1 (BomOperations.ElapsedTime=1) and ‘Movement time’ = -1 (BomOperations.MovementTime=-1).  The MRP calculation then sees the Finished Goods item manufacturing time as 1 day.

 

Once the GL Integration is fixed, one would ideally like to ‘lock down’ the used GlCodes to prevent accidental /malicious (other than the standard Syspro or authorised GL Journal) postings.  Syspro V6 had a ‘Password’ field in GenMaster – so one could easily do a database update of the specified GlCodes.  Unfortunately(?), V7 does not allow a Password update via database – so each code has to be done via the GUI Front End.  I had around 900 GlCodes to ‘lock’, so the only other alternative was to use Business Processes.

 

As soon as one tick the ‘Business process enabled’ box in Ledger Code Maintenance, one can select up to ten (10) Business Processes that are allowed for the specific GlCode.  Upon saving the changes, Syspro stores the selection as Business Process codes in GenMaster (BusinesProcess1, BusinessProcess2, etc).

 

Herewith a list of the Business process codes (extracted from IMPGLB.IMP):

 

Module	Description	BP Code
APS	Admin Tax Code Maint – AP	ATM
APS	Admin Tax Code Maint – Sales	ATC
APS	AP Branch Maint – Control	APC
APS	AP Branch Maint – Discount	APD
APS	AP Integration Freight	GEP
APS	AP Integration Input Tax	GAP
APS	AP Integration Merchandise	GLP
APS	AP Integration QST	GIP
APS	AP Invoice Posting	AP1
APS	AP Permanent Entries Maint	API
APS	AP Revaluation	APR
APS	AP Supplier Maint – Freight	SMF
APS	AP Supplier Maint – Merch	SMM
ARP	AR Customer Branches	ACB
ARP	AR Customer Branches-Asset	ACA
ARP	AR Customer Branches-Expense	ACE
ARP	AR Customer Branches-Liability	ACL
ARP	AR Customer Branches-Revenue	ACR
ARP	AR Payment Interface- Asset	APA
ARP	AR Payment Interface- Expense	APE
ARP	AR Payment Interface-Liability	APL
ARP	SO Counter Sale Pay Interface	CSP
ARP	Trade Promotions Accrual	TPB
ARP	Trade Promotions Accrual Def	TPA
ARP	Trade Promotions Deduction Def	TPD
ARP	Trade Promotions Expense	TPF
ARP	Trade Promotions Expense Def	TPE
ARP	Trade Promotions Resolution Def	TPR
ARP	Trade Promotions Writeoff Def	TPW
ARS	Admin Extended Tax Code Maint	ETC
ARS	Admin Import Extended Tax Code	LIF
ARS	AP Int Contra Suspense	GLR
ARS	AR Int Contra Suspense	GIR
ARS	AR Int Exchange Rate Variance	GER
ARS	AR Invoice	ARI
ARS	AR Invoice Adjustments	ARA
ARS	AR Misc Receipts – Cr	ARC
ARS	AR Misc Receipts – Dr	ARD
ARS	AR Pmt Exchange Rate Variance	ERV
ARS	AR Revaluation	ARR
ARS	AR Sales Interface- Expense	ASE
ARS	AR Sales Interface- Revenue	ASR
ARS	Dispatch Note Trial Balance	DNT
ARS	Reserved	ARR
ASS	Asset Int Asset Control	GAA
ASS	Asset Int Profit And Loss	GIA
ASS	Assets GL Interface-Asset	AGA
ASS	Assets GL Interface-Capital	AGC
ASS	Assets GL Interface-Depreciate	AGD
ASS	Assets GL Interface-Expense	AGE
ASS	Assets GL Interface-Revenue	AGR
ASS	Assets Maintenance	ASC
ASS	Assets Maintenance EUL	AST
ASS	Assets profit	ASP
ASS	GL Integration – Revenue	GRS
CSH	Cash Book Deposit	CST
CSH	CB Bank Maint – Bank Charge	BMG
CSH	CB Bank Maint – Cash account	BMC
CSH	CB Bank Maint – Exchange	BME
CSH	CB Integration Inter-Comp	GLC
CSH	CB Integration Withdrawal	GAC
CSH	CB Posting	CSH
GEN	GL Alternative Currency Setup	ACS
GEN	GL Balance of Recurring Jnl	GBE
GEN	GL Initial History	GIH
GEN	GL Integration balance	GCG
GEN	GL Journal Posting	GJL
GEN	GL Journal Posting Recurring	GJR
GEN	GL Percentage of Recur Jnl	GPT
GEN	GL Prov of Recur Jnl – Cr	GCR
GEN	GL Prov of Recur Jnl – Dr	GDR
GEN	GL Retained Earnings balance	GRE
GEN	GL Revaluation Category Maint	GRC
GEN	GL Statistical Accounting	STA
GEN	GL Template Maintenance	GTM
GEN	WIP Job Receipts	WJR
INV	ABC Element Descriptor Maint	AED
INV	GIT Receipts with Apportionment	GRA
INV	GIT Warehouse Matrices	GWM
INV	GRN Adjustments	GRN
INV	Inv GL Interface  PPV	IGG
INV	Inv GL Interface Ledger Acc	IGI
INV	Inv Integration Costs	GEI
INV	Inv Integration Non-stock	GAI
INV	Inv Integration Operation	GLI
INV	Inv Movements – Change Cost	IMC
INV	Inv Movements – Change Qty	IMQ
INV	Inv Movements – Cost Mod	IMM
INV	Inv Movements – Issues & Adj	IMI
INV	Inv Movements – Non Merch	IMN
INV	Inv Movements – Physical Count	IMP
INV	Inv Movements – Receipts	IMR
INV	Inv Movements – Transfers	IMT
INV	Inv Receipts	REC
INV	Inv Stock Code Maint	SCA
INV	Inv WH for Stock Code	IWS
INV	Inventory adjustment	IAH
INV	Inventory expense issue	IEX
INV	Inventory receipt	IRE
INV	Inventory transfer IN	ITI
INV	Inventory transfer OUT	ITO
INV	Inventory Warehouses-Assets	IWA
INV	Inventory Warehouses-Expense	IWE
INV	Inventory Warehouses-Liability	IWL
INV	LCT Element Maintenance	LEM
INV	PO Blanket PO Contract Maint	BPO
INV	PO Entry	PON
INV	PO Receipts & Inspection Credit	PRI
INV	PO Receipts & Inspection Debit	PRD
INV	PO Receipts & Inspection Nonm	PRN
INV	Product Config Maint	PCH
INV	Quotation Non-Stocked	QNS
INV	Requisition Entry	RQE
INV	SO Counter Sales	SCS
INV	WIP Inspection Maint – Other	WIO
INV	WIP Inspection Maint – Rework	WIR
INV	WIP Job Creation and Maint	JCM
INV	WIP Job Issues	WJI
INV	WIP Non-stk Job Create & Maint	JNS
INV	WIP P&C Standard Hierarchy	JSH
WPB	P&C Job Billing Adjustment	JBA
WPB	WIP Job Closure	JCL
WPB	WIP Part Billing	WPB
WPL	BOM Cost Centers	BCC
WPL	BOM Work Centers	BWC
WPL	WIP Integration	GAW
WPL	WIP labor posting	WPL
WPL	WIP Non-productive Code Maint	BNP

 

As part of the GL Re-coding project (after the Consolidation Project), I had to revamp Syspro GL Integration.

 

I found that Syspro’s Help does not necessarily specify the GL Account Type – and whether the Control Account flag should be switched ‘on’ or not – for GlCodes used in the GL Integration Setup.  I also could not find any documentation online.

 

So herewith a list of the (most used) Syspro (V7) GL Integration Account Types* – and if the Control Account flag should** be switched ‘on’ or not.

* Where the Type is marked as ‘Any’, Syspro does not specify (via a warning or error message) what Account Type should be used.  Best use what makes sense in your environment

** Only on some occasions does Syspro warn that a control account must be used (or not) – so if I’ve used the following keys in the ‘Control?’ column:

• Yes (Warn)  :  Syspro warning message if not a control account.
• No  :  No Syspro warning or error – and no guidance from the Business Processes.
• No (Warn)  :  Syspro warning message if a control account.
• No (Ignore)  :  No Syspro warning message. (No check is done).
• NO (Error)  :  Syspro error if a control account.

 

Setup >> Gl Integration tab and account	Type	Control?	BP Code
General Ledger – Forced balance	Capital	Yes (Warn)	GCG
General Ledger – Retained earnings	Capital	Yes (Warn)	GRE
Payables – Default merchandise	Expense	No (Ignore)	GLP
Payables – Default freight	Expense	No (Ignore)	GEP
Payables – Input Tax	Asset	No (Ignore)	GAP
Payables – Settlement discount (tax)	Expense	No
Payables – Contra suspense	Liability	No (Ignore)	GLR
Payables – Maintain Banks – Cash Account	Asset	Yes (Warn)	BMC
Payables – Maintain Banks – Exchange variance	Asset	Yes (Warn)	BME
Payables – Maintain Branches – A/P control ledger code	Liability	Yes (Warn)	APC
Payables – Maintain Branches – A/P discount taken ledger code	Revenue	Yes (Warn)	APD
Payables – Maintain Tax Codes – Sales tax ledger code	Liability	Yes (Warn)	ATC
Payables – Maintain Tax Codes – AP tax ledger code	Asset	Yes (Warn)	ATM
Receivables – Exchange rate variance	Expense	No (Ignore)	GER
Receivables – Adjustment value (tax)	Any	No
Receivables – Maintain Payments Interfaces – Counter Sales- Payments	Asset	No (Warn)	APA
Receivables – Maintain Payments Interfaces – Payments	Asset	No (Warn)	APA
Receivables – Maintain Payments Interfaces – Discounts	Expense	No (Warn)	APE
Receivables – Maintain Payments Interfaces – Adjustments	Expense	NO (Error)	ARA
Receivables – Maintain Payments Interfaces – Deposits liability	Liability	No
Receivables – Maintain Branches – A/R Control (Dr)	Asset	Yes (Warn)	ACA
Receivables – Maintain Branches – Freight charges (Cr)	Revenue	Yes (Warn)	ACR
Receivables – Maintain Branches – Finance charges (Cr)	Revenue	Yes (Warn)	ACR
Receivables – Maintain Branches – Rounding error (Cr)	Revenue	Yes (Warn)	ACR
Receivables – Maintain Branches – Deposit revenue (Cr)	Revenue	Yes (Warn)	ACR
Receivables – Maintain Branches – Sales tax (Cr)	Liability	Yes (Warn)	ACL
Receivables – Maintain Branches – Freight (Dr)	Expense	Yes (Warn)	ACE
Receivables – Maintain Branches – Non-stocked misc and freight (Cr)	Expense	No
Receivables – Maintain Sales Interfaces – Sales	Revenue	No (Warn)	ASR
Receivables – Maintain Sales Interfaces – Sales returns	Revenue	No (Warn)	ASR
Receivables – Maintain Sales Interfaces – Trade discount	Revenue	No (Warn)	ASR
Receivables – Maintain Sales Interfaces – Cost of sales	Expense	No (Warn)	ASE
Receivables – Maintain Sales Interfaces – IBT sales	Revenue	No (Warn)	ASR
Receivables – Maintain Sales Interfaces – IBT cost of sales	Expense	No (Warn)	ASE
Cash Book – Inter-company	Liability	No (Ignore)	GLC
Cash Book – Tax for deposits	Liability	No (Warn)	CSH
Cash Book – Tax for withdrawals	Asset	No (Ignore)	GAC
Cash Book – Interbank transfer suspense	Asset	No
Inventory – Non-stocked	Asset	No (Ignore)	GAI
Inventory – Cost of goods sold adjustment	Expense	No (Ignore)	GEI
Inventory – Subcontract operations	Liability	No (Ignore)	GLI
Inventory – GRN suspense	Liability	NO (Error)	GRN
Inventory – Purchase price variance	Expense	Yes?***	IGG
Inventory – ABC costing variance	Asset	No
Inventory – Goods in Transit	Asset	No
Inventory – Dispatched but not invoiced	Asset	No
Inventory – Maintain Inventory Interfaces – (All – except Receipts)	Expense	No (Ignore)	IMC,IMQ, IMM, IMI, IMN, IMP, IMT
Inventory – Maintain Inventory Interfaces – Receipts	Revenue	No (Ignore)	IMR
Inventory – Maintain Warehouses – Warehouse control	Asset	Yes (Warn)	IWA
Inventory – Maintain Warehouses – Warehouse variance	Asset	Yes (Warn)	IWA
Inventory – Maintain Warehouses – GRN suspense	Liability	Yes****	IWS
Inventory – Maintain Warehouses – Goods in transit control	Asset	Yes (Warn)	IWA
Inventory – Maintain Warehouses – Dispatched but not invoiced	Asset	Yes (Warn)	IWA
Inventory – Maintain Warehouses – WIP inspection	Asset	Yes (Warn)	IWA
Inventory – Maintain Warehouses – Work in progress	Asset	Yes (Warn)	IWA
Inventory – Maintain Warehouses – WIP variances	Asset	Yes (Warn)	IWA
Inventory – Maintain Warehouses – Automatically cleared variances	Asset	Yes (Warn)	IWA
W.I.P. – Work in progress	Any	No (Ignore)	GAW
W.I.P. – Variance	Any	No
W.I.P. – Automatically cleared variances	Any	No
W.I.P. – WIP Inspection	Any	No

 

***  Business Process says ‘Warning if not a control account’, yet Syspro Front End (GUI) provides no warning or error.

**** Business Process says ‘Warn if control account’, yet Syspro Front End (GUI) gives a warning if not a control account.  Interestingly, die GRN Suspense setup in the ‘default’ Inventory setup (GUI), provides no warning or error.

 

Let me know if you differ on any of the above findings!

 

UPDATE :  I’ve correlated the Standard Syspro Business Processes (code in rightmost column) with the list – and could confirm most of the settings.