Submit concurrent request using fnd_request.submit_request api from sql/plsql

FND_REQUEST.SUBMIT.REQUEST and FND_CONCURRENT.WAIT_FOR_REQUEST

This is an Oracle standard API used to launch concurrent request on Oracle application from sql/plsql.


Parameters

Application : Short code of the application associated with the concurrent request to be submitted(Required)

Program - Short code of the concurrent program (not the executable) for which the request should be submitted. (Required)

Description - Description of the request that is displayed in the Concurrent Requests form (Optional.) You can pass null.

Start_time - Time at which the request should start running, formatted as HH24:MI or HH24:MI:SS (Optional.) Pass null

Sub_request - Set to TRUE if the request is submitted from another request and should be treated as a sub-request.

Argument1...100 - Arguments for the concurrent request; up to 100 arguments are permitted. If submitted from Oracle Forms, you must specify all 100 arguments.

Example:

declare
request_id number;
begin
 fnd_global.APPS_INITIALIZE (user_id        =>1141 ,
                                  resp_id        => 50678,
                                  resp_appl_id   => 724);
    dbms_output.put_line('APPS INTIALIZE') ;    
    COMMIT;
request_id:=fnd_request.submit_request (
                                  'XBOL' – This is concurrent programs application name
                                 ,'CMCSDPVW'
                                 , NULL
                                 , NULL
                                 , FALSE);
                                 commit;
dbms_output.put_line('request id-'||request_id) ;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('eRROR-'||SQLERRM) ;
end;

User_id , you can derive from table fnd_user by passing user_name
resp_id  and resp_appl_id, you can derive it from table  fnd_responsibility_tl by passing responsibility name

FND_CONCURRENT.WAIT_FOR_REQUEST

You can use FND_CONCURRENT.WAIT_FOR_REQUEST api to wait for completion of above programs.

you can pass request_id which got returned from fnd_request.submit_request  api to this api.

Example:

declare
 v_cr_interval                   number := 60;           -- seconds
        v_cr_max_wait                   number := 0;            -- seconds(0 = Will not time out)
        v_cr_phase_code                 varchar2(30);
        v_cr_status_code                varchar2(30);
        v_cr_dev_phase                  varchar2(30);
        v_cr_dev_status                 varchar2(30);
        v_cr_message                    varchar2(240);
        v_jimport_cr_complete           boolean;
begin

 v_jimport_cr_complete := FND_CONCURRENT.WAIT_FOR_REQUEST(request_id,
                                  v_cr_interval,
                                  v_cr_max_wait,
                                  v_cr_phase_code,
                                  v_cr_status_code,
                                  v_cr_dev_phase,
                                  v_cr_dev_status,
                                  v_cr_message);
            IF  (v_cr_dev_phase = 'COMPLETE' and v_cr_dev_status != 'NORMAL')
            THEN
                raise exception;
            END IF;

end;