View: MSC_VERTICAL_PLAN_V

Product:	MSC - Advanced Supply Chain Planning
Description:	this view shows the vertical plan
Implementation/DBA Data:	APPS.MSC_VERTICAL_PLAN_V

View Text

SELECT SUP.TRANSACTION_ID
, SUP.INVENTORY_ITEM_ID
, SUP.ORGANIZATION_ID
, MSI.ORGANIZATION_CODE
, SUP.PLAN_ID
, SUP.SR_INSTANCE_ID
, CAL.CALENDAR_DATE
, DECODE(SUP.ORDER_TYPE
, 1
, SUP.ORDER_NUMBER
, 2
, SUP.ORDER_NUMBER
, 3
, SUP.ORDER_NUMBER
, 7
, SUP.ORDER_NUMBER
, 8
, SUP.ORDER_NUMBER
, 11
, SUP.ORDER_NUMBER
, 12
, SUP.ORDER_NUMBER
, 14
, SUP.WIP_ENTITY_NAME
, 15
, SUP.WIP_ENTITY_NAME
, 27
, SUP.WIP_ENTITY_NAME
, 28
, SUP.WIP_ENTITY_NAME
, 5
, TO_CHAR(SUP.TRANSACTION_ID)
, NULL)
, SUP.ORDER_TYPE
, MSC_GET_NAME.LOOKUP_MEANING('MRP_ORDER_TYPE'
, SUP.ORDER_TYPE)
, NVL(SUP.DAILY_RATE
, SUP.NEW_ORDER_QUANTITY)
, MSI.ITEM_NAME
, MSI.DESCRIPTION
, TO_NUMBER(NULL)
, NULL
, SUP.PLANNING_GROUP
, SUP.PROJECT_ID
, SUP.TASK_ID
, DECODE(SUP.ORDER_TYPE
, 18
, 1
, 2)
, 1
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
FROM MSC_CALENDAR_DATES CAL
, MSC_TRADING_PARTNERS MTP
, MSC_SYSTEM_ITEMS MSI
, MSC_SUPPLIES SUP
WHERE CAL.CALENDAR_DATE BETWEEN TRUNC(SUP.NEW_SCHEDULE_DATE) AND NVL(TRUNC(SUP.LAST_UNIT_COMPLETION_DATE)
, TRUNC(SUP.NEW_SCHEDULE_DATE))
AND MTP.CALENDAR_EXCEPTION_SET_ID = CAL.EXCEPTION_SET_ID
AND MTP.CALENDAR_CODE = CAL.CALENDAR_CODE
AND MTP.SR_INSTANCE_ID = CAL.SR_INSTANCE_ID
AND MTP.SR_TP_ID = SUP.ORGANIZATION_ID
AND MTP.SR_INSTANCE_ID = SUP.SR_INSTANCE_ID
AND MTP.PARTNER_TYPE = 3
AND SUP.PLAN_ID = MSI.PLAN_ID
AND SUP.SR_INSTANCE_ID = MSI.SR_INSTANCE_ID
AND SUP.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND SUP.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND ((SUP.DISPOSITION_STATUS_TYPE <> 2 OR SUP.DISPOSITION_STATUS_TYPE IS NULL) OR (SUP.DISPOSITION_STATUS_TYPE = 2
AND (MSI.BASE_ITEM_ID IS NOT NULL OR MSI.WIP_SUPPLY_TYPE = 6 OR SUP.ORDER_TYPE IN (14
, 15
, 16
, 17
, 18
, 19)))) UNION ALL SELECT DEM.DEMAND_ID
, DEM.INVENTORY_ITEM_ID
, DEM.ORGANIZATION_ID
, MSI.ORGANIZATION_CODE
, DEM.PLAN_ID
, DEM.SR_INSTANCE_ID
, CAL.CALENDAR_DATE
, NVL(DEM.ORDER_NUMBER
, DECODE(DEM.ORIGINATION_TYPE
, 1
, TO_CHAR(DEM.DISPOSITION_ID)
, 29
, MSC_GET_NAME.SCENARIO_DESIGNATOR(DEM.FORECAST_SET_ID
, DEM.PLAN_ID
, DEM.ORGANIZATION_ID
, DEM.SR_INSTANCE_ID)
, MSC_GET_NAME.DESIGNATOR(DEM.SCHEDULE_DESIGNATOR_ID)))
, DEM.ORIGINATION_TYPE
, MSC_GET_NAME.LOOKUP_MEANING(DECODE(DEM.ORIGINATION_TYPE
, 1
, 'MRP_PLANNED_ORDER_DEMAND'
, 3
, 'MRP_PLANNED_ORDER_DEMAND'
, 25
, 'MRP_PLANNED_ORDER_DEMAND'
, 'MRP_DEMAND_ORIGINATION')
, DEM.ORIGINATION_TYPE)
, -(NVL(DEM.DAILY_DEMAND_RATE
, DEM.USING_REQUIREMENT_QUANTITY))*DECODE(DEM.PROBABILITY
, NULL
, 1
, DEM.PROBABILITY)
, MSI.ITEM_NAME
, MSI.DESCRIPTION
, TO_NUMBER(NULL)
, NULL
, DEM.PLANNING_GROUP
, DEM.PROJECT_ID
, DEM.TASK_ID
, 2
, 2
, DEM.PROBABILITY
, -(NVL(DEM.DAILY_DEMAND_RATE
, DEM.USING_REQUIREMENT_QUANTITY))
FROM MSC_CALENDAR_DATES CAL
, MSC_TRADING_PARTNERS MTP
, MSC_SYSTEM_ITEMS MSI
, MSC_DEMANDS DEM
WHERE CAL.CALENDAR_DATE BETWEEN TRUNC(DEM.USING_ASSEMBLY_DEMAND_DATE) AND NVL(TRUNC(DEM.ASSEMBLY_DEMAND_COMP_DATE)
, TRUNC(DEM.USING_ASSEMBLY_DEMAND_DATE))
AND CAL.CALENDAR_CODE = MTP.CALENDAR_CODE
AND CAL.EXCEPTION_SET_ID = MTP.CALENDAR_EXCEPTION_SET_ID
AND CAL.SR_INSTANCE_ID = MTP.SR_INSTANCE_ID
AND MTP.SR_INSTANCE_ID = DEM.SR_INSTANCE_ID
AND MTP.SR_TP_ID = DEM.ORGANIZATION_ID
AND MTP.PARTNER_TYPE = 3
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 UNION ALL SELECT DEM.DEMAND_ID
, DEM.INVENTORY_ITEM_ID
, DEM.ORGANIZATION_ID
, MSI.ORGANIZATION_CODE
, -1
, DEM.SR_INSTANCE_ID
, CAL.CALENDAR_DATE
, DEM.SALES_ORDER_NUMBER
, 30
, MSC_GET_NAME.LOOKUP_MEANING('MRP_DEMAND_ORIGINATION'
, 30)
, -(DEM.PRIMARY_UOM_QUANTITY)
, MSI.ITEM_NAME
, MSI.DESCRIPTION
, TO_NUMBER(NULL)
, NULL
, DEM.PLANNING_GROUP
, DEM.PROJECT_ID
, DEM.TASK_ID
, 2
, 2
, TO_NUMBER(NULL)
, -(DEM.PRIMARY_UOM_QUANTITY)
FROM MSC_CALENDAR_DATES CAL
, MSC_TRADING_PARTNERS MTP
, MSC_SYSTEM_ITEMS MSI
, MSC_SALES_ORDERS DEM
WHERE CAL.CALENDAR_DATE BETWEEN TRUNC(DEM.REQUIREMENT_DATE)
AND TRUNC(DEM.REQUIREMENT_DATE)
AND CAL.CALENDAR_CODE = MTP.CALENDAR_CODE
AND CAL.EXCEPTION_SET_ID = MTP.CALENDAR_EXCEPTION_SET_ID
AND CAL.SR_INSTANCE_ID = MTP.SR_INSTANCE_ID
AND MTP.SR_INSTANCE_ID = DEM.SR_INSTANCE_ID
AND MTP.SR_TP_ID = DEM.ORGANIZATION_ID
AND MTP.PARTNER_TYPE = 3
AND MSI.PLAN_ID = -1
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

Columns