--create working table
SELECT ParentPart as StockCode, CAST(0 as int) As Lev, CAST(10 as float) as Reqd,
CAST(1 as int) as Mnth, CAST('' as varchar(MAX)) as sort
INTO #tmp
FROM BomStructure
WHERE 1=0;
--insert all the models and requirements
DECLARE @level int;
SET @level = 0;
--INSERT INTO #tmp VALUES('DMF322',@level,10,11);
INSERT INTO #tmp VALUES('DMF322',@level,10,12,'001');
--loop through each matching level in the BOM table
WHILE (@@RowCount > 0) AND (@level < 15)
BEGIN
SET @level = @level + 1;
INSERT INTO #tmp
SELECT B.Component, @level , A.Reqd * B.QtyPer, A.Mnth,
A.sort + '.' + RIGHT('00' + CAST(ROW_NUMBER() OVER(ORDER BY B.SequenceNum) as varchar(MAX)),3)
FROM BomStructure B JOIN #tmp A
ON A.StockCode = B.ParentPart
JOIN InvMaster I
ON I.StockCode = B.ParentPart
WHERE A.Lev = @level - 1
AND ((B.StructureOffDate IS NULL) OR (B.StructureOffDate >=GetDate()))
AND ((B.StructureOnDate IS NULL) OR (B.StructureOnDate <= GetDate()))
AND B.Route='0'
AND I.PartCategory <> 'B'
END
SELECT * FROM #tmp
ORDER BY sort