[Home] [Help]
PACKAGE BODY: APPS.JL_ZZ_GL_INFL_ADJ_PKG
Source
1 PACKAGE BODY JL_ZZ_GL_INFL_ADJ_PKG AS
2 /* $Header: jlzzgaxb.pls 120.10 2010/07/12 17:19:54 abuissa ship $ */
3 ----------------------------------------------------------------------------
4 -- FUNCTION --
5 -- inflation_adjustment --
6 -- --
7 -- DESCRIPTION --
8 -- Use this process to perform the Inflation Adjustment for Argentina --
9 -- --
10 -- PURPOSE: --
11 -- Oracle Applications Rel 11.0 --
12 -- Product : Oracle General Ledger - Latin America --
13 -- --
14 -- PARAMETERS: --
15 -- Inflation Run Id : Execution id --
16 -- From period : First period to be adjusted --
17 -- To Period : Last period to be adjusted --
18 -- Set of Books Id : Set of books to be adjusted --
19 -- Index Id : used to adjust the accounts --
20 -- Error Message : Returned to the report --
21 -- Error Message Number : Idem --
22 -- Error Message Code : Idem --
23 -- --
24 -- HISTORY: --
25 -- 19/03/97 German Bertot --
26 -- 20/08/97 German Bertot Changed the procedure definition to call --
27 -- it from the Infl. Adj. Report instead of --
28 -- submiting it as a concurrent request. --
29 -- 22/10/98 Deepak Khanna 1. Major change in Adj.calculation method. --
30 -- 2. Extending Infl. Adj. procedure for 'CL'. --
31 -- 3. Enhancement in segment selection criteria --
32 -- 4. Introduction of rate precision . --
33 ----------------------------------------------------------------------------
34 FUNCTION INFLATION_ADJUSTMENT (p_inflation_adj_run_id IN NUMBER
35 , p_adjust_from_period IN VARCHAR2
36 , p_adjust_to_period IN VARCHAR2
37 , p_set_of_books_id IN NUMBER
38 , p_infl_adj_index_id IN NUMBER
39 , p_group_id IN OUT NOCOPY NUMBER
40 , p_err_msg_name IN OUT NOCOPY VARCHAR2
41 , p_err_msg_num IN OUT NOCOPY NUMBER
42 , p_err_msg_code IN OUT NOCOPY VARCHAR2)
43 RETURN NUMBER IS
44
45 GL_APPS_ID CONSTANT NUMBER (15) := 101;
46
47 set_of_books_name VARCHAR2 (30);
48 set_of_books_currency_code VARCHAR2 (15);
49 currency_precision NUMBER (1);
50 chart_of_accounts_id NUMBER;
51 period_set_name VARCHAR2 (15);
52 balancing_segment VARCHAR2 (30);
53 balancing_segment_idx NUMBER (3);
54
55 num_enabled_segments INTEGER;
56 number_records NUMBER (15);
57 user_je_category_name VARCHAR2 (25);
58 user_je_source_name VARCHAR2 (25);
59
60 infl_adjust_gain_loss_ccid NUMBER (15);
61
62 main_cursor INTEGER;
63
64 infl_adjustment_rate NUMBER;
65
66 infl_adj_gla NUMBER (15); -- Infl. Adj. Gain/Loss Account
67
68 balancing_segment_tot_amount NUMBER := 0;
69 acct_total_adj_amount NUMBER := 0;
70 acct_period_adj_amount NUMBER := 0;
71
72 fv_flag VARCHAR2 (1) := 'N';
73 fv_adjustment_amount NUMBER;
74 fv_period_amount NUMBER := 0;
75
76 c_code_combination_id NUMBER (15);
77 c_balancing_segment VARCHAR2 (25);
78 c_ytd_balance NUMBER;
79 c_ptd_balance NUMBER;
80 c_period_name VARCHAR2 (15);
81 previous_code_combination_id NUMBER (15);
82 previous_balancing_segment VARCHAR2 (25);
83 accounting_date DATE;
84
85 consider_YTD_amount VARCHAR2 (1) := 'N';
86 acct_begin_YTD_adj_amount NUMBER;
87 YTD_prev_period_name VARCHAR2 (25);
88 zero_main_records VARCHAR2(3) := 'YES';
89
90 --profile_country_code VARCHAR2(10) := fnd_profile.value('JGZZ_COUNTRY_CODE');
91 profile_country_code VARCHAR2(10) := jg_zz_shared_pkg.get_country(null,null);
92
93 program_abort EXCEPTION;
94
95 ------------------------------------------------------------
96 -- Record Type Decalartion To Store 'Main Cursor' Values --
97 ------------------------------------------------------------
98 TYPE main_cursor_record IS RECORD (
99 r_ccid NUMBER(15),
100 r_bal_segment VARCHAR2(25),
101 r_ytd_balance NUMBER,
102 r_ptd_balance NUMBER,
103 r_period_name VARCHAR2(15));
104
105 prevrec main_cursor_record;
106
107 ------------------------------------------------------------
108 -- Get the corresponding functional currency amount for --
109 -- account's Non functional currency amount . --
110 ------------------------------------------------------------
111
112 FUNCTION get_non_func_amt (p_set_of_books_id IN NUMBER
113 , p_code_combination_id IN NUMBER
114 , p_ytd IN OUT NOCOPY NUMBER
115 , p_ptd IN OUT NOCOPY NUMBER
116 , p_period_name IN VARCHAR2)
117 RETURN BOOLEAN IS
118
119 BEGIN
120
121 SELECT nvl(sum(nvl(gb.begin_balance_dr_beq, 0) - nvl (gb.begin_balance_cr_beq, 0)),0) YTD_AMOUNT,
122 nvl(sum(nvl (gb.period_net_dr_beq, 0) - nvl (gb.period_net_cr_beq, 0)),0) PTD_AMOUNT
123 into p_ytd,p_ptd
124 FROM gl_balances gb
125 WHERE gb.ledger_id = p_set_of_books_id
126 AND gb.code_combination_id = p_code_combination_id
127 AND gb.currency_code <> set_of_books_currency_code
128 AND gb.Period_name = p_period_name
129 AND gb.actual_flag = 'A'
130 AND gb.translated_flag is not null
131 AND gb.template_id is null;
132
133 RETURN TRUE;
134
135 EXCEPTION
136 WHEN OTHERS THEN
137 p_err_msg_name := substr (SQLERRM, 1, 100);
138 p_err_msg_code := 'ORA';
139 RETURN FALSE;
140
141 END get_non_func_amt;
142
143
144 ------------------------------------------------------------
145 -- Get the previous period name. --
146 ------------------------------------------------------------
147
148 FUNCTION get_previous_period ( p_period_set_name IN VARCHAR2
149 , p_period_name IN VARCHAR2
150 , p_prev_period_name IN OUT NOCOPY VARCHAR2)
151 RETURN BOOLEAN IS
152
153 curr_period_num NUMBER(15);
154 curr_period_Year NUMBER(15);
155 curr_period_type VARCHAR2(15);
156 period_counter NUMBER(15); --Bug #9720379
157
158 BEGIN
159 SELECT a.period_num,
160 a.period_year,
161 a.period_type
162 INTO curr_period_num ,
163 curr_period_year,
164 curr_period_type
165 FROM gl_periods a
166 WHERE a.period_set_name = p_period_set_name
167 AND a.period_name = p_period_name;
168
169
170 /* Counting how many non-adjustment periods exist in same year lesser than
171 current one - Bug #9720379 */
172
173 SELECT count(period_num)
174 INTO period_counter
175 FROM gl_periods
176 WHERE period_set_name = p_period_set_name
177 AND period_year = curr_period_year
178 AND period_num < curr_period_num
179 AND period_type = curr_period_type
180 AND adjustment_period_flag <> 'Y';
181
182 IF curr_period_num > 1
183 AND period_counter > 0 -- Bug #9720379
184 THEN
185
186 SELECT period_name
187 INTO p_prev_period_name
188 FROM gl_periods
189 WHERE period_set_name = p_period_set_name
190 AND period_year = curr_period_year
191 AND period_type = curr_period_type
192 AND period_num = (SELECT max(period_num)
193 FROM gl_periods
194 WHERE period_set_name = p_period_set_name
195 AND period_year = curr_period_year
196 AND period_num < curr_period_num
197 AND period_type = curr_period_type
198 AND adjustment_period_flag <> 'Y');
199
200
201 ELSE
202 SELECT period_name
203 INTO p_prev_period_name
204 FROM gl_periods
205 WHERE period_set_name = p_period_set_name
206 AND period_year = curr_period_year - 1
207 AND period_type = curr_period_type
208 AND period_num = (SELECT max(period_num)
209 FROM gl_periods
210 WHERE period_set_name = p_period_set_name
211 AND period_year = curr_period_year - 1
212 AND period_type = curr_period_type
213 AND adjustment_period_flag <> 'Y');
214 END IF;
215 RETURN TRUE;
216
217 EXCEPTION
218
219 WHEN NO_DATA_FOUND THEN
220 p_err_msg_name := 'JL_ZZ_GL_PERIOD_DETL_NOT_DEF';
221 p_err_msg_num := 62022;
222 p_err_msg_code := 'APP';
223 RETURN FALSE;
224
225 WHEN OTHERS THEN
226 p_err_msg_name := substr (SQLERRM, 1, 100);
227 p_err_msg_code := 'ORA';
228 RETURN FALSE;
229
230 END get_previous_period;
231
232
233
234
235
236 ------------------------------------------------------------
237 -- Get the adjustment rate precision from the profile --
238 -----------------------------------------------------------
239
240 FUNCTION get_adj_rate_precision ( adj_rate_precision OUT NOCOPY NUMBER)
241 RETURN BOOLEAN IS
242
243 profile_in_char VARCHAR2(3);
244
245 BEGIN
246
247 -----------------------------------------------------------
248 -- Pick profile value --
249 -----------------------------------------------------------
250
251 profile_in_char := fnd_profile.value('JLZZ_INF_RATIO_PRECISION');
252
253 IF profile_in_char is NULL then
254
255 adj_rate_precision := 0;
256
257 RETURN FALSE;
258
259 END IF;
260
261 adj_rate_precision := to_number(profile_in_char);
262
263 RETURN TRUE;
264
265 EXCEPTION
266 WHEN NO_DATA_FOUND THEN
267 RETURN FALSE;
268 WHEN OTHERS THEN
269 RETURN FALSE;
270 END get_adj_rate_precision;
271
272 ------------------------------------------------------------
273 -- Retrieves the information related to the set of books --
274 ------------------------------------------------------------
275 FUNCTION get_set_of_books_info (p_set_of_books_id IN NUMBER
276 , p_set_of_books_name IN OUT NOCOPY VARCHAR2
277 , p_sob_currency_code IN OUT NOCOPY VARCHAR2
278 , p_chart_of_accounts_id IN OUT NOCOPY NUMBER
279 , p_balancing_segment IN OUT NOCOPY VARCHAR2
280 , p_period_set_name IN OUT NOCOPY VARCHAR2
281 , p_curr_precision IN OUT NOCOPY NUMBER
282 , p_num_enabled_segments IN OUT NOCOPY INTEGER)
283 RETURN BOOLEAN IS
284
285 acct_flexfield fnd_flex_key_api.flexfield_type;
286 acct_structure fnd_flex_key_api.structure_type;
287 acct_segments fnd_flex_key_api.segment_list;
288 statement VARCHAR2 (20);
289
290 cannot_find_balancing_segment EXCEPTION;
291
292 BEGIN
293
294 ------------------------------------------------------------
295 -- Get book name, currency code, AFF structure and --
296 -- calendar name. --
297 ------------------------------------------------------------
298 SELECT name, currency_code, chart_of_accounts_id, period_set_name
299 INTO p_set_of_books_name, p_sob_currency_code, p_chart_of_accounts_id,
300 p_period_set_name
301 FROM gl_sets_of_books
302 WHERE set_of_books_id = p_set_of_books_id;
303
304 ------------------------------------------------------------
305 -- Get currency precision. --
306 ------------------------------------------------------------
307 statement := 'CURR PREC';
308
309 SELECT precision
310 INTO p_curr_precision
311 FROM fnd_currencies_vl
312 WHERE currency_code = p_sob_currency_code;
313
314 ------------------------------------------------------------
315 -- Get flexfield information --
316 ------------------------------------------------------------
317 statement := 'FND FLEX';
318 fnd_flex_key_api.set_session_mode('customer_data');
319
320 acct_flexfield := fnd_flex_key_api.find_flexfield
321 (appl_short_name => 'SQLGL'
322 , flex_code =>'GL#');
323
324 acct_structure := fnd_flex_key_api.find_structure
325 (flexfield => acct_flexfield
326 , structure_number => p_chart_of_accounts_id);
327
328 ------------------------------------------------------------
329 -- Get number of segments enabled and the segments names. --
330 ------------------------------------------------------------
331 fnd_flex_key_api.get_segments (flexfield => acct_flexfield
332 , structure => acct_structure
333 , enabled_only => TRUE
334 , nsegments => p_num_enabled_segments
335 , segments => acct_segments);
336
337 ------------------------------------------------------------
338 -- Get the balancing segment name (for example 'SEGMENT1')--
339 ------------------------------------------------------------
340 IF NOT fnd_flex_apis.get_segment_column (101
341 ,'GL#'
342 , p_chart_of_accounts_id
343 ,'GL_BALANCING'
344 ,p_balancing_segment) THEN
345
346 RAISE cannot_find_balancing_segment;
347 END IF;
348
349 RETURN TRUE;
350
351 EXCEPTION
352 WHEN cannot_find_balancing_segment THEN
353 p_err_msg_name := 'JL_ZZ_GL_BALANCING_SEG_ERROR';
354 p_err_msg_num := 62014;
355 p_err_msg_code := 'APP';
356 RETURN FALSE;
357
358 WHEN OTHERS THEN
359 IF statement = 'CURR PREC' THEN
360 p_err_msg_name := 'JL_ZZ_GL_CURR_PREC_NA';
361 p_err_msg_num := 62246;
362 p_err_msg_code := 'APP';
363
364 ELSE
365 p_err_msg_name := 'JL_ZZ_GL_BOOK_INFO_NA';
366 p_err_msg_num := 62016;
367 p_err_msg_code := 'APP';
368 END IF;
369
370 RETURN FALSE;
371
372 END get_set_of_books_info;
373
374
375 ------------------------------------------------------------
376 -- Gets the price index value for a certain period. --
377 -- --
378 ------------------------------------------------------------
379 FUNCTION get_inflation_index_value (p_index_id IN NUMBER
380 , p_period_set_name IN VARCHAR2
381 , p_period_name IN VARCHAR2
382 , p_index_value IN OUT NOCOPY NUMBER)
383 RETURN BOOLEAN IS
384
385 ix_period_name VARCHAR2(15);
386 period_error EXCEPTION;
387
388 BEGIN
389
390 ix_period_name := p_period_name;
391
392 ---------------------------------------------------------------
393 -- Check for the country profile. --
394 -- If Country is 'Chile', then introduce one period lag. -- --
395 ---------------------------------------------------------------
396
397 IF fnd_profile.value('JGZZ_COUNTRY_CODE') = 'CL' THEN
398
399 IF NOT get_previous_period( p_period_set_name
400 ,p_period_name
401 ,ix_period_name) THEN
402 RAISE period_error;
403 END IF;
404
405
406 END IF;
407
408 SELECT price_index_value
409 INTO p_index_value
410 FROM fa_price_index_values fpiv
411 , gl_periods gp
412 WHERE fpiv.price_index_id = p_index_id
413 AND gp.period_name = ix_period_name
414 ANd gp.period_set_name = p_period_set_name
415 AND gp.end_date BETWEEN fpiv.from_date
416 AND nvl (fpiv.to_date, gp.end_date);
417
418 RETURN TRUE;
419
420 EXCEPTION
421 WHEN NO_DATA_FOUND THEN
422 p_err_msg_name := 'JL_AR_FA_CURR_INDX_VAL_NOT_DEF';
423 p_err_msg_num := 62001;
424 p_err_msg_code := 'APP';
425 RETURN FALSE;
426
427 WHEN period_error THEN
428 RETURN FALSE;
429
430 WHEN OTHERS THEN
431 p_err_msg_name := substr (SQLERRM, 1, 100);
432 p_err_msg_code := 'ORA';
433 RETURN FALSE;
434
435 END get_inflation_index_value;
436
437
438 ------------------------------------------------------------
439 -- Get group Id for the Journal Entries to be inserted in --
440 -- GL_INTERFACE. --
441 -- It also returns the inflation adjustment gain and loss --
442 -- account template and the index value for the particular--
443 -- period (the 'to' period). --
444 ------------------------------------------------------------
445 FUNCTION init_process (p_group_id IN OUT NOCOPY NUMBER
446 , p_set_of_books_id IN NUMBER
447 , p_REI_ccid IN OUT NOCOPY NUMBER
448 , p_axi_run_id IN NUMBER
449 , p_to_period_name IN VARCHAR2
450 , p_accounting_date IN OUT NOCOPY DATE
451 , p_user_je_category_name IN OUT NOCOPY VARCHAR2
452 , p_user_je_source_name IN OUT NOCOPY VARCHAR2)
453 RETURN BOOLEAN IS
454
455 statement VARCHAR2 (20);
456
457 BEGIN
458 statement := 'GROUP ID';
459 SELECT gl_interface_control_s.nextval
460 INTO p_group_id
461 FROM sys.dual;
462
463 statement := 'REI ACCOUNT';
464 SELECT code_combination_id
465 INTO p_REI_ccid
466 FROM jl_zz_gl_axi
467 WHERE set_of_books_id = p_set_of_books_id;
468
469 statement := 'DELETE';
470 DELETE FROM jl_zz_gl_axi_tmp
471 WHERE axi_run_id = p_axi_run_id;
472
473 statement := 'SOURCE';
474 SELECT user_je_source_name
475 INTO p_user_je_source_name
476 FROM gl_je_sources
477 WHERE je_source_name = 'Inflation';
478
479
480 statement := 'CATEGORY';
481 SELECT user_je_category_name
482 INTO p_user_je_category_name
483 FROM gl_je_categories
484 WHERE je_category_name = 'Adjustment';
485
486 statement := 'ACCOUNTING DATE';
487 SELECT end_date
488 INTO p_accounting_date
489 FROM gl_period_statuses
490 WHERE application_id = GL_APPS_ID
491 AND set_of_books_id = p_set_of_books_id
492 AND period_name = p_to_period_name;
493
494
495 RETURN TRUE;
496
497
498 EXCEPTION
499 WHEN OTHERS THEN
500 IF statement = 'REI ACCOUNT' THEN
501 p_err_msg_name := 'JL_ZZ_GL_INFL_ADJ_ACCOUNT';
502 p_err_msg_num := 62018;
503 p_err_msg_code := 'APP';
504 RETURN FALSE;
505
506 ELSIF statement = 'CATEGORY' THEN
507 p_err_msg_name := 'JL_ZZ_GL_CATEGORY_NAME';
508 p_err_msg_num := 62243;
509 p_err_msg_code := 'APP';
510 RETURN FALSE;
511
512 ELSIF statement = 'SOURCE' THEN
513 p_err_msg_name := 'JL_ZZ_GL_SOURCE_NAME';
514 p_err_msg_num := 62242;
515 p_err_msg_code := 'APP';
516 RETURN FALSE;
517
518 ELSE
519 p_err_msg_name := substr (SQLERRM, 1, 100);
520 p_err_msg_code := 'ORA';
521 RETURN FALSE;
522
523 END IF;
524
525 END init_process;
526
527
528 ------------------------------------------------------------
529 -- Given a price index and a period, returns the inflation--
530 -- adjustment rate to be applied to the account balance --
531 -- in order to get the adjusted value. --
532 -- --
533 ------------------------------------------------------------
534 FUNCTION get_adjustment_rate (p_index_id IN NUMBER
535 , p_set_of_books_id IN NUMBER
536 , p_period_set_name IN VARCHAR2
537 , p_period_name IN VARCHAR2
538 , p_infl_rate OUT NOCOPY NUMBER)
539 RETURN BOOLEAN IS
540
541
542
543 current_period_idx_value NUMBER;
544 to_period_idx_value NUMBER;
545
546
547 adj_rate_precision NUMBER(3);
548
549
550 statement VARCHAR2 (30);
551 INDEX_EQUAL_TO_ZERO EXCEPTION;
552 index_error EXCEPTION;
553
554
555 BEGIN
556
557
558 statement := 'CURRENT_PERIOD_INDEX';
559
560
561
562 IF NOT get_inflation_index_value (p_index_id
563 , p_period_set_name
564 , p_period_name
565 , current_period_idx_value) THEN
566 RAISE index_error;
567 END IF;
568
569 IF current_period_idx_value = 0 then
570 RAISE INDEX_EQUAL_TO_ZERO;
571 END IF;
572
573 statement := 'TO_PERIOD_INDEX';
574
575
576 IF NOT get_inflation_index_value (p_index_id
577 , p_period_set_name
578 , p_adjust_to_period
579 , to_period_idx_value) THEN
580 RAISE index_error;
581 END IF;
582
583 IF NOT get_adj_rate_precision(adj_rate_precision) then
584
585 p_infl_rate := (to_period_idx_value / current_period_idx_value) - 1;
586 ELSE
587 p_infl_rate := round(((to_period_idx_value / current_period_idx_value)-1),
588 adj_rate_precision);
589
590 END IF;
591
592
593 RETURN TRUE;
594
595 EXCEPTION
596 WHEN NO_DATA_FOUND THEN
597 IF statement = 'PREVIOUS PERIOD' THEN
598 p_err_msg_name := 'JL_ZZ_GL_PRICE_INDEX_VALUE_NA';
599 p_err_msg_num := 62023;
600 p_err_msg_code := 'APP';
601 RETURN FALSE;
602
603 END IF;
604
605 WHEN INDEX_EQUAL_TO_ZERO THEN
606 p_err_msg_name := 'JL_ZZ_GL_PRICE_INDEX_VALUE_NA';
607 p_err_msg_num := 62023;
608 p_err_msg_code := 'APP';
609 RETURN FALSE;
610
611 WHEN index_error THEN
612 RETURN FALSE;
613
614 WHEN OTHERS THEN
615 p_err_msg_name := substr (SQLERRM, 1, 100);
616 p_err_msg_code := 'ORA';
617 RETURN FALSE;
618
619 END get_adjustment_rate;
620
621
622 ------------------------------------------------------------
623 -- Given a period name and a calendar, returns the period --
624 -- year and period num. --
625 -- --
626 ------------------------------------------------------------
627 FUNCTION get_period_counter (p_period_set_name IN VARCHAR2
628 , p_period_name IN VARCHAR2
629 , p_period_year IN OUT NOCOPY NUMBER
630 , p_period_num IN OUT NOCOPY NUMBER)
631 RETURN BOOLEAN IS
632
633 BEGIN
634
635 SELECT period_year, period_num
636 INTO p_period_year, p_period_num
637 FROM gl_periods
638 WHERE period_name = p_period_name
639 AND period_set_name = p_period_set_name;
640
641 RETURN TRUE;
642
643 EXCEPTION
644 WHEN OTHERS THEN
645 p_err_msg_name := substr (SQLERRM, 1, 100);
646 p_err_msg_code := 'ORA';
647 RETURN FALSE;
648
649 END get_period_counter;
650
651
652 ------------------------------------------------------------
653 -- Procedure: build_main_cursor --
654 -- --
655 -- Dynamically defines the main cursor, considering the --
656 -- number of segments enabled. --
657 -- --
658 ------------------------------------------------------------
659 FUNCTION build_main_cursor (p_set_of_books_id IN NUMBER
660 , p_period_set_name IN VARCHAR2
661 , p_period_from IN VARCHAR2
662 , p_period_to IN VARCHAR2
663 , p_balancing_segment IN VARCHAR2
664 , p_enabled_segments IN INTEGER
665 , p_axi_run_id IN NUMBER
666 , p_main_cursor IN OUT NOCOPY INTEGER)
667 RETURN BOOLEAN IS
668
669 cursor_string VARCHAR2 (3000);
670 idx INTEGER;
671 code_combination_id NUMBER (15);
672 balance NUMBER;
673 period_name VARCHAR2 (15);
674 segment VARCHAR2 (25);
675 period_year_from NUMBER (15);
676 period_num_from NUMBER (15);
677 period_year_to NUMBER (15);
678 period_num_to NUMBER (15);
679
680 CURSOR SEG_list IS
681 SELECT application_column_name name
682 FROM fnd_id_flex_segments
683 WHERE application_id = 101 AND
684 id_flex_code = 'GL#' AND
685 id_flex_num = chart_of_accounts_id AND
686 enabled_flag = 'Y'
687 ORDER by segment_num;
688
689
690 period_error EXCEPTION;
691
692 BEGIN
693 ------------------------------------------------------------
694 -- Gets the period year and period number for the first --
695 -- period to be adjusted. --
696 ------------------------------------------------------------
697 IF NOT get_period_counter (p_period_set_name
698 , p_period_from
699 , period_year_from
700 , period_num_from) THEN
701 RAISE period_error;
702 END IF;
703
704 ------------------------------------------------------------
705 -- Gets the period year and period number for the last --
706 -- period to be adjusted. --
707 ------------------------------------------------------------
708 IF NOT get_period_counter (p_period_set_name
709 , p_period_to
710 , period_year_to
711 , period_num_to) THEN
712 RAISE period_error;
713 END IF;
714
715 ------------------------------------------------------------
716 -- Store the cursor's select statement in cursor_string. --
717 -- Note: Balancing segment, period year and period num --
718 -- columns are not required by the program logic. --
719 -- However they were included to avoid compilation --
720 -- errors due to the coexistence of both DISTINCT --
721 -- and ORDER BY clauses. --
722 ------------------------------------------------------------
723 /*Bug 2939830 - SQL Bind compliance project
724 cursor_string :=
725 'SELECT gcc.code_combination_id'
726 || ', gcc.' || p_balancing_segment || ' balancing_sement'
727 || ', nvl (gb.begin_balance_dr, 0) - nvl (gb.begin_balance_cr, 0) '
728 || ' ytd_balance'
729 || ', nvl (gb.period_net_dr, 0) - nvl (gb.period_net_cr, 0) ptd_balance'
730 || ', gb.period_name'
731 || ', gb.period_year'
732 || ', gb.period_num'
733 || ' FROM gl_code_combinations gcc'
734 || ' , gl_balances gb'
735 || ' , jl_zz_gl_axi_accounts jagaa'
736 || ' WHERE gcc.code_combination_id = gb.code_combination_id'
737 || ' AND gb.ledger_id = ' || p_set_of_books_id
738 || ' AND gb.currency_code = ''' || set_of_books_currency_code||''''
739 || ' AND gb.actual_flag = ''A'''
740 || ' AND gb.translated_flag is null'
741 || ' AND gb.template_id is null'
742 || ' AND gb.period_year BETWEEN ' || period_year_from
743 || ' AND ' || period_year_to
744 || ' AND gb.period_num BETWEEN ' || period_num_from
745 || ' AND ' || period_num_to
746 || ' AND jagaa.axi_run_id = ' || p_axi_run_id;
747 */
748
749 cursor_string :=
750 'SELECT gcc.code_combination_id'
751 || ', gcc.' || p_balancing_segment || ' balancing_sement'
752 || ', nvl (gb.begin_balance_dr, 0) - nvl (gb.begin_balance_cr, 0) '
753 || ' ytd_balance'
754 || ', nvl (gb.period_net_dr, 0) - nvl (gb.period_net_cr, 0) ptd_balance'
755 || ', gb.period_name'
756 || ', gb.period_year'
757 || ', gb.period_num'
758 || ' FROM gl_code_combinations gcc'
759 || ' , gl_balances gb'
760 || ' , jl_zz_gl_axi_accounts jagaa'
761 || ' WHERE gcc.code_combination_id = gb.code_combination_id'
762 || ' AND gb.ledger_id = :1'
763 --|| ' AND gb.currency_code = ''' || ':2' ||'''' --Bug 3183432
764 || ' AND gb.currency_code = :2'
765 || ' AND gb.actual_flag = ''A'''
766 || ' AND gb.translated_flag is null'
767 || ' AND gb.template_id is null'
768 || ' AND gb.period_year BETWEEN :3' || ' AND :4 '
769 || ' AND gb.period_num BETWEEN :5' || ' AND :6 '
770 || ' AND jagaa.axi_run_id = :7';
771
772 ------------------------------------------------------------
773 -- Depending on the number of segments enabled adds --
774 -- as many conditions as necessary to the select statement--
775 ------------------------------------------------------------
776 idx := 0;
777
778 FOR seg_rec IN SEG_list LOOP
779
780 idx := idx + 1;
781
782 cursor_string := cursor_string ||
783 ' AND gcc.' || seg_rec.name || ' BETWEEN jagaa.' || seg_rec.name ||
784 '_low AND jagaa.' || seg_rec.name || '_high';
785
786 IF upper(seg_rec.name) = upper(balancing_segment) THEN
787
788 balancing_segment_idx := idx;
789
790 END IF;
791
792 END LOOP;
793
794 ------------------------------------------------------------
795 -- Ordering criteria for the cursor. --
796 ------------------------------------------------------------
797 cursor_string := cursor_string || ' ORDER BY gcc.' ||
798 p_balancing_segment ||
799 ', gcc.code_combination_id, gb.period_year, gb.period_num';
800
801 ------------------------------------------------------------
802 -- Dynamic sql operations to parse the select statement --
803 -- and to define columns datatypes. --
804 ------------------------------------------------------------
805 dbms_sql.parse (p_main_cursor, rtrim (cursor_string), dbms_sql.NATIVE);
806 dbms_sql.bind_variable (p_main_cursor, ':1', p_set_of_books_id);
807 dbms_sql.bind_variable (p_main_cursor, ':2', set_of_books_currency_code);
808 dbms_sql.bind_variable (p_main_cursor, ':3', period_year_from);
809 dbms_sql.bind_variable (p_main_cursor, ':4', period_year_to);
810 dbms_sql.bind_variable (p_main_cursor, ':5', period_num_from);
811 dbms_sql.bind_variable (p_main_cursor, ':6', period_num_to);
812 dbms_sql.bind_variable (p_main_cursor, ':7', p_axi_run_id);
813
814 dbms_sql.define_column (p_main_cursor, 1, code_combination_id);
815 dbms_sql.define_column (p_main_cursor, 2, segment, 25);
816 dbms_sql.define_column (p_main_cursor, 3, balance);
817 dbms_sql.define_column (p_main_cursor, 4, balance);
818 dbms_sql.define_column (p_main_cursor, 5, period_name, 15);
819 dbms_sql.define_column (p_main_cursor, 6, period_year_to);
820 dbms_sql.define_column (p_main_cursor, 7, period_num_to);
821
822 RETURN TRUE;
823
824 EXCEPTION
825 WHEN period_error THEN
826 RETURN FALSE;
827
828 WHEN OTHERS THEN
829 p_err_msg_name := substr (SQLERRM, 1, 100);
830 p_err_msg_code := 'ORA';
831 RETURN FALSE;
832
833 END build_main_cursor;
834
835
836 ------------------------------------------------------------
837 -- Calls the procedures to retrieve the information --
838 -- returned by the dynamic cursor. --
839 -- --
840 ------------------------------------------------------------
841 FUNCTION get_cursor_values (p_main_cursor IN INTEGER
842 , p_code_combination_id IN OUT NOCOPY NUMBER
843 , p_balancing_segment IN OUT NOCOPY VARCHAR2
844 , p_ytd_balance IN OUT NOCOPY NUMBER
845 , p_ptd_balance IN OUT NOCOPY NUMBER
846 , p_period_name IN OUT NOCOPY VARCHAR2)
847 RETURN BOOLEAN IS
848
849 non_func_ytd NUMBER;
850 non_func_ptd NUMBER;
851
852 BEGIN
853 dbms_sql.column_value (p_main_cursor, 1, p_code_combination_id);
854 dbms_sql.column_value (p_main_cursor, 2, p_balancing_segment);
855 dbms_sql.column_value (p_main_cursor, 3, p_ytd_balance);
856 dbms_sql.column_value (p_main_cursor, 4, p_ptd_balance);
857 dbms_sql.column_value (p_main_cursor, 5, p_period_name);
858 /*
859 IF profile_country_code = 'CL' THEN
860 IF NOT get_non_func_amt(p_set_of_books_id
861 , p_code_combination_id
862 , non_func_ytd
863 , non_func_ptd
864 , p_period_name) THEN
865 RETURN FALSE;
866 END IF;
867 ELSE
868 non_func_ytd := 0;
869 non_func_ptd := 0;
870 END IF;
871
872 p_ytd_balance := p_ytd_balance - non_func_ytd;
873 p_ptd_balance := p_ptd_balance - non_func_ptd;
874
875 */
876
877 RETURN TRUE;
878
879 EXCEPTION
880 WHEN OTHERS THEN
881 p_err_msg_name := substr (SQLERRM, 1, 100);
882 p_err_msg_code := 'ORA';
883 RETURN FALSE;
884
885 END get_cursor_values;
886
887
888 ------------------------------------------------------------
889 -- Inserts in GL_INTERFACE the lines passed as parameters.--
890 -- If the amount is 0 then do not insert any row. --
891 -- --
892 ------------------------------------------------------------
893 FUNCTION insert_gl_interface (p_set_of_books_id IN NUMBER
894 , p_code_combination_id IN NUMBER
895 , p_accounting_date IN DATE
896 , p_currency_code IN VARCHAR2
897 , p_amount IN NUMBER
898 , p_group_id IN NUMBER
899 , p_user_je_category_name IN VARCHAR2
900 , p_user_je_source_name IN VARCHAR2)
901 RETURN BOOLEAN IS
902
903 insert_failed EXCEPTION;
904
905 BEGIN
906 ------------------------------------------------------------
907 -- If there's nothing to adjust, then do nothing... --
908 ------------------------------------------------------------
909 IF p_amount <> 0 THEN
910 INSERT INTO gl_interface (status
911 , set_of_books_id
912 , accounting_date
913 , currency_code
914 , date_created
915 , created_by
916 , actual_flag
917 , user_je_category_name
918 , user_je_source_name
919 , entered_dr
920 , entered_cr
921 , reference1
922 , code_combination_id
923 , group_id)
924 VALUES ('NEW'
925 , p_set_of_books_id
926 , p_accounting_date
927 , p_currency_code
928 , sysdate
929 , fnd_global.user_id
930 , 'A'
931 , p_user_je_category_name
932 , p_user_je_source_name
933 , decode (sign (p_amount), -1, 0, p_amount)
934 , decode (sign (p_amount), -1, p_amount * (-1), 0)
935 , 'JL'
936 , p_code_combination_id
937 , p_group_id );
938
939 IF SQL%NOTFOUND THEN
940 RAISE insert_failed;
941 END IF;
942
943 END IF;
944
945 RETURN TRUE;
946
947 EXCEPTION
948 WHEN OTHERS THEN
949 p_err_msg_name := substr (SQLERRM, 1, 100);
950 p_err_msg_code := 'ORA';
951 RETURN FALSE;
952
953 END insert_gl_interface;
954
955
956 ------------------------------------------------------------
957 -- Gets the inflation adjustment gain and loss account --
958 -- for a particular balancing segment based on the REI --
959 -- template. --
960 -- --
961 ------------------------------------------------------------
962 FUNCTION get_infl_adj_gla (p_default_REI_ccid IN NUMBER
963 , p_balancing_segment IN VARCHAR2
964 , p_balancing_segment_value IN VARCHAR2
965 , p_new_REI_ccid OUT NOCOPY NUMBER)
966 RETURN BOOLEAN IS
967
968 nsegs NUMBER;
969 new_REI_ccid NUMBER;
970 segments fnd_flex_ext.segmentArray;
971
972 account_error EXCEPTION;
973
974 BEGIN
975
976 ------------------------------------------------------------
977 -- Get the segments values for the template account and --
978 -- the number of enabled segments. --
979 ------------------------------------------------------------
980 IF fnd_flex_ext.get_segments ('SQLGL'
981 , 'GL#'
982 , chart_of_accounts_id
983 , p_default_REI_ccid
984 , nsegs
985 , segments) THEN
986
987 ------------------------------------------------------------
988 -- Change the template balancing segment for the new one. --
989 ------------------------------------------------------------
990 segments (balancing_segment_idx) := p_balancing_segment_value;
991
992 ------------------------------------------------------------
993 -- Get the new ccid. If the account exists then returns --
994 -- the id, otherwise if dynamic insertion is allowed --
995 -- then creates a new account. --
996 ------------------------------------------------------------
997 IF NOT fnd_flex_ext.get_combination_id ('SQLGL'
998 , 'GL#'
999 , chart_of_accounts_id
1000 , sysdate
1001 , nsegs
1002 , segments
1003 , new_REI_ccid) THEN
1004 RAISE account_error;
1005 END IF;
1006
1007 ELSE
1008 RAISE account_error;
1009 END IF;
1010
1011 p_new_REI_ccid := new_REI_ccid;
1012
1013 RETURN TRUE;
1014
1015 EXCEPTION
1016 WHEN account_error THEN
1017 p_err_msg_name := 'JL_ZZ_GL_INFL_ADJ_GLA';
1018 p_err_msg_num := 62019;
1019 p_err_msg_code := 'APP';
1020 RETURN FALSE;
1021
1022 WHEN OTHERS THEN
1023 p_err_msg_name := substr (SQLERRM, 1, 100);
1024 p_err_msg_code := 'ORA';
1025 RETURN FALSE;
1026
1027 END get_infl_adj_gla;
1028
1029
1030 ------------------------------------------------------------
1031 -- Compares to string and returns TRUE if they are equal --
1032 -- and FALSE otherwise. --
1033 ------------------------------------------------------------
1034 FUNCTION same_value (p_new_value IN VARCHAR2
1035 , p_original_value IN OUT NOCOPY VARCHAR2)
1036 RETURN BOOLEAN IS
1037
1038 BEGIN
1039 IF p_original_value IS NULL THEN
1040 p_original_value := p_new_value;
1041 ELSIF p_original_value <> p_new_value THEN
1042 RETURN FALSE;
1043 END IF;
1044
1045 RETURN TRUE;
1046
1047 END same_value;
1048
1049
1050 ------------------------------------------------------------
1051 -- Returns the adjusted amount corresponding to those --
1052 -- journal entries entered in this period but whose --
1053 -- adjustment has to start in a different period --
1054 -- ('Fecha Valor (FV)'). --
1055 ------------------------------------------------------------
1056 FUNCTION get_FV_adjustment (p_set_of_books_id IN NUMBER
1057 , p_price_index_id IN NUMBER
1058 , p_period_set_name IN VARCHAR2
1059 , p_code_combination_id IN NUMBER
1060 , p_period_name IN VARCHAR2
1061 , p_adjustment_amount IN OUT NOCOPY NUMBER
1062 , p_period_fv_amount IN OUT NOCOPY NUMBER)
1063
1064 RETURN BOOLEAN IS
1065
1066 period_name VARCHAR2(15);
1067 profile_value VARCHAR2(10);
1068
1069
1070 adj_rate_precision NUMBER(3);
1071
1072 adjustment_rate NUMBER;
1073 adjustment_amount NUMBER;
1074 FV_index_value NUMBER;
1075 to_period_idx_value NUMBER;
1076
1077 INDEX_EQUAL_TO_ZERO EXCEPTION;
1078 index_error EXCEPTION;
1079
1080
1081 CURSOR FV_jes IS
1082 SELECT sum (nvl (accounted_dr, 0)) -
1083 sum (nvl (accounted_cr, 0)) FV_total
1084 , gps.period_name
1085 FROM gl_je_lines gjl
1086 , gl_je_headers gjh
1087 , gl_period_statuses gps
1088 WHERE gjh.status = 'P'
1089 AND gjh.ledger_id = p_set_of_books_id
1090 AND gjl.je_header_id = gjh.je_header_id
1091 AND gjl.code_combination_id = p_code_combination_id
1092 AND gjl.period_name = p_period_name
1093 AND gjh.actual_flag = 'A'
1094 AND gjh.currency_conversion_date is not null
1095 AND gjh.currency_conversion_date < gjl.effective_date
1096 AND gps.application_id = GL_APPS_ID
1097 AND nvl (gps.adjustment_period_flag, 'N') = 'N'
1098 AND gjh.currency_conversion_date
1099 BETWEEN gps.start_date AND gps.end_date
1100 AND gps.set_of_books_id = p_set_of_books_id
1101 AND gps.period_name <> p_period_name
1102 AND decode(profile_country_code,'CL',set_of_books_currency_code,gjh.currency_code) = gjh.currency_code
1103 GROUP BY gps.period_name;
1104
1105 BEGIN
1106 adjustment_amount := 0;
1107 p_period_fv_amount := 0;
1108
1109 period_name := p_adjust_to_period;
1110
1111 IF NOT get_inflation_index_value (p_price_index_id
1112 , p_period_set_name
1113 , period_name
1114 , to_period_idx_value) THEN
1115 RAISE index_error;
1116 END IF;
1117
1118 FOR FV_jes_rec IN FV_jes LOOP
1119
1120 period_name := FV_jes_rec.period_name;
1121
1122 IF NOT get_inflation_index_value (p_price_index_id
1123 , p_period_set_name
1124 , period_name
1125 , FV_index_value) THEN
1126 RAISE index_error;
1127 END IF;
1128
1129 IF FV_index_value = 0 then
1130 RAISE INDEX_EQUAL_TO_ZERO;
1131 END IF;
1132
1133 IF NOT get_adj_rate_precision(adj_rate_precision) then
1134
1135 adjustment_rate := (to_period_idx_value / FV_index_value) - 1;
1136
1137 ELSE
1138
1139 adjustment_rate := round(((to_period_idx_value /
1140 FV_index_value)- 1), adj_rate_precision);
1141
1142 END IF;
1143
1144
1145 p_period_fv_amount := p_period_fv_amount + FV_jes_rec.FV_total;
1146 adjustment_amount := adjustment_amount +
1147 FV_jes_rec.FV_total * adjustment_rate;
1148
1149 END LOOP;
1150
1151 p_adjustment_amount := adjustment_amount;
1152
1153 RETURN TRUE;
1154
1155 EXCEPTION
1156 WHEN INDEX_EQUAL_TO_ZERO THEN
1157 p_err_msg_name := 'JL_ZZ_GL_PRICE_INDEX_VALUE_NA';
1158 p_err_msg_num := 62023;
1159 p_err_msg_code := 'APP';
1160 RETURN FALSE;
1161
1162 WHEN index_error THEN
1163 RETURN FALSE;
1164
1165 WHEN OTHERS THEN
1166 p_err_msg_name := substr (SQLERRM, 1, 100);
1167 p_err_msg_code := 'ORA';
1168 RETURN FALSE;
1169
1170 END get_FV_adjustment;
1171
1172 ------------------------------------------------------------
1173 -- Inserts into JL_ZZ_GL_AXI_TMP data that the infl. adj. --
1174 -- report will read later. --
1175 -- --
1176 ------------------------------------------------------------
1177 FUNCTION insert_axi_tmp (p_code_combination_id IN NUMBER
1178 , p_inflation_adj_run_id IN NUMBER
1179 , p_group_id IN NUMBER
1180 , p_from_period IN VARCHAR2
1181 , p_to_period IN VARCHAR2
1182 , p_ytd_balance IN NUMBER
1183 , p_ptd_balance IN NUMBER
1184 , p_acct_total_adj_amount IN NUMBER
1185 , p_fv_flag IN VARCHAR2)
1186 RETURN BOOLEAN IS
1187
1188 BEGIN
1189 INSERT INTO jl_zz_gl_axi_tmp (code_combination_id
1190 , axi_run_id
1191 , group_id
1192 , period_from
1193 , period_to
1194 , ytd_balance
1195 , ptd_balance
1196 , adjustment_amount
1197 , fv_flag)
1198 VALUES (p_code_combination_id
1199 , p_inflation_adj_run_id
1200 , p_group_id
1201 , p_from_period
1202 , p_to_period
1203 , p_ytd_balance
1204 , p_ptd_balance
1205 , p_acct_total_adj_amount
1206 , p_fv_flag);
1207
1208 RETURN TRUE;
1209
1210 EXCEPTION
1211 WHEN OTHERS THEN
1212 p_err_msg_name := substr (SQLERRM, 1, 100);
1213 p_err_msg_code := 'ORA';
1214 RETURN FALSE;
1215
1216 END insert_axi_tmp;
1217
1218
1219 ------------------------------------------------------------
1220 -- --
1221 -- Inflation Adjustment Process. Main program. --
1222 -- --
1223 ------------------------------------------------------------
1224
1225 BEGIN
1226 ------------------------------------------------------------
1227 -- Get information related to the parameters' set of books--
1228 ------------------------------------------------------------
1229 IF NOT get_set_of_books_info (p_set_of_books_id
1230 , set_of_books_name
1231 , set_of_books_currency_code
1232 , chart_of_accounts_id
1233 , balancing_segment
1234 , period_set_name
1235 , currency_precision
1236 , num_enabled_segments) THEN
1237 RAISE program_abort;
1238 END IF;
1239
1240 ------------------------------------------------------------
1241 -- Get group id for the JEs to be created by this process.--
1242 ------------------------------------------------------------
1243 IF NOT init_process (p_group_id
1244 , p_set_of_books_id
1245 , infl_adjust_gain_loss_ccid
1246 , p_inflation_adj_run_id
1247 , p_adjust_to_period
1248 , accounting_date
1249 , user_je_category_name
1250 , user_je_source_name) THEN
1251
1252 RAISE program_abort;
1253 END IF;
1254
1255 ------------------------------------------------------------
1256 -- Open accounts cursor using dynamic SQL statements. --
1257 ------------------------------------------------------------
1258 main_cursor := dbms_sql.open_cursor;
1259
1260 ------------------------------------------------------------
1261 -- Creates the accounts cursor ordering the rows by --
1262 -- balancing segment (i.e. the order for fetching the --
1263 -- rows is decided in runtime) --
1264 ------------------------------------------------------------
1265 IF NOT build_main_cursor (p_set_of_books_id
1266 , period_set_name
1267 , p_adjust_from_period
1268 , p_adjust_to_period
1269 , balancing_segment
1270 , num_enabled_segments
1271 , p_inflation_adj_run_id
1272 , main_cursor) THEN
1273
1274 RAISE program_abort;
1275 END IF;
1276
1277 number_records := dbms_sql.execute (main_cursor);
1278 ------------------------------------------------------------
1279 -- Set Flag to Yes before While loop. --
1280 ------------------------------------------------------------
1281
1282 zero_main_records := 'YES';
1283
1284 consider_YTD_amount := 'Y';
1285
1286 WHILE dbms_sql.fetch_rows (main_cursor) > 0 LOOP
1287
1288 zero_main_records := 'NO';
1289 ------------------------------------------------------------
1290 -- Read the values from the cursor's columns --
1291 ------------------------------------------------------------
1292 IF NOT get_cursor_values (main_cursor
1293 , c_code_combination_id
1294 , c_balancing_segment
1295 , c_ytd_balance
1296 , c_ptd_balance
1297 , c_period_name) THEN
1298
1299 RAISE program_abort;
1300 END IF;
1301
1302 ------------------------------------------------------------
1303 -- If the account changes then it is required to insert a --
1304 -- journal entry line for this account. --
1305 ------------------------------------------------------------
1306 IF NOT same_value (to_char (c_code_combination_id)
1307 , previous_code_combination_id) THEN
1308
1309 ------------------------------------------------------------
1310 -- Rounding before inserting in GL_INTERFACE and before --
1311 -- adding to the balancing segment total avoids unbalanced--
1312 -- journal entries. --
1313 ------------------------------------------------------------
1314 acct_total_adj_amount := round ((acct_total_adj_amount +
1315 acct_begin_YTD_adj_amount)
1316 , currency_precision);
1317
1318 IF NOT insert_gl_interface (p_set_of_books_id
1319 , previous_code_combination_id
1320 , accounting_date
1321 , set_of_books_currency_code
1322 , acct_total_adj_amount
1323 , p_group_id
1324 , user_je_category_name
1325 , user_je_source_name) THEN
1326
1327 RAISE program_abort;
1328 END IF;
1329
1330 ------------------------------------------------------------
1331 -- Record the end period YTD and PTD amount with total --
1332 -- adjustment till the end period.The Infl.Adj.Report will--
1333 -- read this data. --
1334 ------------------------------------------------------------
1335
1336 IF NOT insert_axi_tmp (prevrec.r_ccid
1337 , p_inflation_adj_run_id
1338 , p_group_id
1339 , prevrec.r_period_name
1340 , prevrec.r_period_name
1341 , prevrec.r_ytd_balance
1342 , prevrec.r_ptd_balance
1343 , acct_total_adj_amount
1344 , fv_flag) THEN
1345
1346 RAISE program_abort;
1347 END IF;
1348
1349 ------------------------------------------------------------
1350 -- Add the current account adjusted amount to the REI --
1351 -- total. --
1352 ------------------------------------------------------------
1353 balancing_segment_tot_amount := balancing_segment_tot_amount +
1354 acct_total_adj_amount * (-1);
1355
1356 ------------------------------------------------------------
1357 -- Reset account totals. --
1358 ------------------------------------------------------------
1359 acct_period_adj_amount := 0;
1360 acct_total_adj_amount := 0;
1361
1362 previous_code_combination_id := c_code_combination_id;
1363 fv_flag := 'N';
1364
1365
1366 consider_YTD_amount := 'Y';
1367 acct_begin_YTD_adj_amount := 0;
1368
1369 END IF;
1370
1371 ------------------------------------------------------------
1372 -- If the balancing segment changes then the inflation --
1373 -- adjustment gain/loss account should also change. --
1374 ------------------------------------------------------------
1375 IF NOT same_value (c_balancing_segment
1376 ,previous_balancing_segment) THEN
1377
1378 ------------------------------------------------------------
1379 -- Complete the journal entry. --
1380 ------------------------------------------------------------
1381 IF NOT get_infl_adj_gla (infl_adjust_gain_loss_ccid
1382 , balancing_segment
1383 , previous_balancing_segment
1384 , infl_adj_gla) THEN
1385
1386 RAISE program_abort;
1387 END IF;
1388
1389 IF NOT insert_gl_interface (p_set_of_books_id
1390 , infl_adj_gla
1391 , accounting_date
1392 , set_of_books_currency_code
1393 , balancing_segment_tot_amount
1394 , p_group_id
1395 , user_je_category_name
1396 , user_je_source_name) THEN
1397
1398 RAISE program_abort;
1399 END IF;
1400
1401 ------------------------------------------------------------
1402 -- Reset balancing segment totals. --
1403 ------------------------------------------------------------
1404 balancing_segment_tot_amount := 0;
1405 previous_balancing_segment := c_balancing_segment;
1406
1407 END IF;
1408
1409 ------------------------------------------------------------
1410 -- Calculate inflation adj. amt.on Ytd balance of begining--
1411 -- period. --
1412 ------------------------------------------------------------
1413 IF consider_YTD_amount = 'Y' THEN
1414 IF NOT get_previous_period( period_set_name
1415 ,p_adjust_from_period
1416 ,YTD_prev_period_name) THEN
1417
1418 RAISE program_abort;
1419
1420 END IF;
1421 IF NOT get_adjustment_rate (p_infl_adj_index_id
1422 , p_set_of_books_id
1423 , period_set_name
1424 , YTD_prev_period_name
1425 , infl_adjustment_rate ) THEN
1426 RAISE program_abort;
1427
1428 END IF;
1429
1430 acct_begin_YTD_adj_amount := c_ytd_balance
1431 * infl_adjustment_rate;
1432
1433 END IF;
1434
1435
1436 consider_YTD_amount := 'N';
1437
1438
1439 ------------------------------------------------------------
1440 -- Compute FV adjusted amount for those JE entered in the --
1441 -- current account period. --
1442 ------------------------------------------------------------
1443 IF NOT get_FV_adjustment (p_set_of_books_id
1444 , p_infl_adj_index_id
1445 , period_set_name
1446 , c_code_combination_id
1447 , c_period_name
1448 , fv_adjustment_amount
1449 , fv_period_amount) THEN
1450
1451 RAISE program_abort;
1452 END IF;
1453
1454 IF fv_adjustment_amount <> 0 THEN
1455 fv_flag := 'Y';
1456 END IF;
1457
1458 ------------------------------------------------------------
1459 -- Get the rate to perform the adjustment. --
1460 ------------------------------------------------------------
1461 IF NOT get_adjustment_rate (p_infl_adj_index_id
1462 , p_set_of_books_id
1463 , period_set_name
1464 , c_period_name
1465 , infl_adjustment_rate ) THEN
1466 RAISE program_abort;
1467
1468 END IF;
1469
1470 ------------------------------------------------------------
1471 -- Compute the adjustment amount. --
1472 ------------------------------------------------------------
1473 acct_period_adj_amount := ((c_ptd_balance - fv_period_amount) *
1474 infl_adjustment_rate)
1475 + fv_adjustment_amount;
1476 acct_total_adj_amount := acct_total_adj_amount
1477 + acct_period_adj_amount;
1478
1479 fv_adjustment_amount :=0;
1480 fv_period_amount :=0;
1481 ------------------------------------------------------------
1482 -- Store previous 'Main Cursor' values in 'prevrec' record-
1483 ------------------------------------------------------------
1484
1485 prevrec.r_ccid :=c_code_combination_id;
1486 prevrec.r_bal_segment :=c_balancing_segment;
1487 prevrec.r_ytd_balance :=c_ytd_balance;
1488 prevrec.r_ptd_balance :=c_ptd_balance;
1489 prevrec.r_period_name :=c_period_name;
1490
1491 END LOOP;
1492
1493 ------------------------------------------------------------
1494 -- If no record to adjust then return from procedure. --
1495 ------------------------------------------------------------
1496
1497 IF zero_main_records = 'YES' THEN
1498 dbms_sql.close_cursor (main_cursor);
1499 COMMIT;
1500 RETURN 0;
1501 END IF;
1502
1503
1504 ------------------------------------------------------------
1505 -- Insert the last JE's last account. --
1506 ------------------------------------------------------------
1507 ------------------------------------------------------------
1508 -- Rounding before inserting in GL_INTERFACE and before --
1509 -- adding to the balancing segment total avoids unbalanced--
1510 -- journal entries. --
1511 ------------------------------------------------------------
1512 acct_total_adj_amount := round ((acct_total_adj_amount
1513 + acct_begin_YTD_adj_amount)
1514 , currency_precision);
1515
1516 IF NOT insert_gl_interface (p_set_of_books_id
1517 , previous_code_combination_id
1518 , accounting_date
1519 , set_of_books_currency_code
1520 , acct_total_adj_amount
1521 , p_group_id
1522 , user_je_category_name
1523 , user_je_source_name) THEN
1524
1525 RAISE program_abort;
1526 END IF;
1527
1528 IF NOT insert_axi_tmp (prevrec.r_ccid
1529 , p_inflation_adj_run_id
1530 , p_group_id
1531 , prevrec.r_period_name
1532 , prevrec.r_period_name
1533 , prevrec.r_ytd_balance
1534 , prevrec.r_ptd_balance
1535 , acct_total_adj_amount
1536 , fv_flag) THEN
1537
1538 RAISE program_abort;
1539 END IF;
1540
1541
1542 ------------------------------------------------------------
1543 -- Add the current account adjusted amount to the REI --
1544 -- total. --
1545 ------------------------------------------------------------
1546 balancing_segment_tot_amount := balancing_segment_tot_amount +
1547 acct_total_adj_amount * (-1);
1548
1549 ------------------------------------------------------------
1550 -- Insert the REI account for the last balancing segment --
1551 ------------------------------------------------------------
1552 IF NOT get_infl_adj_gla (infl_adjust_gain_loss_ccid
1553 , balancing_segment
1554 , previous_balancing_segment
1555 , infl_adj_gla) THEN
1556
1557 RAISE program_abort;
1558
1559 END IF;
1560
1561 IF NOT insert_gl_interface (p_set_of_books_id
1562 , infl_adj_gla
1563 , accounting_date
1564 , set_of_books_currency_code
1565 , balancing_segment_tot_amount
1566 , p_group_id
1567 , user_je_category_name
1568 , user_je_source_name) THEN
1569
1570 RAISE program_abort;
1571 END IF;
1572
1573 dbms_sql.close_cursor (main_cursor);
1574
1575 COMMIT;
1576
1577 RETURN 0;
1578
1579
1580 EXCEPTION
1581 WHEN program_abort THEN
1582 ROLLBACK;
1583 RETURN 1;
1584
1585 END inflation_adjustment;
1586
1587 END jl_zz_gl_infl_adj_pkg;