View: MSD_BOM_COMPONENTS_V

Product:	MSD - Demand Planning
Description:	This view provides Planning BOMs and its attributes information for demand planning
Implementation/DBA Data:	APPS.MSD_BOM_COMPONENTS_V

View Text

SELECT /*+ ORDERED */ DISTINCT MV1.LEVEL_PK
, MV1.LEVEL_VALUE
, MV2.LEVEL_PK
, MV2.LEVEL_VALUE
, MV3.LEVEL_PK
, MV3.LEVEL_VALUE
, MBC.PLANNING_FACTOR
, MBC.USAGE_QUANTITY
, MBC.EFFECTIVITY_DATE
, MBC.DISABLE_DATE
, NULL
, NULL
FROM MSC_BOM_COMPONENTS MBC
, MSC_BOMS MBS
, MSC_SYSTEM_ITEMS MS1
, MSC_SYSTEM_ITEMS MS2
, MSD_LEVEL_VALUES MV1
, MSD_LEVEL_VALUES MV2
, MSD_LEVEL_VALUES MV3
, (SELECT NVL(FND_PROFILE.VALUE('MSD_PLANNING_PERCENTAGE')
, '1') PROFILE_VALUE
FROM DUAL) PP
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
AND MS2.ATO_FORECAST_CONTROL <> 3
AND MS2.BOM_ITEM_TYPE <> 4
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
AND MS1.ATO_FORECAST_CONTROL = 2
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 UNION ALL SELECT /*+ INDEX(ASMB MSD_LEVEL_VALUES_U3) INDEX(COMP MSD_LEVEL_VALUES_U3) INDEX(ORG MSD_LEVEL_VALUES_U3) */ COMP.LEVEL_PK
, COMP.LEVEL_VALUE
, ASMB.LEVEL_PK
, ASMB.LEVEL_VALUE
, ORG.LEVEL_PK
, ORG.LEVEL_VALUE
, MB.PLANNING_FACTOR
, MB.QUANTITY_PER
, MB.EFFECTIVITY_DATE
, MB.DISABLE_DATE
, NULL
, NULL
FROM MSD_BOM_COMPONENTS MB
, MSD_LEVEL_VALUES COMP
, MSD_LEVEL_VALUES ASMB
, MSD_LEVEL_VALUES ORG
, (SELECT NVL(FND_PROFILE.VALUE('MSD_PLANNING_PERCENTAGE')
, '1') PROFILE_VALUE
FROM DUAL) PP
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

Columns