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:

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:

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:

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.