Guideline for fixing Missing Responsibilities (Doc ID 2508139.1)

Guideline for fixing Missing Responsibilities (Doc ID 2508139.1)
How To Fix Missing Responsibilities For A User (Doc ID 429852.1)

APPLIES TO:

Oracle Workflow - Version 12.1.3 to 12.2.8 [Release 12.1 to 12.2]
Information in this document applies to any platform.

GOAL

 Below steps are needed to collect the required information to determine the cause and fix the missing responsibilities issue

SOLUTION

1 Check if User and Role Data is Synchronized

It may happen that when a user or user-responsibilities are modified, business events may fail to update tables related to role and user information. User data can be found in tables FND_USER, FND_RESPONSIBILITY, WF_LOCAL_USER_ROLES, and WF_USER_ROLE_ASSIGNMENTS and these tables can become out of sync as some business events may fail.

You have two options to synchronize data:

1 Remove/Restore User/Role Related Values to fire unprocessed business events

1.1. Navigate to the define user form (FNDSCAUS).
1.2. Query up the user (that is having the issue) and ensure responsibilities not showing up are NOT end dated.
1.3. END DATE the USER (not the responsibility) and save the record.
1.4. UN-END DATE the USER and save the record.
1.5. Have the user log off and back on and verify that missing responsibilities now appear.

The action plan above should automatically re-syncs user/role data in the tables mentioned above. If issue still persists, do next step.

2 Run the "Workflow Directory Services User/Role Validation" Concurrent Program

Your issue may also occur because out of sync data between FND user/role information and WF Local user/role information. To fix this issue you need to run the "Workflow Directory Services User/Role Validation" Concurrent Program.

The 'Workflow Directory Services/User Role Validation' concurrent request was designed as a one time data fix , though it is re-runnable. The concurrent program needs not be run regularly or scheduled because it corrects the corrupt data at one go. So if you suspect corrupted data that is when you should run it.

2.1 Run the Concurrent Program with the following parameters:

     "Workflow Directory Services User/Role Validation" Parameters : 100000, Yes, No, No
 

Parameters are explained below in sequential order:

• Batch Size: Number of records that will be processed per commit cycle
• Fix dangling user/roles: Removes any user/roles where the user and/or the role does not exist in WF_LOCAL_ROLES
• Add missing user/role assignments: Adds any user/roles that are present in WF_LOCAL_USER_ROLES but missing in WF_USER_ROLE_ASSIGNMENTS
• Update WHO columns in WF tables: Controls the updating of who columns when corrupt records are corrected

2.2. Bounce Apache.

2.3. Retest the issue.

4. Verify Discrepancies in User/Role Data in Workflow Tables

During Upgrade and Patching a concurrent request may change the status of the role in the wf_local_user_roles table. In most cases where this occurs, an upgrade has recently been performed and during the upgrade some responsibilities were removed. This affected the dates of other responsibilities and usually coincided with the end date that appeared in the column. For example, it has been reported that after upgrading and patching, the System Administrator responsibility no longer appears in the Navigator.

Run the following script to check if there is any discrepancies between the tables:

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 you get any rows returned for the user having the issue, do the following:

a. Take a backup of the following tables:

• wf_local_user_roles
• wf_user_role_assignments

b. Set the role_end_date to null in the wf_local_user_roles.

The sql used will depend on the responsibility missing. For example for the System Administrator responsibility, type in the following:

update wf_local_user_roles
set role_end_date = null
where user_name like 'SYSADMIN%'
and role_name = 'FND_RESP|SYSADMIN|SYSTEM_ADMINISTRATOR|STANDARD';

OR

c. Update both the wf_local_user_roles and wf_user_role_assignments tables with null values for the roles.

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');

Once the wf_user_Role_Assignments table is updated with null end dates, even if wf_local_user_Roles is summarized from it, then end date values would remain null and the problem should not re-occur.

