Audit Supplier Site Bank A/C Changes

select 
  asa.vendor_name             supplier
, assa.vendor_site_code       vendor_site_code
, NVL( (select ieba.bank_account_num
        from iby_ext_bank_accounts ieba
        where ieba.ext_bank_account_id = ieba_a.ext_bank_account_id
       )
     ,
       ( select ieba_a2.bank_account_num
         from   iby_ext_bank_accounts_a ieba_a2
         where  ieba_a2.ext_bank_account_id = ieba_a.ext_bank_account_id
         and    ieba_a2.audit_transaction_type = 'D'
       )
     )                        bank_acc
, asa.segment1                supp_num
, 'Bank Account'              mod_type
, case ieba_ac1.audit_transaction_type
  when 'I' then 'Insert'
  when 'U' then 'Update'
  when 'D' then 'Delete'
  when 'C' then 'Current'
           else ieba_ac1.audit_transaction_type
  end                         change_type
--
, case when ieba_a.bank_account_num is null
        and nvl(SUBSTR(ieba_a.audit_true_nulls,2,1),'N') = 'N'
  then NULL
  else ieba_a.bank_account_num
  end                         old_bank_acc_num
, case ieba_ac1.audit_transaction_type
  when 'D' then null
  when 'I' then ieba_ac1.bank_account_num
  else case when ieba_a.bank_account_num is null
             and nvl(SUBSTR(ieba_a.audit_true_nulls,2,1),'N') = 'N'
            then null
            else ieba_ac1.bank_account_num
       end
  end                         new_bank_acc_num
--
, case when ieba_a.bank_account_name is null
        and nvl(SUBSTR(ieba_a.audit_true_nulls,3,1),'N') = 'N'
  then NULL
  else ieba_a.bank_account_name
  end                         old_bank_acc_nam
, case ieba_ac1.audit_transaction_type
  when 'D' then null
  when 'I' then ieba_ac1.bank_account_name
  else case when ieba_a.bank_account_name is null
             and nvl(SUBSTR(ieba_a.audit_true_nulls,3,1),'N') = 'N'
            then null
            else ieba_ac1.bank_account_name
       end
  end                         new_bank_acc_nam
--
, ( select coalesce(papf.full_name,fu.description,fu.user_name)
    from fnd_user fu
       , per_all_people_f papf
    where user_name = ieba_ac1.audit_user_name
    and   fu.employee_id = papf.person_id (+)
    and TRUNC(sysdate) between papf.effective_start_date (+)
                           and papf.effective_end_date (+)
  )                           bank_mod_by
, ieba_ac1.audit_timestamp
, ieba_ac1.row_key
from
  ap_suppliers              asa
, ap_supplier_sites_all     assa
, iby_external_payees_all   iepa
, iby_pmt_instr_uses_all    ipiua
, iby_ext_bank_accounts_a   ieba_a
, iby_ext_bank_accounts_ac1 ieba_ac1
where 1=1
and   asa.vendor_id (+)                 = assa.vendor_id
and   assa.vendor_site_id (+)           = iepa.supplier_site_id
and   iepa.ext_payee_id (+)             = ipiua.ext_pmt_party_id
and   ipiua.instrument_id (+)           = ieba_a.ext_bank_account_id
and   ieba_a.row_key                    = ieba_ac1.row_key
--and   asa.segment1                      = 1008
--and   assa.vendor_site_code             = 'NEW YORK'
order by
  ieba_ac1.row_key DESC