Oracle Application

Converting RDF Reports to XM Publisher

2008-03-21T10:27:00.001-07:00

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

2008-02-27T23:02:00.000-08:00

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

2008-02-26T01:14:00.001-08:00

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

2008-02-25T23:28:00.000-08:00

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

2008-02-17T21:39:00.000-08:00

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

2008-02-17T21:15:00.000-08:00

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

2008-02-16T03:28:00.000-08:00

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

Oracle Integration Repository

2008-02-16T02:34:00.000-08:00

Oracle E-Business Suite includes many different APIs and integration technologies, such as PL/SQL procedures, Java classes, XML Gateway messages, Electronic Data Interchange (EDI) messages, open interfaces, concurrent programs, business events,and Web services.

Most consultants working on Oracle Apps find it difficult to find the right public API to use while designing RICEW components. This was primarily due to the fact that there wasn't a single document that listed all the public APIs or integration points.

Beginning with Oracle E-Business Suite 11i10, Oracle introduced a comprehensive online directory of all Oracle E-Business Suite integration points, including Web services, business-to-business
(B2B) protocols, application programming interfaces (APIs), legacy integration technologies, and business events.

This product is called Integration Repository or iRep. Oracle has made 11i10 iRep available at the following url: http://irep.oracle.com/index.html

The tool lets users easily discover and deploy the appropriate business service interface for integration with any system, application, or business partner.

In Release 12, the Oracle Integration Repository ships as part of the E-Business Suite. Once the instance is patched, the iRep automatically updates with content appropriate for the precise revisions of interfaces in the instance

XML Publisher Using Simple Java + XML + XSL

2007-12-30T08:04:00.000-08:00

XML Publisher provides lot of Java APIs and capabilities through its APIs. Using these APIs we can write Standalone Java Programs to generate Reports or write Java Concurrent Programs(JCPs) in Apps environment.

One of the Java Package/APIs provided is FO Processor Engine. These APIs merge an XML file (data source file) and an XSLT file (template) to generate PDF, Excel, HTML, and RTF reports.

So lets see an example of how to do it using a simple Java Class.

Step 1: Create XML and XSL file
----------------------------------
XML is the data file and XSL is the file that specifies the layout of the data on the report.
Generally, XML can be obtained from database using APIs or from any other system. We will use the same XML data that we have used in other exmples.
XSL is the stylesheet. We can create XSL using XSL tools. Generally, we use a RTF and convert it into XSL.

XML :-
--------
Screen shot of XML file.

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

XSL :-
---------
Screen shot of XSL file.

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

Step 2: Write the Java code
------------------------------

Now we need to write the Java program that uses XML Publisher’s java APIs to generate the output.

Here's a sample code :
------------------------

import oracle.apps.xdo.XDOException;
import oracle.apps.xdo.common.pdf.util.PDFDocMerger;
import oracle.apps.xdo.template.FOProcessor;
import oracle.apps.xdo.template.fo.util.FOUtility;
import oracle.xml.parser.v2.NSResolver;

public class EmpXmlPub {
public EmpXmlPub() {
}

public void foEngine() {
try {
FOProcessor processor = new FOProcessor();
processor.setData("C:\\temp\\xpub\\1\\Emp.xml"); //XML Data File
processor.setTemplate("C:\\temp\\xpub\\1\\emp1.xsl"); //XSL Data File
processor.setOutput("C:\\temp\\xpub\\1\\emp.pdf"); //OutputFormat
processor.setOutputFormat(FOProcessor.FORMAT_PDF);
processor.generate();
} catch (XDOException e) {
System.out.println("XDOException " + e.getMessage());
}
}

public static void main(String[] argv) {
EmpXmlPub empXmlPub = new EmpXmlPub();
empXmlPub.foEngine();

}
} // EmpXmlPub

Step 3: Compile the code and run
-----------------------------------
Now we are ready to compile the code and run the program. We need to ensure that all the required classes, jar files and resources (xml, xsl) files are with us.
We have to ensure that JAVA_HOME and CLASSPATH variables are set or specified explicitly in the command.

javac.exe -classpath .;xdocore.jar;xmlparserv2-904.jar;versioninfo.jar;i18nAPI_v3.jar;classes12.jar;nls_charset12.jar;collections.jar EmpXmlPub.java

Then once class files are generated, we are good to run it.

java.exe -classpath .;xdocore.jar;xmlparserv2-904.jar;versioninfo.jar;i18nAPI_v3.jar;classes12.jar;nls_charset12.jar;collections.jar EmpXmlPub

