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: 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:

 I-PR Components within Bills of Material for Any or All.png

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.

--AUTHOR(s):
--Version 1 Zal Parchem 2011 02 14

SELECT

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

WHERE

(T0.Code LIKE '%%[%1]%%' OR '[%1]' = ' ')

ORDER BY

T0.Code,
T0.Father

FOR BROWSE

  • No labels