eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.MSD_SR_PRD_PF_V

Source

View Text - Preformatted


SELECT /*+ LEADING(imo) */ DISTINCT child.inventory_item_id, child.concatenated_segments, child_tl.description, DECODE(parent.organization_id, morg.parameter_value, parent.inventory_item_id, msd_sr_util.get_null_pk), DECODE(parent.organization_id, morg.parameter_value, parent.concatenated_segments, msd_sr_util.get_null_desc), DECODE(parent.organization_id, morg.parameter_value, parent.description, msd_sr_util.get_null_desc), NULL, NULL, NULL, NULL, NULL FROM msd_setup_parameters imo, mtl_system_items_kfv child, mtl_system_items_tl child_tl, mtl_system_items_kfv parent, bom_inventory_components bicv, bom_bill_of_materials bbomv, msd_setup_parameters morg, msd_setup_parameters morg2, msd_setup_parameters msp, msd_app_instance_orgs vorgs WHERE imo.parameter_name = 'MSD_ITEM_ORG' AND imo.parameter_value = 'MSD_ALL_ORG' AND child.inventory_item_id = child_tl.inventory_item_id AND child.organization_id = child_tl.organization_id AND child_tl.LANGUAGE = userenv('LANG') AND msp.parameter_name = 'MSD_TWO_LEVEL_PLANNING' AND morg2.parameter_name = 'MSD_PLANNING_PERCENTAGE' AND DECODE(NVL(msp.parameter_value, '2'), '1', DECODE(NVL(child.ato_forecast_control, 3), 3, DECODE(NVL(parent.ato_forecast_control, 3), 3, DECODE(NVL(morg2.parameter_value, '1'), '4', msd_sr_util.is_item_optional_for_lvl(bicv.component_item_id), 2), 1), 1), DECODE(NVL(child.ato_forecast_control, 3), 3, DECODE(NVL(morg2.parameter_value, '1'), '4', msd_sr_util.is_item_optional_for_lvl(bicv.component_item_id), 2), 1)) = 1 AND DECODE(NVL(morg2.parameter_value, '1'), '3', DECODE(child.bom_item_type, 2, 2, 1), 1) = 1 AND parent.bom_item_type = 5 AND bbomv.organization_id = vorgs.organization_id AND bbomv.bill_sequence_id = bicv.bill_sequence_id AND bicv.component_item_id = child.inventory_item_id AND bbomv.organization_id = child.organization_id AND(child.mrp_planning_code <> 6 OR(child.mrp_planning_code = 6 AND child.pick_components_flag = 'Y')) AND bbomv.organization_id = parent.organization_id AND bbomv.assembly_item_id = parent.inventory_item_id AND NVL(parent.ato_forecast_control, 3) <> 3 AND parent.mrp_planning_code <> 6 AND morg.parameter_name = 'MSD_MASTER_ORG' AND((parent.organization_id = morg.parameter_value) OR(parent.organization_id <> morg.parameter_value AND NOT EXISTS (SELECT 1 FROM mtl_system_items_kfv child1, mtl_system_items_kfv parent1, bom_bill_of_materials bbomv1, bom_inventory_components bicv1, msd_setup_parameters morg1, msd_setup_parameters morg4, msd_setup_parameters msp1 WHERE msp1.parameter_name = 'MSD_TWO_LEVEL_PLANNING' AND parent1.bom_item_type = 5 AND bbomv1.bill_sequence_id = bicv1.bill_sequence_id AND bicv1.component_item_id = child1.inventory_item_id AND bbomv1.organization_id = child1.organization_id AND morg4.parameter_name = 'MSD_PLANNING_PERCENTAGE' AND DECODE(NVL(msp1.parameter_value, '2'), '1', DECODE(NVL(child1.ato_forecast_control, 3), 3, DECODE(NVL(parent1.ato_forecast_control, 3), 3, DECODE(NVL(morg4.parameter_value, '1'), '4', msd_sr_util.is_item_optional_for_lvl(bicv1.component_item_id), 2), 1), 1), DECODE(NVL(child1.ato_forecast_control, 3), 3, DECODE(NVL(morg4.parameter_value, '1'), '4', msd_sr_util.is_item_optional_for_lvl(bicv1.component_item_id), 2), 1)) = 1 AND DECODE(NVL(morg4.parameter_value, '1'), '3', DECODE(child1.bom_item_type, 2, 2, 1), 1) = 1 AND(child1.mrp_planning_code <> 6 OR(child1.mrp_planning_code = 6 AND child1.pick_components_flag = 'Y')) AND bbomv1.organization_id = parent1.organization_id AND bbomv1.assembly_item_id = parent1.inventory_item_id AND NVL(parent1.ato_forecast_control, 3) <> 3 AND parent1.mrp_planning_code <> 6 AND morg1.parameter_name = 'MSD_MASTER_ORG' AND parent1.organization_id = morg1.parameter_value AND child1.inventory_item_id = child.inventory_item_id ))) UNION ALL SELECT /*+ ORDERED */ child.inventory_item_id, child.concatenated_segments, child_tl.description, DECODE(parent.organization_id, morg.parameter_value, parent.inventory_item_id, msd_sr_util.get_null_pk), DECODE(parent.organization_id, morg.parameter_value, parent.concatenated_segments, msd_sr_util.get_null_desc), DECODE(parent.organization_id, morg.parameter_value, parent.description, msd_sr_util.get_null_desc), NULL, NULL, NULL, NULL, NULL FROM msd_setup_parameters imo, msd_setup_parameters morg, msd_setup_parameters morg2, msd_setup_parameters msp, mtl_system_items_kfv child, mtl_system_items_kfv parent, mtl_system_items_tl child_tl WHERE imo.parameter_name = 'MSD_ITEM_ORG' AND imo.parameter_value = 'MSD_MASTER_ORG' AND child.inventory_item_id = child_tl.inventory_item_id AND child.organization_id = child_tl.organization_id AND child_tl.language = USERENV('LANG') AND msp.parameter_name = 'MSD_TWO_LEVEL_PLANNING' AND morg2.parameter_name = 'MSD_PLANNING_PERCENTAGE' AND DECODE( NVL(msp.parameter_value, '2'), '1', DECODE(NVL(child.ato_forecast_control, 3), 3, DECODE( NVL(parent.ato_forecast_control, 3), 3, DECODE(NVL(morg2.parameter_value, '1'), '4', MSD_SR_UTIL.IS_ITEM_OPTIONAL_FOR_LVL (child.inventory_item_id, morg.parameter_value ), 2 ), 1), 1), DECODE( NVL(child.ato_forecast_control, 3), 3, DECODE(NVL(morg2.parameter_value, '1'), '4',MSD_SR_UTIL.IS_ITEM_OPTIONAL_FOR_LVL (child.inventory_item_id, morg.parameter_value ), 2 ), 1 ) ) = 1 AND DECODE( NVL(morg2.parameter_value, '1') , '3', DECODE( child.bom_item_type, 2, 2, 1), 1) = 1 AND parent.bom_item_type = 5 AND child.product_family_item_id = parent.inventory_item_id AND child.organization_id = morg.parameter_value AND parent.organization_id = child.organization_id AND ( child.mrp_planning_code <> 6 OR (child.mrp_planning_code = 6 AND child.pick_components_flag = 'Y')) AND NVL(parent.ato_forecast_control, 3) <> 3 AND parent.mrp_planning_code <> 6 AND morg.parameter_name = 'MSD_MASTER_ORG'

