Converting RDF Reports to XM Publisher

One of my friends was working on converting 11.5.10 RDF reports into XML Publisher reports. Not a very strange requirement, considering everyone now wants to move to XML Publisher. What however, was strange was the approach followed. Instead of following Approach 1 they opted for Approach 2, listed below.

Approach 1 : Convert the output format of Standard report to XML, create a new RTF template and register a Data Definition and Template with XML Publisher.

Approach 2 : Convert the RDF report into XML Report and use this XML as a Data Template. Create a new XML Publisher report Data Definition with this XML as Data Template and create a new Template.

The problem with Approach 2 is that Oracle provides no tool to convert Reports 6i into XML Report and any other supporting APIs to handle associated PL/SQL logic embedded into RDF.

Enter Reports 9i (or higher) and XML Publisher 5.6.3. These two combinations of Oracle products provide tools to first convert RDF report into XML report and associated XML Publisher API to handle PL/SQL logic.

This is how it works.
-----------------------------
Oracle Reports contains both Data Model (logic) and Layout (presentation) in a single file. In XML Publisher they are stored separately. Therefore migration involves a 2 step process.

Step 1 RDF to XML format report : Oracle Reports 9i (or higher) comes with utility "rwconverter.exe". Use either Reports Designer or "rwconverter.exe" command line to convert RDF into XML format report.

From the Designer :


The following command executes “rwconverter.exe” utility, taking the source report,
XXTEST.rdf and converting it to an RDF-XML format that can be consumed by the
XML Publisher conversion utility.

Note the dtype must be specified as "xmlfile".
\BIN>rwconverter batch=yes source= c:\XXTEST.rdf
dest= c:\XXTEST.xml dtype=xmlfile overwrite=yes


This would create an RDF-XML file, with all the PL/SQL logic and presentation logic.

Step 2.1 Data Model Migration :
XML Publisher 5.6.3 provides a Java API Package "oracle.apps.xdo.rdfparser". This package has "DataTemplateGenerator" class that creates a DataTemplate from the XML report file. If report has triggers this will generate '.pks' and '.pkb' file for that trigger which needs to be compiled into the Database

Example
javaw.exe oracle.apps.xdo.rdfparser.DataTemplateGenerator c:\XXTEST.xml

Output Files:
PL/SQL Package : XXTESTS.pls
PL/SQL Body : XXTESTB.pls
DataTemplate : XXTEST_template.xml

Here is a sample XML output file. Notice the "defaultPackage", "dataTrigger" etc that contain reference to PL/SQL API.


<?xml version="1.0" encoding="UTF-8" ?>
<dataTemplate name="XXTEST" defaultPackage="XXTEST" version="1.0">
<properties>
<property name="xml_tag_case" value="upper" />
</properties>
<parameters>
<parameter name="P_CONC_REQ_ID" dataType = "number"></parameter>
</parameters>
<lexicals>
</lexicals>
<dataQuery>
<sqlStatement name="Q_Main">
<![CDATA[
SELECT user_name, user_id
FROM xxuser
]]>
</sqlStatement></dataQuery>
<dataTrigger name="beforeReportTrigger" source="XXTEST.beforereport"/>
<dataStructure>
<group name="G_1" dataType="varchar2" source="Q_Main">
<element name="user_name" dataType="varchar2" value="SEGMENT_VALUE"/>
<element name="user_id" dataType="number" value="VALUE_SET"/>
</group>
</group>
<element name="CP_segment_name" dataType="varchar2" value="XXTEST.CP_segment_name_p"/>
<element name="CF_RUN_DATE" dataType="USDate" value="XXTEST.cf_run_dateformula()"/>
<element name="CF_NAME" dataType="varchar2" value="XXTEST.cf_nameformula()"/>
</dataStructure>
<dataTrigger name="afterReportTrigger" source="XXTEST.afterreport()"/>
</dataTemplate>


