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