XXAP Supplier Sites Audit

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)*/