Simple Stock Description (string) comparison on Syspro data, using MS SQL Server

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 42
A01 LG
A01 TV
A01 32
A01 32LJ500D

Note the standardisation, using a nested REPLACE containing
– 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
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

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

Migration and consolidation of Pastel Evolution to Syspro (v7)

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.

How to flatten (or ‘explode’) a Syspro Bill Of Material (BOM): recursive loop vs. simple temp table method

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’,
FROM dbo.InvMaster
WHERE StockCode = ‘YourParentStockCode’ — Insert your Parent item – or list of Parent items – in here


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



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



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



Simple way to monitor Rack and/or Bin progress in Excel during a Syspro Stock Take

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’
Example of Warehouse Rack and Bin layout

Figure 1:  Example of Warehouse Rack and Bin layout











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).

Excel example of monitoring Racks /Bins

Figure 2:  Excel example of monitoring Racks /Bins











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


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!

Syspro 7 Assets: Tips and tricks

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.


Syspro 6.0 BomOperations TimeCalcFlag, ElapsedTime and MovementTime

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).

BomOperations TimeCalcFlag, ElapsedTime and MovementTime

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.


Syspro Business Process codes for GlCodes – from IMPGLB.IMP

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 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


Syspro GL Integration – Account Types and Control Account flags

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.

Consolidating several Syspro (V6) companies into one Syspro (V7) company.

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.