[Home] [Help]
PACKAGE BODY: APPS.FA_DEFERRED_DEPRN_PKG
Source
1 PACKAGE BODY fa_deferred_deprn_pkg AS
2 /* $Header: FAXDEFB.pls 120.17.12020000.2 2012/07/19 11:23:14 mswetha 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 g_log_level_rec fa_api_types.log_level_rec_type;
20
21 TYPE number_tbl_type IS TABLE OF number INDEX BY BINARY_INTEGER;
22 TYPE char_tbl_type IS TABLE OF varchar2(30) INDEX BY BINARY_INTEGER;
23 TYPE rowid_tbl_type IS TABLE OF varchar2(64) INDEX BY BINARY_INTEGER;
24
25 ---------------------------------------------------
26 -- Declaration of local procedures and functions --
27 ---------------------------------------------------
28 --------------------------------------------------------------------------------
29 --
30 -- write_mesg
31 --
32 -- used to write messages which we want in both execution report and log
33 -- (i.e. DO NOT USE THIS FOR DEBUG!!!!)
34 --
35 --------------------------------------------------------------------------------
36
37
38 PROCEDURE Write_Mesg (p_calling_fn varchar2,
39 p_name varchar2,
40 p_token1 varchar2 DEFAULT NULL,
41 p_value1 varchar2 DEFAULT NULL,
42 p_token2 varchar2 DEFAULT NULL,
43 p_value2 varchar2 DEFAULT NULL,
44 p_mode varchar2 DEFAULT 'W') IS
45
46 l_string varchar2(2000);
47 l_encoded varchar2(1) := fnd_api.G_FALSE;
48
49 l_calling_fn varchar2(150);
50 l_mesg_count number := 0;
51
52 BEGIN
53
54 -- when error set the calling fn for log purposes
55 if (p_mode = 'E') then
56 l_calling_fn := p_calling_fn;
57 end if;
58
59 -- set up and dump to execution report
60 fnd_message.set_name('OFA', p_name);
61
62 if (p_token1 is not null) then
63 fnd_message.set_token(p_token1, p_value1);
64 end if;
65
66 if (p_token2 is not null) then
67 fnd_message.set_token(p_token2, p_value2);
68 end if;
69
70 l_string := fnd_message.get;
71 FND_FILE.put(FND_FILE.output,l_string);
72 FND_FILE.new_line(FND_FILE.output,1);
73
74 -- now add to log as well
75 fa_srvr_msg.add_message
76 (calling_fn => l_calling_fn,
77 name => p_name,
78 token1 => p_token1,
79 value1 => p_value1,
80 token2 => p_token2,
81 value2 => p_value2);
82
83 EXCEPTION
84 WHEN OTHERS THEN
85 null;
86
87 END Write_Mesg;
88
89 --------------------------------------------------------------------------------
90 --
91 -- write_errmsg_log
92 --
93 -- dump messages (and debug) to the log
94 -- this will work for the gl tieback as well and show at the end of Gl's logfile
95 --
96 --------------------------------------------------------------------------------
97
98
99 PROCEDURE Write_ErrMsg_Log IS
100
101 l_encoded varchar2(1) := fnd_api.G_FALSE;
102 l_mesg_count number := 0;
103 l_calling_fn varchar2(60) := 'fa_journals_pkg.write_errmsg_log';
104 BEGIN
105
106 l_mesg_count := fnd_msg_pub.count_msg;
107
108 if (l_mesg_count > 0) then
109 fnd_file.put(FND_FILE.LOG,
110 fnd_msg_pub.get(fnd_msg_pub.G_FIRST, l_encoded));
111 fnd_file.new_line(FND_FILE.LOG,1);
112
113 for i in 1..(l_mesg_count-1) loop
114 fnd_file.put(FND_FILE.LOG,
115 fnd_msg_pub.get(fnd_msg_pub.G_NEXT, l_encoded));
116 fnd_file.new_line(FND_FILE.LOG,1);
117 end loop;
118
119 end if;
120
121 EXCEPTION
122 WHEN OTHERS THEN
123 fa_srvr_msg.add_sql_error (calling_fn => l_calling_fn);
124
125 END Write_ErrMsg_Log;
126
127 --------------------------------------------------------------------------------
128
129 Procedure deferred_deprn (p_corp_book varchar2,
130 p_tax_book varchar2,
131 p_corp_period_ctr number,
132 p_tax_period_ctr number,
133 p_mrc_sob_type_code varchar2,
134 p_set_of_books_id number) is
135
136 l_batch_size number;
137 l_procedure_name varchar2(80) := 'FA_DEFERRED_PKG.deferred_deprn';
138 l_calling_fn varchar2(80) := 'FA_DEFERRED_PKG.deferred_deprn';
139
140 error_found EXCEPTION;
141
142 l_corp_sob_id number_tbl_type;
143 l_tax_sob_id number_tbl_type;
144 l_sob_id number_tbl_type;
145 l_asset_id number_tbl_type;
146 l_rowid_tbl rowid_tbl_type;
147 l_dist_id number_tbl_type;
148 l_dh_ccid number_tbl_type;
149 l_corp_deprn number_tbl_type;
150 l_tax_deprn number_tbl_type;
151 l_corp_dd_period_counter number_tbl_type;
152 l_tax_dd_period_counter number_tbl_type;
153 l_rsv_adj number_tbl_type;
154 l_book varchar2(30);
155 l_book_class varchar2(15);
156 l_prior_period_tfr number_tbl_type;
157
158 CURSOR c_mrc_deferred is
159 SELECT corp_bc.set_of_books_id,
160 tax_bc.set_of_books_id,
161 DH.Asset_ID,
162 DH.Distribution_ID,
163 DH.Code_Combination_ID,
164 nvl(CORP_DD.Deprn_Amount, 0),
165 nvl(TAX_DD.Deprn_Amount, 0),
166 nvl(CORP_DD.Period_Counter, -1),
167 nvl(TAX_DD.Period_Counter, -1)
168 FROM FA_DISTRIBUTION_HISTORY DH,
169 FA_ASSET_HISTORY AH,
170 FA_MC_DEPRN_PERIODS DP,
171 FA_MC_DEPRN_DETAIL CORP_DD,
172 FA_MC_DEPRN_DETAIL TAX_DD,
173 fa_mc_book_controls corp_bc,
174 fa_mc_book_controls tax_bc
175 WHERE DH.Book_Type_Code = p_corp_book
176 AND DP.Period_Counter = p_tax_period_ctr
177 AND DP.Book_Type_Code = p_tax_book
178 AND DP.set_of_books_id = p_set_of_books_id
179 AND AH.Asset_ID = DH.Asset_ID
180 AND AH.Date_Effective < DP.Period_Close_Date
181 AND DP.Period_Close_Date <= NVL(AH.Date_Ineffective,
182 DP.Period_Close_Date)
183 AND AH.Asset_Type in ('CAPITALIZED', 'GROUP')
184 AND CORP_BC.book_type_code = p_corp_book
185 AND CORP_BC.set_of_books_id = p_set_of_books_id
186 AND TAX_BC.book_type_code = p_tax_book
187 AND TAX_BC.set_of_books_id = p_set_of_books_id
188 AND CORP_DD.Book_Type_Code (+) = p_corp_book
189 AND CORP_DD.set_of_books_id(+) = p_set_of_books_id
190 AND CORP_DD.Period_Counter (+) = p_tax_period_ctr
191 AND CORP_DD.Distribution_ID (+) = DH.Distribution_ID
192 AND CORP_DD.Deprn_Source_Code (+) <> 'T'
193 AND TAX_DD.Book_Type_Code (+) = p_tax_book
194 AND TAX_DD.set_of_books_id(+) = p_set_of_books_id
195 AND TAX_DD.Period_Counter (+) = p_tax_period_ctr
196 AND TAX_DD.Distribution_ID (+) = DH.Distribution_ID
197 AND TAX_DD.Deprn_Source_Code (+) <> 'T'
198 AND TAX_DD.asset_id(+) = DH.asset_id
199 AND (TAX_DD.asset_id is not null OR CORP_DD.asset_id is not null)
200 AND exists
201 (select 1
202 from fa_books bk
203 where bk.asset_id = DH.asset_id
204 and bk.book_type_code = p_tax_book
205 and bk.transaction_header_id_out is null)
206 ORDER BY DH.Asset_ID,
207 DH.Distribution_ID,
208 DH.Code_Combination_ID;
209
210 CURSOR c_deferred IS
211 SELECT DH.Asset_ID,
212 DH.Distribution_ID,
213 DH.Code_Combination_ID,
214 nvl(CORP_DD.Deprn_Amount, 0),
215 nvl(TAX_DD.Deprn_Amount, 0),
216 nvl(CORP_DD.Period_Counter, -1),
217 nvl(TAX_DD.Period_Counter, -1)
218 FROM FA_DISTRIBUTION_HISTORY DH,
219 FA_ASSET_HISTORY AH,
220 FA_DEPRN_PERIODS DP,
221 FA_DEPRN_DETAIL CORP_DD,
222 FA_DEPRN_DETAIL TAX_DD
223 WHERE DH.Book_Type_Code = p_corp_book
224 AND DP.Period_Counter = p_tax_period_ctr
225 AND DP.Book_Type_Code = p_tax_book
226 AND AH.Asset_ID = DH.Asset_ID
227 AND AH.Date_Effective < DP.Period_Close_Date
228 AND DP.Period_Close_Date <= NVL(AH.Date_Ineffective,
229 DP.Period_Close_Date)
230 AND AH.Asset_Type in ('CAPITALIZED', 'GROUP')
231 AND CORP_DD.Book_Type_Code (+) = p_corp_book
232 AND CORP_DD.Period_Counter (+) = p_tax_period_ctr
233 AND CORP_DD.Distribution_ID (+) = DH.Distribution_ID
234 AND CORP_DD.Deprn_Source_Code (+) <> 'T'
235 AND TAX_DD.Book_Type_Code (+) = p_tax_book
236 AND TAX_DD.Period_Counter (+) = p_tax_period_ctr
237 AND TAX_DD.Distribution_ID (+) = DH.Distribution_ID
238 AND TAX_DD.Deprn_Source_Code (+) <> 'T'
239 AND TAX_DD.asset_id(+) = DH.asset_id
240 AND (TAX_DD.asset_id is not null OR CORP_DD.asset_id is not null)
241 AND exists
242 (select 1
243 from fa_books bk
244 where bk.asset_id = DH.asset_id
245 and bk.book_type_code = p_tax_book
246 and bk.transaction_header_id_out is null)
247 ORDER BY DH.Asset_ID,
248 DH.Distribution_ID,
249 DH.Code_Combination_ID;
250
251
252 CURSOR c_deferred_adj(p_book varchar2,
253 p_period_ctr number,
254 p_book_class varchar2) is
255 SELECT asset_id,
256 distribution_id,
257 nvl(sum(nvl(decode(p_book_class,
258 'TAX', decode(AJ.debit_credit_flag,
259 'CR', 1, -1),
260 decode(AJ.debit_credit_flag,
261 'DR', 1, -1)) *
262 AJ.Adjustment_Amount, 0)), 0)
263 from fa_adjustments AJ
264 WHERE AJ.Book_Type_Code (+) = p_book
265 AND AJ.Period_Counter_Created (+) = p_period_ctr
266 AND nvl(AJ.Adjustment_Amount,-9999) <> 0
267 AND AJ.Adjustment_Type (+) = 'RESERVE'
268 AND nvl(AJ.Track_Member_Flag,'N') = 'N'
269 AND exists
270 (select 1
271 from fa_deferred_deprn def
272 where def.asset_id = AJ.asset_id
273 and def.corp_book_type_code = p_corp_book
274 and def.tax_book_type_code = p_tax_book
275 and def.corp_period_counter = p_corp_period_ctr
276 and def.tax_period_counter = p_tax_period_ctr)
277 GROUP BY asset_id,
278 distribution_id;
279
280 CURSOR c_mrc_deferred_adj(p_book varchar2,
281 p_period_ctr number,
282 p_book_class varchar2) IS
283 SELECT set_of_books_id,
284 asset_id,
285 distribution_id,
286 nvl(sum(nvl(decode(p_book_class,
287 'TAX', decode(AJ.debit_credit_flag,
288 'CR', 1, -1),
289 decode(AJ.debit_credit_flag,
290 'DR', 1, -1)) *
291 AJ.Adjustment_Amount, 0)), 0)
292 from fa_mc_adjustments AJ
293 WHERE AJ.Book_Type_Code (+) = p_book
294 AND AJ.Period_Counter_Created (+) = p_period_ctr
295 AND AJ.set_of_books_id = p_set_of_books_id
296 AND nvl(AJ.Adjustment_Amount,-9999) <> 0
297 AND AJ.Adjustment_Type (+) = 'RESERVE'
298 AND nvl(AJ.Track_Member_Flag,'N') = 'N'
299 AND exists
300 (select 1
301 from fa_deferred_deprn def
302 where def.asset_id = AJ.asset_id
303 and def.corp_book_type_code = p_corp_book
304 and def.tax_book_type_code = p_tax_book
305 and def.corp_period_counter = p_corp_period_ctr
306 and def.tax_period_counter = p_tax_period_ctr)
307 GROUP BY set_of_books_id,
308 asset_id,
309 distribution_id;
310
311 begin
312
313 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
314 fnd_log.string(G_LEVEL_PROCEDURE,
315 G_MODULE_NAME||l_procedure_name||'.begin',
316 'Beginning of procedure');
317 END IF;
318
319 -- call the book_controls cache
320 if NOT fa_cache_pkg.fazcbc(X_book => p_tax_book) then
321 raise error_found;
322 end if;
323
324 l_batch_size := nvl(fa_cache_pkg.fa_batch_size, 200);
325
326
327
328 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
329 fnd_log.string(G_LEVEL_STATEMENT,
330 G_MODULE_NAME||l_procedure_name,
331 'opening ' || p_mrc_sob_type_code || ' deferred cursor');
332 END IF;
333
334 -- mrc loop
335 if (p_mrc_sob_type_code = 'R') then
336 OPEN C_MRC_DEFERRED;
337 else
338 OPEN C_DEFERRED;
339 end if;
340
341 while (TRUE) loop
342
343 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
344 fnd_log.string(G_LEVEL_STATEMENT,
345 G_MODULE_NAME||l_procedure_name,
346 'fetching ' || p_mrc_sob_type_code || ' cursor');
347 END IF;
348
349 if (p_mrc_sob_type_code = 'R') then
350 FETCH C_MRC_DEFERRED BULK COLLECT INTO
351 l_corp_sob_id,
352 l_tax_sob_id,
353 l_asset_id,
354 l_dist_id,
355 l_dh_ccid,
356 l_corp_deprn,
357 l_tax_deprn,
358 l_corp_dd_period_counter,
359 l_tax_dd_period_counter
360 LIMIT l_batch_size;
361 else
362 FETCH C_DEFERRED BULK COLLECT INTO
363 l_asset_id,
364 l_dist_id,
365 l_dh_ccid,
366 l_corp_deprn,
367 l_tax_deprn,
368 l_corp_dd_period_counter,
369 l_tax_dd_period_counter
370 LIMIT l_batch_size;
371 end if;
372
373 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
374 fnd_log.string(G_LEVEL_STATEMENT,
375 G_MODULE_NAME||l_procedure_name,
376 'rows fetched: ' || to_char(l_asset_id.count));
377 END IF;
378
379 if (l_asset_id.count = 0) then
380
381 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
382 fnd_log.string(G_LEVEL_STATEMENT,
383 G_MODULE_NAME||l_procedure_name,
384 'exiting loop');
385 END IF;
386
387 exit;
388 end if;
389
390
391 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
392 fnd_log.string(G_LEVEL_STATEMENT,
393 G_MODULE_NAME||l_procedure_name,
394 'bulk inserting into fa_deferred_deprn*' || p_mrc_sob_type_code);
395 END IF;
396
397 if (p_mrc_sob_type_code = 'R') then
398
399 FORALL i in 1..l_asset_id.count
400 INSERT INTO FA_MC_DEFERRED_DEPRN
401 (set_of_books_id,
402 corp_book_type_code,
403 tax_book_type_code,
404 asset_id,
405 distribution_id,
406 deferred_deprn_expense_ccid,
407 deferred_deprn_reserve_ccid,
408 deferred_deprn_expense_amount,
409 deferred_deprn_reserve_amount,
410 corp_period_counter,
411 tax_period_counter,
412 expense_je_line_num,
413 reserve_je_line_num)
414 values
415 (l_tax_sob_id(i),
416 p_corp_book,
417 p_tax_book,
418 l_asset_id(i),
419 l_dist_id(i),
420 null, -- l_deferred_deprn_expense_ccid,
421 null, -- l_deferred_deprn_reserve_ccid,
422 (l_tax_deprn(i) - l_corp_deprn(i)),
423 (l_tax_deprn(i) - l_corp_deprn(i)),
424 p_corp_period_ctr,
425 p_tax_period_ctr,
426 0,
427 0);
428 else
429 FORALL i in 1..l_asset_id.count
430 INSERT INTO FA_DEFERRED_DEPRN
431 (corp_book_type_code,
432 tax_book_type_code,
433 asset_id,
434 distribution_id,
435 deferred_deprn_expense_ccid,
436 deferred_deprn_reserve_ccid,
437 deferred_deprn_expense_amount,
438 deferred_deprn_reserve_amount,
439 corp_period_counter,
440 tax_period_counter,
441 expense_je_line_num,
442 reserve_je_line_num)
443 values
444 (p_corp_book,
445 p_tax_book,
446 l_asset_id(i),
447 l_dist_id(i),
448 null, -- l_deferred_deprn_expense_ccid,
449 null, -- l_deferred_deprn_reserve_ccid,
450 (l_tax_deprn(i) - l_corp_deprn(i)),
451 (l_tax_deprn(i) - l_corp_deprn(i)),
452 p_corp_period_ctr,
453 p_tax_period_ctr,
454 0,
455 0);
456 end if;
457
458 l_corp_sob_id.delete;
459 l_tax_sob_id.delete;
460 l_asset_id.delete;
461 l_dist_id.delete;
462 l_dh_ccid.delete;
463 l_corp_deprn.delete;
464 l_tax_deprn.delete;
465 l_corp_dd_period_counter.delete;
466 l_tax_dd_period_counter.delete;
467
468 end loop;
469
470 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
471 fnd_log.string(G_LEVEL_STATEMENT,
472 G_MODULE_NAME||l_procedure_name,
473 'closing ' || p_mrc_sob_type_code || ' deferred cursor');
474 END IF;
475
476 if (p_mrc_sob_type_code = 'R') then
477 CLOSE C_MRC_DEFERRED;
478 else
479 CLOSE C_DEFERRED;
480 end if;
481
482
483 -------------------------------------
484 -- process the corp/tax adjustments
485 -------------------------------------
486
487 for x in 1..2 loop
488
489 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
490 fnd_log.string(G_LEVEL_STATEMENT,
491 G_MODULE_NAME||l_procedure_name,
492 'in ADJ loop, interation #:' || to_char(x));
493 END IF;
494
495 if (x = 1) then
496 l_book_class := 'CORP';
497 l_book := p_corp_book;
498 else
499 l_book_class := 'TAX';
500 l_book := p_tax_book;
501 end if;
502
503 if (p_mrc_sob_type_code = 'R') then
504 OPEN C_MRC_DEFERRED_ADJ(l_book, p_tax_period_ctr, l_book_class);
505 else
506 OPEN C_DEFERRED_ADJ(l_book, p_tax_period_ctr, l_book_class);
507 end if;
508
509 while (TRUE) loop
510
511 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
512 fnd_log.string(G_LEVEL_STATEMENT,
513 G_MODULE_NAME||l_procedure_name,
514 'fetching ' || p_mrc_sob_type_code || ' ADJ cursor');
515 END IF;
516
517 if (p_mrc_sob_type_code = 'R') then
518 FETCH C_MRC_DEFERRED_ADJ BULK COLLECT INTO
519 l_sob_id,
520 l_asset_id,
521 l_dist_id,
522 l_rsv_adj
523 LIMIT l_batch_size;
524 else
525 FETCH C_DEFERRED_ADJ BULK COLLECT INTO
526 l_asset_id,
527 l_dist_id,
528 l_rsv_adj
529 LIMIT l_batch_size;
530 end if;
531
532 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
533 fnd_log.string(G_LEVEL_STATEMENT,
534 G_MODULE_NAME||l_procedure_name,
535 'rows fetched: ' || to_char(l_asset_id.count));
536 END IF;
537
538 if (l_asset_id.count = 0) then
539
540 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
541 fnd_log.string(G_LEVEL_STATEMENT,
542 G_MODULE_NAME||l_procedure_name,
543 'exiting loop');
544 END IF;
545
546 exit;
547 end if;
548
549 /*
550 for i in 1..l_asset_id.count loop
551
552 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
553 fnd_log.string(G_LEVEL_STATEMENT,
554 G_MODULE_NAME||l_procedure_name,
555 to_char(l_dist_id(i)) || ': ' ||
556 to_char(l_corp_rsv_adj(i)));
557 END IF;
558
559 end loop;
560 */
561
562
563 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
564 fnd_log.string(G_LEVEL_STATEMENT,
565 G_MODULE_NAME||l_procedure_name,
566 'bulk updating fa_deferred_deprn for corp adj' || p_mrc_sob_type_code);
567 END IF;
568
569
570 if (p_mrc_sob_type_code = 'R') then
571
572 FORALL i in 1..l_asset_id.count
573 UPDATE FA_MC_DEFERRED_DEPRN
574 SET deferred_deprn_expense_amount = deferred_deprn_expense_amount + l_rsv_adj(i),
575 deferred_deprn_reserve_amount = deferred_deprn_reserve_amount + l_rsv_adj(i)
576 WHERE set_of_books_id = l_tax_sob_id(i)
577 AND corp_book_type_code = p_corp_book
578 AND tax_book_type_code = p_tax_book
579 AND corp_period_counter = p_corp_period_ctr
580 AND tax_period_counter = p_tax_period_ctr
581 AND asset_id = l_asset_id(i)
582 AND distribution_id = l_dist_id(i);
583
584 else
585
586 FORALL i in 1..l_asset_id.count
587 UPDATE FA_DEFERRED_DEPRN
588 SET deferred_deprn_expense_amount = deferred_deprn_expense_amount + l_rsv_adj(i),
589 deferred_deprn_reserve_amount = deferred_deprn_reserve_amount + l_rsv_adj(i)
590 WHERE corp_book_type_code = p_corp_book
591 AND tax_book_type_code = p_tax_book
592 AND corp_period_counter = p_corp_period_ctr
593 AND tax_period_counter = p_tax_period_ctr
594 AND asset_id = l_asset_id(i)
595 AND distribution_id = l_dist_id(i);
596
597 end if;
598
599 l_sob_id.delete;
600 l_asset_id.delete;
601 l_dist_id.delete;
602 l_rsv_adj.delete;
603
604 end loop;
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 'closing ' || p_mrc_sob_type_code || ' ADJ cursor');
610 END IF;
611
612 if (p_mrc_sob_type_code = 'R') then
613 CLOSE C_MRC_DEFERRED_ADJ;
614 else
615 CLOSE C_DEFERRED_ADJ;
616 end if;
617
618 end loop; -- corp/tax loop
619
620 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
621 fnd_log.string(G_LEVEL_PROCEDURE,
622 G_MODULE_NAME||l_procedure_name||'.end',
623 'End of procedure');
624 END IF;
625
626 EXCEPTION
627 WHEN error_found THEN
628 fa_srvr_msg.add_message(calling_fn => l_calling_fn);
629 raise;
630
631
632 WHEN others THEN
633 if c_mrc_deferred%ISOPEN then
634 close c_mrc_deferred;
635 end if;
636
637 if c_deferred%ISOPEN then
638 close c_deferred;
639 end if;
640
641 if c_mrc_deferred_adj%ISOPEN then
642 close c_mrc_deferred_adj;
643 end if;
644
645 if c_deferred_adj%ISOPEN then
646 close c_deferred_adj;
647 end if;
648
649 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn);
650 raise;
651
652 end deferred_deprn;
653
654 --------------------------------------------------------------------------------
655
656 procedure create_bulk_deferred_events (
657 p_tax_book IN VARCHAR2,
658 p_corp_book IN VARCHAR2,
659 p_tax_period_counter IN NUMBER,
660 p_corp_period_counter IN NUMBER
661 ) IS
662
663 l_batch_size number;
664 l_period_rec fa_api_types.period_rec_type;
665
666 error_found EXCEPTION;
667
668 l_asset_id_tbl FA_XLA_EVENTS_PVT.number_tbl_type;
669 l_rowid_tbl rowid_tbl_type;
670 l_event_id_tbl FA_XLA_EVENTS_PVT.number_tbl_type;
671
672 l_sob_tbl FA_CACHE_PKG.fazcrsob_sob_tbl_type;
673
674 cursor c_deferred_events is
675 select asset_id, min(rowid)
676 from fa_deferred_deprn
677 where corp_book_type_code = p_corp_book
678 and tax_book_type_code = p_tax_book
679 and corp_period_counter = p_corp_period_counter
680 and tax_period_counter = p_tax_period_counter
681 and event_id is null
682 group by asset_id;
683
684 cursor c_mc_deferred_events (p_set_of_books_id number) is
685 select asset_id, min(rowid)
686 from fa_mc_deferred_deprn
687 where corp_book_type_code = p_corp_book
688 and tax_book_type_code = p_tax_book
689 and corp_period_counter = p_corp_period_counter
690 and tax_period_counter = p_tax_period_counter
691 and set_of_books_id = p_set_of_books_id
692 and event_id is null
693 group by asset_id;
694
695 -- Bugfix 6122229: Increased the length from varchar2(35) to varchar2(80)
696 -- for variable l_calling_fn
697 l_calling_fn varchar2(80) := 'FA_DEFERRED_PKG.create_bulk_deprn_events';
698 l_procedure_name varchar2(80) := 'FA_DEFERRED_PKG.create_bulk_deprn_events';
699
700 --Bug6122229
701 --Initializing the second count variable
702 l_count2 number := 1 ;
703
704 begin
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||'.begin',
709 'Beginning of procedure');
710 END IF;
711
712 l_batch_size := nvl(fa_cache_pkg.fa_batch_size, 200);
713
714 if not FA_UTIL_PVT.get_period_rec
715 (p_book => p_corp_book,
716 p_period_counter => p_corp_period_counter,
717 x_period_rec => l_period_rec,
718 p_log_level_rec => g_log_level_rec
719 ) then
720 raise error_found;
721 end if;
722
723 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
724 fnd_log.string(G_LEVEL_STATEMENT,
725 G_MODULE_NAME||l_procedure_name,
726 'opening c_deferred_events');
727 END IF;
728
729 if not fa_cache_pkg.fazcbc(X_book => p_corp_book) then
730 raise error_found;
731 end if;
732
733 open c_deferred_events;
734
735 loop -- Loop for c_deferred_events
736
737 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
738 fnd_log.string(G_LEVEL_STATEMENT,
739 G_MODULE_NAME||l_procedure_name,
740 'bulk fetching c_deferred_events cursor');
741 END IF;
742
743 fetch c_deferred_events bulk collect
744 into l_asset_id_tbl,
745 l_rowid_tbl
746 LIMIT l_batch_size;
747
748 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
749 fnd_log.string(G_LEVEL_STATEMENT,
750 G_MODULE_NAME||l_procedure_name,
751 'rows fetched: ' || to_char(l_asset_id_tbl.count));
752 END IF;
753
754 if l_asset_id_tbl.count = 0 then
755 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
756 fnd_log.string(G_LEVEL_STATEMENT,
757 G_MODULE_NAME||l_procedure_name,
758 'exiting loop...');
759 END IF;
760
761 exit;
762 end if;
763
764 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
765 fnd_log.string(G_LEVEL_STATEMENT,
766 G_MODULE_NAME||l_procedure_name,
767 'calling FA_XLA_EVENTS_PVT.create_bulk_deferred_event for primary');
768 END IF;
769
770 -- call bulk event api
771 FA_XLA_EVENTS_PVT.create_bulk_deferred_event
772 (p_asset_id_tbl => l_asset_id_tbl,
773 p_corp_book => p_corp_book,
774 p_tax_book => p_tax_book,
775 p_corp_period_counter => p_corp_period_counter,
776 p_tax_period_counter => p_tax_period_counter,
777 p_period_close_date => l_period_rec.calendar_period_close_date,
778 -- p_legal_entity => null,
779 p_entity_type_code => 'DEFERRED_DEPRECIATION',
780 x_event_id_tbl => l_event_id_tbl,
781 p_calling_fn => l_calling_fn
782 );
783
784 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
785 fnd_log.string(G_LEVEL_STATEMENT,
786 G_MODULE_NAME||l_procedure_name,
787 'bulk inserting into fa_deferred_deprn_events');
788 END IF;
789
790 FORALL l_count in 1..l_asset_id_tbl.count
791 INSERT into fa_deferred_deprn_events
792 (asset_id ,
793 corp_book_type_code ,
794 tax_book_type_code ,
795 corp_period_counter ,
796 tax_period_counter ,
797 event_id
798 )
799 VALUES
800 (l_asset_id_tbl(l_count),
801 p_corp_book,
802 p_tax_book,
803 p_corp_period_counter,
804 p_tax_period_counter,
805 l_event_id_tbl(l_count));
806
807 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
808 fnd_log.string(G_LEVEL_STATEMENT,
809 G_MODULE_NAME||l_procedure_name,
810 'bulk updating fa_deferred_deprn with event ids');
811 END IF;
812
813 FORALL l_count in 1..l_asset_id_tbl.count
814 update fa_deferred_deprn
815 set event_id = l_event_id_tbl(l_count)
816 where asset_id = l_asset_id_tbl(l_count)
817 AND corp_book_type_code = p_corp_book
818 AND tax_book_type_code = p_tax_book
819 AND corp_period_counter = p_corp_period_counter
820 AND tax_period_counter = p_tax_period_counter;
821
822 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
823 fnd_log.string(G_LEVEL_STATEMENT,
824 G_MODULE_NAME||l_procedure_name,
825 'bulk updating fa_mc_deferred_deprn with event ids');
826 END IF;
827
828 -- now process all matching mrc rows
829 FORALL l_count in 1..l_asset_id_tbl.count
830 update fa_mc_deferred_deprn
831 SET event_id = l_event_id_tbl(l_count)
832 WHERE asset_id = l_asset_id_tbl(l_count)
833 AND corp_book_type_code = p_corp_book
834 AND tax_book_type_code = p_tax_book
835 AND corp_period_counter = p_corp_period_counter
836 AND tax_period_counter = p_tax_period_counter;
837
838 delete from xla_events_int_gt;
839
840 end loop; --End of loop for c_deferred_events
841
842 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
843 fnd_log.string(G_LEVEL_STATEMENT,
844 G_MODULE_NAME||l_procedure_name,
845 'closing c_deferred_events');
846 END IF;
847
848 close c_deferred_events;
849
850 -- now find any mrc rows which are not processed yet and update
851 if not FA_CACHE_PKG.fazcrsob
852 (x_book_type_code => p_corp_book,
853 x_sob_tbl => l_sob_tbl) then
854 raise error_found;
855 end if;
856
857 -- begin at index of 1 not 0 as in apis
858 FOR l_sob_index in 1..l_sob_tbl.count LOOP
859
860 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
861 fnd_log.string(G_LEVEL_STATEMENT,
862 G_MODULE_NAME||l_procedure_name,
863 'opening c_mc_deferred_events cursor');
864 END IF;
865
866 OPEN c_mc_deferred_events(p_set_of_books_id => l_sob_tbl(l_sob_index));
867
868 loop -- Loop for c_mc_deferred_events
869
870 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
871 fnd_log.string(G_LEVEL_STATEMENT,
872 G_MODULE_NAME||l_procedure_name,
873 'fetching c_mc_deferred_events cursor');
874 END IF;
875
876 FETCH c_mc_deferred_events bulk collect
877 into l_asset_id_tbl,
878 l_rowid_tbl
879 LIMIT l_batch_size;
880
881 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
882 fnd_log.string(G_LEVEL_STATEMENT,
883 G_MODULE_NAME||l_procedure_name,
884 'rows fetched: ' || to_char(l_asset_id_tbl.count));
885 END IF;
886
887 if (l_asset_id_tbl.count = 0) then
888 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
889 fnd_log.string(G_LEVEL_STATEMENT,
890 G_MODULE_NAME||l_procedure_name,
891 'exiting loop...');
892 END IF;
893
894 exit;
895 end if;
896
897 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
898 fnd_log.string(G_LEVEL_STATEMENT,
899 G_MODULE_NAME||l_procedure_name,
900 'calling FA_XLA_EVENTS_PVT.create_bulk_deferred_event for reporting');
901 END IF;
902
903 -- call bulk event api
904 FA_XLA_EVENTS_PVT.create_bulk_deferred_event
905 (p_asset_id_tbl => l_asset_id_tbl,
906 p_corp_book => p_corp_book,
907 p_tax_book => p_tax_book,
908 p_corp_period_counter => p_corp_period_counter,
909 p_tax_period_counter => p_tax_period_counter,
910 p_period_close_date => l_period_rec.calendar_period_close_date,
911 -- p_legal_entity => px_max_legal_entity_id,
912 p_entity_type_code => 'DEFERRED_DEPRECIATION',
913 x_event_id_tbl => l_event_id_tbl,
914 p_calling_fn => l_calling_fn
915 );
916
917 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
918 fnd_log.string(G_LEVEL_STATEMENT,
919 G_MODULE_NAME||l_procedure_name,
920 'bulk inserting into fa_deferred_deprn_events');
921 END IF;
922
923 FORALL l_count in 1..l_asset_id_tbl.count
924 INSERT into fa_deferred_deprn_events
925 (asset_id ,
926 corp_book_type_code ,
927 tax_book_type_code ,
928 corp_period_counter ,
929 tax_period_counter ,
930 event_id
931 )
932 VALUES
933 (l_asset_id_tbl(l_count),
934 p_corp_book,
935 p_tax_book,
936 p_corp_period_counter,
937 p_tax_period_counter,
938 l_event_id_tbl(l_count));
939
940 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
941 fnd_log.string(G_LEVEL_STATEMENT,
942 G_MODULE_NAME||l_procedure_name,
943 'bulk updating fa_mc_deferred_deprn with event ids');
944 END IF;
945
946 FORALL l_count in 1..l_asset_id_tbl.count
947 UPDATE FA_MC_DEFERRED_DEPRN
948 SET event_id = l_event_id_tbl(l_count)
949 WHERE asset_id = l_asset_id_tbl(l_count)
950 AND corp_book_type_code = p_corp_book
951 AND tax_book_type_code = p_tax_book
952 AND corp_period_counter = p_corp_period_counter
953 AND tax_period_counter = p_tax_period_counter;
954
955 delete from xla_events_int_gt;
956
957 end loop; --End of loop for c_mc_deferred_events
958
959 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
960 fnd_log.string(G_LEVEL_STATEMENT,
961 G_MODULE_NAME||l_procedure_name,
962 'closing c_mc_deferred_events');
963 END IF;
964
965
966 CLOSE c_mc_deferred_events;
967
968 END LOOP; -- sob loop
969
970 commit;
971
972 if not fa_cache_pkg.fazcbc(X_book => p_tax_book) then
973 raise error_found;
974 end if;
975
976 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
977 fnd_log.string(G_LEVEL_PROCEDURE,
978 G_MODULE_NAME||l_procedure_name||'.end',
979 'End of procedure');
980 END IF;
981
982 EXCEPTION
983 when error_found then
984 rollback;
985 fa_srvr_msg.add_message(calling_fn => l_calling_fn);
986 raise;
987
988 when others then
989 if c_deferred_events%ISOPEN then
990 close c_deferred_events;
991 end if;
992
993 if c_mc_deferred_events%ISOPEN then
994 close c_mc_deferred_events;
995 end if;
996
997 rollback;
998 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn);
999 raise;
1000
1001 end create_bulk_deferred_events;
1002
1003 --------------------------------------------------------------------------------
1004
1005 Procedure do_deferred (errbuf OUT NOCOPY VARCHAR2,
1006 retcode OUT NOCOPY NUMBER,
1007 p_tax_book_type_code IN varchar2,
1008 p_tax_period_name IN varchar2,
1009 p_corp_period_name IN varchar2) is
1010
1011 l_reporting_flag varchar2(1);
1012 l_procedure_name varchar2(80) := 'do_deferred';
1013 l_calling_fn varchar2(80) := 'FA_DEFERRED_PKG.do_deferred';
1014
1015 l_tax_period_counter number;
1016 l_corp_period_counter number;
1017
1018 error_found exception;
1019
1020 l_deferred_exists_count number;
1021
1022
1023 CURSOR C_BOOKS (p_book_type_code varchar2)IS
1024 SELECT 0,
1025 set_of_books_id
1026 FROM fa_book_controls
1027 WHERE book_type_code = p_book_type_code
1028 UNION ALL
1029 SELECT 1, bcm.set_of_books_id
1030 FROM fa_book_controls bc,
1031 fa_mc_book_controls bcm
1032 WHERE bc.book_type_code = p_book_type_code
1033 AND bc.mc_source_flag = 'Y'
1034 AND bcm.book_type_code = bc.book_type_code
1035 AND bcm.primary_set_of_books_id = bc.set_of_books_id
1036 AND bcm.enabled_flag = 'Y'
1037 ORDER BY 1 DESC, 2; -- Process the reporting books first
1038
1039 BEGIN
1040
1041 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1042 fnd_log.string(G_LEVEL_PROCEDURE,
1043 G_MODULE_NAME||l_procedure_name||'.begin',
1044 'Beginning of procedure');
1045 END IF;
1046
1047 if not fa_cache_pkg.fazcbc(X_book => p_tax_book_type_code) then
1048 raise error_found;
1049 end if;
1050
1051 if (not g_log_level_rec.initialized) then
1052 if (NOT fa_util_pub.get_log_level_rec (
1053 x_log_level_rec => g_log_level_rec
1054 )) then
1055 raise error_found;
1056 end if;
1057 end if;
1058
1059 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1060 fnd_log.string(G_LEVEL_STATEMENT,
1061 G_MODULE_NAME||l_procedure_name,
1062 'fetching period information');
1063 END IF;
1064
1065 -- Convert period names to period counters
1066 begin
1067 select period_counter
1068 into l_tax_period_counter
1069 from fa_deprn_periods
1070 where book_type_code = p_tax_book_type_code
1071 and period_name = p_tax_period_name;
1072
1073 select period_counter
1074 into l_corp_period_counter
1075 from fa_deprn_periods
1076 where book_type_code =
1077 fa_cache_pkg.fazcbc_record.distribution_source_book
1078 and period_name = p_corp_period_name;
1079
1080 exception
1081 when others then
1082 raise error_found;
1083 end;
1084
1085 -- BUG# 8393653
1086 -- exit if already run
1087 -- no partial as commit is at end of event processing
1088
1089 begin
1090
1091 select 1
1092 into l_deferred_exists_count
1093 from fa_deferred_deprn
1094 where tax_book_type_code = p_tax_book_type_code
1095 and corp_book_type_code =
1096 fa_cache_pkg.fazcbc_record.distribution_source_book
1097 and tax_period_counter = l_tax_period_counter
1098 and corp_period_counter = l_tax_period_counter
1099 and rownum = 1;
1100
1101 exception
1102 when no_data_found then
1103 l_deferred_exists_count := 0;
1104 end;
1105
1106 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1107 fnd_log.string(G_LEVEL_STATEMENT,
1108 G_MODULE_NAME||l_procedure_name,
1109 'l_deferred_exists_count :' || to_char(l_deferred_exists_count));
1110 END IF;
1111
1112 if (l_deferred_exists_count <> 0) then
1113 return;
1114 end if;
1115
1116 for c_rec in c_books (p_book_type_code => p_tax_book_type_code) loop
1117
1118 fnd_profile.put('GL_SET_OF_BKS_ID', c_rec.set_of_books_id);
1119 fnd_client_info.set_currency_context (c_rec.set_of_books_id);
1120
1121 if not fa_cache_pkg.fazcsob
1122 (X_set_of_books_id => c_rec.set_of_books_id,
1123 X_mrc_sob_type_code => l_reporting_flag
1124 ) then
1125 raise error_found;
1126 end if;
1127
1128 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1129 fnd_log.string(G_LEVEL_STATEMENT,
1130 G_MODULE_NAME||l_procedure_name,
1131 'calling deferred_deprn');
1132 END IF;
1133
1134 deferred_deprn
1135 (p_corp_book => fa_cache_pkg.fazcbc_record.distribution_source_book,
1136 p_tax_book => p_tax_book_type_code,
1137 p_corp_period_ctr => l_corp_period_counter,
1138 p_tax_period_ctr => l_tax_period_counter,
1139 p_mrc_sob_type_code => l_reporting_flag,
1140 p_set_of_books_id => c_rec.set_of_books_id);
1141
1142 end loop;
1143
1144 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1145 fnd_log.string(G_LEVEL_STATEMENT,
1146 G_MODULE_NAME||l_procedure_name,
1147 'calling create_bulk_deferred_events');
1148 END IF;
1149
1150 -- now process the events
1151 create_bulk_deferred_events
1152 (p_tax_book => p_tax_book_type_code,
1153 p_corp_book => fa_cache_pkg.fazcbc_record.distribution_source_book,
1154 p_tax_period_counter => l_tax_period_counter,
1155 p_corp_period_counter => l_corp_period_counter);
1156
1157 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1158 fnd_log.string(G_LEVEL_PROCEDURE,
1159 G_MODULE_NAME||l_procedure_name||'.end',
1160 'End of procedure');
1161 END IF;
1162
1163 Write_Mesg(l_calling_fn,
1164 'FA_SHARED_END_SUCCESS',
1165 'PROGRAM',
1166 'FAXDEF');
1167
1168 Write_ErrMsg_Log;
1169 retcode := 0;
1170
1171 EXCEPTION
1172 WHEN error_found THEN
1173 fa_srvr_msg.add_message(calling_fn => l_calling_fn);
1174
1175 Write_Mesg(l_calling_fn,
1176 'FA_SHARED_PROGRAM_FAILED',
1177 'PROGRAM',
1178 'FAXDEF');
1179
1180 Write_ErrMsg_Log;
1181 retcode := 2;
1182
1183 WHEN others THEN
1184 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn);
1185
1186 Write_Mesg(l_calling_fn,
1187 'FA_SHARED_PROGRAM_FAILED',
1188 'PROGRAM',
1189 'FAXDEF');
1190
1191 Write_ErrMsg_Log;
1192 retcode := 2;
1193
1194 end do_deferred;
1195
1196 END FA_DEFERRED_DEPRN_PKG;