This will generate the pdf file in the specified output directory. Just open the pdf file and check the output.

That's how simple it is !!!

More on XML publisher in coming articles

Cheers !!!
Neeraj

Oracle Mobile Applications (MSCA) Overview

2007-12-16T22:36:00.000-08:00

Oracle Application provides Mobile capability, allowing for transactions to be executed from the Mobile/RF/Wireless devices.
Oracle Mobile Supply Chain Applications is the key technology enabler for successful deployment of the Warehouse Management System.
The Mobile Supply Chain Applications technology provides a seamless integration of the the Oracle e-business suite with the process critical devices such as RF hand-held devices, bar code printers and scales etc.

Mobile computing devices such as RF Hand-helds allow for executing the transactions such as item picking/packing/putaway at the point of activity thus allowing for reducing the time taken to perform the transaction.
Oracle MSCA applications transform these automated data collecdevices into powerful mobile computers with the capability to execute complex transactions by tightly integrating them with the e-business suite.

Integrating the material handling devices to the Oracle e-business suite is also one of the key functionality of the MSCA. With Oracle mobile supply chain applications the transactions are being performed at the point of activity and directly affecting the live data.

Characteristics
------------------------
* IEEE 802.11B Wireless LAN Standard based
* Integrated barcode reader
* “In the building” Applications like Manufacturing Execution and Warehouse Management
* Emphasis on sub-second response times, automating repetitive transactions
* Field level validation
* Execute transactions at the point of activity
* Eliminates: Latency, Redundancy, Inaccuracy
* Delivers: Accuracy, Currancy, Efficiency
* Requires: Mobility, Usability, Reliability

Networks Supported
--------------------------
MSCA allows a wide variety of wireless networks to choose from based on their requirements and business needs.

* Wide Area Wirelss Networks : Include Cellular, GSM, GPRS and PCS that offer integrated voice/data transmission capabilities
* Local Area Networks for high speed, reliable data communication : Include 400 MHz UHF, 2.4 GHz OpenAir, IEEE 802.11 Frequency Hopping, IEEE 802.11 Direct Sequence.
* MSCA Radio Independent gives the customers the freedom of choice for radio technologies. This enables customers to mix different types of wireless radios to meet different range and throughput requirements within a single environment.

Bar code Technology
--------------------------
Bar codes encode letters and numbers into a concise, simple format (sequences of vertical bars and spaces of variable widths) that machines can read. 2D barcode is capable of encoding significantly more information than 1D bar codes.
Bar code scanners read bar codes, the overhead and fallibility of human data entry is nearly eliminated.

Applications/Transactions supported
-----------------------------------------
Oracle MSCA currently supports transactions of different modules of Discrete Manufacturing and Process Manufacturing.

Many transactions are avaialble on MSCA devices. If WMS is installed and an organization is WMS enabled,there are additional WMS transactions also available.

Mobile Receiving : PO’s, transfers & returns, Cross-dock alerts
Mobile Quality : Quality collections, View specifications
Mobile Inventory : Transfers, Misc. issues & receipts, Cycle counts
Mobile Shipping :Ship confirm, Print shipping documents
Mobile Manufacturing : Issues, Moves, Completions, Scrap, Kanban

More transactions are expected in the future.

Architecture
--------------------------------

* MSCA is a pure Java based implementation.It has
-- Thin Client Architecture
-- Distributed capabilities.
* Implemented on the Oracle Apps Datamodel
-- AOL Menus/Form Functions
-- AK based customization

All Mobile Supply Chain Applications are built in Java, using a Java beans development framework. Each page (or screen) of an application is essentially a Java bean, whose fields and properties have been set by the programmer. By extending these classes or adding new ones, MSCA customers can modify existing applications or create their own.

There are two types of clients available
* Character Mode Clent : This client gives a simple character mode look and uses tab and arrow keys for navigation. It does not support mouse actions.

* GUI Client : It is an extension of the existing client and gives Rich UI feel and supports mouse.

I will deal more on MSCA application aspects, like writing new applications, customizing existing applications etc in my subsequent blogs.

Till then
Cheers !!!
Neeraj

Custom Contexts

2007-11-16T21:15:00.000-08:00

A context is "a set of application-defined attributes that validates and secures an application".
A namespace for a context is created using CREATE CONTEXT syntax. Once a attribute of a namespace has been assigned a value, the value remains for the entire user session or until reset by another value.Contexts are also used for Fine-grained Access Control (row level security).
USERENV is a system generated and maintained namespace that provides environment information.

