Query to pull Supplier Bank Account Information in R12

Query to pull active Vendors along with their associated Bank Accounts (External Bank Accounts).
Generally when vendor table is joined to iby_account_owners it returns 2 or more rows and the information does not match with the Accounts we are able to query from Supplier front end. After spending some time I came to know the issues related to this table. See below how I resolved it.

Also refer to metalink Note: 1342935.1 for more information.


  SELECT       ss.vendor_site_id,
               s.vendor_id,
               s.last_update_date,
               s.last_updated_by,
               s.vendor_name,
               s.vendor_name_alt,
               segment1                        vendor_num,
               s.creation_date,
               s.employee_id,
               s.vendor_type_lookup_code,
               s.parent_vendor_id,
               s.num_1099,
               s.type_1099,
               s.start_date_active,
               s.end_date_active,
               s.minority_group_lookup_code,
               s.payment_method_lookup_code,
               s.receipt_required_flag,
               s.enforce_ship_to_location_code,
               s.tax_verification_date,
               s.federal_reportable_flag,
               s.attribute_category,
               s.attribute3,
               s.attribute5,
               s.attribute6,
               s.attribute12,
               s.attribute13,
               s.attribute15,
               s.tax_reporting_name,
               null "site infor stars here",
               ss.vendor_site_id,
               s.tax_reporting_name,
               ss.vendor_site_id,
               ss.last_update_date,
               ss.last_updated_by,
               ss.vendor_site_code,
               ss.vendor_site_code_alt,
               ss.creation_date,
               ss.address_line1,
               ss.address_lines_alt,
               ss.address_line2,
               ss.address_line3,
               ss.address_line4,
               ss.city,
               ss.state,
               ss.zip,
               ss.area_code,
               ss.phone,
               ss.inactive_date,
               ss.payment_method_lookup_code,
               ss.attribute3,
               ss.attribute5,
               ss.attribute6,
               ss.attribute12,
               ss.attribute13,
               ss.attribute15,
               ss.email_address,
               ss.remittance_email,
               --
               null "Bank infor starts here",
               bank.bank_name,
               ext_acct.bank_account_name,
               ext_acct.bank_account_number,
               ext_acct.bank_account_type
               --
          FROM apps.ap_supplier_sites_all   ss,
               apps.ap_suppliers            s,
               apps.iby_account_owners      owner,
               apps.iby_ext_bank_accounts_v ext_acct,
               apps.iby_ext_banks_v    bank,
               apps.iby_ext_bank_branches_v branch
         WHERE s.vendor_id = ss.vendor_id
              --AND ss.pay_site_flag = 'Y'
           AND nvl(ss.inactive_date, SYSDATE) >= SYSDATE
           AND nvl(s.start_date_active, SYSDATE) <= SYSDATE
           AND nvl(s.end_date_active, SYSDATE) >= SYSDATE
           AND nvl(s.enabled_flag, 'Y') = 'Y'
           AND owner.account_owner_party_id(+) = s.party_id
           AND ext_acct.ext_bank_account_id(+) = owner.ext_bank_account_id
           AND bank.bank_party_id(+) = ext_acct.bank_party_id
           AND SYSDATE BETWEEN nvl(ext_acct.start_date, SYSDATE) AND
               nvl(ext_acct.end_date, SYSDATE)
              /*           AND (s.vendor_type_lookup_code <> 'EMPLOYEE' OR
                             s.vendor_type_lookup_code IS NULL)
              */
           AND branch.branch_party_id(+) = ext_acct.branch_party_id
           AND EXISTS
         (SELECT 'ASSIGNMENT'
                  FROM apps.iby_pmt_instr_uses_all piu
                 WHERE (instrument_type = 'BANKACCOUNT' AND
                       piu.instrument_id = ext_acct.ext_bank_account_id)
                    OR ext_acct.ext_bank_account_id IS NULL)
        --AND owner.primary_flag = 'Y'
         ORDER BY s.vendor_name;



If one is interested in retrieving Vendors associated with some bank account only an alternate approach is to simply join iby_ext_bank_accounts table with iby_pmt_instr_uses_all as shown below instead of using EXISTS clause.

piu.instrument_type = 'BANKACCOUNT' AND
piu.instrument_id = ext_acct.ext_bank_account_id