[Home] [Help]
PACKAGE BODY: APPS.FA_GAINLOSS_UND_PKG
Source
1 PACKAGE BODY FA_GAINLOSS_UND_PKG AS
2 /* $Header: fagundb.pls 120.67.12020000.2 2012/07/20 11:52:13 spooyath ship $*/
3
4 g_release number := fa_cache_pkg.fazarel_release;
5
6
7 /* Added types and variables at global level for bug 7396397*/
8
9 cursor c_g_adj is
10 SELECT fadh.distribution_id,
11 fadh.code_combination_id,
12 fadh.location_id,
13 fadh.assigned_to,
14 'N' retire_rec_found,
15 0 cost,
16 0 DEPRN_RSV,
17 0 REVAL_RSV,
18 0 BONUS_DEPRN_RSV,
19 0 IMPAIRMENT_RSV,
20 0 new_units,
21 fadh.code_combination_id adj_ccid
22 FROM fa_distribution_history fadh
23 where 1=0;
24
25 cursor c_g_ret is
26 SELECT fadh.distribution_id,
27 faadj.transaction_header_id,
28 fadh.code_combination_id,
29 fadh.location_id,
30 fadh.assigned_to,
31 faadj.adjustment_type,
32 faadj.debit_credit_flag,
33 faadj.adjustment_amount,
34 'N' adj_rec_found,
35 faadj.code_combination_id adj_ccid
36 FROM fa_adjustments faadj, fa_distribution_history fadh
37 WHERE 1=0;
38
39 cursor c_g_cost_ret IS
40 SELECT faadj.adjustment_type,
41 faadj.adjustment_amount,
42 faadj.debit_credit_flag,
43 faadj.debit_credit_flag rev_debit_credit_flag,
44 faadj.code_combination_id adj_ccid
45 from fa_adjustments faadj
46 where 1=0;
47
48 type tbl_adj is table of c_g_adj%rowtype index by pls_integer;
49 type tbl_ret is table of c_g_ret%rowtype index by pls_integer;
50 type tbl_cost_ret is table of c_g_cost_ret%rowtype index by binary_integer;
51
52 g_tbl_adj_cost tbl_adj;
53 g_tbl_adj_rsv tbl_adj;
54
55 type typ_adj_rec is RECORD
56 (asset_id fa_books.asset_id%type,
57 dist_id fa_adjustments.distribution_id%type,
58 ccid fa_adjustments.code_combination_id%type,
59 adj_type fa_adjustments.adjustment_type%type,
60 dr_cr fa_adjustments.debit_credit_flag%type,
61 cost fa_adjustments.adjustment_amount%type);
62 type tbl_final_adj is table of typ_adj_rec index by binary_integer;
63
64 function process_adj_table(p_mode IN VARCHAR2,
65 RET IN fa_ret_types.ret_struct,
66 BK IN fa_ret_types.book_struct,
67 p_tbl_adj IN OUT NOCOPY tbl_adj,
68 p_tbl_ret IN OUT NOCOPY tbl_ret,
69 p_tbl_cost_ret IN OUT NOCOPY tbl_cost_ret,
70 p_tbl_adj_final IN OUT NOCOPY tbl_final_adj, p_log_level_rec IN FA_API_TYPES.log_level_rec_type) return boolean;
71 /* End of variables at global level for bug 7396397*/
72
73 /*===========================================================================
74 | NAME fagiar
75 |
76 | FUNCTION Adjust the GAIN/LOSS and ITC accounts by the same amount we took
77 | back then.
78 |
79 | History Jacob John 1/29/97 Created
80 |
81 |
82 |
83 |==========================================================================*/
84
85 Function FAGIAR(
86 RET IN OUT NOCOPY fa_ret_types.ret_struct,
87 BK IN OUT NOCOPY fa_ret_types.book_struct,
88 cpd_ctr IN number,
89 user_id IN number,
90 today IN date
91 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) return boolean IS
92
93 adj_row fa_adjust_type_pkg.fa_adj_row_struct;
94 dr_cr varchar2(3);
95 adj_type varchar2(16);
96
97 h_wip_asset integer;
98 h_ret_id number;
99 h_user_id number;
100 h_today date;
101 h_th_id_out number;
102 h_dr_cr varchar2(3);
103 h_adj_type varchar2(16);
104 h_dist_id number;
105 h_ccid number;
106 h_misc_cost number;
107 h_asset_id number;
108 h_track_member_flag varchar2(1); --Bug8244128
109
110 x number;
111
112 X_LAST_UPDATE_DATE date := sysdate;
113 X_last_updated_by number := -1;
114 X_last_update_login number := -1;
115
116 l_group_thid number(15);
117
118 CURSOR c_get_group_thid IS
119 SELECT transaction_header_id
120 FROM fa_transaction_headers
121 WHERE member_transaction_header_id = ret.th_id_in
122 AND asset_id = bk.group_asset_id
123 AND book_type_code = ret.book;
124
125 CURSOR MISC_COST (p_group_thid number,
126 p_group_asset_id number,
127 p_wip_asset number,
128 p_ret_id number) IS
129 SELECT
130 faadj.asset_id,
131 faadj.distribution_id,
132 faadj.code_combination_id,
133 faadj.adjustment_type,
134 DECODE(faadj.debit_credit_flag, 'CR', 'DR', 'CR'),
135 faadj.adjustment_amount,
136 faadj.track_member_flag --Bug8244128
137 FROM
138 fa_adjustments faadj, fa_retirements faret, fa_deprn_periods dp
139 WHERE faadj.transaction_header_id = faret.transaction_header_id_in
140 AND faadj.asset_id = faret.asset_id
141 AND faadj.book_type_Code = faret.book_type_code
142 AND faadj.source_type_code = decode(p_wip_asset, 1, 'CIP RETIREMENT',
143 'RETIREMENT')
144 AND faadj.adjustment_type in ('PROCEEDS', 'REMOVALCOST',
145 'NBV RETIRED', 'REVAL RSV RET',
146 'PROCEEDS CLR',
147 'REMOVALCOST CLR',
148 'CAPITAL ADJ',
149 'GENERAL FUND') -- Added for Bug 6666666
150 AND faret.retirement_id = p_ret_id
151 AND dp.book_type_code = faret.book_type_code
152 AND faret.date_effective between dp.period_open_date and
153 nvl(dp.period_close_date, sysdate)
154 AND faadj.period_counter_created = dp.period_counter
155 UNION
156 SELECT
157 faadj.asset_id,
158 faadj.distribution_id,
159 faadj.code_combination_id,
160 faadj.adjustment_type,
161 DECODE(faadj.debit_credit_flag, 'CR', 'DR', 'CR'),
162 faadj.adjustment_amount,
163 faadj.track_member_flag --Bug8244128
164 FROM
165 fa_adjustments faadj, fa_retirements faret, fa_deprn_periods dp
166 WHERE faadj.transaction_header_id = p_group_thid
167 AND faadj.asset_id = p_group_asset_id
168 AND faadj.book_type_Code = faret.book_type_code
169 AND faadj.source_type_code = decode(p_wip_asset, 1, 'CIP RETIREMENT',
170 'RETIREMENT')
171 AND faadj.adjustment_type in ('PROCEEDS', 'REMOVALCOST',
172 'NBV RETIRED', 'REVAL RSV RET',
173 'PROCEEDS CLR',
174 'REMOVALCOST CLR',
175 'CAPITAL ADJ',
176 'GENERAL FUND') -- Added for Bug 6666666
177 AND faret.retirement_id = p_ret_id
178 AND dp.book_type_code = faret.book_type_code
179 AND faret.date_effective between dp.period_open_date and
180 nvl(dp.period_close_date, sysdate)
181 AND faadj.period_counter_created = dp.period_counter;
182
183 CURSOR MRC_MISC_COST (p_group_thid number,
184 p_group_asset_id number,
185 p_wip_asset number,
186 p_ret_id number) IS
187 SELECT
188 faadj.asset_id,
189 faadj.distribution_id,
190 faadj.code_combination_id,
191 faadj.adjustment_type,
192 DECODE(faadj.debit_credit_flag, 'CR', 'DR', 'CR'),
193 faadj.adjustment_amount,
194 faadj.track_member_flag --Bug8244128
195 FROM
196 fa_mc_adjustments faadj,
197 fa_mc_retirements faret,
198 fa_deprn_periods dp
199 WHERE faadj.transaction_header_id = faret.transaction_header_id_in
200 AND faadj.asset_id = faret.asset_id
201 AND faadj.set_of_books_id = ret.set_of_books_id
202 AND faadj.book_type_Code = faret.book_type_code
203 AND faadj.source_type_code = decode(p_wip_asset, 1, 'CIP RETIREMENT',
204 'RETIREMENT')
205 AND faadj.adjustment_type in ('PROCEEDS', 'REMOVALCOST',
206 'NBV RETIRED', 'REVAL RSV RET',
207 'PROCEEDS CLR',
208 'REMOVALCOST CLR',
209 'CAPITAL ADJ',
210 'GENERAL FUND') -- Added for Bug 6666666
211 AND faret.retirement_id = p_ret_id
212 AND faret.set_of_books_id = ret.set_of_books_id
213 AND dp.book_type_code = faret.book_type_code
214 AND faret.date_effective between dp.period_open_date and
215 nvl(dp.period_close_date, sysdate)
216 AND faadj.period_counter_created = dp.period_counter
217 UNION
218 SELECT
219 faadj.asset_id,
220 faadj.distribution_id,
221 faadj.code_combination_id,
222 faadj.adjustment_type,
223 DECODE(faadj.debit_credit_flag, 'CR', 'DR', 'CR'),
224 faadj.adjustment_amount,
225 faadj.track_member_flag --Bug8244128
226 FROM
227 fa_mc_adjustments faadj,
228 fa_mc_retirements faret,
229 fa_deprn_periods dp
230 WHERE faadj.transaction_header_id = p_group_thid
231 AND faadj.asset_id = p_group_asset_id
232 AND faadj.book_type_Code = faret.book_type_code
233 AND faadj.set_of_books_id = ret.set_of_books_id
234 AND faadj.source_type_code = decode(p_wip_asset, 1, 'CIP RETIREMENT',
235 'RETIREMENT')
236 AND faadj.adjustment_type in ('PROCEEDS', 'REMOVALCOST',
237 'NBV RETIRED', 'REVAL RSV RET',
238 'PROCEEDS CLR',
239 'REMOVALCOST CLR',
240 'CAPITAL ADJ',
241 'GENERAL FUND') -- Added for Bug 6666666
242 AND faret.retirement_id = p_ret_id
243 AND faret.set_of_books_id = ret.set_of_books_id
244 AND dp.book_type_code = faret.book_type_code
245 AND faret.date_effective between dp.period_open_date and
246 nvl(dp.period_close_date, sysdate)
247 AND faadj.period_counter_created = dp.period_counter;
248
249 FAGIAR_ERROR Exception;
250
251 l_calling_fn varchar2(40) := 'FA_GAINLOSS_UND_PKG.fagiar';
252
253 BEGIN <<FAGIAR>>
254
255 h_misc_cost := 0;
256 h_ret_id := ret.retirement_id;
257 h_today := today;
258 h_user_id := user_id;
259 h_wip_asset := ret.wip_asset;
260
261 if (p_log_level_rec.statement_level) then
262 fa_debug_pkg.add
263 (fname => l_calling_fn,
264 element => 'Updating fa_retirements',
265 value => '', p_log_level_rec => p_log_level_rec);
266 end if;
267
268 if (ret.mrc_sob_type_code <> 'R') then
272 last_updated_by = user_id
269 UPDATE FA_RETIREMENTS
270 SET status = 'DELETED',
271 last_update_date = today,
273 WHERE retirement_id = ret.retirement_id;
274 else
275 UPDATE FA_MC_RETIREMENTS
276 SET status = 'DELETED',
277 last_update_date = today,
278 last_updated_by = user_id
279 WHERE retirement_id = ret.retirement_id
280 AND set_of_books_id = ret.set_of_books_id;
281 end if;
282
283 -- Get thid_out from fa_rets
284 -- this can never be different for primary and reporting book
285 select transaction_header_id_out
286 into h_th_id_out
287 from fa_retirements
288 where retirement_id = h_ret_id;
289
290 if ret.wip_asset > 0 then
291 adj_row.source_type_code := 'CIP RETIREMENT';
292 else
293 adj_row.source_type_code := 'RETIREMENT';
294 end if;
295
296 adj_row.transaction_header_id := h_th_id_out;
297 adj_row.asset_invoice_id := 0;
298 adj_row.book_type_code := ret.book;
299 adj_row.period_counter_created := cpd_ctr;
300 adj_row.period_counter_adjusted := cpd_ctr;
301 adj_row.last_update_date := today;
302 adj_row.account := NULL;
303 adj_row.account_type := NULL;
304 adj_row.current_units := bk.cur_units;
305 adj_row.selection_mode := fa_std_types.FA_AJ_SINGLE;
306 adj_row.selection_thid := 0;
307 adj_row.selection_retid := 0;
308 adj_row.flush_adj_flag := TRUE;
309 adj_row.gen_ccid_flag := FALSE;
310 adj_row.annualized_adjustment := 0;
311 adj_row.units_retired := 0;
312 adj_row.leveling_flag := TRUE;
313
314 if (bk.group_asset_id is not null) then
315 OPEN c_get_group_thid;
316 FETCH c_get_group_thid INTO l_group_thid;
317 CLOSE c_get_group_thid;
318 end if;
319
320 -- Get misc cost info
321
322 if (ret.mrc_sob_type_code <> 'R') then
323 OPEN MISC_COST
324 (l_group_thid,
325 bk.group_asset_id,
326 h_wip_asset,
327 h_ret_id);
328 else
329 OPEN MRC_MISC_COST
330 (l_group_thid,
331 bk.group_asset_id,
332 h_wip_asset,
333 h_ret_id);
334 end if;
335
336 LOOP
337 fa_debug_pkg.add
338 (fname => l_calling_fn,
339 element => 'adj_row.adjustment_amount ###',
340 value => 'start of loop', p_log_level_rec => p_log_level_rec);
341 -- Get misc cost info
342
343 if (ret.mrc_sob_type_code <> 'R') then
344 FETCH MISC_COST INTO
345 h_asset_id,
346 h_dist_id,
347 h_ccid,
348 h_adj_type,
349 h_dr_cr,
350 h_misc_cost,
351 h_track_member_flag; --Bug8244128
352 EXIT WHEN MISC_COST%NOTFOUND OR MISC_COST%NOTFOUND IS NULL;
353 else
354 FETCH MRC_MISC_COST INTO
355 h_asset_id,
356 h_dist_id,
357 h_ccid,
358 h_adj_type,
359 h_dr_cr,
360 h_misc_cost,
361 h_track_member_flag; --Bug8244128
362 EXIT WHEN MRC_MISC_COST%NOTFOUND OR MRC_MISC_COST%NOTFOUND IS NULL;
363 end if;
364
365 adj_row.asset_id := h_asset_id;
366 adj_row.code_combination_id := h_ccid;
367 adj_row.adjustment_amount := h_misc_cost;
368 adj_row.distribution_id := h_dist_id;
369 adj_row.debit_credit_flag := h_dr_cr;
370 adj_row.adjustment_type := h_adj_type;
371 adj_row.mrc_sob_type_code := ret.mrc_sob_type_code;
372 adj_row.set_of_books_id := ret.set_of_books_id;
373 adj_row.track_member_flag := h_track_member_flag; --Bug8244128
374
375 fa_debug_pkg.add
376 (fname => l_calling_fn,
377 element => 'adj_row.adjustment_amount ###',
378 value => adj_row.adjustment_amount, p_log_level_rec => p_log_level_rec);
379
380 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
381 X_last_update_date,
382 X_last_updated_by,
383 X_last_update_login
384 , p_log_level_rec => p_log_level_rec)) then
385
386 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
387
388 return(false);
389 end if;
390 fa_debug_pkg.add
391 (fname => l_calling_fn,
392 element => 'adj_row.adjustment_amount ###',
393 value => 'end of loop', p_log_level_rec => p_log_level_rec);
394 END LOOP;
395 fa_debug_pkg.add
396 (fname => l_calling_fn,
397 element => 'adj_row.adjustment_amount ###',
398 value => 'end of call', p_log_level_rec => p_log_level_rec);
399 if (ret.mrc_sob_type_code <> 'R') then
400 CLOSE MISC_COST;
401 else
402 CLOSE MRC_MISC_COST;
403 end if;
404
405 return(true);
406
407 EXCEPTION
408
409 when others then
410 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
411 return FALSE;
412
413 END;
414
415 /*====================================================================
416 * NAME fagtax
420 * we need to insert adjustment rows in associated tax books to
417 *
418 * FUNCTION
419 * For partial unit retirement reinstatements in the corp book,
421 * move balances to distributions which will be created as a
422 * result of the reinstatement.
423 *
424 | History Jacob John 1/29/97 Created
425 *======================================================================*/
426
427
428 Function FAGTAX(
429 RET IN OUT NOCOPY fa_ret_types.ret_struct,
430 BK IN OUT NOCOPY fa_ret_types.book_struct,
431 today IN date
432 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) return boolean IS
433
434 adj_row fa_adjust_type_pkg.fa_adj_row_struct;
435 dpr_row fa_std_types.fa_deprn_row_struct;
436 dpr_dtl fa_std_types.dpr_dtl_row_struct;
437
438 h_book varchar2(30);
439 h_retirement_id number;
440 h_cpd_num Number;
441 h_trans_header_id_in Number;
442 h_trans_header_id_out Number;
443 h_category_id Number;
444 h_asset_id number;
445
446 --variables added for fix to bug 688397
447 h_period_ctr number;
448 h_cost number;
449 h_ytd number;
450 h_deprn_reserve number;
451 h_reval_reserve number;
452 h_ind_rr number;
453 h_ytd_reval_dep_exp number;
454 h_ind_yrde number;
455 h_is_prior_period number;
456 --added the following 2 variables for bug no.3831503
457 h_mrc_sob_type_code varchar2(1);
458 h_set_of_books_id number(15);
459
460 fagtax_error Exception;
461 h_success boolean := TRUE;
462
463 X_LAST_UPDATE_DATE date := sysdate;
464 X_last_updated_by number := -1;
465 X_last_update_login number := -1;
466
467 --modifying the cursors for bug no. 3831503 and using a single cursor for both mrc and primary book
468 CURSOR TAX_BOOKS_DR IS
469 SELECT
470 'P',bc.set_of_books_id,
471 bc.book_type_code,
472 retire.transaction_header_id_out,
473 retire.transaction_header_id_in,
474 bk.group_asset_id,
475 bk.member_rollup_flag,
476 bk.tracking_method
477 FROM fa_book_controls bc, fa_retirements retire, fa_books bk
478 WHERE
479 retire.retirement_id = RET.retirement_id
480 AND retire.units is not null
481 AND bc.distribution_source_book = retire.book_type_code
482 AND bc.book_class = 'TAX'
483 AND bc.date_ineffective is null
484 AND bk.book_type_code = bc.book_type_code
485 AND bk.asset_id = RET.asset_id
486 AND bk.date_ineffective is null
487 UNION ALL
488 SELECT
489 'R',fmcbc.set_of_books_id,
490 fmcbc.book_type_code,
491 retire.transaction_header_id_out,
492 retire.transaction_header_id_in,
493 bk.group_asset_id,
494 bk.member_rollup_flag,
495 bk.tracking_method
496 FROM fa_mc_book_controls fmcbc,fa_book_controls fbc, fa_retirements retire, fa_mc_books bk
497 WHERE
498 retire.retirement_id = RET.retirement_id
499 AND retire.units is not null
500 AND fbc.distribution_source_book = retire.book_type_code
501 AND fbc.book_type_code=fmcbc.book_type_code
502 AND fmcbc.enabled_flag = 'Y'
503 AND fbc.set_of_books_id=fmcbc.primary_set_of_books_id
504 AND fbc.book_class = 'TAX'
505 AND fbc.date_ineffective is null
506 AND bk.book_type_code = fmcbc.book_type_code
507 AND bk.asset_id = RET.asset_id
508 AND bk.date_ineffective is null
509 ORDER BY 3,1;
510
511 l_calling_fn varchar2(40) := 'FA_GAINLOSS_UND_PKG.fagtax';
512
513 h_group_asset_id NUMBER(15);
514 l_status BOOLEAN;
515 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
516 l_asset_cat_rec FA_API_TYPES.asset_cat_rec_type;
517 l_temp_amount number;
518
519 l_trans_rec FA_API_TYPES.trans_rec_type;
520 l_asset_type_rec FA_API_TYPES.asset_type_rec_type;
521 l_period_rec FA_API_TYPES.period_rec_type;
522
523 h_rollup_flag VARCHAR2(1) := NULL;
524 h_tracking_method VARCHAR2(30) := NULL;
525
526 l_orig_book varchar2(30);
527 l_orig_cat_book varchar2(30);
528 --Secondary Changes
529 l_secondary_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
530 l_secondary_trans_rec FA_API_TYPES.trans_rec_type;
531 l_secondary_sob_id number;
532
533 BEGIN <<FAGTAX>>
534
535 -- We need to move the cost, reserve and reval reserve to the
536 -- new distribution_id's for all of the tax books.
537
538 h_asset_id := ret.asset_id;
539 h_retirement_id := ret.retirement_id;
540
541 adj_row.asset_invoice_id := 0;
542
543 l_orig_book := fa_cache_pkg.fazcbc_record.book_type_code;
544 l_orig_cat_book := fa_cache_pkg.fazccb_record.book_type_code;
545
546 --bug 6129798
547 if RET.wip_asset > 0 then
548 adj_row.source_type_code := 'CIP RETIREMENT';
549 else
550 adj_row.source_type_code := 'RETIREMENT';
551 end if;
552
553 adj_row.asset_id := RET.asset_id;
554 adj_row.last_update_date := today;
555 adj_row.current_units := bk.cur_units;
556 adj_row.selection_retid := 0;
557 adj_row.flush_adj_flag := TRUE;
558 adj_row.annualized_adjustment := 0;
559 adj_row.units_retired := 0;
560 adj_row.leveling_flag := TRUE;
561
562 if (p_log_level_rec.statement_level) then
563 fa_debug_pkg.add
564 (fname => l_calling_fn,
565 element => '+++ Step 1',
566 value => '', p_log_level_rec => p_log_level_rec);
567 end if;
568
569 if (p_log_level_rec.statement_level) then
570 fa_debug_pkg.add
571 (fname => l_calling_fn,
572 element => 'Get category from fa_asset_history',
573 value => '', p_log_level_rec => p_log_level_rec);
574 end if;
575
576 if (p_log_level_rec.statement_level) then
577 fa_debug_pkg.add
578 (fname => l_calling_fn,
579 element => 'asset_id',
580 value => RET.asset_id, p_log_level_rec => p_log_level_rec);
581 end if;
582
583 SELECT category_id
584 INTO h_category_id
585 FROM fa_asset_history
586 WHERE asset_id = RET.asset_id
587 AND date_ineffective is null;
588
589 if (p_log_level_rec.statement_level) then
590 fa_debug_pkg.add
591 (fname => l_calling_fn,
592 element => '+++ Step 2',
593 value => '', p_log_level_rec => p_log_level_rec);
594 end if;
595
596 OPEN TAX_BOOKS_DR;
597
598 LOOP
599
600 if (p_log_level_rec.statement_level) then
601 fa_debug_pkg.add
602 (fname => l_calling_fn,
603 element => 'Fetch Tax Books_DR',
604 value => '', p_log_level_rec => p_log_level_rec);
605 end if;
606
607 FETCH TAX_BOOKS_DR INTO
608 h_mrc_sob_type_code,
609 h_set_of_books_id,
610 h_book,
611 h_trans_header_id_out,
612 h_trans_header_id_in,
613 h_group_asset_id,
614 h_rollup_flag,
615 h_tracking_method;
616 EXIT WHEN TAX_BOOKS_DR%NOTFOUND OR TAX_BOOKS_DR%NOTFOUND IS NULL;
617
618 /* Fix for Bug#2821938 */
619 if not fa_cache_pkg.fazcbc (x_book => h_book, p_log_level_rec => p_log_level_rec) then
620 RAISE fagtax_error;
621 end if;
622
623
624 -- fix for bug 688397: check if asset is added in a prior period
625 -- in the tax book. If it is a current period period add call
626 -- fadpdtl to update 'B' row with new distribution and not create
627 -- fa_adjustments rows. Insert into fa_adjustments only if asset is
628 -- not a current period add in tax book. snarayan Jul 4 1998
629
630 -- count will be 0 if it is a current period add
631
632 SELECT count(*)
633 INTO
634 h_is_prior_period
635 FROM
636 FA_TRANSACTION_HEADERS TH,
637 FA_BOOK_CONTROLS BC,
638 FA_DEPRN_PERIODS DP,
639 FA_DEPRN_PERIODS DP_NOW
640 WHERE
641 TH.ASSET_ID = h_asset_id AND
642 -- TH.TRANSACTION_TYPE_CODE = 'ADDITION' AND --bug 6129798
643 TH.TRANSACTION_TYPE_CODE in ('ADDITION', 'CIP ADDITION') AND --bug 6129798
644 TH.BOOK_TYPE_CODE = BC.BOOK_TYPE_CODE AND
645 BC.BOOK_TYPE_CODE = h_book AND
646 TH.DATE_EFFECTIVE BETWEEN
647 DP.PERIOD_OPEN_DATE AND
648 NVL(DP.PERIOD_CLOSE_DATE, SYSDATE)
649 AND
650 DP.BOOK_TYPE_CODE = TH.BOOK_TYPE_CODE AND
651 DP.PERIOD_COUNTER < DP_NOW.PERIOD_COUNTER AND
652 DP_NOW.BOOK_TYPE_CODE = TH.BOOK_TYPE_CODE AND
653 DP_NOW.PERIOD_CLOSE_DATE IS NULL;
654
655
656 if (h_is_prior_period = 0 and
657 G_release = 11) then --bug 6129798
658 -- current period add in tax book
659 -- modified the last parameter passed for bug no. 3831503
660 l_status := FA_INS_DETAIL_PKG.FAXINDD(
661 X_book_type_code => h_book,
662 X_asset_id => h_asset_id,
663 X_set_of_books_id => ret.set_of_books_id,
664 X_mrc_sob_type_code => h_mrc_sob_type_code, p_log_level_rec => p_log_level_rec);
665
666 if not (l_status) then
667 raise fagtax_error;
668 end if;
669
670 if (h_group_asset_id is not null) then
671
672 l_status := FA_INS_DETAIL_PKG.FAXINDD(
676 X_mrc_sob_type_code => h_mrc_sob_type_code, p_log_level_rec => p_log_level_rec);
673 X_book_type_code => h_book,
674 X_asset_id => h_group_asset_id,
675 X_set_of_books_id => ret.set_of_books_id,
677
678 if not (l_status) then
679 raise fagtax_error;
680 end if;
681
682 end if; -- (h_group_asset_id is not null)
683
684 else -- added in prior period,insert into fa_adjustments
685
686 -- SLA UPTAKE
687 -- assign an event for the transaction
688 if (h_mrc_sob_type_code = 'P') then
689
690 if (NOT fa_trx_approval_pkg.faxcat
691 (X_book => h_book,
692 X_asset_id => ret.asset_id,
693 X_trx_type => 'REINSTATEMENT',
694 X_trx_date => greatest(l_period_rec.calendar_period_open_date,
695 least(sysdate,l_period_rec.calendar_period_close_date)),
696 X_init_message_flag => 'NO',
697 p_log_level_rec => p_log_level_rec)) then
698 raise fagtax_error;
699 end if;
700
701 if not FA_UTIL_PVT.get_period_rec
702 (p_book => h_book,
703 x_period_rec => l_period_rec,
704 p_log_level_rec => p_log_level_rec) then
705 raise fagtax_error;
706 end if;
707
708 l_asset_hdr_rec.asset_id := ret.asset_id;
709 l_asset_hdr_rec.book_type_code := h_book;
710 l_asset_hdr_rec.set_of_books_id := h_set_of_books_id;--changed for bug:13878542 from ret.set_of_books_id
711 l_trans_rec.transaction_type_code := 'TRANSFER';
712 l_trans_rec.transaction_header_id := h_trans_header_id_out;
713 l_trans_rec.calling_interface := 'FARET';
714 l_trans_rec.mass_reference_id := FND_GLOBAL.CONC_REQUEST_ID;
715 l_trans_rec.transaction_date_entered :=
716 greatest(l_period_rec.calendar_period_open_date,
717 least(sysdate,l_period_rec.calendar_period_close_date));
718 l_trans_rec.transaction_date_entered :=
719 to_date(to_char(l_trans_rec.transaction_date_entered,'DD/MM/YYYY'),'DD/MM/YYYY');
720 -- populate the asset type for the asset
721 if not FA_UTIL_PVT.get_asset_type_rec(l_asset_hdr_rec,
722 l_asset_type_rec, null,
723 p_log_level_rec) then
724 raise fagtax_error;
725 end if;
726
727 if not FA_XLA_EVENTS_PVT.create_transaction_event
728 (p_asset_hdr_rec => l_asset_hdr_rec,
729 p_asset_type_rec => l_asset_type_rec,
730 px_trans_rec => l_trans_rec,
731 p_event_status => NULL,
732 p_calling_fn => l_calling_fn,
733 p_log_level_rec => p_log_level_rec
734 ) then
735 raise fagtax_error;
736 end if;
737
738 end if;
739 /*=================================================================
740 Secondary Changes Start
741 If primary and secondary sob_id is different then we need to
742 create event for secondary ledger*/
743 l_secondary_sob_id := FA_XLA_EVENTS_PVT.get_secondary_sob_id(l_asset_hdr_rec.book_type_code);
744 if ( (h_mrc_sob_type_code = 'R') and (h_set_of_books_id <> fa_cache_pkg.fazcbc_record.set_of_books_id) and (l_secondary_sob_id = h_set_of_books_id)) then
745 l_secondary_asset_hdr_rec := l_asset_hdr_rec;
746 l_secondary_trans_rec := l_trans_rec;
747
748 if not FA_UTIL_PVT.get_period_rec
749 (p_book => h_book,
750 x_period_rec => l_period_rec,
751 p_log_level_rec => p_log_level_rec) then
752 raise fagtax_error;
753 end if;
754
755 l_secondary_asset_hdr_rec.asset_id := ret.asset_id;
756 l_secondary_asset_hdr_rec.book_type_code := h_book;
757 l_secondary_asset_hdr_rec.set_of_books_id := h_set_of_books_id;
758 l_secondary_trans_rec.transaction_type_code := 'TRANSFER';
759 l_secondary_trans_rec.transaction_header_id := h_trans_header_id_out;
760 l_secondary_trans_rec.calling_interface := 'FARET';
761 l_secondary_trans_rec.mass_reference_id := FND_GLOBAL.CONC_REQUEST_ID;
762 l_secondary_trans_rec.transaction_date_entered :=
763 greatest(l_period_rec.calendar_period_open_date,
764 least(sysdate,l_period_rec.calendar_period_close_date));
765 l_secondary_trans_rec.transaction_date_entered :=
766 to_date(to_char(l_trans_rec.transaction_date_entered,'DD/MM/YYYY'),'DD/MM/YYYY');
767 -- populate the asset type for the asset
768 if not FA_UTIL_PVT.get_asset_type_rec(l_secondary_asset_hdr_rec,
769 l_asset_type_rec, null,
770 p_log_level_rec) then
771 raise fagtax_error;
772 end if;
773
774 if not FA_XLA_EVENTS_PVT.create_transaction_event
775 (p_asset_hdr_rec => l_secondary_asset_hdr_rec,
776 p_asset_type_rec => l_asset_type_rec,
777 px_trans_rec => l_secondary_trans_rec,
778 p_event_status => NULL,
779 p_calling_fn => l_calling_fn,
780 p_log_level_rec => p_log_level_rec
781 ) then
782 raise fagtax_error;
783 end if;
784 end if;
785 /*Secondary Changes End
786 ==================================================================*/
787
788 SELECT period_counter
789 into h_cpd_num
790 from
791 fa_deprn_periods
792 where book_type_code = h_book
793 and period_close_date is null;
794
795 -- We need to move the cost, reserve and reval reserve to the
796 -- new distribution_id's for all of the tax books.
797
798 adj_row.transaction_header_id := h_trans_header_id_out;
799 adj_row.period_counter_created := h_cpd_num;
800 adj_row.period_counter_adjusted := h_cpd_num;
801 adj_row.selection_thid := h_trans_header_id_out;
802 adj_row.distribution_id := 0;
803 adj_row.gen_ccid_flag := TRUE;
804 adj_row.code_combination_id := 0;
805
806 adj_row.book_type_code := h_book;
807 adj_row.mrc_sob_type_code := h_mrc_sob_type_code; /* Moved in earlar part of code to avoid duplicat code */
808 adj_row.set_of_books_id := ret.set_of_books_id;
809
810 -- Get the various Accts from FA_CATEGORY_BOOKS
811
812 if not fa_cache_pkg.fazccb (h_book, h_category_id, p_log_level_rec => p_log_level_rec) then
813 RAISE fagtax_error;
814 end if;
815
816
817 if RET.wip_asset > 0 then
818 if not fa_cache_pkg.fazccb (h_book, h_category_id, p_log_level_rec => p_log_level_rec) then
819 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
820 RAISE fagtax_error;
821 end if;
822 end if;
823
824
825 if h_group_asset_id is null or
826 h_tracking_method = 'CALCULATE' then
827 -- if (nvl(bk.tracking_method, 'CALCULATE') <> 'ALLOCATE') then
828 -- +++++ Clear out the reserve from the old dist_id's +++++
829 adj_row.adjustment_type := 'RESERVE';
830 adj_row.debit_credit_flag := 'DR';
831 adj_row.account_type := 'DEPRN_RESERVE_ACCT';
832 adj_row.account :=
833 fa_cache_pkg.fazccb_record.DEPRN_RESERVE_ACCT;
834 adj_row.selection_mode := fa_std_types.FA_AJ_CLEAR;
835 adj_row.adjustment_amount := 0;
836 adj_row.source_dest_code := 'SOURCE';
837
838 if (h_group_asset_id is not null) and
839 (nvl(h_rollup_flag, 'N') = 'N') then
840 adj_row.track_member_flag := 'Y';
841 else
842 adj_row.track_member_flag := NULL;
843 end if;
844
845 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
846 X_last_update_date,
847 X_last_updated_by,
848 X_last_update_login, p_log_level_rec => p_log_level_rec)) then
849
850 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
851 return(false);
852
853 end if;
854
855 -- +++++ Credit reserve in new dist_id's +++++
856 adj_row.debit_credit_flag := 'CR';
857 adj_row.selection_mode := fa_std_types.FA_AJ_ACTIVE;
858 adj_row.adjustment_amount := adj_row.amount_inserted;
859 l_temp_amount := adj_row.amount_inserted;
860 adj_row.source_dest_code := 'DEST';
861
862 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
863 X_last_update_date,
864 X_last_updated_by,
865 X_last_update_login
866 , p_log_level_rec => p_log_level_rec)) then
867
868 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
869 return(false);
870 end if;
871
872 end if; -- (nvl(bk.tracking_method, 'CALCULATE') <> 'ALLOCATE')
873
874 if (h_group_asset_id is not null) and
875 (nvl(h_rollup_flag, 'N') = 'N') then
876 -- +++++ Clear out the reserve from the old dist_id's +++++
877 adj_row.adjustment_type := 'RESERVE';
878 adj_row.debit_credit_flag := 'DR';
879 adj_row.account_type := 'DEPRN_RESERVE_ACCT';
880 adj_row.asset_id := h_group_asset_id;
881
882 l_asset_hdr_rec.asset_id := h_group_asset_id;
883 l_asset_hdr_rec.book_type_code := ret.book;
884 l_asset_hdr_rec.set_of_books_id := fa_cache_pkg.fazcbc_record.set_of_books_id;
885
886 if not FA_UTIL_PVT.get_asset_cat_rec (
887 p_asset_hdr_rec => l_asset_hdr_rec,
888 px_asset_cat_rec => l_asset_cat_rec,
889 p_date_effective => null, p_log_level_rec => p_log_level_rec) then
890 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
891 return(FALSE);
892 end if;
893
894 if not fa_cache_pkg.fazccb(
895 X_book => ret.book,
896 X_cat_id => l_asset_cat_rec.category_id, p_log_level_rec => p_log_level_rec) then
897 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
898 return(FALSE);
899 end if;
900
901 adj_row.account :=
902 fa_cache_pkg.fazccb_record.DEPRN_RESERVE_ACCT;
903 adj_row.selection_mode := fa_std_types.FA_AJ_CLEAR;
904 adj_row.adjustment_amount := 0;
905 adj_row.track_member_flag := NULL;
906 adj_row.source_dest_code := 'SOURCE';
907
908 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
909 X_last_update_date,
910 X_last_updated_by,
911 X_last_update_login, p_log_level_rec => p_log_level_rec)) then
912
913 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
914 return(false);
915
916 end if;
917
918 -- +++++ Credit reserve in new dist_id's +++++
919 adj_row.debit_credit_flag := 'CR';
920 adj_row.selection_mode := fa_std_types.FA_AJ_ACTIVE;
921
922 if (h_tracking_method = 'CALCULATE') then
923 adj_row.adjustment_amount := l_temp_amount;
924 else
925 adj_row.adjustment_amount := adj_row.amount_inserted;
926 end if;
927
928 adj_row.source_dest_code := 'DEST';
929
930 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
931 X_last_update_date,
932 X_last_updated_by,
933 X_last_update_login
934 , p_log_level_rec => p_log_level_rec)) then
935
936 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
937 return(false);
938 end if;
939
940 adj_row.asset_id := ret.asset_id;
941
942 if not fa_cache_pkg.fazccb (h_book, h_category_id, p_log_level_rec => p_log_level_rec) then
943 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
944 RAISE fagtax_error;
945 end if;
946
947 end if; -- (bk.group_asset_id is not null)
948
949 if bk.group_asset_id is null or
950 bk.tracking_method = 'CALCULATE' then
951 -- if (nvl(bk.tracking_method, 'CALCULATE') <> 'ALLOCATE') then
952 -- Find reval reserve to determine whether we need
953 -- to move that to the new distributions
954 dpr_row.asset_id := RET.asset_id;
955 dpr_row.book := h_book;
956 dpr_row.period_ctr := 0;
957 dpr_row.dist_id := 0;
958 dpr_row.mrc_sob_type_code := ret.mrc_sob_type_code;
959 dpr_row.set_of_books_id := ret.set_of_books_id;
960
961 FA_QUERY_BALANCES_PKG.query_balances_int (
962 X_DPR_ROW => dpr_row,
963 X_RUN_MODE => 'STANDARD',
964 X_DEBUG => FALSE,
965 X_SUCCESS => H_SUCCESS,
966 X_CALLING_FN => l_calling_fn,
967 X_TRANSACTION_HEADER_ID => -1,
968 p_log_level_rec => p_log_level_rec);
969
970 if not h_success then
971 raise fagtax_error;
972 end if;
973
974 if dpr_row.bonus_deprn_rsv <> 0 then
975
976 --Clear out the bonus reserve from the old dist_id's
977
978 adj_row.adjustment_type := 'BONUS RESERVE';
979 adj_row.debit_credit_flag := 'DR';
980 adj_row.account_type := 'BONUS_DEPRN_RESERVE_ACCT';
981 adj_row.account :=
982 fa_cache_pkg.fazccb_record.BONUS_DEPRN_RESERVE_ACCT;
983 adj_row.selection_mode := fa_std_types.FA_AJ_CLEAR;
984 adj_row.adjustment_amount := 0;
985 adj_row.source_dest_code := 'SOURCE';
986
987 if (bk.group_asset_id is not null) and
988 (nvl(bk.member_rollup_flag, 'N') = 'N') then
989 adj_row.track_member_flag := 'Y';
990 else
991 adj_row.track_member_flag := NULL;
992 end if;
993
994 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
995 X_last_update_date,
996 X_last_updated_by,
997 X_last_update_login, p_log_level_rec => p_log_level_rec)) then
998 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
999 return(false);
1000 end if;
1001
1002 -- Credit bonus reserve to new dist_id's
1003
1004 adj_row.debit_credit_flag := 'CR';
1005 adj_row.selection_mode := fa_std_types.FA_AJ_ACTIVE;
1006 adj_row.adjustment_amount := adj_row.amount_inserted;
1007 adj_row.source_dest_code := 'DEST';
1008
1009 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
1010 X_last_update_date,
1011 X_last_updated_by,
1012 X_last_update_login, p_log_level_rec => p_log_level_rec)) then
1013 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
1014 return(False);
1015 end if;
1016
1017 end if;
1018
1019 if dpr_row.impairment_rsv <> 0 then
1020
1021 --Clear out the impairment reserve from the old dist_id's
1022
1023 adj_row.adjustment_type := 'IMPAIR RESERVE';
1024 adj_row.debit_credit_flag := 'DR';
1025 adj_row.account_type := 'IMPAIR_RESERVE_ACCT';
1026 adj_row.account :=
1027 fa_cache_pkg.fazccb_record.IMPAIR_RESERVE_ACCT;
1028 adj_row.selection_mode := fa_std_types.FA_AJ_CLEAR;
1029 adj_row.adjustment_amount := 0;
1030 adj_row.source_dest_code := 'SOURCE';
1031
1032 if (bk.group_asset_id is not null) and
1033 (nvl(bk.member_rollup_flag, 'N') = 'N') then
1034 adj_row.track_member_flag := 'Y';
1035 else
1036 adj_row.track_member_flag := NULL;
1037 end if;
1038
1039 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
1040 X_last_update_date,
1041 X_last_updated_by,
1042 X_last_update_login, p_log_level_rec => p_log_level_rec)) then
1043 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
1044 return(false);
1045 end if;
1046
1047 -- Credit impairment reserve to new dist_id's
1048
1049 adj_row.debit_credit_flag := 'CR';
1050 adj_row.selection_mode := fa_std_types.FA_AJ_ACTIVE;
1051 adj_row.adjustment_amount := adj_row.amount_inserted;
1052 adj_row.source_dest_code := 'DEST';
1053
1054 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
1055 X_last_update_date,
1056 X_last_updated_by,
1057 X_last_update_login, p_log_level_rec => p_log_level_rec)) then
1058 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
1059 return(False);
1060 end if;
1061 end if;
1062
1063 if dpr_row.reval_rsv <> 0 then
1064
1065 --Clear out the reval reserve from the old dist_id's
1066
1067 adj_row.adjustment_type := 'REVAL RESERVE';
1068 adj_row.debit_credit_flag := 'DR';
1069 adj_row.account_type := 'REVAL_RESERVE_ACCT';
1070 adj_row.account :=
1071 fa_cache_pkg.fazccb_record.REVAL_RESERVE_ACCT;
1072 adj_row.selection_mode := fa_std_types.FA_AJ_CLEAR;
1073 adj_row.adjustment_amount := 0;
1074 adj_row.source_dest_code := 'SOURCE';
1075
1076 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
1077 X_last_update_date,
1078 X_last_updated_by,
1079 X_last_update_login, p_log_level_rec => p_log_level_rec)) then
1080 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
1081 return(false);
1082 end if;
1083
1084
1085 -- Credit reval reserve to new dist_id's
1086
1087 adj_row.debit_credit_flag := 'CR';
1088 adj_row.selection_mode := fa_std_types.FA_AJ_ACTIVE;
1089 adj_row.adjustment_amount := adj_row.amount_inserted;
1090 adj_row.source_dest_code := 'DEST';
1091
1092 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
1093 X_last_update_date,
1094 X_last_updated_by,
1095 X_last_update_login, p_log_level_rec => p_log_level_rec)) then
1096 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
1097 return(False);
1098 end if;
1099
1100
1101 end if;
1102 end if; -- (nvl(bk.tracking_method, 'CALCULATE') <> 'ALLOCATE')
1103
1104 if (bk.group_asset_id is not null) and
1105 (nvl(bk.member_rollup_flag, 'N') = 'N') then
1106
1107 dpr_row.asset_id := bk.group_asset_id;
1108 dpr_row.book := h_book;
1109 dpr_row.period_ctr := 0;
1110 dpr_row.dist_id := 0;
1111 dpr_row.mrc_sob_type_code := ret.mrc_sob_type_code;
1112 dpr_row.set_of_books_id := ret.set_of_books_id;
1113
1114 FA_QUERY_BALANCES_PKG.query_balances_int (
1115 X_DPR_ROW => dpr_row,
1116 X_RUN_MODE => 'STANDARD',
1117 X_DEBUG => FALSE,
1118 X_SUCCESS => H_SUCCESS,
1119 X_CALLING_FN => l_calling_fn,
1120 X_TRANSACTION_HEADER_ID => -1, p_log_level_rec => p_log_level_rec);
1121
1122 if not h_success then
1123 raise fagtax_error;
1124 end if;
1125
1126 if dpr_row.bonus_deprn_rsv <> 0 then
1127
1128 --Clear out the bonus reserve from the old dist_id's
1129
1130 adj_row.asset_id := bk.group_asset_id;
1131 adj_row.adjustment_type := 'BONUS RESERVE';
1132 adj_row.debit_credit_flag := 'DR';
1133 adj_row.account_type := 'BONUS_DEPRN_RESERVE_ACCT';
1134 adj_row.track_member_flag := NULL;
1135
1136 l_asset_hdr_rec.asset_id := bk.group_asset_id;
1137 l_asset_hdr_rec.book_type_code := ret.book;
1138 l_asset_hdr_rec.set_of_books_id := fa_cache_pkg.fazcbc_record.set_of_books_id;
1139
1140 if not FA_UTIL_PVT.get_asset_cat_rec (
1141 p_asset_hdr_rec => l_asset_hdr_rec,
1142 px_asset_cat_rec => l_asset_cat_rec,
1143 p_date_effective => null, p_log_level_rec => p_log_level_rec) then
1144 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
1145 return(FALSE);
1146 end if;
1147
1148 if not fa_cache_pkg.fazccb(
1149 X_book => ret.book,
1150 X_cat_id => l_asset_cat_rec.category_id, p_log_level_rec => p_log_level_rec) then
1151 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
1152 return(FALSE);
1153 end if;
1154
1155 adj_row.account :=
1156 fa_cache_pkg.fazccb_record.BONUS_DEPRN_RESERVE_ACCT;
1157 adj_row.selection_mode := fa_std_types.FA_AJ_CLEAR;
1158 adj_row.adjustment_amount := 0;
1159 adj_row.source_dest_code := 'SOURCE';
1160
1161 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
1162 X_last_update_date,
1163 X_last_updated_by,
1164 X_last_update_login
1165 , p_log_level_rec => p_log_level_rec)) then
1166
1167 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
1168
1169 return(false);
1170 end if;
1171
1172 -- Credit bonus reserve to new dist_id's
1173
1174 adj_row.debit_credit_flag := 'CR';
1175 adj_row.selection_mode := fa_std_types.FA_AJ_ACTIVE;
1176 adj_row.adjustment_amount := adj_row.amount_inserted;
1177 adj_row.source_dest_code := 'DEST';
1178
1179 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
1180 X_last_update_date,
1181 X_last_updated_by,
1182 X_last_update_login
1183 , p_log_level_rec => p_log_level_rec)) then
1184
1185 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
1186
1187 return(False);
1188
1189 end if;
1190
1191 adj_row.asset_id := ret.asset_id;
1192
1193 if not fa_cache_pkg.fazccb (h_book, h_category_id, p_log_level_rec => p_log_level_rec) then
1194 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
1195 RAISE fagtax_error;
1196 end if;
1197
1198 end if; -- dpr_row.bonus_deprn_rsv <> 0
1199
1200 if dpr_row.impairment_rsv <> 0 then
1201
1202 --Clear out the impairment reserve from the old dist_id's
1203
1204 adj_row.asset_id := bk.group_asset_id;
1205 adj_row.adjustment_type := 'IMPAIR RESERVE';
1206 adj_row.debit_credit_flag := 'DR';
1207 adj_row.account_type := 'IMPAIR_RESERVE_ACCT';
1208 adj_row.track_member_flag := NULL;
1209
1210 l_asset_hdr_rec.asset_id := bk.group_asset_id;
1211 l_asset_hdr_rec.book_type_code := ret.book;
1212 l_asset_hdr_rec.set_of_books_id := fa_cache_pkg.fazcbc_record.set_of_books_id;
1213
1214 if not FA_UTIL_PVT.get_asset_cat_rec (
1215 p_asset_hdr_rec => l_asset_hdr_rec,
1216 px_asset_cat_rec => l_asset_cat_rec,
1217 p_date_effective => null, p_log_level_rec => p_log_level_rec) then
1218 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
1219 return(FALSE);
1220 end if;
1221
1222 if not fa_cache_pkg.fazccb(
1223 X_book => ret.book,
1224 X_cat_id => l_asset_cat_rec.category_id, p_log_level_rec => p_log_level_rec) then
1225 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
1226 return(FALSE);
1227 end if;
1228
1229 adj_row.account :=
1230 fa_cache_pkg.fazccb_record.IMPAIR_RESERVE_ACCT;
1231 adj_row.selection_mode := fa_std_types.FA_AJ_CLEAR;
1232 adj_row.adjustment_amount := 0;
1233 adj_row.source_dest_code := 'SOURCE';
1234
1235 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
1236 X_last_update_date,
1237 X_last_updated_by,
1238 X_last_update_login
1239 , p_log_level_rec => p_log_level_rec)) then
1240
1241 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
1242
1243 return(false);
1244 end if;
1245
1246 -- Credit impairment reserve to new dist_id's
1247
1248 adj_row.debit_credit_flag := 'CR';
1249 adj_row.selection_mode := fa_std_types.FA_AJ_ACTIVE;
1250 adj_row.adjustment_amount := adj_row.amount_inserted;
1251 adj_row.source_dest_code := 'DEST';
1252
1253 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
1254 X_last_update_date,
1255 X_last_updated_by,
1256 X_last_update_login
1257 , p_log_level_rec => p_log_level_rec)) then
1258
1259 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
1260
1261 return(False);
1262
1263 end if;
1264
1265 adj_row.asset_id := ret.asset_id;
1266
1267 if not fa_cache_pkg.fazccb (h_book, h_category_id, p_log_level_rec => p_log_level_rec) then
1268 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
1269 RAISE fagtax_error;
1270 end if;
1271
1272 end if;
1273
1274 end if; -- (bk.group_asset_id is null)
1275
1276 -- Clear out the cost from the old dist_id's
1277
1278 adj_row.adjustment_type := 'COST';
1279 adj_row.debit_credit_flag := 'CR';
1280 adj_row.selection_mode := fa_std_types.FA_AJ_CLEAR;
1281 adj_row.adjustment_amount := 0;
1282 adj_row.source_dest_code := 'SOURCE';
1283
1284 if (RET.wip_asset is null or ret.wip_asset <= 0) then
1285 adj_row.account_type := 'ASSET_COST_ACCT';
1286 adj_row.account :=
1287 fa_cache_pkg.fazccb_record.ASSET_COST_ACCT;
1288 else
1289 adj_row.account_type := 'CIP_COST_ACCT';
1290 adj_row.account :=
1291 fa_cache_pkg.fazccb_record.CIP_COST_ACCT;
1292 end if;
1293
1294
1295 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
1296 X_last_update_date,
1297 X_last_updated_by,
1298 X_last_update_login
1299 , p_log_level_rec => p_log_level_rec)) then
1300
1301 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
1302
1303 return(false);
1304
1305 end if;
1306
1307 -- Credit cost in new dist_id's
1308
1309 adj_row.debit_credit_flag := 'DR';
1310 adj_row.selection_mode := fa_std_types.FA_AJ_ACTIVE;
1311 adj_row.adjustment_amount := adj_row.amount_inserted;
1312 adj_row.source_dest_code := 'DEST';
1313
1314 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
1315 X_last_update_date,
1316 X_last_updated_by,
1317 X_last_update_login
1318 , p_log_level_rec => p_log_level_rec)) then
1319
1320 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
1321
1322 return(False);
1323
1324 end if;
1325
1326 end if; -- if prior period
1327
1328 END LOOP; -- out_tax_books_dr
1329
1330 CLOSE TAX_BOOKS_DR;
1331
1332 /* Added for bug 9914820 */
1333 if not fa_cache_pkg.fazcbc (x_book => l_orig_book, p_log_level_rec => p_log_level_rec) then
1334 RAISE fagtax_error;
1335 end if;
1336 if l_orig_cat_book is not null then
1337 if not fa_cache_pkg.fazccb ( l_orig_cat_book, h_category_id, p_log_level_rec => p_log_level_rec) then
1338 RAISE fagtax_error;
1339 end if;
1340 end if;
1341
1342 return(true);
1343
1344
1345 EXCEPTION
1346
1347 when fagtax_error then
1348 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
1349 return FALSE;
1350
1351 when others then
1352 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
1353 return FALSE;
1354
1355
1356 END;
1357
1358 /*=============================================================
1359 | NAME fagiat
1360 |
1361 | FUNCTION Updating the tables affected by the previous retirement. We
1362 | reactivate the book and distribution history. Notice that
1363 | for cost retirement, the distribution_history table was NOT
1364 | affected, thus we don't need to do anything.
1365 |
1366 | History Jacob John 1/29/97 Created
1367 |==============================================================*/
1368
1369
1370 FUNCTION FAGIAT(
1371 RET IN OUT NOCOPY fa_ret_types.ret_struct,
1372 user_id IN number,
1373 cpd_ctr in number,
1374 today IN date,
1375 p_asset_fin_rec_new FA_API_TYPES.asset_fin_rec_type
1376 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) return boolean is
1377
1378 new_distid NUMBER;
1379 upd_ctr NUMBER;
1380
1381
1382 h_ret_id number;
1383 h_asset_id number;
1384 h_book varchar2(30);
1385 h_today date;
1386
1387 h_temp_units number;
1388
1389 h_rethdrout number;
1390 h_dist_hdrout number;
1394 h_new_distid number;
1391 h_rdistid number;
1392 h_tdistid number;
1393 h_pdistid number;
1395 h_temp_distid number;
1396 h_adj_distid number;
1397 h_drflag number;
1398 h_user_id number;
1399 --fix for 1722165 changed to afnumber to handle fractional units
1400 h_units_retired number;
1401 h_units_assigned number;
1402 h_count number;
1403 h_rowid rowid;
1404 h_mrc_primary_book_flag number;
1405
1406 -- Bug:5930979:Japan Tax Reform Project
1407 l_rate_in_use number;
1408
1409 fagiat_error EXCEPTION;
1410
1411 --Select active distribution_ids
1412 CURSOR TRET IS
1413 SELECT fadh.distribution_id
1414 FROM fa_distribution_history fadh,
1415 fa_book_controls bc
1416 WHERE fadh.asset_id = h_asset_id
1417 AND fadh.book_type_code = bc.distribution_source_book
1418 AND bc.book_type_code = h_book
1419 AND fadh.date_ineffective is null
1420 ORDER BY fadh.distribution_id;
1421
1422 -- Select distribution_ids that were retired
1423 CURSOR PRET IS
1424 SELECT dh.distribution_id
1425 FROM fa_distribution_history dh,
1426 fa_book_controls bc,
1427 fa_retirements rt
1428 WHERE dh.asset_id = h_asset_id
1429 AND dh.book_type_code = bc.distribution_source_book
1430 AND bc.book_type_code = h_book
1431 AND rt.asset_id = dh.asset_id
1432 AND rt.book_type_code = h_book
1433 AND dh.date_effective < rt.date_effective
1434 AND dh.date_ineffective >= rt.date_effective;
1435
1436
1437 -- Bug 5149832, 5237765, 5251944
1438 CURSOR OLD_NEW_DIST IS
1439 SELECT dh_old.distribution_id,
1440 dh_new.distribution_id
1441 FROM fa_distribution_history dh_old,
1442 fa_distribution_history dh_new,
1443 fa_book_controls bc,
1444 fa_transaction_headers th
1445 WHERE th.transaction_header_id = h_rethdrout
1446 and th.asset_id = h_asset_id
1447 and th.book_type_code = h_book
1448 and bc.book_type_code = th.book_type_code
1449 /* nvl condition is added by bug 6709967 */
1450 and dh_old.transaction_header_id_out = nvl(th.source_transaction_header_id,dh_old.transaction_header_id_out)
1451 and dh_old.book_type_code = bc.distribution_source_book
1452 and dh_old.asset_id = h_asset_id
1453 and
1454 (dh_old.units_assigned + dh_old.transaction_units = 0 -- FULL RET in dh_old DH row
1455 OR
1456 exists
1457 (select 1 -- PARTIAL RET in dh_pret DH row
1458 from fa_distribution_history dh_pret
1459 where dh_pret.asset_id = dh_old.asset_id
1460 and dh_pret.book_type_code = dh_old.book_type_code
1461 and dh_pret.transaction_header_id_out = DH_OLD.transaction_header_id_in
1462 and DH_OLD.transaction_units is NULL
1463 and dh_pret.units_assigned + dh_pret.transaction_units = dh_old.units_assigned
1464 and dh_pret.code_combination_id = dh_old.code_combination_id
1465 and nvl(dh_pret.assigned_to,-99) = nvl(dh_old.assigned_to,-99)
1466 and dh_pret.location_id = dh_old.location_id
1467 )
1468 --Added for 8741598
1469 OR not exists
1470 (select 1
1471 from fa_distribution_history fdh1
1472 where fdh1.asset_id = dh_old.asset_id
1473 and fdh1.book_type_code = dh_old.book_type_code
1474 and fdh1.transaction_header_id_in < DH_OLD.transaction_header_id_in)
1475 --End of added for 8741598
1476 )
1477 -- and dh_new.transaction_header_id_in = dh_old.transaction_header_id_out
1478 -- and dh_new.location_id = dh_old.location_id
1479 -- and nvl(dh_new.assigned_to,-99) = nvl(dh_old.assigned_to,-99)
1480 -- and dh_new.code_combination_id = dh_old.code_combination_id;
1481 -- Bug:6238808
1482 and dh_new.asset_id = h_asset_id
1483 and dh_new.book_type_code = bc.distribution_source_book
1484 and dh_new.date_ineffective is null;
1485
1486 CURSOR UPD_DIST IS
1487 SELECT dh.distribution_id
1488 FROM fa_distribution_history dh
1489 WHERE dh.book_type_code = h_book
1490 AND dh.asset_id = h_asset_id
1491 AND dh.transaction_header_id_out is null
1492 AND exists
1493 (
1494 SELECT 'x'
1495 FROM fa_distribution_history ret
1496 WHERE ret.book_type_code = h_book
1497 AND ret.asset_id = h_asset_id
1498 AND ret.retirement_id = h_ret_id
1499 AND ret.code_combination_id = dh.code_combination_id
1500 AND ret.location_id = dh.location_id
1501 AND nvl (ret.assigned_to, -99) = nvl (dh.assigned_to, -99)
1502 );
1503
1504 CURSOR CRET IS
1505 SELECT dh.distribution_id,
1506 dh.units_assigned
1507 FROM fa_distribution_history dh
1508 WHERE dh.book_type_code = h_book
1512 SELECT r.distribution_id, 0 - nvl (r.transaction_units, 0)
1509 AND dh.asset_id = h_asset_id
1510 AND dh.transaction_header_id_out = h_rethdrout
1511 UNION
1513 FROM fa_distribution_history r
1514 WHERE r.book_type_code = h_book
1515 AND r.asset_id = h_asset_id
1516 AND r.retirement_id = h_ret_id
1517 AND not exists
1518 (
1519 SELECT 'x'
1520 FROM fa_distribution_history d
1521 WHERE d.book_type_code = h_book
1522 AND d.asset_id = h_asset_id
1523 AND d.transaction_header_id_out = h_rethdrout
1524 AND r.code_combination_id = d.code_combination_id
1525 AND r.location_id = d.location_id
1526 AND nvl (r.assigned_to, -99) = nvl (d.assigned_to, -99)
1527 );
1528
1529 /* BUG 2775057 added joins for ccid, location, and employe
1530 * as this would result in the first distribution being used
1531 * for all updates in the case of multi-distributed assets
1532 */
1533 CURSOR CHG_DIST IS
1534 select d1.distribution_id
1535 from fa_distribution_history d1,
1536 fa_distribution_history d2
1537 where d2.book_type_code = h_book
1538 and d2.asset_id = h_asset_id
1539 and d1.book_type_code = d2.book_type_code
1540 and d1.asset_id = d2.asset_id
1541 and d1.transaction_header_id_in =
1542 d2.transaction_header_id_out
1543 and ((abs(d2.transaction_units) =
1544 d1.units_assigned) or
1545 (d2.retirement_id = h_ret_id))
1546 and d2.distribution_id = h_adj_distid
1547 and d1.code_combination_id = d2.code_combination_id -- added for bug 2775057
1548 and d1.location_id = d2.location_id -- added for bug 2775057
1549 and nvl(d1.assigned_to, -99) = nvl(d2.assigned_to, -99); -- added for bug 2775057
1550
1551 l_calling_fn varchar2(40) := 'FA_GAINLOSS_UND_PKG.fagiat';
1552
1553 l_temp_dist_id1 number;
1554
1555 BEGIN <<FAGIAT>>
1556
1557 h_today := today;
1558 h_ret_id := ret.retirement_id;
1559 h_asset_id := ret.asset_id;
1560 h_user_id := user_id;
1561 h_book := ret.book;
1562 h_units_retired := ret.units_retired;
1563 h_temp_units := 0;
1564
1565 /*
1566 -- CHECK to see if the following can be replaced with ret.mrc_sob_type_code
1567 -- select returns 0: if reporting book... 1: if primary book
1568 SELECT count(*)
1569 INTO h_mrc_primary_book_flag
1570 FROM gl_sets_of_books GL, fa_book_controls FA
1571 WHERE gl.set_of_books_id = fa.set_of_books_id
1572 AND fa.book_type_code = h_book
1573 AND gl.mrc_sob_type_code <> 'R'
1574 AND rownum <= 1;
1575 */
1576
1577 SELECT
1578 transaction_header_id_out
1579 INTO h_rethdrout
1580 FROM fa_retirements
1581 WHERE retirement_id = h_ret_id;
1582
1583 if (p_log_level_rec.statement_level) then
1584 fa_debug_pkg.add
1585 (fname => l_calling_fn,
1586 element => 'Updating FA_BOOKS',
1587 value => '', p_log_level_rec => p_log_level_rec);
1588 fa_debug_pkg.add(l_calling_fn, '++ h_ret_id in fagiat',h_ret_id, p_log_level_rec => p_log_level_rec);
1589 fa_debug_pkg.add(l_calling_fn, '++ h_rethdrout',h_rethdrout, p_log_level_rec => p_log_level_rec);
1590 ENd if;
1591
1592 /* Replaced this with the following
1593 if (h_mrc_primary_book_flag = 1) then
1594 UPDATE FA_BOOKS
1595 SET date_ineffective = h_today, -- to_date(h_today,'DD/MM/YYYY hh24:mi:ss'),
1596 transaction_header_id_out = h_rethdrout,
1597 last_updated_by = h_user_id,
1598 last_update_date = h_today -- to_date(h_today, 'DD/MM/YYYY hh24:mi:ss')
1599 WHERE book_type_code = h_book
1600 AND asset_id = h_asset_id
1601 AND date_ineffective is null;
1602 end if;
1603 */
1604 if (p_log_level_rec.statement_level) then
1605 fa_debug_pkg.add
1606 (fname => l_calling_fn,
1607 element => 'Deactivate fa_books row',
1608 value => '', p_log_level_rec => p_log_level_rec);
1609 end if;
1610
1611 if (p_log_level_rec.statement_level) then
1612 fa_debug_pkg.add
1613 (fname => l_calling_fn,
1614 element => 'X_transaction_header_id_out',
1615 value => h_rethdrout, p_log_level_rec => p_log_level_rec);
1616 end if;
1617
1618 -- Bug:5930979:Japan Tax Reform Project (Start)
1619 if fa_cache_pkg.fazccmt_record.GUARANTEE_RATE_METHOD_FLAG = 'YES' then
1620 if ret.mrc_sob_type_code <> 'R' then
1621 select nvl(rate_in_use,0)
1622 into l_rate_in_use
1623 from fa_books
1624 where asset_id = ret.asset_id
1625 and book_type_code = ret.book
1626 and transaction_header_id_out is null;
1627
1628 if p_log_level_rec.statement_level then
1629 fa_debug_pkg.add(l_calling_fn, 'rate_in_use (P) ', l_rate_in_use);
1630 end if;
1631
1632 else -- for reporting
1633 -- MRC
1634 /*select nvl(rate_in_use,0)
1635 into l_rate_in_use
1636 from fa_mc_books
1637 where asset_id = ret.asset_id
1638 and book_type_code = ret.book
1639 and set_of_books_id = ret.set_of_books_id
1643 fa_debug_pkg.add(l_calling_fn, 'rate_in_use (R) ', l_rate_in_use);
1640 and transaction_header_id_out is null;*/
1641
1642 if p_log_level_rec.statement_level then
1644 end if;
1645 end if;
1646 end if;
1647 -- Bug:5930979:Japan Tax Reform Project (End)
1648
1649 -- terminate the active row
1650 fa_books_pkg.deactivate_row
1651 (X_asset_id => h_asset_id,
1652 X_book_type_code => h_book,
1653 X_transaction_header_id_out => h_rethdrout,
1654 X_date_ineffective => h_today,
1655 X_mrc_sob_type_code => ret.mrc_sob_type_code,
1656 X_set_of_books_id => ret.set_of_books_id,
1657 X_Calling_Fn => l_calling_fn
1658 , p_log_level_rec => p_log_level_rec);
1659
1660
1661 if (p_log_level_rec.statement_level) then
1662 fa_debug_pkg.add
1663 (fname => l_calling_fn,
1664 element => 'Create a new fa_books row',
1665 value => '', p_log_level_rec => p_log_level_rec);
1666 end if;
1667
1668 /* Fix for Bug# 2513013: When reinstated, a newly created row in fa_books
1669 * had null in period_counter_life_complete since this was missing in the following insert.
1670 */
1671
1672 if ret.mrc_sob_type_code <> 'R' then
1673 INSERT into fa_books
1674 (book_type_Code, asset_id, date_placed_in_service,
1675 transaction_header_id_in, date_effective, transaction_header_id_out,
1676 date_ineffective, deprn_start_date, deprn_method_code,
1677 life_in_months, rate_adjustment_factor, adjusted_cost, cost,
1678 original_cost, salvage_value, period_counter_fully_retired,
1679 period_counter_fully_reserved,period_counter_life_complete,
1680 prorate_convention_code, prorate_date, itc_amount_id,
1681 itc_amount, cost_change_flag,
1682 adjustment_required_status,capitalize_flag,
1683 retirement_id, retirement_pending_flag, depreciate_flag,
1684 last_update_date,
1685 last_updated_by, itc_basis, tax_request_id,
1686 period_counter_capitalized, basic_rate, adjusted_rate, bonus_rule,
1687 ceiling_name, recoverable_cost,
1688 reval_amortization_basis, reval_ceiling,
1689 production_capacity, fully_rsvd_revals_counter,
1690 idled_flag, unit_of_measure, unrevalued_cost, adjusted_capacity,
1691 short_fiscal_year_flag, conversion_date, original_deprn_start_date,
1692 remaining_life1, remaining_life2,
1693 old_adjusted_cost, formula_factor,
1694 annual_deprn_rounding_flag,
1695 percent_salvage_value, allowed_deprn_limit, allowed_deprn_limit_amount
1696 , group_asset_id
1697 , recapture_reserve_flag
1698 , salvage_type
1699 , deprn_limit_type
1700 , super_group_id
1701 , reduce_addition_flag
1702 , reduce_adjustment_flag
1703 , reduce_retirement_flag
1704 , ytd_proceeds
1705 , ltd_proceeds
1706 , reduction_rate
1707 , over_depreciate_option
1708 , limit_proceeds_flag
1709 , terminal_gain_loss
1710 , tracking_method
1711 , exclude_fully_rsv_flag
1712 , excess_allocation_option
1713 , depreciation_option
1714 , member_rollup_flag
1715 , allocate_to_fully_rsv_flag
1716 , allocate_to_fully_ret_flag
1717 , recognize_gain_loss
1718 , terminal_gain_loss_amount
1719 , cip_cost
1720 , ltd_cost_of_removal
1721 , eofy_reserve
1722 , prior_eofy_reserve
1723 , eop_adj_cost
1724 , eop_formula_factor
1725 , exclude_proceeds_from_basis
1726 , retirement_deprn_option
1727 , adjusted_recoverable_cost /* fix for bug 3149457 */
1728 , cash_generating_unit_id
1729 , extended_deprn_flag -- Japan Tax Phase3
1730 , extended_depreciation_period -- Japan Tax Phase3
1731 , nbv_at_switch
1732 , prior_deprn_limit_type
1733 , prior_deprn_limit_amount
1734 , prior_deprn_limit
1735 , prior_deprn_method
1736 , prior_life_in_months
1737 , prior_basic_rate
1738 , prior_adjusted_rate
1739 )
1740 SELECT book_type_code
1741 , asset_id
1742 , date_placed_in_service
1743 , h_rethdrout
1744 , h_today
1745 , null
1746 , null, deprn_start_date, deprn_method_code
1747 , life_in_months, p_asset_fin_rec_new.rate_adjustment_factor
1748 , p_asset_fin_rec_new.adjusted_cost, p_asset_fin_rec_new.cost
1749 , original_cost
1750 , p_asset_fin_rec_new.salvage_value
1751 , null
1752 , decode(group_asset_id,null,period_counter_fully_reserved,null) --Bug 8425794
1753 , period_counter_life_complete
1754 , prorate_convention_code
1755 , prorate_date
1756 , itc_amount_id
1757 , itc_amount, cost_change_flag,
1758 adjustment_required_status, capitalize_flag,
1759 null, 'NO', depreciate_flag,
1760 h_today,
1761 h_user_id, itc_basis, null,
1762 period_counter_capitalized, basic_rate, adjusted_rate, bonus_rule,
1763 ceiling_name, p_asset_fin_rec_new.recoverable_cost,
1764 p_asset_fin_rec_new.reval_amortization_basis, reval_ceiling,--Bug#7478702
1765 production_capacity, fully_rsvd_revals_counter,
1766 idled_flag, unit_of_measure, p_asset_fin_rec_new.unrevalued_cost, adjusted_capacity,
1767 short_fiscal_year_flag, conversion_date, original_deprn_start_date,
1771 percent_salvage_value, allowed_deprn_limit, p_asset_fin_rec_new.allowed_deprn_limit_amount
1768 remaining_life1, remaining_life2,
1769 old_adjusted_cost, formula_factor,
1770 annual_deprn_rounding_flag,
1772 , group_asset_id
1773 , recapture_reserve_flag
1774 , salvage_type
1775 , deprn_limit_type
1776 , super_group_id
1777 , reduce_addition_flag
1778 , reduce_adjustment_flag
1779 , reduce_retirement_flag
1780 , ytd_proceeds
1781 , ltd_proceeds
1782 , reduction_rate
1783 , over_depreciate_option
1784 , limit_proceeds_flag
1785 , terminal_gain_loss
1786 , tracking_method
1787 , exclude_fully_rsv_flag
1788 , excess_allocation_option
1789 , depreciation_option
1790 , member_rollup_flag
1791 , allocate_to_fully_rsv_flag
1792 , allocate_to_fully_ret_flag
1793 , recognize_gain_loss
1794 , terminal_gain_loss_amount
1795 , cip_cost
1796 , ltd_cost_of_removal
1797 , p_asset_fin_rec_new.eofy_reserve /* fix for bug 5260926 */
1798 , prior_eofy_reserve
1799 , eop_adj_cost
1800 , eop_formula_factor
1801 , exclude_proceeds_from_basis
1802 , retirement_deprn_option
1803 , p_asset_fin_rec_new.adjusted_recoverable_cost /* fix for bug 3149457 */
1804 , cash_generating_unit_id
1805 , extended_deprn_flag -- Japan Tax Phase3
1806 , extended_depreciation_period -- Japan Tax Phase3
1807 , nbv_at_switch
1808 , prior_deprn_limit_type
1809 , prior_deprn_limit_amount
1810 , prior_deprn_limit
1811 , prior_deprn_method
1812 , prior_life_in_months
1813 , prior_basic_rate
1814 , prior_adjusted_rate
1815 FROM fa_books
1816 WHERE asset_id = ret.asset_id
1817 AND book_type_code = ret.book
1818 AND transaction_header_id_out = h_rethdrout;
1819 else -- for reporting
1820 INSERT into fa_mc_books
1821 (book_type_Code, asset_id, date_placed_in_service,
1822 transaction_header_id_in, date_effective, transaction_header_id_out,
1823 date_ineffective, deprn_start_date, deprn_method_code,
1824 life_in_months, rate_adjustment_factor, adjusted_cost, cost,
1825 original_cost, salvage_value, period_counter_fully_retired,
1826 period_counter_fully_reserved,period_counter_life_complete,
1827 prorate_convention_code, prorate_date, itc_amount_id,
1828 itc_amount, cost_change_flag,
1829 adjustment_required_status,capitalize_flag,
1830 retirement_id, retirement_pending_flag, depreciate_flag,
1831 last_update_date,
1832 last_updated_by, itc_basis, tax_request_id,
1833 period_counter_capitalized, basic_rate, adjusted_rate, bonus_rule,
1834 ceiling_name, recoverable_cost,
1835 reval_amortization_basis, reval_ceiling,
1836 production_capacity, fully_rsvd_revals_counter,
1837 idled_flag, unit_of_measure, unrevalued_cost, adjusted_capacity,
1838 short_fiscal_year_flag, conversion_date, original_deprn_start_date,
1839 remaining_life1, remaining_life2,
1840 old_adjusted_cost, formula_factor,
1841 annual_deprn_rounding_flag,
1842 percent_salvage_value, allowed_deprn_limit, allowed_deprn_limit_amount
1843 , group_asset_id
1844 , recapture_reserve_flag
1845 , salvage_type
1846 , deprn_limit_type
1847 , super_group_id
1848 , reduce_addition_flag
1849 , reduce_adjustment_flag
1850 , reduce_retirement_flag
1851 , ytd_proceeds
1852 , ltd_proceeds
1853 , reduction_rate
1854 , over_depreciate_option
1855 , limit_proceeds_flag
1856 , terminal_gain_loss
1857 , tracking_method
1858 , exclude_fully_rsv_flag
1859 , excess_allocation_option
1860 , depreciation_option
1861 , member_rollup_flag
1862 , allocate_to_fully_rsv_flag
1863 , allocate_to_fully_ret_flag
1864 , recognize_gain_loss
1865 , terminal_gain_loss_amount
1866 , cip_cost
1867 , ltd_cost_of_removal
1868 , eofy_reserve
1869 , prior_eofy_reserve
1870 , eop_adj_cost
1871 , eop_formula_factor
1872 , exclude_proceeds_from_basis
1873 , retirement_deprn_option
1874 , adjusted_recoverable_cost /* fix for bug 3149457 */
1875 , cash_generating_unit_id
1876 , set_of_books_id
1877 )
1878 SELECT book_type_code, asset_id, date_placed_in_service,
1879 h_rethdrout, h_today, null,
1880 null, deprn_start_date, deprn_method_code,
1881 life_in_months, p_asset_fin_rec_new.rate_adjustment_factor
1882 , p_asset_fin_rec_new.adjusted_cost, p_asset_fin_rec_new.cost,
1883 original_cost,
1884 p_asset_fin_rec_new.salvage_value, null, decode(group_asset_id,null,period_counter_fully_reserved,null) --Bug 8425794
1885 ,period_counter_life_complete,
1886 prorate_convention_code, prorate_date, itc_amount_id,
1887 itc_amount, cost_change_flag,
1888 adjustment_required_status, capitalize_flag,
1889 null, 'NO', depreciate_flag,
1890 h_today,
1891 h_user_id, itc_basis, null,
1892 period_counter_capitalized, basic_rate, adjusted_rate, bonus_rule,
1893 ceiling_name, p_asset_fin_rec_new.recoverable_cost,
1894 p_asset_fin_rec_new.reval_amortization_basis, reval_ceiling,--Bug#7478702
1895 production_capacity, fully_rsvd_revals_counter,
1899 old_adjusted_cost, formula_factor,
1896 idled_flag, unit_of_measure, unrevalued_cost, adjusted_capacity,
1897 short_fiscal_year_flag, conversion_date, original_deprn_start_date,
1898 remaining_life1, remaining_life2,
1900 annual_deprn_rounding_flag,
1901 percent_salvage_value, allowed_deprn_limit, p_asset_fin_rec_new.allowed_deprn_limit_amount
1902 , group_asset_id
1903 , recapture_reserve_flag
1904 , salvage_type
1905 , deprn_limit_type
1906 , super_group_id
1907 , reduce_addition_flag
1908 , reduce_adjustment_flag
1909 , reduce_retirement_flag
1910 , ytd_proceeds
1911 , ltd_proceeds
1912 , reduction_rate
1913 , over_depreciate_option
1914 , limit_proceeds_flag
1915 , terminal_gain_loss
1916 , tracking_method
1917 , exclude_fully_rsv_flag
1918 , excess_allocation_option
1919 , depreciation_option
1920 , member_rollup_flag
1921 , allocate_to_fully_rsv_flag
1922 , allocate_to_fully_ret_flag
1923 , recognize_gain_loss
1924 , terminal_gain_loss_amount
1925 , cip_cost
1926 , ltd_cost_of_removal
1927 , p_asset_fin_rec_new.eofy_reserve /* fix for bug 5260926 */
1928 , prior_eofy_reserve
1929 , eop_adj_cost
1930 , eop_formula_factor
1931 , exclude_proceeds_from_basis
1932 , retirement_deprn_option
1933 , p_asset_fin_rec_new.adjusted_recoverable_cost /* fix for bug 3149457 */
1934 , cash_generating_unit_id
1935 , set_of_books_id
1936 FROM fa_mc_books
1937 WHERE asset_id = ret.asset_id
1938 AND book_type_code = ret.book
1939 AND set_of_books_id = ret.set_of_books_id
1940 AND transaction_header_id_out = h_rethdrout;
1941 end if;
1942
1943 if (p_log_level_rec.statement_level) then
1944 fa_debug_pkg.add
1945 (fname => l_calling_fn,
1946 element => 'Fetch DH.TRANSACTION_HEADER_ID_OUT',
1947 value => '', p_log_level_rec => p_log_level_rec);
1948 end if;
1949
1950 if (p_log_level_rec.statement_level) then
1951 fa_debug_pkg.add
1952 (fname => l_calling_fn,
1953 element => '+++ Step 3',
1954 value => '', p_log_level_rec => p_log_level_rec);
1955 end if;
1956
1957 -- Bug:5930979:Japan Tax Reform Project (Start)
1958 if fa_cache_pkg.fazccmt_record.GUARANTEE_RATE_METHOD_FLAG = 'YES' then
1959 if ret.mrc_sob_type_code <> 'R' then
1960 update fa_books
1961 set rate_in_use = l_rate_in_use
1962 where asset_id = ret.asset_id
1963 and book_type_code = ret.Book
1964 and transaction_header_id_out is null;
1965
1966 if p_log_level_rec.statement_level then
1967 fa_debug_pkg.add(l_calling_fn, 'Updated rate_in_use (P) ', l_rate_in_use);
1968 end if;
1969
1970 else -- For Reporting
1971 -- TO DO -- MRC
1972 /*update fa_mc_books
1973 set rate_in_use = l_rate_in_use
1974 where asset_id = ret.asset_id
1975 and book_type_code = ret.Book
1976 and set_of_books_id = ret.set_of_books_id
1977 and transaction_header_id_out is null;*/
1978
1979 if p_log_level_rec.statement_level then
1980 fa_debug_pkg.add(l_calling_fn, 'Updated rate_in_use (R) ', l_rate_in_use);
1981 end if;
1982 end if;
1983 end if;
1984 -- Bug:5930979:Japan Tax Reform Project (End)
1985
1986 BEGIN
1987 SELECT distinct fadh.TRANSACTION_HEADER_ID_OUT,
1988 DECODE(fadh.TRANSACTION_HEADER_ID_OUT, null, 0, 1)
1989 INTO h_dist_hdrout, h_drflag
1990 FROM FA_DISTRIBUTION_HISTORY fadh
1991 WHERE fadh.retirement_id = h_ret_id;
1992 EXCEPTION
1993 WHEN NO_DATA_FOUND then
1994 NULL;
1995 END;
1996
1997 if (p_log_level_rec.statement_level) then
1998 fa_debug_pkg.add
1999 (fname => l_calling_fn,
2000 element => '+++ Step 4',
2001 value => '', p_log_level_rec => p_log_level_rec);
2002 end if;
2003
2004 if (RET.units_retired is null or RET.units_retired <= 0) then
2005 -- must be cost retired
2006
2007 /**** -- Replaced this with the following
2008 OPEN TRET;
2009 OPEN PRET;
2010
2011 LOOP
2012 FETCH TRET INTO h_tdistid;
2013 exit when TRET%notfound or TRET%notfound IS NULL;
2014 FETCH PRET INTO h_pdistid;
2015 exit when PRET%notfound or PRET%notfound IS NULL;
2016
2017 if ret.mrc_sob_type_code <> 'R' then
2018 UPDATE fa_adjustments aj
2019 SET distribution_id = h_tdistid
2020 WHERE aj.asset_id = h_asset_id
2021 AND aj.book_type_code = h_book
2022 AND aj.distribution_id = h_pdistid
2023 AND aj.transaction_header_id = h_rethdrout;
2024 else
2025 UPDATE fa_mc_adjustments aj
2026 SET distribution_id = h_tdistid
2027 WHERE aj.asset_id = h_asset_id
2028 AND aj.book_type_code = h_book
2029 AND aj.distribution_id = h_pdistid
2030 AND aj.set_of_books_id = ret.set_of_books_id
2031 AND aj.transaction_header_id = h_rethdrout;
2032 end if;
2033
2034
2035 END LOOP;
2036 CLOSE PRET;
2037 CLOSE TRET;
2038 ***/
2039
2040 -- Bug 5149832
2044
2041 OPEN OLD_NEW_DIST;
2042
2043 LOOP
2045 FETCH OLD_NEW_DIST INTO
2046 h_pdistid,
2047 h_tdistid;
2048 EXIT WHEN OLD_NEW_DIST%NOTFOUND or OLD_NEW_DIST%NOTFOUND IS NULL;
2049
2050 if (p_log_level_rec.statement_level) then
2051 fa_debug_pkg.add(l_calling_fn, '++ h_pdistid OLD DIST', h_pdistid, p_log_level_rec => p_log_level_rec);
2052 fa_debug_pkg.add(l_calling_fn, '++ h_tdistid NEW DIST', h_tdistid, p_log_level_rec => p_log_level_rec);
2053 end if;
2054
2055 if ret.mrc_sob_type_code <> 'R' then
2056 UPDATE fa_adjustments aj
2057 SET distribution_id = h_tdistid
2058 WHERE aj.asset_id = h_asset_id
2059 AND aj.book_type_code = h_book
2060 AND aj.distribution_id = h_pdistid
2061 AND aj.transaction_header_id = h_rethdrout;
2062 else
2063 UPDATE fa_mc_adjustments aj
2064 SET distribution_id = h_tdistid
2065 WHERE aj.asset_id = h_asset_id
2066 AND aj.book_type_code = h_book
2067 AND aj.distribution_id = h_pdistid
2068 AND aj.set_of_books_id = ret.set_of_books_id
2069 AND aj.transaction_header_id = h_rethdrout;
2070 end if;
2071
2072 END LOOP;
2073
2074 CLOSE OLD_NEW_DIST;
2075
2076 return(TRUE);
2077
2078 else -- RET.units_retired is null or RET.units_retired
2079
2080 if (h_drflag = 1) then
2081 -- There is a Header out, must be partialunit retired
2082
2083 if p_log_level_rec.statement_level then
2084 fa_debug_pkg.add
2085 (fname => l_calling_fn,
2086 element => 'Update FA_DISTRIBUTION_HISTORY',
2087 value => '', p_log_level_rec => p_log_level_rec);
2088 end if;
2089
2090 -- bugfix for 991646.
2091 -- When all units of a distribution line or lines in a multi-distributed
2092 -- asset that are
2093 -- retired are reinstated, no need to terminate any distribution rows
2094 -- as in the case
2095 -- for all other partial retirement case. Hence the following update of
2096 -- fa_distribution_history is bypassed and it moves on to create new
2097 -- distribution rows with original units before the retirement
2098
2099 -- Fix for Bug #1256872. Select the active distributions
2100 -- to be terminated. They will be the duplicates of the
2101 -- retired distributions we are now reinstating.
2102
2103 if p_log_level_rec.statement_level then
2104 fa_debug_pkg.add
2105 (fname => l_calling_fn,
2106 element => '+++ Step 4',
2107 value => '', p_log_level_rec => p_log_level_rec);
2108 end if;
2109
2110 OPEN UPD_DIST;
2111 upd_ctr := 0;
2112 LOOP
2113 FETCH UPD_DIST INTO h_rdistid;
2114 if (UPD_DIST%NOTFOUND) then
2115 if (upd_ctr > 0) then
2116 exit;
2117 end if;
2118 -- Fix for Bug #1256872. We aren't terminating already
2119 -- terminated rows, so we may have no rows to update here.
2120 -- Just need to check if we have a least one row to later add
2121 -- for the new distribution. Removing check to see if
2122 -- abs(transaction_units) = units_assigned.
2123
2124 select count(*)
2125 into h_count
2126 from fa_distribution_history
2127 where transaction_header_id_out = h_dist_hdrout
2128 and book_type_code = h_book
2129 and asset_id = h_asset_id
2130 and retirement_id = h_ret_id;
2131
2132 if p_log_level_rec.statement_level then
2133 fa_debug_pkg.add
2134 (fname => l_calling_fn,
2135 element => '+++ Step 4.1',
2136 value => '', p_log_level_rec => p_log_level_rec);
2137 end if;
2138
2139 if (h_count > 0) then
2140 exit;
2141 else
2142 raise fagiat_error;
2143 end if;
2144 else
2145
2146 if p_log_level_rec.statement_level then
2147 fa_debug_pkg.add
2148 (fname => l_calling_fn,
2149 element => '+++ Step 4.6',
2150 value => '', p_log_level_rec => p_log_level_rec);
2151 end if;
2152
2153 -- UPDATING FA_DISTRIBUTION_HISTORY
2154 -- if (h_mrc_primary_book_flag = 1) then
2155 if (ret.mrc_sob_type_code <> 'R') then
2156 UPDATE FA_DISTRIBUTION_HISTORY
2157 SET date_ineffective =
2158 h_today, -- to_date(h_today,'DD/MM/YYYY hh24:mi:ss'),
2159 transaction_header_id_out = h_rethdrout,
2160 last_update_date =
2161 h_today, -- to_date(h_today,'DD/MM/YYYY hh24:mi:ss'),
2162 last_updated_by = h_user_id
2163 WHERE distribution_id = h_rdistid;
2164 end if;
2165 -- END UPDATING FA_DISTRIBUTION_HISTORY
2166 upd_ctr := upd_ctr + 1;
2167
2168 if p_log_level_rec.statement_level then
2172 value => '', p_log_level_rec => p_log_level_rec);
2169 fa_debug_pkg.add
2170 (fname => l_calling_fn,
2171 element => '+++ Step 4.7',
2173 end if;
2174
2175 end if;
2176 END LOOP;
2177 CLOSE UPD_DIST;
2178
2179 if p_log_level_rec.statement_level then
2180 fa_debug_pkg.add
2181 (fname => l_calling_fn,
2182 element => '+++ Step 5',
2183 value => '', p_log_level_rec => p_log_level_rec);
2184 end if;
2185
2186 -- UPDATING FA_ASSET_HISTORY,
2187 -- INSERTING FA_ASSET_HISTORY,
2188 -- UPDATING FA_ADDITIONS
2189 -- if (h_mrc_primary_book_flag = 1) then
2190 if (ret.mrc_sob_type_code <> 'R') then
2191
2192 if p_log_level_rec.statement_level then
2193 fa_debug_pkg.add
2194 (fname => l_calling_fn,
2195 element => '+++ Step 5.1',
2196 value => '', p_log_level_rec => p_log_level_rec);
2197 end if;
2198
2199 -- select rowidtochar(rowid)
2200 select rowid
2201 into h_rowid
2202 from fa_asset_history
2203 where asset_id = RET.asset_id
2204 and date_ineffective is null;
2205
2206 if p_log_level_rec.statement_level then
2207 fa_debug_pkg.add
2208 (fname => l_calling_fn,
2209 element => '+++ Step 5.1.5',
2210 value => '', p_log_level_rec => p_log_level_rec);
2211 end if;
2212
2213 update fa_asset_history
2214 set date_ineffective = today,
2215 transaction_header_id_out = h_rethdrout
2216 where rowid = h_rowid;
2217
2218 if p_log_level_rec.statement_level then
2219 fa_debug_pkg.add
2220 (fname => l_calling_fn,
2221 element => '+++ Step 5.2',
2222 value => '', p_log_level_rec => p_log_level_rec);
2223 end if;
2224
2225 insert into fa_asset_history
2226 (asset_id, category_id, units, asset_type,
2227 date_effective, date_ineffective, last_update_date,
2228 last_updated_by,transaction_header_id_in
2229 )
2230 select asset_id, category_id, units + RET.units_retired,
2231 asset_type, today,
2232 null, today,
2233 user_id, h_rethdrout
2234 from fa_asset_history
2235 where rowid = h_rowid;
2236
2237 if p_log_level_rec.statement_level then
2238 fa_debug_pkg.add
2239 (fname => l_calling_fn,
2240 element => '+++ Step 5.3',
2241 value => '', p_log_level_rec => p_log_level_rec);
2242 end if;
2243
2244 -- CHECK: Had to change the table name to fa_additions_B table.
2245 -- because updating fa_additions.current_units caused
2246 -- ORA-01779: cannot modify a column which maps to
2247 -- a non key-preserved table error
2248
2249 update fa_additions_B
2250 set current_units = current_units + h_units_retired
2251 where asset_id = RET.asset_id;
2252
2253 if p_log_level_rec.statement_level then
2254 fa_debug_pkg.add
2255 (fname => l_calling_fn,
2256 element => '+++ Step 5.4',
2257 value => '', p_log_level_rec => p_log_level_rec);
2258 end if;
2259
2260 end if;
2261 -- END UPDATING FA_ASSET_HISTORY,
2262 -- END INSERTING FA_ASSET_HISTORY,
2263 -- END UPDATING FA_ADDITIONS
2264
2265 if p_log_level_rec.statement_level then
2266 fa_debug_pkg.add
2267 (fname => l_calling_fn,
2268 element => '+++ Step 6',
2269 value => '', p_log_level_rec => p_log_level_rec);
2270 end if;
2271
2272
2273 else -- Must be full retirement else for if There is a Header out
2274
2275 if p_log_level_rec.statement_level then
2276 fa_debug_pkg.add
2277 (fname => l_calling_fn,
2278 element => 'Update FA_DISTRIBUTION_HISTORY (FULL RETIREMENT)',
2279 value => '');
2280 end if;
2281
2282 -- UPDATING FA_DISTRIBUTION_HISTORY
2283 --if (h_mrc_primary_book_flag = 1) then
2284 if (ret.mrc_sob_type_code <> 'R') then
2285 UPDATE FA_DISTRIBUTION_HISTORY
2286 SET date_ineffective =
2287 today,
2288 transaction_header_id_out = h_rethdrout,
2289 last_update_date =
2290 today,
2291 last_updated_by = h_user_id
2292 WHERE retirement_id = h_ret_id
2293 AND book_type_code = h_book
2294 AND asset_id = h_asset_id;
2295 end if;
2296 end if; -- if h_drflag = 1
2297
2298 if p_log_level_rec.statement_level then
2299 fa_debug_pkg.add
2303 end if;
2300 (fname => l_calling_fn,
2301 element => 'Update FA_DISTRIBUTION_HISTORY (new)',
2302 value => '');
2304
2305 -- Fix for Bug #1256872. Select the active distributions
2306 -- were just terminated. They will be the duplicates of the
2307 -- retired distributions we are now reinstating. If no active
2308 -- distributions were terminated, we will need to reinstate
2309 -- those units retired for the inactive distributions of the
2310 -- original retirement. The second part of the UNION selects
2311 -- these distributions that no longer are active.
2312
2313 OPEN CRET;
2314 LOOP
2315 FETCH CRET INTO h_rdistid, h_units_assigned;
2316 exit when CRET%notfound or CRET%notfound IS NULL;
2317
2318 BEGIN
2319 Select FA_DISTRIBUTION_HISTORY_s.nextval
2320 into new_distid
2321 from dual;
2322 EXCEPTION
2323 when others then
2324 return(false);
2325 END;
2326
2327 h_new_distid := new_distid;
2328
2329 --fix for 1722165 - changed to afnumber to handle fractional units
2330 -- h_units_retired = 0;
2331 h_units_retired := 0;
2332
2333 -- Fix for Bug #1256872. Find the units retired for this
2334 -- distribution
2335 -- if any existed. We need to make sure that we are not selecting
2336 -- the same distribution that we got the units_assigned from or
2337 -- we will be double-counting the number of units.
2338
2339 BEGIN
2340 SELECT 0 - nvl (ret.transaction_units, 0)
2341 INTO h_units_retired
2342 FROM fa_distribution_history ret
2343 WHERE ret.book_type_code = h_book
2344 AND ret.asset_id = h_asset_id
2345 AND ret.retirement_id = h_ret_id
2346 AND exists
2347 (
2348 SELECT 'x'
2349 FROM fa_distribution_history dh
2350 WHERE dh.book_type_code = h_book
2351 AND dh.asset_id = h_asset_id
2352 AND dh.distribution_id = h_rdistid
2353 AND dh.distribution_id <> ret.distribution_id
2354 AND ret.code_combination_id = dh.code_combination_id
2355 AND ret.location_id = dh.location_id
2356 AND nvl (ret.assigned_to, -99) = nvl (dh.assigned_to, -99)
2357 );
2358 EXCEPTION
2359 WHEN NO_DATA_FOUND then
2360 NULL;
2361 END;
2362
2363 -- INSERTING FA_DISTRIBUTION_HISTORY
2364 --if (h_mrc_primary_book_flag = 1) then
2365 if (ret.mrc_sob_type_code <> 'R') then
2366 INSERT INTO FA_DISTRIBUTION_HISTORY
2367 (distribution_id, book_type_code, asset_id,
2368 units_assigned, date_effective, date_ineffective,
2369 code_Combination_id,
2370 location_id, assigned_to, transaction_header_id_in,
2371 transaction_header_id_out, transaction_units,
2372 retirement_id, last_update_date, last_updated_by)
2373 SELECT new_distid, book_type_code, asset_id,
2374 nvl(h_units_assigned,0) + nvl(h_units_retired,0), today,
2375 null, code_Combination_id,
2376 location_id, assigned_to, h_rethdrout,
2377 null, null, null, today,
2378 user_id
2379 FROM FA_DISTRIBUTION_HISTORY
2380 WHERE distribution_id = h_rdistid;
2381
2382 end if;
2383
2384 -- Update FA_ADJUSTMENTS so that it contain the new distribution
2385 -- id instead of the old one. Reserve Ledger needs this new id
2386
2387 -- Fix for Bug #1256872.The distributions we want to change are the
2388 -- distributions from original retirement and may not be those that
2389 -- were just terminated
2390
2391 -- if (h_mrc_primary_book_flag = 1) then
2392 if (ret.mrc_sob_type_code <> 'R') then
2393
2394 UPDATE FA_ADJUSTMENTS
2395 SET DISTRIBUTION_ID = new_distid
2396
2397 WHERE TRANSACTION_HEADER_ID = h_rethdrout
2398 AND DISTRIBUTION_ID =
2399 (
2400 SELECT DISTINCT r.distribution_id
2401 FROM fa_distribution_history r
2402 WHERE r.book_type_code = h_book
2403 AND r.asset_id = h_asset_id
2404 AND r.retirement_id = h_ret_id
2405 AND exists
2406 (
2407 SELECT 'x'
2408 FROM fa_distribution_history d
2409 WHERE d.book_type_code = h_book
2410 AND d.asset_id = h_asset_id
2411 AND d.distribution_id = h_rdistid
2412 AND r.code_combination_id = d.code_combination_id
2413 AND r.location_id = d.location_id
2414 AND nvl (r.assigned_to, -99) = nvl (d.assigned_to, -99)
2415 )
2416 );
2417 else
2418 -- CHECK: Fix for 1422427.For reporting book,we cannot use h_new_distid
2422 /* BUG# 2775057 - added rdistid to the first subselect as well as
2419 -- b/c we need to use the new distid already created by the
2420 -- primary book.
2421
2423 * assets with multiple disitributions were causing ora-1722
2424 */
2425
2426 DECLARE
2427 /* Bug 3116047 - Broke the single update statement and created
2428 this cursor enclosing with the DECLARE/BEGIN/END.
2429 As a result of the High Cost SQL exercise msiddiqu */
2430
2431 /* Bug 4890085: Modified cursor C1 as it was not returning any rows.
2432 The conditions "o.retirement_id = h_ret_id" and
2433 "o.distribution_id = h_rdistid" were contradicting so splitted them. */
2434
2435 Cursor C1 is
2436 SELECT DISTINCT n.distribution_id
2437 FROM fa_distribution_history n
2438 WHERE n.book_type_code = h_book
2439 AND n.asset_id = h_asset_id
2440 AND n.date_ineffective is null
2441 AND exists
2442 (
2443 SELECT 'x'
2444 FROM fa_distribution_history o
2445 WHERE o.book_type_code = h_book
2446 AND o.asset_id = h_asset_id
2447 --AND o.retirement_id = h_ret_id
2448 AND o.distribution_id = h_rdistid -- added for bug 2775057
2449 AND n.code_combination_id = o.code_combination_id
2450 AND n.location_id = o.location_id
2451 AND nvl (n.assigned_to, -99) = nvl (o.assigned_to, -99)
2452 )
2453 AND exists
2454 (
2455 SELECT 'x'
2456 FROM fa_distribution_history o
2457 WHERE o.book_type_code = h_book
2458 AND o.asset_id = h_asset_id
2459 AND o.retirement_id = h_ret_id
2460 --AND o.distribution_id = h_rdistid -- added for bug 2775057
2461 AND n.code_combination_id = o.code_combination_id
2462 AND n.location_id = o.location_id
2463 AND nvl (n.assigned_to, -99) = nvl (o.assigned_to, -99)
2464 );
2465
2466 BEGIN
2467
2468 For C1_rec in C1 Loop
2469
2470 -- Fix for Bug #3678791. Break previous update statement
2471 -- into multiple smaller statements.
2472 SELECT DISTINCT r.distribution_id
2473 INTO l_temp_dist_id1
2474 FROM fa_distribution_history r
2475 WHERE r.book_type_code = h_book
2476 AND r.asset_id = h_asset_id
2477 AND r.retirement_id = h_ret_id
2478 AND exists
2479 (
2480 SELECT 'x'
2481 FROM fa_distribution_history d
2482 WHERE d.book_type_code = h_book
2483 AND d.asset_id = h_asset_id
2484 AND d.distribution_id = h_rdistid
2485 AND r.code_combination_id = d.code_combination_id
2486 AND r.location_id = d.location_id
2487 AND nvl (r.assigned_to, -99) = nvl (d.assigned_to, -99)
2488 );
2489
2490 -- CHECK: this was fa_adjustments table before which i think was wrong
2491 UPDATE FA_MC_ADJUSTMENTS
2492 SET DISTRIBUTION_ID = C1_rec.distribution_id
2493 WHERE TRANSACTION_HEADER_ID = h_rethdrout
2494 AND book_type_code = h_book /* Added for bug 7659930*/
2495 AND DISTRIBUTION_ID = l_temp_dist_id1
2496 AND set_of_books_id = ret.set_of_books_id;
2497
2498 End Loop;
2499 END;
2500 end if;
2501
2502 /* Bug2447411 h_drflag has to be compared w/ 0 according to Pro*C */
2503 -- if (h_drflag = 1) then -- if fully retired
2504 if (h_drflag = 0) then -- if fully retired
2505 BEGIN
2506 select d1.distribution_id
2507 into h_adj_distid
2508 from fa_distribution_history d1,
2509 fa_distribution_history d2
2510 where d2.book_type_code = h_book
2511 and d2.asset_id = h_asset_id
2512 and d1.book_type_code = d2.book_type_code
2513 and d1.asset_id = d2.asset_id
2514 and d1.transaction_header_id_in =
2515 d2.transaction_header_id_out
2516 and d1.code_combination_id = d2.code_combination_id
2517 and d1.location_id = d2.location_id
2518 and nvl(d1.assigned_to, -99) = nvl(d2.assigned_to, -99)
2519 and d2.distribution_id = h_rdistid;
2520 EXCEPTION
2521 WHEN NO_DATA_FOUND then
2522 NULL;
2523 END;
2524
2525 else
2526 -- Fix for Bug #1256872. Need to find active distribution if
2527 -- terminated distribution was transferred.
2528 h_adj_distid := h_rdistid;
2529 OPEN CHG_DIST;
2530 LOOP
2531 FETCH CHG_DIST INTO h_temp_distid;
2532 EXIT WHEN CHG_DIST%NOTFOUND;
2536 end if;
2533 h_adj_distid := h_temp_distid;
2534 END LOOP;
2535 CLOSE CHG_DIST;
2537
2538
2539 -- Fix for Bug #1256872. Also need to make sure that rows in
2540 -- fa_adjustments to balance the initial retirement come from
2541 -- the distribution that was just terminated and not the new
2542 -- distribution created from the initial retirement
2543
2544 if (ret.mrc_sob_type_code <> 'R') then
2545
2546 UPDATE FA_ADJUSTMENTS
2547 SET DISTRIBUTION_ID = h_adj_distid
2548 WHERE TRANSACTION_HEADER_ID = h_rethdrout
2549 AND DISTRIBUTION_ID <> h_adj_distid
2550 AND DISTRIBUTION_ID =
2551 (
2552 SELECT DISTINCT r.distribution_id
2553 FROM fa_distribution_history r
2554 WHERE r.book_type_code = h_book
2555 AND r.asset_id = h_asset_id
2556 AND r.transaction_header_id_in =
2557 (
2558 SELECT DISTINCT transaction_header_id_out
2559 FROM fa_distribution_history
2560 WHERE book_type_code = h_book
2561 AND asset_id = h_asset_id
2562 AND retirement_id = h_ret_id
2563 )
2564 AND exists
2565 (
2566 SELECT 'x'
2567 FROM fa_distribution_history d
2568 WHERE d.book_type_code = h_book
2569 AND d.asset_id = h_asset_id
2570 AND d.distribution_id = h_rdistid
2571 AND r.code_combination_id = d.code_combination_id
2572 AND r.location_id = d.location_id
2573 AND nvl (r.assigned_to, -99) = nvl (d.assigned_to, -99)
2574 )
2575 )
2576 AND not exists
2577 (
2578 SELECT 'x'
2579 FROM fa_distribution_history
2580 WHERE book_type_code = h_book
2581 AND asset_id = h_asset_id
2582 AND retirement_id = h_ret_id
2583 AND distribution_id = h_adj_distid
2584 );
2585
2586 else
2587
2588 UPDATE FA_MC_ADJUSTMENTS
2589 SET DISTRIBUTION_ID = h_adj_distid
2590 WHERE TRANSACTION_HEADER_ID = h_rethdrout
2591 AND DISTRIBUTION_ID <> h_adj_distid
2592 AND SET_OF_BOOKS_ID = ret.set_of_books_id
2593 AND DISTRIBUTION_ID =
2594 (
2595 SELECT DISTINCT r.distribution_id
2596 FROM fa_distribution_history r
2597 WHERE r.book_type_code = h_book
2598 AND r.asset_id = h_asset_id
2599 AND r.transaction_header_id_in =
2600 (
2601 SELECT DISTINCT transaction_header_id_out
2602 FROM fa_distribution_history
2603 WHERE book_type_code = h_book
2604 AND asset_id = h_asset_id
2605 AND retirement_id = h_ret_id
2606 )
2607 AND exists
2608 (
2609 SELECT 'x'
2610 FROM fa_distribution_history d
2611 WHERE d.book_type_code = h_book
2612 AND d.asset_id = h_asset_id
2613 AND d.distribution_id = h_rdistid
2614 AND r.code_combination_id = d.code_combination_id
2615 AND r.location_id = d.location_id
2616 AND nvl (r.assigned_to, -99) = nvl (d.assigned_to, -99)
2617 )
2618 )
2619 AND not exists
2620 (
2621 SELECT 'x'
2622 FROM fa_distribution_history
2623 WHERE book_type_code = h_book
2624 AND asset_id = h_asset_id
2625 AND retirement_id = h_ret_id
2626 AND distribution_id = h_adj_distid
2627 );
2628
2629 end if;
2630
2631 END LOOP; -- end of CRET LOOP
2632 CLOSE CRET;
2633 end if;
2634 return(true);
2635
2636 EXCEPTION
2637
2638 when fagiat_error then
2639 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
2640 return FALSE;
2641
2642 when others then
2643 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
2644 return FALSE;
2645
2646
2647 END; -- fagiat
2648
2649 /*============================================================================
2650 | NAME fagict
2651 |
2652 | FUNCTION debit the cost account by the same amount we took back then.
2653 |
2654 | History Jacob John 1/29/97 Created
2655 |
2656 |===========================================================================*/
2657
2658
2659 Function FAGICT(
2660 RET IN OUT NOCOPY fa_ret_types.ret_struct,
2661 BK IN OUT NOCOPY fa_ret_types.book_struct,
2662 cpd_ctr IN NUMBER,
2663 today IN DATE,
2664 user_id IN NUMBER
2665 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) return boolean IS
2666
2667 dr_cr varchar2(3);
2668 adj_row fa_adjust_type_pkg.fa_adj_row_struct;
2669
2670 h_retirement_id number;
2671 h_asset_id number;
2672 h_user_id number;
2673 h_today date;
2677 h_dist_id NUMBER;
2674 h_th_id_out NUMBER;
2675 h_dr_cr varchar2(3);
2676 h_adj_type varchar2(16);
2678 h_ccid NUMBER;
2679 h_cost NUMBER;
2680 h_wip_asset integer;
2681 h_ret_dist_id number;
2682 h_trx_units number;
2683 FAGICT_ERROR EXCEPTION;
2684
2685
2686 /* Added following cursor for bug 7396397
2687 */
2688 cursor c_adj is
2689 SELECT fadh.distribution_id,
2690 fadh.code_combination_id,
2691 fadh.location_id,
2692 nvl(fadh.assigned_to,-99) assigned_to,
2693 'N' retire_rec_found,
2694 0 cost,
2695 0 DEPRN_RSV,
2696 0 REVAL_RSV,
2697 0 BONUS_DEPRN_RSV,
2698 0 IMPAIRMENT_RSV,
2699 0 new_units,
2700 fadh.code_combination_id adj_ccid
2701 FROM fa_distribution_history fadh
2702 WHERE fadh.asset_id = RET.asset_id
2703 AND fadh.date_ineffective is null
2704 AND fadh.transaction_units is null
2705 order by distribution_id;
2706
2707 --Bug#8810791 - Modified to fetch adjustment type from fa_adjustments
2708 cursor c_ret is
2709 SELECT min(fadh.distribution_id) distribution_id,
2710 faadj.transaction_header_id,
2711 fadh.code_combination_id,
2712 fadh.location_id,
2713 nvl(fadh.assigned_to,-99) assigned_to,
2714 faadj.adjustment_type,
2715 decode(sign(sum(decode(faadj.debit_credit_flag,'DR',faadj.adjustment_amount,
2716 -1*faadj.adjustment_amount))),-1,'CR','DR') debit_credit_flag,
2717 abs(sum(decode(faadj.debit_credit_flag,'DR',faadj.adjustment_amount,-1*faadj.adjustment_amount)))
2718 adjustment_amount,
2719 'N' adj_rec_found,
2720 faadj.code_combination_id adj_ccid
2721 FROM fa_adjustments faadj, fa_distribution_history fadh
2722 where fadh.asset_id = RET.asset_id
2723 AND faadj.book_type_code = BK.dis_book
2724 AND faadj.asset_id = RET.asset_id
2725 and fadh.distribution_id = faadj.distribution_id
2726 and faadj.transaction_header_id = ret.th_id_in
2727 AND faadj.source_type_code = decode(RET.wip_asset, 1,
2728 'CIP RETIREMENT','RETIREMENT')
2729 AND faadj.adjustment_type in ('COST', 'CIP COST')
2730 group by
2731 faadj.transaction_header_id,
2732 fadh.code_combination_id,
2733 fadh.location_id,
2734 nvl(fadh.assigned_to,-99),
2735 faadj.adjustment_type,
2736 'N',
2737 faadj.code_combination_id
2738 order by 1,2;
2739
2740 cursor c_cost_ret is
2741 select ret.adjustment_type,
2742 abs(adjustment_amount) adjustment_amount,
2743 decode(sign(adjustment_amount),1,'DR','CR') debit_credit_flag,
2744 decode(sign(adjustment_amount),1,'CR','DR') rev_debit_credit_flag,
2745 ret.adj_ccid
2746 from
2747 (
2748 select adjustment_type,
2749 sum(decode(debit_credit_flag,'DR',adjustment_amount,-1*adjustment_amount )) adjustment_amount,
2750 faadj.code_combination_id adj_ccid
2751 from fa_adjustments faadj
2752 where faadj.asset_id = RET.asset_id
2753 and faadj.transaction_header_id = ret.th_id_in
2754 and faadj.book_type_code = ret.book
2755 and faadj.adjustment_type in ('COST', 'CIP COST')
2756 and faadj.source_type_code = decode(RET.wip_asset, 1,
2757 'CIP RETIREMENT','RETIREMENT')
2758 group by adjustment_type, faadj.code_combination_id
2759 ) ret
2760 where ret.adjustment_amount <> 0;
2761
2762 cursor c_ret_mrc IS
2763 SELECT min(fadh.distribution_id) distribution_id,
2764 faadj.transaction_header_id,
2765 fadh.code_combination_id,
2766 fadh.location_id,
2767 nvl(fadh.assigned_to,-99) assigned_to,
2768 faadj.adjustment_type,
2769 decode(sign(sum(decode(faadj.debit_credit_flag,'DR',faadj.adjustment_amount,
2770 -1*faadj.adjustment_amount))),-1,'CR','DR') debit_credit_flag,
2771 abs(sum(decode(faadj.debit_credit_flag,'DR',faadj.adjustment_amount,-1*faadj.adjustment_amount)))
2772 adjustment_amount,
2773 'N' adj_rec_found,
2774 faadj.code_combination_id adj_ccid
2775 FROM fa_mc_adjustments faadj, fa_distribution_history fadh
2776 where fadh.asset_id = RET.asset_id
2777 AND faadj.book_type_code = BK.dis_book
2778 AND faadj.asset_id = RET.asset_id
2779 and fadh.distribution_id = faadj.distribution_id
2780 and faadj.transaction_header_id = ret.th_id_in
2781 and faadj.set_of_books_id = ret.set_of_books_id
2782 AND faadj.source_type_code = decode(RET.wip_asset, 1,
2783 'CIP RETIREMENT','RETIREMENT')
2784 AND faadj.adjustment_type in ('COST', 'CIP COST')
2785 group by
2786 faadj.transaction_header_id,
2787 fadh.code_combination_id,
2788 fadh.location_id,
2789 nvl(fadh.assigned_to,-99),
2790 faadj.adjustment_type,
2791 'N',
2792 faadj.code_combination_id
2793 order by 1,2;
2794
2795 cursor c_cost_ret_mrc is
2796 select ret.adjustment_type,
2797 abs(adjustment_amount) adjustment_amount,
2798 decode(sign(adjustment_amount),1,'DR','CR') debit_credit_flag,
2802 (
2799 decode(sign(adjustment_amount),1,'CR','DR') rev_debit_credit_flag,
2800 ret.adj_ccid
2801 from
2803 select adjustment_type,
2804 sum(decode(debit_credit_flag,'DR',adjustment_amount,-1*adjustment_amount )) adjustment_amount,
2805 faadj.code_combination_id adj_ccid
2806 from fa_mc_adjustments faadj
2807 where faadj.asset_id = RET.asset_id
2808 and faadj.transaction_header_id = ret.th_id_in
2809 and faadj.book_type_code = ret.book
2810 and faadj.set_of_books_id = ret.set_of_books_id
2811 and faadj.adjustment_type in ('COST', 'CIP COST')
2812 and faadj.source_type_code = decode(RET.wip_asset, 1,
2813 'CIP RETIREMENT','RETIREMENT')
2814 group by adjustment_type, faadj.code_combination_id
2815 ) ret
2816 where ret.adjustment_amount <> 0;
2817
2818
2819 --l_tbl_adj tbl_adj;
2820 l_tbl_ret tbl_ret;
2821 l_tbl_cost_ret tbl_cost_ret;
2822 l_tbl_adj_final tbl_final_adj;
2823
2824 X_LAST_UPDATE_DATE date := sysdate;
2825 X_last_updated_by number := -1;
2826 X_last_update_login number := -1;
2827 h_temp number;
2828 l_adj_type VARCHAR2(15); --Bug#8810791
2829
2830 l_calling_fn varchar2(40) := 'FA_GAINLOSS_UND_PKG.fagict';
2831
2832 BEGIN <<FAGICT>>
2833
2834 h_cost := 0;
2835 h_today := today;
2836 h_retirement_id := ret.retirement_id;
2837 h_asset_id := ret.asset_id;
2838 h_user_id := user_id;
2839 h_wip_asset := ret.wip_asset;
2840
2841 if p_log_level_rec.statement_level then
2842 fa_debug_pkg.add
2843 (fname => l_calling_fn,
2844 element => 'Get thid_out from fa_rets',
2845 value => '', p_log_level_rec => p_log_level_rec);
2846 end if;
2847
2848 select transaction_header_id_out
2849 into h_th_id_out
2850 from fa_retirements
2851 where retirement_id = h_retirement_id;
2852
2853 /* Bug 843625 fix */
2854 if RET.wip_asset > 0 then
2855 l_adj_type := 'CIP COST'; --Bug#8810791
2856 adj_row.source_type_code := 'CIP RETIREMENT';
2857 else
2858 l_adj_type := 'COST';
2859 adj_row.source_type_code := 'RETIREMENT';
2860 end if;
2861
2862 adj_row.transaction_header_id := h_th_id_out;
2863 adj_row.asset_invoice_id := 0;
2864 adj_row.book_type_code := RET.book;
2865 adj_row.period_counter_created := cpd_ctr;
2866 adj_row.asset_id := RET.asset_id;
2867 adj_row.period_counter_adjusted := cpd_ctr;
2868 adj_row.last_update_date := today;
2869 adj_row.account := NULL;
2870 adj_row.account_type := NULL;
2871 adj_row.current_units := bk.cur_units;
2872 adj_row.selection_mode := fa_std_types.FA_AJ_SINGLE;
2873 adj_row.selection_thid := 0;
2874 adj_row.selection_retid := 0;
2875 adj_row.flush_adj_flag := TRUE;
2876 adj_row.gen_ccid_flag := FALSE;
2877 adj_row.annualized_adjustment := 0;
2878 adj_row.units_retired := 0;
2879 adj_row.leveling_flag := TRUE;
2880
2881 if p_log_level_rec.statement_level then
2882 fa_debug_pkg.add
2883 (fname => l_calling_fn,
2884 element => 'Populate PL-SQL tables',
2885 value => '', p_log_level_rec => p_log_level_rec);
2886 end if;
2887 /* Populate table only for unit retirements */
2888 if (nvl(ret.units_retired,0) > 0 ) then
2889
2890 if (ret.mrc_sob_type_code <> 'R') then
2891
2892 g_tbl_adj_cost.delete;
2893 open c_adj;
2894 fetch c_adj BULK COLLECT into g_tbl_adj_cost;
2895 close c_adj;
2896
2897 open c_ret;
2898 fetch c_ret BULK COLLECT into l_tbl_ret;
2899 close c_ret;
2900 else
2901
2902 open c_ret_mrc;
2903 fetch c_ret_mrc BULK COLLECT into l_tbl_ret;
2904 close c_ret_mrc;
2905 end if;
2906
2907 else
2908 if (ret.mrc_sob_type_code <> 'R') then
2909 open c_cost_ret;
2910 fetch c_cost_ret BULK COLLECT into l_tbl_cost_ret;
2911 close c_cost_ret;
2912 else
2913 open c_cost_ret_mrc;
2914 fetch c_cost_ret_mrc BULK COLLECT into l_tbl_cost_ret;
2915 close c_cost_ret_mrc;
2916 end if;
2917 end if;
2918
2919 if p_log_level_rec.statement_level then
2920 fa_debug_pkg.add
2921 (fname => l_calling_fn,
2922 element => 'Calling process_adj_table',
2923 value => '', p_log_level_rec => p_log_level_rec);
2924 end if;
2925 --Bug#8810791 - Passed l_adj_type as p_mode.
2926 if not process_adj_table(p_mode => l_adj_type,RET => ret,BK => bk,
2927 p_tbl_adj => g_tbl_adj_cost, p_tbl_ret => l_tbl_ret,
2928 p_tbl_cost_ret => l_tbl_cost_ret,
2929 p_tbl_adj_final => l_tbl_adj_final,
2930 p_log_level_rec => p_log_level_rec) then
2931 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
2932 return false;
2933 end if ;
2934
2935 if p_log_level_rec.statement_level then
2936 fa_debug_pkg.add
2937 (fname => l_calling_fn,
2938 element => 'process_adj_table done',
2939 value => '', p_log_level_rec => p_log_level_rec);
2940 end if;
2941
2942 for l in 1..l_tbl_adj_final.count
2943 LOOP
2944
2945 h_dist_id := l_tbl_adj_final(l).dist_id;
2946 h_ccid := l_tbl_adj_final(l).ccid;
2950
2947 h_adj_type := l_tbl_adj_final(l).adj_type;
2948 h_dr_cr := l_tbl_adj_final(l).dr_cr;
2949 h_cost := l_tbl_adj_final(l).cost;
2951 adj_row.code_combination_id := h_ccid;
2952 adj_row.adjustment_amount := h_cost;
2953 adj_row.distribution_id := h_dist_id;
2954 adj_row.debit_credit_flag := h_dr_cr;
2955 adj_row.adjustment_type := h_adj_type; -- Added. YYOON
2956 adj_row.mrc_sob_type_code := ret.mrc_sob_type_code;
2957 adj_row.set_of_books_id := ret.set_of_books_id;
2958
2959 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
2960 X_last_update_date,
2961 X_last_updated_by,
2962 X_last_update_login
2963 , p_log_level_rec => p_log_level_rec)) then
2964
2965 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
2966 return(FALSE);
2967 end if;
2968
2969 END LOOP;
2970
2971 return(TRUE);
2972
2973 EXCEPTION
2974
2975 when others then
2976 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
2977 return FALSE;
2978
2979 END; -- fagict
2980
2981 /*==========================================================================*
2982 | NAME fagiav |
2983 | |
2984 | FUNCTION Adjust the reserve that we took at retirement. That is, we |
2985 | credit the debit amount we took back then. |
2986 |
2987 | History Jacob John 1/29/97 Created
2988 | |
2989 |===========================================================================*/
2990
2991 FUNCTION FAGIAV(
2992 RET IN OUT NOCOPY fa_ret_types.ret_struct,
2993 BK IN OUT NOCOPY fa_ret_types.book_struct,
2994 cpd_ctr IN number,
2995 today IN DATE,
2996 user_id IN NUMBER
2997 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) return boolean IS
2998
2999 adj_row fa_adjust_type_pkg.fa_adj_row_struct ;
3000 dpr_row fa_std_types.fa_deprn_row_struct;
3001
3002 h_retirement_id number;
3003 h_asset_id number;
3004 h_user_id number;
3005 h_today date;
3006 h_th_id_out NUMBER;
3007 h_dr_cr VARCHAR2(3);
3008 h_adj_type VARCHAR2(16);
3009 h_book varchar2(30);
3010 h_dist_id NUMBER;
3011 h_ccid NUMBER;
3012 h_old_ccid number;
3013 h_new_ccid number;
3014 h_old_dist_id number;
3015 h_new_dist_id number;
3016 h_reval_rsv number;
3017 h_reserve number;
3018 h_bonus_reserve number;
3019 h_impairment_reserve number;
3020 h_category_id number;
3021
3022 FAGIAV_ERROR EXCEPTION;
3023
3024 X_LAST_UPDATE_DATE date := sysdate;
3025 X_last_updated_by number := -1;
3026 X_last_update_login number := -1;
3027 h_success boolean;
3028 h_temp number;
3029 l_group_thid number(15);
3030
3031 CURSOR c_get_group_thid IS
3032 SELECT transaction_header_id
3033 FROM fa_transaction_headers
3034 WHERE member_transaction_header_id = ret.th_id_in
3035 AND asset_id = bk.group_asset_id
3036 AND book_type_code = ret.book;
3037
3038
3039 CURSOR DEPRN IS
3040 SELECT dh_old.distribution_id,
3041 dh_new.distribution_id,
3042 dh_old.code_combination_id,
3043 dh_new.code_combination_id
3044 FROM fa_distribution_history dh_old,
3045 fa_distribution_history dh_new
3046 WHERE
3047 dh_old.retirement_id = h_retirement_id and
3048 dh_old.book_type_code = RET.book and
3049 dh_old.units_assigned + dh_old.transaction_units <> 0
3050 AND
3051 dh_new.transaction_header_id_in=
3052 dh_old.transaction_header_id_out and
3053 dh_new.location_id = dh_old.location_id and
3054 nvl(dh_new.assigned_to,-99) = nvl(dh_old.assigned_to,-99) and
3055 dh_new.code_combination_id = dh_old.code_combination_id;
3056
3057
3058 /* Bug 7396397 starts */
3059
3060 cursor c_adj is
3061 SELECT fadh.distribution_id,
3062 fadh.code_combination_id,
3063 fadh.location_id,
3064 nvl(fadh.assigned_to,-99) assigned_to,
3065 'N' retire_rec_found,
3066 0 cost,
3067 0 DEPRN_RSV,
3068 0 REVAL_RSV,
3069 0 BONUS_DEPRN_RSV,
3070 0 IMPAIRMENT_RSV,
3071 0 new_units,
3072 fadh.code_combination_id adj_ccid
3073 FROM fa_distribution_history fadh
3074 WHERE fadh.asset_id = RET.asset_id
3075 AND fadh.date_ineffective is null
3076 AND fadh.transaction_units is null
3077 order by distribution_id;
3078
3079 cursor c_adj_mrc is
3080 SELECT fadh.distribution_id,
3081 fadh.code_combination_id,
3082 fadh.location_id,
3083 nvl(fadh.assigned_to,-99) assigned_to,
3084 'N' retire_rec_found,
3088 0 BONUS_DEPRN_RSV,
3085 0 cost,
3086 0 DEPRN_RSV,
3087 0 REVAL_RSV,
3089 0 IMPAIRMENT_RSV,
3090 0 new_units,
3091 fadh.code_combination_id adj_ccid
3092 FROM fa_distribution_history fadh
3093 WHERE fadh.asset_id = RET.asset_id
3094 AND fadh.TRANSACTION_HEADER_ID_OUT
3095 = (select rt.transaction_header_id_out
3096 from fa_retirements rt
3097 where rt.retirement_id = RET.retirement_id
3098 )
3099 order by distribution_id;
3100
3101 cursor c_ret is
3102 SELECT min(fadh.distribution_id) distribution_id,
3103 faadj.transaction_header_id,
3104 fadh.code_combination_id,
3105 fadh.location_id,
3106 nvl(fadh.assigned_to,-99) assigned_to,
3107 faadj.adjustment_type,
3108 decode(sign(sum(decode(faadj.debit_credit_flag,'DR',faadj.adjustment_amount,
3109 -1*faadj.adjustment_amount))),-1,'CR','DR') debit_credit_flag,
3110 abs(sum(decode(faadj.debit_credit_flag,'DR',faadj.adjustment_amount,-1*faadj.adjustment_amount)))
3111 adjustment_amount,
3112 'N' adj_rec_found,
3113 faadj.code_combination_id adj_ccid
3114 FROM fa_adjustments faadj, fa_distribution_history fadh
3115 where fadh.asset_id = RET.asset_id
3116 AND faadj.book_type_code = BK.dis_book
3117 AND faadj.asset_id = RET.asset_id
3118 and fadh.distribution_id = faadj.distribution_id
3119 and faadj.transaction_header_id = ret.th_id_in
3120 and faadj.adjustment_type in ('RESERVE', 'BONUS RESERVE', 'REVAL RESERVE', 'IMPAIR RESERVE')
3121 group by
3122 faadj.transaction_header_id,
3123 fadh.code_combination_id,
3124 fadh.location_id,
3125 nvl(fadh.assigned_to,-99),
3126 faadj.adjustment_type,
3127 'N',
3128 faadj.code_combination_id
3129 order by 1,2;
3130
3131
3132 cursor c_cost_ret is
3133 select cost_ret.adjustment_type,
3134 abs(cost_ret.adjustment_amount) adjustment_amount,
3135 decode(sign(cost_ret.adjustment_amount),1,'DR','CR') debit_credit_flag,
3136 decode(sign(cost_ret.adjustment_amount),1,'CR','DR') rev_debit_credit_flag,
3137 cost_ret.adj_ccid
3138 from
3139 (
3140 select adjustment_type,
3141 sum(decode(debit_credit_flag,'DR',adjustment_amount,-1*adjustment_amount )) adjustment_amount,
3142 faadj.code_combination_id adj_ccid
3143 from fa_adjustments faadj
3144 where ( (faadj.transaction_header_id = ret.th_id_in AND faadj.asset_id = h_asset_id)
3145 )
3146 and faadj.book_type_code = ret.book
3147 and faadj.adjustment_type in ('RESERVE', 'BONUS RESERVE', 'REVAL RESERVE', 'IMPAIR RESERVE')
3148 group by adjustment_type, faadj.code_combination_id
3149 ) cost_ret
3150 where cost_ret.adjustment_amount <> 0;
3151
3152
3153 cursor c_cost_ret_grp is
3154 select cost_ret.adjustment_type,
3155 abs(cost_ret.adjustment_amount) adjustment_amount,
3156 decode(sign(cost_ret.adjustment_amount),1,'DR','CR') debit_credit_flag,
3157 decode(sign(cost_ret.adjustment_amount),1,'CR','DR') rev_debit_credit_flag,
3158 cost_ret.adj_ccid
3159 from
3160 (
3161 select adjustment_type,
3162 sum(decode(debit_credit_flag,'DR',adjustment_amount,-1*adjustment_amount )) adjustment_amount,
3163 faadj.code_combination_id adj_ccid
3164 from fa_adjustments faadj
3165 where (
3166 (faadj.transaction_header_id = l_group_thid AND faadj.asset_id = bk.group_asset_id)
3167 )
3168 and faadj.book_type_code = ret.book
3169 and faadj.adjustment_type in ('RESERVE', 'BONUS RESERVE', 'REVAL RESERVE', 'IMPAIR RESERVE')
3170 group by adjustment_type, faadj.code_combination_id
3171 ) cost_ret
3172 where cost_ret.adjustment_amount <> 0;
3173
3174 cursor c_ret_mrc IS
3175 SELECT min(fadh.distribution_id) distribution_id,
3176 faadj.transaction_header_id,
3177 fadh.code_combination_id,
3178 fadh.location_id,
3179 nvl(fadh.assigned_to,-99) assigned_to,
3180 faadj.adjustment_type,
3181 decode(sign(sum(decode(faadj.debit_credit_flag,'DR',faadj.adjustment_amount,
3182 -1*faadj.adjustment_amount))),-1,'CR','DR') debit_credit_flag,
3183 abs(sum(decode(faadj.debit_credit_flag,'DR',faadj.adjustment_amount,-1*faadj.adjustment_amount)))
3184 adjustment_amount,
3185 'N' adj_rec_found,
3186 faadj.code_combination_id adj_ccid
3187 FROM fa_mc_adjustments faadj, fa_distribution_history fadh
3188 where fadh.asset_id = RET.asset_id
3189 AND faadj.book_type_code = BK.dis_book
3190 AND faadj.asset_id = RET.asset_id
3191 and fadh.distribution_id = faadj.distribution_id
3192 and faadj.transaction_header_id = ret.th_id_in
3193 and faadj.set_of_books_id = ret.set_of_books_id
3194 and faadj.adjustment_type in ('RESERVE', 'BONUS RESERVE', 'REVAL RESERVE', 'IMPAIR RESERVE')
3195 group by
3196 faadj.transaction_header_id,
3197 fadh.code_combination_id,
3198 fadh.location_id,
3199 nvl(fadh.assigned_to,-99),
3203 order by 1,2;
3200 faadj.adjustment_type,
3201 'N',
3202 faadj.code_combination_id
3204
3205 cursor c_cost_ret_mrc is
3206 select cost_ret.adjustment_type,
3207 abs(cost_ret.adjustment_amount) adjustment_amount,
3208 decode(sign(cost_ret.adjustment_amount),1,'DR','CR') debit_credit_flag,
3209 decode(sign(cost_ret.adjustment_amount),1,'CR','DR') rev_debit_credit_flag,
3210 cost_ret.adj_ccid
3211 from
3212 (
3213 select adjustment_type,
3214 sum(decode(debit_credit_flag,'DR',adjustment_amount,-1*adjustment_amount )) adjustment_amount,
3215 faadj.code_combination_id adj_ccid
3216 from fa_mc_adjustments faadj
3217 where ( (faadj.transaction_header_id = ret.th_id_in AND faadj.asset_id = h_asset_id)
3218 )
3219 and faadj.book_type_code = ret.book
3220 and faadj.set_of_books_id = ret.set_of_books_id
3221 and faadj.adjustment_type in ('RESERVE', 'BONUS RESERVE', 'REVAL RESERVE', 'IMPAIR RESERVE')
3222 group by faadj.adjustment_type, faadj.code_combination_id
3223 ) cost_ret
3224 where cost_ret.adjustment_amount <> 0;
3225
3226 cursor c_cost_ret_grp_mrc is
3227 select cost_ret.adjustment_type,
3228 abs(cost_ret.adjustment_amount) adjustment_amount,
3229 decode(sign(cost_ret.adjustment_amount),1,'DR','CR') debit_credit_flag,
3230 decode(sign(cost_ret.adjustment_amount),1,'CR','DR') rev_debit_credit_flag,
3231 cost_ret.adj_ccid
3232 from
3233 (
3234 select adjustment_type,
3235 sum(decode(debit_credit_flag,'DR',adjustment_amount,-1*adjustment_amount )) adjustment_amount,
3236 faadj.code_combination_id adj_ccid
3237 from fa_mc_adjustments faadj
3238 where (
3239 (faadj.transaction_header_id = l_group_thid AND faadj.asset_id = bk.group_asset_id)
3240 )
3241 and faadj.book_type_code = ret.book
3242 and faadj.set_of_books_id = ret.set_of_books_id
3243 and faadj.adjustment_type in ('RESERVE', 'BONUS RESERVE', 'REVAL RESERVE', 'IMPAIR RESERVE')
3244 group by faadj.adjustment_type, faadj.code_combination_id
3245 ) cost_ret
3246 where cost_ret.adjustment_amount <> 0;
3247
3248 l_tbl_adj tbl_adj;
3249 l_tbl_ret tbl_ret;
3250 l_tbl_cost_ret tbl_cost_ret;
3251 l_tbl_adj_final tbl_final_adj;
3252
3253 /* Bug ends 7396397 */
3254
3255 l_calling_fn varchar2(40) := 'FA_GAINLOSS_UND_PKG.fagiav';
3256
3257 BEGIN <<FAGIAV>>
3258
3259 h_reserve := 0;
3260 h_today := today;
3261 h_retirement_id := ret.retirement_id;
3262 h_asset_id := ret.asset_id;
3263 h_user_id := user_id;
3264 h_book := ret.book;
3265
3266 if p_log_level_rec.statement_level then
3267 fa_debug_pkg.add
3268 (fname => l_calling_fn,
3269 element => 'Get thid_out from fa_rets',
3270 value => '', p_log_level_rec => p_log_level_rec);
3271 end if;
3272
3273 begin
3274 select transaction_header_id_out
3275 into h_th_id_out
3276 from fa_retirements
3277 where retirement_id = h_retirement_id;
3278 EXCEPTION
3279 when no_data_found then
3280 raise fagiav_error;
3281 end;
3282
3283 adj_row.transaction_header_id := h_th_id_out;
3284 adj_row.asset_invoice_id := 0;
3285 if ret.wip_asset > 0 then /*12768930 */
3286 adj_row.source_type_code := 'CIP RETIREMENT';
3287 else
3288 adj_row.source_type_code := 'RETIREMENT';
3289 end if;
3290 adj_row.book_type_code := RET.book;
3291 adj_row.period_counter_created := cpd_ctr;
3292 adj_row.asset_id := RET.asset_id;
3293 adj_row.period_counter_adjusted := cpd_ctr;
3294 adj_row.last_update_date := today;
3295 /* BUG# 2635084: bk.cur_units shouldn't be set to null
3296 becase it is being used by faxinaj for reinstatement.
3297 BK.cur_units := NULL;
3298 */
3299 adj_row.current_units := NULL;
3300 adj_row.selection_mode := fa_std_types.FA_AJ_SINGLE;
3301 adj_row.selection_thid := 0;
3302 adj_row.selection_retid := 0;
3303 adj_row.flush_adj_flag := TRUE;
3304 adj_row.annualized_adjustment := 0;
3305 adj_row.units_retired := 0;
3306 adj_row.leveling_flag := TRUE;
3307
3308 /*
3309 * Transfer reserve accumulated between time of retirement
3310 * and reinstatement. Find pairs of distributions (before and
3311 * after the retirement), and move the amount of reserve that
3312 * will clear the distribution created by the retirement. This
3313 * amount turns out to be the difference between the current
3314 * reserve and reserve adjustment amount created by the retirement.
3315 */
3316
3317 if p_log_level_rec.statement_level then
3318 fa_debug_pkg.add
3319 (fname => l_calling_fn,
3320 element => 'Get category from fa_asset_history',
3321 value => '', p_log_level_rec => p_log_level_rec);
3322 end if;
3323
3324 SELECT category_id
3325 INTO h_category_id
3326 FROM fa_asset_history
3327 WHERE asset_id = RET.asset_id
3328 AND date_ineffective is null;
3329
3330 adj_row.account := NULL;
3331 adj_row.account_type := NULL;
3332 adj_row.gen_ccid_flag := FALSE;
3336 (fname => l_calling_fn,
3333
3334 if p_log_level_rec.statement_level then
3335 fa_debug_pkg.add
3337 element => 'Get deprn_reserve info',
3338 value => '', p_log_level_rec => p_log_level_rec);
3339 end if;
3340
3341 if (bk.group_asset_id is not null) then
3342 OPEN c_get_group_thid;
3343 FETCH c_get_group_thid INTO l_group_thid;
3344 CLOSE c_get_group_thid;
3345 end if;
3346
3347
3348 /*
3349 Checks if retirement is a partial unit retirement and
3350 asset is not a member of group. Except COST, all entries
3351 are inserted in fa_adjustments for a group when member asset
3352 is retired
3353 */
3354 if p_log_level_rec.statement_level then
3355 fa_debug_pkg.add
3356 (fname => l_calling_fn,
3357 element => 'Populate PL-SQL tables',
3358 value => '', p_log_level_rec => p_log_level_rec);
3359 end if;
3360
3361 if (nvl(ret.units_retired,0) > 0 ) then
3362 if (ret.mrc_sob_type_code <> 'R') then
3363 g_tbl_adj_rsv.delete;
3364 open c_adj;
3365 fetch c_adj BULK COLLECT into g_tbl_adj_rsv;
3366 close c_adj;
3367
3368 open c_ret;
3369 fetch c_ret BULK COLLECT into l_tbl_ret;
3370 close c_ret;
3371 else
3372 open c_ret_mrc;
3373 fetch c_ret_mrc BULK COLLECT into l_tbl_ret;
3374 close c_ret_mrc;
3375 end if;
3376
3377 else
3378 if (ret.mrc_sob_type_code <> 'R') then
3379 open c_cost_ret;
3380 fetch c_cost_ret BULK COLLECT into l_tbl_cost_ret;
3381 close c_cost_ret;
3382 else
3383 open c_cost_ret_mrc;
3384 fetch c_cost_ret_mrc BULK COLLECT into l_tbl_cost_ret;
3385 close c_cost_ret_mrc;
3386 end if; -- mrc
3387 end if; -- partial_unit retirement
3388
3389 if p_log_level_rec.statement_level then
3390 fa_debug_pkg.add
3391 (fname => l_calling_fn,
3392 element => 'Before calling process_adj_table',
3393 value => '', p_log_level_rec => p_log_level_rec);
3394 end if;
3395
3396 if not process_adj_table(p_mode=> 'RESERVE',RET => ret,BK => bk,
3397 p_tbl_adj => g_tbl_adj_rsv, p_tbl_ret => l_tbl_ret,
3398 p_tbl_cost_ret => l_tbl_cost_ret,
3399 p_tbl_adj_final => l_tbl_adj_final,
3400 p_log_level_rec => p_log_level_rec) then
3401 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
3402 return false;
3403 end if ;
3404
3405 if p_log_level_rec.statement_level then
3406 fa_debug_pkg.add
3407 (fname => l_calling_fn,
3408 element => 'After calling process_adj_table for non group',
3409 value => '', p_log_level_rec => p_log_level_rec);
3410 end if;
3411
3412 -- Now process for groups
3413 if (bk.group_asset_id is not null) then
3414 l_tbl_adj.delete;
3415 l_tbl_ret.delete;
3416 l_tbl_cost_ret.delete;
3417 if (ret.mrc_sob_type_code <> 'R') then
3418 open c_cost_ret_grp;
3419 fetch c_cost_ret_grp BULK COLLECT into l_tbl_cost_ret;
3420 close c_cost_ret_grp;
3421 else
3422 open c_cost_ret_grp_mrc;
3423 fetch c_cost_ret_grp_mrc BULK COLLECT into l_tbl_cost_ret;
3424 close c_cost_ret_grp_mrc;
3425 end if; -- mrc
3426
3427 if not process_adj_table(p_mode=> 'GROUP',RET => ret,BK => bk,
3428 p_tbl_adj => l_tbl_adj, p_tbl_ret => l_tbl_ret,
3429 p_tbl_cost_ret => l_tbl_cost_ret,
3430 p_tbl_adj_final => l_tbl_adj_final,
3431 p_log_level_rec => p_log_level_rec) then
3432 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
3433 return false;
3434 end if ;
3435
3436 end if;
3437
3438 if p_log_level_rec.statement_level then
3439 fa_debug_pkg.add
3440 (fname => l_calling_fn,
3441 element => 'After calling process_adj_table for group',
3442 value => '', p_log_level_rec => p_log_level_rec);
3443 end if;
3444
3445 for l in 1..l_tbl_adj_final.count
3446 LOOP
3447
3448 h_dist_id := l_tbl_adj_final(l).dist_id;
3449 h_ccid := l_tbl_adj_final(l).ccid;
3450 h_adj_type := l_tbl_adj_final(l).adj_type;
3451 h_dr_cr := l_tbl_adj_final(l).dr_cr;
3452 h_reserve := l_tbl_adj_final(l).cost;
3453
3454 adj_row.asset_id := l_tbl_adj_final(l).asset_id;
3455 adj_row.code_combination_id := h_ccid;
3456 adj_row.adjustment_amount := h_reserve;
3457 adj_row.distribution_id := h_dist_id;
3458 adj_row.debit_credit_flag := h_dr_cr;
3459 adj_row.adjustment_type := h_adj_type;
3460 adj_row.mrc_sob_type_code := ret.mrc_sob_type_code;
3461 adj_row.set_of_books_id := ret.set_of_books_id;
3462
3463 if (bk.group_asset_id is not null) and
3464 (bk.group_asset_id <> adj_row.asset_id) and
3465 (nvl(bk.member_rollup_flag, 'N') = 'N') then
3466 adj_row.track_member_flag := 'Y';
3467 else
3468 adj_row.track_member_flag := null;
3469 end if;
3470
3471 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
3472 X_last_update_date,
3476
3473 X_last_updated_by,
3474 X_last_update_login
3475 , p_log_level_rec => p_log_level_rec)) then
3477 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
3478 return(FALSE);
3479
3480 end if;
3481
3482 END LOOP;
3483
3484
3485 return(TRUE);
3486
3487 EXCEPTION
3488
3489 when others then
3490 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
3491 return FALSE;
3492
3493 END; -- fagiav
3494
3495 /*======================================================================*
3496 | NAME faraje |
3497 | |
3498 | FUNCTION Adjust the deprn expense, reval expense, and reval_amort |
3499 | we took at the time of the retirement. |
3500 | |
3501 | History Jacob John 1/29/97 Created
3502 |=======================================================================*/
3503
3504 Function FARAJE(
3505 RET IN OUT NOCOPY fa_ret_types.ret_struct,
3506 BK IN OUT NOCOPY fa_ret_types.book_struct,
3507 expense_amount IN NUMBER,
3508 adj_type IN VARCHAR2,
3509 cpd_ctr IN NUMBER,
3510 today IN date,
3511 user_id IN NUMBER
3512 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) return boolean IS
3513
3514 adj_row fa_adjust_type_pkg.fa_adj_row_struct ;
3515
3516 h_expense_amount number;
3517 h_asset_id number;
3518 h_category_id number;
3519 h_user_id number;
3520 h_th_id_in number;
3521 h_retirement_id number;
3522 h_cur_units number;
3523 h_dist_book number;
3524 h_book varchar2(30);
3525 h_today date;
3526 h_adj_type varchar2(15);
3527 h_cpd_ctr number;
3528 h_th_id_out NUMBER;
3529 h_dist_id NUMBER;
3530 h_ccid NUMBER;
3531 h_exp_adj_amount NUMBER;
3532 h_cost_frac Number;
3533
3534 h_exp_adj_amount_sorp NUMBER; -- Bug 6666666
3535
3536
3537 FARAJE_ERROR EXCEPTION;
3538
3539 X_LAST_UPDATE_DATE date := sysdate;
3540 X_last_updated_by number := -1;
3541 X_last_update_login number := -1;
3542
3543 CURSOR DIST_DEPRN1 is
3544 SELECT dh.distribution_id,
3545 dh.code_combination_id,
3546 (dh.units_assigned / ah.units)
3547 * expense_amount -
3548 nvl(adj.adjustment_amount, 0),
3549 (dh.units_assigned / ah.units)
3550 * expense_amount --Bug 6666666
3551 FROM
3552 FA_DISTRIBUTION_HISTORY dh,
3553 FA_ASSET_HISTORY ah,
3554 FA_ADJUSTMENTS adj
3555 WHERE dh.asset_id = RET.asset_id
3556 AND dh.book_type_code = BK.dis_book
3557 AND dh.date_ineffective is null
3558 AND dh.distribution_id = adj.distribution_id(+)
3559 AND ah.asset_id = RET.asset_id
3560 AND ah.date_ineffective is null
3561 AND adj.transaction_header_id(+) = RET.th_id_in
3562 AND adj.source_type_code(+) = 'RETIREMENT'
3563 AND adj.adjustment_type(+) = adj_type
3564 AND adj.debit_credit_flag(+) = 'DR'
3565 AND adj.asset_id(+) = RET.asset_id
3566 AND adj.book_type_code(+) = RET.book
3567 union all
3568 SELECT dh.distribution_id,
3569 dh.code_combination_id,
3570 ((1/(1-h_cost_frac))-1) * nvl(adj.adjustment_amount, 0),
3571 0 -- Bug 6666666
3572 FROM FA_DISTRIBUTION_HISTORY dh
3573 ,FA_ASSET_HISTORY ah
3574 ,FA_TRANSACTION_HEADERS ret_th
3575 ,FA_ADJUSTMENTS adj
3576 ,FA_TRANSACTION_HEADERS exp_th
3577 WHERE dh.asset_id = RET.asset_id
3578 AND dh.book_type_code = BK.dis_book
3579 AND dh.date_ineffective is null
3580 AND dh.distribution_id = adj.distribution_id
3581 AND ah.asset_id = RET.asset_id
3582 AND ah.date_ineffective is null
3583 AND ret_th.transaction_header_id = RET.th_id_in
3584 AND ret_th.asset_id = dh.asset_id
3585 AND ret_th.book_type_code = RET.book
3586 AND ret_th.transaction_type_code like '%RETIREMENT'
3587 AND adj.transaction_header_id >= RET.th_id_in
3588 AND adj.transaction_header_id <= h_th_id_out
3589 AND adj.source_type_code = 'DEPRECIATION'
3590 AND adj.adjustment_type = adj_type
3591 AND adj.debit_credit_flag = 'DR'
3592 AND adj.asset_id = RET.asset_id
3593 AND adj.book_type_code = RET.book
3594 AND exp_th.transaction_header_id = adj.transaction_header_id
3595 AND exp_th.asset_id = adj.asset_id
3596 AND exp_th.book_type_code = adj.book_type_code
3597 AND exp_th.transaction_subtype = 'EXPENSED'
3598 ;
3599
3600 CURSOR MRC_DIST_DEPRN1 is
3601 SELECT dh.distribution_id,
3602 dh.code_combination_id,
3603 (dh.units_assigned / ah.units)
3604 * expense_amount -
3608 FROM
3605 nvl(adj.adjustment_amount, 0),
3606 (dh.units_assigned / ah.units)
3607 * expense_amount -- Bug 6666666
3609 FA_DISTRIBUTION_HISTORY dh,
3610 FA_ASSET_HISTORY ah,
3611 FA_MC_ADJUSTMENTS adj
3612 WHERE dh.asset_id = RET.asset_id
3613 AND dh.book_type_code = BK.dis_book
3614 AND dh.date_ineffective is null
3615 AND dh.distribution_id = adj.distribution_id(+)
3616 AND ah.asset_id = RET.asset_id
3617 AND ah.date_ineffective is null
3618 AND adj.transaction_header_id(+) = RET.th_id_in
3619 AND adj.source_type_code(+) = 'RETIREMENT'
3620 AND adj.adjustment_type(+) = adj_type
3621 AND adj.debit_credit_flag(+) = 'DR'
3622 AND adj.set_of_books_id(+) = ret.set_of_books_id --Bug#8761988
3623 AND adj.asset_id(+) = RET.asset_id
3624 AND adj.book_type_code(+) = RET.book
3625 union all
3626 SELECT dh.distribution_id,
3627 dh.code_combination_id,
3628 ((1/(1-h_cost_frac))-1) * nvl(adj.adjustment_amount, 0),
3629 0 -- Bug 6666666
3630 FROM
3631 FA_DISTRIBUTION_HISTORY dh,
3632 FA_ASSET_HISTORY ah,
3633 FA_MC_ADJUSTMENTS adj -- bug#5094783 fix
3634 WHERE dh.asset_id = RET.asset_id
3635 AND dh.book_type_code = BK.dis_book
3636 AND dh.date_ineffective is null
3637 AND dh.distribution_id = adj.distribution_id(+)
3638 AND ah.asset_id = RET.asset_id
3639 AND ah.date_ineffective is null
3640 AND adj.transaction_header_id(+) >= RET.th_id_in
3641 AND adj.transaction_header_id(+) <= h_th_id_out
3642 AND adj.source_type_code(+) = 'DEPRECIATION'
3643 AND adj.adjustment_type(+) = adj_type
3644 AND adj.debit_credit_flag(+) = 'DR'
3645 AND adj.set_of_books_id(+) = RET.set_of_books_id
3646 AND adj.asset_id(+) = RET.asset_id
3647 AND adj.book_type_code(+) = RET.book;
3648
3649 CURSOR DIST_DEPRN2 IS
3650 SELECT
3651 faadj.distribution_id,
3652 faadj.code_combination_id,
3653 - 1 * faadj.adjustment_amount
3654 FROM
3655 fa_distribution_history fadh,
3656 fa_adjustments faadj
3657 WHERE fadh.asset_id(+) = RET.asset_id
3658 AND fadh.book_type_code(+) = BK.dis_book
3659 AND fadh.date_ineffective(+) is null
3660 AND fadh.distribution_id(+) = faadj.distribution_id
3661 AND fadh.distribution_id is null
3662 AND faadj.transaction_header_id = RET.th_id_in
3663 AND faadj.source_type_code = 'RETIREMENT'
3664 AND faadj.adjustment_type = adj_type
3665 AND faadj.debit_credit_flag = 'DR'
3666 AND faadj.asset_id = RET.asset_id
3667 AND faadj.book_type_code = RET.book;
3668
3669 CURSOR MRC_DIST_DEPRN2 IS
3670 SELECT
3671 faadj.distribution_id,
3672 faadj.code_combination_id,
3673 - 1 * faadj.adjustment_amount
3674 FROM
3675 fa_distribution_history fadh,
3676 fa_mc_adjustments faadj
3677 WHERE fadh.asset_id(+) = RET.asset_id
3678 AND fadh.book_type_code(+) = BK.dis_book
3679 AND fadh.date_ineffective(+) is null
3680 AND fadh.distribution_id(+) = faadj.distribution_id
3681 AND fadh.distribution_id is null
3682 AND faadj.transaction_header_id = RET.th_id_in
3683 AND faadj.source_type_code = 'RETIREMENT'
3684 AND faadj.adjustment_type = adj_type
3685 AND faadj.debit_credit_flag = 'DR'
3686 AND faadj.asset_id = RET.asset_id
3687 AND faadj.set_of_books_id = RET.set_of_books_id
3688 AND faadj.book_type_code = RET.book;
3689
3690
3691 CURSOR DIST_DEPRN3 IS
3692 SELECT
3693 fadh.distribution_id,
3694 fadh.code_combination_id,
3695 (ABS(fadh.transaction_units) / faret.units)
3696 * expense_amount -
3697 NVL(faadj.adjustment_amount, 0),
3698 (ABS(fadh.transaction_units) / faret.units)
3699 * expense_amount -- Bug 6666666
3700 FROM FA_RETIREMENTS faret, fa_distribution_history fadh,
3701 fa_adjustments faadj
3702 WHERE fadh.asset_id = RET.asset_id
3703 AND fadh.book_type_code = BK.dis_book
3704 AND fadh.retirement_id = RET.retirement_id
3705 AND fadh.distribution_id = faadj.distribution_id(+)
3706 AND faadj.transaction_header_id(+) = RET.th_id_in
3707 AND faadj.source_type_code(+) = 'RETIREMENT'
3708 AND faadj.adjustment_type(+) = adj_type
3709 AND faadj.debit_credit_flag(+) = 'DR'
3710 AND faadj.asset_id(+) = RET.asset_id
3711 AND faadj.book_type_code(+) = RET.book
3712 AND faret.retirement_id = RET.retirement_id;
3713
3714 CURSOR MRC_DIST_DEPRN3 IS
3715 SELECT
3716 fadh.distribution_id,
3717 fadh.code_combination_id,
3718 (ABS(fadh.transaction_units) / faret.units)
3719 * expense_amount -
3720 NVL(faadj.adjustment_amount, 0),
3721 (ABS(fadh.transaction_units) / faret.units)
3722 * expense_amount -- Bug 6666666
3723 FROM FA_MC_RETIREMENTS faret, fa_distribution_history fadh,
3727 AND fadh.retirement_id = RET.retirement_id
3724 fa_mc_adjustments faadj
3725 WHERE fadh.asset_id = RET.asset_id
3726 AND fadh.book_type_code = BK.dis_book
3728 AND fadh.distribution_id = faadj.distribution_id(+)
3729 AND faadj.transaction_header_id(+) = RET.th_id_in
3730 AND faadj.source_type_code(+) = 'RETIREMENT'
3731 AND faadj.adjustment_type(+) = adj_type
3732 AND faadj.debit_credit_flag(+) = 'DR'
3733 AND faadj.asset_id(+) = RET.asset_id
3734 AND faadj.book_type_code(+) = RET.book
3735 AND faadj.set_of_books_id(+) = RET.set_of_books_id
3736 AND faret.set_of_books_id = RET.set_of_books_id
3737 AND faret.retirement_id = RET.retirement_id;
3738
3739 CURSOR DIST_DEPRN4 IS
3740 SELECT
3741 faadj.distribution_id,
3742 faadj.code_combination_id,
3743 - 1 * faadj.adjustment_amount
3744 FROM
3745 FA_RETIREMENTS faret,
3746 FA_DISTRIBUTION_HISTORY fadh,
3747 FA_ADJUSTMENTS faadj
3748 WHERE fadh.asset_id(+) = RET.asset_id
3749 AND fadh.book_type_code(+) = BK.dis_book
3750 AND fadh.retirement_id(+) = RET.retirement_id
3751 AND fadh.distribution_id(+) = faadj.distribution_id
3752 AND fadh.distribution_id is null
3753 AND faadj.transaction_header_id = faret.transaction_header_id_in
3754 AND faadj.source_type_code = 'RETIREMENT'
3755 AND faadj.adjustment_type = adj_type
3756 AND faadj.debit_credit_flag = 'DR'
3757 AND faadj.asset_id = RET.asset_id
3758 AND faadj.book_type_code = RET.book
3759 AND faret.retirement_id = RET.retirement_id;
3760
3761 CURSOR MRC_DIST_DEPRN4 IS
3762 SELECT
3763 faadj.distribution_id,
3764 faadj.code_combination_id,
3765 - 1 * faadj.adjustment_amount
3766 FROM
3767 FA_MC_RETIREMENTS faret,
3768 FA_DISTRIBUTION_HISTORY fadh,
3769 FA_MC_ADJUSTMENTS faadj
3770 WHERE fadh.asset_id(+) = RET.asset_id
3771 AND fadh.book_type_code(+) = BK.dis_book
3772 AND fadh.retirement_id(+) = RET.retirement_id
3773 AND fadh.distribution_id(+) = faadj.distribution_id
3774 AND fadh.distribution_id is null
3775 AND faadj.transaction_header_id = faret.transaction_header_id_in
3776 AND faadj.source_type_code = 'RETIREMENT'
3777 AND faadj.adjustment_type = adj_type
3778 AND faadj.debit_credit_flag = 'DR'
3779 AND faadj.set_of_books_id = RET.set_of_books_id
3780 AND faadj.asset_id = RET.asset_id
3781 AND faadj.book_type_code = RET.book
3782 AND faret.set_of_books_id = RET.set_of_books_id
3783 AND faret.retirement_id = RET.retirement_id;
3784
3785 l_calling_fn varchar2(40) := 'FA_GAINLOSS_UND_PKG.faraje';
3786
3787 BEGIN <<FARAJE>>
3788
3789 h_expense_amount := 0;
3790 h_exp_adj_amount := 0;
3791 h_expense_amount := expense_amount;
3792 h_asset_id := ret.asset_id;
3793 h_user_id := user_id;
3794 h_retirement_id := ret.retirement_id;
3795 h_th_id_in := ret.th_id_in;
3796 h_cur_units := bk.cur_units;
3797 h_cpd_ctr := cpd_ctr;
3798 h_today := today;
3799 h_book := ret.book;
3800
3801 if p_log_level_rec.statement_level then
3802 fa_debug_pkg.add
3803 (fname => l_calling_fn,
3804 element => 'Get thid_out from fa_rets',
3805 value => '', p_log_level_rec => p_log_level_rec);
3806 end if;
3807
3808 select transaction_header_id_out
3809 into h_th_id_out
3810 from fa_retirements
3811 where retirement_id = h_retirement_id;
3812
3813 -- bug#5094783 fix added ((ret.cost_retired / bk.current_cost) =1 )
3814 if ( bk.current_cost is NULL or bk.current_cost <= 0 or ((ret.cost_retired / bk.current_cost) =1 )) then
3815 h_cost_frac := 0;
3816 else
3817 h_cost_frac := ret.cost_retired / bk.current_cost;
3818 end if;
3819
3820
3821 --The following statement will insert rows into FA_ADJUSTMENTS for
3822 -- each active current distribution. Notice, that we still need to insert
3823 -- records for which distribution-id is in FA_ADJUSTMENTS but not in
3824 -- active distributions (e.g: Transfer occured before retirement)
3825
3826 adj_row.transaction_header_id := h_th_id_out;
3827 adj_row.asset_invoice_id := 0;
3828 adj_row.source_type_code := 'RETIREMENT';
3829 adj_row.book_type_code := RET.book;
3830 adj_row.period_counter_created := cpd_ctr;
3831 adj_row.asset_id := RET.asset_id;
3832 adj_row.period_counter_adjusted := cpd_ctr;
3833 adj_row.last_update_date := today;
3834 adj_row.account := NULL;
3835 adj_row.account_type := NULL;
3836 adj_row.current_units := BK.cur_units;
3837 adj_row.selection_mode := fa_std_types.FA_AJ_SINGLE;
3838 adj_row.selection_thid := 0;
3839 adj_row.selection_retid := 0;
3840 adj_row.flush_adj_flag := TRUE;
3841 adj_row.gen_ccid_flag := FALSE;
3842 adj_row.annualized_adjustment := 0;
3843 adj_row.units_retired := 0;
3844 adj_row.leveling_flag := TRUE;
3845 --Bug11886090: Need aj entry to be flaged if it is for tracked member
3846 if bk.group_asset_id is not null and
3847 nvl(bk.member_rollup_flag, 'N') = 'N' then
3848 adj_row.track_member_flag := 'Y';
3849 else
3850 adj_row.track_member_flag := null;
3851 end if;
3852
3853 -- HH
3854 -- BUG 3630399
3858 --
3855 -- Need to call cache routines to avoid null segment as
3856 -- reported in above bug. Cache was not called anywhere before
3857 -- assignment below.
3859
3860 -- This could be done using api types, but doing direct select since
3861 -- old structs are kept here.
3862
3863 SELECT category_id
3864 INTO h_category_id
3865 FROM fa_asset_history
3866 WHERE asset_id = RET.asset_id
3867 AND date_ineffective is null;
3868
3869 if not fa_cache_pkg.fazccb(
3870 X_book => RET.book,
3871 X_cat_id => h_category_id, p_log_level_rec => p_log_level_rec) then
3872 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
3873 return(FALSE);
3874 end if;
3875 -- end HH.
3876
3877 if adj_type = 'EXPENSE' then
3878
3879 -- BUG# 2314015
3880 -- allowing for account generation of the expense account
3881 -- otherwise the account may not match what was charged in
3882 -- the retirements. DEPRN1 and 3 cursors will call faxinaj
3883 -- with gen_ccid = true. 2 and 4 will call with false since
3884 -- they look for rows where the distribution doesn't exist.
3885 -- other struct members that change are account and account type
3886 -- bridgway
3887 --
3888
3889 adj_row.account :=
3890 fa_cache_pkg.fazccb_record.DEPRN_EXPENSE_ACCT;
3891 adj_row.account_type := 'DEPRN_EXPENSE_ACCT';
3892 adj_row.gen_ccid_flag := TRUE;
3893
3894 adj_row.adjustment_type := 'EXPENSE';
3895 adj_row.debit_credit_flag := 'DR';
3896
3897 elsif adj_type = 'BONUS EXPENSE' then
3898
3899 adj_row.account:=
3900 fa_cache_pkg.fazccb_record.BONUS_DEPRN_EXPENSE_ACCT;
3901 adj_row.account_type := 'BONUS_DEPRN_EXPENSE_ACCT';
3902 adj_row.adjustment_type := 'BONUS EXPENSE';
3903 adj_row.debit_credit_flag := 'DR';
3904 adj_row.gen_ccid_flag := TRUE;
3905
3906 elsif adj_type = 'IMPAIR EXPENSE' then
3907
3908 adj_row.account:=
3909 fa_cache_pkg.fazccb_record.IMPAIR_EXPENSE_ACCT;
3910 adj_row.account_type := 'IMPAIR_EXPENSE_ACCT';
3911 adj_row.adjustment_type := 'IMPAIR EXPENSE';
3912 adj_row.debit_credit_flag := 'DR';
3913 adj_row.gen_ccid_flag := TRUE;
3914
3915 elsif adj_type = 'REVAL EXPENSE' then
3916
3917 adj_row.adjustment_type := 'REVAL EXPENSE';
3918 adj_row.debit_credit_flag := 'DR';
3919
3920 else
3921
3922 adj_row.adjustment_type := 'REVAL AMORT';
3923 adj_row.debit_credit_flag := 'DR';
3924
3925 end if;
3926
3927
3928 if RET.units_retired is NULL or RET.units_retired <= 0 then
3929
3930 if p_log_level_rec.statement_level then
3931 fa_debug_pkg.add
3932 (fname => l_calling_fn,
3933 element => 'Get dist and deprn 1 info',
3934 value => '', p_log_level_rec => p_log_level_rec);
3935 end if;
3936
3937 if (ret.mrc_sob_type_code <> 'R') then
3938 OPEN DIST_DEPRN1;
3939 else
3940 OPEN MRC_DIST_DEPRN1;
3941 end if;
3942
3943 LOOP
3944
3945 if p_log_level_rec.statement_level then
3946 fa_debug_pkg.add
3947 (fname => l_calling_fn,
3948 element => 'Fetch dist and deprn 1 info',
3949 value => '', p_log_level_rec => p_log_level_rec);
3950 end if;
3951
3952 if (ret.mrc_sob_type_code <> 'R') then
3953
3954 FETCH DIST_DEPRN1 INTO
3955 h_dist_id,
3956 h_ccid,
3957 h_exp_adj_amount,
3958 h_exp_adj_amount_sorp; -- Bug 6666666
3959 EXIT when DIST_DEPRN1%NOTFOUND OR DIST_DEPRN1%NOTFOUND IS NULL;
3960
3961 else
3962
3963 FETCH MRC_DIST_DEPRN1 INTO
3964 h_dist_id,
3965 h_ccid,
3966 h_exp_adj_amount,
3967 h_exp_adj_amount_sorp; -- Bug 6666666
3968 EXIT when MRC_DIST_DEPRN1%NOTFOUND OR MRC_DIST_DEPRN1%NOTFOUND IS NULL;
3969
3970 end if;
3971
3972 adj_row.code_combination_id := h_ccid;
3973 adj_row.adjustment_amount := h_exp_adj_amount;
3974 adj_row.distribution_id := h_dist_id;
3975 adj_row.mrc_sob_type_code := ret.mrc_sob_type_code;
3976 adj_row.set_of_books_id := ret.set_of_books_id;
3977
3978 if not FA_UTILS_PKG.faxrnd(adj_row.adjustment_amount, ret.book, ret.set_of_books_id, p_log_level_rec => p_log_level_rec) then
3979 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
3980 return(FALSE);
3981 end if;
3982
3983 if adj_row.adjustment_amount <> 0 then
3984
3985 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
3986 X_last_update_date,
3987 X_last_updated_by,
3988 X_last_update_login
3989 , p_log_level_rec => p_log_level_rec)) then
3990
3991 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
3992 return(FALSE);
3993 end if;
3994
3995 end if;
3996
3997
3998 /* Bug 6666666 : Added for SORP Compliance
3999 Only the expense amount calculated during reinstatement
4003 */
4000 should be inserted for SORP. The previous value from the
4001 adjustment table must not be taken into account as it has
4002 been already reversed due to the code in FAGIAR.
4004 if FA_CACHE_PKG.fazcbc_record.sorp_enabled_flag = 'Y'
4005 and adj_row.adjustment_type = 'EXPENSE'
4006 and h_exp_adj_amount_sorp <> 0 then
4007 if not FA_SORP_UTIL_PVT.create_sorp_neutral_acct (
4008 p_amount => h_exp_adj_amount_sorp,
4009 p_reversal => 'N',
4010 p_adj => adj_row,
4011 p_created_by => NULL,
4012 p_creation_date => NULL,
4013 p_last_update_date => X_last_update_date,
4014 p_last_updated_by => X_last_updated_by,
4015 p_last_update_login => X_last_update_login,
4016 p_who_mode => 'UPDATE'
4017 , p_log_level_rec => p_log_level_rec) then
4018 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
4019 return(FALSE);
4020 end if;
4021 end if;
4022
4023
4024 END LOOP;
4025
4026 if (ret.mrc_sob_type_code <> 'R') then
4027 CLOSE DIST_DEPRN1;
4028 else
4029 CLOSE MRC_DIST_DEPRN1;
4030 end if;
4031
4032 -- Inserting to FA_ADJUSTMENTS which dist-id is NOT active distributions
4033
4034 if p_log_level_rec.statement_level then
4035 fa_debug_pkg.add
4036 (fname => l_calling_fn,
4037 element => 'Get dist and deprn 2 info',
4038 value => '', p_log_level_rec => p_log_level_rec);
4039 end if;
4040
4041 -- BUG# 2314015
4042 if (adj_type = 'EXPENSE') then
4043 adj_row.account := NULL;
4044 adj_row.account_type := NULL;
4045 adj_row.gen_ccid_flag := FALSE;
4046 end if;
4047
4048 if (ret.mrc_sob_type_code <> 'R') then
4049 OPEN DIST_DEPRN2;
4050 else
4051 OPEN MRC_DIST_DEPRN2;
4052 end if;
4053
4054 LOOP
4055
4056 if p_log_level_rec.statement_level then
4057 fa_debug_pkg.add
4058 (fname => l_calling_fn,
4059 element => 'Fetch dist and deprn 2 info',
4060 value => '', p_log_level_rec => p_log_level_rec);
4061 end if;
4062
4063 if (ret.mrc_sob_type_code <> 'R') then
4064 FETCH DIST_DEPRN2 INTO
4065 h_dist_id,
4066 h_ccid,
4067 h_exp_adj_amount;
4068 EXIT WHEN DIST_DEPRN2%NOTFOUND OR DIST_DEPRN2%NOTFOUND IS NULL;
4069 else
4070 FETCH MRC_DIST_DEPRN2 INTO
4071 h_dist_id,
4072 h_ccid,
4073 h_exp_adj_amount;
4074 EXIT WHEN MRC_DIST_DEPRN2%NOTFOUND OR MRC_DIST_DEPRN2%NOTFOUND IS NULL;
4075 end if;
4076
4077 adj_row.code_combination_id := h_ccid;
4078 adj_row.adjustment_amount := h_exp_adj_amount;
4079 adj_row.distribution_id := h_dist_id;
4080 adj_row.mrc_sob_type_code := ret.mrc_sob_type_code;
4081 adj_row.set_of_books_id := ret.set_of_books_id;
4082
4083 if adj_row.adjustment_amount <> 0 then
4084
4085 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
4086 X_last_update_date,
4087 X_last_updated_by,
4088 X_last_update_login
4089 , p_log_level_rec => p_log_level_rec)) then
4090
4091 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
4092
4093 return(FALSE);
4094 end if;
4095
4096 end if;
4097
4098 END LOOP ;
4099
4100 if (ret.mrc_sob_type_code <> 'R') then
4101 CLOSE DIST_DEPRN2;
4102 else
4103 CLOSE MRC_DIST_DEPRN2;
4104 end if;
4105
4106 return(TRUE);
4107
4108 else
4109 if p_log_level_rec.statement_level then
4110 fa_debug_pkg.add
4111 (fname => l_calling_fn,
4112 element => 'Get dist and deprn 3 info',
4113 value => '', p_log_level_rec => p_log_level_rec);
4114 end if;
4115
4116 if (ret.mrc_sob_type_code <> 'R') then
4117 OPEN DIST_DEPRN3;
4118 else
4119 OPEN MRC_DIST_DEPRN3;
4120 end if;
4121 LOOP
4122
4123 if p_log_level_rec.statement_level then
4124 fa_debug_pkg.add
4125 (fname => l_calling_fn,
4126 element => 'Fetch dist and deprn 3 info',
4127 value => '', p_log_level_rec => p_log_level_rec);
4128 end if;
4129
4130 if (ret.mrc_sob_type_code <> 'R') then
4131 FETCH DIST_DEPRN3 INTO
4132 h_dist_id,
4133 h_ccid,
4134 h_exp_adj_amount,
4135 h_exp_adj_amount_sorp; -- Bug 6666666
4136 EXIT WHEN DIST_DEPRN3%NOTFOUND OR DIST_DEPRN3%NOTFOUND IS NULL;
4137 else
4138 FETCH MRC_DIST_DEPRN3 INTO
4142 h_exp_adj_amount_sorp; -- Bug 6666666
4139 h_dist_id,
4140 h_ccid,
4141 h_exp_adj_amount,
4143 EXIT WHEN MRC_DIST_DEPRN3%NOTFOUND OR MRC_DIST_DEPRN3%NOTFOUND IS NULL;
4144 end if;
4145
4146 adj_row.code_combination_id := h_ccid;
4147 adj_row.adjustment_amount := h_exp_adj_amount;
4148 adj_row.distribution_id := h_dist_id;
4149 adj_row.mrc_sob_type_code := ret.mrc_sob_type_code;
4150 adj_row.set_of_books_id := ret.set_of_books_id;
4151
4152 if adj_row.adjustment_amount <> 0 then
4153
4154 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
4155 X_last_update_date,
4156 X_last_updated_by,
4157 X_last_update_login
4158 , p_log_level_rec => p_log_level_rec)) then
4159
4160 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
4161 return(FALSE);
4162 end if;
4163
4164 end if;
4165
4166 /* Bug 6666666 : Added for SORP Compliance
4167 Only the expense amount calculated during reinstatement
4168 should be inserted for SORP. The previous value from the
4169 adjustment table must not be taken into account as it has
4170 been already reversed due to the code in FAGIAR.
4171 */
4172 if FA_CACHE_PKG.fazcbc_record.sorp_enabled_flag = 'Y'
4173 and adj_row.adjustment_type = 'EXPENSE'
4174 and h_exp_adj_amount_sorp <> 0 then
4175 if not FA_SORP_UTIL_PVT.create_sorp_neutral_acct (
4176 p_amount => h_exp_adj_amount_sorp,
4177 p_reversal => 'N',
4178 p_adj => adj_row,
4179 p_created_by => NULL,
4180 p_creation_date => NULL,
4181 p_last_update_date => X_last_update_date,
4182 p_last_updated_by => X_last_updated_by,
4183 p_last_update_login => X_last_update_login,
4184 p_who_mode => 'UPDATE'
4185 , p_log_level_rec => p_log_level_rec) then
4186 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
4187 return(FALSE);
4188 end if;
4189 end if;
4190
4191 END LOOP;
4192
4193 if (ret.mrc_sob_type_code <> 'R') then
4194 CLOSE DIST_DEPRN3;
4195 else
4196 CLOSE MRC_DIST_DEPRN3;
4197 end if;
4198
4199 if p_log_level_rec.statement_level then
4200 fa_debug_pkg.add
4201 (fname => l_calling_fn,
4202 element => 'Get dist and deprn 4 info',
4203 value => '', p_log_level_rec => p_log_level_rec);
4204 end if;
4205
4206 -- BUG# 2314015
4207 if (adj_type = 'EXPENSE') then
4208 adj_row.account := NULL;
4209 adj_row.account_type := NULL;
4210 adj_row.gen_ccid_flag := FALSE;
4211 end if;
4212
4213 if (ret.mrc_sob_type_code <> 'R') then
4214 OPEN DIST_DEPRN4;
4215 else
4216 OPEN MRC_DIST_DEPRN4;
4217 end if;
4218
4219 LOOP
4220
4221 if p_log_level_rec.statement_level then
4222 fa_debug_pkg.add
4223 (fname => l_calling_fn,
4224 element => 'Fetch dist and deprn 4 info',
4225 value => '', p_log_level_rec => p_log_level_rec);
4226 end if;
4227
4228 if (ret.mrc_sob_type_code <> 'R') then
4229 FETCH DIST_DEPRN4 INTO
4230 h_dist_id,
4231 h_ccid,
4232 h_exp_adj_amount;
4233 EXIT WHEN DIST_DEPRN4%NOTFOUND OR DIST_DEPRN4%NOTFOUND IS NULL;
4234 else
4235 FETCH MRC_DIST_DEPRN4 INTO
4236 h_dist_id,
4237 h_ccid,
4238 h_exp_adj_amount;
4239 EXIT WHEN MRC_DIST_DEPRN4%NOTFOUND OR MRC_DIST_DEPRN4%NOTFOUND IS NULL;
4240 end if;
4241
4242 adj_row.code_combination_id := h_ccid;
4243 adj_row.adjustment_amount := h_exp_adj_amount;
4244 adj_row.distribution_id := h_dist_id;
4245 adj_row.mrc_sob_type_code := ret.mrc_sob_type_code;
4246 adj_row.set_of_books_id := ret.set_of_books_id;
4247
4248 if adj_row.adjustment_amount <> 0 then
4249
4250 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
4251 X_last_update_date,
4252 X_last_updated_by,
4253 X_last_update_login
4254 , p_log_level_rec => p_log_level_rec)) then
4255
4256 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
4257 return(FALSE);
4258 end if;
4259
4260 end if;
4261
4262 END LOOP;
4263
4264 if (ret.mrc_sob_type_code <> 'R') then
4265 CLOSE DIST_DEPRN4;
4266 else
4267 CLOSE MRC_DIST_DEPRN4;
4268 end if;
4269
4270 return(TRUE);
4271
4272 end if;
4273
4274 EXCEPTION
4278 return FALSE;
4275
4276 when others then
4277 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
4279
4280
4281 END; -- faraje
4282
4283
4284 /*========================================================================*
4285 | NAME fagidn |
4286 | |
4287 | FUNCTION Adjust the depreciation we took at the time of the retirement.|
4288 | The formula is dpcldr - ADJ in FA_ADJUSTMENTS. |
4289 | |
4290 | History Jacob John 1/29/97 Created
4291 |=========================================================================*/
4292
4293 Function FAGIDN(
4294 RET IN OUT NOCOPY fa_ret_types.ret_struct,
4295 BK IN OUT NOCOPY fa_ret_types.book_struct,
4296 deprn_amount IN number,
4297 bonus_deprn_amount IN number,
4298 impairment_amount IN number,
4299 reval_deprn_amt IN number,
4300 reval_amort_amt IN number,
4301 cpd_ctr IN NUMBER,
4302 today IN DATE,
4303 user_id IN NUMBER
4304 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) return boolean IS
4305
4306
4307 l_calling_fn varchar2(40) := 'FA_GAINLOSS_UND_PKG.fagidn';
4308
4309 BEGIN <<FAGIDN>>
4310
4311 if (not faraje(ret,
4312 bk,
4313 deprn_amount,
4314 'EXPENSE',
4315 cpd_ctr,
4316 today,
4317 user_id,
4318 p_log_level_rec )) then
4319
4320 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
4321 return(FALSE);
4322
4323 end if;
4324
4325 if (not faraje(ret,
4326 bk,
4327 bonus_deprn_amount,
4328 'BONUS EXPENSE',
4329 cpd_ctr,
4330 today,
4331 user_id,
4332 p_log_level_rec )) then
4333
4334 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
4335 return(FALSE);
4336
4337 end if;
4338
4339 if (not faraje(ret,
4340 bk,
4341 impairment_amount,
4342 'IMPAIR EXPENSE',
4343 cpd_ctr,
4344 today,
4345 user_id,
4346 p_log_level_rec )) then
4347
4348 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
4349 return(FALSE);
4350
4351 end if;
4352
4353 if (not faraje(ret,
4354 bk,
4355 reval_deprn_amt,
4356 'REVAL EXPENSE',
4357 cpd_ctr,
4358 today,
4359 user_id,
4360 p_log_level_rec )) then
4361
4362 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
4363 return(FALSE);
4364
4365 end if;
4366
4367 if (not faraje(ret,
4368 bk,
4369 reval_amort_amt,
4370 'REVAL AMORT',
4371 cpd_ctr,
4372 today,
4373 user_id ,
4374 p_log_level_rec)) then
4375
4376 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
4377 return(FALSE);
4378
4379 end if;
4380
4381 return(TRUE);
4382
4383 END; -- fagidn
4384
4385
4386 /*============================================================================
4387 | NAME fagirv |
4388 | |
4389 |FUNCTION It determines the reserve when the time retirement happened. |
4390 | |
4391 |
4392 | History Jacob John 1/29/97 Created
4393 |============================================================================*/
4394
4395 Function FAGIRV(
4396 RET IN OUT NOCOPY fa_ret_types.ret_struct,
4397 startpd IN OUT NOCOPY number,
4398 rsv IN OUT NOCOPY number,
4399 bonus_rsv in out nocopy number,
4400 impairment_rsv in out nocopy number,
4401 reval_rsv IN OUT NOCOPY number, prior_fy_exp in out number,
4402 ytd_deprn in out nocopy number, p_log_level_rec IN FA_API_TYPES.log_level_rec_type) return boolean IS
4403
4404 h_rpdnum NUMBER;
4405 h_th_id number;
4406 h_asset_id number;
4407 h_pd_counter NUMBER;
4408 h_rpdname VARCHAR2(16);
4409 h_date_effective date;
4410 h_book varchar2(30);
4411 h_deprn_reserve NUMBER;
4412 h_bonus_deprn_reserve NUMBER;
4413 h_impairment_reserve NUMBER;
4414 h_reval_reserve NUMBER;
4415 h_prior_fy_expense number;
4416 h_ytd_deprn number;
4417 h_tot_deprn_adj NUMBER;
4418 h_tot_bonus_deprn_adj NUMBER;
4419 h_tot_impairment_adj NUMBER;
4420 h_tot_reval_adj NUMBER;
4421 FAGIRV_ERROR EXCEPTION;
4422
4423 l_calling_fn varchar2(40) := 'FA_GAINLOSS_UND_PKG.fagirv';
4424
4425 BEGIN <<FAGIRV>>
4429 h_impairment_reserve := 0;
4426
4427 h_deprn_reserve := 0;
4428 h_bonus_deprn_reserve := 0;
4430 h_reval_reserve := 0;
4431 -- Initialize h_prior_fy_expense
4432 h_prior_fy_expense := 0;
4433 h_ytd_deprn := 0;
4434 h_tot_deprn_adj := 0;
4435 h_tot_bonus_deprn_adj := 0;
4436 h_tot_impairment_adj := 0;
4437 h_tot_reval_adj := 0;
4438 h_asset_id := ret.asset_id;
4439 h_th_id := ret.th_id_in;
4440 h_book := ret.book;
4441 h_date_effective := ret.date_effective;
4442
4443 /* EXEC SQL WHENEVER SQLERROR GOTO fagirv_error;
4444 EXEC SQL WHENEVER NOT FOUND GOTO fagirv_error; */
4445
4446 if p_log_level_rec.statement_level then
4447 fa_debug_pkg.add
4448 (fname => l_calling_fn,
4449 element => 'Retirement Periods',
4450 value => '', p_log_level_rec => p_log_level_rec);
4451 end if;
4452
4453 SELECT PERIOD_NUM , PERIOD_NAME, PERIOD_COUNTER
4454 INTO h_rpdnum, h_rpdname, h_pd_counter
4455 FROM FA_DEPRN_PERIODS fadp
4456 WHERE RET.date_effective
4457 between fadp.period_open_date and
4458 nvl(fadp.period_close_date,
4459 RET.date_effective)
4460 AND fadp.book_type_code = h_book;
4461
4462 if p_log_level_rec.statement_level then
4463 fa_debug_pkg.add
4464 (fname => l_calling_fn,
4465 element => 'reserve (Before Retirement)',
4466 value => '');
4467 end if;
4468
4469 /*** Retrieve FA_DEPRN_SUMMARY.PRIOR_FY_EXPENSE. ***/
4470 /*** and Year-to-date depreciation. ***/
4471
4472 if (ret.mrc_sob_type_code <> 'R') then
4473
4474 SELECT fads.deprn_reserve, fads.bonus_deprn_reserve, fads.reval_reserve,
4475 nvl(fads.prior_fy_expense, 0), nvl(fads.ytd_deprn, 0),
4476 nvl(fads.impairment_reserve, 0)
4477 INTO h_deprn_reserve, h_bonus_deprn_reserve, h_reval_reserve,
4478 h_prior_fy_expense, h_ytd_deprn,
4479 h_impairment_reserve
4480 FROM fa_deprn_summary fads, fa_deprn_periods fadp
4481 WHERE fads.asset_id = h_asset_id
4482 AND fads.book_type_Code = h_book
4483 AND fads.period_counter = fadp.period_counter
4484 AND fadp.period_counter =
4485 (select MAX(DP.PERIOD_COUNTER)
4486 FROM FA_DEPRN_PERIODS DP, FA_DEPRN_SUMMARY DS
4487 WHERE DP.BOOK_TYPE_CODE = h_book
4488 AND DP.PERIOD_COUNTER = DS.PERIOD_COUNTER
4489 AND DP.PERIOD_COUNTER < h_pd_counter
4490 AND DS.BOOK_TYPE_CODE = h_book
4491 AND DS.ASSET_ID = h_asset_id)
4492 AND FADP.BOOK_TYPE_CODE = h_book;
4493
4494 else
4495
4496 SELECT fads.deprn_reserve, fads.bonus_deprn_reserve, fads.reval_reserve,
4497 nvl(fads.prior_fy_expense, 0), nvl(fads.ytd_deprn, 0),
4498 nvl(fads.impairment_reserve, 0)
4499 INTO h_deprn_reserve, h_bonus_deprn_reserve, h_reval_reserve,
4500 h_prior_fy_expense, h_ytd_deprn,
4501 h_impairment_reserve
4502 FROM fa_mc_deprn_summary fads, fa_deprn_periods fadp
4503 WHERE fads.asset_id = h_asset_id
4504 AND fads.book_type_Code = h_book
4505 AND fads.period_counter = fadp.period_counter
4506 AND fads.set_of_books_id = ret.set_of_books_id
4507 AND fadp.period_counter =
4508 (select MAX(DP.PERIOD_COUNTER)
4509 FROM FA_DEPRN_PERIODS DP, FA_MC_DEPRN_SUMMARY DS
4510 WHERE DP.BOOK_TYPE_CODE = h_book
4511 AND DP.PERIOD_COUNTER = DS.PERIOD_COUNTER
4512 AND DP.PERIOD_COUNTER < h_pd_counter
4513 AND DS.set_of_books_id = ret.set_of_books_id
4514 AND DS.BOOK_TYPE_CODE = h_book
4515 AND DS.ASSET_ID = h_asset_id)
4516 AND FADP.BOOK_TYPE_CODE = h_book;
4517
4518 end if;
4519
4520 prior_fy_exp := h_prior_fy_expense;
4521 ytd_deprn := h_ytd_deprn;
4522
4523 /*** Depreciation can handle subtraction method in only case of ***/
4524 /*** normal additions and prior additions. ***/
4525
4526 prior_fy_exp := 0;
4527 ytd_deprn := 0;
4528
4529 h_tot_deprn_adj := 0;
4530 h_tot_bonus_deprn_adj := 0;
4531 h_tot_impairment_adj := 0;
4532 h_tot_reval_adj := 0;
4533
4534 if p_log_level_rec.statement_level then
4535 fa_debug_pkg.add
4536 (fname => l_calling_fn,
4537 element => 'reserve (at Retirement)',
4538 value => '');
4539 end if;
4540
4541 /* WHENEVER NOT FOUND CONTINUE; */
4542
4543 if (ret.mrc_sob_type_code <> 'R') then
4544 BEGIN
4545 SELECT SUM(DECODE(faadj.adjustment_type, 'RESERVE',
4546 DECODE(faadj.debit_credit_flag, 'DR',
4547 -1 * faadj.adjustment_amount,
4548 faadj.adjustment_amount),
4549 faadj.adjustment_amount))
4550 INTO h_tot_deprn_adj
4551 FROM fa_adjustments faadj
4552 WHERE faadj.asset_id = RET.asset_id
4553 AND faadj.book_type_code = RET.book
4554 AND faadj.source_type_code = 'RETIREMENT'
4555 AND faadj.adjustment_type in ('RESERVE', 'EXPENSE', 'REVAL EXPENSE')
4556 AND faadj.period_counter_created = h_pd_counter
4557 AND faadj.transaction_header_id <> RET.th_id_in
4558 GROUP BY faadj.asset_id;
4559 EXCEPTION
4560 When others then
4561 null;
4562 END;
4563 else
4564 BEGIN
4568 faadj.adjustment_amount),
4565 SELECT SUM(DECODE(faadj.adjustment_type, 'RESERVE',
4566 DECODE(faadj.debit_credit_flag, 'DR',
4567 -1 * faadj.adjustment_amount,
4569 faadj.adjustment_amount))
4570 INTO h_tot_deprn_adj
4571 FROM fa_mc_adjustments faadj
4572 WHERE faadj.asset_id = RET.asset_id
4573 AND faadj.book_type_code = RET.book
4574 AND faadj.source_type_code = 'RETIREMENT'
4575 AND faadj.adjustment_type in ('RESERVE', 'EXPENSE', 'REVAL EXPENSE')
4576 AND faadj.period_counter_created = h_pd_counter
4577 AND faadj.set_of_books_id = ret.set_of_books_id
4578 AND faadj.transaction_header_id <> RET.th_id_in
4579 GROUP BY faadj.asset_id;
4580 EXCEPTION
4581 When others then
4582 null;
4583 END;
4584 end if;
4585
4586 if p_log_level_rec.statement_level then
4587 fa_debug_pkg.add
4588 (fname => l_calling_fn,
4589 element => 'bonus reserve (at Retirement)',
4590 value => '');
4591 end if;
4592
4593 if (ret.mrc_sob_type_code <> 'R') then
4594 BEGIN
4595 SELECT SUM(DECODE(faadj.adjustment_type, 'BONUS RESERVE',
4596 DECODE(faadj.debit_credit_flag, 'DR',
4597 -1 * faadj.adjustment_amount,
4598 faadj.adjustment_amount),
4599 faadj.adjustment_amount))
4600 INTO h_tot_bonus_deprn_adj
4601 FROM fa_adjustments faadj
4602 WHERE faadj.asset_id = RET.asset_id
4603 AND faadj.book_type_code = RET.book
4604 AND faadj.source_type_code = 'RETIREMENT'
4605 AND faadj.adjustment_type in ('BONUS RESERVE', 'BONUS EXPENSE')
4606 AND faadj.period_counter_created = h_pd_counter
4607 AND faadj.transaction_header_id <> RET.th_id_in
4608 GROUP BY faadj.asset_id;
4609 EXCEPTION
4610 When others then
4611 null;
4612 END;
4613 else
4614 BEGIN
4615 SELECT SUM(DECODE(faadj.adjustment_type, 'BONUS RESERVE',
4616 DECODE(faadj.debit_credit_flag, 'DR',
4617 -1 * faadj.adjustment_amount,
4618 faadj.adjustment_amount),
4619 faadj.adjustment_amount))
4620 INTO h_tot_bonus_deprn_adj
4621 FROM fa_mc_adjustments faadj
4622 WHERE faadj.asset_id = RET.asset_id
4623 AND faadj.book_type_code = RET.book
4624 AND faadj.source_type_code = 'RETIREMENT'
4625 AND faadj.adjustment_type in ('BONUS RESERVE', 'BONUS EXPENSE')
4626 AND faadj.period_counter_created = h_pd_counter
4627 AND faadj.set_of_books_id = ret.set_of_books_id
4628 AND faadj.transaction_header_id <> RET.th_id_in
4629 GROUP BY faadj.asset_id;
4630 EXCEPTION
4631 When others then
4632 null;
4633 END;
4634 end if;
4635
4636 if p_log_level_rec.statement_level then
4637 fa_debug_pkg.add
4638 (fname => l_calling_fn,
4639 element => 'impair reserve (at Retirement)',
4640 value => '');
4641 end if;
4642
4643 if (ret.mrc_sob_type_code <> 'R') then
4644 BEGIN
4645 SELECT SUM(DECODE(faadj.adjustment_type, 'IMPAIR RESERVE',
4646 DECODE(faadj.debit_credit_flag, 'DR',
4647 -1 * faadj.adjustment_amount,
4648 faadj.adjustment_amount),
4649 faadj.adjustment_amount))
4650 INTO h_tot_impairment_adj
4651 FROM fa_adjustments faadj
4652 WHERE faadj.asset_id = RET.asset_id
4653 AND faadj.book_type_code = RET.book
4654 AND faadj.source_type_code = 'RETIREMENT'
4655 AND faadj.adjustment_type in ('IMPAIR RESERVE', 'IMPAIR EXPENSE')
4656 AND faadj.period_counter_created = h_pd_counter
4657 AND faadj.transaction_header_id <> RET.th_id_in
4658 GROUP BY faadj.asset_id;
4659 EXCEPTION
4660 When others then
4661 null;
4662 END;
4663 else
4664 BEGIN
4665 SELECT SUM(DECODE(faadj.adjustment_type, 'IMPAIR RESERVE',
4666 DECODE(faadj.debit_credit_flag, 'DR',
4667 -1 * faadj.adjustment_amount,
4668 faadj.adjustment_amount),
4669 faadj.adjustment_amount))
4670 INTO h_tot_impairment_adj
4671 FROM fa_mc_adjustments faadj
4672 WHERE faadj.asset_id = RET.asset_id
4673 AND faadj.book_type_code = RET.book
4674 AND faadj.source_type_code = 'RETIREMENT'
4675 AND faadj.adjustment_type in ('IMPAIR RESERVE', 'IMPAIR EXPENSE')
4676 AND faadj.period_counter_created = h_pd_counter
4677 AND faadj.set_of_books_id = ret.set_of_books_id
4678 AND faadj.transaction_header_id <> RET.th_id_in
4679 GROUP BY faadj.asset_id;
4680 EXCEPTION
4681 When others then
4682 null;
4683 END;
4684 end if;
4685
4686 if p_log_level_rec.statement_level then
4687 fa_debug_pkg.add
4688 (fname => l_calling_fn,
4689 element => 'reval reserve (at Retirement)',
4690 value => '');
4691 end if;
4692
4693 if (ret.mrc_sob_type_code <> 'R') then
4694 BEGIN
4695 SELECT SUM(DECODE(faadj.adjustment_type, 'REVAL RESERVE',
4699 -1 * faadj.adjustment_amount))
4696 DECODE(faadj.debit_credit_flag, 'DR',
4697 -1 * faadj.adjustment_amount,
4698 faadj.adjustment_amount),
4700 INTO h_tot_reval_adj
4701 FROM fa_adjustments faadj
4702 WHERE faadj.asset_id = RET.asset_id
4703 AND faadj.book_type_code = RET.book
4704 AND faadj.source_type_code = decode(RET.wip_asset,1,'CIP RETIREMENT','RETIREMENT')
4705 AND faadj.adjustment_type in ('REVAL RESERVE', 'REVAL AMORT')
4706 AND faadj.period_counter_created = h_pd_counter
4707 AND faadj.transaction_header_id <> RET.th_id_in
4708 GROUP BY faadj.asset_id;
4709 EXCEPTION
4710 When others then
4711 null;
4712 END;
4713 else
4714 BEGIN
4715 SELECT SUM(DECODE(faadj.adjustment_type, 'REVAL RESERVE',
4716 DECODE(faadj.debit_credit_flag, 'DR',
4717 -1 * faadj.adjustment_amount,
4718 faadj.adjustment_amount),
4719 -1 * faadj.adjustment_amount))
4720 INTO h_tot_reval_adj
4721 FROM fa_mc_adjustments faadj
4722 WHERE faadj.asset_id = RET.asset_id
4723 AND faadj.book_type_code = RET.book
4724 AND faadj.source_type_code = decode(RET.wip_asset,1,'CIP RETIREMENT','RETIREMENT') /*12768930 */
4725 AND faadj.adjustment_type in ('REVAL RESERVE', 'REVAL AMORT')
4726 AND faadj.period_counter_created = h_pd_counter
4727 AND faadj.set_of_books_id = ret.set_of_books_id
4728 AND faadj.transaction_header_id <> RET.th_id_in
4729 GROUP BY faadj.asset_id;
4730 EXCEPTION
4731 When others then
4732 null;
4733 END;
4734 end if;
4735
4736 startpd := h_rpdnum;
4737 rsv := h_deprn_reserve + h_tot_deprn_adj;
4738 bonus_rsv := h_bonus_deprn_reserve + h_tot_bonus_deprn_adj;
4739 impairment_rsv := h_impairment_reserve + h_tot_impairment_adj;
4740 reval_rsv := h_reval_reserve + h_tot_reval_adj;
4741
4742 return(TRUE);
4743
4744 Exception
4745
4746 when others then
4747 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
4748 return FALSE;
4749
4750 END; -- fagirv
4751
4752 /*============================================================================
4753 | NAME fagict_adj
4754 |
4755 | FUNCTION debit the cost account by the adjustments amount occured between
4756 | retirement and reinstatement.
4757 |
4758 | History SKCHAWLA 04/18/06 Created
4759 | added for the bug 4898842
4760 |===========================================================================*/
4761
4762
4763 Function FAGICT_ADJ(
4764 RET IN OUT NOCOPY fa_ret_types.ret_struct,
4765 BK IN OUT NOCOPY fa_ret_types.book_struct,
4766 cpd_ctr IN NUMBER,
4767 today IN DATE,
4768 user_id IN NUMBER,
4769 p_asset_fin_rec_new FA_API_TYPES.asset_fin_rec_type
4770 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) return boolean IS
4771
4772
4773 h_adj_type varchar2(16);
4774 h_source_type_code varchar2(16);
4775 h_dist_id number;
4776 h_ccid number;
4777 h_misc_cost number;
4778 h_asset_id number;
4779 h_th_id_out number;
4780 X_LAST_UPDATE_DATE date := sysdate;
4781 X_last_updated_by number := -1;
4782 X_last_update_login number := -1;
4783 l_calling_fn varchar2(40) := 'FA_GAINLOSS_UND_PKG.fagict_adj';
4784 h_th_id number;
4785 h_dr_cr varchar2(3);
4786 h_units_assign number;
4787 h_bk_cost number;
4788 H_ADJ_AMT NUMBER;
4789
4790 process_term_dist number := 0;
4791 adj_row fa_adjust_type_pkg.fa_adj_row_struct;
4792 fagict_adj_error EXCEPTION;
4793
4794 CURSOR TERM_DIST IS
4795 SELECT distribution_id
4796 FROM fa_distribution_history
4797 WHERE book_type_code = ret.book
4798 and asset_id = ret.asset_id
4799 and transaction_header_id_out = h_th_id_out;
4800
4801 BEGIN
4802 h_th_id_out := -1;
4803
4804 adj_row.selection_thid := h_th_id_out ;
4805
4806 if(ret.mrc_sob_type_code <> 'R')then
4807 select transaction_header_id_out
4808 into h_th_id_out
4809 from fa_retirements
4810 where retirement_id = ret.retirement_id;
4811 else
4812 select transaction_header_id_out
4813 into h_th_id_out
4814 from fa_mc_retirements
4815 where retirement_id = ret.retirement_id
4816 and set_of_books_id = ret.set_of_books_id;
4817 END IF;
4818
4819
4820 adj_row.selection_thid := h_th_id_out ;
4821 fa_debug_pkg.add
4822 (fname => l_calling_fn,
4823 element => 'h_th_id_out ###------',
4824 value =>h_th_id_out, p_log_level_rec => p_log_level_rec);
4825
4826 if(h_th_id_out is null) or (h_th_id_out < 0)then
4827 return TRUE;
4828 end if;
4829
4830
4831 adj_row.gen_ccid_flag := TRUE;
4832 adj_row.book_type_code := RET.book;
4833
4834 adj_row.asset_id := RET.asset_id;
4835
4836 adj_row.period_counter_adjusted := cpd_ctr;
4837 adj_row.period_counter_created := cpd_ctr;
4838 adj_row.last_update_date := today;
4842
4839 adj_row.adjustment_amount := 0;
4840 adj_row.account := NULL;
4841 adj_row.account_type := NULL;
4843 adj_row.selection_thid := 0;
4844
4845 adj_row.distribution_id := 0;
4846 adj_row.gen_ccid_flag := TRUE;
4847 adj_row.code_combination_id := 0;
4848 adj_row.current_units := bk.cur_units;
4849
4850 adj_row.transaction_header_id := h_th_id_out;
4851 adj_row.selection_thid := h_th_id_out ;
4852
4853 fa_debug_pkg.add
4854 (fname => l_calling_fn,
4855 element => 'books units------',
4856 value =>bk.cur_units, p_log_level_rec => p_log_level_rec);
4857 OPEN TERM_DIST;
4858 LOOP
4859 FETCH TERM_DIST
4860 INTO h_dist_id;
4861 EXIT WHEN TERM_DIST%NOTFOUND OR TERM_DIST%NOTFOUND IS NULL;
4862 fa_debug_pkg.add
4863 (fname => l_calling_fn,
4864 element => 'books units------11111',
4865 value =>bk.cur_units, p_log_level_rec => p_log_level_rec);
4866 if(ret.mrc_sob_type_code <> 'R')then
4867 SELECT SUM(DECODE(DEBIT_CREDIT_FLAG,'DR',ADJUSTMENT_AMOUNT,-1*ADJUSTMENT_AMOUNT)),
4868 max(CODE_COMBINATION_ID),
4869 max(ADJUSTMENT_TYPE),
4870 max(SOURCE_TYPE_CODE),
4871 max(TRANSACTION_HEADER_ID)
4872 INTO H_MISC_COST,
4873 h_ccid,
4874 h_adj_type,
4875 h_source_type_code,
4876 h_th_id
4877 FROM FA_ADJUSTMENTS
4878 WHERE ASSET_ID = RET.ASSET_id
4879 and book_type_code = ret.book
4880 and distribution_id = h_dist_id
4881 AND adjustment_type in ('COST', 'CIP COST')
4882 group by distribution_id;
4883 else
4884 SELECT SUM(DECODE(DEBIT_CREDIT_FLAG,'DR',ADJUSTMENT_AMOUNT,-1*ADJUSTMENT_AMOUNT)),
4885 max(CODE_COMBINATION_ID),
4886 max(ADJUSTMENT_TYPE),
4887 max(SOURCE_TYPE_CODE),
4888 max(TRANSACTION_HEADER_ID)
4889 INTO H_MISC_COST,
4890 h_ccid,
4891 h_adj_type,
4892 h_source_type_code,
4893 h_th_id
4894 FROM fa_mc_adjustments
4895 WHERE ASSET_ID = RET.ASSET_id
4896 and book_type_code = ret.book
4897 and distribution_id = h_dist_id
4898 and set_of_books_id = ret.set_of_books_id
4899 AND adjustment_type in ('COST', 'CIP COST')
4900 group by distribution_id;
4901 END IF;
4902 process_term_dist := process_term_dist + 1;
4903
4904 adj_row.code_combination_id := h_ccid;
4905 adj_row.distribution_id := h_dist_id;
4906 adj_row.debit_credit_flag := 'CR';
4907 adj_row.adjustment_type := h_adj_type;
4908 adj_row.source_type_code := 'RETIREMENT';
4909
4910 if(RET.wip_asset > 0)then
4911 adj_row.account_type := 'CIP_COST_ACCT';
4912 adj_row.account := fa_cache_pkg.fazccb_record.CIP_COST_ACCT;
4913
4914 else
4915 adj_row.account_type := 'ASSET_COST_ACCT';
4916 adj_row.account := fa_cache_pkg.fazccb_record.ASSET_COST_ACCT;
4917 end if;
4918
4919 adj_row.selection_mode := fa_std_types.FA_AJ_CLEAR_PARTIAL;
4920 adj_row.adjustment_amount := 0;
4921 adj_row.mrc_sob_type_code := ret.mrc_sob_type_code;
4922 adj_row.set_of_books_id := ret.set_of_books_id;
4923 if p_log_level_rec.statement_level then
4924 fa_debug_pkg.add
4925 (fname => l_calling_fn,
4926 element => 'before clear',
4927 value => '', p_log_level_rec => p_log_level_rec);
4928 END if;
4929 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
4930 X_last_update_date,
4931 X_last_updated_by,
4932 X_last_update_login, p_log_level_rec => p_log_level_rec)) then
4933
4934 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
4935 return(false);
4936
4937 end if;
4938 if p_log_level_rec.statement_level then
4939 fa_debug_pkg.add
4940 (fname => l_calling_fn,
4941 element => 'after clear',
4942 value => '', p_log_level_rec => p_log_level_rec);
4943 fa_debug_pkg.add
4944 (fname => l_calling_fn,
4945 element => 'TERM h_dist_id ++$$$$$++',
4946 value => h_dist_id);
4947 END if;
4948
4949 adj_row.debit_credit_flag := 'DR';
4950 adj_row.adjustment_type := h_adj_type;
4951 adj_row.source_type_code := 'RETIREMENT';
4952
4953 if(RET.wip_asset > 0)then
4954 adj_row.account_type := 'CIP_COST_ACCT';
4955 adj_row.account := fa_cache_pkg.fazccb_record.CIP_COST_ACCT;
4956
4957 else
4958
4959 adj_row.account_type := 'ASSET_COST_ACCT';
4960 adj_row.account := fa_cache_pkg.fazccb_record.ASSET_COST_ACCT;
4961 end if;
4962
4963 adj_row.selection_mode := fa_std_types.FA_AJ_ACTIVE_PARTIAL;
4964
4965 adj_row.adjustment_amount := adj_row.amount_inserted;
4966
4967 adj_row.mrc_sob_type_code := ret.mrc_sob_type_code;
4968 adj_row.set_of_books_id := ret.set_of_books_id;
4969 if p_log_level_rec.statement_level then
4970 fa_debug_pkg.add
4971 (fname => l_calling_fn,
4972 element => 'before clear',
4973 value => '', p_log_level_rec => p_log_level_rec);
4974 fa_debug_pkg.add
4975 (fname => l_calling_fn,
4976 element => 'h_misc_cost ++$$$$$++1',
4977 value => h_misc_cost);
4978 fa_debug_pkg.add
4979 (fname => l_calling_fn,
4983 (fname => l_calling_fn,
4980 element => 'bk.current_cost ++$$$$$++1',
4981 value => bk.current_cost);
4982 fa_debug_pkg.add
4984 element => 'h_dist_id ++$$$$$++1',
4985 value => h_dist_id);
4986 fa_debug_pkg.add
4987 (fname => l_calling_fn,
4988 element => 'h_units_assign ++$$$$$++1',
4989 value => h_units_assign);
4990 fa_debug_pkg.add
4991 (fname => l_calling_fn,
4992 element => 'bk.cur_units ++$$$$$++1',
4993 value => bk.cur_units);
4994 fa_debug_pkg.add
4995 (fname => l_calling_fn,
4996 element => 'adj_row.adjustment_amount ###1',
4997 value => adj_row.adjustment_amount, p_log_level_rec => p_log_level_rec);
4998 END if;
4999 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
5000 X_last_update_date,
5001 X_last_updated_by,
5002 X_last_update_login, p_log_level_rec => p_log_level_rec)) then
5003
5004 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
5005 return(false);
5006 END if;
5007 adj_row.adjustment_type := 'RESERVE';
5008 adj_row.debit_credit_flag := 'DR';
5009 adj_row.account_type := 'DEPRN_RESERVE_ACCT';
5010 adj_row.account := fa_cache_pkg.fazccb_record.DEPRN_RESERVE_ACCT;
5011
5012 adj_row.selection_mode := fa_std_types.FA_AJ_CLEAR_PARTIAL;
5013
5014 adj_row.adjustment_amount := 0;
5015
5016 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
5017 X_last_update_date,
5018 X_last_updated_by,
5019 X_last_update_login, p_log_level_rec => p_log_level_rec)) then
5020
5021 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
5022 return(false);
5023
5024 end if;
5025 adj_row.debit_credit_flag := 'CR';
5026 adj_row.adjustment_amount := adj_row.amount_inserted;
5027 adj_row.selection_mode := fa_std_types.FA_AJ_ACTIVE_PARTIAL;
5028 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
5029 X_last_update_date,
5030 X_last_updated_by,
5031 X_last_update_login, p_log_level_rec => p_log_level_rec)) then
5032
5033 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
5034 return(false);
5035
5036 end if;
5037 END LOOP;
5038 CLOSE TERM_DIST;
5039
5040 return TRUE;
5041 EXCEPTION
5042
5043 when fagict_adj_error then
5044 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
5045 return FALSE;
5046
5047
5048 when others then
5049
5050 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
5051 return FALSE;
5052
5053
5054 END;
5055
5056 /*=============================================================================
5057 | NAME fagrin |
5058 | |
5059 | FUNCTION This function is called when we reinstate a retirement. |
5060 | |
5061 |============================================================================*/
5062
5063 Function FAGRIN(
5064 RET IN OUT NOCOPY fa_ret_types.ret_struct,
5065 BK IN OUT NOCOPY fa_ret_types.book_struct,
5066 DPR IN OUT NOCOPY fa_std_types.dpr_struct,
5067 today IN DATE,
5068 cpd_ctr IN NUMBER,
5069 cpdnum IN NUMBER,
5070 user_id IN NUMBER
5071 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) return boolean IS
5072
5073 start_pd Number;
5074 deprn_amt Number;
5075 bonus_deprn_amt number;
5076 impairment_amt number;
5077 reval_deprn_amt Number;
5078 reval_amort_amt Number;
5079 deprn_reserve Number;
5080 bonus_deprn_reserve number;
5081 impairment_reserve number;
5082 reval_reserve Number;
5083 /*** Declare prior_fy_exp to get data from fagirv. ***/
5084 prior_fy_exp number;
5085 ytd_deprn number;
5086
5087 cost_frac Number;
5088
5089 l_asset_fin_rec_new FA_API_TYPES.asset_fin_rec_type;
5090 l_deprn_exp NUMBER;
5091 l_bonus_deprn_exp NUMBER;
5092 l_impairment_exp NUMBER;
5093 l_decision_flag BOOLEAN; -- Bug 6660490
5094
5095 l_calling_fn varchar2(40) := 'FA_GAINLOSS_UND_PKG.fagrin';
5096 --bug6853328
5097 l_reserve_ret number;
5098 l_dpr FA_STD_TYPES.FA_DEPRN_ROW_STRUCT;
5099 l_status BOOLEAN;
5100 l_exp_ret number;
5101 --Added for 8651843
5102 l_cur_grp_id NUMBER :=0 ;
5103 --End for 8651843
5104 BEGIN <<FAGRIN>>
5105
5106 if p_log_level_rec.statement_level then
5107 fa_debug_pkg.add
5108 (fname => l_calling_fn,
5109 element => '+++ FAGRIN: Step 1',
5110 value => '', p_log_level_rec => p_log_level_rec);
5111 end if;
5112
5113 deprn_amt := 0;
5114 bonus_deprn_amt := 0;
5115 impairment_amt := 0;
5116 reval_deprn_amt := 0;
5117 reval_amort_amt := 0;
5118 deprn_reserve := 0;
5119 bonus_deprn_reserve := 0;
5120 impairment_reserve := 0;
5121 reval_reserve := 0;
5122
5126 SELECT group_asset_id
5123 --Added for 8651843
5124 BEGIN
5125 IF (ret.mrc_sob_type_code <> 'R') THEN
5127 INTO l_cur_grp_id
5128 FROM fa_books
5129 WHERE asset_id = ret.asset_id
5130 AND book_type_code = ret.book
5131 AND transaction_header_id_out IS NULL;
5132 ELSE
5133
5134 SELECT group_asset_id
5135 INTO l_cur_grp_id
5136 FROM fa_mc_books
5137 WHERE asset_id = ret.asset_id
5138 AND book_type_code = ret.book
5139 AND set_of_books_id = ret.set_of_books_id
5140 AND transaction_header_id_out IS NULL;
5141 END IF;
5142
5143 EXCEPTION
5144 WHEN OTHERS THEN
5145 l_cur_grp_id := NULL;
5146 END;
5147
5148 IF p_log_level_rec.statement_level THEN
5149 fa_debug_pkg.add(l_calling_fn, 'in fagrin group asset id', l_cur_grp_id, p_log_level_rec => p_log_level_rec);
5150 END IF;
5151
5152 IF l_cur_grp_id IS NOT NULL THEN
5153 bk.group_asset_id := l_cur_grp_id;
5154 END IF;
5155 --End of addition 8651843
5156
5157 /*** Initialize prior_fy_exp ***/
5158 prior_fy_exp := 0;
5159 ytd_deprn := 0;
5160 cost_frac := 0;
5161
5162 if p_log_level_rec.statement_level then fa_debug_pkg.add(l_calling_fn, 'in fagrin 1', '', p_log_level_rec => p_log_level_rec); end if;
5163 -- bug 5008040
5164 -- if ( bk.current_cost is NULL or bk.current_cost <= 0) then
5165
5166 if ( bk.current_cost is NULL or bk.current_cost = 0) then
5167 cost_frac := 0;
5168 else
5169 cost_frac := ret.cost_retired / bk.current_cost;
5170
5171 -- Fix for Bug 3172944. Do not round cost_frac
5172 -- Call faxrnd to round cost_frac in fagrin
5173 --if not FA_UTILS_PKG.faxrnd(cost_frac, ret.book, p_log_level_rec => p_log_level_rec) then
5174 -- fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
5175 -- return(FALSE);
5176 --end if;
5177 end if;
5178
5179 if p_log_level_rec.statement_level then fa_debug_pkg.add(l_calling_fn, 'in fagrin 2', '', p_log_level_rec => p_log_level_rec); end if;
5180 /* bg344494 base 334428, re-added in the afmround function. Should have been
5181 * added in version 75.20 but seems to have disappered. Readding
5182 */
5183
5184 /* If it's capitalize and also depreciate, then we know that when we did
5185 the retirement we calculate depreciation; Thus, we need to readjust it.
5186 Otherwise, we will skip it.
5187 */
5188 -- Bug 6660490 for extended assets bk.fully_extended needs
5189 -- to be used instead of bk.fully_reserved
5190
5191 -- Bug 8211842 : Check if asset has started extended depreciation
5192 if bk.extended_flag and bk.start_extended then
5193 l_decision_flag := bk.fully_extended;
5194 else
5195 l_decision_flag := bk.fully_reserved;
5196 end if;
5197 /*Bug#12768930 - Modified following condition to allow call to fagirv for CIP asset and non sorp book */
5198 if (bk.capitalize AND bk.depreciate AND
5199 ((ret.wip_asset is null or ret.wip_asset <= 0) OR
5200 (ret.wip_asset > 0 and nvl(FA_CACHE_PKG.fazcbc_record.sorp_enabled_flag,'N') = 'N'))) then
5201
5202 if ( NOT l_decision_flag) then
5203
5204 if ( NOT fagirv(ret, start_pd, deprn_reserve,
5205 bonus_deprn_reserve, impairment_reserve,
5206 reval_reserve,
5207 prior_fy_exp, ytd_deprn,
5208 p_log_level_rec)) then
5209 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
5210 return(FALSE);
5211 end if;
5212
5213 if p_log_level_rec.statement_level then
5214 fa_debug_pkg.add
5215 (fname => l_calling_fn,
5216 element => 'after fagirv:deprn_reserve',
5217 value => deprn_reserve, p_log_level_rec => p_log_level_rec);
5218 fa_debug_pkg.add
5219 (fname => l_calling_fn,
5220 element => 'after fagirv:bonus_deprn_reserve',
5221 value => bonus_deprn_reserve, p_log_level_rec => p_log_level_rec);
5222 fa_debug_pkg.add
5223 (fname => l_calling_fn,
5224 element => 'after fagirv:impairment_reserve',
5225 value => impairment_reserve, p_log_level_rec => p_log_level_rec);
5226 fa_debug_pkg.add
5227 (fname => l_calling_fn,
5228 element => 'after fagirv:reval_reserve',
5229 value => reval_reserve, p_log_level_rec => p_log_level_rec);
5230 fa_debug_pkg.add
5231 (fname => l_calling_fn,
5232 element => 'after fagirv:prior_fy_exp',
5233 value => prior_fy_exp, p_log_level_rec => p_log_level_rec);
5234 fa_debug_pkg.add
5235 (fname => l_calling_fn,
5236 element => 'after fagirv:ytd_deprn',
5237 value => ytd_deprn, p_log_level_rec => p_log_level_rec);
5238 end if;
5239
5240
5241 dpr.prior_fy_exp := prior_fy_exp;
5242 dpr.ytd_deprn := ytd_deprn;
5243
5244 dpr.jdate_retired := 0;
5245
5246 /* 1012866. Ret_prorate_jdate cannot be zero. Julian date has to
5247 be > 0. I am getting the jdate from the books prorate date */
5248
5249 --dpr.ret_prorate_jdate := 0;
5250 dpr.ret_prorate_jdate := to_char(bk.ret_prorate_date,'J');
5251
5252 deprn_amt := 0;
5256 reval_amort_amt := 0;
5253 bonus_deprn_amt := 0;
5254 impairment_amt := 0;
5255 reval_deprn_amt := 0;
5257
5258
5259 --
5260 -- Following deprn amount calculation is done only if this standalone
5261 -- asset or member asset which group has CALCULATE tracking method.
5262
5263 if (start_pd < cpdnum) and
5264 (not((bk.group_asset_id is not null) and
5265 nvl(bk.tracking_method, 'ALLOCATE') = 'ALLOCATE')) then
5266
5267 dpr.deprn_rsv := deprn_reserve;
5268 dpr.bonus_deprn_rsv := bonus_deprn_reserve;
5269 dpr.impairment_rsv := impairment_reserve;
5270 dpr.reval_rsv := reval_reserve;
5271
5272 dpr.rsv_known_flag := TRUE;
5273
5274 -- Bug:6349882
5275 dpr.transaction_type_code := 'REINSTATEMENT';
5276
5277 if p_log_level_rec.statement_level then fa_debug_pkg.add(l_calling_fn, 'in fagrin 3', '', p_log_level_rec => p_log_level_rec); end if;
5278
5279
5280 --Bug5887343 Added retirement_id
5281 if (NOT FA_GAINLOSS_DPR_PKG.fagcdp(
5282 dpr,
5283 deprn_amt,
5284 bonus_deprn_amt,
5285 impairment_amt,
5286 reval_deprn_amt,
5287 reval_amort_amt,
5288 bk.deprn_start_date,
5289 bk.d_cal, bk.p_cal,
5290 start_pd,
5291 cpdnum - 1,
5292 bk.prorate_fy,
5293 bk.dsd_fy,
5294 bk.prorate_jdate,
5295 bk.deprn_start_jdate,
5296 ret.retirement_id,
5297 p_log_level_rec) ) then
5298
5299 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
5300 return(FALSE);
5301
5302 end if;
5303
5304 --bug6853328 starts
5305 --Added the call to query balances to get the current reserve
5306 --without including the values from adjustments
5307 l_dpr.asset_id := ret.asset_id;
5308 l_dpr.book := ret.book;
5309 l_dpr.period_ctr := 0;
5310 l_dpr.dist_id := 0;
5311 l_dpr.mrc_sob_type_code := ret.mrc_sob_type_code;
5312 l_dpr.set_of_books_id := ret.set_of_books_id;
5313
5314 fa_query_balances_pkg.query_balances_int(
5315 X_DPR_ROW => l_dpr,
5316 X_RUN_MODE => 'STANDARD',
5317 X_DEBUG => FALSE,
5318 X_SUCCESS => l_status,
5319 X_CALLING_FN => 'FA_GAINLOSS_UND_PKG.fagrin',
5320 X_TRANSACTION_HEADER_ID => -1, p_log_level_rec => p_log_level_rec);
5321
5322 if (NOT l_status) then
5323 return(FALSE);
5324 end if;
5325
5326 IF (ret.mrc_sob_type_code <> 'R') THEN
5327 select sum(decode(debit_credit_flag, 'CR', -1, 1) * adjustment_amount)
5328 into l_reserve_ret
5329 from fa_adjustments
5330 where asset_id = ret.asset_id
5331 and book_type_code = ret.book
5332 and source_type_code = 'RETIREMENT'
5333 and adjustment_type = 'RESERVE'
5334 and transaction_header_id = ret.th_id_in;
5335 ELSE
5336 select sum(decode(debit_credit_flag, 'CR', -1, 1) * adjustment_amount)
5337 into l_reserve_ret
5338 from fa_mc_adjustments
5339 where asset_id = ret.asset_id
5340 and book_type_code = ret.book
5341 and source_type_code = 'RETIREMENT'
5342 and adjustment_type = 'RESERVE'
5343 and transaction_header_id = ret.th_id_in
5344 and set_of_books_id = ret.set_of_books_id;
5345 END IF;
5346
5347 -- Bug # 7184690,7199183 added below sql
5348 IF (ret.mrc_sob_type_code <> 'R') THEN
5349 select nvl(sum(decode(debit_credit_flag, 'DR', adjustment_amount, -adjustment_amount)), 0)
5350 into l_exp_ret
5351 from fa_adjustments
5352 where asset_id = ret.asset_id
5353 and book_type_code = ret.book
5354 and source_type_code = 'RETIREMENT'
5355 and adjustment_type = 'EXPENSE'
5356 and transaction_header_id = ret.th_id_in;
5357 ELSE
5358 select nvl(sum(decode(debit_credit_flag, 'DR', adjustment_amount, -adjustment_amount)), 0)
5359 into l_exp_ret
5360 from fa_mc_adjustments
5361 where asset_id = ret.asset_id
5362 and book_type_code = ret.book
5363 and source_type_code = 'RETIREMENT'
5364 and adjustment_type = 'EXPENSE'
5365 and transaction_header_id = ret.th_id_in
5366 and set_of_books_id = ret.set_of_books_id;
5367 END IF;
5368
5369 -- Bug # 7184690,7199183
5370 --Changed the deprn_amt calculation so that penny differences are removed
5371 fa_debug_pkg.add (l_calling_fn, '+++dpr.deprn_rsv (from fagcdp)', dpr.deprn_rsv);
5372 fa_debug_pkg.add (l_calling_fn, '+++l_reserve_ret (from fagcdp)', l_reserve_ret);
5373 fa_debug_pkg.add (l_calling_fn, '+++l_exp_ret (from fagcdp)', l_exp_ret);
5377
5374 fa_debug_pkg.add (l_calling_fn, '+++deprn_amt (from fagcdp)', deprn_amt);
5375 fa_debug_pkg.add (l_calling_fn, '+++l_dpr.deprn_rsv (from fagcdp)', l_dpr.deprn_rsv);
5376 deprn_amt := (dpr.deprn_rsv - l_reserve_ret + l_exp_ret + deprn_amt ) - l_dpr.deprn_rsv;
5378 -- deprn_amt := cost_frac * deprn_amt ;
5379 --bug6853328 ends
5380
5381 bonus_deprn_amt := cost_frac * bonus_deprn_amt ;
5382 impairment_amt := cost_frac * impairment_amt;
5383 reval_deprn_amt := cost_frac * reval_deprn_amt;
5384 reval_amort_amt := cost_frac * reval_amort_amt;
5385
5386 if p_log_level_rec.statement_level then
5387 fa_debug_pkg.add (l_calling_fn, '++++++++++++++++++++++++++++++ BEGIN fagcpd', '...', p_log_level_rec => p_log_level_rec);
5388 fa_debug_pkg.add (l_calling_fn, '+++ start_pd', start_pd, p_log_level_rec => p_log_level_rec);
5389 fa_debug_pkg.add (l_calling_fn, '+++ cpdnum', cpdnum, p_log_level_rec => p_log_level_rec);
5390 fa_debug_pkg.add (l_calling_fn, '+++ deprn_amt (from fagcdp)', deprn_amt);
5391 fa_debug_pkg.add (l_calling_fn, '+++ dpr.deprn_rsv (from fagcdp)', dpr.deprn_rsv);
5392 fa_debug_pkg.add (l_calling_fn, '++++++++++++++++++++++++++++++ END fagcpd', '...', p_log_level_rec => p_log_level_rec);
5393 end if;
5394
5395 else
5396 if p_log_level_rec.statement_level then
5397 fa_debug_pkg.add
5398 (fname => l_calling_fn,
5399 element => 'Skipping to call FA_GAINLOSS_DPR_PKG.fagcdp',
5400 value => bk.tracking_method, p_log_level_rec => p_log_level_rec);
5401 end if;
5402 end if;
5403
5404 end if;
5405
5406 if (not FA_GAINLOSS_DPR_PKG.CALC_CATCHUP(
5407 ret => ret,
5408 BK => bk,
5409 DPR => dpr,
5410 calc_catchup => (start_pd < cpdnum),
5411 x_deprn_exp => l_deprn_exp,
5412 x_bonus_deprn_exp => l_bonus_deprn_exp,
5413 x_impairment_exp => l_impairment_exp,
5414 x_asset_fin_rec_new => l_asset_fin_rec_new,
5415 p_log_level_rec => p_log_level_rec)) then
5416
5417 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
5418 return(FALSE);
5419
5420 end if;
5421
5422 /*
5423 * In order to use the catchup expense from CALC_CATCHUP
5424 * it requires to change cursor in faraje which we don't want to do for now
5425 *
5426
5427 deprn_amt := cost_frac * nvl(l_deprn_exp, 0);
5428 bonus_deprn_amt := cost_frac * nvl(l_bonus_deprn_exp, 0);
5429 impairment_amt := cost_frac * nvl(l_impairment_exp, 0);
5430 */
5431
5432 if p_log_level_rec.statement_level then
5433 fa_debug_pkg.add (l_calling_fn, '+++ Values from CALC_CATCHUP wil replace', 'amounts from faxcde', p_log_level_rec => p_log_level_rec);
5434 fa_debug_pkg.add (l_calling_fn, '+++ l_deprn_exp', l_deprn_exp, p_log_level_rec => p_log_level_rec);
5435 fa_debug_pkg.add (l_calling_fn, '+++ l_bonus_deprn_exp', l_bonus_deprn_exp, p_log_level_rec => p_log_level_rec);
5436 fa_debug_pkg.add (l_calling_fn, '+++ cost_frac', cost_frac, p_log_level_rec => p_log_level_rec);
5437 fa_debug_pkg.add (l_calling_fn, '+++ NEW deprn_amt', deprn_amt, p_log_level_rec => p_log_level_rec);
5438 fa_debug_pkg.add (l_calling_fn, '+++ NEW bonue_deprn_amt', bonus_deprn_amt, p_log_level_rec => p_log_level_rec);
5439 end if;
5440 /*
5441 *
5442 *
5443 */
5444
5445
5446 if ( NOT fagidn( ret,
5447 bk,
5448 deprn_amt,
5449 bonus_deprn_amt,
5450 impairment_amt,
5451 reval_deprn_amt,
5452 reval_amort_amt,
5453 cpd_ctr,
5454 today,
5455 user_id,
5456 p_log_level_rec) ) then
5457
5458 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
5459 return(FALSE);
5460
5461 end if;
5462
5463 else -- no call to calcualte catch
5464 dpr.prior_fy_exp := prior_fy_exp;
5465 dpr.ytd_deprn := ytd_deprn;
5466 dpr.jdate_retired := 0;
5467
5468 -- 1012866. Ret_prorate_jdate cannot be zero. Julian date has to
5469 -- be > 0. I am getting the jdate from the books prorate date
5470 dpr.ret_prorate_jdate := to_char(bk.ret_prorate_date,'J');
5471
5472 deprn_amt := 0;
5473 bonus_deprn_amt := 0;
5474 impairment_amt := 0;
5475 reval_deprn_amt := 0;
5476 reval_amort_amt := 0;
5477
5478
5479 --
5480 -- Following deprn amount calculation is done only if this standalone
5481 -- asset or member asset which group has CALCULATE tracking method.
5482 -- Bug 7486861 Begin
5483 if (not((bk.group_asset_id is not null) and
5484 nvl(bk.tracking_method, 'ALLOCATE') = 'ALLOCATE')) then
5485 dpr.calc_catchup := TRUE;
5486 else
5487 dpr.calc_catchup := FALSE;
5488 end if;
5489
5490 -- Bug 7486861 End
5491 --
5492 -- These depreciation amounts are not important since all we care is
5493 -- financial information
5497 dpr.impairment_rsv := 0;
5494 --
5495 dpr.deprn_rsv := 0;
5496 dpr.bonus_deprn_rsv := 0;
5498 dpr.reval_rsv := 0;
5499 dpr.rsv_known_flag := TRUE;
5500
5501 if (not FA_GAINLOSS_DPR_PKG.CALC_CATCHUP(
5502 ret => ret,
5503 BK => bk,
5504 DPR => dpr,
5505 calc_catchup => dpr.calc_catchup, -- Bug 7486861
5506 x_deprn_exp => l_deprn_exp,
5507 x_bonus_deprn_exp => l_bonus_deprn_exp,
5508 x_impairment_exp => l_impairment_exp,
5509 x_asset_fin_rec_new => l_asset_fin_rec_new, p_log_level_rec => p_log_level_rec)) then
5510 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
5511 return(FALSE);
5512
5513 end if;
5514
5515 if p_log_level_rec.statement_level then
5516 fa_debug_pkg.add (l_calling_fn, '+++ ELSE PART: Values from CALC_CATCHUP','...', p_log_level_rec => p_log_level_rec);
5517 fa_debug_pkg.add (l_calling_fn, '+++ l_deprn_exp', l_deprn_exp, p_log_level_rec => p_log_level_rec);
5518 fa_debug_pkg.add (l_calling_fn, '+++ l_bonus_deprn_exp', l_bonus_deprn_exp, p_log_level_rec => p_log_level_rec);
5519 fa_debug_pkg.add (l_calling_fn, '+++ cost_frac', cost_frac, p_log_level_rec => p_log_level_rec);
5520 fa_debug_pkg.add (l_calling_fn, '+++ NEW deprn_amt', deprn_amt, p_log_level_rec => p_log_level_rec);
5521 fa_debug_pkg.add (l_calling_fn, '+++ NEW bonue_deprn_amt', bonus_deprn_amt, p_log_level_rec => p_log_level_rec);
5522 end if;
5523
5524 deprn_amt := l_deprn_exp;
5525 bonus_deprn_amt := l_bonus_deprn_exp;
5526 impairment_amt := l_impairment_exp;
5527 reval_deprn_amt := 0;
5528 reval_amort_amt := 0;
5529
5530 if p_log_level_rec.statement_level then
5531 fa_debug_pkg.add (l_calling_fn, 'l_asset_fin_rec_new.adjusted_cost (+ +)', l_asset_fin_rec_new.adjusted_cost);
5532 fa_debug_pkg.add (l_calling_fn, 'l_asset_fin_rec_new.rate_adjustment_factor',
5533 l_asset_fin_rec_new.rate_adjustment_factor, p_log_level_rec => p_log_level_rec);
5534 fa_debug_pkg.add (l_calling_fn, 'l_asset_fin_rec_new.formula_factor', l_asset_fin_rec_new.formula_factor, p_log_level_rec => p_log_level_rec);
5535 end if;
5536
5537 if ( NOT fagidn( ret,
5538 bk,
5539 deprn_amt,
5540 bonus_deprn_amt,
5541 impairment_amt,
5542 reval_deprn_amt,
5543 reval_amort_amt,
5544 cpd_ctr,
5545 today,
5546 user_id,
5547 p_log_level_rec) ) then
5548
5549 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
5550 return(FALSE);
5551
5552 end if;
5553
5554 end if;
5555
5556 if p_log_level_rec.statement_level then
5557 fa_debug_pkg.add
5558 (fname => l_calling_fn,
5559 element => '+++ Step 5 ...',
5560 value => '', p_log_level_rec => p_log_level_rec);
5561 end if;
5562 /*Bug#12768930 - modified condition to allow call to fagiav for CIP asset and non sorp book */
5563 if (((ret.wip_asset is null) or (ret.wip_asset <= 0)) or
5564 (ret.wip_asset > 0 and nvl(FA_CACHE_PKG.fazcbc_record.sorp_enabled_flag,'N') = 'N') ) then
5565
5566 if( NOT fagiav( ret,
5567 bk,
5568 cpd_ctr,
5569 today,
5570 user_id,
5571 p_log_level_rec) ) then
5572
5573 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
5574 return(FALSE);
5575
5576 end if;
5577
5578 end if;
5579
5580 if p_log_level_rec.statement_level then
5581 fa_debug_pkg.add
5582 (fname => l_calling_fn,
5583 element => '+++ Step 6',
5584 value => '', p_log_level_rec => p_log_level_rec);
5585 end if;
5586
5587 if (NOT fagict(ret,
5588 bk,
5589 cpd_ctr,
5590 today,
5591 user_id,
5592 p_log_level_rec)) then
5593
5594 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
5595 return(FALSE);
5596
5597 end if;
5598
5599 if p_log_level_rec.statement_level then
5600 fa_debug_pkg.add
5601 (fname => l_calling_fn,
5602 element => '+++ Step 7',
5603 value => '', p_log_level_rec => p_log_level_rec);
5604 end if;
5605
5606 -- insert a new book row with adjusted_cost and cost = l_asset_fin_rec_new.adjusted_cost and cost respectively
5607 if (NOT fagiat(ret,
5608 user_id,
5609 cpd_ctr,
5610 today,
5611 l_asset_fin_rec_new,
5612 p_log_level_rec )) then
5613
5614 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
5615 return(FALSE);
5616
5617 end if;
5618 if( NOT FAGIAVRR( ret,
5619 bk,
5620 reval_reserve,
5621 cpd_ctr,
5625 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
5622 today,
5623 user_id,
5624 p_log_level_rec) ) then
5626 return(FALSE);
5627 end if;
5628 if p_log_level_rec.statement_level then
5629 fa_debug_pkg.add
5630 (fname => l_calling_fn,
5631 element => '+++ Step 7 before fagict_adj',
5632 value => '', p_log_level_rec => p_log_level_rec);
5633 end if;
5634 /*added for the bug 4898842 */
5635
5636 /* need more investigation
5637 if (NOT FAGICT_ADJ(
5638 RET ,
5639 BK ,
5640 cpd_ctr ,
5641 today ,
5642 user_id,
5643 l_asset_fin_rec_new))then
5644 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
5645 return(FALSE);
5646
5647 end if;
5648 */
5649 if p_log_level_rec.statement_level then
5650 fa_debug_pkg.add
5651 (fname => l_calling_fn,
5652 element => '+++ Step 7 after fagict_adj',
5653 value => '', p_log_level_rec => p_log_level_rec);
5654 end if;
5655
5656
5657 if p_log_level_rec.statement_level then
5658 fa_debug_pkg.add
5659 (fname => l_calling_fn,
5660 element => '+++ Step 8',
5661 value => '', p_log_level_rec => p_log_level_rec);
5662 end if;
5663
5664 -- modified the call to fagtax such that it will be called only once for bug no.3831503
5665 if(ret.mrc_sob_type_code<>'R') then
5666 if (NOT fagtax(ret,
5667 bk,
5668 today,
5669 p_log_level_rec)) then
5670
5671 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
5672 return(FALSE);
5673
5674 end if;
5675 end if; --ret.mrc_sob_type_code<>'R'
5676 if p_log_level_rec.statement_level then
5677 fa_debug_pkg.add
5678 (fname => l_calling_fn,
5679 element => '+++ Step 9',
5680 value => '', p_log_level_rec => p_log_level_rec);
5681 end if;
5682
5683 if (NOT fagiar(ret,
5684 bk,
5685 cpd_ctr,
5686 user_id,
5687 today,
5688 p_log_level_rec )) then
5689
5690 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
5691 return(FALSE);
5692
5693 end if;
5694
5695 if p_log_level_rec.statement_level then
5696 fa_debug_pkg.add
5697 (fname => l_calling_fn,
5698 element => '+++ Step 10',
5699 value => '', p_log_level_rec => p_log_level_rec);
5700 end if;
5701
5702 if (bk.group_asset_id is not null) then
5703 -- +++++ Process Group Asse +++++
5704 if not FA_RETIREMENT_PVT.Do_Reinstatement_in_CGL(
5705 p_ret => ret,
5706 p_bk => bk,
5707 p_dpr => dpr,
5708 p_mrc_sob_type_code => ret.mrc_sob_type_code,
5709 p_calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec) then
5710 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
5711 return false;
5712 end if;
5713 end if; -- (bk.group_asset_id is not null)
5714
5715 if p_log_level_rec.statement_level then
5716 fa_debug_pkg.add
5717 (fname => l_calling_fn,
5718 element => '+++ Step 11',
5719 value => '', p_log_level_rec => p_log_level_rec);
5720 end if;
5721
5722 return(true);
5723
5724 END; -- fagrin
5725
5726 /* Added for bug 7396397 */
5727 function process_adj_table(p_mode IN VARCHAR2, RET IN fa_ret_types.ret_struct,
5728 BK IN fa_ret_types.book_struct,
5729 p_tbl_adj IN OUT NOCOPY tbl_adj,
5730 p_tbl_ret IN OUT NOCOPY tbl_ret,
5731 p_tbl_cost_ret IN OUT NOCOPY tbl_cost_ret,
5732 p_tbl_adj_final IN OUT NOCOPY tbl_final_adj, p_log_level_rec IN FA_API_TYPES.log_level_rec_type) return boolean is
5733 l_final_ind number := p_tbl_adj_final.count;
5734 l_calling_fn varchar2(40) := 'FA_GAINLOSS_UND_PKG.pradjtbl';
5735 l_asset_id fa_books.asset_id%type;
5736
5737 /* Cursor of all active distributions */
5738 cursor c_active_dist is
5739 select distribution_id,
5740 code_combination_id,
5741 units_assigned
5742 from fa_distribution_history
5743 where asset_id = l_asset_id
5744 and date_ineffective is null
5745 order by distribution_id;
5746
5747 type tbl_active_dist is table of c_active_dist%rowtype index by BINARY_INTEGER;
5748 l_tbl_active_dist tbl_active_dist;
5749 l_total_active_dist number :=0;
5750
5751 Begin
5752
5753 if (p_log_level_rec.statement_level) then
5754 fa_debug_pkg.add
5755 (fname => l_calling_fn,
5756 element => 'Entered with mode',
5757 value => p_mode, p_log_level_rec => p_log_level_rec);
5758
5759 fa_debug_pkg.add
5760 (fname => l_calling_fn,
5761 element => 'BK.dis_book',
5765 (fname => l_calling_fn,
5762 value => BK.dis_book, p_log_level_rec => p_log_level_rec);
5763
5764 fa_debug_pkg.add
5766 element => 'ret.th_id_in',
5767 value => ret.th_id_in, p_log_level_rec => p_log_level_rec);
5768
5769 fa_debug_pkg.add
5770 (fname => l_calling_fn,
5771 element => 'ret.th_id_in',
5772 value => ret.th_id_in, p_log_level_rec => p_log_level_rec);
5773
5774 fa_debug_pkg.add
5775 (fname => l_calling_fn,
5776 element => 'p_tbl_adj.count',
5777 value => p_tbl_adj.count, p_log_level_rec => p_log_level_rec);
5778
5779 fa_debug_pkg.add
5780 (fname => l_calling_fn,
5781 element => 'p_tbl_ret.count',
5782 value => p_tbl_ret.count, p_log_level_rec => p_log_level_rec);
5783
5784 fa_debug_pkg.add
5785 (fname => l_calling_fn,
5786 element => 'p_tbl_cost_ret.count',
5787 value => p_tbl_cost_ret.count, p_log_level_rec => p_log_level_rec);
5788
5789 fa_debug_pkg.add
5790 (fname => l_calling_fn,
5791 element => 'ret.units_retired',
5792 value => ret.units_retired, p_log_level_rec => p_log_level_rec);
5793
5794 end if;
5795
5796 if (p_log_level_rec.statement_level) then
5797 fa_debug_pkg.add
5798 (fname => l_calling_fn,
5799 element => 'entering retirement table loop',
5800 value => '', p_log_level_rec => p_log_level_rec);
5801 end if;
5802
5803 if ( (nvl(ret.units_retired,0) > 0 ) and (p_mode <> 'GROUP')) then
5804 for j in 1..p_tbl_adj.count
5805 loop
5806 declare
5807 bln_qry_bal boolean;
5808 l_dummy_dum number;
5809 l_dummy_char varchar2(4000);
5810
5811 begin
5812
5813 if (p_log_level_rec.statement_level) then
5814 fa_debug_pkg.add
5815 (fname => l_calling_fn,
5816 element => 'Calling query balance for dist_id',
5817 value => p_tbl_adj(j).distribution_id);
5818 end if;
5819
5820
5821 FA_QUERY_BALANCES_PKG.QUERY_BALANCES
5822 (X_ASSET_ID => ret.asset_id,
5823 X_BOOK => bk.dis_book,
5824 X_PERIOD_CTR => 0,
5825 X_DIST_ID => p_tbl_adj(j).distribution_id,
5826 X_RUN_MODE => 'STANDARD',
5827 X_COST => p_tbl_adj(j).cost,
5828 X_DEPRN_RSV =>p_tbl_adj(j).DEPRN_RSV,
5829 X_REVAL_RSV => p_tbl_adj(j).REVAL_RSV,
5830 X_YTD_DEPRN =>l_dummy_dum,
5831 X_YTD_REVAL_EXP =>l_dummy_dum,
5832 X_REVAL_DEPRN_EXP =>l_dummy_dum,
5833 X_DEPRN_EXP =>l_dummy_dum,
5834 X_REVAL_AMO =>l_dummy_dum,
5835 X_PROD =>l_dummy_dum,
5836 X_YTD_PROD =>l_dummy_dum,
5837 X_LTD_PROD =>l_dummy_dum,
5838 X_ADJ_COST =>l_dummy_dum,
5839 X_REVAL_AMO_BASIS =>l_dummy_dum,
5840 X_BONUS_RATE =>l_dummy_dum,
5841 X_DEPRN_SOURCE_CODE =>l_dummy_char,
5842 X_ADJUSTED_FLAG => bln_qry_bal,
5843 X_TRANSACTION_HEADER_ID => -1,
5844 X_BONUS_DEPRN_RSV => p_tbl_adj(j).BONUS_DEPRN_RSV,
5845 X_BONUS_YTD_DEPRN => l_dummy_dum,
5846 X_BONUS_DEPRN_AMOUNT =>l_dummy_dum,
5847 X_IMPAIRMENT_RSV => p_tbl_adj(j).IMPAIRMENT_RSV,
5848 X_YTD_IMPAIRMENT => l_dummy_dum,
5849 X_IMPAIRMENT_AMOUNT =>l_dummy_dum,
5850 X_capital_adjustment => l_dummy_dum,
5851 X_general_fund => l_dummy_dum,
5852 X_mrc_sob_type_code => ret.mrc_sob_type_code,
5853 X_set_of_books_id => ret.set_of_books_id,
5854 p_log_level_rec => p_log_level_rec);
5855
5856 if (p_log_level_rec.statement_level) then
5857 fa_debug_pkg.add
5858 (fname => l_calling_fn,
5859 element => 'Query balance called for dist_id',
5860 value => p_tbl_adj(j).distribution_id);
5861
5862 fa_debug_pkg.add
5863 (fname => l_calling_fn,
5864 element => 'cost balance',
5865 value => p_tbl_adj(j).cost);
5866
5867 fa_debug_pkg.add
5868 (fname => l_calling_fn,
5869 element => 'Reserve balance',
5870 value => p_tbl_adj(j).DEPRN_RSV);
5871
5872 end if;
5873 for i in 1..p_tbl_ret.count
5874 loop
5875 if (p_tbl_ret(i).code_combination_id = p_tbl_adj(j).code_combination_id
5876 and p_tbl_ret(i).location_id = p_tbl_adj(j).location_id
5877 and nvl(p_tbl_ret(i).assigned_to,-99) = nvl(p_tbl_adj(j).assigned_to,-99)
5878 ) then
5879
5880 if (p_log_level_rec.statement_level) then
5881 fa_debug_pkg.add
5882 (fname => l_calling_fn,
5883 element => 'ret adjustment_amount',
5884 value => p_tbl_ret(i).adjustment_amount);
5885
5886 fa_debug_pkg.add
5887 (fname => l_calling_fn,
5888 element => 'found distribution in retirement table',
5889 value => p_tbl_ret(i).distribution_id);
5890
5891 fa_debug_pkg.add
5892 (fname => l_calling_fn,
5893 element => 'ret adjustment_type',
5897
5894 value => p_tbl_ret(i).adjustment_type);
5895
5896 end if;
5898 declare
5899 bln_create_rec boolean;
5900 l_old_cost fa_adjustments.adjustment_amount%type;
5901 l_new_cost fa_adjustments.adjustment_amount%type;
5902 l_dr_cr_bal VARCHAR2(2);
5903 l_rev_dr_cr_bal VARCHAR2(2);
5904 begin
5905 l_old_cost := 0;
5906 l_new_cost := 0;
5907 p_tbl_adj(j).retire_rec_found := 'Y';
5908 if (p_tbl_ret(i).adjustment_type = 'COST') then
5909 l_old_cost := p_tbl_adj(j).cost;
5910 l_dr_cr_bal := 'DR';
5911 l_rev_dr_cr_bal := 'CR';
5912 elsif (p_tbl_ret(i).adjustment_type = 'RESERVE') then
5913 l_old_cost := p_tbl_adj(j).DEPRN_RSV;
5914 l_dr_cr_bal := 'CR';
5915 l_rev_dr_cr_bal := 'DR';
5916 elsif (p_tbl_ret(i).adjustment_type = 'BONUS RESERVE') then
5917 l_old_cost := p_tbl_adj(j).BONUS_DEPRN_RSV;
5918 l_dr_cr_bal := 'CR';
5919 l_rev_dr_cr_bal := 'DR';
5920 elsif (p_tbl_ret(i).adjustment_type = 'REVAL RESERVE') then
5921 l_old_cost := p_tbl_adj(j).REVAL_RSV;
5922 l_dr_cr_bal := 'CR';
5923 l_rev_dr_cr_bal := 'DR';
5924 elsif (p_tbl_ret(i).adjustment_type = 'IMPAIR RESERVE') then
5925 l_old_cost := p_tbl_adj(j).IMPAIRMENT_RSV;
5926 l_dr_cr_bal := 'CR';
5927 l_rev_dr_cr_bal := 'DR';
5928 end if;
5929
5930
5931 if (l_old_cost <> 0) then
5932
5933 -- Add amount during retirement to adjustment amount
5934 select nvl(l_old_cost,0)
5935 +
5936 decode(p_tbl_ret(i).debit_credit_flag,l_dr_cr_bal, -1*p_tbl_ret(i).adjustment_amount,
5937 p_tbl_ret(i).adjustment_amount)
5938 into l_new_cost
5939 from dual;
5940 p_tbl_ret(i).adj_rec_found := 'Y';
5941 l_final_ind := l_final_ind+1;
5942 p_tbl_adj_final(l_final_ind).asset_id := ret.asset_id;
5943 p_tbl_adj_final(l_final_ind).dist_id := p_tbl_adj(j).distribution_id;
5944 p_tbl_adj_final(l_final_ind).ccid := p_tbl_ret(i).adj_ccid;
5945 p_tbl_adj_final(l_final_ind).adj_type := p_tbl_ret(i).adjustment_type;
5946 p_tbl_adj_final(l_final_ind).cost := l_old_cost;
5947 p_tbl_adj_final(l_final_ind).dr_cr := l_rev_dr_cr_bal;
5948
5949 if (p_log_level_rec.statement_level) then
5950 fa_debug_pkg.add
5951 (fname => l_calling_fn,
5952 element => 'contents of p_tbl_adj_final for old cost',
5953 value => '', p_log_level_rec => p_log_level_rec);
5954
5955 fa_debug_pkg.add
5956 (fname => l_calling_fn,
5957 element => 'p_tbl_adj_final(l_final_ind).dist_id',
5958 value => p_tbl_adj_final(l_final_ind).dist_id);
5959
5960 fa_debug_pkg.add
5961 (fname => l_calling_fn,
5962 element => 'p_tbl_adj_final(l_final_ind).ccid',
5963 value => p_tbl_adj_final(l_final_ind).ccid);
5964
5965 fa_debug_pkg.add
5966 (fname => l_calling_fn,
5967 element => 'p_tbl_adj_final(l_final_ind).adj_type',
5968 value => p_tbl_adj_final(l_final_ind).adj_type);
5969
5970 fa_debug_pkg.add
5971 (fname => l_calling_fn,
5972 element => 'p_tbl_adj_final(l_final_ind).cost',
5973 value => p_tbl_adj_final(l_final_ind).cost);
5974
5975 fa_debug_pkg.add
5976 (fname => l_calling_fn,
5977 element => 'p_tbl_adj_final(l_final_ind).dr_cr',
5978 value => p_tbl_adj_final(l_final_ind).dr_cr);
5979 end if;
5980
5981 end if;
5982
5983 if (p_log_level_rec.statement_level) then
5984 fa_debug_pkg.add
5985 (fname => l_calling_fn,
5986 element => 'Population of old cost into p_tbl_adj_final done',
5987 value => '', p_log_level_rec => p_log_level_rec);
5988 end if;
5989
5990 if (l_new_cost <> 0) then
5991 l_final_ind := l_final_ind+1;
5992 p_tbl_adj_final(l_final_ind).asset_id := ret.asset_id;
5993 p_tbl_adj_final(l_final_ind).dist_id := p_tbl_ret(i).distribution_id;
5994 p_tbl_adj_final(l_final_ind).ccid := p_tbl_ret(i).adj_ccid;
5995 p_tbl_adj_final(l_final_ind).adj_type := p_tbl_ret(i).adjustment_type;
5996 p_tbl_adj_final(l_final_ind).cost := l_new_cost;
5997 p_tbl_adj_final(l_final_ind).dr_cr := l_dr_cr_bal;
5998
5999 if (p_log_level_rec.statement_level) then
6000 fa_debug_pkg.add
6001 (fname => l_calling_fn,
6005 fa_debug_pkg.add
6002 element => 'contents of p_tbl_adj_final for new cost',
6003 value => '', p_log_level_rec => p_log_level_rec);
6004
6006 (fname => l_calling_fn,
6007 element => 'p_tbl_adj_final(l_final_ind).dist_id',
6008 value => p_tbl_adj_final(l_final_ind).dist_id);
6009
6010 fa_debug_pkg.add
6011 (fname => l_calling_fn,
6012 element => 'p_tbl_adj_final(l_final_ind).ccid',
6013 value => p_tbl_adj_final(l_final_ind).ccid);
6014
6015 fa_debug_pkg.add
6016 (fname => l_calling_fn,
6017 element => 'p_tbl_adj_final(l_final_ind).adj_type',
6018 value => p_tbl_adj_final(l_final_ind).adj_type);
6019
6020 fa_debug_pkg.add
6021 (fname => l_calling_fn,
6022 element => 'p_tbl_adj_final(l_final_ind).cost',
6023 value => p_tbl_adj_final(l_final_ind).cost);
6024
6025 fa_debug_pkg.add
6026 (fname => l_calling_fn,
6027 element => 'p_tbl_adj_final(l_final_ind).dr_cr',
6028 value => p_tbl_adj_final(l_final_ind).dr_cr);
6029 end if; --p_log_level_rec.statement_level
6030 end if; -- l_new_cost <> 0
6031
6032 if (p_log_level_rec.statement_level) then
6033 fa_debug_pkg.add
6034 (fname => l_calling_fn,
6035 element => 'Population of New cost into p_tbl_adj_final done',
6036 value => '', p_log_level_rec => p_log_level_rec);
6037 end if;
6038
6039 end;
6040 end if;
6041 end loop; --p_tbl_ret.count
6042 Exception
6043 when others then
6044 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
6045 if (p_log_level_rec.statement_level) then
6046 fa_debug_pkg.add
6047 (fname => l_calling_fn,
6048 element => 'Error occured',
6049 value => SQLERRM, p_log_level_rec => p_log_level_rec);
6050 end if;
6051
6052 return false;
6053 end;
6054 end loop; -- p_tbl_adj.count
6055
6056 if (p_log_level_rec.statement_level) then
6057 fa_debug_pkg.add
6058 (fname => l_calling_fn,
6059 element => 'Adjustment table loop done',
6060 value => '', p_log_level_rec => p_log_level_rec);
6061 end if;
6062
6063 -- Now insert records retirements not having any records in adjustment
6064 for k in 1..p_tbl_ret.count
6065 loop
6066 if (p_tbl_ret(k).adj_rec_found = 'N' and p_tbl_ret(k).adjustment_amount <> 0) then
6067 l_final_ind := l_final_ind+1;
6068 p_tbl_adj_final(l_final_ind).asset_id := ret.asset_id;
6069 p_tbl_adj_final(l_final_ind).dist_id := p_tbl_ret(k).distribution_id;
6070 p_tbl_adj_final(l_final_ind).ccid := p_tbl_ret(k).adj_ccid;
6071 p_tbl_adj_final(l_final_ind).adj_type := p_tbl_ret(k).adjustment_type;
6072 p_tbl_adj_final(l_final_ind).cost := p_tbl_ret(k).adjustment_amount;
6073 if (p_tbl_ret(k).debit_credit_flag = 'CR') then
6074 p_tbl_adj_final(l_final_ind).dr_cr := 'DR';
6075 else
6076 p_tbl_adj_final(l_final_ind).dr_cr := 'CR';
6077 end if;
6078 end if;
6079 end loop;
6080 end if;
6081 /* following will be used in case of cost retirement and for groups */
6082 if (nvl(ret.units_retired,0) = 0 or (p_mode = 'GROUP')
6083 ) then
6084
6085 if (p_log_level_rec.statement_level) then
6086 fa_debug_pkg.add
6087 (fname => l_calling_fn,
6088 element => 'Entered partial cost retirement scenario',
6089 value => p_tbl_adj.count, p_log_level_rec => p_log_level_rec);
6090 end if;
6091
6092 if p_mode = 'GROUP' then
6093 l_asset_id := bk.group_asset_id;
6094 else
6095 l_asset_id := ret.asset_id;
6096 end if;
6097
6098 open c_active_dist;
6099 fetch c_active_dist bulk collect into l_tbl_active_dist;
6100 close c_active_dist;
6101
6102 for l in 1..l_tbl_active_dist.count
6103 loop
6104 l_total_active_dist := l_total_active_dist+l_tbl_active_dist(l).units_assigned;
6105 end loop;
6106
6107 if (p_log_level_rec.statement_level) then
6108 fa_debug_pkg.add
6109 (fname => l_calling_fn,
6110 element => 'total_active_dist',
6111 value => l_total_active_dist, p_log_level_rec => p_log_level_rec);
6112 end if;
6113
6114 for i in 1..p_tbl_cost_ret.count
6115 loop
6116 declare
6117 l_adj_type_total fa_adjustments.adjustment_amount%type :=0 ;
6118 begin
6119 if (p_log_level_rec.statement_level) then
6120 fa_debug_pkg.add
6121 (fname => l_calling_fn,
6122 element => 'adjustment_type',
6123 value =>p_tbl_cost_ret(i).adjustment_type);
6124
6125 fa_debug_pkg.add
6126 (fname => l_calling_fn,
6127 element => 'adjustment_type',
6128 value =>p_tbl_cost_ret(i).adjustment_amount);
6132 loop
6129
6130 end if;
6131 for j in 1..l_tbl_active_dist.count
6133 if (p_log_level_rec.statement_level) then
6134 fa_debug_pkg.add
6135 (fname => l_calling_fn,
6136 element => 'distribution_id',
6137 value =>l_tbl_active_dist(j).distribution_id);
6138
6139 fa_debug_pkg.add
6140 (fname => l_calling_fn,
6141 element => 'units_assigned',
6142 value =>l_tbl_active_dist(j).units_assigned);
6143
6144 end if;
6145
6146 l_final_ind := l_final_ind+1;
6147 p_tbl_adj_final(l_final_ind).asset_id := l_asset_id;
6148 p_tbl_adj_final(l_final_ind).dist_id := l_tbl_active_dist(j).distribution_id;
6149 p_tbl_adj_final(l_final_ind).ccid := p_tbl_cost_ret(i).adj_ccid;
6150 p_tbl_adj_final(l_final_ind).adj_type := p_tbl_cost_ret(i).adjustment_type;
6151 p_tbl_adj_final(l_final_ind).dr_cr := p_tbl_cost_ret(i).rev_debit_credit_flag;
6152
6153 if j < l_tbl_active_dist.count then
6154 p_tbl_adj_final(l_final_ind).cost := p_tbl_cost_ret(i).adjustment_amount*
6155 (l_tbl_active_dist(j).units_assigned/l_total_active_dist);
6156 if not FA_UTILS_PKG.faxrnd(p_tbl_adj_final(l_final_ind).cost, ret.book, ret.set_of_books_id, p_log_level_rec) then
6157 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
6158 return(FALSE);
6159 end if;
6160 l_adj_type_total := l_adj_type_total + p_tbl_adj_final(l_final_ind).cost;
6161 else
6162 p_tbl_adj_final(l_final_ind).cost := p_tbl_cost_ret(i).adjustment_amount - l_adj_type_total;
6163 end if;
6164 end loop;
6165 end;
6166 end loop;
6167
6168 end if;
6169 return true;
6170 end process_adj_table;
6171 /*Bug13727632 - Added new function to move reval reserve from old to new distributions
6172 when reinstated full retirement and retire_reval_reserve_flag is NO */
6173 Function FAGIAVRR(
6174 RET IN OUT NOCOPY fa_ret_types.ret_struct,
6175 BK IN OUT NOCOPY fa_ret_types.book_struct,
6176 reval_reserve IN number,
6177 cpd_ctr IN number,
6178 today IN date,
6179 user_id IN number,
6180 p_log_level_rec IN FA_API_TYPES.log_level_rec_type) return boolean is
6181
6182 adj_row fa_adjust_type_pkg.fa_adj_row_struct ;
6183 H_DPR_ROW FA_STD_TYPES.fa_deprn_row_struct;
6184 h_th_id_out number;
6185 h_th_type_code varchar2(25);
6186
6187 h_dist_book varchar2(30);
6188 h_success boolean;
6189 h_distribution_id number;
6190 h_trans_units number;
6191 h_dist_ccid number;
6192 h_adj_type varchar2(20);
6193 h_mesg_name varchar2(30);
6194 h_row_ctr number:=0;
6195 h_total_amount number:=0;
6196
6197 CURSOR C1 IS
6198 SELECT DISTRIBUTION_ID,
6199 CODE_COMBINATION_ID,
6200 TRANSACTION_UNITS
6201 FROM FA_DISTRIBUTION_HISTORY
6202 WHERE ASSET_ID = RET.asset_id
6203 AND BOOK_TYPE_CODE = h_dist_book
6204 AND RETIREMENT_ID = RET.retirement_id
6205 ORDER BY DISTRIBUTION_ID;
6206
6207 X_LAST_UPDATE_DATE date := sysdate;
6208 X_last_updated_by number := -1;
6209 X_last_update_login number := -1;
6210
6211 FAGIAVRR_ERROR exception;
6212 l_calling_fn varchar2(30) := 'fa_gainloss_und_pkg.fagiavrr';
6213
6214 begin
6215 if (p_log_level_rec.statement_level) then
6216 fa_debug_pkg.add (fname => l_calling_fn,
6217 element => 'fa_cache_pkg.fazcbc_record.RETIRE_REVAL_RESERVE_FLAG',
6218 value => fa_cache_pkg.fazcbc_record.RETIRE_REVAL_RESERVE_FLAG,
6219 p_log_level_rec => p_log_level_rec);
6220 fa_debug_pkg.add (fname => l_calling_fn,
6221 element => 'reval_reserve',
6222 value => reval_reserve,
6223 p_log_level_rec => p_log_level_rec);
6224 fa_debug_pkg.add (fname => l_calling_fn,
6225 element => 'ret.retirement_id',
6226 value => ret.retirement_id,
6227 p_log_level_rec => p_log_level_rec);
6228 fa_debug_pkg.add (fname => l_calling_fn,
6229 element => 'bk.book_class',
6230 value => bk.book_class,
6231 p_log_level_rec => p_log_level_rec);
6232 end if;
6233
6234 if ( not (nvl(fa_cache_pkg.fazcbc_record.RETIRE_REVAL_RESERVE_FLAG,'NO') = 'NO'
6235 and reval_reserve <> 0 and (not bk.book_class))) then
6236 return true;
6237 end if;
6238 begin
6239 select transaction_header_id_out,transaction_type_code
6240 into h_th_id_out,h_th_type_code
6241 from fa_retirements rt,
6242 fa_transaction_headers fth
6243 where retirement_id = ret.retirement_id
6244 and rt.transaction_header_id_IN = fth.transaction_header_id
6245 and rt.asset_id = fth.asset_id
6246 and rt.book_type_code = fth.book_type_code;
6247 exception
6248 when no_data_found then
6249 raise fagiavrr_error;
6250 end;
6251 if h_th_type_code <> 'FULL RETIREMENT' THEN
6252 return true;
6253 end if;
6254
6255 h_dist_book := FA_CACHE_PKG.fazcbc_record.distribution_source_book;
6256 --initialize common attributes of adj_row
6257 adj_row.transaction_header_id := h_th_id_out;
6258 if ret.wip_asset > 0 then
6259 adj_row.source_type_code := 'CIP RETIREMENT';
6260 else
6261 adj_row.source_type_code := 'RETIREMENT';
6262 end if;
6263 adj_row.adjustment_type := 'REVAL RESERVE';
6264 adj_row.book_type_code := RET.book;
6265 adj_row.asset_id := RET.asset_id;
6266 adj_row.period_counter_created := cpd_ctr;
6267 adj_row.period_counter_adjusted := cpd_ctr;
6268 adj_row.asset_invoice_id := 0;
6269 adj_row.last_update_date := today;
6270 adj_row.current_units := bk.cur_units;
6271 adj_row.units_retired :=ret.units_retired;
6272 adj_row.account := fa_cache_pkg.fazccb_record.REVAL_RESERVE_ACCT;
6273 adj_row.account_type := 'REVAL_RESERVE_ACCT';
6274 adj_row.mrc_sob_type_code := ret.mrc_sob_type_code;
6275 adj_row.set_of_books_id := ret.set_of_books_id;
6276 adj_row.selection_mode := fa_std_types.FA_AJ_SINGLE;
6277 adj_row.flush_adj_flag := TRUE;
6278 adj_row.leveling_flag := FALSE;
6279 adj_row.gen_ccid_flag := TRUE;
6280 adj_row.selection_thid := 0;
6281 adj_row.selection_retid := 0;
6282 adj_row.adjustment_amount := 0;
6283 adj_row.debit_credit_flag := 'DR';
6284 if (bk.group_asset_id is not null) and
6285 (nvl(bk.member_rollup_flag, 'N') = 'N') then
6286 adj_row.track_member_flag := 'Y';
6287 else
6288 adj_row.track_member_flag := null;
6289 end if;
6290
6291 --Reverse reval reserve from retired distributions.
6292 for distn IN C1 loop
6293 h_distribution_id := distn.distribution_id;
6294 h_mesg_name := 'FA_INS_ADJ_FCUR_C1';
6295 h_dist_ccid := distn.code_combination_id;
6296 h_trans_units := distn.transaction_units;
6297 h_row_ctr := h_row_ctr+1;
6298 H_DPR_ROW.asset_id := adj_row.asset_id;
6299 H_DPR_ROW.book := adj_row.book_type_code;
6300 H_DPR_ROW.dist_id := h_distribution_id;
6301 H_DPR_ROW.period_ctr:= 0;
6302 H_DPR_ROW.mrc_sob_type_code := adj_row.mrc_sob_type_code;
6303 H_DPR_ROW.set_of_books_id := adj_row.set_of_books_id;
6304
6305 FA_QUERY_BALANCES_PKG.QUERY_BALANCES_INT(H_DPR_ROW,
6306 'STANDARD',
6307 FALSE,
6308 H_SUCCESS,
6309 -1,
6310 p_log_level_rec => p_log_level_rec);
6311 if not h_success then
6312 FA_SRVR_MSG.ADD_MESSAGE(CALLING_FN => l_calling_fn,p_log_level_rec => p_log_level_rec);
6313 return FALSE;
6314 end if;
6315
6316 if H_DPR_ROW.reval_rsv <> 0 then
6317 adj_row.adjustment_amount := H_DPR_ROW.reval_rsv ;
6318 adj_row.distribution_id := h_distribution_id;
6319 adj_row.code_combination_id := h_dist_ccid;
6320
6321 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
6322 X_last_update_date,
6323 X_last_updated_by,
6324 X_last_update_login,
6325 p_log_level_rec => p_log_level_rec)) then
6326
6327 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
6328 return(FALSE);
6329 end if;
6330 end if;
6331 end loop;
6332
6333 --Allocate reval reserve amount to new distributions.
6334 adj_row.selection_mode := fa_std_types.FA_AJ_ACTIVE;
6335 adj_row.debit_credit_flag := 'CR';
6336 adj_row.adjustment_amount := reval_reserve;
6337
6338 if (NOT FA_INS_ADJUST_PKG.faxinaj(adj_row,
6339 X_last_update_date,
6340 X_last_updated_by,
6341 X_last_update_login
6342 , p_log_level_rec => p_log_level_rec)) then
6343
6344 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
6345 return(FALSE);
6346 end if;
6347 return true;
6348 exception
6349 when others then
6350 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
6351 return FALSE;
6352 end FAGIAVRR;
6353
6354 END FA_GAINLOSS_UND_PKG; -- End of Package