Thursday, December 16, 2010

Query to find Supplier and their Bank Details in R12




In this post, I tried to show the relationship between Important Supplier tables, TCA Tables, Payment Tables with the help of a query. I hope it will be useful.

Table Introduction:


Ø  IBY_EXTERNAL_PAYEES_ALL :  supplier information and customer information
Ø  IBY_EXT_BANK_ACCOUNTS : external bank account information
Ø  IBY_PMT_INSTR_USES_ALL : Payment instruments assignments
Ø  AP_SUPPLIERS : Stores Supplier Information
Ø  AP_SUPPLIERS_SITES_ALL : Stores Supplier site Information     

Query:               

SELECT                           
             /*Supplier Information*/           
              aps.segment1                       oracle_supplier_number
             ,aps.vendor_id
             ,aps.vendor_name                    supplier_name
             ,aps.party_id                       supplier_party_id
             ,iepa.remit_advice_fax              remit_advice_fax
             ,iepa.remit_advice_email            remit_advice_email
             /* Supplier Site Information */
             ,assa.vendor_site_id
             ,assa.party_site_id                 supplier_party_site_id
             ,assa.vendor_site_code              vendor_site_code
             ,assa.pay_site_flag                 pay_site_flag
             ,assa.purchasing_site_flag          purchasing_site_flag
             ,assa.rfq_only_site_flag            rfq_only_site_flag
             /* Bank Information*/
             ,ieba.ext_bank_account_id
             ,hp.party_name                      Bank_party_name
             ,ieba.bank_account_num              bank_account_num
             ,ieba.bank_account_name             bank_account_name
             ,ieba.country_code                  bank_acct_country_code
             ,ieba.currency_code                 bank_acct_currency_code
             /* Bank Address */
             ,hp.address1                        bank_address_line1
             ,hp.address2                        bank_address_line2
             ,hp.address3                        bank_address_line3
             ,hp.city                            bank_address_city
             ,hp.state                           bank_address_state
             ,hp.postal_code                     bank_address_zip
             ,hp.country                         bank_address_country
             /* Bank Branch Address */
             ,hp1.address1                       branch_address_line1
             ,hp1.address2                       branch_address_line2
             ,hp1.address3                       branch_address_line3
             ,hp1.city                           branch_address_city
             ,hp1.state                          branch_address_state
             ,hp1.postal_code                    branch_address_zip
             ,hp1.country                        branch_address_country
FROM          ap_supplier_sites_all              assa
             ,hz_parties                         hp           
             ,iby_ext_bank_accounts              ieba
             ,iby_external_payees_all            iepa
             ,iby_pmt_instr_uses_all             ipiua            
             ,ap_suppliers                       aps
             ,hz_parties                         hp1           
WHERE        assa.vendor_site_id         =      iepa.supplier_site_id
AND          hp.party_id                 =      ieba.bank_id
AND          ipiua.instrument_id         =      ieba.ext_bank_account_id
AND          ipiua.ext_pmt_party_id      =      iepa.ext_payee_id
AND          assa.vendor_id              =      aps.vendor_id
AND          ieba.branch_id              =      hp1.party_id
AND          ipiua.instrument_type       =      'BANKACCOUNT'
AND          ipiua.payment_flow          =      'DISBURSEMENTS'
AND          ipiua.order_of_preference   =      1;

12 Responses to “Query to find Supplier and their Bank Details in R12”

Anonymous said...
June 24, 2013 at 3:10 AM

IBY_EXTERNAL_PAYEES_ALL - stores Payee (supplier) information.
IBY_EXTERNAL_PAYERS_ALL - stores Payer (customer) information


Oracle ERP Cloud said...
September 20, 2016 at 6:26 AM

Excellent query provided. Thanks a lot!!!


Oracle ERP Cloud said...
September 20, 2016 at 6:26 AM

Excellent query provided. Thanks a lot!!!


Unknown said...
January 9, 2017 at 9:53 PM

Very easy to use query. I'm sure a lot of people finds this info very helpful, just like the ones that I've read about dissertation website and their suppliers.


Unknown said...
April 27, 2017 at 12:02 AM

Very very useful, thanks a lot for sharing :)


Unknown said...
April 27, 2017 at 10:45 AM

Very useful. Thanks a lot


Unknown said...
July 3, 2017 at 12:31 PM

awesome query ..very useful


Unknown said...
December 17, 2017 at 11:44 PM

Very very useful, thanks a lot for sharing :)

Send Flowers To Colombia


Faisal Ahmed said...
January 26, 2018 at 5:17 AM

Fantastic.. I've to say this information truly helps me a great deal.Send Gifts To pakistan


Anonymous said...
April 2, 2018 at 9:08 AM

This helps to get bank details defined at supplier level, but does not have joins to expose bank details defined at supplier site level. Anyone have that?


Anonymous said...
October 23, 2018 at 9:46 PM

Reserve Bank of India (RBI) has launched IFSC codes which stand for Indian Financial System Code.
IFSC Code of IDBI Bank, Coimbatore
IDBI (Industrial Development Bank of India) is a government-owned financial service company which was established in 1964 and it offers banking and financial solutions in the retail and corporate banking arena.


cosmocrat solution said...
May 30, 2022 at 3:23 AM

Great website you have here. It's hard to find high quality texts like you today. I really appreciate people like you!
Digital marketing agency


Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.