Friday 6 March 2020

Query to get Bank Audit information for Employee as Supplier

SELECT ieba.ext_bank_account_id, DECODE (ipiuaa.audit_transaction_type,
                 'U', 'Update',
                 'I', 'New',
                 'P', 'Previous'
              ) emp_ba_type, aps.vendor_name emp_as_vendor, apss.vendor_site_code emp_as_supplier_number, bank_name,ieb.bank_number Sort_code, ieba.BANK_ACCOUNT_NUM Bank_Account, 
           ieba.iban, ipiuaa.AUDIT_TIMESTAMP, ipiuaa.audit_user_name
      FROM ap.ap_suppliers aps,       
            per_all_people_f papf,       
            ap.ap_supplier_sites_all apss,       
            apps.iby_ext_bank_accounts   ieba,       
            apps.iby_account_owners      iao,       
            apps.iby_ext_banks_v         ieb,       
            apps.iby_ext_bank_branches_v iebb,
            iby_pmt_instr_uses_all_ac1   ipiuaa
      WHERE aps.vendor_id = apss.vendor_id       
      AND iao.account_owner_party_id = aps.party_id       
      AND ieba.ext_bank_account_id = iao.ext_bank_account_id       
      AND ieb.bank_party_id = iebb.bank_party_id       
      AND ieba.branch_id = iebb.branch_party_id       
      AND ieba.bank_id = ieb.bank_party_id       
      AND aps.employee_id = papf.person_id       
      AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
      and  ipiuaa.INSTRUMENT_ID = ieba.ext_bank_account_id
      and  TRUNC (audit_timestamp)  BETWEEN TRUNC (to_date(:p_start_Date,'YYYY/MM/DD HH24:MI:SS')) AND TRUNC (to_date(:p_end_Date, 'YYYY/MM/DD HH24:MI:SS'))

No comments:

Post a Comment