[Home] [Help]
PACKAGE BODY: APPS.FA_GAINLOSS_RET_PKG
Source
1 PACKAGE BODY FA_GAINLOSS_RET_PKG AS
2 /* $Header: fagretb.pls 120.23.12020000.3 2012/07/24 09:01:16 spooyath ship $*/
3
4 g_grp_trx_hdr_id number;
5
6 -- Bug 5525968: Added following function
7 function faggin(ret in out nocopy FA_RET_TYPES.ret_struct,
8 bk in out nocopy FA_RET_TYPES.book_struct, p_log_level_rec IN FA_API_TYPES.log_level_rec_type) return boolean is
9
10 faggin_err exception;
11 h_depreciate_lastyr integer;
12 h_lifemonths number(4);
13 h_capitalize integer;
14 h_depreciate integer;
15 h_fully_reserved integer;
16 h_itc_used number;
17 h_period_num integer;
18 h_rate_source_rule integer;
19 h_deprn_basis_rule integer;
20 h_book_class integer;
21 h_wip_asset integer;
22 h_depr_first_year_ret integer;
23 h_cur_units number(15);
24 h_asset_id number(15);
25 h_retirement_id number(15);
26 h_cpd_fiscal_year number(4);
27 h_ret_fiscalyr number(15);
28 h_method_code varchar2(12);
29 h_jdis number;
30 h_prorate_jdate number;
31 h_deprn_start_jdate number;
32 h_raf number;
33 h_adj_rate number;
34 h_adjusted_cost number;
35 h_current_cost number;
36 h_recoverable_cost number;
37 h_salvage_value number;
38 h_itc_amount number;
39 h_ret_p_conv varchar2(15);
40 h_book varchar2(30);
41 h_dis_book varchar2(30);
42 h_prorate_date date;
43 h_deprn_start_date date;
44 h_date_in_srv date;
45 h_p_cal varchar2(15);
46 h_deprn_cal varchar2(15);
47 h_ret_prorate_date date;
48 h_initial_date date;
49 h_date_retired date;
50 h_ceiling_name varchar2(30);
51 h_bonus_rule varchar2(30);
52 h_dwacq integer;
53 h_same_fy integer;
54 h_reval_amort_basis number;
55 h_unrevalued_cost number;
56 h_adj_capacity number;
57 h_capacity number;
58 h_fiscal_year_name varchar2(30);
59 h_deprn_reserve number;
60 h_adj_rec_cost number;
61 h_annual_deprn_rounding_flag integer; -- NULL->0, 'ADD'->1, 'ADJ'->2
62 -- 'RET'->3, 'REV'->4, 'TRF'->5
63 -- 'RES' ->6, Others->-1
64 h_short_fiscal_year_flag varchar2(3);
65 h_conversion_date date;
66 h_orig_deprn_start_date date;
67 h_old_adj_cost number;
68 h_formula_factor number;
69 h_allowed_deprn_limit_amount number;
70 -- +++++ Group Asset related information +++++
71 h_group_asset_id FA_BOOKS.group_asset_id%type;
72 h_recognize_gain_loss FA_BOOKS.RECOGNIZE_GAIN_LOSS%TYPE;
73 h_recapture_reserve_flag FA_BOOKS.RECAPTURE_RESERVE_FLAG%TYPE;
74 h_limit_proceeds_flag FA_BOOKS.LIMIT_PROCEEDS_FLAG%TYPE;
75 h_terminal_gain_loss FA_BOOKS.TERMINAL_GAIN_LOSS%TYPE;
76 h_tracking_method FA_BOOKS.TRACKING_METHOD%TYPE;
77 h_exclude_fully_rsv_flag FA_BOOKS.EXCLUDE_FULLY_RSV_FLAG%TYPE;
78 h_excess_allocation_option FA_BOOKS.EXCESS_ALLOCATION_OPTION%TYPE;
79 h_depreciation_option FA_BOOKS.DEPRECIATION_OPTION%TYPE;
80 h_member_rollup_flag FA_BOOKS.MEMBER_ROLLUP_FLAG%TYPE;
81 h_ltd_proceeds FA_BOOKS.LTD_PROCEEDS%TYPE;
82 h_allocate_to_fully_rsv_flag FA_BOOKS.ALLOCATE_TO_FULLY_RSV_FLAG%TYPE;
83 h_allocate_to_fully_ret_flag FA_BOOKS.ALLOCATE_TO_FULLY_RET_FLAG%TYPE;
84 h_eofy_reserve FA_BOOKS.EOFY_RESERVE%TYPE;
85 h_cip_cost FA_BOOKS.CIP_COST%TYPE;
86 h_ltd_cost_of_removal FA_BOOKS.LTD_COST_OF_REMOVAL%TYPE;
87 h_prior_eofy_reserve FA_BOOKS.PRIOR_EOFY_RESERVE%TYPE;
88 h_eop_adj_cost FA_BOOKS.EOP_ADJ_COST%TYPE;
89 h_eop_formula_factor FA_BOOKS.EOP_FORMULA_FACTOR%TYPE;
90 h_exclude_proceeds_from_basis FA_BOOKS.EXCLUDE_PROCEEDS_FROM_BASIS%TYPE;
91 h_retirement_deprn_option FA_BOOKS.RETIREMENT_DEPRN_OPTION%TYPE;
92 h_terminal_gain_loss_amount FA_BOOKS.terminal_gain_loss_amount%type;
93 h_pc_fully_reserved number;
94 --Bug#6920756
95 --h_fully_extended FA_BOOKS.EXTENDED_DEPRN_FLAG%Type; -- bug 6913897
96 --h_extended_deprn_flag NUMBER(1); -- bug 6913897
97 h_fully_extended integer;
98 h_pc_fully_extended number;
99 h_extended_flag integer;
100
101 l_calling_fn varchar2(40) := 'fa_gainloss_ret_pkg.faggin';
102
103 BEGIN <<FAGGIN>>
104
105 if p_log_level_rec.statement_level then fa_debug_pkg.add(l_calling_fn, 'in faggin 1', '', p_log_level_rec => p_log_level_rec); end if;
106 h_adj_capacity := 0;
107 h_capacity := 0;
108 h_unrevalued_cost := 0;
109 h_reval_amort_basis := 0;
110 h_raf := 0;
111 h_adj_rate := 0;
112 h_adjusted_cost := 0;
113 h_current_cost := 0;
114 h_recoverable_cost := 0;
115 h_salvage_value := 0;
116 h_itc_amount := 0;
117 h_adj_rec_cost := 0;
118 h_old_adj_cost := 0;
119 h_formula_factor := 0;
120
121 h_asset_id := ret.asset_id;
122 h_retirement_id := ret.retirement_id;
123
124 h_date_retired := ret.date_retired;
125 h_book := ret.book;
126 h_ret_p_conv := ret.prorate_convention;
127 h_dwacq := 0;
128
129
130 if (ret.mrc_sob_type_code <> 'R') then
131
132 SELECT
133 decode(m.depreciate_lastyear_flag,'YES',1,0),
134 nvl(book_grp.life_in_months,0),
135 decode(book_grp.capitalize_flag, 'YES', 1, 0),
136 decode(book_grp.depreciate_flag, 'YES', 1, 0),
137 decode(book_grp.period_counter_fully_reserved, null, 0, 1),
138 nvl(book_grp.itc_amount_id, 0),
139 ah.units,
140 bc.current_fiscal_year,
141 bc.distribution_source_book,
142 book_grp.rate_adjustment_factor,
143 nvl(book_grp.adjusted_rate,0),
144 book_grp.adjusted_cost,
145 book_grp.cost,
146 book_grp.recoverable_cost,
147 book_grp.itc_amount,
148 nvl(book_grp.salvage_value,0),
149 trunc(book_grp.prorate_date),
150 to_number(to_char(book_grp.prorate_date, 'J')),
151 decode(instr(m.rate_source_rule, 'CALCULATED'), 0,
152 trunc(book_grp.deprn_start_date), trunc(book_grp.prorate_date)),
153 decode(instr(m.rate_source_rule, 'CALCULATED'), 0,
154 to_number(to_char(book_grp.deprn_start_date, 'J')),
155 to_number(to_char(book_grp.prorate_date, 'J'))),
156 trunc(book_grp.date_placed_in_service),
157 to_number(to_char(book_grp.date_placed_in_service, 'J')),
158 bc.prorate_calendar,
159 m.method_code,
160 decode(bc.DEPR_FIRST_YEAR_RET_FLAG, 'YES', 1, 0),
161 conv.prorate_date,
162 trunc(bc.initial_date),
163 bc.deprn_calendar,
164 nvl(book_grp.ceiling_name, null),
165 nvl(book_grp.bonus_rule, null),
166 decode(m.rate_source_rule, 'CALCULATED', 1, 'TABLE', 2,
167 'FLAT', 3),
168 decode(m.deprn_basis_rule, 'COST', 1, 'NBV', 2),
169 decode(bc.book_class, 'TAX', 1, 0),
170 decode(ah.asset_type, 'CIP', 1, 0),
171 decode(ctype.depr_when_acquired_flag,'YES',1,0),
172 nvl(book_grp.reval_amortization_basis,0),
173 book_grp.unrevalued_cost,
174 nvl(book_grp.adjusted_capacity,0),
175 nvl(book_grp.production_capacity,0),
176 bc.fiscal_year_name,
177 nvl (book_grp.adjusted_recoverable_cost, book_grp.recoverable_cost),
178 decode(book_grp.annual_deprn_rounding_flag, NULL, 0, 'ADD', 1,
179 'ADJ', 2, 'RET', 3, 'REV', 4, 'TFR', 5,'RES', 6, 'OVE', 7, -1),
180 nvl(book_grp.short_fiscal_year_flag, 'NO'),
181 book_grp.conversion_date,
182 book_grp.original_deprn_start_date,
183 nvl(book_grp.old_adjusted_cost, 1),
184 nvl(book_grp.formula_factor, 1),
185 book_grp.allowed_deprn_limit_amount,
186 book_grp.group_asset_id,
187 book_grp.recognize_gain_loss,
188 book_grp.recapture_reserve_flag,
189 book_grp.limit_proceeds_flag,
190 book_grp.terminal_gain_loss,
191 book_grp.tracking_method,
192 book_grp.exclude_fully_rsv_flag,
193 book_grp.excess_allocation_option,
194 book_grp.depreciation_option,
195 book_grp.member_rollup_flag,
196 book_grp.ltd_proceeds,
197 book_grp.allocate_to_fully_rsv_flag,
198 book_grp.allocate_to_fully_ret_flag,
199 book_grp.eofy_reserve,
200 book_grp.cip_cost,
201 book_grp.ltd_cost_of_removal,
202 book_grp.prior_eofy_reserve,
203 book_grp.eop_adj_cost,
204 book_grp.eop_formula_factor,
205 book_grp.exclude_proceeds_from_basis,
206 book_grp.retirement_deprn_option,
207 book_grp.terminal_gain_loss_amount,
208 book_grp.period_counter_fully_reserved,
209 --Bug# 6920756
210 --book_grp.EXTENDED_DEPRN_FLAG, -- bug 6913897
211 --decode(book_grp.PERIOD_COUNTER_FULLY_EXTENDED, null, 0, 1) -- bug 6913897
212 decode(book_grp.period_counter_fully_extended, null, 0, 1),
213 book_grp.period_counter_fully_extended,
214 decode(book_grp.extended_deprn_flag,'Y', 1, 0)
215 INTO
216 h_depreciate_lastyr,
217 h_lifemonths,
218 h_capitalize,
219 h_depreciate,
220 h_fully_reserved,
221 h_itc_used,
222 h_cur_units,
223 h_cpd_fiscal_year,
224 h_dis_book,
225 h_raf,
226 h_adj_rate,
227 h_adjusted_cost,
228 h_current_cost,
229 h_recoverable_cost,
230 h_itc_amount,
231 h_salvage_value,
232 h_prorate_date,
233 h_prorate_jdate,
234 h_deprn_start_date,
235 h_deprn_start_jdate,
236 h_date_in_srv,
237 h_jdis,
238 h_p_cal,
239 h_method_code,
240 h_depr_first_year_ret,
241 h_ret_prorate_date,
242 h_initial_date,
243 h_deprn_cal,
244 h_ceiling_name,
245 h_bonus_rule,
246 h_rate_source_rule,
247 h_deprn_basis_rule,
248 h_book_class,
249 h_wip_asset,
250 h_dwacq,
251 h_reval_amort_basis,
252 h_unrevalued_cost,
253 h_adj_capacity,
254 h_capacity,
255 h_fiscal_year_name,
256 h_adj_rec_cost,
257 h_annual_deprn_rounding_flag,
258 h_short_fiscal_year_flag,
259 h_conversion_date,
260 h_orig_deprn_start_date,
261 h_old_adj_cost,
262 h_formula_factor,
263 h_allowed_deprn_limit_amount,
264 h_group_asset_id,
265 h_recognize_gain_loss,
266 h_recapture_reserve_flag,
267 h_limit_proceeds_flag,
268 h_terminal_gain_loss,
269 h_tracking_method,
270 h_exclude_fully_rsv_flag,
271 h_excess_allocation_option,
272 h_depreciation_option,
273 h_member_rollup_flag,
274 h_ltd_proceeds,
275 h_allocate_to_fully_rsv_flag,
276 h_allocate_to_fully_ret_flag,
277 h_eofy_reserve,
278 h_cip_cost,
279 h_ltd_cost_of_removal,
280 h_prior_eofy_reserve,
281 h_eop_adj_cost,
282 h_eop_formula_factor,
283 h_exclude_proceeds_from_basis,
284 h_retirement_deprn_option,
285 h_terminal_gain_loss_amount,
286 h_pc_fully_reserved,
287 --Bug# 6920756
288 --h_extended_deprn_flag,
289 h_fully_extended,
290 h_pc_fully_extended,
291 h_extended_flag
292 FROM
293 fa_books book,
294 fa_books book_grp,
295 fa_methods m,
296 fa_conventions conv,
297 fa_convention_types ctype,
298 fa_book_controls bc,
299 fa_asset_history ah
300 WHERE
301 book.retirement_id = h_retirement_id
302 AND book.asset_id = h_asset_id
303 AND book.book_type_code = h_book
304 AND book_grp.transaction_header_id_out is null
305 AND book_grp.asset_id = book.group_asset_id
306 AND book_grp.book_type_code = h_book
307 AND book_grp.deprn_method_code = m.method_code
308 AND nvl(book_grp.life_in_months,1) = nvl(m.life_in_months,1)
309 AND
310 bc.book_type_code = h_book
311 AND
312 ah.asset_id = book_grp.asset_id
313 AND ah.transaction_header_id_out is null
314 AND trunc(h_date_retired) between
315 conv.start_date and conv.end_date
316 AND h_ret_p_conv = conv.prorate_convention_code
317 AND ctype.prorate_convention_code = h_ret_p_conv;
318
319 else
320
321 SELECT
322 decode(m.depreciate_lastyear_flag,'YES',1,0),
323 nvl(book_grp.life_in_months,0),
324 decode(book_grp.capitalize_flag, 'YES', 1, 0),
325 decode(book_grp.depreciate_flag, 'YES', 1, 0),
326 decode(book_grp.period_counter_fully_reserved, null, 0, 1),
327 nvl(book_grp.itc_amount_id, 0),
328 ah.units,
329 bc.current_fiscal_year,
330 bc_primary.distribution_source_book,
331 book_grp.rate_adjustment_factor,
332 nvl(book_grp.adjusted_rate,0),
333 book_grp.adjusted_cost,
334 book_grp.cost,
335 book_grp.recoverable_cost,
336 book_grp.itc_amount,
337 nvl(book_grp.salvage_value,0),
338 trunc(book_grp.prorate_date),
339 to_number(to_char(book_grp.prorate_date, 'J')),
340 decode(instr(m.rate_source_rule, 'CALCULATED'), 0,
341 trunc(book_grp.deprn_start_date), trunc(book_grp.prorate_date)),
342 decode(instr(m.rate_source_rule, 'CALCULATED'), 0,
343 to_number(to_char(book_grp.deprn_start_date, 'J')),
344 to_number(to_char(book_grp.prorate_date, 'J'))),
345 trunc(book_grp.date_placed_in_service),
346 to_number(to_char(book_grp.date_placed_in_service, 'J')),
347 bc_primary.prorate_calendar,
348 m.method_code,
349 decode(bc_primary.DEPR_FIRST_YEAR_RET_FLAG, 'YES', 1, 0),
350 conv.prorate_date,
351 trunc(bc_primary.initial_date),
352 bc_primary.deprn_calendar,
353 nvl(book_grp.ceiling_name, null),
354 nvl(book_grp.bonus_rule, null),
355 decode(m.rate_source_rule, 'CALCULATED', 1, 'TABLE', 2,
356 'FLAT', 3),
357 decode(m.deprn_basis_rule, 'COST', 1, 'NBV', 2),
358 decode(bc_primary.book_class, 'TAX', 1, 0),
359 decode(ah.asset_type, 'CIP', 1, 0),
360 decode(ctype.depr_when_acquired_flag,'YES',1,0),
361 nvl(book_grp.reval_amortization_basis,0),
362 book_grp.unrevalued_cost,
363 nvl(book_grp.adjusted_capacity,0),
364 nvl(book_grp.production_capacity,0),
365 bc_primary.fiscal_year_name,
366 nvl (book_grp.adjusted_recoverable_cost, book_grp.recoverable_cost),
367 decode(book_grp.annual_deprn_rounding_flag, NULL, 0, 'ADD', 1,
368 'ADJ', 2, 'RET', 3, 'REV', 4, 'TFR', 5,'RES', 6, 'OVE', 7, -1),
369 nvl(book_grp.short_fiscal_year_flag, 'NO'),
370 book_grp.conversion_date,
371 book_grp.original_deprn_start_date,
372 nvl(book_grp.old_adjusted_cost, 1),
373 nvl(book_grp.formula_factor, 1),
374 book_grp.allowed_deprn_limit_amount,
375 book_grp.group_asset_id,
376 book_grp.recognize_gain_loss,
377 book_grp.recapture_reserve_flag,
378 book_grp.limit_proceeds_flag,
379 book_grp.terminal_gain_loss,
380 book_grp.tracking_method,
381 book_grp.exclude_fully_rsv_flag,
382 book_grp.excess_allocation_option,
383 book_grp.depreciation_option,
384 book_grp.member_rollup_flag,
385 book_grp.ltd_proceeds,
386 book_grp.allocate_to_fully_rsv_flag,
387 book_grp.allocate_to_fully_ret_flag,
388 book_grp.eofy_reserve,
389 book_grp.cip_cost,
390 book_grp.ltd_cost_of_removal,
391 book_grp.prior_eofy_reserve,
392 book_grp.eop_adj_cost,
393 book_grp.eop_formula_factor,
394 book_grp.exclude_proceeds_from_basis,
395 book_grp.retirement_deprn_option,
396 book_grp.terminal_gain_loss_amount,
397 book_grp.period_counter_fully_reserved
398 INTO
399 h_depreciate_lastyr,
400 h_lifemonths,
401 h_capitalize,
402 h_depreciate,
403 h_fully_reserved,
404 h_itc_used,
405 h_cur_units,
406 h_cpd_fiscal_year,
407 h_dis_book,
408 h_raf,
409 h_adj_rate,
410 h_adjusted_cost,
411 h_current_cost,
412 h_recoverable_cost,
413 h_itc_amount,
414 h_salvage_value,
415 h_prorate_date,
416 h_prorate_jdate,
417 h_deprn_start_date,
418 h_deprn_start_jdate,
419 h_date_in_srv,
420 h_jdis,
421 h_p_cal,
422 h_method_code,
423 h_depr_first_year_ret,
424 h_ret_prorate_date,
425 h_initial_date,
426 h_deprn_cal,
427 h_ceiling_name,
428 h_bonus_rule,
429 h_rate_source_rule,
430 h_deprn_basis_rule,
431 h_book_class,
432 h_wip_asset,
433 h_dwacq,
434 h_reval_amort_basis,
435 h_unrevalued_cost,
436 h_adj_capacity,
437 h_capacity,
438 h_fiscal_year_name,
439 h_adj_rec_cost,
440 h_annual_deprn_rounding_flag,
441 h_short_fiscal_year_flag,
442 h_conversion_date,
443 h_orig_deprn_start_date,
444 h_old_adj_cost,
445 h_formula_factor,
446 h_allowed_deprn_limit_amount,
447 h_group_asset_id,
448 h_recognize_gain_loss,
449 h_recapture_reserve_flag,
450 h_limit_proceeds_flag,
451 h_terminal_gain_loss,
452 h_tracking_method,
453 h_exclude_fully_rsv_flag,
454 h_excess_allocation_option,
455 h_depreciation_option,
456 h_member_rollup_flag,
457 h_ltd_proceeds,
458 h_allocate_to_fully_rsv_flag,
459 h_allocate_to_fully_ret_flag,
460 h_eofy_reserve,
461 h_cip_cost,
462 h_ltd_cost_of_removal,
463 h_prior_eofy_reserve,
464 h_eop_adj_cost,
465 h_eop_formula_factor,
466 h_exclude_proceeds_from_basis,
467 h_retirement_deprn_option,
468 h_terminal_gain_loss_amount,
469 h_pc_fully_reserved
470 FROM
471 fa_mc_books book,
472 fa_mc_books book_grp,
473 fa_methods m,
474 fa_conventions conv,
475 fa_convention_types ctype,
476 fa_mc_book_controls bc,
477 fa_book_controls bc_primary, -- added this to get fiscal year name
478 fa_asset_history ah
479 WHERE
480 book.retirement_id = h_retirement_id
481 AND book.asset_id = h_asset_id
482 AND book.book_type_code = h_book
483 AND book_grp.transaction_header_id_out is null
484 AND book_grp.asset_id = book.group_asset_id
485 AND book_grp.book_type_code = h_book
486 AND book_grp.deprn_method_code = m.method_code
487 AND nvl(book_grp.life_in_months,1) = nvl(m.life_in_months,1)
488 AND
489 bc.book_type_code = h_book
490 AND
491 ah.asset_id = book_grp.asset_id
492 AND ah.transaction_header_id_out is null
493 AND trunc(h_date_retired) between
494 conv.start_date and conv.end_date
495 AND h_ret_p_conv = conv.prorate_convention_code
496 AND book.set_of_books_id = ret.set_of_books_id
497 AND book_grp.set_of_books_id = ret.set_of_books_id
498 AND bc.set_of_books_id = ret.set_of_books_id
499 AND bc_primary.book_type_code = bc.book_type_code
500 AND ctype.prorate_convention_code = h_ret_p_conv;
501
502 end if;
503
504 if p_log_level_rec.statement_level then fa_debug_pkg.add(l_calling_fn, 'in faggin 2', '', p_log_level_rec => p_log_level_rec); end if;
505
506 h_same_fy := 0;
507
508 select decode(fy1.fiscal_year, fy2.fiscal_year,1,0)
509 INTO h_same_fy
510 FROM FA_FISCAL_YEAR FY1, FA_FISCAL_YEAR FY2
511 WHERE trunc(h_date_retired) between fy1.start_date and fy1.end_date
512 AND trunc(h_deprn_start_date)
513 between fy2.start_date and fy2.end_date
514 AND fy1.fiscal_year_name = h_fiscal_year_name
515 AND fy2.fiscal_year_name = h_fiscal_year_name;
516
517 if (h_same_fy > 0) and (h_depr_first_year_ret is null or
518 h_depr_first_year_ret <= 0) then
519
520 h_ret_prorate_date := h_prorate_date;
521
522 end if;
523
524 if p_log_level_rec.statement_level then fa_debug_pkg.add(l_calling_fn, 'in faggin 3', '', p_log_level_rec => p_log_level_rec); end if;
525
526
527 SELECT FISCAL.FISCAL_YEAR
528 INTO h_ret_fiscalyr
529 FROM FA_FISCAL_YEAR FISCAL
530 WHERE trunc(h_ret_prorate_date)
531 between START_DATE and END_DATE
532 AND fiscal_year_name = h_fiscal_year_name;
533
534 if p_log_level_rec.statement_level then fa_debug_pkg.add(l_calling_fn, 'in faggin 4', '', p_log_level_rec => p_log_level_rec); end if;
535
536 if (h_ret_fiscalyr <> h_cpd_fiscal_year) then
537
538 if h_ret_fiscalyr < h_cpd_fiscal_year then
539 h_period_num := 1;
540 else h_period_num := 0;
541 end if;
542
543 if h_ret_fiscalyr > h_cpd_fiscal_year then
544
545 select trunc(start_date)
546 into h_ret_prorate_date
547 from fa_fiscal_year
548 where fiscal_year = h_ret_fiscalyr
549 and fiscal_year_name = h_fiscal_year_name;
550 else
551 SELECT start_date
552 INTO h_ret_prorate_date
553 FROM fa_fiscal_year
554 where fiscal_year = h_cpd_fiscal_year
555 and fiscal_year_name = h_fiscal_year_name;
556 end if;
557
558 end if; -- end of - if (h_ret_fiscalyr
559
560 if p_log_level_rec.statement_level then fa_debug_pkg.add(l_calling_fn, 'in faggin 5', '', p_log_level_rec => p_log_level_rec); end if;
561
562
563 if h_rate_source_rule <> 1 then -- rate_source_rule <> 'CALCULATED'
564
565 if p_log_level_rec.statement_level then fa_debug_pkg.add(l_calling_fn, 'in faggin 6', '', p_log_level_rec => p_log_level_rec); end if;
566 if (h_same_fy > 0) and
567 (h_depr_first_year_ret is null or h_depr_first_year_ret <= 0) and
568 (h_dwacq > 0) then
569
570 begin
571 SELECT h_deprn_start_date
572 INTO h_ret_prorate_date
573 FROM dual;
574 exception
575 when no_data_found then
576 null;
577 end;
578 else
579 begin
580 SELECT h_deprn_start_date
581 INTO h_ret_prorate_date
582 FROM dual
583 where trunc(h_ret_prorate_date) < trunc(h_deprn_start_date);
584 exception
585 when no_data_found then
586 null;
587 end;
588 end if;
589 else
590 if p_log_level_rec.statement_level then
591 fa_debug_pkg.add(l_calling_fn, 'in faggin 7', '', p_log_level_rec => p_log_level_rec);
592 fa_debug_pkg.add(l_calling_fn, to_char(trunc(h_ret_prorate_date)), '');
593 fa_debug_pkg.add(l_calling_fn, to_char(trunc(h_prorate_date)), '');
594 end if;
595
596 begin
597 SELECT h_prorate_date
598 INTO h_ret_prorate_date
599 FROM dual
600 WHERE trunc(h_ret_prorate_date) < trunc(h_prorate_date);
601 exception
602 when no_data_found then
603 null;
604 end;
605
606 end if;
607
608 if p_log_level_rec.statement_level then
609 fa_debug_pkg.add(l_calling_fn, 'Final h_ret_prorate_date', to_char(h_ret_prorate_date));
610 fa_debug_pkg.add(l_calling_fn, 'in faggin 8', '', p_log_level_rec => p_log_level_rec);
611 end if;
612 bk.prorate_date := h_prorate_date;
613 bk.deprn_start_date := h_deprn_start_date;
614 bk.ret_prorate_date := h_ret_prorate_date;
615 bk.date_in_srv := h_date_in_srv;
616 bk.p_cal := h_p_cal;
617 bk.d_cal := h_deprn_cal;
618 bk.ceiling_name := h_ceiling_name;
619 bk.bonus_rule := h_bonus_rule;
620 bk.dis_book := h_dis_book;
621 bk.lifemonths := h_lifemonths;
622 bk.depr_first_year_ret := h_depr_first_year_ret;
623
624 if h_capitalize > 0 then
625 bk.capitalize := TRUE;
626 else
627 bk.capitalize := FALSE;
628 end if;
629
630 if h_depreciate > 0 then
631 bk.depreciate := TRUE;
632 else
633 bk.depreciate := FALSE;
634 end if;
635 -- bug#6913897,Added the filter condition, when asset is fully reserved but not fully extended, need to calculate the catch-up amount.
636 -- Bug#6920756,Added code to judge if the asset is fully extended or fully reserved.
637 /* if ((h_fully_extended > 0) and nvl(h_extended_deprn_flag,'N') = 'Y' ) OR
638 (h_fully_reserved > 0 and nvl(h_extended_deprn_flag,'N') <> 'Y' ) then
639 bk.fully_reserved := TRUE;
640 else
641 bk.fully_reserved := FALSE;
642 end if; */
643
644 if h_fully_reserved > 0 then
645 bk.fully_reserved := TRUE;
646 else
647 bk.fully_reserved := FALSE;
648 end if;
649
650 if h_extended_flag > 0 then
651 bk.extended_flag := TRUE;
652 else
653 bk.extended_flag := FALSE;
654 end if;
655
656 if h_fully_extended > 0 then
657 bk.fully_extended := TRUE;
658 else
659 bk.fully_extended := FALSE;
660 end if;
661 bk.pc_fully_extended := h_pc_fully_extended;
662 -- End of fix Bug#6920756
663 if h_depreciate_lastyr > 0 then
664 bk.depreciate_lastyr := TRUE;
665 else
666 bk.depreciate_lastyr := FALSE;
667 end if;
668
669 if h_book_class > 0 then
670 bk.book_class := TRUE;
671 else
672 bk.book_class := FALSE;
673 end if;
674
675 bk.itc_used := h_itc_used;
676 bk.rate_source_rule := h_rate_source_rule;
677 bk.deprn_basis_rule := h_deprn_basis_rule;
678 bk.cur_units := h_cur_units;
679 bk.method_code := h_method_code;
680 bk.cpd_fiscal_year := h_cpd_fiscal_year;
681 bk.jdis := h_jdis;
682 bk.prorate_jdate := h_prorate_jdate;
683 bk.deprn_start_jdate := h_deprn_start_jdate;
684 bk.ret_fiscal_year := h_ret_fiscalyr;
685 bk.raf := h_raf;
686 bk.adjusted_cost := h_adjusted_cost;
687 bk.adj_rate := h_adj_rate;
688 bk.current_cost := h_current_cost;
689 bk.recoverable_cost := h_recoverable_cost;
690 bk.itc_amount := h_itc_amount;
691 bk.salvage_value := h_salvage_value;
692 bk.reval_amort_basis := h_reval_amort_basis;
693 bk.unrevalued_cost := h_unrevalued_cost;
694 bk.adj_capacity := h_adj_capacity;
695 bk.capacity := h_capacity;
696 bk.fiscal_year_name := h_fiscal_year_name;
697 bk.adj_rec_cost := h_adj_rec_cost;
698 -- +++++ Copy h_annual_deprn_rounding_flag to book_struct. +++++
699 bk.deprn_rounding_flag := h_annual_deprn_rounding_flag;
700 bk.short_fiscal_year_flag := h_short_fiscal_year_flag;
701 bk.conversion_date := h_conversion_date;
702 bk.orig_deprn_start_date := h_orig_deprn_start_date;
703 bk.old_adj_cost := h_old_adj_cost;
704 bk.formula_factor := h_formula_factor;
705 -- +++++ Added for Group Asset +++++
706 bk.group_asset_id := h_group_asset_id;
707 bk.recognize_gain_loss := h_recognize_gain_loss;
708 bk.recapture_reserve_flag := h_recapture_reserve_flag;
709 bk.limit_proceeds_flag := h_limit_proceeds_flag;
710 bk.terminal_gain_loss := h_terminal_gain_loss;
711 bk.tracking_method := h_tracking_method;
712 bk.exclude_fully_rsv_flag := h_exclude_fully_rsv_flag;
713 bk.excess_allocation_option := h_excess_allocation_option;
714 bk.depreciation_option := h_depreciation_option;
715 bk.member_rollup_flag := h_member_rollup_flag;
716 bk.ltd_proceeds := h_ltd_proceeds;
717 bk.allocate_to_fully_rsv_flag := h_allocate_to_fully_rsv_flag;
718 bk.allocate_to_fully_ret_flag := h_allocate_to_fully_ret_flag;
719 bk.eofy_reserve := h_eofy_reserve;
720 bk.cip_cost := h_cip_cost;
721 bk.ltd_cost_of_removal := h_ltd_cost_of_removal;
722 bk.prior_eofy_reserve := h_prior_eofy_reserve;
723 bk.eop_adj_cost := h_eop_adj_cost;
724 bk.eop_formula_factor := h_eop_formula_factor;
725 bk.exclude_proceeds_from_basis := h_exclude_proceeds_from_basis;
726 bk.retirement_deprn_option := h_retirement_deprn_option;
727 bk.terminal_gain_loss_amount := h_terminal_gain_loss_amount;
728 ret.wip_asset := h_wip_asset;
729 bk.pc_fully_reserved := h_pc_fully_reserved;
730
731 if p_log_level_rec.statement_level then fa_debug_pkg.add(l_calling_fn, 'in faggin 100', '', p_log_level_rec => p_log_level_rec); end if;
732
733 return(TRUE);
734
735 EXCEPTION
736
737 when faggin_err then
738
739 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
740 return FALSE;
741
742 when others then
743 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
744 return FALSE;
745
746 END FAGGIN;
747
748 /*===========================================================================
749 | NAME fagfpc |
750 | |
751 | FUNCTION Determines the number of periods need to be catchup. |
752 | A negative number corresponds to the case where the period |
753 | number of the DATE EFFECTIVE of the retirement is less than |
754 | the current period number. A positive number corresponds to |
755 | teh reverse case. If retirement prorate date is in current |
756 | period, then # of periods catchup is zero. |
757 | |
758 | HISTORY 1/12/89 R Rumanang Created |
759 | 8/22/90 R Rumanang add prorate_calendar |
760 | 04/12/91 M Chan Modified for MPL 9 |
761 | 01/08/97 S Behura Rewrote in PL/SQL |
762 |===========================================================================*/
763
764 FUNCTION fagfpc(book in varchar2, ret_p_date in date,
765 cpdnum number, cpd_fiscal_year number,
766 p_cal in out nocopy varchar2, d_cal in out nocopy varchar2,
767 pdspyr number, pds_catchup in out nocopy number,
768 startdp in out nocopy number, enddp in out nocopy number,
769 startpp in out nocopy number, endpp in out nocopy number,
770 fiscal_year_name in out nocopy varchar2,
771 cpdnum_set varchar2 /*Bug#8620551 */
772 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) RETURN BOOLEAN IS
773
774 fagfpc_err exception;
775
776 dummy number;
777 ret_pro_mth number;
778 ret_pro_fy number;
779 fiscal_year number;
780 ret_p_period_num number;
781 ret_p_jdate number;
782 p_pds_per_yr integer;
783
784
785 h_startpp integer;
786 h_endpp integer;
787 h_ret_p_jstartdate integer;
788 h_p_cal varchar2(21);
789 h_d_cal varchar2(21);
790 h_fiscal_year_name varchar2(31);
791 h_ret_p_date date;
792 h_cpp_jstartdate number;
793 h_cpdnum number;
794 h_book varchar2(30);
795 h_cpd_fy number;
796
797 l_calling_fn varchar2(80) := 'fa_gainloss_ret_pkg.fagfpc';
798
799 BEGIN <<FAGFPC>>
800
801 h_d_cal := d_cal;
802 h_p_cal := p_cal;
803 h_fiscal_year_name := fiscal_year_name;
804 h_ret_p_date := ret_p_date;
805 h_book := book;
806 h_cpdnum := cpdnum;
807 h_cpd_fy := cpd_fiscal_year;
808
809 ret_p_jdate := to_char(ret_p_date, 'J');
810
811 if not fa_cache_pkg.fazccp(d_cal, fiscal_year_name, ret_p_jdate,
812 ret_p_period_num, fiscal_year, dummy, p_log_level_rec => p_log_level_rec) then
813 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
814 raise fagfpc_err;
815 end if;
816
817 if not fa_GAINLOSS_MIS_PKG.faggfy(ret_p_date, p_cal,
818 ret_pro_mth, ret_pro_fy,
819 fiscal_year_name, p_log_level_rec => p_log_level_rec) then
820
821 fa_srvr_msg.add_message(
822 calling_fn => 'fa_gainloss_ret_pkg.fagfpc',
823 name => 'FA_RET_GENERIC_ERROR',
824 token1 => 'MODULE',
825 value1 => 'FAGGFY',
826 token2 => 'INFO',
827 value2 => 'Retirement Prorate Date',
828 token3 => 'ASSET',
829 value3 => NULL, p_log_level_rec => p_log_level_rec);
830
831 return(FALSE);
832
833 end if;
834
835 -- Get the number of periods per year in the rate calendar
836 if not fa_cache_pkg.fazcct(p_cal, p_log_level_rec => p_log_level_rec) then
837 fa_srvr_msg.add_message(calling_fn => 'fa_gainloss_ret_pkg.fagfpc', p_log_level_rec => p_log_level_rec);
838 raise fagfpc_err;
839 end if;
840
841 p_pds_per_yr := fa_cache_pkg.fazcct_record.number_per_fiscal_year;
842
843
844 begin
845 -- Bug8620551
846 -- Added the condition to check for daily prorate assets
847 if cpdnum_set = 'N' then
848 SELECT to_number (to_char (cp.start_date, 'J'))
849 INTO h_cpp_jstartdate
850 FROM fa_deprn_periods dp,
851 fa_calendar_periods cp,
852 fa_fiscal_year fy
853 WHERE cp.calendar_type = h_d_cal
854 AND fy.fiscal_year_name = h_fiscal_year_name
855 AND fy.fiscal_year = h_cpd_fy
856 AND dp.fiscal_year = fy.fiscal_year
857 AND dp.book_type_code = h_book
858 AND dp.period_num = h_cpdnum
859 AND dp.period_name = cp.period_name
860 AND dp.period_num = cp.period_num;
861 end if;
862 EXCEPTION
863 when no_data_found then
864 raise fagfpc_err;
865 end;
866
867
868 -- getting start prorate period num
869
870 begin
871 -- Bug8620551
872 -- Added the condition to check for daily prorate assets
873 if cpdnum_set = 'N' then
874 SELECT period_num
875 INTO h_startpp
876 FROM fa_calendar_periods
877 WHERE calendar_type = h_p_cal
878 AND to_date (h_cpp_jstartdate,'J')
879 between start_date and end_date;
880 end if;
881 EXCEPTION
882 when no_data_found then
883 raise fagfpc_err;
884 end;
885
886
887 -- getting end prorate period num
888
889 begin
890 SELECT period_num
891 INTO h_endpp
892 FROM fa_calendar_periods
893 WHERE calendar_type = h_p_cal
894 AND trunc(h_ret_p_date)
895 between start_date and end_date;
896 EXCEPTION
897 when no_data_found then
898 raise fagfpc_err;
899 end;
900
901 startpp := h_startpp;
902 endpp := h_endpp;
903
904 /* Retirement cannot accross fiscal year, thus it always happen in current
905 fiscal year. However, retirement_prorate_convention may cause the
906 prorate_date in the next year (ie: FOLLOWING-MONTH.)
907 */
908
909 pds_catchup := (((ret_pro_fy * p_pds_per_yr) + h_endpp) -
910 ((cpd_fiscal_year * p_pds_per_yr) + h_startpp));
911
912 -- Bug8620551
913 if cpdnum_set = 'N' then
914 startdp := cpdnum;
915 enddp := cpdnum;
916 end if;
917 return(TRUE);
918
919 EXCEPTION
920
921 when others then
922 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
923 return FALSE;
924
925 END FAGFPC;
926
927 /*==========================================================================
928 | NAME faggrv |
929 | |
930 | FUNCTION Gets current depreciation reserve after adjsuted with |
931 | total adjustments so far for this period. For TAX |
932 | retirement, we need to look for if there is any tax |
933 | adjustment or not. |
934 | |
935 | HISTORY 9/9/89 R Rumanang Created |
936 | 9/5/90 R Rumanang Updated for Tax Reserve |
937 | Adjustment |
938 | 04/11/91 M Chan Rewrite for MPL 9 to speed up the|
939 | retirement program. |
940 | 01/08/97 S Behura Rewrote into PL/SQL |
941 | 08/11/97 S Behura Rewrote into PL/SQL(10.7) |
942 |==========================================================================*/
943
944 FUNCTION faggrv(asset_id number, book in varchar2, cpd_ctr number,
945 adj_rsv in out nocopy number, reval_adj_rsv in out nocopy number,
946 prior_fy_exp in out nocopy number, ytd_deprn in out nocopy number,
947 bonus_rsv in out nocopy number,
948 bonus_ytd_deprn in out nocopy number,
949 prior_fy_bonus_exp in out nocopy number,
950 impairment_rsv in out nocopy number,
951 ytd_impairment in out nocopy number,
952 mrc_sob_type_code in varchar2,
953 set_of_books_id in number,
954 p_log_level_rec IN FA_API_TYPES.log_level_rec_type) RETURN BOOLEAN IS
955
956 faggrv_err exception;
957
958 dpr_row fa_STD_TYPES.fa_deprn_row_struct;
959 h_success boolean;
960
961 l_calling_fn varchar2(80) := 'fa_gainloss_ret_pkg.faggrv';
962
963 BEGIN <<FAGGRV>>
964
965 if p_log_level_rec.statement_level then fa_debug_pkg.add(l_calling_fn, 'IN FAGGRV', '', p_log_level_rec => p_log_level_rec); end if;
966
967 if p_log_level_rec.statement_level then
968 fa_debug_pkg.add
969 (fname => l_calling_fn,
970 element => 'Getting depreciation reserve adjustments',
971 value => '', p_log_level_rec => p_log_level_rec);
972 end if;
973
974 /* we need to take into account about the CREDIT, because Reinstatement
975 of a retirement will put up a CR of reserve. h_tot_adjustment holds
976 the value of adjustment to reserve + expense for this period */
977
978 dpr_row.asset_id := asset_id;
979 dpr_row.book := book;
980 dpr_row.dist_id := 0;
981 dpr_row.period_ctr := cpd_ctr;
982 dpr_row.mrc_sob_type_code := mrc_sob_type_code;
983 dpr_row.set_of_books_id := set_of_books_id;
984
985 FA_QUERY_BALANCES_PKG.query_balances_int (
986 X_DPR_ROW => dpr_row,
987 X_RUN_MODE => 'STANDARD',
988 X_DEBUG => FALSE,
989 X_SUCCESS => H_SUCCESS,
990 X_CALLING_FN => l_calling_fn,
991 X_TRANSACTION_HEADER_ID => -1, p_log_level_rec => p_log_level_rec);
992
993 if dpr_row.period_ctr <> 0 then
994 adj_rsv := dpr_row.deprn_rsv;
995 reval_adj_rsv := dpr_row.reval_rsv;
996 bonus_rsv := dpr_row.bonus_deprn_rsv;
997 bonus_ytd_deprn := dpr_row.bonus_ytd_deprn;
998 impairment_rsv := dpr_row.impairment_rsv;
999 ytd_impairment := dpr_row.ytd_impairment;
1000
1001 /*** Copy dpr_row.prior_fy_exp to prior_fy_exp. ***/
1002 prior_fy_exp := dpr_row.prior_fy_exp;
1003 prior_fy_bonus_exp := dpr_row.prior_fy_bonus_exp;
1004 ytd_deprn := dpr_row.ytd_deprn;
1005 else
1006 -- faggrv: no values found in query fin info function
1007 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
1008 raise faggrv_err;
1009 end if;
1010
1011 return(TRUE);
1012
1013 EXCEPTION
1014
1015 when faggrv_err then
1016 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
1017 return FALSE;
1018
1019 when others then
1020 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
1021 return FALSE;
1022
1023
1024 END FAGGRV;
1025
1026
1027 /*==============================================================================
1028 | |
1029 | NAME faggrp |
1030 | |
1031 | FUNCTION This has been created as Do_Retirement_in_CGL has been moved |
1032 | up and still some of code in Do_Retirement_in_CGL needed to |
1033 | be performed at the end of retirement process such as |
1034 | calculation of recapture reserve and updating thid in aj |
1035 | entries for group. |
1036 | |
1037 | Created for bug11886090 |
1038 | |
1039 =============================================================================+*/
1040 FUNCTION faggrp(p_ret IN FA_RET_TYPES.ret_struct
1041 , p_bk IN FA_RET_TYPES.book_struct
1042 , p_current_period_counter IN NUMBER
1043 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) Return BOOLEAN IS
1044
1045
1046 CURSOR c_get_thid is
1047 select transaction_header_id
1048 from fa_transaction_headers
1049 where asset_id = p_ret.asset_id
1050 and book_type_code = p_ret.book
1051 and member_transaction_header_id = p_ret.th_id_in;
1052
1053 l_calling_fn varchar2(40) := 'FA_GAINLOSS_RET_PKG.faggrp';
1054 l_temp_num number;
1055 l_temp_char varchar2(30);
1056 l_temp_bool boolean;
1057 l_g_rsv number;
1058
1059 l_adj FA_ADJUST_TYPE_PKG.FA_ADJ_ROW_STRUCT;
1060 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
1061 l_asset_cat_rec FA_API_TYPES.asset_cat_rec_type;
1062 l_trans_rec FA_API_TYPES.trans_rec_type;
1063 l_rowid ROWID; -- temp variable
1064
1065 l_grp_err exception;
1066
1067 begin
1068 if p_log_level_rec.statement_level then
1069 fa_debug_pkg.add(l_calling_fn, 'BEGIN', p_ret.asset_id, p_log_level_rec => p_log_level_rec);
1070 end if;
1071
1072 if (nvl(p_bk.recapture_reserve_flag, 'N') = 'Y') then
1073 -- get reserve and cost
1074
1075 fa_query_balances_pkg.query_balances(
1076 X_asset_id => p_ret.asset_id,
1077 X_book => p_ret.book,
1078 X_period_ctr => 0,
1079 X_dist_id => 0,
1080 X_run_mode => 'STANDARD',
1081 X_cost => l_temp_num,
1082 X_deprn_rsv => l_g_rsv,
1083 X_reval_rsv => l_temp_num,
1084 X_ytd_deprn => l_temp_num,
1085 X_ytd_reval_exp => l_temp_num,
1086 X_reval_deprn_exp => l_temp_num,
1087 X_deprn_exp => l_temp_num,
1088 X_reval_amo => l_temp_num,
1089 X_prod => l_temp_num,
1090 X_ytd_prod => l_temp_num,
1091 X_ltd_prod => l_temp_num,
1092 X_adj_cost => l_temp_num,
1093 X_reval_amo_basis => l_temp_num,
1094 X_bonus_rate => l_temp_num,
1095 X_deprn_source_code => l_temp_char,
1096 X_adjusted_flag => l_temp_bool,
1097 X_transaction_header_id => -1,
1098 X_bonus_deprn_rsv => l_temp_num,
1099 X_bonus_ytd_deprn => l_temp_num,
1100 X_bonus_deprn_amount => l_temp_num,
1101 X_impairment_rsv => l_temp_num,
1102 X_ytd_impairment => l_temp_num,
1103 X_impairment_amount => l_temp_num,
1104 X_capital_adjustment => l_temp_num,
1105 X_general_fund => l_temp_num,
1106 X_mrc_sob_type_code => p_ret.mrc_sob_type_code,
1107 X_set_of_books_id => p_ret.set_of_books_id,
1108 p_log_level_rec => p_log_level_rec);
1109
1110 if p_log_level_rec.statement_level then
1111 fa_debug_pkg.add(l_calling_fn, 'after', 'query balance', p_log_level_rec => p_log_level_rec);
1112 fa_debug_pkg.add(l_calling_fn, 'p_bk.current_cost', p_bk.current_cost, p_log_level_rec => p_log_level_rec);
1113 fa_debug_pkg.add(l_calling_fn, 'p_ret.cost_retired', p_ret.cost_retired, p_log_level_rec => p_log_level_rec);
1114 fa_debug_pkg.add(l_calling_fn, 'l_g_rsv', l_g_rsv, p_log_level_rec => p_log_level_rec);
1115 end if;
1116
1117 -- compare the two amounts and find out recapture is necessary
1118 if (((nvl(p_bk.current_cost - p_ret.cost_retired, 0) - l_g_rsv) < 0) and sign(p_bk.current_cost- p_ret.cost_retired) >= 0) or
1119 (((nvl(p_bk.current_cost - p_ret.cost_retired, 0) - l_g_rsv) > 0) and sign(p_bk.current_cost- p_ret.cost_retired) <= 0) then
1120 -- need to recapture excess reserve
1121 if p_log_level_rec.statement_level then
1122 fa_debug_pkg.add(l_calling_fn, 'creating', 'recapture entry', p_log_level_rec => p_log_level_rec);
1123 end if;
1124
1125 l_adj.source_type_code := 'RETIREMENT';
1126
1127 l_adj.asset_id := p_ret.asset_id;
1128 l_adj.transaction_header_id := p_ret.th_id_in;
1129 l_adj.book_type_code := p_ret.book;
1130 l_adj.period_counter_created := p_current_period_counter;
1131 l_adj.period_counter_adjusted := p_current_period_counter;
1132 l_adj.current_units := p_bk.cur_units;
1133 l_adj.selection_retid := 0;
1134 l_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
1135 l_adj.leveling_flag := TRUE;
1136 l_adj.flush_adj_flag := TRUE;
1137 l_adj.last_update_date := l_trans_rec.who_info.last_update_date;
1138 l_adj.gen_ccid_flag := TRUE;
1139 l_adj.adjustment_type := 'RESERVE';
1140 l_adj.account_type := 'DEPRN_RESERVE_ACCT';
1141 l_adj.debit_credit_flag := 'DR';
1142 l_adj.mrc_sob_type_code := p_ret.mrc_sob_type_code;
1143 l_adj.set_of_books_id := p_ret.set_of_books_id;
1144 l_adj.adjustment_amount := l_g_rsv - nvl(p_bk.current_cost - p_ret.cost_retired, 0);
1145
1146 l_asset_hdr_rec.asset_id := p_ret.asset_id;
1147 l_asset_hdr_rec.book_type_code := p_ret.book;
1148 l_asset_hdr_rec.set_of_books_id := p_ret.set_of_books_id;
1149
1150 if not FA_UTIL_PVT.get_asset_cat_rec(p_asset_hdr_rec => l_asset_hdr_rec,
1151 px_asset_cat_rec => l_asset_cat_rec,
1152 p_date_effective => null,
1153 p_log_level_rec => p_log_level_rec) then
1154 if p_log_level_rec.statement_level then
1155 fa_debug_pkg.add(l_calling_fn, 'error calling', 'FA_UTIL_PVT.get_asset_cat_rec', p_log_level_rec => p_log_level_rec);
1156 end if;
1157 raise l_grp_err;
1158 end if;
1159
1160 if not fa_cache_pkg.fazccb(X_book => p_ret.book,
1161 X_cat_id => l_asset_cat_rec.category_id,
1162 p_log_level_rec => p_log_level_rec) then
1163 if p_log_level_rec.statement_level then
1164 fa_debug_pkg.add(l_calling_fn, 'error calling', 'fazccb', p_log_level_rec => p_log_level_rec);
1165 end if;
1166 raise l_grp_err;
1167 end if;
1168
1169 l_adj.account := fa_cache_pkg.fazccb_record.DEPRN_RESERVE_ACCT;
1170
1171 if p_log_level_rec.statement_level then
1172 fa_debug_pkg.add(l_calling_fn, 'calling faxinaj', 'for reserve', p_log_level_rec => p_log_level_rec);
1173 end if;
1174
1175 if not FA_INS_ADJUST_PKG.faxinaj
1176 (l_adj,
1177 l_trans_rec.who_info.last_update_date,
1178 l_trans_rec.who_info.last_updated_by,
1179 l_trans_rec.who_info.last_update_login, p_log_level_rec => p_log_level_rec) then
1180 if p_log_level_rec.statement_level then
1181 fa_debug_pkg.add(l_calling_fn, 'error calling', 'faxinaj', p_log_level_rec => p_log_level_rec);
1182 end if;
1183 raise l_grp_err;
1184 end if;
1185
1186 l_adj.adjustment_type := 'NBV RETIRED';
1187 l_adj.adjustment_amount := l_g_rsv - nvl(p_bk.current_cost - p_ret.cost_retired, 0);
1188 l_adj.flush_adj_flag := TRUE;
1189
1190 l_adj.debit_credit_flag := 'CR';
1191 l_adj.account_type := 'NBV_RETIRED_GAIN_ACCT';
1192 l_adj.account := fa_cache_pkg.fazcbc_record.nbv_retired_gain_acct;
1193
1194 if p_log_level_rec.statement_level then
1195 fa_debug_pkg.add(l_calling_fn, 'calling faxinaj', 'for nbv retired', p_log_level_rec => p_log_level_rec);
1196 end if;
1197
1198 if not FA_INS_ADJUST_PKG.faxinaj
1199 (l_adj,
1200 l_trans_rec.who_info.last_update_date,
1201 l_trans_rec.who_info.last_updated_by,
1202 l_trans_rec.who_info.last_update_login, p_log_level_rec => p_log_level_rec) then
1203 if p_log_level_rec.statement_level then
1204 fa_debug_pkg.add(l_calling_fn, 'error calling', 'faxinaj', p_log_level_rec => p_log_level_rec);
1205 end if;
1206 raise l_grp_err;
1207 end if;
1208
1209 -- Bug 8674833 : Populate sob_id from p_ret
1210 FA_RETIREMENTS_PKG.Update_Row(
1211 X_Rowid => l_rowid,
1212 X_Retirement_Id => p_ret.retirement_id,
1213 X_Recapture_Amount => l_g_rsv - nvl(p_bk.current_cost - p_ret.cost_retired, 0),
1214 X_mrc_sob_type_code => p_ret.mrc_sob_type_code,
1215 X_set_of_books_id => p_ret.set_of_books_id,
1216 X_Calling_Fn => l_calling_fn,
1217 p_log_level_rec => p_log_level_rec);
1218
1219 end if; -- if (((nvl(p_bk.current_cost - p_ret.cost_r ...
1220
1221 end if; -- if recapture = Y
1222
1223 --
1224 -- Prepare to call FA_ADJUSTMENT_PVT.do_adjustment to process group
1225 -- asset after member asset retirement.
1226 --
1227 if p_log_level_rec.statement_level then
1228 fa_debug_pkg.add(l_calling_fn, 'Updating FA_ADJUSTMENTS for thid of', p_ret.th_id_in, p_log_level_rec => p_log_level_rec);
1229 fa_debug_pkg.add(l_calling_fn, 'asset', p_ret.asset_id, p_log_level_rec => p_log_level_rec);
1230 fa_debug_pkg.add(l_calling_fn, 'book', p_ret.book, p_log_level_rec => p_log_level_rec);
1231 end if;
1232
1233 if (p_ret.mrc_sob_type_code <> 'R') then
1234 OPEN c_get_thid;
1235 FETCH c_get_thid INTO g_grp_trx_hdr_id;
1236 CLOSE c_get_thid;
1237
1238 if p_log_level_rec.statement_level then
1239 fa_debug_pkg.add(l_calling_fn, 'Updating FA_ADJUSTMENTS with thid of', g_grp_trx_hdr_id, p_log_level_rec => p_log_level_rec);
1240 end if;
1241
1242 if (g_grp_trx_hdr_id is not null) then
1243 UPDATE FA_ADJUSTMENTS
1244 SET TRANSACTION_HEADER_ID = g_grp_trx_hdr_id
1245 WHERE TRANSACTION_HEADER_ID = p_ret.th_id_in
1246 AND ASSET_ID = p_ret.asset_id
1247 AND BOOK_TYPE_CODE = p_ret.book;
1248 end if;
1249 else
1250 if p_log_level_rec.statement_level then
1251 fa_debug_pkg.add(l_calling_fn, 'Updating FA_MC_ADJUSTMENTS with thid of', g_grp_trx_hdr_id, p_log_level_rec => p_log_level_rec);
1252 end if;
1253 UPDATE FA_MC_ADJUSTMENTS
1254 SET TRANSACTION_HEADER_ID = g_grp_trx_hdr_id
1255 WHERE TRANSACTION_HEADER_ID = p_ret.th_id_in
1256 AND ASSET_ID = p_ret.asset_id
1257 AND BOOK_TYPE_CODE = p_ret.book
1258 AND SET_OF_BOOKS_ID = p_ret.set_of_books_id;
1259 end if;
1260
1261 if p_log_level_rec.statement_level then
1262 fa_debug_pkg.add(l_calling_fn, 'END', 'SUCCESS', p_log_level_rec => p_log_level_rec);
1263 end if;
1264
1265 return TRUE;
1266
1267 EXCEPTION
1268 when l_grp_err then
1269 if p_log_level_rec.statement_level then
1270 fa_debug_pkg.add(l_calling_fn, 'EXCEPTION',
1271 'l_grp_err', p_log_level_rec => p_log_level_rec);
1272 end if;
1273 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
1274 return FALSE;
1275
1276 when others then
1277 if p_log_level_rec.statement_level then
1278 fa_debug_pkg.add(l_calling_fn, 'EXCEPTION',
1279 'OTHERS', p_log_level_rec => p_log_level_rec);
1280 end if;
1281
1282 if c_get_thid%ISOPEN then
1283 close c_get_thid;
1284 end if;
1285 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
1286 return FALSE;
1287 end faggrp;
1288
1289
1290
1291 /*=============================================================================
1292 | |
1293 | NAME fagret |
1294 | |
1295 | FUNCTION This function does the retirement process based on the |
1296 | retirement structure and book information. |
1297 | Briefly, it determines the number of periods need to be |
1298 | catchup based on the DATE_EFFECTIVE of the retirement and the |
1299 | current period START_DATE. The number of periods catchup is |
1300 | determined by substracting the current period number from |
1301 | the period number of DATE_EFFECTIVE. If the number is |
1302 | positive, then we need to calculate the depreciation amount |
1303 | needs to be taken each period; otherwise, we don't need to |
1304 | determine the depreciation rate since we can obtain the |
1305 | deprn_amount need to be taken from the previous records in |
1306 | deprn_summary and deprn_detail. |
1307 | |
1308 | HISTORY 1/12/89 R Rumanang Created |
1309 | 6/23/89 R Rumanang Standarized |
1310 | 8/30/89 R Rumanang Add calls to fagprv, etc as part |
1311 | of the GL interface project. |
1312 | 9/08/89 R Rumanang When capitalize_flag is NO, just set |
1313 | the retirment to PROCESSED. |
1314 | 04/08/91 M Chan Rewrite the module so that it will |
1315 | handle the retirement process in a |
1316 | better way. |
1317 | 01/08/97 S Behura Rewrote into PL/SQL |
1318 | 08/11/97 S Behura Rewrote into PL/SQL(10.7) |
1319 |============================================================================*/
1320
1321 FUNCTION fagret(ret in out nocopy fa_RET_TYPES.ret_struct,
1322 bk in out nocopy fa_RET_TYPES.book_struct,
1323 dpr in out nocopy fa_STD_TYPES.dpr_struct, today in date,
1324 cpd_ctr number, cpdnum number, retpdnum in out nocopy number,
1325 user_id number, p_log_level_rec IN FA_API_TYPES.log_level_rec_type) Return BOOLEAN IS
1326 fagret_err exception;
1327
1328 -- Cursors and variables added for bug4343087
1329 CURSOR c_get_new_bk(c_asset_id number) is
1330 select adjusted_cost
1331 , salvage_value
1332 , recoverable_cost
1333 , adjusted_recoverable_cost
1334 , reval_amortization_basis
1335 , old_adjusted_cost
1336 from fa_books
1337 where asset_id = c_asset_id
1338 and book_type_code = ret.book
1339 and transaction_header_id_out is null;
1340
1341 CURSOR c_get_new_mc_bk(c_asset_id number) is
1342 select adjusted_cost
1343 , salvage_value
1344 , recoverable_cost
1345 , adjusted_recoverable_cost
1346 , reval_amortization_basis
1347 , old_adjusted_cost
1348 from fa_mc_books
1349 where asset_id = c_asset_id
1350 and book_type_code = ret.book
1351 and set_of_books_id = ret.set_of_books_id
1352 and transaction_header_id_out is null;
1353
1354 l_temp_deprn_amt number;
1355 l_temp_bonus_deprn_amt number;
1356 l_temp_impairment_amt number;
1357 l_temp_reval_deprn_amt number;
1358 l_temp_reval_amort number;
1359 -- End of Cursors and variables added for bug4343087
1360
1361 periods_catchup number;
1362 start_pd number;
1363 end_pd number;
1364 start_ppd number;
1365 end_ppd number;
1366 deprn_amt number;
1367 bonus_deprn_amt number;
1368 bonus_deprn_reserve number;
1369 bonus_ytd_deprn number;
1370 impairment_amt number;
1371 impairment_reserve number;
1372 ytd_impairment number;
1373 deprn_reserve number;
1374 reval_deprn_amt number;
1375 reval_amort number;
1376 reval_reserve number;
1377 prior_fy_exp number;
1378 prior_fy_bonus_exp number;
1379 ytd_deprn number;
1380 jdate_retired number;
1381 ret_prorate_jdate number;
1382 cost_frac number;
1383
1384 d_cost_retired number;
1385 d_current_cost number;
1386 d_cost_frac number;
1387
1388 h_date_retired date;
1389 h_ret_prorate_date date;
1390 h_jdate_retired number;
1391 h_ret_prorate_jdate number;
1392
1393 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
1394 l_asset_deprn_rec_old FA_API_TYPES.asset_deprn_rec_type;
1395
1396 -- Bug 5525968: start
1397 bk_group fa_ret_types.book_struct;
1398 dpr_group fa_STD_TYPES.dpr_struct;
1399 ret_group fa_ret_types.ret_struct;
1400 pds_per_year number;
1401 p_pds_per_year number;
1402 cpd_num number;
1403 cpd_name varchar2(15);
1404 ret_pdnum number;
1405 pro_mth number;
1406 dsd_mth number;
1407 pro_fy number;
1408 dsd_fy number;
1409 deprn_amt_group number;
1410 bonus_deprn_amt_group number;
1411 impairment_amt_group number;
1412 impairment_reserve_group number;
1413 reval_deprn_amt_group number;
1414 reval_amort_group number;
1415 reval_reserve_group number;
1416 -- Bug 5525968: end
1417
1418 -- Bug 4639408
1419 l_temp_deprn_reserve number;
1420 l_decision_flag BOOLEAN; -- Bug#6920756
1421 l_calling_fn varchar2(80) := 'fa_gainloss_ret_pkg.fagret';
1422 --Bug8620551
1423 --Added new cursor and variable to check for retirement in period of addition
1424 h_cpdnum number; --Bug6187408
1425 prd_flag varchar2(1);
1426 cpdnum_set varchar2(1);
1427 cursor c_prd_flag is
1428 select 'Y'
1429 from fa_calendar_periods fcp1,
1430 fa_calendar_periods fcp2,
1431 fa_book_controls fbc
1432 where to_date (dpr.prorate_jdate,'J') BETWEEN fcp1.start_date and fcp1.end_date
1433 and fbc.book_type_code = dpr.book
1434 and fcp1.calendar_type = fbc.deprn_calendar
1435 and to_date (decode( dpr.jdate_retired,0,null,dpr.jdate_retired),'J') BETWEEN fcp2.start_date and fcp2.end_date
1436 and fcp2.calendar_type=fcp1.calendar_type
1437 and fcp1.period_name=fcp2.period_name;
1438
1439 --bug13014394: checking to see if override is used or not
1440 cursor c_override_exist (c_asset_id number, c_status varchar2, c_rowid rowid)is
1441 select do.rowid
1442 from fa_deprn_override do
1443 , fa_deprn_periods dp
1444 where do.asset_id = c_asset_id
1445 and do.book_type_code = ret.book
1446 and dp.period_counter = cpd_ctr
1447 and do.period_name = dp.period_name
1448 and dp.book_type_code = do.book_type_code
1449 and do.used_by = 'RETIREMENT'
1450 and do.rowid = decode(c_status, 'POSTED', c_rowid, do.rowid)
1451 and do.status = c_status;
1452
1453 --bug13014394: variables used related to above cursor
1454 l_rowid rowid;
1455 l_override_exist boolean := FALSE;
1456 l_override_used boolean := FALSE;
1457
1458 BEGIN <<FAGRET>>
1459
1460
1461 deprn_amt := 0;
1462 deprn_reserve := 0;
1463 bonus_deprn_amt := 0;
1464 bonus_deprn_reserve := 0;
1465 impairment_amt := 0;
1466 impairment_reserve := 0;
1467 reval_deprn_amt := 0;
1468 reval_amort := 0;
1469 reval_reserve := 0;
1470 prior_fy_exp := 0;
1471 prior_fy_bonus_exp := 0;
1472 ytd_deprn := 0;
1473 cost_frac := 0;
1474 bonus_ytd_deprn := 0;
1475 ytd_impairment := 0;
1476
1477 if (bk.current_cost is null or bk.current_cost = 0) then
1478 cost_frac := 0;
1479 else
1480 cost_frac := ret.cost_retired / bk.current_cost;
1481 end if;
1482
1483 --Commented out the following to avoid rounding of
1484 --Cost_Frac. Bug no 1050284
1485
1486 -- CHECK: SNARAYAN. may need to round cost frac to 8 digits
1487
1488 /* if not EFA_utilities.faxrnd(cost_frac, ret.book, cost_frac) then
1489 'Call faxrnd to round cost_frac in fagret');
1490 end if;*/
1491
1492
1493 if (bk.group_asset_id is not null) then
1494 l_asset_hdr_rec.asset_id := bk.group_asset_id;
1495 l_asset_hdr_rec.book_type_code := ret.book;
1496 -- l_asset_hdr_rec.set_of_books_id := fa_cache_pkg.fazcbc_record.set_of_books_id;
1497 l_asset_hdr_rec.set_of_books_id := ret.set_of_books_id;
1498
1499 if not fa_util_pvt.get_asset_deprn_rec (
1500 p_asset_hdr_rec => l_asset_hdr_rec,
1501 px_asset_deprn_rec => l_asset_deprn_rec_old,
1502 p_mrc_sob_type_code => ret.mrc_sob_type_code, p_log_level_rec => p_log_level_rec) then
1503 fa_srvr_msg.add_message(
1504 calling_fn => l_calling_fn,
1505 name => 'FA_RET_GENERIC_ERROR',
1506 token1 => 'MODULE',
1507 value1 => 'get_asset_deprn_rec',
1508 token2 => 'INFO',
1509 value2 => 'old deprn',
1510 token3 => 'ASSET',
1511 value3 => ret.asset_number , p_log_level_rec => p_log_level_rec);
1512
1513 return false;
1514 end if;
1515 end if;
1516
1517 if p_log_level_rec.statement_level then fa_debug_pkg.add(l_calling_fn, 'in fagret1 ', '', p_log_level_rec => p_log_level_rec); end if;
1518
1519 if not faggrv(ret.asset_id, ret.book, cpd_ctr, deprn_reserve,
1520 reval_reserve, prior_fy_exp, ytd_deprn,
1521 bonus_deprn_reserve, bonus_ytd_deprn,
1522 prior_fy_bonus_exp,
1523 impairment_reserve, ytd_impairment,
1524 ret.mrc_sob_type_code,
1525 ret.set_of_books_id,
1526 p_log_level_rec) then
1527
1528 fa_srvr_msg.add_message(
1529 calling_fn => l_calling_fn,
1530 name => 'FA_RET_GENERIC_ERROR',
1531 token1 => 'MODULE',
1532 value1 => 'FAGGRV',
1533 token2 => 'INFO',
1534 value2 => 'reserve',
1535 token3 => 'ASSET',
1536 value3 => ret.asset_number , p_log_level_rec => p_log_level_rec);
1537
1538 return(FALSE);
1539
1540 end if;
1541
1542 if p_log_level_rec.statement_level then fa_debug_pkg.add(l_calling_fn, 'in fagret2', '', p_log_level_rec => p_log_level_rec); end if;
1543
1544 dpr.deprn_rsv := deprn_reserve;
1545 dpr.reval_rsv := reval_reserve;
1546 dpr.prior_fy_exp := prior_fy_exp;
1547 dpr.ytd_deprn := ytd_deprn;
1548 dpr.bonus_deprn_rsv := bonus_deprn_reserve;
1549 dpr.bonus_ytd_deprn := bonus_ytd_deprn;
1550 dpr.prior_fy_bonus_exp := prior_fy_bonus_exp;
1551 dpr.impairment_rsv := impairment_reserve;
1552 dpr.ytd_impairment := ytd_impairment;
1553
1554 dpr.rsv_known_flag := TRUE;
1555
1556 --Bug8620551
1557 -- Get the number of periods per year in the rate calendar
1558 if not fa_cache_pkg.fazcct(bk.p_cal) then
1559 fa_srvr_msg.add_message(calling_fn => 'fa_gainloss_ret_pkg.fagret'
1560 ,p_log_level_rec => p_log_level_rec);
1561 raise fagret_err;
1562 end if;
1563 h_cpdnum := cpdnum;
1564 open c_prd_flag;
1565 fetch c_prd_flag into prd_flag;
1566 if c_prd_flag%NOTFOUND then
1567 prd_flag := 'N';
1568 end if;
1569 close c_prd_flag;
1570
1571 if fa_cache_pkg.fazcct_record.number_per_fiscal_year = 365 and prd_flag = 'Y' then
1572
1573 start_pd := cpdnum;
1574 end_pd := cpdnum;
1575 cpdnum_set := 'Y';
1576
1577 SELECT facp.period_num
1578 INTO h_cpdnum
1579 FROM fa_calendar_periods facp
1580 WHERE facp.calendar_type = bk.p_cal
1581 AND ( facp.start_date = bk.prorate_date
1582 OR facp.end_date = bk.prorate_date );
1583 else
1584 cpdnum_set := 'N';
1585 end if;
1586 --Bug8620551
1587 --Passing h_cpdnum instead of cpdnum and added cpdnum_set flag
1588
1589 if not fagfpc(ret.book, bk.ret_prorate_date, h_cpdnum,
1590 bk.cpd_fiscal_year, bk.p_cal, bk.d_cal,
1591 bk.pers_per_yr, periods_catchup,
1592 start_pd, end_pd, start_ppd, end_ppd,
1593 bk.fiscal_year_name,
1594 cpdnum_set,
1595 p_log_level_rec) then
1596
1597 fa_srvr_msg.add_message(
1598 calling_fn => l_calling_fn,
1599 name => 'FA_RET_GENERIC_ERROR',
1600 token1 => 'MODULE',
1601 value1 => 'FAGFPC',
1602 token2 => 'INFO',
1603 value2 => 'catchup period',
1604 token3 => 'ASSET',
1605 value3 => ret.asset_number , p_log_level_rec => p_log_level_rec);
1606
1607 return(FALSE);
1608
1609 end if;
1610
1611 if p_log_level_rec.statement_level then fa_debug_pkg.add(l_calling_fn, 'in fagret3', '', p_log_level_rec => p_log_level_rec); end if;
1612
1613 /* If it's capitalize and also depreciate, we will calculate
1614 depreciation; otherwise, we will skip it
1615 */
1616
1617 jdate_retired := to_char(ret.date_retired, 'J');
1618 h_jdate_retired := to_char(ret.date_retired, 'J');
1619 ret_prorate_jdate := to_char(bk.ret_prorate_date, 'J');
1620 h_ret_prorate_jdate := to_char(bk.ret_prorate_date, 'J');
1621
1622 dpr.jdate_retired := jdate_retired;
1623 dpr.ret_prorate_jdate := ret_prorate_jdate;
1624
1625 if start_pd = 0 then /* If start is zero, calculate the whole deprn*/
1626 dpr.p_cl_begin := 1;
1627 /* It is O.K. to assign 1 to dpr->p_cl_begin, because the deprn
1628 engine is smart enough to skip over periods before the deprn
1629 start period */
1630
1631 else
1632 dpr.p_cl_begin := start_pd;
1633 end if;
1634
1635 dpr.p_cl_end := end_pd;
1636
1637 deprn_amt := 0;
1638
1639 -- Bug 5525968: start: calulate bk and dpr info for group
1640 if(bk.group_asset_id is not null) then
1641 ret_group := ret;
1642 ret_group.asset_id := bk.group_asset_id;
1643
1644 select asset_number
1645 into ret_group.asset_number
1646 from fa_additions_b
1647 where asset_id = ret_group.asset_id;
1648
1649 if not faggin(ret, bk_group, p_log_level_rec) then
1650
1651 fa_srvr_msg.add_message(
1652 calling_fn => 'fa_gainloss_pro_pkg.fagpsa',
1653 name => 'FA_RET_GENERIC_ERROR',
1654 token1 => 'MODULE',
1655 value1 => 'FAGGBI',
1656 token2 => 'INFO',
1657 value2 => 'FA_BOOKS',
1658 token3 => 'ASSET',
1659 value3 => ret_group.asset_number , p_log_level_rec => p_log_level_rec);
1660
1661 return(FALSE);
1662 end if;
1663
1664
1665 bk_group.pers_per_yr := bk.pers_per_yr;
1666
1667 if not FA_GAINLOSS_MIS_PKG.faggfy(bk_group.prorate_date, bk_group.p_cal,
1668 pro_mth, pro_fy,
1669 bk_group.fiscal_year_name, p_log_level_rec => p_log_level_rec) then
1670
1671 fa_srvr_msg.add_message(
1672 calling_fn => 'fa_gainloss_pro_pkg.fagpsa',
1673 name => 'FA_RET_GENERIC_ERROR',
1674 token1 => 'MODULE',
1675 value1 => 'FAGGFY',
1676 token2 => 'INFO',
1677 value2 => 'Retirement Prorate Date',
1678 token3 => 'ASSET',
1679 value3 => ret_group.asset_number , p_log_level_rec => p_log_level_rec);
1680
1681 return(FALSE);
1682
1683 end if;
1684
1685 dpr_group.prorate_jdate := bk_group.prorate_jdate;
1686 dpr_group.deprn_start_jdate := bk_group.deprn_start_jdate;
1687 bk_group.prorate_mth := pro_mth;
1688 bk_group.prorate_fy := pro_fy;
1689
1690 if not FA_GAINLOSS_MIS_PKG.faggfy(bk_group.deprn_start_date, bk_group.p_cal,
1691 dsd_mth, dsd_fy,
1692 bk_group.fiscal_year_name, p_log_level_rec => p_log_level_rec) then
1693
1694 fa_srvr_msg.add_message(
1695 calling_fn => 'fa_gainloss_pro_pkg.fagpsa',
1696 name => 'FA_RET_GENERIC_ERROR',
1697 token1 => 'MODULE',
1698 value1 => 'FAGGFY',
1699 token2 => 'INFO',
1700 value2 => 'Deprn Prorate Date',
1701 token3 => 'ASSET',
1702 value3 => ret_group.asset_number , p_log_level_rec => p_log_level_rec);
1703
1704 return(FALSE);
1705
1706 end if;
1707
1708 bk_group.dsd_mth := dsd_mth;
1709 bk_group.dsd_fy := dsd_fy;
1710
1711 dpr_group.asset_id := ret_group.asset_id;
1712 dpr_group.book := ret_group.book;
1713 dpr_group.asset_type := 'GROUP';
1714 dpr_group.adj_cost := bk_group.adjusted_cost;
1715 dpr_group.rec_cost := bk_group.recoverable_cost;
1716 dpr_group.adj_rate := bk_group.adj_rate;
1717 dpr_group.rate_adj_factor := bk_group.raf;
1718 dpr_group.reval_amo_basis := bk_group.reval_amort_basis;
1719 dpr_group.adj_capacity := bk_group.adj_capacity;
1720 dpr_group.capacity := bk_group.adj_capacity;
1721 dpr_group.ltd_prod := 0;
1722
1723 dpr_group.adj_rec_cost := bk_group.adj_rec_cost;
1724 dpr_group.salvage_value := bk_group.salvage_value;
1725 dpr_group.old_adj_cost := bk_group.old_adj_cost;
1726 dpr_group.formula_factor := bk_group.formula_factor;
1727 dpr_group.set_of_books_id := dpr.set_of_books_id; --Bug 8497696
1728
1729 if (bk_group.deprn_rounding_flag is null
1730 or bk_group.deprn_rounding_flag=0) then
1731 dpr_group.deprn_rounding_flag := NULL;
1732 elsif bk_group.deprn_rounding_flag=1 then
1733 dpr_group.deprn_rounding_flag := 'ADD';
1734 elsif bk_group.deprn_rounding_flag=2 then
1735 dpr_group.deprn_rounding_flag := 'ADJ';
1736 elsif bk_group.deprn_rounding_flag=3 then
1737 dpr_group.deprn_rounding_flag := 'RET';
1738 elsif bk_group.deprn_rounding_flag=4 then
1739 dpr_group.deprn_rounding_flag := 'REV';
1740 elsif bk_group.deprn_rounding_flag=5 then
1741 dpr_group.deprn_rounding_flag := 'TFR';
1742 elsif bk_group.deprn_rounding_flag=6 then
1743 dpr_group.deprn_rounding_flag := 'RES';
1744 elsif bk_group.deprn_rounding_flag=7 then
1745 dpr_group.deprn_rounding_flag := 'OVE';
1746 else
1747 dpr_group.deprn_rounding_flag := NULL;
1748 end if;
1749
1750 dpr_group.asset_num := ret_group.asset_number;
1751 dpr_group.calendar_type := bk_group.d_cal;
1752 dpr_group.ceil_name := bk_group.ceiling_name;
1753 dpr_group.bonus_rule := bk_group.bonus_rule;
1754 dpr_group.method_code := bk_group.method_code;
1755 dpr_group.jdate_in_service := bk_group.jdis;
1756 dpr_group.life := bk_group.lifemonths;
1757 dpr_group.y_begin := bk_group.cpd_fiscal_year;
1758 dpr_group.y_end := bk_group.cpd_fiscal_year;
1759
1760 dpr_group.short_fiscal_year_flag := bk_group.short_fiscal_year_flag;
1761 dpr_group.conversion_date := bk_group.conversion_date;
1762 dpr_group.prorate_date := bk_group.prorate_date;
1763 dpr_group.orig_deprn_start_date := bk_group.orig_deprn_start_date;
1764
1765 if not faggrv(ret_group.asset_id, ret_group.book, cpd_ctr, dpr_group.deprn_rsv,
1766 dpr_group.reval_rsv, dpr_group.prior_fy_exp, dpr_group.ytd_deprn,
1767 dpr_group.bonus_deprn_rsv, dpr_group.bonus_ytd_deprn,
1768 dpr_group.prior_fy_bonus_exp,
1769 dpr_group.impairment_rsv, dpr_group.ytd_impairment,
1770 ret.mrc_sob_type_code,
1771 ret.set_of_books_id,
1772 p_log_level_rec) then
1773
1774 fa_srvr_msg.add_message(
1775 calling_fn => l_calling_fn,
1776 name => 'FA_RET_GENERIC_ERROR',
1777 token1 => 'MODULE',
1778 value1 => 'FAGGRV',
1779 token2 => 'INFO',
1780 value2 => 'reserve',
1781 token3 => 'ASSET',
1782 value3 => ret_group.asset_number , p_log_level_rec => p_log_level_rec);
1783
1784 return(FALSE);
1785
1786 end if;
1787
1788 dpr_group.rsv_known_flag := TRUE;
1789
1790 dpr_group.jdate_retired := jdate_retired;
1791 dpr_group.ret_prorate_jdate := ret_prorate_jdate;
1792
1793 dpr_group.p_cl_begin := dpr.p_cl_begin;
1794 dpr_group.p_cl_end := dpr.p_cl_end;
1795 deprn_amt_group := 0;
1796 dpr_group.used_by_adjustment := TRUE;
1797 dpr_group.deprn_override_used_by := 'RET'; --Bug11886090
1798 dpr_group.mrc_sob_type_code := dpr.mrc_sob_type_code;
1799
1800
1801 end if;
1802 -- Bug 5525968: end
1803
1804 -- condition modified for Bug 3849510. bk.depreciate moved from first if to
1805 -- to inner if. Call fagcdp to calculate catchup only if depreciate flag is
1806 -- is Yes. If depreciate flag is no, still let logic go through to call
1807 -- fagdpdp/farboe to calculate backout expense if prorate date falls in the
1808 -- past.
1809
1810 if p_log_level_rec.statement_level then
1811 fa_debug_pkg.add (l_calling_fn, 'bk.depr_first_year_ret BEFORE if', bk.depr_first_year_ret, p_log_level_rec => p_log_level_rec);
1812 fa_debug_pkg.add (l_calling_fn, 'periods_catchup', periods_catchup, p_log_level_rec => p_log_level_rec);
1813 fa_debug_pkg.add (l_calling_fn, 'bk.depr_first_year_ret', bk.depr_first_year_ret, p_log_level_rec => p_log_level_rec);
1814 if (bk.fully_reserved = null) then
1815 fa_debug_pkg.add (l_calling_fn, 'bk.fully_reserved', 'NULL !!');
1816 elsif (bk.fully_reserved) then
1817 fa_debug_pkg.add (l_calling_fn, 'bk.fully_reserved', 'TRUE', p_log_level_rec => p_log_level_rec);
1818 else
1819 fa_debug_pkg.add (l_calling_fn, 'bk.fully_reserved', 'FALSE', p_log_level_rec => p_log_level_rec);
1820 end if;
1821 end if;
1822 --Bug#6920756, Using l_decision_flag to judge if the asset is fully reserved/fully extended.
1823 -- Bug 8211842 : Check if asset has started extended depreciation
1824 if bk.extended_flag and bk.start_extended then
1825 l_decision_flag := bk.fully_extended;
1826 else
1827 l_decision_flag := bk.fully_reserved;
1828 end if;
1829
1830
1831 if bk.capitalize and (ret.wip_asset is null or
1832 ret.wip_asset <= 0) then
1833 -- Bug#4867806: if (periods_catchup > 0) and bk.depreciate
1834 -- Note: bk.depr_first_year_ret=> 0:1=No:Yes; Need to back out expense when bk.depr_first_year_ret=0 (No)
1835 if (periods_catchup > 0 or bk.depr_first_year_ret = 0) and bk.depreciate
1836 --and (not bk.fully_reserved) then
1837 and (not l_decision_flag) then --Bug#6920756, Using l_decision_flag instead of fully_reserved.
1838
1839 if p_log_level_rec.statement_level then fa_debug_pkg.add(l_calling_fn, 'in fagret3.1', '', p_log_level_rec => p_log_level_rec); end if;
1840
1841 --bug13014394: checking to see if override exists for retirement
1842 if fa_cache_pkg.fa_deprn_override_enabled then
1843 open c_override_exist(ret.asset_id, 'POST', l_rowid);
1844 fetch c_override_exist into l_rowid;
1845 if c_override_exist%NOTFOUND then
1846 l_override_exist := FALSE;
1847 else
1848 l_override_exist := TRUE;
1849 end if;
1850 close c_override_exist;
1851 end if;
1852
1853 if not FA_GAINLOSS_DPR_PKG.fagcdp(dpr, deprn_amt,
1854 bonus_deprn_amt,
1855 impairment_amt,
1856 reval_deprn_amt,
1857 reval_amort, bk.deprn_start_date,
1858 bk.d_cal, bk.p_cal, start_pd, end_pd,
1859 bk.prorate_fy, bk.dsd_fy, bk.prorate_jdate,
1860 bk.deprn_start_jdate, p_log_level_rec => p_log_level_rec) then
1861
1862 fa_srvr_msg.add_message(
1863 calling_fn => l_calling_fn,
1864 name => 'FA_RET_GENERIC_ERROR',
1865 token1 => 'MODULE',
1866 value1 => 'FAGCDP',
1867 token2 => 'INFO',
1868 value2 => 'depreciation number',
1869 token3 => 'ASSET',
1870 value3 => ret.asset_number , p_log_level_rec => p_log_level_rec);
1871
1872 return(FALSE);
1873
1874 end if;
1875
1876 if p_log_level_rec.statement_level then fa_debug_pkg.add(l_calling_fn, 'in fagret3.2', '', p_log_level_rec => p_log_level_rec); end if;
1877
1878 -- Bug4343087:
1879 -- In order to find correct catchup amount without rounding
1880 -- error we need to find expense before retirement(before) and
1881 -- after retirement(after) and then subtract after amount from before.
1882 -- Following portion of code finds after amounts and then subtract
1883 -- before amount that is found in previous fagcdp call.
1884 if p_log_level_rec.statement_level then
1885 fa_debug_pkg.add (l_calling_fn, 'before deprn_amt', deprn_amt, p_log_level_rec => p_log_level_rec);
1886 fa_debug_pkg.add (l_calling_fn, 'before bonus_deprn_amt', bonus_deprn_amt, p_log_level_rec => p_log_level_rec);
1887 fa_debug_pkg.add (l_calling_fn, 'before reval_deprn_amt', reval_deprn_amt, p_log_level_rec => p_log_level_rec);
1888 fa_debug_pkg.add (l_calling_fn, 'before reval_amort', reval_amort, p_log_level_rec => p_log_level_rec);
1889 end if;
1890
1891 --bug13014394: checking back to see if override is used during previous fagcdp call
1892 if fa_cache_pkg.fa_deprn_override_enabled and
1893 l_override_exist then
1894 open c_override_exist(ret.asset_id, 'POSTED', l_rowid);
1895 fetch c_override_exist into l_rowid;
1896 if c_override_exist%NOTFOUND then
1897 l_override_used := FALSE;
1898 else
1899 l_override_used := TRUE;
1900 end if;
1901 close c_override_exist;
1902 end if;
1903
1904 --bug13014394: following is necessary only if override has not been
1905 -- supplied for this gain loss calculation
1906 if not l_override_used then
1907
1908 -- Store before amount
1909 l_temp_deprn_amt := deprn_amt;
1910 l_temp_bonus_deprn_amt := bonus_deprn_amt;
1911 l_temp_impairment_amt := impairment_amt;
1912 l_temp_reval_deprn_amt := reval_deprn_amt;
1913 l_temp_reval_amort := reval_amort;
1914
1915 -- Get after fin ncial info for calling fagcdp
1916 if (ret.mrc_sob_type_code = 'R') then
1917 open c_get_new_mc_bk(ret.asset_id);
1918 fetch c_get_new_mc_bk into dpr.adj_cost
1919 , dpr.salvage_value
1920 , dpr.rec_cost
1921 , dpr.adj_rec_cost
1922 , dpr.reval_amo_basis
1923 , dpr.old_adj_cost;
1924 close c_get_new_mc_bk;
1925 else
1926 open c_get_new_bk(ret.asset_id);
1927 fetch c_get_new_bk into dpr.adj_cost
1928 , dpr.salvage_value
1929 , dpr.rec_cost
1930 , dpr.adj_rec_cost
1931 , dpr.reval_amo_basis
1932 , dpr.old_adj_cost;
1933 close c_get_new_bk;
1934 end if;
1935
1936 -- fix for 4639408
1937 dpr.deprn_rsv := cost_frac * deprn_reserve;
1938 if not FA_UTILS_PKG.faxrnd(dpr.deprn_rsv, ret.book, ret.set_of_books_id, p_log_level_rec => p_log_level_rec) then
1939 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
1940 return FALSE;
1941 end if;
1942 dpr.deprn_rsv := deprn_reserve - dpr.deprn_rsv;
1943 -- end fix for 4639408
1944
1945 if p_log_level_rec.statement_level then
1946 fa_debug_pkg.add (l_calling_fn, 'calling function', 'FA_GAINLOSS_DPR_PKG.fagcdp', p_log_level_rec => p_log_level_rec);
1947 end if;
1948
1949 -- Find new expense using after amounts
1950 if not FA_GAINLOSS_DPR_PKG.fagcdp(dpr, deprn_amt,
1951 bonus_deprn_amt,
1952 impairment_amt,
1953 reval_deprn_amt,
1954 reval_amort, bk.deprn_start_date,
1955 bk.d_cal, bk.p_cal, start_pd, end_pd,
1956 bk.prorate_fy, bk.dsd_fy, bk.prorate_jdate,
1957 bk.deprn_start_jdate, p_log_level_rec => p_log_level_rec) then
1958
1959 if p_log_level_rec.statement_level then
1960 fa_debug_pkg.add (l_calling_fn, 'calling FA_GAINLOSS_DPR_PKG.fagcdp', 'FAILED', p_log_level_rec => p_log_level_rec);
1961 end if;
1962
1963 fa_srvr_msg.add_message(
1964 calling_fn => l_calling_fn,
1965 name => 'FA_RET_GENERIC_ERROR',
1966 token1 => 'MODULE',
1967 value1 => 'FAGCDP',
1968 token2 => 'INFO',
1969 value2 => 'depreciation number',
1970 token3 => 'ASSET',
1971 value3 => ret.asset_number , p_log_level_rec => p_log_level_rec);
1972
1973 return(FALSE);
1974
1975 end if;
1976
1977 if p_log_level_rec.statement_level then
1978 fa_debug_pkg.add (l_calling_fn, 'after deprn_amt', deprn_amt, p_log_level_rec => p_log_level_rec);
1979 fa_debug_pkg.add (l_calling_fn, 'after bonus_deprn_amt', bonus_deprn_amt, p_log_level_rec => p_log_level_rec);
1980 fa_debug_pkg.add (l_calling_fn, 'after reval_deprn_amt', reval_deprn_amt, p_log_level_rec => p_log_level_rec);
1981 fa_debug_pkg.add (l_calling_fn, 'after reval_amort', reval_amort, p_log_level_rec => p_log_level_rec);
1982 end if;
1983
1984 -- Find catchup amount by subtracting before from after amounts.
1985 deprn_amt := l_temp_deprn_amt - deprn_amt;
1986 bonus_deprn_amt := l_temp_bonus_deprn_amt - bonus_deprn_amt;
1987 impairment_amt := l_temp_impairment_amt - impairment_amt;
1988 reval_deprn_amt := l_temp_reval_deprn_amt - reval_deprn_amt;
1989 reval_amort := l_temp_reval_amort - reval_amort;
1990
1991 if p_log_level_rec.statement_level then
1992 fa_debug_pkg.add (l_calling_fn, '+ final deprn_amt', deprn_amt, p_log_level_rec => p_log_level_rec);
1993 fa_debug_pkg.add (l_calling_fn, '+ final bonus_deprn_amt', bonus_deprn_amt, p_log_level_rec => p_log_level_rec);
1994 fa_debug_pkg.add (l_calling_fn, '+ final reval_deprn_amt', reval_deprn_amt, p_log_level_rec => p_log_level_rec);
1995 fa_debug_pkg.add (l_calling_fn, '+ final reval_amort', reval_amort, p_log_level_rec => p_log_level_rec);
1996 end if;
1997
1998 -- Set original value back in case subsequent calls
1999 -- are expecting before amounts
2000 dpr.adj_cost := bk.adjusted_cost;
2001 dpr.rec_cost := bk.recoverable_cost;
2002 dpr.reval_amo_basis := bk.reval_amort_basis;
2003 dpr.adj_rec_cost := bk.adj_rec_cost;
2004 dpr.salvage_value := bk.salvage_value;
2005 dpr.old_adj_cost := bk.old_adj_cost;
2006 dpr.deprn_override_used_by := null;
2007
2008 -- Bug4343087: Commenting out following four lines due to rounding issue
2009 -- deprn_amt := deprn_amt * cost_frac;
2010 -- bonus_deprn_amt := bonus_deprn_amt * cost_frac;
2011 -- reval_deprn_amt := reval_deprn_amt * cost_frac;
2012 -- reval_amort := reval_amort * cost_frac;
2013
2014 -- End of fix for bug4343087
2015
2016 /* BUG# 2482031: rounding issue - YYOON */
2017 if not FA_UTILS_PKG.faxrnd(x_amount => deprn_amt
2018 ,x_book => ret.book
2019 ,x_set_of_books_id => ret.set_of_books_id
2020 , p_log_level_rec => p_log_level_rec) then
2021 return FALSE;
2022 end if;
2023
2024
2025 if not FA_UTILS_PKG.faxrnd(x_amount => bonus_deprn_amt
2026 ,x_book => ret.book
2027 ,x_set_of_books_id => ret.set_of_books_id
2028 , p_log_level_rec => p_log_level_rec) then
2029 return FALSE;
2030 end if;
2031
2032 if not FA_UTILS_PKG.faxrnd(x_amount => impairment_amt
2033 ,x_book => ret.book
2034 ,x_set_of_books_id => ret.set_of_books_id
2035 , p_log_level_rec => p_log_level_rec) then
2036 return FALSE;
2037 end if;
2038
2039
2040 if not FA_UTILS_PKG.faxrnd(x_amount => reval_deprn_amt
2041 ,x_book => ret.book
2042 ,x_set_of_books_id => ret.set_of_books_id
2043 , p_log_level_rec => p_log_level_rec) then
2044 return FALSE;
2045 end if;
2046
2047 if not FA_UTILS_PKG.faxrnd(x_amount => reval_amort
2048 ,x_book => ret.book
2049 ,x_set_of_books_id => ret.set_of_books_id
2050 , p_log_level_rec => p_log_level_rec) then
2051 return FALSE;
2052 end if;
2053
2054 end if; -- if not l_override_used then --bug13014394
2055
2056
2057 --bug 5525968
2058
2059 end if; -- if (periods_catchup
2060
2061 if p_log_level_rec.statement_level then
2062 fa_debug_pkg.add (l_calling_fn, 'before calling fagpdp', '', p_log_level_rec => p_log_level_rec);
2063 end if;
2064
2065 -- Bug 5525968: called fagpdp only in case where expense row need to inserted for
2066 -- member with member info.
2067 if bk.group_asset_id is null or
2068 (bk.group_asset_id is not null and bk.tracking_method = 'CALCULATE') then
2069
2070 if p_log_level_rec.statement_level then
2071 fa_debug_pkg.add (l_calling_fn, 'calling fagpdp 1', '', p_log_level_rec => p_log_level_rec);
2072 end if;
2073 if not FA_GAINLOSS_UPD_PKG.fagpdp(ret, bk, dpr, today,
2074 periods_catchup, cpd_ctr,
2075 cpdnum, cost_frac, deprn_amt,
2076 bonus_deprn_amt,
2077 impairment_amt, impairment_reserve,
2078 reval_deprn_amt, reval_amort,
2079 reval_reserve, user_id, p_log_level_rec => p_log_level_rec) then
2080
2081 fa_srvr_msg.add_message(
2082 calling_fn => l_calling_fn,
2083 name => 'FA_RET_INSERT_ERROR',
2084 token1 => 'MODULE',
2085 value1 => 'FAGPDP',
2086 token2 => 'ACTION',
2087 value2 => 'insert',
2088 token3 => 'TYPE',
2089 value3 => 'Depreciation',
2090 token4 => 'ASSET',
2091 value4 => ret.asset_number , p_log_level_rec => p_log_level_rec);
2092
2093 return(FALSE);
2094
2095 end if;
2096
2097 end if; -- if (bk.group_asset_id
2098
2099 if p_log_level_rec.statement_level then
2100 fa_debug_pkg.add (l_calling_fn, 'after calling fagpdp', '', p_log_level_rec => p_log_level_rec);
2101 end if;
2102
2103 end if; -- end of - if bk.capitalize
2104
2105 --Bug11886090: Relocating below group rerelated call outs from buttom of this function so
2106 -- that we can pass reserve retired for group to fagprv
2107 if (bk.group_asset_id is not null) then
2108 -- +++++ Process Group Asse +++++
2109 if not FA_RETIREMENT_PVT.Do_Retirement_in_CGL(
2110 p_ret => ret,
2111 p_bk => bk,
2112 p_dpr => dpr,
2113 p_asset_deprn_rec_old => l_asset_deprn_rec_old,
2114 p_mrc_sob_type_code => ret.mrc_sob_type_code,
2115 p_calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec) then
2116
2117 fa_srvr_msg.add_message(calling_fn => l_calling_fn,
2118 name => 'FA_RET_INSERT_ERROR',
2119 token1 => 'MODULE',
2120 value1 => 'FA_RETIREMENT_PVT.Do_Retirement_in_CGL',
2121 token2 => 'ACTION',
2122 value2 => 'make',
2123 token3 => 'TYPE',
2124 value3 => 'table',
2125 token4 => 'ASSET',
2126 value4 => ret.asset_number , p_log_level_rec => p_log_level_rec);
2127 return false;
2128 end if;
2129
2130 if (periods_catchup > 0 or bk.depr_first_year_ret = 0) and bk.depreciate
2131 --and (not bk.fully_reserved) then
2132 and (not l_decision_flag) then --Bug#6920756, Using l_decision_flag instead of fully_reserved.
2133
2134 --bug13014394: checking to see if override exists for retirement
2135 l_rowid := null;
2136 l_override_exist := FALSE;
2137 l_override_used := FALSE;
2138
2139 if fa_cache_pkg.fa_deprn_override_enabled then
2140 open c_override_exist(bk.group_asset_id, 'POST', l_rowid);
2141 fetch c_override_exist into l_rowid;
2142 if c_override_exist%NOTFOUND then
2143 l_override_exist := FALSE;
2144 else
2145 l_override_exist := TRUE;
2146 end if;
2147 close c_override_exist;
2148 end if;
2149
2150 -- Bug 5525968: start
2151 -- calculate group info
2152 if not FA_GAINLOSS_DPR_PKG.fagcdp(dpr_group, deprn_amt_group,
2153 bonus_deprn_amt_group,
2154 impairment_amt_group,
2155 reval_deprn_amt_group,
2156 reval_amort_group, bk_group.deprn_start_date,
2157 bk_group.d_cal, bk_group.p_cal, start_pd, end_pd,
2158 bk_group.prorate_fy, bk_group.dsd_fy, bk_group.prorate_jdate,
2159 bk_group.deprn_start_jdate, p_log_level_rec => p_log_level_rec) then
2160
2161 fa_srvr_msg.add_message(
2162 calling_fn => l_calling_fn,
2163 name => 'FA_RET_GENERIC_ERROR',
2164 token1 => 'MODULE',
2165 value1 => 'FAGCDP',
2166 token2 => 'INFO',
2167 value2 => 'depreciation number',
2168 token3 => 'ASSET',
2169 value3 => ret_group.asset_number , p_log_level_rec => p_log_level_rec);
2170
2171 return(FALSE);
2172
2173 end if;
2174
2175 if p_log_level_rec.statement_level then fa_debug_pkg.add(l_calling_fn, 'in fagret3.2.2', '', p_log_level_rec => p_log_level_rec); end if;
2176
2177 -- Bug4343087:
2178 -- In order to find correct catchup amount without rounding
2179 -- error we need to find expense before retirement(before) and
2180 -- after retirement(after) and then subtract after amount from before.
2181 -- Following portion of code finds after amounts and then subtract
2182 -- before amount that is found in previous fagcdp call.
2183 if p_log_level_rec.statement_level then
2184 fa_debug_pkg.add (l_calling_fn, 'before group_deprn_amt', deprn_amt_group, p_log_level_rec => p_log_level_rec);
2185 fa_debug_pkg.add (l_calling_fn, 'before group_bonus_deprn_amt', bonus_deprn_amt_group, p_log_level_rec => p_log_level_rec);
2186 fa_debug_pkg.add (l_calling_fn, 'before group_reval_deprn_amt', reval_deprn_amt_group, p_log_level_rec => p_log_level_rec);
2187 fa_debug_pkg.add (l_calling_fn, 'before group_reval_amort', reval_amort_group, p_log_level_rec => p_log_level_rec);
2188 end if;
2189
2190 --bug13014394: checking back to see if override is used during previous fagcdp call
2191 if fa_cache_pkg.fa_deprn_override_enabled and
2192 l_override_exist then
2193 open c_override_exist(bk.group_asset_id, 'POSTED', l_rowid);
2194 fetch c_override_exist into l_rowid;
2195 if c_override_exist%NOTFOUND then
2196 l_override_used := FALSE;
2197 else
2198 l_override_used := TRUE;
2199 end if;
2200 close c_override_exist;
2201 end if;
2202
2203 --bug13014394: following is necessary only if override has not been supplied for this
2204 -- gain loss calculation
2205 if not l_override_used then
2206
2207 -- Store before amount
2208 l_temp_deprn_amt := deprn_amt_group;
2209 l_temp_bonus_deprn_amt := bonus_deprn_amt_group;
2210 l_temp_impairment_amt := impairment_amt_group;
2211 l_temp_reval_deprn_amt := reval_deprn_amt_group;
2212 l_temp_reval_amort := reval_amort_group;
2213
2214 -- Get after fin ncial info for calling fagcdp
2215 if (ret.mrc_sob_type_code = 'R') then
2216 open c_get_new_mc_bk(ret_group.asset_id);
2217 fetch c_get_new_mc_bk into dpr_group.adj_cost
2218 , dpr_group.salvage_value
2219 , dpr_group.rec_cost
2220 , dpr_group.adj_rec_cost
2221 , dpr_group.reval_amo_basis
2222 , dpr_group.old_adj_cost;
2223 close c_get_new_mc_bk;
2224 else
2225 open c_get_new_bk(ret_group.asset_id);
2226 fetch c_get_new_bk into dpr_group.adj_cost
2227 , dpr_group.salvage_value
2228 , dpr_group.rec_cost
2229 , dpr_group.adj_rec_cost
2230 , dpr_group.reval_amo_basis
2231 , dpr_group.old_adj_cost;
2232 close c_get_new_bk;
2233 end if;
2234
2235 -- fix for 4639408
2236 dpr_group.deprn_rsv := cost_frac * deprn_reserve;
2237 if not FA_UTILS_PKG.faxrnd(dpr_group.deprn_rsv
2238 ,ret_group.book
2239 ,ret.set_of_books_id
2240 ,p_log_level_rec => p_log_level_rec) then
2241 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
2242 return FALSE;
2243 end if;
2244 dpr_group.deprn_rsv := deprn_reserve - dpr_group.deprn_rsv;
2245 -- end fix for 4639408
2246
2247 if p_log_level_rec.statement_level then
2248 fa_debug_pkg.add (l_calling_fn, 'calling function', 'FA_GAINLOSS_DPR_PKG.fagcdp', p_log_level_rec => p_log_level_rec);
2249 end if;
2250
2251 -- Find new expense using after amounts
2252 if not FA_GAINLOSS_DPR_PKG.fagcdp(dpr_group, deprn_amt_group,
2253 bonus_deprn_amt_group,
2254 impairment_amt_group,
2255 reval_deprn_amt_group,
2256 reval_amort_group, bk_group.deprn_start_date,
2257 bk_group.d_cal, bk_group.p_cal, start_pd, end_pd,
2258 bk_group.prorate_fy, bk_group.dsd_fy, bk_group.prorate_jdate,
2259 bk_group.deprn_start_jdate, p_log_level_rec => p_log_level_rec) then
2260
2261 if p_log_level_rec.statement_level then
2262 fa_debug_pkg.add (l_calling_fn, 'calling FA_GAINLOSS_DPR_PKG.fagcdp', 'FAILED', p_log_level_rec => p_log_level_rec);
2263 end if;
2264
2265 fa_srvr_msg.add_message(
2266 calling_fn => l_calling_fn,
2267 name => 'FA_RET_GENERIC_ERROR',
2268 token1 => 'MODULE',
2269 value1 => 'FAGCDP',
2270 token2 => 'INFO',
2271 value2 => 'depreciation number',
2272 token3 => 'ASSET',
2273 value3 => ret_group.asset_number , p_log_level_rec => p_log_level_rec);
2274
2275 return(FALSE);
2276
2277 end if;
2278
2279 if p_log_level_rec.statement_level then
2280 fa_debug_pkg.add (l_calling_fn, 'after group_deprn_amt', deprn_amt_group, p_log_level_rec => p_log_level_rec);
2281 fa_debug_pkg.add (l_calling_fn, 'after group_bonus_deprn_amt', bonus_deprn_amt_group, p_log_level_rec => p_log_level_rec);
2282 fa_debug_pkg.add (l_calling_fn, 'after group_reval_deprn_amt', reval_deprn_amt_group, p_log_level_rec => p_log_level_rec);
2283 fa_debug_pkg.add (l_calling_fn, 'after group_reval_amort', reval_amort_group, p_log_level_rec => p_log_level_rec);
2284 end if;
2285
2286 -- Find catchup amount by subtracting before from after amounts.
2287 deprn_amt_group := l_temp_deprn_amt - deprn_amt_group;
2288 bonus_deprn_amt_group := l_temp_bonus_deprn_amt - bonus_deprn_amt_group;
2289 impairment_amt_group := l_temp_impairment_amt - impairment_amt_group;
2290 reval_deprn_amt_group := l_temp_reval_deprn_amt - reval_deprn_amt_group;
2291 reval_amort_group := l_temp_reval_amort - reval_amort_group;
2292
2293 if p_log_level_rec.statement_level then
2294 fa_debug_pkg.add (l_calling_fn, '+ final group_deprn_amt', deprn_amt_group, p_log_level_rec => p_log_level_rec);
2295 fa_debug_pkg.add (l_calling_fn, '+ final group_bonus_deprn_amt', bonus_deprn_amt_group, p_log_level_rec => p_log_level_rec);
2296 fa_debug_pkg.add (l_calling_fn, '+ final group_reval_deprn_amt', reval_deprn_amt_group, p_log_level_rec => p_log_level_rec);
2297 fa_debug_pkg.add (l_calling_fn, '+ final group_reval_amort', reval_amort_group, p_log_level_rec => p_log_level_rec);
2298 end if;
2299
2300 -- Set original value back in case subsequent calls
2301 -- are expecting before amounts
2302 dpr_group.adj_cost := bk_group.adjusted_cost;
2303 dpr_group.rec_cost := bk_group.recoverable_cost;
2304 dpr_group.reval_amo_basis := bk_group.reval_amort_basis;
2305 dpr_group.adj_rec_cost := bk_group.adj_rec_cost;
2306 dpr_group.salvage_value := bk_group.salvage_value;
2307 dpr_group.old_adj_cost := bk_group.old_adj_cost;
2308 dpr_group.deprn_override_used_by := null;
2309
2310 if not FA_UTILS_PKG.faxrnd(x_amount => deprn_amt_group
2311 ,x_book => ret.book
2312 ,x_set_of_books_id => ret.set_of_books_id
2313 , p_log_level_rec => p_log_level_rec) then
2314 return FALSE;
2315 end if;
2316
2317
2318 if not FA_UTILS_PKG.faxrnd(x_amount => bonus_deprn_amt_group
2319 ,x_book => ret.book
2320 ,x_set_of_books_id => ret.set_of_books_id
2321 , p_log_level_rec => p_log_level_rec) then
2322 return FALSE;
2323 end if;
2324
2325 if not FA_UTILS_PKG.faxrnd(x_amount => impairment_amt_group
2326 ,x_book => ret.book
2327 ,x_set_of_books_id => ret.set_of_books_id
2328 , p_log_level_rec => p_log_level_rec) then
2329 return FALSE;
2330 end if;
2331
2332
2333 if not FA_UTILS_PKG.faxrnd(x_amount => reval_deprn_amt_group
2334 ,x_book => ret.book
2335 ,x_set_of_books_id => ret.set_of_books_id
2336 , p_log_level_rec => p_log_level_rec) then
2337 return FALSE;
2338 end if;
2339
2340 if not FA_UTILS_PKG.faxrnd(x_amount => reval_amort_group
2341 ,x_book => ret.book
2342 ,x_set_of_books_id => ret.set_of_books_id
2343 , p_log_level_rec => p_log_level_rec) then
2344 return FALSE;
2345 end if;
2346
2347 end if; --if not l_override_used then --bug13014394
2348
2349 --Bug11886090: Following is necessary as ret.reserve_retired will not get reflected in fagurt
2350 -- if fa_(mc_)retirements.reserve_retired is not null
2351 -- Following is NOT necessary if we can remove nvl from update fa_retirements statement in fagurt
2352 if bk.tracking_method = 'CALCULATE' and
2353 nvl(bk.member_rollup_flag, 'N') = 'N' and
2354 nvl(deprn_amt_group, 0) <> 0 then
2355 if (ret.mrc_sob_type_code = 'R') then
2356 update fa_mc_retirements
2357 set reserve_retired = reserve_retired + deprn_amt_group
2358 where retirement_id = ret.retirement_id
2359 and set_of_books_id = ret.set_of_books_id;
2360 else
2361 update fa_retirements
2362 set reserve_retired = reserve_retired + deprn_amt_group
2363 where retirement_id = ret.retirement_id;
2364 end if;
2365 end if;
2366
2367 end if; -- if (periods_catchup > 0 or bk.depr_fi....
2368
2369 end if;
2370 --End of Bug11886090
2371
2372 --Bug#12768930 - Modified condition to allow call to fagprv for CIP asset and non sorp book
2373 if ((ret.wip_asset is NULL or ret.wip_asset <= 0) or
2374 (ret.wip_asset > 0 and nvl(fa_cache_pkg.fazcbc_record.sorp_enabled_flag,'N') = 'N')) then
2375
2376 if not FA_GAINLOSS_UPD_PKG.fagprv(ret, bk, cpd_ctr,
2377 cost_frac, today, user_id,
2378 deprn_amt, reval_deprn_amt, reval_amort,
2379 deprn_reserve, reval_reserve,
2380 bonus_deprn_amt, bonus_deprn_reserve,
2381 impairment_amt, impairment_reserve,
2382 deprn_amt_group, bonus_deprn_amt_group,
2383 p_log_level_rec => p_log_level_rec) then
2384
2385 fa_srvr_msg.add_message(
2386 calling_fn => l_calling_fn,
2387 name => 'FA_RET_INSERT_ERROR',
2388 token1 => 'MODULE',
2389 value1 => 'FAGPRV',
2390 token2 => 'ACTION',
2391 value2 => 'insert',
2392 token3 => 'TYPE',
2393 value3 => 'Depreciation Reserve',
2394 token4 => 'ASSET',
2395 value4 => ret.asset_number , p_log_level_rec => p_log_level_rec);
2396
2397 return(FALSE);
2398
2399 end if; -- end of - if not rupd.fagprv
2400
2401 end if; -- end of - if (ret.wip_asset
2402
2403 if not FA_GAINLOSS_UPD_PKG.fagpct(ret, bk, cpd_ctr, today,
2404 user_id, p_log_level_rec => p_log_level_rec) then
2405
2406 fa_srvr_msg.add_message(
2407 calling_fn => l_calling_fn,
2408 name => 'FA_RET_INSERT_ERROR',
2409 token1 => 'MODULE',
2410 value1 => 'FAGPCT',
2411 token2 => 'ACTION',
2412 value2 => 'insert',
2413 token3 => 'TYPE',
2414 value3 => 'Cost',
2415 token4 => 'ASSET',
2416 value4 => ret.asset_number , p_log_level_rec => p_log_level_rec);
2417
2418 return(FALSE);
2419
2420 end if;
2421
2422 dpr.y_begin := bk.prorate_fy;
2423 dpr.y_end := bk.cpd_fiscal_year;
2424
2425 if retpdnum = 1 then
2426
2427 dpr.y_end := bk.cpd_fiscal_year - 1;
2428 retpdnum := bk.pers_per_yr;
2429
2430 else
2431
2432 retpdnum := retpdnum - 1;
2433
2434 end if;
2435
2436 if dpr.y_end < bk.prorate_fy then
2437
2438 retpdnum := 0; /* Special value assigned */
2439
2440 end if;
2441
2442 if p_log_level_rec.statement_level then
2443 fa_debug_pkg.add (l_calling_fn, 'before calling fagurt', '', p_log_level_rec => p_log_level_rec);
2444 end if;
2445
2446 if not FA_GAINLOSS_UPD_PKG.fagurt(ret, bk, cpd_ctr, dpr,
2447 cost_frac, retpdnum,
2448 today, user_id, p_log_level_rec => p_log_level_rec) then
2449
2450 fa_srvr_msg.add_message(
2451 calling_fn => l_calling_fn,
2452 name => 'FA_RET_INSERT_ERROR',
2453 token1 => 'MODULE',
2454 value1 => 'FAGURT',
2455 token2 => 'ACTION',
2456 value2 => 'make',
2457 token3 => 'TYPE',
2458 value3 => 'table',
2459 token4 => 'ASSET',
2460 value4 => ret.asset_number , p_log_level_rec => p_log_level_rec);
2461
2462 return(FALSE);
2463
2464 end if; -- end of - if not rupd.fagurt
2465
2466 if p_log_level_rec.statement_level then
2467 fa_debug_pkg.add (l_calling_fn, 'after calling fagurt', '', p_log_level_rec => p_log_level_rec);
2468 end if;
2469
2470
2471 if (bk.group_asset_id is not null) then
2472 -- +++++ Process Group Asse +++++
2473
2474 -- Bug11886090: Code in this section has been moved up before fagprv call.
2475
2476 if bk.tracking_method is null then
2477 if p_log_level_rec.statement_level then
2478 fa_debug_pkg.add (l_calling_fn, 'calling fagpdp 2', '', p_log_level_rec => p_log_level_rec);
2479 end if;
2480 if not FA_GAINLOSS_UPD_PKG.fagpdp(ret_group, bk_group, dpr_group, today,
2481 periods_catchup, cpd_ctr,
2482 cpdnum, cost_frac, deprn_amt_group,
2483 bonus_deprn_amt_group,
2484 impairment_amt_group, impairment_reserve_group,
2485 reval_deprn_amt_group, reval_amort_group,
2486 reval_reserve_group, user_id, p_log_level_rec => p_log_level_rec) then
2487
2488 fa_srvr_msg.add_message(
2489 calling_fn => l_calling_fn,
2490 name => 'FA_RET_INSERT_ERROR',
2491 token1 => 'MODULE',
2492 value1 => 'FAGPDP',
2493 token2 => 'ACTION',
2494 value2 => 'insert',
2495 token3 => 'TYPE',
2496 value3 => 'Depreciation',
2497 token4 => 'ASSET',
2498 value4 => ret_group.asset_number , p_log_level_rec => p_log_level_rec);
2499
2500 return(FALSE);
2501
2502 end if;
2503 elsif bk.tracking_method = 'ALLOCATE' then
2504 if p_log_level_rec.statement_level then
2505 fa_debug_pkg.add (l_calling_fn, 'calling fagpdp 3', '', p_log_level_rec => p_log_level_rec);
2506 end if;
2507 if not FA_GAINLOSS_UPD_PKG.fagpdp(ret_group, bk_group, dpr_group, today,
2508 periods_catchup, cpd_ctr,
2509 cpdnum, cost_frac, deprn_amt_group,
2510 bonus_deprn_amt_group,
2511 impairment_amt_group, impairment_reserve_group,
2512 reval_deprn_amt_group, reval_amort_group,
2513 reval_reserve_group, user_id, p_log_level_rec => p_log_level_rec) then
2514
2515 fa_srvr_msg.add_message(
2516 calling_fn => l_calling_fn,
2517 name => 'FA_RET_INSERT_ERROR',
2518 token1 => 'MODULE',
2519 value1 => 'FAGPDP',
2520 token2 => 'ACTION',
2521 value2 => 'insert',
2522 token3 => 'TYPE',
2523 value3 => 'Depreciation',
2524 token4 => 'ASSET',
2525 value4 => ret_group.asset_number , p_log_level_rec => p_log_level_rec);
2526
2527 return(FALSE);
2528 end if;
2529
2530 if p_log_level_rec.statement_level then
2531 fa_debug_pkg.add (l_calling_fn, 'calling fagpdp 4', '', p_log_level_rec => p_log_level_rec);
2532 end if;
2533 if not FA_GAINLOSS_UPD_PKG.fagpdp(ret, bk, dpr_group, today,
2534 periods_catchup, cpd_ctr,
2535 cpdnum, cost_frac, deprn_amt_group,
2536 bonus_deprn_amt_group,
2537 impairment_amt_group, impairment_reserve_group,
2538 reval_deprn_amt_group, reval_amort_group,
2539 reval_reserve_group, user_id, p_log_level_rec => p_log_level_rec) then
2540
2541 fa_srvr_msg.add_message(
2542 calling_fn => l_calling_fn,
2543 name => 'FA_RET_INSERT_ERROR',
2544 token1 => 'MODULE',
2545 value1 => 'FAGPDP',
2546 token2 => 'ACTION',
2547 value2 => 'insert',
2548 token3 => 'TYPE',
2549 value3 => 'Depreciation',
2550 token4 => 'ASSET',
2551 value4 => ret.asset_number , p_log_level_rec => p_log_level_rec);
2552
2553 return(FALSE);
2554 end if;
2555
2556 elsif bk.tracking_method = 'CALCULATE' and bk.member_rollup_flag = 'N' then
2557
2558 if p_log_level_rec.statement_level then
2559 fa_debug_pkg.add (l_calling_fn, 'calling fagpdp 6', '', p_log_level_rec => p_log_level_rec);
2560 end if;
2561 if not FA_GAINLOSS_UPD_PKG.fagpdp(ret_group, bk_group, dpr_group, today,
2562 periods_catchup, cpd_ctr,
2563 cpdnum, cost_frac, deprn_amt_group,
2564 bonus_deprn_amt_group,
2565 impairment_amt_group, impairment_reserve_group,
2566 reval_deprn_amt_group, reval_amort_group,
2567 reval_reserve_group, user_id, p_log_level_rec => p_log_level_rec) then
2568
2569 fa_srvr_msg.add_message(
2570 calling_fn => l_calling_fn,
2571 name => 'FA_RET_INSERT_ERROR',
2572 token1 => 'MODULE',
2573 value1 => 'FAGPDP',
2574 token2 => 'ACTION',
2575 value2 => 'insert',
2576 token3 => 'TYPE',
2577 value3 => 'Depreciation',
2578 token4 => 'ASSET',
2579 value4 => ret_group.asset_number , p_log_level_rec => p_log_level_rec);
2580
2581 return(FALSE);
2582 end if;
2583
2584 end if; --if (bk.tracking_method */
2585 -- Bug 5525968: end
2586
2587 if not faggrp(p_ret => ret_group
2588 , p_bk => bk_group
2589 , p_current_period_counter => cpd_ctr
2590 , p_log_level_rec => p_log_level_rec) then
2591 fa_srvr_msg.add_message(
2592 calling_fn => l_calling_fn,
2593 name => 'FA_RET_INSERT_ERROR',
2594 token1 => 'MODULE',
2595 value1 => 'FAGGRP',
2596 token2 => 'ACTION',
2597 value2 => 'insert',
2598 token3 => 'TYPE',
2599 value3 => 'Depreciation Reserve',
2600 token4 => 'ASSET',
2601 value4 => ret_group.asset_number , p_log_level_rec => p_log_level_rec);
2602
2603 return(FALSE);
2604 end if;
2605 end if; -- (bk.group_asset_id is not null)
2606
2607 return(TRUE);
2608
2609 EXCEPTION
2610
2611 when others then
2612 --bug13014394
2613 if c_override_exist%ISOPEN then
2614 close c_override_exist;
2615 end if;
2616
2617 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
2618 return FALSE;
2619
2620
2621 END FAGRET;
2622
2623 END FA_GAINLOSS_RET_PKG; -- End of Package EFA_RRET