[Home] [Help]
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
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.
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:
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:
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
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);
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,
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
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:
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
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
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.
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,
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
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
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;
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: /* removed alias tv from add_table , column_name in below dynamic sql*/
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,
295: SYSDATE
296: FROM ' ||add_table || '
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''
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
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:
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:
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 =
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'
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
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
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
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 =
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;
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 ,
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
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,
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'
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
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 =
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
841: token_num => 2,
842: t1 =>'NUM',
843: v1 =>TO_CHAR(l_no_rows),
844: t2 =>'TABLE',
845: v2 =>'GL_SEG_VAL_HIERARCHIES');
846:
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.
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
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
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 =
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:
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',
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,
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,
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')
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
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:
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,
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)
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'
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
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:
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,
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;
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)
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'
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'
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
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:
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:
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:
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:
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:
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: