VIEW: APPS.MSD_SR_PF_ALL_V

Source

View Text - Preformatted

SELECT distinct to_char(msiv.inventory_item_id), msiv.concatenated_segments, msiv_tl.description, to_char(MSD_SR_UTIL.get_all_prd_pk), MSD_SR_UTIL.get_all_prd_desc, MSD_SR_UTIL.get_all_prd_desc, null, null, null, null, null, flv.meaning, null, null from fnd_lookup_values_vl flv, mtl_system_items_kfv msiv, mtl_system_items_tl msiv_tl, bom_bill_of_materials_v bbomv WHERE FLV.LOOKUP_TYPE='MSD_LEVEL_VALUE_DESC' AND FLV.LOOKUP_CODE='P' and msiv.BOM_ITEM_TYPE = 5 and msiv.inventory_item_id = msiv_tl.inventory_item_id and msiv.organization_id = msiv_tl.organization_id and msiv_tl.language = USERENV('LANG') and msiv.ato_forecast_control <> 3 and msiv.mrp_planning_code <> 6 and msiv.inventory_item_id = bbomv.assembly_item_id and msiv.organization_id = bbomv.organization_id /* Bug# 4157588 */ AND NOT EXISTS (SELECT 1 FROM msd_setup_parameters WHERE parameter_name = 'MSD_ITEM_ORG' AND parameter_value = 'MSD_MASTER_ORG') /* Bug# 4157588 */ UNION ALL select to_char(msd_sr_util.get_null_pk), msd_sr_util.get_null_desc, msd_sr_util.get_null_desc, to_char(MSD_SR_UTIL.get_all_prd_pk), MSD_SR_UTIL.get_all_prd_desc, MSD_SR_UTIL.get_all_prd_desc, null, null, null, null, null, null, null, null from dual /* Bug# 4157588 */ UNION ALL SELECT /*+ ORDERED */ DISTINCT to_char(msiv.inventory_item_id), msiv.concatenated_segments, msiv_tl.description, to_char(msd_sr_util.get_all_prd_pk), msd_sr_util.get_all_prd_desc, msd_sr_util.get_all_prd_desc, NULL, NULL, NULL, NULL, NULL, flv.meaning, NULL, NULL FROM fnd_lookup_values_vl flv, msd_setup_parameters morg, mtl_system_items_kfv msiv, bom_bill_of_materials bbomv, mtl_system_items_tl msiv_tl WHERE flv.lookup_type = 'MSD_LEVEL_VALUE_DESC' AND flv.lookup_code = 'P' AND EXISTS (SELECT 1 FROM msd_setup_parameters WHERE parameter_name = 'MSD_ITEM_ORG' AND parameter_value = 'MSD_MASTER_ORG') AND msiv.bom_item_type = 5 AND msiv.inventory_item_id = msiv_tl.inventory_item_id AND msiv.organization_id = msiv_tl.organization_id AND msiv_tl.LANGUAGE = userenv('LANG') AND msiv.ato_forecast_control <> 3 AND msiv.mrp_planning_code <> 6 AND msiv.inventory_item_id = bbomv.assembly_item_id AND msiv.organization_id = bbomv.organization_id AND morg.parameter_name = 'MSD_MASTER_ORG' AND morg.parameter_value = msiv.organization_id

View Text - HTML Formatted

