[Home] [Help]
PACKAGE BODY: APPS.FA_DEPRN_ROLLBACK_PVT
Source
1 PACKAGE BODY FA_DEPRN_ROLLBACK_PVT AS
2 /* $Header: FAVDRBB.pls 120.15.12010000.1 2008/07/28 13:18:22 appldev ship $ */
3
4 g_print_debug boolean := fa_cache_pkg.fa_print_debug;
5
6
7 function do_rollback (
8 p_asset_hdr_rec IN fa_api_types.asset_hdr_rec_type,
9 p_period_rec IN fa_api_types.period_rec_type,
10 p_deprn_run_id IN NUMBER,
11 p_reversal_event_id IN NUMBER,
12 p_reversal_date IN DATE,
13 p_deprn_exists_count IN NUMBER,
14 p_mrc_sob_type_code IN VARCHAR2,
15 p_calling_fn IN VARCHAR2,
16 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) return boolean as
17
18 CURSOR c_get_thid IS
19 select transaction_header_id
20 , event_id
21 from fa_transaction_headers
22 where asset_id = p_asset_hdr_rec.asset_id
23 and book_type_code = p_asset_hdr_rec.book_type_code
24 and date_effective between p_period_rec.period_open_date
25 and nvl(p_period_rec.period_close_date, sysdate)
26 and calling_interface = 'FADEPR'
27 and transaction_key = 'TG';
28
29 CURSOR c_get_new_thid IS
30 select transaction_header_id
31 , date_effective
32 from fa_transaction_headers
33 where asset_id = p_asset_hdr_rec.asset_id
34 and book_type_code = p_asset_hdr_rec.book_type_code
35 and date_effective between p_period_rec.period_open_date
36 and nvl(p_period_rec.period_close_date, sysdate)
37 and calling_interface = 'FAXDRB'
38 and transaction_key = 'TG'
39 order by transaction_header_id desc;
40
41 l_deprn_run_date DATE;
42
43 -- Terminal Gain/Loss
44 l_thid NUMBER;
45 l_event_id NUMBER;
46 l_event_status VARCHAR2(1);
47 l_deprn_source_info XLA_EVENTS_PUB_PKG.t_event_source_info;
48 l_security_context XLA_EVENTS_PUB_PKG.t_security;
49 l_trans_rec FA_API_TYPES.trans_rec_type;
50 l_asset_type_rec FA_API_TYPES.asset_type_rec_type;
51 l_asset_fin_rec FA_API_TYPES.asset_fin_rec_type;
52 l_status boolean;
53 l_bks_rowid ROWID;
54 pers_per_yr NUMBER; -- Bug:5701095
55
56 l_calling_fn varchar2(40) := 'fa_deprn_rollback_pvt.do_rollback';
57
58 rb_error exception;
59
60 -- Bug:6665510:Japan Tax Reform Project
61 TYPE tab_rowid_type is table of rowid index by binary_integer;
62 TYPE tab_number_type is table of number index by binary_integer;
63 TYPE tab_varchar_type2 is table of varchar2(150) index by binary_integer;
64
65 l_bks_rowid_tbl2 tab_rowid_type;
66 l_asset_id_tbl2 tab_number_type;
67 l_method_code_tbl2 tab_varchar_type2;
68 l_life_in_months_tbl2 tab_number_type;
69 l_rate_in_use_tbl tab_number_type;
70
71 l_method_type number := 0;
72 l_success integer;
73 l_rate_in_use number;
74
75 -- used for bulk fetching
76 l_batch_size NUMBER;
77 l_rows_processed NUMBER;
78
79 -- Bug:6665510:Japan Tax Reform Project
80 cursor c_rate_in_use
81 Is
82 select bks.rowid,
83 bks.asset_id,
84 bks.deprn_method_code,
85 bks.life_in_months
86 from fa_books bks,
87 fa_methods mt
88 where bks.asset_id = p_asset_hdr_rec.asset_id
89 and bks.book_type_code = p_asset_hdr_rec.book_type_code
90 and bks.transaction_header_id_out is null
91 and bks.deprn_method_code = mt.method_code
92 and nvl(bks.life_in_months, -99) = nvl(mt.life_in_months, -99)
93 and nvl(mt.guarantee_rate_method_flag, 'NO') = 'YES';
94
95 -- Bug:6665510:Japan Tax Reform Project
96 cursor c_mc_rate_in_use
97 Is
98 select bks.rowid,
99 bks.asset_id,
100 bks.deprn_method_code,
101 bks.life_in_months
102 from fa_books_mrc_v bks,
103 fa_methods mt
104 where bks.asset_id = p_asset_hdr_rec.asset_id
105 and bks.book_type_code = p_asset_hdr_rec.book_type_code
106 and bks.transaction_header_id_out is null
107 and bks.deprn_method_code = mt.method_code
108 and nvl(bks.life_in_months, -99) = nvl(mt.life_in_months, -99)
109 and nvl(mt.guarantee_rate_method_flag, 'NO') = 'YES';
110 begin
111
112
113 -- SLA UPTAKE
114 -- backup the data delete or reverse the event
115
116 if (p_reversal_event_id is not null) then
117
118 if (p_mrc_sob_type_code = 'R') then
119
120 insert into fa_deprn_summary_h_mrc_v
121 (BOOK_TYPE_CODE ,
122 ASSET_ID ,
123 DEPRN_RUN_DATE ,
124 DEPRN_AMOUNT ,
125 YTD_DEPRN ,
126 DEPRN_RESERVE ,
127 DEPRN_SOURCE_CODE ,
128 ADJUSTED_COST ,
129 BONUS_RATE ,
130 LTD_PRODUCTION ,
131 PERIOD_COUNTER ,
132 PRODUCTION ,
133 REVAL_AMORTIZATION ,
134 REVAL_AMORTIZATION_BASIS ,
135 REVAL_DEPRN_EXPENSE ,
136 REVAL_RESERVE ,
137 YTD_PRODUCTION ,
138 YTD_REVAL_DEPRN_EXPENSE ,
139 PRIOR_FY_EXPENSE ,
140 BONUS_DEPRN_AMOUNT ,
141 BONUS_YTD_DEPRN ,
142 BONUS_DEPRN_RESERVE ,
143 PRIOR_FY_BONUS_EXPENSE ,
144 DEPRN_OVERRIDE_FLAG ,
145 SYSTEM_DEPRN_AMOUNT ,
146 SYSTEM_BONUS_DEPRN_AMOUNT ,
147 EVENT_ID ,
148 DEPRN_RUN_ID ,
149 REVERSAL_EVENT_ID ,
150 REVERSAL_DATE )
151 select BOOK_TYPE_CODE ,
152 ASSET_ID ,
153 DEPRN_RUN_DATE ,
154 DEPRN_AMOUNT ,
155 YTD_DEPRN ,
156 DEPRN_RESERVE ,
157 DEPRN_SOURCE_CODE ,
158 ADJUSTED_COST ,
159 BONUS_RATE ,
160 LTD_PRODUCTION ,
161 PERIOD_COUNTER ,
162 PRODUCTION ,
163 REVAL_AMORTIZATION ,
164 REVAL_AMORTIZATION_BASIS ,
165 REVAL_DEPRN_EXPENSE ,
166 REVAL_RESERVE ,
167 YTD_PRODUCTION ,
168 YTD_REVAL_DEPRN_EXPENSE ,
169 PRIOR_FY_EXPENSE ,
170 BONUS_DEPRN_AMOUNT ,
171 BONUS_YTD_DEPRN ,
172 BONUS_DEPRN_RESERVE ,
173 PRIOR_FY_BONUS_EXPENSE ,
174 DEPRN_OVERRIDE_FLAG ,
175 SYSTEM_DEPRN_AMOUNT ,
176 SYSTEM_BONUS_DEPRN_AMOUNT ,
177 EVENT_ID ,
178 DEPRN_RUN_ID ,
179 P_REVERSAL_EVENT_ID ,
180 P_REVERSAL_DATE
181 from fa_deprn_summary_mrc_v ds
182 where ds.book_type_code = p_asset_hdr_rec.book_type_code
183 and ds.asset_id = p_asset_hdr_rec.asset_id
184 and ds.period_counter = p_period_rec.period_counter
185 and ds.deprn_source_code in ('DEPRN','TRACK');
186
187 insert into fa_deprn_detail_h_mrc_v
188 (BOOK_TYPE_CODE ,
189 ASSET_ID ,
190 PERIOD_COUNTER ,
191 DISTRIBUTION_ID ,
192 DEPRN_SOURCE_CODE ,
193 DEPRN_RUN_DATE ,
194 DEPRN_AMOUNT ,
195 YTD_DEPRN ,
196 DEPRN_RESERVE ,
197 ADDITION_COST_TO_CLEAR ,
198 COST ,
199 DEPRN_ADJUSTMENT_AMOUNT ,
200 REVAL_AMORTIZATION ,
201 REVAL_DEPRN_EXPENSE ,
202 REVAL_RESERVE ,
203 YTD_REVAL_DEPRN_EXPENSE ,
204 BONUS_DEPRN_AMOUNT ,
205 BONUS_YTD_DEPRN ,
206 BONUS_DEPRN_RESERVE ,
207 BONUS_DEPRN_ADJUSTMENT_AMOUNT ,
208 EVENT_ID ,
209 DEPRN_RUN_ID ,
210 REVERSAL_EVENT_ID ,
211 REVERSAL_DATE )
212 select BOOK_TYPE_CODE ,
213 ASSET_ID ,
214 PERIOD_COUNTER ,
215 DISTRIBUTION_ID ,
216 DEPRN_SOURCE_CODE ,
217 DEPRN_RUN_DATE ,
218 DEPRN_AMOUNT ,
219 YTD_DEPRN ,
220 DEPRN_RESERVE ,
221 ADDITION_COST_TO_CLEAR ,
222 COST ,
223 DEPRN_ADJUSTMENT_AMOUNT ,
224 REVAL_AMORTIZATION ,
225 REVAL_DEPRN_EXPENSE ,
226 REVAL_RESERVE ,
227 YTD_REVAL_DEPRN_EXPENSE ,
228 BONUS_DEPRN_AMOUNT ,
229 BONUS_YTD_DEPRN ,
230 BONUS_DEPRN_RESERVE ,
231 BONUS_DEPRN_ADJUSTMENT_AMOUNT ,
232 EVENT_ID ,
233 DEPRN_RUN_ID ,
234 P_REVERSAL_EVENT_ID ,
235 P_REVERSAL_DATE
236 from fa_deprn_detail_mrc_v ds
237 where ds.book_type_code = p_asset_hdr_rec.book_type_code
238 and ds.asset_id = p_asset_hdr_rec.asset_id
239 and ds.period_counter = p_period_rec.period_counter
240 and ds.deprn_source_code in ('D','T');
241
242 else
243
244 -- archive the prior info into the backup table
245 insert into fa_deprn_summary_h
246 (BOOK_TYPE_CODE ,
247 ASSET_ID ,
248 DEPRN_RUN_DATE ,
249 DEPRN_AMOUNT ,
250 YTD_DEPRN ,
251 DEPRN_RESERVE ,
252 DEPRN_SOURCE_CODE ,
253 ADJUSTED_COST ,
254 BONUS_RATE ,
255 LTD_PRODUCTION ,
256 PERIOD_COUNTER ,
257 PRODUCTION ,
258 REVAL_AMORTIZATION ,
259 REVAL_AMORTIZATION_BASIS ,
260 REVAL_DEPRN_EXPENSE ,
261 REVAL_RESERVE ,
262 YTD_PRODUCTION ,
263 YTD_REVAL_DEPRN_EXPENSE ,
264 PRIOR_FY_EXPENSE ,
265 BONUS_DEPRN_AMOUNT ,
266 BONUS_YTD_DEPRN ,
267 BONUS_DEPRN_RESERVE ,
268 PRIOR_FY_BONUS_EXPENSE ,
269 DEPRN_OVERRIDE_FLAG ,
270 SYSTEM_DEPRN_AMOUNT ,
271 SYSTEM_BONUS_DEPRN_AMOUNT ,
272 EVENT_ID ,
273 DEPRN_RUN_ID ,
274 REVERSAL_EVENT_ID ,
275 REVERSAL_DATE )
276 select BOOK_TYPE_CODE ,
277 ASSET_ID ,
278 DEPRN_RUN_DATE ,
279 DEPRN_AMOUNT ,
280 YTD_DEPRN ,
281 DEPRN_RESERVE ,
282 DEPRN_SOURCE_CODE ,
283 ADJUSTED_COST ,
284 BONUS_RATE ,
285 LTD_PRODUCTION ,
286 PERIOD_COUNTER ,
287 PRODUCTION ,
288 REVAL_AMORTIZATION ,
289 REVAL_AMORTIZATION_BASIS ,
290 REVAL_DEPRN_EXPENSE ,
291 REVAL_RESERVE ,
292 YTD_PRODUCTION ,
293 YTD_REVAL_DEPRN_EXPENSE ,
294 PRIOR_FY_EXPENSE ,
295 BONUS_DEPRN_AMOUNT ,
296 BONUS_YTD_DEPRN ,
297 BONUS_DEPRN_RESERVE ,
298 PRIOR_FY_BONUS_EXPENSE ,
299 DEPRN_OVERRIDE_FLAG ,
300 SYSTEM_DEPRN_AMOUNT ,
301 SYSTEM_BONUS_DEPRN_AMOUNT ,
302 EVENT_ID ,
303 DEPRN_RUN_ID ,
304 P_REVERSAL_EVENT_ID ,
305 P_REVERSAL_DATE
306 from fa_deprn_summary ds
307 where ds.book_type_code = p_asset_hdr_rec.book_type_code
308 and ds.asset_id = p_asset_hdr_rec.asset_id
309 and ds.period_counter = p_period_rec.period_counter
310 and ds.deprn_source_code in ('DEPRN','TRACK');
311
312 insert into fa_deprn_detail_h
313 (BOOK_TYPE_CODE ,
314 ASSET_ID ,
315 PERIOD_COUNTER ,
316 DISTRIBUTION_ID ,
317 DEPRN_SOURCE_CODE ,
318 DEPRN_RUN_DATE ,
319 DEPRN_AMOUNT ,
320 YTD_DEPRN ,
321 DEPRN_RESERVE ,
322 ADDITION_COST_TO_CLEAR ,
323 COST ,
324 DEPRN_ADJUSTMENT_AMOUNT ,
325 REVAL_AMORTIZATION ,
326 REVAL_DEPRN_EXPENSE ,
327 REVAL_RESERVE ,
328 YTD_REVAL_DEPRN_EXPENSE ,
329 BONUS_DEPRN_AMOUNT ,
330 BONUS_YTD_DEPRN ,
331 BONUS_DEPRN_RESERVE ,
332 BONUS_DEPRN_ADJUSTMENT_AMOUNT ,
333 EVENT_ID ,
334 DEPRN_RUN_ID ,
335 REVERSAL_EVENT_ID ,
336 REVERSAL_DATE )
337 select BOOK_TYPE_CODE ,
338 ASSET_ID ,
339 PERIOD_COUNTER ,
340 DISTRIBUTION_ID ,
341 DEPRN_SOURCE_CODE ,
342 DEPRN_RUN_DATE ,
343 DEPRN_AMOUNT ,
344 YTD_DEPRN ,
345 DEPRN_RESERVE ,
346 ADDITION_COST_TO_CLEAR ,
347 COST ,
348 DEPRN_ADJUSTMENT_AMOUNT ,
349 REVAL_AMORTIZATION ,
350 REVAL_DEPRN_EXPENSE ,
351 REVAL_RESERVE ,
352 YTD_REVAL_DEPRN_EXPENSE ,
353 BONUS_DEPRN_AMOUNT ,
354 BONUS_YTD_DEPRN ,
355 BONUS_DEPRN_RESERVE ,
356 BONUS_DEPRN_ADJUSTMENT_AMOUNT ,
357 EVENT_ID ,
358 DEPRN_RUN_ID ,
359 P_REVERSAL_EVENT_ID ,
360 P_REVERSAL_DATE
361 from fa_deprn_detail ds
362 where ds.book_type_code = p_asset_hdr_rec.book_type_code
363 and ds.asset_id = p_asset_hdr_rec.asset_id
364 and ds.period_counter = p_period_rec.period_counter
365 and ds.deprn_source_code in ('D','T');
366
367 -- flag the header table too
368 update fa_deprn_events
369 set reversal_event_id = P_REVERSAL_EVENT_ID,
370 reversal_date = p_reversal_date
371 where asset_id = p_asset_hdr_rec.asset_id
372 and book_type_code = p_asset_hdr_rec.book_type_code
373 and period_counter = p_period_rec.period_counter
374 and deprn_run_id = p_deprn_run_id;
375
376 end if;
377 else -- event was not final -0 need to delete the dpern event
378 delete from fa_deprn_events
379 where asset_id = p_asset_hdr_rec.asset_id
380 and book_type_code = p_asset_hdr_rec.book_type_code
381 and period_counter = p_period_rec.period_counter
382 and reversal_event_id is null;
383 end if;
384
385 -- now continue with main processing
386
387
388 if not fa_cache_pkg.fazcct(X_calendar => fa_cache_pkg.fazcbc_record.deprn_calendar
389 ,p_log_level_rec => p_log_level_rec) then
390 raise rb_error;
391 end if;
392
393 -- Bug:5701095
394 pers_per_yr := fa_cache_pkg.fazcct_record.NUMBER_PER_FISCAL_YEAR;
395
396 -- Bug:6665510:Japan Tax Reform Project
397 l_batch_size := nvl(fa_cache_pkg.fa_batch_size, 200);
398
399 -- Bug# 3798413: Reset adjusted_cost back to the original adjusted_cost
400 -- for the addition transaction.
401 -- For period of addition transaction, we are getting the value
402 -- from fa_deprn_summary's BOOKS row
403 -- since Adj API also sets adjusted_cost in BOOKS' row to the latest value
404 -- before depreciation correctly.
405 --
406 -- BUG# 4094166
407 -- also update eofy_reserve and limit update to make
408 -- sure this is period of addition only
409
410 if (p_mrc_sob_type_code = 'R') then
411
412 -- Bug:5701095
413 update fa_books_mrc_v bk
414 set (adjusted_cost, eofy_reserve, formula_factor, adjusted_capacity) =
415 (Select Decode(ds2.deprn_source_code,
416 'BOOKS', ds2.adjusted_cost,
417 decode(p_period_rec.period_num - pers_per_yr,
418 0, decode(bk2.eofy_adj_cost,
419 null, decode(bk2.eop_adj_cost,
420 null, decode(ds1.asset_id,
421 null, bk2.adjusted_cost,
422 ds1.adjusted_cost),
423 bk2.eop_adj_cost),
424 bk2.eofy_adj_cost),
425 decode(bk2.eop_adj_cost,
426 null, decode(ds1.asset_id,
427 null, bk2.adjusted_cost,
428 ds1.adjusted_cost),
429 Decode(ds1.deprn_source_code,
430 'DEPRN', bk2.eop_adj_cost,
431 decode(ds1.asset_id,
432 null, bk2.adjusted_cost,
433 ds1.adjusted_cost)))
434 )),
435 Decode(ds2.deprn_source_code,
436 'BOOKS', nvl(bk2.prior_eofy_reserve, bk2.eofy_reserve),
437 decode(p_period_rec.period_num - pers_per_yr,
438 0, decode(bk2.prior_eofy_reserve,
439 null, bk2.eofy_reserve,
440 bk2.prior_eofy_reserve),
441 bk2.eofy_reserve)),
442 Decode(p_period_rec.period_num - pers_per_yr,
443 0, decode(bk2.eofy_adj_cost,
444 null, decode(bk2.eop_adj_cost,
445 null, bk2.formula_factor,
446 bk2.eop_formula_factor),
447 bk2.eofy_formula_factor),
448 decode(bk2.eop_adj_cost,
449 null, bk2.formula_factor,
450 bk2.eop_formula_factor)),
451 Decode(bk2.eop_adj_cost,
452 null, bk2.adjusted_capacity,
453 bk2.old_adjusted_capacity)
454 From fa_deprn_summary_mrc_v ds2, fa_deprn_summary_mrc_v ds1, fa_books_mrc_v bk2
455 where bk2.transaction_header_id_in = bk.transaction_header_id_in
456 and ds2.asset_id(+) = bk2.asset_id
457 and ds2.book_type_code(+) = bk2.book_type_code
458 and ds2.period_counter(+) = (p_period_rec.period_counter - 1)
459 and ds1.asset_id (+) = bk2.asset_id
460 and ds1.book_type_code (+) = bk2.book_type_code
461 and ds1.period_counter (+) = (p_period_rec.period_counter)),
462 eop_adj_cost = NULL,
463 eop_formula_factor = NULL,
464 eofy_adj_cost = Decode(p_period_rec.period_num - pers_per_yr,
465 0, NULL,
466 eofy_adj_cost),
467 eofy_formula_factor = Decode(p_period_rec.period_num - pers_per_yr,
468 0, NULL,
469 eofy_formula_factor),
470 prior_eofy_reserve = Decode(p_period_rec.period_num - pers_per_yr,
471 0, NULL,
472 prior_eofy_reserve),
473 period_counter_fully_reserved = Decode(period_counter_fully_reserved,
474 p_period_rec.period_counter,
475 Null,
476 period_counter_fully_reserved),
477 period_counter_life_complete = Decode(period_counter_life_complete,
478 p_period_rec.period_counter,
479 Null,
480 period_counter_life_complete)
481 where asset_id = p_asset_hdr_rec.asset_id -- Bug:6778581
482 and book_type_code = p_asset_hdr_rec.book_type_code
483 and transaction_header_id_out is null;
484
485 else -- if (p_mrc_sob_type_code = 'R') then
486
487 -- Bug:5701095
488 update fa_books bk
489 set (adjusted_cost, eofy_reserve, formula_factor, adjusted_capacity) =
490 (Select Decode(ds2.deprn_source_code,
491 'BOOKS', ds2.adjusted_cost,
492 decode(p_period_rec.period_num - pers_per_yr,
493 0, decode(bk2.eofy_adj_cost,
494 null, decode(bk2.eop_adj_cost,
495 null, decode(ds1.asset_id,
496 null, bk2.adjusted_cost,
497 ds1.adjusted_cost),
498 bk2.eop_adj_cost),
499 bk2.eofy_adj_cost),
500 decode(bk2.eop_adj_cost,
501 null, decode(ds1.asset_id,
502 null, bk2.adjusted_cost,
503 ds1.adjusted_cost),
504 decode(ds1.deprn_source_code,
505 'DEPRN', bk2.eop_adj_cost,
506 decode(ds1.asset_id,
507 null, bk2.adjusted_cost,
508 ds1.adjusted_cost)))
509 )),
510 Decode(ds2.deprn_source_code,
511 'BOOKS', nvl(bk2.prior_eofy_reserve, bk2.eofy_reserve),
512 decode(p_period_rec.period_num - pers_per_yr,
513 0, decode(bk2.prior_eofy_reserve,
514 null, bk2.eofy_reserve,
515 bk2.prior_eofy_reserve),
516 bk2.eofy_reserve)),
517 Decode(p_period_rec.period_num - pers_per_yr,
518 0, decode(bk2.eofy_adj_cost,
519 null, decode(bk2.eop_adj_cost,
520 null, bk2.formula_factor,
521 bk2.eop_formula_factor),
522 bk2.eofy_formula_factor),
523 decode(bk2.eop_adj_cost,
524 null, bk2.formula_factor,
525 bk2.eop_formula_factor)),
526 Decode(bk2.eop_adj_cost,
527 null, bk2.adjusted_capacity,
528 bk2.old_adjusted_capacity)
529 From fa_deprn_summary ds2, fa_deprn_summary ds1, fa_books bk2
530 where bk2.transaction_header_id_in = bk.transaction_header_id_in
531 and ds2.asset_id(+) = bk2.asset_id
532 and ds2.book_type_code(+) = bk2.book_type_code
533 and ds2.period_counter(+) = (p_period_rec.period_counter - 1)
534 and ds1.asset_id (+) = bk2.asset_id
535 and ds1.book_type_code (+) = bk2.book_type_code
536 and ds1.period_counter (+) = (p_period_rec.period_counter)),
537 eop_adj_cost = NULL,
538 eop_formula_factor = NULL,
539 eofy_adj_cost = Decode(p_period_rec.period_num - pers_per_yr,
540 0, NULL,
541 eofy_adj_cost),
542 eofy_formula_factor = Decode(p_period_rec.period_num - pers_per_yr,
543 0, NULL,
544 eofy_formula_factor),
545 prior_eofy_reserve = Decode(p_period_rec.period_num - pers_per_yr,
546 0, NULL,
547 prior_eofy_reserve),
548 period_counter_fully_reserved = Decode(period_counter_fully_reserved,
549 p_period_rec.period_counter,
550 Null,
551 period_counter_fully_reserved),
552 period_counter_life_complete = Decode(period_counter_life_complete,
553 p_period_rec.period_counter,
554 Null,
555 period_counter_life_complete)
556 where asset_id = p_asset_hdr_rec.asset_id -- Bug:6778581
557 and book_type_code = p_asset_hdr_rec.book_type_code
558 and transaction_header_id_out is null;
559 end if; -- if (p_mrc_sob_type_code = 'R') then
560
561 -- delete from DS
562
563 if (p_mrc_sob_type_code = 'R') then
564 delete
565 from fa_deprn_summary_mrc_v ds
566 where ds.asset_id = p_asset_hdr_rec.asset_id
567 and ds.book_type_code = p_asset_hdr_rec.book_type_code
568 and ds.period_counter = p_period_rec.period_counter
569 and ds.deprn_source_code in ('DEPRN','TRACK');
570 else
571 delete
572 from fa_deprn_summary ds
573 where ds.asset_id = p_asset_hdr_rec.asset_id
574 and ds.book_type_code = p_asset_hdr_rec.book_type_code
575 and ds.period_counter = p_period_rec.period_counter
576 and ds.deprn_source_code in ('DEPRN','TRACK');
577 end if;
578
579
580 -- delete from DD
581
582 if (p_mrc_sob_type_code = 'R') then
583 delete
584 from fa_deprn_detail_mrc_v dd
585 where dd.asset_id = p_asset_hdr_rec.asset_id
586 and dd.book_type_code = p_asset_hdr_rec.book_type_code
587 and dd.period_counter = p_period_rec.period_counter
588 and dd.deprn_source_code in ('D','T');
589 else
590 delete
591 from fa_deprn_detail dd
592 where dd.asset_id = p_asset_hdr_rec.asset_id
593 and dd.book_type_code = p_asset_hdr_rec.book_type_code
594 and dd.period_counter = p_period_rec.period_counter
595 and dd.deprn_source_code in ('D','T');
596 end if;
597
598 -- Delete from FA_BOOKS_SUMMARY
599
600 if (p_log_level_rec.statement_level) then
601 fa_debug_pkg.add(l_calling_fn,
602 'Deleting FA_BOOKS_SUMMARY rows, next period ctr',
603 p_period_rec.period_counter + 1
604 ,p_log_level_rec => p_log_level_rec);
605 end if;
606
607 if (p_mrc_sob_type_code = 'R') then
608
609 delete from fa_books_summary_mrc_v bs
610 where bs.asset_id = p_asset_hdr_rec.asset_id
611 and bs.book_type_code = p_asset_hdr_rec.book_type_code
612 and bs.period_counter = p_period_rec.period_counter + 1;
613
614 update fa_books_summary_mrc_v bs
615 set deprn_amount = expense_adjustment_amount,
616 ytd_deprn = ytd_deprn - deprn_amount + expense_adjustment_amount,
617 deprn_reserve = deprn_reserve - deprn_amount + expense_adjustment_amount,
618 bonus_deprn_amount = 0,
619 bonus_ytd_deprn = bonus_ytd_deprn - bonus_deprn_amount,
620 bonus_deprn_reserve = bonus_deprn_reserve - bonus_deprn_amount,
621 bonus_rate = 0,
622 adjusted_capacity = adjusted_capacity + production,
623 ltd_production = ltd_production - production,
624 ytd_production = ytd_production - production,
625 production = 0,
626 reval_deprn_expense = 0,
627 reval_reserve = reval_reserve - reval_deprn_expense,
628 ytd_reval_deprn_expense = ytd_reval_deprn_expense - reval_deprn_expense
629 where bs.asset_id = p_asset_hdr_rec.asset_id
630 and bs.book_type_code = p_asset_hdr_rec.book_type_code
631 and bs.period_counter = p_period_rec.period_counter;
632
633 else
634
635 delete from fa_books_summary bs
636 where bs.asset_id = p_asset_hdr_rec.asset_id
637 and bs.book_type_code = p_asset_hdr_rec.book_type_code
638 and bs.period_counter = p_period_rec.period_counter + 1;
639
640 update fa_books_summary bs
641 set deprn_amount = expense_adjustment_amount,
642 ytd_deprn = ytd_deprn - deprn_amount + expense_adjustment_amount,
643 deprn_reserve = deprn_reserve - deprn_amount + expense_adjustment_amount,
644 bonus_deprn_amount = 0,
645 bonus_ytd_deprn = bonus_ytd_deprn - bonus_deprn_amount,
646 bonus_deprn_reserve = bonus_deprn_reserve - bonus_deprn_amount,
647 bonus_rate = 0,
648 adjusted_capacity = adjusted_capacity + production,
649 ltd_production = ltd_production - production,
650 ytd_production = ytd_production - production,
651 production = 0,
652 reval_deprn_expense = 0,
653 reval_reserve = reval_reserve - reval_deprn_expense,
654 ytd_reval_deprn_expense = ytd_reval_deprn_expense - reval_deprn_expense
655 where bs.asset_id = p_asset_hdr_rec.asset_id
656 and bs.book_type_code = p_asset_hdr_rec.book_type_code
657 and bs.period_counter = p_period_rec.period_counter;
658
659 end if;
660
661 -- rollback/reverse terminal gain loss
662 -- pending new api / logic
663
664 if (p_log_level_rec.statement_level) then
665 fa_debug_pkg.add(l_calling_fn, 'BEGIN', 'Processing terminal gain loss'
666 ,p_log_level_rec => p_log_level_rec);
667 end if;
668
669 open c_get_thid;
670 fetch c_get_thid into l_thid, l_event_id;
671 close c_get_thid;
672
673 if (p_log_level_rec.statement_level) then
674 fa_debug_pkg.add(l_calling_fn, 'THID of TGL', l_thid
675 ,p_log_level_rec => p_log_level_rec);
676 end if;
677
678 if (l_thid is not null) then
679
680 if (p_log_level_rec.statement_level) then
681 fa_debug_pkg.add(l_calling_fn, 'event id', l_event_id
682 ,p_log_level_rec => p_log_level_rec);
683 fa_debug_pkg.add(l_calling_fn, 'sob type code', p_mrc_sob_type_code
684 ,p_log_level_rec => p_log_level_rec);
685 end if;
686
687 if (l_event_id is not null) then
688 if (p_log_level_rec.statement_level) then
689 fa_debug_pkg.add(l_calling_fn, 'calling get event status for event ', l_event_id
690 ,p_log_level_rec => p_log_level_rec);
691 end if;
692
693 l_deprn_source_info.application_id := 140;
694 l_deprn_source_info.ledger_id := p_asset_hdr_rec.set_of_books_id;
695
696 --
697 -- Old thid or new thid?
698 --
699 l_deprn_source_info.source_id_int_1 := l_thid;
700 l_deprn_source_info.entity_type_code := 'TRANSACTIONS';
701
702 -- check the event status
703 l_event_status := XLA_EVENTS_PUB_PKG.get_event_status
704 (p_event_source_info => l_deprn_source_info,
705 p_event_id => l_event_id,
706 p_valuation_method => p_asset_hdr_rec.book_type_code,
707 p_security_context => l_security_context);
708
709 if (p_log_level_rec.statement_level) then
710 fa_debug_pkg.add(l_calling_fn, 'event status ', l_event_status
711 ,p_log_level_rec => p_log_level_rec);
712 end if;
713
714 end if;
715
716 if (l_event_status = XLA_EVENTS_PUB_PKG.C_EVENT_PROCESSED) then
717 -- Reverse Terminal Gain Loss
718
719 if (p_mrc_sob_type_code <> 'R') then
720 SELECT fa_transaction_headers_s.nextval
721 INTO l_trans_rec.transaction_header_id
722 FROM DUAL;
723
724 --
725 -- Populating for calling FA_XLA_EVENTS_PVT.create_transaction_event
726 --
727 l_trans_rec.transaction_subtype := 'AMORTIZED';
728 l_trans_rec.transaction_type_code := 'ADJUSTMENT';
729 l_trans_rec.transaction_key := 'TG';
730 l_trans_rec.transaction_date_entered := greatest(p_period_rec.calendar_period_open_date,
731 least(sysdate,p_period_rec.calendar_period_close_date));
732 l_trans_rec.amortization_start_date := l_trans_rec.transaction_date_entered;
733 l_trans_rec.calling_interface := 'FAXDRB';
734
735 l_asset_type_rec.asset_type := 'GROUP';
736
737 if not FA_XLA_EVENTS_PVT.create_transaction_event(
738 p_asset_hdr_rec => p_asset_hdr_rec,
739 p_asset_type_rec => l_asset_type_rec,
740 px_trans_rec => l_trans_rec,
741 p_event_status => NULL,
742 p_calling_fn => l_calling_fn
743 ,p_log_level_rec => p_log_level_rec) then
744 fa_debug_pkg.add(l_calling_fn, 'ERROR', 'Calling create_transaction_event'
745 ,p_log_level_rec => p_log_level_rec);
746 raise rb_error;
747 end if;
748
749 INSERT INTO FA_TRANSACTION_HEADERS(
750 TRANSACTION_HEADER_ID
751 , BOOK_TYPE_CODE
752 , ASSET_ID
753 , TRANSACTION_TYPE_CODE
754 , TRANSACTION_DATE_ENTERED
755 , DATE_EFFECTIVE
756 , LAST_UPDATE_DATE
757 , LAST_UPDATED_BY
758 , TRANSACTION_NAME
759 , INVOICE_TRANSACTION_ID
760 , SOURCE_TRANSACTION_HEADER_ID
761 , MASS_REFERENCE_ID
762 , LAST_UPDATE_LOGIN
763 , TRANSACTION_SUBTYPE
764 , ATTRIBUTE1
765 , ATTRIBUTE2
766 , ATTRIBUTE3
767 , ATTRIBUTE4
768 , ATTRIBUTE5
769 , ATTRIBUTE6
770 , ATTRIBUTE7
771 , ATTRIBUTE8
772 , ATTRIBUTE9
773 , ATTRIBUTE10
774 , ATTRIBUTE11
775 , ATTRIBUTE12
776 , ATTRIBUTE13
777 , ATTRIBUTE14
778 , ATTRIBUTE15
779 , ATTRIBUTE_CATEGORY_CODE
780 , TRANSACTION_KEY
781 , AMORTIZATION_START_DATE
782 , CALLING_INTERFACE
783 , MASS_TRANSACTION_ID
784 , MEMBER_TRANSACTION_HEADER_ID
785 , TRX_REFERENCE_ID
786 , EVENT_ID
787 ) select
788 l_trans_rec.transaction_header_id --TRANSACTION_HEADER_ID
789 , BOOK_TYPE_CODE
790 , ASSET_ID
791 , TRANSACTION_TYPE_CODE
792 , TRANSACTION_DATE_ENTERED
793 , p_reversal_date -- DATE_EFFECTIVE
794 , p_reversal_date -- LAST_UPDATE_DATE
795 , l_trans_rec.who_info.last_updated_by
796 , TRANSACTION_NAME
797 , INVOICE_TRANSACTION_ID
798 , SOURCE_TRANSACTION_HEADER_ID
799 , MASS_REFERENCE_ID
800 , LAST_UPDATE_LOGIN
801 , TRANSACTION_SUBTYPE
802 , ATTRIBUTE1
803 , ATTRIBUTE2
804 , ATTRIBUTE3
805 , ATTRIBUTE4
806 , ATTRIBUTE5
807 , ATTRIBUTE6
808 , ATTRIBUTE7
809 , ATTRIBUTE8
810 , ATTRIBUTE9
811 , ATTRIBUTE10
812 , ATTRIBUTE11
813 , ATTRIBUTE12
814 , ATTRIBUTE13
815 , ATTRIBUTE14
816 , ATTRIBUTE15
817 , ATTRIBUTE_CATEGORY_CODE
818 , TRANSACTION_KEY
819 , AMORTIZATION_START_DATE
820 , 'FAXDRB' -- CALLING_INTERFACE
821 , MASS_TRANSACTION_ID
822 , MEMBER_TRANSACTION_HEADER_ID
823 , TRX_REFERENCE_ID
824 , l_trans_rec.event_id -- EVENT_ID
825 from fa_transaction_headers
826 where asset_id = p_asset_hdr_rec.asset_id
827 and book_type_code = p_asset_hdr_rec.book_type_code
828 and transaction_header_id = l_thid
829 ;
830
831 INSERT INTO FA_ADJUSTMENTS(
832 TRANSACTION_HEADER_ID
833 , SOURCE_TYPE_CODE
834 , ADJUSTMENT_TYPE
835 , DEBIT_CREDIT_FLAG
836 , CODE_COMBINATION_ID
837 , BOOK_TYPE_CODE
838 , ASSET_ID
839 , ADJUSTMENT_AMOUNT
840 , DISTRIBUTION_ID
841 , LAST_UPDATE_DATE
842 , LAST_UPDATED_BY
843 , LAST_UPDATE_LOGIN
844 , ANNUALIZED_ADJUSTMENT
845 , PERIOD_COUNTER_ADJUSTED
846 , PERIOD_COUNTER_CREATED
847 , ASSET_INVOICE_ID
848 , GLOBAL_ATTRIBUTE1
849 , GLOBAL_ATTRIBUTE2
850 , GLOBAL_ATTRIBUTE3
851 , GLOBAL_ATTRIBUTE4
852 , GLOBAL_ATTRIBUTE5
853 , GLOBAL_ATTRIBUTE6
854 , GLOBAL_ATTRIBUTE7
855 , GLOBAL_ATTRIBUTE8
856 , GLOBAL_ATTRIBUTE9
857 , GLOBAL_ATTRIBUTE10
858 , GLOBAL_ATTRIBUTE11
859 , GLOBAL_ATTRIBUTE12
860 , GLOBAL_ATTRIBUTE13
861 , GLOBAL_ATTRIBUTE14
862 , GLOBAL_ATTRIBUTE15
863 , GLOBAL_ATTRIBUTE16
864 , GLOBAL_ATTRIBUTE17
865 , GLOBAL_ATTRIBUTE18
866 , GLOBAL_ATTRIBUTE19
867 , GLOBAL_ATTRIBUTE20
868 , GLOBAL_ATTRIBUTE_CATEGORY
869 , DEPRN_OVERRIDE_FLAG
870 , TRACK_MEMBER_FLAG
871 , ADJUSTMENT_LINE_ID
872 , SOURCE_LINE_ID
873 , SOURCE_DEST_CODE
874 ) select
875 l_trans_rec.transaction_header_id -- TRANSACTION_HEADER_ID
876 , SOURCE_TYPE_CODE
877 , ADJUSTMENT_TYPE
878 , decode(debit_credit_flag, 'DR', 'CR', 'DR') --DEBIT_CREDIT_FLAG
879 , CODE_COMBINATION_ID
880 , BOOK_TYPE_CODE
881 , ASSET_ID
882 , ADJUSTMENT_AMOUNT
883 , DISTRIBUTION_ID
884 , p_reversal_date --LAST_UPDATE_DATE
885 , l_trans_rec.who_info.last_updated_by --LAST_UPDATED_BY
886 , l_trans_rec.who_info.last_update_login --LAST_UPDATE_LOGIN
887 , ANNUALIZED_ADJUSTMENT
888 , PERIOD_COUNTER_ADJUSTED
889 , PERIOD_COUNTER_CREATED
890 , ASSET_INVOICE_ID
891 , GLOBAL_ATTRIBUTE1
892 , GLOBAL_ATTRIBUTE2
893 , GLOBAL_ATTRIBUTE3
894 , GLOBAL_ATTRIBUTE4
895 , GLOBAL_ATTRIBUTE5
896 , GLOBAL_ATTRIBUTE6
897 , GLOBAL_ATTRIBUTE7
898 , GLOBAL_ATTRIBUTE8
899 , GLOBAL_ATTRIBUTE9
900 , GLOBAL_ATTRIBUTE10
901 , GLOBAL_ATTRIBUTE11
902 , GLOBAL_ATTRIBUTE12
903 , GLOBAL_ATTRIBUTE13
904 , GLOBAL_ATTRIBUTE14
905 , GLOBAL_ATTRIBUTE15
906 , GLOBAL_ATTRIBUTE16
907 , GLOBAL_ATTRIBUTE17
908 , GLOBAL_ATTRIBUTE18
909 , GLOBAL_ATTRIBUTE19
910 , GLOBAL_ATTRIBUTE20
911 , GLOBAL_ATTRIBUTE_CATEGORY
912 , DEPRN_OVERRIDE_FLAG
913 , TRACK_MEMBER_FLAG
914 , fa_adjustments_s.nextval -- ADJUSTMENT_LINE_ID
915 , SOURCE_LINE_ID
916 , SOURCE_DEST_CODE
917 from fa_adjustments
918 where asset_id = p_asset_hdr_rec.asset_id
919 and book_type_code = p_asset_hdr_rec.book_type_code
920 and transaction_header_id = l_thid
921 ;
922
923 else -- Reporting book
924 open c_get_new_thid;
925 fetch c_get_new_thid into l_trans_rec.transaction_header_id
926 , l_trans_rec.who_info.last_update_date;
927 close c_get_new_thid;
928
929 INSERT INTO FA_ADJUSTMENTS_MRC_V(
930 TRANSACTION_HEADER_ID
931 , SOURCE_TYPE_CODE
932 , ADJUSTMENT_TYPE
933 , DEBIT_CREDIT_FLAG
934 , CODE_COMBINATION_ID
935 , BOOK_TYPE_CODE
936 , ASSET_ID
937 , ADJUSTMENT_AMOUNT
938 , DISTRIBUTION_ID
939 , LAST_UPDATE_DATE
940 , LAST_UPDATED_BY
941 , LAST_UPDATE_LOGIN
942 , ANNUALIZED_ADJUSTMENT
943 , PERIOD_COUNTER_ADJUSTED
944 , PERIOD_COUNTER_CREATED
945 , ASSET_INVOICE_ID
946 , GLOBAL_ATTRIBUTE1
947 , GLOBAL_ATTRIBUTE2
948 , GLOBAL_ATTRIBUTE3
949 , GLOBAL_ATTRIBUTE4
950 , GLOBAL_ATTRIBUTE5
951 , GLOBAL_ATTRIBUTE6
952 , GLOBAL_ATTRIBUTE7
953 , GLOBAL_ATTRIBUTE8
954 , GLOBAL_ATTRIBUTE9
955 , GLOBAL_ATTRIBUTE10
956 , GLOBAL_ATTRIBUTE11
957 , GLOBAL_ATTRIBUTE12
958 , GLOBAL_ATTRIBUTE13
959 , GLOBAL_ATTRIBUTE14
960 , GLOBAL_ATTRIBUTE15
961 , GLOBAL_ATTRIBUTE16
962 , GLOBAL_ATTRIBUTE17
963 , GLOBAL_ATTRIBUTE18
964 , GLOBAL_ATTRIBUTE19
965 , GLOBAL_ATTRIBUTE20
966 , GLOBAL_ATTRIBUTE_CATEGORY
967 , DEPRN_OVERRIDE_FLAG
968 , TRACK_MEMBER_FLAG
969 , ADJUSTMENT_LINE_ID
970 , SOURCE_LINE_ID
971 , SOURCE_DEST_CODE
972 ) select
973 l_trans_rec.transaction_header_id -- TRANSACTION_HEADER_ID
974 , SOURCE_TYPE_CODE
975 , ADJUSTMENT_TYPE
976 , decode(debit_credit_flag, 'DR', 'CR', 'DR') --DEBIT_CREDIT_FLAG
977 , CODE_COMBINATION_ID
978 , BOOK_TYPE_CODE
979 , ASSET_ID
980 , ADJUSTMENT_AMOUNT
981 , DISTRIBUTION_ID
982 , l_trans_rec.who_info.last_update_date --LAST_UPDATE_DATE
983 , l_trans_rec.who_info.last_updated_by --LAST_UPDATED_BY
984 , l_trans_rec.who_info.last_update_login --LAST_UPDATE_LOGIN
985 , ANNUALIZED_ADJUSTMENT
986 , PERIOD_COUNTER_ADJUSTED
987 , PERIOD_COUNTER_CREATED
988 , ASSET_INVOICE_ID
989 , GLOBAL_ATTRIBUTE1
990 , GLOBAL_ATTRIBUTE2
991 , GLOBAL_ATTRIBUTE3
992 , GLOBAL_ATTRIBUTE4
993 , GLOBAL_ATTRIBUTE5
994 , GLOBAL_ATTRIBUTE6
995 , GLOBAL_ATTRIBUTE7
996 , GLOBAL_ATTRIBUTE8
997 , GLOBAL_ATTRIBUTE9
998 , GLOBAL_ATTRIBUTE10
999 , GLOBAL_ATTRIBUTE11
1000 , GLOBAL_ATTRIBUTE12
1001 , GLOBAL_ATTRIBUTE13
1002 , GLOBAL_ATTRIBUTE14
1003 , GLOBAL_ATTRIBUTE15
1004 , GLOBAL_ATTRIBUTE16
1005 , GLOBAL_ATTRIBUTE17
1006 , GLOBAL_ATTRIBUTE18
1007 , GLOBAL_ATTRIBUTE19
1008 , GLOBAL_ATTRIBUTE20
1009 , GLOBAL_ATTRIBUTE_CATEGORY
1010 , DEPRN_OVERRIDE_FLAG
1011 , TRACK_MEMBER_FLAG
1012 , fa_adjustments_s.nextval -- ADJUSTMENT_LINE_ID
1013 , SOURCE_LINE_ID
1014 , SOURCE_DEST_CODE
1015 from fa_adjustments_mrc_v
1016 where asset_id = p_asset_hdr_rec.asset_id
1017 and book_type_code = p_asset_hdr_rec.book_type_code
1018 and transaction_header_id = l_thid
1019 ;
1020
1021 end if; -- (p_mrc_sob_type_code <> 'R')
1022
1023 fa_books_pkg.deactivate_row
1024 (X_asset_id => p_asset_hdr_rec.asset_id,
1025 X_book_type_code => p_asset_hdr_rec.book_type_code,
1026 X_transaction_header_id_out => l_trans_rec.transaction_header_id,
1027 X_date_ineffective => l_trans_rec.who_info.last_update_date,
1028 X_mrc_sob_type_code => p_mrc_sob_type_code,
1029 X_Calling_Fn => l_calling_fn
1030 ,p_log_level_rec => p_log_level_rec);
1031
1032 fa_books_pkg.insert_row
1033 (X_Rowid => l_bks_rowid,
1034 X_Book_Type_Code => p_asset_hdr_rec.book_type_code,
1035 X_Asset_Id => p_asset_hdr_rec.asset_id,
1036 X_Date_Placed_In_Service => l_asset_fin_rec.date_placed_in_service,
1037 X_Date_Effective => l_trans_rec.who_info.last_update_date,
1038 X_Deprn_Start_Date => l_asset_fin_rec.deprn_start_date,
1039 X_Deprn_Method_Code => l_asset_fin_rec.deprn_method_code,
1040 X_Life_In_Months => l_asset_fin_rec.life_in_months,
1041 X_Rate_Adjustment_Factor => l_asset_fin_rec.rate_adjustment_factor,
1042 X_Adjusted_Cost => l_asset_fin_rec.adjusted_cost,
1043 X_Cost => l_asset_fin_rec.cost,
1044 X_Original_Cost => l_asset_fin_rec.original_cost,
1045 X_Salvage_Value => l_asset_fin_rec.salvage_value,
1046 X_Prorate_Convention_Code => l_asset_fin_rec.prorate_convention_code,
1047 X_Prorate_Date => l_asset_fin_rec.prorate_date,
1048 X_Cost_Change_Flag => l_asset_fin_rec.cost_change_flag,
1049 X_Adjustment_Required_Status => l_asset_fin_rec.adjustment_required_status,
1050 X_Capitalize_Flag => l_asset_fin_rec.capitalize_flag,
1051 X_Retirement_Pending_Flag => l_asset_fin_rec.retirement_pending_flag,
1052 X_Depreciate_Flag => l_asset_fin_rec.depreciate_flag,
1053 X_Disabled_Flag => l_asset_fin_rec.disabled_flag, --HH
1054 X_Last_Update_Date => l_trans_rec.who_info.last_update_date,
1055 X_Last_Updated_By => l_trans_rec.who_info.last_updated_by,
1056 X_Date_Ineffective => NULL,
1057 X_Transaction_Header_Id_In => l_trans_rec.transaction_header_id,
1058 X_Transaction_Header_Id_Out => NULL,
1059 X_Itc_Amount_Id => l_asset_fin_rec.itc_amount_id,
1060 X_Itc_Amount => l_asset_fin_rec.itc_amount,
1061 X_Retirement_Id => l_asset_fin_rec.retirement_id,
1062 X_Tax_Request_Id => l_asset_fin_rec.tax_request_id,
1063 X_Itc_Basis => l_asset_fin_rec.itc_basis,
1064 X_Basic_Rate => l_asset_fin_rec.basic_rate,
1065 X_Adjusted_Rate => l_asset_fin_rec.adjusted_rate,
1066 X_Bonus_Rule => l_asset_fin_rec.bonus_rule,
1067 X_Ceiling_Name => l_asset_fin_rec.ceiling_name,
1068 X_Recoverable_Cost => l_asset_fin_rec.recoverable_cost,
1069 X_Last_Update_Login => l_trans_rec.who_info.last_update_login,
1070 X_Adjusted_Capacity => l_asset_fin_rec.adjusted_capacity,
1071 X_Fully_Rsvd_Revals_Counter => l_asset_fin_rec.fully_rsvd_revals_counter,
1072 X_Idled_Flag => l_asset_fin_rec.idled_flag,
1073 X_Period_Counter_Capitalized => l_asset_fin_rec.period_counter_capitalized,
1074 X_PC_Fully_Reserved => l_asset_fin_rec.period_counter_fully_reserved,
1075 X_Period_Counter_Fully_Retired => l_asset_fin_rec.period_counter_fully_retired,
1076 X_Production_Capacity => l_asset_fin_rec.production_capacity,
1077 X_Reval_Amortization_Basis => l_asset_fin_rec.reval_amortization_basis,
1078 X_Reval_Ceiling => l_asset_fin_rec.reval_ceiling,
1079 X_Unit_Of_Measure => l_asset_fin_rec.unit_of_measure,
1080 X_Unrevalued_Cost => l_asset_fin_rec.unrevalued_cost,
1081 X_Annual_Deprn_Rounding_Flag => l_asset_fin_rec.annual_deprn_rounding_flag,
1082 X_Percent_Salvage_Value => l_asset_fin_rec.percent_salvage_value,
1083 X_Allowed_Deprn_Limit => l_asset_fin_rec.allowed_deprn_limit,
1084 X_Allowed_Deprn_Limit_Amount => l_asset_fin_rec.allowed_deprn_limit_amount,
1085 X_Period_Counter_Life_Complete => l_asset_fin_rec.period_counter_life_complete,
1086 X_Adjusted_Recoverable_Cost => l_asset_fin_rec.adjusted_recoverable_cost,
1087 X_Short_Fiscal_Year_Flag => l_asset_fin_rec.short_fiscal_year_flag,
1088 X_Conversion_Date => l_asset_fin_rec.conversion_date,
1089 X_Orig_Deprn_Start_Date => l_asset_fin_rec.orig_deprn_start_date,
1090 X_Remaining_Life1 => l_asset_fin_rec.remaining_life1,
1091 X_Remaining_Life2 => l_asset_fin_rec.remaining_life2,
1092 X_Old_Adj_Cost => l_asset_fin_rec.old_adjusted_cost,
1093 X_Formula_Factor => l_asset_fin_rec.formula_factor,
1094 X_gf_Attribute1 => l_asset_fin_rec.global_attribute1,
1095 X_gf_Attribute2 => l_asset_fin_rec.global_attribute2,
1096 X_gf_Attribute3 => l_asset_fin_rec.global_attribute3,
1097 X_gf_Attribute4 => l_asset_fin_rec.global_attribute4,
1098 X_gf_Attribute5 => l_asset_fin_rec.global_attribute5,
1099 X_gf_Attribute6 => l_asset_fin_rec.global_attribute6,
1100 X_gf_Attribute7 => l_asset_fin_rec.global_attribute7,
1101 X_gf_Attribute8 => l_asset_fin_rec.global_attribute8,
1102 X_gf_Attribute9 => l_asset_fin_rec.global_attribute9,
1103 X_gf_Attribute10 => l_asset_fin_rec.global_attribute10,
1104 X_gf_Attribute11 => l_asset_fin_rec.global_attribute11,
1105 X_gf_Attribute12 => l_asset_fin_rec.global_attribute12,
1106 X_gf_Attribute13 => l_asset_fin_rec.global_attribute13,
1107 X_gf_Attribute14 => l_asset_fin_rec.global_attribute14,
1108 X_gf_Attribute15 => l_asset_fin_rec.global_attribute15,
1109 X_gf_Attribute16 => l_asset_fin_rec.global_attribute16,
1110 X_gf_Attribute17 => l_asset_fin_rec.global_attribute17,
1111 X_gf_Attribute18 => l_asset_fin_rec.global_attribute18,
1112 X_gf_Attribute19 => l_asset_fin_rec.global_attribute19,
1113 X_gf_Attribute20 => l_asset_fin_rec.global_attribute20,
1114 X_global_attribute_category => l_asset_fin_rec.global_attribute_category,
1115 X_group_asset_id => l_asset_fin_rec.group_asset_id,
1116 X_salvage_type => l_asset_fin_rec.salvage_type,
1117 X_deprn_limit_type => l_asset_fin_rec.deprn_limit_type,
1118 X_over_depreciate_option => l_asset_fin_rec.over_depreciate_option,
1119 X_super_group_id => l_asset_fin_rec.super_group_id,
1120 X_reduction_rate => l_asset_fin_rec.reduction_rate,
1121 X_reduce_addition_flag => l_asset_fin_rec.reduce_addition_flag,
1122 X_reduce_adjustment_flag => l_asset_fin_rec.reduce_adjustment_flag,
1123 X_reduce_retirement_flag => l_asset_fin_rec.reduce_retirement_flag,
1124 X_recognize_gain_loss => l_asset_fin_rec.recognize_gain_loss,
1125 X_recapture_reserve_flag => l_asset_fin_rec.recapture_reserve_flag,
1126 X_limit_proceeds_flag => l_asset_fin_rec.limit_proceeds_flag,
1127 X_terminal_gain_loss => l_asset_fin_rec.terminal_gain_loss,
1128 X_exclude_proceeds_from_basis => l_asset_fin_rec.exclude_proceeds_from_basis,
1129 X_retirement_deprn_option => l_asset_fin_rec.retirement_deprn_option,
1130 X_tracking_method => l_asset_fin_rec.tracking_method,
1131 X_allocate_to_fully_rsv_flag => l_asset_fin_rec.allocate_to_fully_rsv_flag,
1132 X_allocate_to_fully_ret_flag => l_asset_fin_rec.allocate_to_fully_ret_flag,
1133 X_exclude_fully_rsv_flag => l_asset_fin_rec.exclude_fully_rsv_flag,
1134 X_excess_allocation_option => l_asset_fin_rec.excess_allocation_option,
1135 X_depreciation_option => l_asset_fin_rec.depreciation_option,
1136 X_member_rollup_flag => l_asset_fin_rec.member_rollup_flag,
1137 X_ytd_proceeds => l_asset_fin_rec.ytd_proceeds,
1138 X_ltd_proceeds => l_asset_fin_rec.ltd_proceeds,
1139 X_eofy_reserve => l_asset_fin_rec.eofy_reserve,
1140 X_cip_cost => l_asset_fin_rec.cip_cost,
1141 X_terminal_gain_loss_amount => null,
1142 X_terminal_gain_loss_flag => 'Y',
1143 X_ltd_cost_of_removal => l_asset_fin_rec.ltd_cost_of_removal,
1144 X_mrc_sob_type_code => p_mrc_sob_type_code,
1145 X_Return_Status => l_status,
1146 X_Calling_Fn => l_calling_fn
1147 ,p_log_level_rec => p_log_level_rec);
1148
1149 if not l_status then
1150 fa_debug_pkg.add(l_calling_fn, 'Failed to insert ', 'FA_BOOKS'
1151 ,p_log_level_rec => p_log_level_rec);
1152 raise rb_error;
1153 end if;
1154
1155 elsif (l_event_status = XLA_EVENTS_PUB_PKG.C_EVENT_UNPROCESSED) then
1156
1157 if (p_log_level_rec.statement_level) then
1158 fa_debug_pkg.add(l_calling_fn, 'deleting event', l_event_id
1159 ,p_log_level_rec => p_log_level_rec);
1160 end if;
1161
1162 XLA_EVENTS_PUB_PKG.delete_event
1163 (p_event_source_info => l_deprn_source_info,
1164 p_event_id => l_event_id,
1165 p_valuation_method => p_asset_hdr_rec.book_type_code,
1166 p_security_context => l_security_context);
1167
1168 -- Rollback Terminal Gain Loss
1169 if (p_mrc_sob_type_code <> 'R') then
1170 delete from fa_transaction_headers
1171 where transaction_header_id = l_thid;
1172
1173 delete from fa_adjustments
1174 where asset_id = p_asset_hdr_rec.asset_id
1175 and book_type_code = p_asset_hdr_rec.book_type_code
1176 and transaction_header_id = l_thid;
1177
1178 delete from fa_books
1179 where asset_id = p_asset_hdr_rec.asset_id
1180 and book_type_code = p_asset_hdr_rec.book_type_code
1181 and transaction_header_id_in = l_thid;
1182
1183 update fa_books
1184 set transaction_header_id_out = null,
1185 date_ineffective = null
1186 where asset_id = p_asset_hdr_rec.asset_id
1187 and book_type_code = p_asset_hdr_rec.book_type_code
1188 and transaction_header_id_out = l_thid;
1189
1190 else
1191
1192 delete from fa_adjustments_mrc_v
1193 where asset_id = p_asset_hdr_rec.asset_id
1194 and book_type_code = p_asset_hdr_rec.book_type_code
1195 and transaction_header_id = l_thid;
1196
1197 delete from fa_books_mrc_v
1198 where asset_id = p_asset_hdr_rec.asset_id
1199 and book_type_code = p_asset_hdr_rec.book_type_code
1200 and transaction_header_id_in = l_thid;
1201
1202 update fa_books_mrc_v
1203 set transaction_header_id_out = null,
1204 date_ineffective = null
1205 where asset_id = p_asset_hdr_rec.asset_id
1206 and book_type_code = p_asset_hdr_rec.book_type_code
1207 and transaction_header_id_out = l_thid;
1208
1209 end if; -- (p_mrc_sob_type_code <> 'R')
1210
1211 else
1212 raise rb_error;
1213 end if; -- (l_event_status = XLA_EVENTS_PUB_PKG.C_EVENT_PROCESSED)
1214
1215 -- Common for both situations (reverse, delete)
1216 if (p_mrc_sob_type_code <> 'R') then
1217 update fa_books_summary
1218 set terminal_gain_loss_flag = 'Y',
1219 terminal_gain_loss_amount = null,
1220 reserve_adjustment_amount = reserve_adjustment_amount - terminal_gain_loss_amount,
1221 deprn_reserve = deprn_reserve - terminal_gain_loss_amount,
1222 last_update_date = l_trans_rec.who_info.last_update_date,
1223 last_update_login = l_trans_rec.who_info.last_update_login,
1224 last_updated_by = l_trans_rec.who_info.last_updated_by
1225 where book_type_code = p_asset_hdr_rec.book_type_code
1226 and asset_id = p_asset_hdr_rec.asset_id
1227 and period_counter = p_period_rec.period_counter;
1228 else
1229 update fa_books_summary_mrc_v
1230 set terminal_gain_loss_flag = 'Y',
1231 terminal_gain_loss_amount = null,
1232 reserve_adjustment_amount = reserve_adjustment_amount - terminal_gain_loss_amount,
1233 deprn_reserve = deprn_reserve - terminal_gain_loss_amount,
1234 last_update_date = l_trans_rec.who_info.last_update_date,
1235 last_update_login = l_trans_rec.who_info.last_update_login,
1236 last_updated_by = l_trans_rec.who_info.last_updated_by
1237 where book_type_code = p_asset_hdr_rec.book_type_code
1238 and asset_id = p_asset_hdr_rec.asset_id
1239 and period_counter = p_period_rec.period_counter;
1240 end if;
1241
1242 end if; -- l_thid is not null)
1243
1244 -- End of terminal gain loss
1245
1246 -- Bug:6665510:Japan Tax Reform Project
1247 if (g_print_debug) then
1248 fa_debug_pkg.add('FAVDRBB', 'guarantee_flag', fa_cache_pkg.fazccmt_record.guarantee_rate_method_flag);
1249 fa_debug_pkg.add('FAVDRBB', 'period_num', p_period_rec.period_num);
1250 fa_debug_pkg.add('FAVDRBB', 'pers_per_yr', pers_per_yr);
1251 end if;
1252
1253 if (p_period_rec.period_num - pers_per_yr) = 0 then
1254 fa_debug_pkg.add('Updating rate_in_use', 'EOFY', 'YES');
1255
1256 if (p_mrc_sob_type_code = 'R') then
1257 Null;
1258 else
1259 open c_rate_in_use;
1260 end if;
1261
1262 loop
1263 if (p_mrc_sob_type_code = 'R') then
1264 Null;
1265 else
1266
1267 fetch c_rate_in_use bulk collect
1268 into l_bks_rowid_tbl2,
1269 l_asset_id_tbl2,
1270 l_method_code_tbl2,
1271 l_life_in_months_tbl2
1272 LIMIT l_batch_size;
1273
1274 l_rows_processed := l_bks_rowid_tbl2.count;
1275
1276 if l_rows_processed = 0 then
1277 exit;
1278 end if;
1279
1280 fa_debug_pkg.add('FAVDRBB', 'l_bks_rowid_tbl2.count', l_bks_rowid_tbl2.count);
1281
1282 for i in 1..l_bks_rowid_tbl2.count loop
1283 fa_debug_pkg.add('FAVDRBB', 'l_asset_id_tbl2(i)', l_asset_id_tbl2(i));
1284 fa_debug_pkg.add('FAVDRBB', 'l_method_code_tbl2(i)', l_method_code_tbl2(i));
1285
1286 FA_CDE_PKG.faxgfr (X_Book_Type_Code => p_asset_hdr_rec.book_type_code,
1287 X_Asset_Id => l_asset_id_tbl2(i),
1288 X_Short_Fiscal_Year_Flag => NULL,
1289 X_Conversion_Date => NULL,
1290 X_Prorate_Date => NULL,
1291 X_Orig_Deprn_Start_Date => NULL,
1292 C_Prorate_Date => NULL,
1293 C_Conversion_Date => NULL,
1294 C_Orig_Deprn_Start_Date => NULL,
1295 X_Method_Code => l_method_code_tbl2(i),
1296 X_Life_In_Months => l_life_in_months_tbl2(i),
1297 X_Fiscal_Year => -99,
1298 X_Current_Period => -99,
1299 X_calling_interface => 'ROLLBACK_DEPRN',
1300 X_Rate => l_rate_in_use_tbl(i),
1301 X_Method_Type => l_method_type,
1302 X_Success => l_success);
1303
1304 if (l_success <= 0) then
1305 fa_srvr_msg.add_message(calling_fn => 'FA_DEPRN_ROLLBACK_PKG.do_rollback');
1306 raise rb_error;
1307 end if;
1308 end loop;
1309
1310 fa_debug_pkg.add('FAVDRBB', 'update fa_books.rate_in_use', l_bks_rowid_tbl2.count);
1311
1312 forall i IN 1..l_bks_rowid_tbl2.count
1313 update fa_books
1314 set rate_in_use = l_rate_in_use_tbl(i)
1315 where rowid = l_bks_rowid_tbl2(i);
1316 end if;
1317 end loop;
1318
1319 if (p_mrc_sob_type_code = 'R') then
1320 Null;
1321 else
1322 close c_rate_in_use;
1323 end if;
1324 end if;
1325 -- Bug:6665510:Japan Tax Reform Project (End)
1326
1327 -- updates to adj_req_status should be obseolete at this point
1328 -- since deprn will not catchup
1329
1330 -- BUG# 2238090
1331 -- reset the periodic production amounts for primary only
1332 -- bridgway 02/25/02
1333
1334 if (p_mrc_sob_type_code <> 'R') then
1335
1336 update fa_periodic_production pp
1337 set used_flag = 'NO'
1338 where pp.asset_id = p_asset_hdr_rec.asset_id
1339 and pp.book_type_code = p_asset_hdr_rec.book_type_code
1340 and pp.start_date >= p_period_rec.calendar_period_open_date
1341 and pp.end_date <= p_period_rec.calendar_period_close_date;
1342
1343 -- Manual Override
1344
1345 fa_std_types.deprn_override_trigger_enabled:= FALSE;
1346
1347 update FA_DEPRN_OVERRIDE do
1348 set status = 'POST'
1349 where do.asset_id = p_asset_hdr_rec.asset_id
1350 and do.book_type_code = p_asset_hdr_rec.book_type_code
1351 and do.period_name = p_period_rec.period_name
1352 and do.used_by = 'DEPRECIATION'
1353 and do.status = 'POSTED';
1354
1355 fa_std_types.deprn_override_trigger_enabled:= TRUE;
1356
1357 -- End of Manual Override
1358
1359 end if;
1360
1361
1362 return TRUE;
1363
1364 EXCEPTION
1365 when rb_error then
1366 fa_srvr_msg.add_message(calling_fn => l_calling_fn
1367 ,p_log_level_rec => p_log_level_rec);
1368 return FALSE;
1369
1370 when others then
1371 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn
1372 ,p_log_level_rec => p_log_level_rec);
1373 return FALSE;
1374
1375 end do_rollback;
1376
1377 END FA_DEPRN_ROLLBACK_PVT;