BOM and Standard Cost

[bradderz]

Hi Experts,

I am currently undergoing a big project which requires a query to display a BOM and its contents in the aim of a predictive std cost report. some of the fields i have left as NULL as this is down to manual input but whilst the query works to a degree i am unable to see when in BOMS another BOM has been used as a component:
this is my logic:

WITH ComponentPrices AS (
SELECT
T0.Father AS ParentItem,
T0.Code AS Component,
T0.Quantity,
T0.Price AS ComponentPrice,
T1.LstEvlPric AS STDComponentPrice,
T0.Quantity * T0.Price AS ComponentTotalPrice,
T0.Quantity * T1.LstEvlPric AS STDComponentTotalPrice
FROM ITT1 T0
INNER JOIN OITM T1 ON T0.Code = T1.ItemCode
WHERE T1.QryGroup6 = 'Y'
)

SELECT
T0.Father AS "PARENT PRODUCT CODE",
T0.VisOrder AS "Line Level",
T0.Code AS "PRODUCT COMPONENT",
T1.ItemName AS "COMPONENT DESCRIPTION",
T0.Quantity AS "QTY",
T0.Currency,
T0.Price AS "UNIT COST",
T0.Quantity * T0.Price AS "TOTAL COST",
T2.CardName AS "SUPPLIER",
T0.Quantity * T0.Price * 0.01 AS "HANDLING 1% (TBC)", -- Holding Charge - 1% of stock Price
T0.Quantity * T0.Price * 0.02 AS "CUSTOM 2% (TBC)",
'1.5' AS "TRANSPORT COST (%)",
T0.Quantity * T0.Price * 0.015 AS "TRANS COST (TBC)",
NULL AS "FLIGHT RISK(%)",
SUM(T0.Quantity * T1.LstEvlPric) * (SELECT TOP 1 Rate FROM ORTT WHERE Currency = 'EUR' AND RateDate <= CONVERT(DATE, GETDATE()) ORDER BY RateDate DESC) AS "STD 2024 IN €",
(SUM(T0.Quantity * T0.Price) + (T0.Quantity * T0.Price * 0.01) + (T0.Quantity * T0.Price * 0.02) + (T0.Quantity * T0.Price * 0.015)) AS "STD 2024 IN GBP",
NULL AS "STD 2023 IN €",
NULL AS "STD 2023 IN GBP",
NULL AS "DIFF **bleep** 24/23",
NULL AS "DIFF. % 24/23",
NULL AS "LIST",
'GBP' AS "CURRENCY",
NULL AS "LIST IN EURO",
NULL AS "COST EURO (X QNT)",
NULL AS "NOTE",
NULL AS "CODICE PRODOTTO",
NULL AS "TOTALE 2020 EURO"
FROM ITT1 T0
INNER JOIN OITM T1 ON T0.Code = T1.ItemCode
INNER JOIN OCRD T2 ON T1.CardCode = T2.CardCode
WHERE T1.QryGroup6 = 'Y'
GROUP BY T0.Father, T0.VisOrder, T0.Code, T1.ItemName, T0.Quantity, T0.Currency, T1.LstEvlPric, T2.CardName,T0.Price

UNION ALL

SELECT
ParentItem AS "PARENT PRODUCT CODE",
NULL AS "Line Level",
NULL AS "PRODUCT COMPONENT",
NULL AS "COMPONENT DESCRIPTION",
NULL AS "QTY",
NULL AS Currency,
MAX(ComponentPrice) AS "UNIT COST",
SUM(ComponentTotalPrice) AS "TOTAL COST",
NULL AS "SUPPLIER",
NULL AS "HDLN",
NULL AS "CUSTOM%",
NULL AS "TRASP. FOB (%)",
NULL AS "TRASP. FOB (euro)",
NULL AS "FLIGHT RISK(%)",
NULL AS "STD 2024 IN €",
NULL AS "STD 2024 IN GBP",
NULL AS "STD 2023 IN €",
NULL AS "STD 2023 IN GBP",
NULL AS "DIFF **bleep** 24/23",
NULL AS "DIFF. % 24/23",
NULL AS "LIST",
'GBP' AS "CURRENCY",
NULL AS "LIST IN EURO",
NULL AS "COST EURO (X QNT)",
NULL AS "NOTE",
NULL AS "CODICE PRODOTTO",
NULL AS "TOTALE 2020 EURO"
FROM ComponentPrices
GROUP BY ParentItem

ORDER BY "PARENT PRODUCT CODE", "Line Level"

Any assistance would be greatly appreciated.

Best wishes

Brad

[LoHa · Accepted solution]

Hi Bradderz,

best practice for boms is a recurisve query.

WITH Data as 
(
SELECT 
	  1 AS [Level]
	, OITT.Code AS [HeadCode]
	, OITT.Code AS [LevelCode]
	, ITT1.Code AS [ItemCode]
	, OITM.ItemName
	, ITT1.Quantity 

FROM 
	OITT 
		INNER JOIN ITT1 ON 
			OITT.Code = ITT1.Father
		INNER JOIN OITM ON 
			ITT1.Code = OITM.ItemCode
WHERE 
	OITT.Code = 'HKW1923AX1C'

UNION ALL

SELECT 
	  Data.Level+1 
	, Data.HeadCode
	, OITT.Code
	, ITT1.Code 
	, OITM.ItemName
	, ITT1.Quantity

FROM 
	Data 
		INNER JOIN OITT ON 
			Data.ItemCode = OITT.Code
		INNER JOIN ITT1 ON 
			OITT.Code = ITT1.Father
		INNER JOIN OITM ON 
			ITT1.Code = OITM.ItemCode
)

SELECT 
 * 
FROM 
	Data

On the last select you can add further informations

regards Lothar