[Home] [Help]
PACKAGE BODY: APPS.FA_SLA_CURRENT_PERIOD_UPG_PKG
Source
1 PACKAGE BODY FA_SLA_CURRENT_PERIOD_UPG_PKG as
2 /* $Header: FACPUPGB.pls 120.28 2011/06/29 11:27:44 spooyath ship $ */
3
4 Procedure Upgrade_Addition (
5 p_book_type_code IN varchar2,
6 p_start_rowid IN rowid,
7 p_end_rowid IN rowid,
8 p_batch_size IN number,
9 x_success_count OUT NOCOPY number,
10 x_failure_count OUT NOCOPY number,
11 x_return_status OUT NOCOPY number
12 ,p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
13
14 c_upgrade_bugno constant number(15) := -4107161;
15 c_fnd_user constant number(15) := 2;
16
17 -- this value can be altered in order to process more of less per batch
18 l_batch_size NUMBER;
19
20 l_rows_processed NUMBER;
21
22 l_precision NUMBER;
23
24 cursor c_additions is
25 select /*+ leading(th) rowid(th) */
26 th.transaction_header_id ,
27 th.asset_id ,
28 th.book_type_code ,
29 th.transaction_type_code ,
30 bk.cost ,
31 ad.asset_category_id ,
32 ad.asset_type ,
33 ad.current_units ,
34 bc.set_of_books_id
35 from fa_transaction_headers th,
36 fa_deprn_periods dp,
37 fa_books bk,
38 fa_category_books cb,
39 fa_additions_b ad,
40 fa_book_controls bc
41 where th.rowid between p_start_rowid and p_end_rowid
42 and th.book_type_code = dp.book_type_code
43 and th.date_effective > dp.period_open_date
44 and dp.period_close_date is null
45 and dp.book_type_code = bc.book_type_code
46 and bc.book_class <> 'BUDGET'
47 and nvl(bc.date_ineffective, sysdate + 1) > sysdate
48 and bc.book_type_code = cb.book_type_code
49 and ad.asset_category_id = cb.category_id
50 and th.transaction_type_code in ('ADDITION', 'CIP ADDITION')
51 and th.transaction_header_id = bk.transaction_header_id_in
52 and bk.cost <> 0
53 and th.asset_id = ad.asset_id
54 and not exists (select 'x'
55 from fa_adjustments aj
56 where aj.asset_id = th.asset_id
57 and aj.book_type_code = th.book_type_code
58 and aj.transaction_header_id = th.transaction_header_id
59 and aj.adjustment_type like '%COST');
60
61 cursor c_invoices (p_asset_id number) is
62 select AI.Payables_Code_Combination_ID,
63 NVL(AI.Payables_Cost, 0),
64 AI.Asset_Invoice_ID,
65 AI.source_line_id
66 FROM FA_ASSET_INVOICES AI
67 WHERE AI.ASSET_ID = p_asset_id
68 AND AI.Payables_Code_Combination_ID IS NOT NULL
69 AND AI.Date_Ineffective IS NULL
70 ORDER BY AI.Payables_Code_Combination_ID,
71 AI.PO_Vendor_ID,
72 AI.Invoice_Number;
73
74 TYPE tab_varchar IS TABLE OF varchar2(150) INDEX BY BINARY_INTEGER;
75 TYPE tab_number IS TABLE OF number INDEX BY BINARY_INTEGER;
76
77 -- for bulk collect
78 l_thid tab_number;
79 l_asset_id tab_number;
80 l_book_type_code tab_varchar;
81 l_trx_type_code tab_varchar;
82 l_cost tab_number;
83 l_category_id tab_number;
84 l_asset_type tab_varchar;
85 l_current_units tab_number;
86 l_set_of_books_id tab_number;
87
88 l_payables_ccid tab_number;
89 l_payables_cost tab_number;
90 l_asset_invoice_id tab_number;
91 l_source_line_id tab_number;
92
93 l_sum_payables_cost number := 0;
94 l_clearing_to_insert number := 0;
95 l_mrc_sob_type_code varchar2(1) := 'P';
96
97 -- for api callouts
98 l_trans_rec fa_api_types.trans_rec_type;
99 l_asset_hdr_rec fa_api_types.asset_hdr_rec_type;
100 l_asset_desc_rec fa_api_types.asset_desc_rec_type;
101 l_asset_cat_rec fa_api_types.asset_cat_rec_type;
102 l_asset_type_rec fa_api_types.asset_type_rec_type;
103
104 l_adj fa_adjust_type_pkg.fa_adj_row_struct;
105 l_calling_fn varchar2(35) := 'FACPUPGB.Upgrade_Addition';
106 l_log_level_rec FA_API_TYPES.log_level_rec_type;
107
108 error_found exception;
109
110 BEGIN
111
112 l_batch_size := nvl(nvl(p_batch_size, fa_cache_pkg.fa_batch_size), 1000);
113
114 loop
115
116 OPEN c_additions;
117
118 FETCH c_additions BULK COLLECT
119 INTO l_thid ,
120 l_asset_id ,
121 l_book_type_code ,
122 l_trx_type_code ,
123 l_cost ,
124 l_category_id ,
125 l_asset_type ,
126 l_current_units ,
127 l_set_of_books_id
128 LIMIT l_batch_size;
129 CLOSE c_additions;
130
131 l_rows_processed := l_thid.count;
132
133 l_trans_rec.who_info.last_update_date := sysdate;
134 l_trans_rec.who_info.last_updated_by := c_upgrade_bugno;
135 l_trans_rec.who_info.last_update_login := c_upgrade_bugno;
136
137 for i in 1..l_thid.count loop
138
139 --Added for bug# 5213257
140 fnd_profile.put('GL_SET_OF_BKS_ID', l_set_of_books_id(i));
141 fnd_client_info.set_currency_context (l_set_of_books_id(i));
142
143 -- call the cache
144 if not (fa_cache_pkg.fazcbc(x_book => l_book_type_code(i))) then
145 raise error_found;
146 end if;
147
148 -- load the api rec types
149 l_trans_rec.transaction_header_id := l_thid (i);
150 l_asset_hdr_rec.asset_id := l_asset_id(i);
151 l_asset_hdr_rec.book_type_code := l_book_type_code(i);
152 l_asset_hdr_rec.set_of_books_id := l_set_of_books_id(i); -- Bug 6827893
153 l_trans_rec.transaction_type_code := l_trx_type_code(i);
154 l_asset_cat_rec.category_id := l_category_id(i);
155 l_asset_type_rec.asset_type := l_asset_type(i);
156 l_asset_desc_rec.current_units := l_current_units(i);
157
158 l_adj.transaction_header_id := l_thid (i);
159 l_adj.asset_id := l_asset_id(i);
160 l_adj.book_type_code := l_book_type_code(i);
161 l_adj.set_of_books_id := l_set_of_books_id(i); -- Bug 12537420
162 l_adj.period_counter_created :=
163 fa_cache_pkg.fazcbc_record.last_period_counter + 1;
164 l_adj.period_counter_adjusted :=
165 fa_cache_pkg.fazcbc_record.last_period_counter + 1;
166 l_adj.current_units := l_current_units(i) ;
167 l_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
168 l_adj.selection_thid := 0;
169 l_adj.selection_retid := 0;
170 l_adj.leveling_flag := TRUE;
171 l_adj.last_update_date :=
172 l_trans_rec.who_info.last_update_date;
173
174 l_adj.flush_adj_flag := FALSE;
175 l_adj.gen_ccid_flag := FALSE;
176 l_adj.annualized_adjustment := 0;
177 l_adj.distribution_id := 0;
178
179 l_adj.adjustment_type := 'COST CLEARING';
180 l_adj.source_type_code := l_trx_type_code(i);
181 l_adj.mrc_sob_type_code := 'P';
182
183 if l_asset_type(i) = 'CIP' then
184 l_adj.account_type := 'CIP_CLEARING_ACCT';
185 else
186 l_adj.account_type := 'ASSET_CLEARING_ACCT';
187 end if;
188
189 /* commented for bug# 5213257
190 fnd_profile.put('GL_SET_OF_BKS_ID', l_set_of_books_id(i));
191 fnd_client_info.set_currency_context (l_set_of_books_id(i)); */
192
193 l_sum_payables_cost := 0;
194
195 -- process invoices first
196 OPEN c_invoices (p_asset_id => l_asset_id(i));
197
198 FETCH c_invoices BULK COLLECT
199 INTO l_payables_ccid ,
200 l_payables_cost ,
201 l_asset_invoice_id ,
202 l_source_line_id ;
203
204 CLOSE c_invoices;
205
206 for x in 1..l_payables_ccid.count loop
207
208 l_sum_payables_cost := l_sum_payables_cost +
209 l_payables_cost(x);
210
211 l_adj.asset_invoice_id := l_asset_invoice_id(x);
212 l_adj.source_line_id := l_source_line_id(x);
213 l_adj.code_combination_id := l_payables_ccid(x);
214
215 if l_payables_cost(x) > 0 then
216 l_adj.debit_credit_flag := 'CR';
217 l_adj.adjustment_amount := l_payables_cost(x);
218 else
219 l_adj.debit_credit_flag := 'DR';
220 l_adj.adjustment_amount := -l_payables_cost(x);
221 end if;
222
223 if not FA_INS_ADJUST_PKG.faxinaj
224 (l_adj,
225 l_trans_rec.who_info.last_update_date,
226 l_trans_rec.who_info.last_updated_by,
227 l_trans_rec.who_info.last_update_login,
228 l_log_level_rec) then
229 raise error_found;
230 end if;
231
232 end loop;
233
234 -- now calc difference between invoice total and
235 l_clearing_to_insert := l_cost(i) - l_sum_payables_cost;
236
237 -- now insert cost, etc
238
239 if not FA_INS_ADJ_PVT.faxiat
240 (p_trans_rec => l_trans_rec,
241 p_asset_hdr_rec => l_asset_hdr_rec,
242 p_asset_desc_rec => l_asset_desc_rec,
243 p_asset_cat_rec => l_asset_cat_rec,
244 p_asset_type_rec => l_asset_type_rec,
245 p_cost => l_cost(i),
246 p_clearing => l_clearing_to_insert,
247 p_deprn_expense => 0,
248 p_bonus_expense => 0,
249 p_impair_expense => 0,
250 p_ann_adj_amt => 0,
251 p_mrc_sob_type_code => l_mrc_sob_type_code,
252 p_calling_fn => l_calling_fn,
253 p_log_level_rec => l_log_level_rec
254 ) then
255 raise error_found;
256 end if;
257
258 -- Bug 6811554 : To be used in FAEVUPGB.pls
259 -- for creating the events in U/U status
260 update fa_transaction_headers
261 set event_id = -2
262 where transaction_header_id = l_adj.transaction_header_id
263 and asset_id = l_adj.asset_id
264 and book_type_code = l_adj.book_type_code;
265
266 end loop; -- additions
267
268 -- flush remaining rows
269 l_adj.transaction_header_id := 0;
270 l_adj.flush_adj_flag := TRUE;
271 l_adj.leveling_flag := TRUE;
272
273 if not FA_INS_ADJUST_PKG.faxinaj
274 (l_adj,
275 l_trans_rec.who_info.last_update_date,
276 l_trans_rec.who_info.last_updated_by,
277 l_trans_rec.who_info.last_update_login,
278 l_log_level_rec) then
279
280 raise error_found;
281 end if;
282
283 COMMIT;
284
285 l_thid.delete;
286 l_asset_id.delete;
287 l_book_type_code.delete;
288 l_trx_type_code.delete;
289 l_cost.delete;
290 l_category_id.delete;
291 l_asset_type.delete;
292 l_current_units.delete;
293 l_set_of_books_id.delete;
294 l_payables_ccid.delete;
295 l_payables_cost.delete;
296 l_asset_invoice_id.delete;
297 l_source_line_id.delete;
298
299 if (l_rows_processed < l_batch_size) then exit; end if;
300
301 end loop;
302
303 EXCEPTION
304 when error_found then
305 if (c_additions%ISOPEN) then
306 close c_additions;
307 end if;
308 rollback;
309 raise;
310
311 /*
312 when others then
313 close c_additions;
314 rollback;
315 raise;
316 */
317
318 END Upgrade_Addition;
319
320 Procedure Upgrade_Addition_MRC (
321 p_book_type_code IN varchar2,
322 p_start_rowid IN rowid,
323 p_end_rowid IN rowid,
324 p_batch_size IN number,
325 x_success_count OUT NOCOPY number,
326 x_failure_count OUT NOCOPY number,
327 x_return_status OUT NOCOPY number
328 ,p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
329
330 c_upgrade_bugno constant number(15) := -4107161;
331 c_fnd_user constant number(15) := 2;
332
333 -- this value can be altered in order to process more of less per batch
334 l_batch_size NUMBER;
335
336 l_rows_processed NUMBER;
337
338 l_precision NUMBER;
339
340 cursor c_additions_mrc is
341 select /*+ leading(th) rowid(th) */
342 th.transaction_header_id ,
343 th.asset_id ,
344 th.book_type_code ,
345 th.transaction_type_code ,
346 bk.cost ,
347 ad.asset_category_id ,
348 ad.asset_type ,
349 ad.current_units ,
350 mcbc.set_of_books_id
351 from fa_transaction_headers th,
352 fa_mc_deprn_periods dp,
353 fa_mc_books bk,
354 fa_additions_b ad,
355 fa_category_books cb,
356 fa_book_controls bc,
357 fa_mc_book_controls mcbc
358 where th.rowid between p_start_rowid and p_end_rowid
359 and th.book_type_code = dp.book_type_code
360 and th.date_effective > dp.period_open_date
361 and dp.period_close_date is null
362 and dp.book_type_code = bc.book_type_code
363 and bc.book_class <> 'BUDGET'
364 and nvl(bc.date_ineffective, sysdate + 1) > sysdate
365 and bc.book_type_code = cb.book_type_code
366 and ad.asset_category_id = cb.category_id
367 and dp.book_type_code = mcbc.book_type_code
368 and dp.set_of_books_id = mcbc.set_of_books_id
369 and th.transaction_type_code in ('ADDITION', 'CIP ADDITION')
370 and th.transaction_header_id = bk.transaction_header_id_in
371 and bk.set_of_books_id = mcbc.set_of_books_id
372 and bk.book_type_code = mcbc.book_type_code
373 and mcbc.enabled_flag = 'Y'
374 and bk.cost <> 0
375 and th.asset_id = ad.asset_id
376 and not exists (select 'x'
377 from fa_mc_adjustments aj
378 where aj.asset_id = th.asset_id
379 and aj.book_type_code = th.book_type_code
380 and aj.set_of_books_id = mcbc.set_of_books_id
381 and aj.book_type_code = mcbc.book_type_code
382 and aj.transaction_header_id = th.transaction_header_id
383 and aj.adjustment_type like '%COST')
384 order by set_of_books_id;
385
386 cursor c_invoices_mrc (p_asset_id number,
387 p_set_of_books_id number) is
388 select AI.Payables_Code_Combination_ID,
389 NVL(AI.Payables_Cost, 0),
390 AI.Asset_Invoice_ID,
391 AI.source_line_id
392 FROM FA_MC_ASSET_INVOICES AI
393 WHERE AI.ASSET_ID = p_asset_id
394 AND AI.set_of_books_id = p_set_of_books_id
395 AND AI.Payables_Code_Combination_ID IS NOT NULL
396 AND AI.Date_Ineffective IS NULL
397 ORDER BY AI.Payables_Code_Combination_ID,
398 AI.PO_Vendor_ID,
399 AI.Invoice_Number;
400
401 TYPE tab_varchar IS TABLE OF varchar2(150) INDEX BY BINARY_INTEGER;
402 TYPE tab_number IS TABLE OF number INDEX BY BINARY_INTEGER;
403
404 -- for bulk collect
405 l_thid tab_number;
406 l_asset_id tab_number;
407 l_book_type_code tab_varchar;
408 l_trx_type_code tab_varchar;
409 l_cost tab_number;
410 l_category_id tab_number;
411 l_asset_type tab_varchar;
412 l_current_units tab_number;
413 l_set_of_books_id tab_number;
414
415 l_payables_ccid tab_number;
416 l_payables_cost tab_number;
417 l_asset_invoice_id tab_number;
418 l_source_line_id tab_number;
419
420 l_sum_payables_cost number := 0;
421 l_clearing_to_insert number := 0;
422 l_mrc_sob_type_code varchar2(1) := 'R';
423
424 -- for api callouts
425 l_trans_rec fa_api_types.trans_rec_type;
426 l_asset_hdr_rec fa_api_types.asset_hdr_rec_type;
427 l_asset_desc_rec fa_api_types.asset_desc_rec_type;
428 l_asset_cat_rec fa_api_types.asset_cat_rec_type;
429 l_asset_type_rec fa_api_types.asset_type_rec_type;
430
431 l_adj fa_adjust_type_pkg.fa_adj_row_struct;
432 l_calling_fn varchar2(35) := 'FACPUPGB.Upgrade_Addition_MRC';
433 l_log_level_rec FA_API_TYPES.log_level_rec_type;
434
435 error_found exception;
436
437 BEGIN
438
439 l_batch_size := nvl(nvl(p_batch_size, fa_cache_pkg.fa_batch_size), 1000);
440
441 loop
442
443 OPEN c_additions_mrc;
444
445 FETCH c_additions_mrc BULK COLLECT
446 INTO l_thid ,
447 l_asset_id ,
448 l_book_type_code ,
449 l_trx_type_code ,
450 l_cost ,
451 l_category_id ,
452 l_asset_type ,
453 l_current_units ,
454 l_set_of_books_id
455 LIMIT l_batch_size;
456 CLOSE c_additions_mrc;
457
458 l_rows_processed := l_thid.count;
459
460 l_trans_rec.who_info.last_update_date := sysdate;
461 l_trans_rec.who_info.last_updated_by := c_upgrade_bugno;
462 l_trans_rec.who_info.last_update_login := c_upgrade_bugno;
463
464 for i in 1..l_thid.count loop
465
466 -- Added for bug# 5213257
467 fnd_profile.put('GL_SET_OF_BKS_ID', l_set_of_books_id(i));
468 fnd_client_info.set_currency_context (l_set_of_books_id(i));
469
470 -- call the cache
471 if not (fa_cache_pkg.fazcbc(x_book => l_book_type_code(i))) then
472 raise error_found;
473 end if;
474
475 -- load the api rec types
476 l_trans_rec.transaction_header_id := l_thid (i);
477 l_asset_hdr_rec.asset_id := l_asset_id(i);
478 l_asset_hdr_rec.book_type_code := l_book_type_code(i);
479 l_asset_hdr_rec.set_of_books_id := l_set_of_books_id(i); -- Bug 12537420
480 l_trans_rec.transaction_type_code := l_trx_type_code(i);
481 l_asset_cat_rec.category_id := l_category_id(i);
482 l_asset_type_rec.asset_type := l_asset_type(i);
483 l_asset_desc_rec.current_units := l_current_units(i);
484
485 l_adj.transaction_header_id := l_thid (i);
486 l_adj.asset_id := l_asset_id(i);
487 l_adj.book_type_code := l_book_type_code(i);
488 l_adj.period_counter_created := fa_cache_pkg.fazcbc_record.last_period_counter + 1;
489 l_adj.period_counter_adjusted := fa_cache_pkg.fazcbc_record.last_period_counter + 1;
490 l_adj.current_units := l_current_units(i) ;
491 l_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
492 l_adj.selection_thid := 0;
493 l_adj.selection_retid := 0;
494 l_adj.leveling_flag := TRUE;
495 l_adj.last_update_date := l_trans_rec.who_info.last_update_date;
496
497 l_adj.flush_adj_flag := FALSE;
498 l_adj.gen_ccid_flag := FALSE;
499 l_adj.annualized_adjustment := 0;
500 l_adj.distribution_id := 0;
501
502 l_adj.adjustment_type := 'COST CLEARING';
503 l_adj.source_type_code := l_trx_type_code(i);
504 l_adj.mrc_sob_type_code := 'R';
505 l_adj.set_of_books_id := l_set_of_books_id(i); -- Bug 12537420
506
507 if l_asset_type(i) = 'CIP' then
508 l_adj.account_type := 'CIP_CLEARING_ACCT';
509 else
510 l_adj.account_type := 'ASSET_CLEARING_ACCT';
511 end if;
512
513 /* commented for bug# 5213257
514 fnd_profile.put('GL_SET_OF_BKS_ID', l_set_of_books_id(i));
515 fnd_client_info.set_currency_context (l_set_of_books_id(i)); */
516
517 l_sum_payables_cost := 0;
518
519 -- process invoices first
520 OPEN c_invoices_mrc (p_asset_id => l_asset_id(i),
521 p_set_of_books_id => l_set_of_books_id(i));
522
523 FETCH c_invoices_mrc BULK COLLECT
524 INTO l_payables_ccid ,
525 l_payables_cost ,
526 l_asset_invoice_id ,
527 l_source_line_id ;
528
529 CLOSE c_invoices_mrc;
530
531 for x in 1..l_payables_ccid.count loop
532
533 l_sum_payables_cost := l_sum_payables_cost + l_payables_cost(x);
534
535 l_adj.asset_invoice_id := l_asset_invoice_id(x);
536 l_adj.source_line_id := l_source_line_id(x);
537 l_adj.code_combination_id := l_payables_ccid(x);
538
539 if l_payables_cost(x) > 0 then
540 l_adj.debit_credit_flag := 'CR';
541 l_adj.adjustment_amount := l_payables_cost(x);
542 else
543 l_adj.debit_credit_flag := 'DR';
544 l_adj.adjustment_amount := -l_payables_cost(x);
545 end if;
546
547 if not FA_INS_ADJUST_PKG.faxinaj
548 (l_adj,
549 l_trans_rec.who_info.last_update_date,
550 l_trans_rec.who_info.last_updated_by,
551 l_trans_rec.who_info.last_update_login,
552 l_log_level_rec) then
553 raise error_found;
554 end if;
555
556 end loop;
557
558 -- now calc difference between invoice total and
559 l_clearing_to_insert := l_cost(i) - l_sum_payables_cost;
560
561
562 -- now insert cost, etc
563
564 if not FA_INS_ADJ_PVT.faxiat
565 (p_trans_rec => l_trans_rec,
566 p_asset_hdr_rec => l_asset_hdr_rec,
567 p_asset_desc_rec => l_asset_desc_rec,
568 p_asset_cat_rec => l_asset_cat_rec,
569 p_asset_type_rec => l_asset_type_rec,
570 p_cost => l_cost(i),
571 p_clearing => l_clearing_to_insert,
572 p_deprn_expense => 0,
573 p_bonus_expense => 0,
574 p_impair_expense => 0,
575 p_ann_adj_amt => 0,
576 p_mrc_sob_type_code => l_mrc_sob_type_code,
577 p_calling_fn => l_calling_fn,
578 p_log_level_rec => l_log_level_rec
579 ) then
580 raise error_found;
581 end if;
582
583 end loop; -- additions
584
585 -- flush remaining rows
586 l_adj.transaction_header_id := 0;
587 l_adj.flush_adj_flag := TRUE;
588 l_adj.leveling_flag := TRUE;
589
590 if not FA_INS_ADJUST_PKG.faxinaj
591 (l_adj,
592 l_trans_rec.who_info.last_update_date,
593 l_trans_rec.who_info.last_updated_by,
594 l_trans_rec.who_info.last_update_login,
595 l_log_level_rec) then
596 raise error_found;
597 end if;
598
599 COMMIT;
600
601 l_thid.delete;
602 l_asset_id.delete;
603 l_book_type_code.delete;
604 l_trx_type_code.delete;
605 l_cost.delete;
606 l_category_id.delete;
607 l_asset_type.delete;
608 l_current_units.delete;
609 l_set_of_books_id.delete;
610 l_payables_ccid.delete;
611 l_payables_cost.delete;
612 l_asset_invoice_id.delete;
613 l_source_line_id.delete;
614
615 if (l_rows_processed < l_batch_size) then exit; end if;
616
617 end loop;
618
619 EXCEPTION
620 when error_found then
621 if (c_additions_mrc%ISOPEN) then
622 close c_additions_mrc;
623 end if;
624 rollback;
625 raise;
626
627 when others then
628 close c_additions_mrc;
629 rollback;
630 raise;
631
632 END Upgrade_Addition_MRC;
633
634 Procedure Upgrade_Backdated_Trxns (
635 p_book_type_code IN varchar2,
636 p_start_rowid IN rowid,
637 p_end_rowid IN rowid,
638 p_batch_size IN number,
639 x_success_count OUT NOCOPY number,
640 x_failure_count OUT NOCOPY number,
641 x_return_status OUT NOCOPY number,
642 p_log_level_rec IN FA_API_TYPES.log_level_rec_type
643 default null
644 ) IS
645
646 c_upgrade_bugno constant number(15) := -4107161;
647 c_fnd_user constant number(15) := 2;
648
649 -- this value can be altered in order to process more of less per batch
650 l_batch_size NUMBER;
651
652 l_rows_processed NUMBER;
653
654 -- type for table variable
655 type num_tbl_type is table of number index by binary_integer;
656 type char_tbl_type is table of varchar2(150) index by binary_integer;
657 type date_tbl_type is table of date index by binary_integer;
658 type rowid_tbl_type is table of rowid index by binary_integer;
659
660 -- used for bulk fetching
661 -- main cursor
662 l_transaction_header_id_tbl num_tbl_type;
663 l_asset_id_tbl num_tbl_type;
664 l_book_type_code_tbl char_tbl_type;
665 l_adj_req_status_tbl char_tbl_type;
666 l_asset_type_tbl char_tbl_type;
667 l_current_units_tbl num_tbl_type;
668 l_category_id_tbl num_tbl_type;
669 l_transaction_type_code_tbl char_tbl_type;
670 l_set_of_books_id_tbl num_tbl_type;
671 l_transaction_subtype_tbl char_tbl_type;
672 l_transaction_name_tbl char_tbl_type;
673 l_src_thid_tbl num_tbl_type;
674 l_transaction_key_tbl char_tbl_type;
675 l_amortization_start_date_tbl date_tbl_type;
676 l_group_asset_id_tbl num_tbl_type;
677 l_ann_deprn_rounding_flag_tbl char_tbl_type;
678 l_transaction_date_entered_tbl date_tbl_type;
679 l_cost_tbl num_tbl_type;
680 l_adjusted_cost_tbl num_tbl_type;
681 l_recoverable_cost_tbl num_tbl_type;
682 l_reval_amortization_basis_tbl num_tbl_type;
683 l_adjusted_rate_tbl num_tbl_type;
684 l_production_capacity_tbl num_tbl_type;
685 l_adjusted_capacity_tbl num_tbl_type;
686 l_adj_recoverable_cost_tbl num_tbl_type;
687 l_deprn_method_code_tbl char_tbl_type;
688 l_life_in_months_tbl num_tbl_type;
689 l_salvage_value_tbl num_tbl_type;
690 l_depreciate_flag_tbl char_tbl_type;
691 l_ceiling_name_tbl char_tbl_type;
692 l_rate_adjustment_factor_tbl num_tbl_type;
693 l_bonus_rule_tbl char_tbl_type;
694 l_prorate_date_tbl date_tbl_type;
695 l_deprn_start_date_tbl date_tbl_type;
696 l_date_placed_in_service_tbl date_tbl_type;
697 l_short_fiscal_year_flag_tbl char_tbl_type;
698 l_conversion_date_tbl date_tbl_type;
699 l_orig_deprn_start_date_tbl date_tbl_type;
700 l_formula_factor_tbl num_tbl_type;
701 l_eofy_reserve_tbl num_tbl_type;
702 l_asset_number_tbl char_tbl_type;
703 l_deprn_amount_tbl num_tbl_type;
704 l_ytd_deprn_tbl num_tbl_type;
705 l_deprn_reserve_tbl num_tbl_type;
706 l_prior_fy_expense_tbl num_tbl_type;
707 l_bonus_deprn_amount_tbl num_tbl_type;
708 l_bonus_ytd_deprn_tbl num_tbl_type;
709 l_prior_fy_bonus_expense_tbl num_tbl_type;
710 l_reval_amortization_tbl num_tbl_type;
711 l_reval_amortization_basis num_tbl_type;
712 l_reval_deprn_expense_tbl num_tbl_type;
713 l_reval_ytd_deprn_tbl num_tbl_type;
714 l_reval_deprn_reserve_tbl num_tbl_type;
715 l_production_tbl num_tbl_type;
716 l_ytd_production_tbl num_tbl_type;
717 l_ltd_production_tbl num_tbl_type;
718
719 l_period_rec fa_api_types.period_rec_type;
720 l_trans_rec FA_API_TYPES.trans_rec_type;
721 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
722 l_asset_desc_rec FA_API_TYPES.asset_desc_rec_type;
723 l_asset_cat_rec FA_API_TYPES.asset_cat_rec_type;
724 l_asset_type_rec FA_API_TYPES.asset_type_rec_type;
725 l_asset_fin_rec FA_API_TYPES.asset_fin_rec_type;
726 l_asset_deprn_rec FA_API_TYPES.asset_deprn_rec_type;
727 l_asset_dist_rec FA_API_TYPES.asset_dist_rec_type;
728 l_asset_dist_tbl FA_API_TYPES.asset_dist_tbl_type;
729 l_asset_fin_rec_null FA_API_TYPES.asset_fin_rec_type := null;
730
731 l_deprn_exp NUMBER;
732 l_bonus_deprn_exp NUMBER;
733 l_impairment_exp NUMBER;
734 l_ann_adj_deprn_exp NUMBER;
735 l_ann_adj_bonus_deprn_exp NUMBER;
736
737 l_book_type_code VARCHAR2(15);
738 l_asset_id NUMBER(15);
739 l_transaction_header_id NUMBER(15);
740 l_mrc_sob_type_code VARCHAR2(1);
741 l_dist NUMBER;
742 l_mrc_books NUMBER;
743
744 l_calling_fn varchar2(35) := 'FACPUPGB.Upgrade_Backdated_Trxns';
745 l_log_level_rec FA_API_TYPES.log_level_rec_type;
746
747 error_found exception;
748
749 cursor c_trx is
750 select /*+ leading(th) rowid(th) swap_join_inputs(bc) */
751 th.transaction_header_id,
752 th.asset_id,
753 th.book_type_code,
754 bks.adjustment_required_status,
755 ad.asset_type,
756 ad.current_units,
757 ad.asset_category_id,
758 th.transaction_type_code,
759 bc.set_of_books_id,
760 th.transaction_subtype,
761 bks.group_asset_id,
762 bks.annual_deprn_rounding_flag,
763 th.transaction_date_entered,
764 th.transaction_name,
765 th.source_transaction_header_id,
766 th.transaction_key,
767 th.amortization_start_date,
768 bks.cost,
769 bks.adjusted_cost,
770 bks.recoverable_cost,
771 bks.reval_amortization_basis,
772 bks.adjusted_rate,
773 bks.production_capacity,
774 bks.adjusted_capacity,
775 bks.adjusted_recoverable_cost,
776 bks.deprn_method_code,
777 bks.life_in_months,
778 bks.salvage_value,
779 bks.depreciate_flag,
780 bks.ceiling_name,
781 bks.rate_adjustment_factor,
782 bks.bonus_rule,
783 bks.prorate_date,
784 bks.deprn_start_date,
785 bks.date_placed_in_service,
786 bks.short_fiscal_year_flag,
787 bks.conversion_date,
788 bks.original_deprn_start_date,
789 bks.formula_factor,
790 bks.eofy_reserve,
791 ad.asset_number,
792 ds.deprn_amount,
793 ds.ytd_deprn,
794 ds.deprn_reserve,
795 ds.prior_fy_expense,
796 ds.bonus_deprn_amount,
797 ds.bonus_ytd_deprn,
798 ds.prior_fy_bonus_expense,
799 ds.reval_amortization,
800 ds.reval_amortization_basis,
801 ds.reval_deprn_expense,
802 ds.ytd_reval_deprn_expense,
803 ds.reval_reserve,
804 ds.production,
805 ds.ytd_production,
806 ds.ltd_production
807 from fa_transaction_headers th,
808 fa_books bks,
809 fa_additions_b ad,
810 fa_book_controls bc,
811 fa_category_books cb,
812 fa_deprn_summary ds,
813 fa_deprn_periods dp
814 where th.rowid between p_start_rowid and p_end_rowid
815 and bc.book_type_code = th.book_type_code
816 and nvl(bc.date_ineffective, sysdate + 1) > sysdate
817 and bc.book_type_code = cb.book_type_code
818 and ad.asset_category_id = cb.category_id
819 and th.transaction_header_id = bks.transaction_header_id_in
820 and bks.transaction_header_id_out is null
821 and bks.adjustment_required_status in ('ADD', 'TFR')
822 and th.asset_id = ad.asset_id
823 and th.asset_id = ds.asset_id
824 and th.book_type_code = ds.book_type_code
825 and ds.deprn_source_code = 'BOOKS'
826 and dp.book_type_code = th.book_type_code
827 and dp.period_close_date is null
828 and th.date_effective between dp.period_open_date and sysdate;
829
830 cursor c_mc_trx is
831 select /*+ leading(th) rowid(th) swap_join_inputs(bc) */
832 th.transaction_header_id,
833 th.asset_id,
834 th.book_type_code,
835 bks.adjustment_required_status,
836 ad.asset_type,
837 ad.current_units,
838 ad.asset_category_id,
839 th.transaction_type_code,
840 mcbc.set_of_books_id,
841 th.transaction_subtype,
842 bks.group_asset_id,
843 bks.annual_deprn_rounding_flag,
844 th.transaction_date_entered,
845 th.transaction_name,
846 th.source_transaction_header_id,
847 th.transaction_key,
848 th.amortization_start_date,
849 bks.cost,
850 bks.adjusted_cost,
851 bks.recoverable_cost,
852 bks.reval_amortization_basis,
853 bks.adjusted_rate,
854 bks.production_capacity,
855 bks.adjusted_capacity,
856 bks.adjusted_recoverable_cost,
857 bks.deprn_method_code,
858 bks.life_in_months,
859 bks.salvage_value,
860 bks.depreciate_flag,
861 bks.ceiling_name,
862 bks.rate_adjustment_factor,
863 bks.bonus_rule,
864 bks.prorate_date,
865 bks.deprn_start_date,
866 bks.date_placed_in_service,
867 bks.short_fiscal_year_flag,
868 bks.conversion_date,
869 bks.original_deprn_start_date,
870 bks.formula_factor,
871 bks.eofy_reserve,
872 ad.asset_number,
873 ds.deprn_amount,
874 ds.ytd_deprn,
875 ds.deprn_reserve,
876 ds.prior_fy_expense,
877 ds.bonus_deprn_amount,
878 ds.bonus_ytd_deprn,
879 ds.prior_fy_bonus_expense,
880 ds.reval_amortization,
881 ds.reval_amortization_basis,
882 ds.reval_deprn_expense,
883 ds.ytd_reval_deprn_expense,
884 ds.reval_reserve,
885 ds.production,
886 ds.ytd_production,
887 ds.ltd_production
888 from fa_transaction_headers th,
889 fa_mc_books bks,
890 fa_additions_b ad,
891 fa_book_controls bc,
892 fa_category_books cb,
893 fa_mc_book_controls mcbc,
894 fa_mc_deprn_summary ds,
895 fa_mc_deprn_periods dp
896 where th.rowid between p_start_rowid and p_end_rowid
897 and bc.book_type_code = th.book_type_code
898 and nvl(bc.date_ineffective, sysdate + 1) > sysdate
899 and bc.book_type_code = cb.book_type_code
900 and ad.asset_category_id = cb.category_id
901 and bc.book_type_code = mcbc.book_type_code
902 and mcbc.enabled_flag = 'Y'
903 and mcbc.book_type_code = bks.book_type_code
904 and mcbc.set_of_books_id = bks.set_of_books_id
905 and mcbc.book_type_code = ds.book_type_code
906 and mcbc.set_of_books_id = ds.set_of_books_id
907 and th.transaction_header_id = bks.transaction_header_id_in
908 and bks.transaction_header_id_out is null
909 and bks.adjustment_required_status = 'ADD'
910 and th.asset_id = ad.asset_id
911 and th.asset_id = ds.asset_id
912 and th.book_type_code = ds.book_type_code
913 and ds.deprn_source_code = 'BOOKS'
914 and nvl(th.transaction_subtype, 'EXPENSED') <> 'AMORTIZED'
915 and ad.asset_type = 'CAPITALIZED'
916 and bks.group_asset_id is null
917 and nvl(bks.annual_deprn_rounding_flag, 'ADD') <> 'RES'
918 and dp.book_type_code = mcbc.book_type_code
919 and dp.set_of_books_id = mcbc.set_of_books_id
920 and dp.book_type_code = th.book_type_code
921 and dp.period_close_date is null
922 and th.date_effective between dp.period_open_date and sysdate;
923
924
925 cursor c_distributions is
926 select dh.distribution_id,
927 dh.units_assigned,
928 dh.transaction_units,
929 dh.assigned_to,
930 dh.code_combination_id,
931 dh.location_id
932 from fa_distribution_history dh
933 where dh.book_type_code = l_book_type_code
934 and dh.asset_id = l_asset_id
935 and dh.transaction_header_id_out = l_transaction_header_id
936 union all
937 select dh.distribution_id,
938 dh.units_assigned,
939 dh.transaction_units,
940 dh.assigned_to,
941 dh.code_combination_id,
942 dh.location_id
943 from fa_distribution_history dh
944 where dh.book_type_code = l_book_type_code
945 and dh.asset_id = l_asset_id
946 and dh.transaction_header_id_in = l_transaction_header_id;
947
948 BEGIN
949
950 l_batch_size := nvl(nvl(p_batch_size, fa_cache_pkg.fa_batch_size), 1000);
951
952 loop
953
954 OPEN c_trx;
955
956 FETCH c_trx BULK COLLECT
957 INTO l_transaction_header_id_tbl,
958 l_asset_id_tbl,
959 l_book_type_code_tbl,
960 l_adj_req_status_tbl,
961 l_asset_type_tbl,
962 l_current_units_tbl,
963 l_category_id_tbl,
964 l_transaction_type_code_tbl,
965 l_set_of_books_id_tbl,
966 l_transaction_subtype_tbl,
967 l_group_asset_id_tbl,
968 l_ann_deprn_rounding_flag_tbl,
969 l_transaction_date_entered_tbl,
970 l_transaction_name_tbl,
971 l_src_thid_tbl,
972 l_transaction_key_tbl,
973 l_amortization_start_date_tbl,
974 l_cost_tbl,
975 l_adjusted_cost_tbl,
976 l_recoverable_cost_tbl,
977 l_reval_amortization_basis_tbl,
978 l_adjusted_rate_tbl,
979 l_production_capacity_tbl,
980 l_adjusted_capacity_tbl,
981 l_adj_recoverable_cost_tbl,
982 l_deprn_method_code_tbl,
983 l_life_in_months_tbl,
984 l_salvage_value_tbl,
985 l_depreciate_flag_tbl,
986 l_ceiling_name_tbl,
987 l_rate_adjustment_factor_tbl,
988 l_bonus_rule_tbl,
989 l_prorate_date_tbl,
990 l_deprn_start_date_tbl,
991 l_date_placed_in_service_tbl,
992 l_short_fiscal_year_flag_tbl,
993 l_conversion_date_tbl,
994 l_orig_deprn_start_date_tbl,
995 l_formula_factor_tbl,
996 l_eofy_reserve_tbl,
997 l_asset_number_tbl,
998 l_deprn_amount_tbl,
999 l_ytd_deprn_tbl,
1000 l_deprn_reserve_tbl,
1001 l_prior_fy_expense_tbl,
1002 l_bonus_deprn_amount_tbl,
1003 l_bonus_ytd_deprn_tbl,
1004 l_prior_fy_bonus_expense_tbl,
1005 l_reval_amortization_tbl,
1006 l_reval_amortization_basis,
1007 l_reval_deprn_expense_tbl,
1008 l_reval_ytd_deprn_tbl,
1009 l_reval_deprn_reserve_tbl,
1010 l_production_tbl,
1011 l_ytd_production_tbl,
1012 l_ltd_production_tbl
1013 LIMIT l_batch_size;
1014 CLOSE c_trx;
1015
1016 l_rows_processed := l_transaction_header_id_tbl.count;
1017
1018 for i in 1..l_transaction_header_id_tbl.count loop
1019
1020 fnd_profile.put('GL_SET_OF_BKS_ID', l_set_of_books_id_tbl(i));
1021 fnd_client_info.set_currency_context (l_set_of_books_id_tbl(i));
1022
1023 -- call the cache
1024 if not (fa_cache_pkg.fazcbc(x_book => l_book_type_code_tbl(i))) then
1025 raise error_found;
1026 end if;
1027
1028 l_trans_rec.transaction_header_id := l_transaction_header_id_tbl(i);
1029 l_trans_rec.transaction_type_code := l_transaction_type_code_tbl(i);
1030 l_trans_rec.transaction_date_entered :=
1031 l_transaction_date_entered_tbl(i);
1032 l_trans_rec.transaction_subtype := l_transaction_subtype_tbl(i);
1033 l_trans_rec.deprn_override_flag := fa_std_types.FA_NO_OVERRIDE;
1034 l_trans_rec.transaction_name := l_transaction_name_tbl(i);
1035 l_trans_rec.source_transaction_header_id := l_src_thid_tbl(i);
1036 l_trans_rec.transaction_key := l_transaction_key_tbl(i);
1037 l_trans_rec.amortization_start_date :=
1038 l_amortization_start_date_tbl(i);
1039 l_trans_rec.calling_interface := 'R12 Upgrade';
1040 l_trans_rec.who_info.created_by := c_fnd_user;
1041 l_trans_rec.who_info.creation_date := sysdate;
1042 l_trans_rec.who_info.last_update_date := sysdate;
1043 l_trans_rec.who_info.last_updated_by := c_fnd_user;
1044 l_trans_rec.who_info.last_update_login := c_upgrade_bugno;
1045
1046 l_asset_hdr_rec.asset_id := l_asset_id_tbl(i);
1047 l_asset_hdr_rec.book_type_code := l_book_type_code_tbl(i);
1048 l_asset_hdr_rec.set_of_books_id := l_set_of_books_id_tbl(i);
1049
1050 l_asset_desc_rec.asset_number := l_asset_number_tbl(i);
1051 l_asset_desc_rec.current_units := l_current_units_tbl(i);
1052
1053 l_asset_cat_rec.category_id := l_category_id_tbl(i);
1054
1055 l_asset_type_rec.asset_type := l_asset_type_tbl(i);
1056
1057 l_asset_fin_rec.set_of_books_id := l_set_of_books_id_tbl(i);
1058 l_asset_fin_rec.cost := l_cost_tbl(i);
1059 l_asset_fin_rec.adjusted_cost := l_adjusted_cost_tbl(i);
1060 l_asset_fin_rec.recoverable_cost := l_recoverable_cost_tbl(i);
1061 l_asset_fin_rec.reval_amortization_basis :=
1062 l_reval_amortization_basis_tbl(i);
1063 l_asset_fin_rec.adjusted_rate := l_adjusted_rate_tbl(i);
1064 l_asset_fin_rec.production_capacity := l_production_capacity_tbl(i);
1065 l_asset_fin_rec.adjusted_capacity := l_adjusted_capacity_tbl(i);
1066 l_asset_fin_rec.adjusted_recoverable_cost :=
1067 l_adj_recoverable_cost_tbl(i);
1068 l_asset_fin_rec.deprn_method_code := l_deprn_method_code_tbl(i);
1069 l_asset_fin_rec.life_in_months := l_life_in_months_tbl(i);
1070 l_asset_fin_rec.salvage_value := l_salvage_value_tbl(i);
1071 l_asset_fin_rec.depreciate_flag := l_depreciate_flag_tbl(i);
1072 l_asset_fin_rec.ceiling_name := l_ceiling_name_tbl(i);
1073 l_asset_fin_rec.rate_adjustment_factor :=
1074 l_rate_adjustment_factor_tbl(i);
1075 l_asset_fin_rec.bonus_rule := l_bonus_rule_tbl(i);
1076 l_asset_fin_rec.prorate_date := l_prorate_date_tbl(i);
1077 l_asset_fin_rec.deprn_start_date := l_deprn_start_date_tbl(i);
1078 l_asset_fin_rec.date_placed_in_service :=
1079 l_date_placed_in_service_tbl(i);
1080 l_asset_fin_rec.short_fiscal_year_flag :=
1081 l_short_fiscal_year_flag_tbl(i);
1082 l_asset_fin_rec.conversion_date := l_conversion_date_tbl(i);
1083 l_asset_fin_rec.orig_deprn_start_date :=
1084 l_orig_deprn_start_date_tbl(i);
1085 l_asset_fin_rec.formula_factor := l_formula_factor_tbl(i);
1086 l_asset_fin_rec.eofy_reserve := l_eofy_reserve_tbl(i);
1087 l_asset_fin_rec.group_asset_id := l_group_asset_id_tbl(i);
1088 l_asset_fin_rec.annual_deprn_rounding_flag :=
1089 l_ann_deprn_rounding_flag_tbl(i);
1090
1091 l_asset_deprn_rec.set_of_books_id := l_set_of_books_id_tbl(i);
1092 l_asset_deprn_rec.deprn_amount := l_deprn_amount_tbl(i);
1093 l_asset_deprn_rec.ytd_deprn := l_ytd_deprn_tbl(i);
1094 l_asset_deprn_rec.deprn_reserve := l_deprn_reserve_tbl(i);
1095 l_asset_deprn_rec.prior_fy_expense := l_prior_fy_expense_tbl(i);
1096 l_asset_deprn_rec.bonus_deprn_amount := l_bonus_deprn_amount_tbl(i);
1097 l_asset_deprn_rec.bonus_ytd_deprn := l_bonus_ytd_deprn_tbl(i);
1098 l_asset_deprn_rec.prior_fy_bonus_expense :=
1099 l_prior_fy_bonus_expense_tbl(i);
1100 l_asset_deprn_rec.reval_amortization := l_reval_amortization_tbl(i);
1101 l_asset_deprn_rec.reval_amortization_basis :=
1102 l_reval_amortization_basis(i);
1103 l_asset_deprn_rec.reval_deprn_expense := l_reval_deprn_expense_tbl(i);
1104 l_asset_deprn_rec.reval_ytd_deprn := l_reval_ytd_deprn_tbl(i);
1105 l_asset_deprn_rec.reval_deprn_reserve := l_reval_deprn_reserve_tbl(i);
1106 l_asset_deprn_rec.production := l_production_tbl(i);
1107 l_asset_deprn_rec.ytd_production := l_ytd_production_tbl(i);
1108 l_asset_deprn_rec.ltd_production := l_ltd_production_tbl(i);
1109
1110 l_mrc_sob_type_code := 'P';
1111
1112 if (NOT FA_UTIL_PVT.get_period_rec (
1113 p_book => l_asset_hdr_rec.book_type_code,
1114 p_effective_date => NULL,
1115 x_period_rec => l_period_rec,
1116 p_log_level_rec => p_log_level_rec)) then
1117 raise error_found;
1118 end if;
1119
1120 if (l_adj_req_status_tbl(i) = 'ADD') then
1121
1122 -- Catchup addition by calling FA_EXP_PVT.faxexp
1123
1124 if ((nvl(l_trans_rec.transaction_subtype, 'EXPENSED') <>
1125 'AMORTIZED') and
1126 (l_asset_type_rec.asset_type = 'CAPITALIZED') and
1127 (l_asset_fin_rec.group_asset_id is null) and
1128 (nvl(l_asset_fin_rec.annual_deprn_rounding_flag,'ADD') <>
1129 'RES') and
1130 (l_trans_rec.transaction_date_entered <
1131 l_period_rec.calendar_period_open_date)
1132 ) then
1133
1134 if not FA_EXP_PVT.faxexp
1135 (px_trans_rec => l_trans_rec,
1136 p_asset_hdr_rec => l_asset_hdr_rec,
1137 p_asset_desc_rec => l_asset_desc_rec,
1138 p_asset_cat_rec => l_asset_cat_rec,
1139 p_asset_type_rec => l_asset_type_rec,
1140 p_asset_fin_rec_old => l_asset_fin_rec_null,
1141 px_asset_fin_rec_new => l_asset_fin_rec,
1142 p_asset_deprn_rec => l_asset_deprn_rec,
1143 p_period_rec => l_period_rec,
1144 p_mrc_sob_type_code => l_mrc_sob_type_code,
1145 p_running_mode => fa_std_types.FA_DPR_NORMAL,
1146 p_used_by_revaluation => null,
1147 x_deprn_exp => l_deprn_exp,
1148 x_bonus_deprn_exp => l_bonus_deprn_exp,
1149 x_impairment_exp => l_impairment_exp,
1150 x_ann_adj_deprn_exp => l_ann_adj_deprn_exp,
1151 x_ann_adj_bonus_deprn_exp
1152 => l_ann_adj_bonus_deprn_exp,
1153 p_log_level_rec => l_log_level_rec) then
1154
1155 raise error_found;
1156 end if;
1157
1158 if not FA_INS_ADJ_PVT.faxiat
1159 (p_trans_rec => l_trans_rec,
1160 p_asset_hdr_rec => l_asset_hdr_rec,
1161 p_asset_desc_rec => l_asset_desc_rec,
1162 p_asset_cat_rec => l_asset_cat_rec,
1163 p_asset_type_rec => l_asset_type_rec,
1164 p_cost => 0,
1165 p_clearing => 0,
1166 p_deprn_expense => l_deprn_exp,
1167 p_bonus_expense => l_bonus_deprn_exp,
1168 p_impair_expense => l_impairment_exp,
1169 p_deprn_reserve => 0,
1170 p_bonus_reserve => 0,
1171 p_ann_adj_amt => l_ann_adj_deprn_exp,
1172 p_mrc_sob_type_code => l_mrc_sob_type_code,
1173 p_calling_fn => l_calling_fn,
1174 p_log_level_rec => l_log_level_rec
1175 ) then raise error_found;
1176 end if;
1177
1178 -- Reflect post catch-up info to fa_books
1179 fa_books_pkg.update_row
1180 (X_asset_id => l_asset_hdr_rec.asset_id,
1181 X_book_type_code => l_asset_hdr_rec.book_type_code,
1182 X_Adjustment_Required_Status
1183 => 'NONE',
1184 X_rate_adjustment_factor
1185 => l_asset_fin_rec.rate_adjustment_factor,
1186 X_reval_amortization_basis
1187 =>
1188 l_asset_fin_rec.reval_amortization_basis,
1189 X_adjusted_cost => l_asset_fin_rec.adjusted_cost,
1190 X_adjusted_capacity => l_asset_fin_rec.adjusted_capacity,
1191 X_formula_factor => l_asset_fin_rec.formula_factor,
1192 X_eofy_reserve => l_asset_fin_rec.eofy_reserve,
1193 X_mrc_sob_type_code => l_mrc_sob_type_code,
1194 X_set_of_books_id => l_asset_hdr_rec.set_of_books_id,
1195 X_calling_fn => l_calling_fn,
1196 p_log_level_rec => l_log_level_rec
1197 );
1198
1199 -- Bug 6811554 : To be used in FAEVUPGB.pls
1200 update fa_transaction_headers
1201 set event_id = -2
1202 where transaction_header_id = l_trans_rec.transaction_header_id
1203 and asset_id = l_asset_hdr_rec.asset_id
1204 and book_type_code = l_asset_hdr_rec.book_type_code;
1205
1206 end if;
1207
1208 elsif (l_adj_req_status_tbl(i) = 'TFR') then
1209 -- Catchup transfer by calling FA_TRANSFER_PVT.fadppt
1210
1211 if (l_asset_type_rec.asset_type = 'GROUP' or
1212 l_asset_type_rec.asset_type = 'CAPITALIZED'
1213 ) then
1214
1215 -- Need to populate the distributions tbl
1216 l_dist := 0;
1217 l_asset_dist_tbl.delete;
1218 l_book_type_code := l_book_type_code_tbl(i);
1219 l_asset_id := l_asset_id_tbl(i);
1220 l_transaction_header_id := l_transaction_header_id_tbl(i);
1221
1222 open c_distributions;
1223 loop
1224 l_dist := l_dist + 1;
1225 fetch c_distributions into
1226 l_asset_dist_rec.distribution_id,
1227 l_asset_dist_rec.units_assigned,
1228 l_asset_dist_rec.transaction_units,
1229 l_asset_dist_rec.assigned_to,
1230 l_asset_dist_rec.expense_ccid,
1231 l_asset_dist_rec.location_ccid;
1232 if (c_distributions%NOTFOUND) then
1233 exit;
1234 end if;
1235 l_asset_dist_tbl(l_dist) := l_asset_dist_rec;
1236 end loop;
1237 close c_distributions;
1238
1239 if (l_asset_dist_tbl.count > 0) then
1240 if not FA_TRANSFER_PVT.fadppt
1241 (p_trans_rec => l_trans_rec,
1242 p_asset_hdr_rec => l_asset_hdr_rec,
1243 p_asset_desc_rec => l_asset_desc_rec,
1244 p_asset_cat_rec => l_asset_cat_rec,
1245 p_asset_dist_tbl => l_asset_dist_tbl,
1246 p_log_level_rec => l_log_level_rec) then
1247 raise error_found;
1248 end if;
1249
1250 -- Reflect post catch-up info to fa_books
1251 fa_books_pkg.update_row
1252 (X_asset_id => l_asset_hdr_rec.asset_id,
1253 X_book_type_code => l_asset_hdr_rec.book_type_code,
1254 X_Adjustment_Required_Status
1255 => 'NONE',
1256 X_mrc_sob_type_code => 'P',
1257 X_set_of_books_id => l_asset_hdr_rec.set_of_books_id,
1258 X_calling_fn => l_calling_fn,
1259 p_log_level_rec => l_log_level_rec
1260 );
1261
1262 -- Bug 6811554 : To be used in FAEVUPGB.pls
1263 update fa_transaction_headers
1264 set event_id = -2
1265 where transaction_header_id = l_trans_rec.transaction_header_id
1266 and asset_id = l_asset_hdr_rec.asset_id
1267 and book_type_code = l_asset_hdr_rec.book_type_code;
1268
1269 end if;
1270 end if;
1271 end if;
1272 end loop;
1273
1274 COMMIT;
1275
1276 l_period_rec := null;
1277 l_trans_rec := null;
1278 l_asset_hdr_rec := null;
1279 l_asset_desc_rec := null;
1280 l_asset_cat_rec := null;
1281 l_asset_type_rec := null;
1282 l_asset_fin_rec := null;
1283 l_asset_deprn_rec := null;
1284 l_asset_dist_rec := null;
1285 l_asset_dist_tbl.delete;
1286
1287 l_transaction_header_id_tbl.delete;
1288 l_asset_id_tbl.delete;
1289 l_book_type_code_tbl.delete;
1290 l_adj_req_status_tbl.delete;
1291 l_asset_type_tbl.delete;
1292 l_current_units_tbl.delete;
1293 l_category_id_tbl.delete;
1294 l_transaction_type_code_tbl.delete;
1295 l_set_of_books_id_tbl.delete;
1296 l_transaction_subtype_tbl.delete;
1297 l_group_asset_id_tbl.delete;
1298 l_ann_deprn_rounding_flag_tbl.delete;
1299 l_transaction_date_entered_tbl.delete;
1300 l_transaction_name_tbl.delete;
1301 l_src_thid_tbl.delete;
1302 l_transaction_key_tbl.delete;
1303 l_amortization_start_date_tbl.delete;
1304 l_cost_tbl.delete;
1305 l_adjusted_cost_tbl.delete;
1306 l_recoverable_cost_tbl.delete;
1307 l_reval_amortization_basis_tbl.delete;
1308 l_adjusted_rate_tbl.delete;
1309 l_production_capacity_tbl.delete;
1310 l_adjusted_capacity_tbl.delete;
1311 l_adj_recoverable_cost_tbl.delete;
1312 l_deprn_method_code_tbl.delete;
1313 l_life_in_months_tbl.delete;
1314 l_salvage_value_tbl.delete;
1315 l_depreciate_flag_tbl.delete;
1316 l_ceiling_name_tbl.delete;
1317 l_rate_adjustment_factor_tbl.delete;
1318 l_bonus_rule_tbl.delete;
1319 l_prorate_date_tbl.delete;
1320 l_deprn_start_date_tbl.delete;
1321 l_date_placed_in_service_tbl.delete;
1322 l_short_fiscal_year_flag_tbl.delete;
1323 l_conversion_date_tbl.delete;
1324 l_orig_deprn_start_date_tbl.delete;
1325 l_formula_factor_tbl.delete;
1326 l_eofy_reserve_tbl.delete;
1327 l_asset_number_tbl.delete;
1328 l_deprn_amount_tbl.delete;
1329 l_ytd_deprn_tbl.delete;
1330 l_deprn_reserve_tbl.delete;
1331 l_prior_fy_expense_tbl.delete;
1332 l_bonus_deprn_amount_tbl.delete;
1333 l_bonus_ytd_deprn_tbl .delete;
1334 l_prior_fy_bonus_expense_tbl.delete;
1335 l_reval_amortization_tbl.delete;
1336 l_reval_amortization_basis.delete;
1337 l_reval_deprn_expense_tbl.delete;
1338 l_reval_ytd_deprn_tbl.delete;
1339 l_reval_deprn_reserve_tbl.delete;
1340 l_production_tbl.delete;
1341 l_ytd_production_tbl.delete;
1342 l_ltd_production_tbl.delete;
1343
1344 if (l_rows_processed < l_batch_size) then exit; end if;
1345
1346 end loop;
1347
1348 -- Now run for MRC only if there are mrc books
1349
1350 select count(*)
1351 into l_mrc_books
1352 from fa_mc_book_controls
1353 where enabled_flag = 'Y';
1354
1355 if (l_mrc_books > 0) then
1356
1357 loop
1358
1359 OPEN c_mc_trx;
1360
1361 FETCH c_mc_trx BULK COLLECT
1362 INTO l_transaction_header_id_tbl,
1363 l_asset_id_tbl,
1364 l_book_type_code_tbl,
1365 l_adj_req_status_tbl,
1366 l_asset_type_tbl,
1367 l_current_units_tbl,
1368 l_category_id_tbl,
1369 l_transaction_type_code_tbl,
1370 l_set_of_books_id_tbl,
1371 l_transaction_subtype_tbl,
1372 l_group_asset_id_tbl,
1373 l_ann_deprn_rounding_flag_tbl,
1374 l_transaction_date_entered_tbl,
1375 l_transaction_name_tbl,
1376 l_src_thid_tbl,
1377 l_transaction_key_tbl,
1378 l_amortization_start_date_tbl,
1379 l_cost_tbl,
1380 l_adjusted_cost_tbl,
1381 l_recoverable_cost_tbl,
1382 l_reval_amortization_basis_tbl,
1383 l_adjusted_rate_tbl,
1384 l_production_capacity_tbl,
1385 l_adjusted_capacity_tbl,
1386 l_adj_recoverable_cost_tbl,
1387 l_deprn_method_code_tbl,
1388 l_life_in_months_tbl,
1389 l_salvage_value_tbl,
1390 l_depreciate_flag_tbl,
1391 l_ceiling_name_tbl,
1392 l_rate_adjustment_factor_tbl,
1393 l_bonus_rule_tbl,
1394 l_prorate_date_tbl,
1395 l_deprn_start_date_tbl,
1396 l_date_placed_in_service_tbl,
1397 l_short_fiscal_year_flag_tbl,
1398 l_conversion_date_tbl,
1399 l_orig_deprn_start_date_tbl,
1400 l_formula_factor_tbl,
1401 l_eofy_reserve_tbl,
1402 l_asset_number_tbl,
1403 l_deprn_amount_tbl,
1404 l_ytd_deprn_tbl,
1405 l_deprn_reserve_tbl,
1406 l_prior_fy_expense_tbl,
1407 l_bonus_deprn_amount_tbl,
1408 l_bonus_ytd_deprn_tbl,
1409 l_prior_fy_bonus_expense_tbl,
1410 l_reval_amortization_tbl,
1411 l_reval_amortization_basis,
1412 l_reval_deprn_expense_tbl,
1413 l_reval_ytd_deprn_tbl,
1414 l_reval_deprn_reserve_tbl,
1415 l_production_tbl,
1416 l_ytd_production_tbl,
1417 l_ltd_production_tbl
1418 LIMIT l_batch_size;
1419 CLOSE c_mc_trx;
1420
1421 l_rows_processed := l_transaction_header_id_tbl.count;
1422
1423 for i in 1..l_transaction_header_id_tbl.count loop
1424
1425 fnd_profile.put('GL_SET_OF_BKS_ID', l_set_of_books_id_tbl(i));
1426 fnd_client_info.set_currency_context (l_set_of_books_id_tbl(i));
1427
1428 -- call the cache
1429 if not (fa_cache_pkg.fazcbc(x_book => l_book_type_code_tbl(i))) then
1430 raise error_found;
1431 end if;
1432
1433 l_trans_rec.transaction_header_id := l_transaction_header_id_tbl(i);
1434 l_trans_rec.transaction_type_code := l_transaction_type_code_tbl(i);
1435 l_trans_rec.transaction_date_entered :=
1436 l_transaction_date_entered_tbl(i);
1437 l_trans_rec.transaction_subtype := l_transaction_subtype_tbl(i);
1438 l_trans_rec.deprn_override_flag := fa_std_types.FA_NO_OVERRIDE;
1439 l_trans_rec.transaction_name := l_transaction_name_tbl(i);
1440 l_trans_rec.source_transaction_header_id := l_src_thid_tbl(i);
1441 l_trans_rec.transaction_key := l_transaction_key_tbl(i);
1442 l_trans_rec.amortization_start_date :=
1443 l_amortization_start_date_tbl(i);
1444 l_trans_rec.calling_interface := 'R12 Upgrade';
1445 l_trans_rec.who_info.created_by := c_fnd_user;
1446 l_trans_rec.who_info.creation_date := sysdate;
1447 l_trans_rec.who_info.last_update_date := sysdate;
1448 l_trans_rec.who_info.last_updated_by := c_fnd_user;
1449 l_trans_rec.who_info.last_update_login := c_upgrade_bugno;
1450
1451 l_asset_hdr_rec.asset_id := l_asset_id_tbl(i);
1452 l_asset_hdr_rec.book_type_code := l_book_type_code_tbl(i);
1453 l_asset_hdr_rec.set_of_books_id := l_set_of_books_id_tbl(i);
1454
1455 l_asset_desc_rec.asset_number := l_asset_number_tbl(i);
1456 l_asset_desc_rec.current_units := l_current_units_tbl(i);
1457
1458 l_asset_cat_rec.category_id := l_category_id_tbl(i);
1459
1460 l_asset_type_rec.asset_type := l_asset_type_tbl(i);
1461
1462 l_asset_fin_rec.set_of_books_id := l_set_of_books_id_tbl(i);
1463 l_asset_fin_rec.cost := l_cost_tbl(i);
1464 l_asset_fin_rec.adjusted_cost := l_adjusted_cost_tbl(i);
1465 l_asset_fin_rec.recoverable_cost := l_recoverable_cost_tbl(i);
1466 l_asset_fin_rec.reval_amortization_basis :=
1467 l_reval_amortization_basis_tbl(i);
1468 l_asset_fin_rec.adjusted_rate := l_adjusted_rate_tbl(i);
1469 l_asset_fin_rec.production_capacity := l_production_capacity_tbl(i);
1470 l_asset_fin_rec.adjusted_capacity := l_adjusted_capacity_tbl(i);
1471 l_asset_fin_rec.adjusted_recoverable_cost :=
1472 l_adj_recoverable_cost_tbl(i);
1473 l_asset_fin_rec.deprn_method_code := l_deprn_method_code_tbl(i);
1474 l_asset_fin_rec.life_in_months := l_life_in_months_tbl(i);
1475 l_asset_fin_rec.salvage_value := l_salvage_value_tbl(i);
1476 l_asset_fin_rec.depreciate_flag := l_depreciate_flag_tbl(i);
1477 l_asset_fin_rec.ceiling_name := l_ceiling_name_tbl(i);
1478 l_asset_fin_rec.rate_adjustment_factor :=
1479 l_rate_adjustment_factor_tbl(i);
1480 l_asset_fin_rec.bonus_rule := l_bonus_rule_tbl(i);
1481 l_asset_fin_rec.prorate_date := l_prorate_date_tbl(i);
1482 l_asset_fin_rec.deprn_start_date := l_deprn_start_date_tbl(i);
1483 l_asset_fin_rec.date_placed_in_service :=
1484 l_date_placed_in_service_tbl(i);
1485 l_asset_fin_rec.short_fiscal_year_flag :=
1486 l_short_fiscal_year_flag_tbl(i);
1487 l_asset_fin_rec.conversion_date := l_conversion_date_tbl(i);
1488 l_asset_fin_rec.orig_deprn_start_date :=
1489 l_orig_deprn_start_date_tbl(i);
1490 l_asset_fin_rec.formula_factor := l_formula_factor_tbl(i);
1491 l_asset_fin_rec.eofy_reserve := l_eofy_reserve_tbl(i);
1492 l_asset_fin_rec.group_asset_id := l_group_asset_id_tbl(i);
1493 l_asset_fin_rec.annual_deprn_rounding_flag :=
1494 l_ann_deprn_rounding_flag_tbl(i);
1495
1496 l_asset_deprn_rec.set_of_books_id := l_set_of_books_id_tbl(i);
1497 l_asset_deprn_rec.deprn_amount := l_deprn_amount_tbl(i);
1498 l_asset_deprn_rec.ytd_deprn := l_ytd_deprn_tbl(i);
1499 l_asset_deprn_rec.deprn_reserve := l_deprn_reserve_tbl(i);
1500 l_asset_deprn_rec.prior_fy_expense := l_prior_fy_expense_tbl(i);
1501 l_asset_deprn_rec.bonus_deprn_amount := l_bonus_deprn_amount_tbl(i);
1502 l_asset_deprn_rec.bonus_ytd_deprn := l_bonus_ytd_deprn_tbl(i);
1503 l_asset_deprn_rec.prior_fy_bonus_expense :=
1504 l_prior_fy_bonus_expense_tbl(i);
1505 l_asset_deprn_rec.reval_amortization := l_reval_amortization_tbl(i);
1506 l_asset_deprn_rec.reval_amortization_basis :=
1507 l_reval_amortization_basis(i);
1508 l_asset_deprn_rec.reval_deprn_expense := l_reval_deprn_expense_tbl(i);
1509 l_asset_deprn_rec.reval_ytd_deprn := l_reval_ytd_deprn_tbl(i);
1510 l_asset_deprn_rec.reval_deprn_reserve := l_reval_deprn_reserve_tbl(i);
1511 l_asset_deprn_rec.production := l_production_tbl(i);
1512 l_asset_deprn_rec.ytd_production := l_ytd_production_tbl(i);
1513 l_asset_deprn_rec.ltd_production := l_ltd_production_tbl(i);
1514
1515 l_mrc_sob_type_code := 'R';
1516
1517 if (NOT FA_UTIL_PVT.get_period_rec (
1518 p_book => l_asset_hdr_rec.book_type_code,
1519 p_effective_date => NULL,
1520 x_period_rec => l_period_rec,
1521 p_log_level_rec => p_log_level_rec)) then
1522 raise error_found;
1523 end if;
1524
1525 if (l_adj_req_status_tbl(i) = 'ADD') then
1526 -- Catchup addition by calling FA_EXP_PVT.faxexp
1527
1528 if ((nvl(l_trans_rec.transaction_subtype, 'EXPENSED') <>
1529 'AMORTIZED') and
1530 (l_asset_type_rec.asset_type = 'CAPITALIZED') and
1531 (l_asset_fin_rec.group_asset_id is null) and
1532 (nvl(l_asset_fin_rec.annual_deprn_rounding_flag,'ADD') <>
1533 'RES') and
1534 (l_trans_rec.transaction_date_entered <
1535 l_period_rec.calendar_period_open_date)
1536 ) then
1537
1538 if not FA_EXP_PVT.faxexp
1539 (px_trans_rec => l_trans_rec,
1540 p_asset_hdr_rec => l_asset_hdr_rec,
1541 p_asset_desc_rec => l_asset_desc_rec,
1542 p_asset_cat_rec => l_asset_cat_rec,
1543 p_asset_type_rec => l_asset_type_rec,
1544 p_asset_fin_rec_old => l_asset_fin_rec_null,
1545 px_asset_fin_rec_new => l_asset_fin_rec,
1546 p_asset_deprn_rec => l_asset_deprn_rec,
1547 p_period_rec => l_period_rec,
1548 p_mrc_sob_type_code => l_mrc_sob_type_code,
1549 p_running_mode => fa_std_types.FA_DPR_NORMAL,
1550 p_used_by_revaluation => null,
1551 x_deprn_exp => l_deprn_exp,
1552 x_bonus_deprn_exp => l_bonus_deprn_exp,
1553 x_impairment_exp => l_impairment_exp,
1554 x_ann_adj_deprn_exp => l_ann_adj_deprn_exp,
1555 x_ann_adj_bonus_deprn_exp
1556 => l_ann_adj_bonus_deprn_exp,
1557 p_log_level_rec => l_log_level_rec) then
1558 raise error_found;
1559 end if;
1560
1561 if not FA_INS_ADJ_PVT.faxiat
1562 (p_trans_rec => l_trans_rec,
1563 p_asset_hdr_rec => l_asset_hdr_rec,
1564 p_asset_desc_rec => l_asset_desc_rec,
1565 p_asset_cat_rec => l_asset_cat_rec,
1566 p_asset_type_rec => l_asset_type_rec,
1567 p_cost => 0,
1568 p_clearing => 0,
1569 p_deprn_expense => l_deprn_exp,
1570 p_bonus_expense => l_bonus_deprn_exp,
1571 p_impair_expense => l_impairment_exp,
1572 p_deprn_reserve => 0,
1573 p_bonus_reserve => 0,
1574 p_ann_adj_amt => l_ann_adj_deprn_exp,
1575 p_mrc_sob_type_code => l_mrc_sob_type_code,
1576 p_calling_fn => l_calling_fn,
1577 p_log_level_rec => l_log_level_rec
1578 ) then raise error_found;
1579 end if;
1580
1581 -- Reflect post catch-up info to fa_books
1582 fa_books_pkg.update_row
1583 (X_asset_id => l_asset_hdr_rec.asset_id,
1584 X_book_type_code => l_asset_hdr_rec.book_type_code,
1585 X_Adjustment_Required_Status
1586 => 'NONE',
1587 X_rate_adjustment_factor
1588 => l_asset_fin_rec.rate_adjustment_factor,
1589 X_reval_amortization_basis
1590 =>
1591 l_asset_fin_rec.reval_amortization_basis,
1592 X_adjusted_cost => l_asset_fin_rec.adjusted_cost,
1593 X_adjusted_capacity => l_asset_fin_rec.adjusted_capacity,
1594 X_formula_factor => l_asset_fin_rec.formula_factor,
1595 X_eofy_reserve => l_asset_fin_rec.eofy_reserve,
1596 X_mrc_sob_type_code => l_mrc_sob_type_code,
1597 X_set_of_books_id => l_asset_hdr_rec.set_of_books_id,
1598 X_calling_fn => l_calling_fn,
1599 p_log_level_rec => l_log_level_rec
1600 );
1601 end if;
1602 end if;
1603 end loop;
1604
1605 COMMIT;
1606
1607 l_period_rec := null;
1608 l_trans_rec := null;
1609 l_asset_hdr_rec := null;
1610 l_asset_desc_rec := null;
1611 l_asset_cat_rec := null;
1612 l_asset_type_rec := null;
1613 l_asset_fin_rec := null;
1614 l_asset_deprn_rec := null;
1615 l_asset_dist_rec := null;
1616 l_asset_dist_tbl.delete;
1617
1618 l_transaction_header_id_tbl.delete;
1619 l_asset_id_tbl.delete;
1620 l_book_type_code_tbl.delete;
1621 l_adj_req_status_tbl.delete;
1622 l_asset_type_tbl.delete;
1623 l_current_units_tbl.delete;
1624 l_category_id_tbl.delete;
1625 l_transaction_type_code_tbl.delete;
1626 l_set_of_books_id_tbl.delete;
1627 l_transaction_subtype_tbl.delete;
1628 l_group_asset_id_tbl.delete;
1629 l_ann_deprn_rounding_flag_tbl.delete;
1630 l_transaction_date_entered_tbl.delete;
1631 l_transaction_name_tbl.delete;
1632 l_src_thid_tbl.delete;
1633 l_transaction_key_tbl.delete;
1634 l_amortization_start_date_tbl.delete;
1635 l_cost_tbl.delete;
1636 l_adjusted_cost_tbl.delete;
1637 l_recoverable_cost_tbl.delete;
1638 l_reval_amortization_basis_tbl.delete;
1639 l_adjusted_rate_tbl.delete;
1640 l_production_capacity_tbl.delete;
1641 l_adjusted_capacity_tbl.delete;
1642 l_adj_recoverable_cost_tbl.delete;
1643 l_deprn_method_code_tbl.delete;
1644 l_life_in_months_tbl.delete;
1645 l_salvage_value_tbl.delete;
1646 l_depreciate_flag_tbl.delete;
1647 l_ceiling_name_tbl.delete;
1648 l_rate_adjustment_factor_tbl.delete;
1649 l_bonus_rule_tbl.delete;
1650 l_prorate_date_tbl.delete;
1651 l_deprn_start_date_tbl.delete;
1652 l_date_placed_in_service_tbl.delete;
1653 l_short_fiscal_year_flag_tbl.delete;
1654 l_conversion_date_tbl.delete;
1655 l_orig_deprn_start_date_tbl.delete;
1656 l_formula_factor_tbl.delete;
1657 l_eofy_reserve_tbl.delete;
1658 l_asset_number_tbl.delete;
1659 l_deprn_amount_tbl.delete;
1660 l_ytd_deprn_tbl.delete;
1661 l_deprn_reserve_tbl.delete;
1662 l_prior_fy_expense_tbl.delete;
1663 l_bonus_deprn_amount_tbl.delete;
1664 l_bonus_ytd_deprn_tbl .delete;
1665 l_prior_fy_bonus_expense_tbl.delete;
1666 l_reval_amortization_tbl.delete;
1667 l_reval_amortization_basis.delete;
1668 l_reval_deprn_expense_tbl.delete;
1669 l_reval_ytd_deprn_tbl.delete;
1670 l_reval_deprn_reserve_tbl.delete;
1671 l_production_tbl.delete;
1672 l_ytd_production_tbl.delete;
1673 l_ltd_production_tbl.delete;
1674
1675 if (l_rows_processed < l_batch_size) then exit; end if;
1676
1677 end loop;
1678
1679 end if;
1680
1681
1682 EXCEPTION
1683 when error_found then
1684 if (c_trx%isopen) then
1685 close c_trx;
1686 elsif (c_mc_trx%isopen) then
1687 close c_mc_trx;
1688 end if;
1689
1690 rollback;
1691 raise;
1692
1693 when others then
1694 if (c_trx%isopen) then
1695 close c_trx;
1696 elsif (c_mc_trx%isopen) then
1697 close c_mc_trx;
1698 end if;
1699
1700 rollback;
1701 raise;
1702
1703 END Upgrade_Backdated_Trxns;
1704
1705 Procedure Upgrade_Invoices (
1706 p_book_type_code IN varchar2,
1707 p_start_rowid IN rowid,
1708 p_end_rowid IN rowid,
1709 p_batch_size IN number,
1710 x_success_count OUT NOCOPY number,
1711 x_failure_count OUT NOCOPY number,
1712 x_return_status OUT NOCOPY number
1713 ,p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
1714
1715 -- this value can be altered in order to process more of less per batch
1716 l_batch_size NUMBER;
1717
1718 l_rows_processed NUMBER;
1719
1720 -- type for table variable
1721 type num_tbl_type is table of number index by binary_integer;
1722 type char_tbl_type is table of varchar2(150) index by binary_integer;
1723 type date_tbl_type is table of date index by binary_integer;
1724 type rowid_tbl_type is table of rowid index by binary_integer;
1725
1726 -- used for bulk fetching
1727 -- main cursor
1728 l_adj_rowid_tbl rowid_tbl_type;
1729 l_source_line_id_tbl num_tbl_type;
1730 l_source_dest_code_tbl char_tbl_type;
1731 l_asset_id_tbl num_tbl_type;
1732 l_book_type_code_tbl char_tbl_type;
1733 l_distribution_id_tbl num_tbl_type;
1734 l_transaction_header_id_tbl num_tbl_type;
1735 l_source_type_code_tbl char_tbl_type;
1736 l_adjustment_type_tbl char_tbl_type;
1737 l_debit_credit_flag_tbl char_tbl_type;
1738 l_code_combination_id_tbl num_tbl_type;
1739
1740 cursor c_invoices is
1741 select /*+ leading(th) rowid(th) */
1742 adj.rowid,
1743 ai.source_line_id,
1744 mult.source_dest_code,
1745 adj.asset_id,
1746 adj.book_type_code,
1747 adj.distribution_id,
1748 adj.transaction_header_id,
1749 adj.source_type_code,
1750 adj.adjustment_type,
1751 adj.debit_credit_flag,
1752 adj.code_combination_id
1753 from fa_transaction_headers th,
1754 fa_book_controls bc,
1755 fa_deprn_periods dp,
1756 fa_adjustments adj,
1757 fa_asset_invoices ai,
1758 fa_invoice_transactions it,
1759 (select 'SOURCE' source_dest_code from dual union all
1760 select 'DEST' source_dest_code from dual) mult
1761 where th.rowid between p_start_rowid and p_end_rowid
1762 and bc.book_type_code = th.book_type_code
1763 and nvl(bc.date_ineffective, sysdate + 1) > sysdate
1764 and bc.book_type_code = dp.book_type_code
1765 and dp.period_close_date is null
1766 and th.date_effective > dp.period_open_date
1767 and th.transaction_header_id = adj.transaction_header_id
1768 and th.asset_id = adj.asset_id
1769 and th.book_type_code = adj.book_type_code
1770 and adj.asset_id = ai.asset_id
1771 and adj.asset_invoice_id = ai.asset_invoice_id
1772 and it.invoice_transaction_id =
1773 decode(mult.source_dest_code,
1774 'SOURCE', ai.invoice_transaction_id_out,
1775 'DEST' , ai.invoice_transaction_id_in)
1776 and th.invoice_transaction_id = it.invoice_transaction_id
1777 and th.book_type_code = it.book_type_code
1778 and it.transaction_type = 'INVOICE TRANSFER'
1779 and adj.source_line_id is null;
1780
1781 cursor c_transfers is
1782 select /*+ leading(th) rowid(th) */
1783 adj.rowid,
1784 mult.source_dest_code,
1785 adj.asset_id,
1786 adj.book_type_code,
1787 adj.distribution_id,
1788 adj.transaction_header_id,
1789 adj.source_type_code,
1790 adj.adjustment_type,
1791 adj.debit_credit_flag,
1792 adj.code_combination_id
1793 from fa_transaction_headers th,
1794 fa_book_controls bc,
1795 fa_deprn_periods dp,
1796 fa_adjustments adj,
1797 fa_distribution_history dh,
1798 (select 'SOURCE' source_dest_code from dual union all
1799 select 'DEST' source_dest_code from dual) mult
1800 where th.rowid between p_start_rowid and p_end_rowid
1801 and bc.book_type_code = th.book_type_code
1802 and nvl(bc.date_ineffective, sysdate + 1) > sysdate
1803 and bc.book_type_code = dp.book_type_code
1804 and dp.period_close_date is null
1805 and th.date_effective > dp.period_open_date
1806 and th.transaction_header_id = adj.transaction_header_id
1807 and th.asset_id = adj.asset_id
1808 and th.book_type_code = adj.book_type_code
1809 and adj.asset_id = dh.asset_id
1810 and adj.book_type_code = dh.book_type_code
1811 and adj.distribution_id = dh.distribution_id
1812 and adj.transaction_header_id =
1813 decode (mult.source_dest_code,
1814 'SOURCE', dh.transaction_header_id_out,
1815 'DEST' , dh.transaction_header_id_in)
1816 and adj.source_dest_code is null;
1817
1818 BEGIN
1819
1820 l_batch_size := nvl(nvl(p_batch_size, fa_cache_pkg.fa_batch_size), 1000);
1821
1822 loop
1823
1824 open c_invoices;
1825 fetch c_invoices bulk collect into
1826 l_adj_rowid_tbl,
1827 l_source_line_id_tbl,
1828 l_source_dest_code_tbl,
1829 l_asset_id_tbl,
1830 l_book_type_code_tbl,
1831 l_distribution_id_tbl,
1832 l_transaction_header_id_tbl,
1833 l_source_type_code_tbl,
1834 l_adjustment_type_tbl,
1835 l_debit_credit_flag_tbl,
1836 l_code_combination_id_tbl
1837 limit l_batch_size;
1838 close c_invoices;
1839
1840 l_rows_processed := l_adj_rowid_tbl.count;
1841
1842 forall i in 1..l_adj_rowid_tbl.count
1843 update fa_adjustments
1844 set source_line_id = l_source_line_id_tbl(i),
1845 source_dest_code = l_source_dest_code_tbl(i)
1846 where rowid = l_adj_rowid_tbl(i);
1847
1848 forall i in 1..l_adj_rowid_tbl.count
1849 update fa_mc_adjustments
1850 set source_line_id = l_source_line_id_tbl(i),
1851 source_dest_code = l_source_dest_code_tbl(i)
1852 where asset_id = l_asset_id_tbl(i)
1853 and book_type_code = l_book_type_code_tbl(i)
1854 and distribution_id = l_distribution_id_tbl(i)
1855 and transaction_header_id = l_transaction_header_id_tbl(i)
1856 and source_type_code = l_source_type_code_tbl(i)
1857 and adjustment_type = l_adjustment_type_tbl(i)
1858 and debit_credit_flag = l_debit_credit_flag_tbl(i)
1859 and code_combination_id = l_code_combination_id_tbl(i);
1860
1861 commit;
1862
1863 l_adj_rowid_tbl.delete;
1864 l_source_line_id_tbl.delete;
1865 l_asset_id_tbl.delete;
1866 l_book_type_code_tbl.delete;
1867 l_distribution_id_tbl.delete;
1868 l_transaction_header_id_tbl.delete;
1869 l_source_type_code_tbl.delete;
1870 l_adjustment_type_tbl.delete;
1871 l_debit_credit_flag_tbl.delete;
1872 l_code_combination_id_tbl.delete;
1873
1874 if (l_rows_processed < l_batch_size) then exit; end if;
1875
1876 end loop;
1877
1878 loop
1879
1880 open c_transfers;
1881 fetch c_transfers bulk collect into
1882 l_adj_rowid_tbl,
1883 l_source_dest_code_tbl,
1884 l_asset_id_tbl,
1885 l_book_type_code_tbl,
1886 l_distribution_id_tbl,
1887 l_transaction_header_id_tbl,
1888 l_source_type_code_tbl,
1889 l_adjustment_type_tbl,
1890 l_debit_credit_flag_tbl,
1891 l_code_combination_id_tbl
1892 limit l_batch_size;
1893 close c_transfers;
1894
1895 l_rows_processed := l_adj_rowid_tbl.count;
1896
1897 forall i in 1..l_adj_rowid_tbl.count
1898 update fa_adjustments
1899 set source_dest_code = l_source_dest_code_tbl(i)
1900 where rowid = l_adj_rowid_tbl(i);
1901
1902 forall i in 1..l_adj_rowid_tbl.count
1903 update fa_mc_adjustments
1904 set source_dest_code = l_source_dest_code_tbl(i)
1905 where asset_id = l_asset_id_tbl(i)
1906 and book_type_code = l_book_type_code_tbl(i)
1907 and distribution_id = l_distribution_id_tbl(i)
1908 and transaction_header_id = l_transaction_header_id_tbl(i)
1909 and source_type_code = l_source_type_code_tbl(i)
1910 and adjustment_type = l_adjustment_type_tbl(i)
1911 and debit_credit_flag = l_debit_credit_flag_tbl(i)
1912 and code_combination_id = l_code_combination_id_tbl(i);
1913
1914 commit;
1915
1916 l_adj_rowid_tbl.delete;
1917 l_source_line_id_tbl.delete;
1918 l_asset_id_tbl.delete;
1919 l_book_type_code_tbl.delete;
1920 l_distribution_id_tbl.delete;
1921 l_transaction_header_id_tbl.delete;
1922 l_source_type_code_tbl.delete;
1923 l_adjustment_type_tbl.delete;
1924 l_debit_credit_flag_tbl.delete;
1925 l_code_combination_id_tbl.delete;
1926
1927 if (l_rows_processed < l_batch_size) then exit; end if;
1928
1929 end loop;
1930
1931 END Upgrade_Invoices;
1932
1933 END FA_SLA_CURRENT_PERIOD_UPG_PKG;