Learn how to write off customer deposits using a journal entry

06/10/2023
Jaime Requena
0

Introduction

A common problem in NetSuite is open customer deposits which have not been completely consumed. As an example, suppose you created a customer deposit for a sales order that included a shipping cost but then for some reason the shipping cost was removed on the invoice. As a result, the customer deposit will remain open as it will not be completely consumed. Depending on the situation, it may make sense to create a credit memo, customer refund, or write off the balance. This article will detail how to write off the balance using a journal entry. I will walk you through how to do this manually and then offer a script to automate the process.

Step 1: Create a Sales Order

Create a test sales order as shown below. For this example, I’m using a service item that does not need to be fulfilled. The total is $1040.00.

Step 2: Create a Customer Deposit

Click “Create Deposit” and save the record. We now have a customer deposit of $1040.00.

Step 3: Bill the Sales Order

Navigate back to the Sales Order. Approve the Sales Order if necessary. Click “Bill Remaining” to generate the invoice.

Add a discount line to the invoice so the total is less than the customer deposit total. For this example, I added a 10% discount line. Save.

Step 4: Review the Customer Deposit

Navigate back to the Customer Deposit. You can see it is still open. Click on the “Applied To” Tab to see the amount applied to the invoice. The deposit still has $100 that could be consumed (1040 – 940).

Step 5: Create a Journal Entry

Create a Journal Entry for $100. We will debit the AR account and credit the Bad Debt Expense account. Set the Name field on each line.

Step 6: Apply the Journal Entry to the Customer Deposit

Navigate back to the customer deposit in view mode. Click the “Apply” button.

A deposit application record is generated. Click the checkbox next to the Journal Entry which you just created on the invoice sublist. Save the deposit application. After saving, NetSuite redirects you back to the customer deposit record which has been fully applied. Click on the “Applied To” tab to see the journal entry that was applied.

Clicking on the date under “Date Applied” for the Journal will open the deposit application. You can see the journal was applied to the customer deposit.

Step 7: Create a map/reduce script to automate the process

This script takes a list of customer deposits (internal ids) and amounts. It first gets the customer associated with the customer deposit by doing a search. It then creates the journal entry for the amount. Finally, it applies the journal entry to the customer deposit.

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
*/
define([ 'N/record', 'N/search'],

(record,search) => {

	getInputData = (context) => {
        log.debug('==START==','==START==');
        // first value is internal id of customer deposit, second value is amount to create journal for
        let customerDepositData = [
            ["22447","100.00"]
        ];

        return customerDepositData;
    }

    map = (context) => {
        log.debug('context', JSON.stringify(context));
        let data = JSON.parse(context.value);
        try {
            let customerDepositData = getCustomerDepositData(data[0]);
            let jeId = createJournalEntry(customerDepositData, parseFloat(data[1]));
            let depositApplicationRecordId = createDepositApplication(jeId, customerDepositData);
            if (!depositApplicationRecordId) {
                // failed to make deposit application so delete the journal entry
                record.delete({
                    type: record.Type.JOURNAL_ENTRY,
                    id: jeId
                });
                log.error('Journal was deleted as deposit application failed for ' + data[0]);
            }
        }
        catch(e) {
            log.error('Error when processing customer deposit ' + data[0], JSON.stringify(e));
        }
    }

    getCustomerDepositData = (internalId) => {
        let searchObj = search.create({
            type: "customerdeposit",
            filters: [
                ["internalid", "is", internalId],
                "AND",
                ["mainline", "is", "T"]
            ],
            columns: [
                "entity"
            ]
        });
        let data = {};
        searchObj.run().each(function (result) {
            data.id = result.id;
            data.entity = result.getValue('entity');
            return true;
        });
        return data;
    }


    createDepositApplication = (jeId, customerDepositData) => {
        try {
            let depositApplicationRecord = record.transform({
                fromType: record.Type.CUSTOMER_DEPOSIT,
                fromId: customerDepositData.id,
                toType: record.Type.DEPOSIT_APPLICATION,
                isDynamic: true
            });

            let lineCount = depositApplicationRecord.getLineCount({sublistId: 'apply'});
            for (let i = 0; i < lineCount; i++) {
                let currentLine = depositApplicationRecord.selectLine({sublistId: 'apply',line: i});
                let testId = depositApplicationRecord.getCurrentSublistValue({sublistId: 'apply',fieldId: 'doc'});
                if (testId == jeId) {
                    depositApplicationRecord.setCurrentSublistValue({sublistId: 'apply',fieldId: 'apply',value: true});
                    depositApplicationRecord.commitLine({sublistId: 'apply'});
                    break;
                }
            }

            let depositApplicationRecordId = depositApplicationRecord.save();
            log.debug('Created deposit application with Id', depositApplicationRecordId);
            return depositApplicationRecordId;
        }
        catch(e) {
            log.error('Failed to create deposit application', JSON.stringify(e));
        }
        return false;
    }

    createJournalEntry = (customerDepositData, amount) => {
        let accounts = {
            'ar_account' : 6,
            'bad_debt_account' : 63
        };

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

        jeRecord.setValue('subsidiary', 1);

        // add debit
        let lineNum = jeRecord.selectNewLine({sublistId: 'line'});
        jeRecord.setCurrentSublistValue({sublistId: 'line',fieldId: 'account',value: accounts.ar_account});
        jeRecord.setCurrentSublistValue({sublistId: 'line',fieldId: 'debit',value: amount});
        jeRecord.setCurrentSublistValue({sublistId: 'line',fieldId: 'entity',value: customerDepositData.entity});
        jeRecord.commitLine({sublistId: 'line'});

        // add credit
        let lineNum = jeRecord.selectNewLine({sublistId: 'line'});
        jeRecord.setCurrentSublistValue({sublistId: 'line',fieldId: 'account',value: accounts.bad_debt_account});
        jeRecord.setCurrentSublistValue({sublistId: 'line',fieldId: 'credit',value: amount});
        jeRecord.setCurrentSublistValue({sublistId: 'line',fieldId: 'entity',value: customerDepositData.entity});
        jeRecord.commitLine({sublistId: 'line'});

        let recordId = jeRecord.save();

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

        return recordId;
    }

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

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

});

Conclusion

If you have many customer deposits to clean up, this script will get it done fast. Do you need help with cleaning up open transactions in your account? Please contact Suite Tooth consulting here to set up a free consultation.

If you liked this article, please sign up for my newsletter to get these delivered to your inbox here.

Follow on

Jaime Requena Chief Executive Officer

Jaime Requena is a seasoned NetSuite Consultant and Solutions Architect, known for delivering WHITE GLOVE service to businesses. With 15+ years of experience and 3x certifications in ERP, Developer, and Admin, Jaime specializes in highly customized NetSuite accounts, transforming operations for 200+ satisfied customers all across the globe.

Learn how to write off customer deposits using a journal entry