How to submit a concurrent program from pl sql

How to submit a concurrent program from pl sql

How to submit a concurrent program from backend:

Using FND_REQUEST.SUBMIT_REQUEST function & by passing the required parameters to it we can submit a concurrent program from backend.

But before doing so, we have to set the environment of the user submitting the request.

We have to initialize the following parameters using FND_GLOBAL.APPS_INITIALIZE procedure:

           ·                                 USER_ID

           ·                                 RESPONSIBILITY_ID

           ·                                 RESPONSIBILITY_APPLICATION_ID

Syntax:

FND_GLOBAL.APPS_INITIALIZE:

procedure APPS_INITIALIZE(user_id in number,
                                      resp_id in number,
                                      resp_appl_id in number);

FND_REQUEST.SUBMIT_REQUEST:

REQ_ID := FND_REQUEST.SUBMIT_REQUEST ( application => 'Application Name', program => 'Program Name', description => NULL, start_time => NULL, sub_request => FALSE, argument1 => 1 argument2 => ....argument n );

Where, REQ_ID is the concurrent request ID upon successful completion.

And concurrent request ID returns 0 for any submission problems.

Example:

First get the USER_ID and RESPONSIBILITY_ID by which we have to submit the program:

SELECT USER_ID,

RESPONSIBILITY_ID,

RESPONSIBILITY_APPLICATION_ID,
SECURITY_GROUP_ID

FROM FND_USER_RESP_GROUPS
WHERE USER_ID = (SELECT USER_ID
                             FROM FND_USER
                             WHERE USER_NAME = '&user_name')
AND RESPONSIBILITY_ID = (SELECT RESPONSIBILITY_ID
                                      FROM FND_RESPONSIBILITY_VL
                                      WHERE RESPONSIBILITY_NAME = '&resp_name');


Now create this procedure
CREATE OR REPLACE PROCEDURE APPS.CALL_RACUST (p_return_code OUT NUMBER,
                                                                         p_org_id NUMBER, -- This is required in R12
                                                                         p_return_msg OUT VARCHAR2)

IS

v_request_id VARCHAR2(100) ;
p_create_reciprocal_flag varchar2(1) := 'N'; -- This is value of create reciprocal customer

                                                  -- Accounts parameter, defaulted to N
BEGIN


-- First set the environment of the user submitting the request by submitting
-- Fnd_global.apps_initialize().
-- The procedure requires three parameters
-- Fnd_Global.apps_initialize(userId, responsibilityId, applicationId)
-- Replace the following code with correct value as get from sql above

Fnd_Global.apps_initialize(10081, 5559, 220);

v_request_id := APPS.FND_REQUEST.SUBMIT_REQUEST('AR','RACUST','',
                                                       '',FALSE,p_create_reciprocal_flag,p_org_id,
                                                        chr(0) -- End of parameters);

p_return_msg := 'Request submitted. ID = ' || v_request_id;
p_return_code := 0; commit ;


EXCEPTION

when others then

          p_return_msg := 'Request set submission failed - unknown error: ' || sqlerrm;
          p_return_code := 2;
END;


Output:
DECLARE
V_RETURN_CODE NUMBER;
V_RETURN_MSG VARCHAR2(200);
V_ORG_ID NUMBER := 204;
BEGIN
CALL_RACUST(
P_RETURN_CODE => V_RETURN_CODE,
P_ORG_Id => V_ORG_ID,
P_RETURN_MSG => V_RETURN_MSG
);
DBMS_OUTPUT.PUT_LINE('V_RETURN_CODE = ' || V_RETURN_CODE);
DBMS_OUTPUT.PUT_LINE('V_RETURN_MSG = ' || V_RETURN_MSG);
END;  


If Return Code is 0(zero) then it has submitted the Customer Interface program successfully and the request id will appear in Return Message as :
V_RETURN_CODE = 0
V_RETURN_MSG = Request submitted. ID = 455949