XXAP Supplier Sites Audit

Description
Reports all changes to an audited application table.

The report has one row per audit transaction showing the old and new values for all audited columns in a single row.

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

select
'Address1' address1,
:audit_table audit_table,
xxen_util.client_time(aud_a.audit_timestamp) audit_timestamp,
xxen_util.meaning(aud_ac1.audit_transaction_type,'FND_AUDIT_TRANS_TYPE',0)  audit_transaction_type,
xxen_util.user_name(aud_ac1.audit_user_name) audit_transaction_user,
xtab.segment1 "AP_SUPPLIERS.SEGMENT1",
xtab.vendor_name "AP_SUPPLIERS.VENDOR_NAME",
case when aud_a.accts_pay_code_combination_id is null and nvl(substr(aud_a.audit_true_nulls,2,1),'N')='N' then null else aud_a.accts_pay_code_combination_id end old_accts_pay_code_combination,
case aud_a.audit_transaction_type when 'D' then null when 'I' then aud_ac1.accts_pay_code_combination_id else case when aud_a.accts_pay_code_combination_id is null and nvl(substr(aud_a.audit_true_nulls,2,1),'N')='N' then null else aud_ac1.accts_pay_code_combination_id end end new_accts_pay_code_combination,
case when aud_a.allow_awt_flag is null and nvl(substr(aud_a.audit_true_nulls,3,1),'N')='N' then null else aud_a.allow_awt_flag end old_allow_awt_flag,
case aud_a.audit_transaction_type when 'D' then null when 'I' then aud_ac1.allow_awt_flag else case when aud_a.allow_awt_flag is null and nvl(substr(aud_a.audit_true_nulls,3,1),'N')='N' then null else aud_ac1.allow_awt_flag end end new_allow_awt_flag,
case when aud_a.always_take_disc_flag is null and nvl(substr(aud_a.audit_true_nulls,4,1),'N')='N' then null else aud_a.always_take_disc_flag end old_always_take_disc_flag,
case aud_a.audit_transaction_type when 'D' then null when 'I' then aud_ac1.always_take_disc_flag else case when aud_a.always_take_disc_flag is null and nvl(substr(aud_a.audit_true_nulls,4,1),'N')='N' then null else aud_ac1.always_take_disc_flag end end new_always_take_disc_flag,
case when aud_a.awt_group_id is null and nvl(substr(aud_a.audit_true_nulls,5,1),'N')='N' then null else aud_a.awt_group_id end old_awt_group_id,
case aud_a.audit_transaction_type when 'D' then null when 'I' then aud_ac1.awt_group_id else case when aud_a.awt_group_id is null and nvl(substr(aud_a.audit_true_nulls,5,1),'N')='N' then null else aud_ac1.awt_group_id end end new_awt_group_id,
case when aud_a.bank_charge_bearer is null and nvl(substr(aud_a.audit_true_nulls,6,1),'N')='N' then null else aud_a.bank_charge_bearer end old_bank_charge_bearer,
case aud_a.audit_transaction_type when 'D' then null when 'I' then aud_ac1.bank_charge_bearer else case when aud_a.bank_charge_bearer is null and nvl(substr(aud_a.audit_true_nulls,6,1),'N')='N' then null else aud_ac1.bank_charge_bearer end end new_bank_charge_bearer,
case when aud_a.bill_to_location_id is null and nvl(substr(aud_a.audit_true_nulls,7,1),'N')='N' then null else aud_a.bill_to_location_id end old_bill_to_location_id,
case aud_a.audit_transaction_type when 'D' then null when 'I' then aud_ac1.bill_to_location_id else case when aud_a.bill_to_location_id is null and nvl(substr(aud_a.audit_true_nulls,7,1),'N')='N' then null else aud_ac1.bill_to_location_id end end new_bill_to_location_id,
case when aud_a.fob_lookup_code is null and nvl(substr(aud_a.audit_true_nulls,8,1),'N')='N' then null else aud_a.fob_lookup_code end old_fob_lookup_code,
case aud_a.audit_transaction_type when 'D' then null when 'I' then aud_ac1.fob_lookup_code else case when aud_a.fob_lookup_code is null and nvl(substr(aud_a.audit_true_nulls,8,1),'N')='N' then null else aud_ac1.fob_lookup_code end end new_fob_lookup_code,
case when aud_a.freight_terms_lookup_code is null and nvl(substr(aud_a.audit_true_nulls,9,1),'N')='N' then null else aud_a.freight_terms_lookup_code end old_freight_terms_lookup_code,
case aud_a.audit_transaction_type when 'D' then null when 'I' then aud_ac1.freight_terms_lookup_code else case when aud_a.freight_terms_lookup_code is null and nvl(substr(aud_a.audit_true_nulls,9,1),'N')='N' then null else aud_ac1.freight_terms_lookup_code end end new_freight_terms_lookup_code,
case when aud_a.future_dated_payment_ccid is null and nvl(substr(aud_a.audit_true_nulls,10,1),'N')='N' then null else aud_a.future_dated_payment_ccid end old_future_dated_payment_ccid,
case aud_a.audit_transaction_type when 'D' then null when 'I' then aud_ac1.future_dated_payment_ccid else case when aud_a.future_dated_payment_ccid is null and nvl(substr(aud_a.audit_true_nulls,10,1),'N')='N' then null else aud_ac1.future_dated_payment_ccid end end new_future_dated_payment_ccid,
case when aud_a.hold_unmatched_invoices_flag is null and nvl(substr(aud_a.audit_true_nulls,11,1),'N')='N' then null else aud_a.hold_unmatched_invoices_flag end old_hold_unmatched_invoices_fl,
case aud_a.audit_transaction_type when 'D' then null when 'I' then aud_ac1.hold_unmatched_invoices_flag else case when aud_a.hold_unmatched_invoices_flag is null and nvl(substr(aud_a.audit_true_nulls,11,1),'N')='N' then null else aud_ac1.hold_unmatched_invoices_flag end end new_hold_unmatched_invoices_fl,
case when aud_a.invoice_currency_code is null and nvl(substr(aud_a.audit_true_nulls,12,1),'N')='N' then null else aud_a.invoice_currency_code end old_invoice_currency_code,
case aud_a.audit_transaction_type when 'D' then null when 'I' then aud_ac1.invoice_currency_code else case when aud_a.invoice_currency_code is null and nvl(substr(aud_a.audit_true_nulls,12,1),'N')='N' then null else aud_ac1.invoice_currency_code end end new_invoice_currency_code,
case when aud_a.match_option is null and nvl(substr(aud_a.audit_true_nulls,13,1),'N')='N' then null else aud_a.match_option end old_match_option,
case aud_a.audit_transaction_type when 'D' then null when 'I' then aud_ac1.match_option else case when aud_a.match_option is null and nvl(substr(aud_a.audit_true_nulls,13,1),'N')='N' then null else aud_ac1.match_option end end new_match_option,
case when aud_a.payment_currency_code is null and nvl(substr(aud_a.audit_true_nulls,14,1),'N')='N' then null else aud_a.payment_currency_code end old_payment_currency_code,
case aud_a.audit_transaction_type when 'D' then null when 'I' then aud_ac1.payment_currency_code else case when aud_a.payment_currency_code is null and nvl(substr(aud_a.audit_true_nulls,14,1),'N')='N' then null else aud_ac1.payment_currency_code end end new_payment_currency_code,
case when aud_a.payment_method_lookup_code is null and nvl(substr(aud_a.audit_true_nulls,15,1),'N')='N' then null else aud_a.payment_method_lookup_code end old_payment_method_lookup_code,
case aud_a.audit_transaction_type when 'D' then null when 'I' then aud_ac1.payment_method_lookup_code else case when aud_a.payment_method_lookup_code is null and nvl(substr(aud_a.audit_true_nulls,15,1),'N')='N' then null else aud_ac1.payment_method_lookup_code end end new_payment_method_lookup_code,
case when aud_a.pay_date_basis_lookup_code is null and nvl(substr(aud_a.audit_true_nulls,16,1),'N')='N' then null else aud_a.pay_date_basis_lookup_code end old_pay_date_basis_lookup_code,
case aud_a.audit_transaction_type when 'D' then null when 'I' then aud_ac1.pay_date_basis_lookup_code else case when aud_a.pay_date_basis_lookup_code is null and nvl(substr(aud_a.audit_true_nulls,16,1),'N')='N' then null else aud_ac1.pay_date_basis_lookup_code end end new_pay_date_basis_lookup_code,
case when aud_a.prepay_code_combination_id is null and nvl(substr(aud_a.audit_true_nulls,17,1),'N')='N' then null else aud_a.prepay_code_combination_id end old_prepay_code_combination_id,
case aud_a.audit_transaction_type when 'D' then null when 'I' then aud_ac1.prepay_code_combination_id else case when aud_a.prepay_code_combination_id is null and nvl(substr(aud_a.audit_true_nulls,17,1),'N')='N' then null else aud_ac1.prepay_code_combination_id end end new_prepay_code_combination_id,
case when aud_a.ship_to_location_id is null and nvl(substr(aud_a.audit_true_nulls,18,1),'N')='N' then null else aud_a.ship_to_location_id end old_ship_to_location_id,
case aud_a.audit_transaction_type when 'D' then null when 'I' then aud_ac1.ship_to_location_id else case when aud_a.ship_to_location_id is null and nvl(substr(aud_a.audit_true_nulls,18,1),'N')='N' then null else aud_ac1.ship_to_location_id end end new_ship_to_location_id,
case when aud_a.ship_via_lookup_code is null and nvl(substr(aud_a.audit_true_nulls,19,1),'N')='N' then null else aud_a.ship_via_lookup_code end old_ship_via_lookup_code,
case aud_a.audit_transaction_type when 'D' then null when 'I' then aud_ac1.ship_via_lookup_code else case when aud_a.ship_via_lookup_code is null and nvl(substr(aud_a.audit_true_nulls,19,1),'N')='N' then null else aud_ac1.ship_via_lookup_code end end new_ship_via_lookup_code,
case when aud_a.terms_date_basis is null and nvl(substr(aud_a.audit_true_nulls,20,1),'N')='N' then null else aud_a.terms_date_basis end old_terms_date_basis,
case aud_a.audit_transaction_type when 'D' then null when 'I' then aud_ac1.terms_date_basis else case when aud_a.terms_date_basis is null and nvl(substr(aud_a.audit_true_nulls,20,1),'N')='N' then null else aud_ac1.terms_date_basis end end new_terms_date_basis,
case when aud_a.terms_id is null and nvl(substr(aud_a.audit_true_nulls,21,1),'N')='N' then null else aud_a.terms_id end old_terms_id,
case aud_a.audit_transaction_type when 'D' then null when 'I' then aud_ac1.terms_id else case when aud_a.terms_id is null and nvl(substr(aud_a.audit_true_nulls,21,1),'N')='N' then null else aud_ac1.terms_id end end new_terms_id,
case when aud_a.validation_number is null and nvl(substr(aud_a.audit_true_nulls,22,1),'N')='N' then null else aud_a.validation_number end old_validation_number,
case aud_a.audit_transaction_type when 'D' then null when 'I' then aud_ac1.validation_number else case when aud_a.validation_number is null and nvl(substr(aud_a.audit_true_nulls,22,1),'N')='N' then null else aud_ac1.validation_number end end new_validation_number,
case when aud_a.vendor_id is null and nvl(substr(aud_a.audit_true_nulls,23,1),'N')='N' then null else aud_a.vendor_id end old_vendor_id,
case aud_a.audit_transaction_type when 'D' then null when 'I' then aud_ac1.vendor_id else case when aud_a.vendor_id is null and nvl(substr(aud_a.audit_true_nulls,23,1),'N')='N' then null else aud_ac1.vendor_id end end new_vendor_id,
case when aud_a.vendor_site_code is null and nvl(substr(aud_a.audit_true_nulls,24,1),'N')='N' then null else aud_a.vendor_site_code end old_vendor_site_code,
case aud_a.audit_transaction_type when 'D' then null when 'I' then aud_ac1.vendor_site_code else case when aud_a.vendor_site_code is null and nvl(substr(aud_a.audit_true_nulls,24,1),'N')='N' then null else aud_ac1.vendor_site_code end end new_vendor_site_code,
case when aud_a.bank_account_name is null and nvl(substr(aud_a.audit_true_nulls,25,1),'N')='N' then null else aud_a.bank_account_name end old_bank_account_name,
case aud_a.audit_transaction_type when 'D' then null when 'I' then aud_ac1.bank_account_name else case when aud_a.bank_account_name is null and nvl(substr(aud_a.audit_true_nulls,25,1),'N')='N' then null else aud_ac1.bank_account_name end end new_bank_account_name,
case when aud_a.bank_account_num is null and nvl(substr(aud_a.audit_true_nulls,26,1),'N')='N' then null else aud_a.bank_account_num end old_bank_account_num,
case aud_a.audit_transaction_type when 'D' then null when 'I' then aud_ac1.bank_account_num else case when aud_a.bank_account_num is null and nvl(substr(aud_a.audit_true_nulls,26,1),'N')='N' then null else aud_ac1.bank_account_num end end new_bank_account_num,
to_char(aud_a.row_key) audit_row_key,
aud_a.audit_session_id,
aud_a.audit_sequence_id,
aud_a.audit_commit_id
from
ap_supplier_sites_all_a aud_a,
ap_supplier_sites_all_ac1 aud_ac1,
ap_supplier_sites_all tab,
ap_suppliers xtab
where
aud_a.row_key=aud_ac1.row_key and
aud_a.vendor_site_id=aud_ac1.vendor_site_id and
aud_a.vendor_site_id=tab.vendor_site_id(+) and
aud_a.vendor_id=xtab.vendor_id(+) and
1=1
order by
aud_a.row_key desc
/*x in (select dbms_lob.substr(xrrpv.value) from xxen_report_run_param_values xrrpv where xrrpv.parameter_id=386398 and xrrpv.run_id=:run_id)*/
Parameter Name SQL text Validation
Audit Date From
aud_a.audit_timestamp>=:date_from
DateTime
Audit Date To
aud_a.audit_timestamp<=:date_to
DateTime
Audited User
aud_a.audit_user_name=:audited_user
LOV
Download
 
Blitz Report™

Blitz Report™ provides multiple benefits: