[Home] [Help]
PACKAGE BODY: APPS.FA_GAINLOSS_MIS_PKG
Source
1 PACKAGE BODY FA_GAINLOSS_MIS_PKG AS
2 /* $Header: fagmisb.pls 120.22 2011/11/04 17:22:06 rmandali ship $*/
3
4 /*============================================================================
5 | NAME faggfy |
6 | |
7 | FUNCTION It returns the fiscal year, prorate_calendar, prorate_periods|
8 | per_year through the input parameter 'xdate" |
9 | |
10 | HISTORY 1/12/89 R Rumanang Created |
11 | 08/09/90 M Chan Modified for MPL 8 |
12 | 01/08/97 S Behura Rewrote into PL/SQL |
13 |===========================================================================*/
14
15 FUNCTION faggfy(xdate in date,
16 p_cal in out nocopy varchar2,
17 pro_month in out nocopy number,
18 fiscalyr in out nocopy number,
19 fiscal_year_name in out nocopy varchar2, p_log_level_rec IN FA_API_TYPES.log_level_rec_type)
20 RETURN BOOLEAN IS
21
22 faggfy_err exception;
23
24 dummy number;
25 jxdate number;
26
27 l_calling_fn varchar2(40) := 'fa_gainloss_mis_pkg.faggfy';
28
29 BEGIN <<FAGGFY>>
30
31 jxdate := to_char(xdate, 'J');
32
33 if not fa_cache_pkg.fazccp(p_cal, fiscal_year_name, jxdate,
34 pro_month,
35 fiscalyr, dummy, p_log_level_rec => p_log_level_rec) then
36
37 -- get retirement period number in fazccp
38 fa_srvr_msg.add_message(
39 calling_fn => l_calling_fn,
40 name => 'FA_PROD_INCORRECT_DATE', p_log_level_rec => p_log_level_rec);
41
42 raise faggfy_err;
43
44 end if;
45
46 return(TRUE);
47
48 EXCEPTION
49
50 when faggfy_err then
51
52 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
53 return FALSE;
54
55 when others then
56 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
57 return FALSE;
58
59 END FAGGFY;
60
61
62 /*===========================================================================
63 | NAME fagpdi |
64 | |
65 | FUNCTION Return period information based on the deprn_calendar and |
66 | prorate_calendar |
67 | |
68 | HISTORY 01/12/89 R Rumanang Created |
69 | 06/23/89 R Rumanang Standarized |
70 | 08/21/90 M Chan return p_pds_per_year |
71 | 04/04/91 M Chan restructure the function |
72 | 01/09/97 S Behura Rewrote in PL/SQL |
73 |===========================================================================*/
74
75 Function fagpdi(book_type in varchar2, pds_per_year_ptr in out nocopy number,
76 period_type in out nocopy varchar2, cpdname in varchar2,
77 cpdnum in out nocopy number, ret_p_date in out date,
78 ret_pd in out nocopy number, p_pds_per_year_ptr in out number,
79 fiscal_year_name in out nocopy varchar2,
80 p_log_level_rec in FA_API_TYPES.log_level_rec_type) Return BOOLEAN IS
81
82 fagpdi_err exception;
83
84 dummy number;
85 ret_p_jdate number;
86 fiscal_year number;
87
88 h_book_type varchar2(30);
89 h_period_type varchar2(15);
90 h_cpdnum number;
91 h_cpdname varchar2(16);
92 h_pds_per_year integer;
93 h_p_pds_per_year integer;
94
95 l_calling_fn varchar2(40) := 'fa_gainloss_mis_pkg.fagpdi';
96
97 BEGIN <<FAGPDI>>
98
99 if p_log_level_rec.statement_level then fa_debug_pkg.add(l_calling_fn, 'in fagpdi 1', '', p_log_level_rec => p_log_level_rec); end if;
100
101 h_book_type := book_type;
102 h_cpdname := cpdname;
103
104 -- Get number of periods per year
105
106 SELECT d_cal.number_per_fiscal_year,
107 p_cal.number_per_fiscal_year,
108 bc.deprn_calendar
109 INTO h_pds_per_year,
110 h_p_pds_per_year,
111 h_period_type
112 FROM fa_calendar_types d_cal,
113 fa_calendar_types p_cal,
114 fa_book_controls bc
115 WHERE bc.deprn_calendar = d_cal.calendar_type
116 AND bc.prorate_calendar = p_cal.calendar_type
117 AND bc.book_type_code = h_book_type;
118
119 if p_log_level_rec.statement_level then
120 fa_debug_pkg.add(l_calling_fn, 'in fagpdi 2', '', p_log_level_rec => p_log_level_rec);
121 end if;
122
123 period_type := h_period_type;
124
125 -- Get current period number
126
127 SELECT fadp.period_num
128 INTO h_cpdnum
129 FROM fa_deprn_periods fadp
130 WHERE fadp.book_type_Code = h_book_type
131 AND fadp.period_name = h_cpdname;
132
133 ret_p_jdate := to_char(ret_p_date, 'J');
134
135 if p_log_level_rec.statement_level then
136 fa_debug_pkg.add(l_calling_fn, 'in fagpdi 3', '', p_log_level_rec => p_log_level_rec);
137 end if;
138
139 if not fa_cache_pkg.fazccp(period_type, fiscal_year_name,
140 ret_p_jdate,
141 ret_pd, fiscal_year, dummy, p_log_level_rec => p_log_level_rec) then
142
143 -- get retirement period number in fazccp
144 fa_srvr_msg.add_message(
145 calling_fn => l_calling_fn,
146 name => 'FA_PROD_INCORRECT_DATE', p_log_level_rec => p_log_level_rec);
147
148 raise fagpdi_err;
149
150 end if;
151
152 if p_log_level_rec.statement_level then
153 fa_debug_pkg.add(l_calling_fn, 'in fagpdi 4', '', p_log_level_rec => p_log_level_rec);
154 end if;
155
156 pds_per_year_ptr := h_pds_per_year;
157 p_pds_per_year_ptr := h_p_pds_per_year;
158 cpdnum := h_cpdnum;
159
160 return(TRUE);
161
162 EXCEPTION
163
164 when fagpdi_err then
165
166 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
167 return FALSE;
168
169 when others then
170 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
171 return FALSE;
172
173 END FAGPDI;
174
175
176 /*===========================================================================
177 | NAME faggbi |
178 | |
179 | FUNCTION Returns book information based on a retirement-id |
180 | |
181 | HISTORY 1/12/89 R Rumanang Created |
182 | 6/23/89 R Rumanang Standarized |
183 | 7/11/89 R Rumanang Fixed a bug in getting |
184 | prorate date. There maybe |
185 | possible to have 2 rows |
186 | for calendar type year. |
187 | 8/8/90 M Chan Add prorate calendar |
188 | 04/02/91 M Chan Rewrite the routine |
189 | 01/09/97 S Behura Rewrote in PL/SQL |
190 | 08/09/97 S Behura Converted to 10.7 PL/SQL |
191 |===========================================================================*/
192
193 FUNCTION faggbi(bk in out nocopy fa_ret_types.book_struct,
194 ret in out nocopy fa_ret_types.ret_struct,
195 p_log_level_rec in FA_API_TYPES.log_level_rec_type) Return BOOLEAN IS
196
197 faggbi_err exception;
198
199 h_depreciate_lastyr integer;
200 h_lifemonths number(4);
201 h_capitalize integer;
202 h_depreciate integer;
203 h_fully_reserved integer;
204 h_itc_used number;
205 h_period_num integer;
206 h_rate_source_rule integer;
207 h_deprn_basis_rule integer;
208 h_book_class integer;
209 h_wip_asset integer;
210 h_depr_first_year_ret integer;
211 h_cur_units number(15);
212 h_asset_id number(15);
213 h_retirement_id number(15);
214 h_cpd_fiscal_year number(4);
215 h_ret_fiscalyr number(15);
216 h_method_code varchar2(12);
217 h_jdis number;
218 h_prorate_jdate number;
219 h_deprn_start_jdate number;
220 h_raf number;
221 h_adj_rate number;
222 h_adjusted_cost number;
223 h_current_cost number;
224 h_recoverable_cost number;
225 h_salvage_value number;
226 h_itc_amount number;
227 h_ret_p_conv varchar2(15);
228 h_book varchar2(30);
229 h_dis_book varchar2(30);
230 h_prorate_date date;
231 h_deprn_start_date date;
232 h_date_in_srv date;
233 h_p_cal varchar2(15);
234 h_deprn_cal varchar2(15);
235 h_ret_prorate_date date;
236 h_initial_date date;
237 h_date_retired date;
238 h_ceiling_name varchar2(30);
239 h_bonus_rule varchar2(30);
240 h_dwacq integer;
241 h_same_fy integer;
242 h_reval_amort_basis number;
243 h_unrevalued_cost number;
244 h_adj_capacity number;
245 h_capacity number;
246 h_fiscal_year_name varchar2(30);
247 h_deprn_reserve number;
248 h_adj_rec_cost number;
249 h_annual_deprn_rounding_flag integer; -- NULL->0, 'ADD'->1, 'ADJ'->2
250 -- 'RET'->3, 'REV'->4, 'TRF'->5
251 -- 'RES' ->6, Others->-1
252 h_short_fiscal_year_flag varchar2(3);
253 h_conversion_date date;
254 h_orig_deprn_start_date date;
255 h_old_adj_cost number;
256 h_formula_factor number;
257 h_allowed_deprn_limit_amount number;
258 -- +++++ Group Asset related information +++++
259 h_group_asset_id FA_BOOKS.group_asset_id%type;
260 h_recognize_gain_loss FA_BOOKS.RECOGNIZE_GAIN_LOSS%TYPE;
261 h_recapture_reserve_flag FA_BOOKS.RECAPTURE_RESERVE_FLAG%TYPE;
262 h_limit_proceeds_flag FA_BOOKS.LIMIT_PROCEEDS_FLAG%TYPE;
263 h_terminal_gain_loss FA_BOOKS.TERMINAL_GAIN_LOSS%TYPE;
264 h_tracking_method FA_BOOKS.TRACKING_METHOD%TYPE;
265 h_exclude_fully_rsv_flag FA_BOOKS.EXCLUDE_FULLY_RSV_FLAG%TYPE;
266 h_excess_allocation_option FA_BOOKS.EXCESS_ALLOCATION_OPTION%TYPE;
267 h_depreciation_option FA_BOOKS.DEPRECIATION_OPTION%TYPE;
268 h_member_rollup_flag FA_BOOKS.MEMBER_ROLLUP_FLAG%TYPE;
269 h_ltd_proceeds FA_BOOKS.LTD_PROCEEDS%TYPE;
270 h_allocate_to_fully_rsv_flag FA_BOOKS.ALLOCATE_TO_FULLY_RSV_FLAG%TYPE;
271 h_allocate_to_fully_ret_flag FA_BOOKS.ALLOCATE_TO_FULLY_RET_FLAG%TYPE;
272 h_eofy_reserve FA_BOOKS.EOFY_RESERVE%TYPE;
273 h_cip_cost FA_BOOKS.CIP_COST%TYPE;
274 h_ltd_cost_of_removal FA_BOOKS.LTD_COST_OF_REMOVAL%TYPE;
275 h_prior_eofy_reserve FA_BOOKS.PRIOR_EOFY_RESERVE%TYPE;
276 h_eop_adj_cost FA_BOOKS.EOP_ADJ_COST%TYPE;
277 h_eop_formula_factor FA_BOOKS.EOP_FORMULA_FACTOR%TYPE;
278 h_exclude_proceeds_from_basis FA_BOOKS.EXCLUDE_PROCEEDS_FROM_BASIS%TYPE;
279 h_retirement_deprn_option FA_BOOKS.RETIREMENT_DEPRN_OPTION%TYPE;
280 h_terminal_gain_loss_amount FA_BOOKS.terminal_gain_loss_amount%type;
281 h_pc_fully_reserved number;
282 -- Bug 6660490 Japan Phase3 bug
283 h_fully_extended integer;
284 h_pc_fully_extended number;
285 h_extended_flag integer;
286
287 -- Bug 8211842
288 h_pc_extended number;
289 h_current_pc number;
290
291 l_calling_fn varchar2(40) := 'fa_gainloss_mis_pkg.faggbi';
292
293 BEGIN <<FAGGBI>>
294
295 if p_log_level_rec.statement_level then fa_debug_pkg.add(l_calling_fn, 'in faggbi 1', '', p_log_level_rec => p_log_level_rec); end if;
296 h_adj_capacity := 0;
297 h_capacity := 0;
298 h_unrevalued_cost := 0;
299 h_reval_amort_basis := 0;
300 h_raf := 0;
301 h_adj_rate := 0;
302 h_adjusted_cost := 0;
303 h_current_cost := 0;
304 h_recoverable_cost := 0;
305 h_salvage_value := 0;
306 h_itc_amount := 0;
307 h_adj_rec_cost := 0;
308 h_old_adj_cost := 0;
309 h_formula_factor := 0;
310
311 h_asset_id := ret.asset_id;
312 h_retirement_id := ret.retirement_id;
313
314 h_date_retired := ret.date_retired;
315 h_book := ret.book;
316 h_ret_p_conv := ret.prorate_convention;
317 h_dwacq := 0;
318
319
320 -- To calculate the prorate date based on the date_retired, you need to
321 -- run the date_retired through the prorate convention logic */
322
323
324 if (ret.mrc_sob_type_code <> 'R') then
325
326 SELECT
327 decode(m.depreciate_lastyear_flag,'YES',1,0),
328 nvl(book.life_in_months,0),
329 decode(book.capitalize_flag, 'YES', 1, 0),
330 decode(book.depreciate_flag, 'YES', 1, 0),
331 decode(book.period_counter_fully_reserved, null, 0, 1),
332 nvl(book.itc_amount_id, 0),
333 ah.units,
334 bc.current_fiscal_year,
335 bc.distribution_source_book,
336 book.rate_adjustment_factor,
337 nvl(book.adjusted_rate,0),
338 book.adjusted_cost,
339 book.cost,
340 book.recoverable_cost,
341 book.itc_amount,
342 nvl(book.salvage_value,0),
343 trunc(book.prorate_date),
344 to_number(to_char(book.prorate_date, 'J')),
345 decode(instr(m.rate_source_rule, 'CALCULATED'), 0,
346 trunc(book.deprn_start_date), trunc(book.prorate_date)),
347 decode(instr(m.rate_source_rule, 'CALCULATED'), 0,
348 to_number(to_char(book.deprn_start_date, 'J')),
349 to_number(to_char(book.prorate_date, 'J'))),
350 trunc(book.date_placed_in_service),
351 to_number(to_char(book.date_placed_in_service, 'J')),
352 bc.prorate_calendar,
353 m.method_code,
354 decode(bc.DEPR_FIRST_YEAR_RET_FLAG, 'YES', 1, 0),
355 conv.prorate_date,
356 trunc(bc.initial_date),
357 bc.deprn_calendar,
358 nvl(book.ceiling_name, null),
359 nvl(book.bonus_rule, null),
360 decode(m.rate_source_rule, 'CALCULATED', 1, 'TABLE', 2,
361 'FLAT', 3),
362 decode(m.deprn_basis_rule, 'COST', 1, 'NBV', 2),
363 decode(bc.book_class, 'TAX', 1, 0),
364 decode(ah.asset_type, 'CIP', 1, 0),
365 decode(ctype.depr_when_acquired_flag,'YES',1,0),
366 nvl(book.reval_amortization_basis,0),
367 book.unrevalued_cost,
368 nvl(book.adjusted_capacity,0),
369 nvl(book.production_capacity,0),
370 bc.fiscal_year_name,
371 nvl (book.adjusted_recoverable_cost, book.recoverable_cost),
372 decode(book.annual_deprn_rounding_flag, NULL, 0, 'ADD', 1,
373 'ADJ', 2, 'RET', 3, 'REV', 4, 'TFR', 5,'RES', 6, 'OVE', 7, -1),
374 nvl(book.short_fiscal_year_flag, 'NO'),
375 book.conversion_date,
376 book.original_deprn_start_date,
377 nvl(book.old_adjusted_cost, 1),
378 nvl(book.formula_factor, 1),
379 book.allowed_deprn_limit_amount,
380 book.group_asset_id,
381 book.recognize_gain_loss,
382 book.recapture_reserve_flag,
383 book.limit_proceeds_flag,
384 book.terminal_gain_loss,
385 book.tracking_method,
386 book.exclude_fully_rsv_flag,
387 book.excess_allocation_option,
388 book.depreciation_option,
389 book.member_rollup_flag,
390 book.ltd_proceeds,
391 book.allocate_to_fully_rsv_flag,
392 book.allocate_to_fully_ret_flag,
393 book.eofy_reserve,
394 book.cip_cost,
395 book.ltd_cost_of_removal,
396 book.prior_eofy_reserve,
397 book.eop_adj_cost,
398 book.eop_formula_factor,
399 book.exclude_proceeds_from_basis,
400 book.retirement_deprn_option,
401 book.terminal_gain_loss_amount,
402 book.period_counter_fully_reserved,
403 decode(book.period_counter_fully_extended, null, 0, 1),
404 book.period_counter_fully_extended,
405 decode(book.extended_deprn_flag,'Y', 1, 0),
406 book.extended_depreciation_period,
407 bc.last_period_counter + 1
408 INTO
409 h_depreciate_lastyr,
410 h_lifemonths,
411 h_capitalize,
412 h_depreciate,
413 h_fully_reserved,
414 h_itc_used,
415 h_cur_units,
416 h_cpd_fiscal_year,
417 h_dis_book,
418 h_raf,
419 h_adj_rate,
420 h_adjusted_cost,
421 h_current_cost,
422 h_recoverable_cost,
423 h_itc_amount,
424 h_salvage_value,
425 h_prorate_date,
426 h_prorate_jdate,
427 h_deprn_start_date,
428 h_deprn_start_jdate,
429 h_date_in_srv,
430 h_jdis,
431 h_p_cal,
432 h_method_code,
433 h_depr_first_year_ret,
434 h_ret_prorate_date,
435 h_initial_date,
436 h_deprn_cal,
437 h_ceiling_name,
438 h_bonus_rule,
439 h_rate_source_rule,
440 h_deprn_basis_rule,
441 h_book_class,
442 h_wip_asset,
443 h_dwacq,
444 h_reval_amort_basis,
445 h_unrevalued_cost,
446 h_adj_capacity,
447 h_capacity,
448 h_fiscal_year_name,
449 h_adj_rec_cost,
450 h_annual_deprn_rounding_flag,
451 h_short_fiscal_year_flag,
452 h_conversion_date,
453 h_orig_deprn_start_date,
454 h_old_adj_cost,
455 h_formula_factor,
456 h_allowed_deprn_limit_amount,
457 h_group_asset_id,
458 h_recognize_gain_loss,
459 h_recapture_reserve_flag,
460 h_limit_proceeds_flag,
461 h_terminal_gain_loss,
462 h_tracking_method,
463 h_exclude_fully_rsv_flag,
464 h_excess_allocation_option,
465 h_depreciation_option,
466 h_member_rollup_flag,
467 h_ltd_proceeds,
468 h_allocate_to_fully_rsv_flag,
469 h_allocate_to_fully_ret_flag,
470 h_eofy_reserve,
471 h_cip_cost,
472 h_ltd_cost_of_removal,
473 h_prior_eofy_reserve,
474 h_eop_adj_cost,
475 h_eop_formula_factor,
476 h_exclude_proceeds_from_basis,
477 h_retirement_deprn_option,
478 h_terminal_gain_loss_amount,
479 h_pc_fully_reserved,
480 h_fully_extended, -- Bug 6660490
481 h_pc_fully_extended, -- Bug 6660490
482 h_extended_flag, -- Bug 6660490
483 h_pc_extended, -- Bug 8211842
484 h_current_pc -- Bug 8211842
485 FROM
486 fa_books book,
487 fa_methods m,
488 fa_conventions conv,
489 fa_convention_types ctype,
490 fa_book_controls bc,
491 fa_asset_history ah
492 WHERE
493 book.retirement_id = h_retirement_id
494 AND book.asset_id = h_asset_id
495 AND book.book_type_code = h_book
496 AND book.deprn_method_code = m.method_code
497 AND nvl(book.life_in_months,1) = nvl(m.life_in_months,1)
498 AND
499 bc.book_type_code = h_book
500 AND
501 ah.asset_id = h_asset_id
502 AND
503 book.transaction_header_id_out <=
504 nvl(ah.transaction_header_id_out,
505 book.transaction_header_id_out)
506 AND
507 book.transaction_header_id_out >
508 ah.transaction_header_id_in
509 AND trunc(h_date_retired) between
510 conv.start_date and conv.end_date
511 AND h_ret_p_conv = conv.prorate_convention_code
512 AND ctype.prorate_convention_code = h_ret_p_conv;
513
514 else
515
516 SELECT
517 decode(m.depreciate_lastyear_flag,'YES',1,0),
518 nvl(book.life_in_months,0),
519 decode(book.capitalize_flag, 'YES', 1, 0),
520 decode(book.depreciate_flag, 'YES', 1, 0),
521 decode(book.period_counter_fully_reserved, null, 0, 1),
522 nvl(book.itc_amount_id, 0),
523 ah.units,
524 bc.current_fiscal_year,
525 bc_primary.distribution_source_book,
526 book.rate_adjustment_factor,
527 nvl(book.adjusted_rate,0),
528 book.adjusted_cost,
529 book.cost,
530 book.recoverable_cost,
531 book.itc_amount,
532 nvl(book.salvage_value,0),
533 trunc(book.prorate_date),
534 to_number(to_char(book.prorate_date, 'J')),
535 decode(instr(m.rate_source_rule, 'CALCULATED'), 0,
536 trunc(book.deprn_start_date), trunc(book.prorate_date)),
537 decode(instr(m.rate_source_rule, 'CALCULATED'), 0,
538 to_number(to_char(book.deprn_start_date, 'J')),
539 to_number(to_char(book.prorate_date, 'J'))),
540 trunc(book.date_placed_in_service),
541 to_number(to_char(book.date_placed_in_service, 'J')),
542 bc_primary.prorate_calendar,
543 m.method_code,
544 decode(bc_primary.DEPR_FIRST_YEAR_RET_FLAG, 'YES', 1, 0),
545 conv.prorate_date,
546 trunc(bc_primary.initial_date),
547 bc_primary.deprn_calendar,
548 nvl(book.ceiling_name, null),
549 nvl(book.bonus_rule, null),
550 decode(m.rate_source_rule, 'CALCULATED', 1, 'TABLE', 2,
551 'FLAT', 3),
552 decode(m.deprn_basis_rule, 'COST', 1, 'NBV', 2),
553 decode(bc_primary.book_class, 'TAX', 1, 0),
554 decode(ah.asset_type, 'CIP', 1, 0),
555 decode(ctype.depr_when_acquired_flag,'YES',1,0),
556 nvl(book.reval_amortization_basis,0),
557 book.unrevalued_cost,
558 nvl(book.adjusted_capacity,0),
559 nvl(book.production_capacity,0),
560 bc_primary.fiscal_year_name,
561 nvl (book.adjusted_recoverable_cost, book.recoverable_cost),
562 decode(book.annual_deprn_rounding_flag, NULL, 0, 'ADD', 1,
563 'ADJ', 2, 'RET', 3, 'REV', 4, 'TFR', 5,'RES', 6, 'OVE', 7, -1),
564 nvl(book.short_fiscal_year_flag, 'NO'),
565 book.conversion_date,
566 book.original_deprn_start_date,
567 nvl(book.old_adjusted_cost, 1),
568 nvl(book.formula_factor, 1),
569 book.allowed_deprn_limit_amount,
570 book.group_asset_id,
571 book.recognize_gain_loss,
572 book.recapture_reserve_flag,
573 book.limit_proceeds_flag,
574 book.terminal_gain_loss,
575 book.tracking_method,
576 book.exclude_fully_rsv_flag,
577 book.excess_allocation_option,
578 book.depreciation_option,
579 book.member_rollup_flag,
580 book.ltd_proceeds,
581 book.allocate_to_fully_rsv_flag,
582 book.allocate_to_fully_ret_flag,
583 book.eofy_reserve,
584 book.cip_cost,
585 book.ltd_cost_of_removal,
586 book.prior_eofy_reserve,
587 book.eop_adj_cost,
588 book.eop_formula_factor,
589 book.exclude_proceeds_from_basis,
590 book.retirement_deprn_option,
591 book.terminal_gain_loss_amount,
592 book.period_counter_fully_reserved
593 INTO
594 h_depreciate_lastyr,
595 h_lifemonths,
596 h_capitalize,
597 h_depreciate,
598 h_fully_reserved,
599 h_itc_used,
600 h_cur_units,
601 h_cpd_fiscal_year,
602 h_dis_book,
603 h_raf,
604 h_adj_rate,
605 h_adjusted_cost,
606 h_current_cost,
607 h_recoverable_cost,
608 h_itc_amount,
609 h_salvage_value,
610 h_prorate_date,
611 h_prorate_jdate,
612 h_deprn_start_date,
613 h_deprn_start_jdate,
614 h_date_in_srv,
615 h_jdis,
616 h_p_cal,
617 h_method_code,
618 h_depr_first_year_ret,
619 h_ret_prorate_date,
620 h_initial_date,
621 h_deprn_cal,
622 h_ceiling_name,
623 h_bonus_rule,
624 h_rate_source_rule,
625 h_deprn_basis_rule,
626 h_book_class,
627 h_wip_asset,
628 h_dwacq,
629 h_reval_amort_basis,
630 h_unrevalued_cost,
631 h_adj_capacity,
632 h_capacity,
633 h_fiscal_year_name,
634 h_adj_rec_cost,
635 h_annual_deprn_rounding_flag,
636 h_short_fiscal_year_flag,
637 h_conversion_date,
638 h_orig_deprn_start_date,
639 h_old_adj_cost,
640 h_formula_factor,
641 h_allowed_deprn_limit_amount,
642 h_group_asset_id,
643 h_recognize_gain_loss,
644 h_recapture_reserve_flag,
645 h_limit_proceeds_flag,
646 h_terminal_gain_loss,
647 h_tracking_method,
648 h_exclude_fully_rsv_flag,
649 h_excess_allocation_option,
650 h_depreciation_option,
651 h_member_rollup_flag,
652 h_ltd_proceeds,
653 h_allocate_to_fully_rsv_flag,
654 h_allocate_to_fully_ret_flag,
655 h_eofy_reserve,
656 h_cip_cost,
657 h_ltd_cost_of_removal,
658 h_prior_eofy_reserve,
659 h_eop_adj_cost,
660 h_eop_formula_factor,
661 h_exclude_proceeds_from_basis,
662 h_retirement_deprn_option,
663 h_terminal_gain_loss_amount,
664 h_pc_fully_reserved
665 FROM
666 fa_mc_books book,
667 fa_methods m,
668 fa_conventions conv,
669 fa_convention_types ctype,
670 fa_mc_book_controls bc,
671 fa_book_controls bc_primary,
672 fa_asset_history ah
673 WHERE
674 book.retirement_id = h_retirement_id
675 AND book.asset_id = h_asset_id
676 AND book.book_type_code = h_book
677 AND book.deprn_method_code = m.method_code
678 AND nvl(book.life_in_months,1) = nvl(m.life_in_months,1)
679 AND
680 bc.book_type_code = h_book
681 AND
682 ah.asset_id = h_asset_id
683 AND
684 book.transaction_header_id_out <=
685 nvl(ah.transaction_header_id_out,
686 book.transaction_header_id_out)
687 AND
688 book.transaction_header_id_out >
689 ah.transaction_header_id_in
690 AND trunc(h_date_retired) between
691 conv.start_date and conv.end_date
692 AND h_ret_p_conv = conv.prorate_convention_code
693 AND book.set_of_books_id = ret.set_of_books_id
694 AND bc.set_of_books_id = ret.set_of_books_id
695 AND bc_primary.book_type_code = bc.book_type_code
696 AND ctype.prorate_convention_code = h_ret_p_conv;
697
698 end if;
699
700 if p_log_level_rec.statement_level then fa_debug_pkg.add(l_calling_fn, 'in faggbi 2', '', p_log_level_rec => p_log_level_rec); end if;
701
702 h_same_fy := 0;
703
704 select decode(fy1.fiscal_year, fy2.fiscal_year,1,0)
705 INTO h_same_fy
706 FROM FA_FISCAL_YEAR FY1, FA_FISCAL_YEAR FY2
707 WHERE trunc(h_date_retired) between fy1.start_date and fy1.end_date
708 AND trunc(h_deprn_start_date)
709 between fy2.start_date and fy2.end_date
710 AND fy1.fiscal_year_name = h_fiscal_year_name
711 AND fy2.fiscal_year_name = h_fiscal_year_name;
712
713 if (h_same_fy > 0) and (h_depr_first_year_ret is null or
714 h_depr_first_year_ret <= 0) then
715
716 h_ret_prorate_date := h_prorate_date;
717
718 end if;
719
720 if p_log_level_rec.statement_level then
721 fa_debug_pkg.add(l_calling_fn, 'in faggbi 3', '', p_log_level_rec => p_log_level_rec);
722 end if;
723
724
725 /* For asset with convention code that the */
726 /* "DEPRECIATE_WHEN_ACQUIRED_FLAG = 'YES' and in its first fiscal year,
727 and the method is not STL, then use the use the date retired and
728 not the retirement prorate date ; Changed made after the discussion
729 with Dave and Gregg - 9/10/91 */
730 /* Removed after discussion with Dave, no longer works with the */
731 /* current model. -Steve */
732
733 -- if(h_same_fy and h_dwacq and (h_rate_source_rule <> 1)) {
734 -- DISCARD NLSSCPY((text *) h_ret_prorate_date.arr,
735 -- (text *) h_date_retired.arr);
736 -- h_ret_prorate_date.len = h_date_retired.len;
737 -- }
738
739 /* Note 2 : If the deprn method is flat rate, just make sure the join to */
740 /* life_in_months always return TRUE */
741
742
743
744 SELECT FISCAL.FISCAL_YEAR
745 INTO h_ret_fiscalyr
746 FROM FA_FISCAL_YEAR FISCAL
747 WHERE trunc(h_ret_prorate_date)
748 between START_DATE and END_DATE
749 AND fiscal_year_name = h_fiscal_year_name;
750
751 if p_log_level_rec.statement_level then
752 fa_debug_pkg.add(l_calling_fn, 'in faggbi 4', '', p_log_level_rec => p_log_level_rec);
753 end if;
754
755 if (h_ret_fiscalyr <> h_cpd_fiscal_year) then
756
757 if h_ret_fiscalyr < h_cpd_fiscal_year then
758 h_period_num := 1;
759 else h_period_num := 0;
760 end if;
761
762 /*
763 if p_log_level_rec.statement_level then
764 -- Retirement Prorate Date
765 end if;
766 */
767
768 if h_ret_fiscalyr > h_cpd_fiscal_year then
769
770 select trunc(start_date)
771 into h_ret_prorate_date
772 from fa_fiscal_year
773 where fiscal_year = h_ret_fiscalyr
774 and fiscal_year_name = h_fiscal_year_name;
775 else
776 SELECT start_date
777 INTO h_ret_prorate_date
778 FROM fa_fiscal_year
779 where fiscal_year = h_cpd_fiscal_year
780 and fiscal_year_name = h_fiscal_year_name;
781 end if;
782
783 end if; -- end of - if (h_ret_fiscalyr
784
785 if p_log_level_rec.statement_level then
786 fa_debug_pkg.add(l_calling_fn, 'in faggbi 5', '', p_log_level_rec => p_log_level_rec);
787 end if;
788
789 /* Note 1 :
790 If the retirement prorate-date is less than the prorate date
791 we need to calculate GL adjusment based on the prorate date. Thus, the
792 retirement prorate date is the prorate date
793
794 */
795
796 if h_rate_source_rule <> 1 then -- rate_source_rule <> 'CALCULATED'
797 /* Need the following condition for fix to bug 712568. Not all
798 periods deprecition expense was getting backed out nocopy because
799 retirement prorate date was not getting set correctly when the
800 asset was retired in first year and Depreciate When Retired in
801 First Year was set to No in Book Controls. To back out all the
802 periods we have to get the period in which we started to allocate
803 depreciation
804 */
805
806 if p_log_level_rec.statement_level then
807 fa_debug_pkg.add(l_calling_fn, 'in faggbi 6', '', p_log_level_rec => p_log_level_rec);
808 end if;
809
810 if (h_same_fy > 0) and
811 (h_depr_first_year_ret is null or h_depr_first_year_ret <= 0) and
812 (h_dwacq > 0) then
813
814 begin
815 SELECT h_deprn_start_date
816 INTO h_ret_prorate_date
817 FROM dual;
818 exception
819 when no_data_found then
820 null;
821 end;
822 else
823 begin
824 SELECT h_deprn_start_date
825 INTO h_ret_prorate_date
826 FROM dual
827 where trunc(h_ret_prorate_date) < trunc(h_deprn_start_date);
828 exception
829 when no_data_found then
830 null;
831 end;
832 end if;
833
834 else
835
836 if p_log_level_rec.statement_level then
837 fa_debug_pkg.add(l_calling_fn, 'in faggbi 7', '', p_log_level_rec => p_log_level_rec);
838 fa_debug_pkg.add(l_calling_fn, to_char(trunc(h_ret_prorate_date)), '');
839 fa_debug_pkg.add(l_calling_fn, to_char(trunc(h_prorate_date)), '');
840 end if;
841
842 begin
843 SELECT h_prorate_date
844 INTO h_ret_prorate_date
845 FROM dual
846 WHERE trunc(h_ret_prorate_date) < trunc(h_prorate_date);
847 exception
848 when no_data_found then
849 null;
850 end;
851
852 end if;
853
854 --Bug7414920
855 --Bug8288367. The previous bug take care of only previous month convention dates.
856 --I added code for following month convention also.
857
858 --Bug11886090: Honor retirement prorate convention if CALCULATE tracking method w/o sum-up
859 if (( h_group_asset_id is not null )
860 or (nvl(fa_cache_pkg.fazcdrd_record.rule_name,'ZZ') = 'ENERGY PERIOD END BALANCE' )) /* Added condition for ER 12600386 */
861 and
862 not(nvl(h_tracking_method, 'NULL') = 'CALCULATE' and
863 nvl(h_member_rollup_flag, 'N') = 'N') then
864
865 begin
866 SELECT h_date_retired
867 INTO h_ret_prorate_date
868 from fa_deprn_periods
869 where book_type_code = h_book
870 and period_close_date is null
871 and (h_ret_prorate_date < calendar_period_open_date or h_ret_prorate_date > calendar_period_close_date);
872 exception
873 when no_data_found then
874 null;
875 end;
876 end if;
877 --End of changes for bug7414920
878
879 if p_log_level_rec.statement_level then
880 fa_debug_pkg.add(l_calling_fn, 'Final h_ret_prorate_date', to_char(h_ret_prorate_date));
881 fa_debug_pkg.add(l_calling_fn, 'in faggbi 8', '', p_log_level_rec => p_log_level_rec);
882 end if;
883
884 bk.prorate_date := h_prorate_date;
885 bk.deprn_start_date := h_deprn_start_date;
886 bk.ret_prorate_date := h_ret_prorate_date;
887 bk.date_in_srv := h_date_in_srv;
888 bk.p_cal := h_p_cal;
889 bk.d_cal := h_deprn_cal;
890 bk.ceiling_name := h_ceiling_name;
891 bk.bonus_rule := h_bonus_rule;
892 bk.dis_book := h_dis_book;
893 bk.lifemonths := h_lifemonths;
894 bk.depr_first_year_ret := h_depr_first_year_ret;
895
896 if h_capitalize > 0 then
897 bk.capitalize := TRUE;
898 else
899 bk.capitalize := FALSE;
900 end if;
901
902 if h_depreciate > 0 then
903 bk.depreciate := TRUE;
904 else
905 bk.depreciate := FALSE;
906 end if;
907
908 if h_fully_reserved > 0 then
909 bk.fully_reserved := TRUE;
910 else
911 bk.fully_reserved := FALSE;
912 end if;
913
914 -- Bug 6660490 new variables for
915 -- extended deprn
916 if h_extended_flag > 0 then
917 bk.extended_flag := TRUE;
918 else
919 bk.extended_flag := FALSE;
920 end if;
921
922 if h_fully_extended > 0 then
923 bk.fully_extended := TRUE;
924 else
925 bk.fully_extended := FALSE;
926 end if;
927 bk.pc_fully_extended := h_pc_fully_extended;
928 -- Bug 6660490 end
929
930 -- Bug 8211842 : Check if asset has started extended depreciation
931 if h_current_pc >= h_pc_extended then
932 bk.start_extended := TRUE;
933 else
934 bk.start_extended := FALSE;
935 end if;
936 bk.pc_extended := h_pc_extended ;
937 -- Bug 8211842
938
939 if h_depreciate_lastyr > 0 then
940 bk.depreciate_lastyr := TRUE;
941 else
942 bk.depreciate_lastyr := FALSE;
943 end if;
944
945 if h_book_class > 0 then
946 bk.book_class := TRUE;
947 else
948 bk.book_class := FALSE;
949 end if;
950
951 bk.itc_used := h_itc_used;
952 bk.rate_source_rule := h_rate_source_rule;
953 bk.deprn_basis_rule := h_deprn_basis_rule;
954 bk.cur_units := h_cur_units;
955 bk.method_code := h_method_code;
956 bk.cpd_fiscal_year := h_cpd_fiscal_year;
957 bk.jdis := h_jdis;
958 bk.prorate_jdate := h_prorate_jdate;
959 bk.deprn_start_jdate := h_deprn_start_jdate;
960 bk.ret_fiscal_year := h_ret_fiscalyr;
961 bk.raf := h_raf;
962 bk.adjusted_cost := h_adjusted_cost;
963 bk.adj_rate := h_adj_rate;
964 bk.current_cost := h_current_cost;
965 bk.recoverable_cost := h_recoverable_cost;
966 bk.itc_amount := h_itc_amount;
967 bk.salvage_value := h_salvage_value;
968 bk.reval_amort_basis := h_reval_amort_basis;
969 bk.unrevalued_cost := h_unrevalued_cost;
970 bk.adj_capacity := h_adj_capacity;
971 bk.capacity := h_capacity;
972 bk.fiscal_year_name := h_fiscal_year_name;
973 bk.adj_rec_cost := h_adj_rec_cost;
974 -- +++++ Copy h_annual_deprn_rounding_flag to book_struct. +++++
975 bk.deprn_rounding_flag := h_annual_deprn_rounding_flag;
976 bk.short_fiscal_year_flag := h_short_fiscal_year_flag;
977 bk.conversion_date := h_conversion_date;
978 bk.orig_deprn_start_date := h_orig_deprn_start_date;
979 bk.old_adj_cost := h_old_adj_cost;
980 bk.formula_factor := h_formula_factor;
981 -- +++++ Added for Group Asset +++++
982 bk.group_asset_id := h_group_asset_id;
983 bk.recognize_gain_loss := h_recognize_gain_loss;
984 bk.recapture_reserve_flag := h_recapture_reserve_flag;
985 bk.limit_proceeds_flag := h_limit_proceeds_flag;
986 bk.terminal_gain_loss := h_terminal_gain_loss;
987 bk.tracking_method := h_tracking_method;
988 bk.exclude_fully_rsv_flag := h_exclude_fully_rsv_flag;
989 bk.excess_allocation_option := h_excess_allocation_option;
990 bk.depreciation_option := h_depreciation_option;
991 bk.member_rollup_flag := h_member_rollup_flag;
992 bk.ltd_proceeds := h_ltd_proceeds;
993 bk.allocate_to_fully_rsv_flag := h_allocate_to_fully_rsv_flag;
994 bk.allocate_to_fully_ret_flag := h_allocate_to_fully_ret_flag;
995 bk.eofy_reserve := h_eofy_reserve;
996 bk.cip_cost := h_cip_cost;
997 bk.ltd_cost_of_removal := h_ltd_cost_of_removal;
998 bk.prior_eofy_reserve := h_prior_eofy_reserve;
999 bk.eop_adj_cost := h_eop_adj_cost;
1000 bk.eop_formula_factor := h_eop_formula_factor;
1001 bk.exclude_proceeds_from_basis := h_exclude_proceeds_from_basis;
1002 bk.retirement_deprn_option := h_retirement_deprn_option;
1003 bk.terminal_gain_loss_amount := h_terminal_gain_loss_amount;
1004 ret.wip_asset := h_wip_asset;
1005 bk.pc_fully_reserved := h_pc_fully_reserved;
1006
1007 if p_log_level_rec.statement_level then fa_debug_pkg.add(l_calling_fn, 'in faggbi 100', '', p_log_level_rec => p_log_level_rec); end if;
1008
1009 return(TRUE);
1010
1011 EXCEPTION
1012
1013 when faggbi_err then
1014
1015 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
1016 return FALSE;
1017
1018 when others then
1019 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
1020 return FALSE;
1021
1022 END FAGGBI;
1023
1024 END FA_GAINLOSS_MIS_PKG; -- End of Package EFA_RMIS