Concurrent Manager Troubleshooting : Multiple Concurrent Requests Struck without Processing

Scenario: Concurrent Requests are in Running Normal state for longer time than normal duration of the Program.

Analysis: 

Step 1: Navigate to System Administrator Responsibility : Concurrent Manager --> Administer 

Identify the Concurrent Requests which are in Running Status.

Get the Database Session Details of the currently running concurrent requests using the below Query:

SELECT DISTINCT  a.request_id,C.INST_ID, d.sid, d.serial# ,d.osuser,d.process , c.SPID ,d.inst_id
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
gv$process c,
gv$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.phase_code = 'R' and a.status_coDe='R';



Step 2: Verify if the database sessions are active/Inactive at the database level using the below query.


select inst_id,sid,serial#,program,module,status,last_call_et,sql_id from gv$session where sid=&sid;

-- sid value to be taken from output of Sql Query in Step 1


If Database session is INACTIVE And Running no sql for more than an Hour, we can Terminate the Concurrent Requests.
If the Database session is ACTIVE and has an SQL_ID attached with it, Need to check on tuning the sql being run by the database session.

I will cover more details about SQL Tuning in another post.

Oracle Workflow:How to modify workflow administrator role in Oracle EBS?

By default,Oracle Workflow System administrator role is set to SYSADMIN User in Oracle EBS 11i/R12. 
This means only SYSADMIN user can have access to view Oracle workflow notifications of all the EBS users.
We can modify this to any other EBS user who has administrator rights or assign to a reponsibility.
For Example, If we change Oracle Workflow System administrator to Responsinility name "Workflow Administrator Web Applications", All users 
who are assigned with this responsibility can have access to view workflow details owned by other Oracle applications users.

Let us identify the scenarios which require Workflow System administrator privilege.
- Workflow notification is errored and not processed to next approver
- There is an urgent requirement to delegate the workflow notification to another approver.
- Check the status of workflow notification
- Retry / Rewind the workflow notification
- Check the pending notifications for any Oracle EBS user
- Check Workflow Status Diagram

Steps to modify Workflow system administrator role in Oracle EBS 11i/R12

Oracle Workflow System Administrator can be changed in below ways.

1.update wf_resources set text='&Enter_Admin_Name' where name='WF_ADMIN_ROLE';

e.g
update wf_resources set text='FND_RESP1:20420' where name='WF_ADMIN_ROLE';


2. Change the value of Context file parameter s_wf_admin_role and run Autoconfig

$ cat $CONTEXT_FILE|grep wf_admin
         <username oa_var="s_wf_admin_role" customized="yes">SYSADMIN</username>


3. Change it from Workflow Administrator Web Applications responsibility (Login as sysadmin >> Workflow Administrator Web Applications >> Administration


[Note: Ensure that context file parameter "s_wf_admin_role" is updated with modified value to preserve changes during autoconfig Run]