17: BEGIN
18:
19:
20:
21: GL_MESSAGE.Func_Ent(func_name =>
22: 'GL_FLATTEN_SETUP_DATA.Check_Seg_val_Hierarchy');
23:
24: GLSTFL_VS_ID := x_vs_id ;
25: GL_MESSAGE.Write_Log(msg_name => 'Value Set is '||GLSTFL_VS_ID,
21: GL_MESSAGE.Func_Ent(func_name =>
22: 'GL_FLATTEN_SETUP_DATA.Check_Seg_val_Hierarchy');
23:
24: GLSTFL_VS_ID := x_vs_id ;
25: GL_MESSAGE.Write_Log(msg_name => 'Value Set is '||GLSTFL_VS_ID,
26: token_num => 0);
27:
28: -- Call routine to check if the value set is a table
29: -- validated set.
75: IF (NOT result_val) THEN
76: RAISE Seg_val_Hier_err;
77: END IF;
78:
79: GL_MESSAGE.Func_Succ(func_name =>
80: 'GL_FLATTEN_SETUP_DATA.Check_Seg_val_Hierarchy');
81:
82: Return True;
83:
86: -- Release exclusive lock on the value set ID
87: result_val := GL_FLATTEN_SETUP_DATA.Release_Lock
88: (X_Param_Type => 'V',
89: X_Param_Id => GLSTFL_VS_ID);
90: GL_MESSAGE.Func_Fail(func_name =>
91: 'GL_FLATTEN_SETUP_DATA.Check_Seg_val_Hierarchy');
92: Return False;
93:
94: END Check_Seg_val_Hierarchy;
112: schema VARCHAR2(30):= NULL;
113:
114: BEGIN
115:
116: GL_MESSAGE.Func_Ent(func_name => 'GL_FLATTEN_SETUP_DATA.Main');
117:
118: -- Obtain user ID, login ID and concurrent request ID and initialize
119:
120: -- package variables
128: GLSTFL_LOGIN_ID is NULL OR
129: GLSTFL_REQ_ID is NULL) THEN
130:
131: -- Fail to initialize
132: GL_MESSAGE.Write_Log(msg_name => 'FLAT0011',
133: token_num => 0);
134:
135: RAISE GLSTFL_fatal_err;
136: END IF;
136: END IF;
137:
138: FND_PROFILE.GET('GL_DEBUG_MODE', l_dmode_profile);
139:
140: GL_MESSAGE.Write_Log(msg_name =>'SHRD0181',
141: token_num => 3 ,
142: t1 =>'ROUTINE',
143: v1 =>
144: 'GL_FLATTEN_SETUP_DATA.Main',
158: -- Turn trace on if process is run in debug mode
159: IF (GLSTFL_Debug) THEN
160:
161: -- Program running in debug mode, turning trace on
162: GL_MESSAGE.Write_Log(msg_name => 'FLAT0012',
163: token_num => 0);
164:
165: EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE';
166:
209: AND rownum = 1;
210:
211: ELSE
212: -- Invalid Operation mode, error out
213: GL_MESSAGE.Write_Log(msg_name => 'FLAT0013',
214: token_num => 0);
215:
216: RAISE GLSTFL_fatal_err;
217:
217:
218: END IF;
219:
220: -- Print out program parameters
221: GL_MESSAGE.Write_Log(msg_name => 'FLAT0014',
222: token_num => 5,
223: t1 => 'REQ_ID',
224: v1 => TO_CHAR(GLSTFL_REQ_ID),
225: t2 => 'OP_MODE',
230: v4 => TO_CHAR(GLSTFL_VS_ID),
231: t5 => 'VS_TAB_NAME',
232: v5 => GLSTFL_VS_TAB_NAME);
233:
234: GL_MESSAGE.Write_Log(msg_name => 'FLAT0018',
235: token_num => 3,
236: t1 => 'BAL_VS_ID',
237: v1 => TO_CHAR(GLSTFL_BAL_VS_ID),
238: t2 => 'MGT_VS_ID',
278: -- 3) GL_LEDGER_SET_NORM_ASSIGN: VS, LS
279: -- 4) GL_ACCESS_SET_NORM_ASSIGN: AS
280: IF (GLSTFL_OP_MODE IN ('LV', 'VH', 'VS')) THEN
281: IF (GLSTFL_Debug) THEN
282: GL_MESSAGE.Write_Log
283: (msg_name => 'SHRD0180',
284: token_num => 2,
285: t1 => 'ROUTINE',
286: v1 => 'Main()',
298: GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID);
299:
300: IF (GLSTFL_Debug) THEN
301: row_count := SQL%ROWCOUNT;
302: GL_MESSAGE.Write_Log(msg_name => 'SHRD0118',
303: token_num => 2,
304: t1 => 'NUM',
305: v1 => TO_CHAR(row_count),
306: t2 => 'TABLE',
311: END IF;
312:
313: IF (GLSTFL_OP_MODE IN ('LS', 'VS')) THEN
314: IF (GLSTFL_Debug) THEN
315: GL_MESSAGE.Write_Log
316: (msg_name => 'SHRD0180',
317: token_num => 2,
318: t1 => 'ROUTINE',
319: v1 => 'Main()',
328: WHERE status_code is NOT NULL;
329:
330: IF (GLSTFL_Debug) THEN
331: row_count := SQL%ROWCOUNT;
332: GL_MESSAGE.Write_Log(msg_name => 'SHRD0118',
333: token_num => 2,
334: t1 => 'NUM',
335: v1 => TO_CHAR(row_count),
336: t2 => 'TABLE',
341: END IF;
342:
343: IF (GLSTFL_OP_MODE = 'AS') THEN
344: IF (GLSTFL_Debug) THEN
345: GL_MESSAGE.Write_Log
346: (msg_name => 'SHRD0180',
347: token_num => 2,
348: t1 => 'ROUTINE',
349: v1 => 'Main()',
358: WHERE status_code is NOT NULL;
359:
360: IF (GLSTFL_Debug) THEN
361: row_count := SQL%ROWCOUNT;
362: GL_MESSAGE.Write_Log(msg_name => 'SHRD0118',
363: token_num => 2,
364: t1 => 'NUM',
365: v1 => TO_CHAR(row_count),
366: t2 => 'TABLE',
417: IF (cont_processing) THEN
418:
419: -- print out debug message
420: IF (GLSTFL_Debug) THEN
421: GL_MESSAGE.Write_Log(msg_name => 'FLAT0015',
422: token_num => 0);
423: END IF;
424:
425: ret_val := GL_FLATTEN_LEDGER_SEG_VALS.Fix_By_Value_Set;
608: END IF;
609:
610: -- Perform full refresh on materialized view GL_ACCESS_SET_LEDGERS
611: IF (GLSTFL_OP_MODE NOT IN ('SH', 'FF', 'LV')) THEN
612: GL_MESSAGE.Func_Ent(func_name => 'GL_FLATTEN_SETUP_DATA.MV_Refresh');
613:
614: rval := fnd_installation.get_app_info('SQLGL', dummy1, dummy2, schema);
615:
616: GL_MESSAGE.Write_Log(msg_name =>'SHRD0181',
612: GL_MESSAGE.Func_Ent(func_name => 'GL_FLATTEN_SETUP_DATA.MV_Refresh');
613:
614: rval := fnd_installation.get_app_info('SQLGL', dummy1, dummy2, schema);
615:
616: GL_MESSAGE.Write_Log(msg_name =>'SHRD0181',
617: token_num => 3 ,
618: t1 =>'ROUTINE',
619: v1 =>
620: 'GL_FLATTEN_SETUP_DATA.MV_Refresh',
624: v3 => schema);
625:
626: DBMS_MVIEW.Refresh('GL_ACCESS_SET_LEDGERS');
627:
628: GL_MESSAGE.Func_Succ(func_name => 'GL_FLATTEN_SETUP_DATA.MV_Refresh');
629:
630: END IF;
631:
632: -- Release all locks
678: END IF;
679: END IF;
680: END IF;
681:
682: GL_MESSAGE.Func_Succ(func_name => 'GL_FLATTEN_SETUP_DATA.Main');
683:
684: ret_val := FND_CONCURRENT.Set_Completion_Status
685: (status => 'COMPLETE', message => NULL);
686:
714: X_Param_Id => GLSTFL_MGT_VS_ID);
715: END IF;
716: END IF;
717:
718: GL_MESSAGE.Write_Log
719: (msg_name => 'FLAT0002',
720: token_num => 1,
721: t1 => 'ROUTINE_NAME',
722: v1 => 'GL_FLATTEN_SETUP_DATA.Main()');
720: token_num => 1,
721: t1 => 'ROUTINE_NAME',
722: v1 => 'GL_FLATTEN_SETUP_DATA.Main()');
723:
724: GL_MESSAGE.Func_Fail(func_name => 'GL_FLATTEN_SETUP_DATA.Main');
725:
726: ret_val := FND_CONCURRENT.Set_Completion_Status
727: (status => 'ERROR', message => NULL);
728:
754: X_Param_Id => GLSTFL_MGT_VS_ID);
755: END IF;
756: END IF;
757:
758: GL_MESSAGE.Write_Log(msg_name => 'SHRD0203',
759: token_num => 2,
760: t1 => 'FUNCTION',
761: v1 => 'GL_FLATTEN_SETUP_DATA.Main()',
762: t2 => 'SQLERRMC',
761: v1 => 'GL_FLATTEN_SETUP_DATA.Main()',
762: t2 => 'SQLERRMC',
763: v2 => SQLERRM);
764:
765: GL_MESSAGE.Func_Fail(func_name => 'GL_FLATTEN_SETUP_DATA.Main');
766:
767: ret_val := FND_CONCURRENT.Set_Completion_Status
768: (status => 'ERROR', message => NULL);
769:
795: ret_val BOOLEAN;
796: GLSTFL_fatal_err EXCEPTION;
797: BEGIN
798:
799: GL_MESSAGE.Func_Ent(func_name => 'GL_FLATTEN_SETUP_DATA.Clean_Up');
800:
801: -- Start cleaning up according to the mode of operation.
802: --
803: -- Here is the list of routines called by each mode:
960:
961: -- Commit all work
962: FND_CONCURRENT.Af_Commit;
963:
964: GL_MESSAGE.Func_Succ(func_name => 'GL_FLATTEN_SETUP_DATA.Clean_Up');
965:
966: RETURN TRUE;
967:
968: EXCEPTION
967:
968: EXCEPTION
969: WHEN GLSTFL_fatal_err THEN
970:
971: GL_MESSAGE.Write_Log(msg_name => 'FLAT0002',
972: token_num => 1,
973: t1 => 'ROUTINE_NAME',
974: v1 => 'GL_FLATTEN_SETUP_DATA.Clean_Up()');
975:
975:
976: -- Rollback
977: FND_CONCURRENT.Af_Rollback;
978:
979: GL_MESSAGE.Func_Fail(func_name =>'GL_FLATTEN_SETUP_DATA.Clean_Up');
980:
981: RETURN FALSE;
982:
983: END Clean_Up;
991: tab_name VARCHAR2(240) := NULL;
992: col_name VARCHAR2(240) := NULL;
993: BEGIN
994:
995: GL_MESSAGE.Func_Ent
996: (func_name => 'GL_FLATTEN_SETUP_DATA.Get_Value_Set_Info');
997:
998: -- Execute statement to determine if the value set is table validated
999: BEGIN
1015: Table_Name := tab_name;
1016: Column_Name := col_name;
1017: END IF;
1018:
1019: GL_MESSAGE.Func_Succ
1020: (func_name => 'GL_FLATTEN_SETUP_DATA.Get_Value_Set_Info');
1021:
1022: RETURN TRUE;
1023:
1022: RETURN TRUE;
1023:
1024: EXCEPTION
1025: WHEN OTHERS THEN
1026: GL_MESSAGE.Write_Log
1027: (msg_name => 'SHRD0203',
1028: token_num => 2,
1029: t1 => 'FUNCTION',
1030: v1 => 'GL_FLATTEN_SETUP_DATA.Get_Value_Set_Info()',
1030: v1 => 'GL_FLATTEN_SETUP_DATA.Get_Value_Set_Info()',
1031: t2 => 'SQLERRMC',
1032: v2 => SQLERRM);
1033:
1034: GL_MESSAGE.Func_Fail
1035: (func_name => 'GL_FLATTEN_SETUP_DATA.Get_Value_Set_Info');
1036:
1037: FND_CONCURRENT.Af_Rollback;
1038:
1057: got_lock BOOLEAN := FALSE;
1058: trial_num NUMBER := 0;
1059: BEGIN
1060:
1061: GL_MESSAGE.Func_Ent(func_name => 'GL_FLATTEN_SETUP_DATA.Request_Lock');
1062:
1063: -- generate name for the user defined lock
1064: IF (X_Param_Type = 'C') THEN
1065: lkname := 'GLSTFL_COA_' || TO_CHAR(X_Param_Id);
1068: ELSE
1069: -- Invalid parameter type, print message and error out
1070:
1071: -- PARAM_VALUE is not a valid value for parameter PARAM_NAME
1072: GL_MESSAGE.Write_Log(msg_name => 'FLAT0006',
1073: token_num => 2,
1074: t1 => 'PARAM_NAME',
1075: v1 => 'X_Param_Type',
1076: t2 => 'PARAM_VALUE',
1106: trial_num := trial_num + 1;
1107:
1108: -- Cannot obtain user named lock LOCK_NAME, putting the proccess
1109: -- to sleep for SLEEP_TIME minutes before trying again.
1110: GL_MESSAGE.Write_Log(msg_name => 'FLAT0007',
1111: token_num => 2,
1112: t1 => 'LOCK_NAME',
1113: v1 => lkname,
1114: t2 => 'SLEEP_TIME',
1120: -- Either encounter deadlock, parameter error or illegal lock handle.
1121: -- Print out appropriate message and error out
1122:
1123: -- Fatal error occurred when obtaining user named lock LOCK_NAME
1124: GL_MESSAGE.Write_Log(msg_name => 'FLAT0008',
1125: token_num => 1,
1126: t1 => 'LOCK_NAME',
1127: v1 => lkname);
1128: RAISE GLSTFL_fatal_err;
1129: END IF;
1130: END LOOP;
1131:
1132: IF (got_lock) THEN
1133: GL_MESSAGE.Func_Succ(func_name => 'GL_FLATTEN_SETUP_DATA.Request_Lock');
1134:
1135: RETURN TRUE;
1136: ELSE
1137: -- Cannot obtain lock after maximum number of attempts.
1138: -- Print out appropriate message and raise exception
1139:
1140: -- Program failed to obtain user named lock LOCK_NAME after
1141: -- MAX_ATTEMPTS attempts.
1142: GL_MESSAGE.Write_Log(msg_name => 'FLAT0009',
1143: token_num => 2,
1144: t1 => 'LOCK_NAME',
1145: v1 => lkname,
1146: t2 => 'MAX_ATTEMPTS',
1150:
1151: EXCEPTION
1152: WHEN GLSTFL_fatal_err THEN
1153:
1154: GL_MESSAGE.Write_Log
1155: (msg_name => 'FLAT0002',
1156: token_num => 1,
1157: t1 => 'ROUTINE_NAME',
1158: v1 => 'GL_FLATTEN_SETUP_DATA.Request_Lock()');
1156: token_num => 1,
1157: t1 => 'ROUTINE_NAME',
1158: v1 => 'GL_FLATTEN_SETUP_DATA.Request_Lock()');
1159:
1160: GL_MESSAGE.Func_Fail(func_name => 'GL_FLATTEN_SETUP_DATA.Request_Lock');
1161:
1162: FND_CONCURRENT.Af_Rollback;
1163:
1164: RETURN FALSE;
1163:
1164: RETURN FALSE;
1165:
1166: WHEN OTHERS THEN
1167: GL_MESSAGE.Write_Log
1168: (msg_name => 'SHRD0203',
1169: token_num => 2,
1170: t1 => 'FUNCTION',
1171: v1 => 'GL_FLATTEN_SETUP_DATA.Request_Lock()',
1171: v1 => 'GL_FLATTEN_SETUP_DATA.Request_Lock()',
1172: t2 => 'SQLERRMC',
1173: v2 => SQLERRM);
1174:
1175: GL_MESSAGE.Func_Fail
1176: (func_name => 'GL_FLATTEN_SETUP_DATA.Request_Lock');
1177:
1178: FND_CONCURRENT.Af_Rollback;
1179:
1191: lkresult INTEGER;
1192: GLSTFL_fatal_err EXCEPTION;
1193: BEGIN
1194:
1195: GL_MESSAGE.Func_Ent(func_name => 'GL_FLATTEN_SETUP_DATA.Release_Lock');
1196:
1197:
1198: -- generate name for the user defined lock
1199: IF (X_Param_Type = 'C') THEN
1201: ELSIF (X_Param_Type = 'V') THEN
1202: lkname := 'GLSTFL_VS_' || TO_CHAR(X_Param_Id);
1203: ELSE
1204: -- Invalid parameter type, print message and error out
1205: GL_MESSAGE.Write_Log(msg_name => 'FLAT0006',
1206: token_num => 2,
1207: t1 => 'PARAM_NAME',
1208: v1 => 'X_Param_Type',
1209: t2 => 'PARAM_VALUE',
1211:
1212: RAISE GLSTFL_fatal_err;
1213: END IF;
1214: IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
1215: GL_MESSAGE.Write_Log
1216: (msg_name => 'SHRD0180',
1217: token_num => 2,
1218: t1 => 'ROUTINE',
1219: v1 => 'Main()',
1228: -- release the user named lock
1229: lkresult := DBMS_LOCK.Release(lockhandle => lkhandle);
1230:
1231: IF (lkresult = 0) THEN
1232: GL_MESSAGE.Func_Succ(func_name => 'GL_FLATTEN_SETUP_DATA.Release_Lock');
1233:
1234: RETURN TRUE;
1235: ELSE
1236: -- Errors encountered when releasing the lock
1233:
1234: RETURN TRUE;
1235: ELSE
1236: -- Errors encountered when releasing the lock
1237: GL_MESSAGE.Write_Log(msg_name => 'FLAT0010',
1238: token_num => 0);
1239: RAISE GLSTFL_fatal_err;
1240: END IF;
1241:
1241:
1242: EXCEPTION
1243: WHEN GLSTFL_fatal_err THEN
1244:
1245: GL_MESSAGE.Write_Log
1246: (msg_name => 'FLAT0002',
1247: token_num => 1,
1248: t1 => 'ROUTINE_NAME',
1249: v1 => 'GL_FLATTEN_SETUP_DATA.Release_Lock()');
1247: token_num => 1,
1248: t1 => 'ROUTINE_NAME',
1249: v1 => 'GL_FLATTEN_SETUP_DATA.Release_Lock()');
1250:
1251: GL_MESSAGE.Func_Fail
1252: (func_name =>'GL_FLATTEN_SETUP_DATA.Release_Lock');
1253:
1254: FND_CONCURRENT.Af_Rollback;
1255:
1255:
1256: RETURN FALSE;
1257:
1258: WHEN OTHERS THEN
1259: GL_MESSAGE.Write_Log
1260: (msg_name => 'SHRD0203',
1261: token_num => 2,
1262: t1 => 'FUNCTION',
1263: v1 => 'GL_FLATTEN_SETUP_DATA.Release_Lock()',
1263: v1 => 'GL_FLATTEN_SETUP_DATA.Release_Lock()',
1264: t2 => 'SQLERRMC',
1265: v2 => SQLERRM);
1266:
1267: GL_MESSAGE.Func_Fail
1268: (func_name => 'GL_FLATTEN_SETUP_DATA.Release_Lock');
1269:
1270: FND_CONCURRENT.Af_Rollback;
1271: