Query to showed all level of BOM details, sort by create/update date

[Former Member]

Hi expert,

I need a query to display all the level of BOM and can be sort by create / update date.

The display of query should be as below:

A (1st level) Parent BOM

B (2nd level) Child BOM 1

B (2nd level) Child BOM 2

C (3rd level)  Child BOM 2 - 1

C (3rd level)  Child BOM 2 - 2

B (2nd level) Child BOM 3

B (2nd level) Child BOM 4

Below is the BOM  query that can only display up to 2nd level and cannot sort by date.

Can someone please help to modify or there is a better query?

Thanks

Declare @BOMDetails table(TreeType Nvarchar(MAX),PItem NVARCHAR(Max),PName NVARCHAR(MAX),CItem  NVARCHAR(Max),CName NVARCHAR(MAX),[Quantity] Numeric(18,2),[UoM] NVARCHAR(MAX),[WareHouse] NVARCHAR(MAX),[IssuMethod] NVARCHAR(MAX),[PriceList] NVARCHAR(MAX))

INSERT Into @BOMDetails

SELECT T1.TreeType ,T0.Father AS [Parent Code], T2.ItemName AS [Parent Description], T0.Code AS [Child Code],

T1.ItemName AS [Child Description], T0.Quantity ,T0.Uom ,T0.Warehouse ,T0.IssueMthd,T0.PriceList  

FROM ITT1 T0 INNER JOIN OITM T1 ON T0.Code = T1.ItemCode

             INNER JOIN OITM T2 ON T0.Father = T2.ItemCode

Union All

SELECT ' ',T0.Father as [Parent Code], T2.ItemName AS [Parent Description], ' ', ' ', 0, ' ',' ',' ' , 0 FROM ITT1 T0 INNER JOIN OITM T1

ON T0.Code = T1.ItemCode INNER JOIN OITM T2 ON T0.Father = T2.ItemCode

Group By T0.Father,T2.ItemName

ORDER BY T0.Father,t0.Code

update @BOMDetails set PItem='' ,PName='' where TreeType='N' or TreeType='P'

Select PItem as[Parent Code] ,PName as [Parent Description],CItem as [Child Code],CName as [Child Description],Quantity,UoM,IssuMethod ,PriceList    from @BOMDetails

[kothandaraman_nagarajan · Accepted solution]

Hi,

Try this query and modify as per your requirement:

SELECT

T0.[Father] as
'Assembly',T0.[code] as 'Component1', t10.[ItemName]
'Description1',T1.[Code] as 'Component2', t11.[ItemName]
'Description2', T2.[Code] as 'Component3', t12.[ItemName]
'Description3', T3.[Code] as 'Component4', t13.[ItemName]
'Description4',T4.[Code] as 'Component5', t14.[ItemName]
'Description5', T5.[Code] as 'Component6', t15.[ItemName]
'Description6'

FROM

ITT1 T0 LEFT OUTER
JOIN ITT1 T1 on T0.Code = T1.Father LEFT OUTER JOIN ITT1 T2 on
T1.Code = T2.Father LEFT OUTER JOIN ITT1 T3 on T2.Code = T3.Father
LEFT OUTER JOIN ITT1 T4 on T3.Code = T4.Father LEFT OUTER JOIN ITT1
T5 on T4.Code = T5.Father LEFT OUTER JOIN ITT1 T6 on T5.Code =
T6.Father left outer join oitm t20 on t0.father = t20.itemcode left
outer join oitm t10 on t0.code = t10.itemcode left outer join oitm
t11 on t1.code = t11.itemcode left outer join oitm t12 on t2.code =
t12.itemcode left outer join oitm t13 on t3.code = t13.itemcode left
outer join oitm t14 on t4.code = t14.itemcode left outer join oitm
t15 on t5.code = t15.itemcode

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

Hi Nagarajan,

Thank you for the query.

Is it possible to list out other parts that are not a BOM?

Can the query display in vertical format?

Thanks

[kothandaraman_nagarajan · Accepted solution]

Hi,

1.The above query will return only BOM items.

2. What you mean by vertical format?

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

Hi,

1) is it possible to display items without BOM? the complete BOM, including child bom of each parent bom

2) vertical format:

Parent BOM A

           Child BOM - 1

           Child BOM - 2

                   Child BOM - 2 - 1

                   Child BOM - 2 - 2

           Child BOM - 3

           Child BOM - 4

Parent BOM B

           Child BOM - 1

           Child BOM - 2

                   Child BOM - 2 - 1

                   Child BOM - 2 - 2

           Child BOM - 3

                   Child BOM - 3 - 1

                   Child BOM - 3 - 2

           Child BOM - 4

Thanks

[kothandaraman_nagarajan · Accepted solution]

Hi,

1. The above query is written up to six level  of components. If you need more level, please modify the query.

2. Not possible to display in vertical format by query.

Thanks & Regards,

Nagarajan