Sunday, December 04, 2011

Supplier Bank Account Details

Bank Account can be associated with Supplier at following levels
1. Supplier
2. Supplier site
3. Party Site
4. Party Site + Org

This query can find bank accounts associated with a given supplier at any level.



SELECT 'Bank Account At Supplier Site Level' Bank_Account_Level
, sup.segment1
, sup.vendor_name
, epa.org_id
, ss.vendor_site_code
, NULL Party_Site_Code
, eba.bank_account_num
, piu.order_of_preference priority
, eba.ext_bank_account_id
FROM ap_suppliers sup
, ap_supplier_sites_all ss
, iby_external_payees_all epa
, iby_pmt_instr_uses_all piu
, iby_ext_bank_accounts eba
WHERE sup.vendor_id = ss.vendor_id
AND ss.vendor_site_id = epa.supplier_site_id
AND epa.ext_payee_id = piu.ext_pmt_party_id
AND piu.instrument_id = eba.ext_bank_account_id
AND sup.segment1 = '702393'
UNION
SELECT 'Bank Account at Supplier Level'
, sup.segment1
, sup.vendor_name
, epa.org_id
, NULL
, NULL
, eba.bank_account_num
, piu.order_of_preference priority
, eba.ext_bank_account_id
FROM ap_suppliers sup
, iby_external_payees_all epa
, iby_pmt_instr_uses_all piu
, iby_ext_bank_accounts eba
WHERE sup.party_id = epa.payee_party_id
AND epa.ext_payee_id = piu.ext_pmt_party_id
AND piu.instrument_id = eba.ext_bank_account_id
AND sup.segment1 = '702393'
AND supplier_site_id IS NULL
AND party_site_id IS NULL
UNION
SELECT 'Bank Account at Address + Opearting Unit Level'
, sup.segment1
, sup.vendor_name
, epa.org_id
, NULL
, psite.party_site_name
, eba.bank_account_num
, piu.order_of_preference priority
, eba.ext_bank_account_id
FROM ap_suppliers sup
, hz_party_sites psite
, iby_external_payees_all epa
, iby_pmt_instr_uses_all piu
, iby_ext_bank_accounts eba
WHERE sup.party_id = psite.party_id
AND psite.party_site_id = epa.party_site_id
AND epa.ext_payee_id = piu.ext_pmt_party_id
AND piu.instrument_id = eba.ext_bank_account_id
AND sup.segment1 = '702393'
AND supplier_site_id IS NULL
AND epa.org_id IS NOT NULL
UNION
SELECT 'Bank Account at Address Level'
, sup.segment1
, sup.vendor_name
, epa.org_id
, NULL
, psite.party_site_name
, eba.bank_account_num
, piu.order_of_preference priority
, eba.ext_bank_account_id
FROM ap_suppliers sup
, hz_party_sites psite
, iby_external_payees_all epa
, iby_pmt_instr_uses_all piu
, iby_ext_bank_accounts eba
WHERE sup.party_id = psite.party_id
AND psite.party_site_id = epa.party_site_id
AND epa.ext_payee_id = piu.ext_pmt_party_id
AND piu.instrument_id = eba.ext_bank_account_id
AND sup.segment1 = '702393'
AND supplier_site_id IS NULL
AND epa.org_id IS NULL
ORDER BY bank_account_num

9 comments:

  1. hi Sanjay

    This is excellent ..can you also please advice as to how can we show the CREATED BY -name of the person who created that account

    Thanks

    ReplyDelete
    Replies
    1. Sorry for late reply.

      You can add FND_USER table to this query and join USER_ID column of FND_USER with CREATED_BY column of IBY_EXT_BANK_ACCOUNTS and select USER_NAME or DESCRIPTION column of FND_USER. You will have name of person who created that account.

      Delete
  2. Hi, along with this , I need to have audit information. Can you please tell whether below is achievable or not:
    Basically we need to check 2 things as part of Audit requirement.

    • Need to know which supplier bank account field has been updated.
    • Need to know the region of the user or responsibility used which the change is made.

    ReplyDelete
  3. I noticed the query returned some bank accounts which are not seen from the front end. Seems like something is incorrect. The site and header do not have some bank accounts and are still appearing in the output of this query..

    ReplyDelete