Skip to Main Content
Return to Navigation

Receiving and Editing Spreadsheet Payments

This section provides an overview of spreadsheet payments, lists prerequisites, and discusses how to:

Pages Used to Receive and Edit Spreadsheet Payments

Page Name

Definition Name

Navigation

Usage

Excel Payment Upload Process

EXCEL_EDIT_REQUEST

select Accounts Receivable, then select Payments, then select Electronic Payments, then select Excel Edit Request

Use to retrieve and validate payment data that is created from an uploaded XML file.

Payment Interface

PAYLOAD_REQUEST

select Accounts Receivable, then select Payments, then select Electronic Payments, then select Process Payment Interface, then select Payment Interface

Run the Payment Loader process for spreadsheet payments.

Excel Upload Deposit Errors

EXCEL_ERROR_LIST

select Accounts Receivable, then select Payments, then select Electronic Payments, then select Excel Error Correction

Select deposit records created from the Excel Payment Upload process.

Excel Upload Error Correction

EXCEL_ERR_CORR

Click the link for the deposit ID on the Excel Upload Deposit Errors page.

Edit payments created from the Excel Payment Upload process that have been marked as containing errors

References

EXCEL_ERR_CORR2

Click the References link on the Excel Upload Error Correction page

Edit the reference information for a payment received from the Excel Payment Upload process.

Understanding Spreadsheet Payments

The Spreadsheet Payment workbook enables you to enter payments offline using Microsoft Excel and then import the payments into your PeopleSoft Receivables system. When you import the spreadsheet payments, and they go through the edit process, the Payment Loader Application Engine process loads the payment data into the payment application tables.

Note: The spreadsheet payment upload process supports MicroSoft Excel input formats. If you use a non-Excel spreadsheet, you must convert the data to an Excel format before importing.

The ExcelUploadforDeposits.xls workbook is the Microsoft Excel data input tool. You use it to prepare and enter payments and import them into your PeopleSoft database.

Two worksheets make up the ExcelUploadforDeposits.xls workbook, the Template worksheet and the Data Sheet worksheet. The Template worksheet contains all of the available fields that you can configure for data entry. You enter the transaction data on the Data Sheet worksheet. You can create multiple versions of the Microsoft Excel workbook, each with different templates if required.

The Spreadsheet Payment workbook supports check, EFT, and EFT Giro payment methods.

Spreadsheet Payment Upload Process Flow

The Spreadsheet Payment Upload Process flow consists of the following steps:

  1. Enter the payment data into the spreadsheet.

    Enter data into all of the required fields for a PeopleSoft Receivables payment.

  2. Generate XML and post the file from the spreadsheet payment options.

    A Visual Basic (VB) macro in the Spreadsheet Payment workbook converts the spreadsheet data into an XML format that the PeopleSoft Integration Broker reads and then posts to a URL available to PeopleSoft systems.

  3. Integration Broker processes the incoming XML data and transfers it to the Excel Payment Upload tables on the application server.

  4. The Excel Payment Upload process (AR_EDIT_UPLOADED_PAY_INFO) validates the data in the Excel Payment Upload tables. Data with errors causes the rejection of the entire payment group. You must correct any errors before the data can be processed successfully. Oracle recommends that you correct the errors online through the Excel Upload Error Correction page. However, you can correct the errors in the Spreadsheet Payment workbook and then regenerate the XML file and post the data again.

  5. The Excel Upload Payment process copies the validated payment data into the payment staging tables.

  6. Run the Payment Loader process to select the payments based on the process run control parameters and build the PeopleSoft Receivables deposit and payment groups.

    All payments in the Excel Payment Upload tables are assigned the value XEL in the DATA_SOURCE field. The Payment Loader process uses the XEL data source value to recognize payments created through the spreadsheet payment upload process.

The following diagram illustrates the flow for processing spreadsheet payments. The data is entered into an Excel spreadsheet and the Excel Visual Basic Macros are run on the data, which is sent to Integration Broker. Integration Broker sends the data to Excel Payment Upload tables. The Excel Upload Payment Process (AR_EDIT_UPLOADED_PAY_INFO) is run and, if errors occur, they can be corrected on an Excel Upload Error Correction page. If no errors occur, the payment data is sent to the Payment Staging Tables. The Payment Loader Process (AR_PAYLOAD) runs and the data is updated in the payment tables.

