NetSuite Mass Update with SuiteScript to Write Off Invoices

This article is relevant if you are an accountant using NetSuite and you need a way to write off many invoices.  However, the article will describe a custom NetSuite mass update pattern that can be used in other accounting or operational situations. This pattern may also be used in Accounts Payable contexts where you have vendor bills that need to be effectively closed through journal entries.

Background

Depending on the business model, an organization may need to write off invoices that are deemed not collectible.  We recently created a native NetSuite based recurring billing solution which produced thousands of invoices in a billing run.  Over the course of time, a portion of these invoices were deemed bad debt.  Our client was very pleased with the automation we created to manage subscriptions and invoice generation.  Yet, a predictable aspect of their business entailed invoices that at some point were deemed not collectible.

To help them keep their operations smooth, we built a custom mass update that would search for specific invoices and write off the remaining amounts due.  What’s great about this offer is that the Mass Update approach allows the Accounting Department fine control over which invoices are targeted for write off.

Using Journal Entries to Write Off Bad Debt

One of the interesting and powerful capacities of NetSuite is to have Journal Entries participate in traditional sub-ledger transaction contexts.  For example, the two ways most accounting systems allow you to close an invoice is to accept a cash receipt or to create a credit memo and then apply the credit memo to the invoice effectively satisfying it.  NetSuite allows a journal entry to be applied as payment against an invoice.  This can be very convenient; further, because I have written before about generating automatic journal entries in SuiteScript, much of this work can be automated as this article will show you.

In this use case, we want to take a specific invoice, determine the amount remaining (open or due), create a journal entry debiting Bad Debt Expense and crediting Accounts Receivable for that amount and then apply the Journal Entry to the Invoice to clean up our Accounts Receivable Aging.

Custom Mass Update to Write Off Invoices

They key to this solution is a Custom Mass Update that will drive SuiteScript to do all the work needed.  The following Custom Mass Update SuiteScript represents the pattern.  I have commented in the code so can follow along.  I assume that you can setup a Custom Mass Update to reference SuiteScript to get this logic to execute.

function MassUpdate_WriteOff(rectype, recid) {
    nlapiLogExecution('AUDIT', 'MassUpdate_WriteOff', rectype + " : " + recid);

    // the invoice to be written off is the input for the mass update; exit if it isn't an invoice
    if (rectype != 'invoice') return;

	// the recid is supplied by the mass update one record at a time.  The business sets the criteria
	// for which invoices will be written off

	// the mass update has three script variables to allow specific configuration by the business in the
	// script deployment records
	// noempty is our custom function (not offered here) that works with nulls by providing a value we can rely on
	// our prolecto_now function allows us to work with dates in our preferred format

    var context = nlapiGetContext();
    var bad_debt_account    = noempty(context.getSetting('SCRIPT', 'custscript_rbe01_bad_debt_account'), '');             // bad debt account
    var writeoff_memo       = noempty(context.getSetting('SCRIPT', 'custscript_rbe01_writeoff_memo'), '');	          // the memo to include with the JE
    var transaction_date    = noempty(context.getSetting('SCRIPT', 'custscript_rbe01_writeoff_date'), prolecto_now(true));// the date, if provided, else today

    // if no bad debt account is provided, we should exit.
    if (bad_debt_account.length==0) return;

    //now call the function that does the real work passing in key inputs
    WriteOff_Invoice(recid, bad_debt_account, writeoff_memo, transaction_date);
};

