Using XML Publisher in eBusiness Suite (Part 3)
So far in this series I haven’t actually talked about XML Publisher at all: I wanted to concentrate on the creation of XML data files. This was for a good reason - without XML datafiles XML Publisher is pretty useless. This time I’m going to briefly cover two other XML production methods that are loosely related.
Oracle Reports 6i or 9i
When any Oracle Reports RDF/REP is run under Concurrent Manager you can specify the Output Type in the Concurrent Program. Ordinarilly, this would be set to Text or PDF, but if you set this to XML the Reports Engine will generate an XML file that replicates your Report’s Data Model. The names of tags of the XML elements in the file are normally generated by Reports, but they can be overridden by the programmer.
Below is a fragment of the output that you might get if you set RAXINV (Receivables Invoice Print) to XML:
<?xml version="1.0"?>
<!-- Generated by Oracle Reports version 6.0.8.25.0 -->
<RAXINV>
<LIST_G_ORDER_BY>
<G_ORDER_BY>
<ORDER_BY>Conf Test1</ORDER_BY>
<LIST_G_INVOICE>
<G_INVOICE>
<CUSTOMER_TRX_ID>*</CUSTOMER_TRX_ID>
<TRX_NUMBER>Conf Test1</TRX_NUMBER>
<TRX_TYPE>INV</TRX_TYPE>
<TRX_TYPE_NAME>Invoice</TRX_TYPE_NAME>
<OPEN_RECEIVABLE_FLAG>Y</OPEN_RECEIVABLE_FLAG>
<TRX_DATE>15-APR-07</TRX_DATE>
<CUSTOMER_NUMBER>1005</CUSTOMER_NUMBER>
<INVOICE_CURRENCY_CODE>GBP</INVOICE_CURRENCY_CODE>
<BILL_CUST_NAME>Cope Management</BILL_CUST_NAME>
<BILL_ADDRESS1>Rathbourn Street</BILL_ADDRESS1>
<BILL_ADDRESS2></BILL_ADDRESS2>
<BILL_CITY>Bath</BILL_CITY>
<BILL_POSTAL_CODE>BA14 5PY</BILL_POSTAL_CODE>
<BILL_COUNTRY>GB</BILL_COUNTRY>
<LIST_G_INV_TERM>
<G_INV_TERM>
<TERM_SEQUENCE_NUMBER>1</TERM_SEQUENCE_NUMBER>
<PURCHASE_ORDER_NUMBER>PO67234234</PURCHASE_ORDER_NUMBER>
<TERM_DUE_DATE_FROM_PS>26-APR-07</TERM_DUE_DATE_FROM_PS>
<PRINTING_PENDING>N</PRINTING_PENDING>
<TRX_FREIGHT_AMOUNT>0</TRX_FREIGHT_AMOUNT>
<TERM_NAME>11 Days</TERM_NAME>
<LIST_G_LINE_TOTAL>
<G_LINE_TOTAL>
<LINE_OF_TYPE_FRT>A</LINE_OF_TYPE_FRT>
<ORDER_BY1>1</ORDER_BY1>
<LINK_TO_LINE>1540</LINK_TO_LINE>
<LIST_G_LINES>
<G_LINES>
<LINE_NUMBER>1</LINE_NUMBER>
<LINE_CUSTOMER_TRX_ID>1608</LINE_CUSTOMER_TRX_ID>
<LINE_CUSTOMER_TRX_LINE_ID>1540</LINE_CUSTOMER_TRX_LINE_ID>
<LINE_CHILD_INDICATOR>0</LINE_CHILD_INDICATOR>
<LINE_TYPE>LINE</LINE_TYPE>
<LINE_DESCRIPTION>Commercial Revenue</LINE_DESCRIPTION>
<LINE_QTY_ORDERED>1</LINE_QTY_ORDERED>
<LINE_QTY_INVOICED>1</LINE_QTY_INVOICED>
<LINE_UOM>Each</LINE_UOM>
<LINE_UNIT_SELLING_PRICE>55</LINE_UNIT_SELLING_PRICE>
<LINE_EXTENDED_AMOUNT>55</LINE_EXTENDED_AMOUNT>
....
As you can see, each Group in the Report’s Data Model is mapped to a LIST_… element in the XML; each record return in that group is mapped to an element with the same name as the Group, nested within the LIST element. Usefully, Report-level Placeholders are also output, at the end of the XML:
....
<REPORT_ALL_AMOUNT>945.91</REPORT_ALL_AMOUNT>
<REPORT_ITEM_AMOUNT>805</REPORT_ITEM_AMOUNT>
<REPORT_TAX_AMOUNT>140.91</REPORT_TAX_AMOUNT>
<REPORT_FREIGHT_AMOUNT>140.91</REPORT_FREIGHT_AMOUNT>
<REPORT_TERM_COUNT>12</REPORT_TERM_COUNT>
<REPORT_INV_COUNT>12</REPORT_INV_COUNT>
<PRODUCT_INSTALLED_SO>N</PRODUCT_INSTALLED_SO>
<REPORT_ADJ_ALL_AMOUNT>945.91</REPORT_ADJ_ALL_AMOUNT>
<PAGE_SUM_TAX_AMOUNT>-3.33</PAGE_SUM_TAX_AMOUNT>
<PAGE_SUM_TAX_INCL_AMOUNT>0</PAGE_SUM_TAX_INCL_AMOUNT>
</RAXINV>
Oracle Reports does give you a certain amount of control over the naming of the Element Tags. In the Property Palette for a Group you have the following Attributes:
- XML Tag
- XML Tag Attributes
- Exclude from XML Output
- Outer XML Tag
- Outer XML Tag Attributes
Using Reports as method of producing XML is useful in two situations:
- You want to produce a report that has identical content to one of Oracle’s standard Reports, but need to give it a much better look-and-feel,
- You come from the “old school” of Apps development and the talk of XMLElement functions in my last blog entry scared you silly.
Either way, it is very simple to get some XML out of eBusiness Suite. However, this simplicity is also where (for me at least) the problem with Reports XML lies: you don’t have ultimate control of the structure of the XML. If you need your output to match a supplied XML Schema it can be quite difficult to acheive with Reports (more on XML Schemas next time).
Another problems is if you should need an extra item of data for your version of the report, you’re stuck! You’ll have to create a new version of the RDF and then alter the code in there, which means maintaining a parallel version of the report.
XML Publisher Data Templates
At last, I hear you cry - he’s going to start talking about XML Publisher. Well, sort of. Data Templates is a new-ish add-on feature of XML Publisher 5.6. You manually create an XML file that describes the data that you want and the SQL statements required to get it. This Data Template XML is passed to XML Publisher which then creates the XML data file based on the definition in the Template.
The structure of the Template file will seem very familiar to Reports developers:
<dataTemplate name="dataTemplateName" description="description" defaultPackage="employee_pkg" version="1.0">
<parameters>
<parameter name="department" dataType="character" defaultValue="10"/>
</parameters>
<dataQuery>
<sqlStatement name="Q1">
SELECT empno, ename, sal FROM emp WHERE deptno = :department
</sqlStatement>
</dataQuery>
<dataTrigger name="beforeReport" source="employee_pkg.beforeReportTrigger"/>
<dataStructure>
<group name="G_EMPLOYEE" source="Q1">
<element name="EMPLOYEE_NUMBER" value="EMPNO"/>
<element name="NAME" value="ENAME"/>
<element name="SALARY" value="SAL"/>
</group>
</dataStructure>
</dataTemplate>
I’ve had a bit of a play with what you can do with Data Templates, and whilst the idea sounds great, it gives you little to no feedback when things go wrong and I found it very difficult to meet the requirements of my more data complex files.