Submitting BI Publisher Concurrent Program from PL/SQL

Most of us know how to submit a concurrent program from PL/SQL but here is the sample PL/SQL code on how to apply the template/layout to XML Publisher /BI Publsiher Concurrent Program.

For example, we want to submit Open Balance Supplier Letter Data report from PL/SQL and here are the details.

Data Definition Code: APXSOBLX
Application Short Name: AP

Template Code: APXSOBLX_1
Template Name: Supplier Balances Letter

Concurrent Program Name : Supplier Open Balance Letter
Short Name : APXSOBLX

Concurrent Program short name always same as Data Definition Code.

Here is the PL/SQL logic to fire this :

create or replace PACKAGE SAMPLE_CP_BIP_REPORT_PKG AS

PROCEDURE submit_req ( requestId OUT NUMBER, errbuf OUT VARCHAR2, retcode OUT VARCHAR2);

END SAMPLE_CP_BIP_REPORT_PKG ;
/

create or replace

PACKAGE BODY SAMPLE_CP_BIP_REPORT_PKG AS

PROCEDURE submit_req (

requestId OUT NUMBER,

errbuf OUT VARCHAR2,

retcode OUT VARCHAR2)

IS

l_request_id NUMBER;

l_layout BOOLEAN;

l_phase VARCHAR2(50);

l_status VARCHAR2(50);

l_dev_phase VARCHAR2(50);

l_dev_status VARCHAR2(50);

l_message VARCHAR2(100);

l_wait BOOLEAN;

BEGIN

l_layout := FND_REQUEST.ADD_LAYOUT

('AP',

'APXSOBLX_1',

'en',

'US',

'PDF');

IF l_layout THEN

l_request_id := FND_REQUEST.SUBMIT_REQUEST

('AP',

'APXSOBLX',

'Supplier Balances Letter ',

NULL,

FALSE,'','','',

'', '', '', '', '', '', '',

'', '', '', '', '', '', '', '', '', '',

'', '', '', '', '', '', '', '', '', '',

'', '', '', '', '', '', '', '', '', '',

'', '', '', '', '', '', '', '', '', '',

'', '', '', '', '', '', '', '', '', '',

'', '', '', '', '', '', '', '', '', '',

'', '', '', '', '', '', '', '', '', '',

'', '', '', '', '', '', '', '', '', '',

'', '', '', '', '', '', '', '', '', '');

COMMIT;

requestId :=l_request_id;

END IF;

EXCEPTION

WHEN OTHERS

THEN

RETCODE := 2;

ERRBUF := sqlcode||':'||sqlerrm;

--fnd_file.put_line(fnd_file.log, sqlcode||':'||sqlerrm);

END submit_req;

END SAMPLE_CP_BIP_REPORT_PKG ;

/

We are using following two function from the FND_REQUEST package :

1) FND_REQUEST.add_layout (
template_appl_name in varchar2,
template_code in varchar2,
template_language in varchar2,
template_territory in varchar2,
output_format in varchar2) return boolean;

This need to be call before submit request to add the layout to Request. It initiates the post process to apply the Layout.

Arguments
-- Template_APPL_Name - Template Application Short name.
-- Template_code - Template code
-- Template_Language - Template File language (iso value)
-- Template_Territory - Template File Territory (iso value)
-- Output Format - Output Format

2) FND_REQUEST.Submit_Request
submit_request (
application IN varchar2 default NULL,
program IN varchar2 default NULL,
description IN varchar2 default NULL,
start_time IN varchar2 default NULL,
sub_request IN boolean default FALSE,
argument1 IN varchar2 default CHR(0),
argument2 IN varchar2 default CHR(0),
--
--
--
argument100 IN varchar2 default CHR(0))
return number;

This submits the concurrent request and returns the Request ID.

Attributes:
-- application - -Short name of application
-- program - concurrent program short name
-- description - -Optional Program Description
-- start_time - -Optional. Time at which the request has to start
-- - running
-- sub_request - Optional. Set to TRUE if the request is sub request, The default is false.
-- argument1..100 - Optional. Arguments for the concurrent request, Please pass the parameter value define in CP Report definition.

There are other functions available within FND_REQUEST package like add_notification, add_printer. We can use them as per our requirement.