// write off the specified invoice
function WriteOff_Invoice(invoice_id, bad_debt_account, memo_field, transaction_date)
{
    // the amount to be written off is the amount_remaining, which we can lookup via saved search
    var filters = [new nlobjSearchFilter('internalidnumber', null, 'equalto', invoice_id)];
    var columns = [new nlobjSearchColumn('amountremaining')];
    columns[1] = new nlobjSearchColumn('tranid');
    columns[2] = new nlobjSearchColumn('entity');
    columns[3] = new nlobjSearchColumn('trandate');
    columns[4] = new nlobjSearchColumn('account');
    columns[5] = new nlobjSearchColumn('location');
    columns[6] = new nlobjSearchColumn('class');

    //perform the search to learn about the invoice

    var results = noempty(nlapiSearchRecord('invoice', null, filters, columns),{length:0});
    nlapiLogExecution('DEBUG', 'WriteOff_Invoice results.length', results.length);

    //if we don't get a result, simply return
    if (results.length==0) return;

    //put information into variables for processing
    var customer = results[0].getValue('entity');
    var cus_name = results[0].getText('entity');
    var inv_num  = results[0].getValue('tranid');
    var inv_date = results[0].getValue('trandate');
    var amount   = results[0].getValue('amountremaining');
    var ar_account      = results[0].getValue('account');
    var loc_id      = results[0].getValue('location');
    var class_id = results[0].getValue('class');

    //output to log to help diagnose and watch processing
    nlapiLogExecution('DEBUG', 'write off', customer + ' inv#: ' + inv_num + ' date: ' + inv_date + ' amt: ' + amount + ' sub: ' + sub_id + ' loc:' + loc_id + ' class:' + class_id);

    //we also expect the amount remaining to be greater than zero (something must be open, else, why are we hear?)

    if (parseFloat(amount)<=0) return;

    // if there is no data already in the memo field, go aheand and used the script parameter value
    if (noempty(memo_field,'').length==0){
        memo_field = 'Write-off';
    };

    //build a memo description for easy reference
    memo_field = memo_field.concat(' ').concat(cus_name).concat(' Inv# ').concat(inv_num).concat(' ').concat(inv_date);

    var tran_date = transaction_date;

    // we need to create a journal entry to write off the amount; start with the header
    var je_record = nlapiCreateRecord('journalentry');
    je_record.setFieldValue('tranid', 'Undefined TranId');
    je_record.setFieldValue('trandate', tran_date);
    je_record.setFieldValue('location', loc_id);
    je_record.setFieldValue('class', class_id);

    // now work the lines; first the debit for the bad debt account
    je_record.setLineItemValue('line', 'account', 1, bad_debt_account);
    je_record.setLineItemValue('line', 'memo', 1, memo_field);
    je_record.setLineItemValue('line', 'debit', 1, amount);
    je_record.setLineItemValue('line', 'location', 1, loc_id);
    je_record.setLineItemValue('line', 'class', 1, class_id);

    // then the credit using the AR account associated with invoice [don't assume that there is only one AR account]
    je_record.setLineItemValue('line', 'account', 2, ar_account);
    je_record.setLineItemValue('line', 'memo', 2, memo_field);
    je_record.setLineItemValue('line', 'credit', 2, amount);
    je_record.setLineItemValue('line', 'location', 2, loc_id);
    je_record.setLineItemValue('line', 'class', 2, class_id);
    je_record.setLineItemValue('line', 'entity', 2, customer);

    // save the journal entry and hold the returned ID.  Now we need to apply the journal entry to the invoice
    var journal_id = nlapiSubmitRecord(je_record);

    nlapiLogExecution('DEBUG', 'Created j/e', journal_id);

    // now, we apply the j/e to the customer account using a payment record
    var pmt_record  = nlapiTransformRecord('invoice', invoice_id , 'customerpayment');
    pmt_record.setFieldValue('tranid', tran_date);
    pmt_record.setFieldValue('trandate', tran_date);
    pmt_record.setFieldValue('memo', memo_field);
    pmt_record.setFieldValue('location', loc_id);
    pmt_record.setFieldValue('class', class_id);

    //look for the open journal entry we just created by using the journal ID.  It should be there.
    var lineNum = pmt_record.findLineItemValue('credit', 'doc', journal_id);
    nlapiLogExecution('DEBUG', 'Applying deposit at '+ lineNum);
    pmt_record.setLineItemValue('credit', 'apply', lineNum, 'T');
    pmt_record.setLineItemValue('credit', 'amount', lineNum, amount);

    //submit the payment record; we are complete!
    var pmt_id = nlapiSubmitRecord(pmt_record);

    nlapiLogExecution('DEBUG', 'Created Customer Payment', pmt_id);
    return journal_id;

    // note, ideally we would add error handling to make this more robust in day-to-day use
};

Take Control of your NetSuite and Automate Tasks

The NetSuite business platform is excellent for applying specific logic designed to run your operation more smoothly.  If you would like superior help and advice on how you can get more out of your NetSuite investment, let’s have a conversation.