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)
7 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!!!
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
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?
Post a Comment