Skip to end of metadata
Go to start of metadata

(Back to J-MR MRP Module SQL Posted to Wiki Listing - J-MR MRP Module SQL Posted to Wiki  )

TYPE: Monthly Special

AUTHOR NAME: Zal Parchem

PURPOSE AND BACKGROUND:  This SQL was created in 8.8 SP00 PL16.  Before actually using MRP, certain fields must be filled out completely.  This is a good example of an "Exception SQL" to help the End User populate missing date from a specific table before using that data in SAP B1.  This SQL displays only those product missing data that most consider the basic and minimum information required to run the MRP function found in SAP B1.  Please note the last part of the WHERE section which basically says to display any product not having all four fields completely populated.  Also note that some Item Groups are being excluded - you will need to change that by looking at the OITB (Items Group Table) before using this SQL.  When the End User had completely filled out all four fields in their system, we turned the SQL into an Alert Message with fewer fields being displayed in the Alert Message.  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:

J-MR Minimum Data Check for MRP Completeness.png

Copy and Paste this SQL:

--J-MR Minimum Data Check for MRP Completeness Ver 1 ZP 2011 02 12

--DESCRIPTION: SQL displays those inventoried items purchased and set for MRP which do not have a preferred vendor, a minimum inventory level, a minimum order quantity, or a lead time in specific groups.  This is an exception report which can later be used as an Alert Message once all MRP items are set appropriately.

--Version 1 Zal Parchem 2011 02 12


T1.ItmsGrpCod AS 'Group Code',
T1.ItmsGrpNam AS 'Group Name',
T0.PlaningSys AS 'MRP?',
T0.PrcrmntMtd AS 'Buy?', 
T0.CardCode AS 'Pref Vend',
T0.MinLevel AS 'Min Inv Level',

T0.MinOrdrQty AS 'Min Order',


ON T0.ItmsGrpCod = T1.ItmsGrpCod


T0.InvntItem = 'Y'
AND  T0.PrchseItem = 'Y'
AND  T0.ItmsGrpCod <> 108
AND  T0.ItmsGrpCod <> 116
AND  T0.ItmsGrpCod <> 102

AND (T0.LeadTime = 0
OR T0.MinOrdrQty = 0
OR T0.MinLevel = 0
OR T0.CardCode IS NULL)


  • No labels