1 PACKAGE BODY jtf_rs_wf_integration_pub AS
2 /* $Header: jtfrswfb.pls 120.1 2005/06/24 20:30:40 baianand ship $ */
3
4 /*****************************************************************************************
5 ******************************************************************************************/
6
7 /* Package variables. */
8
9 G_PKG_NAME VARCHAR2(30) := 'JTF_RS_WF_INTEGRATION_PUB';
10
11 G_GRP_ORIG_SYSTEM CONSTANT VARCHAR2(10) := 'JRES_GRP';
12 G_TEAM_ORIG_SYSTEM CONSTANT VARCHAR2(10) := 'JRES_TEAM';
13
14 PROCEDURE get_wf_role
15 (p_resource_id IN number,
16 x_role_name OUT NOCOPY varchar2,
17 x_orig_system OUT NOCOPY varchar2,
18 x_orig_system_id OUT NOCOPY number
19 ) IS
20
21 cursor res_cur IS
22 select user_id
23 from jtf_rs_resource_extns
24 where resource_id = p_resource_id;
25
26 l_user_id number;
27
28 BEGIN
29
30 --xx OPEN res_cur;
31 --xx FETCH res_cur INTO l_user_id;
32 --xx CLOSE res_cur;
33
34 l_user_id := NULL;
35
36 jtf_rs_wf_integration_pub.get_wf_role
37 (p_resource_id => p_resource_id,
38 p_user_id => l_user_id,
39 x_role_name => x_role_name,
40 x_orig_system => x_orig_system,
41 x_orig_system_id => x_orig_system_id);
42 EXCEPTION when OTHERS then
43 null;
44 END get_wf_role;
45
46 PROCEDURE get_wf_role
47 (p_resource_id IN number,
48 p_user_id IN number,
49 x_role_name OUT NOCOPY varchar2,
50 x_orig_system OUT NOCOPY varchar2,
51 x_orig_system_id OUT NOCOPY number
52 ) IS
53
54 l_res_usr_orig_system wf_local_roles.orig_system%TYPE := 'JRES_IND';
55 l_res_usr_role_name wf_local_roles.name%TYPE := l_res_usr_orig_system||':'||to_char(p_resource_id);
56 l_res_hz_orig_system wf_local_roles.orig_system%TYPE := 'HZ_PARTY';
57
58 l_role_name wf_local_roles.name%TYPE;
59 l_orig_system wf_local_roles.orig_system%TYPE;
60 l_orig_system_id wf_local_roles.orig_system_id%TYPE;
61
62 l_category jtf_rs_resource_extns.category%TYPE;
63 l_source_id jtf_rs_resource_extns.source_id%TYPE;
64 l_person_party_id jtf_rs_resource_extns.person_party_id%TYPE;
65 l_party_id hz_parties.party_id%TYPE;
66
67 cursor res_cur IS
68 select category, source_id, person_party_id
69 from jtf_rs_resource_extns
70 where resource_id = p_resource_id;
71
72 cursor res_wfrole_cur IS
73 select name, orig_system, orig_system_id
74 from wf_local_roles
75 where name = l_res_usr_role_name
76 and orig_system = l_res_usr_orig_system
77 and orig_system_id = p_resource_id;
78
79 cursor res_hz_wfrole_cur(c_party_id number) IS
80 select name, orig_system, orig_system_id
81 from wf_local_roles
82 where orig_system = l_res_hz_orig_system
83 and orig_system_id = c_party_id;
84
85 cursor res_po_party_cur(c_vendor_contact_id number) IS
86 select per_party_id
87 from po_vendor_contacts
88 where vendor_contact_id = c_vendor_contact_id;
89
90 --xx cursor fnd_wfrole_cur IS
91 --xx select user_name, 'FND_USR', user_id
92 --xx from fnd_user
93 --xx where user_id = p_user_id;
94
95 BEGIN
96
97 OPEN res_cur;
98 FETCH res_cur INTO l_category, l_source_id, l_person_party_id;
99 CLOSE res_cur;
100
101 if l_category = 'EMPLOYEE' then
102 l_party_id := l_person_party_id;
103 elsif l_category in ('PARTY','PARTNER') then
104 l_party_id := l_source_id;
105 elsif (l_category = 'SUPPLIER_CONTACT') then
106 OPEN res_po_party_cur(l_source_id);
107 FETCH res_po_party_cur INTO l_party_id;
108 CLOSE res_po_party_cur;
109 end if;
110
111 /* If user is addtached to the given resource_id */
112 --xx if (p_user_id is NULL) then
113 --xx
114 if l_category not in ('EMPLOYEE','PARTY','PARTNER','SUPPLIER_CONTACT') then
115 OPEN res_wfrole_cur;
116 FETCH res_wfrole_cur INTO l_role_name, l_orig_system, l_orig_system_id;
117 CLOSE res_wfrole_cur;
118 else
119 OPEN res_hz_wfrole_cur(l_party_id);
120 FETCH res_hz_wfrole_cur INTO l_role_name, l_orig_system, l_orig_system_id;
121 CLOSE res_hz_wfrole_cur;
122 end if;
123 --xx
124 --xx else
125 --xx OPEN fnd_wfrole_cur;
126 --xx FETCH fnd_wfrole_cur INTO x_role_name, x_orig_system, x_orig_system_id;
127 --xx CLOSE fnd_wfrole_cur;
128 --xx
129 --xx Wf_Directory.GetRoleOrigSysInfo(
130 --xx x_role_name,
131 --xx x_orig_system,
132 --xx x_orig_system_id );
133 --xx
134 --xx end if; /* End of If user is addtached to the given resource_id */
135
136 x_role_name := l_role_name;
137 x_orig_system := l_orig_system;
138 x_orig_system_id := l_orig_system_id;
139
140 EXCEPTION when OTHERS then
141 null;
142
143 END get_wf_role;
144
145 FUNCTION get_wf_role(p_resource_id IN number) RETURN varchar2
146 IS
147 l_role_name wf_local_roles.name%TYPE;
148 l_orig_system wf_local_roles.orig_system%TYPE;
149 l_orig_system_id wf_local_roles.orig_system_id%TYPE;
150 BEGIN
151 jtf_rs_wf_integration_pub.get_wf_role
152 (p_resource_id => p_resource_id,
153 x_role_name => l_role_name,
154 x_orig_system => l_orig_system,
155 x_orig_system_id => l_orig_system_id);
156 RETURN l_role_name;
157 EXCEPTION when OTHERS then
158 RETURN NULL;
159 END get_wf_role;
160
161 /*
162 AddParameterToList - adds name and value to wf_parameter_list_t
163 If the list is null, will initialize, otherwise just adds to the end of list
164 */
165 PROCEDURE AddParameterToList(p_name in varchar2,
166 p_value in varchar2,
167 p_parameterlist in out nocopy wf_parameter_list_t)
168 IS
169 j number;
170 BEGIN
171 if (p_ParameterList is null) then
172 --
173 -- Initialize Parameter List and set value
174 --
175 p_ParameterList := wf_parameter_list_t(null);
176 p_ParameterList(1) := wf_parameter_t(p_Name, p_Value);
177 else
178 --
179 -- parameter list exists, add parameter to list
180 --
181 p_ParameterList.EXTEND;
182 j := p_ParameterList.COUNT;
183 p_ParameterList(j) := wf_parameter_t(p_Name, p_Value);
184 end if;
185 END AddParameterToList;
186
187 /*
188 PROCEDURE get_user_role_dates
189 (p_user_start_date IN DATE,
190 p_user_end_date IN DATE,
191 p_role_start_date IN DATE,
192 p_role_end_date IN DATE,
193 x_user_role_start_date OUT NOCOPY DATE,
194 x_user_role_end_date OUT NOCOPY DATE
195 ) IS
196
197 l_g_miss_date date := trunc(to_date('31-12-4712','DD-MM-YYYY'));
198
199 BEGIN
200
201 if p_role_start_date >= p_user_start_date then
202 x_user_role_start_date := p_role_start_date;
203 else
204 x_user_role_start_date := p_user_start_date;
205 end if;
206
207 if nvl(p_role_end_date,l_g_miss_date) >= nvl(p_user_end_date,l_g_miss_date) then
208 x_user_role_end_date := p_user_end_date;
209 else
210 x_user_role_end_date := p_role_end_date;
211 end if;
212
213 END get_user_role_dates;
214 */
215
216 PROCEDURE create_resource
217 (P_API_VERSION IN NUMBER,
218 P_INIT_MSG_LIST IN VARCHAR2,
219 P_COMMIT IN VARCHAR2,
220 P_RESOURCE_ID IN NUMBER,
221 P_RESOURCE_NAME IN VARCHAR2,
222 P_CATEGORY IN VARCHAR2,
223 P_USER_ID IN NUMBER,
224 P_EMAIL_ADDRESS IN VARCHAR2,
225 P_START_DATE_ACTIVE IN DATE,
226 P_END_DATE_ACTIVE IN DATE,
227 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
228 X_MSG_COUNT OUT NOCOPY NUMBER,
229 X_MSG_DATA OUT NOCOPY VARCHAR2
230 ) IS
231
232 l_api_version CONSTANT NUMBER := 1.0;
233 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RESOURCE';
234
235 l_start_date_active date := trunc(p_start_date_active);
236 l_end_date_active date := trunc(p_end_date_active);
237 l_sysdate date := trunc(sysdate);
238
239 l_res_usr_orig_system wf_local_roles.orig_system%TYPE := 'JRES_IND';
240 l_res_usr_role_name wf_local_roles.name%TYPE := l_res_usr_orig_system||':'||to_char(p_resource_id);
241
242 l_list WF_PARAMETER_LIST_T;
243
244 /* Cursor to get the party id of the employee */
245 cursor emp_party_id_cur IS
246 select ppf.party_id
247 from per_all_people_f ppf,
248 jtf_rs_resource_extns res
249 where res.category = 'EMPLOYEE'
250 and res.source_id = ppf.person_id
251 and res.resource_id = p_resource_id
252 order by ppf.effective_start_date desc;
253
254 /* Cursor to get the party id of the party/partner */
255 cursor partner_party_id_cur IS
256 select res.source_id
257 from jtf_rs_resource_extns res
258 where res.resource_id = p_resource_id;
259
260 l_person_party_id number;
261
262 BEGIN
263
264 x_return_status := fnd_api.g_ret_sts_success;
265 savepoint cr_emp_wf_save;
266
267 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
268 RAISE fnd_api.g_exc_unexpected_error;
269 END IF;
270
271 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
272 if p_init_msg_list is not NULL and fnd_api.to_boolean(p_init_msg_list)
273 then
274 fnd_msg_pub.Initialize;
275 end if;
276
277 /* If the resource is not attached to an fnd user */
278 -- if (p_user_id is NULL) then
279
280 if (p_category not in ('EMPLOYEE','PARTY','PARTNER','SUPPLIER_CONTACT')) then
281
282 /* Looking for resources with l_end_date_active >= l_sysdate at the time of creation */
283 -- if ( (l_start_date_active <= l_sysdate) AND
284 -- ((l_end_date_active >= l_sysdate) OR (l_end_date_active is NULL)) ) then
285 if ((l_end_date_active >= l_sysdate) OR (l_end_date_active is NULL)) then
286
287 /* Below If statement is to derive the party_id of the resource.
288 If the category is not EMPLOYEE, PARTY or PARTNER, then party_id will be NULL */
289 l_person_party_id := NULL;
290 if p_category = 'EMPLOYEE' then
291 OPEN emp_party_id_cur;
292 FETCH emp_party_id_cur INTO l_person_party_id;
293 CLOSE emp_party_id_cur;
294 elsif (p_category = 'PARTY' OR p_category = 'PARTNER') then
295 OPEN partner_party_id_cur;
296 FETCH partner_party_id_cur INTO l_person_party_id;
297 CLOSE partner_party_id_cur;
298 end if;
299
300 /* Changed the code to call Wf_local_synch instead of Wf_Directory
301 Fix for bug # 2671368 */
302 AddParameterToList('USER_NAME',l_res_usr_role_name,l_list);
303 AddParameterToList('DISPLAYNAME',p_resource_name,l_list);
304 AddParameterToList('MAIL',p_email_address,l_list);
305 AddParameterToList('ORCLISENABLED','ACTIVE',l_list);
306 AddParameterToList('PERSON_PARTY_ID',l_person_party_id,l_list);
307
308 Wf_local_synch.propagate_role(
309 p_orig_system => l_res_usr_orig_system,
310 p_orig_system_id => p_resource_id,
311 p_attributes => l_list,
312 p_start_date => l_start_date_active,
313 p_expiration_date => l_end_date_active);
314
315 l_list.DELETE;
316
317 Wf_local_synch.propagate_user_role(
318 p_user_orig_system => l_res_usr_orig_system,
319 p_user_orig_system_id => p_resource_id,
320 p_role_orig_system => l_res_usr_orig_system,
321 p_role_orig_system_id => p_resource_id,
322 p_start_date => l_start_date_active,
323 p_expiration_date => l_end_date_active);
324
325 end if; /* End of - looking for active resource at the time of creation */
326
327 end if; /* End of - If the resource is not an EMPLOYEE, PARTY, PARTNER or SUPPLIER_CONTACT */
328 -- end if; /* End of - If the resource is not attached to an fnd user */
329
330 IF p_commit is not NULL and fnd_api.to_boolean(p_commit) THEN
331 COMMIT WORK;
332 END IF;
333
334 EXCEPTION when OTHERS then
335 ROLLBACK TO cr_emp_wf_save;
336 x_return_status := fnd_api.g_ret_sts_unexp_error;
337 END create_resource;
338
339 PROCEDURE update_resource
340 (P_API_VERSION IN NUMBER,
341 P_INIT_MSG_LIST IN VARCHAR2,
342 P_COMMIT IN VARCHAR2,
343 P_RESOURCE_ID IN NUMBER,
344 P_RESOURCE_NAME IN VARCHAR2,
345 P_USER_ID IN NUMBER,
346 P_EMAIL_ADDRESS IN VARCHAR2,
347 P_START_DATE_ACTIVE IN DATE,
348 P_END_DATE_ACTIVE IN DATE,
349 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
350 X_MSG_COUNT OUT NOCOPY NUMBER,
351 X_MSG_DATA OUT NOCOPY VARCHAR2
352 ) IS
353
354 l_api_version CONSTANT NUMBER := 1.0;
355 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RESOURCE';
356
357 l_start_date_active date := trunc(p_start_date_active);
358 l_end_date_active date := trunc(p_end_date_active);
359 l_sysdate date := trunc(sysdate);
360
361 cursor res_cur IS
362 select resource_name, source_email, user_id, source_id, category,
363 trunc(start_date_active) start_date_active,
364 trunc(end_date_active) end_date_active
365 from jtf_rs_resource_extns_vl
366 where resource_id = p_resource_id;
367
368 res_rec res_cur%rowtype;
369
370 l_res_usr_orig_system wf_local_roles.orig_system%TYPE := 'JRES_IND';
371 l_res_usr_role_name wf_local_roles.name%TYPE := l_res_usr_orig_system||':'||to_char(p_resource_id);
372
373 cursor res_wfrole_cur IS
374 select name
375 from wf_local_roles
376 where name = l_res_usr_role_name
377 and orig_system = l_res_usr_orig_system
378 and orig_system_id = p_resource_id;
379
380 l_role_name wf_local_roles.name%TYPE;
381 res_wfrole_exists varchar2(1) := 'N';
382
383 cursor fnd_wfrole_cur(l_user_id number) IS
384 select user_name
385 from fnd_user
386 where user_id = l_user_id;
387
388 l_fnd_old_user_name wf_local_roles.name%TYPE;
389 l_fnd_new_user_name wf_local_roles.name%TYPE;
390 l_fnd_usr_old_orig_system wf_local_roles.orig_system%TYPE;
391 l_fnd_usr_new_orig_system wf_local_roles.orig_system%TYPE;
392 l_fnd_usr_old_orig_system_id wf_local_roles.orig_system_id%TYPE;
393 l_fnd_usr_new_orig_system_id wf_local_roles.orig_system_id%TYPE;
394
395 PROCEDURE create_wf_role_usr_role (ll_role_name VARCHAR2,
396 ll_role_orig_system VARCHAR2,
397 ll_role_orig_system_id NUMBER,
398 ll_role_display_name VARCHAR2,
399 ll_email_address VARCHAR2,
400 ll_start_date_active DATE,
401 ll_expiration_date DATE,
402 ll_source_id NUMBER,
403 ll_category VARCHAR2) IS
404
405 l_list WF_PARAMETER_LIST_T;
406
407 /* Cursor to get the party id of the employee */
408 cursor emp_party_id_cur IS
409 select party_id
410 from per_all_people_f ppf
411 where ppf.person_id = ll_source_id
412 order by ppf.effective_start_date desc;
413
414 l_person_party_id number;
415
416 BEGIN
417
418 /* Below If statement is to derive the party_id of the resource.
419 If the category is not EMPLOYEE, PARTY or PARTNER, then party_id will be NULL */
420 l_person_party_id := NULL;
421 if ll_category = 'EMPLOYEE' then
422 OPEN emp_party_id_cur;
423 FETCH emp_party_id_cur INTO l_person_party_id;
424 CLOSE emp_party_id_cur;
425 elsif (ll_category = 'PARTY' OR ll_category = 'PARTNER') then
426 l_person_party_id := ll_source_id;
427 end if;
428
429 /* Changed the code to call Wf_local_synch instead of Wf_Directory
430 Fix for bug # 2671368 */
431
432 AddParameterToList('USER_NAME',ll_role_name,l_list);
433 AddParameterToList('DISPLAYNAME',ll_role_display_name,l_list);
434 AddParameterToList('MAIL',ll_email_address,l_list);
435 AddParameterToList('ORCLISENABLED','ACTIVE',l_list);
436 AddParameterToList('PERSON_PARTY_ID',l_person_party_id,l_list);
437
438 Wf_local_synch.propagate_role(
439 p_orig_system => ll_role_orig_system,
440 p_orig_system_id => ll_role_orig_system_id,
441 p_attributes => l_list,
442 p_start_date => ll_start_date_active,
443 p_expiration_date => ll_expiration_date);
444
445 l_list.DELETE;
446
447 Wf_local_synch.propagate_user_role(
448 p_user_orig_system => ll_role_orig_system,
449 p_user_orig_system_id => ll_role_orig_system_id,
450 p_role_orig_system => ll_role_orig_system,
451 p_role_orig_system_id => ll_role_orig_system_id,
452 p_start_date => ll_start_date_active,
453 p_expiration_date => ll_expiration_date);
454
455 EXCEPTION when others then
456 null;
457 END create_wf_role_usr_role;
458
459 PROCEDURE update_wf_role (ll_role_name VARCHAR2,
460 ll_role_orig_system VARCHAR2,
461 ll_role_orig_system_id NUMBER,
462 ll_role_display_name VARCHAR2,
463 ll_email_address VARCHAR2,
464 ll_status VARCHAR2,
465 ll_start_date_active DATE,
466 ll_expiration_date DATE,
467 ll_source_id NUMBER,
468 ll_category VARCHAR2) IS
469 l_list WF_PARAMETER_LIST_T;
470
471 /* Cursor to get the party id of the employee */
472 cursor emp_party_id_cur IS
473 select party_id
474 from per_all_people_f ppf
475 where ppf.person_id = ll_source_id
476 order by ppf.effective_start_date desc;
477
478 l_person_party_id number;
479
480 BEGIN
481
482 /* Below If statement is to derive the party_id of the resource.
483 If the category is not EMPLOYEE, PARTY or PARTNER, then party_id will be NULL */
484 l_person_party_id := NULL;
485 if ll_category = 'EMPLOYEE' then
486 OPEN emp_party_id_cur;
487 FETCH emp_party_id_cur INTO l_person_party_id;
488 CLOSE emp_party_id_cur;
489 elsif (ll_category = 'PARTY' OR ll_category = 'PARTNER') then
490 l_person_party_id := ll_source_id;
491 end if;
492
493 /* Changed the code to call Wf_local_synch instead of Wf_Directory
494 Fix for bug # 2671368 */
495
496 if ((nvl(ll_expiration_date,l_sysdate) < l_sysdate)) then
497 Wf_local_synch.propagate_user_role(
498 p_user_orig_system => ll_role_orig_system,
499 p_user_orig_system_id => ll_role_orig_system_id,
500 p_role_orig_system => ll_role_orig_system,
501 p_role_orig_system_id => ll_role_orig_system_id,
502 p_start_date => ll_start_date_active,
503 p_expiration_date => ll_expiration_date,
504 p_overwrite => TRUE);
505 end if;
506 AddParameterToList('USER_NAME',ll_role_name,l_list);
507 AddParameterToList('DISPLAYNAME',ll_role_display_name,l_list);
508 AddParameterToList('MAIL',ll_email_address,l_list);
509 AddParameterToList('ORCLISENABLED',ll_status,l_list);
510 AddParameterToList('WFSYNCH_OVERWRITE','TRUE',l_list);
511 AddParameterToList('PERSON_PARTY_ID',l_person_party_id,l_list);
512
513 Wf_local_synch.propagate_role(
514 p_orig_system => ll_role_orig_system,
515 p_orig_system_id => ll_role_orig_system_id,
516 p_attributes => l_list,
517 p_start_date => ll_start_date_active,
518 p_expiration_date => ll_expiration_date);
519
520 l_list.DELETE;
521
522 if ((nvl(ll_expiration_date,l_sysdate) >= l_sysdate)) then
523 Wf_local_synch.propagate_user_role(
524 p_user_orig_system => ll_role_orig_system,
525 p_user_orig_system_id => ll_role_orig_system_id,
526 p_role_orig_system => ll_role_orig_system,
527 p_role_orig_system_id => ll_role_orig_system_id,
528 p_start_date => ll_start_date_active,
529 p_expiration_date => ll_expiration_date,
530 p_overwrite => TRUE);
531 end if;
532
533 -- EXCEPTION when others then
534 -- null;
535 END update_wf_role;
536
537 PROCEDURE move_wf_user_role (ll_resource_id VARCHAR2,
538 ll_start_date_active DATE,
539 ll_end_date_active DATE,
540 ll_old_user_name VARCHAR2,
541 ll_old_user_orig_system VARCHAR2,
542 ll_old_user_orig_system_id NUMBER,
543 ll_new_user_name VARCHAR2,
544 ll_new_user_orig_system VARCHAR2,
545 ll_new_user_orig_system_id NUMBER) IS
546
547 CURSOR grp_cur IS
548 SELECT mem.group_id, grp.group_number,
549 trunc(grp.start_date_active) start_date_active,
550 trunc(grp.end_date_active) end_date_active
551 FROM jtf_rs_group_members mem, jtf_rs_groups_b grp
552 WHERE mem.group_id = grp.group_id
553 AND nvl(mem.delete_flag,'N') <> 'Y'
554 AND l_sysdate between trunc(grp.start_date_active) and nvl(trunc(grp.end_date_active),l_sysdate)
555 AND mem.resource_id = ll_resource_id;
556
557 cursor grp_wfrole_cur(c_group_id number, c_grp_role_name varchar2) IS
558 select name
559 from wf_local_roles
560 where name = c_grp_role_name
561 and orig_system = g_grp_orig_system
562 and orig_system_id = c_group_id;
563
564 CURSOR team_cur IS
565 SELECT mem.team_id,
566 trunc(tm.start_date_active) start_date_active,
567 trunc(tm.end_date_active) end_date_active
568 FROM jtf_rs_team_members mem, jtf_rs_teams_b tm
569 WHERE mem.team_id = tm.team_id
570 AND nvl(mem.delete_flag,'N') <> 'Y'
571 AND l_sysdate between trunc(tm.start_date_active) and nvl(trunc(tm.end_date_active),l_sysdate)
572 AND mem.team_resource_id = ll_resource_id
573 AND mem.RESOURCE_TYPE = 'INDIVIDUAL';
574
575 cursor tm_wfrole_cur(c_team_id number, c_team_role_name varchar2) IS
576 select name
577 from wf_local_roles
578 where name = c_team_role_name
579 and orig_system = g_team_orig_system
580 and orig_system_id = c_team_id;
581
582 l_grp_role_name wf_local_roles.name%TYPE;
583 l_team_role_name wf_local_roles.name%TYPE;
584 l_role_name wf_local_user_roles.role_name%TYPE;
585 l_role_orig_system_id wf_local_user_roles.role_orig_system_id%TYPE;
586
587 l_mem_role_start_date date;
588 l_mem_role_end_date date;
589 l_g_miss_date date := trunc(to_date('31-12-4712','DD-MM-YYYY'));
590
591 BEGIN
592
593 /* Changed the code to call Wf_local_synch instead of Wf_Directory
594 Fix for bug # 2671368 */
595
596 l_mem_role_start_date := sysdate;
597 l_mem_role_end_date := l_g_miss_date;
598
599 /* Processing for Group members */
600 for i in grp_cur LOOP
601 l_role_orig_system_id := i.group_id;
602 l_role_name := g_grp_orig_system ||':'|| to_char(l_role_orig_system_id);
603 OPEN grp_wfrole_cur(l_role_orig_system_id,l_role_name);
604 FETCH grp_wfrole_cur INTO l_grp_role_name;
605 if grp_wfrole_cur%FOUND then /* If the group has a corresponding record in wf_local_user */
606 if ll_old_user_orig_system is NOT NULL then
607 BEGIN
608 Wf_local_synch.propagate_user_role(
609 p_user_orig_system => ll_old_user_orig_system,
610 p_user_orig_system_id => ll_old_user_orig_system_id,
611 p_role_orig_system => g_grp_orig_system,
612 p_role_orig_system_id => l_role_orig_system_id,
613 -- p_start_date => sysdate,
614 p_expiration_date => sysdate-1);
615
616 EXCEPTION when others then
617 null;
618 END;
619 end if;
620 if ll_new_user_orig_system is NOT NULL then
621 BEGIN
622
623 l_mem_role_start_date := greatest(ll_start_date_active, i.start_date_active);
624 l_mem_role_end_date := least (nvl(ll_end_date_active, l_g_miss_date), nvl(i.end_date_active, l_g_miss_date));
625
626 if (l_mem_role_end_date = l_g_miss_date) then
627 l_mem_role_end_date := NULL;
628 end if;
629
630 -- get_user_role_dates
631 -- (p_user_start_date => ll_start_date_active,
632 -- p_user_end_date => ll_end_date_active,
633 -- p_role_start_date => i.start_date_active,
634 -- p_role_end_date => i.end_date_active,
635 -- x_user_role_start_date => l_mem_role_start_date,
636 -- x_user_role_end_date => l_mem_role_end_date);
637
638 Wf_local_synch.propagate_user_role(
639 p_user_orig_system => ll_new_user_orig_system,
640 p_user_orig_system_id => ll_new_user_orig_system_id,
641 p_role_orig_system => g_grp_orig_system,
642 p_role_orig_system_id => l_role_orig_system_id,
643 p_start_date => l_mem_role_start_date,
644 p_expiration_date => l_mem_role_end_date,
645 p_overwrite => TRUE);
646
647 EXCEPTION when others then
648 null;
649 END;
650 end if;
651 end if; /* End of - If the group has a corresponding record in wf_local_user */
652 CLOSE grp_wfrole_cur;
653 END LOOP;
654
655 l_mem_role_start_date := sysdate;
656 l_mem_role_end_date := l_g_miss_date;
657
658 /* Processing for Team members */
659 for i in team_cur LOOP
660 l_role_orig_system_id := i.team_id;
661 l_role_name := g_team_orig_system ||':'|| to_char(l_role_orig_system_id);
662 OPEN tm_wfrole_cur(l_role_orig_system_id,l_role_name);
663 FETCH tm_wfrole_cur INTO l_team_role_name;
664 if tm_wfrole_cur%FOUND then /* If the team has a corresponding record in wf_local_user */
665 if ll_old_user_orig_system is NOT NULL then
666 BEGIN
667
668 Wf_local_synch.propagate_user_role(
669 p_user_orig_system => ll_old_user_orig_system,
670 p_user_orig_system_id => ll_old_user_orig_system_id,
671 p_role_orig_system => g_team_orig_system,
672 p_role_orig_system_id => l_role_orig_system_id,
673 -- p_start_date => sysdate,
674 p_expiration_date => sysdate-1);
675
676 EXCEPTION when others then
677 null;
678 END;
679 end if;
680 if ll_new_user_orig_system is NOT NULL then
681 BEGIN
682
683 l_mem_role_start_date := greatest(ll_start_date_active, i.start_date_active);
684 l_mem_role_end_date := least (nvl(ll_end_date_active, l_g_miss_date), nvl(i.end_date_active, l_g_miss_date));
685
686 if (l_mem_role_end_date = l_g_miss_date) then
687 l_mem_role_end_date := NULL;
688 end if;
689
690 -- get_user_role_dates
691 -- (p_user_start_date => ll_start_date_active,
692 -- p_user_end_date => ll_end_date_active,
693 -- p_role_start_date => i.start_date_active,
694 -- p_role_end_date => i.end_date_active,
695 -- x_user_role_start_date => l_mem_role_start_date,
696 -- x_user_role_end_date => l_mem_role_end_date);
697
698 Wf_local_synch.propagate_user_role(
699 p_user_orig_system => ll_new_user_orig_system,
700 p_user_orig_system_id => ll_new_user_orig_system_id,
701 p_role_orig_system => g_team_orig_system,
702 p_role_orig_system_id => l_role_orig_system_id,
703 p_start_date => l_mem_role_start_date,
704 p_expiration_date => l_mem_role_end_date,
705 p_overwrite => TRUE);
706
707 EXCEPTION when others then
708 null;
709 END;
710 end if;
711 end if; /* End of - If the team has a corresponding record in wf_local_user */
712 CLOSE tm_wfrole_cur;
713 END LOOP;
714
715 END move_wf_user_role;
716
717 BEGIN
718
719 x_return_status := fnd_api.g_ret_sts_success;
720 savepoint upd_emp_wf_save;
721
722 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
723 RAISE fnd_api.g_exc_unexpected_error;
724 END IF;
725
726 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
727 if p_init_msg_list is not NULL and fnd_api.to_boolean(p_init_msg_list)
728 then
729 fnd_msg_pub.Initialize;
730 end if;
731
732 OPEN res_cur;
733 FETCH res_cur INTO res_rec;
734
735 /* there are some changes and not a past to past updation */
736 if ( ( (res_rec.source_email <> p_email_address ) OR
737 ((res_rec.source_email is NULL) AND (p_email_address is NOT NULL)) OR
738 ((res_rec.source_email is NOT NULL) AND (p_email_address is NULL)) OR
739 -- (nvl(res_rec.user_id,-9999) <> nvl(p_user_id,-9999)) OR
740 (res_rec.resource_name <> p_resource_name) OR
741 (nvl(res_rec.end_date_active,fnd_api.g_miss_date) <> nvl(l_end_date_active,fnd_api.g_miss_date)) OR
742 (res_rec.start_date_active <> l_start_date_active) )
743 AND
744 ( (nvl(res_rec.end_date_active,l_sysdate) >= l_sysdate) OR
745 (nvl(l_end_date_active,l_sysdate) >= l_sysdate) )
746 ) then
747
748 OPEN res_wfrole_cur;
749 FETCH res_wfrole_cur INTO l_role_name;
750 if res_wfrole_cur%FOUND then /* If the resource has a corresponding record in wf_local_user */
751 res_wfrole_exists := 'Y';
752 end if; /* End of - If the resource has a corresponding record in wf_local_user */
753 CLOSE res_wfrole_cur;
754
755 /* Commented the below if condition to check the Resource validitiy.
756 The new Wf_local_synch API accepts start and end date for roles and user roles */
757
758 -- /* If the Resource is VALID within the new date range */
759 -- if ( (l_start_date_active <= l_sysdate) AND
760 -- ((l_end_date_active >= l_sysdate) OR (l_end_date_active is NULL)) ) then
761
762 --xx if (p_user_id is NULL) then /* If p_user_id is NULL */
763 --xx
764 --xx if res_rec.user_id IS NOT NULL then /* If p_user_id is changed to NULL */
765 --xx
766 --xx if res_wfrole_exists = 'N' then /* If the resource does not have a corresponding record in wf_local_user */
767 --xx create_wf_role_usr_role(ll_role_name => l_res_usr_role_name,
768 --xx ll_role_orig_system => l_res_usr_orig_system,
769 --xx ll_role_orig_system_id => p_resource_id,
770 --xx ll_role_display_name => p_resource_name,
771 --xx ll_email_address => p_email_address,
772 --xx ll_start_date_active => l_start_date_active,
773 --xx ll_expiration_date => l_end_date_active,
774 --xx ll_source_id => res_rec.source_id,
775 --xx ll_category => res_rec.category);
776 --xx else
777 --xx update_wf_role(ll_role_name => l_res_usr_role_name,
778 --xx ll_role_orig_system => l_res_usr_orig_system,
779 --xx ll_role_orig_system_id => p_resource_id,
780 --xx ll_role_display_name => p_resource_name,
781 --xx ll_email_address => p_email_address,
782 --xx ll_status => 'ACTIVE',
783 --xx ll_start_date_active => l_start_date_active,
784 --xx ll_expiration_date => l_end_date_active,
785 --xx ll_source_id => res_rec.source_id,
786 --xx ll_category => res_rec.category);
787 --xx end if; /* If the resource has a record in wf_local_user */
788
789 --xx OPEN fnd_wfrole_cur(res_rec.user_id);
790 --xx FETCH fnd_wfrole_cur INTO l_fnd_old_user_name;
791 --xx CLOSE fnd_wfrole_cur;
792 --xx
793 --xx Wf_Directory.GetRoleOrigSysInfo(
794 --xx l_fnd_old_user_name,
795 --xx l_fnd_usr_old_orig_system,
796 --xx l_fnd_usr_old_orig_system_id);
797 --xx
798 --xx move_wf_user_role(ll_resource_id => p_resource_id,
799 --xx ll_start_date_active => l_start_date_active,
800 --xx ll_end_date_active => l_end_date_active,
801 --xx ll_old_user_name => l_fnd_old_user_name,
802 --xx ll_old_user_orig_system => l_fnd_usr_old_orig_system,
803 --xx ll_old_user_orig_system_id => l_fnd_usr_old_orig_system_id, /* res_rec.user_id */
804 --xx ll_new_user_name => l_res_usr_role_name,
805 --xx ll_new_user_orig_system => l_res_usr_orig_system,
806 --xx ll_new_user_orig_system_id => p_resource_id);
807 --xx
808 --xx else /* If p_user_id is already NULL, no change user_id */
809
810 if ((l_sysdate not between l_start_date_active and nvl(l_end_date_active,l_sysdate)) AND
811 (l_sysdate between res_rec.start_date_active and nvl(res_rec.end_date_active,l_sysdate))) then
812 /* above if is to find out if the resource is changed from active to inactive */
813 if res_wfrole_exists = 'Y' then
814
815 /* following procedure will end date all the user roles for the resource */
816 move_wf_user_role(ll_resource_id => p_resource_id,
817 ll_start_date_active => l_start_date_active,
818 ll_end_date_active => l_end_date_active,
819 ll_old_user_name => l_res_usr_role_name,
820 ll_old_user_orig_system => l_res_usr_orig_system,
821 ll_old_user_orig_system_id => p_resource_id,
822 ll_new_user_name => NULL,
823 ll_new_user_orig_system => NULL,
824 ll_new_user_orig_system_id => NULL);
825
826 /* following procedure will update the roles with latest info */
827 update_wf_role(ll_role_name => l_res_usr_role_name,
828 ll_role_orig_system => l_res_usr_orig_system,
829 ll_role_orig_system_id => p_resource_id,
830 ll_role_display_name => p_resource_name,
831 ll_email_address => p_email_address,
832 ll_status => 'ACTIVE',
833 ll_start_date_active => l_start_date_active,
834 ll_expiration_date => l_end_date_active,
835 ll_source_id => res_rec.source_id,
836 ll_category => res_rec.category);
837 end if;
838 else
839 if res_wfrole_exists = 'N' then
840 /* If the resource does not have a corresponding record in wf_local_user */
841 create_wf_role_usr_role(ll_role_name => l_res_usr_role_name,
842 ll_role_orig_system => l_res_usr_orig_system,
843 ll_role_orig_system_id => p_resource_id,
844 ll_role_display_name => p_resource_name,
845 ll_email_address => p_email_address,
846 ll_start_date_active => l_start_date_active,
847 ll_expiration_date => l_end_date_active,
848 ll_source_id => res_rec.source_id,
849 ll_category => res_rec.category);
850 else
851 /* following procedure will update the wf_local_user with latest info */
852 update_wf_role(ll_role_name => l_res_usr_role_name,
853 ll_role_orig_system => l_res_usr_orig_system,
854 ll_role_orig_system_id => p_resource_id,
855 ll_role_display_name => p_resource_name,
856 ll_email_address => p_email_address,
857 ll_status => 'ACTIVE',
858 ll_start_date_active => l_start_date_active,
859 ll_expiration_date => l_end_date_active,
860 ll_source_id => res_rec.source_id,
861 ll_category => res_rec.category);
862 end if; /* End of - If the resource does not have a corresponding record in wf_local_user */
863
864 /* following procedure will reactivate all the user roles for the resource
865 if the resource any of the resource dates are changed. */
866 if ((l_start_date_active <> res_rec.start_date_active) OR
867 (l_end_date_active is null and res_rec.end_date_active is not null) OR
868 (l_end_date_active is not null and res_rec.end_date_active is null)) THEN
869 move_wf_user_role(ll_resource_id => p_resource_id,
870 ll_start_date_active => l_start_date_active,
871 ll_end_date_active => l_end_date_active,
872 ll_old_user_name => NULL,
873 ll_old_user_orig_system => NULL,
874 ll_old_user_orig_system_id => NULL,
875 ll_new_user_name => l_res_usr_role_name,
876 ll_new_user_orig_system => l_res_usr_orig_system,
877 ll_new_user_orig_system_id => p_resource_id);
878 end if;
879 end if;
880
881 --xx end if; /* If p_user_id is already NULL or changed to NULL */
882 --xx else /* If p_user_id is NOT NULL */
883 --xx
884 --xx if (res_rec.user_id is NULL) then /* If res_rec.user_id is NULL */
885 --xx
886 --xx OPEN fnd_wfrole_cur(p_user_id);
887 --xx FETCH fnd_wfrole_cur INTO l_fnd_new_user_name;
888 --xx CLOSE fnd_wfrole_cur;
889 --xx
890 --xx Wf_Directory.GetRoleOrigSysInfo(
891 --xx l_fnd_new_user_name,
892 --xx l_fnd_usr_new_orig_system,
893 --xx l_fnd_usr_new_orig_system_id);
894 --xx
895 --xx move_wf_user_role(ll_resource_id => p_resource_id,
896 --xx ll_start_date_active => l_start_date_active,
897 --xx ll_end_date_active => l_end_date_active,
898 --xx ll_old_user_name => l_res_usr_role_name,
899 --xx ll_old_user_orig_system => l_res_usr_orig_system,
900 --xx ll_old_user_orig_system_id => p_resource_id,
901 --xx ll_new_user_name => l_fnd_new_user_name,
902 --xx ll_new_user_orig_system => l_fnd_usr_new_orig_system,
903 --xx ll_new_user_orig_system_id => l_fnd_usr_new_orig_system_id); /* p_user_id */
904 --xx
905 --xx /* If the resource does not have a corresponding record in wf_local_user */
906 --xx if res_wfrole_exists = 'Y' then
907 --xx update_wf_role(ll_role_name => l_res_usr_role_name,
908 --xx ll_role_orig_system => l_res_usr_orig_system,
909 --xx ll_role_orig_system_id => p_resource_id,
910 --xx ll_role_display_name => p_resource_name,
911 --xx ll_email_address => p_email_address,
912 --xx ll_status => 'INACTIVE',
913 --xx ll_start_date_active => l_start_date_active,
914 --xx ll_expiration_date => l_sysdate-1,
915 --xx ll_source_id => res_rec.source_id,
916 --xx ll_category => res_rec.category);
917 --xx
918 --xx
919 --xx end if; /* End of - If the resource does not have a corresponding record in wf_local_user */
920 --xx
921 --xx else /* If res_rec.user_id is NOT NULL */
922 --xx
923 --xx if (res_rec.user_id <> p_user_id) then /* If user_id is changed from one value to another value */
924 --xx
925 --xx OPEN fnd_wfrole_cur(res_rec.user_id);
926 --xx FETCH fnd_wfrole_cur INTO l_fnd_old_user_name;
927 --xx CLOSE fnd_wfrole_cur;
928 --xx Wf_Directory.GetRoleOrigSysInfo(
929 --xx l_fnd_old_user_name,
930 --xx l_fnd_usr_old_orig_system,
931 --xx l_fnd_usr_old_orig_system_id);
932 --xx
933 --xx OPEN fnd_wfrole_cur(p_user_id);
934 --xx FETCH fnd_wfrole_cur INTO l_fnd_new_user_name;
935 --xx CLOSE fnd_wfrole_cur;
936 --xx Wf_Directory.GetRoleOrigSysInfo(
937 --xx l_fnd_new_user_name,
938 --xx l_fnd_usr_new_orig_system,
939 --xx l_fnd_usr_new_orig_system_id);
940 --xx
941 --xx move_wf_user_role(ll_resource_id => p_resource_id,
942 --xx ll_start_date_active => l_start_date_active,
943 --xx ll_end_date_active => l_end_date_active,
944 --xx ll_old_user_name => l_fnd_old_user_name,
945 --xx ll_old_user_orig_system => l_fnd_usr_old_orig_system,
946 --xx ll_old_user_orig_system_id => l_fnd_usr_old_orig_system_id, /* res_rec.user_id */
947 --xx ll_new_user_name => l_fnd_new_user_name,
948 --xx ll_new_user_orig_system => l_fnd_usr_new_orig_system,
949 --xx ll_new_user_orig_system_id => l_fnd_usr_new_orig_system_id); /* p_user_id */
950 --xx else /* User id is NOT NULL and no change. So, no need to update the local roles.
951 --xx only end date the user role if the resource is inacticated and
952 --xx reactivate the user role if the resource is acticated */
953 --xx
954 --xx /* below if is to find out if there a change in resource dates
955 --xx if there is no change, the no need to update the user roles table */
956 --xx if ((l_start_date_active <> res_rec.start_date_active) OR
957 --xx (l_end_date_active is null and res_rec.end_date_active is not null) OR
958 --xx (l_end_date_active is not null and res_rec.end_date_active is null)) THEN
959 --xx
960 --xx OPEN fnd_wfrole_cur(p_user_id);
961 --xx FETCH fnd_wfrole_cur INTO l_fnd_new_user_name;
962 --xx CLOSE fnd_wfrole_cur;
963 --xx Wf_Directory.GetRoleOrigSysInfo(
964 --xx l_fnd_new_user_name,
965 --xx l_fnd_usr_new_orig_system,
966 --xx l_fnd_usr_new_orig_system_id);
967 --xx
968 --xx /* following procedure will reactivate all the user roles for the resource */
969 --xx move_wf_user_role(ll_resource_id => p_resource_id,
970 --xx ll_start_date_active => l_start_date_active,
971 --xx ll_end_date_active => l_end_date_active,
972 --xx ll_old_user_name => NULL,
973 --xx ll_old_user_orig_system => NULL,
974 --xx ll_old_user_orig_system_id => NULL,
975 --xx ll_new_user_name => l_fnd_new_user_name,
976 --xx ll_new_user_orig_system => l_fnd_usr_new_orig_system,
977 --xx ll_new_user_orig_system_id => l_fnd_usr_new_orig_system_id);
978 --xx
979 --xx end if;
980 --xx
981 -- if ((l_sysdate between l_start_date_active and nvl(l_end_date_active,l_sysdate)) AND
982 -- (l_sysdate not between res_rec.start_date_active and nvl(res_rec.end_date_active,l_sysdate))) then
983 -- /* above if is to find out if the resource is changed from inactive to active */
984 --
985 -- OPEN fnd_wfrole_cur(p_user_id);
986 -- FETCH fnd_wfrole_cur INTO l_fnd_new_user_name;
987 -- CLOSE fnd_wfrole_cur;
988 -- Wf_Directory.GetRoleOrigSysInfo(
989 -- l_fnd_new_user_name,
990 -- l_fnd_usr_new_orig_system,
991 -- l_fnd_usr_new_orig_system_id);
992 --
993 -- /* following procedure will reactivate all the user roles for the resource */
994 -- move_wf_user_role(ll_resource_id => p_resource_id,
995 -- ll_start_date_active => l_start_date_active,
996 -- ll_end_date_active => l_end_date_active,
997 -- ll_old_user_name => NULL,
998 -- ll_old_user_orig_system => NULL,
999 -- ll_old_user_orig_system_id => NULL,
1000 -- ll_new_user_name => l_fnd_new_user_name,
1001 -- ll_new_user_orig_system => l_fnd_usr_new_orig_system,
1002 -- ll_new_user_orig_system_id => l_fnd_usr_new_orig_system_id);
1003 -- elsif ((l_sysdate not between l_start_date_active and nvl(l_end_date_active,l_sysdate)) AND
1004 -- (l_sysdate between res_rec.start_date_active and nvl(res_rec.end_date_active,l_sysdate))) then
1005 -- /* above if is to find out if the resource is changed from active to inactive */
1006 --
1007 -- OPEN fnd_wfrole_cur(p_user_id);
1008 -- FETCH fnd_wfrole_cur INTO l_fnd_old_user_name;
1009 -- CLOSE fnd_wfrole_cur;
1010 -- Wf_Directory.GetRoleOrigSysInfo(
1011 -- l_fnd_old_user_name,
1012 -- l_fnd_usr_old_orig_system,
1013 -- l_fnd_usr_old_orig_system_id);
1014 --
1015 -- /* following procedure will end date all the user roles for the resource */
1016 -- move_wf_user_role(ll_resource_id => p_resource_id,
1017 -- ll_start_date_active => l_start_date_active,
1018 -- ll_end_date_active => l_end_date_active,
1019 -- ll_old_user_name => l_fnd_old_user_name,
1020 -- ll_old_user_orig_system => l_fnd_usr_old_orig_system,
1021 -- ll_old_user_orig_system_id => l_fnd_usr_old_orig_system_id,
1022 -- ll_new_user_name => NULL,
1023 -- ll_new_user_orig_system => NULL,
1024 -- ll_new_user_orig_system_id => NULL);
1025 -- end if;
1026 --xx end if; /* If user_id is changed/not changed from one value to another value */
1027 --xx end if; /* If res_rec.user_id is NULL or NOT NULL */
1028 --xx end if; /* If p_user_id is NULL or NOT NULL*/
1029 end if; /* there are some changes and not a future to future updation */
1030
1031 CLOSE res_cur;
1032
1033 IF p_commit is not NULL and fnd_api.to_boolean(p_commit) THEN
1034 COMMIT WORK;
1035 END IF;
1036
1037 EXCEPTION when OTHERS then
1038 ROLLBACK TO upd_emp_wf_save;
1039 x_return_status := fnd_api.g_ret_sts_unexp_error;
1040
1041 END update_resource;
1042
1043 PROCEDURE delete_resource
1044 (P_API_VERSION IN NUMBER,
1045 P_INIT_MSG_LIST IN VARCHAR2,
1046 P_COMMIT IN VARCHAR2,
1047 P_RESOURCE_ID IN NUMBER,
1048 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1049 X_MSG_COUNT OUT NOCOPY NUMBER,
1050 X_MSG_DATA OUT NOCOPY VARCHAR2
1051 ) IS
1052
1053 l_api_version CONSTANT NUMBER := 1.0;
1054 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_RESOURCE';
1055
1056 l_sysdate date := trunc(sysdate);
1057
1058 l_res_usr_orig_system wf_local_roles.orig_system%TYPE := 'JRES_IND';
1059 l_res_usr_role_name wf_local_roles.name%TYPE := l_res_usr_orig_system||':'||to_char(p_resource_id);
1060
1061 CURSOR res_user_role_cur IS
1062 SELECT role_name, role_orig_system, role_orig_system_id
1063 FROM wf_local_user_roles
1064 WHERE user_name = l_res_usr_role_name
1065 AND user_orig_system = l_res_usr_orig_system
1066 AND user_orig_system_id = p_resource_id
1067 AND role_name <> l_res_usr_role_name;
1068
1069 l_list WF_PARAMETER_LIST_T;
1070
1071 BEGIN
1072
1073 x_return_status := fnd_api.g_ret_sts_success;
1074 savepoint del_emp_wf_save;
1075
1076 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1077 RAISE fnd_api.g_exc_unexpected_error;
1078 END IF;
1079
1080 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
1081 if p_init_msg_list is not NULL and fnd_api.to_boolean(p_init_msg_list)
1082 then
1083 fnd_msg_pub.Initialize;
1084 end if;
1085
1086 for i in res_user_role_cur LOOP
1087 Wf_local_synch.propagate_user_role(
1088 p_user_orig_system => l_res_usr_orig_system,
1089 p_user_orig_system_id => p_resource_id,
1090 p_role_orig_system => i.role_orig_system,
1091 p_role_orig_system_id => i.role_orig_system_id,
1092 p_expiration_date => l_sysdate-1);
1093
1094 END LOOP;
1095
1096 /* Changed the code to call Wf_local_synch instead of Wf_Directory
1097 Fix for bug # 2671368 */
1098
1099 AddParameterToList('USER_NAME',l_res_usr_role_name,l_list);
1100 AddParameterToList('RAISEERRORS','TRUE',l_list);
1101 AddParameterToList('DELETE','TRUE',l_list);
1102
1103 Wf_local_synch.propagate_role(
1104 p_orig_system => l_res_usr_orig_system,
1105 p_orig_system_id => p_resource_id,
1106 p_attributes => l_list,
1107 p_expiration_date => l_sysdate-1);
1108
1109 l_list.DELETE;
1110
1111 IF p_commit is not NULL and fnd_api.to_boolean(p_commit) THEN
1112 COMMIT WORK;
1113 END IF;
1114
1115 EXCEPTION when OTHERS then
1116 ROLLBACK TO del_emp_wf_save;
1117 x_return_status := fnd_api.g_ret_sts_unexp_error;
1118 END delete_resource;
1119
1120 PROCEDURE create_resource_group
1121 (P_API_VERSION IN NUMBER,
1122 P_INIT_MSG_LIST IN VARCHAR2,
1123 P_COMMIT IN VARCHAR2,
1124 P_GROUP_ID IN NUMBER,
1125 P_GROUP_NAME IN VARCHAR2,
1126 P_EMAIL_ADDRESS IN VARCHAR2,
1127 P_START_DATE_ACTIVE IN DATE,
1128 P_END_DATE_ACTIVE IN DATE,
1129 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1130 X_MSG_COUNT OUT NOCOPY NUMBER,
1131 X_MSG_DATA OUT NOCOPY VARCHAR2
1132 ) IS
1133 l_api_version CONSTANT NUMBER := 1.0;
1134 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RESOURCE_GROUP';
1135 l_grp_role_name wf_local_roles.name%TYPE := g_grp_orig_system||':'||to_char(p_group_id);
1136 l_start_date_active date := trunc(P_START_DATE_ACTIVE);
1137 l_end_date_active date := trunc(P_END_DATE_ACTIVE);
1138 l_sysdate date := trunc(sysdate);
1139
1140 l_list WF_PARAMETER_LIST_T;
1141
1142 BEGIN
1143 SAVEPOINT wf_int_create_resource_group;
1144 x_return_status := fnd_api.g_ret_sts_success;
1145
1146 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1147 RAISE fnd_api.g_exc_unexpected_error;
1148 END IF;
1149 IF p_init_msg_list is not NULL and fnd_api.to_boolean(p_init_msg_list) THEN
1150 fnd_msg_pub.initialize;
1151 END IF;
1152
1153 -- if (l_start_date_active <= l_sysdate AND
1154 -- (l_end_date_active is null OR
1155 -- l_end_date_active >= l_sysdate)) THEN
1156 if ((l_end_date_active >= l_sysdate) OR (l_end_date_active is NULL)) then
1157 /* Create role only if the group is currently active or future active*/
1158
1159 /* Changed the code to call Wf_local_synch instead of Wf_Directory
1160 Fix for bug # 2671368 */
1161
1162 AddParameterToList('USER_NAME',l_grp_role_name,l_list);
1163 AddParameterToList('DISPLAYNAME',p_group_name,l_list);
1164 AddParameterToList('MAIL',p_email_address,l_list);
1165 AddParameterToList('ORCLISENABLED','ACTIVE',l_list);
1166 AddParameterToList('RAISEERRORS','TRUE',l_list);
1167
1168 Wf_local_synch.propagate_role(
1169 p_orig_system => g_grp_orig_system,
1170 p_orig_system_id => p_group_id,
1171 p_attributes => l_list,
1172 p_start_date => l_start_date_active,
1173 p_expiration_date => l_end_date_active);
1174
1175 l_list.DELETE;
1176
1177 Wf_local_synch.propagate_user_role(
1178 p_user_orig_system => g_grp_orig_system,
1179 p_user_orig_system_id => p_group_id,
1180 p_role_orig_system => g_grp_orig_system,
1181 p_role_orig_system_id => p_group_id,
1182 p_start_date => l_start_date_active,
1183 p_expiration_date => l_end_date_active);
1184
1185 END IF;
1186
1187 IF p_commit is not NULL and fnd_api.to_boolean(p_commit) THEN
1188 COMMIT WORK;
1189 END IF;
1190
1191 EXCEPTION
1192 WHEN OTHERS THEN
1193 /* Since we don't care about
1194 the errors/exceptions in WF API, we are just catching when OTHERS */
1195 -- DBMS_OUTPUT.put_line (' ========================================== ');
1196 -- DBMS_OUTPUT.put_line (' =========== Raised Others in Create Resource Group Pvt ========= ');
1197 -- DBMS_OUTPUT.put_line (SQLCODE || SQLERRM);
1198
1199 ROLLBACK TO wf_int_create_resource_group;
1200 x_return_status := fnd_api.g_ret_sts_unexp_error;
1201 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1202 END;
1203
1204
1205 PROCEDURE update_resource_group
1206 (P_API_VERSION IN NUMBER,
1207 P_INIT_MSG_LIST IN VARCHAR2,
1208 P_COMMIT IN VARCHAR2,
1209 P_GROUP_ID IN NUMBER,
1210 P_GROUP_NAME IN VARCHAR2,
1211 P_EMAIL_ADDRESS IN VARCHAR2,
1212 P_START_DATE_ACTIVE IN DATE,
1213 P_END_DATE_ACTIVE IN DATE,
1214 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1215 X_MSG_COUNT OUT NOCOPY NUMBER,
1216 X_MSG_DATA OUT NOCOPY VARCHAR2
1217 ) IS
1218 l_api_version CONSTANT NUMBER := 1.0;
1219 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RESOURCE_GROUP';
1220 l_grp_role_name wf_local_roles.name%TYPE := g_grp_orig_system||':'||to_char(p_group_id);
1221 l_start_date_active date := trunc(P_START_DATE_ACTIVE);
1222 l_end_date_active date := trunc(P_END_DATE_ACTIVE);
1223 l_sysdate date := trunc(sysdate);
1224 l_g_miss_date date := trunc(to_date('31-12-4712','DD-MM-YYYY'));
1225
1226 CURSOR C_ROLE_EXISTS (P_NAME IN VARCHAR2, P_ORG_SYS IN VARCHAR2, P_ORG_SYS_ID IN NUMBER) IS
1227 SELECT 'Y'
1228 FROM WF_LOCAL_ROLES
1229 WHERE NAME = P_NAME AND
1230 ORIG_SYSTEM_ID = P_ORG_SYS_ID AND
1231 ORIG_SYSTEM = P_ORG_SYS;
1232
1233 CURSOR C_GRP_OLD_VALS(P_GROUP_ID IN NUMBER) IS
1234 SELECT EMAIL_ADDRESS, GROUP_NAME, trunc(START_DATE_ACTIVE) START_DATE_ACTIVE, trunc(END_DATE_ACTIVE) END_DATE_ACTIVE
1235 FROM JTF_RS_GROUPS_VL WHERE GROUP_ID = P_GROUP_ID;
1236
1237 l_old_grp_vals C_GRP_OLD_VALS%ROWTYPE;
1238 l_check_role varchar2(1);
1239
1240 CURSOR grp_mem_cur IS
1241 SELECT mem.resource_id,
1242 greatest(l_start_date_active, res.start_date_active) grp_mem_start_date,
1243 least (nvl(l_end_date_active, l_g_miss_date), nvl(res.end_date_active, l_g_miss_date)) grp_mem_end_date
1244 FROM jtf_rs_group_members mem, jtf_rs_groups_b grp, jtf_rs_resource_extns res
1245 WHERE mem.group_id = grp.group_id
1246 AND mem.resource_id = res.resource_id
1247 AND nvl(mem.delete_flag,'N') <> 'Y'
1248 AND l_sysdate between trunc(res.start_date_active) and nvl(trunc(res.end_date_active),l_sysdate)
1249 AND mem.group_id = p_group_id;
1250
1251 CURSOR grp_as_team_mem_cur IS
1252 SELECT mem.team_id,
1253 trunc(tm.start_date_active) start_date_active,
1254 trunc(tm.end_date_active) end_date_active
1255 FROM jtf_rs_team_members mem, jtf_rs_teams_b tm
1256 WHERE mem.team_id = tm.team_id
1257 AND nvl(mem.delete_flag,'N') <> 'Y'
1258 -- AND l_sysdate between trunc(tm.start_date_active) and nvl(trunc(tm.end_date_active),l_sysdate)
1259 AND mem.team_resource_id = p_group_id
1260 AND mem.RESOURCE_TYPE = 'GROUP';
1261
1262 l_grp_mem_user_name wf_local_roles.name%TYPE;
1263 l_grp_mem_orig_system wf_local_roles.orig_system%TYPE;
1264 l_grp_mem_orig_system_id wf_local_roles.orig_system_id%TYPE;
1265
1266 l_mem_role_start_date date;
1267 l_mem_role_end_date date;
1268
1269 l_list WF_PARAMETER_LIST_T;
1270
1271 BEGIN
1272 SAVEPOINT wf_int_update_resource_group;
1273 x_return_status := fnd_api.g_ret_sts_success;
1274
1275 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1276 RAISE fnd_api.g_exc_unexpected_error;
1277 END IF;
1278 IF p_init_msg_list is not NULL and fnd_api.to_boolean(p_init_msg_list) THEN
1279 fnd_msg_pub.initialize;
1280 END IF;
1281
1282 /* Role record exists then update if group name, email, start date
1283 or end date is changed */
1284 OPEN C_GRP_OLD_VALS(p_group_id);
1285 FETCH C_GRP_OLD_VALS into l_old_grp_vals;
1286
1287 IF C_GRP_OLD_VALS%FOUND AND
1288 (P_GROUP_NAME <> l_old_grp_vals.group_name OR
1289 (P_EMAIL_ADDRESS is null and
1290 l_old_grp_vals.email_address is not null) OR
1291 (P_EMAIL_ADDRESS is not null and
1292 l_old_grp_vals.email_address is null) OR
1293 P_EMAIL_ADDRESS <> l_old_grp_vals.email_address OR
1294 L_START_DATE_ACTIVE <> l_old_grp_vals.start_date_active OR
1295 (L_END_DATE_ACTIVE is null and
1296 l_old_grp_vals.end_date_active is not null) OR
1297 (L_END_DATE_ACTIVE is not null and
1298 l_old_grp_vals.end_date_active is null) OR
1299 L_END_DATE_ACTIVE <> l_old_grp_vals.end_date_active) AND
1300 ((nvl(l_old_grp_vals.end_date_active,l_sysdate) >= l_sysdate) OR
1301 (nvl(l_end_date_active,l_sysdate) >= l_sysdate)) THEN
1302 /* If any of the above is changed and the group old/new end_date is >= l_sysdate, then update the group */
1303
1304 if ((nvl(l_end_date_active,l_sysdate) >= l_sysdate)) then
1305
1306 AddParameterToList('USER_NAME',l_grp_role_name,l_list);
1307 AddParameterToList('DISPLAYNAME',p_group_name,l_list);
1308 AddParameterToList('MAIL',p_email_address,l_list);
1309 AddParameterToList('ORCLISENABLED','ACTIVE',l_list);
1310 AddParameterToList('WFSYNCH_OVERWRITE','TRUE',l_list);
1311
1312 Wf_local_synch.propagate_role(
1313 p_orig_system => g_grp_orig_system,
1314 p_orig_system_id => p_group_id,
1315 p_attributes => l_list,
1316 p_start_date => l_start_date_active,
1317 p_expiration_date => l_end_date_active);
1318
1319 l_list.DELETE;
1320
1321 Wf_local_synch.propagate_user_role(
1322 p_user_orig_system => g_grp_orig_system,
1323 p_user_orig_system_id => p_group_id,
1324 p_role_orig_system => g_grp_orig_system,
1325 p_role_orig_system_id => p_group_id,
1326 p_start_date => l_start_date_active,
1327 p_expiration_date => l_end_date_active,
1328 p_overwrite => TRUE);
1329 end if;
1330 if ((l_start_date_active <> l_old_grp_vals.start_date_active) OR
1331 (l_end_date_active is null and l_old_grp_vals.end_date_active is not null) OR
1332 (l_end_date_active is not null and l_old_grp_vals.end_date_active is null)) THEN
1333 /* above if is to find out if there a change in group dates */
1334
1335 for i in grp_mem_cur LOOP
1336 jtf_rs_wf_integration_pub.get_wf_role(P_RESOURCE_ID => i.resource_id,
1337 X_ROLE_NAME => l_grp_mem_user_name,
1338 X_ORIG_SYSTEM => l_grp_mem_orig_system,
1339 X_ORIG_SYSTEM_ID => l_grp_mem_orig_system_id);
1340 if (i.grp_mem_end_date = l_g_miss_date) then
1341 l_mem_role_end_date := NULL;
1342 else
1343 l_mem_role_end_date := i.grp_mem_end_date;
1344 end if;
1345
1346 IF l_grp_mem_user_name is not null THEN
1347 Wf_local_synch.propagate_user_role(
1348 p_user_orig_system => l_grp_mem_orig_system,
1349 p_user_orig_system_id => l_grp_mem_orig_system_id,
1350 p_role_orig_system => g_grp_orig_system,
1351 p_role_orig_system_id => p_group_id,
1352 p_start_date => i.grp_mem_start_date,
1353 p_expiration_date => l_mem_role_end_date,
1354 p_overwrite => TRUE);
1355 END IF;
1356 END LOOP;
1357
1358 for j in grp_as_team_mem_cur LOOP
1359 l_mem_role_start_date := greatest(l_start_date_active, j.start_date_active);
1360 l_mem_role_end_date := least (nvl(l_end_date_active, l_g_miss_date), nvl(j.end_date_active, l_g_miss_date));
1361
1362 if (l_mem_role_end_date = l_g_miss_date) then
1363 l_mem_role_end_date := NULL;
1364 end if;
1365
1366 l_check_role := 'N';
1367 OPEN c_role_exists(g_team_orig_system||':'||to_char(j.team_id), g_team_orig_system, j.team_id);
1368 FETCH c_role_exists into l_check_role;
1369 CLOSE c_role_exists;
1370
1371 IF (l_check_role = 'Y') THEN
1372 Wf_local_synch.propagate_user_role(
1373 p_user_orig_system => g_grp_orig_system,
1374 p_user_orig_system_id => p_group_id,
1375 p_role_orig_system => g_team_orig_system,
1376 p_role_orig_system_id => j.team_id,
1377 p_start_date => l_mem_role_start_date,
1378 p_expiration_date => l_mem_role_end_date,
1379 p_overwrite => TRUE);
1380 END IF;
1381 END LOOP;
1382 end if;
1383 if ((nvl(l_end_date_active,l_sysdate) < l_sysdate)) then
1384
1385 Wf_local_synch.propagate_user_role(
1386 p_user_orig_system => g_grp_orig_system,
1387 p_user_orig_system_id => p_group_id,
1388 p_role_orig_system => g_grp_orig_system,
1389 p_role_orig_system_id => p_group_id,
1390 p_start_date => l_start_date_active,
1391 p_expiration_date => l_end_date_active,
1392 p_overwrite => TRUE);
1393
1394 AddParameterToList('USER_NAME',l_grp_role_name,l_list);
1395 AddParameterToList('DISPLAYNAME',p_group_name,l_list);
1396 AddParameterToList('MAIL',p_email_address,l_list);
1397 AddParameterToList('ORCLISENABLED','ACTIVE',l_list);
1398 AddParameterToList('WFSYNCH_OVERWRITE','TRUE',l_list);
1399
1400 Wf_local_synch.propagate_role(
1401 p_orig_system => g_grp_orig_system,
1402 p_orig_system_id => p_group_id,
1403 p_attributes => l_list,
1404 p_start_date => l_start_date_active,
1405 p_expiration_date => l_end_date_active);
1406
1407 l_list.DELETE;
1408
1409 end if;
1410
1411 END IF; /* C_GRP_OLD_VALS%FOUND .. */
1412
1413 IF c_grp_old_vals%ISOPEN THEN
1414 CLOSE c_grp_old_vals;
1415 END IF;
1416
1417 IF c_role_exists%ISOPEN THEN
1418 CLOSE c_role_exists;
1419 END IF;
1420
1421 IF p_commit is not NULL and fnd_api.to_boolean(p_commit) THEN
1422 COMMIT WORK;
1423 END IF;
1424
1425 EXCEPTION
1426 WHEN OTHERS THEN
1427 /* Since we don't care about
1428 the errors/exceptions in WF API, we are just catching when OTHERS */
1429 -- DBMS_OUTPUT.put_line (' ========================================== ');
1430 -- DBMS_OUTPUT.put_line (' =========== Raised Others in Update Resource Group Pvt ========= ');
1431 -- DBMS_OUTPUT.put_line (SQLCODE || SQLERRM);
1432
1433 IF c_grp_old_vals%ISOPEN THEN
1434 CLOSE c_grp_old_vals;
1435 END IF;
1436
1437 IF c_role_exists%ISOPEN THEN
1438 CLOSE c_role_exists;
1439 END IF;
1440
1441 ROLLBACK TO wf_int_update_resource_group;
1442 x_return_status := fnd_api.g_ret_sts_unexp_error;
1443 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1444 END;
1445
1446
1447 PROCEDURE create_resource_group_members
1448 (P_API_VERSION IN NUMBER,
1449 P_INIT_MSG_LIST IN VARCHAR2,
1450 P_COMMIT IN VARCHAR2,
1451 P_RESOURCE_ID IN NUMBER,
1452 P_GROUP_ID IN NUMBER,
1453 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1454 X_MSG_COUNT OUT NOCOPY NUMBER,
1455 X_MSG_DATA OUT NOCOPY VARCHAR2
1456 ) IS
1457 l_api_version CONSTANT NUMBER := 1.0;
1458 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RESOURCE_GROUP_MEMBERS';
1459 l_grp_role_name wf_local_roles.name%TYPE := g_grp_orig_system||':'||to_char(p_group_id);
1460 l_sysdate date := trunc(sysdate);
1461
1462 CURSOR C_ROLE_EXISTS (P_NAME IN VARCHAR2, P_ORG_SYS IN VARCHAR2, P_ORG_SYS_ID IN NUMBER) IS
1463 SELECT 'Y'
1464 FROM WF_LOCAL_ROLES
1465 WHERE NAME = P_NAME AND
1466 ORIG_SYSTEM_ID = P_ORG_SYS_ID AND
1467 ORIG_SYSTEM = P_ORG_SYS;
1468
1469 CURSOR c_grp_active (p_group_id IN NUMBER) IS
1470 SELECT trunc(START_DATE_ACTIVE) START_DATE_ACTIVE,
1471 trunc(END_DATE_ACTIVE) END_DATE_ACTIVE
1472 FROM JTF_RS_GROUPS_B
1473 WHERE GROUP_ID = P_GROUP_ID AND
1474 trunc(START_DATE_ACTIVE) <= l_sysdate AND
1475 NVL(trunc(END_DATE_ACTIVE), l_sysdate) >= l_sysdate;
1476
1477 CURSOR c_res_active (p_resource_id IN NUMBER) IS
1478 SELECT trunc(START_DATE_ACTIVE) START_DATE_ACTIVE,
1479 trunc(END_DATE_ACTIVE) END_DATE_ACTIVE
1480 FROM JTF_RS_RESOURCE_EXTNS
1481 WHERE RESOURCE_ID = P_RESOURCE_ID AND
1482 trunc(START_DATE_ACTIVE) <= l_sysdate AND
1483 NVL(trunc(END_DATE_ACTIVE), l_sysdate) >= l_sysdate;
1484
1485 l_group_role_exists c_role_exists%ROWTYPE;
1486 l_grp_active c_grp_active%ROWTYPE;
1487 l_res_active c_res_active%ROWTYPE;
1488
1489 l_user_name wf_local_roles.name%TYPE;
1490 l_orig_system wf_local_roles.orig_system%TYPE;
1491 l_orig_system_id wf_local_roles.orig_system_id%TYPE;
1492
1493 l_mem_role_start_date date;
1494 l_mem_role_end_date date;
1495 l_g_miss_date date := trunc(to_date('31-12-4712','DD-MM-YYYY'));
1496
1497 BEGIN
1498 SAVEPOINT wf_int_cr_res_grp_mbr;
1499 x_return_status := fnd_api.g_ret_sts_success;
1500
1501 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1502 RAISE fnd_api.g_exc_unexpected_error;
1503 END IF;
1504 IF p_init_msg_list is not NULL and fnd_api.to_boolean(p_init_msg_list) THEN
1505 fnd_msg_pub.initialize;
1506 END IF;
1507
1508 OPEN c_grp_active(p_group_id);
1509 FETCH c_grp_active INTO l_grp_active;
1510
1511 IF (c_grp_active%FOUND) THEN
1512 OPEN c_res_active(p_resource_id);
1513 FETCH c_res_active INTO l_res_active;
1514 IF (c_res_active%FOUND) THEN
1515
1516 l_mem_role_start_date := greatest(l_res_active.start_date_active, l_grp_active.start_date_active);
1517 l_mem_role_end_date := least (nvl(l_res_active.end_date_active, l_g_miss_date), nvl(l_grp_active.end_date_active, l_g_miss_date));
1518
1519 if (l_mem_role_end_date = l_g_miss_date) then
1520 l_mem_role_end_date := NULL;
1521 end if;
1522
1523 -- get_user_role_dates
1524 -- (p_user_start_date => l_res_active.start_date_active,
1525 -- p_user_end_date => l_res_active.end_date_active,
1526 -- p_role_start_date => l_grp_active.start_date_active,
1527 -- p_role_end_date => l_grp_active.end_date_active,
1528 -- x_user_role_start_date => l_mem_role_start_date,
1529 -- x_user_role_end_date => l_mem_role_end_date);
1530
1531 /* Group as well as resource are active */
1532 OPEN c_role_exists(l_grp_role_name, g_grp_orig_system, p_group_id);
1533 FETCH c_role_exists into l_group_role_exists;
1534 IF (c_role_exists%FOUND) THEN
1535 jtf_rs_wf_integration_pub.get_wf_role(P_RESOURCE_ID => P_RESOURCE_ID,
1536 X_ROLE_NAME => l_user_name,
1537 X_ORIG_SYSTEM => l_orig_system,
1538 X_ORIG_SYSTEM_ID => l_orig_system_id);
1539
1540
1541 IF l_user_name is not null THEN
1542 Wf_local_synch.propagate_user_role(
1543 p_user_orig_system => l_orig_system,
1544 p_user_orig_system_id => l_orig_system_id,
1545 p_role_orig_system => g_grp_orig_system,
1546 p_role_orig_system_id => p_group_id,
1547 p_start_date => l_mem_role_start_date,
1548 p_expiration_date => l_mem_role_end_date,
1549 p_overwrite => TRUE);
1550 END IF;
1551 END IF;
1552 CLOSE c_role_exists;
1553 END IF;
1554 CLOSE c_res_active;
1555 END IF;
1556
1557 CLOSE c_grp_active;
1558
1559 IF p_commit is not NULL and fnd_api.to_boolean(p_commit) THEN
1560 COMMIT WORK;
1561 END IF;
1562
1563 EXCEPTION
1564 WHEN OTHERS THEN
1565 /* Since we don't care about
1566 the errors/exceptions in WF API, we are just catching when OTHERS */
1567 -- DBMS_OUTPUT.put_line (' ========================================== ');
1568 -- DBMS_OUTPUT.put_line (' =========== Raised Others in Create Resource Group Member Pvt ========= ');
1569 -- DBMS_OUTPUT.put_line (SQLCODE || SQLERRM);
1570
1571 IF c_role_exists%ISOPEN THEN
1572 CLOSE c_role_exists;
1573 END IF;
1574
1575 IF c_grp_active%ISOPEN THEN
1576 CLOSE c_grp_active;
1577 END IF;
1578
1579 IF c_res_active%ISOPEN THEN
1580 CLOSE c_res_active;
1581 END IF;
1582
1583 ROLLBACK TO wf_int_cr_res_grp_mbr;
1584 x_return_status := fnd_api.g_ret_sts_unexp_error;
1585 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1586 END;
1587
1588 PROCEDURE delete_resource_group_members
1589 (P_API_VERSION IN NUMBER,
1590 P_INIT_MSG_LIST IN VARCHAR2,
1591 P_COMMIT IN VARCHAR2,
1592 P_RESOURCE_ID IN NUMBER,
1593 P_GROUP_ID IN NUMBER,
1594 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1595 X_MSG_COUNT OUT NOCOPY NUMBER,
1596 X_MSG_DATA OUT NOCOPY VARCHAR2
1597 ) IS
1598 l_api_version CONSTANT NUMBER := 1.0;
1599 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_RESOURCE_GROUP_MEMBERS';
1600 l_grp_role_name wf_local_roles.name%TYPE := g_grp_orig_system||':'||to_char(p_group_id);
1601
1602 l_user_name wf_local_roles.name%TYPE;
1603 l_orig_system wf_local_roles.orig_system%TYPE;
1604 l_orig_system_id wf_local_roles.orig_system_id%TYPE;
1605 BEGIN
1606 SAVEPOINT wf_int_del_res_grp_mbr;
1607 x_return_status := fnd_api.g_ret_sts_success;
1608
1609 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1610 RAISE fnd_api.g_exc_unexpected_error;
1611 END IF;
1612 IF p_init_msg_list is not NULL and fnd_api.to_boolean(p_init_msg_list) THEN
1613 fnd_msg_pub.initialize;
1614 END IF;
1615
1616 jtf_rs_wf_integration_pub.get_wf_role(P_RESOURCE_ID => P_RESOURCE_ID,
1617 X_ROLE_NAME => l_user_name,
1618 X_ORIG_SYSTEM => l_orig_system,
1619 X_ORIG_SYSTEM_ID => l_orig_system_id);
1620
1621 IF l_user_name is not null THEN
1622
1623 /* Changed the code to call Wf_local_synch instead of Wf_Directory
1624 Fix for bug # 2671368 */
1625
1626 Wf_local_synch.propagate_user_role(
1627 p_user_orig_system => l_orig_system,
1628 p_user_orig_system_id => l_orig_system_id,
1629 p_role_orig_system => g_grp_orig_system,
1630 p_role_orig_system_id => p_group_id,
1631 -- p_start_date => sysdate,
1632 p_expiration_date => sysdate-1);
1633 END IF;
1634
1635
1636 IF p_commit is not NULL and fnd_api.to_boolean(p_commit) THEN
1637 COMMIT WORK;
1638 END IF;
1639
1640 EXCEPTION
1641 WHEN OTHERS THEN
1642 /* Since we don't care about
1643 the errors/exceptions in WF API, we are just catching when OTHERS */
1644 -- DBMS_OUTPUT.put_line (' ========================================== ');
1645 -- DBMS_OUTPUT.put_line (' =========== Raised Others in Delete Resource Group Member Pvt ========= ');
1646 -- DBMS_OUTPUT.put_line (SQLCODE || SQLERRM);
1647
1648 ROLLBACK TO wf_int_del_res_grp_mbr;
1649 x_return_status := fnd_api.g_ret_sts_unexp_error;
1650 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1651 END;
1652
1653 PROCEDURE create_resource_team
1654 (P_API_VERSION IN NUMBER,
1655 P_INIT_MSG_LIST IN VARCHAR2,
1656 P_COMMIT IN VARCHAR2,
1657 P_TEAM_ID IN NUMBER,
1658 P_TEAM_NAME IN VARCHAR2,
1659 P_EMAIL_ADDRESS IN VARCHAR2,
1660 P_START_DATE_ACTIVE IN DATE,
1661 P_END_DATE_ACTIVE IN DATE,
1662 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1663 X_MSG_COUNT OUT NOCOPY NUMBER,
1664 X_MSG_DATA OUT NOCOPY VARCHAR2
1665 ) IS
1666 l_api_version CONSTANT NUMBER := 1.0;
1667 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RESOURCE_TEAM';
1668 l_team_role_name wf_local_roles.name%TYPE := g_team_orig_system||':'||to_char(p_team_id);
1669 l_start_date_active date := trunc(P_START_DATE_ACTIVE);
1670 l_end_date_active date := trunc(P_END_DATE_ACTIVE);
1671 l_sysdate date := trunc(sysdate);
1672
1673 l_list WF_PARAMETER_LIST_T;
1674
1675 BEGIN
1676 SAVEPOINT wf_int_create_resource_team;
1677 x_return_status := fnd_api.g_ret_sts_success;
1678
1679 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name,
1680 g_pkg_name) THEN
1681 RAISE fnd_api.g_exc_unexpected_error;
1682 END IF;
1683 IF p_init_msg_list is not NULL and fnd_api.to_boolean(p_init_msg_list) THEN
1684 fnd_msg_pub.initialize;
1685 END IF;
1686
1687 -- if (l_start_date_active <= l_sysdate AND
1688 -- (l_end_date_active is null OR
1689 -- l_end_date_active >= l_sysdate)) THEN
1690 if ( (l_end_date_active >= l_sysdate) OR (l_end_date_active is NULL) ) then
1691 /* Create role only if team is active */
1692
1693 /* Changed the code to call Wf_local_synch instead of Wf_Directory
1694 Fix for bug # 2671368 */
1695
1696 AddParameterToList('USER_NAME',l_team_role_name,l_list);
1697 AddParameterToList('DISPLAYNAME',p_team_name,l_list);
1698 AddParameterToList('MAIL',p_email_address,l_list);
1699 AddParameterToList('ORCLISENABLED','ACTIVE',l_list);
1700
1701 Wf_local_synch.propagate_role(
1702 p_orig_system => g_team_orig_system,
1703 p_orig_system_id => p_team_id,
1704 p_attributes => l_list,
1705 p_start_date => l_start_date_active,
1706 p_expiration_date => l_end_date_active);
1707
1708 l_list.DELETE;
1709
1710 Wf_local_synch.propagate_user_role(
1711 p_user_orig_system => g_team_orig_system,
1712 p_user_orig_system_id => p_team_id,
1713 p_role_orig_system => g_team_orig_system,
1714 p_role_orig_system_id => p_team_id,
1715 p_start_date => l_start_date_active,
1716 p_expiration_date => l_end_date_active);
1717 END IF;
1718
1719 IF p_commit is not NULL and fnd_api.to_boolean(p_commit) THEN
1720 COMMIT WORK;
1721 END IF;
1722
1723 EXCEPTION
1724 WHEN OTHERS THEN
1725 /* Since we don't care about
1726 the errors/exceptions in WF API, we are just catching when OTHERS */
1727 -- DBMS_OUTPUT.put_line (' ========================================== ');
1728 -- DBMS_OUTPUT.put_line (' =========== Raised Others in Create Resource Team Pvt ========= ');
1729 -- DBMS_OUTPUT.put_line (SQLCODE || SQLERRM);
1730
1731 ROLLBACK TO wf_int_create_resource_team;
1732 x_return_status := fnd_api.g_ret_sts_unexp_error;
1733 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1734 END;
1735
1736 PROCEDURE update_resource_team
1737 (P_API_VERSION IN NUMBER,
1738 P_INIT_MSG_LIST IN VARCHAR2,
1739 P_COMMIT IN VARCHAR2,
1740 P_TEAM_ID IN NUMBER,
1741 P_TEAM_NAME IN VARCHAR2,
1742 P_EMAIL_ADDRESS IN VARCHAR2,
1743 P_START_DATE_ACTIVE IN DATE,
1744 P_END_DATE_ACTIVE IN DATE,
1745 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1746 X_MSG_COUNT OUT NOCOPY NUMBER,
1747 X_MSG_DATA OUT NOCOPY VARCHAR2
1748 ) IS
1749 l_api_version CONSTANT NUMBER := 1.0;
1750 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RESOURCE_TEAM';
1751 l_team_role_name wf_local_roles.name%TYPE := g_team_orig_system||':'||to_char(p_team_id);
1752 l_start_date_active date := trunc(P_START_DATE_ACTIVE);
1753 l_end_date_active date := trunc(P_END_DATE_ACTIVE);
1754 l_sysdate date := trunc(sysdate);
1755 l_g_miss_date date := trunc(to_date('31-12-4712','DD-MM-YYYY'));
1756
1757 CURSOR C_ROLE_EXISTS (P_NAME IN VARCHAR2, P_ORG_SYS IN VARCHAR2, P_ORG_SYS_ID IN NUMBER) IS
1758 SELECT 'Y'
1759 FROM WF_LOCAL_ROLES
1760 WHERE NAME = P_NAME AND
1761 ORIG_SYSTEM_ID = P_ORG_SYS_ID AND
1762 ORIG_SYSTEM = P_ORG_SYS;
1763
1764 CURSOR C_TEAM_OLD_VALS(P_TEAM_ID IN NUMBER) IS
1765 SELECT EMAIL_ADDRESS, TEAM_NAME, trunc(START_DATE_ACTIVE) START_DATE_ACTIVE, trunc(END_DATE_ACTIVE) END_DATE_ACTIVE
1766 FROM JTF_RS_TEAMS_VL WHERE TEAM_ID = P_TEAM_ID;
1767
1768 l_old_team_vals C_TEAM_OLD_VALS%ROWTYPE;
1769 l_check_role C_ROLE_EXISTS%ROWTYPE;
1770
1771 CURSOR team_mem_cur IS
1772 SELECT mem.team_resource_id,
1773 mem.resource_type
1774 FROM jtf_rs_team_members mem, jtf_rs_teams_b team
1775 WHERE mem.team_id = team.team_id
1776 AND nvl(mem.delete_flag,'N') <> 'Y'
1777 -- AND l_sysdate between trunc(team.start_date_active) and nvl(trunc(team.end_date_active),l_sysdate)
1778 AND team.team_id = p_team_id;
1779
1780 CURSOR res_dates(c_resource_id NUMBER) IS
1781 SELECT trunc(start_date_active) start_date_active,
1782 trunc(end_date_active) end_date_active
1783 FROM jtf_rs_resource_extns
1784 WHERE resource_id = c_resource_id;
1785
1786 CURSOR group_dates(c_group_id NUMBER) IS
1787 SELECT trunc(start_date_active) start_date_active,
1788 trunc(end_date_active) end_date_active
1789 FROM jtf_rs_groups_b
1790 WHERE group_id = c_group_id;
1791
1792 l_team_mem_user_name wf_local_roles.name%TYPE;
1793 l_team_mem_orig_system wf_local_roles.orig_system%TYPE;
1794 l_team_mem_orig_system_id wf_local_roles.orig_system_id%TYPE;
1795
1796 l_team_mem_start_date date;
1797 l_team_mem_end_date date;
1798
1799 l_list WF_PARAMETER_LIST_T;
1800
1801 BEGIN
1802 SAVEPOINT wf_int_update_resource_team;
1803 x_return_status := fnd_api.g_ret_sts_success;
1804
1805 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1806 RAISE fnd_api.g_exc_unexpected_error;
1807 END IF;
1808 IF p_init_msg_list is not NULL and fnd_api.to_boolean(p_init_msg_list) THEN
1809 fnd_msg_pub.initialize;
1810 END IF;
1811
1812 /* Role record exists then update if team name, email, start date
1813 or end date is changed */
1814 OPEN C_TEAM_OLD_VALS(p_team_id);
1815 FETCH C_TEAM_OLD_VALS into l_old_team_vals;
1816
1817 IF C_TEAM_OLD_VALS%FOUND AND
1818 (P_TEAM_NAME <> l_old_team_vals.team_name OR
1819 (P_EMAIL_ADDRESS is null and
1820 l_old_team_vals.email_address is not null) OR
1821 (P_EMAIL_ADDRESS is not null and
1822 l_old_team_vals.email_address is null) OR
1823 P_EMAIL_ADDRESS <> l_old_team_vals.email_address OR
1824 L_START_DATE_ACTIVE <> l_old_team_vals.start_date_active OR
1825 (L_END_DATE_ACTIVE is null and
1826 l_old_team_vals.end_date_active is not null) OR
1827 (L_END_DATE_ACTIVE is not null and
1828 l_old_team_vals.end_date_active is null) OR
1829 L_END_DATE_ACTIVE <> l_old_team_vals.end_date_active) AND
1830 ((nvl(l_old_team_vals.end_date_active,l_sysdate) >= l_sysdate) OR
1831 (nvl(l_end_date_active,l_sysdate) >= l_sysdate)) THEN
1832 /* If any of the above is changed and the team old/new end_date is >= l_sysdate, then update the team */
1833
1834 if ((nvl(l_end_date_active,l_sysdate) >= l_sysdate)) then
1835
1836 AddParameterToList('USER_NAME',l_team_role_name,l_list);
1837 AddParameterToList('DISPLAYNAME',p_team_name,l_list);
1838 AddParameterToList('MAIL',p_email_address,l_list);
1839 AddParameterToList('ORCLISENABLED','ACTIVE',l_list);
1840 AddParameterToList('WFSYNCH_OVERWRITE','TRUE',l_list);
1841
1842 Wf_local_synch.propagate_role(
1843 p_orig_system => g_team_orig_system,
1844 p_orig_system_id => p_team_id,
1845 p_attributes => l_list,
1846 p_start_date => l_start_date_active,
1847 p_expiration_date => l_end_date_active);
1848
1849 l_list.DELETE;
1850
1851 Wf_local_synch.propagate_user_role(
1852 p_user_orig_system => g_team_orig_system,
1853 p_user_orig_system_id => p_team_id,
1854 p_role_orig_system => g_team_orig_system,
1855 p_role_orig_system_id => p_team_id,
1856 p_start_date => l_start_date_active,
1857 p_expiration_date => l_end_date_active,
1858 p_overwrite => TRUE);
1859 end if;
1860
1861 if ((l_start_date_active <> l_old_team_vals.start_date_active) OR
1862 (l_end_date_active is null and l_old_team_vals.end_date_active is not null) OR
1863 (l_end_date_active is not null and l_old_team_vals.end_date_active is null)) THEN
1864 /* above if is to find out if there a change in group dates */
1865
1866 for i in team_mem_cur LOOP
1867
1868 if i.resource_type = 'INDIVIDUAL' then
1869 jtf_rs_wf_integration_pub.get_wf_role(P_RESOURCE_ID => i.team_resource_id,
1870 X_ROLE_NAME => l_team_mem_user_name,
1871 X_ORIG_SYSTEM => l_team_mem_orig_system,
1872 X_ORIG_SYSTEM_ID => l_team_mem_orig_system_id);
1873
1874 OPEN res_dates(i.team_resource_id);
1875 FETCH res_dates into l_team_mem_start_date, l_team_mem_end_date;
1876 CLOSE res_dates;
1877 elsif i.resource_type = 'GROUP' then
1878 l_team_mem_orig_system := g_grp_orig_system;
1879 l_team_mem_orig_system_id := i.team_resource_id;
1880 l_team_mem_user_name := l_team_mem_orig_system||':'||to_char(l_team_mem_orig_system_id);
1881
1882 OPEN group_dates(i.team_resource_id);
1883 FETCH group_dates into l_team_mem_start_date, l_team_mem_end_date;
1884 CLOSE group_dates;
1885 end if;
1886
1887 l_team_mem_start_date := greatest(l_team_mem_start_date, l_start_date_active);
1888 l_team_mem_end_date := least (nvl(l_team_mem_end_date, l_g_miss_date), nvl(l_end_date_active, l_g_miss_date));
1889
1890 if (l_team_mem_end_date = l_g_miss_date) then
1891 l_team_mem_end_date := NULL;
1892 end if;
1893
1894 Wf_local_synch.propagate_user_role(
1895 p_user_orig_system => l_team_mem_orig_system,
1896 p_user_orig_system_id => l_team_mem_orig_system_id,
1897 p_role_orig_system => g_team_orig_system,
1898 p_role_orig_system_id => p_team_id,
1899 p_start_date => l_team_mem_start_date,
1900 p_expiration_date => l_team_mem_end_date,
1901 p_overwrite => TRUE);
1902
1903 END LOOP;
1904 end if;
1905
1906 if ((nvl(l_end_date_active,l_sysdate) < l_sysdate)) then
1907
1908 Wf_local_synch.propagate_user_role(
1909 p_user_orig_system => g_team_orig_system,
1910 p_user_orig_system_id => p_team_id,
1911 p_role_orig_system => g_team_orig_system,
1912 p_role_orig_system_id => p_team_id,
1913 p_start_date => l_start_date_active,
1914 p_expiration_date => l_end_date_active,
1915 p_overwrite => TRUE);
1916
1917 AddParameterToList('USER_NAME',l_team_role_name,l_list);
1918 AddParameterToList('DISPLAYNAME',p_team_name,l_list);
1919 AddParameterToList('MAIL',p_email_address,l_list);
1920 AddParameterToList('ORCLISENABLED','ACTIVE',l_list);
1921 AddParameterToList('WFSYNCH_OVERWRITE','TRUE',l_list);
1922
1923 Wf_local_synch.propagate_role(
1924 p_orig_system => g_team_orig_system,
1925 p_orig_system_id => p_team_id,
1926 p_attributes => l_list,
1927 p_start_date => l_start_date_active,
1928 p_expiration_date => l_end_date_active);
1929
1930 l_list.DELETE;
1931
1932 end if;
1933
1934 END IF; /* C_TEAM_OLD_VALS%FOUND .. */
1935
1936 IF c_team_old_vals%ISOPEN THEN
1937 CLOSE c_team_old_vals;
1938 END IF;
1939
1940 IF c_role_exists%ISOPEN THEN
1941 CLOSE c_role_exists;
1942 END IF;
1943
1944 IF p_commit is not NULL and fnd_api.to_boolean(p_commit) THEN
1945 COMMIT WORK;
1946 END IF;
1947
1948 EXCEPTION
1949 WHEN OTHERS THEN
1950 /* Since we don't care about
1951 the errors/exceptions in WF API, we are just catching when OTHERS */
1952 -- DBMS_OUTPUT.put_line (' ========================================== ');
1953 -- DBMS_OUTPUT.put_line (' =========== Raised Others in Update Resource Team Pvt ========= ');
1954 -- DBMS_OUTPUT.put_line (SQLCODE || SQLERRM);
1955
1956 IF c_team_old_vals%ISOPEN THEN
1957 CLOSE c_team_old_vals;
1958 END IF;
1959
1960 IF c_role_exists%ISOPEN THEN
1961 CLOSE c_role_exists;
1962 END IF;
1963
1964 ROLLBACK TO wf_int_update_resource_team;
1965 x_return_status := fnd_api.g_ret_sts_unexp_error;
1966 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1967 END;
1968
1969 PROCEDURE create_resource_team_members
1970 (P_API_VERSION IN NUMBER,
1971 P_INIT_MSG_LIST IN VARCHAR2,
1972 P_COMMIT IN VARCHAR2,
1973 P_RESOURCE_ID IN NUMBER,
1974 P_GROUP_ID IN NUMBER,
1975 P_TEAM_ID IN NUMBER,
1976 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1977 X_MSG_COUNT OUT NOCOPY NUMBER,
1978 X_MSG_DATA OUT NOCOPY VARCHAR2
1979 ) IS
1980 l_api_version CONSTANT NUMBER := 1.0;
1981 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RESOURCE_TEAM_MEMBERS';
1982 l_team_role_name wf_local_roles.name%TYPE := g_team_orig_system||':'||to_char(p_team_id);
1983 l_sysdate date := trunc(sysdate);
1984
1985 CURSOR C_ROLE_EXISTS (P_NAME IN VARCHAR2, P_ORG_SYS IN VARCHAR2, P_ORG_SYS_ID IN NUMBER) IS
1986 SELECT 'Y'
1987 FROM WF_LOCAL_ROLES
1988 WHERE NAME = P_NAME AND
1989 ORIG_SYSTEM_ID = P_ORG_SYS_ID AND
1990 ORIG_SYSTEM = P_ORG_SYS;
1991
1992 CURSOR c_team_active (p_team_id IN NUMBER) IS
1993 SELECT trunc(START_DATE_ACTIVE) START_DATE_ACTIVE,
1994 trunc(END_DATE_ACTIVE) END_DATE_ACTIVE
1995 FROM JTF_RS_TEAMS_B
1996 WHERE TEAM_ID = P_TEAM_ID AND
1997 trunc(START_DATE_ACTIVE) <= l_sysdate AND
1998 NVL(trunc(END_DATE_ACTIVE), l_sysdate) >= l_sysdate;
1999
2000 CURSOR c_res_active (p_resource_id IN NUMBER) IS
2001 SELECT trunc(START_DATE_ACTIVE) START_DATE_ACTIVE,
2002 trunc(END_DATE_ACTIVE) END_DATE_ACTIVE
2003 FROM JTF_RS_RESOURCE_EXTNS
2004 WHERE RESOURCE_ID = P_RESOURCE_ID AND
2005 trunc(START_DATE_ACTIVE) <= l_sysdate AND
2006 NVL(trunc(END_DATE_ACTIVE), l_sysdate) >= l_sysdate;
2007
2008 CURSOR c_grp_active (p_group_id IN NUMBER) IS
2009 SELECT trunc(START_DATE_ACTIVE) START_DATE_ACTIVE,
2010 trunc(END_DATE_ACTIVE) END_DATE_ACTIVE
2011 FROM JTF_RS_GROUPS_B
2012 WHERE GROUP_ID = P_GROUP_ID AND
2013 trunc(START_DATE_ACTIVE) <= l_sysdate AND
2014 NVL(trunc(END_DATE_ACTIVE), l_sysdate) >= l_sysdate;
2015
2016 l_role_exists c_role_exists%ROWTYPE;
2017 l_team_active c_team_active%ROWTYPE;
2018 l_res_active c_res_active%ROWTYPE;
2019 l_grp_active c_grp_active%ROWTYPE;
2020
2021 l_user_name wf_local_roles.name%TYPE;
2022 l_orig_system wf_local_roles.orig_system%TYPE;
2023 l_orig_system_id wf_local_roles.orig_system_id%TYPE;
2024
2025 l_mem_role_start_date date;
2026 l_mem_role_end_date date;
2027 l_g_miss_date date := trunc(to_date('31-12-4712','DD-MM-YYYY'));
2028
2029 BEGIN
2030 SAVEPOINT wf_int_cr_res_team_mbr;
2031 x_return_status := fnd_api.g_ret_sts_success;
2032
2033 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
2034 RAISE fnd_api.g_exc_unexpected_error;
2035 END IF;
2036 IF p_init_msg_list is not NULL and fnd_api.to_boolean(p_init_msg_list) THEN
2037 fnd_msg_pub.initialize;
2038 END IF;
2039
2040 OPEN c_team_active(p_team_id);
2041 FETCH c_team_active INTO l_team_active;
2042
2043 IF (c_team_active%FOUND) THEN
2044 IF (p_resource_id is not null) THEN
2045 OPEN c_res_active(p_resource_id);
2046 FETCH c_res_active INTO l_res_active;
2047 IF (c_res_active%FOUND) THEN
2048
2049 l_mem_role_start_date := greatest(l_res_active.start_date_active, l_team_active.start_date_active);
2050 l_mem_role_end_date := least (nvl(l_res_active.end_date_active, l_g_miss_date), nvl(l_team_active.end_date_active, l_g_miss_date));
2051
2052 if (l_mem_role_end_date = l_g_miss_date) then
2053 l_mem_role_end_date := NULL;
2054 end if;
2055
2056 -- get_user_role_dates
2057 -- (p_user_start_date => l_res_active.start_date_active,
2058 -- p_user_end_date => l_res_active.end_date_active,
2059 -- p_role_start_date => l_team_active.start_date_active,
2060 -- p_role_end_date => l_team_active.end_date_active,
2061 -- x_user_role_start_date => l_mem_role_start_date,
2062 -- x_user_role_end_date => l_mem_role_end_date);
2063
2064 /* Team as well as resource are active */
2065 OPEN c_role_exists(l_team_role_name, g_team_orig_system, p_team_id);
2066 FETCH c_role_exists into l_role_exists;
2067 IF (c_role_exists%FOUND) THEN
2068 jtf_rs_wf_integration_pub.get_wf_role(P_RESOURCE_ID => P_RESOURCE_ID,
2069 X_ROLE_NAME => l_user_name,
2070 X_ORIG_SYSTEM => l_orig_system,
2071 X_ORIG_SYSTEM_ID => l_orig_system_id);
2072
2073
2074 IF l_user_name is not null THEN
2075
2076 Wf_local_synch.propagate_user_role(
2077 p_user_orig_system => l_orig_system,
2078 p_user_orig_system_id => l_orig_system_id,
2079 p_role_orig_system => g_team_orig_system,
2080 p_role_orig_system_id => p_team_id,
2081 p_start_date => l_mem_role_start_date,
2082 p_expiration_date => l_mem_role_end_date,
2083 p_overwrite => TRUE);
2084 END IF;
2085 END IF;
2086 CLOSE c_role_exists;
2087 END IF;
2088 CLOSE c_res_active;
2089 ELSIF (p_group_id is not null) THEN
2090 OPEN c_grp_active(p_group_id);
2091 FETCH c_grp_active INTO l_grp_active;
2092 IF (c_grp_active%FOUND) THEN
2093
2094 l_mem_role_start_date := greatest(l_grp_active.start_date_active, l_team_active.start_date_active);
2095 l_mem_role_end_date := least (nvl(l_grp_active.end_date_active, l_g_miss_date), nvl(l_team_active.end_date_active, l_g_miss_date));
2096
2097 if (l_mem_role_end_date = l_g_miss_date) then
2098 l_mem_role_end_date := NULL;
2099 end if;
2100
2101 -- get_user_role_dates
2102 -- (p_user_start_date => l_grp_active.start_date_active,
2103 -- p_user_end_date => l_grp_active.end_date_active,
2104 -- p_role_start_date => l_team_active.start_date_active,
2105 -- p_role_end_date => l_team_active.end_date_active,
2106 -- x_user_role_start_date => l_mem_role_start_date,
2107 -- x_user_role_end_date => l_mem_role_end_date);
2108
2109 /* Team as well as group are active */
2110 OPEN c_role_exists(l_team_role_name, g_team_orig_system, p_team_id);
2111 FETCH c_role_exists into l_role_exists;
2112 IF (c_role_exists%FOUND) THEN
2113 /* Team - role record exists */
2114 l_user_name := g_grp_orig_system||':'||to_char(p_group_id);
2115 CLOSE c_role_exists;
2116 OPEN c_role_exists(l_user_name, g_grp_orig_system, p_group_id);
2117 FETCH c_role_exists into l_role_exists;
2118 IF (c_role_exists%FOUND) THEN
2119
2120 /* Group - role record exists */
2121 Wf_local_synch.propagate_user_role(
2122 p_user_orig_system => g_grp_orig_system,
2123 p_user_orig_system_id => p_group_id,
2124 p_role_orig_system => g_team_orig_system,
2125 p_role_orig_system_id => p_team_id,
2126 p_start_date => l_mem_role_start_date,
2127 p_expiration_date => l_mem_role_end_date,
2128 p_overwrite => TRUE);
2129 END IF;
2130 END IF;
2131 CLOSE c_role_exists;
2132 END IF;
2133 CLOSE c_grp_active;
2134 END IF;
2135 END IF;
2136
2137 CLOSE c_team_active;
2138
2139 IF p_commit is not NULL and fnd_api.to_boolean(p_commit) THEN
2140 COMMIT WORK;
2141 END IF;
2142
2143 EXCEPTION
2144 WHEN OTHERS THEN
2145 /* Since we don't care about
2146 the errors/exceptions in WF API, we are just catching when OTHERS */
2147 -- DBMS_OUTPUT.put_line (' ========================================== ');
2148 -- DBMS_OUTPUT.put_line (' =========== Raised Others in Create Resource Team Member Pvt ========= ');
2149 -- DBMS_OUTPUT.put_line (SQLCODE || SQLERRM);
2150
2151 IF c_role_exists%ISOPEN THEN
2152 CLOSE c_role_exists;
2153 END IF;
2154
2155 IF c_team_active%ISOPEN THEN
2156 CLOSE c_team_active;
2157 END IF;
2158
2159 IF c_res_active%ISOPEN THEN
2160 CLOSE c_res_active;
2161 END IF;
2162
2163 IF c_grp_active%ISOPEN THEN
2164 CLOSE c_res_active;
2165 END IF;
2166
2167 ROLLBACK TO wf_int_cr_res_team_mbr;
2168 x_return_status := fnd_api.g_ret_sts_unexp_error;
2169 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2170 END;
2171
2172
2173 PROCEDURE delete_resource_team_members
2174 (P_API_VERSION IN NUMBER,
2175 P_INIT_MSG_LIST IN VARCHAR2,
2176 P_COMMIT IN VARCHAR2,
2177 P_RESOURCE_ID IN NUMBER,
2178 P_GROUP_ID IN NUMBER,
2179 P_TEAM_ID IN NUMBER,
2180 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2181 X_MSG_COUNT OUT NOCOPY NUMBER,
2182 X_MSG_DATA OUT NOCOPY VARCHAR2
2183 ) IS
2184 l_api_version CONSTANT NUMBER := 1.0;
2185 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_RESOURCE_TEAM_MEMBERS';
2186 l_team_role_name wf_local_roles.name%TYPE := g_team_orig_system||':'||to_char(p_team_id);
2187
2188 l_user_name wf_local_roles.name%TYPE;
2189 l_orig_system wf_local_roles.orig_system%TYPE;
2190 l_orig_system_id wf_local_roles.orig_system_id%TYPE;
2191 BEGIN
2192 SAVEPOINT wf_int_del_res_team_mbr;
2193 x_return_status := fnd_api.g_ret_sts_success;
2194
2195 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name,
2196 g_pkg_name) THEN
2197 RAISE fnd_api.g_exc_unexpected_error;
2198 END IF;
2199 IF p_init_msg_list is not NULL and fnd_api.to_boolean(p_init_msg_list) THEN
2200 fnd_msg_pub.initialize;
2201 END IF;
2202
2203 IF (P_RESOURCE_ID is not null) THEN
2204 jtf_rs_wf_integration_pub.get_wf_role(P_RESOURCE_ID => P_RESOURCE_ID,
2205 X_ROLE_NAME => l_user_name,
2206 X_ORIG_SYSTEM => l_orig_system,
2207 X_ORIG_SYSTEM_ID => l_orig_system_id);
2208
2209 IF l_user_name is not null THEN
2210
2211 /* Changed the code to call Wf_local_synch instead of Wf_Directory
2212 Fix for bug # 2671368 */
2213
2214 Wf_local_synch.propagate_user_role(
2215 p_user_orig_system => l_orig_system,
2216 p_user_orig_system_id => l_orig_system_id,
2217 p_role_orig_system => g_team_orig_system,
2218 p_role_orig_system_id => p_team_id,
2219 -- p_start_date => sysdate,
2220 p_expiration_date => sysdate-1);
2221 END IF;
2222 ELSIF (P_GROUP_ID is not null) THEN
2223 l_user_name := g_grp_orig_system||':'||to_char(p_group_id);
2224
2225 Wf_local_synch.propagate_user_role(
2226 p_user_orig_system => g_grp_orig_system,
2227 p_user_orig_system_id => p_group_id,
2228 p_role_orig_system => g_team_orig_system,
2229 p_role_orig_system_id => p_team_id,
2230 -- p_start_date => sysdate,
2231 p_expiration_date => sysdate-1);
2232 END IF;
2233
2234
2235 IF p_commit is not NULL and fnd_api.to_boolean(p_commit) THEN
2236 COMMIT WORK;
2237 END IF;
2238
2239 EXCEPTION
2240 WHEN OTHERS THEN
2241 /* Since we don't care about
2242 the errors/exceptions in WF API, we are just catching when OTHERS */
2243 -- DBMS_OUTPUT.put_line (' ========================================== ');
2244 -- DBMS_OUTPUT.put_line (' =========== Raised Others in Delete Resource Team Member Pvt ========= ');
2245 -- DBMS_OUTPUT.put_line (SQLCODE || SQLERRM);
2246
2247 ROLLBACK TO wf_int_del_res_team_mbr;
2248 x_return_status := fnd_api.g_ret_sts_unexp_error;
2249 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2250 END;
2251
2252 END jtf_rs_wf_integration_pub;
[Home] [Help]