Spreadsheet Direct Journal Upload Data Fields

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

Y

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

Record Type 001

Payment Information

Entry Event

N

Determine if entry event is required.

If the entry event is marked as required and no entry event is entered on the excel sheet, the transaction will be marked in error. You can correct the transaction on the Excel Error Correction component by populating the entry event field and running the excel edit process again.

If the entry event is marked as optional and does not match any value in the entry event prompt table, the transaction will be marked in error and can be corrected on the Excel Error Correction component.

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

Y

Journal line reference.

Record Type 002

DJ Accounting Information

Line Description

Y

Journal line description.

Record Type 002

DJ Accounting Information

Open Item Key

Y

To be entered by user.

If the account is marked as an open item account and no open item key is entered on the excel sheet, the transaction will be marked in error. You can correct the transaction on the Excel Error Correction component by entering the open item key information and running the excel edit process again. If the open item key entered is not valid, the transaction will be marked in error. You can correct the transaction on the Excel Error Correction component by entering the open item key information and running the excel edit process again.

Record Type 002

DJ Accounting Information

SpeedType Key

Y

To be entered by user.

If the speedtype entered is not valid, the transaction will be marked in error. You can correct the transaction on the Excel Error Correction component by entering a valid speedtype key and running the excel edit process again.

Record Type 002

DJ Accounting Information

Accounting Tag

Y

To be entered by user.

If the accounting tag entered is not valid, the transaction will be marked in error. You can correct the transaction on the Excel Error Correction component by entering a valid accounting tag and running the excel edit process again.

Note: If you enter both speedtype and accounting tag on the excel template, accounting tag will be used if it is enabled on the accounting tag setup. If not enabled, speedtype key will be used.