You are here: BI Publisher Submitting BI Publisher Concurrent Program from PL/SQL

Submitting BI Publisher Concurrent Program from PL/SQL

Written by  Raj
Rate this item
(4 votes)

Today one of my consultants shared his problem on submitting the Concurrent Program to run XML Publisher report. They are customizing EBS functionality and it requires submitting the concurrent program programmatically. The request was submitted and completed successfully but the report output was XML instead of PDF and what I found, they were missing one single statement - FND_REQUEST.ADD_LAYOUT () before calling the FND_REQUEST.Submit_Request.

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.

Please feel free to ask your question or provide any suggestion on this article.

Thanks
-Raj

Read 38663 times

2 comments

Leave a comment

Make sure you enter the (*) required information where indicated.
Basic HTML code is allowed.