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.

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

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:

But in this particular case, the demand for ‘Handle Bar’ was calculated by MRP as:

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.

— The following expression calculates ==> max(@val1, @val2)

SELECT 0.5 * ((@val1 + @val2) + ABS(@val1 – @val2))

Kudos to splattne.

(Used in Demand Forecasting calculation project)