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.

UPDATE:  see (much more) efficient solution towards the end of this post!

 

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
CASE
WHEN MONTH(@ThisMonth) = 12 THEN (SELECT DATEFROMPARTS(YEAR(@ThisMonth)+1,1,1))
ELSE (SELECT DATEFROMPARTS(YEAR(@ThisMonth),MONTH(@ThisMonth)+1,1))
END);

DECLARE @NextMonth1 DATE = (SELECT
CASE
WHEN MONTH(@NextMonth) = 12 THEN (SELECT DATEFROMPARTS(YEAR(@NextMonth)+1,1,1))
ELSE (SELECT DATEFROMPARTS(YEAR(@NextMonth),MONTH(@NextMonth)+1,1))
END);

DECLARE @NextMonth2 DATE = (SELECT
CASE
WHEN MONTH(@NextMonth1) = 12 THEN (SELECT DATEFROMPARTS(YEAR(@NextMonth1)+1,1,1))
ELSE (SELECT DATEFROMPARTS(YEAR(@NextMonth1),MONTH(@NextMonth1)+1,1))
END);

DECLARE @NextMonth3 DATE = (SELECT
CASE
WHEN MONTH(@NextMonth2) = 12 THEN (SELECT DATEFROMPARTS(YEAR(@NextMonth2)+1,1,1))
ELSE (SELECT DATEFROMPARTS(YEAR(@NextMonth2),MONTH(@NextMonth2)+1,1))
END);

DECLARE @NextMonth4 DATE = (SELECT
CASE
WHEN MONTH(@NextMonth3) = 12 THEN (SELECT DATEFROMPARTS(YEAR(@NextMonth3)+1,1,1))
ELSE (SELECT DATEFROMPARTS(YEAR(@NextMonth3),MONTH(@NextMonth3)+1,1))
END);

 

— UPDATE!  I have just learned about EOMONTH()…  So a more efficient solution would be:

DECLARE @ThisMonth DATE = DATEADD(DAY, 1, EOMONTH(GETDATE(),-1))
DECLARE @NextMonth DATE = DATEADD(DAY, 1, EOMONTH(GETDATE()))
DECLARE @NextMonth1 DATE = DATEADD(DAY, 1, EOMONTH(GETDATE(),1))
DECLARE @NextMonth2 DATE = DATEADD(DAY, 1, EOMONTH(GETDATE(),2))
DECLARE @NextMonth3 DATE = DATEADD(DAY, 1, EOMONTH(GETDATE(),3))
DECLARE @NextMonth4 DATE = DATEADD(DAY, 1, EOMONTH(GETDATE(),4))

—————————————————————————————————-

SELECT @ThisMonth
UNION
SELECT @NextMonth
UNION
SELECT @NextMonth1
UNION
SELECT @NextMonth2
UNION
SELECT @NextMonth3
UNION
SELECT @NextMonth4