-- This is a query to get audit details of dimensions (division and vendor).
-- Due to use of multiple UNION, the query returns ORA-01790 error at the  
-- first ID value (q_t1.div_id).  This can be resolved by using CAST as 
-- shown below, even though the datatype of selection columns matches in 
-- position in the two main queries:
-- CAST(q_t1.div_id  AS  NUMBER) dim_id

SELECT
 CAST(q_t1.div_id  AS  NUMBER) dim_id, 
 'DIVISION'  lkup_type,
 q_t1.crud_type,
 q_t1.div_desc          curr_dim_desc,
 DECODE(q_t1.div_desc, 
    q_t2.div_desc, '-',
    q_t2.div_desc)      prev_dim_desc,
 q_t1.updated_no        updated_no,
 q_t2.updated_no        created_no
FROM
 (sELECT
    lkup_div_pk_id,
    lkup_arch_id,
    div_id,
    div_desc,
    crud_type,
    updated_on
  FROM   lkup_division_arch
  WHERE  created_on > ADD_MONTHS(created_date,-2)
 ) q_t1.
 (SELECT
    lkup_div_pk_id,
    lkup_arch_id,
    div_id,
    div_name,
    crud_type,
    updated_on
  FROM   lkup_division_arch
 ) q_t2
WHERE q_t1.lkup_div_pk_id = q_t2.lkup_div_pk_id
AND   q_t2.lkup_arch_id = (SELECT MAX(al.lkup_arch_id)
                           FROM  lkup_division_arch al
                           WHERE al.lkup_div_pk_id = q_t1.lkup_div_pk_id
                           AND   al.lkup_arch_id < q_t1.lkup_arch_id)

UNION -- division insert only

SELECT
  CAST(di.div_id  AS NUMBER)  dim_id, 
  'DIVISION'  lkup_type,
  di.crud_type,
  di.div_desc    curr_dim_desc,
  '-'            prev_dim_desc,
  di.updated_on,
  di.created_on
FROM   lkup_division_arch di
WHERE  NOT EXISTS
 (SELECT  NULL
  FROM    lkup_division_arch du
  WHERE   di.div_id = du.div_id
  AND     du.crud_type = 'UPDATE')

UNION     -- vendor data

SELECT
  CAST(q_t1.vendor_id  AS NUMBER)  dim_id, 
 'VENDOR'  lkup_type,
 q_t1.crud_type,
 q_t1.vendor_desc          curr_dim_desc,
 DECODE(q_t1.vendor_desc, 
    q_t2.vendor_desc, '-',
    q_t2.vendor_desc)      prev_dim_desc,
 q_t1.updated_no        updated_no,
 q_t2.updated_no        created_no
FROM
 (sELECT
    lkup_vendor_pk_id,
    lkup_arch_id,
    vendor_id,
    vendor_desc,
    crud_type,
    updated_on
  FROM   lkup_vendor_arch
  WHERE  created_on > ADD_MONTHS(created_date,-2)
 ) q_t1.
 (SELECT
    lkup_vendor_pk_id,
    lkup_arch_id,
    vendor_id,
    vendor_name,
    crud_type,
    updated_on
  FROM   lkup_vendor_arch
 ) q_t2
WHERE q_t1.lkup_vendor_pk_id = q_t2.lkup_vendor_pk_id
AND   q_t2.lkup_arch_id = (SELECT MAX(al.lkup_arch_id)
                           FROM  lkup_vendor_arch al
                           WHERE al.lkup_vendor_pk_id = q_t1.lkup_vendor_pk_id
                           AND   al.lkup_arch_id < q_t1.lkup_arch_id)

UNION -- vendor insert only

SELECT
  CAST(di.vendor_id  AS NUMBER)  dim_id, 
  'VENDOR'  lkup_type,
  di.crud_type,
  di.vendor_desc    curr_dim_desc,
  '-'               prev_dim_desc,
  di.updated_on,
  di.created_on
FROM   lkup_vendor_arch di
WHERE  NOT EXISTS
 (SELECT  NULL
  FROM    lkup_vendor_arch du
  WHERE   di.vendor_id = du.vendor_id
  AND     du.crud_type = 'UPDATE')
;