Creating Custom Contexts
--------------------------------

A namespace for a context is created using CREATE CONTEXT syntax. To be able to create a context namespace, we must associate it with an API.
This ensures that context attributes are set by only those users who have access to the API associated with the Context.
Then we need to define a procedure to set the context attribute based on some condition. We can set the context at the runtime by calling this API.

SQL>
create or replace context tek_app_context using apps.tek_app_context_pkg;
/
SQL>
create or replace package tek_app_context_pkg as
procedure set_tek_app_context (p_user_id NUMBER) ;
end;
/
SQL>
create or replace package body tek_app_context_pkg as
procedure set_tek_app_context ( p_user_id NUMBER) as
begin
n_debug('set app context ' || p_user_id );
dbms_session.set_context( namespace => 'tek_app_context',
attribute => 'user_id',
value => p_user_id );
end set_tek_app_context ;

end tek_app_context_pkg ;
/

Listing Context Namespaces and Attributes
-------------------------------------------------

DBMS_SESSION.LIST_CONTEXT is an API that can be used to determine all namespaces and attributes active for a session, excluding USERENV.
Since the procedure returns a table of type AppCtxTabType - PL/SQL must be used to execute the package and process the returned list.

SQL> set serverout on
SQL> declare
list dbms_session.AppCtxTabTyp; -- type defined in dbms_session pkg
cnt number;
begin
dbms_session.list_context (list, cnt);
for i in 1..cnt loop
dbms_output.put_line( list(i).namespace || ' ' || list(i).attribute ||
' = '|| list(i).value );
end loop;
end;
/

Cheers
~Neeraj

WebADI Creating Custom Integrators

2007-10-31T20:06:00.000-07:00

Web Applications Desktop Integrator (Web ADI) is a self-service Oracle Application.
Web ADI generates Microsoft Excel or Word documents on your desktop, and downloads the data you have selected from the application to be viewed, or modified and uploaded.
It can also be used to load legacy data into Oracle HRMS, and allows you to manage the Mail Merge process
We can use the WebADI to create custom Integrators that can be used to Upload or Download data from the application.

In this blog I will list down the steps involved in creating a Custom Integrator that can be used to upload data into Custom table. Again, here we will be using EMP table for the sake of simplicity.

1) Assign yourself “Oracle Web ADI” responsibility. It has three important functions.

Create Document : Allows using existing Integrators or create custom Integrators
Define Layout : Create a Layout for existing or Custom Integrators
Define Mapping : Create Map for exiting or Custom integrator to load data using flat text files.

2) Update the "Oracle Web ADI Menu" menu to add the Integrator functions menu "HR ADI Seeded Integrator Form Functions" . Otherwise, we won't be able to use integrators as permission is not granted.

3) Now, click on the “Create Document” option.

4) Select the Viewer, generally Excel, to generate the document (Reporting Check box is used when downloading

5) Next, select "HR Integrator Setup" to create a custom Integrator.

6) Next, select the layout as "Integrator Setup" .

7) Next, leave the content as "None".

8) Next, review and select "Create Document".

9) This action creates an Excel Spreadsheet. Make sure you "Enable Macros" when you open it.

Now using this spreadsheet we can create New Integrators. Basically, WebADI uses data from this standard template/layout to create/upload details about new Integrators. Complete the excel sheet with the Integrator Name.

Column

Meaning

Metadata Type

CREATE : create new Integrator

UPDATE : update an Integrator

DOWNLOAD : download Integrator data

Application Short Name

Application Short Name, like AP, XXTEK

Integrator User Name

Integrator User Name, Appear in the drop down list

View Name

Name of the View used to download data, not required for CREATE type.

Form Name

GENERAL if don’t associate Integrator with form

LETTER for mail merge.

Specific form name to associate with Integrator

API Package Name

PROCEDURE or FUNCTION

API Procedure Name

Procedure that inserts/uploads data.

Interface User Name

Interface name displayed on to the user for this integrator on layout page.

Interface Parameter List Name

Name for the parameter list displayed on Layout page

API Type

PROCEDURE or FUNCTION

API Return Type

Date, Number or Varhar2

Click on Oracle --> Upload menu option. This will upload the Integrator definition into the system and the Integrator name would appear on the list of available integrators.

NOTE : Integrator commits only if all rows are uploaded successfully
Make sure that the API + Procedure is defined in the database, as the Integrator uses the procedure parameters to identify the data/columns to handle and shows it in the layout (which we will be creating soon ..)