Image: Excel Payment Upload process flow

The following diagram illustrates the flow for processing spreadsheet payments. The data is entered into an Excel spreadsheet and the Excel Visual Basic Macros are run on the data, which is sent to Integration Broker. Integration Broker sends the data to Excel Payment Upload tables. The Excel Upload Payment Process (AR_EDIT_UPLOADED_PAY_INFO) is run and, if errors occur, they can be corrected on an Excel Upload Error Correction page. If no errors occur, the payment data is sent to the Payment Staging Tables. The Payment Loader Process (AR_PAYLOAD) runs and the data is updated in the payment tables

Excel Payment Upload process flow

Spreadsheet Payment Upload Data Fields

You can configure the ExcelUploadforDeposits.xls workbook for the appropriate data entry required. However, certain fields must contain data order to properly convert the data into an XML file.

The ExcelUploadforDeposits.xls workbook enables you to enter data into fields that are available in the regular deposit entry process. The fields available in the ExcelUploadforDeposits.xls workbook are:

Record Type

Field

Required?

Notes

Record Type 000

Deposit Information

Process Flag

N

This field exists on the Data Sheet worksheet.

Valid values are Y and N.

This field is used only by the Visual Basic macros in the Spreadsheet Payment workbook. This row of data is processed only if the Process Flag field value is Y.

After processing, the field value is set to N. To reprocess this row, you must change the value to Y.

Record Type 000

Deposit Information

Remit Only

N

Valid values are Y and N.

Y indicates that only the remit data will be processed and added to a payment already in the system. The payment is identified in record type 001 of the spreadsheet.

Record Type 000

Deposit Information

Deposit Business Unit

Y

Populating this field on the Data Sheet worksheet indicates a new deposit for system processing.

Note: This field must be populated in the first row of the Data Sheet worksheet. All rows are skipped until the VB macros find a row with this field populated.

Record Type 000

Deposit Information

Deposit ID

Y

The system assigns the deposit ID when you deselect the Add to Data Sheet check box for this field type on the Template worksheet.

The system assigns the deposit ID when this field on the Data Sheet worksheet is blank.

Record Type 000

Deposit Information

OprID

Y

Default values are not available for this field

Record Type 000

Deposit Information

Control Count

Y

 

Record Type 000

Deposit Information

Control Amount

Y

 

Record Type 000

Deposit Information

Accounting Date

Y

 

Record Type 000

Deposit Information

Bank Code

N

If you leave this field blank, the system uses the bank specified on the Receivables Options - General 1 page.

Record Type 000

Deposit Information

Bank Account

N

If you leave this field blank, the system uses the bank account specified on the Receivables Options - General 1 page.

Record Type 000

Deposit Information

Received Date

N

If you leave this field blank, the system uses the current date.

Record Type 001

Payment Information

Deposit BU

Deposit ID

Y

Default values for these fields are copied from the deposit record, record type 000.

Record Type 001

Payment Information

Payment Sequence

Y

This field is automatically populated in the background. The number in this field increments by one for each new payment.

Record Type 001

Payment Information

Payment ID

Y

Data in this field on the Data Sheet worksheet indicates that a new payment is within a deposit for system processing.

If this field is left blank, the payment will not be uploaded to the database.

Record Type 001

Payment Information

Payment Amount

Y

The Excel Payment Upload process does not process payments with negative values.

Record Type 001

Payment Information

Payment Currency

N

If you leave this field blank, the system uses the format currency specified on the Receivables Options - Payment Options page.

Record Type 001

Payment Information

Payment Method

N

The supported payment methods are:

  • Check

  • EFT

  • EFT Giro

If you leave this field blank, the system uses the payment method specified on the Receivables Options - Payment Options page.

Record Type 001

Payment Information

Payment Predictor Flag

N

In this Yes/No field, Yes (Y) indicates payment will be processed by Payment Predictor.

Note: It is invalid to enter Y for both Payment Predictor and Direct Journal flags.

Record Type 001

Payment Information

Direct Journal Flag

N

In this Yes/No field, Yes (Y) indicates payment will be directly journaled.

