[Home] [Help]
PACKAGE BODY: APPS.AHL_FLEET_UTIL_HISTORY_PVT
Source
1 PACKAGE BODY AHL_FLEET_UTIL_HISTORY_PVT AS
2 /* $Header: AHLVFUHB.pls 120.0.12020000.2 2012/12/10 13:37:52 shnatu noship $ */
3
4 ---------------------------- FND Logging Constants -----------------------------
5 G_DEBUG_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
6 G_DEBUG_PROC CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
7 G_DEBUG_STMT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
8 G_REQ_TYPE_FORECAST CONSTANT VARCHAR2(30) := 'FORECAST';
9 G_DEBUG VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
10 G_IS_PM_INSTALLED CONSTANT VARCHAR2(1) := AHL_UTIL_PKG.IS_PM_INSTALLED;
11
12 G_CONCURRENT_FLAG VARCHAR2(1);
13
14 ---------------------------- Commit counter variable ---------------------------
15 g_commit_counter NUMBER := 0;
16
17 ---------------------------- Exception looging variables -----------------------
18 g_err_msg VARCHAR2(4000);
19 g_err_code NUMBER;
20 -- added to debug concurrent worker pgms failure.
21 G_DEBUG_LINE_NUM NUMBER;
22 G_APPLN_USAGE VARCHAR2(30) :=ltrim(rtrim(FND_PROFILE.VALUE('AHL_APPLN_USAGE')));
23 G_PKG_NAME VARCHAR2(30) :='AHL_FLEET_UTIL_HISTORY_PVT';
24
25
26 -------------------------------------------------------------------------------
27 -- Define local record structures used by populate_fleet_utilization Procedure.
28 -------------------------------------------------------------------------------
29
30 -- Table type for storing material for each UE
31 TYPE util_forecasts_tbl_type
32 IS
33 TABLE OF AHL_FLEET_UTILIZATION%rowtype INDEX BY BINARY_INTEGER;
34
35 -- Record type for storing forecasted utilization data
36 --util_forecasts_rec_type AHL_FLEET_UTILIZATION%rowtype;
37 util_forecasts_table util_forecasts_tbl_type;
38
39
40 -- To hold the forecast details.
41 TYPE forecast_details_rec_type
42 IS
43 RECORD
44 (
45 START_DATE DATE,
46 END_DATE DATE,
47 UOM_CODE VARCHAR2(3),
48 USAGE_PER_DAY NUMBER,
49 sorties_val NUMBER,
50 fleet_size NUMBER);
51
52 -- The table type type to store utlization records
53 TYPE forecast_details_tbl_type
54 IS
55 TABLE OF forecast_details_rec_type INDEX BY BINARY_INTEGER;
56
57 -- Table type variable which holds the utilization data
58 l_forecast_details_tbl forecast_details_tbl_type;
59
60 -- Procedure to identify the forecasted usage
61 PROCEDURE Get_Forecast_Records
62 (
63 p_fleet_header_id IN NUMBER,
64 p_simulation_plan_id IN NUMBER,
65 p_usage_counter_uom_code IN VARCHAR,
66 p_sorties_counter_uom_code IN VARCHAR,
67 p_forecast_details_tbl OUT NOCOPY forecast_details_tbl_type );
68
69 FUNCTION Get_Forecasting_Window_Date
70 RETURN DATE;
71
72 /*
73 --
74 Procedure Name: refresh_utilization_table
75 Type: Public
76 Function: This procedure is called by populate_fleet_utilization procedure to refresh the AHL_FLEET_UTILIZATION table completely.
77 --
78 */
79 -- Start of Procedure populate_utilization_history --
80 PROCEDURE refresh_utilization_table
81 AS
82
83 l_debug_module VARCHAR2(1000) := 'ahl.plsql.AHL_FLEET_UTIL_HISTORY_PVT.refresh_utilization_table';
84
85 BEGIN
86
87 DELETE FROM AHL_FLEET_UTILIZATION;
88 --COMMIT;
89 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
90 THEN
91 FND_LOG.STRING(G_DEBUG_STMT, l_debug_module, 'AHL_FLEET_UTILIZATION table refreshed and committed.');
92 END IF;
93
94 END refresh_utilization_table;
95
96 /*
97 --
98 Procedure Name: populate_fleet_utilization
99 Type: Public
100 Function: This procedure is called by concurrent program to populate historical utilization data for fleets
101 Parameters:
102 1. errbuf - Error message if any
103 2. retcode - retcode equal to 0 implies succesfull processing
104 --
105 */
106 -- Start of Procedure populate_fleet_utilization --
107 PROCEDURE populate_fleet_utilization
108 (
109 errbuf OUT NOCOPY VARCHAR2,
110 retcode OUT NOCOPY NUMBER,
111 p_sim_plan_id NUMBER
112 )
113 AS
114
115 -- Cursor to get the fleet header Ids
116 CURSOR GetFleetHeaderIds(p_sim_plan_id NUMBER)
117 IS
118 SELECT fleet_header_id,
119 name,
120 operating_org_id
121 FROM ahl_fleet_headers_b
122 WHERE fleet_header_id IN
123 (SELECT Fleet_header_id FROM ahl_fleet_unit_assocs
124 where simulation_plan_id = p_sim_plan_id
125 )
126 AND status_code = 'COMPLETE';
127
128 -- Cursor to get the primalry plan id
129 CURSOR GetPrmPlanId
130 IS
131 SELECT simulation_plan_id
132 FROM ahl_simulation_plans_b
133 WHERE primary_plan_flag = 'Y'
134 and simulation_type = 'UMP';
135
136 -- Cursor to get the Usage Counters from counter setup UI
137 CURSOR GetUsageCounters
138 IS
139 SELECT parameter_name,
140 parameter_value
141 FROM ahl_parameters
142 WHERE parameter_source_type = 'FLT'
143 AND parameter_source_sub_type = 'FUHUC';
144
145 -- Cursor to get the Sorties Counters from counter setup UI
146 CURSOR GetSortiesCounter
147 IS
148 SELECT parameter_name,
149 parameter_value
150 FROM ahl_parameters
151 WHERE parameter_source_type = 'FLT'
152 AND parameter_source_sub_type = 'FUHSC';
153
154 -- Cursor to calculate fleet size - average units for the period provided
155 CURSOR GetFleetSize(p_fleet_header_id NUMBER, p_sim_plan_id NUMBER, p_start_date DATE, p_end_date DATE)
156 IS
157 SELECT decode((SUM(unit_per_day)/(p_end_date - p_start_date + 1)), null, 0, (SUM(unit_per_day)/(p_end_date - p_start_date + 1))) unit_per_month
158 FROM
159 (SELECT ddate,
160 COUNT(*) unit_per_day
161 FROM
162 (SELECT rownum,
163 (p_start_date + rownum - 1) ddate
164 FROM fnd_new_messages
165 WHERE rownum <= (p_end_date - p_start_date + 1)
166 ) dtab,
167 ahl_fleet_unit_assocs fleet
168 WHERE fleet.fleet_header_id = p_fleet_header_id
169 and fleet.simulation_plan_id = p_sim_plan_id
170 AND dtab.ddate BETWEEN association_start AND NVL(association_end, dtab.ddate)
171 GROUP BY ddate
172 );
173
174 -- Cursor to get associated units to the fleet for the sim plan id.
175 CURSOR GetAssocUnits(p_fleet_header_id NUMBER, p_sim_plan_id NUMBER, p_start_date DATE, p_end_date DATE)
176 IS
177 SELECT unit_config_header_id
178 FROM ahl_fleet_unit_assocs
179 WHERE fleet_header_id = p_fleet_header_id
180 AND simulation_plan_id = p_sim_plan_id
181 AND (association_start BETWEEN p_start_date AND p_end_date
182 OR association_end BETWEEN p_start_date AND p_end_date
183 OR (association_start < p_start_date
184 AND association_end > p_end_date));
185
186 -- Cursor to get the counter net reading for the units associated to the fleetfleet
187 CURSOR GetCounterReading(p_fleet_header_id NUMBER, p_unit_config_header_id NUMBER, p_counter_name VARCHAR2, p_uom_code VARCHAR2, p_counter_val_date DATE)
188 IS
189 SELECT *
190 FROM
191 (SELECT ccr.net_reading
192 FROM CSI_COUNTER_ASSOCIATIONS csa,
193 ahl_unit_config_headers auch,
194 csi_counter_readings ccr,
195 ahl_fleet_unit_assocs afua,
196 csi_item_instances cii,
197 csi_counters_vl ccv
198 WHERE afua.unit_config_header_id = auch.unit_config_header_id
199 AND auch.csi_item_instance_id = cii.instance_id
200 AND ccv.counter_id = ccr.counter_id
201 AND csa.source_object_code = 'CP'
202 AND csa.source_object_id = auch.csi_item_instance_id
203 AND ccr.counter_id = csa.counter_id
204 AND afua.fleet_header_id = p_fleet_header_id
205 AND afua.unit_config_header_id = p_unit_config_header_id
206 AND ccv.counter_template_name = p_counter_name
207 AND ccv.uom_code = p_uom_code
208 AND ccr.value_timestamp <= p_counter_val_date
209 ORDER BY ccr.value_timestamp DESC
210 )
211 WHERE rownum < 2;
212
213 -- Cursor to get the calculation window for historical utilization
214 CURSOR GetCalculationWindowActual(p_fleet_header_id NUMBER, p_sim_plan_id NUMBER)
215 IS
216 SELECT MIN(association_start) window_start,
217 MAX(NVL(association_end,sysdate)) window_end
218 FROM ahl_fleet_unit_assocs
219 WHERE fleet_header_id = p_fleet_header_id;
220 --and simulation_plan_id = p_sim_plan_id;
221
222 -- Cursor to get the calculation window for historical utilization
223 /*CURSOR GetCalculationWindowForecast(p_fleet_header_id NUMBER)
224 IS
225 SELECT (sysdate+1) period_start_date,
226 MAX(NVL(period_end_date, AHL_UMP_ProcessUnit_PVT.Get_Rolling_Window_Date)) period_end_date
227 FROM ahl_fleet_utlzn_forecast
228 WHERE fleet_header_id = p_fleet_header_id;*/
229 /*CURSOR GetCalculationWindowForecast(p_fleet_header_id NUMBER)
230 IS
231 SELECT (sysdate+1) period_start_date,
232 MAX(NVL(association_end, Get_Rolling_Window_Date)) period_end_date
233 FROM ahl_fleet_unit_assocs
234 WHERE fleet_header_id = p_fleet_header_id;*/
235 -- Variables to be used
236
237 l_api_name CONSTANT VARCHAR2(30) := 'populate_utilization_history';
238 l_debug_module VARCHAR2(1000) := 'ahl.plsql.AHL_FLEET_UTIL_HISTORY_PVT.populate_utilization_history';
239 l_api_version CONSTANT NUMBER := 1.0;
240 l_fleet_header_id NUMBER;
241 l_fleet_op_org_id NUMBER;
242 l_fleet_name VARCHAR2(80);
243 l_period_start DATE;
244 l_period_end DATE;
245 l_fleet_size NUMBER;
246 l_window_start DATE;
247 l_window_end DATE;
248 l_assoc_unit NUMBER;
249 l_sorties_counter_name VARCHAR2(80);
250 l_sorties_counter_uom_code VARCHAR2(3);
251 l_period_start_reading NUMBER := 0;
252 l_period_end_reading NUMBER := 0;
253 l_all_unit_sorties_counter_val NUMBER := 0;
254 l_all_unit_usage_counter_val NUMBER := 0;
255 l_all_unit_counter_usage NUMBER := 0;
256 l_counter_val_total NUMBER := 0;
257 l_usage_counter_name VARCHAR2(80);
258 l_usage_counter_uom_code VARCHAR2(3);
259 l_daily_sorties_val NUMBER := 0;
260 l_daily_usage_val NUMBER := 0;
261 l_sim_plan_id NUMBER;
262 l_msg_count NUMBER := 0;
263 l_msg_data VARCHAR2(3000);
264 x_msg_count NUMBER := 0;
265 l_appln_code VARCHAR2(30);
266 x_return_status VARCHAR2(1);
267 l_record_num NUMBER := 1;
268 l_run_no NUMBER := 1;
269
270 BEGIN
271
272 SAVEPOINT HANDLE_FLEET_UTIL_ERRORS;
273
274 x_return_status := FND_API.G_RET_STS_SUCCESS;
275 IF G_APPLN_USAGE IS NULL THEN
276 FND_MESSAGE.SET_NAME('AHL','AHL_COM_APP_PRFL_UNDEF');
277 FND_MSG_PUB.ADD;
278 RETURN;
279 END IF;
280
281 G_DEBUG_LINE_NUM := 1;
282 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL) THEN
283 fnd_log.string (G_DEBUG_PROC, l_debug_module, 'Start populate_utilization_history');
284 fnd_log.string (G_DEBUG_PROC, l_debug_module, 'Input Parameter: p_sim_plan_id:' || p_sim_plan_id);
285 END IF;
286
287 -- Initialize return status.
288 retcode := 0;
289
290 /*
291 IF FND_API.TO_BOOLEAN(p_commit) THEN
292 COMMIT;
293 END IF;*/
294
295 -- Complete refresh of AHL_FLEET_UTILIZATION table
296 refresh_utilization_table();
297
298 l_sim_plan_id := p_sim_plan_id;
299
300 -- Calculate primaly plan id if passed sim plan id is null
301 IF(p_sim_plan_id IS NULL) THEN
302
303 G_DEBUG_LINE_NUM := 10;
304 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
305 THEN
306 fnd_log.string (G_DEBUG_STMT, l_debug_module, ' passed sim plan Id is null, hence get the primary plan id');
307 END IF;
308
309 BEGIN
310 OPEN GetPrmPlanId;
311 FETCH GetPrmPlanId INTO l_sim_plan_id;
312
313 CLOSE GetPrmPlanId;
314 EXCEPTION WHEN NO_DATA_FOUND
315 then
316 retcode := 1;
317 errbuf := 'Primary Plan not present';
318 END;
319 end if;
320
321 G_DEBUG_LINE_NUM := 20;
322 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
323 THEN
324 fnd_log.string (G_DEBUG_STMT, l_debug_module, ' calculated l_sim_plan_id = ' || l_sim_plan_id);
325 END IF;
326 -- Calculate primaly plan id end
327
328
329 -- Logic to get the Sorties counter name from Counter Setup UI
330 OPEN GetSortiesCounter();
331 FETCH GetSortiesCounter
332 INTO l_sorties_counter_name,
333 l_sorties_counter_uom_code;
334
335 if(GetSortiesCounter%NOTFOUND)
336 THEN
337 G_DEBUG_LINE_NUM := 30;
338 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
339 THEN
340 fnd_log.string (G_DEBUG_STMT, l_debug_module, ' Sorties counter data from setup UI not found.');
341 END IF;
342 end if;
343 CLOSE GetSortiesCounter;
344 -- getting Sorties counter name ends
345 G_DEBUG_LINE_NUM := 40;
346 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
347 THEN
348 fnd_log.string (G_DEBUG_STMT, l_debug_module, ' l_sorties_counter_name = ' || l_sorties_counter_name
349 || ' and l_sorties_counter_uom_code = ' || l_sorties_counter_uom_code);
350 END IF;
351
352
353 -- Loop through fleets one by one and start processing
354 FOR fleet_header_id_rec IN GetFleetHeaderIds(l_sim_plan_id)
355 LOOP
356 l_fleet_header_id := fleet_header_id_rec.fleet_header_id;
357 l_fleet_name := fleet_header_id_rec.name;
358 l_fleet_op_org_id := fleet_header_id_rec.operating_org_id;
359 G_DEBUG_LINE_NUM := 40;
360 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
361 THEN
362 fnd_log.string (G_DEBUG_STMT, l_debug_module, ' l_fleet_header_id = ' || l_fleet_header_id
363 || ' and l_fleet_name = ' || l_fleet_name || ' and l_fleet_op_org_id = ' || l_fleet_op_org_id);
364 END IF;
365
366
367 --Get the window period for which historical (actual) utilization data is to be updated for the fleet
368 OPEN GetCalculationWindowActual(l_fleet_header_id, l_sim_plan_id);
369 FETCH GetCalculationWindowActual INTO l_window_start, l_window_end;
370
371 if(GetCalculationWindowActual%NOTFOUND)
372 THEN
373 G_DEBUG_LINE_NUM := 50;
374 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
375 THEN
376 fnd_log.string (G_DEBUG_STMT, l_debug_module, ' Min and max unit association could not be found for the fleet - ' || l_fleet_name);
377 END IF;
378 end if;
379 CLOSE GetCalculationWindowActual;
380
381
382 -- If Window start doesn't fall after sysdate and window end is greater than sysdate reset window end to sysdate
383 IF (l_window_start IS NOT NULL AND l_window_start < sysdate) THEN
384 -- Utilization to be calculated till sysdate only
385 IF(l_window_end > sysdate) THEN
386 l_window_end := sysdate;
387 END IF;
388
389 G_DEBUG_LINE_NUM := 60;
390 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
391 THEN
392 fnd_log.string (G_DEBUG_STMT, l_debug_module, ' l_window_start = ' || l_window_start
393 || ' and l_window_end = ' || l_window_end);
394 END IF;
395
396 l_period_start := add_months(last_day(l_window_start) + 1, -1);
397 l_period_end := last_day(l_window_start);
398 IF(l_period_end > sysdate) THEN
399 l_period_end := sysdate;
400 END IF;
401
402
403 -- Loop through the least last update date and sysdate for a period of one month each till we dont come to sysdate
404 WHILE l_period_start <= l_window_end
405 LOOP
406 G_DEBUG_LINE_NUM := 70;
407 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
408 THEN
409 fnd_log.string (G_DEBUG_STMT, l_debug_module, ' current l_period_start = ' || l_period_start
410 || ' and current l_period_end = ' || l_period_end);
411 END IF;
412
413
414 -- Logic to calculate fleet size
415 OPEN GetFleetSize(l_fleet_header_id, l_sim_plan_id, l_period_start, l_period_end);
416 FETCH GetFleetSize INTO l_fleet_size;
417
418 CLOSE GetFleetSize;
419 -- calculate fleet size ends
420 l_fleet_size := ROUND(l_fleet_size,2);
421 G_DEBUG_LINE_NUM := 80;
422 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
423 THEN
424 fnd_log.string (G_DEBUG_STMT, l_debug_module, ' fleet size = ' || l_fleet_size || ' for fleet - ' || l_fleet_name);
425 fnd_log.string (G_DEBUG_STMT, l_debug_module, ' Historical utilization calculation begins for fleet - ' || l_fleet_name);
426 END IF;
427
428
429
430 -- Monthly Sorties caluclation Starts
431 l_all_unit_sorties_counter_val := 0;
432
433 -- Loop through each unit accociated to the fleet for Sorties counter
434 FOR assoc_unit_rec IN GetAssocUnits(l_fleet_header_id, l_sim_plan_id, l_period_start, l_period_end)
435 LOOP
436 l_assoc_unit := assoc_unit_rec.unit_config_header_id;
437
438 -- For each usage counter, get the readings and do calculation
439 OPEN GetCounterReading(l_fleet_header_id, l_assoc_unit, l_sorties_counter_name, l_sorties_counter_uom_code, l_period_start);
440 FETCH GetCounterReading INTO l_period_start_reading;
441
442 CLOSE GetCounterReading;
443
444 OPEN GetCounterReading(l_fleet_header_id, l_assoc_unit, l_sorties_counter_name, l_sorties_counter_uom_code, l_period_end);
445 FETCH GetCounterReading INTO l_period_end_reading;
446
447 CLOSE GetCounterReading;
448 -- Looping throug counter reading details ends
449
450 l_all_unit_sorties_counter_val := l_all_unit_sorties_counter_val + (l_period_end_reading - l_period_start_reading);
451 END LOOP;
452
453 l_daily_sorties_val := l_all_unit_sorties_counter_val; --/(l_period_end - l_period_start);
454 G_DEBUG_LINE_NUM := 90;
455 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
456 THEN
457 fnd_log.string (G_DEBUG_STMT, l_debug_module, ' l_daily_sorties_val for the period = ' || l_daily_sorties_val);
458 END IF;
459
460
461 -- Monthly Usage caluclation Starts
462 -- Loop through each usage counter UOM from setup
463 FOR usage_counter_rec IN GetUsageCounters()
464 LOOP
465 l_usage_counter_name := usage_counter_rec.parameter_name;
466 l_usage_counter_uom_code := usage_counter_rec.parameter_value;
467 G_DEBUG_LINE_NUM := 100;
468 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
469 THEN
470 fnd_log.string (G_DEBUG_STMT, l_debug_module, ' current l_usage_counter_name = ' || l_usage_counter_name
471 || ' and l_usage_counter_uom_code = ' || l_usage_counter_uom_code);
472 END IF;
473
474 l_all_unit_counter_usage := 0;
475
476 -- Loop through each unit accociated to the fleet
477 FOR assoc_unit_rec IN GetAssocUnits(l_fleet_header_id, l_sim_plan_id, l_period_start, l_period_end)
478 LOOP
479 l_assoc_unit := assoc_unit_rec.unit_config_header_id;
480
481 l_period_start_reading := 0;
482 l_period_end_reading := 0;
483
484 -- For each usage counter, get the readings and do calculation
485 OPEN GetCounterReading(l_fleet_header_id, l_assoc_unit, l_usage_counter_name, l_usage_counter_uom_code, l_period_start);
486 FETCH GetCounterReading INTO l_period_start_reading;
487
488 CLOSE GetCounterReading;
489
490 OPEN GetCounterReading(l_fleet_header_id, l_assoc_unit, l_usage_counter_name, l_usage_counter_uom_code, l_period_end);
491 FETCH GetCounterReading INTO l_period_end_reading;
492
493 CLOSE GetCounterReading;
494
495 -- Looping throug counter reading details ends
496
497 l_all_unit_counter_usage := l_all_unit_counter_usage + (l_period_end_reading - l_period_start_reading);
498 END LOOP;
499 -- Looping through each unit associated ends
500
501 l_daily_usage_val := ROUND(l_all_unit_counter_usage/(l_period_end - l_period_start + 1), 2);
502 G_DEBUG_LINE_NUM := 110;
503 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
504 THEN
505 fnd_log.string (G_DEBUG_STMT, l_debug_module, ' calculated daily usage for the fleet = ' || l_daily_usage_val);
506 END IF;
507
508 -- Insert a record to table for usage UOM for current period
509 util_forecasts_table(l_record_num).FLEET_UTILIZATION_ID := AHL_FLEET_UTILIZATION_S.nextval;
510 util_forecasts_table(l_record_num).object_version_number := 1;
511 util_forecasts_table(l_record_num).FLEET_HEADER_ID := l_fleet_header_id;
512 util_forecasts_table(l_record_num).NAME := l_fleet_name;
513 util_forecasts_table(l_record_num).OPERATING_ORG_ID := l_fleet_op_org_id;
514 util_forecasts_table(l_record_num).PERIOD_START_DATE := l_period_start;
515 util_forecasts_table(l_record_num).PERIOD_END_DATE := l_period_end;
516 util_forecasts_table(l_record_num).UOM_CODE := l_usage_counter_uom_code;
517 util_forecasts_table(l_record_num).FLEET_SIZE := l_fleet_size;
518 util_forecasts_table(l_record_num).FORECASTED_DAILY_USAGE := NULL;
519 util_forecasts_table(l_record_num).FORECASTED_DAILY_SORTIES := NULL;
520 util_forecasts_table(l_record_num).ACTUAL_DAILY_USAGE := l_daily_usage_val;
521 util_forecasts_table(l_record_num).ACTUAL_DAILY_SORTIES := l_daily_sorties_val;
522 util_forecasts_table(l_record_num).SIMULATION_PLAN_ID := l_sim_plan_id;
523 util_forecasts_table(l_record_num).SECURITY_GROUP_ID := NULL;
524 util_forecasts_table(l_record_num).LAST_UPDATE_DATE := SYSDATE;
525 util_forecasts_table(l_record_num).LAST_UPDATED_BY := FND_GLOBAL.user_id;
526 util_forecasts_table(l_record_num).CREATION_DATE := SYSDATE;
527 util_forecasts_table(l_record_num).CREATED_BY := FND_GLOBAL.user_id;
528 util_forecasts_table(l_record_num).LAST_UPDATE_LOGIN := FND_GLOBAL.login_id;
529 util_forecasts_table(l_record_num).ATTRIBUTE_CATEGORY := NULL;
530 util_forecasts_table(l_record_num).ATTRIBUTE1 := NULL;
531 util_forecasts_table(l_record_num).ATTRIBUTE2 := NULL;
532 util_forecasts_table(l_record_num).ATTRIBUTE3 := NULL;
533 util_forecasts_table(l_record_num).ATTRIBUTE4 := NULL;
534 util_forecasts_table(l_record_num).ATTRIBUTE5 := NULL;
535 util_forecasts_table(l_record_num).ATTRIBUTE6 := NULL;
536 util_forecasts_table(l_record_num).ATTRIBUTE7 := NULL;
537 util_forecasts_table(l_record_num).ATTRIBUTE8 := NULL;
538 util_forecasts_table(l_record_num).ATTRIBUTE9 := NULL;
539 util_forecasts_table(l_record_num).ATTRIBUTE10 := NULL;
540 util_forecasts_table(l_record_num).ATTRIBUTE11 := NULL;
541 util_forecasts_table(l_record_num).ATTRIBUTE12 := NULL;
542 util_forecasts_table(l_record_num).ATTRIBUTE13 := NULL;
543 util_forecasts_table(l_record_num).ATTRIBUTE14 := NULL;
544 util_forecasts_table(l_record_num).ATTRIBUTE15 := NULL;
545
546 -- increase record number by 1
547 l_record_num := l_record_num + 1;
548 END LOOP;
549 -- Looping through each usage counter in counter setup ends
550 -- Monthly Usage caluclation ends
551
552 -- Move to next monthly bucket
553 l_period_start := add_months(l_period_start, 1);
554 l_period_end := add_months(l_period_end, 1);
555 IF(l_period_end > l_window_end) THEN
556 l_period_end := l_window_end;
557 END IF;
558
559 END LOOP;
560 -- Looping through whole period in buckets of a month ends
561 END IF;
562 G_DEBUG_LINE_NUM := 120;
563 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
564 THEN
565 fnd_log.string (G_DEBUG_STMT, l_debug_module, ' Historical utilization calculation ends for fleet - ' || l_fleet_name);
566 fnd_log.string (G_DEBUG_STMT, l_debug_module, ' Forecasted utilization calculation begins for fleet - ' || l_fleet_name);
567 END IF;
568
569 --forecasted utilization data calculation is to be updated for the fleet
570 -- Loop through each usage counter UOM from setup
571 FOR usage_counter_rec IN GetUsageCounters()
572 LOOP
573 l_usage_counter_name := usage_counter_rec.parameter_name;
574 l_usage_counter_uom_code := usage_counter_rec.parameter_value;
575
576 -- Get the forecatsed records for the fleet
577 G_DEBUG_LINE_NUM := 130;
578 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
579 THEN
580 fnd_log.string (G_DEBUG_STMT, l_debug_module, ' call Get_Forecast_Records procedure to get the forecatsed utilization data for fleet - ' || l_fleet_name);
581 END IF;
582 Get_Forecast_Records(l_fleet_header_id,l_sim_plan_id,l_usage_counter_uom_code,l_sorties_counter_uom_code,l_forecast_details_tbl);
583
584 IF(l_forecast_details_tbl.count > 0) THEN
585 l_run_no := 1;
586 WHILE l_run_no IS NOT NULL
587 LOOP
588 util_forecasts_table(l_record_num).FLEET_UTILIZATION_ID := AHL_FLEET_UTILIZATION_S.nextval;
589 util_forecasts_table(l_record_num).object_version_number := 1;
590 util_forecasts_table(l_record_num).FLEET_HEADER_ID := l_fleet_header_id;
591 util_forecasts_table(l_record_num).NAME := l_fleet_name;
592 util_forecasts_table(l_record_num).OPERATING_ORG_ID := l_fleet_op_org_id;
593 util_forecasts_table(l_record_num).PERIOD_START_DATE := l_forecast_details_tbl(l_run_no).START_DATE;
594 util_forecasts_table(l_record_num).PERIOD_END_DATE := l_forecast_details_tbl(l_run_no).END_DATE;
595 util_forecasts_table(l_record_num).UOM_CODE := l_forecast_details_tbl(l_run_no).uom_code;
596 util_forecasts_table(l_record_num).FLEET_SIZE := l_forecast_details_tbl(l_run_no).fleet_size;
597 util_forecasts_table(l_record_num).FORECASTED_DAILY_USAGE := l_forecast_details_tbl(l_run_no).USAGE_PER_DAY;
598 util_forecasts_table(l_record_num).FORECASTED_DAILY_SORTIES := l_forecast_details_tbl(l_run_no).sorties_val;
599 util_forecasts_table(l_record_num).ACTUAL_DAILY_USAGE := NULL;
600 util_forecasts_table(l_record_num).ACTUAL_DAILY_SORTIES := NULL;
601 util_forecasts_table(l_record_num).SIMULATION_PLAN_ID := l_sim_plan_id;
602 util_forecasts_table(l_record_num).SECURITY_GROUP_ID := NULL;
603 util_forecasts_table(l_record_num).LAST_UPDATE_DATE := SYSDATE;
604 util_forecasts_table(l_record_num).LAST_UPDATED_BY := FND_GLOBAL.user_id;
605 util_forecasts_table(l_record_num).CREATION_DATE := SYSDATE;
606 util_forecasts_table(l_record_num).CREATED_BY := FND_GLOBAL.user_id;
607 util_forecasts_table(l_record_num).LAST_UPDATE_LOGIN := FND_GLOBAL.login_id;
608 util_forecasts_table(l_record_num).ATTRIBUTE_CATEGORY := NULL;
609 util_forecasts_table(l_record_num).ATTRIBUTE1 := NULL;
610 util_forecasts_table(l_record_num).ATTRIBUTE2 := NULL;
611 util_forecasts_table(l_record_num).ATTRIBUTE3 := NULL;
612 util_forecasts_table(l_record_num).ATTRIBUTE4 := NULL;
613 util_forecasts_table(l_record_num).ATTRIBUTE5 := NULL;
614 util_forecasts_table(l_record_num).ATTRIBUTE6 := NULL;
615 util_forecasts_table(l_record_num).ATTRIBUTE7 := NULL;
616 util_forecasts_table(l_record_num).ATTRIBUTE8 := NULL;
617 util_forecasts_table(l_record_num).ATTRIBUTE9 := NULL;
618 util_forecasts_table(l_record_num).ATTRIBUTE10 := NULL;
619 util_forecasts_table(l_record_num).ATTRIBUTE11 := NULL;
620 util_forecasts_table(l_record_num).ATTRIBUTE12 := NULL;
621 util_forecasts_table(l_record_num).ATTRIBUTE13 := NULL;
622 util_forecasts_table(l_record_num).ATTRIBUTE14 := NULL;
623 util_forecasts_table(l_record_num).ATTRIBUTE15 := NULL;
624 -- increase record number by 1
625 l_record_num := l_record_num + 1;
626 l_run_no := l_forecast_details_tbl.NEXT(l_run_no); -- get index of next element
627 END LOOP;
628 END IF;
629
630 END LOOP;
631 -- usage counters traversal over
632 G_DEBUG_LINE_NUM := 140;
633 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
634 THEN
635 fnd_log.string (G_DEBUG_STMT, l_debug_module, ' Forecasted utilization calculation ends for fleet - ' || l_fleet_name);
636 END IF;
637
638 END LOOP;
639 -- Looping through all fleets over
640
641 IF(util_forecasts_table.count > 0) THEN
642 l_run_no :=1;
643 WHILE l_run_no IS NOT NULL
644 LOOP
645 G_DEBUG_LINE_NUM := 150;
646 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
647 THEN
648 fnd_log.string (G_DEBUG_STMT, l_debug_module, 'util_forecasts_table -- record - ' || l_run_no || ' - FLEET_UTILIZATION_ID = ' || util_forecasts_table(l_run_no).FLEET_UTILIZATION_ID);
649 fnd_log.string (G_DEBUG_STMT, l_debug_module, ' - PERIOD_START_DATE = ' || util_forecasts_table(l_run_no).PERIOD_START_DATE || ' - PERIOD_END_DATE = ' || util_forecasts_table(l_run_no).PERIOD_END_DATE);
650 fnd_log.string (G_DEBUG_STMT, l_debug_module, ' - UOM_CODE = ' || util_forecasts_table(l_run_no).UOM_CODE || ' - FLEET_SIZE = ' || util_forecasts_table(l_run_no).FLEET_SIZE);
651 fnd_log.string (G_DEBUG_STMT, l_debug_module, ' - ACTUAL_DAILY_USAGE = ' || util_forecasts_table(l_run_no).ACTUAL_DAILY_USAGE || ' - ACTUAL_DAILY_SORTIES = ' || util_forecasts_table(l_run_no).ACTUAL_DAILY_SORTIES);
652 fnd_log.string (G_DEBUG_STMT, l_debug_module, ' - FORECASTED_DAILY_USAGE = ' || util_forecasts_table(l_run_no).FORECASTED_DAILY_USAGE || ' - FORECASTED_DAILY_SORTIES = ' || util_forecasts_table(l_run_no).FORECASTED_DAILY_SORTIES);
653 END IF;
654
655 l_run_no := util_forecasts_table.NEXT(l_run_no); -- get index of next element
656 END LOOP;
657 END IF;
658
659 G_DEBUG_LINE_NUM := 160;
660 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
661 THEN
662 fnd_log.string (G_DEBUG_STMT, l_debug_module, ' utilization calculation over for all fleets. insertion of data into AHL_FLEET_UTILIZATION begins');
663 END IF;
664
665 -- Isert bulk data into DB table
666 FORALL x IN util_forecasts_table.FIRST..util_forecasts_table.LAST
667 INSERT
668 INTO AHL_FLEET_UTILIZATION
669 (
670 FLEET_UTILIZATION_ID,
671 OBJECT_VERSION_NUMBER,
672 FLEET_HEADER_ID,
673 NAME,
674 OPERATING_ORG_ID,
675 PERIOD_START_DATE,
676 PERIOD_END_DATE,
677 UOM_CODE,
678 FLEET_SIZE,
679 FORECASTED_DAILY_USAGE,
680 FORECASTED_DAILY_SORTIES,
681 ACTUAL_DAILY_USAGE,
682 ACTUAL_DAILY_SORTIES,
683 SIMULATION_PLAN_ID,
684 SECURITY_GROUP_ID,
685 LAST_UPDATE_DATE,
686 LAST_UPDATED_BY,
687 CREATION_DATE,
688 CREATED_BY,
689 LAST_UPDATE_LOGIN,
690 ATTRIBUTE_CATEGORY,
691 ATTRIBUTE1,
692 ATTRIBUTE2,
693 ATTRIBUTE3,
694 ATTRIBUTE4,
695 ATTRIBUTE5,
696 ATTRIBUTE6,
697 ATTRIBUTE7,
698 ATTRIBUTE8,
699 ATTRIBUTE9,
700 ATTRIBUTE10,
701 ATTRIBUTE11,
702 ATTRIBUTE12,
703 ATTRIBUTE13,
704 ATTRIBUTE14,
705 ATTRIBUTE15
706 )
707 VALUES
708 (
709 util_forecasts_table(x).FLEET_UTILIZATION_ID,
710 util_forecasts_table(x).object_version_number,
711 util_forecasts_table(x).FLEET_HEADER_ID,
712 util_forecasts_table(x).NAME,
713 util_forecasts_table(x).OPERATING_ORG_ID,
714 util_forecasts_table(x).PERIOD_START_DATE,
715 util_forecasts_table(x).PERIOD_END_DATE,
716 util_forecasts_table(x).UOM_CODE,
717 util_forecasts_table(x).FLEET_SIZE,
718 util_forecasts_table(x).FORECASTED_DAILY_USAGE,
719 util_forecasts_table(x).FORECASTED_DAILY_SORTIES,
720 util_forecasts_table(x).ACTUAL_DAILY_USAGE,
721 util_forecasts_table(x).ACTUAL_DAILY_SORTIES,
722 util_forecasts_table(x).SIMULATION_PLAN_ID,
723 util_forecasts_table(x).SECURITY_GROUP_ID,
724 util_forecasts_table(x).LAST_UPDATE_DATE,
725 util_forecasts_table(x).LAST_UPDATED_BY,
726 util_forecasts_table(x).CREATION_DATE,
727 util_forecasts_table(x).CREATED_BY,
728 util_forecasts_table(x).LAST_UPDATE_LOGIN,
729 util_forecasts_table(x).ATTRIBUTE_CATEGORY,
730 util_forecasts_table(x).ATTRIBUTE1,
731 util_forecasts_table(x).ATTRIBUTE2,
732 util_forecasts_table(x).ATTRIBUTE3,
733 util_forecasts_table(x).ATTRIBUTE4,
734 util_forecasts_table(x).ATTRIBUTE5,
735 util_forecasts_table(x).ATTRIBUTE6,
736 util_forecasts_table(x).ATTRIBUTE7,
737 util_forecasts_table(x).ATTRIBUTE8,
738 util_forecasts_table(x).ATTRIBUTE9,
739 util_forecasts_table(x).ATTRIBUTE10,
740 util_forecasts_table(x).ATTRIBUTE11,
741 util_forecasts_table(x).ATTRIBUTE12,
742 util_forecasts_table(x).ATTRIBUTE13,
743 util_forecasts_table(x).ATTRIBUTE14,
744 util_forecasts_table(x).ATTRIBUTE15
745 );
746
747 G_DEBUG_LINE_NUM := 170;
748 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
749 THEN
750 fnd_log.string (G_DEBUG_STMT, l_debug_module, ' insertion of data into AHL_FLEET_UTILIZATION ends');
751 END IF;
752
753 IF (util_forecasts_table IS NOT NULL AND util_forecasts_table.COUNT > 0)
754 THEN
755 util_forecasts_table.delete;
756
757 END IF;
758
759 G_DEBUG_LINE_NUM := 180;
760 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
761 THEN
762 fnd_log.string (G_DEBUG_STMT, l_debug_module, ' util_forecasts_table deleted');
763 END IF;
764
765 -- Commit the data to table
766 COMMIT;
767
768 retcode := 0;
769 --errbuf := 'Error while running program';
770
771 G_DEBUG_LINE_NUM := 180;
772 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
773 THEN
774 fnd_log.string (G_DEBUG_STMT, l_debug_module, ' data committed');
775 END IF;
776
777 EXCEPTION
778 WHEN FND_API.G_EXC_ERROR
779 THEN
780 -- Rollback changes since last commit
781 ROLLBACK TO HANDLE_FLEET_UTIL_ERRORS;
782 retcode := 1;
783 errbuf := 'Error while running program';
784
785 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
786 THEN
787 g_err_msg := substr(SQLERRM, 1, 3900);
788 g_err_code := SQLCODE;
789 fnd_log.string (G_DEBUG_STMT, l_debug_module, 'Error in procedure populate_fleet_utilization procedure');
790 fnd_log.string (G_DEBUG_STMT, l_debug_module, 'error code is '|| g_err_code);
791 fnd_log.string (G_DEBUG_STMT, l_debug_module, 'error message is '|| g_err_msg);
792 fnd_file.put_line(fnd_file.log, 'AHL_FLEET_UTIL_HISTORY_PVT.populate_fleet_utilization Threw unexpected error with error code - '
793 || g_err_code || ' and error message as - ' || g_err_msg || ' and errbuf - ' || errbuf);
794 END IF;
795
796 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
797 THEN
798 -- Rollback changes since last commit
799 ROLLBACK TO HANDLE_FLEET_UTIL_ERRORS;
800 retcode := 1;
801 errbuf := 'Error while running program';
802
803 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
804 THEN
805 g_err_msg := substr(SQLERRM, 1, 3900);
806 g_err_code := SQLCODE;
807 fnd_log.string (G_DEBUG_STMT, l_debug_module, 'Error in procedure populate_fleet_utilization procedure');
808 fnd_log.string (G_DEBUG_STMT, l_debug_module, 'error code is '|| g_err_code);
809 fnd_log.string (G_DEBUG_STMT, l_debug_module, 'error message is '|| g_err_msg);
810 fnd_file.put_line(fnd_file.log, 'AHL_FLEET_UTIL_HISTORY_PVT.populate_fleet_utilization Threw unexpected error with error code - '
811 || g_err_code || ' and error message as - ' || g_err_msg || ' and errbuf - ' || errbuf);
812 END IF;
813
814 WHEN OTHERS
815 THEN
816 -- Rollback changes since last commit
817 ROLLBACK TO HANDLE_FLEET_UTIL_ERRORS;
818 retcode := 1;
819 errbuf := 'Error while running program';
820
821 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
822 THEN
823 g_err_msg := substr(SQLERRM, 1, 3900);
824 g_err_code := SQLCODE;
825 fnd_log.string (G_DEBUG_STMT, l_debug_module, 'Error in procedure populate_fleet_utilization procedure');
826 fnd_log.string (G_DEBUG_STMT, l_debug_module, 'error code is '|| g_err_code);
827 fnd_log.string (G_DEBUG_STMT, l_debug_module, 'error message is '|| g_err_msg);
828 fnd_file.put_line(fnd_file.log, 'AHL_FLEET_UTIL_HISTORY_PVT.populate_fleet_utilization Threw unexpected error with error code - '
829 || g_err_code || ' and error message as - ' || g_err_msg || ' and errbuf - ' || errbuf);
830 END IF;
831
832 END populate_fleet_utilization;
833
834 /*
835 --
836 Procedure Name: Get_Forecast_Records
837 Type: Private
838 Function: This procedure is called by populate_fleet_utilization procedure to get forecasted utilization data for the fleet.
839 --
840 */
841 -- Start of Procedure Get_Forecast_Records --
842 PROCEDURE Get_Forecast_Records
843 (
844 p_fleet_header_id IN NUMBER,
845 p_simulation_plan_id IN NUMBER,
846 p_usage_counter_uom_code IN VARCHAR,
847 p_sorties_counter_uom_code IN VARCHAR,
848 p_forecast_details_tbl OUT NOCOPY forecast_details_tbl_type
849 )
850 AS
851 l_debug_module VARCHAR2(1000) := 'ahl.plsql.AHL_FLEET_UTIL_HISTORY_PVT.Get_Forecast_Records';
852 l_flt_start_date DATE;
853 l_flt_end_date DATE;
854 l_index NUMBER := 1;
855 l_run_no NUMBER := 1;
856 l_unit_config_header_id NUMBER;
857 l_assoc_start DATE;
858 l_assoc_end DATE;
859 l_prev_assoc_start DATE;
860 l_prev_assoc_end DATE;
861 l_period_start DATE;
862 l_period_end DATE;
863 l_forecast_start DATE;
864 l_forecast_end DATE;
865 l_all_unit_forecast_usage NUMBER := 0;
866 l_daily_usage NUMBER := 0;
867 l_daily_usage_avg NUMBER :=0;
868 l_all_unit_forecast_sorties NUMBER := 0;
869 l_sorties_val NUMBER := 0;
870 l_sorties_val_total NUMBER := 0;
871 l_fleet_size NUMBER := 0;
872
873 -- Cursor to calculate fleet size - average units for the period provided
874 CURSOR GetFleetSize
875 (
876 p_fleet_header_id NUMBER, p_sim_plan_id NUMBER, p_start_date DATE, p_end_date DATE
877 )
878 IS
879 SELECT decode((SUM(unit_per_day)/(p_end_date - p_start_date + 1)), null, 0, (SUM(unit_per_day)/(p_end_date - p_start_date + 1))) unit_per_month
880 FROM
881 (SELECT ddate,
882 COUNT(*) unit_per_day
883 FROM
884 (SELECT rownum,
885 (p_start_date + rownum - 1) ddate
886 FROM fnd_new_messages
887 WHERE rownum <= (p_end_date - p_start_date + 1)
888 ) dtab,
889 ahl_fleet_unit_assocs fleet
890 WHERE fleet.fleet_header_id = p_fleet_header_id
891 and fleet.simulation_plan_id = p_sim_plan_id
892 AND dtab.ddate BETWEEN association_start AND NVL(association_end, dtab.ddate)
893 GROUP BY ddate
894 );
895
896 -- Get the associated units to the fleet
897 CURSOR GetAssociatedUnits(p_fleet_header_id NUMBER, p_simulation_plan_id NUMBER, p_period_start DATE, p_period_end DATE)
898 IS
899 SELECT association_start,
900 association_end,
901 (association_end - association_start + 1) assoc_days
902 FROM ahl_fleet_unit_assocs
903 WHERE fleet_header_id = p_fleet_header_id
904 AND simulation_plan_id = p_simulation_plan_id
905 AND ((association_start BETWEEN p_period_start AND p_period_end)
906 OR (association_end BETWEEN p_period_start AND p_period_end)
907 OR (p_period_start BETWEEN association_start AND association_end)
908 OR (p_period_end BETWEEN association_start AND association_end))
909 ORDER BY association_start,
910 association_end;
911
912 -- Get the setup forecast data from
913 CURSOR ahl_fleet_forecast_details(p_fleet_header_id IN NUMBER, p_uom_code VARCHAR)
914 IS
915 SELECT uom_code,
916 decode(sign(period_start_date - sysdate), 1, period_start_date, sysdate + 1) period_start_date,
917 period_end_date,
918 forecasted_daily_usage,
919 fleet_header_id
920 FROM AHL_FLEET_UTLZN_FORECAST
921 WHERE fleet_header_id = p_fleet_header_id
922 AND uom_code = p_uom_code
923 ORDER BY period_start_date;
924
925 /*SELECT FUF.uom_code, FUF.period_start_date, FUF.period_end_date,FUA.association_start ,FUA.association_end,
926 FUF.forecasted_daily_usage usage_per_day, FUA.fleet_header_id, FUA.unit_config_header_id, FUA.simulation_plan_id
927 FROM ahl_fleet_utlzn_forecast FUF ,ahl_fleet_unit_assocs FUA
928 WHERE trunc(nvl(period_end_date, sysdate)) >= trunc(sysdate)
929 AND trunc(nvl(association_end, sysdate)) >= trunc(sysdate)
930 AND trunc(nvl(period_end_date, association_start)) >= trunc(association_start)
931 AND FUA.simulation_plan_id = p_simulation_plan_id
932 AND FUA.fleet_header_id = p_fleet_header_id
933 --and FUA.unit_config_header_id = p_unit_config_header_id
934 order by uom_code, association_start, period_start_date;*/
935
936 -- Get the sorties counter val from UI
937 CURSOR ahl_fleet_sorties_counter_val(p_fleet_header_id NUMBER, p_sorties_counter_uom_code VARCHAR, period_start DATE, period_end DATE)
938 IS
939 SELECT rownum,
940 forecasted_daily_usage
941 FROM ahl_fleet_utlzn_forecast
942 WHERE fleet_header_id = p_fleet_header_id
943 AND UOM_CODE = p_sorties_counter_uom_code
944 AND ((period_start_date BETWEEN period_start AND period_end)
945 OR (period_end_date BETWEEN period_start AND period_end)
946 OR (period_start BETWEEN period_start_date AND period_end_date)
947 OR (period_end BETWEEN period_start_date AND period_end_date));
948
949 BEGIN
950
951 G_DEBUG_LINE_NUM := 1;
952 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL)
953 THEN
954 fnd_log.string (G_DEBUG_PROC, l_debug_module, 'Start Get_Forecast_Records');
955 fnd_log.string (G_DEBUG_PROC, l_debug_module, 'Input Parameter: p_fleet_header_id:' || p_fleet_header_id || ', p_simulation_plan_id:' || p_simulation_plan_id
956 || ', p_usage_counter_uom_code:' || p_sorties_counter_uom_code || ', p_sorties_counter_uom_code:' || p_usage_counter_uom_code);
957 END IF;
958
959 -- get the forecast data from the setup table for the fleet and UOM code
960 FOR l_forecast_detail_rec IN ahl_fleet_forecast_details(p_fleet_header_id,p_usage_counter_uom_code)
961 LOOP
962 l_forecast_start := l_forecast_detail_rec.period_start_date;
963 l_forecast_end := l_forecast_detail_rec.period_end_date;
964 l_daily_usage := l_forecast_detail_rec.forecasted_daily_usage;
965 if(l_forecast_end is NULL) THEN
966 l_forecast_end := Get_Forecasting_Window_Date;
967 end if;
968 G_DEBUG_LINE_NUM := 10;
969 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
970 THEN
971 fnd_log.string (G_DEBUG_STMT, l_debug_module, ' current l_forecast_start = ' || l_forecast_start || ', l_forecast_end = ' || l_forecast_end
972 || ', l_daily_usage:' || l_daily_usage);
973 END IF;
974
975 -- get the fleet size for the period
976 OPEN GetFleetSize(p_fleet_header_id, p_simulation_plan_id, l_forecast_start, l_forecast_end);
977 FETCH GetFleetSize INTO l_fleet_size;
978
979 l_fleet_size := ROUND(l_fleet_size, 2);
980 G_DEBUG_LINE_NUM := 20;
981 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
982 THEN
983 fnd_log.string (G_DEBUG_STMT, l_debug_module, ' calculated l_fleet_size = ' || l_fleet_size || ' for p_fleet_header_id - ' || p_fleet_header_id);
984 END IF;
985 CLOSE GetFleetSize;
986
987 if(l_fleet_size = 0)
988 then
989 l_daily_usage := 0;
990 end if;
991
992 -- get the sorties val for the specified period
993 FOR l_sorties_counter_rec IN ahl_fleet_sorties_counter_val(p_fleet_header_id,p_sorties_counter_uom_code, l_forecast_start, l_forecast_end)
994 LOOP
995 l_sorties_val_total := l_sorties_val_total + l_sorties_counter_rec.forecasted_daily_usage;
996 l_sorties_val := l_sorties_val_total / l_sorties_counter_rec.rownum;
997 END LOOP;
998 l_sorties_val := ROUND((l_sorties_val * l_fleet_size * 30), 2);
999 G_DEBUG_LINE_NUM := 30;
1000 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
1001 THEN
1002 fnd_log.string (G_DEBUG_STMT, l_debug_module, ' forecasted l_sorties_val = ' || l_sorties_val);
1003 END IF;
1004
1005 -- calculate daily usage considering all asociated units
1006 /*FOR l_unit_config_rec IN GetAssociatedUnits(p_fleet_header_id,p_simulation_plan_id, l_forecast_start, l_forecast_end)
1007 LOOP
1008 --l_unit_config_header_id := l_unit_config_rec.unit_config_header_id;
1009 IF(l_unit_config_rec.association_start <= l_forecast_start AND l_unit_config_rec.association_end < l_forecast_end) THEN
1010 l_all_unit_forecast_usage := l_all_unit_forecast_usage + l_daily_usage;
1011 elsif(l_unit_config_rec.association_start > l_forecast_start AND l_unit_config_rec.association_end < l_forecast_end) THEN
1012 l_all_unit_forecast_usage := l_all_unit_forecast_usage + l_daily_usage;
1013 elsif(l_unit_config_rec.association_start > l_forecast_start AND l_unit_config_rec.association_end > l_forecast_end) THEN
1014 l_all_unit_forecast_usage := l_all_unit_forecast_usage + l_daily_usage;
1015 elsif(l_unit_config_rec.association_start < l_forecast_start AND l_unit_config_rec.association_end > l_forecast_end) THEN
1016 l_all_unit_forecast_usage := l_all_unit_forecast_usage + l_daily_usage;
1017 END IF;
1018 END LOOP; -- END GetAssociatedUnits
1019
1020 l_daily_usage_avg := ROUND((l_daily_usage * l_fleet_size), 2);
1021 G_DEBUG_LINE_NUM := 40;
1022 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
1023 THEN
1024 fnd_log.string (G_DEBUG_STMT, l_debug_module, ' forecasted l_daily_usage_avg = ' || l_daily_usage_avg);
1025 END IF; */
1026
1027 -- Populate table for the calculated data.
1028 p_forecast_details_tbl(l_index).uom_code := p_usage_counter_uom_code;
1029 p_forecast_details_tbl(l_index).start_date := l_forecast_start;
1030 p_forecast_details_tbl(l_index).end_date := l_forecast_end;
1031 p_forecast_details_tbl(l_index).usage_per_day := l_daily_usage;
1032 p_forecast_details_tbl(l_index).sorties_val := l_sorties_val;
1033 p_forecast_details_tbl(l_index).fleet_size := l_fleet_size;
1034 l_index := l_index + 1;
1035 END LOOP; -- END ahl_fleet_forecast_details
1036
1037
1038 END Get_Forecast_Records;
1039
1040 /*
1041 --
1042 Function Name: Get_Forecasting_Window_Date
1043 Type: private
1044 Function: This function calculates the last day of the rolling window based on profile values.
1045 Parameters:
1046 --
1047 */
1048 -- Start of Procedure Get_Forecasting_Window_Date --
1049 -----------------------------------------------------------------------
1050 -- This function calculates the last day of the rolling window based on
1051 -- profile values.
1052 FUNCTION Get_Forecasting_Window_Date
1053 RETURN DATE
1054 IS
1055 l_debug_module VARCHAR2(1000) := 'ahl.plsql.AHL_FLEET_UTIL_HISTORY_PVT.Get_Forecasting_Window_Date';
1056 l_date_uom VARCHAR2(30);
1057 l_value NUMBER;
1058 l_last_day_of_window DATE;
1059 BEGIN
1060
1061 G_DEBUG_LINE_NUM := 1;
1062 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL)
1063 THEN
1064 fnd_log.string (G_DEBUG_PROC, l_debug_module, 'Start Get_Forecasting_Window_Date');
1065 END IF;
1066
1067 BEGIN
1068 l_date_uom := FND_PROFILE.VALUE('AHL_UMP_MAX_PLANNING_UOM');
1069 l_value := to_number(FND_PROFILE.VALUE('AHL_UMP_MAX_PLANNING_VALUE'));
1070 IF (l_date_uom IS NULL) THEN
1071 l_last_day_of_window := SYSDATE;
1072 ELSIF (l_value <= 0 OR l_value IS NULL) THEN
1073 l_last_day_of_window := SYSDATE;
1074 ELSIF (l_date_uom = 'YR') THEN
1075 l_last_day_of_window := ADD_MONTHS(SYSDATE, 12 * l_value);
1076 ELSIF (l_date_uom = 'MTH') THEN
1077 l_last_day_of_window := ADD_MONTHS(SYSDATE, l_value);
1078 ELSIF (l_date_uom = 'WK') THEN
1079 l_last_day_of_window := SYSDATE + (7 * l_value);
1080 ELSIF (l_date_uom = 'DAY') THEN
1081 l_last_day_of_window := SYSDATE + l_value;
1082 END IF;
1083
1084 EXCEPTION
1085 WHEN VALUE_ERROR THEN
1086 l_last_day_of_window := SYSDATE;
1087 WHEN INVALID_NUMBER THEN
1088 l_last_day_of_window := SYSDATE;
1089 END;
1090
1091 G_DEBUG_LINE_NUM := 10;
1092 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
1093 THEN
1094 fnd_log.string(G_DEBUG_STMT, l_debug_module, 'last day of window' || l_last_day_of_window || ', profile uom:' || l_date_uom
1095 || ', profile value:' || l_value);
1096 END IF;
1097
1098 -- return date.
1099 RETURN l_last_day_of_window;
1100 END Get_Forecasting_Window_Date;
1101
1102
1103 END AHL_FLEET_UTIL_HISTORY_PVT;
1104