Customer & Supplier Banks(External Banks) Technical Information.................
Suppliers and their Bank Details in Oracle Apps 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;
Suppliers and their Bank Details in Oracle Apps R12
Let us go step by step and finally we can see the consolidated Query. All these queries were tested in R12.1.1 instance.
Query to Fetch Customer Data:
SELECT
cust.party_name customer_name
, cust_acct.cust_account_id
, cust_acct.account_number
, cust_uses.site_use_code
, cust_loc.address1
, cust_loc.address2
, cust_loc.address3
, cust_loc.address4
, cust_loc.city
, cust_loc.postal_code
FROM
hz_parties cust
, hz_cust_accounts cust_acct
, hz_cust_acct_sites_all cust_site
, hz_party_sites party_site
, hz_cust_site_uses_all cust_uses
, hz_locations cust_loc
WHERE cust.party_id = cust_acct.party_id
AND cust_acct.cust_account_id = cust_site.cust_account_id
AND cust_site.cust_acct_site_id = cust_uses.cust_acct_site_id
AND cust_site.party_site_id = party_site.party_site_id
AND party_site.location_id = cust_loc.location_id
AND cust.party_name like '%&party_name%';
Query to Find the Bank Account id based on Customer Info
SELECT
account.ext_bank_account_id -- Link to Bank and Branch Information
,acc_instr.instrument_id
,acc_instr.ext_pmt_party_id
,ext_payer.ext_payer_id
,ext_payer.cust_account_id -- Link to Cust Account Info
,ext_payer.acct_site_use_id
FROM
iby_ext_bank_accounts account
, iby_pmt_instr_uses_all acc_instr
, iby_external_payers_all ext_payer
WHERE 1= 1
AND account.ext_bank_account_id = acc_instr.instrument_id
AND acc_instr.ext_pmt_party_id = ext_payer.ext_payer_id
AND ext_payer.cust_account_id = '&id_from_previus_query';
Query to Find the Bank And Branch Information based on previous Query
SELECT
cust.party_name customer_Party_name
,cust.party_id customer_party_id
,bank.party_name bank_name
,bank_prof.home_country
,account.bank_account_num
,account.bank_account_name
,branch.party_name branch_name
,branch_prof.bank_or_branch_number branch_number
FROM hz_parties bank
, hz_relationships rel
, hz_parties branch
, hz_organization_profiles bank_prof
, hz_organization_profiles branch_prof
, iby_ext_bank_accounts account
, iby_account_owners acc_owner
,hz_parties cust
WHERE 1=1
AND bank.party_id = rel.object_id
and bank.party_type = rel.object_type
AND rel.object_table_name = 'HZ_PARTIES'
AND rel.relationship_code = 'BRANCH_OF'
AND rel.subject_id = branch.party_id
AND rel.subject_type = branch.party_type
AND rel.subject_table_name = 'HZ_PARTIES'
AND bank.party_id = bank_prof.party_id
AND branch.party_id = branch_prof.party_id
AND bank.party_id = account.bank_id
AND branch.party_id = account.branch_id
AND account.ext_bank_account_id = acc_owner.ext_bank_account_id
AND acc_owner.account_owner_party_id = cust.party_id
AND account.ext_bank_account_id ='&ext_bank_accout_id_frm_previous_query';
Consolidated Query to fetch Customer info, Customer Site info, Bank Info and Bank Branch Info:
SELECT
cust.party_name customer_name
, cust_acct.account_number
, cust_acct.cust_account_id
, cust_uses.site_use_code
, cust_loc.address1
, cust_loc.address2
, cust_loc.address3
, cust_loc.address4
, cust_loc.city
, cust_loc.postal_code
, bank.party_name bank_name
, bank_prof.home_country
, branch.party_name branch_name
, branch_prof.bank_or_branch_number branch_number
, account.bank_account_num
, account.bank_account_name
FROM hz_parties bank
, hz_relationships rel
, hz_parties branch
, hz_organization_profiles bank_prof
, hz_organization_profiles branch_prof
, iby_ext_bank_accounts account
, iby_external_payers_all ext_payer
, iby_pmt_instr_uses_all acc_instr
, hz_parties cust
, hz_cust_accounts cust_acct
, hz_cust_acct_sites_all cust_site
, hz_cust_site_uses_all cust_uses
, hz_party_sites party_site
, hz_locations cust_loc
WHERE 1=1
AND bank.party_id = rel.object_id
and bank.party_type = rel.object_type
AND rel.object_table_name = 'HZ_PARTIES'
AND rel.relationship_code = 'BRANCH_OF'
AND rel.subject_id = branch.party_id
AND rel.subject_type = branch.party_type
AND rel.subject_table_name = 'HZ_PARTIES'
AND bank.party_id = bank_prof.party_id
AND branch.party_id = branch_prof.party_id
AND cust_acct.cust_account_id = cust_site.cust_account_id
AND cust_site.cust_acct_site_id = cust_uses.cust_acct_site_id
AND party_site.party_id = cust.party_id
AND party_site.party_site_id = cust_site.party_site_id
AND party_site.location_id = cust_loc.location_id
AND cust.party_id = cust_acct.party_id
AND bank.party_id = account.bank_id
AND branch.party_id = account.branch_id
AND account.ext_bank_account_id = acc_instr.instrument_id
AND acc_instr.ext_pmt_party_id = ext_payer.ext_payer_id
AND ext_payer.cust_account_id = cust_acct.cust_account_id
AND cust_uses.site_use_id = ext_payer.acct_site_use_id
AND cust.party_name like '%$Party_name%';
No comments:
Post a Comment