[Home] [Help]
PACKAGE BODY: APPS.CSM_QUERY_INSTANCE_PKG
Source
1 PACKAGE BODY CSM_QUERY_INSTANCE_PKG AS
2 /* $Header: csmuqib.pls 120.8.12020000.2 2012/08/10 09:37:53 pramosin ship $ */
3
4 error EXCEPTION;
5
6
7 /*** Globals ***/
8 g_object_name CONSTANT VARCHAR2(30) := 'CSM_QUERY_INSTANCE_PKG';
9 g_pub_name CONSTANT VARCHAR2(30) := 'CSM_QUERY_INSTANCES';
10 g_pub_qvv_name CONSTANT VARCHAR2(30) := 'CSM_QUERY_VARIABLE_VALUES';
11 g_debug_level NUMBER; -- debug level
12 g_seqno_lst CSM_INTEGER_LIST;
13 /* Select all inq records */
14 CURSOR c_query_instances( b_user_name VARCHAR2, b_tranid NUMBER, b_from_sync VARCHAR2) is
15 SELECT inq.*
16 FROM CSM_QUERY_INSTANCES_INQ inq,
17 CSM_QUERY_B b
18 WHERE inq.tranid$$ = b_tranid
19 AND inq.clid$$cs = b_user_name
20 AND inq.QUERY_ID = b.QUERY_ID
21 AND ((b.EXECUTION_MODE = 'SYNCHRONOUS' AND b_from_sync ='Y') OR b_from_sync = 'N');
22
23 CURSOR c_query_variable_values( b_user_name VARCHAR2, b_tranid NUMBER, b_instance_id NUMBER) is
24 SELECT *
25 FROM CSM_QUERY_VARIABLE_VALUES_INQ
26 WHERE tranid$$ = b_tranid
27 AND clid$$cs = b_user_name
28 AND INSTANCE_ID = b_instance_id;
29 /***
30 This procedure is called by APPLY_CLIENT_CHANGES when an inserted record is to be processed.
31 ***/
32 PROCEDURE APPLY_INSERT
33 (
34 p_record IN c_query_instances%ROWTYPE,
35 p_error_msg out nocopy VARCHAR2,
36 x_return_status IN out nocopy VARCHAR2
37 ) IS
38
39 CURSOR c_check_query(c_QUERY_ID NUMBER)
40 IS
41 SELECT QUERY_NAME,LEVEL_ID,LEVEL_VALUE, QUERY_TYPE
42 FROM CSM_QUERY_B
43 WHERE QUERY_ID =c_QUERY_ID
44 AND NVL(DELETE_FLAG,'N') = 'N';
45
46 CURSOR c_get_variables (c_QUERY_ID NUMBER)
47 IS
48 SELECT vb.VARIABLE_ID,
49 vb.VARIABLE_VALUE_CHAR,
50 vb.VARIABLE_VALUE_DATE,
51 vb.VARIABLE_TYPE
52 FROM CSM_QUERY_VARIABLES_B vb
53 WHERE vb.QUERY_ID = c_QUERY_ID;
54
55 CURSOR c_get_variables_from_inq (c_user_name VARCHAR2,c_tran_id NUMBER,c_QUERY_ID NUMBER, c_instance_id NUMBER)
56 IS
57 SELECT vb.VARIABLE_ID,
58 vb.VARIABLE_VALUE_CHAR,
59 vb.VARIABLE_VALUE_DATE,
60 vb.SEQNO$$
61 FROM CSM_QUERY_VARIABLE_VALUES_INQ vb
62 WHERE vb.CLID$$CS = c_user_name
63 AND vb.TRANID$$ = c_tran_id
64 AND vb.QUERY_ID = c_QUERY_ID
65 AND vb.INSTANCE_ID = c_instance_id;
66
67 CURSOR c_get_next_instance
68 IS
69 SELECT CSM_QUERY_INSTANCES_ACC_S.NEXTVAL
70 FROM DUAL;
71
72 CURSOR c_get_user(c_user_name VARCHAR2)
73 IS
74 SELECT USER_ID
75 FROM ASG_USER
76 WHERE USER_NAME =c_user_name;
77
78 CURSOR c_get_next_qvariableid
79 IS
80 SELECT CSM_QUERY_VARIABLE_VAL_ACC_S.NEXTVAL
81 FROM DUAL;
82
83 --Variable Declarations
84 l_msg_count NUMBER;
85 l_msg_data VARCHAR2(4000);
86 l_variable_id_lst CSM_INTEGER_LIST;
87 l_var_value_char_lst CSM_VARCHAR_LIST;
88 l_var_value_date_lst CSM_DATE_LIST;
89 l_var_type_lst CSM_VARCHAR_LIST;
90 l_variable_id_inq_lst CSM_INTEGER_LIST;
91 l_var_value_char_inq_lst CSM_VARCHAR_LIST;
92 l_var_value_date_inq_lst CSM_DATE_LIST;
93 l_query_id NUMBER;
94 l_variable_cnt NUMBER;
95 l_ins_variable_cnt NUMBER;
96 l_instance_name VARCHAR2(255);
97 l_instance_id NUMBER;
98 l_access_id NUMBER;
99 l_mark_dirty BOOLEAN;
100 l_dummy_qry_id NUMBER;
101 l_user_id NUMBER;
102 l_qvariable_id NUMBER;
103 l_level_id NUMBER;
104 l_level_value NUMBER;
105 l_responsibility_id NUMBER := NULL;
106 l_error_msg VARCHAR2(4000);
107 l_query_type VARCHAR2(255);
108 BEGIN
109
110 x_return_status := FND_API.G_RET_STS_SUCCESS;
111 CSM_UTIL_PKG.LOG('Entering CSM_QUERY_INSTANCE_PKG.APPLY_INSERT for Instance ID ' || p_record.INSTANCE_ID ,
112 'CSM_QUERY_INSTANCE_PKG.APPLY_INSERT',FND_LOG.LEVEL_PROCEDURE);
113 l_query_id := p_record.QUERY_ID;
114 --Check if the Query id is valid
115 OPEN c_check_query(l_query_id);
116 FETCH c_check_query INTO l_instance_name, l_level_id, l_level_value,l_query_type;
117 IF c_check_query%NOTFOUND THEN
118 CSM_UTIL_PKG.LOG( 'Invalid Query Id : ' || l_query_id ,FND_LOG.LEVEL_ERROR);
119 x_return_status := FND_API.G_RET_STS_ERROR;
120 p_error_msg := 'Invalid Query Id : ' || l_query_id ;
121 CLOSE c_check_query;
122 x_return_status := FND_API.G_RET_STS_ERROR;
123 RETURN;
124 END IF;
125 CLOSE c_check_query;
126
127 IF l_level_id = 10003 THEN
128 l_responsibility_id := l_level_value;
129 END IF;
130 IF l_query_type ='SQL' THEN
131 --Validate the Access to a Query
132 CSM_QUERY_PKG.VALIDATE_ACCESS( p_QUERY_ID => l_query_id,
133 p_QUERY_TEXT1 => NULL,
134 p_QUERY_TEXT2 => NULL,
135 p_RESPONSIBILITY_ID => l_responsibility_id,
136 x_return_status => x_return_status,
137 x_error_message => l_error_msg);
138
139 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
140 CSM_UTIL_PKG.LOG
141 ( 'Exception occurred in VALIDATE_ACCESS for Query Name ' || l_instance_name || SQLERRM, 'INSERT_INSTANCE',
142 FND_LOG.LEVEL_EXCEPTION);
143 x_return_status := FND_API.G_RET_STS_ERROR;
144 p_error_msg := 'Query Access Validation failed : ' || l_error_msg;
145 RETURN;
146 END IF;
147 END IF;
148
149 IF p_record.INSTANCE_NAME IS NOT NULL THEN
150 l_instance_name := p_record.INSTANCE_NAME;
151 ELSE
152 l_instance_name := 'Instance for Query id ' || ' : '|| l_query_id ||' at ' || sysdate;
153 END IF;
154 --get user_id
155 OPEN c_get_user(p_record.CLID$$CS);
156 FETCH c_get_user INTO l_user_id;
157 CLOSE c_get_user;
158 l_instance_id := p_record.instance_id;
159
160 SAVEPOINT INSERT_QUERY_INSTANCE;
161
162 IF l_instance_id IS NULL THEN
163 OPEN c_get_next_instance;
164 FETCH c_get_next_instance INTO l_instance_id;
165 CLOSE c_get_next_instance;
166 END IF;
167 --Insert the Instance
168 INSERT INTO CSM_QUERY_INSTANCES_ACC(ACCESS_ID , USER_ID , QUERY_ID , INSTANCE_ID , INSTANCE_NAME,STATUS,
169 CREATED_BY,CREATION_DATE,LAST_UPDATED_BY, LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
170 VALUES (l_instance_id, l_user_id, l_query_id, l_instance_id,l_instance_name,'OPEN',
171 fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE, fnd_global.login_id);
172
173 IF csm_util_pkg.is_palm_user(l_user_id) THEN
174 l_mark_dirty := asg_Download.mark_dirty (p_pub_item => g_pub_name,
175 p_accessid => l_instance_id, --same as access id
176 p_userid => l_user_id,
177 p_dml => asg_download.ins,
178 p_timestamp => sysdate);
179 END IF;
180
181 OPEN c_get_variables (l_query_id);
182 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;
183 CLOSE c_get_variables;
184
185 --Query does not have any where condition so Execute and leave
186 IF l_variable_id_lst.COUNT = 0 THEN
187
188 --Execute the Query
189 CSM_QUERY_PKG.EXECUTE_QUERY ( p_USER_ID => l_user_id,
190 p_QUERY_ID => l_query_id,
191 p_INSTANCE_ID => l_instance_id,
192 x_return_status => x_return_status,
193 x_error_message => l_error_msg,
194 p_commit => fnd_api.G_FALSE,
195 p_source_module => 'MFSCLIENT'
196 );
197 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
198 CSM_UTIL_PKG.LOG( 'Query Upload Failed for : ' || l_query_id ,FND_LOG.LEVEL_ERROR);
199 p_error_msg := 'Query Upload Failed at execution for Instance id : '
200 || l_instance_id || 'With Message' || l_error_msg;
201 x_return_status := FND_API.G_RET_STS_ERROR;
202 ROLLBACK TO INSERT_QUERY_INSTANCE;
203 RETURN;
204 END IF;
205
206 CSM_UTIL_PKG.LOG( 'Leaving INSERT_INSTANCE for Query Id : ' || l_user_id , FND_LOG.LEVEL_ERROR);
207 x_return_status := FND_API.G_RET_STS_SUCCESS;
208 p_error_msg := 'Leaving INSERT_INSTANCE for Query Id : ' || l_user_id ;
209 RETURN;
210
211 END IF;
212
213 --Get variablefr from the INQ
214 OPEN c_get_variables_from_inq (p_record.CLID$$CS,p_record.TRANID$$,l_query_id,p_record.instance_id);
215 FETCH c_get_variables_from_inq BULK COLLECT INTO l_variable_id_inq_lst, l_var_value_char_inq_lst, l_var_value_date_inq_lst, g_seqno_lst;
216 CLOSE c_get_variables_from_inq;
217
218 IF l_variable_id_lst.COUNT <> l_variable_id_inq_lst.COUNT THEN
219 CSM_UTIL_PKG.LOG
220 ( 'Variable Count mismatch.Leaving INSERT_INSTANCE for Query Id : ' || l_query_id ,
221 FND_LOG.LEVEL_ERROR);
222 x_return_status := FND_API.G_RET_STS_ERROR;
223 p_error_msg := 'Variable Count mismatch.Leaving INSERT_INSTANCE for Query Id : ' || l_query_id ;
224 RETURN;
225 END IF;
226 --Check if the values send are proper according to the Type
227 FOR i in 1..l_variable_id_inq_lst.COUNT LOOP
228 IF UPPER(l_var_type_lst(i)) = 'DATE' THEN
229 IF l_var_value_date_inq_lst(i) IS NULL THEN
230 CSM_UTIL_PKG.LOG
231 ( 'Variable Value sent not matching with Type.Leaving INSERT_INSTANCE for Query Id : ' || l_query_id ,
232 FND_LOG.LEVEL_ERROR);
233 x_return_status := FND_API.G_RET_STS_ERROR;
234 p_error_msg := 'Variable Value sent not matching with Type.Leaving INSERT_INSTANCE for Query Id : ' || l_query_id ;
235 RETURN;
236 END IF;
237 ELSE
238
239 IF l_var_value_char_inq_lst(i) IS NULL THEN
240 CSM_UTIL_PKG.LOG
241 ( 'Variable Value sent not matching with Type.Leaving INSERT_INSTANCE for Query Id : ' || l_query_id ,
242 FND_LOG.LEVEL_ERROR);
243 x_return_status := FND_API.G_RET_STS_ERROR;
244 p_error_msg := 'Variable Value sent not matching with Type.Leaving INSERT_INSTANCE for Query Id : ' || l_query_id ;
245 RETURN;
246 END IF;
247 END IF;
248
249 END LOOP;
250
251 --Insert into variable values table
252 FOR i in 1..l_variable_id_inq_lst.COUNT LOOP
253
254 OPEN c_get_next_qvariableid;
255 FETCH c_get_next_qvariableid INTO l_qvariable_id;
256 CLOSE c_get_next_qvariableid;
257
258 INSERT INTO CSM_QUERY_VARIABLE_VALUES_ACC(ACCESS_ID , USER_ID , QUERY_ID ,
259 INSTANCE_ID , VARIABLE_ID,VARIABLE_VALUE_CHAR,VARIABLE_VALUE_DATE,
260 CREATED_BY,CREATION_DATE,LAST_UPDATED_BY, LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,GEN_PK)
261 VALUES (l_qvariable_id, l_user_id, l_query_id,
262 l_instance_id, i, l_var_value_char_inq_lst(i), l_var_value_date_inq_lst(i),
263 l_user_id, SYSDATE, l_user_id, SYSDATE, l_user_id,l_qvariable_id)
264 RETURNING ACCESS_ID into l_access_id;
265
266 IF csm_util_pkg.is_palm_user(l_user_id) THEN
267 l_mark_dirty := asg_Download.mark_dirty(p_pub_item => g_pub_qvv_name,
268 p_accessid => l_access_id,
269 p_userid => l_user_id,
270 p_dml => asg_download.ins,
271 p_timestamp => sysdate);
272 END IF;
273 END LOOP;
274
275 --Execute the Query
276 CSM_QUERY_PKG.EXECUTE_QUERY ( p_USER_ID => l_user_id,
277 p_QUERY_ID => l_query_id,
278 p_INSTANCE_ID => l_instance_id,
279 x_return_status => x_return_status,
280 x_error_message => l_error_msg,
281 p_commit => fnd_api.G_FALSE,
282 p_source_module => 'MFSCLIENT'
283 );
284 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
285 CSM_UTIL_PKG.LOG( 'Query Upload Failed for : ' || l_query_id ,FND_LOG.LEVEL_ERROR);
286 p_error_msg := 'Query Upload Failed at execution for Instance id : '
287 || l_instance_id || ' With Message ' || l_error_msg;
288 x_return_status := FND_API.G_RET_STS_ERROR;
289 ROLLBACK TO INSERT_QUERY_INSTANCE;
290 RETURN;
291 END IF;
292
293 CSM_UTIL_PKG.LOG('Leaving CSM_QUERY_INSTANCE_PKG.APPLY_INSERT for Instance ID ' || p_record.INSTANCE_ID ,
294 'CSM_QUERY_INSTANCE_PKG.APPLY_INSERT',FND_LOG.LEVEL_PROCEDURE);
295 x_return_status := FND_API.G_RET_STS_SUCCESS;
296 p_error_msg := 'Upload Successful for the Instance Id : ' || l_instance_id ;
297 RETURN;
298
299 EXCEPTION WHEN OTHERS THEN
300 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_INSERT', sqlerrm);
301 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
302 (
303 p_api_error => TRUE
304 );
305 CSM_UTIL_PKG.log( 'Exception in ' || g_object_name || '.APPLY_INSERT: ' || sqlerrm
306 || ' for Instance ID ' || p_record.INSTANCE_ID ,'CSM_QUERY_INSTANCE_PKG.APPLY_INSERT',FND_LOG.LEVEL_EXCEPTION);
307 p_error_msg := 'Exception in APPLY_INSERT for Instance Id : ' || l_instance_id || substr(SQLERRM, 1,2000);
308 x_return_status := FND_API.G_RET_STS_ERROR;
309 ROLLBACK TO INSERT_QUERY_INSTANCE;
310 END APPLY_INSERT;
311
312
313 /***
314 This procedure is called by APPLY_CLIENT_CHANGES when an Delete is to be processed.
315 ***/
316 PROCEDURE APPLY_DELETE
317 (
318 p_record IN c_query_instances%ROWTYPE,
319 p_error_msg out nocopy VARCHAR2,
320 x_return_status IN out nocopy VARCHAR2
321 )
322 IS
323 CURSOR c_get_user(c_user_name VARCHAR2)
324 IS
325 SELECT USER_ID
326 FROM ASG_USER
327 WHERE USER_NAME =c_user_name;
328
329 CURSOR c_get_queryid (c_instance_id NUMBER)
330 IS
331 SELECT QUERY_ID
332 FROM CSM_QUERY_INSTANCES_ACC
333 WHERE INSTANCE_ID = c_instance_id;
334
335 --Variable Declarations
336 l_msg_count NUMBER;
337 l_msg_data VARCHAR2(4000);
338 l_query_id NUMBER;
339 l_instance_id NUMBER;
340 l_user_id NUMBER;
341
342 BEGIN
343
344 x_return_status := FND_API.G_RET_STS_SUCCESS;
345 CSM_UTIL_PKG.LOG('Entering CSM_QUERY_INSTANCE_PKG.APPLY_DELETE for Instance ID ' || p_record.INSTANCE_ID ,
346 'CSM_QUERY_INSTANCE_PKG.APPLY_DELETE',FND_LOG.LEVEL_PROCEDURE);
347 l_query_id := p_record.QUERY_ID;
348 l_instance_id := p_record.instance_id;
349 --get user_id
350 OPEN c_get_user(p_record.CLID$$CS);
351 FETCH c_get_user INTO l_user_id;
352 CLOSE c_get_user;
353
354 IF l_query_id IS NULL THEN
355 OPEN c_get_queryid (l_instance_id);
356 FETCH c_get_queryid INTO l_query_id;
357 CLOSE c_get_queryid;
358 END IF;
359
360 IF l_query_id IS NULL THEN
361 CSM_UTIL_PKG.LOG( 'Query Instance Delete Failed for : ' || l_instance_id ,FND_LOG.LEVEL_ERROR);
362 x_return_status := FND_API.G_RET_STS_ERROR;
363 p_error_msg := 'Delete request failed for Instance Id : ' || l_instance_id || ' as it does not exist.';
364 RETURN;
365 END IF;
366
367 --Calling Query instance Delete
368 CSM_QUERY_PKG.DELETE_INSTANCE
369 ( p_USER_ID => l_user_id,
370 p_QUERY_ID => l_query_id,
371 p_INSTANCE_ID => l_instance_id,
372 p_commit => fnd_api.G_FALSE,
373 x_return_status => x_return_status,
374 x_error_message => p_error_msg) ;
375
376 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
377 CSM_UTIL_PKG.LOG( 'Query Instance Delete Failed for : ' || l_instance_id ,FND_LOG.LEVEL_ERROR);
378 x_return_status := FND_API.G_RET_STS_ERROR;
379 RETURN;
380 END IF;
381
382 CSM_UTIL_PKG.LOG('Leaving CSM_QUERY_INSTANCE_PKG.APPLY_DELETE for Instance ID ' || p_record.INSTANCE_ID ,
383 'CSM_QUERY_INSTANCE_PKG.APPLY_DELETE',FND_LOG.LEVEL_PROCEDURE);
384 x_return_status := FND_API.G_RET_STS_SUCCESS;
385 p_error_msg := 'Delete Successful for the Instance Id : ' || l_instance_id ;
386 RETURN;
387
388 EXCEPTION WHEN OTHERS THEN
389 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_DELETE', sqlerrm);
390 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT (p_api_error => TRUE);
391 CSM_UTIL_PKG.log( 'Exception in ' || g_object_name || '.APPLY_DELETE: ' || sqlerrm
392 || ' for Instance ID ' || p_record.INSTANCE_ID ,'CSM_QUERY_INSTANCE_PKG.APPLY_DELETE',FND_LOG.LEVEL_EXCEPTION);
393 p_error_msg := 'Exception in APPLY_DELETE for Instance Id : ' || l_instance_id || substr(SQLERRM, 1,2000);
394 x_return_status := FND_API.G_RET_STS_ERROR;
395 END APPLY_DELETE;
396 /***
397 This procedure is called by APPLY_CLIENT_CHANGES for every record in in-queue that needs to be processed.
398 ***/
399 PROCEDURE APPLY_RECORD
400 (
401 p_record IN c_query_instances%ROWTYPE,
402 p_error_msg out nocopy VARCHAR2,
403 x_return_status IN out nocopy VARCHAR2
404 ) IS
405 l_rc BOOLEAN;
406 l_access_id NUMBER;
407 BEGIN
408 /*** initialize return status and message list ***/
409 x_return_status := FND_API.G_RET_STS_SUCCESS;
410 FND_MSG_PUB.INITIALIZE;
411
412 CSM_UTIL_PKG.LOG('Entering CSM_QUERY_INSTANCE_PKG.APPLY_RECORD for Query Instance ID ' || p_record.INSTANCE_ID ,
413 'CSM_QUERY_INSTANCE_PKG.APPLY_RECORD',FND_LOG.LEVEL_PROCEDURE);
414
415 IF p_record.dmltype$$='I' THEN
416 -- Process insert
417 APPLY_INSERT
418 (
419 p_record,
420 p_error_msg,
421 x_return_status
422 );
423 ELSIF p_record.dmltype$$='D' THEN
424 -- Process Delete
425 APPLY_DELETE
426 (
427 p_record,
428 p_error_msg,
429 x_return_status
430 );
431 ELSE -- update is not supported for this PI
432 -- invalid dml type
433 CSM_UTIL_PKG.LOG
434 ( 'Invalid DML type: ' || p_record.dmltype$$ || ' is not supported for this entity'
435 || ' for Query Instance ID ' || p_record.INSTANCE_ID ,'CSM_QUERY_INSTANCE_PKG.APPLY_RECORD',FND_LOG.LEVEL_ERROR);
436
437 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
438 (
439 p_message => 'CSM_DML_OPERATION'
440 , p_token_name1 => 'DML'
441 , p_token_value1 => p_record.dmltype$$
442 );
443
444 x_return_status := FND_API.G_RET_STS_ERROR;
445 END IF;
446
447 CSM_UTIL_PKG.LOG('Leaving CSM_QUERY_INSTANCE_PKG.APPLY_RECORD for Query Instance ID ' || p_record.INSTANCE_ID ,
448 'CSM_QUERY_INSTANCE_PKG.APPLY_RECORD',FND_LOG.LEVEL_PROCEDURE);
449 EXCEPTION WHEN OTHERS THEN
450 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_RECORD', sqlerrm);
451 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
452 (
453 p_api_error => TRUE
454 );
455 CSM_UTIL_PKG.log( 'Exception in ' || g_object_name || '.APPLY_RECORD: ' || sqlerrm
456 || ' for Query Instance ID ' || p_record.INSTANCE_ID ,'CSM_QUERY_INSTANCE_PKG.APPLY_RECORD',FND_LOG.LEVEL_EXCEPTION);
457
458 x_return_status := FND_API.G_RET_STS_ERROR;
459
460 END APPLY_RECORD;
461
462 /***
463 This procedure is called by CSM_SERVICEP_WRAPPER_PKG when publication item CSM_QUERY_INSTANCES
464 is dirty. This happens when a mobile field service device executed DML on an updatable table and did
465 a fast sync. This procedure will insert the data that came from mobile into the backend tables using
466 public APIs.
467 ***/
468 PROCEDURE APPLY_CLIENT_CHANGES
469 (
470 p_user_name IN VARCHAR2,
471 p_tranid IN NUMBER,
472 p_debug_level IN NUMBER,
473 p_from_sync IN VARCHAR2 DEFAULT 'N',
474 x_return_status IN out nocopy VARCHAR2
475 ) IS
476
477 l_process_status VARCHAR2(1);
478 l_error_msg VARCHAR2(4000);
479
480 BEGIN
481 CSM_UTIL_PKG.LOG('Entering CSM_QUERY_INSTANCE_PKG.APPLY_CLIENT_CHANGES ',
482 'CSM_QUERY_INSTANCE_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_PROCEDURE);
483 g_debug_level := p_debug_level;
484 x_return_status := FND_API.G_RET_STS_SUCCESS;
485
486 /*** loop through all the records in inqueue ***/
487 FOR r_qi_rec IN c_query_instances( p_user_name, p_tranid, p_from_sync) LOOP
488 SAVEPOINT save_rec ;
489 /*** apply record ***/
490 APPLY_RECORD
491 (
492 r_qi_rec
493 , l_error_msg
494 , l_process_status
495 );
496
497 /*** was record processed successfully? ***/
498 IF l_process_status = FND_API.G_RET_STS_SUCCESS THEN
499 /*** Yes -> delete record from inqueue ***/
500 CSM_UTIL_PKG.REJECT_RECORD
501 (
502 p_user_name,
503 p_tranid,
504 r_qi_rec.seqno$$,
505 r_qi_rec.INSTANCE_ID,
506 g_object_name,
507 g_pub_name,
508 l_error_msg,
509 l_process_status
510 );
511 /*** was Instance delete successful? ***/
512 IF l_process_status = FND_API.G_RET_STS_SUCCESS THEN
513 /*** Yes -> delete Variable Values record from inqueue ***/
514 FOR r_qvv_rec IN c_query_variable_values( p_user_name, p_tranid, r_qi_rec.INSTANCE_ID) LOOP
515 CSM_UTIL_PKG.REJECT_RECORD
516 (
517 p_user_name,
518 p_tranid,
519 r_qvv_rec.seqno$$,
520 r_qvv_rec.GEN_PK,
521 g_object_name,
522 g_pub_qvv_name,
523 l_error_msg,
524 l_process_status
525 );
526 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
527 EXIT;
528 END IF;
529 END LOOP;
530 END IF;
531
532 /*** was delete successful? ***/
533 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
534 /*** no -> rollback ***/
535 CSM_UTIL_PKG.LOG
536 ( 'Deleting from inqueue failed, rolling back to savepoint'
537 || ' for Instance ID ' || r_qi_rec.INSTANCE_ID ,'CSM_QUERY_INSTANCE_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_ERROR); -- put PK column here
538 ROLLBACK TO save_rec;
539 x_return_status := FND_API.G_RET_STS_ERROR;
540 END IF;
541 ELSIF l_process_Status <> FND_API.G_RET_STS_SUCCESS AND p_from_sync = 'Y' THEN
542 CSM_UTIL_PKG.LOG
543 ( 'Defer record failed, rolling back to savepoint'
544 || ' for PK ' || r_qi_rec.INSTANCE_ID ,'CSM_QUERY_INSTANCE_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_ERROR); -- put PK column here
545 ROLLBACK TO save_rec;
546 x_return_status := FND_API.G_RET_STS_ERROR;
547 ELSIF l_process_Status <> FND_API.G_RET_STS_SUCCESS AND p_from_sync ='N' THEN
548 /*** Record was not applied successfully -> defer and reject records ***/
549 csm_util_pkg.log( 'Record not processed successfully, deferring and rejecting record'
550 || ' for PK ' || r_qi_rec.INSTANCE_ID ,'CSM_QUERY_INSTANCE_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_ERROR); -- put PK column here
551
552 CSM_UTIL_PKG.DEFER_RECORD
553 (
554 p_user_name
555 , p_tranid
556 , r_qi_rec.seqno$$
557 , r_qi_rec.INSTANCE_ID
558 , g_object_name
559 , g_pub_name
560 , l_error_msg
561 , l_process_status
562 , r_qi_rec.dmltype$$
563 );
564
565 /*** was Instance defer successful? ***/
566 IF l_process_status = FND_API.G_RET_STS_SUCCESS THEN
567 /*** Yes -> defer Variable Values record from inqueue ***/
568 FOR r_qvv_rec IN c_query_variable_values( p_user_name, p_tranid, r_qi_rec.INSTANCE_ID) LOOP
569 CSM_UTIL_PKG.DEFER_RECORD
570 (
571 p_user_name,
572 p_tranid,
573 r_qvv_rec.seqno$$,
574 r_qvv_rec.GEN_PK,
575 g_object_name,
576 g_pub_qvv_name,
577 l_error_msg,
578 l_process_status,
579 r_qvv_rec.dmltype$$
580 );
581 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
582 EXIT;
583 END IF;
584 END LOOP;
585 END IF;
586
587
588 /*** Was defer successful? ***/
589 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
590 /*** no -> rollback ***/
591 CSM_UTIL_PKG.LOG
592 ( 'Defer record failed, rolling back to savepoint'
593 || ' for PK ' || r_qi_rec.INSTANCE_ID ,'CSM_QUERY_INSTANCE_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_ERROR); -- put PK column here
594 ROLLBACK TO save_rec;
595 x_return_status := FND_API.G_RET_STS_ERROR;
596 END IF;
597 END IF;
598
599 END LOOP;
600
601 CSM_UTIL_PKG.LOG('Leaving CSM_QUERY_INSTANCE_PKG.APPLY_CLIENT_CHANGES',
602 'CSM_QUERY_INSTANCE_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_PROCEDURE);
603
604 EXCEPTION WHEN OTHERS THEN
605 /*** catch and log exceptions ***/
606 CSM_UTIL_PKG.log( 'Exception in ' || g_object_name || '.APPLY_CLIENT_CHANGES: ' || sqlerrm
607 ,'CSM_QUERY_INSTANCE_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_EXCEPTION);
608
609 x_return_status := FND_API.G_RET_STS_ERROR;
610
611 END APPLY_CLIENT_CHANGES;
612
613 END CSM_QUERY_INSTANCE_PKG;