View: MSD_CONSTRAINED_FORECAST_V

Product:	MSD - Demand Planning
Description:	Constrained Plan View
Implementation/DBA Data:	APPS.MSD_CONSTRAINED_FORECAST_V

View Text

SELECT TOTAL.PLAN_ID
, TOTAL.CS_NAME
, TOTAL.ORG_LEVEL_VALUE_PK
, TOTAL.ORG_LEVEL_ID
, TOTAL.PRD_LEVEL_VALUE_PK
, TOTAL.PRD_LEVEL_ID
, TOTAL.GEO_LEVEL_VALUE_PK
, TOTAL.GEO_LEVEL_ID
, TOTAL.TIME_LEVEL_ID
, TOTAL.QUANTITY QUANTITY
, TOTAL.END_DATE
, TOTAL.CREATION_DATE
, TOTAL.CREATED_BY
, TOTAL.LAST_UPDATE_DATE
, TOTAL.LAST_UPDATED_BY
, TOTAL.LAST_UPDATE_LOGIN
FROM (SELECT DEM.PLAN_ID PLAN_ID
, PLANS.COMPILE_DESIGNATOR CS_NAME
, D_ORG.LEVEL_PK ORG_LEVEL_VALUE_PK
, D_ORG.LEVEL_ID ORG_LEVEL_ID
, D_ITM.LEVEL_PK PRD_LEVEL_VALUE_PK
, D_ITM.LEVEL_ID PRD_LEVEL_ID
, NVL(D_CUST.LEVEL_PK
, MSD_COMMON_UTILITIES.GET_LOC_KEY( DEM.SR_INSTANCE_ID
, '-777'
, NULL
, 15)) GEO_LEVEL_VALUE_PK
, 15 GEO_LEVEL_ID
, 9 TIME_LEVEL_ID
, DEM.QUANTITY_BY_DUE_DATE QUANTITY
, TRUNC(DEM.USING_ASSEMBLY_DEMAND_DATE) END_DATE
, DEM.CREATION_DATE
, DEM.CREATED_BY
, DEM.LAST_UPDATE_DATE
, DEM.LAST_UPDATED_BY
, DEM.LAST_UPDATE_LOGIN
FROM MSC_DEMANDS DEM
, MSC_PLANS PLANS
, MSC_SYSTEM_ITEMS MSI
, MSC_SYSTEM_ITEMS MSIA
, MSC_TRADING_PARTNERS MTP
, MSD_LEVEL_VALUES D_ITM
, MSD_LEVEL_VALUES D_CUST
, MSD_LEVEL_VALUES D_ORG
WHERE DEM.PLAN_ID != -1
AND DEM.PLAN_ID = PLANS.PLAN_ID
AND DEM.PLAN_ID = MSIA.PLAN_ID
AND DEM.SR_INSTANCE_ID = MSIA.SR_INSTANCE_ID
AND DEM.ORGANIZATION_ID = MSIA.ORGANIZATION_ID
AND DEM.USING_ASSEMBLY_ITEM_ID = MSIA.INVENTORY_ITEM_ID
AND ((DEM.INVENTORY_ITEM_ID != DEM.USING_ASSEMBLY_ITEM_ID
AND MSIA.BOM_ITEM_TYPE = 5) OR (DEM.INVENTORY_ITEM_ID = DEM.USING_ASSEMBLY_ITEM_ID))
AND DEM.ORIGINATION_TYPE IN (6
, 7
, 8
, 9
, 11
, 29
, 30
, 42
, 22)
AND DEM.PLAN_ID = MSI.PLAN_ID
AND DEM.SR_INSTANCE_ID = MSI.SR_INSTANCE_ID
AND DEM.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND DEM.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MSI.ATO_FORECAST_CONTROL <> 3
AND MSI.MRP_PLANNING_CODE <> 6
AND D_ITM.SR_LEVEL_PK = TO_CHAR(MSI.SR_INVENTORY_ITEM_ID)
AND D_ITM.INSTANCE = TO_CHAR(MSI.SR_INSTANCE_ID)
AND D_ITM.LEVEL_ID = 1
AND DEM.CUSTOMER_ID = MTP.PARTNER_ID(+)
AND D_CUST.SR_LEVEL_PK(+) = TO_CHAR(MTP.SR_TP_ID)
AND D_CUST.INSTANCE(+)= TO_CHAR(MTP.SR_INSTANCE_ID)
AND D_CUST.LEVEL_ID(+)= 15
AND D_ORG.SR_LEVEL_PK = TO_CHAR(DEM.ORGANIZATION_ID)
AND D_ORG.INSTANCE = TO_CHAR(DEM.SR_INSTANCE_ID)
AND D_ORG.LEVEL_ID = 7
AND NVL(DEM.QUANTITY_BY_DUE_DATE
, 0) != 0 UNION ALL SELECT DEM.PLAN_ID
, PLANS.COMPILE_DESIGNATOR
, D_ORG.LEVEL_PK
, D_ORG.LEVEL_ID
, D_ITM.LEVEL_PK
, D_ITM.LEVEL_ID
, NVL(D_CUST.LEVEL_PK
, MSD_COMMON_UTILITIES.GET_LOC_KEY(DEM.SR_INSTANCE_ID
, '-777'
, NULL
, 15))
, 15
, 9
, (DEM.USING_REQUIREMENT_QUANTITY - NVL(DEM.QUANTITY_BY_DUE_DATE
, 0)) QUANTITY
, TRUNC(DEM.DMD_SATISFIED_DATE) DUE_DATE
, DEM.CREATION_DATE
, DEM.CREATED_BY
, DEM.LAST_UPDATE_DATE
, DEM.LAST_UPDATED_BY
, DEM.LAST_UPDATE_LOGIN
FROM MSC_DEMANDS DEM
, MSC_PLANS PLANS
, MSC_SYSTEM_ITEMS MSI
, MSC_SYSTEM_ITEMS MSIA
, MSC_TRADING_PARTNERS MTP
, MSD_LEVEL_VALUES D_ITM
, MSD_LEVEL_VALUES D_CUST
, MSD_LEVEL_VALUES D_ORG
WHERE DEM.PLAN_ID != -1
AND DEM.PLAN_ID = PLANS.PLAN_ID
AND DEM.PLAN_ID = MSIA.PLAN_ID
AND DEM.SR_INSTANCE_ID = MSIA.SR_INSTANCE_ID
AND DEM.USING_ASSEMBLY_ITEM_ID = MSIA.INVENTORY_ITEM_ID
AND DEM.ORGANIZATION_ID = MSIA.ORGANIZATION_ID
AND ((DEM.INVENTORY_ITEM_ID != DEM.USING_ASSEMBLY_ITEM_ID
AND MSIA.BOM_ITEM_TYPE =5) OR (DEM.INVENTORY_ITEM_ID = DEM.USING_ASSEMBLY_ITEM_ID))
AND DEM.ORIGINATION_TYPE IN (6
, 7
, 8
, 9
, 11
, 29
, 30
, 42
, 22)
AND DEM.PLAN_ID = MSI.PLAN_ID
AND DEM.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND DEM.SR_INSTANCE_ID = MSI.SR_INSTANCE_ID
AND DEM.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MSI.ATO_FORECAST_CONTROL <> 3
AND MSI.MRP_PLANNING_CODE <> 6
AND D_ITM.SR_LEVEL_PK = TO_CHAR(MSI.SR_INVENTORY_ITEM_ID)
AND D_ITM.INSTANCE = TO_CHAR(MSI.SR_INSTANCE_ID)
AND D_ITM.LEVEL_ID = 1
AND DEM.CUSTOMER_ID = MTP.PARTNER_ID(+)
AND D_CUST.SR_LEVEL_PK(+)= TO_CHAR(MTP.SR_TP_ID)
AND D_CUST.INSTANCE(+)= TO_CHAR(MTP.SR_INSTANCE_ID)
AND D_CUST.LEVEL_ID(+)= 15
AND D_ORG.SR_LEVEL_PK = TO_CHAR(DEM.ORGANIZATION_ID)
AND D_ORG.INSTANCE = TO_CHAR(DEM.SR_INSTANCE_ID)
AND D_ORG.LEVEL_ID = 7
AND (DEM.USING_REQUIREMENT_QUANTITY - NVL(DEM.QUANTITY_BY_DUE_DATE
, 0) !=0) ) TOTAL

Columns