[Home] [Help]
PACKAGE BODY: APPS.IGI_IAC_REINSTATE_PKG
Source
1 PACKAGE BODY IGI_IAC_REINSTATE_PKG AS
2 -- $Header: igiiarnb.pls 120.22.12000000.2 2007/10/16 14:24:36 sharoy ship $
3 -- ===================================================================
4 -- Global Variables
5 -- ===================================================================
6
7 --===========================FND_LOG.START=====================================
8
9 g_state_level NUMBER;
10 g_proc_level NUMBER;
11 g_event_level NUMBER;
12 g_excep_level NUMBER;
13 g_error_level NUMBER;
14 g_unexp_level NUMBER;
15 g_path VARCHAR2(100);
16
17 --===========================FND_LOG.END=======================================
18
19 g_message VARCHAR2(200);
20 g_calling_fn VARCHAR2(200);
21 g_calling_fn1 VARCHAR2(200);
22
23 g_book_type_code igi_iac_transaction_headers.book_type_code%TYPE;
24 g_asset_id igi_iac_transaction_headers.asset_id%TYPE;
25 g_retirement_id NUMBER;
26 g_adj_prior_ret igi_iac_transaction_headers.adjustment_id%TYPE;
27
28 -- define asset rec type
29 TYPE fa_reins_rec_info IS RECORD (asset_id NUMBER,
30 book_type_code VARCHAR2(15),
31 transaction_header_id NUMBER,
32 transaction_type_code VARCHAR2(30),
33 transaction_date_entered DATE,
34 date_effective DATE,
35 mass_reference_id NUMBER,
36 transaction_subtype VARCHAR2(30),
37 asset_category_id NUMBER,
38 set_of_books_id NUMBER,
39 curr_period_counter NUMBER,
40 eff_ret_period_counter NUMBER
41 );
42
43 -- ===================================================================
44 -- Common Cursors
45 -- ===================================================================
46
47 -- cursor to see if the asset has been fully reserved
48 CURSOR c_fully_reserved(n_asset_id NUMBER,
49 n_book_type_code VARCHAR2)
50 IS
51 SELECT nvl(period_counter_fully_reserved,0)
52 FROM fa_books
53 WHERE book_type_code = n_book_type_code
54 AND asset_id = n_asset_id
55 AND date_ineffective IS NULL;
56
57 -- the units_assigned can be obtained from fa_distribution_history
58 CURSOR c_fa_units_assigned(n_dist_id NUMBER)
59 IS
60 SELECT units_assigned
61 FROM fa_distribution_history
62 WHERE distribution_id = n_dist_id;
63
64
65 -- retrieve the transaction just prior to the
66 -- retirement
67 CURSOR c_trx_prev_ret(cp_book_type_code igi_iac_transaction_headers.book_type_code%TYPE,
68 cp_asset_id igi_iac_transaction_headers.asset_id%TYPE,
69 cp_trxhdr_id_retire igi_iac_transaction_headers.transaction_header_id%TYPE)
70 IS
71 SELECT adjustment_id
72 FROM igi_iac_transaction_headers
73 WHERE book_type_code = cp_book_type_code
74 AND asset_id = cp_asset_id
75 AND adjustment_id_out = (select min(adjustment_id)
76 from igi_iac_transaction_headers
77 where transaction_header_id = cp_trxhdr_id_retire);
78 /*CURSOR c_trx_prev_ret(n_trx_id NUMBER)
79 IS
80 SELECT adjustment_id,
81 period_counter
82 FROM igi_iac_transaction_headers
83 WHERE adjustment_id_out = (SELECT adjustment_id
84 FROM igi_iac_transaction_headers
85 WHERE transaction_header_id = n_trx_id); */
86
87 -- cursor to get the asset balances for the period
88 CURSOR c_ret_ass_bal(n_asset_id NUMBER,
89 n_book_type_code VARCHAR2,
90 n_period_counter NUMBER)
91 IS
92 SELECT iiab.period_counter,
93 iiab.net_book_value,
94 iiab.adjusted_cost,
95 iiab.operating_acct,
96 iiab.reval_reserve,
97 iiab.deprn_amount,
98 iiab.deprn_reserve,
99 iiab.backlog_deprn_reserve,
100 iiab.general_fund,
101 iiab.last_reval_date,
102 iiab.current_reval_factor,
103 iiab.cumulative_reval_factor
104 FROM igi_iac_asset_balances iiab
105 WHERE iiab.asset_id = n_asset_id
106 AND iiab.book_type_code = n_book_type_code
107 AND iiab.period_counter = n_period_counter;
108
109 -- cursor to get all distribution_ids
110 -- from igi_iac_det_balances for an
111 -- adjustment_id
112
113 CURSOR c_det_bal(n_adjust_id NUMBER)
114 IS
115 SELECT iidb.adjustment_id,
116 iidb.distribution_id,
117 iidb.period_counter,
118 iidb.adjustment_cost,
119 iidb.net_book_value,
120 iidb.reval_reserve_cost,
121 iidb.reval_reserve_backlog,
122 iidb.reval_reserve_gen_fund,
123 iidb.reval_reserve_net,
124 iidb.operating_acct_cost,
125 iidb.operating_acct_backlog,
126 iidb.operating_acct_ytd,
127 iidb.operating_acct_net,
128 iidb.deprn_period,
129 iidb.deprn_ytd,
130 iidb.deprn_reserve,
131 iidb.deprn_reserve_backlog,
132 iidb.general_fund_per,
133 iidb.general_fund_acc,
134 iidb.last_reval_date,
135 iidb.current_reval_factor,
136 iidb.cumulative_reval_factor,
137 iidb.active_flag
138 FROM igi_iac_det_balances iidb
139 WHERE iidb.adjustment_id = n_adjust_id
140 AND iidb.active_flag IS NULL
141 ORDER BY iidb.distribution_id;
142
143 /* Bug 2906934 vgadde 25/04/2003 Start(1) */
144 -- Cursor to fetch depreciation balances from
145 -- igi_iac_fa_deprn for a adjustment_id and
146 -- distribution_id
147 CURSOR c_get_fa_deprn(cp_adjustment_id NUMBER,cp_distribution_id NUMBER) IS
148 SELECT iifd.deprn_period,
149 iifd.deprn_ytd,
150 iifd.deprn_reserve
151 FROM igi_iac_fa_deprn iifd
152 WHERE iifd.adjustment_id = cp_adjustment_id
153 AND iifd.distribution_id = cp_distribution_id;
154
155 -- Cursor to fetch depreciation balances from
156 -- fa_deprn_detail for a distribution
157 CURSOR c_get_fa_det(cp_book_type_code VARCHAR2,
158 cp_asset_id NUMBER,
159 cp_distribution_id NUMBER,
160 cp_period_counter NUMBER)
161 IS
162 SELECT nvl(deprn_reserve,0) deprn_reserve,
163 (nvl(deprn_amount,0) - nvl(deprn_adjustment_amount,0)) deprn_amount
164 FROM fa_deprn_detail
165 WHERE book_type_code = cp_book_type_code
166 AND asset_id = cp_asset_id
167 AND distribution_id = cp_distribution_id
168 AND period_counter = (SELECT max(period_counter)
169 FROM fa_deprn_detail
170 WHERE book_type_code = cp_book_type_code
171 AND asset_id = cp_asset_id
172 AND distribution_id = cp_distribution_id
173 AND period_counter <= cp_period_counter);
174
175 -- cursor to retieve the ytd value for a distribution
176 CURSOR c_get_fa_ytd(cp_book_type_code varchar2,
177 cp_asset_id number,
178 cp_distribution_id number)
179 IS
180 SELECT sum(nvl(fdd.deprn_amount,0)-nvl(fdd.deprn_adjustment_amount,0)) deprn_YTD
181 FROM fa_deprn_detail fdd
182 WHERE fdd.distribution_id = cp_distribution_id
183 AND fdd.book_type_code = cp_book_type_code
184 AND fdd.asset_id = cp_asset_id
185 AND fdd.period_counter IN (SELECT period_counter
186 FROM fa_deprn_periods
187 WHERE book_type_code = cp_book_type_code
188 AND fiscal_year = (SELECT fiscal_year
189 FROM fa_deprn_periods
190 WHERE period_close_date IS NULL
191 AND book_type_code = cp_book_type_code))
192 GROUP BY fdd.distribution_id;
193 /* Bug 2906934 vgadde 25/04/2003 End(1) */
194
195 -- cursor to get the YTD rows associated to the
196 -- retirement
197 CURSOR c_get_ytd(cp_adjustment_id NUMBER,
198 cp_asset_id NUMBER,
199 cp_book_type_code VARCHAR2)
200 IS
201 SELECT iidb.adjustment_id,
202 iidb.distribution_id,
203 iidb.adjustment_cost,
204 iidb.net_book_value,
205 iidb.reval_reserve_cost,
206 iidb.reval_reserve_backlog,
207 iidb.reval_reserve_gen_fund,
208 iidb.reval_reserve_net,
209 iidb.operating_acct_cost,
210 iidb.operating_acct_backlog,
211 iidb.operating_acct_net,
212 iidb.operating_acct_ytd,
213 iidb.deprn_period,
214 iidb.deprn_ytd,
215 iidb.deprn_reserve,
216 iidb.deprn_reserve_backlog,
217 iidb.general_fund_per,
218 iidb.general_fund_acc,
219 iidb.active_flag,
220 iidb.last_reval_date,
221 iidb.current_reval_factor,
222 iidb.cumulative_reval_factor
223 FROM igi_iac_det_balances iidb,
224 fa_distribution_history fdh
225 WHERE iidb.adjustment_id = cp_adjustment_id
226 AND iidb.book_type_code = cp_book_type_code
227 AND iidb.asset_id = cp_asset_id
228 AND fdh.book_type_code = cp_book_type_code
229 AND fdh.asset_id = cp_asset_id
230 AND iidb.distribution_id = fdh.distribution_id
231 AND fdh.transaction_header_id_out IS NOT NULL;
232
233 -- cursor to retrieve the depreciation expense
234 -- for an adjustment id and distribution id
235 CURSOR c_deprn_expense(n_adjust_id NUMBER,
236 n_dist_id NUMBER)
237 IS
238 SELECT iidb.deprn_period,
239 iidb.deprn_reserve,
240 iidb.general_fund_acc,
241 iidb.reval_reserve_gen_fund
242 FROM igi_iac_det_balances iidb
243 WHERE iidb.adjustment_id = n_adjust_id
244 AND iidb.distribution_id = n_dist_id;
245
246 -- bug 2480915, start (1)
247 -- cursor to retrieve a row from igi_iac_fa_deprn
248
249 CURSOR c_fa_deprn(n_adjust_id NUMBER,
250 n_dist_id NUMBER,
251 n_prd_cnt NUMBER)
252 IS
253 SELECT ifd.book_type_code,
254 ifd.asset_id,
255 ifd.period_counter,
256 ifd.adjustment_id,
257 ifd.distribution_id,
258 ifd.deprn_period,
259 ifd.deprn_ytd,
260 ifd.deprn_reserve,
261 ifd.active_flag
262 FROM igi_iac_fa_deprn ifd
263 WHERE ifd.adjustment_id = n_adjust_id
264 AND ifd.distribution_id = n_dist_id
265 AND ifd.period_counter = n_prd_cnt;
266
267 -- cursor to retieve the ytd figure for a distribution
268 CURSOR c_get_dist_ytd(cp_book_type_code varchar2,
269 cp_asset_id number,
270 cp_distribution_id number,
271 cp_period_counter number)
272 IS
273 -- SELECT sum(nvl(fdd.deprn_amount,0)-nvl(fdd.deprn_adjustment_amount,0)) deprn_YTD
274 SELECT fdd.ytd_deprn
275 FROM fa_deprn_detail fdd
276 WHERE fdd.distribution_id = cp_distribution_id
277 AND fdd.book_type_code = cp_book_type_code
278 AND fdd.asset_id = cp_asset_id
279 AND fdd.period_counter = (SELECT period_counter
280 FROM fa_deprn_periods
281 WHERE book_type_code = cp_book_type_code
282 AND period_counter = cp_period_counter
283 AND fiscal_year = (SELECT fiscal_year
284 FROM fa_deprn_periods
285 WHERE period_close_date IS NULL
286 AND book_type_code = cp_book_type_code)) ;
287 -- GROUP BY fdd.asset_id,fdd.distribution_id;
288 -- bug 2480915, end (1)
289
290 -- ===================================================================
291 -- Local functions and procedures
292 -- ===================================================================
293 -- ===================================================================
294 -- FUNCTION DoRnding :
295 --
296 -- This procedure will round up the different amounts
297 -- ===================================================================
298 FUNCTION DoRnding(x_amount IN NUMBER)
299 RETURN number
300 IS
301 l_ret BOOLEAN;
302 l_amt NUMBER := 0;
303
304 BEGIN
305 l_amt := x_amount;
306 l_ret := IGI_IAC_COMMON_UTILS.Iac_Round(l_amt,
307 g_book_type_code);
308 RETURN l_amt;
309 END DoRnding;
310
311 -- bug 2452521 start(1)
312 -- ===================================================================
313 -- FUNCTION is_revalued :
314
315 -- This function is part of enhancement requirement bug 2452521 which
316 -- will stop an asset from being reinstated if it has been revalued
317 -- even once after it has been retired partially
318 -- ===================================================================
319 FUNCTION is_revalued(x_asset_id IN NUMBER,
320 x_book_type_code IN VARCHAR2,
321 -- x_ret_trx_hdr_id IN NUMBER,
322 x_eff_ret_period_cnt IN NUMBER,
323 X_curr_period_cnt IN NUMBER
324 )
325 RETURN boolean
326 IS
327 l_cnt NUMBER;
328 BEGIN
329 SELECT count(*)
330 INTO l_cnt
331 FROM igi_iac_transaction_headers a1
332 WHERE a1.asset_id = x_asset_id
333 AND a1.book_type_code = x_book_type_code
334 AND a1.transaction_type_code = 'REVALUATION'
335 AND a1.adjustment_status NOT IN ('PREVIEW', 'OBSOLETE')
336 AND a1.period_counter BETWEEN x_eff_ret_period_cnt AND x_curr_period_cnt;
337 /* AND a1.adjustment_id > (SELECT a2.adjustment_id
338 FROM igi_iac_transaction_headers a2
339 WHERE a2.transaction_header_id = x_ret_trx_hdr_id
340 AND a2.transaction_type_code = 'PARTIAL RETIRE'
341 AND a2.period_counter <= a1.period_counter
342 AND a2.asset_id = x_asset_id
343 AND a2.book_type_code = x_book_type_code); */
344
345 IF (l_cnt > 0) THEN
346 RETURN TRUE;
347 ELSE
348 RETURN FALSE;
349 END IF;
350
351 END is_revalued;
352 -- bug 2452521 end(1)
353
354
355 -- ===================================================================
356 -- FUNCTION elapsed_periods :
357
358 -- SHOULD BE DELETED
359
360 -- function to calculate the number of periods elapsed between
361 -- reinstatement and retirement
362 -- ===================================================================
363 /* FUNCTION elapsed_periods(x_ret_adj_id IN NUMBER,
364 x_ren_adj_id IN NUMBER)
365 RETURN number
366 IS
367 l_ret_prd_counter NUMBER;
368 l_ren_prd_counter NUMBER;
369 l_diff NUMBER := 0;
370
371 l_path VARCHAR2(150) := g_path||'elapsed_periods';
372 BEGIN
373 -- get the retirement period counter
374 SELECT period_counter
375 INTO l_ret_prd_counter
376 FROM igi_iac_transaction_headers
377 WHERE adjustment_id = x_ret_adj_id;
378
379 -- get the reinstatement period counter
380 SELECT period_counter
381 INTO l_ren_prd_counter
382 FROM igi_iac_transaction_headers
383 WHERE adjustment_id = x_ren_adj_id;
384
385 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Reinstate period counter: '||l_ren_prd_counter);
386 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Retire period counter: '||l_ret_prd_counter);
387 -- elapsed period
388 l_diff := l_ren_prd_counter - l_ret_prd_counter;
389 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Difference: '||l_diff);
390
391 RETURN l_diff;
392 EXCEPTION
393 WHEN OTHERS THEN
394 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
395 RETURN 0;
396 END elapsed_periods;
397 */
398 -- =======================================================================
399 -- FUNCTION Get_Corr_Ren_Dist_id:
400
401 -- This function will retrieve the corresponding reinstatement distribution
402 -- id that is linked
403 -- to the retired distribution id
404 -- =======================================================================
405
406 FUNCTION Get_Corr_Ren_Dist_Id(
407 n_trx_hdr_id_in IN NUMBER,
408 n_ret_dist_id IN NUMBER
409 )
410 RETURN NUMBER
411 IS
412
413 l_ren_dist_id NUMBER := 0;
414 l_cnt NUMBER := 0;
415 l_path VARCHAR2(150);
416 BEGIN
417 l_path := g_path||'Get_Corr_Ren_Dist_Id';
418
419 -- initially find out NOCOPY if the distribution has ever
420 -- been unit or full retired or never been retired
421 SELECT COUNT(*)
422 INTO l_cnt
423 FROM fa_distribution_history old,
424 fa_distribution_history new
425 WHERE old.location_id = new.location_id
426 AND old.code_combination_id = new.code_combination_id
427 AND NVL(old.assigned_to, -999) = NVL(new.assigned_to, -999)
428 AND old.retirement_id = g_retirement_id
429 AND new.transaction_header_id_in = n_trx_hdr_id_in
430 AND old.distribution_id = n_ret_dist_id;
431
432 IF (l_cnt = 0) THEN
433 -- never been retired
434 l_ren_dist_id := n_ret_dist_id;
435 ELSE
436 -- has been retired partially(units) or fully
437 SELECT new.distribution_id
438 INTO l_ren_dist_id
439 FROM fa_distribution_history old,
440 fa_distribution_history new
441 WHERE old.location_id = new.location_id
442 AND old.code_combination_id = new.code_combination_id
443 AND NVL(old.assigned_to, -999) = NVL(new.assigned_to, -999)
444 AND old.retirement_id = g_retirement_id
445 AND new.transaction_header_id_in = n_trx_hdr_id_in
446 AND old.distribution_id = n_ret_dist_id;
447 END IF;
448 RETURN l_ren_dist_id;
449 EXCEPTION
450 WHEN OTHERS THEN
451 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
452 RETURN 0;
453 END Get_Corr_Ren_Dist_Id;
454
455 -- =======================================================================
456 -- FUNCTION Full_Reinstatement :
457
458 -- Function to reinstate the asset fully
459 -- 1. This procedure will retrieve the rows just prior to the full retirement
460 -- and bring them forward as reinstated with catchup calculated for
461 -- depreciation reserve and expense. Catchup will be calculated for general
462 -- fund and reval resrve general fund if the adjustment cost is greater than
463 -- zero. Catchup for deprn expense
464 -- will be the whole amount brought forward from the transaction previous
465 -- to retirement
466 -- 2. The ytd rows associated with the retirement will be brought forward
467 -- ========================================================================
468 FUNCTION Full_Reinstatement(
469 p_adjust_id_reinstate NUMBER,
470 p_trxhdr_id_retire NUMBER,
471 p_trxhdr_id_reinstate NUMBER,
472 p_sob_id NUMBER,
473 p_period_counter NUMBER,
474 p_effective_retire_period_cnt NUMBER,
475 p_transaction_run VARCHAR2,
476 p_event_id NUMBER
477 )
478 RETURN BOOLEAN
479 IS
480
481 -- local variables
482 l_rowid ROWID;
483
484 l_prev_adj_id igi_iac_transaction_headers.adjustment_id%TYPE;
485 l_rsv_catchup_amt igi_iac_det_balances.deprn_period%TYPE;
486 l_gf_catchup_amt igi_iac_det_balances.general_fund_acc%TYPE;
487
488 l_ren_dist_id igi_iac_transaction_headers.adjustment_id%TYPE;
489 l_prorate_factor NUMBER;
490 l_elapsed_periods NUMBER;
491
492 l_adjustment_cost NUMBER := 0;
493 l_net_book_value NUMBER := 0;
494 l_reval_reserve_cost NUMBER := 0;
495 l_reval_reserve_backlog NUMBER := 0;
496 l_reval_reserve_gen_fund NUMBER := 0;
497 l_reval_reserve_net NUMBER := 0;
498 l_operating_acct_cost NUMBER := 0;
499 l_operating_acct_backlog NUMBER := 0;
500 l_operating_acct_net NUMBER := 0;
501 l_operating_acct_ytd NUMBER := 0;
502 l_deprn_period NUMBER := 0;
503 l_deprn_ytd NUMBER := 0;
504 l_deprn_reserve NUMBER := 0;
505 l_deprn_reserve_backlog NUMBER := 0;
506 l_general_fund_per NUMBER := 0;
507 l_general_fund_acc NUMBER := 0;
508
509 l_tot_adjustment_cost NUMBER := 0;
510 l_tot_net_book_value NUMBER := 0;
511 l_tot_reval_reserve_cost NUMBER := 0;
512 l_tot_reval_reserve_backlog NUMBER := 0;
513 l_tot_reval_reserve_gen_fund NUMBER := 0;
514 l_tot_reval_reserve_net NUMBER := 0;
515 l_tot_operating_acct_cost NUMBER := 0;
516 l_tot_operating_acct_backlog NUMBER := 0;
517 l_tot_operating_acct_net NUMBER := 0;
518 l_tot_operating_acct_ytd NUMBER := 0;
519 l_tot_deprn_period NUMBER := 0;
520 l_tot_deprn_ytd NUMBER := 0;
521 l_tot_deprn_reserve NUMBER := 0;
522 l_tot_deprn_reserve_backlog NUMBER := 0;
523 l_tot_general_fund_per NUMBER := 0;
524 l_tot_general_fund_acc NUMBER := 0;
525
526 l_ab_net_book_value NUMBER := 0;
527 l_ab_adjusted_cost NUMBER := 0;
528 l_ab_operating_acct NUMBER := 0;
529 l_ab_reval_reserve NUMBER := 0;
530 l_ab_deprn_amount NUMBER := 0;
531 l_ab_deprn_reserve NUMBER := 0;
532 l_ab_backlog_deprn_reserve NUMBER := 0;
533 l_ab_general_fund NUMBER := 0;
534
535 l_ccid igi_iac_adjustments.code_combination_id%TYPE;
536 l_reval_rsv_ccid igi_iac_adjustments.code_combination_id%TYPE;
537 l_exists NUMBER;
538 l_fully_reserved NUMBER;
539
540 l_ret_ass_bal c_ret_ass_bal%ROWTYPE;
541 l_units_assigned fa_distribution_history.units_assigned%TYPE;
542
543 -- bug 2480915 start(1)
544 l_fa_deprn_ytd igi_iac_fa_deprn.deprn_ytd%TYPE;
545 l_fa_deprn_period igi_iac_fa_deprn.deprn_period%TYPE;
546 l_fa_deprn_reserve igi_iac_fa_deprn.deprn_reserve%TYPE;
547 -- bug 2480915 end(1)
548 l_active_flag igi_iac_det_balances.active_flag%TYPE;
549
550 -- exceptions
551 e_no_corr_reinstatement EXCEPTION;
552 e_no_ccid_found EXCEPTION;
553
554 l_path VARCHAR2(150);
555 BEGIN
556 l_path := g_path||'Full_Reinstatement';
557 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'In Full Reinstatements function');
558 -- get the adjustment_id of the transaction previous to the
559 -- retirement
560 -- OPEN c_trx_prev_ret(p_trxhdr_id_retire);
561 OPEN c_trx_prev_ret(cp_book_type_code => g_book_type_code,
562 cp_asset_id => g_asset_id,
563 cp_trxhdr_id_retire => p_trxhdr_id_retire);
564 FETCH c_trx_prev_ret INTO l_prev_adj_id;
565 IF c_trx_prev_ret%NOTFOUND THEN
566 CLOSE c_trx_prev_ret;
567 RETURN FALSE;
568 END IF;
569
570 CLOSE c_trx_prev_ret;
571
572 -- calculate the number of periods that have elapsed between the transaction
573 -- previous to the retirement and reinstatement of the asset
574 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Reinstatement period counter: '|| p_period_counter);
575 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Retirement period cntr: '|| p_effective_retire_period_cnt);
576
577 -- l_elapsed_periods := p_period_counter - l_prev_prd_cnt;
578 l_elapsed_periods := p_period_counter - p_effective_retire_period_cnt;
579 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Elapsed periods: '|| l_elapsed_periods);
580
581 -- find out NOCOPY if the asset has been fully reserved prior to retirement
582 OPEN c_fully_reserved(g_asset_id, g_book_type_code);
583 FETCH c_fully_reserved INTO l_fully_reserved;
584 IF c_fully_reserved%NOTFOUND THEN
585 CLOSE c_fully_reserved;
586 RETURN FALSE;
587 END IF;
588 CLOSE c_fully_reserved;
589 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Asset fully reserved period counter: '||l_fully_reserved);
590
591 -- get all the previous balances to reinstate
592 FOR l_det_bal IN c_det_bal(l_prev_adj_id) LOOP
593
594 -- initialise to zero for each distribution_id
595 l_gf_catchup_amt := 0;
596 IF (l_fully_reserved <= 0) THEN
597 l_gf_catchup_amt := l_elapsed_periods *l_det_bal.deprn_period;
598 ELSE
599 l_gf_catchup_amt := 0;
600 END IF;
601
602 l_rsv_catchup_amt := 0;
603 IF (p_transaction_run = 'SECOND') THEN
604 -- calculate the catchup for depreciation reserve
605 l_rsv_catchup_amt := l_elapsed_periods *l_det_bal.deprn_period;
606 ELSE
607 l_rsv_catchup_amt := 0;
608 END IF;
609
610 -- If asset is fully reserved then catchup is 0
611 IF (l_fully_reserved > 0) THEN
612 l_rsv_catchup_amt := 0;
613 END IF;
614 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Depreciation expense: '||l_det_bal.deprn_period);
615 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Depreciation reserve: '||l_rsv_catchup_amt);
616 -- get the link between the reinstated distribution_ids to the
617 -- retired distribution id
618 l_ren_dist_id := Get_Corr_Ren_Dist_Id(
619 p_trxhdr_id_reinstate,
620 l_det_bal.distribution_id -- retirement
621 );
622
623 IF (l_ren_dist_id = 0) THEN
624 RAISE e_no_corr_reinstatement;
625 END IF;
626 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Reinstatement dist id: '||l_ren_dist_id);
627
628
629 -- from this the units_assigned can be obtained from fa_distribution_history
630 SELECT units_assigned
631 INTO l_units_assigned
632 FROM fa_distribution_history
633 WHERE distribution_id = l_ren_dist_id;
634
635 -- get account values
636 l_adjustment_cost := l_det_bal.adjustment_cost;
637 l_reval_reserve_cost := l_det_bal.reval_reserve_cost;
638 l_reval_reserve_backlog := l_det_bal.reval_reserve_backlog;
639 l_operating_acct_cost := l_det_bal.operating_acct_cost;
640 l_operating_acct_backlog := l_det_bal.operating_acct_backlog;
641 l_operating_acct_ytd := l_det_bal.operating_acct_ytd;
642 l_deprn_period := l_det_bal.deprn_period;
643 l_deprn_reserve_backlog := l_det_bal.deprn_reserve_backlog;
644
645 l_deprn_ytd := l_det_bal.deprn_ytd +l_rsv_catchup_amt;
646 l_deprn_reserve := l_det_bal.deprn_reserve + l_rsv_catchup_amt;
647 IF (l_det_bal.adjustment_cost > 0) THEN
648 l_general_fund_per := l_deprn_period;
649 l_general_fund_acc := l_det_bal.general_fund_acc + l_gf_catchup_amt;
650 l_reval_reserve_gen_fund := l_general_fund_acc;
651 l_reval_reserve_net := l_reval_reserve_cost - ( l_reval_reserve_backlog + l_reval_reserve_gen_fund);
652 ELSE
653 l_general_fund_per := l_det_bal.general_fund_per;
654 l_general_fund_acc := l_det_bal.general_fund_acc;
655 l_reval_reserve_gen_fund := l_det_bal.reval_reserve_gen_fund;
656 l_reval_reserve_net := l_det_bal.reval_reserve_net;
657 END IF;
658
659 l_operating_acct_net := l_operating_acct_cost - l_operating_acct_backlog;
660 l_net_book_value := l_adjustment_cost - (l_deprn_reserve + l_deprn_reserve_backlog);
661
662 -- bug 2480915 start(5) Modified for bug 2906034
663 -- get the deprn values from igi_iac_fa_deprn or fa_deprn_detail
664 OPEN c_get_fa_deprn(l_prev_adj_id, l_det_bal.distribution_id);
665 FETCH c_get_fa_deprn INTO l_fa_deprn_period, l_fa_deprn_ytd, l_fa_deprn_reserve;
666 IF c_get_fa_deprn%NOTFOUND THEN
667 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Record not found in igi_iac_fa_deprn');
668 OPEN c_get_fa_det(g_book_type_code,
669 g_asset_id,
670 l_det_bal.distribution_id,
671 l_det_bal.period_counter);
672 FETCH c_get_fa_det INTO l_fa_deprn_period, l_fa_deprn_reserve;
673 CLOSE c_get_fa_det;
674
675 OPEN c_get_fa_ytd(g_book_type_code,
676 g_asset_id,
677 l_det_bal.distribution_id);
678 FETCH c_get_fa_ytd INTO l_fa_deprn_ytd;
679 IF c_get_fa_ytd%NOTFOUND THEN
680 l_fa_deprn_ytd := 0;
681 END IF;
682 CLOSE c_get_fa_ytd;
683 ELSE
684 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Record found in igi_iac_fa_deprn');
685 IF l_fully_reserved > 0 THEN
686 l_fa_deprn_period := 0;
687 END IF;
688 IF (p_transaction_run = 'SECOND') THEN
689 l_fa_deprn_ytd := l_fa_deprn_ytd + (l_elapsed_periods*l_fa_deprn_period);
690 l_fa_deprn_reserve := l_fa_deprn_reserve + (l_elapsed_periods*l_fa_deprn_period);
691 END IF;
692 END IF;
693 CLOSE c_get_fa_deprn;
694
695 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'FA Depreciation amount: '||l_fa_deprn_period);
696 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Detail level FA deprn rsv: '||l_fa_deprn_reserve);
697 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Detail level FA deprn YTD: '||l_fa_deprn_ytd);
698 -- bug 2480915 end(5) modified for bug 2906034
699
700 -- keep a running total of the different account amounts after they have
701 -- been prorated
702 l_tot_adjustment_cost := l_tot_adjustment_cost + l_adjustment_cost;
703 l_tot_net_book_value := l_tot_net_book_value + l_net_book_value;
704 l_tot_reval_reserve_cost := l_tot_reval_reserve_cost + l_reval_reserve_cost;
705 l_tot_reval_reserve_backlog := l_tot_reval_reserve_backlog + l_reval_reserve_backlog;
706 l_tot_reval_reserve_gen_fund := l_tot_reval_reserve_gen_fund + l_reval_reserve_gen_fund;
707 l_tot_reval_reserve_net := l_tot_reval_reserve_net + l_reval_reserve_net;
708 l_tot_operating_acct_cost := l_tot_operating_acct_cost + l_operating_acct_cost;
709 l_tot_operating_acct_backlog := l_tot_operating_acct_backlog + l_operating_acct_backlog;
710 l_tot_operating_acct_net := l_tot_operating_acct_net + l_operating_acct_net;
711 l_tot_operating_acct_ytd := l_tot_operating_acct_ytd + l_operating_acct_ytd;
712 l_tot_deprn_period := l_tot_deprn_period + l_deprn_period;
713 l_tot_deprn_ytd := l_tot_deprn_ytd + l_deprn_ytd;
714 l_tot_deprn_reserve := l_tot_deprn_reserve + l_deprn_reserve;
715 l_tot_deprn_reserve_backlog := l_tot_deprn_reserve_backlog + l_deprn_reserve_backlog;
716 l_tot_general_fund_per := l_tot_general_fund_per + l_general_fund_per;
717 l_tot_general_fund_acc := l_tot_general_fund_acc + l_general_fund_acc;
718
719 -- insert into igi_iac_det_balances with reinstatement adjustment_id
720 IGI_IAC_DET_BALANCES_PKG.Insert_Row(
721 x_rowid => l_rowid,
722 x_adjustment_id => p_adjust_id_reinstate,
723 x_asset_id => g_asset_id,
724 x_book_type_code => g_book_type_code,
725 x_distribution_id => l_ren_dist_id,
726 x_period_counter => p_period_counter,
727 x_adjustment_cost => l_adjustment_cost,
728 x_net_book_value => l_net_book_value,
729 x_reval_reserve_cost => l_reval_reserve_cost,
730 x_reval_reserve_backlog => l_reval_reserve_backlog,
731 x_reval_reserve_gen_fund => l_reval_reserve_gen_fund,
732 x_reval_reserve_net => l_reval_reserve_net,
733 x_operating_acct_cost => l_operating_acct_cost,
734 x_operating_acct_backlog => l_operating_acct_backlog,
735 x_operating_acct_net => l_operating_acct_net,
736 x_operating_acct_ytd => l_operating_acct_ytd,
737 x_deprn_period => l_deprn_period,
738 x_deprn_ytd => l_deprn_ytd,
739 x_deprn_reserve => l_deprn_reserve,
740 x_deprn_reserve_backlog => l_deprn_reserve_backlog,
741 x_general_fund_per => l_general_fund_per,
742 x_general_fund_acc => l_general_fund_acc,
743 x_last_reval_date => l_det_bal.last_reval_date,
744 x_current_reval_factor => l_det_bal.current_reval_factor,
745 x_cumulative_reval_factor => l_det_bal.cumulative_reval_factor,
746 x_active_flag => null,
747 x_mode => 'R'
748 );
749
750 -- Bug 2480915, start(6)
751 -- insert into igi_iac_fa_deprn with the reinstatement adjustment_id
752 IGI_IAC_FA_DEPRN_PKG.Insert_Row(
753 x_rowid => l_rowid,
754 x_book_type_code => g_book_type_code,
755 x_asset_id => g_asset_id,
756 x_period_counter => p_period_counter,
757 x_adjustment_id => p_adjust_id_reinstate,
758 x_distribution_id => l_ren_dist_id,
759 x_deprn_period => l_fa_deprn_period,
760 x_deprn_ytd => l_fa_deprn_ytd,
761 x_deprn_reserve => l_fa_deprn_reserve,
762 x_active_flag => null,
763 x_mode => 'R'
764 );
765 -- Bug 2480915, end(6)
766
767 -- do the adjustment catchup journals here, insert only if the catchup amount
768 -- is greater than zero and is the second Depreciation transaction
769 IF (p_transaction_run = 'SECOND') THEN
770 IF (l_elapsed_periods > 0) THEN
771 -- get the ccid for the account type Depreciation Expense
772 IF NOT igi_iac_common_utils.get_account_ccid(g_book_type_code,
773 g_asset_id,
774 l_det_bal.distribution_id,
775 'DEPRN_EXPENSE_ACCT',
776 p_trxhdr_id_reinstate,
777 'RETIREMENT',
778 l_ccid)
779 THEN
780 --RETURN false;
781 g_message := 'No account code combination found for Depreciation Expense';
782 RAISE e_no_ccid_found;
783 END IF;
784 -- insert into igi_iac_adjustments
785 IGI_IAC_ADJUSTMENTS_PKG.Insert_Row(
786 x_rowid => l_rowid,
787 x_adjustment_id => p_adjust_id_reinstate,
788 x_book_type_code => g_book_type_code,
789 x_code_combination_id => l_ccid,
790 x_set_of_books_id => p_sob_id,
791 x_dr_cr_flag => 'DR',
792 x_amount => l_rsv_catchup_Amt, -- l_deprn_period,
793 x_adjustment_type => 'EXPENSE',
794 x_adjustment_offset_type => 'RESERVE',
795 x_report_ccid => Null,
796 x_transfer_to_gl_flag => 'Y',
797 x_units_assigned => l_units_assigned,
798 x_asset_id => g_asset_id,
799 x_distribution_id => l_ren_dist_id,
800 x_period_counter => p_period_counter,
801 x_mode => 'R',
802 x_event_id => p_event_id );
803 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Done Expense: '||l_rsv_catchup_amt);
804 -- insert RESERVE journal into igi_iac_adjustments with the reserve catchup amount
805 -- get the ccid for the account type
806 IF NOT igi_iac_common_utils.get_account_ccid(g_book_type_code,
807 g_asset_id,
808 l_ren_dist_id,
809 'DEPRN_RESERVE_ACCT',
810 p_trxhdr_id_reinstate,
811 'RETIREMENT',
812 l_ccid)
813 THEN
814 --RETURN false;
815 g_message := 'No account code combination found for Accumulated Depreciation';
816 RAISE e_no_ccid_found;
817 END IF;
818 -- insert into igi_iac_adjustments
819 IGI_IAC_ADJUSTMENTS_PKG.Insert_Row(
820 x_rowid => l_rowid,
821 x_adjustment_id => p_adjust_id_reinstate,
822 x_book_type_code => g_book_type_code,
823 x_code_combination_id => l_ccid,
824 x_set_of_books_id => p_sob_id,
825 x_dr_cr_flag => 'CR',
826 x_amount => l_rsv_catchup_amt,
827 x_adjustment_type => 'RESERVE',
828 x_adjustment_offset_type => 'EXPENSE',
829 x_report_ccid => Null,
830 x_transfer_to_gl_flag => 'Y',
831 x_units_assigned => l_units_assigned,
832 x_asset_id => g_asset_id,
833 x_distribution_id => l_ren_dist_id,
834 x_period_counter => p_period_counter,
835 x_mode => 'R',
836 x_event_id => p_event_id );
837 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Done Deprn Reserve: '||l_rsv_catchup_amt);
838 -- insert GENERAL FUND journal into igi_iac_adjustments with the catchup amount
839 -- only if adjustment amount is greater than zero
840 IF (l_det_bal.adjustment_cost > 0) THEN
841 -- get the ccid for the account type Reval Reserve
842 IF NOT igi_iac_common_utils.get_account_ccid(g_book_type_code,
843 g_asset_id,
844 l_ren_dist_id,
845 'REVAL_RESERVE_ACCT',
846 p_trxhdr_id_reinstate,
847 'RETIREMENT',
848 l_ccid)
849 THEN
850 --RETURN false;
851 g_message := 'No account code combination found for Revaluation Reserve';
852 RAISE e_no_ccid_found;
853 END IF;
854
855 l_reval_rsv_ccid := l_ccid;
856
857 -- insert into igi_iac_adjustments
858 IGI_IAC_ADJUSTMENTS_PKG.Insert_Row(
859 x_rowid => l_rowid,
860 x_adjustment_id => p_adjust_id_reinstate,
861 x_book_type_code => g_book_type_code,
862 x_code_combination_id => l_ccid,
863 x_set_of_books_id => p_sob_id,
864 x_dr_cr_flag => 'DR',
865 x_amount => l_rsv_catchup_Amt, --l_gf_catchup_amt,
866 x_adjustment_type => 'REVAL RESERVE',
867 x_adjustment_offset_type => 'GENERAL FUND',
868 x_report_ccid => Null,
869 x_transfer_to_gl_flag => 'Y',
870 x_units_assigned => l_units_assigned,
871 x_asset_id => g_asset_id,
872 x_distribution_id => l_ren_dist_id,
873 x_period_counter => p_period_counter,
874 x_mode => 'R',
875 x_event_id => p_event_id );
876
877 -- get the ccid for the account type General Fund
878 IF NOT igi_iac_common_utils.get_account_ccid(g_book_type_code,
879 g_asset_id,
880 l_ren_dist_id,
881 'GENERAL_FUND_ACCT',
882 p_trxhdr_id_reinstate,
883 'RETIREMENT',
884 l_ccid)
885 THEN
886 --RETURN false;
887 g_message := 'No account code combination found for General Fund';
888 RAISE e_no_ccid_found;
889 END IF;
890 -- insert into igi_iac_adjustments
891 IGI_IAC_ADJUSTMENTS_PKG.Insert_Row(
892 x_rowid => l_rowid,
893 x_adjustment_id => p_adjust_id_reinstate,
894 x_book_type_code => g_book_type_code,
895 x_code_combination_id => l_ccid,
896 x_set_of_books_id => p_sob_id,
897 x_dr_cr_flag => 'CR',
898 x_amount => l_rsv_catchup_Amt, --l_gf_catchup_amt,
899 x_adjustment_type => 'GENERAL FUND',
900 x_adjustment_offset_type => 'REVAL RESERVE',
901 x_report_ccid => l_reval_rsv_ccid,
902 x_transfer_to_gl_flag => 'Y',
903 x_units_assigned => l_units_assigned,
904 x_asset_id => g_asset_id,
905 x_distribution_id => l_ren_dist_id,
906 x_period_counter => p_period_counter,
907 x_mode => 'R',
908 x_event_id => p_event_id
909 );
910 END IF; -- adjustment cost > 0
911 END IF; -- elapsed periods > 0
912 END IF; -- second transaction
913 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'End for distribution: '||l_det_bal.distribution_id);
914 END LOOP;
915
916 -- bring the YTD rows associated to the retirement over with the
917 -- reinstatement adjustment id and current period counter
918 FOR l_get_ytd IN c_get_ytd(cp_adjustment_id => l_prev_adj_id,
919 cp_asset_id => g_asset_id,
920 cp_book_type_code => g_book_type_code) LOOP
921 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'In YTD: dist id: '||l_get_ytd.distribution_id);
922
923 IF nvl(l_get_ytd.active_flag,'Y') = 'Y' THEN
924 l_get_ytd.adjustment_cost := 0;
925 l_get_ytd.net_book_value := 0;
926 l_get_ytd.reval_reserve_cost := 0;
927 l_get_ytd.reval_reserve_backlog := 0;
928 l_get_ytd.reval_reserve_gen_fund := 0;
929 l_get_ytd.reval_reserve_net := 0;
930 l_get_ytd.operating_acct_cost := 0;
931 l_get_ytd.operating_acct_backlog := 0;
932 l_get_ytd.operating_acct_net := 0;
933 l_get_ytd.operating_acct_ytd := 0;
934 l_get_ytd.deprn_period := 0;
935 l_get_ytd.deprn_ytd := 0;
936 l_get_ytd.deprn_reserve := 0;
937 l_get_ytd.deprn_reserve_backlog := 0;
938 l_get_ytd.general_fund_per := 0;
939 l_get_ytd.general_fund_acc := 0;
940 END IF;
941 l_active_flag := 'N';
942
943 -- insert into igi_iac_det_balances with reinstatement adjustment_id
944 IGI_IAC_DET_BALANCES_PKG.Insert_Row(
945 x_rowid => l_rowid,
946 x_adjustment_id => p_adjust_id_reinstate,
947 x_asset_id => g_asset_id,
948 x_book_type_code => g_book_type_code,
949 x_distribution_id => l_get_ytd.distribution_id,
950 x_period_counter => p_period_counter,
951 x_adjustment_cost => l_get_ytd.adjustment_cost,
952 x_net_book_value => l_get_ytd.net_book_value,
953 x_reval_reserve_cost => l_get_ytd.reval_reserve_cost,
954 x_reval_reserve_backlog => l_get_ytd.reval_reserve_backlog,
955 x_reval_reserve_gen_fund => l_get_ytd.reval_reserve_gen_fund,
956 x_reval_reserve_net => l_get_ytd.reval_reserve_net,
957 x_operating_acct_cost => l_get_ytd.operating_acct_cost,
958 x_operating_acct_backlog => l_get_ytd.operating_acct_backlog,
959 x_operating_acct_net => l_get_ytd.operating_acct_net,
960 x_operating_acct_ytd => l_get_ytd.operating_acct_ytd,
961 x_deprn_period => l_get_ytd.deprn_period,
962 x_deprn_ytd => l_get_ytd.deprn_ytd,
963 x_deprn_reserve => l_get_ytd.deprn_reserve,
964 x_deprn_reserve_backlog => l_get_ytd.deprn_reserve_backlog,
965 x_general_fund_per => l_get_ytd.general_fund_per,
966 x_general_fund_acc => l_get_ytd.general_fund_acc,
967 x_last_reval_date => l_get_ytd.last_reval_date,
968 x_current_reval_factor => l_get_ytd.current_reval_factor,
969 x_cumulative_reval_factor => l_get_ytd.cumulative_reval_factor,
970 x_active_flag => l_active_flag
971 );
972
973 -- Bug 2480915, start(7) Modified for 2906034
974 OPEN c_get_fa_deprn(l_get_ytd.adjustment_id, l_get_ytd.distribution_id);
975 FETCH c_get_fa_deprn INTO l_fa_deprn_period, l_fa_deprn_ytd, l_fa_deprn_reserve;
976 IF c_get_fa_deprn%NOTFOUND THEN
977 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Record not found in igi_iac_fa_deprn');
978 OPEN c_get_fa_ytd(g_book_type_code,
979 g_asset_id,
980 l_get_ytd.distribution_id);
981 FETCH c_get_fa_ytd INTO l_fa_deprn_ytd;
982 IF c_get_fa_ytd%NOTFOUND THEN
983 l_fa_deprn_ytd := 0;
984 END IF;
985 CLOSE c_get_fa_ytd;
986 END IF;
987 CLOSE c_get_fa_deprn;
988 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'In YTD: YTD deprn: '||l_fa_deprn_ytd);
989
990 IF nvl(l_get_ytd.active_flag,'Y') = 'Y' THEN
991 l_fa_deprn_ytd := 0;
992 END IF;
993 -- insert into igi_iac_fa_deprn with the reinstatement adjustment_id
994 IGI_IAC_FA_DEPRN_PKG.Insert_Row(
995 x_rowid => l_rowid,
996 x_book_type_code => g_book_type_code,
997 x_asset_id => g_asset_id,
998 x_period_counter => p_period_counter,
999 x_adjustment_id => p_adjust_id_reinstate,
1000 x_distribution_id => l_get_ytd.distribution_id,
1001 x_deprn_period => 0,
1002 x_deprn_ytd => l_fa_deprn_ytd,
1003 x_deprn_reserve => 0,
1004 x_active_flag => 'N',
1005 x_mode => 'R'
1006 );
1007 -- Bug 2480915, end(7) Modified for 2906034
1008 END LOOP;
1009
1010 -- update the asset balances table for the asset to reflect the full reinstatement
1011 -- if a row exists for the asset for the current period else create a new row
1012 SELECT count(*)
1013 INTO l_exists
1014 FROM igi_iac_asset_balances
1015 WHERE asset_id = g_asset_id
1016 AND book_type_code = g_book_type_code
1017 AND period_counter = p_period_counter;
1018
1019 -- fetch asset balances for the period prior to retirement
1020 OPEN c_ret_ass_bal(g_Asset_id,
1021 g_book_type_code,
1022 p_effective_retire_period_cnt --l_prev_prd_cnt
1023 );
1024 FETCH c_ret_ass_bal INTO l_ret_ass_bal;
1025 IF c_ret_ass_bal%NOTFOUND THEN
1026 CLOSE c_ret_ass_bal;
1027 RETURN FALSE;
1028 END IF;
1029 CLOSE c_ret_ass_bal;
1030 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Retrieved the asset balance');
1031
1032 -- update the existing asset balances record
1033 l_ab_net_book_value := l_tot_net_book_value;
1034 l_ab_adjusted_cost := l_tot_adjustment_cost;
1035 l_ab_operating_acct := l_tot_operating_acct_net;
1036 l_ab_reval_reserve := l_tot_reval_reserve_net;
1037 l_ab_deprn_amount := l_tot_deprn_period;
1038 l_ab_deprn_reserve := l_tot_deprn_reserve;
1039 l_ab_backlog_deprn_reserve := l_tot_deprn_reserve_backlog;
1040 l_ab_general_fund := l_tot_general_fund_acc;
1041
1042 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'NBV: '||l_ab_net_book_value);
1043 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Adjusted Cost: '||l_ab_adjusted_cost);
1044 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Operating acct: '||l_ab_operating_acct);
1045 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Reval Reserve: '||l_ab_reval_reserve);
1046 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Deprn Amount/Expense: '||l_ab_deprn_amount);
1047 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Deprn Reserve: '||l_ab_deprn_reserve);
1048 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Blog deprn reserve: '||l_ab_backlog_deprn_reserve);
1049 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Gen Fund: '||l_ab_general_fund);
1050
1051 IF (l_exists > 0) THEN
1052 -- update the existing asset balances record
1053 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Updating asset balances');
1054 IGI_IAC_ASSET_BALANCES_PKG.Update_Row(
1055 X_asset_id => g_asset_id,
1056 X_book_type_code => g_book_type_code,
1057 X_period_counter => p_period_counter,
1058 X_net_book_value => l_ab_net_book_value,
1059 X_adjusted_cost => l_ab_adjusted_cost,
1060 X_operating_acct => l_ab_operating_acct,
1061 X_reval_reserve => l_ab_reval_reserve,
1062 X_deprn_amount => l_ab_deprn_amount,
1063 X_deprn_reserve => l_ab_deprn_reserve,
1064 X_backlog_deprn_reserve => l_ab_backlog_deprn_reserve,
1065 X_general_fund => l_ab_general_fund,
1066 X_last_reval_date => l_ret_ass_bal.last_reval_date,
1067 X_current_reval_factor => l_ret_ass_bal.current_reval_factor,
1068 X_cumulative_reval_factor => l_ret_ass_bal.cumulative_reval_factor
1069 ) ;
1070
1071 ELSE
1072 -- insert a new record for the reinstatement period by bringing forward the record previous to the
1073 -- retired with catchup values adjusted for account type RESERVE and GENERAL FUND
1074 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Inserting into asset balances');
1075 IGI_IAC_ASSET_BALANCES_PKG.Insert_Row(
1076 X_rowid => l_rowid,
1077 X_asset_id => g_asset_id,
1078 X_book_type_code => g_book_type_code,
1079 X_period_counter => p_period_counter,
1080 X_net_book_value => l_ab_net_book_value,
1081 X_adjusted_cost => l_ab_adjusted_cost,
1082 X_operating_acct => l_ab_operating_acct,
1083 X_reval_reserve => l_ab_reval_reserve,
1084 X_deprn_amount => l_ab_deprn_amount,
1085 X_deprn_reserve => l_ab_deprn_reserve,
1086 X_backlog_deprn_reserve => l_ab_backlog_deprn_reserve,
1087 X_general_fund => l_ab_general_fund,
1088 X_last_reval_date => l_ret_ass_bal.last_reval_date,
1089 X_current_reval_factor => l_ret_ass_bal.current_reval_factor,
1090 X_cumulative_reval_factor => l_ret_ass_bal.cumulative_reval_factor
1091 ) ;
1092 END IF;
1093 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Finished reinstating asset balances fully');
1094 RETURN TRUE;
1095 EXCEPTION
1096 WHEN e_no_corr_reinstatement THEN
1097 IF c_get_ytd%ISOPEN THEN
1098 CLOSE c_get_ytd;
1099 END IF;
1100 g_message := 'No corresponding reinstatement found for the retirement distribution';
1101 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,g_message);
1102 FA_SRVR_MSG.add_message(
1103 Calling_Fn => g_calling_fn,
1104 Name => 'IGI_IAC_NO_CORR_REIN_DIST_ID'
1105 );
1106 RETURN FALSE;
1107
1108 WHEN e_no_ccid_found THEN
1109 IF c_get_ytd%ISOPEN THEN
1110 CLOSE c_get_ytd;
1111 END IF;
1112
1113 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,g_message);
1114 FA_SRVR_MSG.add_message(
1115 Calling_Fn => g_calling_fn,
1116 Name => 'IGI_IAC_WF_FAILED_CCID'
1117 );
1118 RETURN FALSE;
1119
1120 WHEN others THEN
1121 IF c_get_ytd%ISOPEN THEN
1122 CLOSE c_get_ytd;
1123 END IF;
1124 g_message := 'Error:'||SQLERRM;
1125 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
1126 g_calling_fn1 := g_calling_fn||' : Full';
1127 FA_SRVR_MSG.add_sql_error(
1128 Calling_Fn => g_calling_fn1
1129 );
1130 RETURN FALSE;
1131 END Full_Reinstatement;
1132
1133 -- ===============================================================================
1134 -- FUNCTION Cost Reinstatement :
1135
1136 -- Function to reinstate the asset partially based on Cost
1137 -- 1. This procedure will retrieve the rows just prior to the partial retirement
1138 -- and bring them forward as reinstated with catchup calculated for
1139 -- depreciation reserve and expense.
1140 -- 2. The catchup amount for the catchup journals will be calculated as the difference
1141 -- between the reinstated amount and the latest amount
1142 -- 2. The ytd rows associated with the retirement will be brought forward
1143 -- ===============================================================================
1144 FUNCTION Cost_Reinstatement(
1145 p_adjust_id_reinstate NUMBER,
1146 p_adjust_id_retire NUMBER,
1147 p_trxhdr_id_retire NUMBER,
1148 p_trxhdr_id_reinstate NUMBER,
1149 p_latest_adjust_id NUMBER,
1150 p_sob_id NUMBER,
1151 p_period_counter NUMBER,
1152 p_retirement_type VARCHAR2,
1153 p_effective_retire_period_cnt NUMBER,
1154 p_transaction_run VARCHAR2,
1155 p_event_id NUMBER
1156 )
1157 RETURN BOOLEAN
1158 IS
1159
1160
1161 -- local variables
1162 l_rowid ROWID;
1163
1164 l_path VARCHAR2(150);
1165
1166 l_prev_adj_id NUMBER;
1167 l_rsv_catchup_amt NUMBER := 0;
1168 l_gf_catchup_amt NUMBER := 0;
1169
1170 l_prorate_factor NUMBER := 0;
1171 -- l_prev_prd_cnt NUMBER;
1172 l_de_catchup NUMBER;
1173 l_rsv_catchup NUMBER;
1174 l_latest_dep_exp NUMBER;
1175 l_latest_dep_rsv NUMBER;
1176 l_latest_gen_fund NUMBER;
1177 l_latest_rev_rsv NUMBER;
1178
1179 l_ccid igi_iac_adjustments.code_combination_id%TYPE;
1180 l_reval_rsv_ccid igi_iac_adjustments.code_combination_id%TYPE;
1181 l_elapsed_periods NUMBER;
1182
1183 l_exists NUMBER;
1184 l_ret_ass_bal c_ret_ass_bal%ROWTYPE;
1185
1186 l_adjustment_cost NUMBER;
1187 l_net_book_value NUMBER;
1188 l_reval_reserve_cost NUMBER;
1189 l_reval_reserve_backlog NUMBER;
1190 l_reval_reserve_gen_fund NUMBER;
1191 l_reval_reserve_net NUMBER;
1192 l_operating_acct_cost NUMBER;
1193 l_operating_acct_backlog NUMBER;
1194 l_operating_acct_net NUMBER;
1195 l_operating_acct_ytd NUMBER;
1196 l_deprn_period NUMBER;
1197 l_deprn_ytd NUMBER;
1198 l_deprn_reserve NUMBER;
1199 l_deprn_reserve_backlog NUMBER;
1200 l_general_fund_per NUMBER;
1201 l_general_fund_acc NUMBER;
1202
1203 l_tot_adjustment_cost NUMBER := 0;
1204 l_tot_net_book_value NUMBER := 0;
1205 l_tot_reval_reserve_cost NUMBER := 0;
1206 l_tot_reval_reserve_backlog NUMBER := 0;
1207 l_tot_reval_reserve_gen_fund NUMBER := 0;
1208 l_tot_reval_reserve_net NUMBER := 0;
1209 l_tot_operating_acct_cost NUMBER := 0;
1210 l_tot_operating_acct_backlog NUMBER := 0;
1211 l_tot_operating_acct_net NUMBER := 0;
1212 l_tot_operating_acct_ytd NUMBER := 0;
1213 l_tot_deprn_period NUMBER := 0;
1214 l_tot_deprn_ytd NUMBER := 0;
1215 l_tot_deprn_reserve NUMBER := 0;
1216 l_tot_deprn_reserve_backlog NUMBER := 0;
1217 l_tot_general_fund_per NUMBER := 0;
1218 l_tot_general_fund_acc NUMBER := 0;
1219
1220 l_ab_net_book_value NUMBER;
1221 l_ab_adjusted_cost NUMBER;
1222 l_ab_operating_acct NUMBER;
1223 l_ab_reval_reserve NUMBER;
1224 l_ab_deprn_amount NUMBER;
1225 l_ab_deprn_reserve NUMBER;
1226 l_ab_backlog_deprn_reserve NUMBER;
1227 l_ab_general_fund NUMBER;
1228
1229 l_units_assigned fa_distribution_history.units_assigned%TYPE;
1230 l_fully_reserved NUMBER;
1231
1232 -- bug 2480915 start(8)
1233 l_fa_deprn_ytd igi_iac_fa_deprn.deprn_ytd%TYPE;
1234 l_fa_deprn_period igi_iac_fa_deprn.deprn_period%TYPE;
1235 l_fa_deprn_reserve igi_iac_fa_deprn.deprn_reserve%TYPE;
1236 -- bug 2480915 end(8)
1237 l_ret boolean;
1238 -- exceptions
1239 e_no_ccid_found EXCEPTION;
1240 e_no_cost_prorate EXCEPTION;
1241 BEGIN
1242 l_path := g_path||'Cost_Reinstatement';
1243
1244 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'In reinstatement cost');
1245 -- get the adjustment id and period counter for the transaction previous to retirement
1246 -- OPEN c_trx_prev_ret(p_trxhdr_id_retire);
1247 OPEN c_trx_prev_ret(cp_book_type_code => g_book_type_code,
1248 cp_asset_id => g_asset_id,
1249 cp_trxhdr_id_retire => p_trxhdr_id_retire);
1250 FETCH c_trx_prev_ret INTO l_prev_adj_id;
1251 IF c_trx_prev_ret%NOTFOUND THEN
1252 CLOSE c_trx_prev_ret;
1253 RETURN FALSE;
1254 END IF;
1255 CLOSE c_trx_prev_ret;
1256 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Adjustment id previous to retirement: '||l_prev_adj_id);
1257
1258 -- get the cost retirement factor
1259 IF NOT igi_iac_common_utils.get_cost_retirement_factor(g_book_type_code,
1260 g_asset_id,
1261 g_retirement_id,
1262 l_prorate_factor)
1263 THEN
1264 RAISE e_no_cost_prorate;
1265 END IF;
1266 -- calculate the reinstatement prorate factor from the retirement
1267 -- prorate factor
1268 --l_prorate_factor := 1/(1 - l_prorate_factor);
1269 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Cost prorate factor: '||l_prorate_factor);
1270
1271 -- calculate the number of periods that have elapsed between the transaction
1272 -- previous to the retirement and reinstatement of the asset
1273 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Reinstatement period counter: '|| p_period_counter);
1274 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Retirement period cntr: '|| p_effective_retire_period_cnt);
1275 -- l_elapsed_periods := p_period_counter - l_prev_prd_cnt;
1276 l_elapsed_periods := p_period_counter - p_effective_retire_period_cnt;
1277 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Elapsed periods: '|| l_elapsed_periods);
1278
1279 -- find out NOCOPY if the asset has been fully reserved prior to retirement
1280 OPEN c_fully_reserved(g_asset_id, g_book_type_code);
1281 FETCH c_fully_reserved INTO l_fully_reserved;
1282 IF c_fully_reserved%NOTFOUND THEN
1283 CLOSE c_fully_reserved;
1284 RETURN FALSE;
1285 END IF;
1286 CLOSE c_fully_reserved;
1287 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Asset fully reserved period counter: '||l_fully_reserved);
1288
1289 -- get all the previous balances to reinstate
1290 FOR l_det_bal IN c_det_bal(l_prev_adj_id) LOOP
1291 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'getting all previous row for reinstatement');
1292 -- calculate catchup amount if second transaction
1293 -- initialise to zero for each distribution_id
1294 l_rsv_catchup_amt := 0;
1295 l_gf_catchup_amt := 0;
1296 l_general_fund_acc:=0;
1297
1298 IF (l_fully_reserved <= 0) THEN
1299 If l_det_bal.adjustment_cost >= 0 THEN
1300 l_gf_catchup_amt := l_det_bal.general_fund_acc + (l_elapsed_periods * l_det_bal.deprn_period * l_prorate_factor);
1301 l_ret:= igi_iac_common_utils.iac_round(l_gf_catchup_amt,g_book_type_code) ;
1302 l_general_fund_acc := l_gf_catchup_amt;
1303 END IF;
1304 ELSE
1305 l_gf_catchup_amt := 0;
1306 END IF;
1307
1308 IF (p_transaction_run = 'SECOND') THEN
1309 -- calculate the catchup for depreciation reserve
1310 l_rsv_catchup_amt := l_elapsed_periods * l_det_bal.deprn_period;
1311
1312 If l_det_bal.adjustment_cost >= 0 THEN
1313 l_gf_catchup_amt := l_elapsed_periods * l_det_bal.deprn_period ;
1314 l_ret:= igi_iac_common_utils.iac_round(l_gf_catchup_amt,g_book_type_code) ;
1315 l_general_fund_acc:= l_det_bal.general_fund_acc + l_gf_catchup_amt;
1316 END IF;
1317 ELSE
1318 l_rsv_catchup_amt := 0;
1319 END IF;
1320
1321
1322
1323 -- If asset is fully reserved then catchup is 0
1324 IF (l_fully_reserved > 0) THEN
1325 l_rsv_catchup_amt := 0;
1326 END IF;
1327 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Reinstated Depreciation expense: '||l_det_bal.deprn_period);
1328 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Depreciation reserve catchup amount: '||l_rsv_catchup_amt);
1329
1330 -- get account values
1331 -- add the catchup amounts as well
1332 l_adjustment_cost := l_det_bal.adjustment_cost;
1333 l_reval_reserve_cost := l_det_bal.reval_reserve_cost;
1334 l_reval_reserve_backlog := l_det_bal.reval_reserve_backlog;
1335 l_operating_acct_cost := l_det_bal.operating_acct_cost;
1336 l_operating_acct_backlog := l_det_bal.operating_acct_backlog;
1337 l_operating_acct_ytd := l_det_bal.operating_acct_ytd;
1338 l_deprn_period := l_det_bal.deprn_period;
1339 l_deprn_reserve_backlog := l_det_bal.deprn_reserve_backlog;
1340
1341 l_deprn_ytd := l_det_bal.deprn_ytd +l_rsv_catchup_amt;
1342 l_deprn_reserve := l_det_bal.deprn_reserve + l_rsv_catchup_amt;
1343 IF (l_det_bal.adjustment_cost > 0) THEN
1344 l_general_fund_per := l_deprn_period;
1345 l_general_fund_acc := l_general_fund_acc;
1346 l_reval_reserve_gen_fund := l_general_fund_acc;
1347 l_reval_reserve_net := l_reval_reserve_cost - ( l_reval_reserve_backlog + l_reval_reserve_gen_fund);
1348 ELSE
1349 l_general_fund_per := l_det_bal.general_fund_per;
1350 l_general_fund_acc := l_det_bal.general_fund_acc;
1351 l_reval_reserve_gen_fund := l_det_bal.reval_reserve_gen_fund;
1352 l_reval_reserve_net := l_det_bal.reval_reserve_net;
1353 END IF;
1354 -- calculate these
1355 l_operating_acct_net := l_operating_acct_cost - l_operating_acct_backlog;
1356 l_net_book_value := l_adjustment_cost - (l_deprn_reserve + l_deprn_reserve_backlog);
1357
1358 -- bug 2480915 start(12) Modified for Bug 2906034
1359 OPEN c_get_fa_deprn(l_prev_adj_id, l_det_bal.distribution_id);
1360 FETCH c_get_fa_deprn INTO l_fa_deprn_period, l_fa_deprn_ytd, l_fa_deprn_reserve;
1361 IF c_get_fa_deprn%NOTFOUND THEN
1362 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Record not found in igi_iac_fa_deprn');
1363 OPEN c_get_fa_det(g_book_type_code,
1364 g_asset_id,
1365 l_det_bal.distribution_id,
1366 l_det_bal.period_counter);
1367 FETCH c_get_fa_det INTO l_fa_deprn_period, l_fa_deprn_reserve;
1368 CLOSE c_get_fa_det;
1369
1370 OPEN c_get_fa_ytd(g_book_type_code,
1371 g_asset_id,
1372 l_det_bal.distribution_id);
1373 FETCH c_get_fa_ytd INTO l_fa_deprn_ytd;
1374 IF c_get_fa_ytd%NOTFOUND THEN
1375 l_fa_deprn_ytd := 0;
1376 END IF;
1377 CLOSE c_get_fa_ytd;
1378 ELSE
1379 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Record found in igi_iac_fa_deprn');
1380 IF l_fully_reserved > 0 THEN
1381 l_fa_deprn_period := 0;
1382 END IF;
1383 IF (p_transaction_run = 'SECOND') THEN
1384 l_fa_deprn_ytd := l_fa_deprn_ytd + (l_elapsed_periods*l_fa_deprn_period);
1385 l_fa_deprn_reserve := l_fa_deprn_reserve + (l_elapsed_periods*l_fa_deprn_period);
1386 END IF;
1387 END IF;
1388 CLOSE c_get_fa_deprn;
1389
1390 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'FA Depreciation amount: '||l_fa_deprn_period);
1391 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Detail level FA deprn rsv: '||l_fa_deprn_reserve);
1392 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Detail level FA deprn YTD: '||l_fa_deprn_ytd);
1393 -- bug 2480915 end(12) Modified for Bug 2906034
1394
1395 -- keep a running total of the different account amounts after they have
1396 -- been prorated
1397 l_tot_adjustment_cost := l_tot_adjustment_cost + l_adjustment_cost;
1398 l_tot_net_book_value := l_tot_net_book_value + l_net_book_value;
1399 l_tot_reval_reserve_cost := l_tot_reval_reserve_cost + l_reval_reserve_cost;
1400 l_tot_reval_reserve_backlog := l_tot_reval_reserve_backlog + l_reval_reserve_backlog;
1401 l_tot_reval_reserve_gen_fund := l_tot_reval_reserve_gen_fund + l_reval_reserve_gen_fund;
1402 l_tot_reval_reserve_net := l_tot_reval_reserve_net + l_reval_reserve_net;
1403 l_tot_operating_acct_cost := l_tot_operating_acct_cost + l_operating_acct_cost;
1404 l_tot_operating_acct_backlog := l_tot_operating_acct_backlog + l_operating_acct_backlog;
1405 l_tot_operating_acct_net := l_tot_operating_acct_net + l_operating_acct_net;
1406 l_tot_operating_acct_ytd := l_tot_operating_acct_ytd + l_operating_acct_ytd;
1407 l_tot_deprn_period := l_tot_deprn_period + l_deprn_period;
1408 l_tot_deprn_ytd := l_tot_deprn_ytd + l_deprn_ytd;
1409 l_tot_deprn_reserve := l_tot_deprn_reserve + l_deprn_reserve;
1410 l_tot_deprn_reserve_backlog := l_tot_deprn_reserve_backlog + l_deprn_reserve_backlog;
1411 l_tot_general_fund_per := l_tot_general_fund_per + l_general_fund_per;
1412 l_tot_general_fund_acc := l_tot_general_fund_acc + l_general_fund_acc;
1413
1414 -- insert into igi_iac_det_balances with reinstatement adjustment_id
1415 IGI_IAC_DET_BALANCES_PKG.Insert_Row(
1416 x_rowid => l_rowid,
1417 x_adjustment_id => p_adjust_id_reinstate,
1418 x_asset_id => g_asset_id,
1419 x_book_type_code => g_book_type_code,
1420 x_distribution_id => l_det_bal.distribution_id,
1421 x_period_counter => p_period_counter,
1422 x_adjustment_cost => l_adjustment_cost,
1423 x_net_book_value => l_net_book_value,
1424 x_reval_reserve_cost => l_reval_reserve_cost,
1425 x_reval_reserve_backlog => l_reval_reserve_backlog,
1426 x_reval_reserve_gen_fund => l_reval_reserve_gen_fund,
1427 x_reval_reserve_net => l_reval_reserve_net,
1428 x_operating_acct_cost => l_operating_acct_cost,
1429 x_operating_acct_backlog => l_operating_acct_backlog,
1430 x_operating_acct_net => l_operating_acct_net,
1431 x_operating_acct_ytd => l_operating_acct_ytd,
1432 x_deprn_period => l_deprn_period,
1433 x_deprn_ytd => l_deprn_ytd,
1434 x_deprn_reserve => l_deprn_reserve,
1435 x_deprn_reserve_backlog => l_deprn_reserve_backlog,
1436 x_general_fund_per => l_general_fund_per,
1437 x_general_fund_acc => l_general_fund_acc,
1438 x_last_reval_date => l_det_bal.last_reval_date,
1439 x_current_reval_factor => l_det_bal.current_reval_factor,
1440 x_cumulative_reval_factor => l_det_bal.cumulative_reval_factor,
1441 x_active_flag => null,
1442 x_mode => 'R'
1443 );
1444
1445 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Inserted into det balances dist id '||l_det_bal.distribution_id);
1446 -- Bug 2480915, start(13)
1447 -- insert into igi_iac_fa_deprn with the reinstatement adjustment_id
1448 IGI_IAC_FA_DEPRN_PKG.Insert_Row(
1449 x_rowid => l_rowid,
1450 x_book_type_code => g_book_type_code,
1451 x_asset_id => g_asset_id,
1452 x_period_counter => p_period_counter,
1453 x_adjustment_id => p_adjust_id_reinstate,
1454 x_distribution_id => l_det_bal.distribution_id,
1455 x_deprn_period => l_fa_deprn_period,
1456 x_deprn_ytd => l_fa_deprn_ytd,
1457 x_deprn_reserve => l_fa_deprn_reserve,
1458 x_active_flag => null,
1459 x_mode => 'R'
1460 );
1461 -- Bug 2480915, end(13)
1462 -- create the catchup adjustment entries for the second transaction only
1463 IF (p_transaction_run = 'SECOND') THEN
1464 -- get the number of units assigned to the distribution
1465 SELECT units_assigned
1466 INTO l_units_assigned
1467 FROM fa_distribution_history
1468 WHERE distribution_id = l_det_bal.distribution_id;
1469 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Units assigned to distribution '||l_units_assigned);
1470
1471 -- create the catchup journals only if the catchup amounts are greater than zero,
1472 -- which will be only if the number of elapsed periods is greater than zero
1473 IF (l_elapsed_periods > 0) THEN
1474 -- get the latest account amounts for calculating adjustment catchups
1475 OPEN c_deprn_expense(p_latest_adjust_id, l_det_bal.distribution_id);
1476 FETCH c_deprn_expense INTO l_latest_dep_exp, l_latest_dep_rsv,
1477 l_latest_gen_fund, l_latest_rev_rsv;
1478 IF c_deprn_expense%NOTFOUND THEN
1479 CLOSE c_deprn_expense;
1480 RETURN FALSE;
1481 END IF;
1482 CLOSE c_deprn_expense;
1483 -- get the ccid for the account type Depreciation Expense
1484 IF NOT igi_iac_common_utils.get_account_ccid(g_book_type_code,
1485 g_asset_id,
1486 l_det_bal.distribution_id,
1487 'DEPRN_EXPENSE_ACCT',
1488 p_trxhdr_id_reinstate,
1489 'RETIREMENT',
1490 l_ccid)
1491 THEN
1492 --RETURN false;
1493 g_message := 'No account code combination found for Depreciation Expense';
1494 RAISE e_no_ccid_found;
1495 END IF;
1496 -- depreciation expense catchup
1497 l_rsv_catchup := (l_deprn_period - l_latest_dep_exp)*l_elapsed_periods;
1498
1499 -- insert into igi_iac_adjustments
1500 IGI_IAC_ADJUSTMENTS_PKG.Insert_Row(
1501 x_rowid => l_rowid,
1502 x_adjustment_id => p_adjust_id_reinstate,
1503 x_book_type_code => g_book_type_code,
1504 x_code_combination_id => l_ccid,
1505 x_set_of_books_id => p_sob_id,
1506 x_dr_cr_flag => 'DR',
1507 x_amount => l_rsv_catchup,
1508 x_adjustment_type => 'EXPENSE',
1509 x_adjustment_offset_type => 'RESERVE', --Null,
1510 x_report_ccid => Null,
1511 x_transfer_to_gl_flag => 'Y',
1512 x_units_assigned => l_units_assigned,
1513 x_asset_id => g_asset_id,
1514 x_distribution_id => l_det_bal.distribution_id,
1515 x_period_counter => p_period_counter,
1516 x_mode => 'R',
1517 x_event_id => p_event_id
1518 );
1519 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Done Expense: '||l_rsv_catchup);
1520
1521 -- insert RESERVE journal into igi_iac_adjustments with the reserve catchup amount
1522 -- calculate the catchup amount
1523 --l_de_catchup := l_deprn_reserve - l_latest_dep_rsv;
1524 -- get the ccid for the account type
1525 IF NOT igi_iac_common_utils.get_account_ccid(g_book_type_code,
1526 g_asset_id,
1527 l_det_bal.distribution_id,
1528 'DEPRN_RESERVE_ACCT',
1529 p_trxhdr_id_reinstate,
1530 'RETIREMENT',
1531 l_ccid)
1532 THEN
1533 --RETURN false;
1534 g_message := 'No account code combination found for Accumulated Depreciation';
1535 RAISE e_no_ccid_found;
1536 END IF;
1537
1538 -- insert into igi_iac_adjustments
1539 IGI_IAC_ADJUSTMENTS_PKG.Insert_Row(
1540 x_rowid => l_rowid,
1541 x_adjustment_id => p_adjust_id_reinstate,
1542 x_book_type_code => g_book_type_code,
1543 x_code_combination_id => l_ccid,
1544 x_set_of_books_id => p_sob_id,
1545 x_dr_cr_flag => 'CR',
1546 x_amount => l_rsv_catchup, --l_rsv_catchup_amt,
1547 x_adjustment_type => 'RESERVE',
1548 x_adjustment_offset_type => 'EXPENSE', -- Null,
1549 x_report_ccid => Null,
1550 x_transfer_to_gl_flag => 'Y',
1551 x_units_assigned => l_units_assigned,
1552 x_asset_id => g_asset_id,
1553 x_distribution_id => l_det_bal.distribution_id,
1554 x_period_counter => p_period_counter,
1555 x_mode => 'R',
1556 x_event_id => p_event_id
1557 );
1558 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Done Reserve: '|| l_rsv_catchup);
1559
1560 -- insert GENERAL FUND a REVAL RESERVE journal into igi_iac_adjustments with the catchup amount
1561 -- only if adjustment amount is greater than zero
1562 IF (l_det_bal.adjustment_cost > 0) THEN
1563 -- get the ccid for the account type REVAL RESERVE
1564 IF NOT igi_iac_common_utils.get_account_ccid(g_book_type_code,
1565 g_asset_id,
1566 l_det_bal.distribution_id,
1567 'REVAL_RESERVE_ACCT',
1568 p_trxhdr_id_reinstate,
1569 'RETIREMENT',
1570 l_ccid)
1571 THEN
1572 --RETURN false;
1573 g_message := 'No account code combination found for Revaluation Reserve';
1574 RAISE e_no_ccid_found;
1575 END IF;
1576
1577 l_reval_rsv_ccid := l_ccid;
1578
1579 -- insert into igi_iac_adjustments
1580 IGI_IAC_ADJUSTMENTS_PKG.Insert_Row(
1581 x_rowid => l_rowid,
1582 x_adjustment_id => p_adjust_id_reinstate,
1583 x_book_type_code => g_book_type_code,
1584 x_code_combination_id => l_ccid,
1585 x_set_of_books_id => p_sob_id,
1586 x_dr_cr_flag => 'DR',
1587 x_amount => l_rsv_catchup,
1588 x_adjustment_type => 'REVAL RESERVE',
1589 x_adjustment_offset_type => 'GENERAL FUND', --Null,
1590 x_report_ccid => Null,
1591 x_transfer_to_gl_flag => 'Y',
1592 x_units_assigned => l_units_assigned,
1593 x_asset_id => g_asset_id,
1594 x_distribution_id => l_det_bal.distribution_id,
1595 x_period_counter => p_period_counter,
1596 x_mode => 'R',
1597 x_event_id => p_event_id
1598 );
1599 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Done Reval Reserve: '||l_rsv_catchup);
1600
1601 -- get the ccid for the account type General Fund
1602 IF NOT igi_iac_common_utils.get_account_ccid(g_book_type_code,
1603 g_asset_id,
1604 l_det_bal.distribution_id,
1605 'GENERAL_FUND_ACCT',
1606 p_trxhdr_id_reinstate,
1607 'RETIREMENT',
1608 l_ccid)
1609 THEN
1610 --RETURN false;
1611 g_message := 'No account code combination found for General Fund';
1612 RAISE e_no_ccid_found;
1613 END IF;
1614 -- insert into igi_iac_adjustments
1615 IGI_IAC_ADJUSTMENTS_PKG.Insert_Row(
1616 x_rowid => l_rowid,
1617 x_adjustment_id => p_adjust_id_reinstate,
1618 x_book_type_code => g_book_type_code,
1619 x_code_combination_id => l_ccid,
1620 x_set_of_books_id => p_sob_id,
1621 x_dr_cr_flag => 'CR',
1622 x_amount => l_rsv_catchup,
1623 x_adjustment_type => 'GENERAL FUND',
1624 x_adjustment_offset_type => 'REVAL RESERVE', -- Null,
1625 x_report_ccid => l_reval_rsv_ccid, -- Null,
1626 x_transfer_to_gl_flag => 'Y',
1627 x_units_assigned => l_units_assigned,
1628 x_asset_id => g_asset_id,
1629 x_distribution_id => l_det_bal.distribution_id,
1630 x_period_counter => p_period_counter,
1631 x_mode => 'R',
1632 x_event_id => p_event_id
1633 );
1634 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Done General Fund: '||l_rsv_catchup);
1635
1636 END IF; -- adjustment cost > 0
1637 END IF; -- elapsed periods > 0
1638 END IF; -- second transaction
1639 END LOOP;
1640
1641 -- bring the YTD rows associated to the retirement over with the
1642 -- reinstatement adjustment id and current period counter
1643 FOR l_get_ytd IN c_get_ytd(cp_adjustment_id => l_prev_adj_id,
1644 cp_asset_id => g_asset_id,
1645 cp_book_type_code => g_book_type_code) LOOP
1646 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'In YTD: dist id: '||l_get_ytd.distribution_id);
1647 -- insert into igi_iac_det_balances with reinstatement adjustment_id
1648 IGI_IAC_DET_BALANCES_PKG.Insert_Row(
1649 x_rowid => l_rowid,
1650 x_adjustment_id => p_adjust_id_reinstate,
1651 x_asset_id => g_asset_id,
1652 x_book_type_code => g_book_type_code,
1653 x_distribution_id => l_get_ytd.distribution_id,
1654 x_period_counter => p_period_counter,
1655 x_adjustment_cost => l_get_ytd.adjustment_cost,
1656 x_net_book_value => l_get_ytd.net_book_value,
1657 x_reval_reserve_cost => l_get_ytd.reval_reserve_cost,
1658 x_reval_reserve_backlog => l_get_ytd.reval_reserve_backlog,
1659 x_reval_reserve_gen_fund => l_get_ytd.reval_reserve_gen_fund,
1660 x_reval_reserve_net => l_get_ytd.reval_reserve_net,
1661 x_operating_acct_cost => l_get_ytd.operating_acct_cost,
1662 x_operating_acct_backlog => l_get_ytd.operating_acct_backlog,
1663 x_operating_acct_net => l_get_ytd.operating_acct_net,
1664 x_operating_acct_ytd => l_get_ytd.operating_acct_ytd,
1665 x_deprn_period => l_get_ytd.deprn_period,
1666 x_deprn_ytd => l_get_ytd.deprn_ytd,
1667 x_deprn_reserve => l_get_ytd.deprn_reserve,
1668 x_deprn_reserve_backlog => l_get_ytd.deprn_reserve_backlog,
1669 x_general_fund_per => l_get_ytd.general_fund_per,
1670 x_general_fund_acc => l_get_ytd.general_fund_acc,
1671 x_last_reval_date => l_get_ytd.last_reval_date,
1672 x_current_reval_factor => l_get_ytd.current_reval_factor,
1673 x_cumulative_reval_factor => l_get_ytd.cumulative_reval_factor,
1674 x_active_flag => l_get_ytd.active_flag
1675 );
1676
1677 -- Bug 2480915, start(14)
1678 OPEN c_get_fa_deprn(l_get_ytd.adjustment_id, l_get_ytd.distribution_id);
1679 FETCH c_get_fa_deprn INTO l_fa_deprn_period, l_fa_deprn_ytd, l_fa_deprn_reserve;
1680 IF c_get_fa_deprn%NOTFOUND THEN
1681 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Record not found in igi_iac_fa_deprn');
1682 OPEN c_get_fa_ytd(g_book_type_code,
1683 g_asset_id,
1684 l_get_ytd.distribution_id);
1685 FETCH c_get_fa_ytd INTO l_fa_deprn_ytd;
1686 IF c_get_fa_ytd%NOTFOUND THEN
1687 l_fa_deprn_ytd := 0;
1688 END IF;
1689 CLOSE c_get_fa_ytd;
1690 END IF;
1691 CLOSE c_get_fa_deprn;
1692 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'In YTD: YTD deprn: '||l_fa_deprn_ytd);
1693
1694 -- insert into igi_iac_fa_deprn with the reinstatement adjustment_id
1695 IGI_IAC_FA_DEPRN_PKG.Insert_Row(
1696 x_rowid => l_rowid,
1697 x_book_type_code => g_book_type_code,
1698 x_asset_id => g_asset_id,
1699 x_period_counter => p_period_counter,
1700 x_adjustment_id => p_adjust_id_reinstate,
1701 x_distribution_id => l_get_ytd.distribution_id,
1702 x_deprn_period => 0,
1703 x_deprn_ytd => l_fa_deprn_ytd,
1704 x_deprn_reserve => 0,
1705 x_active_flag => 'N',
1706 x_mode => 'R'
1707 );
1708 -- Bug 2480915, end(14)
1709 END LOOP;
1710 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Get the asset balance for the period counter: '||p_effective_retire_period_cnt);
1711 -- update the asset balances table for the asset to reflect the full reinstatement
1712 -- fetch asset balances for the period prior to retirement
1713
1714 -- if a row exists for the asset for the current period else create a new row
1715 SELECT count(*)
1716 INTO l_exists
1717 FROM igi_iac_asset_balances
1718 WHERE asset_id = g_asset_id
1719 AND book_type_code = g_book_type_code
1720 AND period_counter = p_period_counter;
1721
1722 -- fetch asset balances for the period prior to retirement
1723 OPEN c_ret_ass_bal(g_Asset_id,
1724 g_book_type_code,
1725 p_effective_retire_period_cnt --l_prev_prd_cnt
1726 );
1727 FETCH c_ret_ass_bal INTO l_ret_ass_bal;
1728 IF c_ret_ass_bal%NOTFOUND THEN
1729 CLOSE c_ret_ass_bal;
1730 RETURN FALSE;
1731 END IF;
1732 CLOSE c_ret_ass_bal;
1733 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Retrieved the asset balance');
1734
1735 -- update the existing asset balances record
1736 l_ab_net_book_value := l_tot_net_book_value;
1737 l_ab_adjusted_cost := l_tot_adjustment_cost;
1738 l_ab_operating_acct := l_tot_operating_acct_net;
1739 l_ab_reval_reserve := l_tot_reval_reserve_net;
1740 l_ab_deprn_amount := l_tot_deprn_period;
1741 l_ab_deprn_reserve := l_tot_deprn_reserve;
1742 l_ab_backlog_deprn_reserve := l_tot_deprn_reserve_backlog;
1743 l_ab_general_fund := l_tot_general_fund_acc;
1744
1745 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'NBV: '||l_ab_net_book_value);
1746 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Adjusted Cost: '||l_ab_adjusted_cost);
1747 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Operating acct: '||l_ab_operating_acct);
1748 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Reval Reserve: '||l_ab_reval_reserve);
1749 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Deprn Amount/Expense: '||l_ab_deprn_amount);
1750 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Deprn Reserve: '||l_ab_deprn_reserve);
1751 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Blog deprn reserve: '||l_ab_backlog_deprn_reserve);
1752 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Gen Fund: '||l_ab_general_fund);
1753
1754 IF (l_exists > 0) THEN
1755 -- update the asset balances to reflect the reinstatement
1756 IGI_IAC_ASSET_BALANCES_PKG.Update_Row(
1757 X_asset_id => g_asset_id,
1758 X_book_type_code => g_book_type_code,
1759 X_period_counter => p_period_counter,
1760 X_net_book_value => l_ab_net_book_value,
1761 X_adjusted_cost => l_ab_adjusted_cost,
1762 X_operating_acct => l_ab_operating_acct,
1763 X_reval_reserve => l_ab_reval_reserve,
1764 X_deprn_amount => l_ab_deprn_amount,
1765 X_deprn_reserve => l_ab_deprn_reserve,
1766 X_backlog_deprn_reserve => l_ab_backlog_deprn_reserve,
1767 X_general_fund => l_ab_general_fund,
1768 X_last_reval_date => l_ret_ass_bal.last_reval_date,
1769 X_current_reval_factor => l_ret_ass_bal.current_reval_factor,
1770 X_cumulative_reval_factor => l_ret_ass_bal.cumulative_reval_factor
1771 ) ;
1772 ELSE
1773 -- insert a row for the current period counter
1774
1775 IGI_IAC_ASSET_BALANCES_PKG.Insert_Row(
1776 X_rowid => l_rowid,
1777 X_asset_id => g_asset_id,
1778 X_book_type_code => g_book_type_code,
1779 X_period_counter => p_period_counter,
1780 X_net_book_value => l_ab_net_book_value,
1781 X_adjusted_cost => l_ab_adjusted_cost,
1782 X_operating_acct => l_ab_operating_acct,
1783 X_reval_reserve => l_ab_reval_reserve,
1784 X_deprn_amount => l_ab_deprn_amount,
1785 X_deprn_reserve => l_ab_deprn_reserve,
1786 X_backlog_deprn_reserve => l_ab_backlog_deprn_reserve,
1787 X_general_fund => l_ab_general_fund,
1788 X_last_reval_date => l_ret_ass_bal.last_reval_date,
1789 X_current_reval_factor => l_ret_ass_bal.current_reval_factor,
1790 X_cumulative_reval_factor => l_ret_ass_bal.cumulative_reval_factor
1791 ) ;
1792 END IF;
1793 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'finish asset balance updates');
1794
1795 RETURN TRUE;
1796 EXCEPTION
1797 WHEN e_no_cost_prorate THEN
1798 g_message := 'Could not get a cost prorate factor';
1799 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,g_message);
1800 FA_SRVR_MSG.add_message(
1801 Calling_Fn => g_calling_fn,
1802 Name => 'IGI_IAC_NO_REINS_PRORATE'
1803 );
1804 RETURN FALSE;
1805
1806 WHEN e_no_ccid_found THEN
1807 IF c_get_ytd%ISOPEN THEN
1808 CLOSE c_get_ytd;
1809 END IF;
1810
1811 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,g_message);
1812 FA_SRVR_MSG.add_message(
1813 Calling_Fn => g_calling_fn,
1814 Name => 'IGI_IAC_WF_FAILED_CCID'
1815 );
1816 RETURN FALSE;
1817
1818 WHEN others THEN
1819 IF c_get_ytd%ISOPEN THEN
1820 CLOSE c_get_ytd;
1821 END IF;
1822 g_message := 'Error:'||SQLERRM;
1823 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
1824
1825 g_calling_fn1 := g_calling_fn||' : Cost';
1826 FA_SRVR_MSG.add_sql_error(
1827 Calling_Fn => g_calling_fn1
1828 );
1829 RETURN FALSE;
1830 END Cost_Reinstatement;
1831
1832
1833 -- ======================================================================================
1834 -- FUNCTION Unit_Reinstatement :
1835
1836 -- Function to reinstate the asset partially based on Units
1837
1838 -- Note: This procedure needs to consider following cases:
1839 -- 1. Distribution is fully retired, then reinstate the row previous to retirement
1840 -- 2. Distribution is partially retired, then reinstate the row by prorating the active
1841 -- transaction with the reinstatement proration factor
1842 -- 3. Distribution was never retired, so need not be reinstated, but bring the row forward
1843 -- with the reinstatement adjustment id
1844 -- 4. Distribution A was retired fully in partial retirement 1, then Distribution B
1845 -- is fully retired in partial retirement 2. On reinstatement, only Distribution B
1846 -- must be reinstated and ytd figures for Distribution A must be brought forward
1847 -- ======================================================================================
1848
1849 FUNCTION Unit_Reinstatement(
1850 p_adjust_id_reinstate NUMBER,
1851 p_trxhdr_id_retire NUMBER,
1852 p_trxhdr_id_reinstate NUMBER,
1853 p_latest_adjust_id NUMBER,
1854 p_sob_id NUMBER,
1855 p_period_counter NUMBER,
1856 p_retirement_type VARCHAR2,
1857 p_effective_retire_period_cnt NUMBER,
1858 p_transaction_run VARCHAR2,
1859 p_event_id NUMBER
1860
1861 )
1862 RETURN BOOLEAN
1863 IS
1864
1865 -- cursor to get all distribution_ids
1866 -- from igi_iac_det_balances for an
1867 -- adjustment_id and distribution id
1868 CURSOR c_dist_det_bal(n_adjust_id NUMBER,
1869 n_dist_id NUMBER,
1870 n_retirement_id NUMBER)
1871 IS
1872 SELECT iidb.adjustment_id,
1873 iidb.distribution_id,
1874 iidb.adjustment_cost,
1875 iidb.net_book_value,
1876 iidb.reval_reserve_cost,
1877 iidb.reval_reserve_backlog,
1878 iidb.reval_reserve_gen_fund,
1879 iidb.reval_reserve_net,
1880 iidb.operating_acct_cost,
1881 iidb.operating_acct_backlog,
1882 iidb.operating_acct_net,
1883 iidb.operating_acct_ytd,
1884 iidb.deprn_period,
1885 iidb.deprn_ytd,
1886 iidb.deprn_reserve,
1887 iidb.deprn_reserve_backlog,
1888 iidb.general_fund_per,
1889 iidb.general_fund_acc,
1890 iidb.last_reval_date,
1891 iidb.current_reval_factor,
1892 iidb.cumulative_reval_factor,
1893 iidb.active_flag
1894 FROM igi_iac_det_balances iidb,
1895 fa_distribution_history fdh
1896 WHERE iidb.adjustment_id = n_adjust_id
1897 AND iidb.distribution_id = n_dist_id
1898 AND fdh.distribution_id = iidb.distribution_id
1899 AND fdh.retirement_id = n_retirement_id;
1900
1901 -- bug 2485778, start 1
1902 -- cursor that will retrieve all
1903 -- the distributions that had been retired
1904 -- and is now being reinstated, thes will then be brought
1905 -- forward as YTD rows in addition to the YTD rows
1906 -- from the previous transaction
1907
1908 CURSOR c_ret_ytd(n_dist_id NUMBER)
1909 IS
1910 SELECT iidb.distribution_id,
1911 iidb.adjustment_id,
1912 iidb.operating_acct_ytd,
1913 iidb.deprn_ytd,
1914 iidb.last_reval_date,
1915 iidb.current_reval_factor,
1916 iidb.cumulative_reval_factor
1917 FROM igi_iac_det_balances iidb
1918 WHERE iidb.distribution_id = n_dist_id
1919 AND iidb.period_counter = (SELECT max(idb1.period_counter)
1920 FROM igi_iac_det_balances idb1
1921 WHERE idb1.distribution_id = n_dist_id
1922 AND active_flag IS NULL);
1923 -- bug 2485778, end 1
1924
1925 -- local variables
1926 l_rowid ROWID;
1927 l_path VARCHAR2(150);
1928
1929 l_units_ren fa_distribution_history.units_assigned%TYPE;
1930 l_units_ret fa_distribution_history.units_assigned%TYPE;
1931 l_prev_adj_id igi_iac_transaction_headers.adjustment_id%TYPE;
1932 l_prev_dist_id igi_iac_det_balances.distribution_id%TYPE;
1933
1934 l_retire_effect NUMBER := 0;
1935 l_prorate_factor NUMBER;
1936 l_rsv_catchup NUMBER;
1937
1938 l_latest_dep_exp NUMBER;
1939 l_latest_dep_rsv NUMBER;
1940 l_latest_gen_fund NUMBER;
1941 l_latest_rev_rsv NUMBER;
1942 l_de_catchup NUMBER;
1943
1944 l_units_before NUMBER;
1945 l_units_after NUMBER;
1946 l_elapsed_periods NUMBER := 0;
1947 l_ret_type VARCHAR2(4);
1948
1949 l_ccid igi_iac_adjustments.code_combination_id%TYPE;
1950 l_reval_rsv_ccid igi_iac_adjustments.code_combination_id%TYPE;
1951
1952 l_rsv_catchup_amt igi_iac_det_balances.deprn_period%TYPE;
1953 l_gf_catchup_amt igi_iac_det_balances.general_fund_acc%TYPE;
1954 l_adjustment_cost NUMBER;
1955 l_net_book_value NUMBER;
1956 l_reval_reserve_cost NUMBER;
1957 l_reval_reserve_backlog NUMBER;
1958 l_reval_reserve_gen_fund NUMBER;
1959 l_reval_reserve_net NUMBER;
1960 l_operating_acct_cost NUMBER;
1961 l_operating_acct_backlog NUMBER;
1962 l_operating_acct_net NUMBER;
1963 l_operating_acct_ytd NUMBER;
1964 l_deprn_period NUMBER;
1965 l_deprn_ytd NUMBER;
1966 l_deprn_reserve NUMBER;
1967 l_deprn_reserve_backlog NUMBER;
1968 l_general_fund_per NUMBER;
1969 l_general_fund_acc NUMBER;
1970
1971 l_tot_adjustment_cost NUMBER := 0;
1972 l_tot_net_book_value NUMBER := 0;
1973 l_tot_reval_reserve_cost NUMBER := 0;
1974 l_tot_reval_reserve_backlog NUMBER := 0;
1975 l_tot_reval_reserve_gen_fund NUMBER := 0;
1976 l_tot_reval_reserve_net NUMBER := 0;
1977 l_tot_operating_acct_cost NUMBER := 0;
1978 l_tot_operating_acct_backlog NUMBER := 0;
1979 l_tot_operating_acct_net NUMBER := 0;
1980 l_tot_operating_acct_ytd NUMBER := 0;
1981 l_tot_deprn_period NUMBER := 0;
1982 l_tot_deprn_ytd NUMBER := 0;
1983 l_tot_deprn_reserve NUMBER := 0;
1984 l_tot_deprn_reserve_backlog NUMBER := 0;
1985 l_tot_general_fund_per NUMBER := 0;
1986 l_tot_general_fund_acc NUMBER := 0;
1987
1988 l_ab_net_book_value NUMBER;
1989 l_ab_adjusted_cost NUMBER;
1990 l_ab_operating_acct NUMBER;
1991 l_ab_reval_reserve NUMBER;
1992 l_ab_deprn_amount NUMBER;
1993 l_ab_deprn_reserve NUMBER;
1994 l_ab_backlog_deprn_reserve NUMBER;
1995 l_ab_general_fund NUMBER;
1996
1997 l_ren_dist_id igi_iac_det_balances.distribution_id%TYPE;
1998 l_ret_dist_id igi_iac_det_balances.distribution_id%TYPE;
1999 l_units_assigned fa_distribution_history.units_assigned%TYPE;
2000 l_dist_units fa_distribution_history.transaction_units%TYPE;
2001 l_curr_units fa_distribution_history.units_assigned%TYPE;
2002
2003 l_retire_id fa_distribution_history.retirement_id%TYPE;
2004 l_trx_hdr_id_out fa_distribution_history.transaction_header_id_out%TYPE;
2005
2006 l_ret_ass_bal c_ret_ass_bal%ROWTYPE;
2007 l_ret_ytd c_ret_ytd%ROWTYPE;
2008 l_fully_reserved NUMBER;
2009 l_exists NUMBER;
2010
2011 -- bug 2480915 start(15)
2012 l_fa_deprn_ytd igi_iac_fa_deprn.deprn_ytd%TYPE;
2013 l_fa_deprn_period igi_iac_fa_deprn.deprn_period%TYPE;
2014 l_fa_deprn_reserve igi_iac_fa_deprn.deprn_reserve%TYPE;
2015 -- bug 2480915 end(15)
2016 l_active_flag igi_iac_det_balances.active_flag%TYPE;
2017
2018 -- exceptions
2019 e_no_corr_reinstatement EXCEPTION;
2020 e_no_ccid_found EXCEPTION;
2021 e_no_unit_prorate EXCEPTION;
2022
2023 BEGIN
2024 l_path := g_path||'Unit_Reinstatement';
2025 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'In Unit Reinstatement function');
2026
2027 -- get the adjustment_id of the transaction previous to the
2028 -- retirement, for distributions that will need to be fully reinstated
2029 -- OPEN c_trx_prev_ret(p_trxhdr_id_retire);
2030 OPEN c_trx_prev_ret(cp_book_type_code => g_book_type_code,
2031 cp_asset_id => g_asset_id,
2032 cp_trxhdr_id_retire => p_trxhdr_id_retire);
2033 FETCH c_trx_prev_ret INTO l_prev_adj_id;
2034 IF c_trx_prev_ret%NOTFOUND THEN
2035 CLOSE c_trx_prev_ret;
2036 RETURN FALSE;
2037 END IF;
2038 CLOSE c_trx_prev_ret;
2039 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Adjustment Id previous to retirement:'|| l_prev_adj_id);
2040
2041 -- count the elapsed periods
2042 --l_elapsed_periods := p_period_counter - l_prev_prd_cnt;
2043 l_elapsed_periods := p_period_counter - p_effective_retire_period_cnt;
2044 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Elapsed periods: '||l_elapsed_periods);
2045
2046 -- find out NOCOPY if the asset has been fully reserved prior to retirement
2047 OPEN c_fully_reserved(g_asset_id, g_book_type_code);
2048 FETCH c_fully_reserved INTO l_fully_reserved;
2049 IF c_fully_reserved%NOTFOUND THEN
2050 CLOSE c_fully_reserved;
2051 RETURN FALSE;
2052 END IF;
2053 CLOSE c_fully_reserved;
2054 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Asset fully reserved period counter: '||l_fully_reserved);
2055
2056 -- get the active distributions for the transaction
2057 -- from igi_iac_det_balances previous to the retirement
2058 FOR l_det_bal IN c_det_bal(l_prev_adj_id) LOOP
2059 -- initialise to zero for each distribution_id
2060 l_rsv_catchup_amt := 0;
2061
2062 -- find out NOCOPY if the distribution has been touched by retirement
2063 SELECT COUNT(*)
2064 INTO l_retire_effect
2065 FROM fa_distribution_history
2066 WHERE distribution_id = l_det_bal.distribution_id
2067 AND retirement_id = g_retirement_id;
2068 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Retirement effect: '||l_retire_effect);
2069
2070 -- get the reinstatement distribution id associated to the retired
2071 -- distribution id
2072 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Retirement distribution id: '||l_det_bal.distribution_id);
2073 IF (l_retire_effect > 0) THEN
2074 l_ren_dist_id := Get_Corr_Ren_Dist_Id(
2075 p_trxhdr_id_reinstate,
2076 l_det_bal.distribution_id -- retirement distribution id
2077 );
2078
2079 IF (l_ren_dist_id = 0) THEN
2080 RAISE e_no_corr_reinstatement;
2081 END IF;
2082 ELSE
2083 l_ren_dist_id := l_det_bal.distribution_id;
2084 END IF;
2085 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Reinstate distribution id: '||l_ren_dist_id);
2086
2087 -- calculate GF catchup for first transaction
2088 l_gf_catchup_amt := 0;
2089 IF (l_fully_reserved <= 0) THEN
2090 l_gf_catchup_amt := l_elapsed_periods *l_det_bal.deprn_period;
2091 ELSE
2092 l_gf_catchup_amt := 0;
2093 END IF;
2094
2095 -- Calculate the depreciation expense catchup amount if called from Second_Transaction
2096 IF (p_transaction_run = 'SECOND') THEN
2097 -- calculate the catchup for depreciation reserve
2098 l_rsv_catchup_amt := l_elapsed_periods *l_det_bal.deprn_period;
2099 ELSE
2100 l_rsv_catchup_amt := 0;
2101 END IF;
2102 -- If asset is fully reserved then catchup is 0
2103 IF (l_fully_reserved > 0) THEN
2104 l_rsv_catchup_amt := 0;
2105 END IF;
2106
2107 -- get all the account amounts
2108 l_adjustment_cost := l_det_bal.adjustment_cost;
2109 l_reval_reserve_cost := l_det_bal.reval_reserve_cost;
2110 l_reval_reserve_backlog := l_det_bal.reval_reserve_backlog;
2111 l_operating_acct_cost := l_det_bal.operating_acct_cost;
2112 l_operating_acct_backlog := l_det_bal.operating_acct_backlog;
2113 l_operating_acct_ytd := l_det_bal.operating_acct_ytd;
2114 l_deprn_period := l_det_bal.deprn_period;
2115 l_deprn_reserve_backlog := l_det_bal.deprn_reserve_backlog;
2116
2117 l_deprn_ytd := l_det_bal.deprn_ytd +l_rsv_catchup_amt;
2118 l_deprn_reserve := l_det_bal.deprn_reserve + l_rsv_catchup_amt;
2119 IF (l_det_bal.adjustment_cost > 0) THEN
2120 l_general_fund_per := l_deprn_period;
2121 l_general_fund_acc := l_det_bal.general_fund_acc + l_gf_catchup_amt;
2122 l_reval_reserve_gen_fund := l_general_fund_acc;
2123 l_reval_reserve_net := l_reval_reserve_cost - ( l_reval_reserve_backlog + l_reval_reserve_gen_fund);
2124 ELSE
2125 l_general_fund_per := l_det_bal.general_fund_per;
2126 l_general_fund_acc := l_det_bal.general_fund_acc;
2127 l_reval_reserve_gen_fund := l_det_bal.reval_reserve_gen_fund;
2128 l_reval_reserve_net := l_det_bal.reval_reserve_net;
2129 END IF;
2130
2131 l_operating_acct_net := l_operating_acct_cost - l_operating_acct_backlog;
2132 l_net_book_value := l_adjustment_cost - (l_deprn_reserve + l_deprn_reserve_backlog);
2133
2134 -- bug 2480915 start(19) Modified for bug 2906034
2135 OPEN c_get_fa_deprn(l_prev_adj_id, l_det_bal.distribution_id);
2136 FETCH c_get_fa_deprn INTO l_fa_deprn_period, l_fa_deprn_ytd, l_fa_deprn_reserve;
2137 IF c_get_fa_deprn%NOTFOUND THEN
2138 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Record not found in igi_iac_fa_deprn');
2139 OPEN c_get_fa_det(g_book_type_code,
2140 g_asset_id,
2141 l_det_bal.distribution_id,
2142 l_det_bal.period_counter);
2143 FETCH c_get_fa_det INTO l_fa_deprn_period, l_fa_deprn_reserve;
2144 CLOSE c_get_fa_det;
2145
2146 OPEN c_get_fa_ytd(g_book_type_code,
2147 g_asset_id,
2148 l_det_bal.distribution_id);
2149 FETCH c_get_fa_ytd INTO l_fa_deprn_ytd;
2150 IF c_get_fa_ytd%NOTFOUND THEN
2151 l_fa_deprn_ytd := 0;
2152 END IF;
2153 CLOSE c_get_fa_ytd;
2154 ELSE
2155 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Record found in igi_iac_fa_deprn');
2156 IF l_fully_reserved > 0 THEN
2157 l_fa_deprn_period := 0;
2158 END IF;
2159 IF (p_transaction_run = 'SECOND') THEN
2160 l_fa_deprn_ytd := l_fa_deprn_ytd + (l_elapsed_periods*l_fa_deprn_period);
2161 l_fa_deprn_reserve := l_fa_deprn_reserve + (l_elapsed_periods*l_fa_deprn_period);
2162 END IF;
2163 END IF;
2164 CLOSE c_get_fa_deprn;
2165
2166 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'FA Depreciation amount: '||l_fa_deprn_period);
2167 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Detail level FA deprn rsv: '||l_fa_deprn_reserve);
2168 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Detail level FA deprn YTD: '||l_fa_deprn_ytd);
2169 -- bug 2480915 end(19) Modified for bug 2906034
2170
2171 -- keep a sum total for all the amounts
2172 l_tot_adjustment_cost := l_tot_adjustment_cost + l_adjustment_cost;
2173 l_tot_net_book_value := l_tot_net_book_value + l_net_book_value;
2174 l_tot_reval_reserve_cost := l_tot_reval_reserve_cost + l_reval_reserve_cost;
2175 l_tot_reval_reserve_backlog := l_tot_reval_reserve_backlog + l_reval_reserve_backlog;
2176 l_tot_reval_reserve_gen_fund := l_tot_reval_reserve_gen_fund + l_reval_reserve_gen_fund;
2177 l_tot_reval_reserve_net := l_tot_reval_reserve_net + l_reval_reserve_net;
2178 l_tot_operating_acct_cost := l_tot_operating_acct_cost + l_operating_acct_cost;
2179 l_tot_operating_acct_backlog := l_tot_operating_acct_backlog + l_operating_acct_backlog;
2180 l_tot_operating_acct_net := l_tot_operating_acct_net + l_operating_acct_net;
2181 l_tot_operating_acct_ytd := l_tot_operating_acct_ytd + l_operating_acct_ytd;
2182 l_tot_deprn_period := l_tot_deprn_period + l_deprn_period;
2183 l_tot_deprn_ytd := l_tot_deprn_ytd + l_deprn_ytd;
2184 l_tot_deprn_reserve := l_tot_deprn_reserve + l_deprn_reserve;
2185 l_tot_deprn_reserve_backlog := l_tot_deprn_reserve_backlog + l_deprn_reserve_backlog;
2186 l_tot_general_fund_per := l_tot_general_fund_per + l_general_fund_per;
2187 l_tot_general_fund_acc := l_tot_general_fund_acc + l_general_fund_acc;
2188
2189 -- insert into detail balances
2190 IGI_IAC_DET_BALANCES_PKG.Insert_Row(
2191 x_rowid => l_rowid,
2192 x_adjustment_id => p_adjust_id_reinstate,
2193 x_asset_id => g_asset_id,
2194 x_book_type_code => g_book_type_code,
2195 x_distribution_id => l_ren_dist_id,
2196 x_period_counter => p_period_counter,
2197 x_adjustment_cost => l_adjustment_cost,
2198 x_net_book_value => l_net_book_value,
2199 x_reval_reserve_cost => l_reval_reserve_cost,
2200 x_reval_reserve_backlog => l_reval_reserve_backlog,
2201 x_reval_reserve_gen_fund => l_reval_reserve_gen_fund,
2202 x_reval_reserve_net => l_reval_reserve_net,
2203 x_operating_acct_cost => l_operating_acct_cost,
2204 x_operating_acct_backlog => l_operating_acct_backlog,
2205 x_operating_acct_net => l_operating_acct_net,
2206 x_operating_acct_ytd => l_operating_acct_ytd,
2207 x_deprn_period => l_deprn_period,
2208 x_deprn_ytd => l_deprn_ytd,
2209 x_deprn_reserve => l_deprn_reserve,
2210 x_deprn_reserve_backlog => l_deprn_reserve_backlog,
2211 x_general_fund_per => l_general_fund_per,
2212 x_general_fund_acc => l_general_fund_acc,
2213 x_last_reval_date => l_det_bal.last_reval_date,
2214 x_current_reval_factor => l_det_bal.current_reval_factor,
2215 x_cumulative_reval_factor => l_det_bal.cumulative_reval_factor,
2216 x_active_flag => null,
2217 x_mode => 'R'
2218 );
2219
2220 -- Bug 2480915, start(20)
2221 -- insert into igi_iac_fa_deprn with the reinstatement adjustment_id
2222 IGI_IAC_FA_DEPRN_PKG.Insert_Row(
2223 x_rowid => l_rowid,
2224 x_book_type_code => g_book_type_code,
2225 x_asset_id => g_asset_id,
2226 x_period_counter => p_period_counter,
2227 x_adjustment_id => p_adjust_id_reinstate,
2228 x_distribution_id => l_ren_dist_id,
2229 x_deprn_period => l_fa_deprn_period,
2230 x_deprn_ytd => l_fa_deprn_ytd,
2231 x_deprn_reserve => l_fa_deprn_reserve,
2232 x_active_flag => null,
2233 x_mode => 'R'
2234 );
2235 -- Bug 2480915, end(20)
2236 -- do the catchup adjustments here only if the amounts are greater than zero
2237 IF (l_elapsed_periods > 0) THEN
2238 IF (l_retire_effect > 0) THEN
2239 -- check if full or partial retirement
2240 SELECT units_assigned,
2241 transaction_units
2242 INTO l_units_assigned,
2243 l_dist_units
2244 FROM fa_distribution_history
2245 WHERE distribution_id = l_det_bal.distribution_id;
2246
2247 -- get the retirement prorate factor
2248 l_prorate_factor := l_dist_units/l_units_assigned;
2249 l_prorate_factor := abs(l_prorate_factor);
2250
2251 IF (l_prorate_factor = 1) THEN
2252 -- this is a full retirement
2253 -- create catchup adjustment entries only for the second
2254 -- depreciation transaction
2255 IF (p_transaction_run = 'SECOND') THEN
2256 -- get the ccid for the account type Depreciation Expense
2257 IF NOT igi_iac_common_utils.get_account_ccid(g_book_type_code,
2258 g_asset_id,
2259 l_det_bal.distribution_id,
2260 'DEPRN_EXPENSE_ACCT',
2261 p_trxhdr_id_reinstate,
2262 'RETIREMENT',
2263 l_ccid)
2264 THEN
2265 --RETURN false;
2266 g_message := 'No account code combination found for Depreciation Expense';
2267 RAISE e_no_ccid_found;
2268 END IF;
2269 -- insert into igi_iac_adjustments
2270 IGI_IAC_ADJUSTMENTS_PKG.Insert_Row(
2271 x_rowid => l_rowid,
2272 x_adjustment_id => p_adjust_id_reinstate,
2273 x_book_type_code => g_book_type_code,
2274 x_code_combination_id => l_ccid,
2275 x_set_of_books_id => p_sob_id,
2276 x_dr_cr_flag => 'DR',
2277 x_amount => l_rsv_catchup_amt, -- l_deprn_period,
2278 x_adjustment_type => 'EXPENSE',
2279 x_adjustment_offset_type => 'RESERVE',
2280 x_report_ccid => Null,
2281 x_transfer_to_gl_flag => 'Y',
2282 x_units_assigned => l_units_assigned,
2283 x_asset_id => g_asset_id,
2284 x_distribution_id => l_ren_dist_id,
2285 x_period_counter => p_period_counter,
2286 x_mode => 'R',
2287 x_event_id => p_event_id
2288 );
2289 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'FULL:Done Expense: '||l_rsv_catchup_amt);
2290 -- insert RESERVE journal into igi_iac_adjustments with the reserve catchup amount
2291 -- get the ccid for the account type
2292 IF NOT igi_iac_common_utils.get_account_ccid(g_book_type_code,
2293 g_asset_id,
2294 l_ren_dist_id,
2295 'DEPRN_RESERVE_ACCT',
2296 p_trxhdr_id_reinstate,
2297 'RETIREMENT',
2298 l_ccid)
2299 THEN
2300 --RETURN false;
2301 g_message := 'No account code combination found for Accumulated Depreciation';
2302 RAISE e_no_ccid_found;
2303 END IF;
2304 -- insert into igi_iac_adjustments
2305 IGI_IAC_ADJUSTMENTS_PKG.Insert_Row(
2306 x_rowid => l_rowid,
2307 x_adjustment_id => p_adjust_id_reinstate,
2308 x_book_type_code => g_book_type_code,
2309 x_code_combination_id => l_ccid,
2310 x_set_of_books_id => p_sob_id,
2311 x_dr_cr_flag => 'CR',
2312 x_amount => l_rsv_catchup_amt,
2313 x_adjustment_type => 'RESERVE',
2314 x_adjustment_offset_type => 'EXPENSE',
2315 x_report_ccid => Null,
2316 x_transfer_to_gl_flag => 'Y',
2317 x_units_assigned => l_units_assigned,
2318 x_asset_id => g_asset_id,
2319 x_distribution_id => l_ren_dist_id,
2320 x_period_counter => p_period_counter,
2321 x_mode => 'R',
2322 x_event_id => p_event_id
2323 );
2324 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'FULL:Done Deprn Reserve: '||l_rsv_catchup_amt);
2325 -- insert GENERAL FUND journal into igi_iac_adjustments with the catchup amount
2326 -- only if adjustment amount is greater than zero
2327 IF (l_det_bal.adjustment_cost > 0) THEN
2328 -- get the ccid for the account type Reval Reserve
2329 IF NOT igi_iac_common_utils.get_account_ccid(g_book_type_code,
2330 g_asset_id,
2331 l_ren_dist_id,
2332 'REVAL_RESERVE_ACCT',
2333 p_trxhdr_id_reinstate,
2334 'RETIREMENT',
2335 l_ccid)
2336 THEN
2337 --RETURN false;
2338 g_message := 'No account code combination found for Revaluation Reserve';
2339 RAISE e_no_ccid_found;
2340 END IF;
2341
2342 l_reval_rsv_ccid := l_ccid;
2343
2344 -- insert into igi_iac_adjustments
2345 IGI_IAC_ADJUSTMENTS_PKG.Insert_Row(
2346 x_rowid => l_rowid,
2347 x_adjustment_id => p_adjust_id_reinstate,
2348 x_book_type_code => g_book_type_code,
2349 x_code_combination_id => l_ccid,
2350 x_set_of_books_id => p_sob_id,
2351 x_dr_cr_flag => 'DR',
2352 x_amount => l_rsv_catchup_amt, --l_gf_catchup_amt,
2353 x_adjustment_type => 'REVAL RESERVE',
2354 x_adjustment_offset_type => 'GENERAL FUND', -- Null,
2355 x_report_ccid => Null,
2356 x_transfer_to_gl_flag => 'Y',
2357 x_units_assigned => l_units_assigned,
2358 x_asset_id => g_asset_id,
2359 x_distribution_id => l_ren_dist_id,
2360 x_period_counter => p_period_counter,
2361 x_mode => 'R',
2362 x_event_id => p_event_id );
2363 -- igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'FULL:Done Reval Reserve: '||l_gf_catchup_amt);
2364
2365 -- get the ccid for the account type
2366 IF NOT igi_iac_common_utils.get_account_ccid(g_book_type_code,
2367 g_asset_id,
2368 l_ren_dist_id,
2369 'GENERAL_FUND_ACCT',
2370 p_trxhdr_id_reinstate,
2371 'RETIREMENT',
2372 l_ccid)
2373 THEN
2374 --RETURN false;
2375 g_message := 'No account code combination found for General Fund';
2376 RAISE e_no_ccid_found;
2377 END IF;
2378 -- insert into igi_iac_adjustments
2379 IGI_IAC_ADJUSTMENTS_PKG.Insert_Row(
2380 x_rowid => l_rowid,
2381 x_adjustment_id => p_adjust_id_reinstate,
2382 x_book_type_code => g_book_type_code,
2383 x_code_combination_id => l_ccid,
2384 x_set_of_books_id => p_sob_id,
2385 x_dr_cr_flag => 'CR',
2386 x_amount => l_rsv_catchup_amt, --l_gf_catchup_amt,
2387 x_adjustment_type => 'GENERAL FUND',
2388 x_adjustment_offset_type => 'REVAL RESERVE',
2389 x_report_ccid => l_reval_rsv_ccid,
2390 x_transfer_to_gl_flag => 'Y',
2391 x_units_assigned => l_units_assigned,
2392 x_asset_id => g_asset_id,
2393 x_distribution_id => l_ren_dist_id,
2394 x_period_counter => p_period_counter,
2395 x_mode => 'R',
2396 x_event_id => p_event_id );
2397 -- igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'FULL:Done General Fund: '||l_gf_catchup_amt);
2398 END IF; -- adjustment cost > 0
2399 END IF; -- second transaction
2400 ELSE
2401 -- this is a partial retirement
2402 -- get the latest account amounts for calculating adjustment catchups
2403 -- get the associated retirement distribution id
2404 SELECT new.distribution_id
2405 INTO l_ret_dist_id
2406 FROM fa_distribution_history new, fa_distribution_history old
2407 WHERE old.retirement_id = g_retirement_id
2408 AND old.location_id = new.location_id
2409 AND old.code_combination_id = new.code_combination_id
2410 AND NVL(old.assigned_to,-99) = NVL(new.assigned_to,-99)
2411 AND old.transaction_header_id_out = new.transaction_header_id_in
2412 AND old.distribution_id = l_det_bal.distribution_id ;
2413
2414 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Latest adjustment id: '||p_latest_adjust_id||' dist id: '||l_ret_dist_id);
2415 -- bug 2485778, start 2
2416 -- the retirement distribution needs to be rolled forward as a YTD row
2417 -- for the reinstatement transaction
2418 OPEN c_ret_ytd(l_ret_dist_id);
2419 FETCH c_ret_ytd INTO l_ret_ytd;
2420 IF c_ret_ytd%NOTFOUND THEN
2421 CLOSE c_ret_ytd;
2422 RETURN FALSE;
2423 END IF;
2424 CLOSE c_ret_ytd;
2425
2426 -- insert into detail balances
2427 IGI_IAC_DET_BALANCES_PKG.Insert_Row(
2428 x_rowid => l_rowid,
2429 x_adjustment_id => p_adjust_id_reinstate,
2430 x_asset_id => g_asset_id,
2431 x_book_type_code => g_book_type_code,
2432 x_distribution_id => l_ret_dist_id,
2433 x_period_counter => p_period_counter,
2434 x_adjustment_cost => 0,
2435 x_net_book_value => 0,
2436 x_reval_reserve_cost => 0,
2437 x_reval_reserve_backlog => 0,
2438 x_reval_reserve_gen_fund => 0,
2439 x_reval_reserve_net => 0,
2440 x_operating_acct_cost => 0,
2441 x_operating_acct_backlog => 0,
2442 x_operating_acct_net => 0,
2443 x_operating_acct_ytd => l_ret_ytd.operating_acct_ytd,
2444 x_deprn_period => 0,
2445 x_deprn_ytd => 0, --l_ret_ytd.deprn_ytd,
2446 x_deprn_reserve => 0,
2447 x_deprn_reserve_backlog => 0,
2448 x_general_fund_per => 0,
2449 x_general_fund_acc => 0,
2450 x_last_reval_date => l_ret_ytd.last_reval_date,
2451 x_current_reval_factor => l_ret_ytd.current_reval_factor,
2452 x_cumulative_reval_factor => l_ret_ytd.cumulative_reval_factor,
2453 x_active_flag => 'N',
2454 x_mode => 'R'
2455 );
2456
2457 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Inserted YTD for partially retired distribution: '||l_ret_dist_id);
2458 -- bug 2485778, end 2
2459
2460 -- Bug 2480915, start(21) Modified for 2906034
2461 -- insert into igi_iac_fa_deprn with the reinstatement adjustment_id
2462 OPEN c_get_fa_deprn(l_ret_ytd.adjustment_id, l_ret_ytd.distribution_id);
2463 FETCH c_get_fa_deprn INTO l_fa_deprn_period, l_fa_deprn_ytd, l_fa_deprn_reserve;
2464 IF c_get_fa_deprn%NOTFOUND THEN
2465 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Record not found in igi_iac_fa_deprn');
2466 OPEN c_get_fa_ytd(g_book_type_code,
2467 g_asset_id,
2468 l_ret_ytd.distribution_id);
2469 FETCH c_get_fa_ytd INTO l_fa_deprn_ytd;
2470 IF c_get_fa_ytd%NOTFOUND THEN
2471 l_fa_deprn_ytd := 0;
2472 END IF;
2473 CLOSE c_get_fa_ytd;
2474 END IF;
2475 CLOSE c_get_fa_deprn;
2476
2477 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'In YTD: YTD deprn: '||l_fa_deprn_ytd);
2478 IGI_IAC_FA_DEPRN_PKG.Insert_Row(
2479 x_rowid => l_rowid,
2480 x_book_type_code => g_book_type_code,
2481 x_asset_id => g_asset_id,
2482 x_period_counter => p_period_counter,
2483 x_adjustment_id => p_adjust_id_reinstate,
2484 x_distribution_id => l_ret_dist_id,
2485 x_deprn_period => 0,
2486 x_deprn_ytd => 0, --l_fa_deprn_ytd,
2487 x_deprn_reserve => 0,
2488 x_active_flag => 'N',
2489 x_mode => 'R'
2490 );
2491 -- Bug 2480915, end(21) Modified for 2906034
2492 IF (p_transaction_run = 'SECOND') THEN
2493 OPEN c_deprn_expense(p_latest_adjust_id, l_ret_dist_id);
2494 FETCH c_deprn_expense INTO l_latest_dep_exp, l_latest_dep_rsv,
2495 l_latest_gen_fund, l_latest_rev_rsv;
2496 IF c_deprn_expense%NOTFOUND THEN
2497 CLOSE c_deprn_expense;
2498 RETURN FALSE;
2499 END IF;
2500 CLOSE c_deprn_expense;
2501 -- get the ccid for the account type Depreciation Expense
2502 IF NOT igi_iac_common_utils.get_account_ccid(g_book_type_code,
2503 g_asset_id,
2504 l_ren_dist_id,
2505 'DEPRN_EXPENSE_ACCT',
2506 p_trxhdr_id_reinstate,
2507 'RETIREMENT',
2508 l_ccid)
2509 THEN
2510 --RETURN false;
2511 g_message := 'No account code combination found for Depreciation Expense';
2512 RAISE e_no_ccid_found;
2513 END IF;
2514
2515 -- depreciation expense catchup
2516 l_rsv_catchup := (l_deprn_period - l_latest_dep_exp)*l_elapsed_periods;
2517
2518 -- insert into igi_iac_adjustments
2519 IGI_IAC_ADJUSTMENTS_PKG.Insert_Row(
2520 x_rowid => l_rowid,
2521 x_adjustment_id => p_adjust_id_reinstate,
2522 x_book_type_code => g_book_type_code,
2523 x_code_combination_id => l_ccid,
2524 x_set_of_books_id => p_sob_id,
2525 x_dr_cr_flag => 'DR',
2526 x_amount => l_rsv_catchup,
2527 x_adjustment_type => 'EXPENSE',
2528 x_adjustment_offset_type => 'RESERVE',
2529 x_report_ccid => Null,
2530 x_transfer_to_gl_flag => 'Y',
2531 x_units_assigned => l_units_assigned,
2532 x_asset_id => g_asset_id,
2533 x_distribution_id => l_ren_dist_id,
2534 x_period_counter => p_period_counter,
2535 x_mode => 'R',
2536 x_event_id => p_event_id );
2537 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'PARTIAL:Done Expense: '||l_rsv_catchup);
2538 -- insert RESERVE journal into igi_iac_adjustments with the reserve catchup amount
2539 -- get the ccid for the account type
2540 IF NOT igi_iac_common_utils.get_account_ccid(g_book_type_code,
2541 g_asset_id,
2542 l_ren_dist_id,
2543 'DEPRN_RESERVE_ACCT',
2544 p_trxhdr_id_reinstate,
2545 'RETIREMENT',
2546 l_ccid)
2547 THEN
2548 --RETURN false;
2549 g_message := 'No account code combination found for Accumulated Depreciation';
2550 RAISE e_no_ccid_found;
2551 END IF;
2552 -- insert into igi_iac_adjustments
2553 IGI_IAC_ADJUSTMENTS_PKG.Insert_Row(
2554 x_rowid => l_rowid,
2555 x_adjustment_id => p_adjust_id_reinstate,
2556 x_book_type_code => g_book_type_code,
2557 x_code_combination_id => l_ccid,
2558 x_set_of_books_id => p_sob_id,
2559 x_dr_cr_flag => 'CR',
2560 x_amount => l_rsv_catchup, --l_rsv_catchup_amt,
2561 x_adjustment_type => 'RESERVE',
2562 x_adjustment_offset_type => 'EXPENSE',
2563 x_report_ccid => Null,
2564 x_transfer_to_gl_flag => 'Y',
2565 x_units_assigned => l_units_assigned,
2566 x_asset_id => g_asset_id,
2567 x_distribution_id => l_ren_dist_id,
2568 x_period_counter => p_period_counter,
2569 x_mode => 'R',
2570 x_event_id => p_event_id );
2571 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'PARTIAL:Done Dep Reserve: '||l_rsv_catchup);
2572 -- insert GENERAL FUND journal into igi_iac_adjustments with the catchup amount
2573 -- only if adjustment amount is greater than zero
2574 IF (l_det_bal.adjustment_cost > 0) THEN
2575 -- get the ccid for the account type Revaluaion Reserve
2576 IF NOT igi_iac_common_utils.get_account_ccid(g_book_type_code,
2577 g_asset_id,
2578 l_ren_dist_id,
2579 'REVAL_RESERVE_ACCT',
2580 p_trxhdr_id_reinstate,
2581 'RETIREMENT',
2582 l_ccid)
2583 THEN
2584 --RETURN false;
2585 g_message := 'No account code combination found for Revaluation Reserve';
2586 RAISE e_no_ccid_found;
2587 END IF;
2588
2589 l_reval_rsv_ccid := l_ccid;
2590
2591 -- insert into igi_iac_adjustments
2592 IGI_IAC_ADJUSTMENTS_PKG.Insert_Row(
2593 x_rowid => l_rowid,
2594 x_adjustment_id => p_adjust_id_reinstate,
2595 x_book_type_code => g_book_type_code,
2596 x_code_combination_id => l_ccid,
2597 x_set_of_books_id => p_sob_id,
2598 x_dr_cr_flag => 'DR',
2599 x_amount => l_rsv_catchup,
2600 x_adjustment_type => 'REVAL RESERVE',
2601 x_adjustment_offset_type => 'GENERAL FUND',
2602 x_report_ccid => Null,
2603 x_transfer_to_gl_flag => 'Y',
2604 x_units_assigned => l_units_assigned,
2605 x_asset_id => g_asset_id,
2606 x_distribution_id => l_ren_dist_id,
2607 x_period_counter => p_period_counter,
2608 x_mode => 'R',
2609 x_event_id => p_event_id );
2610 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'PARTIAL:Done Reval Reserve: '||l_rsv_catchup);
2611
2612 -- get the ccid for the account type General Fund
2613 IF NOT igi_iac_common_utils.get_account_ccid(g_book_type_code,
2614 g_asset_id,
2615 l_ren_dist_id,
2616 'GENERAL_FUND_ACCT',
2617 p_trxhdr_id_reinstate,
2618 'RETIREMENT',
2619 l_ccid)
2620 THEN
2621 --RETURN false;
2622 g_message := 'No account code combination found for General Fund';
2623 RAISE e_no_ccid_found;
2624 END IF;
2625
2626 -- insert into igi_iac_adjustments
2627 IGI_IAC_ADJUSTMENTS_PKG.Insert_Row(
2628 x_rowid => l_rowid,
2629 x_adjustment_id => p_adjust_id_reinstate,
2630 x_book_type_code => g_book_type_code,
2631 x_code_combination_id => l_ccid,
2632 x_set_of_books_id => p_sob_id,
2633 x_dr_cr_flag => 'CR',
2634 x_amount => l_rsv_catchup,
2635 x_adjustment_type => 'GENERAL FUND',
2636 x_adjustment_offset_type => 'REVAL RESERVE',
2637 x_report_ccid => l_reval_rsv_ccid,
2638 x_transfer_to_gl_flag => 'Y',
2639 x_units_assigned => l_units_assigned,
2640 x_asset_id => g_asset_id,
2641 x_distribution_id => l_ren_dist_id,
2642 x_period_counter => p_period_counter,
2643 x_mode => 'R',
2644 x_event_id => p_event_id );
2645 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'PARTIAL:Done General Fund: '||l_de_catchup);
2646 END IF; -- adjustment cost > 0
2647 END IF; -- second transaction
2648 END IF; -- partial or full retirement
2649 END IF; -- distribution has retirement effect
2650 END IF; -- elapsed periods > 0
2651 END LOOP; -- all active dists from adjustment just prior to retirement
2652
2653 -- bring the YTD rows associated to the retirement over with the
2654 -- reinstatement adjustment id and current period counter
2655 FOR l_get_ytd IN c_get_ytd(cp_adjustment_id => l_prev_adj_id,
2656 cp_asset_id => g_asset_id,
2657 cp_book_type_code => g_book_type_code) LOOP
2658 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'In YTD: dist id: '||l_get_ytd.distribution_id);
2659
2660 IF nvl(l_get_ytd.active_flag,'Y') = 'Y' THEN
2661 -- Set all except deprn_ytd to zeroes
2662 l_get_ytd.adjustment_cost := 0;
2663 l_get_ytd.net_book_value := 0;
2664 l_get_ytd.reval_reserve_cost := 0;
2665 l_get_ytd.reval_reserve_backlog := 0;
2666 l_get_ytd.reval_reserve_gen_fund := 0;
2667 l_get_ytd.reval_reserve_net := 0;
2668 l_get_ytd.operating_acct_cost := 0;
2669 l_get_ytd.operating_acct_backlog := 0;
2670 l_get_ytd.operating_acct_net := 0;
2671 l_get_ytd.operating_acct_ytd := 0;
2672 l_get_ytd.deprn_period := 0;
2673 l_get_ytd.deprn_ytd := 0;
2674 l_get_ytd.deprn_reserve := 0;
2675 l_get_ytd.deprn_reserve_backlog := 0;
2676 l_get_ytd.general_fund_per := 0;
2677 l_get_ytd.general_fund_acc := 0;
2678 END IF;
2679 l_active_flag := 'N';
2680
2681 -- insert into igi_iac_det_balances with reinstatement adjustment_id
2682 IGI_IAC_DET_BALANCES_PKG.Insert_Row(
2683 x_rowid => l_rowid,
2684 x_adjustment_id => p_adjust_id_reinstate,
2685 x_asset_id => g_asset_id,
2686 x_book_type_code => g_book_type_code,
2687 x_distribution_id => l_get_ytd.distribution_id,
2688 x_period_counter => p_period_counter,
2689 x_adjustment_cost => l_get_ytd.adjustment_cost,
2690 x_net_book_value => l_get_ytd.net_book_value,
2691 x_reval_reserve_cost => l_get_ytd.reval_reserve_cost,
2692 x_reval_reserve_backlog => l_get_ytd.reval_reserve_backlog,
2693 x_reval_reserve_gen_fund => l_get_ytd.reval_reserve_gen_fund,
2694 x_reval_reserve_net => l_get_ytd.reval_reserve_net,
2695 x_operating_acct_cost => l_get_ytd.operating_acct_cost,
2696 x_operating_acct_backlog => l_get_ytd.operating_acct_backlog,
2697 x_operating_acct_net => l_get_ytd.operating_acct_net,
2698 x_operating_acct_ytd => l_get_ytd.operating_acct_ytd,
2699 x_deprn_period => l_get_ytd.deprn_period,
2700 x_deprn_ytd => l_get_ytd.deprn_ytd,
2701 x_deprn_reserve => l_get_ytd.deprn_reserve,
2702 x_deprn_reserve_backlog => l_get_ytd.deprn_reserve_backlog,
2703 x_general_fund_per => l_get_ytd.general_fund_per,
2704 x_general_fund_acc => l_get_ytd.general_fund_acc,
2705 x_last_reval_date => l_get_ytd.last_reval_date,
2706 x_current_reval_factor => l_get_ytd.current_reval_factor,
2707 x_cumulative_reval_factor => l_get_ytd.cumulative_reval_factor,
2708 x_active_flag => l_active_flag
2709 );
2710
2711 -- Bug 2480915, start(22)
2712 OPEN c_get_fa_deprn(l_get_ytd.adjustment_id, l_get_ytd.distribution_id);
2713 FETCH c_get_fa_deprn INTO l_fa_deprn_period, l_fa_deprn_ytd, l_fa_deprn_reserve;
2714 IF c_get_fa_deprn%NOTFOUND THEN
2715 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Record not found in igi_iac_fa_deprn');
2716 OPEN c_get_fa_ytd(g_book_type_code,
2717 g_asset_id,
2718 l_get_ytd.distribution_id);
2719 FETCH c_get_fa_ytd INTO l_fa_deprn_ytd;
2720 IF c_get_fa_ytd%NOTFOUND THEN
2721 l_fa_deprn_ytd := 0;
2722 END IF;
2723 CLOSE c_get_fa_ytd;
2724 END IF;
2725 CLOSE c_get_fa_deprn;
2726
2727 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'In YTD: YTD deprn: '||l_fa_deprn_ytd);
2728 IF nvl(l_get_ytd.active_flag,'Y') = 'Y' THEN
2729 l_fa_deprn_ytd := 0;
2730 END IF;
2731 -- insert into igi_iac_fa_deprn with the reinstatement adjustment_id
2732 IGI_IAC_FA_DEPRN_PKG.Insert_Row(
2733 x_rowid => l_rowid,
2734 x_book_type_code => g_book_type_code,
2735 x_asset_id => g_asset_id,
2736 x_period_counter => p_period_counter,
2737 x_adjustment_id => p_adjust_id_reinstate,
2738 x_distribution_id => l_get_ytd.distribution_id,
2739 x_deprn_period => 0,
2740 x_deprn_ytd => l_fa_deprn_ytd,
2741 x_deprn_reserve => 0,
2742 x_active_flag => 'N',
2743 x_mode => 'R'
2744 );
2745 -- Bug 2480915, end(22)
2746 END LOOP;
2747
2748 -- update the existing asset balances record
2749 -- if a row exists for the asset for the current period else create a new row
2750 SELECT count(*)
2751 INTO l_exists
2752 FROM igi_iac_asset_balances
2753 WHERE asset_id = g_asset_id
2754 AND book_type_code = g_book_type_code
2755 AND period_counter = p_period_counter;
2756
2757 -- fetch asset balances for the effective retirement period
2758 OPEN c_ret_ass_bal(g_asset_id,
2759 g_book_type_code,
2760 p_effective_retire_period_cnt --p_period_counter
2761 );
2762 FETCH c_ret_ass_bal INTO l_ret_ass_bal;
2763 IF c_ret_ass_bal%NOTFOUND THEN
2764 CLOSE c_ret_ass_bal;
2765 RETURN FALSE;
2766 END IF;
2767 CLOSE c_ret_ass_bal;
2768
2769 l_ab_net_book_value := l_tot_net_book_value;
2770 l_ab_adjusted_cost := l_tot_adjustment_cost;
2771 l_ab_operating_acct := l_tot_operating_acct_net;
2772 l_ab_reval_reserve := l_tot_reval_reserve_net;
2773 l_ab_deprn_amount := l_tot_deprn_period;
2774 l_ab_deprn_reserve := l_tot_deprn_reserve;
2775 l_ab_backlog_deprn_reserve := l_tot_deprn_reserve_backlog;
2776 l_ab_general_fund := l_tot_general_fund_acc;
2777
2778 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'NBV: '||l_ab_net_book_value);
2779 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Adjusted Cost: '||l_ab_adjusted_cost);
2780 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Operating acct: '||l_ab_operating_acct);
2781 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Reval Reserve: '||l_ab_reval_reserve);
2782 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Deprn Amount/Expense: '||l_ab_deprn_amount);
2783 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Deprn Reserve: '||l_ab_deprn_reserve);
2784 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Blog deprn reserve: '||l_ab_backlog_deprn_reserve);
2785 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Gen Fund: '||l_ab_general_fund);
2786
2787 IF (l_exists > 0) THEN
2788 IGI_IAC_ASSET_BALANCES_PKG.Update_Row(
2789 X_asset_id => g_asset_id,
2790 X_book_type_code => g_book_type_code,
2791 X_period_counter => p_period_counter,
2792 X_net_book_value => l_ab_net_book_value,
2793 X_adjusted_cost => l_ab_adjusted_cost,
2794 X_operating_acct => l_ab_operating_acct,
2795 X_reval_reserve => l_ab_reval_reserve,
2796 X_deprn_amount => l_ab_deprn_amount,
2797 X_deprn_reserve => l_ab_deprn_reserve,
2798 X_backlog_deprn_reserve => l_ab_backlog_deprn_reserve,
2799 X_general_fund => l_ab_general_fund,
2800 X_last_reval_date => l_ret_ass_bal.last_reval_date,
2801 X_current_reval_factor => l_ret_ass_bal.current_reval_factor,
2802 X_cumulative_reval_factor => l_ret_ass_bal.cumulative_reval_factor
2803 ) ;
2804 ELSE
2805 -- insert a row for the current period counter
2806 IGI_IAC_ASSET_BALANCES_PKG.Insert_Row(
2807 X_rowid => l_rowid,
2808 X_asset_id => g_asset_id,
2809 X_book_type_code => g_book_type_code,
2810 X_period_counter => p_period_counter,
2811 X_net_book_value => l_ab_net_book_value,
2812 X_adjusted_cost => l_ab_adjusted_cost,
2813 X_operating_acct => l_ab_operating_acct,
2814 X_reval_reserve => l_ab_reval_reserve,
2815 X_deprn_amount => l_ab_deprn_amount,
2816 X_deprn_reserve => l_ab_deprn_reserve,
2817 X_backlog_deprn_reserve => l_ab_backlog_deprn_reserve,
2818 X_general_fund => l_ab_general_fund,
2819 X_last_reval_date => l_ret_ass_bal.last_reval_date,
2820 X_current_reval_factor => l_ret_ass_bal.current_reval_factor,
2821 X_cumulative_reval_factor => l_ret_ass_bal.cumulative_reval_factor
2822 ) ;
2823 END IF;
2824
2825 RETURN TRUE;
2826 EXCEPTION
2827 WHEN e_no_corr_reinstatement THEN
2828 IF c_dist_det_bal%ISOPEN THEN
2829 CLOSE c_dist_det_bal;
2830 END IF;
2831 g_message := 'No corresponding reinstatement found for the retirement distribution';
2832 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,g_message);
2833 FA_SRVR_MSG.add_message(
2834 Calling_Fn => g_calling_fn,
2835 Name => 'IGI_IAC_NO_CORR_REIN_DIST_ID'
2836 );
2837 RETURN FALSE;
2838
2839 WHEN e_no_unit_prorate THEN
2840 IF c_dist_det_bal%ISOPEN THEN
2841 CLOSE c_dist_det_bal;
2842 END IF;
2843 g_message := 'Prorate factor for unit reinstatement not found';
2844 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,g_message);
2845 FA_SRVR_MSG.add_message(
2846 Calling_Fn => g_calling_fn,
2847 Name => 'IGI_IAC_NO_REINS_PRORATE'
2848 );
2849 RETURN FALSE;
2850
2851 WHEN e_no_ccid_found THEN
2852 IF c_dist_det_bal%ISOPEN THEN
2853 CLOSE c_dist_det_bal;
2854 END IF;
2855 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,g_message);
2856 FA_SRVR_MSG.add_message(
2857 Calling_Fn => g_calling_fn,
2858 Name => 'IGI_IAC_WF_FAILED_CCID'
2859 );
2860 RETURN FALSE;
2861
2862 WHEN others THEN
2863 IF c_dist_det_bal%ISOPEN THEN
2864 CLOSE c_dist_det_bal;
2865 END IF;
2866 g_message := 'Error:'||SQLERRM;
2867 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
2868 g_calling_fn1 := g_calling_fn||' : Unit';
2869 FA_SRVR_MSG.add_sql_error(
2870 Calling_Fn => g_calling_fn1
2871 );
2872 RETURN FALSE;
2873 END Unit_Reinstatement;
2874
2875 -- ========================================================================
2876 -- PROCEDURE First_Transaction: Will record the first transaction of type
2877 -- REINSTATEMENT for the reinstatement transaction
2878 -- ========================================================================
2879 FUNCTION First_Transaction(p_fa_reins_rec_info IN fa_reins_rec_info,
2880 p_retirement_type IN VARCHAR2,
2881 p_fa_ret_rec IN FA_API_TYPES.asset_retire_rec_type,
2882 p_event_id IN NUMBER
2883 )
2884 RETURN BOOLEAN
2885 IS
2886 -- cursor to retrieve all the journal records associated with the
2887 -- retirement_id
2888 CURSOR c_reverse_je(p_trx_header_id NUMBER)
2889 IS
2890 SELECT iaa.adjustment_id,
2891 iaa.book_type_code,
2892 iaa.code_combination_id,
2893 iaa.set_of_books_id,
2894 iaa.dr_cr_flag,
2895 iaa.amount,
2896 iaa.adjustment_type,
2897 iaa.transfer_to_gl_flag,
2898 iaa.units_assigned,
2899 iaa.asset_id,
2900 iaa.distribution_id,
2901 iaa.period_counter,
2902 iaa.adjustment_offset_type,
2903 iaa.report_ccid
2904 FROM igi_iac_adjustments iaa,
2905 igi_iac_transaction_headers iath
2906 WHERE iaa.adjustment_id = iath.adjustment_id
2907 AND iath.transaction_header_id = p_trx_header_id;
2908
2909 -- local variables
2910 l_rowid ROWID;
2911 l_latest_trx_type igi_iac_transaction_headers.transaction_type_code%TYPE;
2912 l_latest_trx_id igi_iac_transaction_headers.transaction_header_id%TYPE;
2913 l_latest_mref_id igi_iac_transaction_headers.mass_reference_id%TYPE;
2914 l_latest_adj_id igi_iac_transaction_headers.adjustment_id%TYPE;
2915 l_latest_adj_status igi_iac_transaction_headers.adjustment_status%TYPE;
2916 l_prev_adjustment_id igi_iac_transaction_headers.adjustment_id%TYPE;
2917 l_adjust_id_reinstate igi_iac_transaction_headers.adjustment_id%TYPE;
2918 l_dr_cr_flag igi_iac_adjustments.dr_cr_flag%TYPE;
2919 l_ren_dist_id igi_iac_det_balances.distribution_id%TYPE;
2920 l_units_assigned fa_distribution_history.units_assigned%TYPE;
2921
2922 l_path VARCHAR2(150);
2923
2924 -- exceptions
2925 e_latest_trx_not_avail EXCEPTION;
2926 e_no_corr_reinstatement EXCEPTION;
2927 e_reinstate_failed EXCEPTION;
2928
2929 BEGIN
2930 l_path := g_path||'First_Transaction';
2931
2932 -- get the latest transaction for the asset
2933 IF NOT igi_iac_common_utils.get_latest_transaction(p_fa_reins_rec_info.book_type_code,
2934 p_fa_reins_rec_info.asset_id,
2935 l_latest_trx_type,
2936 l_latest_trx_id,
2937 l_latest_mref_id,
2938 l_latest_adj_id,
2939 l_prev_adjustment_id,
2940 l_latest_adj_status)
2941 THEN
2942 RAISE e_latest_trx_not_avail;
2943 END IF;
2944
2945 -- insert a new row for the asset with transaction type REINSTATEMENT
2946 -- into igi_iac_transaction_headers
2947 l_adjust_id_reinstate := null;
2948 IGI_IAC_TRANS_HEADERS_PKG.Insert_Row(
2949 x_rowid => l_rowid,
2950 x_adjustment_id => l_adjust_id_reinstate, -- out parameter
2951 x_transaction_header_id => p_fa_reins_rec_info.transaction_header_id,
2952 x_adjustment_id_out => null,
2953 x_transaction_type_code => p_fa_reins_rec_info.transaction_type_code,
2954 x_transaction_date_entered => p_fa_reins_rec_info.transaction_date_entered,
2955 x_mass_refrence_id => p_fa_reins_rec_info.mass_reference_id,
2956 x_transaction_sub_type => p_fa_reins_rec_info.transaction_subtype,
2957 x_book_type_code => g_book_type_code,
2958 x_asset_id => g_asset_id,
2959 x_category_id => p_fa_reins_rec_info.asset_category_id,
2960 x_adj_deprn_start_date => null,
2961 x_revaluation_type_flag => null,
2962 x_adjustment_status => 'COMPLETE',
2963 x_period_counter => p_fa_reins_rec_info.curr_period_counter,
2964 x_event_id => p_event_id );
2965 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Inserted into headers table');
2966
2967 -- update the previous active row for the asset in igi_iac_transaction_headers
2968 -- in order to make it inactive by setting adjustment_id_out= adjustment_id of
2969 -- the active row in igi_iac_transaction_headers
2970 IGI_IAC_TRANS_HEADERS_PKG.Update_Row(
2971 x_prev_adjustment_id => l_latest_adj_id,
2972 x_adjustment_id => l_adjust_id_reinstate
2973 );
2974 g_adj_prior_ret := l_latest_adj_id;
2975
2976 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Updated Headers table');
2977 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Latest adjustment id: '||l_latest_adj_id);
2978 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Reinstatement adjustment id: '||l_adjust_id_reinstate);
2979
2980 -- find all the journal entries for the retirement from igi_iac_adjustments
2981 -- and create reverse journal entries for each of them for reinstatement
2982 FOR l_reverse_je IN c_reverse_je(p_fa_ret_rec.detail_info.transaction_header_id_in) LOOP
2983
2984 -- the dr_cr_flag will reverse, if it was DR then it will become CR
2985 IF (l_reverse_je.dr_cr_flag = 'DR') THEN
2986 l_dr_cr_flag := 'CR';
2987 ELSIF (l_reverse_je.dr_cr_flag = 'CR') THEN
2988 l_dr_cr_flag := 'DR';
2989 END IF;
2990 -- get the associated reinstatement distribution id if it exists
2991
2992 l_ren_dist_id := Get_Corr_Ren_Dist_Id(
2993 p_fa_ret_rec.detail_info.transaction_header_id_out,
2994 l_reverse_je.distribution_id -- retirement distribution id
2995 );
2996
2997 IF (l_ren_dist_id = 0) THEN
2998 RAISE e_no_corr_reinstatement;
2999 END IF;
3000 -- get the units_assigned value for the distribution
3001 -- from fa_distribution_history
3002 SELECT units_assigned
3003 INTO l_units_assigned
3004 FROM fa_distribution_history
3005 WHERE distribution_id = l_reverse_je.distribution_id;
3006
3007 -- insert into igi_iac_adjustments
3008 IGI_IAC_ADJUSTMENTS_PKG.Insert_Row(
3009 x_rowid => l_rowid,
3010 x_adjustment_id => l_adjust_id_reinstate,
3011 x_book_type_code => l_reverse_je.book_type_code,
3012 x_code_combination_id => l_reverse_je.code_combination_id,
3013 x_set_of_books_id => l_reverse_je.set_of_books_id,
3014 x_dr_cr_flag => l_dr_cr_flag,
3015 x_amount => l_reverse_je.amount,
3016 x_adjustment_type => l_reverse_je.adjustment_type,
3017 x_adjustment_offset_type => l_reverse_je.adjustment_offset_type,
3018 x_report_ccid => l_reverse_je.report_ccid,
3019 x_transfer_to_gl_flag => 'Y',
3020 x_units_assigned => l_units_assigned,
3021 x_asset_id => l_reverse_je.asset_id,
3022 x_distribution_id => l_ren_dist_id,
3023 x_period_counter => p_fa_reins_rec_info.curr_period_counter,
3024 x_mode => 'R',
3025 x_event_id => p_event_id
3026 );
3027
3028 END LOOP;
3029 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Done reversing retirement journals');
3030
3031 -- action accordingly based on retirement type
3032 IF (p_retirement_type = 'FULL') THEN
3033 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Calling Full Reinstatement');
3034 -- call procedure to do full reinstatement
3035 IF NOT Full_Reinstatement(l_adjust_id_reinstate,
3036 p_fa_ret_rec.detail_info.transaction_header_id_in, -- trx id of the retirement
3037 p_fa_ret_rec.detail_info.transaction_header_id_out, -- trx id for reinstatement
3038 p_fa_reins_rec_info.set_of_books_id,
3039 p_fa_reins_rec_info.curr_period_counter,
3040 p_fa_reins_rec_info.eff_ret_period_counter,
3041 'FIRST',
3042 p_event_id
3043 )
3044 THEN
3045 g_message := 'Full reinstatement failure';
3046 RAISE e_reinstate_failed;
3047 END IF;
3048 ELSIF (p_retirement_type = 'COST') THEN
3049 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Calling Cost Reinstatement');
3050 -- call procedure to do partial cost reinstatement
3051 IF NOT Cost_Reinstatement(l_adjust_id_reinstate,
3052 l_latest_adj_id,
3053 p_fa_ret_rec.detail_info.transaction_header_id_in, -- trx id of the retirement
3054 p_fa_ret_rec.detail_info.transaction_header_id_out, -- trx id for reinstatement
3055 l_latest_adj_id,
3056 p_fa_reins_rec_info.set_of_books_id,
3057 p_fa_reins_rec_info.curr_period_counter,
3058 p_retirement_type,
3059 p_fa_reins_rec_info.eff_ret_period_counter,
3060 'FIRST',
3061 p_event_id
3062 )
3063 THEN
3064 g_message := 'Cost reinstatement failure';
3065 RAISE e_reinstate_failed;
3066 END IF;
3067 ELSIF (p_retirement_type = 'UNIT') THEN
3068 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Calling Unit Reinstatement');
3069 -- call procedure to do partial unit reinstatement
3070 IF NOT Unit_Reinstatement(l_adjust_id_reinstate,
3071 p_fa_ret_rec.detail_info.transaction_header_id_in, -- trx id of the retirement
3072 p_fa_ret_rec.detail_info.transaction_header_id_out, -- trx id for reinstatement
3073 l_latest_adj_id,
3074 p_fa_reins_rec_info.set_of_books_id,
3075 p_fa_reins_rec_info.curr_period_counter,
3076 p_retirement_type,
3077 p_fa_reins_rec_info.eff_ret_period_counter,
3078 'FIRST',
3079 p_event_id
3080 )
3081 THEN
3082 g_message := 'Unit reinstatement failure';
3083 RAISE e_reinstate_failed;
3084 END IF;
3085
3086 END IF;
3087
3088 RETURN TRUE;
3089 EXCEPTION
3090 WHEN e_latest_trx_not_avail THEN
3091 -- close open cursors
3092 IF c_reverse_je%ISOPEN THEN
3093 CLOSE c_reverse_je;
3094 END IF;
3095 g_message := 'Latest transaction could not be retrieved for the asset';
3096 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,g_message);
3097 FA_SRVR_MSG.add_message(
3098 Calling_Fn => g_calling_fn,
3099 Name => 'IGI_IAC_NO_LATEST_TRX'
3100 );
3101 RETURN FALSE;
3102
3103 WHEN e_no_corr_reinstatement THEN
3104 -- close open cursors
3105 IF c_reverse_je%ISOPEN THEN
3106 CLOSE c_reverse_je;
3107 END IF;
3108 g_message := 'Could not get corresponding reinstatement distribution id';
3109 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,g_message);
3110 FA_SRVR_MSG.add_message(
3111 Calling_Fn => g_calling_fn,
3112 Name => 'IGI_IAC_NO_CORR_REIN_DIST_ID'
3113 );
3114 RETURN FALSE;
3115
3116 WHEN e_reinstate_failed THEN
3117 -- close open cursors
3118 IF c_reverse_je%ISOPEN THEN
3119 CLOSE c_reverse_je;
3120 END IF;
3121 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,g_message);
3122 FA_SRVR_MSG.add_message(
3123 Calling_Fn => g_calling_fn,
3124 Name => 'IGI_IAC_REINSTATE_FAILED'
3125 );
3126 RETURN FALSE;
3127
3128 WHEN others THEN
3129 -- close open cursors
3130 IF c_reverse_je%ISOPEN THEN
3131 CLOSE c_reverse_je;
3132 END IF;
3133 g_message := 'Error: '||SQLERRM;
3134 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
3135 g_calling_fn1 := g_calling_fn||' : First_Transaction';
3136 FA_SRVR_MSG.add_sql_error(
3137 Calling_Fn => g_calling_fn1
3138 );
3139 RETURN FALSE;
3140 END First_Transaction;
3141
3142 -- ========================================================================
3143 -- PROCEDURE Second_Transaction: Will record the second transaction of type
3144 -- DEPRECIATION - REINSTATEMENT for the reinstatement transaction
3145 -- ========================================================================
3146 FUNCTION Second_Transaction(p_fa_reins_rec_info IN fa_reins_rec_info,
3147 p_retirement_type IN VARCHAR2,
3148 p_fa_ret_rec IN FA_API_TYPES.asset_retire_rec_type,
3149 p_event_id IN NUMBER
3150 )
3151 RETURN BOOLEAN
3152 IS
3153
3154 -- local variables
3155 l_rowid ROWID;
3156 l_latest_trx_type igi_iac_transaction_headers.transaction_type_code%TYPE;
3157 l_latest_trx_id igi_iac_transaction_headers.transaction_header_id%TYPE;
3158 l_latest_mref_id igi_iac_transaction_headers.mass_reference_id%TYPE;
3159 l_latest_adj_id igi_iac_transaction_headers.adjustment_id%TYPE;
3160 l_latest_adj_status igi_iac_transaction_headers.adjustment_status%TYPE;
3161 l_prev_adjustment_id igi_iac_transaction_headers.adjustment_id%TYPE;
3162 l_adjust_id_reinstate igi_iac_transaction_headers.adjustment_id%TYPE;
3163
3164 l_path VARCHAR2(150);
3165
3166 -- exceptions
3167 e_latest_trx_not_avail EXCEPTION;
3168 e_reinstate_failed EXCEPTION;
3169 BEGIN
3170 l_path := g_path||'Second_Transaction';
3171 -- get the latest transaction for the asset
3172 IF NOT igi_iac_common_utils.get_latest_transaction(p_fa_reins_rec_info.book_type_code,
3173 p_fa_reins_rec_info.asset_id,
3174 l_latest_trx_type,
3175 l_latest_trx_id,
3176 l_latest_mref_id,
3177 l_latest_adj_id,
3178 l_prev_adjustment_id,
3179 l_latest_adj_status)
3180 THEN
3181 RAISE e_latest_trx_not_avail;
3182 END IF;
3183
3184 -- insert a new row for the asset with transaction type REINSTATEMENT
3185 -- into igi_iac_transaction_headers
3186 l_adjust_id_reinstate := null;
3187 IGI_IAC_TRANS_HEADERS_PKG.Insert_Row(
3188 x_rowid => l_rowid,
3189 x_adjustment_id => l_adjust_id_reinstate, -- out parameter
3190 x_transaction_header_id => null,
3191 x_adjustment_id_out => null,
3192 x_transaction_type_code => 'DEPRECIATION',
3193 x_transaction_date_entered => p_fa_reins_rec_info.transaction_date_entered,
3194 x_mass_refrence_id => null,
3195 x_transaction_sub_type => 'REINSTATEMENT',
3196 x_book_type_code => g_book_type_code,
3197 x_asset_id => g_asset_id,
3198 x_category_id => p_fa_reins_rec_info.asset_category_id,
3199 x_adj_deprn_start_date => null,
3200 x_revaluation_type_flag => null,
3201 x_adjustment_status => 'COMPLETE',
3202 x_period_counter => p_fa_reins_rec_info.curr_period_counter,
3203 x_event_id => p_event_id
3204 );
3205 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Inserted into headers table');
3206
3207 -- update the previous active row for the asset in igi_iac_transaction_headers
3208 -- in order to make it inactive by setting adjustment_id_out= adjustment_id of
3209 -- the active row in igi_iac_transaction_headers
3210 IGI_IAC_TRANS_HEADERS_PKG.Update_Row(
3211 x_prev_adjustment_id => l_latest_adj_id,
3212 x_adjustment_id => l_adjust_id_reinstate
3213 );
3214 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Updated Headers table');
3215 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Latest adjustment id: '||l_latest_adj_id);
3216 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Reinstatement adjustment id: '||l_adjust_id_reinstate);
3217
3218 -- action accordingly based on retirement type
3219 IF (p_retirement_type = 'FULL') THEN
3220 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Calling Full Reinstatement');
3221 -- call procedure to do full reinstatement
3222 IF NOT Full_Reinstatement(l_adjust_id_reinstate,
3223 p_fa_ret_rec.detail_info.transaction_header_id_in, -- trx id of the retirement
3224 p_fa_ret_rec.detail_info.transaction_header_id_out, -- trx id for reinstatement
3225 p_fa_reins_rec_info.set_of_books_id,
3226 p_fa_reins_rec_info.curr_period_counter,
3227 p_fa_reins_rec_info.eff_ret_period_counter,
3228 'SECOND',
3229 p_event_id
3230 )
3231 THEN
3232 g_message := 'Full reinstatement failure';
3233 RAISE e_reinstate_failed;
3234 END IF;
3235 ELSIF (p_retirement_type = 'COST') THEN
3236 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Calling Cost Reinstatement');
3237 -- call procedure to do partial cost reinstatement
3238 IF NOT Cost_Reinstatement(l_adjust_id_reinstate,
3239 l_latest_adj_id,
3240 p_fa_ret_rec.detail_info.transaction_header_id_in, -- trx id of the retirement
3241 p_fa_ret_rec.detail_info.transaction_header_id_out, -- trx id for reinstatement
3242 g_adj_prior_ret, --l_latest_adj_id,
3243 p_fa_reins_rec_info.set_of_books_id,
3244 p_fa_reins_rec_info.curr_period_counter,
3245 p_retirement_type,
3246 p_fa_reins_rec_info.eff_ret_period_counter,
3247 'SECOND',
3248 p_event_id
3249 )
3250 THEN
3251 g_message := 'Cost reinstatement failure';
3252 RAISE e_reinstate_failed;
3253 END IF;
3254 ELSIF (p_retirement_type = 'UNIT') THEN
3255 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Calling Unit Reinstatement');
3256 -- call procedure to do partial unit reinstatement
3257 IF NOT Unit_Reinstatement(l_adjust_id_reinstate,
3258 p_fa_ret_rec.detail_info.transaction_header_id_in, -- trx id of the retirement
3259 p_fa_ret_rec.detail_info.transaction_header_id_out, -- trx id for reinstatement
3260 g_adj_prior_ret, --l_latest_adj_id,
3261 p_fa_reins_rec_info.set_of_books_id,
3262 p_fa_reins_rec_info.curr_period_counter,
3263 p_retirement_type,
3264 p_fa_reins_rec_info.eff_ret_period_counter,
3265 'SECOND',
3266 p_event_id
3267 )
3268 THEN
3269 g_message := 'Unit reinstatement failure';
3270 RAISE e_reinstate_failed;
3271 END IF;
3272
3273 END IF;
3274 RETURN TRUE;
3275 EXCEPTION
3276 WHEN e_latest_trx_not_avail THEN
3277 g_message := 'Latest transaction could not be retrieved for the asset';
3278 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,g_message);
3279 FA_SRVR_MSG.add_message(
3280 Calling_Fn => g_calling_fn,
3281 Name => 'IGI_IAC_NO_LATEST_TRX'
3282 );
3283 RETURN FALSE;
3284 WHEN e_reinstate_failed THEN
3285 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,g_message);
3286 FA_SRVR_MSG.add_message(
3287 Calling_Fn => g_calling_fn,
3288 Name => 'IGI_IAC_REINSTATE_FAILED'
3289 );
3290 RETURN FALSE;
3291 WHEN others THEN
3292 g_message := 'Error: '||SQLERRM;
3293 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
3294 g_calling_fn1 := g_calling_fn||' : Second_Transaction';
3295 FA_SRVR_MSG.add_sql_error(
3296 Calling_Fn => g_calling_fn1
3297 );
3298 RETURN FALSE;
3299 END Second_Transaction;
3300
3301 -- ===================================================================
3302 -- Public functions and procedures
3303 -- ===================================================================
3304
3305 -- ===================================================================
3306 -- FUNCTION Do_Iac_Reinstatement:
3307 --
3308 -- Main IAC reinsatement function that will be called from Assets Gains
3309 -- and Losses program via codehook
3310 -- ===================================================================
3311 FUNCTION Do_Iac_Reinstatement(p_asset_id NUMBER,
3312 p_book_type_code VARCHAR2,
3313 p_retirement_id NUMBER,
3314 p_calling_function VARCHAR2,
3315 p_event_id NUMBER) --R12 uptake
3316 RETURN BOOLEAN
3317 IS
3318 -- cursor to retrieve information from fa_transaction_headers
3319 -- associated with a given transaction_header_id
3320 CURSOR c_fa_trx_headers(p_trx_hdr_id NUMBER)
3321 IS
3322 SELECT fth.transaction_header_id,
3323 fth.book_type_code,
3324 fth.asset_id,
3325 fth.transaction_type_code,
3326 fth.transaction_date_entered,
3327 fth.date_effective,
3328 fth.mass_reference_id,
3329 fth.transaction_subtype,
3330 fab.asset_category_id
3331 FROM fa_transaction_headers fth,
3332 fa_additions_b fab
3333 WHERE fth.asset_id = fab.asset_id
3334 AND fth.transaction_header_id = p_trx_hdr_id;
3335
3336 -- local variables
3337 l_path VARCHAR2(150);
3338 l_retirement_type VARCHAR2(5);
3339 l_asset_num FA_ADDITIONS.asset_number%TYPE;
3340
3341 l_fa_trx_headers c_fa_trx_headers%ROWTYPE;
3342 l_ret_rec FA_API_TYPES.asset_retire_rec_type;
3343 l_prd_rec IGI_IAC_TYPES.prd_rec;
3344 l_eff_ret_rec IGI_IAC_TYPES.prd_rec;
3345 l_sob_id NUMBER;
3346 l_coa_id NUMBER;
3347 l_currency VARCHAR2(15);
3348 l_precision NUMBER;
3349 l_mrc_sob_type VARCHAR2(30);
3350 l_fa_reins_rec_info fa_reins_rec_info;
3351
3352 -- exceptions
3353 e_iac_not_enabled EXCEPTION;
3354 e_not_iac_book EXCEPTION;
3355 e_no_iac_effect EXCEPTION;
3356 e_no_retire_effect EXCEPTION;
3357 e_no_period_info_avail EXCEPTION;
3358 e_indef_ret_type EXCEPTION;
3359 e_asset_revalued EXCEPTION;
3360 e_iac_fa_deprn EXCEPTION;
3361 e_first_trans_failed EXCEPTION;
3362 e_second_trans_failed EXCEPTION;
3363
3364 -- Sekhar ,14-07-2003
3365 -- Status for adjustments in a period
3366 FUNCTION Get_Adjustment_Status(
3367 X_book_type_code IN VARCHAR2,
3368 X_asset_id IN NUMBER,
3369 X_Period_Counter IN NUMBER
3370 )
3371 RETURN BOOLEAN
3372 IS
3373 CURSOR C_get_asset_adj (p_book_type_code fa_transaction_headers.book_type_code%TYPE,
3374 p_asset_id fa_transaction_headers.asset_id%TYPE,
3375 P_period_counter number)
3376 IS
3377 SELECT *
3378 FROM IGI_IAC_TRANSACTION_HEADERS
3379 WHERE book_type_code = p_book_type_code
3380 AND period_counter >= p_period_counter
3381 AND asset_id = p_asset_id
3382 AND transaction_type_code='REVALUATION' and transaction_sub_type in ('OCCASSIONAL','PRFOESSIONAL');
3383
3384 l_get_asset_adj C_get_asset_adj%rowtype;
3385
3386 BEGIN
3387 OPEN c_get_asset_adj( X_book_type_code,
3388 X_asset_id ,
3389 X_Period_Counter);
3390 FETCH c_get_asset_adj INTO l_get_asset_adj;
3391 IF c_get_asset_adj%FOUND THEN
3392 CLOSE c_get_asset_adj;
3393 RETURN FALSE;
3394 ELSE
3395 CLOSE c_get_asset_adj;
3396 RETURN TRUE;
3397 END IF;
3398 END Get_Adjustment_Status;
3399
3400
3401 BEGIN
3402 l_path := g_path||'Do_Iac_Reinstatement';
3403
3404 -- check if IAC option is enabled in IGI
3405 IF NOT igi_gen.is_req_installed('IAC')
3406 THEN
3407 RAISE e_iac_not_enabled;
3408 END IF;
3409 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'IAC is enabled');
3410
3411 -- check if the FA book is an IAC book
3412 IF NOT igi_iac_common_utils.is_iac_book(p_book_type_code)
3413 THEN
3414 RAISE e_not_iac_book;
3415 END IF;
3416 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' This is an IAC book: '|| p_book_type_code);
3417
3418 -- check if there is an IAC effect on the asset
3419 IF NOT igi_iac_common_utils.is_asset_proc(p_book_type_code,
3420 p_asset_id)
3421 THEN
3422 RAISE e_no_iac_effect;
3423 END IF;
3424
3425 -- get the asset number for easier debugging
3426 SELECT asset_number
3427 INTO l_asset_num
3428 FROM fa_additions_b
3429 WHERE asset_id = p_asset_id;
3430 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'There is an IAC effect on the asset: '||l_asset_num);
3431
3432 -- bug 2480915 start, call ytd preprocessor to populate igi_iac_fa_deprn
3433 -- if no entries exist for the book in the table
3434 IF NOT igi_iac_common_utils.populate_iac_fa_deprn_data(p_book_type_code,
3435 'REINSTATEMENT')
3436 THEN
3437 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Problems creating rows in igi_iac_fa_deprn');
3438 RAISE e_iac_fa_deprn;
3439 END IF;
3440 -- bug 2480915, end
3441
3442 -- set up the global variables
3443 g_book_type_code := p_book_type_code;
3444 g_asset_id := p_asset_id;
3445 g_retirement_id := p_retirement_id;
3446
3447 -- get the period counter value
3448 IF NOT igi_iac_common_utils.get_open_period_info(p_book_type_code,
3449 l_prd_rec)
3450 THEN
3451 RAISE e_no_period_info_avail;
3452 END IF;
3453
3454 -- get the GL set of books id
3455 IF NOT igi_iac_common_utils.get_book_GL_info(p_book_type_code,
3456 l_sob_id,
3457 l_coa_id,
3458 l_currency,
3459 l_precision)
3460 THEN
3461 RETURN false;
3462 END IF;
3463 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Obtained GL information');
3464
3465 -- from the p_retirement_id parameter passed in, retrieve the active
3466 -- transaction_header_id and the associated adjustment_id of the asset
3467 -- from igi_iac_transaction_headers
3468 l_ret_rec.retirement_id := p_retirement_id;
3469 l_mrc_sob_type := null;
3470 IF NOT fa_util_pvt.get_asset_retire_rec(l_ret_rec,
3471 l_mrc_sob_type)
3472 THEN
3473 RAISE e_no_retire_effect;
3474 ELSE
3475 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Have retiremen info from fa_retirements');
3476 -- in fa_retirements, transaction_header_id_in is fa_transaction_headers.transaction_header_id
3477 -- of the retirement
3478 -- transaction_header_id_out is fa_transaction_headers.transaction_header_id of the
3479 -- reinstatement
3480
3481 -- get the period info for the effective retirement date
3482 -- get the period counter value
3483 IF NOT igi_iac_common_utils.Get_Period_Info_for_Date(p_book_type_code,
3484 l_ret_rec.date_retired,
3485 l_eff_ret_rec)
3486 THEN
3487 RAISE e_no_period_info_avail;
3488 END IF;
3489
3490 --- Check if adjustment has been processed in the current period
3491 IF NOT Get_Adjustment_Status(p_book_type_code,
3492 P_Asset_Id,
3493 l_eff_ret_rec.period_counter ) THEN
3494
3495
3496 FA_SRVR_MSG.Add_Message(
3497 Calling_FN => p_calling_function ,
3498 Name => 'IGI_IAC_NO_REINST_REVAL',
3499 TOKEN1 => 'NUMBER',
3500 VALUE1 => l_asset_num,
3501 TRANSLATE => TRUE,
3502 APPLICATION => 'IGI');
3503
3504 RETURN FALSE;
3505 END IF;
3506
3507
3508 -- check if type of retirement is full, partial cost or partial unit as the
3509 -- cases have to be handled somewhat differently
3510 IF NOT igi_iac_common_utils.get_retirement_type(p_book_type_code,
3511 p_asset_id,
3512 p_retirement_id,
3513 l_retirement_type
3514 )
3515 THEN
3516 RAISE e_indef_ret_type;
3517 END IF;
3518 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'retirement type '||l_retirement_type);
3519
3520 -- bug 2452521 start(2), check if a revaluation has been done
3521 -- after the asset has been partially retired
3522 -- revaluation for fully retired assets can be ignored
3523 IF (l_retirement_type <> 'FULL') THEN
3524 IF is_revalued(p_asset_id,
3525 p_book_type_code,
3526 -- l_ret_rec.detail_info.transaction_header_id_in,
3527 l_eff_ret_rec.period_counter,
3528 l_prd_rec.period_counter)
3529 THEN
3530 RAISE e_asset_revalued;
3531 END IF;
3532 END IF;
3533 -- bug 2452521 end(2)
3534
3535 -- retrieve information from fa_transaction_headers for the
3536 -- corresponding reinstatement row
3537 OPEN c_fa_trx_headers(l_ret_rec.detail_info.transaction_header_id_out);
3538 FETCH c_fa_trx_headers INTO l_fa_trx_headers;
3539 CLOSE c_fa_trx_headers;
3540
3541 -- populate the fa reinstatement header info record group
3542 l_fa_reins_rec_info.asset_id := l_fa_trx_headers.asset_id;
3543 l_fa_reins_rec_info.book_type_code := l_fa_trx_headers.book_type_code;
3544 l_fa_reins_rec_info.transaction_header_id := l_fa_trx_headers.transaction_header_id;
3545 l_fa_reins_rec_info.transaction_type_code := l_fa_trx_headers.transaction_type_code;
3546 l_fa_reins_rec_info.transaction_date_entered := l_fa_trx_headers.transaction_date_entered;
3547 l_fa_reins_rec_info.date_effective := l_fa_trx_headers.date_effective;
3548 l_fa_reins_rec_info.mass_reference_id := l_fa_trx_headers.mass_reference_id;
3549 l_fa_reins_rec_info.transaction_subtype := l_fa_trx_headers.transaction_subtype;
3550 l_fa_reins_rec_info.asset_category_id := l_fa_trx_headers.asset_category_id;
3551 l_fa_reins_rec_info.set_of_books_id := l_sob_id;
3552 l_fa_reins_rec_info.curr_period_counter := l_prd_rec.period_counter;
3553 l_fa_reins_rec_info.eff_ret_period_counter := l_eff_ret_rec.period_counter;
3554
3555 -- call first transaction procedure
3556 IF NOT First_Transaction(p_fa_reins_rec_info => l_fa_reins_rec_info,
3557 p_retirement_type => l_retirement_type,
3558 p_fa_ret_rec => l_ret_rec,
3559 p_event_id => p_event_id
3560 )
3561 THEN
3562 RAISE e_first_trans_failed;
3563 END IF;
3564
3565 -- call second transaction procedure
3566 IF NOT Second_Transaction(p_fa_reins_rec_info => l_fa_reins_rec_info,
3567 p_retirement_type => l_retirement_type,
3568 p_fa_ret_rec => l_ret_rec,
3569 p_event_id => p_event_id
3570 )
3571 THEN
3572 RAISE e_second_trans_failed;
3573 END IF;
3574
3575 END IF;
3576 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Inflation Accounting Reinstatement successful');
3577
3578 --ROLLBACK;
3579 --RETURN FALSE;
3580 RETURN TRUE;
3581 EXCEPTION
3582 WHEN e_iac_not_enabled THEN
3583 -- close open cursors
3584 IF c_fa_trx_headers%ISOPEN THEN
3585 CLOSE c_fa_trx_headers;
3586 END IF;
3587
3588 g_message := 'IAC is not enabled in IGI options.';
3589 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,g_message);
3590 FA_SRVR_MSG.add_message(
3591 Calling_Fn => g_calling_fn,
3592 Name => 'IGI_IAC_NOT_INSTALLED'
3593 );
3594
3595 RETURN TRUE;
3596
3597 WHEN e_not_iac_book THEN
3598 -- close open cursors
3599 IF c_fa_trx_headers%ISOPEN THEN
3600 CLOSE c_fa_trx_headers;
3601 END IF;
3602
3603 g_message := 'The book is not an IAC book';
3604 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,g_message);
3605 FA_SRVR_MSG.add_message(
3606 Calling_Fn => g_calling_fn,
3607 Name => 'IGI_IAC_NOT_IAC_BOOK'
3608 );
3609
3610 RETURN TRUE;
3611
3612 WHEN e_no_iac_effect THEN
3613 -- close open cursors
3614 IF c_fa_trx_headers%ISOPEN THEN
3615 CLOSE c_fa_trx_headers;
3616 END IF;
3617
3618 g_message := 'This asset has not been revalued with IAC.';
3619 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,g_message);
3620 FA_SRVR_MSG.add_message(
3621 Calling_Fn => g_calling_fn,
3622 Name => 'IGI_IAC_NO_IAC_EFFECT'
3623 );
3624
3625 RETURN TRUE;
3626
3627 WHEN e_iac_fa_deprn THEN
3628 -- close open cursors
3629 IF c_fa_trx_headers%ISOPEN THEN
3630 CLOSE c_fa_trx_headers;
3631 END IF;
3632
3633 g_message := 'Could not create the ytd rows in igi_iac_fa_deprn';
3634 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,g_message);
3635 FA_SRVR_MSG.add_message(
3636 Calling_Fn => g_calling_fn,
3637 Name => 'IGI_IAC_FA_DEPR_CREATE_PROB',
3638 Token1 => 'BOOK',
3639 Value1 => g_book_type_code
3640 );
3641
3642 RETURN TRUE;
3643
3644 -- bug 2452521 start (3)
3645 WHEN e_asset_revalued THEN
3646 -- close open cursors
3647 IF c_fa_trx_headers%ISOPEN THEN
3648 CLOSE c_fa_trx_headers;
3649 END IF;
3650 g_message := 'This asset has been revalued atleast once after retirement. Cannot be reinstated.';
3651 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,g_message);
3652 FA_SRVR_MSG.add_message(
3653 Calling_Fn => g_calling_fn,
3654 Name => 'IGI_IAC_REVAL_POST_RETIRE'
3655 );
3656 ROLLBACK;
3657 RETURN TRUE;
3658 -- bug 2452521 end (3)
3659
3660 WHEN e_no_retire_effect THEN
3661 -- close open cursors
3662 IF c_fa_trx_headers%ISOPEN THEN
3663 CLOSE c_fa_trx_headers;
3664 END IF;
3665
3666 g_message := 'Retirement information not available for asset';
3667 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,g_message);
3668 FA_SRVR_MSG.add_message(
3669 Calling_Fn => g_calling_fn,
3670 Name => 'IGI_IAC_NO_RETIRE_EFFECT'
3671 );
3672 --ROLLBACK;
3673 RETURN FALSE;
3674
3675 WHEN e_no_period_info_avail THEN
3676 -- close open cursors
3677 IF c_fa_trx_headers%ISOPEN THEN
3678 CLOSE c_fa_trx_headers;
3679 END IF;
3680
3681 g_message := 'Period Info Error: '||SQLERRM;
3682 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,g_message);
3683 FA_SRVR_MSG.add_message(
3684 Calling_Fn => g_calling_fn,
3685 Name => 'IGI_IAC_NO_PERIOD_INFO'
3686 );
3687 --ROLLBACK;
3688 RETURN FALSE;
3689
3690 WHEN e_indef_ret_type THEN
3691 -- close open cursors
3692 IF c_fa_trx_headers%ISOPEN THEN
3693 CLOSE c_fa_trx_headers;
3694 END IF;
3695
3696 g_message := 'Cannot define retirement type';
3697 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,g_message);
3698 FA_SRVR_MSG.add_message(
3699 Calling_Fn => g_calling_fn,
3700 Name => 'IGI_IAC_INDEF_RETIRE_TYPE'
3701 );
3702 --ROLLBACK;
3703 RETURN FALSE;
3704
3705 WHEN e_first_trans_failed THEN
3706 -- close open cursors
3707 IF c_fa_trx_headers%ISOPEN THEN
3708 CLOSE c_fa_trx_headers;
3709 END IF;
3710
3711 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,g_message);
3712 FA_SRVR_MSG.add_message(
3713 Calling_Fn => g_calling_fn,
3714 Name => 'IGI_IAC_REINSTATE_FAILED'
3715 );
3716 --ROLLBACK;
3717 RETURN FALSE;
3718
3719 WHEN e_second_trans_failed THEN
3720 -- close open cursors
3721 IF c_fa_trx_headers%ISOPEN THEN
3722 CLOSE c_fa_trx_headers;
3723 END IF;
3724
3725 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,g_message);
3726 FA_SRVR_MSG.add_message(
3727 Calling_Fn => g_calling_fn,
3728 Name => 'IGI_IAC_REINSTATE_FAILED'
3729 );
3730 --ROLLBACK;
3731 RETURN FALSE;
3732
3733 WHEN others THEN
3734 -- close open cursors
3735 IF c_fa_trx_headers%ISOPEN THEN
3736 CLOSE c_fa_trx_headers;
3737 END IF;
3738
3739 g_message := 'Error: '||SQLERRM;
3740 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
3741 g_calling_fn1 := g_calling_fn||' : Main';
3742 FA_SRVR_MSG.add_sql_error(
3743 Calling_Fn => g_calling_fn1
3744 );
3745 --ROLLBACK;
3746 RETURN FALSE;
3747
3748 END Do_Iac_Reinstatement;
3749
3750 BEGIN
3751 --===========================FND_LOG.START=====================================
3752 g_state_level := FND_LOG.LEVEL_STATEMENT;
3753 g_proc_level := FND_LOG.LEVEL_PROCEDURE;
3754 g_event_level := FND_LOG.LEVEL_EVENT;
3755 g_excep_level := FND_LOG.LEVEL_EXCEPTION;
3756 g_error_level := FND_LOG.LEVEL_ERROR;
3757 g_unexp_level := FND_LOG.LEVEL_UNEXPECTED;
3758 g_path := 'IGI.PLSQL.igiiarnb.IGI_IAC_REINSTATE_PKG.';
3759 --===========================FND_LOG.END=======================================
3760
3761 g_calling_fn := 'IGI_IAC_REINSTATE_PKG.Do_Iac_Reinstatement';
3762
3763 END IGI_IAC_REINSTATE_PKG;