Design Patterns for Database Reports with XML Publisher and Email Bursting

In a recent article, A Design Pattern for Oracle eBusiness Audit Trail Reports with XML Publisher, I presented a report that I developed within Oracle eBusiness 12.1, using Oracle XML Publisher. The report was for displaying audit trail data for a particular table, and I proposed that it could be used as a design pattern for audit trail reporting on any eBusiness table. The article focussed on the rather complex SQL, with associated layout structures, needed to render the audit data into a readable format.

In this article, I present a pair of XML Publisher reports intended to serve as design patterns for more general reporting. The reports were again developed within the framework of Oracle’s eBusiness embedded version of XML Publisher, of which there is also a stand-alone version,
Oracle Business Intelligence Publisher, which describes the product (rather ungrammatically) thus:

Oracle BI Publisher is the reporting solution to author, manage, and deliver all your reports and documents easier and faster than traditional reporting tools.

Reports Overview

The reports were developed specifically to serve as models for other reports, from which program constructs could be copied and pasted. For this reason I considered taking for the data sources universally available Oracle metadata such as all_tables or all_objects etc., but found problems with that idea, including:

  • They are not generally ordinary tables, and tend to produce very complex execution plans
  • The possible group structures I found were not quite general enough for my purposes

On the other hand, within Oracle eBusiness I had a number of queries available against the (FND application) metadata tables for concurrent programs that seemed to fit the purpose pretty well, and so decided to use these. As a compromise I created views on the tables with generic column names, so that the reports could be ported to other data sources relatively easily.

Concurrent Programs Data Model
In Oracle eBusiness applications concurrent (i.e. batch) programs have a logical metadata record for the program itself, and (potentially) a set of records for each of the following associated entities:

  • Parameter
  • Request group
  • XML layout template

All of these can have zero records, and for the third entity, only programs of specific type can have any records defined, a structure providing a reasonable degree of generality.

Email Bursting
Business application reports are often used to generate documents for sending to customers, suppliers etc., and increasingly companies prefer to email these out to save costs compared with mailing printed documents. In the past report developers had to code the emailing functionality themselves, usually by means of a co-ordinating program that would loop over the master records and call the reporting tool individually for each one, creating an attachment file that would then be emailed, perhaps by the Unix mailx program. As well as the development effort involved, this approach had the drawback that it scales badly as the number of emails rises owing to the individual calls to the reporting tool (such as Oracle Reports). A printed report will frequently create 10,000 records in little more time than for 1,000 records, whereas for the hand-coded emailing process it would likely take 10 times longer.

The bursting functionality in Oracle XML Publisher solves these problems by allowing the emailing processing to be specified by an XML configuration file, and by creating the files to be emailed in batch just as with printed reports. The data files are created initially in XML format from the data model, and are then merged with layout templates that are emailed by a second program.

The design pattern reports will consist of an email bursting version with a printed version, which would generally be required as a fallback for records with missing or invalid email addresses. We’ll address the obvious security issue with automated emailing programs as part of our design pattern below.

Report Outputs

Examples of complete report outputs for the printed report in .pdf format are included in the attached zip file. Here three pages are given, two showing the listing for the example report programs themselves, and a third showing a non-XML program.

Example Report – XX Example XML CP
This is page 8 of the printed report run with the only the first parameter set, the application.

  • Only the parameters that were actually set are listed after the title
  • The templates region appears because it is an XML report
  • There is only one template for this printed report, with XSL conditionality to include or exclude regions or columns

XX_ERPXMLCP

Example Report – XX Example XML CP (Email)
This is page 9 of the printed report run with the only the first parameter set, the application.

  • Notice that the bursting file column appears because this report has the file attached, using XSL conditionality
  • This email version has two layout templates that are used conditionally for each record depending on whether it’s of XML type or not
  • This record-level choice of template is implemented in the bursting XML file, and is not available in this way for the printed version

XX_ERPXMLCP_EM

Advanced Pricing Report – QP: Java Pricing Engine Warm Up
This is page 44 of the printed report run for the Advanced Pricing application.

  • It shows a non-XML program, and the templates region consequently does not appear
  • Notice that only the summary line appears for the parameters as there are no parameters defined for the program.

QPXJCCWU-2

XML Data Templates and XML Generators

XML Publisher reports require at least one layout template in one of several formats, and also require an executable program to generate the XML data file to merge with the template(s). Oracle supplies a Java executable to generate the data file from an XML data template containing the SQL queries, group structure etc. that the programmer attaches to the concurrent program. This is usually the best approach since it mimimizes the amount of programming required.

It is also possible to use Oracle Reports to generate XML. This can be a reasonable approach for converting legacy reports to XML Publisher to avail of some of the additional features, such as Excel output and email bursting. However, if the data model needs changing it is probably best to extract the SQL and create an XML data template from it.

It’s a really bad idea to write a PL/SQL program to generate the XML data file, being a serious case of ‘reinventing the wheel’ – use Oracle’s Java executable with XML data template instead!

Data Model: Groups, Queries, Parameters, Summaries

As described above, the example report has one master data group and three detail groups. As discussed in my last article Query Query Query, the minimum number of queries required for a group structure hierarchy is the number of groups minus the number of parent groups, which equals three here. The group structure with detail queries linked by bind variables could be depicted as below.

Group Structure Diagram
XML Publisher Model - Groups

A possible query structure for the three queries might then look like the following, where I have included summaries for two of the detail groups, and allowed for report constraints via lexical parameters, to be discussed later.

Query Structure Diagram
XML Publisher Model - Queries

Query Links and Bind Variables
It is possible to link detail queries to their master by XML query links, but according to the manual Oracle® XML Publisher Administration and Developer’s Guide, Release 12:

XML Publisher tests have shown that using bind variables is more efficient than using the link tag.

Linking a detail query to its master by bind variables involves simply referencing the master variable link columns within the detail query preceded by a colon.

Constraints and Parameters
Reports often have constraints depending on input parameters, which may be mandatory or optional. Input parameters are defined in a parameters section within the data template.

Mandatory parameters can be referenced directly as bind variables within the queries (i.e. by preceding the parameter name with a colon).

