eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.MSD_DP_ASCP_SCN_ENTRIES_V

Source

View Text - Preformatted


SELECT /*+ INDEX(mdse MSD_DP_SCN_ENTRIES_N1)*/ 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, null, mdse.total_quantity_uom, msi.uom_code)), nvl(mdp.roundoff_decimal_places, 6)) quantity , to_number(decode (mdse.organization_lvl_id, 7, decode(mdp.use_org_specific_bom_flag, 'N',-1, decode(ltrim(sr_organization_lvl_pk, '.0123456789'), null, sr_organization_lvl_pk, -1)),-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, null)), null), /* sr_customer_id */ 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, null)), 41, msd_common_utilities.get_parent_level_pk(mdse.instance, 11, 15, msd_common_utilities.get_child_level_pk(mdse.instance, 11, 41, mdse.sr_geography_lvl_pk)), null), /* sr_zone_id */ decode(mdse.geography_lvl_id, 41, msd_common_utilities.get_parent_level_pk(mdse.instance, 41, 42, mdse.sr_geography_lvl_pk), 42, to_number(decode(ltrim(mdse.sr_geography_lvl_pk, '.0123456789'), null, mdse.sr_geography_lvl_pk, null)), null), mdse.Forecast_Priority, mdse.total_quantity_uom, msi.uom_code, decode(mdse.demand_class_lvl_id, 40, null, 34, decode(mdse.demand_class, '-777', null, mdse.demand_class), mdse.demand_class), /* Bug# 4771455 */ nvl(abs(round(mdse.amount/decode(mdse.total_quantity, 0, NULL, mdse.total_quantity), 2)), msi.list_price * ( (100-msi.average_discount)/100)), /* Bug# 5248221*/ mdse.time_lvl_id, ds.dmd_priority_scenario_id FROM msc_system_items msi, msd_dp_scenario_entries mdse, msd_dp_scenarios ds, msd_dp_scenario_revisions mdsr, msd_demand_plans mdp, msc_apps_instances mai WHERE mdse.demand_plan_id = ds.demand_plan_id and mdse.scenario_id = ds.scenario_id and /* Bug# 5248221*/ mdse.revision = ds.last_revision and /* mdse.time_lvl_id in (9, 1, 2) and */ /* mdse.ORGANIZATION_LVL_ID = 7 and */ mdse.PRODUCT_LVL_ID in (1,3) /* and mdse.GEOGRAPHY_LVL_ID in (11, 15) */ and msi.plan_id = -1 and /* Bug# 5248221*/ msi.sr_instance_id = mdse.instance and msi.organization_id = decode(mdp.use_org_specific_bom_flag, 'N', mai.validation_org_id, null, decode(mdse.organization_lvl_id, 7, to_number(decode(ltrim(sr_organization_lvl_pk,'.0123456789'), null, sr_organization_lvl_pk, -1)), mai.validation_org_id), 'Y', decode(mdse.organization_lvl_id, 7, to_number(decode(ltrim(sr_organization_lvl_pk,'.0123456789'), null, sr_organization_lvl_pk, -1)), -1), -1) and msi.sr_inventory_item_id = to_number(decode(ltrim(sr_product_lvl_pk, '.0123456789'), null, sr_product_lvl_pk, -1)) and (mdse.organization_lvl_id = 7 or nvl(mdp.use_org_specific_bom_flag, 'N') = 'N') 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 and msi.mrp_planning_code <> 6 and msi.sr_instance_id = mai.instance_id and mai.instance_id = mdse.instance and nvl(msi.ato_forecast_control, 3) <> 3 and nvl(mdp.plan_type, 'DP') <> 'LIABILITY' /* Bug# 4771455 */ AND decode( mdse.total_quantity, 0, decode( mdsr.error_type, 'MAD', decode( nvl( mdse.forecast_error, 0), 0, -1, 1), -1), 1) = 1 UNION ALL SELECT dem.demand_plan_id, dem.scenario_id, dem.demand_id, dem.bucket_type, dem.start_time, dem.end_time, dem.quantity, dem.sr_organization_id, dem.sr_instance_id, dem.sr_inventory_item_id, dem.error_type, dem.forecast_error, dem.inventory_item_id, dem.sr_ship_to_loc_id, dem.sr_customer_id, dem.sr_zone_id, dem.priority, dem.dp_uom_code, dem.ascp_uom_code, dem.demand_class, dem.unit_price, to_number(null), to_number(null) FROM msd_dp_scn_entries_denorm dem WHERE dem.demand_plan_id = 5555555

View Text - HTML Formatted

