[Home] [Help]
Skip to content
PACKAGE BODY: APPS.CSM_MOBILE_QUERY_PKG
Source
1 PACKAGE BODY CSM_MOBILE_QUERY_PKG AS
2 /* $Header: csmuqryb.pls 120.5 2009/10/22 04:00:18 trajasek noship $ */
3
4 error EXCEPTION;
5
6
7 /*** Globals ***/
8 g_object_name CONSTANT VARCHAR2(30) := 'CSM_MOBILE_QUERY_PKG';
9 g_pub_name CONSTANT VARCHAR2(30) := 'CSM_QUERY';
10 g_query_var_pub_name CONSTANT VARCHAR2(30) := 'CSM_QUERY_VARIABLES';
11
12 g_debug_level NUMBER; -- debug level
13
14 /* Select all inq records */
15 CURSOR c_query( b_user_name VARCHAR2, b_tranid NUMBER)
16 IS
17 SELECT *
18 FROM CSM_QUERY_INQ
19 WHERE tranid$$ = b_tranid
20 AND clid$$cs = b_user_name;
21
22 CURSOR c_query_var_inq( b_user_name VARCHAR2, b_tranid NUMBER, b_query_id NUMBER)
23 IS
24 SELECT *
25 FROM CSM_QUERY_VARIABLES_INQ
26 WHERE tranid$$ = b_tranid
27 AND clid$$cs = b_user_name
28 AND QUERY_ID = b_query_id;
29
30 /***
31 This procedure is called Upload to insert a Saved Queries
32 That is created in the Mobile Client
33 ***/
34 PROCEDURE INSERT_QUERY
35 ( p_QUERY_ID IN NUMBER,
36 p_QUERY_NAME IN VARCHAR2,
37 P_QUERY_DESC IN VARCHAR2,
38 P_QUERY_TYPE IN VARCHAR2,
39 p_QUERY_TEXT1 IN VARCHAR2,
40 p_QUERY_TEXT2 IN VARCHAR2,
41 p_LEVEL_ID IN NUMBER,
42 p_LEVEL_VALUE IN NUMBER,
43 p_PARENT_QUERY_ID IN NUMBER,
44 p_SAVED_QUERY IN VARCHAR2,
45 p_QUERY_OUTPUT_FORMAT IN VARCHAR2,
46 p_MIME_TYPE IN VARCHAR2,
47 p_WORK_FLOW IN VARCHAR2,
48 p_RETENTION_POLICY IN VARCHAR2,
49 p_RETENTION_DAYS IN NUMBER,
50 p_TEMPLATE IN VARCHAR2,
51 p_TEMPLATE_FILE IN VARCHAR2,
52 p_EXECUTION_MODE IN VARCHAR2,
53 p_VARIABLE_NAME IN CSM_VARCHAR_LIST,
54 p_VARIABLE_TYPE IN CSM_VARCHAR_LIST,
55 p_VARIABLE_VALUE_CHAR IN CSM_VARCHAR_LIST,
56 p_VARIABLE_VALUE_DATE IN CSM_DATE_LIST,
57 p_HIDDEN_FLAG IN CSM_VARCHAR_LIST,
58 p_DEFAULT_FLAG IN CSM_VARCHAR_LIST,
59 x_return_status OUT NOCOPY VARCHAR2,
60 x_error_message OUT NOCOPY VARCHAR2
61 )
62 AS
63 CURSOR c_get_query_id --get query_id
64 IS
65 SELECT CSM_QUERY_B_S.NEXTVAL
66 FROM DUAL;
67
68 CURSOR c_get_resp_id (c_parent_query_id NUMBER)
69 IS
70 SELECT LEVEL_ID, LEVEL_VALUE
71 FROM CSM_QUERY_B
72 WHERE QUERY_ID = c_parent_query_id;
73
74 l_query_id NUMBER;
75 l_variable_count NUMBER;
76 l_return_status VARCHAR2(4000);
77 l_return_message VARCHAR2(4000);
78 l_language VARCHAR2(10) := 'US' ;
79 l_qry_with_no_var VARCHAR2(1) := 'N';
80 l_level_value NUMBER := NULL;
81 l_level_id NUMBER;
82 l_responsibility_id NUMBER;
83 BEGIN
84 CSM_UTIL_PKG.LOG
85 ( 'Entering INSERT_QUERY for Query Name :' || p_QUERY_NAME ,
86 FND_LOG.LEVEL_ERROR);
87 --Check if the user has valid access to responsibility
88 OPEN c_get_resp_id (p_PARENT_QUERY_ID);
89 FETCH c_get_resp_id INTO l_level_id, l_level_value;
90 CLOSE c_get_resp_id ;
91
92 IF l_level_id = 10003 THEN
93 l_responsibility_id := l_level_value;
94 END IF;
95
96 --Do the Validation only if query type is SQL
97 IF UPPER(P_QUERY_TYPE) ='SQL' THEN
98 --Validate the Access to a Query
99 CSM_QUERY_PKG.VALIDATE_ACCESS( p_QUERY_ID => NULL,
100 p_QUERY_TEXT1 => p_QUERY_TEXT1,
101 p_QUERY_TEXT2 => p_QUERY_TEXT2,
102 p_RESPONSIBILITY_ID => l_responsibility_id,
103 x_return_status => l_return_status,
104 x_error_message => l_return_message);
105
106 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
107 CSM_UTIL_PKG.LOG
108 ( 'Exception occurred in VALIDATE_ACCESS for Query Name ' || p_QUERY_NAME || SQLERRM, 'INSERT_QUERY',
109 FND_LOG.LEVEL_EXCEPTION);
110 x_return_status := FND_API.G_RET_STS_ERROR;
111 x_error_message := 'Query Validation failed : ' || l_return_message;
112 RETURN;
113 END IF;
114 --Validate the Query
115 CSM_QUERY_PKG.VALIDATE_QUERY( p_QUERY_ID => NULL,
116 p_QUERY_TEXT1 => p_QUERY_TEXT1,
117 p_QUERY_TEXT2 => p_QUERY_TEXT2,
118 x_return_status => l_return_status,
119 x_error_message => l_return_message);
120
121 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
122 CSM_UTIL_PKG.LOG
123 ( 'Exception occurred in VALIDATE_QUERY for Query Name ' || p_QUERY_NAME || SQLERRM, 'INSERT_QUERY',
124 FND_LOG.LEVEL_EXCEPTION);
125 x_return_status := FND_API.G_RET_STS_ERROR;
126 x_error_message := 'Query Validation failed : ' || l_return_message;
127 RETURN;
128 END IF;
129 ELSIF UPPER(P_QUERY_TYPE) ='WORKFLOW' THEN
130 CSM_QUERY_PKG.VALIDATE_WORKFLOW
131 ( p_QUERY_ID => p_QUERY_ID,
132 p_WORKFLOW => p_WORK_FLOW,
133 p_VARIABLE_NAME => p_VARIABLE_NAME,
134 x_return_status => l_return_status,
135 x_error_message => l_return_message
139 ( 'Exception occurred in VALIDATE_WORLFLOW for Query Name ' || p_QUERY_NAME || SQLERRM, 'INSERT_QUERY',
136 );
137 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
138 CSM_UTIL_PKG.LOG
140 FND_LOG.LEVEL_EXCEPTION);
141 x_return_status := FND_API.G_RET_STS_ERROR;
142 x_error_message := 'Query Validation failed : ' || l_return_message;
143 RETURN;
144 END IF;
145 END IF;
146
147 IF p_VARIABLE_NAME IS NOT NULL AND p_VARIABLE_TYPE IS NOT NULL THEN
148
149 IF p_VARIABLE_NAME.COUNT <> p_VARIABLE_TYPE.COUNT THEN
150 CSM_UTIL_PKG.LOG
151 ( 'Variable Name and Variable Type Mismatch: Leaving INSERT_QUERY for Query Name :' || p_QUERY_NAME ,
152 FND_LOG.LEVEL_ERROR);
153
154 x_return_status := FND_API.G_RET_STS_ERROR;
155 x_error_message := 'Query insert failed for Query Name: ' || p_QUERY_NAME ||
156 ' because of Variable Name and Variable Type Mismatch' ;
157 RETURN;
158 END IF;
159 ELSE
160 IF p_VARIABLE_NAME IS NULL AND p_VARIABLE_TYPE IS NULL THEN
161 l_qry_with_no_var := 'Y';
162 ELSE
163 CSM_UTIL_PKG.LOG
164 ( 'Variable Name and Variable Type Mismatch: Leaving INSERT_QUERY for Query Name :' || p_QUERY_NAME ,
165 FND_LOG.LEVEL_ERROR);
166
167 x_return_status := FND_API.G_RET_STS_ERROR;
168 x_error_message := 'Query insert failed for Query Name: ' || p_QUERY_NAME ||
169 ' because of Variable Name and Variable Type Mismatch' ;
170 RETURN;
171 END IF;
172
173 END IF;
174
175 --Get the query id from the sequence.
176 IF p_QUERY_ID IS NULL THEN
177 OPEN c_get_query_id;
178 FETCH c_get_query_id INTO l_query_id;
179 CLOSE c_get_query_id;
180 ELSE
181 l_query_id:= p_QUERY_ID;
182 END IF;
183 --Insert the Basic Query Definition into the base table
184 INSERT INTO CSM_QUERY_B ( QUERY_ID, QUERY_NAME, QUERY_TYPE,
185 QUERY_TEXT1, QUERY_TEXT2, LEVEL_ID,
186 LEVEL_VALUE, PARENT_QUERY_ID, SAVED_QUERY,
187 QUERY_OUTPUT_FORMAT, MIME_TYPE, WORK_FLOW,
188 RETENTION_POLICY, RETENTION_DAYS, TEMPLATE,
189 TEMPLATE_FILE, EXECUTION_MODE, CREATION_DATE,
190 CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY,
191 LAST_UPDATE_LOGIN, SEEDED )
192 VALUES ( l_query_id, p_QUERY_NAME, p_QUERY_TYPE,
193 p_QUERY_TEXT1, p_QUERY_TEXT2, p_LEVEL_ID,
194 p_LEVEL_VALUE, p_PARENT_QUERY_ID, p_SAVED_QUERY,
195 p_QUERY_OUTPUT_FORMAT, p_MIME_TYPE, p_WORK_FLOW,
196 p_RETENTION_POLICY, p_RETENTION_DAYS, p_TEMPLATE,
197 p_TEMPLATE_FILE, p_EXECUTION_MODE, sysdate,
198 p_LEVEL_VALUE, sysdate, p_LEVEL_VALUE,
199 fnd_global.login_id, 'N' );
200
201 --Insert the QUERY DESCRIPTION into the TL table
202 INSERT INTO CSM_QUERY_TL ( QUERY_ID, DESCRIPTION, LANGUAGE,
203 CREATION_DATE, CREATED_BY,
204 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN )
205 VALUES ( l_query_id, P_QUERY_DESC, l_language,
206 sysdate, p_LEVEL_VALUE,
207 sysdate, p_LEVEL_VALUE, fnd_global.login_id);
208
209 IF l_qry_with_no_var = 'N' THEN --Proces only if the query has variables
210 l_variable_count := p_VARIABLE_NAME.COUNT;
211
212 FOR i in 1..l_variable_count LOOP
213 --Insert the Query Variable Definition into the base table
214 INSERT INTO CSM_QUERY_VARIABLES_B ( QUERY_ID, VARIABLE_ID, VARIABLE_NAME,
215 VARIABLE_TYPE, VARIABLE_VALUE_CHAR, VARIABLE_VALUE_DATE,
216 HIDDEN, DEFAULT_FLAG, CREATION_DATE,
217 CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY,
218 LAST_UPDATE_LOGIN )
219 VALUES ( l_query_id, i, p_VARIABLE_NAME(i),
220 p_VARIABLE_TYPE(i), p_VARIABLE_VALUE_CHAR(i), p_VARIABLE_VALUE_DATE(i),
221 NVL(p_HIDDEN_FLAG(i),'N'), NVL(p_DEFAULT_FLAG(i),'N'), sysdate,
222 p_LEVEL_VALUE, sysdate, p_LEVEL_VALUE,
223 fnd_global.login_id );
224
225 END LOOP;
226 END IF;
227
228 CSM_UTIL_PKG.LOG( 'Leaving INSERT_QUERY for Query Name :' || p_QUERY_NAME ,
229 FND_LOG.LEVEL_ERROR);
230 x_error_message := 'Query insert is Successful for Query Name: ' || p_QUERY_NAME ;
231 x_return_status := FND_API.G_RET_STS_SUCCESS;
232
233 EXCEPTION WHEN OTHERS THEN
234 /*** catch and log exceptions ***/
235 CSM_UTIL_PKG.LOG
236 ( 'Exception occurred in INSERT_QUERY for Query Name ' || p_QUERY_NAME || SQLERRM, 'INSERT_QUERY',
237 FND_LOG.LEVEL_EXCEPTION);
238 x_return_status := FND_API.G_RET_STS_ERROR;
239 x_error_message := 'Query insert failed for Query Name: ' || p_QUERY_NAME ||
240 ' : ' || SUBSTR(SQLERRM,1,3000);
241 RETURN;
242 END INSERT_QUERY;
243
247 PROCEDURE APPLY_INSERT
244 /***
245 This procedure is called by APPLY_CLIENT_CHANGES when an inserted record is to be processed.
246 ***/
248 (
249 p_record IN c_query%ROWTYPE,
250 p_error_msg out nocopy VARCHAR2,
251 x_return_status IN out nocopy VARCHAR2
252 )
253 IS
254 CURSOR c_check_query(c_QUERY_ID NUMBER)
255 IS
256 SELECT QUERY_NAME, QUERY_TYPE, QUERY_TEXT1, QUERY_TEXT2,
257 QUERY_OUTPUT_FORMAT, MIME_TYPE, WORK_FLOW, RETENTION_POLICY,
258 RETENTION_DAYS, TEMPLATE, TEMPLATE_FILE, EXECUTION_MODE
259 FROM CSM_QUERY_B
260 WHERE QUERY_ID =c_QUERY_ID
261 AND NVL(DELETE_FLAG,'N') = 'N';
262
263 CURSOR c_get_variables (c_QUERY_ID NUMBER)
264 IS
265 SELECT
266 vb.VARIABLE_NAME,
267 vb.VARIABLE_TYPE,
268 vb.VARIABLE_VALUE_CHAR,
269 vb.VARIABLE_VALUE_DATE,
270 vb.HIDDEN,
271 vb.DEFAULT_FLAG
272 FROM CSM_QUERY_VARIABLES_INQ vb
273 WHERE vb.QUERY_ID = c_QUERY_ID;
274
275 CURSOR c_get_user(c_user_name VARCHAR2)
276 IS
277 SELECT USER_ID
278 FROM ASG_USER
279 WHERE USER_NAME =c_user_name;
280 --Variable Declarations
281 l_variable_name_lst CSM_VARCHAR_LIST;
282 l_var_type_lst CSM_VARCHAR_LIST;
283 l_var_value_char_lst CSM_VARCHAR_LIST;
284 l_var_value_date_lst CSM_DATE_LIST;
285 l_var_hidden_lst CSM_VARCHAR_LIST;
286 l_var_default_lst CSM_VARCHAR_LIST;
287 l_user_id NUMBER;
288 l_QUERY_ID NUMBER;
289 l_QUERY_NAME VARCHAR2(255);
290 l_QUERY_TYPE VARCHAR2(255);
291 l_QUERY_TEXT1 VARCHAR2(4000);
292 l_QUERY_TEXT2 VARCHAR2(4000);
293 l_LEVEL_ID NUMBER;
294 l_LEVEL_VALUE NUMBER;
295 l_PARENT_QUERY_ID NUMBER;
296 l_SAVED_QUERY VARCHAR2(1);
297 l_QUERY_OUTPUT_FORMAT VARCHAR2(255);
298 l_MIME_TYPE VARCHAR2(255);
299 l_WORK_FLOW VARCHAR2(255);
300 l_RETENTION_POLICY VARCHAR2(30);
301 l_RETENTION_DAYS NUMBER;
302 l_TEMPLATE VARCHAR2(30);
303 l_TEMPLATE_FILE VARCHAR2(255);
304 l_EXECUTION_MODE VARCHAR2(30);
305 --x_return_status VARCHAR2(255);
306 --p_error_msg VARCHAR2(4000);
307 BEGIN
308
309 x_return_status := FND_API.G_RET_STS_SUCCESS;
310 CSM_UTIL_PKG.LOG('Entering CSM_MOBILE_QUERY_PKG.APPLY_INSERT for Query ID ' || p_record.QUERY_ID ,
311 'CSM_MOBILE_QUERY_PKG.APPLY_INSERT',FND_LOG.LEVEL_PROCEDURE);
312 l_PARENT_QUERY_ID := p_record.PARENT_QUERY_ID;
313 l_query_id := p_record.QUERY_ID;
314
315 IF l_PARENT_QUERY_ID IS NULL THEN
316 CSM_UTIL_PKG.LOG( 'Parent Query Id is Invalid for Query Id: ' || l_query_id ,FND_LOG.LEVEL_ERROR);
317 p_error_msg := 'Parent Query Id is Invalid for Query Id : ' || l_query_id ;
318 x_return_status := FND_API.G_RET_STS_ERROR;
319 RETURN;
320 END IF;
321
322 --Check if the Query id is valid
323 OPEN c_check_query(l_PARENT_QUERY_ID);
324 FETCH c_check_query INTO l_QUERY_NAME, l_QUERY_TYPE, l_QUERY_TEXT1, l_QUERY_TEXT2,
325 l_QUERY_OUTPUT_FORMAT, l_MIME_TYPE, l_WORK_FLOW, l_RETENTION_POLICY,
326 l_RETENTION_DAYS,l_TEMPLATE, l_TEMPLATE_FILE, l_EXECUTION_MODE;
327
328 IF c_check_query%NOTFOUND THEN
329 CSM_UTIL_PKG.LOG( 'Parent Query Id is Invalid for Query Id: ' || l_query_id ,FND_LOG.LEVEL_ERROR);
330 p_error_msg := 'Parent Query Id is Invalid for Query Id : ' || l_query_id ;
331 CLOSE c_check_query;
332 x_return_status := FND_API.G_RET_STS_ERROR;
333 RETURN;
334 END IF;
335 CLOSE c_check_query;
336 --get user_id
337 OPEN c_get_user(p_record.CLID$$CS);
338 FETCH c_get_user INTO l_user_id;
339 CLOSE c_get_user;
340
341 --Set the parameters for the Insert Query from INQ
342 l_QUERY_ID := p_record.QUERY_ID;
343 l_PARENT_QUERY_ID := p_record.PARENT_QUERY_ID;
344 l_QUERY_NAME := NVL(p_record.QUERY_NAME,l_QUERY_NAME);
345 l_QUERY_TYPE := NVL(p_record.QUERY_TYPE,l_QUERY_TYPE);
346 l_LEVEL_ID := 10004; --For user level
347 l_LEVEL_VALUE := l_user_id;
348 l_SAVED_QUERY := 'Y'; --from MFS Client;
349 l_QUERY_OUTPUT_FORMAT := NVL(p_record.QUERY_TYPE,l_QUERY_TYPE);
350 l_MIME_TYPE := NVL(p_record.MIME_TYPE,l_MIME_TYPE);
351 l_WORK_FLOW := NVL(p_record.WORK_FLOW,l_WORK_FLOW);
352 l_RETENTION_POLICY := NVL(p_record.RETENTION_POLICY,l_RETENTION_POLICY);
353 l_RETENTION_DAYS := NVL(p_record.RETENTION_DAYS,l_RETENTION_DAYS);
354 l_TEMPLATE := NVL(p_record.TEMPLATE,l_TEMPLATE);
355 l_TEMPLATE_FILE := NVL(p_record.TEMPLATE_FILE,l_TEMPLATE_FILE);
356 l_EXECUTION_MODE := NVL(p_record.EXECUTION_MODE,l_EXECUTION_MODE);
357
358 OPEN c_get_variables (l_QUERY_ID);
359 FETCH c_get_variables BULK COLLECT INTO l_variable_name_lst, l_var_type_lst, l_var_value_char_lst, l_var_value_date_lst,
360 l_var_hidden_lst, l_var_default_lst;
361 CLOSE c_get_variables;
362
363 INSERT_QUERY
364 ( p_QUERY_ID => l_QUERY_ID,
365 p_QUERY_NAME => l_QUERY_NAME,
366 P_QUERY_DESC => p_record.DESCRIPTION,
367 P_QUERY_TYPE => l_QUERY_TYPE,
368 p_QUERY_TEXT1 => l_QUERY_TEXT1,
369 p_QUERY_TEXT2 => l_QUERY_TEXT2,
370 p_LEVEL_ID => l_LEVEL_ID,
371 p_LEVEL_VALUE => l_LEVEL_VALUE,
372 p_PARENT_QUERY_ID => l_PARENT_QUERY_ID,
373 p_SAVED_QUERY => l_SAVED_QUERY,
374 p_QUERY_OUTPUT_FORMAT => l_QUERY_OUTPUT_FORMAT,
378 p_RETENTION_DAYS => l_RETENTION_DAYS,
375 p_MIME_TYPE => l_MIME_TYPE,
376 p_WORK_FLOW => l_WORK_FLOW,
377 p_RETENTION_POLICY => l_RETENTION_POLICY,
379 p_TEMPLATE => l_TEMPLATE,
380 p_TEMPLATE_FILE => l_TEMPLATE_FILE,
381 p_EXECUTION_MODE => l_EXECUTION_MODE,
382 p_VARIABLE_NAME => l_variable_name_lst,
383 p_VARIABLE_TYPE => l_var_type_lst,
384 p_VARIABLE_VALUE_CHAR => l_var_value_char_lst,
385 p_VARIABLE_VALUE_DATE => l_var_value_date_lst,
386 p_HIDDEN_FLAG => l_var_hidden_lst,
387 p_DEFAULT_FLAG => l_var_default_lst,
388 x_return_status => x_return_status,
389 x_error_message => p_error_msg);
390
391
392 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
393 CSM_UTIL_PKG.LOG( 'Query Upload Failed for Query ID : ' || l_query_id ,FND_LOG.LEVEL_ERROR);
394 p_error_msg := 'Query Upload Failed for Query ID : '|| l_query_id || ' with error ' || p_error_msg;
395 x_return_status := FND_API.G_RET_STS_ERROR;
396 RETURN;
397 END IF;
398
399 CSM_UTIL_PKG.LOG('Leaving CSM_MOBILE_QUERY_PKG.APPLY_INSERT for Query ID : ' || p_record.QUERY_ID ,
400 'CSM_MOBILE_QUERY_PKG.APPLY_INSERT',FND_LOG.LEVEL_PROCEDURE);
401 p_error_msg := 'Query Upload is Successful for Query ID : '|| l_query_id;
402 x_return_status := FND_API.G_RET_STS_SUCCESS;
403
404
405 EXCEPTION WHEN OTHERS THEN
406 p_error_msg := 'Exeception in Query Upload for Query Id : '|| l_query_id ||' with Error ' ||SUBSTR(SQLERRM,1,3000);
407 CSM_UTIL_PKG.log( 'Exception in ' || g_object_name || '.APPLY_INSERT: ' || SUBSTR(SQLERRM,1,3000)
408 || ' for Query ID ' || p_record.QUERY_ID ,'CSM_MOBILE_QUERY_PKG.APPLY_INSERT',FND_LOG.LEVEL_EXCEPTION);
409 x_return_status := FND_API.G_RET_STS_ERROR;
410 END APPLY_INSERT;
411
412 /***
413 This procedure is called by APPLY_CLIENT_CHANGES when an inserted record is to be processed.
414 ***/
415 PROCEDURE APPLY_DELETE
416 (
417 p_record IN c_query%ROWTYPE,
418 p_error_msg out nocopy VARCHAR2,
419 x_return_status IN out nocopy VARCHAR2
420 )
421 IS
422 CURSOR c_check_query(c_QUERY_ID NUMBER, c_USER_ID NUMBER)
423 IS
424 SELECT NVL(DELETE_FLAG,'N')
425 FROM CSM_QUERY_B
426 WHERE QUERY_ID = c_QUERY_ID
427 AND LEVEL_ID = 10004 --user level
428 AND LEVEL_VALUE = c_USER_ID
429 AND SAVED_QUERY = 'Y'
430 AND NVL(DELETE_FLAG,'N') = 'N';
431
432
433 CURSOR c_get_user(c_user_name VARCHAR2)
434 IS
435 SELECT USER_ID
436 FROM ASG_USER
437 WHERE USER_NAME =c_user_name;
438
439 l_DELETE_FLAG VARCHAR2(1) := NULL;
440 l_USER_ID NUMBER := NULL;
441 l_QUERY_ID NUMBER;
442 BEGIN
443
444 CSM_UTIL_PKG.LOG('Entering CSM_MOBILE_QUERY_PKG.APPLY_DELETE for Query ID ' || p_record.QUERY_ID ,
445 'CSM_MOBILE_QUERY_PKG.APPLY_DELETE',FND_LOG.LEVEL_PROCEDURE);
446 l_query_id := p_record.QUERY_ID;
447
448 OPEN c_get_user(p_record.CLID$$CS);
449 FETCH c_get_user INTO l_USER_ID;
450 CLOSE c_get_user;
451
452 IF l_USER_ID IS NULL THEN
453 p_error_msg := 'The user is invalid.Please Check. User Name : '|| p_record.CLID$$CS;
454 x_return_status := FND_API.G_RET_STS_ERROR;
455 RETURN;
456 END IF;
457
458 OPEN c_check_query(l_QUERY_ID, l_USER_ID);
459 FETCH c_check_query INTO l_DELETE_FLAG;
460 CLOSE c_check_query;
461
462 IF l_DELETE_FLAG IS NULL THEN
463 p_error_msg := 'User may not have permission to Delete the Query or The Query is not available for Delete. Query ID : '|| l_query_id;
464 x_return_status := FND_API.G_RET_STS_ERROR;
465 RETURN;
466 END IF;
467 --Update the Query Definition in the base table
468 UPDATE CSM_QUERY_B
469 SET DELETE_FLAG = 'Y',
470 LAST_UPDATE_DATE = sysdate,
471 LAST_UPDATED_BY = fnd_global.user_id,
472 LAST_UPDATE_LOGIN = fnd_global.login_id
473 WHERE QUERY_ID = l_QUERY_ID;
474
475 CSM_UTIL_PKG.LOG('Leaving CSM_MOBILE_QUERY_PKG.APPLY_DELETE for Query ID : ' || p_record.QUERY_ID ,
476 'CSM_MOBILE_QUERY_PKG.APPLY_DELETE',FND_LOG.LEVEL_PROCEDURE);
477 p_error_msg := 'Query Delete is Successful for Query ID : '|| l_query_id;
478 x_return_status := FND_API.G_RET_STS_SUCCESS;
479
480
481 EXCEPTION WHEN OTHERS THEN
482 p_error_msg := 'Exeception in Query Delete for Query Id : '|| l_query_id ||' with Error ' ||SUBSTR(SQLERRM,1,3000);
483 CSM_UTIL_PKG.log( 'Exception in ' || g_object_name || '.APPLY_DELETE: ' || SUBSTR(SQLERRM,1,3000)
484 || ' for Query ID ' || p_record.QUERY_ID ,'CSM_MOBILE_QUERY_PKG.APPLY_DELETE',FND_LOG.LEVEL_EXCEPTION);
485 x_return_status := FND_API.G_RET_STS_ERROR;
486 END APPLY_DELETE;
487
488 /***
489 This procedure is called by APPLY_CLIENT_CHANGES for every record in in-queue that needs to be processed.
490 ***/
491 PROCEDURE APPLY_RECORD
492 (
493 p_record IN c_query%ROWTYPE,
494 p_error_msg out nocopy VARCHAR2,
495 x_return_status IN out nocopy VARCHAR2
496 ) IS
497 l_rc BOOLEAN;
498 l_access_id NUMBER;
499 BEGIN
500 /*** initialize return status and message list ***/
501 x_return_status := FND_API.G_RET_STS_SUCCESS;
502 FND_MSG_PUB.INITIALIZE;
503
504 CSM_UTIL_PKG.LOG('Entering CSM_MOBILE_QUERY_PKG.APPLY_RECORD for Query ID ' || p_record.QUERY_ID ,
508 -- Process insert
505 'CSM_MOBILE_QUERY_PKG.APPLY_RECORD',FND_LOG.LEVEL_PROCEDURE);
506
507 IF p_record.dmltype$$='I' THEN
509 APPLY_INSERT
510 (
511 p_record,
512 p_error_msg,
513 x_return_status
514 );
515 ELSIF p_record.dmltype$$='D' THEN
516 -- Process DELETE
517 APPLY_DELETE
518 (
519 p_record,
520 p_error_msg,
521 x_return_status
522 );
523
524 ELSE --Delete and update is not supported for this PI
525 -- invalid dml type
526 CSM_UTIL_PKG.LOG
527 ( 'Invalid DML type: ' || p_record.dmltype$$ || ' is not supported for this entity'
528 || ' for Query Instance ID ' || p_record.QUERY_ID ,'CSM_MOBILE_QUERY_PKG.APPLY_RECORD',FND_LOG.LEVEL_ERROR);
529
530 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
531 (
532 p_message => 'CSM_DML_OPERATION'
533 , p_token_name1 => 'DML'
534 , p_token_value1 => p_record.dmltype$$
535 );
536
537 x_return_status := FND_API.G_RET_STS_ERROR;
538 END IF;
539
540 CSM_UTIL_PKG.LOG('Leaving CSM_MOBILE_QUERY_PKG.APPLY_RECORD for Query ID ' || p_record.QUERY_ID ,
541 'CSM_MOBILE_QUERY_PKG.APPLY_RECORD',FND_LOG.LEVEL_PROCEDURE);
542 EXCEPTION WHEN OTHERS THEN
543 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_RECORD', sqlerrm);
544 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
545 (
546 p_api_error => TRUE
547 );
548 CSM_UTIL_PKG.log( 'Exception in ' || g_object_name || '.APPLY_RECORD: ' || SUBSTR(SQLERRM,1,3000)
549 || ' for Query ID ' || p_record.QUERY_ID ,'CSM_MOBILE_QUERY_PKG.APPLY_RECORD',FND_LOG.LEVEL_EXCEPTION);
550
551 x_return_status := FND_API.G_RET_STS_ERROR;
552
553 END APPLY_RECORD;
554
555 /***
556 This procedure is called by CSM_SERVICEP_WRAPPER_PKG when publication item CSM_QUERY
557 is dirty. This happens when a mobile field service device executed DML on an updatable table and did
558 a fast sync. This procedure will insert the data that came from mobile into the backend tables using
559 public APIs.
560 ***/
561 PROCEDURE APPLY_CLIENT_CHANGES
562 (
563 p_user_name IN VARCHAR2,
564 p_tranid IN NUMBER,
565 p_debug_level IN NUMBER,
566 x_return_status IN out nocopy VARCHAR2
567 ) IS
568
569 l_process_status VARCHAR2(1);
570 l_error_msg VARCHAR2(4000);
571
572 BEGIN
573 CSM_UTIL_PKG.LOG('Entering CSM_MOBILE_QUERY_PKG.APPLY_CLIENT_CHANGES ',
574 'CSM_MOBILE_QUERY_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_PROCEDURE);
575 g_debug_level := p_debug_level;
576 x_return_status := FND_API.G_RET_STS_SUCCESS;
577
578 /*** loop through all the records in inqueue ***/
579 FOR r_query_rec IN c_query( p_user_name, p_tranid) LOOP
580 SAVEPOINT save_rec ;
581 /*** apply record ***/
582 APPLY_RECORD
583 (
584 r_query_rec
585 , l_error_msg
586 , l_process_status
587 );
588
589 /*** was record processed successfully? ***/
590 IF l_process_status = FND_API.G_RET_STS_SUCCESS THEN
591 /*** Yes -> delete record from inqueue ***/
592 CSM_UTIL_PKG.DELETE_RECORD
593 (
594 p_user_name,
595 p_tranid,
596 r_query_rec.seqno$$,
597 r_query_rec.QUERY_ID,
598 g_object_name,
599 g_pub_name,
600 l_error_msg,
601 l_process_status
602 );
603 IF l_process_status = FND_API.G_RET_STS_SUCCESS THEN
604 FOR r_query_var_inq_rec IN c_query_var_inq( p_user_name, p_tranid,r_query_rec.QUERY_ID ) LOOP
605 CSM_UTIL_PKG.DELETE_RECORD
606 (
607 p_user_name,
608 p_tranid,
609 r_query_var_inq_rec.seqno$$,
610 r_query_var_inq_rec.GEN_PK,
611 g_object_name,
612 g_query_var_pub_name,
613 l_error_msg,
614 l_process_status
615 );
616 EXIT WHEN l_process_status <> FND_API.G_RET_STS_SUCCESS;
617 END LOOP;
618 END IF;
619 /*** was delete successful? ***/
620 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
621 /*** no -> rollback ***/
622 CSM_UTIL_PKG.LOG
623 ( 'Deleting from inqueue failed, rolling back to savepoint'
624 || ' for Instance ID ' || r_query_rec.QUERY_ID ,'CSM_MOBILE_QUERY_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_ERROR); -- put PK column here
625 ROLLBACK TO save_rec;
626 x_return_status := FND_API.G_RET_STS_ERROR;
627 END IF;
628 ELSIF l_process_Status <> FND_API.G_RET_STS_SUCCESS THEN
629 /*** Record was not applied successfully -> defer and reject records ***/
630 csm_util_pkg.log( 'Record not processed successfully, deferring and rejecting record'
631 || ' for PK ' || r_query_rec.QUERY_ID ,'CSM_MOBILE_QUERY_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_ERROR); -- put PK column here
632
633 CSM_UTIL_PKG.DEFER_RECORD
634 (
635 p_user_name
636 , p_tranid
637 , r_query_rec.seqno$$
638 , r_query_rec.QUERY_ID
639 , g_object_name
640 , g_pub_name
641 , l_error_msg
642 , l_process_status
643 , r_query_rec.dmltype$$
644 );
645
646 IF l_process_status = FND_API.G_RET_STS_SUCCESS THEN
647 FOR r_query_var_inq_rec IN c_query_var_inq( p_user_name, p_tranid,r_query_rec.QUERY_ID ) LOOP
648 CSM_UTIL_PKG.DEFER_RECORD
649 (
650 p_user_name,
651 p_tranid,
652 r_query_var_inq_rec.seqno$$,
653 r_query_var_inq_rec.GEN_PK,
654 g_object_name,
655 g_query_var_pub_name,
656 l_error_msg,
657 l_process_status,
658 r_query_var_inq_rec.dmltype$$
659 );
660 EXIT WHEN l_process_status <> FND_API.G_RET_STS_SUCCESS;
661 END LOOP;
662 END IF;
663 /*** Was defer successful? ***/
664 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
665 /*** no -> rollback ***/
666 CSM_UTIL_PKG.LOG
667 ( 'Defer record failed, rolling back to savepoint'
668 || ' for PK ' || r_query_rec.QUERY_ID ,'CSM_MOBILE_QUERY_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_ERROR); -- put PK column here
669 ROLLBACK TO save_rec;
670 x_return_status := FND_API.G_RET_STS_ERROR;
671 END IF;
672 END IF;
673
674 END LOOP;
675
676 CSM_UTIL_PKG.LOG('Leaving CSM_MOBILE_QUERY_PKG.APPLY_CLIENT_CHANGES',
677 'CSM_MOBILE_QUERY_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_PROCEDURE);
678
679 EXCEPTION WHEN OTHERS THEN
680 /*** catch and log exceptions ***/
681 CSM_UTIL_PKG.log( 'Exception in ' || g_object_name || '.APPLY_CLIENT_CHANGES: ' || sqlerrm
682 ,'CSM_MOBILE_QUERY_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_EXCEPTION);
683
684 x_return_status := FND_API.G_RET_STS_ERROR;
685
686 END APPLY_CLIENT_CHANGES;
687
688 END CSM_MOBILE_QUERY_PKG;