[Home] [Help]
PACKAGE BODY: APPS.CSM_QUERY_PKG
Source
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
6 */
7 -- Purpose: Store the Query Definition done in the Mobile Admin page
8 --
9 -- MODIFICATION HISTORY
10 -- Person Date Comments
11 -- TRAJASEK 11-APR-2009 Created
12 --
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';
22 g_pub_item_qvar VARCHAR2(50) := 'CSM_QUERY_VARIABLES';
23 g_pub_item_qval VARCHAR2(50) := 'CSM_QUERY_VARIABLE_VALUES';
24 g_pub_item_qins VARCHAR2(50) := 'CSM_QUERY_INSTANCES';
25 g_validate_procedure VARCHAR2(1) := 'Y';
26
27 --Procedure to UPDATE the status of the Query Execution
28 PROCEDURE UPDATE_EXE_STATUS
29 ( p_USER_ID IN NUMBER,
30 p_QUERY_ID IN NUMBER,
31 p_INSTANCE_ID IN NUMBER,
32 p_QSTART_TIME IN DATE,
33 p_QEND_TIME IN DATE,
34 p_STATUS IN VARCHAR2,
35 p_ERROR IN VARCHAR2
36 )
37 IS
38 l_access_id NUMBER;
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) ,
48 QUERY_END_TIME = NVL(p_QEND_TIME,QUERY_END_TIME) ,
49 STATUS = NVL(p_STATUS,STATUS),
50 ERROR_DESCRIPTION= NVL(p_ERROR,ERROR_DESCRIPTION)
51 WHERE USER_ID = p_USER_ID
52 AND QUERY_ID = p_QUERY_ID
53 AND INSTANCE_ID = p_INSTANCE_ID
54 RETURNING ACCESS_ID INTO l_access_id;
55
56 IF csm_util_pkg.is_palm_user(p_USER_ID) AND l_access_id IS NOT NULL THEN
57
58 l_mark_dirty := asg_Download.mark_dirty (p_pub_item => g_pub_item_qins,
59 p_accessid => l_access_id,
60 p_userid => p_USER_ID,
61 p_dml => asg_download.upd,
62 p_timestamp => sysdate);
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 ***/
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
79 /***
80 This procedure is called by ASG team to insert a Query
81 That is created in the Mobile Admin Page
82 ***/
83 PROCEDURE INSERT_QUERY
84 ( p_QUERY_ID IN NUMBER,
85 p_QUERY_NAME IN VARCHAR2,
86 P_QUERY_DESC IN VARCHAR2,
87 P_QUERY_TYPE IN VARCHAR2,
88 p_QUERY_TEXT1 IN VARCHAR2,
89 p_QUERY_TEXT2 IN VARCHAR2,
90 p_LEVEL_ID IN NUMBER,
91 p_LEVEL_VALUE IN NUMBER,
92 p_PARENT_QUERY_ID IN NUMBER,
93 p_SAVED_QUERY IN VARCHAR2,
94 p_QUERY_OUTPUT_FORMAT IN VARCHAR2,
95 p_MIME_TYPE IN VARCHAR2,
96 p_WORK_FLOW IN VARCHAR2,
97 p_PROCEDURE IN VARCHAR2,
98 p_RETENTION_POLICY IN VARCHAR2,
99 p_RETENTION_DAYS IN NUMBER,
100 p_TEMPLATE IN VARCHAR2,
101 p_TEMPLATE_FILE IN VARCHAR2,
102 p_EXECUTION_MODE IN VARCHAR2,
103 p_VARIABLE_NAME IN CSM_VARCHAR_LIST,
104 p_VARIABLE_TYPE IN CSM_VARCHAR_LIST,
105 p_VARIABLE_VALUE_CHAR IN CSM_VARCHAR_LIST,
106 p_VARIABLE_VALUE_DATE IN CSM_DATE_LIST,
107 p_HIDDEN_FLAG IN CSM_VARCHAR_LIST,
108 p_DEFAULT_FLAG IN CSM_VARCHAR_LIST,
109 p_EMAIL_ENABLED IN VARCHAR2,
110 p_RESTRICTED_FLAG IN VARCHAR2,
111 p_DISABLED_FLAG IN VARCHAR2,
112 x_return_status OUT NOCOPY VARCHAR2,
113 x_error_message OUT NOCOPY VARCHAR2
114 )
115 AS
116 CURSOR c_get_query_id --get query_id
117 IS
118 SELECT CSM_QUERY_B_S.NEXTVAL
119 FROM DUAL;
120
121 l_query_id NUMBER;
122 l_variable_count NUMBER;
123 l_return_status VARCHAR2(4000);
124 l_return_message VARCHAR2(4000);
125 l_language VARCHAR2(10) := 'US' ;
126 l_qry_with_no_var VARCHAR2(1) := 'N';
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;
136 END IF;
137 --Do the Validation only if query type is SQL
138 IF UPPER(P_QUERY_TYPE) ='SQL' THEN
139 --Validate the Access to a Query
140 VALIDATE_ACCESS( p_QUERY_ID => NULL,
141 p_QUERY_TEXT1 => p_QUERY_TEXT1,
142 p_QUERY_TEXT2 => p_QUERY_TEXT2,
143 p_RESPONSIBILITY_ID => l_responsibility_id,
144 x_return_status => l_return_status,
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;
154 END IF;
155 --Validate the Query
156 VALIDATE_QUERY( p_QUERY_ID => NULL,
157 p_QUERY_TEXT1 => p_QUERY_TEXT1,
158 p_QUERY_TEXT2 => p_QUERY_TEXT2,
159 x_return_status => l_return_status,
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;
169 END IF;
170 ELSIF UPPER(P_QUERY_TYPE) ='WORKFLOW' THEN
171 VALIDATE_WORKFLOW
172 ( p_QUERY_ID => p_QUERY_ID,
173 p_WORKFLOW => p_WORK_FLOW,
174 p_VARIABLE_NAME => p_VARIABLE_NAME,
175 x_return_status => l_return_status,
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;
185 END IF;
186
187 ELSIF UPPER(P_QUERY_TYPE) = 'PROCEDURE' AND g_validate_procedure = 'Y' THEN
188
189 VALIDATE_PROCEDURE
190 ( p_QUERY_ID => p_QUERY_ID,
191 p_PROCEDURE => p_PROCEDURE,
192 x_return_status => l_return_status,
193 x_error_message => l_return_message
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;
203 END IF;
204 END IF;
205
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 ||
215 ' because of Variable Name and Variable Type Mismatch' ;
216 RETURN;
217 END IF;
218 ELSE
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 ||
228 ' because of Variable Name and Variable Type Mismatch' ;
229 RETURN;
230 END IF;
231
232 END IF;
233
234 --Get the query id from the sequence.
235 OPEN c_get_query_id;
236 FETCH c_get_query_id INTO l_query_id;
237 CLOSE c_get_query_id;
238
239 --Insert the Basic Query Definition into the base table
240 INSERT INTO CSM_QUERY_B ( QUERY_ID, QUERY_NAME, QUERY_TYPE,
241 QUERY_TEXT1, QUERY_TEXT2, LEVEL_ID,
242 LEVEL_VALUE, PARENT_QUERY_ID, SAVED_QUERY,
243 QUERY_OUTPUT_FORMAT, MIME_TYPE, WORK_FLOW,
244 RETENTION_POLICY, RETENTION_DAYS, TEMPLATE,
245 TEMPLATE_FILE, EXECUTION_MODE, CREATION_DATE,
246 CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY,
247 LAST_UPDATE_LOGIN, SEEDED, PROCEDURE_NAME,
248 EMAIL_ENABLED, RESTRICTED_FLAG, DISABLED_FLAG )
249 VALUES ( l_query_id, p_QUERY_NAME, p_QUERY_TYPE,
250 p_QUERY_TEXT1, p_QUERY_TEXT2, p_LEVEL_ID,
251 p_LEVEL_VALUE, p_PARENT_QUERY_ID, p_SAVED_QUERY,
252 p_QUERY_OUTPUT_FORMAT, p_MIME_TYPE, p_WORK_FLOW,
253 p_RETENTION_POLICY, p_RETENTION_DAYS, p_TEMPLATE,
254 p_TEMPLATE_FILE, p_EXECUTION_MODE, sysdate,
255 fnd_global.user_id, sysdate, fnd_global.user_id,
256 fnd_global.login_id, 'N', p_PROCEDURE,
257 p_EMAIL_ENABLED, p_RESTRICTED_FLAG, p_DISABLED_FLAG);
258
259 --Insert the QUERY DESCRIPTION into the TL table
260 INSERT INTO CSM_QUERY_TL ( QUERY_ID, DESCRIPTION, LANGUAGE,
261 CREATION_DATE, CREATED_BY,
262 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN )
263 VALUES ( l_query_id, P_QUERY_DESC, l_language,
264 sysdate, fnd_global.user_id,
265 sysdate, fnd_global.user_id, fnd_global.login_id);
266
267 IF l_qry_with_no_var = 'N' THEN --Proces only if the query has variables
268 l_variable_count := p_VARIABLE_NAME.COUNT;
269
270 FOR i in 1..l_variable_count LOOP
271 --Insert the Query Variable Definition into the base table
272 l_VARIABLE_VALUE_CHAR := SUBSTR(p_VARIABLE_VALUE_CHAR(i),1, 4000);
273 INSERT INTO CSM_QUERY_VARIABLES_B ( QUERY_ID, VARIABLE_ID, VARIABLE_NAME,
274 VARIABLE_TYPE, VARIABLE_VALUE_CHAR, VARIABLE_VALUE_DATE,
275 HIDDEN, DEFAULT_FLAG, CREATION_DATE,
276 CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY,
277 LAST_UPDATE_LOGIN )
278 VALUES ( l_query_id, i, p_VARIABLE_NAME(i),
279 p_VARIABLE_TYPE(i), l_VARIABLE_VALUE_CHAR, p_VARIABLE_VALUE_DATE(i),
280 NVL(p_HIDDEN_FLAG(i),'N'), NVL(p_DEFAULT_FLAG(i),'N'), sysdate,
281 fnd_global.user_id, sysdate, fnd_global.user_id,
282 fnd_global.login_id );
283
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
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);
301 ROLLBACK;
302 END INSERT_QUERY;
303
304
305 /***
306 This procedure is called by ASG team to Update a Query
307 that is created in the Mobile Admin Page
308 ***/
309 PROCEDURE UPDATE_QUERY
310 ( p_QUERY_ID IN NUMBER,
311 p_QUERY_NAME IN VARCHAR2,
312 P_QUERY_DESC IN VARCHAR2,
313 P_QUERY_TYPE IN VARCHAR2,
314 p_QUERY_TEXT1 IN VARCHAR2,
315 p_QUERY_TEXT2 IN VARCHAR2,
316 p_LEVEL_ID IN NUMBER,
317 p_LEVEL_VALUE IN NUMBER,
318 p_PARENT_QUERY_ID IN NUMBER,
319 p_SAVED_QUERY IN VARCHAR2,
320 p_QUERY_OUTPUT_FORMAT IN VARCHAR2,
321 p_MIME_TYPE IN VARCHAR2,
322 p_WORK_FLOW IN VARCHAR2,
323 p_PROCEDURE IN VARCHAR2,
324 p_RETENTION_POLICY IN VARCHAR2,
325 p_RETENTION_DAYS IN NUMBER,
326 p_TEMPLATE IN VARCHAR2,
327 p_TEMPLATE_FILE IN VARCHAR2,
328 p_EXECUTION_MODE IN VARCHAR2,
329 p_VARIABLE_NAME IN CSM_VARCHAR_LIST,
330 p_VARIABLE_TYPE IN CSM_VARCHAR_LIST,
331 p_VARIABLE_VALUE_CHAR IN CSM_VARCHAR_LIST,
332 p_VARIABLE_VALUE_DATE IN CSM_DATE_LIST,
333 p_HIDDEN_FLAG IN CSM_VARCHAR_LIST,
334 p_DEFAULT_FLAG IN CSM_VARCHAR_LIST,
335 p_EMAIL_ENABLED IN VARCHAR2,
336 p_RESTRICTED_FLAG IN VARCHAR2,
337 p_DISABLED_FLAG IN VARCHAR2,
338 x_return_status OUT NOCOPY VARCHAR2,
339 x_error_message OUT NOCOPY VARCHAR2
340 )
341 AS
342 CURSOR c_get_existing_var_count(c_query_id NUMBER) --get the count of existing variables
343 IS
344 SELECT count(*)
345 FROM CSM_QUERY_VARIABLES_B
346 WHERE QUERY_ID =c_query_id ;
347
348 CURSOR c_get_query(c_query_id NUMBER) --get the existing query data
349 IS
350 SELECT LEVEL_VALUE
351 FROM CSM_QUERY_B
352 WHERE QUERY_ID = c_query_id ;
353
354
355 l_Existing_variable_count NUMBER;
356 l_variable_count NUMBER;
357 l_language VARCHAR2(10) := 'US' ;
358 l_old_LEVEL_VALUE NUMBER;
359 l_return_status VARCHAR2(100);
360 l_return_message VARCHAR2(4000);
361 l_qry_with_no_var VARCHAR2(1) := 'N';
362 l_responsibility_id NUMBER := NULL;
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;
372 END IF;
373 --Do the Validation only if query type is SQL
374 IF UPPER(P_QUERY_TYPE) ='SQL' THEN
375 --Validate the Access to a Query
376 VALIDATE_ACCESS( p_QUERY_ID => NULL,
377 p_QUERY_TEXT1 => p_QUERY_TEXT1,
378 p_QUERY_TEXT2 => p_QUERY_TEXT2,
379 p_RESPONSIBILITY_ID => l_responsibility_id,
380 x_return_status => l_return_status,
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;
390 END IF;
391
392 --Validate the Query
393 VALIDATE_QUERY( p_QUERY_ID => NULL,
394 p_QUERY_TEXT1 => p_QUERY_TEXT1,
395 p_QUERY_TEXT2 => p_QUERY_TEXT2,
396 x_return_status => l_return_status,
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;
406 END IF;
407 ELSIF UPPER(P_QUERY_TYPE) ='WORKFLOW' THEN
408 VALIDATE_WORKFLOW
409 ( p_QUERY_ID => p_QUERY_ID,
410 p_WORKFLOW => p_WORK_FLOW,
411 p_VARIABLE_NAME => p_VARIABLE_NAME,
412 x_return_status => l_return_status,
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;
422 END IF;
423
424 ELSIF UPPER(P_QUERY_TYPE) ='PROCEDURE' AND g_validate_procedure = 'Y' THEN
425 VALIDATE_PROCEDURE
426 ( p_QUERY_ID => p_QUERY_ID,
427 p_PROCEDURE => p_PROCEDURE,
428 x_return_status => l_return_status,
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;
438 END IF;
439 END IF;
440
441 OPEN c_get_query(p_QUERY_ID);
442 FETCH c_get_query INTO l_old_LEVEL_VALUE;
443 CLOSE c_get_query;
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,
453 x_return_status => l_return_status,
454 x_error_message => l_return_message
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;
464 ELSE --Insert the query again for the new responsibility
465 INSERT_QUERY
466 ( p_QUERY_ID => NULL,
467 p_QUERY_NAME => p_QUERY_NAME,
468 P_QUERY_DESC => P_QUERY_DESC,
469 P_QUERY_TYPE => P_QUERY_TYPE,
470 p_QUERY_TEXT1 => p_QUERY_TEXT1,
471 p_QUERY_TEXT2 => p_QUERY_TEXT2,
472 p_LEVEL_ID => p_LEVEL_ID,
473 p_LEVEL_VALUE => p_LEVEL_VALUE,
474 p_PARENT_QUERY_ID => p_PARENT_QUERY_ID,
475 p_SAVED_QUERY => p_SAVED_QUERY,
476 p_QUERY_OUTPUT_FORMAT => p_QUERY_OUTPUT_FORMAT,
477 p_MIME_TYPE => p_MIME_TYPE,
478 p_WORK_FLOW => p_WORK_FLOW,
479 p_PROCEDURE => p_PROCEDURE,
480 p_RETENTION_POLICY => p_RETENTION_POLICY,
481 p_RETENTION_DAYS => p_RETENTION_DAYS,
482 p_TEMPLATE => p_TEMPLATE,
483 p_TEMPLATE_FILE => p_TEMPLATE_FILE,
484 p_EXECUTION_MODE => p_EXECUTION_MODE,
485 p_VARIABLE_NAME => p_VARIABLE_NAME,
486 p_VARIABLE_TYPE => p_VARIABLE_TYPE,
487 p_VARIABLE_VALUE_CHAR => p_VARIABLE_VALUE_CHAR,
488 p_VARIABLE_VALUE_DATE => p_VARIABLE_VALUE_DATE,
489 p_HIDDEN_FLAG => p_HIDDEN_FLAG,
490 p_DEFAULT_FLAG => p_DEFAULT_FLAG,
491 p_EMAIL_ENABLED => p_EMAIL_ENABLED,
492 p_RESTRICTED_FLAG => p_RESTRICTED_FLAG,
493 p_DISABLED_FLAG => p_DISABLED_FLAG,
494 x_return_status => l_return_status,
495 x_error_message => l_return_message
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;
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;
513 END IF;
514
515 END IF;
516 END IF; --level value check
517
518 --Update the Query Definition in the base table
519 UPDATE CSM_QUERY_B
520 SET QUERY_NAME = p_QUERY_NAME, QUERY_TYPE = p_QUERY_TYPE,
521 QUERY_TEXT1 = p_QUERY_TEXT1, QUERY_TEXT2 = p_QUERY_TEXT2,
522 LEVEL_ID = p_LEVEL_ID, LEVEL_VALUE = p_LEVEL_VALUE,
523 PARENT_QUERY_ID = p_PARENT_QUERY_ID, SAVED_QUERY = p_SAVED_QUERY,
524 QUERY_OUTPUT_FORMAT = p_QUERY_OUTPUT_FORMAT, MIME_TYPE = p_MIME_TYPE,
525 WORK_FLOW = p_WORK_FLOW, RETENTION_DAYS = p_RETENTION_DAYS,
526 RETENTION_POLICY = p_RETENTION_POLICY, TEMPLATE = p_TEMPLATE,
527 TEMPLATE_FILE = p_TEMPLATE_FILE, EXECUTION_MODE= p_EXECUTION_MODE,
528 LAST_UPDATE_DATE = sysdate, LAST_UPDATED_BY = fnd_global.user_id,
529 LAST_UPDATE_LOGIN = fnd_global.login_id,
530 PROCEDURE_NAME = p_PROCEDURE,
531 EMAIL_ENABLED = p_EMAIL_ENABLED,
532 RESTRICTED_FLAG = p_RESTRICTED_FLAG,
533 DISABLED_FLAG = p_DISABLED_FLAG
534 WHERE QUERY_ID = p_QUERY_ID;
535
536 UPDATE CSM_QUERY_TL
537 SET DESCRIPTION = P_QUERY_DESC,
538 LAST_UPDATE_DATE = sysdate,
539 LAST_UPDATED_BY = fnd_global.user_id,
540 LAST_UPDATE_LOGIN = fnd_global.login_id
541 WHERE QUERY_ID = p_QUERY_ID
542 AND LANGUAGE = l_language;
543
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 || ' : ' ||
553 p_QUERY_NAME || ' because of Variable Name and Variable Type Mismatch';
554 RETURN;
555 END IF;
556 ELSE
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 || ' : ' ||
566 p_QUERY_NAME || ' because of Variable Name and Variable Type Mismatch';
567 RETURN;
568 END IF;
569
570 END IF;
571
572 OPEN c_get_existing_var_count(p_QUERY_ID);
573 FETCH c_get_existing_var_count INTO l_Existing_variable_count;
574 CLOSE c_get_existing_var_count;
575
576 --the check are done to avoid error during uninitialized Table types
577 IF l_qry_with_no_var = 'N' THEN
578 l_variable_count := p_VARIABLE_NAME.COUNT;
579 ELSE
580 l_variable_count := 0;
581 END IF;
582
583
584 --If the variable count has increased or Decreased then do
585 IF l_Existing_variable_count <> l_variable_count THEN
586
587 IF l_Existing_variable_count > 0 THEN
588 --Delete all the existing variables and insert again
589 DELETE FROM CSM_QUERY_VARIABLES_B WHERE QUERY_ID =p_QUERY_ID;
590 END IF;
591 FOR i in 1..l_variable_count LOOP
592 --Insert the Query Variable Definition into the base table
593 l_VARIABLE_VALUE_CHAR := SUBSTR(p_VARIABLE_VALUE_CHAR(i),1, 4000);
594 INSERT INTO CSM_QUERY_VARIABLES_B ( QUERY_ID, VARIABLE_ID, VARIABLE_NAME,
595 VARIABLE_TYPE, VARIABLE_VALUE_CHAR, VARIABLE_VALUE_DATE,
596 HIDDEN, DEFAULT_FLAG, CREATION_DATE,
597 CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY,
598 LAST_UPDATE_LOGIN )
599 VALUES ( p_QUERY_ID, i, p_VARIABLE_NAME(i),
600 p_VARIABLE_TYPE(i), l_VARIABLE_VALUE_CHAR, p_VARIABLE_VALUE_DATE(i),
601 NVL(p_HIDDEN_FLAG(i),'N'), NVL(p_DEFAULT_FLAG(i),'N'), sysdate,
602 fnd_global.user_id, sysdate, fnd_global.user_id,
603 fnd_global.login_id );
604
605
606 END LOOP;
607
608 ELSE
609 --Update the Existing Variable as they are changed
610 FOR i in 1..l_variable_count LOOP
611 --Insert the Query Variable Definition into the base table
612 l_VARIABLE_VALUE_CHAR := SUBSTR(p_VARIABLE_VALUE_CHAR(i),1, 4000);
613 UPDATE CSM_QUERY_VARIABLES_B
614 SET VARIABLE_NAME = p_VARIABLE_NAME(i),
615 VARIABLE_TYPE = p_VARIABLE_TYPE(i),
616 VARIABLE_VALUE_CHAR = l_VARIABLE_VALUE_CHAR,
617 VARIABLE_VALUE_DATE = p_VARIABLE_VALUE_DATE(i),
618 HIDDEN = NVL(p_HIDDEN_FLAG(i),'N'),
619 DEFAULT_FLAG = NVL(p_DEFAULT_FLAG(i),'N'),
620 LAST_UPDATE_DATE = sysdate,
621 LAST_UPDATED_BY = fnd_global.user_id,
622 LAST_UPDATE_LOGIN = fnd_global.login_id
623 WHERE QUERY_ID =p_QUERY_ID
624 AND VARIABLE_ID = i ;
625
626 END LOOP;
627
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 ***/
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);
643 ROLLBACK;
644 END UPDATE_QUERY;
645
646 /***
647 This procedure is called by ASG team to Delete a Query
648 That is created in the Mobile Admin Page
649 ***/
650 PROCEDURE DELETE_QUERY
651 ( p_QUERY_ID IN NUMBER,
652 x_return_status OUT NOCOPY VARCHAR2,
653 x_error_message OUT NOCOPY VARCHAR2
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
663 SET DELETE_FLAG = 'Y',
664 LAST_UPDATE_DATE = sysdate,
665 LAST_UPDATED_BY = fnd_global.user_id,
666 LAST_UPDATE_LOGIN = fnd_global.login_id
667 WHERE QUERY_ID = p_QUERY_ID;
668
669 --Update the Query Definition in the base table for parent queries
670 UPDATE CSM_QUERY_B
671 SET DELETE_FLAG = 'Y',
672 LAST_UPDATE_DATE = sysdate,
673 LAST_UPDATED_BY = fnd_global.user_id,
674 LAST_UPDATE_LOGIN = fnd_global.login_id
675 WHERE PARENT_QUERY_ID = p_QUERY_ID;
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 ***/
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;
691
692 /***
693 This procedure can be used to validate a given query
694 ***/
695 PROCEDURE VALIDATE_QUERY
696 ( p_QUERY_ID IN NUMBER DEFAULT NULL,
697 p_QUERY_TEXT1 IN VARCHAR2 DEFAULT NULL,
698 p_QUERY_TEXT2 IN VARCHAR2 DEFAULT NULL,
699 x_return_status OUT NOCOPY VARCHAR2,
700 x_error_message OUT NOCOPY VARCHAR2
701 )
702 AS
703 cursor_name INTEGER;
704 --TYPE VARCHAR2S IS TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER;
705 l_query_lst dbms_sql.varchar2s;
706 l_qry_length1 NUMBER;
707 l_qry_length2 NUMBER;
708 l_qry_count1 NUMBER;
709 l_qry_count2 NUMBER;
710 l_qry_tot_count NUMBER;
711 l_QUERY_TEXT1 VARCHAR2(4000);
712 l_QUERY_TEXT2 VARCHAR2(4000);
713
714 CURSOR c_get_query_txt (c_QUERY_ID NUMBER)
715 IS
716 SELECT QUERY_TEXT1,QUERY_TEXT2
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;
726
727 --if the Query text is empty get the query text using the query id from the table
728 IF l_QUERY_TEXT1 IS NULL AND l_QUERY_TEXT2 IS NULL THEN
729
730 OPEN c_get_query_txt(p_QUERY_ID);
731 FETCH c_get_query_txt INTO l_QUERY_TEXT1,l_QUERY_TEXT2;
732 CLOSE c_get_query_txt;
733
734 END IF;
735
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;
745 END IF;
746
747 IF l_QUERY_TEXT1 IS NOT NULL THEN
748 IF l_QUERY_TEXT2 IS NOT NULL THEN
749 --Open the Cursor
750 l_qry_length1 := LENGTH(l_QUERY_TEXT1);
751 l_qry_count1 := CEIL(l_qry_length1/256);
752
753 FOR i in 1..l_qry_count1 LOOP
754
755 l_query_lst(i) := substr(l_QUERY_TEXT1,(((i-1)*256)+1),((i)*256));
756
757 END LOOP;
758 l_qry_length2 := LENGTH(l_QUERY_TEXT2);
759 l_qry_count2 := CEIL(l_qry_length2/256);
760 FOR i in 1..l_qry_count2 LOOP
761
762 l_query_lst(l_qry_count1 + i) := substr(l_QUERY_TEXT2,(((i-1)*256)+1),((i)*256));
763
764 END LOOP;
765 l_qry_tot_count := l_qry_count1 + l_qry_count2;
766 cursor_name := dbms_sql.open_cursor;
767 dbms_sql.parse(cursor_name, l_query_lst, 1, l_qry_tot_count, NULL, dbms_sql.native);
768 dbms_sql.close_cursor(cursor_name);
769 ELSE
770 --Open the Cursor
771 cursor_name := dbms_sql.open_cursor;
772 dbms_sql.parse(cursor_name, l_QUERY_TEXT1,dbms_sql.native);
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;
782
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;
792
793
794
795 /***
796 This procedure can be used to Execute a given query
797 ***/
798 PROCEDURE EXECUTE_QUERY
799 ( p_USER_ID IN NUMBER,
800 p_QUERY_ID IN NUMBER,
801 p_INSTANCE_ID IN NUMBER,
802 x_return_status OUT NOCOPY VARCHAR2,
803 x_error_message OUT NOCOPY VARCHAR2,
804 p_commit IN VARCHAR2 DEFAULT fnd_api.G_TRUE,
805 p_source_module IN VARCHAR2 DEFAULT 'MOBILEADMIN'
806 )
807 AS
808 CURSOR c_check_query(c_QUERY_ID NUMBER)
809 IS
810 SELECT
811 QUERY_TEXT1,
812 QUERY_TEXT2,
813 QUERY_TYPE,
814 WORK_FLOW,
815 PROCEDURE_NAME
816 FROM CSM_QUERY_B
817 WHERE QUERY_ID =c_QUERY_ID;
818
819 CURSOR c_check_instance(c_USER_ID NUMBER,c_QUERY_ID NUMBER,c_INSTANCE_ID NUMBER)
820 IS
821 SELECT INSTANCE_ID
822 FROM CSM_QUERY_INSTANCES_ACC
823 WHERE QUERY_ID = c_QUERY_ID
824 AND USER_ID = c_USER_ID
825 AND INSTANCE_ID = c_INSTANCE_ID;
826
827 CURSOR c_get_variables (c_USER_ID NUMBER,c_QUERY_ID NUMBER,c_INSTANCE_ID NUMBER)
828 IS
829 SELECT vacc.VARIABLE_ID,
830 NVL(vacc.VARIABLE_VALUE_CHAR,vb.VARIABLE_VALUE_CHAR) as VARIABLE_VALUE_CHAR,
831 NVL(vacc.VARIABLE_VALUE_DATE,vb.VARIABLE_VALUE_DATE) as VARIABLE_VALUE_DATE,
832 vb.VARIABLE_TYPE,
833 vb.VARIABLE_NAME
834 FROM
835 CSM_QUERY_INSTANCES_ACC iacc,
836 CSM_QUERY_VARIABLE_VALUES_ACC vacc,
837 CSM_QUERY_VARIABLES_B vb
838 WHERE iacc.USER_ID = vacc.USER_ID
839 AND iacc.QUERY_ID = vacc.QUERY_ID
840 AND iacc.INSTANCE_ID = vacc.INSTANCE_ID
841 AND vacc.QUERY_ID = vb.QUERY_ID
842 AND vacc.VARIABLE_ID = vb.VARIABLE_ID
843 AND iacc.USER_ID = c_USER_ID
844 AND iacc.QUERY_ID = c_QUERY_ID
845 AND iacc.INSTANCE_ID = c_INSTANCE_ID;
846
847 CURSOR c_check_results(c_USER_ID NUMBER,c_QUERY_ID NUMBER,c_INSTANCE_ID NUMBER)
848 IS
849 SELECT ACCESS_ID
850 FROM CSM_QUERY_RESULTS_ACC
851 WHERE QUERY_ID = c_QUERY_ID
852 AND USER_ID = c_USER_ID
853 AND INSTANCE_ID = c_INSTANCE_ID;
854
855 CURSOR c_get_wf_root (c_process_item_type VARCHAR2)
856 IS
857 SELECT ACTIVITY_NAME
858 FROM WF_PROCESS_ACTIVITIES
859 WHERE process_item_type =c_process_item_type
860 AND PROCESS_NAME ='ROOT';
861
862 l_query_lst dbms_sql.varchar2s;
863 l_qry_length1 NUMBER;
864 l_qry_length2 NUMBER;
865 l_qry_count1 NUMBER;
866 l_qry_count2 NUMBER;
867 l_qry_tot_count NUMBER;
868 l_QUERY_TEXT1 VARCHAR2(4000);
869 l_QUERY_TEXT2 VARCHAR2(4000);
870 l_file Utl_File.File_Type;
871 l_xml CLOB;
872 l_xml_blob BLOB;
873 l_more BOOLEAN := TRUE;
874 l_query_using VARCHAR2(4000);
875 rows_processed INTEGER;
876 qrycontext DBMS_XMLGEN.ctxHandle;
877 l_instance_id NUMBER;
878 l_dest_offset NUMBER := 1;
879 l_Src_offset NUMBER := 1;
880 l_language NUMBER := 0;
881 l_warning NUMBER := 0;
882 l_access_id NUMBER;
883 l_mark_dirty BOOLEAN;
884 l_rs_access_id NUMBER;
885 l_QUERY_TYPE VARCHAR2(255);
886 l_item_type VARCHAR2(8);
887 l_item_key VARCHAR2(240);
888 l_WORK_FLOW VARCHAR2(255);
889 l_root_process VARCHAR2(255);
890 l_cursor_name INTEGER;
891 l_cursor_ret INTEGER;
892 l_PROCEDURE_NAME VARCHAR2(255);
893 l_procedure_stmt VARCHAR2(32767);
894 l_variable_id_lst CSM_INTEGER_LIST;
895 l_var_value_char_lst CSM_VARCHAR_LIST;
896 l_var_value_date_lst CSM_DATE_LIST;
897 l_var_type_lst CSM_VARCHAR_LIST;
898 l_var_name_lst CSM_VARCHAR_LIST;
899 l_return_status VARCHAR2(4000);
900 l_return_message VARCHAR2(4000);
901 i NUMBER;
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
911 UPDATE_EXE_STATUS( p_USER_ID, p_QUERY_ID, p_INSTANCE_ID,
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;
921 RETURN;
922 END IF;
923 CLOSE c_check_query;
924
925 --Remove the concat string from Front end
926 --l_QUERY_TEXT1 := REPLACE(l_QUERY_TEXT1,'--','');
927 --l_QUERY_TEXT2 := REPLACE(l_QUERY_TEXT2,'--','');
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;
937 RETURN;
938 END IF;
939 CLOSE c_check_instance;
940
941 OPEN c_check_results(p_USER_ID, p_QUERY_ID, p_INSTANCE_ID);
942 FETCH c_check_results INTO l_rs_access_id;
943 CLOSE c_check_results;
944
945 IF (UPPER(l_QUERY_TYPE) ='SQL' OR UPPER(l_QUERY_TYPE) ='PROCEDURE') THEN
946
947 IF UPPER(l_QUERY_TYPE) = 'SQL' THEN
948
949 qrycontext := DBMS_XMLGEN.newcontext(l_QUERY_TEXT1 || l_QUERY_TEXT2) ;
950
951 FOR l_variable_rec IN c_get_variables(p_USER_ID,p_QUERY_ID,p_INSTANCE_ID)
952 LOOP
953 IF UPPER(l_variable_rec.VARIABLE_TYPE) = 'DATE' THEN
954 DBMS_XMLGEN.setbindvalue (qrycontext, l_variable_rec.VARIABLE_ID, l_variable_rec.VARIABLE_VALUE_DATE);
955 ELSE
956 DBMS_XMLGEN.setbindvalue (qrycontext, l_variable_rec.VARIABLE_ID, l_variable_rec.VARIABLE_VALUE_CHAR);
957 END IF;
958 END LOOP;
959 DBMS_XMLGEN.setnullhandling (qrycontext, DBMS_XMLGEN.empty_tag);
960 --Execute the SQL query
961 l_xml := DBMS_XMLGEN.getxml (qrycontext);
962
963 END IF;
964
965 IF UPPER(l_QUERY_TYPE) ='PROCEDURE' THEN
966
967 G_INST_IN_PROCESS := p_INSTANCE_ID;
968 l_cursor_name := DBMS_SQL.OPEN_CURSOR;
969 l_procedure_stmt := 'BEGIN ' || l_PROCEDURE_NAME || '(' ;
970
971 OPEN c_get_variables(p_USER_ID,p_QUERY_ID,p_INSTANCE_ID);
972 FETCH c_get_variables BULK COLLECT INTO l_variable_id_lst, l_var_value_char_lst, l_var_value_date_lst, l_var_type_lst, l_var_name_lst;
973 CLOSE c_get_variables;
974
975 l_bind_count := l_variable_id_lst.COUNT;
976 FOR i IN 1..l_bind_count LOOP
977 l_procedure_stmt := l_procedure_stmt || ':' || i || ',';
978 END LOOP;
979
980 l_procedure_stmt := l_procedure_stmt || ':' || TO_CHAR(l_bind_count + 1) || ',';
981 l_procedure_stmt := l_procedure_stmt || ':' || TO_CHAR(l_bind_count + 2) || ',';
982 l_procedure_stmt := l_procedure_stmt || ':' || TO_CHAR(l_bind_count + 3) || '); END;';
983
984 DBMS_SQL.PARSE (l_cursor_name, l_procedure_stmt, DBMS_SQL.NATIVE);
985
986 FOR i IN 1..l_variable_id_lst.COUNT LOOP
987 IF UPPER(l_var_type_lst(i)) = 'DATE' THEN
988 DBMS_SQL.BIND_VARIABLE (l_cursor_name, ':'|| i, l_var_value_date_lst(i));
989 ELSE
990 DBMS_SQL.BIND_VARIABLE (l_cursor_name, ':'|| i, l_var_value_char_lst(i));
991 END IF;
992 END LOOP;
993
994 DBMS_SQL.BIND_VARIABLE (l_cursor_name, ':'|| TO_CHAR(l_bind_count + 1), l_xml);
995 DBMS_SQL.BIND_VARIABLE (l_cursor_name, ':'|| TO_CHAR(l_bind_count + 2), l_return_status,4000);
996 DBMS_SQL.BIND_VARIABLE (l_cursor_name, ':'|| TO_CHAR(l_bind_count + 3), l_return_message,4000);
997
998 l_cursor_ret := DBMS_SQL.EXECUTE ( l_cursor_name );
999
1000 DBMS_SQL.VARIABLE_VALUE(l_cursor_name, ':'|| TO_CHAR(l_bind_count + 1), l_xml);
1001 DBMS_SQL.VARIABLE_VALUE(l_cursor_name, ':'|| TO_CHAR(l_bind_count + 2), l_return_status);
1002 DBMS_SQL.VARIABLE_VALUE(l_cursor_name, ':'|| TO_CHAR(l_bind_count + 3), l_return_message);
1003
1004 DBMS_SQL.CLOSE_CURSOR (l_cursor_name);
1005 x_error_message := l_return_message;
1006
1007 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1008 UPDATE_EXE_STATUS( p_USER_ID, p_QUERY_ID, p_INSTANCE_ID,
1009 NULL,SYSDATE,'ERROR','Query Completed with Error: '||SUBSTR(l_return_message,1,3000));
1010 x_error_message := l_return_message;
1011 G_INST_IN_PROCESS:=NULL;
1012 RETURN;
1013 END IF;
1014 END IF;
1015
1016 IF DBMS_LOB.GETLENGTH(l_xml) > 0 THEN
1017 --Convert the XML output into BLOB and store it in the DB
1018 dbms_lob.createtemporary(l_xml_blob,TRUE);
1019 DBMS_LOB.convertToBlob(l_xml_blob,l_xml,DBMS_LOB.LOBMAXSIZE,
1020 l_dest_offset,l_src_offset,DBMS_LOB.default_csid,l_language,l_warning);
1021 IF l_rs_access_id IS NOT NULL THEN
1022 UPDATE CSM_QUERY_RESULTS_ACC
1023 SET RESULT = l_xml_blob,
1024 LAST_UPDATE_DATE = sysdate,
1025 LAST_UPDATED_BY = fnd_global.user_id
1026 WHERE ACCESS_ID = l_rs_access_id;
1027
1028 l_access_id := l_rs_access_id;
1029 ELSE
1030
1031 INSERT INTO CSM_QUERY_RESULTS_ACC(ACCESS_ID , USER_ID , QUERY_ID , INSTANCE_ID , LINE_ID,
1032 RESULT ,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY, LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
1033 VALUES (CSM_QUERY_RESULTS_ACC_S.NEXTVAL, p_USER_ID, p_QUERY_ID, p_INSTANCE_ID, 1,
1034 l_xml_blob, fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE, fnd_global.login_id)
1035 RETURNING ACCESS_ID into l_access_id;
1036
1037 END IF;
1038 ELSE--empty results
1039 IF l_rs_access_id IS NOT NULL THEN
1040 UPDATE CSM_QUERY_RESULTS_ACC
1041 SET RESULT = EMPTY_BLOB(),
1042 LAST_UPDATE_DATE = sysdate,
1043 LAST_UPDATED_BY = fnd_global.user_id
1044 WHERE ACCESS_ID = l_rs_access_id;
1045
1046 l_access_id := l_rs_access_id;
1047 ELSE
1048 INSERT INTO CSM_QUERY_RESULTS_ACC(ACCESS_ID , USER_ID , QUERY_ID , INSTANCE_ID , LINE_ID,
1049 RESULT ,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY, LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
1050 VALUES (CSM_QUERY_RESULTS_ACC_S.NEXTVAL, p_USER_ID, p_QUERY_ID, p_INSTANCE_ID, 1,
1051 EMPTY_BLOB(), fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE, fnd_global.login_id)
1052 RETURNING ACCESS_ID into l_access_id;
1053 END IF;
1054
1055 END IF;
1056
1057 IF csm_util_pkg.is_palm_user(p_USER_ID) THEN
1058 IF l_rs_access_id IS NULL THEN
1059 l_mark_dirty := asg_Download.mark_dirty (p_pub_item => g_pub_item_qres,
1060 p_accessid => l_access_id,
1061 p_userid => p_USER_ID,
1062 p_dml => asg_download.ins,
1063 p_timestamp => sysdate);
1064 ELSE
1065 l_mark_dirty := asg_Download.mark_dirty (p_pub_item => g_pub_item_qres,
1066 p_accessid => l_access_id,
1067 p_userid => p_USER_ID,
1068 p_dml => asg_download.upd,
1069 p_timestamp => sysdate);
1070
1071 END IF;
1072 END IF;
1073
1074 UPDATE_EXE_STATUS( p_USER_ID, p_QUERY_ID, p_INSTANCE_ID,
1075 NULL,SYSDATE,'EXECUTED','Query Successfully Executed');
1076 G_INST_IN_PROCESS:=NULL;
1077
1078 ELSIF UPPER(l_QUERY_TYPE) ='WORKFLOW' THEN
1079
1080 l_item_type := SUBSTR(l_WORK_FLOW,1,8);
1081 l_item_key := SUBSTR(l_WORK_FLOW,1,8)||':' ||p_INSTANCE_ID ;
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;
1091 RETURN;
1092 END IF;
1093 CLOSE c_get_wf_root;
1094
1095
1096 wf_engine.createprocess(l_item_type, l_item_key,l_root_process);
1097
1098 wf_engine.setitemuserkey(itemtype => l_item_type
1099 ,itemkey => l_item_key
1100 ,userkey => 'USERKEY: ' || l_item_key);
1101 wf_engine.setitemowner(itemtype => l_item_type
1102 ,itemkey => l_item_key
1103 ,owner => 'SYSADMIN');
1104
1105 --Set the Default Variables
1106 wf_engine.setitemattrText(itemtype => l_item_type
1107 ,itemkey => l_item_key
1108 ,aname => 'USER_ID'
1109 ,avalue => p_USER_ID);
1110
1111 wf_engine.setitemattrText(itemtype => l_item_type
1112 ,itemkey => l_item_key
1113 ,aname => 'QUERY_ID'
1114 ,avalue => p_QUERY_ID);
1115
1116 wf_engine.setitemattrText(itemtype => l_item_type
1117 ,itemkey => l_item_key
1118 ,aname => 'INSTANCE_ID'
1119 ,avalue => p_INSTANCE_ID);
1120
1121 --Set the parmeters for the Workflow
1122 FOR l_variable_rec IN c_get_variables(p_USER_ID,p_QUERY_ID,p_INSTANCE_ID)
1123 LOOP
1124 IF UPPER(l_variable_rec.VARIABLE_TYPE) = 'DATE' THEN
1125 wf_engine.setitemattrDate(itemtype => l_item_type
1126 ,itemkey => l_item_key
1127 ,aname => l_variable_rec.VARIABLE_NAME
1128 ,avalue => l_variable_rec.VARIABLE_VALUE_DATE);
1129 ELSE
1130 wf_engine.setitemattrText(itemtype => l_item_type
1131 ,itemkey => l_item_key
1132 ,aname => l_variable_rec.VARIABLE_NAME
1133 ,avalue => l_variable_rec.VARIABLE_VALUE_CHAR);
1134 END IF;
1135 END LOOP;
1136 wf_engine.startprocess(l_item_type, l_item_key);
1137 --update Work flow status as running
1138 UPDATE_EXE_STATUS( p_USER_ID, p_QUERY_ID, p_INSTANCE_ID,
1139 NULL,SYSDATE,'RUNNING','Query Successfully Executed');
1140
1141 END IF;--Query Type
1142
1143 IF FND_API.To_Boolean(p_commit) THEN
1144 COMMIT WORK;
1145 END IF;
1146
1147 IF UPPER(l_QUERY_TYPE) ='SQL' AND l_rs_access_id IS NULL THEN
1148 --Notify user for auto sync on New Query result
1149 CSM_WF_PKG.RAISE_START_AUTO_SYNC_EVENT('CSM_QUERY_RESULTS',to_char(p_INSTANCE_ID),'NEW');
1150 END IF;
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
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
1168 UPDATE_EXE_STATUS( p_USER_ID, p_QUERY_ID, p_INSTANCE_ID,
1169 NULL,SYSDATE,'ERROR','Query Completed with Error'||SUBSTR(SQLERRM,1,3000) );
1170 COMMIT WORK;
1171 ELSE
1172 ROLLBACK TO EXECUTE_QUERY;
1173 END IF;
1174 END EXECUTE_QUERY;
1175
1176 --Procedure to Create a Instance for a Given Query and store in the Acc table
1177
1178 PROCEDURE INSERT_INSTANCE
1179 ( p_USER_ID IN NUMBER,
1180 p_QUERY_ID IN NUMBER,
1181 p_INSTANCE_ID IN VARCHAR2 DEFAULT NULL,
1182 p_INSTANCE_NAME IN VARCHAR2,
1183 p_VARIABLE_ID IN CSM_INTEGER_LIST,
1184 p_VARIABLE_VALUE_CHAR IN CSM_VARCHAR_LIST,
1185 p_VARIABLE_VALUE_DATE IN CSM_DATE_LIST,
1186 p_commit IN VARCHAR2 DEFAULT fnd_api.G_TRUE,
1187 x_INSTANCE_ID OUT NOCOPY NUMBER,
1188 x_return_status OUT NOCOPY VARCHAR2,
1189 x_error_message OUT NOCOPY VARCHAR2
1190 )
1191 AS
1192 CURSOR c_check_query(c_QUERY_ID NUMBER)
1193 IS
1194 SELECT QUERY_NAME,LEVEL_ID,LEVEL_VALUE,QUERY_TYPE
1195 FROM CSM_QUERY_B
1196 WHERE QUERY_ID =c_QUERY_ID
1197 AND NVL(DELETE_FLAG,'N') = 'N';
1198
1199 CURSOR c_check_query_acc(c_QUERY_ID NUMBER, c_USER_ID NUMBER)
1200 IS
1201 SELECT QUERY_ID
1202 FROM CSM_QUERY_ACC
1203 WHERE QUERY_ID = c_QUERY_ID
1204 AND USER_ID = c_USER_ID;
1205
1206 CURSOR c_get_variables (c_QUERY_ID NUMBER)
1207 IS
1208 SELECT vb.VARIABLE_ID,
1209 vb.VARIABLE_VALUE_CHAR,
1210 vb.VARIABLE_VALUE_DATE,
1211 vb.VARIABLE_TYPE
1212 FROM CSM_QUERY_VARIABLES_B vb
1213 WHERE vb.QUERY_ID = c_QUERY_ID;
1214
1215 CURSOR c_get_next_instance
1216 IS
1217 SELECT CSM_QUERY_INSTANCES_ACC_S.NEXTVAL
1218 FROM DUAL;
1219
1220 CURSOR c_get_next_qvariableid
1221 IS
1222 SELECT CSM_QUERY_VARIABLE_VAL_ACC_S.NEXTVAL
1223 FROM DUAL;
1224
1225 l_variable_id_lst CSM_INTEGER_LIST;
1226 l_var_value_char_lst CSM_VARCHAR_LIST;
1227 l_var_value_date_lst CSM_DATE_LIST;
1228 l_var_type_lst CSM_VARCHAR_LIST;
1229 l_query_id NUMBER;
1230 l_variable_cnt NUMBER;
1231 l_ins_variable_cnt NUMBER;
1232 l_instance_name VARCHAR2(255);
1233 l_instance_id NUMBER;
1234 l_access_id NUMBER;
1235 l_mark_dirty BOOLEAN;
1236 l_dummy_qry_id NUMBER;
1237 l_qvariable_id NUMBER;
1238 l_level_id NUMBER;
1239 l_level_value NUMBER;
1240 l_responsibility_id NUMBER := NULL;
1241 l_return_status VARCHAR2(4000);
1242 l_return_message VARCHAR2(4000);
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;
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;
1258 END IF;
1259 CLOSE c_check_query;
1260
1261 IF l_level_id = 10003 THEN
1262 l_responsibility_id := l_level_value;
1263 END IF;
1264 IF l_query_type ='SQL' THEN
1265 --Validate the Access to a Query
1266 VALIDATE_ACCESS( p_QUERY_ID => p_QUERY_ID,
1267 p_QUERY_TEXT1 => NULL,
1268 p_QUERY_TEXT2 => NULL,
1269 p_RESPONSIBILITY_ID => l_responsibility_id,
1270 x_return_status => l_return_status,
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
1280 RETURN;
1281 END IF;
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;
1291 RETURN;
1292 END IF;
1293 CLOSE c_check_query_acc;*/
1294
1295 SAVEPOINT INSERT_INSTANCE;
1296
1297 IF p_INSTANCE_NAME IS NOT NULL THEN
1298 l_instance_name := p_INSTANCE_NAME;
1299 ELSE
1300 l_instance_name := l_instance_name || p_USER_ID || ':'|| p_QUERY_ID;
1301 END IF;
1302
1303 IF p_INSTANCE_ID IS NULL THEN
1304 OPEN c_get_next_instance;
1305 FETCH c_get_next_instance INTO l_instance_id;
1306 CLOSE c_get_next_instance;
1307 ELSE
1308 l_instance_id:= p_INSTANCE_ID;
1309 END IF;
1310 --Insert the Instance
1311 INSERT INTO CSM_QUERY_INSTANCES_ACC(ACCESS_ID , USER_ID , QUERY_ID , INSTANCE_ID , INSTANCE_NAME,STATUS,
1312 CREATED_BY,CREATION_DATE,LAST_UPDATED_BY, LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
1313 VALUES (l_instance_id, p_USER_ID, p_QUERY_ID, l_instance_id,l_instance_name,'OPEN',
1314 fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE, fnd_global.login_id);
1315
1316
1317 IF csm_util_pkg.is_palm_user(p_USER_ID) THEN
1318 l_mark_dirty := asg_Download.mark_dirty (p_pub_item => g_pub_item_qins,
1319 p_accessid => l_instance_id, --same as access id
1320 p_userid => p_USER_ID,
1321 p_dml => asg_download.ins,
1322 p_timestamp => sysdate);
1323 END IF;
1324
1325 --set the newly created instance id
1326 x_INSTANCE_ID := l_instance_id;
1327
1328 OPEN c_get_variables (p_QUERY_ID);
1329 FETCH c_get_variables BULK COLLECT INTO l_variable_id_lst, l_var_value_char_lst, l_var_value_date_lst, l_var_type_lst;
1330 CLOSE c_get_variables;
1331
1332 IF l_variable_id_lst.COUNT = 0 THEN
1333
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 ;
1343 RETURN;
1344
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;
1354 RETURN;
1355 END IF;
1356 --Check if the values send are proper according to the Type
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;
1366 RETURN;
1367 END IF;
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
1377 ROLLBACK TO INSERT_INSTANCE;
1378 RETURN;
1379 END IF;
1380 END IF;
1381
1382 END LOOP;
1383
1384 --Insert into variable values table
1385 FOR i in 1..p_VARIABLE_ID.COUNT LOOP
1386
1387 OPEN c_get_next_qvariableid;
1388 FETCH c_get_next_qvariableid INTO l_qvariable_id;
1389 CLOSE c_get_next_qvariableid;
1390
1391 l_VARIABLE_VALUE_CHAR := SUBSTR(p_VARIABLE_VALUE_CHAR(i),1, 4000);
1392
1393 INSERT INTO CSM_QUERY_VARIABLE_VALUES_ACC(ACCESS_ID , USER_ID , QUERY_ID ,
1394 INSTANCE_ID , VARIABLE_ID,VARIABLE_VALUE_CHAR,VARIABLE_VALUE_DATE,
1395 CREATED_BY,CREATION_DATE,LAST_UPDATED_BY, LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,GEN_PK)
1396 VALUES (l_qvariable_id, p_USER_ID, p_QUERY_ID,
1397 l_instance_id, i, l_VARIABLE_VALUE_CHAR, p_VARIABLE_VALUE_DATE(i),
1398 fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE, fnd_global.login_id,l_qvariable_id)
1399 RETURNING ACCESS_ID into l_access_id;
1400
1401 IF csm_util_pkg.is_palm_user(p_USER_ID) THEN
1402 l_mark_dirty := asg_Download.mark_dirty(p_pub_item => g_pub_item_qval,
1403 p_accessid => l_access_id,
1404 p_userid => p_USER_ID,
1405 p_dml => asg_download.ins,
1406 p_timestamp => sysdate);
1407 END IF;
1408 END LOOP;
1409
1410 IF FND_API.To_Boolean(p_commit) THEN
1411 COMMIT WORK;
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
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
1428 ROLLBACK TO INSERT_INSTANCE;
1429 END INSERT_INSTANCE;
1430
1431 /***
1432 This procedure can be used to validate a given query
1433 ***/
1434 PROCEDURE VALIDATE_ACCESS
1435 ( p_QUERY_ID IN NUMBER DEFAULT NULL,
1436 p_QUERY_TEXT1 IN VARCHAR2 DEFAULT NULL,
1437 p_QUERY_TEXT2 IN VARCHAR2 DEFAULT NULL,
1438 p_RESPONSIBILITY_ID IN NUMBER DEFAULT NULL,
1439 x_return_status OUT NOCOPY VARCHAR2,
1440 x_error_message OUT NOCOPY VARCHAR2
1441 )
1442 AS
1443 cursor_name INTEGER;
1444 TYPE TABLE_TYPE IS TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER;
1445 l_query_lst dbms_sql.varchar2s;
1446 l_qry_length1 NUMBER;
1447 l_qry_length2 NUMBER;
1448 l_qry_count1 NUMBER;
1449 l_qry_count2 NUMBER;
1450 l_qry_tot_count NUMBER;
1451 l_QUERY_TEXT1 VARCHAR2(4000);
1452 l_QUERY_TEXT2 VARCHAR2(4000);
1453 l_from_count NUMBER;
1454 l_found_from_num NUMBER;
1455 i NUMBER;
1456 l_MQ_SCHEMA VARCHAR2(30);
1457 l_dummy NUMBER;
1458 l_TABLE_STR VARCHAR2(4000);
1459 l_str_length NUMBER;
1460 l_end_num NUMBER;
1461 l_TABLE_LIST TABLE_TYPE;
1462 l_from_start NUMBER;
1463 l_where_start NUMBER;
1464 l_query_clob CLOB;
1465 l_temp_query_clob CLOB;
1466 CURSOR c_get_query_txt (c_QUERY_ID NUMBER)
1467 IS
1468 SELECT QUERY_TEXT1,QUERY_TEXT2
1469 FROM CSM_QUERY_B
1470 WHERE QUERY_ID = c_QUERY_ID;
1471
1472 CURSOR c_check_table_access (c_schema VARCHAR2,c_object_name VARCHAR2)
1473 IS
1474 SELECT 1 FROM ALL_OBJECTS
1475 WHERE OBJECT_NAME = c_object_name
1476 AND OWNER = c_schema
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
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
1492 RETURN;
1493 END IF;
1494
1495 l_QUERY_TEXT1 := p_QUERY_TEXT1;
1496 l_QUERY_TEXT2 := p_QUERY_TEXT2;
1497
1498 --if the Query text is empty get the query text using the query id from the table
1499 IF l_QUERY_TEXT1 IS NULL AND l_QUERY_TEXT2 IS NULL THEN
1500 OPEN c_get_query_txt(p_QUERY_ID);
1501 FETCH c_get_query_txt INTO l_QUERY_TEXT1,l_QUERY_TEXT2;
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;
1511 END IF;
1512 l_query_clob := l_QUERY_TEXT1 || l_QUERY_TEXT2;
1513
1514 IF l_query_clob IS NOT NULL THEN
1515 l_from_count := 0;
1516 --l_query_clob := REPLACE(l_query_clob,'--','');
1517 l_temp_query_clob := l_query_clob;
1518 --get total FROM in the Query
1519 LOOP
1520 l_from_count := l_from_count+1;
1521 l_found_from_num := INSTR(UPPER(l_temp_query_clob),' FROM ',1,l_from_count);
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
1531 RETURN;
1532 END IF;
1533 --For all the From Get the Tables in a table_list
1534 i := 1;
1535
1536 FOR j IN 1..l_from_count-1 LOOP
1537 l_where_start := INSTR(UPPER(l_temp_query_clob),' WHERE ');
1538 l_from_start := INSTR(UPPER(l_temp_query_clob),' FROM ')+ 6;
1539 --to support Queries without where condition
1540 IF l_where_start =0 THEN
1541 l_where_start := LENGTH(l_temp_query_clob);
1542 END IF;
1543
1544 l_TABLE_STR := SUBSTR(l_temp_query_clob,l_from_start,l_where_start-l_from_start);
1545 l_temp_query_clob := SUBSTR(l_temp_query_clob,l_where_start+7,LENGTH(l_temp_query_clob));
1546 LOOP
1547 l_str_length := LENGTH(l_TABLE_STR);
1548 l_end_num := INSTR(l_TABLE_STR,',',1);
1549 IF l_end_num = 0 THEN
1550 IF INSTR(l_TABLE_STR,' ',1) > 0 THEN
1551 l_TABLE_LIST(i) := LTRIM(SUBSTR(l_TABLE_STR,1,INSTR(l_TABLE_STR,' ',1)-1));
1552 ELSE
1553 l_TABLE_LIST(i) := LTRIM(l_TABLE_STR);
1554 END IF;
1555 ELSE
1556 l_TABLE_LIST(i) := SUBSTR(l_TABLE_STR,1,l_end_num-1);
1557 l_TABLE_STR := LTRIM(SUBSTR(l_TABLE_STR,l_end_num+1,l_str_length));
1558 IF INSTR(l_TABLE_LIST(i),' ',1) > 0 THEN
1559 l_TABLE_LIST(i) := LTRIM(SUBSTR(l_TABLE_LIST(i),1,INSTR(l_TABLE_LIST(i),' ',1)-1));
1560 ELSE
1561 l_TABLE_LIST(i) := LTRIM(l_TABLE_LIST(i));
1562 END IF;
1563 END IF;
1564
1565 i := i+1;
1566 EXIT WHEN l_end_num=0;
1567 END LOOP; --loop to get all tables ends
1568 END LOOP; --For loop for all From Count Ends
1569
1570 --Verify the Tables in the schema
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;
1580 RETURN;
1581 END IF;
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
1600 END VALIDATE_ACCESS;
1601
1602 --Procedure to Delete a Instance for a Given Query
1603
1604 PROCEDURE DELETE_INSTANCE
1605 ( p_USER_ID IN NUMBER,
1606 p_QUERY_ID IN NUMBER,
1607 p_INSTANCE_ID IN NUMBER,
1608 p_commit IN VARCHAR2 DEFAULT fnd_api.G_TRUE,
1609 x_return_status OUT NOCOPY VARCHAR2,
1610 x_error_message OUT NOCOPY VARCHAR2
1611 )
1612 AS
1613 CURSOR c_get_instance (c_USER_ID NUMBER, c_QUERY_ID NUMBER, c_INSTANCE_ID NUMBER )
1614 IS
1615 SELECT ACCESS_ID
1616 FROM CSM_QUERY_INSTANCES_ACC
1617 WHERE QUERY_ID = c_QUERY_ID
1618 AND USER_ID = c_USER_ID
1619 AND INSTANCE_ID = c_INSTANCE_ID;
1620
1621 CURSOR c_get_ins_varval (c_USER_ID NUMBER, c_QUERY_ID NUMBER, c_INSTANCE_ID NUMBER )
1622 IS
1623 SELECT ACCESS_ID
1624 FROM CSM_QUERY_VARIABLE_VALUES_ACC
1625 WHERE QUERY_ID = c_QUERY_ID
1626 AND USER_ID = c_USER_ID
1627 AND INSTANCE_ID = c_INSTANCE_ID;
1628
1629 CURSOR c_get_results (c_USER_ID NUMBER, c_QUERY_ID NUMBER, c_INSTANCE_ID NUMBER )
1630 IS
1631 SELECT ACCESS_ID
1632 FROM CSM_QUERY_RESULTS_ACC
1633 WHERE QUERY_ID = c_QUERY_ID
1634 AND USER_ID = c_USER_ID
1635 AND INSTANCE_ID = c_INSTANCE_ID;
1636
1637 l_access_id_list ASG_DOWNLOAD.ACCESS_LIST;
1638 l_instance_name VARCHAR2(255);
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
1648 --Delete the instance
1649 OPEN c_get_instance(p_USER_ID, p_QUERY_ID, p_INSTANCE_ID);
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;
1659 END IF;
1660
1661 DELETE FROM CSM_QUERY_INSTANCES_ACC WHERE ACCESS_ID = l_access_id;
1662
1663 IF csm_util_pkg.is_palm_user(p_USER_ID) THEN
1664 l_mark_dirty := asg_Download.mark_dirty (p_pub_item => g_pub_item_qins,
1665 p_accessid => l_access_id, --same as access id
1666 p_userid => p_USER_ID,
1667 p_dml => asg_download.del,
1668 p_timestamp => sysdate);
1669 END IF;
1670
1671 --Delete the Variable Values attached to the instance
1672 OPEN c_get_ins_varval(p_USER_ID, p_QUERY_ID, p_INSTANCE_ID);
1673 FETCH c_get_ins_varval BULK COLLECT INTO l_access_id_list;
1674 CLOSE c_get_ins_varval;
1675
1676 FORALL i in 1..l_access_id_list.COUNT
1677 DELETE FROM CSM_QUERY_VARIABLE_VALUES_ACC WHERE ACCESS_ID = l_access_id_list(i);
1678
1679 IF csm_util_pkg.is_palm_user(p_USER_ID) THEN
1680 FOR i in 1..l_access_id_list.COUNT LOOP
1681 l_mark_dirty := asg_Download.mark_dirty (p_pub_item => g_pub_item_qval,
1682 p_accessid => l_access_id_list(i), --same as access id
1683 p_userid => p_USER_ID,
1684 p_dml => asg_download.del,
1685 p_timestamp => sysdate);
1686 END LOOP;
1687 END IF;
1688
1689 IF l_access_id_list.COUNT > 0 THEN
1690 l_access_id_list.DELETE;
1691 END IF;
1692 --Delete the Results attached to the instance
1693 OPEN c_get_results(p_USER_ID, p_QUERY_ID, p_INSTANCE_ID);
1694 FETCH c_get_results BULK COLLECT INTO l_access_id_list;
1695 CLOSE c_get_results;
1696
1697 FORALL i in 1..l_access_id_list.COUNT
1698 DELETE FROM CSM_QUERY_RESULTS_ACC WHERE ACCESS_ID = l_access_id_list(i);
1699
1700 IF csm_util_pkg.is_palm_user(p_USER_ID) THEN
1701 FOR i in 1..l_access_id_list.COUNT LOOP
1702 l_mark_dirty := asg_Download.mark_dirty (p_pub_item => g_pub_item_qres,
1703 p_accessid => l_access_id_list(i), --same as access id
1704 p_userid => p_USER_ID,
1705 p_dml => asg_download.del,
1706 p_timestamp => sysdate);
1707 END LOOP;
1708 END IF;
1709
1710 IF FND_API.To_Boolean(p_commit) THEN
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
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;
1728 END DELETE_INSTANCE;
1729
1730 /***
1731 This procedure can be used to validate a given query
1732 ***/
1733 PROCEDURE VALIDATE_WORKFLOW
1734 ( p_QUERY_ID IN NUMBER DEFAULT NULL,
1735 p_WORKFLOW IN VARCHAR2 DEFAULT NULL,
1736 p_VARIABLE_NAME IN CSM_VARCHAR_LIST,
1737 x_return_status OUT NOCOPY VARCHAR2,
1738 x_error_message OUT NOCOPY VARCHAR2
1739 )
1740 AS
1741
1742 l_WORKFLOW VARCHAR2(8);
1743 l_TEMP_WORKFLOW VARCHAR2(8);
1744 l_TEMP_VARIABLE VARCHAR2(30);
1745 CURSOR c_get_wf (c_query_id NUMBER)
1746 IS
1747 SELECT WORK_FLOW
1748 FROM CSM_QUERY_B
1749 WHERE QUERY_ID = c_query_id;
1750
1751 CURSOR c_get_item_type (c_work_flow VARCHAR2)
1752 IS
1753 SELECT NAME
1754 FROM WF_ITEM_TYPES
1755 WHERE NAME = c_work_flow;
1756
1757 CURSOR c_get_item_variable (c_work_flow VARCHAR2, c_variable_name VARCHAR2)
1758 IS
1759 SELECT NAME
1760 FROM WF_ITEM_ATTRIBUTES
1761 WHERE item_type = c_work_flow
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;
1771 --if the Work Flow is empty get the query text using the query id from the table
1772 IF l_WORKFLOW IS NULL THEN
1773 OPEN c_get_wf(p_QUERY_ID);
1774 FETCH c_get_wf INTO l_WORKFLOW;
1775 CLOSE c_get_wf;
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;
1785 END IF;
1786
1787 IF l_WORKFLOW IS NOT NULL THEN
1788 l_WORKFLOW := SUBSTR(p_WORKFLOW,1,8);
1789
1790 OPEN c_get_item_type(l_WORKFLOW);
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;
1800 END IF;
1801 --check if work flow parameters are valid
1802 IF p_VARIABLE_NAME IS NOT NULL THEN
1803
1804 FOR i in 1..p_VARIABLE_NAME.COUNT LOOP
1805 l_TEMP_VARIABLE := NULL;
1806 OPEN c_get_item_variable (l_WORKFLOW, UPPER(p_VARIABLE_NAME(i)));
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;
1816 END IF;
1817
1818 END LOOP;
1819 END IF;
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
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;
1836
1837 /***
1838 This procedure can be used to validate a given query
1839 ***/
1840 PROCEDURE VALIDATE_PROCEDURE
1841 ( p_QUERY_ID IN NUMBER DEFAULT NULL,
1842 p_PROCEDURE IN VARCHAR2 DEFAULT NULL,
1843 x_return_status OUT NOCOPY VARCHAR2,
1844 x_error_message OUT NOCOPY VARCHAR2
1845 )
1846 AS
1847
1848 l_PROCEDURE VARCHAR2(240);
1849 l_PACKAGE_NAME VARCHAR2(240);
1850 l_SCHEMA_NAME VARCHAR2(30);
1851
1852 CURSOR c_get_procedure (c_query_id NUMBER)
1853 IS
1854 SELECT procedure_name
1855 FROM CSM_QUERY_B
1856 WHERE QUERY_ID = c_query_id;
1857
1858 CURSOR c_is_package_valid (c_package_name VARCHAR2, p_schema_name VARCHAR2)
1859 IS
1860 SELECT object_name
1861 FROM all_objects
1862 WHERE object_name = c_package_name
1863 AND OWNER = p_schema_name
1864 AND object_type = 'PACKAGE'
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
1874 OPEN c_get_procedure(p_QUERY_ID);
1875 FETCH c_get_procedure INTO l_PROCEDURE;
1876 CLOSE c_get_procedure;
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;
1886 END IF;
1887
1888 SELECT oracle_username INTO l_SCHEMA_NAME
1889 FROM fnd_oracle_userid
1890 WHERE read_only_flag = 'U';
1891
1892 IF l_PROCEDURE IS NOT NULL THEN
1893 l_PACKAGE_NAME := SUBSTR(l_PROCEDURE, 0, INSTR(l_PROCEDURE, '.') -1);
1894
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;
1904 END IF;
1905
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 ***/
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;
1921
1922
1923 PROCEDURE INSERT_RESULT
1924 ( p_USER_ID IN NUMBER,
1925 p_QUERY_ID IN NUMBER,
1926 p_INSTANCE_ID IN VARCHAR2 DEFAULT NULL,
1927 p_QUERY_RESULT IN BLOB,
1928 p_commit IN VARCHAR2 DEFAULT fnd_api.G_TRUE,
1929 x_return_status OUT NOCOPY VARCHAR2,
1930 x_error_message OUT NOCOPY VARCHAR2
1931 )
1932 AS
1933 CURSOR c_check_results(c_USER_ID NUMBER,c_QUERY_ID NUMBER,c_INSTANCE_ID NUMBER)
1934 IS
1935 SELECT ACCESS_ID
1936 FROM CSM_QUERY_RESULTS_ACC
1937 WHERE QUERY_ID = c_QUERY_ID
1938 AND USER_ID = c_USER_ID
1939 AND INSTANCE_ID = c_INSTANCE_ID;
1940
1941 l_access_id NUMBER;
1942 l_mark_dirty BOOLEAN;
1943 l_rs_access_id NUMBER;
1944 g_pub_item_qres VARCHAR2(30) := 'CSM_QUERY_RESULTS';
1945 l_sqlerrno VARCHAR2(200);
1946 l_sqlerrmsg VARCHAR2(4000);
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;
1956 CLOSE c_check_results;
1957 x_return_status := FND_API.G_RET_STS_SUCCESS;
1958 SAVEPOINT INSERT_RESULT;
1959
1960 IF p_QUERY_RESULT IS NOT NULL THEN
1961 IF l_rs_access_id IS NOT NULL THEN
1962 UPDATE CSM_QUERY_RESULTS_ACC
1963 SET RESULT = p_QUERY_RESULT,
1964 LAST_UPDATE_DATE = sysdate,
1965 LAST_UPDATED_BY = fnd_global.user_id
1966 WHERE ACCESS_ID = l_rs_access_id;
1967
1968 l_access_id := l_rs_access_id;
1969 ELSE
1970
1971 INSERT INTO CSM_QUERY_RESULTS_ACC(ACCESS_ID , USER_ID , QUERY_ID , INSTANCE_ID , LINE_ID,
1972 RESULT ,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY, LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
1973 VALUES (CSM_QUERY_RESULTS_ACC_S.NEXTVAL, p_USER_ID, p_QUERY_ID, p_INSTANCE_ID, 1,
1974 p_QUERY_RESULT, fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE, fnd_global.login_id)
1975 RETURNING ACCESS_ID into l_access_id;
1976
1977 END IF;
1978 ELSE--empty results
1979 IF l_rs_access_id IS NOT NULL THEN
1980 UPDATE CSM_QUERY_RESULTS_ACC
1981 SET RESULT = EMPTY_BLOB(),
1982 LAST_UPDATE_DATE = sysdate,
1983 LAST_UPDATED_BY = fnd_global.user_id
1984 WHERE ACCESS_ID = l_rs_access_id;
1985
1986 l_access_id := l_rs_access_id;
1987 ELSE
1988 INSERT INTO CSM_QUERY_RESULTS_ACC(ACCESS_ID , USER_ID , QUERY_ID , INSTANCE_ID , LINE_ID,
1989 RESULT ,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY, LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
1990 VALUES (CSM_QUERY_RESULTS_ACC_S.NEXTVAL, p_USER_ID, p_QUERY_ID, p_INSTANCE_ID, 1,
1991 EMPTY_BLOB(), fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE, fnd_global.login_id)
1992 RETURNING ACCESS_ID into l_access_id;
1993 END IF;
1994
1995 END IF;
1996
1997 IF csm_util_pkg.is_palm_user(p_USER_ID) THEN
1998 IF l_rs_access_id IS NULL THEN
1999 l_mark_dirty := asg_Download.mark_dirty (p_pub_item => g_pub_item_qres,
2000 p_accessid => l_access_id,
2001 p_userid => p_USER_ID,
2002 p_dml => asg_download.ins,
2003 p_timestamp => sysdate);
2004 ELSE
2005 l_mark_dirty := asg_Download.mark_dirty (p_pub_item => g_pub_item_qres,
2006 p_accessid => l_access_id,
2007 p_userid => p_USER_ID,
2008 p_dml => asg_download.upd,
2009 p_timestamp => sysdate);
2010
2011 END IF;
2012 END IF;
2013 --Update the Status of the Workflow
2014 UPDATE_EXE_STATUS( p_USER_ID, p_QUERY_ID, p_INSTANCE_ID,
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';
2024
2025 EXCEPTION
2026 WHEN OTHERS THEN
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;
2036
2037 PROCEDURE SET_VALIDATE_PROCEDURE(p_validate_procedure IN VARCHAR2)
2038 AS
2039 BEGIN
2040 IF(upper(p_validate_procedure) IN ('Y', 'N')) THEN
2041 g_validate_procedure := upper(p_validate_procedure);
2042 END IF;
2043
2044 END SET_VALIDATE_PROCEDURE;
2045
2046 END CSM_QUERY_PKG; -- Package spec
2047