1 PACKAGE BODY CSM_RESOURCE_EXTNS_EVENT_PKG AS
2 /* $Header: csmeresb.pls 120.7 2008/02/07 10:39:55 anaraman ship $ */
3
4 g_table_name1 CONSTANT VARCHAR2(30) := 'JTF_RS_RESOURCE_EXTNS';
5 g_acc_table_name1 CONSTANT VARCHAR2(30) := 'CSM_RS_RESOURCE_EXTNS_ACC';
6 g_acc_sequence_name1 CONSTANT VARCHAR2(30) := 'CSM_RS_RESOURCE_EXTNS_ACC_S';
7 g_publication_item_name1 CONSTANT CSM_ACC_PKG.t_publication_item_list :=
8 CSM_ACC_PKG.t_publication_item_list('CSF_M_EMPLOYEES');
9 g_pk1_name1 CONSTANT VARCHAR2(30) := 'RESOURCE_ID';
10
11 g_pub_item CONSTANT varchar(30) := 'CSF_M_EMPLOYEES';
12
13 l_markdirty_failed EXCEPTION;
14
15
16 PROCEDURE RESOURCE_EXTNS_ACC_I (p_resource_id IN NUMBER, p_user_id IN NUMBER )
17 IS
18 l_sqlerrno VARCHAR2(20);
19 l_sqlerrmsg VARCHAR2(4000);
20 l_error_msg VARCHAR2(4000);
21 l_return_status VARCHAR2(2000);
22
23 BEGIN
24 CSM_UTIL_PKG.LOG('Entering RESOURCE_EXTNS_ACC_I for resource_id: ' || p_resource_id,
25 'CSM_RESOURCE_EXTNS_EVENT_PKG.RESOURCE_EXTNS_ACC_I',FND_LOG.LEVEL_PROCEDURE);
26
27 CSM_ACC_PKG.Insert_Acc
28 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
29 , P_ACC_TABLE_NAME => g_acc_table_name1
30 , P_SEQ_NAME => g_acc_sequence_name1
31 , P_PK1_NAME => g_pk1_name1
32 , P_PK1_NUM_VALUE => p_resource_id
33 , P_USER_ID => p_user_id
34 );
35
36 CSM_UTIL_PKG.LOG('Leaving RESOURCE_EXTNS_ACC_I for resource_id: ' || p_resource_id,
37 'CSM_RESOURCE_EXTNS_EVENT_PKG.RESOURCE_EXTNS_ACC_I',FND_LOG.LEVEL_PROCEDURE);
38 EXCEPTION
39 WHEN OTHERS THEN
40 l_sqlerrno := to_char(SQLCODE);
41 l_sqlerrmsg := substr(SQLERRM, 1,2000);
42 l_error_msg := ' Exception in RESOURCE_EXTNS_ACC_I for resource_id:' || to_char(p_resource_id)
43 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
44 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_RESOURCE_EXTNS_EVENT_PKG.RESOURCE_EXTNS_ACC_I',FND_LOG.LEVEL_EXCEPTION);
45 RAISE;
46 END RESOURCE_EXTNS_ACC_I;
47
48 PROCEDURE RESOURCE_EXTNS_ACC_D (p_resource_id IN NUMBER, p_user_id IN NUMBER )
49 IS
50 l_sqlerrno VARCHAR2(20);
51 l_sqlerrmsg VARCHAR2(4000);
52 l_error_msg VARCHAR2(4000);
53 l_return_status VARCHAR2(2000);
54
55 BEGIN
56 CSM_UTIL_PKG.LOG('Entering RESOURCE_EXTNS_ACC_D for resource_id: ' || p_resource_id,
57 'CSM_RESOURCE_EXTNS_EVENT_PKG.RESOURCE_EXTNS_ACC_D',FND_LOG.LEVEL_PROCEDURE);
58
59 CSM_ACC_PKG.Delete_Acc
60 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
61 , P_ACC_TABLE_NAME => g_acc_table_name1
62 , P_PK1_NAME => g_pk1_name1
63 , P_PK1_NUM_VALUE => p_resource_id
64 , P_USER_ID => p_user_id
65 );
66
67 CSM_UTIL_PKG.LOG('Leaving RESOURCE_EXTNS_ACC_D for resource_id: ' || p_resource_id,
68 'CSM_RESOURCE_EXTNS_EVENT_PKG.RESOURCE_EXTNS_ACC_D',FND_LOG.LEVEL_PROCEDURE);
69 EXCEPTION
70 WHEN OTHERS THEN
71 l_sqlerrno := to_char(SQLCODE);
72 l_sqlerrmsg := substr(SQLERRM, 1,2000);
73 l_error_msg := ' Exception in RESOURCE_EXTNS_ACC_D for resource_id:' || to_char(p_resource_id)
74 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
75 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_RESOURCE_EXTNS_EVENT_PKG.RESOURCE_EXTNS_ACC_D',FND_LOG.LEVEL_EXCEPTION);
76 RAISE;
77 END RESOURCE_EXTNS_ACC_D;
78
79 --Bug 5236469
80 PROCEDURE RESOURCE_EXTNS_ACC_CLEANUP (p_user_id IN NUMBER)
81 IS
82 Cursor c_res(b_user_id NUMBER) IS
83 SELECT resource_id
84 FROM ASG_USER
85 WHERE useR_id=b_user_id;
86
87 Cursor c_delete(b_resource_id NUMBER) IS
88 SELECT user_id
89 FROM CSM_RS_RESOURCE_EXTNS_ACC
90 WHERE resource_id=b_resource_id;
91
92 l_sqlerrno VARCHAR2(20);
93 l_sqlerrmsg VARCHAR2(4000);
94 l_error_msg VARCHAR2(4000);
95 l_return_status VARCHAR2(2000);
96 l_resource_id NUMBER;
97
98
99 BEGIN
100 CSM_UTIL_PKG.LOG('Entering RESOURCE_EXTNS_ACC_CLEANUP for resource_id: ' || p_user_id,
101 'CSM_RESOURCE_EXTNS_EVENT_PKG.RESOURCE_EXTNS_ACC_CLEANUP',FND_LOG.LEVEL_PROCEDURE);
102
103 OPEN c_res(p_user_id);
104 FETCH c_res INTO l_resource_id;
105 CLOSE c_res;
106
107 FOR rec IN C_DELETE(l_resource_id)
108 LOOP
109 RESOURCE_EXTNS_ACC_D(l_resource_id,rec.user_id);
110 END LOOP;
111
112 --to remove old bad data from access table
113 DELETE FROM CSM_RS_RESOURCE_EXTNS_ACC WHERE resource_id=l_resource_id;
114
115 CSM_UTIL_PKG.LOG('Leaving RESOURCE_EXTNS_ACC_CLEANUP for resource_id: ' || p_user_id,
116 'CSM_RESOURCE_EXTNS_EVENT_PKG.RESOURCE_EXTNS_ACC_CLEANUP',FND_LOG.LEVEL_PROCEDURE);
117 EXCEPTION
118 WHEN OTHERS THEN
119 l_sqlerrno := to_char(SQLCODE);
120 l_sqlerrmsg := substr(SQLERRM, 1,2000);
121 l_error_msg := ' Exception in RESOURCE_EXTNS_ACC_CLEANUP for user_id:' || to_char(p_user_id)
122 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
123 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_RESOURCE_EXTNS_EVENT_PKG.RESOURCE_EXTNS_ACC_CLEANUP',FND_LOG.LEVEL_EXCEPTION);
124 RAISE;
125 END RESOURCE_EXTNS_ACC_CLEANUP;
126
127 --Bug 5236469
128 FUNCTION PROFILE_VALUE(p_user_id IN NUMBER) RETURN NUMBER
129 IS
130 CURSOR c_get_resp_id IS
131 SELECT APP_ID, RESPONSIBILITY_ID
132 FROM ASG_USER
133 WHERE USER_ID=p_user_id;
134
135 l_resp_id NUMBER;
136 l_app_id NUMBER;
137
138 BEGIN
139
140 OPEN c_get_resp_id;
141 FETCH c_get_resp_id INTO l_app_id,l_resp_id;
142 CLOSE c_get_resp_id;
143
144 RETURN fnd_profile.value_specific('CSF_M_RECIPIENTS_BOUNDARY', p_user_id,l_resp_id, l_app_id);
145
146 END PROFILE_VALUE;
147
148 --Bug 5236469
149 PROCEDURE PROCESS_USER (p_resource_id IN NUMBER, p_user_id IN NUMBER)
150 IS
151
152 CURSOR c_delete_prof0(p_resource_id NUMBER,p_user_id NUMBER) IS
153 SELECT resource_id
154 FROM CSM_RS_RESOURCE_EXTNS_ACC ACC
155 WHERE user_id=p_user_id
156 AND resource_id <> p_resource_id
157 AND NOT EXISTS (SELECT 1
158 FROM ASG_USER AU,
159 ASG_USER_PUB_RESPS AUPR
160 WHERE AU.RESOURCE_ID=ACC.RESOURCE_ID
161 AND AU.user_name=AUPR.user_name
162 AND AUPR.pub_name='SERVICEP'
163 AND AU.ENABLED='Y');
164
165 CURSOR c_insert_prof0(p_user_id NUMBER) IS
166 SELECT AU.resource_id
167 FROM ASG_USER AU,
168 ASG_USER_PUB_RESPS AUPR
169 WHERE AU.enabled='Y'
170 AND AU.user_name=AUPR.user_name
171 AND AUPR.pub_name='SERVICEP'
172 AND AU.user_id <> p_user_id
173 AND NOT EXISTS(SELECT 1 FROM CSM_RS_RESOURCE_EXTNS_ACC ACC
174 WHERE ACC.USER_ID=p_user_ID
175 AND ACC.RESOURCE_ID=AU.RESOURCE_ID);
176
177 --12.1
178 CURSOR c_grp_members(p_resource_id NUMBER, p_user_id NUMBER) IS --insert prof2
179 SELECT DISTINCT jtf_rs.user_id,
180 jtf_rs.resource_id
181 FROM jtf_rs_group_members jtf_rs_grp,
182 jtf_rs_resource_extns jtf_rs
183 WHERE EXISTS (SELECT 1
184 FROM jtf_rs_group_members
185 WHERE group_id = jtf_rs_grp.group_id
186 AND resource_id = p_resource_id
187 AND delete_flag = 'N')
188 AND jtf_rs.resource_id = jtf_rs_grp.resource_id
189 AND jtf_rs.resource_id <> p_resource_id
190 AND sysdate BETWEEN jtf_rs.start_date_active AND NVL(jtf_rs.end_date_active,sysdate)
191 AND jtf_rs.USER_ID IS NOT NULL
192 AND jtf_rs.USER_NAME IS NOT NULL
193 AND NOT EXISTS(SELECT 1 FROM CSM_RS_RESOURCE_EXTNS_ACC ACC
194 WHERE USER_ID=p_user_ID
195 AND ACC.RESOURCE_ID=jtf_rs.resource_id);
196
197 --12.1
198 CURSOR c_delete_prof2(p_resource_id NUMBER,p_user_id NUMBER) IS --delete prof2
199 SELECT resource_id
200 FROM CSM_RS_RESOURCE_EXTNS_ACC ACC
201 WHERE acc.user_id=p_user_id
202 AND NOT EXISTS ( SELECT 1
203 FROM jtf_rs_group_members jtf_rs_grp,
204 jtf_rs_resource_extns valid_fnd_user
205 WHERE EXISTS (SELECT 1
206 FROM jtf_rs_group_members
207 WHERE group_id = jtf_rs_grp.group_id
208 AND resource_id = p_resource_id
209 AND delete_flag = 'N')
210 AND jtf_rs_grp.resource_id= Acc.resource_id
211 AND valid_fnd_user.resource_id=Acc.resource_id
212 AND valid_fnd_user.USER_ID IS NOT NULL
213 AND valid_fnd_user.USER_NAME IS NOT NULL);
214
215 l_sqlerrno VARCHAR2(20);
216 l_sqlerrmsg VARCHAR2(4000);
217 l_error_msg VARCHAR2(4000);
218 l_return_status VARCHAR2(2000);
219
220 BEGIN
221 CSM_UTIL_PKG.LOG('Entering PROCESS_USER for resource_id: ' || p_resource_id,
222 'CSM_RESOURCE_EXTNS_EVENT_PKG.PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);
223
224 IF PROFILE_VALUE(p_user_id)=0 THEN --ALL MFS USERS
225
226 CSM_UTIL_PKG.LOG('DELETING FOR PROFILE-0 for user id : ' || p_user_id,
227 'CSM_RESOURCE_EXTNS_EVENT_PKG.PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);
228 FOR user_rec IN c_delete_prof0(p_resource_id,p_user_id)
229 LOOP
230 RESOURCE_EXTNS_ACC_D(user_rec.resource_id,p_user_id);
231 END LOOP;
232
233 CSM_UTIL_PKG.LOG('INSERTING FOR PROFILE-0 for user id : ' || p_user_id,
234 'CSM_RESOURCE_EXTNS_EVENT_PKG.PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);
235 FOR user_rec IN c_insert_prof0(p_user_id)
236 LOOP
237 RESOURCE_EXTNS_ACC_I(user_rec.resource_id,p_user_id);
238 END LOOP;
239
240 ELSE
241
242 CSM_UTIL_PKG.LOG('DELETING FOR PROFILE "1" for user id : ' || p_user_id,
243 'CSM_RESOURCE_EXTNS_EVENT_PKG.PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);
244
245 FOR user_rec IN c_delete_prof2(p_resource_id,p_user_id)
246 LOOP
247 RESOURCE_EXTNS_ACC_D(user_rec.resource_id,p_user_id);
248 END LOOP;
249
250 CSM_UTIL_PKG.LOG('INSERTING FOR PROFILE "1" for user id : ' || p_user_id,
251 'CSM_RESOURCE_EXTNS_EVENT_PKG.PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);
252
253 FOR user_rec IN c_grp_members(p_resource_id,p_user_id)
254 LOOP
255 RESOURCE_EXTNS_ACC_I(user_rec.resource_id,p_user_id);
256 END LOOP;
257
258 END IF;
259
260 CSM_UTIL_PKG.LOG('Leaving PROCESS_USER for resource_id: ' || p_resource_id,
261 'CSM_RESOURCE_EXTNS_EVENT_PKG.PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);
262 EXCEPTION
263 WHEN OTHERS THEN
264 l_sqlerrno := to_char(SQLCODE);
265 l_sqlerrmsg := substr(SQLERRM, 1,2000);
266 l_error_msg := ' Exception in PROCESS_USER for resource_id:' || to_char(p_resource_id)
267 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
268 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_RESOURCE_EXTNS_EVENT_PKG.PROCESS_USER',FND_LOG.LEVEL_EXCEPTION);
269 RAISE;
270 END PROCESS_USER;
271
272 --Bug 5236469 : CONCURRENT PROGRAM TO CAPTURE PROFILE UPDATES
273 PROCEDURE PROCESS_NOTIFICATION_SCOPE(p_status OUT NOCOPY VARCHAR2,p_message OUT NOCOPY VARCHAR2)
274 IS
275 PRAGMA AUTONOMOUS_TRANSACTION;
276
277 CURSOR c_palm_users IS
278 SELECT USER_ID,RESOURCE_ID
279 FROM ASG_USER au,
280 ASG_USER_PUB_RESPS aupr
281 WHERE au.USER_NAME=aupr.USER_NAME
282 AND au.USER_ID=au.OWNER_ID
283 AND au.ENABLED='Y'
284 AND aupr.PUB_NAME='SERVICEP';
285
286 l_sqlerrno VARCHAR2(20);
287 l_sqlerrmsg VARCHAR2(4000);
288 l_error_msg VARCHAR2(4000);
289 l_return_status VARCHAR2(2000);
290 l_scope NUMBER;
291 BEGIN
292 CSM_UTIL_PKG.LOG('Entering RESOURCE_EXTNS_ACC_CLEANUP',
293 'CSM_RESOURCE_EXTNS_EVENT_PKG.RESOURCE_EXTNS_ACC_CLEANUP',FND_LOG.LEVEL_PROCEDURE);
294
295 FOR r_users IN C_PALM_USERS
296 LOOP
297 Process_user(r_users.resource_id,r_users.user_id);
298 COMMIT;
299 END LOOP;
300
301 p_status := 'FINE';
302 p_message := 'CSM_RESOURCE_EXTNS_EVENT_PKG.PROCESS_NOTIFICATION_SCOPE executed successfully';
303
304 CSM_UTIL_PKG.LOG('Leaving PROCESS_NOTIFICATION_SCOPE',
305 'CSM_RESOURCE_EXTNS_EVENT_PKG.PROCESS_NOTIFICATION_SCOPE',FND_LOG.LEVEL_PROCEDURE);
306 EXCEPTION
307 WHEN OTHERS THEN
308 l_sqlerrno := to_char(SQLCODE);
309 l_sqlerrmsg := substr(SQLERRM, 1,2000);
310 l_error_msg := ' Exception in PROCESS_NOTIFICATION_SCOPE :' || l_sqlerrno || ':' || l_sqlerrmsg;
311 p_status := 'ERROR';
312 p_message := 'Error in CSM_RESOURCE_EXTNS_EVENT_PKG.PROCESS_NOTIFICATION_SCOPE: ' || l_sqlerrno || ':' || l_sqlerrmsg;
313 ROLLBACK;
314 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_RESOURCE_EXTNS_EVENT_PKG.PROCESS_NOTIFICATION_SCOPE',FND_LOG.LEVEL_EXCEPTION);
315 END PROCESS_NOTIFICATION_SCOPE;
316
317 /* RESOURCE_EXTNS_ACC_PROCESSOR
318 * ----------------------------
319 * Populate the CSM_RESOURCE_EXTNS_ACC table.
320 */
321 --Bug 5236469
322 PROCEDURE RESOURCE_EXTNS_ACC_PROCESSOR (p_resource_id IN NUMBER, p_user_id IN NUMBER)
323 IS
324 -- Cursor to get grp members
325 CURSOR c_grp_palm_members (p_resource_id jtf_rs_group_members.resource_id%TYPE) IS
326 SELECT DISTINCT au.user_id,
327 au.resource_id
328 FROM jtf_rs_group_members jtf_rs_grp,
329 ASG_user au,
330 ASG_USER_PUB_RESPS aupr
331 WHERE EXISTS (SELECT 1
332 FROM jtf_rs_group_members
333 WHERE group_id = jtf_rs_grp.group_id
334 AND resource_id = p_resource_id
335 AND delete_flag = 'N')
336 AND jtf_rs_grp.resource_id=au.resource_id
337 AND au.enabled='Y'
338 AND au.resource_id<>p_resource_id
339 AND au.USER_NAME=aupr.USER_NAME
340 AND aupr.PUB_NAME='SERVICEP';
341
342
343 CURSOR c_all_mfs_users(p_resource_id jtf_rs_group_members.resource_id%TYPE) IS
344 SELECT AU.user_id,AU.resource_id
345 FROM ASG_USER AU,
346 ASG_USER_PUB_RESPS aupr
347 WHERE AU.ENABLED='Y'
348 AND AU.resource_id<>p_resource_id
349 AND au.USER_NAME=aupr.USER_NAME
350 AND aupr.PUB_NAME='SERVICEP';
351
352
353 TYPE TABLE_TYPE is TABLE OF NUMBER INDEX BY VARCHAR2(80);
354 grp_mfs_user TABLE_TYPE;
355
356 l_sqlerrno VARCHAR2(20);
357 l_sqlerrmsg VARCHAR2(4000);
358 l_error_msg VARCHAR2(4000);
359 l_return_status VARCHAR2(2000);
360
361 BEGIN
362 CSM_UTIL_PKG.LOG('Entering RESOURCE_EXTNS_ACC_PROCESSOR for resource_id: ' || p_resource_id,
363 'CSM_RESOURCE_EXTNS_EVENT_PKG.RESOURCE_EXTNS_ACC_PROCESSOR',FND_LOG.LEVEL_PROCEDURE);
364
365 IF csm_util_pkg.is_palm_resource(p_resource_id) THEN
366 -- when the parameter RESOURCE_ID is a mobile user..also for him enabled='N' at the time of creation
367 ---the above api takes care of this.
368 --Insert his record to himself
369 RESOURCE_EXTNS_ACC_I(p_resource_id,p_user_id);
370
371 -- Insert all other users based on profile
372 PROCESS_USER(p_resource_id,p_user_id);
373
374 grp_mfs_user.delete;
375 FOR user_rec in c_grp_palm_members(p_resource_id)
376 LOOP
377 grp_mfs_user(to_char(user_rec.user_id)) :=user_rec.user_id;
378
379 -- insert to this user regardless of profile...since this guy is grp member and mfs user
380 RESOURCE_EXTNS_ACC_I(p_resource_id,user_rec.user_id);
381 END LOOP;
382
383 --for other non-grp mfs users if their profile_value =0 then insert
384 FOR user_rec IN c_all_mfs_users(p_resource_id)
385 LOOP
386 IF (NOT grp_mfs_user.EXISTS(to_char(user_rec.user_id))) AND PROFILE_VALUE(user_rec.user_id)=0 THEN
387 RESOURCE_EXTNS_ACC_I(p_resource_id,user_rec.user_id);
388 END IF;
389 END LOOP;
390
391 END IF;
392
393 CSM_UTIL_PKG.LOG('Leaving RESOURCE_EXTNS_ACC_PROCESSOR for resource_id: ' || p_resource_id,
394 'CSM_RESOURCE_EXTNS_EVENT_PKG.RESOURCE_EXTNS_ACC_PROCESSOR',FND_LOG.LEVEL_PROCEDURE);
395 EXCEPTION
396 WHEN OTHERS THEN
397 l_sqlerrno := to_char(SQLCODE);
398 l_sqlerrmsg := substr(SQLERRM, 1,2000);
399 l_error_msg := ' Exception in RESOURCE_EXTNS_ACC_PROCESSOR for resource_id:' || to_char(p_resource_id)
400 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
401 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_RESOURCE_EXTNS_EVENT_PKG.RESOURCE_EXTNS_ACC_PROCESSOR',FND_LOG.LEVEL_EXCEPTION);
402 RAISE;
403 END RESOURCE_EXTNS_ACC_PROCESSOR;
404
405 --Bug 5236469
406 PROCEDURE RS_GROUP_MEMBERS_INS_INIT(p_resource_id IN NUMBER, p_group_id IN NUMBER)
407 IS
408
409 CURSOR l_group_members_csr(p_group_id IN number, p_resource_id IN number)
410 IS
411 SELECT au.resource_id, au.user_id
412 FROM jtf_rs_group_members grp,
413 asg_user au,
414 asg_user_pub_resps aupr
415 WHERE grp.group_id = p_group_id
416 AND grp.resource_id = au.resource_id
417 AND grp.delete_flag = 'N'
418 AND au.enabled='Y'
419 AND au.USER_NAME=aupr.USER_NAME
420 AND aupr.PUB_NAME='SERVICEP'
421 AND grp.resource_id <> p_resource_id
422 AND EXISTS (SELECT 1 --12.1 -SHD BE A VALID FND USER
423 FROM JTF_RS_RESOURCE_EXTNS
424 WHERE RESOURCE_ID=p_resource_id
425 AND USER_NAME IS NOT NULL
426 AND USER_ID IS NOT NULL );
427
428
429 CURSOR l_resource_csr(p_resource_id jtf_rs_resource_extns.resource_id%TYPE)
430 IS
431 SELECT au.user_id
432 FROM asg_user au,
433 asg_user_pub_resps aupr
434 WHERE au.resource_id = p_resource_id
435 AND au.USER_NAME=aupr.USER_NAME
436 AND aupr.PUB_NAME='SERVICEP'
437 AND au.enabled='Y';
438
439 l_sqlerrno VARCHAR2(20);
440 l_sqlerrmsg VARCHAR2(4000);
441 l_error_msg VARCHAR2(4000);
442 l_return_status VARCHAR2(2000);
443
444 l_user_id fnd_user.user_id%TYPE := NULL;
445 BEGIN
446 CSM_UTIL_PKG.LOG('Entering RS_GROUP_MEMBERS_INS_INIT for resource_id: ' || to_char(p_resource_id) || ' and group_id: '
447 || TO_CHAR(p_group_id), 'CSM_RESOURCE_EXTNS_EVENT_PKG.RS_GROUP_MEMBERS_INS_INIT',FND_LOG.LEVEL_PROCEDURE);
448
449 -- get user_id
450 OPEN l_resource_csr(p_resource_id);
451 FETCH l_resource_csr INTO l_user_id;
452 CLOSE l_resource_csr;
453
454
455 -- add this resource to existing MFS members in the group
456 FOR r_group_members_rec IN l_group_members_csr(p_group_id, p_resource_id)
457 LOOP
458 IF l_user_id IS NOT NULL THEN --he's a palm user
459 IF PROFILE_VALUE(l_user_id)=2 THEN --0 means all palm users are already there so dont insert
460 RESOURCE_EXTNS_ACC_I(r_group_members_rec.resource_id,l_user_id);
461 END IF;
462 IF PROFILE_VALUE(r_group_members_rec.user_id)=2 THEN --0 means all palm users are already there so dont insert
463 RESOURCE_EXTNS_ACC_I(p_resource_id,r_group_members_rec.user_id);
464 END IF;
465 ELSE
466 IF PROFILE_VALUE(r_group_members_rec.user_id)=2 THEN --insert if profile is set at grp level
467 RESOURCE_EXTNS_ACC_I(p_resource_id,r_group_members_rec.user_id);
468 END IF;
469 END IF;
470 END LOOP;
471
472
473 CSM_UTIL_PKG.LOG('Leaving RS_GROUP_MEMBERS_INS_INIT for resource_id: ' || to_char(p_resource_id) || ' and group_id: '
474 || TO_CHAR(p_group_id), 'CSM_RESOURCE_EXTNS_EVENT_PKG.RS_GROUP_MEMBERS_INS_INIT',FND_LOG.LEVEL_PROCEDURE);
475 EXCEPTION
476 WHEN OTHERS THEN
477 l_sqlerrno := to_char(SQLCODE);
478 l_sqlerrmsg := substr(SQLERRM, 1,2000);
479 l_error_msg := ' Exception in RS_GROUP_MEMBERS_INS_INIT for resource_id:' || to_char(p_resource_id) || ' and group_id: '
480 || TO_CHAR(p_group_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
481 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_RESOURCE_EXTNS_EVENT_PKG.RS_GROUP_MEMBERS_INS_INIT',FND_LOG.LEVEL_EXCEPTION);
482 RAISE;
483 END RS_GROUP_MEMBERS_INS_INIT;
484
485 --Bug 5236469
486 PROCEDURE RS_GROUP_MEMBERS_DEL_INIT(p_resource_id IN NUMBER, p_group_id IN NUMBER)
487 IS
488
489 CURSOR l_group_members_csr(p_group_id IN number, p_resource_id IN number)
490 IS
491 SELECT au.resource_id, au.user_id
492 FROM jtf_rs_group_members grp,
493 asg_user au,
494 asg_user_pub_resps aupr
495 WHERE grp.group_id = p_group_id
496 AND grp.resource_id = au.resource_id
497 AND grp.delete_flag = 'N'
498 AND au.enabled='Y'
499 AND au.USER_NAME=aupr.USER_NAME
500 AND aupr.PUB_NAME='SERVICEP'
501 AND grp.resource_id <> p_resource_id
502 AND EXISTS (SELECT 1 --12.1 -SHD BE A VALID FND USER
503 FROM JTF_RS_RESOURCE_EXTNS
504 WHERE RESOURCE_ID=p_resource_id
505 AND USER_NAME IS NOT NULL
506 AND USER_ID IS NOT NULL);
507
508
509 CURSOR l_resource_csr(p_resource_id jtf_rs_resource_extns.resource_id%TYPE)
510 IS
511 SELECT au.user_id
512 FROM asg_user au,
513 asg_user_pub_resps aupr
514 WHERE au.resource_id = p_resource_id
515 AND au.USER_NAME=aupr.USER_NAME
516 AND aupr.PUB_NAME='SERVICEP'
517 AND au.enabled='Y';
518
519 l_sqlerrno VARCHAR2(20);
520 l_sqlerrmsg VARCHAR2(4000);
521 l_error_msg VARCHAR2(4000);
522 l_return_status VARCHAR2(2000);
523
524 l_user_id fnd_user.user_id%TYPE := NULL;
525
526 BEGIN
527 CSM_UTIL_PKG.LOG('Entering RS_GROUP_MEMBERS_DEL_INIT for resource_id: ' || to_char(p_resource_id) || ' and group_id: '
528 || TO_CHAR(p_group_id), 'CSM_RESOURCE_EXTNS_EVENT_PKG.RS_GROUP_MEMBERS_DEL_INIT',FND_LOG.LEVEL_PROCEDURE);
529
530 -- get the user_id
531 OPEN l_resource_csr(p_resource_id);
532 FETCH l_resource_csr INTO l_user_id;
533 CLOSE l_resource_csr;
534
535 -- delete this resource from existing MFS members in the group
536 FOR r_group_members_rec IN l_group_members_csr(p_group_id, p_resource_id)
537 LOOP
538 IF l_user_id IS NOT NULL THEN --he's a palm user
539 IF PROFILE_VALUE(l_user_id)=2 THEN --0 means all palm users shd be there so dont delete
540 RESOURCE_EXTNS_ACC_D(r_group_members_rec.resource_id,l_user_id);
541 END IF;
542 IF PROFILE_VALUE(r_group_members_rec.user_id)=2 THEN --0 means all palm users shd be there so dont delete
543 RESOURCE_EXTNS_ACC_D(p_resource_id,r_group_members_rec.user_id);
544 END IF;
545 ELSE --non palm user so delete if he leaves the grp and if the profile is set to grp level
546 IF PROFILE_VALUE(r_group_members_rec.user_id)=2 THEN
547 RESOURCE_EXTNS_ACC_D(p_resource_id,r_group_members_rec.user_id);
548 END IF;
549 END IF;
550 END LOOP;
551
552 CSM_UTIL_PKG.LOG('Leaving RS_GROUP_MEMBERS_DEL_INIT for resource_id: ' || to_char(p_resource_id) || ' and group_id: '
553 || TO_CHAR(p_group_id), 'CSM_RESOURCE_EXTNS_EVENT_PKG.RS_GROUP_MEMBERS_DEL_INIT',FND_LOG.LEVEL_PROCEDURE);
554 EXCEPTION
555 WHEN OTHERS THEN
556 l_sqlerrno := to_char(SQLCODE);
557 l_sqlerrmsg := substr(SQLERRM, 1,2000);
558 l_error_msg := ' Exception in RS_GROUP_MEMBERS_DEL_INIT for resource_id:' || to_char(p_resource_id) || ' and group_id: '
559 || TO_CHAR(p_group_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
560 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_RESOURCE_EXTNS_EVENT_PKG.RS_GROUP_MEMBERS_DEL_INIT',FND_LOG.LEVEL_EXCEPTION);
561 RAISE;
562 END RS_GROUP_MEMBERS_DEL_INIT;
563
564 END CSM_RESOURCE_EXTNS_EVENT_PKG;
[Home] [Help]