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