Audit Supplier Site Bank A/C Changes

Description
Columns: Supplier, Vendor Site Code, Bank Acc, Supp Num, Mod Type, Change Type, Old Bank Acc Num, New Bank Acc Num, Old Bank Acc Nam, New Bank Acc Nam ...
Use to Audit 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
Parameter Name SQL text Validation
Supplier
asa.vendor_name=:supplier_name
LOV
Changed within days
ieba_ac1.audit_timestamp>=sysdate-:days
Number