Oracle ERP by Mazeed

Thursday, July 7, 2011

Create XML Publisher Reports based on D2K RDF

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.

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.

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.

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.

This is how output would look like :

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.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).5) XML Data looks something like this :

How to Find XML Publisher Version

a) 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".

b)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');
c) From the class file version of MetaInfo.classAgain 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

Installing XML Publisher Desktop Client (used in MS WORD)

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.

Converting RDF Reports to XM Publisher

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.

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

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

not a valid responsibility for the current user. Please contact your System Administrator.

1) Logon with System Administrator responsibility
2) Navigate to Profile > System, click on User and enter the user name
3) Search for profile 'Applications Start Page'. Delete the value set at user level and save
4) Try then