Thursday, November 11, 2010

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”

Image
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
Image
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
Image


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
Image


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.
Image


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.
Image


Image



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


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].
Image


Save this MSWord file as MANAGER_XML_TEMPLATE.rtf

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

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

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”
Image


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.
Image


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.
Image


14. Navigate to “Receivables Manager” and submit report XX_MANAGERS.
Click on Output button to see the report as pasted below.
Image

No comments:

Post a Comment