Step 2.2 Data Model Migration :
XML Publisher 5.6.3 API Package "oracle.apps.xdo.rdfparser" has "RTFTemplateGenerator" class to migrate the Oracle Reports layout to an XML Publisher RTF template. Because there is no support for PL/SQL in an RTF Template, the generator writes all Oracle Report format trigger code to the log file. This must later be implemented as XSL code

Example
javaw.exe oracle.apps.xdo.rdfparser.RTFTemplateGenerator c:\XXTEST.xml

Output Files:
RTF Template : C:\XXTEST.rtf
Log File : C:\XXTEST.log

That’s it we are done with the conversion.
Now that we have converted the RDF into XML publisher report, we simply need to use the Data Template and RTF to register with the XML Publisher.

Step 3 : Register a new Data Definition with XML Publisher Responsibility, with XML file generated as part of Step 2.2 as Data Template. Register the RTF template for the DD.

Step 4 : Create a Concurrent Report Program with Report output as XML and executable as "XDODTEXE". XDODTEXE is XML Publisher Data Template Executable Engine, which is a Java Concurrent Program.



That’s it. We have traced the steps involved to convert an existing RDF into an XML Publisher Report for 11.5.10.

I will write more on XML Publisher in my subsequent blogs.

Till then keep reading my blog and feel free to write your comments.

Cheers
~Neeraj

Multilevel XML To Multiple Table

Recently I was working on an implementation project and was faced with situation where client wanted some inbound data populate standard tables. Fair enough, but the problem was that the data provided was in XML format and they wanted a PL/SQL solution for it.
After some research I managed to find out lot of APIs provided by Oracle (and there are plenty of them). Oracle 9i has DBMS_XMLSave
while Oracle 10g has DBMS_XMLStore .
DBMS_XMLStore is the preferred package as it is written in C and linked into the Oracle kernel. It also uses the SAX parser. As a result DBMS_XMLStore has better performance characteristics.

Both of these APIs rest over XMLtype object. XMLType is a system-defined opaque type for handling XML data and has predefined member functions on it to extract XML nodes and fragments. You can create columns of XMLType and insert XML documents into it. You can also generate XML documents as XMLType instances dynamically.

Now, most samples that I found on internet handled inserting into a single table at a time (will show that later as well). However, what I needed was
1) Read XML file from a file known location.
2) Read the structure into a master-detail (parent-child) kind of relation.

Based on my learnings, here is what it looks like :


-- In -- Flat Multi Level XML
-- Out -- Two Tables

----------------------------------
TABLES
----------------------------------
CREATE TABLE emp_t (
EMP_ID NUMBER,
NAME VARCHAR2(10) )
/
CREATE TABLE emp_details (
EMP_ID NUMBER,
DESCRIPTION VARCHAR2(100),
B_DATE DATE)
/
----------------------------------
XML
----------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<EMP_ROWS>
<EMP>
<EMP_ID>1</EMP_ID>
<NAME>A</NAME>
<LINE_ROWS>
<LINE>
<EMP_ID>1</EMP_ID>
<B_DATE>01-JUN-2005</B_DATE>
<DESCRIPTION>DESCRIPTION1</DESCRIPTION>
</LINE>
<LINE>
<EMP_ID>1</EMP_ID>
<B_DATE>01-JUN-2005</B_DATE>
<DESCRIPTION>DESCRIPTION2</DESCRIPTION>
</LINE>
<LINE>
<EMP_ID>1</EMP_ID>
<B_DATE>01-JUN-2005</B_DATE>
<DESCRIPTION>DESCRIPTION3</DESCRIPTION>
</LINE>
<LINE>
<EMP_ID>1</EMP_ID>
<B_DATE>01-JUN-2005</B_DATE>
<DESCRIPTION>DESCRIPTIONDESCRIPTION4</DESCRIPTION>
</LINE>
</LINE_ROWS>
</EMP>
<EMP>
<EMP_ID>2</EMP_ID>
<NAME>B</NAME>
<LINE_ROWS>
<LINE>
<EMP_ID>2</EMP_ID>
<B_DATE>01-JUN-2005</B_DATE>
<DESCRIPTION>DESCRIPTION5</DESCRIPTION>
</LINE>
<LINE>
<EMP_ID>2</EMP_ID>
<B_DATE>01-JUN-2005</B_DATE>
<DESCRIPTION>DESCRIPTION6</DESCRIPTION>
</LINE>
<LINE>
<EMP_ID>2</EMP_ID>
<B_DATE>01-JUN-2005</B_DATE>
<DESCRIPTION>DESCRIPTION7</DESCRIPTION>
</LINE>
<LINE>
<EMP_ID>2</EMP_ID>
<B_DATE>01-JUN-2005</B_DATE>
<DESCRIPTION>DESCRIPTION8</DESCRIPTION>
</LINE>
</LINE_ROWS>
</EMP>
</EMP_ROWS>