SELECT DISTINCT TO_CHAR(MSIV.INVENTORY_ITEM_ID)
, MSIV.CONCATENATED_SEGMENTS
, MSIV_TL.DESCRIPTION
, TO_CHAR(MSD_SR_UTIL.GET_ALL_PRD_PK)
, MSD_SR_UTIL.GET_ALL_PRD_DESC
, MSD_SR_UTIL.GET_ALL_PRD_DESC
, NULL
, NULL
, NULL
, NULL
, NULL
, FLV.MEANING
, NULL
, NULL
FROM FND_LOOKUP_VALUES_VL FLV
, MTL_SYSTEM_ITEMS_KFV MSIV
, MTL_SYSTEM_ITEMS_TL MSIV_TL
, BOM_BILL_OF_MATERIALS_V BBOMV
WHERE FLV.LOOKUP_TYPE='MSD_LEVEL_VALUE_DESC'
AND FLV.LOOKUP_CODE='P'
AND MSIV.BOM_ITEM_TYPE = 5
AND MSIV.INVENTORY_ITEM_ID = MSIV_TL.INVENTORY_ITEM_ID
AND MSIV.ORGANIZATION_ID = MSIV_TL.ORGANIZATION_ID
AND MSIV_TL.LANGUAGE = USERENV('LANG')
AND MSIV.ATO_FORECAST_CONTROL <> 3
AND MSIV.MRP_PLANNING_CODE <> 6
AND MSIV.INVENTORY_ITEM_ID = BBOMV.ASSEMBLY_ITEM_ID
AND MSIV.ORGANIZATION_ID = BBOMV.ORGANIZATION_ID /* BUG# 4157588 */
AND NOT EXISTS (SELECT 1
FROM MSD_SETUP_PARAMETERS
WHERE PARAMETER_NAME = 'MSD_ITEM_ORG'
AND PARAMETER_VALUE = 'MSD_MASTER_ORG') /* BUG# 4157588 */ UNION ALL SELECT TO_CHAR(MSD_SR_UTIL.GET_NULL_PK)
, MSD_SR_UTIL.GET_NULL_DESC
, MSD_SR_UTIL.GET_NULL_DESC
, TO_CHAR(MSD_SR_UTIL.GET_ALL_PRD_PK)
, MSD_SR_UTIL.GET_ALL_PRD_DESC
, MSD_SR_UTIL.GET_ALL_PRD_DESC
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
FROM DUAL /* BUG# 4157588 */ UNION ALL SELECT /*+ ORDERED */ DISTINCT TO_CHAR(MSIV.INVENTORY_ITEM_ID)
, MSIV.CONCATENATED_SEGMENTS
, MSIV_TL.DESCRIPTION
, TO_CHAR(MSD_SR_UTIL.GET_ALL_PRD_PK)
, MSD_SR_UTIL.GET_ALL_PRD_DESC
, MSD_SR_UTIL.GET_ALL_PRD_DESC
, NULL
, NULL
, NULL
, NULL
, NULL
, FLV.MEANING
, NULL
, NULL
FROM FND_LOOKUP_VALUES_VL FLV
, MSD_SETUP_PARAMETERS MORG
, MTL_SYSTEM_ITEMS_KFV MSIV
, BOM_BILL_OF_MATERIALS BBOMV
, MTL_SYSTEM_ITEMS_TL MSIV_TL
WHERE FLV.LOOKUP_TYPE = 'MSD_LEVEL_VALUE_DESC'
AND FLV.LOOKUP_CODE = 'P'
AND EXISTS (SELECT 1
FROM MSD_SETUP_PARAMETERS
WHERE PARAMETER_NAME = 'MSD_ITEM_ORG'
AND PARAMETER_VALUE = 'MSD_MASTER_ORG')
AND MSIV.BOM_ITEM_TYPE = 5
AND MSIV.INVENTORY_ITEM_ID = MSIV_TL.INVENTORY_ITEM_ID
AND MSIV.ORGANIZATION_ID = MSIV_TL.ORGANIZATION_ID
AND MSIV_TL.LANGUAGE = USERENV('LANG')
AND MSIV.ATO_FORECAST_CONTROL <> 3
AND MSIV.MRP_PLANNING_CODE <> 6
AND MSIV.INVENTORY_ITEM_ID = BBOMV.ASSEMBLY_ITEM_ID
AND MSIV.ORGANIZATION_ID = BBOMV.ORGANIZATION_ID
AND MORG.PARAMETER_NAME = 'MSD_MASTER_ORG'
AND MORG.PARAMETER_VALUE = MSIV.ORGANIZATION_ID