Use Custom Logic to Drive NetSuite General Ledger Posting Accounts

This article is relevant if you want to have NetSuite post sales (or other types) to different general ledger account based on custom logic.

Background

Accounting systems use different approaches to post entries to the general ledger.  In NetSuite’s case, when working with invoices, the lines on the transaction drive the respective general ledger entries.  More specifically, the item definitions on each transaction line has a single general ledger account code reference.

When you save a NetSuite invoice, the system determines the general ledger debits and credits.  The debit, which typically references Accounts Receivable, can be modified if you expose that account on the invoice form.  The credits though all come through the item definition lookups behind the scenes.

Logic for New Sales, Existing Sales and Renewal Sales

In our work with clients and partners, a request sometimes comes up to categorize sales as “New”, or “Existing” or “Renewal”.  The idea is that if produce a sale to a new customer, we want that revenue going to our “New Sale” account.  If the customer previously purchased from us, we want that revenue to go to “Existing Sales”.  If we are selling a product that is renewed each year, such as insurance, we want those sales to go to “Renewal Sales”.  With the proper logic based on our business operation, we can then have a more intelligent assessment of our business performance when we review financial statements.

Unfortunately, NetSuite does not make this straightforward and simple with basic SuiteScript.   Because we work with NetSuite at the API level, we often experiment with the NetSuite platform to confirm assumptions or push on capacities not well-documented.  In this case, we explored if we could modify the General Ledger pointer before the invoice is committed.    Even when we modified the general ledger pointer on the invoice line item before it was committed to the database, NetSuite modified our API work and looked up the pointer defined on the item.

Bummer.  Other accounting systems allow this to happen with concepts such as distribution accounts.  Yet, I respect that NetSuite is stable and this account coding logic constraint is one good way things stay well under control.

How to Produce Custom Logic to Point to Different General Ledger Accounts

We solved this by creating a framework that generates related journal entries when ever an invoice or credit memo is updated.  While the concept may seem simple on the first pass, the number of use cases can be challenging.  Here is the basic logic structure (we’ll spare you reading SuiteScript source code; although I suspect a few readers would get into that):

1. On a New Invoice Save (aftersubmit) event, do the following:
• Determine the state of the customer: have they purchased from us before?
• Review each line item; exclude non-GL related items such as item descriptions.
• Determine if this item has actually been sold in the past.  If so, it’s a renewal.  If the customer is not new, but the item has never been sold, then this is an Existing sale.
• Update the line items to stamp them as to their nature.
• Create a journal entry to debit NetSuite’s credit and then credit our new GL revenue reference.
• Update the new journal entry to point it back to the invoice so that we can see it is linked for better reporting and other lookup work (see below).
2. If the Invoice is Edited, do the following:
• Look up the linked Journal entry and reverse it.
• Perform the basic work we did in the New Invoice scenario.
3. If the Invoice is Deleted, reverse the linked Journal entry.
4. If a Credit Memo is Created off an invoice, do the following:
• Determine the nature of the linked journal entry on the invoice to get the GL references.
• Create another journal entry to offset the work NetSuite does to debit item-defined sales so that we can debit the new sales account.
5. If a Credit Memo is Edited or Deleted, follow a similar logic structure as we did for the invoice.

Related Article Addendum

Note, this technique is now circumvented with NetSuite’s new Suite GL Custom Lines Plug-in  See related article “NetSuite Up Close: Custom GL Lines Plug-in to Reclass General Ledger Postings” here.

Summary