2. A user cannot see any responsibilities after login:

a. Review the 'Applications Start Page' profile option.

b. If it's set to an unknown value, un-set the profile and re-test.

3. The "System Administrator" Responsibility is missing:

Please run the below concurrent programs in the order given, with the respective values of the parameters, and then retest the issue.

a. Workflow Directory services user/role validation with argument values as 10000:Yes:Yes:No:NULL.

b. Run adadmin - Compile/generate menu and rebuild JAR files.

c. Rerun:  FNDLOAD APPS/<apps_password> 0 Y UPLOAD @FND:patch/115/import/afscursp.lct @FND:patch/115/import/US/wfssresp.ldt

 4. Check if all the workflow services are up and running:

This select reflects that the status of the workflow services
SQL> select component_id,component_name, component_status from fnd_svc_components

FND_SVC_COMPONENTS
COMPONENT_ID  COMPONENT_NAME  COMPONENT_                     STATUS
10000         ECX Inbound Agent Listener                                    STOPPED
10001         ECX Transaction Agent Listener                               STOPPED
10020         Web Services IN Agent                                          STOPPED
10002         Workflow Deferred Agent Listener                            STOPPED
10003         Workflow Deferred Notification Agent Listener            STOPPED
10004         Workflow Error Agent Listener                                 STOPPED
10024         Workflow Inbound JMS Agent Listener                      STOPPED
10005         Workflow Inbound Notifications Agent Listener           STOPPED
10021         Workflow Java Deferred Agent Listener                     STOPPED
10022         Workflow Java Error Agent Listener                          STOPPED

4.1. Need to activate/start:

a. From Container Column: Agent Listener Service
b. From Service Components window: Workflow Java Error Agent Listener, Workflow Java Deferred Agent Listener, Workflow Inbound Notifications Agent Listener, Workflow Error Agent Listener, Workflow Deferred Notification Agent Listener, Workflow Deferred Agent Listener, Web Services IN Agent.

4.2. Once the service components above are started, the processes should then be processed.


#######################

APPLIES TO:

Oracle Application Object Library - Version 11.5.10.2 to 12.2 [Release 11.5 to 12.2]
Oracle Workflow - Version 11.5.10.2 to 12.2 [Release 11.5.10 to 12.2]
Oracle Bills of Material - Version 12.2.5 to 12.2.5 [Release 12.2]
Information in this document applies to any platform.
Reviewed for Relevance 29 Jul 18.

GOAL

This is a consolidation of Top Documents to provide a Single Source for troubleshooting common problems with Missing Responsibilities for a user.

NOTE: Please first review the troubleshooting steps below to apply one that best meets current needs.  Not ALL are required for resolving missing responsibility issues. 

All information contained on this document includes both versions 11i and R12. When something is either 11i or R12 specific, it will be specified.

The symptoms experienced are:

• Missing responsibilities for a user in the navigator page during logon.
• After upgrading and patching, the System Administrator responsibility no longer appears in the Navigator.
• Some users cannot see/receive their open notifications due to missing responsibilities.
• Some users get notification e-mails even though their responsibilities are end-dated
• If a user is end-dated, a user's responsibility creates duplicate row
• Newly added responsibility is not displayed until Apache is bounced
• Intermitent issue: After resetting user passwords using the "Login Assistance " > "Forgot Password" link, users do not see their responsibilities in home page and get the following message:
• "There are no active responsibilities available for this user"
• "System Administrator" responsibility missing

Missing responsibilities for a user may occur if any of the following actions take place:

• User is end-dated.
• Username is changed.
• User is reactivated. Value of the end-date field is removed.
• Upgrade and patching.
• 11i: ATG RUP3 or ATG RUP4 Patches are applied.
• 11i: Responsibility with a colon (:) is assigned to a user after CU2 patch is applied
• The Effective From date for the user responsibility assigned is invalid. (For example 1951 - before Oracle Applications even existed)

