1 PACKAGE BODY CSL_JTF_RS_GRP_MEM_ACC_PKG AS
2 /* $Header: csljgacb.pls 120.0 2005/05/24 17:18:20 appldev noship $ */
3
4 /*** Globals ***/
5 g_acc_table_name CONSTANT VARCHAR2(30) := 'JTM_JTF_RS_GROUP_MEMBERS_ACC';
6 g_publication_item_name CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
7 JTM_HOOK_UTIL_PKG.t_publication_item_list('JTF_RS_GROUP_MEMBERS');
8 g_table_name CONSTANT VARCHAR2(30) := 'JTF_RS_GROUP_MEMBERS';
9 g_pk1_name CONSTANT VARCHAR2(30) := 'GROUP_MEMBER_ID';
10 g_debug_level NUMBER;
11
12
13 /*** Function that checks if group member should be replicated. Returns TRUE if it should ***/
14 FUNCTION Replicate_Record
15 ( p_group_member_id NUMBER
16 )
17 RETURN BOOLEAN
18 IS
19 /** Get the group Id for a given group member Id ***/
20 CURSOR c_GetGroupId( b_group_member_id NUMBER )
21 IS
22 SELECT group_id, resource_id
23 FROM jtf_rs_group_members
24 WHERE DELETE_FLAG = 'N'
25 AND group_member_id = b_group_member_id;
26
27 r_GetGroupId c_GetGroupId%ROWTYPE;
28
29 /** Get all resource ids that reside in a retrieved group id ***/
30 CURSOR c_GetResourceId( b_group_id NUMBER )
31 IS
32 SELECT resource_id
33 FROM jtf_rs_group_members
34 WHERE group_id = b_group_id
35 AND delete_flag = 'N';
36
37 r_GetResourceId c_GetResourceId%ROWTYPE;
38
39 /*** Initialise the following parameters ***/
40 l_group_found boolean := FALSE;
41 l_resource_is_mobile boolean := FALSE;
42 l_replicate boolean := FALSE;
43 BEGIN
44 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
45
46 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
47 THEN
48 jtm_message_log_pkg.Log_Msg
49 ( p_group_member_id
50 , g_table_name
51 , 'Entering Replicate_Record'
52 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
53 );
54 END IF;
55
56 /*** First retrieve the group id for de given group member id ***/
57 OPEN c_GetGroupId ( p_group_member_id );
58 FETCH c_GetGroupId INTO r_GetGroupId;
59 IF c_GetGroupId%FOUND
60 THEN
61 l_group_found:=TRUE;
62 ELSE
63 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
64 THEN
65 jtm_message_log_pkg.Log_Msg
66 ( p_group_member_id
67 , g_table_name
68 , 'Replicate_Record returned FALSE' || fnd_global.local_chr(10) ||
69 'Group member record was deleted'
70 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
71 );
72 END IF;
73 END IF;
74 CLOSE c_GetGroupId;
75
76 /*** Second retrieve all resource ids for de retrieved group id ***/
77 IF l_group_found
78 THEN
79 FOR r_GetResourceId IN c_GetResourceId( r_GetGroupId.group_id )
80 LOOP
81 /*** Then check if the resource from a retrieved group is a mobile resource ***/
82 IF JTM_HOOK_UTIL_PKG.isMobileFSresource( r_GetResourceId.resource_id )
83 THEN
84 l_resource_is_mobile := TRUE;
85 END IF;
86 END LOOP;
87 END IF;
88
89 IF l_resource_is_mobile
90 THEN
91 l_replicate := TRUE;
92 ELSE
93 jtm_message_log_pkg.Log_Msg
94 ( p_group_member_id
95 , g_table_name
96 , 'No mobile resources were found for this group id: ' || r_GetGroupId.group_id
97 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
98 );
99 END IF;
100
101 /** Record matched criteria -> return true ***/
102 RETURN l_replicate;
103 END Replicate_Record;
104
105 /*** Private procedure that inserts given group member related data for resource ***/
106 PROCEDURE Insert_ACC_Record
107 ( p_group_member_id IN NUMBER
108 ,p_resource_id IN NUMBER
109 )
110 IS
111 /*** cursor to retrieve group member details ***/
112 CURSOR c_group_member( b_group_member_id NUMBER ) IS
113 SELECT rgm.resource_id
114 , rxt.user_id
115 FROM jtf_rs_group_members rgm
116 , jtf_rs_resource_extns rxt
117 WHERE rgm.resource_id = rxt.resource_id
118 AND rgm.group_member_id = b_group_member_id;
119 r_group_member c_group_member%ROWTYPE;
120
121 BEGIN
122 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
123 THEN
124 jtm_message_log_pkg.Log_Msg
125 ( p_group_member_id
126 , g_table_name
127 , 'Entering Insert_ACC_Record'
128 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
129 );
130 END IF;
131
132 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
133 THEN
134 jtm_message_log_pkg.LOG_MSG
135 ( p_group_member_id
136 , g_table_name
137 , 'Inserting ACC record for resource id = ' || p_resource_id
138 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
139 END IF;
140
141 /** Insert the related resource id in Acc table **/
142 JTM_HOOK_UTIL_PKG.Insert_Acc
143 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
144 , P_ACC_TABLE_NAME => g_acc_table_name
145 , P_PK1_NAME => g_pk1_name
146 , P_PK1_NUM_VALUE => p_group_member_id
147 , P_RESOURCE_ID => p_resource_id
148 );
149
150 /*** retrieve group member resource_id and user_id ***/
151 OPEN c_group_member ( p_group_member_id );
152 FETCH c_group_member INTO r_group_member;
153 IF c_group_member%FOUND
154 THEN
155 /** Delete the resource extns ACC record **/
156 CSL_JTF_RESOURCE_EXTNS_ACC_PKG.Insert_Resource_Extns
157 ( r_group_member.resource_id
158 , p_resource_id
159 );
160
161 /*** Delete the fnd_user record ***/
162 CSL_FND_USER_ACC_PKG.Insert_User
163 ( r_group_member.user_id
164 , p_resource_id
165 );
166 END IF;
167 CLOSE c_group_member;
168
169 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
170 THEN
171 jtm_message_log_pkg.Log_Msg
172 ( p_group_member_id
173 , g_table_name
174 , 'Leaving Insert_ACC_Record'
175 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
176 );
177 END IF;
178 END Insert_ACC_Record;
179
180 /*** Private procedure that deletes group member from acc table ***/
181 PROCEDURE Delete_ACC_Record
182 ( p_group_member_id IN NUMBER
183 ,p_resource_id IN NUMBER
184 )
185 IS
186 /*** cursor to retrieve group member details ***/
187 CURSOR c_group_member( b_group_member_id NUMBER ) IS
188 SELECT rgm.resource_id
189 , rxt.user_id
190 FROM jtf_rs_group_members rgm
191 , jtf_rs_resource_extns rxt
192 WHERE rgm.resource_id = rxt.resource_id
193 AND rgm.group_member_id = b_group_member_id;
194 r_group_member c_group_member%ROWTYPE;
195
196 BEGIN
197 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
198 jtm_message_log_pkg.Log_Msg
199 ( p_group_member_id
200 , g_table_name
201 ,'Entering Delete_ACC_Record'
202 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
203 );
204 END IF;
205
206 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
207 jtm_message_log_pkg.Log_Msg
208 ( p_group_member_id
209 , g_table_name
210 , 'Deleting ACC record for p_resource_id = ' || p_resource_id
211 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
212 );
213 END IF;
214
215 /*** Delete group member ACC record ***/
216 JTM_HOOK_UTIL_PKG.Delete_Acc
217 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
218 , P_ACC_TABLE_NAME => g_acc_table_name
219 , P_PK1_NAME => g_pk1_name
220 , P_PK1_NUM_VALUE => p_group_member_id
221 , P_RESOURCE_ID => p_resource_id
222 );
223
224 /*** retrieve group member resource_id and user_id ***/
225 OPEN c_group_member ( p_group_member_id );
226 FETCH c_group_member INTO r_group_member;
227 IF c_group_member%FOUND
228 THEN
229 /** Delete the resource extns ACC record **/
230 CSL_JTF_RESOURCE_EXTNS_ACC_PKG.Delete_Resource_Extns
231 ( r_group_member.resource_id
232 , p_resource_id
233 );
234
235 /*** Delete the fnd_user record ***/
236 CSL_FND_USER_ACC_PKG.Delete_User
237 ( r_group_member.user_id
238 , p_resource_id
239 );
240 END IF;
241 CLOSE c_group_member;
242
243 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
244 jtm_message_log_pkg.Log_Msg
245 ( p_group_member_id
246 , g_table_name
247 , 'Leaving Delete_ACC_Record'
248 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
249 );
250 END IF;
251 END Delete_ACC_Record;
252
253 /*** Called after Group member Insert ***/
254 PROCEDURE POST_INSERT_RS_GROUP_MEMBER
255 (P_GROUP_MEMBER_ID IN JTF_RS_GROUP_MEMBERS.GROUP_MEMBER_ID%TYPE,
256 P_GROUP_ID IN JTF_RS_GROUP_MEMBERS.GROUP_ID%TYPE,
257 P_RESOURCE_ID IN JTF_RS_GROUP_MEMBERS.RESOURCE_ID%TYPE,
258 X_RETURN_STATUS OUT NOCOPY VARCHAR2
259 )
260 IS
261 CURSOR c_group_resources ( b_group_id NUMBER ) IS
262 SELECT group_member_id, resource_id
263 FROM jtf_rs_group_members
264 WHERE group_id = b_group_id
265 AND delete_flag = 'N';
266 r_group_resources c_group_resources%ROWTYPE;
267
268 l_is_mobile_resource BOOLEAN;
269 BEGIN
270 /*** get debug level ***/
271 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
272
273 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
274 THEN
275 jtm_message_log_pkg.Log_Msg
276 ( p_group_member_id
277 , g_table_name
278 , 'Entering POST_INSERT_RS_GROUP_MEMBER hook'
279 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
280 END IF;
281
282 /*** check if record needs to be replicated ***/
283 IF Replicate_Record( p_group_member_id ) THEN
284 /*** yes -> replicate record to mobile resources within group ***/
285 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
286 THEN
287 jtm_message_log_pkg.Log_Msg
288 ( p_group_member_id
289 , g_table_name
290 , 'Record needs to be replicated'
291 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
292 END IF;
293
294 /*** check if new member record is mobile resource ***/
295 IF JTM_HOOK_UTIL_PKG.isMobileFSresource( p_resource_id ) THEN
296 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
297 THEN
298 jtm_message_log_pkg.Log_Msg
299 ( p_group_member_id
300 , g_table_name
301 , 'New group member is mobile resource'
302 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
303 END IF;
304
305 l_is_mobile_resource := TRUE;
306 ELSE
307
308 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
309 THEN
310 jtm_message_log_pkg.Log_Msg
311 ( p_group_member_id
312 , g_table_name
313 , 'New group member is not a mobile resource'
314 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
315 END IF;
316
317 l_is_mobile_resource := FALSE;
318 END IF;
319
320 /***
321 Loop through all group members.
322 Replicate new record to all mobile resources in resource group.
323 If new group member is a mobile resource, then also replicate all
324 existing group member records to the mobile resource.
325 ***/
326 FOR r_group_resources IN c_group_resources( p_group_id )
327 LOOP
328 /*** is group member a mobile resource? ***/
329 IF JTM_HOOK_UTIL_PKG.isMobileFSresource( r_group_resources.resource_id )
330 THEN
331 /*** yes -> replicate new record to mobile resource ***/
332 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
333 THEN
334 jtm_message_log_pkg.Log_Msg
335 ( p_group_member_id
336 , g_table_name
337 , 'Replicating new member record to mobile resource_id = ' || r_group_resources.resource_id
338 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
339 END IF;
340
341 Insert_ACC_Record
342 ( p_group_member_id
343 ,r_group_resources.resource_id
344 );
345 END IF;
346
347 /*** is new group member record mobile resource? ***/
348 IF l_is_mobile_resource THEN
349 /***
350 yes -> is current record the new record?
351 We only replicate existing record to the new resource;
352 the new record will be replicated to the new resource
353 by the previous Insert_ACC_Record call (we don't need it twice).
354 ***/
355 IF r_group_resources.group_member_id <> p_group_member_id THEN
356 /*** no -> replicate existing member to new mobile group member ***/
357 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
358 THEN
359 jtm_message_log_pkg.Log_Msg
360 ( p_group_member_id
361 , g_table_name
362 , 'Replicating existing member record to new mobile resource group member. ' ||
363 fnd_global.local_chr(10) || 'group_member_id = ' || r_group_resources.group_member_id
364 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
365 END IF;
366
367 Insert_ACC_Record
368 ( r_group_resources.group_member_id
369 ,p_resource_id
370 );
371 END IF;
372 END IF;
373 END LOOP;
374 END IF;
375
376 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
377 THEN
378 jtm_message_log_pkg.Log_Msg
379 ( p_group_member_id
380 , g_table_name
381 , 'Leaving POST_INSERT_RS_GROUP_MEMBER hook'
382 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
383 END IF;
384 x_return_status := fnd_api.g_ret_sts_success;
385
386 EXCEPTION WHEN OTHERS THEN
387 /*** hook failed -> log error ***/
388 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
389 jtm_message_log_pkg.Log_Msg
390 ( p_group_member_id
391 , g_table_name
392 , 'Caught exception in POST_INSERT Procedure:' || fnd_global.local_chr(10) || sqlerrm
393 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
394 END IF;
395 x_return_status := FND_API.G_RET_STS_SUCCESS;
396
397 END POST_INSERT_RS_GROUP_MEMBER;
398
399 /* Called after Group member delete */
400 PROCEDURE PRE_DELETE_RS_GROUP_MEMBER
401 (P_GROUP_ID IN JTF_RS_GROUP_MEMBERS.GROUP_ID%TYPE,
402 P_RESOURCE_ID IN JTF_RS_GROUP_MEMBERS.RESOURCE_ID%TYPE,
403 X_RETURN_STATUS OUT NOCOPY VARCHAR2
404 )
405 IS
406 CURSOR c_group_resources ( b_group_id NUMBER ) IS
407 SELECT group_member_id, resource_id
408 FROM jtf_rs_group_members
409 WHERE group_id = b_group_id
410 AND delete_flag = 'N';
411 r_group_resources c_group_resources%ROWTYPE;
412
413 CURSOR c_group_member ( b_group_id NUMBER, b_resource_id NUMBER ) IS
414 SELECT group_member_id
415 FROM jtf_rs_group_members
416 WHERE group_id = b_group_id
417 AND resource_id = b_resource_id
418 AND delete_flag = 'N';
419 l_group_member_id jtf_rs_group_members.group_member_id%TYPE;
420
421 l_is_mobile_resource BOOLEAN;
422 BEGIN
423 /*** get debug level ***/
424 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
425
426 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
427 THEN
428 jtm_message_log_pkg.Log_Msg
429 ( null
430 , g_table_name
431 , 'Entering PRE_DELETE_RS_GROUP_MEMBER hook'
432 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
433 END IF;
434
435 /*** retrieve group_member_id for deleted member ***/
436 OPEN c_group_member( p_group_id, p_resource_id );
437 FETCH c_group_member INTO l_group_member_id;
438 IF c_group_member%NOTFOUND THEN
439 /*** could not find group member -> log error and exit ***/
440 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
441 THEN
442 jtm_message_log_pkg.Log_Msg
443 ( null
444 , g_table_name
445 , 'Could not find group member for group_id = ' || p_group_id || ', resource_id = ' || p_resource_id
446 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
447 END IF;
448 CLOSE c_group_member;
449 x_return_status := FND_API.G_RET_STS_SUCCESS;
450 RETURN;
451 END IF;
452 CLOSE c_group_member;
453
454 /*** is deleted group member a mobile resource? ***/
455 IF JTM_HOOK_UTIL_PKG.isMobileFSresource( p_resource_id ) THEN
456 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
457 THEN
458 jtm_message_log_pkg.Log_Msg
459 ( l_group_member_id
460 , g_table_name
461 , 'Deleted group member is mobile resource'
462 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
463 END IF;
464 l_is_mobile_resource := TRUE;
465 ELSE
466 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
467 THEN
468 jtm_message_log_pkg.Log_Msg
469 ( l_group_member_id
470 , g_table_name
471 , 'Deleted group member is not a mobile resource'
472 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
473 END IF;
474 l_is_mobile_resource := FALSE;
475 END IF;
476
477 /*** loop through group resources ***/
478 FOR r_group_resources IN c_group_resources ( p_group_id ) LOOP
479
480 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
481 THEN
482 jtm_message_log_pkg.Log_Msg
483 ( l_group_member_id
484 , g_table_name
485 , 'Processing group_member_id = ' || r_group_resources.group_member_id
486 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
487 END IF;
488
489 /*** is group member a mobile resource? ***/
490 IF JTM_HOOK_UTIL_PKG.isMobileFSresource( r_group_resources.resource_id )
491 THEN
492 /*** yes -> delete deleted member for mobile user ***/
493 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
494 THEN
495 jtm_message_log_pkg.Log_Msg
496 ( l_group_member_id
497 , g_table_name
498 , 'Deleting deleted member record for mobile resource_id = ' || r_group_resources.resource_id
499 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
500 END IF;
501
502 Delete_ACC_Record
503 ( l_group_member_id
504 ,r_group_resources.resource_id
505 );
506 END IF;
507
508 /*** Is deleted group member a mobile resource? ***/
509 IF l_is_mobile_resource THEN
510 /*** Yes -> has this record already been deleted above? ***/
511 IF l_group_member_id <> r_group_resources.group_member_id THEN
512 /*** No -> delete existing group member for deleted mobile resource ***/
513 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
514 THEN
515 jtm_message_log_pkg.Log_Msg
516 ( l_group_member_id
517 , g_table_name
518 , 'Deleting existing member record for deleted mobile group member'
519 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
520 END IF;
521
522 Delete_ACC_Record
523 ( r_group_resources.group_member_id
524 ,p_resource_id
525 );
526 END IF;
527 END IF;
528 END LOOP;
529
530 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
531 THEN
532 jtm_message_log_pkg.Log_Msg
533 ( l_group_member_id
534 , g_table_name
535 , 'Deleted group_member_id = ' || l_group_member_id
536 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
537 END IF;
538
539 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
540 THEN
541 jtm_message_log_pkg.Log_Msg
542 ( l_group_member_id
543 , g_table_name
544 , 'Leaving PRE_DELETE_RS_GROUP_MEMBER hook'
545 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
546 END IF;
547
548 x_return_status := FND_API.G_RET_STS_SUCCESS;
549
550 EXCEPTION WHEN OTHERS THEN
551 /*** hook failed -> log error ***/
552 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
553 jtm_message_log_pkg.Log_Msg
554 ( l_group_member_id
555 , g_table_name
556 , 'Caught exception in PRE_DELETE Procedure:' || fnd_global.local_chr(10) || sqlerrm
557 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
558 END IF;
559 x_return_status := FND_API.G_RET_STS_SUCCESS;
560
561 END PRE_DELETE_RS_GROUP_MEMBER;
562
563 /* Remove all ACC records of a mobile user */
564 PROCEDURE Delete_All_ACC_Records
565 ( p_resource_id in NUMBER
566 , x_return_status OUT NOCOPY varchar2
567 )
568 IS
569
570 CURSOR c_grp_mem_acc( b_resource_id NUMBER)
571 IS
572 SELECT acc.group_member_id
573 FROM jtm_jtf_rs_group_members_acc acc
574 WHERE acc.resource_id = b_resource_id;
575 r_grp_mem_acc c_grp_mem_acc%ROWTYPE;
576
577 BEGIN
578 /*** get debug level ***/
579 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
580
581 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
582 jtm_message_log_pkg.Log_Msg
583 ( null
584 , g_table_name
585 , 'Entering Delete_All_ACC_Records'
586 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
587 END IF;
588
589 /*** check if resource is fs laptop resource ***/
590 IF NOT JTM_HOOK_UTIL_PKG.isMobileFSresource( p_resource_id ) THEN
591 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
592 jtm_message_log_pkg.Log_Msg
593 ( null
594 , g_table_name
595 , 'Resource is not a mobile field service/laptop resource'
596 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
597 END IF;
598 -- x_return_status := FND_API.G_RET_STS_ERROR;
599 x_return_status := FND_API.G_RET_STS_SUCCESS;
600 ELSE
601 FOR r_grp_mem_acc IN c_grp_mem_acc( p_resource_id )
602 LOOP
603 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
604 jtm_message_log_pkg.Log_Msg
605 ( r_grp_mem_acc.group_member_id
606 , g_table_name
607 , 'Calling Delete_ACC_Record'
608 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
609 END IF;
610
611 Delete_ACC_Record
612 ( r_grp_mem_acc.group_member_id
613 ,p_resource_id
614 );
615 END LOOP;
616
617 x_return_status := FND_API.G_RET_STS_SUCCESS;
618 END IF;
619
620 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
621 jtm_message_log_pkg.Log_Msg
622 ( v_object_id => null
623 , v_object_name => g_table_name
624 , v_message => 'Leaving Delete_All_ACC_Records'
625 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
626 END IF;
627
628 EXCEPTION WHEN OTHERS THEN
629 /*** hook failed -> log error ***/
630 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
631 jtm_message_log_pkg.Log_Msg
632 ( null
633 , g_table_name
634 , 'Caught exception in Delete_All_ACC_Records:' || fnd_global.local_chr(10) || sqlerrm
635 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
636 END IF;
637
638 fnd_msg_pub.Add_Exc_Msg('CSL_JTF_RS_GRP_MEM_ACC_PKG','Delete_All_ACC_Records',sqlerrm);
639 -- x_return_status := FND_API.G_RET_STS_ERROR;
640 x_return_status := FND_API.G_RET_STS_SUCCESS;
641 END Delete_All_ACC_Records;
642
643 /* Full synch for a mobile user */
644 PROCEDURE Insert_All_ACC_Records
645 ( p_resource_id IN NUMBER
646 , x_return_status OUT NOCOPY VARCHAR2
647 )
648 IS
649 CURSOR c_jtf_rs_group_members (b_resource_id NUMBER) IS
650 SELECT group_member_id
651 FROM JTF_RS_GROUP_MEMBERS
652 WHERE delete_flag = 'N'
653 AND group_id IN (
654 SELECT group_id
655 FROM jtf_rs_group_members
656 WHERE delete_flag = 'N'
657 AND RESOURCE_ID = b_resource_id);
658 r_jtf_rs_group_members c_jtf_rs_group_members%ROWTYPE;
659 BEGIN
660 /*** get debug level ***/
661 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
662
663 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
664 jtm_message_log_pkg.Log_Msg
665 ( null
666 , g_table_name
667 , 'Entering Insert_All_ACC_Records'
668 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
669 END IF;
670
671 /*** check if resource is fs laptop resource ***/
672 IF NOT JTM_HOOK_UTIL_PKG.isMobileFSresource( p_resource_id ) THEN
673 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
674 jtm_message_log_pkg.Log_Msg
675 ( null
676 , g_table_name
677 , 'Resource is not a mobile field service/laptop resource'
678 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
679 END IF;
680 -- x_return_status := FND_API.G_RET_STS_ERROR;
681 x_return_status := FND_API.G_RET_STS_SUCCESS;
682 ELSE
683 /*** Yes -> insert all replicateable records ***/
684 FOR r_jtf_rs_group_members IN c_jtf_rs_group_members( p_resource_id )
685 LOOP
686 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
687 jtm_message_log_pkg.Log_Msg
688 ( r_jtf_rs_group_members.group_member_id
689 , g_table_name
690 , 'Calling Insert_ACC_Record'
691 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
692 END IF;
693
694 Insert_ACC_Record
695 ( r_jtf_rs_group_members.group_member_id
696 , p_resource_id
697 );
698 END LOOP;
699 x_return_status := FND_API.G_RET_STS_SUCCESS;
700 END IF;
701
702 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
703 jtm_message_log_pkg.Log_Msg
704 ( v_object_id => p_resource_id
705 , v_object_name => g_table_name
706 , v_message => 'Leaving Insert_All_ACC_Records procedure for user: ' || p_resource_id
707 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
708 END IF;
709
710 EXCEPTION WHEN OTHERS THEN
711 /*** hook failed -> log error ***/
712 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
713 jtm_message_log_pkg.Log_Msg
714 ( null
715 , g_table_name
716 , 'Caught exception in Delete_All_ACC_Records:' || fnd_global.local_chr(10) || sqlerrm
717 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
718 END IF;
719
720 fnd_msg_pub.Add_Exc_Msg('CSL_JTF_RS_GRP_MEM_ACC_PKG','Insert_All_ACC_Records',sqlerrm);
721 -- x_return_status := FND_API.G_RET_STS_ERROR;
722 x_return_status := FND_API.G_RET_STS_SUCCESS;
723 END Insert_All_ACC_Records;
724
725 END CSL_JTF_RS_GRP_MEM_ACC_PKG;
[Home] [Help]