1 PACKAGE BODY msd_roll_demand_plan AS
2 /* $Header: msddprlb.pls 115.3 2003/06/20 17:50:31 jarora ship $ */
3
4
5 v_errtxt VARCHAR2(300);
6
7 -- declaring the private procedures
8 PROCEDURE update_parameters ( p_demand_plan_id IN NUMBER, lv_parameter_id IN NUMBER,lv_parameter_type IN VARCHAR2,
9 lv_parameter_name IN VARCHAR2,lv_forecast_date_used IN VARCHAR2,lv_input_demand_plan_id IN NUMBER,
10 lv_input_scenario_id IN NUMBER,p_period_type IN NUMBER,p_number_of_periods IN NUMBER );
11 PROCEDURE update_scenarios (p_demand_plan_id IN NUMBER,lv_scenario_id IN NUMBER,lv_scenario_name VARCHAR2,
12 lv_exclude_flag IN VARCHAR2 ,p_period_type IN NUMBER, p_number_of_periods IN NUMBER);
13
14
15 /*========================================================================================+
16 | DESCRIPTION : This procedure is called to roll forward the start and end dates of |
17 | parameters and to attach the latest forecast version. |
18 | It also changes the parameter_name in msd_dp_scenarios table for latest |
19 | forecasts versions attached. |
20 +========================================================================================*/
21
22 PROCEDURE update_parameters ( p_demand_plan_id IN NUMBER,
23 lv_parameter_id IN NUMBER,
24 lv_parameter_type IN VARCHAR2,
25 lv_parameter_name IN VARCHAR2,
26 lv_forecast_date_used IN VARCHAR2,
27 lv_input_demand_plan_id IN NUMBER,
28 lv_input_scenario_id IN NUMBER,
29 p_period_type IN NUMBER,
30 p_number_of_periods IN NUMBER ) AS
31
32 lv_forecast_name MSD_DP_SCENARIO_REVISIONS.REVISION_NAME%TYPE;
33
34 -- This cursor is used to select the latest forecast version for the dynamic parameter of type input scenario.
35 CURSOR latest_forecast_version IS
36 SELECT revision_name
37 FROM msd_dp_scenario_revisions
38 WHERE scenario_id = lv_input_scenario_id
39 AND demand_plan_id = lv_input_demand_plan_id
40 AND revision = (SELECT MAX (TO_NUMBER(revision))
41 FROM msd_dp_scenario_revisions
42 WHERE scenario_id = lv_input_scenario_id
43 AND demand_plan_id = lv_input_demand_plan_id)
44 FOR UPDATE;
45
46 BEGIN
47
48 OPEN latest_forecast_version;
49 FETCH latest_forecast_version into lv_forecast_name;
50
51 UPDATE msd_dp_parameters
52 SET start_date = decode(p_period_type,G_DAY,(start_date + p_number_of_periods),ADD_MONTHS(start_date,p_number_of_periods)),
53 end_date = decode(p_period_type,G_DAY,(end_date + p_number_of_periods),ADD_MONTHS(end_date,p_number_of_periods)) ,
54 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)
55 WHERE parameter_id = lv_parameter_id;
56
57
58 IF ( lv_parameter_type= G_TYPE_INPUT_SCENARIO ) THEN
59 msc_st_util.log_message ('Rolled the parameter dates and attached the latest forecast for the parameter with parameter_id - '||lv_parameter_id||' and parameter_name - '||lv_parameter_name);
60 ELSE
61 msc_st_util.log_message ('Rolled the parameter dates for the parameter with parameter_id - '||lv_parameter_id||' and parameter_name - '||lv_parameter_name);
62 END IF;
63
64
65
66
67
68 UPDATE msd_dp_scenarios
69 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)
70 WHERE nvl(parameter_name, NULL_CHAR ) = nvl(lv_parameter_name, NULL_CHAR )
71 AND nvl(forecast_based_on,NULL_CHAR) = nvl(lv_parameter_type,NULL_CHAR)
72 AND nvl(forecast_date_used,NULL_CHAR) = nvl(lv_forecast_date_used,NULL_CHAR)
73 AND demand_plan_id = p_demand_plan_id;
74
75 IF ( SQL%NOTFOUND AND lv_parameter_type= G_TYPE_INPUT_SCENARIO ) THEN
76 msc_st_util.log_message ('No Change for the parameter_name for latest forecast version in msd_dp_scenarios table');
77 ELSIF ( lv_parameter_type= G_TYPE_INPUT_SCENARIO ) THEN
78 msc_st_util.log_message ('Changed the parameter_name for latest forecast version in msd_dp_scenarios table');
79 END IF;
80
81
82 CLOSE latest_forecast_version;
83
84 END update_parameters;
85
86 /*========================================================================================+
87 | DESCRIPTION : This procedure is called to roll forward the scenario horizon periods |
88 | (horizon start and end dates) and the scenario history periods(horizon |
89 | start and end dates). |
90 +========================================================================================*/
91
92 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
93
94 BEGIN
95
96
97 UPDATE msd_dp_scenarios
98 SET horizon_start_date = decode(nvl(lv_exclude_flag,'N'),'N',decode(p_period_type,G_DAY,(horizon_start_date + p_number_of_periods),ADD_MONTHS(horizon_start_date,p_number_of_periods)),horizon_start_date),
99 horizon_end_date = decode(nvl(lv_exclude_flag,'N'),'N',decode(p_period_type,G_DAY,(horizon_end_date + p_number_of_periods),ADD_MONTHS(horizon_end_date,p_number_of_periods)),horizon_end_date),
100 history_start_date = decode(nvl(lv_exclude_flag,'N'),'N',decode(p_period_type,G_DAY,(history_start_date + p_number_of_periods),ADD_MONTHS(history_start_date,p_number_of_periods)),history_start_date),
101 history_end_date = decode(nvl(lv_exclude_flag,'N'),'N',decode(p_period_type,G_DAY,(history_end_date + p_number_of_periods),ADD_MONTHS(history_end_date,p_number_of_periods)),history_end_date)
102 WHERE scenario_id=lv_scenario_id;
103
104 IF (( nvl(lv_exclude_flag,'N') = 'N') AND ( p_period_type = G_GREGORIAN_MONTH )) THEN
105 UPDATE msd_dp_scenarios
106 SET horizon_start_date = decode(trunc(horizon_start_date),trunc(history_end_date),horizon_start_date+1,horizon_start_date)
107 WHERE scenario_id=lv_scenario_id;
108 END IF;
109
110
111 IF ( nvl(lv_exclude_flag,'N')= 'N' ) THEN
112 msc_st_util.log_message ('Rolled the scenario history and horizon dates for the scenario with scenario_id - '||lv_scenario_id||' and scenario_name - '||lv_scenario_name);
113 ELSE
114 msc_st_util.log_message ('Not Rolled the scenario history and horizon dates for the scenario with scenario_id - '||lv_scenario_id||' and scenario_name - '||lv_scenario_name);
115 END IF;
116
117
118
119 END update_scenarios;
120
121
122 /*=============================================================================================+
123 | DESCRIPTION : This is the main program that calls the procedures to do the following: |
124 | 1. Roll forwards the parameter start and end dates. |
125 | 2. Roll forwards the scenario horizon dates. |
126 | 3. Roll forwards the scenario history dates. |
127 | 4. Attaches the latest forecast Versions. |
128 | The procedure starts with invalidating the plan status |
129 +=============================================================================================*/
130
131 /*=============================================================================================+
132 | The input parameters of the main procedure are: |
133 | 1. p_demand_plan_id - Demand Plan ID for the plan to roll forward. |
134 | 2. p_period_type - Period type to roll with(Day/Gregorian Month) |
135 | 3. p_number_of_periods - Number of periods to roll with. |
136 +=============================================================================================*/
137
138
139 PROCEDURE launching_roll ( ERRBUF OUT NOCOPY VARCHAR2,
140 RETCODE OUT NOCOPY NUMBER,
141 p_demand_plan_id IN NUMBER,
142 p_period_type IN NUMBER,
143 p_number_of_periods IN NUMBER ) AS
144
145
146 lv_scenario_id MSD_DP_SCENARIOS.SCENARIO_ID%TYPE;
147 lv_scenario_name MSD_DP_SCENARIOS.SCENARIO_NAME%TYPE;
148
149 lv_parameter_type MSD_DP_PARAMETERS.PARAMETER_TYPE%TYPE;
150 lv_exclude_flag MSD_DP_PARAMETERS.EXCLUDE_FROM_ROLLING_CYCLE%TYPE;
151 lv_parameter_id MSD_DP_PARAMETERS.PARAMETER_ID%TYPE;
152 lv_parameter_name MSD_DP_PARAMETERS.PARAMETER_NAME%TYPE;
153 lv_input_demand_plan_id MSD_DP_PARAMETERS.INPUT_DEMAND_PLAN_ID%TYPE;
154 lv_input_scenario_id MSD_DP_PARAMETERS.INPUT_SCENARIO_ID%TYPE;
155 lv_forecast_date_used MSD_DP_PARAMETERS.FORECAST_DATE_USED%TYPE;
156
157 -- This cursor is used to select all the dynamic parameters.
158 CURSOR parameters IS
159 SELECT parameter_id,parameter_type,parameter_name,forecast_date_used,input_demand_plan_id,input_scenario_id
160 FROM msd_dp_parameters
161 WHERE demand_plan_id= p_demand_plan_id
162 AND nvl(exclude_from_rolling_cycle,'N') = 'N'
163 FOR UPDATE ;
164
165 -- This cursor is used to select all the scenarios that are attached to the demand plan.
166 CURSOR scenarios IS
167 SELECT s.scenario_id,s.scenario_name,p.exclude_from_rolling_cycle
168 FROM msd_dp_scenarios s, msd_dp_parameters p
169 WHERE s.demand_plan_id = p_demand_plan_id
170 AND p.demand_plan_id = p_demand_plan_id
171 AND nvl(s.parameter_name,NULL_CHAR) = nvl(p.parameter_name,NULL_CHAR)
172 AND nvl(s.forecast_based_on,NULL_CHAR) = p.parameter_type
173 AND nvl(s.forecast_date_used,NULL_CHAR) = nvl(p.forecast_date_used,NULL_CHAR)
174 UNION ALL
175 SELECT s.scenario_id,s.scenario_name,'N'
176 FROM msd_dp_scenarios s
177 WHERE s.demand_plan_id = p_demand_plan_id
178 AND s.parameter_name IS NULL
179 AND s.forecast_based_on IS NULL
180 AND s.forecast_date_used IS NULL;
181
182
183 BEGIN
184
185
186 -- invalidating the demand plan status
187 UPDATE msd_demand_plans
188 SET valid_flag= G_INVALID_PLAN
189 WHERE demand_plan_id=p_demand_plan_id;
190 COMMIT;
191
192 -- rolling forward parameter start and end dates and attaching latest forecat version
193 OPEN parameters;
194 LOOP
195 FETCH parameters INTO lv_parameter_id,lv_parameter_type,lv_parameter_name,lv_forecast_date_used,lv_input_demand_plan_id,lv_input_scenario_id ;
196 EXIT WHEN parameters%NOTFOUND;
197 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);
198 END LOOP;
199 msc_st_util.log_message ('************************Total number of parameters for which definition is modified ='||parameters%ROWCOUNT||'***********************');
200 CLOSE parameters;
201
202 -- rolling forward scenario history and horizon periods
203 OPEN scenarios;
204 LOOP
205 FETCH scenarios INTO lv_scenario_id,lv_scenario_name,lv_exclude_flag;
206 EXIT WHEN scenarios%NOTFOUND;
207 update_scenarios( p_demand_plan_id, lv_scenario_id,lv_scenario_name,lv_exclude_flag, p_period_type,p_number_of_periods );
208 END LOOP;
209 msc_st_util.log_message ('************************Total number of scenarios for which definition is modified ='||scenarios%ROWCOUNT||'***********************');
210 CLOSE scenarios;
211
212 -- commiting all the changes
213 COMMIT;
214 msc_st_util.log_message ('Commiting all changes');
215
216
217 EXCEPTION
218 WHEN OTHERS THEN
219 ROLLBACK;
220 ERRBUF := SQLERRM;
221 RETCODE := SQLCODE;
222 v_errtxt := substr(SQLERRM,1,240) ;
223 msc_st_util.log_message(v_errtxt);
224
225 END launching_roll;
226
227 END msd_roll_demand_plan;
[Home] [Help]