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