Lockbox

Lockbox Check Import

The lockbox import functionality will allow staff to create check payments received by their bank or other third-party. Payments will be received and processed at the bank, and a file will be provided with the payment details so they can be recorded in ClearVantage.

The lockbox import tool will create the payment record in ClearVantage using a file template with the payment information.

CV Lockbox Setup

Required Settings

Confirm the CHECK Payment Method is already configured.

1. Click Administration from the Main Menu Dropdowns and select Edit Reference Tables, then Payment Methods.

   

Confirm the Lockbox is available in the menu:

1. Click Tools from the Main Menu Dropdowns àSettings à Customize Main Menu

   

2. Under Financials the Lockbox Check Import menu item should have The Hidden? flag unchecked. If the hidden checkbox is checked, un-check it to make the button visible and select Save Changes.

   

User Permissions

To give users access to the Lockbox Check Import button:

1. Click on Administration from the Main Menu Dropdowns and select Edit Users.

2. Find the user you want to give access to and click on the UserCD to open the User Notebook.

3. Click on the Financial Administrator checkbox to give the appropriate permissions.

4. Select Save on the bottom right-hand corner.

   

Lockbox Management

Import Requirements

1. Ensure the data spreadsheet is in CSV format. Other file types are not accepted.

2. Review payments included in the spreadsheet for accuracy and that required fields are populated.

3. Review Instructional text in the Example CSV file.

4. Company (Column I) must include leading zeros “001”

5. Remove any incomplete or blank lines.

Import Template

1. Date:

   • Payment Date

   • If there is no Batch Date provided on the Lockbox Check Import screen, this date will be used to create a payment batch for the company associated with the date.

2. Payment Type:

   • CASH or CHECK

3. Payment Amount:

   • Total amount of the payment.

     • For example, a single 100$ check is received and is being applied to two 50$ invoices. The payment amount would be 100$.

   • Must be in currency format.

   • No special characters.

   • The amount of the payment cannot be greater than the applied amount provided.

   • To split a payment across multiple invoices, use a separate line for each invoice with the full payment 'Payment Amount' on each line.

4. Check Num:

   • This is optional when Payment Type = CASH.

   • This is required when Payment Type = CHECK.

   • If the same check is being applied to multiple payments, use the same check number for each payment.

5. Invoice Number:

   • Include invoice number from ClearVantage to link the payment to using INVOICENUM.

   • Payment will be rejected if there is no matching invoice found.

6. Applied Amount:

   • Using the amount field for Applied Amount and Payment amount, payments can be split across multiple invoices and allow for partial payments.

   • Set a different 'Applied Amount' on each line to indicate how much of the 'Payment Amount' should be applied each invoice provided.

   • This can be the same as the value provided in Payment Amount.

     • For example, a single 100$ check is received but being applied to two 50$ invoices. The applied amount would be 50$ for each payment.

7. CUSTOMERID:

   • This is either the CUSTOMERCD (I) or ORGCD (O) that is added to the PAYEECD field on the payment record.

   • This field will populate both the payee’s name and code on the payment record.

   • Payment will be rejected if there is no matching customer or organization record found.

8. IND/ORG

   • Required

   • Value indicates if payment is associated with an Individual (I) or Organization (O) that is added to the PAYEECD field on the payment record.

9. COMPANYCD

   • This field must contain the alphanumeric code value, for example 001 for the company with which the payment is associated with.

   • Each Company included in the file will have all payments for that company go to a specific batch for that company.

     • Example: If the import file contains payments for 3 companies for the same payment date, 3 payment batches will be created.

10. Comments

    • Any additional details or comments here will display in the payment record comment field.

Importing the Lockbox File

The lockbox functionality allows users to create payments by entering each payment manually or with a CSV file. Payments are validated and can be edited prior to being created.

1. Lockbox payments can be created using the lockbox utility window.

   1. Add records manually.

   2. Browse your computer for the CSV file.

Adding Records

1. Use the Add Record button to manually create one payment.

   

2. This will create a blank line in the utility window. Enter the information into the text entry field for each column.

   

3. Click Update to save changes.

4. Click Cancel to close the entry.

Import CSV File

1. Enter the Batch Date

   • This is the date that will be used to create the payment batch.

   • The date provided for each payment in the batch will be used as the payment date.

   • If there is no date provided for the batch, the import process will create a batch using the payment date and company information provided for each payment.

     • All payments for the same date and company will go into the same batch.

2. Please review the Example CSV file provided in the import window. It will be available in your browser’s download folder.

   

3. In the import window, click Browse… to select the file from your computer.

4. Click Import CSV File

   

All lines with payment information will load in the window for review.

Validating Records

1. Once the import file loads and after any manual payments have been made, click Validate Records.

   

2. Use the scrollbar along the bottom of the window to review all columns of data.

   • If needed a row can be highlighted and each column can be edited in the cell. Click Update to save changes.

   Tip

   To view more columns at once, click the right arrow in the window. This will collapse the right panel.

   

