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 |
Deposit Type |
N |
If deposit type is blank, then the deposit type specified at the bank account is fetched. |
|
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:
If you leave this field blank, the system uses the payment method on the deposit type. If payment method is not defined for the deposit type, the payment method specified on the Receivables Options - Payment Options page is used. |
|
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:
|
|
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:
|
|
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:
|
|
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:
|
|
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:
|
|
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:
|