1 PACKAGE BODY jtf_rs_conc_wf_pub AS
2 /*$Header: jtfrsbwb.pls 120.4 2005/07/27 04:43:30 repuri noship $ */
3
4 /* Package variables. */
5
6 G_PKG_NAME VARCHAR2(30) := 'JTF_RS_CONC_WF_PUB';
7
8 PROCEDURE synchronize_wf_roles
9 (ERRBUF OUT NOCOPY VARCHAR2,
10 RETCODE OUT NOCOPY VARCHAR2,
11 P_SYNC_COMP IN VARCHAR2
12 )
13
14 IS
15
16 l_grp_orig_system VARCHAR2(10) := 'JRES_GRP';
17 l_ind_orig_system VARCHAR2(10) := 'JRES_IND';
18 l_team_orig_system VARCHAR2(10) := 'JRES_TEAM';
19 l_hz_orig_system VARCHAR2(10) := 'HZ_PARTY';
20
21 l_sysdate DATE := TRUNC (SYSDATE);
22 m_sysdate DATE := TRUNC (SYSDATE-1);
23 l_inactive VARCHAR2(10) := 'INACTIVE';
24 l_active VARCHAR2(10) := 'ACTIVE';
25 l_no_email VARCHAR2(15) := '*NOEMAIL1234*';
26 l_false BOOLEAN := FALSE;
27
28 error_name VARCHAR2(30);
29 error_message VARCHAR2(2000);
30 error_stack VARCHAR2(32000);
31
32 l_list WF_PARAMETER_LIST_T;
33 l_fnd_date DATE := to_date ('31-12-4712', 'DD-MM-RRRR');
34
35 PROCEDURE call_exception IS
36 error_name VARCHAR2(30);
37 error_message VARCHAR2(2000);
38 error_stack VARCHAR2(32000);
39 BEGIN
40 wf_core.get_error(error_name, error_message, error_stack);
41 fnd_file.put_line (fnd_file.log,error_message);
42 fnd_file.new_line (fnd_file.log,1);
43 fnd_file.put_line (fnd_file.log,error_stack);
44 fnd_file.new_line (fnd_file.log,1);
45 wf_core.clear;
46 END call_exception;
47
48 PROCEDURE synchronize_teams_wf AS
49
50 -- Select all inactive records from resource teams table whose
51 -- corresponding (Self and Member) records in the workflow local
52 -- user roles table (wf_local_user_roles) are still active.
53 -- These workflow local user role records have to be inactivated.
54
55 CURSOR c_team_wf_ur_del IS
56 SELECT team.team_id, team.start_date_active, team.end_date_active
57 FROM jtf_rs_teams_b team, wf_local_user_roles wlur
58 WHERE NVL(TRUNC(team.end_date_active),l_sysdate) < l_sysdate
59 AND wlur.role_orig_system_id = team.team_id
60 AND wlur.role_orig_system = l_team_orig_system
61 AND wlur.role_name = l_team_orig_system||':'||to_char(team.team_id)
62 AND NVL(TRUNC(wlur.expiration_date),l_sysdate) >= l_sysdate;
63
64 -- Select all inactive records from resource teams table
65 -- whose corresponding records in the workflow local roles
66 -- table (wf_local_roles) are still active.
67 -- These workflow local role records have to be inactivated.
68
69 CURSOR c_team_wf_del IS
70 SELECT team.team_id, team.team_name, team.email_address
71 ,team.start_date_active, team.end_date_active
72 FROM jtf_rs_teams_vl team, wf_local_roles wlr
73 WHERE NVL(TRUNC(team.end_date_active),l_sysdate) < l_sysdate
74 AND wlr.orig_system_id = team.team_id
75 AND wlr.orig_system = l_team_orig_system
76 AND wlr.name = l_team_orig_system||':'||to_char(team.team_id)
77 AND (wlr.status = l_active
78 OR NVL(TRUNC(wlr.expiration_date),l_sysdate) >= l_sysdate);
79
80 -- Select all active team records from resource teams table where one
81 -- of the matching columns to the corresponding records in workflow
82 -- local roles table (wf_local_roles) has been modified (not in sync).
83 -- These workflow role records have to be updated with new values.
84
85 CURSOR c_team_wf_upd IS
86 SELECT team.team_id, team.team_name, team.email_address
87 ,team.end_date_active, team.start_date_active
88 ,wlr.start_date wlr_start_date, wlr.expiration_date wlr_exp_date
89 FROM jtf_rs_teams_vl team, wf_local_roles wlr
90 WHERE NVL(TRUNC(team.end_date_active),l_sysdate) >= l_sysdate
91 AND wlr.orig_system_id = team.team_id
92 AND wlr.orig_system = l_team_orig_system
93 AND wlr.name = l_team_orig_system||':'||to_char(team.team_id)
94 AND (wlr.display_name <> team.team_name OR
95 NVL(wlr.email_address, l_no_email)
96 <> NVL(team.email_address, l_no_email) OR
97 wlr.start_date IS NULL OR wlr.start_date <> team.start_date_active OR
98 (wlr.expiration_date IS NULL AND team.end_date_active IS NOT NULL) OR
99 (wlr.expiration_date IS NOT NULL AND team.end_date_active IS NULL) OR
100 wlr.expiration_date <> team.end_date_active);
101
102 -- Select all active team records from resource teams table where one of
103 -- the date columns of the corresponding records (Self or Member Records) in workflow
104 -- local user roles table (wf_local_user_roles) has been modified (not in sync).
105 -- These workflow user role records have to be updated with new values.
106
107 CURSOR c_team_wf_ur_upd IS
108 SELECT team.team_id, team.start_date_active, team.end_date_active
109 FROM jtf_rs_teams_b team, wf_local_user_roles wlur
110 WHERE NVL(TRUNC(team.end_date_active),l_sysdate) >= l_sysdate
111 AND wlur.role_orig_system_id = team.team_id
112 AND wlur.role_orig_system = l_team_orig_system
113 AND wlur.role_name = l_team_orig_system||':'||to_char(team.team_id)
114 AND (wlur.start_date IS NULL OR wlur.start_date <> team.start_date_active OR
115 (wlur.expiration_date IS NULL AND team.end_date_active IS NOT NULL) OR
116 (wlur.expiration_date IS NOT NULL AND team.end_date_active IS NULL) OR
117 wlur.expiration_date <> team.end_date_active);
118
119 -- Select all the team records from the resource teams table
120 -- that are still not defined in workflow roles table (wf_local_roles).
121 -- A new record to be created in Workflow roles table, for each team record.
122
123 CURSOR c_team_wf_crt IS
124 SELECT team.team_id, team.team_name, team.email_address
125 ,team.end_date_active, team.start_date_active
126 FROM jtf_rs_teams_vl team
127 WHERE NVL(TRUNC(team.end_date_active),l_sysdate) >= l_sysdate
128 AND NOT EXISTS (SELECT 1 FROM wf_local_roles wlr
129 WHERE wlr.orig_system_id = team.team_id
130 AND wlr.orig_system = l_team_orig_system
131 AND wlr.name = l_team_orig_system||':'||to_char(team.team_id));
132
133 log_msg_hdr4 fnd_new_messages.message_text%type := NULL;
134 log_message12 fnd_new_messages.message_text%type := NULL;
135 log_message13 fnd_new_messages.message_text%type := NULL;
136 log_message14 fnd_new_messages.message_text%type := NULL;
137 log_message36 fnd_new_messages.message_text%type := NULL;
138 log_message37 fnd_new_messages.message_text%type := NULL;
139
140 BEGIN
141
142 log_msg_hdr4 := fnd_message.get_string ('JTF','JTF_RS_WF_ROLE_SYNC_LOG_HDR4');
143 log_message12 := fnd_message.get_string ('JTF','JTF_RS_WF_ROLE_SYNC_LOG_MSG12');
144 log_message13 := fnd_message.get_string ('JTF','JTF_RS_WF_ROLE_SYNC_LOG_MSG13');
145 log_message14 := fnd_message.get_string ('JTF','JTF_RS_WF_ROLE_SYNC_LOG_MSG14');
146 log_message36 := fnd_message.get_string ('JTF','JTF_RS_WF_ROLE_SYNC_LOG_MSG36');
147 log_message37 := fnd_message.get_string ('JTF','JTF_RS_WF_ROLE_SYNC_LOG_MSG37');
148
149 fnd_file.new_line (fnd_file.log,1);
150 fnd_file.put_line (fnd_file.log,log_msg_hdr4);
151 --fnd_file.put_line (fnd_file.log,'Beginning of Resource Workflow Synchronization for Resource Teams');
152 fnd_file.put_line (fnd_file.log,'-----------------------------------------------------------------');
153 fnd_file.new_line (fnd_file.log,1);
154
155 wf_core.clear;
156
157 -- Inactivate Workflow User Roles (Self and Member) for corresponding Inactive Resource Teams
158 FOR i IN c_team_wf_ur_del LOOP
159 BEGIN
160 Wf_local_synch.propagate_user_role(
161 p_user_orig_system => l_team_orig_system,
162 p_user_orig_system_id => i.team_id,
163 p_role_orig_system => l_team_orig_system,
164 p_role_orig_system_id => i.team_id,
165 p_raiseerrors => TRUE,
166 p_start_date => i.start_date_active,
167 p_expiration_date => i.end_date_active,
168 p_overwrite => TRUE);
169
170 EXCEPTION
171 WHEN OTHERS THEN
172 wf_core.get_error(error_name, error_message, error_stack);
173 fnd_file.put_line (fnd_file.log,error_message);
174 fnd_file.new_line (fnd_file.log,1);
175 fnd_file.put_line (fnd_file.log,error_stack);
176 fnd_file.new_line (fnd_file.log,1);
177 wf_core.clear;
178 END;
179 END LOOP;
180
181 fnd_file.put_line (fnd_file.log,log_message36);
182 --fnd_file.put_line (fnd_file.log,'Successfully inactivated self and member records in Workflow User Roles table,
183 --for all the corresponding records in Resource Teams table that have been inactivated');
184 fnd_file.new_line (fnd_file.log,1);
185
186 -- Inactivate Workflow Roles for corresponding Inactive Resource Teams
187 FOR i IN c_team_wf_del LOOP
188 BEGIN
189 JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('USER_NAME',l_team_orig_system||':'||to_char(i.team_id),l_list);
190 JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('DISPLAYNAME',i.team_name,l_list);
191 JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('MAIL',i.email_address,l_list);
192 JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('ORCLISENABLED',l_inactive,l_list);
193 JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('WFSYNCH_OVERWRITE','TRUE',l_list);
194 JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('RAISEERRORS','TRUE',l_list);
195
196 Wf_local_synch.propagate_role(
197 p_orig_system => l_team_orig_system,
198 p_orig_system_id => i.team_id,
199 p_attributes => l_list,
200 p_start_date => i.start_date_active,
201 p_expiration_date => i.end_date_active);
202
203 l_list.DELETE;
204
205 EXCEPTION
206 WHEN OTHERS THEN
207 l_list.DELETE;
208 wf_core.get_error(error_name, error_message, error_stack);
209 fnd_file.put_line (fnd_file.log,error_message);
210 fnd_file.new_line (fnd_file.log,1);
211 fnd_file.put_line (fnd_file.log,error_stack);
212 fnd_file.new_line (fnd_file.log,1);
213 wf_core.clear;
214 END;
215 END LOOP;
216
217 fnd_file.put_line (fnd_file.log,log_message12);
218 --fnd_file.put_line (fnd_file.log,'Successfully inactivated records in Workflow Roles table,
219 --for all the records in Resource Teams table that have been inactivated');
220 fnd_file.new_line (fnd_file.log,1);
221
222 -- Update Workflow Roles for corresponding Updated and Active Resource Teams
223 FOR i IN c_team_wf_upd LOOP
224 BEGIN
225 JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('USER_NAME',l_team_orig_system||':'||to_char(i.team_id),l_list);
226 JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('DISPLAYNAME',i.team_name,l_list);
227 JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('MAIL',i.email_address,l_list);
228 JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('WFSYNCH_OVERWRITE','TRUE',l_list);
229 JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('RAISEERRORS','TRUE',l_list);
230
231 -- Passing the parameter for 'Status' as 'ACTIVE' always, for Update.
232 -- The status will be set to 'INACTIVE' if dates are inactive by WF APIs.
233 JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('ORCLISENABLED',l_active,l_list);
234
235 Wf_local_synch.propagate_role(
236 p_orig_system => l_team_orig_system,
237 p_orig_system_id => i.team_id,
238 p_attributes => l_list,
239 p_start_date => i.start_date_active,
240 p_expiration_date => i.end_date_active);
241
242 l_list.DELETE;
243
244 EXCEPTION
245 WHEN OTHERS THEN
246 l_list.DELETE;
247 wf_core.get_error(error_name, error_message, error_stack);
248 fnd_file.put_line (fnd_file.log,error_message);
249 fnd_file.new_line (fnd_file.log,1);
250 fnd_file.put_line (fnd_file.log,error_stack);
251 fnd_file.new_line (fnd_file.log,1);
252 wf_core.clear;
253 END;
254
255 IF (i.wlr_start_date IS NULL OR
256 i.start_date_active <> i.wlr_start_date OR
257 (i.end_date_active IS NULL AND i.wlr_exp_date IS NOT NULL) OR
258 (i.end_date_active IS NOT NULL AND i.wlr_exp_date IS NULL) OR
259 i.end_date_active <> i.wlr_exp_date) THEN
260
261 BEGIN
262 Wf_local_synch.propagate_user_role(
263 p_user_orig_system => l_team_orig_system,
264 p_user_orig_system_id => i.team_id,
265 p_role_orig_system => l_team_orig_system,
266 p_role_orig_system_id => i.team_id,
267 p_raiseerrors => TRUE,
268 p_start_date => i.start_date_active,
269 p_expiration_date => i.end_date_active,
270 p_overwrite => TRUE);
271
272 EXCEPTION
273 WHEN OTHERS THEN
274 wf_core.get_error(error_name, error_message, error_stack);
275 fnd_file.put_line (fnd_file.log,error_message);
276 fnd_file.new_line (fnd_file.log,1);
277 fnd_file.put_line (fnd_file.log,error_stack);
278 fnd_file.new_line (fnd_file.log,1);
279 wf_core.clear;
280 END;
281 END IF;
282
283 END LOOP;
284
285 fnd_file.put_line (fnd_file.log,log_message13);
286 --fnd_file.put_line (fnd_file.log,'Successfully updated records in Workflow Roles and User Roles tables,
287 --whose corresponding records in Resource Teams table have been updated');
288 fnd_file.new_line (fnd_file.log,1);
289
290 -- Update Workflow User Roles (Self and Member Records) for corresponding Updated/Active Resource Teams
291 FOR i IN c_team_wf_ur_upd LOOP
292 BEGIN
293 Wf_local_synch.propagate_user_role(
294 p_user_orig_system => l_team_orig_system,
295 p_user_orig_system_id => i.team_id,
296 p_role_orig_system => l_team_orig_system,
297 p_role_orig_system_id => i.team_id,
298 p_raiseerrors => TRUE,
299 p_start_date => i.start_date_active,
300 p_expiration_date => i.end_date_active,
301 p_overwrite => TRUE);
302
303 EXCEPTION
304 WHEN OTHERS THEN
305 wf_core.get_error(error_name, error_message, error_stack);
306 fnd_file.put_line (fnd_file.log,error_message);
307 fnd_file.new_line (fnd_file.log,1);
308 fnd_file.put_line (fnd_file.log,error_stack);
309 fnd_file.new_line (fnd_file.log,1);
310 wf_core.clear;
311 END;
312 END LOOP;
313
314 fnd_file.put_line (fnd_file.log,log_message37);
315 --fnd_file.put_line (fnd_file.log,'Successfully updated dates for records in Workflow User Roles table,
316 --whose corresponding records in Resource Teams table have different dates');
317 fnd_file.new_line (fnd_file.log,1);
318
319 -- Create Workflow Roles for Active Resource Teams, if they don't exist
320 FOR i IN c_team_wf_crt LOOP
321 BEGIN
322 JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('USER_NAME',l_team_orig_system||':'||to_char(i.team_id),l_list);
323 JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('DISPLAYNAME',i.team_name,l_list);
324 JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('MAIL',i.email_address,l_list);
325 JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('RAISEERRORS','TRUE',l_list);
326 JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('ORCLISENABLED',l_active,l_list);
327
328 Wf_local_synch.propagate_role(
332 p_start_date => i.start_date_active,
329 p_orig_system => l_team_orig_system,
330 p_orig_system_id => i.team_id,
331 p_attributes => l_list,
333 p_expiration_date => i.end_date_active);
334
335 l_list.DELETE;
336
337 EXCEPTION
338 WHEN OTHERS THEN
339 l_list.DELETE;
340 wf_core.get_error(error_name, error_message, error_stack);
341 fnd_file.put_line (fnd_file.log,error_message);
342 fnd_file.new_line (fnd_file.log,1);
343 fnd_file.put_line (fnd_file.log,error_stack);
344 fnd_file.new_line (fnd_file.log,1);
345 wf_core.clear;
346 END;
347
348 -- Create Self-Record in wf_local_user_roles for the above record
349 BEGIN
350 Wf_local_synch.propagate_user_role(
351 p_user_orig_system => l_team_orig_system,
352 p_user_orig_system_id => i.team_id,
353 p_role_orig_system => l_team_orig_system,
354 p_role_orig_system_id => i.team_id,
355 p_raiseerrors => TRUE,
356 p_start_date => i.start_date_active,
357 p_expiration_date => i.end_date_active);
358
359 EXCEPTION
360 WHEN OTHERS THEN
361 wf_core.get_error(error_name, error_message, error_stack);
362 fnd_file.put_line (fnd_file.log,error_message);
363 fnd_file.new_line (fnd_file.log,1);
364 fnd_file.put_line (fnd_file.log,error_stack);
365 fnd_file.new_line (fnd_file.log,1);
366 wf_core.clear;
367 END;
368 END LOOP;
369
370 fnd_file.put_line (fnd_file.log,log_message14);
371 --fnd_file.put_line (fnd_file.log,'Successfully created records in Workflow Roles table, for all the records in
372 --Resource Teams table that dont exist as roles. Corresponding self records also created in Workflow User Roles');
373 fnd_file.new_line (fnd_file.log,1);
374
375 EXCEPTION
376 WHEN OTHERS THEN
377 wf_core.get_error(error_name, error_message, error_stack);
378 fnd_file.put_line (fnd_file.log,error_message);
379 fnd_file.new_line (fnd_file.log,1);
380 fnd_file.put_line (fnd_file.log,error_stack);
381 fnd_file.new_line (fnd_file.log,1);
382 wf_core.clear;
383
384 END synchronize_teams_wf;
385
386
387 PROCEDURE synchronize_groups_wf AS
388
389 -- Select all inactive records from resource groups table
390 -- whose corresponding (self and member) records in the workflow
391 -- local user roles table (wf_local_user_roles) are still active.
392 -- These workflow local user role records have to be inactivated.
393
394 CURSOR c_grp_wf_ur_del IS
395 SELECT grp.group_id, grp.start_date_active, grp.end_date_active
396 FROM jtf_rs_groups_b grp, wf_local_user_roles wlur
397 WHERE NVL(TRUNC(grp.end_date_active),l_sysdate) < l_sysdate
398 AND ((wlur.role_orig_system_id = grp.group_id
399 AND wlur.role_orig_system = l_grp_orig_system
400 AND wlur.role_name = l_grp_orig_system||':'||to_char(grp.group_id))
401 OR
402 (wlur.user_orig_system_id = grp.group_id
403 AND wlur.user_orig_system = l_grp_orig_system
404 AND wlur.user_name = l_grp_orig_system||':'||to_char(grp.group_id)))
405 AND NVL(TRUNC(wlur.expiration_date),l_sysdate) >= l_sysdate;
406
407 -- Select all inactive records from resource groups table
408 -- whose corresponding records in the workflow local roles
409 -- table (wf_local_roles) are still active.
410 -- These workflow local role records have to be inactivated.
411
412 CURSOR c_grp_wf_del IS
413 -- Hints provided based on Perf. Team's recommendations (Jaikumar Bathija).
414 SELECT /*+ use_hash(grp.t) use_hash(grp.b) use_hash(wlr) parallel(grp) parallel(wlr) */
415 grp.group_id, grp.group_name, grp.email_address
416 ,grp.start_date_active, grp.end_date_active
417 FROM jtf_rs_groups_vl grp, wf_local_roles wlr
418 WHERE NVL(TRUNC(grp.end_date_active),l_sysdate) < l_sysdate
419 AND wlr.orig_system_id = grp.group_id
420 AND wlr.orig_system = l_grp_orig_system
421 AND wlr.name = l_grp_orig_system||':'||to_char(grp.group_id)
422 AND (wlr.status = l_active
423 OR NVL(TRUNC(wlr.expiration_date),l_sysdate) >= l_sysdate);
424
425 -- Select all active group records from resource groups table
426 -- where one of the matching columns to the corresponding records
427 -- in workflow table (wf_local_users) have been modified (not in sync).
428 -- These workflow records have to be updated with new values.
429
430 CURSOR c_grp_wf_upd IS
431 -- Hints provided based on Perf. Team's recommendations (Jaikumar Bathija).
432 SELECT /*+ use_hash(grp.t) use_hash(grp.b) use_hash(wlr) parallel(grp) parallel(wlr) */
433 grp.group_id, grp.group_name, grp.email_address
434 ,grp.end_date_active, grp.start_date_active
435 ,wlr.start_date wlr_start_date, wlr.expiration_date wlr_exp_date
436 FROM jtf_rs_groups_vl grp, wf_local_roles wlr
437 WHERE NVL(TRUNC(grp.end_date_active),l_sysdate) >= l_sysdate
438 AND wlr.orig_system_id = grp.group_id
439 AND wlr.orig_system = l_grp_orig_system
440 AND wlr.name = l_grp_orig_system||':'||to_char(grp.group_id)
444 (wlr.start_date IS NULL OR wlr.start_date <> grp.start_date_active) OR
441 AND (wlr.display_name <> grp.group_name OR
442 NVL(wlr.email_address, l_no_email)
443 <> NVL(grp.email_address, l_no_email) OR
445 (wlr.expiration_date is null AND grp.end_date_active is not null) OR
446 (wlr.expiration_date is not null AND grp.end_date_active is null) OR
447 wlr.expiration_date <> grp.end_date_active);
448
449 -- Select all active group records from resource groups table where one of
450 -- the date columns of the corresponding (self and member) records in workflow
451 -- local user roles table (wf_local_user_roles) has been modified (not in sync).
452 -- These workflow user role records have to be updated with new values.
453
454 CURSOR c_grp_wf_ur_upd IS
455 SELECT grp.group_id, grp.start_date_active, grp.end_date_active
456 FROM jtf_rs_groups_b grp, wf_local_user_roles wlur
457 WHERE NVL(TRUNC(grp.end_date_active),l_sysdate) >= l_sysdate
458 AND ((wlur.role_orig_system_id = grp.group_id
459 AND wlur.role_orig_system = l_grp_orig_system
460 AND wlur.role_name = l_grp_orig_system||':'||to_char(grp.group_id))
461 OR
462 (wlur.user_orig_system_id = grp.group_id
463 AND wlur.user_orig_system = l_grp_orig_system
464 AND wlur.user_name = l_grp_orig_system||':'||to_char(grp.group_id)))
465 AND (wlur.start_date IS NULL OR wlur.start_date <> grp.start_date_active OR
466 (wlur.expiration_date IS NULL AND grp.end_date_active IS NOT NULL) OR
467 (wlur.expiration_date IS NOT NULL AND grp.end_date_active IS NULL) OR
468 wlur.expiration_date <> grp.end_date_active);
469
470 -- Select all the group records from the resource groups table
471 -- that are still not defined in workflow roles table (wf_local_roles).
472 -- A new record to be created in Workflow roles table, for each group record.
473
474 CURSOR c_grp_wf_crt IS
475 -- Hints provided based on Perf. Team's recommendations (Jaikumar Bathija).
476 SELECT /*+ use_hash(grp.t) use_hash(grp.b) parallel(grp) */
477 grp.group_id, grp.group_name, grp.email_address
478 ,grp.end_date_active, grp.start_date_active
479 FROM jtf_rs_groups_vl grp
480 WHERE NVL(TRUNC(grp.end_date_active),l_sysdate) >= l_sysdate
481 AND NOT EXISTS (SELECT 1 FROM wf_local_roles wlr
482 WHERE wlr.orig_system = l_grp_orig_system
483 AND wlr.orig_system_id = grp.group_id
484 AND wlr.name = l_grp_orig_system||':'||to_char(group_id));
485
486 log_msg_hdr1 fnd_new_messages.message_text%type := NULL;
487 log_message1 fnd_new_messages.message_text%type := NULL;
488 log_message2 fnd_new_messages.message_text%type := NULL;
489 log_message3 fnd_new_messages.message_text%type := NULL;
490 log_message38 fnd_new_messages.message_text%type := NULL;
491 log_message39 fnd_new_messages.message_text%type := NULL;
492
493 BEGIN
494
495 log_msg_hdr1 := fnd_message.get_string ('JTF','JTF_RS_WF_ROLE_SYNC_LOG_HDR1');
496 log_message1 := fnd_message.get_string ('JTF','JTF_RS_WF_ROLE_SYNC_LOG_MSG1');
497 log_message2 := fnd_message.get_string ('JTF','JTF_RS_WF_ROLE_SYNC_LOG_MSG2');
498 log_message3 := fnd_message.get_string ('JTF','JTF_RS_WF_ROLE_SYNC_LOG_MSG3');
499 log_message38:= fnd_message.get_string ('JTF','JTF_RS_WF_ROLE_SYNC_LOG_MSG38');
500 log_message39:= fnd_message.get_string ('JTF','JTF_RS_WF_ROLE_SYNC_LOG_MSG39');
501
502 fnd_file.new_line (fnd_file.log,1);
503 fnd_file.put_line (fnd_file.log,log_msg_hdr1);
504 --fnd_file.put_line (fnd_file.log,'Beginning of Resource Workflow Synchronization for Resource Groups');
505 fnd_file.put_line (fnd_file.log,'------------------------------------------------------------------');
506 fnd_file.new_line (fnd_file.log,1);
507
508 wf_core.clear;
509
510 -- Inactivate Workflow User Roles (Self-Records) for corresponding Inactive Resource Groups
511 FOR i IN c_grp_wf_ur_del LOOP
512 BEGIN
513 Wf_local_synch.propagate_user_role(
514 p_user_orig_system => l_grp_orig_system,
515 p_user_orig_system_id => i.group_id,
516 p_role_orig_system => l_grp_orig_system,
517 p_role_orig_system_id => i.group_id,
518 p_raiseerrors => TRUE,
519 p_start_date => i.start_date_active,
520 p_expiration_date => i.end_date_active,
521 p_overwrite => TRUE);
522
523 EXCEPTION
524 WHEN OTHERS THEN
525 wf_core.get_error(error_name, error_message, error_stack);
526 fnd_file.put_line (fnd_file.log,error_message);
527 fnd_file.new_line (fnd_file.log,1);
528 fnd_file.put_line (fnd_file.log,error_stack);
529 fnd_file.new_line (fnd_file.log,1);
530 wf_core.clear;
531 END;
532 END LOOP;
533
534 fnd_file.put_line (fnd_file.log,log_message38);
535 --fnd_file.put_line (fnd_file.log,'Successfully inactivated records in Workflow User Roles table, for both self and
536 --group member records, whose corresponding records in Resource Groups table that have been inactivated');
537 fnd_file.new_line (fnd_file.log,1);
538
539 -- Inactivate Workflow Roles for corresponding Inactive Resource Groups
540 FOR i IN c_grp_wf_del LOOP
541 BEGIN
542 JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('USER_NAME',l_grp_orig_system||':'||to_char(i.group_id),l_list);
543 JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('DISPLAYNAME',i.group_name,l_list);
544 JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('MAIL',i.email_address,l_list);
545 JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('ORCLISENABLED',l_inactive,l_list);
546 JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('WFSYNCH_OVERWRITE','TRUE',l_list);
547 JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('RAISEERRORS','TRUE',l_list);
548
549 Wf_local_synch.propagate_role(
550 p_orig_system => l_grp_orig_system,
551 p_orig_system_id => i.group_id,
552 p_attributes => l_list,
553 p_start_date => i.start_date_active,
554 p_expiration_date => i.end_date_active);
555
556 l_list.DELETE;
557
558 EXCEPTION
559 WHEN OTHERS THEN
560 l_list.DELETE;
561 wf_core.get_error(error_name, error_message, error_stack);
562 fnd_file.put_line (fnd_file.log,error_message);
563 fnd_file.new_line (fnd_file.log,1);
564 fnd_file.put_line (fnd_file.log,error_stack);
565 fnd_file.new_line (fnd_file.log,1);
566 wf_core.clear;
567 END;
568 END LOOP;
569
570 fnd_file.put_line (fnd_file.log,log_message1);
571 --fnd_file.put_line (fnd_file.log,'Successfully inactivated records in Workflow Roles table,
572 --for all the records in Resource Groups table that have been inactivated');
573 fnd_file.new_line (fnd_file.log,1);
574
575 -- Update Workflow Roles for corresponding Updated and Active Resource Groups
576 FOR i IN c_grp_wf_upd LOOP
577 BEGIN
578 JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('USER_NAME',l_grp_orig_system||':'||to_char(i.group_id),l_list);
579 JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('DISPLAYNAME',i.group_name,l_list);
580 JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('MAIL',i.email_address,l_list);
581 JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('WFSYNCH_OVERWRITE','TRUE',l_list);
582 JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('RAISEERRORS','TRUE',l_list);
583
584 -- Passing the parameter for 'Status' as 'ACITVE' always, for Update.
585 -- The status will be set to 'INACTIVE' if dates are inactive by WF APIs.
586 JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('ORCLISENABLED',l_active,l_list);
587
588 Wf_local_synch.propagate_role(
589 p_orig_system => l_grp_orig_system,
590 p_orig_system_id => i.group_id,
591 p_attributes => l_list,
592 p_start_date => i.start_date_active,
593 p_expiration_date => i.end_date_active);
594
595 l_list.DELETE;
596
597 EXCEPTION
598 WHEN OTHERS THEN
599 l_list.DELETE;
600 wf_core.get_error(error_name, error_message, error_stack);
601 fnd_file.put_line (fnd_file.log,error_message);
602 fnd_file.new_line (fnd_file.log,1);
603 fnd_file.put_line (fnd_file.log,error_stack);
604 fnd_file.new_line (fnd_file.log,1);
605 wf_core.clear;
606 END;
607
608 IF (i.wlr_start_date IS NULL OR
609 i.start_date_active <> i.wlr_start_date OR
610 (i.end_date_active IS NULL AND i.wlr_exp_date IS NOT NULL) OR
611 (i.end_date_active IS NOT NULL AND i.wlr_exp_date IS NULL) OR
612 i.end_date_active <> i.wlr_exp_date) THEN
613
614 BEGIN
615 Wf_local_synch.propagate_user_role(
616 p_user_orig_system => l_grp_orig_system,
617 p_user_orig_system_id => i.group_id,
618 p_role_orig_system => l_grp_orig_system,
619 p_role_orig_system_id => i.group_id,
620 p_raiseerrors => TRUE,
621 p_start_date => i.start_date_active,
622 p_expiration_date => i.end_date_active,
623 p_overwrite => TRUE);
624
625 EXCEPTION
626 WHEN OTHERS THEN
627 wf_core.get_error(error_name, error_message, error_stack);
628 fnd_file.put_line (fnd_file.log,error_message);
629 fnd_file.new_line (fnd_file.log,1);
630 fnd_file.put_line (fnd_file.log,error_stack);
631 fnd_file.new_line (fnd_file.log,1);
632 wf_core.clear;
633 END;
634 END IF;
635
636 END LOOP;
637
638 fnd_file.put_line (fnd_file.log,log_message2);
639 --fnd_file.put_line (fnd_file.log,'Successfully updated records in Workflow Roles and User Roles table,
640 --whose corresponding records in Resource Groups table have been updated');
641 fnd_file.new_line (fnd_file.log,1);
642
643 -- Update Workflow User Roles (Self and Member Records) for correspoding Updated/Active Resource Groups
644 FOR i IN c_grp_wf_ur_upd LOOP
645 BEGIN
646 Wf_local_synch.propagate_user_role(
647 p_user_orig_system => l_grp_orig_system,
648 p_user_orig_system_id => i.group_id,
649 p_role_orig_system => l_grp_orig_system,
650 p_role_orig_system_id => i.group_id,
651 p_raiseerrors => TRUE,
652 p_start_date => i.start_date_active,
656 EXCEPTION
653 p_expiration_date => i.end_date_active,
654 p_overwrite => TRUE);
655
657 WHEN OTHERS THEN
658 wf_core.get_error(error_name, error_message, error_stack);
659 fnd_file.put_line (fnd_file.log,error_message);
660 fnd_file.new_line (fnd_file.log,1);
661 fnd_file.put_line (fnd_file.log,error_stack);
662 fnd_file.new_line (fnd_file.log,1);
663 wf_core.clear;
664 END;
665 END LOOP;
666
667 fnd_file.put_line (fnd_file.log,log_message39);
668 --fnd_file.put_line (fnd_file.log,'Successfully updated dates for all self and member records in Workflow
669 --User Roles table, whose corresponding records in Resource Groups table have been updated');
670 fnd_file.new_line (fnd_file.log,1);
671
672 -- Create Workflow Roles for Active Resource Groups, if they don't exist
673 FOR i IN c_grp_wf_crt LOOP
674 BEGIN
675 JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('USER_NAME',l_grp_orig_system||':'||to_char(i.group_id),l_list);
676 JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('DISPLAYNAME',i.group_name,l_list);
677 JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('MAIL',i.email_address,l_list);
678 JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('RAISEERRORS','TRUE',l_list);
679
680 Wf_local_synch.propagate_role(
681 p_orig_system => l_grp_orig_system,
682 p_orig_system_id => i.group_id,
683 p_attributes => l_list,
684 p_start_date => i.start_date_active,
685 p_expiration_date => i.end_date_active);
686
687 l_list.DELETE;
688
689 EXCEPTION
690 WHEN OTHERS THEN
691 l_list.DELETE;
692 wf_core.get_error(error_name, error_message, error_stack);
693 fnd_file.put_line (fnd_file.log,error_message);
694 fnd_file.new_line (fnd_file.log,1);
695 fnd_file.put_line (fnd_file.log,error_stack);
696 fnd_file.new_line (fnd_file.log,1);
697 wf_core.clear;
698 END;
699
700 -- Create Self-Record in wf_local_user_roles for the above record
701 BEGIN
702 Wf_local_synch.propagate_user_role(
703 p_user_orig_system => l_grp_orig_system,
704 p_user_orig_system_id => i.group_id,
705 p_role_orig_system => l_grp_orig_system,
706 p_role_orig_system_id => i.group_id,
707 p_raiseerrors => TRUE,
708 p_start_date => i.start_date_active,
709 p_expiration_date => i.end_date_active);
710 EXCEPTION
711 WHEN OTHERS THEN
712 wf_core.get_error(error_name, error_message, error_stack);
713 fnd_file.put_line (fnd_file.log,error_message);
714 fnd_file.new_line (fnd_file.log,1);
715 fnd_file.put_line (fnd_file.log,error_stack);
716 fnd_file.new_line (fnd_file.log,1);
717 wf_core.clear;
718 END;
719 END LOOP;
720
721 fnd_file.put_line (fnd_file.log,log_message3);
722 --fnd_file.put_line (fnd_file.log,'Successfully created records in Workflow Roles table, for all the records in
723 --Resource Groups table that dont exist as roles. Corresponding records also created in Workflow User Roles');
724 fnd_file.new_line (fnd_file.log,1);
725
726 EXCEPTION
727 WHEN OTHERS THEN
728 wf_core.get_error(error_name, error_message, error_stack);
729 fnd_file.put_line (fnd_file.log,error_message);
730 fnd_file.new_line (fnd_file.log,1);
731 fnd_file.put_line (fnd_file.log,error_stack);
732 fnd_file.new_line (fnd_file.log,1);
733 wf_core.clear;
734
735 END synchronize_groups_wf;
736
737
738 PROCEDURE synchronize_resources_wf AS
739
740 CURSOR c_res_details IS
741 SELECT resource_id, category, start_date_active, end_date_active
742 ,source_id, source_email, resource_name
743 FROM jtf_rs_resource_extns_vl;
744
745 CURSOR c_wf_ur_details (l_user_orig_system_id NUMBER, l_user_orig_system VARCHAR2, l_user_name VARCHAR2) IS
746 SELECT role_orig_system_id, role_orig_system, role_name
747 ,start_date, expiration_date
748 FROM wf_local_user_roles
749 WHERE user_orig_system_id = l_user_orig_system_id
750 AND user_orig_system = l_user_orig_system
751 AND user_name = l_user_name
752 AND NVL (expiration_date, l_sysdate) >= l_sysdate;
753
754 CURSOR c_wf_ur_mem_details (l_user_orig_system_id NUMBER, l_user_orig_system VARCHAR2, l_user_name VARCHAR2) IS
755 SELECT role_orig_system_id, role_orig_system, role_name
756 ,start_date, expiration_date
757 FROM wf_local_user_roles
758 WHERE user_orig_system_id = l_user_orig_system_id
759 AND user_orig_system = l_user_orig_system
760 AND user_name = l_user_name
761 AND role_orig_system IN ('JRES_IND','JRES_GRP','JRES_TEAM')
762 AND NVL (expiration_date, l_sysdate) >= l_sysdate;
763
764 CURSOR c_wf_role_details (l_orig_system_id NUMBER, l_orig_system VARCHAR2, l_name VARCHAR2) IS
765 SELECT display_name, email_address, start_date, expiration_date
766 FROM wf_local_roles
767 WHERE orig_system_id = l_orig_system_id
768 AND orig_system = l_orig_system
772 l_user_name wf_local_roles.name%TYPE;
769 AND name = l_name
770 AND NVL (expiration_date, l_sysdate) >= l_sysdate;
771
773 l_ind_user_name wf_local_roles.name%TYPE;
774 l_user_orig_system wf_local_roles.orig_system%TYPE;
775 l_user_orig_system_id wf_local_roles.orig_system_id%TYPE;
776
777 l_start_date DATE;
778 l_end_date DATE;
779
780 l_display_name wf_local_roles.display_name%TYPE;
781 l_email_address wf_local_roles.email_address%TYPE;
782 l_expiration_date wf_local_roles.expiration_date%TYPE;
783
784 log_msg_hdr2 fnd_new_messages.message_text%type := NULL;
785 log_message40 fnd_new_messages.message_text%type := NULL;
786
787 BEGIN
788
789 log_msg_hdr2 := fnd_message.get_string ('JTF','JTF_RS_WF_ROLE_SYNC_LOG_HDR2');
790 log_message40 := fnd_message.get_string ('JTF','JTF_RS_WF_ROLE_SYNC_LOG_MSG40');
791
792 fnd_file.put_line (fnd_file.log,log_msg_hdr2);
793 --fnd_file.put_line (fnd_file.log,'Beginning of Resource Workflow Synchronization for Resources');
794 fnd_file.put_line (fnd_file.log,'------------------------------------------------------------');
795 fnd_file.new_line (fnd_file.log,1);
796
797 FOR i IN c_res_details LOOP
798 l_ind_user_name := l_ind_orig_system ||':'|| TO_CHAR (i.resource_id);
799 IF (i.category IN ('EMPLOYEE','PARTY','PARTNER','SUPPLIER_CONTACT')) THEN
800 FOR j IN c_wf_ur_details (i.resource_id, l_ind_orig_system, l_ind_user_name) LOOP
801 -- Inactivate the wf user roles
802 BEGIN
803 Wf_local_synch.propagate_user_role(
804 p_user_orig_system => l_ind_orig_system,
805 p_user_orig_system_id => i.resource_id,
806 p_role_orig_system => l_ind_orig_system,
807 p_role_orig_system_id => i.resource_id,
808 p_raiseerrors => TRUE,
809 p_start_date => l_sysdate-2,
810 p_expiration_date => l_sysdate-1,
811 p_overwrite => TRUE);
812 EXCEPTION
813 WHEN OTHERS THEN
814 wf_core.get_error(error_name, error_message, error_stack);
815 fnd_file.put_line (fnd_file.log,error_message);
816 fnd_file.new_line (fnd_file.log,1);
817 fnd_file.put_line (fnd_file.log,error_stack);
818 fnd_file.new_line (fnd_file.log,1);
819 wf_core.clear;
820 END;
821 END LOOP;
822 -- Get all wf roles still defined as JRES_IND and Active.
823 FOR n in c_wf_role_details (i.resource_id, l_ind_orig_system, l_ind_user_name) LOOP
824 IF i.resource_name IS NOT NULL THEN
825 -- Inactivate wf roles with expiration date as sysdate-2
826 BEGIN
827 JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('USER_NAME',l_ind_orig_system||':'||to_char(i.resource_id),l_list);
828 --JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('DISPLAYNAME',i.resource_name,l_list);
829 --JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('MAIL',i.source_email,l_list);
830 JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('ORCLISENABLED',l_inactive,l_list);
831 JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('WFSYNCH_OVERWRITE','TRUE',l_list);
832 JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('RAISEERRORS','TRUE',l_list);
833
834 Wf_local_synch.propagate_role(
835 p_orig_system => l_ind_orig_system,
836 p_orig_system_id => i.resource_id,
837 p_attributes => l_list,
838 p_start_date => l_sysdate-2,
839 p_expiration_date => l_sysdate-1);
840 l_list.DELETE;
841 EXCEPTION
842 WHEN OTHERS THEN
843 l_list.DELETE;
844 wf_core.get_error(error_name, error_message, error_stack);
845 fnd_file.put_line (fnd_file.log,error_message);
846 fnd_file.new_line (fnd_file.log,1);
847 fnd_file.put_line (fnd_file.log,error_stack);
848 fnd_file.new_line (fnd_file.log,1);
849 wf_core.clear;
850 END;
851 END IF;
852 END LOOP;
853 -- Check if the Resources of category OTHER and TBH are inactive
854 ELSIF (i.category IN ('OTHER','TBH') AND NVL (i.end_date_active, l_sysdate) < l_sysdate) THEN
855 -- Inactivate corresponding wf user roles with resource dates
856 FOR k IN c_wf_ur_details (i.resource_id, l_ind_orig_system, l_ind_user_name) LOOP
857 BEGIN
858 Wf_local_synch.propagate_user_role(
859 p_user_orig_system => l_ind_orig_system,
860 p_user_orig_system_id => i.resource_id,
861 p_role_orig_system => l_ind_orig_system,
862 p_role_orig_system_id => i.resource_id,
863 p_raiseerrors => TRUE,
864 p_start_date => i.start_date_active,
865 p_expiration_date => i.end_date_active,
866 p_overwrite => TRUE);
867
868 EXCEPTION
869 WHEN OTHERS THEN
870 wf_core.get_error(error_name, error_message, error_stack);
871 fnd_file.put_line (fnd_file.log,error_message);
872 fnd_file.new_line (fnd_file.log,1);
873 fnd_file.put_line (fnd_file.log,error_stack);
874 fnd_file.new_line (fnd_file.log,1);
875 wf_core.clear;
876 END;
877 END LOOP;
878 -- Inactivate corresponding wf roles with resource dates
879 FOR p in c_wf_role_details (i.resource_id, l_ind_orig_system , l_ind_user_name) LOOP
880 IF i.resource_name IS NOT NULL THEN
881 BEGIN
882 JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('USER_NAME',l_ind_orig_system||':'||to_char(i.resource_id),l_list);
883 --JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('DISPLAYNAME',i.resource_name,l_list);
884 --JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('MAIL',i.source_email,l_list);
885 JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('ORCLISENABLED',l_inactive,l_list);
886 JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('WFSYNCH_OVERWRITE','TRUE',l_list);
887 JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('RAISEERRORS','TRUE',l_list);
888
889 Wf_local_synch.propagate_role(
890 p_orig_system => l_ind_orig_system,
891 p_orig_system_id => i.resource_id,
892 p_attributes => l_list,
893 p_start_date => i.start_date_active,
894 p_expiration_date => i.end_date_active);
895 l_list.DELETE;
896 EXCEPTION
897 WHEN OTHERS THEN
898 l_list.DELETE;
899 wf_core.get_error(error_name, error_message, error_stack);
900 fnd_file.put_line (fnd_file.log,error_message);
901 fnd_file.new_line (fnd_file.log,1);
902 fnd_file.put_line (fnd_file.log,error_stack);
903 fnd_file.new_line (fnd_file.log,1);
904 wf_core.clear;
905 END;
906 END IF;
907 END LOOP;
908 END IF;
909 --Check for active resource
910 IF (NVL (i.end_date_active, l_sysdate) >= l_sysdate) THEN
911 --Get wf role information for the resouce
912 jtf_rs_wf_integration_pub.get_wf_role (
913 p_resource_id => i.resource_id
914 ,x_role_name => l_user_name
915 ,x_orig_system => l_user_orig_system
916 ,x_orig_system_id => l_user_orig_system_id
917 );
918 -- Get corresponding active wf user roles
919 FOR c in c_wf_ur_mem_details (l_user_orig_system_id, l_user_orig_system, l_user_name) LOOP
920 --Check if any dates have been modified for any active resource of any category
921 IF (c.start_date IS NULL OR c.start_date <> i.start_date_active) OR
922 (c.expiration_date IS NULL AND i.end_date_active IS NOT NULL) OR
923 (c.expiration_date IS NOT NULL AND i.end_date_active IS NULL) OR
924 (c.expiration_date <> i.end_date_active) THEN
925 --Check for Self or Member record
926 IF (l_user_orig_system = c.role_orig_system AND l_user_orig_system_id = c.role_orig_system_id
927 AND l_user_name = c.role_name) THEN
928 --Its a self record. Update with Resource Dates
929 l_start_date := i.start_date_active;
930 l_end_date := i.end_date_active;
931 ELSE -- Its a member record. Update with the correct greatest and least dates.
932 l_start_date := greatest (i.start_date_active, NVL (c.start_date, i.start_date_active));
933 l_end_date := least (NVL (i.end_date_active, l_fnd_date), NVL (c.expiration_date, l_fnd_date));
934 --Update user roles with correct dates.
935 IF l_end_date = l_fnd_date THEN
936 l_end_date := NULL;
937 END IF;
938 BEGIN
939 Wf_local_synch.propagate_user_role(
940 p_user_orig_system => l_user_orig_system,
941 p_user_orig_system_id => l_user_orig_system_id,
942 p_role_orig_system => c.role_orig_system,
943 p_role_orig_system_id => c.role_orig_system_id,
944 p_raiseerrors => TRUE,
945 p_start_date => l_start_date,
946 p_expiration_date => l_end_date,
947 p_overwrite => TRUE);
948 EXCEPTION
949 WHEN OTHERS THEN
950 wf_core.get_error(error_name, error_message, error_stack);
951 fnd_file.put_line (fnd_file.log,error_message);
952 fnd_file.new_line (fnd_file.log,1);
953 fnd_file.put_line (fnd_file.log,error_stack);
954 fnd_file.new_line (fnd_file.log,1);
955 wf_core.clear;
956 END;
957 END IF;
958 END IF;
959 END LOOP;
960 END IF;
961 -- Active Resources of category OTHER and TBH
962 IF (i.category IN ('OTHER','TBH') AND NVL (i.end_date_active, l_sysdate) >= l_sysdate) THEN
963 --Get corresponding wf roles
964 OPEN c_wf_role_details (i.resource_id, l_ind_orig_system, l_ind_user_name);
965 FETCH c_wf_role_details INTO l_display_name, l_email_address, l_start_date, l_expiration_date;
966 IF c_wf_role_details%FOUND THEN
967 --Check for Updates
968 IF (l_display_name <> NVL (i.resource_name, l_display_name)
969 OR NVL (l_email_address, l_no_email) <> NVL (i.source_email, l_no_email)
970 OR (l_start_date IS NULL OR l_start_date <> i.start_date_active)
974 --Update wf roles for corresponding changes in resource details for OTHER and TBH.
971 OR (l_expiration_date IS NULL AND i.end_date_active IS NOT NULL)
972 OR (l_expiration_date IS NOT NULL AND i.end_date_active IS NULL)
973 OR l_expiration_date <> i.end_date_active) THEN
975 IF i.resource_name IS NOT NULL THEN
976 BEGIN
977 JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('USER_NAME',l_ind_orig_system||':'||to_char(i.resource_id),l_list);
978 JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('DISPLAYNAME',i.resource_name,l_list);
979 JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('MAIL',i.source_email,l_list);
980 JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('WFSYNCH_OVERWRITE','TRUE',l_list);
981 JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('RAISEERRORS','TRUE',l_list);
982 -- Passing the parameter for 'Status' as 'ACTIVE' always, for Update.
983 -- The status will be set to 'INACTIVE' if dates are inactive by WF APIs.
984 JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('ORCLISENABLED',l_active,l_list);
985
986 Wf_local_synch.propagate_role(
987 p_orig_system => l_ind_orig_system,
988 p_orig_system_id => i.resource_id,
989 p_attributes => l_list,
990 p_start_date => i.start_date_active,
991 p_expiration_date => i.end_date_active);
992 l_list.DELETE;
993 EXCEPTION
994 WHEN OTHERS THEN
995 IF c_wf_role_details%ISOPEN THEN
996 CLOSE c_wf_role_details;
997 END IF;
998 l_list.DELETE;
999 wf_core.get_error(error_name, error_message, error_stack);
1000 fnd_file.put_line (fnd_file.log,error_message);
1001 fnd_file.new_line (fnd_file.log,1);
1002 fnd_file.put_line (fnd_file.log,error_stack);
1003 fnd_file.new_line (fnd_file.log,1);
1004 wf_core.clear;
1005 END;
1006 END IF;
1007 END IF;
1008 ELSE
1009 --Create new wf roles
1010 IF i.resource_name IS NOT NULL THEN
1011 BEGIN
1012 JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('USER_NAME',l_ind_orig_system||':'||to_char(i.resource_id),l_list);
1013 JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('DISPLAYNAME',i.resource_name,l_list);
1014 JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('MAIL',i.source_email,l_list);
1015 JTF_RS_WF_INTEGRATION_PUB.AddParameterToList('RAISEERRORS','TRUE',l_list);
1016
1017 Wf_local_synch.propagate_role(
1018 p_orig_system => l_ind_orig_system,
1019 p_orig_system_id => i.resource_id,
1020 p_attributes => l_list,
1021 p_start_date => i.start_date_active,
1022 p_expiration_date => i.end_date_active);
1023 l_list.DELETE;
1024 EXCEPTION
1025 WHEN OTHERS THEN
1026 l_list.DELETE;
1027 wf_core.get_error(error_name, error_message, error_stack);
1028 fnd_file.put_line (fnd_file.log,error_message);
1029 fnd_file.new_line (fnd_file.log,1);
1030 fnd_file.put_line (fnd_file.log,error_stack);
1031 fnd_file.new_line (fnd_file.log,1);
1032 wf_core.clear;
1033 END;
1034 -- Create Self-Record in wf_local_user_roles for the above record
1035 BEGIN
1036 Wf_local_synch.propagate_user_role(
1037 p_user_orig_system => l_ind_orig_system,
1038 p_user_orig_system_id => i.resource_id,
1039 p_role_orig_system => l_ind_orig_system,
1040 p_role_orig_system_id => i.resource_id,
1041 p_raiseerrors => TRUE,
1042 p_start_date => i.start_date_active,
1043 p_expiration_date => i.end_date_active);
1044 EXCEPTION
1045 WHEN OTHERS THEN
1046 wf_core.get_error(error_name, error_message, error_stack);
1047 fnd_file.put_line (fnd_file.log,error_message);
1048 fnd_file.new_line (fnd_file.log,1);
1049 fnd_file.put_line (fnd_file.log,error_stack);
1050 fnd_file.new_line (fnd_file.log,1);
1051 wf_core.clear;
1052 END;
1053 END IF;
1054 END IF;
1055 CLOSE c_wf_role_details;
1056 END IF;
1057 END LOOP;
1058
1059 fnd_file.put_line (fnd_file.log,log_message40);
1060 --fnd_file.put_line (fnd_file.log,'Successfully synchronized Workflow Roles and User Role tables, for all its
1061 --corresponding records in Resources Table. This includes inactivation, updating and creation');
1062 fnd_file.new_line (fnd_file.log,1);
1063
1064 END synchronize_resources_wf;
1065
1066
1067 PROCEDURE synchronize_group_members_wf AS
1068
1069 -- Cursor to get all active wf user role records whose corresponding
1070 -- resource group member records have been deleted (delete_flag Y).
1071
1072 CURSOR c_wlur_grp_mem_del IS
1073 SELECT wlur.user_orig_system, wlur.user_orig_system_id, mem.group_id
1074 FROM wf_local_user_roles wlur, jtf_rs_group_members mem
1075 WHERE NVL (mem.delete_flag,'N') = 'Y'
1079 AND wlur.user_orig_system <> l_grp_orig_system
1076 AND wlur.role_orig_system = l_grp_orig_system
1077 AND wlur.role_orig_system_id = mem.group_id
1078 AND wlur.role_name = l_grp_orig_system ||':'|| mem.group_id
1080 AND NVL(wlur.expiration_date, l_sysdate) >= l_sysdate;
1081
1082 -- Cursor to select all active records from resource group members table
1083 -- (jtf_rs_group_members) that are still not defined in workflow
1084 -- user role table (wf_local_user_roles). This is for category OTHER and TBH.
1085 -- These records have to be created in workflow user roles table.
1086
1087 CURSOR c_grp_mem_wf_crt IS
1088 -- Hints provided based on Perf. Team's recommendations (Jaikumar Bathija).
1089 SELECT /*+ use_hash(ext) use_hash(mem) use_hash(grp) parallel(ext)
1090 parallel(mem) parallel(grp) */
1091 mem.resource_id, mem.group_id
1092 ,greatest (ext.start_date_active, grp.start_date_active) m_start_date_active
1093 ,least (NVL (grp.end_date_active, l_fnd_date) ,
1094 NVL (ext.end_date_active, l_fnd_date)) m_end_date_active
1095 FROM jtf_rs_resource_extns_vl ext, jtf_rs_group_members mem, jtf_rs_groups_b grp
1096 WHERE mem.resource_id = ext.resource_id
1097 AND mem.group_id = grp.group_id
1098 AND ext.category IN ('OTHER','TBH')
1099 AND ext.resource_name IS NOT NULL
1100 AND NVL (mem.delete_flag,'N') <> 'Y'
1101 AND NVL(TRUNC(ext.end_date_active),l_sysdate) >= l_sysdate
1102 AND NVL(TRUNC(grp.end_date_active),l_sysdate) >= l_sysdate
1103 AND NOT EXISTS (SELECT 1 FROM wf_local_user_roles wlur
1104 WHERE wlur.user_orig_system_id = mem.resource_id
1105 AND wlur.role_orig_system_id = mem.group_id
1106 AND wlur.user_orig_system = l_ind_orig_system
1107 AND wlur.role_orig_system = l_grp_orig_system);
1108
1109 -- Select all active records from resource group members table
1110 -- (jtf_rs_group_members) that are still not defined in workflow
1111 -- user role table (wf_local_user_roles). This is for category
1112 -- EMPLOYEE, PARTY, PARTNER and SUPPLIER_CONTACT
1113 -- These records have to be created in workflow user roles table.
1114
1115 CURSOR c_grp_mem_wf_epps_crt IS
1116 -- Hints provided based on Perf. Team's recommendations (Jaikumar Bathija).
1117 SELECT /*+ use_hash(ext) use_hash(mem) use_hash(grp) parallel(ext)
1118 parallel(mem) parallel(grp) */
1119 mem.resource_id, mem.group_id, ext.category, ext.resource_name
1120 ,greatest (ext.start_date_active, grp.start_date_active) m_start_date_active
1121 ,least (NVL (grp.end_date_active, l_fnd_date) ,
1122 NVL (ext.end_date_active, l_fnd_date)) m_end_date_active
1123 FROM jtf_rs_resource_extns_vl ext, jtf_rs_group_members mem, jtf_rs_groups_b grp
1124 WHERE mem.resource_id = ext.resource_id
1125 AND mem.group_id = grp.group_id
1126 AND ext.category IN ('EMPLOYEE','PARTY','PARTNER','SUPPLIER_CONTACT')
1127 AND NVL (mem.delete_flag,'N') <> 'Y'
1128 AND ext.resource_name IS NOT NULL
1129 AND NVL(TRUNC(ext.end_date_active),l_sysdate) >= l_sysdate
1130 AND NVL(TRUNC(grp.end_date_active),l_sysdate) >= l_sysdate
1131 AND NOT EXISTS (SELECT 1 FROM wf_local_user_roles wlur
1132 WHERE wlur.role_orig_system_id = mem.group_id
1133 AND wlur.user_orig_system = l_hz_orig_system
1134 AND wlur.role_orig_system = l_grp_orig_system);
1135
1136
1137 log_msg_hdr3 fnd_new_messages.message_text%type := NULL;
1138 log_message8 fnd_new_messages.message_text%type := NULL;
1139 log_message9 fnd_new_messages.message_text%type := NULL;
1140 log_message10 fnd_new_messages.message_text%type := NULL;
1141 log_message11 fnd_new_messages.message_text%type := NULL;
1142
1143 log_message19 fnd_new_messages.message_text%type := NULL;
1144 log_message20 fnd_new_messages.message_text%type := NULL;
1145 log_message21 fnd_new_messages.message_text%type := NULL;
1146
1147 log_message30 fnd_new_messages.message_text%type := NULL;
1148 log_message31 fnd_new_messages.message_text%type := NULL;
1149
1150 l_end_date_active DATE;
1151 m_end_date_active DATE;
1152 m_start_date_active DATE;
1153 m_role_name wf_local_roles.name%TYPE;
1154 l_user_name wf_local_roles.name%TYPE;
1155 m_user_orig_system wf_local_roles.orig_system%TYPE;
1156 l_user_orig_system wf_local_roles.orig_system%TYPE;
1157 m_user_orig_system_id wf_local_roles.orig_system_id%TYPE;
1158 l_user_orig_system_id wf_local_roles.orig_system_id%TYPE;
1159
1160 BEGIN
1161
1162 log_msg_hdr3 := fnd_message.get_string ('JTF','JTF_RS_WF_ROLE_SYNC_LOG_HDR3');
1163 log_message8 := fnd_message.get_string ('JTF','JTF_RS_WF_ROLE_SYNC_LOG_MSG8');
1164 log_message9 := fnd_message.get_string ('JTF','JTF_RS_WF_ROLE_SYNC_LOG_MSG9');
1165 log_message10 := fnd_message.get_string ('JTF','JTF_RS_WF_ROLE_SYNC_LOG_MSG10');
1166 log_message11 := fnd_message.get_string ('JTF','JTF_RS_WF_ROLE_SYNC_LOG_MSG11');
1167
1168 log_message19 := fnd_message.get_string ('JTF','JTF_RS_WF_ROLE_SYNC_LOG_MSG19');
1169 log_message20 := fnd_message.get_string ('JTF','JTF_RS_WF_ROLE_SYNC_LOG_MSG20');
1170 log_message21 := fnd_message.get_string ('JTF','JTF_RS_WF_ROLE_SYNC_LOG_MSG21');
1171
1175 fnd_file.put_line (fnd_file.log,log_msg_hdr3);
1172 log_message30 := fnd_message.get_string ('JTF','JTF_RS_WF_ROLE_SYNC_LOG_MSG30');
1173 log_message31 := fnd_message.get_string ('JTF','JTF_RS_WF_ROLE_SYNC_LOG_MSG31');
1174
1176 --fnd_file.put_line (fnd_file.log,'Beginning of Resource Workflow Synchronization for Group Members');
1177 fnd_file.put_line (fnd_file.log,'----------------------------------------------------------------');
1178 fnd_file.new_line (fnd_file.log,1);
1179
1180 --Inactivate all wf user roles which are still active and whose corresponding records
1181 -- in resource group members table have been deleted (delete_flag Y).
1182 FOR i IN c_wlur_grp_mem_del LOOP
1183 BEGIN
1184 Wf_local_synch.propagate_user_role (
1185 p_user_orig_system => i.user_orig_system,
1186 p_user_orig_system_id => i.user_orig_system_id,
1187 p_role_orig_system => l_grp_orig_system,
1188 p_role_orig_system_id => i.group_id,
1189 p_raiseerrors => TRUE,
1190 p_start_date => sysdate-2,
1191 p_expiration_date => sysdate-1,
1192 p_overwrite => TRUE);
1193 EXCEPTION
1194 WHEN OTHERS THEN
1195 wf_core.get_error(error_name, error_message, error_stack);
1196 fnd_file.put_line (fnd_file.log,error_message);
1197 fnd_file.new_line (fnd_file.log,1);
1198 fnd_file.put_line (fnd_file.log,error_stack);
1199 fnd_file.new_line (fnd_file.log,1);
1200 wf_core.clear;
1201 END;
1202 END LOOP;
1203
1204 fnd_file.put_line (fnd_file.log,log_message8);
1205 --fnd_file.put_line (fnd_file.log,'Successfully inactivated all records in Workflow User Roles table,
1206 --whose corresponding records in Resource Group Members table have been deleted.
1207 fnd_file.new_line (fnd_file.log,1);
1208
1209
1210 -- Create Workflow User Role for Active Resource Group Members
1211 -- whose Resource Category is OTHER or TBH
1212 FOR i IN c_grp_mem_wf_crt LOOP
1213 BEGIN
1214 IF i.m_end_date_active = l_fnd_date THEN
1215 l_end_date_active := NULL;
1216 ELSE
1217 l_end_date_active := i.m_end_date_active;
1218 END IF;
1219 Wf_local_synch.propagate_user_role (
1220 p_user_orig_system => l_ind_orig_system,
1221 p_user_orig_system_id => i.resource_id,
1222 p_role_orig_system => l_grp_orig_system,
1223 p_role_orig_system_id => i.group_id,
1224 p_raiseerrors => TRUE,
1225 p_start_date => i.m_start_date_active,
1226 p_expiration_date => l_end_date_active);
1227 EXCEPTION
1228 WHEN OTHERS THEN
1229 wf_core.get_error(error_name, error_message, error_stack);
1230 fnd_file.put_line (fnd_file.log,error_message);
1231 fnd_file.new_line (fnd_file.log,1);
1232 fnd_file.put_line (fnd_file.log,error_stack);
1233 fnd_file.new_line (fnd_file.log,1);
1234 wf_core.clear;
1235 END;
1236 END LOOP;
1237
1238 fnd_file.put_line (fnd_file.log,log_message30);
1239 --fnd_file.put_line (fnd_file.log,'Successfully created Workflow User Role Records, for its corresponding records in
1240 --Groups Members Table. This is for Resources whose category is Other or To be hired.');
1241 fnd_file.new_line (fnd_file.log,1);
1242
1243
1244 -- Create Workflow User Role for Active Resource Group Members
1245 -- whose Resource Cateogry is EMPLOYEE, PARTY, PARTNER or SUPPLIER_CONTACT
1246 FOR i IN c_grp_mem_wf_epps_crt LOOP
1247 IF i.m_end_date_active = l_fnd_date THEN
1248 l_end_date_active := NULL;
1249 ELSE
1250 l_end_date_active := i.m_end_date_active;
1251 END IF;
1252 JTF_RS_WF_INTEGRATION_PUB.get_wf_role (
1253 p_resource_id => i.resource_id
1254 ,x_role_name => l_user_name
1255 ,x_orig_system => l_user_orig_system
1256 ,x_orig_system_id => l_user_orig_system_id
1257 );
1258 IF (l_user_orig_system IS NULL OR l_user_orig_system_id IS NULL) THEN
1259 fnd_file.put_line (fnd_file.log,'Not creating Workflow User Roles for corresponding Resource Group Members whose Member
1260 Resource is Resource ID - '||i.resource_id||', Resource Name - '||i.resource_name||' and Resource
1261 Category - '||i.category||', because there was no corresponding User defined in wf_local_roles table');
1262 ELSE
1263 BEGIN
1264 Wf_local_synch.propagate_user_role (
1265 p_user_orig_system => l_user_orig_system,
1266 p_user_orig_system_id => l_user_orig_system_id,
1267 p_role_orig_system => l_grp_orig_system,
1268 p_role_orig_system_id => i.group_id,
1269 p_raiseerrors => TRUE,
1270 p_start_date => i.m_start_date_active,
1271 p_expiration_date => l_end_date_active);
1272 EXCEPTION
1273 WHEN OTHERS THEN
1274 wf_core.get_error(error_name, error_message, error_stack);
1275 fnd_file.put_line (fnd_file.log,error_message);
1276 fnd_file.new_line (fnd_file.log,1);
1277 fnd_file.put_line (fnd_file.log,error_stack);
1278 fnd_file.new_line (fnd_file.log,1);
1279 wf_core.clear;
1280 END;
1281 END IF;
1282 END LOOP;
1283
1287 fnd_file.new_line (fnd_file.log,1);
1284 fnd_file.put_line (fnd_file.log,log_message31);
1285 --fnd_file.put_line (fnd_file.log,'Successfully created Workflow User Role Records, for its corresponding records in
1286 --Resource Group Members tables. This is for Resources who category is Employee, Party, Partner or Supplier Contact.');
1288
1289
1290 EXCEPTION
1291 WHEN OTHERS THEN
1292 wf_core.get_error(error_name, error_message, error_stack);
1293 fnd_file.put_line (fnd_file.log,error_message);
1294 fnd_file.new_line (fnd_file.log,1);
1295 fnd_file.put_line (fnd_file.log,error_stack);
1296 fnd_file.new_line (fnd_file.log,1);
1297 wf_core.clear;
1298
1299 END synchronize_group_members_wf;
1300
1301
1302 PROCEDURE synchronize_team_members_wf AS
1303
1304 -- Cursor to get all active wf user role records whose corresponding
1305 -- resource team member records have been deleted (delete_flag Y).
1306
1307 CURSOR c_wlur_team_mem_del IS
1308 SELECT wlur.user_orig_system, wlur.user_orig_system_id, mem.team_id
1309 FROM wf_local_user_roles wlur, jtf_rs_team_members mem
1310 WHERE NVL (mem.delete_flag,'N') = 'Y'
1311 AND wlur.role_orig_system = l_team_orig_system
1312 AND wlur.role_orig_system_id = mem.team_id
1313 AND wlur.role_name = l_team_orig_system ||':'|| mem.team_id
1314 AND wlur.user_orig_system <> l_team_orig_system
1315 AND NVL(wlur.expiration_date, l_sysdate) >= l_sysdate;
1316
1317
1318 -- Select all active records from resource team members table
1319 -- (jtf_rs_team_members) that are still not defined in workflow
1320 -- user role table (wf_local_user_roles). This is for 'INDIVIDUAL'
1321 -- resource team members whose category is OTHER or TBH.
1322 -- These records have to be created in workflow user roles table.
1323
1324 CURSOR c_team_mem_wf_crt IS
1325 SELECT mem.team_resource_id resource_id, mem.team_id
1326 ,greatest (team.start_date_active, ext.start_date_active) m_start_date_active
1327 ,least (NVL (team.end_date_active, l_fnd_date) ,
1328 NVL (ext.end_date_active, l_fnd_date)) m_end_date_active
1329 FROM jtf_rs_resource_extns_vl ext, jtf_rs_team_members mem, jtf_rs_teams_b team
1330 WHERE NVL (mem.delete_flag,'N') <> 'Y'
1331 AND mem.team_resource_id = ext.resource_id
1332 AND mem.resource_type = 'INDIVIDUAL'
1333 AND mem.team_id = team.team_id
1334 AND ext.category IN ('OTHER','TBH')
1335 AND ext.resource_name IS NOT NULL
1336 AND NVL (TRUNC (ext.end_date_active),l_sysdate) >= l_sysdate
1337 AND NVL (TRUNC (team.end_date_active),l_sysdate) >= l_sysdate
1338 AND NOT EXISTS (SELECT 1 FROM wf_local_user_roles wlur
1339 WHERE mem.resource_type = 'INDIVIDUAL'
1340 AND wlur.user_orig_system_id = mem.team_resource_id
1341 AND wlur.user_orig_system = l_ind_orig_system
1342 AND wlur.role_orig_system_id = mem.team_id
1343 AND wlur.role_orig_system = l_team_orig_system);
1344
1345
1346 -- Select all active records from resource team members table
1347 -- (jtf_rs_team_members) that are still not defined in workflow user role
1348 -- table (wf_local_user_roles). This is for 'INDIVIDUAL' resource team
1349 -- members whose category is EMPLOYEE, PARTY, PARTNER or SUPPLIER_CONTACT
1350 -- These records have to be created in workflow user roles table.
1351
1352 CURSOR c_team_mem_wf_epps_crt IS
1353 SELECT mem.team_resource_id resource_id, mem.team_id, ext.category, ext.resource_name
1354 ,greatest (team.start_date_active, ext.start_date_active) m_start_date_active
1355 ,least (NVL (team.end_date_active, l_fnd_date) ,
1356 NVL (ext.end_date_active, l_fnd_date)) m_end_date_active
1357 FROM jtf_rs_resource_extns_vl ext, jtf_rs_team_members mem, jtf_rs_teams_b team
1358 WHERE NVL (mem.delete_flag,'N') <> 'Y'
1359 AND mem.team_resource_id = ext.resource_id
1360 AND mem.resource_type = 'INDIVIDUAL'
1361 AND mem.team_id = team.team_id
1362 AND ext.resource_name IS NOT NULL
1363 AND ext.category IN ('EMPLOYEE','PARTY','PARTNER','SUPPLIER_CONTACT')
1364 AND NVL (TRUNC (ext.end_date_active),l_sysdate) >= l_sysdate
1365 AND NVL (TRUNC (team.end_date_active),l_sysdate) >= l_sysdate
1366 AND NOT EXISTS (SELECT 1 FROM wf_local_user_roles wlur
1367 WHERE mem.resource_type = 'INDIVIDUAL'
1368 AND wlur.role_orig_system_id = mem.team_id
1369 AND wlur.role_orig_system = l_team_orig_system
1370 AND wlur.user_orig_system = l_hz_orig_system);
1371
1372 -- Select all active records from resource team members table
1373 -- (jtf_rs_team_members) that are still not defined in workflow
1374 -- user role table (wf_local_user_roles). This is for 'GROUP'
1375 -- resource team members.
1376 -- These records have to be created in workflow user roles table.
1377
1378 CURSOR c_team_mem_grp_wf_crt IS
1379 SELECT mem.team_resource_id group_id, mem.team_id
1380 ,greatest (team.start_date_active, grp.start_date_active) m_start_date_active
1381 ,least (NVL (team.end_date_active, l_fnd_date) ,
1382 NVL (grp.end_date_active, l_fnd_date)) m_end_date_active
1386 AND mem.resource_type = 'GROUP'
1383 FROM jtf_rs_groups_b grp, jtf_rs_team_members mem, jtf_rs_teams_b team
1384 WHERE NVL (mem.delete_flag,'N') <> 'Y'
1385 AND mem.team_resource_id = grp.group_id
1387 AND mem.team_id = team.team_id
1388 AND NVL (TRUNC (grp.end_date_active),l_sysdate) >= l_sysdate
1389 AND NVL (TRUNC (team.end_date_active),l_sysdate) >= l_sysdate
1390 AND NOT EXISTS (SELECT 1 FROM wf_local_user_roles wlur
1391 WHERE mem.resource_type = 'GROUP'
1392 AND wlur.user_orig_system_id = mem.team_resource_id
1393 AND wlur.user_orig_system = l_grp_orig_system
1394 AND wlur.role_orig_system_id = mem.team_id
1395 AND wlur.role_orig_system = l_team_orig_system);
1396
1397
1398 log_msg_hdr5 fnd_new_messages.message_text%type := NULL;
1399 log_message15 fnd_new_messages.message_text%type := NULL;
1400 log_message16 fnd_new_messages.message_text%type := NULL;
1401 log_message17 fnd_new_messages.message_text%type := NULL;
1402 log_message18 fnd_new_messages.message_text%type := NULL;
1403
1404 log_message22 fnd_new_messages.message_text%type := NULL;
1405 log_message23 fnd_new_messages.message_text%type := NULL;
1406 log_message24 fnd_new_messages.message_text%type := NULL;
1407 log_message25 fnd_new_messages.message_text%type := NULL;
1408
1409 log_message32 fnd_new_messages.message_text%type := NULL;
1410 log_message33 fnd_new_messages.message_text%type := NULL;
1411 log_message34 fnd_new_messages.message_text%type := NULL;
1412 log_message35 fnd_new_messages.message_text%type := NULL;
1413
1414 l_end_date_active DATE;
1415 m_end_date_active DATE;
1416 m_start_date_active DATE;
1417 l_user_name wf_local_roles.name%TYPE;
1418 l_user_orig_system wf_local_roles.orig_system%TYPE;
1419 l_user_orig_system_id wf_local_roles.orig_system_id%TYPE;
1420
1421 BEGIN
1422
1423 log_msg_hdr5 := fnd_message.get_string ('JTF','JTF_RS_WF_ROLE_SYNC_LOG_HDR5');
1424 log_message15 := fnd_message.get_string ('JTF','JTF_RS_WF_ROLE_SYNC_LOG_MSG15');
1425 log_message16 := fnd_message.get_string ('JTF','JTF_RS_WF_ROLE_SYNC_LOG_MSG16');
1426 log_message17 := fnd_message.get_string ('JTF','JTF_RS_WF_ROLE_SYNC_LOG_MSG17');
1427 log_message18 := fnd_message.get_string ('JTF','JTF_RS_WF_ROLE_SYNC_LOG_MSG18');
1428
1429 log_message22 := fnd_message.get_string ('JTF','JTF_RS_WF_ROLE_SYNC_LOG_MSG22');
1430 log_message23 := fnd_message.get_string ('JTF','JTF_RS_WF_ROLE_SYNC_LOG_MSG23');
1431 log_message24 := fnd_message.get_string ('JTF','JTF_RS_WF_ROLE_SYNC_LOG_MSG24');
1432 log_message25 := fnd_message.get_string ('JTF','JTF_RS_WF_ROLE_SYNC_LOG_MSG25');
1433
1434 log_message32 := fnd_message.get_string ('JTF','JTF_RS_WF_ROLE_SYNC_LOG_MSG32');
1435 log_message33 := fnd_message.get_string ('JTF','JTF_RS_WF_ROLE_SYNC_LOG_MSG33');
1436 log_message34 := fnd_message.get_string ('JTF','JTF_RS_WF_ROLE_SYNC_LOG_MSG34');
1437 log_message35 := fnd_message.get_string ('JTF','JTF_RS_WF_ROLE_SYNC_LOG_MSG35');
1438
1439 fnd_file.put_line (fnd_file.log,log_msg_hdr5);
1440 --fnd_file.put_line (fnd_file.log,'Beginning of Resource Workflow Synchronization for Team Members');
1441 fnd_file.put_line (fnd_file.log,'---------------------------------------------------------------');
1442 fnd_file.new_line (fnd_file.log,1);
1443
1444 --Inactivate all wf user roles which are still active and whose corresponding records
1445 -- in resource team members table have been deleted (delete_flag Y).
1446 FOR i IN c_wlur_team_mem_del LOOP
1447 BEGIN
1448 Wf_local_synch.propagate_user_role (
1449 p_user_orig_system => i.user_orig_system,
1450 p_user_orig_system_id => i.user_orig_system_id,
1451 p_role_orig_system => l_team_orig_system,
1452 p_role_orig_system_id => i.team_id,
1453 p_raiseerrors => TRUE,
1454 p_start_date => sysdate-2,
1455 p_expiration_date => sysdate-1,
1456 p_overwrite => TRUE);
1457 EXCEPTION
1458 WHEN OTHERS THEN
1459 wf_core.get_error(error_name, error_message, error_stack);
1460 fnd_file.put_line (fnd_file.log,error_message);
1461 fnd_file.new_line (fnd_file.log,1);
1462 fnd_file.put_line (fnd_file.log,error_stack);
1463 fnd_file.new_line (fnd_file.log,1);
1464 wf_core.clear;
1465 END;
1466 END LOOP;
1467
1468 fnd_file.put_line (fnd_file.log,log_message32);
1469 --fnd_file.put_line (fnd_file.log,'Successfully inactivated all records in Workflow User Roles table, whose
1470 --corresponding records in Resource Team Members table have been deleted.');
1471 fnd_file.new_line (fnd_file.log,1);
1472
1473
1474 -- Create Workflow User Role for Active Resource Team Members whose
1475 -- resource_type is INDIVIDUAL and resource category is OTHER or TBH
1476 FOR i IN c_team_mem_wf_crt LOOP
1477 IF i.m_end_date_active = l_fnd_date THEN
1478 l_end_date_active := NULL;
1479 ELSE
1480 l_end_date_active := i.m_end_date_active;
1481 END IF;
1482 BEGIN
1483 Wf_local_synch.propagate_user_role(
1484 p_user_orig_system => l_ind_orig_system,
1485 p_user_orig_system_id => i.resource_id,
1486 p_role_orig_system => l_team_orig_system,
1487 p_role_orig_system_id => i.team_id,
1488 p_raiseerrors => TRUE,
1492 WHEN OTHERS THEN
1489 p_start_date => i.m_start_date_active,
1490 p_expiration_date => l_end_date_active);
1491 EXCEPTION
1493 wf_core.get_error(error_name, error_message, error_stack);
1494 fnd_file.put_line (fnd_file.log,error_message);
1495 fnd_file.new_line (fnd_file.log,1);
1496 fnd_file.put_line (fnd_file.log,error_stack);
1497 fnd_file.new_line (fnd_file.log,1);
1498 wf_core.clear;
1499 END;
1500 END LOOP;
1501
1502 fnd_file.put_line (fnd_file.log,log_message33);
1503 --fnd_file.put_line (fnd_file.log,'Successfully created Workflow User Role Records, for its corresponding records in
1504 --Resource Team Members table of Resource Type Individual. This is for Resources who category is Other or To be hired.');
1505 fnd_file.new_line (fnd_file.log,1);
1506
1507 -- Create Workflow User Role for Active Resource Team Members whose resource_type is
1508 -- INDIVIDUAL and resource category is EMPLOYEE, PARTY, PARTNER or SUPPLIER_CONTACT
1509 FOR i IN c_team_mem_wf_epps_crt LOOP
1510 IF i.m_end_date_active = l_fnd_date THEN
1511 l_end_date_active := NULL;
1512 ELSE
1513 l_end_date_active := i.m_end_date_active;
1514 END IF;
1515 -- Get wf user_orig_system and user_orig_system_id for the given resource_id
1516 jtf_rs_wf_integration_pub.get_wf_role (
1517 p_resource_id => i.resource_id
1518 ,x_role_name => l_user_name
1519 ,x_orig_system => l_user_orig_system
1520 ,x_orig_system_id => l_user_orig_system_id
1521 );
1522 IF (l_user_orig_system IS NULL OR l_user_orig_system_id IS NULL) THEN
1523 fnd_file.put_line (fnd_file.log,'Not creating Workflow User Roles for corresponding Resource Team Members whose Member
1524 Resource is Resource ID - '||i.resource_id||', Resource Name - '||i.resource_name||' and Resource
1525 Category - '||i.category||', because there was no corresponding User defined wf_local_roles table');
1526 ELSE
1527 BEGIN
1528 Wf_local_synch.propagate_user_role(
1529 p_user_orig_system => l_user_orig_system,
1530 p_user_orig_system_id => l_user_orig_system_id,
1531 p_role_orig_system => l_team_orig_system,
1532 p_role_orig_system_id => i.team_id,
1533 p_raiseerrors => TRUE,
1534 p_start_date => i.m_start_date_active,
1535 p_expiration_date => l_end_date_active);
1536 EXCEPTION
1537 WHEN OTHERS THEN
1538 wf_core.get_error(error_name, error_message, error_stack);
1539 fnd_file.put_line (fnd_file.log,error_message);
1540 fnd_file.new_line (fnd_file.log,1);
1541 fnd_file.put_line (fnd_file.log,error_stack);
1542 fnd_file.new_line (fnd_file.log,1);
1543 wf_core.clear;
1544 END;
1545 END IF;
1546 END LOOP;
1547
1548 fnd_file.put_line (fnd_file.log,log_message34);
1549 --fnd_file.put_line (fnd_file.log,'Successfully created Workflow User Role Records, for its corresponding records in
1550 --Resource Team Members tables of Resource Type Individual. This is for Resources who category is Employee, Party,
1551 --Partner or Supplier Contact.');
1552 fnd_file.new_line (fnd_file.log,1);
1553
1554 -- Create Workflow User Role for Active Resource Team Members ('GROUP' Members)
1555 FOR i IN c_team_mem_grp_wf_crt LOOP
1556 IF i.m_end_date_active = l_fnd_date THEN
1557 l_end_date_active := NULL;
1558 ELSE
1559 l_end_date_active := i.m_end_date_active;
1560 END IF;
1561 BEGIN
1562 Wf_local_synch.propagate_user_role(
1563 p_user_orig_system => l_grp_orig_system,
1564 p_user_orig_system_id => i.group_id,
1565 p_role_orig_system => l_team_orig_system,
1566 p_role_orig_system_id => i.team_id,
1567 p_raiseerrors => TRUE,
1568 p_start_date => i.m_start_date_active,
1569 p_expiration_date => l_end_date_active);
1570 EXCEPTION
1571 WHEN OTHERS THEN
1572 wf_core.get_error(error_name, error_message, error_stack);
1573 fnd_file.put_line (fnd_file.log,error_message);
1574 fnd_file.new_line (fnd_file.log,1);
1575 fnd_file.put_line (fnd_file.log,error_stack);
1576 fnd_file.new_line (fnd_file.log,1);
1577 wf_core.clear;
1578 END;
1579 END LOOP;
1580
1581 fnd_file.put_line (fnd_file.log,log_message35);
1582 --fnd_file.put_line (fnd_file.log,'Successfully created Workflow User Role Records, for its corresponding
1583 --records in Resource Team Members table of Resource Type Group');
1584 fnd_file.new_line (fnd_file.log,1);
1585
1586 EXCEPTION
1587 WHEN OTHERS THEN
1588 wf_core.get_error(error_name, error_message, error_stack);
1589 fnd_file.put_line (fnd_file.log,error_message);
1590 fnd_file.new_line (fnd_file.log,1);
1591 fnd_file.put_line (fnd_file.log,error_stack);
1592 fnd_file.new_line (fnd_file.log,1);
1593 wf_core.clear;
1594
1595 END synchronize_team_members_wf;
1596
1597 BEGIN
1598
1599 IF (P_SYNC_COMP = 'Team') THEN
1600 synchronize_teams_wf;
1601 synchronize_groups_wf;
1602 synchronize_resources_wf;
1603 synchronize_team_members_wf;
1604 ELSIF (P_SYNC_COMP = 'Group') THEN
1605 synchronize_groups_wf;
1606 synchronize_resources_wf;
1607 synchronize_group_members_wf;
1608 ELSIF (P_SYNC_COMP = 'All') THEN
1609 synchronize_groups_wf;
1610 synchronize_teams_wf;
1611 synchronize_resources_wf;
1612 synchronize_group_members_wf;
1613 synchronize_team_members_wf;
1614 END IF;
1615
1616 END synchronize_wf_roles;
1617
1618 END jtf_rs_conc_wf_pub;
[Home] [Help]