-------------------------------------------------------------------------------
-- This example uses a recursive BOM CTE in order to list a breakdown of a
-- bill of materials (BOM) in hierarchical order. The key component of this
-- is the ability to sort the list. The ROW_Number function is used to
-- successively append a sequence number to each recursive instance of a component.
-------------------------------------------------------------------------------
ALTER PROCEDURE [dbo].[uspExtractBOM]
@model char(6)
AS
DECLARE @CurDate datetime;
SET @CurDate = Convert(DateTime, Convert(VarChar, GetDate(), 101));
WITH Parts(ParentPart,BomSort,SequenceNum,Component, QtyPer,StructureOnDate, StructureOffDate, ComponentLevel,ExplodedCost,PartCat) AS
(
SELECT b.ParentPart AS ProductParentPart,
CAST(RIGHT('00000' + CAST(ROW_NUMBER() OVER(ORDER BY SequenceNum) as varchar(30)),5) as varchar(30)),
b.SequenceNum,
b.Component, cast(b.QtyPer as float) As AccQtyPer,b.StructureOnDate,
b.StructureOffDate, 1 AS ComponentLevel,
cast(b.QtyPer * i.MaterialCost AS float) as Cost,
i.PartCategory
FROM BomStructure AS b
INNER JOIN InvMaster i ON b.Component=i.StockCode
WHERE b.ParentPart = @model
AND [Route]='0'
AND ((b.StructureOffDate IS NULL) OR (b.StructureOffDate >=@CurDate))
AND ((b.StructureOnDate IS NULL) OR (b.StructureOnDate <= @CurDate))
UNION ALL
SELECT bom.ParentPart,
CAST(
CAST(p.BomSort as varchar(30)) + '.' +
RIGHT('00000' +
CAST(CAST(ROW_NUMBER() OVER(ORDER BY bom.SequenceNum,bom.Component) as varchar(30)) as varchar(30)),5) as varchar),
p.SequenceNum, bom.Component, cast(bom.QtyPer*p.QtyPer as float) As AccQtyPer,
bom.StructureOnDate,bom.StructureOffDate, ComponentLevel + 1,
cast(bom.QtyPer*p.QtyPer*i.MaterialCost as float) AS ExplodedCost,i.PartCategory
FROM BomStructure AS bom
INNER JOIN InvMaster i ON bom.Component=i.StockCode
INNER JOIN Parts AS p ON bom.ParentPart = p.Component
WHERE [Route]='0'
AND ((bom.StructureOffDate IS NULL) OR (bom.StructureOffDate >=@CurDate))
AND ((bom.StructureOnDate IS NULL) OR (bom.StructureOnDate <= @CurDate))
AND (p.PartCat<>'B')
)
SELECT ROW_NUMBER() OVER(ORDER BY BomSort) as BomRow,p.ComponentLevel,p.ParentPart,p.Component,I.Description,p.QtyPer,
p.StructureOffDate, p.ExplodedCost,p.PartCat
FROM Parts p JOIN InvMaster I
ON p.Component = I.StockCode
ORDER BY BomSort