Published on December 7, 2020.

Last week, a fellow NetSuite developer reached out to me for help with SuiteQL. She's new to SuiteQL, and SQL in general. She was trying to find a way to query for return authorizations (RA), also known as return merchandise authorizations (RMA) or return goods authorizations (RGA). Her company is in the process of reviewing their returns policy, and is also trying to identify items that are often returned.

Specifically, she wanted queries for pulling a summary of RMAs, RMAs summarized by customer, and RMAs grouped by item - all for a given date range. Included below are the three queries that I created for her.

RMAs by Date Range

SELECT
	Transaction.TranDate,
	Transaction.TranID ,
	BUILTIN.DF( Transaction.Entity ) AS Customer,
	BUILTIN.DF( Transaction.Status ) AS Status
FROM
	Transaction
WHERE
	( Transaction.Type = 'RtnAuth' )
	AND ( Transaction.TranDate BETWEEN TO_DATE('2020-01-01', 'YYYY-MM-DD' ) AND TO_DATE('2020-11-30', 'YYYY-MM-DD' ) )	
	AND ( Transaction.Voided = 'F' )

RMAs Summarized by Customer

SELECT
	BUILTIN.DF( Transaction.Entity ) AS Customer,
	COUNT(*) AS RMACount,
	( SUM( Transaction.ForeignTotal ) * -1 ) AS RMATotal
FROM
	Transaction
WHERE
	( Transaction.Type = 'RtnAuth' )
	AND ( Transaction.TranDate BETWEEN TO_DATE('2020-01-01', 'YYYY-MM-DD' ) AND TO_DATE('2020-11-30', 'YYYY-MM-DD' ) )	
	AND ( Transaction.Voided = 'F' )
GROUP BY
	BUILTIN.DF( Transaction.Entity )
ORDER BY
	Customer

RMAs Summarized by Item

SELECT
	Item.ItemID,
	Item.Description,
	Count(*) AS RMACount
FROM
	Transaction
	INNER JOIN TransactionLine ON
		( TransactionLine.Transaction = Transaction.ID )
	INNER JOIN Item ON
		( Item.ID = TransactionLine.Item )
WHERE
	( Transaction.Type = 'RtnAuth' )
	AND ( Transaction.TranDate BETWEEN TO_DATE('2020-01-01', 'YYYY-MM-DD' ) AND TO_DATE('2020-11-30', 'YYYY-MM-DD' ) )	
	AND ( Transaction.Voided = 'F' )
GROUP BY
	Item.ItemID,
	Item.Description
ORDER BY
	RMACount DESC,
	Item.ItemID

All of those queries are based on the Transaction table, which is where returns are stored. They're identified by the transaction type "RtnAuth." The query that summarizes returns by item includes joins from the Transaction table to the TransactionLine and Item tables. Those joins are required to get the item's ItemID and Description values.

If you need help with SuiteQL, please feel free to contact me. I'm booked up through the end of 2020, but will be providing one-hour SuiteQL support calls starting in January 2021.