VIEW: APPS.MSD_BOM_COMPONENTS_DS_V

Source

View Text - Preformatted

SELECT /*+ ORDERED */ distinct mv1.demand_plan_id, mv1.level_pk, mv2.level_pk, mv3.level_pk, mbc.planning_factor, decode(mbc.usage_quantity/decode(mbc.usage_quantity,null,1,0,1, abs(mbc.usage_quantity)),1, (mbc.usage_quantity * mbc.Component_Yield_Factor), (mbc.usage_quantity / mbc.Component_Yield_Factor))*msd_common_utilities.uom_conv(ms1.uom_code,ms1.inventory_item_id), mbc.effectivity_date, mbc.disable_date, null, null , mbc.optional_component, decode(mai.validation_org_id, null, 'N', 'Y') from msc_bom_components mbc, msc_boms mbs, msc_system_items ms1, msc_system_items ms2, msd_dp_session mds, msd_level_values_ds mv1, msd_level_values_ds mv2, msd_level_values_ds mv3, (select nvl(fnd_profile.value('MSD_PLANNING_PERCENTAGE'), '1') profile_value from dual) pp, msc_apps_instances mai WHERE mbs.assembly_item_id = mbc.using_assembly_id and pp.profile_value <> 3 and mbs.bill_sequence_id = mbc.bill_sequence_id and mbs.organization_id = mbc.organization_id and mbs.sr_instance_id = mbc.sr_instance_id and mbs.plan_id = mbc.plan_id and mbs.alternate_bom_designator is null and mv3.sr_level_pk = to_char(mbc.organization_id) and mv3.instance = to_char(mbc.sr_instance_id) and mv3.level_id = 7 and ms2.inventory_item_id = mbc.using_assembly_id and ms2.organization_id = mbc.organization_id and ms2.sr_instance_id = mbc.sr_instance_id and ms2.plan_id = mbc.plan_id and (ms2.mrp_planning_code <> 6 or (ms2.mrp_planning_code = 6 and ms2.pick_components_flag = 'Y')) and (ms2.ato_forecast_control <> 3 or (pp.profile_value = 4 and ms2.bom_item_type =2)) and (ms2.bom_item_type <> 4 or (ms2.bom_item_type = 4 and ms2.pick_components_flag = 'Y')) and mv2.instance =to_char( ms2.sr_instance_id) and mv2.sr_level_pk = to_char(ms2.sr_inventory_item_id) and mv2.level_id = 1 and ms1.inventory_item_id = mbc.inventory_item_id and ms1.organization_id = mbc.organization_id and ms1.sr_instance_id = mbc.sr_instance_id and ms1.plan_id = mbc.plan_id and (ms1.mrp_planning_code <> 6 or (ms1.mrp_planning_code = 6 and ms1.pick_components_flag = 'Y')) and ((ms1.ato_forecast_control = 2) or (pp.profile_value = 4 and (ms1.bom_item_type = 2 or mbc.optional_component = '1') )) and mv1.instance = to_char(ms1.sr_instance_id) and mv1.sr_level_pk = to_char(ms1.sr_inventory_item_id) and mv1.level_id = 1 and mbc.plan_id = -1 and mv1.demand_plan_id = mds.demand_plan_id and mv2.demand_plan_id = mds.demand_plan_id and mv3.demand_plan_id = mds.demand_plan_id and mai.instance_id(+) = ms2.sr_instance_id and mai.validation_org_id (+) = ms2.organization_id UNION ALL SELECT /*+ INDEX(asmb MSD_LEVEL_VALUES_DS_U2) INDEX(comp MSD_LEVEL_VALUES_DS_U2) INDEX(org MSD_LEVEL_VALUES_DS_U2) */ comp.demand_plan_id, comp.level_pk, asmb.level_pk, org.level_pk, mb.planning_factor,mb.quantity_per, mb.effectivity_date, mb.disable_date,null,null, nvl(mb.optional_flag, 2), decode(mai.validation_org_id, null, 'N', 'Y') FROM Msd_bom_components mb, msd_dp_session mds, msd_level_values_ds comp, msd_level_values_ds asmb, msd_level_values_ds org, (select nvl(fnd_profile.value('MSD_PLANNING_PERCENTAGE'), '1') profile_value from dual) pp , msc_apps_instances mai WHERE comp.instance = mb.instance and comp.level_id = 1 and comp.sr_level_pk = mb.sr_component_pk and asmb.instance = mb.instance and asmb.level_id = 1 and asmb.sr_level_pk = mb.sr_assembly_pk and org.instance = mb.instance and org.level_id = 7 and org.sr_level_pk = mb.sr_organization_pk and pp.profile_value = 3 And comp.demand_plan_id = mds.demand_plan_id and asmb.demand_plan_id = mds.demand_plan_id and org.demand_plan_id = mds.demand_plan_id and mai.instance_id (+) = mb.instance and mai.validation_org_id (+) = mb.sr_organization_pk

