This chapter provides an overview of the Bank Statement Import process and discusses how to set up bank statement processing.
Important! If you have installed Cash Management 8.9 or
later, you have more options for importing bank statements by taking advantage
of its Financial Gateway functionality. You can import a variety of bank formats
by using Flat File Import, FTP, HTTP, or PeopleSoft Integration Broker.
If you do not have Cash Management 8.9 or later installed, use the delivered
generic electronic banking functionality described in this chapter to load
bank statements.
See Also
Setting Up Electronic Banking Using Financial Gateway
This diagram illustrates the generic bank import process; from transforming bank statements from files that the bank provides into the PSBD1 or PSBD2 document layouts used by PeopleSoft Enterprise 8.8 or later versions; to running the Bank Statement Import process and the Bank Statement Load process to populate the application tables so that the bank statements can be viewed or edited from within the PeopleSoft components:
Generic bank statement Import process
To load bank statement data without Cash Management's Financial Gateway functionality:
Transform bank statements from a file that the bank provides, such as a BAI file, into the PeopleSoft Business Document layout (PSBD1 or PSBD2 for PeopleSoft Enterprise 8.8 or later).
Note. You are responsible for implementing functionality that transforms the organization's bank statement files to the PSBD1 or PSBD2 layout. PeopleSoft does not provide this functionality.
Use the Import Bank Statements page to run the Bank Statement Import Application Engine process (IMPORT_FILES) to stage and load bank statement data.
When the process is complete, the status for the bank statement on the Review Bank Statement Files page is Ready.
Once the data is staged in the staging tables, the system loads the bank statement by using the Bank Statement Load Application Engine process (TR_BSP_LOAD). This process transfers the data from the staging tables to the application tables. The process scrubs the data to ensure that there are no duplicate statements being loaded and that the data will load into the application tables properly. Once the data is in the applications tables, it is available to view and edit on the various bank statement pages.
See Also
PeopleTools PeopleBook: Integration Broker
PeopleTools PeopleBook: PeopleSoft Integration Testing Utilities and Tools
This section discusses how to:
Transform bank-statement files to a PSBD layout.
Define bank statement balance codes.
Define bank statement transaction codes.
Set up a node for importing bank statements.
After you set up this functionality, you can import statements by using the Import Bank Statement page.
Page Name |
Definition Name |
Navigation |
Usage |
Balance Codes |
BSP_BAL_CODES |
Banking, Administer Bank Statements, Bank Statement Codes, Bank Statement Codes - Balance Codes |
Define bank statement code information. Also select three favorite statement balance codes to appear on online inquiry pages. |
Transaction Codes |
BSP_TXN_CODES |
Banking, Administer Bank Statements, Bank Statement Codes, Bank Statement Codes - Transaction Codes |
Define bank statement transaction code information, such as activity type and payment method. |
This section contains tables that can be used as guidelines for converting bank-statement data into a PSBD1 layout, which is required for importing banks-statement, data files into the PeopleSoft system if Cash Management is not installed.
To import and load bank statements:
See Importing Bank Statements.
Note. You can import bank-statement data files using the PSBD2 layout if you have Cash Management installed and are using a third-party, communication toolkit that has the capability of processing files in XML format. Sample files that can be used as guidelines for transforming bank-statement date files into the PSBD2 layout can be found on My Oracle Support.
This table defines the PSDB1 layout:
Record |
Length |
Field |
000 |
1 – 3 |
000 |
27 – 36 |
As of Date |
|
37 – 46 |
As of Time |
|
001 |
1 – 3 |
001 |
7 – 26 |
Bank ID Nbr |
|
27 – 61 |
Bank Account Number |
|
62 – 89 |
Beginning Balance (*) |
|
90 – 99 |
Beginning Date |
|
100 – 127 |
Ending Balance (*) |
|
128 – 137 |
End Date |
|
138 – 165 |
Available Balance (*) |
|
204 – 206 |
Currency Code |
|
002 |
1 – 3 |
002 |
7 – 26 |
Bank ID Nbr |
|
27 – 61 |
Bank Account Number |
|
62 – 81 |
Bank Transaction Code (also Statement Activity Type) |
|
82 – 109 |
Transaction Amount (*) |
|
110 – 119 |
Value Date |
|
120 – 129 |
Transaction Date |
|
130 – 144 |
Reference ID |
|
145 – 160 |
Bank Reference ID |
|
161 – 176 |
Description |
|
204 – 206 |
Currency Code |
|
003 |
1 – 3 |
003 |
62 – 89 |
Payment Amount (*) |
|
90 – 117 |
Discount Taken (*) |
|
118 – 145 |
Adjustment Amount (*) |
|
146 – 147 |
Adjustment Reason |
|
148 – 149 |
Reference Qualifier Code |
|
150 – 179 |
Reference Value From |
|
180 – 209 |
Reference Value To |
|
210 – 224 |
Customer ID |
|
225 – 254 |
MICR ID |
Access the Bank Statement Codes - Balance Codes page (Banking, Administer Bank Statements, Bank Statement Codes, Bank Statement Codes - Balance Codes).
Balance codes are assigned to each balance line and determine how the reconciliation process handles them. Each balance entry that is received electronically or entered manually should be assigned a balance code.
Statement Code |
Enter a three-digit statement code that is to be defined. |
Type Code |
Indicate if the code is a Status or Summary code. |
CR/DB (credit/debit) |
Indicate if the code is a CR (credit) or DB (debit), or select NA (not applicable) if this categorization does not apply. |
Display Balance |
Select this check box to indicate that the balance is a favorite balance. The system automatically displays the favorite balances on certain pages, such as the Bank Balance Inquiry page. You can select up to three different balances to be displayed. You can edit these selections at any time. |
Access the Bank Statement Codes - Transaction Codes page (Banking, Administer Bank Statements, Bank Statement Codes, Bank Statement Codes - Transaction Codes).
The system assigns transaction codes to each bank statement transaction line during electronic load or manual entry. The transaction code determines how the reconciliation processes the specific line item.
Trans Code (transaction code) |
Identifies the type of transaction in a bank statement. Select from: ACH: Automatic clearing house. BOE - Out: Bill of exchange - outgoing (or disbursed). Bank Adj: Bank adjustments. Bill Order CHK: Check. Deposits Direct Debits DD-Out: Direct debit - outgoing (or disbursed). Draft EFT: Electronic funds transfer. Fees Fund Rcpt: Fund receipt. General: General transaction. Interest: Interest expense. Man Check: Manual check. Misc: Miscellaneous. Payment Receipt Stop Pay: Stop payment. Voids Wire Out: Wire - disbursement. |
Activity |
Select a statement activity type. |
Payment Method |
Identifies the payment method that is specified for a transaction code. Select from: ACH: Automatic clearing house. Check Cust Draft: Customer draft. Direct Debit EFT: Electronic funds transfer. EFT Drafts: Electronic funds transfer drafts. Giro-EFT: Giro-electronic funds transfer. Giro-Manual Giro-manual. LC: Letter of credit. Manual: Manual check. WIR: Wire transfer. Vndr Draft: Vendor draft. Wire: Wire disbursement. |