View Text - HTML Formatted

SELECT /*+ LEADING(IMO) */ DISTINCT CHILD.INVENTORY_ITEM_ID
, CHILD.CONCATENATED_SEGMENTS
, CHILD_TL.DESCRIPTION
, DECODE(PARENT.ORGANIZATION_ID
, MORG.PARAMETER_VALUE
, PARENT.INVENTORY_ITEM_ID
, MSD_SR_UTIL.GET_NULL_PK)
, DECODE(PARENT.ORGANIZATION_ID
, MORG.PARAMETER_VALUE
, PARENT.CONCATENATED_SEGMENTS
, MSD_SR_UTIL.GET_NULL_DESC)
, DECODE(PARENT.ORGANIZATION_ID
, MORG.PARAMETER_VALUE
, PARENT.DESCRIPTION
, MSD_SR_UTIL.GET_NULL_DESC)
, NULL
, NULL
, NULL
, NULL
, NULL
FROM MSD_SETUP_PARAMETERS IMO
, MTL_SYSTEM_ITEMS_KFV CHILD
, MTL_SYSTEM_ITEMS_TL CHILD_TL
, MTL_SYSTEM_ITEMS_KFV PARENT
, BOM_INVENTORY_COMPONENTS BICV
, BOM_BILL_OF_MATERIALS BBOMV
, MSD_SETUP_PARAMETERS MORG
, MSD_SETUP_PARAMETERS MORG2
, MSD_SETUP_PARAMETERS MSP
, MSD_APP_INSTANCE_ORGS VORGS
WHERE IMO.PARAMETER_NAME = 'MSD_ITEM_ORG'
AND IMO.PARAMETER_VALUE = 'MSD_ALL_ORG'
AND CHILD.INVENTORY_ITEM_ID = CHILD_TL.INVENTORY_ITEM_ID
AND CHILD.ORGANIZATION_ID = CHILD_TL.ORGANIZATION_ID
AND CHILD_TL.LANGUAGE = USERENV('LANG')
AND MSP.PARAMETER_NAME = 'MSD_TWO_LEVEL_PLANNING'
AND MORG2.PARAMETER_NAME = 'MSD_PLANNING_PERCENTAGE'
AND DECODE(NVL(MSP.PARAMETER_VALUE
, '2')
, '1'
, DECODE(NVL(CHILD.ATO_FORECAST_CONTROL
, 3)
, 3
, DECODE(NVL(PARENT.ATO_FORECAST_CONTROL
, 3)
, 3
, DECODE(NVL(MORG2.PARAMETER_VALUE
, '1')
, '4'
, MSD_SR_UTIL.IS_ITEM_OPTIONAL_FOR_LVL(BICV.COMPONENT_ITEM_ID)
, 2)
, 1)
, 1)
, DECODE(NVL(CHILD.ATO_FORECAST_CONTROL
, 3)
, 3
, DECODE(NVL(MORG2.PARAMETER_VALUE
, '1')
, '4'
, MSD_SR_UTIL.IS_ITEM_OPTIONAL_FOR_LVL(BICV.COMPONENT_ITEM_ID)
, 2)
, 1)) = 1
AND DECODE(NVL(MORG2.PARAMETER_VALUE
, '1')
, '3'
, DECODE(CHILD.BOM_ITEM_TYPE
, 2
, 2
, 1)
, 1) = 1
AND PARENT.BOM_ITEM_TYPE = 5
AND BBOMV.ORGANIZATION_ID = VORGS.ORGANIZATION_ID
AND BBOMV.BILL_SEQUENCE_ID = BICV.BILL_SEQUENCE_ID
AND BICV.COMPONENT_ITEM_ID = CHILD.INVENTORY_ITEM_ID
AND BBOMV.ORGANIZATION_ID = CHILD.ORGANIZATION_ID AND(CHILD.MRP_PLANNING_CODE <> 6 OR(CHILD.MRP_PLANNING_CODE = 6
AND CHILD.PICK_COMPONENTS_FLAG = 'Y'))
AND BBOMV.ORGANIZATION_ID = PARENT.ORGANIZATION_ID
AND BBOMV.ASSEMBLY_ITEM_ID = PARENT.INVENTORY_ITEM_ID
AND NVL(PARENT.ATO_FORECAST_CONTROL
, 3) <> 3
AND PARENT.MRP_PLANNING_CODE <> 6
AND MORG.PARAMETER_NAME = 'MSD_MASTER_ORG' AND((PARENT.ORGANIZATION_ID = MORG.PARAMETER_VALUE) OR(PARENT.ORGANIZATION_ID <> MORG.PARAMETER_VALUE
AND NOT EXISTS (SELECT 1
FROM MTL_SYSTEM_ITEMS_KFV CHILD1
, MTL_SYSTEM_ITEMS_KFV PARENT1
, BOM_BILL_OF_MATERIALS BBOMV1
, BOM_INVENTORY_COMPONENTS BICV1
, MSD_SETUP_PARAMETERS MORG1
, MSD_SETUP_PARAMETERS MORG4
, MSD_SETUP_PARAMETERS MSP1
WHERE MSP1.PARAMETER_NAME = 'MSD_TWO_LEVEL_PLANNING'
AND PARENT1.BOM_ITEM_TYPE = 5
AND BBOMV1.BILL_SEQUENCE_ID = BICV1.BILL_SEQUENCE_ID
AND BICV1.COMPONENT_ITEM_ID = CHILD1.INVENTORY_ITEM_ID
AND BBOMV1.ORGANIZATION_ID = CHILD1.ORGANIZATION_ID
AND MORG4.PARAMETER_NAME = 'MSD_PLANNING_PERCENTAGE'
AND DECODE(NVL(MSP1.PARAMETER_VALUE
, '2')
, '1'
, DECODE(NVL(CHILD1.ATO_FORECAST_CONTROL
, 3)
, 3
, DECODE(NVL(PARENT1.ATO_FORECAST_CONTROL
, 3)
, 3
, DECODE(NVL(MORG4.PARAMETER_VALUE
, '1')
, '4'
, MSD_SR_UTIL.IS_ITEM_OPTIONAL_FOR_LVL(BICV1.COMPONENT_ITEM_ID)
, 2)
, 1)
, 1)
, DECODE(NVL(CHILD1.ATO_FORECAST_CONTROL
, 3)
, 3
, DECODE(NVL(MORG4.PARAMETER_VALUE
, '1')
, '4'
, MSD_SR_UTIL.IS_ITEM_OPTIONAL_FOR_LVL(BICV1.COMPONENT_ITEM_ID)
, 2)
, 1)) = 1
AND DECODE(NVL(MORG4.PARAMETER_VALUE
, '1')
, '3'
, DECODE(CHILD1.BOM_ITEM_TYPE
, 2
, 2
, 1)
, 1) = 1 AND(CHILD1.MRP_PLANNING_CODE <> 6 OR(CHILD1.MRP_PLANNING_CODE = 6
AND CHILD1.PICK_COMPONENTS_FLAG = 'Y'))
AND BBOMV1.ORGANIZATION_ID = PARENT1.ORGANIZATION_ID
AND BBOMV1.ASSEMBLY_ITEM_ID = PARENT1.INVENTORY_ITEM_ID
AND NVL(PARENT1.ATO_FORECAST_CONTROL
, 3) <> 3
AND PARENT1.MRP_PLANNING_CODE <> 6
AND MORG1.PARAMETER_NAME = 'MSD_MASTER_ORG'
AND PARENT1.ORGANIZATION_ID = MORG1.PARAMETER_VALUE
AND CHILD1.INVENTORY_ITEM_ID = CHILD.INVENTORY_ITEM_ID ))) UNION ALL SELECT /*+ ORDERED */ CHILD.INVENTORY_ITEM_ID
, CHILD.CONCATENATED_SEGMENTS
, CHILD_TL.DESCRIPTION
, DECODE(PARENT.ORGANIZATION_ID
, MORG.PARAMETER_VALUE
, PARENT.INVENTORY_ITEM_ID
, MSD_SR_UTIL.GET_NULL_PK)
, DECODE(PARENT.ORGANIZATION_ID
, MORG.PARAMETER_VALUE
, PARENT.CONCATENATED_SEGMENTS
, MSD_SR_UTIL.GET_NULL_DESC)
, DECODE(PARENT.ORGANIZATION_ID
, MORG.PARAMETER_VALUE
, PARENT.DESCRIPTION
, MSD_SR_UTIL.GET_NULL_DESC)
, NULL
, NULL
, NULL
, NULL
, NULL
FROM MSD_SETUP_PARAMETERS IMO
, MSD_SETUP_PARAMETERS MORG
, MSD_SETUP_PARAMETERS MORG2
, MSD_SETUP_PARAMETERS MSP
, MTL_SYSTEM_ITEMS_KFV CHILD
, MTL_SYSTEM_ITEMS_KFV PARENT
, MTL_SYSTEM_ITEMS_TL CHILD_TL
WHERE IMO.PARAMETER_NAME = 'MSD_ITEM_ORG'
AND IMO.PARAMETER_VALUE = 'MSD_MASTER_ORG'
AND CHILD.INVENTORY_ITEM_ID = CHILD_TL.INVENTORY_ITEM_ID
AND CHILD.ORGANIZATION_ID = CHILD_TL.ORGANIZATION_ID
AND CHILD_TL.LANGUAGE = USERENV('LANG')
AND MSP.PARAMETER_NAME = 'MSD_TWO_LEVEL_PLANNING'
AND MORG2.PARAMETER_NAME = 'MSD_PLANNING_PERCENTAGE'
AND DECODE( NVL(MSP.PARAMETER_VALUE
, '2')
, '1'
, DECODE(NVL(CHILD.ATO_FORECAST_CONTROL
, 3)
, 3
, DECODE( NVL(PARENT.ATO_FORECAST_CONTROL
, 3)
, 3
, DECODE(NVL(MORG2.PARAMETER_VALUE
, '1')
, '4'
, MSD_SR_UTIL.IS_ITEM_OPTIONAL_FOR_LVL (CHILD.INVENTORY_ITEM_ID
, MORG.PARAMETER_VALUE )
, 2 )
, 1)
, 1)
, DECODE( NVL(CHILD.ATO_FORECAST_CONTROL
, 3)
, 3
, DECODE(NVL(MORG2.PARAMETER_VALUE
, '1')
, '4'
, MSD_SR_UTIL.IS_ITEM_OPTIONAL_FOR_LVL (CHILD.INVENTORY_ITEM_ID
, MORG.PARAMETER_VALUE )
, 2 )
, 1 ) ) = 1
AND DECODE( NVL(MORG2.PARAMETER_VALUE
, '1')
, '3'
, DECODE( CHILD.BOM_ITEM_TYPE
, 2
, 2
, 1)
, 1) = 1
AND PARENT.BOM_ITEM_TYPE = 5
AND CHILD.PRODUCT_FAMILY_ITEM_ID = PARENT.INVENTORY_ITEM_ID
AND CHILD.ORGANIZATION_ID = MORG.PARAMETER_VALUE
AND PARENT.ORGANIZATION_ID = CHILD.ORGANIZATION_ID
AND ( CHILD.MRP_PLANNING_CODE <> 6 OR (CHILD.MRP_PLANNING_CODE = 6
AND CHILD.PICK_COMPONENTS_FLAG = 'Y'))
AND NVL(PARENT.ATO_FORECAST_CONTROL
, 3) <> 3
AND PARENT.MRP_PLANNING_CODE <> 6
AND MORG.PARAMETER_NAME = 'MSD_MASTER_ORG'