[Home] [Help]
PACKAGE BODY: APPS.FA_DEPRN_EVENTS_PKG
Source
1 package body FA_DEPRN_EVENTS_PKG as
2 /* $Header: fadpevnb.pls 120.13.12010000.2 2008/07/31 07:12:51 sbhaskar ship $ */
3
4 g_log_level_rec fa_api_types.log_level_rec_type;
5
6 PROCEDURE process_deprn_events
7 (p_book_type_code varchar2,
8 p_period_counter number,
9 p_total_requests NUMBER,
10 p_request_number NUMBER,
11 x_return_status OUT NOCOPY number) IS
12
13 l_asset_id_tbl FA_XLA_EVENTS_PVT.number_tbl_type;
14 l_rowid_tbl rowid_tbl_type;
15 l_event_id_tbl FA_XLA_EVENTS_PVT.number_tbl_type;
16 l_event_date date;
17
18 l_sob_index number;
19 l_sob_tbl fa_cache_pkg.fazcrsob_sob_tbl_type;
20
21 l_batch_size number;
22 l_calling_fn varchar2(60) := 'FA_DEPRN_EVENTS_PKG.process_deprn_events';
23
24 error_found exception;
25
26 CURSOR DEPRN_EVENTS (p_book_type_code varchar2,
27 p_period_counter number
28 ) IS
29 select rowid,
30 asset_id
31 from fa_deprn_summary
32 where book_type_code = p_book_type_code
33 and period_counter = p_period_counter
34 and event_id is null
35 and (deprn_amount - deprn_adjustment_amount <> 0 or
36 reval_amortization <> 0)
37 and MOD(asset_id, p_total_requests) = (p_request_number - 1)
38 and deprn_source_code <> 'TRACK';
39
40 CURSOR MC_DEPRN_EVENTS (p_set_of_books_id number,
41 p_book_type_code varchar2,
42 p_period_counter number
43 ) IS
44 select rowid,
45 asset_id
46 from fa_mc_deprn_summary
47 where set_of_books_id = p_set_of_books_id
48 and book_type_code = p_book_type_code
49 and period_counter = p_period_counter
50 and event_id is null
51 and (deprn_amount - deprn_adjustment_amount <> 0 or
52 reval_amortization <> 0)
53 and MOD(asset_id, p_total_requests) = (p_request_number - 1)
54 and deprn_source_code <> 'TRACK';
55
56 l_deprn_run_id number;
57
58 BEGIN
59
60 if (not g_log_level_rec.initialized) then
61 if (NOT fa_util_pub.get_log_level_rec (
62 x_log_level_rec => g_log_level_rec
63 )) then
64 raise error_found;
65 end if;
66 end if;
67
68
69 -- clear the debug stack for each line
70 FA_DEBUG_PKG.Initialize;
71
72 if (g_log_level_rec.statement_level) then
73 fa_debug_pkg.add(l_calling_fn, 'at ', 'begin' ,p_log_level_rec => g_log_level_rec);
74 end if;
75
76 if not fa_cache_pkg.fazcbc(X_book => p_book_type_code,
77 p_log_level_rec => g_log_level_rec) then
78 raise error_found;
79 end if;
80
81 if not fa_cache_pkg.fazcdp(x_book_type_code => p_book_type_code,
82 x_period_counter => p_period_counter,
83 x_effective_date => null,
84 p_log_level_rec => g_log_level_rec) then
85 raise error_found;
86 end if;
87
88
89 if (g_log_level_rec.statement_level) then
90 fa_debug_pkg.add(l_calling_fn, 'setting ', 'batch size',
91 p_log_level_rec => g_log_level_rec);
92 end if;
93
94 l_batch_size := nvl(fa_cache_pkg.fa_batch_size, 200);
95
96 select fa_deprn_summary_s.nextval
97 into l_deprn_run_id
98 from dual;
99
100 if (g_log_level_rec.statement_level) then
101 fa_debug_pkg.add(l_calling_fn, 'initializing', 'debug stack',
102 p_log_level_rec => g_log_level_rec);
103 end if;
104
105
106
107 if (g_log_level_rec.statement_level) then
108 fa_debug_pkg.add(l_calling_fn, 'opening', 'deprn_events cursor',
109 p_log_level_rec => g_log_level_rec);
110 end if;
111
112
113 OPEN deprn_events(p_book_type_code => p_book_type_code,
114 p_period_counter => p_period_counter);
115
116
117 loop
118
119 if (g_log_level_rec.statement_level) then
120 fa_debug_pkg.add(l_calling_fn, 'in', 'loop',
121 p_log_level_rec => g_log_level_rec);
122 end if;
123
124 FETCH deprn_events bulk collect
125 into l_rowid_tbl,
126 l_asset_id_tbl
127 LIMIT l_batch_size;
128
129
130
131 if (g_log_level_rec.statement_level) then
132 fa_debug_pkg.add(l_calling_fn, 'l_asset_id_tbl.count', l_asset_id_tbl.count,
133 p_log_level_rec => g_log_level_rec);
134 end if;
135
136 if (l_asset_id_tbl.count = 0) then
137 exit;
138 end if;
139
140 if (g_log_level_rec.statement_level) then
141 fa_debug_pkg.add(l_calling_fn, 'calling', 'xla event api',
142 p_log_level_rec => g_log_level_rec);
143 end if;
144
145 l_event_date :=
146 greatest(fa_cache_pkg.fazcdp_record.calendar_period_open_date,
147 least(nvl(fa_cache_pkg.fazcdp_record.calendar_period_close_date,
148 sysdate),
149 sysdate));
150
151
152 fa_xla_events_pvt.create_bulk_deprn_event
153 (p_asset_id_tbl => l_asset_id_tbl,
154 p_book_type_code => p_book_type_code,
155 p_period_counter => p_period_counter,
156 p_period_close_date => l_event_date,
157 p_deprn_run_id => l_deprn_run_id,
158 p_entity_type_code => 'DEPRECIATION',
159 x_event_id_tbl => l_event_id_tbl,
160 p_calling_fn => l_calling_fn,
161 p_log_level_rec => g_log_level_rec);
162
163 if (g_log_level_rec.statement_level) then
164 fa_debug_pkg.add(l_calling_fn, 'performing', 'bulk update - primary',
165 p_log_level_rec => g_log_level_rec);
166 end if;
167
168 FORALL l_count in 1..l_asset_id_tbl.count
169 UPDATE FA_DEPRN_SUMMARY
170 SET event_id = l_event_id_tbl(l_count),
171 deprn_run_id = l_deprn_run_id
172 WHERE rowid = l_rowid_tbl(l_count);
173
174
175 FORALL l_count in 1..l_asset_id_tbl.count
176 UPDATE FA_DEPRN_DETAIL
177 SET event_id = l_event_id_tbl(l_count),
178 deprn_run_id = l_deprn_run_id
179 WHERE asset_id = l_asset_id_tbl(l_count)
180 AND book_type_code = p_book_type_code
181 AND period_counter = p_period_counter;
182
183 if (g_log_level_rec.statement_level) then
184 fa_debug_pkg.add(l_calling_fn, 'performing', 'bulk update - reporting',
185 p_log_level_rec => g_log_level_rec);
186 end if;
187
188 -- now process all matching mrc rows
189 FORALL l_count in 1..l_asset_id_tbl.count
190 UPDATE FA_MC_DEPRN_SUMMARY
191 SET event_id = l_event_id_tbl(l_count),
192 deprn_run_id = l_deprn_run_id
193 WHERE asset_id = l_asset_id_tbl(l_count)
194 AND book_type_code = p_book_type_code
195 AND period_counter = p_period_counter;
196
197
198 FORALL l_count in 1..l_asset_id_tbl.count
199 UPDATE FA_MC_DEPRN_DETAIL
200 SET event_id = l_event_id_tbl(l_count),
201 deprn_run_id = l_deprn_run_id
202 WHERE asset_id = l_asset_id_tbl(l_count)
203 AND book_type_code = p_book_type_code
204 AND period_counter = p_period_counter;
205
206 if (g_log_level_rec.statement_level) then
207 fa_debug_pkg.add(l_calling_fn, 'done', 'bulk updates',
208 p_log_level_rec => g_log_level_rec);
209 end if;
210
211 FORALL l_count in 1..l_asset_id_tbl.count
212 INSERT into fa_deprn_events
213 (asset_id ,
214 book_type_code ,
215 period_counter ,
216 deprn_run_id ,
217 deprn_run_date ,
218 event_id ,
219 reversal_event_id
220 )
221 VALUES
222 (l_asset_id_tbl(l_count),
223 p_book_type_code,
224 p_period_counter,
225 l_deprn_run_id,
226 sysdate,
227 l_event_id_tbl(l_count),
228 null);
229
230 if (g_log_level_rec.statement_level) then
231 fa_debug_pkg.add(l_calling_fn, 'done', 'bulk insert',
232 p_log_level_rec => g_log_level_rec);
233 end if;
234
235 commit;
236
237 end loop;
238
239 CLOSE deprn_events;
240
241 -- now find any mrc rows which are not processed yet and update
242 if not FA_CACHE_PKG.fazcrsob
243 (x_book_type_code => p_book_type_code,
244 x_sob_tbl => l_sob_tbl,
245 p_log_level_rec => g_log_level_rec) then
246 raise error_found;
247 end if;
248
249
250 -- begin at index of 1 not 0 as in apis
251 FOR l_sob_index in 1..l_sob_tbl.count LOOP
252
253 if (g_log_level_rec.statement_level) then
254 fa_debug_pkg.add(l_calling_fn, 'opening mc_deprn_events for sob', l_sob_tbl(l_sob_index),
255 p_log_level_rec => g_log_level_rec);
256 end if;
257
258 OPEN mc_deprn_events(p_set_of_books_id => l_sob_tbl(l_sob_index),
259 p_book_type_code => p_book_type_code,
260 p_period_counter => p_period_counter);
261
262 loop
263
264 if (g_log_level_rec.statement_level) then
265 fa_debug_pkg.add(l_calling_fn, 'in', 'mrc loop',
266 p_log_level_rec => g_log_level_rec);
267 end if;
268
269 FETCH mc_deprn_events bulk collect
270 into l_rowid_tbl,
271 l_asset_id_tbl
272 LIMIT l_batch_size;
273
274 if (g_log_level_rec.statement_level) then
275 fa_debug_pkg.add(l_calling_fn, 'l_asset_id_tbl.count', l_asset_id_tbl.count,
276 p_log_level_rec => g_log_level_rec);
277 end if;
278
279 if (l_asset_id_tbl.count = 0) then
280 exit;
281 end if;
282
283 if (g_log_level_rec.statement_level) then
284 fa_debug_pkg.add(l_calling_fn, 'calling', 'xla event api',
285 p_log_level_rec => g_log_level_rec);
286 end if;
287
288 fa_xla_events_pvt.create_bulk_deprn_event
289 (p_asset_id_tbl => l_asset_id_tbl,
290 p_book_type_code => p_book_type_code,
291 p_period_counter => p_period_counter,
292 p_period_close_date => sysdate, -- fa_cache_pkg.fazcdp_record.period_close_date,
293 p_deprn_run_id => l_deprn_run_id,
294 p_entity_type_code => 'DEPRECIATION',
295 x_event_id_tbl => l_event_id_tbl,
296 p_calling_fn => l_calling_fn,
297 p_log_level_rec => g_log_level_rec);
298
299 if (g_log_level_rec.statement_level) then
300 fa_debug_pkg.add(l_calling_fn, 'performing', 'bulk update - reporting1',
301 p_log_level_rec => g_log_level_rec);
302 end if;
303
304 FORALL l_count in 1..l_asset_id_tbl.count
305 UPDATE FA_MC_DEPRN_SUMMARY
306 SET event_id = l_event_id_tbl(l_count),
307 deprn_run_id = l_deprn_run_id
308 WHERE rowid = l_rowid_tbl(l_count);
309
310
311 FORALL l_count in 1..l_asset_id_tbl.count
312 UPDATE FA_MC_DEPRN_DETAIL
313 SET event_id = l_event_id_tbl(l_count),
314 deprn_run_id = l_deprn_run_id
315 WHERE asset_id = l_asset_id_tbl(l_count)
316 AND book_type_code = p_book_type_code
317 AND period_counter = p_period_counter;
318
319 if (g_log_level_rec.statement_level) then
320 fa_debug_pkg.add(l_calling_fn, 'performing', 'bulk update - reporting2',
321 p_log_level_rec => g_log_level_rec);
322 end if;
323
324 -- now process all matching mrc rows for summary
325 FORALL l_count in 1..l_asset_id_tbl.count
326 UPDATE FA_MC_DEPRN_SUMMARY
327 SET event_id = l_event_id_tbl(l_count),
328 deprn_run_id = l_deprn_run_id
329 WHERE set_of_books_id <> l_sob_tbl(l_sob_index)
330 AND asset_id = l_asset_id_tbl(l_count)
331 AND book_type_code = p_book_type_code
332 AND period_counter = p_period_counter;
333
334 if (g_log_level_rec.statement_level) then
335 fa_debug_pkg.add(l_calling_fn, 'done', 'mc bulk updates',
336 p_log_level_rec => g_log_level_rec);
337 end if;
338
339 FORALL l_count in 1..l_asset_id_tbl.count
340 INSERT into fa_deprn_events
341 (asset_id ,
342 book_type_code ,
343 period_counter ,
344 deprn_run_id ,
345 deprn_run_date ,
346 event_id ,
347 reversal_event_id
348 )
349 VALUES
350 (l_asset_id_tbl(l_count),
351 p_book_type_code,
352 p_period_counter,
353 l_deprn_run_id,
354 sysdate,
355 l_event_id_tbl(l_count),
356 null);
357
358 if (g_log_level_rec.statement_level) then
359 fa_debug_pkg.add(l_calling_fn, 'done', 'bulk insert',
360 p_log_level_rec => g_log_level_rec);
361 end if;
362
363
364 end loop;
365
366 commit;
367
368 CLOSE mc_deprn_events;
369
370 END LOOP; -- sob loop
371
372 -- Bug 6391045
373 -- Code hook for IAC
374
375 if (FA_IGI_EXT_PKG.IAC_Enabled) then
376 if not FA_IGI_EXT_PKG.Do_Depreciation(
377 p_book_type_code => p_book_type_code,
378 p_period_counter => p_period_counter,
379 p_calling_function => l_calling_fn ) then
380 raise error_found;
381 end if;
382 end if; -- (FA_IGI_EXT_PKG.IAC_Enabled)
383
384 x_return_status := 0;
385
386 EXCEPTION
387 WHEN error_found THEN
388 fa_srvr_msg.add_message(calling_fn => l_calling_fn,
389 p_log_level_rec => g_log_level_rec);
390 x_return_status := 2;
391
392 commit;
393
394 raise;
395
396 WHEN OTHERS THEN
397 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn,
398 p_log_level_rec => g_log_level_rec);
399 x_return_status := 2;
400
401 commit;
402
403 raise;
404
405
406 END process_deprn_events;
407
408 --------------------------------------------------------------------------------
409
410 PROCEDURE process_deferred_events
411 (p_corp_book_type_code varchar2,
412 p_tax_book_type_code varchar2,
413 p_corp_period_counter number,
414 p_tax_period_counter number,
415 x_return_status OUT NOCOPY number) IS
416
417
418 l_asset_id_tbl FA_XLA_EVENTS_PVT.number_tbl_type;
419 l_rowid_tbl rowid_tbl_type;
420 l_event_id_tbl FA_XLA_EVENTS_PVT.number_tbl_type;
421
422 l_sob_index number;
423 l_sob_tbl fa_cache_pkg.fazcrsob_sob_tbl_type;
424
425 l_batch_size number;
426 l_calling_fn varchar2(60) := 'FA_DEPRN_EVENTS_PKG.process_deferred_events';
427
428 error_found exception;
429
430 CURSOR DEFERRED_EVENTS (p_corp_book_type_code varchar2,
431 p_tax_book_type_code varchar2,
432 p_corp_period_counter number,
433 p_tax_period_counter number
434 ) IS
435 select rowid,
436 asset_id
437 from fa_deferred_deprn
438 where corp_book_type_code = p_corp_book_type_code
439 and tax_book_type_code = p_tax_book_type_code
440 and corp_period_counter = p_corp_period_counter
441 and tax_period_counter = p_tax_period_counter
442 and event_id is null;
443
444 CURSOR MC_DEFERRED_EVENTS (p_set_of_books_id number,
445 p_corp_book_type_code varchar2,
446 p_tax_book_type_code varchar2,
447 p_corp_period_counter number,
448 p_tax_period_counter number
449 ) IS
450 select rowid,
451 asset_id
452 from fa_mc_deferred_deprn
453 where set_of_books_id = p_set_of_books_id
454 and corp_book_type_code = p_corp_book_type_code
455 and tax_book_type_code = p_tax_book_type_code
456 and corp_period_counter = p_corp_period_counter
457 and tax_period_counter = p_tax_period_counter
458 and event_id is null;
459
460 BEGIN
461
462 if (not g_log_level_rec.initialized) then
463 if (NOT fa_util_pub.get_log_level_rec (
464 x_log_level_rec => g_log_level_rec
465 )) then
466 raise error_found;
467 end if;
468 end if;
469
470 if not fa_cache_pkg.fazcbc(X_book => p_corp_book_type_code
471 ,p_log_level_rec => g_log_level_rec) then
472 raise error_found;
473 end if;
474
475 if not fa_cache_pkg.fazcdp(x_book_type_code => p_corp_book_type_code,
476 x_period_counter => p_corp_period_counter,
477 x_effective_date => null,
478 p_log_level_rec => g_log_level_rec) then
479 raise error_found;
480 end if;
481
482 l_batch_size := nvl(fa_cache_pkg.fa_batch_size, 200);
483
484 -- clear the debug stack for each line
485 FA_DEBUG_PKG.Initialize;
486
487
488 OPEN deferred_events(p_corp_book_type_code => p_corp_book_type_code,
489 p_tax_book_type_code => p_tax_book_type_code,
490 p_corp_period_counter => p_corp_period_counter ,
491 p_tax_period_counter => p_tax_period_counter);
492
493 loop
494
495 FETCH deferred_events bulk collect
496 into l_rowid_tbl,
497 l_asset_id_tbl
498 LIMIT l_batch_size;
499
500 if (l_asset_id_tbl.count = 0) then
501 exit;
502 end if;
503
504 fa_xla_events_pvt.create_bulk_deferred_event
505 (p_asset_id_tbl => l_asset_id_tbl,
506 p_corp_book => p_corp_book_type_code,
507 p_tax_book => p_tax_book_type_code,
508 p_corp_period_counter => p_corp_period_counter,
509 p_tax_period_counter => p_tax_period_counter,
510 p_period_close_date => sysdate, --fa_cache_pkg.fazcdp_record.period_close_date,
511 p_entity_type_code => 'DEFERRED_DEPRECIATION',
512 x_event_id_tbl => l_event_id_tbl,
513 p_calling_fn => l_calling_fn,
514 p_log_level_rec => g_log_level_rec);
515
516 FORALL l_count in 1..l_asset_id_tbl.count
517 UPDATE FA_DEFERRED_DEPRN
518 SET event_id = l_event_id_tbl(l_count)
519 WHERE rowid = l_rowid_tbl(l_count);
520
521 -- now process all matching mrc rows
522 FORALL l_count in 1..l_asset_id_tbl.count
523 UPDATE FA_MC_DEFERRED_DEPRN
524 SET event_id = l_event_id_tbl(l_count)
525 WHERE asset_id = l_asset_id_tbl(l_count)
526 AND corp_book_type_code = p_corp_book_type_code
527 AND tax_book_type_code = p_tax_book_type_code
528 AND corp_period_counter = p_corp_period_counter
529 AND tax_period_counter = p_tax_period_counter;
530
531 commit;
532
533
534 FORALL l_count in 1..l_asset_id_tbl.count
535 INSERT into fa_deferred_deprn_events
536 (asset_id ,
537 corp_book_type_code ,
538 tax_book_type_code ,
539 corp_period_counter ,
540 tax_period_counter ,
541 event_id
542 )
543 VALUES
544 (l_asset_id_tbl(l_count),
545 p_corp_book_type_code,
546 p_tax_book_type_code,
547 p_corp_period_counter,
548 p_tax_period_counter,
549 l_event_id_tbl(l_count));
550
551 end loop;
552
553 CLOSE deferred_events;
554
555
556
557 -- now find any mrc rows which are not processed yet and update
558 if not FA_CACHE_PKG.fazcrsob
559 (x_book_type_code => p_corp_book_type_code,
560 x_sob_tbl => l_sob_tbl,
561 p_log_level_rec => g_log_level_rec) then
562 raise error_found;
563 end if;
564
565
566 -- begin at index of 1 not 0 as in apis
567 FOR l_sob_index in 1..l_sob_tbl.count LOOP
568
569 OPEN mc_deferred_events(p_set_of_books_id => l_sob_tbl(l_sob_index),
570 p_corp_book_type_code => p_corp_book_type_code,
571 p_tax_book_type_code => p_tax_book_type_code,
572 p_corp_period_counter => p_corp_period_counter ,
573 p_tax_period_counter => p_tax_period_counter);
574
575 loop
576
577 FETCH mc_deferred_events bulk collect
578 into l_rowid_tbl,
579 l_asset_id_tbl
580 LIMIT l_batch_size;
581
582 if (l_asset_id_tbl.count = 0) then
583 exit;
584 end if;
585
586
587 fa_xla_events_pvt.create_bulk_deferred_event
588 (p_asset_id_tbl => l_asset_id_tbl,
589 p_corp_book => p_corp_book_type_code,
590 p_tax_book => p_tax_book_type_code,
591 p_corp_period_counter => p_corp_period_counter,
592 p_tax_period_counter => p_tax_period_counter,
593 p_period_close_date => fa_cache_pkg.fazcdp_record.period_close_date,
594 p_entity_type_code => 'DEFERRED_DEPRECIATION',
595 x_event_id_tbl => l_event_id_tbl,
596 p_calling_fn => l_calling_fn,
597 p_log_level_rec => g_log_level_rec);
598
599 FORALL l_count in 1..l_asset_id_tbl.count
600 UPDATE FA_MC_DEFERRED_DEPRN
601 SET event_id = l_event_id_tbl(l_count)
602 WHERE rowid = l_rowid_tbl(l_count);
603
604 -- now process all matching mrc rows
605 FORALL l_count in 1..l_asset_id_tbl.count
606 UPDATE FA_MC_DEFERRED_DEPRN
607 SET event_id = l_event_id_tbl(l_count)
608 WHERE set_of_books_id <> l_sob_tbl(l_sob_index)
609 AND asset_id = l_asset_id_tbl(l_count)
610 AND corp_book_type_code = p_corp_book_type_code
611 AND tax_book_type_code = p_tax_book_type_code
612 AND corp_period_counter = p_corp_period_counter
613 AND tax_period_counter = p_tax_period_counter;
614
615
616 FORALL l_count in 1..l_asset_id_tbl.count
617 INSERT into fa_deferred_deprn_events
618 (asset_id ,
619 corp_book_type_code ,
620 tax_book_type_code ,
621 corp_period_counter ,
622 tax_period_counter ,
623 event_id
624 )
625 VALUES
626 (l_asset_id_tbl(l_count),
627 p_corp_book_type_code,
628 p_tax_book_type_code,
629 p_corp_period_counter,
630 p_tax_period_counter,
631 l_event_id_tbl(l_count));
632
633 end loop;
634
635 commit;
636
637 CLOSE mc_deferred_events;
638
639 END LOOP; -- sob loop
640
641 x_return_status := 0;
642
643 EXCEPTION
644 WHEN error_found THEN
645 fa_srvr_msg.add_message(calling_fn => l_calling_fn,
646 p_log_level_rec => g_log_level_rec);
647 x_return_status := 2;
648 raise;
649
650 WHEN OTHERS THEN
651 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn,
652 p_log_level_rec => g_log_level_rec);
653 x_return_status := 2;
654 raise;
655
656 END process_deferred_events;
657
658
659 END FA_DEPRN_EVENTS_PKG;