Understanding Spreadsheet Payments and Direct Journals

The spreadsheet payment and direct journal 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.

Spreadsheet Payments

When you import spreadsheet payments and they go through the edit process, the Payment Loader Application Engine process loads the payment data into the payment application tables.

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:

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.
Data Sheet worksheet: supports check, EFT, and EFT Giro payment methods.

Spreadsheet Direct Journals

The spreadsheet direct journal upload process enables you to enter a group of direct journals in spreadsheet format and upload in a batch.

Integration Broker loads the data from the excel spreadsheet into the excel payment upload tables on the application server. All editing is done in the existing application engine excel upload edit program (AR_EXCL_EDIT).

The edit program provides edits specifically for direct journals and applies ChartField inheritance and security rules. The process loads the data into the payment staging tables. If there are no edit errors, the Payment Load process moves the data from the payment staging tables to the deposit, payment and direct journal accounting entry tables. Any edit errors can be corrected by deleting the spreadsheet in the Excel Upload Error Correction component and making the corrections directly in the excel spreadsheet. After the correction, the data must be uploaded again.

The workbook, ExcelUploadforDirectJournalDeposits.xlsm, includes the deposit, payment and direct journal accounting entries. The template is similar to the existing template for excel payment upload but will include accounting entry lines for direct journals. The spreadsheet will only do format type editing and some fields are required such as business unit.

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

Enter the payment data into the spreadsheet.
Enter data into all of the required fields for a PeopleSoft Receivables payment.
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.
Integration Broker processes the incoming XML data and transfers it to the Excel Payment Upload tables on the application server.
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.
The Excel Upload Payment process copies the validated payment data into the payment staging tables.
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.

The Spreadsheet Direct Journal process flow consists of the following steps:

Enter the payment data into the spreadsheet.
Enter data into all of the required fields for a PeopleSoft Receivables payment.
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.
Integration Broker processes the incoming XML data and transfers it to the Excel Payment Upload tables on the application server.
The Excel Edit Request process (AR_EXCL_EDIT) validates the data in the Excel Payment Upload tables and creates the offsetting entry (cash line) for the direct journals. Data with errors causes the rejection of the entire payment group. You must correct any errors before the data can be processed successfully. Delete the payment from the Excel Error Correction component and correct the errors in the Spreadsheet Payment workbook and then regenerate the XML file and post the data again.
The Excel Edit Request process copies the validated payment data into the payment staging tables (_EC tables).
Run the Process Payment Interface (Payment Loader) process to select the payments based on the process run control parameters and build the PeopleSoft Receivables deposit and payment groups. If inter or intra unit entries are required for the direct journal, the InterUnit Processor is called from Payment Loader (AR_PAYLOAD).
If the Payment Loader process successfully loaded the master tables, mark the direct journal complete. You can do this on the Direct Journal Create Entries component or the Direct Journals to Complete MyWork link on the AR Fluid Workcenter.
If combo editing is enabled, combo edits will be run when the complete checkbox is checked on the Direct Journal Create Entries component or the Direct Journals to Complete MyWork link. You must resolve combo edit errors before you can mark the direct journal complete.
If the direct journal requires budget checking, you can run the budget processor online via the Direct Journal Create Entries component or the Direct Journals to Budget Check MyWork link on the AR Fluid Workcenter.
Run Journal Generator to send the direct journal to the general ledger.

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.

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

You can configure the ExcelUploadforDirectJournalDeposits.xlsm workbook for the appropriate data entry required.

