R12 Supplier/Site/Banking Drilldown

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