As a part of Order to Cash process, customers are sending payments based on the invoice received. On some cases, received payment amount is slightly higher than the invoice amount which is considered as overpayment. On this article, we will cover how to address this exception using SuiteScript.

Accountants were tracking overpayments and creating a Journal Entry for every overpayment to realize the amounts correctly in the ledger. This became an extra workload for the users and we were asked if there is a better way to handle it.

Considering the use case, it is agreed to filter the overpayments by a threshold amount specified for the subsidiary and payment currency. In this way, we were able to identify the customer payments that needs to be captured by the process using transaction search. With the use of Map/Reduce script, it was possible to group the payments and create Journal Entries that will correctly realize the transactions in the ledger.

Script is designed to accept the following parameters:

• Subsidiary – Subsidiary of the payment transactions. This is also used when creating the Journal Entry.
• Currency – Payment currency to have the threshold set correctly between different currencies. This value is also passed to the Journal Entry.
• Amount – Maximum amount that will considered as overpayment in the currency.
• Account – GL Account to be used to post the overpayment amounts.
• Department, Class, Location – These are used on the Journal Credit lines for correct booking.
• Start Date – Payments created after this date will be taken into consideration.

On the getInputData phase, transaction search will be executed to find the customer payments to be processed. Identified payment transactions are grouped on the map phase based on the posting period. Script is designed to create a single Journal Entry for each posting period. The Journal Entry will include all the payment transactions in that posting period.

Journal Entry creation is done on the reduce phase. Transaction Date of the Journal Entry is determined by the posting period set on the payment transactions. If the related posting period is not locked or closed, last date of the posting period is set. In other cases, current date will be used. For the overpayments, single debit line per payment transaction is added to the Journal Entry with the remaining amount from the customer payment. These amounts are grouped per customer and single credit line per customer is added to balance the Journal Entry. After the creation of the Journal Entry, reduce phase will be finalized by applying the Journal Entry to the Customer Payments.

Note: getTransactionDateByPeriod function returns arlocked true only if all subsidiaries have their AR locked. It is possible to improve this function by using TaskItemStatus (taskitemstatus) search (not documented officially).

Summarize phase will log any errors encountered during map or reduce phases. It is also possible to send emails in case required.

Source code and the XML file from the script record is available on our GitHub repository. You can review and analyze to see if it will help your business for a similar scenario. In case you will decide to deploy the script to your account, please update the values under script deployment section.

We would like to hear your opinion if you liked this solution or if you think there is a place for improvement. If you have any comments on this solution, please use the section below. For any other communication, please use the form on the Contact Us page.