[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
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
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:
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:
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);
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,
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
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:
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:
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
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
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
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
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:
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
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,
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,
627: last_updated_by, last_update_login, last_update_date)
628: (SELECT DISTINCT ffvnh.flex_value_set_id,
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
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:
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'
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'
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'
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
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'
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
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:
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:
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')
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);
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;