This article is relevant if you are looking for a manner to apply NetSuite unapplied credits to invoices and other credit memos.
Background
For a recent new client who asked us to help them clean up open accounts receivable and customer deposit balances for a previously troubled implementation, we worked with them to isolate out their customers that have balances that needed to be written off.
The client had these situations affecting the balances on the customer ledger:
- Unapplied cash payments
- Outstanding customer deposits
- Outstanding credit memos
- Outstanding invoices
The goal of the project was to get the customer balances down to zero as all the balances were suspect.
Approach to Write Down the Balances
To do this, we needed to be work with a clearing general ledger sales account that would allow us to generate clearing transactions. Thus, we crafted an Other Charge Item and routed the general ledger account to a one-time sales clearing account.
Summary queries were run to determine the customer’s net receivable/deposit situation which then informs the path required to perform the write off:
- Customer Deposit Balances: we needed to craft an invoice for the amount of the customer deposit so that the funds can be applied. A single line on the invoice with the amount of the customer deposit balance will reference our Other Charge Item which routes to our clearing sales account.
- Net Accounts Receivable Credit (negative) Situation: just like our customer deposits balance situation, we needed an invoice so that we can apply the credits. It is important to note that the customer deposit balances are not part of the accounts receivable ledger; readers may be interested in how we can craft a NetSuite customer statement that includes customer deposits. See my article, Yes You Can: Generate a NetSuite Customer Statement on an Invoice with Customer Deposit Accounting.
- Net Accounts Receivable Debit (positive) Situation: a Net debit accounts receivable situation means we needed to craft a credit memo, like our invoice above, with routing to the clearing sales account. We used the same Other Charge type item to create a single line credit memo in the amount of the AR debit.
Generating Credit Memos and Invoices
Once we had the pathway, we elected to use standard CSV import techniques to generate both the invoices and credit memos. We agreed on the date and period for which these clearing transactions would be crafted. We did not try to apply the outstanding funds in the CSV upload because the situation is complex due to all the underlying transactions. We will get to that below.
Of note, this technique for creating invoices and credits memos with a single line is a common approach when we take a client live with NetSuite and they wish to only bring over the outstanding balance to the ledger.
After all the invoice and credit memos are uploaded, we then ran our summary queries again to indeed confirm that the customer deposit balances and the credit memos all net down to zero. However, we were not finished.
Applying Transactions to Each Other to Close Out NetSuite Ledgers
We decided to do the final work with a SuiteScript to apply all the funds together. We used a Map/Reduce script because of its ability to be fed transactions and work in groups (sets). In this case, the group element is the customer record.
NetSuite does not formally discuss this in the documentation, but a Customer Payment record can be used as an agent to apply all the funds together — when you apply all the funds to each other, the payment record will not actually record in the database because we are not going to bring in any new funds. The customer payment record acts as a logical consolidator of everything open so we can easily apply the values to each other.
SuiteScript Code Pattern to Apply Transactions via Customer Payment Record
The code fragment below illustrates the pattern used to apply all open transactions to each other. The new payment record will bring forth all open transactions which then you can walk the sublists to apply funds. By walking the entire set of sublists, you effectively write it off.
function reduce(context) {
//we will be supplied with a list of transactions that make up the customer balance
try {
var entityId = context.key; // our map pattern grouped by customer record.
var REC;
if (context.values && context.values.length > 0) {
for (var i = 0; i < context.values.length; i++) {
//get the transaction in the list
var transactionObj = JSON.parse(context.values[i]);
log.debug(funcName + 'transactionObj', transactionObj);
//generate the customer payment record against the customer
if (!REC) {
REC = record.create(
{ type: record.Type.CUSTOMER_PAYMENT,
defaultValues: { entity: entityId }
});
}
// determine the transaction type and apply funds to the right sublist
switch (transactionObj.trxType.value) {
case 'CustInvc':
applyTrxToPayment(REC, 'apply', transactionObj.internalId.value)
break;
case 'CustCred':
applyTrxToPayment(REC, 'credit', transactionObj.internalId.value)
break;
case 'CustPymt':
applyTrxToPayment(REC, 'credit', transactionObj.internalId.value)
break;
case 'CustDep':
applyTrxToPayment(REC, 'deposit', transactionObj.internalId.value)
break;
}
}
}
if (REC) {
var recordId = REC.save();
//note, the record will not actually be created here, we will see
//a result of "0".
}
} catch (e) {
log.error(funcName, e);
}
}
//helper function to allow us to apply funds from any point
function applyTrxToPayment(REC, sublistId, internalId) {
var tmpLineNum = REC.findSublistLineWithValue({ sublistId: sublistId, fieldId: 'internalid', value: internalId });
if (tmpLineNum > -1) {
REC.setSublistValue({ sublistId: sublistId, fieldId: 'apply', value: true, line: tmpLineNum });
}
}
Work with Expert NetSuite Professionals
One of the great things about the NetSuite platform is our ability to solve problems in many different ways. The approach offered here simplified the problem by allowing us to query the customer balance situation in summary, craft a single transaction to write it off, and then use the NetSuite platform to clean it all up.
Our clients tell us that we have a unique blend of understanding fundamental accounting, while we have exceptional skills to adapt the Platform to solve business problems. This article illustrates that capacity.
If you found this article helpful, feel free to sign up for notifications of new articles. If you would like to work with professionals that can navigate your complex situation, let’s have a conversation.
See Related Articles
Marty Zigman
Holding all three official certifications, Marty is regarded as the top NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.
More Posts - Website - Twitter - Facebook - LinkedIn - YouTube