The fields available in the ExcelUploadforDirectJournalDeposits.xlsm 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.
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	Direct Journal Flag	N	In this Yes/No field, Yes (Y) indicates payment will be directly journaled.
Record Type 002 DJ Accounting 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 DJ Accounting Information	Distribution 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 DJ Accounting Information	Business Unit	Y	To be entered by user.
Record Type 002 DJ Accounting Information	Business Unit GL	Y	To be entered by user.
Record Type 002 DJ Accounting Information	Foreign Amount	Y	To be entered by user.
Record Type 002 DJ Accounting Information	Foreign Currency	Y	To be entered by user.
Record Type 002 DJ Accounting Information	Account	Y	To be entered by user.
Record Type 002 DJ Accounting Information	Alternate Account	N	Not mandatory.
Record Type 002 DJ Accounting Information	Department ID	N	Not mandatory.
Record Type 002 DJ Accounting Information	Configured ChartFields	N	Not mandatory.
Record Type 002 DJ Accounting Information	Journal Reference	N	Journal line reference.
Record Type 002 DJ Accounting Information	Line Description	N	Journal line description.

You can submit both Customer and Item Remit records as Remit Only records through the AR Excel Upload Deposit process. The process searches the database and matches the Remit Only records with a deposit or payment already in the database that is normally processed through EDI, Lockbox, or any other process.

These basic rules apply to remit only processing:

The deposit or payment available for matching must be unidentified.
If there are any remit records attached to the payment, the payment is ignored for matching. Incoming Remit Only records will not match to that payment. This process does not allow the user to add remit records to a payment that already has remit records.
Remit only records coming from Excel can only use 2 of the 4 matching options available on the Payment Interface run control page for the AR_PAYLOAD process. The fields used to match to the payment are Payment Amount and Payment ID, and Accounting Date.
The process attempts to match the incoming Remit Only records to payments already loaded onto the database. The process also attempts to match the incoming Remit Only records to payments in the staging tables. The staging tables are deposits or payments that have been uploaded from EDI, Lockbox, or Cash Drawer, and so on, but not yet processed and loaded to the deposit/payment tables by the AR Payload program. Rules 1 and 2 above apply to both Loaded Payment and Staging Payments for matching.

Select these options on the Payment Interface run control page (Accounts Receivable > Payments > Electronic Payments > Process Payment Interface > Payment Interface) for the process AR_PAYLOAD:

Select the Upload from Excel check box in the Data Sources group box.
Select the Match Split Stream Data check box.
(Optional) Select the Match Payments Already Loaded check box if you want to match to payments already loaded into the database as well as payments still in the staging tables. Otherwise, only payments in staging are considered for matching.
Select the Payment Amount and Payment ID and/or Date, Amount and Payment ID check boxes in the Match Criteria Options group box. Only these two criteria options work for Excel Remit Only records. The Remit Only records must have the same payment ID and payment amount as the payment, and in the case of the Date, Amount and Payment ID, the Remit Only record must match the estimated settlement date of the payment on the payment record.

Input data on the spreadsheet must meet certain rules for Remit Only records. The field names and values are as follows:

Record Type	Field Name	Value
Record Type 000	Process	Y
Record Type 000	Remit Only	Y
Record Type 000	Deposit Unit	Use a value that is a valid Receivables business unit. This value is not used except to verify that this record is valid.
Record Type 000	Deposit ID	The value entered is not used except to identify the record if the user looks at the XML output.
Record Type 000	Control Count	Use a value that is equal to the total number of payments in this Remit Only group.
Record Type 000	Control Total	Use a value that is equal to the total amount of all payment amounts of all payments in this Remit Only group.
Record Type 000	Accounting Date	Only required if DATE is among the match criteria that should be selected when matching. This date is matched to the estimated settlement date on the payment.
Record Type 001	Payment ID	Required for matching to the payment ID.
Record Type 001	Payment Amount	Required for matching to the payment amount.
Record Type 002	All fields can be used for Customer Remit records.
Record Type 003	All fields can be used for Item Reference Remit records.

Understanding Spreadsheet Payments and Direct Journals

Working with Spreadsheet Payments and Direct Journals

Spreadsheet Payments

Spreadsheet Direct Journals

Spreadsheet Payment Upload Process Flow

Spreadsheet Direct Journal Process Flow

Spreadsheet Payment Upload Data Fields

Spreadsheet Direct Journal Upload Data Fields

Split Stream Processing Using the AR Excel Upload Deposit Process