View: MSD_DP_ASCP_SCN_ENTRIES_V

Product:	MSD - Demand Planning
Description:	This view provides the integration api with ASCP for scenario entries
Implementation/DBA Data:	APPS.MSD_DP_ASCP_SCN_ENTRIES_V

View Text

SELECT /*+ INDEX(MSI
, MSC_SYSTEM_ITEMS_U3) */ MDSE.DEMAND_PLAN_ID
, MDSE.SCENARIO_ID
, MDSE.ENTRY_ID
, DECODE(MDSE.TIME_LVL_ID
, 9
, 1
, 1
, 2
, 3)
, MDSE.TIME_LVL_VAL_FROM
, MDSE.TIME_LVL_VAL_TO
, ROUND(MDSE.TOTAL_QUANTITY * DECODE(MDSE.TOTAL_QUANTITY_UOM
, MSI.UOM_CODE
, 1
, MSD_COMMON_UTILITIES.MSD_UOM_CONVERT(MSI.INVENTORY_ITEM_ID
, 2
, MDSE.TOTAL_QUANTITY_UOM
, MSI.UOM_CODE))
, NVL(MDP.ROUNDOFF_DECIMAL_PLACES
, 6)) QUANTITY
, TO_NUMBER(DECODE(LTRIM(SR_ORGANIZATION_LVL_PK
, '.0123456789')
, NULL
, SR_ORGANIZATION_LVL_PK
, -1)) SR_ORGANIZATION_LVL_PK
, TO_NUMBER(INSTANCE)
, TO_NUMBER(DECODE(LTRIM(SR_PRODUCT_LVL_PK
, '.0123456789')
, NULL
, SR_PRODUCT_LVL_PK
, -1)) SR_PRODUCT_LVL_PK
, MDSR.ERROR_TYPE
, MDSE.FORECAST_ERROR
, MSI.INVENTORY_ITEM_ID
, DECODE(MDSE.GEOGRAPHY_LVL_ID
, 11
, TO_NUMBER(DECODE(LTRIM(MDSE.SR_GEOGRAPHY_LVL_PK
, '.0123456789')
, NULL
, MDSE.SR_GEOGRAPHY_LVL_PK
, -1))
, NULL)
, DECODE(MDSE.GEOGRAPHY_LVL_ID
, 11
, MSD_COMMON_UTILITIES.GET_PARENT_LEVEL_PK(MDSE.INSTANCE
, 11
, 15
, MDSE.SR_GEOGRAPHY_LVL_PK)
, 15
, TO_NUMBER(DECODE(LTRIM(MDSE.SR_GEOGRAPHY_LVL_PK
, '.0123456789')
, NULL
, MDSE.SR_GEOGRAPHY_LVL_PK
, -1))
, NULL)
, MDSE.FORECAST_PRIORITY
, MDSE.TOTAL_QUANTITY_UOM
, MSI.UOM_CODE
, MDSE.DEMAND_CLASS
, NVL(ABS(ROUND(MDSE.AMOUNT/MDSE.TOTAL_QUANTITY
, 2))
, MSI.LIST_PRICE * ( (100-MSI.AVERAGE_DISCOUNT)/100))
FROM MSC_SYSTEM_ITEMS MSI
, MSD_DP_SCENARIO_ENTRIES MDSE
, MSD_DP_SCENARIOS DS
, MSD_DP_SCENARIO_REVISIONS MDSR
, MSD_DEMAND_PLANS MDP
WHERE MDSE.DEMAND_PLAN_ID = DS.DEMAND_PLAN_ID
AND MDSE.SCENARIO_ID = DS.SCENARIO_ID
AND DS.PUBLISH_FLAG = 'Y'
AND MDSE.ORGANIZATION_LVL_ID = 7
AND MDSE.PRODUCT_LVL_ID IN (1
, 3) /*
AND MDSE.GEOGRAPHY_LVL_ID IN (11
, 15) */
AND MDSE.REVISION = DS.LAST_REVISION
AND MSI.PLAN_ID = -1
AND MSI.ORGANIZATION_ID = TO_NUMBER(DECODE(LTRIM(SR_ORGANIZATION_LVL_PK
, '.0123456789')
, NULL
, SR_ORGANIZATION_LVL_PK
, -1))
AND MSI.SR_INVENTORY_ITEM_ID = TO_NUMBER(DECODE(LTRIM(SR_PRODUCT_LVL_PK
, '.0123456789')
, NULL
, SR_PRODUCT_LVL_PK
, -1))
AND MSI.SR_INSTANCE_ID = MDSE.INSTANCE
AND DS.LAST_REVISION = MDSR.REVISION
AND MDSR.DEMAND_PLAN_ID = DS.DEMAND_PLAN_ID
AND MDSR.SCENARIO_ID = DS.SCENARIO_ID
AND MDP.DEMAND_PLAN_ID = DS.DEMAND_PLAN_ID

Columns