Optional parameters should normally be referenced indirectly using additional lexical parameters. This allows the exact query required to be executed rather than a composite query with Nvls to represent all possible query combinations. The latter all-purpose queries tend to be more complex and to perform poorly. Lexical parameters are declared and processed in the associated database package, and referenced by preceding them with an ampersand (&).

Note that, confusingly, these lexical parameters are not the same as the lexical tags applicable only within eBusiness that refer to eBusiness flexfields. Like the corresponding user-exits in eBusiness Oracle Reports the lexical tags allow flexfields to be included without their structure being known to the report developer. This is necessary for the standard eBusiness reports but developers of custom reports normally know the structures, and so can avoid these tags altogether (at least for new reports).

Summaries
There are various ways of computing summaries in XML Publisher reports:

  • Within the SQL
  • within the XML group elements
  • within the layout template

SQL Summaries
Often the best way to do the summaries is in the SQL itself. This makes testing simpler because the queries can be run and debugged in SQL Developer or Toad, and also facilitates production support where developers can often run queries in a read-only schema, but can’t change the production code.

In the simple case of summarising detail groups that are defined against a main query, the summaries can be done using analytic functions partitioning by the master key for first level details, and as desired for any subsequent levels. In my example reports, Example Line One is summarised in this way; a subquery factor was used but this is optional here.

Groups defined against additional queries cannot be summarised quite so easily in the SQL. I have summarised the Example Line Two group by adding a subquery factor in the main query purely to do the summaries: Because the line detail is not present in the query we can’t do this by analytic functions, so a grouping summary in a separate subquery is necessary.

XML Group Summaries
Where possible SQL summaries via analytic functions appears best, but in other cases we may wish to consider the non-SQL alternatives. One of these is to define the summaries within the appropriate group as elements in the XML template setting the value attribute to the element in the lower level group to be summarised, and setting the function attribute as desired within a limited set of functions. I have not implemented this method in my examples.

XSL Layout Summaries
A further non-SQL alternative for summaries is to define them within the layout template using the XSL language, and I have implemented the summaries of Example Line Three using this method.

Database Package and Report Triggers

Each XML Publisher has a default package specified in the XML template, that handles parameter processing and implements any triggers required.

Package Spec
The spec declares both input parameters and lexical parameters, as well as the procedures that implement the report triggers (usually three).

Triggers

Before Report

  • This trigger is used to process the lexical parameters, constructing the appropriate where condition depending on which optional parameters have values
  • The example packages show how ranges of character, date and number are processed. I have found it more convenient to pass dates as string parameters.

After Report

  • This trigger can be used to write column headings to the log file for fields that are logged in the group filter

Group Filter

  • This trigger can be used to filter out records depending on some condition, by returning FALSE.
  • I use it in the examples to log selected fields for each record returned.
  • This logging is particularly important for email bursting reports as it enables checking that emails to be sent externally are appropriate before sending in a second step.

Printed Example Report Details

Report Parameters
The report parameters were designed to illustrate the implementation of character, date and number ranges, with an equality join parameter that fits well with the report content, all optional:

  • Application – the eBusiness application id
  • Program name From and To parameters – character range
  • Creation date From and To parameters – date range
  • Number of parameters From and To parameters – number range

Where there is an associated email version it may be worth having an additional Yes/No parameter determining whether to include records that have an email address defined.

Layout Template
There is a single layout template, of .rtf format.

Layout Template Structure
XML Publisher Model - Layout

Layout Template File

Loading...

Notes on Layout

  • Body and Margins
    • The page numbers are in the bottom margin (or footer)
    • The title, parameters and header fields are above the body tag, and repeat on each page of a given header record
  • Conditional Blocks and Columns
    • XSL if-blocks are used to conditionally print blocks such as input parameters, or detail tables depending on data values
    • XSL if-blocks with column qualifiers are used to conditionally print table columns: If there is no bursting file, the entire bursting file column will not appear
  • Sections and Page Breaks
    • The XSL for-each field for the header group uses the section qualifier, which, in conjunction with the page break field, specifies a new page after each header record except the last
    • By default, the above section qualifier would specify that page numbers reset with each header record, but this is overriden here by the initial-page-number field in the footer
  • XSL Summary
    • As discussed in the data model section, the line three summary is implemented in XSL – by the XSL count field

Email Bursting Example Report

Email Address Parameters
In addition to the printed report constraint parameters, three email address parameters have been added.

  • Override email address – setting a value here overrides the record level email address and is useful for testing
  • From and CC email addresses are parameters, which can be defaulted, for flexibility
  • The email address data source is hard-coded in this demo report: normally it would be something like a supplier email address at master record level

Two Step Bursting Process
The first step is to run the concurrent program for the report, which results in an XML file containing the report data to be created on the server. In the second step, a standard concurrent program ‘XML Publisher Report Bursting Program’ is run, passing the request id of the earlier custom report, in order to merge the XML data file with the layout template(s) and send the individual reports by email.

It’s sometimes recommended to trigger the running of the second program within the custom report itself, but it’s probably better not to do this, in order to allow validation of the reports and email addresses before sending them out.

Email Report Logging
The example report logs the email address and other fields in the concurrent program log file, including whether an override email address was specified, where the user can check them before executing the second step that sends out the emails.

Layout Template
There are two layout templates, of .rtf format, which are used conditionally depending on the type of record. The structure of the one used for XML type programs (shown below) has conditional header fields and the third lines block that are absent from the other (not shown).

Layout Template Structure
XML Publisher Model - Layout EM

Layout Template File

Loading...

Notes on Layout

  • The master group is excluded from the template, although its fields are present in the header. The XML Publisher bursting program performs the looping over this group

Email Bursting XML File

<xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi" type="bursting">
<xapi:request select="/XX_ERPXMLCP_EM/LIST_G_HDR/G_HDR">
<xapi:delivery>
<xapi:email server="127.0.0.1" port="25" from="${FROM_EMAIL}" reply-to ="">
<xapi:message id="123" to="${EMAIL_ADDRESS}" cc="${CC_EMAIL}" 
attachment="true" subject="${SUB}">Dear Sir/Madam

Pleae find attached your concurrent program details. 

