Payment Instruments In Oracle Payments : Data Model For Funds Capture [ID 1183453.
1]
Modified 22-MAR-2011 Type BULLETIN Status PUBLISHED
In this Document
Purpose
Scope and Application
Payment Instruments In Oracle Payments : Data Model For Funds Capture
A) Main Bank tables and changes in Bank Accounts data model at R12 level
B) How to create an assignment of an instrument information end and get related information ?
C) Creating an authorization from an AR receipt
D) Creating an authorization from an OM order :
E) Main APIs which manage payment instruments and useful notes most frequent errors
E) APIs to manage Supplier Bank Account
References
Applies to:
Oracle E-Business Suite > Financial Management > Credit to Cash
Information in this document applies to any platform.
Purpose
The purpose of this article is to give information on payment instruments Data Model in scope of
Oracle Payments and Funds Capture feature
Scope and Application
This note is dedicated to users and administrators of Oracle Payment (Funds Capture part).
Payment Instruments In Oracle Payments : Data Model For Funds Capture
A) Main Bank tables and changes in Bank Accounts data model at R12 level
1. Main Bank tables are :
bank tables
iby_ext_bank_accounts
iby_account_owners
iby_pmt_inst_uses_all
iby_external_payers_all
iby_creditcard
iby_pmt_mthd_attrib_appl
Main table joins are:
iby_ext_bank_accounts.ext_bank_account_id = iby_pmt_instr_uses_all.instrument_id
iby_ext_bank_accounts.ext_bank_account_id = iby_account_owners.ext_bank_account_id
iby_account_owners.account_owner_party_id = hz_parties.party_id -- TCA party_id
-- This column represents the party id of the account owner or the joint owner of the external bank
account.
iby_external_payers_all.party_id = hz_parties.party_id -- TCA party_id
iby_pmt_instr_uses_all.ext_pmt_party_id = iby_external_payers_all.ext_payer_id
iby_creditcard.instrid = iby_pmt_inst_uses_all.instrument_id
IBY_EXT_BANK_ACCOUNTS
"This table contains records of the external bank accounts entered manually. This table corresponds
to the Banking details page of supplier or customer.
This table contains bank, branch, masking, encryption details of the external bank accounts.
This table should not be translated for any columns. This is a common table used by both Funds
disbursement and funds Capture module.
IBY_ACCOUNT_OWNERS
This table contains records of the account owners of the external bank accounts entered manually.
This table corresponds to the Banking details page of supplier or customer. When an
external bank account is created a row will be stored in to this table as well as
IBY_EXT_BANK_ACCOUNTS. The external bank account can be shared by multiple parties. When a
joint owner
is added to the external bank account, then one more entry with the joint owner details is entered in
the IBY_ACCOUNT_OWNERS table. This table holds the primary flag, owner party
identifier etc. details of the external bank account [Link] is a common table used by both Funds
disbursement and Funds Capture modules.
IBY_PMT_INSTR_USES_ALL
"This table contains records of the relationship between payment instruments and Payer/Payee. Data
can be entered manually.
IBY_PMT_INSTR_USES_ALL table assigns payment instruments to external party payers and
payees. This table is shared by both the funds disbursement and funds capture modules of Oracle
Payments. The payment instruments can be credit cards, bank accounts, etc."
IBY_EXTERNAL_PAYERS_ALL
This table contains payment details of an external payer automatically created by the source product.
For example when customer or customer site is created manually, system
automatically insert row in to the IBY_EXTERNAL_PAYERS_ALL table.
A customer account can have multiple payers at following levels -
- Party
- Site
- Address
- Address operating unit
This table contains the details of all such payers.
IBY_EXTERNAL_PAYERS_ALL stores payment-related attributes for the funds capture process for
external party payers.
This table stores the data entered through the payment details (header) screen of the customer
account setup UI. "
IBY_CREDITCARD stores the credit card information for a customer
IBY_FNDCPT_TX_EXTENSIONS : The data in this table will be created by the source products
through the public API IBY_FNDCPT_TRXN_PUB using procedures :
Create_Transaction_Extension and Copy_Transaction_Extension to register a Funds capture
transaction.
IBY_FNDCPT_TX_EXTENSIONS table stores the payment processing-specific attributes of a source
product's transaction. The payment entities and attributes include payment method, payment
instrument assignment, and payment method-driven transaction attributes. The payment method-
driven transaction attributes are credit card, security value, voice authorization flag,voice
authorization code, and voice authorization date.
IBY_PMT_MTHD_ATTRIB_APPL stores payment channel or method attribute applicability settings.
eg of SQL statement :
SELECT attribute_applicability
FROM iby_pmt_mthd_attrib_appl
WHERE (payment_flow = 'FUNDS_CAPTURE')
AND (payment_method_code = &pmt_channel)
AND (attribute_code = 'PAYMENT_INSTRUMENT');
IBY_TRXN_CORE contains the details of a payment request that are specific for basic credit card
operations.
2. Changes in Data Model at R12 level
- At R12 level Bank Account management had been passes from AP to IBY
In Release 12, Customer Bank Information is no longer stored in AP_BANK_ACCOUNTS and
AP_BANK_ACCT_USES_ALL tables as they are obsolete.
- IBY manages the Payments Instruments in relationships with TCA and Bank Account data model
This information is stored in the following iPayment (IBY) tables:
1) IBY_EXT_BANK_ACCOUNTS defines the external bank account entity. This table should not be
translated for any columns.
2) IBY_ACCOUNT_OWNERS stores the joint account owners of a bank account.
3) IBY_EXTERNAL_PAYERS_ALL stores payment attributes of the customer.
4) IBY_PMT_INSTR_USES_ALL Payment instruments assignments .
5) IBY_CREDITCARD stores the credit card information for a customer
If you link these tables to the customer and customer address tables (HZ_PARTIES,
HZ_CUST_ACCOUNTS and HZ_CUST_ACCT_SITES_ALL), you will see the bank
account / credit card information.
This new data model allows the bank and bank branch entities to be defined separately allowing users
to establish a hierarchical relationship between them.
Release 11i bank branches and bank accounts data model upgrade will be upgraded prior to upgrade
of transaction data. This includes the following tables in order:
· AP_BANK_BRANCHES
· AP_BANK_ACCOUNTS_ALL
3. Commonly used methods to created or assign instrument and populate related tables (R12)
:
1. API to create External Customer Bank
IBY_EXT_BANKACCT_PUB.create_ext_bank
2. Bank Branch
IBY_EXT_BANKACCT_PUB.create_ext_bank_branch
3. Customer Bank Account
IBY_EXT_BANKACCT_PUB.create_ext_bank_acct
4. Instrument Assignment
IBY_FNDCPT_SETUP_PUB.Set_Payer_Instr_Assignment
5. Payer Attributes
IBY_FNDCPT_SETUP_PUB.Set_Payer_Attributes
6. For the Branch Address
hz_location_v2pub.create_location
hz_party_site_v2pub.create_party_site
7. Contacts
HZ_PARTY_V2PUB.create_person
hz_party_contact_v2pub.create_org_contact
hz_party_contact_v2pub.create_org_contact_role
HZ_CONTACT_POINT_V2PUB.create_contact_point
