BOM Component Required Qty

[Former Member]

Hi,

I need a query which when a BOM Parent Qty is given it should show the Instock of the component item as well as the required qty to meet the BOM Qty demand.

Thanks and Regards,

S.G.Sekar

[kothandaraman_nagarajan · Accepted solution]

Hi,

Try this:

SELECT T0.[Father] as 'Assembly',
-- T0.[Quantity],
[%1] as
'BuildQty',
T0.[code] as 'Component1', t10.[ItemName]
'Description1', T0.[Quantity] as 'Quantity1',
[%1] * t0.[Quantity]
as 'ExtQty1',
t10.OnHand as 'OnHand1',
case when t10.OnHand -
([%1] * t0.[Quantity]) > 0 then 0 else
-(t10.OnHand - ([%1] *
t0.[Quantity])) end as 'Shortage1',
T1.[Code] as 'Component2',
t11.[ItemName] 'Description2', T1.[Quantity] as 'Quantity2',
[%1]
* t0.[Quantity] * t1.[Quantity] as 'ExtQty2',
t11.OnHand as
'OnHand2',
case when t11.OnHand - ([%1] * t0.[Quantity] *
t1.[Quantity]) > 0 then 0 else
-(t11.OnHand - ([%1] *
t0.[Quantity] * t1.[Quantity])) end as 'Shortage2',
T2.[Code] as
'Component3', t12.[ItemName] 'Description3', T2.[Quantity] as
'Quantity3',
[%1] * t0.[Quantity] * t1.[Quantity] * t2.[Quantity]
as 'ExtQty3',
t12.OnHand as 'OnHand3',
case when t12.OnHand -
([%1] * t0.[Quantity] * t1.[Quantity] * t2.[Quantity]) > 0 then 0
else
-(t12.OnHand - ([%1] * t0.[Quantity] * t1.[Quantity] *
t2.[Quantity])) end as 'Shortage3',
T3.[Code] as 'Component4',
t13.[ItemName] 'Description4', T3.[Quantity] as 'Quantity4',
[%1]
* t0.[Quantity] * t1.[Quantity] * t2.[Quantity] * t3.[Quantity] as
'ExtQty4',
t13.OnHand as 'OnHand4',
case when t13.OnHand -
([%1] * t0.[Quantity] * t1.[Quantity] * t2.[Quantity] *
t3.[Quantity]) > 0 then 0 else
-(t13.OnHand - ([%1] *
t0.[Quantity] * t1.[Quantity] * t2.[Quantity] * t3.[Quantity])) end
as 'Shortage4',
T4.[Code] as 'Component5', t14.[ItemName]
'Description5', T4.[Quantity] as 'Quantity5',
[%1] * t0.[Quantity]
* t1.[Quantity] * t2.[Quantity] * t3.[Quantity] * t4.[Quantity] as
'ExtQty5',
t14.OnHand as 'OnHand5',
case when t14.OnHand -
([%1] * t0.[Quantity] * t1.[Quantity] * t2.[Quantity] * t3.[Quantity]
* t4.[Quantity]) > 0 then 0 else
-(t14.OnHand - ([%1] *
t0.[Quantity] * t1.[Quantity] * t2.[Quantity] * t3.[Quantity] *
t4.[Quantity])) end as 'Shortage5',
T5.[Code] as 'Component6',
t15.[ItemName] 'Description6', T5.[Quantity] as 'Quantity6',
[%1]
* t0.[Quantity] * t1.[Quantity] * t2.[Quantity] * t3.[Quantity] *
t4.[Quantity] * t5.[Quantity] as 'ExtQty6',
t15.OnHand as
'OnHand6',
case when t15.OnHand - ([%1] * t0.[Quantity] *
t1.[Quantity] * t2.[Quantity] * t3.[Quantity] * t4.[Quantity] *
t5.[Quantity]) > 0 then 0 else
-(t15.OnHand - ([%1] *
t0.[Quantity] * t1.[Quantity] * t2.[Quantity] * t3.[Quantity] *
t4.[Quantity] * t5.[Quantity])) end as 'Shortage6' 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
WHERE T0.[Father] = [%0]

Hope helpful.

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

Thanks Nagarajan.It works.

Regards ,

S.G.Sekar

[Former Member]

Hi,

I modified the Query and used.Now the Required Qty should not come from Instock but should be calculated from Available Qty(Instock-commited+ordered).Also Report should display Commited ,ordered and available. Can you do this?

SELECT T0.[Father] as 'Assembly',
-- T0.[Quantity],
[%1] as
'BuildQty',
T0.[code] as 'Component', t10.[ItemName]
'Description1', T0.[Quantity] as 'Quantity',
[%1] * t0.[Quantity]
as 'ExtQty',
t10.OnHand as 'OnHand',
case when t10.OnHand -
([%1] * t0.[Quantity]) > 0 then 0 else
-(t10.OnHand - ([%1] *
t0.[Quantity])) end as 'Required Qty'
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
WHERE T0.[Father] = [%0]

Thanks and Regards,

S.G.Sekar

[kothandaraman_nagarajan]

Hi,

Please post as new discussion.

Thanks & Regards,

Nagarajan