SuiteQL: GL Impact

Introduction

In the post SuiteQL: Transactions we discussed the Transaction and the TransactionLine tables.

A third table called the TransactionAccountingLIne table contains the GL impact of the transaction. This table contains the data you will see when viewing the GL impact tab of a transaction.

Getting the GL impact using SuiteQL allows you to see the item associated with a particular GL impact line. This can be very useful for debugging. In this example we will look at a work order close transaction of an account that uses standard costing. Netsuite also has a table TransactionAccountingLIneCostComponent that has a more detailed breakdown of the underlying costs but this post will just discuss getting the GL impact as shown on the GL impact tab.

TransactionAccountingLIne

This table is where the data resides. It has a 1:N relationship with the TransactionLine table i.e. one line item on the transaction can have multiple GL lines.

Since the primary key of the TransactionLine table is the columns TransactionLine.id and TransactionLine.transaction we must join to the TransactionAccountingLIne using both columns:

FROM Transaction INNER JOIN TransactionLine on TransactionLine.transaction = transaction.id
INNER JOIN TransactionAccountingLIne on TransactionAccountingLIne.transactionline = TransactionLine.id
AND TransactionAccountingLIne.transaction = transactionLine.transaction

Here are some important columns of the TransactionAccountingLIne table:

Column	Description
credit	Credit amount if the line is a credit
debit	Debit amount if the line is a debit
netamount	Net amount of line whether it’s a debit or a credit
account	Account internal id of the GL entry
posting	Whether it posted to the GL or not

The item is not stored on the TransactionAccountingLIne table, but we can use the column TransactionLine.item to get the item.

Example WOC GL impact Explained

The GL impact of a Work Order Close of an account that uses Standard costing might look something like this:

Account	Amount (Debit)	Amount (Credit)
Wip Inventory		100
Wip Inventory		500
Wip Inventory		1000
COGS: Overhead Variance	100
COGS: Usage Variance		200
Finished Good Inventory Asset	1700

For each inventory item there is a credit from the WIP inventory account, in this example simply named “WIP inventory”. For the assembly item there is a debit to the inventory asset account in this case “Finished Good Inventory Asset”.

Because the amounts being debited and credited from those accounts are always simply the standard cost of the items times the quantity, they won’t always match up.

1. If the quantity used in the work order was less or then what is usually required to build this assembly. In this case there will be a credit/debit to the “Production Quantity Variance” account. In this example the account is named “COGS: Usage Variance”.
2. Even if we used the appropriate quantities as listed on the BOM, the costs still don’t match up. In this case there will be a credit/debit to the “Production Price Variance” account. In this example the account is named “COGS: Overhead Variance”.

There may be more lines if there are Manufacturing Routing costs associated with the assembly item. For more information about the variances on a Work Order close please refer to SuiteAnswers #28082.

The Query

Given a transaction internal id of 1234:

SELECT
Transaction.tranid as document_number,
BUILTIN.DF(TransactionLine.item) as item,
BUILTIN.DF(TransactionAccountingLIne.account) as account,
TransactionAccountingLIne.debit,
TransactionAccountingLIne.credit,
TransactionAccountingLIne.posting
FROM Transaction INNER JOIN TransactionLine on TransactionLine.transaction = transaction.id
INNER JOIN TransactionAccountingLIne on TransactionAccountingLIne.transactionline = TransactionLine.id
AND TransactionAccountingLIne.transaction = transactionLine.transaction
WHERE transaction.id = 1234

This query will return the following data:

Document_Number	Item	Account	Debit	Credit	Posting
WOC1234	Inventory Item 1	Wip Inventory		100	T
WOC1234	Inventory Item 2	Wip Inventory		500	T
WOC1234	Inventory Item 3	Wip Inventory		1000	T
WOC1234	Assembly Item 1	COGS: Overhead Variance	100		T
WOC1234	Assembly Item 1	COGS: Usage Variance		200	T
WOC1234	Assembly Item 1	Finished Good Inventory Asset	1700		T

NetSuite Boolean values are stored as T or F.

An important detail to note is that that the variance will always be associated with the main item line. For a more detailed breakdown of the variance you would have to query the items standard cost and calculate it yourself.

Conclusion

Until NetSuite provides the ability for saved searches to search GL impact of transactions, NetSuite’s SuiteQL module can be invaluable for generating insights into the GL impact of a transaction and how each item line affects the GL

In the next post we will discuss the Item table and some fields you might use to get information about an item.