In my current project but also in previous ones I make use of the BI (XML) Publisher functionality of Oracle E-Business Suite. Using this you’re able to migrate your own created SQL queries into a nice report which an end-user can run himself including custom parameters. The report can, for example, generate an XML file which can be opened with Microsoft Excel.
I’ll explain in this article how you can use this functionality. In later posts I’ll explain the other nice features of BI Publisher.
First we need to have s SQL query which you want to migrate to BI Publisher. Let’s start with a simple query which generates a list of application user names together with the name of the assigned employee.
The SQL I use is:
SELECT
fuser.USER_NAME
, per.FULL_NAME EMPLOYEE
FROM
APPLSYS.FND_USER fuser
, APPS.PER_PEOPLE_F per
WHERE
fuser.EMPLOYEE_ID = per.person_id
AND SYSDATE BETWEEN per.EFFECTIVE_START_DATE AND per.EFFECTIVE_END_DATE
The first thing you need to do is to create a so called Data Template. A data template is a simple xml file with a defined number of elements.
A basic data template containts the dataTemplate element where you define the name of your template and a description. The next element set is the parameters section. In this section your define the names, datatypes and possible default values for parameters used in your query. You define these parameters later also in the concurrent program definition. The dataQuery block contains the sqlStatement block where you define the SQL query (in conjunction with any parameters). Optionally your are able to run some trigger at a stage of the data generation (quite handy sometimes :-)). The dataStructure block defines the output columns of the data. When defining the xml file always make sure you close every element block.
What we want to do with our SQL query is provide the user the option to query on a user name while running the report so we’ll add one parameter to it.
Let’s build up the XML file. First we define the XML tag and the dataTemplate tag.
<?xml version="1.0"?>
<dataTemplate name="XXX_EXAMPLE" description="Example Report" Version="1.0">
Next step I’ll add the optional properties block. By default used parameters are also in the output of the resulting data file. I want to exclude those parameters in the output and add the below part to my XML file.
<properties>
<property name="include_parameters" value="false" />
</properties>
I want to use one parameter in my report being the user name. The user must be able to use a wildcard to query his results (use a LIKE). I’ll add the below parameter block.
<parameters>
<parameter name="p_USER_NAME" dataType="character" />
</parameters>
After this I need to add my SQL query to the XML file within the dataQuery and sqlStatement blocks. Provide a short name to the sqlStatement within the name variable. I’ll add an additional where clause to my statement and reference my earlier defined parameter with a : before it.
<dataQuery>
<sqlStatement name="EX">
<![CDATA[SELECT
fuser.USER_NAME
, per.FULL_NAME EMPLOYEE
FROM
APPLSYS.FND_USER fuser
, APPS.PER_PEOPLE_F per
WHERE
fuser.EMPLOYEE_ID = per.person_id
AND SYSDATE BETWEEN per.EFFECTIVE_START_DATE AND per.EFFECTIVE_END_DATE
AND fuser.USER_NAME LIKE decode(:p_USER_NAME,null,'%',:p_USER_NAME)
]]>
</sqlStatement>
</dataQuery>
Next I’ll define which data to output in the resulting file. The dataStructure element is opened with a group element inside it. Define a name for the group (this will be a element in the resulting XML file) and set the source of the group. The source value must contain the name of the earlier defined sqlStatement element. I’ll output the user name and the employee column from my statement. The name variable will define the name of the resulting XML element in the data XML file.
<dataStructure>
<group name="G_EX" source="EX">
<element name="USER_NAME" value="USER_NAME" />
<element name="EMPLOYEE" value="EMPLOYEE" />
</group>
</dataStructure>
As the last step we need to close the dataTemplate tag and save the XML file.
</dataTemplate>
We open E-Business Suite and go to responsibility XML Publisher Administrator.
Click on Create Data Definition and provide the required details. Remember or write down the code value you’re using. Click on Apply.
Select your data template XML file by clicking on the Add File button after Data Template.
We’re done now in BI Publisher and are ready to define the concurrent program which will run the data template you’ve just defined. Go to System Administrator – Concurrent – Program – Define.
When defining the concurrent it’s important to set the Short Name of the concurrent program to be the same as the code you’ve given to the Data Definition in BI Publisher. (You remember it or have written it down right? :-)) The concurrent manager will assign the data template to the concurrent program when started. Also important is to use executable XDODTEXE. This is the executable which is able to run your defined data template. I want an XML file as output so change the Output Format to XML.
I’ll add the parameter I defined in my data template so click on Parameters. As the Token of the parameter use the name you defined in your data template xml file.
You’re done with setting up your report. Add the concurrent program to the required Request Group and start your concurrent program. The concurrent manager will run your data template and will pass the given parameter. Your result will be a well formatted XML file with the result of the SQL query.
Tell me what you think and your experiences with using this BI Publisher functionality through the below comment form.
Thnx Cyriel, Just what we were looking for 🙂
Do you know of anyway to put information into the concurrent request log?
Hi,
What do you mean exactly? Through bi publisher? Or through pl/sql driven concurrent programs?
Let me know,
Cyriel
Cyriel,
Just curious to know if there is any Wizard method available to create the data template. Or only possible by creating it manually?
Thanks.
Regards,
Ken
Hi,
This post was using an old version of XML Publisher, currently BI Publisher. Starting from BI Publisher 11g datatemplates, or in 11g terms, datamodels can be created more easily by using the datamodel builder. I’ll soon post some information about that.
Regards,
Cyriel
Thnx Cyriel, you have done great job it is useful to me
Hi,
Is there any way to pass input mask for data parameter in data template
Thanks,
Vivek