[Home] [Help]
PACKAGE BODY: APPS.CSP_FORECAST_PVT
Source
1 Package Body CSP_FORECAST_PVT AS
2 /* $Header: cspvpfob.pls 120.1 2006/02/03 00:54:34 hhaugeru noship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30):='CSP_FORECAST_PVT';
6
7
8 FUNCTION period_end_date(
9 p_organization_id IN NUMBER,
10 p_period_type IN NUMBER,
11 p_period_start_date IN DATE)
12 RETURN DATE IS
13
14 l_end_date DATE;
15
16 cursor c_end_date is
17 select schedule_close_date end_date
18 from org_acct_periods
19 where period_start_date = trunc(p_period_start_date)
20 and organization_id = p_organization_id
21 order by period_start_date asc;
22
23 begin
24 if p_period_type = 1 then --Weekly
25 l_end_date := trunc(p_period_start_date) + 6;
26 elsif p_period_type = 2 then --Periodic
27 open c_end_date;
28 fetch c_end_date into l_end_date;
29 close c_end_date;
30 end if;
31 return l_end_date;
32 end;
33
34 PROCEDURE period_start_dates(
35 p_start_date IN DATE,
36 p_period_type IN NUMBER,
37 p_number_of_periods IN NUMBER,
38 p_organization_id IN NUMBER,
39 x_period_start_dates OUT NOCOPY csp_forecast_pvt.t_date_table) IS
40
41 l_count NUMBER := 0;
42 l_start_date DATE;
43
44 cursor c_next_period(c_start_date DATE) is
45 /*select start_date
46 from org_acct_periods_v
47 where start_date >= c_start_date
48 and organization_id = p_organization_id
49 order by start_date asc;*/
50 select PERIOD_START_DATE
51 from ORG_ACCT_PERIODS
52 where PERIOD_START_DATE >= c_start_date
53 and organization_id = p_organization_id
54 order by PERIOD_START_DATE asc;
55
56 cursor c_previous_period(c_start_date DATE) is
57 /*select start_date
58 from org_acct_periods_v
59 where start_date < c_start_date
60 and organization_id = p_organization_id
61 order by start_date desc;*/
62 select PERIOD_START_DATE
63 from ORG_ACCT_PERIODS
64 where PERIOD_START_DATE < c_start_date
65 and organization_id = p_organization_id
66 order by PERIOD_START_DATE desc;
67
68 begin
69 -- History
70 if p_number_of_periods < 0 then
71 if p_period_type = 1 then -- Weekly
72 l_start_date := trunc(p_start_date-7,'IW');
73 for l_count in 0..abs(p_number_of_periods) loop
74 x_period_start_dates(l_count) := l_start_date - 7 * l_count;
75 end loop;
76 elsif p_period_type = 2 then -- Periodic
77 open c_previous_period(p_start_date);
78 fetch c_previous_period into l_start_date;
79 close c_previous_period;
80 for l_count in 0..abs(p_number_of_periods) loop
81 open c_previous_period(l_start_date);
82 fetch c_previous_period into l_start_date;
83 close c_previous_period;
84 x_period_start_dates(l_count) := l_start_date;
85 end loop;
86 end if;
87 -- Forecast
88 else
89 if p_period_type = 1 then
90 l_start_date := trunc(p_start_date,'IW');
91 for l_count in 1..abs(p_number_of_periods) loop
92 x_period_start_dates(l_count) := l_start_date + 7 * (l_count-1);
93 end loop;
94 elsif p_period_type = 2 then -- Periodic
95 open c_previous_period(p_start_date);
96 fetch c_previous_period into l_start_date;
97 close c_previous_period;
98 open c_next_period(l_start_date);
99 for l_count in 1..abs(p_number_of_periods) loop
100 fetch c_next_period into l_start_date;
101 x_period_start_dates(l_count) := l_start_date;
102 end loop;
103 close c_next_period;
104 end if;
105
106
107 end if;
108 end;
109
110 PROCEDURE simple_average(
111 p_usage_history IN csp_forecast_pvt.t_number_table,
112 p_history_periods IN NUMBER,
113 p_forecast_periods IN NUMBER,
114 x_forecast_quantities OUT NOCOPY csp_forecast_pvt.t_number_table) IS
115
116 l_count NUMBER;
117 l_total_quantity NUMBER := 0;
118 l_forecast NUMBER;
119
120 begin
121 for l_count in 0..p_history_periods-1 loop
122 l_total_quantity := l_total_quantity + p_usage_history(l_count);
123 end loop;
124 l_forecast := l_total_quantity / p_history_periods;
125 for l_count in 1..p_forecast_periods loop
126 x_forecast_quantities(l_count) := l_forecast;
127 end loop;
128 end simple_average;
129
130 PROCEDURE weighted_average(
131 p_usage_history IN csp_forecast_pvt.t_number_table,
132 p_history_periods IN NUMBER,
133 p_forecast_periods IN NUMBER,
134 p_weighted_avg IN csp_forecast_pvt.t_number_table,
135 x_forecast_quantities OUT NOCOPY csp_forecast_pvt.t_number_table) IS
136
137 l_count NUMBER;
138 l_count1 NUMBER := 0;
139 l_forecast NUMBER := 0;
140
141 begin
142 for l_count in 0..p_history_periods-1 loop
143 l_count1:=l_count+1;
144 l_forecast := l_forecast + p_usage_history(l_count) * p_weighted_avg(l_count1);
145 end loop;
146 for l_count in 1..p_forecast_periods loop
147 x_forecast_quantities(l_count) := l_forecast;
148 end loop;
149 end weighted_average;
150
151 PROCEDURE exponential_smoothing(
152 p_usage_history IN csp_forecast_pvt.t_number_table,
153 p_history_periods IN NUMBER,
154 p_forecast_periods IN NUMBER,
155 p_alpha IN NUMBER,
156 x_forecast_quantities OUT NOCOPY csp_forecast_pvt.t_number_table) IS
157
158 l_count NUMBER := 0;
159 l_forecast NUMBER := 0;
160 l_actual NUMBER := 0;
161
162 begin
163 l_forecast := p_usage_history(0);
164 for l_count in 1..p_history_periods-1 loop
165 l_forecast := p_usage_history(l_count) * p_alpha + l_forecast * (1 - p_alpha);
166 end loop;
167 for l_count in 1..p_forecast_periods loop
168 x_forecast_quantities(l_count) := l_forecast;
169 end loop;
170 end exponential_smoothing;
171
172 PROCEDURE trend_enhanced(
173 p_usage_history IN csp_forecast_pvt.t_number_table,
174 p_history_periods IN NUMBER,
175 p_forecast_periods IN NUMBER,
176 p_alpha IN NUMBER,
177 p_beta IN NUMBER,
178 x_forecast_quantities OUT NOCOPY csp_forecast_pvt.t_number_table) IS
179
180 l_count NUMBER := 0;
181 l_forecast NUMBER := 0;
182 l_actual NUMBER := 0;
183 l_base NUMBER := 0;
184 l_trend NUMBER := 0;
185 l_previous_base NUMBER := 0;
186
187 begin
188 l_base := p_usage_history(0);
189 for l_count in 1..p_history_periods-1 loop
190 l_previous_base := l_base;
191 l_base := p_usage_history(l_count) * p_alpha + l_base * (1 - p_alpha);
192 if l_count = 1 then
193 l_trend := p_usage_history(1) - p_usage_history(0);
194 else
195 l_trend := (l_base - l_previous_base) * p_beta + l_trend * (1 - p_beta);
196 end if;
197 end loop;
198 for l_count in 1..p_forecast_periods loop
199 x_forecast_quantities(l_count) := l_base + l_trend * l_count;
200 end loop;
201 end trend_enhanced;
202 PROCEDURE create_forecast(
203 p_api_version IN NUMBER,
204 p_parts_loop_id IN NUMBER,
205 p_organization_id IN NUMBER,
206 p_subinventory_code IN VARCHAR2 ,
207 p_inventory_item_id IN NUMBER,
208 p_start_date IN DATE,
209 x_start_date OUT NOCOPY DATE,
210 x_end_date OUT NOCOPY DATE,
211 x_period_type OUT NOCOPY NUMBER,
212 x_return_status OUT NOCOPY VARCHAR2,
213 x_msg_count OUT NOCOPY NUMBER,
214 x_msg_data OUT NOCOPY VARCHAR2) IS
215
216 l_api_name CONSTANT VARCHAR2(30) := 'create_forecast_pvt';
217 l_api_version CONSTANT NUMBER := 1.0;
218
219 l_start_date DATE;
220 l_start_date1 DATE;
221 l_end_date DATE;
222 l_count NUMBER := 0;
223 l_count1 NUMBER := 0;
224 l_usage_id NUMBER;
225 l_used_quantity NUMBER;
226 l_so_quantity NUMBER;
227 l_index NUMBER := 0;
228
229 l_usage_history T_NUMBER_TABLE;
230 l_forecast_quantities T_NUMBER_TABLE;
231 l_weighted_avg T_NUMBER_TABLE;
232 l_forecast_dates T_DATE_TABLE;
233 l_history_dates T_DATE_TABLE;
234
235 cursor c_usage_history Is
236 select sum(quantity) usage_quantity
237 from csp_usage_histories
238 where parts_loop_id = nvl(p_parts_loop_id,parts_loop_id)
239 and organization_id = p_organization_id
240 and subinventory_code = nvl(p_subinventory_code,subinventory_code)
241 and inventory_item_id = p_inventory_item_id
242 and history_data_type = 0
243 Group by Period_Start_date
244 Order by period_start_date desc;
245
246 cursor c_forecast_info is
247 select cfrb.period_size,
248 cfrb.forecast_rule_id,
249 cfrb.period_type,
250 cfrb.forecast_method,
251 cfrb.forecast_periods,
252 cfrb.history_periods,
253 cfrb.alpha,
254 cfrb.beta,
255 cfrb.weighted_avg_period1,
256 cfrb.weighted_avg_period2,
257 cfrb.weighted_avg_period3,
258 cfrb.weighted_avg_period4,
259 cfrb.weighted_avg_period5,
260 cfrb.weighted_avg_period6,
261 cfrb.weighted_avg_period7,
262 cfrb.weighted_avg_period8,
263 cfrb.weighted_avg_period9,
264 cfrb.weighted_avg_period10,
265 cfrb.weighted_avg_period11,
266 cfrb.weighted_avg_period12
267 from csp_forecast_rules_b cfrb,
268 csp_parts_loops_b cplb
269 where cfrb.forecast_rule_id = cplb.forecast_rule_id
270 and cplb.parts_loop_id = p_parts_loop_id;
271
272 l_rec c_forecast_info%rowtype;
273 l_forecast_qty Number := 0;
274
275 BEGIN
276
277 -- Standard Start of API savepoint
278 SAVEPOINT create_forecast_pvt;
279
280 -- Standard call to check for call compatibility.
281 IF NOT FND_API.Compatible_API_Call (l_api_version ,
282 p_api_version ,
283 l_api_name ,
284 G_PKG_NAME )
285 THEN
286 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
287 END IF;
288 -- Initialize API return status to success
289 x_return_status := FND_API.G_RET_STS_SUCCESS;
290
291 open c_forecast_info;
292 fetch c_forecast_info into l_rec;
293 close c_forecast_info;
294 For l_Index in 0..l_rec.history_periods - 1 Loop
295 l_Usage_history(l_index) := 0;
296 End Loop;
297 l_index := 0;
298 For l_usg in c_usage_history Loop
299 l_usage_history(l_index) := nvl(l_usg.usage_quantity,0);
300 l_index := l_index + 1;
301 End loop;
302
303 if l_rec.forecast_method = 1 then
304 simple_average(
305 p_usage_history => l_usage_history,
306 p_history_periods => l_rec.history_periods,
307 p_forecast_periods => l_rec.forecast_periods,
308 x_forecast_quantities => l_forecast_quantities);
309 elsif l_rec.forecast_method = 2 then
310 l_weighted_avg(1) := l_rec.weighted_avg_period1;
311 l_weighted_avg(2) := l_rec.weighted_avg_period2;
312 l_weighted_avg(3) := l_rec.weighted_avg_period3;
313 l_weighted_avg(4) := l_rec.weighted_avg_period4;
314 l_weighted_avg(5) := l_rec.weighted_avg_period5;
315 l_weighted_avg(6) := l_rec.weighted_avg_period6;
316 l_weighted_avg(7) := l_rec.weighted_avg_period7;
317 l_weighted_avg(8) := l_rec.weighted_avg_period8;
318 l_weighted_avg(9) := l_rec.weighted_avg_period9;
319 l_weighted_avg(10) := l_rec.weighted_avg_period10;
320 l_weighted_avg(11) := l_rec.weighted_avg_period11;
321 l_weighted_avg(12) := l_rec.weighted_avg_period12;
322 weighted_average(
323 p_usage_history => l_usage_history,
324 p_history_periods => l_rec.history_periods,
325 p_forecast_periods => l_rec.forecast_periods,
326 p_weighted_avg => l_weighted_avg,
327 x_forecast_quantities => l_forecast_quantities);
328 elsif l_rec.forecast_method = 3 then
329 exponential_smoothing(
330 p_usage_history => l_usage_history,
331 p_history_periods => l_rec.history_periods,
332 p_forecast_periods => l_rec.forecast_periods,
333 p_alpha => l_rec.alpha,
334 x_forecast_quantities => l_forecast_quantities);
335 elsif l_rec.forecast_method = 4 then
336 trend_enhanced(
337 p_usage_history => l_usage_history,
338 p_history_periods => l_rec.history_periods,
339 p_forecast_periods => l_rec.forecast_periods,
340 p_alpha => l_rec.alpha,
341 p_beta => l_rec.beta,
342 x_forecast_quantities => l_forecast_quantities);
343 end if;
344 for l_count in 1..l_rec.forecast_periods loop
345 l_usage_id := null;
346 If l_forecast_quantities(l_count) > 0 Then
347 l_forecast_qty := l_forecast_quantities(l_count);
348 Else
349 l_forecast_qty := 0;
350 End If;
351 csp_usage_histories_pkg.insert_row(
352 px_usage_id => l_usage_id,
353 p_created_by => fnd_global.user_id,
354 p_creation_date => sysdate,
358 p_inventory_item_id => p_inventory_item_id,
355 p_last_updated_by => fnd_global.user_id,
356 p_last_update_date => sysdate,
357 p_last_update_login => null,
359 p_organization_id => p_organization_id,
360 p_period_type => l_rec.period_type,
361 p_period_start_date => sysdate + ((l_count - 1) * l_rec.period_size),
362 p_quantity => l_forecast_qty,
363 p_request_id => null,
364 p_program_application_id => null,
365 p_program_id => null,
366 p_program_update_date => null,
367 p_subinventory_code => nvl(p_subinventory_code,'-'),
368 p_transaction_type_id => -1,
369 p_hierarchy_node_id => null,
370 p_parts_loop_id => p_parts_loop_id,
371 p_history_data_type => 0,
372 p_attribute_category => null,
373 p_attribute1 => null,
374 p_attribute2 => null,
375 p_attribute3 => null,
376 p_attribute4 => null,
377 p_attribute5 => null,
378 p_attribute6 => null,
379 p_attribute7 => null,
380 p_attribute8 => null,
381 p_attribute9 => null,
382 p_attribute10 => null,
383 p_attribute11 => null,
384 p_attribute12 => null,
385 p_attribute13 => null,
386 p_attribute14 => null,
387 p_attribute15 => null);
388 end loop;
389
390 x_period_type := l_rec.period_type;
391 x_start_date := trunc(sysdate) - l_rec.history_periods * l_rec.period_size;
392 x_end_date := trunc(sysdate) + (l_rec.forecast_periods - 1) * l_rec.period_size;
393
394 EXCEPTION
395 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
396 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
397 P_API_NAME => L_API_NAME
398 ,P_PKG_NAME => G_PKG_NAME
399 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
400 ,P_PACKAGE_TYPE => null
401 ,X_MSG_COUNT => X_MSG_COUNT
402 ,X_MSG_DATA => X_MSG_DATA
403 ,X_RETURN_STATUS => X_RETURN_STATUS);
404 WHEN OTHERS THEN
405 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
406 P_API_NAME => L_API_NAME
407 ,P_PKG_NAME => G_PKG_NAME
408 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
409 ,P_PACKAGE_TYPE => null
410 ,X_MSG_COUNT => X_MSG_COUNT
411 ,X_MSG_DATA => X_MSG_DATA
412 ,X_RETURN_STATUS => X_RETURN_STATUS);
413 end;
414
415
416 PROCEDURE rollback_forecast IS
417 begin
418 rollback to create_forecast_pvt;
419 end;
420
421
422 end;
423
424