oracle concepts for you: XML Publisher-report-from-rdf

XML Publisher Concurrent program - XMLP:-

In this blog post I will detail the step by step process of developing a XML publisher report using an RDF type report concurrent program in oracle apps.

The XML publisher windows desktop patch has to be applied in you PC before developing the XML template

Overview

• XML Publisher is a template based publishing solution by Oracle which allows us to create visually rich documents with numerous formatting options. (Available in ver.11.5.10)

• XML Publisher makes use of the data retrieved from Reports 6i.

• The template, designed by the developer is used as the layout for the report.

• The template will contain the layout with tags that are mapped to the data model of the report.

• At runtime the data from reports is merged onto the designed template to produce the final report.

Here it goes:

Before we see the step by step process, I will brief the main points:

· Create a report and register it as Concurrent Program of type XML

· Build a Data Definition & XML Template using XML Publisher

· Create a relation between the XML Template & Concurrent Program

Develop a RDF Report concurrent program in oracle apps:

1. Develop a RDF report and register it in oracle apps

· Develop the Report (Report Name: Sample XML Report) Data model

· Develop the Report Layout model

· Define the RDF executable

· Define the concurrent program

· Assign this concurrent program to a responsibility

The RDF report concurrent program is complete, now we have to get the XML data file from the concurrent program and create the .xml file:

2. Run the report from a responsibility after changing the output format of the concurrent program to XML.

· Go to the concurrent program definition and change the output format of the concurrent program

· Run the concurrent program

· The XML data output of the concurrent program will look like this pic below

If you map the oracle report data model and the xml data file. It will look like this

· Save this XML data file as filename.xml say FNDWRR.xml

Create the .rtf file. Before you do this, check whether XML publisher desktop patch is applied in your local machine. check the blog post for installing the XML publisher desktop patch:
http://oracleapps4u.blogspot.com/2011/04/installing-oracle-bi-publisher-xml.html

3. Load the XML data file to MS Word and save it as .rtf file

· Load the XML file (FNDWRR.xml) into MS-Word

· Using table wizard, form the template layout

If you map the xml data file to the word template. It will look like this

We can develop the template layout with out the table wizard, by marking up the text form fields with the corresponding tags.

11. Insert a Text Form Field from the Forms tool bar (ViewàToolbarsà Forms)

2. Place the Text Form field in a position where you desire.

3. Double click on the form fieldàClick on the Add Help Text button.

4. Go to the Status Bar Tab and type your tag in the text field available.

It will look like this

· Save the file in .rtf extension, say XXSMPRTF.rtf

A Bit of XML publisher

1. Go to “XML Publisher Administrator” Responsibility and define the XML Data Definition & XML Template. Also create a relation between the XML Template and the concurrent program.

· Create the data definition

Define the values as below:

Name: Sample XMLP Report – XML Output (Any Unique name in the Application)

Application: Receivables (Any Application)

Code: XXSMPRPT (This should be same as the Concurrent program short name)

Start Date: Leave default (sysdate)

· Create the template

Define the values as below:

Name: Sample XMPL Report Template (Any Unique name in the application)

Application: Receivables (Any Application)

Data Definition: Sample XMLP Report – XML Output (This should be same as the XMLP Data Definition Name)

Code: XXSMPTMP (Any Unique name)

Start Date: Leave Default (sysdate)

Type: RTF

Default Output Type: PDF (As your wish)

· Create relation ship between the XML Template and the concurrent program

In the XML Data Definition the Data Definition code should be same as the concurrent program short name. As the XML Template has the XML Data Definition name, this actually creates a relationship between the XML Template and the concurrent program.

Concurrent program	XML Data Definition	XML Template
Code: XXSMPRPT	Code: XXSMPRPT
	Name: Sample XMLP Report – XML Output	Name: Sample XMLP Report – XML Output

· Now run the concurrent program. The program output will be displayed as PDF file.

XML Publisher Concurrent Program - XMLP

In this article I have listed a step by step demo of XML Publisher as a Concurrent Program in Oracle Apps.

I think it is very important that you get a feel of XML Publisher, as this will become the reporting tool in Fusion [assuming XMLP can become as powerful as Oracle Report in the given time].

Before I explain the steps in details with screenshots, let me brief them:-
a.   Create a report and register it as Concurrent Program of type XML
b.   Build a Data Definition & XML Template using XML Publisher.
c.   Create a relation between XML Template & Concurrent Program.

