Receive Bank Statement Service
This service is in the WmOAFIN1227 package and has the following fully-qualified service name: cashManagement1227.intoOA.bankStatement:receiveBankStatement.
This service monitors the import process.
Use this service to load bank statements into the Cash Management module of Oracle Applications and to reconcile the bank statements automatically. The Auto Reconciliation program submits a request to reconcile the bank statement when all import errors are resolved. Other characteristics of this service include:
![*](chapterTOC_bullet.png)
Seven control columns for error-checking purposes to accommodate the various control totals provided in bank statements.
![*](chapterTOC_bullet.png)
All transactions in the batch must use the same currency.
Database Scripts
This service uses the following database scripts:
Database Script | Description |
wm_install_into_bankstmt.sql | Runs all the scripts listed below, except the uninstall script. |
wm_into_bankstmt_pkg.sql | Installs WM_CE_BANK_IMP_HANDLER_PKG. WM_HANDLE_CEBANK, which calls the WM_CONC_REQUEST.WM_REQUEST_SUBMIT procedure to submit the bank statement process. |
wm_drop_into_bankstmt.sql | Uninstalls all components created by wm_install_into_bankstmt.sql. |
For more information about using database scripts, see
Database Scripts.
Supporting Transaction Definitions
This service was built from the configured services defined in the following transaction definitions:
![*](chapterTOC_bullet.png)
BankStmtTransactions1227.txp
![*](chapterTOC_bullet.png)
setBankStatementTxn1227.txp
For information about using the transaction definition files to customize this service, see
Transaction Definitions.
Flow Control
The main flow executes as follows:
specifyDefaultSettings specifies the default parameter settings required for service execution. You should change these settings accordingly.
specifyConcProgParams specifies the default parameter settings required for concurrent program execution. You should change these settings accordingly.
bizDocMapping maps the incoming business document structure to the required Oracle Applications data structures (interface tables). It loops over the parent and the child to extract data to flat format.
setBankStatementTxn inserts data into the interface table. It extracts data from the
IData structure and puts the data into the interface tables in Oracle Applications.
importBankStatement imports bank statement to the production table from the interface table. To monitor the import process, it then sequentially invokes the
callBankStatementImport,
checkbankStatementImportStatus, and
getBankStatementImport_ERR services. If the status of the execution is FAILED, this step appends the dbErrorMsgRecorsdSet and concProgMsgRecordSet record list. Otherwise this it checks for data error that occurred during the import process.
execBankStatementConcProg invokes custom package and stored procedure WM_CE_BANK_IMP_HANDLER_PKG.WM_HANDLE_CEBANK to execute the concurrent program.
checkBankStatementImportStatus. After the data import execution, this service checks the CE_STATEMENT_HEADERS_INT_ALL table to find the errors related to data import for the corresponding bank details.
getBankStatementImport_ERR gets the data import error for the specific bank. This service queries the CE_HEADER_INTERFACE_ERRORS and CE_LINE_INTERFACE_ERRORS tables and gets relevant error messages for the header and the lines.
Business Document Structure
This service uses the business document BankStatementBizDoc. Its structure is as follows:
![*](chapterTOC_bullet.png)
1.0 STATEMENT
![*](chapterTOC_bullet.png)
1.1 STATEMENT_LINES
1.0 STATEMENT (Maps to CE_STATEMENT_HEADERS_INT_ALL)
Field Name | Maps to Column | Description |
BANK_NAME | BANK_NAME | |
BRANCH_NAME | BANK_BRANCH_ NAME | Required. |
STATEMENT_ NUMBER | STATEMENT_ NUMBER | Required. |
STATEMENT_DATE | STATEMENT_DATE | Required. |
BANK_ACCOUNT_ NUM | BANK_ACCOUNT_ NUM | Required. Bank account number. |
CONTROL_BEGIN_ BALANCE | CONTROL_BEGIN_ BALANCE | Control beginning balance. |
CONTROL_END_ BALANCE | CONTROL_END_ BALANCE | |
CONTROL_TOTAL_ DR | CONTROL_TOTAL_ DR | Total receipt amount of the bank statement that can be compared to the actual receipt entry totals for control purposes. |
CONTROL_TOTAL_ CR | CONTROL_TOTAL_ CR | Total payment amount of the bank statement that can be compared to the actual payment entry totals for control. |
CONTROL_DR_ LINE_COUNT | CONTROL_DR_ LINE_COUNT | Total payment line count of the statement that can be compared to the actual number of payments entered for control purposes. |
CONTROL_CR_ LINE_COUNT | CONTROL_CR_ LINE_COUNT | Total payment line count of the statement that can be compared to the actual number of payments entered for control purposes. |
CONTROL_LINE_ COUNT | CONTROL_LINE_ COUNT | Total line count of the statement that can be compared to the actual number of lines entered for control purposes. |
RECORD_STATUS_ FLAG | RECORD_STATUS_ FLAG | Statement upload status lookup type HEADER_INTERFACE_STATUS. Use: ![*](chapterTOC_bullet.png) C for Corrected. ![*](chapterTOC_bullet.png) E for Error. ![*](chapterTOC_bullet.png) N for New. ![*](chapterTOC_bullet.png) T for Transferred. |
CURRENCY_CODE | CURRENCY_CODE | Bank statement currency code. |
ORG_NAME | ORG_ID | Derived from organization definitions. |
CHECK_DIGITS | CHECK_DIGITS | Holds any check digits that result from bank account number validation in FBS. |
ONE_DAY_FLOAT | ONE_DAY_FLOAT | |
TWO_DAY_FLOAT | TWO_DAY_FLOAT | |
1.1 STATEMENT_LINES (Maps to CE_STATEMENT_LINES_INTERFACE)
Field Name | Maps to Column | Description |
LINE_NUMBER | LINE_NUMBER | Required. |
TRX_DATE | TRX_DATE | Required. Line transaction date that updates AP check’s cleared date and AR receipt’s cleared date if EFFECTIVE_DATE is null. |
TRX_CODE | TRX_CODE | Bank transaction code. |
EFFECTIVE_DATE | EFFECTIVE_DATE | Statement line effective date. |
TRX_TEXT | TRX_TEXT | Statement line description. |
INVOICE_TEXT | INVOICE_TEXT | Invoice number for finding available receipts by invoice. |
AMOUNT | AMOUNT | Statement line amount. |
CURRENCY_CODE | CURRENCY_CODE | Statement line currency code. |
EXCHANGE_RATE | EXCHANGE_RATE | |
BANK_TRX_NUMBER | BANK_TRX_NUMBER | Transaction number that identifies the transaction to be matched against the statement line. |
CUSTOMER_TEXT | ACCOUNT_NUMBER | |
USER_EXCHANGE_ RATE_TYPE | USER_EXCHANGE_ RATE_TYPE | Currency conversion rate type. |
EXCHANGE_RATE_ DATE | EXCHANGE_RATE_ DATE | |
ORIGINAL_AMOUNT | ORIGINAL_AMOUNT | Statement line amount in currency code. |
CHARGES_AMOUNT | CHARGES_AMOUNT | Bank charges amount. |
BANK_ACCOUNT_TEXT | BANK_ACCOUNT_TEXT | Supplier or customer bank account number. |