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.

 

 

In Syspro, it is very handy to add as many custom fields as you want – but then to report on these fields could be a pain. 

I found it easiest to create a view based on AdmFormdata (where the custom field data are stored) – and then report using only the view.

For example, if you are storing and capturing stock dimensions (Length, Width, Height, etc) – your AdmFormdata might look like this:

FormType	KeyField	FieldName	AlphaValue	NumericValue
STK	A100	DIM_L		10
STK	A100	DIM_W		20
STK	A100	DIM_H		30
STK	A100	DIM_M		40
STK	A201	DIM_L		11
STK	A201	DIM_W		22
STK	A201	DIM_H		33
STK	A201	DIM_M		44

where 

–  ‘FormType’ indicates the data is connected to Inventory (InvMaster) and will always be ‘STK’.

–  ‘KeyField’ contains the InMaster primary key = StockCode (in this case two stock codes A100 and A201)

–  ‘FieldName’ is the (user defined) fieldname e.g. DIM_L = Length, DIM_W = Width, DIM_H = Height and DIM_M = Weight (Mass)

–  ‘AlphaValue’ not used in this case – but can be used for text/alphanumeric values.

–  ‘NumericValue’ is the field value.

 

Now I would create the following simple view:

CREATE VIEW StockCodeDimensions

AS

SELECT KeyField AS StockCode,

MAX(CASE WHEN FieldName = ‘DIM_L’

 THEN NumericValue

 ELSE NULL END) AS Length,

MAX(CASE WHEN FieldName = ‘DIM_W’

 THEN NumericValue

 ELSE NULL END) AS Width,

MAX(CASE WHEN FieldName = ‘DIM_H’

 THEN NumericValue

 ELSE NULL END) AS Height,

MAX(CASE WHEN FieldName = ‘DIM_M’

 THEN NumericValue

 ELSE NULL END) AS Weight

FROM AdmFormData

WHERE FormType = ‘STK’ AND (FieldName LIKE ‘DIM%’)

GROUP BY KeyField;

 

 Resulting in the following table:    (SELECT * FROM StockCodeDimensions)

StockCode	Length	Width	Height	Weight
A100	10	20	30	40
A201	11	22	33	44

 

Note that I used  ” FieldName LIKE ‘DIM% ” in my query above.  This can simply be modified/copied to create a view for (say) ‘ANA’ if analysis codes are used to specify (eg) stock code category, colour, age group, chemical restriction, etc.

 

The following is a useful query for extracting the current (active) promotional prices from a Sage Line 500 price list table (oplistm) – where (say) ‘MN’ is the main selling Finished Goods warehouse in stockm and all prices should be shown as inclusive of VAT (14%):

select p.price_list, p.product_code, s.long_description, round(s.price*1.14,2) normal_price,
CASE
WHEN p.vat_inclusive_flag = “Y” THEN p.price
WHEN p.vat_inclusive_flag = “N” THEN round(p.price*1.14,2)
END promo_price,
p.price_start_date,
p.price_end_date
FROM oplistm p, stockm s
WHERE p.product_code = s.product AND s.warehouse = “MN”
AND p.sequence_number <> ”  ”
AND p.price_start_date <=today
AND p.price_end_date >= today
ORDER BY p.product_code, p.price_start_date;