VIEW: APPS.MSD_SOP_BOM_COMP_DS_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, asmb.level_pk ASSEMBLY_PK, a.EFFECTIVITY_DATE, a.DISABLE_DATE, nvl(mabc.capacity_usage_ratio,0), nvl(mabc.lead_time,0), 'Y' OPTIONAL_FLAG, a.res_comp from ( select mscbc.sr_instance_id instance, mscbc.demand_plan_id, mabc1.cap_usg_ratio_obj, mscbc.sr_assembly_pk, mscbc.sr_component_pk, mabc1.EFFECTIVITY_DATE, mabc1.DISABLE_DATE, mscbc.res_comp, 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 ='SOP' and mscbc.DEMAND_PLAN_ID = mdp.demand_plan_id and mparam.demand_plan_id = mdp.demand_plan_id and mparam.parameter_type = 'MSD_SUPPLY_PLANS' and mabc1.cap_usg_ratio_obj = mparam.capacity_usage_ratio and mabc1.sr_assembly_pk = mscbc.sr_assembly_pk and mabc1.sr_component_pk = mscbc.sr_component_pk and trunc(mabc1.effectivity_date) = trunc(mscbc.effectivity_date) and trunc(mabc1.disable_date) = trunc(mscbc.disable_date) 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_ds comp, msd_level_values_ds asmb, msd_dp_session mds 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.EFFECTIVITY_DATE = mabc.EFFECTIVITY_DATE(+) and a.DISABLE_DATE = mabc.DISABLE_DATE(+) and a.plan_type = mabc.plan_type(+) and a.bom_type = mabc.bom_type(+) and a.demand_plan_id = mds.demand_plan_id and comp.instance = a.instance and comp.level_id = 1 and comp.sr_level_pk = a.sr_component_pk and comp.demand_plan_id= mds.demand_plan_id and asmb.instance = a.instance and asmb.level_id = 1 and asmb.sr_level_pk = a.sr_assembly_pk and asmb.demand_plan_id= mds.demand_plan_id

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
, ASMB.LEVEL_PK ASSEMBLY_PK
, A.EFFECTIVITY_DATE
, A.DISABLE_DATE
, NVL(MABC.CAPACITY_USAGE_RATIO
, 0)
, NVL(MABC.LEAD_TIME
, 0)
, 'Y' OPTIONAL_FLAG
, A.RES_COMP
FROM ( SELECT MSCBC.SR_INSTANCE_ID INSTANCE
, MSCBC.DEMAND_PLAN_ID
, MABC1.CAP_USG_RATIO_OBJ
, MSCBC.SR_ASSEMBLY_PK
, MSCBC.SR_COMPONENT_PK
, MABC1.EFFECTIVITY_DATE
, MABC1.DISABLE_DATE
, MSCBC.RES_COMP
, 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 ='SOP'
AND MSCBC.DEMAND_PLAN_ID = MDP.DEMAND_PLAN_ID
AND MPARAM.DEMAND_PLAN_ID = MDP.DEMAND_PLAN_ID
AND MPARAM.PARAMETER_TYPE = 'MSD_SUPPLY_PLANS'
AND MABC1.CAP_USG_RATIO_OBJ = MPARAM.CAPACITY_USAGE_RATIO
AND MABC1.SR_ASSEMBLY_PK = MSCBC.SR_ASSEMBLY_PK
AND MABC1.SR_COMPONENT_PK = MSCBC.SR_COMPONENT_PK
AND TRUNC(MABC1.EFFECTIVITY_DATE) = TRUNC(MSCBC.EFFECTIVITY_DATE)
AND TRUNC(MABC1.DISABLE_DATE) = TRUNC(MSCBC.DISABLE_DATE)
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_DS COMP
, MSD_LEVEL_VALUES_DS ASMB
, MSD_DP_SESSION MDS
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.EFFECTIVITY_DATE = MABC.EFFECTIVITY_DATE(+)
AND A.DISABLE_DATE = MABC.DISABLE_DATE(+)
AND A.PLAN_TYPE = MABC.PLAN_TYPE(+)
AND A.BOM_TYPE = MABC.BOM_TYPE(+)
AND A.DEMAND_PLAN_ID = MDS.DEMAND_PLAN_ID
AND COMP.INSTANCE = A.INSTANCE
AND COMP.LEVEL_ID = 1
AND COMP.SR_LEVEL_PK = A.SR_COMPONENT_PK
AND COMP.DEMAND_PLAN_ID= MDS.DEMAND_PLAN_ID
AND ASMB.INSTANCE = A.INSTANCE
AND ASMB.LEVEL_ID = 1
AND ASMB.SR_LEVEL_PK = A.SR_ASSEMBLY_PK
AND ASMB.DEMAND_PLAN_ID= MDS.DEMAND_PLAN_ID