----------------------------------
Sample Code
----------------------------------

DECLARE

v_xml_clob CLOB;
-- Oracle directory object and filename
-- Read the XML file into BFILE

v_xml_file BFILE := BFILENAME('USR_TMP', 'emp_data_levels.xml');

-- offsets refer to start of files
v_dst_offset number := 1;
v_src_offset number := 1;

v_lang_ctx number := DBMS_LOB.DEFAULT_LANG_CTX;
v_warning number;

v_xml XMLtype;

v_rows NUMBER;

BEGIN
-- Open xml file on OS
dbms_lob.open(v_xml_file,dbms_lob.lob_readonly);

-- necessary for correct handling of LOB locator
dbms_lob.createtemporary(v_xml_clob, true, dbms_lob.session);

-- xml file to clob
dbms_lob.loadCLOBfromfile(
dest_lob => v_xml_clob
, src_bfile => v_xml_file
, amount => dbms_lob.getlength(v_xml_file)
, dest_offset => v_dst_offset
, src_offset => v_src_offset
, bfile_csid => dbms_lob.default_csid
, lang_context => v_lang_ctx
, warning => v_warning
);

-- close xml file
dbms_lob.close(v_xml_file);

-- clob to XMLtype
v_xml := XMLtype(v_xml_clob);

------------------------------------
-- XML Processing Begins
------------------------------------
INSERT
WHEN tab = 'emp_t'
THEN
INTO emp_t
VALUES (id, name)
WHEN tab = 'emp_details'
THEN
INTO emp_details
VALUES (id, b_date, description)
WITH T AS (select XMLtype(v_xml_clob) xml from dual)
SELECT 'emp_t' tab,
to_number(extractvalue (t2.column_value, 'EMP/EMP_ID')) id,
extractvalue (t2.column_value, 'EMP/NAME') name,
null b_date,
null description
FROM t t, TABLE (xmlsequence (t.xml.extract ('EMP_ROWS/EMP'))) t2
UNION ALL
SELECT 'emp_details',
to_number(extractvalue (t2.column_value, 'LINE/EMP_ID')) emp_id,
null,
extractvalue (t2.column_value, 'LINE/DESCRIPTION') description,
to_date(extractvalue (t2.column_value, 'LINE/B_DATE'),'DD-MON-YYYY') b_date
FROM t t,
TABLE (xmlsequence (t.xml.extract ('EMP_ROWS/EMP/LINE_ROWS/LINE'))) t2;

-- commit ;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line ( 'Exception' || SQLERRM );
END;


------------------------------------------------------------------------

Code is mostly self explanatory. Read the physical file, create a temporary CLOB, load CLOB from file and then crete a new XMLType. Using XMLType and XPath notation, read the XML and insert into the required columns.
That's it !!!

Looks like a really simple solution and it really really works too..

We will more later. Till then ..

Cheers
~Neeraj

XML Publisher Features : PDF Merge

In the previous article I addressed merging different XMLs and Templates (RTF to XSL) to generate PDF output.

