VIEW: APPS.MSD_DP_SCN_OUTPUT_LEVELS_V

Source

View Text - Preformatted

SELECT mdsol.ROWID, mdsol.DEMAND_PLAN_ID, mddv.DEMAND_PLAN_NAME, mdsol.SCENARIO_ID , mdsv.SCENARIO_NAME, mdsv.DESCRIPTION, mdsv.OUTPUT_PERIOD_TYPE_ID, mdsv.OUTPUT_PERIOD_TYPE, mdsv.HORIZON_START_DATE, mdsv.HORIZON_END_DATE, mdsv.FORECAST_DATE_USED_ID, mdsv.FORECAST_DATE_USED, mdsv.FORECAST_BASED_ON_ID, mdsv.FORECAST_BASED_ON, mdsv.PRICE_LIST_NAME, mdsol.LEVEL_ID, mlv.LEVEL_NAME, mddv.DP_DIMENSION_CODE, mddv.DP_DIMENSION, mlv.DIMENSION_CODE, mddv.DIMENSION, mdsol.LAST_UPDATE_DATE, mdsol.LAST_UPDATED_BY, mdsol.CREATION_DATE, mdsol.CREATED_BY, mdsol.LAST_UPDATE_LOGIN, mdsol.REQUEST_ID, mdsol.PROGRAM_APPLICATION_ID, mdsol.PROGRAM_ID, mdsol.PROGRAM_UPDATE_DATE, mdsol.DELETEABLE_FLAG , mdsol.ENABLE_NONSEED_FLAG from msd_dp_dimensions_v mddv, msd_levels mlv, msd_dp_scenarios_cs_v mdsv, msd_dp_scenario_output_levels mdsol, msd_demand_plans mdp WHERE mddv.demand_plan_id = mdsol.demand_plan_id and mddv.demand_plan_id = mdp.demand_plan_id and mlv.level_id = mdsol.level_id and mddv.dimension_code = mlv.dimension_code and mdsv.demand_plan_id = mdsol.demand_plan_id and mdsv.scenario_id = mdsol.scenario_id and nvl(mlv.plan_type,'DP') = DECODE(mdp.plan_type,'SOP','DP', 'EOL', 'DP','','DP',mdp.plan_type) UNION ALL SELECT null, 5555555, tl.name, tq.id + 5555555, tq.query_name, null, null, null, null, null, null, null, null, null, null, to_number(flv.lookup_code), null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null FROM msd_dem_transfer_list tl, msd_dem_transfer_query tq, msd_dem_transfer_query_levels tql, msd_dem_group_tables gt, fnd_lookup_values_vl flv WHERE tq.transfer_id = tl.id AND tql.id = tq.id AND gt.group_table_id = tql.level_id AND flv.lookup_type = 'MSD_DEM_LEVELS' AND to_number(msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', flv.meaning, 1, 'group_table_id')) = gt.group_table_id UNION ALL SELECT null, mds.demand_plan_id, substr(mds.forecast_based_on,1,30), mds.scenario_id, mds.scenario_name, null, null, null, null, null, null, null, null, null, null, mdsol.level_id, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null FROM msd_dp_scenarios mds, msd_dp_scenario_output_levels mdsol WHERE mds.demand_plan_id = 5555555 AND mdsol.demand_plan_id = mds.demand_plan_id AND mdsol.scenario_id = mds.scenario_id

View Text - HTML Formatted

SELECT MDSOL.ROWID
, MDSOL.DEMAND_PLAN_ID
, MDDV.DEMAND_PLAN_NAME
, MDSOL.SCENARIO_ID
, MDSV.SCENARIO_NAME
, MDSV.DESCRIPTION
, MDSV.OUTPUT_PERIOD_TYPE_ID
, MDSV.OUTPUT_PERIOD_TYPE
, MDSV.HORIZON_START_DATE
, MDSV.HORIZON_END_DATE
, MDSV.FORECAST_DATE_USED_ID
, MDSV.FORECAST_DATE_USED
, MDSV.FORECAST_BASED_ON_ID
, MDSV.FORECAST_BASED_ON
, MDSV.PRICE_LIST_NAME
, MDSOL.LEVEL_ID
, MLV.LEVEL_NAME
, MDDV.DP_DIMENSION_CODE
, MDDV.DP_DIMENSION
, MLV.DIMENSION_CODE
, MDDV.DIMENSION
, MDSOL.LAST_UPDATE_DATE
, MDSOL.LAST_UPDATED_BY
, MDSOL.CREATION_DATE
, MDSOL.CREATED_BY
, MDSOL.LAST_UPDATE_LOGIN
, MDSOL.REQUEST_ID
, MDSOL.PROGRAM_APPLICATION_ID
, MDSOL.PROGRAM_ID
, MDSOL.PROGRAM_UPDATE_DATE
, MDSOL.DELETEABLE_FLAG
, MDSOL.ENABLE_NONSEED_FLAG
FROM MSD_DP_DIMENSIONS_V MDDV
, MSD_LEVELS MLV
, MSD_DP_SCENARIOS_CS_V MDSV
, MSD_DP_SCENARIO_OUTPUT_LEVELS MDSOL
, MSD_DEMAND_PLANS MDP
WHERE MDDV.DEMAND_PLAN_ID = MDSOL.DEMAND_PLAN_ID
AND MDDV.DEMAND_PLAN_ID = MDP.DEMAND_PLAN_ID
AND MLV.LEVEL_ID = MDSOL.LEVEL_ID
AND MDDV.DIMENSION_CODE = MLV.DIMENSION_CODE
AND MDSV.DEMAND_PLAN_ID = MDSOL.DEMAND_PLAN_ID
AND MDSV.SCENARIO_ID = MDSOL.SCENARIO_ID
AND NVL(MLV.PLAN_TYPE
, 'DP') = DECODE(MDP.PLAN_TYPE
, 'SOP'
, 'DP'
, 'EOL'
, 'DP'
, ''
, 'DP'
, MDP.PLAN_TYPE) UNION ALL SELECT NULL
, 5555555
, TL.NAME
, TQ.ID + 5555555
, TQ.QUERY_NAME
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, TO_NUMBER(FLV.LOOKUP_CODE)
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
FROM MSD_DEM_TRANSFER_LIST TL
, MSD_DEM_TRANSFER_QUERY TQ
, MSD_DEM_TRANSFER_QUERY_LEVELS TQL
, MSD_DEM_GROUP_TABLES GT
, FND_LOOKUP_VALUES_VL FLV
WHERE TQ.TRANSFER_ID = TL.ID
AND TQL.ID = TQ.ID
AND GT.GROUP_TABLE_ID = TQL.LEVEL_ID
AND FLV.LOOKUP_TYPE = 'MSD_DEM_LEVELS'
AND TO_NUMBER(MSD_DEM_COMMON_UTILITIES.GET_APP_ID_TEXT('MSD_DEM_DEMANTRA_OBJECT_ID'
, FLV.MEANING
, 1
, 'GROUP_TABLE_ID')) = GT.GROUP_TABLE_ID UNION ALL SELECT NULL
, MDS.DEMAND_PLAN_ID
, SUBSTR(MDS.FORECAST_BASED_ON
, 1
, 30)
, MDS.SCENARIO_ID
, MDS.SCENARIO_NAME
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, MDSOL.LEVEL_ID
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
FROM MSD_DP_SCENARIOS MDS
, MSD_DP_SCENARIO_OUTPUT_LEVELS MDSOL
WHERE MDS.DEMAND_PLAN_ID = 5555555
AND MDSOL.DEMAND_PLAN_ID = MDS.DEMAND_PLAN_ID
AND MDSOL.SCENARIO_ID = MDS.SCENARIO_ID