SUMMARY / CHECKS

When a responsibility is assigned to a user in the Security / User / Define form, triggers are fired to send business events to the Workflow Deferred Agent Listeners. These events need to be processed before the base tables are updated with the changes. (The data used in the forms is derived from views based on certain workflow tables).

These business events are processed by the Workflow Deferred Agent Listener and Workflow Java Deferred Agent Listener.

Check the following:

1. Ensure both workflow services are running, the Workflow Deferred Agent Listener and Workflow Java Deferred Agent Listener.

Run the following code to get status of Listeners:

SELECT c.component_name, c.component_status from fnd_svc_components c where upper (c.component_name) in ('WORKFLOW DEFERRED AGENT LISTENER',
'WORKFLOW JAVA DEFERRED AGENT LISTENER');

Activate listeners if not running. See Note 548918.1 "How to start the Workflow Deferred Agent Listener and Workflow Java Deferred Agent Listener?"

2. Ensure Deferred Workflow Events are being processed.

Run the following query to check for Deferred Workflow Events:

select corrid, decode(state, 0, 'Ready', 1, 'Delayed', 2, 'Retained',  
3, 'Exception', to_char(state)) State, count(*) COUNT from WF_DEFERRED where corrid like 'APPS:oracle.apps.fnd.wf.ds%' group by corrid, state ;

State of READY indicates they have NOT yet been processed.

If a backlog of processes exist, missing responsibilities may result from that. There can be a delay based on the load of the listeners as these are not the only business events that they process.

Use the script wfver.sql that is provided with Oracle Workflow 2.x to reflect the current health of workflow. For more information, see Note 1011379.102 "How To Check The Version And Health Of Workflow Installed On The Server".

3. Ensure the Apache Java Cache Mechanism is working fine.

Do either of the two actions below to clear the cache.

3.1 Restart Apache
3.2 Manually clear Java Cache using the "Functional Administrator" responsibility. To do this, do the following:

1. Login with 'Functional Administrator' responsibility
2. Navigate to Core Service -> Caching Framework -> Global Configuration
3. Click on 'Clear All Cache'

If responsibilities are not showing up, then you may be experiencing a Java Cache Mechanism issue. See Note 374309.1 "Newly added responsibility is not displayed until Apache is bounced, after applying ATG RUP3 or ATG RUP4" to help fix this issue. Also sett Note 455194.1 to help you troubleshoot the health of your system in case you have this cache issue.

4. Ensure the Workflow Background Process request is scheduled to run daily. 

See Note 182936.1 on "How to Submit a Workflow Background Process Engine". A Workflow Background process should be scheduled to run with a frequency required by the volume of workflow processing. Some high volume instances run this once every 5 minutes. At a minimum, a workflow background process should be scheduled for deferred, stuck, and timeout processes.

5. 11i: Ensure Responsibility Assigned to User has no colon (:) in the name after CU2 patch is applied

If User Responsibility contains a colon please do the following:

1. Please download Patch 4595862 FND_USER_RESP_GROUPS fixes for CU2 and review the readme and pre-requisites
2. Please apply any required pre-requisite patches and Patch 4595862 in a Test environment.
This patch will install the following file:
FND: patch/115/sql AFSCURGB.pls 115.50
3. Please retest the issue.
4. If the issue is resolved, please migrate the solution as appropriate to other environments

6. Ensure new user responsibility assigned has a valid effective from date

You are assigning a predefined responsibility to new users. The responsibility does not show up in the menu when the user logs in. The responsibility shows in the Define User form (FNDSCAUS). However, the responsibility does not show in SSWA or Forms.

SOLUTION

Please review the Doc ID 943096.1 before going through all the steps of Doc ID 429852.1.

Please first review the steps below to determine the one that best meets existing needs as not all are required.  Please take a backup before making any of these changes and try this on a Test instance first as direct table updates from sqlplus are not supported.