In this article I will show a similar capability provide for merging existing PDF outputs and additional manipulation that can be achieved.
XML Publisher PDF merging allows us to merge different unprotected PDF documents (PDF docs not password protected) and add Page Number, Text and Image Watermarks.If both text and image watermark are used, only image watermark is applied.
PDFs can be from totally different sources having totally different layouts.

This capability is provided by "PDFDocMerger" class of "oracle.apps.xdo.common.pdf.util" package.

Again we need to write a simple Java class that reads different PDF file streams and merges them using PDFDocMerger class. It also allows for adding watermark and page numbering.
Page numbering capability allows us to specify Page Numbering coordinates, starting page number and physical page from where numbering should begin.

Sample Code :
------------------------

import oracle.apps.xdo.common.pdf.util.PDFDocMerger;
import java.io.*;

public class PdfMerger
{
public static void main(String[] args)
{
System.out.println("Started") ;
try
{
FileInputStream[] fis = new FileInputStream [3] ;
FileInputStream fis1 = new FileInputStream("C:\\temp\\xpub\\pdf\\Input1.pdf");
FileInputStream fis2 = new FileInputStream("C:\\temp\\xpub\\pdf\\Input2.pdf");
FileInputStream fis3 = new FileInputStream("C:\\temp\\xpub\\pdf\\Input3.pdf");
fis[0] = fis1 ; fis[1] = fis2 ; fis[2] = fis3 ;

FileOutputStream fos1 = new FileOutputStream ("C:\\temp\\xpub\\pdf\\PdfMerger1.pdf") ;

// 1.Text Watermark
System.out.println("1 BEGIN") ;
PDFDocMerger pdfDocMrgr = new PDFDocMerger(fis,fos1);

// Adding Page Numbering
// Calling setPageNumberCoordinates() method is necessary to set Page Numbering
// setPageNumberFontInfo : the default font"(Helvetica, 8)" is used.
// setPageNumberValue : the default initial vaulue "(1, 1)" is used.

pdfDocMrgr.setPageNumberCoordinates(300, 20);
pdfDocMrgr.setPageNumberFontInfo("Courier", 10);
pdfDocMrgr.setPageNumberValue(3, 2); // Start from what number, start from which page


// Add Text Watermark
// Can also use setTextDefaultWatermark(String) without these detailed setting
pdfDocMrgr.setTextWatermark("TEXT WM", 200f, 200f); //set text and place
pdfDocMrgr.setTextWatermarkAngle(80); //set angle
pdfDocMrgr.setTextWatermarkColor(0.3f, 0.3f, 1.0f); // set RGB Color

/* Commented code for Image watermark.
// Add Image Watermark, Fit to Size
FileInputStream imgStream = new FileInputStream("C:\\temp\\xpub\\pdf\\oralogo.gif");
float[] rct = {10f, 10f, -1f, -1f}; // fit to size .. no stretch -1,-1
//float[] rct = {100f, 100f, 400f, 400f}; // Stretch
pdfDocMrgr1.setImageWatermark(imgStream, rct);

*/
// Merge PDF Documents and generates new PDF Document
pdfDocMrgr.mergePDFDocs();
pdfDocMrgr = null;
System.out.println("1 END") ;


fos1.close();

}//try
catch (Exception e)
{
System.out.println(" Exception " + e.getMessage()) ;
e.printStackTrace() ;
}
System.out.println("End") ;

}//main
}//PdfMerger

After we compile and run the code, we can see the output.

Output : Page Numbering + Text Watermark
---------------------------------------------




Output : Page Numbering + Image Watermark
---------------------------------------------




I will be writing more about XML Publisher in my subsequent articles. Till then...

Cheers
~Neeraj

XML Publisher Features : XML Merge

In my previous articles I have written about generating PDF output using different approaches provided by XML Publisher.
In this article I will write about Merge feature provided by XML Publisher. FOUtility class provided as part of the "oracle.apps.xdo.template.fo.util" package allows us to merge different XML sources with single or multiple XSL or RTF templates to create a single PDF output.

