[Home] [Help]
PACKAGE BODY: APPS.FA_MC_UPG2_PKG
Source
1 PACKAGE BODY FA_MC_UPG2_PKG AS
2 /* $Header: faxmcu2b.pls 120.7 2009/08/04 19:56:34 bridgway ship $ */
3
4 g_print_debug boolean := fa_cache_pkg.fa_print_debug;
5
6 PROCEDURE convert_books(
7 p_rsob_id IN NUMBER,
8 p_book_type_code IN VARCHAR2,
9 p_numerator_rate IN NUMBER,
10 p_denominator_rate IN NUMBER,
11 p_mau IN NUMBER,
12 p_precision IN NUMBER) IS
13
14 l_count number;
15 /* ************************************************************************
16 This procedure will select all the rows in fa_books for each asset being
17 converted and insert them into fa_mc_books. All amount columns will be
18 converted either with the exchange rate or a derived rate of
19 cost/primary_cur_cost based on the conversion basis of R or C.
20 The converted amounts will also be rounded using the precision and
21 minimum accountable unti of the reporting currency
22 ************************************************************************ */
23
24 BEGIN
25
26 if (g_print_debug) then
27 fa_debug_pkg.add('convert_assets',
28 'Converting FA_BOOKS records',
29 'start');
30 end if;
31
32 INSERT INTO fa_mc_books(
33 set_of_books_id,
34 asset_id,
35 book_type_code,
36 transaction_header_id_in,
37 transaction_header_id_out,
38 adjusted_cost,
39 cost,
40 source_cost,
41 original_cost,
42 source_original_cost,
43 salvage_value,
44 adjustment_required_status,
45 retirement_pending_flag,
46 last_update_date,
47 last_updated_by,
48 itc_amount,
49 itc_basis,
50 recoverable_cost,
51 last_update_login,
52 reval_ceiling,
53 period_counter_fully_reserved,
54 unrevalued_cost,
55 allowed_deprn_limit_amount,
56 period_counter_lIFe_complete,
57 adjusted_recoverable_cost,
58 converted_flag,
59 annual_deprn_rounding_flag,
60 itc_amount_id,
61 retirement_id,
62 tax_request_id,
63 basic_rate,
64 adjusted_rate,
65 bonus_rule,
66 ceiling_name,
67 adjusted_capacity,
68 fully_rsvd_revals_counter,
69 idled_flag,
70 period_counter_capitalized,
71 period_counter_fully_retired,
72 production_capacity,
73 unit_of_measure,
74 percent_salvage_value,
75 allowed_deprn_limit,
76 annual_rounding_flag,
77 global_attribute1,
78 global_attribute2,
79 global_attribute3,
80 global_attribute4,
81 global_attribute5,
82 global_attribute6,
83 global_attribute7,
84 global_attribute8,
85 global_attribute9,
86 global_attribute10,
87 global_attribute11,
88 global_attribute12,
89 global_attribute13,
90 global_attribute14,
91 global_attribute15,
92 global_attribute16,
93 global_attribute17,
94 global_attribute18,
95 global_attribute19,
96 global_attribute20,
97 global_attribute_category,
98 date_placed_in_service,
99 date_effective,
100 deprn_start_date,
101 deprn_method_code,
102 life_in_months,
103 rate_adjustment_factor,
104 prorate_convention_code,
105 prorate_date,
106 cost_change_flag,
107 capitalize_flag,
108 depreciate_flag,
109 date_ineffective,
110 conversion_date,
111 original_deprn_start_date,
112 salvage_type,
113 deprn_limit_type,
114 allocate_to_fully_ret_flag,
115 allocate_to_fully_rsv_flag,
116 cash_generating_unit_id,
117 depreciation_option,
118 disabled_flag,
119 eofy_formula_factor,
120 eop_formula_factor,
121 excess_allocation_option,
122 exclude_fully_rsv_flag,
123 exclude_proceeds_from_basis,
124 formula_factor,
125 group_asset_id,
126 limit_proceeds_flag,
127 member_rollup_flag,
128 old_adjusted_capacity,
129 over_depreciate_option,
130 recapture_reserve_flag,
131 recognize_gain_loss,
132 reduce_addition_flag,
133 reduce_adjustment_flag,
134 reduce_retirement_flag,
135 reduction_rate,
136 remaining_life1,
137 remaining_life2,
138 retirement_deprn_option,
139 short_fiscal_year_flag,
140 super_group_id,
141 terminal_gain_loss,
142 terminal_gain_loss_flag,
143 tracking_method
144 )
145 SELECT p_rsob_id,
146 bk.asset_id,
147 bk.book_type_code,
148 bk.transaction_header_id_in,
149 bk.transaction_header_id_out,
150 DECODE(p_mau,
151 NULL, ROUND(
152 DECODE(cr.conversion_basis,
153 'C', (bk.adjusted_cost *
154 (cr.cost/cr.primary_cur_cost)),
155
156 'R', (bk.adjusted_cost/
157 p_denominator_rate)
158 *
159 DECODE(cr.exchange_rate,
160 NULL, p_numerator_rate,
161 cr.exchange_rate)
162 ), p_precision),
163 ROUND(
164 DECODE(cr.conversion_basis,
165 'C', (bk.adjusted_cost *
166 (cr.cost/cr.primary_cur_cost)),
167 'R', (bk.adjusted_cost/
168 p_denominator_rate)
169 *
170 DECODE(cr.exchange_rate,
171 NULL, p_numerator_rate,
172 cr.exchange_rate))
173 / p_mau) * p_mau),
174 DECODE(p_mau,
175 NULL, ROUND(
176 DECODE(cr.conversion_basis,
177 'C', (bk.cost *
178 (cr.cost/cr.primary_cur_cost)),
179
180 'R', (bk.cost/
181 p_denominator_rate)
182 *
183 DECODE(cr.exchange_rate,
184 NULL, p_numerator_rate,
185 cr.exchange_rate)
186 ), p_precision),
187 ROUND(
188 DECODE(cr.conversion_basis,
189 'C', (bk.cost *
190 (cr.cost/cr.primary_cur_cost)),
191 'R', (bk.cost/
192 p_denominator_rate)
193 *
194 DECODE(cr.exchange_rate,
195 NULL, p_numerator_rate,
196 cr.exchange_rate))
197 / p_mau) * p_mau),
198 bk.cost,
199 DECODE(p_mau,
200 NULL, ROUND(
201 DECODE(cr.conversion_basis,
202 'C', (bk.original_cost *
203 (cr.cost/cr.primary_cur_cost)),
204 'R', (bk.original_cost/
205 p_denominator_rate)
206 *
207 DECODE(cr.exchange_rate,
208 NULL, p_numerator_rate,
209 cr.exchange_rate)
210 ), p_precision),
211 ROUND(
212 DECODE(cr.conversion_basis,
213 'C', (bk.original_cost *
214 (cr.cost/cr.primary_cur_cost)),
215 'R', (bk.original_cost/
216 p_denominator_rate)
217 *
218 DECODE(cr.exchange_rate,
219 NULL, p_numerator_rate,
220 cr.exchange_rate))
221 / p_mau) * p_mau),
222
223 bk.original_cost,
224 DECODE(p_mau,
225 NULL, ROUND(
226 DECODE(cr.conversion_basis,
227 'C', (bk.salvage_value *
228 (cr.cost/cr.primary_cur_cost)),
229 'R', (bk.salvage_value/
230 p_denominator_rate)
231 *
232 DECODE(cr.exchange_rate,
233 NULL, p_numerator_rate,
234 cr.exchange_rate)
235 ), p_precision),
236 ROUND(
237 DECODE(cr.conversion_basis,
238 'C', (bk.salvage_value *
239 (cr.cost/cr.primary_cur_cost)),
240 'R', (bk.salvage_value/
241 p_denominator_rate)
242 *
243 DECODE(cr.exchange_rate,
244 NULL, p_numerator_rate,
245 cr.exchange_rate))
246 / p_mau) * p_mau),
247 bk.adjustment_required_status,
248 bk.retirement_pending_flag,
249 bk.last_update_date,
250 bk.last_updated_by,
251 DECODE(p_mau,
252 NULL, ROUND(
253 DECODE(cr.conversion_basis,
254 'C', (bk.itc_amount *
255 (cr.cost/cr.primary_cur_cost)),
256 'R', (bk.itc_amount /
257 p_denominator_rate )
258 *
259 DECODE(cr.exchange_rate,
260 NULL, p_numerator_rate,
261 cr.exchange_rate)
262 ), p_precision),
263 ROUND(
264 DECODE(cr.conversion_basis,
265 'C', (bk.itc_amount *
266 (cr.cost/cr.primary_cur_cost)),
267 'R', (bk.itc_amount/
268 p_denominator_rate)
269 *
270 DECODE(cr.exchange_rate,
271 NULL, p_numerator_rate,
272 cr.exchange_rate))
273 / p_mau) * p_mau),
274 bk.itc_basis,
275 DECODE(p_mau,
276 NULL, ROUND(
277 DECODE(cr.conversion_basis,
278 'C', (bk.recoverable_cost *
279 (cr.cost/cr.primary_cur_cost)),
280 'R', (bk.recoverable_cost/
281 p_denominator_rate)
282 *
283 DECODE(cr.exchange_rate,
284 NULL, p_numerator_rate,
285 cr.exchange_rate)
286 ), p_precision),
287 ROUND(
288 DECODE(cr.conversion_basis,
289 'C', (bk.recoverable_cost *
290 (cr.cost/cr.primary_cur_cost)),
291 'R', (bk.recoverable_cost/
292 p_denominator_rate)
293 *
294 DECODE(cr.exchange_rate,
295 NULL, p_numerator_rate,
296 cr.exchange_rate))
297 / p_mau) * p_mau),
298
299 bk.last_update_login,
300 DECODE(p_mau,
301 NULL, ROUND(
302 DECODE(cr.conversion_basis,
303 'C', (bk.reval_ceiling *
304 (cr.cost/cr.primary_cur_cost)),
305 'R', (bk.reval_ceiling/
306 p_denominator_rate)
307 *
308 DECODE(cr.exchange_rate,
309 NULL, p_numerator_rate,
310 cr.exchange_rate)
311 ), p_precision),
312 ROUND(
313 DECODE(cr.conversion_basis,
314 'C', (bk.reval_ceiling *
315 (cr.cost/cr.primary_cur_cost)),
316 'R', (bk.reval_ceiling/
317 p_denominator_rate)
318 *
319 DECODE(cr.exchange_rate,
320 NULL, p_numerator_rate,
321 cr.exchange_rate))
322 / p_mau) * p_mau),
323 bk.period_counter_fully_reserved,
324 DECODE(p_mau,
325 NULL, ROUND(
326 DECODE(cr.conversion_basis,
327 'C', (bk.unrevalued_cost *
328 (cr.cost/cr.primary_cur_cost)),
329 'R', bk.unrevalued_cost/
330 p_denominator_rate
331 *
332 DECODE(cr.exchange_rate,
333 NULL, p_numerator_rate,
334 cr.exchange_rate)
335 ), p_precision),
336 ROUND(
337 DECODE(cr.conversion_basis,
338 'C', (bk.unrevalued_cost *
339 (cr.cost/cr.primary_cur_cost)),
340 'R', (bk.unrevalued_cost/
341 p_denominator_rate)
342 *
343 DECODE(cr.exchange_rate,
344 NULL, p_numerator_rate,
345 cr.exchange_rate))
346 / p_mau) * p_mau),
347
348 DECODE(p_mau,
349 NULL, ROUND(
350 DECODE(cr.conversion_basis,
351 'C', (bk.allowed_deprn_limit_amount *
352 (cr.cost/cr.primary_cur_cost)),
353
354 'R', (bk.allowed_deprn_limit_amount/
355 p_denominator_rate)
356 *
357 DECODE(cr.exchange_rate,
358 NULL, p_numerator_rate,
359 cr.exchange_rate)
360 ), p_precision),
361 ROUND(
362 DECODE(cr.conversion_basis,
363 'C', (bk.allowed_deprn_limit_amount *
364 (cr.cost/cr.primary_cur_cost)),
365 'R', (bk.allowed_deprn_limit_amount/
366 p_denominator_rate)
367 *
368 DECODE(cr.exchange_rate,
369 NULL, p_numerator_rate,
370 cr.exchange_rate))
371 / p_mau) * p_mau),
372 bk.period_counter_lIFe_complete,
373 DECODE(p_mau,
374 NULL, ROUND(
375 DECODE(cr.conversion_basis,
376 'C', (bk.adjusted_recoverable_cost *
377 (cr.cost/cr.primary_cur_cost)),
378
379 'R', (bk.adjusted_recoverable_cost/
380 p_denominator_rate)
381 *
382 DECODE(cr.exchange_rate,
383 NULL, p_numerator_rate,
384 cr.exchange_rate)
385 ), p_precision),
386 ROUND(
387 DECODE(cr.conversion_basis,
388 'C', (bk.adjusted_recoverable_cost *
389 (cr.cost/cr.primary_cur_cost)),
390 'R', (bk.adjusted_recoverable_cost/
391 p_denominator_rate)
392 *
393 DECODE(cr.exchange_rate,
394 NULL, p_numerator_rate,
395 cr.exchange_rate))
396 / p_mau) * p_mau),
397 'Y',
398 bk.annual_deprn_rounding_flag,
399 bk.itc_amount_id,
400 bk.retirement_id,
401 bk.tax_request_id,
402 bk.basic_rate,
403 bk.adjusted_rate,
404 bk.bonus_rule,
405 bk.ceiling_name,
406 bk.adjusted_capacity,
407 bk.fully_rsvd_revals_counter,
408 bk.idled_flag,
409 bk.period_counter_capitalized,
410 bk.period_counter_fully_retired,
411 bk.production_capacity,
412 bk.unit_of_measure,
413 bk.percent_salvage_value,
414 bk.allowed_deprn_limit,
415 bk.annual_rounding_flag,
416 bk.global_attribute1,
417 bk.global_attribute2,
418 bk.global_attribute3,
419 bk.global_attribute4,
420 bk.global_attribute5,
421 bk.global_attribute6,
422 bk.global_attribute7,
423 bk.global_attribute8,
424 bk.global_attribute9,
425 bk.global_attribute10,
426 bk.global_attribute11,
427 bk.global_attribute12,
428 bk.global_attribute13,
429 bk.global_attribute14,
430 bk.global_attribute15,
431 bk.global_attribute16,
432 bk.global_attribute17,
433 bk.global_attribute18,
434 bk.global_attribute19,
435 bk.global_attribute20,
436 bk.global_attribute_category,
437 bk.date_placed_in_service,
438 bk.date_effective,
439 bk.deprn_start_date,
440 bk.deprn_method_code,
441 bk.life_in_months,
442 bk.rate_adjustment_factor,
443 bk.prorate_convention_code,
444 bk.prorate_date,
445 bk.cost_change_flag,
446 bk.capitalize_flag,
447 bk.depreciate_flag,
448 bk.date_ineffective,
449 bk.conversion_date,
450 bk.original_deprn_start_date,
451 bk.salvage_type,
452 bk.deprn_limit_type,
453 bk.allocate_to_fully_ret_flag,
454 bk.allocate_to_fully_rsv_flag,
455 bk.cash_generating_unit_id,
456 bk.depreciation_option,
457 bk.disabled_flag,
458 bk.eofy_formula_factor,
459 bk.eop_formula_factor,
460 bk.excess_allocation_option,
461 bk.exclude_fully_rsv_flag,
462 bk.exclude_proceeds_from_basis,
463 bk.formula_factor,
464 bk.group_asset_id,
465 bk.limit_proceeds_flag,
466 bk.member_rollup_flag,
467 bk.old_adjusted_capacity,
468 bk.over_depreciate_option,
469 bk.recapture_reserve_flag,
470 bk.recognize_gain_loss,
471 bk.reduce_addition_flag,
472 bk.reduce_adjustment_flag,
473 bk.reduce_retirement_flag,
474 bk.reduction_rate,
475 bk.remaining_life1,
476 bk.remaining_life2,
477 bk.retirement_deprn_option,
478 bk.short_fiscal_year_flag,
479 bk.super_group_id,
480 bk.terminal_gain_loss,
481 bk.terminal_gain_loss_flag,
482 bk.tracking_method
483 FROM
484 fa_books bk,
485 fa_mc_conversion_rates cr
486 WHERE
487 cr.asset_id = bk.asset_id AND
488 cr.set_of_books_id = p_rsob_id AND
489 cr.book_type_code = p_book_type_code AND
490 bk.book_type_code = cr.book_type_code AND
491 cr.status = 'S';
492
493 if (g_print_debug) then
494 fa_debug_pkg.add('convert_assets',
495 'Converted FA_BOOKS records',
496 'success');
497 end if;
498
499
500 EXCEPTION
501 WHEN OTHERS THEN
502 fa_srvr_msg.add_sql_error (
503 calling_fn => 'fa_mc_upg_pkg2.convert_books');
504
505 app_exception.raise_exception;
506
507 END convert_books;
508
509
510 PROCEDURE convert_invoices (
511 p_rsob_id IN NUMBER,
512 p_book_type_code IN VARCHAR2,
513 p_numerator_rate IN NUMBER,
514 p_denominator_rate IN NUMBER,
515 p_mau IN NUMBER,
516 p_precision IN NUMBER) IS
517 /* ************************************************************************
518 This procedure will select all the rows in fa_asset_invoices for each
519 asset being converted and insert them into fa_mc_asset_invoices.
520 All amount columns will be
521 converted either with the exchange rate or a derived rate of
522 cost/primary_cur_cost based on the conversion basis of R or C.
523 The converted amounts will also be rounded using the precision and
524 minimum accountable unti of the reporting currency
525 ************************************************************************ */
526
527 l_book_class varchar2(15);
528
529 BEGIN
530
531 if (g_print_debug) then
532 fa_debug_pkg.add('convert_assets',
533 'Converting FA_ASSET_INVOICES records',
534 'start');
535 end if;
536
537 SELECT book_class
538 INTO l_book_class
539 FROM fa_book_controls
540 WHERE book_type_code = p_book_type_code;
541
542 -- Only convert fa_asset_invoices when run for corporate books. When
543 -- run for tax books can result in duplicate invoice lines in
544 -- reporting book
545 IF (l_book_class = 'CORPORATE') THEN
546 INSERT INTO fa_mc_asset_invoices(
547 set_of_books_id,
548 exchange_rate,
549 asset_id,
550 asset_invoice_id,
551 invoice_transaction_id_in,
552 fixed_assets_cost,
553 payables_cost,
554 unrevalued_cost,
555 po_vendor_id,
556 date_effective,
557 date_ineffective,
558 invoice_transaction_id_out,
559 deleted_flag,
560 po_number,
561 invoice_number,
562 payables_batch_name,
563 payables_code_combination_id,
564 feeder_system_name,
565 create_batch_date,
566 create_batch_id,
567 invoice_date,
568 post_batch_id,
569 invoice_id,
570 ap_distribution_line_number,
571 payables_units,
572 split_merged_code,
573 description,
574 parent_mass_addition_id,
575 last_update_date,
576 last_updated_by,
577 created_by,
578 creation_date,
579 last_update_login,
580 attribute1,
581 attribute2,
582 attribute3,
583 attribute4,
584 attribute5,
585 attribute6,
586 attribute7,
587 attribute8,
588 attribute9,
589 attribute10,
590 attribute11,
591 attribute12,
592 attribute13,
593 attribute14,
594 attribute15,
595 attribute_category_code,
596 merged_code,
597 split_code,
598 merge_parent_mass_additions_id,
599 split_parent_mass_additions_id,
600 project_asset_line_id,
601 project_id,
602 task_id,
603 source_line_id,
604 invoice_distribution_id,
605 invoice_line_number,
606 po_distribution_id
607 )
608 SELECT
609 p_rsob_id,
610 DECODE(cr.conversion_basis,
611 'C', cr.cost/cr.primary_cur_cost,
612 'R', decode(cr.exchange_rate,
613 NULL, p_numerator_rate/p_denominator_rate,
614 cr.exchange_rate),
615 p_numerator_rate/p_denominator_rate),
616 ai.asset_id,
617 ai.asset_invoice_id,
618 ai.invoice_transaction_id_in,
619 DECODE(p_mau,
620 NULL, ROUND(
621 DECODE(cr.conversion_basis,
622 'C', (ai.fixed_assets_cost *
623 (cr.cost/cr.primary_cur_cost)),
624 'R', (ai.fixed_assets_cost/
625 p_denominator_rate)
626 *
627 DECODE(cr.exchange_rate,
628 NULL, p_numerator_rate,
629 cr.exchange_rate)
630 ), p_precision),
631 ROUND(
632 DECODE(cr.conversion_basis,
633 'C', (ai.fixed_assets_cost *
634 (cr.cost/cr.primary_cur_cost)),
635 'R', (ai.fixed_assets_cost/
636 p_denominator_rate)
637 *
638 DECODE(cr.exchange_rate,
639 NULL, p_numerator_rate,
640 cr.exchange_rate))
641 / p_mau) * p_mau),
642 DECODE(p_mau,
643 NULL, ROUND(
644 DECODE(cr.conversion_basis,
645 'C', (ai.payables_cost *
646 (cr.cost/cr.primary_cur_cost)),
647 'R', (ai.payables_cost /
648 p_denominator_rate)
649 *
650 DECODE(cr.exchange_rate,
651 NULL, p_numerator_rate,
652 cr.exchange_rate)
653 ), p_precision),
654 ROUND(
655 DECODE(cr.conversion_basis,
656 'C', (ai.payables_cost *
657 (cr.cost/cr.primary_cur_cost)),
658 'R', (ai.payables_cost/
659 p_denominator_rate)
660 *
661 DECODE(cr.exchange_rate,
662 NULL, p_numerator_rate,
663 cr.exchange_rate))
664 / p_mau) * p_mau),
665 DECODE(p_mau,
666 NULL, ROUND(
667 DECODE(cr.conversion_basis,
668 'C', (ai.unrevalued_cost *
669 (cr.cost/cr.primary_cur_cost)),
670 'R', (ai.unrevalued_cost/
671 p_denominator_rate)
672 *
673 DECODE(cr.exchange_rate,
674 NULL, p_numerator_rate,
675 cr.exchange_rate)
676 ), p_precision),
677 ROUND(
678 DECODE(cr.conversion_basis,
679 'C', (ai.unrevalued_cost *
680 (cr.cost/cr.primary_cur_cost)),
681 'R', (ai.unrevalued_cost/
682 p_denominator_rate)
683 *
684 DECODE(cr.exchange_rate,
685 NULL, p_numerator_rate,
686 cr.exchange_rate))
687 / p_mau) * p_mau),
688 ai.po_vendor_id,
689 ai.date_effective,
690 ai.date_ineffective,
691 ai.invoice_transaction_id_out,
692 ai.deleted_flag,
693 ai.po_number,
694 ai.invoice_number,
695 ai.payables_batch_name,
696 ai.payables_code_combination_id,
697 ai.feeder_system_name,
698 ai.create_batch_date,
699 ai.create_batch_id,
700 ai.invoice_date,
701 ai.post_batch_id,
702 ai.invoice_id,
703 ai.ap_distribution_line_number,
704 ai.payables_units,
705 ai.split_merged_code,
706 ai.description,
707 ai.parent_mass_addition_id,
708 ai.last_update_date,
709 ai.last_updated_by,
710 ai.created_by,
711 ai.creation_date,
712 ai.last_update_login,
713 ai.attribute1,
714 ai.attribute2,
715 ai.attribute3,
716 ai.attribute4,
717 ai.attribute5,
718 ai.attribute6,
719 ai.attribute7,
720 ai.attribute8,
721 ai.attribute9,
722 ai.attribute10,
723 ai.attribute11,
724 ai.attribute12,
725 ai.attribute13,
726 ai.attribute14,
727 ai.attribute15,
728 ai.attribute_category_code,
729 ai.merged_code,
730 ai.split_code,
731 ai.merge_parent_mass_additions_id,
732 ai.split_parent_mass_additions_id,
733 ai.project_asset_line_id,
734 ai.project_id,
735 ai.task_id,
736 ai.source_line_id,
737 ai.invoice_distribution_id,
738 ai.invoice_line_number,
739 ai.po_distribution_id
740 FROM
741 fa_asset_invoices ai,
742 fa_mc_conversion_rates cr
743 WHERE
744 ai.asset_id = cr.asset_id AND
745 cr.set_of_books_id = p_rsob_id AND
746 cr.book_type_code = p_book_type_code AND
747 cr.status = 'S';
748
749 END IF; -- l_book_class
750
751 if (g_print_debug) then
752 fa_debug_pkg.add('convert_assets',
753 'Converted FA_ASSET_INVOICES records',
754 'success');
755 end if;
756
757
758 EXCEPTION
759 WHEN OTHERS THEN
760 fa_srvr_msg.add_sql_error (
761 calling_fn => 'fa_mc_upg2_pkg.convert_invoices');
762 app_exception.raise_exception;
763
764 END convert_invoices;
765
766
767 PROCEDURE insert_bks_rates(
768 p_rsob_id IN NUMBER,
769 p_book_type_code IN VARCHAR2,
770 p_numerator_rate IN NUMBER,
771 p_denominator_rate IN NUMBER,
772 p_precision IN NUMBER) IS
773 /* ************************************************************************
774 This procedure will insert a row for each asset converted to reporting
775 book. It will contain the transaction_header_id_in from the active row
776 fa_books at the time of conversion and has the rate that was used
777 to convert the asset transactions. This is the ratio of the cost in
778 reporting book divided by the cost in primary book. For assets that
779 have a cost of 0 we will use the exchange_rate as of the initial
780 conversion date for the Reporting Book
781 ************************************************************************ */
782
783 BEGIN
784
785 if (g_print_debug) then
786 fa_debug_pkg.add('convert_assets',
787 'Inserting into FA_MC_BOOKS_RATES',
788 'start');
789 end if;
790
791 INSERT INTO fa_mc_books_rates
792 (set_of_books_id,
793 asset_id,
794 book_type_code,
795 transaction_header_id,
796 invoice_transaction_id,
797 transaction_date_entered,
798 cost,
799 exchange_rate,
800 avg_exchange_rate,
801 last_updated_by,
802 last_update_date,
803 last_update_login,
804 complete)
805 SELECT p_rsob_id,
806 mcbk.asset_id,
807 p_book_type_code,
808 mcbk.transaction_header_id_in,
809 NULL,
810 bk.date_effective,
811 bk.cost,
812 DECODE(cr.conversion_basis,
813 'R', decode(cr.exchange_rate,
814 NULL, (p_numerator_rate/
815 p_denominator_rate),
816 decode(bk.cost,
817 0, cr.exchange_rate,
818 (mcbk.cost/bk.cost))),
819 decode(bk.cost,
820 0, (p_numerator_rate/
821 p_denominator_rate),
822 (mcbk.cost/bk.cost))),
823 DECODE(cr.conversion_basis,
824 'R', decode(cr.exchange_rate,
825 NULL, (p_numerator_rate/
826 p_denominator_rate),
827 decode(bk.cost,
828 0, cr.exchange_rate,
829 (mcbk.cost/bk.cost))),
830 decode(bk.cost,
831 0, (p_numerator_rate/
832 p_denominator_rate),
833 (mcbk.cost/bk.cost))),
834 mcbk.last_updated_by,
835 mcbk.last_update_date,
836 mcbk.last_update_login,
837 'Y'
838 FROM
839 fa_mc_books mcbk,
840 fa_books bk,
841 fa_mc_conversion_rates cr
842
843 WHERE
844 cr.status = 'S' AND
845 cr.asset_id = bk.asset_id AND
846 bk.asset_id = mcbk.asset_id AND
847 cr.set_of_books_id = p_rsob_id AND
848 bk.date_ineffective is NULL AND
849 bk.transaction_header_id_in =
850 mcbk.transaction_header_id_in AND
851 cr.book_type_code = p_book_type_code AND
852 bk.book_type_code = cr.book_type_code AND
853 mcbk.set_of_books_id = cr.set_of_books_id AND
854 mcbk.book_type_code = bk.book_type_code;
855
856 if (g_print_debug) then
857 fa_debug_pkg.add('convert_assets',
858 'Insered into FA_MC_BOOKS_RATES',
859 'success');
860 end if;
861
862
863 EXCEPTION
864 WHEN OTHERS THEN
865 fa_srvr_msg.add_sql_error (
866 calling_fn => 'fa_mc_upg2_pkg.insert_books_rates');
867
868 app_exception.raise_exception;
869 END insert_bks_rates;
870
871
872
873 PROCEDURE convert_adjustments(
874 p_rsob_id IN NUMBER,
875 p_book_type_code IN VARCHAR2,
876 p_start_pc IN NUMBER,
877 p_end_pc IN NUMBER,
878 p_numerator_rate IN NUMBER,
879 p_denominator_rate IN NUMBER,
880 p_mau IN NUMBER,
881 p_precision IN NUMBER) IS
882 /* ************************************************************************
883 This procedure will convert all rows in fa_adjustments in the fiscal
884 being converted and all rows in prior fiscal years related to balance
885 sheet accounts for the candidate assets. The first insert will insert
886 all rows in the fiscal year being converted and the second insert
887 inserts all prior years rows.
888 ************************************************************************ */
889
890 BEGIN
891 if (g_print_debug) then
892 fa_debug_pkg.add('convert_assets',
893 'Converting FA_ADJUSTMENTS records',
894 'start');
895 end if;
896
897
898 -- convert everything in fa_adjustments in the fiscal year being
899 -- converted
900
901 INSERT INTO fa_mc_adjustments(
902 set_of_books_id,
903 transaction_header_id,
904 source_type_code,
905 adjustment_type,
906 debit_credit_flag,
907 code_combination_id,
908 book_type_code,
909 asset_id,
910 adjustment_amount,
911 distribution_id,
912 last_update_date,
913 last_updated_by,
914 last_update_login,
915 annualized_adjustment,
916 je_header_id,
917 je_line_num,
918 period_counter_adjusted,
919 period_counter_created,
920 asset_invoice_id,
921 global_attribute1,
922 global_attribute2,
923 global_attribute3,
924 global_attribute4,
925 global_attribute5,
926 global_attribute6,
927 global_attribute7,
928 global_attribute8,
929 global_attribute9,
930 global_attribute10,
931 global_attribute11,
932 global_attribute12,
933 global_attribute13,
934 global_attribute14,
935 global_attribute15,
936 global_attribute16,
937 global_attribute17,
938 global_attribute18,
939 global_attribute19,
940 global_attribute20,
941 global_attribute_category,
942 converted_flag,
943 adjustment_line_id,
944 deprn_override_flag,
945 track_member_flag,
946 source_line_id,
947 source_dest_code
948 )
949 SELECT
950 p_rsob_id,
951 aj.transaction_header_id,
952 aj.source_type_code,
953 aj.adjustment_type,
954 aj.debit_credit_flag,
955 aj.code_combination_id,
956 aj.book_type_code,
957 aj.asset_id,
958 DECODE(p_mau,
959 NULL, ROUND(
960 DECODE(cr.conversion_basis,
961 'C', (aj.adjustment_amount *
962 (cr.cost/cr.primary_cur_cost)),
963
964 'R', (aj.adjustment_amount/
965 p_denominator_rate)
966 *
967 DECODE(cr.exchange_rate,
968 NULL, p_numerator_rate,
969 cr.exchange_rate)
970 ), p_precision),
971 ROUND(
972 DECODE(cr.conversion_basis,
973 'C', (aj.adjustment_amount *
974 (cr.cost/cr.primary_cur_cost)),
975 'R', (aj.adjustment_amount/
976 p_denominator_rate)
977 *
978 DECODE(cr.exchange_rate,
979 NULL, p_numerator_rate,
980 cr.exchange_rate))
981 / p_mau) * p_mau),
982
983 aj.distribution_id,
984 aj.last_update_date,
985 aj.last_updated_by,
986 aj.last_update_login,
987 DECODE(p_mau,
988 NULL, ROUND(
989 DECODE(cr.conversion_basis,
990 'C', (aj.annualized_adjustment *
991 (cr.cost/cr.primary_cur_cost)),
992
993 'R', (aj.annualized_adjustment/
994 p_denominator_rate)
995 *
996 DECODE(cr.exchange_rate,
997 NULL, p_numerator_rate,
998 cr.exchange_rate)
999 ), p_precision),
1000 ROUND(
1001 DECODE(cr.conversion_basis,
1002 'C', (aj.annualized_adjustment *
1003 (cr.cost/cr.primary_cur_cost)),
1004 'R', (aj.annualized_adjustment/
1005 p_denominator_rate)
1006 *
1007 DECODE(cr.exchange_rate,
1008 NULL, p_numerator_rate,
1009 cr.exchange_rate))
1010 / p_mau) * p_mau),
1011 aj.je_header_id,
1012 aj.je_line_num,
1013 aj.period_counter_adjusted,
1014 aj.period_counter_created,
1015 aj.asset_invoice_id,
1016 aj.global_attribute1,
1017 aj.global_attribute2,
1018 aj.global_attribute3,
1019 aj.global_attribute4,
1020 aj.global_attribute5,
1021 aj.global_attribute6,
1022 aj.global_attribute7,
1023 aj.global_attribute8,
1024 aj.global_attribute9,
1025 aj.global_attribute10,
1026 aj.global_attribute11,
1027 aj.global_attribute12,
1028 aj.global_attribute13,
1029 aj.global_attribute14,
1030 aj.global_attribute15,
1031 aj.global_attribute16,
1032 aj.global_attribute17,
1033 aj.global_attribute18,
1034 aj.global_attribute19,
1035 aj.global_attribute20,
1036 aj.global_attribute_category,
1037 'Y',
1038 fa_adjustments_s.nextval,
1039 aj.deprn_override_flag,
1040 aj.track_member_flag,
1041 aj.source_line_id,
1042 aj.source_dest_code
1043 FROM
1044 fa_adjustments aj,
1045 fa_mc_conversion_rates cr
1046
1047 WHERE
1048 cr.set_of_books_id = p_rsob_id AND
1049 cr.book_type_code = p_book_type_code AND
1050 cr.asset_id = aj.asset_id AND
1051 aj.book_type_code = cr.book_type_code AND
1052 aj.period_counter_created between p_start_pc
1053 and p_end_pc AND
1054 cr.status = 'S';
1055
1056 -- convert all balance sheet relevant accounts in the
1057 -- past years as well for the assets being converted.
1058
1059 INSERT INTO fa_mc_adjustments(
1060 set_of_books_id,
1061 transaction_header_id,
1062 source_type_code,
1063 adjustment_type,
1064 debit_credit_flag,
1065 code_combination_id,
1066 book_type_code,
1067 asset_id,
1068 adjustment_amount,
1069 distribution_id,
1070 last_update_date,
1071 last_updated_by,
1072 last_update_login,
1073 annualized_adjustment,
1074 je_header_id,
1075 je_line_num,
1076 period_counter_adjusted,
1077 period_counter_created,
1078 asset_invoice_id,
1079 global_attribute1,
1080 global_attribute2,
1081 global_attribute3,
1082 global_attribute4,
1083 global_attribute5,
1084 global_attribute6,
1085 global_attribute7,
1086 global_attribute8,
1087 global_attribute9,
1088 global_attribute10,
1089 global_attribute11,
1090 global_attribute12,
1091 global_attribute13,
1092 global_attribute14,
1093 global_attribute15,
1094 global_attribute16,
1095 global_attribute17,
1096 global_attribute18,
1097 global_attribute19,
1098 global_attribute20,
1099 global_attribute_category,
1100 converted_flag,
1101 adjustment_line_id,
1102 deprn_override_flag,
1103 track_member_flag,
1104 source_line_id,
1105 source_dest_code
1106 )
1107 SELECT
1108 p_rsob_id,
1109 aj.transaction_header_id,
1110 aj.source_type_code,
1111 aj.adjustment_type,
1112 aj.debit_credit_flag,
1113 aj.code_combination_id,
1114 aj.book_type_code,
1115 aj.asset_id,
1116 DECODE(p_mau,
1117 NULL, ROUND(
1118 DECODE(cr.conversion_basis,
1119 'C', (aj.adjustment_amount *
1120 (cr.cost/cr.primary_cur_cost)),
1121
1122 'R', (aj.adjustment_amount/
1123 p_denominator_rate)
1124 *
1125 DECODE(cr.exchange_rate,
1126 NULL, p_numerator_rate,
1127 cr.exchange_rate)
1128 ), p_precision),
1129 ROUND(
1130 DECODE(cr.conversion_basis,
1131 'C', (aj.adjustment_amount *
1132 (cr.cost/cr.primary_cur_cost)),
1133 'R', (aj.adjustment_amount/
1134 p_denominator_rate)
1135 *
1136 DECODE(cr.exchange_rate,
1137 NULL, p_numerator_rate,
1138 cr.exchange_rate))
1139 / p_mau) * p_mau),
1140 aj.distribution_id,
1141 aj.last_update_date,
1142 aj.last_updated_by,
1143 aj.last_update_login,
1144 DECODE(p_mau,
1145 NULL, ROUND(
1146 DECODE(cr.conversion_basis,
1147 'C', (aj.annualized_adjustment *
1148 (cr.cost/cr.primary_cur_cost)),
1149
1150 'R', (aj.annualized_adjustment/
1151 p_denominator_rate)
1152 *
1153 DECODE(cr.exchange_rate,
1154 NULL, p_numerator_rate,
1155 cr.exchange_rate)
1156 ), p_precision),
1157 ROUND(
1158 DECODE(cr.conversion_basis,
1159 'C', (aj.annualized_adjustment *
1160 (cr.cost/cr.primary_cur_cost)),
1161 'R', (aj.annualized_adjustment/
1162 p_denominator_rate)
1163 *
1164 DECODE(cr.exchange_rate,
1165 NULL, p_numerator_rate,
1166 cr.exchange_rate))
1167 / p_mau) * p_mau),
1168 aj.je_header_id,
1169 aj.je_line_num,
1170 aj.period_counter_adjusted,
1171 aj.period_counter_created,
1172 aj.asset_invoice_id,
1173 aj.global_attribute1,
1174 aj.global_attribute2,
1175 aj.global_attribute3,
1176 aj.global_attribute4,
1177 aj.global_attribute5,
1178 aj.global_attribute6,
1179 aj.global_attribute7,
1180 aj.global_attribute8,
1181 aj.global_attribute9,
1182 aj.global_attribute10,
1183 aj.global_attribute11,
1184 aj.global_attribute12,
1185 aj.global_attribute13,
1186 aj.global_attribute14,
1187 aj.global_attribute15,
1188 aj.global_attribute16,
1189 aj.global_attribute17,
1190 aj.global_attribute18,
1191 aj.global_attribute19,
1192 aj.global_attribute20,
1193 aj.global_attribute_category,
1194 'Y',
1195 fa_adjustments_s.nextval,
1196 aj.deprn_override_flag,
1197 aj.track_member_flag,
1198 aj.source_line_id,
1199 aj.source_dest_code
1200 FROM
1201 fa_adjustments aj,
1202 fa_mc_conversion_rates cr
1203 WHERE
1204 cr.set_of_books_id = p_rsob_id AND
1205 cr.book_type_code = p_book_type_code AND
1206 cr.asset_id = aj.asset_id AND
1207 aj.book_type_code = cr.book_type_code AND
1208 aj.period_counter_created < p_start_pc AND
1209 aj.adjustment_type IN ( 'COST',
1210 'COST CLEARING',
1211 'CIP COST',
1212 'RESERVE',
1213 'REVAL RESERVE',
1214 'DEPRN ADJUST',
1215 'INTERCO AP',
1216 'INTERCO AR',
1217 'PROCEEDS CLR',
1218 'REMOVALCOST CLR') AND
1219 cr.status = 'S';
1220
1221 if (g_print_debug) then
1222 fa_debug_pkg.add('convert_assets',
1223 'Converted FA_ADJUSTMENTS records',
1224 'success');
1225 end if;
1226
1227
1228 EXCEPTION
1229 WHEN OTHERS THEN
1230 fa_srvr_msg.add_sql_error (
1231 calling_fn => 'fa_mc_upg2_pkg.convert_adjustments');
1232 app_exception.raise_exception;
1233
1234 END convert_adjustments;
1235
1236
1237 PROCEDURE round_retirements(
1238 p_book_type_code IN VARCHAR2,
1239 p_rsob_id IN NUMBER,
1240 p_start_pc IN NUMBER) IS
1241 /* ************************************************************************
1242 This procedure rounds all retirement rows for the candidate assets.
1243 We round all the retirements that have happened in the current fiscal
1244 year being converted. We are only concerned with those retirement_id's
1245 that have not been reinstated. Rounding is to set NBV_RETIRED TO
1246 COST - RESERVE.
1247 ************************************************************************ */
1248
1249 l_trx_id NUMBER;
1250 l_nbv_retired NUMBER;
1251 l_cost_retired NUMBER;
1252 l_gain_loss NUMBER;
1253 l_proceeds NUMBER;
1254 l_cor NUMBER;
1255 l_reval_rsv NUMBER;
1256 l_rsv_retired NUMBER;
1257 l_maj_rowid ROWID;
1258 l_mrt_rowid ROWID;
1259
1260 CURSOR get_retirements IS
1261 SELECT
1262 maj.rowid,
1263 mrt.cost_retired,
1264 mrt.rowid,
1265 mrt.reval_reserve_retired,
1266 mrt.proceeds_of_sale,
1267 mrt.cost_of_removal,
1268 maj.transaction_header_id
1269 FROM
1270 fa_mc_adjustments maj,
1271 fa_deprn_periods dp,
1272 fa_mc_retirements mrt,
1273 fa_retirements rt,
1274 fa_mc_conversion_rates cr
1275 WHERE
1276 cr.book_type_code = p_book_type_code AND
1277 cr.set_of_books_id = p_rsob_id AND
1278 cr.status = 'S' AND
1279 cr.asset_id = rt.asset_id AND
1280 mrt.retirement_id = rt.retirement_id AND
1281 rt.book_type_code = cr.book_type_code AND
1282 rt.date_effective >= dp.period_open_date AND
1283 dp.book_type_code = rt.book_type_code AND
1284 dp.period_counter = p_start_pc AND
1285 nvl(mrt.nbv_retired,0) <> 0 AND
1286 maj.set_of_books_id = p_rsob_id AND
1287 maj.book_type_code = rt.book_type_code AND
1288 maj.asset_id = rt.asset_id AND
1289 maj.transaction_header_id =
1290 rt.transaction_header_id_in AND
1291 rt.transaction_header_id_out is NULL AND
1292 maj.adjustment_type = 'NBV RETIRED';
1293
1294 CURSOR reserve_retired IS
1295 SELECT
1296 nvl(sum(decode(maj.debit_credit_flag,
1297 'DR', maj.adjustment_amount,
1298 'CR', -1 * maj.adjustment_amount)),0)
1299 FROM
1300 fa_mc_adjustments maj
1301 WHERE
1302 maj.set_of_books_id = p_rsob_id AND
1303 maj.book_type_code = p_book_type_code AND
1304 maj.transaction_header_id = l_trx_id AND
1305 maj.adjustment_type = 'RESERVE';
1306 BEGIN
1307
1308 if (g_print_debug) then
1309 fa_debug_pkg.add('convert_assets',
1310 'Round Retirements',
1311 'start');
1312 end if;
1313
1314 OPEN get_retirements;
1315 LOOP
1316 FETCH get_retirements INTO
1317 l_maj_rowid,
1318 l_cost_retired,
1319 l_mrt_rowid,
1320 l_reval_rsv,
1321 l_proceeds,
1322 l_cor,
1323 l_trx_id;
1324 IF (get_retirements%NOTFOUND) THEN
1325 EXIT;
1326 END IF;
1327 OPEN reserve_retired;
1328 FETCH reserve_retired INTO l_rsv_retired;
1329 CLOSE reserve_retired;
1330
1331 l_nbv_retired := l_cost_retired - l_rsv_retired;
1332 l_gain_loss := l_proceeds - l_cor - l_nbv_retired +
1333 l_reval_rsv;
1334
1335 UPDATE fa_mc_adjustments
1336 SET adjustment_amount = l_nbv_retired
1337 WHERE rowid = l_maj_rowid;
1338
1339 UPDATE fa_mc_retirements
1340 SET gain_loss_amount = l_gain_loss,
1341 nbv_retired = l_nbv_retired
1342 WHERE rowid = l_mrt_rowid;
1343 END LOOP;
1344 CLOSE get_retirements;
1345
1346
1347 EXCEPTION
1348 WHEN OTHERS THEN
1349 fa_srvr_msg.add_sql_error (
1350 calling_fn => 'fa_mc_upg2_pkg.round_retirements');
1351 app_exception.raise_exception;
1352 END round_retirements;
1353
1354
1355 PROCEDURE convert_retirements(
1356 p_rsob_id IN NUMBER,
1357 p_book_type_code IN VARCHAR2,
1358 p_start_pc IN NUMBER,
1359 p_end_pc IN NUMBER,
1360 p_numerator_rate IN NUMBER,
1361 p_denominator_rate IN NUMBER,
1362 p_mau IN NUMBER,
1363 p_precision IN NUMBER) IS
1364 /* ************************************************************************
1365 This procedure converts all retirement rows for the candidate assets
1366 being converted.
1367 ************************************************************************ */
1368
1369 l_period_num NUMBER;
1370
1371 BEGIN
1372
1373 if (g_print_debug) then
1374 fa_debug_pkg.add('convert_assets',
1375 'Converting FA_RETIREMENTS records',
1376 'start');
1377 end if;
1378
1379 INSERT INTO fa_mc_retirements(
1380 set_of_books_id,
1381 retirement_id,
1382 cost_retired,
1383 status,
1384 last_update_date,
1385 last_updated_by,
1386 cost_of_removal,
1387 nbv_retired,
1388 gain_loss_amount,
1389 proceeds_of_sale,
1390 itc_recaptured,
1391 stl_deprn_amount,
1392 last_update_login,
1393 reval_reserve_retired,
1394 unrevalued_cost_retired,
1395 converted_flag,
1396 book_type_code,
1397 asset_id,
1398 transaction_header_id_in,
1399 transaction_header_id_out,
1400 date_retired,
1401 date_effective,
1402 retirement_prorate_convention,
1403 units,
1404 gain_loss_type_code,
1405 retirement_type_code,
1406 itc_recapture_id,
1407 reference_num,
1408 sold_to,
1409 trade_in_asset_id,
1410 stl_method_code,
1411 stl_life_in_months,
1412 created_by,
1413 creation_date,
1414 attribute1,
1415 attribute2,
1416 attribute3,
1417 attribute4,
1418 attribute5,
1419 attribute6,
1420 attribute7,
1421 attribute8,
1422 attribute9,
1423 attribute10,
1424 attribute11,
1425 attribute12,
1426 attribute13,
1427 attribute14,
1428 attribute15,
1429 attribute_category_code,
1430 limit_proceeds_flag,
1431 recapture_reserve_flag,
1432 recognize_gain_loss,
1433 reduction_rate,
1434 terminal_gain_loss
1435 )
1436 SELECT p_rsob_id,
1437 rt.retirement_id,
1438 DECODE(p_mau,
1439 NULL, ROUND(
1440 DECODE(cr.conversion_basis,
1441 'C', (rt.cost_retired *
1442 (cr.cost/cr.primary_cur_cost)),
1443
1444 'R', (rt.cost_retired/
1445 p_denominator_rate)
1446 *
1447 DECODE(cr.exchange_rate,
1448 NULL, p_numerator_rate,
1449 cr.exchange_rate)
1450 ), p_precision),
1451 ROUND(
1452 DECODE(cr.conversion_basis,
1453 'C', (rt.cost_retired *
1454 (cr.cost/cr.primary_cur_cost)),
1455 'R', (rt.cost_retired/
1456 p_denominator_rate)
1457 *
1458 DECODE(cr.exchange_rate,
1459 NULL, p_numerator_rate,
1460 cr.exchange_rate))
1461 / p_mau) * p_mau),
1462 rt.status,
1463 rt.last_update_date,
1464 rt.last_updated_by,
1465 DECODE(p_mau,
1466 NULL, ROUND(
1467 DECODE(cr.conversion_basis,
1468 'C', (rt.cost_of_removal *
1469 (cr.cost/cr.primary_cur_cost)),
1470
1471 'R', (rt.cost_of_removal /
1472 p_denominator_rate)
1473 *
1474 DECODE(cr.exchange_rate,
1475 NULL, p_numerator_rate,
1476 cr.exchange_rate)
1477 ), p_precision),
1478 ROUND(
1479 DECODE(cr.conversion_basis,
1480 'C', (rt.cost_of_removal *
1481 (cr.cost/cr.primary_cur_cost)),
1482 'R', (rt.cost_of_removal /
1483 p_denominator_rate)
1484 *
1485 DECODE(cr.exchange_rate,
1486 NULL, p_numerator_rate,
1487 cr.exchange_rate))
1488 / p_mau) * p_mau),
1489 DECODE(p_mau,
1490 NULL, ROUND(
1491 DECODE(cr.conversion_basis,
1492 'C', (rt.nbv_retired *
1493 (cr.cost/cr.primary_cur_cost)),
1494
1495 'R', (rt.nbv_retired /
1496 p_denominator_rate)
1497 *
1498 DECODE(cr.exchange_rate,
1499 NULL, p_numerator_rate,
1500 cr.exchange_rate)
1501 ), p_precision),
1502 ROUND(
1503 DECODE(cr.conversion_basis,
1504 'C', (rt.nbv_retired *
1505 (cr.cost/cr.primary_cur_cost)),
1506 'R', (rt.nbv_retired /
1507 p_denominator_rate)
1508 *
1509 DECODE(cr.exchange_rate,
1510 NULL, p_numerator_rate,
1511 cr.exchange_rate))
1512 / p_mau) * p_mau),
1513 DECODE(p_mau,
1514 NULL, ROUND(
1515 DECODE(cr.conversion_basis,
1516 'C', (rt.gain_loss_amount *
1517 (cr.cost/cr.primary_cur_cost)),
1518
1519 'R', (rt.gain_loss_amount/
1520 p_denominator_rate)
1521 *
1522 DECODE(cr.exchange_rate,
1523 NULL, p_numerator_rate,
1524 cr.exchange_rate)
1525 ), p_precision),
1526 ROUND(
1527 DECODE(cr.conversion_basis,
1528 'C', (rt.gain_loss_amount *
1529 (cr.cost/cr.primary_cur_cost)),
1530 'R', (rt.gain_loss_amount /
1531 p_denominator_rate)
1532 *
1533 DECODE(cr.exchange_rate,
1534 NULL, p_numerator_rate,
1535 cr.exchange_rate))
1536 / p_mau) * p_mau),
1537 DECODE(p_mau,
1538 NULL, ROUND(
1539 DECODE(cr.conversion_basis,
1540 'C', (rt.proceeds_of_sale *
1541 (cr.cost/cr.primary_cur_cost)),
1542
1543 'R', (rt.proceeds_of_sale/
1544 p_denominator_rate)
1545 *
1546 DECODE(cr.exchange_rate,
1547 NULL, p_numerator_rate,
1548 cr.exchange_rate)
1549 ), p_precision),
1550 ROUND(
1551 DECODE(cr.conversion_basis,
1552 'C', (rt.proceeds_of_sale *
1553 (cr.cost/cr.primary_cur_cost)),
1554 'R', (rt.proceeds_of_sale /
1555 p_denominator_rate)
1556 *
1557 DECODE(cr.exchange_rate,
1558 NULL, p_numerator_rate,
1559 cr.exchange_rate))
1560 / p_mau) * p_mau),
1561 DECODE(p_mau,
1562 NULL, ROUND(
1563 DECODE(cr.conversion_basis,
1564 'C', (rt.itc_recaptured *
1565 (cr.cost/cr.primary_cur_cost)),
1566
1567 'R', (rt.itc_recaptured/
1568 p_denominator_rate)
1569 *
1570 DECODE(cr.exchange_rate,
1571 NULL, p_numerator_rate,
1572 cr.exchange_rate)
1573 ), p_precision),
1574 ROUND(
1575 DECODE(cr.conversion_basis,
1576 'C', (rt.itc_recaptured *
1577 (cr.cost/cr.primary_cur_cost)),
1578 'R', (rt.itc_recaptured /
1579 p_denominator_rate)
1580 *
1581 DECODE(cr.exchange_rate,
1582 NULL, p_numerator_rate,
1583 cr.exchange_rate))
1584 / p_mau) * p_mau),
1585 DECODE(p_mau,
1586 NULL, ROUND(
1587 DECODE(cr.conversion_basis,
1588 'C', (rt.stl_deprn_amount *
1589 (cr.cost/cr.primary_cur_cost)),
1590
1591 'R', (rt.stl_deprn_amount /
1592 p_denominator_rate)
1593 *
1594 DECODE(cr.exchange_rate,
1595 NULL, p_numerator_rate,
1596 cr.exchange_rate)
1597 ), p_precision),
1598 ROUND(
1599 DECODE(cr.conversion_basis,
1600 'C', (rt.stl_deprn_amount *
1601 (cr.cost/cr.primary_cur_cost)),
1602 'R', (rt.stl_deprn_amount /
1603 p_denominator_rate)
1604 *
1605 DECODE(cr.exchange_rate,
1606 NULL, p_numerator_rate,
1607 cr.exchange_rate))
1608 / p_mau) * p_mau),
1609
1610 rt.last_update_login,
1611 DECODE(p_mau,
1612 NULL, ROUND(
1613 DECODE(cr.conversion_basis,
1614 'C', (rt.reval_reserve_retired *
1615 (cr.cost/cr.primary_cur_cost)),
1616
1617 'R', (rt.reval_reserve_retired /
1618 p_denominator_rate)
1619 *
1620 DECODE(cr.exchange_rate,
1621 NULL, p_numerator_rate,
1622 cr.exchange_rate)
1623 ), p_precision),
1624 ROUND(
1625 DECODE(cr.conversion_basis,
1626 'C', (rt.reval_reserve_retired *
1627 (cr.cost/cr.primary_cur_cost)),
1628 'R', (rt.reval_reserve_retired /
1629 p_denominator_rate)
1630 *
1631 DECODE(cr.exchange_rate,
1632 NULL, p_numerator_rate,
1633 cr.exchange_rate))
1634 / p_mau) * p_mau),
1635 DECODE(p_mau,
1636 NULL, ROUND(
1637 DECODE(cr.conversion_basis,
1638 'C', (rt.unrevalued_cost_retired *
1639 (cr.cost/cr.primary_cur_cost)),
1640
1641 'R', (rt.unrevalued_cost_retired /
1642 p_denominator_rate)
1643 *
1644 DECODE(cr.exchange_rate,
1645 NULL, p_numerator_rate,
1646 cr.exchange_rate)
1647 ), p_precision),
1648 ROUND(
1649 DECODE(cr.conversion_basis,
1650 'C', (rt.unrevalued_cost_retired *
1651 (cr.cost/cr.primary_cur_cost)),
1652 'R', (rt.unrevalued_cost_retired /
1653 p_denominator_rate)
1654 *
1655 DECODE(cr.exchange_rate,
1656 NULL, p_numerator_rate,
1657 cr.exchange_rate))
1658 / p_mau) * p_mau),
1659
1660 'Y',
1661 rt.book_type_code,
1662 rt.asset_id,
1663 rt.transaction_header_id_in,
1664 rt.transaction_header_id_out,
1665 rt.date_retired,
1666 rt.date_effective,
1667 rt.retirement_prorate_convention,
1668 rt.units,
1669 rt.gain_loss_type_code,
1670 rt.retirement_type_code,
1671 rt.itc_recapture_id,
1672 rt.reference_num,
1673 rt.sold_to,
1674 rt.trade_in_asset_id,
1675 rt.stl_method_code,
1676 rt.stl_life_in_months,
1677 rt.created_by,
1678 rt.creation_date,
1679 rt.attribute1,
1680 rt.attribute2,
1681 rt.attribute3,
1682 rt.attribute4,
1683 rt.attribute5,
1684 rt.attribute6,
1685 rt.attribute7,
1686 rt.attribute8,
1687 rt.attribute9,
1688 rt.attribute10,
1689 rt.attribute11,
1690 rt.attribute12,
1691 rt.attribute13,
1692 rt.attribute14,
1693 rt.attribute15,
1694 rt.attribute_category_code,
1695 rt.limit_proceeds_flag,
1696 rt.recapture_reserve_flag,
1697 rt.recognize_gain_loss,
1698 rt.reduction_rate,
1699 rt.terminal_gain_loss
1700 FROM
1701 fa_retirements rt,
1702 fa_mc_conversion_rates cr
1703 WHERE
1704 cr.set_of_books_id = p_rsob_id AND
1705 cr.book_type_code = p_book_type_code AND
1706 cr.asset_id = rt.asset_id AND
1707 rt.book_type_code = cr.book_type_code AND
1708 cr.status = 'S';
1709
1710 SELECT dp.period_num
1711 INTO l_period_num
1712 FROM
1713 fa_deprn_periods dp
1714 WHERE dp.book_type_code = p_book_type_code AND
1715 period_counter = p_end_pc;
1716
1717 -- if current open period is the first period of fiscal year
1718 -- there is no need to round retirements of the fiscal year
1719 -- that was converted since reinstatement cannot be done
1720 -- across fiscal years
1721
1722 IF (l_period_num > 1) THEN
1723 round_retirements(
1724 p_book_type_code,
1725 p_rsob_id,
1726 p_start_pc);
1727 END IF;
1728
1729 if (g_print_debug) then
1730 fa_debug_pkg.add('convert_retirements',
1731 'round_retirements',
1732 'success');
1733 fa_debug_pkg.add('convert_assets',
1734 'Converted FA_RETIREMENTS records',
1735 'success');
1736 end if;
1737
1738
1739 EXCEPTION
1740 WHEN OTHERS THEN
1741 fa_srvr_msg.add_sql_error (
1742 calling_fn => 'fa_mc_upg2_pkg.convert_retirements');
1743
1744 app_exception.raise_exception;
1745
1746 END convert_retirements;
1747
1748
1749 PROCEDURE convert_deprn_summary(
1750 p_book_type_code IN VARCHAR2,
1751 p_rsob_id IN NUMBER,
1752 p_start_pc IN NUMBER,
1753 p_end_pc IN NUMBER,
1754 p_convert_order IN VARCHAR2,
1755 p_mau IN NUMBER,
1756 p_precision IN NUMBER) IS
1757 /* ************************************************************************
1758 This procedure converts rows in fa_deprn_summary for each asset and
1759 inserts them into fa_mc_deprn_summary. First it selects and converts
1760 the BOOKS row for all the assets. Then depending on the convert order of
1761 of F or L it converts the DEPRN rows for the assets. When the convert_order
1762 is F, all the DEPRN rows in current year are converted and when the
1763 convert_order is L, only the last DEPRN in a prior fiscal year is
1764 converted.
1765 The rows in fa_deprn_summary are fetched one at a time and ordered by
1766 asset_id and period_counter. The deprn_amount column for pc is
1767 calculated using a ratio of cost in fa_mc_books * cost in fa_books/
1768 deprn_amount in primary currency. deprn_reserve and ytd_deprn for the
1769 first period_counter in the current year are also calulated the same way.
1770 For subsequent period counters the deprn_amount for current period is
1771 added to deprn_reserve of previous period. This is necessary to round
1772 reserve correctly as it is then used in converting and rounding
1773 fa_deprn_detail
1774 ************************************************************************ */
1775
1776 l_last_asset_id number;
1777 l_last_deprn_rsv number;
1778 l_last_ytd_deprn number;
1779 l_last_reval_rsv number;
1780 l_last_ytd_reval_exp number;
1781 l_last_book varchar2(30);
1782
1783 l_book_type_code varchar2(30);
1784 l_asset_id number;
1785 l_deprn_run_date date;
1786 l_deprn_amount number;
1787 l_ytd_deprn number;
1788 l_deprn_reserve number;
1789 l_deprn_source_code varchar2(15);
1790 l_adjusted_cost number;
1791 l_bonus_rate number;
1792 l_ltd_prod number;
1793 l_period_counter number;
1794 l_prod number;
1795 l_reval_amort number;
1796 l_reval_amort_basis number;
1797 l_reval_exp number;
1798 l_reval_reserve number;
1799 l_ytd_prod number;
1800 l_ytd_reval_exp number;
1801 l_prior_fy_exp number;
1802
1803 -- adding for fix to 990059
1804 l_primary_rsv number;
1805
1806 -- potential fix for BUG# 1662585
1807 l_ret_reserve number;
1808 l_deprn_override_flag varchar2(1);
1809
1810 CURSOR ds_row IS
1811 SELECT
1812 ds.book_type_code,
1813 ds.asset_id,
1814 ds.deprn_run_date,
1815 DECODE(p_mau,
1816 NULL, ROUND(
1817 (mcbk.cost * (ds.deprn_amount /
1818 decode(bk.cost,0,1,bk.cost))),
1819 p_precision),
1820 ROUND(
1821 (mcbk.cost * (ds.deprn_amount /
1822 decode(bk.cost,0,1,bk.cost)))/
1823 p_mau) * p_mau),
1824 DECODE(p_mau,
1825 NULL, ROUND(
1826 (mcbk.cost * (ds.ytd_deprn /
1827 decode(bk.cost,0,1,bk.cost))),
1828 p_precision),
1829 ROUND(
1830 (mcbk.cost * (ds.ytd_deprn /
1831 decode(bk.cost,0,1,bk.cost))) /
1832 p_mau) * p_mau),
1833 DECODE(p_mau,
1834 NULL, ROUND(
1835 (mcbk.cost * (ds.deprn_reserve/
1836 decode(bk.cost,0,1,bk.cost))),
1837 p_precision),
1838 ROUND(
1839 (mcbk.cost * (ds.deprn_reserve/
1840 decode(bk.cost,0,1,bk.cost))) /
1841 p_mau) * p_mau),
1842 ds.deprn_source_code,
1843 mcbk.adjusted_cost,
1844 ds.bonus_rate,
1845 ds.ltd_production,
1846 ds.period_counter,
1847 ds.production,
1848 DECODE(p_mau,
1849 NULL, ROUND(
1850 (mcbk.cost * (ds.reval_amortization/
1851 decode(bk.cost,0,1,bk.cost))),
1852 p_precision),
1853 ROUND(
1854 (mcbk.cost * (ds.reval_amortization/
1855 decode(bk.cost,0,1,bk.cost)))/
1856 p_mau) * p_mau),
1857 DECODE(p_mau,
1858 NULL, ROUND(
1859 (mcbk.cost * (ds.reval_amortization_basis/
1860 decode(bk.cost,0,1,bk.cost))),
1861 p_precision),
1862 ROUND(
1863 (mcbk.cost * (ds.reval_amortization_basis/
1864 decode(bk.cost,0,1,bk.cost))) /
1865 p_mau) * p_mau),
1866 DECODE(p_mau,
1867 NULL, ROUND(
1868 (mcbk.cost * (ds.reval_deprn_expense/
1869 decode(bk.cost,0,1,bk.cost))),
1870 p_precision),
1871 ROUND(
1872 (mcbk.cost * (ds.reval_deprn_expense/
1873 decode(bk.cost,0,1,bk.cost)))/
1874 p_mau) * p_mau),
1875 DECODE(p_mau,
1876 NULL, ROUND(
1877 (mcbk.cost * (ds.reval_reserve/
1878 decode(bk.cost,0,1,bk.cost))),
1879 p_precision),
1880 ROUND(
1881 (mcbk.cost * (ds.reval_reserve/
1882 decode(bk.cost,0,1,bk.cost))) /
1883 p_mau) * p_mau),
1884 ds.ytd_production,
1885 DECODE(p_mau,
1886 NULL, ROUND(
1887 (mcbk.cost * (ds.ytd_reval_deprn_expense/
1888 decode(bk.cost,0,1,bk.cost))),
1889 p_precision),
1890 ROUND(
1891 (mcbk.cost * (ds.ytd_reval_deprn_expense/
1892 decode(bk.cost,0,1,bk.cost))) /
1893 p_mau) * p_mau),
1894 DECODE(p_mau,
1895 NULL, ROUND(
1896 (mcbk.cost * (ds.prior_fy_expense/
1897 decode(bk.cost,0,1,bk.cost))),
1898 p_precision),
1899 ROUND(
1900 (mcbk.cost * (ds.prior_fy_expense/
1901 decode(bk.cost,0,1,bk.cost))) /
1902 p_mau) * p_mau),
1903 ds.deprn_reserve,
1904 ds.deprn_override_flag
1905 FROM
1906 fa_deprn_summary ds,
1907 fa_deprn_periods dp,
1908 fa_mc_books mcbk,
1909 fa_books bk,
1910 fa_mc_conversion_rates cr
1911 WHERE
1912 ds.asset_id = mcbk.asset_id AND
1913 cr.book_type_code = p_book_type_code AND
1914 cr.set_of_books_id = p_rsob_id AND
1915 bk.asset_id = cr.asset_id AND
1916 bk.book_type_code = cr.book_type_code AND
1917 mcbk.asset_id = bk.asset_id AND
1918 mcbk.book_type_code = bk.book_type_code AND
1919 mcbk.book_type_code = dp.book_type_code AND
1920 ds.deprn_source_code = 'DEPRN' AND
1921 dp.period_counter = ds.period_counter AND
1922 dp.book_type_code = ds.book_type_code AND
1923 nvl(dp.period_CLOSE_date, sysdate)
1924 between bk.date_effective and
1925 nvl(bk.date_ineffective, sysdate) AND
1926 bk.transaction_header_id_in =
1927 mcbk.transaction_header_id_in AND
1928 mcbk.set_of_books_id = cr.set_of_books_id AND
1929 cr.status = 'S' AND
1930 ds.period_counter between p_start_pc and
1931 p_end_pc
1932 ORDER BY
1933 ds.book_type_code,
1934 ds.asset_id,
1935 ds.period_counter;
1936
1937 BEGIN
1938
1939 if (g_print_debug) then
1940 fa_debug_pkg.add('convert_assets',
1941 'Converting FA_DEPRN_SUMMARY records',
1942 'start');
1943 fa_debug_pkg.add('Convert_summary','convert_order',
1944 p_convert_order);
1945 end if;
1946
1947 INSERT INTO fa_mc_deprn_summary(set_of_books_id,
1948 book_type_code,
1949 asset_id,
1950 deprn_run_date,
1951 deprn_amount,
1952 ytd_deprn,
1953 deprn_reserve,
1954 deprn_source_code,
1955 adjusted_cost,
1956 bonus_rate,
1957 ltd_production,
1958 period_counter,
1959 production,
1960 reval_amortization,
1961 reval_amortization_basis,
1962 reval_deprn_expense,
1963 reval_reserve,
1964 ytd_production,
1965 ytd_reval_deprn_expense,
1966 prior_fy_expense,
1967 converted_flag,
1968 deprn_override_flag)
1969 SELECT
1970 p_rsob_id,
1971 ds.book_type_code,
1972 ds.asset_id,
1973 ds.deprn_run_date,
1974 DECODE(p_mau,
1975 NULL, ROUND(
1976 (mcbk.cost * (ds.deprn_amount /
1977 decode(bk.cost,0,1,bk.cost))),
1978 p_precision),
1979 ROUND(
1980 (mcbk.cost * (ds.deprn_amount /
1981 decode(bk.cost,0,1,bk.cost)))/
1982 p_mau) * p_mau),
1983 DECODE(p_mau,
1984 NULL, ROUND(
1985 (mcbk.cost * (ds.ytd_deprn /
1986 decode(bk.cost,0,1,bk.cost))),
1987 p_precision),
1988 ROUND(
1989 (mcbk.cost * (ds.ytd_deprn /
1990 decode(bk.cost,0,1,bk.cost))) /
1991 p_mau) * p_mau),
1992 DECODE(p_mau,
1993 NULL, ROUND(
1994 (mcbk.cost * (ds.deprn_reserve/
1995 decode(bk.cost,0,1,bk.cost))),
1996 p_precision),
1997 ROUND(
1998 (mcbk.cost * (ds.deprn_reserve/
1999 decode(bk.cost,0,1,bk.cost))) /
2000 p_mau) * p_mau),
2001 ds.deprn_source_code,
2002 mcbk.adjusted_cost,
2003 ds.bonus_rate,
2004 ds.ltd_production,
2005 ds.period_counter,
2006 ds.production,
2007 DECODE(p_mau,
2008 NULL, ROUND(
2009 (mcbk.cost * (ds.reval_amortization/
2010 decode(bk.cost,0,1,bk.cost))),
2011 p_precision),
2012 ROUND(
2013 (mcbk.cost * (ds.reval_amortization/
2014 decode(bk.cost,0,1,bk.cost)))/
2015 p_mau) * p_mau),
2016 DECODE(p_mau,
2017 NULL, ROUND(
2018 (mcbk.cost * (ds.reval_amortization_basis/
2019 decode(bk.cost,0,1,bk.cost))),
2020 p_precision),
2021 ROUND(
2022 (mcbk.cost * (ds.reval_amortization_basis/
2023 decode(bk.cost,0,1,bk.cost))) /
2024 p_mau) * p_mau),
2025 DECODE(p_mau,
2026 NULL, ROUND(
2027 (mcbk.cost * (ds.reval_deprn_expense/
2028 decode(bk.cost,0,1,bk.cost))),
2029 p_precision),
2030 ROUND(
2031 (mcbk.cost * (ds.reval_deprn_expense/
2032 decode(bk.cost,0,1,bk.cost)))/
2033 p_mau) * p_mau),
2034 DECODE(p_mau,
2035 NULL, ROUND(
2036 (mcbk.cost * (ds.reval_reserve/
2037 decode(bk.cost,0,1,bk.cost))),
2038 p_precision),
2039 ROUND(
2040 (mcbk.cost * (ds.reval_reserve/
2041 decode(bk.cost,0,1,bk.cost))) /
2042 p_mau) * p_mau),
2043 ds.ytd_production,
2044 DECODE(p_mau,
2045 NULL, ROUND(
2046 (mcbk.cost * (ds.ytd_reval_deprn_expense/
2047 decode(bk.cost,0,1,bk.cost))),
2048 p_precision),
2049 ROUND(
2050 (mcbk.cost * (ds.ytd_reval_deprn_expense/
2051 decode(bk.cost,0,1,bk.cost))) /
2052 p_mau) * p_mau),
2053 DECODE(p_mau,
2054 NULL, ROUND(
2055 (mcbk.cost * (ds.prior_fy_expense/
2056 decode(bk.cost,0,1,bk.cost))),
2057 p_precision),
2058 ROUND(
2059 (mcbk.cost * (ds.prior_fy_expense/
2060 decode(bk.cost,0,1,bk.cost))) /
2061 p_mau) * p_mau),
2062 'Y',
2063 ds.deprn_override_flag
2064 FROM
2065 fa_deprn_summary ds,
2066 fa_deprn_periods dp,
2067 fa_mc_books mcbk,
2068 fa_books bk,
2069 fa_mc_conversion_rates cr
2070 WHERE
2071 ds.asset_id = mcbk.asset_id AND
2072 ds.deprn_source_code = 'BOOKS' AND
2073 cr.book_type_code = p_book_type_code AND
2074 cr.set_of_books_id = p_rsob_id AND
2075 cr.status = 'S' AND
2076 bk.asset_id = cr.asset_id AND
2077 mcbk.asset_id = bk.asset_id AND
2078 mcbk.set_of_books_id = cr.set_of_books_id AND
2079 mcbk.book_type_code = dp.book_type_code AND
2080 mcbk.book_type_code = bk.book_type_code AND
2081 bk.book_type_code = cr.book_type_code AND
2082 dp.book_type_code = ds.book_type_code AND
2083 nvl(dp.period_close_date, sysdate)
2084 between bk.date_effective and
2085 nvl(bk.date_ineffective, sysdate) AND
2086 dp.period_counter = ds.period_counter + 1 AND
2087 bk.transaction_header_id_in = mcbk.transaction_header_id_in;
2088
2089 if (g_print_debug) then
2090 fa_debug_pkg.add('Convert_summary','conversion stage',
2091 'Inserted BOOKS rows');
2092 end if;
2093
2094 -- now convert all DEPRN rows from start of current FISCAL Year
2095
2096 IF (p_convert_order = 'F') THEN
2097
2098 l_last_asset_id := 0;
2099 l_last_deprn_rsv := 0;
2100 l_last_ytd_deprn := 0;
2101 l_last_reval_rsv := 0;
2102 l_last_ytd_reval_exp := 0;
2103
2104 OPEN ds_row;
2105 LOOP
2106 FETCH ds_row into
2107 l_book_type_code,
2108 l_asset_id,
2109 l_deprn_run_date,
2110 l_deprn_amount,
2111 l_ytd_deprn,
2112 l_deprn_reserve,
2113 l_deprn_source_code,
2114 l_adjusted_cost,
2115 l_bonus_rate,
2116 l_ltd_prod,
2117 l_period_counter,
2118 l_prod,
2119 l_reval_amort,
2120 l_reval_amort_basis,
2121 l_reval_exp,
2122 l_reval_reserve,
2123 l_ytd_prod,
2124 l_ytd_reval_exp,
2125 l_prior_fy_exp,
2126 l_primary_rsv,
2127 l_deprn_override_flag;
2128 IF (ds_row%NOTFOUND) THEN
2129 exit;
2130 END IF;
2131
2132 IF ((l_asset_id = l_last_asset_id) AND
2133 (l_book_type_code = l_last_book)) THEN
2134 /*
2135 dbms_output.put_line('adding previous rows amounts');
2136 dbms_output.put_line('l_last_deprn_rsv' || l_last_deprn_rsv);
2137 dbms_output.put_line('l_deprn_amount' || l_deprn_amount);
2138 dbms_output.put_line('l_deprn_reserve' || l_deprn_reserve);
2139 */
2140
2141 -- Fix for Bug 990059
2142 -- When asset is fully retired need to set deprn_reserve
2143 -- to 0
2144 IF (l_primary_rsv <> 0) then
2145 -- BUG# 1662585
2146 -- need to get any effects from partial retirements
2147 -- and reinstatements
2148
2149 select sum(decode(debit_credit_flag,
2150 'DR', -1 * adjustment_amount,
2151 adjustment_amount))
2152 into l_ret_reserve
2153 from fa_mc_adjustments
2154 where asset_id = l_asset_id
2155 and book_type_code = l_book_type_code
2156 and set_of_books_id = p_rsob_id
2157 and period_counter_created = l_period_counter
2158 and source_type_code = 'RETIREMENT'
2159 and adjustment_type = 'RESERVE';
2160
2161 l_deprn_reserve := l_deprn_amount + l_last_deprn_rsv +
2162 nvl(l_ret_reserve, 0);
2163 END IF;
2164 l_ytd_deprn := l_deprn_amount + l_last_ytd_deprn;
2165 l_reval_reserve := l_last_reval_rsv - l_reval_exp;
2166 l_ytd_reval_exp := l_reval_exp + l_last_ytd_reval_exp;
2167 ELSE
2168 l_last_book := l_book_type_code;
2169 l_last_asset_id := l_asset_id;
2170 END IF;
2171 l_last_deprn_rsv := l_deprn_reserve;
2172 l_last_ytd_deprn := l_ytd_deprn;
2173 l_last_reval_rsv := l_reval_reserve;
2174 l_last_ytd_reval_exp := l_ytd_reval_exp;
2175
2176
2177 INSERT INTO fa_mc_deprn_summary(set_of_books_id,
2178 book_type_code,
2179 asset_id,
2180 deprn_run_date,
2181 deprn_amount,
2182 ytd_deprn,
2183 deprn_reserve,
2184 deprn_source_code,
2185 adjusted_cost,
2186 bonus_rate,
2187 ltd_production,
2188 period_counter,
2189 production,
2190 reval_amortization,
2191 reval_amortization_basis,
2192 reval_deprn_expense,
2193 reval_reserve,
2194 ytd_production,
2195 ytd_reval_deprn_expense,
2196 prior_fy_expense,
2197 converted_flag,
2198 deprn_override_flag)
2199 VALUES(
2200 p_rsob_id,
2201 l_book_type_code,
2202 l_asset_id,
2203 l_deprn_run_date,
2204 l_deprn_amount,
2205 l_ytd_deprn,
2206 l_deprn_reserve,
2207 l_deprn_source_code,
2208 l_adjusted_cost,
2209 l_bonus_rate,
2210 l_ltd_prod,
2211 l_period_counter,
2212 l_prod,
2213 l_reval_amort,
2214 l_reval_amort_basis,
2215 l_reval_exp,
2216 l_reval_reserve,
2217 l_ytd_prod,
2218 l_ytd_reval_exp,
2219 l_prior_fy_exp,
2220 'Y',
2221 l_deprn_override_flag);
2222 END LOOP;
2223 CLOSE ds_row;
2224 ELSIF (p_convert_order = 'L') THEN
2225 INSERT INTO fa_mc_deprn_summary(set_of_books_id,
2226 book_type_code,
2227 asset_id,
2228 deprn_run_date,
2229 deprn_amount,
2230 ytd_deprn,
2231 deprn_reserve,
2232 deprn_source_code,
2233 adjusted_cost,
2234 bonus_rate,
2235 ltd_production,
2236 period_counter,
2237 production,
2238 reval_amortization,
2239 reval_amortization_basis,
2240 reval_deprn_expense,
2241 reval_reserve,
2242 ytd_production,
2243 ytd_reval_deprn_expense,
2244 prior_fy_expense,
2245 converted_flag,
2246 deprn_override_flag)
2247 SELECT
2248 p_rsob_id,
2249 ds.book_type_code,
2250 ds.asset_id,
2251 ds.deprn_run_date,
2252 DECODE(p_mau,
2253 NULL, ROUND(
2254 (mcbk.cost * (ds.deprn_amount /
2255 decode(bk.cost,0,1,bk.cost))),
2256 p_precision),
2257 ROUND(
2258 (mcbk.cost * (ds.deprn_amount /
2259 decode(bk.cost,0,1,bk.cost)))/
2260 p_mau) * p_mau),
2261 DECODE(p_mau,
2262 NULL, ROUND(
2263 (mcbk.cost * (ds.ytd_deprn /
2264 decode(bk.cost,0,1,bk.cost))),
2265 p_precision),
2266 ROUND(
2267 (mcbk.cost * (ds.ytd_deprn /
2268 decode(bk.cost,0,1,bk.cost))) /
2269 p_mau) * p_mau),
2270 DECODE(p_mau,
2271 NULL, ROUND(
2272 (mcbk.cost * (ds.deprn_reserve/
2273 decode(bk.cost,0,1,bk.cost))),
2274 p_precision),
2275 ROUND(
2276 (mcbk.cost * (ds.deprn_reserve/
2277 decode(bk.cost,0,1,bk.cost))) /
2278 p_mau) * p_mau),
2279 ds.deprn_source_code,
2280 mcbk.adjusted_cost,
2281 ds.bonus_rate,
2282 ds.ltd_production,
2283 ds.period_counter,
2284 ds.production,
2285 DECODE(p_mau,
2286 NULL, ROUND(
2287 (mcbk.cost * (ds.reval_amortization/
2288 decode(bk.cost,0,1,bk.cost))),
2289 p_precision),
2290 ROUND(
2291 (mcbk.cost * (ds.reval_amortization/
2292 decode(bk.cost,0,1,bk.cost)))/
2293 p_mau) * p_mau),
2294 DECODE(p_mau,
2295 NULL, ROUND(
2296 (mcbk.cost * (ds.reval_amortization_basis/
2297 decode(bk.cost,0,1,bk.cost))),
2298 p_precision),
2299 ROUND(
2300 (mcbk.cost * (ds.reval_amortization_basis/
2301 decode(bk.cost,0,1,bk.cost))) /
2302 p_mau) * p_mau),
2303 DECODE(p_mau,
2304 NULL, ROUND(
2305 (mcbk.cost * (ds.reval_deprn_expense/
2306 decode(bk.cost,0,1,bk.cost))),
2307 p_precision),
2308 ROUND(
2309 (mcbk.cost * (ds.reval_deprn_expense/
2310 decode(bk.cost,0,1,bk.cost)))/
2311 p_mau) * p_mau),
2312 DECODE(p_mau,
2313 NULL, ROUND(
2314 (mcbk.cost * (ds.reval_reserve/
2315 decode(bk.cost,0,1,bk.cost))),
2316 p_precision),
2317 ROUND(
2318 (mcbk.cost * (ds.reval_reserve/
2319 decode(bk.cost,0,1,bk.cost))) /
2320 p_mau) * p_mau),
2321 ds.ytd_production,
2322 DECODE(p_mau,
2323 NULL, ROUND(
2324 (mcbk.cost * (ds.ytd_reval_deprn_expense/
2325 decode(bk.cost,0,1,bk.cost))),
2326 p_precision),
2327 ROUND(
2328 (mcbk.cost * (ds.ytd_reval_deprn_expense/
2329 decode(bk.cost,0,1,bk.cost))) /
2330 p_mau) * p_mau),
2331 DECODE(p_mau,
2332 NULL, ROUND(
2333 (mcbk.cost * (ds.prior_fy_expense/
2334 decode(bk.cost,0,1,bk.cost))),
2335 p_precision),
2336 ROUND(
2337 (mcbk.cost * (ds.prior_fy_expense/
2338 decode(bk.cost,0,1,bk.cost))) /
2339 p_mau) * p_mau),
2340 'Y',
2341 ds.deprn_override_flag
2342 FROM
2343 fa_deprn_summary ds,
2344 fa_deprn_periods dp,
2345 fa_mc_books mcbk,
2346 fa_books bk,
2347 fa_mc_conversion_rates cr
2348 WHERE
2349 cr.book_type_code = p_book_type_code AND
2350 cr.set_of_books_id = p_rsob_id AND
2351 cr.status = 'S' AND
2352 ds.period_counter = cr.last_period_counter AND
2353 ds.deprn_source_code = 'DEPRN' AND
2354 dp.book_type_code = mcbk.book_type_code AND
2355 dp.period_counter = ds.period_counter AND
2356 dp.book_type_code = ds.book_type_code AND
2357 nvl(dp.period_CLOSE_date, sysdate)
2358 between bk.date_effective and
2359 nvl(bk.date_ineffective, sysdate) AND
2360 bk.transaction_header_id_in =
2361 mcbk.transaction_header_id_in AND
2362 bk.asset_id = cr.asset_id AND
2363 bk.asset_id = mcbk.asset_id AND
2364 ds.asset_id = mcbk.asset_id AND
2365 bk.book_type_code = cr.book_type_code AND
2366 mcbk.book_type_code = bk.book_type_code AND
2367 mcbk.set_of_books_id = cr.set_of_books_id;
2368
2369 END IF;
2370
2371 if (g_print_debug) then
2372 fa_debug_pkg.add('convert_assets',
2373 'Converted FA_DEPRN_SUMMARY records',
2374 'success');
2375 end if;
2376
2377
2378 EXCEPTION
2379 WHEN OTHERS THEN
2380 fa_srvr_msg.add_sql_error (
2381 calling_fn => 'fa_mc_upg2_pkg.convert_summary');
2382 app_exception.raise_exception;
2383
2384 END convert_deprn_summary;
2385
2386
2387 PROCEDURE convert_deprn_detail(
2388 p_rsob_id IN NUMBER,
2389 p_book_type_code IN VARCHAR2,
2390 p_mau IN NUMBER,
2391 p_precision IN NUMBER) IS
2392 /* ************************************************************************
2393 This procedure will convert the rows in fa_deprn_detail for the
2394 candidate assets. Candidate assets will have rows in fa_deprn_detail
2395 converted for the period_counters that were converted in
2396 fa_deprn_summary. All fa_deprn_detail rows will be fetched one by one
2397 and converted using logic similar to FAUPDD to round COST,
2398 DEPRN_RESERVE, REVAL_RESERVE and REVAL_AMORTIZATION to the active
2399 distributions at the end of a period. YTD_DEPRN will not be rounded
2400 as it is not used to post to GL and is used only for reporting. The
2401 ORDER BY clause ensures that assets will be rounded period by period.
2402 ************************************************************************ */
2403
2404 l_part_cost number;
2405 l_cost number;
2406 l_app_cost number;
2407
2408 l_part_deprn_rsv number;
2409 l_deprn_rsv number;
2410 l_app_deprn_rsv number;
2411
2412 l_total_units number;
2413 l_units_assigned number;
2414 l_app_units number;
2415
2416 l_reval_rsv number;
2417 l_app_reval_rsv number;
2418 l_part_reval_rsv number;
2419
2420 l_part_reval_amort number;
2421 l_reval_amort number;
2422 l_app_reval_amort number;
2423
2424 l_part_reval_exp number;
2425 l_reval_exp number;
2426 l_app_reval_exp number;
2427
2428 l_date_ineff varchar2(18);
2429 l_p_close_date varchar2(18);
2430 l_book_type_code varchar2(30);
2431
2432 l_last_period_counter number := 0;
2433 l_last_asset_id number := 0;
2434 l_last_book varchar2(30);
2435 l_asset_id number;
2436
2437 l_total_deprn_amt number;
2438 l_distribution_id number;
2439 l_period_counter number;
2440 l_app_deprn_amt number;
2441 l_deprn_source_code varchar2(1);
2442 l_deprn_amount number;
2443 l_deprn_run_date date;
2444 l_ytd_deprn number;
2445 l_deprn_adjustment_amount number;
2446 l_addition_cost_to_clear number;
2447 l_deprn_expense_je_line_num number;
2448 l_deprn_reserve_je_line_num number;
2449 l_reval_amort_je_line_num number;
2450 l_reval_reserve_je_line_num number;
2451 l_je_header_id number;
2452 l_ytd_reval_deprn_expense number;
2453 l_prev_ytd_deprn number;
2454 l_bonus_deprn_expense_ccid number;
2455 l_bonus_deprn_reserve_ccid number;
2456 l_deprn_expense_ccid number;
2457 l_deprn_reserve_ccid number;
2458 l_reval_amort_ccid number;
2459 l_reval_reserve_ccid number;
2460
2461 -- cursor to select all rows from fa_deprn_detail
2462 CURSOR dd_row IS
2463 SELECT /*+ ordered leading(cr)
2464 index(ds fa_mc_deprn_summary_u1)
2465 index(dd fa_deprn_detail_n1)
2466 index(dh fa_distribution_history_u1)
2467 index(dp fa_deprn_periods_u3)
2468 index(bk fa_books_n1)
2469 index(mcbk fa_mc_books_u1)
2470 index(ah fa_asset_history_n2) */
2471 dd.book_type_code,
2472 dd.asset_id,
2473 dd.distribution_id,
2474 dd.period_counter,
2475 nvl(ds.deprn_reserve,0),
2476 mcbk.cost,
2477 to_char(nvl(dh.date_ineffective, sysdate+1),
2478 'YYYYMMDD HH24:MI:SS'),
2479 to_char(nvl(dp.period_close_date, sysdate),
2480 'YYYYMMDD HH24:MI:SS'),
2481 nvl(dh.units_assigned,0),
2482 nvl(ah.units,0),
2483 DECODE(p_mau,
2484 NULL, ROUND(
2485 (mcbk.cost * (dd.deprn_amount /
2486 decode(bk.cost,0,1,bk.cost))),
2487 p_precision),
2488 ROUND(
2489 (mcbk.cost * (dd.deprn_amount /
2490 decode(bk.cost,0,1,bk.cost))) /
2491 p_mau) * p_mau),
2492 DECODE(p_mau,
2493 NULL, ROUND(
2494 (mcbk.cost * (dd.ytd_deprn /
2495 decode(bk.cost,0,1,bk.cost))),
2496 p_precision),
2497 ROUND(
2498 (mcbk.cost * (dd.ytd_deprn /
2499 decode(bk.cost,0,1,bk.cost))) /
2500 p_mau) * p_mau),
2501 DECODE(p_mau,
2502 NULL, ROUND(
2503 (mcbk.cost *(dd.deprn_adjustment_amount
2504 / decode(bk.cost,0,1,bk.cost))),
2505 p_precision),
2506 ROUND(
2507 (mcbk.cost *(dd.deprn_adjustment_amount
2508 / decode(bk.cost,0,1,bk.cost))) /
2509 p_mau) * p_mau),
2510 dd.deprn_source_code,
2511 dd.deprn_expense_je_line_num,
2512 dd.deprn_reserve_je_line_num,
2513 dd.reval_amort_je_line_num,
2514 dd.reval_reserve_je_line_num,
2515 dd.je_header_id,
2516 ds.reval_amortization,
2517 ds.reval_deprn_expense,
2518 ds.reval_reserve,
2519 DECODE(p_mau,
2520 NULL, ROUND(
2521 (mcbk.cost *(dd.ytd_reval_deprn_expense
2522 / decode(bk.cost,0,1,bk.cost))),
2523 p_precision),
2524 ROUND(
2525 (mcbk.cost *(dd.ytd_reval_deprn_expense
2526 / decode(bk.cost,0,1,bk.cost))) /
2527 p_mau) * p_mau),
2528 ds.deprn_amount,
2529 dd.deprn_run_date,
2530 dd.bonus_deprn_expense_ccid,
2531 dd.bonus_deprn_reserve_ccid,
2532 dd.deprn_expense_ccid,
2533 dd.deprn_reserve_ccid,
2534 dd.reval_amort_ccid,
2535 dd.reval_reserve_ccid
2536 FROM
2537 fa_mc_deprn_summary ds,
2538 fa_deprn_detail dd,
2539 fa_deprn_periods dp,
2540 fa_books bk,
2541 fa_mc_books mcbk,
2542 fa_distribution_history dh,
2543 fa_asset_history ah,
2544 fa_mc_conversion_rates cr
2545 WHERE
2546 cr.status = 'S' AND
2547 cr.asset_id = ds.asset_id AND
2548 cr.book_type_code = p_book_type_code AND
2549 ds.book_type_code = cr.book_type_code AND
2550 ds.book_type_code = dd.book_type_code AND
2551 cr.set_of_books_id = p_rsob_id AND
2552 ds.set_of_books_id = cr.set_of_books_id AND
2553 ds.asset_id = dd.asset_id AND
2554 bk.book_type_code = ds.book_type_code AND
2555 mcbk.set_of_books_id = ds.set_of_books_id AND
2556 bk.transaction_header_id_in =
2557 mcbk.transaction_header_id_in AND
2558 ds.period_counter = dd.period_counter AND
2559 dp.book_type_code = dd.book_type_code AND
2560 dp.period_counter =
2561 DECODE(ds.deprn_source_code,
2562 'DEPRN', dd.period_counter,
2563 'BOOKS', dd.period_counter + 1) AND
2564 bk.book_type_code = dd.book_type_code AND
2565 mcbk.book_type_code = bk.book_type_code AND
2566 bk.asset_id = dd.asset_id AND
2567 mcbk.asset_id = dd.asset_iD AND
2568 nvl(dp.period_close_date, sysdate)
2569 between bk.date_effective and
2570 nvl(bk.date_ineffective, sysdate) AND
2571 dh.distribution_id = dd.distribution_id AND
2572 ah.asset_id = dd.asset_id AND
2573 nvl(dp.period_close_date, sysdate)
2574 between ah.date_effective and
2575 nvl(ah.date_ineffective, sysdate)
2576 order by
2577 dd.book_type_code,
2578 dd.period_counter,
2579 dd.asset_id,
2580 dd.distribution_id;
2581
2582 BEGIN
2583
2584 if (g_print_debug) then
2585 fa_debug_pkg.add('convert_assets',
2586 'Converting FA_DEPRN_DETAIL records',
2587 'start');
2588 end if;
2589
2590 OPEN dd_row;
2591 LOOP
2592 FETCH dd_row into l_book_type_code,
2593 l_asset_id,
2594 l_distribution_id,
2595 l_period_counter,
2596 l_deprn_rsv,
2597 l_cost,
2598 l_date_ineff,
2599 l_p_close_date,
2600 l_units_assigned,
2601 l_total_units,
2602 l_deprn_amount,
2603 l_ytd_deprn,
2604 l_deprn_adjustment_amount,
2605 l_deprn_source_code,
2606 l_deprn_expense_je_line_num,
2607 l_deprn_reserve_je_line_num,
2608 l_reval_amort_je_line_num,
2609 l_reval_reserve_je_line_num,
2610 l_je_header_id,
2611 l_reval_amort,
2612 l_reval_exp,
2613 l_reval_rsv,
2614 l_ytd_reval_deprn_expense,
2615 l_total_deprn_amt,
2616 l_deprn_run_date,
2617 l_bonus_deprn_expense_ccid,
2618 l_bonus_deprn_reserve_ccid,
2619 l_deprn_expense_ccid,
2620 l_deprn_reserve_ccid,
2621 l_reval_amort_ccid,
2622 l_reval_reserve_ccid;
2623
2624 IF (dd_row%NOTFOUND) THEN
2625 exit;
2626 END IF;
2627
2628 IF ((l_asset_id <> l_last_asset_id) OR
2629 (l_period_counter <> l_last_period_counter) OR
2630 (l_book_type_code <> l_last_book)) THEN
2631
2632 l_app_cost := 0;
2633 l_app_deprn_rsv := 0;
2634 l_app_units := 0;
2635
2636 l_app_reval_rsv := 0;
2637 l_app_reval_amort := 0;
2638 l_app_reval_exp := 0;
2639 l_app_deprn_amt := 0;
2640
2641 l_last_asset_id := l_asset_id;
2642 l_last_period_counter := l_period_counter;
2643 l_last_book := l_book_type_code;
2644
2645 END IF;
2646
2647 -- use the active distributions to allocate cost and
2648 -- reserve
2649
2650 -- If distribution is active at end of period
2651
2652 IF (l_date_ineff > l_p_close_date) THEN
2653 l_app_units := l_units_assigned + l_app_units;
2654 l_part_deprn_rsv := (l_deprn_rsv * l_units_assigned)/
2655 l_total_units;
2656
2657 l_part_cost := (l_cost * l_units_assigned) /
2658 l_total_units;
2659 l_part_reval_rsv := (l_reval_rsv * l_units_assigned)/
2660 l_total_units;
2661 l_part_reval_amort := (l_reval_amort * l_units_assigned)
2662 / l_total_units;
2663 l_part_reval_exp := (l_reval_exp * l_units_assigned)/
2664 l_total_units;
2665 -- now round all the amounts
2666
2667 IF (p_mau IS NOT NULL) THEN
2668 l_part_deprn_rsv := (ROUND( l_part_deprn_rsv/p_mau)
2669 * p_mau );
2670 l_part_cost := (ROUND( l_part_cost/p_mau)
2671 * p_mau );
2672 l_part_reval_rsv := (ROUND( l_part_reval_rsv/p_mau)
2673 * p_mau );
2674 l_part_reval_amort := (ROUND(l_part_reval_amort/
2675 p_mau)
2676 * p_mau );
2677 l_part_reval_exp := (ROUND(l_part_reval_exp/p_mau)
2678 * p_mau );
2679 ELSE
2680 l_part_deprn_rsv := ROUND( l_part_deprn_rsv,
2681 p_precision);
2682 l_part_cost := ROUND( l_part_cost,
2683 p_precision );
2684 l_part_reval_rsv := ROUND( l_part_reval_rsv,
2685 p_precision );
2686 l_part_reval_amort := ROUND( l_part_reval_amort,
2687 p_precision );
2688 l_part_reval_exp := ROUND( l_part_reval_exp,
2689 p_precision );
2690 END IF;
2691
2692 l_app_deprn_rsv := l_part_deprn_rsv + l_app_deprn_rsv;
2693 l_app_cost := l_part_cost + l_app_cost;
2694 l_app_reval_rsv := l_part_reval_rsv + l_app_reval_rsv;
2695 l_app_reval_amort := l_part_reval_amort +
2696 l_app_reval_amort;
2697 l_app_reval_exp := l_part_reval_exp + l_app_reval_exp;
2698
2699
2700 IF (l_total_units = l_app_units) THEN
2701 l_part_cost := l_part_cost +
2702 (l_cost - l_app_cost);
2703 l_part_deprn_rsv := l_part_deprn_rsv +
2704 (l_deprn_rsv - l_app_deprn_rsv);
2705 l_part_reval_rsv := l_part_reval_rsv +
2706 (l_reval_rsv - l_app_reval_rsv);
2707 l_part_reval_amort := l_part_reval_amort +
2708 (l_reval_amort - l_app_reval_amort);
2709 l_part_reval_exp := l_part_reval_exp +
2710 (l_reval_exp - l_app_reval_exp);
2711 END IF;
2712 ELSE
2713 l_part_cost := 0;
2714 l_part_deprn_rsv := 0;
2715 l_part_reval_rsv := 0;
2716 l_part_reval_amort := 0;
2717 l_part_reval_exp := 0;
2718 END IF;
2719
2720 l_app_deprn_amt := l_deprn_amount + l_app_deprn_amt;
2721
2722 IF (l_total_units = l_app_units) THEN
2723 l_deprn_amount := l_total_deprn_amt -
2724 (l_app_deprn_amt - l_deprn_amount);
2725 END IF;
2726
2727 /*
2728 IF (l_prev_ytd_deprn <> 0) THEN
2729 l_ytd_deprn := l_deprn_amount + l_prev_ytd_deprn;
2730 END IF;
2731 */
2732
2733 /*
2734 dbms_output.put_line('inserting set_of_books_id' || p_rsob_id);
2735 dbms_output.put_line('inserting book_type_code' || p_book_type_code);
2736 dbms_output.put_line('inserting asset_id ' || l_asset_id);
2737 dbms_output.put_line('inserting period_counter' || l_period_counter);
2738 dbms_output.put_line('inserting distribution_id' || l_distribution_id);
2739 */
2740 -- Now insert the row into FA_MC_DEPRN_DETAIL
2741
2742 INSERT INTO FA_MC_DEPRN_DETAIL(
2743 set_of_books_id,
2744 book_type_code,
2745 asset_id,
2746 period_counter,
2747 distribution_id,
2748 deprn_source_code,
2749 deprn_run_date,
2750 deprn_amount,
2751 ytd_deprn,
2752 deprn_reserve,
2753 addition_cost_to_clear,
2754 cost,
2755 deprn_adjustment_amount,
2756 deprn_expense_je_line_num,
2757 deprn_reserve_je_line_num,
2758 reval_amort_je_line_num,
2759 reval_reserve_je_line_num,
2760 je_header_id,
2761 reval_amortization,
2762 reval_deprn_expense,
2763 reval_reserve,
2764 ytd_reval_deprn_expense,
2765 converted_flag,
2766 bonus_deprn_expense_ccid,
2767 bonus_deprn_reserve_ccid,
2768 deprn_expense_ccid,
2769 deprn_reserve_ccid,
2770 reval_amort_ccid,
2771 reval_reserve_ccid)
2772 VALUES(
2773 p_rsob_id,
2774 p_book_type_code,
2775 l_asset_id,
2776 l_period_counter,
2777 l_distribution_id,
2778 l_deprn_source_code,
2779 l_deprn_run_date,
2780 l_deprn_amount,
2781 l_ytd_deprn,
2782 l_part_deprn_rsv,
2783 DECODE(l_deprn_source_code,
2784 'B', l_part_cost, 0),
2785 DECODE(l_deprn_source_code,
2786 'D', l_part_cost, 0),
2787 l_deprn_adjustment_amount,
2788 l_deprn_expense_je_line_num,
2789 l_deprn_reserve_je_line_num,
2790 l_reval_amort_je_line_num,
2791 l_reval_reserve_je_line_num,
2792 l_je_header_id,
2793 l_part_reval_amort,
2794 l_part_reval_exp,
2795 l_part_reval_rsv,
2796 l_ytd_reval_deprn_expense,
2797 'Y',
2798 l_bonus_deprn_expense_ccid,
2799 l_bonus_deprn_reserve_ccid,
2800 l_deprn_expense_ccid,
2801 l_deprn_reserve_ccid,
2802 l_reval_amort_ccid,
2803 l_reval_reserve_ccid
2804 );
2805
2806 END LOOP;
2807 CLOSE dd_row;
2808
2809 if (g_print_debug) then
2810 fa_debug_pkg.add('convert_assets',
2811 'Converted FA_DEPRN_DETAIL records',
2812 'success');
2813 end if;
2814
2815 EXCEPTION
2816 WHEN OTHERS THEN
2817 fa_srvr_msg.add_sql_error (
2818 calling_fn => 'fa_mc_upg2_pkg.convert_detail');
2819 app_exception.raise_exception;
2820
2821 END convert_deprn_detail;
2822
2823
2824 PROCEDURE convert_deprn_periods (
2825 p_rsob_id IN NUMBER,
2826 p_book_type_code IN VARCHAR2,
2827 p_start_pc IN NUMBER,
2828 p_end_pc IN NUMBER) IS
2829 /* ************************************************************************
2830 This procedure will convert fa_deprn_periods records for the Primary
2831 Book to the reporting book. All existing records in fa_mc_deprn_periods
2832 for the reporting book are first deleted. To preserve the trail as to
2833 when the reporting book was first set up, fa_deprn_period rows will be
2834 fetched using the first period counter in fa_mc_deprn_periods all the
2835 way upto the current open period. This will ensure that the primary
2836 book and reporting book have the same open periods after conversion
2837 is completed.
2838 ************************************************************************ */
2839
2840 l_pc number;
2841 BEGIN
2842 if (g_print_debug) then
2843 fa_debug_pkg.add('convert_assets',
2844 'Converting FA_DEPRN_PERIODS records',
2845 'start');
2846 end if;
2847
2848 SELECT min(period_counter)
2849 INTO l_pc
2850 FROM fa_mc_deprn_periods
2851 WHERE
2852 set_of_books_id = p_rsob_id AND
2853 book_type_code = p_book_type_code;
2854
2855 DELETE FROM fa_mc_deprn_periods
2856 WHERE
2857 set_of_books_id = p_rsob_id AND
2858 book_type_code = p_book_type_code;
2859
2860 INSERT INTO fa_mc_deprn_periods(
2861 set_of_books_id,
2862 book_type_code,
2863 period_name,
2864 period_counter,
2865 fiscal_year,
2866 period_num,
2867 period_open_date,
2868 period_close_date,
2869 depreciation_batch_id,
2870 retirement_batch_id,
2871 reclass_batch_id,
2872 transfer_batch_id,
2873 addition_batch_id,
2874 adjustment_batch_id,
2875 deferred_deprn_batch_id,
2876 calendar_period_open_date,
2877 calendar_period_close_date,
2878 cip_addition_batch_id,
2879 cip_adjustment_batch_id,
2880 cip_reclass_batch_id,
2881 cip_retirement_batch_id,
2882 cip_reval_batch_id,
2883 cip_transfer_batch_id,
2884 reval_batch_id,
2885 deprn_adjustment_batch_id)
2886 SELECT
2887 p_rsob_id,
2888 dp.book_type_code,
2889 dp.period_name,
2890 dp.period_counter,
2891 dp.fiscal_year,
2892 dp.period_num,
2893 dp.period_open_date,
2894 dp.period_close_date,
2895 dp.depreciation_batch_id,
2896 dp.retirement_batch_id,
2897 dp.reclass_batch_id,
2898 dp.transfer_batch_id,
2899 dp.addition_batch_id,
2900 dp.adjustment_batch_id,
2901 dp.deferred_deprn_batch_id,
2902 dp.calendar_period_open_date,
2903 dp.calendar_period_close_date,
2904 dp.cip_addition_batch_id,
2905 dp.cip_adjustment_batch_id,
2906 dp.cip_reclass_batch_id,
2907 dp.cip_retirement_batch_id,
2908 dp.cip_reval_batch_id,
2909 dp.cip_transfer_batch_id,
2910 dp.reval_batch_id,
2911 dp.deprn_adjustment_batch_id
2912 FROM
2913 fa_deprn_periods dp
2914 WHERE
2915 dp.book_type_code = p_book_type_code AND
2916 dp.period_counter between l_pc and p_end_pc;
2917
2918 if (g_print_debug) then
2919 fa_debug_pkg.add('convert_assets',
2920 'Converted FA_DEPRN_PERIODS records',
2921 'success');
2922 end if;
2923
2924 EXCEPTION
2925 WHEN OTHERS THEN
2926 fa_srvr_msg.add_sql_error (
2927 calling_fn => 'fa_mc_upg2_pkg.convert_deprn_periods');
2928 app_exception.raise_exception;
2929
2930 END convert_deprn_periods;
2931
2932
2933 PROCEDURE convert_deferred_deprn (
2934 p_rsob_id IN NUMBER,
2935 p_book_type_code IN VARCHAR2,
2936 p_start_pc IN NUMBER,
2937 p_end_pc IN NUMBER,
2938 p_numerator_rate IN NUMBER,
2939 p_denominator_rate IN NUMBER,
2940 p_mau IN NUMBER,
2941 p_precision IN NUMBER) IS
2942 /* ************************************************************************
2943 This procedure will convert all rows in fa_deferred_deprn to the
2944 reporting book. fa_deferred_deprn will only be converted if the Primary
2945 Book is a TAX book since deferred deprn is relevant to the difference
2946 in depreciation between tax and corporate book for a given period.
2947 deferred deprn information is posted to the GL set of books that
2948 TAX book is associated to in the primary book and thus this only
2949 needs to be converted for candidate assets in a TAX book with reporting
2950 books associated to it.
2951 ************************************************************************ */
2952
2953 l_book_class varchar2(15);
2954 BEGIN
2955
2956 if (g_print_debug) then
2957 fa_debug_pkg.add('convert_assets',
2958 'Converting FA_DEFERRED_DEPRN records',
2959 'start');
2960 end if;
2961
2962 SELECT book_class
2963 INTO l_book_class
2964 FROM fa_book_controls
2965 WHERE book_type_code = p_book_type_code;
2966
2967 IF (l_book_class = 'TAX') THEN
2968
2969 INSERT INTO fa_mc_deferred_deprn(
2970 set_of_books_id,
2971 corp_book_type_code,
2972 tax_book_type_code,
2973 asset_id,
2974 distribution_id,
2975 deferred_deprn_expense_ccid,
2976 deferred_deprn_reserve_ccid,
2977 deferred_deprn_expense_amount,
2978 deferred_deprn_reserve_amount,
2979 corp_period_counter,
2980 tax_period_counter,
2981 je_header_id,
2982 expense_je_line_num,
2983 reserve_je_line_num)
2984 SELECT
2985 p_rsob_id,
2986 dd.corp_book_type_code,
2987 p_book_type_code,
2988 dd.asset_id,
2989 dd.distribution_id,
2990 dd.deferred_deprn_expense_ccid,
2991 dd.deferred_deprn_reserve_ccid,
2992 DECODE(p_mau,
2993 NULL, ROUND(
2994 DECODE(cr.conversion_basis,
2995 'C',(dd.deferred_deprn_expense_amount *
2996 (cr.cost/cr.primary_cur_cost)),
2997
2998 'R',(dd.deferred_deprn_expense_amount/
2999 p_denominator_rate)
3000 *
3001 DECODE(cr.exchange_rate,
3002 NULL, p_numerator_rate,
3003 cr.exchange_rate)
3004 ), p_precision),
3005 ROUND(
3006 DECODE(cr.conversion_basis,
3007 'C',(dd.deferred_deprn_expense_amount *
3008 (cr.cost/cr.primary_cur_cost)),
3009 'R',(dd.deferred_deprn_expense_amount/
3010 p_denominator_rate)
3011 *
3012 DECODE(cr.exchange_rate,
3013 NULL, p_numerator_rate,
3014 cr.exchange_rate))
3015 / p_mau) * p_mau),
3016 DECODE(p_mau,
3017 NULL, ROUND(
3018 DECODE(cr.conversion_basis,
3019 'C',(dd.deferred_deprn_reserve_amount *
3020 (cr.cost/cr.primary_cur_cost)),
3021 'R',(dd.deferred_deprn_reserve_amount/
3022 p_denominator_rate)
3023 *
3024 DECODE(cr.exchange_rate,
3025 NULL, p_numerator_rate,
3026 cr.exchange_rate)
3027 ), p_precision),
3028 ROUND(
3029 DECODE(cr.conversion_basis,
3030 'C',(dd.deferred_deprn_reserve_amount *
3031 (cr.cost/cr.primary_cur_cost)),
3032 'R',(dd.deferred_deprn_reserve_amount/
3033 p_denominator_rate)
3034 *
3035 DECODE(cr.exchange_rate,
3036 NULL, p_numerator_rate,
3037 cr.exchange_rate))
3038 / p_mau) * p_mau),
3039 dd.corp_period_counter,
3040 dd.tax_period_counter,
3041 dd.je_header_id,
3042 dd.expense_je_line_num,
3043 dd.reserve_je_line_num
3044 FROM
3045 fa_deferred_deprn dd,
3046 fa_book_controls bc,
3047 fa_mc_conversion_rates cr
3048 WHERE
3049 cr.book_type_code = p_book_type_code AND
3050 cr.set_of_books_id = p_rsob_id AND
3051 cr.status = 'S' AND
3052 bc.book_type_code = cr.book_type_code AND
3053 cr.asset_id = dd.asset_id AND
3054 dd.corp_book_type_code = bc.distribution_source_book AND
3055 dd.tax_book_type_code = bc.book_type_code;
3056
3057 END IF;
3058
3059 if (g_print_debug) then
3060 fa_debug_pkg.add('convert_assets',
3061 'Converted FA_DEFERRED_DEPRN records',
3062 'success');
3063 end if;
3064
3065
3066 EXCEPTION
3067 WHEN OTHERS THEN
3068 fa_srvr_msg.add_sql_error (
3069 calling_fn => 'fa_mc_upg2_pkg.convert_deferred_deprn');
3070 app_exception.raise_exception;
3071
3072 END convert_deferred_deprn;
3073
3074 END FA_MC_UPG2_PKG;