VIEW: APPS.MSD_DEM_MODEL_BOM_COMPONENTS_V

Source

View Text - Preformatted

SELECT /*+ ORDERED */ mc.sr_instance_id || '::' || mc.sr_organization_id || '::' || mc.component_code cto_code, decode (mc.is_base_model, 1, mc.parent_component_code, mc.parent_component_code || '-' || mc.component_item_id) cto_child_code, mc.parent_component_code cto_parent_code, mi_bm.item_name base_model_code, mi_pi.item_name parent_item_code, mi_op.item_name option_code, mtp.organization_code org_code, mc.effectivity_date effectivity_date, mc.disable_date disable_date, mc.plng_pct_existing plng_pct_existing, DECODE (mc.is_base_model, 1, msd_dem_common_utilities.get_dem_cto_base_model, 2, msd_dem_common_utilities.get_dem_cto_option_class, msd_dem_common_utilities.get_dem_cto_option) cto_type, mi_bm.inventory_item_id, mc.top_ato_model_id , mi_pi.inventory_item_id, mi_op.inventory_item_id, mi_bm.sr_inventory_item_id, mi_pi.sr_inventory_item_id, mi_op.sr_inventory_item_id, DECODE (mc.is_base_model, 1, to_number(NULL), DECODE(mc.planning_factor, 0, 0, mc.plng_pct_existing *100 /mc.planning_factor)), mc.optional_flag, nvl(mi_op.ato_forecast_control,3) ato_forecast_control FROM (SELECT fnd_profile.value('MSD_DEM_PLANNING_PERCENTAGE') profile_value FROM dual ) mdpp, msd_dem_model_bom_components mc, msc_system_items mi_bm, msc_system_items mi_pi, msc_system_items mi_op, msc_trading_partners mtp WHERE mdpp.profile_value <> 2 AND mi_bm.plan_id = -1 AND mi_bm.sr_instance_id = mc.sr_instance_id AND mi_bm.organization_id = mc.sr_organization_id AND mi_bm.inventory_item_id = mc.base_model_id AND mi_pi.plan_id = -1 AND mi_pi.sr_instance_id = mc.sr_instance_id AND mi_pi.organization_id = mc.sr_organization_id AND mi_pi.inventory_item_id = mc.parent_item_id AND mi_op.plan_id = -1 AND mi_op.sr_instance_id = mc.sr_instance_id AND mi_op.organization_id = mc.sr_organization_id AND mi_op.inventory_item_id = mc.component_item_id AND mtp.partner_type = 3 AND mtp.sr_instance_id = mc.sr_instance_id AND mtp.sr_tp_id = mc.sr_organization_id UNION ALL SELECT /*+ ORDERED */ iv.sr_instance_id || '::' || iv.sr_organization_id || '::' || iv.component_code_s cto_code, decode (iv.is_base_model, 1, iv.parent_component_code, iv.parent_component_code || '-' || iv.component_item_id) cto_child_code, iv.parent_component_code cto_parent_code, mi_bm.item_name base_model_code, mi_pi.item_name parent_item_code, mi_op.item_name option_code, mtp.organization_code org_code, iv.effectivity_date effectivity_date, iv.disable_date disable_date, iv.plng_pct_existing plng_pct_existing, DECODE (iv.is_base_model, 1, msd_dem_common_utilities.get_dem_cto_base_model, 2, msd_dem_common_utilities.get_dem_cto_option_class, msd_dem_common_utilities.get_dem_cto_option) cto_type, mi_bm.inventory_item_id, iv.top_ato_model_id top_ato_model_id, mi_pi.inventory_item_id, mi_op.inventory_item_id, mi_bm.sr_inventory_item_id, mi_pi.sr_inventory_item_id, mi_op.sr_inventory_item_id, iv.component_qty, iv.optional_flag, nvl(mi_op.ato_forecast_control,3) ato_forecast_control FROM (SELECT sr_instance_id, sr_organization_id, base_model_id, parent_item_id_s, component_item_id, component_code_s, parent_component_code, MIN(top_ato_model_id)top_ato_model_id, MIN(effectivity_date_s) effectivity_date, MAX(disable_date_s) disable_date, SUM(plng_pct_existing_s) plng_pct_existing, MIN(is_base_model) is_base_model, DECODE (MIN(is_base_model), 1, to_number(NULL), SUM(DECODE(planning_factor_s, 0, 0, plng_pct_existing_s *100 /planning_factor_s))) component_qty, MIN(optional_flag_s) optional_flag FROM (SELECT fnd_profile.value('MSD_DEM_PLANNING_PERCENTAGE') profile_value FROM dual ) mdpp, msd_dem_model_bom_components WHERE mdpp.profile_value = 2 AND is_in = 1 GROUP BY sr_instance_id, sr_organization_id, base_model_id, parent_item_id_s, component_item_id, component_code_s, parent_component_code ) iv, msc_system_items mi_bm, msc_system_items mi_pi, msc_system_items mi_op, msc_trading_partners mtp WHERE mi_bm.plan_id = -1 AND mi_bm.sr_instance_id = iv.sr_instance_id AND mi_bm.organization_id = iv.sr_organization_id AND mi_bm.inventory_item_id = iv.base_model_id AND mi_pi.plan_id = -1 AND mi_pi.sr_instance_id = iv.sr_instance_id AND mi_pi.organization_id = iv.sr_organization_id AND mi_pi.inventory_item_id = iv.parent_item_id_s AND mi_op.plan_id = -1 AND mi_op.sr_instance_id = iv.sr_instance_id AND mi_op.organization_id = iv.sr_organization_id AND mi_op.inventory_item_id = iv.component_item_id AND mtp.partner_type = 3 AND mtp.sr_instance_id = iv.sr_instance_id AND mtp.sr_tp_id = iv.sr_organization_id

