1 PACKAGE BODY jtf_rs_grp_membership_pub AS
2 /* $Header: jtfrsrmb.pls 120.0 2005/05/11 08:21:40 appldev ship $ */
3
4 /*****************************************************************************************
5 ******************************************************************************************/
6
7 /* Package variables. */
8
9 G_PKG_NAME VARCHAR2(30) := 'JTF_RS_GRP_MEMBERSHIP_PUB';
10
11
12
13 PROCEDURE create_group_membership
14 (P_API_VERSION IN NUMBER,
15 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT FND_API.G_FALSE,
16 P_COMMIT IN VARCHAR2 DEFAULT FND_API.G_FALSE,
17 P_RESOURCE_ID IN NUMBER,
18 P_GROUP_ID IN NUMBER,
19 P_ROLE_ID IN NUMBER,
20 P_START_DATE IN DATE,
21 P_END_DATE IN DATE DEFAULT NULL,
22 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
23 X_MSG_COUNT OUT NOCOPY NUMBER,
24 X_MSG_DATA OUT NOCOPY VARCHAR2
25 ) IS
26
27 l_api_version constant number := 1.0;
28 l_api_name constant varchar2(30) := 'CREATE_GROUP_MEMBERSHIP';
29 l_return_status varchar2(100) := fnd_api.g_ret_sts_success;
30 l_msg_count number;
31 l_msg_data varchar2(2000);
32
33 /* Out Parameters for Create Role Relation*/
34
35 l_role_relate_id number;
36 l_role_relate_id_old number;
37 l_object_version_number number;
38 l_object_version_number_old number;
39 l_start_date_active date;
40 l_end_date_active date;
41 l_resource_start_date date;
42 l_resource_end_date date;
43 l_resource_name varchar2(240);
44 l_group_name varchar2(240);
45
46 /* Out Parameters for Create Group Member*/
47
48 l_group_member_id number;
49
50 /* Cursor Variables to get role realtions */
51
52 cursor chk_role_relate(l_role_id number, l_resource_id number)
53 is
54 select role_relate_id,start_date_active,end_date_active,object_version_number
55 from jtf_rs_role_relations
56 where role_id = l_role_id
57 and role_resource_id = l_resource_id
58 and role_resource_type = 'RS_INDIVIDUAL'
59 and nvl(delete_flag, 'N') <> 'Y'
60 order by start_date_active desc;
61
62 cursor get_resource_dates(l_resource_id number)
63 is
64 select start_date_active,
65 end_date_active,
66 resource_name
67 from jtf_rs_resource_extns_vl
68 where resource_id = l_resource_id;
69
70 cursor get_group_name(l_group_id number)
71 is
72 select group_name
73 from jtf_rs_groups_vl
74 where group_id = l_group_id;
75
76 /* Cursor Variables to check group member exists or not */
77
78 cursor check_group_member_exists(l_group_id number, l_resource_id number)
79 is
80 select group_member_id
81 from jtf_rs_group_members
82 where group_id = l_group_id
83 and resource_id = l_resource_id
84 and nvl(delete_flag, 'N') <> 'Y';
85
86 cursor role_type_dtl(l_role_id number)
87 is
88 select trunc(lkp.start_date_active),
89 trunc(lkp.end_date_active)
90 from fnd_lookups lkp, jtf_rs_roles_b rol
91 where rol.role_id = l_role_id
92 and lkp.lookup_type = 'JTF_RS_ROLE_TYPE'
93 and lkp.lookup_code = rol.role_type_code;
94
95 l_role_type_start_date date;
96 l_role_type_end_date date;
97 l_new_role_start_date date;
98 l_new_role_end_date date;
99
100 BEGIN
101 x_return_status := fnd_api.g_ret_sts_success;
102
103 savepoint cr_grp_memship;
104
105 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
106 if fnd_api.tO_BOOLEAN(p_init_msg_list)
107 then
108 fnd_msg_pub.Initialize;
109 end if;
110
111 open get_resource_dates(p_resource_id);
112 fetch get_resource_dates
113 into l_resource_start_date,
114 l_resource_end_date,
115 l_resource_name;
116 close get_resource_dates;
117
118 if (p_role_id is not null) then
119
120 open role_type_dtl(p_role_id);
121 fetch role_type_dtl
122 into l_role_type_start_date,
123 l_role_type_end_date;
124 close role_type_dtl;
125
126 open chk_role_relate(p_role_id,p_resource_id);
127 fetch chk_role_relate
128 into l_role_relate_id,
129 l_start_date_active,
130 l_end_date_active,
131 l_object_version_number;
132 if chk_role_relate%NOTFOUND
133 then
134 --dbms_output.put_line(' resource id = ' || to_char(p_resource_id));
135 --dbms_output.put_line(' role id = ' || to_char(p_role_id));
136 /* Calling the role relate api's to create resource role relation */
137
138 /* Added the following if condition to fix bug # 2941784 (suggested by Hari)
139 After deriving the date effectivity for resource role membership,
140 the below condition will look for role type date effectivity also.
141 Resource role membership start date will be the greatest of derived start date and role type start date.
142 Resource role membership end date will be the least of derived end date and role type end date. */
143
144 if ( l_role_type_end_date is NULL OR
145 ( trunc(l_role_type_end_date) >= trunc(l_resource_start_date))) then
146
147 l_new_role_start_date := greatest(l_resource_start_date, l_role_type_start_date);
148
149 if (l_resource_end_date is NULL and l_role_type_end_date is NULL) then
150 l_new_role_end_date := l_resource_end_date;
151 elsif (l_resource_end_date is NULL and l_role_type_end_date is NOT NULL) then
152 l_new_role_end_date := l_role_type_end_date;
153 elsif (l_resource_end_date is NOT NULL and l_role_type_end_date is NULL) then
154 l_new_role_end_date := l_resource_end_date;
155 else
156 l_new_role_end_date := least(l_resource_end_date, l_role_type_end_date);
157 end if;
158 else
159 l_new_role_start_date := l_resource_start_date;
160 l_new_role_end_date := l_resource_end_date;
161 end if;
162
163 jtf_rs_role_relate_pub.create_resource_role_relate
164 (p_api_version => 1.0
165 ,p_init_msg_list => fnd_api.g_false
166 ,p_commit => fnd_api.g_false
167 ,p_role_resource_type => 'RS_INDIVIDUAL'
168 ,p_role_resource_id => p_resource_id
169 ,p_role_id => p_role_id
170 ,p_role_code => null
171 ,p_start_date_active => l_new_role_start_date
172 ,p_end_date_active => l_new_role_end_date
173 ,x_return_status => l_return_status
174 ,x_msg_count => l_msg_count
175 ,x_msg_data => l_msg_data
176 ,x_role_relate_id => l_role_relate_id
177 );
178 if not (l_return_status = fnd_api.g_ret_sts_success) THEN
179 raise fnd_api.g_exc_unexpected_error;
180 end if;
181
182 else
183
184 if (p_start_date >= l_start_date_active) and (p_start_date <= nvl(l_end_date_active,p_start_date))
185 then
186 if ((p_end_date > l_end_date_active) or p_end_date is NULL) and (l_end_date_active is NOT NULL)
187 then
188 -- dbms_output.put_line('update the role end date with ' ||to_char(l_resource_end_date));
189
190 /* Added the following if condition to fix bug # 2941784 (suggested by Hari)
191 After deriving the new end date of resource role membership,
192 the below condition will look for role type date effectivity also.
193 Resource role membership end date will be the least of derived end date and role type end date. */
194
195 l_new_role_start_date := l_start_date_active;
196
197 if (l_resource_end_date is NULL and l_role_type_end_date is NULL) then
198 l_new_role_end_date := l_resource_end_date;
199 elsif (l_resource_end_date is NULL and l_role_type_end_date is NOT NULL) then
200 l_new_role_end_date := l_role_type_end_date;
201 elsif (l_resource_end_date is NOT NULL and l_role_type_end_date is NULL) then
202 l_new_role_end_date := l_resource_end_date;
203 else
204 l_new_role_end_date := least(l_resource_end_date, l_role_type_end_date);
205 end if;
206
207 jtf_rs_role_relate_pvt.update_resource_role_relate
208 (P_API_VERSION => 1.0,
209 P_INIT_MSG_LIST => fnd_api.g_false,
210 P_COMMIT => fnd_api.g_false,
211 P_ROLE_RELATE_ID => l_role_relate_id,
212 P_START_DATE_ACTIVE => l_new_role_start_date,
213 P_END_DATE_ACTIVE => l_new_role_end_date,
214 P_OBJECT_VERSION_NUM => l_object_version_number,
215 X_RETURN_STATUS => l_return_status,
216 X_MSG_COUNT => l_msg_count,
217 X_MSG_DATA => l_msg_data);
218 if not (l_return_status = fnd_api.g_ret_sts_success) THEN
219 raise fnd_api.g_exc_unexpected_error;
220 end if;
221 else
222 -- dbms_output.put_line('no need to update the role');
223 null;
224 end if;
225 elsif (p_start_date >= l_start_date_active)
226 then
227 -- dbms_output.put_line('update the role end date with ' ||to_char(l_resource_end_date));
228
229 /* Added the following if condition to fix bug # 2941784 (suggested by Hari)
230 After deriving the end date of resource role membership,
231 the below condition will look for role type date effectivity also.
232 Resource role membership end date will be the least of derived end date and role type end date. */
233
234 l_new_role_start_date := l_start_date_active;
235
236 if (l_resource_end_date is NULL and l_role_type_end_date is NULL) then
237 l_new_role_end_date := l_resource_end_date;
238 elsif (l_resource_end_date is NULL and l_role_type_end_date is NOT NULL) then
239 l_new_role_end_date := l_role_type_end_date;
240 elsif (l_resource_end_date is NOT NULL and l_role_type_end_date is NULL) then
241 l_new_role_end_date := l_resource_end_date;
242 else
243 l_new_role_end_date := least(l_resource_end_date, l_role_type_end_date);
244 end if;
245
246 jtf_rs_role_relate_pvt.update_resource_role_relate
247 (P_API_VERSION => 1.0,
248 P_INIT_MSG_LIST => fnd_api.g_false,
249 P_COMMIT => fnd_api.g_false,
250 P_ROLE_RELATE_ID => l_role_relate_id,
251 P_START_DATE_ACTIVE => l_new_role_start_date,
252 P_END_DATE_ACTIVE => l_new_role_end_date,
253 P_OBJECT_VERSION_NUM => l_object_version_number,
254 X_RETURN_STATUS => l_return_status,
255 X_MSG_COUNT => l_msg_count,
256 X_MSG_DATA => l_msg_data);
257 if not (l_return_status = fnd_api.g_ret_sts_success) THEN
258 raise fnd_api.g_exc_unexpected_error;
259 end if;
260 elsif (p_start_date < l_start_date_active)
261 then
262 fetch chk_role_relate into l_role_relate_id_old,l_start_date_active,l_end_date_active,l_object_version_number_old;
263 if chk_role_relate%NOTFOUND
264 then
265 l_start_date_active := l_resource_start_date;
266 else
267 l_start_date_active := l_end_date_active+1;
268 end if;
269 -- dbms_output.put_line('update role start date with'|| to_char(l_end_date_active+1) || ' the role end date with'||to_char(l_resource_end_date));
270
271 /* Added the following if condition to fix bug # 2941784 (suggested by Hari)
272 After deriving the start date and end date resource role membership,
273 the below condition will look for role type date effectivity also.
274 Resource role membership start date will be the greatest of derived start date and role type start date.
275 Resource role membership end date will be the least of derived end date and role type end date. */
276
277 if ( l_role_type_end_date is NULL OR
278 ( trunc(l_role_type_end_date) >= trunc(l_start_date_active))) then
279
280 l_new_role_start_date := greatest(l_start_date_active, l_role_type_start_date);
281
282 if (l_resource_end_date is NULL and l_role_type_end_date is NULL) then
283 l_new_role_end_date := l_resource_end_date;
284 elsif (l_resource_end_date is NULL and l_role_type_end_date is NOT NULL) then
285 l_new_role_end_date := l_role_type_end_date;
286 elsif (l_resource_end_date is NOT NULL and l_role_type_end_date is NULL) then
287 l_new_role_end_date := l_resource_end_date;
288 else
289 l_new_role_end_date := least(l_resource_end_date, l_role_type_end_date);
290 end if;
291 else
292 l_new_role_start_date := l_start_date_active;
293 l_new_role_end_date := l_resource_end_date;
294 end if;
295
296 jtf_rs_role_relate_pvt.update_resource_role_relate
297 (P_API_VERSION => 1.0,
298 P_INIT_MSG_LIST => fnd_api.g_false,
299 P_COMMIT => fnd_api.g_false,
300 P_ROLE_RELATE_ID => l_role_relate_id,
301 P_START_DATE_ACTIVE => l_new_role_start_date,
302 P_END_DATE_ACTIVE => l_new_role_end_date,
303 P_OBJECT_VERSION_NUM => l_object_version_number,
304 X_RETURN_STATUS => l_return_status,
305 X_MSG_COUNT => l_msg_count,
306 X_MSG_DATA => l_msg_data);
307 if not (l_return_status = fnd_api.g_ret_sts_success) THEN
308 raise fnd_api.g_exc_unexpected_error;
309 end if;
310
311 end if;
312
313 end if;
314
315 close chk_role_relate;
316 end if;
317 --dbms_output.put_line(' resource role_relate id = ' || to_char(l_role_relate_id));
318
319 open check_group_member_exists(p_group_id, p_resource_id);
320 fetch check_group_member_exists
321 into l_group_member_id;
322 if check_group_member_exists%NOTFOUND
323 then
324
325 jtf_rs_group_members_pub.create_resource_group_members
326 (p_api_version => 1.0
327 ,p_init_msg_list => fnd_api.g_false
328 ,p_commit => fnd_api.g_false
329 ,p_group_id => p_group_id
330 ,p_group_number => null
331 ,p_resource_id => p_resource_id
332 ,p_resource_number => null
333 ,x_return_status => l_return_status
334 ,x_msg_count => l_msg_count
335 ,x_msg_data => l_msg_data
336 ,x_group_member_id => l_group_member_id
337 );
338
339 if not (l_return_status = fnd_api.g_ret_sts_success) THEN
340 raise fnd_api.g_exc_unexpected_error;
341 end if;
342 else
343 if (p_role_id is null) then
344 open get_group_name(p_group_id);
345 fetch get_group_name
346 into l_group_name;
347 close get_group_name;
348
349 -- hk_debug_proc(l_resource_name ||' is already a member of '||l_group_name);
350
351 fnd_message.set_name('JTF', 'JTF_RS_RESOURCE_EXISTS_GROUP');
352 fnd_message.set_token('P_RESOURCE',l_resource_name);
353 fnd_message.set_token('P_GROUP',l_group_name);
354 fnd_msg_pub.add;
355 RAISE fnd_api.g_exc_unexpected_error;
356
357 end if;
358 end if;
359 --dbms_output.put_line('group_member_id id = ' || to_char(l_group_member_id));
360
361 /* Calling the role relate api's to create resource role relation */
362 if (p_role_id is not null) then
363 jtf_rs_role_relate_pub.create_resource_role_relate
364 (p_api_version => 1.0
365 ,p_init_msg_list => fnd_api.g_false
366 ,p_commit => fnd_api.g_false
367 ,p_role_resource_type => 'RS_GROUP_MEMBER'
368 ,p_role_resource_id => l_group_member_id
369 ,p_role_id => p_role_id
370 ,p_role_code => null
371 ,p_start_date_active => p_start_date
372 ,p_end_date_active => p_end_date
373 ,x_return_status => l_return_status
374 ,x_msg_count => l_msg_count
375 ,x_msg_data => l_msg_data
376 ,x_role_relate_id => l_role_relate_id
377 );
378
379 if not (l_return_status = fnd_api.g_ret_sts_success) THEN
380 raise fnd_api.g_exc_unexpected_error;
381 end if;
382 end if;
383
384 --dbms_output.put_line(' group role_relate id = ' || to_char(l_role_relate_id));
385
386 if fnd_api.to_boolean(p_commit)
387 then
388 commit work;
389 end if;
390
391 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
392
393 exception
394 when fnd_api.g_exc_unexpected_error
395 then
396 rollback to cr_grp_memship;
397 x_return_status := fnd_api.g_ret_sts_error;
398 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
399 WHEN fnd_api.g_exc_error
400 THEN
401 ROLLBACK TO cr_grp_memship;
402 x_return_status := fnd_api.g_ret_sts_error;
403 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
404 when others
405 then
406 rollback to cr_grp_memship;
407 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
408 fnd_message.set_token('P_SQLCODE',SQLCODE);
409 fnd_message.set_token('P_SQLERRM',SQLERRM);
410 fnd_message.set_token('P_API_NAME',l_api_name);
411 FND_MSG_PUB.add;
412 x_return_status := fnd_api.g_ret_sts_unexp_error;
413 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
414
415 END create_group_membership;
416
417 PROCEDURE update_group_membership
418 (P_API_VERSION IN NUMBER,
419 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT FND_API.G_FALSE,
420 P_COMMIT IN VARCHAR2 DEFAULT FND_API.G_FALSE,
421 P_RESOURCE_ID IN NUMBER,
422 P_ROLE_ID IN NUMBER,
423 P_ROLE_RELATE_ID IN NUMBER,
424 P_START_DATE IN DATE DEFAULT FND_API.G_MISS_DATE,
425 P_END_DATE IN DATE DEFAULT FND_API.G_MISS_DATE,
426 P_OBJECT_VERSION_NUM IN NUMBER,
427 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
428 X_MSG_COUNT OUT NOCOPY NUMBER,
429 X_MSG_DATA OUT NOCOPY VARCHAR2
430 ) IS
431
432 l_api_version constant number := 1.0;
433 l_api_name constant varchar2(30) := 'UPDATE_GROUP_MEMBERSHIP';
434 l_return_status varchar2(100) := fnd_api.g_ret_sts_success;
435 l_msg_count number;
436 l_msg_data varchar2(2000);
437 l_object_version_num number := p_object_version_num;
438
439 /* Out Parameters for Create Role Relation*/
440
441 l_role_relate_id number;
442 l_role_relate_id_old number;
443 l_object_version_number number;
444 l_object_version_number_old number;
445 l_start_date_active date;
446 l_end_date_active date;
447 l_resource_start_date date;
448 l_resource_end_date date;
449
450 /* Cursor Variables to get role realtions */
451
452 cursor chk_role_relate(l_role_id number, l_resource_id number)
453 is
454 select role_relate_id,start_date_active,end_date_active,object_version_number
455 from jtf_rs_role_relations
456 where role_id = l_role_id
457 and role_resource_id = l_resource_id
458 and role_resource_type = 'RS_INDIVIDUAL'
459 and nvl(delete_flag, 'N') <> 'Y'
460 order by start_date_active desc;
461
462 cursor get_resource_dates(l_resource_id number)
463 is
464 select start_date_active,
465 end_date_active
466 from jtf_rs_resource_extns
467 where resource_id = l_resource_id;
468
469 cursor role_type_dtl(l_role_id number)
470 is
471 select trunc(lkp.start_date_active),
472 trunc(lkp.end_date_active)
473 from fnd_lookups lkp, jtf_rs_roles_b rol
474 where rol.role_id = l_role_id
475 and lkp.lookup_type = 'JTF_RS_ROLE_TYPE'
476 and lkp.lookup_code = rol.role_type_code;
477
478 l_role_type_start_date date;
479 l_role_type_end_date date;
480 l_new_role_start_date date;
481 l_new_role_end_date date;
482
483 BEGIN
484 x_return_status := fnd_api.g_ret_sts_success;
485
486 savepoint upd_grp_memship;
487
488 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
489 if fnd_api.tO_BOOLEAN(p_init_msg_list)
490 then
491 fnd_msg_pub.Initialize;
492 end if;
493
494 open get_resource_dates(p_resource_id);
495 fetch get_resource_dates
496 into l_resource_start_date,
497 l_resource_end_date;
498 close get_resource_dates;
499
500 open role_type_dtl(p_role_id);
501 fetch role_type_dtl
502 into l_role_type_start_date,
503 l_role_type_end_date;
504 close role_type_dtl;
505
506 open chk_role_relate(p_role_id,p_resource_id);
507 fetch chk_role_relate
508 into l_role_relate_id,
509 l_start_date_active,
510 l_end_date_active,
511 l_object_version_number;
512
513 if (p_start_date >= l_start_date_active) and (p_start_date <= nvl(l_end_date_active,p_start_date))
514 then
515 if (p_end_date > l_end_date_active) and (l_end_date_active is NOT NULL)
516 then
517 -- dbms_output.put_line('update the role end date with ' ||to_char(l_resource_end_date));
518
519 /* Added the following if condition to fix bug # 2941784 (suggested by Hari)
520 After deriving the end date of resource role membership,
521 the below condition will look for role type date effectivity also.
522 Resource role membership end date will be the least of derived end date and role type end date. */
523
524 l_new_role_start_date := l_start_date_active;
525
526 if (l_resource_end_date is NULL and l_role_type_end_date is NULL) then
527 l_new_role_end_date := l_resource_end_date;
528 elsif (l_resource_end_date is NULL and l_role_type_end_date is NOT NULL) then
529 l_new_role_end_date := l_role_type_end_date;
530 elsif (l_resource_end_date is NOT NULL and l_role_type_end_date is NULL) then
531 l_new_role_end_date := l_resource_end_date;
532 else
533 l_new_role_end_date := least(l_resource_end_date, l_role_type_end_date);
534 end if;
535
536 jtf_rs_role_relate_pub.update_resource_role_relate
537 (P_API_VERSION => 1.0,
538 P_INIT_MSG_LIST => fnd_api.g_false,
539 P_COMMIT => fnd_api.g_false,
540 P_ROLE_RELATE_ID => l_role_relate_id,
541 P_START_DATE_ACTIVE => l_new_role_start_date,
542 P_END_DATE_ACTIVE => l_new_role_end_date,
543 P_OBJECT_VERSION_NUM => l_object_version_number,
544 X_RETURN_STATUS => l_return_status,
545 X_MSG_COUNT => l_msg_count,
546 X_MSG_DATA => l_msg_data);
547 if not (l_return_status = fnd_api.g_ret_sts_success) THEN
548 raise fnd_api.g_exc_unexpected_error;
549 end if;
550 else
551 -- dbms_output.put_line('no need to update the role');
552 null;
553 end if;
554 elsif (p_start_date >= l_start_date_active)
555 then
556 -- dbms_output.put_line('update the role end date with ' ||to_char(l_resource_end_date));
557
558 /* Added the following if condition to fix bug # 2941784 (suggested by Hari)
559 After deriving the end date of resource role membership,
560 the below condition will look for role type date effectivity also.
561 Resource role membership end date will be the least of derived end date and role type end date. */
562
563 l_new_role_start_date := l_start_date_active;
564
565 if (l_resource_end_date is NULL and l_role_type_end_date is NULL) then
566 l_new_role_end_date := l_resource_end_date;
567 elsif (l_resource_end_date is NULL and l_role_type_end_date is NOT NULL) then
568 l_new_role_end_date := l_role_type_end_date;
569 elsif (l_resource_end_date is NOT NULL and l_role_type_end_date is NULL) then
570 l_new_role_end_date := l_resource_end_date;
571 else
572 l_new_role_end_date := least(l_resource_end_date, l_role_type_end_date);
573 end if;
574
575 jtf_rs_role_relate_pub.update_resource_role_relate
576 (P_API_VERSION => 1.0,
577 P_INIT_MSG_LIST => fnd_api.g_false,
578 P_COMMIT => fnd_api.g_false,
579 P_ROLE_RELATE_ID => l_role_relate_id,
580 P_START_DATE_ACTIVE => l_new_role_start_date,
581 P_END_DATE_ACTIVE => l_new_role_end_date,
582 P_OBJECT_VERSION_NUM => l_object_version_number,
583 X_RETURN_STATUS => l_return_status,
584 X_MSG_COUNT => l_msg_count,
585 X_MSG_DATA => l_msg_data);
586 if not (l_return_status = fnd_api.g_ret_sts_success) THEN
587 raise fnd_api.g_exc_unexpected_error;
588 end if;
589 elsif (p_start_date < l_start_date_active)
590 then
591 fetch chk_role_relate into l_role_relate_id_old,l_start_date_active,l_end_date_active,l_object_version_number_old;
592 if chk_role_relate%NOTFOUND
593 then
594 l_start_date_active := l_resource_start_date;
595 else
596 l_start_date_active := l_end_date_active+1;
597 end if;
598 -- dbms_output.put_line('update role start date with'|| to_char(l_end_date_active+1) || ' the role end date with'||to_char(l_resource_end_date));
599
600 /* Added the following if condition to fix bug # 2941784 (suggested by Hari)
601 After deriving the start date and end date of resource role membership,
602 the below condition will look for role type date effectivity also.
603 Resource role membership start date will be the greatest of derived start date and role type start date.
604 Resource role membership end date will be the least of derived end date and role type end date. */
605
606 if ( l_role_type_end_date is NULL OR
607 ( trunc(l_role_type_end_date) >= trunc(l_start_date_active))) then
608
609 l_new_role_start_date := greatest(l_start_date_active, l_role_type_start_date);
610
611 if (l_resource_end_date is NULL and l_role_type_end_date is NULL) then
612 l_new_role_end_date := l_resource_end_date;
613 elsif (l_resource_end_date is NULL and l_role_type_end_date is NOT NULL) then
614 l_new_role_end_date := l_role_type_end_date;
615 elsif (l_resource_end_date is NOT NULL and l_role_type_end_date is NULL) then
616 l_new_role_end_date := l_resource_end_date;
617 else
618 l_new_role_end_date := least(l_resource_end_date, l_role_type_end_date);
619 end if;
620 else
621 l_new_role_start_date := l_start_date_active;
622 l_new_role_end_date := l_resource_end_date;
623 end if;
624
625 jtf_rs_role_relate_pub.update_resource_role_relate
626 (P_API_VERSION => 1.0,
627 P_INIT_MSG_LIST => fnd_api.g_false,
628 P_COMMIT => fnd_api.g_false,
629 P_ROLE_RELATE_ID => l_role_relate_id,
630 P_START_DATE_ACTIVE => l_new_role_start_date,
631 P_END_DATE_ACTIVE => l_new_role_end_date,
632 P_OBJECT_VERSION_NUM => l_object_version_number,
633 X_RETURN_STATUS => l_return_status,
634 X_MSG_COUNT => l_msg_count,
635 X_MSG_DATA => l_msg_data);
636 if not (l_return_status = fnd_api.g_ret_sts_success) THEN
637 raise fnd_api.g_exc_unexpected_error;
638 end if;
639
640 end if;
641
642 close chk_role_relate;
643
644 --dbms_output.put_line(' resource role_relate id = ' || to_char(l_role_relate_id));
645
646 /* Calling the role relate api's to create resource role relation */
647
648 jtf_rs_role_relate_pub.update_resource_role_relate
649 (p_api_version => 1.0
650 ,p_init_msg_list => fnd_api.g_false
651 ,p_commit => fnd_api.g_false
652 ,p_role_relate_id => p_role_relate_id
653 ,p_start_date_active => p_start_date
654 ,p_end_date_active => p_end_date
655 ,p_object_version_num => l_object_version_num
656 ,x_return_status => l_return_status
657 ,x_msg_count => l_msg_count
658 ,x_msg_data => l_msg_data
659 );
660
661 if not (l_return_status = fnd_api.g_ret_sts_success) THEN
662 raise fnd_api.g_exc_unexpected_error;
663 end if;
664
665 --dbms_output.put_line(' group role_relate id = ' || to_char(l_role_relate_id));
666
667 if fnd_api.to_boolean(p_commit)
668 then
669 commit work;
670 end if;
671
672 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
673
674 exception
675 when fnd_api.g_exc_unexpected_error
676 then
677 rollback to upd_grp_memship;
678 x_return_status := fnd_api.g_ret_sts_error;
679 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
680 WHEN fnd_api.g_exc_error
681 THEN
682 ROLLBACK TO upd_grp_memship;
683 x_return_status := fnd_api.g_ret_sts_error;
684 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
685 when others
686 then
687 rollback to upd_grp_memship;
688 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
689 fnd_message.set_token('P_SQLCODE',SQLCODE);
690 fnd_message.set_token('P_SQLERRM',SQLERRM);
691 fnd_message.set_token('P_API_NAME',l_api_name);
692 FND_MSG_PUB.add;
693 x_return_status := fnd_api.g_ret_sts_unexp_error;
694 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
695
696 END update_group_membership;
697
698 PROCEDURE delete_group_membership
699 (P_API_VERSION IN NUMBER,
700 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT FND_API.G_FALSE,
701 P_COMMIT IN VARCHAR2 DEFAULT FND_API.G_FALSE,
702 P_GROUP_ID IN NUMBER,
703 P_RESOURCE_ID IN NUMBER,
704 P_GROUP_MEMBER_ID IN NUMBER,
705 P_ROLE_RELATE_ID IN NUMBER,
706 P_OBJECT_VERSION_NUM IN NUMBER,
707 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
708 X_MSG_COUNT OUT NOCOPY NUMBER,
709 X_MSG_DATA OUT NOCOPY VARCHAR2
710 ) IS
711
712 l_api_version constant number := 1.0;
713 l_api_name constant varchar2(30) := 'DELETE_GROUP_MEMBERSHIP';
714 l_return_status varchar2(100) := fnd_api.g_ret_sts_success;
715 l_msg_count number;
716 l_msg_data varchar2(2000);
717
718 /* Cursor Variables to check group member has to be deleted or not */
719
720 cursor group_member_exists(l_group_member_id number)
721 is
722 select role_relate_id
723 from jtf_rs_role_relations
724 where role_resource_id = l_group_member_id
725 and role_resource_type = 'RS_GROUP_MEMBER'
726 and nvl(delete_flag, 'N') <> 'Y';
727
728 l_role_relete_id jtf_rs_role_relations.role_relate_id%type;
729
730 /* Cursor Variables to get object_version_number for group member */
731
732 cursor get_obj_ver_num(l_group_member_id number)
733 is
734 select object_version_number
735 from jtf_rs_group_members
736 where group_member_id = l_group_member_id;
737
738 l_object_version_num jtf_rs_role_relations.object_version_number%type;
739
740 BEGIN
741
742 x_return_status := fnd_api.g_ret_sts_success;
743
744 savepoint del_grp_memship;
745
746 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
747
748 if fnd_api.tO_BOOLEAN(p_init_msg_list)
749 then
750 fnd_msg_pub.Initialize;
751 end if;
752
753 if (p_role_relate_id is not null) then
754 jtf_rs_role_relate_pub.delete_resource_role_relate
755 (P_API_VERSION => 1.0,
756 P_INIT_MSG_LIST => fnd_api.g_false,
757 P_COMMIT => fnd_api.g_false,
758 P_ROLE_RELATE_ID => p_role_relate_id,
759 P_OBJECT_VERSION_NUM => p_object_version_num,
760 X_RETURN_STATUS => l_return_status,
761 X_MSG_COUNT => l_msg_count,
762 X_MSG_DATA => l_msg_data);
763 if not (l_return_status = fnd_api.g_ret_sts_success) THEN
764 raise fnd_api.g_exc_unexpected_error;
765 end if;
766 else
767 -- open group_member_exists(p_group_member_id);
768 -- fetch group_member_exists
769 -- into l_role_relete_id;
770 -- if group_member_exists%NOTFOUND
771 -- then
772
773 open get_obj_ver_num(p_group_member_id);
774 fetch get_obj_ver_num
775 into l_object_version_num;
776 close get_obj_ver_num;
777
778 jtf_rs_group_members_pub.delete_resource_group_members
779 (P_API_VERSION => 1.0,
780 P_INIT_MSG_LIST => fnd_api.g_false,
781 P_COMMIT => fnd_api.g_false,
782 P_GROUP_ID => p_group_id,
783 P_GROUP_NUMBER => null,
784 P_RESOURCE_ID => p_resource_id,
785 P_RESOURCE_NUMBER => null,
786 P_OBJECT_VERSION_NUM => l_object_version_num,
787 X_RETURN_STATUS => l_return_status,
788 X_MSG_COUNT => l_msg_count,
789 X_MSG_DATA => l_msg_data);
790
791 if not (l_return_status = fnd_api.g_ret_sts_success) THEN
792 raise fnd_api.g_exc_unexpected_error;
793 end if;
794 -- end if;
795 -- close group_member_exists;
796 end if;
797
798 if fnd_api.to_boolean(p_commit)
799 then
800 commit work;
801 end if;
802
803 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
804
805 exception
806 when fnd_api.g_exc_unexpected_error
807 then
808 rollback to del_grp_memship;
809 x_return_status := fnd_api.g_ret_sts_error;
810 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
811 WHEN fnd_api.g_exc_error
812 THEN
813 ROLLBACK TO del_grp_memship;
814 x_return_status := fnd_api.g_ret_sts_error;
815 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
816 when others
817 then
818 rollback to del_grp_memship;
819 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
820 fnd_message.set_token('P_SQLCODE',SQLCODE);
821 fnd_message.set_token('P_SQLERRM',SQLERRM);
822 fnd_message.set_token('P_API_NAME',l_api_name);
823 FND_MSG_PUB.add;
824 x_return_status := fnd_api.g_ret_sts_unexp_error;
825 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
826
827 END delete_group_membership;
828
829 END jtf_rs_grp_membership_pub;
[Home] [Help]