NetSuite Up Close: Custom GL Lines Plug-in to Reclass General Ledger Postings

This article is relevant if you ever wanted to modify the way that NetSuite posts to the general ledger based on some type of rule that is different from the default rules. The technique leverages NetSuite’s SuiteGL feature.

Background

It’s not uncommon to want to route a general ledger posting according to some criteria or rule based on a transaction element or related information.  For example, a classic request is to post “New Sales” to one GL revenue account versus “Existing Business”.  However, NetSuite allows one pointer to the revenue account from the item definition and has no built-in concept of new-sales versus existing-business.  The challenge has always been that NetSuite does not give you direct access to modify the general ledger posting — until now with the 2015.2 release!

In the old model, we would write a script that would inspect a posting transaction and then create a subsequent, independent journal entry for desired reclass work.  We called this the dependent journal entry as it needed to be linked to the original transaction.  It was cumbersome because any modification (update or delete) to the source transaction required error-prone synchronization to the dependent reclass journal entry.  It also meant that you needed a custom linking field between these transaction types slightly obscuring the reclass work.

Our team was delighted when we learned that NetSuite finally opened up the GL posting routines to allow us to create our own GL entries! These custom entries become inherent to the source transaction and avoid the dependent journal entry synchronization management.  The key to understanding this model is that NetSuite is still going to post it the way it would by default; however, as a NetSuite developer, you have the opportunity to write more posting lines (which must balance) which can offset NetSuite’s work — or simply post to any account you want.

Example SuiteScript Custom General Ledger Line Plug-In

The following code example is an adaptation of a client situation where the accounting team wanted to modify the posting of Cost of Goods Sold account (NetSuite’s default based on the item pointer definition) to an Expense account based on a flag on a sales order.  When setting up the plug-in, we applied it to the Item Fulfillment record so it would fire during the posting routine upon shipment.

/*
 * Function: customizeGlImpact
 * Purpose:  Reclass Item Fulfillment GL Postings from COGS to an expense account based on a "billable" flag on Sales Order
 * Developer: SG - Prolecto Senior Consultant
 * Manager: MZ - Principal;
 * Client: Anonymous
 * Note: MZ repurposed for NetSuite community education
*/

