[Home] [Help]
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
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)
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
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)
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,
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
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))
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
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))
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,
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')
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,
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'
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).
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);
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
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:
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
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
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
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
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);
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
1386: AND mem.resource_type = 'GROUP'
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