[Alternatively, leave blank and use output_type=html in document to have attachment go to body instead]</xapi:message>
</xapi:email>
</xapi:delivery>
<xapi:document output="CP_${PROG_NAME}.pdf" output-type="pdf" delivery="123">
<xapi:template type="rtf" location="XDO://CUSTOM.XX_ERPXMLCP_EM.en.US/?getSource=true" filter="//G_HDR[OUTPUT_TYPE!='XML']"></xapi:template>
<xapi:template type="rtf" location="XDO://CUSTOM.XX_ERPXMLCP_EM_XML.en.US/?getSource=true" filter="//G_HDR[OUTPUT_TYPE='XML']"></xapi:template>
</xapi:document>
</xapi:request>
</xapi:requestset>

Notes on Bursting File

  • The email server is specified in the bursting XML configuration file, along with the email addresses to be used, which can be variables from the selected records or hard-coded
  • The layout template is specified, and more than one can be included, as here, with filter conditions depending on selected data fields
  • Here the output is specified to be sent as a .pdf attachment; changing to output type html results in the the output appearing as body text
  • It can be useful to store some or all of the subject (or body) text on the database; the table fnd_messages is used to store the subject here, as records from the fnd tables can be installed automatically via fndload

Code to Download

The zip file Brendan_Model_XML_Reports contains a root folder and three subfolders, as follows:

  • Root – MD120 installation document for the email version. It references a generic Unix script that installs all objects, see A Generic Unix Script for Uploading Oracle eBusiness Concurrent Programs
  • Output – examples of log files for both printed and email versions and pdf outputs for printed version
  • XX_ERPXMLCP – complete code and metadata for the printed version
  • XX_ERPXMLCP_EM – complete code and metadata for the email version (except requires some printed version objects to be installed first)






A Design Pattern for Oracle eBusiness Audit Trail Reports with XML Publisher

Oracle eBusiness applications allow audit history records to be automatically maintained on database tables, as explained in the release 12 System Administrator’s guide, Reporting On AuditTrail Data.

Oracle E-Business Suite provides an auditing mechanism based on Oracle database triggers. AuditTrail stores change information in a “shadow table” of the audited table. This mechanism saves audit data in an uncompressed but “sparse” format, and you enable auditing for particular tables and groups of tables (“audit groups”).

Oracle provides an Audit Query Navigator page where it is possible to search for changes by primary key values. For reporting purposes, the manual says:

You should write audit reports as needed. Audit Trail provides the views of your shadow tables to make audit reporting easier; you can write your reports to use these views.

In fact the views are of little practical use, and it is quite hard to develop reports that are user-friendly, efficient and not over-complex, owing, amongst other things, to the “sparse” data format. However, once you have developed one you can use that as a design pattern and starting point for audit reporting on any of the eBusiness tables.

In this article I provide such a report for auditing external bank account changes on Oracle eBusiness 12.1. The report displays the current record, with lookup information, followed by a list of the changes within an input date range. Only records that have changes within that range are included, and for each change only the fields that were changed are listed. The lookup information includes a list of detail records, making the report overall pretty general in structure: It has a master entity with two independent detail entities, and therefore requires a minimum of two queries. This minimum number of queries is usually the best choice and is what I have implemented (it’s fine to have an extra query for global data, but I don’t have any here). The main query makes extensive use of analytic functions, case expressions and subquery factors to achieve the necessary data transformations as simply and efficiently as possible.

The report is implemented in XML (or BI) Publisher, which is the main batch reporting tool for Oracle eBusiness.

I start by showing sample output from the report, followed by the RTF template. The queries are then documented, starting with query structure diagrams with annotations explaining the logic. A link is included to a zip file with all the code and templates needed to install the report. Oracle provides extensive documentation on the setup of Auditing and developing in XML Publisher, so I will not cover this.

Report Layout
Example Output in Excel Format

  • There are three regions
    • Bank Account Current Record – the master record
    • Owners – first detail block, listing the owners of the bank account
    • Bank Account Changes – the second detail block, listing the audit history. Note that unchanged fields are omitted
  • Note that some audit fields are displayed directly, such as account number, while for others, such as branch number, the display value is on a referenced table

XML Publisher RTF Tempate

  • Note that each audit field has its own row in the table, but the if-block excludes it if both old and new values are null

Audit Query
Query Structure Diagram
Audit Query QSD
Subquery Tabulation

SQL

WITH audit_range AS (
SELECT DISTINCT ext_bank_account_id
  FROM iby_ext_bank_accounts_a aup
 WHERE 1=1
&lp_beg_dat
&lp_end_dat
), audit_union AS (
SELECT ext_bank_account_id acc_id,
       CASE WHEN Substr (audit_true_nulls, 2, 1) = 'Y' OR audit_transaction_type = 'I' THEN '*NULL*' ELSE bank_account_name END acc_name,
       CASE WHEN Substr (audit_true_nulls, 3, 1) = 'Y' OR audit_transaction_type = 'I' THEN '*NULL*' ELSE bank_account_num END acc_num,
       CASE WHEN Substr (audit_true_nulls, 8, 1) = 'Y' OR audit_transaction_type = 'I' THEN '*NULL*' ELSE iban END iban,
       CASE WHEN Substr (audit_true_nulls, 7, 1) = 'Y' OR audit_transaction_type = 'I' THEN '*NULL*' ELSE currency_code END curr,
       CASE WHEN Substr (audit_true_nulls, 6, 1) = 'Y' OR audit_transaction_type = 'I' THEN '*NULL*' ELSE country_code END coun,
       CASE WHEN Substr (audit_true_nulls, 4, 1) = 'Y' OR audit_transaction_type = 'I' THEN 0 ELSE bank_id END bank_id,
       CASE WHEN Substr (audit_true_nulls, 5, 1) = 'Y' OR audit_transaction_type = 'I' THEN 0 ELSE branch_id END branch_id,
       audit_sequence_id seq_id,
       audit_timestamp,
       audit_user_name a_user,
       CASE WHEN audit_transaction_type = 'I' THEN 'INSERT' ELSE 'UPDATE' END a_type
  FROM iby_ext_bank_accounts_a aup
 WHERE aup.ext_bank_account_id IN (SELECT ext_bank_account_id FROM audit_range)
&lp_beg_dat
 UNION
SELECT bac.ext_bank_account_id,
       bac.bank_account_name,
       bac.bank_account_num,
       bac.iban,
       bac.currency_code,
       bac.country_code,
       bac.bank_id,
       bac.branch_id,
       NULL,
       bac.last_update_date,
       usr.user_name,
       NULL
  FROM iby_ext_bank_accounts            bac
  JOIN fnd_user                         usr
    ON usr.user_id                      = bac.last_updated_by
 WHERE bac.ext_bank_account_id IN (SELECT ext_bank_account_id FROM audit_range)
), audit_pairs AS (
SELECT acc_id,
       acc_name,
       bank_id,
       First_Value (bank_id IGNORE NULLS) OVER 
        (PARTITION BY acc_id ORDER BY audit_timestamp, seq_id ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) bank_id_n,
       branch_id,
       First_Value (branch_id IGNORE NULLS) OVER 
        (PARTITION BY acc_id ORDER BY audit_timestamp, seq_id ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) branch_id_n,
       First_Value (acc_name IGNORE NULLS) OVER 
        (PARTITION BY acc_id ORDER BY audit_timestamp, seq_id ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) acc_name_n,
       acc_num,
       First_Value (acc_num IGNORE NULLS) OVER 
        (PARTITION BY acc_id ORDER BY audit_timestamp, seq_id ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) acc_num_n,
       iban,
       First_Value (iban IGNORE NULLS) OVER 
        (PARTITION BY acc_id ORDER BY audit_timestamp, seq_id ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) iban_n,
       curr,
       First_Value (curr IGNORE NULLS) OVER 
        (PARTITION BY acc_id ORDER BY audit_timestamp, seq_id ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) curr_n,
       coun,
       First_Value (iban IGNORE NULLS) OVER 
        (PARTITION BY acc_id ORDER BY audit_timestamp, seq_id ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) coun_n,
       seq_id,
       audit_timestamp,
       a_user,
       a_type
  FROM audit_union
)
SELECT aup.acc_id,
       par_bnk.party_name bank_name,
       par_brn.party_name bra_name,
       orp.bank_or_branch_number bra_num,
       bac.bank_account_name acc_name,
       bac.bank_account_num acc_num,
       bac.iban,
       bac.country_code coun,
       bac.currency_code curr,
       To_Char (bac.creation_date, 'DD-MON-YYYY HH24:MI:SS') creation_date,
       usr.user_name created_by,
       To_Char (aup.audit_timestamp, 'DD-MON-YYYY HH24:MI:SS') a_time,
       aup.a_user,
       aup.a_type,
/*
attr: 1. NULL -> no change; 2. 0/'*NULL*' -> change from null; 3. 'other'-> change from not null
        old: 1 and 2 both return NULL; 3 returns old not null value
        new: only return value for 2 and 3, meaning some change
*/
       CASE WHEN aup.bank_id != 0 THEN par_bnk_o.party_name END bank_name_o,
       CASE WHEN aup.bank_id IS NOT NULL THEN CASE WHEN aup.bank_id_n != 0 THEN par_bnk_o.party_name END END bank_name_n,
       CASE WHEN aup.branch_id != 0 THEN par_brn_o.party_name END bra_name_o,
       CASE WHEN aup.branch_id IS NOT NULL THEN CASE WHEN aup.branch_id_n != 0 THEN par_brn_n.party_name END END bra_name_n,
       CASE WHEN aup.branch_id != 0 THEN orp_o.bank_or_branch_number END bra_num_o,
       CASE WHEN aup.branch_id IS NOT NULL THEN CASE WHEN aup.branch_id_n != 0 THEN orp_n.bank_or_branch_number END END bra_num_n,
       CASE WHEN aup.acc_name != '*NULL*' THEN aup.acc_name END acc_name_o,
       CASE WHEN aup.acc_name IS NOT NULL THEN CASE WHEN aup.acc_name_n != '*NULL*' THEN aup.acc_name_n END END acc_name_n,
       CASE WHEN aup.acc_num != '*NULL*' THEN aup.acc_num END acc_num_o,
       CASE WHEN aup.acc_num IS NOT NULL THEN CASE WHEN aup.acc_num_n != '*NULL*' THEN aup.acc_num_n END END acc_num_n,
       CASE WHEN aup.iban != '*NULL*' THEN aup.iban END iban_o,
       CASE WHEN aup.iban IS NOT NULL THEN CASE WHEN aup.iban_n != '*NULL*' THEN aup.iban_n END END iban_n,
       CASE WHEN aup.curr != '*NULL*' THEN aup.curr END curr_o,
       CASE WHEN aup.curr IS NOT NULL THEN CASE WHEN aup.curr_n != '*NULL*' THEN aup.curr_n END END curr_n,
       CASE WHEN aup.coun != '*NULL*' THEN aup.coun END coun_o,
       CASE WHEN aup.coun IS NOT NULL THEN CASE WHEN aup.coun_n != '*NULL*' THEN aup.coun_n END END coun_n
  FROM audit_pairs                      aup
  JOIN iby_ext_bank_accounts            bac
    ON bac.ext_bank_account_id          = aup.acc_id
  LEFT JOIN hz_parties                  par_bnk
    ON par_bnk.party_id                 = bac.bank_id
  LEFT JOIN hz_parties                  par_bnk_o
    ON par_bnk_o.party_id               = aup.bank_id
  LEFT JOIN hz_parties                  par_bnk_n
    ON par_bnk_n.party_id               = aup.bank_id_n
  LEFT JOIN hz_parties                  par_brn
    ON par_brn.party_id                 = bac.branch_id
  LEFT JOIN hz_organization_profiles    orp
    ON orp.party_id                     = par_brn.party_id
   AND SYSDATE BETWEEN Trunc (orp.effective_start_date) AND Nvl (Trunc (orp.effective_end_date), SYSDATE+1)
  LEFT JOIN hz_parties                  par_brn_o
    ON par_brn_o.party_id               = aup.branch_id
  LEFT JOIN hz_organization_profiles    orp_o
    ON orp_o.party_id                   = par_brn_o.party_id
   AND SYSDATE BETWEEN Trunc (orp_o.effective_start_date) AND Nvl (Trunc (orp_o.effective_end_date), SYSDATE+1)
  LEFT JOIN hz_parties                  par_brn_n
    ON par_brn_n.party_id               = aup.branch_id_n
  LEFT JOIN hz_organization_profiles    orp_n
    ON orp_n.party_id                   = par_brn_n.party_id
   AND SYSDATE BETWEEN Trunc (orp_n.effective_start_date) AND Nvl (Trunc (orp_n.effective_end_date), SYSDATE+1)
  JOIN fnd_user                         usr
    ON usr.user_id                      = bac.created_by
 WHERE aup.seq_id                       IS NOT NULL
&lp_beg_dat
&lp_end_dat
 ORDER BY aup.acc_id, aup.audit_timestamp DESC, aup.seq_id DESC

Owners Query
Query Structure Diagram
Owners Query
Subquery Tabulation

SQL

SELECT par.party_name owner,
       sup.vendor_name,
       sup.segment1
  FROM iby_account_owners               own
  JOIN hz_parties                       par
    ON par.party_id                     = own.account_owner_party_id
  LEFT JOIN ap_suppliers                sup
    ON sup.party_id                     = par.party_id
 WHERE own.ext_bank_account_id          = :ACC_ID

Code for Bank Account Auditing XML Publisher Report

XX_IBYBNKAUDIT

See also A Generic Unix Script for Uploading Oracle eBusiness Concurrent Programs






Design Patterns for Extracting Relational Data from XML

I recently replicated a very complicated Informatica data load from XML files into two Oracle staging tables in a much simpler way using just three SQL insert statements. I was able to load over 13,000 XML files into the tables in around 3 minutes on a windows PC running Oracle 11.2 XE, populating about 2 million records. This article will focus on the general design patterns involved in selecting relational data from XML using SQL in Oracle 11.2.

XML Selection Scenarios

The hierarchical structure of XML files means that the data may be stored at various different levels, but a relational SELECT statement returns data in a flat format. One way of converting between hierarchical and flat formats is to have separate SELECT statements for each level of data, and have any additional post-processing performed after inserting into staging tables. However, it is also possible to flatten the hierarchy within a single SELECT query clause in the case of a strict linear hierarchy.

It is instructive to consider the general case where any number of entities can occur within a linear hierarchy. If we understand how to deal with the case of two entities in a master-detail relationship, along with global values, then we will know how to deal with the general case, and so I will construct just such a test example.

XML Test Data

Here is the test XML file

<elem-0-body>
    <elem-1-global attr-1-global="id_1_global">
        <field-1-global>val_global</field-1-global>
    </elem-1-global>
    <list-1-master>
        <elem-1-master>
            <field-1-master>val_master_m1</field-1-master>
            <list-2-detail>
                  <elem-2-detail>
                       <field-2-detail>val_detail_m1_d1</field-2-detail>
                  </elem-2-detail>
                  <elem-2-detail>
                       <field-2-detail>val_detail_m1_d2</field-2-detail>
                  </elem-2-detail>
            </list-2-detail>
        </elem-1-master>
        <elem-1-master>
            <field-1-master>val_master_m2</field-1-master>
            <list-2-detail>
                  <elem-2-detail>
                       <field-2-detail>val_detail_m2_d1</field-2-detail>
                  </elem-2-detail>
                  <elem-2-detail>
                       <field-2-detail>val_detail_m2_d2</field-2-detail>
                  </elem-2-detail>
            </list-2-detail>
        </elem-1-master>
        <elem-1-master>
            <field-1-master>val_master_m3</field-1-master>
        </elem-1-master>
    </list-1-master>
</elem-0-body>

The file represents data in a master-detail relationship, plus data that are global to the file. As can be seen, there is a global entity, elem-1-global, that occurs exactly once, a master entity, elem-1-master, that may occur multiple times and that contains a child entity, elem-2-detail, that may occur multiple times. Each entity has been assigned a single sample field. One entity has an XML attribute.

There are three master records, having two, two and zero detail records respectively. We will filter out one of the second master’s detail records.

In an article from January 2012, Data Modelling of XML SOAP Documents, I introduced my own diagram notation for hierarchical data structures, and re-use it below, showing the links to entities in relational form. I have added rounded corners to distinguish attributes from fields.

XMLSQL

SQL
Reading the XML File

CREATE TABLE xml_design_pattern (
       xml_field   XMLTYPE) 
       XMLTYPE COLUMN xml_field STORE AS BINARY XML
/
INSERT INTO xml_design_pattern VALUES (
       XMLTYPE (BFilename ('BRENDAN_IN_DIR', 'DESIGN_PATTERN.xml'),                      
                           NLS_Charset_id ('AL32UTF8'))
)
/

Unfiltered, Outer Join on Detail

SELECT /*+ GATHER_PLAN_STATISTICS XMLOJA */ 
       x1.global_id, x1.global_val, x2.master, x3.detail
  FROM xml_design_pattern t,
     XMLTable ('/elem-0-body'
                   PASSING t.xml_field
                   COLUMNS global_id           VARCHAR2(100) PATH 'elem-1-global/@attr-1-global',
                           global_val          VARCHAR2(100) PATH 'elem-1-global/field-1-global',
                           l2_xml              XMLTYPE       PATH 'list-1-master') x1,
     XMLTable ('/list-1-master/elem-1-master'
                   PASSING x1.l2_xml
                   COLUMNS master              VARCHAR2(100) PATH 'field-1-master',
                           l3_xml              XMLTYPE       PATH 'list-2-detail') x2,
     XMLTable ('/list-2-detail/elem-2-detail'
                   PASSING x2.l3_xml
                   COLUMNS detail              VARCHAR2(100) PATH 'field-2-detail') (+) x3
ORDER BY 1, 2, 3, 4

Output

GLOBAL_ID       GLOBAL_VAL      MASTER          DETAIL
--------------- --------------- --------------- --------------------
id_1_global     val_global      val_master_m1
id_1_global     val_global      val_master_m2
id_1_global     val_global      val_master_m3

The result shows that none of the detail records have been joined, while the expected result would be that all detail records would be joined with null detail only for m3.

Filtered, Outer Join on Detail

SELECT x1.global_id, x1.global_val, x2.master, x3.detail
  FROM xml_design_pattern t,
     XMLTable ('/elem-0-body'
                   PASSING t.xml_field
                   COLUMNS global_id           VARCHAR2(100) PATH 'elem-1-global/@attr-1-global',
                           global_val          VARCHAR2(100) PATH 'elem-1-global/field-1-global',
                           l2_xml              XMLTYPE       PATH 'list-1-master') x1,
     XMLTable ('/list-1-master/elem-1-master'
                   PASSING x1.l2_xml
                   COLUMNS master              VARCHAR2(100) PATH 'field-1-master',
                           l3_xml              XMLTYPE       PATH 'list-2-detail') x2,
     XMLTable ('/list-2-detail/elem-2-detail[field-2-detail != "val_detail_m2_d1"]'
                   PASSING x2.l3_xml
                   COLUMNS detail              VARCHAR2(100) PATH 'field-2-detail') (+) x3
ORDER BY 1, 2, 3, 4

Output

no rows selected

The result shows that no records have been returned, while the expected result would be that all master records would be returned, with detail records joined for all except the filtered out detail, with null detail only for m3.

Unfiltered, Outer Joins on all
SQL

SELECT x1.global_id, x1.global_val, x2.master, x3.detail
  FROM xml_design_pattern t,
     XMLTable ('/elem-0-body'
                   PASSING t.xml_field
                   COLUMNS global_id           VARCHAR2(100) PATH 'elem-1-global/@attr-1-global',
                           global_val          VARCHAR2(100) PATH 'elem-1-global/field-1-global',
                           l2_xml              XMLTYPE       PATH 'list-1-master') (+) x1,
    XMLTable ('/list-1-master/elem-1-master'
                   PASSING x1.l2_xml
                   COLUMNS master              VARCHAR2(100) PATH 'field-1-master',
                           l3_xml              XMLTYPE       PATH 'list-2-detail') (+) x2,
     XMLTable ('/list-2-detail/elem-2-detail'
                   PASSING x2.l3_xml
                   COLUMNS detail              VARCHAR2(100) PATH 'field-2-detail') (+) x3
ORDER BY 1, 2, 3, 4

Output

GLOBAL_ID       GLOBAL_VAL      MASTER          DETAIL
--------------- --------------- --------------- --------------------
id_1_global     val_global      val_master_m1   val_detail_m1_d1
id_1_global     val_global      val_master_m1   val_detail_m1_d2
id_1_global     val_global      val_master_m2   val_detail_m2_d1
id_1_global     val_global      val_master_m2   val_detail_m2_d2
id_1_global     val_global      val_master_m3

The result shows that all records are returned, as expected.

Unfiltered, Inner Joins
The attached file shows that, without the outer joins, records are returned only for the first two master records that have detail records, as expected.

Filtered, Outer Joins on all

SQL

SELECT x1.global_id, x1.global_val, x2.master, x3.detail
  FROM xml_design_pattern t,
     XMLTable ('/elem-0-body'
                   PASSING t.xml_field
                   COLUMNS global_id           VARCHAR2(100) PATH 'elem-1-global/@attr-1-global',
                           global_val          VARCHAR2(100) PATH 'elem-1-global/field-1-global',
                           l2_xml              XMLTYPE       PATH 'list-1-master') (+) x1,
     XMLTable ('/list-1-master/elem-1-master'
                   PASSING x1.l2_xml
                   COLUMNS master              VARCHAR2(100) PATH 'field-1-master',
                           l3_xml              XMLTYPE       PATH 'list-2-detail') (+) x2,
     XMLTable ('/list-2-detail/elem-2-detail[field-2-detail != "val_detail_m2_d1"]'
                   PASSING x2.l3_xml
                   COLUMNS detail              VARCHAR2(100) PATH 'field-2-detail') (+) x3
ORDER BY 1, 2, 3, 4

Output

GLOBAL_ID       GLOBAL_VAL      MASTER          DETAIL
--------------- --------------- --------------- --------------------
id_1_global     val_global      val_master_m1   val_detail_m1_d1
id_1_global     val_global      val_master_m1   val_detail_m1_d2
id_1_global     val_global      val_master_m2   val_detail_m2_d2
id_1_global     val_global      val_master_m3

The result shows that, with all joins outer joins, all records are returned except for the filtered-out detail record, as expected.

Filtered, Ansi Outer Joins on all

SQL

SELECT x1.global_id, x1.global_val, x2.master, x3.detail
  FROM xml_design_pattern t
    LEFT JOIN
     XMLTable ('/elem-0-body'
                   PASSING t.xml_field
                   COLUMNS global_id           VARCHAR2(100) PATH 'elem-1-global/@attr-1-global',
                           global_val          VARCHAR2(100) PATH 'elem-1-global/field-1-global',
                           l2_xml              XMLTYPE       PATH 'list-1-master') x1 ON 1=1
    LEFT JOIN
     XMLTable ('/list-1-master/elem-1-master'
                   PASSING x1.l2_xml
                   COLUMNS master              VARCHAR2(100) PATH 'field-1-master',
                           l3_xml              XMLTYPE       PATH 'list-2-detail') x2 ON 1=1
    LEFT JOIN
     XMLTable ('/list-2-detail/elem-2-detail[field-2-detail != "val_detail_m2_d1"]'
                   PASSING x2.l3_xml
                   COLUMNS detail              VARCHAR2(100) PATH 'field-2-detail') x3 ON 1=1
ORDER BY 1, 2, 3, 4

Output: The result in the attached file shows that, with all joins Ansi outer joins, all records are returned except for the filtered-out detail record, as expected.

Notes on SQL

XMLTable and XPath Syntax
Thh XMLTable function represents a rowset retrieved from an XML fragment. The first string within the call represents an XPath expression for the root element defining the rowset in the XML fragment passed. The number of occurrences in the fragment is the rowset cardinality.

The PASSING clause passes in the XMLTYPE field from a prior table or XMLTable instance from which the XML fragment is extracted. For a detail entity, the field will be passed from a master record in a a prior instance, and the instance order matters.

The COLUMNS clause defines the columns that can be included in the select list, and specifies a field for each column by an XPath expression. The expression is relative to the XMLTable root and must specify a single element instance.

XQuery Error (ORA-19279)
A common error in development is:
‘ORA-19279: XPTY0004 – XQuery dynamic type mismatch: expected singleton sequence – got multi-item sequence’
The error occurs when the XPath expression for a column selects more than one instance.

Filtering
Filtering of the XNL elements is effected using standard XPath notation, with conditions placed in square brackets after the element name.

Fields and Attributes
Elements may contain attributes, which are denoted in standard XML syntax by preceding the attribute name with ‘@’. Further details on XML syntax for Oracle, including additional features such as namespaces, can be found in Oracle XML DB Developer’s Guide

Ansi and Oracle Join Syntaxes
Oracle introduced the Ansi standard join syntax in version 9, and it is generally to be preferred to its older proprietary syntax. However, this is debatable in the special case of SQL incorporating XMLTable because joining occurs in a non-standard way within the PASSING and other clauses of XMLTable, and not in the mandatory (except for cross joins) ON clause. In my examples, I have had to join ON 1=1 as a work-around.

Outer Joins
The examples show that outer-joining only the detail table does not work correctly. This appears to be a bug in Oracle 11.2. We have worked around it by outer-joining all tables.

Also note that the outer-join (+) needs to be after the table instance, unlike in standard SQL, where it goes after the join columns.

XMLTYPE Storage Clause
The XMLTYPE column in the table used to load the XML file has a storage clause that can specify either CLOB or BINARY XML (and maybe others) as the storage mode. It is vital for performance to choose BINARY XML if the table is going to be queried using XMLTable.

XPlan Statistics
I included calls to DBMS_XPlan in some of the queries in the attached files, and it can be seen that the CBO cardinalities are extermely inaccurate. This is perhaps not surprising as there is only one record in the table, which is exploded within the SQL, and the CBO is obviously not designed to optimise this.
SQL XML Design Patterns Files






A Layered Approach To Processing XML Web Services

As explained in an earlier post, Data Modelling XML SOAP Documents, I have an approach to calling web services that involves the use of a generic layer that lies between the client applications and low-level APIs for HTTP calls and XML processing. The earlier post introduces the subject, and deals with the data modelling aspects. This post gives high-level, largely diagrammatic, design information for my PL/SQL implementation of the approach. I expect to post on examples of use and results at a later date.

Layer Diagram

External Call Structure

External Call Structure Diagram


External Call Procedures

Procedure Description

Client Side

Client Program Any client program that needs to access web services
Client Converter A program specific to the client to convert between the generic data models of the package and the formats of the client. If there is only one client program then the converter need not be a separate program.

WS Package

Set Header Adds header level nodes into the XML Tree array
Format Attribute Formats an attribute string from name, value and name-space prefix
Add Element (Request) Adds an element node into the XML Tree array
Add Record (Request) Adds a record, consisting of a record header element and child element nodes, into the XML Tree array
Add Element (Response) Adds an element node into the Structure Tree array
Add Record (Response) Adds a record, consisting of a record header element and child element nodes, into the Structure Tree array
Process Web Service Converts the XML Tree array into the SOAP request message, calls the web service and transforms the SOAP response message into the output Data Tree array
Write Output Writes the output Data Tree array

Web Service Call

Web Service Call Structure Diagram

Web Service Call Structure Procedures

Procedure Description

Custom Procedures

Call Web Service Coordinating procedure for the web service call. Note that both request and response writing and reading calls are within loops as the messages can be more than the HTTP maximum chunk size of 32767 bytes
Expand Element (Request) Recursive procedure to create the XML SOAP request from the XML Tree array and other inputs
Delim Field Formats an XML element within its tags
Expand Element (Response) Recursive procedure to convert the initial form of Group Structure Tree List by Parent into the nested form, Group Structure Tree List, used by later processing

Oracle Built-in Packages

UTL_HTTP Oracle HTTP package used to make the HTTP request and read the response
DBMS_LOB Oracle ‘large object’ package used for processing CLOB variables for the full request and response, passed in 32767-byte chunks in the HTTP calls
DBMS_XMLDOM Oracle XML package used to create an XML document and node from the response
XMLTYPE Oracle XML package used to create a variable of XML type for passing to the above package

Populate Tree Call

Populate Tree Call Structure Diagram

Populate Tree Procedures

Procedure Description

Custom Procedures

Populate Tree Main procedure for populating Data Tree List array. First an attempt is made to populate the output tree specified by the input Group Structure Tree List array; if this returns an error, then a second call is made to populate the output tree specified by the standard error group structure; sometimes this too can fail, if the HHTP response is not the expected SOAP message, and this will also be trapped and returned as an error message variable
Check Fault Resets the input Group Structure Tree List array to match the standard SOAP error structure and calls the next procedure to populate the corresponding output tree
Populate Specific Tree Populates the output tree specified by the current Group Structure Tree List array: this may be either that specified by the client application, or the standard error structure
Populate Tree Record Recursive procedure to build the output Data Tree List array using Oracle’s XML APIs

Oracle Built-in Packages

DBMS_XMLDOM ‘The DBMS_XMLDOM package is used to access XMLType objects, and implements the Document Object Model (DOM), an application programming interface for HTML and XML documents’ – Oracle® Database PL/SQL Packages and Types Reference, v11.2
DBMS_XMLProcessor ‘The DBMS_XSLPROCESSOR package provides an interface to manage the contents and structure of XML documents’ – Oracle® Database PL/SQL Packages and Types Reference, v11.2


Data Modelling of XML SOAP Documents

I have been involved in a number of projects where web services are used for interfacing, and have generally found a high level of complexity in their use compared with traditional interfacing methods. One of the areas of complexity lies in converting between the XML messages and the fields and arrays used in conventional programming languages such as Oracle PL/SQL. Often this is handled in an unmodular way with request messages being manually built, and the response message being parsed by individual xpath searches for specific strings.

My approach is to handle these conversions in a generic layer that lies between the client applications and the low-level APIs provided by the programming language for HTTP calls and XML processing. This post deals with a data model and the data structures used in the PL/SQL package that I wrote for calling web services from PL/SQL. I have posted on the program itself here: A Layered Approach To Processing XML Web Services and expect to post on examples of use at a later date.

