The XML page cannot be displayed

Error –  Program completed in error and output shows below

Resolution -  Please check your data definition XML file (data template). Some issue with XML tags.

Correct data template and check again!!

Print parametr in XML report without having XML tags

Oracle provides option to print input parameter directly in RTF without generating a XML tag for this. This can be done in below 2 steps,

Define the parameter reference: this can be done in two different ways

<?param@begin:P_PARAM?>

<xsl:param name="P_PARAM" xdofo:ctx="begin"/>

Print the report parameter value:

<?$P_PARAM?>

Example

CP Parameter Name

START_TIME

END_TIME

Invoke parameters on rtf

<?param@begin:START_TIME?>

<?param@begin:END_TIME?>

Create 2 form field (Placeholder) in RTF  with below values

<?$START_TIME?>

<?$END_TIME?>

Warning !!! Due to high volume of data, got out of memory exception - XML Memory Error

 

Issue – Memory  Exception while generating XML publisher report

 

 

Resolution 1 -  Give the below shown option in CP definition –

 

 

 

 

Resolution 2 - Set the scalable option in data template (XML Publisher >> Data Definition >> Data Template. Xml )

<properties>

   <property name="scalable_mode" value="on" />

</properties>

 

 
 

Java.io.FileNotFoundException - Bursting Error

 

Bursting Error –

 

Set Bursting parameters..

Temp. Directory:/ora/integration_xxx/ebs/xxx

[070814_031305645][][STATEMENT] Oracle XML Parser version ::: Oracle XML Developers Kit 10.1.3.130 - Production

[070814_031305647][][STATEMENT] setOAProperties called..

Bursting propertes.....

{user-variable:cp:territory=AU, user-variable:cp:ReportRequestID=1000101, user-variable:cp:language=en, user-variable:cp:responsibility=2000, user-variable.OA_MEDIA=http://oracle.xxxx.local:2010/OA_MEDIA, burstng-source=EBS, user-variable:cp:DebugFlag=Y, user-variable:cp:parent_request_id=1000101, user-variable:cp:locale=en-AU, user-variable:cp:user=SANDEEP, user-variable:cp:application_short_name=XDO, user-variable:cp:request_id=1000101, user-variable:cp:org_id=30, user-variable:cp:reportdescription=Bursting Error Report, user-variable:cp:Dummy for Data Security=N}

Start bursting process..

Bursting process complete..

Generating Bursting Status Report..

--Exception

/ora/integration_xxx/ebs/xxx/070814_031305694/XXXXREPORTNAME.xls (No such file or directory)

java.io.FileNotFoundException: /ora/integration_xxx/ebs/xxx/070814_031305694/XXXXREPORTNAME.xls (No such file or directory)

                    at java.io.FileInputStream.open(Native Method)

                    at java.io.FileInputStream.<init>(FileInputStream.java:106)

                    at java.io.FileInputStream.<init>(FileInputStream.java:66)

                    at oracle.apps.xdo.oa.cp.JCP4XDOBurstingEngine.zipOutputFiles(JCP4XDOBurstingEngine.java:523)

                    at oracle.apps.xdo.oa.cp.JCP4XDOBurstingEngine.runProgram(JCP4XDOBurstingEngine.java:292)

                    at oracle.apps.fnd.cp.request.Run.main(Run.java:157)

 

Resolution -

Check the uploaded template properties. Either use correct territory or leave this field as blank (this is not mandatory)

Keeping territory blank is advisable.

 

In case its already created and delete icon is disabled. Use below query to delete the template file from backend.

 

 

 
 

SQL to find XML Files (RTF/Data Template/RTF ) Details

Given SQL is to fetch the files details uploaded through XML Publisher Administrator Responsibility

 

SELECT

 a.application_short_name,

  a.data_source_code,

  b.lob_code,

  b.lob_type,

  b.file_name,

  b.last_update_date

FROM

   XDO_DS_DEFINITIONS_VL a,

   XDO_LOBS b

WHERE a.data_source_code    =b.lob_code

AND a.application_short_name=’XXXXX’

AND a.data_source_code      =:ConCurrentProgram or Data Defintion Code;

;

 
 

Import data Template/RTF/Bursting control files

Problem is that any custom XML publisher report requires dependency on application access in PROD (XML Publisher Administrator (R)). All supporting files for XML report are being uploaded through application.

 

Requirement is to have any import method for XML publisher supporting files i.e.

Data Template file (XML file)

Bursting Control File (XML file)

Template (RTF file)

 

Resolution

XDOLoader is the standard OA utility to suffice this requirement. This is similar to unix based FNDLOAD utility and should be run from server with all parameters.  

 

Syntex

java oracle.apps.xdo.oa.util.XDOLoader  

MODE                                                                 UPLOAD/DOWNLOAD

–DB_USERNAME                                             apps user name

–DB_PASSOWRD                                             apps password

–JDBC_CONNECTION                                     jdbc connection string

–LOB_TYPE                                                       TEMPLATE_SOURCE/   
                                                                              DATA_TEMPLATE/
                                                                              BURSTING_FILE             

–APPS_SHORT_NAME                                     xxxxx

–LOB_CODE                                                       Data definition Code

–LANGUAGE                                                      en

–XDO_FILE_TYPE                                             RTF/XML-DATA-TEMPLATE
                                                                              /XML-BURSTING-FILE

[–CUSTOM_MODE]                                            FORCE

[–LOG_FILE]                                                       log file name

[-DEBUG]                                                             true/false

 

 

RTF File >>

java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME apps -DB_PASSWORD appsPWD -JDBC_CONNECTION  "(DESCRIPTION= (ADDRESS=(

PROTOCOL=TCP)(Host=XXXXXXXXXX)(Port=100))(CONNECT_DATA=(SID=EBS123)))"  -LOB_TYPE TEMPLATE_SOURCE -APPS_SHORT_NAME XXXXX  -LOB_CODE DATA_DEFINITION_CODE -LANGUAGE en  -XDO_FILE_TYPE RTF -FILE_NAME 123.rtf -CUSTOM_MODE FORCE

 

 

 

