OracleFree generatorPDF, Excel, Word exportOracle E-Business Suite

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

Matches the user concurrent program name, case-insensitive.

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

Generated code
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.

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 ExpertExplore More Free Tools

Related Tools

Oracle

Oracle EBS Concurrent Request Phase and Status Reference

Look up every common phase and status combination for a concurrent request, with what it means and the first thing to check.

Oracle

Oracle EBS AP Invoice Interface Template

Fill in header and line values to get insert statements for the Payables open interface tables, ready to adapt for your conversion or integration.

Oracle

Oracle EBS Patching Checklist for adop

Step through a full adop patching cycle with pre-checks, the five phases and post-patch validation.

More Oracle tools

See all
Back to All Tools