[Home] [Help]
PACKAGE BODY: APPS.FA_DEFERRED_DEPRN_PKG
Source
1 PACKAGE BODY fa_deferred_deprn_pkg AS
2 /* $Header: FAXDEFB.pls 120.8.12010000.2 2009/01/20 15:35:13 bridgway ship $ */
3
4 ---------------------------------------------------
5 -- Declaration of global variables --
6 ---------------------------------------------------
7
8 G_CURRENT_RUNTIME_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
9
10 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
11 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
12 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
13 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
14 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
15 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
16
17 G_MODULE_NAME CONSTANT VARCHAR2(50):= 'fa.plsql.fa_deferred_deprn_pkg.';
18
19 TYPE number_tbl_type IS TABLE OF number INDEX BY BINARY_INTEGER;
20 TYPE char_tbl_type IS TABLE OF varchar2(30) INDEX BY BINARY_INTEGER;
21 TYPE rowid_tbl_type IS TABLE OF varchar2(64) INDEX BY BINARY_INTEGER;
22
23 ---------------------------------------------------
24 -- Declaration of local procedures and functions --
25 ---------------------------------------------------
26
27 Procedure deferred_deprn (p_corp_book varchar2,
28 p_tax_book varchar2,
29 p_corp_period_ctr number,
30 p_tax_period_ctr number,
31 p_mrc_sob_type_code varchar2) is
32
33 l_batch_size number;
34 l_procedure_name varchar2(80) := 'deferred_deprn';
35
36 l_deferred_deprn_exp_acct number;
37
38 error_found EXCEPTION;
39
40 l_corp_sob_id number_tbl_type;
41 l_tax_sob_id number_tbl_type;
42 l_asset_id number_tbl_type;
43 l_rowid_tbl rowid_tbl_type;
44 l_dist_id number_tbl_type;
45 l_dh_ccid number_tbl_type;
46 l_corp_deprn number_tbl_type;
47 l_tax_deprn number_tbl_type;
48 l_corp_dd_period_counter number_tbl_type;
49 l_tax_dd_period_counter number_tbl_type;
50 l_corp_rsv_adj number_tbl_type;
51 l_tax_rsv_adj number_tbl_type;
52
53 l_prior_period_tfr number_tbl_type;
54
55 CURSOR c_mrc_deferred is
56 SELECT corp_bc.set_of_books_id,
57 tax_bc.set_of_books_id,
58 DH.Asset_ID,
59 DH.Distribution_ID,
60 DH.Code_Combination_ID,
61 sum (nvl (CORP_DD.Deprn_Amount, 0)),
62 sum (nvl ( TAX_DD.Deprn_Amount, 0)),
63 nvl(CORP_DD.Period_Counter, -1),
64 nvl(TAX_DD.Period_Counter, -1),
65 nvl(sum(nvl(decode(CORP_AJ.debit_credit_flag, 'CR', 1, -1) *
66 CORP_AJ.Adjustment_Amount, 0)), 0),
67 nvl(sum(nvl(decode(TAX_AJ.debit_credit_flag, 'CR', 1, -1) *
68 TAX_AJ.Adjustment_Amount, 0)), 0)
69 FROM FA_DISTRIBUTION_HISTORY DH,
70 FA_DEPRN_DETAIL_MRC_V CORP_DD,
71 FA_DEPRN_DETAIL_MRC_V TAX_DD,
72 FA_ASSET_HISTORY AH,
73 FA_DEPRN_PERIODS_MRC_V DP,
74 fa_adjustments_mrc_v CORP_AJ,
75 fa_adjustments_mrc_v TAX_AJ,
76 fa_book_controls_mrc_v corp_bc,
77 fa_book_controls_mrc_v tax_bc
78 WHERE DH.Book_Type_Code = p_corp_book
79 AND CORP_BC.book_type_code = p_corp_book
80 AND TAX_BC.book_type_code = p_tax_book
81 AND CORP_DD.Book_Type_Code (+) = p_corp_book
82 AND CORP_DD.set_of_books_id = corp_bc.set_of_books_id
83 AND CORP_DD.Period_Counter (+) = p_tax_period_ctr
84 AND CORP_DD.Distribution_ID (+) = DH.Distribution_ID
85 AND CORP_DD.Deprn_Source_Code (+) <> 'T'
86 AND TAX_DD.Book_Type_Code (+) = p_tax_book
87 AND TAX_DD.set_of_books_id = tax_bc.set_of_books_id
88 AND TAX_DD.Period_Counter (+) = p_tax_period_ctr
89 AND TAX_DD.Distribution_ID (+) = DH.Distribution_ID
90 AND TAX_DD.Deprn_Source_Code (+) <> 'T'
91 AND TAX_DD.asset_id(+) = DH.asset_id
92 AND DP.Period_Counter = p_tax_period_ctr
93 AND DP.Book_Type_Code = p_tax_book
94 AND DP.set_of_books_id = tax_bc.set_of_books_id
95 AND AH.Asset_ID = DH.Asset_ID
96 AND AH.Date_Effective < DP.Period_Close_Date
97 AND DP.Period_Close_Date <= NVL(AH.Date_Ineffective,
98 DP.Period_Close_Date)
99 AND AH.Asset_Type in ('CAPITALIZED', 'GROUP')
100 AND CORP_AJ.Book_Type_Code(+) = p_corp_book
101 AND CORP_AJ.set_of_books_id = corp_bc.set_of_books_id
102 AND CORP_AJ.Period_Counter_Created(+) = p_tax_period_ctr
103 AND CORP_AJ.Distribution_ID(+) = dh.distribution_id
104 AND nvl(CORP_AJ.Adjustment_Amount,-9999) <> 0
105 AND CORP_AJ.Adjustment_Type(+) = 'RESERVE'
106 AND nvl(CORP_AJ.Track_Member_Flag,'N') = 'N'
107 AND TAX_AJ.Book_Type_Code (+) = p_corp_book
108 AND TAX_AJ.Period_Counter_Created (+) = p_tax_period_ctr
109 AND TAX_AJ.Distribution_ID (+) = dh.distribution_id
110 AND nvl(TAX_AJ.Adjustment_Amount,-9999) <> 0
111 AND TAX_AJ.Adjustment_Type (+) = 'RESERVE'
112 AND nvl(TAX_AJ.Track_Member_Flag,'N') = 'N'
113 GROUP BY DH.Asset_ID,
114 DH.Distribution_ID,
115 DH.Code_Combination_ID,
116 CORP_DD.Period_counter,
117 TAX_DD.Period_counter,
118 CORP_BC.set_of_books_id,
119 TAX_BC.set_of_books_id
120 ORDER BY DH.Asset_ID,
121 DH.Distribution_ID,
122 DH.Code_Combination_ID;
123
124 CURSOR c_deferred IS
125 SELECT DH.Asset_ID,
126 DH.Distribution_ID,
127 DH.Code_Combination_ID,
128 sum (nvl (CORP_DD.Deprn_Amount, 0)),
129 sum (nvl ( TAX_DD.Deprn_Amount, 0)),
130 nvl(CORP_DD.Period_Counter, -1),
131 nvl(TAX_DD.Period_Counter, -1),
132 nvl(sum(nvl(decode(CORP_AJ.debit_credit_flag, 'CR', 1, -1) *
133 CORP_AJ.Adjustment_Amount, 0)), 0),
134 nvl(sum(nvl(decode(TAX_AJ.debit_credit_flag, 'CR', 1, -1) *
135 TAX_AJ.Adjustment_Amount, 0)), 0)
136 FROM FA_DISTRIBUTION_HISTORY DH,
137 FA_DEPRN_DETAIL CORP_DD,
138 FA_DEPRN_DETAIL TAX_DD,
139 FA_ASSET_HISTORY AH,
140 FA_DEPRN_PERIODS DP,
141 fa_adjustments_mrc_v CORP_AJ,
142 fa_adjustments_mrc_v TAX_AJ
143 WHERE DH.Book_Type_Code = p_corp_book
144 AND CORP_DD.Book_Type_Code (+) = p_corp_book
145 AND CORP_DD.Period_Counter (+) = p_tax_period_ctr
146 AND CORP_DD.Distribution_ID (+) = DH.Distribution_ID
147 AND CORP_DD.Deprn_Source_Code (+) <> 'T'
148 AND TAX_DD.Book_Type_Code (+) = p_tax_book
149 AND TAX_DD.Period_Counter (+) = p_tax_period_ctr
150 AND TAX_DD.Distribution_ID (+) = DH.Distribution_ID
151 AND TAX_DD.Deprn_Source_Code (+) <> 'T'
152 AND TAX_DD.asset_id(+) = DH.asset_id
153 AND DP.Period_Counter = p_tax_period_ctr
154 AND DP.Book_Type_Code = p_tax_book
155 AND AH.Asset_ID = DH.Asset_ID
156 AND AH.Date_Effective < DP.Period_Close_Date
157 AND DP.Period_Close_Date <= NVL(AH.Date_Ineffective,
158 DP.Period_Close_Date)
159 AND AH.Asset_Type in ('CAPITALIZED', 'GROUP')
160 AND CORP_AJ.Book_Type_Code(+) = p_corp_book
161 AND CORP_AJ.Period_Counter_Created(+) = p_tax_period_ctr
162 AND CORP_AJ.Distribution_ID(+) = dh.distribution_id
163 AND nvl(CORP_AJ.Adjustment_Amount,-9999) <> 0
164 AND CORP_AJ.Adjustment_Type(+) = 'RESERVE'
165 AND nvl(CORP_AJ.Track_Member_Flag,'N') = 'N'
166 AND TAX_AJ.Book_Type_Code (+) = p_tax_book
167 AND TAX_AJ.Period_Counter_Created (+) = p_tax_period_ctr
168 AND TAX_AJ.Distribution_ID (+) = dh.distribution_id
169 AND nvl(TAX_AJ.Adjustment_Amount,-9999) <> 0
170 AND TAX_AJ.Adjustment_Type (+) = 'RESERVE'
171 AND nvl(TAX_AJ.Track_Member_Flag,'N') = 'N'
172 GROUP BY DH.Asset_ID,
173 DH.Distribution_ID,
174 DH.Code_Combination_ID,
175 CORP_DD.Period_counter,
176 TAX_DD.Period_counter
177 ORDER BY DH.Asset_ID,
178 DH.Distribution_ID,
179 DH.Code_Combination_ID;
180
181 begin
182
183 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
184 fnd_log.string(G_LEVEL_PROCEDURE,
185 G_MODULE_NAME||l_procedure_name||'.begin',
186 'Beginning of procedure');
187 END IF;
188
189 -- call the book_controls cache
190 if NOT fa_cache_pkg.fazcbc(X_book => p_tax_book) then
191 raise error_found;
192 end if;
193
194 l_batch_size := nvl(fa_cache_pkg.fa_batch_size, 200);
195
196 -- The deferred deprn exp acct of primary book is the same as the
197 -- reporting book. No need to enable MRC
198 select deferred_deprn_expense_acct
199 into l_deferred_deprn_exp_acct
200 from fa_book_controls
201 where book_type_code = p_tax_book;
202
203
204 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
205 fnd_log.string(G_LEVEL_STATEMENT,
206 G_MODULE_NAME||l_procedure_name,
207 'opening ' || p_mrc_sob_type_code || ' cursor');
208 END IF;
209
210 -- mrc loop
211 if (p_mrc_sob_type_code = 'R') then
212 OPEN C_MRC_DEFERRED;
213 else
214 OPEN C_DEFERRED;
215 end if;
216
217 while (TRUE) loop
218
219 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
220 fnd_log.string(G_LEVEL_STATEMENT,
221 G_MODULE_NAME||l_procedure_name,
222 'fetching ' || p_mrc_sob_type_code || ' cursor');
223 END IF;
224
225 if (p_mrc_sob_type_code = 'R') then
226 FETCH C_MRC_DEFERRED BULK COLLECT INTO
227 l_corp_sob_id,
228 l_tax_sob_id,
229 l_asset_id,
230 l_dist_id,
231 l_dh_ccid,
232 l_corp_deprn,
233 l_tax_deprn,
234 l_corp_dd_period_counter,
235 l_tax_dd_period_counter,
236 l_corp_rsv_adj,
237 l_tax_rsv_adj
238 LIMIT l_batch_size;
239 else
240 FETCH C_DEFERRED BULK COLLECT INTO
241 l_asset_id,
242 l_dist_id,
243 l_dh_ccid,
244 l_corp_deprn,
245 l_tax_deprn,
246 l_corp_dd_period_counter,
247 l_tax_dd_period_counter,
248 l_corp_rsv_adj,
249 l_tax_rsv_adj
250 LIMIT l_batch_size;
251 end if;
252
253 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
254 fnd_log.string(G_LEVEL_STATEMENT,
255 G_MODULE_NAME||l_procedure_name,
256 'rows fetched: ' || to_char(l_asset_id.count));
257 END IF;
258
259 if (l_asset_id.count = 0) then
260
261 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
262 fnd_log.string(G_LEVEL_STATEMENT,
263 G_MODULE_NAME||l_procedure_name,
264 'exiting loop');
265 END IF;
266
267 exit;
268 end if;
269
270 -- BUG# 4605332
271 -- backing out fix for 2259461
272
273 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
274 fnd_log.string(G_LEVEL_STATEMENT,
275 G_MODULE_NAME||l_procedure_name,
276 'bulk inserting into fa_deferred_deprn*' || p_mrc_sob_type_code);
277 END IF;
278
279 if (p_mrc_sob_type_code = 'R') then
280
281 FORALL i in 1..l_asset_id.count
282 INSERT INTO FA_DEFERRED_DEPRN_MRC_V
283 (set_of_books_id,
284 corp_book_type_code,
285 tax_book_type_code,
286 asset_id,
287 distribution_id,
288 deferred_deprn_expense_ccid,
289 deferred_deprn_reserve_ccid,
290 deferred_deprn_expense_amount,
291 deferred_deprn_reserve_amount,
292 corp_period_counter,
293 tax_period_counter,
294 expense_je_line_num,
295 reserve_je_line_num)
296 values
297 (l_tax_sob_id(i),
298 p_corp_book,
299 p_tax_book,
300 l_asset_id(i),
301 l_dist_id(i),
302 null, -- l_deferred_deprn_expense_ccid,
303 null, -- l_deferred_deprn_reserve_ccid,
304 (l_tax_deprn(i) - l_corp_deprn(i)) +
305 (l_tax_rsv_adj(i) - l_corp_rsv_adj(i)),
306 (l_tax_deprn(i) - l_corp_deprn(i)) +
307 (l_tax_rsv_adj(i) - l_corp_rsv_adj(i)),
308 p_corp_period_ctr,
309 p_tax_period_ctr,
310 0,
311 0);
312
313 else
314 FORALL i in 1..l_asset_id.count
315 INSERT INTO FA_DEFERRED_DEPRN
316 (corp_book_type_code,
317 tax_book_type_code,
318 asset_id,
319 distribution_id,
320 deferred_deprn_expense_ccid,
321 deferred_deprn_reserve_ccid,
322 deferred_deprn_expense_amount,
323 deferred_deprn_reserve_amount,
324 corp_period_counter,
325 tax_period_counter,
326 expense_je_line_num,
327 reserve_je_line_num)
328 values
329 (p_corp_book,
330 p_tax_book,
331 l_asset_id(i),
332 l_dist_id(i),
333 null, -- l_deferred_deprn_expense_ccid,
334 null, -- l_deferred_deprn_reserve_ccid,
335 (l_tax_deprn(i) - l_corp_deprn(i)) +
336 (l_tax_rsv_adj(i) - l_corp_rsv_adj(i)),
337 (l_tax_deprn(i) - l_corp_deprn(i)) +
338 (l_tax_rsv_adj(i) - l_corp_rsv_adj(i)),
339 p_corp_period_ctr,
340 p_tax_period_ctr,
341 0,
342 0);
343 end if;
344
345 l_corp_sob_id.delete;
346 l_tax_sob_id.delete;
347 l_asset_id.delete;
348 l_dist_id.delete;
349 l_dh_ccid.delete;
350 l_corp_deprn.delete;
351 l_tax_deprn.delete;
352 l_corp_dd_period_counter.delete;
353 l_tax_dd_period_counter.delete;
354 l_corp_rsv_adj.delete;
355 l_tax_rsv_adj.delete;
356
357 end loop;
358
359 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
360 fnd_log.string(G_LEVEL_STATEMENT,
361 G_MODULE_NAME||l_procedure_name,
362 'closing ' || p_mrc_sob_type_code || ' cursor');
363 END IF;
364
365 if (p_mrc_sob_type_code = 'R') then
366 CLOSE C_MRC_DEFERRED;
367 else
368 CLOSE C_DEFERRED;
369 end if;
370
371 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
372 fnd_log.string(G_LEVEL_PROCEDURE,
373 G_MODULE_NAME||l_procedure_name||'.end',
374 'End of procedure');
375 END IF;
376
377 EXCEPTION
378 WHEN error_found THEN
379 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
380 FND_LOG.STRING(G_LEVEL_EXCEPTION,
381 G_MODULE_NAME || l_procedure_name,
382 'errored');
383 END IF;
384 raise;
385
386
387 WHEN others THEN
388 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
389 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
390 fnd_message.set_token('ORACLE_ERR',SQLERRM);
391 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME || l_procedure_name,TRUE);
392 END IF;
393 raise;
394
395 end deferred_deprn;
396
397 --------------------------------------------------------------------------------
398
399 procedure create_bulk_deferred_events (
400 p_tax_book IN VARCHAR2,
401 p_corp_book IN VARCHAR2,
402 p_tax_period_counter IN NUMBER,
403 p_corp_period_counter IN NUMBER
404 ) IS
405
406 l_batch_size number;
407 l_period_rec fa_api_types.period_rec_type;
408
409 error_found EXCEPTION;
410
411 l_asset_id_tbl FA_XLA_EVENTS_PVT.number_tbl_type;
412 l_rowid_tbl rowid_tbl_type;
413 l_event_id_tbl FA_XLA_EVENTS_PVT.number_tbl_type;
414
415 l_sob_tbl FA_CACHE_PKG.fazcrsob_sob_tbl_type;
416
417 cursor c_deferred_events is
418 select asset_id, min(rowid)
419 from fa_deferred_deprn
420 where corp_book_type_code = p_corp_book
421 and tax_book_type_code = p_tax_book
422 and corp_period_counter = p_corp_period_counter
423 and tax_period_counter = p_tax_period_counter
424 and event_id is null
425 group by asset_id;
426
427 cursor c_mc_deferred_events (p_set_of_books_id number) is
428 select asset_id, min(rowid)
429 from fa_mc_deferred_deprn
430 where corp_book_type_code = p_corp_book
431 and tax_book_type_code = p_tax_book
432 and corp_period_counter = p_corp_period_counter
433 and tax_period_counter = p_tax_period_counter
434 and set_of_books_id = p_set_of_books_id
435 and event_id is null
436 group by asset_id;
437
438 -- Bugfix 6122229: Increased the length from varchar2(35) to varchar2(80)
439 -- for variable l_calling_fn
440 l_calling_fn varchar2(80) := 'FA_DEFERRED_PKG.create_bulk_deprn_events';
441 l_procedure_name varchar2(80) := 'create_bulk_deprn_events';
442
443 --Bug6122229
444 --Initializing the second count variable
445 l_count2 number := 1 ;
446
447 begin
448
449 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
450 fnd_log.string(G_LEVEL_PROCEDURE,
451 G_MODULE_NAME||l_procedure_name||'.begin',
452 'Beginning of procedure');
453 END IF;
454
455 l_batch_size := nvl(fa_cache_pkg.fa_batch_size, 200);
456
457 if not FA_UTIL_PVT.get_period_rec
458 (p_book => p_corp_book,
459 p_period_counter => p_corp_period_counter,
460 x_period_rec => l_period_rec
461 ) then
462 raise error_found;
463 end if;
464
465 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
466 fnd_log.string(G_LEVEL_STATEMENT,
467 G_MODULE_NAME||l_procedure_name,
468 'opening c_deferred_events');
469 END IF;
470
471 open c_deferred_events;
472
473 loop -- Loop for c_deferred_events
474
475 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
476 fnd_log.string(G_LEVEL_STATEMENT,
477 G_MODULE_NAME||l_procedure_name,
478 'bulk fetching c_deferred_events cursor');
479 END IF;
480
481 fetch c_deferred_events bulk collect
482 into l_asset_id_tbl,
483 l_rowid_tbl
484 LIMIT l_batch_size;
485
486 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
487 fnd_log.string(G_LEVEL_STATEMENT,
488 G_MODULE_NAME||l_procedure_name,
489 'rows fetched: ' || to_char(l_asset_id_tbl.count));
490 END IF;
491
492 if l_asset_id_tbl.count = 0 then
493 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
494 fnd_log.string(G_LEVEL_STATEMENT,
495 G_MODULE_NAME||l_procedure_name,
496 'exiting loop...');
497 END IF;
498
499 exit;
500 end if;
501
502 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
503 fnd_log.string(G_LEVEL_STATEMENT,
504 G_MODULE_NAME||l_procedure_name,
505 'calling FA_XLA_EVENTS_PVT.create_bulk_deferred_event for primary');
506 END IF;
507
508 -- call bulk event api
509 FA_XLA_EVENTS_PVT.create_bulk_deferred_event
510 (p_asset_id_tbl => l_asset_id_tbl,
511 p_corp_book => p_corp_book,
512 p_tax_book => p_tax_book,
513 p_corp_period_counter => p_corp_period_counter,
514 p_tax_period_counter => p_tax_period_counter,
515 p_period_close_date => l_period_rec.calendar_period_close_date,
516 -- p_legal_entity => null,
517 p_entity_type_code => 'DEFERRED_DEPRECIATION',
518 x_event_id_tbl => l_event_id_tbl,
519 p_calling_fn => l_calling_fn
520 );
521
522 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
523 fnd_log.string(G_LEVEL_STATEMENT,
524 G_MODULE_NAME||l_procedure_name,
525 'bulk inserting into fa_deferred_deprn_events');
526 END IF;
527
528 FORALL l_count in 1..l_asset_id_tbl.count
529 INSERT into fa_deferred_deprn_events
530 (asset_id ,
531 corp_book_type_code ,
532 tax_book_type_code ,
533 corp_period_counter ,
534 tax_period_counter ,
535 event_id
536 )
537 VALUES
538 (l_asset_id_tbl(l_count),
539 p_corp_book,
540 p_tax_book,
541 p_corp_period_counter,
542 p_tax_period_counter,
543 l_event_id_tbl(l_count));
544
545 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
546 fnd_log.string(G_LEVEL_STATEMENT,
547 G_MODULE_NAME||l_procedure_name,
548 'bulk updating fa_deferred_deprn with event ids');
549 END IF;
550
551 FORALL l_count in 1..l_asset_id_tbl.count
552 update fa_deferred_deprn
553 set event_id = l_event_id_tbl(l_count)
554 where asset_id = l_asset_id_tbl(l_count)
555 AND corp_book_type_code = p_corp_book
556 AND tax_book_type_code = p_tax_book
557 AND corp_period_counter = p_corp_period_counter
558 AND tax_period_counter = p_tax_period_counter;
559
560 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
561 fnd_log.string(G_LEVEL_STATEMENT,
562 G_MODULE_NAME||l_procedure_name,
563 'bulk updating fa_mc_deferred_deprn with event ids');
564 END IF;
565
566 -- now process all matching mrc rows
567 FORALL l_count in 1..l_asset_id_tbl.count
568 update fa_mc_deferred_deprn
569 SET event_id = l_event_id_tbl(l_count)
570 WHERE asset_id = l_asset_id_tbl(l_count)
571 AND corp_book_type_code = p_corp_book
572 AND tax_book_type_code = p_tax_book
573 AND corp_period_counter = p_corp_period_counter
574 AND tax_period_counter = p_tax_period_counter;
575
576 end loop; --End of loop for c_deferred_events
577
578 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
579 fnd_log.string(G_LEVEL_STATEMENT,
580 G_MODULE_NAME||l_procedure_name,
581 'closing c_deferred_events');
582 END IF;
583
584 close c_deferred_events;
585
586 -- now find any mrc rows which are not processed yet and update
587 if not FA_CACHE_PKG.fazcrsob
588 (x_book_type_code => p_corp_book,
589 x_sob_tbl => l_sob_tbl) then
590 raise error_found;
591 end if;
592
593 -- begin at index of 1 not 0 as in apis
594 FOR l_sob_index in 1..l_sob_tbl.count LOOP
595
596 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
597 fnd_log.string(G_LEVEL_STATEMENT,
598 G_MODULE_NAME||l_procedure_name,
599 'opening c_mc_deferred_events cursor');
600 END IF;
601
602 OPEN c_mc_deferred_events(p_set_of_books_id => l_sob_tbl(l_sob_index));
603
604 loop -- Loop for c_mc_deferred_events
605
606 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
607 fnd_log.string(G_LEVEL_STATEMENT,
608 G_MODULE_NAME||l_procedure_name,
609 'fetching c_mc_deferred_events cursor');
610 END IF;
611
612 FETCH c_mc_deferred_events bulk collect
613 into l_asset_id_tbl,
614 l_rowid_tbl
615 LIMIT l_batch_size;
616
617 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
618 fnd_log.string(G_LEVEL_STATEMENT,
619 G_MODULE_NAME||l_procedure_name,
620 'rows fetched: ' || to_char(l_asset_id_tbl.count));
621 END IF;
622
623 if (l_asset_id_tbl.count = 0) then
624 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
625 fnd_log.string(G_LEVEL_STATEMENT,
626 G_MODULE_NAME||l_procedure_name,
627 'exiting loop...');
628 END IF;
629
630 exit;
631 end if;
632
633 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
634 fnd_log.string(G_LEVEL_STATEMENT,
635 G_MODULE_NAME||l_procedure_name,
636 'calling FA_XLA_EVENTS_PVT.create_bulk_deferred_event for reporting');
637 END IF;
638
639 -- call bulk event api
640 FA_XLA_EVENTS_PVT.create_bulk_deferred_event
641 (p_asset_id_tbl => l_asset_id_tbl,
642 p_corp_book => p_corp_book,
643 p_tax_book => p_tax_book,
644 p_corp_period_counter => p_corp_period_counter,
645 p_tax_period_counter => p_tax_period_counter,
646 p_period_close_date => l_period_rec.calendar_period_close_date,
647 -- p_legal_entity => px_max_legal_entity_id,
648 p_entity_type_code => 'DEFERRED_DEPRECIATION',
649 x_event_id_tbl => l_event_id_tbl,
650 p_calling_fn => l_calling_fn
651 );
652
653 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
654 fnd_log.string(G_LEVEL_STATEMENT,
655 G_MODULE_NAME||l_procedure_name,
656 'bulk inserting into fa_deferred_deprn_events');
657 END IF;
658
659 FORALL l_count in 1..l_asset_id_tbl.count
660 INSERT into fa_deferred_deprn_events
661 (asset_id ,
662 corp_book_type_code ,
663 tax_book_type_code ,
664 corp_period_counter ,
665 tax_period_counter ,
666 event_id
667 )
668 VALUES
669 (l_asset_id_tbl(l_count),
670 p_corp_book,
671 p_tax_book,
672 p_corp_period_counter,
673 p_tax_period_counter,
674 l_event_id_tbl(l_count));
675
676 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
677 fnd_log.string(G_LEVEL_STATEMENT,
678 G_MODULE_NAME||l_procedure_name,
679 'bulk updating fa_mc_deferred_deprn with event ids');
680 END IF;
681
682 FORALL l_count in 1..l_asset_id_tbl.count
683 UPDATE FA_MC_DEFERRED_DEPRN
684 SET event_id = l_event_id_tbl(l_count)
685 WHERE asset_id = l_asset_id_tbl(l_count)
686 AND corp_book_type_code = p_corp_book
687 AND tax_book_type_code = p_tax_book
688 AND corp_period_counter = p_corp_period_counter
689 AND tax_period_counter = p_tax_period_counter;
690
691 end loop; --End of loop for c_mc_deferred_events
692
693 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
694 fnd_log.string(G_LEVEL_STATEMENT,
695 G_MODULE_NAME||l_procedure_name,
696 'closing c_mc_deferred_events');
697 END IF;
698
699
700 CLOSE c_mc_deferred_events;
701
702 END LOOP; -- sob loop
703
704 commit;
705
706 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
707 fnd_log.string(G_LEVEL_PROCEDURE,
708 G_MODULE_NAME||l_procedure_name||'.end',
709 'End of procedure');
710 END IF;
711
712 EXCEPTION
713 when error_found then
714 rollback;
715 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
716 FND_LOG.STRING(G_LEVEL_EXCEPTION,
717 G_MODULE_NAME || l_procedure_name,
718 'errored');
719 END IF;
720 raise;
721
722 when others then
723 rollback;
724 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
725 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
726 fnd_message.set_token('ORACLE_ERR',SQLERRM);
727 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME || l_procedure_name,TRUE);
728 END IF;
729
730 raise;
731
732
733 end create_bulk_deferred_events;
734
735 --------------------------------------------------------------------------------
736
737 Procedure do_deferred (errbuf OUT NOCOPY VARCHAR2,
738 retcode OUT NOCOPY NUMBER,
739 p_tax_book_type_code IN varchar2,
740 p_tax_period_name IN varchar2,
741 p_corp_period_name IN varchar2) is
742
743 l_reporting_flag varchar2(1);
744 l_procedure_name varchar2(80) := 'do_deferred';
745
746 l_tax_period_counter number;
747 l_corp_period_counter number;
748
749 error_found exception;
750
751 CURSOR C_BOOKS (p_book_type_code varchar2)IS
752 SELECT 0,
753 set_of_books_id
754 FROM fa_book_controls
755 WHERE book_type_code = p_book_type_code
756 UNION ALL
757 SELECT 1, bcm.set_of_books_id
758 FROM fa_book_controls bc,
759 fa_mc_book_controls bcm
760 WHERE bc.book_type_code = p_book_type_code
761 AND bc.mc_source_flag = 'Y'
762 AND bcm.book_type_code = bc.book_type_code
763 AND bcm.primary_set_of_books_id = bc.set_of_books_id
764 AND bcm.enabled_flag = 'Y'
765 ORDER BY 1 DESC, 2; -- Process the reporting books first
766
767 BEGIN
768
769 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
770 fnd_log.string(G_LEVEL_PROCEDURE,
771 G_MODULE_NAME||l_procedure_name||'.begin',
772 'Beginning of procedure');
773 END IF;
774
775 if not fa_cache_pkg.fazcbc(X_book => p_tax_book_type_code) then
776 raise error_found;
777 end if;
778
779 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
780 fnd_log.string(G_LEVEL_STATEMENT,
781 G_MODULE_NAME||l_procedure_name,
782 'fetching period information');
783 END IF;
784
785 -- Convert period names to period counters
786 begin
787 select period_counter
788 into l_tax_period_counter
789 from fa_deprn_periods
790 where book_type_code = p_tax_book_type_code
791 and period_name = p_tax_period_name;
792
793 select period_counter
794 into l_corp_period_counter
795 from fa_deprn_periods
796 where book_type_code =
797 fa_cache_pkg.fazcbc_record.distribution_source_book
798 and period_name = p_corp_period_name;
799
800 exception
801 when others then
802 raise error_found;
803 end;
804
805 for c_rec in c_books (p_book_type_code => p_tax_book_type_code) loop
806
807 fnd_profile.put('GL_SET_OF_BKS_ID', c_rec.set_of_books_id);
808 fnd_client_info.set_currency_context (c_rec.set_of_books_id);
809
810 if not fa_cache_pkg.fazcsob
811 (X_set_of_books_id => c_rec.set_of_books_id,
812 X_mrc_sob_type_code => l_reporting_flag
813 ) then
814 raise error_found;
815 end if;
816
817 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
818 fnd_log.string(G_LEVEL_STATEMENT,
819 G_MODULE_NAME||l_procedure_name,
820 'calling deferred_deprn');
821 END IF;
822
823 deferred_deprn
824 (p_corp_book => fa_cache_pkg.fazcbc_record.distribution_source_book,
825 p_tax_book => p_tax_book_type_code,
826 p_corp_period_ctr => l_corp_period_counter,
827 p_tax_period_ctr => l_tax_period_counter,
828 p_mrc_sob_type_code => l_reporting_flag);
829
830 end loop;
831
832 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
833 fnd_log.string(G_LEVEL_STATEMENT,
834 G_MODULE_NAME||l_procedure_name,
835 'calling create_bulk_deferred_events');
836 END IF;
837
838 -- now process the events
839 create_bulk_deferred_events
840 (p_tax_book => p_tax_book_type_code,
841 p_corp_book => fa_cache_pkg.fazcbc_record.distribution_source_book,
842 p_tax_period_counter => l_tax_period_counter,
843 p_corp_period_counter => l_corp_period_counter);
844
845 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
846 fnd_log.string(G_LEVEL_PROCEDURE,
847 G_MODULE_NAME||l_procedure_name||'.end',
848 'End of procedure');
849 END IF;
850
851 retcode := 0;
852
853 EXCEPTION
854 WHEN error_found THEN
855 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
856 FND_LOG.STRING(G_LEVEL_EXCEPTION,
857 G_MODULE_NAME || l_procedure_name,
858 'errored');
859 END IF;
860 retcode := 2;
861
862 WHEN others THEN
863 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
864 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
865 fnd_message.set_token('ORACLE_ERR',SQLERRM);
866 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME || l_procedure_name,TRUE);
867 END IF;
868 retcode := 2;
869
870 end do_deferred;
871
872 END FA_DEFERRED_DEPRN_PKG;