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
};
});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.
05/16/2024
What is Netsuite supply chain management?
We aim to bring unmatched expertise and professionalism to your NetSuite initiatives. Let’s talk about how our NetSuite consultancy can make a difference!
It’s Been 4+ Years Now And We Have Worked With Hundreds Of Clients, Building Our Way To The Top, One Happy Client After Another! Their Voices Of Satisfaction Serve As A Testament To Our Success –
We can help you achieve as your dedicated Lead NetSuite Consultant. My team and I are experts in tailoring the NetSuite platform to meet the unique challenges of your business in this fast-paced digital era.
