Documentation on Importing Internal and External Bank Account In R12

Documentation on Importing Internal and External Bank Account In R12: Bank API's And Q/A [ID 948993.1]
		Modified 16-NOV-2011     Type PROBLEM     Status MODERATED

 

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

Applies to: Oracle Receivables - Version: 12.0.0 to 12.1.3 - Release: 12.0 to 12.1
Oracle Cash Management - Version: 12.0.0 to 12.1.3   [Release: 12 to 12.1]
Oracle Payables - Version: 12.0.0 to 12.1.3   [Release: 12.0 to 12.1]
Information in this document applies to any platform.
Symptoms1. Documentation on Importing Internal and External Bank Account In R12: Bank API's?
2. Solution Approach used by some customers?
3. Any Suggestions?
4. Oracle Financial Pre-Upgrade Patch?
5. Other Latest Patches?
6. API's Issues ?
7. Q & A as reference?

CauseDocumentation is now provided as attachment to this note. Solution

(1). This note has attachments for two areas of Bank API's and these are:

1. Internal:  Release 12 Bank Data Model Public APIs

2. External: Process of importing external bank account data in R12

(2). Solution Approach used by some customers:

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

(3). Suggestions:

1. Please note: this do not have any wrapper API's to create/update bank/branch address or contacts.
The API's as mentioned for branch addresses and contacts are all owned by HZ so the you would
do well to go through the documentation of those API's.

2. It's strongly recommended that the solution be tested rigorously on a test instance in all
aspects to ensure data integrity and then moved over to production. Also note that in the CE API's
documentation, we have provided a way to check whether the branches or banks have been created
properly.

(4). Oracle Financial Pre-Upgrade Patch:

R12.0.:

Consolidated upgrade fixes
ce patch/115/sql cebalupg.sql 120.6.12000000.2
ce patch/115/sql cebattcu.sql 120.1.12000000.2
ce patch/115/sql cebucopy.sql 120.16.12000000.3
ce patch/115/sql cebuinst.sql 120.37.12000000.2
ce patch/115/sql ceburgrp.sql 120.21.12000000.2
ce patch/115/sql cecestmt.sql 120.5.12000000.4
ce patch/115/sql ceupgrole.sql 120.7.12000000.2

R12.1:

1. Please review:    R12.1: Oracle Financials Pre-Upgrade Patch -- Supplemental List for EBS CUP
2. Confirm: the following:
Consolidated pre-upgrade fixes
ce patch/115/sql cebalupg.sql 120.6.12010000.2
ce patch/115/sql cebattcu.sql 120.1.12010000.2
ce patch/115/sql cebucopy.sql 120.17.12010000.3
ce patch/115/sql cebuinst.sql 120.37.12010000.2
ce patch/115/sql ceburgrp.sql 120.21.12010000.2
ce patch/115/sql cecestmt.sql 120.7.12010000.2
ce patch/115/sql ceupgrole.sql 120.7.12010000.2

(5). Other Latest Patches:

Oracle Applications Release 12.0.6:

1). UNABLE TO FIND UNCLEARED PAYMENTS TO CLEAR
Patch can be applied over 12.0.6
cevw.odf 120.141.12000000.45

2). BANK STATEMENT LOADER NOT POPULATING ORIGINAL AMOUNT AND CHARGE AMOUNT FOR MT940
Patch can be applied over 12.0.4
ce patch/115/sql ceextbab.pls 120.12.12000000.13 +

Oracle Applications Release 12.1.x and above:

1). This patch fixes the set_bank_branch_end_date procedure in the CE_BANK_PUB API
patch/115/sql/ceextbas.pls 120.3.12010000.5
patch/115/sql/ceextbab.pls 120.12.12010000.16
patch/115/import/US/ce12amg.ldt 120.42.12010000.32

2). R12 CASH MANAGEMENT SYSTEM PARAMETERS FORM NOT SECURED AFTER RELEASE 12 UPGRADE
ce patch/115/odf cevw.odf 120.150.12010000.30
The patch can be applied over 12.1 and above.

3). UNABLE TO FIND UNCLEARED PAYMENTS TO CLEAR
Patch can be applied over 12.1.1 and above
cevw.odf 120.150.12010000.33

(6). API Issues:

