[Home] [Help]
PACKAGE BODY: APPS.FA_MC_UPG3_PKG
Source
1 PACKAGE BODY FA_MC_UPG3_PKG AS
2 /* $Header: faxmcu3b.pls 115.7 2004/03/19 02:45:32 bridgway ship $ */
3
4 -- Global types and variables
5
6 -- record to hold the total entered and accounted amts for a ccid
7 TYPE ccid_table_rec IS RECORD (
8 ccid NUMBER(15),
9 entered_dr NUMBER, -- functional amount
10 entered_cr NUMBER,
11 accounted_dr NUMBER, -- reporting amount
12 accounted_cr NUMBER);
13
14 -- array of records to hold all the ccid's and amounts
15 TYPE ccid_table is TABLE of ccid_table_rec
16 INDEX BY BINARY_INTEGER;
17
18 -- array to hold the coa structure with all columns
19 TYPE flex_table IS TABLE OF VARCHAR2(25)
20 INDEX BY BINARY_INTEGER;
21
22 -- Count variables to hold the number of entries in all
23 -- ccid arrays
24
25 G_drsv_ccid_count NUMBER := 0;
26 G_dexp_ccid_count NUMBER := 0;
27 G_rrsv_ccid_count NUMBER := 0;
28 G_ramo_ccid_count NUMBER := 0;
29 G_adj_ccid_count NUMBER := 0;
30 G_dfrsv_ccid_count NUMBER := 0;
31 G_dfexp_ccid_count NUMBER := 0;
32 G_flex_seg_count NUMBER := 0;
33
34 -- ccid arrays
35 deprn_reserve_ccids ccid_table;
36 deprn_expense_ccids ccid_table;
37 reval_reserve_ccids ccid_table;
38 reval_amort_ccids ccid_table;
39 adjustments_ccids ccid_table;
40 def_deprn_rsv_ccids ccid_table;
41 def_deprn_exp_ccids ccid_table;
42
43 G_book_class VARCHAR2(15);
44 G_rbook_name VARCHAR2(30);
45 G_coa_id NUMBER;
46 G_re_ccid NUMBER;
47 coa_structure flex_table;
48 G_flex_buf VARCHAR2(750);
49 G_bal_seg_col VARCHAR2(25);
50 G_insert_buf VARCHAR2(4000); -- holds dynamic insert stmt
51 G_start_pc NUMBER;
52 G_end_pc NUMBER;
53
54 G_category_name VARCHAR2(25);
55 G_source_name VARCHAR2(25);
56 G_accounting_date DATE;
57 G_group_id NUMBER;
58 G_actual_flag VARCHAR2(1) := 'A';
59 G_status VARCHAR2(50) := 'NEW';
60 G_from_currency VARCHAR2(15);
61
62 g_print_debug boolean := fa_cache_pkg.fa_print_debug;
63
64 PROCEDURE check_conversion_status (
65 p_rsob_id IN NUMBER,
66 p_book_type_code IN VARCHAR2) IS
67 /* ************************************************************************
68 This procedure checks to see if conversion has been run the given
69 Primary Book - Reporting Book combination. Calculation of balances
70 and transferring to GL can only be run after conversion has completed
71 successfully and the conversion was not a fixed rate conversion.
72 This procedure raises an exception if conversion has not completed or
73 conversion was done using a fixed rate. We also retrieve the period
74 information from conversion history table.
75 ************************************************************************ */
76
77 l_status VARCHAR2(1);
78 l_fixed_conversion VARCHAR2(1);
79
80 conversion_error EXCEPTION;
81 fixed_rate_error EXCEPTION;
82
83 CURSOR get_status IS
84 SELECT
85 conversion_status,
86 period_counter_start,
87 period_counter_converted,
88 fixed_rate_conversion
89 FROM
90 fa_mc_conversion_history
91 WHERE
92 set_of_books_id = p_rsob_id AND
93 book_type_code = p_book_type_code;
94 BEGIN
95
96 OPEN get_status;
97 FETCH get_status INTO
98 l_status,
99 G_start_pc,
100 G_end_pc,
101 l_fixed_conversion;
102 IF (get_status%NOTFOUND) THEN
103 RAISE conversion_error;
104 ELSIF (l_status <> 'C') THEN
105 RAISE conversion_error;
106 ELSIF (l_fixed_conversion = 'Y') THEN
107 RAISE fixed_rate_error;
108 END IF;
109
110 EXCEPTION
111 WHEN conversion_error THEN
112 fa_srvr_msg.add_message(
113 calling_fn => 'fa_mc_upg3_pkg.check_conversion_status',
114 name => 'FA_MRC_NO_CONVERSION',
115 token1 => 'BOOK',
116 value1 => p_book_type_code,
117 token2 => 'REPORTING_BOOK',
118 value2 => G_rbook_name);
119 app_exception.raise_exception;
120
121 WHEN fixed_rate_error THEN
122 fa_srvr_msg.add_message(
123 calling_fn => 'fa_mc_upg3_pkg.check_conversion_status',
124 name => 'FA_MRC_FIXED_RATE_CONVERSION',
125 token1 => 'REPORTING_BOOK',
126 value1 => G_rbook_name);
127 app_exception.raise_exception;
128
129 WHEN OTHERS THEN
130 fa_srvr_msg.add_sql_error (
131 calling_fn => 'fa_mc_upg3_pkg.check_conversion_status');
132 app_exception.raise_exception;
133
134 END check_conversion_status;
135
136
137 PROCEDURE calculate_balances(
138 p_reporting_book IN VARCHAR2,
139 p_book_type_code IN VARCHAR2) IS
140 /* ************************************************************************
141 This procedure is the driving routine to calculate balances for all
142 the unique CCIDS that transactions to the candidate assets have been
143 posted to. Get the conversion info, check conversion status and ensure
144 that all periods have been posted to in the Primary Book before
145 calculating balances. After the balances have been obtained for all the
146 CCIDS, we will create rows in GL_INTERFACE for them to be posted.
147 Any out of balance entries for a balancing segment caused due to
148 activity to revenue/expense accounts for past years will be plugged
149 into retained earnings account for that balancing segment.
150 ************************************************************************ */
151
152 l_psob_id NUMBER;
153 l_rsob_id NUMBER;
154 l_to_currency VARCHAR2(10);
155 l_start_pc NUMBER;
156 l_end_pc NUMBER;
157 l_conv_date date;
158 l_conv_type VARCHAR2(15);
159 l_accounting_date DATE;
160
161 book_info_error EXCEPTION;
162
163 CURSOR get_book_info IS
164 SELECT
165 mbc.primary_set_of_books_id,
166 gls.set_of_books_id,
167 mbc.primary_currency_code,
168 bc.book_class
169 FROM
170 gl_sets_of_books gls,
171 fa_book_controls bc,
172 fa_mc_book_controls mbc
173 WHERE
174 mbc.book_type_code = p_book_type_code AND
175 mbc.set_of_books_id = gls.set_of_books_id AND
176 bc.book_type_code = mbc.book_type_code AND
177 gls.name = p_reporting_book;
178
179 BEGIN
180
181 if not fa_cache_pkg.fazcbc(X_book => p_book_type_code) then
182 RAISE book_info_error;
183 end if;
184
185 G_rbook_name := p_reporting_book;
186 OPEN get_book_info;
187 FETCH get_book_info INTO
188 l_psob_id,
189 l_rsob_id,
190 G_from_currency,
191 G_book_class;
192
193 IF (get_book_info%NOTFOUND) THEN
194 RAISE book_info_error;
195 END IF;
196 CLOSE get_book_info;
197
198 check_conversion_status(
199 l_rsob_id,
200 p_book_type_code);
201
202 fa_mc_upg1_pkg.get_conversion_info(
203 p_book_type_code,
204 l_psob_id,
205 l_rsob_id,
206 l_start_pc,
207 l_end_pc,
208 l_conv_date,
209 l_conv_type,
210 l_accounting_date);
211
212 G_accounting_date := l_accounting_date;
213 -- G_accounting_date := sysdate;
214
215 if (g_print_debug) then
216 fa_debug_pkg.add('calculate_balances',
217 'Accounting Date to post to GL',
218 G_accounting_date);
219 end if;
220
221
222 -- check if all periods in primary book have been posted to GL
223 check_period_posted(
224 p_book_type_code,
225 l_start_pc,
226 l_end_pc);
227 get_adj_balances(
228 l_rsob_id,
229 p_book_type_code);
230
231 get_deprn_reserve_balances(
232 l_rsob_id,
233 p_book_type_code);
234
235 get_deprn_exp_balances(
236 l_rsob_id,
237 p_book_type_code);
238
239 get_reval_rsv_balances(
240 l_rsob_id,
241 p_book_type_code);
242
243 get_reval_amort_balances(
244 l_rsob_id,
245 p_book_type_code);
246
247 IF (G_book_class = 'TAX') THEN
248 get_def_rsv_balances(
249 l_rsob_id,
250 p_book_type_code);
251 get_def_exp_balances(
252 l_rsob_id,
253 p_book_type_code);
254 END IF;
255
256 get_coa_info(
257 p_book_type_code,
258 l_rsob_id);
259
260 insert_balances(
261 l_rsob_id);
262
263 insert_ret_earnings(
264 p_book_type_code,
265 l_rsob_id);
266
267 COMMIT;
268
269 EXCEPTION
270 WHEN book_info_error THEN
271 fa_srvr_msg.add_message(
272 calling_fn => 'fa_mc_upg3_pkg.calculate_balances',
273 name => 'FA_MRC_BOOK_NOT_ASSOCIATED',
274 token1 => 'REPORTING_BOOK',
275 value1 => G_rbook_name,
276 token2 => 'BOOK',
277 value2 => p_book_type_code);
278 app_exception.raise_exception;
279
280 WHEN OTHERS THEN
281 fa_srvr_msg.add_sql_error (
282 calling_fn => 'fa_mc_upg3_pkg.calculate_balances');
283 app_exception.raise_exception;
284
285 END calculate_balances;
286
287
288 PROCEDURE check_period_posted(
289 p_book_type_code IN VARCHAR2,
290 p_start_pc IN NUMBER,
291 p_end_pc IN NUMBER) IS
292 /* ************************************************************************
293 This procedure checks to see if all periods in the Primary Book have
294 been posted to GL. To initialize balances for all the depreciation
295 expense and reserve accounts in the fiscal year being converted all
296 periods must be posted.
297 ************************************************************************ */
298
299 l_allow_posting VARCHAR2(3);
300 l_count NUMBER;
301 unposted_error EXCEPTION;
302
303 CURSOR unposted_periods IS
304 SELECT count(*)
305 FROM
306 fa_book_controls bc,
307 fa_deprn_periods dp
308 WHERE
309 bc.book_type_code = p_book_type_code AND
310 dp.book_type_code = p_book_type_code AND
311 dp.period_counter between
312 (bc.initial_period_counter + 1) and
313 p_end_pc - 1 AND
314 (dp.depreciation_batch_id is NULL AND
315 dp.retirement_batch_id is NULL AND
316 dp.reclass_batch_id is NULL AND
317 dp.transfer_batch_id is NULL AND
318 dp.addition_batch_id is NULL AND
319 dp.adjustment_batch_id is NULL AND
320 dp.deferred_deprn_batch_id is NULL AND
321 dp.cip_addition_batch_id is NULL AND
322 dp.cip_adjustment_batch_id is NULL AND
323 dp.cip_reclass_batch_id is NULL AND
324 dp.cip_retirement_batch_id is NULL AND
325 dp.cip_reval_batch_id is NULL AND
326 dp.cip_transfer_batch_id is NULL AND
327 dp.reval_batch_id is NULL AND
328 dp.deprn_adjustment_batch_id is NULL);
329
330 BEGIN
331 SELECT bc.gl_posting_allowed_flag
332 INTO l_allow_posting
333 FROM fa_book_controls bc
334 WHERE bc.book_type_code = p_book_type_code;
335
336 IF (l_allow_posting = 'YES') THEN
337 OPEN unposted_periods;
338 FETCH unposted_periods INTO l_count;
339 IF (l_count > 0) THEN
340 RAISE unposted_error;
341 END IF;
342 CLOSE unposted_periods;
343 END IF;
344
345 EXCEPTION
346 WHEN unposted_error THEN
347 fa_srvr_msg.add_message(
348 calling_fn => 'fa_mc_upg3_pkg.check_period_posted',
349 name => 'FA_MRC_PERIODS_NOT_POSTED',
350 token1 => 'BOOK',
351 value1 => p_book_type_code);
352 app_exception.raise_exception;
353 WHEN OTHERS THEN
354 fa_srvr_msg.add_sql_error (
355 calling_fn => 'fa_mc_upg3_pkg.check_period_posted');
356 app_exception.raise_exception;
357
358 END check_period_posted;
359
360
361 PROCEDURE get_adj_balances(
362 p_rsob_id IN NUMBER,
363 p_book_type_code IN VARCHAR2) IS
364 /* ************************************************************************
365 This procedure gets the balances for all the unique CCID's except for
366 EXPENSE, RESERVE and REVAL RESERVE CCID's. The total debit and credit
367 amounts for each CCID retrieved is stored in an array and each CCID
368 will have 1 entry in the array and running total is maintained. The
369 sum from FA_ADJUSTMENTS will give the entered balances and the sum
370 from FA_MC_ADJUSTMENTS will give the accounted balances.
371 ************************************************************************ */
372
373 CURSOR adj_bal IS
374 SELECT nvl(sum(maj.adjustment_amount),0),
375 nvl(sum(aj.adjustment_amount),0),
376 aj.code_combination_id,
377 aj.debit_credit_flag
378 FROM
379 fa_mc_adjustments maj,
380 fa_adjustments aj,
381 fa_mc_conversion_rates cr
382 WHERE
383 cr.set_of_books_id = p_rsob_id AND
384 maj.set_of_books_id = cr.set_of_books_id AND
385 cr.book_type_code = p_book_type_code AND
386 maj.book_type_code = cr.book_type_code AND
387 aj.asset_id = cr.asset_id AND
388 aj.code_combination_id = maj.code_combination_id AND
389 aj.debit_credit_flag = maj.debit_credit_flag AND
390 aj.distribution_id = maj.distribution_id AND
391 aj.asset_id = maj.asset_id AND
392 aj.book_type_code = cr.book_type_code AND
393 aj.adjustment_type = maj.adjustment_type AND
394 aj.adjustment_type not in ('RESERVE',
395 'EXPENSE',
396 'REVAL RESERVE')
397 GROUP BY
398 aj.code_combination_id,
399 aj.debit_credit_flag;
400
401 l_balance NUMBER;
402 l_ccid NUMBER;
403 l_dr_cr_flag VARCHAR2(2);
404 l_rbalance NUMBER;
405 l_fbalance NUMBER;
406
407 l_found BOOLEAN;
408 l_index NUMBER;
409 BEGIN
410 if (g_print_debug) then
411 fa_debug_pkg.add('calculate_balances',
412 'Getting Balances from FA_ADJUSTMENTS',
413 'start');
414 end if;
415
416 OPEN adj_bal;
417 LOOP
418 FETCH adj_bal into
419 l_rbalance,
420 l_fbalance,
421 l_ccid,
422 l_dr_cr_flag;
423
424 if (adj_bal%NOTFOUND) then
425 exit;
426 end if;
427
428 /*
429 dbms_output.put_line('l_rbalance: ' || l_rbalance);
430 dbms_output.put_line('l_fbalance ' || l_fbalance);
431 dbms_output.put_line('l_ccid: ' || l_ccid);
432 dbms_output.put_line('l_dr_cr_flag: ' || l_dr_cr_flag);
433 */
434 find_ccid(
435 l_ccid,
436 'ADJUSTMENTS',
437 l_found,
438 l_index);
439
440 IF (l_found) THEN
441 IF (l_dr_cr_flag = 'DR') THEN
442 adjustments_ccids(l_index).entered_dr :=
443 adjustments_ccids(l_index).entered_dr + l_fbalance;
444 adjustments_ccids(l_index).accounted_dr :=
445 adjustments_ccids(l_index).accounted_dr + l_rbalance;
446 ELSE
447 adjustments_ccids(l_index).entered_cr :=
448 adjustments_ccids(l_index).entered_cr + l_fbalance;
449 adjustments_ccids(l_index).accounted_cr :=
450 adjustments_ccids(l_index).accounted_cr + l_rbalance;
451 END IF;
452 ELSE
453 G_adj_ccid_count := G_adj_ccid_count + 1;
454
455 adjustments_ccids(G_adj_ccid_count).entered_dr := 0;
456 adjustments_ccids(G_adj_ccid_count).accounted_dr := 0;
457 adjustments_ccids(G_adj_ccid_count).entered_cr := 0;
458 adjustments_ccids(G_adj_ccid_count).accounted_cr := 0;
459
460 IF (l_dr_cr_flag = 'DR') THEN
461 adjustments_ccids(G_adj_ccid_count).entered_dr :=
462 adjustments_ccids(G_adj_ccid_count).entered_dr +
463 l_fbalance;
464 adjustments_ccids(G_adj_ccid_count).accounted_dr :=
465 adjustments_ccids(G_adj_ccid_count).accounted_dr +
466 l_rbalance;
467 ELSE
468 adjustments_ccids(G_adj_ccid_count).entered_cr :=
469 adjustments_ccids(G_adj_ccid_count).entered_cr +
470 l_fbalance;
471 adjustments_ccids(G_adj_ccid_count).accounted_cr :=
472 adjustments_ccids(G_adj_ccid_count).accounted_cr +
473 l_rbalance;
474 END IF;
475 adjustments_ccids(G_adj_ccid_count).ccid := l_ccid;
476 END IF;
477
478 END LOOP;
479 CLOSE adj_bal;
480
481 if (g_print_debug) then
482 fa_debug_pkg.add('calculate_balances',
483 'Getting Balances from FA_ADJUSTMENTS',
484 'success');
485 end if;
486
487 /* Debug Statements to print contents of adjustments ccid array
488 ****************************************************************************
489 dbms_output.put_line('Printing Contents of Adjustments Array:');
490 dbms_output.put_line('Total Number of array entries: ' || G_adj_ccid_count);
491 dbms_output.put_line('*******************************************');
492
493 for i in 1 .. G_adj_ccid_count LOOP
494
495 dbms_output.put_line('CCID and amount: ' ||
496 adjustments_ccids(i).ccid || ',' || adjustments_ccids(i).entered_dr);
497 dbms_output.put_line('CCID and amount: ' ||
498 adjustments_ccids(i).ccid || ',' || adjustments_ccids(i).accounted_dr);
499 dbms_output.put_line('CCID and amount: ' ||
500 adjustments_ccids(i).ccid || ',' || adjustments_ccids(i).entered_cr);
501 dbms_output.put_line('CCID and amount: ' ||
502 adjustments_ccids(i).ccid || ',' || adjustments_ccids(i).accounted_cr);
503
504 dbms_output.put_line('-----------------------------------------------');
505 end LOOP;
506 **************************************************************************** */
507
508 EXCEPTION
509 WHEN OTHERS THEN
510 fa_srvr_msg.add_sql_error (
511 calling_fn => 'fa_mc_upg3_pkg.get_adj_balances');
512 app_exception.raise_exception;
513
514 END get_adj_balances;
515
516
517 PROCEDURE get_deprn_reserve_balances(
518 p_rsob_id IN NUMBER,
519 p_book_type_code IN VARCHAR2) IS
520 /* ************************************************************************
521 This procedure gets the DEPRN_RESERVE balances for all reserve CCIDS.
522 For all the reserve ccid's of assets that depreciated in the fiscal
523 year converted, the ccid is obtained from gl_je_lines. For assets
524 with their last DEPRN row in a prior year, we look at gl_je_lines
525 first and if not found the reserve CCID will be flexbuilt. The
526 reserve balances are stored in a global array.
527 ************************************************************************ */
528
529 l_line_num NUMBER;
530 l_header_id NUMBER;
531 l_dist_ccid NUMBER;
532 l_dist_id NUMBER;
533 l_pc NUMBER;
534 l_rsv_account VARCHAR2(25);
535 l_account_ccid NUMBER;
536 l_calculated_ccid NUMBER;
537 l_ccid NUMBER;
538 l_total_func_rsv NUMBER;
539 l_total_rep_rsv NUMBER;
540
541 l_found BOOLEAN;
542 l_index NUMBER;
543
544 invalid_ccid EXCEPTION;
545 reserve_error EXCEPTION;
546
547
548 CURSOR get_periods IS
549 SELECT distinct last_period_counter
550 FROM fa_mc_conversion_rates
551 WHERE
552 set_of_books_id = p_rsob_id AND
553 book_type_code = p_book_type_code;
554
555 CURSOR reserve_line_num IS
556 SELECT distinct deprn_reserve_je_line_num, je_header_id
557 FROM fa_mc_deprn_detail
558 where period_counter = l_pc AND
559 book_type_code = p_book_type_code AND
560 set_of_books_id = p_rsob_id AND
561 deprn_reserve_je_line_num is not null AND
562 je_header_id is not null;
563
564 CURSOR get_ccid IS
565 select
566 gjl.code_combination_id
567 from
568 gl_je_lines gjl
569 where
570 gjl.je_header_id = l_header_id AND
571 gjl.je_line_num = l_line_num;
572
573 CURSOR ccid_total IS
574 SELECT
575 sum(mdd.deprn_reserve),
576 sum(dd.deprn_reserve)
577 FROM
578 fa_deprn_detail dd,
579 fa_mc_deprn_detail mdd,
580 fa_mc_conversion_rates cr
581 WHERE
582 cr.last_period_counter = l_pc AND
583 cr.set_of_books_id = p_rsob_id AND
584 cr.book_type_code = p_book_type_code AND
585 mdd.period_counter = cr.last_period_counter AND
586 mdd.set_of_books_id = cr.set_of_books_id AND
587 mdd.book_type_code = cr.book_type_code AND
588 mdd.deprn_reserve_je_line_num = l_line_num AND
589 dd.deprn_reserve_je_line_num =
590 mdd.deprn_reserve_je_line_num AND
591 dd.period_counter = mdd.period_counter AND
592 dd.book_type_code = mdd.book_type_code AND
593 mdd.asset_id = cr.asset_id AND
594 dd.asset_id = mdd.asset_id AND
595 dd.distribution_id = mdd.distribution_id;
596
597 BEGIN
598 if (g_print_debug) then
599 fa_debug_pkg.add('calculate_balances',
600 'Getting deprn reserve from balances',
601 'start');
602 end if;
603
604 OPEN get_periods;
605 LOOP
606 FETCH get_periods into l_pc;
607 if (get_periods%NOTFOUND) then
608 exit;
609 end if;
610 -- dbms_output.put_line('Processing period counter: ' || l_pc);
611 OPEN reserve_line_num;
612 LOOP
613 FETCH reserve_line_num into l_line_num, l_header_id;
614 if (reserve_line_num%NOTFOUND) then
615 exit;
616 end if;
617
618 -- dbms_output.put_line('Processing je line num: ' || l_line_num);
619 -- dbms_output.put_line('Processing je header id: ' || l_header_id);
620
621 OPEN get_ccid;
622 FETCH get_ccid into l_ccid;
623
624 -- dbms_output.put_line('Fetched ccid: ' || l_ccid);
625
626 IF (get_ccid%NOTFOUND) THEN
627
628 -- dbms_output.put_line('Could not find ccid in gl_je_lines');
629 -- dbms_output.put_line('Calling FAFBGCC');
630
631 -- could not find the ccid in gl_je_lines
632 -- need to flexbuild the reserve account
633 -- need one distribution_id to build the account
634 -- even if there are several assets with different
635 -- distribution_id's, the fact that they have the same
636 -- reserve line num means they went to the same account
637 -- as a result we can minimize flexbuild as much as
638 -- as possible
639
640 SELECT
641 dd.distribution_id,
642 dh.code_combination_id,
643 cb.deprn_reserve_acct,
644 cb.reserve_account_ccid
645 INTO
646 l_dist_id,
647 l_dist_ccid,
648 l_rsv_account,
649 l_account_ccid
650 FROM
651 fa_deprn_detail dd,
652 fa_category_books cb,
653 fa_asset_history ah,
654 fa_distribution_history dh
655 WHERE
656 dd.book_type_code = p_book_type_code AND
657 dd.period_counter = l_pc AND
658 dd.deprn_reserve_je_line_num = l_line_num AND
659 dd.distribution_id = dh.distribution_id AND
660 dd.asset_id = ah.asset_id AND
661 ah.category_id = cb.category_id AND
662 ah.date_ineffective is null AND
663 cb.book_type_code = dd.book_type_code AND
664 rownum = 1;
665
666 IF (not FA_GCCID_PKG.fafbgcc(
667 X_book_type_code=>p_book_type_code,
668 X_fn_trx_code=>'DEPRN_RESERVE_ACCT',
669 X_dist_ccid=>l_dist_ccid,
670 X_acct_segval=>l_rsv_account,
671 X_account_ccid=>l_account_ccid,
672 X_distribution_id=>l_dist_id,
673 X_rtn_ccid=>l_ccid)) THEN
674 RAISE invalid_ccid;
675 END IF;
676 -- dbms_output.put_line('After FAFBGCC ccid is: ' || to_char(l_ccid));
677 END IF;
678 CLOSE get_ccid;
679
680 OPEN ccid_total;
681 FETCH ccid_total into
682 l_total_rep_rsv,
683 l_total_func_rsv;
684
685 -- dbms_output.put_line('Total rep_rsv: ' || l_total_rep_rsv);
686 -- dbms_output.put_line('Total func_rsv: '|| l_total_func_rsv);
687
688 IF (ccid_total%NOTFOUND) THEN
689 RAISE reserve_error;
690 END IF;
691 CLOSE ccid_total;
692
693 find_ccid(
694 l_ccid,
695 'RESERVE',
696 l_found,
697 l_index);
698
699 IF (l_found) THEN
700 deprn_reserve_ccids(l_index).entered_cr :=
701 deprn_reserve_ccids(l_index).entered_cr +
702 l_total_func_rsv;
703 deprn_reserve_ccids(l_index).accounted_cr :=
704 deprn_reserve_ccids(l_index).accounted_cr +
705 l_total_rep_rsv;
706 ELSE
707
708 G_drsv_ccid_count := G_drsv_ccid_count + 1;
709
710 deprn_reserve_ccids(G_drsv_ccid_count).entered_dr := 0;
711 deprn_reserve_ccids(G_drsv_ccid_count).accounted_dr := 0;
712 deprn_reserve_ccids(G_drsv_ccid_count).entered_cr := 0;
713 deprn_reserve_ccids(G_drsv_ccid_count).accounted_cr := 0;
714
715 deprn_reserve_ccids(G_drsv_ccid_count).entered_cr :=
716 deprn_reserve_ccids(G_drsv_ccid_count).entered_cr
717 + l_total_func_rsv;
718 deprn_reserve_ccids(G_drsv_ccid_count).accounted_cr :=
719 deprn_reserve_ccids(G_drsv_ccid_count).accounted_cr
720 + l_total_rep_rsv;
721 deprn_reserve_ccids(G_drsv_ccid_count).ccid := l_ccid;
722
723 END IF;
724 end LOOP;
725 CLOSE reserve_line_num;
726 end LOOP;
727 CLOSE get_periods;
728
729 if (g_print_debug) then
730 fa_debug_pkg.add('calculate_balances',
731 'Getting deprn reserve balances',
732 'success');
733 end if;
734
735 /* Debug Statements to print contents of deprn reeserve ccid array
736 ****************************************************************************
737 dbms_output.put_line('Printing Contents of Deprn Reserve Array:');
738 dbms_output.put_line('Total Number of array entries: ' || G_drsv_ccid_count);
739 dbms_output.put_line('*******************************************');
740
741 for i in 1 .. G_drsv_ccid_count LOOP
742
743 dbms_output.put_line('CCID and amount: ' ||
744 deprn_reserve_ccids(i).ccid || ',' || deprn_reserve_ccids(i).entered_dr);
745 dbms_output.put_line('CCID and amount: ' ||
746 deprn_reserve_ccids(i).ccid || ',' || deprn_reserve_ccids(i).accounted_dr);
747 dbms_output.put_line('CCID and amount: ' ||
748 deprn_reserve_ccids(i).ccid || ',' || deprn_reserve_ccids(i).entered_cr);
749 dbms_output.put_line('CCID and amount: ' ||
750 deprn_reserve_ccids(i).ccid || ',' || deprn_reserve_ccids(i).accounted_cr);
751
752 dbms_output.put_line('-----------------------------------------------');
753 end LOOP;
754 **************************************************************************** */
755
756 EXCEPTION
757 WHEN invalid_ccid THEN
758 if (g_print_debug) then
759 fa_debug_pkg.add('get_deprn_reserve_balances',
760 'Error Getting reserve ccid ',
761 'failure');
762 end if;
763
764 fa_srvr_msg.add_sql_error (
765 calling_fn => 'fa_mc_upg3_pkg.get_deprn_reserve_balances');
766 app_exception.raise_exception;
767
768 WHEN reserve_error THEN
769 if (g_print_debug) then
770 fa_debug_pkg.add('get_deprn_reserve_balances',
771 'Error Getting reserve for period ',
772 l_pc);
773 fa_debug_pkg.add('get_deprn_reserve_balances',
774 'Error Getting reserve for line num ',
775 l_line_num);
776 end if;
777 fa_srvr_msg.add_sql_error (
778 calling_fn => 'fa_mc_upg3_pkg.get_deprn_reserve_balances');
779 app_exception.raise_exception;
780
781 WHEN OTHERS THEN
782 fa_srvr_msg.add_sql_error (
783 calling_fn => 'fa_mc_upg3_pkg.get_deprn_reserve_balances');
784 app_exception.raise_exception;
785
786 END get_deprn_reserve_balances;
787
788
789 PROCEDURE get_deprn_exp_balances(
790 p_rsob_id IN NUMBER,
791 p_book_type_code IN VARCHAR2) IS
792 /* ************************************************************************
793 This procedure gets the DEPRN_AMOUNT balances for all expense CCIDS.
794 For all the expense ccid's of assets that depreciated in the fiscal
795 year converted, the ccid is obtained from gl_je_lines. Depreciation
796 Expense only needs to be calculated for the periods in the fiscal year
797 converted since prior years expense will go into retained earnings
798 ************************************************************************ */
799
800 l_total_func_exp NUMBER;
801 l_total_rep_exp NUMBER;
802 l_pc NUMBER;
803 l_line_num NUMBER;
804 l_header_id NUMBER;
805 l_ccid NUMBER;
806 l_found BOOLEAN;
807 l_index NUMBER;
808
809 CURSOR exp_line_num IS
810 SELECT distinct deprn_expense_je_line_num, je_header_id
811 FROM fa_mc_deprn_detail
812 where period_counter = l_pc AND
813 book_type_code = p_book_type_code AND
814 set_of_books_id = p_rsob_id and
815 deprn_expense_je_line_num is not null and
816 je_header_id is not null;
817
818 CURSOR get_ccid IS
819 SELECT
820 gjl.code_combination_id
821 FROM
822 gl_je_lines gjl
823 WHERE
824 gjl.je_header_id = l_header_id AND
825 gjl.je_line_num = l_line_num;
826
827 CURSOR ccid_total IS
828 SELECT
829 nvl(sum(dd.deprn_amount),0),
830 nvl(sum(mdd.deprn_amount),0)
831 FROM
832 fa_deprn_detail dd,
833 fa_mc_deprn_detail mdd
834 WHERE
835 mdd.period_counter = l_pc AND
836 mdd.set_of_books_id = p_rsob_id AND
837 mdd.book_type_code = p_book_type_code AND
838 mdd.deprn_expense_je_line_num = l_line_num AND
839 dd.period_counter = mdd.period_counter AND
840 dd.book_type_code = mdd.book_type_code AND
841 dd.deprn_expense_je_line_num =
842 mdd.deprn_expense_je_line_num AND
843 mdd.asset_id = dd.asset_id AND
844 mdd.distribution_id = dd.distribution_id;
845
846 BEGIN
847 if (g_print_debug) then
848 fa_debug_pkg.add('calculate_balances',
849 'Getting deprn expense balances',
850 'start');
851 end if;
852
853 FOR i IN G_start_pc .. G_end_pc LOOP
854 l_pc := i;
855 -- dbms_output.put_line('Processing period counter: ' || l_pc);
856 OPEN exp_line_num;
857 LOOP
858 FETCH exp_line_num into l_line_num, l_header_id;
859 IF (exp_line_num%NOTFOUND) then
860 exit;
861 END IF;
862 -- dbms_output.put_line('Processing je line num: ' || l_line_num);
863 -- dbms_output.put_line('Processing je header id: ' || l_header_id);
864 OPEN get_ccid;
865 FETCH get_ccid into l_ccid;
866 CLOSE get_ccid;
867 -- dbms_output.put_line('Fetched ccid: ' || l_ccid);
868 OPEN ccid_total;
869 FETCH ccid_total into
870 l_total_func_exp,
871 l_total_rep_exp;
872 -- dbms_output.put_line('Total rep_exp: ' || l_total_rep_exp);
873 -- dbms_output.put_line('Total func_exp: '|| l_total_func_exp);
874
875 CLOSE ccid_total;
876 find_ccid(
877 l_ccid,
878 'EXPENSE',
879 l_found,
880 l_index);
881
882 IF (l_found) THEN
883 -- dbms_output.put_line('CCID found in array: ' || 'TRUE');
884 deprn_expense_ccids(l_index).entered_dr :=
885 deprn_expense_ccids(l_index).entered_dr +
886 l_total_func_exp;
887 deprn_expense_ccids(l_index).accounted_dr :=
888 deprn_expense_ccids(l_index).accounted_dr +
889 l_total_rep_exp;
890 ELSE
891 -- dbms_output.put_line('CCID found in array: ' || 'FALSE');
892 G_dexp_ccid_count := G_dexp_ccid_count + 1;
893
894 deprn_expense_ccids(G_dexp_ccid_count).entered_dr := 0;
895 deprn_expense_ccids(G_dexp_ccid_count).accounted_dr := 0;
896 deprn_expense_ccids(G_dexp_ccid_count).entered_cr := 0;
897 deprn_expense_ccids(G_dexp_ccid_count).accounted_cr := 0;
898
899 deprn_expense_ccids(G_dexp_ccid_count).entered_dr :=
900 deprn_expense_ccids(G_dexp_ccid_count).entered_dr +
901 l_total_func_exp;
902 deprn_expense_ccids(G_dexp_ccid_count).accounted_dr :=
903 deprn_expense_ccids(G_dexp_ccid_count).accounted_dr +
904 l_total_rep_exp;
905 deprn_expense_ccids(G_dexp_ccid_count).ccid := l_ccid;
906 END IF;
907 END LOOP;
908 CLOSE exp_line_num;
909 END LOOP;
910
911 if (g_print_debug) then
912 fa_debug_pkg.add('calculate_balances',
913 'Getting deprn expense balances',
914 'success');
915 end if;
916
917 /* Debug Statements to print contents of deprn expense ccid array
918 *****************************************************************************
919 dbms_output.put_line('Printing Contents of Deprn Expense Array:');
920 dbms_output.put_line('Total Number of array entries: ' || G_dexp_ccid_count);
921 dbms_output.put_line('*******************************************');
922
923 for i in 1 .. G_dexp_ccid_count LOOP
924
925 dbms_output.put_line('CCID and amount: ' ||
926 deprn_expense_ccids(i).ccid || ',' || deprn_expense_ccids(i).entered_dr);
927 dbms_output.put_line('CCID and amount: ' ||
928 deprn_expense_ccids(i).ccid || ',' || deprn_expense_ccids(i).accounted_dr);
929 dbms_output.put_line('CCID and amount: ' ||
930 deprn_expense_ccids(i).ccid || ',' || deprn_expense_ccids(i).entered_cr);
931 dbms_output.put_line('CCID and amount: ' ||
932 deprn_expense_ccids(i).ccid || ',' || deprn_expense_ccids(i).accounted_cr);
933 dbms_output.put_line('-----------------------------------------------');
934 end LOOP;
935 **************************************************************************** */
936
937 EXCEPTION
938 WHEN OTHERS THEN
939 fa_srvr_msg.add_sql_error (
940 calling_fn => 'fa_mc_upg3_pkg.get_deprn_exp_balances');
941 app_exception.raise_exception;
942
943 END get_deprn_exp_balances;
944
945
946 PROCEDURE get_reval_rsv_balances(
947 p_rsob_id IN NUMBER,
948 p_book_type_code IN VARCHAR2) IS
949 /* ************************************************************************
950 This procedure gets the REVAL_RESERVE balances.
951 For all the reval reserve ccid's of assets that depreciated in the fiscal
952 year converted, the ccid is obtained from FA_ADJUSTMENTS. When a
953 revaluation is perfomed REVAL RESERVE rows are created in FA_ADJUSTMENTS
954 for each active distribution. If REVAL RESERVE is transferred, or retired
955 there will still be a row in FA_ADJUSTMENTS for the new distribution
956 ************************************************************************ */
957
958 l_balance NUMBER;
959 l_ccid NUMBER;
960 l_dr_cr_flag VARCHAR2(2);
961 l_rbalance NUMBER;
962 l_fbalance NUMBER;
963 l_found BOOLEAN;
964 l_index NUMBER;
965
966 CURSOR reval_rsv_bal IS
967 SELECT
968 aj.code_combination_id,
969 nvl(sum(dd.reval_reserve),0),
970 nvl(sum(mdd.reval_reserve),0)
971 FROM
972 fa_adjustments aj,
973 fa_deprn_detail dd,
974 fa_mc_deprn_detail mdd,
975 fa_mc_conversion_rates cr
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 = dd.asset_id AND
980 cr.last_period_counter = dd.period_counter AND
981 dd.book_type_code = p_book_type_code AND
982 dd.asset_id = aj.asset_id AND
983 dd.book_type_code = aj.book_type_code AND
984 dd.distribution_id = aj.distribution_id AND
985 mdd.period_counter = dd.period_counter AND
986 mdd.book_type_code = dd.book_type_code AND
987 mdd.asset_id = dd.asset_id AND
988 mdd.distribution_id = dd.distribution_id AND
989 mdd.set_of_books_id = p_rsob_id AND
990 aj.adjustment_type = 'REVAL RESERVE'
991 GROUP BY
992 aj.code_combination_id;
993
994 BEGIN
995 if (g_print_debug) then
996 fa_debug_pkg.add('calculate_balances',
997 'Getting reval reserve balances',
998 'start');
999 end if;
1000
1001 OPEN reval_rsv_bal;
1002 LOOP
1003 FETCH reval_rsv_bal into
1004 l_ccid,
1005 l_rbalance,
1006 l_fbalance;
1007
1008 IF (reval_rsv_bal%NOTFOUND) THEN
1009 EXIT;
1010 END IF;
1011 -- dbms_output.put_line('Reval rsv ccid is: ' || to_char(l_ccid));
1012 -- dbms_output.put_line('Reval rsv l_fbalance: ' || to_char(l_fbalance));
1013 -- dbms_output.put_line('Reval rsv l_rbalance: ' || to_char(l_rbalance));
1014
1015
1016 find_ccid(
1017 l_ccid,
1018 'REVAL RESERVE',
1019 l_found,
1020 l_index);
1021
1022 IF (l_found) THEN
1023 reval_reserve_ccids(l_index).entered_cr :=
1024 reval_reserve_ccids(l_index).entered_cr +
1025 l_fbalance;
1026 reval_reserve_ccids(l_index).accounted_cr :=
1027 reval_reserve_ccids(l_index).accounted_cr +
1028 l_rbalance;
1029 ELSE
1030 G_rrsv_ccid_count := G_rrsv_ccid_count + 1;
1031 reval_reserve_ccids(G_rrsv_ccid_count).entered_dr := 0;
1032 reval_reserve_ccids(G_rrsv_ccid_count).accounted_dr := 0;
1033 reval_reserve_ccids(G_rrsv_ccid_count).entered_cr := 0;
1034 reval_reserve_ccids(G_rrsv_ccid_count).accounted_cr := 0;
1035
1036 reval_reserve_ccids(G_rrsv_ccid_count).entered_cr :=
1037 reval_reserve_ccids(G_rrsv_ccid_count).entered_cr
1038 + l_fbalance;
1039 reval_reserve_ccids(G_rrsv_ccid_count).accounted_cr :=
1040 reval_reserve_ccids(G_rrsv_ccid_count).accounted_cr
1041 + l_rbalance;
1042 reval_reserve_ccids(G_rrsv_ccid_count).ccid := l_ccid;
1043
1044 END IF;
1045 END LOOP;
1046 CLOSE reval_rsv_bal;
1047
1048 if (g_print_debug) then
1049 fa_debug_pkg.add('calculate_balances',
1050 'Getting reval reserve balances',
1051 'success');
1052 end if;
1053
1054 EXCEPTION
1055 WHEN OTHERS THEN
1056 fa_srvr_msg.add_sql_error (
1057 calling_fn => 'fa_mc_upg3_pkg.get_reval_rsv_balances');
1058 app_exception.raise_exception;
1059
1060 END get_reval_rsv_balances;
1061
1062
1063 PROCEDURE get_reval_amort_balances(
1064 p_rsob_id IN NUMBER,
1065 p_book_type_code IN VARCHAR2) IS
1066 /* ************************************************************************
1067 This procedure gets the REVAL AMORTIZATION balances.
1068 For all the reval_amort ccid's of assets that depreciated in the fiscal
1069 year converted, the ccid is obtained from gl_je_lines. Reval Amortization
1070 only needs to be calculated for the periods in the fiscal year.
1071 ************************************************************************ */
1072
1073 l_pc NUMBER;
1074 l_line_num NUMBER;
1075 l_ccid NUMBER;
1076 l_fbalance NUMBER;
1077 l_rbalance NUMBER;
1078 l_found BOOLEAN;
1079 l_index NUMBER;
1080
1081
1082 CURSOR amort_line_num IS
1083 SELECT distinct reval_amort_je_line_num
1084 FROM fa_mc_deprn_detail
1085 where period_counter = l_pc AND
1086 book_type_code = p_book_type_code AND
1087 set_of_books_id = p_rsob_id AND
1088 reval_amort_je_line_num is not null;
1089
1090 CURSOR get_ccid IS
1091 SELECT
1092 gs.code_combination_id
1093 FROM
1094 gl_je_lines gs,
1095 gl_je_headers gh,
1096 gl_je_batches gb,
1097 fa_deprn_periods dp
1098 WHERE
1099 dp.period_counter = l_pc AND
1100 dp.book_type_code = p_book_type_code AND
1101 dp.reval_batch_id = gb.je_batch_id AND
1102 gh.je_batch_id = gb.je_batch_id AND
1103 gs.je_header_id = gh.je_header_id AND
1104 gs.je_line_num = l_line_num;
1105
1106 CURSOR ccid_total IS
1107 SELECT
1108 nvl(sum(dd.reval_amortization),0),
1109 nvl(sum(mdd.reval_amortization),0)
1110 FROM
1111 fa_mc_deprn_detail mdd,
1112 fa_deprn_detail dd
1113 WHERE
1114 mdd.book_type_code = p_book_type_code AND
1115 mdd.period_counter = l_pc AND
1116 mdd.set_of_books_id = p_rsob_id AND
1117 mdd.reval_amort_je_line_num = l_line_num AND
1118 dd.book_type_code = mdd.book_type_code AND
1119 dd.period_counter = mdd.period_counter AND
1120 dd.reval_amort_je_line_num =
1121 mdd.reval_amort_je_line_num AND
1122 mdd.asset_id = dd.asset_id AND
1123 mdd.distribution_id = dd.distribution_id;
1124
1125 BEGIN
1126 if (g_print_debug) then
1127 fa_debug_pkg.add('calculate_balances',
1128 'Getting reval amort balances',
1129 'start');
1130 end if;
1131
1132 FOR l_pc IN G_start_pc .. G_end_pc LOOP
1133 OPEN amort_line_num;
1134 LOOP
1135 FETCH amort_line_num into l_line_num;
1136 IF (amort_line_num%NOTFOUND) THEN
1137 EXIT;
1138 END IF;
1139 OPEN get_ccid;
1140 FETCH get_ccid into l_ccid;
1141 CLOSE get_ccid;
1142 OPEN ccid_total;
1143 FETCH ccid_total into
1144 l_fbalance,
1145 l_rbalance;
1146 CLOSE ccid_total;
1147 find_ccid(
1148 l_ccid,
1149 'REVAL AMORT',
1150 l_found,
1151 l_index);
1152
1153 IF (l_found) THEN
1154 reval_amort_ccids(l_index).entered_cr :=
1155 reval_amort_ccids(l_index).entered_cr +
1156 l_fbalance;
1157 reval_amort_ccids(l_index).accounted_cr :=
1158 reval_amort_ccids(l_index).accounted_cr +
1159 l_rbalance;
1160 ELSE
1161 G_ramo_ccid_count := G_ramo_ccid_count + 1;
1162
1163 reval_amort_ccids(G_ramo_ccid_count).entered_dr := 0;
1164 reval_amort_ccids(G_ramo_ccid_count).accounted_dr := 0;
1165 reval_amort_ccids(G_ramo_ccid_count).entered_cr := 0;
1166 reval_amort_ccids(G_ramo_ccid_count).accounted_cr := 0;
1167
1168 reval_amort_ccids(G_ramo_ccid_count).entered_cr :=
1169 reval_amort_ccids(G_ramo_ccid_count).entered_cr
1170 + l_fbalance;
1171 reval_amort_ccids(G_ramo_ccid_count).accounted_cr :=
1172 reval_amort_ccids(G_ramo_ccid_count).accounted_cr
1173 + l_rbalance;
1174 reval_amort_ccids(G_ramo_ccid_count).ccid := l_ccid;
1175 END IF;
1176 END LOOP;
1177 CLOSE amort_line_num;
1178 END LOOP;
1179
1180 if (g_print_debug) then
1181 fa_debug_pkg.add('calculate_balances',
1182 'Getting reval amort balances',
1183 'success');
1184 end if;
1185
1186 EXCEPTION
1187 WHEN OTHERS THEN
1188 fa_srvr_msg.add_sql_error (
1189 calling_fn => 'fa_mc_upg3_pkg.get_reval_amort_balances');
1190 app_exception.raise_exception;
1191
1192 END get_reval_amort_balances;
1193
1194
1195
1196 PROCEDURE get_def_rsv_balances (
1197 p_rsob_id IN NUMBER,
1198 p_book_type_code IN VARCHAR2) IS
1199 /* ************************************************************************
1200 This procedure gets the deferred depreciation reserve balances.
1201 We will get the deferred reserve for all years since we only store
1202 period reserve balances for each row and to get the total reserve
1203 we have to past years as well. This is required since this is a
1204 balance sheet account
1205 ************************************************************************ */
1206
1207
1208 l_frsv NUMBER;
1209 l_rrsv NUMBER;
1210 l_ccid NUMBER;
1211
1212 CURSOR def_rsv_balances IS
1213 SELECT
1214 nvl(sum(dd.deferred_deprn_reserve_amount),0),
1215 nvl(sum(mdd.deferred_deprn_reserve_amount),0),
1216 dd.deferred_deprn_reserve_ccid
1217 FROM
1218 fa_deferred_deprn dd,
1219 fa_mc_deferred_deprn mdd,
1220 fa_mc_conversion_rates cr
1221 WHERE
1222 cr.set_of_books_id = p_rsob_id AND
1223 cr.book_type_code = p_book_type_code AND
1224 cr.asset_id = dd.asset_id AND
1225 dd.tax_book_type_code = cr.book_type_code AND
1226 mdd.tax_book_type_code = cr.book_type_code AND
1227 mdd.asset_id = dd.asset_id AND
1228 mdd.deferred_deprn_reserve_ccid =
1229 dd.deferred_deprn_reserve_ccid AND
1230 dd.distribution_id = mdd.distribution_id AND
1231 dd.tax_period_counter = mdd.tax_period_counter AND
1232 dd.corp_period_counter = mdd.corp_period_counter AND
1233 dd.je_header_id = mdd.je_header_id AND
1234 dd.reserve_je_line_num = mdd.reserve_je_line_num
1235 GROUP BY
1236 dd.deferred_deprn_reserve_ccid;
1237 BEGIN
1238 if (g_print_debug) then
1239 fa_debug_pkg.add('calculate_balances',
1240 'Getting deferred deprn reserve balances',
1241 'start');
1242 end if;
1243
1244 OPEN def_rsv_balances;
1245 LOOP
1246 FETCH def_rsv_balances into l_frsv,
1247 l_rrsv,
1248 l_ccid;
1249 IF (def_rsv_balances%NOTFOUND) THEN
1250 EXIT;
1251 END IF;
1252
1253 G_dfrsv_ccid_count := G_dfrsv_ccid_count + 1;
1254
1255 def_deprn_rsv_ccids(G_dfrsv_ccid_count).entered_cr := 0;
1256 def_deprn_rsv_ccids(G_dfrsv_ccid_count).entered_dr := 0;
1257 def_deprn_rsv_ccids(G_dfrsv_ccid_count).accounted_cr := 0;
1258 def_deprn_rsv_ccids(G_dfrsv_ccid_count).accounted_dr := 0;
1259
1260 def_deprn_rsv_ccids(G_dfrsv_ccid_count).entered_cr :=
1261 def_deprn_rsv_ccids(G_dfrsv_ccid_count).entered_cr +
1262 l_frsv;
1263 def_deprn_rsv_ccids(G_dfrsv_ccid_count).accounted_cr :=
1264 def_deprn_rsv_ccids(G_dfrsv_ccid_count).accounted_cr +
1265 l_rrsv;
1266 def_deprn_rsv_ccids(G_dfrsv_ccid_count).ccid := l_ccid;
1267 END LOOP;
1268 CLOSE def_rsv_balances;
1269
1270 if (g_print_debug) then
1271 fa_debug_pkg.add('calculate_balances',
1272 'Getting deferred deprn reserve balances',
1273 'success');
1274 end if;
1275
1276 EXCEPTION
1277 WHEN OTHERS THEN
1278 fa_srvr_msg.add_sql_error (
1279 calling_fn => 'fa_mc_upg3_pkg.get_def_rsv_balances');
1280 app_exception.raise_exception;
1281 END get_def_rsv_balances;
1282
1283
1284
1285 PROCEDURE get_def_exp_balances (
1286 p_rsob_id IN NUMBER,
1287 p_book_type_code IN VARCHAR2) IS
1288 /* ************************************************************************
1289 This procedure gets the deferred depreciation expense balances.
1290 We will get the deferred expense only for the fiscal year converted
1291 since deferred deprn expense is a expense account and past year balances
1292 will go to retained earnings.
1293 ************************************************************************ */
1294
1295
1296 l_fexp NUMBER;
1297 l_rexp NUMBER;
1298 l_ccid NUMBER;
1299
1300 CURSOR def_exp_balances IS
1301 SELECT
1302 nvl(sum(dd.deferred_deprn_expense_amount),0),
1303 nvl(sum(mdd.deferred_deprn_expense_amount),0),
1304 dd.deferred_deprn_expense_ccid
1305 FROM
1306 fa_deferred_deprn dd,
1307 fa_mc_deferred_deprn mdd,
1308 fa_mc_conversion_rates cr
1309 WHERE
1310 cr.set_of_books_id = p_rsob_id AND
1311 cr.book_type_code = p_book_type_code AND
1312 cr.asset_id = dd.asset_id AND
1313 dd.tax_book_type_code = cr.book_type_code AND
1314 mdd.tax_book_type_code = cr.book_type_code AND
1315 mdd.asset_id = dd.asset_id AND
1316 mdd.deferred_deprn_expense_ccid =
1317 dd.deferred_deprn_expense_ccid AND
1318 dd.distribution_id = mdd.distribution_id AND
1319 dd.tax_period_counter = mdd.tax_period_counter AND
1320 dd.corp_period_counter = mdd.corp_period_counter AND
1321 dd.je_header_id = mdd.je_header_id AND
1322 dd.expense_je_line_num = mdd.expense_je_line_num AND
1323 dd.tax_period_counter between G_start_pc and
1324 G_end_pc
1325 GROUP BY
1326 dd.deferred_deprn_expense_ccid;
1327 BEGIN
1328 if (g_print_debug) then
1329 fa_debug_pkg.add('calculate_balances',
1330 'Getting deferred deprn exp balances',
1331 'start');
1332 end if;
1333
1334 OPEN def_exp_balances;
1335 LOOP
1336 FETCH def_exp_balances into l_fexp,
1337 l_rexp,
1338 l_ccid;
1339 IF (def_exp_balances%NOTFOUND) THEN
1340 EXIT;
1341 END IF;
1342
1343 G_dfexp_ccid_count := G_dfexp_ccid_count + 1;
1344
1345 def_deprn_exp_ccids(G_dfexp_ccid_count).entered_cr := 0;
1346 def_deprn_exp_ccids(G_dfexp_ccid_count).entered_dr := 0;
1347 def_deprn_exp_ccids(G_dfexp_ccid_count).accounted_cr := 0;
1348 def_deprn_exp_ccids(G_dfexp_ccid_count).accounted_dr := 0;
1349
1350 def_deprn_exp_ccids(G_dfexp_ccid_count).entered_dr :=
1351 def_deprn_exp_ccids(G_dfexp_ccid_count).entered_dr +
1352 l_fexp;
1353 def_deprn_exp_ccids(G_dfexp_ccid_count).accounted_dr :=
1354 def_deprn_exp_ccids(G_dfexp_ccid_count).accounted_dr +
1355 l_rexp;
1356 def_deprn_exp_ccids(G_dfexp_ccid_count).ccid := l_ccid;
1357 END LOOP;
1358 CLOSE def_exp_balances;
1359
1360 if (g_print_debug) then
1361 fa_debug_pkg.add('calculate_balances',
1362 'Getting deferred deprn exp balances',
1363 'success');
1364 end if;
1365
1366 EXCEPTION
1367 WHEN OTHERS THEN
1368 fa_srvr_msg.add_sql_error (
1369 calling_fn => 'fa_mc_upg3_pkg.get_def_exp_balances');
1370 app_exception.raise_exception;
1371 END get_def_exp_balances;
1372
1373
1374
1375 PROCEDURE find_ccid(
1376 p_ccid IN NUMBER,
1377 p_adjustment_type IN VARCHAR2,
1378 X_found OUT NOCOPY BOOLEAN,
1379 X_index OUT NOCOPY NUMBER) IS
1380 /* ************************************************************************
1381 This procedure will search for a CCID within an array and returns
1382 the index at which the CCID was found and whether a CCID was found
1383 in the global array or not.
1384 ************************************************************************ */
1385
1386 tmp_ccids ccid_table; -- temp copy of global array
1387 tmp_count NUMBER; -- temp size of global array
1388 BEGIN
1389 X_found := FALSE;
1390 X_index := 0;
1391
1392 IF (p_adjustment_type = 'RESERVE') THEN
1393 tmp_ccids := deprn_reserve_ccids;
1394 tmp_count := G_drsv_ccid_count;
1395 ELSIF (p_adjustment_type = 'EXPENSE') THEN
1396 tmp_ccids := deprn_expense_ccids;
1397 tmp_count := G_dexp_ccid_count;
1398 ELSIF (p_adjustment_type = 'REVAL RESERVE') THEN
1399 tmp_ccids := reval_reserve_ccids;
1400 tmp_count := G_rrsv_ccid_count;
1401 ELSIF (p_adjustment_type = 'REVAL AMORT') THEN
1402 tmp_ccids := reval_amort_ccids;
1403 tmp_count := G_ramo_ccid_count;
1404 ELSIF (p_adjustment_type = 'ADJUSTMENTS') THEN
1405 tmp_ccids := adjustments_ccids;
1406 tmp_count := G_adj_ccid_count;
1407 END IF;
1408
1409 FOR i IN 1 .. tmp_count LOOP
1410 IF (tmp_ccids(i).ccid = p_ccid) THEN
1411 X_index := i;
1412 X_found := TRUE;
1413 END IF;
1414 END LOOP;
1415
1416 EXCEPTION
1417 WHEN OTHERS THEN
1418 fa_srvr_msg.add_sql_error (
1419 calling_fn => 'fa_mc_upg3_pkg.find_ccid');
1420 app_exception.raise_exception;
1421
1422 END find_ccid;
1423
1424 PROCEDURE get_coa_info(
1425 p_book_type_code IN VARCHAR2,
1426 p_rsob_id IN NUMBER) IS
1427 /* ************************************************************************
1428 This procedure will obtain the Chart of Accounts Info for the reporting
1429 book for which we are initializing the account balances. We get the
1430 balancing segment for the coa and all the active segments in a buffer
1431 which will be used to dynamically build the retained earnings account
1432 when creating the balancing entry to retained earnings in GL_INTERFACE
1433 ************************************************************************ */
1434
1435 l_bal_seg_num NUMBER;
1436 l_segcount NUMBER;
1437 l_seg_name VARCHAR2(30);
1438 l_appcol_name VARCHAR2(25);
1439 l_prompt VARCHAR2(80);
1440 l_value_set_name VARCHAR2(60);
1441
1442 coa_exception EXCEPTION;
1443 seginfo_exception EXCEPTION;
1444 BEGIN
1445 if (g_print_debug) then
1446 fa_debug_pkg.add('calculate_balances',
1447 'Getting chart of accounts info',
1448 'start');
1449 end if;
1450
1451 SELECT
1452 gls.chart_of_accounts_id,
1453 gls.ret_earn_code_combination_id
1454 INTO G_coa_id,
1455 G_re_ccid
1456 FROM
1457 gl_sets_of_books gls,
1458 fa_mc_book_controls bc
1459 WHERE
1460 bc.book_type_code = p_book_type_code AND
1461 bc.set_of_books_id = p_rsob_id AND
1462 gls.set_of_books_id = bc.primary_set_of_books_id;
1463
1464 -- Get the balancing segment NUMBER
1465 IF (NOT fnd_flex_apis.get_qualifier_segnum(
1466 appl_id => 101,
1467 key_flex_code => 'GL#',
1468 structure_NUMBER => G_coa_id,
1469 flex_qual_name => 'GL_BALANCING',
1470 segment_NUMBER => l_bal_seg_num)) THEN
1471 RAISE coa_exception;
1472 END IF;
1473
1474 -- Get the NUMBER of segments
1475 SELECT count(*)
1476 INTO l_segcount
1477 FROM fnd_id_flex_segments
1478 WHERE enabled_flag = 'Y'
1479 AND id_flex_num = G_coa_id
1480 AND application_id = 101
1481 AND id_flex_code = 'GL#';
1482
1483 G_flex_buf := '';
1484
1485 -- LOOP thro all segments and build segment buffer
1486 FOR segnum IN 1..l_segcount LOOP
1487 IF (NOT fnd_flex_apis.get_segment_info(
1488 x_application_id => 101,
1489 x_id_flex_code => 'GL#',
1490 x_id_flex_num => G_coa_id,
1491 x_seg_num => segnum,
1492 x_appcol_name => l_appcol_name,
1493 x_seg_name => l_seg_name,
1494 x_prompt => l_prompt,
1495 x_value_set_name => l_value_set_name)) THEN
1496 RAISE seginfo_exception;
1497 END IF;
1498
1499 G_flex_seg_count := G_flex_seg_count + 1;
1500 coa_structure(segnum) := l_appcol_name;
1501
1502 IF (segnum = l_bal_seg_num) THEN
1503 G_bal_seg_col := l_appcol_name;
1504 END IF;
1505 G_flex_buf := G_flex_buf || l_appcol_name || ', ';
1506 END LOOP;
1507
1508 if (g_print_debug) then
1509 fa_debug_pkg.add('calculate_balances',
1510 'Getting chart of accounts info',
1511 'success');
1512 end if;
1513
1514 -- dbms_output.put_line('The COA structure is: ' || G_flex_buf);
1515
1516 EXCEPTION
1517 WHEN coa_exception THEN
1518 fa_srvr_msg.add_sql_error (
1519 calling_fn => 'fa_mc_upg3_pkg.get_coa_info');
1520 app_exception.raise_exception;
1521
1522 WHEN seginfo_exception THEN
1523 fa_srvr_msg.add_message(
1524 calling_fn => 'fa_mc_upg3_pkg.get_coa_info',
1525 name => 'FA_SHARED_FLEX_SEGCOLUMNS',
1526 token1 => 'STRUCT_ID',
1527 value1 => to_char(G_coa_id),
1528 token2 => 'FLEX_CODE',
1529 value2 => 'GL#');
1530 app_exception.raise_exception;
1531
1532 WHEN OTHERS THEN
1533 fa_srvr_msg.add_sql_error (
1534 calling_fn => 'fa_mc_upg3_pkg.get_coa_info');
1535 app_exception.raise_exception;
1536
1537 END get_coa_info;
1538
1539
1540 PROCEDURE insert_rec(
1541 p_rsob_id IN NUMBER,
1542 p_entered_cr IN NUMBER,
1543 p_entered_dr IN NUMBER,
1544 p_accounted_cr IN NUMBER,
1545 p_accounted_dr IN NUMBER,
1546 p_ccid IN NUMBER) IS
1547 /* ************************************************************************
1548 This procedure inserts a row into gl_interface and obtains the segment
1549 values from gl_code_combination using the CCID. We get all the segment
1550 values for each CCID since it is required for the GROUP BY in
1551 insert_ret_earnings procedure. We insert the entered and accounted
1552 amounts for each CCID.
1553 ************************************************************************ */
1554
1555 BEGIN
1556 INSERT INTO gl_interface (
1557 set_of_books_id,
1558 status,
1559 code_combination_id,
1560 user_je_source_name,
1561 user_je_category_name,
1562 date_created,
1563 accounting_date,
1564 entered_cr,
1565 entered_dr,
1566 accounted_cr,
1567 accounted_dr,
1568 currency_code,
1569 actual_flag,
1570 created_by,
1571 group_id,
1572 segment1,
1573 segment2,
1574 segment3,
1575 segment4,
1576 segment5,
1577 segment6,
1578 segment7,
1579 segment8,
1580 segment9,
1581 segment10,
1582 segment11,
1583 segment12,
1584 segment13,
1585 segment14,
1586 segment15,
1587 segment16,
1588 segment17,
1589 segment18,
1590 segment19,
1591 segment20,
1592 segment21,
1593 segment22,
1594 segment23,
1595 segment24,
1596 segment25,
1597 segment26,
1598 segment27,
1599 segment28,
1600 segment29,
1601 segment30)
1602 SELECT
1603 p_rsob_id,
1604 G_status,
1605 p_ccid,
1606 G_source_name,
1607 G_category_name,
1608 sysdate,
1609 G_accounting_date,
1610 p_entered_cr,
1611 p_entered_dr,
1612 p_accounted_cr,
1613 p_accounted_dr,
1614 G_from_currency,
1615 G_actual_flag,
1616 -1,
1617 G_group_id,
1618 glcc.segment1,
1619 glcc.segment2,
1620 glcc.segment3,
1621 glcc.segment4,
1622 glcc.segment5,
1623 glcc.segment6,
1624 glcc.segment7,
1625 glcc.segment8,
1626 glcc.segment9,
1627 glcc.segment10,
1628 glcc.segment11,
1629 glcc.segment12,
1630 glcc.segment13,
1631 glcc.segment14,
1632 glcc.segment15,
1633 glcc.segment16,
1634 glcc.segment17,
1635 glcc.segment18,
1636 glcc.segment19,
1637 glcc.segment20,
1638 glcc.segment21,
1639 glcc.segment22,
1640 glcc.segment23,
1641 glcc.segment24,
1642 glcc.segment25,
1643 glcc.segment26,
1644 glcc.segment27,
1645 glcc.segment28,
1646 glcc.segment29,
1647 glcc.segment30
1648
1649 FROM gl_code_combinations glcc
1650 WHERE glcc.code_combination_id = p_ccid AND
1651 glcc.chart_of_accounts_id = G_coa_id;
1652 EXCEPTION
1653 WHEN OTHERS THEN
1654 fa_srvr_msg.add_sql_error (
1655 calling_fn => 'fa_mc_upg3_pkg.insert_rec');
1656 app_exception.raise_exception;
1657
1658 END insert_rec;
1659
1660 PROCEDURE insert_ret_earnings (
1661 p_book_type_code IN VARCHAR2,
1662 p_rsob_id IN NUMBER) IS
1663 /* ************************************************************************
1664 This procedure created the balancing entry to retained earnings for a
1665 given balancing segment. After all the CCID balances have been obtained
1666 and inserted into GL_INTERFACE, we will have to balance each balancing
1667 segment. This is because we do not obtain the balances of revenue/expense
1668 accounts for past years and this will cause the entries in GL_INTERFACE
1669 to be out of balance. We use the template RE ccid in gl_sets_of_books
1670 in the dynamic sql and group by the balancing segment to create the plug
1671 to the correct retained earning account.
1672 ************************************************************************ */
1673
1674 insert_cursor INTEGER; -- Handles the insert cursor
1675 l_row_count NUMBER; -- Number of rows returned by FETCH
1676
1677 buf1 VARCHAR2(255);
1678 buf2 VARCHAR2(255);
1679 buf3 VARCHAR2(255);
1680 buf4 VARCHAR2(255);
1681 buf5 VARCHAR2(255);
1682 buf6 VARCHAR2(255);
1683 buf7 VARCHAR2(255);
1684 buf8 VARCHAR2(255);
1685 buf9 VARCHAR2(255);
1686 buf10 VARCHAR2(255);
1687
1688 BEGIN
1689 if (g_print_debug) then
1690 fa_debug_pkg.add('calculate_balances',
1691 'Inserting retained earnings in GL_INTERFACE',
1692 'start');
1693 end if;
1694
1695 G_insert_buf := '';
1696 G_insert_buf := G_insert_buf ||
1697 'INSERT INTO gl_interface(' ||
1698 'status,' ||
1699 'set_of_books_id,' ||
1700 'user_je_source_name,' ||
1701 'user_je_category_name,' ||
1702 'currency_code,' ||
1703 'date_created,' ||
1704 'created_by,' ||
1705 'accounting_date,' ||
1706 'actual_flag,' ||
1707 'entered_cr,' ||
1708 'entered_dr,' ||
1709 'accounted_cr,' ||
1710 'accounted_dr,' ||
1711 G_flex_buf ||
1712 'group_id) ' ;
1713
1714 G_insert_buf := G_insert_buf ||
1715 'SELECT ' ||
1716 ':status' || ',' ||
1717 ':rsob_id' || ',' ||
1718 ':source' || ',' ||
1719 ':category' || ',' ||
1720 ':fcurrency' || ',' ||
1721 ':date_created' || ',' ||
1722 -1 || ',' ||
1723 ':acc_date'|| ',' ||
1724 ':actual_flag' || ',' ||
1725 'decode(sign(sum(nvl(gli.entered_dr, 0) - ' ||
1726 'nvl(gli.entered_cr, 0))), 1, ' ||
1727 '(sum(nvl(gli.entered_dr, 0) - ' ||
1728 'nvl(gli.entered_cr,0))), 0), ' ||
1729 'decode(sign(sum(nvl(gli.entered_dr, 0) - ' ||
1730 'nvl(gli.entered_cr, 0))), -1, ' ||
1731 '(sum(nvl(gli.entered_cr, 0) - ' ||
1732 'nvl(gli.entered_dr, 0))), 0), ' ||
1733 'decode(sign(sum(nvl(gli.accounted_dr, 0) - ' ||
1734 'nvl(gli.accounted_cr, 0))), 1, ' ||
1735 'sum(nvl(gli.accounted_dr, 0) - ' ||
1736 'nvl(gli.accounted_cr, 0)), 0), '||
1737 'decode(sign(sum(nvl(gli.accounted_dr, 0) - ' ||
1738 'nvl(gli.accounted_cr, 0))), -1, ' ||
1739 'sum(nvl(gli.accounted_cr, 0) - ' ||
1740 'nvl(gli.accounted_dr, 0)), 0), ';
1741
1742 FOR i in 1..G_flex_seg_count LOOP
1743 IF (coa_structure(i) = G_bal_seg_col) THEN
1744 G_insert_buf := G_insert_buf ||
1745 'min(gli.' || G_bal_seg_col || '),';
1746 ELSE
1747 G_insert_buf := G_insert_buf ||
1748 'min(glcc.' ||
1749 coa_structure(i) || '),';
1750 END IF;
1751 END LOOP;
1752
1753 G_insert_buf := G_insert_buf || ':group_id' ;
1754 G_insert_buf := G_insert_buf ||
1755 ' FROM ' ||
1756 'gl_code_combinations glcc,' ||
1757 'gl_interface gli ' ||
1758 'WHERE ' ||
1759 'gli.user_je_source_name = :source and ' ||
1760 'gli.set_of_books_id = :rsob_id and ' ||
1761 'gli.group_id = :group_id and ' ||
1762 'glcc.chart_of_accounts_id = :coa_id and ' ||
1763 'glcc.template_id is NULL and ' ||
1764 'glcc.code_combination_id = :re_ccid ' ||
1765 'GROUP BY ' ||
1766 'gli.' || G_bal_seg_col ;
1767
1768 -- write buffer to log when run in debug mode
1769 if (g_print_debug) then
1770 buf1 := SUBSTRB(G_insert_buf,200,200);
1771 buf2 := SUBSTRB(G_insert_buf,400,200);
1772 buf3 := SUBSTRB(G_insert_buf,600,200);
1773 buf4 := SUBSTRB(G_insert_buf,800,200);
1774 buf5 := SUBSTRB(G_insert_buf,1000,200);
1775 buf6 := SUBSTRB(G_insert_buf,1200,200);
1776 buf7 := SUBSTRB(G_insert_buf,1400,200);
1777 buf8 := SUBSTRB(G_insert_buf,1600,200);
1778 buf9 := SUBSTRB(G_insert_buf,1800,200);
1779 buf10 := SUBSTRB(G_insert_buf,2000,200);
1780
1781 fa_rx_conc_mesg_pkg.log('Insert buf is: ' ||
1782 SUBSTRB(G_insert_buf,1,199));
1783 fa_rx_conc_mesg_pkg.log(buf1);
1784 fa_rx_conc_mesg_pkg.log(buf2);
1785 fa_rx_conc_mesg_pkg.log(buf3);
1786 fa_rx_conc_mesg_pkg.log(buf4);
1787 fa_rx_conc_mesg_pkg.log(buf5);
1788 fa_rx_conc_mesg_pkg.log(buf6);
1789 fa_rx_conc_mesg_pkg.log(buf7);
1790 fa_rx_conc_mesg_pkg.log(buf8);
1791 fa_rx_conc_mesg_pkg.log(buf9);
1792 fa_rx_conc_mesg_pkg.log(buf10);
1793 end if;
1794 /*
1795 dbms_output.put_line('Insert buf is: ' || SUBSTRB(G_insert_buf,1,199));
1796 dbms_output.put_line(buf1);
1797 dbms_output.put_line(buf2);
1798 dbms_output.put_line(buf3);
1799 dbms_output.put_line(buf4);
1800 dbms_output.put_line(buf5);
1801 dbms_output.put_line(buf6);
1802 dbms_output.put_line(buf7);
1803 dbms_output.put_line(buf8);
1804 dbms_output.put_line(buf9);
1805 dbms_output.put_line(buf10);
1806 */
1807
1808 -- OPEN the insert cursor
1809 insert_cursor := dbms_sql.open_cursor;
1810
1811 if (g_print_debug) then
1812 fa_debug_pkg.add('insert_ret_earnings',
1813 'Open cursor',
1814 'success');
1815 end if;
1816 -- parse the insert smt
1817 dbms_sql.parse(insert_cursor, G_insert_buf, dbms_sql.v7);
1818
1819 if (g_print_debug) then
1820 fa_debug_pkg.add('insert_ret_earnings',
1821 'Parse cursor',
1822 'success');
1823 end if;
1824
1825 -- bind all input variables
1826
1827 dbms_sql.bind_variable(insert_cursor, ':category',G_category_name );
1828 dbms_sql.bind_variable(insert_cursor, ':status', G_status);
1829 dbms_sql.bind_variable(insert_cursor, ':actual_flag', G_actual_flag);
1830 dbms_sql.bind_variable(insert_cursor, ':fcurrency', G_from_currency);
1831 dbms_sql.bind_variable(insert_cursor, ':date_created', sysdate);
1832 dbms_sql.bind_variable(insert_cursor, ':acc_date',G_accounting_date);
1833 dbms_sql.bind_variable(insert_cursor, ':source',G_source_name );
1834 dbms_sql.bind_variable(insert_cursor, ':group_id', G_group_id);
1835 dbms_sql.bind_variable(insert_cursor, ':rsob_id', p_rsob_id);
1836 dbms_sql.bind_variable(insert_cursor, ':coa_id', G_coa_id);
1837 dbms_sql.bind_variable(insert_cursor, ':re_ccid', G_re_ccid);
1838
1839 l_row_count := dbms_sql.execute(insert_cursor);
1840
1841 if (g_print_debug) then
1842 fa_debug_pkg.add('insert_ret_earnings',
1843 'Number of retained earnings rows inserted',
1844 l_row_count);
1845 end if;
1846 dbms_sql.close_cursor(insert_cursor);
1847
1848 if (g_print_debug) then
1849 fa_debug_pkg.add('calculate_balances',
1850 'Inserting retained earnings in GL_INTERFACE',
1851 'success');
1852 end if;
1853 EXCEPTION
1854 WHEN OTHERS THEN
1855 fa_srvr_msg.add_sql_error (
1856 calling_fn => 'fa_mc_upg3_pkg.insert_ret_earnings');
1857 app_exception.raise_exception;
1858
1859 END insert_ret_earnings;
1860
1861
1862 PROCEDURE insert_balances(
1863 p_rsob_id IN NUMBER) IS
1864 /* ************************************************************************
1865 This procedure loops thro each of the global arrays that hold the CCID's
1866 and the entered and accounted amounts and calls inert_rec to insert the
1867 row in GL_INTERFACE.
1868 ************************************************************************ */
1869
1870 BEGIN
1871 if (g_print_debug) then
1872 fa_debug_pkg.add('calculate_balances',
1873 'Inserting account balances in GL_INTERFACE',
1874 'start');
1875 end if;
1876
1877 -- get the group_id from sequence
1878 SELECT gl_interface_control_s.nextval
1879 INTO G_group_id
1880 FROM dual;
1881
1882 -- dbms_output.put_line('Group id is: ' || G_group_id);
1883
1884 -- get the category name
1885 SELECT user_je_category_name
1886 INTO G_category_name
1887 FROM GL_JE_CATEGORIES
1888 WHERE je_category_name = 'MRC Open Balances';
1889
1890 -- get the source name
1891 SELECT user_je_source_name
1892 INTO G_source_name
1893 FROM GL_JE_SOURCES
1894 WHERE je_source_name = 'Assets';
1895
1896 -- LOOP thro all ccid arrays and insert rows into gl_interface
1897
1898 FOR i IN 1..G_adj_ccid_count LOOP
1899 insert_rec(
1900 p_rsob_id,
1901 adjustments_ccids(i).entered_cr,
1902 adjustments_ccids(i).entered_dr,
1903 adjustments_ccids(i).accounted_cr,
1904 adjustments_ccids(i).accounted_dr,
1905 adjustments_ccids(i).ccid);
1906 END LOOP;
1907
1908 FOR i IN 1..G_drsv_ccid_count LOOP
1909 insert_rec(
1910 p_rsob_id,
1911 deprn_reserve_ccids(i).entered_cr,
1912 deprn_reserve_ccids(i).entered_dr,
1913 deprn_reserve_ccids(i).accounted_cr,
1914 deprn_reserve_ccids(i).accounted_dr,
1915 deprn_reserve_ccids(i).ccid);
1916 END LOOP;
1917
1918 FOR i IN 1..G_dexp_ccid_count LOOP
1919 insert_rec(
1920 p_rsob_id,
1921 deprn_expense_ccids(i).entered_cr,
1922 deprn_expense_ccids(i).entered_dr,
1923 deprn_expense_ccids(i).accounted_cr,
1924 deprn_expense_ccids(i).accounted_dr,
1925 deprn_expense_ccids(i).ccid);
1926
1927 END LOOP;
1928
1929 FOR i IN 1..G_rrsv_ccid_count LOOP
1930 insert_rec(
1931 p_rsob_id,
1932 reval_reserve_ccids(i).entered_cr,
1933 reval_reserve_ccids(i).entered_dr,
1934 reval_reserve_ccids(i).accounted_cr,
1935 reval_reserve_ccids(i).accounted_dr,
1936 reval_reserve_ccids(i).ccid);
1937 END LOOP;
1938
1939 FOR i IN 1..G_ramo_ccid_count LOOP
1940 insert_rec(
1941 p_rsob_id,
1942 reval_amort_ccids(i).entered_cr,
1943 reval_amort_ccids(i).entered_dr,
1944 reval_amort_ccids(i).accounted_cr,
1945 reval_amort_ccids(i).accounted_dr,
1946 reval_amort_ccids(i).ccid);
1947 END LOOP;
1948
1949 FOR i IN 1..G_dfrsv_ccid_count LOOP
1950 insert_rec(
1951 p_rsob_id,
1952 def_deprn_rsv_ccids(i).entered_cr,
1953 def_deprn_rsv_ccids(i).entered_dr,
1954 def_deprn_rsv_ccids(i).accounted_cr,
1955 def_deprn_rsv_ccids(i).accounted_dr,
1956 def_deprn_rsv_ccids(i).ccid);
1957 END LOOP;
1958
1959 FOR i IN 1..G_dfexp_ccid_count LOOP
1960 insert_rec(
1961 p_rsob_id,
1962 def_deprn_exp_ccids(i).entered_cr,
1963 def_deprn_exp_ccids(i).entered_dr,
1964 def_deprn_exp_ccids(i).accounted_cr,
1965 def_deprn_exp_ccids(i).accounted_dr,
1966 def_deprn_exp_ccids(i).ccid);
1967 END LOOP;
1968
1969 if (g_print_debug) then
1970 fa_debug_pkg.add('calculate_balances',
1971 'Inserting account balances in GL_INTERFACE',
1972 'success');
1973 end if;
1974 EXCEPTION
1975 WHEN OTHERS THEN
1976 fa_srvr_msg.add_sql_error (
1977 calling_fn => 'fa_mc_upg3_pkg.insert_balances');
1978 app_exception.raise_exception;
1979
1980 END insert_balances;
1981
1982 END FA_MC_UPG3_PKG;