3. If needed, lines can be deleted. To remove a payment from the import, highlight the payment line and click Delete Record.

   

Validation Messages

The validation messages that display will use logic that prevent overpayments and ensures accurate payment information is being used for all payments created.

Each error will have a specific validation message to assist in resolving the issue prior to completing the lockbox check import.

These errors can be resolved by updating the CSV file or by editing inline for each row and then selecting “Validate Records” again.

1. No matching individual/organization record found!

   1. Review the CUSTOMERCD or ORGCD for accuracy.

   2. Confirm Ind/Org value is appropriate for the CUSTOMERCD or ORGCD.

   

2. Payment amount is invalid!

   1. Payment must be in currency format and use only 2 decimal places.

   2. This must be a number greater than .01$

   3. No special characters.

   

3. No matching invoice record found!

   1. Confirm the invoice exists in ClearVantage.

   2. If the invoice does not exist, create both the invoice and payment manually.

   3. Remove any lines that do not have an associated invoice number.

   

4. If the invoice record is not found, you may search for the invoice using the invoice lookup on the payment line.

   

5. No valid COMPANYCD provided!

   

   1. Confirm company is 3 digits in the format of “001” or “002”

6. Check amount is different from the sum of multiple associated payments imported!

   1. Confirm that the Payment Amount and Applied Amounts are accurate sums.

      • Example: A 100$ Check (Check # 2) has a Payment Amount of 100$ is being applied to 2 50$ invoices. Then the Applied Amount would be 50$ for each payment. If this 100$ check is being applied to 4 invoices, then each of the 4 payments must equal to 100$.

   

7. Payment amount is greater than invoice balance due!

   

8. If there is more than one error, multiple validation messages will display. All errors must be resolved prior to processing payment

   

9. Ready to Proceed with Payment Creation!

   1. Payment information is valid and ready to be processed.

   

Tip

To view more columns at once, click the right arrow in the window. This will collapse the right panel.

Process Records

After resolving all validation messages, click Process Records to create the payment batch and all payments.

If the invoice does not exist (indicated in the validation message) the invoice will also be created and have the payment associated with it.

When the payment is applied to the Invoice, the system will update the balance due and paid amount fields as needed.

Once the payments and any necessary invoices are created, a Results window will display providing the results for each payment created.

The payment numbers provided here will be the payment number associated with the payment in ClearVantage.

No action is needed, this is for review only. The results window can be closed.

Payment Batches

Using the company provided for each payment in the lockbox import file, ClearVantage will automatically create a unique batch for lockbox payments.

The date provided during import along with the company provided for each payment will be used to create the appropriate batch. All payments for each company will go into the designated company batch for the date selected.

Each batch that the system creates will have the following format:

Example: 001LB230301

• The first three digits indicate the financial company.

• LB is static to indicate the payment is associated with lockbox.

• 2 digits for Year

• 2 digits for Month

• 2 digits for Day

For more information about batches and payments, please review our Payments & Refunds User Guide.

Reporting & Querying

Queries

When the process has been run and the lockbox has successfully created the payments, the Invoice, Payment, and the Batch tables that are updated.

For on the fly searching and reporting needs users can review payments using the Batch list or Payments list view.

1. In the Financial Menu click Batches, use the query tool to filter by

   1. Batch ID

   2. Batch Date

   3. Company

2. In the Financial Menu click Payments, use the query tool to filter by

   1. Payment Date

   2. Payment Batch

Mapping

INVOICE Table Joins
Common mapping from the invoice table:

Table to Join	Key Field	Key Field in Invoice Table
INVOICEITEM	INVOICENUM	INVOICENUM
INVOICEPAYMENT	INVOICENUM	INVOICENUM
PAYMENTS	INVOICENUM	INVOICENUM

PAYMENTS Table Joins
Common mapping from the Payments table:

Table to Join	Key Field	Key Field in Payment Table
BATCH	BATCHCD	USERBATCHCD
INVOICEPAYMENT	PAYMENTNUM	PAYMENTNUM
PAYMENTS	INVOICENUM	INVOICENUM
CUSTOMER	CUSTOMERCD	PAYEECD (where PAYEETYPECD = I)
ORGANIZATION	ORGCD	PAYEECD (where PAYEETYPECD = O)

Reports & Dashboards

Available in the Market Place is a lockbox report that is ready to be downloaded and added in CVPro. This report along with the dashboard gadget are useful for listing and summarizing the batches imported via the lockbox.

• Lockbox Payment Totals by Date Range – Report is available in formatted results or data only for a selected date range.

  

• Lockbox Payments last 30 days – This is a table view dashboard gadget that can be added to any dashboard. This data can also be exported.

  

Additional Resources

Please check out our other user guides for more help creating reports in ClearVantage.

Report Writer

Extreme Report Writer

Field Mapping Guide