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






Case Expressions and Ignoring Nulls in Analytic Functions

IGNORE NULLS: This is not the mission statement of a right-wing political party :), but an optional clause in some of Oracle's analytic functions. Recently I posted a query on OTN that used Last_Value with this clause to simplify another poster's solution to a grouping problem. It occurred to me then that the clause is much more powerful than is generally appreciated, and I'll try to demonstrate that below.

Oracle describes the analytic function First_Value in its SQL manual thus:

'FIRST_VALUE is an analytic function. It returns the first value in an ordered set of values. If the first value in the set is null, then the function returns NULL unless you specify IGNORE NULLS. This setting is useful for data densification.'

Although accurate, the reference to data densification possibly undersells it: When used in conjunction with CASE expressions, IGNORE NULLS allows you effectively to include a WHERE condition on the rows processed by the function, in addition to the partitioning and windowing conditions. This is useful because the latter two conditions have to be defined relative to the current row, whereas the new condition is absolute. Let's take an example based on Oracle's demo HR schema.

Suppose that we want a list of employees, and for each employee we want to assign another employee, perhaps as a mentor. We'll take the following rules:

  • The mentor has to be in the same department
  • The mentor has to earn more than the employee, but not too much more, say up to 1000 more
  • The mentor has to have worked at the company since at least a certain date, say 01-JAN-1998

Subject to these rules, we'll take the highest-earning (or maybe the lowest, let's try both) employee as mentor, and won't worry about tie-breaks for this post.

The objective of maximising (or minimising) the mentor's salary subject to the rules implies the use of Last_Value (or First_Value) with an ordering on salary (we can't use Max because we don't want to return just the salary). The first two conditions can be implemented as partioning and windowing clauses respectively, and operate relative to the current employee. The third condition is absolute though and can't be implemented within the analytic clause itself, which is where IGNORE NULLS comes in. If we make the operand a CASE expression that returns the required details only for employees that meet the required condition and null otherwise, this will implement the required condition. A possible query would be:

SELECT	emp.first_name ||' ' || emp.last_name employee,
	dep.department_name dept, 
        To_Char (emp.hire_date, 'DD-MON-YYYY') hire_date, 
        emp.salary,
	Last_Value (CASE WHEN emp.hire_date < '01-JAN-1998' THEN 
                emp.first_name || ' ' || emp.last_name || ', ' || To_Char (emp.hire_date, 'DD-MON-YYYY') || ', ' || emp.salary
                END IGNORE NULLS)
            OVER (PARTITION BY emp.department_id 
			ORDER BY emp.salary
			RANGE BETWEEN 1 FOLLOWING AND 1000 FOLLOWING) mentor
  FROM employees	    emp
  JOIN departments	    dep
    ON dep.department_id    = emp.department_id
 ORDER BY 2, 4, 1;

Of course, there may be employees who don't have a mentor on our rules, but here are the first few records for the Shipping department (note that I deleted the department column to reduce scrolling):

Last_Value:

EMPLOYEE            HIRE_DATE   SALARY MENTOR
------------------  ----------- ------ -----------------------------------
TJ Olson            10-APR-1999   2100 Curtis Davies, 29-JAN-1997, 3100
Hazel Philtanker    06-FEB-2000   2200 Julia Nayer, 16-JUL-1997, 3200
Steven Markle       08-MAR-2000   2200 Julia Nayer, 16-JUL-1997, 3200
James Landry        14-JAN-1999   2400 Laura Bissot, 20-AUG-1997, 3300
Ki Gee              12-DEC-1999   2400 Laura Bissot, 20-AUG-1997, 3300
James Marlow        16-FEB-1997   2500 Trenna Rajs, 17-OCT-1995, 3500
Joshua Patel        06-APR-1998   2500 Trenna Rajs, 17-OCT-1995, 3500
Martha Sullivan     21-JUN-1999   2500 Trenna Rajs, 17-OCT-1995, 3500
Peter Vargas        09-JUL-1998   2500 Trenna Rajs, 17-OCT-1995, 3500
Randall Perkins     19-DEC-1999   2500 Trenna Rajs, 17-OCT-1995, 3500
Donald OConnell     21-JUN-1999   2600 Renske Ladwig, 14-JUL-1995, 3600

First_Value:

TJ Olson            10-APR-1999   2100 James Marlow, 16-FEB-1997, 2500
Hazel Philtanker    06-FEB-2000   2200 James Marlow, 16-FEB-1997, 2500
Steven Markle       08-MAR-2000   2200 James Marlow, 16-FEB-1997, 2500
James Landry        14-JAN-1999   2400 James Marlow, 16-FEB-1997, 2500
Ki Gee              12-DEC-1999   2400 James Marlow, 16-FEB-1997, 2500
James Marlow        16-FEB-1997   2500 Mozhe Atkinson, 30-OCT-1997, 2800
Joshua Patel        06-APR-1998   2500 Mozhe Atkinson, 30-OCT-1997, 2800
Martha Sullivan     21-JUN-1999   2500 Mozhe Atkinson, 30-OCT-1997, 2800
Peter Vargas        09-JUL-1998   2500 Mozhe Atkinson, 30-OCT-1997, 2800
Randall Perkins     19-DEC-1999   2500 Mozhe Atkinson, 30-OCT-1997, 2800
Donald OConnell     21-JUN-1999   2600 Mozhe Atkinson, 30-OCT-1997, 2800

In general, to find the last value of an expression in a record set ordered by a possibly different expression, with an absolute condition on the records to be considered, use the following form of the function:

Last_Value (CASE WHEN absolute_condition THEN return_expression END IGNORE NULLS)
	OVER (partitioning_clause ORDER BY order_expression windowing_clause)

Note that there is an interesting special case that arises when forming break groups defined by changes in sequential records in an ordered set. The break points can often be obtained by the Lag and Lead analytic functions, and the groups that other records belong to can then be found through expressions of the above type. However, analytic functions can't be nested, so the first step needs to be performed in a separate subquery (inline view or subfactor) -see the first embedded scribd document below for further details on the SQL for this common requirement.

I stated above that we wouldn't worry about tie-breaks in this post, but it's worth mentioning that Oracle allows multiple columns in the ORDER BY only if the windowing clause includes only UNBOUNDED and CURRENT ROW terms. However, you can often pack multiple columns into a single expression by formatting numbers with fixed size and zero-padding etc.

Other Analytic Functions and Null Values
IGNORE NULLS can also be used with Lead and Lag and the new 11.2 function Nth_Value, which extends First_Value, Last_Value to specific ranked values. It is interesting to note that some of the other functions, such as Sum, ignore nulls implicitly:

SELECT 1 + NULL added, Sum (x) summed
  FROM (
SELECT 1 X FROM DUAL
 UNION 
SELECT NULL FROM DUAL);

     ADDED     SUMMED
---------- ----------
                    1

In Oracle null signifies an unknown value and therefore adding null to any number, for example, results in null. Technically, you would therefore expect a sum that includes a null value to result in null, but in fact it does not as the SQL above shows. No doubt practicality won out over theory here.

Again, with other functions such as Sum we can apply a condition by using a CASE expression that returns null or zero if the condition is not met, although not with certain functions such as Avg (but where we could sum and count separately and then calculate the average ourselves).

Other Examples with IGNORE NULLS
Here is the OTN thread mentioned earlier: Custom ranking. The table temp3 contains transactions, some of which are defined to be interest-only transactions based on a condition on two fields. The requirement is to list all non-interest transactions but to summarise interest-only transactions beneath the previous non-interest transaction. My solution, simplifying an earlier proposed solution, involved using Last_Value with IGNORE NULLS in a subfactor to associate the prior non-interest transaction with all transactions, and then doing a GROUP BY in the main query.

BREAK ON trx_grp
WITH grp AS (
SELECT  Last_Value (CASE WHEN tran_id != 'SHD' OR flg = 'N' THEN tran_code END IGNORE NULLS)
            OVER (ORDER BY tran_code) trx_grp,
        tran_id, flg, tran_date, tran_code, amt
 FROM temp3
)
SELECT tran_id, flg, Min (tran_date) "From", Max (tran_date) "To", trx_grp, Sum (amt)
  FROM grp
 GROUP BY tran_id, flg, trx_grp
 ORDER BY trx_grp, flg
/

TRA FLG From      To           TRX_GRP   SUM(AMT)
--- --- --------- --------- ---------- ----------
ADV N   31-OCT-11 31-OCT-11   59586455         50
SHD Y   01-NOV-11 02-NOV-11                    10
PAY N   03-NOV-11 03-NOV-11   59587854         50
PAY N   03-NOV-11 03-NOV-11   59587855         50
SHD Y   03-NOV-11 05-NOV-11                     9
PAY N   06-NOV-11 06-NOV-11   59588286         50
SHD N   06-NOV-11 06-NOV-11   59590668         50
PAY N   07-NOV-11 07-NOV-11   59590669         50

8 rows selected.

I have also used First_Value, Last_Value to help form range-based groups, here (if you can't see the document, 'Forming Range-Based Break Groups with Advanced SQL', it is also in the previous post, up the page):

Using KEEP with the First and Last Functions
Oracle says:

FIRST and LAST are very similar functions. Both are aggregate and analytic functions that operate on a set of values from a set of rows that rank as the FIRST or LAST with respect to a given sorting specification. If only one row ranks as FIRST or LAST, then the aggregate operates on the set with only one element.

and describes their value thus:

When you need a value from the first or last row of a sorted group, but the needed value is not the sort key, the FIRST and LAST functions eliminate the need for self-joins or views and enable better performance.

This seems at first pretty similar to First_Value and Last_Value, so we might ask what they could do in relation to our requirements above. The problem for us is that we can't include a windowing clause as it's not allowed in this case, so we'd have to accept the maximum salary within the allowed date range:

SELECT	emp.first_name ||' ' || emp.last_name employee,
	dep.department_name dept, 
        To_Char (emp.hire_date, 'DD-MON-YYYY') hire_date, 
        emp.salary,
	Max (CASE WHEN emp.hire_date < '01-JAN-1998' THEN 
                emp.first_name || ' ' || emp.last_name || ', ' || To_Char (emp.hire_date, 'DD-MON-YYYY') || ', ' || emp.salary
                END)
	 KEEP (DENSE_RANK LAST ORDER BY emp.salary) 
            OVER (PARTITION BY emp.department_id) mentor
  FROM employees	        emp
  JOIN departments	        dep
    ON dep.department_id    = emp.department_id
 ORDER BY 2, 4, 1;

[dept deleted from output]
EMPLOYEE            HIRE_DATE   SALARY MENTOR
------------------  ----------- ------ -----------------------------------
TJ Olson            10-APR-1999   2100 Adam Fripp, 10-APR-1997, 8200
Hazel Philtanker    06-FEB-2000   2200 Adam Fripp, 10-APR-1997, 8200
Steven Markle       08-MAR-2000   2200 Adam Fripp, 10-APR-1997, 8200
James Landry        14-JAN-1999   2400 Adam Fripp, 10-APR-1997, 8200
Ki Gee              12-DEC-1999   2400 Adam Fripp, 10-APR-1997, 8200
James Marlow        16-FEB-1997   2500 Adam Fripp, 10-APR-1997, 8200
Joshua Patel        06-APR-1998   2500 Adam Fripp, 10-APR-1997, 8200
Martha Sullivan     21-JUN-1999   2500 Adam Fripp, 10-APR-1997, 8200
Peter Vargas        09-JUL-1998   2500 Adam Fripp, 10-APR-1997, 8200
Randall Perkins     19-DEC-1999   2500 Adam Fripp, 10-APR-1997, 8200
Donald OConnell     21-JUN-1999   2600 Adam Fripp, 10-APR-1997, 8200

However, I thought these functions worth mentioning in this post because they can be very useful but seem to be not very well known. People often simulate the functions, in aggregate form anyway, by means of another analytic function, Row_Number, within an inline view but, as is generally the case, the native constructs are simpler and more efficient. I benchmarked various approaches for the aggregation case here (if you can't see the document, 'SQL Pivot and Prune Queries - Keeping an Eye on Performance', it is also in the previous post, up the page):