1 PACKAGE BODY IGI_IAC_PROJ_PKG AS
2 -- $Header: igiiacpb.pls 120.25 2007/08/01 10:47:04 npandya ship $
3
4 --===========================FND_LOG.START=====================================
5
6 g_state_level NUMBER := FND_LOG.LEVEL_STATEMENT;
7 g_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
8 g_event_level NUMBER := FND_LOG.LEVEL_EVENT;
9 g_excep_level NUMBER := FND_LOG.LEVEL_EXCEPTION;
10 g_error_level NUMBER := FND_LOG.LEVEL_ERROR;
11 g_unexp_level NUMBER := FND_LOG.LEVEL_UNEXPECTED;
12 g_path VARCHAR2(100) := 'IGI.PLSQL.igiiacpb.IGI_IAC_PROJ_PKG.';
13
14 --===========================FND_LOG.END=====================================
15
16 -- ===================================================================
17 -- PROCEDURE Update_Status:
18 -- ===================================================================
19 PROCEDURE Update_Status(x_projection_id igi_iac_projections.projection_id%TYPE,
20 x_status igi_iac_projections.status%TYPE
21 )
22 IS
23
24
25 BEGIN
26 UPDATE igi_iac_projections
27 SET status = x_status
28 WHERE projection_id = x_projection_id;
29
30 END Update_Status;
31
32 -- ===================================================================
33 -- FUNCTION Get_Period_Info_For_Counter : Gets period related
34 -- information for the period counter
35 -- ===================================================================
36 FUNCTION Get_Period_Info_for_Counter( P_book_type_Code IN VARCHAR2 ,
37 P_period_Counter IN NUMBER ,
38 P_prd_rec OUT NOCOPY igi_iac_types.prd_rec
39 )
40 RETURN BOOLEAN AS
41 l_ret_flag BOOLEAN;
42 BEGIN
43
44 l_ret_flag := igi_iac_common_utils.get_Period_Info_for_Counter( P_book_type_Code,
45 p_period_Counter,
46 P_prd_rec );
47 RETURN TRUE;
48
49 EXCEPTION
50 WHEN OTHERS THEN
51 igi_iac_debug_pkg.debug_unexpected_msg(g_path||'Get_Period_Info_for_Counter');
52 RETURN FALSE;
53 END;
54
55
56 -- ===================================================================
57 -- FUNCTION Non_Depreciating_Asset: The function will check if the
58 -- asset is a non depreciating asset
59 -- ===================================================================
60 FUNCTION Non_Depreciating_Asset(x_asset_id IN fa_books.asset_id%TYPE,
61 x_book_code IN fa_books.book_type_code%TYPE)
62 RETURN BOOLEAN AS
63 l_exists NUMBER := 0;
64
65 BEGIN
66
67 SELECT count(*)
68 INTO l_exists
69 FROM fa_books
70 WHERE depreciate_flag = 'NO'
71 AND transaction_header_id_out IS NULL
72 AND book_type_code = x_book_code
73 AND asset_id = x_asset_id;
74
75 IF (l_exists = 0) THEN
76 RETURN FALSE;
77 ELSE
78 RETURN TRUE;
79 END IF;
80
81 EXCEPTION
82 WHEN OTHERS THEN
83 igi_iac_debug_pkg.debug_other_string(g_unexp_level,g_path||'Non_Depreciating_Asset' ,'Non Depr Asset'||sqlerrm);
84 RETURN FALSE;
85
86 END Non_Depreciating_Asset;
87
88 -- ===================================================================
89 -- PROCEDURE Submit_Report_Request: Procedure to submit the
90 -- concurrent request for running the Projections report
91 -- ===================================================================
92 PROCEDURE submit_report_request(p_projection_id IN igi_iac_projections.projection_id%type,
93 p_book_type_code IN fa_books.book_type_code%type,
94 p_reval_period_num IN igi_iac_projections.revaluation_period%type,
95 p_concat_cat IN varchar2,
96 p_start_period_name IN varchar2,
97 p_end_period_name IN varchar2,
98 p_rx_attribute_set IN fa_rx_attrsets_b.attribute_set%TYPE,
99 p_rx_output_format IN fnd_lookups.lookup_code%TYPE) IS
100
101 l_request_id NUMBER;
102 l_Err_Buf VARCHAR2(1000);
103 l_Ret_Code NUMBER(3);
104 l_message varchar2(1000);
105
106 l_report_id NUMBER;
107 IGI_IAC_REQUEST_SUB_ERR Exception;
108 BEGIN
109 -- Get the report id
110 SELECT report_id
111 INTO l_report_id
112 FROM fa_rx_reports r,
113 fnd_concurrent_programs c,
114 fnd_application a
115 WHERE r.application_id = a.application_id
116 AND r.application_id = c.application_id
117 AND r.concurrent_program_id = c.concurrent_program_id
118 AND a.application_short_name = 'IGI'
119 AND c.concurrent_program_name = 'RXIGIIAP';
120
121
122 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
123 APPLICATION => 'IGI',
124 PROGRAM => 'IGIIARXP', -- 'IGIIACPR',
125 DESCRIPTION => 'Inflation Accounting : Projections Report',
126 START_TIME => NULL,
127 SUB_REQUEST => FALSE,
128 ARGUMENT1 => 'SUBMIT', -- p_projection_id,
129 ARGUMENT2 => 'IGI',
130 ARGUMENT3 => 'RXIGIIAP',
131 ARGUMENT4 => l_report_id,
132 ARGUMENT5 => p_rx_attribute_set,
133 ARGUMENT6 => p_rx_output_format,
134 ARGUMENT7 => p_projection_id,
135 ARGUMENT8 => p_book_type_code,
136 ARGUMENT9 => p_reval_period_num,
137 ARGUMENT10 => p_concat_cat,
138 ARGUMENT11 => p_start_period_name,
139 ARGUMENT12 => p_end_period_name,
140 ARGUMENT13 => p_rx_attribute_set,
141 ARGUMENT14 => p_rx_output_format,
142 ARGUMENT15 => chr(0),
143 ARGUMENT16 => NULL,
144 ARGUMENT17 => NULL,
145 ARGUMENT18 => NULL,
146 ARGUMENT19 => NULL,
147 ARGUMENT20 => NULL,
148 ARGUMENT21 => NULL,
149 ARGUMENT22 => NULL,
150 ARGUMENT23 => NULL,
151 ARGUMENT24 => NULL,
152 ARGUMENT25 => NULL,
153 ARGUMENT26 => NULL,
154 ARGUMENT27 => NULL,
155 ARGUMENT28 => NULL,
156 ARGUMENT29 => NULL,
157 ARGUMENT30 => NULL,
158 ARGUMENT31 => NULL,
159 ARGUMENT32 => NULL,
160 ARGUMENT33 => NULL,
161 ARGUMENT34 => NULL,
162 ARGUMENT35 => NULL,
163 ARGUMENT36 => NULL,
164 ARGUMENT37 => NULL,
165 ARGUMENT38 => NULL,
166 ARGUMENT39 => NULL,
167 ARGUMENT40 => NULL,
168 ARGUMENT41 => NULL,
169 ARGUMENT42 => NULL,
170 ARGUMENT43 => NULL,
171 ARGUMENT44 => NULL,
172 ARGUMENT45 => NULL,
173 ARGUMENT46 => NULL,
174 ARGUMENT47 => NULL,
175 ARGUMENT48 => NULL,
176 ARGUMENT49 => NULL,
177 ARGUMENT50 => NULL,
178 ARGUMENT51 => NULL,
179 ARGUMENT52 => NULL,
180 ARGUMENT53 => NULL,
181 ARGUMENT54 => NULL,
182 ARGUMENT55 => NULL,
183 ARGUMENT56 => NULL,
184 ARGUMENT57 => NULL,
185 ARGUMENT58 => NULL,
186 ARGUMENT59 => NULL,
187 ARGUMENT60 => NULL,
188 ARGUMENT61 => NULL,
189 ARGUMENT62 => NULL,
190 ARGUMENT63 => NULL,
191 ARGUMENT64 => NULL,
192 ARGUMENT65 => NULL,
193 ARGUMENT66 => NULL,
194 ARGUMENT67 => NULL,
195 ARGUMENT68 => NULL,
196 ARGUMENT69 => NULL,
197 ARGUMENT70 => NULL,
198 ARGUMENT71 => NULL,
199 ARGUMENT72 => NULL,
200 ARGUMENT73 => NULL,
201 ARGUMENT74 => NULL,
202 ARGUMENT75 => NULL,
203 ARGUMENT76 => NULL,
204 ARGUMENT77 => NULL,
205 ARGUMENT78 => NULL,
206 ARGUMENT79 => NULL,
207 ARGUMENT80 => NULL,
208 ARGUMENT81 => NULL,
209 ARGUMENT82 => NULL,
210 ARGUMENT83 => NULL,
211 ARGUMENT84 => NULL,
212 ARGUMENT85 => NULL,
213 ARGUMENT86 => NULL,
214 ARGUMENT87 => NULL,
215 ARGUMENT88 => NULL,
216 ARGUMENT89 => NULL,
217 ARGUMENT90 => NULL,
218 ARGUMENT91 => NULL,
219 ARGUMENT92 => NULL,
220 ARGUMENT93 => NULL,
221 ARGUMENT94 => NULL,
222 ARGUMENT95 => NULL,
223 ARGUMENT96 => NULL,
224 ARGUMENT97 => NULL,
225 ARGUMENT98 => NULL,
226 ARGUMENT99 => NULL,
227 ARGUMENT100 => NULL
228 );
229
230 IF l_request_id = 0 THEN
231 raise IGI_IAC_REQUEST_SUB_ERR;
232 ELSE
233 commit;
234 FND_MESSAGE.SET_NAME('IGI','IGI_IAC_SUBMIT_REQUEST');
235 FND_MESSAGE.SET_TOKEN('REQUEST_ID',l_request_id);
236 l_message:= fnd_message.get;
237 igi_iac_debug_pkg.debug_other_string(g_event_level,g_path||'submit_report_request','Submit Request'||l_message);
238
239 END IF;
240 EXCEPTION
241 WHEN IGI_IAC_REQUEST_SUB_ERR then
242 fnd_message.retrieve(l_message);
243 igi_iac_debug_pkg.debug_other_string(g_event_level,g_path||'submit_report_request','Submit Request'||l_message);
244 END submit_report_request;
245
246 -- ===================================================================
247 -- FUNCTION Get_Price_Index_Val: get the price index value for the book,
248 -- category and period
249 -- ===================================================================
250 FUNCTION Get_Price_Index_Val(p_book_code fa_books.book_type_code%TYPE,
251 p_category_id fa_category_books.category_id%TYPE,
252 p_period_ctr fa_deprn_periods.period_counter%TYPE,
253 p_price_index_val OUT NOCOPY igi_iac_cal_idx_values.current_price_index_value%TYPE
254 )
255 RETURN BOOLEAN
256 IS
257
258 -- To get deprn calendar
259 CURSOR c_get_calendar(n_book_type_code fa_books.book_type_code%TYPE)
260 IS
261 SELECT deprn_calendar
262 FROM fa_book_controls
263 WHERE book_type_code = n_book_type_code;
264
265 -- To get the price index value for a given period
266 CURSOR c_get_price_index_value(n_book_code fa_books.book_type_code%TYPE,
267 n_category_id fa_category_books.category_id%TYPE,
268 n_start_date fa_calendar_periods.start_date%TYPE,
269 n_end_date fa_calendar_periods.end_date%TYPE,
270 n_calendar_type fa_calendar_periods.calendar_type%TYPE
271 )
272 IS
273 SELECT current_price_index_value
274 FROM igi_iac_cal_idx_values
275 WHERE date_from = n_start_date
276 AND date_to = n_end_date
277 AND cal_price_index_link_id = (SELECT cal_price_index_link_id
278 FROM igi_iac_cal_price_indexes
279 WHERE calendar_type= n_calendar_type
280 AND price_index_id = (SELECT price_index_id
281 FROM igi_iac_category_books
282 WHERE book_type_code = n_book_code
283 AND category_id= n_category_id));
284
285 l_prd_rec igi_iac_types.prd_rec;
286 l_ret_flag BOOLEAN;
287 l_calendar fa_calendar_types.calendar_type%type;
288
289
290 BEGIN
291 -- Get Price index value
292 l_ret_flag := get_period_info_for_counter(p_book_code,
293 p_period_ctr,
294 l_prd_rec
295 );
296 OPEN c_get_calendar(p_book_code);
297 FETCH c_get_calendar INTO l_calendar;
298 CLOSE c_get_calendar;
299
300 OPEN c_get_price_index_value(p_book_code,
301 p_category_id,
302 l_prd_rec.period_start_date,
303 l_prd_rec.period_end_date,
304 l_calendar
305 );
306 FETCH c_get_price_index_value INTO p_price_index_val;
307
308
309 CLOSE c_get_price_index_value;
310
311 RETURN TRUE;
312
313 EXCEPTION
314 WHEN OTHERS THEN
315 RETURN FALSE;
316 END Get_Price_Index_Val;
317
318
319 -- ===================================================================
320 -- FUNCTION Get_Reval_Prd_Dpis_Ctr:
321 -- Bug no: 2514825 sowsubra start
322 -- Procedure to get the period_counter of the revaluation period
323 -- before the start_period . If such a period does not exist - ie.
324 -- the book/asset was created afetr the revaluation period then
325 -- return the DPIS period_counter
326 -- the parameter l_reval_prd_ctr returns the either reval_prd or
327 -- dpis ctr
328 -- ===================================================================
329 FUNCTION Get_Reval_Prd_Dpis_Ctr(p_book_code fa_books.book_type_code%TYPE,
330 p_asset_id fa_books.asset_id%TYPE,
331 p_reval_prd_ctr OUT NOCOPY fa_deprn_summary.period_counter%TYPE
332 )
333 RETURN BOOLEAN
334 IS
335
336 -- get the revaluation period counter
337 CURSOR c_get_reval_period(n_book_code fa_books.book_type_code%TYPE,
338 n_asset_id fa_books.asset_id%TYPE
339 )
340 IS
341 SELECT MAX(irr.period_counter) period_counter
342 FROM igi_iac_revaluation_rates irr
343 WHERE irr.book_type_code = n_book_code
344 AND asset_id = n_asset_id
345 AND irr.adjustment_id = (SELECT MAX(adjustment_id)
346 FROM igi_iac_transaction_headers
347 WHERE book_type_code = n_book_code
348 AND asset_id = n_asset_id
349 AND transaction_type_code = 'REVALUATION'
350 AND adjustment_status<>'PREVIEW');
351
352
353 -- Cursor to get the Date Placed in Service of the asset
354 CURSOR c_get_dpis(n_book_code fa_books.book_type_code%TYPE,
355 n_asset_id fa_books.asset_id%TYPE
356 )
357 IS
358 SELECT date_placed_in_service
359 FROM fa_books
360 WHERE book_type_code = n_book_code
361 AND asset_id = n_asset_id
362 AND date_ineffective IS NULL;
363
364 l_dpis fa_books.date_placed_in_service%TYPE;
365 l_ret_flag BOOLEAN;
366 l_get_reval_period fa_deprn_summary.period_counter%TYPE;--c_get_reval_period%type;
367 l_dpis_prd_rec igi_iac_types.prd_rec;
368
369
370 BEGIN
371
372 -- Get the date placed in service
373 OPEN c_get_dpis(p_book_code,
374 p_asset_id);
375 FETCH c_get_dpis INTO l_dpis;
376 CLOSE c_get_dpis;
377
378 -- Get the period info for the dpis
379 l_ret_flag := igi_iac_common_utils.Get_period_info_for_date(p_book_code,
380 l_dpis,
381 l_dpis_prd_rec
382 );
383
384 -- To get the the previous reval period counter
385 OPEN c_get_reval_period(p_book_code,
386 p_asset_id);
387 FETCH c_get_reval_period INTO l_get_reval_period;
388 IF l_get_reval_period IS NOT NULL THEN --(c_get_reval_period%FOUND) THEN
389 p_reval_prd_ctr:=l_get_reval_period;
390 ELSE
391 p_reval_prd_ctr := l_dpis_prd_rec.period_counter;
392 END IF;
393 CLOSE c_get_reval_period;
394
395 RETURN TRUE;
396
397 EXCEPTION
398 WHEN NO_DATA_FOUND THEN
399 igi_iac_debug_pkg.debug_other_string(g_error_level,g_path||'Get_Reval_Prd_Dpis_Ctr' ,'Get Reval PC:'||'No Data found');
400 RETURN FALSE;
401
402 WHEN OTHERS THEN
403 igi_iac_debug_pkg.debug_other_string(g_unexp_level,g_path||'Get_Reval_Prd_Dpis_Ctr','exception Raised ');
404 igi_iac_debug_pkg.debug_other_string(g_unexp_level,g_path||'Get_Reval_Prd_Dpis_Ctr','Get Reval PC:'||sqlerrm);
405 RETURN FALSE;
406
407 END Get_Reval_Prd_Dpis_Ctr;
408
409 -- ===================================================================
410 -- PROCEDURE Get_Next_Period_Ctr: Procedure will retrieve the next
411 -- period counter
412 -- ===================================================================
413 PROCEDURE Get_Next_Period_Ctr(p_period_rec IN igi_iac_types.prd_rec,
414 p_book_code IN fa_books.book_type_code%TYPE,
415 p_next_period_ctr OUT NOCOPY fa_deprn_periods.period_counter%TYPE)
416 IS
417
418 -- get the number of periods per fiscal year
419 CURSOR c_get_num_per_period(n_calendar fa_calendar_types.calendar_type%TYPE)
420 IS
421 SELECT number_per_fiscal_year
422 FROM fa_calendar_types
423 WHERE calendar_type = n_calendar;
424
425 -- Get deprn calendar
426 CURSOR c_get_calendar(n_book_code fa_books.book_type_code%TYPE)
427 IS
428 SELECT deprn_calendar
429 FROM fa_book_controls
430 WHERE book_type_code = n_book_code;
431
432 l_num_per_period fa_calendar_types.number_per_fiscal_year%TYPE;
433 l_mod_value NUMBER;
434 l_fiscal_yr fa_fiscal_year.fiscal_year%TYPE;
435 l_next_period_num fa_calendar_periods.period_num%TYPE;
436 l_calendar fa_calendar_types.calendar_type%TYPE;
437
438
439 BEGIN
440
441 OPEN c_get_calendar(p_book_code);
442 FETCH c_get_calendar INTO l_calendar;
443 CLOSE c_get_calendar;
444
445 OPEN c_get_num_per_period(l_calendar);
446 FETCH c_get_num_per_period INTO l_num_per_period;
447 CLOSE c_get_num_per_period;
448
449 l_mod_value := MOD(p_period_rec.period_num,l_num_per_period);
450
451 IF l_mod_value = 0 THEN
452 l_fiscal_yr:=p_period_rec.fiscal_year+1;
453 ELSE
454 l_fiscal_yr:=p_period_rec.fiscal_year;
455 END IF;
456
457 l_next_period_num:=l_mod_value+1;
458
459 p_next_period_ctr:= (l_fiscal_yr*l_num_per_period)+l_next_period_num;
460 EXCEPTION
461 WHEN NO_DATA_FOUND THEN
462 igi_iac_debug_pkg.debug_other_string(g_error_level,g_path||'Get_Next_Period_Ctr','Get Next PC: No Data Found');
463 WHEN OTHERS THEN
464 igi_iac_debug_pkg.debug_other_string(g_unexp_level,g_path||'Get_Next_Period_Ctr','Exception raised: '||sqlerrm);
465 END Get_Next_Period_Ctr;
466
467 -- ===================================================================
468 -- FUNCTION Chk_Asset_Life: Find if the life of asset is completed in
469 -- the given period
470 -- ===================================================================
471 FUNCTION Chk_Asset_Life( p_book_code fa_books.book_type_code%TYPE,
472 p_period_counter fa_deprn_periods.period_counter%TYPE,
473 p_asset_id fa_books.asset_id%TYPE,
474 l_last_period_counter OUT NOCOPY fa_deprn_periods.period_counter%TYPE
475 )
476 RETURN BOOLEAN
477 IS
478
479 CURSOR c_get_asset_det(n_book_code fa_books.book_type_code%TYPE,
480 n_asset_id fa_books.asset_id%TYPE
481 )
482 IS
483 SELECT date_placed_in_service,
484 life_in_months
485 FROM fa_books
486 WHERE book_type_code = n_book_code
487 AND date_ineffective is NULL -- Bug 5850597
488 AND asset_id = n_asset_id;
489
490 CURSOR c_get_periods_in_year(n_book_code fa_books.book_type_code%TYPE)
491 IS
492 SELECT number_per_fiscal_year
493 FROM fa_calendar_types
494 WHERE calendar_type = (SELECT deprn_calendar
495 FROM fa_book_controls
496 WHERE book_type_code = n_book_code);
497
498
499 l_prd_rec_frm_ctr igi_iac_types.prd_rec;
500 l_prd_rec_frm_date igi_iac_types.prd_rec;
501 l_end_date DATE;
502 l_asset_rec c_get_asset_det%ROWTYPE;
503 l_ret_flag BOOLEAN;
504 l_mess varchar2(255);
505
506 l_periods_in_year fa_calendar_types.number_per_fiscal_year%TYPE;
507 l_dpis_prd_rec igi_iac_types.prd_rec;
508 l_total_periods NUMBER;
509
510 -- l_last_period_counter NUMBER;
511
512 BEGIN
513
514 OPEN c_get_asset_det(p_book_code,
515 p_asset_id
516 );
517 FETCH c_get_asset_det INTO l_asset_rec;
518 CLOSE c_get_asset_det;
519
520 OPEN c_get_periods_in_year(p_book_code);
521 FETCH c_get_periods_in_year INTO l_periods_in_year;
522 CLOSE c_get_periods_in_year;
523
524 -- Get the period info for the dpis
525 l_ret_flag := igi_iac_common_utils.Get_period_info_for_date(p_book_code,
526 l_asset_rec.date_placed_in_service,
527 l_dpis_prd_rec
528 );
529 l_total_periods := ceil((l_asset_rec.life_in_months*l_periods_in_year)/12);
530 l_last_period_counter := (l_dpis_prd_rec.period_counter + l_total_periods - 1);
531
532 -- Bug 3139173, last period in asset life is being missed out
533 -- IF (l_last_period_counter = p_period_counter) THEN
534 IF (l_last_period_counter < p_period_counter) THEN
535 RETURN FALSE; /* The life of asset is over */
536 ELSE
537 RETURN TRUE; /* Life not over at this period */
538 END IF;
539
540 EXCEPTION
541 WHEN OTHERS THEN
542 igi_iac_debug_pkg.debug_other_string(g_unexp_level,g_path||'Chk_Asset_Life','Check Asset Life'||sqlerrm);
543 raise_Application_error(-20001,SQLERRM);
544 rollback;
545 END Chk_Asset_Life;
546
547
548 -- ===================================================================
549 -- PROCEDURE Do_Proj_CAlc: Main procedure to calculate the projections
550 -- for the projection id
551 -- ===================================================================
552 -- This procedure has been rewritten for the Projections enhancement project
553 PROCEDURE Do_Proj_Calc(
554 errbuf OUT NOCOPY VARCHAR2,
555 retcode OUT NOCOPY VARCHAR2,
556 p_projection_id IN igi_iac_projections.projection_id%TYPE,
557 p_rx_attribute_set IN fa_rx_attrsets_b.attribute_set%TYPE,
558 p_rx_output_format IN fnd_lookups.lookup_code%TYPE
559 ) IS
560
561 -- cursors
562 -- Get all the projection details
563 CURSOR c_get_proj(n_projection_id NUMBER)
564 IS
565 SELECT book_type_code,
566 start_period_counter,
567 end_period,
568 category_id,
569 revaluation_period,
570 status
571 FROM igi_iac_projections
572 WHERE projection_id = n_projection_id;
573
574
575 -- Get all the assets that should be considered for projection
576 -- this query should include non depreciating assets as well
577 -- and excludes all fully retired assets for a category
578 CURSOR c_get_assets_one_cat(n_book_code fa_books.book_type_code%TYPE,
579 n_category_id igi_iac_projections.category_id%TYPE DEFAULT NULL,
580 n_period_counter fa_deprn_summary.period_counter%TYPE)
581 IS
582 SELECT DISTINCT fh.asset_id asset_id
583 FROM fa_books fb,
584 fa_additions fh,
585 igi_iac_category_books fcb
586 WHERE fb.book_type_code = n_book_code
587 AND fb.book_type_code = fcb.book_type_code
588 AND fcb.category_id = fh.asset_category_id
589 AND fh.asset_category_id = n_category_id
590 AND fb.asset_id=fh.asset_id
591 AND fb.period_counter_fully_retired IS NULL
592 AND fh.asset_type <> 'CIP'
593 -- AND fb.asset_id IN (SELECT asset_id
594 -- FROM fa_deprn_summary
595 -- WHERE book_type_code = n_book_code
596 -- AND period_counter = n_period_counter - 1
597 -- AND deprn_source_code <> 'BOOKS')
598 ORDER BY fh.asset_id;
599
600 -- Get all the assets that should be considered for projection
601 -- this query should include non depreciating assets as well
602 -- and excludes all fully retired assets for all categries
603 CURSOR c_get_assets_all_cat(n_book_code fa_books.book_type_code%TYPE,
604 n_period_counter fa_deprn_summary.period_counter%TYPE)
605 IS
606 SELECT DISTINCT fh.asset_id asset_id
607 FROM fa_books fb,
608 fa_additions fh,
609 igi_iac_category_books fcb
610 WHERE fb.book_type_code = n_book_code
611 AND fb.book_type_code = fcb.book_type_code
612 AND fcb.category_id = fh.asset_category_id
613 AND fb.asset_id=fh.asset_id
614 AND fb.period_counter_fully_retired IS NULL
615 AND fh.asset_type <> 'CIP'
616 -- AND fb.asset_id IN (SELECT asset_id
617 -- FROM fa_deprn_summary
618 -- WHERE book_type_code = n_book_code
619 -- AND period_counter = n_period_counter - 1
620 -- AND deprn_source_code <> 'BOOKS')
621 ORDER BY fh.asset_id;
622
623 -- Get all the latest records for the given asset of the given book for which the deprn had
624 -- been run
625 -- get the information from the distribution level
626 CURSOR c_get_asset_all(n_book_type_code fa_books.book_type_code%TYPE,
627 n_asset_id fa_books.asset_id%TYPE )
628 IS
629 SELECT ad.asset_id,
630 dh.code_combination_id,
631 ah.category_id,
632 dd.period_counter,
633 sum(nvl(id.adjustment_cost,0) + nvl(dd.cost,0)) adjusted_cost,
634 sum(nvl(id.Deprn_Period+dd.deprn_amount-dd.deprn_adjustment_amount, 0)) deprn_period,
635 sum(nvl(id.Deprn_YTD+ifd.deprn_ytd, 0)) deprn_ytd,
636 'IAC' source_type
637 FROM fa_additions ad ,
638 fa_Books bk ,
639 fa_distribution_history dh,
640 fa_deprn_Detail dd ,
641 igi_iac_det_balances id ,
642 igi_iac_fa_deprn ifd,
643 gl_code_combinations cc,
644 fa_categories cf,
645 fa_asset_history ah
646 WHERE ad.asset_id = dh.asset_id
647 AND cf.category_id = ah.category_id
648 AND bk.book_Type_code = n_book_type_code
649 AND ad.asset_id = n_asset_id
650 AND dh.book_type_Code = bk.book_type_code
651 AND dh.book_type_code = dd.book_type_code
652 AND dh.asset_id = dd.asset_id
653 AND dh.distribution_id = dd.distribution_id
654 AND dh.asset_id = ah.asset_id
655 AND bk.depreciate_flag <> 'NO'
656 AND nvl(dh.date_ineffective,sysdate+1) > ah.date_effective
657 AND nvl(dh.date_ineffective,sysdate+1) <= nvl(ah.date_ineffective,sysdate+1)
658 AND dd.period_counter = (SELECT period_counter - 1
659 FROM fa_deprn_periods
660 WHERE book_type_code = n_book_type_code
661 AND period_close_date IS NULL)
662 AND bk.date_ineffective IS NULL
663 AND dh.distribution_id = id.distribution_id
664 AND dh.code_Combination_id = cc.code_combination_id
665 AND id.adjustment_id = ifd.adjustment_id
666 AND id.distribution_id = ifd.distribution_id
667 AND id.period_counter = ifd.period_counter
668 AND id.adjustment_id = ( SELECT max(adjustment_id)
669 FROM igi_iac_transaction_headers it
670 WHERE it.asset_id = bk.asset_id
671 AND it.book_type_code = bk.book_type_code
672 AND it.period_counter = dd.period_counter
673 AND it.adjustment_status not in( 'PREVIEW', 'OBSOLETE'))
674 GROUP BY ad.asset_id,
675 dh.code_combination_id,
676 ah.category_id,
677 dd.period_counter
678 UNION
679 SELECT ad.asset_id,
680 dh.code_combination_id,
681 ah.category_id,
682 dd.period_counter,
683 sum(nvl(dd.cost,0)) adjusted_cost,
684 sum(nvl(dd.deprn_amount,0)-nvl(dd.deprn_adjustment_amount,0)) deprn_period,
685 sum(nvl(dd.ytd_deprn,0)) deprn_ytd,
686 'FA' source_type
687 FROM fa_additions ad,
688 fa_Books bk,
689 fa_distribution_history dh,
690 fa_deprn_Detail dd,
691 gl_code_combinations cc,
692 fa_categories cf,
693 fa_asset_history ah
694 WHERE ad.asset_id = bk.asset_id
695 AND ad.asset_type <> 'CIP'
696 AND cf.category_id = ah.category_id
697 AND bk.transaction_header_id_out is NULL
698 AND bk.book_type_code = n_book_type_code
699 AND dd.asset_id = n_asset_id
700 AND dd.asset_id = bk.asset_id
701 AND dd.book_type_code = bk.book_type_code
702 AND dh.distribution_id = dd.distribution_id
703 AND dh.transaction_header_id_out is NULL
704 AND dh.code_combination_id = cc.code_combination_id
705 AND dh.asset_id = ah.asset_id
706 AND nvl(dh.date_ineffective,sysdate+1) > ah.date_effective
707 AND nvl(dh.date_ineffective,sysdate+1) <= nvl(ah.date_ineffective,sysdate+1)
708 AND dd.period_counter = (SELECT period_counter -1
709 FROM fa_deprn_periods
710 WHERE book_type_code = n_book_type_code
711 AND period_close_date IS NULL)
712 AND bk.asset_id NOT IN
713 (SELECT asset_id
714 FROM igi_iac_asset_balances
715 WHERE book_type_code = bk.book_type_code
716 AND asset_id = bk.asset_id)
717 AND bk.depreciate_flag <> 'NO'
718 GROUP BY ad.asset_id,
719 dh.code_combination_id,
720 ah.category_id,
721 dd.period_counter
722 UNION
723 SELECT ad.asset_id,
724 dh.code_combination_id,
725 ah.category_id,
726 dd.period_counter,
727 sum(nvl(dd.cost,0)) adjusted_cost,
728 0 deprn_period,
729 0 deprn_ytd,
730 'NONDEPFA' source_type
731 FROM fa_additions ad,
732 fa_Books bk,
733 fa_distribution_history dh,
734 fa_deprn_Detail dd,
735 gl_code_combinations cc,
736 fa_categories cf,
737 fa_asset_history ah
738 WHERE ad.asset_id = bk.asset_id
739 AND cf.category_id = ah.category_id
740 AND ad.asset_type <> 'CIP'
741 AND bk.transaction_header_id_out is NULL
742 AND bk.book_type_code = n_book_type_code
743 AND dd.asset_id = n_asset_id
744 AND dd.asset_id = bk.asset_id
745 AND dd.book_type_code = bk.book_type_code
746 AND dh.distribution_id = dd.distribution_id
747 AND dh.transaction_header_id_out is NULL
748 AND dh.code_combination_id = cc.code_combination_id
749 AND dh.asset_id = ah.asset_id
750 AND nvl(dh.date_ineffective,sysdate+1) > ah.date_effective
751 AND nvl(dh.date_ineffective,sysdate+1) <= nvl(ah.date_ineffective,sysdate+1)
752 AND dd.period_counter = (SELECT max(period_counter)
753 FROM fa_deprn_detail
754 WHERE book_type_code = n_book_type_code
755 AND asset_id = n_asset_id)
756 AND bk.depreciate_flag = 'NO'
757 GROUP BY ad.asset_id,
758 dh.code_combination_id,
759 ah.category_id,
760 dd.period_counter
761 UNION
762 SELECT ad.asset_id,
763 dh.code_combination_id,
764 ah.category_id,
765 dd.period_counter,
766 sum(nvl(dd.cost,0)) adjusted_cost,
767 sum(nvl(dd.deprn_amount,0)-nvl(dd.deprn_adjustment_amount,0)) deprn_period,
768 sum(nvl(dd.ytd_deprn,0)) deprn_ytd,
769 'FULLRSVDFA' source_type
770 FROM fa_additions ad,
771 fa_Books bk,
772 fa_distribution_history dh,
773 fa_deprn_Detail dd,
774 gl_code_combinations cc,
775 fa_categories cf,
776 fa_asset_history ah
777 WHERE ad.asset_id = bk.asset_id
778 AND ad.asset_type <> 'CIP'
779 AND cf.category_id = ah.category_id
780 AND bk.transaction_header_id_out is NULL
781 AND bk.book_type_code = n_book_type_code
782 AND dd.asset_id = n_asset_id
783 AND dd.asset_id = bk.asset_id
784 AND dd.book_type_code = bk.book_type_code
785 AND dh.distribution_id = dd.distribution_id
786 AND dh.transaction_header_id_out is NULL
787 AND dh.code_combination_id = cc.code_combination_id
788 AND dh.asset_id = ah.asset_id
789 AND nvl(dh.date_ineffective,sysdate+1) > ah.date_effective
790 AND nvl(dh.date_ineffective,sysdate+1) <= nvl(ah.date_ineffective,sysdate+1)
791 AND dd.period_counter = (SELECT period_counter_fully_reserved
792 FROM fa_books
793 WHERE book_type_code = n_book_type_code
794 AND asset_id = n_asset_id
795 AND date_ineffective IS NULL
796 AND transaction_header_id_out IS NULL)
797 GROUP BY ad.asset_id,
798 dh.code_combination_id,
799 ah.category_id,
800 dd.period_counter;
801
802 -- cursor to get non depreciating IAC data
803 CURSOR c_iac_non_deprn(n_asset_id fa_books.asset_id%TYPE,
804 n_book_code fa_books.book_type_code%TYPE)
805 IS
806 SELECT NVL(SUM(NVL(dd.adjustment_cost,0)),0)
807 FROM igi_iac_det_balances dd,
808 fa_books fb
809 WHERE dd.book_type_code = fb.book_type_code
810 AND dd.asset_id = fb.asset_id
811 AND dd.book_type_code = n_book_code
812 AND dd.asset_id = n_asset_id
813 AND fb.transaction_header_id_out IS NULL
814 AND fb.depreciate_flag = 'NO'
815 AND dd.adjustment_id = (SELECT max(ith.adjustment_id)
816 FROM igi_iac_transaction_headers ith
817 WHERE ith.book_type_code = n_book_code
818 AND ith.asset_id = n_Asset_id
819 AND ith.adjustment_status NOT IN( 'PREVIEW', 'OBSOLETE'));
820
821 -- cursor to get fully reserved IAC data
822 /* CURSOR c_iac_full_rsvd(n_asset_id fa_books.asset_id%TYPE,
823 n_book_code fa_books.book_type_code%TYPE)
824 IS
825 SELECT NVL(SUM(NVL(dd.adjustment_cost,0)),0) adjustment_cost,
826 NVL(sum(nvl(dd.Deprn_Period, 0)),0) deprn_period,
827 NVL(sum(nvl(dd.Deprn_YTD+ifd.deprn_ytd, 0)),0) deprn_ytd
828 FROM igi_iac_det_balances dd,
829 igi_iac_fa_deprn ifd,
830 fa_books fb
831 WHERE dd.book_type_code = fb.book_type_code
832 AND dd.asset_id = fb.asset_id
833 AND dd.distribution_id = ifd.distribution_id
834 AND dd.adjustment_id = ifd.adjustment_id
835 AND dd.asset_id = ifd.asset_id
836 AND dd.book_type_code = n_book_code
837 AND dd.asset_id = n_asset_id
838 AND fb.transaction_header_id_out IS NULL
839 AND fb.period_counter_fully_reserved IS NOT NULL
840 AND dd.adjustment_id = (SELECT max(ith.adjustment_id)
841 FROM igi_iac_transaction_headers ith
842 WHERE ith.book_type_code = n_book_code
843 AND ith.asset_id = n_Asset_id
844 AND ith.adjustment_status NOT IN( 'PREVIEW', 'OBSOLETE'));
845 */
846 -- check to see if the asset is fully reserved
847 CURSOR c_chk_fully_rsvd (n_asset_id fa_books.asset_id%TYPE,
848 n_book_code fa_books.book_type_code%TYPE)
849 IS
850 SELECT period_counter_fully_reserved
851 FROM fa_books where book_type_code = n_book_code
852 AND asset_id = n_asset_id;
853
854 -- cursor to retrieve the DPIS date for an asset
855 CURSOR c_get_asset_dpis(n_asset_id fa_books.asset_id%TYPE,
856 n_book_type_code fa_books.book_type_code%TYPE)
857 IS
858 SELECT date_placed_in_service
859 FROM fa_books
860 WHERE asset_id = n_asset_id
861 AND book_type_code = n_book_type_code
862 AND date_ineffective IS NULL;
863
864 -- variables
865 -- variables for projection detail records
866 TYPE asset_id_type IS TABLE OF fa_books.asset_id%TYPE INDEX BY BINARY_INTEGER;
867
868 TYPE asset_id_type1 IS TABLE OF igi_iac_proj_details.asset_id%TYPE INDEX BY BINARY_INTEGER;
869 TYPE proj_id_type IS TABLE OF igi_iac_proj_details.projection_id%TYPE INDEX BY BINARY_INTEGER;
870 TYPE period_counter_type IS TABLE OF igi_iac_proj_details.projection_id%TYPE INDEX BY BINARY_INTEGER;
871 TYPE category_id_type IS TABLE OF igi_iac_proj_details.category_id%TYPE INDEX BY BINARY_INTEGER;
872 TYPE fiscal_year_type IS TABLE OF igi_iac_proj_details.fiscal_year%TYPE INDEX BY BINARY_INTEGER;
873 TYPE company_type IS TABLE OF igi_iac_proj_details.company%TYPE INDEX BY BINARY_INTEGER;
874 TYPE cost_center_type IS TABLE OF igi_iac_proj_details.cost_center%TYPE INDEX BY BINARY_INTEGER;
875 TYPE reval_cost_type IS TABLE OF igi_iac_proj_details.latest_reval_cost%TYPE INDEX BY BINARY_INTEGER;
876 TYPE deprn_period_type IS TABLE OF igi_iac_proj_details.deprn_period%TYPE INDEX BY BINARY_INTEGER;
877 TYPE deprn_ytd_type IS TABLE OF igi_iac_proj_details.deprn_ytd%TYPE INDEX BY BINARY_INTEGER;
878 TYPE asset_exception_type IS TABLE OF igi_iac_proj_details.asset_exception%TYPE INDEX BY BINARY_INTEGER;
879 TYPE reccount_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
880
881 TYPE proj_details IS RECORD (
882 record_counter reccount_type,
883 projection_id proj_id_type,
884 period_counter period_counter_type,
885 category_id category_id_type,
886 fiscal_year fiscal_year_type,
887 company company_type,
888 cost_center cost_center_type,
889 asset_id asset_id_type1,
890 latest_reval_cost reval_cost_type,
891 deprn_period deprn_period_type,
892 deprn_ytd deprn_ytd_type,
893 asset_exception asset_exception_type);
894
895
896 l_proj_rec proj_details;
897 l_assets_list asset_id_type;
898 l_asset_count NUMBER;
899 l_asset_id fa_books.asset_id%TYPE;
900
901 l_book_type_code igi_iac_projections.book_type_code%TYPE;
902 l_start_period_counter igi_iac_projections.start_period_counter%TYPE;
903 l_reval_period_ctr igi_iac_projections.start_period_counter%TYPE;
904
905 l_end_period_counter igi_iac_projections.end_period%TYPE;
906 l_category_id igi_iac_projections.category_id%TYPE;
907 l_reval_period_num igi_iac_projections.revaluation_period%TYPE;
908 l_status igi_iac_projections.status%TYPE;
909
910 l_sob_id fa_book_controls.set_of_books_id%TYPE;
911 l_get_asset_bal_rec c_get_asset_all%ROWTYPE;
912 l_asset_dpis_date fa_books.date_placed_in_service%TYPE;
913
914 l_next_period_ctr igi_iac_projections.start_period_counter%TYPE := 0;
915 l_period_info_rec igi_iac_types.prd_rec;
916 l_dpis_info_rec igi_iac_types.prd_rec;
917 l_ret_flag BOOLEAN;
918 l_fully_rsvd fa_books.period_counter_fully_reserved%TYPE;
919 l_company_seg VARCHAR2(30);
920 l_cc_seg VARCHAR2(30);
921
922 l_hist_cost fa_books.cost%TYPE;
923 l_iac_cost igi_iac_asset_balances.adjusted_cost%TYPE;
924 l_hist_deprn_period igi_iac_proj_details.deprn_period%TYPE;
925 l_iac_deprn_period igi_iac_proj_details.deprn_period%TYPE;
926 l_hist_deprn_ytd igi_iac_proj_details.deprn_ytd%TYPE;
927 l_iac_deprn_ytd igi_iac_proj_details.deprn_ytd%TYPE;
928
929 l_curr_cost fa_books.cost%TYPE;
930 l_prev_cost fa_books.cost%TYPE;
931 l_curr_deprn_period igi_iac_proj_details.deprn_period%TYPE;
932 l_prev_deprn_period igi_iac_proj_details.deprn_period%TYPE;
933 l_curr_deprn_period_catchup igi_iac_proj_details.deprn_period%TYPE;
934 l_curr_deprn_ytd igi_iac_proj_details.deprn_ytd%TYPE;
935 l_prev_deprn_ytd igi_iac_proj_details.deprn_ytd%TYPE;
936 l_prior_prd_deprn_ytd igi_iac_proj_details.deprn_ytd%TYPE;
937
938
939 l_prior_prd_index_val igi_iac_cal_idx_values.current_price_index_value%TYPE;
940 l_curr_price_index_val igi_iac_cal_idx_values.current_price_index_value%TYPE;
941 l_prior_cumul_rate igi_iac_asset_balances.cumulative_reval_factor%TYPE;
942 l_curr_reval_rate igi_iac_asset_balances.current_reval_factor%TYPE;
943 l_curr_reval_rate igi_iac_asset_balances.current_reval_factor%TYPE;
944 l_cumul_rate igi_iac_asset_balances.cumulative_reval_factor%TYPE;
945
946 l_count NUMBER := 1;
947 l_login_id NUMBER := fnd_profile.value('LOGIN_ID');
948 l_user_id NUMBER := fnd_profile.value('USER_ID');
949
950 l_prd_rec igi_iac_types.prd_rec;
951 l_start_period_name VARCHAR2(30);
952 l_end_period_name VARCHAR2(30);
953 l_concat_cat VARCHAR2(500);
954 l_cat_segs fa_rx_shared_pkg.Seg_Array;
955 l_cat_struct fa_system_controls.category_flex_structure%TYPE;
956
957
958 l_last_prd_counter fa_deprn_periods.period_counter%TYPE;
959 l_rec_count NUMBER;
960
961 -- exceptions
962 NO_ASSETS_FOUND EXCEPTION;
963 NO_INDEX_FOUND EXCEPTION;
964 NO_PROJ_MAIN_DATA EXCEPTION;
965 NO_ASSETS_TO_PROJECT EXCEPTION;
966
967 BEGIN
968
969 -- Get all the projection details
970 OPEN c_get_proj(p_projection_id);
971 FETCH c_get_proj INTO
972 l_book_type_code,
973 l_start_period_counter,
974 l_end_period_counter,
975 l_category_id,
976 l_reval_period_num,
977 l_status;
978
979 IF c_get_proj%NOTFOUND THEN
980 RAISE NO_PROJ_MAIN_DATA;
981 END IF;
982 CLOSE c_get_proj;
983
984 -- get period name for start period counter
985 l_ret_flag := igi_iac_common_utils.get_period_info_for_counter( l_book_type_code,
986 l_start_period_counter,
987 l_prd_rec );
988 l_start_period_name := l_prd_rec.period_name;
989
990 -- get period name for end period counter
991 l_ret_flag := igi_iac_common_utils.get_period_info_for_counter( l_book_type_code,
992 l_end_period_counter,
993 l_prd_rec );
994
995 l_end_period_name := l_prd_rec.period_name;
996
997 -- get the category and asset key flex structures
998 SELECT category_flex_structure
999 INTO l_cat_struct
1000 FROM fa_system_controls;
1001
1002 -- get category
1003 IF (l_category_id IS NOT NULL) THEN
1004 -- get the concatenated category name
1005 fa_rx_shared_pkg.concat_category (
1006 struct_id => l_cat_struct,
1007 ccid => l_category_id,
1008 concat_string => l_concat_cat,
1009 segarray => l_cat_segs);
1010 ELSE
1011
1012 l_concat_cat := 'All';
1013 END IF;
1014
1015 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'Do_Proj_Calc','Book type code: '||l_book_type_code);
1016 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'Do_Proj_Calc','CAtegory Id: '||l_category_id);
1017 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'Do_Proj_Calc','Start period name: '||l_start_period_name);
1018 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'Do_Proj_Calc','End Period name: '||l_end_period_name);
1019 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'Do_Proj_Calc','Revaluation Period number: '||l_reval_period_num);
1020
1021 -- get the set of books id for the book
1022 SELECT set_of_books_id
1023 INTO l_sob_id
1024 FROM fa_book_controls
1025 WHERE book_type_code =l_book_type_code;
1026
1027 -- Retrieve all the assets that qualify for the projection run
1028 IF (l_category_id IS NOT NULL) THEN
1029 -- if it is for a category
1030 OPEN c_get_assets_one_cat(l_book_type_code, l_category_id, l_start_period_counter);
1031 FETCH c_get_assets_one_cat BULK COLLECT INTO l_assets_list;
1032 CLOSE c_get_assets_one_cat;
1033 ELSE
1034 -- if it is for all cats
1035 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'Do_Proj_Calc' ,'Getting assets for all IAC categories');
1036 OPEN c_get_assets_all_cat(l_book_type_code, l_start_period_counter);
1037 FETCH c_get_assets_all_cat BULK COLLECT INTO l_assets_list;
1038 CLOSE c_get_assets_all_cat;
1039 END IF;
1040
1041 l_rec_count := l_assets_list.COUNT;
1042
1043 -- calculate projections for each of the assets queried
1044 FOR i IN 1 .. l_assets_list.COUNT
1045 LOOP
1046 -- get the asset position for the latest closed or depreciated period
1047 l_asset_id := l_assets_list(i);
1048
1049 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'Do_Proj_Calc' ,'Processing asset id: '||l_asset_id);
1050
1051 -- get the dpis for the asset
1052 OPEN c_get_asset_dpis(l_asset_id,
1053 l_book_type_code);
1054 FETCH c_get_asset_dpis INTO l_asset_dpis_date;
1055 IF c_get_asset_dpis%NOTFOUND THEN
1056 CLOSE c_get_asset_dpis;
1057 END IF;
1058 CLOSE c_get_asset_dpis;
1059
1060 -- get the period counter associated with the dpis
1061 l_ret_flag := igi_iac_common_utils.get_period_info_for_date(l_book_type_code,
1062 l_asset_dpis_date,
1063 l_dpis_info_rec);
1064
1065 -- check if the asset is fully reserved
1066 OPEN c_chk_fully_rsvd (l_asset_id,
1067 l_book_type_code);
1068 FETCH c_chk_fully_rsvd INTO l_fully_rsvd;
1069 IF c_chk_fully_rsvd%NOTFOUND THEN
1070 CLOSE c_chk_fully_rsvd;
1071 END IF;
1072 CLOSE c_chk_fully_rsvd;
1073
1074 -- bug 3188025, start 1
1075 <<L_asset_loop>>
1076 -- bug 3188025, end 1
1077 -- For each asset distribution ccid record loop
1078 FOR l_get_asset_bal_rec IN c_get_asset_all(l_book_type_code,l_asset_id)
1079 LOOP
1080
1081 -- bug 3188025, start 2
1082 -- only inactive dists will have cost and deprn period 0 at the same time
1083 -- so filter these out now
1084 IF (l_get_asset_bal_rec.adjusted_cost = 0 AND l_get_asset_bal_rec.deprn_period = 0) THEN
1085 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'Do_Proj_Calc' ,'Exiting loop L_asset_loop');
1086
1087 EXIT L_asset_loop;
1088 END IF;
1089
1090 -- bug 3188025, end 2
1091 -- To get company name in company
1092 l_ret_flag := igi_iac_common_utils.get_account_segment_value(l_sob_id,
1093 l_get_asset_bal_rec.code_combination_id,
1094 'GL_BALANCING',
1095 l_company_seg);
1096
1097 -- To get cost center in l_cost_center
1098 l_ret_flag := igi_iac_common_utils.get_account_segment_value(l_sob_id,
1099 l_get_asset_bal_rec.code_combination_id,
1100 'FA_COST_CTR',
1101 l_cc_seg);
1102
1103
1104 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'Do_Proj_Calc' ,'Company: '||l_company_seg||' Cost Center: '||l_cc_seg);
1105
1106 -- IF (l_fully_rsvd IS NOT NULL) THEN
1107 IF (l_get_asset_bal_rec.source_type = 'FULLRSVDFA') THEN
1108 -- asset is fully reserved
1109 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'Do_Proj_Calc' ,'Asset is fully reserved');
1110 l_count := l_count + 1;
1111
1112 -- set the asset details for the projection record
1113 l_proj_rec.record_counter(l_count) := l_count;
1114 l_proj_rec.asset_id(l_count) := l_asset_id;
1115 l_proj_rec.projection_id(l_count) := p_projection_id;
1116 l_proj_rec.company(l_count) := l_company_seg;
1117 l_proj_rec.cost_center(l_count) := l_cc_seg;
1118 l_proj_rec.category_id(l_count) := l_get_asset_bal_rec.category_id;
1119
1120 -- get the period information for the start period counter
1121 l_ret_flag := get_period_info_for_counter(l_book_type_code,
1122 l_start_period_counter,
1123 l_period_info_rec);
1124
1125 -- set period related information for the projection record
1126 l_proj_rec.fiscal_year(l_count) := l_period_info_rec.fiscal_year;
1127 l_proj_rec.period_counter(l_count) := l_start_period_counter;
1128
1129 -- set the exception comment and the projection amounts to NULL
1130 l_proj_rec.asset_exception(l_count) := 'FULL_RSVD';
1131 l_proj_rec.latest_reval_cost(l_count) := NULL;
1132 l_proj_rec.deprn_period(l_count) := NULL;
1133 l_proj_rec.deprn_ytd(l_count) := NULL;
1134
1135 ELSE
1136 -- asset is not fully reserved
1137 -- do the projections calculations
1138
1139 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'Do_Proj_Calc' ,'Asset is not a fully reserved asset, calculating projections');
1140 -- initialise the period counter
1141 l_next_period_ctr := l_start_period_counter;
1142
1143 l_prev_cost := l_get_asset_bal_rec.adjusted_cost;
1144 l_prev_deprn_period := l_get_asset_bal_rec.deprn_period;
1145 l_prev_deprn_ytd := l_get_asset_bal_rec.deprn_ytd;
1146
1147 -- Get the previous revaluation period or the DPIS period if it does not exist
1148 -- Get the price index details for this period into l_prior_prd_index_val
1149 l_ret_flag := get_reval_prd_dpis_ctr(l_book_type_code,
1150 l_asset_id,
1151 l_reval_period_ctr);
1152
1153 l_ret_flag:= get_price_index_val(l_book_type_code,
1154 l_get_asset_bal_rec.category_id,
1155 l_reval_period_ctr,
1156 l_prior_prd_index_val);
1157
1158 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'Do_Proj_Calc' ,'The previous reval period is: ' || l_reval_period_ctr);
1159
1160 -- calculate projections for the projection period while asset still has life
1161 -- and is not fully reserved
1162
1163 WHILE ((l_next_period_ctr <= l_end_period_counter) AND
1164 chk_asset_life(l_book_type_code, l_next_period_ctr,l_asset_id, l_last_prd_counter))
1165 -- AND l_prev_cost >= 0)
1166 LOOP
1167 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'Do_Proj_Calc' ,'Next period counter: '||l_next_period_ctr);
1168
1169 -- increase the counter
1170 l_count := l_count + 1;
1171
1172 -- set the asset details for the projection record
1173 l_proj_rec.record_counter(l_count) := l_count;
1174 l_proj_rec.asset_id(l_count) := l_asset_id;
1175 l_proj_rec.projection_id(l_count) := p_projection_id;
1176 l_proj_rec.company(l_count) := l_company_seg;
1177 l_proj_rec.cost_center(l_count) := l_cc_seg;
1178 l_proj_rec.category_id(l_count) := l_get_asset_bal_rec.category_id;
1179 -- get the period information for the next period counter
1180 l_ret_flag := get_period_info_for_counter(l_book_type_code,
1181 l_next_period_ctr,
1182 l_period_info_rec);
1183
1184 -- set period related information for the projection record
1185 l_proj_rec.fiscal_year(l_count) := l_period_info_rec.fiscal_year;
1186 l_proj_rec.period_counter(l_count) := l_next_period_ctr;
1187
1188 -- get the price_index_value for the current period l_next_period_ctr
1189 l_ret_flag := get_price_index_val(l_book_type_code,
1190 l_get_asset_bal_rec.category_id,
1191 l_next_period_ctr,
1192 l_curr_price_index_val);
1193 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'Do_Proj_Calc' ,'Price Index Value: '||l_curr_price_index_val);
1194
1195 -- Bug 3139146, if PI is 9999.99 then do not calculate projections
1196 -- instead trap with exceptions code INVALID_PI
1197 IF (l_curr_price_index_val = 9999.99 OR l_prior_prd_index_val = 9999.99) THEN
1198 -- set cost, deprn_period and ytd_deprn to 0
1199 -- set exception to INVALID_PI
1200 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'Do_Proj_Calc' ,'Invalid Price Index');
1201 l_proj_rec.latest_reval_cost(l_count) := 0;
1202 l_proj_rec.deprn_period(l_count) := 0;
1203 l_proj_rec.deprn_ytd(l_count) := 0;
1204 l_proj_rec.asset_exception(l_count) := 'INVALID_PI';
1205 EXIT;
1206 ELSE
1207 -- valid PI
1208 -- check if this is a non depreciating asset
1209 -- if it has delta IAC amount then add it to the FA cost for the first run
1210 IF (l_get_Asset_bal_rec.source_type = 'NONDEPFA' AND
1211 l_next_period_ctr = l_start_period_counter) THEN
1212 OPEN c_iac_non_deprn(l_asset_id, l_book_type_code);
1213 FETCH c_iac_non_deprn INTO l_iac_cost;
1214 CLOSE c_iac_non_deprn;
1215 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'Do_Proj_Calc' ,'Non Depreciating IAC cost: '||l_iac_cost);
1216 l_prev_cost := l_prev_cost + l_iac_cost;
1217 END IF;
1218
1219 -- check if the period is a revaluation period or just a normal period
1220 -- to calculate the cost or the periodic depreciation amount
1221 IF (l_period_info_rec.period_num = l_reval_period_num) THEN
1222 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'Do_Proj_Calc' ,'This is revaluation period number: '||l_reval_period_num);
1223 -- this is the revaluation catchup period
1224 -- calculate the current cost
1225
1226 l_curr_cost := l_prev_cost*(l_curr_price_index_val/l_prior_prd_index_val);
1227
1228 -- calculate the current depreciation period if it a depreciating asset
1229 -- IF NOT Non_Depreciating_Asset(l_asset_id, l_book_type_code) THEN
1230 IF (l_get_asset_bal_rec.source_type <> 'NONDEPFA') THEN
1231 l_curr_deprn_period := l_prev_deprn_period*(l_curr_price_index_val/l_prior_prd_index_val);
1232 /* 01-Aug-2003, commenting out as catchup should be calculated for all instances
1233 IF (l_get_asset_bal_rec.source_type = 'IAC') THEN
1234 l_curr_deprn_period_catchup :=
1235 l_prev_deprn_ytd*((l_curr_price_index_val/l_prior_prd_index_val) - 1);
1236 ELSE
1237 l_curr_deprn_period_catchup := 0;
1238 END IF; */
1239
1240 -- calculate the catchup amount
1241 l_curr_deprn_period_catchup :=
1242 l_prev_deprn_ytd*((l_curr_price_index_val/l_prior_prd_index_val) - 1);
1243
1244 -- calculate the YTD depreciation amount
1245 IF (l_period_info_rec.period_num = 1) THEN
1246 l_curr_deprn_ytd := l_curr_deprn_period + l_curr_deprn_period_catchup;
1247 ELSE
1248 l_curr_deprn_ytd := l_prev_deprn_ytd + l_curr_deprn_period + l_curr_deprn_period_catchup;
1249 END IF;
1250 ELSE
1251 -- is a non deprecaiting asset
1252 l_curr_deprn_period := 0;
1253 l_curr_deprn_period_catchup := 0;
1254 l_curr_deprn_ytd := 0;
1255 END IF;
1256 ELSE
1257 l_curr_cost := l_prev_cost;
1258 l_curr_deprn_period := l_prev_deprn_period;
1259 -- calculate the YTD depreciation amount
1260 IF (l_period_info_rec.period_num = 1) THEN
1261 l_curr_deprn_ytd := l_curr_deprn_period;
1262 ELSE
1263 l_curr_deprn_ytd := l_prev_deprn_ytd + l_curr_deprn_period;
1264 END IF;
1265 END IF;
1266
1267 -- set the rest of the details
1268 -- bug 3139173, handling exception or comments
1269
1270 l_proj_rec.asset_exception(l_count) := NULL;
1271 --IF (l_curr_cost = 0) THEN
1272 IF (l_next_period_ctr = l_last_prd_counter) THEN
1273 -- asset is fully reserved
1274 l_proj_rec.asset_exception(l_count) := 'FULL_RSVD2';
1275 END IF;
1276
1277 l_proj_rec.latest_reval_cost(l_count) := l_curr_cost;
1278
1279 IF (l_period_info_rec.period_num = l_reval_period_num) THEN
1280 l_proj_rec.deprn_period(l_count) := l_curr_deprn_period_catchup + l_curr_deprn_period;
1281 l_proj_rec.asset_exception(l_count) := 'REVAL_PRD';
1282 ELSE
1283 l_proj_rec.deprn_period(l_count) := l_curr_deprn_period;
1284 -- l_proj_rec.asset_exception(l_count) := NULL;
1285 END IF;
1286 l_proj_rec.deprn_ytd(l_count) := l_curr_deprn_ytd;
1287
1288 -- set the exception flag for non depreciating assets for the first rwo only
1289 IF (l_next_period_ctr = l_start_period_counter AND l_get_asset_bal_rec.source_type = 'NONDEPFA') THEN
1290 l_proj_rec.asset_exception(l_count) := 'NON_DEPR_ASSET';
1291 END IF;
1292
1293 -- round the calculated amounts
1294 l_ret_flag := igi_iac_common_utils.iac_round(l_proj_rec.latest_reval_cost(l_count),l_book_type_code);
1295 l_ret_flag := igi_iac_common_utils.iac_round(l_proj_rec.deprn_period(l_count),l_book_type_code);
1296 l_ret_flag := igi_iac_common_utils.iac_round(l_proj_rec.deprn_ytd(l_count),l_book_type_code);
1297
1298 -- Get the next period ctr and store it in l_next_period_ctr
1299 get_next_period_ctr(l_period_info_rec,l_book_type_code,l_next_period_ctr);
1300
1301 -- set the previous values
1302 l_prev_cost := l_curr_cost;
1303 l_prev_deprn_period := l_curr_deprn_period;
1304 l_prev_deprn_ytd := l_curr_deprn_ytd;
1305 END IF; -- check for valide PI
1306 END LOOP; -- end projection period counter loop (while)
1307
1308 END IF; -- fully reserved asset check
1309 END LOOP; -- end for c_get_asset_all loop
1310 END LOOP; -- end for l_asset_id loop
1311
1312 IF (l_rec_count > 0) THEN
1313 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'Do_Proj_Calc' ,'Before insertion into igi_iac_proj_details');
1314 -- insert the projections into igi_iac_proj_details
1315 FORALL l_count IN l_proj_rec.record_counter.FIRST..l_proj_rec.record_counter.LAST
1316 INSERT INTO igi_iac_proj_details(
1317 projection_id,
1318 period_counter,
1319 category_id,
1320 fiscal_year,
1321 company,
1322 cost_center,
1323 asset_id,
1324 latest_reval_cost,
1325 deprn_period,
1326 deprn_ytd,
1327 asset_exception,
1328 created_by,
1329 creation_date,
1330 last_updated_by,
1331 last_update_date,
1332 last_update_login
1333 ) VALUES (
1334 l_proj_rec.projection_id(l_count),
1335 l_proj_rec.period_counter(l_count),
1336 l_proj_rec.category_id(l_count),
1337 l_proj_rec.fiscal_year(l_count),
1338 l_proj_rec.company(l_count),
1339 l_proj_rec.cost_center(l_count),
1340 l_proj_rec.asset_id(l_count),
1341 l_proj_rec.latest_reval_cost(l_count),
1342 l_proj_rec.deprn_period(l_count),
1343 l_proj_rec.deprn_ytd(l_count),
1344 l_proj_rec.asset_exception(l_count),
1345 l_user_id,
1346 sysdate,
1347 l_user_id,
1348 sysdate,
1349 l_login_id);
1350 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'Do_Proj_Calc' ,'Insert complete');
1351
1352 -- submit the report
1353 igi_iac_debug_pkg.debug_other_string(g_event_level,g_path||'Do_Proj_Calc' ,'Submitted the RX Projections report');
1354 submit_report_request(p_projection_id,
1355 l_book_type_code,
1356 l_reval_period_num,
1357 l_concat_cat,
1358 l_start_period_name,
1359 l_end_period_name,
1360 p_rx_attribute_set,
1361 p_rx_output_format
1362 );
1363 ELSE
1364 RAISE NO_ASSETS_TO_PROJECT;
1365 END IF;
1366 FND_MESSAGE.SET_NAME('IGI', 'IGI_IAC_EXCEPTION');
1367 FND_MESSAGE.SET_TOKEN('PACKAGE','igi_iac_proj_pkg');
1368 FND_MESSAGE.SET_TOKEN('ERROR_MESSAGE','Projections completed successfully');
1369
1370 igi_iac_debug_pkg.debug_other_msg(g_event_level,g_path||'Do_Proj_Calc',TRUE);
1371 errbuf := fnd_message.get;
1372 retcode := 0;
1373 -- ROLLBACK;
1374 EXCEPTION
1375 WHEN NO_PROJ_MAIN_DATA then
1376 Update_Status(p_projection_id, 'ERROR');
1377 igi_iac_debug_pkg.debug_other_string(g_error_level,g_path||'Do_Proj_Calc' ,'Projection submission information could not be found ');
1378 FND_MESSAGE.SET_NAME('IGI', 'IGI_IAC_EXCEPTION');
1379 FND_MESSAGE.SET_TOKEN('PACKAGE','igi_iac_proj_pkg');
1380 FND_MESSAGE.SET_TOKEN('ERROR_MESSAGE','Projection submission information could not be found ');
1381
1382 igi_iac_debug_pkg.debug_other_msg(g_error_level,g_path||'Do_Proj_Calc',FALSE);
1383 errbuf := fnd_message.get;
1384 retcode := 2;
1385 WHEN NO_DATA_FOUND then
1386 Update_Status(p_projection_id, 'ERROR');
1387 igi_iac_debug_pkg.debug_other_string(g_error_level,g_path||'Do_Proj_Calc' ,'No data found '||sqlerrm);
1388 FND_MESSAGE.SET_NAME('IGI', 'IGI_IAC_EXCEPTION');
1389 FND_MESSAGE.SET_TOKEN('PACKAGE','igi_iac_proj_pkg');
1390 FND_MESSAGE.SET_TOKEN('ERROR_MESSAGE','No data found '||sqlerrm);
1391
1392 igi_iac_debug_pkg.debug_other_msg(g_error_level,g_path||'Do_Proj_Calc',FALSE);
1393 errbuf := fnd_message.get;
1394 retcode := 2;
1395 WHEN NO_ASSETS_FOUND then
1396 Update_Status(p_projection_id, 'ERROR');
1397 igi_iac_debug_pkg.debug_other_string(g_error_level,g_path||'Do_Proj_Calc' ,'No assets are present for this book and category . Hence projections is not run ');
1398 FND_MESSAGE.SET_NAME('IGI', 'IGI_IAC_EXCEPTION');
1399 FND_MESSAGE.SET_TOKEN('PACKAGE','igi_iac_proj_pkg');
1400 FND_MESSAGE.SET_TOKEN('ERROR_MESSAGE','No assets are present for this book and category . Hence projections is not run ');
1401
1402 igi_iac_debug_pkg.debug_other_msg(g_error_level,g_path||'Do_Proj_Calc',FALSE);
1403 errbuf := fnd_message.get;
1404 retcode := 2;
1405 WHEN NO_INDEX_FOUND then
1406 Update_Status(p_projection_id, 'ERROR');
1407 igi_iac_debug_pkg.debug_other_string(g_error_level,g_path||'Do_Proj_Calc' ,'No Price index value found for this calendar and price index ');
1408 FND_MESSAGE.SET_NAME('IGI', 'IGI_IAC_EXCEPTION');
1409 FND_MESSAGE.SET_TOKEN('PACKAGE','igi_iac_proj_pkg');
1410 FND_MESSAGE.SET_TOKEN('ERROR_MESSAGE','No Price index value found for this calendar and price index ');
1411
1412 igi_iac_debug_pkg.debug_other_msg(g_error_level,g_path||'Do_Proj_Calc',FALSE);
1413 errbuf := fnd_message.get;
1414 retcode := 2;
1415 WHEN NO_ASSETS_TO_PROJECT then
1416 igi_iac_debug_pkg.debug_other_string(g_error_level,g_path||'Do_Proj_Calc' ,'No assets have been selected for Projections. Hence, report not submitted');
1417 FND_MESSAGE.SET_NAME('IGI', 'IGI_IAC_EXCEPTION');
1418 FND_MESSAGE.SET_TOKEN('PACKAGE','igi_iac_proj_pkg');
1419 FND_MESSAGE.SET_TOKEN('ERROR_MESSAGE','No assets have been selected for Projections. Hence, report not submitted');
1420
1421 igi_iac_debug_pkg.debug_other_msg(g_error_level,g_path||'Do_Proj_Calc',FALSE);
1422 errbuf := fnd_message.get;
1423 retcode := 0;
1424 WHEN OTHERS THEN
1425 Update_Status(p_projection_id, 'ERROR');
1426 igi_iac_debug_pkg.debug_other_string(g_unexp_level,g_path||'Do_Proj_Calc' ,sqlerrm);
1427 FND_MESSAGE.SET_NAME('IGI', 'IGI_IAC_EXCEPTION');
1428 FND_MESSAGE.SET_TOKEN('PACKAGE','igi_iac_proj_pkg');
1429 FND_MESSAGE.SET_TOKEN('ERROR_MESSAGE',sqlerrm);
1430
1431 igi_iac_debug_pkg.debug_other_msg(g_error_level,g_path||'Do_Proj_Calc',FALSE);
1432 errbuf := fnd_message.get;
1433 retcode := 2;
1434 END Do_Proj_Calc;
1435
1436 -- 15-May-2003, add new procedure to delete projections for a range of projection ids
1437 PROCEDURE Delete_Projections(
1438 p_from_projection IN igi_iac_projections.projection_id%TYPE,
1439 p_to_projection IN igi_iac_projections.projection_id%TYPE
1440 ) IS
1441
1442 BEGIN
1443
1444 DELETE FROM igi_iac_proj_details
1445 WHERE projection_id BETWEEN p_from_projection AND p_to_projection;
1446
1447 DELETE FROM igi_iac_projections
1448 WHERE projection_id BETWEEN p_from_projection AND p_to_projection;
1449
1450 DELETE FROM igi_iac_proj_rep_itf
1451 WHERE projection_id BETWEEN p_from_projection AND p_to_projection;
1452
1453 END Delete_Projections;
1454
1455
1456 END igi_iac_proj_pkg; -- Package body
1457
[Home] [Help]