View Text - HTML Formatted

SELECT /*+ ORDERED */ MC.SR_INSTANCE_ID || '::' || MC.SR_ORGANIZATION_ID || '::' || MC.COMPONENT_CODE CTO_CODE
, DECODE (MC.IS_BASE_MODEL
, 1
, MC.PARENT_COMPONENT_CODE
, MC.PARENT_COMPONENT_CODE || '-' || MC.COMPONENT_ITEM_ID) CTO_CHILD_CODE
, MC.PARENT_COMPONENT_CODE CTO_PARENT_CODE
, MI_BM.ITEM_NAME BASE_MODEL_CODE
, MI_PI.ITEM_NAME PARENT_ITEM_CODE
, MI_OP.ITEM_NAME OPTION_CODE
, MTP.ORGANIZATION_CODE ORG_CODE
, MC.EFFECTIVITY_DATE EFFECTIVITY_DATE
, MC.DISABLE_DATE DISABLE_DATE
, MC.PLNG_PCT_EXISTING PLNG_PCT_EXISTING
, DECODE (MC.IS_BASE_MODEL
, 1
, MSD_DEM_COMMON_UTILITIES.GET_DEM_CTO_BASE_MODEL
, 2
, MSD_DEM_COMMON_UTILITIES.GET_DEM_CTO_OPTION_CLASS
, MSD_DEM_COMMON_UTILITIES.GET_DEM_CTO_OPTION) CTO_TYPE
, MI_BM.INVENTORY_ITEM_ID
, MC.TOP_ATO_MODEL_ID
, MI_PI.INVENTORY_ITEM_ID
, MI_OP.INVENTORY_ITEM_ID
, MI_BM.SR_INVENTORY_ITEM_ID
, MI_PI.SR_INVENTORY_ITEM_ID
, MI_OP.SR_INVENTORY_ITEM_ID
, DECODE (MC.IS_BASE_MODEL
, 1
, TO_NUMBER(NULL)
, DECODE(MC.PLANNING_FACTOR
, 0
, 0
, MC.PLNG_PCT_EXISTING *100 /MC.PLANNING_FACTOR))
, MC.OPTIONAL_FLAG
, NVL(MI_OP.ATO_FORECAST_CONTROL
, 3) ATO_FORECAST_CONTROL
FROM (SELECT FND_PROFILE.VALUE('MSD_DEM_PLANNING_PERCENTAGE') PROFILE_VALUE
FROM DUAL ) MDPP
, MSD_DEM_MODEL_BOM_COMPONENTS MC
, MSC_SYSTEM_ITEMS MI_BM
, MSC_SYSTEM_ITEMS MI_PI
, MSC_SYSTEM_ITEMS MI_OP
, MSC_TRADING_PARTNERS MTP
WHERE MDPP.PROFILE_VALUE <> 2
AND MI_BM.PLAN_ID = -1
AND MI_BM.SR_INSTANCE_ID = MC.SR_INSTANCE_ID
AND MI_BM.ORGANIZATION_ID = MC.SR_ORGANIZATION_ID
AND MI_BM.INVENTORY_ITEM_ID = MC.BASE_MODEL_ID
AND MI_PI.PLAN_ID = -1
AND MI_PI.SR_INSTANCE_ID = MC.SR_INSTANCE_ID
AND MI_PI.ORGANIZATION_ID = MC.SR_ORGANIZATION_ID
AND MI_PI.INVENTORY_ITEM_ID = MC.PARENT_ITEM_ID
AND MI_OP.PLAN_ID = -1
AND MI_OP.SR_INSTANCE_ID = MC.SR_INSTANCE_ID
AND MI_OP.ORGANIZATION_ID = MC.SR_ORGANIZATION_ID
AND MI_OP.INVENTORY_ITEM_ID = MC.COMPONENT_ITEM_ID
AND MTP.PARTNER_TYPE = 3
AND MTP.SR_INSTANCE_ID = MC.SR_INSTANCE_ID
AND MTP.SR_TP_ID = MC.SR_ORGANIZATION_ID UNION ALL SELECT /*+ ORDERED */ IV.SR_INSTANCE_ID || '::' || IV.SR_ORGANIZATION_ID || '::' || IV.COMPONENT_CODE_S CTO_CODE
, DECODE (IV.IS_BASE_MODEL
, 1
, IV.PARENT_COMPONENT_CODE
, IV.PARENT_COMPONENT_CODE || '-' || IV.COMPONENT_ITEM_ID) CTO_CHILD_CODE
, IV.PARENT_COMPONENT_CODE CTO_PARENT_CODE
, MI_BM.ITEM_NAME BASE_MODEL_CODE
, MI_PI.ITEM_NAME PARENT_ITEM_CODE
, MI_OP.ITEM_NAME OPTION_CODE
, MTP.ORGANIZATION_CODE ORG_CODE
, IV.EFFECTIVITY_DATE EFFECTIVITY_DATE
, IV.DISABLE_DATE DISABLE_DATE
, IV.PLNG_PCT_EXISTING PLNG_PCT_EXISTING
, DECODE (IV.IS_BASE_MODEL
, 1
, MSD_DEM_COMMON_UTILITIES.GET_DEM_CTO_BASE_MODEL
, 2
, MSD_DEM_COMMON_UTILITIES.GET_DEM_CTO_OPTION_CLASS
, MSD_DEM_COMMON_UTILITIES.GET_DEM_CTO_OPTION) CTO_TYPE
, MI_BM.INVENTORY_ITEM_ID
, IV.TOP_ATO_MODEL_ID TOP_ATO_MODEL_ID
, MI_PI.INVENTORY_ITEM_ID
, MI_OP.INVENTORY_ITEM_ID
, MI_BM.SR_INVENTORY_ITEM_ID
, MI_PI.SR_INVENTORY_ITEM_ID
, MI_OP.SR_INVENTORY_ITEM_ID
, IV.COMPONENT_QTY
, IV.OPTIONAL_FLAG
, NVL(MI_OP.ATO_FORECAST_CONTROL
, 3) ATO_FORECAST_CONTROL
FROM (SELECT SR_INSTANCE_ID
, SR_ORGANIZATION_ID
, BASE_MODEL_ID
, PARENT_ITEM_ID_S
, COMPONENT_ITEM_ID
, COMPONENT_CODE_S
, PARENT_COMPONENT_CODE
, MIN(TOP_ATO_MODEL_ID)TOP_ATO_MODEL_ID
, MIN(EFFECTIVITY_DATE_S) EFFECTIVITY_DATE
, MAX(DISABLE_DATE_S) DISABLE_DATE
, SUM(PLNG_PCT_EXISTING_S) PLNG_PCT_EXISTING
, MIN(IS_BASE_MODEL) IS_BASE_MODEL
, DECODE (MIN(IS_BASE_MODEL)
, 1
, TO_NUMBER(NULL)
, SUM(DECODE(PLANNING_FACTOR_S
, 0
, 0
, PLNG_PCT_EXISTING_S *100 /PLANNING_FACTOR_S))) COMPONENT_QTY
, MIN(OPTIONAL_FLAG_S) OPTIONAL_FLAG
FROM (SELECT FND_PROFILE.VALUE('MSD_DEM_PLANNING_PERCENTAGE') PROFILE_VALUE
FROM DUAL ) MDPP
, MSD_DEM_MODEL_BOM_COMPONENTS
WHERE MDPP.PROFILE_VALUE = 2
AND IS_IN = 1 GROUP BY SR_INSTANCE_ID
, SR_ORGANIZATION_ID
, BASE_MODEL_ID
, PARENT_ITEM_ID_S
, COMPONENT_ITEM_ID
, COMPONENT_CODE_S
, PARENT_COMPONENT_CODE ) IV
, MSC_SYSTEM_ITEMS MI_BM
, MSC_SYSTEM_ITEMS MI_PI
, MSC_SYSTEM_ITEMS MI_OP
, MSC_TRADING_PARTNERS MTP
WHERE MI_BM.PLAN_ID = -1
AND MI_BM.SR_INSTANCE_ID = IV.SR_INSTANCE_ID
AND MI_BM.ORGANIZATION_ID = IV.SR_ORGANIZATION_ID
AND MI_BM.INVENTORY_ITEM_ID = IV.BASE_MODEL_ID
AND MI_PI.PLAN_ID = -1
AND MI_PI.SR_INSTANCE_ID = IV.SR_INSTANCE_ID
AND MI_PI.ORGANIZATION_ID = IV.SR_ORGANIZATION_ID
AND MI_PI.INVENTORY_ITEM_ID = IV.PARENT_ITEM_ID_S
AND MI_OP.PLAN_ID = -1
AND MI_OP.SR_INSTANCE_ID = IV.SR_INSTANCE_ID
AND MI_OP.ORGANIZATION_ID = IV.SR_ORGANIZATION_ID
AND MI_OP.INVENTORY_ITEM_ID = IV.COMPONENT_ITEM_ID
AND MTP.PARTNER_TYPE = 3
AND MTP.SR_INSTANCE_ID = IV.SR_INSTANCE_ID
AND MTP.SR_TP_ID = IV.SR_ORGANIZATION_ID