The lockbox utility in Microsoft Dynamics GP is a useful tool that allows you to integrate cash receivables from a supplied bank file and update the customer’s account and invoices with the appropriate amounts defined in the bank file. The process allows several cash receipts to be processed at one time without the need to enter each receipt independently. There are two main steps in creating a lockbox entry: Create the configuration file and Run the integration.
Create Lockbox Configuration:
The first step is to create the lockbox configuration file. The lockbox configuration defines where the bank file is located, how the file is formatted and what fields from the file map to Dynamics GP. To create a lockbox configuration file select Cards – Sales – Lockbox.
- Enter the Lockbox ID – a unique id that defined the lockbox configuration
- Enter the Description – a description of the lockbox configuration
- Enter Checkbook ID – Checkbook used for the lockbox configuration
- Currency ID and Bank Account Number will autofill based on the Checkbook ID selected
- Enter Location of Lockbox File – The location of the file supplied by the bank
- Select the File Format Type of the bank file (Fixed Length, Comma- Delimited, Tab-Delimited)
- Select Lockbox Format Type
- Single Line – Each line item includes a unique check applied to one item
- Multiple Line – If a check applies to multiple invoices and lines in the bank file
- Header Row Indicator – In a multiple line setup this is the row indicator the first invoice for the check
- Detail Row Indicator – In a multiple line setup this is the row indicator for the remaining invoice for the check
- Decimal Places for amounts – The selection is 0 to 5. If the bank file has no decimal places, then select the 0. This will automatically use the last two digits as the decimal value. (i.e.,. 10213 with 0 decimal places will be $102.13; 13 with two decimal places will create the same value $102.13)
- Lockbox apply method – Defines how to apply the invoices
- Alternate apply method – A secondary apply method if specific invoices is selected
- Omit Start Import Records – lines to ignore for the import at the top of the bank file
- Omit End Import Records – lines to ignore for the import at the end of the bank file.
- Map the fields to the appropriate values
The system will pull the data in each column of the bank file and allow you to use the magnify glass to map the values.
Run the Lockbox Integration
The second step is to run the integration: select Transactions – Sales – Lockbox Entry.
- Enter the Lockbox ID
- Confirm the lockbox location
- Enter the Batch ID
- Click Add
- Enter the checkbook ID
- Save the batch
- Click Transactions
- Review Exception Report to confirm any issues
- Close the report
Verify the Data
- Review items for integration status:
- Items matched with a customer and fully applied mean the item had no issues.
- Items Matched with a customer and not fully used means the item could find an invoice, but there was more on the payment then the invoice required. Because of this, it placed the remaining amount in an Open Cash Receipt.
- Items not yet matched with a customer or a valid means there was an error, and there is no customer to match the amount to. This item will not integrate.
- Items with the same check that are applied to more than one customer’s invoice means there was an error with the check and it was applied to more than one invoice.
- You can choose a payment and then select the check number to review the invoices paid by a particular check.
- Modify what the payment was applied to, if required.
-
- Select the payment.
- Click the document number to review the current applied amounts to the invoices. Check and uncheck invoices to modify the applied amounts and invoice numbers, if required.
- Modify the Customer ID, if required.
- Select the payment.
- Click the Reset Customer Button.
- Select the correct customer record to associate with a transaction.
Create Batch
When everything has been reviewed and confirmed, click the ‘Create Batch’ button. This approves the batch of cash receipts and closes the window. You then can edit or post these transactions in the cash receipts batch.
- If you receive an error, click OK.
- Click the Refresh button.
- Click Create the Batch button again.
Post the Receivables batch.
- Select Transactions-Sales-Receivables Batches.
- Select the Batch created in Lockbox Processing.
- Post the Batch.
- The payments and invoices should now be affected in Dynamics GP.
[avatar user=”afraser” size=”thumbnail” align=”left” /]ANDREW FRASER | Senior Business Software Consultant
As a Senior Business Consultant, Andrew is responsible for the implementation and support of CRM, GP, and other Microsoft products, ensuring that our solution scoping, architecting, and delivery meet and exceed customer expectations. He helps clients analyze their current business processes and provide them with process improvements. During his time at KTL, Andrew has built lasting relationships with clients by understanding their business needs and provided value by leveraging their existing resources. Andrew has more than 21 years of experience in implementing business software for a variety of industries, including service, government, distribution, not-for-profit and manufacturing. He graduated with a Bachelor’s Degree in Math with concentration in Computer Science and a Bachelor’s Degree in Business Marketing from St. Andrew’s University in Laurinburg, North Carolina. His experience includes helping companies embrace technology to fulfill their organizational needs and creating innovative solutions using CRM, GP and other Microsoft products.
