(Back to E-PU Purchasing AP Module SQL Posted to Wiki Listing - E-PU Purchasing AP Module SQL Posted to Wiki )
TYPE: Monthly Special
AUTHOR NAME: Zal Parchem
PURPOSE AND BACKGROUND: This SQL was created in 8.8 SP00 PL16. This SQL shows the most important items required by the End User in keeping the production line going (done by the selection of the item groups which you will need to change in your own company's SQL in the WHERE section). The SQL searches out and displays those products which have fallen below Minimum Inventory Level based upon an "Available" calculation and suggests what quantity should be ordered in the "To Purchase" column. Highlighted is a CASE function showing how to select what quantity to post in a column based upon a calcuation which might come in handy for future usage. The CASE function shows different quantities depending upon whether the calculation is greater or less than Minimum Order Quantity. Complete a good analysis of the attached screen print and you will see how this functions. This SQL can also be turned into an Alert Message (depending upon the End User Purchase Review schedule). 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:
--E-PU Priority Items To Be Purchased Ver 1 ZP 2011 02 09
--DESCRIPTION: SQL provides a suggested quantity to be ordered by considering the minimum level and other buckets in Inventory Master Data window. This SQL reports on specific product groups which are critical - for example only raw material which impacts production.
--Version 1 Zal Parchem 09 Feb 2011
T0.CardCode AS 'Pref Vend',
T2.CardName AS 'Pref Vend Name',
T0.ItemCode AS 'Item Code',
T0.ItemName AS 'Item Description',
T1.ItmsGrpNam AS 'Item Group',
T0.LeadTime AS 'Lead',
WHEN T0.MinLevel - (T0.OnHand + T0.OnOrder - T0.IsCommited) > T0.MinOrdrQty
THEN T0.MinLevel - (T0.OnHand + T0.OnOrder - T0.IsCommited)
WHEN T0.MinLevel - (T0.OnHand + T0.OnOrder - T0.IsCommited) < T0.MinOrdrQty
END AS 'To Purch',
T0.MinOrdrQty AS 'Min Ord',
T0.DfltWH AS 'Whs',
T0.MinLevel AS 'Min Inv',
T0.PrchseItem AS 'Buy',
T0.OnHand AS 'On Hand',
T0.OnOrder AS 'On Order',
T0.IsCommited AS 'Committed'
FROM OITM T0
LEFT OUTER JOIN OITB T1
ON T0.ItmsGrpCod = T1.ItmsGrpCod
LEFT OUTER JOIN OCRD T2
ON T0.CardCode = T2.CardCode
T0.InvntItem = 'Y'
AND T0.PrchseItem = 'Y'
AND T0.MinLevel - (T0.OnHand + T0.OnOrder - T0.IsCommited) > 0
AND T0.ItmsGrpCod <> 108