SELECT DS.SCENARIO_ID , DP.DEMAND_PLAN_NAME||':'||DS.SCENARIO_NAME FROM MSD_DP_SCENARIOS DS , MSD_DEMAND_PLANS DP WHERE DS.DEMAND_PLAN_ID = DP.DEMAND_PLAN_ID AND DS.PUBLISH_FLAG = 'Y' /* ITEM OR PRODUCT FAMILY LEVEL */ AND 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 IN (1 , 3)) /* ORGANIZATION LEVEL */ AND 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)