Oracle Apps Adapter 6.0 | webMethods Oracle Applications Adapter Documentation | webMethods Oracle Applications Adapter 12.2.7 Predefined Transaction Services Documentation | Financial Predefined Transaction Services | Receive Bank Statement Service
 
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:
*Seven control columns for error-checking purposes to accommodate the various control totals provided in bank statements.
*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:
*BankStmtTransactions1227.txp
*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.
For more details on receive transactions, see Overview of Receive Service Transaction Processing.
Business Document Structure
This service uses the business document BankStatementBizDoc. Its structure is as follows:
*1.0 STATEMENT
*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:
*C for Corrected.
*E for Error.
*N for New.
*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.