Submitting Concurrent Program From Back end / PL SQL

Pre-requisites :
Step1: Data Definition and Template to be created
Step2: Concurrent program needs to be created

Steps To Create the PL/SQL package:

1. Initialize the  Session Specific variable using fnd_global.APPS_INITIALIZE
2. Set The BI publisher report layout Before submitting the concurrent program
3. Submit the Concurrent Program

Code: (Tested in R12.1.1 )

DECLARE
l_user_id              fnd_user.user_id%TYPE;
l_resp_id              fnd_responsibility.responsibility_id%TYPE;
l_resp_appl_id         fnd_application.application_id%TYPE;
l_set_layout           boolean;
l_request_id           NUMBER;
l_phase                VARCHAR2 (100);
l_status               VARCHAR2 (100);
l_dev_phase            VARCHAR2 (100);
l_dev_status           VARCHAR2 (100);
l_wait_for_request     boolean := FALSE;
l_get_request_status   boolean := FALSE;
Output_layout_failed EXCEPTION;
request_submission_failed EXCEPTION;
request_completion_abnormal EXCEPTION;
BEGIN
l_request_id := NULL;

—
— Get the Apps Intilization Variables
—
SELECT   fnd.user_id, fresp.responsibility_id, fresp.application_id
INTO   l_user_id, l_resp_id, l_resp_appl_id
FROM   fnd_user fnd, fnd_responsibility_tl fresp
WHERE   fnd.user_name = ‘OEAG’
AND fresp.responsibility_name = ‘Custom XML Reports’;

—
–Initialize the Apps Variables
—
fnd_global.APPS_INITIALIZE (user_id        => l_user_id,
resp_id        => l_resp_id,
resp_appl_id   => l_resp_appl_id);

COMMIT;

—
— Set the Layout  for BI Publisher Report
—

l_set_layout :=
fnd_request.add_layout (template_appl_name   => ‘XXERP’,
template_code        => ‘XXORACLEERPAPPSGUIDE’,
–Data Template Code
template_language    => ‘en’,
template_territory   => ‘US’,
output_format        => ‘PDF’);

IF l_set_layout
THEN
— Submit the Request

l_request_id :=
fnd_request.submit_request (application   => ‘XXERP’,
program       => ‘XXOEAG_PG’,
description   => ”,
start_time    => SYSDATE,
sub_request   => FALSE,
argument1     => l_person_id);

COMMIT;

IF l_request_id > 0
THEN
—
–waits for the request completion
—

l_wait_for_request :=
fnd_concurrent.wait_for_request (request_id   => l_request_id,
interval     => 60,
max_wait     => 0,
phase        => l_phase,
status       => l_status,
dev_phase    => l_dev_phase,
dev_status   => l_dev_status,
MESSAGE      => l_messase);

COMMIT;

—
— Get the Request Completion Status.
—
l_get_request_status :=
fnd_concurrent.get_request_status (
request_id       => l_request_id,
appl_shortname   => NULL,
program          => NULL,
phase            => l_phase,
status           => l_status,
dev_phase        => l_dev_phase,
dev_status       => l_dev_status,
MESSAGE          => l_messase
);

—
–Check the status if It IS completed Normal Or Not
—
IF UPPER (l_dev_phase) != ‘COMPLETED’
AND UPPER (l_dev_status) != ‘NORMAL’
THEN
RAISE request_completion_abnormal;
END IF;
ELSE
RAISE request_submission_failed;
END IF;
ELSE
RAISE Output_layout_failed;
END IF;

p_request_id := l_request_id;
EXCEPTION
WHEN Output_layout_failed
THEN
DBMS_OUTPUT.put_line (‘Out put Layout failed’);
WHEN request_submission_failed
THEN
DBMS_OUTPUT.put_line (‘Concurrent request submission failed’);
WHEN request_completion_abnormal
THEN
DBMS_OUTPUT.put_line (
‘Submitted request completed with error’ || l_request_id
);
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (‘ERROR:’ || SUBSTR (SQLERRM, 0, 240));
END;

/

You can Create this as PL/SQL Procedure and register into Concurrent Program also.