R12 Supplier/Site/Banking Drilldown

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