Supplier level
PO_VENDORS.party_id = HZ_PARTIES.party_id
PO_VENDORS.employee_id = PER_EMPLOYEES_CURRENT_X.employee_id(+)
PO_VENDORS.employee_id = PER_ALL_PEOPLE_F.person_id(+)
Site level
AP_SUPPLIER_SITES_ALL.vendor_id = PO_VENDORS.vendor_id
AP_SUPPLIER_SITES_ALL.location_id = HZ_LOCATIONS.location_id(+)
AP_SUPPLIER_SITES_ALL.ship_to_location_id =
HR_LOCATIONS.location_id(+)
AP_SUPPLIER_SITES_ALL.org_id = AP_SYSTEM_PARAMETERS_ALL.org_id
AP_SUPPLIER_SITES_ALL.org_id = HR_OPERATING_UNITS.org_id
AP_SUPPLIER.SITES_ALL.party_site_id=HZ_PARTY_SITES.party_site_id
Bank level
IBY_EXT_BANK_ACCOUNTS.ext_bank_account_id = IBY_PMT_INSTR_USES_ALL.instrument_id
IBY_PMT_INSTR_USES_ALL.instrument_type = ‘BANKACCOUNT’
IBY_PMT_INSTR_USES_ALL.ext_pmt_party_id = IBY_EXTERNAL_PAYEES_ALL.ext_payee_id
IBY_EXTERNAL_PAYEES_ALL.payee_party_id = PO_VENDORS.party_id
IBY_EXTERNAL_PAYEES_ALL.party_site_id = AP_SUPPLIER_SITES_ALL.party_site_id
IBY_EXTERNAL_PAYEES_ALL.payment_function = ‘PAYABLES_DISB’
IBY_EXTERNAL_PAYEES.supplier_site_id =
AP_SUPPLIER_SITES_ALL.vendor_site_id
--Bank as Party
IBY_EXT_BANK_ACCOUNTS.bank_id = HZ_PARTIES.party_id(+)
IBY_EXT_BANK_ACCOUNTS.bank_id =
HZ_ORGANIZATION_PROFILES.party_id(+)
IBY_EXT_BANK_ACCOUNTS.branch_id =
CE_BANK_BRANCHES_V.branch_party_id(+)
Sample Vendor Name - 'XXABC Company Inc.'
Vendor Site Code – 'DIGITAL INC’
Steps to Drill down to Bank Account info in R12
--------------------------------------------------------------------
1. Get the Supplier Party Id
select * from ap_suppliers where vendor_name = 'XXABC Company Inc.';
party_id 3310541
2. Get the Supplier Site Info
select * from apps.ap_supplier_sites_all where vendor_id = 654980
and org_id = 484;
vendor_site_id 1342690
party_site_id 2339546
vendor_site_code 'DIGITAL INC’
3. Get the External Payee Info
select * from apps.iby_external_payees_all where party_site_id = 2339546 and org_id = 484;
ext_payee_id 3005139
payee_party_id 3310541
supplier_site_id 1342690
4. Get the Payment Instrument Info
select * from apps.iby_pmt_instr_uses_all where ext_pmt_party_id = 3005139 and sysdate between nvl(start_date, sysdate) and nvl(end_date, sysdate);
instrument_id 743177
5. Get the External Bank Account Info
select * from apps.iby_ext_bank_accounts where ext_bank_account_id = 743177;
-----------------------------------------------------------------------------------------------------------
Query used to get the active bank account info for a given Supplier Name, Org Id and Site Code. – R12
SELECT accts.ext_bank_account_id,
accts.bank_account_name,
accts.masked_iban AS iban,
accts.currency_code,
uses.order_of_preference,
uses.start_date,
uses.end_date,
fc.name AS currency_name,
bank.party_name AS bank_name,
accts.masked_bank_account_num AS bank_account_number,
branch.bank_branch_name,
branch.branch_number,
bankProfile.bank_or_branch_number AS bank_number,
branch.eft_swift_code,
accts.bank_account_type
FROM apps.IBY_PMT_INSTR_USES_ALL uses,
apps.IBY_EXTERNAL_PAYEES_ALL payee,
apps.IBY_EXT_BANK_ACCOUNTS accts,
apps.FND_CURRENCIES_VL fc,
apps.HZ_PARTIES bank,
apps.HZ_ORGANIZATION_PROFILES bankProfile,
apps.CE_BANK_BRANCHES_V branch,
apps.po_vendors pv,
apps.ap_supplier_sites_all sites
WHERE pv.vendor_name = 'XXABC Company Inc.'
and pv.vendor_id = sites.vendor_id
and sites.vendor_site_code = ‘DIGITAL INC’
and sites.org_id = 484
and uses.instrument_type = 'BANKACCOUNT'
AND payee.ext_payee_id = uses.ext_pmt_party_id
AND payee.payee_party_id = pv.party_id
AND payee.payment_function = 'PAYABLES_DISB'
AND payee.party_site_id = sites.party_site_id
AND payee.org_id = sites.org_id
AND payee.supplier_site_id = sites.vendor_site_id
AND uses.instrument_id = accts.ext_bank_account_id
AND fc.currency_code(+) = accts.currency_code
AND SYSDATE BETWEEN NVL (accts.start_date, SYSDATE)
AND NVL (accts.end_date, SYSDATE)
AND SYSDATE BETWEEN NVL (uses.start_date, SYSDATE)
AND NVL (uses.end_date, SYSDATE)
AND accts.bank_id = bank.party_id(+)
AND accts.bank_id = bankProfile.party_id(+)
AND accts.branch_id = branch.branch_party_id(+)
AND SYSDATE BETWEEN TRUNC(bankProfile.effective_start_date(+)) AND NVL(TRUNC(bankProfile.effective_end_date(+)),SYSDATE + 1)
ORDER BY ORDER_OF_PREFERENCE ASC
