APPS.MSD_WS_DEM_RENAME_FORECAST SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 29

       *      Update the scenario id in the MSD_DP_SCN_ENTRIES_DENORM table to the Scenario Id generated
       *      for the given Plan Name.
       *
       *   Return Values -
       *      The procedure returns a status. The possible return statuses are:
       *         SUCCESS, ERROR, INVALID_DATA_PROFILE
       *
       */
       PROCEDURE ASSIGN_PLAN_NAME_TO_FORECAST (
                   status		OUT NOCOPY 	VARCHAR2,
                   NewPlanName		IN		VARCHAR2,
                   DataProfileName	IN		VARCHAR2,
                   ArchiveFlag          IN              NUMBER)
       IS

          TYPE CUR_TYPE	IS REF CURSOR;

Line: 70

          x_sql_stmt := 'SELECT nvl(sum(tq.id), 0) '
                        || ' FROM msd_dem_transfer_query tq '
                        || ' WHERE tq.query_name = ''' || x_data_profile_name || ''''
                        || '   AND msd_dem_upload_forecast.is_valid_scenario(tq.id) = 1 ';

Line: 82

             x_sql_stmt := 'SELECT nvl(sum(scenario_id), 0) '
                           || ' FROM msd_dp_scenarios mds '
                           || ' WHERE mds.demand_plan_id = ' || MSD_DEM_UPLOAD_FORECAST.C_DEMAND_PLAN_ID
                           || '   AND mds.scenario_name  = ''' || x_new_plan_name || '''';

Line: 89

            /* Create/Update an entry for the Plan Name in the table msd_dp_scenarios */
            IF (x_scenario_id = 0) THEN

               SELECT MSD_DP_SCENARIOS_S.nextval
                  INTO x_scenario_id
                  FROM DUAL;

Line: 97

               INSERT INTO msd_dp_scenarios (
               		demand_plan_id,
               		scenario_id,
               		scenario_name,
               		forecast_based_on,
               		sc_type,
               		error_type,
               		associate_parameter,
               		last_update_date,
               		last_updated_by,
               		creation_date,
               		created_by )
               		VALUES (
               		   MSD_DEM_UPLOAD_FORECAST.C_DEMAND_PLAN_ID,
               		   x_scenario_id,
               		   x_new_plan_name,
               		   substr(x_data_profile_name,1,30),
               		   msd_dem_upload_forecast.is_global_scenario(x_data_profile_id),
               		   msd_dem_upload_forecast.get_error_type(x_data_profile_id),
               		   C_ASSOCIATE_PARAMETER,
               		   sysdate,
               		   FND_GLOBAL.USER_ID,
               		   sysdate,
               		   FND_GLOBAL.USER_ID);

Line: 124

               UPDATE msd_dp_scenarios
               SET
                  forecast_based_on = substr(x_data_profile_name,1,30),
                  sc_type = msd_dem_upload_forecast.is_global_scenario(x_data_profile_id),
                  error_type = msd_dem_upload_forecast.get_error_type(x_data_profile_id),
                  last_update_date = sysdate,
                  last_updated_by = FND_GLOBAL.USER_ID
               WHERE scenario_id = x_scenario_id;

Line: 133

               DELETE FROM msd_dp_scenario_output_levels
               WHERE scenario_id = x_scenario_id;

Line: 136

               DELETE FROM msd_dp_scn_entries_denorm
               WHERE scenario_id = x_scenario_id;

Line: 143

            INSERT INTO msd_dp_scenario_output_levels (
            	demand_plan_id,
            	scenario_id,
            	level_id,
            	last_update_date,
            	last_updated_by,
            	creation_date,
            	created_by )
            	SELECT
            	   MSD_DEM_UPLOAD_FORECAST.C_DEMAND_PLAN_ID,
            	   x_scenario_id,
            	   to_number(flv.lookup_code),
            	   sysdate,
               	   FND_GLOBAL.USER_ID,
               	   sysdate,
               	   FND_GLOBAL.USER_ID
               	FROM
                   msd_dem_transfer_query tq,
                   msd_dem_transfer_query_levels tql,
                   msd_dem_group_tables gt,
                   fnd_lookup_values_vl flv
                WHERE
                       tq.id = x_data_profile_id
                   AND tql.id = tq.id
                   AND gt.group_table_id = tql.level_id
                   AND flv.lookup_type = 'MSD_DEM_LEVELS'
                   AND upper(flv.meaning) = upper(gt.table_label);

Line: 172

            /* Update the scenario id in the denorm table */
            UPDATE msd_dp_scn_entries_denorm
               SET scenario_id = x_scenario_id
               WHERE scenario_id = MSD_DEM_UPLOAD_FORECAST.C_SCENARIO_ID_OFFSET + x_data_profile_id;

Line: 305

          EXECUTE IMMEDIATE ' SELECT supply_plan_id '
                            || '    FROM ' || fnd_profile.value('MSD_DEM_SCHEMA') || '.supply_plan sp'
                            || '    WHERE sp.plan_id = ' || PlanId
             INTO x_member_id;

Line: 375

   SELECT object_name into x_mview
   FROM dba_objects
   WHERE owner = x_schema
    AND object_type = 'MATERIALIZED VIEW'
    AND object_name = upper(mviewname)
   ORDER BY created DESC;