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