Note: It is invalid to enter Y for both Payment Predictor and Direct Journal flags.

Record Type 002

Customer Remit Information

Deposit BU

Deposit ID

Payment Sequence

Y

Default values for these fields are copied from the payment information, record type 001.

Record Type 002

Customer Remit Information

Identification Sequence

Y

This field is automatically populated in the background. The number in this field increments by one for each new customer.

Record Type 002

Customer Remit Information

Customer ID

Y

Do not enter data in this field if data is entered in the MICR ID field

Record Type 002

Customer Remit Information

Business Unit

Y

This field is required when the Customer ID field is used.

Do not enter data in this field if data is entered in the MICR ID field

Record Type 002

Customer Remit Information

MICR ID

Y

This field is required if the Customer ID field is not used.

Do not enter data in this field if data is entered in the Customer ID field

Record Type 003

Summary and Detail Reference Remit Information

Deposit BU

Deposit ID

Payment Sequence

Y

Default values for these fields are copied from the customer payment information, record type 001.

Record Type 003

Summary and Detail Reference Remit Information

Identification Sequence

Y

This field is automatically populated in the background. The number in this field increments by one for each new payment.

Record Type 003

Summary and Detail Reference Remit Information

Reference Qualifier Code

Y

This field requires data in the Reference Value field.

If you enter data in this field, do not enter data in the Item, Item Line, Business Unit, Customer ID, Payment Amount, or Discount Taken field.

Record Type 003

Summary and Detail Reference Remit Information

Reference Value

Y

This field requires data in the Reference Qualifier Code field.

Record Type 003

Summary and Detail Reference Remit Information

Entry Event

N

 

Record Type 003

Summary and Detail Reference Remit Information

Item

Y

If you enter data in this field, do not enter data in the Reference Qualifier Code or Reference Value field.

The following fields require that you enter data:

  • Item Line

  • Business Unit

  • Customer ID

  • Payment Amount

  • Discount Taken

Record Type 003

Summary and Detail Reference Remit Information

Item Line

Y

If you enter data in this field, do not enter data in the Reference Qualifier Code or Reference Value field.

Enter data in the following fields:

  • Item

  • Business Unit

  • Customer ID

  • Payment Amount

  • Discount Taken

Record Type 003

Summary and Detail Reference Remit Information

Business Unit

Y

If you enter data in this field, do not enter data in the Reference Qualifier Code or Reference Value field. Enter data in the following fields:

  • Item

  • Item Line

  • Customer ID

  • Payment Amount

  • Discount Taken

Record Type 003

Summary and Detail Reference Remit Information

Customer ID

Y

If you enter data in this field, do not enter data in the Reference Qualifier Code or Reference Value field.

Enter data in the following fields:

  • Item

  • Item Line

  • Business Unit

  • Payment Amount

  • Discount Taken

Record Type 003

Summary and Detail Reference Remit Information

Payment Amount

Y

If you enter data in this field, do not enter data in the Reference Qualifier Code or Reference Value field.

This amount must be greater than zero.

Enter data in the following fields:

  • Item

  • Item Line

  • Business Unit

  • Customer ID

  • Discount Taken

Record Type 003

Summary and Detail Reference Remit Information

Discount Taken

N

If you enter data in this field, do not enter data in the Reference Qualifier Code or Reference Value field.

This amount must be greater than or equal to zero.

Requires data in the following fields:

  • Item

  • Item Line

  • Business Unit

  • Customer ID

  • Payment Amount

Prerequisites

Before you can enter and import spreadsheet payments, you must:

  • Copy the ExcelUploadforDeposits.xls file to a folder on your workstation.

    The file is located in the core\build\excel folder of the shipped PeopleSoft software.

  • Install MicroSoft-delivered XML library file MSXML.DLL on your workstation.

  • Set up your Microsoft Excel to accept macros.

    Important! The Security is set to Medium in the delivered ExcelUploadforDeposits.xls file. When the security is set to Medium, you can choose whether or not to run potentially unsafe macros. Always choose to enable macros! If you disable the macros, the functionality of the upload will not run.

  • Configure Integration Broker for the Receivables EIP (enterprise integration point).

    The web server and the application server should be configured for setting up the Integration Gateway. Also, the application server should be configured to have the Pub/Sub servers established before bringing up the application server.

    Determine your default local node for the Integration Broker to receive the data and run the application message. Oracle defines the default local node as PSFT_EP, but you can change it to your default local node.

    Configure your browser to automatically detect LAN settings. From your browser, navigate to select Tools, then select Internet Options. Select the Connections tab and click the LAN Settings button. Select Automatically detect settings in the Automatic configuration group box.

  • Create an Excel Payment Upload process run control.

Setting Up Spreadsheet Payments

Access the Spreadsheet Payment - Template worksheet by clicking the ExcelUploadforDeposits.xls file delivered with your PeopleSoft products. You set the defaults and set up your payment spreadsheets for importing into PeopleSoft Receivables on the Template worksheet.

Image: Spreadsheet Payment -Template worksheet

This example illustrates the fields and controls on the Spreadsheet Payment -Template worksheet.

Spreadsheet Payment -Template worksheet

The Template worksheet in the Spreadsheet Payment workbook contains all of the available fields that you can use on the Data Sheet worksheet to enter payment data.

Entering Spreadsheet Payments

Access the Spreadsheet Payment - Data Sheet worksheet by clicking the ExcelUploadforDeposits.xls file delivered with your PeopleSoft products. You use the Data Sheet worksheet to create and import payment data.

Image: Spreadsheet - Data Sheet worksheet

This example illustrates the fields and controls on the Spreadsheet - Data Sheet worksheet.

Spreadsheet - Data Sheet worksheet

Enter payment data and import the payment data into PeopleSoft Receivables using the Data Sheet worksheet. The Data Sheet worksheet is designed to display only the fields selected in the Template worksheet.

Note: You must enter data in record type 000, the Deposit Information fields in the first row of the Data Sheet worksheet. The VB macro skips all rows until the macro locates data in the Deposit Unit field in record type 000. You are not required to enter the deposit information for multiple payments if they are part of a single deposit. Also, you are not required to enter the same payment information for multiple lines of customer and item remit information.

Excel Payment Upload Process Page

Use the Excel Payment Upload Process page (EXCEL_EDIT_REQUEST) to use to retrieve and validate payment data that is created from an uploaded XML file.

Running the Payment Loader Process

Use the Payment Interface page (PAYLOAD_REQUEST) to run the Payment Loader process for spreadsheet payments.

Select the Upload from Excel check box.

Note: You can use the Excel Payment Upload process to enter remit-only information.

Excel Upload Deposit Errors Page

Use the Excel Upload Deposit Errors page (EXCEL_ERROR_LIST) to select deposit records created from the Excel Payment Upload process.

Use this page to review and select deposit records created by the Excel Payment Upload process. You can narrow the selection criteria by specifying a deposit business unit, deposit ID, payment ID, or user ID. You can review all of the Excel deposit records by leaving all four of these fields blank.

Image: Excel Upload Deposit Errors page

This example illustrates the fields and controls on the Excel Upload Deposit Errors page. You can find definitions for the fields and controls later on this page.

Excel Upload Deposit Errors page

Excel Upload Error Correction Page

Use the Excel Upload Error Correction page (EXCEL_ERR_CORR) to display the deposit data for deposits with errors.

This page also displays up to five error messages for a deposit. You can change the deposit information or information for individual payments on this page to correct the errors. The system enables you to add or delete payments for the current deposit. If you add or delete payments, you must also change the values in the Control Total Amount and Control Count fields until the values in the Difference Amount and Difference Count fields equal zero.

After completing the changes, click the Apply button to save your changes, or click the OK button to save the changes and return to the Excel Upload Error Correction page. Click the Cancel button to remove any changes you have made since clicking the Apply button or the OK button.

Note: Clicking the Cancel button after accessing the References page can also remove some changed data if you do not first click the Apply button or the OK button.

References Page

Use the References page (EXCEL_ERR_CORR2) to edit the reference information for a payment received from the Excel Payment Upload process.

Use this page to add, delete, or modify any customer, summary, and detail references associated with a payment.

Click the Apply button to save your changes or click the OK button to save your changes and return to the Excel Upload Error Correction page. Clicking the Cancel button cancels any changes you have made.