1. Issue 1: CE_BANK_PUB.update_bank_branch API:
When selecting the OBJECT_VERSION_NUMBER from hz_code_assignments using
CLASS_CATEGORY = 'BANK_BRANCH_TYPE' get no value returned.

Resolution 1: 12.1 Ce_bank_bub Issue When Updating Bank Branch
The CE_BANK_PUB.update_bank_branch API has four parameters for object versions:

p_bch_object_version_number: Current object version number for the bank branch.
p_typ_object_version_number: Current object version number for bank branch type code assignment.
p_rfc_object_version_number: Current object version number for RFC code assignment.
p_eft_object_version_number: Current object version number for BIC(EFT) contact point.

When fetching the versions to pass to the API, one has to add a filter as per the class category. Therefore, for p_bch_object_version_number the filter CLASS_CATEGORY = 'BANK_INSTITUTION_TYPE' has to be used while for p_typ_object_version_number, CLASS_CATEGORY = 'BANK_BRANCH_TYPE' has to be used.

There should be no confusion here. The object version of CLASS_CATEGORY = 'BANK_INSTITUTION_TYPE' is not related to the object version of CLASS_CATEGORY = 'BANK_BRANCH_TYPE'. Please have your client design queries using the proper filters to fetch the appropriate versions as per the parameter.

Also, to clarify on the BANK_BRANCH_TYPE row:
The bank branch type is a mandatory field in the CE UI (which defaults to the value of 'ABA'), therefore, a row with class_category='BANK_BRANCH_TYPE' will always be created if the bank branch is created/updated online.

This is not a mandatory parameter in the API, therefore, when a branch is created using the API, and a branch type is not passed (as the client is doing here) this row will not be created. This is not a bug, this is how the API is designed to work.

Note that the presence/absence of the BANK_BRANCH_TYPE category row in the database should not affect product functionality/UI.

Issue 2: API for HZ_PARTIES.COUNTRY: , the table HZ_PARTIES is missing the country of 'US' for our BANK records. The country is populated with 'US' for the BANK_BRANCH records. This is causing an issue using the CASH MANAGEMENT API for banks using the procedure CE_BANK_PUB.check_bank_exist."

Resolution 2:
- PUBLIC API CE_BANK_PUB.CHECK_BANK_EXIST WON'T FIND NEWLY CREATED BANK
: PUBLIC API CE_BANK_PUB.CHECK_BANK_EXIST WON'T FIND NEWLY CREATED BANK

R12.0.x:

This patch consolidates fixes for issues with the CE_BANK_PUB API over CE.A
Patch can be applied over base R12, any RUP
ce patch/115/sql ceextbab.pls 120.12.12000000.11
ce patch/115/sql ceextbas.pls 120.3.12000000.3 + 

The c_bank_num cursor in CE_BANK_PUB API uses hz_parties.country column to identify the home country of the bank. So changing to The c_bank_num cursor is modified to use hz_organization_profiles.home_country column to identify the home country of the bank.

R12.1.1
Resolved by and has following files:
patch/115/sql/ceextbab.pls 120.12.12010000.9
patch/115/sql/ceextbas.pls 120.3.12010000.3

Latest: R12.1.1
The patch fixes the issue wherein the CE Bank Account update API does not allow duplicate account name even if the account number is different for a particular bank branch
This patch is applicable over 12.1.1
patch/115/sql/ceextbab.pls 120.12.12010000.15
ce patch/115/sql ceextbas.pls 120.3.12010000.3 +

Issue 3: The decimal precision of min_check_amount is getting lost when creating a bank account using the API. For instance if the passed is 0.01, the system is taking this as 0.00.

Resolution 3:

- API: CE_BANK_PUB.CREATE_BANK_ACCT TO CREATE BANK ACCOUNTS L_BANKACCT_REC.MIN_CHE

The patch fixes the issue in minimum payment amount field when creating the bank acocunt using the API:CE_BANK_PUB.CREATE_BANK_ACCT
ce patch/115/sql ceextbas.pls 120.3.12000000.4

  API: Minimum Check Amount Is Rounding To Zero Even When Passed A Value

(7). Q & A as reference:

Question 1:
A) Documents for Bank Account/Assignment (CE_R12_BANK_MODEL_PUBLIC_API's.)
1) How do you convert address information at either Bank/Branch level? R12 UI's support this
and you have a requirement to convert this information.
2) How do you convert over "Contact" information at Bank/Branch level?. R12 UI's support this and you
have a requirement to convert this information.