So far we have only created the custom Integrator. We still need to do a few more things before we can actually begin to use it . ---------------------------------------------------------------------------------
Here's the sample code attached, simple and easy.

CREATE OR REPLACE PACKAGE temp_emp IS
PROCEDURE CREATE_EMP(
p_empno IN NUMBER
, p_ename IN VARCHAR2
, p_job IN VARCHAR2
, p_mgr IN NUMBER
, p_hiredate IN DATE
, p_sal IN NUMBER
, p_comm IN NUMBER
, p_deptno IN NUMBER
);
END temp_emp;

CREATE OR REPLACE PACKAGE BODY temp_emp IS
PROCEDURE CREATE_EMP(
p_empno IN NUMBER
, p_ename IN VARCHAR2
, p_job IN VARCHAR2
, p_mgr IN NUMBER
, p_hiredate IN DATE
, p_sal IN NUMBER
, p_comm IN NUMBER
, p_deptno IN NUMBER
)
IS
BEGIN
insert into emp ( empno, ename, job, mgr, hiredate, sal, comm, deptno);
values (p_empno, p_ename, p_job, p_mgr,p_hiredate,p_sal,p_comm,p_deptno);
EXCEPTION
WHEN OTHERS THEN
null;
END ;
END temp_emp ;
---------------------------------------------------------------------------------

Cheers
~Neeraj

PL SQL Stored Procedure + XML Publisher

2007-10-30T19:08:00.000-07:00

This is one of the least used approaches. However, I am writing this blog just to show if it can be done.
This approach uses a PL/SQL Stored API based concurrent program to generate the XML data. Once we have the XML data, rest of the steps are same as using Report.

1) Create a PL SQL Package Procedure. In this procedure write the logic to fetch the required records and generate the xml file. In this simple approach, we are generating xml file by concatenating the structure with result from query and by using FND_FILE.OUTPUT
Here is the sample code.

----------------------------------
-- Package Spec

CREATE OR REPLACE PACKAGE xx_pls_xml_rep_test AS
PROCEDURE xx_gen_xml_data ( p_error_code OUT NUMBER
,p_error_msg OUT VARCHAR2
,v_dept_no IN NUMBER
);
END xx_pls_xml_rep_test;
/

----------------------------------
-- Package Body

CREATE OR REPLACE PACKAGE BODY xx_pls_xml_rep_test
AS
PROCEDURE xx_gen_xml_data ( p_error_code OUT NUMBER
,p_error_msg OUT VARCHAR2
,v_dept_no IN NUMBER
)
IS
CURSOR emp_details_cur ( v_dept_no VARCHAR2 ) IS
SELECT employee_id empno
, employee_name ename
, job job
, d.dname dept_name
, salary sal
FROM emp e
, dept d
WHERE e.deptno = d.deptno
AND NVL (d.deptno, 0) = NVL (v_dept_no, 0) ;
output_file utl_file.file_type;

/* v_empno NUMBER;
v_empname VARCHAR2(100);
v_job VARCHAR2(100);
v_deptname VARCHAR2(100);
v_sal NUMBER;
v_row emp_details_cur%rowtype;*/

BEGIN
FND_FILE.put_line( FND_FILE.LOG,'Entered in the procedure');

FND_FILE.put_line( FND_FILE.OUTPUT,'' ) ;
FND_FILE.put_line( FND_FILE.OUTPUT,'' ) ;
FOR l_rec IN emp_details_cur ( v_dept_no)
LOOP
FND_FILE.put_line( FND_FILE.LOG,'inside Loop');
FND_FILE.put_line( FND_FILE.OUTPUT,'' ) ;
FND_FILE.put_line( FND_FILE.OUTPUT,'' || l_rec.empno || '' ) ;
FND_FILE.put_line( FND_FILE.OUTPUT,'' || l_rec.ename || '' ) ;
FND_FILE.put_line( FND_FILE.OUTPUT,'' || l_rec.job || '' ) ;
FND_FILE.put_line( FND_FILE.OUTPUT,'' || l_rec.dept_name || '') ;
FND_FILE.put_line( FND_FILE.OUTPUT,'' || l_rec.sal || '' ) ;
FND_FILE.put_line( FND_FILE.OUTPUT,'' ) ;
FND_FILE.put_line( FND_FILE.LOG,'' ) ;
FND_FILE.put_line( FND_FILE.LOG,'' || l_rec.empno || '' ) ;
FND_FILE.put_line( FND_FILE.LOG,'' || l_rec.ename || '' ) ;
FND_FILE.put_line( FND_FILE.LOG,'' || l_rec.job || '' ) ;
FND_FILE.put_line( FND_FILE.LOG,'' || l_rec.dept_name || '') ;
FND_FILE.put_line( FND_FILE.LOG,'' || l_rec.sal || '' ) ;
FND_FILE.put_line( FND_FILE.LOG,'' ) ;
END LOOP;
FND_FILE.put_line( FND_FILE.OUTPUT,'' ) ;
FND_FILE.put_line( FND_FILE.OUTPUT,'' ) ;
FND_FILE.put_line( FND_FILE.LOG,'END ');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line( ' EXCEPTION WHEN OTHERS THEN' || SQLERRM );
END xx_gen_xml_data;
END xx_pls_xml_rep_test;
/