View Text - HTML Formatted

SELECT /*+ ORDERED */ DISTINCT MV1.DEMAND_PLAN_ID
, MV1.LEVEL_PK
, MV2.LEVEL_PK
, MV3.LEVEL_PK
, MBC.PLANNING_FACTOR
, DECODE(MBC.USAGE_QUANTITY/DECODE(MBC.USAGE_QUANTITY
, NULL
, 1
, 0
, 1
, ABS(MBC.USAGE_QUANTITY))
, 1
, (MBC.USAGE_QUANTITY * MBC.COMPONENT_YIELD_FACTOR)
, (MBC.USAGE_QUANTITY / MBC.COMPONENT_YIELD_FACTOR))*MSD_COMMON_UTILITIES.UOM_CONV(MS1.UOM_CODE
, MS1.INVENTORY_ITEM_ID)
, MBC.EFFECTIVITY_DATE
, MBC.DISABLE_DATE
, NULL
, NULL
, MBC.OPTIONAL_COMPONENT
, DECODE(MAI.VALIDATION_ORG_ID
, NULL
, 'N'
, 'Y')
FROM MSC_BOM_COMPONENTS MBC
, MSC_BOMS MBS
, MSC_SYSTEM_ITEMS MS1
, MSC_SYSTEM_ITEMS MS2
, MSD_DP_SESSION MDS
, MSD_LEVEL_VALUES_DS MV1
, MSD_LEVEL_VALUES_DS MV2
, MSD_LEVEL_VALUES_DS MV3
, (SELECT NVL(FND_PROFILE.VALUE('MSD_PLANNING_PERCENTAGE')
, '1') PROFILE_VALUE
FROM DUAL) PP
, MSC_APPS_INSTANCES MAI
WHERE MBS.ASSEMBLY_ITEM_ID = MBC.USING_ASSEMBLY_ID
AND PP.PROFILE_VALUE <> 3
AND MBS.BILL_SEQUENCE_ID = MBC.BILL_SEQUENCE_ID
AND MBS.ORGANIZATION_ID = MBC.ORGANIZATION_ID
AND MBS.SR_INSTANCE_ID = MBC.SR_INSTANCE_ID
AND MBS.PLAN_ID = MBC.PLAN_ID
AND MBS.ALTERNATE_BOM_DESIGNATOR IS NULL
AND MV3.SR_LEVEL_PK = TO_CHAR(MBC.ORGANIZATION_ID)
AND MV3.INSTANCE = TO_CHAR(MBC.SR_INSTANCE_ID)
AND MV3.LEVEL_ID = 7
AND MS2.INVENTORY_ITEM_ID = MBC.USING_ASSEMBLY_ID
AND MS2.ORGANIZATION_ID = MBC.ORGANIZATION_ID
AND MS2.SR_INSTANCE_ID = MBC.SR_INSTANCE_ID
AND MS2.PLAN_ID = MBC.PLAN_ID
AND (MS2.MRP_PLANNING_CODE <> 6 OR (MS2.MRP_PLANNING_CODE = 6
AND MS2.PICK_COMPONENTS_FLAG = 'Y'))
AND (MS2.ATO_FORECAST_CONTROL <> 3 OR (PP.PROFILE_VALUE = 4
AND MS2.BOM_ITEM_TYPE =2))
AND (MS2.BOM_ITEM_TYPE <> 4 OR (MS2.BOM_ITEM_TYPE = 4
AND MS2.PICK_COMPONENTS_FLAG = 'Y'))
AND MV2.INSTANCE =TO_CHAR( MS2.SR_INSTANCE_ID)
AND MV2.SR_LEVEL_PK = TO_CHAR(MS2.SR_INVENTORY_ITEM_ID)
AND MV2.LEVEL_ID = 1
AND MS1.INVENTORY_ITEM_ID = MBC.INVENTORY_ITEM_ID
AND MS1.ORGANIZATION_ID = MBC.ORGANIZATION_ID
AND MS1.SR_INSTANCE_ID = MBC.SR_INSTANCE_ID
AND MS1.PLAN_ID = MBC.PLAN_ID
AND (MS1.MRP_PLANNING_CODE <> 6 OR (MS1.MRP_PLANNING_CODE = 6
AND MS1.PICK_COMPONENTS_FLAG = 'Y'))
AND ((MS1.ATO_FORECAST_CONTROL = 2) OR (PP.PROFILE_VALUE = 4
AND (MS1.BOM_ITEM_TYPE = 2 OR MBC.OPTIONAL_COMPONENT = '1') ))
AND MV1.INSTANCE = TO_CHAR(MS1.SR_INSTANCE_ID)
AND MV1.SR_LEVEL_PK = TO_CHAR(MS1.SR_INVENTORY_ITEM_ID)
AND MV1.LEVEL_ID = 1
AND MBC.PLAN_ID = -1
AND MV1.DEMAND_PLAN_ID = MDS.DEMAND_PLAN_ID
AND MV2.DEMAND_PLAN_ID = MDS.DEMAND_PLAN_ID
AND MV3.DEMAND_PLAN_ID = MDS.DEMAND_PLAN_ID
AND MAI.INSTANCE_ID(+) = MS2.SR_INSTANCE_ID
AND MAI.VALIDATION_ORG_ID (+) = MS2.ORGANIZATION_ID UNION ALL SELECT /*+ INDEX(ASMB MSD_LEVEL_VALUES_DS_U2) INDEX(COMP MSD_LEVEL_VALUES_DS_U2) INDEX(ORG MSD_LEVEL_VALUES_DS_U2) */ COMP.DEMAND_PLAN_ID
, COMP.LEVEL_PK
, ASMB.LEVEL_PK
, ORG.LEVEL_PK
, MB.PLANNING_FACTOR
, MB.QUANTITY_PER
, MB.EFFECTIVITY_DATE
, MB.DISABLE_DATE
, NULL
, NULL
, NVL(MB.OPTIONAL_FLAG
, 2)
, DECODE(MAI.VALIDATION_ORG_ID
, NULL
, 'N'
, 'Y')
FROM MSD_BOM_COMPONENTS MB
, MSD_DP_SESSION MDS
, MSD_LEVEL_VALUES_DS COMP
, MSD_LEVEL_VALUES_DS ASMB
, MSD_LEVEL_VALUES_DS ORG
, (SELECT NVL(FND_PROFILE.VALUE('MSD_PLANNING_PERCENTAGE')
, '1') PROFILE_VALUE
FROM DUAL) PP
, MSC_APPS_INSTANCES MAI
WHERE COMP.INSTANCE = MB.INSTANCE
AND COMP.LEVEL_ID = 1
AND COMP.SR_LEVEL_PK = MB.SR_COMPONENT_PK
AND ASMB.INSTANCE = MB.INSTANCE
AND ASMB.LEVEL_ID = 1
AND ASMB.SR_LEVEL_PK = MB.SR_ASSEMBLY_PK
AND ORG.INSTANCE = MB.INSTANCE
AND ORG.LEVEL_ID = 7
AND ORG.SR_LEVEL_PK = MB.SR_ORGANIZATION_PK
AND PP.PROFILE_VALUE = 3
AND COMP.DEMAND_PLAN_ID = MDS.DEMAND_PLAN_ID
AND ASMB.DEMAND_PLAN_ID = MDS.DEMAND_PLAN_ID
AND ORG.DEMAND_PLAN_ID = MDS.DEMAND_PLAN_ID
AND MAI.INSTANCE_ID (+) = MB.INSTANCE
AND MAI.VALIDATION_ORG_ID (+) = MB.SR_ORGANIZATION_PK