[Home] [Help]
1: PACKAGE BODY CSM_QUERY_PKG AS
2: /* $Header: csmqryb.pls 120.17.12020000.2 2013/04/09 11:01:55 saradhak ship $ */
3:
4: /*
5: * The function to be called by Mobile Admin page to store defined queries
13: -- --------- ------------------- ------------------------------------------
14: -- Enter package declarations as shown below
15:
16: /*** Globals ***/
17: g_object_name CONSTANT VARCHAR2(30) := 'CSM_QUERY_PKG'; -- package name
18: g_pub_name CONSTANT VARCHAR2(30) := 'CSM_QUERIES'; -- publication item name
19: g_debug_level NUMBER; -- debug level
20: g_pub_item_qres VARCHAR2(30) := 'CSM_QUERY_RESULTS';
21: g_pub_item_qry VARCHAR2(50) := 'CSM_QUERY';
39: l_mark_dirty BOOLEAN;
40: BEGIN
41: CSM_UTIL_PKG.LOG
42: ( 'Entering UPDATE_EXE_STATUS for User ID, Query Id, Instance ID : ' || p_USER_ID ||
43: ':' || p_QUERY_ID || ':' || p_INSTANCE_ID,'CSM_QUERY_PKG.UPDATE_EXE_STATUS',
44: FND_LOG.LEVEL_ERROR);
45:
46: UPDATE CSM_QUERY_INSTANCES_ACC
47: SET QUERY_START_TIME = NVL(p_QSTART_TIME,QUERY_START_TIME) ,
63: END IF;
64:
65: CSM_UTIL_PKG.LOG
66: ( 'Leaving UPDATE_EXE_STATUS for User ID, Query Id, Instance ID : ' || p_USER_ID ||
67: ':' || p_QUERY_ID || ':' || p_INSTANCE_ID,'CSM_QUERY_PKG.UPDATE_EXE_STATUS',
68: FND_LOG.LEVEL_ERROR);
69:
70: EXCEPTION WHEN OTHERS THEN
71: /*** catch and log exceptions ***/
70: EXCEPTION WHEN OTHERS THEN
71: /*** catch and log exceptions ***/
72: CSM_UTIL_PKG.LOG
73: ( 'Exception occurred in UPDATE_EXE_STATUS for User ID, Query Id, Instance ID : ' || p_USER_ID ||
74: ':' || p_QUERY_ID || ':' || p_INSTANCE_ID || SQLERRM, 'CSM_QUERY_PKG.UPDATE_EXE_STATUS',
75: FND_LOG.LEVEL_EXCEPTION);
76: RAISE;
77: END UPDATE_EXE_STATUS;
78:
127: l_responsibility_id NUMBER := NULL;
128: l_VARIABLE_VALUE_CHAR VARCHAR2(4000);
129: BEGIN
130: CSM_UTIL_PKG.LOG
131: ( 'Entering INSERT_QUERY for Query Name :' || p_QUERY_NAME ,'CSM_QUERY_PKG.INSERT_QUERY',
132: FND_LOG.LEVEL_ERROR);
133:
134: IF p_LEVEL_ID = 10003 THEN
135: l_responsibility_id := p_LEVEL_VALUE;
145: x_error_message => l_return_message);
146:
147: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
148: CSM_UTIL_PKG.LOG
149: ( 'Exception occurred in VALIDATE_ACCESS for Query Name ' || p_QUERY_NAME || SQLERRM, 'CSM_QUERY_PKG.INSERT_QUERY',
150: FND_LOG.LEVEL_EXCEPTION);
151: x_return_status := FND_API.G_RET_STS_ERROR;
152: x_error_message := 'Query Access Validation failed : ' || l_return_message;
153: RETURN;
160: x_error_message => l_return_message);
161:
162: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
163: CSM_UTIL_PKG.LOG
164: ( 'Exception occurred in VALIDATE_QUERY for Query Name ' || p_QUERY_NAME || SQLERRM, 'CSM_QUERY_PKG.INSERT_QUERY',
165: FND_LOG.LEVEL_EXCEPTION);
166: x_return_status := FND_API.G_RET_STS_ERROR;
167: x_error_message := 'Query Validation failed : ' || l_return_message;
168: RETURN;
176: x_error_message => l_return_message
177: );
178: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
179: CSM_UTIL_PKG.LOG
180: ( 'Exception occurred in VALIDATE_WORLFLOW for Query Name ' || p_QUERY_NAME || SQLERRM, 'CSM_QUERY_PKG.INSERT_QUERY',
181: FND_LOG.LEVEL_EXCEPTION);
182: x_return_status := FND_API.G_RET_STS_ERROR;
183: x_error_message := 'Query Validation failed : ' || l_return_message;
184: RETURN;
194: );
195:
196: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
197: CSM_UTIL_PKG.LOG
198: ( 'Exception occurred in VALIDATE_PROCEDURE for Query Name ' || p_QUERY_NAME || SQLERRM, 'CSM_QUERY_PKG.INSERT_QUERY',
199: FND_LOG.LEVEL_EXCEPTION);
200: x_return_status := FND_API.G_RET_STS_ERROR;
201: x_error_message := 'Query Validation failed : ' || l_return_message;
202: RETURN;
206: IF p_VARIABLE_NAME IS NOT NULL AND p_VARIABLE_TYPE IS NOT NULL THEN
207:
208: IF p_VARIABLE_NAME.COUNT <> p_VARIABLE_TYPE.COUNT THEN
209: CSM_UTIL_PKG.LOG
210: ( 'Variable Name and Variable Type Mismatch: Leaving INSERT_QUERY for Query Name :' || p_QUERY_NAME ,'CSM_QUERY_PKG.INSERT_QUERY',
211: FND_LOG.LEVEL_ERROR);
212:
213: x_return_status := FND_API.G_RET_STS_ERROR;
214: x_error_message := 'Query insert failed for Query Name: ' || p_QUERY_NAME ||
219: IF p_VARIABLE_NAME IS NULL AND p_VARIABLE_TYPE IS NULL THEN
220: l_qry_with_no_var := 'Y';
221: ELSE
222: CSM_UTIL_PKG.LOG
223: ( 'Variable Name and Variable Type Mismatch: Leaving INSERT_QUERY for Query Name :' || p_QUERY_NAME ,'CSM_QUERY_PKG.INSERT_QUERY',
224: FND_LOG.LEVEL_ERROR);
225:
226: x_return_status := FND_API.G_RET_STS_ERROR;
227: x_error_message := 'Query insert failed for Query Name: ' || p_QUERY_NAME ||
284: END LOOP;
285: END IF;
286:
287: COMMIT;
288: CSM_UTIL_PKG.LOG( 'Leaving INSERT_QUERY for Query Name :' || p_QUERY_NAME ,'CSM_QUERY_PKG.INSERT_QUERY',
289: FND_LOG.LEVEL_ERROR);
290:
291: x_return_status := FND_API.G_RET_STS_SUCCESS;
292:
292:
293: EXCEPTION WHEN OTHERS THEN
294: /*** catch and log exceptions ***/
295: CSM_UTIL_PKG.LOG
296: ( 'Exception occurred in INSERT_QUERY for Query Name ' || p_QUERY_NAME || SQLERRM, 'CSM_QUERY_PKG.INSERT_QUERY',
297: FND_LOG.LEVEL_EXCEPTION);
298: x_return_status := FND_API.G_RET_STS_ERROR;
299: x_error_message := 'Query insert failed for Query Name: ' || p_QUERY_NAME ||
300: ' : ' || SUBSTR(SQLERRM,1,3000);
363: l_VARIABLE_VALUE_CHAR VARCHAR2(4000);
364:
365: BEGIN
366: CSM_UTIL_PKG.LOG
367: ( 'Entering UPDATE_QUERY for Query Id and Query Name : ' || p_QUERY_ID || ' : ' || p_QUERY_NAME ,'CSM_QUERY_PKG.UPDATE_QUERY',
368: FND_LOG.LEVEL_ERROR);
369:
370: IF p_LEVEL_ID = 10003 THEN
371: l_responsibility_id := p_LEVEL_VALUE;
381: x_error_message => l_return_message);
382:
383: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
384: CSM_UTIL_PKG.LOG
385: ( 'Exception occurred in VALIDATE_Access for Query Name ' || p_QUERY_NAME || SQLERRM, 'CSM_QUERY_PKG.UPDATE_QUERY',
386: FND_LOG.LEVEL_EXCEPTION);
387: x_return_status := FND_API.G_RET_STS_ERROR;
388: x_error_message := 'Query Access Validation failed : ' || l_return_message;
389: RETURN;
397: x_error_message => l_return_message);
398:
399: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
400: CSM_UTIL_PKG.LOG
401: ( 'Exception occurred in VALIDATE_QUERY for Query Id ' || p_QUERY_ID || SQLERRM, 'CSM_QUERY_PKG.UPDATE_QUERY',
402: FND_LOG.LEVEL_EXCEPTION);
403: x_return_status := FND_API.G_RET_STS_ERROR;
404: x_error_message := 'Query Validation failed : ' || l_return_message;
405: RETURN;
413: x_error_message => l_return_message
414: );
415: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
416: CSM_UTIL_PKG.LOG
417: ( 'Exception occurred in VALIDATE_WORLFLOW for Query Name ' || p_QUERY_NAME || SQLERRM, 'CSM_QUERY_PKG.UPDATE_QUERY',
418: FND_LOG.LEVEL_EXCEPTION);
419: x_return_status := FND_API.G_RET_STS_ERROR;
420: x_error_message := 'Query Validation failed : ' || l_return_message;
421: RETURN;
429: x_error_message => l_return_message
430: );
431: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
432: CSM_UTIL_PKG.LOG
433: ( 'Exception occurred in VALIDATE_PROCEDURE for Query Name ' || p_QUERY_NAME || SQLERRM, 'CSM_QUERY_PKG.UPDATE_QUERY',
434: FND_LOG.LEVEL_EXCEPTION);
435: x_return_status := FND_API.G_RET_STS_ERROR;
436: x_error_message := 'Query Validation failed : ' || l_return_message;
437: RETURN;
444:
445: IF p_LEVEL_ID =10003 AND p_LEVEL_VALUE <> l_old_LEVEL_VALUE THEN
446:
447: CSM_UTIL_PKG.LOG
448: ( 'Responsibility Mapping for the query changed : ' || p_QUERY_ID || ' : ' || p_QUERY_NAME ,'CSM_QUERY_PKG.UPDATE_QUERY',
449: FND_LOG.LEVEL_ERROR);
450: --do soft delete to maintain history
451: DELETE_QUERY
452: ( p_QUERY_ID => p_QUERY_ID,
455: );
456:
457: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
458: CSM_UTIL_PKG.LOG
459: ( 'ERROR occurred in DELETE_QUERY for Query Name ' || p_QUERY_NAME || SQLERRM, 'CSM_QUERY_PKG.UPDATE_QUERY',
460: FND_LOG.LEVEL_EXCEPTION);
461: x_return_status := FND_API.G_RET_STS_ERROR;
462: x_error_message := 'Query Delete failed durin Update: ' || l_return_message;
463: RETURN;
496: );
497:
498: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
499: CSM_UTIL_PKG.LOG
500: ( 'ERROR occurred in INSERT_QUERY for Query Name ' || p_QUERY_NAME || SUBSTR(SQLERRM,1,3000) , 'CSM_QUERY_PKG.UPDATE_QUERY',
501: FND_LOG.LEVEL_EXCEPTION);
502: x_return_status := FND_API.G_RET_STS_ERROR;
503: x_error_message := 'INSERT_QUERY failed during UPDATE: ' || l_return_message;
504: ROLLBACK;
504: ROLLBACK;
505: RETURN;
506: ELSE
507: CSM_UTIL_PKG.LOG
508: ( 'Leaving CSM_QUERY_PKG.UPDATE_QUERY for Query Id and Query Name : ' || p_QUERY_ID || ' : ' || p_QUERY_NAME ,'CSM_QUERY_PKG.UPDATE_QUERY',
509: FND_LOG.LEVEL_ERROR);
510: x_return_status := FND_API.G_RET_STS_SUCCESS;
511: COMMIT;
512: RETURN;
544: IF p_VARIABLE_NAME IS NOT NULL AND p_VARIABLE_TYPE IS NOT NULL THEN
545:
546: IF p_VARIABLE_NAME.COUNT <> p_VARIABLE_TYPE.COUNT THEN
547: CSM_UTIL_PKG.LOG
548: ( 'Variable Name and Variable Type Mismatch: Leaving UPDATE_QUERY for Query Id and Query Name : ' || p_QUERY_ID || ' : ' || p_QUERY_NAME ,'CSM_QUERY_PKG.UPDATE_QUERY',
549: FND_LOG.LEVEL_ERROR);
550:
551: x_return_status := FND_API.G_RET_STS_ERROR;
552: x_error_message := ' UPDATE_QUERY Failed for Query Id and Query Name : ' || p_QUERY_ID || ' : ' ||
557: IF p_VARIABLE_NAME IS NULL AND p_VARIABLE_TYPE IS NULL THEN
558: l_qry_with_no_var := 'Y';
559: ELSE
560: CSM_UTIL_PKG.LOG
561: ( 'Variable Name and Variable Type Mismatch: Leaving UPDATE_QUERY for Query Id and Query Name : ' || p_QUERY_ID || ' : ' || p_QUERY_NAME ,'CSM_QUERY_PKG.UPDATE_QUERY',
562: FND_LOG.LEVEL_ERROR);
563:
564: x_return_status := FND_API.G_RET_STS_ERROR;
565: x_error_message := ' UPDATE_QUERY Failed for Query Id and Query Name : ' || p_QUERY_ID || ' : ' ||
628: END IF;
629:
630: COMMIT;
631: CSM_UTIL_PKG.LOG
632: ( 'Leaving UPDATE_QUERY for Query Id and Query Name : ' || p_QUERY_ID || ' : ' || p_QUERY_NAME ,'CSM_QUERY_PKG.UPDATE_QUERY',
633: FND_LOG.LEVEL_ERROR);
634: x_return_status := FND_API.G_RET_STS_SUCCESS;
635: EXCEPTION WHEN OTHERS THEN
636: /*** catch and log exceptions ***/
634: x_return_status := FND_API.G_RET_STS_SUCCESS;
635: EXCEPTION WHEN OTHERS THEN
636: /*** catch and log exceptions ***/
637: CSM_UTIL_PKG.LOG
638: ( 'Exception occurred in UPDATE_QUERY for Query Id and Query Name : ' || p_QUERY_ID || ' : ' || p_QUERY_NAME || SQLERRM, 'CSM_QUERY_PKG.UPDATE_QUERY',
639: FND_LOG.LEVEL_EXCEPTION);
640: x_return_status := FND_API.G_RET_STS_ERROR;
641: x_error_message := 'Query Update Failed for Query Id and Query Name : ' || p_QUERY_ID || ' : ' || p_QUERY_NAME ||
642: ' : ' || SUBSTR(SQLERRM,1,3000);
654: )
655: AS
656: BEGIN
657: CSM_UTIL_PKG.LOG
658: ( 'Entering DELETE_QUERY for Query Id and Query Name : ' || p_QUERY_ID ,'CSM_QUERY_PKG.DELETE_QUERY',
659: FND_LOG.LEVEL_ERROR);
660:
661: --Update the Query Definition in the base table
662: UPDATE CSM_QUERY_B
676:
677: COMMIT;
678:
679: CSM_UTIL_PKG.LOG
680: ( 'Leaving DELETE_QUERY for Query Id and Query Name : ' || p_QUERY_ID ,'CSM_QUERY_PKG.DELETE_QUERY',
681: FND_LOG.LEVEL_ERROR);
682: x_return_status := FND_API.G_RET_STS_SUCCESS;
683: EXCEPTION WHEN OTHERS THEN
684: /*** catch and log exceptions ***/
682: x_return_status := FND_API.G_RET_STS_SUCCESS;
683: EXCEPTION WHEN OTHERS THEN
684: /*** catch and log exceptions ***/
685: CSM_UTIL_PKG.LOG
686: ( 'Exception occurred in DELETE_QUERY for Query Id and Query Name : ' || p_QUERY_ID || SQLERRM, 'CSM_QUERY_PKG.DELETE_QUERY',
687: FND_LOG.LEVEL_EXCEPTION);
688: x_return_status := FND_API.G_RET_STS_ERROR;
689: x_error_message := 'Query Delete Failed for Query Id and Query Name : ' || p_QUERY_ID ;
690: END DELETE_QUERY;
717: FROM CSM_QUERY_B
718: WHERE QUERY_ID = c_QUERY_ID;
719: BEGIN
720: CSM_UTIL_PKG.LOG
721: ( 'Entering VALIDATE_QUERY for Query Id : ' || p_QUERY_ID ,'CSM_QUERY_PKG.VALIDATE_QUERY',
722: FND_LOG.LEVEL_ERROR);
723:
724: l_QUERY_TEXT1 := p_QUERY_TEXT1;
725: l_QUERY_TEXT2 := p_QUERY_TEXT2;
736: --l_QUERY_TEXT1 := REPLACE(l_QUERY_TEXT1,'--','');
737: --l_QUERY_TEXT2 := REPLACE(l_QUERY_TEXT2,'--','');
738:
739: IF l_QUERY_TEXT1 IS NULL AND l_QUERY_TEXT2 IS NULL THEN
740: CSM_UTIL_PKG.LOG( 'Error in VALIDATE_QUERY for Query Id : ' || p_QUERY_ID, 'CSM_QUERY_PKG.VALIDATE_QUERY',
741: FND_LOG.LEVEL_EXCEPTION);
742: x_return_status := FND_API.G_RET_STS_ERROR;
743: x_error_message := 'Query Text is Empty ';
744: RETURN;
773: dbms_sql.close_cursor(cursor_name);
774: END IF;
775:
776: CSM_UTIL_PKG.LOG
777: ( 'Leaving VALIDATE_QUERY for Query Id : ' || p_QUERY_ID ,'CSM_QUERY_PKG.VALIDATE_QUERY',
778: FND_LOG.LEVEL_ERROR);
779:
780: x_return_status := FND_API.G_RET_STS_SUCCESS;
781: END IF;
783: EXCEPTION WHEN OTHERS THEN
784: /*** catch and log exceptions ***/
785: dbms_sql.close_cursor(cursor_name);
786: CSM_UTIL_PKG.LOG
787: ( 'Exception occurred in VALIDATE_QUERY for Query Id : ' || p_QUERY_ID || SQLERRM, 'CSM_QUERY_PKG.VALIDATE_QUERY',
788: FND_LOG.LEVEL_EXCEPTION);
789: x_return_status := FND_API.G_RET_STS_ERROR;
790: x_error_message := 'Query Validation Failed With Message : ' || SQLERRM ;
791: END VALIDATE_QUERY;
902: l_bind_count NUMBER;
903:
904: BEGIN
905: CSM_UTIL_PKG.LOG
906: ( 'Entering EXECUTE_QUERY for Query Id and Query Name : ' || p_QUERY_ID ,'CSM_QUERY_PKG.EXECUTE_QUERY',
907: FND_LOG.LEVEL_ERROR);
908: SAVEPOINT EXECUTE_QUERY;
909:
910: --Query Execution status update
912: SYSDATE,NULL,'RUNNING',NULL);
913: OPEN c_check_query(p_QUERY_ID);
914: FETCH c_check_query INTO l_QUERY_TEXT1, l_QUERY_TEXT2,l_QUERY_TYPE,l_WORK_FLOW, l_PROCEDURE_NAME;
915: IF c_check_query%NOTFOUND THEN
916: CSM_UTIL_PKG.LOG( 'Invalid Query Id : ' || p_QUERY_ID ,'CSM_QUERY_PKG.EXECUTE_QUERY',FND_LOG.LEVEL_ERROR);
917: x_return_status := FND_API.G_RET_STS_ERROR;
918: x_error_message := 'Invalid Query Id : ' || p_QUERY_ID ;
919: CLOSE c_check_query;
920: ROLLBACK TO EXECUTE_QUERY;
928:
929: OPEN c_check_instance(p_USER_ID, p_QUERY_ID, p_INSTANCE_ID);
930: FETCH c_check_instance INTO l_instance_id;
931: IF c_check_instance%NOTFOUND THEN
932: CSM_UTIL_PKG.LOG( 'The User : ' || p_USER_ID || ' does not have valid Instance ID : ' || p_INSTANCE_ID ,'CSM_QUERY_PKG.EXECUTE_QUERY',FND_LOG.LEVEL_ERROR);
933: x_return_status := FND_API.G_RET_STS_ERROR;
934: x_error_message := 'The User : ' || p_USER_ID || ' does not have valid Instance ID : ' || p_INSTANCE_ID ;
935: CLOSE c_check_instance;
936: ROLLBACK TO EXECUTE_QUERY;
1082:
1083: OPEN c_get_wf_root(l_item_type);
1084: FETCH c_get_wf_root INTO l_root_process;
1085: IF c_get_wf_root%NOTFOUND THEN
1086: CSM_UTIL_PKG.LOG( 'The Workflow : ' || l_item_type || ' does not have valid Root Process ' ,'CSM_QUERY_PKG.EXECUTE_QUERY',FND_LOG.LEVEL_ERROR);
1087: x_return_status := FND_API.G_RET_STS_ERROR;
1088: x_error_message := 'The Workflow : ' || l_item_type || ' does not have valid Root Process ' ;
1089: CLOSE c_get_wf_root;
1090: ROLLBACK TO EXECUTE_QUERY;
1151:
1152:
1153: CSM_UTIL_PKG.LOG
1154: ( 'Leaving EXECUTE_QUERY for Query Id and Instance Id after successfully Executing :
1155: ' || p_QUERY_ID || '-' || p_INSTANCE_ID ,'CSM_QUERY_PKG.EXECUTE_QUERY',
1156: FND_LOG.LEVEL_ERROR);
1157: x_return_status := FND_API.G_RET_STS_SUCCESS;
1158:
1159: EXCEPTION WHEN OTHERS THEN
1159: EXCEPTION WHEN OTHERS THEN
1160: /*** catch and log exceptions ***/
1161:
1162: CSM_UTIL_PKG.LOG
1163: ( 'Exception occurred in EXECUTE_QUERY for Instance id : ' || p_INSTANCE_ID || SUBSTR(SQLERRM,1,3000), 'CSM_QUERY_PKG.EXECUTE_QUERY',
1164: FND_LOG.LEVEL_EXCEPTION);
1165: x_return_status := FND_API.G_RET_STS_ERROR;
1166: x_error_message := 'Query Execution Failed With Message : ' || SUBSTR(SQLERRM,1,3000) ;
1167: IF FND_API.To_Boolean(p_commit) AND p_source_module ='MOBILEADMIN' THEN
1243: l_query_type VARCHAR2(255);
1244: l_VARIABLE_VALUE_CHAR VARCHAR2(4000);
1245: BEGIN
1246: CSM_UTIL_PKG.LOG
1247: ( 'Entering INSERT_INSTANCE for Query Id : ' || p_QUERY_ID ,'CSM_QUERY_PKG.INSERT_INSTANCE',
1248: FND_LOG.LEVEL_ERROR);
1249:
1250: OPEN c_check_query(p_QUERY_ID);
1251: FETCH c_check_query INTO l_instance_name, l_level_id, l_level_value,l_query_type;
1249:
1250: OPEN c_check_query(p_QUERY_ID);
1251: FETCH c_check_query INTO l_instance_name, l_level_id, l_level_value,l_query_type;
1252: IF c_check_query%NOTFOUND THEN
1253: CSM_UTIL_PKG.LOG( 'Invalid Query Id : ' || p_QUERY_ID ,'CSM_QUERY_PKG.INSERT_INSTANCE',FND_LOG.LEVEL_ERROR);
1254: x_return_status := FND_API.G_RET_STS_ERROR;
1255: x_error_message := 'Invalid Query Id : ' || p_QUERY_ID ;
1256: CLOSE c_check_query;
1257: RETURN;
1271: x_error_message => l_return_message);
1272:
1273: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1274: CSM_UTIL_PKG.LOG
1275: ( 'Exception occurred in VALIDATE_ACCESS for Query Name ' || l_instance_name || SQLERRM, 'CSM_QUERY_PKG.INSERT_INSTANCE',
1276: FND_LOG.LEVEL_EXCEPTION);
1277: x_return_status := FND_API.G_RET_STS_ERROR;
1278: x_error_message := 'Query Access Validation failed : ' || l_return_message;
1279:
1282: END IF;
1283: /*OPEN c_check_query_acc(p_QUERY_ID, p_USER_ID);
1284: FETCH c_check_query_acc INTO l_dummy_qry_id;
1285: IF c_check_query_acc%NOTFOUND THEN
1286: CSM_UTIL_PKG.LOG( 'User does not have access to the Query Id : ' || p_QUERY_ID ,'CSM_QUERY_PKG.INSERT_INSTANCE',FND_LOG.LEVEL_ERROR);
1287: x_return_status := FND_API.G_RET_STS_ERROR;
1288: x_error_message := 'User does not have access to the Query Id : ' || p_QUERY_ID ||
1289: '. Please run the Concurrent program and try again';
1290: CLOSE c_check_query_acc;
1334: IF FND_API.To_Boolean(p_commit) THEN
1335: COMMIT WORK;
1336: END IF;
1337: CSM_UTIL_PKG.LOG
1338: ( 'Leaving INSERT_INSTANCE for Query Id : ' || p_QUERY_ID ,'CSM_QUERY_PKG.INSERT_INSTANCE',
1339: FND_LOG.LEVEL_ERROR);
1340:
1341: x_return_status := FND_API.G_RET_STS_SUCCESS;
1342: x_error_message := 'Leaving INSERT_INSTANCE for Query Id : ' || p_QUERY_ID ;
1345: END IF;
1346:
1347: IF l_variable_id_lst.COUNT <> p_VARIABLE_ID.COUNT THEN
1348: CSM_UTIL_PKG.LOG
1349: ( 'Variable Count mismatch.Leaving INSERT_INSTANCE for Query Id : ' || p_QUERY_ID ,'CSM_QUERY_PKG.INSERT_INSTANCE',
1350: FND_LOG.LEVEL_ERROR);
1351: x_return_status := FND_API.G_RET_STS_ERROR;
1352: x_error_message := 'Leaving INSERT_INSTANCE for Query Id : ' || p_QUERY_ID ;
1353: ROLLBACK TO INSERT_INSTANCE;
1357: FOR i in 1..p_VARIABLE_ID.COUNT LOOP
1358: IF UPPER(l_var_type_lst(i)) = 'DATE' THEN
1359: IF p_VARIABLE_VALUE_DATE(i) IS NULL THEN
1360: CSM_UTIL_PKG.LOG
1361: ( 'Variable Value sent not matching with Type.Leaving INSERT_INSTANCE for Query Id : ' || p_QUERY_ID ,'CSM_QUERY_PKG.INSERT_INSTANCE',
1362: FND_LOG.LEVEL_ERROR);
1363: x_return_status := FND_API.G_RET_STS_ERROR;
1364: x_error_message := 'Variable Value sent not matching with Type.Leaving INSERT_INSTANCE for Query Id : ' || p_QUERY_ID ;
1365: ROLLBACK TO INSERT_INSTANCE;
1368: ELSE
1369:
1370: IF p_VARIABLE_VALUE_CHAR(i) IS NULL THEN
1371: CSM_UTIL_PKG.LOG
1372: ( 'Variable Value sent not matching with Type.Leaving INSERT_INSTANCE for Query Id : ' || p_QUERY_ID ,'CSM_QUERY_PKG.INSERT_INSTANCE',
1373: FND_LOG.LEVEL_ERROR);
1374: x_return_status := FND_API.G_RET_STS_ERROR;
1375: x_error_message := 'Variable Value sent not matching with Type.Leaving INSERT_INSTANCE for Query Id : ' || p_QUERY_ID ;
1376:
1412: END IF;
1413:
1414: x_INSTANCE_ID := l_instance_id;
1415: CSM_UTIL_PKG.LOG
1416: ( 'Leaving INSERT_INSTANCE for Query Id and Query Name : ' || p_QUERY_ID ,'CSM_QUERY_PKG.INSERT_INSTANCE',
1417: FND_LOG.LEVEL_ERROR);
1418: x_return_status := FND_API.G_RET_STS_SUCCESS;
1419: x_error_message := 'Insert Successful for Instance Id : ' || p_INSTANCE_ID ;
1420: EXCEPTION WHEN OTHERS THEN
1419: x_error_message := 'Insert Successful for Instance Id : ' || p_INSTANCE_ID ;
1420: EXCEPTION WHEN OTHERS THEN
1421: /*** catch and log exceptions ***/
1422: CSM_UTIL_PKG.LOG
1423: ( 'Exception occurred in INSERT_INSTANCE for Query Id : ' || p_QUERY_ID || SQLERRM,'CSM_QUERY_PKG.INSERT_INSTANCE',
1424: FND_LOG.LEVEL_EXCEPTION);
1425: x_return_status := FND_API.G_RET_STS_ERROR;
1426: x_error_message := 'Exception occurred in INSERT_INSTANCE With Message : ' || SUBSTR(SQLERRM,1,3000) ;
1427:
1477: AND OBJECT_TYPE IN('TABLE','VIEW','SYNONYM');
1478:
1479: BEGIN
1480: CSM_UTIL_PKG.LOG
1481: ( 'Entering VALIDATE_ACCESS for Query Id : ' || p_QUERY_ID ,'CSM_QUERY_PKG.VALIDATE_ACCESS',
1482: FND_LOG.LEVEL_ERROR);
1483: --Get profile Value
1484: l_MQ_SCHEMA := UPPER(CSM_PROFILE_PKG.Get_Mobile_Query_Schema(p_RESPONSIBILITY_ID));
1485:
1483: --Get profile Value
1484: l_MQ_SCHEMA := UPPER(CSM_PROFILE_PKG.Get_Mobile_Query_Schema(p_RESPONSIBILITY_ID));
1485:
1486: IF l_MQ_SCHEMA IS NULL THEN
1487: CSM_UTIL_PKG.LOG( 'Error in VALIDATE_ACCESS for Query Id : ' || p_QUERY_ID, 'CSM_QUERY_PKG.VALIDATE_ACCESS',
1488: FND_LOG.LEVEL_EXCEPTION);
1489: x_return_status := FND_API.G_RET_STS_ERROR;
1490: x_error_message := 'The Schema to Execute the Query is Null.Please set the profile CSM: Mobile Query Schema Name';
1491:
1502: CLOSE c_get_query_txt;
1503: END IF;
1504:
1505: IF l_QUERY_TEXT1 IS NULL AND l_QUERY_TEXT2 IS NULL THEN
1506: CSM_UTIL_PKG.LOG( 'Error in VALIDATE_ACCESS for Query Id : ' || p_QUERY_ID, 'CSM_QUERY_PKG.VALIDATE_ACCESS',
1507: FND_LOG.LEVEL_EXCEPTION);
1508: x_return_status := FND_API.G_RET_STS_ERROR;
1509: x_error_message := 'Query Text is Empty ';
1510: RETURN;
1522: EXIT WHEN l_found_from_num = 0;
1523: END LOOP;
1524:
1525: IF l_from_count-1 = 0 THEN
1526: CSM_UTIL_PKG.LOG( 'Error in VALIDATE_ACCESS for Query Id : ' || p_QUERY_ID, 'CSM_QUERY_PKG.VALIDATE_ACCESS',
1527: FND_LOG.LEVEL_EXCEPTION);
1528: x_return_status := FND_API.G_RET_STS_ERROR;
1529: x_error_message := 'Query Does not have any From Please check the Query ';
1530:
1571: FOR j in 1..l_TABLE_LIST.COUNT LOOP
1572: OPEN c_check_table_access (l_MQ_SCHEMA,UPPER(l_TABLE_LIST(j)));
1573: FETCH c_check_table_access INTO l_dummy;
1574: IF c_check_table_access%NOTFOUND THEN
1575: CSM_UTIL_PKG.LOG( 'Error in VALIDATE_ACCESS for Query Id : ' || p_QUERY_ID,'CSM_QUERY_PKG.VALIDATE_ACCESS',
1576: FND_LOG.LEVEL_EXCEPTION);
1577: x_return_status := FND_API.G_RET_STS_ERROR;
1578: x_error_message := 'The Object : ' || l_TABLE_LIST(j) || ' does not present in the Mobile Query Schema :' || l_MQ_SCHEMA || ' Please Check.' ;
1579: CLOSE c_check_table_access;
1582: CLOSE c_check_table_access;
1583: END LOOP;
1584:
1585: CSM_UTIL_PKG.LOG
1586: ( 'Query Validation Succeeded for Query Id : ' || p_QUERY_ID ,'CSM_QUERY_PKG.VALIDATE_ACCESS',
1587: FND_LOG.LEVEL_ERROR);
1588: x_return_status := FND_API.G_RET_STS_SUCCESS;
1589: x_error_message := 'ACCESS VALIDATION SUCCESSFUL FOR QUERY ID : ' || p_QUERY_ID;
1590: END IF;
1591:
1592: EXCEPTION WHEN OTHERS THEN
1593: /*** catch and log exceptions ***/
1594: CSM_UTIL_PKG.LOG
1595: ( 'Exception occurred in VALIDATE_ACCESS for Query Id : ' || p_QUERY_ID || SQLERRM, 'CSM_QUERY_PKG.VALIDATE_ACCESS',
1596: FND_LOG.LEVEL_EXCEPTION);
1597: x_return_status := FND_API.G_RET_STS_ERROR;
1598: x_error_message := 'Query Validation Failed With Message : ' || SQLERRM ;
1599:
1639: l_access_id NUMBER;
1640: l_mark_dirty BOOLEAN;
1641: BEGIN
1642: CSM_UTIL_PKG.LOG
1643: ( 'Entering DELETE_INSTANCE for Query Id : ' || p_QUERY_ID ,'CSM_QUERY_PKG.DELETE_INSTANCE',
1644: FND_LOG.LEVEL_ERROR);
1645:
1646: SAVEPOINT DELETE_INSTANCE;
1647:
1650: FETCH c_get_instance INTO l_access_id;
1651: CLOSE c_get_instance;
1652:
1653: IF l_access_id IS NULL THEN
1654: CSM_UTIL_PKG.LOG( 'Invalid Instance Id : ' || p_INSTANCE_ID ,'CSM_QUERY_PKG.DELETE_INSTANCE',FND_LOG.LEVEL_ERROR);
1655: x_return_status := FND_API.G_RET_STS_ERROR;
1656: x_error_message := 'Invalid Instance Id : ' || p_INSTANCE_ID ;
1657: ROLLBACK TO DELETE_INSTANCE;
1658: RETURN;
1711: COMMIT WORK;
1712: END IF;
1713:
1714: CSM_UTIL_PKG.LOG
1715: ( 'Leaving DELETE_INSTANCE for Query Id and Query Name : ' || p_QUERY_ID ,'CSM_QUERY_PKG.DELETE_INSTANCE',
1716: FND_LOG.LEVEL_ERROR);
1717: x_return_status := FND_API.G_RET_STS_SUCCESS;
1718: x_error_message := 'Delete call Successful for Instance Id : ' || p_INSTANCE_ID ;
1719:
1719:
1720: EXCEPTION WHEN OTHERS THEN
1721: /*** catch and log exceptions ***/
1722: CSM_UTIL_PKG.LOG
1723: ( 'Exception occurred in DELETE_INSTANCE for Query Id : ' || p_QUERY_ID || SQLERRM, 'CSM_QUERY_PKG.DELETE_INSTANCE',
1724: FND_LOG.LEVEL_EXCEPTION);
1725: x_return_status := FND_API.G_RET_STS_ERROR;
1726: x_error_message := 'Exception occurred in DELETE_INSTANCE With Message : ' || SUBSTR(SQLERRM,1,3000) ;
1727: ROLLBACK TO DELETE_INSTANCE;
1762: AND name = c_variable_name;
1763:
1764: BEGIN
1765: CSM_UTIL_PKG.LOG
1766: ( 'Entering VALIDATE_WORKFLOW for Query Id : ' || p_QUERY_ID ,'CSM_QUERY_PKG.VALIDATE_WORKFLOW',
1767: FND_LOG.LEVEL_ERROR);
1768:
1769:
1770: l_WORKFLOW := p_WORKFLOW;
1776: END IF;
1777:
1778:
1779: IF l_WORKFLOW IS NULL THEN
1780: CSM_UTIL_PKG.LOG( 'Error in VALIDATE_WORKFLOW for Query Id : ' || p_QUERY_ID, 'CSM_QUERY_PKG.VALIDATE_WORKFLOW',
1781: FND_LOG.LEVEL_EXCEPTION);
1782: x_return_status := FND_API.G_RET_STS_ERROR;
1783: x_error_message := 'Workflow Name is Empty ';
1784: RETURN;
1791: FETCH c_get_item_type INTO l_TEMP_WORKFLOW;
1792: CLOSE c_get_item_type;
1793:
1794: IF l_TEMP_WORKFLOW IS NULL THEN
1795: CSM_UTIL_PKG.LOG( 'The Given Work Flow for Query Id : ' || p_QUERY_ID || ' is not Valid.', 'CSM_QUERY_PKG.VALIDATE_WORKFLOW',
1796: FND_LOG.LEVEL_EXCEPTION);
1797: x_return_status := FND_API.G_RET_STS_ERROR;
1798: x_error_message := 'The given Workflow Name is not Valid. ';
1799: RETURN;
1807: FETCH c_get_item_variable INTO l_TEMP_VARIABLE;
1808: CLOSE c_get_item_variable;
1809:
1810: IF l_TEMP_VARIABLE IS NULL THEN
1811: CSM_UTIL_PKG.LOG( 'The Variable : ' || p_VARIABLE_NAME(i) || ' is not Valid.', 'CSM_QUERY_PKG.VALIDATE_WORKFLOW',
1812: FND_LOG.LEVEL_EXCEPTION);
1813: x_return_status := FND_API.G_RET_STS_ERROR;
1814: x_error_message := 'The Variable Name is not Valid : '|| p_VARIABLE_NAME(i);
1815: RETURN;
1820: END IF;
1821:
1822:
1823: CSM_UTIL_PKG.LOG
1824: ( 'Leaving VALIDATE_WORKFLOW for Query Id : ' || p_QUERY_ID ,'CSM_QUERY_PKG.CSM_QUERY_PKG.VALIDATE_WORKFLOW',
1825: FND_LOG.LEVEL_ERROR);
1826: x_return_status := FND_API.G_RET_STS_SUCCESS;
1827:
1828: EXCEPTION WHEN OTHERS THEN
1827:
1828: EXCEPTION WHEN OTHERS THEN
1829: /*** catch and log exceptions ***/
1830: CSM_UTIL_PKG.LOG
1831: ( 'Exception occurred in VALIDATE_WORKFLOW for Query Id : ' || p_QUERY_ID || SQLERRM, 'CSM_QUERY_PKG.VALIDATE_WORKFLOW',
1832: FND_LOG.LEVEL_EXCEPTION);
1833: x_return_status := FND_API.G_RET_STS_ERROR;
1834: x_error_message := 'Query Validation Failed With Message : ' || SQLERRM ;
1835: END VALIDATE_WORKFLOW;
1865: AND status = 'VALID';
1866:
1867: BEGIN
1868: CSM_UTIL_PKG.LOG
1869: ( 'Entering VALIDATE_PROCEDURE for Query Id : ' || p_QUERY_ID ,'CSM_QUERY_PKG.VALIDATE_PROCEDURE',
1870: FND_LOG.LEVEL_ERROR);
1871: l_PROCEDURE := p_PROCEDURE;
1872: --if the Work Flow is empty get the query text using the query id from the table
1873: IF l_PROCEDURE IS NULL THEN
1877: END IF;
1878:
1879:
1880: IF l_PROCEDURE IS NULL THEN
1881: CSM_UTIL_PKG.LOG( 'Error in VALIDATE_PROCEDURE for Query Id : ' || p_QUERY_ID, 'CSM_QUERY_PKG.VALIDATE_PROCEDURE',
1882: FND_LOG.LEVEL_EXCEPTION);
1883: x_return_status := FND_API.G_RET_STS_ERROR;
1884: x_error_message := 'Procedure Name is Empty ';
1885: RETURN;
1895: OPEN c_is_package_valid(l_PACKAGE_NAME, l_SCHEMA_NAME);
1896: FETCH c_is_package_valid INTO l_PACKAGE_NAME;
1897:
1898: IF c_is_package_valid%NOTFOUND THEN
1899: CSM_UTIL_PKG.LOG( 'The Given Package : ' || l_PACKAGE_NAME || ' is not Valid.', 'CSM_QUERY_PKG.VALIDATE_PROCEDURE',
1900: FND_LOG.LEVEL_EXCEPTION);
1901: x_return_status := FND_API.G_RET_STS_ERROR;
1902: x_error_message := 'The Given Package for Query Id : ' || p_QUERY_ID || ' is not Valid.';
1903: RETURN;
1906: CLOSE c_is_package_valid;
1907: END IF;
1908:
1909: CSM_UTIL_PKG.LOG
1910: ( 'Leaving VALIDATE_PROCEDURE for Query Id : ' || p_QUERY_ID ,'CSM_QUERY_PKG.VALIDATE_PROCEDURE',
1911: FND_LOG.LEVEL_ERROR);
1912: x_return_status := FND_API.G_RET_STS_SUCCESS;
1913: EXCEPTION WHEN OTHERS THEN
1914: /*** catch and log exceptions ***/
1912: x_return_status := FND_API.G_RET_STS_SUCCESS;
1913: EXCEPTION WHEN OTHERS THEN
1914: /*** catch and log exceptions ***/
1915: CSM_UTIL_PKG.LOG
1916: ( 'Exception occurred in VALIDATE_PROCEDURE for Query Id : ' || p_QUERY_ID || SQLERRM, 'CSM_QUERY_PKG.VALIDATE_PROCEDURE',
1917: FND_LOG.LEVEL_EXCEPTION);
1918: x_return_status := FND_API.G_RET_STS_ERROR;
1919: x_error_message := 'Query Validation Failed With Message : ' || SQLERRM ;
1920: END VALIDATE_PROCEDURE;
1947: l_error_msg VARCHAR2(4000);
1948:
1949:
1950: BEGIN
1951: CSM_UTIL_PKG.LOG('Entering CSM_QUERY_PKG.INSERT_RESULT for Instance ID: ' || p_INSTANCE_ID,
1952: 'CSM_QUERY_PKG.INSERT_RESULT',FND_LOG.LEVEL_PROCEDURE);
1953:
1954: OPEN c_check_results(p_USER_ID, p_QUERY_ID, p_INSTANCE_ID);
1955: FETCH c_check_results INTO l_rs_access_id;
1948:
1949:
1950: BEGIN
1951: CSM_UTIL_PKG.LOG('Entering CSM_QUERY_PKG.INSERT_RESULT for Instance ID: ' || p_INSTANCE_ID,
1952: 'CSM_QUERY_PKG.INSERT_RESULT',FND_LOG.LEVEL_PROCEDURE);
1953:
1954: OPEN c_check_results(p_USER_ID, p_QUERY_ID, p_INSTANCE_ID);
1955: FETCH c_check_results INTO l_rs_access_id;
1956: CLOSE c_check_results;
2015: NULL,SYSDATE,'EXECUTED','Query Successfully Executed');
2016: IF FND_API.To_Boolean(p_commit) THEN
2017: COMMIT WORK;
2018: END IF;
2019: CSM_UTIL_PKG.LOG('Leaving CSM_QUERY_PKG.INSERT_RESULT for Instance ID : ' || p_INSTANCE_ID,
2020: 'CSM_QUERY_PKG.INSERT_RESULT',FND_LOG.LEVEL_EXCEPTION);
2021:
2022: x_return_status := FND_API.G_RET_STS_SUCCESS;
2023: x_error_message := 'Query Result Successfully Inserted into the Access Table';
2016: IF FND_API.To_Boolean(p_commit) THEN
2017: COMMIT WORK;
2018: END IF;
2019: CSM_UTIL_PKG.LOG('Leaving CSM_QUERY_PKG.INSERT_RESULT for Instance ID : ' || p_INSTANCE_ID,
2020: 'CSM_QUERY_PKG.INSERT_RESULT',FND_LOG.LEVEL_EXCEPTION);
2021:
2022: x_return_status := FND_API.G_RET_STS_SUCCESS;
2023: x_error_message := 'Query Result Successfully Inserted into the Access Table';
2024:
2027: l_sqlerrno := to_char(SQLCODE);
2028: l_sqlerrmsg := substr(SQLERRM, 1,2000);
2029: x_return_status := FND_API.G_RET_STS_ERROR;
2030: x_error_message := l_error_msg;
2031: l_error_msg := ' Exception in CSM_QUERY_PKG for Instance ID :' || to_char(p_INSTANCE_ID)
2032: || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
2033: CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_QUERY_PKG.INSERT_RESULT',FND_LOG.LEVEL_EXCEPTION);
2034: ROLLBACK TO INSERT_RESULT;
2035: END INSERT_RESULT;
2029: x_return_status := FND_API.G_RET_STS_ERROR;
2030: x_error_message := l_error_msg;
2031: l_error_msg := ' Exception in CSM_QUERY_PKG for Instance ID :' || to_char(p_INSTANCE_ID)
2032: || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
2033: CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_QUERY_PKG.INSERT_RESULT',FND_LOG.LEVEL_EXCEPTION);
2034: ROLLBACK TO INSERT_RESULT;
2035: END INSERT_RESULT;
2036:
2037: PROCEDURE SET_VALIDATE_PROCEDURE(p_validate_procedure IN VARCHAR2)
2042: END IF;
2043:
2044: END SET_VALIDATE_PROCEDURE;
2045:
2046: END CSM_QUERY_PKG; -- Package spec
2047: