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.

Understanding the Bank Statement Import Infrastructure

This diagram illustrates the generic bank import process:

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.

See Transforming Bank Statement Files to a PSBD Layout.
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.

Setting Up the Bank Statement Import Process

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.

Pages Used to Set Up Bank Statement Import Functionality

Page Name	Object 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.

Transforming Bank Statement Files to a PSBD Layout

This section contains tables that can be used as guidelines for converting banks-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.

Note. You can import bank-statement, data files using the PSBD2 layout if you have Cash Management 8.8 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 Customer Connection.

This table provides guidelines for formatting PSBD1 file-header information.

Sequence	Field Name	Variable Type	Starting Position	Character Length	Comments
1	ECFILEROWID	String	1	3	Always 000
2	ECACTIONCD	String	4	3	Always 1
3	BNK_ID_NBR	String	7	20	Bank Identification number
4	ASOFDATE	Date	27	10	As-of date for the statement
5	ASOFTIME	Time	37	15	As-of time for the statement
6	RECEIVER	String	52	20	Identifier for party receiving the bank statement.
7	TRANSACTION_REF	String	72	16	Transaction reference number
8	STMT_NUMBER	Number	88	5	Statement number
9	PAGE_NUMBER	Number	93	3	Page number

This table provides guidelines for formatting PSBD1 bank-statement balance information.

Sequence	Field Name	Variable Type	Starting Position	Character Length	Comments
1	ECFILEROWID	String	1	3	Always 001
2	ECACTIONCD	String	4	3	Always 1
3	BNK_ID_NBR	String	7	20	Bank identification number
4	BANK_ACCOUNT_NUM	String	27	17	Bank account number
5	STMT_BAL_OPEN	Signed number	44	17	Opening balance
6	STMT_BAL_OPEN_DT	Date	61	10	Opening balance date
7	STMT_BAL_CLOSE	Signed number	71	17	Closing balance
8	STMT_BAL_CLOSE_DT	Date	88	10	Closing balance date
9	STMT_BAL_AVAIL	Signed number	98	17	Available balance
10	STMT_BAL_FWD	Signed number	115	17	Forward balance
11	STMT_BAL_FWD_DT	Date	132	10	Forward balance date
12	CURRENCY_CD	String	142	3	Currency code

This table provides guidelines for formatting PSBD1 bank-statement transaction information.

Sequence	Field Name	Variable Type	Starting Position	Character Length	Comments
1	ECFILEROWID	String	1	3	Always 001
2	ECACTIONCD	String	4	3	Always 1
3	BNK_ID_NBR	String	7	20	Bank identification number
4	BANK_ACCOUNT_NUM	String	27	17	Bank account number
5	BANK_TRANS_CODE	String	44	20	Transaction code given by the bank. This will be changed using code mapping in the bank-statement load process.
6	RECON_TRAN_AMT	Signed number	64	17	Reconciliation transaction amount
7	VALUE_DT	Date	81	10	Value date for the transaction
8	RECON_BANK_DT	Date	91	10	Bank date provided for reconciliation
9	RECON_REF_ID	String	101	12	Reconciliation-specific reference ID
10	RECON_PAYEE_ID	String	113	40	Reconciliation payee
11	BANK_REF_ID	String	153	16	Reference ID provided by the bank
12	OTHER_REF_ID	String	169	16	Additional reference ID
13	STMT_ACTIVITY_TYPE	String	44	20	Statement activity type derived from the BANK_TRANS_CODE.

Defining Balance Codes

Access the Bank Statement Codes - Balance Codes page.

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.

Defining Transaction Codes

Access the Bank Statement Codes - Transaction Codes page.

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.