Query to find Concurrent Requests submitted by a particular user

Query to find Concurrent Requests submitted by a particular User:

SELECT
    request_id,
    request_date,
    user_concurrent_program_name,
    responsibility_name,   
    argument_text,   
    phase_code,
    status_code,
    logfile_name,
    outfile_name,
    output_file_type
FROM
    fnd_concurrent_requests fcr,
    fnd_concurrent_programs_tl fcp,
    fnd_responsibility_tl fr,
    fnd_user fu
WHERE
    fcr.CONCURRENT_PROGRAM_ID = fcp.concurrent_program_id
    and fcr.responsibility_id = fr.responsibility_id
    and fcr.requested_by = fu.user_id
    and user_name = upper('&USERNAME')
ORDER BY REQUEST_DATE DESC;

Query to find runtime of a concurrent program

The following query finds total run-time in minutes for a concurrent program.

SELECT /*+ rule */
       rq.parent_request_id                   "Parent Req. ID",
       rq.request_id                          "Req. ID",
       tl.user_concurrent_program_name        "Program Name",
       rq.actual_start_date                   "Start Date",
       rq.actual_completion_date              "Completion Date",
       ROUND((rq.actual_completion_date -
           rq.actual_start_date) * 1440, 2)   "Runtime (in Minutes)"      
  FROM applsys.fnd_concurrent_programs_tl  tl,
       applsys.fnd_concurrent_requests     rq
 WHERE tl.application_id        = rq.program_application_id
   AND tl.concurrent_program_id = rq.concurrent_program_id
   AND tl.LANGUAGE              = USERENV('LANG')
   AND rq.actual_start_date IS NOT NULL
   AND rq.actual_completion_date IS NOT NULL
   AND tl.user_concurrent_program_name = 'PRC: Generate Draft Revenue for a Range of Projects'  -- <change it>
   -- AND TRUNC(rq.actual_start_date) = '&start_date'  -- uncomment this for a specific date
 ORDER BY rq.request_id DESC;

Find Concurrent Request ID from SID

Find Concurrent Request id from SID:

SELECT s.inst_id,a.request_id,s.sid,s.serial#,c.spid
  FROM apps.fnd_concurrent_requests a, gv$process c, gv$session s
   WHERE s.sid in ('&sid')
  AND s.paddr = c.addr
  AND a.oracle_process_id = c.spid
  AND a.phase_code = UPPER ('R');

Output:

   INST_ID REQUEST_ID        SID    SERIAL# SPID
---------- ---------- ---------- ---------- ------------------------
         1   20602689      15647       2673 5617

Concurrent Program failed with ORACLE error 20100 in FDPSTP

FND_FILE creation failed.

In the concurrent program log file, we see below message.

ORACLE error 20100 in FDPSTP 
 Cause: FDPSTP failed due to ORA-20100: ORA-20100: File o2710130.tmp creation failed. File could not be opened or operated on as requested. 
 Action: Make sure the directory - /applptmp - is a valid directory with write permiss 


Checks for this error:

Option - 1:  Check your applptmp variable

applmgr@oracledemo ~
EBS: echo $APPLPTMP
/applptmp
applmgr@oracledemo ~
EBS:

Option - 2: Go to the applptmp directory and try to create or modify a file with applmgr.

applmgr@oracledemo ~
EBS: touch file
applmgr@oracledemo ~
EBS:

I'm able to create a file, so no issue with this.

Option - 3: Check utl_file_dir parameter in the database.

SQL> show parameter utl_file_dir

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                         string      /applptmp

This variable is pointing to right directory.


Option - 4: Create a test file using FND_FILE.

SQL> exec FND_FILE.PUT_LINE(FND_FILE.LOG, 'THIS IS A TEST');
BEGIN FND_FILE.PUT_LINE(FND_FILE.LOG, 'THIS IS A TEST'); END;

*
ERROR at line 1:
ORA-20100: ORA-20100: File o2711023.tmp creation failed.
File could not be opened or operated on as requested.
Action: Make sure the directory - /applptmp - is a valid directory with write
permissions and is accessible from the database server node for write
operations. This
ORA-06512: at "APPS.FND_FILE", line 319
ORA-06512: at "APPS.FND_FILE", line 364
ORA-06512: at "APPS.FND_FILE", line 421
ORA-06512: at line 1

This has failed and it written the error message clearly, /applptmp is a valid directory with write permissions and is accessible from the database server node for write.

About our environment, we have database on one server and application on another server.  On verification, somehow /applptmp is mounted on AppTier but not on DBTier, hence concurrent requests are failing.  Once we mounted the /applptmp on DBTier all requests got completed successfully.

SQL>exec FND_FILE.PUT_LINE(FND_FILE.LOG, 'THIS IS A TEST');

PL/SQL procedure successfully completed.

Cancel Concurrent Request From Backend

Run this as 'APPS' user:

1) Concurrent Request Status:

select status_code,phase_code from fnd_concurrent_requests where  request_id=&1;  

2) Cancel Concurrent Request:

update fnd_concurrent_requests set status_code='D', phase_code='C' where request_id=&1; 
commit; 

3)  Update Concurrent request as 'Complete Normal'

update fnd_concurrent_requests set status_code='C',phase_code='C' where request_id=&1;

commit;

4)  Cancel All Pending Concurrent Requests

Updating base tables directly is not supported or recommended by Oracle – this update would be OK to execute in a non-production instance. In a production instance, use APIs or cancel the pending requests through the forms interface.

update FND_CONCURRENT_REQUESTS set phase_code='C',status_code='D' where phase_code='P' ;

commit;