Setting Up the Bank Statement Import Process

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

Setting Up Payment Security

Click to jump to parent topicUnderstanding the Bank Statement Import Infrastructure

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:

  1. 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.

    See Transforming Bank Statement Files to a PSBD Layout.

  2. 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 are 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 are in the applications tables, they are available to view and edit on the various bank statement pages.

See Also

Importing Bank Statements

PeopleTools PeopleBook: Integration Broker

PeopleTools PeopleBook: PeopleSoft Integration Testing Utilities & Tools

Click to jump to parent topicSetting Up the Bank Statement Import Process

This section discusses how to:

After you set up this functionality, you can import statements by using the Import Bank Statement page.

Click to jump to top of pageClick to jump to parent topicPages Used to Set Up Bank Statement Import Functionality

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.

Click to jump to top of pageClick to jump to parent topicTransforming Bank Statement Files to a PSBD Layout

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

Click to jump to top of pageClick to jump to parent topicDefining Balance Codes

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.

Click to jump to top of pageClick to jump to parent topicDefining Transaction Codes

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.