How To Maintain Oracle EBS Concurrent Programs Run History

Table of Contents

In this Post

We’ll be exploring how to Implement and Maintain Oracle EBS Concurrent Programs Run History.

In general, DBA’s schedule concurrent programs to purge these concurrent program details to avoid any performance issues on a business agreed time frame.

Several instances have occurred after a patching or an upgrade user says, “This concurrent was running quickly before !!”, “How Quickly ??” and we struggle to verify the details which parameter was passed, concurrent run frequencies, concurrent run timings, etc.,

To overcome these situations, A Custom Solution to maintain concurrent program run history is important. Let’s explore…

Disclaimer: The code and examples in this post are provided for educational purposes only. I make no guarantees about accuracy, completeness or fitness for a specific purpose and you should test and review any code before using it in production.

Maintaining Oracle EBS Concurrent Programs Run History

Details are maintained in Custom Schema for avoiding any conflicts with the seeded objects.

Custom Schema Name: XXCUST

Create History Table

Create a Table to capture the History details of Concurrent Programs


CREATE TABLE xxcust.xxcust_concurrent_history (
	application_id	   			NUMBER,
	application_short_name		VARCHAR2(50),
    request_id         			NUMBER PRIMARY KEY,
    program_name       			VARCHAR2(240),
	argument_text	   			VARCHAR2(240),
    user_name          			VARCHAR2(100),
    responsibility     			VARCHAR2(240),
    start_time         			DATE,
    end_time           			DATE,
    status             			VARCHAR2(30),
    phase              			VARCHAR2(30),
    completion_text    			VARCHAR2(240),
    log_file_name      			VARCHAR2(240),
    output_file_name   			VARCHAR2(240),
    creation_date      			DATE DEFAULT SYSDATE
);

Create Indexes

Created indexes to avoid performance issues why fetching the details from the history table.


-- Index on request_id for quick lookups
CREATE INDEX xxcust.xxconc_hist_reqid_idx ON xxcust.xxcust_concurrent_history (request_id);

-- Index on start_time for date-based and program_name filtering
CREATE INDEX xxcust.xxconc_hist_details_idx ON xxcust.xxcust_concurrent_history (start_time, program_name);

Verify the Index Creation

SELECT ui.index_name, 
       ui.table_name, 
       uic.column_name, 
       ui.uniqueness
FROM   user_indexes ui
       JOIN user_ind_columns uic 
       ON ui.index_name = uic.index_name
WHERE  ui.table_name = 'xxcust_concurrent_history'
ORDER BY ui.index_name, uic.column_position;

Identify Source Tables

Use the following Oracle EBS tables and specific columns to extract concurrent request data.

FND_CONCURRENT_REQUESTS
FND_CONCURRENT_PROGRAMS_TL
FND_USER
FND_RESPONSIBILITY_TL
FND_APPLICATION

select * from FND_CONCURRENT_REQUESTS;

select * from FND_CONCURRENT_PROGRAMS_TL;

select * from FND_USER;

select * from FND_RESPONSIBILITY_TL;

select * from FND_APPLICATION;

Create a Tracking Table

This table stores the last processed request_id, so that from that the next request_id will be picked for adding the details to the History table.


CREATE TABLE xxcust.xxcust_conc_hist_tracker (last_request_id NUMBER);

INSERT INTO xxcust.xxcust_conc_hist_tracker (last_request_id) VALUES (0);

COMMIT;

Create Procedure for Incremental Load


CREATE OR REPLACE PROCEDURE xxcust_log_concurrent_history_prc IS
    l_last_request_id NUMBER;
BEGIN
    -- Get the last processed request ID
    SELECT last_request_id INTO l_last_request_id FROM xxcust.xxcust_conc_hist_tracker;

    -- Insert new records only
    INSERT INTO xxcust.xxcust_concurrent_history (
		application_id,
		application_short_name,
        request_id,
        program_name,
		argument_text,
        user_name,
        responsibility,
        start_time,
        end_time,
        status,
        phase,
        completion_text,
        log_file_name,
        output_file_name,
        creation_date
    )
    SELECT  
        p.application_id,
        fp.application_short_name,
        r.request_id, 
        p.user_concurrent_program_name,
		r.argument_text,
        u.user_name,
        rt.responsibility_name,
        r.actual_start_date,
        r.actual_completion_date,
        r.status_code,
        r.phase_code,
        r.completion_text,
        r.logfile_name,
        r.outfile_name,
        SYSDATE 
	from
		fnd_concurrent_requests r,
		fnd_concurrent_programs_tl p,
		fnd_user u,
		fnd_responsibility_tl rt,
		fnd_application fp
	where 
		r.concurrent_program_id = p.concurrent_program_id 
	and r.program_application_id = p.application_id
	and r.requested_by = u.user_id
	and r.responsibility_id = rt.responsibility_id
	and fp.application_id = p.application_id
	and p.language = 'US'
	and rt.language = 'US'
    and r.request_id > l_last_request_id;

    -- Update the tracker with the new max request ID
    UPDATE xxcust.xxcust_conc_hist_tracker
    SET last_request_id = (SELECT NVL(MAX(request_id), l_last_request_id) FROM fnd_concurrent_requests);

    COMMIT;
END;
/

Schedule the Procedure

From EBS Apps schema run the below statement to schedule.


BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'XXCUST_LOG_CONCURRENT_HISTORY_JOB',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'XXCUST_LOG_CONCURRENT_HISTORY',  -- Your procedure name
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY;BYHOUR=6',     -- Adjust as needed
    enabled         => TRUE,
    comments        => 'Job to log concurrent request history daily'
  );
END;
/

The repeat_interval uses calendaring syntax.

For example:

‘FREQ=DAILY;BYHOUR=2’ → runs daily at 2 AM

‘FREQ=MINUTELY;INTERVAL=30’ → runs every 30 minutes

Enable the Job (if not already enabled)

BEGIN
DBMS_SCHEDULER.ENABLE('XXLOG_CONCURRENT_HISTORY_JOB');
END;
/

Disable and Drop the Job (if not required)


--To Disable the Job

EXEC DBMS_SCHEDULER.DISABLE('XXCUST_LOG_CONCURRENT_HISTORY_JOB');

--To Drop the Job

EXEC DBMS_SCHEDULER.DROP_JOB('XXCUST_LOG_CONCURRENT_HISTORY_JOB');

Monitor the Job

Check job status and run history.


SELECT job_name, state, run_count, last_start_date, next_run_date
FROM dba_scheduler_jobs
WHERE job_name = 'XXCUST_LOG_CONCURRENT_HISTORY_JOB';

To view job run logs

SELECT * FROM dba_scheduler_job_run_details
WHERE job_name = 'XXCUST_LOG_CONCURRENT_HISTORY_JOB'
ORDER BY log_date DESC;

Verify the Concurrent Programs History

To view the history after the above steps has been completed, the history table will hold the concurrent program run details. This can be maintained for a longer time and cleared as required.


select * from xxcust.xxcust_concurrent_history;

End of Post

Thanks for taking the time to read this post.
Stay tuned for the next post coming soon. . .