Registration

Dear SAP Community Member,
In order to fully benefit from what the SAP Community has to offer, please register at:
http://scn.sap.com
Thank you,
The SAP Community team.
Skip to end of metadata
Go to start of metadata

(Back to I-PR Production Module SQL Posted to Wiki Listing - I-PR Production Module SQL Posted to Wiki )

TYPE: Useful SQL.

AUTHOR NAME: Balakumar Viswanathan

PURPOSE AND BACKGROUND: This SQL will list all the components (upto 6 levels deep) for the selected Parent (father) item. I frequently saw in the Forum that some people needs to run the MRP for specific Sale order or for specific items. This query won't fulfill their needs but it is surely helpful for those kind of scenarios.  If you find this SQL helpful, then use “copy and paste” to place and save it using the Query Generator of your SAP B1 installation.

Here is a screen print of the SQL results:

Copy and Paste this SQL:

--I-PR  To Check the Components upto 6 levels deep into a BOM 1 VB 2011 04 06

--DESCRIPTION:  This SQL list out either a selected component of an BOM or an entire list of all components for Bills of Material.  Several important fields are included.

--AUTHOR(s):
--Version 1 Balakumar Viswanathan 2011 04 06

SELECT T0.[Father] as 'Assembly',
[%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]

  • No labels