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