Data Template File >>

java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME apps -DB_PASSWORD appsPWD -JDBC_CONNECTION  "(DESCRIPTION= (ADDRESS=(

PROTOCOL=TCP)(Host=XXXXXXXXXX)(Port=100))(CONNECT_DATA=(SID=EBS123)))"  -LOB_TYPE DATA_TEMPLATE -APPS_SHORT_NAME XXXXX  -LOB_CODE  DATA_DEFINITION_CODE -LANGUAGE en  -XDO_FILE_TYPE XML-DATA-TEMPLATE -FILE_NAME 123.xml -CUSTOM_MODE FORCE

 

 

Bursting File >>

java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME apps -DB_PASSWORD appsPWD -JDBC_CONNECTION  "(DESCRIPTION= (ADDRESS=(

PROTOCOL=TCP)(Host=XXXXXXXXXX)(Port=100))(CONNECT_DATA=(SID=EBS123)))"  -LOB_TYPE BURSTING_FILE -APPS_SHORT_NAME XXXXX  -LOB_CODE  DATA_DEFINITION_CODE -LANGUAGE en  -XDO_FILE_TYPE XML-BURSTING-FILE -FILE_NAME 123b.xml -CUSTOM_MODE FORCE

 

**Highlighted are user defined inputs

 

Multiple Queries in Data Templete

Problem – Oracle report builder provides flexibility to create multiple queries and join those with common attribute. How does data template method help us to use multiple queries?

Solution – Data template also provides same flexibilities to list down queries in multiple sections and connect them as parent child relationship.

There are two approached available to join multiple queries in data template.

•        Bind variable
•        Data link method

Bind Variable most efficient and faster option. Identify the common column in parent query and use it as bind variable in child queries.

<dataQuery>

<sqlstatement name="Q1_USER">

<![CDATA[

SELECT user_id BIND_USER_ID ,user_name From fnd_user

]]>

</sqlstatement>

<sqlstatement name="Q2_RESP">

<![CDATA[

SELECT responsibility_id  From fnd_user_resp_groups WHERE user_id=: BIND_USER_ID

]]>

</sqlstatement>

</dataQuery>

Data Link is another way to establish the relationship between multiple queries

<link name="USER_RESP_LINK" parentQuery="Q1_USER" parentColumn="user_id" childQuery="Q2_RESP" childColumn="user_id" condition="Q1_USER.user_id= Q2_RESP.user_id" />

XML Output

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

< XXXOALEARNING >

<P_CUSTOMER/>100 </P_CUSTOMER/>

<LIST_G_USER>

