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 |