Create Impactful Customer Statements in NetSuite by Leveraging the power of SuiteQL

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:

1. 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.
2. 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:

1. 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.
2. 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”.
3. 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.

Customer Deposits

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.

Journal Entries

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.

Reporting Approaches

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:

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

Transaction Types	Table	Field	Meaning
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)

Conclusion

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:

If you would like to learn about working with us, or would like to hear how we can put our tools to work for you, please feel free to reach out!