VIEW: APPS.MSD_SR_PRD_ORG_V

Source

View Text - Preformatted

SELECT /*+ INDEX (itm MTL_SYSTEM_ITEMS_B_N13) */ itm.inventory_item_id, 7, itm.organization_id FROM msd_app_instance_orgs vorg, mtl_system_items_kfv itm, msd_setup_parameters msp, msd_setup_parameters msp2, msd_setup_parameters morg2 WHERE morg2.parameter_name = 'MSD_MASTER_ORG' AND msp.parameter_name = 'MSD_TWO_LEVEL_PLANNING' AND msp2.parameter_name = 'MSD_PLANNING_PERCENTAGE' AND DECODE( NVL(msp.parameter_value, '2'), '1', DECODE(NVL(itm.ato_forecast_control,3), 3, DECODE(NVL(msp2.parameter_value, '1'), '4', DECODE(itm.bom_item_type, 2, 1, msd_sr_util.is_pf_forecastable(morg2.parameter_value, itm.inventory_item_id, itm.product_family_item_id, 1,2) ), msd_sr_util.is_pf_forecastable(morg2.parameter_value, itm.inventory_item_id, itm.product_family_item_id, 2,2) ), 1), DECODE( NVL(itm.ato_forecast_control, 3), 3, DECODE(NVL(msp2.parameter_value,'1'), '4', DECODE(itm.bom_item_type, 2, 1, MSD_SR_UTIL.IS_ITEM_OPTIONAL_FOR_LVL(itm.inventory_item_id ) ), 2), 1) ) = 1 AND DECODE( NVL(msp2.parameter_value, '1') , '3', DECODE( itm.bom_item_type, 2, 2, 1), 1) = 1 AND itm.organization_id = vorg.organization_id AND (itm.mrp_planning_code <> 6 OR (itm.mrp_planning_code = 6 AND itm.pick_components_flag = 'Y'))

View Text - HTML Formatted

SELECT /*+ INDEX (ITM MTL_SYSTEM_ITEMS_B_N13) */ ITM.INVENTORY_ITEM_ID
, 7
, ITM.ORGANIZATION_ID
FROM MSD_APP_INSTANCE_ORGS VORG
, MTL_SYSTEM_ITEMS_KFV ITM
, MSD_SETUP_PARAMETERS MSP
, MSD_SETUP_PARAMETERS MSP2
, MSD_SETUP_PARAMETERS MORG2
WHERE MORG2.PARAMETER_NAME = 'MSD_MASTER_ORG'
AND MSP.PARAMETER_NAME = 'MSD_TWO_LEVEL_PLANNING'
AND MSP2.PARAMETER_NAME = 'MSD_PLANNING_PERCENTAGE'
AND DECODE( NVL(MSP.PARAMETER_VALUE
, '2')
, '1'
, DECODE(NVL(ITM.ATO_FORECAST_CONTROL
, 3)
, 3
, DECODE(NVL(MSP2.PARAMETER_VALUE
, '1')
, '4'
, DECODE(ITM.BOM_ITEM_TYPE
, 2
, 1
, MSD_SR_UTIL.IS_PF_FORECASTABLE(MORG2.PARAMETER_VALUE
, ITM.INVENTORY_ITEM_ID
, ITM.PRODUCT_FAMILY_ITEM_ID
, 1
, 2) )
, MSD_SR_UTIL.IS_PF_FORECASTABLE(MORG2.PARAMETER_VALUE
, ITM.INVENTORY_ITEM_ID
, ITM.PRODUCT_FAMILY_ITEM_ID
, 2
, 2) )
, 1)
, DECODE( NVL(ITM.ATO_FORECAST_CONTROL
, 3)
, 3
, DECODE(NVL(MSP2.PARAMETER_VALUE
, '1')
, '4'
, DECODE(ITM.BOM_ITEM_TYPE
, 2
, 1
, MSD_SR_UTIL.IS_ITEM_OPTIONAL_FOR_LVL(ITM.INVENTORY_ITEM_ID ) )
, 2)
, 1) ) = 1
AND DECODE( NVL(MSP2.PARAMETER_VALUE
, '1')
, '3'
, DECODE( ITM.BOM_ITEM_TYPE
, 2
, 2
, 1)
, 1) = 1
AND ITM.ORGANIZATION_ID = VORG.ORGANIZATION_ID
AND (ITM.MRP_PLANNING_CODE <> 6 OR (ITM.MRP_PLANNING_CODE = 6
AND ITM.PICK_COMPONENTS_FLAG = 'Y'))