Calling concurrent programs from BPEL

When you are integrating some system with Oracle Apps, and especially if it involves an inbound data movement into Oracle, you will often find a situation where your BPEL process needs to call a concurrent program in Apps. Although, the the Oracle Apps Adapter supports this functionality, you'd rather write your own procedure. The reason is when you call the Apps adapter, it merely calls the concurrent program and exits. Usually, you don't want that to happen, but rather wait till the concurrent program has finished execution. Assume that somewhere later in your BPEL process you want to access some of the base tables (populated by the concurrent program), but since the BPEL goes ahead with its execution you end up looking into those tables when the concurrent program is still running and data has not been populated properly yet. Ofcourse, you can add a wait activity, but then you will have to set the wait time manually because there isn't a way to know when the program will have finished. In this post I am going to show you how to handle such situations.


For calling concurrent programs through PL/SQL Oracle provides a procedure submit_request. This procedure can be found under the fnd_request package. For calling a concurrent program you can directly call this procedure, however the 'wait' problem would persist. So, we are going to create a wrapper around this procedure and call this wrapper instead. This procedure takes in excess of 100 parameters, but the good thing is you need not worry about all of them. Pass a few mandatory ones and you are through. Here are the parameters it takes as input

• application: Short name of application under which the program is registered
• program: concurrent program name for which the request has to be submitted
• description(Optional): Will be displayed along with user concurrent program name
• start_time(Optional): Time at which the request has to start running
• sub_request(Optional): Set to TRUE if the request is submitted from another running request and has to be treated as a sub request default is FALSE
• argument1..100(Optional): Arguments for the concurrent request

Here is the code

PROCEDURE submit_journal_import(
p_source IN VARCHAR2,
p_post_errors_to_suspense IN VARCHAR2 DEFAULT 'N',
p_start_date IN VARCHAR2,
p_end_date IN VARCHAR2,
p_create_summary_journals IN VARCHAR2 DEFAULT 'N',
p_import_desc_flexfields IN VARCHAR2 DEFAULT 'N',
p_run_id IN NUMBER,
x_req_id OUT NUMBER
)
AS
v_run_id INTEGER;
v_req_id INTEGER;
v_phase_code CHAR := 'R';
BEGIN

/*Set appropriate values for user_id, responsibility_id and application_id. These must exist in the apps.fnd_user and apps.fnd_responsbility_tl*/
apps.fnd_global.apps_initialize(0, 20420, 1);
x_req_id := apps.fnd_request.submit_request('SQLGL', 'GLLEZL', 'Journal Import - ' || _source, NULL, FALSE, to_char(p_run_id),
'2001', p_post_errors_to_suspense,
p_start_date, p_end_date, p_create_summary_journals,
p_import_desc_flexfields
);

DBMS_OUTPUT.PUT_LINE('after');
COMMIT;

DBMS_OUTPUT.PUT_LINE('The request id is' || to_char(x_req_id));
/*
Wait for the concurrent program to finish
*/
WHILE(v_phase_code <> 'C')
LOOP
SELECT phase_code
INTO v_phase_code
FROM apps.fnd_concurrent_requests
WHERE request_id = x_req_id;
END LOOP;

END submit_journal_import;

Observe that we have passed the appropriate values into the submit_request procedure. For more information on these refer the Oracle Applications documentation. But, before you can call this procedure you need to set up the Apps environment. This is done by calling the apps.fnd_global.apps_initialize procedure. This again takes three parameters

• user_id
• responsibility_id
• responsibility_application_id

These values can be found in apps.fnd_user and apps.fnd_responsbility_tl tables.

Also, notice that we have used a while loop that keeps looping over till the program has finished executing. Only when the program has completed, the procedure is exited.