[Home] [Help]
View: MRP_I2_SAFETY_STOCK_V
| Product: | MRP - Master Scheduling/MRP |
|---|
| Description: | A Rhythm view supporting information regarding safety stock quantities |
|---|
| Implementation/DBA Data: |
Not implemented in this database
|
|---|
View Text
SELECT I.PART_NUMBER
, I.INVENTORY_ITEM_ID
, S.SAFETY_STOCK_QUANTITY
, S.ORGANIZATION_ID
, GREATEST(S.EFFECTIVITY_DATE
, MRP_I2_ERP.GET_START_DATE(S.ORGANIZATION_ID) + I.LEADTIME + I.POSTPROCESSING_LEADTIME)
, SUBSTR( MRP_I2_ERP.GET_PARAMETER('MRP_I2_L_DEMAND_PREFIX'
, S.ORGANIZATION_ID
, 3) || MRP_I2_ERP.GET_PARAMETER( 'MRP_I2_P_DELIMITER'
, S.ORGANIZATION_ID) || I.PART_NUMBER || MRP_I2_ERP.GET_PARAMETER('MRP_I2_P_DELIMITER'
, S.ORGANIZATION_ID) || TO_CHAR( GREATEST(S.EFFECTIVITY_DATE
, MRP_I2_ERP.GET_START_DATE(S.ORGANIZATION_ID) + I.LEADTIME + I.POSTPROCESSING_LEADTIME))
, 1
, 240)
, MRP_I2.CALCULATE_PRIORITY('SAFETY_STOCK'
, GREATEST(S.EFFECTIVITY_DATE
, MRP_I2_ERP.GET_START_DATE(S.ORGANIZATION_ID) + I.LEADTIME + I.POSTPROCESSING_LEADTIME)
, S.ORGANIZATION_ID)
FROM MRP_RHX_SAFETY_STOCK_V S
, MRP_I2_PART_MASTER_V I
WHERE S.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
AND S.ORGANIZATION_ID = I.ORGANIZATION_ID
AND S.EFFECTIVITY_DATE < MRP_I2_ERP.GET_START_DATE(S.ORGANIZATION_ID) + I.LEADTIME + I.POSTPROCESSING_LEADTIME
AND (S.INVENTORY_ITEM_ID
, S.ORGANIZATION_ID
, S.EFFECTIVITY_DATE) IN (SELECT S1.INVENTORY_ITEM_ID
, S1.ORGANIZATION_ID
, MAX(S1.EFFECTIVITY_DATE)
FROM MRP_RHX_SAFETY_STOCK_V S1
WHERE S1.INVENTORY_ITEM_ID = S.INVENTORY_ITEM_ID
AND S1.ORGANIZATION_ID = S.ORGANIZATION_ID
AND S1.EFFECTIVITY_DATE < MRP_I2_ERP.GET_START_DATE(S.ORGANIZATION_ID) + I.LEADTIME + I.POSTPROCESSING_LEADTIME
AND MRP_I2_ERP.GET_PARAMETER('SCHEDULING_ONLY'
, I.ORGANIZATION_ID) <> 'Y' GROUP BY S1.INVENTORY_ITEM_ID
, S1.ORGANIZATION_ID) UNION ALL SELECT I.PART_NUMBER
, I.INVENTORY_ITEM_ID
, S.SAFETY_STOCK_QUANTITY
, S.ORGANIZATION_ID
, S.EFFECTIVITY_DATE
, SUBSTR( MRP_I2_ERP.GET_PARAMETER('MRP_I2_L_DEMAND_PREFIX'
, S.ORGANIZATION_ID
, 3) || MRP_I2_ERP.GET_PARAMETER('MRP_I2_P_DELIMITER'
, S.ORGANIZATION_ID) || I.PART_NUMBER || MRP_I2_ERP.GET_PARAMETER('MRP_I2_P_DELIMITER'
, S.ORGANIZATION_ID) || TO_CHAR(EFFECTIVITY_DATE)
, 1
, 240)
, MRP_I2.CALCULATE_PRIORITY('SAFETY_STOCK'
, S.EFFECTIVITY_DATE
, S.ORGANIZATION_ID)
FROM MRP_RHX_SAFETY_STOCK_V S
, MRP_I2_PART_MASTER_V I
WHERE S.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
AND S.ORGANIZATION_ID = I.ORGANIZATION_ID
AND S.EFFECTIVITY_DATE >= MRP_I2_ERP.GET_START_DATE(S.ORGANIZATION_ID) + I.LEADTIME + I.POSTPROCESSING_LEADTIME
AND (S.INVENTORY_ITEM_ID
, S.ORGANIZATION_ID
, S.EFFECTIVITY_DATE) IN (SELECT S1.INVENTORY_ITEM_ID
, S1.ORGANIZATION_ID
, MIN(S1.EFFECTIVITY_DATE)
FROM MRP_RHX_SAFETY_STOCK_V S1
WHERE S1.INVENTORY_ITEM_ID = S.INVENTORY_ITEM_ID
AND S1.ORGANIZATION_ID = S.ORGANIZATION_ID
AND S1.EFFECTIVITY_DATE >= MRP_I2_ERP.GET_START_DATE(S.ORGANIZATION_ID) + I.LEADTIME + I.POSTPROCESSING_LEADTIME GROUP BY S1.INVENTORY_ITEM_ID
, S1.ORGANIZATION_ID)
AND NOT EXISTS (SELECT NULL
FROM MRP_RHX_SAFETY_STOCK_V S1
WHERE S1.INVENTORY_ITEM_ID = S.INVENTORY_ITEM_ID
AND S1.ORGANIZATION_ID = S.ORGANIZATION_ID
AND S1.EFFECTIVITY_DATE < MRP_I2_ERP.GET_START_DATE(S.ORGANIZATION_ID) + I.LEADTIME + I.POSTPROCESSING_LEADTIME)
AND MRP_I2_ERP.GET_PARAMETER('SCHEDULING_ONLY'
, I.ORGANIZATION_ID) <> 'Y'
Columns
| Name |
| PART_NUMBER |
| INVENTORY_ITEM_ID |
| SAFETY_STOCK_QUANTITY |
| ORGANIZATION_ID |
| EFFECTIVITY_DATE |
| ORDER_NUMBER |
| PRIORITY |