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