<G_USER>

  <USERID>100</USERID>

  <USERNAME>Sandeep</USERNAME>

            <LIST_G_RESP>

            <G_RESP>

            <RESPONSIBILITY_ID>1010</ RESPONSIBILITY_ID >               

            <RESPONSIBILITY_ID>1020</ RESPONSIBILITY_ID >               

</G_RESP>

<LIST_G_RESP>

</G_USER>

<G_USER>

  <USERID>200</USERID>

  <USERNAME>Deepak</USERNAME>

            <LIST_G_RESP>

            <G_RESP>

            <RESPONSIBILITY_ID>1010</ RESPONSIBILITY_ID >               

            <RESPONSIBILITY_ID>1030</ RESPONSIBILITY_ID >               

</G_RESP>

<LIST_G_RESP>

</G_USER>

</LIST_G_USER>

</ XXXOALEARNING >

Insert Table in XML Layout

Requirement – Insert a Table in xml layout. Inserting table in MS word and placing place holder creates some alignment issues.

Options – Instead of creating table in MS word manually, we can use BI publisher desktop utility to insert table in RTF file.

1.       Generate the XML Tags
2.       Open the MS Word to create RTF layout
3.       Go to Add on ( Menu) >> Data >> Load XML Data
4.       Browse the xml file and load
5.       Go to Add on ( Menu) >> Insert >> Table/Form>>Select the group and drag to Template section and press Drop All Nodes                                 
6.       Select the Group and pick Table property (Other options are form and free form).Select and delete any unwanted field and press OK.                      

Table will be created with all column and respective place holders.

XML Report Using Source as Data Template

Problem – Generate XML source for BI publisher report.

Solution – There is several options available to generate XML tags, intend as source for BI/XML publisher reports in EBS. One among those is use of data template.

Data Template is an xml file having predefined set of tags. Create a data template file and attach file at (XML Publisher Administrator (R) >> Data Definition >> Add Files >> Data Template)

Header	<?xml version="1.0" encoding="WINDOWS-1252" ?> <dataTemplate name="XXXOALEARNING"  dataSourceRef="ORCL_DB1"   defaultPackage=”T1” version="1.0" >
Data Trigger	<dataTrigger name="beforeParameter" source="T1.beforeParameterFunction"/>
Parameter Section	<Parameters>             <parameter name="p_param1" datatype="number"/>       </Parameters>
Data Trigger	<dataTrigger name="afterParameter" source="T1.afterParameterFunction"/> <dataTrigger name="beforeReport" source="T1.beforeReportFunction"/>
Query Section : We can use multiple query here	<dataQuery>             <sqlstatement name="Q_EMP">                 <![CDATA[SELECT empname From EMP WHERE empid=: p_param1]]>             </sqlstatement>      </dataQuery>
Data Structure : XML tag generation.	<dataStructure>                      <group name="G_EMP" source="Q_EMP">                 <element name="EMPNAME" value="empname"/>            </group>     </dataStructure>
Data Trigger	<dataTrigger name="aftreReport" source="T1. aftreReportFunction "/>

  

</dataTemplate>

Sample Data Template File

<?xml version="1.0" encoding="WINDOWS-1252" ?>

<dataTemplate name="XXXOALEARNING"  dataSourceRef="ORCL_DB1" defaultPackage="T1"   version="1.0" >      

<Parameters>

<parameter name="p_user_id" datatype="number"/>

</Parameters>

<dataQuery>

<sqlstatement name="Q_USER">

<![CDATA[

SELECT user_id,

                User_name,

       FROM FND_USER

       where

        user_id=:p_user_id

   ]]>

</sqlstatement>

</dataQuery>

<dataStructure>

<group name="G_USER" source="Q_USER">

                <element name="USERID" value="user_id"/>

<element name="USERNAME" value="user_name"/>

  </group>

</dataStructure>

</dataTemplate>

Sample generated XML -

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

< XXXOALEARNING >

<P_CUSTOMER/>100 </P_CUSTOMER/>

<LIST_G_USER>

<G_USER>

  <USERID>100</USERID>

  <USERNAME>Sandeep</USERNAME>

</G_USER>

</LIST_G_USER>

</ XXXOALEARNING >

XML Publisher - Different Executables & Data Source available

This post is to list out different executable and corresponding data source available for XML publisher report.

System Administrator Responsibility -

Concurrent >> Program >> Executable >> Create Executable file

                XML_LEARNING  (Oracle Report Builder)

                OA_LEARNING.XML_LEARNING (PL/SQL Stored Procedure)

                XDODTEXE (Java Concurrent Porgram)     <*** Standard program provided by Oracle>

