VIEW: APPS.MSD_DP_SCE_SCN_ENTRIES_V

Source

View Text - Preformatted

SELECT mdse.demand_plan_id, mdse.scenario_id, mdse.time_lvl_id, mdse.time_lvl_val_from, mdse.time_lvl_val_to, mdse.total_quantity, to_number(decode(ltrim(mdse.sr_organization_lvl_pk, '.0123456789'), null, mdse.sr_organization_lvl_pk, -1)) sr_organization_lvl_pk, to_number(decode(ltrim(mdse.sr_geography_lvl_pk, '.0123456789'), null, mdse.sr_geography_lvl_pk, -1)) sr_geography_lvl_pk, to_number(decode(ltrim(mdse.sr_product_lvl_pk, '.0123456789'), null, mdse.sr_product_lvl_pk, -1)) sr_product_lvl_pk, to_number(mdse.instance), mdse.total_quantity_uom FROM msd_dp_scenario_entries mdse, msd_dp_scenarios ds WHERE mdse.demand_plan_id = ds.demand_plan_id and mdse.scenario_id = ds.scenario_id and mdse.ORGANIZATION_LVL_ID = 7 and mdse.PRODUCT_LVL_ID = 1 and mdse.GEOGRAPHY_LVL_ID = 11 and mdse.revision = ds.last_revision UNION ALL SELECT fcst.demand_plan_id DEMAND_PLAN_ID, fcst.scenario_id SCENARIO_ID , fcst.bucket_type BUCKET_TYPE, fcst.start_time START_DATE, fcst.end_time END_DATE, fcst.quantity QUANTITY , fcst.sr_organization_id sr_organization_id, fcst.sr_ship_to_loc_id sr_geography_id, fcst.sr_inventory_item_id sr_inventory_item_id, fcst.sr_instance_id sr_instance_id, decode(fcst.dp_uom_code, 'units', fcst.ascp_uom_code, SUBSTR(fcst.dp_uom_code,1,3)) uom_code FROM msd_dp_scn_entries_denorm fcst, msd_dp_scn_output_levels_v item_level, msd_dp_scn_output_levels_v org_level, msd_dp_scn_output_levels_v geo_level WHERE fcst.demand_plan_id = 5555555 AND item_level.demand_plan_id = 5555555 AND fcst.scenario_id = item_level.scenario_id AND item_level.level_id = 1 AND org_level.demand_plan_id = 5555555 AND fcst.scenario_id = org_level.scenario_id AND org_level.level_id = 7 AND geo_level.demand_plan_id = 5555555 AND fcst.scenario_id = geo_level.scenario_id AND geo_level.level_id = 11

View Text - HTML Formatted

SELECT MDSE.DEMAND_PLAN_ID
, MDSE.SCENARIO_ID
, MDSE.TIME_LVL_ID
, MDSE.TIME_LVL_VAL_FROM
, MDSE.TIME_LVL_VAL_TO
, MDSE.TOTAL_QUANTITY
, TO_NUMBER(DECODE(LTRIM(MDSE.SR_ORGANIZATION_LVL_PK
, '.0123456789')
, NULL
, MDSE.SR_ORGANIZATION_LVL_PK
, -1)) SR_ORGANIZATION_LVL_PK
, TO_NUMBER(DECODE(LTRIM(MDSE.SR_GEOGRAPHY_LVL_PK
, '.0123456789')
, NULL
, MDSE.SR_GEOGRAPHY_LVL_PK
, -1)) SR_GEOGRAPHY_LVL_PK
, TO_NUMBER(DECODE(LTRIM(MDSE.SR_PRODUCT_LVL_PK
, '.0123456789')
, NULL
, MDSE.SR_PRODUCT_LVL_PK
, -1)) SR_PRODUCT_LVL_PK
, TO_NUMBER(MDSE.INSTANCE)
, MDSE.TOTAL_QUANTITY_UOM
FROM MSD_DP_SCENARIO_ENTRIES MDSE
, MSD_DP_SCENARIOS DS
WHERE MDSE.DEMAND_PLAN_ID = DS.DEMAND_PLAN_ID
AND MDSE.SCENARIO_ID = DS.SCENARIO_ID
AND MDSE.ORGANIZATION_LVL_ID = 7
AND MDSE.PRODUCT_LVL_ID = 1
AND MDSE.GEOGRAPHY_LVL_ID = 11
AND MDSE.REVISION = DS.LAST_REVISION UNION ALL SELECT FCST.DEMAND_PLAN_ID DEMAND_PLAN_ID
, FCST.SCENARIO_ID SCENARIO_ID
, FCST.BUCKET_TYPE BUCKET_TYPE
, FCST.START_TIME START_DATE
, FCST.END_TIME END_DATE
, FCST.QUANTITY QUANTITY
, FCST.SR_ORGANIZATION_ID SR_ORGANIZATION_ID
, FCST.SR_SHIP_TO_LOC_ID SR_GEOGRAPHY_ID
, FCST.SR_INVENTORY_ITEM_ID SR_INVENTORY_ITEM_ID
, FCST.SR_INSTANCE_ID SR_INSTANCE_ID
, DECODE(FCST.DP_UOM_CODE
, 'UNITS'
, FCST.ASCP_UOM_CODE
, SUBSTR(FCST.DP_UOM_CODE
, 1
, 3)) UOM_CODE
FROM MSD_DP_SCN_ENTRIES_DENORM FCST
, MSD_DP_SCN_OUTPUT_LEVELS_V ITEM_LEVEL
, MSD_DP_SCN_OUTPUT_LEVELS_V ORG_LEVEL
, MSD_DP_SCN_OUTPUT_LEVELS_V GEO_LEVEL
WHERE FCST.DEMAND_PLAN_ID = 5555555
AND ITEM_LEVEL.DEMAND_PLAN_ID = 5555555
AND FCST.SCENARIO_ID = ITEM_LEVEL.SCENARIO_ID
AND ITEM_LEVEL.LEVEL_ID = 1
AND ORG_LEVEL.DEMAND_PLAN_ID = 5555555
AND FCST.SCENARIO_ID = ORG_LEVEL.SCENARIO_ID
AND ORG_LEVEL.LEVEL_ID = 7
AND GEO_LEVEL.DEMAND_PLAN_ID = 5555555
AND FCST.SCENARIO_ID = GEO_LEVEL.SCENARIO_ID
AND GEO_LEVEL.LEVEL_ID = 11