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