(Back to I-PR Production Module SQL Posted to Wiki Listing - I-PR Production Module SQL Posted to Wiki )
TYPE: Frequently Requested SQL.
AUTHOR NAME: Zal Parchem
PURPOSE AND BACKGROUND: This SQL was created in 2007A SP01 PL05. This is a rather simple SQL which lists out all of the Bills of Material associated with a specific product, or the SQL can list out all products and their assoicated Bills of Material. You can see from the WHERE clause that the End User can enter an individual product code or leave the Selection field blank and press the "Enter" key to get a list of all products associated with a Bill of Material. This LIKE command can actually be used to look at a range of product codes by just entering a part of the item code - that is if the items were set up with a specific beginning portion of the item code meaning a group of products (i.e., "IBM" is the start of each product code coming from IBM). Try different ways and you will see this type of WHERE clause might be helpful in the future for some of your work. 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 Components within Bills of Material for Any or All Ver 1 ZP 2011 02 14
--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.
--Version 1 Zal Parchem 2011 02 14
T0.Code AS 'BOM Component',
T2.ItemName AS 'BOM Comp Description',
T0.Father AS 'BOM Product Num',
T0.Quantity AS 'Comp Qty',
T0.Price AS 'Comp Cost',
T0.PriceList AS 'Comp Price List',
T1.ListName AS 'Price List Name'
FROM ITT1 T0
LEFT OUTER JOIN OPLN T1
ON T0.PriceList = T1.ListNum
LEFT OUTER JOIN OITM T2
ON T0.Code = T2.ItemCode
(T0.Code LIKE '%%[%1]%%' OR '[%1]' = ' ')