Troubleshooting Missing Responsibilities For A User
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:
SQL> 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');
2. Ensure Deferred Workflow Events are being processed.
Run the following query to check for Deferred Workflow Events:
SQL> 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 ;
SQL> select notification_preference,email_address from applsys.wf_local_roles where name = 'XYZ';
SQL> update wf_local_roles set email_address='svreddy@singiri.com' where name='SYSADMIN';
commit;
If below SQL statement is returning rows you need to correct the email addresses for associated users:
col name format a25
col email_address format a35
SQL> select name, '['||substr(email_address, 1, 25)||']', orig_system from wf_local_roles where email_address like '% %';
SQL> select corrid, count(*) from WF_DEFERRED group by corrid;
Why the Workflow Mailer Response Time Is Slow For Email Notifications?
The Workflow Mailer is sending email notifications very slowly.
Cause :
java.io.InterruptedIOException: timeout in stream read)
Internal Bug 4064890 RT FAILLING TO SEND FRAMEWORK MESSAGE - JAVA.IO.INTERRUPTEDIOEXCEPTION: TIMEOUT
Solution
1. Increase the value of FRAMEWORK_URL_TIMEOUT mailer parameter using $FND_TOP/sql/afsvcpup.sql to 120 or 180 secs. By default, it should be 30 secs.
2. Bounce the Workflow Mailer component and the Apache server.
3. Test the issue again to see if the time out error still occurs.
Q) Which Table Stores the NEW Test Address 'set override address'?
Solution
Run the following select to reflect the associated tables:
SQL> select fscpv.parameter_value
from fnd_svc_comp_params_tl fscpt
,fnd_svc_comp_param_vals fscpv
where fscpt.display_name = 'Test Address'
and fscpt.parameter_id = fscpv.parameter_id;
SQL> set pagesize 100
set linesize 132
set feedback off
set verify off
col value format a35
col component_name format a30
select c.component_id, c.component_name, p.parameter_id, p.parameter_name, v.parameter_value value
from fnd_svc_comp_param_vals_v v, fnd_svc_comp_params_b p, fnd_svc_components c
where c.component_type = 'WF_MAILER'
and v.component_id = c.component_id
and v.parameter_id = p.parameter_id
and p.parameter_name in ( 'INBOUND_SERVER','ACCOUNT', 'REPLYTO')
order by c.component_id, c.component_name,p.parameter_name;
Make sure that Workflow Mailer account is not monitored using an email client while Workflow Notification Mailer is up and running. Folders of this account should be monitored only having the Workflow Notification Mailer stopped to prevent locking it.
E. Make sure that "Processor Close on Read Timeout" and "Expunge Inbox on Close" parameter is checked (In OAM --> Navigate to Workflow Manager --> Notification Mailers --> Edit --> Advanced --> Step 2)
To check current value you may execute below SQL statement and the value field should be 'Y':
SQL> set pagesize 100
set linesize 132
set feedback off
set verify off
col value format a35
col component_name format a30
select c.component_id, c.component_name, p.parameter_id, p.parameter_name, v.parameter_value value
from fnd_svc_comp_param_vals_v v, fnd_svc_comp_params_b p, fnd_svc_components c
where c.component_type = 'WF_MAILER'
and v.component_id = c.component_id
and v.parameter_id = p.parameter_id
and p.parameter_name = 'PROCESSOR_READ_TIMEOUT_CLOSE'
order by c.component_name,p.parameter_name;
note:
http://docs.oracle.com/cd/E18727_01/doc.121/e12903/T319398T319402.htm#T319579
Doc ID 562551.1, 429852.1 ,554352.1,741352.1,433280.1
++++++++++++++++++++++++++++++++++++++++
How to mass change Mail Preferences for FND USERS (Doc ID 451929.1)
Direct table updates are not supported. Please ensure to perform a backup prior to making any changes reflected in this document.
Using sqlplus as the apps user:
Step 1). SQL> update wf_local_roles set notification_preference = 'MAILHTML' where user_flag='Y';
Step 2). SQL > update fnd_user_preferences set preference_value = 'MAILHTML' where module_name = 'WF' and preference_name = 'MAILTYPE';
Step 3). SQL> commit
Email Notification Is Not Sent To The Approver (Doc ID 314320.1)
SQL> Select name, orig_system, email_address from wf_users where NAME like '%JAIN%';
SQL> select orig_system,orig_system_id,name,display_name from wf_local_roles wfr1 where (wfr1.orig_system <> 'WF_LOCAL_ROLES' and wfr1.orig_system_id <> 0)
and exists ( select 'row found' from wf_local_roles wfr2 where (wfr2.orig_system <> 'WF_LOCAL_ROLES' and wfr2.orig_system_id <> 0) and wfr2.orig_system_id = wfr1.orig_system_id and wfr2.orig_system = wfr1.orig_system and wfr2.name <> wfr1.name)
and orig_system = 'PER'order by orig_system,orig_system_id;
To implement the solution, please execute the following steps:
1. Navigate to the Define User form
Responsibility: System Administrator
Navigation: Security -> User -> Define
2. Enter Query mode on the form (F11)
3. Click in the Person field and enter the person's name (Employee Name)
for the individual not receiving notifications.
4. Execute the query (CTRL-F11)
At least one record should be returned at this point; once data appears on the form, hit the down arrow. If the screen moves to a new record showing login information, there is in fact a duplicate login issue.
To resolve:
- Enter an End Date for each of the duplicate logins, so only one login remains for the employee - this should be the login which will be receiving all notifications.
or
- Delete the person's name (Employee Name) from the Person field
- save the record so the link between the employee and invalid login is removed.
5. Run the Synchronize WF Local Tables request set to update the role information into the
workflow tables
+++++++++++++++++++++++++++++
Notify Function When Running Concurrent Request Not Sending Notification to User (Doc ID 1370390.1)
1. From the request log, verify the notification id has been generated and make a note of the id.
2. Check that the notification mail_status is "MAIL", using the following select statement, which means the notification is in processing.
SQL> select NOTIFICATION_ID, STATUS, MAIL_STATUS from wf_notifications where NOTIFICATION_ID = '423828';
3. Using the script identified below together with the notification id to check the notification detail information:
SQL> sqlplus apps/apps @$FND_TOP/sql/wfmlrdbg.sql <insert notification id here>
This will identify a missing vaild email address while sending notification.
4. Using following select to find if the user has been defined with a valid email address:
SQL> select user_name, email_address from fnd_user where user_name = '<USER NAME>';
5. Verify the email address is not missing in view wf_roles as following:
SQL> select display_name, email_address from wf_roles where display_name = '<USER NAME>';
6. Manually update table wf_local_roles to set the user a valid email address:
SQL> update WF_LOCAL_ROLES set email_address = '<email address>'where display_name = '<USER NAME>';
SQL> commit;
Note:
Once conformation by user & Run concurrent program 'Synchronize WF LOCAL tables' with parameter orig_system = 'ALL'
7. User should now receive the notification normally.
+++++++++++++++Manjunatha++++++++++++++++