2) Once the api is compiled and tested successfully, create the executable. Note the execution method should be PL SQL Stored Procedure.

3) Register the Concurrent program and any input parameters. Note the output file type should be XML.

Now the remainder of the steps are same as used in "Reports + XML Publisher" approach

4) Run the above program to generate the XML data file and use it to create the RTF template using template builder.

5) We need to create a Data Definition for this object in XML Publisher. To do this, log into Application with "XML Publisher Administrator" responsibility. Click on "Data Definition" function.
While creating Data Definition, ensure that the Data Definition Code remains SAME as the Concurrent Program Short Name. This allows Concurrent manager to bind the output of Conc report prog output to the template, by finding same name template.
Upload the template in Template manager.

6) Now we need to register this template with XML Publisher. Click on the "Templates" tab.
Fill all the required information. Select the Data Definition we created above. Select the language as English and leave territory empty.

7) Now run the program and check the output.

That's it ...

Cheers ..
Neeraj

Reports + XML Publisher

2007-10-17T21:00:00.000-07:00

This is one of the easiest and simplest combinations to use with XML Publisher. This approach allows us to use any existing RDF report to generate XML data and bind it with a Template created in XML Publisher.
Lets go through the steps involved in this approach. In the example used, we will use simple query based on EMP and DEPT tables.

1) Write the RDF with the query to get the required output.

2) Register the executable with Apps. Note the Executable name.

3) Register the Concurrent Program with Apps. Check the output to XML

4) Just Run the report to get the XML output. Save the output file to local disk. We will use this while generating the template.

5) XML Data looks something like this :

6) Now we will create a Template using Oracle XML Publisher Desktop Client. Open a New RTF file and save it. (See guide to learn more on creating RTF templates).

7) We need to create a Data Definition for this object in XML Publisher. To do this, log into Application with "XML Publisher Administrator" responsibility. Click on "Data Definition" function.
While creating Data Definition, ensure that the Data Definition Code remains SAME as the Concurrent Program Short Name. This allows Concurrent manager to bind the output of Conc report prog output to the template, by finding same name template.
Upload the template in Template manager.

8) Now we need to register this template with XML Publisher. Click on the "Templates" tab.
Fill all the required information. Select the Data Definition we created above. Select the language as English and leave territory empty.

9) Voila !! We are done with the setup and just need to run the concurrent program to generate the output in PDF. When we raise the concurrent request, the Concurrent manager looks for a Template with the same Data Definition code as the Concurrent Program short name and binds the XML data with the template a Runtime to generate the required output.

This is how output would look like :

With a little more work on the Template, we can improve the output look based on the requirement without ever having to touch the code.

Cheers !!
~Neeraj

Installing XML Publisher Desktop Client

2007-10-15T20:59:00.001-07:00

To create templates we need to in Desktop Client provided by Oracle. This tool is used for building and testing of layout templates.
This installer consists of a plugin to MS Word for the building of RTF templates and the Template Viewer, for testing and debugging all templates.

Use the following URL to download the client :

http://www.oracle.com/technology/products/xml-publisher/xmlpclient.html

Once download completes, unzip the file and just run the Setup program, which will guide you through the steps in installation.

Once the installation is complete, run MS Word.

As you can see, it creates a Toolbar and a Menu. Explore through these to get to know more of it options and capabilities.

Cheers !!

Find XML Publisher Version

2007-10-14T20:35:00.000-07:00

If we are using XML Publisher, we must know what version is being used. So how do we find out the version of XML Publisher being used ? It kind of tricky as there is no screen that actually shows the product release versions.
This can be done indirectly using different ways. Here are some I found :

