VIEW: APPS.MSD_DP_SCE_SCENARIOS_V

Source

View Text - Preformatted

SELECT ds.demand_plan_id, dp.demand_plan_name, ds.scenario_id, ds.scenario_name, dp.organization_id, dp.sr_instance_id FROM msd_dp_scenarios ds, msd_demand_plans dp WHERE ds.demand_plan_id = dp.demand_plan_id and ds.last_revision is not null and /* item level */ exists(select 1 from msd_dp_scenario_output_levels ol where ol.demand_plan_id = ds.demand_plan_id and ol.scenario_id = ds.scenario_id and ol.level_id = 1) and /* organization level */ exists(select 1 from msd_dp_scenario_output_levels ol where ol.demand_plan_id = ds.demand_plan_id and ol.scenario_id = ds.scenario_id and ol.level_id = 7) and /* geography level */ exists(select 1 from msd_dp_scenario_output_levels ol where ol.demand_plan_id = ds.demand_plan_id and ol.scenario_id = ds.scenario_id and ol.level_id = 11) UNION ALL SELECT 5555555 demand_plan_id, 'Demantra' demand_plan_name, tq.id + 5555555 scenario_id, SUBSTR(tq.query_name, 1, 30) scenario_name, -23453 organization_id, msd_dem_upload_forecast.get_sr_instance_id_for_profile(tq.id) sr_instance_id FROM msd_dem_transfer_list tl, msd_dem_transfer_query tq WHERE tl.id = tq.transfer_id AND tq.integration_type <> 1 AND tq.export_type = 1 AND tq.presentation_type = 1 AND msd_dem_upload_forecast.is_valid_scenario(tq.id) = 1 AND msd_dem_upload_forecast.upload_to_cp(tq.id) = 1

View Text - HTML Formatted

SELECT DS.DEMAND_PLAN_ID
, DP.DEMAND_PLAN_NAME
, DS.SCENARIO_ID
, DS.SCENARIO_NAME
, DP.ORGANIZATION_ID
, DP.SR_INSTANCE_ID
FROM MSD_DP_SCENARIOS DS
, MSD_DEMAND_PLANS DP
WHERE DS.DEMAND_PLAN_ID = DP.DEMAND_PLAN_ID
AND DS.LAST_REVISION IS NOT NULL
AND /* ITEM LEVEL */ EXISTS(SELECT 1
FROM MSD_DP_SCENARIO_OUTPUT_LEVELS OL
WHERE OL.DEMAND_PLAN_ID = DS.DEMAND_PLAN_ID
AND OL.SCENARIO_ID = DS.SCENARIO_ID
AND OL.LEVEL_ID = 1)
AND /* ORGANIZATION LEVEL */ EXISTS(SELECT 1
FROM MSD_DP_SCENARIO_OUTPUT_LEVELS OL
WHERE OL.DEMAND_PLAN_ID = DS.DEMAND_PLAN_ID
AND OL.SCENARIO_ID = DS.SCENARIO_ID
AND OL.LEVEL_ID = 7)
AND /* GEOGRAPHY LEVEL */ EXISTS(SELECT 1
FROM MSD_DP_SCENARIO_OUTPUT_LEVELS OL
WHERE OL.DEMAND_PLAN_ID = DS.DEMAND_PLAN_ID
AND OL.SCENARIO_ID = DS.SCENARIO_ID
AND OL.LEVEL_ID = 11) UNION ALL SELECT 5555555 DEMAND_PLAN_ID
, 'DEMANTRA' DEMAND_PLAN_NAME
, TQ.ID + 5555555 SCENARIO_ID
, SUBSTR(TQ.QUERY_NAME
, 1
, 30) SCENARIO_NAME
, -23453 ORGANIZATION_ID
, MSD_DEM_UPLOAD_FORECAST.GET_SR_INSTANCE_ID_FOR_PROFILE(TQ.ID) SR_INSTANCE_ID
FROM MSD_DEM_TRANSFER_LIST TL
, MSD_DEM_TRANSFER_QUERY TQ
WHERE TL.ID = TQ.TRANSFER_ID
AND TQ.INTEGRATION_TYPE <> 1
AND TQ.EXPORT_TYPE = 1
AND TQ.PRESENTATION_TYPE = 1
AND MSD_DEM_UPLOAD_FORECAST.IS_VALID_SCENARIO(TQ.ID) = 1
AND MSD_DEM_UPLOAD_FORECAST.UPLOAD_TO_CP(TQ.ID) = 1