[Home] [Help]
PACKAGE BODY: APPS.GL_FLATTEN_SEG_VAL_HIERARCHIES
Source
1 PACKAGE BODY GL_FLATTEN_SEG_VAL_HIERARCHIES AS
2 /* $Header: gluflshb.pls 120.11.12010000.2 2008/08/04 21:52:54 bsrikant ship $ */
3
4
5 -- ********************************************************************
6 -- FUNCTION
7 -- Flatten_Seg_Val_Hier
8 -- Purpose
9 -- This prodcedure is the entry point for maintaining segment hierarchy
10 -- in the tables GL_SEG_VAL_NORM_Hierarchy and GL_SEG_VAL_HIERARCHIES
11 -- History
12 -- 25-04-2001 Srini Pala Created
13 -- Arguments
14 -- Is_Seg_Hier_Changed Indicates changes in the segment hierarchy
15 -- Example
16 -- ret_status := Flatten_Seg_Val_Hier(Is_Seg_Hier_Changed);
17
18
19 FUNCTION Flatten_Seg_Val_Hier(Is_Seg_Hier_Changed OUT NOCOPY BOOLEAN)
20 RETURN BOOLEAN IS
21
22 l_Seg_Hier_Norm_Changed BOOLEAN := FALSE;
23 l_Seg_Hier_Flat_Changed BOOLEAN := FALSE;
24 l_no_rows NUMBER := 0;
25 t_record_check_id NUMBER := 0;
26 -- Variable used for 'T'records check
27
28 result VARCHAR2(8);
29 -- Variable used to Call table validated function
30
31 ret_message VARCHAR2(2000);
32
33 sqlbuf VARCHAR2(2400);
34 add_table VARCHAR2(240):= NULL;
35 column_name VARCHAR2(240):= NULL;
36 l_sql_stmt NUMBER;
37 l_status_flag VARCHAR2(1);
38
39 GLSTFL_fatal_err EXCEPTION;
40
41 BEGIN
42
43 GL_MESSAGE.Func_Ent (func_name =>
44 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Flatten_Seg_Val_Hier');
45
46 -- The flow of the Flatten_Seg_Val_Hier routine is as follows
47 -- First cleans all records with status 'I'and updates records with
48 -- status_code 'D'to NULL in GL_SEG_VAL_HIERARCHIES.
49 -- For operartion mode 'SH' call FND_FLEX_HIERARCHY_
50 -- COMPILER.Compile_Hierarchy proccedure
51 -- Maintain changes in the value set itself by maiantaining mappings for
52 -- the parent value 'T' in the GL_SEG_VAL_HIERARCHIES table.
53 -- Calls Fix_Norm_Table to maiantain the GL_SEG_VAL_NORM_HIERARCHY table
54 -- Calls Fix_Flattened_Table to maiantain the GL_SEG_VAL_HIERARCHIES table.
55
56
57 -- Clean Norm Table before processing
58
59 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
60 GL_MESSAGE.Write_Log(msg_name =>'SHRD0180',
61 token_num => 2,
62 t1 =>'ROUTINE',
63 v1 =>
64 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Flatten_Seg_Val_Hier()',
65 t2 =>'ACTION',
66 v2 =>'Deleting records with status code I '
67 || 'in the table GL_SEG_VAL_NORM_HIERARCHY');
68 END IF;
69
70 -- Delete records with status_code 'I' from GL_SEG_VAL_NORM_HIERARCHY
71
72 -- To improve performance for bug fix # 5075776
73 l_status_flag := 'I';
74
75 DELETE
76 FROM GL_SEG_VAL_NORM_HIERARCHY
77 WHERE status_code = l_status_flag
78 AND flex_value_set_id = GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID;
79
80 l_no_rows := NVL(SQL%ROWCOUNT,0);
81 GL_MESSAGE.Write_Log(msg_name =>'SHRD0119',
82 token_num =>2,
83 t1 =>'NUM',
84 v1 => TO_CHAR(l_no_rows),
85 t2 =>'TABLE',
86 v2 =>'GL_SEG_VAL_NORM_HIERARCHY');
87
88 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
89 GL_MESSAGE.Write_Log(msg_name =>'SHRD0180',
90 token_num => 2,
91 t1 =>'ROUTINE',
92 v1 =>
93 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Flatten_Seg_Val_Hier()',
94 t2 => 'ACTION',
95 v2 =>'Updating records with status code D '
96 ||'to status code NULL in the table'
97 ||' GL_SEG_VAL_NORM_HIERARCHY');
98 END IF;
99
100 UPDATE GL_SEG_VAL_NORM_HIERARCHY
101 SET status_code = NULL
102 WHERE status_code ='D'
103 AND flex_value_set_id = GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID;
104
105 l_no_rows := NVL(SQL%ROWCOUNT,0);
106 GL_MESSAGE.Write_Log(msg_name =>'SHRD0118',
107 token_num =>2,
108 t1 =>'NUM',
109 v1 => TO_CHAR(l_no_rows),
110 t2 =>'TABLE',
111 v2 =>'GL_SEG_VAL_NORM_HIERARCHY');
112
113
114 -- Clean Flattened table before processing
115
116 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
117 GL_MESSAGE.Write_Log(msg_name =>'SHRD0180',
118 token_num => 2,
119 t1 =>'ROUTINE',
120 v1 =>
121 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Flatten_Seg_Val_Hier()',
122 t2 =>'ACTION',
123 v2 =>'Deleting records with status code I '
124 || 'in the table GL_SEG_VAL_HIERARCHIES');
125 END IF;
126
127 -- Delete records with status_code 'I'
128 -- To improve performance for bug fix # 5075776
129 l_status_flag := 'I';
130
131 DELETE
132 FROM GL_SEG_VAL_HIERARCHIES
133 WHERE status_code = l_status_flag
134 AND flex_value_set_id = GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID;
135
136 l_no_rows := NVL(SQL%ROWCOUNT,0);
137 GL_MESSAGE.Write_Log(msg_name =>'SHRD0119',
138 token_num =>2,
139 t1 =>'NUM',
140 v1 => TO_CHAR(l_no_rows),
141 t2 =>'TABLE',
142 v2 =>'GL_SEG_VAL_HIERARCHIES');
143
144 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
145 GL_MESSAGE.Write_Log(msg_name =>'SHRD0180',
146 token_num => 2,
147 t1 =>'ROUTINE',
148 v1 =>
149 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Flatten_Seg_Val_Hier()',
150 t2 => 'ACTION',
151 v2 =>'Updating records with status code D '
152 ||'to status code NULL in the table'
153 ||' GL_SEG_VAL_HIERARCHIES');
154 END IF;
155
156 UPDATE GL_SEG_VAL_HIERARCHIES
157 SET status_code = NULL
158 WHERE status_code ='D'
159 AND flex_value_set_id = GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID;
160
161 l_no_rows := NVL(SQL%ROWCOUNT,0);
162 GL_MESSAGE.Write_Log(msg_name =>'SHRD0118',
163 token_num =>2,
164 t1 =>'NUM',
165 v1 => TO_CHAR(l_no_rows),
166 t2 =>'TABLE',
167 v2 =>'GL_SEG_VAL_HIERARCHIES');
168
169 FND_CONCURRENT.Af_Commit; -- COMMIT point after initial cleaning
170
171 -- Th following block checks for the Value set ie being
172 -- used by any chart of accounts or it has an hierarchy.
173
174 BEGIN
175
176 -- The following statement checks the value set is being
177 -- used by any chart of accounts or it has an hierarchy.
178
179 SELECT 1
180 INTO t_record_check_id
181 FROM DUAL
182 WHERE EXISTS
183 (SELECT 1
184 FROM FND_ID_FLEX_SEGMENTS fifs
185 WHERE fifs.application_id = 101
186 AND fifs.id_flex_code = 'GL#'
187 AND fifs.flex_value_set_id = GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID)
188 OR EXISTS
189 (SELECT 1
190 FROM FND_FLEX_VALUE_NORM_HIERARCHY ffvnh
191 WHERE ffvnh.flex_value_set_id = GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
192 AND ROWNUM = 1);
193
194 EXCEPTION
195
196 WHEN NO_DATA_FOUND THEN
197 t_record_check_id := 0;
198 END;
199
200 IF (t_record_check_id = 1) THEN
201
202 -- The follwoing statement updates 'T' records if any segement value
203 -- changes from being a parent to a child value and vice versa.
204 -- Bug7134519 Added DISTINCT in the sub query to spport Dependent value sets
205
206 UPDATE GL_SEG_VAL_HIERARCHIES glsvh
207 SET glsvh.status_code = 'U',
208 glsvh.summary_flag =
209 (SELECT DISTINCT ffv.summary_flag
210 FROM FND_FLEX_VALUES ffv
211 WHERE ffv.flex_value_set_id =
212 GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
213 AND ffv.flex_value = glsvh.child_flex_value)
214 WHERE glsvh.flex_value_set_id =
215 GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
216 AND glsvh.parent_flex_value = 'T'
217 AND glsvh.child_flex_value IN
218 (SELECT ffv2.flex_value
219 FROM FND_FLEX_VALUES ffv2
220 WHERE ffv2.flex_value_set_id =
221 glsvh.flex_value_set_id
222 AND ffv2.flex_value = glsvh.child_flex_value
223 AND ffv2.summary_flag <> glsvh.summary_flag);
224
225 l_no_rows := NVL(SQL%ROWCOUNT,0);
226 GL_MESSAGE.Write_Log(msg_name =>'SHRD0118',
227 token_num =>2,
228 t1 =>'NUM',
229 v1 =>TO_CHAR(l_no_rows),
230 t2 =>'TABLE',
231 v2 =>'GL_SEG_VAL_HIERARCHIES');
232
233 -- The following SQL statement insert new 'T' records for the new
234 -- segment values added to the value set. The following statement is
235 -- for regular value sets.
236
237 INSERT INTO GL_SEG_VAL_HIERARCHIES
238 (flex_value_set_id, parent_flex_value, child_flex_value,
239 summary_flag, status_code, created_by, creation_date,
240 last_updated_by, last_update_login, last_update_date)
241 (SELECT DISTINCT ffv.flex_value_set_id, 'T', ffv.flex_value,
242 ffv.summary_flag, 'I',
243 GL_FLATTEN_SETUP_DATA.GLSTFL_User_Id,
244 SYSDATE, GL_FLATTEN_SETUP_DATA.GLSTFL_User_Id,
245 GL_FLATTEN_SETUP_DATA.GLSTFL_Login_Id,
246 SYSDATE
247 FROM FND_FLEX_VALUES ffv
248 WHERE ffv.flex_value_set_id = GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
249 AND NOT EXISTS
250 (SELECT 1
251 FROM GL_SEG_VAL_HIERARCHIES glsvh
252 WHERE glsvh.flex_value_set_id =
253 ffv.flex_value_set_id
254 AND glsvh.parent_flex_value = 'T'
255 AND glsvh.child_flex_value = ffv.flex_value
256 AND glsvh.summary_flag = ffv.summary_flag));
257
258 IF (SQL%FOUND) THEN
259 Is_Seg_Hier_Changed := TRUE;
260 END IF;
261
262 l_no_rows := NVL(SQL%ROWCOUNT,0);
263 GL_MESSAGE.Write_Log(msg_name =>'SHRD0117',
264 token_num =>2,
265 t1 =>'NUM',
266 v1 =>TO_CHAR(l_no_rows),
267 t2 =>'TABLE',
268 v2 =>'GL_SEG_VAL_HIERARCHIES');
269
270 -- Insert 'T'records for table validated value sets.
271 -- For any table validated value set the parent values will always be
272 -- stored in FND_FLEX_VALUES table. Only the detail values will be
273 -- stored in the user defined table. So the above statement takes
274 -- care of all parent values and the following DYNAMIC SQL statement
275 -- inserts all detail records into GL_SEG_VAL_HIERARCHIES table.
276
277 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_VS_TAB_NAME IS NOT NULL) THEN
278
279 l_no_rows := 0;
280 add_table := GL_FLATTEN_SETUP_DATA.GLSTFL_VS_TAB_NAME;
281 column_name := GL_FLATTEN_SETUP_DATA.GLSTFL_VS_COL_NAME;
282
283 sqlbuf := 'INSERT INTO GL_SEG_VAL_HIERARCHIES
284 (flex_value_set_id, parent_flex_value,
285 child_flex_value, summary_flag,
286 status_code, created_by,
287 creation_date, last_updated_by,
288 last_update_login, last_update_date)
289 (SELECT DISTINCT :v_id,
290 ''T'', tv.'||column_name||' ,
291 ''N'', ''I'',
292 :user_id,
293 SYSDATE, :u_id,
294 :log_id,
295 SYSDATE
296 FROM ' ||add_table || ' tv
297 WHERE NOT EXISTS
298 (SELECT 1
299 FROM GL_SEG_VAL_HIERARCHIES glsvh
300 WHERE glsvh.flex_value_set_id
301 = :vs_id
302 AND glsvh.parent_flex_value
303 = ''T''
304 AND glsvh.child_flex_value
305 = tv.'||column_name|| ' ))';
306
307 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
308
309 l_sql_stmt := LENGTH(sqlbuf);
310
311
312
313 GL_MESSAGE.Write_Log(msg_name =>'SHRD0181',
314 token_num => 3 ,
315 t1 =>'ROUTINE',
316 v1 =>
317 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Flatten_Seg_Val_Hier()',
318 t2 =>'VARIABLE',
319 v2 =>'Value_Set_Id',
320 t3 =>'VALUE',
321 v3 => GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID);
322
323 GL_MESSAGE.Write_Log(msg_name =>'SHRD0181',
324 token_num => 3 ,
325 t1 =>'ROUTINE',
326 v1 =>
327 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Flatten_Seg_Val_Hier()',
328 t2 =>'VARIABLE',
329 v2 =>'Table Name',
330 t3 =>'VALUE',
331 v3 => add_table);
332
333 GL_MESSAGE.Write_Log(msg_name =>'SHRD0181',
334 token_num => 3 ,
335 t1 =>'ROUTINE',
336 v1 =>
337 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Flatten_Seg_Val_Hier()',
338 t2 =>'VARIABLE',
339 v2 =>'Column Name',
340 t3 =>'VALUE',
341 v3 => GL_FLATTEN_SETUP_DATA.GLSTFL_VS_COL_NAME);
342
343 GL_MESSAGE.Write_Log(msg_name =>'SHRD0181',
344 token_num => 3 ,
345 t1 =>'ROUTINE',
346 v1 =>
347 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Flatten_Seg_Val_Hier()',
348 t2 =>'VARIABLE',
349 v2 =>'sqlbuf',
350 t3 =>'VALUE',
351 v3 => sqlbuf);
352
353 GL_MESSAGE.Write_Log(msg_name =>'SHRD0181',
354 token_num => 3 ,
355 t1 =>'ROUTINE',
356 v1 =>
357 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Flatten_Seg_Val_Hier()',
358 t2 =>'VARIABLE',
359 v2 =>'Length of sql_stmt',
363 GL_MESSAGE.Write_Log(msg_name =>'SHRD0180',
360 t3 =>'VALUE',
361 v3 =>l_sql_stmt);
362
364 token_num => 2,
365 t1 =>'ROUTINE',
366 v1 =>
367 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Flatten_Seg_Val_Hier()',
368 t2 => 'ACTION',
369 v2 =>'Inserting ''T'' records for table validated'
370 ||' value set into the table '
371 ||' GL_SEG_VAL_HIERARCHIES');
372
373 END IF;
374
375 EXECUTE IMMEDIATE sqlbuf
376 USING GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID,
377 GL_FLATTEN_SETUP_DATA.GLSTFL_User_Id,
378 GL_FLATTEN_SETUP_DATA.GLSTFL_User_Id,
379 GL_FLATTEN_SETUP_DATA.GLSTFL_Login_Id,
380 GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID;
381
382 IF (SQL%FOUND) OR (Is_Seg_Hier_Changed) THEN
383 Is_Seg_Hier_Changed := TRUE;
384 END IF;
385
386
387 l_no_rows := NVL(SQL%ROWCOUNT,0);
388
389 GL_MESSAGE.Write_Log(msg_name =>'SHRD0117',
390 token_num =>2,
391 t1 =>'NUM',
392 v1 =>TO_CHAR(l_no_rows),
393 t2 =>'TABLE',
394 v2 =>'GL_SEG_VAL_HIERARCHIES');
395 END IF;
396
397 FND_CONCURRENT.Af_Commit; -- COMMIT Point
398
399 END IF; -- 'T' records If control block ends.
400
401 IF (NOT Fix_Norm_Table(Is_Norm_Table_Changed =>
402 l_Seg_Hier_Norm_Changed)) THEN
403
404 RAISE GLSTFL_fatal_err;
405
406 ELSIF (NOT l_Seg_Hier_Norm_Changed) THEN
407
408 GL_MESSAGE.Write_Log(msg_name =>'FLAT0001',
409 token_num => 1,
410 t1 =>'ROUTINE_NAME',
411 v1 =>
412 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Flatten_Seg_Val_Hier()');
413 ELSE
414
415 IF (NOT Fix_Flattened_Table (Is_Flattened_Tab_Changed =>
416 l_Seg_Hier_Flat_Changed )) THEN
417 RAISE GLSTFL_fatal_err;
418
419 ELSIF (NOT l_Seg_Hier_Flat_Changed) THEN
420
421 GL_MESSAGE.Write_Log(msg_name =>'FLAT0001',
422 token_num => 1,
423 t1 =>'ROUTINE_NAME',
424 v1 =>
425 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Flatten_Seg_Val_Hier()');
426
427 ELSE
428
429 -- Return True Only if any changes processed in Flattened table.
430
431 Is_Seg_Hier_Changed := TRUE;
432
433 END IF; -- Inner Fix_Flattened_table If Control block ends.
434
435 END IF; -- Outer Fix_Norm_Table If control statement ends.
436
437 GL_MESSAGE.Func_Succ(func_name =>
438 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Flatten_Seg_Val_Hier');
439
440 RETURN TRUE;
441
442 EXCEPTION
443
444 WHEN GLSTFL_fatal_err THEN
445
446 GL_MESSAGE.Write_Log(msg_name =>'FLAT0002',
447 token_num => 1,
448 t1 =>'ROUTINE_NAME',
449 v1 =>
450 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Flatten_Seg_Val_Hier()');
451
452 GL_MESSAGE.Func_Fail(func_name =>
453 'GL_FLATTEN_SEG_VAL_HIERARCHIE.Flatten_Seg_Val_Hier');
454
455 FND_CONCURRENT.Af_Rollback; -- Rollback Point
456
457 Is_Seg_Hier_Changed := FALSE;
458
459 RETURN FALSE;
460
461 WHEN OTHERS THEN
462
463 GL_MESSAGE.Write_Log(msg_name =>'SHRD0203',
464 token_num =>2,
465 t1 =>'FUNCTION',
466 v1 =>
467 'GL_FLATTEN_SEG_VAL_HIERARCHIE.Flatten_Seg_Val_Hier()',
468 t2 =>'SQLERRMC',
469 v2 => SQLERRM);
470
471 GL_MESSAGE.Func_Fail(func_name =>
472 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Flatten_Seg_Val_Hier');
473
474 FND_CONCURRENT.Af_Rollback; -- Rollback Point
475
476 Is_Seg_Hier_Changed := FALSE;
477
478 RETURN FALSE;
479
480 END Flatten_Seg_Val_Hier;
481
482 -- ******************************************************************
483 -- FUNCTION
484 -- Fix_Norm_Table
485 -- Purpose
486 -- This prodcedure maintains the table GL_SEG_VAL_NORM_Hierarchy
487 -- History
488 -- 25-04-2001 Srini Pala Created
489 -- Arguments
490 -- Is_Norm_Table_Changed Indicates changes in Norm Table -values BOOLEAN
491 -- Example
492 -- ret_status := Fix_Flattened_Table(Is_Norm_Table_Changed);
493 --
494
495 FUNCTION Fix_Norm_Table (Is_Norm_Table_Changed OUT NOCOPY BOOLEAN)
496 RETURN BOOLEAN IS
497
498 condition VARCHAR2(200) ;
499 add_table VARCHAR2(50);
500 --sql_stmt VARCHAR2(2400);
501 --l_sql_stmt VARCHAR2(4);
505
502 l_no_rows NUMBER :=0;
503
504
506 BEGIN
507
508 GL_MESSAGE.FUNC_ENT(func_name =>
509 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Fix_Norm_Table');
510
511 -- The following check allows the program to continue processing
512 -- even there are no hierarchy changes occur in the case of
513 -- inserting a new value into the value set. If the new value is
514 -- a parent value then the program should run the FIX_FLATTENED_TABLE
515 -- procedure.
516
517 SELECT count(*) INTO l_no_rows
518 FROM
519 DUAL
520 WHERE EXISTS
521 (SELECT 1
522 FROM GL_SEG_VAL_HIERARCHIES
523 WHERE status_code = 'I'
524 AND flex_value_set_id =
525 GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID);
526
527 IF (l_no_rows = 1) THEN
528 Is_Norm_Table_Changed := TRUE;
529 ELSE
530 Is_Norm_Table_Changed := FALSE;
531 END IF;
532
533 --Reset the number of rows variable.
534 l_no_rows := 0;
535
536 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
537 GL_MESSAGE.Write_Log(msg_name =>'SHRD0180',
538 token_num => 2,
539 t1 =>'ROUTINE',
540 v1 =>
541 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Fix_Norm_Table()',
542 t2 =>'ACTION',
543 v2 =>'Mark all outdated parent-child '
544 ||' segment value mappings'
545 ||' in the table '
546 ||' GL_SEG_VAL_NORM_HIERARCHY');
547 END IF;
548
549 -- The following SQL statement mark all outdated parent-child segment
550 -- value mappings in the table GL_SEG_VAL_NORM_HIERARCHY
551
552 UPDATE GL_SEG_VAL_NORM_HIERARCHY glsvnh
553 SET glsvnh.status_code = 'D'
554 WHERE glsvnh.flex_value_set_id = GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
555 AND
556 (NOT EXISTS
557 (SELECT 1
558 FROM FND_FLEX_VALUE_NORM_HIERARCHY ffvnh
559 WHERE ffvnh.flex_value_set_id =
560 glsvnh.flex_value_set_id
561 AND ffvnh.range_attribute =
562 DECODE(glsvnh.summary_flag,'Y','P','N','C')
563 AND ffvnh.parent_flex_value =
564 glsvnh.parent_flex_value
565 AND glsvnh.child_flex_value
566 BETWEEN ffvnh.child_flex_value_low
567 AND ffvnh.child_flex_value_high)
568 OR
569 EXISTS
570 (SELECT 1
571 FROM GL_SEG_VAL_HIERARCHIES glsvh
572 WHERE glsvh.flex_value_set_id =
573 glsvnh.flex_value_set_id
574 AND glsvh.parent_flex_value = 'T'
575 AND glsvh.child_flex_value =
576 glsvnh.child_flex_value
577 AND glsvh.status_code = 'U'
578 AND glsvh.summary_flag <>
579 glsvnh.summary_flag));
580
581 l_no_rows := NVL(SQL%ROWCOUNT,0);
582
583 IF (l_no_rows > 0) THEN
584 Is_Norm_Table_Changed := TRUE;
585 END IF;
586
587 GL_MESSAGE.Write_Log(msg_name =>'SHRD0118',
588 token_num =>2,
589 t1 =>'NUM',
590 v1 =>TO_CHAR(l_no_rows),
591 t2 =>'TABLE',
592 v2 =>'GL_SEG_VAL_NORM_HIERARCHY');
593
594
595 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
596
597
598 GL_MESSAGE.Write_Log(msg_name =>'SHRD0181',
599 token_num => 3 ,
600 t1 =>'ROUTINE',
601 v1 =>
602 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Fix_Norm_Table()',
603 t2 =>'VARIABLE',
604 v2 =>'Value_Set_Id',
605 t3 =>'VALUE',
606 v3 =>GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID);
607
608 GL_MESSAGE.Write_Log(msg_name =>'SHRD0180',
609 token_num => 2,
610 t1 =>'ROUTINE',
611 v1 =>
612 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Fix_Norm_Table()',
613 t2 => 'ACTION',
614 v2 =>'Insert any new parent-child'
615 ||' segment value mappings into'
616 ||' the table'
617 ||' GL_SEG_VAL_NORM_HIERARCHY');
618 END IF;
619
620 -- The following statement inserts all new parent-child
621 -- (direct mappings) segment value mappings into the
622 -- table GL_SEG_VAL_NORM_HIERARCHY
623
624 INSERT INTO GL_SEG_VAL_NORM_HIERARCHY
625 (flex_value_set_id, parent_flex_value, child_flex_value,
626 summary_flag, status_code, created_by, creation_date,
630 glsvh.child_flex_value,
627 last_updated_by, last_update_login, last_update_date)
628 (SELECT DISTINCT ffvnh.flex_value_set_id,
629 ffvnh.parent_flex_value,
631 glsvh.summary_flag, 'I',
632 GL_FLATTEN_SETUP_DATA.GLSTFL_User_Id,
633 SYSDATE,
634 GL_FLATTEN_SETUP_DATA.GLSTFL_User_Id,
635 GL_FLATTEN_SETUP_DATA.GLSTFL_Login_Id,
636 SYSDATE
637 FROM FND_FLEX_VALUE_NORM_HIERARCHY ffvnh,
638 GL_SEG_VAL_HIERARCHIES glsvh
639 WHERE ffvnh.flex_value_set_id =
640 GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
641 AND glsvh.flex_value_set_id = ffvnh.flex_value_set_id
642 AND glsvh.parent_flex_value = 'T'
643 AND glsvh.summary_flag =
644 DECODE(ffvnh.range_attribute,
645 'P','Y', 'C','N')
646 AND glsvh.child_flex_value
647 BETWEEN ffvnh.child_flex_value_low
648 AND ffvnh.child_flex_value_high
649 AND glsvh.child_flex_value <> 'T'
650 AND NOT EXISTS
651 (SELECT 1
652 FROM GL_SEG_VAL_NORM_HIERARCHY glsvnh2
653 WHERE glsvnh2.flex_value_set_id =
654 ffvnh.flex_value_set_id
655 AND glsvnh2.parent_flex_value =
656 ffvnh.parent_flex_value
657 AND glsvnh2.child_flex_value =
658 glsvh.child_flex_value
659 AND glsvnh2.summary_flag =
660 DECODE(ffvnh.range_attribute ,
661 'P','Y', 'C', 'N')));
662
663 l_no_rows := NVL(SQL%ROWCOUNT,0);
664
665 IF (l_no_rows > 0) THEN
666 Is_Norm_Table_Changed := TRUE;
667 END IF;
668
669 GL_MESSAGE.Write_Log(msg_name =>'SHRD0117',
670 token_num => 2,
671 t1 =>'NUM',
672 v1 => TO_CHAR(l_no_rows),
673 t2 => 'TABLE',
674 v2 => 'GL_SEG_VAL_NORM_HIERARCHY');
675
676 GL_MESSAGE.Func_Succ(func_name =>
677 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Fix_Norm_Table');
678
679 FND_CONCURRENT.Af_Commit; --COMMIT point
680
681 RETURN TRUE;
682
683 EXCEPTION
684
685 WHEN OTHERS THEN
686
687 GL_MESSAGE.Write_Log (msg_name =>'SHRD0102',
688 token_num => 1,
689 t1 =>'EMESSAGE',
690 v1 => SQLERRM);
691
692 GL_MESSAGE.Func_Fail(func_name =>
693 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Fix_Norm_Table');
694
695 FND_CONCURRENT.Af_Rollback; -- Rollback Point
696
697 RETURN FALSE;
698
699 END Fix_Norm_Table;
700
701 -- *****************************************************************
702
703 -- FUNCTION
704 -- Fix_Flattened_Table
705 -- Purpose
706 -- The FUNCTION maintains table GL_SEG_VAL_HIERARCHIES
707 -- History
708 -- 27-04-2001 Srini Pala Created
709 -- Arguments
710 -- Is_Falttened_Tab_Changed Indicates table change status -values BOOLEAN
711 -- Example
712 -- ret_status := Fix_Flattened_Table(Is_Falttened_Tab_Changed);
713 --
714
715 FUNCTION Fix_Flattened_Table(Is_Flattened_Tab_Changed OUT NOCOPY BOOLEAN)
716 RETURN BOOLEAN IS
717
718 c_rows_process BOOLEAN;
719
720 l_commit_ctr NUMBER;
721
722 l_no_rows NUMBER :=0;
723
724 BEGIN
725
726 GL_MESSAGE.Func_Ent(func_name =>
727 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Fix_Flattened_Table');
728
729 Is_Flattened_Tab_Changed := FALSE;
730
731 -- The following SQL statement determine all records in
732 -- GL_SEG_VAL_HIERARCHIES that contain the deleted child via the
733 -- deleted parent segment value mappings for all mappings marked
734 -- for delete in GL_SEG_VAL_NORM_HIERARCHY
735
736 UPDATE GL_SEG_VAL_HIERARCHIES GLSVH
737 SET status_code ='D'
738 WHERE NVL(glsvh.status_code,'X') <>'D'
739 AND (glsvh.flex_value_set_id , glsvh.parent_flex_value,
740 glsvh.child_flex_value, glsvh.summary_flag) IN
741 (SELECT DISTINCT glsvnh.flex_value_set_id,
742 glsvh1.parent_flex_value,
743 glsvnh.child_flex_value,
744 glsvnh.summary_flag
745 FROM GL_SEG_VAL_NORM_HIERARCHY glsvnh,
746 GL_SEG_VAL_HIERARCHIES glsvh1
747 WHERE glsvnh.flex_value_set_id =
748 GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
749 AND glsvnh.status_code ='D'
750 AND glsvh1.flex_value_set_id =
751 glsvnh.flex_value_set_id
752 AND glsvh1.child_flex_value =
753 glsvnh.parent_flex_value
754 AND glsvh1.parent_flex_value <> 'T');
755
756 l_no_rows := NVL(SQL%ROWCOUNT,0);
757
758
759 IF (l_no_rows > 0) THEN
760
761 -- Tracks the changes in the table GL_Seg_Val_Hierarchies
762
763 Is_Flattened_Tab_Changed := TRUE;
764
765 END IF;
766
767 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
768 GL_MESSAGE.Write_Log(msg_name =>'SHRD0180',
769 token_num =>2,
770 t1 =>'ROUTINE',
771 v1 =>
772 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Fix_Flattened_Table()',
773 t2 =>'ACTION',
774 v2 =>'Updating all mapping(s) that'
775 ||' contain deleted child via'
776 ||' deleted parent in the'
777 ||' table GL_SEG_VAL_HIERARCHIES');
778 END IF;
779
780 GL_MESSAGE.Write_Log(msg_name =>'SHRD0118',
781 token_num => 2 ,
782 t1 =>'NUM',
783 v1 => TO_CHAR(l_no_rows),
784 t2 =>'TABLE',
785 v2 => 'GL_SEG_VAL_HIERARCHIES');
786
787 -- The following SQL statement mark all records for delete that
788 -- contain any deleted parent child and its descendants
789 -- in GL_SEG_VAL_HIERARCHIES
790
791 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
792 GL_MESSAGE.Write_Log(msg_name =>'SHRD0180',
793 token_num =>2,
794 t1 =>'ROUTINE',
795 v1 =>
796 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Fix_Flattened_Table()',
797 t2 =>'ACTION',
798 v2 =>'Updating all mapping(s) that'
799 ||' contain deleted parent child'
800 ||' and its descendants in the'
801 ||' table GL_SEG_VAL_HIERARCHIES');
802 END IF;
803
804 UPDATE GL_SEG_VAL_HIERARCHIES GLSVH
805 SET status_code ='D'
806 WHERE NVL(glsvh.status_code,'X') <>'D'
807 AND (glsvh.flex_value_set_id , glsvh.parent_flex_value,
808 glsvh.child_flex_value, glsvh.summary_flag) IN
809 (SELECT DISTINCT glsvnh.flex_value_set_id,
810 glsvh1.parent_flex_value,
811 glsvh2.child_flex_value,
812 glsvh2.summary_flag
813 FROM GL_SEG_VAL_NORM_HIERARCHY glsvnh,
814 GL_SEG_VAL_HIERARCHIES glsvh1,
815 GL_SEG_VAL_HIERARCHIES glsvh2
816 WHERE glsvnh.status_code ='D'
817 AND glsvnh.summary_flag = 'Y'
818 AND glsvnh.flex_value_set_id =
819 GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
820 AND glsvh1.flex_value_set_id =
821 glsvnh.flex_value_set_id
822 AND glsvh1.child_flex_value =
823 glsvnh.parent_flex_value
824 AND glsvh1.parent_flex_value <> 'T'
825 AND glsvh2.flex_value_set_id =
826 glsvnh.flex_value_set_id
827 AND glsvh2.parent_flex_value =
828 glsvnh.child_flex_value);
829
830 l_no_rows := NVL(SQL%ROWCOUNT,0);
831
832 FND_CONCURRENT.Af_Commit; -- COMMIT Point.
833
834 IF (l_no_rows > 0) THEN
835
836 Is_Flattened_Tab_Changed := TRUE;
837
838 END IF;
839
840 GL_MESSAGE.Write_Log(msg_name =>'SHRD0118',
841 token_num => 2,
842 t1 =>'NUM',
843 v1 =>TO_CHAR(l_no_rows),
844 t2 =>'TABLE',
845 v2 =>'GL_SEG_VAL_HIERARCHIES');
846
850
847 -- The following SQL checks and reconnects if the deleted child is
848 -- mapped to the parent again through some other paths. It will run
849 -- in a loop until no more changes occur.
851 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
852
853 GL_MESSAGE.Write_Log(msg_name =>'SHRD0180',
854 token_num =>2,
855 t1 =>'ROUTINE',
856 v1 =>
857 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Fix_Flattened_Table()',
858 t2 =>'ACTION',
859 v2 =>'Reconnecting mapping(s) that if '
860 ||' any deleted child mapped to the'
861 ||' parent through someother path');
862
863 END IF;
864
865 c_rows_process := FALSE;
866
867 l_no_rows :=0;
868
869 WHILE NOT c_rows_process
870
871 LOOP
872
873 UPDATE GL_SEG_VAL_HIERARCHIES glsvh1
874 SET glsvh1.status_code = NULL
875 WHERE glsvh1.status_code ='D'
876 AND glsvh1.flex_value_set_id =
877 GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
878 AND (EXISTS
879 (SELECT 1
880 FROM GL_SEG_VAL_HIERARCHIES glsvh2,
881 GL_SEG_VAL_HIERARCHIES glsvh3
882 WHERE glsvh2.flex_value_set_id =
883 glsvh1.flex_value_set_id
884 AND glsvh2.status_code IS NULL
885 AND glsvh2.child_flex_value =
886 glsvh1.child_flex_value
887 AND glsvh3.flex_value_set_id =
888 glsvh2.flex_value_set_id
889 AND glsvh3.status_code is NULL
890 AND glsvh3.parent_flex_value =
891 glsvh1.parent_flex_value
892 AND glsvh3.child_flex_value =
893 glsvh2.parent_flex_value)
894 OR EXISTS
895
896 (SELECT 1
897 FROM GL_SEG_VAL_NORM_HIERARCHY glsvnh
898 WHERE glsvnh.flex_value_set_id = glsvh1.flex_value_set_id
899 AND glsvnh.parent_flex_value = glsvh1.parent_flex_value
900 AND glsvnh.child_flex_value = glsvh1.child_flex_value
901 AND glsvnh.status_code IS NULL
902 AND glsvnh.summary_flag = glsvh1.summary_flag)) ;
903
904 l_no_rows := l_no_rows+NVL(SQL%ROWCOUNT,0);
905
906 c_rows_process := SQL%NOTFOUND;
907
908 FND_CONCURRENT.Af_Commit; -- COMMIT point
909
910 END LOOP;
911
912 IF (l_no_rows > 0) THEN
913 Is_Flattened_Tab_Changed := TRUE;
914 END IF;
915
916 GL_MESSAGE.Write_Log(msg_name =>'SHRD0118',
917 token_num =>2,
918 t1 =>'NUM',
919 v1 =>TO_CHAR(l_no_rows),
920 t2 =>'TABLE',
921 v2 =>'GL_SEG_VAL_HIERARCHIES');
922
923 -- Insert new self reocrds into GL_SEG_VAL_HIERARCHIES for each
924 -- new parent / changing being a child value to a parent value
925
926 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
927 GL_MESSAGE.Write_Log(msg_name =>'SHRD0180',
928 token_num =>2,
929 t1 =>'ROUTINE',
930 v1 =>
931 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Fix_Flattened_Table()',
932 t2 =>'ACTION',
933 v2 =>'Inserting Parent segement value'
934 ||' with itself as child into the table'
935 ||' GL_SEG_VAL_HIERARCHIES');
936 END IF;
937
938 INSERT INTO GL_SEG_VAL_HIERARCHIES
939 (flex_value_set_id, parent_flex_value, child_flex_value,
940 summary_flag, status_code, created_by, creation_date,
941 last_updated_by, last_update_login, last_update_date)
942 (SELECT DISTINCT glsvh.flex_value_set_id, glsvh.child_flex_value,
943 glsvh.child_flex_value, 'Y', 'I',
944 GL_FLATTEN_SETUP_DATA.GLSTFL_User_Id,
945 SYSDATE,
946 GL_FLATTEN_SETUP_DATA.GLSTFL_User_Id,
947 GL_FLATTEN_SETUP_DATA.GLSTFL_Login_Id,
948 SYSDATE
949 FROM GL_SEG_VAL_HIERARCHIES glsvh
950 WHERE glsvh.flex_value_set_id =
951 GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
952 AND glsvh.parent_flex_value = 'T'
953 AND glsvh.status_code IN ('I','U')
954 AND glsvh.summary_flag = 'Y'
955 AND NOT EXISTS
956 (SELECT 1
957 FROM GL_SEG_VAL_HIERARCHIES glsvh1
958 WHERE glsvh1.flex_value_set_id =
959 glsvh.flex_value_set_id
960 AND glsvh1.parent_flex_value =
961 glsvh.child_flex_value
962 AND glsvh1.child_flex_value =
963 glsvh.child_flex_value
964 AND NVL(glsvh1.status_code,'X') <> 'D'
968
965 AND glsvh1.summary_flag = 'Y') );
966
967 l_no_rows := NVL(SQL%ROWCOUNT,0);
969 IF (l_no_rows > 0) THEN
970 Is_Flattened_Tab_Changed := TRUE;
971 END IF;
972
973 GL_MESSAGE.Write_Log(msg_name =>'SHRD0117',
974 token_num => 2,
975 t1 => 'NUM',
976 v1 => TO_CHAR(l_no_rows),
977 t2 =>'TABLE',
978 v2 => 'GL_SEG_VAL_HIERARCHIES');
979
980
981 -- The following SQL statement Insert all new detail child mappings
982
983 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
984 GL_MESSAGE.Write_Log(msg_name =>'SHRD0180',
985 token_num =>2,
986 t1 =>'ROUTINE',
987 v1 =>
988 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Fix_Flattened_Table()',
989 t2 =>'ACTION',
990 v2 =>'Inserting deatail child mapping(s)'
991 ||' into table GL_SEG_VAL_HIERARCHIES');
992 END IF;
993
994 INSERT INTO GL_SEG_VAL_HIERARCHIES
995 (flex_value_set_id, parent_flex_value,
996 child_flex_value, summary_flag, status_code,
997 created_by, creation_date, last_updated_by,
998 last_update_login, last_update_date)
999 (SELECT DISTINCT glsvnh.flex_value_set_id,
1000 glsvh.parent_flex_value,
1001 glsvnh.child_flex_value,
1002 glsvnh.summary_flag, 'I',
1003 GL_FLATTEN_SETUP_DATA.GLSTFL_User_Id,
1004 SYSDATE,
1005 GL_FLATTEN_SETUP_DATA.GLSTFL_User_Id,
1006 GL_FLATTEN_SETUP_DATA.GLSTFL_Login_Id,
1007 SYSDATE
1008 FROM GL_SEG_VAL_NORM_HIERARCHY glsvnh,
1009 GL_SEG_VAL_HIERARCHIES glsvh
1010 WHERE glsvnh.flex_value_set_id =
1011 GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
1012 AND glsvnh.status_code = 'I'
1013 AND glsvnh.summary_flag = 'N'
1014 AND glsvh.flex_value_set_id =
1015 glsvnh.flex_value_set_id
1016 AND glsvh.child_flex_value =
1017 glsvnh.parent_flex_value
1018 AND NVL(glsvh.status_code, 'X') <> 'D'
1019 AND NOT EXISTS
1020 (SELECT 1
1021 FROM GL_SEG_VAL_HIERARCHIES glsvh2
1022 WHERE glsvh2.flex_value_set_id =
1023 glsvnh.flex_value_set_id
1024 AND glsvh2.parent_flex_value =
1025 glsvh.parent_flex_value
1026 AND glsvh2.child_flex_value =
1027 glsvnh.child_flex_value
1028 AND glsvh2.summary_flag =
1029 glsvnh.summary_flag
1030 AND NVL(glsvh2.status_code,'X') <>'D'));
1031
1032 l_no_rows := NVL(SQL%ROWCOUNT,0);
1033
1034 FND_CONCURRENT.Af_Commit; -- COMMIT point.
1035
1036 IF (l_no_rows > 0) THEN
1037 Is_Flattened_Tab_Changed := TRUE;
1038 END IF;
1039
1040 GL_MESSAGE.Write_Log(msg_name =>'SHRD0117',
1041 token_num => 2,
1042 t1 => 'NUM',
1043 v1 => TO_CHAR(l_no_rows),
1044 t2 => 'TABLE',
1045 v2 => 'GL_SEG_VAL_HIERARCHIES');
1046
1047 -- The following SQL statement insert all new parent-child mappings
1048 -- for all levels in the hierarchy into the table GL_SEG_VAL_HIERARCHIES
1049
1050 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
1051 GL_MESSAGE.Write_Log(msg_name =>'SHRD0180',
1052 token_num =>2,
1053 t1 =>'ROUTINE',
1054 v1 =>
1055 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Fix_Flattened_Table()',
1056 t2 =>'ACTION',
1057 v2 =>'Inserting Parent child mapping(s)'
1058 ||'into table GL_SEG_VAL_HIERARCHIES');
1059 END IF;
1060
1061 c_rows_process := FALSE;
1062 l_no_rows :=0;
1063
1064 WHILE NOT c_rows_process
1065 LOOP
1066 INSERT INTO GL_SEG_VAL_HIERARCHIES
1067 (flex_value_set_id, parent_flex_value,
1068 child_flex_value, summary_flag, status_code,
1069 created_by, creation_date, last_updated_by,
1070 last_update_login, last_update_date)
1071 (SELECT DISTINCT glsvnh.flex_value_set_id,
1072 glsvh1.parent_flex_value,
1073 glsvh2.child_flex_value,
1074 glsvh2.summary_flag, 'I',
1075 GL_FLATTEN_SETUP_DATA.GLSTFL_User_Id,
1076 SYSDATE,
1077 GL_FLATTEN_SETUP_DATA.GLSTFL_User_Id,
1078 GL_FLATTEN_SETUP_DATA.GLSTFL_Login_Id,
1079 SYSDATE
1080 FROM GL_SEG_VAL_NORM_HIERARCHY glsvnh,
1081 GL_SEG_VAL_HIERARCHIES glsvh1,
1082 GL_SEG_VAL_HIERARCHIES glsvh2
1083 WHERE glsvnh.flex_value_set_id =
1084 GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
1085 AND glsvnh.status_code = 'I'
1086 AND glsvnh.summary_flag = 'Y'
1087 AND glsvh1.flex_value_set_id =
1088 glsvnh.flex_value_set_id
1089 AND glsvh1.child_flex_value =
1090 glsvnh.parent_flex_value
1091 AND NVL(glsvh1.status_code, 'X') <>'D'
1092 AND glsvh2.flex_value_set_id =
1093 glsvnh.flex_value_set_id
1094 AND glsvh2.parent_flex_value =
1095 glsvnh.child_flex_value
1096 AND NVL(glsvh2.status_code, 'X') <>'D'
1097 AND NOT EXISTS
1098 (SELECT 1
1099 FROM GL_SEG_VAL_HIERARCHIES glsvh3
1100 WHERE glsvh3.flex_value_set_id =
1101 glsvnh.flex_value_set_id
1102 AND glsvh3.parent_flex_value =
1103 glsvh1.parent_flex_value
1104 AND glsvh3.child_flex_value =
1105 glsvh2.child_flex_value
1106 AND glsvh3.summary_flag =
1107 glsvh2.summary_flag
1108 AND NVL(glsvh3.status_code,'X') <>'D'));
1109
1110 l_no_rows := l_no_rows+NVL(SQL%ROWCOUNT,0);
1111
1112 -- l_commit_ctr := l_commit_ctr+NVL(SQL%ROWCOUNT,0);
1113
1114 c_rows_process := SQL%NOTFOUND;
1115
1116 FND_CONCURRENT.Af_Commit; -- COMMIT Point.
1117
1118 END LOOP;
1119
1120 IF (l_no_rows > 0) THEN
1121 Is_Flattened_Tab_Changed := TRUE;
1122 END IF;
1123
1124 GL_MESSAGE.Write_Log(msg_name =>'SHRD0117',
1125 token_num => 2,
1126 t1 =>'NUM',
1127 v1 =>TO_CHAR(l_no_rows),
1128 t2 =>'TABLE',
1129 v2 => 'GL_SEG_VAL_HIERARCHIES');
1130
1131 GL_MESSAGE.Func_Succ(func_name =>
1132 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Fix_Flattend_Table');
1133
1134 RETURN TRUE;
1135
1136 EXCEPTION
1137
1138 WHEN OTHERS THEN
1139 GL_MESSAGE.Write_Log (msg_name =>'SHRD0102',
1140 token_num => 1,
1141 t1 =>'EMESSAGE',
1142 v1 => SQLERRM);
1143
1144 GL_MESSAGE.Func_Fail(func_name =>
1145 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Fix_Flatten_Table');
1146
1147 FND_CONCURRENT.Af_Rollback; -- ROLLBACK Ponit
1148
1149 RETURN FALSE;
1150
1151 END Fix_Flattened_Table;
1152
1153 -- ******************************************************************
1154
1155 -- Function
1156 -- Clean_Up
1157 -- Purpose
1158 -- This function is to bring all records to its final state in the tables
1159 -- GL_SEG_VAL_NORM_HIERARCHY and GL_SEG_VAL_HIERARCHIES
1160 -- History
1161 -- 25-04-2001 Srini Pala Created
1162 -- Arguments
1163
1164 -- Example
1165 -- ret_status := Clean_Up()
1166 --
1167
1168 FUNCTION Clean_Up RETURN BOOLEAN IS
1169 l_no_rows NUMBER :=0;
1170 l_status VARCHAR2(1);
1171
1172 BEGIN
1173 GL_MESSAGE.Func_Ent(func_name =>
1174 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Clean_Up');
1175
1176 IF(GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
1177
1178 GL_MESSAGE.Write_Log(msg_name =>'SHRD0181',
1179 token_num => 3,
1180 t1 => 'ROUTINE',
1181 v1 =>
1182 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Clean_Up',
1183 t2 =>'VARIABLE',
1184 v2 => 'Value_Set_Id',
1185 t3 => 'VALUE',
1186 v3 =>
1187 TO_CHAR(GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID));
1188 END IF;
1189
1190 UPDATE GL_SEG_VAL_NORM_HIERARCHY
1191 SET status_code = NULL
1192 WHERE status_code = 'I'
1193 AND flex_value_set_id = GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID;
1194
1195 l_no_rows := NVL(SQL%ROWCOUNT,0);
1196
1197 GL_MESSAGE.Write_Log(msg_name =>'SHRD0118',
1198 token_num =>2,
1199 t1 =>'NUM',
1200 v1 =>TO_CHAR(l_no_rows),
1201 t2 =>'TABLE',
1202 v2 =>'GL_SEG_VAL_NORM_HIERARCHY');
1203
1204 UPDATE GL_SEG_VAL_HIERARCHIES
1205 SET status_code = NULL
1206 WHERE status_code IN ('I','U')
1207 AND flex_value_set_id = GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID;
1208
1209 l_no_rows := NVL(SQL%ROWCOUNT,0);
1210
1211 GL_MESSAGE.Write_Log(msg_name =>'SHRD0118',
1212 token_num =>2,
1213 t1 =>'NUM',
1214 v1 =>TO_CHAR(l_no_rows),
1215 t2 => 'TABLE',
1216 v2 => 'GL_SEG_VAL_HIERARCHIES');
1217
1218 -- To improve performance for bug fix # 5075776
1219 l_status := 'D';
1220
1221 DELETE FROM GL_SEG_VAL_NORM_HIERARCHY
1222 WHERE status_code = l_status
1223 AND flex_value_set_id = GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID;
1224
1225 l_no_rows := NVL(SQL%ROWCOUNT,0);
1226
1227 GL_MESSAGE.Write_Log(msg_name =>'SHRD0119',
1228 token_num =>2,
1229 t1 =>'NUM',
1230 v1 =>TO_CHAR(l_no_rows),
1231 t2 =>'TABLE',
1232 v2 =>'GL_SEG_VAL_NORM_HIERARCHY');
1233
1234 DELETE FROM GL_SEG_VAL_HIERARCHIES
1235 WHERE status_code= l_status
1236 AND flex_value_set_id = GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID;
1237 l_no_rows := NVL(SQL%ROWCOUNT,0);
1238
1239 GL_MESSAGE.Write_Log(msg_name =>'SHRD0119',
1240 token_num => 2,
1241 t1 => 'NUM',
1242 v1 => TO_CHAR(l_no_rows),
1243 t2 =>'TABLE',
1244 v2 => 'GL_SEG_VAL_HIERARCHIES');
1245
1246 GL_MESSAGE.Func_Succ(func_name =>
1247 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Clean_Up');
1248
1249 RETURN TRUE;
1250
1251 EXCEPTION
1252
1253 WHEN OTHERS THEN
1254
1255 GL_MESSAGE.Write_Log(msg_name =>'SHRD0102',
1256 token_num => 1,
1257 t1 =>'EMESSAGE',
1258 v1 => SQLERRM);
1259
1260 FND_CONCURRENT.Af_Rollback; -- ROLLBACK point
1261
1262 GL_MESSAGE.Func_Fail(func_name =>
1263 'GL_FLATTEN_SEG_VAL_HIERARCHIES.Clean_Up');
1264
1265 RETURN FALSE;
1266
1267 END Clean_Up;
1268
1269
1270 -- ******************************************************************
1271
1272 END GL_FLATTEN_SEG_VAL_HIERARCHIES;