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

 

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

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

You’ve got table #t1:

InvoiceNumber	StockCode
Inv001	Stk001
Inv001	Stk002
Inv001	Stk003
Inv002	Stk001
Inv002	Stk003

But you want:

InvoiceNumber	StockList
Inv001	Stk001,Stk002,Stk003
Inv002	Stk001,Stk003

 

Then use:

SELECT

DISTINCT t2.InvoiceNumber,

SUBSTRING((Select‘,’+RTRIM(t1.StockCode)AS[text()]

FROM #t1 t1

WHERE t1.InvoiceNumber = t2.InvoiceNumber

ORDER BY t1.InvoiceNumber,StockCode

For XML PATH (”)),2, 1000)[StockList]

FROM  #t1 t2;