Answer 1:
For both 1 and 2, unfortunately there is no CE API's which will handle creation of bank/branch addresses or the contact information.

Question 2:
B) Customer Bank Account/Assignment: External (Importing External bank account data)
1) You need to convert customer bank/account information, but the customer open interface approach recommended in this document may not work. You have a complex party/customer/account/contact relationship model and need to use core TCA API's to convert legacy data and model it appropriately in the TCA . These TCA relationships may not be modelled by the Customer Open Interface tables.

Answer 2: 
Customer interface program does support importing of Receipt methods and customer contacts. You can refer to the TCA User guide for more information. 

Question 3: Is it possible to have different BANK_ACCOUNT_ID for Internal bank account from 11i to R12 when migrate?

Answer 3: - INCORRECT BANK ACCOUNT INFORMATION GOT MIGRATED DURING 11.5.10.2 TO R12.1.3 UPGR

Compare the results:

11i:
1) select * from ap_bank_branches ;
2) select * from ap_bank_accounts_all where account_type ='INTERNAL' ;

R12:
3) select * from ce_upg_bank_rec ;
4) select * from ce_bank_accounts ;
5) select * from ce_bank_branches_v ;
6) select * from ce_bank_acct_uses_all ;
7) select * from ce_upg_bank_accounts ;
8) select * from ce_bank_upgrade_modes ;
9) select * from ce_upg_ba_uses_all ;

References - UPGRADE FROM 11I: INTERNAL BANKS CANNOT BE SEEN FROM FRONT-END
- INCORRECT BANK ACCOUNT INFORMATION GOT MIGRATED DURING 11.5.10.2 TO R12.1.3 UPGR
- UPGRADE 11I MULTIPLE BANK ACCOUNTS DUE TO ORG SOB DUPLICATED IN R12
- DFF DEFINED FOR BANKS IN 11I NOT INCLUDED IN R12 UPGRADE
- APPLYING UPGRADE PATCH 6678700 WORKER FAILED: FILE CECESTMT.SQL
- PUBLIC API CE_BANK_PUB.CHECK_BANK_EXIST WON'T FIND NEWLY CREATED BANK
- API: CE_BANK_PUB.CREATE_BANK_ACCT TO CREATE BANK ACCOUNTS L_BANKACCT_REC.MIN_CHE
- API: Minimum Check Amount Is Rounding To Zero Even When Passed A Value
- R12.1: Oracle Financials Pre-Upgrade Patch - Supplemental List for EBS CUP
- Internal Bank Account API does not allow duplicate Account Name even Acccount Number is different
- TCA New Banks And Bank Branches Model In Release 12
- R12: How to Find and Modify Validations on Uploaded Internal Banks Using CE_BANK_PUB API
- Where is Supplier Bank Information Stored in Release 12?
- How To Designate a Bank Account as Internal or External Uses
- Unable to Create Account for Employee Type Supplier by Using API IBY_EXT_BANKACCT_PUB.CREATE_EXT_BANK_ACCT
- iSETUP Not Migrating Banks/Branches Other Then Internal

Attachments

(804.09 KB)

(1,770.27 KB)

Related

Products

• Oracle E-Business Suite > Financial Management > Credit to Cash > Oracle Receivables
• Oracle E-Business Suite > Financial Management > Cash and Treasury > Oracle Cash Management
• Oracle E-Business Suite > Financial Management > Procure to Pay > Oracle Payables

Keywords

ACCOUNT NAMES; ACCOUNT NUMBER; ACCOUNTING INFORMATION; AP_BANK_ACCOUNTS_ALL; BANK ACCOUNT; CE_BANK_ACCOUNTS; CE_BANK_ACCT_USES_ALL; CE_UPG_BANK_ACCOUNTS; CE_UPG_BANK_REC; CE_UPG_BA_USES_ALL; CUSTOMER BANK ACCOUNT; DESCRIPTIVE FLEXFIELD; DOCUMENTATION; EXTERNAL BANK ACCOUNT; EXTERNAL PROCESS; FLEXFIELD SEGMENTS; HZ_CODE_ASSIGNMENTS; HZ_PARTIES; PRE-UPGRADE; TCA-API

阅读(10512) | 评论(0) | 转发(0) |

0

上一篇：员工借款及还款场景演练

下一篇：《沁园春.雪》