Friday, January 22, 2010
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 Supplier is a bit more involved. The table
IBY_ACCOUNT_OWNERS can be used to identify the Supplier Party that the Bank Account belongs to.
This is done through linking together the following tables IBY_EXTERNAL_PAYEES_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 Payee Record held in IBY_EXTERNAL_PAYEES_ALL using the
ext_pmt_party_id. It is the External Payee Record that links us to a Supplier Party ID (payee_party_id),
Supplier Party Site ID (party_site_id) and Supplier Site ID (supplier_site_id).
There is a record stored in the IBY_EXTERNAL_PAYEES_ALL table for every Supplier Site defined
and for the supplier itself (Bank Accounts can be defined at supplier level too). The
IBY_PMT_INSTR_USES_ALL is a pointer to the specific Site/Supplier that the Bank Account has
been assigned to.
As an added complexity in R12, links to Suppliers are now created in the TCA. Suppliers have a Party
Record and Supplier Sites have Party Site Records. As part of this functionality shift, Suppliers and
their Sites have now moved to AP_SUPPLIERS and AP_SUPPLIER_SITES_ALL (although the
unique keys are still called VENDOR_ID and VENDOR_SITE_ID respectively!!). The old PO tables
used in 11i and before are now created as views which link the Supplier Records to their related TCA records (i.e. PO_VENDORS links AP_SUPPLIERS with HZ_PARTIES and
PO_VENDOR_SITES_ALL links AP_SUPPLIER_SITES_ALL with HZ_PARTY_SITES).