1 PACKAGE BODY CSM_SERVICEP_WRAPPER_PKG AS
2 /* $Header: csmuspwb.pls 120.6 2006/06/08 09:54:09 utekumal noship $ */
3
4 -- MODIFICATION HISTORY
5 -- Anurag 06/09/02 Created
6 -- --------- ------ ------------------------------------------
7 -- Enter procedure, function bodies as shown below
8
9 /*** Globals ***/
10 g_debug_level NUMBER; -- debug level
11 g_object_name CONSTANT VARCHAR2(30) := 'CSM_SERVICEP_WRAPPER_PKG';
12 --bug4233613
13 g_olite_schema CONSTANT VARCHAR2(15) := 'MOBILEADMIN';
14
15
16 PROCEDURE GET_ALL_DEFERRED_PUB_ITEMS(p_username IN VARCHAR2,
17 p_tranid IN NUMBER,
18 p_pubname IN VARCHAR2,
19 x_pubitems_tbl OUT NOCOPY asg_apply.vc2_tbl_type,
20 x_return_status OUT NOCOPY VARCHAR2)
21 IS
22 l_defer_count pls_integer;
23 l_all_pubitems_tbl asg_apply.vc2_tbl_type;
24 l_null_pubitems_tbl asg_apply.vc2_tbl_type;
25 l_return_status varchar2(1);
26 l_dummy number;
27
28 CURSOR c_isdeferred(p_user_name VARCHAR2, p_tran_id NUMBER, p_pubitem VARCHAR2)
29 IS
30 SELECT 1
31 FROM asg_deferred_traninfo
32 WHERE device_user_name = p_user_name
33 AND deferred_tran_id = p_tranid
34 AND object_name = p_pubitem
35 ;
36
37 BEGIN
38 x_return_status := FND_API.G_RET_STS_ERROR;
39
40 /** initialize tables **/
41 l_all_pubitems_tbl := l_null_pubitems_tbl;
42 x_pubitems_tbl := l_null_pubitems_tbl;
43
44 /*** retrieve names of deferred dirty SERVICEP publication items ***/
45 asg_apply.get_all_pub_items(p_username,
46 p_tranid,
47 'SERVICEP',
48 l_all_pubitems_tbl,
49 l_return_status);
50
51 /*** successfully retrieved item names? ***/
52 IF l_return_status <> FND_API.G_RET_STS_SUCCESS OR l_all_pubitems_tbl.COUNT = 0 THEN
53 NULL;
54 ELSE
55 l_defer_count := 0;
56 FOR i IN 1..l_all_pubitems_tbl.count LOOP
57 OPEN c_isdeferred(p_username,p_tranid, l_all_pubitems_tbl(i));
58 FETCH c_isdeferred INTO l_dummy;
59 IF c_isdeferred%FOUND THEN
60 l_defer_count := l_defer_count + 1;
61 x_pubitems_tbl(l_defer_count) := l_all_pubitems_tbl(i);
62 END IF;
63 CLOSE c_isdeferred;
64 END LOOP;
65 END IF;
66
67 x_return_status := FND_API.G_RET_STS_SUCCESS;
68
69 END GET_ALL_DEFERRED_PUB_ITEMS;
70
71
72 /***
73 This function accepts a list of publication items and a publication item name and
74 returns whether the item name was found within the item list.
75 When the item name was found, it will be removed from the list.
76 ***/
77 FUNCTION ITEM_EXISTS
78 (
79 p_pubitems_tbl IN OUT nocopy asg_apply.vc2_tbl_type,
80 p_item_name IN VARCHAR2
81 )
82 RETURN BOOLEAN IS
83 l_index BINARY_INTEGER;
84 BEGIN
85
86
87 IF p_pubitems_tbl.COUNT <= 0 THEN
88 /*** no items in list -> item name not found ***/
89 RETURN FALSE;
90 END IF;
91 FOR l_index IN p_pubitems_tbl.FIRST..p_pubitems_tbl.LAST LOOP
92 IF p_pubitems_tbl.EXISTS(l_index) THEN
93 IF p_pubitems_tbl( l_index ) = p_item_name THEN
94 /*** found item -> delete from array and return TRUE ***/
95 p_pubitems_tbl.DELETE( l_index );
96
97 RETURN TRUE;
98 END IF;
99 END IF;
100 END LOOP;
101 /*** item name not found ***/
102
103 RETURN FALSE;
104 EXCEPTION WHEN OTHERS THEN
105 CSM_UTIL_PKG.log( 'Exception occurred in CSM_SERVICEP_WRAPPER_PKG.ITEM_EXISTS:' || fnd_global.local_chr(10) || SQLERRM,
106 'CSM_SERVICEP_WRAPPER_PKG.ITEM_EXISTS',FND_LOG.LEVEL_EXCEPTION);
107
108 RETURN FALSE;
109 END ITEM_EXISTS;
110
111 /***
112 This procedure is called by ASG_APPLY.APPLY_CLIENT_CHANGES if a list of dirty publication items
113 has been retrieved for a user/tranid combination. This procedure gets called for both
114 deferred and non-deferred publication items.
115 ***/
116 PROCEDURE APPLY_DIRTY_PUBITEMS
117 (
118 p_user_name IN VARCHAR2,
119 p_tranid IN NUMBER,
120 p_pubitems_tbl IN OUT nocopy asg_apply.vc2_tbl_type,
121 x_return_status IN OUT nocopy VARCHAR2
122 ) IS
123 l_index BINARY_INTEGER;
124 BEGIN
125
126 x_return_status := FND_API.G_RET_STS_SUCCESS;
127
128 /*** call appropriate wrapper ***/
129
130 /*** call incident wrapper
131 We should process 'CSM_INCIDENTS_ALL' before CSM_TASKS because
132 CSM_TASKS might be refering to locally created INCIDENT_ID.
133 ***/
134 IF ITEM_EXISTS( p_pubitems_tbl, 'CSM_INCIDENTS_ALL') THEN
135 CSM_SERVICE_REQUESTS_PKG.APPLY_CLIENT_CHANGES
136 (
137 p_user_name,
138 p_tranid,
139 g_debug_level,
140 x_return_status
141 );
142 END IF;
143
144 IF ITEM_EXISTS( p_pubitems_tbl, 'CSM_TASKS') THEN
145 CSM_TASKS_PKG.APPLY_CLIENT_CHANGES
146 (
147 p_user_name,
148 p_tranid,
149 g_debug_level,
150 x_return_status
151 );
152 END IF;
153
154
155 IF ITEM_EXISTS( p_pubitems_tbl, 'CSM_TASK_ASSIGNMENTS') THEN
156
157 CSM_TASK_ASSIGNMENTS_PKG.APPLY_CLIENT_CHANGES
158 (
159 p_user_name,
160 p_tranid,
161 g_debug_level,
162 x_return_status
163 );
164 END IF;
165
166 IF ITEM_EXISTS( p_pubitems_tbl, 'CSF_M_COUNTER_VALUES') THEN
167
168 CSM_COUNTER_VALUES_PKG.APPLY_CLIENT_CHANGES
169 (
170 p_user_name,
171 p_tranid,
172 g_debug_level,
173 x_return_status
174 );
175 END IF;
176
177 IF ITEM_EXISTS( p_pubitems_tbl, 'CSM_DEBRIEF_HEADERS') THEN
178
179 CSM_DEBRIEF_HEADERS_PKG.APPLY_CLIENT_CHANGES
180 (
181 p_user_name,
182 p_tranid,
183 g_debug_level,
184 x_return_status
185 );
186 END IF;
187
188 IF ITEM_EXISTS( p_pubitems_tbl, 'CSF_M_DEBRIEF_EXPENSES') THEN
189
190 CSM_DEBRIEF_EXPENSES_PKG.APPLY_CLIENT_CHANGES
191 (
192 p_user_name,
193 p_tranid,
194 g_debug_level,
195 x_return_status
196 );
197 END IF;
198
199 IF ITEM_EXISTS( p_pubitems_tbl, 'CSF_M_DEBRIEF_LABOR') THEN
200
201 CSM_DEBRIEF_LABOR_PKG.APPLY_CLIENT_CHANGES
202 (
203 p_user_name,
204 p_tranid,
205 g_debug_level,
206 x_return_status
207 );
208 END IF ;
209
210 IF ITEM_EXISTS( p_pubitems_tbl, 'CSF_M_DEBRIEF_PARTS') THEN
211
212 CSM_DEBRIEF_PARTS_PKG.APPLY_CLIENT_CHANGES
213 (
214 p_user_name,
215 p_tranid,
216 g_debug_level,
217 x_return_status
218 );
219 END IF;
220
221 IF ITEM_EXISTS( p_pubitems_tbl, 'CSF_M_NOTES') THEN
222 CSM_NOTES_PKG.APPLY_CLIENT_CHANGES
223 (
224 p_user_name,
225 p_tranid,
226 g_debug_level,
227 x_return_status
228 );
229 END IF;
230
231 IF ITEM_EXISTS( p_pubitems_tbl, 'CSF_M_LOBS') THEN
232
233 CSM_LOBS_PKG.APPLY_CLIENT_CHANGES
234 (
235 p_user_name,
236 p_tranid,
237 g_debug_level,
238 x_return_status
239 );
240 END IF;
241
242 IF ITEM_EXISTS( p_pubitems_tbl, 'CSF_M_MAIL_MESSAGES') THEN
243 CSM_MAIL_MESSAGES_PKG.APPLY_CLIENT_CHANGES
244 (
245 p_user_name,
246 p_tranid,
247 g_debug_level,
248 x_return_status
249 );
250 END IF;
251
252 IF ITEM_EXISTS( p_pubitems_tbl, 'CSF_M_MAIL_RECIPIENTS') THEN
253 CSM_MAIL_RECIPIENTS_PKG.APPLY_CLIENT_CHANGES
254 (
255 p_user_name,
256 p_tranid,
257 g_debug_level,
258 x_return_status
259 );
260 END IF;
261
262 /*** call requirements wrapper ***/
263 IF ITEM_EXISTS( p_pubitems_tbl, 'CSM_REQ_LINES') THEN
264 CSM_REQUIREMENTS_PKG.APPLY_CLIENT_CHANGES
265 (
266 p_user_name,
267 p_tranid,
268 g_debug_level,
269 x_return_status
270 );
271 END IF;
272
273 /*** call requirements wrapper ***/
274 IF ITEM_EXISTS( p_pubitems_tbl, 'CSF_M_INVENTORY') THEN
275 CSM_INVENTORY_PKG.APPLY_CLIENT_CHANGES
276 (
277 p_user_name,
278 p_tranid,
279 g_debug_level,
280 x_return_status
281 );
282 END IF;
283
284 IF ITEM_EXISTS( p_pubitems_tbl, 'CSF_M_USER') THEN
285 CSM_USER_PKG.APPLY_CLIENT_CHANGES
286 (
287 p_user_name,
288 p_tranid,
289 g_debug_level,
290 x_return_status
291 );
292 END IF;
293
294 --Support for Parts Transfer
295 IF ITEM_EXISTS( p_pubitems_tbl, 'CSM_MTL_MATERIAL_TXNS') THEN
296 CSM_MATERIAL_TRANSACTION_PKG.APPLY_CLIENT_CHANGES
297 (
298 p_user_name,
299 p_tranid,
300 g_debug_level,
301 x_return_status
302 );
303 END IF;
304
305 --TODO: call other wrappers for other updatable PIs
306
307
308 EXCEPTION WHEN OTHERS THEN
309 CSM_UTIL_PKG.log('Exception occurred in CSM_SERVICEP_WRAPPER_PKG.APPLY_DIRTY_PUBITEMS:' || fnd_global.local_chr(10) || SQLERRM,
310 'CSM_SERVICEP_WRAPPER_PKG.APPLY_DIRTY_PUBITEMS',FND_LOG.LEVEL_EXCEPTION );
311
312 END APPLY_DIRTY_PUBITEMS;
313
314 /***
315 This procedure is called by ASG_APPLY.PROCESS_UPLOAD when a publication item for publication SERVICEL
316 is dirty. This happens when a mobile field service device executed DML on an updatable table and did
317 a fast sync. This procedure will detect which publication items got dirty and will execute the wrapper
318 procedures which will insert the data that came from mobile into the backend tables using public APIs.
319 ***/
320 PROCEDURE APPLY_CLIENT_CHANGES
321 (
322 p_user_name IN VARCHAR2,
323 p_tranid IN NUMBER
324 ) IS
325 l_pubitems_tbl asg_apply.vc2_tbl_type;
326 l_return_status VARCHAR2(1);
327 BEGIN
328
329 /*** retrieve names of all dirty SERVICEP publication items ***/
330 /*** get_all_dirty and get_all_defered_pub_items is replaced by get_all_pub_items ***/
331 asg_apply.get_all_pub_items(p_user_name,
332 p_tranid,
333 'SERVICEP',
334 l_pubitems_tbl,
335 l_return_status);
336
337 FOR i IN 1..l_pubitems_tbl.count LOOP
338
339 csm_util_pkg.log('Dirty Pub items for tranid: ' || p_tranid || ' : ' || l_pubitems_tbl(i),'CSM_SERVICEP_WRAPPER_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_PROCEDURE);
340 -- logm('Dirty Pub items for tranid: ' || p_tranid || ' : ' || l_pubitems_tbl(i));
341 END LOOP;
342
343 /*** successfully retrieved item names? ***/
344 IF l_return_status <> FND_API.G_RET_STS_SUCCESS OR l_pubitems_tbl.COUNT = 0 THEN
345 NULL;
346 ELSE
347 /*** yes -> process them ***/
348
349 APPLY_DIRTY_PUBITEMS
350 ( p_user_name
351 , p_tranid
352 , l_pubitems_tbl
353 , l_return_status
354 );
355 END IF;
356
357 /*** retrieve names of deferred dirty SERVICEP publication items ***/
358 /*
359 get_all_deferred_pub_items(p_user_name,
360 p_tranid,
361 'SERVICEP',
362 l_pubitems_tbl,
363 l_return_status);
364
365
366
367 IF l_return_status <> FND_API.G_RET_STS_SUCCESS OR l_pubitems_tbl.COUNT = 0 THEN
368 NULL;
369 ELSE
370
371
372 APPLY_DIRTY_PUBITEMS
373 ( p_user_name
374 , p_tranid
375 , l_pubitems_tbl
376 , l_return_status
377 );
378 END IF;
379 */
380
381 EXCEPTION WHEN OTHERS THEN
382 /*** catch and log exceptions ***/
383 CSM_UTIL_PKG.log('Exception occurred in CSM_SERVICEP_WRAPPER_PKG.Apply_Client_Changes: ' || sqlerrm
384 || ' for User: ' || p_user_name ,'CSM_SERVICEP_WRAPPER_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_EXCEPTION);
385
386
387 END APPLY_CLIENT_CHANGES;
388
389
390
391
392 /**
393 * POPULATE_ACCESS_RECORDS
394 * is the bootstrap procedure called by MDG upon CSM user creation
395 * we need to iterate over the responsibilities assigned to this CSM user
396 * and call the CSM_WF_PKG.User_Resp_Post_Ins(user_id, resp_id)
397 */
398 PROCEDURE POPULATE_ACCESS_RECORDS ( p_userid IN NUMBER)
399 IS
400 CURSOR l_resp_csr(b_userid NUMBER) IS
401 SELECT DISTINCT aupr.RESPONSIBILITY_ID AS RESPONSIBILITY_ID
402 FROM ASG_USER au
403 , asg_user_pub_resps aupr
404 WHERE au.USER_ID = b_userid
405 AND aupr.pub_name = 'SERVICEP'
406 AND au.user_name = aupr.user_name;
407
408 l_resp_rec l_resp_csr%ROWTYPE;
409
410 BEGIN
411 csm_util_pkg.log('insert called','CSM_SERVICEP_WRAPPER_PKG.POPULATE_ACCESS_RECORDS',FND_LOG.LEVEL_PROCEDURE);
412
413 csm_util_pkg.pvt_log('insert called' || 'CSM_SERVICEP_WRAPPER_PKG.POPULATE_ACCESS_RECORDS');
414 FOR l_resp_rec IN l_resp_csr(p_userid) LOOP
415 csm_util_pkg.pvt_log('POPULATE_ACCESS_RECORDS: USER_ID = ' || p_userid || ' RESP_ID = ' || l_resp_rec.RESPONSIBILITY_ID);
416 csm_util_pkg.log('POPULATE_ACCESS_RECORDS: USER_ID = ' || p_userid || ' RESP_ID = ' || l_resp_rec.RESPONSIBILITY_ID
417 ,FND_LOG.LEVEL_STATEMENT );
418 CSM_WF_PKG.User_Resp_Post_Ins(p_user_id => p_userid,
419 p_responsibility_id => l_resp_rec.RESPONSIBILITY_ID );
420 END LOOP;
421 END POPULATE_ACCESS_RECORDS; -- end POPULATE_ACCESS_RECORDS
422
423 /**
424 * DELETE_ACCESS_RECORDS
425 * is the bootstrap procedure called by MDG upon CSM user deletion
426 * we need to iterate over the responsibilities assigned to this CSM user
427 * and call the CSM_WF_PKG.User_Resp_Post_Ins(user_id, resp_id)
428 */
429 PROCEDURE DELETE_ACCESS_RECORDS ( p_userid in number)
430 IS
431 BEGIN
432 csm_util_pkg.log('delete called','CSM_SERVICEP_WRAPPER_PKG.DELETE_ACCESS_RECORDS',FND_LOG.LEVEL_PROCEDURE);
433 CSM_WF_PKG.User_Del(p_user_id => p_userid);
434 END DELETE_ACCESS_RECORDS; -- end DELETE_ACCESS_RECORDS
435
436 /*
437 Call back function for ASG. used for create synonyms / grant accesses in mobileadmin schema
438 before running installation manager
439 */
440 FUNCTION CHECK_OLITE_SCHEMA RETURN VARCHAR2 IS
441 l_count NUMBER;
442 BEGIN
443 SELECT count(1) INTO l_count
444 FROM all_synonyms
445 WHERE owner = g_olite_schema AND SYNONYM_NAME = 'FND_GLOBAL';
446 IF l_count = 0 THEN
447 -- csm_util_pkg.log(' synonym mobileadmin.FND_GLOBAL does not exist');
448 --EXECUTE IMMEDIATE 'create synonym mobileadmin.FND_GLOBAL for FND_GLOBAL';
449 EXECUTE IMMEDIATE 'create synonym '
450 ||g_olite_schema||'.FND_GLOBAL for FND_GLOBAL';
451 END IF;
452 SELECT count(1) INTO l_count
453 FROM all_synonyms
454 WHERE owner = g_olite_schema AND SYNONYM_NAME = 'CSM_PROFILE_PKG';
455 IF l_count = 0 THEN
456 -- csm_util_pkg.log(' synonym mobileadmin.csm_profile_pkg does not exist');
457 --EXECUTE IMMEDIATE 'create synonym mobileadmin.csm_profile_pkg for csm_profile_pkg';
458
459 EXECUTE IMMEDIATE 'create synonym '
460 ||g_olite_schema||'.csm_profile_pkg for csm_profile_pkg';
461 END IF;
462 SELECT count(1) INTO l_count
463 FROM all_synonyms
464 WHERE owner = g_olite_schema AND SYNONYM_NAME = 'CSM_UTIL_PKG';
465 IF l_count = 0 THEN
466 -- csm_util_pkg.log(' synonym mobileadmin.csm_profile_pkg does not exist');
467 --EXECUTE IMMEDIATE 'create synonym mobileadmin.csm_util_pkg for csm_util_pkg';
468
469 EXECUTE IMMEDIATE 'create synonym '
470 ||g_olite_schema||'.csm_util_pkg for csm_util_pkg';
471 END IF;
472
473 RETURN 'Y';
474
475 EXCEPTION
476 WHEN OTHERS THEN
477 RETURN 'N';
478 END CHECK_OLITE_SCHEMA;
479
480 FUNCTION detect_conflict(p_user_name IN VARCHAR2) RETURN VARCHAR2 IS
481 BEGIN
482 CSM_UTIL_PKG.LOG('Entering CSM_SERVICEP_WRAPPER_PKG.DETECT_CONFLICT for user ' || p_user_name ,
483 'CSM_SERVICEP_WRAPPER_PKG.DETECT_CONFLICT',FND_LOG.LEVEL_PROCEDURE);
484
485 RETURN 'Y' ;
486 EXCEPTION
487 WHEN OTHERS THEN
488 RETURN 'Y';
489 END ;
490
491 FUNCTION CONFLICT_RESOLUTION_METHOD (p_user_name IN VARCHAR2,
492 p_tran_id IN NUMBER,
493 p_sequence IN NUMBER)
494 RETURN VARCHAR2 IS
495 l_profile_value VARCHAR2(30) ;
496 BEGIN
497 CSM_UTIL_PKG.LOG('Entering CSM_SERVICEP_WRAPPER_PKG.CONFLICT_RESOLUTION_METHOD for user ' || p_user_name ,
498 'CSM_SERVICEP_WRAPPER_PKG.CONFLICT_RESOLUTION_METHOD',FND_LOG.LEVEL_PROCEDURE);
499 l_profile_value := fnd_profile.value(csm_profile_pkg.g_JTM_APPL_CONFLICT_RULE);
500
501 if l_profile_value = 'SERVER_WINS' then
502 RETURN 'S' ;
503 else
504 RETURN 'C' ;
505 END IF ;
506 EXCEPTION
507 WHEN OTHERS THEN
508 RETURN 'C';
509 END ;
510
511
512
513 END CSM_SERVICEP_WRAPPER_PKG;
[Home] [Help]