This kind of feature is useful when we have multiple XML sources and want them to produce a single formatted output. Alternately, we can merge multiple templates to different XML data stream to generate output in one shot.

To exploit this capability we need to write a Java class that uses XML publisher API.

Steps Involved are similar to what we have already done in earlier Java+XML code.

Approach 1 : Single Templates + Multiple Data XML
----------------------------------------------------------

1) Read RTF Template and convert it to XSL.
2) Get XML data streams. Create FO strem of XML and XSL using "FOUtility.createFO" for each XML data.
3) Merge these FOs using "FOUtility.mergeFOs" to generate a single merged FO stream.
4) Generate PDF (or any other output) using FOProcessor class.

Sample Code
--------------------------

import oracle.apps.xdo.template.RTFProcessor;
import oracle.apps.xdo.template.FOProcessor;
import oracle.apps.xdo.template.fo.util.FOUtility;
import oracle.apps.xdo.XDOException;

import java.lang.reflect.Array;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.InputStream;

public class XmlMerge
{


public static void main(String[] args)
{
System.out.println("Started") ;
try
{ //Convert RTF to XSL-FO
RTFProcessor rtfProcessor = new RTFProcessor("C:\\temp\\xpub\\rtf\\XMLPUBTEST.rtf"); //input template
rtfProcessor.setOutput("C:\\temp\\xpub\\rtf\\XmlMerge.xsl"); // output file
rtfProcessor.process();

//Create XSL-FO objects for multiple XML input streams
InputStream[] in = new InputStream[2] ;
InputStream fFOStream = FOUtility.createFO("C:\\temp\\xpub\\rtf\\Emp1.xml", "C:\\temp\\xpub\\rtf\\XmlMerge.xsl");
InputStream sFOStream = FOUtility.createFO("C:\\temp\\xpub\\rtf\\Emp2.xml", "C:\\temp\\xpub\\rtf\\XmlMerge.xsl");
Array.set(in, 0, fFOStream);
Array.set(in, 1, sFOStream);

//Generate the merged FO
InputStream mergedFOStream = FOUtility.mergeFOs(in, null);

if (mergedFOStream == null) {
System.out.println("Merge failed.");
}
// Create FO Processor
FOProcessor processor = new FOProcessor();
processor.setData(mergedFOStream);
processor.setTemplate((String) null);
//FORMAT_EXCEL , FORMAT_HTML, FORMAT_RTF , FORMAT_PDF
//Set the format
processor.setOutput("C:\\temp\\xpub\\rtf\\XmlMerge.pdf");
processor.setOutputFormat(FOProcessor.FORMAT_PDF);
processor.generate();
}
catch (XDOException xe)
{
System.out.println(" XDOException " + xe.getMessage()) ;
}
catch (Exception e)
{
System.out.println(" Exception " + e.getMessage()) ;
}
System.out.println("End") ;
} //main
} //XmlMerge



Output
-------------


/***********************************************************************/

Approach 2 : Different Templates + Multiple Data XML
----------------------------------------------------------
1) Read RTF templates and convert them to XSL.
2) Creat an String array of XML and XSL file names identifying data and templates.
3) Generate PDF (or any other output) using FOProcessor class.

Sample Code
--------------------------


import oracle.apps.xdo.template.RTFProcessor;
import oracle.apps.xdo.template.FOProcessor;
import oracle.apps.xdo.template.fo.util.FOUtility;
import oracle.apps.xdo.XDOException;


import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.InputStream;

// This approach takes different XML and XSL files