SELECT /*+ INDEX(MDSE MSD_DP_SCN_ENTRIES_N1)*/ 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
, NULL
, MDSE.TOTAL_QUANTITY_UOM
, MSI.UOM_CODE))
, NVL(MDP.ROUNDOFF_DECIMAL_PLACES
, 6)) QUANTITY
, TO_NUMBER(DECODE (MDSE.ORGANIZATION_LVL_ID
, 7
, DECODE(MDP.USE_ORG_SPECIFIC_BOM_FLAG
, 'N'
, -1
, DECODE(LTRIM(SR_ORGANIZATION_LVL_PK
, '.0123456789')
, NULL
, SR_ORGANIZATION_LVL_PK
, -1))
, -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
, NULL))
, NULL)
, /* SR_CUSTOMER_ID */ 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
, NULL))
, 41
, MSD_COMMON_UTILITIES.GET_PARENT_LEVEL_PK(MDSE.INSTANCE
, 11
, 15
, MSD_COMMON_UTILITIES.GET_CHILD_LEVEL_PK(MDSE.INSTANCE
, 11
, 41
, MDSE.SR_GEOGRAPHY_LVL_PK))
, NULL)
, /* SR_ZONE_ID */ DECODE(MDSE.GEOGRAPHY_LVL_ID
, 41
, MSD_COMMON_UTILITIES.GET_PARENT_LEVEL_PK(MDSE.INSTANCE
, 41
, 42
, MDSE.SR_GEOGRAPHY_LVL_PK)
, 42
, TO_NUMBER(DECODE(LTRIM(MDSE.SR_GEOGRAPHY_LVL_PK
, '.0123456789')
, NULL
, MDSE.SR_GEOGRAPHY_LVL_PK
, NULL))
, NULL)
, MDSE.FORECAST_PRIORITY
, MDSE.TOTAL_QUANTITY_UOM
, MSI.UOM_CODE
, DECODE(MDSE.DEMAND_CLASS_LVL_ID
, 40
, NULL
, 34
, DECODE(MDSE.DEMAND_CLASS
, '-777'
, NULL
, MDSE.DEMAND_CLASS)
, MDSE.DEMAND_CLASS)
, /* BUG# 4771455 */ NVL(ABS(ROUND(MDSE.AMOUNT/DECODE(MDSE.TOTAL_QUANTITY
, 0
, NULL
, MDSE.TOTAL_QUANTITY)
, 2))
, MSI.LIST_PRICE * ( (100-MSI.AVERAGE_DISCOUNT)/100))
, /* BUG# 5248221*/ MDSE.TIME_LVL_ID
, DS.DMD_PRIORITY_SCENARIO_ID
FROM MSC_SYSTEM_ITEMS MSI
, MSD_DP_SCENARIO_ENTRIES MDSE
, MSD_DP_SCENARIOS DS
, MSD_DP_SCENARIO_REVISIONS MDSR
, MSD_DEMAND_PLANS MDP
, MSC_APPS_INSTANCES MAI
WHERE MDSE.DEMAND_PLAN_ID = DS.DEMAND_PLAN_ID
AND MDSE.SCENARIO_ID = DS.SCENARIO_ID
AND /* BUG# 5248221*/ MDSE.REVISION = DS.LAST_REVISION
AND /* MDSE.TIME_LVL_ID IN (9
, 1
, 2)
AND */ /* MDSE.ORGANIZATION_LVL_ID = 7
AND */ MDSE.PRODUCT_LVL_ID IN (1
, 3) /*
AND MDSE.GEOGRAPHY_LVL_ID IN (11
, 15) */
AND MSI.PLAN_ID = -1
AND /* BUG# 5248221*/ MSI.SR_INSTANCE_ID = MDSE.INSTANCE
AND MSI.ORGANIZATION_ID = DECODE(MDP.USE_ORG_SPECIFIC_BOM_FLAG
, 'N'
, MAI.VALIDATION_ORG_ID
, NULL
, DECODE(MDSE.ORGANIZATION_LVL_ID
, 7
, TO_NUMBER(DECODE(LTRIM(SR_ORGANIZATION_LVL_PK
, '.0123456789')
, NULL
, SR_ORGANIZATION_LVL_PK
, -1))
, MAI.VALIDATION_ORG_ID)
, 'Y'
, DECODE(MDSE.ORGANIZATION_LVL_ID
, 7
, TO_NUMBER(DECODE(LTRIM(SR_ORGANIZATION_LVL_PK
, '.0123456789')
, NULL
, SR_ORGANIZATION_LVL_PK
, -1))
, -1)
, -1)
AND MSI.SR_INVENTORY_ITEM_ID = TO_NUMBER(DECODE(LTRIM(SR_PRODUCT_LVL_PK
, '.0123456789')
, NULL
, SR_PRODUCT_LVL_PK
, -1))
AND (MDSE.ORGANIZATION_LVL_ID = 7 OR NVL(MDP.USE_ORG_SPECIFIC_BOM_FLAG
, 'N') = 'N')
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
AND MSI.MRP_PLANNING_CODE <> 6
AND MSI.SR_INSTANCE_ID = MAI.INSTANCE_ID
AND MAI.INSTANCE_ID = MDSE.INSTANCE
AND NVL(MSI.ATO_FORECAST_CONTROL
, 3) <> 3
AND NVL(MDP.PLAN_TYPE
, 'DP') <> 'LIABILITY' /* BUG# 4771455 */
AND DECODE( MDSE.TOTAL_QUANTITY
, 0
, DECODE( MDSR.ERROR_TYPE
, 'MAD'
, DECODE( NVL( MDSE.FORECAST_ERROR
, 0)
, 0
, -1
, 1)
, -1)
, 1) = 1 UNION ALL SELECT DEM.DEMAND_PLAN_ID
, DEM.SCENARIO_ID
, DEM.DEMAND_ID
, DEM.BUCKET_TYPE
, DEM.START_TIME
, DEM.END_TIME
, DEM.QUANTITY
, DEM.SR_ORGANIZATION_ID
, DEM.SR_INSTANCE_ID
, DEM.SR_INVENTORY_ITEM_ID
, DEM.ERROR_TYPE
, DEM.FORECAST_ERROR
, DEM.INVENTORY_ITEM_ID
, DEM.SR_SHIP_TO_LOC_ID
, DEM.SR_CUSTOMER_ID
, DEM.SR_ZONE_ID
, DEM.PRIORITY
, DEM.DP_UOM_CODE
, DEM.ASCP_UOM_CODE
, DEM.DEMAND_CLASS
, DEM.UNIT_PRICE
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
FROM MSD_DP_SCN_ENTRIES_DENORM DEM
WHERE DEM.DEMAND_PLAN_ID = 5555555