[Home] [Help]
PACKAGE BODY: APPS.FA_GAINLOSS_UPD_PKG
Source
1 PACKAGE BODY FA_GAINLOSS_UPD_PKG AS
2 /* $Header: fagupdb.pls 120.79.12010000.9 2009/02/04 09:12:08 souroy ship $*/
3
4 g_print_debug boolean := fa_cache_pkg.fa_print_debug;
5
6
7 /*============================================================================
8 | NAME faginfo
9 |
10 | FUNCTION Set selection_mode accordingly for Tax book
11 |
12 |
13 | History YYOON 05/23/06 Created
14 | added for the bug 5149832 and 5231996
15 |===========================================================================*/
16
17
18 Function faginfo(
19 RET IN fa_ret_types.ret_struct,
20 BK IN fa_ret_types.book_struct,
21 cpd_ctr IN NUMBER,
22 today IN DATE,
23 user_id IN NUMBER,
24 calling_module IN varchar,
25 candidate_mode IN varchar,
26 set_adj_row IN boolean,
27 unit_ret_in_corp OUT nocopy boolean,
28 ret_id_in_corp OUT nocopy number,
29 th_id_out_in_corp OUT nocopy number,
30 balance_tfr_in_tax OUT nocopy number,
31 adj_row IN OUT nocopy FA_ADJUST_TYPE_PKG.fa_adj_row_struct,
32 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
33 ) return boolean IS
34
35 l_unit_ret_in_corp boolean;
36 l_ret_id_in_corp number;
37 l_id_out number;
38 l_selection_retid number;
39 l_units_retired number;
40 l_balance_tfr_in_tax number;
41
42 l_calling_fn varchar2(40) := 'FA_GAINLOSS_UPD_PKG.faginfo';
43
44 faginfo_error EXCEPTION;
45
46 BEGIN
47
48 if (p_log_level_rec.statement_level) then
49 fa_debug_pkg.add(l_calling_fn, '++++++++++++++++++++++++', '...', p_log_level_rec);
50 fa_debug_pkg.add(l_calling_fn, '++ CALLED BY ', calling_module, p_log_level_rec);
51 end if;
52
53 if candidate_mode in ('RETIRE', 'CLEAR_PARTIAL') then
54
55 l_id_out := -1;
56
57 begin
58
59 select r.retirement_id
60 ,r.units
61 into l_ret_id_in_corp
62 ,l_units_retired
63 from fa_transaction_headers sth
64 ,fa_retirements r
65 ,fa_book_controls bc
66 where sth.transaction_header_id = RET.th_id_in
67 and sth.asset_id = RET.asset_id
68 and sth.book_type_code = RET.book
69 and bc.book_type_code = sth.book_type_code
70 and bc.book_class = 'TAX'
71 and r.asset_id = sth.asset_id
72 and r.transaction_header_id_in = nvl(sth.source_transaction_header_id, sth.transaction_header_id)
73 and rownum = 1;
74
75 if (p_log_level_rec.statement_level) then
76 fa_debug_pkg.add(l_calling_fn, '++ IN FAGINFO: Processing TAX', '...', p_log_level_rec);
77 fa_debug_pkg.add(l_calling_fn, '++ retirement_id in CORP', l_ret_id_in_corp, p_log_level_rec);
78 fa_debug_pkg.add(l_calling_fn, '++ l_units_retired in CORP', l_units_retired, p_log_level_rec);
79 end if;
80
81 exception
82 when no_data_found then -- when called from Corp
83 l_ret_id_in_corp := RET.retirement_id;
84 l_units_retired := RET.units_retired;
85 end;
86
87 l_unit_ret_in_corp := FALSE;
88 begin
89 -- calculate Corp's TRANSFER OUT THID
90 select transaction_header_id_out
91 into l_id_out
92 from fa_distribution_history
93 where retirement_id =
94 (select r.retirement_id
95 from fa_transaction_headers sth
96 ,fa_retirements r
97 where sth.transaction_header_id = RET.th_id_in
98 and sth.asset_id = RET.asset_id
99 and sth.book_type_code = RET.book
100 and r.asset_id = sth.asset_id
101 and r.transaction_header_id_in = nvl(sth.source_transaction_header_id, sth.transaction_header_id)
102 )
103 and transaction_header_id_out is not null
104 and rownum = 1;
105
106 l_unit_ret_in_corp := TRUE;
107
108 exception
109 when no_data_found then
110 l_unit_ret_in_corp := FALSE;
111 end;
112
113
114 -- Bug 5337905
115 begin
116
117 -- check if there were balance transfers in Tax book due to changes to DIST IDs in Corp book.(done via ret api)
118 -- l_balance_tfr_in_tax will still remain zero for the partial retirement after reinstatement being copied to TAX.
119 begin
120
121 if nvl(ret.mrc_sob_type_code,'P') <> 'R' then
122 select count(*)
123 into l_balance_tfr_in_tax
124 from fa_adjustments
125 where book_type_code = RET.book
126 and asset_id = RET.asset_id
127 and transaction_header_id = l_id_out -- Corp's TRANSFER OUT THID
128 and source_type_code in ('TRANSFER', 'RETIREMENT')
129 --and adjustment_amount <> 0 -- BUG 6655838
130 and rownum = 1;
131 else
132 select count(*)
133 into l_balance_tfr_in_tax
134 from fa_adjustments_mrc_v
135 where book_type_code = RET.book
136 and asset_id = RET.asset_id
137 and transaction_header_id = l_id_out -- Corp's TRANSFER OUT THID
138 and source_type_code in ('TRANSFER', 'RETIREMENT') -- TRANSFER for part-ret, RETIREMENT for reinst of full retirement
139 --and adjustment_amount <> 0
140 and rownum = 1;
141 end if;
142
143 exception
144 when no_data_found then null;
145 when others then
146 fa_srvr_msg.add_message(calling_fn => l_calling_fn
147 ,p_log_level_rec => p_log_level_rec);
148 raise faginfo_error;
149 end;
150
151 end;
152
153
154 -- issue #3 in Bug 4398887
155 begin
156
157 if (g_print_debug) then
158 fa_debug_pkg.add(l_calling_fn, '++ IN FAGINFO: l_balance_tfr_in_tax BEFORE code for issue#3', l_balance_tfr_in_tax);
159 end if;
160
161 if l_balance_tfr_in_tax = 0 then
162
163 -- check if a transaction prior to this retirement in Tax book has already used the latest active dist IDs
164 -- if yes, l_balance_tfr_in_tax will be set to 2.
165 begin
166
167 if nvl(ret.mrc_sob_type_code,'P') <> 'R' then
168
169 select 2 -- this has to be set to 2 to differ from 1 for a regular balance tfr; used for fagurt
170 into l_balance_tfr_in_tax
171 from fa_distribution_history
172 where asset_id = RET.asset_id
173 and transaction_header_id_out is NULL
174 and rownum = 1
175 and distribution_id =
176 (select max(distribution_id)
177 from fa_adjustments
178 where book_type_code = RET.book
179 and asset_id = RET.asset_id
180 -- and source_type_code in ('ADDITION')
181 and adjustment_amount <> 0
182 and transaction_header_id
183 = (select max(transaction_header_id)
184 from fa_transaction_headers
185 where book_type_code = RET.book
186 and asset_id = RET.asset_id
187 and transaction_header_id < RET.th_id_in -- ret thid in TAX
188 )
189 );
190 else
191
192 select 2 -- this has to be set to 2 to differ from 1 for a regular balance tfr; used for fagurt
193 into l_balance_tfr_in_tax
194 from fa_distribution_history
195 where asset_id = RET.asset_id
196 and transaction_header_id_out is NULL
197 and rownum = 1
198 and distribution_id =
199 (select max(distribution_id)
200 from fa_adjustments_mrc_v
201 where book_type_code = RET.book
202 and asset_id = RET.asset_id
203 -- and source_type_code in ('ADDITION')
204 and adjustment_amount <> 0
205 and transaction_header_id
206 = (select max(transaction_header_id)
207 from fa_transaction_headers
208 where book_type_code = RET.book
209 and asset_id = RET.asset_id
210 and transaction_header_id < RET.th_id_in -- ret thid in TAX
211 )
212 );
213
214 end if;
215
216 exception
217 when no_data_found then
218 l_balance_tfr_in_tax := 0;
219 when others then
220 fa_srvr_msg.add_message(calling_fn => l_calling_fn);
221 raise faginfo_error;
222 end;
223
224
225 end if;
226
227 if (g_print_debug) then
228 fa_debug_pkg.add(l_calling_fn, '++ IN FAGINFO: l_balance_tfr_in_tax AFTER code for issue#3', l_balance_tfr_in_tax);
229 end if;
230
231 end;
232
233
234 if set_adj_row then
235
236 if (bk.book_class and l_unit_ret_in_corp) then -- if partial unit ret in TAX book
237
238 if (l_balance_tfr_in_tax > 0) then
239 --adj_row.selection_retid := 0;
240 --adj_row.units_retired := 0;
241 adj_row.selection_thid := 0;
242 adj_row.selection_mode := FA_STD_TYPES.FA_AJ_ACTIVE;
243 if p_log_level_rec.statement_level then
244 fa_debug_pkg.add(l_calling_fn, '++++ selection_mode', 'FA_STD_TYPES.FA_AJ_ACTIVE', p_log_level_rec);
245 end if;
246 else
247 if candidate_mode in ('RETIRE') then
248 adj_row.selection_retid := l_ret_id_in_corp;
249 adj_row.units_retired := l_units_retired;
250 adj_row.selection_mode := FA_STD_TYPES.FA_AJ_RETIRE;
251 if p_log_level_rec.statement_level then
252 fa_debug_pkg.add(l_calling_fn, '++++ selection_mode', 'FA_STD_TYPES.FA_AJ_RETIRE', p_log_level_rec);
253 end if;
254 else
255 adj_row.selection_thid := l_id_out;
256 adj_row.selection_mode := FA_STD_TYPES.FA_AJ_CLEAR_PARTIAL;
257 if p_log_level_rec.statement_level then
258 fa_debug_pkg.add(l_calling_fn, '++++ selection_mode', 'FA_STD_TYPES.FA_AJ_CLEAR_PARTIAL', p_log_level_rec);
259 end if;
260 end if;
261
262 end if;
263
264 else
265 --adj_row.selection_retid := 0;
266 --adj_row.units_retired := 0;
267 adj_row.selection_thid := 0;
268 adj_row.selection_mode := FA_STD_TYPES.FA_AJ_ACTIVE;
269 if p_log_level_rec.statement_level then
270 fa_debug_pkg.add(l_calling_fn, '++++ in ELSE: selection_mode', 'FA_STD_TYPES.FA_AJ_ACTIVE', p_log_level_rec);
271 end if;
272 end if;
273
274 end if;
275
276 end if;
277
278
279 unit_ret_in_corp := l_unit_ret_in_corp;
280 ret_id_in_corp := l_ret_id_in_corp;
281 th_id_out_in_corp := l_id_out;
282 balance_tfr_in_tax := l_balance_tfr_in_tax;
283
284 return TRUE;
285 EXCEPTION
286
287 when faginfo_error then
288 fa_srvr_msg.add_message(calling_fn => l_calling_fn
289 ,p_log_level_rec => p_log_level_rec);
290 return FALSE;
291
292 when others then
293 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn
294 ,p_log_level_rec => p_log_level_rec);
295 return FALSE;
296 END;
297
298
299 /*==========================================================================*
300 | NAME fagitc |
301 | |
302 | FUNCTION This function calculates ITC_RECAPTURED (if necessary) for |
303 | this retirement. The ITC_AMOUNT_ID in FA_BOOKS must not be |
304 | null in order for ITC_RECAPTURED to be calculated. |
305 | |
306 | HISTORY 1/17/89 R Rumanang Created |
307 | 12/27/89 R RUmanang Fixed bug in itc recaptured.|
308 | we should take partial itc |
309 | recaptured when partial ret.|
310 | 05/03/91 M Chan Modified for MPL 9 |
311 | 11/17/96 S Behura Converted into PL/SQL |
312 *===========================================================================*/
313
314 FUNCTION fagitc(ret in out nocopy fa_ret_types.ret_struct,
315 bk in out nocopy fa_ret_types.book_struct,
316 cost_frac in number,
317 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) Return boolean IS
318
319 no_recapture exception;
320 fagitc_err exception;
321
322 h_itc_amount number;
323 h_itc_amount_id number(15);
324 h_retirement_id number(15);
325 h_asset_id number(15);
326 h_recaptured number;
327 h_itc_recapture_id number(15);
328 h_date_placed date;
329 h_date_retired date;
330 h_book varchar2(16);
331 h_years_kept number(5);
332 h_cost_frac number;
333 h_cur_units number(6);
334
335 l_calling_fn varchar2(40) := 'FA_GAINLOSS_UPD_PKG.fagitc';
336
337 BEGIN <<FAGITC>>
338
339 h_itc_amount_id := bk.itc_used;
340 h_date_placed := bk.date_in_srv;
341 h_date_retired := ret.date_retired;
342 h_book := ret.book;
343 h_cur_units := bk.cur_units;
344 h_itc_amount := bk.itc_amount;
345 h_asset_id := ret.asset_id;
346 h_retirement_id := ret.retirement_id;
347 h_cost_frac := cost_frac;
348
349 SELECT (MONTHS_BETWEEN(trunc(h_date_retired),
350 trunc(h_date_placed)) / 12) + 1
351 INTO h_years_kept
352 FROM FA_RETIREMENTS
353 WHERE RETIREMENT_ID = h_retirement_id;
354
355 begin
356 SELECT farecap.itc_recapture_id ,
357 h_itc_amount * farecap.itc_recapture_rate *
358 h_cost_frac
359 INTO
360 h_itc_recapture_id,
361 h_recaptured
362 FROM fa_itc_recapture_rates farecap,
363 fa_itc_rates farate
364 WHERE farecap.tax_year = farate.tax_year
365 AND farecap.life_in_months = farate.life_in_months
366 AND farecap.year_of_retirement = h_years_kept
367 AND farate.itc_amount_id = h_itc_amount_id;
368
369 EXCEPTION
370
371 when no_data_found then
372 RAISE no_recapture;
373
374 end;
375
376 -- Call faxrnd in fagitc
377 if not FA_UTILS_PKG.faxrnd(h_recaptured, ret.book) then
378 fa_srvr_msg.add_message(calling_fn => l_calling_fn
379 ,p_log_level_rec => p_log_level_rec);
380 RAISE fagitc_err;
381 end if;
382
383 if (ret.mrc_sob_type_code <> 'R') then
384 UPDATE fa_retirements fr
385 SET fr.itc_recaptured = h_recaptured,
386 fr.itc_recapture_id = h_itc_recapture_id
387 WHERE fr.retirement_id = h_retirement_id;
388 else
389 UPDATE fa_retirements_mrc_v fr
390 SET fr.itc_recaptured = h_recaptured,
391 fr.itc_recapture_id = h_itc_recapture_id
392 WHERE fr.retirement_id = h_retirement_id;
393 end if;
394
395 return(TRUE);
396
397 EXCEPTION
398
399 when no_recapture then
400 fa_srvr_msg.add_message(
401 calling_fn => NULL,
402 name => 'FA_RET_NO_ITC',
403 token1 => 'MODULE',
404 value1 => 'FAGITC'
405 ,p_log_level_rec => p_log_level_rec);
406 return TRUE;
407
408 when others then
409 fa_srvr_msg.add_message(calling_fn => l_calling_fn
410 ,p_log_level_rec => p_log_level_rec);
411 return FALSE;
412
413 END FAGITC; -- End of Function FAGITC
414
415 /*===========================================================================*
416 | NAME fagurt |
417 | |
418 | FUNCTION |
419 | It calculates GAIN/LOSS, NBV_RETIRED, STL_DEPRN_AMOUNT. Update the |
420 | status in FA_RETIREMENTS table from 'PENDING' to 'PROCESSED'. It |
421 | also inserts GAIN/LOSS, PROCEEDS_OF_SALE, and COST_OF_REMOVAL to |
422 | FA_ADJUSTMENTS table. |
423 | |
424 | HISTORY 01/12/89 R Rumanang Created |
425 | 08/30/89 R Rumanang Updated to insert to |
426 | FA_ADJUSTMENTS. |
427 | 01/31/90 R Rumanang Insert PROCEEDS_OF_SALE to |
428 | ADJUSTMENT |
429 | 05/03/91 M Chan Rewrote for MPL 9 |
430 | 12/30/96 S Behura Rewriting in PL/SQL |
431 *============================================================================*/
432
433 FUNCTION fagurt(ret in out nocopy fa_ret_types.ret_struct,
434 bk in out nocopy fa_ret_types.book_struct,
435 cpd_ctr number,
436 dpr in out nocopy FA_STD_TYPES.dpr_struct,
437 cost_frac in number,
438 retpdnum in out nocopy number,
439 today in date,
440 user_id number,
441 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) return boolean IS
442
443 reval_deprn_amt number;
444 reval_amort_amt number;
445 cost_of_removal number;
446 proceeds_of_sale number;
447 proc_of_sale_clearing_acct varchar2(26);
448 proceeds_of_sale_gain_acct varchar2(26);
449 proceeds_of_sale_loss_acct varchar2(26);
450 cost_of_removal_clearing_acct varchar2(26);
451 cost_of_removal_gain_acct varchar2(26);
452 cost_of_removal_loss_acct varchar2(26);
453 nbv_retired_gain_acct varchar2(26);
454 nbv_retired_loss_acct varchar2(26);
455 reval_rsv_retired_gain_acct varchar2(26);
456 reval_rsv_retired_loss_acct varchar2(26);
457
458 --adj_row FA_STD_TYPES.fa_adj_row_struct;
459 adj_row FA_ADJUST_TYPE_PKG.fa_adj_row_struct;
460
461 h_dr_cr_flag number;
462 h_retire_reval_flag number;
463 h_retirement_id number(15);
464 h_th_id_in number(15);
465 h_asset_id number(15);
466 h_user_id number(15);
467 h_nbv_retired number;
468 h_gain_loss number;
469 h_reval_rsv_retired number;
470 h_bonus_rsv_retired number;
471 h_stl_deprn number;
472 h_unrevalued_cost_retired number;
473 h_dist_book varchar2(16);
474 h_today date;
475 h_book varchar2(16);
476 h_proc_of_sale_clearing_acct varchar2(26);
477 h_proceeds_of_sale_gain_acct varchar2(26);
478 h_proceeds_of_sale_loss_acct varchar2(26);
479 h_cost_of_removal_clr_acct varchar2(26);
480 h_cost_of_removal_gain_acct varchar2(26);
481 h_cost_of_removal_loss_acct varchar2(26);
482 h_nbv_retired_gain_acct varchar2(26);
483 h_nbv_retired_loss_acct varchar2(26);
484 h_reval_rsv_retired_gain_acct varchar2(26);
485 h_reval_rsv_retired_loss_acct varchar2(26);
486
487 X_LAST_UPDATE_DATE date := sysdate;
488 X_last_updated_by number := -1;
489 X_last_update_login number := -1;
490
491 h_bonus_deprn_amt number := 0;
492
493 l_calling_fn varchar2(40) := 'FA_GAINLOSS_UPD_PKG.fagurt';
494
495 /* Bug2316862 */
496 l_dist_id NUMBER;
497 l_ccid NUMBER;
498 l_location_id NUMBER;
499 l_th_id_out NUMBER;
500 l_new_dist_id NUMBER;
501 l_dist_cost NUMBER;
502 l_dist_reserve NUMBER;
503
504 /* bug 3519644 */
505 l_assigned_to NUMBER;
506 total_adj_amount NUMBER;
507 loop_counter NUMBER;
508 tot_dist_lines NUMBER;
509 /* bug 3519644 */
510
511
512 l_unit_ret_in_corp boolean;
513 l_ret_id_in_corp number;
514 h_id_out number;
515 l_balance_tfr_in_tax number;
516
517
518 CURSOR c_ret_dists IS
519 SELECT DISTRIBUTION_ID,
520 CODE_COMBINATION_ID,
521 LOCATION_ID,
522 ASSIGNED_TO, -- bug 3519644
523 TRANSACTION_HEADER_ID_OUT
524 FROM FA_DISTRIBUTION_HISTORY dist,
525 FA_BOOK_CONTROLS bc
526 -- Bug 5149832 WHERE RETIREMENT_ID = ret.retirement_id
527 WHERE RETIREMENT_ID = nvl(l_ret_id_in_corp, ret.retirement_id)
528 AND ASSET_ID = ret.asset_id
529 -- Bug 5149832 AND BOOK_TYPE_CODE = ret.book;
530 AND bc.book_type_code = ret.book
531 AND dist.BOOK_TYPE_CODE = bc.distribution_source_book;
532
533
534 CURSOR c_new_dist (c_ccid number,
535 c_location_id number,
536 c_assigned_to number, -- bug 3519644
537 c_th_id_out number) IS
538 SELECT DISTRIBUTION_ID
539 FROM FA_DISTRIBUTION_HISTORY dist,
540 FA_BOOK_CONTROLS bc
541 WHERE dist.TRANSACTION_HEADER_ID_IN = c_th_id_out
542 AND dist.TRANSACTION_HEADER_ID_OUT is NULL
543 AND dist.CODE_COMBINATION_ID = c_ccid
544 AND dist.LOCATION_ID = c_location_id
545 AND dist.ASSET_ID = ret.asset_id
546 -- Bug 5149832 AND BOOK_TYPE_CODE = ret.book
547 AND bc.book_type_code = RET.book
548 AND dist.BOOK_TYPE_CODE = bc.distribution_source_book
549 AND nvl (dist.assigned_to, -99) = nvl (c_assigned_to, -99); -- bug 3519644
550
551
552 -- NEW for TAX
553 CURSOR c_new_dist_tax (c_ccid number,
554 c_location_id number,
555 c_assigned_to number,
556 c_th_id_out number) IS
557 SELECT DISTRIBUTION_ID
558 FROM FA_DISTRIBUTION_HISTORY dist,
559 FA_BOOK_CONTROLS bc
560 WHERE
561 dist.CODE_COMBINATION_ID = c_ccid
562 AND dist.LOCATION_ID = c_location_id
563 AND dist.ASSET_ID = RET.asset_id
564 AND bc.book_type_code = RET.book
565 AND bc.book_class = 'TAX'
566 AND dist.BOOK_TYPE_CODE = bc.distribution_source_book
567 AND dist.transaction_header_id_in =
568 (select max(adj.transaction_header_id) -- get the latest THID in the same period that caused DIST ID to change
569 from fa_adjustments adj
570 where adj.book_type_code = RET.book
571 and adj.asset_id = RET.asset_id
572 and adj.source_type_code in ('RETIREMENT', 'TRANSFER') -- RETIREMENT: balance tfr for Reinstatement, TRANSFER: balance tfr for Retirement
573 and adj.period_counter_created = cpd_ctr
574 and adj.adjustment_type = 'COST'
575 and not exists -- check to see if adj.THID is from Corp
576 (select 1
577 from fa_transaction_headers th
578 where th.transaction_header_id = adj.transaction_header_id
579 and th.book_type_code = RET.book
580 and th.asset_id = RET.asset_id
581 )
582 )
583 ;
584
585
586 /* BUG 2665163. No NBV RETIRED row in fa_adj.
587 Modified c_ret_amount cursor to handle TAX book as well */
588 CURSOR c_ret_amount (c_asset_id number) is
589 SELECT DISTRIBUTION_ID,
590 CODE_COMBINATION_ID
591 FROM FA_DISTRIBUTION_HISTORY dist,
592 FA_BOOK_CONTROLS bc
593 WHERE TRANSACTION_HEADER_ID_OUT is NULL
594 AND ASSET_ID = c_asset_id
595 AND bc.book_type_code = ret.book
596 AND dist.book_type_code = bc.distribution_source_book;
597
598
599 CURSOR c_ret_rsv_costs (c_dist_id number,
600 c_new_dist_id number,
601 c_source_type_code varchar2,
602 c_adjustment_type varchar2) IS
603 SELECT SUM(NVL(DECODE(DEBIT_CREDIT_FLAG,
604 'CR', ADJUSTMENT_AMOUNT,
605 -1 * ADJUSTMENT_AMOUNT), 0)*
606 DECODE(ADJUSTMENT_TYPE, 'RESERVE', -1, 1))
607 FROM FA_ADJUSTMENTS
608 WHERE (DISTRIBUTION_ID = c_dist_id
609 OR DISTRIBUTION_ID = c_new_dist_id)
610 AND BOOK_TYPE_CODE = ret.book
611 AND PERIOD_COUNTER_CREATED = cpd_ctr
612 AND SOURCE_TYPE_CODE = c_source_type_code
613 AND ADJUSTMENT_TYPE = c_adjustment_type
614 AND TRANSACTION_HEADER_ID = ret.th_id_in;
615
616 CURSOR c_ret_rsv_costs_mrc (c_dist_id number,
617 c_new_dist_id number,
618 c_source_type_code varchar2,
619 c_adjustment_type varchar2) IS
620 SELECT SUM(NVL(DECODE(DEBIT_CREDIT_FLAG,
621 'CR', ADJUSTMENT_AMOUNT,
622 -1 * ADJUSTMENT_AMOUNT), 0)*
623 DECODE(ADJUSTMENT_TYPE, 'RESERVE', -1, 1))
624 FROM FA_ADJUSTMENTS_MRC_V
625 WHERE (DISTRIBUTION_ID = c_dist_id
626 OR DISTRIBUTION_ID = c_new_dist_id)
627 AND BOOK_TYPE_CODE = ret.book
628 AND PERIOD_COUNTER_CREATED = cpd_ctr
629 AND SOURCE_TYPE_CODE = c_source_type_code
630 AND ADJUSTMENT_TYPE = c_adjustment_type
631 AND TRANSACTION_HEADER_ID = ret.th_id_in;
632 /* End of Bug2316862 */
633 /* Bug2316862 was causing a data corruption.
634 Fixed by BUG#2626812 */
635
636 -- +++++ Get Current Unit of Group Asset +++++
637 CURSOR c_get_unit (c_asset_id number) is
638 select units
639 from fa_asset_history
640 where asset_id = c_asset_id
641 and transaction_header_id_out is null;
642
643 -- +++++ Get Group's reserve retired +++++
644 -- At this stage reserve retired entry against
645 -- Group has member asset's transaction_header_id.
646 -- This will be updated to group one in
647 -- FA_RETIREMENT_PVT.Do_Retirement_in_CGL
648 --
649 CURSOR c_get_g_rsv_ret is
650 select adjustment_amount
651 from fa_adjustments
652 where asset_id = bk.group_asset_id
653 and book_type_code = ret.book
654 and transaction_header_id = ret.th_id_in
655 and adjustment_type = 'RESERVE';
656
657 CURSOR c_get_nbv_ret is
658 select sum(decode(adjustment_type
659 ,'COST', decode(debit_credit_flag,'CR', nvl(adjustment_amount,0), -1 * nvl(adjustment_amount,0))
660 ,'RESERVE', decode(debit_credit_flag,'DR', -1 * nvl(adjustment_amount,0), nvl(adjustment_amount,0))
661 ,0))
662 from fa_adjustments
663 where asset_id = ret.asset_id
664 and book_type_code = ret.book
665 and transaction_header_id = ret.th_id_in
666 and source_type_code='RETIREMENT'
667 and adjustment_type in ('COST', 'RESERVE');
668
669 /* Bug2425233 */
670 l_adj_type VARCHAR2(15);
671
672 -- +++++ Records to hold member asset and group asset info +++++
673 l_asset_cat_rec_m FA_API_TYPES.asset_cat_rec_type;
674 l_asset_hdr_rec_g FA_API_TYPES.asset_hdr_rec_type;
675 l_asset_cat_rec_g FA_API_TYPES.asset_cat_rec_type;
676
677 l_g_reserve NUMBER;
678
679 BEGIN <<FAGURT>>
680
681 h_retire_reval_flag := 0;
682 reval_deprn_amt := 0;
683 reval_amort_amt := 0;
684 cost_of_removal := 0;
685 proceeds_of_sale := 0;
686 h_nbv_retired := 0;
687 h_gain_loss := 0;
688 h_reval_rsv_retired := 0;
689 h_bonus_rsv_retired := 0;
690 h_unrevalued_cost_retired := 0;
691 h_stl_deprn := 0;
692
693 h_th_id_in := ret.th_id_in;
694 h_asset_id := ret.asset_id;
695 h_retirement_id := ret.retirement_id;
696 h_user_id := user_id;
697 h_today := today;
698
699 h_book := ret.book;
700 h_dist_book := bk.dis_book;
701
702 if (bk.group_asset_id is not null) and
703 (nvl(bk.member_rollup_flag, 'N') = 'N') then
704 OPEN c_get_g_rsv_ret;
705 FETCH c_get_g_rsv_ret INTO l_g_reserve;
706 CLOSE c_get_g_rsv_ret;
707
708 -- fix for bug 3627497
709 /*h_nbv_retired := (ret.cost_retired + ret.cost_of_removal) -
710 (ret.proceeds_of_sale + l_g_reserve); */
711 h_nbv_retired := ret.cost_retired - nvl(l_g_reserve,0); --bug fix3639923
712
713 elsif ret.wip_asset > 0 then
714 h_nbv_retired := ret.cost_retired;
715 else
716 -- Bug#7766701:fetching c_get_nbv_ret even in case of full_retirement.
717 OPEN c_get_nbv_ret;
718 FETCH c_get_nbv_ret INTO h_nbv_retired;
719 CLOSE c_get_nbv_ret;
720
721 end if;
722
723 h_unrevalued_cost_retired := cost_frac * bk.unrevalued_cost;
724
725 /****************************************************************
726 Calculate stl_deprn . The dpr.y_begin and dpr.y_end had been
727 setup in retire routine. However, if h_deprn_lastyr_flag is 1,
728 we should not take the last year depreciation. Thus, we
729 decrease dpr.y_end by 1. If dpr.y_end is less than dpr.y_begin,
730 we set pd_num = 0 which is special value and caused it NOT to
731 caluculate the stl depreciation.
732 ****************************************************************/
733
734 /**************************************************************
735 If the asset is WIP, no STL calculation is required.
736 If the STL_METHOD_ID = 0, it implies that NO STL method is
737 used; no calculation is needed.
738 ************************************************************/
739
740 if (not bk.depreciate_lastyr) and (bk.book_class) and
741 (ret.wip_asset is null or ret.wip_asset <= 0) and
742 (ret.stl_method_code is not null) then
743
744 dpr.y_end := dpr.y_end - 1;
745 retpdnum := bk.pers_per_yr;
746
747 if dpr.y_end < dpr.y_begin then
748 retpdnum := 0; -- Special value
749 end if;
750 end if;
751
752 if (bk.book_class) and (retpdnum <> 0) and
753 (ret.wip_asset is null or ret.wip_asset <= 0) and
754 (ret.stl_method_code is not null) then
755
756 dpr.method_code := ret.stl_method_code;
757 dpr.life := ret.stl_life;
758
759 /**************************************************************
760 For STL depreciation calculation, the old deprn reserve
761 should not be used. zero depreciation reserve should be used.
762 ************************************************************/
763 dpr.deprn_rsv := 0;
764
765 if not FA_GAINLOSS_DPR_PKG.fagcdp(dpr, h_stl_deprn,
766 reval_deprn_amt, h_bonus_deprn_amt,
767 reval_amort_amt, bk.deprn_start_date,
768 bk.d_cal, bk.p_cal, 0, retpdnum, bk.prorate_fy,
769 bk.dsd_fy, bk.prorate_jdate,
770 bk.deprn_start_jdate
771 ,p_log_level_rec => p_log_level_rec) then
772
773 fa_srvr_msg.add_message(calling_fn => l_calling_fn
774 ,p_log_level_rec => p_log_level_rec);
775 return(FALSE);
776
777 end if;
778
779 h_stl_deprn := cost_frac * (h_stl_deprn + reval_deprn_amt);
780
781 end if;
782
783 select bc.proceeds_of_sale_clearing_acct,
784 bc.proceeds_of_sale_gain_acct,
785 bc.proceeds_of_sale_loss_acct,
786 bc.cost_of_removal_clearing_acct,
787 bc.cost_of_removal_gain_acct,
788 bc.cost_of_removal_loss_acct,
789 bc.nbv_retired_gain_acct,
790 bc.nbv_retired_loss_acct,
791 bc.reval_rsv_retired_gain_acct,
792 bc.reval_rsv_retired_loss_acct,
793 decode(bc.retire_reval_reserve_flag,'NO',0,
794 decode(cb.reval_reserve_acct,null,0,1)),
795 ad.asset_category_id
796 into h_proc_of_sale_clearing_acct,
797 h_proceeds_of_sale_gain_acct,
798 h_proceeds_of_sale_loss_acct,
799 h_cost_of_removal_clr_acct,
800 h_cost_of_removal_gain_acct,
801 h_cost_of_removal_loss_acct,
802 h_nbv_retired_gain_acct,
803 h_nbv_retired_loss_acct,
804 h_reval_rsv_retired_gain_acct,
805 h_reval_rsv_retired_loss_acct,
806 h_retire_reval_flag,
807 l_asset_cat_rec_m.category_id
808 from fa_book_controls bc,
809 fa_additions_b ad, fa_category_books cb
810 where ad.asset_id = h_asset_id
811 and cb.category_id = ad.asset_category_id
812 and cb.book_type_code = h_book
813 and bc.book_type_code = cb.book_type_code;
814
815 proc_of_sale_clearing_acct := h_proc_of_sale_clearing_acct;
816 proceeds_of_sale_gain_acct := h_proceeds_of_sale_gain_acct;
817 proceeds_of_sale_loss_acct := h_proceeds_of_sale_loss_acct;
818 cost_of_removal_clearing_acct := h_cost_of_removal_clr_acct;
819 cost_of_removal_gain_acct := h_cost_of_removal_gain_acct;
820 cost_of_removal_loss_acct := h_cost_of_removal_loss_acct;
821 nbv_retired_gain_acct := h_nbv_retired_gain_acct;
822 nbv_retired_loss_acct := h_nbv_retired_loss_acct;
823 reval_rsv_retired_gain_acct := h_reval_rsv_retired_gain_acct;
824 reval_rsv_retired_loss_acct := h_reval_rsv_retired_loss_acct;
825
826 if h_retire_reval_flag = 0 then
827 ret.reval_rsv_retired := 0;
828 end if;
829
830 h_reval_rsv_retired := ret.reval_rsv_retired;
831 h_bonus_rsv_retired := ret.bonus_rsv_retired;
832
833 -- Bug#5037745: added nvl to reval_rsv_retired to avoid NULL in gain/loss amount
834 h_gain_loss := (ret.proceeds_of_sale + nvl(ret.reval_rsv_retired,0)) -
835 (h_nbv_retired + ret.cost_of_removal);
836
837 -- Call faxrnd to round nbv_retired in fagurt
838 if not FA_UTILS_PKG.faxrnd(h_nbv_retired, ret.book) then
839 fa_srvr_msg.add_message(calling_fn => l_calling_fn
840 ,p_log_level_rec => p_log_level_rec);
841 return(FALSE);
842 end if;
843
844 -- Call faxrnd to round gain_loss in fagurt
845 if not FA_UTILS_PKG.faxrnd(h_gain_loss, ret.book) then
846 fa_srvr_msg.add_message(calling_fn => l_calling_fn
847 ,p_log_level_rec => p_log_level_rec);
848 return(FALSE);
849 end if;
850
851 -- Call faxrnd to round stl_deprn in fagurt
852 if not FA_UTILS_PKG.faxrnd(h_stl_deprn, ret.book) then
853 fa_srvr_msg.add_message(calling_fn => l_calling_fn
854 ,p_log_level_rec => p_log_level_rec);
855 return(FALSE);
856 end if;
857
858 -- Call faxrnd to round reval_rsv_retired in fagurt
859 if not FA_UTILS_PKG.faxrnd(h_reval_rsv_retired, ret.book) then
860 fa_srvr_msg.add_message(calling_fn => l_calling_fn
861 ,p_log_level_rec => p_log_level_rec);
862 return(FALSE);
863 end if;
864
865 -- Call faxrnd to round bonus_rsv_retired in fagurt
866 if not FA_UTILS_PKG.faxrnd(h_bonus_rsv_retired, ret.book) then
867 fa_srvr_msg.add_message(calling_fn => l_calling_fn
868 ,p_log_level_rec => p_log_level_rec);
869 return(FALSE);
870 end if;
871
872 -- Call faxrnd to round unrevalued_cost_retired in fagurt
873 if not FA_UTILS_PKG.faxrnd(h_unrevalued_cost_retired, ret.book) then
874 fa_srvr_msg.add_message(calling_fn => l_calling_fn
875 ,p_log_level_rec => p_log_level_rec);
876 return(FALSE);
877 end if;
878
879 if (ret.mrc_sob_type_code <> 'R') then
880 UPDATE fa_retirements fr
881 SET fr.nbv_retired = h_nbv_retired,
882 fr.gain_loss_amount = h_gain_loss,
883 fr.stl_deprn_amount = h_stl_deprn,
884 fr.reval_reserve_retired = h_reval_rsv_retired,
885 bonus_reserve_retired = h_bonus_rsv_retired,
886 fr.unrevalued_cost_retired =
887 h_unrevalued_cost_retired,
888 fr.status = 'PROCESSED',
889 fr.last_update_date = h_today,
890 fr.last_updated_by = h_user_id
891 WHERE
892 fr.retirement_id = h_retirement_id;
893 else
894 UPDATE fa_retirements_mrc_v fr
895 SET fr.nbv_retired = h_nbv_retired,
896 fr.gain_loss_amount = h_gain_loss,
897 fr.stl_deprn_amount = h_stl_deprn,
898 fr.reval_reserve_retired = h_reval_rsv_retired,
899 bonus_reserve_retired = h_bonus_rsv_retired,
900 fr.unrevalued_cost_retired =
901 h_unrevalued_cost_retired,
902 fr.status = 'PROCESSED',
903 fr.last_update_date = h_today,
904 fr.last_updated_by = h_user_id
905 WHERE
906 fr.retirement_id = h_retirement_id;
907 end if;
908
909 /* If gain it's 1(CR), else it's 0(DR) */
910 /* h_dr_cr_flag = (int) ((h_gain_loss < 0) ? 0 : 1); */
911
912 if h_gain_loss < 0 then
913 h_dr_cr_flag := 0;
914 else
915 h_dr_cr_flag := 1;
916 end if;
917
918 /* Note that we debit or credit the account based on the gain-loss.
919 The amount that we inserted into the table must be positive.
920 */
921
922 -- Setting l_unit_ret_in_corp
923 if NOT faginfo(
924 RET, BK, cpd_ctr,today, user_id
925 ,calling_module => l_calling_fn
926 ,candidate_mode => 'RETIRE'
927 ,set_adj_row => FALSE -- just to get l_unit_ret_in_corp and h_id_out
928 ,unit_ret_in_corp => l_unit_ret_in_corp
929 ,ret_id_in_corp => l_ret_id_in_corp
930 ,th_id_out_in_corp => h_id_out
931 ,balance_tfr_in_tax => l_balance_tfr_in_tax
932 ,adj_row => adj_row
933 ,p_log_level_rec => p_log_level_rec
934 ) then
935 fa_srvr_msg.add_message(calling_fn => l_calling_fn);
936 return(FALSE);
937 end if;
938
939 if (p_log_level_rec.statement_level) then
940 fa_debug_pkg.add(l_calling_fn, '++++++ AFTER faginfo to get some variables...', '...', p_log_level_rec);
941 fa_debug_pkg.add(l_calling_fn, '++ l_unit_ret_in_corp', l_unit_ret_in_corp, p_log_level_rec);
942 fa_debug_pkg.add(l_calling_fn, '++ l_ret_id_in_corp', l_ret_id_in_corp, p_log_level_rec);
943 fa_debug_pkg.add(l_calling_fn, '++ h_id_out', h_id_out, p_log_level_rec);
944 fa_debug_pkg.add(l_calling_fn, '++ l_balance_tfr_in_tax (1=TRUE)', l_balance_tfr_in_tax, p_log_level_rec);
945 end if;
946
947 adj_row.transaction_header_id := ret.th_id_in;
948
949 if ret.wip_asset > 0 then
950 adj_row.source_type_code := 'CIP RETIREMENT';
951 else
952 adj_row.source_type_code := 'RETIREMENT';
953 end if;
954
955 adj_row.book_type_code := ret.book;
956 adj_row.period_counter_created := cpd_ctr;
957 adj_row.asset_id := ret.asset_id;
958 adj_row.period_counter_adjusted := cpd_ctr;
959 adj_row.last_update_date := today;
960 adj_row.current_units := bk.cur_units;
961 adj_row.gen_ccid_flag := TRUE;
962 adj_row.flush_adj_flag := TRUE;
963 adj_row.annualized_adjustment := 0;
964 adj_row.code_combination_id := 0;
965 adj_row.distribution_id := 0;
966 adj_row.selection_thid := 0;
967 adj_row.asset_invoice_id := 0;
968 adj_row.leveling_flag := TRUE;
969
970
971 if (ret.units_retired <= 0 or ret.units_retired is null) then
972
973 adj_row.selection_mode := FA_STD_TYPES.FA_AJ_ACTIVE;
974 adj_row.selection_retid := 0;
975 adj_row.units_retired := 0;
976
977 if (bk.current_cost = ret.cost_retired) then
978 adj_row.selection_mode := FA_STD_TYPES.FA_AJ_ACTIVE;
979 else
980
981 if bk.book_class then
982 if NOT faginfo(
983 RET, BK, cpd_ctr,today, user_id
984 ,calling_module => l_calling_fn
985 ,candidate_mode => 'RETIRE'
986 ,set_adj_row => TRUE -- set adj_row
987 ,unit_ret_in_corp => l_unit_ret_in_corp
988 ,ret_id_in_corp => l_ret_id_in_corp
989 ,th_id_out_in_corp => h_id_out
990 ,balance_tfr_in_tax => l_balance_tfr_in_tax
991 ,adj_row => adj_row
992 ,p_log_level_rec => p_log_level_rec
993 ) then
994 fa_srvr_msg.add_message(calling_fn => l_calling_fn);
995 return(FALSE);
996 end if;
997 end if;
998
999 end if;
1000
1001 else
1002 adj_row.selection_mode := FA_STD_TYPES.FA_AJ_RETIRE;
1003 adj_row.selection_retid := ret.retirement_id;
1004 adj_row.units_retired := ret.units_retired;
1005 end if;
1006
1007 if (bk.group_asset_id is not null) and
1008 (nvl(bk.member_rollup_flag, 'N') = 'N') then
1009 if not fa_cache_pkg.fazccb
1010 (X_book => ret.book,
1011 X_cat_id => l_asset_cat_rec_m.category_id
1012 ,p_log_level_rec => p_log_level_rec) then
1013 fa_srvr_msg.add_message(calling_fn => l_calling_fn
1014 ,p_log_level_rec => p_log_level_rec);
1015 return(FALSE);
1016 end if;
1017
1018 end if;
1019
1020 -- +++++ BUG 2669638: Change operator from > to <> +++++
1021 -- +++++ Create PROCEEDS/RESERVE if this is not member asset with ALLOCATE +++++
1022 if (ret.proceeds_of_sale <> 0) and
1023 (not((bk.group_asset_id is not null) and
1024 nvl(bk.tracking_method, 'ALLOCATE') = 'ALLOCATE')) then
1025
1026 if (bk.group_asset_id is not null) and
1027 (nvl(bk.member_rollup_flag, 'N') = 'N') then
1028 adj_row.track_member_flag := 'Y';
1029 else
1030 adj_row.track_member_flag := null;
1031 end if;
1032
1033 adj_row.adjustment_type := 'PROCEEDS';
1034
1035 adj_row.adjustment_amount := ret.proceeds_of_sale;
1036
1037 if h_dr_cr_flag = 1 then
1038 adj_row.account := proceeds_of_sale_gain_acct;
1039 adj_row.account_type := 'PROCEEDS_OF_SALE_GAIN_ACCT';
1040 else
1041 adj_row.account := proceeds_of_sale_loss_acct;
1042 adj_row.account_type := 'PROCEEDS_OF_SALE_LOSS_ACCT';
1043 end if;
1044
1045 adj_row.debit_credit_flag := 'CR';
1046 adj_row.mrc_sob_type_code := ret.mrc_sob_type_code;
1047
1048 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
1049 X_last_update_date,
1050 X_last_updated_by,
1051 X_last_update_login
1052 ,p_log_level_rec => p_log_level_rec)) then
1053 fa_srvr_msg.add_message(calling_fn => l_calling_fn
1054 ,p_log_level_rec => p_log_level_rec);
1055 return(FALSE);
1056
1057 end if;
1058
1059 -- added for bug 3627497
1060 if (bk.group_asset_id is not null) and
1061 (nvl(bk.member_rollup_flag, 'N') = 'N') then
1062
1063 adj_row.adjustment_type := 'PROCEEDS CLR';
1064 adj_row.adjustment_amount := ret.proceeds_of_sale;
1065 adj_row.account := proc_of_sale_clearing_acct;
1066 adj_row.account_type := 'PROCEEDS_OF_SALE_CLEARING_ACCT';
1067 adj_row.debit_credit_flag := 'DR';
1068 adj_row.mrc_sob_type_code := ret.mrc_sob_type_code;
1069
1070 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
1071 X_last_update_date,
1072 X_last_updated_by,
1073 X_last_update_login
1074 ,p_log_level_rec => p_log_level_rec)) then
1075 fa_srvr_msg.add_message(calling_fn => l_calling_fn
1076 ,p_log_level_rec => p_log_level_rec);
1077 return(FALSE);
1078 end if;
1079
1080 end if;
1081
1082 end if; -- (ret.proceeds_of_sale <> 0) and
1083
1084 -- +++++ BUG 2669638: Changed operator from > to <>. +++++
1085 -- +++++ Create REMOVALCOST/RESERVE if this is not member asset with ALLOCATE +++++
1086 if (ret.cost_of_removal <> 0) and
1087 (not((bk.group_asset_id is not null) and
1088 nvl(bk.tracking_method, 'ALLOCATE') = 'ALLOCATE')) then
1089
1090 if (bk.group_asset_id is not null) and
1091 (nvl(bk.member_rollup_flag, 'N') = 'N') then
1092 adj_row.track_member_flag := 'Y';
1093 else
1094 adj_row.track_member_flag := null;
1095 end if;
1096
1097 adj_row.adjustment_type := 'REMOVALCOST';
1098
1099 adj_row.adjustment_amount := ret.cost_of_removal;
1100
1101 if h_dr_cr_flag = 1 then
1102 adj_row.account := cost_of_removal_gain_acct;
1103 adj_row.account_type := 'COST_OF_REMOVAL_GAIN_ACCT';
1104 else
1105 adj_row.account := cost_of_removal_loss_acct;
1106 adj_row.account_type := 'COST_OF_REMOVAL_LOSS_ACCT';
1107 end if;
1108
1109 adj_row.debit_credit_flag := 'DR';
1110 adj_row.mrc_sob_type_code := ret.mrc_sob_type_code;
1111
1112 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
1113 X_last_update_date,
1114 X_last_updated_by,
1115 X_last_update_login
1116 ,p_log_level_rec => p_log_level_rec)) then
1117 fa_srvr_msg.add_message(calling_fn => l_calling_fn
1118 ,p_log_level_rec => p_log_level_rec);
1119 return(FALSE);
1120
1121 end if;
1122
1123
1124 -- added for bug 3627497
1125 if (bk.group_asset_id is not null) and
1126 (nvl(bk.member_rollup_flag, 'N') = 'N') then
1127 adj_row.adjustment_type := 'REMOVALCOST CLR';
1128 adj_row.adjustment_amount := ret.cost_of_removal;
1129 adj_row.account := cost_of_removal_clearing_acct;
1130 adj_row.account_type := 'COST_OF_REMOVAL_CLEARING_ACCT';
1131 adj_row.debit_credit_flag := 'CR';
1132 adj_row.mrc_sob_type_code := ret.mrc_sob_type_code;
1133
1134 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
1135 X_last_update_date,
1136 X_last_updated_by,
1137 X_last_update_login
1138 ,p_log_level_rec => p_log_level_rec)) then
1139 fa_srvr_msg.add_message(calling_fn => l_calling_fn
1140 ,p_log_level_rec => p_log_level_rec);
1141 return(FALSE);
1142
1143 end if;
1144 end if;
1145
1146 end if; -- (ret.cost_of_removal > 0) and
1147
1148 /* BUG 2316862
1149 * NBV RETIRED entiries are created for each distribution lines retired
1150 * to avoid rounding errors.
1151 * Fisrt of all, get distribution which is effected by retirement.
1152 * If this is partial unit retirement, get only effected lines, otherwise
1153 * all ditribution lines.
1154 * Then find out cost and reserve retired and use these to find out
1155 * NBV retired for each effected distribution line.
1156 */
1157 if (fa_cache_pkg.fa_print_debug) then
1158 fa_debug_pkg.add(fname => l_calling_fn,
1159 element => '+++ret.cost_retired before if condition',
1160 value => ret.cost_retired
1161 ,p_log_level_rec => p_log_level_rec);
1162 fa_debug_pkg.add(fname => l_calling_fn,
1163 element => '+++ret.rsv_retired before if condition',
1164 value => ret.rsv_retired
1165 ,p_log_level_rec => p_log_level_rec);
1166 fa_debug_pkg.add(fname => l_calling_fn,
1167 element => '+++h_nbv_retired before if condition',
1168 value => h_nbv_retired
1169 ,p_log_level_rec => p_log_level_rec);
1170 end if;
1171
1172 -- +++++ BUG 2669638: Changed operator from > to <> +++++
1173 if (h_nbv_retired <> 0) and
1174 (not((bk.group_asset_id is not null) and
1175 nvl(bk.tracking_method, 'ALLOCATE') = 'ALLOCATE')) then
1176
1177 adj_row.selection_mode := fa_adjust_type_pkg.FA_AJ_TRANSFER_SINGLE;
1178 adj_row.adjustment_type := 'NBV RETIRED';
1179 adj_row.mrc_sob_type_code := ret.mrc_sob_type_code;
1180
1181 if (bk.group_asset_id is not null) and
1182 (nvl(bk.member_rollup_flag, 'N') = 'N') then
1183 adj_row.track_member_flag := 'Y';
1184 else
1185 adj_row.track_member_flag := null;
1186 end if;
1187
1188 if h_dr_cr_flag = 1 then
1189 adj_row.account := nbv_retired_gain_acct;
1190 adj_row.account_type := 'NBV_RETIRED_GAIN_ACCT';
1191 adj_row.debit_credit_flag := 'DR';
1192 else
1193 adj_row.account := nbv_retired_loss_acct;
1194 adj_row.account_type := 'NBV_RETIRED_LOSS_ACCT';
1195 adj_row.debit_credit_flag := 'DR';
1196 end if;
1197
1198 if (ret.units_retired is null and NOT l_unit_ret_in_corp) then
1199 OPEN c_ret_amount (ret.asset_id);
1200 if (fa_cache_pkg.fa_print_debug) then
1201 fa_debug_pkg.add(fname => l_calling_fn,
1202 element => '+++in amount',
1203 value => 1
1204 ,p_log_level_rec => p_log_level_rec);
1205 end if;
1206 else
1207 OPEN c_ret_dists;
1208 if (fa_cache_pkg.fa_print_debug) then
1209 fa_debug_pkg.add(fname => l_calling_fn,
1210 element => '+++in dists',
1211 value => 1
1212 ,p_log_level_rec => p_log_level_rec);
1213 end if;
1214 end if;
1215
1216 /* bug 3519644 */
1217 if (ret.units_retired is null and NOT l_unit_ret_in_corp) then
1218
1219 SELECT count(*)
1220 INTO tot_dist_lines
1221 FROM FA_DISTRIBUTION_HISTORY dist,
1222 FA_BOOK_CONTROLS bc
1223 WHERE TRANSACTION_HEADER_ID_OUT is NULL
1224 AND ASSET_ID = RET.asset_id
1225 AND bc.book_type_code = RET.book
1226 AND dist.book_type_code = bc.distribution_source_book;
1227
1228 else
1229
1230 SELECT count(*)
1231 INTO tot_dist_lines
1232 FROM FA_DISTRIBUTION_HISTORY dist,
1233 FA_BOOK_CONTROLS bc
1234 WHERE dist.RETIREMENT_ID = nvl(l_ret_id_in_corp, ret.retirement_id)
1235 AND dist.ASSET_ID = RET.asset_id
1236 AND bc.book_type_code = RET.book
1237 AND dist.book_type_code = bc.distribution_source_book;
1238
1239 end if;
1240
1241
1242 loop_counter := 0;
1243 total_adj_amount := 0;
1244 /* bug 3519644 */
1245
1246 LOOP
1247 /* Fetch all distribution effected process NBV retired */
1248 -- Bug 5149832
1249 if (ret.units_retired is null and NOT l_unit_ret_in_corp ) then
1250 FETCH c_ret_amount INTO l_dist_id, l_ccid;
1251 else
1252 FETCH c_ret_dists INTO l_dist_id, l_ccid, l_location_id, l_assigned_to, l_th_id_out; -- bug 3519644
1253 end if;
1254
1255 if (ret.units_retired is null and NOT l_unit_ret_in_corp) then
1256 EXIT WHEN c_ret_amount%NOTFOUND;
1257 else
1258 EXIT WHEN c_ret_dists%NOTFOUND;
1259 l_new_dist_id := to_number(null);
1260
1261 /*
1262 * If there are new distributiion created because of retirement
1263 * get distribution id which will be used to determine cost and reserve
1264 * retired.
1265 */
1266 if (l_th_id_out is not null) then
1267
1268 if bk.book_class then -- if TRUE=TAX
1269 OPEN c_new_dist_tax (l_ccid, l_location_id, l_assigned_to, l_th_id_out);
1270 FETCH c_new_dist_tax INTO l_new_dist_id;
1271 CLOSE c_new_dist_tax;
1272 else
1273 OPEN c_new_dist (l_ccid, l_location_id, l_assigned_to, l_th_id_out);
1274 FETCH c_new_dist INTO l_new_dist_id;
1275 CLOSE c_new_dist;
1276 end if;
1277
1278 end if;
1279
1280 if l_new_dist_id is null then
1281 l_new_dist_id := l_dist_id;
1282 else
1283 if bk.book_class and l_balance_tfr_in_tax > 0 then
1284 l_dist_id := l_new_dist_id;
1285 end if;
1286 end if;
1287
1288
1289 end if;
1290
1291 loop_counter := loop_counter + 1; -- bug 3519644
1292 adj_row.distribution_id := l_dist_id;
1293 adj_row.code_combination_id := l_ccid;
1294
1295 if (p_log_level_rec.statement_level) then
1296 fa_debug_pkg.add(fname => l_calling_fn,
1297 element => 'l_dist_id',
1298 value => l_dist_id
1299 ,p_log_level_rec => p_log_level_rec);
1300 fa_debug_pkg.add(fname => l_calling_fn,
1301 element => 'l_ccid',
1302 value => l_ccid
1303 ,p_log_level_rec => p_log_level_rec);
1304 fa_debug_pkg.add(fname => l_calling_fn,
1305 element => 'l_location_id',
1306 value => l_location_id
1307 ,p_log_level_rec => p_log_level_rec);
1308 fa_debug_pkg.add(fname => l_calling_fn,
1309 element => 'l_th_id_out',
1310 value => l_th_id_out
1311 ,p_log_level_rec => p_log_level_rec);
1312 fa_debug_pkg.add(fname => l_calling_fn,
1313 element => 'l_new_dist_id',
1314 value => l_new_dist_id
1315 ,p_log_level_rec => p_log_level_rec);
1316 end if;
1317
1318
1319 /* Get distribution level cost retired */
1320 /* Bug2425233
1321 * Check source type code and set ajustment type
1322 * accordingly. Also moved reserve query inside
1323 * of this check so reserve query should be executed
1324 * only if it's necessary.
1325 */
1326 if (adj_row.source_type_code <> 'CIP RETIREMENT') then
1327 l_adj_type := 'COST';
1328
1329 /* Get distribution level reserve retired */
1330 /* BUG# 2626812 */
1331 if (ret.mrc_sob_type_code <> 'R') then
1332 OPEN c_ret_rsv_costs (adj_row.distribution_id,
1333 l_new_dist_id,
1334 'RETIREMENT',
1335 'RESERVE');
1336 FETCH c_ret_rsv_costs INTO l_dist_reserve;
1337 CLOSE c_ret_rsv_costs;
1338 else
1339 OPEN c_ret_rsv_costs_mrc (adj_row.distribution_id,
1340 l_new_dist_id,
1341 'RETIREMENT',
1342 'RESERVE');
1343 FETCH c_ret_rsv_costs_mrc INTO l_dist_reserve;
1344 CLOSE c_ret_rsv_costs_mrc;
1345 end if;
1346
1347 else
1348 l_adj_type := 'CIP COST';
1349 l_dist_reserve := 0;
1350 end if;
1351
1352 if (ret.mrc_sob_type_code <> 'R') then
1353 OPEN c_ret_rsv_costs (adj_row.distribution_id,
1354 l_new_dist_id,
1355 adj_row.source_type_code,
1356 l_adj_type);
1357 FETCH c_ret_rsv_costs INTO l_dist_cost;
1358 CLOSE c_ret_rsv_costs;
1359 else
1360 OPEN c_ret_rsv_costs_mrc (adj_row.distribution_id,
1361 l_new_dist_id,
1362 adj_row.source_type_code,
1363 l_adj_type);
1364 FETCH c_ret_rsv_costs_mrc INTO l_dist_cost;
1365 CLOSE c_ret_rsv_costs_mrc;
1366 end if;
1367
1368 if (p_log_level_rec.statement_level) then
1369 fa_debug_pkg.add(l_calling_fn, '++++ ret.th_id_in', ret.th_id_in, p_log_level_rec);
1370 fa_debug_pkg.add(l_calling_fn, '++++ ret.book', ret.book, p_log_level_rec);
1371 fa_debug_pkg.add(l_calling_fn, '++++ cpd_ctr', cpd_ctr, p_log_level_rec);
1372 fa_debug_pkg.add(l_calling_fn, '++++ adj_row.distribution_id', adj_row.distribution_id, p_log_level_rec);
1373 fa_debug_pkg.add(l_calling_fn, '++++ adj_row.source_type_code', adj_row.source_type_code, p_log_level_rec);
1374 fa_debug_pkg.add(l_calling_fn, '++++ l_adj_type', l_adj_type, p_log_level_rec);
1375 fa_debug_pkg.add(l_calling_fn, '++ l_dist_cost', l_dist_cost, p_log_level_rec);
1376 fa_debug_pkg.add(l_calling_fn, '++ l_dist_reserve', l_dist_reserve, p_log_level_rec);
1377 end if;
1378
1379 --commented for bug 3519644
1380 --adj_row.adjustment_amount := l_dist_cost - l_dist_reserve;
1381
1382
1383 /* bug 3519644 */
1384 if (loop_counter <> tot_dist_lines) then
1385 adj_row.adjustment_amount := l_dist_cost - l_dist_reserve;
1386 else
1387 adj_row.adjustment_amount := h_nbv_retired - total_adj_amount;
1388 end if;
1389
1390
1391 total_adj_amount := total_adj_amount + adj_row.adjustment_amount;
1392
1393 /* bug 3519644 */
1394
1395 if (fa_cache_pkg.fa_print_debug) then
1396 fa_debug_pkg.add(fname => l_calling_fn,
1397 element => '+++adjustment_amount for NBV retired',
1398 value => adj_row.adjustment_amount
1399 ,p_log_level_rec => p_log_level_rec);
1400 end if;
1401
1402 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
1403 X_last_update_date,
1404 X_last_updated_by,
1405 X_last_update_login
1406 ,p_log_level_rec => p_log_level_rec)) then
1407 fa_srvr_msg.add_message(calling_fn => l_calling_fn
1408 ,p_log_level_rec => p_log_level_rec);
1409 return(FALSE);
1410
1411 end if;
1412 END LOOP;
1413
1414 if (ret.units_retired is null and NOT l_unit_ret_in_corp) then
1415 CLOSE c_ret_amount;
1416 else
1417 CLOSE c_ret_dists;
1418 end if;
1419
1420 end if; -- h_nbv_retired <> 0
1421
1422 -- +++++ Process for Group Asset +++++
1423 adj_row.track_member_flag := null;
1424
1425 if (bk.group_asset_id is not null) and
1426 (nvl(bk.member_rollup_flag, 'N') = 'N') and
1427 (h_nbv_retired <> 0) then
1428 l_asset_hdr_rec_g.asset_id := bk.group_asset_id;
1429 l_asset_hdr_rec_g.book_type_code := ret.book;
1430 l_asset_hdr_rec_g.set_of_books_id := fa_cache_pkg.fazcbc_record.set_of_books_id;
1431
1432 if not FA_UTIL_PVT.get_asset_cat_rec (
1433 p_asset_hdr_rec => l_asset_hdr_rec_g,
1434 px_asset_cat_rec => l_asset_cat_rec_g,
1435 p_date_effective => null
1436 ,p_log_level_rec => p_log_level_rec) then
1437 fa_srvr_msg.add_message(calling_fn => l_calling_fn
1438 ,p_log_level_rec => p_log_level_rec);
1439 return(FALSE);
1440 end if;
1441
1442 if not fa_cache_pkg.fazccb(
1443 X_book => ret.book,
1444 X_cat_id => l_asset_cat_rec_g.category_id
1445 ,p_log_level_rec => p_log_level_rec) then
1446 fa_srvr_msg.add_message(calling_fn => l_calling_fn
1447 ,p_log_level_rec => p_log_level_rec);
1448 return(FALSE);
1449 end if;
1450
1451 OPEN c_get_unit (bk.group_asset_id);
1452 FETCH c_get_unit INTO adj_row.current_units;
1453 CLOSE c_get_unit;
1454
1455 adj_row.asset_id := bk.group_asset_id;
1456 adj_row.selection_mode := FA_STD_TYPES.FA_AJ_ACTIVE;
1457 adj_row.selection_retid := 0;
1458 adj_row.units_retired := 0;
1459
1460 adj_row.adjustment_type := 'NBV RETIRED';
1461 adj_row.mrc_sob_type_code := ret.mrc_sob_type_code;
1462
1463 /* commented for bug 3627497 */
1464 /* if ((ret.proceeds_of_sale + l_g_reserve)>
1465 (ret.cost_retired + ret.cost_of_removal)) then
1466 adj_row.account := nbv_retired_gain_acct;
1467 adj_row.account_type := 'NBV_RETIRED_GAIN_ACCT';
1468 adj_row.debit_credit_flag := 'CR';
1469 else
1470 adj_row.account := nbv_retired_loss_acct;
1471 adj_row.account_type := 'NBV_RETIRED_LOSS_ACCT';
1472 adj_row.debit_credit_flag := 'DR';
1473 end if;
1474
1475 adj_row.adjustment_amount := abs((ret.cost_retired + ret.cost_of_removal) -
1476 (ret.proceeds_of_sale + l_g_reserve)); */
1477
1478 -- added for bug 3627497
1479 adj_row.adjustment_amount := ret.cost_retired - nvl(l_g_reserve,0);--bug fix 3639923
1480
1481 if h_dr_cr_flag = 1 then
1482 adj_row.account := nbv_retired_gain_acct;
1483 adj_row.account_type := 'NBV_RETIRED_GAIN_ACCT';
1484 adj_row.debit_credit_flag := 'DR';
1485 else
1486 adj_row.account := nbv_retired_loss_acct;
1487 adj_row.account_type := 'NBV_RETIRED_LOSS_ACCT';
1488 adj_row.debit_credit_flag := 'DR';
1489 end if;
1490
1491 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
1492 X_last_update_date,
1493 X_last_updated_by,
1494 X_last_update_login
1495 ,p_log_level_rec => p_log_level_rec)) then
1496 fa_srvr_msg.add_message(calling_fn => l_calling_fn
1497 ,p_log_level_rec => p_log_level_rec);
1498 return(FALSE);
1499 end if;
1500
1501 -- +++++ Set asset id back to member asset +++++
1502 adj_row.asset_id := ret.asset_id;
1503 end if; -- (bk.group_asset_id is not null) and
1504
1505
1506 /* BUG# 2444408
1507 This error began to occur after the fix for bug 2316862
1508 which was propagated from pro*c code.
1509 The remaining part of this revaluation gain/loss logic
1510 still continue to be called in the same manner
1511 as done before the fix for bug 2316862 */
1512 if (ret.units_retired <= 0 or ret.units_retired is null) then
1513
1514 adj_row.selection_mode := FA_STD_TYPES.FA_AJ_ACTIVE;
1515 adj_row.selection_retid := 0;
1516 adj_row.units_retired := 0;
1517 else
1518 adj_row.selection_mode := FA_STD_TYPES.FA_AJ_RETIRE;
1519 adj_row.selection_retid := ret.retirement_id;
1520 adj_row.units_retired := ret.units_retired;
1521 end if;
1522 /* End of fix for bug 2444408 */
1523
1524 if (ret.wip_asset is null or ret.wip_asset <= 0) and
1525 (h_retire_reval_flag = 1) then
1526
1527 if ret.reval_rsv_retired <> 0 then
1528
1529 adj_row.adjustment_type := 'REVAL RSV RET';
1530 adj_row.adjustment_amount := ret.reval_rsv_retired;
1531 -- bug 418884, should always be CR since the 'REVAL RESERVE' is always DR
1532 adj_row.debit_credit_flag := 'CR';
1533
1534 if h_dr_cr_flag = 1 then
1535 adj_row.account := reval_rsv_retired_gain_acct;
1536 adj_row.account_type := 'REVAL_RSV_RETIRED_GAIN_ACCT';
1537 else
1538 adj_row.account := reval_rsv_retired_loss_acct;
1539 adj_row.account_type := 'REVAL_RSV_RETIRED_LOSS_ACCT';
1540 end if;
1541
1542 adj_row.mrc_sob_type_code := ret.mrc_sob_type_code;
1543
1544 if p_log_level_rec.statement_level then
1545 fa_debug_pkg.add
1546 (fname => 'fagurt',
1547 element => '+++ before faxinaj for REVAL_RSV_RETIRED_GAIN_ACCT',
1548 value => adj_row.selection_mode
1549 ,p_log_level_rec => p_log_level_rec);
1550 end if;
1551
1552 if (bk.group_asset_id is not null) and
1553 (nvl(bk.member_rollup_flag, 'N') = 'N') then
1554 adj_row.track_member_flag := 'Y';
1555 else
1556 adj_row.track_member_flag := null;
1557 end if;
1558
1559
1560 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
1561 X_last_update_date,
1562 X_last_updated_by,
1563 X_last_update_login
1564 ,p_log_level_rec => p_log_level_rec)) then
1565 fa_srvr_msg.add_message(calling_fn => l_calling_fn
1566 ,p_log_level_rec => p_log_level_rec);
1567 return(FALSE);
1568
1569 end if;
1570
1571 if p_log_level_rec.statement_level then
1572 fa_debug_pkg.add
1573 (fname => 'fagurt',
1574 element => '+++ before faxinaj for REVAL_RSV_RETIRED_GAIN_ACCT',
1575 value => ''
1576 ,p_log_level_rec => p_log_level_rec);
1577 end if;
1578
1579 end if;
1580
1581 end if;
1582
1583 --
1584 -- If this is a member asset, POS and COR needs to be inserted
1585 -- with group asset id. Also group is not the one retired
1586 -- so set other parameters accordingly.
1587 --
1588 if (bk.group_asset_id is not null) and
1589 (nvl(bk.member_rollup_flag, 'N') = 'N') then
1590 adj_row.track_member_flag := null;
1591 adj_row.asset_id := bk.group_asset_id;
1592 adj_row.selection_mode := FA_STD_TYPES.FA_AJ_ACTIVE;
1593 adj_row.selection_retid := 0;
1594 adj_row.units_retired := 0;
1595 end if;
1596
1597 /* BUG 2669638: if ret.proceeds_of_sale > 0 then */
1598 if ret.proceeds_of_sale <> 0 then
1599
1600 adj_row.adjustment_type := 'PROCEEDS CLR';
1601 adj_row.adjustment_amount := ret.proceeds_of_sale;
1602 adj_row.account := proc_of_sale_clearing_acct;
1603 adj_row.account_type := 'PROCEEDS_OF_SALE_CLEARING_ACCT';
1604 adj_row.debit_credit_flag := 'DR';
1605 adj_row.mrc_sob_type_code := ret.mrc_sob_type_code;
1606
1607 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
1608 X_last_update_date,
1609 X_last_updated_by,
1610 X_last_update_login
1611 ,p_log_level_rec => p_log_level_rec)) then
1612 fa_srvr_msg.add_message(calling_fn => l_calling_fn
1613 ,p_log_level_rec => p_log_level_rec);
1614 return(FALSE);
1615 end if;
1616
1617 -- added for bug 3627497
1618 if (bk.group_asset_id is not null) and
1619 (nvl(bk.member_rollup_flag, 'N') = 'N') then
1620 adj_row.adjustment_type := 'PROCEEDS';
1621 adj_row.adjustment_amount := ret.proceeds_of_sale;
1622 adj_row.debit_credit_flag := 'CR';
1623 adj_row.mrc_sob_type_code := ret.mrc_sob_type_code;
1624
1625 if h_dr_cr_flag = 1 then
1626 adj_row.account := proceeds_of_sale_gain_acct;
1627 adj_row.account_type := 'PROCEEDS_OF_SALE_GAIN_ACCT';
1628 else
1629 adj_row.account := proceeds_of_sale_loss_acct;
1630 adj_row.account_type := 'PROCEEDS_OF_SALE_LOSS_ACCT';
1631 end if;
1632
1633 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
1634 X_last_update_date,
1635 X_last_updated_by,
1636 X_last_update_login
1637 ,p_log_level_rec => p_log_level_rec)) then
1638 fa_srvr_msg.add_message(calling_fn => l_calling_fn
1639 ,p_log_level_rec => p_log_level_rec);
1640 return(FALSE);
1641 end if;
1642 end if;
1643 end if;
1644
1645 /* BUG 2669638: if ret.cost_of_removal > 0 then */
1646 if ret.cost_of_removal <> 0 then
1647
1648 adj_row.adjustment_type := 'REMOVALCOST CLR';
1649 adj_row.adjustment_amount := ret.cost_of_removal;
1650 adj_row.account := cost_of_removal_clearing_acct;
1651 adj_row.account_type := 'COST_OF_REMOVAL_CLEARING_ACCT';
1652 adj_row.debit_credit_flag := 'CR';
1653 adj_row.mrc_sob_type_code := ret.mrc_sob_type_code;
1654
1655 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
1656 X_last_update_date,
1657 X_last_updated_by,
1658 X_last_update_login
1659 ,p_log_level_rec => p_log_level_rec)) then
1660 fa_srvr_msg.add_message(calling_fn => l_calling_fn
1661 ,p_log_level_rec => p_log_level_rec);
1662 return(FALSE);
1663
1664 end if;
1665
1666 -- added for bug 3627497
1667 if (bk.group_asset_id is not null) and
1668 (nvl(bk.member_rollup_flag, 'N') = 'N') then
1669 adj_row.adjustment_type := 'REMOVALCOST';
1670 adj_row.adjustment_amount := ret.cost_of_removal;
1671 adj_row.debit_credit_flag := 'DR';
1672 adj_row.mrc_sob_type_code := ret.mrc_sob_type_code;
1673
1674 if h_dr_cr_flag = 1 then
1675 adj_row.account := cost_of_removal_gain_acct;
1676 adj_row.account_type := 'COST_OF_REMOVAL_GAIN_ACCT';
1677 else
1678 adj_row.account := cost_of_removal_loss_acct;
1679 adj_row.account_type := 'COST_OF_REMOVAL_LOSS_ACCT';
1680 end if;
1681
1682 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
1683 X_last_update_date,
1684 X_last_updated_by,
1685 X_last_update_login
1686 ,p_log_level_rec => p_log_level_rec)) then
1687 fa_srvr_msg.add_message(calling_fn => l_calling_fn
1688 ,p_log_level_rec => p_log_level_rec);
1689 return(FALSE);
1690
1691 end if;
1692 end if;
1693
1694 end if;
1695
1696 /* Calculate ITC recaptured */
1697
1698 if (bk.itc_used > 0) and
1699 (ret.wip_asset is null or ret.wip_asset <= 0) then
1700
1701 if not fagitc(ret, bk, cost_frac) then
1702
1703 fa_srvr_msg.add_message(calling_fn => l_calling_fn
1704 ,p_log_level_rec => p_log_level_rec);
1705 return(FALSE);
1706
1707 end if;
1708
1709 end if;
1710
1711 return(TRUE);
1712
1713 EXCEPTION
1714 when others then
1715
1716 if c_ret_amount%ISOPEN then
1717 CLOSE c_ret_amount;
1718 end if;
1719
1720 if c_ret_dists%ISOPEN then
1721 CLOSE c_ret_dists;
1722 end if;
1723
1724 if c_ret_rsv_costs%ISOPEN then
1725 CLOSE c_ret_rsv_costs;
1726 end if;
1727
1728 if c_ret_rsv_costs_mrc%ISOPEN then
1729 CLOSE c_ret_rsv_costs_mrc;
1730 end if;
1731
1732 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn
1733 ,p_log_level_rec => p_log_level_rec);
1734 return FALSE;
1735
1736 END FAGURT;
1737
1738 /*===========================================================================*
1739 | NAME fagpct |
1740 | |
1741 | FUNCTION Add a cost retired adjustment into FA_ADJUSTMENTS. Credit to |
1742 | the asset account. |
1743 | |
1744 | HISTORY 08/30/89 R Rumanang Created |
1745 | 05/03/91 M Chan Rewrote for MPL 9 |
1746 | 12/31/96 S Behura Rewrote in PL/SQL |
1747 | |
1748 *============================================================================*/
1749
1750 FUNCTION fagpct(ret in out nocopy fa_ret_types.ret_struct,
1751 bk in out nocopy fa_ret_types.book_struct,
1752 cpd_ctr number, today in date,
1753 user_id number,
1754 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) return boolean IS
1755
1756 fagpct_err exception;
1757
1758 asset_cost_acct varchar2(26);
1759 cip_cost_acct varchar2(26);
1760 -- adj_row FA_STD_TYPES.fa_adj_row_struct;
1761 adj_row FA_ADJUST_TYPE_PKG.fa_adj_row_struct;
1762
1763 h_asset_id number(15);
1764 h_ret_id number(15);
1765 h_book varchar2(15);
1766 h_id_out number;
1767 h_cip_cost_acct varchar2(26);
1768 h_asset_cost_acct varchar2(26);
1769 h_cur_units number;
1770
1771 h_cost_retired number;
1772 h_adjustment_amount number;
1773 h_adjustment_ccid number;
1774
1775 l_balance_tfr_in_tax number;
1776 l_unit_ret_in_corp boolean;
1777 l_ret_id_in_corp number;
1778
1779 X_LAST_UPDATE_DATE date := sysdate;
1780 X_last_updated_by number := -1;
1781 X_last_update_login number := -1;
1782
1783 l_calling_fn varchar2(40) := 'FA_GAINLOSS_UPD_PKG.fagpct';
1784
1785 l_dummy number;
1786
1787 BEGIN <<FAGPCT>>
1788
1789 h_cur_units := 0;
1790 h_asset_id := ret.asset_id;
1791 h_ret_id := ret.retirement_id;
1792 h_book := ret.book;
1793
1794 if p_log_level_rec.statement_level then
1795 fa_debug_pkg.add
1796 (fname => l_calling_fn,
1797 element => 'acct segment in fagpct',
1798 value => ''
1799 ,p_log_level_rec => p_log_level_rec);
1800 end if;
1801
1802 select asset_cost_acct,
1803 nvl(cip_cost_acct, '0')
1804 into h_asset_cost_acct,
1805 h_cip_cost_acct
1806 from fa_additions_b faadd,
1807 fa_category_books facb
1808 where faadd.asset_id = h_asset_id
1809 and facb.category_id = faadd.asset_category_id
1810 and facb.book_type_code = h_book;
1811
1812 asset_cost_acct := h_asset_cost_acct;
1813 cip_cost_acct := h_cip_cost_acct;
1814
1815 adj_row.transaction_header_id := ret.th_id_in;
1816 adj_row.book_type_code := ret.book;
1817 adj_row.period_counter_created := cpd_ctr;
1818 adj_row.asset_id := ret.asset_id;
1819 adj_row.period_counter_adjusted := cpd_ctr;
1820 adj_row.last_update_date := today;
1821 adj_row.current_units := bk.cur_units;
1822 adj_row.gen_ccid_flag := TRUE;
1823 adj_row.flush_adj_flag := TRUE;
1824 adj_row.annualized_adjustment := 0;
1825 adj_row.code_combination_id := 0;
1826 adj_row.distribution_id := 0;
1827 adj_row.selection_retid := 0;
1828 adj_row.units_retired := 0;
1829 adj_row.asset_invoice_id := 0;
1830 adj_row.leveling_flag := FALSE;
1831
1832 if ret.wip_asset > 0 then
1833 adj_row.source_type_code := 'CIP RETIREMENT';
1834 adj_row.account := cip_cost_acct;
1835 adj_row.account_type := 'CIP_COST_ACCT';
1836 else
1837 adj_row.source_type_code := 'RETIREMENT';
1838 adj_row.account := asset_cost_acct;
1839 adj_row.account_type := 'ASSET_COST_ACCT';
1840 end if;
1841
1842 adj_row.adjustment_type := 'COST';
1843 adj_row.adjustment_amount := ret.cost_retired;
1844
1845 if (ret.units_retired <= 0 or ret.units_retired is null) then
1846 adj_row.selection_thid := 0;
1847 adj_row.debit_credit_flag := 'CR';
1848
1849 if (bk.current_cost = ret.cost_retired) then
1850 adj_row.selection_mode := FA_STD_TYPES.FA_AJ_CLEAR;
1851 else
1852
1853 adj_row.selection_mode := FA_STD_TYPES.FA_AJ_ACTIVE;
1854
1855 if (bk.book_class) then
1856 if NOT faginfo(
1857 RET, BK, cpd_ctr,today, user_id
1858 ,calling_module => l_calling_fn
1859 ,candidate_mode => 'CLEAR_PARTIAL'
1860 ,set_adj_row => TRUE
1861 ,unit_ret_in_corp => l_unit_ret_in_corp
1862 ,ret_id_in_corp => l_ret_id_in_corp
1863 ,th_id_out_in_corp => h_id_out
1864 ,balance_tfr_in_tax => l_balance_tfr_in_tax
1865 ,adj_row => adj_row
1866 ,p_log_level_rec => p_log_level_rec
1867 ) then
1868 fa_srvr_msg.add_message(calling_fn => l_calling_fn);
1869 return(FALSE);
1870 end if;
1871 end if;
1872
1873 end if;
1874
1875 if p_log_level_rec.statement_level then
1876 fa_debug_pkg.add(l_calling_fn, '++ bk.book_class (TRUE=TAX)', bk.book_class, p_log_level_rec);
1877 fa_debug_pkg.add(l_calling_fn, '++ ret.th_id_in', ret.th_id_in, p_log_level_rec);
1878 fa_debug_pkg.add(l_calling_fn, '++ h_id_out=th_id_out_in_corp', h_id_out, p_log_level_rec);
1879 fa_debug_pkg.add(l_calling_fn, '++ adj_row.selection_thid', adj_row.selection_thid, p_log_level_rec);
1880 end if;
1881
1882
1883 adj_row.mrc_sob_type_code := ret.mrc_sob_type_code;
1884
1885 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
1886 X_last_update_date,
1887 X_last_updated_by,
1888 X_last_update_login
1889 ,p_log_level_rec => p_log_level_rec)) then
1890 fa_srvr_msg.add_message(calling_fn => l_calling_fn
1891 ,p_log_level_rec => p_log_level_rec);
1892 return(FALSE);
1893
1894 end if;
1895
1896 else
1897 select distinct nvl(transaction_header_id_out,0)
1898 into h_id_out
1899 from fa_distribution_history
1900 where asset_id = h_asset_id
1901 and book_type_code = h_book
1902 and retirement_id = h_ret_id;
1903
1904 /* Fix for Bug#4617352: We have decided to create adj lines only for affected rows
1905 to avoid rounding issues with remaining rows in partial unit intercompany retirement.
1906 */
1907 adj_row.selection_thid := h_id_out;
1908 adj_row.debit_credit_flag := 'CR';
1909 adj_row.selection_mode := FA_STD_TYPES.FA_AJ_CLEAR_PARTIAL;
1910 adj_row.mrc_sob_type_code := ret.mrc_sob_type_code;
1911
1912 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
1913 X_last_update_date,
1914 X_last_updated_by,
1915 X_last_update_login
1916 ,p_log_level_rec => p_log_level_rec)) then
1917 fa_srvr_msg.add_message(calling_fn => l_calling_fn
1918 ,p_log_level_rec => p_log_level_rec);
1919 return(FALSE);
1920
1921 end if;
1922
1923 adj_row.adjustment_amount := adj_row.amount_inserted-
1924 ret.cost_retired;
1925
1926 /*
1927 select nvl(units,0)
1928 into h_cur_units
1929 from fa_asset_history
1930 where asset_id = h_asset_id
1931 and date_ineffective is null;
1932 */
1933 h_cur_units := 0;
1934
1935 begin
1936 select count(*)
1937 into l_dummy
1938 from fa_distribution_history
1939 where asset_id = h_asset_id
1940 and date_ineffective is null
1941 and transaction_header_id_in = h_id_out;
1942
1943 select sum(nvl(units_assigned,0))
1944 into h_cur_units
1945 from fa_distribution_history
1946 where asset_id = h_asset_id
1947 and date_ineffective is null
1948 and transaction_header_id_in = h_id_out;
1949
1950 exception
1951 when no_data_found then
1952 h_cur_units := 0;
1953 end;
1954
1955 if p_log_level_rec.statement_level then
1956 fa_debug_pkg.add
1957 (fname => l_calling_fn,
1958 element => 'h_cur_units before THE partial unit ret condition',
1959 value => h_cur_units
1960 ,p_log_level_rec => p_log_level_rec);
1961 end if;
1962
1963 if (h_cur_units <>0) then
1964
1965 adj_row.current_units := h_cur_units;
1966 --adj_row.selection_thid := 0;
1967 adj_row.selection_thid := h_id_out;
1968 adj_row.debit_credit_flag := 'DR';
1969 adj_row.selection_mode := FA_STD_TYPES.FA_AJ_ACTIVE_PARTIAL;
1970 adj_row.leveling_flag := FALSE;
1971 adj_row.mrc_sob_type_code := ret.mrc_sob_type_code;
1972
1973 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
1974 X_last_update_date,
1975 X_last_updated_by,
1976 X_last_update_login
1977 ,p_log_level_rec => p_log_level_rec)) then
1978 fa_srvr_msg.add_message(calling_fn => l_calling_fn
1979 ,p_log_level_rec => p_log_level_rec);
1980 return(FALSE);
1981
1982 end if;
1983
1984 end if; -- if h_cur_units <> 0
1985
1986
1987 end if;
1988
1989 return (TRUE);
1990
1991 EXCEPTION
1992
1993 when others then
1994
1995 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn
1996 ,p_log_level_rec => p_log_level_rec);
1997 return FALSE;
1998
1999 END FAGPCT;
2000
2001 /*======================================================================*
2002 | Name farboe |
2003 | |
2004 | Function |
2005 | This function is to calculate the back out deprn_expense, |
2006 | reval_expense, and reval_amort we have taken so far and |
2007 | insert them into fa_adjustments table. |
2008 | |
2009 | History 11/13/92 L. Sun Created |
2010 | |
2011 | 12/31/96 S. Behura Rewrote into PL/SQL |
2012 | 11/08/97 S. Behura Rewrote into PL/SQL(10.7)|
2013 *=====================================================================*/
2014
2015 FUNCTION farboe(asset_id number, book in varchar2,
2016 current_fiscal_yr number, cost_frac in number,
2017 start_pdnum number, end_pdnum number,
2018 adj_type in varchar2, pds_per_year number,
2019 dpr_evenly number, fiscal_year_name in varchar2,
2020 units_retired number, th_id_in number,
2021 cpd_ctr number, today in date,
2022 current_units number, retirement_id number, d_cal in varchar2,
2023 dpr in out nocopy FA_STD_TYPES.dpr_struct, p_cal in varchar2,
2024 pds_catchup number, depreciate_lastyr boolean,
2025 start_pp number, end_pp number,
2026 mrc_sob_type_code in varchar2,
2027 ret in fa_ret_types.ret_struct,
2028 bk in out nocopy fa_ret_types.book_struct,
2029 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) Return BOOLEAN IS
2030
2031 farboe_err exception;
2032
2033 i integer;
2034 m integer;
2035 j integer;
2036 dpr_detail_counter number;
2037 dpr_detail_size number;
2038 dpr_detail fa_RET_TYPES.dpr_detail_struct;
2039 in_dpr_detail number;
2040 target_dpr_detail number;
2041 expand_array number;
2042 tot_backup_deprn number;
2043 backup_deprn number;
2044 frac_of_fiscal_year number;
2045 -- adj_row FA_STD_TYPES.fa_adj_row_struct;
2046 adj_row FA_ADJUST_TYPE_PKG.fa_adj_row_struct;
2047 period_fracs FA_STD_TYPES.table_fa_cp_struct;
2048
2049 -- dummy FA_STD_TYPES.dpr_arr_type;
2050 dummy_num number;
2051 dummy_num2 number;
2052 dpr_out FA_STD_TYPES.dpr_out_struct;
2053 deprn_adjustment number;
2054
2055 temp_frac number;
2056 temp_pds number;
2057 y_begin integer;
2058 pp_begin integer;
2059 dpy_begin integer;
2060 dpp_begin integer;
2061 --fy_name varchar2(20); -- bug 2719715
2062 /* the length of fy_name should be equal to
2063 the lenght of FA_FISCAL_YEAR.FISCAL_YEAR_NAME varchar2(30) */
2064 --fy_name varchar2(30); -- bug 2719715
2065 fy_name FA_FISCAL_YEAR.FISCAL_YEAR_NAME%TYPE;
2066
2067 temp_start_pp number;
2068 source_type_code varchar2(30);
2069 prev_dist_id number;
2070 bonus_deprn_exp_acct varchar2(30);
2071 deprn_exp_acct varchar2(30);
2072
2073 h_start_pdnum number;
2074 h_end_pdnum number;
2075 h_i number;
2076 h_cost_frac number;
2077 h_asset_id number(15);
2078 h_book varchar2(16);
2079 h_bonus_deprn_exp_acct varchar2(30);
2080 h_current_fiscal_yr number;
2081 h_adj_type varchar2(16);
2082 h_adj_amount number;
2083 h_dist_id number(15);
2084 h_ccid number;
2085 h_deprn_amount number;
2086 h_annualized_adj number;
2087 h_tot_deprn number;
2088 h_units_retired number(15);
2089 h_retirement_id number(15);
2090 h_cpd_ctr number(15);
2091 h_same_fy integer;
2092 h_rate_source_rule integer;
2093 h_dwacq integer;
2094 h_depr_first_year_ret integer;
2095 h_adj_count number;
2096 h_ret_count number;
2097 h_curr_pd_add number;
2098 h_currpd_amount number;
2099 h_net_deprn_amount number;
2100 h_source_type_code varchar2(30);
2101 h_deprn_exp_acct varchar2(30);
2102 h_prior_pd_tfr number;
2103 h_curr_pd_reinst number;
2104 h_old_reinst_trx_id number;
2105 h_old_reinst_pc number;
2106 h_old_ret_pc number;
2107 h_ret_prorate_pc number;
2108 h_no_of_per_to_exclude number;
2109
2110 -- Fix for 4259471
2111 k integer;
2112 h_start_pd_endpp number;
2113 h_start_pd_deprn number;
2114 h_temp_startpd_deprn number;
2115 h_ret_pp number;
2116 h_ret_pjdate number;
2117 h_pc number;
2118 h_start_pd_pc number;
2119 deprn_amt number;
2120 bonus_deprn_amt number;
2121 reval_deprn_amt number;
2122 reval_amort number;
2123 p_pers_per_yr number(3);
2124 h_dpr_temp FA_STD_TYPES.dpr_struct;
2125 h_amt_to_retain number;
2126 h_temp_end_pp number;
2127
2128
2129 --bug fix 3558253 and 3518604 start
2130 h_temp_calc number;
2131 h_adj_exp_row number;
2132 h_prior_fy_exp number;
2133 h_backout_flag number;
2134 h_ytd_deprn number;
2135 h_bonus_ytd_deprn number; -- bug 3846296
2136 h_pd_num number;
2137 h_temp_deprn_tot number;
2138 h_fiscal_year number;
2139 --bug fix 3558253 and 3518604 end
2140
2141 h_Brow_deprn_reserve number; /*Bug 7595090 Declred this variable*/
2142
2143 deprn_start_pnum number;
2144 deprn_start_fy number;
2145
2146 l_deprn_exp number;
2147 l_bonus_deprn_exp number;
2148 l_impairment_exp number;
2149 l_asset_fin_rec_new FA_API_TYPES.asset_fin_rec_type;
2150
2151 h_id_out number;
2152 l_balance_tfr_in_tax number;
2153 l_unit_ret_in_corp boolean;
2154 l_ret_id_in_corp number;
2155
2156
2157 X_LAST_UPDATE_DATE date := sysdate;
2158 X_last_updated_by number := -1;
2159 X_last_update_login number := -1;
2160
2161
2162 /* The second half of the union was added in order to obtain
2163 * adjustments amounts for the current open period. This is
2164 * neccessary since that period would not have deprn_detail
2165 * rows yet, and the original select statement would not have
2166 * returned these adjustment amounts. --y.i.
2167 */
2168 CURSOR DEPRN_ADJ IS
2169 SELECT fadd.distribution_id,
2170 fadh.code_combination_id,
2171 -1 * h_cost_frac *
2172 (decode (h_adj_type,
2173 'EXPENSE', fadd.deprn_amount,
2174 'BONUS EXPENSE', fadd.bonus_deprn_amount,
2175 'REVAL EXPENSE', fadd.reval_deprn_expense,
2176 'REVAL AMORT', fadd.reval_amortization) -
2177 nvl(sum(decode (faadj.debit_credit_flag, 'DR', 1, -1) *
2178 faadj.adjustment_amount), 0)),
2179 nvl(SUM(-1 * h_cost_frac *
2180 decode (faadj.debit_credit_flag, 'DR', 1, -1) *
2181 faadj.adjustment_amount), 0),
2182 nvl(SUM(-1 * h_cost_frac *
2183 decode (faadj.debit_credit_flag, 'DR', 1, -1) *
2184 faadj.annualized_adjustment), 0),
2185 nvl(-1 * h_cost_frac *
2186 (decode (h_adj_type,
2187 'EXPENSE',(fadd.deprn_amount -
2188 fadd.deprn_adjustment_amount),
2189 'BONUS EXPENSE',(fadd.bonus_deprn_amount -
2190 fadd.bonus_deprn_adjustment_amount),
2191 'REVAL EXPENSE', fadd.reval_deprn_expense,
2192 'REVAL AMORT', fadd.reval_amortization)),0),
2193 nvl(-1 * h_cost_frac *
2194 (decode (h_adj_type,
2195 'EXPENSE', fadd.deprn_amount,
2196 'BONUS EXPENSE', fadd.bonus_deprn_amount,
2197 'REVAL EXPENSE', fadd.reval_deprn_expense,
2198 'REVAL AMORT', fadd.reval_amortization)),0),
2199 nvl(faadj.source_type_code, 'DEPRECIATION'),
2200 fadp.period_counter
2201 FROM
2202 fa_distribution_history fadh,
2203 fa_deprn_detail fadd,
2204 fa_deprn_periods fadp,
2205 fa_adjustments faadj
2206 WHERE
2207 fadd.asset_id = h_asset_id
2208 AND fadd.distribution_id = fadh.distribution_id
2209 AND fadd.book_type_code = h_book
2210 AND fadd.deprn_source_code = 'D'
2211 AND fadd.period_counter = fadp.period_counter
2212 AND fadp.period_num = h_i
2213 AND fadp.book_type_code = h_book
2214 AND fadp.fiscal_year = h_current_fiscal_yr
2215 AND faadj.distribution_id(+) = fadd.distribution_id
2216 AND faadj.book_type_code(+) = fadd.book_type_code
2217 AND faadj.asset_id(+) = fadd.asset_id
2218 AND faadj.period_counter_created(+) = fadd.period_counter
2219 AND faadj.adjustment_type(+) = h_adj_type
2220 GROUP BY
2221 fadd.distribution_id,
2222 fadh.code_combination_id,
2223 fadd.deprn_amount,
2224 fadd.deprn_adjustment_amount,
2225 fadd.bonus_deprn_amount,
2226 fadd.bonus_deprn_adjustment_amount,
2227 fadd.reval_deprn_expense,
2228 fadd.reval_amortization,
2229 faadj.distribution_id,
2230 faadj.source_type_Code,
2231 faadj.adjustment_amount,
2232 fadp.period_counter
2233 UNION
2234 SELECT fadh.distribution_id,
2235 fadh.code_combination_id,
2236 0,
2237 nvl(SUM(-1 * h_cost_frac *
2238 decode (faadj.debit_credit_flag, 'DR', 1, -1) *
2239 faadj.adjustment_amount), 0),
2240 nvl(SUM(-1 * h_cost_frac *
2241 decode (faadj.debit_credit_flag, 'DR', 1, -1) *
2242 faadj.annualized_adjustment), 0),
2243 0,
2244 0,
2245 nvl(faadj.source_type_code, 'DEPRECIATION'),
2246 fadp.period_counter
2247 FROM
2248 fa_distribution_history fadh,
2249 fa_deprn_periods fadp,
2250 fa_adjustments faadj
2251 WHERE
2252 fadp.period_num = h_i
2253 AND fadp.book_type_code = h_book
2254 AND fadp.fiscal_year = h_current_fiscal_yr
2255 AND fadp.period_counter = h_cpd_ctr
2256 AND faadj.distribution_id = fadh.distribution_id
2257 AND faadj.book_type_code = fadp.book_type_code
2258 AND faadj.asset_id = h_asset_id
2259 AND faadj.period_counter_created = fadp.period_counter
2260 AND faadj.adjustment_type = h_adj_type
2261 GROUP BY
2262 fadh.distribution_id,
2263 fadh.code_combination_id,
2264 faadj.distribution_id,
2265 faadj.source_type_Code,
2266 faadj.adjustment_amount,
2267 fadp.period_counter;
2268
2269 CURSOR MRC_DEPRN_ADJ IS
2270 SELECT fadd.distribution_id,
2271 fadh.code_combination_id,
2272 -1 * h_cost_frac *
2273 (decode (h_adj_type,
2274 'EXPENSE', fadd.deprn_amount,
2275 'BONUS EXPENSE', fadd.bonus_deprn_amount,
2276 'REVAL EXPENSE', fadd.reval_deprn_expense,
2277 'REVAL AMORT', fadd.reval_amortization) -
2278 nvl(sum(decode (faadj.debit_credit_flag, 'DR', 1, -1) *
2279 faadj.adjustment_amount), 0)),
2280 nvl(SUM(-1 * h_cost_frac *
2281 decode (faadj.debit_credit_flag, 'DR', 1, -1) *
2282 faadj.adjustment_amount), 0),
2283 nvl(SUM(-1 * h_cost_frac *
2284 decode (faadj.debit_credit_flag, 'DR', 1, -1) *
2285 faadj.annualized_adjustment), 0),
2286 nvl(-1 * h_cost_frac *
2287 (decode (h_adj_type,
2288 'EXPENSE',(fadd.deprn_amount -
2289 fadd.deprn_adjustment_amount),
2290 'BONUS EXPENSE',(fadd.bonus_deprn_amount -
2291 fadd.bonus_deprn_adjustment_amount),
2292 'REVAL EXPENSE', fadd.reval_deprn_expense,
2293 'REVAL AMORT', fadd.reval_amortization)),0),
2294 nvl(-1 * h_cost_frac *
2295 (decode (h_adj_type,
2296 'EXPENSE', fadd.deprn_amount,
2297 'BONUS EXPENSE', fadd.bonus_deprn_amount,
2298 'REVAL EXPENSE', fadd.reval_deprn_expense,
2299 'REVAL AMORT', fadd.reval_amortization)),0),
2300 nvl(faadj.source_type_code, 'DEPRECIATION'),
2301 fadp.period_counter
2302 FROM
2303 fa_distribution_history fadh,
2304 fa_deprn_detail_mrc_v fadd,
2305 fa_deprn_periods fadp,
2306 fa_adjustments_mrc_v faadj
2307 WHERE
2308 fadd.asset_id = h_asset_id
2309 AND fadd.distribution_id = fadh.distribution_id
2310 AND fadd.book_type_code = h_book
2311 AND fadd.deprn_source_code = 'D'
2312 AND fadd.period_counter = fadp.period_counter
2313 AND fadp.period_num = h_i
2314 AND fadp.book_type_code = h_book
2315 AND fadp.fiscal_year = h_current_fiscal_yr
2316 AND faadj.distribution_id(+) = fadd.distribution_id
2317 AND faadj.book_type_code(+) = fadd.book_type_code
2318 AND faadj.asset_id(+) = fadd.asset_id
2319 AND faadj.period_counter_created(+) = fadd.period_counter
2320 AND faadj.adjustment_type(+) = h_adj_type
2321 GROUP BY
2322 fadd.distribution_id,
2323 fadh.code_combination_id,
2324 fadd.deprn_amount,
2325 fadd.deprn_adjustment_amount,
2326 fadd.bonus_deprn_amount,
2327 fadd.bonus_deprn_adjustment_amount,
2328 fadd.reval_deprn_expense,
2329 fadd.reval_amortization,
2330 faadj.distribution_id,
2331 faadj.source_type_Code,
2332 faadj.adjustment_amount,
2333 fadp.period_counter
2334 UNION
2335 SELECT fadh.distribution_id,
2336 fadh.code_combination_id,
2337 0,
2338 nvl(SUM(-1 * h_cost_frac *
2339 decode (faadj.debit_credit_flag, 'DR', 1, -1) *
2340 faadj.adjustment_amount), 0),
2341 nvl(SUM(-1 * h_cost_frac *
2342 decode (faadj.debit_credit_flag, 'DR', 1, -1) *
2343 faadj.annualized_adjustment), 0),
2344 0,
2345 0,
2346 nvl(faadj.source_type_code, 'DEPRECIATION'),
2347 fadp.period_counter
2348 FROM
2349 fa_distribution_history fadh,
2350 fa_deprn_periods fadp,
2351 fa_adjustments_mrc_v faadj
2352 WHERE
2353 fadp.period_num = h_i
2354 AND fadp.book_type_code = h_book
2355 AND fadp.fiscal_year = h_current_fiscal_yr
2356 AND fadp.period_counter = h_cpd_ctr
2357 AND faadj.distribution_id = fadh.distribution_id
2358 AND faadj.book_type_code = fadp.book_type_code
2359 AND faadj.asset_id = h_asset_id
2360 AND faadj.period_counter_created = fadp.period_counter
2361 AND faadj.adjustment_type = h_adj_type
2362 GROUP BY
2363 fadh.distribution_id,
2364 fadh.code_combination_id,
2365 faadj.distribution_id,
2366 faadj.source_type_Code,
2367 faadj.adjustment_amount,
2368 fadp.period_counter;
2369
2370
2371 l_calling_fn varchar2(40) := 'FA_GAINLOSS_UPD_PKG.farboe';
2372
2373 BEGIN <<FARBOE>>
2374
2375
2376 if (p_log_level_rec.statement_level) then
2377 fa_debug_pkg.add(l_calling_fn, 'in farboe', '', p_log_level_rec);
2378 fa_debug_pkg.add('farboe','IN FARBOE',1
2379 ,p_log_level_rec => p_log_level_rec);
2380 fa_debug_pkg.add('farboe','start_pp',start_pp
2381 ,p_log_level_rec => p_log_level_rec);
2382 fa_debug_pkg.add('farboe','end_pp',end_pp
2383 ,p_log_level_rec => p_log_level_rec);
2384 fa_debug_pkg.add('farboe','pds_catchup',pds_catchup
2385 ,p_log_level_rec => p_log_level_rec);
2386 end if;
2387
2388 tot_backup_deprn := 0;
2389 backup_deprn := 0;
2390 frac_of_fiscal_year := 0;
2391 h_cost_frac := 0;
2392 h_adj_amount := 0;
2393 h_deprn_amount := 0;
2394 h_annualized_adj := 0;
2395 h_tot_deprn := 0;
2396 h_temp_calc := 0;----bug fix 3558253 and 3518604 start
2397 h_adj_exp_row := 0;
2398 h_prior_fy_exp := 0;
2399 h_backout_flag := 1;
2400 h_ytd_deprn := 0;
2401 h_bonus_ytd_deprn := 0;
2402 h_pd_num := 1;
2403 h_temp_deprn_tot := 0;--bug fix 3558253 and 3518604 end
2404 h_start_pdnum := start_pdnum;
2405 h_end_pdnum := end_pdnum;
2406 h_cost_frac := cost_frac;
2407 h_asset_id := asset_id;
2408 h_book := book;
2409 h_current_fiscal_yr := current_fiscal_yr;
2410 h_adj_type := adj_type;
2411 h_units_retired := units_retired;
2412 h_retirement_id := retirement_id;
2413 h_cpd_ctr := cpd_ctr;
2414 h_Brow_deprn_reserve := 0; /*Bug 7595090 Initialized this variable to 0*/
2415
2416 if (p_log_level_rec.statement_level) then
2417 fa_debug_pkg.add('farboe','h_start_pdnum',h_start_pdnum
2418 ,p_log_level_rec => p_log_level_rec);
2419 fa_debug_pkg.add('farboe','h_end_pdnum',h_end_pdnum
2420 ,p_log_level_rec => p_log_level_rec);
2421 end if;
2422
2423
2424 expand_array := 2;
2425
2426 for m in 1.. FA_RET_TYPES.FA_DPR_DETAIL_SIZE loop
2427
2428 dpr_detail.dist_id(m) := 0;
2429 dpr_detail.ccid(m) := 0;
2430 dpr_detail.deprn_amount(m) := 0;
2431 dpr_detail.adj_amount(m) := 0;
2432 dpr_detail.annualized_adj(m) := 0;
2433
2434 end loop;
2435
2436 dpr_detail_size := FA_RET_TYPES.FA_DPR_DETAIL_SIZE;
2437 dpr_detail_counter := 1;
2438
2439 --bug fix 3558253 and 3518604 start
2440
2441 if mrc_sob_type_code <> 'R' then
2442
2443 SELECT ytd_deprn, period_num, bonus_ytd_deprn, fiscal_year,
2444 deprn_reserve
2445 INTO h_ytd_deprn, h_pd_num, h_bonus_ytd_deprn, h_fiscal_year,
2446 h_Brow_deprn_reserve /*Bug 7595090 added this variable to get initial depreciation reserve*/
2447 FROM
2448 fa_deprn_summary ds,
2449 fa_deprn_periods dp
2450 WHERE
2451 ds.asset_id = h_asset_id
2452 AND ds.book_type_code = h_book
2453 AND ds.deprn_source_code = 'BOOKS'
2454 AND dp.book_type_code = h_book
2455 AND dp.period_counter = ds.period_counter;
2456
2457 else
2458
2459 SELECT ytd_deprn,period_num, bonus_ytd_deprn, fiscal_year,
2460 deprn_reserve
2461 INTO h_ytd_deprn, h_pd_num, h_bonus_ytd_deprn, h_fiscal_year,
2462 h_Brow_deprn_reserve /*Bug 7595090 added this variable to get initial depreciation reserve*/
2463 FROM
2464 fa_deprn_summary_mrc_v ds,
2465 fa_deprn_periods dp
2466 WHERE
2467 ds.asset_id = h_asset_id
2468 AND ds.book_type_code = h_book
2469 AND ds.deprn_source_code = 'BOOKS'
2470 AND dp.book_type_code = h_book
2471 AND dp.period_counter = ds.period_counter;
2472
2473 end if;
2474
2475 --bug fix 3558253 and 3518604 end
2476
2477 for i in reverse h_start_pdnum..h_end_pdnum loop
2478
2479 h_i := i;
2480 prev_dist_id := 0;
2481
2482
2483 if (p_log_level_rec.statement_level) then
2484 fa_debug_pkg.add('farboe','backing out pd',h_i
2485 ,p_log_level_rec => p_log_level_rec);
2486 fa_debug_pkg.add('farboe','h_asset_id',h_asset_id
2487 ,p_log_level_rec => p_log_level_rec);
2488 fa_debug_pkg.add('farboe','h_book',h_book
2489 ,p_log_level_rec => p_log_level_rec);
2490 fa_debug_pkg.add('farboe','h_adj_type',h_adj_type
2491 ,p_log_level_rec => p_log_level_rec);
2492 fa_debug_pkg.add('farboe','h_current_fiscal_yr',h_current_fiscal_yr
2493 ,p_log_level_rec => p_log_level_rec);
2494 fa_debug_pkg.add('farboe','h_cpd_ctr',h_cpd_ctr
2495 ,p_log_level_rec => p_log_level_rec);
2496 fa_debug_pkg.add('farboe','h_i',h_i
2497 ,p_log_level_rec => p_log_level_rec);
2498 end if;
2499
2500 if mrc_sob_type_code <> 'R' then
2501 open DEPRN_ADJ;
2502 else
2503 open MRC_DEPRN_ADJ;
2504 end if;
2505
2506
2507 if (p_log_level_rec.statement_level) then
2508 fa_debug_pkg.add('farboe','after open deprn_adj',1
2509 ,p_log_level_rec => p_log_level_rec);
2510 end if;
2511
2512 /* Get the number of adjustments in period being backed out
2513 Fix for 807256 */
2514
2515 if mrc_sob_type_code <> 'R' then
2516
2517 SELECT count(*)
2518 INTO h_ret_count
2519 FROM
2520 fa_deprn_periods fadp,
2521 fa_adjustments faadj
2522 WHERE
2523 fadp.period_num = h_i
2524 AND fadp.book_type_code = h_book
2525 AND fadp.fiscal_year = h_current_fiscal_yr
2526 AND faadj.book_type_code = h_book
2527 AND faadj.asset_id = h_asset_id
2528 AND faadj.period_counter_created = fadp.period_counter
2529 AND faadj.adjustment_type = h_adj_type
2530 AND faadj.source_type_code = 'RETIREMENT'
2531 AND faadj.adjustment_amount <> 0;
2532
2533 SELECT count(*)
2534 INTO h_adj_count
2535 FROM
2536 fa_deprn_periods fadp,
2537 fa_adjustments faadj
2538 WHERE
2539 fadp.period_num = h_i
2540 AND fadp.book_type_code = h_book
2541 AND fadp.fiscal_year = h_current_fiscal_yr
2542 AND faadj.book_type_code = h_book
2543 AND faadj.asset_id = h_asset_id
2544 AND faadj.period_counter_created = fadp.period_counter
2545 AND faadj.adjustment_type = h_adj_type
2546 AND faadj.source_type_code <> 'RETIREMENT'
2547 AND faadj.adjustment_amount <> 0;
2548
2549 else
2550
2551 SELECT count(*)
2552 INTO h_ret_count
2553 FROM
2554 fa_deprn_periods fadp,
2555 fa_adjustments_mrc_v faadj
2556 WHERE
2557 fadp.period_num = h_i
2558 AND fadp.book_type_code = h_book
2559 AND fadp.fiscal_year = h_current_fiscal_yr
2560 AND faadj.book_type_code = h_book
2561 AND faadj.asset_id = h_asset_id
2562 AND faadj.period_counter_created = fadp.period_counter
2563 AND faadj.adjustment_type = h_adj_type
2564 AND faadj.source_type_code = 'RETIREMENT'
2565 AND faadj.adjustment_amount <> 0;
2566
2567 SELECT count(*)
2568 INTO h_adj_count
2569 FROM
2570 fa_deprn_periods fadp,
2571 fa_adjustments_mrc_v faadj
2572 WHERE
2573 fadp.period_num = h_i
2574 AND fadp.book_type_code = h_book
2575 AND fadp.fiscal_year = h_current_fiscal_yr
2576 AND faadj.book_type_code = h_book
2577 AND faadj.asset_id = h_asset_id
2578 AND faadj.period_counter_created = fadp.period_counter
2579 AND faadj.adjustment_type = h_adj_type
2580 AND faadj.source_type_code <> 'RETIREMENT'
2581 AND faadj.adjustment_amount <> 0;
2582
2583 end if;
2584
2585 -- Fix for Bug #3941213. Check if there are any prior period
2586 -- transfers in this period.
2587 h_prior_pd_tfr := 0;
2588
2589 SELECT count(*)
2590 INTO h_prior_pd_tfr
2591 FROM fa_deprn_periods dp1,
2592 fa_deprn_periods dp2,
2593 fa_transaction_headers th
2594 WHERE th.asset_id = h_asset_id
2595 AND th.book_type_code = h_book
2596 AND th.transaction_type_code = 'TRANSFER'
2597 AND th.date_effective between dp1.period_open_date
2598 and nvl(dp1.period_close_date, sysdate)
2599 AND dp1.book_type_code = th.book_type_code
2600 AND dp1.period_num = h_i
2601 AND dp1.fiscal_year = h_current_fiscal_yr
2602 AND th.transaction_date_entered between
2603 dp2.calendar_period_open_date and
2604 dp2.calendar_period_close_date
2605 AND dp2.book_type_code = th.book_type_code
2606 AND dp2.period_num < h_i;
2607
2608 --while deprn_adj%FOUND loop
2609 loop
2610
2611 if (p_log_level_rec.statement_level) then
2612 fa_debug_pkg.add('farboe','top of while loop',1
2613 ,p_log_level_rec => p_log_level_rec);
2614 end if;
2615
2616 h_adj_amount := 0;
2617 h_deprn_amount := 0;
2618 h_annualized_adj := 0;
2619
2620 if mrc_sob_type_code <> 'R' then
2621
2622 fetch DEPRN_ADJ into
2623 h_dist_id,
2624 h_ccid,
2625 h_deprn_amount,
2626 h_adj_amount,
2627 h_annualized_adj,
2628 h_currpd_amount,
2629 h_net_deprn_amount,
2630 h_source_type_code,
2631 h_pc;
2632
2633 if (DEPRN_ADJ%NOTFOUND) then
2634 --bug fix 3558253 and 3518604 start
2635 if ((h_ytd_deprn <> 0) and (h_pd_num <> 0) and
2636 (h_pd_num >= h_i) and
2637 (h_fiscal_year = h_current_fiscal_yr) and
2638 (DEPRN_ADJ%ROWCOUNT = 0)) then
2639
2640 -- Fix for Bug #4601712: h_start_pdnum should be taken into account for assets added in the middle of the current fy
2641 -- Fix for Bug #3846296. Separate out bonus.
2642 if (adj_type = 'BONUS EXPENSE') then
2643
2644 h_temp_deprn_tot := nvl(h_temp_deprn_tot,0) +
2645 ((nvl(h_bonus_ytd_deprn,0) / (h_pd_num - h_start_pdnum + 1)) *
2646 h_cost_frac);
2647 else
2648
2649 h_temp_deprn_tot := nvl(h_temp_deprn_tot,0) +
2650 ((h_ytd_deprn / (h_pd_num - h_start_pdnum + 1)) * h_cost_frac);
2651 end if;
2652 end if;
2653 --bug fix 3558253 and 3518604 end
2654
2655 exit;
2656 end if;
2657
2658 else
2659
2660 fetch MRC_DEPRN_ADJ into
2661 h_dist_id,
2662 h_ccid,
2663 h_deprn_amount,
2664 h_adj_amount,
2665 h_annualized_adj,
2666 h_currpd_amount,
2667 h_net_deprn_amount,
2668 h_source_type_code,
2669 h_pc;
2670
2671 if (MRC_DEPRN_ADJ%NOTFOUND) then
2672 --bug fix 3558253 and 3518604 start
2673 if ((h_ytd_deprn <> 0) and (h_pd_num <> 0) and
2674 (h_pd_num >= h_i) and
2675 (h_fiscal_year = h_current_fiscal_yr) and
2676 (MRC_DEPRN_ADJ%ROWCOUNT = 0)) then
2677
2678 -- Fix for Bug #4601712: h_start_pdnum should be taken into account for assets added in the middle of the current fy
2679 -- Fix for Bug #3846296. Separate out bonus.
2680 if (adj_type = 'BONUS EXPENSE') then
2681
2682 h_temp_deprn_tot := nvl(h_temp_deprn_tot,0) +
2683 ((nvl(h_bonus_ytd_deprn,0) / (h_pd_num - h_start_pdnum + 1)) *
2684 h_cost_frac);
2685 else
2686
2687 h_temp_deprn_tot := nvl(h_temp_deprn_tot,0) +
2688 ((h_ytd_deprn / (h_pd_num - h_start_pdnum + 1)) * h_cost_frac);
2689 end if;
2690 end if;
2691 --bug fix 3558253 and 3518604 end
2692
2693 exit;
2694 end if;
2695 end if;
2696
2697 if p_log_level_rec.statement_level then
2698 fa_debug_pkg.add
2699 (fname => l_calling_fn,
2700 element => 'deprn and adj',
2701 value => ''
2702 ,p_log_level_rec => p_log_level_rec);
2703 fa_debug_pkg.add('farboe','deprn_adjFOUND',1
2704 ,p_log_level_rec => p_log_level_rec);
2705 fa_debug_pkg.add('farboe','h_deprn_amount',h_deprn_amount
2706 ,p_log_level_rec => p_log_level_rec);
2707 fa_debug_pkg.add('farboe','h_adj_amount',h_adj_amount
2708 ,p_log_level_rec => p_log_level_rec);
2709 fa_debug_pkg.add('farboe','h_currpd_amount',h_currpd_amount
2710 ,p_log_level_rec => p_log_level_rec);
2711 fa_debug_pkg.add('farboe','h_net_deprn_amount',h_net_deprn_amount
2712 ,p_log_level_rec => p_log_level_rec);
2713 end if;
2714
2715 source_type_code := h_source_type_code;
2716
2717 in_dpr_detail := 0;
2718
2719 for m in 1..dpr_detail_counter loop
2720
2721 if (dpr_detail.dist_id(m) = h_dist_id) then
2722
2723 in_dpr_detail := 1;
2724 target_dpr_detail := m;
2725 exit;
2726
2727 end if;
2728
2729 end loop;
2730
2731 if in_dpr_detail > 0 then
2732
2733 /* Commenting out. Do not add the deprn_amount if there is an adjustment
2734 at this stage. This does not work if there is more than 1 adjustment
2735 in the period being backed out. Fix for 807256 - SNARAYAN
2736 dpr_detail.deprn_amount(target_dpr_detail) :=
2737 dpr_detail.deprn_amount(target_dpr_detail) +
2738 h_deprn_amount;
2739 */
2740
2741 null;
2742
2743 else
2744 if dpr_detail_counter = dpr_detail_size then
2745 for m in (dpr_detail_size + 1) ..
2746 (FA_RET_TYPES.FA_DPR_DETAIL_SIZE * expand_array) loop
2747
2748 dpr_detail.dist_id(m) := 0;
2749 dpr_detail.ccid(m) := 0;
2750 dpr_detail.deprn_amount(m) := 0;
2751 dpr_detail.adj_amount(m) := 0;
2752 dpr_detail.annualized_adj(m) := 0;
2753
2754 end loop; -- end of loop - for m
2755
2756 dpr_detail_size :=
2757 fa_RET_TYPES.FA_DPR_DETAIL_SIZE * expand_array;
2758 expand_array := expand_array + 1;
2759 end if;
2760
2761 dpr_detail.dist_id(dpr_detail_counter) := h_dist_id;
2762 dpr_detail.ccid(dpr_detail_counter) := h_ccid;
2763
2764 /* Commenting out. Do not copy the deprn_amount if there is an adjustment
2765 at this stage. This does not work if there is more than 1 adjustment
2766 in the period being backed out. Fix for 807256 - SNARAYAN
2767
2768 dpr_detail.deprn_amount(dpr_detail_counter) :=
2769 h_deprn_amount;
2770 */
2771 dpr_detail.adj_amount(dpr_detail_counter) :=
2772 h_adj_amount;
2773 dpr_detail.annualized_adj(dpr_detail_counter) :=
2774 h_annualized_adj;
2775
2776 target_dpr_detail := dpr_detail_counter;
2777 dpr_detail_counter := dpr_detail_counter + 1;
2778
2779 end if;
2780
2781 if (p_log_level_rec.statement_level) then
2782 fa_debug_pkg.add('farboe','target_dpr_detail',
2783 target_dpr_detail
2784 ,p_log_level_rec => p_log_level_rec);
2785 fa_debug_pkg.add('farboe','dpr_detail_counter',
2786 dpr_detail_counter
2787 ,p_log_level_rec => p_log_level_rec);
2788 end if;
2789
2790 tot_backup_deprn := 0;
2791
2792 /* check to see if any of the periods being backed out
2793 is period of addition */
2794
2795 SELECT count(*)
2796 INTO h_curr_pd_add
2797 FROM
2798 fa_deprn_periods dp,
2799 fa_transaction_headers th
2800 WHERE th.asset_id = h_asset_id
2801 AND th.book_type_code = h_book
2802 AND th.transaction_type_code || '' = 'ADDITION'
2803 AND th.date_effective between dp.period_open_date
2804 and nvl(dp.period_close_date, sysdate)
2805 AND dp.book_type_code = th.book_type_code
2806 AND dp.period_num = h_i
2807 AND dp.fiscal_year = h_current_fiscal_yr;
2808
2809 --bug fix 3558253 and 3518604 start
2810 h_backout_flag := 1;
2811 if(nvl(h_curr_pd_add,0) > 0)then
2812 begin
2813
2814 SELECT sum(adjustment_amount)--bug fix 3905436
2815 INTO h_adj_exp_row
2816 FROM
2817 fa_adjustments adj,
2818 fa_deprn_periods dp,
2819 fa_transaction_headers th
2820 WHERE
2821 th.asset_id = h_asset_id
2822 AND th.book_type_code = h_book
2823 AND th.transaction_type_code || '' = 'ADDITION'
2824 AND adj.asset_id = h_asset_id
2825 AND adj.book_type_code = h_book
2826 AND adj.source_type_code || '' = 'DEPRECIATION'
2827 AND adj.ADJUSTMENT_type || '' = 'EXPENSE'
2828 AND adj.distribution_id = h_dist_id --bug fix 3905436
2829 AND adj.period_counter_created = dp.period_counter
2830 AND th.date_effective between dp.period_open_date
2831 and nvl(dp.period_close_date, sysdate)
2832 AND dp.book_type_code = th.book_type_code
2833 AND dp.period_num = h_i
2834 AND dp.fiscal_year = h_current_fiscal_yr;
2835 EXCEPTION
2836 when no_data_found then
2837 h_adj_exp_row := -1;
2838 end;
2839 if(nvl(h_adj_exp_row,0) > 0)then
2840
2841 BEGIN /* Added for bug 6156244 */
2842
2843 SELECT PRIOR_FY_EXPENSE
2844 INTO h_prior_fy_exp
2845 FROM
2846 fa_deprn_summary ds,
2847 fa_deprn_periods dp,
2848 fa_transaction_headers th
2849 WHERE
2850 th.asset_id = h_asset_id
2851 AND th.book_type_code = h_book
2852 AND th.transaction_type_code || '' = 'ADDITION'
2853 AND th.date_effective between dp.period_open_date
2854 and nvl(dp.period_close_date, sysdate)
2855 AND ds.asset_id = h_asset_id
2856 AND ds.book_type_code = h_book
2857 AND ds.period_counter = dp.period_counter
2858 AND dp.book_type_code = th.book_type_code
2859 AND dp.period_num = h_i
2860 AND dp.fiscal_year = h_current_fiscal_yr;
2861 if(h_adj_exp_row = nvl(h_prior_fy_exp,0))then
2862 h_backout_flag := 0;
2863 tot_backup_deprn := 0;
2864 else
2865 h_backout_flag := 1;
2866 end if;
2867
2868 /* Added for bug 6156244 */
2869 exception
2870 when no_data_found then
2871 h_prior_fy_exp := 0;
2872
2873 if(h_adj_exp_row = nvl(h_prior_fy_exp,0))then
2874 h_backout_flag := 0;
2875 tot_backup_deprn := 0;
2876 else
2877 h_backout_flag := 1;
2878 end if;
2879 end;
2880 /* Added for bug 6156244 */
2881
2882 end if;
2883 elsif (nvl(h_curr_pd_reinst,0) > 0 )then -- Bug#4540554
2884
2885 if (p_log_level_rec.statement_level) then
2886 fa_debug_pkg.add('farboe','START - RET AFTER REINST',
2887 ' ');
2888 fa_debug_pkg.add('farboe','bk.ret_prorate_date',
2889 bk.ret_prorate_date);
2890 fa_debug_pkg.add('farboe','ret.th_id_in',
2891 ret.th_id_in);
2892 end if;
2893
2894 begin
2895
2896 SELECT sum(adjustment_amount), max(th.transaction_header_id)
2897 INTO h_adj_exp_row, h_old_reinst_trx_id
2898 FROM
2899 fa_adjustments adj,
2900 fa_deprn_periods dp,
2901 fa_transaction_headers th
2902 WHERE
2903 th.asset_id = h_asset_id
2904 AND th.book_type_code = h_book
2905 AND th.transaction_type_code || '' = 'REINSTATEMENT'
2906 AND adj.source_type_code || '' = 'RETIREMENT'
2907 AND adj.ADJUSTMENT_type || '' = 'EXPENSE'
2908 AND adj.asset_id = h_asset_id
2909 AND adj.book_type_code = h_book
2910 AND adj.distribution_id = h_dist_id --bug fix 3905436
2911 AND adj.period_counter_created = dp.period_counter
2912 AND th.date_effective between dp.period_open_date
2913 and nvl(dp.period_close_date, sysdate)
2914 AND dp.book_type_code = th.book_type_code
2915 AND dp.period_num = h_i
2916 AND dp.fiscal_year = h_current_fiscal_yr;
2917
2918
2919 exception
2920 when no_data_found then
2921 h_adj_exp_row := -1;
2922 end;
2923
2924 begin
2925 select dp1.period_counter
2926 ,dp2.period_counter
2927 into h_old_reinst_pc
2928 ,h_old_ret_pc
2929 from fa_transaction_headers trx,
2930 fa_deprn_periods dp1,
2931 fa_retirements old_ret,
2932 fa_deprn_periods dp2
2933 where trx.transaction_header_id = h_old_reinst_trx_id
2934 and dp1.book_type_code = trx.book_type_code
2935 and trx.transaction_date_entered between dp1.CALENDAR_PERIOD_OPEN_DATE
2936 and dp1.CALENDAR_PERIOD_CLOSE_DATE
2937 and old_ret.transaction_header_id_out = trx.transaction_header_id
2938 and dp2.book_type_code = trx.book_type_code
2939 and old_ret.date_retired between dp2.CALENDAR_PERIOD_OPEN_DATE
2940 and dp2.CALENDAR_PERIOD_CLOSE_DATE
2941 ;
2942
2943 select dp.period_counter
2944 into h_ret_prorate_pc
2945 from fa_retirements new_ret
2946 ,fa_conventions conv
2947 ,fa_deprn_periods dp
2948 where new_ret.transaction_header_id_in=ret.th_id_in
2949 and conv.prorate_convention_code=new_ret.retirement_prorate_convention
2950 and new_ret.date_retired between conv.start_date and conv.end_date
2951 and dp.book_type_code = new_ret.book_type_code
2952 and conv.prorate_date between dp.CALENDAR_PERIOD_OPEN_DATE
2953 and dp.CALENDAR_PERIOD_CLOSE_DATE
2954 ;
2955
2956 exception
2957 when no_data_found then null;
2958 end;
2959
2960 -- backout amount = catchup for reinstatement(=h_adj_exp_row) * (num of periods to backout)/(num of periods for the catchup)
2961 h_no_of_per_to_exclude := h_cpd_ctr - h_ret_prorate_pc;
2962
2963 if (p_log_level_rec.statement_level) then
2964 fa_debug_pkg.add('farboe','h_old_reinst_pc',
2965 h_old_reinst_pc);
2966 fa_debug_pkg.add('farboe','h_old_ret_pc',
2967 h_old_ret_pc);
2968 fa_debug_pkg.add('farboe','h_ret_prorate_pc',
2969 h_ret_prorate_pc);
2970 fa_debug_pkg.add('farboe','h_adj_exp_row',
2971 h_adj_exp_row);
2972 fa_debug_pkg.add('farboe','h_no_of_per_to_exclude',
2973 h_no_of_per_to_exclude);
2974 end if;
2975
2976 if h_no_of_per_to_exclude > 0 and (h_old_reinst_pc - h_old_ret_pc) > 0 then
2977 backup_deprn := h_adj_exp_row * h_no_of_per_to_exclude / (h_old_reinst_pc - h_old_ret_pc);
2978 else
2979 backup_deprn := 0;
2980 end if;
2981
2982 if (p_log_level_rec.statement_level) then
2983 fa_debug_pkg.add('farboe','backup_deprn',
2984 backup_deprn);
2985 fa_debug_pkg.add('farboe','END - RET AFTER REINST',' ');
2986 end if;
2987 end if;
2988 --bug fix 3558253 and 3518604 end
2989
2990 if (p_log_level_rec.statement_level) then
2991 fa_debug_pkg.add('farboe','before J in reverse',1
2992 ,p_log_level_rec => p_log_level_rec);
2993 end if;
2994
2995 if (h_adj_amount <> 0)and( h_backout_flag = 1) then
2996 for j in reverse h_start_pdnum..(i) loop
2997 backup_deprn := 0;
2998
2999 frac_of_fiscal_year := 0;
3000
3001 if dpr_evenly > 0 then
3002 if (p_log_level_rec.statement_level) then
3003 fa_debug_pkg.add('farboe','J in reverse',2
3004 ,p_log_level_rec => p_log_level_rec);
3005 end if;
3006
3007 frac_of_fiscal_year := 1 / pds_per_year;
3008 else
3009 if (p_log_level_rec.statement_level) then
3010 fa_debug_pkg.add('farboe','J in reverse',3
3011 ,p_log_level_rec => p_log_level_rec);
3012 end if;
3013
3014 if not fa_cache_pkg.fazcff (d_cal, book,
3015 current_fiscal_yr, period_fracs
3016 ,p_log_level_rec => p_log_level_rec) then
3017 fa_srvr_msg.add_message(calling_fn => l_calling_fn
3018 ,p_log_level_rec => p_log_level_rec);
3019 return(FALSE);
3020 end if;
3021 frac_of_fiscal_year := period_fracs(j-1).frac;
3022 end if;
3023
3024 --bug fix 3558253 and 3518604 start
3025 if(nvl(h_adj_exp_row,0) <> 0)and (nvl(h_prior_fy_exp,0) <> 0)and
3026 (nvl(frac_of_fiscal_year,0) <> 0)and (nvl(h_annualized_adj,0) <> 0)
3027 then
3028 h_temp_calc := (h_adj_exp_row - h_prior_fy_exp)/(frac_of_fiscal_year * (h_annualized_adj/h_cost_frac));
3029
3030 h_temp_calc := abs(trunc(h_temp_calc));
3031 end if;
3032
3033 if(h_temp_calc > 0)then
3034 if(j <= h_temp_calc)then
3035 backup_deprn := frac_of_fiscal_year * h_annualized_adj;
3036 tot_backup_deprn := tot_backup_deprn + backup_deprn;
3037 end if;
3038 else
3039 backup_deprn := frac_of_fiscal_year * h_annualized_adj;
3040 tot_backup_deprn := tot_backup_deprn + backup_deprn;
3041 end if;
3042 --bug fix 3558253 and 3518604 end
3043 end loop; -- end of loop - for j
3044
3045 end if; -- end of - if h_adj_amount
3046
3047 /* Take the lesser of the absolute values of the
3048 * tot_backup_deprn or the h_adj_amount
3049 * Remember that both values are negative in the compare. */
3050
3051
3052 /* Perform this comparison only when annualized_adjustment
3053 * is not zero. Otherwise add adjustment_amount to tot_backup_deprn
3054 * Fix for 807256 - SNARAYAN
3055 */
3056
3057 if (p_log_level_rec.statement_level) then
3058 fa_debug_pkg.add('farboe','h_curr_pd_add',
3059 h_curr_pd_add
3060 ,p_log_level_rec => p_log_level_rec);
3061 end if;
3062
3063 if (h_annualized_adj <> 0) then
3064 if (abs(h_adj_amount) < abs(tot_backup_deprn)) then
3065 tot_backup_deprn := h_adj_amount;
3066 end if;
3067
3068 /* Add the annualized_adj only when it is in a period
3069 * other than period of addition as the annualized amount
3070 * is already factored into deprn_amount in fa_deprn_detail
3071 */
3072 if (h_curr_pd_add = 1) then
3073 -- Fix for Bug #3941213. Don't do this for prior
3074 -- period transfer.
3075 if (h_prior_pd_tfr = 0) then
3076 dpr_detail.deprn_amount(target_dpr_detail) :=
3077 dpr_detail.deprn_amount(target_dpr_detail) +
3078 tot_backup_deprn;
3079 end if;
3080 end if;
3081 elsif ( nvl(h_curr_pd_reinst,0) > 0 ) then -- Bug#4540554
3082
3083 if (p_log_level_rec.statement_level) then
3084 fa_debug_pkg.add('farboe','START - RET AFTER REINST', '2.1');
3085 fa_debug_pkg.add('farboe','dpr_detail.deprn_amount(target_dpr_detail)'
3086 , dpr_detail.deprn_amount(target_dpr_detail) );
3087 fa_debug_pkg.add('farboe','h_adj_amount'
3088 , h_adj_amount );
3089 fa_debug_pkg.add('farboe','backup_deprn'
3090 , backup_deprn );
3091 end if;
3092
3093 dpr_detail.deprn_amount(target_dpr_detail) :=
3094 h_adj_amount + backup_deprn +
3095 dpr_detail.deprn_amount(target_dpr_detail);
3096
3097 if (p_log_level_rec.statement_level) then
3098 fa_debug_pkg.add('farboe','NEW dpr_detail.deprn_amount(target_dpr_detail)'
3099 , dpr_detail.deprn_amount(target_dpr_detail) );
3100 fa_debug_pkg.add('farboe','END - RET AFTER REINST', '2.9');
3101 end if;
3102 else
3103 /* else to add adjustment amount to total backup deprn when
3104 there is no annualized adjustment amount. Add adjustment
3105 amount only in period other than addition. Since if there
3106 was adjustment expense in period of addition then it would
3107 mean it was a retroactive-backdated addition and this would
3108 means annualized_adj will be or should have a value. In this
3109 case there is specific logic just above */
3110
3111 if (p_log_level_rec.statement_level) then
3112 fa_debug_pkg.add('farboe','6.1', 6.1
3113 ,p_log_level_rec => p_log_level_rec);
3114 end if;
3115
3116 if (h_curr_pd_add = 0) then
3117 if (p_log_level_rec.statement_level) then
3118 fa_debug_pkg.add('farboe','6.2', 6.2
3119 ,p_log_level_rec => p_log_level_rec);
3120 fa_debug_pkg.add('farboe','h_adj_amount',
3121 h_adj_amount
3122 ,p_log_level_rec => p_log_level_rec);
3123 fa_debug_pkg.add('farboe','target_dpr_detail',
3124 target_dpr_detail
3125 ,p_log_level_rec => p_log_level_rec);
3126 end if;
3127
3128 -- Fix for Bug #3941213. Don't do this for prior
3129 -- period transfer.
3130 if (h_prior_pd_tfr = 0) then
3131 dpr_detail.deprn_amount(target_dpr_detail) :=
3132 h_adj_amount +
3133 dpr_detail.deprn_amount(target_dpr_detail);
3134 end if;
3135 if (p_log_level_rec.statement_level) then
3136 fa_debug_pkg.add('farboe','6.2.1', 6.21
3137 ,p_log_level_rec => p_log_level_rec);
3138 end if;
3139 end if;
3140 if (p_log_level_rec.statement_level) then
3141 fa_debug_pkg.add('farboe','6.3', 6.3
3142 ,p_log_level_rec => p_log_level_rec);
3143 end if;
3144 end if;
3145
3146
3147 /* Based on the number of adjustments and retirement expense rows
3148 * add to the total backup deprn. The following was required as
3149 * fix for 807256. May not be the most elegant but it works
3150 * without significant redesign of farboe. Always adding
3151 * h_deprn_amount which was the difference between deprn_amount
3152 * in fa_deprn_detail and adjustment_amount in fa_adjustments
3153 * does not work for all the cases. Instead the following
3154 * conditional logic adds the amounts correctly. SNARAYAN
3155 */
3156
3157 if p_log_level_rec.statement_level then
3158 fa_debug_pkg.add('farboe','7',7
3159 ,p_log_level_rec => p_log_level_rec);
3160 end if;
3161
3162 if ((h_adj_count > 0) and (h_ret_count = 0)) then
3163 /* When there are expense rows other than retirement expense
3164 add the current period deprn amount once to the total
3165 being backed out for each distinct distribution */
3166 if (prev_dist_id <> h_dist_id) then
3167 dpr_detail.deprn_amount(target_dpr_detail) :=
3168 h_currpd_amount +
3169 dpr_detail.deprn_amount(target_dpr_detail);
3170 end if;
3171 elsif ((h_adj_count = 0) and (h_ret_count = 0)) then
3172 /* When there are no expense rows or retirment expense rows
3173 add the current period deprn amount for each distribution
3174 fetched to the total being backed out */
3175 dpr_detail.deprn_amount(target_dpr_detail) :=
3176 h_deprn_amount +
3177 dpr_detail.deprn_amount(target_dpr_detail);
3178
3179 elsif ((h_adj_count = 0) and (h_ret_count > 0) and
3180 (h_source_type_code in( 'DEPRECIATION','RETIREMENT'))) then --bug fix 4995325
3181 if (prev_dist_id <> h_dist_id) then
3182 dpr_detail.deprn_amount(target_dpr_detail) :=
3183 h_currpd_amount +
3184 dpr_detail.deprn_amount(target_dpr_detail);
3185 end if;
3186 elsif ((h_adj_count > 0) and (h_ret_count > 0)) then
3187 if (prev_dist_id <> h_dist_id) then
3188 dpr_detail.deprn_amount(target_dpr_detail) :=
3189 h_currpd_amount +
3190 dpr_detail.deprn_amount(target_dpr_detail);
3191 end if;
3192 end if;
3193 prev_dist_id := h_dist_id;
3194 if p_log_level_rec.statement_level then
3195 fa_debug_pkg.add('farboe','9',9
3196 ,p_log_level_rec => p_log_level_rec);
3197 end if;
3198
3199 end loop; -- end of - while deprn_adj
3200
3201 if mrc_sob_type_code <> 'R' then
3202 close DEPRN_ADJ;
3203 else
3204 close MRC_DEPRN_ADJ;
3205 end if;
3206
3207 if h_start_pdnum <> h_end_pdnum -1 and h_i = h_start_pdnum + 1 then
3208
3209 h_temp_startpd_deprn := 0;
3210 for k in 1 .. dpr_detail_counter loop
3211 h_temp_startpd_deprn := h_temp_startpd_deprn +
3212 dpr_detail.deprn_amount(k);
3213 end loop; -- end of - for k
3214 end if;
3215 if h_i = h_start_pdnum then
3216 h_start_pd_pc := h_pc;
3217 end if;
3218
3219 h_i := i;
3220
3221 end loop; -- end of - for i in reverse
3222
3223 if adj_type = 'EXPENSE' then
3224
3225 adj_row.adjustment_type := 'EXPENSE';
3226 adj_row.debit_credit_flag := 'DR';
3227
3228 elsif adj_type = 'BONUS EXPENSE' then
3229
3230 select nvl(cb.bonus_deprn_expense_acct,'0')
3231 into h_bonus_deprn_exp_acct
3232 from fa_additions_b ad,
3233 fa_category_books cb
3234 where ad.asset_id = h_asset_id
3235 and cb.category_id = ad.asset_category_id
3236 and cb.book_type_code = h_book;
3237
3238 bonus_deprn_exp_acct := h_bonus_deprn_exp_acct;
3239 adj_row.account := bonus_deprn_exp_acct;
3240 adj_row.account_type := 'BONUS_DEPRN_EXPENSE_ACCT';
3241 adj_row.adjustment_type := 'BONUS EXPENSE';
3242 adj_row.debit_credit_flag := 'DR';
3243
3244 elsif adj_type = 'REVAL EXPENSE' then
3245
3246 adj_row.adjustment_type := 'REVAL EXPENSE';
3247 adj_row.debit_credit_flag := 'DR';
3248
3249 else
3250
3251 adj_row.adjustment_type := 'REVAL AMORT';
3252 adj_row.debit_credit_flag := 'DR';
3253
3254 end if;
3255
3256 /* BUG# 1400554
3257 populating the account seg for expense with the value
3258 in category books
3259 -- bridgway 09/14/00
3260
3261 adj_row.account[0] = '\0';
3262 */
3263
3264 select facb.deprn_expense_acct
3265 into h_deprn_exp_acct
3266 from fa_additions_b faadd,
3267 fa_category_books facb,
3268 fa_book_controls bc
3269 where faadd.asset_id = h_asset_id
3270 and facb.category_id = faadd.asset_category_id
3271 and facb.book_type_code = h_book
3272 and bc.book_type_code = facb.book_type_code;
3273
3274 adj_row.account := h_deprn_exp_acct;
3275
3276 adj_row.transaction_header_id := th_id_in;
3277 adj_row.source_type_code := 'RETIREMENT';
3278 adj_row.book_type_code := book;
3279 adj_row.period_counter_created := cpd_ctr;
3280 adj_row.asset_id := asset_id;
3281 adj_row.period_counter_adjusted := cpd_ctr;
3282 adj_row.last_update_date := today;
3283
3284 -- adj_row.account_type := 'DEPRN_EXPENSE_ACCT';
3285
3286 --bug fix 4492828 in case of bonus we need to use bonus account as set above
3287 if (adj_type <> 'BONUS EXPENSE')then
3288 adj_row.account_type := 'DEPRN_EXPENSE_ACCT';
3289 end if;
3290
3291 adj_row.current_units := current_units;
3292 adj_row.selection_thid := 0;
3293 adj_row.flush_adj_flag := TRUE;
3294 adj_row.annualized_adjustment := 0;
3295 adj_row.asset_invoice_id := 0;
3296 adj_row.leveling_flag := TRUE;
3297 adj_row.gen_ccid_flag := TRUE;
3298 adj_row.code_combination_id := 0;
3299
3300 h_tot_deprn := 0;
3301 h_start_pd_deprn := 0;
3302
3303 if p_log_level_rec.statement_level then
3304 fa_debug_pkg.add('farboe','dpr_detail_counter',dpr_detail_counter
3305 ,p_log_level_rec => p_log_level_rec);
3306 end if;
3307
3308 for m in 1 .. dpr_detail_counter loop
3309 h_tot_deprn := h_tot_deprn + dpr_detail.deprn_amount(m);
3310 end loop; -- end of - for m
3311
3312 h_start_pd_deprn := h_tot_deprn - h_temp_startpd_deprn;
3313
3314 if p_log_level_rec.statement_level then
3315 fa_debug_pkg.add('farboe','h_tot_deprn',h_tot_deprn
3316 ,p_log_level_rec => p_log_level_rec);
3317 fa_debug_pkg.add('farboe','h_temp_startpd_deprn',
3318 h_temp_startpd_deprn
3319 ,p_log_level_rec => p_log_level_rec);
3320 fa_debug_pkg.add('farboe','h_start_pd_deprn',h_start_pd_deprn
3321 ,p_log_level_rec => p_log_level_rec);
3322 end if;
3323
3324
3325 begin
3326 SELECT pcal.period_num
3327 INTO h_start_pd_endpp
3328 FROM fa_calendar_periods pcal,
3329 fa_deprn_periods dp
3330 WHERE calendar_type = p_cal
3331 AND dp.book_type_code = h_book
3332 AND dp.fiscal_year = h_current_fiscal_yr
3333 AND dp.period_num = h_start_pdnum
3334 AND dp.calendar_period_close_date
3335 between start_date and end_date;
3336 exception
3337 when no_data_found then
3338 h_start_pd_endpp := h_start_pdnum;
3339 end;
3340
3341
3342 adj_row.adjustment_amount := h_tot_deprn;
3343
3344 if p_log_level_rec.statement_level then
3345 fa_debug_pkg.add('farboe','h_start_pd_endpp',h_start_pd_endpp
3346 ,p_log_level_rec => p_log_level_rec);
3347 end if;
3348
3349 select decode(fy1.fiscal_year, fy2.fiscal_year,1,0),
3350 decode(BC.DEPR_FIRST_YEAR_RET_FLAG, 'YES', 1, 0),
3351 decode(ctype.depr_when_acquired_flag,'YES',1,0),
3352 decode(mt.rate_source_rule,
3353 'CALCULATED', 1,
3354 'TABLE', 2,
3355 'FLAT', 3)
3356 INTO h_same_fy, h_depr_first_year_ret,
3357 h_dwacq, h_rate_source_rule
3358 FROM FA_FISCAL_YEAR fy1,
3359 FA_FISCAL_YEAR fy2,
3360 FA_BOOKS bk,
3361 FA_RETIREMENTS rt,
3362 FA_CONVENTION_TYPES ctype,
3363 FA_METHODS mt,
3364 FA_BOOK_CONTROLS bc
3365 WHERE rt.date_retired between
3366 fy1.start_date and fy1.end_date
3367 AND bk.deprn_start_date between
3368 fy2.start_date and fy2.end_date
3369 AND rt.asset_id = h_asset_id
3370 AND bk.asset_id = h_asset_id
3371 AND bk.book_type_code = bc.book_type_code
3372 AND bc.book_type_code = h_book
3373 AND rt.retirement_id = h_retirement_id
3374 AND bk.retirement_id = rt.retirement_id
3375 AND bk.transaction_header_id_out is not null
3376 AND bk.deprn_method_code = mt.method_code
3377 AND nvl(bk.life_in_months,1) = nvl(mt.life_in_months,1)
3378 AND bk.prorate_convention_code = ctype.prorate_convention_code
3379 AND fy1.fiscal_year_name = bc.fiscal_year_name
3380 AND fy2.fiscal_year_name = bc.fiscal_year_name;
3381
3382 /* If depreciate_lastyr is TRUE then we calculate the fraction of
3383 expense to backout based on prorate periods. Otherwise we backout
3384 the entire amount for current fiscal year and it is not necessary
3385 to calculate the fraction. If depreciate_lastyr in the method is
3386 TRUE but asset if retired is retired in first year and
3387 DEPR_FIRST_YEAR_RET_FLAG in book controls is NO then we need to
3388 back out all the deprn taken so far and there is no need to calculate
3389 fraction of depreciation to backout.
3390 */
3391
3392 temp_start_pp := 0;
3393
3394 -- Get the number of periods per year in the rate calendar
3395 if not fa_cache_pkg.fazcct(p_cal) then
3396 fa_srvr_msg.add_message(calling_fn => 'fa_gainloss_ret_pkg.fagfpc'
3397 ,p_log_level_rec => p_log_level_rec);
3398 raise farboe_err;
3399 end if;
3400
3401 p_pers_per_yr := fa_cache_pkg.fazcct_record.number_per_fiscal_year;
3402
3403 if p_pers_per_yr <> 365 then
3404 if (depreciate_lastyr and not
3405
3406 /* FIX for BUG#2787098:
3407 This logic seems to be trasformed wrongly from pro*C code.
3408 (h_same_fy = 1 and (not h_depr_first_year_ret = 0))) then
3409 */
3410 (h_same_fy = 1 and (not (h_depr_first_year_ret = 1)))) then
3411 if (h_same_fy = 1) then
3412
3413 -- Get the Fiscal Year Name from FA_BOOK_CONTROLS cache
3414 if not fa_cache_pkg.fazcbc(book) then
3415 raise farboe_err;
3416
3417 end if;
3418
3419 fy_name := fa_cache_pkg.fazcbc_record.fiscal_year_name;
3420
3421 -- Get the prorate period, and the corresponding fiscal year
3422 if not fa_cache_pkg.fazccp(p_cal, fy_name, dpr.prorate_jdate,
3423 pp_begin, y_begin, dummy_num
3424 ,p_log_level_rec => p_log_level_rec) then
3425 raise farboe_err;
3426
3427 end if;
3428
3429 /* FIX for BUG#2787098:
3430 Changed dpr.prorate_jdate to dpr.deprn_start_jdate in call fazccp
3431 to make it in sinc with the pro*c code
3432 */
3433 -- Get the depreciation start prorate period
3434 if not fa_cache_pkg.fazccp(p_cal, fy_name, dpr.deprn_start_jdate,
3435 dpp_begin, dpy_begin, dummy_num
3436 ,p_log_level_rec => p_log_level_rec) then
3437 raise farboe_err;
3438
3439 end if;
3440
3441 /* FIX for BUG#2787098:
3442 Changed condition of h_dwacq to make it in sinc with the pro*c code
3443 */
3444 if ((start_pp < pp_begin) and (h_rate_source_rule = 1)) then
3445 temp_start_pp := pp_begin;
3446 elsif ((start_pp < pp_begin) and (h_rate_source_rule <> 1)
3447 and (not (h_dwacq = 0))) then
3448 temp_start_pp := pp_begin;
3449 elsif ((start_pp < pp_begin) and (h_rate_source_rule <> 1)
3450 and (h_dwacq = 1)) then
3451 temp_start_pp := dpp_begin;
3452 else temp_start_pp := start_pp;
3453 end if;
3454 else
3455 temp_start_pp := start_pp;
3456 end if;
3457
3458 temp_pds := -1 * pds_catchup;
3459 /* FIX for BUG#2787098:
3460 Changed the following to make it in sinc with the pro*c code
3461 Old code: temp_frac := (end_pp - temp_start_pp) /temp_pds;
3462 */
3463
3464 --Bug 7488420 added the if condition
3465 if (end_pp = temp_start_pp ) then
3466 temp_frac := 0;
3467 else
3468 temp_frac := temp_pds/(end_pp - temp_start_pp);
3469 end if;
3470
3471 h_tot_deprn := h_tot_deprn * temp_frac;
3472
3473
3474 --------------------- Bug 5148828 : NEW APPROACH for Bug#5074257
3475 -- We can remove the logic above after some more verifications.
3476
3477 if h_Brow_deprn_reserve = 0 then /*Bug 7595090. Make sure that calc catchup should not be called when the asset is added with Depreciation Reserve */
3478 -- Bug# 5018194
3479 if not fa_cache_pkg.fazccp(p_cal, fiscal_year_name,
3480 bk.deprn_start_jdate,
3481 deprn_start_pnum, deprn_start_fy, dummy_num) then
3482 fa_srvr_msg.add_message(calling_fn => l_calling_fn);
3483 raise farboe_err;
3484 end if;
3485
3486 if p_log_level_rec.statement_level then
3487 fa_debug_pkg.add('farboe','++ CATCHUP: ret.th_id_in', ret.th_id_in);
3488 end if;
3489
3490 if (not FA_GAINLOSS_DPR_PKG.CALC_CATCHUP(
3491 ret => ret,
3492 BK => bk,
3493 DPR => dpr,
3494 calc_catchup => TRUE, -- (start_pd < cpdnum),
3495 x_deprn_exp => l_deprn_exp,
3496 x_bonus_deprn_exp => l_bonus_deprn_exp,
3497 x_asset_fin_rec_new => l_asset_fin_rec_new,
3498 p_log_level_rec => p_log_level_rec)) then
3499
3500 fa_srvr_msg.add_message(calling_fn => l_calling_fn);
3501 return(FALSE);
3502
3503 end if;
3504
3505
3506 -- Bug 5362790
3507 -- Bug 5652883 (Removed '=' from if clause)
3508 -- if deprn_start_pnum >= start_pp and h_same_fy=1 then
3509 if deprn_start_pnum > start_pp and h_same_fy=1 then
3510 h_tot_deprn := l_deprn_exp;
3511 else
3512 -- h_tot_deprn := l_deprn_exp * abs(pds_catchup) / (end_pp - start_pp);
3513
3514 -- Fix for Bug #5844937/5851102. To prevent divisor by zero error,
3515 -- set the h_tot_deprn to 0 when it should have calculated to 0.
3516 if (l_deprn_exp = 0) or (pds_catchup = 0) or (end_pp = start_pp) then
3517
3518 h_tot_deprn := 0;
3519 else
3520
3521 h_tot_deprn := l_deprn_exp * abs(pds_catchup) / (end_pp - start_pp);
3522 end if;
3523
3524 end if;
3525
3526 if p_log_level_rec.statement_level then
3527 fa_debug_pkg.add('farboe','++ CATCHUP: l_deprn_exp', l_deprn_exp);
3528 fa_debug_pkg.add('farboe','++ CATCHUP: pds_catchup', pds_catchup);
3529 fa_debug_pkg.add('farboe','++ CATCHUP: start_pp', start_pp);
3530 fa_debug_pkg.add('farboe','++ CATCHUP: deprn_start_pnum', deprn_start_pnum);
3531 fa_debug_pkg.add('farboe','++ CATCHUP: end_pp', end_pp);
3532 fa_debug_pkg.add('farboe','++ CATCHUP: h_tot_deprn', h_tot_deprn);
3533 end if;
3534 end if; /*End of If for Bug 7595090*/
3535
3536 ---------------------
3537
3538 end if;
3539 -- Bug# 5018194: elsif p_pers_per_yr = 365 and h_start_pdnum <> h_end_pdnum - 1 then -- p_pds_per year is 365
3540 elsif p_pers_per_yr = 365 and pds_catchup <> 0
3541 and nvl(bk.pc_fully_reserved,-1) <> h_start_pd_pc then -- p_pds_per year is 365
3542
3543 if p_log_level_rec.statement_level then
3544 fa_debug_pkg.add('farboe','IN 365',365,p_log_level_rec);
3545 fa_debug_pkg.add('farboe','++ p_cal',p_cal,p_log_level_rec);
3546 fa_debug_pkg.add('farboe','++ fiscal_year_name',fiscal_year_name,p_log_level_rec);
3547 fa_debug_pkg.add('farboe','++ bk.deprn_start_jdate',bk.deprn_start_jdate,p_log_level_rec);
3548 end if;
3549 if h_Brow_deprn_reserve = 0 then /*Bug 7595090. Make sure that calc catchup should not be called when the asset is added with Depreciation Reserve */
3550 h_ret_pjdate := to_char(bk.ret_prorate_date, 'J');
3551
3552 if p_log_level_rec.statement_level then
3553 fa_debug_pkg.add('farboe','h_ret_pjdate',h_ret_pjdate, p_log_level_rec);
3554 end if;
3555
3556 -- Bug# 5018194: Added additional fazccp to calculate a correct deprn_start_pnum
3557 if not fa_cache_pkg.fazccp(p_cal, fiscal_year_name,
3558 bk.deprn_start_jdate,
3559 deprn_start_pnum, deprn_start_fy, dummy_num) then
3560 fa_srvr_msg.add_message(calling_fn => l_calling_fn);
3561 raise farboe_err;
3562 end if;
3563
3564 if p_log_level_rec.statement_level then
3565 fa_debug_pkg.add('farboe','++ deprn_start_fy', deprn_start_fy, p_log_level_rec);
3566 fa_debug_pkg.add('farboe','++ deprn_start_pnum', deprn_start_pnum, p_log_level_rec);
3567 end if;
3568
3569
3570 -- Get the depreciation start prorate period
3571 if not fa_cache_pkg.fazccp(p_cal, fiscal_year_name, h_ret_pjdate,
3572 h_ret_pp, dummy_num2, dummy_num,
3573 p_log_level_rec => p_log_level_rec) then
3574 raise farboe_err;
3575 end if;
3576
3577 if p_log_level_rec.statement_level then
3578 fa_debug_pkg.add('farboe','h_ret_pp',h_ret_pp,p_log_level_rec => p_log_level_rec);
3579 fa_debug_pkg.add('farboe','h_start_pd_endpp',h_start_pd_endpp, p_log_level_rec => p_log_level_rec);
3580 end if;
3581
3582 /* Bug#4347020 */
3583 h_amt_to_retain :=
3584 ((h_start_pd_deprn - nvl( -1 * h_adj_exp_row,0))
3585 * ((h_ret_pp - start_pp) /
3586 ((h_start_pd_endpp - start_pp) + 1)));
3587
3588 h_amt_to_retain := h_amt_to_retain + (-1 * h_adj_exp_row);
3589
3590
3591 if not FA_UTILS_PKG.faxrnd(h_start_pd_deprn, ret.book) then
3592 fa_srvr_msg.add_message(calling_fn => l_calling_fn
3593 ,p_log_level_rec => p_log_level_rec);
3594 raise farboe_err;
3595 end if;
3596
3597 if p_log_level_rec.statement_level then
3598 fa_debug_pkg.add('farboe','h_tot_deprn - Before', h_tot_deprn, p_log_level_rec);
3599 fa_debug_pkg.add('farboe','h_amt_to_retain - Before', h_amt_to_retain, p_log_level_rec);
3600 end if;
3601
3602 h_tot_deprn := h_tot_deprn - h_amt_to_retain;
3603
3604 if p_log_level_rec.statement_level then
3605 fa_debug_pkg.add('farboe','h_start_pd_deprn',h_start_pd_deprn
3606 ,p_log_level_rec => p_log_level_rec);
3607 fa_debug_pkg.add('farboe','h_tot_deprn',h_tot_deprn
3608 ,p_log_level_rec => p_log_level_rec);
3609 end if;
3610
3611
3612 --------------------- NEW APPROACH for Bug#5074257
3613 -- We can remove the logic above after some more verifications.
3614
3615 if p_log_level_rec.statement_level then
3616 fa_debug_pkg.add('farboe','++ CATCHUP: ret.th_id_in', ret.th_id_in);
3617 end if;
3618
3619 if (not FA_GAINLOSS_DPR_PKG.CALC_CATCHUP(
3620 ret => ret,
3621 BK => bk,
3622 DPR => dpr,
3623 calc_catchup => TRUE, -- (start_pd < cpdnum),
3624 x_deprn_exp => l_deprn_exp,
3625 x_bonus_deprn_exp => l_bonus_deprn_exp,
3626 x_asset_fin_rec_new => l_asset_fin_rec_new,
3627 p_log_level_rec => p_log_level_rec)) then
3628
3629 fa_srvr_msg.add_message(calling_fn => l_calling_fn);
3630 return(FALSE);
3631
3632 end if;
3633
3634 -- Bug 5362790
3635 -- Bug 5652883 (Removed '=' from if clause)
3636 -- if deprn_start_pnum >= start_pp and h_same_fy=1 then
3637 if deprn_start_pnum > start_pp and h_same_fy=1 then
3638 h_tot_deprn := l_deprn_exp;
3639 else
3640 -- h_tot_deprn := l_deprn_exp * abs(pds_catchup) / (end_pp - start_pp);
3641
3642 -- Fix for Bug #5844937/5851102/5870503. To prevent divisor by
3643 -- zero error, set the h_tot_deprn to 0 when it should have
3644 -- calculated to 0.
3645 if (l_deprn_exp = 0) or (pds_catchup = 0) or (end_pp = start_pp) then
3646
3647 h_tot_deprn := 0;
3648 else
3649
3650 h_tot_deprn := l_deprn_exp * abs(pds_catchup) / (end_pp - start_pp);
3651 end if;
3652
3653 end if;
3654 end if; /*End of If for bug 7595090*/
3655
3656 if p_log_level_rec.statement_level then
3657 fa_debug_pkg.add('farboe','++ CATCHUP: l_deprn_exp', l_deprn_exp);
3658 fa_debug_pkg.add('farboe','++ CATCHUP: pds_catchup', pds_catchup);
3659 fa_debug_pkg.add('farboe','++ CATCHUP: start_pp', start_pp);
3660 fa_debug_pkg.add('farboe','++ CATCHUP: deprn_start_pnum', deprn_start_pnum);
3661 fa_debug_pkg.add('farboe','++ CATCHUP: end_pp', end_pp);
3662 fa_debug_pkg.add('farboe','++ CATCHUP: h_tot_deprn', h_tot_deprn);
3663 end if;
3664
3665 ---------------------
3666
3667
3668 -- Bug# 5018194: elsif p_pers_per_yr = 365 and h_start_pdnum = h_end_pdnum - 1 and
3669 -- bk.pc_fully_reserved = h_start_pd_pc then
3670 elsif p_pers_per_yr = 365 and h_start_pdnum = h_end_pdnum - 1 and
3671 nvl(bk.pc_fully_reserved,-1) = h_start_pd_pc then
3672
3673 if p_log_level_rec.statement_level then
3674 fa_debug_pkg.add('farboe','365 prorate and pcfr',h_start_pd_deprn
3675 ,p_log_level_rec => p_log_level_rec);
3676 end if;
3677
3678 h_dpr_temp := dpr;
3679 h_dpr_temp.rsv_known_flag := TRUE;
3680 h_dpr_temp.deprn_rsv := 0;
3681 h_dpr_temp.reval_rsv := 0;
3682 h_dpr_temp.prior_fy_exp := 0;
3683 h_dpr_temp.ytd_deprn := 0;
3684 h_dpr_temp.bonus_deprn_rsv := 0;
3685 h_dpr_temp.bonus_ytd_deprn := 0;
3686 h_dpr_temp.prior_fy_bonus_exp := 0;
3687 h_dpr_temp.jdate_retired := 0;
3688 h_dpr_temp.ret_prorate_jdate := to_char(bk.ret_prorate_date,'J');
3689
3690 if not FA_GAINLOSS_DPR_PKG.fagcdp(h_dpr_temp, deprn_amt,
3691 bonus_deprn_amt,
3692 reval_deprn_amt,
3693 reval_amort, bk.deprn_start_date,
3694 bk.d_cal, bk.p_cal, h_start_pdnum,
3695 h_start_pdnum,
3696 bk.prorate_fy, bk.dsd_fy, bk.prorate_jdate,
3697 bk.deprn_start_jdate
3698 ,p_log_level_rec => p_log_level_rec) then
3699
3700 fa_srvr_msg.add_message(
3701 calling_fn => l_calling_fn,
3702 name => 'FA_RET_GENERIC_ERROR',
3703 token1 => 'MODULE',
3704 value1 => 'FAGCDP',
3705 token2 => 'INFO',
3706 value2 => 'depreciation number',
3707 token3 => 'ASSET',
3708 value3 => ret.asset_number
3709 ,p_log_level_rec => p_log_level_rec);
3710
3711 return(FALSE);
3712
3713 end if;
3714 if p_log_level_rec.statement_level then
3715 fa_debug_pkg.add('farboe','after fagcdp deprn_amt',deprn_amt
3716 ,p_log_level_rec => p_log_level_rec);
3717 end if;
3718 -- bug fix 5716178
3719 if(deprn_amt <> 0)then
3720 h_temp_end_pp := (h_tot_deprn * ((h_start_pd_endpp - start_pp) + 1)) /
3721 deprn_amt;
3722 h_temp_end_pp := -1 * h_temp_end_pp;
3723 else
3724 h_temp_end_pp := 0;
3725 /*h_temp_end_pp := (h_tot_deprn * ((h_start_pd_endpp - start_pp) + 1)) /
3726 deprn_amt;
3727 h_temp_end_pp := -1 * h_temp_end_pp;*/
3728 end if; -- Bug7409643
3729 if not FA_UTILS_PKG.faxceil(h_temp_end_pp,
3730 ret.book
3731 ,p_log_level_rec => p_log_level_rec) then
3732 raise farboe_err;
3733 end if;
3734
3735 if p_log_level_rec.statement_level then
3736 fa_debug_pkg.add('farboe','after faxceil',h_temp_end_pp
3737 ,p_log_level_rec => p_log_level_rec);
3738 end if;
3739
3740 h_ret_pjdate := to_char(bk.ret_prorate_date, 'J');
3741 if p_log_level_rec.statement_level then
3742 fa_debug_pkg.add('farboe','h_ret_pjdate',h_ret_pjdate
3743 ,p_log_level_rec => p_log_level_rec);
3744 end if;
3745
3746 -- Get the depreciation start prorate period
3747 if not fa_cache_pkg.fazccp(p_cal, fiscal_year_name, h_ret_pjdate,
3748 h_ret_pp, dummy_num2, dummy_num
3749 ,p_log_level_rec => p_log_level_rec) then
3750 raise farboe_err;
3751 end if;
3752
3753 if h_ret_pp - start_pp < h_temp_end_pp then
3754 h_amt_to_retain := h_tot_deprn * ((h_ret_pp - start_pp) /
3755 h_temp_end_pp);
3756 else h_amt_to_retain := h_tot_deprn;
3757 end if;
3758
3759 if not FA_UTILS_PKG.faxrnd(h_amt_to_retain, ret.book) then
3760 fa_srvr_msg.add_message(calling_fn => l_calling_fn
3761 ,p_log_level_rec => p_log_level_rec);
3762 raise farboe_err;
3763 end if;
3764
3765 h_tot_deprn := h_tot_deprn - h_amt_to_retain;
3766
3767 end if; -- 365
3768
3769 adj_row.adjustment_amount := h_tot_deprn - nvl(h_temp_deprn_tot,0); --bug fix 3558253 and 3518604 start
3770
3771 if p_log_level_rec.statement_level then
3772 fa_debug_pkg.add('farboe','h_tot_deprn final',h_tot_deprn
3773 ,p_log_level_rec => p_log_level_rec);
3774 end if;
3775
3776 --bugfix 4380845
3777 -- Call faxrnd
3778 if not FA_UTILS_PKG.faxrnd(adj_row.adjustment_amount,book) then
3779 fa_srvr_msg.add_message(calling_fn => l_calling_fn);
3780 RAISE farboe_err;
3781 end if;
3782
3783 -- if bonus expense amount is zero, skip the following and return
3784 if (adj_row.adjustment_type = 'BONUS EXPENSE' and
3785 adj_row.adjustment_amount = 0) then
3786 return TRUE;
3787 end if;
3788
3789
3790 -- Fix for Bug#2676794
3791 if (adj_row.adjustment_type = 'REVAL EXPENSE' and
3792 (adj_row.adjustment_amount = 0 or adj_row.adjustment_amount is null)) then
3793 return TRUE;
3794 end if;
3795 if (adj_row.adjustment_type = 'REVAL AMORT' and
3796 (adj_row.adjustment_amount = 0 or adj_row.adjustment_amount is null)) then
3797 return TRUE;
3798 end if;
3799
3800
3801 if (units_retired is null or units_retired <= 0) then
3802 -- partial cost retirement
3803
3804 adj_row.selection_retid := 0;
3805 adj_row.units_retired := 0;
3806 adj_row.selection_mode := FA_STD_TYPES.FA_AJ_ACTIVE;
3807 adj_row.mrc_sob_type_code := mrc_sob_type_code;
3808
3809
3810 if (bk.book_class) then
3811 if NOT faginfo(
3812 RET, BK, cpd_ctr,today, -1
3813 ,calling_module => l_calling_fn
3814 ,candidate_mode => 'RETIRE'
3815 ,set_adj_row => TRUE
3816 ,unit_ret_in_corp => l_unit_ret_in_corp
3817 ,ret_id_in_corp => l_ret_id_in_corp
3818 ,th_id_out_in_corp => h_id_out
3819 ,balance_tfr_in_tax => l_balance_tfr_in_tax
3820 ,adj_row => adj_row
3821 ,p_log_level_rec => p_log_level_rec
3822 ) then
3823 fa_srvr_msg.add_message(calling_fn => l_calling_fn);
3824 return(FALSE);
3825 end if;
3826 end if;
3827
3828
3829 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
3830 X_last_update_date,
3831 X_last_updated_by,
3832 X_last_update_login
3833 ,p_log_level_rec => p_log_level_rec)) then
3834 fa_srvr_msg.add_message(calling_fn => l_calling_fn
3835 ,p_log_level_rec => p_log_level_rec);
3836 return(FALSE);
3837
3838 end if;
3839
3840 else -- partial unit retirement
3841
3842 adj_row.selection_retid := retirement_id;
3843 adj_row.units_retired := units_retired;
3844 adj_row.selection_mode := FA_STD_TYPES.FA_AJ_RETIRE;
3845
3846 if p_log_level_rec.statement_level then
3847 fa_debug_pkg.add
3848 (fname => l_calling_fn,
3849 element => 'dist and deprn',
3850 value => ''
3851 ,p_log_level_rec => p_log_level_rec);
3852 end if;
3853
3854 adj_row.mrc_sob_type_code := mrc_sob_type_code;
3855
3856 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
3857 X_last_update_date,
3858 X_last_updated_by,
3859 X_last_update_login
3860 ,p_log_level_rec => p_log_level_rec)) then
3861 fa_srvr_msg.add_message(calling_fn => l_calling_fn
3862 ,p_log_level_rec => p_log_level_rec);
3863 return(FALSE);
3864
3865 end if;
3866
3867 end if;
3868
3869 return(TRUE);
3870
3871 EXCEPTION
3872
3873 when others then
3874
3875 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn
3876 ,p_log_level_rec => p_log_level_rec);
3877 return FALSE;
3878
3879 END FARBOE;
3880
3881 /*==========================================================================*
3882 | NAME fagpdp |
3883 | |
3884 | FUNCTION |
3885 | |
3886 | This function figures out how much depreciation expense needs to be |
3887 | allocated to each cost center (distribution). It inserts the amount into |
3888 | the FA_ADJUSTMENTS table. |
3889 | If the number of periods to be catchup is negative or the |
3890 | DEPRECIATE_LAST_YEAR_FLAG is set to 'NO', we need to back out depreciation|
3891 | When the flag is set to NO, we need to back out the whole depreciation |
3892 | taken so far this year. |
3893 | |
3894 | |
3895 | HISTORY 1/12/89 R Rumanang Created |
3896 | 6/23/89 R Rumanang Standarized |
3897 | 8/24/89 R Rumanang Insert to FA_ADJUSTMENTS |
3898 | 04/15/91 M Chan Rewritten for MPL 9 |
3899 | 01/02/96 S Behura Rewritten into PL/SQL |
3900 *===========================================================================*/
3901
3902 FUNCTION fagpdp(ret in out nocopy fa_ret_types.ret_struct,
3903 bk in out nocopy fa_ret_types.book_struct,
3904 dpr in out nocopy FA_STD_TYPES.dpr_struct,
3905 today in date, pds_catchup number,
3906 cpd_ctr number, cpdnum number,
3907 cost_frac in number, deprn_amt in out nocopy number,
3908 bonus_deprn_amt in out nocopy number,
3909 reval_deprn_amt in out nocopy number, reval_amort in out number,
3910 reval_reserve in out nocopy number, user_id number,
3911 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) Return BOOLEAN IS
3912
3913 fagpdp_err exception;
3914 dummy number;
3915 fy_name varchar2(30);
3916 deprn_start_pnum number;
3917 deprn_start_fy number;
3918 -- adj_row FA_STD_TYPES.fa_adj_row_struct;
3919 adj_row FA_ADJUST_TYPE_PKG.fa_adj_row_struct;
3920
3921 h_work_pdnum number;
3922 h_stop_pdnum number;
3923 h_ret_p_date date;
3924 h_d_cal varchar2(16);
3925 h_asset_id number(15);
3926 h_th_id_in number(15);
3927 h_book varchar2(16);
3928 h_cpd_ctr number(15);
3929 h_deprn_amt number;
3930 h_bonus_deprn_amt number;
3931 h_reval_deprn_amt number;
3932 h_reval_amort number;
3933 h_deprn_exp_acct varchar2(30);
3934 h_bonus_deprn_exp_acct varchar2(30);
3935
3936 h_dpis_pr_jdt number;
3937 /* new variables for retirements to handle different
3938 prorate calendars */
3939
3940 h_cpp_jstartdate number;
3941
3942
3943 h_cpp_jenddate number;
3944 h_startpp number;
3945 h_endpp number;
3946 h_current_fiscal_yr integer;
3947 h_p_cal varchar2(30);
3948 h_fy_name varchar2(30);
3949
3950 h_id_out number;
3951 l_balance_tfr_in_tax number;
3952 l_unit_ret_in_corp boolean;
3953 l_ret_id_in_corp number;
3954
3955
3956 X_LAST_UPDATE_DATE date := sysdate;
3957 X_last_updated_by number := -1;
3958 X_last_update_login number := -1;
3959
3960 l_calling_fn varchar2(40) := 'FA_GAINLOSS_UPD_PKG.fagpdp';
3961
3962 BEGIN <<FAGPDP>>
3963
3964 if p_log_level_rec.statement_level then
3965 fa_debug_pkg.add(l_calling_fn, 'in FAGPDP 1', '', p_log_level_rec);
3966 fa_debug_pkg.add(l_calling_fn, 'pds_catchup', pds_catchup, p_log_level_rec);
3967 end if;
3968
3969 h_book := ret.book;
3970 h_asset_id := ret.asset_id;
3971 h_th_id_in := ret.th_id_in;
3972 h_cpd_ctr := cpd_ctr;
3973 h_ret_p_date := bk.ret_prorate_date;
3974 h_d_cal := bk.d_cal;
3975 h_p_cal := bk.p_cal;
3976 h_current_fiscal_yr := bk.cpd_fiscal_year;
3977 h_startpp := 0;
3978 h_endpp := 0;
3979 -- Bug#4867806: if (pds_catchup = 0) and (bk.depreciate_lastyr) then
3980 if (pds_catchup = 0 and bk.depr_first_year_ret = 1) and (bk.depreciate_lastyr) then
3981 return(TRUE);
3982 end if;
3983
3984 if p_log_level_rec.statement_level then fa_debug_pkg.add(l_calling_fn, 'in FAGPDP 3', '', p_log_level_rec); end if;
3985
3986 /* when depreciate_lastyr is FALSE, then we need to back out the whole
3987 depreaciation taken that year. Period Num should be the greater
3988 of the first period this fiscal year or the first 'DEPRN' row.
3989 */
3990 -- Bug#4867806: if (pds_catchup <= 0) or (not bk.depreciate_lastyr) then
3991 if (pds_catchup <= 0) or (not bk.depreciate_lastyr) or (bk.depr_first_year_ret = 0) then
3992
3993
3994 if p_log_level_rec.statement_level then fa_debug_pkg.add(l_calling_fn, 'in FAGPDP 3', '', p_log_level_rec); end if;
3995 h_stop_pdnum := cpdnum;
3996
3997 /* Determine the period number to start marching forward */
3998
3999 if not fa_cache_pkg.fazcbc(ret.book) then
4000 fa_srvr_msg.add_message(calling_fn => l_calling_fn
4001 ,p_log_level_rec => p_log_level_rec);
4002 raise fagpdp_err;
4003 end if;
4004
4005 fy_name := fa_cache_pkg.fazcbc_record.fiscal_year_name;
4006
4007 if not fa_cache_pkg.fazccp(bk.d_cal, fy_name,
4008 bk.deprn_start_jdate,
4009 deprn_start_pnum, deprn_start_fy, dummy
4010 ,p_log_level_rec => p_log_level_rec) then
4011 fa_srvr_msg.add_message(calling_fn => l_calling_fn
4012 ,p_log_level_rec => p_log_level_rec);
4013 raise fagpdp_err;
4014 end if;
4015
4016 if p_log_level_rec.statement_level then fa_debug_pkg.add(l_calling_fn, 'in FAGPDP 3.1', '', p_log_level_rec); end if;
4017
4018 -- Bug#4867806: if not bk.depreciate_lastyr then
4019 if (not bk.depreciate_lastyr) or (bk.depr_first_year_ret = 0) then
4020
4021 if p_log_level_rec.statement_level then fa_debug_pkg.add(l_calling_fn, 'in FAGPDP 4', '', p_log_level_rec); end if;
4022
4023 if (deprn_start_fy * bk.pers_per_yr + deprn_start_pnum) >=
4024 (bk.cpd_fiscal_year * bk.pers_per_yr + 1) then
4025 h_work_pdnum := deprn_start_pnum;
4026 else
4027 h_work_pdnum := 1;
4028 end if;
4029 if p_log_level_rec.statement_level then fa_debug_pkg.add(l_calling_fn, 'in FAGPDP 4.1', '', p_log_level_rec); end if;
4030 else
4031 /* get the depreciation period in which the retirement
4032 prorate date falls into */
4033 if p_log_level_rec.statement_level then
4034 fa_debug_pkg.add(l_calling_fn, 'in FAGPDP 5', '', p_log_level_rec);
4035 fa_debug_pkg.add(l_calling_fn, 'h_d_cal', h_d_cal, p_log_level_rec);
4036 fa_debug_pkg.add(l_calling_fn, 'h_ret_p_date', h_ret_p_date, p_log_level_rec);
4037 end if;
4038
4039 begin
4040 SELECT cp.period_num
4041 INTO h_work_pdnum
4042 FROM fa_calendar_periods cp
4043 WHERE h_ret_p_date
4044 between cp.start_date and cp.end_date
4045 AND cp.calendar_type = h_d_cal;
4046 EXCEPTION
4047 when no_data_found then
4048 raise fagpdp_err;
4049 end; -- end of - select
4050 if p_log_level_rec.statement_level then fa_debug_pkg.add(l_calling_fn, 'in FAGPDP 5.1.1', '', p_log_level_rec); end if;
4051
4052 if (deprn_start_fy * bk.pers_per_yr + deprn_start_pnum) >=
4053 (bk.cpd_fiscal_year * bk.pers_per_yr + h_work_pdnum) then
4054
4055 h_work_pdnum := deprn_start_pnum;
4056
4057 end if;
4058 if p_log_level_rec.statement_level then fa_debug_pkg.add(l_calling_fn, 'in FAGPDP 5.1', '', p_log_level_rec); end if;
4059
4060 end if;
4061
4062 if p_log_level_rec.statement_level then fa_debug_pkg.add(l_calling_fn, 'in FAGPDP 3.2', '', p_log_level_rec); end if;
4063
4064 /* get the first prorate prorate period in h_work_pdnum
4065 and the last prorate period in h_stop_pdnum to obtain
4066 the total number of prorate periods in the periods that
4067 we are backing out. In farboe we will use prorate
4068 period catchup / total prorate periods to backout the
4069 correct fraction of expense */
4070
4071 /*
4072 BUG# 780910: commentting this out so that the start date of the
4073 is obtained from the calendars table rather than
4074 from deprn_periods. In cases where the implementation
4075 was done in the middle of the year, the prorate period
4076 may not have been opened (i.e. half-year) --bridgway 08/23/00
4077
4078 EXEC SQL
4079 SELECT to_number (to_char (dp.calendar_period_open_date, 'J'))
4080 INTO :h_cpp_jstartdate
4081 FROM fa_deprn_periods dp
4082 WHERE dp.book_type_code = :h_book
4083 AND dp.fiscal_year = :h_current_fiscal_yr
4084 AND dp.period_num = :h_work_pdnum;
4085 */
4086
4087 SELECT to_number (to_char (dcp.start_date, 'J'))
4088 INTO h_cpp_jstartdate
4089 FROM fa_calendar_periods dcp,
4090 fa_fiscal_year fy,
4091 fa_book_controls bc
4092 WHERE bc.book_type_code = h_book
4093 AND bc.fiscal_year_name = fy.fiscal_year_name
4094 AND dcp.calendar_type = h_d_cal
4095 AND fy.fiscal_year = h_current_fiscal_yr
4096 AND dcp.period_num = h_work_pdnum
4097 AND dcp.start_date
4098 between fy.start_date and fy.end_date;
4099
4100
4101 /* Bug fix 5676896 In case retrement prorate date lies in the
4102 same period (as per deprn calendar) as of DPIS prorate date,
4103 consider greatest of DPIS prorate date and calendar period
4104 start date of retirement prorate date for calculating start_pp,
4105 otherwise consider calendar period start date of retirement
4106 prorate date as was happening earlier */
4107
4108 BEGIN
4109 SELECT to_number (to_char (bk.prorate_date, 'J'))
4110 INTO h_dpis_pr_jdt
4111 FROM fa_books bk,
4112 fa_calendar_periods dcp
4113 WHERE bk.book_type_code = ret.book
4114 AND transaction_header_id_out is null
4115 AND asset_id = ret.asset_id
4116 AND dcp.calendar_type = h_d_cal
4117 AND bk.prorate_date
4118 BETWEEN dcp.start_date AND dcp.end_date
4119 AND h_ret_p_date
4120 BETWEEN dcp.start_date AND dcp.end_date;
4121 EXCEPTION
4122 WHEN no_data_found then
4123 h_dpis_pr_jdt := null;
4124 END;
4125
4126 h_cpp_jstartdate := greatest(h_cpp_jstartdate, nvl(h_dpis_pr_jdt, h_cpp_jstartdate));
4127
4128 -- Bug fix 5676896 ends here
4129
4130 SELECT period_num
4131 INTO h_startpp
4132 FROM fa_calendar_periods
4133 WHERE calendar_type = h_p_cal
4134 AND to_date (h_cpp_jstartdate,'J')
4135 between start_date and end_date;
4136
4137 SELECT to_number (to_char (dp.calendar_period_open_date, 'J'))
4138 INTO h_cpp_jenddate
4139 FROM fa_deprn_periods dp
4140 WHERE dp.book_type_code = h_book
4141 AND dp.fiscal_year = h_current_fiscal_yr
4142 AND dp.period_num = h_stop_pdnum;
4143
4144 SELECT period_num
4145 INTO h_endpp
4146 FROM fa_calendar_periods
4147 WHERE calendar_type = h_p_cal
4148 AND to_date (h_cpp_jenddate,'J')
4149 between start_date and end_date;
4150
4151
4152 /* The amount to back-off is caculated as follows:
4153 (cost_retired/Cost for the period we're backing out) *
4154 [deprn_amount - (adjustment_amount - counter *
4155 adjustment per period)]
4156 Counter is the difference between the period we're backing out and
4157 the prorate-retirement period. However, if the Absolute value of the
4158 counter * adj per period is greater than the adjustment amount,
4159 then use deprn_amount only.
4160 If the adjustment records were put up by Retirement program, then
4161 don't use the "counter" part (the adjustment_per_period is 0)
4162 */
4163
4164 if p_log_level_rec.statement_level then fa_debug_pkg.add(l_calling_fn, 'calling farboe', '', p_log_level_rec); end if;
4165
4166 if not farboe(ret.asset_id, ret.book, bk.cpd_fiscal_year,
4167 cost_frac, h_work_pdnum, h_stop_pdnum, 'EXPENSE',
4168 bk.pers_per_yr, ret.dpr_evenly, bk.fiscal_year_name,
4169 ret.units_retired, ret.th_id_in, cpd_ctr, today,
4170 bk.cur_units,ret.retirement_id, bk.d_cal, dpr,
4171 bk.p_cal, pds_catchup, bk.depreciate_lastyr,
4172 h_startpp, h_endpp,
4173 ret.mrc_sob_type_code,
4174 ret,bk
4175 ,p_log_level_rec => p_log_level_rec) then
4176
4177 fa_srvr_msg.add_message(calling_fn => l_calling_fn
4178 ,p_log_level_rec => p_log_level_rec);
4179 raise fagpdp_err;
4180
4181 end if;
4182
4183 /* BUG# 1646713
4184 making bonus to farboe for bonus expense conditional on whether
4185 a bonus rule has been linked to the asset. Note that once a bonus
4186 rule has been assigned, it can't be removed, it could only assigned
4187 to a 0% rule which would back out expense.
4188
4189 -- bridgway 02/27/01
4190 */
4191 if (nvl(bk.bonus_rule,'NONE') <> 'NONE') then
4192
4193 if not farboe(ret.asset_id, ret.book, bk.cpd_fiscal_year,
4194 cost_frac, h_work_pdnum, h_stop_pdnum,
4195 'BONUS EXPENSE', bk.pers_per_yr,
4196 ret.dpr_evenly, bk.fiscal_year_name,
4197 ret.units_retired, ret.th_id_in, cpd_ctr,
4198 today, bk.cur_units, ret.retirement_id,
4199 bk.d_cal, dpr, bk.p_cal, pds_catchup,
4200 bk.depreciate_lastyr,h_startpp, h_endpp,
4201 ret.mrc_sob_type_code,
4202 ret,bk
4203 ,p_log_level_rec => p_log_level_rec) then
4204
4205 fa_srvr_msg.add_message(calling_fn => l_calling_fn
4206 ,p_log_level_rec => p_log_level_rec);
4207 raise fagpdp_err;
4208
4209 end if;
4210
4211 end if;
4212
4213 if (bk.current_cost > bk.unrevalued_cost
4214 or reval_reserve <> 0) then
4215 if not farboe(ret.asset_id, ret.book, bk.cpd_fiscal_year,
4216 cost_frac, h_work_pdnum, h_stop_pdnum,
4217 'REVAL EXPENSE', bk.pers_per_yr,
4218 ret.dpr_evenly, bk.fiscal_year_name,
4219 ret.units_retired, ret.th_id_in, cpd_ctr,
4220 today, bk.cur_units, ret.retirement_id,
4221 bk.d_cal, dpr, bk.p_cal, pds_catchup,
4222 bk.depreciate_lastyr,h_startpp, h_endpp,
4223 ret.mrc_sob_type_code,
4224 ret,bk
4225 ,p_log_level_rec => p_log_level_rec) then
4226
4227 -- Error in farboe for reval expense
4228 fa_srvr_msg.add_message(calling_fn => l_calling_fn
4229 ,p_log_level_rec => p_log_level_rec);
4230 raise fagpdp_err;
4231
4232 end if;
4233
4234 if not farboe(ret.asset_id, ret.book, bk.cpd_fiscal_year,
4235 cost_frac, h_work_pdnum, h_stop_pdnum,
4236 'REVAL AMORT', bk.pers_per_yr, ret.dpr_evenly,
4237 bk.fiscal_year_name, ret.units_retired,
4238 ret.th_id_in, cpd_ctr, today, bk.cur_units,
4239 ret.retirement_id, bk.d_cal, dpr,
4240 bk.p_cal, pds_catchup, bk.depreciate_lastyr,
4241 h_startpp, h_endpp,
4242 ret.mrc_sob_type_code,
4243 ret,bk
4244 ,p_log_level_rec => p_log_level_rec) then
4245 -- Error in farboe for reval amort
4246 fa_srvr_msg.add_message(calling_fn => l_calling_fn
4247 ,p_log_level_rec => p_log_level_rec);
4248 raise fagpdp_err;
4249
4250 end if;
4251
4252 end if; -- end of - if bk.current_cost
4253
4254 else -- This must be pds_catchup > 0
4255
4256 if p_log_level_rec.statement_level then fa_debug_pkg.add(l_calling_fn, 'in FAGPDP 2', '', p_log_level_rec); end if;
4257
4258 if not bk.fully_reserved then
4259 if p_log_level_rec.statement_level then fa_debug_pkg.add(l_calling_fn, 'in FAGPDP 2.1', '', p_log_level_rec); end if;
4260 /* BUG# 1400554
4261 populating the account seg for expense with the value
4262 in category books
4263 -- bridgway 09/14/00
4264
4265 adj_row.account[0] = '\0';
4266 */
4267
4268 select facb.deprn_expense_acct
4269 into h_deprn_exp_acct
4270 from fa_additions_b faadd,
4271 fa_category_books facb,
4272 fa_book_controls bc
4273 where faadd.asset_id = h_asset_id
4274 and facb.category_id = faadd.asset_category_id
4275 and facb.book_type_code = h_book
4276 and bc.book_type_code = facb.book_type_code;
4277
4278 if p_log_level_rec.statement_level then fa_debug_pkg.add(l_calling_fn, 'in FAGPDP 3', '', p_log_level_rec); end if;
4279
4280 adj_row.account := h_deprn_exp_acct;
4281
4282 adj_row.transaction_header_id := ret.th_id_in;
4283 adj_row.asset_invoice_id := 0;
4284 adj_row.source_type_code := 'RETIREMENT';
4285 adj_row.book_type_code := ret.book;
4286 adj_row.period_counter_created := cpd_ctr;
4287 adj_row.asset_id := ret.asset_id;
4288 adj_row.period_counter_adjusted := cpd_ctr;
4289 adj_row.last_update_date := today;
4290 adj_row.account_type := 'DEPRN_EXPENSE_ACCT';
4291 adj_row.current_units := bk.cur_units;
4292 adj_row.selection_thid := 0;
4293 adj_row.flush_adj_flag := TRUE;
4294 adj_row.gen_ccid_flag := TRUE;
4295 adj_row.annualized_adjustment := 0;
4296 adj_row.code_combination_id := 0;
4297 adj_row.distribution_id := 0;
4298 adj_row.leveling_flag := TRUE;
4299
4300 --
4301 -- bug3627497: Added following to prevent
4302 -- non tracked entry for member assets
4303 --
4304 if (bk.group_asset_id is not null) and
4305 (nvl(bk.member_rollup_flag, 'N') = 'N') then
4306 adj_row.track_member_flag := 'Y';
4307 else
4308 adj_row.track_member_flag := null;
4309 end if;
4310
4311 if (ret.units_retired is NULL or ret.units_retired <= 0) then
4312
4313 if p_log_level_rec.statement_level then
4314 fa_debug_pkg.add(l_calling_fn, 'in FAGPDP 4', '', p_log_level_rec);
4315 fa_debug_pkg.add
4316 (fname => l_calling_fn,
4317 element => 'Insert cost into fa_adj',
4318 value => ''
4319 ,p_log_level_rec => p_log_level_rec);
4320 end if;
4321
4322 adj_row.debit_credit_flag := 'DR';
4323 adj_row.adjustment_type := 'EXPENSE';
4324 adj_row.adjustment_amount := deprn_amt;
4325 adj_row.selection_mode := FA_STD_TYPES.FA_AJ_ACTIVE;
4326 adj_row.selection_retid := 0;
4327 adj_row.units_retired := 0;
4328 adj_row.mrc_sob_type_code := ret.mrc_sob_type_code;
4329
4330
4331 if (bk.book_class) then
4332 if NOT faginfo(
4333 RET, BK, cpd_ctr,today, user_id
4334 ,calling_module => l_calling_fn
4335 ,candidate_mode => 'RETIRE'
4336 ,set_adj_row => TRUE
4337 ,unit_ret_in_corp => l_unit_ret_in_corp
4338 ,ret_id_in_corp => l_ret_id_in_corp
4339 ,th_id_out_in_corp => h_id_out
4340 ,balance_tfr_in_tax => l_balance_tfr_in_tax
4341 ,adj_row => adj_row
4342 ,p_log_level_rec => p_log_level_rec
4343 ) then
4344 fa_srvr_msg.add_message(calling_fn => l_calling_fn);
4345 return(FALSE);
4346 end if;
4347 end if;
4348
4349
4350 -- insert expense into fa_adjustments
4351 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
4352 X_last_update_date,
4353 X_last_updated_by,
4354 X_last_update_login
4355 ,p_log_level_rec => p_log_level_rec)) then
4356 fa_srvr_msg.add_message(calling_fn => l_calling_fn
4357 ,p_log_level_rec => p_log_level_rec);
4358 return(FALSE);
4359
4360 end if; -- end of - if not FA_INS_ADJUST_PKG.faxinaj
4361
4362 if bonus_deprn_amt <> 0 then
4363 select nvl(cb.bonus_deprn_expense_acct,'0')
4364 into h_bonus_deprn_exp_acct
4365 from fa_additions_b ad,
4366 fa_category_books cb
4367 where ad.asset_id = h_asset_id
4368 and cb.category_id = ad.asset_category_id
4369 and cb.book_type_code = h_book;
4370
4371 adj_row.account := h_bonus_deprn_exp_acct;
4372 adj_row.account_type := 'BONUS_DEPRN_EXPENSE_ACCT';
4373 adj_row.debit_credit_flag := 'DR';
4374 adj_row.adjustment_type := 'BONUS EXPENSE';
4375 adj_row.adjustment_amount := bonus_deprn_amt;
4376 adj_row.selection_mode := FA_STD_TYPES.FA_AJ_ACTIVE;
4377 adj_row.selection_retid := 0;
4378 adj_row.units_retired := 0;
4379 adj_row.mrc_sob_type_code := ret.mrc_sob_type_code;
4380
4381 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
4382 X_last_update_date,
4383 X_last_updated_by,
4384 X_last_update_login
4385 ,p_log_level_rec => p_log_level_rec)) then
4386
4387 fa_srvr_msg.add_message(calling_fn => l_calling_fn
4388 ,p_log_level_rec => p_log_level_rec);
4389 return(FALSE);
4390
4391 end if; -- end of if not FA_INS_ADJUST_PKG.faxinaj
4392
4393 end if;
4394
4395 -- BUG# 1400554
4396 -- resetting the account segment to value prior to
4397 -- the bonus rule logic. i.e. = cb.deprn_exp_acct
4398 -- adj_row.account[0] = '\0';
4399
4400 adj_row.account := h_deprn_exp_acct;
4401 adj_row.account_type := 'DEPRN_EXPENSE_ACCT';
4402
4403 if reval_deprn_amt > 0 then
4404
4405 adj_row.debit_credit_flag := 'DR';
4406 adj_row.adjustment_type := 'REVAL EXPENSE';
4407 adj_row.adjustment_amount := reval_deprn_amt;
4408
4409 -- This is now obsolete. We do not calculate reval expense
4410 -- seperately from deprn expense. All expense is calculated
4411 -- by the deprn engine. I am leaving logic to calculate this
4412 -- in case of future use, but we should not insert the
4413 -- adjustment rows.
4414
4415 end if;
4416
4417 if reval_amort > 0 then
4418
4419 adj_row.debit_credit_flag := 'DR';
4420 adj_row.adjustment_type := 'REVAL AMORT';
4421 adj_row.adjustment_amount := reval_amort;
4422 adj_row.mrc_sob_type_code := ret.mrc_sob_type_code;
4423
4424 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
4425 X_last_update_date,
4426 X_last_updated_by,
4427 X_last_update_login
4428 ,p_log_level_rec => p_log_level_rec)) then
4429
4430 fa_srvr_msg.add_message(calling_fn => l_calling_fn
4431 ,p_log_level_rec => p_log_level_rec);
4432 return(FALSE);
4433
4434 end if;
4435
4436 end if; -- end of - if reval_amort
4437
4438 else
4439
4440 adj_row.debit_credit_flag := 'DR';
4441 adj_row.adjustment_type := 'EXPENSE';
4442 adj_row.adjustment_amount := deprn_amt;
4443 adj_row.selection_mode := FA_STD_TYPES.FA_AJ_RETIRE;
4444 adj_row.selection_retid := ret.retirement_id;
4445 adj_row.units_retired := ret.units_retired;
4446 adj_row.mrc_sob_type_code := ret.mrc_sob_type_code;
4447
4448 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
4449 X_last_update_date,
4450 X_last_updated_by,
4451 X_last_update_login
4452 ,p_log_level_rec => p_log_level_rec)) then
4453
4454 fa_srvr_msg.add_message(calling_fn => l_calling_fn
4455 ,p_log_level_rec => p_log_level_rec);
4456 return(FALSE);
4457
4458 end if;
4459
4460
4461 if bonus_deprn_amt <> 0 then
4462
4463 select nvl(cb.bonus_deprn_expense_acct,'0')
4464 into h_bonus_deprn_exp_acct
4465 from fa_additions_b ad,
4466 fa_category_books cb
4467 where ad.asset_id = h_asset_id
4468 and cb.category_id = ad.asset_category_id
4469 and cb.book_type_code = h_book;
4470
4471 adj_row.account := h_bonus_deprn_exp_acct;
4472 adj_row.account_type := 'BONUS_DEPRN_EXPENSE_ACCT';
4473 adj_row.debit_credit_flag := 'DR';
4474 adj_row.adjustment_type := 'BONUS EXPENSE';
4475 adj_row.adjustment_amount := bonus_deprn_amt;
4476 adj_row.selection_mode := FA_STD_TYPES.FA_AJ_ACTIVE;
4477 adj_row.selection_retid := 0;
4478 adj_row.units_retired := 0;
4479 adj_row.mrc_sob_type_code := ret.mrc_sob_type_code;
4480
4481 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
4482 X_last_update_date,
4483 X_last_updated_by,
4484 X_last_update_login
4485 ,p_log_level_rec => p_log_level_rec)) then
4486
4487 fa_srvr_msg.add_message(calling_fn => l_calling_fn
4488 ,p_log_level_rec => p_log_level_rec);
4489 return(FALSE);
4490
4491 end if; -- end of if not FA_INS_ADJUST_PKG.faxinaj
4492
4493 end if;
4494
4495 -- BUG# 1400554
4496 -- resetting the account segment to value prior to
4497 -- the bonus rule logic. i.e. = cb.deprn_exp_acct
4498 -- adj_row.account[0] = '\0';
4499
4500 adj_row.account := h_deprn_exp_acct;
4501 adj_row.account_type := 'DEPRN_EXPENSE_ACCT';
4502
4503 if reval_deprn_amt > 0 then
4504
4505 adj_row.debit_credit_flag := 'DR';
4506 adj_row.adjustment_type := 'REVAL EXPENSE';
4507 adj_row.adjustment_amount := reval_deprn_amt;
4508 adj_row.mrc_sob_type_code := ret.mrc_sob_type_code;
4509
4510 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
4511 X_last_update_date,
4512 X_last_updated_by,
4513 X_last_update_login
4514 ,p_log_level_rec => p_log_level_rec)) then
4515
4516 fa_srvr_msg.add_message(calling_fn => l_calling_fn
4517 ,p_log_level_rec => p_log_level_rec);
4518 return(FALSE);
4519
4520 end if;
4521
4522 end if; -- end of - if reval_deprn_amt
4523
4524 if reval_amort > 0 then
4525
4526 adj_row.debit_credit_flag := 'DR';
4527 adj_row.adjustment_type := 'REVAL AMORT';
4528 adj_row.adjustment_amount := reval_amort;
4529 adj_row.mrc_sob_type_code := ret.mrc_sob_type_code;
4530
4531 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
4532 X_last_update_date,
4533 X_last_updated_by,
4534 X_last_update_login
4535 ,p_log_level_rec => p_log_level_rec)) then
4536
4537 fa_srvr_msg.add_message(calling_fn => l_calling_fn
4538 ,p_log_level_rec => p_log_level_rec);
4539 return(FALSE);
4540
4541 end if;
4542
4543 end if; -- end of - if reval_amort
4544
4545 end if; -- end of - if (ret.units_retired
4546
4547 end if; -- end of - if not bk.fully_reserved
4548
4549 end if; -- end of - if (pds_catchup = 0)
4550
4551 if p_log_level_rec.statement_level then fa_debug_pkg.add(l_calling_fn, 'in FAGPDP 3', '', p_log_level_rec); end if;
4552 h_deprn_amt := 0;
4553 h_reval_deprn_amt := 0;
4554 h_reval_amort := 0;
4555 h_bonus_deprn_amt := 0;
4556
4557 if p_log_level_rec.statement_level then
4558 fa_debug_pkg.add
4559 (fname => l_calling_fn,
4560 element => 'total deprn expense',
4561 value => ''
4562 ,p_log_level_rec => p_log_level_rec);
4563 end if;
4564
4565 if (ret.mrc_sob_type_code <> 'R') then
4566 begin
4567 SELECT SUM(faadj.adjustment_amount)
4568 INTO h_deprn_amt
4569 FROM FA_ADJUSTMENTS faadj
4570 WHERE
4571 faadj.transaction_header_id = h_th_id_in
4572 AND faadj.source_type_code = 'RETIREMENT'
4573 AND faadj.adjustment_type = 'EXPENSE'
4574 AND faadj.book_type_Code = h_book
4575 AND faadj.asset_id = h_asset_id
4576 AND faadj.period_counter_created = h_cpd_ctr
4577 GROUP BY faadj.transaction_header_id;
4578
4579 /* Test for a no rows found condition;
4580 * return zeroes in this case.
4581 * Set h_found_period_counter to zero also.
4582 */
4583 EXCEPTION
4584 when no_data_found then
4585 h_deprn_amt := 0;
4586 end;
4587
4588 else
4589
4590 begin
4591 SELECT SUM(faadj.adjustment_amount)
4592 INTO h_deprn_amt
4593 FROM FA_ADJUSTMENTS_MRC_V faadj
4594 WHERE
4595 faadj.transaction_header_id = h_th_id_in
4596 AND faadj.source_type_code = 'RETIREMENT'
4597 AND faadj.adjustment_type = 'EXPENSE'
4598 AND faadj.book_type_Code = h_book
4599 AND faadj.asset_id = h_asset_id
4600 AND faadj.period_counter_created = h_cpd_ctr
4601 GROUP BY faadj.transaction_header_id;
4602
4603 /* Test for a no rows found condition;
4604 * return zeroes in this case.
4605 * Set h_found_period_counter to zero also.
4606 */
4607 EXCEPTION
4608 when no_data_found then
4609 h_deprn_amt := 0;
4610 end;
4611
4612 end if;
4613
4614 deprn_amt := h_deprn_amt;
4615
4616 if (ret.mrc_sob_type_code <> 'R') then
4617 begin
4618 SELECT SUM(faadj.adjustment_amount)
4619 INTO h_bonus_deprn_amt
4620 FROM FA_ADJUSTMENTS faadj
4621 WHERE
4622 faadj.transaction_header_id = h_th_id_in
4623 AND faadj.source_type_code = 'RETIREMENT'
4624 AND faadj.adjustment_type = 'BONUS EXPENSE'
4625 AND faadj.book_type_Code = h_book
4626 AND faadj.asset_id = h_asset_id
4627 AND faadj.period_counter_created = h_cpd_ctr
4628 GROUP BY faadj.transaction_header_id;
4629
4630 /* Test for a no rows found condition;
4631 * return zeroes in this case.
4632 * Set h_found_period_counter to zero also.
4633 */
4634 EXCEPTION
4635 when no_data_found then
4636 h_bonus_deprn_amt := 0;
4637 end;
4638 else
4639 begin
4640 SELECT SUM(faadj.adjustment_amount)
4641 INTO h_bonus_deprn_amt
4642 FROM FA_ADJUSTMENTS_MRC_V faadj
4643 WHERE
4644 faadj.transaction_header_id = h_th_id_in
4645 AND faadj.source_type_code = 'RETIREMENT'
4646 AND faadj.adjustment_type = 'BONUS EXPENSE'
4647 AND faadj.book_type_Code = h_book
4648 AND faadj.asset_id = h_asset_id
4649 AND faadj.period_counter_created = h_cpd_ctr
4650 GROUP BY faadj.transaction_header_id;
4651 /* Test for a no rows found condition;
4652 * return zeroes in this case.
4653 * Set h_found_period_counter to zero also.
4654 */
4655 EXCEPTION
4656 when no_data_found then
4657 h_bonus_deprn_amt := 0;
4658 end;
4659 end if;
4660
4661 bonus_deprn_amt := h_bonus_deprn_amt;
4662
4663 if p_log_level_rec.statement_level then
4664 fa_debug_pkg.add
4665 (fname => l_calling_fn,
4666 element => 'total reval deprn expense',
4667 value => ''
4668 ,p_log_level_rec => p_log_level_rec);
4669 end if;
4670
4671 if (ret.mrc_sob_type_code <> 'R') then
4672 begin
4673 SELECT SUM(faadj.adjustment_amount)
4674 INTO h_reval_deprn_amt
4675 FROM FA_ADJUSTMENTS faadj
4676 WHERE
4677 faadj.transaction_header_id = h_th_id_in
4678 AND faadj.source_type_code = 'RETIREMENT'
4679 AND faadj.adjustment_type = 'REVAL EXPENSE'
4680 AND faadj.book_type_Code = h_book
4681 AND faadj.asset_id = h_asset_id
4682 AND faadj.period_counter_created = h_cpd_ctr
4683 GROUP BY faadj.transaction_header_id;
4684
4685 EXCEPTION
4686 when no_data_found then
4687 h_reval_deprn_amt := 0;
4688 end;
4689 else
4690 begin
4691 SELECT SUM(faadj.adjustment_amount)
4692 INTO h_reval_deprn_amt
4693 FROM FA_ADJUSTMENTS_MRC_V faadj
4694 WHERE
4695 faadj.transaction_header_id = h_th_id_in
4696 AND faadj.source_type_code = 'RETIREMENT'
4697 AND faadj.adjustment_type = 'REVAL EXPENSE'
4698 AND faadj.book_type_Code = h_book
4699 AND faadj.asset_id = h_asset_id
4700 AND faadj.period_counter_created = h_cpd_ctr
4701 GROUP BY faadj.transaction_header_id;
4702
4703 EXCEPTION
4704 when no_data_found then
4705 h_reval_deprn_amt := 0;
4706 end;
4707 end if;
4708
4709 reval_deprn_amt := h_reval_deprn_amt;
4710
4711 if p_log_level_rec.statement_level then
4712 fa_debug_pkg.add
4713 (fname => l_calling_fn,
4714 element => 'total reval amort',
4715 value => ''
4716 ,p_log_level_rec => p_log_level_rec);
4717 end if;
4718
4719 if (ret.mrc_sob_type_code <> 'R') then
4720 begin
4721 SELECT SUM(faadj.adjustment_amount)
4722 INTO h_reval_amort
4723 FROM FA_ADJUSTMENTS faadj
4724 WHERE
4725 faadj.transaction_header_id = h_th_id_in
4726 AND faadj.source_type_code = 'RETIREMENT'
4727 AND faadj.adjustment_type = 'REVAL AMORT'
4728 AND faadj.book_type_Code = h_book
4729 AND faadj.asset_id = h_asset_id
4730 AND faadj.period_counter_created = h_cpd_ctr
4731 GROUP BY faadj.transaction_header_id;
4732
4733 EXCEPTION
4734 when no_data_found then
4735 h_reval_amort := 0;
4736 end;
4737 else
4738 begin
4739 SELECT SUM(faadj.adjustment_amount)
4740 INTO h_reval_amort
4741 FROM FA_ADJUSTMENTS_MRC_V faadj
4742 WHERE
4743 faadj.transaction_header_id = h_th_id_in
4744 AND faadj.source_type_code = 'RETIREMENT'
4745 AND faadj.adjustment_type = 'REVAL AMORT'
4746 AND faadj.book_type_Code = h_book
4747 AND faadj.asset_id = h_asset_id
4748 AND faadj.period_counter_created = h_cpd_ctr
4749 GROUP BY faadj.transaction_header_id;
4750
4751 EXCEPTION
4752 when no_data_found then
4753 h_reval_amort := 0;
4754 end;
4755 end if;
4756
4757 reval_amort := h_reval_amort;
4758
4759 return(TRUE);
4760
4761
4762 EXCEPTION
4763
4764 when others then
4765
4766 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn
4767 ,p_log_level_rec => p_log_level_rec);
4768 return FALSE;
4769
4770
4771 END FAGPDP;
4772
4773
4774 /*===========================================================================*
4775 | NAME fagprv |
4776 | |
4777 | FUNCTION Calculate reserve retired and insert it into FA_ADJUSTMENTS. |
4778 | It returns the current depreciation reserve before adjusted. |
4779 | |
4780 | HISTORY 08/30/89 R Rumanang Created |
4781 | 11/21/89 R Rumanang Put distribution_id in adjustments |
4782 | 05/03/91 M Chan Rewrote for MPL 9 |
4783 | 01/03/96 S Behura Rewrote using PL/SQL |
4784 | |
4785 *============================================================================*/
4786
4787 FUNCTION fagprv(ret in out nocopy fa_ret_types.ret_struct,
4788 bk in out nocopy fa_ret_types.book_struct,
4789 cpd_ctr number, cost_frac in number,
4790 today in date, user_id number,
4791 deprn_amt in out nocopy number, reval_deprn_amt in out number,
4792 reval_amort in out nocopy number, deprn_reserve in out number,
4793 reval_reserve in out nocopy number,
4794 bonus_deprn_amt in out nocopy number,
4795 bonus_deprn_reserve in out nocopy number,
4796 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) Return BOOLEAN IS
4797
4798 CURSOR c_get_unit is
4799 select units
4800 from fa_asset_history
4801 where asset_id = bk.group_asset_id
4802 and transaction_header_id_out is null;
4803
4804 CURSOR c_get_cost is
4805 select cost
4806 from fa_books
4807 where asset_id = bk.group_asset_id
4808 and book_type_code = ret.book
4809 and date_ineffective is null;
4810
4811
4812 fagprv_err exception;
4813
4814 tot_deprn_reserve number;
4815 tot_reval_reserve number;
4816 tot_bonus_deprn_reserve number;
4817 deprn_rsv_acct varchar2(30);
4818 reval_rsv_acct varchar2(30);
4819 bonus_deprn_rsv_acct varchar2(30);
4820 -- adj_row FA_STD_TYPES.fa_adj_row_struct;
4821 adj_row FA_ADJUST_TYPE_PKG.fa_adj_row_struct;
4822
4823 h_retire_reval_flag number;
4824 h_asset_id number(15);
4825 h_book varchar2(15);
4826 h_ret_id number(15);
4827 h_id_out number(15);
4828 h_deprn_rsv_acct varchar2(30);
4829 h_reval_rsv_acct varchar2(30);
4830 h_bonus_deprn_rsv_acct varchar2(30);
4831 h_cur_units number;
4832
4833 -- Fix for Bug 3441030
4834 l_prev_deprn_reserve number;
4835 l_prev_adj_rec_cost number;
4836 l_new_adj_rec_cost number;
4837 l_final_rsv number;
4838 l_fully_rsvd_flag boolean := FALSE;
4839
4840 X_LAST_UPDATE_DATE date := sysdate;
4841 X_last_updated_by number := -1;
4842 X_last_update_login number := -1;
4843
4844 l_calling_fn varchar2(40) := 'FA_GAINLOSS_UPD_PKG.fagprv';
4845
4846 l_rsv_retired number := null;
4847 l_temp_num number;
4848 l_temp_char varchar2(30);
4849 l_temp_bool boolean;
4850 l_g_cost number;
4851 l_g_rsv number;
4852 l_g_bonus_rsv number;
4853 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
4854 l_asset_cat_rec FA_API_TYPES.asset_cat_rec_type;
4855
4856
4857 l_balance_tfr_in_tax number;
4858 l_unit_ret_in_corp boolean;
4859 l_ret_id_in_corp number;
4860
4861
4862 l_dummy number;
4863 h_sum_of_part_active_units number;
4864
4865
4866 BEGIN <<FAGPRV>>
4867
4868 if p_log_level_rec.statement_level then
4869 fa_debug_pkg.add
4870 (fname => l_calling_fn,
4871 element => 'cost frac in fagprv',
4872 value => to_char(cost_frac)||':'||to_char(ret.reserve_retired));
4873 end if;
4874
4875 l_rsv_retired := ret.reserve_retired;
4876
4877 tot_deprn_reserve := 0;
4878 tot_bonus_deprn_reserve := 0;
4879 tot_reval_reserve := 0;
4880 h_cur_units := 0;
4881 h_asset_id := ret.asset_id;
4882 h_book := ret.book;
4883 h_ret_id := ret.retirement_id;
4884
4885 -- Fix for Bug 3441030
4886 l_prev_deprn_reserve := deprn_reserve;
4887 select adjusted_recoverable_cost
4888 into l_prev_adj_rec_cost
4889 from fa_books
4890 where transaction_header_id_out = ret.th_id_in;
4891
4892 if (l_prev_deprn_reserve = l_prev_adj_rec_cost) then
4893 l_fully_rsvd_flag := TRUE;
4894 select adjusted_recoverable_cost
4895 into l_new_adj_rec_cost
4896 from fa_books
4897 where transaction_header_id_in = ret.th_id_in;
4898 l_final_rsv := l_new_adj_rec_cost;
4899 end if;
4900
4901 deprn_reserve := deprn_reserve * cost_frac;
4902 /* Bug#4648226 rounding issue */
4903 if not FA_UTILS_PKG.faxrnd(deprn_reserve, ret.book) then
4904 fa_srvr_msg.add_message(calling_fn => l_calling_fn);
4905 raise fagprv_err;
4906 end if;
4907
4908 -- fix for 1972854 - cost_frac is set to zero when cost is zero
4909 -- which causes reval_reserve go zero, thus do the following
4910 -- multiplication only for non-zero cost_frac
4911 if cost_frac <> 0 then
4912 reval_reserve := reval_reserve * cost_frac;
4913
4914 /* Bug#4459585 rounding issue */
4915 if not FA_UTILS_PKG.faxrnd(reval_reserve, ret.book) then
4916 fa_srvr_msg.add_message(calling_fn => l_calling_fn);
4917 raise fagprv_err;
4918 end if;
4919
4920 end if;
4921
4922 bonus_deprn_reserve := bonus_deprn_reserve * cost_frac;
4923
4924 /* Bug#4459585 rounding issue */
4925 if not FA_UTILS_PKG.faxrnd(bonus_deprn_reserve, ret.book) then
4926 fa_srvr_msg.add_message(calling_fn => l_calling_fn);
4927 raise fagprv_err;
4928 end if;
4929
4930 if p_log_level_rec.statement_level then
4931 fa_debug_pkg.add
4932 (fname => l_calling_fn,
4933 element => 'deprn_reserve in fagprv',
4934 value => to_char(deprn_reserve));
4935 fa_debug_pkg.add
4936 (fname => l_calling_fn,
4937 element => 'deprn_amt in fagprv',
4938 value => to_char(deprn_amt));
4939 end if;
4940
4941 tot_deprn_reserve := deprn_reserve + deprn_amt;
4942 tot_reval_reserve := reval_reserve - reval_amort;
4943 tot_bonus_deprn_reserve := bonus_deprn_reserve + bonus_deprn_amt;
4944
4945 -- Fix for Bug 3441030
4946 if (l_fully_rsvd_flag and deprn_amt = 0) then
4947 tot_deprn_reserve := l_prev_deprn_reserve - l_final_rsv;
4948 end if;
4949
4950 /*
4951 * Round tot_deprn_reserve according to functional currency
4952 */
4953 if not FA_UTILS_PKG.faxrnd(tot_deprn_reserve, ret.book) then
4954 fa_srvr_msg.add_message(calling_fn => l_calling_fn
4955 ,p_log_level_rec => p_log_level_rec);
4956 raise fagprv_err;
4957 end if;
4958
4959 if not FA_UTILS_PKG.faxrnd(tot_bonus_deprn_reserve, ret.book) then
4960 fa_srvr_msg.add_message(calling_fn => l_calling_fn
4961 ,p_log_level_rec => p_log_level_rec);
4962 raise fagprv_err;
4963 end if;
4964
4965
4966 /*
4967 * Round tot_reval_reserve according to functional currency
4968 */
4969
4970 if not FA_UTILS_PKG.faxrnd(tot_reval_reserve, ret.book) then
4971 fa_srvr_msg.add_message(calling_fn => l_calling_fn
4972 ,p_log_level_rec => p_log_level_rec);
4973 raise fagprv_err;
4974 end if;
4975
4976 if p_log_level_rec.statement_level then
4977 fa_debug_pkg.add
4978 (fname => l_calling_fn,
4979 element => 'acct segment in fagprv',
4980 value => ''
4981 ,p_log_level_rec => p_log_level_rec);
4982 end if;
4983
4984 h_retire_reval_flag := 0;
4985
4986 begin
4987 select facb.deprn_reserve_acct,
4988 facb.reval_reserve_acct,
4989 facb.bonus_deprn_reserve_acct,
4990 decode(bc.retire_reval_reserve_flag,'NO',0,
4991 decode(facb.reval_reserve_acct,null,0,1))
4992 into h_deprn_rsv_acct,
4993 h_reval_rsv_acct,
4994 h_bonus_deprn_rsv_acct,
4995 h_retire_reval_flag
4996 from fa_additions_b faadd,
4997 fa_category_books facb,
4998 fa_book_controls bc
4999 where faadd.asset_id = h_asset_id
5000 and facb.category_id = faadd.asset_category_id
5001 and facb.book_type_code = h_book
5002 and bc.book_type_code = facb.book_type_code;
5003 EXCEPTION
5004 when no_data_found then
5005 raise fagprv_err;
5006 end;
5007
5008 deprn_rsv_acct := h_deprn_rsv_acct;
5009 reval_rsv_acct := h_reval_rsv_acct;
5010 bonus_deprn_rsv_acct := h_bonus_deprn_rsv_acct;
5011 adj_row.transaction_header_id := ret.th_id_in;
5012 adj_row.source_type_code := 'RETIREMENT';
5013 adj_row.book_type_code := ret.book;
5014 adj_row.period_counter_created := cpd_ctr;
5015 adj_row.asset_id := ret.asset_id;
5016 adj_row.period_counter_adjusted := cpd_ctr;
5017 adj_row.last_update_date := today;
5018 adj_row.current_units := bk.cur_units;
5019 adj_row.gen_ccid_flag := TRUE;
5020 adj_row.flush_adj_flag := TRUE;
5021 adj_row.annualized_adjustment := 0;
5022 adj_row.code_combination_id := 0;
5023 adj_row.distribution_id := 0;
5024 adj_row.selection_retid := 0;
5025 adj_row.units_retired := 0;
5026 adj_row.asset_invoice_id := 0;
5027 adj_row.leveling_flag := TRUE;
5028
5029 if p_log_level_rec.statement_level then
5030 fa_debug_pkg.add
5031 (fname => l_calling_fn,
5032 element => 'deprn_allocation_code in fagprv',
5033 value => ''
5034 ,p_log_level_rec => p_log_level_rec);
5035 end if;
5036
5037 begin
5038 select decode(RETIRE_REVAL_RESERVE_FLAG,'NO',0,1)
5039 into h_retire_reval_flag
5040 from fa_book_controls bc
5041 where bc.book_type_code = h_book;
5042
5043 EXCEPTION
5044 when no_data_found then
5045 raise fagprv_err;
5046 end;
5047
5048 if (ret.units_retired is null or ret.units_retired <= 0) and
5049 (not((bk.group_asset_id is not null) and
5050 (nvl(bk.tracking_method, 'ALLOCATE') = 'ALLOCATE'))) then
5051
5052 adj_row.account := deprn_rsv_acct;
5053 adj_row.account_type := 'DEPRN_RESERVE_ACCT';
5054 adj_row.adjustment_type := 'RESERVE';
5055 adj_row.adjustment_amount := tot_deprn_reserve;
5056 adj_row.selection_thid := 0;
5057 adj_row.debit_credit_flag := 'DR';
5058
5059 if (bk.current_cost = ret.cost_retired) then
5060 adj_row.selection_mode := FA_STD_TYPES.FA_AJ_CLEAR;
5061 else
5062 adj_row.selection_mode := FA_STD_TYPES.FA_AJ_ACTIVE;
5063 -- Bug 5149832
5064 if (bk.book_class) then
5065 if NOT faginfo(
5066 RET, BK, cpd_ctr,today, user_id
5067 ,calling_module => l_calling_fn
5068 ,candidate_mode => 'CLEAR_PARTIAL'
5069 ,set_adj_row => TRUE
5070 ,unit_ret_in_corp => l_unit_ret_in_corp
5071 ,ret_id_in_corp => l_ret_id_in_corp
5072 ,th_id_out_in_corp => h_id_out
5073 ,balance_tfr_in_tax => l_balance_tfr_in_tax
5074 ,adj_row => adj_row
5075 ,p_log_level_rec => p_log_level_rec
5076 ) then
5077 fa_srvr_msg.add_message(calling_fn => l_calling_fn);
5078 return(FALSE);
5079 end if;
5080 end if;
5081
5082 end if;
5083
5084 if p_log_level_rec.statement_level then
5085 fa_debug_pkg.add(l_calling_fn, '++ bk.book_class (TRUE=TAX)', bk.book_class, p_log_level_rec);
5086 fa_debug_pkg.add(l_calling_fn, '++ adj_row.selection_thid', adj_row.selection_thid, p_log_level_rec);
5087 end if;
5088
5089 adj_row.mrc_sob_type_code := ret.mrc_sob_type_code;
5090
5091 if (bk.group_asset_id is not null) and
5092 (nvl(bk.member_rollup_flag, 'N') = 'N') then
5093 adj_row.track_member_flag := 'Y';
5094 else
5095 adj_row.track_member_flag := null;
5096 end if;
5097
5098 if p_log_level_rec.statement_level then
5099 fa_debug_pkg.add
5100 (fname => l_calling_fn,
5101 element => 'Before faxinaj for RESERVE',
5102 value => ''
5103 ,p_log_level_rec => p_log_level_rec);
5104
5105 fa_debug_pkg.add
5106 (fname => l_calling_fn,
5107 element => 'account',
5108 value => adj_row.account
5109 ,p_log_level_rec => p_log_level_rec);
5110
5111 end if;
5112
5113 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
5114 X_last_update_date,
5115 X_last_updated_by,
5116 X_last_update_login
5117 ,p_log_level_rec => p_log_level_rec)) then
5118
5119 fa_srvr_msg.add_message(calling_fn => l_calling_fn
5120 ,p_log_level_rec => p_log_level_rec);
5121 return(FALSE);
5122
5123 end if;
5124
5125 if p_log_level_rec.statement_level then
5126 fa_debug_pkg.add
5127 (fname => l_calling_fn,
5128 element => 'After faxinaj for RESERVE',
5129 value => ''
5130 ,p_log_level_rec => p_log_level_rec);
5131 end if;
5132
5133 if (tot_bonus_deprn_reserve <> 0) then
5134
5135 adj_row.account := bonus_deprn_rsv_acct;
5136 adj_row.account_type := 'BONUS_DEPRN_RESERVE_ACCT';
5137 adj_row.adjustment_type := 'BONUS RESERVE';
5138 adj_row.adjustment_amount := tot_bonus_deprn_reserve;
5139 adj_row.mrc_sob_type_code := ret.mrc_sob_type_code;
5140
5141 if (bk.group_asset_id is not null) and
5142 (nvl(bk.member_rollup_flag, 'N') = 'N') then
5143 adj_row.track_member_flag := 'Y';
5144 else
5145 adj_row.track_member_flag := null;
5146 end if;
5147
5148 if adj_row.adjustment_amount <> 0 then
5149
5150 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
5151 X_last_update_date,
5152 X_last_updated_by,
5153 X_last_update_login
5154 ,p_log_level_rec => p_log_level_rec)) then
5155
5156 fa_srvr_msg.add_message(calling_fn => l_calling_fn
5157 ,p_log_level_rec => p_log_level_rec);
5158 return(FALSE);
5159
5160 end if;
5161
5162 end if;
5163
5164 end if; -- end of tot_bonus_deprn_reserve <> 0
5165
5166
5167 if p_log_level_rec.statement_level then
5168 fa_debug_pkg.add
5169 (fname => l_calling_fn,
5170 element => 'After faxinaj for BONUS RESERVE',
5171 value => ''
5172 ,p_log_level_rec => p_log_level_rec);
5173 end if;
5174
5175 if (bk.current_cost > bk.unrevalued_cost OR reval_reserve <> 0) and
5176 (h_retire_reval_flag = 1) then
5177
5178 adj_row.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE_REVAL;
5179 -- adj_row.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
5180 adj_row.account := reval_rsv_acct;
5181 adj_row.account_type := 'REVAL_RESERVE_ACCT';
5182 adj_row.adjustment_type := 'REVAL RESERVE';
5183 adj_row.adjustment_amount := tot_reval_reserve;
5184 adj_row.mrc_sob_type_code := ret.mrc_sob_type_code;
5185
5186 if (bk.group_asset_id is not null) and
5187 (nvl(bk.member_rollup_flag, 'N') = 'N') then
5188 adj_row.track_member_flag := 'Y';
5189 else
5190 adj_row.track_member_flag := null;
5191 end if;
5192
5193 if adj_row.adjustment_amount <> 0 then
5194 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
5195 X_last_update_date,
5196 X_last_updated_by,
5197 X_last_update_login
5198 ,p_log_level_rec => p_log_level_rec)) then
5199
5200 fa_srvr_msg.add_message(calling_fn => l_calling_fn
5201 ,p_log_level_rec => p_log_level_rec);
5202 return(FALSE);
5203
5204 end if;
5205 end if;
5206
5207 end if; -- end of - if (bk.current_cost
5208
5209 -- Bug3766289: Old condition didn't process tracked member asset
5210 -- elsif (bk.group_asset_id is null) then
5211 elsif (not((bk.group_asset_id is not null) and
5212 (nvl(bk.tracking_method, 'ALLOCATE') = 'ALLOCATE'))) then
5213
5214 if p_log_level_rec.statement_level then
5215 fa_debug_pkg.add
5216 (fname => l_calling_fn,
5217 element => 'transaction_header_id_out in fagprv',
5218 value => ''
5219 ,p_log_level_rec => p_log_level_rec);
5220 end if;
5221
5222 begin
5223 select distinct nvl(transaction_header_id_out,0)
5224 into h_id_out
5225 from fa_distribution_history
5226 where asset_id = h_asset_id
5227 and book_type_code = h_book
5228 and retirement_id = h_ret_id;
5229
5230 EXCEPTION
5231 when no_data_found then
5232 raise fagprv_err;
5233 end;
5234
5235 -- Bug# 5170275
5236 h_sum_of_part_active_units := 0;
5237
5238 begin
5239
5240 select sum(nvl(units_assigned,0))
5241 into h_sum_of_part_active_units
5242 from fa_distribution_history
5243 where asset_id = h_asset_id
5244 and transaction_header_id_in = h_id_out
5245 and date_ineffective is null;
5246
5247 exception
5248 when no_data_found then
5249 h_sum_of_part_active_units := 0;
5250 end;
5251
5252 adj_row.account := deprn_rsv_acct;
5253 adj_row.account_type := 'DEPRN_RESERVE_ACCT';
5254 adj_row.adjustment_type := 'RESERVE';
5255 adj_row.adjustment_amount := 0;
5256 adj_row.selection_thid := h_id_out;
5257 adj_row.debit_credit_flag := 'DR';
5258
5259 /* Fix for Bug#4617352: We have decided to create adj lines only for affected rows
5260 to avoid rounding issues with remaining rows in partial unit intercompany retirement.
5261 */
5262 adj_row.selection_mode := FA_STD_TYPES.FA_AJ_CLEAR_PARTIAL;
5263 adj_row.mrc_sob_type_code := ret.mrc_sob_type_code;
5264
5265 if (bk.group_asset_id is not null) and
5266 (nvl(bk.member_rollup_flag, 'N') = 'N') then
5267 adj_row.track_member_flag := 'Y';
5268 else
5269 adj_row.track_member_flag := null;
5270 end if;
5271
5272 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
5273 X_last_update_date,
5274 X_last_updated_by,
5275 X_last_update_login
5276 ,p_log_level_rec => p_log_level_rec)) then
5277
5278 fa_srvr_msg.add_message(calling_fn => l_calling_fn
5279 ,p_log_level_rec => p_log_level_rec);
5280 return(FALSE);
5281
5282 end if;
5283
5284 adj_row.adjustment_amount := adj_row.amount_inserted -
5285 tot_deprn_reserve;
5286
5287 if p_log_level_rec.statement_level then
5288 fa_debug_pkg.add
5289 (fname => l_calling_fn,
5290 element => 'cleared amount in fagprv(2)',
5291 value => adj_row.amount_inserted);
5292 fa_debug_pkg.add
5293 (fname => l_calling_fn,
5294 element => 'cost_frac in fagprv(2)',
5295 value => cost_frac);
5296 fa_debug_pkg.add
5297 (fname => l_calling_fn,
5298 element => 're-allocated amount in fagprv(2)',
5299 value => adj_row.adjustment_amount);
5300 end if;
5301
5302 /*
5303 select nvl(units,0)
5304 into h_cur_units
5305 from fa_asset_history
5306 where asset_id = h_asset_id
5307 and date_ineffective is null;
5308 */
5309
5310 h_cur_units := 0;
5311
5312 begin
5313
5314 select count(*)
5315 into l_dummy
5316 from fa_distribution_history
5317 where asset_id = h_asset_id
5318 and date_ineffective is null
5319 and transaction_header_id_in = h_id_out;
5320
5321 /* Fix for Bug#4617352 */
5322 select sum(nvl(units_assigned,0))
5323 into h_cur_units
5324 from fa_distribution_history
5325 where asset_id = h_asset_id
5326 and date_ineffective is null
5327 and transaction_header_id_in = h_id_out;
5328
5329 exception
5330 when no_data_found then
5331 h_cur_units := 0;
5332 end;
5333
5334 if p_log_level_rec.statement_level then
5335 fa_debug_pkg.add
5336 (fname => l_calling_fn,
5337 element => 'h_cur_units before THE partial unit ret condition',
5338 value => h_cur_units
5339 ,p_log_level_rec => p_log_level_rec);
5340 end if;
5341
5342 if (h_cur_units <> 0) then
5343
5344 adj_row.current_units := h_cur_units;
5345 --adj_row.selection_thid := 0;
5346 adj_row.selection_thid := h_id_out;
5347 adj_row.debit_credit_flag := 'CR';
5348 -- Bug 5170275: adj_row.selection_mode := FA_STD_TYPES.FA_AJ_ACTIVE_PARTIAL;
5349 if h_sum_of_part_active_units > 0 then
5350 adj_row.selection_mode := FA_STD_TYPES.FA_AJ_ACTIVE_PARTIAL;
5351 else
5352 adj_row.selection_mode := FA_STD_TYPES.FA_AJ_ACTIVE;
5353 end if;
5354 adj_row.leveling_flag := FALSE;
5355 adj_row.mrc_sob_type_code := ret.mrc_sob_type_code;
5356
5357
5358 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
5359 X_last_update_date,
5360 X_last_updated_by,
5361 X_last_update_login
5362 ,p_log_level_rec => p_log_level_rec)) then
5363
5364 fa_srvr_msg.add_message(calling_fn => l_calling_fn
5365 ,p_log_level_rec => p_log_level_rec);
5366 return(FALSE);
5367
5368 end if;
5369
5370
5371 if (tot_bonus_deprn_reserve <> 0) then
5372
5373 adj_row.current_units := bk.cur_units;
5374 adj_row.account := bonus_deprn_rsv_acct;
5375 adj_row.account_type := 'BONUS_DEPRN_RESERVE_ACCT';
5376 adj_row.adjustment_type := 'BONUS RESERVE';
5377 adj_row.adjustment_amount := 0;
5378 adj_row.amount_inserted := 0;
5379 adj_row.selection_thid := h_id_out;
5380 adj_row.debit_credit_flag := 'DR';
5381 adj_row.selection_mode := FA_STD_TYPES.FA_AJ_CLEAR;
5382 adj_row.mrc_sob_type_code := ret.mrc_sob_type_code;
5383
5384 if p_log_level_rec.statement_level then
5385 fa_debug_pkg.add
5386 (fname => l_calling_fn,
5387 element => 'Insert fa_adjustments in fagprv2,accnt_type',
5388 value => adj_row.account_type
5389 ,p_log_level_rec => p_log_level_rec);
5390 end if;
5391
5392 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
5393 X_last_update_date,
5394 X_last_updated_by,
5395 X_last_update_login
5396 ,p_log_level_rec => p_log_level_rec)) then
5397
5398 fa_srvr_msg.add_message(calling_fn => l_calling_fn
5399 ,p_log_level_rec => p_log_level_rec);
5400 return(FALSE);
5401
5402 end if;
5403
5404 adj_row.adjustment_amount := adj_row.amount_inserted -
5405 tot_bonus_deprn_reserve;
5406
5407 adj_row.current_units := h_cur_units;
5408 adj_row.selection_thid := 0;
5409 adj_row.debit_credit_flag := 'CR';
5410 adj_row.selection_mode := FA_STD_TYPES.FA_AJ_ACTIVE;
5411 adj_row.mrc_sob_type_code := ret.mrc_sob_type_code;
5412
5413 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
5414 X_last_update_date,
5415 X_last_updated_by,
5416 X_last_update_login
5417 ,p_log_level_rec => p_log_level_rec)) then
5418
5419 fa_srvr_msg.add_message(calling_fn => l_calling_fn
5420 ,p_log_level_rec => p_log_level_rec);
5421 return(FALSE);
5422 end if;
5423
5424
5425 end if;
5426
5427
5428 end if; -- if h_cur_units <> 0
5429
5430
5431
5432 if p_log_level_rec.statement_level then
5433 fa_debug_pkg.add
5434 (fname => l_calling_fn,
5435 element => 'Before faxinaj for REVAL RESERVE',
5436 value => ''
5437 ,p_log_level_rec => p_log_level_rec);
5438 end if;
5439
5440 if (bk.current_cost > bk.unrevalued_cost OR reval_reserve <> 0 ) and
5441 (h_retire_reval_flag = 1) then
5442
5443 adj_row.current_units := bk.cur_units;
5444 adj_row.account := reval_rsv_acct;
5445 adj_row.account_type := 'REVAL_RESERVE_ACCT';
5446 adj_row.adjustment_type := 'REVAL RESERVE';
5447 adj_row.adjustment_amount := 0;
5448 adj_row.selection_thid := h_id_out;
5449 adj_row.debit_credit_flag := 'DR';
5450 adj_row.selection_mode := FA_STD_TYPES.FA_AJ_CLEAR;
5451 adj_row.mrc_sob_type_code := ret.mrc_sob_type_code;
5452
5453 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
5454 X_last_update_date,
5455 X_last_updated_by,
5456 X_last_update_login
5457 ,p_log_level_rec => p_log_level_rec)) then
5458
5459 fa_srvr_msg.add_message(calling_fn => l_calling_fn
5460 ,p_log_level_rec => p_log_level_rec);
5461 return(FALSE);
5462 end if;
5463
5464 adj_row.adjustment_amount := adj_row.amount_inserted -
5465 tot_reval_reserve;
5466
5467 -- Bug # 5170275
5468 if (h_cur_units <> 0) then
5469 adj_row.current_units := h_cur_units;
5470 adj_row.selection_thid := 0;
5471 adj_row.debit_credit_flag := 'CR';
5472 adj_row.mrc_sob_type_code := ret.mrc_sob_type_code;
5473
5474 adj_row.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE_REVAL;
5475 -- adj_row.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
5476
5477 if adj_row.adjustment_amount <> 0 then
5478 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
5479 X_last_update_date,
5480 X_last_updated_by,
5481 X_last_update_login
5482 ,p_log_level_rec => p_log_level_rec)) then
5483
5484 fa_srvr_msg.add_message(calling_fn => l_calling_fn
5485 ,p_log_level_rec => p_log_level_rec);
5486 return(FALSE);
5487 end if;
5488 end if;
5489 end if; -- h_cur_units
5490
5491 end if; -- end of - if (bk.current_cost
5492 end if; -- end of - if (ret.units_retired
5493
5494 ret.rsv_retired := tot_deprn_reserve;
5495 ret.reval_rsv_retired := tot_reval_reserve;
5496 ret.bonus_rsv_retired := tot_bonus_deprn_reserve;
5497
5498 if p_log_level_rec.statement_level then
5499 fa_debug_pkg.add
5500 (fname => l_calling_fn,
5501 element => 'Value of reval_rsv_retired is ',
5502 value => to_char(tot_reval_reserve));
5503 end if;
5504
5505 if p_log_level_rec.statement_level then
5506 fa_debug_pkg.add
5507 (fname => l_calling_fn,
5508 element => 'Value of rsv_retired is ',
5509 value => to_char(tot_deprn_reserve));
5510 end if;
5511
5512 if p_log_level_rec.statement_level then
5513 fa_debug_pkg.add
5514 (fname => l_calling_fn,
5515 element => 'Return true from EFA_RUPD.fagprv ',
5516 value => to_char(tot_deprn_reserve));
5517 end if;
5518
5519 -- +++++ Process Create Reserve Retired entry for Group +++++ --
5520 if (bk.group_asset_id is not null) and
5521 (nvl(bk.member_rollup_flag, 'N') = 'N') then
5522
5523 if (l_rsv_retired is not null) then
5524 ret.reserve_retired := l_rsv_retired;
5525 end if;
5526
5527 fa_query_balances_pkg.query_balances(
5528 X_asset_id => bk.group_asset_id,
5529 X_book => ret.book,
5530 X_period_ctr => 0,
5531 X_dist_id => 0,
5532 X_run_mode => 'STANDARD',
5533 X_cost => l_temp_num,
5534 X_deprn_rsv => l_g_rsv,
5535 X_reval_rsv => l_temp_num,
5536 X_ytd_deprn => l_temp_num,
5537 X_ytd_reval_exp => l_temp_num,
5538 X_reval_deprn_exp => l_temp_num,
5539 X_deprn_exp => l_temp_num,
5540 X_reval_amo => l_temp_num,
5541 X_prod => l_temp_num,
5542 X_ytd_prod => l_temp_num,
5543 X_ltd_prod => l_temp_num,
5544 X_adj_cost => l_temp_num,
5545 X_reval_amo_basis => l_temp_num,
5546 X_bonus_rate => l_temp_num,
5547 X_deprn_source_code => l_temp_char,
5548 X_adjusted_flag => l_temp_bool,
5549 X_transaction_header_id => -1,
5550 X_bonus_deprn_rsv => l_g_bonus_rsv,
5551 X_bonus_ytd_deprn => l_temp_num,
5552 X_bonus_deprn_amount => l_temp_num
5553 ,p_log_level_rec => p_log_level_rec);
5554
5555 if (nvl(l_rsv_retired, 0) <> 0) or
5556 (nvl(l_g_rsv, 0) <> 0) or
5557 (nvl(l_g_bonus_rsv, 0) <> 0) then
5558
5559 adj_row.asset_id := bk.group_asset_id;
5560
5561 OPEN c_get_unit;
5562 FETCH c_get_unit INTO adj_row.current_units;
5563 CLOSE c_get_unit;
5564
5565 l_asset_hdr_rec.asset_id := bk.group_asset_id;
5566 l_asset_hdr_rec.book_type_code := ret.book;
5567 l_asset_hdr_rec.set_of_books_id := fa_cache_pkg.fazcbc_record.set_of_books_id;
5568
5569 if not FA_UTIL_PVT.get_asset_cat_rec (
5570 p_asset_hdr_rec => l_asset_hdr_rec,
5571 px_asset_cat_rec => l_asset_cat_rec,
5572 p_date_effective => null
5573 ,p_log_level_rec => p_log_level_rec) then
5574 fa_srvr_msg.add_message(calling_fn => l_calling_fn
5575 ,p_log_level_rec => p_log_level_rec);
5576 return(FALSE);
5577 end if;
5578
5579 if not fa_cache_pkg.fazccb(
5580 X_book => ret.book,
5581 X_cat_id => l_asset_cat_rec.category_id
5582 ,p_log_level_rec => p_log_level_rec) then
5583 fa_srvr_msg.add_message(calling_fn => l_calling_fn
5584 ,p_log_level_rec => p_log_level_rec);
5585 return(FALSE);
5586 end if;
5587
5588 OPEN c_get_cost;
5589 FETCH c_get_cost INTO l_g_cost;
5590 CLOSE c_get_cost;
5591
5592 if (nvl(l_g_rsv, 0) <> 0) then
5593 if (nvl(bk.member_rollup_flag, 'N') <> 'Y') and
5594 (l_rsv_retired is null) then
5595
5596 adj_row.adjustment_amount := (ret.cost_retired / l_g_cost) * l_g_rsv;
5597
5598 if not FA_UTILS_PKG.faxrnd(adj_row.adjustment_amount, ret.book) then
5599 fa_srvr_msg.add_message(calling_fn => l_calling_fn
5600 ,p_log_level_rec => p_log_level_rec);
5601 raise fagprv_err;
5602 end if;
5603
5604 else
5605 adj_row.adjustment_amount := nvl(l_rsv_retired, ret.rsv_retired);
5606
5607 end if;
5608
5609 --Bug7713533: Populate fa_ret_types.ret_struct with reserve(/rsv)_retired
5610 ret.rsv_retired := adj_row.adjustment_amount;
5611 ret.reserve_retired := adj_row.adjustment_amount;
5612 --Bug7713533 ends here
5613
5614 adj_row.account := fa_cache_pkg.fazccb_record.deprn_reserve_acct;
5615 adj_row.account_type := 'DEPRN_RESERVE_ACCT';
5616 adj_row.adjustment_type := 'RESERVE';
5617 adj_row.selection_thid := 0;
5618 adj_row.debit_credit_flag := 'DR';
5619 adj_row.selection_mode := FA_STD_TYPES.FA_AJ_ACTIVE;
5620 adj_row.mrc_sob_type_code := ret.mrc_sob_type_code;
5621 adj_row.track_member_flag := null;
5622
5623 if p_log_level_rec.statement_level then
5624 fa_debug_pkg.add(fname => l_calling_fn,
5625 element => 'Before faxinaj for Group RESERVE',
5626 value => adj_row.adjustment_amount
5627 ,p_log_level_rec => p_log_level_rec);
5628 end if;
5629
5630 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
5631 X_last_update_date,
5632 X_last_updated_by,
5633 X_last_update_login
5634 ,p_log_level_rec => p_log_level_rec)) then
5635
5636 fa_srvr_msg.add_message(calling_fn => l_calling_fn
5637 ,p_log_level_rec => p_log_level_rec);
5638 return(FALSE);
5639
5640 end if;
5641 end if; -- (nvl(l_g_rsv, 0) <> 0)
5642
5643 if (nvl(l_g_bonus_rsv, 0) <> 0) then
5644 if (nvl(bk.member_rollup_flag, 'N') <> 'Y') and
5645 (l_rsv_retired is null) then
5646
5647 adj_row.adjustment_amount := (ret.cost_retired / l_g_cost) * l_g_bonus_rsv;
5648
5649 if not FA_UTILS_PKG.faxrnd(adj_row.adjustment_amount, ret.book) then
5650 fa_srvr_msg.add_message(calling_fn => l_calling_fn
5651 ,p_log_level_rec => p_log_level_rec);
5652 raise fagprv_err;
5653 end if;
5654
5655 elsif (l_rsv_retired is not null) then
5656 adj_row.adjustment_amount := (l_rsv_retired/l_g_rsv) * l_g_bonus_rsv;
5657
5658 if not FA_UTILS_PKG.faxrnd(adj_row.adjustment_amount, ret.book) then
5659 fa_srvr_msg.add_message(calling_fn => l_calling_fn
5660 ,p_log_level_rec => p_log_level_rec);
5661 raise fagprv_err;
5662 end if;
5663
5664 else
5665 adj_row.adjustment_amount := ret.bonus_rsv_retired;
5666 end if;
5667
5668 adj_row.account := fa_cache_pkg.fazccb_record.bonus_deprn_expense_acct;
5669 adj_row.account_type := 'BONUS_DEPRN_RESERVE_ACCT';
5670 adj_row.adjustment_type := 'BONUS RESERVE';
5671 adj_row.selection_thid := 0;
5672 adj_row.debit_credit_flag := 'DR';
5673 adj_row.selection_mode := FA_STD_TYPES.FA_AJ_ACTIVE;
5674 adj_row.mrc_sob_type_code := ret.mrc_sob_type_code;
5675 adj_row.track_member_flag := null;
5676
5677 if p_log_level_rec.statement_level then
5678 fa_debug_pkg.add(fname => l_calling_fn,
5679 element => 'Before faxinaj for Group BONUS RESERVE',
5680 value => adj_row.adjustment_amount
5681 ,p_log_level_rec => p_log_level_rec);
5682 end if;
5683
5684 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
5685 X_last_update_date,
5686 X_last_updated_by,
5687 X_last_update_login
5688 ,p_log_level_rec => p_log_level_rec)) then
5689
5690 fa_srvr_msg.add_message(calling_fn => l_calling_fn
5691 ,p_log_level_rec => p_log_level_rec);
5692 return(FALSE);
5693
5694 end if;
5695 end if; -- (nvl(l_g_bonus_rsv, 0) <> 0)
5696 end if;
5697
5698 end if; -- (bk.group_asset_id is not null)
5699
5700 return(TRUE);
5701
5702 EXCEPTION
5703
5704 when others then
5705
5706 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn
5707 ,p_log_level_rec => p_log_level_rec);
5708 return FALSE;
5709
5710 END FAGPRV;
5711
5712 END FA_GAINLOSS_UPD_PKG; -- End of Package EFA_RUPD