function customizeGlImpact(transactionRecord, standardLines, customLines, book)
{
	try {
		//get basic variables for context
		var rectype = transactionRecord.getRecordType();
		var recid   = transactionRecord.getId();
		//noempty is custom function to return a value we expect if null is returned
		var createdfrom = noempty(transactionRecord.getFieldValue('createdfrom'),'');
		nlapiLogExecution('AUDIT', 'customizeGlImpact starting', rectype + ':' + recid + ' createdfrom:'+createdfrom);

		//we expect to be in an item fulfillment from a Sales Order
		if (createdfrom.length == 0) return;

		//learn about the transaction
		var linecount = standardLines.getCount();
		if (linecount == 0) return;  // no work to complete

		nlapiLogExecution('DEBUG', 'standardLines linecount', linecount);

		//assumption is that the deployment was only against the item fulfillment record type
		var sorecord = nlapiLoadRecord('salesorder', createdfrom);

		//only act if the flag is marked as "F"
		var billable = noempty(sorecord.getFieldValue('custbody_billable_nonbillable'),'T');
		nlapiLogExecution('DEBUG', 'billable', billable);
		if (billable == 'T') return;

		//we will create a simple map of the GL accounts (internal IDs) we are looking for and the
		//target account it should move to; ideally this be a global constant or in a lookup table.
		var map = {"1059" : "261"}

		// map_list effectively is a custom collection (not provided) that allows us to summarize
		// the value of the relevant GL accounts as the same account may be used on multiple lines;
		// the map allows us to key on only the GL accounts that need to be reclassed.  The rest are ignored
		var reclass_summary = new map_list(map);

		// loop through the posted lines and add them to the map

		for (var i=0; i<linecount; i++) {

			//get the value of NetSuite's GL posting
			var line =  standardLines.getLine(i);
			if ( !line.isPosting() ) continue; // not a posting item
			if ( line.getId() == 0 ) continue; // summary lines; ignore

			//build a unique key that spans the account, class, dept, and location
			var acc = noempty(line.getAccountId(),'').toString();
			var cls = noempty(line.getClassId(),'').toString();
			var loc = noempty(line.getLocationId(),'').toString();
			var dep = noempty(line.getDepartmentId(),'').toString();

			var key = acc + '|' + cls + '|' + loc + '|' + dep;

			//determine the amount.  debits will be positive.   Add it to the summary map
			var amt = parseFloat(noempty(line.getDebitAmount(),0)) + (parseFloat(noempty(line.getCreditAmount(),0)) * parseFloat(-1));

			reclass_summary.add(key, amt);
		};

		// now the reclass array should have the amounts we want to adjust.  Spin through it as it
		// will have unique combinations of account, class, dept and location
		var arr_reclass = reclass_summary.list;
		var keys = Object.keys(arr_reclass);
		var klen = keys.length;
		for (var k=0; k<klen; k++){
			var key = keys[k];
			nlapiLogExecution('DEBUG', key, arr_reclass[key]);
			var akey = key.split('|');
			var amt = arr_reclass[keys[k]];
			var from_acc = akey[0];
			var cls = noempty(akey[1],'').toString();
			var loc = noempty(akey[2],'').toString();
			var dep = noempty(akey[3],'').toString();
			var to_acc = noempty(akey[4],'').toString();
			nlapiLogExecution('DEBUG', 'to_acc', to_acc);

			// reverse the amounts
			amt = parseFloat(amt) * parseFloat(-1);
			if ( amt==0 ) continue;

			// remove the original amount
			var newLine = customLines.addNewLine();
			newLine.setAccountId(parseInt(from_acc));
			if ( cls.length > 0 ) {
				newLine.setClassId(parseInt(cls));
			};
			if ( loc.length > 0 ) {
				newLine.setLocationId(parseInt(loc));
			};
			if ( dep.length > 0 ) {
				newLine.setDepartmentId(parseInt(dep));
			};
			if ( parseFloat(amt) > = 0 ) {
				newLine.setDebitAmount(RoundNumber(amt,2));
			} else {
				newLine.setCreditAmount(RoundNumber(parseFloat(amt) * parseFloat(-1),2));
			};
			newLine.setMemo("Reclass non-billable expenses");

			var newLine = customLines.addNewLine();
			newLine.setAccountId(parseInt(to_acc));
			if ( cls.length > 0 ) {
				newLine.setClassId(parseInt(cls));
			}
			if ( loc.length > 0 ) {
				newLine.setLocationId(parseInt(loc));
			}
			if ( dep.length > 0 ) {
				newLine.setDepartmentId(parseInt(dep));
			}
            if ( parseFloat(amt) < 0 ) {
				newLine.setDebitAmount(RoundNumber(amt,2) * parseFloat(-1));
			} else {
				newLine.setCreditAmount(RoundNumber(parseFloat(amt),2));
			}
			newLine.setMemo("Reclass non-billable expenses");
		}
	} catch(e) {
		try {
			var err_title = 'Unexpected error';
			var err_description = '';
			if (e){
				if ( e instanceof nlobjError ){
					err_description = err_description + ' ' + e.getCode() + '|' + e.getDetails();
				} else {
					err_description = err_description + ' ' + e.toString();
				};
			};
			nlapiLogExecution('ERROR', 'Log Error ' + err_title, err_description);
		} catch(ex) {
			nlapiLogExecution('ERROR', 'Error performing error logging');
		};
	};
};

Related Articles

  1. Use Custom Logic to Drive NetSuite General Ledger Posting Accounts
  2. How To: Script to Automate NetSuite Journal Entries

Extend NetSuite to Control NetSuite General Ledger Accounting

The new GL Custom Lines Plug-in is a perfect capacity that can illustrate our expertise in both accounting and NetSuite platform based software development.  If you are ready to get control over your NetSuite general ledger posting rules, and you seek assistance, let’s have a conversation.

Be Sociable, Share!

Marty Zigman

Holding all three official certifications, Marty is Southern California's NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 25 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to setup a conversation.

More Posts - Website - Twitter - Facebook - LinkedIn - Google Plus - YouTube

| Tags: , , , , , , , | Category: Accounting, NetSuite, Technical | 15 Comments