Sounds simple, it is indeed, but devil lies in detail. Hence read the below carefully.
I have provided the source code with installation instructions for you to try this. Please note to try this you need 11.5.10 environment.

Now lets get into the details….Step by Step…..
1. Create a table xx_emp_demo and insert two records into it.
create table xx_managers ( manager_no integer, manager_name varchar2(200) ) ;
insert into xx_managers ( manager_no, manager_name ) values ( 1000, 'Anil Passi') ;
insert into xx_managers ( manager_no, manager_name ) values ( 1001, 'Martin') ;
commit;

2. Next you need a concurrent program that spit out an XML file to the output of the concurrent program. Such concurrent program can be of type SQL or PL/SQL or Oracle Report or any other supportable type, provided it can produce a XML output.
Hence in this case, we will develop a Report that has all the columns from table xx_managers
Create a plain default Oracle Report using wizard in Oracle Reports 6i.
This can be done using wizard with SQL “select * from xx_managers”

Above picture shows the report output in Reports 6i preview

Do not change anything in DESFORMAT, as Concurrent Manager will take care of passing XML to this. Hence XML Publisher will be fed an XML output by the concurrent manager itself.

3. Add three user parameters P_CONC_REQUEST_ID, P_RESPONSIBILITY_ID, P_USER_ID

These parameters will make it possible to run a report as concurrent program.
Save the above RDF as XX_MANAGERS.rdf

4. FTP XX_MANAGERS.rdf to $AR_TOP/reports/US
Silly it is, but for demo I will use standard Application [forgive me, but I am making it easier for you to run the source code].

5. Create Concurrent program executable for XMLP report, as if it is a normal report

6. Create a concurrent program for the executable. Note that I have kept the shortname and the program name both as XX_MANAGERS.
IMPORTANT:- Note that format is XML

7. Register this program with Receivables request group.
DECLARE
BEGIN
FND_PROGRAM.add_to_group
   (
   PROGRAM_SHORT_NAME =>'XX_MANAGERS'
   ,PROGRAM_APPLICATION =>'AR'
   ,REQUEST_GROUP       => 'Receivables All'
   ,GROUP_APPLICATION   =>'AR'
   ) ;
commit;
exception
   when others then
          dbms_output.put_line('Object already exists');
END ;
/

8. Run the report and you will see an xml output appearing. Save the output as XX_MANAGERS_241106.xml on your computer. You will need to use menu /Tool/Copy to open this XML output in browser, and then save it as XX_MANAGERS_241106.xml on your PC.

NOW THE TIME TO DO XML PUBLISHER BIT…

9. Install this XML Publisher Desktop software on your pc, ensuring that you have MS-Word installed(for this demo)
The XML Builder Desktop Patch for Word is Patch Number: 4561835
This software can be downloaded from
http://updates.oracle.com/download/4561835.html

This is a tool given by Oracle[Desktop XML Publisher] that will read any XML file, will create the fields automatically.

10. Open MS Word after installing as per step 9, and you will now see something similar to below in Microsoft Word

Now in MS Word, click on “Load XML Data” and select file XX_MANAGERS_241106.xml that was created in Step 8.

11. Now, click on Insert/Table Form
And then drag and drop “List G Manager No” within the template section and click on OK.

Effectively by doing these steps, you have just created a Layout for the report in XML Publisher. The layout will look like this [as below].

Save this MSWord file as MANAGER_XML_TEMPLATE.rtf

By clicking on Preview, you will see the output as below

12. Now, lets do the real bit in attaching the Concurrent Program definition to XML
Publisher. Logon to responsibility “XML Publisher Administrator”.

Click on “Data Definition” menu as above…so that we can create the XMLs source data Definition. Effectively we are saying that output of the Concurrent program becomes the data definition for XMLP.

Lets define the value in below fields
Name : XX_MANAGERS
Application : Receivables
Code : XX_MANAGERS [Note: This is the short name of the Concurrent program]
Start Date : Leave it to default Sysdate
Click on “Apply”

13. Now lets define a template as below for XML Publisher.

For doing so, click on Templates Tab. We need to create a Template for the data-definition of previous step.

Name : XX_MANAGERS [this is the name of the template]
Code : XX_MANAGERS [short name of conc program]
Application : Receivables
Data Definition : XX_MANAGERS [the one that we created in prior step 12]
Start Date: : Leave this default.
Type : RTF
Click on browse and select the RTF file[MANAGER_XML_TEMPLATE.rtf] that you had saved in Step 11.
xmlp_A_14

Click on Apply, and you will get the below record.