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

 

Excel Output Without XML publisher

Requirement– There are limited formats available to view Concurrent Program output. XML is one format which is not available. 

                    

Available Options – XML publisher is one frequently used solution, which has capability to display output in PDF, EXCEL and RTF formats. EXCEL file generated using xml processing is having more size compare to actuals.

                                    

Solution – This post is to discuss another way to generate output file in EXCEL format without using XML publisher. A small setup change is required.

1. Create a concurrent program having PL/SQL as executable
2. Select the output type as any available option (ex - PCI)                              
3. Print the output using FND_FILE.PUT_LINE(FND_FILE.OUTPUT,’Output Message’);
4. Change the Mime type(view option) as EXCEL for PCL file format
5. Run the concurrent program
6. Select Tool>>Copy option to see the output

Script to Update the Mime Type

Limitation – To view multi column format, use a separator during output print. Once output is opened in excel use excel options to display multi column view by using used separator.  

Update MIME type

There are few file formats available in oracle EBS to support concurrent program output.

Each file format is having standard Mime Type associated and being stored in FND_MIME_TYPES_TL Table, which can be updated to any other mime type using below script –

UPDATE fnd_mime_types_tl

SET    mime_type = 'application/vnd.ms-excel'

,      description = 'Excel (SYLK) used to be application/vnd.hp-PCL: Printer Control Language'

,      last_updated_by = 0

,      last_update_date = sysdate

WHERE  file_format_code = 'PCL'

AND   mime_type = 'application/vnd.hp-PCL';

COMMIT;