CAUSE AND FIX for MISSING RESPONSIBILITIES issue in EBS R12
===================================================
1) Run the Concurrent Program “Workflow Directory Services User/Role Validation” with the following Parameters
Batch Size : 100000
Fix dangling user/roles : Yes
Add missing user/role assignments : No
Update WHO columns in WF tables : No
2) Run the following script to check if there is any discrepancies between the tables “wf_local_user_roles” & “wf_user_role_assignments” .
select ura.user_name, ura.role_name
from wf_local_user_roles ur, wf_user_role_assignments ura
where ur.user_name = ura.user_name
and ur.role_name = ura.role_name
and ura.relationship_id = -1
and ((ur.effective_start_date is null or ur.effective_start_date <>
ura.effective_start_date)
or (ur.effective_end_date is null or ur.effective_end_date <> ura.effective_end_date));
If the above script returns any rows,
(i) Backup both the tables ( wf_local_user_roles and wf_user_role_assignments)
(ii) Run the below update command, which updates both the wf_local_user_roles and wf_user_role_assignments tables with null values for the roles
select name from v$database;
update wf_user_role_assignments
set user_end_date=null,
role_end_date=null,
assigning_Role_end_Date=null,
effective_end_Date=to_date(’31-12-4712′,’dd-mm-yyyy’)
where user_name=upper(‘&username’);
update wf_user_role_assignments
set user_end_date=null,
role_end_date=null,
assigning_role_end_Date=null,
effective_end_Date=to_date(’31-12-4712′,’dd-mm-yyyy’)
where role_name=upper(‘&rolename’);
update wf_local_user_Roles
set user_end_date=null,
role_end_date=null,
effective_end_Date=to_date(’31-12-4712′,’dd-mm-yyyy’)
where user_name=upper(‘&username’);
update wf_local_user_Roles
set user_end_date=null,
role_end_date=null,
effective_end_Date=to_date(’31-12-4712′,’dd-mm-yyyy’)
where role_name=upper(‘&rolename’);
commit;
(iii) Bounce workflow notification mailer
(iv) Bounce Apache
(v) Retest the issue