OracleFree generatorPDF, Excel, Word exportOracle E-Business SuiteAll Oracle E-Business Suite tools

Oracle EBS SQL Query Generator for Concurrent Requests

Most Oracle EBS support work starts with the same few questions: what ran, who ran it, what is still running and what failed. This generator writes the SQL for those questions against fnd_concurrent_requests so you do not rebuild the joins each time.

Fill in

Look back (hours)

Program name contains

Matches the user concurrent program name, case-insensitive.

Requested by (user name)

Phase code

C completed, R running, P pending, I inactive.

Long-running threshold (minutes)

Generated code

Free, optional. Reopen it on any device.

Download:

Recent requests by program, user and phase

SELECT r.request_id,
       p.user_concurrent_program_name AS program_name,
       u.user_name AS requested_by,
       r.phase_code,
       r.status_code,
       r.requested_start_date,
       r.actual_start_date,
       r.actual_completion_date,
       ROUND((NVL(r.actual_completion_date, SYSDATE) - r.actual_start_date) * 24 * 60, 1) AS run_minutes
FROM   apps.fnd_concurrent_requests r
JOIN   apps.fnd_concurrent_programs_vl p
       ON p.concurrent_program_id = r.concurrent_program_id
      AND p.application_id = r.program_application_id
JOIN   apps.fnd_user u ON u.user_id = r.requested_by
WHERE  r.request_date > SYSDATE - (24 / 24)
AND    UPPER(p.user_concurrent_program_name) LIKE UPPER('%Payables%')
AND    u.user_name = UPPER('SYSADMIN')
AND    r.phase_code = 'C'
ORDER BY r.request_id DESC;

Running requests over the time threshold

SELECT r.request_id,
       p.user_concurrent_program_name AS program_name,
       u.user_name AS requested_by,
       r.actual_start_date,
       ROUND((SYSDATE - r.actual_start_date) * 24 * 60, 1) AS running_minutes,
       r.oracle_process_id
FROM   apps.fnd_concurrent_requests r
JOIN   apps.fnd_concurrent_programs_vl p
       ON p.concurrent_program_id = r.concurrent_program_id
      AND p.application_id = r.program_application_id
JOIN   apps.fnd_user u ON u.user_id = r.requested_by
WHERE  r.phase_code = 'R'
AND    r.status_code = 'R'
AND    (SYSDATE - r.actual_start_date) * 24 * 60 > 30
ORDER BY r.actual_start_date;

Error requests with completion text

SELECT r.request_id,
       p.user_concurrent_program_name AS program_name,
       r.actual_completion_date,
       r.completion_text
FROM   apps.fnd_concurrent_requests r
JOIN   apps.fnd_concurrent_programs_vl p
       ON p.concurrent_program_id = r.concurrent_program_id
      AND p.application_id = r.program_application_id
WHERE  r.phase_code = 'C'
AND    r.status_code = 'E'
AND    r.actual_completion_date > SYSDATE - (24 / 24)
ORDER BY r.actual_completion_date DESC;

Run these as a read-only user with access to the APPS views, or in a reporting copy, and start with a short time window on large systems because fnd_concurrent_requests can hold millions of rows. Column names are standard for R12 but confirm on your release. Use the phase and status reference page to read the code values.

Ask your EBS questions in plain English

An optional review of a read-only assistant that shows the SQL it runs against your EBS data.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

How to use the generator

Set the look-back window, a fragment of the program name, the user and the phase, and the three queries update. The first lists requests with run time in minutes, the second finds running requests beyond your threshold, and the third lists errors with completion text. Copy them into SQL Developer or your preferred client. Start narrow and widen the filters, because an unfiltered scan of the requests table can be slow on a busy system.

Tables behind the queries

Request rows live in fnd_concurrent_requests, with the program joined through concurrent_program_id and program_application_id to fnd_concurrent_programs_vl, which holds the user program name. The requester joins to fnd_user through requested_by. Phase_code and status_code are the one-letter codes, and completion_text holds the message for finished requests. The oracle_process_id column gives you the database or OS process link for a running request.

fnd_concurrent_requests: one row per request
fnd_concurrent_programs_vl: program names
fnd_user: who submitted it
phase_code and status_code: current state

Reading the results

Compare run minutes with the normal run time of the same program, not with a global rule, since a payment run and a trial balance report have very different norms. A request in Running for much longer than usual needs a session check in the database, looking at blocking and wait events. Errors need the log file and completion text together. If many requests pile up as Pending, look at manager queues rather than at individual requests.

Join history by program name for a baseline
Look at database sessions for slow runs
Read the log file for every Error
Check manager queues for a pending backlog

Frequently Asked Questions

Which table holds concurrent requests in Oracle EBS?

It is fnd_concurrent_requests. Join it to fnd_concurrent_programs_vl for the readable program name and to fnd_user for the requester. The phase_code and status_code columns hold the current state as one-letter codes.

How do I find long-running concurrent requests?

Query requests with phase R and status R and compare the time since actual_start_date to a threshold. Then compare to the same program's usual run time. The second query in this tool does exactly that and returns the oracle_process_id for a session lookup.

Is it safe to run these queries in production?

They are read-only selects, but a wide scan of a large requests table can load the database. Use a short time window, run off-peak when possible, or use a reporting copy. Never grant broad update access to the APPS schema for this purpose.

Why is my program name filter returning nothing?

The filter matches the user concurrent program name, which is the name shown in the Requests form, not the short name. Try a shorter fragment, and check that the look-back window is long enough and the phase value matches the state you want.

If your team writes these queries daily, we can set up a read-only assistant that turns plain questions into this kind of SQL, shows the query and runs on your own servers.

Talk to an Expert Explore More Free Tools