1. 11i: Verify Pre/Post RUP5 code level is up-to-dateOld code may be the cause of missing responsibilities for a user. Check Pre-RUP5 and Post-RUP5 level code on your system to make sure Applications code is up-to-date and is not the cause of this issue.

a. Pre RUP5

FND_USER_RESP_GROUPS_API.sync_roles_one_resp_secgrp is not setting the status to ACTIVE
when calling WF_LOCAL_SYNCH.Propagate_Role() for the old role name format.

FND_USER_RESP_GROUPS_API.sync_roles_one_resp_secgrp() does not pass
WFSYNC_OVERWRITE when calling WF_LOCAL_SYNCH.Propagate_Role()

This happens for all versions of file AFSCURGB.pls <= 115.54.

If a user/role relationship refers to a user or a role that does not exist in WF_LOCAL_ROLES,
it should remove the relationship.

If a user/role relationship is duplicated with users and roles that do exist then it should select one
of the records based on priority of 'FND_USR', registered originating system, non-registered
originating system and log the decision using WF_LOG and correct the assignments so that
these records would not appear in the result sets for the corrective measures down the line.

This issue is described in unpublished Bug:4719658:
MISSING RESPONSIBILITIES PATCH VERSION 2

Make sure that you have AFSCURGB.pls = 115.55 or greater. This could be achieved by applying Patch 4719658

To implement the solution, execute the following steps:

1. Please download Patch 4719658
ONE OFF PATCH FOR MISSING RESPONSIBILITIES
and review the readme and pre-requisites
2. Please apply any required pre-requisite patches and Patch 4719658 in a Test environment.
This patch will install the following file:
FND: patch/115/sql AFSCURGB.pls 115.55
3. After the patch is installed, run the Workflow Directory Services User/Role Validation
concurrent program with parameters 10000, Yes, Yes.
4. Please retest the issue.
5. If the issue is resolved, please migrate the solution as appropriate to other environments.

If Patch 4719658 did not solve the issue, apply Patch 5121512:

Patch 5121512 fixes many bugs regarding the user/responsibilities and so run some sql statements via concurrent requests which solve issues regarding the end date on users and end date on assignment of responsibilities.

Note: This patch was originally replaced by < >. The most recent replacement for this patch is Patch 6241631 (Applications Technology 11i.ATG_PF.H.delta.7).

To apply this patch, please execute the following steps:
1. Please download Patch 5121512
AOL USER RESPONSIBILITY SECURITY FIXES VERSION 1
and review the readme and pre-requisites
2. Please apply any required pre-requisite patches and Patch 5121512 in a Test environment.
This patch will install the following file:
FND: patch/115/sql AFSCURGB.pls 115.61
3. After the patch is installed, run the Workflow Directory Services User/Role Validation
concurrent program with parameters 10000, Yes, Yes.
4. Please retest the issue.
5. If the issue is resolved, please migrate the solution as appropriate to other environments.

b. Post RUP5

This issue is described in Bug:6008417
AOL USER RESPONSIBILITY SECURITY FIXES 2b

To implement the solution, execute the following steps:

1. Please download Patch 6008417
    AOL USER RESPONSIBILITY SECURITY FIXES 2b
    and review the readme and pre-requisites
2. Please apply any required pre-requisite patches and Patch 6008417 in a Test environment.
    This patch will install the following file:
    FND: patch/115/sql affxdesc.sql 115.5
3. After the patch is installed, run the Workflow Directory Services User/Role Validation
    concurrent program with parameters 10000, Yes, Yes.
4. Please retest the issue.
5. If the issue is resolved, please migrate the solution as appropriate to other environments.

2. 11i: Verify the status of the subscription AP_WEB_PROXY_ASSIGN_PKG.proxy_assignments

