Learn the background of how Accounts Receivable balances are brought together using SuiteQL
Custom Customer Statements
As described in this article: Extend NetSuite Customer Statements, from Marty Zigman, one of the popular tools we offer is the ability to produce enhanced customer statements. There are 2 components used in the solution:
- Prolecto’s Content Rendering Engine (“CRE”): A versatile tool to create and distribute external-facing content such as customer forms, special reports, and Vendor Remittance statements. The content can be distributed as PDF or HTML embedded in an email, and either automatically sent or via a suitelet. This tool is extremely powerful, and we often use it as part of holistic business process improvements.
- SuiteQL queries: Since early 2021 the ability to use SQL to bring together far-reaching data into one report in NetSuite has been a game changer.
The latest version of the CRE tool leverages SQL queries as the “inputs” for the content shown in a PDF or email body.
In this article I will describe the background of the components needed to produce a Customer Statement and the structure in NetSuite that allow us to bring the data together in understanding a customer’s balance.
NetSuite Accounts Receivable Patterns
Accounts that are of the Account Type “Accounts Receivable” (and “Accounts Payable”) have special treatment in NetSuite. Unlike other account types, when a transaction impacts a GL Account that is of one of these types, it can be “settled”. In other words, in addition to a simple running total of a balance based on debits and credits, specific transactions can be applied to each other. When entering a customer payment and “applying” it to unpaid invoices, NetSuite preserves a link between the transactions. At first glace this may be obvious, but there are some important ramifications that might not be intuitive:
- Presentation of balance aging on A/R or A/P reports depend on settlement of invoices. Even if a customer or vendor balance is zero, there may be results showing on a standard Aging report as positives and negatives, if transactions are not “settled” against each other.
- Reporting tools in NetSuite can show values based on “settlement”. Certain transaction types will have values as “open” or “unpaid” balances, and other transactions types may have “amount applied” or “amount unapplied”.
- Currency revaluation works differently on “settled” transactions vs. open transactions, if the underlying transactions are in a foreign currency as compared to the Subsidiary’s base currency.
One of the important improvements that we can provide is the ability to include Customer Deposit balances (prepayments) in a customer’s open balance. Being able to present open Customer Deposit balances in a similar way as open Invoices and open Credit Memos is challenging.
Customer Deposits in NetSuite (prepayments from customers) have special treatment for A/R reporting purposes. They do not impact Accounts Receivable, rather they result in a credit to a liability account, “Customer Deposits”. Yet, they represent an important part of the Accounts Receivable collection cycle. NetSuite allows the application of Customer Deposits to invoices, via a transaction called “Deposit Application” which acts as a reclassing entry to move the credit out of Customer Deposits liability accounts into an Accounts Receivable account so that it can settle an invoice or other A/R balance.
Generally, Journal Entries should be avoided when attempting to impact Accounts Receivable. If there is no “name” on the line, there will be no way to settle the A/R balance. If a name exists on the JE line that is impacting A/R, it can be settled against any other A/R transaction. Another challenge with Journal Entries impacting A/R is that not all of the fields that are available on Invoices or Credit Memos are available on Journal Entries in Saved Search or SuiteQL query reporting. The table at the end of this article will show which fields are available for Journal Entry balances.
There are really 2 ways to produce analysis of customer balances using SuiteQL tools.
- Balance Approach: It would be straightforward to produce a simple analysis of the customer balances using SQL. We could query the transaction impacts in either the base currency or the transactional currency by pulling in the below fields:
FROM Transaction Tran
INNER JOIN Transactionline TRL ON (Tran.id = TRL.transaction)
INNER JOIN Transactionaccountingline TRA ON (Tran.id = TRA.transaction AND TRL.id = TRA.transactionline)
- Aging Approach: If we want to get Aging buckets, we need to know not just the “debits and credits” side of transactions, but also the amounts still open on each transaction. For example, if an invoice date was 60 days ago, and a partial payment was made 30 days ago, we do not want to see a debit in the 60 column for the full invoice amount and a credit in the 30 day column for the full payment amount. Obviously, we want only the unpaid balance of that invoice in the 60 day bucket.
Generally, companies would be more interested in the second approach. We thus need to find which fields to pull in a query to provide the “Open Balances”. The tricky part here relates to Transactions Types and the concepts of what NetSuite considers an “Open” balance. The below table shows the tables, field mapping, and meanings of each available field:
|All Types||transactionLine||foreignamount||Transaction amount in transaction currency|
|All Types||transactionAccountingLine||amount||Transaction amount in GL base currency|
|Invoice||transaction||foreignAmountPaid||Total paid amount, in the transaction currency|
|Invoice||transaction||foreignAmountUnpaid||Total unpaid (open) amount, in the transaction currency|
|Customer Deposit, Payment, Credit Memo||transaction||foreignPaymentAmountUnused||Total unapplied (open) amount in the transaction currency|
|Customer Deposit, Deposit Application, Payment, Credit Memo||transaction||foreignPaymentAmountUsed||Total applied amount in the transaction currency|
|Invoice, Journal Entry||transactionLine||foreignAmountPaid||Total paid amount, in the transaction currency (note: JE only if Dr to A/R)|
|Invoice, Journal Entry||transactionLine||foreignAmountUnpaid||Total unpaid amount, in the transaction currency (note: JE only if Dr to A/R)|
|Customer Deposit, Deposit Application, Payment, Credit Memo, Journal Entry||transactionLine||foreignPaymentAmountUsed||Total applied amount in the transaction currency (note: JE only if Cr to A/R)|
|Customer Deposit, Payment, Credit Memo, Journal Entry||transactionLine||foreignPaymentAmountUnused||Total unapplied (open) amount in the transaction currency (note: JE only if Cr to A/R)|
|Invoice, Journal Entry||transactionAccountingLine||amountPaid||The amount “settled” against a Debit to A/R, in GL base currency (note: JE only if Dr to A/R)|
|Invoice, Journal Entry||transactionAccountingLine||amountUnpaid||Total unpaid amount, in GL base currency (note: JE only if Dr to A/R)|
|Customer Deposit, Deposit Application, Payment, Credit Memo, Journal Entry||transactionAccountingLine||paymentAmountUsed||Total applied amount in GL base currency (note: JE only if Cr to A/R)|
|Customer Deposit, Payment, Credit Memo, Journal Entry||transactionAccountingLine||paymentAmountUnused||Total unapplied (open) amount in GL base currency (note: JE only if Cr to A/R)|
Observe the following general guidelines in the above table:
- The “Transaction” table fields related to open/unapplied amounts are not available for Journal Entries
- The “transactionLine” and “TransactionAccountingLine” table fields are available for Journal entries, and the specific fields available depend on whether there is a credit or debit to A/R
- “transactionAccountingLine” table fields only show the Base Currency (GL) amounts, and the other two tables’ fields only show the original transaction currency amounts
Once we understand the fields and tables that are available, we can use SQL arguments to pull in the necessary field values to craft the balances and aging buckets. I worked together with members of the incredibly talented team at Prolecto to get the queries wired up to the CRE tool to produce the below output: