R12 Customer and Supp;ier Bank Accounts in oracle apps

 The data model for storing Banks and Bank Account information has changed for this release of the Oracle Applications Suite.

Banks and their Branches are now each stored as Parties (in HZ_PARTIES) in their own right. They are linked together through Relationships (in HZ_RELATIONSHIP). There is a separate link for both Bank to Branch and also from Branch to Bank.

The Bank Accounts themselves are now stored in the new Oracle Payments Application. All tables are prefixed with the Application Short Name, IBY. The bank accounts themselves are stored in the IBY_EXT_BANK_ACCOUNTS table. The bank_id and branch_id fields link the Bank Account to the relevant Bank and Branch Parties in the HZ_PARTIES table.

Now, linking the Bank Account to the relevant Customer is a bit more involved. The table IBY_ACCOUNT_OWNERS can be used to identify the Customer Party (using the ext_bank_account_id to link the 2 IBY tables) that the Bank Account belongs to. Identifying the Customer Site that the Bank Account is actually attached to is a little bit trickier!

This is done through linking together the following tables IBY_EXTERNAL_PAYERS_ALL and IBY_PMT_INSTR_USES_ALL. A record is created in the Payment Instrument Uses table IBY_PMT_INSTR_USES_ALL for each assignment of a Bank Account. This record is linked to the bank account by matching the ext_bank_account_id to the instrument_id. Now, each Instrument Record links to an External Payer Record held in IBY_EXTERNAL_PAYERS_ALL using the ext_pmt_party_id. It is the External Payer Record that links us to a customer and Customer Site Use.

There is a record stored in the IBY_EXTERNAL_PAYERS_ALL table for every customer site use defined. The IBY_PMT_INSTR_USES_ALL is a pointer to the specific Site Use that the Bank Account has been assigned to.

The following query gives you the links required for matching a Bank Account to its Customer Site Record:

/* Formatted on 3/27/2017 4:38:20 PM (QP5 v5.114.809.3010) */
SELECT   cust.party_name customer_name,
         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,
         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_account_owners acc_owner,
         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_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 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 = 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_acct.cust_account_id = cust_site.cust_account_id
         AND cust_site.cust_acct_site_id = cust_uses.cust_acct_site_id
         AND cust_uses.site_use_id = ext_payer.acct_site_use_id
         AND cust_uses.location = cust_loc.location_id
         AND cust.party_id = cust_acct.party_id; 

The following query gives you the links required for matching a Bank Account to its Supplier Site Record

SELECT party_supp.party_name supplier_name

, aps.segment1 supplier_number

, ass.vendor_site_code supplier_site

, ieb.bank_account_num

, ieb.bank_account_name

, party_bank.party_name bank_name

, branch_prof.bank_or_branch_number bank_number

, party_branch.party_name branch_name

, branch_prof.bank_or_branch_number branch_number

FROM hz_parties party_supp

, ap_suppliers aps

, hz_party_sites site_supp

, ap_supplier_sites_all ass

, iby_external_payees_all iep

, iby_pmt_instr_uses_all ipi

, iby_ext_bank_accounts ieb

, hz_parties party_bank

, hz_parties party_branch

, hz_organization_profiles bank_prof

, hz_organization_profiles branch_prof

WHERE party_supp.party_id = aps.party_id

AND party_supp.party_id = site_supp.party_id

AND site_supp.party_site_id = ass.party_site_id

AND ass.vendor_id = aps.vendor_id

AND iep.payee_party_id = party_supp.party_id

AND iep.party_site_id = site_supp.party_site_id

AND iep.supplier_site_id = ass.vendor_site_id

AND iep.ext_payee_id = ipi.ext_pmt_party_id

AND ipi.instrument_id = ieb.ext_bank_account_id

AND ieb.bank_id = party_bank.party_id

AND ieb.bank_id = party_branch.party_id

AND party_branch.party_id = branch_prof.party_id

AND party_bank.party_id = bank_prof.party_id

ORDER BY party_supp.party_name

, ass.vendor_site_code;