Learn how to create a script to write off bad debt

Introduction

A common problem in NetSuite is overdue balances on customer accounts which need to be written off as bad debt.  To do this a journal is created for the bad debt.  The journal entry will then be applied to the invoice.  After the journal entry is applied, the invoice will be marked as “Paid”.  Let me walk you through an example.

Here is a sample invoice I created.  I created a payment for $13,000.  There is an amount due of $1,400.

I will create a Journal Entry for $1,400 which is the amount due.  The journal entry will move the money from the AR account to the Bad Debt Expense account.

To apply this journal to the invoice, create a customer payment record.  Select the customer and the invoice will show on the “Invoices” tab and the journal entry will show on the “Credits” tab.  Select both to apply the journal to the invoice as shown.

Now save the customer payment record.  A customer payment record will not be created.  The journal entry should now be applied to the invoice.  Reload the invoice record and you will see the invoice is paid and the journal has been applied.

Let’s write a map/reduce script that automates this process.  The script will take a list of customer internal IDs.  These customers should have an overdue consolidated balance.  For each customer, the script creates a Journal Entry for the overdue amount just like we did in the example.  It then creates a customer payment record which selects the invoice and the journal entry.  It matches the invoice based on the amount due being equal to the consolidated balance.  It saves the customer payment record which applies the journal to the invoice (no customer payment record is created).  You can obviously change the logic of this script to fit your business needs.

You can test the script using my example above.  Add and deploy the script (code shown below).  Reference my article “Quick Guide to Adding and Deploying a Script in NetSuite” if needed.

/**
 * @NApiVersion 2.1
 * @NScriptType MapReduceScript
 * @NModuleScope SameAccount
*/
//------------------------------------------------------------------
//Script: 		PayInvoiceUsingJournalEntry_MR.js
//------------------------------------------------------------------
define([ 'N/record', 'N/search'],

(record,search) => {

	getInputData = (context) => {
        log.debug('==START==','==START==');
        let customers = [
            536
        ];

        return customers;
    }

    map = (context) => {
        log.debug('context', JSON.stringify(context));
        let customerId = null;
        try {
            customerId = context.value;
            let data = getCustomerData(customerId);
            let jeId = createJournalEntry(data);
            applyJournalEntry(data, jeId);
        }
        catch(e) {
            log.error('Error when processing customer ' + customerId, JSON.stringify(e));
        }
    }

    applyJournalEntry = (data, jeId) => {
        const paymentRecord = record.create({
            type: record.Type.CUSTOMER_PAYMENT,
            isDynamic: true
        });

        paymentRecord.setValue('customer', data.customerId);

        // mark the invoice by using the amount due
        var lineCount = paymentRecord.getLineCount({sublistId: 'apply'});
        for (let i = 0; i < lineCount; i++) {
            let currentLine = paymentRecord.selectLine({sublistId: 'apply',line: i});
            let due = paymentRecord.getCurrentSublistValue({sublistId: 'apply',fieldId: 'due'});
            if (parseFloat(due) == Math.abs(data.balance)) {
                paymentRecord.setCurrentSublistValue({sublistId: 'apply',fieldId: 'apply',value: true});
                paymentRecord.commitLine({sublistId: 'apply'});
                break;
            }
        }
        // mark the journal entry
        let lineCount = paymentRecord.getLineCount({sublistId: 'credit'});
        for (let i = 0; i < lineCount; i++) {
            const currentLine = paymentRecord.selectLine({sublistId: 'credit',line: i});
            const creditId = paymentRecord.getCurrentSublistValue({sublistId: 'credit',fieldId: 'doc'});
            if (creditId == jeId) {
                paymentRecord.setCurrentSublistValue({sublistId: 'credit',fieldId: 'apply',value: true});
                paymentRecord.commitLine({sublistId: 'credit'});
                break;
            }
        }

        let paymentId = paymentRecord.save();
        // should not create a payment record
        if (paymentId) {
            // delete and show error
            record.delete({
                type: record.Type.CUSTOMER_PAYMENT,
                id: paymentId
            });
            log.error('Incorrectly created a customer payment record for customer with id ',data.customerId);
        }
        else {
            log.debug('Success','Successfully applied journal for customer ' + data.customerId);
        }
    }

    createJournalEntry = (customerData) => {
        const accounts = {
            'ar_account' : 6,
            'bad_debt_account' : 63
        }

        let jeRecord = record.create({
            type: 'journalentry',
            isDynamic: true
        });

        jeRecord.setValue('subsidiary', customerData.subsidiary);
        jeRecord.setValue('currency', customerData.currency);

        // add credit
        let lineNum = jeRecord.selectNewLine({sublistId: 'line'});
        jeRecord.setCurrentSublistValue({sublistId: 'line',fieldId: 'account',value: accounts.ar_account});
        jeRecord.setCurrentSublistValue({sublistId: 'line',fieldId: 'credit',value: Math.abs(customerData.balance)});
        jeRecord.setCurrentSublistValue({sublistId: 'line',fieldId: 'entity',value: customerData.customerId});
        jeRecord.commitLine({sublistId: 'line'});
        // add debit
        let lineNum = jeRecord.selectNewLine({sublistId: 'line'});
        jeRecord.setCurrentSublistValue({sublistId: 'line',fieldId: 'account',value: accounts.bad_debt_account});
        jeRecord.setCurrentSublistValue({sublistId: 'line',fieldId: 'debit',value: Math.abs(customerData.balance)});
        jeRecord.setCurrentSublistValue({sublistId: 'line',fieldId: 'entity',value: customerData.customerId});
        jeRecord.commitLine({sublistId: 'line'});

        const recordId = jeRecord.save();

        log.debug('Created journal with Id', recordId);

        return recordId;
    }

    getCustomerData = (customerId) => {
        const searchObj = search.create({
            type: "customer",
            filters: [
                ["internalid", "anyof", customerId]
            ],
            columns: [
                'consolbalance',
                'subsidiary',
                'currency'
            ]
        });
        let data = {};
        searchObj.run().each(function (result) {
            data.customerId = result.id;
            data.balance = result.getValue('consolbalance');
            data.subsidiary = result.getValue('subsidiary');
            data.currency = result.getValue('currency');
            return true;
        });
        log.debug('data', JSON.stringify(data));
        return data;
    }

    summarize = (summary) => {
        log.debug('-- END --');
    }

    return {
        getInputData: getInputData,
        map: map,
        summarize: summarize
    };

});