[Home] [Help]
PACKAGE BODY: APPS.IGI_BUD
Source
1 PACKAGE BODY IGI_BUD AS
2 -- $Header: igibudab.pls 120.5.12000000.2 2007/08/01 08:49:58 pshivara ship $
3
4 /* ===============================================================
5 This function checks is a column is numeric or not
6
7 Input : P_CHAR The entered column */
8
9 FUNCTION is_number(P_CHAR VARCHAR2) RETURN NUMBER IS
10 l_char VARCHAR2(1);
11 BEGIN
12 SELECT 'x'
13 INTO l_char
14 FROM sys.dual
15 WHERE TO_CHAR(TO_NUMBER(p_char)) = p_char;
16
17 RETURN 1;
18
19 EXCEPTION
20 WHEN VALUE_ERROR THEN
21 RETURN -1;
22
23 WHEN OTHERS THEN
24 RETURN 0;
25 END is_number;
26
27 /* =======================================================================
28 This Function returns the profiled budget amount for the
29 requested period. It will first calculate the period_ratio,
30 total_ratio and the max_period_number.
31
32 Input : p_annual_amount The entered annual amount
33 p_period_number The target period number
34 p_start_period The entered start period number
35 p_profile_code The entered profile code.
36 p_set_of_books_id The entered set of books id.
37 p_max_period_number Maximum period number.
38
39 Output: period_amount The amount for the target period */
40
41 FUNCTION bud_period_amount
42 ( p_Annual_Amount NUMBER
43 , p_Period_Number NUMBER
44 , p_Start_Period NUMBER
45 , p_Profile_Code VARCHAR2
46 , p_Set_Of_Books_Id NUMBER
47 , p_Max_Period_Number NUMBER
48 )
49 RETURN NUMBER IS
50
51 CURSOR cPeriodRatio
52 ( p_first_period NUMBER
53 , p_last_period NUMBER
54 ) IS
55 SELECT period_ratio
56 FROM igi_bud_profile_periods
57 WHERE profile_code = p_profile_code
58 AND set_of_books_id = p_Set_Of_Books_Id
59 AND period_number BETWEEN
60 p_first_period AND p_last_period
61 AND period_ratio <> 0;
62
63 l_Max_Period_Number NUMBER;
64 l_Total_Ratio NUMBER;
65 l_Profile_Amount NUMBER := 0;
66
67 BEGIN
68
69 l_Max_Period_Number := p_Max_Period_Number;
70 --
71 -- Validate p_Max_Period_Number and sum period ratios
72 --
73 SELECT max(period_number)
74 , sum(period_ratio)
75 INTO l_Max_Period_Number
76 , l_Total_Ratio
77 FROM igi_bud_profile_periods
78 WHERE profile_code = p_Profile_Code
79 AND set_of_books_id = p_Set_Of_Books_Id
80 AND period_number BETWEEN p_Start_Period
81 AND decode(p_Max_Period_Number
82 , 0, period_number
83 , p_Max_Period_Number
84 )
85 AND period_ratio <> 0;
86
87 l_Total_Ratio:=nvl(l_Total_ratio,1);
88 l_Max_Period_Number:=nvl(l_Max_Period_Number,p_Start_Period);
89
90 IF p_Annual_Amount = 0 THEN
91 RETURN (0);
92 ELSIF l_Total_Ratio = 0 THEN
93 RETURN (0);
94 ELSIF p_Period_Number NOT BETWEEN p_Start_Period
95 AND l_Max_Period_Number THEN
96 RETURN (0);
97 ELSIF p_Period_Number <> l_Max_Period_Number THEN
98 FOR PeriodRatio IN cPeriodRatio
99 ( p_Period_Number
100 , p_Period_Number) LOOP
101 l_Profile_Amount :=
102 (floor((abs(p_Annual_Amount)/l_Total_Ratio)*
103 PeriodRatio.period_ratio))*sign(p_Annual_Amount);
104 END LOOP;
105 RETURN l_Profile_Amount;
106 ELSE
107 FOR PeriodRatio IN cPeriodRatio
108 ( p_Start_Period
109 , l_Max_Period_Number-1)
110 LOOP
111 l_Profile_Amount := l_Profile_Amount +
112 (floor((abs(p_Annual_Amount)/l_Total_Ratio)*
113 PeriodRatio.period_ratio))*sign(p_Annual_Amount);
114 END LOOP;
115 RETURN (p_Annual_Amount-l_Profile_Amount);
116 END IF;
117
118 EXCEPTION
119 WHEN OTHERS THEN
120 RETURN (0);
121 END;
122
123 /* =======================================================================
124 This function returns true if profile is still valid and false
125 if profile does not exist or is out NOCOPY of date.
126 Input: p_set_of_books_id
127 p_profile_code
128 Return: boolean */
129
130 FUNCTION bud_profile_valid
131 ( p_set_of_books_id NUMBER
132 , p_profile_code VARCHAR2
133 )
134 RETURN boolean IS
135
136 x NUMBER;
137 BEGIN
138 SELECT 1
139 INTO x
140 FROM igi_bud_profile_codes pc
141 WHERE pc.set_of_books_id = p_set_of_books_id
142 AND pc.profile_code = p_profile_code
143 AND nvl(pc.start_date_active, sysdate-1) <= sysdate
144 AND nvl(pc.end_date_active, sysdate+1) > sysdate;
145
146 RETURN true;
147
148 EXCEPTION
149 WHEN OTHERS THEN
150 RETURN false;
151 END;
152
153 /* ========================================================================
154 This Procedure creates entries in IGI_BUD_JOURNAL_PERIODS and
155 GL_INTERFACE for each balanced entry into IGI_BUD_JOURNAL_LINES.
156 Inputs: Batch_ID The Batch ID of the line.
157 Header_ID The Header ID of the line.
158 Line_Number The Line Number of the line.
159 SOB_ID The Set Of Books ID
160 Profile_Code The Required Profile Code.
161 Start_Period The Required Start Period.
162 Entered_DR The Entered Debit Amount.
163 Entered_CR The Entered Credit Amount. */
164
165 PROCEDURE bud_profile_insert
166 ( p_sob_id NUMBER
167 , p_batch_id NUMBER
168 , p_header_id NUMBER
169 , p_line_number NUMBER
170 , p_cc_id NUMBER
171 , p_profile_code VARCHAR2
172 , p_start_period VARCHAR2
173 , p_entered_dr NUMBER
174 , p_entered_cr NUMBER
175 , p_description VARCHAR2
176 , p_reason_code VARCHAR2
177 , p_recurring VARCHAR2
178 , p_effect VARCHAR2
179 , p_next_year_budget NUMBER
180 )
181 IS
182 p_period_amount NUMBER;
183 p_period_nyb NUMBER;
184 p_total_ratio NUMBER;
185 p_start_period_number NUMBER;
186 p_max_period_number NUMBER;
187 p_period_name VARCHAR2(30);
188 p_period_year NUMBER;
189 p_period_set_name VARCHAR2(30);
190 p_period_type VARCHAR2(30);
191
192 CURSOR periods IS
193 SELECT period_number,period_ratio
194 FROM IGI_BUD_PROFILE_PERIODS jupp
195 WHERE jupp.PROFILE_CODE = p_profile_code
196 AND jupp.SET_OF_BOOKS_ID = p_sob_id;
197
198 CURSOR totals_nyb IS
199 SELECT sum(nvl(jupp.period_ratio,0)) total
200 , max(nvl(jubjl.period_number,0))
201 FROM IGI_BUD_PROFILE_PERIODS jupp
202 , IGI_BUD_JOURNAL_PERIODS jubjl
203 WHERE jupp.PROFILE_CODE = p_profile_code
204 AND jupp.SET_OF_BOOKS_ID = p_sob_id
205 AND jubjl.BE_BATCH_ID = p_batch_id
206 AND jubjl.BE_HEADER_ID = p_header_id
207 AND jubjl.BE_LINE_NUM = p_line_number
208 AND jupp.PERIOD_NUMBER = jubjl.PERIOD_NUMBER;
209
210 CURSOR periods_nyb IS
211 SELECT jubjl.period_number
212 , period_ratio
213 FROM IGI_BUD_PROFILE_PERIODS jupp
214 , IGI_BUD_JOURNAL_PERIODS jubjl
215 WHERE jupp.PROFILE_CODE = p_profile_code
216 AND jupp.SET_OF_BOOKS_ID = p_sob_id
217 AND jupp.PERIOD_NUMBER = jubjl.PERIOD_NUMBER
218 AND jubjl.BE_BATCH_ID = p_batch_id
219 AND jubjl.BE_HEADER_ID = p_header_id
220 AND jubjl.BE_LINE_NUM = p_line_number;
221
222 /* Bug 1979303 sekhar 13-sep-01
223 added cursor to get the user je source name */
224 CURSOR get_user_je_source IS
225 select user_je_source_name
226 from gl_je_sources
227 where je_source_name = 'IGIGBMJL'
228 and language = userenv('LANG');
229
230 l_total number(24,2) := 0;
231 l_max number := 0;
232 l_amount number :=0;
233 l_nyb_amt number :=0;
234
235 /* Bug 1979303 sekhar 13-sep-01
236 added following variable for user_je_source name */
237 l_user_je_source_name varchar2(25);
238
239 BEGIN
240 SELECT gp.PERIOD_NUM
241 , gp.PERIOD_YEAR
242 , gsob.PERIOD_SET_NAME
243 , gsob.ACCOUNTED_PERIOD_TYPE
244 INTO p_start_period_number
245 , p_period_year
246 , p_period_set_name
247 , p_period_type
248 FROM GL_PERIODS gp
249 , GL_SETS_OF_BOOKS gsob
250 WHERE gsob.SET_OF_BOOKS_ID = p_sob_id
251 AND gp.PERIOD_SET_NAME = gsob.PERIOD_SET_NAME
252 AND gp.PERIOD_NAME = p_start_period;
253
254 FOR period IN periods LOOP
255 SELECT gp.PERIOD_NAME
256 INTO p_period_name
257 FROM GL_PERIODS gp
258 WHERE gp.PERIOD_SET_NAME = p_period_set_name
259 AND gp.PERIOD_YEAR = p_period_year
260 AND gp.PERIOD_TYPE = p_period_type
261 AND gp.PERIOD_NUM = period.period_number;
262
263 p_period_amount := IGI_BUD.bud_period_amount
264 ( NVL(p_entered_dr,0) - NVL(p_entered_cr,0)
265 , period.period_number
266 , p_start_period_number
267 , p_profile_code
268 , p_sob_id
269 , 0
270 );
271 /*
272 p_period_nyb := IGI_BUD.bud_period_amount
273 ( nvl(p_next_year_budget,0)
274 , period.period_number
275 , p_start_period_number
276 , p_profile_code
277 , p_sob_id
278 ,0
279 );
280 */
281
282 IF p_period_amount <> 0 THEN
283 INSERT INTO IGI_BUD_JOURNAL_PERIODS
284 ( BE_BATCH_ID
285 , BE_HEADER_ID
286 , BE_LINE_NUM
287 , PERIOD_NUMBER
288 , PERIOD_YEAR
289 , PERIOD_NAME
290 , ENTERED_DR
291 , ENTERED_CR
292 , NEXT_YEAR_BUDGET
293 )
294 VALUES
295 ( p_batch_id
296 , p_header_id
297 , p_line_number
298 , period.period_number
299 , p_period_year
300 , p_period_name
301 , DECODE( SIGN(p_period_amount), '1',ABS(p_period_amount),NULL)
302 , DECODE( SIGN(p_period_amount),'-1',ABS(p_period_amount),NULL)
303 , NULL
304 );
305
306 END IF;
307 END LOOP;
308
309 OPEN totals_nyb;
310 FETCH totals_nyb INTO l_total, l_max;
311 CLOSE totals_nyb;
312
313 IF l_total > 0 and l_max > 0 THEN
314 l_amount := 0;
315 FOR nyb IN periods_nyb
316 LOOP
317 IF nyb.period_number <> l_max THEN
318 l_nyb_amt := 0;
319 l_nyb_amt := floor(( abs(p_next_year_budget)/ l_total)
320 * nyb.period_ratio)
321 * sign(p_next_year_budget);
322 l_amount := l_amount + l_nyb_amt;
323
324 UPDATE IGI_BUD_JOURNAL_PERIODS
325 SET NEXT_YEAR_BUDGET = l_nyb_amt
326 WHERE PERIOD_NUMBER = nyb.period_number
327 AND BE_BATCH_ID = p_batch_id
328 AND BE_HEADER_ID = p_header_id
329 AND BE_LINE_NUM = p_line_number;
330 ELSE
331 UPDATE IGI_BUD_JOURNAL_PERIODS
332 SET NEXT_YEAR_BUDGET =
333 p_next_year_budget - l_amount
334 WHERE PERIOD_NUMBER = nyb.period_number
335 AND BE_BATCH_ID = p_batch_id
336 AND BE_HEADER_ID = p_header_id
337 AND BE_LINE_NUM = p_line_number;
338 END IF;
339 END LOOP;
340 END IF;
341 /* bug 1979303 sekhar
342 Modified and correct parameter is passed */
343
344 OPEN get_user_je_source;
345 FETCH get_user_je_source INTO l_user_je_source_name;
346 CLOSE get_user_je_source;
347
348 INSERT INTO GL_INTERFACE
349 ( STATUS
350 , CREATED_BY
351 , DATE_CREATED
352 , GROUP_ID
353 , SET_OF_BOOKS_ID
354 , ACTUAL_FLAG
355 , USER_JE_CATEGORY_NAME
356 , USER_JE_SOURCE_NAME
357 , BUDGET_VERSION_ID
358 , CURRENCY_CODE
359 , ACCOUNTING_DATE
360 , CODE_COMBINATION_ID
361 , ENTERED_CR
362 , ENTERED_DR
363 , PERIOD_NAME
364 , REFERENCE1
365 , REFERENCE2
366 , REFERENCE4
367 , REFERENCE5
368 , REFERENCE7
369 , REFERENCE10
370 , REFERENCE21
371 , REFERENCE22
372 , REFERENCE23
373 , REFERENCE24
374 , REFERENCE25
375 , REFERENCE26
376 , REFERENCE27
377 , REFERENCE28
378 , REFERENCE29
379 , REFERENCE30
380 )
381 SELECT
382 'HOLDING'
383 , '-1'
384 , SYSDATE
385 , jubjb.BE_BATCH_ID
386 , jubjb.SET_OF_BOOKS_ID
387 , 'B'
388 , gjc.USER_JE_CATEGORY_NAME
389 , l_user_je_source_name
390 , jubjh.BUDGET_VERSION_ID
391 , jubjh.CURRENCY_CODE
392 , SYSDATE
393 , p_cc_id
394 , jubjp.ENTERED_CR
395 , jubjp.ENTERED_DR
396 , jubjp.PERIOD_NAME
397 , jubjb.NAME
398 , jubjb.NAME
399 , jubjh.NAME
400 , jubjh.DESCRIPTION
401 , 'N'
402 , p_description
403 , 'IGIGBUDPR'
404 , jubjb.BE_BATCH_ID
405 , p_profile_code
406 , p_reason_code
407 , p_start_period
408 , p_recurring
409 , p_effect
410 , jubjp.NEXT_YEAR_BUDGET
411 , jubjh.BE_HEADER_ID
412 , p_line_number
413 FROM IGI_BUD_JOURNAL_BATCHES jubjb
414 , IGI_BUD_JOURNAL_HEADERS jubjh
415 , IGI_BUD_JOURNAL_PERIODS jubjp
416 , GL_JE_CATEGORIES gjc
417 WHERE jubjb.BE_BATCH_ID = p_batch_id
418 AND jubjh.BE_HEADER_ID = p_header_id
419 AND jubjp.BE_HEADER_ID = p_header_id
420 AND jubjp.BE_LINE_NUM = p_line_number
421 AND gjc.JE_CATEGORY_NAME = jubjh.JE_CATEGORY_NAME
422 --Start Bug 2885983 extra join to remove mjc
423 AND jubjh.be_header_id = jubjp.be_header_id;
424 --End Bug 2885983
425
426 END; -- bud_profile_insert
427
428 /* ===========================================================================
429 This function returns a select string which produces a comma seperated
430 key flexfield for a given key flexfield
431 Inputs: p_appl_short_name The application short name (eg SQLGL)
432 p_id_flex_code The flex code (eg GL#)
433 p_if_flex_num The flex num (eg 101)
434 p_table_alias Alias for table
435 Output: r_where_list */
436
437 FUNCTION flexsql_select
438 ( p_appl_short_name VARCHAR2
439 , p_id_flex_code VARCHAR2
440 , p_id_flex_num NUMBER
441 , p_table_alias VARCHAR2
442 )
443 RETURN VARCHAR2
444 IS
445 where_list VARCHAR2(2000) :=null;
446 r_where_list VARCHAR2(2000) :=null;
447
448 CURSOR segments IS
449 SELECT fs.application_column_name
450 FROM FND_ID_FLEX_SEGMENTS fs
451 , FND_APPLICATION a
452 WHERE a.application_short_name = p_appl_short_name
453 AND fs.application_id = a.application_id
454 AND fs.ID_FLEX_CODE = p_id_flex_code
455 AND fs.ID_FLEX_NUM = p_id_flex_num
456 AND fs.ENABLED_FLAG = 'Y'
457 ORDER BY fs.SEGMENT_NUM;
458
459 BEGIN
460 FOR segment IN segments LOOP
461 SELECT decode(r_where_list, null, null, ',')||
462 decode(p_table_alias,null,null,
463 p_table_alias||'.')||
464 segment.APPLICATION_COLUMN_NAME
465 INTO where_list
466 FROM dual;
467
468 r_where_list := r_where_list||where_list;
469 END LOOP;
470
471 RETURN (r_where_list);
472 END; -- Of flexsql_select
473
474 /* =========================================================================
475 This function returns a select string which produces a concatenated
476 key flexfield for a given key flexfield
477 Inputs: p_appl_short_name The application short name (eg SQLGL)
478 p_id_flex_code The flex code (eg GL#)
479 p_if_flex_num The flex num (eg 101)
480 p_table_alias Alias for table
481 Output: r_where_list */
482
483 FUNCTION flexsql_concat
484 ( p_appl_short_name VARCHAR2
485 , p_id_flex_code VARCHAR2
486 , p_id_flex_num NUMBER
487 , p_table_alias VARCHAR2
488 )
489 RETURN VARCHAR2
490 IS
491 where_list VARCHAR2(2000) :=null;
492 r_where_list VARCHAR2(2000) :=null;
493
494 CURSOR segments IS
495 SELECT fs.application_column_name
496 , str.concatenated_segment_delimiter delim
497 FROM FND_ID_FLEX_SEGMENTS fs
498 , FND_ID_FLEX_STRUCTURES str
499 , FND_APPLICATION a
500 WHERE a.application_short_name = p_appl_short_name
501 AND fs.application_id = a.application_id
502 AND fs.ID_FLEX_CODE = p_id_flex_code
503 AND fs.ID_FLEX_NUM = p_id_flex_num
504 AND fs.ENABLED_FLAG = 'Y'
505 AND str.application_id = fs.application_id
506 AND str.id_flex_code = fs.id_flex_code
507 AND str.id_flex_num = fs.id_flex_num
508 ORDER BY fs.SEGMENT_NUM;
509
510 BEGIN
511 FOR segment IN segments LOOP
512 SELECT decode(r_where_list, null, null,
513 '||'''||segment.delim||'''||')||
514 decode(p_table_alias,null,null,
515 p_table_alias||'.')||
516 segment.APPLICATION_COLUMN_NAME
517 INTO where_list
518 FROM dual;
519
520 r_where_list := r_where_list||where_list;
521 END LOOP;
522 RETURN (r_where_list);
523 END; -- Of flexsql_concat
524
525 /* ===========================================================================
526 This function returns a where clause (beginning AND ... ) for a
527 given key flexfield for use between low/high range of segments
528 eg: SEGMENT1 BETWEEN SEGMENT1_LOW AND SEGMENT1_HIGH
529 Inputs: p_appl_short_name The application short name (eg SQLGL)
530 p_id_flex_code The flex code (eg GL#)
531 p_if_flex_num The flex num (eg 101)
532 p_single_table_alias Alias for non-range table
533 p_range_table_alias Alias for range table
534 Output: r_where_list */
535
536 FUNCTION flexsql_range
537 ( p_appl_short_name VARCHAR2
538 , p_id_flex_code VARCHAR2
539 , p_id_flex_num NUMBER
540 , p_single_table_alias VARCHAR2
541 , p_range_table_alias VARCHAR2
542 , p_not_between VARCHAR2
543 )
544 RETURN VARCHAR2
545 IS
546 where_list VARCHAR2(2000) :=null;
547 r_where_list VARCHAR2(2000) :=null;
548
549 CURSOR segments IS
550 SELECT fs.application_column_name
551 FROM FND_ID_FLEX_SEGMENTS fs
552 , FND_APPLICATION a
553 WHERE a.application_short_name = p_appl_short_name
554 AND fs.application_id = a.application_id
555 AND fs.ID_FLEX_CODE = p_id_flex_code
556 AND fs.ID_FLEX_NUM = p_id_flex_num
557 AND fs.ENABLED_FLAG = 'Y'
558 ORDER BY fs.SEGMENT_NUM;
559
560 BEGIN
561 FOR segment IN segments LOOP
562 SELECT decode(r_where_list, null, null, ' AND ')||
563 decode(p_single_table_alias,null,null,
564 p_single_table_alias||'.')||
565 segment.APPLICATION_COLUMN_NAME||
566 ' '||p_not_between ||' BETWEEN '||
567 decode(p_range_table_alias,null,null,
568 p_range_table_alias||'.')||
569 segment.APPLICATION_COLUMN_NAME||'_LOW AND '||
570 decode(p_range_table_alias,null,null,
571 p_range_table_alias||'.')||
572 segment.APPLICATION_COLUMN_NAME||'_HIGH'
573 INTO where_list
574 FROM dual;
575
576 r_where_list := r_where_list||where_list;
577 END LOOP;
578 RETURN (r_where_list);
579 END; -- Of flexsql_range
580
581 /* ==================================================================
582 This proceedure updates or inserts into igi_bud_profile_defaults
583 Parameters: Valid Code Combination ID
584 Valid Set of Books ID
585 Valid Profile Code */
586
587 PROCEDURE bud_profile_default
588 ( p_code_combination_id NUMBER
589 , p_set_of_books_id NUMBER
590 , p_new_profile_code VARCHAR2
591 )
592 IS
593 err_msg VARCHAR2(240);
594 BEGIN
595 IF p_new_profile_code is NOT NULL THEN
596 UPDATE igi_bud_profile_defaults
597 SET latest_profile_code = p_new_profile_code
598 WHERE code_combination_id = p_code_combination_id
599 AND set_of_books_id = p_set_of_books_id;
600
601 IF SQL%NOTFOUND THEN -- No row to update so
602 INSERT INTO igi_bud_profile_defaults
603 ( code_combination_id
604 , set_of_books_id
605 , primary_profile_code
606 , latest_profile_code
607 , creation_date
608 , created_by
609 , last_update_date
610 , last_updated_by
611 , last_update_login
612 )
613 VALUES
614 ( p_code_combination_id
615 , p_set_of_books_id
616 , p_new_profile_code
617 , p_new_profile_code
618 , sysdate
619 , -1
620 , sysdate
621 , -1
622 , -1
623 );
624 END IF;
625 END IF;
626
627 EXCEPTION
628 WHEN OTHERS THEN
629 err_msg := substr(SQLERRM, 1, 240);
630 --Bug 3199481 (start)
631 If (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) then
632 FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_UNEXP_ERROR');
633 FND_MESSAGE.SET_TOKEN('CODE', sqlcode);
634 FND_MESSAGE.SET_TOKEN('MSG', sqlerrm);
635 FND_LOG.MESSAGE(fnd_log.level_unexpected,'igi.plsql.igi_bud.bud_profile_default.Msg1',TRUE);
636 End if;
637 --Bug 3199481 (end)
638 raise_application_error (-20000, err_msg);
639
640 END; -- Of bud_profile_default
641 /* =============================================================
642 This proceedure updates or inserts igi_bud_ny_balances
643 Parameters: JE_HEADER_ID of Posted Budget Journal */
644
645 PROCEDURE bud_next_year_budget
646 ( p_je_header_id NUMBER
647 , p_set_of_books_id NUMBER
648 , p_budget_version_id NUMBER
649 , p_currency_code VARCHAR2
650 , p_period_name VARCHAR2
651 )
652 IS
653 p_code_combination_id NUMBER;
654
655 CURSOR lines IS
656 SELECT JE_LINE_NUM
657 , CODE_COMBINATION_ID
658 , REFERENCE_3
659 FROM GL_JE_LINES
660 WHERE JE_HEADER_ID = p_je_header_id;
661
662 l_line_reference_3 gl_je_lines.reference_3%type;
663 BEGIN
664 FOR line IN lines LOOP
665 BEGIN
666 INSERT INTO IGI_BUD_NY_BALANCES
667 ( SET_OF_BOOKS_ID
668 , CODE_COMBINATION_ID
669 , BUDGET_VERSION_ID
670 , PERIOD_NAME
671 , CURRENCY_CODE
672 , NEXT_YEAR_BUDGET)
673 SELECT
674 p_set_of_books_id
675 , gjl.CODE_COMBINATION_ID
676 , p_budget_version_id
677 , p_period_name
678 , p_currency_code
679 , NVL(gjl.REFERENCE_8,0)
680 FROM GL_JE_LINES gjl
681 WHERE gjl.JE_HEADER_ID = p_je_header_id
682 AND gjl.JE_LINE_NUM = line.JE_LINE_NUM
683 --
684 -- 01-NOV-00 EGARRETT Start(1)
685 -- replaced translate with is_number function
686 AND is_number(NVL(gjl.reference_8,0)) = 1;
687 /* AND translate(gjl.REFERENCE_8,
688 '-0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz\|*!"#$%^&*()_+. '
689 ,'-0123456789') = gjl.REFERENCE_8
690 AND ( (gjl.REFERENCE_8 like '-%' and
691 instr(gjl.REFERENCE_8,'-') <> 0)
692 OR ( instr(gjl.REFERENCE_8,'-') = 0)); */
693 -- 01-NOV-00 EGARRETT End(1)
694
695 EXCEPTION
696 WHEN dup_val_on_index THEN
697 SELECT CODE_COMBINATION_ID
698 INTO p_code_combination_id
699 FROM GL_JE_LINES
700 WHERE JE_HEADER_ID = p_je_header_id
701 AND JE_LINE_NUM = line.JE_LINE_NUM;
702
703 UPDATE IGI_BUD_NY_BALANCES nyb
704 SET NEXT_YEAR_BUDGET =
705 (SELECT nyb.NEXT_YEAR_BUDGET +
706 NVL(gjl.REFERENCE_8,0)
707 FROM GL_JE_LINES gjl
708 WHERE gjl.JE_HEADER_ID = p_je_header_id
709 AND gjl.JE_LINE_NUM = line.JE_LINE_NUM
710 -- 01-NOV-00 EGARRETT Start(2)
711 AND is_number(NVL(gjl.reference_8,0)) = 1)
712 /* AND translate(gjl.REFERENCE_8,
713 '-0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz\|*!"#$%^&*()_+. '
714 ,'-0123456789') = gjl.REFERENCE_8
715 AND ( (gjl.REFERENCE_8 like '-%' and
716 instr(gjl.REFERENCE_8,'-') <> 0)
717 OR ( instr(gjl.REFERENCE_8,'-') = 0))) */
718 -- 01-NOV-00 EGARRETT End(2)
719 WHERE SET_OF_BOOKS_ID = p_set_of_books_id
720 AND BUDGET_VERSION_ID = p_budget_version_id
721 AND CURRENCY_CODE = p_currency_code
722 AND CODE_COMBINATION_ID = p_code_combination_id
723 AND PERIOD_NAME = p_period_name;
724
725 WHEN value_error THEN
726 --Bug 3199481 (Start)
727 If (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) then
728 FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_UNEXP_ERROR');
729 FND_MESSAGE.SET_TOKEN('CODE', sqlcode);
730 FND_MESSAGE.SET_TOKEN('MSG', sqlerrm);
731 FND_LOG.MESSAGE(fnd_log.level_unexpected,'igi.plsql.igi_bud.bud_next_year_budget.Msg1',TRUE);
732 End if;
733 --Bug 3199481 (End)
734 raise_application_error(-20002,'Value Error Occurred');
735 WHEN others THEN
736 --Bug 3199481 (Start)
737 If (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) then
738 FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_UNEXP_ERROR');
739 FND_MESSAGE.SET_TOKEN('CODE', sqlcode);
740 FND_MESSAGE.SET_TOKEN('MSG', sqlerrm);
741 FND_LOG.MESSAGE(fnd_log.level_unexpected,'igi.plsql.igi_bud.bud_next_year_budget.Msg2',TRUE);
742 End if;
743 --Bug 3199481 (End)
744 raise_application_error(-20003,SQLERRM);
745 END;
746
747 BEGIN
748 SELECT NULL
749 INTO l_line_reference_3
750 FROM sys.dual
751 WHERE line.reference_3 <> 'MANUAL'
752 AND line.reference_3 is not null
753 AND line.reference_3 not in (
754 SELECT profile_code
755 FROM igi_bud_profile_codes
756 WHERE set_of_books_id = p_set_of_books_id
757 AND sysdate >= nvl(start_date_Active,sysdate-1)
758 AND sysdate <= nvl(end_date_active,sysdate+1));
759
760 EXCEPTION
761 WHEN OTHERS THEN NULL;
762 END;
763
764 IF l_line_reference_3 <> 'MANUAL'
765 AND l_line_reference_3 IS NOT NULL THEN
766 IGI_BUD.bud_profile_default
767 ( line.CODE_COMBINATION_ID
768 , p_set_of_books_id
769 , line.REFERENCE_3);
770 END IF;
771 END LOOP;
772 END; -- of bud_next_year_budget
773 -- ==========================================================================
774 END; -- Of Budgeting Package Body Creation