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.

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.