VIEW: APPS.MSD_EOL_BOM_COMP_V

Source

View Text - Preformatted

SELECT a.instance SR_INSTANCE_ID, a.demand_plan_id DEMAND_PLAN_ID, a.cap_usg_ratio_obj SUPPLY_PLAN, comp.level_pk COMPONENT_PK, comp.level_value COMPONENT, asmb.level_pk ASSEMBLY_PK, asmb.level_value ASSEMBLY, nvl(mabc.capacity_usage_ratio,0) QUANTITY_PER, nvl(mabc.lead_time,0) LEAD_TIME, a.bom_type BOM_TYPE from ( select mscbc.sr_instance_id instance, mscbc.demand_plan_id, mabc1.cap_usg_ratio_obj, mscbc.sr_assembly_pk, mscbc.sr_component_pk, mscbc.plan_type, mscbc.bom_type from msd_ascp_bom_comp mabc1, msd_sop_collapsed_bom_comp mscbc, msd_demand_plans mdp, msd_dp_parameters mparam where mdp.plan_type = 'EOL' and mparam.demand_plan_id = mdp.demand_plan_id and mparam.parameter_type = 'MSD_SIM_END_ITEM_DEMAND' and mscbc.DEMAND_PLAN_ID = mdp.demand_plan_id and mabc1.cap_usg_ratio_obj = mparam.capacity_usage_ratio /* msd_ascp_bom_comp and msd_sop_collapsed_bom_comp */ and mabc1.sr_assembly_pk = mscbc.sr_assembly_pk and mabc1.sr_component_pk = mscbc.sr_component_pk and mabc1.plan_type = mscbc.plan_type and mabc1.bom_type = mscbc.bom_type and mscbc.plan_type = mdp.plan_type ) a , msd_ascp_bom_comp mabc, msd_level_values comp, msd_level_values asmb WHERE a.cap_usg_ratio_obj = mabc.cap_usg_ratio_obj(+) and a.sr_assembly_pk = mabc.sr_assembly_pk(+) and a.sr_component_pk = mabc.sr_component_pk(+) and a.instance = mabc.sr_instance_id(+) and a.plan_type = mabc.plan_type(+) and a.bom_type = mabc.bom_type(+) and comp.instance = a.instance and comp.level_id = 1 and comp.sr_level_pk = a.sr_component_pk and asmb.instance = a.instance and asmb.level_id = 1 and asmb.sr_level_pk = a.sr_assembly_pk

View Text - HTML Formatted

SELECT A.INSTANCE SR_INSTANCE_ID
, A.DEMAND_PLAN_ID DEMAND_PLAN_ID
, A.CAP_USG_RATIO_OBJ SUPPLY_PLAN
, COMP.LEVEL_PK COMPONENT_PK
, COMP.LEVEL_VALUE COMPONENT
, ASMB.LEVEL_PK ASSEMBLY_PK
, ASMB.LEVEL_VALUE ASSEMBLY
, NVL(MABC.CAPACITY_USAGE_RATIO
, 0) QUANTITY_PER
, NVL(MABC.LEAD_TIME
, 0) LEAD_TIME
, A.BOM_TYPE BOM_TYPE
FROM ( SELECT MSCBC.SR_INSTANCE_ID INSTANCE
, MSCBC.DEMAND_PLAN_ID
, MABC1.CAP_USG_RATIO_OBJ
, MSCBC.SR_ASSEMBLY_PK
, MSCBC.SR_COMPONENT_PK
, MSCBC.PLAN_TYPE
, MSCBC.BOM_TYPE
FROM MSD_ASCP_BOM_COMP MABC1
, MSD_SOP_COLLAPSED_BOM_COMP MSCBC
, MSD_DEMAND_PLANS MDP
, MSD_DP_PARAMETERS MPARAM
WHERE MDP.PLAN_TYPE = 'EOL'
AND MPARAM.DEMAND_PLAN_ID = MDP.DEMAND_PLAN_ID
AND MPARAM.PARAMETER_TYPE = 'MSD_SIM_END_ITEM_DEMAND'
AND MSCBC.DEMAND_PLAN_ID = MDP.DEMAND_PLAN_ID
AND MABC1.CAP_USG_RATIO_OBJ = MPARAM.CAPACITY_USAGE_RATIO /* MSD_ASCP_BOM_COMP
AND MSD_SOP_COLLAPSED_BOM_COMP */
AND MABC1.SR_ASSEMBLY_PK = MSCBC.SR_ASSEMBLY_PK
AND MABC1.SR_COMPONENT_PK = MSCBC.SR_COMPONENT_PK
AND MABC1.PLAN_TYPE = MSCBC.PLAN_TYPE
AND MABC1.BOM_TYPE = MSCBC.BOM_TYPE
AND MSCBC.PLAN_TYPE = MDP.PLAN_TYPE ) A
, MSD_ASCP_BOM_COMP MABC
, MSD_LEVEL_VALUES COMP
, MSD_LEVEL_VALUES ASMB
WHERE A.CAP_USG_RATIO_OBJ = MABC.CAP_USG_RATIO_OBJ(+)
AND A.SR_ASSEMBLY_PK = MABC.SR_ASSEMBLY_PK(+)
AND A.SR_COMPONENT_PK = MABC.SR_COMPONENT_PK(+)
AND A.INSTANCE = MABC.SR_INSTANCE_ID(+)
AND A.PLAN_TYPE = MABC.PLAN_TYPE(+)
AND A.BOM_TYPE = MABC.BOM_TYPE(+)
AND COMP.INSTANCE = A.INSTANCE
AND COMP.LEVEL_ID = 1
AND COMP.SR_LEVEL_PK = A.SR_COMPONENT_PK
AND ASMB.INSTANCE = A.INSTANCE
AND ASMB.LEVEL_ID = 1
AND ASMB.SR_LEVEL_PK = A.SR_ASSEMBLY_PK