Web Services
XML web services have become a standard mechanism for interfacing data between applications across the internet. The advantage that they have is that a standard transfer mechanism (HTTP, Hypertext Transfer Protocol) is used, and the data are formatted according to a standard specification, regardless of the technologies in which the applications are implemented. The data formats are described in a Web Services Description Language (WSDL) file, and interfacing is effected using SOAP (Simple Object Access Protocol) messages, as specified by the World Wide Web Consortium (W3C).

Web services form the cornerstone of Service Oriented Architecture (SOA), which Oracle uses in its Application Integration Architecture (AIA). A good acronym dictionary is vital for working in these areas 🙂

Interfacing by web services consists of sending input data as a text string in XML format by an HTTP request, and receiving an HTTP response, also as a text string in XML format.

My Web Service Interface Program
The layer package is intended to handle any data structures that can be represented in XML. On the request side, the client application will call layer APIs to add records and elements to build the request structure without having to write any XML directly, and the layer will construct the XML SOAP message and call the web service. The response side relies on a generic data structure comprising two hierarchical arrays: a structure array that specifies the group structure of the response XML message (or a subset of it), and a data array that holds the data with pointers to the structure array. The structure array forms an input, and is used by the layer to call standard XML APIs (Oracle XML APIs in my implementation) to retrieve the data from the response. The data modelling and conceptual framework are not language-specific, while my implementation is in Oracle PL/SQL.

SOAP Data Model
Both input and output of a web service call include an XML SOAP message, which is a text string consisting of the data in the form of an XML hierarchy. The elements in the hierarchy contain a mandatory name field, plus optional namespace and value, optional list of child elements, and an optional list of attributes. The hierarchy must contain certain standard elements and attributes, within a structure shown in this skeleton SOAP message (modified from an example here SOAP Tutorial/SOAP Syntax, by W3Schools.com ):

In addition to the standard elements and attributes, there may be application specific elements, attributes and values as indicated by the ellipses.The hierarchy of elements can be represented as below, where the group entity on the left represents the fact that a number of elements at a given level may be implicitly grouped, although without this grouping being explicit in the SOAP document. We use this grouping in the data structures on the response side but not on the request side:


Each element in an XML document has a name, an optional value, optional attribute name/value pairs, with the names being optionally name space-qualified, and the element may contain child elements. Please refer to widely available documentation on the SOAP protocol for further information; here’s a link I found useful: A Busy Developer’s Guide to SOAP 1.1

Request Side Data Structures

The diagram shows the main data structures that we use for building the request. Boxes with double borders represent arrays, and a solid arrow represents a pointer from a field to an array element.

The XML Tree List structure is derived from the general model above by treating the list of attributes as a string to be included in the element entity, and corresponds to the array specified by data type xml_tree_list_type in the following table. This array is built from procedure calls by the client application, and is processed internally by a recursive procedure to construct the SOAP request message. Note that as well as the XML tree types, we have defined two additional list structures for convenience of parameter passing:

  • Name Space List for passing name spaces
  • XML Field List as it is often convenient to treat a group of fields as a record, consisting of a parent element and a list of child fields

Name Space List Data Types

Type Name Element Category Description
name_space_type Object Name space
ns_label Character Name space label
ns_address Character Name space address
name_space_list_type Array List of name spaces
(unnamed element) name_space_type* Name space

Field List Data Types

Type Name Element Category Description
field_type Object XML field
field_name Character Field name
field_value Character Field value (optional)
field_list_type Array List of XML fields
(unnamed element) field_type* XML field

XML Tree List Data Types

Type Name Element Category Description
xml_tree_type Object Record in the XML tree holding the data for an XML element
parent_id Integer Index to parent record in the XML tree
ns_label Character Name space label (optional)
field_name Character Field name
field_value Character Field value (optional)
attr_string Character Attribute string, including name and value (optional)
xml_tree_list_type Array XML tree
(unnamed element) xml_tree_type* Record in the XML tree

Response Side Data Structures

The diagram shows the main data structures that we use for processing the response. The broken arrow between the two arrays signifies that each nested child list in the data tree corresponds to a child group record in the structure tree.

The Group Structure Tree List array defines the group structure of the output data structure. To simplify input, this is first set up in an unnested structure, where a field points to its parent, by procedure calls from the client application. The program then converts this into the nested structure shown below it, where the children are included in the parent record, which is more suitable for the later processing. Note that the hierarchy may be, and usually is, a subset of the actual SOAP response, since typically layers are present in a SOAP response message that are not useful for the client application. Also note that in the event of a standard error response being returned from the web service, the group structure is replaced by that for the error response by the program.

  • Each of the arrays (at the top level) has a root element without a parent, and these records have null values other than for their respective child lists
  • The model can represent any number of hierarchy levels

Group Structure Data Types

Type Name Element Category Description
int_list_type Array List of integers
(unnamed element) Integer Integer
structure_tree_type Object Structure tree record
group_name Character The group tag, used as a search string
ns_prefix Character Name space prefix
attr_string Character An attribute string that has to be included in searches where the group name is ambiguous (Oracle JDeveloper uses array as a group tag for arrays, with an attribute to differentiate)
child_list int_list_type*
structure_tree_list_type Array Structure tree list
(unnamed element) structure_tree_type* Structure tree record

Data Structure Data Types

Type Name Element Category Description
child_group_list_type Array List of indexes in the data tree list of child groups of current data tree record
(unnamed element) int_list_type* List of indexes in the data tree list of child records of current group
data_tree_type Object Data tree record
field_list field_list_type* Field list (specified in input side)
child_group_list child_group_list_type* List of indexes in the data tree list of child groups of current data tree record
data_tree_list_type Array Data tree list
(unnamed element) data_tree_type* Data tree record

Generic Data Models
In database design it is well known that overly generic data models lead to poor performance and extra application complexity. The web service interfacing model is of course highly generic, and it should be noted that the same problems may indeed offset the acknowledged standardisation advantages. The data model and structures described here necessarily reflect the genericity of the underlying architecture, while the approach taken is intended to reduce application complexity by moving much of it into a callable module.