Sunday, 7 January 2018

Query for Supplier details with banks

SELECT         
                aps.vendor_name|| ' ~ '|| ass.vendor_site_code "Payee_Identifier"
                ,accts.ext_bank_account_id "Payee_Bank_Account_Identifier"
                ,bank.party_name "bank_name"
                ,branch.bank_branch_name "Branch_Name"
                ,accts.country_code "account_country_code"
                ,accts.bank_account_name "Account_Name"
                ,accts.bank_account_num "Account_Number"
                ,accts.currency_code "Account_Currency_Code"
                ,NULL "Allow_International_Payments"
                ,uses.start_date "Account_start_date"
                ,uses.end_date "Account_end_date"
                ,accts.iban "IBAN"
                ,accts.check_digits "check_digits"
                ,accts.bank_account_name_alt "Account_Alternate_Name"
                ,accts.bank_account_type "Bank_account_type"
                ,accts.account_suffix "Account_Suffix"
                ,accts.description "DESCRIPTION"
                ,accts.agency_location_code "Agency_Location_Code"
                ,accts.exchange_rate_agreement_num   "Exchange_Rate_Agreement_Number"
                ,accts.exchange_rate_agreement_type "Exchange_Rate_Agreement_Type"
                ,accts.exchange_rate "EXCHANGE_RATE"
                ,accts.secondary_account_reference "SECONDARY_ACCOUNT_REFERENCE"
           FROM  ap_suppliers aps
                ,ap_supplier_sites_all ass
                ,iby_pmt_instr_uses_all uses
                ,iby_external_payees_all payee
                ,iby_ext_bank_accounts accts
                ,hz_parties bank
                ,ce_bank_branches_v branch
          WHERE uses.instrument_type   = 'BANKACCOUNT'
            AND aps.vendor_id          = ass.vendor_id
            AND aps.party_id           = payee.payee_party_id
            AND payee.ext_payee_id     = uses.ext_pmt_party_id
            AND payee.payment_function = 'PAYABLES_DISB'
            AND uses.instrument_id     = accts.ext_bank_account_id
            AND accts.bank_id          = bank.party_id
            --AND accts.bank_id          = bankprofile.party_id
            AND accts.branch_id         = branch.branch_party_id
            AND aps.end_date_active  IS NULL

            AND ass.inactive_date    IS NULL

1 comment:

  1. You really make it seem really easy together with your presentation but I find this topic to be actually something that I believe I would never understand. It kind of feels too complex and very broad for me. I’m having a look ahead on your subsequent submit, I’ll try to get the cling of it! supplier process audits

    ReplyDelete