As long as n is strictly less than 100:
(Yes, now it seems obvious.. 🙂
Kudos to Steve Kass
Business Analysis, BI and Reporting, Project Management
As long as n is strictly less than 100:
(Yes, now it seems obvious.. 🙂
Kudos to Steve Kass
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):
|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 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 Adjustments||ARA|
|ARS||AR Misc Receipts – Cr||ARC|
|ARS||AR Misc Receipts – Dr||ARD|
|ARS||AR Pmt Exchange Rate Variance||ERV|
|ARS||AR Sales Interface- Expense||ASE|
|ARS||AR Sales Interface- Revenue||ASR|
|ARS||Dispatch Note Trial Balance||DNT|
|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 EUL||AST|
|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|
|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||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 Stock Code Maint||SCA|
|INV||Inv WH for Stock Code||IWS|
|INV||Inventory expense issue||IEX|
|INV||Inventory transfer IN||ITI|
|INV||Inventory transfer OUT||ITO|
|INV||LCT Element Maintenance||LEM|
|INV||PO Blanket PO Contract Maint||BPO|
|INV||PO Receipts & Inspection Credit||PRI|
|INV||PO Receipts & Inspection Debit||PRD|
|INV||PO Receipts & Inspection Nonm||PRN|
|INV||Product Config Maint||PCH|
|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 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:
|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.
It is always tempting just to MONTH(GETDATE())+1 – but then there’s the end of the year to consider.
DECLARE @ThisMonth DATE = (SELECT DATEFROMPARTS(YEAR(GETDATE()),MONTH(GETDATE()),1));
DECLARE @NextMonth DATE = (SELECT
WHEN MONTH(@ThisMonth) = 12 THEN (SELECT DATEFROMPARTS(YEAR(@ThisMonth)+1,1,1))
ELSE (SELECT DATEFROMPARTS(YEAR(@ThisMonth),MONTH(@ThisMonth)+1,1))
DECLARE @NextMonth1 DATE = (SELECT
WHEN MONTH(@NextMonth) = 12 THEN (SELECT DATEFROMPARTS(YEAR(@NextMonth)+1,1,1))
ELSE (SELECT DATEFROMPARTS(YEAR(@NextMonth),MONTH(@NextMonth)+1,1))
DECLARE @NextMonth2 DATE = (SELECT
WHEN MONTH(@NextMonth1) = 12 THEN (SELECT DATEFROMPARTS(YEAR(@NextMonth1)+1,1,1))
ELSE (SELECT DATEFROMPARTS(YEAR(@NextMonth1),MONTH(@NextMonth1)+1,1))
DECLARE @NextMonth3 DATE = (SELECT
WHEN MONTH(@NextMonth2) = 12 THEN (SELECT DATEFROMPARTS(YEAR(@NextMonth2)+1,1,1))
ELSE (SELECT DATEFROMPARTS(YEAR(@NextMonth2),MONTH(@NextMonth2)+1,1))
DECLARE @NextMonth4 DATE = (SELECT
WHEN MONTH(@NextMonth3) = 12 THEN (SELECT DATEFROMPARTS(YEAR(@NextMonth3)+1,1,1))
ELSE (SELECT DATEFROMPARTS(YEAR(@NextMonth3),MONTH(@NextMonth3)+1,1))
— The following expression calculates ==> max(@val1, @val2)
SELECT 0.5 * ((@val1 + @val2) + ABS(@val1 – @val2))
Kudos to splattne.
(Used in Demand Forecasting calculation project)
I was very grateful to receive the BFI Group Financial Award – for the Consolidation Project and other work done during the 2016 Financial Year.
I am delighted and privileged to work for a company that believes in recognition for hard work!
I was tasked to project manage the consolidation of 13 x Syspro (V6) companies into one Syspro (V7) company in 2015.
The first phase was a master file cleanup, standardisation and setup check. The following areas were covered:
Inventory Master (InvMaster)
I had to ensure each (unique) StockCode had the same description, Unit of Measure, Alternate Unit Of Measure and conversion factor, etc. – and that it represented the same item in reality – for all companies. I found the easiest way to do this, was to create a single ‘consolidated’ InvMaster – and compare each companies’ data to it.
Customer and Supplier Master (ArCustomer and ApSupplier)
Each companies’ Customer and Supplier accounts remained unique – but Area, Class, Branch, Terms Codes, etc. had to be standardised across all companies. Price lists and Contracts were also made unique.
General Ledger (GenMaster)
There was no time for a complete re-code of all GlCodes – so I prefixed each companies’ GlCodes to make it unique.
Other data and setup
Banks, Tax Codes, Gl Integration, Document Numbering, additional fields*, were just some of the list of areas that were covered.
*V6 uses AdmFormData, whereas V7 makes use of the (MUCH more convenient) ‘plus’ tables. e.g. [InvMaster+], [ArCustomer+], etc.
A staggered approach was used for implementation (as opposed to a ‘big bang’) – and this proved to be very wise. Learnings from each go-live were incorporated for the next consolidation run.
I wrote SQL Server 2014 scripts to handle the extraction, transformation and loading (ETL) of each companies’ Syspro data into the Consolidated environment. For the very first implementation, I ran the (list of 70!) manually – but the rest was done via SQL Server Jobs.
I recently wrote a report for a customer with Average costing switched ON, so there were no dated lots/batches to use in the aging.
I made the assumption that FIFO was used.
I first calculated the receipts (POs, Jobs, Adjustments and Stock Receipts) quantities and values for the last 3, 6, 9, 12 (etc.) months, at Actual Costs (from InvMovements).
I then systematically calculated backward from the InvWarehouse.QtyOnHand, by deducting each period’s receipts, until InvWarehouse.QtyOnHand was ‘depleted’. The remaining periods’ receipts were then cleared/zeroed.
Let me know if you need assistance with something similar.
When a stock take result will cause a Sales Order’s Ship Qty to go to negative or zero – the Stock Take Confirmation process will halt – until all the relevant Sales Order detail lines are de-allocated (‘available’). Syspro’s standard report shows a list of StockCodes – so one would need to find the relating Sales Order(s) (via Inventory Enquiry) – and de-allocate accordingly. Quite a lengthy process!
But here’s a SQL query that will produce a (crude) list of Sales Order numbers that are preventing the Stock Take Confirmation to complete:
SELECT DISTINCT h.SalesOrder, h.ShipAddress1, d.MWarehouse, d.MStockCode, d.MStockDes, d.MOrderQty, d.MShipQty FROM SorDetail d, SorMaster h WHERE h.CancelledFlag <> 'Y' AND h.OrderStatus <> '9' AND h.OrderStatus <> '*' AND h.OrderStatus <> '\' AND d.SalesOrderDetStat <> 'C' AND d.SalesOrder = h.SalesOrder AND h.DocumentType <> 'C' AND d.LineType <> '6' AND (d.MShipQty + d.MBackOrderQty) > 0 AND CONCAT(d.MWarehouse, d.MStockCode) IN ( SELECT CONCAT(w.Warehouse, w.StockCode) FROM InvWarehouse w, InvStockTake s WHERE w.Warehouse = s.Warehouse AND w.StockCode = s.StockCode AND (s.OrigQtyOnHand - s.QtyCaptured) > 0 -- Only interested in REDUCTIONS AND ((w.QtyOnHand-(w.QtyAllocated-w.QtyOnBackOrder)) - (s.OrigQtyOnHand - s.QtyCaptured)) < 0 -- Only where ShipQtys will be affected ) ORDER BY h.SalesOrder
This might speed this up!
PS: It might even speed things up more – if you run this BEFORE the stock take – and reduce allocations where more than 90% of the stock holding is allocated to Sales Orders.