[Home] [Help]
PACKAGE BODY: APPS.GL_FLEX_INSERT_PKG
Source
1 PACKAGE BODY gl_flex_insert_pkg AS
2 /* $Header: glffglib.pls 120.11.12000000.2 2007/01/23 23:17:26 djogg ship $ */
3
4 /* ------------------------------------------------------------------------- */
5 /* Function called just after code combination is inserted into */
6 /* GL code combinations table in the accounting flexfield. */
7 /* Returns TRUE if ok, or returns FALSE and sets FND_MESSAGE on error. */
8 /* ------------------------------------------------------------------------- */
9
10 --===========================FND_LOG.START=====================================
11 g_state_level NUMBER := FND_LOG.LEVEL_STATEMENT;
12 g_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
13 g_event_level NUMBER := FND_LOG.LEVEL_EVENT;
14 g_excep_level NUMBER := FND_LOG.LEVEL_EXCEPTION;
15 g_error_level NUMBER := FND_LOG.LEVEL_ERROR;
16 g_unexp_level NUMBER := FND_LOG.LEVEL_UNEXPECTED;
17 g_path VARCHAR2(100) := 'psa.plsql.glffglib.gl_flex_insert_pkg.';
18 --===========================FND_LOG.END=======================================
19
20 g_segment_nvl_value CONSTANT VARCHAR2(30) := '-99$$!!';
21 -- Types :
22
23
24 -- SegValArray contains values for all the Segments
25
26 TYPE SegValArray IS TABLE OF VARCHAR2(25) INDEX BY BINARY_INTEGER;
27
28 -- TokNameArray contains names of all tokens
29
30 TYPE TokNameArray IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
31
32 -- TokValArray contains values for all tokens
33
34 TYPE TokValArray IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;
35
36 -- SegTypeArray contains entries for the Segment Types in the
37 -- Summary Templates
38
39 TYPE SegTypeArray IS TABLE OF VARCHAR2(25) INDEX BY BINARY_INTEGER;
40
41 -- SegRgrpArray contains the Rollup Groups for Segments
42
43 TYPE SegRgrpArray IS TABLE OF VARCHAR2(11) INDEX BY BINARY_INTEGER;
44
45 -- RgrpSrtArray contains the Rollup Groups sorted by Rollup Group Scores
46
47 TYPE RgrpSrtArray IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
48
49 -- RgrpIndArray contains the Segment Indices for the Sorted Rollup Group
50 -- Scores
51
52 TYPE RgrpIndArray IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
53
54 -- SegVsetArray contains the Value Set IDs for Segments
55
56 TYPE SegVsetArray IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
57
58 -- TabColArray contains Segment Names
59
60 TYPE TabColArray IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
61
62
63 -- Private Global Variables :
64
65
66 -- Flex Num for the Accounting Flexfield Structure
67
68 coaid gl_code_combinations.chart_of_accounts_id%TYPE;
69
70 -- User ID
71
72 user_id gl_code_combinations.last_updated_by%TYPE;
73
74 -- Responsibility ID
75
76 user_resp_id NUMBER;
77
78 -- Login ID (unique per signon)
79
80 login_id gl_budget_assignments.last_update_login%TYPE;
81
82 -- Segment Values for the Code Combination. Segment Values are stored in
83 -- an array. A row in the array identifies the corresponding Segment Value.
84 -- Thus, if a Code Combination has valid values for Segments 2, 4, 7, 11
85 -- and 9, the 2nd, 4th, 7th, 11th and 9th rows of the array will contain
86 -- the corresponding Segment Values
87
88 seg_val SegValArray;
89
90 -- Whether Detail Budgeting is allowed for the Code Combination
91
92 db_allowed_flag VARCHAR2(1);
93
94 -- Account Category for the Code Combination
95
96 acct_category VARCHAR2(1);
97
98 -- Dynamic Group ID
99
100 dyn_grp_id gl_dynamic_summ_combinations.dynamic_group_id%TYPE;
101
102 -- Whether GL has been installed
103
104 gl_installed VARCHAR2(15);
105
106 -- Whether Government Install
107
108 industry VARCHAR2(1);
109
110 -- Number of Active Segments in the Code Combination
111
112 num_active_segs NUMBER;
113
114 -- Cardinal Number of the Accounting Segment in the Code Combination
115
116 acct_seg_index NUMBER;
117
118 -- Minimum CCID. All new Parent Accounts will have CCIDs greater than the
119 -- minimum CCID
120
121 min_ccid gl_dynamic_summ_combinations.code_combination_id%TYPE;
122
123 -- Number of Budgetary Control Ledgers
124
125 num_bc_lgr NUMBER;
126
127 -- Number of Summary Templates
128
129 num_templates NUMBER;
130
131 -- Whether Parent Accounts have been created for this CCID
132
133 created_parent BOOLEAN;
134
135 -- Number of Message Tokens
136
137 no_msg_tokens NUMBER;
138
139 -- Message Token Name
140
141 msg_tok_names TokNameArray;
142
143 -- Message Token Value
144
145 msg_tok_val TokValArray;
146
147 -- For bug 3380377
148 -- check to see if ccid already exists
149
150 num_ccid NUMBER;
151
152 /* ----------------------------------------------------------------------- */
153 /* */
154 /* Private Function Definition */
155 /* */
156 /* ----------------------------------------------------------------------- */
157
158 FUNCTION glfcin RETURN BOOLEAN;
159
160
161 FUNCTION glfini(ccid IN NUMBER) RETURN BOOLEAN;
162
163
164 FUNCTION glfisi(val_set IN OUT NOCOPY SegVsetArray) RETURN BOOLEAN;
165
166
167 FUNCTION glfiba(ccid IN NUMBER) RETURN BOOLEAN;
168
169
170 FUNCTION glfcst(val_set IN SegVsetArray, ccid IN gl_code_combinations.code_combination_id%TYPE) RETURN BOOLEAN;
171
172
173 FUNCTION glfgdg RETURN BOOLEAN;
174
175
176 FUNCTION glfcrg(val_set IN SegVsetArray,
177 seg_type IN SegTypeArray,
178 rgroup IN OUT NOCOPY SegRgrpArray,
179 template_name IN VARCHAR2) RETURN BOOLEAN;
180
181
182
183
184
185 FUNCTION glfcpc(seg_type IN SegTypeArray,
186 rgroup IN SegRgrpArray,
187 rgroup_sorted IN RgrpSrtArray,
188 rgroup_ind IN RgrpIndArray,
189 val_set IN SegVsetArray,
190 template_id IN NUMBER,
191 lgr_id IN NUMBER) RETURN BOOLEAN;
192
193
194 FUNCTION glflst RETURN BOOLEAN;
195
196
197 FUNCTION glfaec RETURN BOOLEAN;
198
199
200 FUNCTION glfanc RETURN BOOLEAN;
201
202
203 FUNCTION glficc RETURN BOOLEAN;
204
205
206 FUNCTION glfmah(ccid IN NUMBER) RETURN BOOLEAN;
207
208
209 FUNCTION glgfdi(ccid IN NUMBER) RETURN BOOLEAN;
210
211
212 FUNCTION glfupd(ccid IN NUMBER) RETURN BOOLEAN;
213
214
215 PROCEDURE message_token(tokname IN VARCHAR2,
216 tokval IN VARCHAR2);
217
218
219 PROCEDURE add_message(appname IN VARCHAR2,
220 msgname IN VARCHAR2);
221
222
223 FUNCTION dsql_execute(sql_statement IN VARCHAR2) RETURN NUMBER;
224
225 PROCEDURE allocate_lock(lockname IN VARCHAR2,
226 lockhandle OUT NOCOPY VARCHAR2) IS
227 PRAGMA AUTONOMOUS_TRANSACTION; -- Bug 5074981
228 BEGIN
229 dbms_lock.allocate_unique(lockname, lockhandle);
230 END allocate_lock;
231
232 /* ----------------------------------------------------------------------- */
233 /* */
234 /* Main Routine for insertion of Foundation Flexfields. Returns TRUE */
235 /* if successful; otherwise, it returns FALSE. */
236 /* */
237 /* If Oracle General Ledger is not installed or the number of Ledgers */
238 /* with the Budgetary Control Option enabled is 0 or less, this */
239 /* function returns TRUE (successful). */
240 /* */
241 /* In case of failure, this routine will populate the global Message */
242 /* Stack using FND_MESSAGE. The calling routine will read the Message */
243 /* from the Stack. */
244 /* */
245 /* External Packages which are being invoked include : */
246 /* */
247 /* FND_GLOBAL */
248 /* FND_PROFILE */
249 /* FND_INSTALLATION */
250 /* FND_MESSAGE */
251 /* */
252 /* GL Tables which are being used include : */
253 /* */
254 /* GL_CODE_COMBINATIONS */
255 /* GL_LEDGERS */
256 /* GL_BUDGET_ASSIGNMENT_RANGES */
257 /* GL_BUDGET_ASSIGNMENTS */
258 /* GL_SUMMARY_TEMPLATES */
259 /* GL_DYNAMIC_SUMM_COMBINATIONS */
260 /* GL_ROLLUP_GROUP_SCORES */
261 /* GL_CONCURRENCY_CONTROL */
262 /* GL_ACCOUNT_HIERARCHIES */
263 /* */
264 /* AOL Tables which are being used include : */
265 /* */
266 /* FND_ID_FLEX_SEGMENTS */
267 /* FND_SEGMENT_ATTRIBUTE_VALUES */
268 /* FND_FLEX_HIERARCHIES */
269 /* FND_FLEX_VALUE_HIERARCHIES */
270 /* FND_FLEX_VALUES */
271 /* FND_SEG_RPT_ATTRIBUTES */
272 /* FND_FLEX_VALUE_SETS */
273 /* FND_TABLES */
274 /* FND_FLEX_VALIDATION_TABLES */
275 /* */
276 /* ----------------------------------------------------------------------- */
277
278
279 -- Called Routines :
280
281 -- glfini : Setup Global Variables
282
283 -- glfcin : Check if GL has been installed
284
285 -- glfisi : Retrieve Value Set IDs for the Segments in the Code Combination
286
287 -- glfiba : Insert into Budget Assignments table only if Segment Values fall
288 -- within any Account Ranges in the Budget Organization
289
290 -- glfcst : Loop through the Summary Templates and create Parent Accounts for
291 -- each template
292
293 -- glflst : Lock Summary Templates
294
295 -- glfaec : Find CCIDs for existing Code Combinations
296
297 -- glfanc : Assign new CCID to new Code Combinations
298
299 -- glficc : Insert newly created Parent Accounts into Code Combinations
300 -- Table
301
302 -- glfmah : Maintain Account Hierarchies
303
304 -- glgfdi : Maintain Reporting Attributes
305
306
307 -- Arguments :
308
309 -- ccid : Code Combination ID
310
311
312 FUNCTION fdfgli(ccid IN NUMBER) RETURN BOOLEAN IS
313
314 val_set SegVsetArray;
315
316 i BINARY_INTEGER;
317 lockhandle VARCHAR2(128);
318 retval INTEGER;
319 lock_flag BOOLEAN;
320 l_temp_var VARCHAR2(1);
321
322 -- ========================= FND LOG ===========================
323 l_full_path VARCHAR2(100) := g_path || 'fdfgli.';
324 -- ========================= FND LOG ===========================
325
326 BEGIN
327
328 -- ========================= FND LOG ===========================
329 psa_utils.debug_other_string(g_state_level,l_full_path,' START fdfgli ');
330 -- ========================= FND LOG ===========================
331
332 -- Initialize Global Variables
333
334 dyn_grp_id := -1;
335 gl_installed := 'HAVENT_CHECKED';
336 num_active_segs := 0;
337 num_bc_lgr := 0;
338 num_templates := 0;
339 created_parent := FALSE;
340 no_msg_tokens := 0;
341 num_ccid := 0;
342
343 -- ========================= FND LOG ===========================
344 psa_utils.debug_other_string(g_state_level,l_full_path,' Initializing Variables ');
345 psa_utils.debug_other_string(g_state_level,l_full_path,' dyn_grp_id -> ' || to_char(dyn_grp_id));
346 psa_utils.debug_other_string(g_state_level,l_full_path,' gl_installed -> ' || gl_installed);
347 psa_utils.debug_other_string(g_state_level,l_full_path,' num_active_segs -> ' || num_active_segs);
348 psa_utils.debug_other_string(g_state_level,l_full_path,' num_bc_lgr -> ' || num_bc_lgr);
349 psa_utils.debug_other_string(g_state_level,l_full_path,' num_templates -> ' || num_templates);
350 -- ========================= FND LOG ===========================
351
352 --select count(*)
353 --into num_ccid
354 --from gl_code_combinations
355 --where code_combination_id = ccid;
356
357 BEGIN
358 select 'Y' into l_temp_var
359 from dual
360 where not exists (select 'x'
361 from gl_account_hierarchies
362 where detail_code_combination_id = ccid)
363 and not exists (select 'x'
364 from gl_budget_assignments
365 where code_combination_id = ccid);
366
367 -- ========================= FND LOG ===========================
368 psa_utils.debug_other_string(g_state_level,l_full_path,' l_temp_var -> ' || l_temp_var);
369 -- ========================= FND LOG ===========================
370
371 EXCEPTION
372 WHEN NO_DATA_FOUND THEN
373 -- ========================= FND LOG ===========================
374 psa_utils.debug_other_string(g_state_level,l_full_path,' EXCEPTION WHEN NO_DATA_FOUND');
375 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN -> TRUE');
376 -- ========================= FND LOG ===========================
377 RETURN (true);
378 END;
379
380 -- For bug 3380377, check to see if ccid exists. If it already exists, just return TRUE
381 --if (num_ccid > 0) then
382 -- return(TRUE);
383 --end if;
384
385
386 -- ========================= FND LOG ===========================
387 psa_utils.debug_other_string(g_state_level,l_full_path,' Calling glfini -> ' || ccid);
388 -- ========================= FND LOG ===========================
389
390 -- Setup Global Variables
391
392 if not glfini(ccid) then
393 -- ========================= FND LOG ===========================
394 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN -> FALSE');
395 -- ========================= FND LOG ===========================
396 return(FALSE);
397 end if;
398
399
400 -- ========================= FND LOG ===========================
401 psa_utils.debug_other_string(g_state_level,l_full_path,' Calling glfcin ');
402 -- ========================= FND LOG ===========================
403
404 -- Check if GL has been installed
405
406 if not glfcin then
407 -- ========================= FND LOG ===========================
408 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN -> FALSE');
409 -- ========================= FND LOG ===========================
410 return(FALSE);
411 end if;
412
413
414 -- ========================= FND LOG ===========================
415 psa_utils.debug_other_string(g_state_level,l_full_path,' gl_installed -> ' || gl_installed);
416 -- ========================= FND LOG ===========================
417
418 -- If GL is not installed or number of set of books with the
419 -- budgetary control option enabled is zero or less then
420 -- return TRUE
421
422 if gl_installed = 'NOT_INSTALLED' then
423 -- ========================= FND LOG ===========================
424 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN -> TRUE');
425 -- ========================= FND LOG ===========================
426 return(TRUE);
427 end if;
428
429
430 -- ========================= FND LOG ===========================
431 psa_utils.debug_other_string(g_state_level,l_full_path,
432 ' Calling HR_GL_COST_CENTERS.create_org');
433 psa_utils.debug_other_string(g_state_level,l_full_path,
434 ' update company_cost_enter_org_id of GL_CODE_COMBINATIONS');
435 -- ========================= FND LOG ===========================
436
437 -- Call API to update company_cost_enter_org_id of GL_CODE_COMBINATIONS table..
438 -- We pass CCID as parameter to this procedure
439
440 HR_GL_COST_CENTERS.create_org(ccid);
441
442 -- ========================= FND LOG ===========================
443 psa_utils.debug_other_string(g_state_level,l_full_path,' num_bc_lgr -> ' || num_bc_lgr);
444 -- ========================= FND LOG ===========================
445
446 -- If no Budgetary Control then maintain Reporting Attributes
447 -- and exit
448
449 if num_bc_lgr = 0 then
450
451 -- ========================= FND LOG ===========================
452 psa_utils.debug_other_string(g_state_level,l_full_path,' Calling glgfdi -> '|| ccid);
453 -- ========================= FND LOG ===========================
454
455 if not glgfdi(ccid) then
456 -- ========================= FND LOG ===========================
457 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN -> FALSE');
458 -- ========================= FND LOG ===========================
459 return(FALSE);
460 else
461 -- ========================= FND LOG ===========================
462 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN -> TRUE');
463 -- ========================= FND LOG ===========================
464 return(TRUE);
465 end if;
466 end if;
467
468 -- ========================= FND LOG ===========================
469 psa_utils.debug_other_string(g_state_level,l_full_path,
470 ' Retrieve Value Set IDs for the Segments in the Code Combination ');
471 -- ========================= FND LOG ===========================
472
473 -- Retrieve Value Set IDs for the Segments in the Code Combination
474
475 for i in 1..30 loop
476 val_set(i) := null;
477 end loop;
478
479 -- ========================= FND LOG ===========================
480 psa_utils.debug_other_string(g_state_level,l_full_path,
481 ' Calling glfisi passing val_set ');
482 -- ========================= FND LOG ===========================
483
484 if not glfisi(val_set) then
485 -- ========================= FND LOG ===========================
486 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN -> FALSE');
487 -- ========================= FND LOG ===========================
488 return(FALSE);
489 end if;
490
491
492 -- ========================= FND LOG ===========================
493 psa_utils.debug_other_string(g_state_level,l_full_path,' db_allowed_flag -> ' || db_allowed_flag);
494 -- ========================= FND LOG ===========================
495
496 -- Maintain Budget Assignments only if Detail Budgeting is allowed for the
497 -- new Code Combination
498
499 if (db_allowed_flag = 'Y') then
500
501 -- ========================= FND LOG ===========================
502 psa_utils.debug_other_string(g_state_level,l_full_path,' Calling glfiba -> ' || ccid);
503 -- ========================= FND LOG ===========================
504
505 -- Insert into Budget Assignments table only if Segment Values fall
506 -- within any Account Ranges in the Budget Organization
507
508 if not glfiba(ccid) then
509 -- ========================= FND LOG ===========================
510 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN -> FALSE');
511 -- ========================= FND LOG ===========================
512 return(FALSE);
513 end if;
514
515 end if;
516
517 lock_flag := FALSE;
518
519 -- ========================= FND LOG ===========================
520 psa_utils.debug_other_string(g_state_level,l_full_path,' Doing the Locking');
521 -- ========================= FND LOG ===========================
522
523 -- A new share user name lock is added. It will be released at the end.
524 -- This ensures that when a new code combination is created, no GLSIMS
525 -- runs to incrementally update the hierarchies.
526
527 LOOP
528
529 if(lock_flag = TRUE) then
530 -- ========================= FND LOG ===========================
531 psa_utils.debug_other_string(g_state_level,l_full_path,' lock_flag - TRUE - EXIT');
532 -- ========================= FND LOG ===========================
533 exit;
534
535 end if;
536
537 allocate_lock('GL_BC_SUMMARY_TEMPLATES'||coaid, lockhandle); -- Bug 5074981
538 retval := dbms_lock.request(lockhandle,4,32767,FALSE);
539
540 -- ========================= FND LOG ===========================
541 psa_utils.debug_other_string(g_state_level,l_full_path,' retval -> ' || retval);
542 -- ========================= FND LOG ===========================
543
544 if(retval = 0 OR retval = 4)then
545 lock_flag := TRUE;
546 -- ========================= FND LOG ===========================
547 psa_utils.debug_other_string(g_state_level,l_full_path,' lock_flag -> TRUE');
548 -- ========================= FND LOG ===========================
549
550 elsif(retval = 1)then
551 -- ========================= FND LOG ===========================
552 psa_utils.debug_other_string(g_state_level,l_full_path,' Calling dbms_lock.sleep');
553 -- ========================= FND LOG ===========================
554 dbms_lock.sleep(15);
555
556 else
557 -- ========================= FND LOG ===========================
558 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN FALSE');
559 -- ========================= FND LOG ===========================
560 return(FALSE);
561
562 end if;
563
564 END LOOP;
565
566 -- ========================= FND LOG ===========================
567 psa_utils.debug_other_string(g_state_level,l_full_path,
568 ' Loop through the Summary Templates and create Parent Accounts');
569 psa_utils.debug_other_string(g_state_level,l_full_path,
570 ' Calling glfcst passing val_set ');
571 -- ========================= FND LOG ===========================
572
573 -- Loop through the Summary Templates and create Parent Accounts
574 -- for each template
575
576 if not glfcst(val_set, ccid) then
577 -- ========================= FND LOG ===========================
578 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN -> FALSE');
579 -- ========================= FND LOG ===========================
580 return(FALSE);
581 end if;
582
583 -- Now that parent accounts have been created for all the Summary
584 -- Templates, we
585 -- (1) lock the summary templates that have parent accounts created
586 -- (2) find ccids for existing parents
587 -- (3) assign new ccids to new parents
588 -- (4) insert the newly created parents into gl_code_combinations
589 -- (5) maintain gl_account_hierarchies
590
591 if created_parent then
592
593 -- ========================= FND LOG ===========================
594 psa_utils.debug_other_string(g_state_level,l_full_path,' Lock Summary Templates');
595 psa_utils.debug_other_string(g_state_level,l_full_path,' Calling glflst');
596 -- ========================= FND LOG ===========================
597
598 -- Lock Summary Templates
599
600 if not glflst then
601 -- ========================= FND LOG ===========================
602 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN -> FALSE');
603 -- ========================= FND LOG ===========================
604 return(FALSE);
605 end if;
606
607 -- ========================= FND LOG ===========================
608 psa_utils.debug_other_string(g_state_level,l_full_path,
609 ' Find CCIDs for existing Code Combinations');
610 psa_utils.debug_other_string(g_state_level,l_full_path,' Calling glfaec');
611 -- ========================= FND LOG ===========================
612
613 -- Find CCIDs for existing Code Combinations
614
615 if not glfaec then
616 -- ========================= FND LOG ===========================
617 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN -> FALSE');
618 -- ========================= FND LOG ===========================
619
620 return(FALSE);
621 end if;
622
623 -- ========================= FND LOG ===========================
624 psa_utils.debug_other_string(g_state_level,l_full_path,
625 ' Assign new CCIDs to new Code Combinations');
626 psa_utils.debug_other_string(g_state_level,l_full_path,' Calling glfanc');
627 -- ========================= FND LOG ===========================
628
629 -- Assign new CCIDs to new Code Combinations
630
631 if not glfanc then
632 -- ========================= FND LOG ===========================
633 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN -> FALSE');
634 -- ========================= FND LOG ===========================
635 return(FALSE);
636 end if;
637
638 -- ========================= FND LOG ===========================
639 psa_utils.debug_other_string(g_state_level,l_full_path,
640 ' Insert newly created Parent Accounts into Code Combinations table');
641 psa_utils.debug_other_string(g_state_level,l_full_path,' Calling glficc');
642 -- ========================= FND LOG ===========================
643
644 -- Insert newly created Parent Accounts into Code Combinations table
645
646 if not glficc then
647 -- ========================= FND LOG ===========================
648 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN -> FALSE');
649 -- ========================= FND LOG ===========================
650 return(FALSE);
651 end if;
652
653
654 -- Maintain Account Hierarchies
655
656 if not glfmah(ccid) then
657 -- ========================= FND LOG ===========================
658 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN -> FALSE');
659 -- ========================= FND LOG ===========================
660 return(FALSE);
661 end if;
662
663 end if;
664
665 -- ========================= FND LOG ===========================
666 psa_utils.debug_other_string(g_state_level,l_full_path,
667 ' Release the budgetary control locks');
668 -- ========================= FND LOG ===========================
669
670 -- Release the budgetary control user name lock after maintaining the
671 -- account hierarchies.
672
673 retval := dbms_lock.release(lockhandle);
674
675 -- ========================= FND LOG ===========================
676 psa_utils.debug_other_string(g_state_level,l_full_path,
677 ' retval -> ' || retval);
678 -- ========================= FND LOG ===========================
679
680 if(retval <> 0) then
681 -- ========================= FND LOG ===========================
682 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN -> FALSE');
683 -- ========================= FND LOG ===========================
684 return(FALSE);
685 end if;
686
687 -- ========================= FND LOG ===========================
688 psa_utils.debug_other_string(g_state_level,l_full_path,
689 ' Maintain Reporting Attributes');
690 psa_utils.debug_other_string(g_state_level,l_full_path,' Calling glgfdi -> ' || ccid);
691 -- ========================= FND LOG ===========================
692
693 -- Maintain Reporting Attributes
694
695 if not glgfdi(ccid) then
696 -- ========================= FND LOG ===========================
697 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN -> FALSE');
698 -- ========================= FND LOG ===========================
699 return(FALSE);
700 end if;
701
702 -- ========================= FND LOG ===========================
703 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN -> TRUE');
704 -- ========================= FND LOG ===========================
705 return(TRUE);
706
707 END FDFGLI;
708
709 /* ------------------------------------------------------------------------ */
710
711 -- Setup Global Variables
712
713
714 -- Called Routines :
715
716 -- FND_GLOBAL : Setup User ID, Login ID, Responsibility ID
717
718 -- message_token : Add Token and Value to the Message Token array
719
720 -- add_message : Define a Message Token with a Value and set the Message Name
721
722
723 -- Arguments :
724
725 -- ccid : Code Combination ID
726
727
728 FUNCTION glfini(ccid IN NUMBER) RETURN BOOLEAN IS
729
730 -- Maximum Length for this Dynamic SQL Statement is 463
731
732 sql_glcc VARCHAR2(700);
733 cur_glcc INTEGER;
734 ignore INTEGER;
735
736 i BINARY_INTEGER;
737
738 account_type gl_code_combinations.account_type%TYPE;
739 segment1 gl_code_combinations.segment1%TYPE;
740 segment2 gl_code_combinations.segment2%TYPE;
741 segment3 gl_code_combinations.segment3%TYPE;
742 segment4 gl_code_combinations.segment4%TYPE;
743 segment5 gl_code_combinations.segment5%TYPE;
744 segment6 gl_code_combinations.segment6%TYPE;
745 segment7 gl_code_combinations.segment7%TYPE;
746 segment8 gl_code_combinations.segment8%TYPE;
747 segment9 gl_code_combinations.segment9%TYPE;
748 segment10 gl_code_combinations.segment10%TYPE;
749 segment11 gl_code_combinations.segment11%TYPE;
750 segment12 gl_code_combinations.segment12%TYPE;
751 segment13 gl_code_combinations.segment13%TYPE;
752 segment14 gl_code_combinations.segment14%TYPE;
753 segment15 gl_code_combinations.segment15%TYPE;
754 segment16 gl_code_combinations.segment16%TYPE;
755 segment17 gl_code_combinations.segment17%TYPE;
756 segment18 gl_code_combinations.segment18%TYPE;
757 segment19 gl_code_combinations.segment19%TYPE;
758 segment20 gl_code_combinations.segment20%TYPE;
759 segment21 gl_code_combinations.segment21%TYPE;
760 segment22 gl_code_combinations.segment22%TYPE;
761 segment23 gl_code_combinations.segment23%TYPE;
762 segment24 gl_code_combinations.segment24%TYPE;
763 segment25 gl_code_combinations.segment25%TYPE;
764 segment26 gl_code_combinations.segment26%TYPE;
765 segment27 gl_code_combinations.segment27%TYPE;
766 segment28 gl_code_combinations.segment28%TYPE;
767 segment29 gl_code_combinations.segment29%TYPE;
768 segment30 gl_code_combinations.segment30%TYPE;
769
770 -- ========================= FND LOG ===========================
771 l_full_path VARCHAR2(100) := g_path || 'glfini.';
772 -- ========================= FND LOG ===========================
773
774 BEGIN
775
776 -- ========================= FND LOG ===========================
777 psa_utils.debug_other_string(g_state_level,l_full_path,' START glfini ');
778 -- ========================= FND LOG ===========================
779
780 -- Setup User ID
781 user_id := FND_GLOBAL.USER_ID;
782
783 -- ========================= FND LOG ===========================
784 psa_utils.debug_other_string(g_state_level,l_full_path,' user_id -> ' || user_id);
785 -- ========================= FND LOG ===========================
786
787 if user_id = -1 then
788 message_token('ROUTINE', 'FDFGLI');
789 add_message('FND', 'FLEXGL-CANNOT GET USERID');
790 -- goto return_invalid;
791 end if;
792
793 -- Setup Login ID
794 login_id := FND_GLOBAL.LOGIN_ID;
795
796 -- ========================= FND LOG ===========================
797 psa_utils.debug_other_string(g_state_level,l_full_path,' login_id -> ' || login_id);
798 -- ========================= FND LOG ===========================
799
800 if login_id = -1 then
801 message_token('ROUTINE', 'FDFGLI');
802 add_message('FND', 'FLEXGL-CANNOT GET LOGIN ID');
803 -- goto return_invalid;
804 end if;
805
806 -- ========================= FND LOG ===========================
807 psa_utils.debug_other_string(g_state_level,l_full_path,' user_resp_id -> ' || user_resp_id);
808 -- ========================= FND LOG ===========================
809
810 -- Setup Responsibility ID
811 user_resp_id := FND_GLOBAL.RESP_ID;
812
813 if user_resp_id = -1 then
814 message_token('ROUTINE', 'FDFGLI');
815 add_message('FND', 'FLEX-CANNOT FIND RESP_ID PROF');
816 -- goto return_invalid;
817 end if;
818
819
820 -- Initialize Segment Values array
821
822 for i in 1..30 loop
823 seg_val(i) := null;
824 end loop;
825
826
827 -- Dynamic SQL for fetching from the Code Combinations table
828
829 sql_glcc := 'select ' ||
830 'chart_of_accounts_id, ' ||
831 'detail_budgeting_allowed_flag, ' ||
832 'account_type';
833
834 for i in 1..30 loop
835 sql_glcc := sql_glcc ||
836 ', segment' || i;
837 end loop;
838
839 sql_glcc := sql_glcc ||
840 ' from gl_code_combinations ' ||
841 'where code_combination_id = :ccid';
842
843
844 -- ========================= FND LOG ===========================
845 psa_utils.debug_other_string(g_state_level,l_full_path,' sql_glcc -> ' || sql_glcc);
846 -- ========================= FND LOG ===========================
847
848 cur_glcc := dbms_sql.open_cursor;
849 dbms_sql.parse(cur_glcc, sql_glcc, dbms_sql.v7);
850
851 -- ========================= FND LOG ===========================
852 psa_utils.debug_other_string(g_state_level,l_full_path,' BIND PARAMETERS');
853 psa_utils.debug_other_string(g_state_level,l_full_path,' ccid -> ' || ccid);
854 -- ========================= FND LOG ===========================
855
856 dbms_sql.bind_variable(cur_glcc, ':ccid', ccid);
857
858 dbms_sql.define_column(cur_glcc, 1, coaid);
859 dbms_sql.define_column(cur_glcc, 2, db_allowed_flag, 1);
860 dbms_sql.define_column(cur_glcc, 3, account_type, 1);
861
862 for i in 1..30 loop
863 dbms_sql.define_column(cur_glcc, i + 3, 'segment' || i , 25);
864 end loop;
865
866 ignore := dbms_sql.execute(cur_glcc);
867
868 -- ========================= FND LOG ===========================
869 psa_utils.debug_other_string(g_state_level,l_full_path,' ignore -> ' || ignore);
870 -- ========================= FND LOG ===========================
871
872 loop
873
874 -- ========================= FND LOG ===========================
875 psa_utils.debug_other_string(g_state_level,l_full_path,' start loop');
876 -- ========================= FND LOG ===========================
877
878 if dbms_sql.fetch_rows(cur_glcc) = 0 then
879 -- ========================= FND LOG ===========================
880 psa_utils.debug_other_string(g_state_level,l_full_path,' exit');
881 -- ========================= FND LOG ===========================
882 exit;
883 end if;
884
885 dbms_sql.column_value(cur_glcc, 1, coaid);
886 dbms_sql.column_value(cur_glcc, 2, db_allowed_flag);
887 dbms_sql.column_value(cur_glcc, 3, account_type);
888
889 for i in 1..30 loop
890 dbms_sql.column_value(cur_glcc, i + 3, seg_val(i));
891 -- ========================= FND LOG ===========================
892 psa_utils.debug_other_string(g_state_level,l_full_path,' seg_val(' || i || ') -> ' || seg_val(i));
893 -- ========================= FND LOG ===========================
894 end loop;
895
896 -- ========================= FND LOG ===========================
897 psa_utils.debug_other_string(g_state_level,l_full_path,' end loop');
898 -- ========================= FND LOG ===========================
899
900 end loop;
901
902 dbms_sql.close_cursor(cur_glcc);
903
904 -- ========================= FND LOG ===========================
905 psa_utils.debug_other_string(g_state_level,l_full_path,' account_type -> ' || account_type);
906 -- ========================= FND LOG ===========================
907
908 if account_type in ('A', 'L', 'O', 'R', 'E') then
909 acct_category := 'P';
910 else
911 acct_category := 'B';
912 end if;
913
914 -- ========================= FND LOG ===========================
915 psa_utils.debug_other_string(g_state_level,l_full_path,' acct_category -> ' || acct_category);
916 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN -> TRUE');
917 -- ========================= FND LOG ===========================
918
919 return(TRUE);
920
921 <<return_invalid>>
922 -- ========================= FND LOG ===========================
923 psa_utils.debug_other_string(g_state_level,l_full_path,' LABEL - return_invalid');
924 -- ========================= FND LOG ===========================
925 if dbms_sql.is_open(cur_glcc) then
926 dbms_sql.close_cursor(cur_glcc);
927 end if;
928
929 -- ========================= FND LOG ===========================
930 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN -> FALSE');
931 -- ========================= FND LOG ===========================
932
933 return(FALSE);
934
935
936 EXCEPTION
937
938 WHEN OTHERS THEN
939
940 if dbms_sql.is_open(cur_glcc) then
941 dbms_sql.close_cursor(cur_glcc);
942 end if;
943
944 message_token('MSG', 'glfini() exception:' || SQLERRM);
945 add_message('FND', 'FLEX-SSV EXCEPTION');
946
947 -- ========================= FND LOG ===========================
948 psa_utils.debug_other_string(g_state_level,l_full_path,
949 ' EXCEPTION WHEN OTHERS GLFINI - '||SQLERRM);
950 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN -> FALSE');
951 -- ========================= FND LOG ===========================
952
953 return(FALSE);
954
955 END glfini;
956
957 /* ------------------------------------------------------------------------- */
958
959 -- Check if GL has been installed
960
961
962 -- Called Routines :
963
964 -- FND_PROFILE.GET_SPECIFIC : Get Profile Value
965
966 -- FND_INSTALLATION.GET : Get Product Installation Info
967
968 -- message_token : Add Token and Value to the Message Token array
969
970 -- add_message : Define a Message Token with a Value and set the Message Name
971
972
973 FUNCTION glfcin RETURN BOOLEAN IS
974
975 dep_appl_id fnd_application.application_id%TYPE;
976 status fnd_product_installations.status%TYPE;
977 l_temp_industry fnd_product_installations.industry%TYPE;
978 l_industry fnd_profile_option_values.profile_option_value%type;
979
980 l_defined BOOLEAN;
981
982 cursor cnt_lgr(coaid NUMBER) IS
983 select count(*)
984 from gl_ledgers
985 where enable_budgetary_control_flag = 'Y'
986 and chart_of_accounts_id = coaid;
987
988
989 -- ========================= FND LOG ===========================
990 l_full_path VARCHAR2(100) := g_path || 'glfcin.';
991 -- ========================= FND LOG ===========================
992
993 BEGIN
994
995 -- ========================= FND LOG ===========================
996 psa_utils.debug_other_string(g_state_level,l_full_path,' START glfcin ');
997 -- ========================= FND LOG ===========================
998
999 -- Get Product Installation info by Application ID (101 for SQLGL)
1000
1001 dep_appl_id := FND_GLOBAL.RESP_APPL_ID;
1002
1003 if dep_appl_id = -1 then
1004 dep_appl_id := 101;
1005 end if;
1006
1007 -- ========================= FND LOG ===========================
1008 psa_utils.debug_other_string(g_state_level,l_full_path,' dep_appl_id -> ' || to_char(dep_appl_id));
1009 -- ========================= FND LOG ===========================
1010
1011 -- Get GL Installation Status
1012 -- The installation info is now implemented as a profile option (INDUSTRY).
1013
1014 FND_PROFILE.GET_SPECIFIC('INDUSTRY',
1015 user_id,
1016 user_resp_id,
1017 dep_appl_id,
1018 l_industry,
1019 l_defined);
1020
1021 if not FND_INSTALLATION.GET(dep_appl_id,
1022 101,
1023 status,
1024 l_temp_industry) then
1025
1026 message_token('ROUTINE', 'FDFGLI');
1027 add_message('SQLGL', 'GL_CANT_GET_INSTALL_INDUSTRY');
1028 return(FALSE);
1029
1030 end if;
1031
1032 if not l_defined then
1033
1034 l_industry := l_temp_industry;
1035
1036 end if;
1037
1038 -- ========================= FND LOG ===========================
1039 psa_utils.debug_other_string(g_state_level,l_full_path,' l_industry -> ' || l_industry);
1040 -- ========================= FND LOG ===========================
1041
1042 -- If installed check count of Set of Books with Budgetary Control flag
1043 -- enabled
1044
1045 if status = 'I' then
1046
1047 gl_installed := 'INSTALLED';
1048 industry := l_industry;
1049
1050 open cnt_lgr(coaid);
1051
1052 fetch cnt_lgr
1053 into num_bc_lgr;
1054
1055 close cnt_lgr;
1056
1057 end if;
1058
1059 -- ========================= FND LOG ===========================
1060 psa_utils.debug_other_string(g_state_level,l_full_path,' num_bc_lgr -> ' || num_bc_lgr);
1061 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN -> TRUE');
1062 -- ========================= FND LOG ===========================
1063
1064 return(TRUE);
1065
1066
1067 EXCEPTION
1068
1069 WHEN OTHERS THEN
1070
1071 if cnt_lgr%ISOPEN then
1072 close cnt_lgr;
1073 end if;
1074
1075 message_token('MSG', 'glfcin() exception:' || SQLERRM);
1076 add_message('FND', 'FLEX-SSV EXCEPTION');
1077
1078 -- ========================= FND LOG ===========================
1079 psa_utils.debug_other_string(g_state_level,l_full_path,' EXCEPTION WHEN OTHERS GLFCIN - ' || SQLERRM);
1080 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN -> FALSE');
1081 -- ========================= FND LOG ===========================
1082
1083 return(FALSE);
1084
1085 END glfcin;
1086
1087 /* ------------------------------------------------------------------------- */
1088
1089 -- Retrieve Value Set IDs for the Segments in the Code Combination
1090
1091
1092 -- Called Routines :
1093
1094 -- message_token : Add Token and Value to the Message Token array
1095
1096 -- add_message : Update global Message String
1097
1098
1099 -- Arguments :
1100
1101 -- val_set : Value Set IDs for all the Segments in the Combination
1102
1103
1104 FUNCTION glfisi(val_set IN OUT NOCOPY SegVsetArray) RETURN BOOLEAN IS
1105
1106 i BINARY_INTEGER;
1107
1108 col_name fnd_id_flex_segments.application_column_name%TYPE;
1109 vset_id fnd_id_flex_segments.flex_value_set_id%TYPE;
1110 e_val_set SegVsetArray;
1111
1112 cursor valset(flex_num NUMBER,
1113 appl_id NUMBER,
1114 flex_code VARCHAR2) IS
1115 select application_column_name,
1116 nvl(flex_value_set_id, 0) value_set_id
1117 from fnd_id_flex_segments
1118 where enabled_flag = 'Y'
1119 and id_flex_num = flex_num
1120 and application_id = appl_id
1121 and id_flex_code = flex_code;
1122
1123 cursor accseg(flex_num NUMBER,
1124 appl_id NUMBER,
1125 flex_code VARCHAR2) IS
1126 select /*+ ORDERED INDEX (FND_SEGMENT_ATTRIBUTE_VALUES
1127 FND_SEGMENT_ATTRIBUTE_VALS_U1) */
1128 application_column_name
1129 from fnd_segment_attribute_values
1130 where attribute_value = 'Y'
1131 and segment_attribute_type = 'GL_ACCOUNT'
1132 and id_flex_num = flex_num
1133 and application_id = appl_id
1134 and id_flex_code = flex_code;
1135
1136
1137 -- ========================= FND LOG ===========================
1138 l_full_path VARCHAR2(100) := g_path || 'glfisi.';
1139 -- ========================= FND LOG ===========================
1140
1141 BEGIN
1142
1143 -- ========================= FND LOG ===========================
1144 psa_utils.debug_other_string(g_state_level,l_full_path,' START glfisi ');
1145 -- ========================= FND LOG ===========================
1146
1147 e_val_set := val_set;
1148 -- Assign Value Set IDs for the Segments
1149
1150 -- ========================= FND LOG ===========================
1151 psa_utils.debug_other_string(g_state_level,l_full_path,' populating temp table val_set ');
1152 psa_utils.debug_other_string(g_state_level,l_full_path,' coaid -> '|| coaid );
1153 -- ========================= FND LOG ===========================
1154
1155 for c_valset in valset(coaid, 101, 'GL#') loop
1156
1157 col_name := c_valset.application_column_name;
1158
1159 -- ========================= FND LOG ===========================
1160 psa_utils.debug_other_string(g_state_level,l_full_path,' col_name -> ' || col_name);
1161 -- ========================= FND LOG ===========================
1162
1163 vset_id := c_valset.value_set_id;
1164
1165 -- ========================= FND LOG ===========================
1166 psa_utils.debug_other_string(g_state_level,l_full_path,' vset_id -> ' || vset_id);
1167 -- ========================= FND LOG ===========================
1168
1169 i := to_number(substr(col_name, 8, length(col_name) - 7));
1170
1171 -- ========================= FND LOG ===========================
1172 psa_utils.debug_other_string(g_state_level,l_full_path,' i -> ' || i);
1173 -- ========================= FND LOG ===========================
1174
1175 val_set(i) := vset_id;
1176
1177 -- ========================= FND LOG ===========================
1178 psa_utils.debug_other_string(g_state_level,l_full_path,' val_set(' || i || ') ->' || vset_id);
1179 -- ========================= FND LOG ===========================
1180
1181 num_active_segs := num_active_segs + 1;
1182
1183 -- ========================= FND LOG ===========================
1184 psa_utils.debug_other_string(g_state_level,l_full_path,' num_active_segs -> ' || num_active_segs);
1185 -- ========================= FND LOG ===========================
1186
1187 end loop;
1188
1189
1190 -- ========================= FND LOG ===========================
1191 psa_utils.debug_other_string(g_state_level,l_full_path,' opening accseg ');
1192 -- ========================= FND LOG ===========================
1193
1194 -- Get Cardinal Order or Index Number of the Account Segment
1195 open accseg(coaid, 101, 'GL#');
1196
1197 fetch accseg
1198 into col_name;
1199
1200 -- ========================= FND LOG ===========================
1201 psa_utils.debug_other_string(g_state_level,l_full_path,' col_name -> ' || col_name);
1202 -- ========================= FND LOG ===========================
1203
1204 -- No Accounting Segment defined
1205
1206 if accseg%NOTFOUND then
1207 message_token('ROUTINE', 'FDFGLI');
1208 add_message('FND', 'FLEXGL-NO ACCT SEG');
1209 -- ========================= FND LOG ===========================
1210 psa_utils.debug_other_string(g_state_level,l_full_path,' No Accounting Segment defined');
1211 psa_utils.debug_other_string(g_state_level,l_full_path,' goto return_invalid');
1212 -- ========================= FND LOG ===========================
1213 goto return_invalid;
1214 end if;
1215
1216 close accseg;
1217
1218 i := to_number(substr(col_name, 8, length(col_name) - 7));
1219
1220 acct_seg_index := i;
1221
1222 -- ========================= FND LOG ===========================
1223 psa_utils.debug_other_string(g_state_level,l_full_path,' acct_seg_index -> ' || acct_seg_index);
1224 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN -> TRUE');
1225 -- ========================= FND LOG ===========================
1226
1227 return(TRUE);
1228
1229 <<return_invalid>>
1230
1231 -- ========================= FND LOG ===========================
1232 psa_utils.debug_other_string(g_state_level,l_full_path,' LABEL - return_invalid');
1233 -- ========================= FND LOG ===========================
1234
1235 if accseg%ISOPEN then
1236 close accseg;
1237 end if;
1238
1239 -- ========================= FND LOG ===========================
1240 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN -> FALSE');
1241 -- ========================= FND LOG ===========================
1242
1243 return(FALSE);
1244
1245
1246 EXCEPTION
1247
1248 WHEN OTHERS THEN
1249 val_set := e_val_set;
1250 if accseg%ISOPEN then
1251 close accseg;
1252 end if;
1253
1254 message_token('MSG', 'glfisi() exception:' || SQLERRM);
1255 add_message('FND', 'FLEX-SSV EXCEPTION');
1256
1257 -- ========================= FND LOG ===========================
1258 psa_utils.debug_other_string(g_state_level,l_full_path,' EXCEPTION WHEN OTHERS GLFISI -> ' || SQLERRM);
1259 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN -> FALSE');
1260 -- ========================= FND LOG ===========================
1261
1262 return(FALSE);
1263
1264 END glfisi;
1265
1266 /* ------------------------------------------------------------------------- */
1267
1268 -- Insert into Budget Assignments table only if Segment Values fall within
1269 -- any Account Ranges in the Budget Organization
1270
1271
1272 -- Called Routines :
1273
1274 -- message_token : Add Token and Value to the Message Token array
1275
1276 -- add_message : Update global Message String
1277
1278
1279 -- Arguments :
1280
1281 -- ccid : Code Combination ID
1282
1283
1284 FUNCTION glfiba(ccid IN NUMBER) RETURN BOOLEAN IS
1285
1286 -- Maximum Length for this Dynamic SQL Statement is 3387
1287
1288 sql_insba VARCHAR2(4000);
1289 cur_insba INTEGER;
1290 num_rows INTEGER;
1291
1292 i BINARY_INTEGER;
1293
1294
1295 -- ========================= FND LOG ===========================
1296 l_full_path VARCHAR2(100) := g_path || 'glfiba.';
1297 -- ========================= FND LOG ===========================
1298
1299 BEGIN
1300
1301 -- ========================= FND LOG ===========================
1302 psa_utils.debug_other_string(g_state_level,l_full_path,' START glfiba. ');
1303 -- ========================= FND LOG ===========================
1304 -- Bug 5501177/Bug 5556665 -dynamic sql changed to static sql from performance
1305 -- improvement
1306 insert into gl_budget_assignments (
1307 budget_entity_id,
1308 ledger_id,
1309 currency_code,
1310 range_id,
1311 entry_code,
1312 ordering_value,
1313 code_combination_id,
1314 last_update_date,
1315 last_updated_by,
1316 creation_date,
1317 created_by,
1318 last_update_login )
1319 select bar.budget_entity_id,
1320 bar.ledger_id,
1321 bar.currency_code,
1322 bar.range_id,
1323 bar.entry_code,
1324 seg_val(acct_seg_index),
1325 ccid,
1326 sysdate,
1327 user_id,
1328 sysdate,
1329 user_id,
1330 login_id
1331 from gl_budget_assignment_ranges bar,
1332 gl_ledgers lgr
1333 where
1334 exists (select 'found' from gl_budorg_bc_options bco
1335 where bar.range_id = bco.range_id)
1336 AND (seg_val(1) IS NULL OR seg_val(1) BETWEEN bar.segment1_low AND
1337 bar.segment1_high)
1338 AND (seg_val(2) IS NULL OR seg_val(2) BETWEEN bar.segment2_low AND
1339 bar.segment2_high)
1340 AND (seg_val(3) IS NULL OR seg_val(3) BETWEEN bar.segment3_low AND
1341 bar.segment3_high)
1342 AND (seg_val(4) IS NULL OR seg_val(4) BETWEEN bar.segment4_low AND
1343 bar.segment4_high)
1344 AND (seg_val(5) IS NULL OR seg_val(5) BETWEEN bar.segment5_low AND
1345 bar.segment5_high)
1346 AND (seg_val(6) IS NULL OR seg_val(6) BETWEEN bar.segment6_low AND
1347 bar.segment6_high)
1348 AND (seg_val(7) IS NULL OR seg_val(7) BETWEEN bar.segment7_low AND
1349 bar.segment7_high)
1350 AND (seg_val(8) IS NULL OR seg_val(8) BETWEEN bar.segment8_low AND
1351 bar.segment8_high)
1352 AND (seg_val(9) IS NULL OR seg_val(9) BETWEEN bar.segment9_low AND
1353 bar.segment9_high)
1354 AND (seg_val(10) IS NULL OR seg_val(10) BETWEEN bar.segment10_low AND
1355 bar.segment10_high)
1356 AND (seg_val(11) IS NULL OR seg_val(11) BETWEEN bar.segment11_low AND
1357 bar.segment11_high)
1358 AND (seg_val(12) IS NULL OR seg_val(12) BETWEEN bar.segment12_low AND
1359 bar.segment12_high)
1360 AND (seg_val(13) IS NULL OR seg_val(13) BETWEEN bar.segment13_low AND
1361 bar.segment13_high)
1362 AND (seg_val(14) IS NULL OR seg_val(14) BETWEEN bar.segment14_low AND
1363 bar.segment14_high)
1364 AND (seg_val(15) IS NULL OR seg_val(15) BETWEEN bar.segment15_low AND
1365 bar.segment15_high)
1366 AND (seg_val(16) IS NULL OR seg_val(16) BETWEEN bar.segment16_low AND
1367 bar.segment16_high)
1368 AND (seg_val(17) IS NULL OR seg_val(17) BETWEEN bar.segment17_low AND
1369 bar.segment17_high)
1370 AND (seg_val(18) IS NULL OR seg_val(18) BETWEEN bar.segment18_low AND
1371 bar.segment18_high)
1372 AND (seg_val(19) IS NULL OR seg_val(19) BETWEEN bar.segment19_low AND
1373 bar.segment19_high)
1374 AND (seg_val(20) IS NULL OR seg_val(20) BETWEEN bar.segment20_low AND
1375 bar.segment20_high)
1376 AND (seg_val(21) IS NULL OR seg_val(21) BETWEEN bar.segment21_low AND
1377 bar.segment21_high)
1378 AND (seg_val(22) IS NULL OR seg_val(22) BETWEEN bar.segment22_low AND
1379 bar.segment22_high)
1380 AND (seg_val(23) IS NULL OR seg_val(23) BETWEEN bar.segment23_low AND
1381 bar.segment23_high)
1382 AND (seg_val(24) IS NULL OR seg_val(24) BETWEEN bar.segment24_low AND
1383 bar.segment24_high)
1384 AND (seg_val(25) IS NULL OR seg_val(25) BETWEEN bar.segment25_low AND
1385 bar.segment25_high)
1386 AND (seg_val(26) IS NULL OR seg_val(26) BETWEEN bar.segment26_low AND
1387 bar.segment26_high)
1388 AND (seg_val(27) IS NULL OR seg_val(27) BETWEEN bar.segment27_low AND
1389 bar.segment27_high)
1390 AND (seg_val(28) IS NULL OR seg_val(28) BETWEEN bar.segment28_low AND
1391 bar.segment28_high)
1392 AND (seg_val(29) IS NULL OR seg_val(29) BETWEEN bar.segment29_low AND
1393 bar.segment29_high)
1394 AND (seg_val(30) IS NULL OR seg_val(30) BETWEEN bar.segment30_low AND
1395 bar.segment30_high)
1396 and bar.currency_code = lgr.currency_code
1397 and bar.ledger_id = lgr.ledger_id
1398 and lgr.enable_budgetary_control_flag = 'Y'
1399 and lgr.chart_of_accounts_id = coaid ;
1400 -- ========================= FND LOG ===========================
1401 psa_utils.debug_other_string(g_state_level,l_full_path,' BIND PARAMETERS ');
1402 psa_utils.debug_other_string(g_state_level,l_full_path,
1403 ' seg_val(' || acct_seg_index || ') -> ' || seg_val(acct_seg_index));
1404 psa_utils.debug_other_string(g_state_level,l_full_path,' ccid -> ' || ccid);
1405 psa_utils.debug_other_string(g_state_level,l_full_path,' user_id -> ' || user_id);
1406 psa_utils.debug_other_string(g_state_level,l_full_path,' login_id -> ' || login_id);
1407 psa_utils.debug_other_string(g_state_level,l_full_path,' coaid -> ' || coaid);
1408
1409 psa_utils.debug_other_string(g_state_level,l_full_path,' num_rows -> ' || num_rows);
1410
1411 psa_utils.debug_other_string(g_state_level,l_full_path,'RETURN -> TRUE');
1412 -- ========================= FND LOG ===========================
1413
1414 return(TRUE);
1415
1416
1417 EXCEPTION
1418
1419 WHEN OTHERS THEN
1420
1421
1422 -- Dynamic SQL Exception
1423
1424 message_token('MSG', SQLERRM);
1425 message_token('SQLSTR', substr(sql_insba, 1, 1000));
1426 add_message('FND', 'FLEX-DSQL EXCEPTION');
1427
1428 -- ========================= FND LOG ===========================
1429 psa_utils.debug_other_string(g_state_level,l_full_path,' EXCEPTION WHEN OTHERS GLFIBA -> ' || SQLERRM);
1430 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN -> FALSE');
1431 -- ========================= FND LOG ===========================
1432
1433 return(FALSE);
1434
1435 END glfiba;
1436
1437 /* ------------------------------------------------------------------------- */
1438
1439 -- Loop through the Summary Templates and create Parent Accounts for
1440 -- each template
1441
1442
1443 -- Called Routines :
1444
1445 -- glfgdg : Get Dynamic Group ID for the new Parent Accounts
1446
1447 -- glfcrg : Identify Rollup Groups
1448
1449
1450 -- glfcpc : Create Parent Accounts
1451
1452 -- message_token : Add Token and Value to the Message Token array
1453
1454 -- add_message : Update global Message String
1455
1456
1457 -- Arguments :
1458
1459 -- val_set : Value Set IDs for the Segments in the Code Combination
1460
1461
1462 FUNCTION glfcst(val_set IN SegVsetArray, ccid IN gl_code_combinations.code_combination_id%TYPE) RETURN BOOLEAN IS
1463
1464 -- Maximum Length for this Dynamic SQL Statement is 993
1465
1466 sql_stmp VARCHAR2(32767);
1467 sql_stmp_length INTEGER;
1468 sql_stmp_printed INTEGER;
1469 cur_stmp INTEGER;
1470 ignore INTEGER;
1471 sql_stmp_count INTEGER;
1472 test_value INTEGER;
1473 i BINARY_INTEGER;
1474 seg_type SegTypeArray;
1475 rgroup SegRgrpArray;
1476 rgroup_sorted RgrpSrtArray;
1477 rgroup_ind RgrpIndArray;
1478
1479 template_name gl_summary_templates.template_name%TYPE;
1480 template_id gl_summary_templates.template_id%TYPE;
1481 lgr_id gl_summary_templates.ledger_id%TYPE;
1482 segment1_type gl_summary_templates.segment1_type%TYPE;
1483 segment2_type gl_summary_templates.segment2_type%TYPE;
1484 segment3_type gl_summary_templates.segment3_type%TYPE;
1485 segment4_type gl_summary_templates.segment4_type%TYPE;
1486 segment5_type gl_summary_templates.segment5_type%TYPE;
1487 segment6_type gl_summary_templates.segment6_type%TYPE;
1488 segment7_type gl_summary_templates.segment7_type%TYPE;
1489 segment8_type gl_summary_templates.segment8_type%TYPE;
1490 segment9_type gl_summary_templates.segment9_type%TYPE;
1491 segment10_type gl_summary_templates.segment10_type%TYPE;
1492 segment11_type gl_summary_templates.segment11_type%TYPE;
1493 segment12_type gl_summary_templates.segment12_type%TYPE;
1494 segment13_type gl_summary_templates.segment13_type%TYPE;
1495 segment14_type gl_summary_templates.segment14_type%TYPE;
1496 segment15_type gl_summary_templates.segment15_type%TYPE;
1497 segment16_type gl_summary_templates.segment16_type%TYPE;
1498 segment17_type gl_summary_templates.segment17_type%TYPE;
1499 segment18_type gl_summary_templates.segment18_type%TYPE;
1500 segment19_type gl_summary_templates.segment19_type%TYPE;
1501 segment20_type gl_summary_templates.segment20_type%TYPE;
1502 segment21_type gl_summary_templates.segment21_type%TYPE;
1503 segment22_type gl_summary_templates.segment22_type%TYPE;
1504 segment23_type gl_summary_templates.segment23_type%TYPE;
1505 segment24_type gl_summary_templates.segment24_type%TYPE;
1506 segment25_type gl_summary_templates.segment25_type%TYPE;
1507 segment26_type gl_summary_templates.segment26_type%TYPE;
1508 segment27_type gl_summary_templates.segment27_type%TYPE;
1509 segment28_type gl_summary_templates.segment28_type%TYPE;
1510 segment29_type gl_summary_templates.segment29_type%TYPE;
1511 segment30_type gl_summary_templates.segment30_type%TYPE;
1512
1513
1514 -- ========================= FND LOG ===========================
1515 l_full_path VARCHAR2(100) := g_path || 'glfcst.';
1516 -- ========================= FND LOG ===========================
1517
1518 BEGIN
1519
1520 -- ========================= FND LOG ===========================
1521 psa_utils.debug_other_string(g_state_level,l_full_path,' START glfcst ');
1522 -- ========================= FND LOG ===========================
1523
1524 sql_stmp := 'select ' ||
1525 'smt.template_name, ' ||
1526 'smt.template_id, ' ||
1527 'smt.ledger_id';
1528
1529 for i in 1..30 loop
1530 sql_stmp := sql_stmp ||
1531 ', smt.segment' || i || '_type';
1532 end loop;
1533
1534 sql_stmp := sql_stmp || ' ' ||
1535 'from gl_summary_templates smt, ' ||
1536 'gl_ledgers lgr ' ||
1537 'where smt.status in (''A'', ''F'') ' ||
1538 'and smt.account_category_code = :catg ' ||
1539 'and smt.ledger_id = lgr.ledger_id ' ||
1540 'and lgr.enable_budgetary_control_flag = ''Y'' ' ||
1541 'and lgr.chart_of_accounts_id = :coaid ' ||
1542 'and EXISTS (select ''found'' from ' ||
1543 'gl_summary_bc_options smb where ' ||
1544 'smt.template_id = smb.template_id) ' ;
1545
1546 for i in 1..10 loop
1547 -- This query is for performance improvement Bug 5220785
1548 -- The query will run fine without this loop.
1549 -- The objective of this loop is to restrict the no of summary templates so
1550 -- that less PL/SQL processing would be required in the later
1551 -- process to create the parents.
1552 -- When the loop is set to run from 1 to 30, it will only include the
1553 -- summary templates that are associated with the code combination
1554 -- Please note that the iteration only runs from 1 to 10 at this moment in
1555 -- time. It is specifically set this way for :
1556 -- 1. Avoid the huge query that may have occurred
1557 -- 2. Normally customers would not be using more than 10 segments. As
1558 -- this is used as an optimization, it is okay even the customer
1559 -- has more than 10 segments defined
1560 -- 3. Since there are quite some tables involved, by having less joins can
1561 -- improve performance for normal case.
1562 sql_stmp := sql_stmp || ' ' ||
1563 'and (segment' || i || '_type is null ' ||
1564 'or segment' || i || '_type in (''D'', ''T'') ' ||
1565 'or segment' || i || '_type in (select fh.hierarchy_name ' ||
1566 'from gl_code_combinations cc, gl_summary_hierarchies gsh, fnd_flex_values fv, fnd_id_flex_segments fs, ' ||
1567 'fnd_flex_hierarchies_vl fh ' ||
1568 'where cc.code_combination_id = :ccid and ' ||
1569 'cc.segment' || i || ' between gsh.child_flex_value_low and gsh.child_flex_value_high and ' ||
1570 'gsh.flex_value_set_id = fv.flex_value_set_id and ' ||
1571 'gsh.parent_flex_value = fv.flex_value and ' ||
1572 'gsh.flex_value_set_id = fv.flex_value_set_id and ' ||
1573 'fv.enabled_flag = ''Y'' and ' ||
1574 'fs.flex_value_set_id = gsh.flex_value_set_id and ' ||
1575 -- 'sob.chart_of_accounts_id = cc.chart_of_accounts_id and ' ||
1576 'fs.enabled_flag = ''Y'' and ' ||
1577 'cc.chart_of_accounts_id = fs.id_flex_num and ' ||
1578 'fs.application_id = 101 and ' ||
1579 'fs.id_flex_code = ''GL#'' and ' ||
1580 'fs.application_column_name = ''SEGMENT' || i || ''' and ' ||
1581 'fv.flex_value_set_id = fh.flex_value_set_id and ' ||
1582 'fv.structured_hierarchy_level = fh.hierarchy_id)) ';
1583 -- Still need to get the val_set_id somehow
1584 -- take this out later
1585 -- ========================= FND LOG ===========================
1586 psa_utils.debug_other_string(g_state_level,l_full_path,' sql_stmp -> here' || i);
1587 -- ========================= FND LOG ===========================
1588 end loop;
1589
1590 sql_stmp := sql_stmp || ' ' || 'order by smt.template_id, smt.ledger_id';
1591
1592
1593
1594 -- ========================= FND LOG ===========================
1595 -- psa_utils.debug_other_string(g_state_level,l_full_path,' sql_stmp -> ' || sql_stmp);
1596 sql_stmp_printed := 1;
1597 sql_stmp_length := length(sql_stmp);
1598 psa_utils.debug_other_string(g_state_level,l_full_path,' sql_stmp length -> ' || sql_stmp_length);
1599 loop
1600 exit when sql_stmp_printed >= sql_stmp_length;
1601 psa_utils.debug_other_string(g_state_level,l_full_path,' here again');
1602 psa_utils.debug_other_string(g_state_level,l_full_path,' sql_stmp -> ' || SUBSTR(sql_stmp,sql_stmp_printed,3000));
1603 sql_stmp_printed := sql_stmp_printed + 3000;
1604 --psa_utils.debug_other_string(g_state_level,l_full_path,' sql_stmp -> ' || SUBSTR(sql_stmp,3001,6000));
1605 --psa_utils.debug_other_string(g_state_level,l_full_path,' here again2');
1606 end loop;
1607
1608 -- ========================= FND LOG ===========================
1609
1610 cur_stmp := dbms_sql.open_cursor;
1611 dbms_sql.parse(cur_stmp, sql_stmp, dbms_sql.v7);
1612
1613 -- ========================= FND LOG ===========================
1614 -- psa_utils.debug_other_string(g_state_level,l_full_path,' BIND PARAMETERS');
1615 -- psa_utils.debug_other_string(g_state_level,l_full_path,' coaid -> ' || coaid);
1616 -- psa_utils.debug_other_string(g_state_level,l_full_path,' acct_category -> ' || acct_category);
1617
1618 psa_utils.debug_other_string(g_state_level,l_full_path,' sql_stmp BIND PARAMETERS');
1619 psa_utils.debug_other_string(g_state_level,l_full_path,' sql_stmp coaid -> ' || coaid);
1620 psa_utils.debug_other_string(g_state_level,l_full_path,' sql_stmp acct_category -> ' || acct_category);
1621 psa_utils.debug_other_string(g_state_level,l_full_path,' sql_stmp ccid -> ' || ccid);
1622 -- ========================= FND LOG ===========================
1623
1624 dbms_sql.bind_variable(cur_stmp, ':coaid', coaid);
1625 dbms_sql.bind_variable(cur_stmp, ':catg', acct_category);
1626 dbms_sql.bind_variable(cur_stmp, ':ccid', ccid);
1627
1628 dbms_sql.define_column(cur_stmp, 1, template_name, 50);
1629 dbms_sql.define_column(cur_stmp, 2, template_id);
1630 dbms_sql.define_column(cur_stmp, 3, lgr_id);
1631
1632 for i in 1..30 loop
1633 dbms_sql.define_column(cur_stmp, i + 3, 'segment' || i || '_' ||
1634 'type', 25);
1635 end loop;
1636
1637 ignore := dbms_sql.execute(cur_stmp);
1638 sql_stmp_count := 0;
1639 -- ========================= FND LOG ===========================
1640 psa_utils.debug_other_string(g_state_level,l_full_path,' ignore ->' || ignore);
1641 -- ========================= FND LOG ===========================
1642
1643 loop
1644
1645 -- ========================= FND LOG ===========================
1646 psa_utils.debug_other_string(g_state_level,l_full_path,' start loop');
1647 -- ========================= FND LOG ===========================
1648 test_value := dbms_sql.fetch_rows(cur_stmp);
1649 -- ========================= FND LOG ===========================
1650 psa_utils.debug_other_string(g_state_level,l_full_path,' sql_stmp test_value -> ' || test_value);
1651 -- ========================= FND LOG ===========================
1652 -- if dbms_sql.fetch_rows(cur_stmp) > 0 then
1653 if test_value > 0 then
1654 psa_utils.debug_other_string(g_state_level,l_full_path,' sql_stmp -> here1');
1655 sql_stmp_count := sql_stmp_count + 1;
1656
1657 -- ========================= FND LOG ===========================
1658 psa_utils.debug_other_string(g_state_level,l_full_path,' sql_stmp dyn_grp_id -> ' || dyn_grp_id);
1659 -- ========================= FND LOG ===========================
1660
1661 -- Get a new Dynamic Group ID if the number of templates > 0
1662
1663 if dyn_grp_id = -1 then
1664 -- ========================= FND LOG ===========================
1665 psa_utils.debug_other_string(g_state_level,l_full_path,' Calling glfgdg');
1666 -- ========================= FND LOG ===========================
1667
1668 if not glfgdg then
1669 -- ========================= FND LOG ===========================
1670 psa_utils.debug_other_string(g_state_level,l_full_path,' goto return_invalid');
1671 -- ========================= FND LOG ===========================
1672 goto return_invalid;
1673 end if;
1674 end if;
1675
1676 -- ========================= FND LOG ===========================
1677 psa_utils.debug_other_string(g_state_level,l_full_path,' Initialize the Summary Template structure');
1678 -- ========================= FND LOG ===========================
1679
1680 -- Initialize the Summary Template structure
1681
1682 for i in 1..30 loop
1683 seg_type(i) := null;
1684 end loop;
1685
1686 dbms_sql.column_value(cur_stmp, 1, template_name);
1687 dbms_sql.column_value(cur_stmp, 2, template_id);
1688 dbms_sql.column_value(cur_stmp, 3, lgr_id);
1689
1690 -- ========================= FND LOG ===========================
1691 psa_utils.debug_other_string(g_state_level,l_full_path,' sql_stmp Template name -> ' || template_name);
1692 -- ========================= FND LOG ===========================
1693 for i in 1..30 loop
1694 dbms_sql.column_value(cur_stmp, i + 3, seg_type(i));
1695 end loop;
1696
1697 -- ========================= FND LOG ===========================
1698 psa_utils.debug_other_string(g_state_level,l_full_path,
1699 ' Calling glfcrg - Identify Rollup Groups');
1700 -- ========================= FND LOG ===========================
1701
1702 -- Identify Rollup Groups
1703
1704 if not glfcrg(val_set, seg_type, rgroup, template_name) then
1705 -- ========================= FND LOG ===========================
1706 psa_utils.debug_other_string(g_state_level,l_full_path,' goto return_invalid');
1707 -- ========================= FND LOG ===========================
1708 goto return_invalid;
1709 end if;
1710
1711 -- ========================= FND LOG ===========================
1712 psa_utils.debug_other_string(g_state_level,l_full_path,
1713 ' Initializing the rgroup_sorted and rgroup_ind array');
1714 -- ========================= FND LOG ===========================
1715
1716 FOR i IN 1..30 LOOP
1717 rgroup_sorted(i) := null;
1718 rgroup_ind(i) := null;
1719 END LOOP;
1720
1721 FOR i IN 1..30 LOOP
1722 IF ((rgroup(i) is not null) and (rgroup(i) not in ('D', 'T'))) then
1723 -- ========================= FND LOG ===========================
1724 psa_utils.debug_other_string(g_state_level,l_full_path,
1725 ' rgroup(' || i || ') -> ' || rgroup(i));
1726 psa_utils.debug_other_string(g_state_level,l_full_path,
1727 ' val_set(' || i || ') -> ' || val_set(i));
1728 -- ========================= FND LOG ===========================
1729
1730 rgroup_ind(i) := i;
1731
1732 -- ========================= FND LOG ===========================
1733 psa_utils.debug_other_string(g_state_level,l_full_path,
1734 ' rgroup_ind(' || i || ') -> ' || rgroup_ind(i));
1735 -- ========================= FND LOG ===========================
1736 END IF;
1737 END LOOP;
1738 -- ========================= FND LOG ===========================
1739 psa_utils.debug_other_string(g_state_level,l_full_path,
1740 ' Calling glfcpc - Create Parent Accounts');
1741 -- ========================= FND LOG ===========================
1742
1743 -- Create Parent Accounts
1744
1745 if not glfcpc(seg_type, rgroup, rgroup_sorted, rgroup_ind,
1746 val_set, template_id, lgr_id) then
1747 -- ========================= FND LOG ===========================
1748 psa_utils.debug_other_string(g_state_level,l_full_path,' goto return_invalid');
1749 -- ========================= FND LOG ===========================
1750 goto return_invalid;
1751 else
1752 -- ========================= FND LOG ===========================
1753 psa_utils.debug_other_string(g_state_level,l_full_path,' Created_parent -> TRUE');
1754 -- ========================= FND LOG ===========================
1755 created_parent := TRUE;
1756 end if;
1757
1758 else
1759 -- ========================= FND LOG ===========================
1760 psa_utils.debug_other_string(g_state_level,l_full_path,' exit ');
1761 -- ========================= FND LOG ===========================
1762 exit;
1763 end if;
1764
1765 -- ========================= FND LOG ===========================
1766 psa_utils.debug_other_string(g_state_level,l_full_path,' end loop ');
1767 -- ========================= FND LOG ===========================
1768
1769 end loop;
1770
1771 --num_templates := dbms_sql.last_row_count;
1772 num_templates := sql_stmp_count;
1773
1774 -- ========================= FND LOG ===========================
1775 psa_utils.debug_other_string(g_state_level,l_full_path,' sql_stmp: num_templates -> '|| num_templates);
1776 -- ========================= FND LOG ===========================
1777
1778 dbms_sql.close_cursor(cur_stmp);
1779
1780 -- ========================= FND LOG ===========================
1781 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN -> TRUE');
1782 -- ========================= FND LOG ===========================
1783
1784 return(TRUE);
1785
1786 <<return_invalid>>
1787 -- ========================= FND LOG ===========================
1788 psa_utils.debug_other_string(g_state_level,l_full_path,' LABEL - return_valid');
1789 -- ========================= FND LOG ===========================
1790
1791 if dbms_sql.is_open(cur_stmp) then
1792 dbms_sql.close_cursor(cur_stmp);
1793 end if;
1794
1795 -- ========================= FND LOG ===========================
1796 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN -> FALSE');
1797 -- ========================= FND LOG ===========================
1798
1799 return(FALSE);
1800
1801
1802 EXCEPTION
1803
1804 WHEN OTHERS THEN
1805
1806 if dbms_sql.is_open(cur_stmp) then
1807 dbms_sql.close_cursor(cur_stmp);
1808 end if;
1809
1810 -- Dynamic SQL Exception
1811
1812 message_token('MSG', SQLERRM);
1813 message_token('SQLSTR', substr(sql_stmp, 1, 1000));
1814 add_message('FND', 'FLEX-DSQL EXCEPTION');
1815
1816 -- ========================= FND LOG ===========================
1817 psa_utils.debug_other_string(g_state_level,l_full_path,' EXCEPTION WHEN OTHERS GLFCST -' || SQLERRM);
1818 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN -> FALSE ');
1819 -- ========================= FND LOG ===========================
1820
1821 return(FALSE);
1822
1823 END glfcst;
1824
1825 /* ------------------------------------------------------------------------- */
1826
1827 -- Get Dynamic Group ID for the new Parent Accounts
1828
1829
1830 -- Called Routines :
1831
1832 -- message_token : Add Token and Value to the Message Token array
1833
1834 -- add_message : Update global Message String
1835
1836
1837 FUNCTION glfgdg RETURN BOOLEAN IS
1838
1839 cursor dyngrp is
1840 select gl_dynamic_summ_combinations_s.NEXTVAL
1841 from sys.dual;
1842
1843 -- ========================= FND LOG ===========================
1844 l_full_path VARCHAR2(100) := g_path || 'glfgdg.';
1845 -- ========================= FND LOG ===========================
1846
1847 BEGIN
1848
1849 -- ========================= FND LOG ===========================
1850 psa_utils.debug_other_string(g_state_level,l_full_path,' START glfgdg ');
1851 -- ========================= FND LOG ===========================
1852
1853 open dyngrp;
1854
1855 fetch dyngrp
1856 into dyn_grp_id;
1857
1858 -- ========================= FND LOG ===========================
1859 psa_utils.debug_other_string(g_state_level,l_full_path,' dyn_grp_id -> ' || dyn_grp_id);
1860 -- ========================= FND LOG ===========================
1861
1862 if dyngrp%NOTFOUND then
1863 add_message('FND', 'FLEX-NO ROWS IN DUAL');
1864 -- ========================= FND LOG ===========================
1865 psa_utils.debug_other_string(g_state_level,l_full_path,' goto return_invalid ');
1866 -- ========================= FND LOG ===========================
1867 goto return_invalid;
1868 end if;
1869
1870 close dyngrp;
1871
1872 -- ========================= FND LOG ===========================
1873 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN -> TRUE');
1874 -- ========================= FND LOG ===========================
1875
1876 return(TRUE);
1877
1878 <<return_invalid>>
1879 -- ========================= FND LOG ===========================
1880 psa_utils.debug_other_string(g_state_level,l_full_path,' LABEL - return_invalid');
1881 -- ========================= FND LOG ===========================
1882
1883 if dyngrp%ISOPEN then
1884 close dyngrp;
1885 end if;
1886
1887 -- ========================= FND LOG ===========================
1888 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN -> FALSE');
1889 -- ========================= FND LOG ===========================
1890
1891 return(FALSE);
1892
1893
1894 EXCEPTION
1895
1896 WHEN OTHERS THEN
1897
1898 if dyngrp%ISOPEN then
1899 close dyngrp;
1900 end if;
1901
1902 message_token('MSG', 'glfgdg() exception:' || SQLERRM);
1903 add_message('FND', 'FLEX-SSV EXCEPTION');
1904
1905 -- ========================= FND LOG ===========================
1906 psa_utils.debug_other_string(g_state_level,l_full_path,' EXCEPTION WHEN PTHERS GLFGDG - ' || SQLERRM);
1907 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN -> FALSE');
1908 -- ========================= FND LOG ===========================
1909
1910 return(FALSE);
1911
1912 END glfgdg;
1913
1914 /* ------------------------------------------------------------------------- */
1915
1916 -- Identify Rollup Groups
1917
1918
1919 -- Called Routines :
1920
1921 -- message_token : Add Token and Value to the Message Token array
1922
1923 -- add_message : Update global Message String
1924
1925
1926 -- Arguments :
1927
1928 -- val_set : Value Set IDs for the Segments in the Code Combination
1929
1930 -- seg_type : Summary Template Segment Types
1931
1932 -- rgroup : Rollup Group for the Summary Template Segment Types
1933
1934 -- template_name : Template Name
1935
1936
1937 FUNCTION glfcrg(val_set IN SegVsetArray,
1938 seg_type IN SegTypeArray,
1939 rgroup IN OUT NOCOPY SegRgrpArray,
1940 template_name IN VARCHAR2) RETURN BOOLEAN IS
1941
1942 i BINARY_INTEGER;
1943 e_rgroup SegRgrpArray;
1944
1945 cursor flexhid(vsid NUMBER,
1946 hname VARCHAR2) is
1947 select hierarchy_id
1948 from fnd_flex_hierarchies_vl
1949 where flex_value_set_id = vsid
1950 and hierarchy_name = hname;
1951
1952 -- ========================= FND LOG ===========================
1953 l_full_path VARCHAR2(100) := g_path || 'glfcrg.';
1954 -- ========================= FND LOG ===========================
1955
1956 BEGIN
1957
1958 -- ========================= FND LOG ===========================
1959 psa_utils.debug_other_string(g_state_level,l_full_path,' START glfcrg ');
1960 -- ========================= FND LOG ===========================
1961
1962 -- Initialize Rollup Groups everytime this function is invoked
1963 e_rgroup := rgroup;
1964
1965 for i in 1..30 loop
1966 rgroup(i) := null;
1967 end loop;
1968
1969 for i in 1..30 loop
1970
1971 -- ========================= FND LOG ===========================
1972 psa_utils.debug_other_string(g_state_level,l_full_path,' start loop ');
1973 psa_utils.debug_other_string(g_state_level,l_full_path,
1974 ' seg_type(' || i || ') -> ' || seg_type(i));
1975 -- ========================= FND LOG ===========================
1976
1977 if seg_type(i) is not null then
1978
1979 if seg_type(i) in ('D', 'T') then
1980 rgroup(i) := seg_type(i);
1981 -- ========================= FND LOG ===========================
1982 psa_utils.debug_other_string(g_state_level,l_full_path,
1983 ' rgroup(' || i || ') -> ' || rgroup(i));
1984 -- ========================= FND LOG ===========================
1985 else
1986
1987 begin
1988
1989 -- ========================= FND LOG ===========================
1990 psa_utils.debug_other_string(g_state_level,l_full_path,
1991 ' val_set(' || i || ') -> ' || val_set(i));
1992 psa_utils.debug_other_string(g_state_level,l_full_path,
1993 ' seg_type(' || i || ') -> ' || seg_type(i));
1994 -- ========================= FND LOG ===========================
1995
1996 open flexhid(val_set(i), seg_type(i));
1997
1998 fetch flexhid
1999 into rgroup(i);
2000
2001 -- ========================= FND LOG ===========================
2002 psa_utils.debug_other_string(g_state_level,l_full_path,
2003 ' rgroup(' || i || ') -> ' || rgroup(i));
2004 -- ========================= FND LOG ===========================
2005
2006 if flexhid%NOTFOUND then
2007
2008 -- Cannot find Hierarchy ID for this Rollup Group
2009
2010 message_token('HNAME', seg_type(i));
2011 message_token('TNAME', template_name);
2012 add_message('FND', 'FLEXGL-CANNOT FIND HCHY ID');
2013 -- ========================= FND LOG ===========================
2014 psa_utils.debug_other_string(g_state_level,l_full_path,
2015 ' Cannot find Hierarchy ID for this Rollup Group ');
2016 psa_utils.debug_other_string(g_state_level,l_full_path,
2017 ' goto return_invalid ');
2018 -- ========================= FND LOG ===========================
2019 goto return_invalid;
2020
2021 end if;
2022
2023 close flexhid;
2024
2025 end;
2026 end if;
2027
2028 end if;
2029
2030 -- ========================= FND LOG ===========================
2031 psa_utils.debug_other_string(g_state_level,l_full_path,' end loop ');
2032 -- ========================= FND LOG ===========================
2033
2034 end loop;
2035
2036 -- ========================= FND LOG ===========================
2037 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN -> TRUE');
2038 -- ========================= FND LOG ===========================
2039
2040 return(TRUE);
2041
2042 <<return_invalid>>
2043 -- ========================= FND LOG ===========================
2044 psa_utils.debug_other_string(g_state_level,l_full_path,' LABEL - return_invalid');
2045 -- ========================= FND LOG ===========================
2046
2047 if flexhid%ISOPEN then
2048 close flexhid;
2049 end if;
2050
2051 -- ========================= FND LOG ===========================
2052 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN -> FALSE');
2053 -- ========================= FND LOG ===========================
2054
2055 return(FALSE);
2056
2057
2058 EXCEPTION
2059
2060 WHEN OTHERS THEN
2061 rgroup := e_rgroup;
2062
2063 if flexhid%ISOPEN then
2064 close flexhid;
2065 end if;
2066
2067 message_token('MSG', 'glfcrg() exception:' || SQLERRM);
2068 add_message('FND', 'FLEX-SSV EXCEPTION');
2069
2070 -- ========================= FND LOG ===========================
2071 psa_utils.debug_other_string(g_state_level,l_full_path,' EXCEPTION WHEN OTHERS GLFCRG - '|| SQLERRM);
2072 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN -> FALSE');
2073 -- ========================= FND LOG ===========================
2074
2075 return(FALSE);
2076
2077 END glfcrg;
2078
2079
2080 -- Create Parent Accounts
2081
2082
2083 -- Called Routines :
2084
2085 -- message_token : Add Token and Value to the Message Token array
2086
2087 -- add_message : Update global Message String
2088
2089
2090 -- Arguments :
2091
2092 -- seg_type : Summary Template Segment Types
2093
2094 -- rgroup : Rollup Group for the Summary Template Segment Types
2095
2096 -- rgroup_sorted : Rollup Groups sorted by Rollup Group Scores
2097
2098 -- rgroup_ind : Segment Indices for the Sorted Rollup Group Scores
2099
2100 -- val_set : Value Set IDs for the Segments in the Code Combination
2101
2102 -- template_id : Template ID
2103
2104 -- lgr_id : Ledger ID
2105
2106 /*======================================================================================+
2107 | Bug 3805589 : This function glfcpc has been re-written to get rid of |
2108 | the shared pool overflow issue caused by the function |
2109 | |
2110 | The logic used is to create a PL/SQL table and dump the values to be inserted |
2111 | in this table. Later use the values from this table for inserting data. |
2112 | The original design had a cartesian join and hence the same is implemented in this |
2113 | change. The logic is implemented using 3 procedures initialize_values, assign_values |
2114 | and create_duplicates. Parameters passed to these procedures are as below |
2115 | |
2116 | p_row -> Row Number to be updated |
2117 | p_segment -> Segment to be be updated (eg. segment1, segment2 etc.) |
2118 | p_val -> Value to be updated |
2119 | p_status_code -> This is used to set the ledger_id |
2120 | |
2121 | We go on assigning the value to the PL/SQL table until we find multiple parents |
2122 | For the second parent we create duplicate rows and assign the new value to the |
2123 | duplicated rows. This is the way cartesian is established. |
2124 | Finally we insert all rows in the table and later delete the duplicate ones |
2125 | Earlier since DML was used we could make use of distinct clause but now since we are |
2126 | inserting via PL/SQL table we cant make use of that clause. Hence we insert all rows |
2127 | and later delete the duplicate ones |
2128 +======================================================================================*/
2129
2130
2131 FUNCTION glfcpc(seg_type IN SegTypeArray,
2132 rgroup IN SegRgrpArray,
2133 rgroup_sorted IN RgrpSrtArray,
2134 rgroup_ind IN RgrpIndArray,
2135 val_set IN SegVsetArray,
2136 template_id IN NUMBER,
2137 lgr_id IN NUMBER) RETURN BOOLEAN IS
2138
2139 -- Create a PL/SQL table which will have the same structure as that of the table
2140 -- it will update
2141 Type GDSC_Table IS TABLE OF GL_DYNAMIC_SUMM_COMBINATIONS%ROWTYPE;
2142 GDSC_Type GDSC_Table := GDSC_Table();
2143 l_dup_rows NUMBER; -- Bug 5265341
2144
2145 -- ========================= FND LOG ===========================
2146 l_full_path VARCHAR2(100) := g_path || 'glfcpc.';
2147 -- ========================= FND LOG ===========================
2148
2149 PROCEDURE assign_values (p_row NUMBER, p_segment NUMBER, p_val VARCHAR2, p_status_code VARCHAR2) IS
2150
2151 -- ========================= FND LOG ===========================
2152 l_full_path VARCHAR2(100) := g_path || 'assign_values.';
2153 -- ========================= FND LOG ===========================
2154
2155 BEGIN
2156
2157 -- ========================= FND LOG ===========================
2158 psa_utils.debug_other_string(g_state_level,l_full_path,' ');
2159 psa_utils.debug_other_string(g_state_level,l_full_path,' START assign_values ');
2160 psa_utils.debug_other_string(g_state_level,l_full_path,' ');
2161 psa_utils.debug_other_string(g_state_level,l_full_path,' PARAMETERS :');
2162 psa_utils.debug_other_string(g_state_level,l_full_path,' ============');
2163 psa_utils.debug_other_string(g_state_level,l_full_path,' p_row -->' || p_row );
2164 psa_utils.debug_other_string(g_state_level,l_full_path,' p_segment -->' || p_segment);
2165 psa_utils.debug_other_string(g_state_level,l_full_path,' p_val -->' || p_val);
2166 psa_utils.debug_other_string(g_state_level,l_full_path,' p_status_code -->' || p_status_code);
2167 -- ========================= FND LOG ===========================
2168
2169 IF (p_segment = 1) THEN
2170 GDSC_Type(p_row).SEGMENT1 := p_val;
2171 ELSIF (p_segment = 2) THEN
2172 GDSC_Type(p_row).SEGMENT2 := p_val;
2173 ELSIF (p_segment = 3) THEN
2174 GDSC_Type(p_row).SEGMENT3 := p_val;
2175 ELSIF (p_segment = 4) THEN
2176 GDSC_Type(p_row).SEGMENT4 := p_val;
2177 ELSIF (p_segment = 5) THEN
2178 GDSC_Type(p_row).SEGMENT5 := p_val;
2179 ELSIF (p_segment = 6) THEN
2180 GDSC_Type(p_row).SEGMENT6 := p_val;
2181 ELSIF (p_segment = 7) THEN
2182 GDSC_Type(p_row).SEGMENT7 := p_val;
2183 ELSIF (p_segment = 8) THEN
2184 GDSC_Type(p_row).SEGMENT8 := p_val;
2185 ELSIF (p_segment = 9) THEN
2186 GDSC_Type(p_row).SEGMENT9 := p_val;
2187 ELSIF (p_segment = 10) THEN
2188 GDSC_Type(p_row).SEGMENT10 := p_val;
2189 ELSIF (p_segment =11) THEN
2190 GDSC_Type(p_row).SEGMENT11 := p_val;
2191 ELSIF (p_segment = 12) THEN
2192 GDSC_Type(p_row).SEGMENT12 := p_val;
2193 ELSIF (p_segment = 13) THEN
2194 GDSC_Type(p_row).SEGMENT13 := p_val;
2195 ELSIF (p_segment = 14) THEN
2196 GDSC_Type(p_row).SEGMENT14 := p_val;
2197 ELSIF (p_segment = 15) THEN
2198 GDSC_Type(p_row).SEGMENT15 := p_val;
2199 ELSIF (p_segment = 16) THEN
2200 GDSC_Type(p_row).SEGMENT16 := p_val;
2201 ELSIF (p_segment = 17) THEN
2202 GDSC_Type(p_row).SEGMENT17 := p_val;
2203 ELSIF (p_segment = 18) THEN
2204 GDSC_Type(p_row).SEGMENT18 := p_val;
2205 ELSIF (p_segment = 19) THEN
2206 GDSC_Type(p_row).SEGMENT19 := p_val;
2207 ELSIF (p_segment = 20) THEN
2208 GDSC_Type(p_row).SEGMENT20 := p_val;
2209 ELSIF (p_segment = 21) THEN
2210 GDSC_Type(p_row).SEGMENT21 := p_val;
2211 ELSIF (p_segment = 22) THEN
2212 GDSC_Type(p_row).SEGMENT22 := p_val;
2213 ELSIF (p_segment = 23) THEN
2214 GDSC_Type(p_row).SEGMENT23 := p_val;
2215 ELSIF (p_segment = 24) THEN
2216 GDSC_Type(p_row).SEGMENT24 := p_val;
2217 ELSIF (p_segment = 25) THEN
2218 GDSC_Type(p_row).SEGMENT25 := p_val;
2219 ELSIF (p_segment = 26) THEN
2220 GDSC_Type(p_row).SEGMENT26 := p_val;
2221 ELSIF (p_segment = 27) THEN
2222 GDSC_Type(p_row).SEGMENT27 := p_val;
2223 ELSIF (p_segment = 28) THEN
2224 GDSC_Type(p_row).SEGMENT28 := p_val;
2225 ELSIF (p_segment = 29) THEN
2226 GDSC_Type(p_row).SEGMENT29 := p_val;
2227 ELSIF (p_segment = 30) THEN
2228 GDSC_Type(p_row).SEGMENT30 := p_val;
2229 END IF;
2230
2231 -- ========================= FND LOG ===========================
2232 psa_utils.debug_other_string(g_state_level,l_full_path,
2233 'GDSC_Type(' || p_row || ').SEGMENT' || p_segment ||' ->' || p_val);
2234 psa_utils.debug_other_string(g_state_level,l_full_path,
2235 'p_status_code -> ' || p_status_code);
2236 psa_utils.debug_other_string(g_state_level,l_full_path,
2237 'GDSC_Type(' || p_row || ').LEDGER_ID ->' || GDSC_Type(p_row).LEDGER_ID);
2238 -- ========================= FND LOG ===========================
2239
2240 IF (p_status_code = 'I') AND (GDSC_Type(p_row).LEDGER_ID IS NULL) THEN
2241 GDSC_Type(p_row).LEDGER_ID := -lgr_id;
2242 GDSC_Type(p_row).TEMPLATE_ID := -template_id;
2243 -- ========================= FND LOG ===========================
2244 psa_utils.debug_other_string(g_state_level,l_full_path,
2245 'GDSC_Type(' || p_row || ').LEDGER_ID -> -' || lgr_id);
2246 psa_utils.debug_other_string(g_state_level,l_full_path,
2247 'GDSC_Type(' || p_row || ').TEMPLATE_ID -> -' || template_id);
2248 -- ========================= FND LOG ===========================
2249 END IF;
2250
2251
2252 -- ========================= FND LOG ===========================
2253 psa_utils.debug_other_string(g_state_level,l_full_path,' END assign_values ');
2254 -- ========================= FND LOG ===========================
2255
2256 END assign_values;
2257
2258 PROCEDURE initialize_values (p_segment number, p_val VARCHAR2, p_status_code VARCHAR2) IS
2259
2260 -- ========================= FND LOG ===========================
2261 l_full_path VARCHAR2(100) := g_path || 'initialize_values.';
2262 -- ========================= FND LOG ===========================
2263
2264 BEGIN
2265
2266 -- ========================= FND LOG ===========================
2267 psa_utils.debug_other_string(g_state_level,l_full_path,' ');
2268 psa_utils.debug_other_string(g_state_level,l_full_path,' START initialize_values ');
2269 psa_utils.debug_other_string(g_state_level,l_full_path,' ');
2270 psa_utils.debug_other_string(g_state_level,l_full_path,' PARAMETERS :');
2271 psa_utils.debug_other_string(g_state_level,l_full_path,' ============');
2272 psa_utils.debug_other_string(g_state_level,l_full_path,' p_segment -->' || p_segment);
2273 psa_utils.debug_other_string(g_state_level,l_full_path,' p_val -->' || p_val);
2274 psa_utils.debug_other_string(g_state_level,l_full_path,' p_status_code -->' || p_status_code);
2275 -- ========================= FND LOG ===========================
2276
2277 FOR cntr IN GDSC_Type.FIRST .. GDSC_Type.LAST LOOP
2278 -- ========================= FND LOG ===========================
2279 psa_utils.debug_other_string(g_state_level,l_full_path,' Calling assign_values ');
2280 -- ========================= FND LOG ===========================
2281 assign_values(cntr, p_segment, p_val, p_status_code);
2282
2283 END LOOP;
2284
2285 -- ========================= FND LOG ===========================
2286 psa_utils.debug_other_string(g_state_level,l_full_path,' END initialize_values ');
2287 -- ========================= FND LOG ===========================
2288
2289 END initialize_values;
2290
2291 PROCEDURE create_duplicates (p_segment NUMBER, p_val VARCHAR2, p_status_code VARCHAR2) IS
2292
2293 l_curr_cnt NUMBER;
2294 -- ========================= FND LOG ===========================
2295 l_full_path VARCHAR2(100) := g_path || 'create_duplicates.';
2296 -- ========================= FND LOG ===========================
2297
2298 BEGIN
2299
2300 -- ========================= FND LOG ===========================
2301 psa_utils.debug_other_string(g_state_level,l_full_path,' ');
2302 psa_utils.debug_other_string(g_state_level,l_full_path,' START create_duplicates ');
2303 psa_utils.debug_other_string(g_state_level,l_full_path,' ');
2304 psa_utils.debug_other_string(g_state_level,l_full_path,' PARAMETERS :');
2305 psa_utils.debug_other_string(g_state_level,l_full_path,' ============');
2306 psa_utils.debug_other_string(g_state_level,l_full_path,' p_segment -->' || p_segment);
2307 psa_utils.debug_other_string(g_state_level,l_full_path,' p_val -->' || p_val);
2308 psa_utils.debug_other_string(g_state_level,l_full_path,' p_status_code -->' || p_status_code);
2309 -- ========================= FND LOG ===========================
2310
2311 l_curr_cnt := GDSC_Type.COUNT;
2312
2313 -- ========================= FND LOG ===========================
2314 psa_utils.debug_other_string(g_state_level,l_full_path,' l_curr_cnt -->' || l_curr_cnt);
2315 psa_utils.debug_other_string(g_state_level,l_full_path,' l_dup_rows -->' || l_dup_rows); -- Bug 5265341
2316 -- ========================= FND LOG ===========================
2317
2318 GDSC_Type.Extend(l_dup_rows); -- Bug 5265341
2319
2320 -- ========================= FND LOG ===========================
2321 psa_utils.debug_other_string(g_state_level,l_full_path,' GDSC_Type.COUNT extended-->' || GDSC_Type.COUNT);
2322 -- ========================= FND LOG ===========================
2323
2324
2325 FOR cntr IN 1..l_dup_rows LOOP -- Bug 5265341
2326 GDSC_Type(l_curr_cnt + cntr) := GDSC_Type(cntr);
2327 -- ========================= FND LOG ===========================
2328 psa_utils.debug_other_string(g_state_level,l_full_path,' GDSC_Type(' || cntr || ')');
2329 -- psa_utils.debug_other_string(g_state_level,l_full_path,
2330 -- ' GDSC_Type(' || l_curr_cnt + cntr || ') := GDSC_Type(' || cntr || ')');
2331 psa_utils.debug_other_string(g_state_level,l_full_path,' Calling assign_values');
2332 -- ========================= FND LOG ===========================
2333
2334 assign_values(l_curr_cnt + cntr, p_segment, p_val, p_status_code);
2335
2336 END LOOP;
2337
2338 -- ========================= FND LOG ===========================
2339 psa_utils.debug_other_string(g_state_level,l_full_path,' END create_duplicates ');
2340 -- ========================= FND LOG ===========================
2341
2342 END create_duplicates;
2343
2344
2345 BEGIN
2346
2347 -- ========================= FND LOG ===========================
2348 psa_utils.debug_other_string(g_state_level,l_full_path,' ');
2349 psa_utils.debug_other_string(g_state_level,l_full_path,' START glfcpc ');
2350 psa_utils.debug_other_string(g_state_level,l_full_path,' ');
2351 psa_utils.debug_other_string(g_state_level,l_full_path,' PARAMETERS :');
2352 psa_utils.debug_other_string(g_state_level,l_full_path,' ============');
2353 psa_utils.debug_other_string(g_state_level,l_full_path,' template_id -->' || template_id);
2354 psa_utils.debug_other_string(g_state_level,l_full_path,' lgr_id -->' || lgr_id);
2355 -- ========================= FND LOG ===========================
2356
2357 -- Creating the first row. Rows are later extended as required.
2358 GDSC_Type.Extend(1);
2359 l_dup_rows := 1; -- Bug 5265341
2360
2361 -- Loop through 30 times and assign the values to PL/SQL table columns
2362 for i in 1..30 loop
2363
2364 -- ========================= FND LOG ===========================
2365 psa_utils.debug_other_string(g_state_level,l_full_path,' rgroup(' || i || ') -> ' || rgroup(i));
2366 -- ========================= FND LOG ===========================
2367
2368 if rgroup(i) is not null then
2369
2370 if (rgroup(i) = 'D') then
2371 -- ========================= FND LOG ===========================
2372 psa_utils.debug_other_string(g_state_level,l_full_path,' Calling initialize_values- D');
2373 -- ========================= FND LOG ===========================
2374 initialize_values(i, seg_val(i), NULL);
2375 elsif (rgroup(i) = 'T') then
2376 -- ========================= FND LOG ===========================
2377 psa_utils.debug_other_string(g_state_level,l_full_path,' Calling initialize_values - T');
2378 -- ========================= FND LOG ===========================
2379 initialize_values(i, 'T', NULL);
2380 else
2381 -- ========================= FND LOG ===========================
2382 psa_utils.debug_other_string(g_state_level,l_full_path,' IN the else part ');
2383 -- ========================= FND LOG ===========================
2384
2385 --
2386 -- Bug 4143033
2387 -- ***********
2388 -- c_get_parents modified:
2389 -- 1. join on value_set_id
2390 -- 2. utilization of new index on gl_summary_hierarchies
2391 -- Bug 4191758
2392 -- ***********
2393 -- Added 'distinct' to select of c_get_parents to prevent excessive
2394 -- looping.
2395 --
2396
2397 DECLARE
2398 CURSOR c_get_parents IS
2399 SELECT DISTINCT gsh.status_code, fv.flex_value
2400 FROM gl_summary_hierarchies gsh, fnd_flex_values fv
2401 WHERE gsh.flex_value_set_id = fv.flex_value_set_id
2402 AND gsh.parent_flex_value = fv.flex_value
2403 AND (seg_val(rgroup_ind(i)) between gsh.child_flex_value_low
2404 and gsh.child_flex_value_high)
2405 AND gsh.flex_value_set_id = val_set(rgroup_ind(i))
2406 AND fv.flex_value_set_id = val_set(rgroup_ind(i))
2407 AND fv.structured_hierarchy_level = rgroup(rgroup_ind(i))
2408 AND fv.enabled_flag = 'Y';
2409
2410 l_curr_val fnd_flex_values.flex_value%type;
2411 l_status_code gl_summary_hierarchies.status_code%type;
2412 BEGIN
2413
2414 -- ========================= FND LOG ===========================
2415 psa_utils.debug_other_string(g_state_level,l_full_path,
2416 ' With in pl/sql block ');
2417 psa_utils.debug_other_string(g_state_level,l_full_path,
2418 'seg_val(rgroup_ind(' || i || ')) -> ' || seg_val(rgroup_ind(i)));
2419 psa_utils.debug_other_string(g_state_level,l_full_path,
2420 'val_set(rgroup_ind(' || i || ')) -> ' || val_set(rgroup_ind(i)));
2421 psa_utils.debug_other_string(g_state_level,l_full_path,
2422 ' rgroup_ind(' || i || ') -> ' || rgroup_ind(i));
2423 -- ========================= FND LOG ===========================
2424
2425 OPEN c_get_parents;
2426 LOOP
2427
2428 FETCH c_get_parents INTO l_status_code, l_curr_val;
2429
2430 -- ========================= FND LOG ===========================
2431 psa_utils.debug_other_string(g_state_level,l_full_path,
2432 ' l_status_code -> ' || l_status_code);
2433 psa_utils.debug_other_string(g_state_level,l_full_path,
2434 ' l_curr_val -> ' || l_curr_val);
2435 psa_utils.debug_other_string(g_state_level,l_full_path,
2436 ' c_get_parents%ROWCOUNT -> ' || c_get_parents%ROWCOUNT);
2437 -- ========================= FND LOG ===========================
2438
2439 EXIT WHEN c_get_parents%NOTFOUND;
2440
2441
2442 IF (c_get_parents%ROWCOUNT) = 1 THEN
2443 -- ========================= FND LOG ===========================
2444 psa_utils.debug_other_string(g_state_level,l_full_path,' Calling initialize_values');
2445 -- ========================= FND LOG ===========================
2446 -- Since this is only first record, just set this value to all the existing rows
2447 initialize_values(i, l_curr_val, l_status_code);
2448 l_dup_rows := GDSC_TYPE.COUNT; -- Bug 5265341
2449
2450 -- ========================= FND LOG ===========================
2451 psa_utils.debug_other_string(g_state_level,l_full_path,' l_dup_rows set to -->' || l_dup_rows); -- Bug 5265341
2452 -- ========================= FND LOG ===========================
2453
2454 ELSIF (c_get_parents%ROWCOUNT > 1) THEN
2455 -- ========================= FND LOG ===========================
2456 psa_utils.debug_other_string(g_state_level,l_full_path,'create_duplicates ');
2457 -- ========================= FND LOG ===========================
2458 -- Since this is the second parent found, first duplicate the existing rows and
2459 -- assign the new value to the duplicates
2460 create_duplicates(i, l_curr_val, l_status_code);
2461
2462 END IF;
2463
2464 END LOOP;
2465
2466
2467 IF (c_get_parents%ROWCOUNT = 0) THEN
2468 -- ========================= FND LOG ===========================
2469 psa_utils.debug_other_string(g_state_level,l_full_path,' No rows found ');
2470 -- ========================= FND LOG ===========================
2471 -- Since query fetched now rows return TRUE
2472 CLOSE c_get_parents;
2473 -- ========================= FND LOG ===========================
2474 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN -> TRUE ');
2475 -- ========================= FND LOG ===========================
2476 return (TRUE);
2477 END IF;
2478
2479 CLOSE c_get_parents;
2480
2481 EXCEPTION
2482 WHEN OTHERS THEN
2483 -- ========================= FND LOG ===========================
2484 psa_utils.debug_other_string(g_state_level,l_full_path,' EXCEPTION IN pl/sql block ');
2485 psa_utils.debug_other_string(g_state_level,l_full_path,' - ' || SQLERRM);
2486 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN -> FALSE ');
2487 -- ========================= FND LOG ===========================
2488 return (FALSE);
2489 END;
2490
2491 end if;
2492
2493 -- ========================= FND LOG ===========================
2494 psa_utils.debug_other_string(g_state_level,l_full_path,' rgroup(' || i || ') -> ' || rgroup(i));
2495 -- ========================= FND LOG ===========================
2496
2497 end if;
2498
2499 end loop;
2500
2501 -- Insert the record using the PL/SQL table thus using bind variables.
2502 -- This will also insert duplicate rows. The duplicates are later removed using a delete
2503 -- statement. This is again to overcome a PL/SQL table limitation
2504
2505 FOR i IN 1..GDSC_Type.COUNT LOOP
2506
2507 -- ========================= FND LOG ===========================
2508 psa_utils.debug_other_string(g_state_level,l_full_path,' Inserting into gl_dynamic_summ_combinations');
2509 -- ========================= FND LOG ===========================
2510
2511 insert into gl_dynamic_summ_combinations (
2512 dynamic_group_id,
2513 code_combination_id,
2514 last_update_date,
2515 last_updated_by,
2516 segment1,
2517 segment2,
2518 segment3,
2519 segment4,
2520 segment5,
2521 segment6,
2522 segment7,
2523 segment8,
2524 segment9,
2525 segment10,
2526 segment11,
2527 segment12,
2528 segment13,
2529 segment14,
2530 segment15,
2531 segment16,
2532 segment17,
2533 segment18,
2534 segment19,
2535 segment20,
2536 segment21,
2537 segment22,
2538 segment23,
2539 segment24,
2540 segment25,
2541 segment26,
2542 segment27,
2543 segment28,
2544 segment29,
2545 segment30,
2546 ledger_id,
2547 template_id
2548 )
2549 VALUES (
2550 dyn_grp_id,
2551 -1,
2552 sysdate,
2553 user_id,
2554 GDSC_Type(i).SEGMENT1,
2555 GDSC_Type(i).SEGMENT2,
2556 GDSC_Type(i).SEGMENT3,
2557 GDSC_Type(i).SEGMENT4,
2558 GDSC_Type(i).SEGMENT5,
2559 GDSC_Type(i).SEGMENT6,
2560 GDSC_Type(i).SEGMENT7,
2561 GDSC_Type(i).SEGMENT8,
2562 GDSC_Type(i).SEGMENT9,
2563 GDSC_Type(i).SEGMENT10,
2564 GDSC_Type(i).SEGMENT11,
2565 GDSC_Type(i).SEGMENT12,
2566 GDSC_Type(i).SEGMENT13,
2567 GDSC_Type(i).SEGMENT14,
2568 GDSC_Type(i).SEGMENT15,
2569 GDSC_Type(i).SEGMENT16,
2570 GDSC_Type(i).SEGMENT17,
2571 GDSC_Type(i).SEGMENT18,
2572 GDSC_Type(i).SEGMENT19,
2573 GDSC_Type(i).SEGMENT20,
2574 GDSC_Type(i).SEGMENT21,
2575 GDSC_Type(i).SEGMENT22,
2576 GDSC_Type(i).SEGMENT23,
2577 GDSC_Type(i).SEGMENT24,
2578 GDSC_Type(i).SEGMENT25,
2579 GDSC_Type(i).SEGMENT26,
2580 GDSC_Type(i).SEGMENT27,
2581 GDSC_Type(i).SEGMENT28,
2582 GDSC_Type(i).SEGMENT29,
2583 GDSC_Type(i).SEGMENT30,
2584 DECODE(GDSC_Type(i).LEDGER_ID, NULL, lgr_id, GDSC_Type(i).LEDGER_ID),
2585 DECODE(GDSC_Type(i).TEMPLATE_ID, NULL, template_id, GDSC_Type(i).TEMPLATE_ID)
2586 );
2587
2588 -- ========================= FND LOG ===========================
2589 psa_utils.debug_other_string(g_state_level,l_full_path,' ####### START ');
2590 psa_utils.debug_other_string(g_state_level,l_full_path,' dynamic_group_id ->' || dyn_grp_id);
2591 psa_utils.debug_other_string(g_state_level,l_full_path,' code_combination_id -> -1');
2592 psa_utils.debug_other_string(g_state_level,l_full_path,' GDSC_Type(' || i || ').SEGMENT1 ->' || GDSC_Type(i).SEGMENT1);
2593 psa_utils.debug_other_string(g_state_level,l_full_path,' GDSC_Type(' || i || ').SEGMENT2 ->' || GDSC_Type(i).SEGMENT2);
2594 psa_utils.debug_other_string(g_state_level,l_full_path,' GDSC_Type(' || i || ').SEGMENT3 ->' || GDSC_Type(i).SEGMENT3);
2595 psa_utils.debug_other_string(g_state_level,l_full_path,' GDSC_Type(' || i || ').SEGMENT4 ->' || GDSC_Type(i).SEGMENT4);
2596 psa_utils.debug_other_string(g_state_level,l_full_path,' GDSC_Type(' || i || ').SEGMENT5 ->' || GDSC_Type(i).SEGMENT5);
2597 psa_utils.debug_other_string(g_state_level,l_full_path,' GDSC_Type(' || i || ').SEGMENT6 ->' || GDSC_Type(i).SEGMENT6);
2598 psa_utils.debug_other_string(g_state_level,l_full_path,' GDSC_Type(' || i || ').SEGMENT7 ->' || GDSC_Type(i).SEGMENT7);
2599 psa_utils.debug_other_string(g_state_level,l_full_path,' GDSC_Type(' || i || ').SEGMENT8 ->' || GDSC_Type(i).SEGMENT8);
2600 psa_utils.debug_other_string(g_state_level,l_full_path,' GDSC_Type(' || i || ').SEGMENT9 ->' || GDSC_Type(i).SEGMENT9);
2601 psa_utils.debug_other_string(g_state_level,l_full_path,' GDSC_Type(' || i || ').SEGMENT10 ->' || GDSC_Type(i).SEGMENT10);
2602 psa_utils.debug_other_string(g_state_level,l_full_path,' GDSC_Type(' || i || ').SEGMENT11 ->' || GDSC_Type(i).SEGMENT11);
2603 psa_utils.debug_other_string(g_state_level,l_full_path,' GDSC_Type(' || i || ').SEGMENT12 ->' || GDSC_Type(i).SEGMENT12);
2604 psa_utils.debug_other_string(g_state_level,l_full_path,' GDSC_Type(' || i || ').SEGMENT13 ->' || GDSC_Type(i).SEGMENT13);
2605 psa_utils.debug_other_string(g_state_level,l_full_path,' GDSC_Type(' || i || ').SEGMENT14 ->' || GDSC_Type(i).SEGMENT14);
2606 psa_utils.debug_other_string(g_state_level,l_full_path,' GDSC_Type(' || i || ').SEGMENT15 ->' || GDSC_Type(i).SEGMENT15);
2607 psa_utils.debug_other_string(g_state_level,l_full_path,' GDSC_Type(' || i || ').SEGMENT16 ->' || GDSC_Type(i).SEGMENT16);
2608 psa_utils.debug_other_string(g_state_level,l_full_path,' GDSC_Type(' || i || ').SEGMENT17 ->' || GDSC_Type(i).SEGMENT17);
2609 psa_utils.debug_other_string(g_state_level,l_full_path,' GDSC_Type(' || i || ').SEGMENT18 ->' || GDSC_Type(i).SEGMENT18);
2610 psa_utils.debug_other_string(g_state_level,l_full_path,' GDSC_Type(' || i || ').SEGMENT19 ->' || GDSC_Type(i).SEGMENT19);
2611 psa_utils.debug_other_string(g_state_level,l_full_path,' GDSC_Type(' || i || ').SEGMENT20 ->' || GDSC_Type(i).SEGMENT20);
2612 psa_utils.debug_other_string(g_state_level,l_full_path,' GDSC_Type(' || i || ').SEGMENT21 ->' || GDSC_Type(i).SEGMENT21);
2613 psa_utils.debug_other_string(g_state_level,l_full_path,' GDSC_Type(' || i || ').SEGMENT22 ->' || GDSC_Type(i).SEGMENT22);
2614 psa_utils.debug_other_string(g_state_level,l_full_path,' GDSC_Type(' || i || ').SEGMENT23 ->' || GDSC_Type(i).SEGMENT23);
2615 psa_utils.debug_other_string(g_state_level,l_full_path,' GDSC_Type(' || i || ').SEGMENT24 ->' || GDSC_Type(i).SEGMENT24);
2616 psa_utils.debug_other_string(g_state_level,l_full_path,' GDSC_Type(' || i || ').SEGMENT25 ->' || GDSC_Type(i).SEGMENT25);
2617 psa_utils.debug_other_string(g_state_level,l_full_path,' GDSC_Type(' || i || ').SEGMENT26 ->' || GDSC_Type(i).SEGMENT26);
2618 psa_utils.debug_other_string(g_state_level,l_full_path,' GDSC_Type(' || i || ').SEGMENT27 ->' || GDSC_Type(i).SEGMENT27);
2619 psa_utils.debug_other_string(g_state_level,l_full_path,' GDSC_Type(' || i || ').SEGMENT28 ->' || GDSC_Type(i).SEGMENT28);
2620 psa_utils.debug_other_string(g_state_level,l_full_path,' GDSC_Type(' || i || ').SEGMENT29 ->' || GDSC_Type(i).SEGMENT29);
2621 psa_utils.debug_other_string(g_state_level,l_full_path,' GDSC_Type(' || i || ').SEGMENT30 ->' || GDSC_Type(i).SEGMENT30);
2622 IF GDSC_Type(i).LEDGER_ID IS NULL THEN
2623 psa_utils.debug_other_string(g_state_level,l_full_path,
2624 ' GDSC_Type(' || i || ').LEDGER_ID - lgr_id ->' || lgr_id);
2625 ELSE
2626 psa_utils.debug_other_string(g_state_level,l_full_path,
2627 ' GDSC_Type(' || i || ').LEDGER_ID ->' || GDSC_Type(i).LEDGER_ID);
2628 END IF;
2629 IF GDSC_Type(i).TEMPLATE_ID IS NULL THEN
2630 psa_utils.debug_other_string(g_state_level,l_full_path,
2631 ' GDSC_Type(' || i || ').TEMPLATE_ID - template_id ->' || template_id);
2632 ELSE
2633 psa_utils.debug_other_string(g_state_level,l_full_path,
2634 ' GDSC_Type(' || i || ').TEMPLATE_ID ->' || GDSC_Type(i).TEMPLATE_ID);
2635 END IF;
2636 psa_utils.debug_other_string(g_state_level,l_full_path,' ####### END');
2637 -- ========================= FND LOG ===========================
2638
2639
2640 END LOOP;
2641
2642 -- ========================= FND LOG ===========================
2643 psa_utils.debug_other_string(g_state_level,l_full_path,' DELETEING DUPLICATE ROWS.');
2644 -- ========================= FND LOG ===========================
2645
2646
2647 -- Delete the duplicate rows for this dynamic group id.
2648 -- This will not delete rows which have negative lgr and template ids
2649 DELETE FROM gl_dynamic_summ_combinations
2650 WHERE rowid NOT IN (SELECT min(rowid)
2651 FROM gl_dynamic_summ_combinations
2652 WHERE dynamic_group_id = dyn_grp_id
2653 GROUP BY dynamic_group_id,
2654 ledger_id,
2655 template_id,
2656 segment1,
2657 segment2,
2658 segment3,
2659 segment4,
2660 segment5,
2661 segment6,
2662 segment7,
2663 segment8,
2664 segment9,
2665 segment10,
2666 segment11,
2667 segment12,
2668 segment13,
2669 segment14,
2670 segment15,
2671 segment16,
2672 segment17,
2673 segment18,
2674 segment19,
2675 segment20,
2676 segment21,
2677 segment22,
2678 segment23,
2679 segment24,
2680 segment25,
2681 segment26,
2682 segment27,
2683 segment28,
2684 segment29,
2685 segment30)
2686 and dynamic_group_id = dyn_grp_id;
2687
2688 -- ========================= FND LOG ===========================
2689 psa_utils.debug_other_string(g_state_level,l_full_path,
2690 ' DELETE FROM gl_dynamic_summ_combinations -> ' || SQL%ROWCOUNT);
2691 -- ========================= FND LOG ===========================
2692
2693 -- bug 4130352 start
2694
2695 -- delete duplicate negative ledger_id/template_id rows where there is a
2696 -- matching positive ledger_id/template_id row for this dyn_grp_id
2697 -- having the same segment values.
2698 -- CCID at this point cannot be relied upon as it could be -1
2699
2700 FOR crec in (SELECT abs(ledger_id) ledger_id,
2701 abs(template_id) template_id,
2702 segment1, segment2, segment3,
2703 segment4, segment5, segment6,
2704 segment7, segment8, segment9,
2705 segment10, segment11, segment12,
2706 segment13, segment14, segment15,
2707 segment16, segment17, segment18,
2708 segment19, segment20, segment21,
2709 segment22, segment23, segment24,
2710 segment25, segment26, segment27,
2711 segment28, segment29, segment30
2712 FROM gl_dynamic_summ_combinations
2713 WHERE dynamic_group_id = dyn_grp_id
2714 GROUP BY dynamic_group_id,
2715 abs(ledger_id),
2716 abs(template_id),
2717 segment1, segment2, segment3,
2718 segment4, segment5, segment6,
2719 segment7, segment8, segment9,
2720 segment10, segment11, segment12,
2721 segment13, segment14, segment15,
2722 segment16, segment17, segment18,
2723 segment19, segment20, segment21,
2724 segment22, segment23, segment24,
2725 segment25, segment26, segment27,
2726 segment28, segment29, segment30
2727 HAVING count(*) > 1)
2728 LOOP
2729
2730 -- ========================= FND LOG ===========================
2731 psa_utils.debug_other_string(g_state_level,l_full_path,' Inside crec cursor');
2732 -- ========================= FND LOG ===========================
2733
2734 DELETE from gl_dynamic_summ_combinations
2735 WHERE ledger_id = -1 * crec.ledger_id
2736 AND template_id = -1 * crec.template_id
2737 AND dynamic_group_id = dyn_grp_id
2738 AND nvl(segment1,g_segment_nvl_value) = nvl(crec.segment1,g_segment_nvl_value)
2739 AND nvl(segment2,g_segment_nvl_value) = nvl(crec.segment2,g_segment_nvl_value)
2740 AND nvl(segment3,g_segment_nvl_value) = nvl(crec.segment3,g_segment_nvl_value)
2741 AND nvl(segment4,g_segment_nvl_value) = nvl(crec.segment4,g_segment_nvl_value)
2742 AND nvl(segment5,g_segment_nvl_value) = nvl(crec.segment5,g_segment_nvl_value)
2743 AND nvl(segment6,g_segment_nvl_value) = nvl(crec.segment6,g_segment_nvl_value)
2744 AND nvl(segment7,g_segment_nvl_value) = nvl(crec.segment7,g_segment_nvl_value)
2745 AND nvl(segment8,g_segment_nvl_value) = nvl(crec.segment8,g_segment_nvl_value)
2746 AND nvl(segment9,g_segment_nvl_value) = nvl(crec.segment9,g_segment_nvl_value)
2747 AND nvl(segment10,g_segment_nvl_value) = nvl(crec.segment10,g_segment_nvl_value)
2748 AND nvl(segment11,g_segment_nvl_value) = nvl(crec.segment11,g_segment_nvl_value)
2749 AND nvl(segment12,g_segment_nvl_value) = nvl(crec.segment12,g_segment_nvl_value)
2750 AND nvl(segment13,g_segment_nvl_value) = nvl(crec.segment13,g_segment_nvl_value)
2751 AND nvl(segment14,g_segment_nvl_value) = nvl(crec.segment14,g_segment_nvl_value)
2752 AND nvl(segment15,g_segment_nvl_value) = nvl(crec.segment15,g_segment_nvl_value)
2753 AND nvl(segment16,g_segment_nvl_value) = nvl(crec.segment16,g_segment_nvl_value)
2754 AND nvl(segment17,g_segment_nvl_value) = nvl(crec.segment17,g_segment_nvl_value)
2755 AND nvl(segment18,g_segment_nvl_value) = nvl(crec.segment18,g_segment_nvl_value)
2756 AND nvl(segment19,g_segment_nvl_value) = nvl(crec.segment19,g_segment_nvl_value)
2757 AND nvl(segment20,g_segment_nvl_value) = nvl(crec.segment20,g_segment_nvl_value)
2758 AND nvl(segment21,g_segment_nvl_value) = nvl(crec.segment21,g_segment_nvl_value)
2759 AND nvl(segment22,g_segment_nvl_value) = nvl(crec.segment22,g_segment_nvl_value)
2760 AND nvl(segment23,g_segment_nvl_value) = nvl(crec.segment23,g_segment_nvl_value)
2761 AND nvl(segment24,g_segment_nvl_value) = nvl(crec.segment24,g_segment_nvl_value)
2762 AND nvl(segment25,g_segment_nvl_value) = nvl(crec.segment25,g_segment_nvl_value)
2763 AND nvl(segment26,g_segment_nvl_value) = nvl(crec.segment26,g_segment_nvl_value)
2764 AND nvl(segment27,g_segment_nvl_value) = nvl(crec.segment27,g_segment_nvl_value)
2765 AND nvl(segment28,g_segment_nvl_value) = nvl(crec.segment28,g_segment_nvl_value)
2766 AND nvl(segment29,g_segment_nvl_value) = nvl(crec.segment29,g_segment_nvl_value)
2767 AND nvl(segment30,g_segment_nvl_value) = nvl(crec.segment30,g_segment_nvl_value);
2768
2769 -- ========================= FND LOG ===========================
2770 psa_utils.debug_other_string(g_state_level,l_full_path,
2771 ' DELETE FROM gl_dynamic_summ_combinations II -> ' || SQL%ROWCOUNT);
2772 -- ========================= FND LOG ===========================
2773
2774 END LOOP;
2775
2776 --bug 4130352 end
2777
2778 -- ========================= FND LOG ===========================
2779 psa_utils.debug_other_string(g_state_level,l_full_path,'RETURN -> TRUE');
2780 -- ========================= FND LOG ===========================
2781
2782 return (TRUE);
2783
2784 EXCEPTION
2785
2786 WHEN OTHERS THEN
2787 -- ========================= FND LOG ===========================
2788 psa_utils.debug_other_string(g_state_level,l_full_path,' EXCEPTION WHEN OTHERS - glfcpc ');
2789 psa_utils.debug_other_string(g_state_level,l_full_path,' - ' || SQLERRM);
2790 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN -> FALSE');
2791 -- ========================= FND LOG ===========================
2792
2793 -- Dynamic SQL Exception
2794 message_token('MSG', SQLERRM);
2795 message_token('SQLSTR', 'INSERT INTO GL_DYNAMIC_SUMM_COMBINATIONS ...');
2796 add_message('FND', 'FLEXGL-DSQL EXCEPTION');
2797
2798 return(FALSE);
2799
2800 END glfcpc;
2801
2802 /* ------------------------------------------------------------------------- */
2803
2804 -- Lock Summary Templates
2805
2806
2807 -- Called Routines :
2808
2809 -- message_token : Add Token and Value to the Message Token array
2810
2811 -- add_message : Update global Message String
2812
2813
2814 FUNCTION glflst RETURN BOOLEAN IS
2815
2816 tmpmsg VARCHAR2(100);
2817
2818 cursor lockst(dyn_grp_id NUMBER) is
2819 select 'Obtain Row Share Lock on the ' ||
2820 'corresponding record of this template in ' ||
2821 'gl_concurrency_control'
2822 from gl_concurrency_control ct
2823 where ct.concurrency_class = 'INSERT_SUMMARY_ACCOUNTS'
2824 and ct.concurrency_entity_name = 'SUMMARY_TEMPLATE'
2825 and exists (
2826 select 1
2827 from gl_dynamic_summ_combinations dsc
2828 where to_char(abs(dsc.template_id)) = ct.concurrency_entity_id
2829 and dsc.dynamic_group_id = dyn_grp_id
2830 )
2831 FOR UPDATE;
2832 -- FOR UPDATE NOWAIT; -- Bug 4074489
2833
2834 -- ========================= FND LOG ===========================
2835 l_full_path VARCHAR2(100) := g_path || 'glflst.';
2836 -- ========================= FND LOG ===========================
2837
2838 BEGIN
2839
2840 -- ========================= FND LOG ===========================
2841 psa_utils.debug_other_string(g_state_level,l_full_path,' START glflst ');
2842 psa_utils.debug_other_string(g_state_level,l_full_path,' dyn_grp_id -> ' || dyn_grp_id);
2843 -- ========================= FND LOG ===========================
2844
2845 -- All rows are locked when the Cursor is opened; these rows are unlocked
2846 -- after commit or rollback of the fdfgli routine
2847
2848 open lockst(dyn_grp_id);
2849
2850 fetch lockst
2851 into tmpmsg;
2852
2853 close lockst;
2854
2855 -- ========================= FND LOG ===========================
2856 psa_utils.debug_other_string(g_state_level,l_full_path,' LOCKING gl_concurrency_control ');
2857 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN -> TRUE ');
2858 -- ========================= FND LOG ===========================
2859
2860 return(TRUE);
2861
2862 EXCEPTION
2863
2864 WHEN NO_DATA_FOUND THEN
2865
2866 if lockst%ISOPEN then
2867 close lockst;
2868 end if;
2869
2870 message_token('MSG', 'glflst() exception:' || SQLERRM);
2871 add_message('FND', 'FLEX-SSV EXCEPTION');
2872
2873 -- ========================= FND LOG ===========================
2874 psa_utils.debug_other_string(g_state_level,l_full_path,' EXCEPTION NO_DATA_FOUND - glflst ');
2875 psa_utils.debug_other_string(g_state_level,l_full_path,' - ' || SQLERRM);
2876 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN -> FALSE');
2877 -- ========================= FND LOG ===========================
2878
2879 return(FALSE);
2880
2881 WHEN OTHERS THEN
2882
2883 if lockst%ISOPEN then
2884 close lockst;
2885 end if;
2886
2887 message_token('TABLE', 'GL_CONCURRENCY_CONTROL');
2888 add_message('FND', 'FORM-CANNOT LOCK');
2889
2890 -- ========================= FND LOG ===========================
2891 psa_utils.debug_other_string(g_state_level,l_full_path,' EXCEPTION OTHERS - glflst ');
2892 psa_utils.debug_other_string(g_state_level,l_full_path,' - ' || SQLERRM);
2893 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN -> FALSE');
2894 -- ========================= FND LOG ===========================
2895
2896 return(FALSE);
2897
2898 END glflst;
2899
2900 /* ------------------------------------------------------------------------- */
2901
2902 -- Find CCIDs for existing Code Combinations
2903
2904
2905 -- Called Routines :
2906
2907 -- dsql_execute : Execute a Dynamic SQL Statement with no Bind Variables
2908
2909
2910 FUNCTION glfaec RETURN BOOLEAN IS
2911
2912 -- Maximum Length for this Dynamic SQL Statement is 1393 assuming there
2913 -- is a 30 Segment Flexfield
2914
2915 sql_statement VARCHAR2(1800);
2916 num_rows INTEGER;
2917
2918 i BINARY_INTEGER;
2919
2920 -- ========================= FND LOG ===========================
2921 l_full_path VARCHAR2(100) := g_path || 'glfaec.';
2922 -- ========================= FND LOG ===========================
2923
2924 BEGIN
2925
2926 -- ========================= FND LOG ===========================
2927 psa_utils.debug_other_string(g_state_level,l_full_path,' START glfaec ');
2928 -- ========================= FND LOG ===========================
2929
2930 sql_statement := 'update gl_dynamic_summ_combinations tc1 ' ||
2931 'set code_combination_id = (' ||
2932 'select nvl(cc.code_combination_id, -1) ' ||
2933 'from gl_code_combinations cc, ' ||
2934 'gl_dynamic_summ_combinations tc2 ' ||
2935 'where cc.template_id(+) = abs(tc1.template_id) ' ||
2936 'and cc.chart_of_accounts_id(+) = ' || coaid || ' ';
2937
2938 for i in 1..30 loop
2939 if seg_val(i) is not null then
2940 sql_statement := sql_statement ||
2941 'and cc.segment' || i || '(+) = ' ||
2942 'tc2.segment' || i || ' ';
2943 end if;
2944 end loop;
2945
2946 sql_statement := sql_statement ||
2947 'and tc2.rowid = tc1.rowid) ' ||
2948 'where tc1.dynamic_group_id = ' || dyn_grp_id;
2949
2950 -- ========================= FND LOG ===========================
2951 psa_utils.debug_other_string(g_state_level,l_full_path,' sql_statement -> ' || sql_statement);
2952 -- ========================= FND LOG ===========================
2953
2954 num_rows := dsql_execute(sql_statement);
2955
2956 -- ========================= FND LOG ===========================
2957 psa_utils.debug_other_string(g_state_level,l_full_path,'num_rows -> ' || num_rows);
2958 -- ========================= FND LOG ===========================
2959
2960 if num_rows < 0 then
2961 -- ========================= FND LOG ===========================
2962 psa_utils.debug_other_string(g_state_level,l_full_path,'RETURN -> FALSE');
2963 -- ========================= FND LOG ===========================
2964 return(FALSE);
2965 end if;
2966
2967 -- ========================= FND LOG ===========================
2968 psa_utils.debug_other_string(g_state_level,l_full_path,'RETURN -> TRUE');
2969 -- ========================= FND LOG ===========================
2970
2971 return(TRUE);
2972
2973 END glfaec;
2974
2975 /* ------------------------------------------------------------------------- */
2976
2977 -- Assign new CCIDs to new Code Combinations
2978
2979
2980 -- Called Routines :
2981
2982 -- message_token : Add Token and Value to the Message Token array
2983
2984 -- add_message : Update global Message String
2985
2986
2987 FUNCTION glfanc RETURN BOOLEAN IS
2988
2989 cursor ccid_seq is
2990 select gl_code_combinations_s.NEXTVAL
2991 from sys.dual;
2992
2993 -- ========================= FND LOG ===========================
2994 l_full_path VARCHAR2(100) := g_path || 'glfanc.';
2995 -- ========================= FND LOG ===========================
2996
2997 BEGIN
2998
2999 -- ========================= FND LOG ===========================
3000 psa_utils.debug_other_string(g_state_level,l_full_path,' START glfanc ');
3001 -- ========================= FND LOG ===========================
3002
3003 open ccid_seq;
3004
3005 fetch ccid_seq
3006 into min_ccid;
3007
3008 -- ========================= FND LOG ===========================
3009 psa_utils.debug_other_string(g_state_level,l_full_path,' min_ccid -> ' || min_ccid);
3010 -- ========================= FND LOG ===========================
3011
3012 if ccid_seq%NOTFOUND then
3013 add_message('FND', 'FLEX-NO ROWS IN DUAL');
3014 -- ========================= FND LOG ===========================
3015 psa_utils.debug_other_string(g_state_level,l_full_path,' goto return_invalid');
3016 -- ========================= FND LOG ===========================
3017 goto return_invalid;
3018 end if;
3019
3020 update gl_dynamic_summ_combinations
3021 set code_combination_id = gl_code_combinations_s.NEXTVAL
3022 where code_combination_id = -1
3023 and dynamic_group_id = dyn_grp_id;
3024
3025 -- ========================= FND LOG ===========================
3026 psa_utils.debug_other_string(g_state_level,l_full_path,
3027 ' update gl_dynamic_summ_combinations -' || SQL%ROWCOUNT);
3028 psa_utils.debug_other_string(g_state_level,l_full_path,
3029 ' RETURN -> TRUE');
3030 -- ========================= FND LOG ===========================
3031
3032 return(TRUE);
3033
3034 <<return_invalid>>
3035 -- ========================= FND LOG ===========================
3036 psa_utils.debug_other_string(g_state_level,l_full_path,
3037 ' LABEL -> return_invalid');
3038 -- ========================= FND LOG ===========================
3039
3040 if ccid_seq%ISOPEN then
3041 close ccid_seq;
3042 end if;
3043
3044 -- ========================= FND LOG ===========================
3045 psa_utils.debug_other_string(g_state_level,l_full_path,
3046 ' RETURN -> FALSE');
3047 -- ========================= FND LOG ===========================
3048
3049 return(FALSE);
3050
3051
3052 EXCEPTION
3053
3054 WHEN OTHERS THEN
3055
3056 if ccid_seq%ISOPEN then
3057 close ccid_seq;
3058 end if;
3059
3060 message_token('MSG', 'glfanc() exception:' || SQLERRM);
3061 add_message('FND', 'FLEX-SSV EXCEPTION');
3062
3063 -- ========================= FND LOG ===========================
3064 psa_utils.debug_other_string(g_state_level,l_full_path,
3065 ' EXCEPTION WHEN OTHERS - ' || SQLERRM);
3066 psa_utils.debug_other_string(g_state_level,l_full_path,
3067 ' RETURN -> FALSE');
3068 -- ========================= FND LOG ===========================
3069
3070 return(FALSE);
3071
3072 END glfanc;
3073
3074 /* ------------------------------------------------------------------------- */
3075
3076 -- Insert newly created Parent Accounts into Code Combinations table
3077
3078
3079 -- Called Routines :
3080
3081 -- message_token : Add Token and Value to the Message Token array
3082
3083 -- add_message : Update global Message String
3084
3085
3086 FUNCTION glficc RETURN BOOLEAN IS
3087
3088 -- Maximum Length for this Dynamic SQL Statement is 1144
3089
3090 sql_inscc VARCHAR2(1800);
3091 cur_inscc INTEGER;
3092 num_rows INTEGER;
3093 i BINARY_INTEGER;
3094
3095 -- ========================= FND LOG ===========================
3096 l_full_path VARCHAR2(100) := g_path || 'glficc.';
3097 -- ========================= FND LOG ===========================
3098
3099 BEGIN
3100
3101 -- ========================= FND LOG ===========================
3102 psa_utils.debug_other_string(g_state_level,l_full_path,' START glficc ');
3103 -- ========================= FND LOG ===========================
3104
3105 sql_inscc := 'insert into gl_code_combinations (' ||
3106 'code_combination_id, ' ||
3107 'last_update_date, ' ||
3108 'last_updated_by, ' ||
3109 'chart_of_accounts_id, ' ||
3110 'detail_posting_allowed_flag, ' ||
3111 'detail_budgeting_allowed_flag, ' ||
3112 'account_type, ' ||
3113 'enabled_flag, ' ||
3114 'summary_flag, ' ||
3115 'template_id, ' ||
3116 'allocation_create_flag, ' ||
3117 'start_date_active, ' ||
3118 'end_date_active';
3119
3120 for i in 1..30 loop
3121 sql_inscc := sql_inscc ||
3122 ', segment' || i;
3123 end loop;
3124
3125 sql_inscc := sql_inscc ||
3126 ') ';
3127
3128 sql_inscc := sql_inscc ||
3129 'select ' ||
3130 'code_combination_id, ' ||
3131 'sysdate, ' ||
3132 ':user_id, ' ||
3133 ':coaid, ' ||
3134 '''N'', ' ||
3135 '''N'', ' ||
3136 '''O'', ' ||
3137 '''Y'', ' ||
3138 '''Y'', ' ||
3139 'abs(template_id), ' ||
3140 '''Y'', ' ||
3141 'null, ' ||
3142 'null';
3143
3144 for i in 1..30 loop
3145 sql_inscc := sql_inscc ||
3146 ', segment' || i;
3147 end loop;
3148
3149 sql_inscc := sql_inscc ||
3150 ' from gl_dynamic_summ_combinations dsc ' ||
3151 'where dynamic_group_id = :grp_id ' ||
3152 'and code_combination_id > :min_ccid';
3153
3154 -- ========================= FND LOG ===========================
3155 psa_utils.debug_other_string(g_state_level,l_full_path,' sql_inscc -> ' || sql_inscc);
3156 -- ========================= FND LOG ===========================
3157
3158 cur_inscc := dbms_sql.open_cursor;
3159 dbms_sql.parse(cur_inscc, sql_inscc, dbms_sql.v7);
3160
3161 dbms_sql.bind_variable(cur_inscc, ':user_id', user_id);
3162 dbms_sql.bind_variable(cur_inscc, ':coaid', coaid);
3163 dbms_sql.bind_variable(cur_inscc, ':grp_id', dyn_grp_id);
3164 dbms_sql.bind_variable(cur_inscc, ':min_ccid', min_ccid);
3165
3166 -- ========================= FND LOG ===========================
3167 psa_utils.debug_other_string(g_state_level,l_full_path,' user_id -> ' || user_id);
3168 psa_utils.debug_other_string(g_state_level,l_full_path,' coaid -> ' || coaid);
3169 psa_utils.debug_other_string(g_state_level,l_full_path,' dyn_grp_id -> ' || dyn_grp_id);
3170 psa_utils.debug_other_string(g_state_level,l_full_path,' min_ccid -> ' || min_ccid);
3171 -- ========================= FND LOG ===========================
3172
3173 num_rows := dbms_sql.execute(cur_inscc);
3174
3175 -- ========================= FND LOG ===========================
3176 psa_utils.debug_other_string(g_state_level,l_full_path,' num_rows -> ' || num_rows);
3177 -- ========================= FND LOG ===========================
3178
3179 dbms_sql.close_cursor(cur_inscc);
3180
3181
3182 -- Call API to update account types of the summary accounts
3183 BEGIN
3184
3185 -- ========================= FND LOG ===========================
3186 psa_utils.debug_other_string(g_state_level,l_full_path,
3187 ' Calling GL_SUMMARY_ACCOUNT_TYPES_PKG.update_account_types');
3188 -- ========================= FND LOG ===========================
3189
3190 GL_SUMMARY_ACCOUNT_TYPES_PKG.update_account_types(coaid, min_ccid);
3191
3192 -- ========================= FND LOG ===========================
3193 psa_utils.debug_other_string(g_state_level,l_full_path,
3194 ' After GL_SUMMARY_ACCOUNT_TYPES_PKG.update_account_types');
3195 -- ========================= FND LOG ===========================
3196
3197 EXCEPTION
3198
3199 WHEN GL_SUMMARY_ACCOUNT_TYPES_PKG.invalid_combination THEN
3200
3201 add_message('SQLGL', 'GL_FLEX_ACC_TYPE_INVALID_COMB');
3202 -- ========================= FND LOG ===========================
3203 psa_utils.debug_other_string(g_state_level,l_full_path,
3204 ' GL_SUMMARY_ACCOUNT_TYPES_PKG RETURN -> FALSE');
3205 -- ========================= FND LOG ===========================
3206 return(FALSE);
3207
3208 WHEN OTHERS THEN
3209 -- ========================= FND LOG ===========================
3210 psa_utils.debug_other_string(g_state_level,l_full_path,
3211 ' EXCEPTION WHEN OTHERS GL_SUMMARY_ACCOUNT_TYPES_PKG - ' || SQLERRM);
3212 -- ========================= FND LOG ===========================
3213
3214 RAISE;
3215 END;
3216
3217 -- ========================= FND LOG ===========================
3218 psa_utils.debug_other_string(g_state_level,l_full_path,
3219 ' RETURN -> TRUE');
3220 -- ========================= FND LOG ===========================
3221
3222 return(TRUE);
3223
3224 EXCEPTION
3225
3226 WHEN OTHERS THEN
3227
3228 if dbms_sql.is_open(cur_inscc) then
3229 dbms_sql.close_cursor(cur_inscc);
3230 end if;
3231
3232 -- Dynamic SQL Exception
3233
3234 message_token('MSG', SQLERRM);
3235 message_token('SQLSTR', substr(sql_inscc, 1, 1000));
3236 add_message('FND', 'FLEX-DSQL EXCEPTION');
3237
3238 -- ========================= FND LOG ===========================
3239 psa_utils.debug_other_string(g_state_level,l_full_path,
3240 ' GLFICC RETURN -> FALSE ' || SQLERRM);
3241 -- ========================= FND LOG ===========================
3242
3243 return(FALSE);
3244
3245 END glficc;
3246
3247 /* ------------------------------------------------------------------------- */
3248
3249 -- Maintain Account Hierarchies
3250
3251
3252 -- Called Routines :
3253
3254 -- message_token : Add Token and Value to the Message Token array
3255
3256 -- add_message : Update global Message String
3257
3258
3259 FUNCTION glfmah(ccid IN NUMBER) RETURN BOOLEAN IS
3260
3261 -- Maximum Length of this Dynamic SQL Statement is 510
3262
3263 sql_acchy VARCHAR2(800);
3264 cur_acchy INTEGER;
3265 num_rows INTEGER;
3266
3267
3268 -- ========================= FND LOG ===========================
3269 l_full_path VARCHAR2(100) := g_path || 'glfmah.';
3270 -- ========================= FND LOG ===========================
3271
3272 BEGIN
3273
3274 -- ========================= FND LOG ===========================
3275 psa_utils.debug_other_string(g_state_level,l_full_path,' START glfmah ');
3276 -- ========================= FND LOG ===========================
3277
3278 sql_acchy := 'insert into gl_account_hierarchies (' ||
3279 'ledger_id, ' ||
3280 'summary_code_combination_id, ' ||
3281 'detail_code_combination_id, ' ||
3282 'template_id, ' ||
3283 'last_updated_by, ' ||
3284 'last_update_date, ' ||
3285 'ordering_value) ';
3286
3287 sql_acchy := sql_acchy ||
3288 'select ledger_id, ' ||
3289 'code_combination_id, ' ||
3290 ':ccid, ' ||
3291 'template_id, ' ||
3292 ':user_id, ' ||
3293 'sysdate, ' ||
3294 ':ordering_value ' ||
3295 'from gl_dynamic_summ_combinations dsc ' ||
3296 'where dsc.dynamic_group_id = :grp_id ' ||
3297 'and not exists (' ||
3298 'select 1 ' ||
3299 'from gl_account_hierarchies ah ' ||
3300 'where ah.summary_code_combination_id = ' ||
3301 'dsc.code_combination_id ' ||
3302 'and ah.detail_code_combination_id = :ccid)';
3303
3304 -- ========================= FND LOG ===========================
3305 psa_utils.debug_other_string(g_state_level,l_full_path,' sql_acchy -> ' || sql_acchy);
3306 -- ========================= FND LOG ===========================
3307
3308 cur_acchy := dbms_sql.open_cursor;
3309 dbms_sql.parse(cur_acchy, sql_acchy, dbms_sql.v7);
3310
3311 dbms_sql.bind_variable(cur_acchy, ':ccid', ccid);
3312 dbms_sql.bind_variable(cur_acchy, ':user_id', user_id);
3313 dbms_sql.bind_variable(cur_acchy, ':ordering_value',
3314 seg_val(acct_seg_index));
3315 dbms_sql.bind_variable(cur_acchy, ':grp_id', dyn_grp_id);
3316
3317 -- ========================= FND LOG ===========================
3318 psa_utils.debug_other_string(g_state_level,l_full_path,' ccid -> ' || ccid);
3319 psa_utils.debug_other_string(g_state_level,l_full_path,' user_id -> ' || user_id);
3320 psa_utils.debug_other_string(g_state_level,l_full_path,' dyn_grp_id -> ' || dyn_grp_id);
3321 -- ========================= FND LOG ===========================
3322
3323 num_rows := dbms_sql.execute(cur_acchy);
3324
3325 -- ========================= FND LOG ===========================
3326 psa_utils.debug_other_string(g_state_level,l_full_path,' num_rows -> ' || num_rows);
3327 -- ========================= FND LOG ===========================
3328
3329 dbms_sql.close_cursor(cur_acchy);
3330
3331 -- ========================= FND LOG ===========================
3332 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN -> TRUE');
3333 -- ========================= FND LOG ===========================
3334
3335 return(TRUE);
3336
3337
3338 EXCEPTION
3339
3340 WHEN OTHERS THEN
3341
3342 if dbms_sql.is_open(cur_acchy) then
3343 dbms_sql.close_cursor(cur_acchy);
3344 end if;
3345
3346 -- Dynamic SQL Exception
3347
3348 message_token('MSG', SQLERRM);
3349 message_token('SQLSTR', substr(sql_acchy, 1, 1000));
3350 add_message('FND', 'FLEX-DSQL EXCEPTION');
3351
3352 -- ========================= FND LOG ===========================
3353 psa_utils.debug_other_string(g_state_level,l_full_path,' EXCEPTION WHEN OTHERS - '||SQLERRM);
3354 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN -> FALSE');
3355 -- ========================= FND LOG ===========================
3356
3357 return(FALSE);
3358
3359 END glfmah;
3360
3361 /* ------------------------------------------------------------------------- */
3362
3363 -- Maintain Reporting Attributes
3364
3365 -- This function is used to maintain the reporting attribute segments
3366 -- for a new ccid created; it also maintains the reporting attributes
3367 -- for any new summary accounts that have been created.
3368
3369 -- Reporting Attributes are maintained only for Government GL install
3370 -- when the reporting attribute profile is set.
3371
3372 -- This function returns TRUE for non-Government install and when
3373 -- the Reporting Attributes profile is not set.
3374
3375 -- This function first updates the reporting attributes for a detail
3376 -- account; it then goes thru the gl_dynamic_summ_combinations table
3377 -- to update the reporting attributes for all the summary accounts that
3378 -- have been created.
3379
3380
3381 -- Called Routines :
3382
3383 -- FND_PROFILE.GET_SPECIFIC : Get Profile Value
3384
3385 -- glfupd : Update Segment Attributes in the Code Combinations table
3386
3387 -- message_token : Add Token and Value to the Message Token array
3388
3389 -- add_message : Update global Message String
3390
3391
3392 FUNCTION glgfdi(ccid IN NUMBER) RETURN BOOLEAN IS
3393
3394 cursor SummAcct(grp_id number,
3395 min_ccid number) IS
3396 select code_combination_id ccid
3397 from gl_dynamic_summ_combinations
3398 where dynamic_group_id = grp_id
3399 and code_combination_id >= min_ccid;
3400
3401 value fnd_profile_option_values.profile_option_value%TYPE;
3402 defined BOOLEAN;
3403
3404 -- ========================= FND LOG ===========================
3405 l_full_path VARCHAR2(100) := g_path || 'glgfdi.';
3406 -- ========================= FND LOG ===========================
3407
3408 BEGIN
3409
3410 -- ========================= FND LOG ===========================
3411 psa_utils.debug_other_string(g_state_level,l_full_path,' START glgfdi ');
3412 -- ========================= FND LOG ===========================
3413
3414 -- Check if this is a OGF installation
3415
3416 if industry <> 'G' then
3417 -- ========================= FND LOG ===========================
3418 psa_utils.debug_other_string(g_state_level,l_full_path,' Industry G RETURN -> TRUE');
3419 -- ========================= FND LOG ===========================
3420 return(TRUE);
3421 end if;
3422
3423
3424 -- Check Reporting Attribute profile
3425
3426 FND_PROFILE.GET_SPECIFIC('ATTRIBUTE_REPORTING',
3427 user_id,
3428 user_resp_id,
3429 101,
3430 value,
3431 defined);
3432
3433
3434 -- If Reporting Attributes profile option is not set return TRUE
3435
3436 if not defined then
3437 -- ========================= FND LOG ===========================
3438 psa_utils.debug_other_string(g_state_level,l_full_path,' Reporting Attributes not defined');
3439 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN -> TRUE');
3440 -- ========================= FND LOG ===========================
3441 return(TRUE);
3442 end if;
3443
3444 -- ========================= FND LOG ===========================
3445 psa_utils.debug_other_string(g_state_level,l_full_path,' Calling glfupd ');
3446 -- ========================= FND LOG ===========================
3447
3448 if not glfupd(ccid) then
3449 -- ========================= FND LOG ===========================
3450 psa_utils.debug_other_string(g_state_level,l_full_path,' GLFUPD RETURN -> FALSE');
3451 -- ========================= FND LOG ===========================
3452 return(FALSE);
3453 end if;
3454
3455 -- ========================= FND LOG ===========================
3456 psa_utils.debug_other_string(g_state_level,l_full_path,' num_bc_lgr -> ' || num_bc_lgr);
3457 -- ========================= FND LOG ===========================
3458
3459 -- repeat for all the summary accounts that have been created
3460 if num_bc_lgr > 0 then
3461
3462 for c_SummAcct in SummAcct(dyn_grp_id, min_ccid) loop
3463
3464 -- ========================= FND LOG ===========================
3465 psa_utils.debug_other_string(g_state_level,l_full_path,' c_SummAcct.ccid -> ' || c_SummAcct.ccid);
3466 psa_utils.debug_other_string(g_state_level,l_full_path,' Calling glfupd ');
3467 -- ========================= FND LOG ===========================
3468
3469 if not glfupd(c_SummAcct.ccid) then
3470 -- ========================= FND LOG ===========================
3471 psa_utils.debug_other_string(g_state_level,l_full_path,' goto return_invalid');
3472 -- ========================= FND LOG ===========================
3473 goto return_invalid;
3474 end if;
3475
3476 end loop;
3477
3478 end if;
3479
3480 -- ========================= FND LOG ===========================
3481 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN -> TRUE');
3482 -- ========================= FND LOG ===========================
3483
3484 return(TRUE);
3485
3486 <<return_invalid>>
3487 -- ========================= FND LOG ===========================
3488 psa_utils.debug_other_string(g_state_level,l_full_path,' LABEL - return_invalid');
3489 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN -> FALSE');
3490 -- ========================= FND LOG ===========================
3491 return(FALSE);
3492
3493
3494 EXCEPTION
3495
3496 WHEN OTHERS THEN
3497
3498 message_token('MSG', 'glgfdi() exception:' || SQLERRM);
3499 add_message('FND', 'FLEX-SSV EXCEPTION');
3500 -- ========================= FND LOG ===========================
3501 psa_utils.debug_other_string(g_state_level,l_full_path,' EXCEPTION WHEN OTHER GLGFDI -' || SQLERRM);
3502 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN -> FALSE');
3503 -- ========================= FND LOG ===========================
3504 return(FALSE);
3505
3506 END glgfdi;
3507
3508 /* ------------------------------------------------------------------------ */
3509
3510 -- Update Segment Attributes in the Code Combinations table
3511
3512 -- This function updates the segment_attribute1..42 columns in the
3513 -- gl_code_combination table
3514
3515 -- SQL statement for the update is constructed dynamically from the
3516 -- definition fnd_flex tables; the SQL will be in this form:
3517 --
3518 -- UPDATE gl_code_combinations glcc set
3519 -- segment_attribute1 = (select attribute2
3520 -- from fnd_flex_values ffval
3521 -- where ffval.flex_value_set_id = 1234
3522 -- and enable_flag = 'Y'
3523 -- and ffval.flex_value = glcc.segment1)
3524 -- segment_attribute2 = (select ....)
3525 -- ...
3526 -- last_update_by = user_id
3527 -- last_updated_date = sysdate
3528 -- where glcc.code_combination = ccid
3529
3530
3531 -- Called Routines :
3532
3533 -- dsql_execute : Execute a Dynamic SQL Statement with no Bind Variables
3534
3535 -- message_token : Add Token and Value to the Message Token array
3536
3537 -- add_message : Update global Message String
3538
3539
3540 FUNCTION glfupd(ccid IN NUMBER) RETURN BOOLEAN IS
3541
3542 cursor RptAttr(flex_num NUMBER) IS
3543 select attr.flex_value_set_id vsid,
3544 nvl(attr.attribute_num, '') attr_name,
3545 attr.table_id table_id,
3546 nvl(attr.application_column_name, '') col_name,
3547 nvl(attr.segment_name, '') seg_name,
3548 attr.segment_num seg_num,
3549 nvl(attr.attr_segment_name, '') aseg_name,
3550 nvl(valset.validation_type, '') vtype,
3551 valset.parent_flex_value_set_id parent_vsid
3552 from fnd_seg_rpt_attributes attr,
3553 fnd_flex_value_sets valset
3554 where attr.application_id = 101
3555 and valset.flex_value_set_id = attr.flex_value_set_id
3556 and attr.id_flex_num = flex_num
3557 order by attr.segment_num;
3558
3559 update_cl VARCHAR2(10000);
3560
3561 vsid_array SegVsetArray;
3562 vseg_array TabColArray;
3563 attr_num NUMBER := 1;
3564
3565 parentval VARCHAR2(20);
3566 vs_column_name VARCHAR2(30);
3567 vs_table_name VARCHAR2(30);
3568
3569
3570 -- ========================= FND LOG ===========================
3571 l_full_path VARCHAR2(100) := g_path || 'glfupd.';
3572 -- ========================= FND LOG ===========================
3573
3574 BEGIN
3575
3576 -- ========================= FND LOG ===========================
3577 psa_utils.debug_other_string(g_state_level,l_full_path,' START glfupd ');
3578 -- ========================= FND LOG ===========================
3579
3580 update_cl := 'UPDATE gl_code_combinations glcc SET ';
3581
3582 for c_RptAttr in RptAttr(coaid) loop
3583
3584 vsid_array(attr_num) := c_RptAttr.vsid;
3585 vseg_array(attr_num) := c_RptAttr.seg_name;
3586
3587 if c_RptAttr.vtype <> 'F' then
3588
3589 update_cl := update_cl ||
3590 c_RptAttr.aseg_name ||
3591 ' = (select ' || c_RptAttr.attr_name ||
3592 ' from fnd_flex_values ffval '||
3593 ' where ffval.flex_value_set_id = ' || c_RptAttr.vsid ||
3594 ' and enabled_flag = ''Y''' ||
3595 ' and ffval.flex_value = glcc.' || c_RptAttr.seg_name;
3596
3597 -- Dependent Value Set
3598
3599 if c_RptAttr.parent_vsid is NOT NULL then
3600
3601 for i in reverse 1..attr_num loop
3602
3603 if (c_RptAttr.parent_vsid = vsid_array(i)) then
3604 parentval := vseg_array(i);
3605 exit;
3606 end if;
3607
3608 end loop;
3609
3610 update_cl := update_cl ||
3611 ' and parent_flex_value_low = glcc.'|| parentval;
3612
3613 end if;
3614
3615 update_cl := update_cl || '), ';
3616
3617 -- Column is table validated
3618
3619 else
3620
3621 select user_table_name
3622 into vs_table_name
3623 from fnd_tables
3624 where application_id = 101
3625 and table_id = c_RptAttr.table_id;
3626
3627 select value_column_name
3628 into vs_column_name
3629 from fnd_flex_validation_tables
3630 where flex_value_set_id = c_RptAttr.vsid;
3631
3632 update_cl := update_cl ||
3633 c_RptAttr.aseg_name ||
3634 ' = ( select ' || c_RptAttr.col_name ||
3635 ' from ' || vs_table_name ||
3636 ' where ' || vs_column_name || ' = glcc.' ||
3637 c_RptAttr.seg_name || ' ), ';
3638
3639 end if;
3640
3641 attr_num := attr_num + 1;
3642
3643 end loop;
3644
3645 update_cl := update_cl ||
3646 'last_update_date = sysdate, ' ||
3647 'last_updated_by = ' || user_id ||
3648 ' where glcc.code_combination_id = ' || ccid;
3649
3650 -- ========================= FND LOG ===========================
3651 psa_utils.debug_other_string(g_state_level,l_full_path,' update_cl -> ' || SUBSTR(update_cl,1,3000));
3652 psa_utils.debug_other_string(g_state_level,l_full_path,' update_cl -> ' || SUBSTR(update_cl,3000,6000));
3653 -- ========================= FND LOG ===========================
3654
3655 if dsql_execute(update_cl) < 0 then
3656 -- ========================= FND LOG ===========================
3657 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN -> FALSE');
3658 -- ========================= FND LOG ===========================
3659 return(FALSE);
3660 else
3661 -- ========================= FND LOG ===========================
3662 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN -> TRUE');
3663 -- ========================= FND LOG ===========================
3664 return(TRUE);
3665 end if;
3666
3667
3668 EXCEPTION
3669
3670 WHEN OTHERS THEN
3671
3672 message_token('MSG', 'glfupd() exception:' || SQLERRM);
3673 add_message('FND', 'FLEX-SSV EXCEPTION');
3674 -- ========================= FND LOG ===========================
3675 psa_utils.debug_other_string(g_state_level,l_full_path,' EXCEPTION WHEN OTHERS GLFUPD - ' || SQLERRM);
3676 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN FALSE');
3677 -- ========================= FND LOG ===========================
3678 return(FALSE);
3679
3680 END glfupd;
3681
3682 /* ----------------------------------------------------------------------- */
3683
3684 -- Add Token and Value to the Message Token array
3685
3686
3687 -- Arguments :
3688
3689 -- tokname : Token Name
3690
3691 -- tokval : Token Value
3692
3693
3694 PROCEDURE message_token(tokname IN VARCHAR2,
3695 tokval IN VARCHAR2) IS
3696
3697 BEGIN
3698
3699 if no_msg_tokens is null then
3700 no_msg_tokens := 1;
3701 else
3702 no_msg_tokens := no_msg_tokens + 1;
3703 end if;
3704
3705 msg_tok_names(no_msg_tokens) := tokname;
3706 msg_tok_val(no_msg_tokens) := tokval;
3707
3708 END message_token;
3709
3710 /* ----------------------------------------------------------------------- */
3711
3712 -- Define a Message Token with a Value and set the Message Name
3713
3714 -- Calls FND_MESSAGE server package to set the Message Stack. This message is
3715 -- retrieved by the calling program.
3716
3717
3718 -- Called Routines :
3719
3720 -- FND_MESSAGE.SET_NAME : Set Message Name
3721
3722 -- FND_MESSAGE.SET_TOKEN : Defines a Message Token with a Value
3723
3724
3725 -- Arguments :
3726
3727 -- appname : Application Short Name
3728
3729 -- msgname : Message Name
3730
3731
3732 PROCEDURE add_message(appname IN VARCHAR2,
3733 msgname IN VARCHAR2) IS
3734
3735 i BINARY_INTEGER;
3736
3737 BEGIN
3738
3739 if ((appname is not null) and
3740 (msgname is not null)) then
3741
3742 FND_MESSAGE.SET_NAME(appname, msgname);
3743
3744 if no_msg_tokens is not null then
3745 for i in 1..no_msg_tokens loop
3746 FND_MESSAGE.SET_TOKEN(msg_tok_names(i), msg_tok_val(i));
3747 end loop;
3748 end if;
3749
3750 end if;
3751
3752
3753 -- Clear Message Token stack
3754
3755 no_msg_tokens := 0;
3756
3757 END add_message;
3758
3759 /* ----------------------------------------------------------------------- */
3760
3761 -- Execute a Dynamic SQL Statement with no Bind Variables
3762
3763 -- Returns number of rows processed or -1 if error (add_message)
3764 -- Return Value is valid only for insert, update and delete statements
3765
3766
3767 -- Called Routines :
3768
3769 -- message_token : Add Token and Value to the Message Token array
3770
3771 -- add_message : Update global Message String
3772
3773
3774 -- Arguments :
3775
3776 -- sql_statement : SQL Statement
3777
3778
3779 FUNCTION dsql_execute(sql_statement IN VARCHAR2) RETURN NUMBER IS
3780
3781 cursornum INTEGER;
3782 nprocessed INTEGER;
3783
3784 BEGIN
3785
3786 cursornum := dbms_sql.open_cursor;
3787 dbms_sql.parse(cursornum, sql_statement, dbms_sql.v7);
3788 nprocessed := dbms_sql.execute(cursornum);
3789 dbms_sql.close_cursor(cursornum);
3790 return(nprocessed);
3791
3792 EXCEPTION
3793
3794 WHEN OTHERS THEN
3795
3796 if dbms_sql.is_open(cursornum) then
3797 dbms_sql.close_cursor(cursornum);
3798 end if;
3799
3800 -- Dynamic SQL Exception
3801
3802 message_token('MSG', SQLERRM);
3803 message_token('SQLSTR', substr(sql_statement, 1, 1000));
3804 add_message('FND', 'FLEXGL-DSQL EXCEPTION');
3805
3806 return(-1);
3807
3808 END dsql_execute;
3809
3810 /* ----------------------------------------------------------------------- */
3811
3812 END GL_FLEX_INSERT_PKG;