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 regarded as the top 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 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.

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

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

34 thoughts on “NetSuite Up Close: Custom GL Lines Plug-in to Reclass General Ledger Postings

  1. Justin says:

    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. Marty Zigman says:

    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 says:

    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. Marty Zigman says:

    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 says:

    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. Marty Zigman says:

    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 says:

    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. Marty Zigman says:

    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 says:

    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 says:

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

  11. Marty Zigman says:

    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. Marty Zigman says:

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

  13. Aishwarya says:

    Could you please explain how to set ‘Name’ field while adding custom GL lines?

  14. Marty Zigman says:

    I don’t believe you can set the name this way. It is part of the native transaction that is the source of the transaction.

  15. Keaton Robinson says:

    Hey Marty,

    Have you considered fractional quantities on the item fulfillment record? For example, an item fulfillment record is created for a partial fulfillment. After the item fulfillment record has been has been marked shipped, can the plug-in handle updates to the item fulfillment record quantity field?

    In my testing I have not been able to trigger the plug-in on, “edit”, only when marked shipped.

    Any thought would be great!

  16. Marty Zigman says:

    Hello Keaton,

    SuiteGL is a general ledger (GL) concept only. Thus two matters here in your comment:
    1. Quantities are not GL concepts, only amounts are.
    2. The item fulfillment record has no GL impact (posting = true) until the record status is “Shipped”. Thus the event to fire your script won’t be raised until you are in the right state.

    Marty

  17. brus says:

    Hi Marty,

    Is it possible to set ‘custom transaction line’ field while adding custom GL lines? can you please tell me.

  18. Marty Zigman says:

    Hello Brus,

    I asked one of our consultants as I wasn’s sure. From her testing, it doesn’t appear so. Check out this image: Custom GL Update Testing

    A) The transactionRecord parameter is read only. Hence, using this object to modify the header and line fields is not possible.

    B) Another way to modify the fields is to load the record and made the changes. This works as it updates the header and line fields, however, the system creates unending loop as the CustomGL is always triggered after saving the record. Unlike UE where it stops the execution within the UE.

    C) The nlapiSubmitField function seems to work, but only for the header fields.

    Marty

  19. Hi Marty,

    I’m working on an issue where in case of non-recoverable VAT an other custom GL plugin adds custom lines to the transaction but I can’t read them the same way from the customLines as I can read the standardLines. (it’s even possible that the other plugin runs later but I can’t see any option to set the execution order) Do you have experience with this?
    Regards,
    Viktor

  20. Marty Zigman says:

    Hi Viktor,

    I don’t have direct experience with this situation.

    Marty

  21. Adam Strait says:

    We are trying to add a custom approval workflow to bank deposit transactions but have found that GL Impact occurs as soon as the deposit record is created and is not delayed until a deposit has been approved. NetSuite support told us that we could use the Custom GL Lines plugin to prevent GL impact on creation of this record type, but having read through the provided documentation, I don’t see any indication that our goal can be achieved using this plugin, since its purpose is to add additional GL impact to the standard impact rather than enabling amendments to or cancellation of standard impacts.

    So is my interpretation of the documentation correct or can it be used to delay GL impact until a bank deposit has been approved?

  22. Marty Zigman says:

    My instinct is that you can’t really delay it because the posting flag is Yes. Have you considered using a “staging” clearing account that holds information “in transit” while you are waiting for the actual monies to arrive? Perhaps as the monies actually arrive, especially if they are in summary, you can do a single Bank Transfer to the actual account.

    Marty

  23. Adam Strait says:

    Hi Marty,

    Thanks for your response. This confirms my suspicion.

    In answer to your question about a “Staging” account, this was proposed, but rejected by our accounting manager.

    It looks as though I am going to have to create a Suitelet which mimics the bank deposit form and posts to a custom transaction type, so I can control GL impact manually.

    Thanks again,

    Adam

  24. You could neutralise the GL impact with inverted GL lines as soon as the Bank deposit transaction created (and not approved) and add the original GL impact again when the deposit was approved.

  25. Marty Zigman says:

    Victor,

    Thank you. That may just work should everything line up. Our GL Reclasser tool would make it easy to test these assumptions.

    Marty

  26. Amtoj says:

    hello Marty,
    One of our old NS developers has used your script for GL reclass and the account of the promo triggers the reclass is not the correct account. I am trying to find the place where the developer used the Internal ID so I can swap the internal ID but I am unable to find it.
    I am big fan of your blog.

    Appreciate any help.
    thanks,
    Amtoj

  27. Marty Zigman says:

    Amtoj,

    Send me the code and I can comment. Contact me here:
    https://www.prolecto.com/contact-us/

    Marty

  28. Brian Waugh says:

    Not sure if you’ve faced this before but it appears as though the Custom GL Lines Plug-in is not capable to creating a line under a different subsidiary than the header which is very shortsighted of netsuite and frustrating.

    Case example would be an intercompany item fulfillment from an intercompany transfer order where you want a specific COGS account to be hit rather than the default associated account. In this case, and based on the model there is no setSubsidiaryId() on the customLines even though there is a getSubsidiaryId() on the standard lines. I’ve got a case opened with NS but very frustrating, likely going to have to go with an ugly and less transparent separate journal entry…

  29. Marty Zigman says:

    Thank you Brian for the update. I am not surprised. I believe they want to be very careful about keeping the books balanced by the subsidiary.

    Marty

  30. Jay V. Berry says:

    We would like to implement a GL reclass to provide a holding account for shipments that are in transit. Can I have someone contact me about our project?

  31. Marty Zigman says:

    Hello Jay,

    You can contact me for a private conversation at https://www.prolecto.com/contact-us/

    Also, I would suggest being careful about using GL Reclsser for in-transit. There are two approaches that use best practices:

    1. Use Take Ownership on Inbound Shipment Records
    2. Consider using Transfer Orders. You may want to see how we solved Freight Containers as we naturally produce in-transit in this flow: https://blog.prolecto.com/2017/01/07/track-netsuite-inventory-in-transit-with-freight-containers-and-automated-landed-costs/

    Marty

Leave a Reply

Your email address will not be published. Required fields are marked *