Migrating BI Publisher Files: using XDOLoader FNDLOAD

BI Publisher reports are typically made up of several components:

• Metadata that defines the details about the data definition and templates
• BI Publisher files i.e Data Definition Files/RTF Template/Bursting file etc
• RDF files in case of reports based BI Files
• Concurrent programs to run the report.

In Order to move the entire definition of the BI Publisher report from one instance to other you need to move each of the components described above. The tools namely XDOLoader and FNDLOAD are used to migrate these files across instances.

You can use the following commands to download :
• Metadata: 
FNDLOAD apps/****0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct XXJW_ARDLP_NON_SRS.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME=AR DATA_SOURCE_CODE=ARDLP_NON_SRS

• BI Publisher files i.e BI Publisher Data definition template/Bursting File/RTF Template for ARDLP_NON_SRS Data definition:

java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD \
-DB_USERNAME apps \
-DB_PASSWORD ***** \
-JDBC_CONNECTION db_server:db_port:db_sid \
-APPS_SHORT_NAME AR \
-LCT_FILE ${XDO_TOP}/patch/115/import/xdotmpl.lct \
-DS_CODE ARDLP_NON_SRS

• Copy the RFD file if any from the instance directly

• Download the concurrent program using: 
FNDLOAD apps/**** 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_ARDLP_NON_SRS.ldt PROGRAM CONCURRENT_PROGRAM_NAME="ARDLP_NON_SRS"

Once the files have all been downloaded you need to take them to the middle tier of another instance and upload them back. This can be done using the following commands:

• Metadata: 

FNDLOAD apps/****0 Y UPLOAD $XDO_TOP/patch/115/import/xdotmpl.lct XXJW_ARDLP_NON_SRS.ldt

• Upload each of the xml publisher files using commands below:

o Bursting File: 
java oracle.apps.xdo.oa.util.XDOLoader UPLOAD \ -DB_USERNAME apps \ -DB_PASSWORD **** \ -JDBC_CONNECTION db_server:db_port:instance_sid \ -LOB_TYPE BURSTING_FILE \ -APPS_SHORT_NAME AR \ -LOB_CODE ARDLP_NON_SRS \ -LANGUAGE en \ -TERRITORY US \ -NLS_LANG American_America.WE8ISO8859P1 \ -XDO_FILE_TYPE XML-BURSTING-FILE \ -FILE_CONTENT_TYPE 'text/html' \ -FILE_NAME ./xmlpub/BURSTING_FILE_AR_ARDLP_NON_SRS.xml

o RTF Template: 
java oracle.apps.xdo.oa.util.XDOLoader UPLOAD \ -DB_USERNAME apps \ -DB_PASSWORD **** \ -JDBC_CONNECTION db_server:db_port:instance_sid \ -LOB_TYPE TEMPLATE_SOURCE \ -APPS_SHORT_NAME AR \ -LOB_CODE ARDLP_NON_SRS \ -LANGUAGE en \ -TERRITORY US \ -NLS_LANG American_America.WE8ISO8859P1 \ -XDO_FILE_TYPE RTF \ -FILE_CONTENT_TYPE 'text/html' \ -FILE_NAME ./xmlpub/TEMPLATE_SOURCE_AR_ARDLP_NON_SRS_en.rtf

o Data Definition Template:
java oracle.apps.xdo.oa.util.XDOLoader UPLOAD \ -DB_USERNAME apps \ -DB_PASSWORD **** \ -JDBC_CONNECTION :: \ -LOB_TYPE DATA_TEMPLATE \ -APPS_SHORT_NAME AR \ -LOB_CODE ARDLP_NON_SRS \ -LANGUAGE en \ -TERRITORY US \ -NLS_LANG American_America\.WE8ISO8859P1 \ -XDO_FILE_TYPE XML-DATA-TEMPLATE \ -FILE_CONTENT_TYPE 'text/html' \ -FILE_NAME DATA_TEMPLATE.xml

• Copy the RFD file if any to the reports/US folder directly. If it is customised then copy to /reports/US and then create a soft link in /reports/US directory.

• Upload the concurrent program using: 
FNDLOAD apps/**** 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct .ldt

XML Bursting in Oracle Apps

XML Bursting

XML Bursting can be used to split one XML File into multiple XML Blocks. These individual xml blocks can then be used to generate reports and even use different layouts. You can also deliver the reports to multiple destinations based on a XML Element. The steps involved are listed below for your understanding:

• Generate the XML File
• Split the XML into multiple XML Blocks
• Generate Report based on the individual XML Blocks
• Deliver the report

Take a look at the below image and the process will be much more clearer:

Bursting Control File

Bursting control is used to identify:

• How to split the XML file / data
• How to deliver the Report
• Delivery destination details

After defining the XML Bursting Control File, we will upload the same to the Data Definition. But before that, lets take a look at the XML file that we have.

<?xml version="1.0" encoding="UTF-8"?> <INVOICEDATA>   <LIST_G_SUPPLIER>     <G_SUPPLIER>       <SUPPLIER_NUMBER>1001</SUPPLIER_NUMBER>       <SUPPLIER_NAME>VENDOR001</SUPPLIER_NAME>       <SUPPLIER_EMAIL>contact@vendor1.com </SUPPLIER_EMAIL>       <LIST_G_INV>         <G_INV>          <INV_NUMBER>INVOICE_01_001</INV_NUMBER>          <CURRENCY_CODE>USD</CURRENCY_CODE>          <AMOUNT>100.27</AMOUNT>         <G/G_INV>       </LIST_G_INV>     </G_SUPPLIER>     <G_SUPPLIER>       <SUPPLIER_NUMBER>1002</SUPPLIER_NUMBER>       <SUPPLIER_NAME>VENDOR002</SUPPLIER_NAME>       <SUPPLIER_EMAIL>contact@vendor2.com</SUPPLIER_EMAIL>       <LIST_G_INV>         <G_INV>           <INV_NUMBER>INVOICE_02_001</INV_NUMBER>           <CURRENCY_CODE>USD</CURRENCY_CODE>           <AMOUNT>612.99</AMOUNT>         </G_INV>       </LIST_G_INV>     </G_SUPPLIER>   </LIST_G_SUPPLIER> </INVOICEDATA>

As you can see the above XML file has details of two Suppliers (VENDOR001 and VENDOR002). I want the invoice details of VENDOR001 to be emailed to contact@vendor1.com and invoice details of VENDOR002 to be emailed to contact@vendor2.com. In this case, I know that I have to split the XML File into two.

The first thing that we need to do is to identify the Level at which the file has to be split, so that we get two different XML files. If you take a closer look at the XML file, you will see that the <G_SUPPLIER> … </G_SUPPLIER> group is repeated twice. So to get two different XML Files, we will split the actual file at /INVOICEDATA/LIST_G_SUPPLIER/G_SUPPLIER Level. The result is the below XML Files.

We also know that we need to deliver these reports by email. We will use the G_SUPPLIER/SUPPLIER_EMAIL element to get the email id from the XML file. Now let us take a look at the Bursting Control File.

<?xml version="1.0" encoding="utf-8"?>

<xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi">

  <xapi:request select="/INVOICEDATA/LIST_G_SUPPLIER/G_SUPPLIER">

     <xapi:delivery>

       <xapi:email id="${SUPPLIER_NUMBER}" reply-to="ap@quest4apps.com"

                 from="ap@quest4apps.com" port="25" server="ora.q4apps.us">

         <xapi:message subject="Invoice Details" attachment="true"

                       to="${SUPPLIER_EMAIL}" id="${SUPPLIER_NUMBER}">

          Please find your electronically formatted Invoice Details.

         </xapi:message>

       </xapi:email>

     </xapi:delivery>

     <xapi:document delivery="${SUPPLIER_NUMBER}" output-type="pdf"

                    output="/data/test1/interfaces/out/${SUPPLIER_NUMBER}.pdf">

       <xapi:template type="rtf"

                      location="xdo://Q4A.XXQ4ASUPINV.en.00/?getSource=true" />

     </xapi:document>

  </xapi:request>

</xapi:requestset>




Please find below details of the XML tags.

xapi:request: Give details of how you want to split the XML File

xapi:email: Give details of the Email

• id: This is a unique value that identifies each group. In our case it is the Supplier Number.
• reply-to: This is the reply to email address
• from: This is the from email address that will be used while sending the email
• port: The port number of the email server. Check with your DBA / System Administrator for this value.
• server: The details of the email server

xapi:message: Give details of the Email Message

• subject: Subject of the Email
• attachment: We are attaching the report output with the email. So the value should be “true”.
• to: The email id of the Supplier, which is the recipient’s email address.
• id: This is a unique value that identifies each group. In our case it is the Supplier Number.

xapi:document: Here we define the output document details

• output-type: The output type of the report output.
• output: The folder in which the output file will be saved.

xapi:template: Give details of the RTF Template

• type: Give the details of the Template Type. In our case it is RTF
• location: Location of the RTF Template.

Lets take a closer look at the location:

location="xdo://Q4A.XXQ4ASUPINV.en.00/?getSource=true"

For simplicity the above statement can be broken down as

location="xdo://Application Short Name.Template Code.Default Language.Default Territory/?getSource=true"

And you can use the below SQL Statement to get the details of the Template.

select   xtb.application_short_name||'.'||          xtb.template_code ||'.'||          xtb.default_language ||'.'||          xtb.default_territory   from   apps.xdo_templates_b xtb  where   xtb.template_code ='<Template Code>';

Uploading the Bursting Control File

Once the bursting control file has been defined, you need to attach the same to the Data Definition. Query for the Data Definition and open the defintion. And then click on “Add File” button and upload the XML file that we have defined. Save the changes and you are all set to test the process.

Check the below image for more details:

Submitting the Bursting Process

Attaching the XML Bursting Control file will not burst the report just yet. You need to submit the “XML Publisher Report Bursting Program” Concurrent program after the XML Publisher Report is completed. You can either manually Submit the program or use the below SQL Code Snippet in the After Report Trigger to submit the bursting program once the XML Publisher Report is completed.

DECLARE    l_conc_id     NUMBER;    g_request_id  NUMBER; BEGIN    l_conc_id :=    fnd_request.submit_request ( application => 'XDO' ,program => 'XDOBURSTREP' ,description => NULL ,start_time => SYSDATE ,sub_request => FALSE ,argument1 => NULL ,argument2 => g_request_id -– Request ID of XML Publisher Report ,argument3 => 'Y' -– debug Flag );      COMMIT; END;