NetSuite: Use SuiteQL to Get Customer Invoices by Date Range

This SuiteQL query returns all customer invoices issued in the past 30 days, including the customer's name and PO number, sales order number, sales rep, status, balance due, and more.

SELECT
	Transaction.ID AS Invoice,			
	Transaction.TranID AS InvoiceNumber,	
	Transaction.TranDate AS InvoiceDate,
	Transaction.Entity AS Customer,
	BUILTIN.DF( Transaction.Entity ) AS CustomerName,
	Transaction.OtherRefNum AS CustomerPONumber,
	TransactionLine.CreatedFrom AS SalesOrder,
	BUILTIN.DF( TransactionLine.CreatedFrom ) AS SONumber,	
	Transaction.Employee AS SalesRep,
	BUILTIN.DF( Transaction.Employee ) AS SalesRepName,
	Transaction.ForeignTotal AS TotalAmount,
	REPLACE( BUILTIN.DF( Transaction.Status ), 'Invoice : ', '' ) AS Status,
	Transaction.ForeignAmountUnpaid AS BalanceDue,
	Transaction.DueDate			
FROM
	Transaction		
	INNER JOIN TransactionLine ON
		( TransactionLine.Transaction = Transaction.ID )
		AND ( TransactionLine.MainLine = 'T' )
	LEFT OUTER JOIN Transaction AS SalesOrder ON
		( SalesOrder.ID = TransactionLine.CreatedFrom )
WHERE
	( Transaction.Type = 'CustInvc' )
	AND ( Transaction.TranDate >= BUILTIN.RELATIVE_RANGES( 'DAGO30', 'START' ) )
ORDER BY			
	Transaction.TranID DESC

Related Posts