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;
Do you think this Article is useful?
Subscribe to:
Post Comments (Atom)
12 Responses to “Query to find Supplier and their Bank Details in R12”
June 24, 2013 at 3:10 AM
IBY_EXTERNAL_PAYEES_ALL - stores Payee (supplier) information.
IBY_EXTERNAL_PAYERS_ALL - stores Payer (customer) information
September 20, 2016 at 6:26 AM
Excellent query provided. Thanks a lot!!!
September 20, 2016 at 6:26 AM
Excellent query provided. Thanks a lot!!!
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.
April 27, 2017 at 12:02 AM
Very very useful, thanks a lot for sharing :)
April 27, 2017 at 10:45 AM
Very useful. Thanks a lot
July 3, 2017 at 12:31 PM
awesome query ..very useful
December 17, 2017 at 11:44 PM
Very very useful, thanks a lot for sharing :)
Send Flowers To Colombia
January 26, 2018 at 5:17 AM
Fantastic.. I've to say this information truly helps me a great deal.Send Gifts To pakistan
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?
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.
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