Concurrent >> Program >> Define >> Create Program

                Executable Name – should be as above

                Output Format - XML

XML Publisher Responsibility -

Data Definition >> Create New data definition (code should be CP name)

                Data Template File = Attached if data source is XML file and CP is Java concurrent program

                Bursting File = Attach XML file, if bursting is required

Template >> Create a new template and attach the layout (RTF) file

                                                                               

 Table: Different Executable and Data source for BI Report

 

XML Bursting

Requirement -Split the generated report output into multiple reports based on no of suppliers.

For example - Report is having 3 invoices, 2 for one supplier and 1 for second. concurrent program will generate one file as output having  all 3 invoice detail.
Project need is to split output either on 3 files (one for each invoice) or 2 files ( for 2 supplier). 
  
XML bursting concept help us to cut down the generated output in multiple pieces based on requirement.
Also we can send output through email using this service. 

Assumption - SMTP configuration should be available

Steps for XML bursting -
1) Create and set temp folder  
   - Create one folder in unix box ( application server) and set the temp path in application
       - Go to XML administrator responsibility
       - Administrator Function
       - Give the folder path for TEMP directory

2)Create the bursting control file
    Create the control file. control file contains split logic and delivery method, which will be used by bursting engine. Sample file as below-

   -     

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

          <xapi:request select="/XXXXX/LIST_G_SUPPLIER_NO/G_SUPPLIER_NO">

  <xapi:delivery>

   <xapi:email id="123" server="smtp.host.com" port="25" from="${EMAILFROM}" reply-to="${EMAILFROM}">

   <xapi:message id="123" to="${SUPPLIERSITEEMAIL}" cc="${EMAILCC}" attachment="true" subject="XML Bursting">This program is for xml bursting check

</xapi:message> </xapi:email> </xapi:delivery>

   <xapi:document output="AttachmentName" output-type="pdf" delivery="123">

  <xapi:template type="rtf" location="xdo://APPSHORTNAME.TEMPLATECODE.en.00/?getSource=true" />

 </xapi:document>

</xapi:request></xapi:requestset>

*${XML_TAG_NAME}
Request – Describe group level, on which bursting is required. In above example bursting will be on G_SUPPLIER_NO level
Delivery – How to send the split data ( Email, Fax, Printer etc).
Document output – Subject and format of output    
Template Type – Related template information. Location can have temp directory path ( make sure your rtf file is placed at temp directory too) too  or use the above syntax only.

***en is the territory code.Either use the correct territory code or upload rtf file without territory name.

3)Place the control file –

 XML publisher responsibility >> Data Definition >> Search for your Data Definition >> Bursting Control file >> Add File

4)Invoke Bursting Concurrent Program – XDOBURSTREP is oracle provided standard concurrent program for bursting. Invoke this concurrent program in your program at the last.
   This will read the instructions given in control file and perform bursting.

l_request_id := FND_REQUEST.SUBMIT_REQUEST (
    'XDO',
    'XDOBURSTREP',
    'XML Publisher Report Bursting Program',
    '',
    FALSE,
    'N',
    fnd_global.conc_request_id, 'Y',
    chr(0), '', '', '', '', '', '',
    '', '', '', '', '', '', '', '', '', '',
    '', '', '', '', '', '', '', '', '', '',
    '', '', '', '', '', '', '', '', '', '',
    '', '', '', '', '', '', '', '', '', '',
    '', '', '', '', '', '', '', '', '', '',
    '', '', '', '', '', '', '', '', '', '',
    '', '', '', '', '', '', '', '', '', '',
    '', '', '', '', '', '', '', '', '', '',
    '', '', '', '', '', '', '', '', '', '');

Different Header and Footer for First Page - XML Publisher Report

Requirement  was to report invoice details in xml publisher in PDF format. If there is multiple pages for any invoice, it should show different header details.

In rtf it is very easily achievable using MS word features.

• 1)      Open the rtf file
• 2)      Go Header & Footer section
• 3)      Check the flag “Different First Page”
• 

• 4)      Write the Header/footer Details
• 
• 5)      Go to the second page (if rtf has only one page, put some line space to reach second page)
• 6)      Create second header/footer details
• 
• 7)      Remove the extra line space created in step 5
• 8)      Load the data source
• 9)      Preview the output 
•