The problem can ALSO be caused by the subscription of AP_WEB_PROXY_ASSIGN_PKG.proxy_assignments on the event 'oracle.apps.fnd.wf.ds.userRole.updated', which is failing and set to rollback the transaction, which makes the whole update not make any effect.

Run the following SQL statement to confirm that the correct subscriptions exist and that 'AP_WEB_PROXY_ASSIGN_PKG.proxy_assignments' is DISABLED (i.e it will not show in the output of the query). Only ENABLED subscriptions will be retrieved by the query.

select subscription_rule_function from wf_active_subscriptions_v
where event_name in ( 'oracle.apps.fnd.wf.ds.userRole.created', 'oracle.apps.fnd.wf.ds.userRole.updated');

If the output returns "AP_WEB_PROXY_ASSIGN_PKG.proxy_assignments", that means that this subscription is enabled and it has to be disabled. There are two options:

2.1. First one is a workaround:
  2.1.1. Connect to applications using the Workflow Administrator Web Applications responsibility
  2.1.2. Click on Business Events
  2.1.3. Query up the event 'oracle.apps.fnd.wf.ds.userRole.updated'
  2.1.4. Click on the Subscriptions link (icon)
  2.1.5. Disable the Subscription of AP_WEB_PROXY_ASSIGN_PKG.proxy_assignments.
  2.1.6  Run the Workflow Directory Services User/Role Validation concurrent program with parameters 10000, Yes, Yes

         In any case bounce the Workflow Mailer and Agent Listeners.

2.2. Or apply Patch 6153778 11i.OIE.J: UNABLE TO ACTIVATE A USER RESPONSIBILITY ASSIGNMENT

Patch 6153778 was written specifically for OIE.J and the fixes provided by 6153778 did not make it into OIE.K.
Patch 6857801 is the equivalent one-off for OIE.K.
Therefore:
To resolve this issue, if your instance is OIE.J, then apply Patch 6153778.
If your instance is OIE.K, then instead apply Patch 6857801.

This patch adds a new subscription to :
       oracle.apps.fnd.user.role.update
but you still need to disable subscription to :
       oracle.apps.fnd.wf.ds.userRole.updated

3. Verify User and Role Data is Synchronized

It may happen that when a user or user-responsibilities are modified, business events may fail to update tables related to role and user information. User data can be found in tables FND_USER, FND_RESPONSIBILITY, WF_LOCAL_USER_ROLES, and WF_USER_ROLE_ASSIGNMENTS and these tables can become out of sync as some business events may fail.

You have two options to synchronize data:

3.1 Remove/Restore User/Role Related Values to fire unprocessed business events

3.1.1. Navigate to the define user form (FNDSCAUS).
3.1.2. Query up the user (that is having the issue) and ensure responsibilities not showing up are NOT end dated.
3.1.3. END DATE the USER (not the responsibility) and save the record.
3.1.4. UN-END DATE the USER and save the record.
3.1.5. Have the user log off and back on and verify that missing responsibilities now appear.

The action plan above should automatically re-syncs user/role data in the tables mentioned above. If issue still persists, do next step.

3.2 Run the "Workflow Directory Services User/Role Validation" Concurrent Program

Your issue may also occur because out of sync data between FND user/role information and WF Local user/role information. To fix this issue you need to run the "Workflow Directory Services User/Role Validation" Concurrent Program.

The 'Workflow Directory Services/User Role Validation' concurrent request was designed as a one time data fix , though it is re-runnable. The concurrent program needs not be run regularly or scheduled because it corrects the corrupt data at one go. So if you suspect corrupted data that is when you should run it.

3.2.1 Run the Concurrent Program with the following parameters:

     "Workflow Directory Services User/Role Validation" Parameters : 100000, Yes, No, No
  

Parameters are explained below in sequential order:

