How to Export Completed Oracle EBS Concurrent Programs by Daily, Weekly, Monthly, Quarterly, Half-Yearly, and Yearly Periods

Oracle E-Business Suite stores concurrent request execution details in the FND_CONCURRENT_REQUESTS table. Using a read-only SQL query, an Apps DBA can extract completed concurrent programs for a selected reporting period and export the results to Excel.

The report includes:

• Request ID
• Application and concurrent program
• Requested-by user
• Request and start times
• Completion time
• Execution duration
• Completion status
• Request arguments
• Oracle session and operating system process IDs
• Log and output file locations

Important distinction

This report returns concurrent requests completed during a selected period.

For example:

• DAILY returns requests completed today.
• WEEKLY returns requests completed during the current ISO week.
• MONTHLY returns requests completed during the current month.
• QUARTERLY returns requests completed during the current quarter.
• HALFYEARLY returns requests completed during the current six-month period.
• YEARLY returns requests completed during the current year.

It does not determine whether a concurrent program is itself scheduled to run daily, weekly, or monthly. Schedule-frequency analysis requires a separate query using request scheduling information and execution history.

Completed Concurrent Requests Query

Run the following query as the Oracle EBS APPS user:

DEFINE p_period = 'DAILY';

WITH period_dates AS
(
    SELECT
        CASE UPPER('&p_period')
            WHEN 'DAILY' THEN
                TRUNC(SYSDATE)
            WHEN 'WEEKLY' THEN
                TRUNC(SYSDATE, 'IW')
            WHEN 'MONTHLY' THEN
                TRUNC(SYSDATE, 'MM')
            WHEN 'QUARTERLY' THEN
                TRUNC(SYSDATE, 'Q')
            WHEN 'HALFYEARLY' THEN
                ADD_MONTHS(
                    TRUNC(SYSDATE, 'YYYY'),
                    CASE
                        WHEN TO_NUMBER(TO_CHAR(SYSDATE, 'MM')) <= 6
                        THEN 0
                        ELSE 6
                    END
                )
            WHEN 'YEARLY' THEN
                TRUNC(SYSDATE, 'YYYY')
        END AS start_date,
        SYSDATE AS end_date
    FROM dual
)
SELECT
    UPPER('&p_period') AS report_period,
    d.start_date AS period_start,
    d.end_date AS period_end,
    r.request_id,
    a.application_name,
    p.concurrent_program_name AS program_short_name,
    p.user_concurrent_program_name,
    u.user_name AS requested_by,
    r.request_date,
    r.requested_start_date,
    r.actual_start_date,
    r.actual_completion_date,
    ROUND(
        (r.actual_completion_date - r.actual_start_date) * 24,
        2
    ) AS elapsed_hours,
    TRUNC(
        (r.actual_completion_date - r.actual_start_date) * 24
    ) || ':' ||
    LPAD(
        TRUNC(
            MOD(
                (r.actual_completion_date - r.actual_start_date) * 1440,
                60
            )
        ),
        2,
        '0'
    ) || ':' ||
    LPAD(
        TRUNC(
            MOD(
                (r.actual_completion_date - r.actual_start_date) * 86400,
                60
            )
        ),
        2,
        '0'
    ) AS elapsed_hh_mm_ss,
    DECODE(
        r.status_code,
        'C', 'Normal',
        'G', 'Warning',
        'E', 'Error',
        'X', 'Terminated',
        'D', 'Cancelled',
        r.status_code
    ) AS completion_status,
    r.argument_text,
    r.oracle_process_id AS os_process_id,
    r.oracle_session_id,
    r.logfile_name,
    r.outfile_name
FROM fnd_concurrent_requests r
JOIN fnd_concurrent_programs_vl p
  ON p.application_id = r.program_application_id
 AND p.concurrent_program_id = r.concurrent_program_id
JOIN fnd_application_vl a
  ON a.application_id = r.program_application_id
JOIN fnd_user u
  ON u.user_id = r.requested_by
CROSS JOIN period_dates d
WHERE r.phase_code = 'C'
  AND r.actual_completion_date >= d.start_date
  AND r.actual_completion_date <= d.end_date
ORDER BY r.actual_completion_date DESC;

Selecting the Reporting Period

Change the value of p_period before executing the query.

Daily report

DEFINE p_period = 'DAILY';

This returns requests completed from midnight today until the current time.

Weekly report

DEFINE p_period = 'WEEKLY';

This uses the ISO week, starting on Monday.

Monthly report

DEFINE p_period = 'MONTHLY';

This returns requests completed from the first day of the current month.

Quarterly report

DEFINE p_period = 'QUARTERLY';

This returns requests completed from the beginning of the current calendar quarter.

Half-yearly report

DEFINE p_period = 'HALFYEARLY';

The reporting periods are:

• January through June
• July through December

Yearly report

DEFINE p_period = 'YEARLY';

This returns requests completed from January 1 of the current year.

Completion Status Filters

The condition below includes every request whose phase is Completed:

WHERE r.phase_code = 'C'

A completed phase can contain requests that ended with Normal, Warning, Error, Terminated, or Cancelled status.

Successfully completed requests only

Add the following condition:

AND r.status_code = 'C'

Normal, Warning, and Error requests

Use:

AND r.status_code IN ('C', 'G', 'E')

Error requests only

Use:

AND r.status_code = 'E'

Warning requests only

Use:

AND r.status_code = 'G'

Understanding the Execution Duration

The report provides duration in two formats.

Decimal hours

The ELAPSED_HOURS column displays the runtime in hours:

2.50

This represents two hours and thirty minutes.

Hours, minutes, and seconds

The ELAPSED_HH_MM_SS column displays duration as:

02:30:00

This format is useful when reviewing long-running concurrent requests.

Exporting the Results to Excel

In Oracle SQL Developer:

1. Execute the query.
2. Right-click anywhere in the query result grid.
3. Select Export.
4. Select Excel 2007+ (.xlsx) as the output format.
5. Enable Include Column Headers.
6. Select the destination file.
7. Click Next, followed by Finish.

The results can be maintained in separate Excel worksheets:

• Daily
• Weekly
• Monthly
• Quarterly
• Half-Yearly
• Yearly

Production Safety

The query is read-only and does not update Oracle EBS data.

However, yearly reports may retrieve a large number of records from a busy production environment. Consider the following precautions:

• Run large reports outside peak business hours.
• Test the query in a non-production environment first.
• Use a specific date range when the request history is extensive.
• Avoid opening millions of rows directly in Excel.
• Export large results to CSV when necessary.

An Excel worksheet supports a maximum of 1,048,576 rows. If the report exceeds this limit, use CSV files or divide the report into smaller date ranges.

Conclusion

This query provides a production-safe method to extract completed Oracle EBS concurrent requests for daily, weekly, monthly, quarterly, half-yearly, and yearly reporting periods. The output can be exported directly to Excel for operational reporting, performance analysis, audit review, and identification of long-running or failed concurrent requests.