public class XmlMerge1
{

public static void main(String[] args)
{
System.out.println("Started") ;
try
{
//Create XSL-FO objects for multiple XML input streams
// This approach applies specific XSL Format to specific XML data files
String[] xmlInput = {"C:\\temp\\xpub\\rtf\\Emp1.xml", "C:\\temp\\xpub\\rtf\\Emp2.xml"};
String[] xslInput = {"C:\\temp\\xpub\\rtf\\XmlMerge1.xsl", "C:\\temp\\xpub\\rtf\\XmlMerge2.xsl"};

// Create FO Processor
FOProcessor processor = new FOProcessor();
processor.setData(xmlInput);
processor.setTemplate(xslInput);
//FORMAT_EXCEL , FORMAT_HTML, FORMAT_RTF , FORMAT_PDF
//Set the format
processor.setOutput("C:\\temp\\xpub\\rtf\\XmlMerge12.pdf");
processor.setOutputFormat(FOProcessor.FORMAT_PDF);
processor.generate();
}
catch (XDOException xe)
{
System.out.println(" XDOException " + xe.getMessage()) ;
}
catch (Exception e)
{
System.out.println(" Exception " + e.getMessage()) ;
}
System.out.println("End") ;
} //main
} //XmlMerge1


Output
-------------



In my next few articles I will address more of the XML Publisher features.


Cheers
~Neeraj

WebADI - Using a Custom Integrator

After all the hard work in creating custom integrator, we are all set to use our custom integrator. This is the easiest of all the activities.

1) Select "Oracle Web ADI” responsibility and click on "Create Document" function.

2) Select the viewer, generally Excel, to generate the document.



3) Select our custom Integrator from the list of available integrators.



4) Select out layout we defined in our earlier steps.



5) Select "None" on the Content tab.



6) Review and click on Create Document.



7) This will create and excel sheet with all the columns we defined in the layout. Fill the excel sheet with the data we want to upload.



8) Click on Oracle -> Upload and wait for it to upload the records.
Upon successful upload, a smiley icon is shown for each record.

NOTE :- Integrator commits only if all rows are uploaded successfully.

We can check the results from the database to verify the success .




That’s it.. We have created our Integrator and used it to upload data into Oracle Apps using WebADI !!!!!!

Cheers
~Neeraj

WebADI - Creating Integrator Layout

In my earlier posts, we saw steps involved in WebADI Creating Custom Integrators and steps involved to Associate Function with Custom Integrators.
Now, continuing with WebADI custom Integrators, we need to define a layout. This layout actually defines how the XLSheet will look like for uploading actual data.

1) From “Oracle Web ADI” responsibility, click "Define Layout: function to create a Layout for existing or Custom Integrators.



2) Next select the Integrator we created in earlier steps.



3) Click Define Layout, give Layout a name and Layout Key.



4) Click Continue. The next screen shows all the procedure parameters.
Choose as Header if the value should remain same for all data lines or Line if the value changes for each line. See the screen shot below. Click Apply.
For complex layouts, we need to first identify Header and Lines at the start itself.



5) That's it !! We are done. And ready to use this custom integrator to load data into Oracle Apps without coding or programming anymoer.


Cheers
~Neeraj

WebADI - Associate Function with Custom Integrators

In an earlier post WebADI Creating Custom Integrators we saw the steps to create a Custom WebADI Integrator However, we can’t use this Integrator as we need to take care of the Function Security in Oracle Apps.
For this we need to create a Function and associate the Integrator with this function and assign it to the menu seeded WebADI Menu.

Here are the steps to create this link.

1) Create a from function TESTEMP.



2) Repeat steps 3) and 4) of previous post to select another Integrator and select "HR Maintain Integrator Form Function Associations". This is a standard integrator that allows us to associate a Form Function with an Integrator.



3)Next, select layout "Form Function Association"(default standard).



4)Next, select "Form Function Association" (default standard)for content.



5)Next, give the Application Short Name and Integrator name (just create in steps earlier)
Click continue, and on Review screen, click on "Create Document" button.
This will generate the excel sheet with all the required details. Open the excel sheet.



6)Complete the excel sheet by entering the Form function name we created earlier.



7)Click Oracle -> Upload. This will create the association between Integrator and the Form Function.

8)Now, add this function to the Menu (function security)



Bingo!!! We are done. Now we need to create/ define a Layout and we will be ready to upload data using this Integrator.

Cheers
~Neeraj