• Batch Size: Number of records that will be processed per commit cycle
• Fix dangling user/roles: Removes any user/roles where the user and/or the role does not exist in WF_LOCAL_ROLES
• Add missing user/role assignments: Adds any user/roles that are present in WF_LOCAL_USER_ROLES but missing in WF_USER_ROLE_ASSIGNMENTS
• Update WHO columns in WF tables: Controls the updating of who columns when corrupt records are corrected

3.2.2. Bounce Apache.

3.2.3. Retest the issue.

4. Verify Discrepancies in User/Role Data in Workflow Tables

During Upgrade and Patching a concurrent request may change the status of the role in the wf_local_user_roles table. In most cases where this occurs, an upgrade has recently been performed and during the upgrade some responsibilities were removed. This affected the dates of other responsibilities and usually coincided with the end date that appeared in the column. For example, it has been reported that after upgrading and patching, the System Administrator responsibility no longer appears in the Navigator.

Run the following script to check if there is any discrepancies between the tables:

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 you get any rows returned for the user having the issue, do the following:

a. Take a backup of the following tables:

• wf_local_user_roles
• wf_user_role_assignments

b. Set the role_end_date to null in the wf_local_user_roles.

The sql used will depend on the responsibility missing. For example for the System Administrator responsibility, type in the following:

update wf_local_user_roles
set role_end_date = null
where user_name like 'SYSADMIN%'
and role_name = 'FND_RESP|SYSADMIN|SYSTEM_ADMINISTRATOR|STANDARD';

OR

c. Update both the wf_local_user_roles and wf_user_role_assignments tables with null values for the roles.

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');

Once the wf_user_Role_Assignments table is updated with null end dates, even if wf_local_user_Roles is summarized from it, then end date values would remain null and the problem should not re-occur.

NOTE:
&username = Applications Username
&rolename = Responsibility name

4. R12: Apply latest UMX code levels.

You may try this item solution when users reset their passwords using the "Login Assistance " > "Forgot Password" link, and do not see their responsibilities in home page and get the following message:

There are no active responsibilities available for this user

a. Please download Patch 7507301:R12.UMX.A - PASSWORD RESET VIA LOGIN ASSISTANCE EXPIRING USER - and review the readme and pre-requisites. This patch will bring the latest code level for UMX - User management.

b. Please apply any required pre-requisite patches and Patch 7507301:R12.UMX.A in a Test environment.
This patch will install the following file:
FND: patch/115/sql UMXLHLPB.pls 120.5.12000000.3

c. Please retest the issue.

d. If the issue is resolved, please migrate the solution as appropriate to other environments

5. In 12.2 User Roles and Responsibilities are not Getting Populated While Upgrading from 11i to 12.2.4:

To resolve the issue please perform the resolution steps below:

a.  Please download and review the readme for Patch 20714070: UPGRADE 11I TO 12.2.4 UMXFGAMIGRATIONSCRIPT.SQL FAILS TO INSERT ROLES FOR UMX.

b.  Please apply Patch 20714070 in a Test environment initially before application to any Production instance.

c.  Please retest the issue.

d.  If the issue is resolved, please migrate the solution as appropriate to other environments.

6. A user cannot see any responsibilities after login:

a. Review the 'Applications Start Page' profile option.

b. If it's set to an unknown value, un-set the profile and re-test.

7. The "System Administrator" Responsibility is missing:

Please run the below concurrent programs in the order given, with the respective values of the parameters, and then retest the issue.

a. Workflow Directory services user/role validation with argument values as 10000:Yes:Yes:No:NULL.

b. Run adadmin - Compile/generate menu and rebuild JAR files.

c. Rerun:  FNDLOAD APPS/<apps_password> 0 Y UPLOAD @FND:patch/115/import/afscursp.lct @FND:patch/115/import/US/wfssresp.ldt

Diagnostics & Utilities Community:

• Diagnostics
  For the latest diagnostics, please reference Document 421245.1 E-Business Suite Diagnostics References for R12.

• Utilities Community
  Visit the Utilities community for help from industry experts or to share knowledge.