12 Comments

  1. Justin
    Posted January 12, 2016 at 11:20 am | Permalink

    Hi – Say for a standard sales order, I want it to hit my standard revenue account and COGS account. But for a “Promotional Item” order, I want it to hit a different COGS account and for a “replacement” order hit a 3rd different COGS account. Can SuiteGL accomplish this?

  2. Posted January 16, 2016 at 11:12 am | Permalink

    Hi Justin,

    Yes, if you have a way to distinguish a standard order from a promotional item sales order, then you can apply the logic you need. You can hard code it or you can use a table driven approach like we did in our Accruals Allowance SuiteGL Program. See this article. . You may need to undo what ever NetSuite does to get the accounting right.

    Marty

  3. Dave
    Posted February 9, 2016 at 4:00 pm | Permalink

    Hi Marty. I’m trying to help a customer who is trying to split up a deposit to go to the same accounts. They are issuing invoice 1 with location A to customer 123 for $300. They are issuing invoice 2 with location B to customer 123 (same customer) for $200. Customer 123 sends a payment of $500. When they deposit it, they want to use SuiteGL to reverse the $500 posting, and generate $300 and $200 postings using the same accounts, but with the locations of the respective invoices.

    However, they are getting a message: This transaction has duplicate accounts. The main line of the transaction and the line labeled ‘###OUR LABEL###’ both use the account named ‘Cash’.

    Can I actually create custom lines against the same account as the standardlines to reverse out the amount, and then create more custom lines against the same account with different locations?

  4. Posted February 9, 2016 at 5:20 pm | Permalink

    Hi Dave,

    I normally would think that you could do what you are trying to achieve. We certainly are doing something similar with revenue and cost of goods accounts. However, the Cash Account is likely special. We know that cash accounts must be of one subsidiary, which all the other accounts can be used across subsidiaries.

    If after trying a number of things, I might ask NetSuite support given this is still a relatively new feature. Finally, since you appear to have good location information relative to the originating invoices, have you considered using the new custom GL transaction approach to generate a new related posting entry that is triggered based on the cash/deposit posting? Typically, we would not break out the cash this way because you are distant from the location as you are working against Cash and the AR account.

  5. Dave
    Posted February 22, 2016 at 3:23 pm | Permalink

    Marty –

    After talking with Netsuite, they told me this:
    Unfortunately, it is currently not possible to have a CustomLine post to the same Account as the Main/Summary line of the Transaction

    I ended up creating a custom transaction (journal), and inside customizeGlImpact, I am creating a new record of my custom transaction type, adding the appropriate lines (including location), and it seems to work quite nicely.

    Thanks, as always, for another nice tip. See you at SuiteWorld. 🙂

  6. Posted February 25, 2016 at 7:13 am | Permalink

    Hey Dave,

    That’s good to know. We have to be careful with the GL Plug-in technology as it is not “fully implemented” from a basic transaction set. For example, we still can’t get to the native “Name” element like you can on other transactions. Indeed, see you at NSW16.

    Marty

  7. David
    Posted March 2, 2016 at 1:33 pm | Permalink

    Hi Marty,

    We are looking to implement NetSuite. A current need is proving difficult.

    We need to periodically update the credit on a journal… Someone will have an account and money will periodically come in. In the meantime we need to enable the customer to withdrawer amounts (for now that doesn’t necessarily have to be available in the balance).

    It seems like Custom GL is the best way of doing this, am I right in my assumption?

  8. Posted March 10, 2016 at 10:26 pm | Permalink

    Hi David,

    My instinct is that SuiteGL is not quite right for this. But I don’t think I understand your flow. Have you consider using 1) a Customer Deposit record or 2) allowing a cash receipt that does not necessarily apply to an open invoice thus keeping a credit on file to be applied to future invoices?

    Marty

  9. Jaysen
    Posted June 22, 2017 at 12:08 pm | Permalink

    Hey Marty,

    You helped me with a previous issue and was wondering if I can get you advisement or insight on this new one.

    I implemented auto customer deposits against cash sale sales orders which is working fine. However, the GL impact is that its crediting/debiting from, what I imagine, accounts that NetSuite arbitrarily set up and assigned. I would like these to impact a specific GL account instead. It sounds like I can achieve this with the above plugin since NetSuite doesn’t have an out of the box solution. Would this work for me to route these transactions to an account of my choosing instead?

  10. Tzvi G
    Posted July 12, 2017 at 12:42 pm | Permalink

    Just a quick typo: “These custom entries become inherit” should probably be “inherent”

  11. Posted July 12, 2017 at 5:03 pm | Permalink

    Hi Jaysen,

    My understanding is that the customer deposit GL account code is a system managed account. You can change the account number on it. Yes, you can use SuiteGL to effectively change the accounting. But you need to be careful. When the customer deposit is consumed, it is going to debit the system account.

    Marty

  12. Posted July 12, 2017 at 5:14 pm | Permalink

    Thanks for the edit suggestion! I have updated the article.

3 Trackbacks

  1. […] NetSuite Up Close: Custom GL Lines Plug-in to Reclass General Ledger Postings […]

  2. […] NetSuite Up Close: Custom GL Lines Plug-in to Reclass General Ledger Postings […]

  3. […] With the advent of NetSuite SuiteGL, we now have the ability to enhance NetSuite’s native GL posting routine. This means that we can, at the transaction level, produce the accruals that otherwise would have been done at period end. This fits nicely in the real-time nature of the application and ensures that management’s policies are being adhered to.  For those more technically minded, see article ”NetSuite Up Close: Custom GL Lines Plug-in to Reclass General Ledger Postings&#8…. […]

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>