1) From the Output document. This is the easiest way and can be used by anyone to find the XML Pub version.
From the output document (PDF, RTF, HTML, Excel) we can extract this information by opening them and checking from some comments.
PDF : Open PDF in Adobe Acrobat Reader (I am using Reader 8). Then File --> Properties --> Description Tab. Look for "PDF Producer" property. It also shows the PDF version used to create the PDF.
RTF : Open RTF in Word. File --> Properties --> Summary Tab. Look for "Comments" prorerty.
HTML, Excel : Open it in text editor. Look for comment like "Generated by".

2) From the Patch applied.
Each release comes on a different patch. So we can use this information to determine the XML Pub version. But you must have database access to do this.

Execute the following query :

SELECT DECODE (bug_number
, '3554613', '4.5.0'
, '3263588', 'XDO.H'
, '3822219', '5.0.0'
, '4236958', '5.0.1'
, '4206181', '5.5.0'
, '4561451', '5.6.0'
, '4905678', '5.6.1'
, '5097966', '5.6.2') PATCH, bug_number
FROM ad_bugs
WHERE bug_number IN
('3554613'
, '3263588'
, '3822219'
, '4236958'
, '4206181'
, '4561451'
, '4905678'
, '5097966');

3) From the class file version of MetaInfo.class
Again with each release the files get update and so do their versions. Bingo !! Read the file and determine the version. Oracle has given the following matrix to determine XML Pub version.
However, you must have access to the Application Server where these files are installed, so its kinda tricky.
This file is available at the following location :
$OA_JAVA/oracle/apps/xdo/common/MetaInfo.class

Metainfo.class	XML Publisher	Patch
115.27	XDO 5.6.2	5097966
115.26	XDO 5.6.1	4905678
115.24	XDO 5.6.0	4561451
115.22	XDO 5.5.0	4206181
115.21	XDO 5.0.1	4236958
115.19	XDO 5.0.0	3822219
115.15	XDO 4.5.0 (XDO.H)	3263588
115.12	XDO 4.3.0	3395631

Shows something like this :
$Header MetaInfo.java 115.19 2004/11/30 02:45:52 tsuzuki noship $

So, now we have enough of information to know what version of XML Pub is installed before we go ahead.

Cheers !!!!

XML Publisher - Overview

2007-10-13T16:58:00.000-07:00

Of late I have been working a lot on XML Publisher. On the consulting side, most reports are being dished out using XML Publisher, or at least the Layout part using most of the current RDF for XML data.
However, most of the presentations or documents on XML Publisher are more like sales or feature presentations. There isn't a real good how-to-do document that actually takes beginners or first time users of XML Publisher through its different capabilities. So as part of my own learning process, I created my very own "XML Publisher for Dummies", a book that I used to document different ways of using XML Pub.

I am going to share some basic and simple codes samples to exploit XML Publisher capabilities. So here we go.

XML Publisher is a powerful product, (though it still has some of its own problems ) that can be used to bind that data set,generated in many ways, to a template. We can create XML data using existing RDF or PL/SQL or Java program or any other source. We can also use the Java XML Publisher APIs for that and much much more.

Here is what XML Pub is from a very simplistic point of view :

XML Publisher is made up of the following core API components:

1) PDF Form Processing Engine The PDF Form Processing Engine creates a PDF document by merging a PDF template with an XML data file.
oracle.apps.xdo.template.FormProcessor;

2) RTF Processor Engine The RTF processor engine takes an RTF template as input. The processor parses the template and creates an XSL-FO template, which can be used input to the FO Engine.
oracle.apps.xdo.template.RTFProcessor;

3) FO Processor Engine
The FO Processor Engine is XML Publisher’s implementation of the W3C XSL-FO
standard. It does not represent a complete implementation of every XSL-FO
component. The FO Processor Merges XSL and XML to produce following output formats: Excel (HTML), PDF, RTF, or HTML.from either of the following two inputs:
• Template (XSL) and Data (XML) combination
• FO object

oracle.apps.xdo.template.FOProcessor;

4) PDF Document Merger The PDF Document Merger class provides a set of utilities to manipulate PDF documents. These utilities provide optional postprocessing of PDF files to merge documents, add page numbering, and set watermarks.
oracle.apps.xdo.common.pdf.util.PDFDocMerger;

5) eText Processor
Converts RTF eText templates to XSL and merges the XSL with XML to produce text
output for EDI and EFT transmissions.

6) Document Processor Engine (XML APIs)
Provides batch processing functionality to access a single API or multiple APIs by
passing a single XML file to specify template names, data sources, languages, output
type, output names, and destinations.