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