eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.MSD_ROLL_DEMAND_PLAN SQL Statements

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

Line: 8


  PROCEDURE update_parameters ( p_demand_plan_id IN NUMBER, lv_parameter_id IN NUMBER,lv_parameter_type IN VARCHAR2,
                                lv_parameter_name IN VARCHAR2,lv_forecast_date_used IN VARCHAR2,lv_input_demand_plan_id IN NUMBER,
                                lv_input_scenario_id IN NUMBER,p_period_type IN NUMBER,p_number_of_periods IN NUMBER );

Line: 11


  PROCEDURE update_scenarios  (p_demand_plan_id IN NUMBER,lv_scenario_id IN NUMBER,lv_scenario_name VARCHAR2,
                               lv_exclude_flag IN VARCHAR2 ,p_period_type IN NUMBER, p_number_of_periods IN NUMBER);

Line: 22


  PROCEDURE update_parameters ( p_demand_plan_id        IN   NUMBER,
                                lv_parameter_id         IN   NUMBER,
                                lv_parameter_type       IN  VARCHAR2,
                                lv_parameter_name       IN  VARCHAR2,
                                lv_forecast_date_used   IN  VARCHAR2,
                                lv_input_demand_plan_id IN   NUMBER,
                                lv_input_scenario_id    IN   NUMBER,
                                p_period_type           IN   NUMBER,
                                p_number_of_periods     IN   NUMBER ) AS

   lv_forecast_name         MSD_DP_SCENARIO_REVISIONS.REVISION_NAME%TYPE;

Line: 37


    SELECT revision_name, revision
    FROM   msd_dp_scenario_revisions
    WHERE  scenario_id    =   lv_input_scenario_id
    AND demand_plan_id    =   lv_input_demand_plan_id
    AND revision          = (SELECT MAX (TO_NUMBER(revision))
                             FROM msd_dp_scenario_revisions
                             WHERE scenario_id   =  lv_input_scenario_id
                             AND demand_plan_id  =  lv_input_demand_plan_id)
    FOR UPDATE;

Line: 52


        UPDATE msd_dp_parameters
        SET    start_date       =  decode(p_period_type,G_DAY,(start_date + p_number_of_periods),G_WEEK,(start_date + p_number_of_periods*7),ADD_MONTHS(start_date,p_number_of_periods)),
               end_date         =  decode(p_period_type,G_DAY,(end_date + p_number_of_periods), G_WEEK, (end_date + p_number_of_periods*7), ADD_MONTHS(end_date,p_number_of_periods)) ,
               parameter_name   =  decode(lv_parameter_type,G_TYPE_INPUT_SCENARIO,(substr(parameter_name,1,instr( parameter_name,':',instr(parameter_name,':')+1))||lv_forecast_name),parameter_name),
               revision = lv_forecast_revision
        WHERE  parameter_id     =  lv_parameter_id;

Line: 70


        UPDATE  msd_dp_scenarios
        SET     parameter_name                    =  decode(lv_parameter_type,G_TYPE_INPUT_SCENARIO,(substr(parameter_name,1,instr( parameter_name,':',instr(parameter_name,':')+1))||lv_forecast_name),parameter_name)
        WHERE   nvl(parameter_name, NULL_CHAR )   =  nvl(lv_parameter_name, NULL_CHAR )
        AND     nvl(forecast_based_on,NULL_CHAR)  =  nvl(lv_parameter_type,NULL_CHAR)
        AND     nvl(forecast_date_used,NULL_CHAR) =  nvl(lv_forecast_date_used,NULL_CHAR)
        AND     demand_plan_id                    =  p_demand_plan_id;

Line: 86


    END update_parameters;

Line: 94


  PROCEDURE update_scenarios (p_demand_plan_id IN NUMBER,lv_scenario_id IN NUMBER,lv_scenario_name VARCHAR2,lv_exclude_flag IN VARCHAR2,p_period_type IN NUMBER, p_number_of_periods IN NUMBER) AS

  BEGIN


    UPDATE  msd_dp_scenarios
    SET     horizon_start_date   =   decode(nvl(lv_exclude_flag,'N'),'N',decode(p_period_type,G_DAY,(horizon_start_date +
    p_number_of_periods), G_WEEK, (horizon_start_date + p_number_of_periods*7), ADD_MONTHS(horizon_start_date,p_number_of_periods)),horizon_start_date),
            horizon_end_date     =   decode(nvl(lv_exclude_flag,'N'),'N',decode(p_period_type,G_DAY,(horizon_end_date + p_number_of_periods),
	    G_WEEK, (horizon_end_date + p_number_of_periods*7), ADD_MONTHS(horizon_end_date,p_number_of_periods)),horizon_end_date),
            history_start_date   =   decode(nvl(lv_exclude_flag,'N'),'N',decode(p_period_type,G_DAY,(history_start_date +
	    p_number_of_periods), G_WEEK, ( history_start_date + p_number_of_periods*7), ADD_MONTHS(history_start_date,p_number_of_periods)),history_start_date),
            history_end_date     =   decode(nvl(lv_exclude_flag,'N'),'N',decode(p_period_type,G_DAY,(history_end_date + p_number_of_periods),
	    G_WEEK, (history_end_date + p_number_of_periods*7), ADD_MONTHS(history_end_date,p_number_of_periods)),history_end_date)
    WHERE   scenario_id=lv_scenario_id;

Line: 111


        UPDATE  msd_dp_scenarios
        SET  horizon_start_date = decode(trunc(horizon_start_date),trunc(history_end_date),horizon_start_date+1,horizon_start_date)
        WHERE   scenario_id=lv_scenario_id;

Line: 125


  END update_scenarios;

Line: 165


    SELECT parameter_id,parameter_type,parameter_name,forecast_date_used,input_demand_plan_id,input_scenario_id
    FROM   msd_dp_parameters
    WHERE demand_plan_id= p_demand_plan_id
    AND nvl(exclude_from_rolling_cycle,'N') = 'N'
    FOR UPDATE ;

Line: 173


    SELECT s.scenario_id,s.scenario_name,p.exclude_from_rolling_cycle
    FROM   msd_dp_scenarios s, msd_dp_parameters p
    WHERE  s.demand_plan_id                   =   p_demand_plan_id
    AND    p.demand_plan_id                   =   p_demand_plan_id
    AND    nvl(s.parameter_name,NULL_CHAR)    =   nvl(p.parameter_name,NULL_CHAR)
    AND    nvl(s.forecast_based_on,NULL_CHAR) =   p.parameter_type
    AND nvl(s.forecast_date_used,NULL_CHAR)   =   nvl(p.forecast_date_used,NULL_CHAR)
    UNION ALL
    SELECT  s.scenario_id,s.scenario_name,'N'
    FROM    msd_dp_scenarios s
    WHERE   s.demand_plan_id          =    p_demand_plan_id
    AND     s.parameter_name         IS    NULL
    AND     s.forecast_based_on      IS    NULL
    AND s.forecast_date_used         IS    NULL;

Line: 193


    UPDATE msd_demand_plans
    SET valid_flag= G_INVALID_PLAN
    WHERE demand_plan_id=p_demand_plan_id;

Line: 203


        update_parameters( p_demand_plan_id, lv_parameter_id,lv_parameter_type,lv_parameter_name,lv_forecast_date_used,lv_input_demand_plan_id,lv_input_scenario_id ,p_period_type, p_number_of_periods);

Line: 213


       update_scenarios( p_demand_plan_id, lv_scenario_id,lv_scenario_name,lv_exclude_flag, p_period_type,p_number_of_periods );