[Home] [Help]
PACKAGE BODY: APPS.CSM_QUERY_PUB
Source
1 PACKAGE BODY CSM_QUERY_PUB AS
2 /* $Header: csmqrypb.pls 120.3 2011/01/06 09:15:22 ravir noship $ */
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-JUL-2009 Created
12 --
13 -- --------- ------------------- ------------------------------------------
14 -- Enter package declarations as shown below
15
16 /*** Globals ***/
17
18 /***
19 This procedure is called to insert a Query
20 ***/
21 PROCEDURE INSERT_QUERY
22 (
23 p_API_VERSION IN NUMBER,
24 p_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
25 p_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
26 p_QUERY_ID IN NUMBER,
27 p_QUERY_NAME IN VARCHAR2,
28 P_QUERY_DESC IN VARCHAR2,
29 P_QUERY_TYPE IN VARCHAR2,
30 p_QUERY_TEXT1 IN VARCHAR2,
31 p_QUERY_TEXT2 IN VARCHAR2,
32 p_LEVEL_ID IN NUMBER,
33 p_LEVEL_VALUE IN NUMBER,
34 p_PARENT_QUERY_ID IN NUMBER,
35 p_SAVED_QUERY IN VARCHAR2,
36 p_QUERY_OUTPUT_FORMAT IN VARCHAR2,
37 p_MIME_TYPE IN VARCHAR2,
38 p_WORK_FLOW IN VARCHAR2,
39 p_PROCEDURE IN VARCHAR2,
40 p_RETENTION_POLICY IN VARCHAR2,
41 p_RETENTION_DAYS IN NUMBER,
42 p_TEMPLATE IN VARCHAR2,
43 p_TEMPLATE_FILE IN VARCHAR2,
44 p_EXECUTION_MODE IN VARCHAR2,
45 p_VARIABLE_NAME IN CSM_VARCHAR_LIST,
46 p_VARIABLE_TYPE IN CSM_VARCHAR_LIST,
47 p_VARIABLE_VALUE_CHAR IN CSM_VARCHAR_LIST,
48 p_VARIABLE_VALUE_DATE IN CSM_DATE_LIST,
49 p_HIDDEN_FLAG IN CSM_VARCHAR_LIST,
50 p_DEFAULT_FLAG IN CSM_VARCHAR_LIST,
51 p_EMAIL_ENABLED IN VARCHAR2,
52 p_RESTRICTED_FLAG IN VARCHAR2,
53 p_DISABLED_FLAG IN VARCHAR2,
54 x_return_status OUT NOCOPY VARCHAR2,
55 x_error_message OUT NOCOPY VARCHAR2
56 )
57 AS
58 BEGIN
59 CSM_UTIL_PKG.LOG
60 ( 'Entering INSERT_QUERY for Query Name :' || p_QUERY_NAME ,
61 FND_LOG.LEVEL_ERROR);
62
63 SAVEPOINT CSM_INSERT_QUERY_PUB;
64
65 CSM_QUERY_PKG.INSERT_QUERY
66 ( p_QUERY_ID => p_QUERY_ID,
67 p_QUERY_NAME => p_QUERY_NAME,
68 P_QUERY_DESC => p_QUERY_DESC,
69 P_QUERY_TYPE => p_QUERY_TYPE,
70 p_QUERY_TEXT1 => p_QUERY_TEXT1,
71 p_QUERY_TEXT2 => p_QUERY_TEXT2,
72 p_LEVEL_ID => p_LEVEL_ID,
73 p_LEVEL_VALUE => p_LEVEL_VALUE,
74 p_PARENT_QUERY_ID => p_PARENT_QUERY_ID,
75 p_SAVED_QUERY => p_SAVED_QUERY,
76 p_QUERY_OUTPUT_FORMAT => p_QUERY_OUTPUT_FORMAT,
77 p_MIME_TYPE => p_MIME_TYPE,
78 p_WORK_FLOW => p_WORK_FLOW,
79 p_PROCEDURE => p_PROCEDURE,
80 p_RETENTION_POLICY => p_RETENTION_POLICY,
81 p_RETENTION_DAYS => p_RETENTION_DAYS,
82 p_TEMPLATE => p_TEMPLATE,
83 p_TEMPLATE_FILE => p_TEMPLATE_FILE,
84 p_EXECUTION_MODE => p_EXECUTION_MODE,
85 p_VARIABLE_NAME => p_VARIABLE_NAME,
86 p_VARIABLE_TYPE => p_VARIABLE_TYPE,
87 p_VARIABLE_VALUE_CHAR => p_VARIABLE_VALUE_CHAR,
88 p_VARIABLE_VALUE_DATE => p_VARIABLE_VALUE_DATE,
89 p_HIDDEN_FLAG => p_HIDDEN_FLAG,
90 p_DEFAULT_FLAG => p_DEFAULT_FLAG,
91 p_EMAIL_ENABLED => p_EMAIL_ENABLED,
92 p_RESTRICTED_FLAG => p_RESTRICTED_FLAG,
93 p_DISABLED_FLAG => p_DISABLED_FLAG,
94 x_return_status => x_return_status,
95 x_error_message => x_error_message);
96
97
98 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
99 CSM_UTIL_PKG.LOG
100 ( 'Error occurred in Insert Query for Query Name ' || p_QUERY_NAME || SQLERRM, 'INSERT_QUERY',
101 FND_LOG.LEVEL_EXCEPTION);
102 x_return_status := FND_API.G_RET_STS_ERROR;
103 x_error_message := 'Query Insert failed : ' || x_error_message;
104 ROLLBACK TO CSM_INSERT_QUERY_PUB;
105 RETURN;
106 END IF;
107
108 IF FND_API.To_Boolean(p_COMMIT) THEN
109 COMMIT WORK;
110 END IF;
111 CSM_UTIL_PKG.LOG( 'Leaving INSERT_QUERY for Query Name :' || p_QUERY_NAME ,
112 FND_LOG.LEVEL_ERROR);
113 x_return_status := FND_API.G_RET_STS_SUCCESS;
114
115 EXCEPTION WHEN OTHERS THEN
116 /*** catch and log exceptions ***/
117 CSM_UTIL_PKG.LOG
118 ( 'Exception occurred in INSERT_QUERY for Query Name ' || p_QUERY_NAME || SQLERRM, 'INSERT_QUERY',
119 FND_LOG.LEVEL_EXCEPTION);
120 x_return_status := FND_API.G_RET_STS_ERROR;
121 x_error_message := 'Query insert failed for Query Name: ' || p_QUERY_NAME ||
122 ' : ' || SUBSTR(SQLERRM,1,3000);
123 ROLLBACK TO CSM_INSERT_QUERY_PUB;
124 END INSERT_QUERY;
125
126
127 /***
128 This procedure is called to Update a Query
129
130 ***/
131 PROCEDURE UPDATE_QUERY
132 (
133 p_API_VERSION IN NUMBER,
134 p_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
135 p_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
136 p_QUERY_ID IN NUMBER,
137 p_QUERY_NAME IN VARCHAR2,
138 P_QUERY_DESC IN VARCHAR2,
139 P_QUERY_TYPE IN VARCHAR2,
140 p_QUERY_TEXT1 IN VARCHAR2,
141 p_QUERY_TEXT2 IN VARCHAR2,
142 p_LEVEL_ID IN NUMBER,
143 p_LEVEL_VALUE IN NUMBER,
144 p_PARENT_QUERY_ID IN NUMBER,
145 p_SAVED_QUERY IN VARCHAR2,
146 p_QUERY_OUTPUT_FORMAT IN VARCHAR2,
147 p_MIME_TYPE IN VARCHAR2,
148 p_WORK_FLOW IN VARCHAR2,
149 p_PROCEDURE IN VARCHAR2,
150 p_RETENTION_POLICY IN VARCHAR2,
151 p_RETENTION_DAYS IN NUMBER,
152 p_TEMPLATE IN VARCHAR2,
153 p_TEMPLATE_FILE IN VARCHAR2,
154 p_EXECUTION_MODE IN VARCHAR2,
155 p_VARIABLE_NAME IN CSM_VARCHAR_LIST,
156 p_VARIABLE_TYPE IN CSM_VARCHAR_LIST,
157 p_VARIABLE_VALUE_CHAR IN CSM_VARCHAR_LIST,
158 p_VARIABLE_VALUE_DATE IN CSM_DATE_LIST,
159 p_HIDDEN_FLAG IN CSM_VARCHAR_LIST,
160 p_DEFAULT_FLAG IN CSM_VARCHAR_LIST,
161 p_EMAIL_ENABLED IN VARCHAR2,
162 p_RESTRICTED_FLAG IN VARCHAR2,
163 p_DISABLED_FLAG IN VARCHAR2,
164 x_return_status OUT NOCOPY VARCHAR2,
165 x_error_message OUT NOCOPY VARCHAR2
166 )
167 AS
168 BEGIN
169 CSM_UTIL_PKG.LOG
170 ( 'Entering UPDATE_QUERY for Query Id and Query Name : ' || p_QUERY_ID || ' : ' || p_QUERY_NAME ,
171 FND_LOG.LEVEL_ERROR);
172
173 SAVEPOINT CSM_UPDATE_QUERY_PUB;
174
175 CSM_QUERY_PKG.UPDATE_QUERY
176 ( p_QUERY_ID => p_QUERY_ID,
177 p_QUERY_NAME => p_QUERY_NAME,
178 P_QUERY_DESC => p_QUERY_DESC,
179 P_QUERY_TYPE => p_QUERY_TYPE,
180 p_QUERY_TEXT1 => p_QUERY_TEXT1,
181 p_QUERY_TEXT2 => p_QUERY_TEXT2,
182 p_LEVEL_ID => p_LEVEL_ID,
183 p_LEVEL_VALUE => p_LEVEL_VALUE,
184 p_PARENT_QUERY_ID => p_PARENT_QUERY_ID,
185 p_SAVED_QUERY => p_SAVED_QUERY,
186 p_QUERY_OUTPUT_FORMAT => p_QUERY_OUTPUT_FORMAT,
187 p_MIME_TYPE => p_MIME_TYPE,
188 p_WORK_FLOW => p_WORK_FLOW,
189 p_PROCEDURE => p_PROCEDURE,
190 p_RETENTION_POLICY => p_RETENTION_POLICY,
191 p_RETENTION_DAYS => p_RETENTION_DAYS,
192 p_TEMPLATE => p_TEMPLATE,
193 p_TEMPLATE_FILE => p_TEMPLATE_FILE,
194 p_EXECUTION_MODE => p_EXECUTION_MODE,
195 p_VARIABLE_NAME => p_VARIABLE_NAME,
196 p_VARIABLE_TYPE => p_VARIABLE_TYPE,
197 p_VARIABLE_VALUE_CHAR => p_VARIABLE_VALUE_CHAR,
198 p_VARIABLE_VALUE_DATE => p_VARIABLE_VALUE_DATE,
199 p_HIDDEN_FLAG => p_HIDDEN_FLAG,
200 p_DEFAULT_FLAG => p_DEFAULT_FLAG,
201 p_EMAIL_ENABLED => p_EMAIL_ENABLED,
202 p_RESTRICTED_FLAG => p_RESTRICTED_FLAG,
203 p_DISABLED_FLAG => p_DISABLED_FLAG,
204 x_return_status => x_return_status,
205 x_error_message => x_error_message);
206
207 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
208 CSM_UTIL_PKG.LOG
209 ( 'Exception occurred in UPDATE QUERY for Query Name ' || p_QUERY_NAME || SQLERRM, 'UPDATE_QUERY',
210 FND_LOG.LEVEL_EXCEPTION);
211 x_return_status := FND_API.G_RET_STS_ERROR;
212 x_error_message := 'Query Update failed : ' || x_error_message;
213 ROLLBACK TO CSM_UPDATE_QUERY_PUB;
214 RETURN;
215 END IF;
216
217 IF FND_API.To_Boolean(p_COMMIT) THEN
218 COMMIT WORK;
219 END IF;
220
221 CSM_UTIL_PKG.LOG
222 ( 'Leaving UPDATE_QUERY for Query Id and Query Name : ' || p_QUERY_ID || ' : ' || p_QUERY_NAME ,
223 FND_LOG.LEVEL_ERROR);
224 x_return_status := FND_API.G_RET_STS_SUCCESS;
225
226 EXCEPTION WHEN OTHERS THEN
227 /*** catch and log exceptions ***/
228 CSM_UTIL_PKG.LOG
229 ( 'Exception occurred in UPDATE_QUERY for Query Id and Query Name : ' || p_QUERY_ID || ' : ' || p_QUERY_NAME || SQLERRM, 'UPDATE_QUERY',
230 FND_LOG.LEVEL_EXCEPTION);
231 x_return_status := FND_API.G_RET_STS_ERROR;
232 x_error_message := 'Query Update Failed for Query Id and Query Name : ' || p_QUERY_ID || ' : ' || p_QUERY_NAME ||
236
233 ' : ' || SUBSTR(SQLERRM,1,3000);
234 ROLLBACK TO CSM_UPDATE_QUERY_PUB;
235 END UPDATE_QUERY;
237 /***
238 This procedure is called to Delete a Query
239 ***/
240 PROCEDURE DELETE_QUERY
241 (
242 p_API_VERSION IN NUMBER,
243 p_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
244 p_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
245 p_QUERY_ID IN NUMBER,
246 x_return_status OUT NOCOPY VARCHAR2,
247 x_error_message OUT NOCOPY VARCHAR2
248 )
249 AS
250 BEGIN
251 CSM_UTIL_PKG.LOG
252 ( 'Entering DELETE_QUERY for Query Id and Query Name : ' || p_QUERY_ID ,
253 FND_LOG.LEVEL_ERROR);
254
255 SAVEPOINT CSM_DELETE_QUERY_PUB;
256
257 CSM_QUERY_PKG.DELETE_QUERY
258 (
259 p_QUERY_ID => p_QUERY_ID,
260 x_return_status => x_return_status,
261 x_error_message => x_error_message
262 );
263
264 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
265 CSM_UTIL_PKG.LOG
266 ( 'Exception occurred in DELETE QUERY for Query Id ' || p_QUERY_ID || SQLERRM, 'DELETE_QUERY',
267 FND_LOG.LEVEL_EXCEPTION);
268 x_return_status := FND_API.G_RET_STS_ERROR;
269 x_error_message := 'Query Delete failed : ' || x_error_message;
270 ROLLBACK TO CSM_DELETE_QUERY_PUB;
271 RETURN;
272 END IF;
273
274 IF FND_API.To_Boolean(p_COMMIT) THEN
275 COMMIT WORK;
276 END IF;
277
278 CSM_UTIL_PKG.LOG
279 ( 'Leaving DELETE_QUERY for Query Id and Query Name : ' || p_QUERY_ID ,
280 FND_LOG.LEVEL_ERROR);
281 x_return_status := FND_API.G_RET_STS_SUCCESS;
282 x_error_message := 'Delete Query Call Successful for Query id '|| p_QUERY_ID;
283 EXCEPTION WHEN OTHERS THEN
284 /*** catch and log exceptions ***/
285 CSM_UTIL_PKG.LOG
286 ( 'Exception occurred in DELETE_QUERY for Query Id and Query Name : ' || p_QUERY_ID || SQLERRM, 'DELETE_QUERY',
287 FND_LOG.LEVEL_EXCEPTION);
288 x_return_status := FND_API.G_RET_STS_ERROR;
289 x_error_message := 'Query Delete Failed for Query Id and Query Name : ' || p_QUERY_ID ;
290 ROLLBACK TO CSM_DELETE_QUERY_PUB;
291 END DELETE_QUERY;
292
293 /***
294 This procedure can be used to Execute a given query
295 ***/
296 PROCEDURE EXECUTE_QUERY
297 ( p_API_VERSION IN NUMBER,
298 p_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
299 p_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
300 p_USER_ID IN NUMBER,
301 p_QUERY_ID IN NUMBER,
302 p_INSTANCE_ID IN NUMBER,
303 x_return_status OUT NOCOPY VARCHAR2,
304 x_error_message OUT NOCOPY VARCHAR2
305 )
306 AS
307 BEGIN
308 CSM_UTIL_PKG.LOG
309 ( 'Entering EXECUTE_QUERY for Query Id and Query Name : ' || p_QUERY_ID ,
310 FND_LOG.LEVEL_ERROR);
311 --Query Execution status update
312 SAVEPOINT CSM_EXECUTE_QUERY_PUB;
313
314 CSM_QUERY_PKG.EXECUTE_QUERY
315 (
316 p_USER_ID => p_USER_ID,
317 p_QUERY_ID => p_QUERY_ID,
318 p_INSTANCE_ID => p_INSTANCE_ID,
319 x_return_status => x_return_status,
320 x_error_message => x_error_message,
321 p_commit => p_COMMIT
322 );
323
324 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
325 CSM_UTIL_PKG.LOG
326 ( 'Exception occurred in QUERY Execution for Instance ID ' || p_INSTANCE_ID || SUBSTR(SQLERRM,1,3000), 'EXECUTE_QUERY',
327 FND_LOG.LEVEL_EXCEPTION);
328 x_return_status := FND_API.G_RET_STS_ERROR;
329 x_error_message := 'Query Execution failed : ' || x_error_message;
330 ROLLBACK TO CSM_EXECUTE_QUERY_PUB;
331 RETURN;
332 END IF;
333
334 IF FND_API.To_Boolean(p_COMMIT) THEN
335 COMMIT WORK;
336 END IF;
337
338 CSM_UTIL_PKG.LOG
339 ( 'Leaving EXECUTE_QUERY for Query Id and Instance Id after successfully Executing :
340 ' || p_QUERY_ID || '-' || p_INSTANCE_ID ,
341 FND_LOG.LEVEL_ERROR);
342 x_return_status := FND_API.G_RET_STS_SUCCESS;
343 x_error_message := 'Query Execution Successful ';
344 EXCEPTION WHEN OTHERS THEN
345 /*** catch and log exceptions ***/
346
347 CSM_UTIL_PKG.LOG
348 ( 'Exception occurred in EXECUTE_QUERY for Instance id : ' || p_INSTANCE_ID || SUBSTR(SQLERRM,1,3000), 'EXECUTE_QUERY',
349 FND_LOG.LEVEL_EXCEPTION);
350 x_return_status := FND_API.G_RET_STS_ERROR;
351 x_error_message := 'Query Execution Failed With Message : ' || SUBSTR(SQLERRM,1,3000) ;
352 ROLLBACK TO CSM_EXECUTE_QUERY_PUB;
353 END EXECUTE_QUERY;
354
355 --Procedure to Create a Instance for a Given Query and store in the Acc table
356
357 PROCEDURE INSERT_INSTANCE
358 ( p_API_VERSION IN NUMBER,
359 p_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
360 p_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
361 p_USER_ID IN NUMBER,
362 p_QUERY_ID IN NUMBER,
363 p_INSTANCE_ID IN VARCHAR2 DEFAULT NULL,
364 p_INSTANCE_NAME IN VARCHAR2,
365 p_VARIABLE_ID IN CSM_INTEGER_LIST,
366 p_VARIABLE_VALUE_CHAR IN CSM_VARCHAR_LIST,
367 p_VARIABLE_VALUE_DATE IN CSM_DATE_LIST,
368 x_INSTANCE_ID OUT NOCOPY NUMBER,
369 x_return_status OUT NOCOPY VARCHAR2,
370 x_error_message OUT NOCOPY VARCHAR2
371 )
372 AS
373 BEGIN
374 CSM_UTIL_PKG.LOG
375 ( 'Entering INSERT_INSTANCE for Query Id : ' || p_QUERY_ID ,
376 FND_LOG.LEVEL_ERROR);
377
378 SAVEPOINT CSM_INSERT_INSTANCE_PUB;
379
380 CSM_QUERY_PKG.INSERT_INSTANCE
381 ( p_USER_ID => p_USER_ID,
382 p_QUERY_ID => p_QUERY_ID,
383 p_INSTANCE_ID => p_INSTANCE_ID,
384 p_INSTANCE_NAME => p_INSTANCE_NAME,
385 p_VARIABLE_ID => p_VARIABLE_ID,
386 p_VARIABLE_VALUE_CHAR => p_VARIABLE_VALUE_CHAR,
387 p_VARIABLE_VALUE_DATE => p_VARIABLE_VALUE_DATE,
388 p_commit => p_COMMIT,
389 x_INSTANCE_ID => x_INSTANCE_ID,
390 x_return_status => x_return_status,
391 x_error_message => x_error_message
392 );
393
394 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
395 CSM_UTIL_PKG.LOG
396 ( 'Exception occurred for Instance ID ' || p_INSTANCE_ID || SUBSTR(SQLERRM,1,3000), 'INSERT_INSTANCE',
397 FND_LOG.LEVEL_EXCEPTION);
398 x_return_status := FND_API.G_RET_STS_ERROR;
399 x_error_message := 'Insert Instance failed : ' || x_error_message;
400 ROLLBACK TO CSM_INSERT_INSTANCE_PUB;
401 RETURN;
402 END IF;
403
404 IF FND_API.To_Boolean(p_COMMIT) THEN
405 COMMIT WORK;
406 END IF;
407
408 CSM_UTIL_PKG.LOG
409 ( 'Leaving INSERT_INSTANCE for Instance Id : ' || x_INSTANCE_ID ,
410 FND_LOG.LEVEL_ERROR);
411 x_return_status := FND_API.G_RET_STS_SUCCESS;
412 x_error_message := 'Insert Successful for Instance Id : ' || x_INSTANCE_ID ;
413
414 EXCEPTION WHEN OTHERS THEN
415 /*** catch and log exceptions ***/
416 CSM_UTIL_PKG.LOG
417 ( 'Exception occurred in INSERT_INSTANCE for Query Id : ' || p_QUERY_ID || SQLERRM, 'EXECUTE_QUERY',
418 FND_LOG.LEVEL_EXCEPTION);
419 x_return_status := FND_API.G_RET_STS_ERROR;
420 x_error_message := 'Exception occurred in INSERT_INSTANCE With Message : ' || SUBSTR(SQLERRM,1,3000) ;
421 ROLLBACK TO CSM_INSERT_INSTANCE_PUB;
422 END INSERT_INSTANCE;
423
424 --Procedure to Delete a Instance for a Given Query
425 PROCEDURE DELETE_INSTANCE
426 ( p_API_VERSION IN NUMBER,
427 p_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
428 p_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
429 p_USER_ID IN NUMBER,
430 p_QUERY_ID IN NUMBER,
431 p_INSTANCE_ID IN NUMBER,
432 x_return_status OUT NOCOPY VARCHAR2,
433 x_error_message OUT NOCOPY VARCHAR2
434 )
435 AS
436 BEGIN
437 CSM_UTIL_PKG.LOG
438 ( 'Entering DELETE_INSTANCE for Instance Id : ' || p_INSTANCE_ID ,
439 FND_LOG.LEVEL_ERROR);
440
441 SAVEPOINT CSM_DELETE_INSTANCE_PUB;
442
443 CSM_QUERY_PKG.DELETE_INSTANCE
444 ( p_USER_ID => p_USER_ID,
445 p_QUERY_ID => p_QUERY_ID,
446 p_INSTANCE_ID => p_INSTANCE_ID,
447 p_commit => p_COMMIT,
448 x_return_status => x_return_status,
449 x_error_message => x_error_message
450 );
451
452 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
453 CSM_UTIL_PKG.LOG
454 ( 'Exception occurred for Instance ID ' || p_INSTANCE_ID || SUBSTR(SQLERRM,1,3000), 'DELETE_INSTANCE',
455 FND_LOG.LEVEL_EXCEPTION);
456 x_return_status := FND_API.G_RET_STS_ERROR;
457 x_error_message := 'Delete Instance failed : ' || x_error_message;
458 ROLLBACK TO CSM_DELETE_INSTANCE_PUB;
459 RETURN;
460 END IF;
461
462 IF FND_API.To_Boolean(p_COMMIT) THEN
463 COMMIT WORK;
464 END IF;
465
466 CSM_UTIL_PKG.LOG
467 ( 'Leaving DELETE_INSTANCE for Query Id and Query Name : ' || p_QUERY_ID ,
468 FND_LOG.LEVEL_ERROR);
469 x_return_status := FND_API.G_RET_STS_SUCCESS;
470 x_error_message := 'Delete call Successful for Instance Id : ' || p_INSTANCE_ID ;
471
472 EXCEPTION WHEN OTHERS THEN
473 /*** catch and log exceptions ***/
474 CSM_UTIL_PKG.LOG
475 ( 'Exception occurred in DELETE_INSTANCE for Query Id : ' || p_QUERY_ID || SQLERRM, 'DELETE_INSTANCE',
476 FND_LOG.LEVEL_EXCEPTION);
477 x_return_status := FND_API.G_RET_STS_ERROR;
478 x_error_message := 'Exception occurred in DELETE_INSTANCE With Message : ' || SUBSTR(SQLERRM,1,3000) ;
479 ROLLBACK TO CSM_DELETE_INSTANCE_PUB;
480 END DELETE_INSTANCE;
481
482
483 --Public Procedure to Insert a Result once a Given Query is executed by Custom code
484
485 PROCEDURE INSERT_RESULT
486 ( p_API_VERSION IN NUMBER,
487 p_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
488 p_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
489 p_USER_ID IN NUMBER,
490 p_QUERY_ID IN NUMBER,
491 p_INSTANCE_ID IN VARCHAR2 DEFAULT NULL,
492 p_QUERY_RESULT IN BLOB,
493 x_return_status OUT NOCOPY VARCHAR2,
494 x_error_message OUT NOCOPY VARCHAR2
495 )
496 AS
497 BEGIN
498 CSM_UTIL_PKG.LOG
499 ( 'Entering INSERT_RESULT for Instance Id : ' || p_INSTANCE_ID ,
500 FND_LOG.LEVEL_ERROR);
501
502 SAVEPOINT CSM_INSERT_RESULT_PUB;
503
504 CSM_QUERY_PKG.INSERT_RESULT
505 ( p_USER_ID => p_USER_ID,
506 p_QUERY_ID => p_QUERY_ID,
507 p_INSTANCE_ID => p_INSTANCE_ID,
508 p_QUERY_RESULT => p_QUERY_RESULT,
509 p_commit => p_COMMIT,
510 x_return_status => x_return_status,
511 x_error_message => x_error_message
512 );
513
514 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
515 CSM_UTIL_PKG.LOG
516 ( 'Exception occurred for Instance ID ' || p_INSTANCE_ID || SUBSTR(SQLERRM,1,3000), 'INSERT_RESULT',
517 FND_LOG.LEVEL_EXCEPTION);
518 x_return_status := FND_API.G_RET_STS_ERROR;
519 x_error_message := 'Insert Result for Instance failed : ' || x_error_message;
520 ROLLBACK TO CSM_INSERT_RESULT_PUB;
521 RETURN;
522 END IF;
523
524 IF FND_API.To_Boolean(p_COMMIT) THEN
525 COMMIT WORK;
526 END IF;
527
528 CSM_UTIL_PKG.LOG
529 ( 'Leaving INSERT_RESULT for Instance ID : ' || p_INSTANCE_ID ,
530 FND_LOG.LEVEL_ERROR);
531 x_return_status := FND_API.G_RET_STS_SUCCESS;
532 x_error_message := 'Insert Result Successful for Instance Id : ' || p_INSTANCE_ID ;
533
534 EXCEPTION WHEN OTHERS THEN
535 /*** catch and log exceptions ***/
536 CSM_UTIL_PKG.LOG
537 ( 'Exception occurred in INSERT_RESULT for Instance ID : ' || p_INSTANCE_ID || SQLERRM, 'INSERT_RESULT',
538 FND_LOG.LEVEL_EXCEPTION);
539 x_return_status := FND_API.G_RET_STS_ERROR;
540 x_error_message := 'Exception occurred in INSERT_RESULT With Message : ' || SUBSTR(SQLERRM,1,3000) ;
541 ROLLBACK TO CSM_INSERT_RESULT_PUB;
542 END INSERT_RESULT;
543
544 END CSM_QUERY_PUB; -- Package spec