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
  • January 8, 2018 | 15 views
  • Comments