suiteQL query to get invoices by a date range

The SuiteQL query below returns all customer invoices issued in the past 30 days.

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	

Leave a comment

Your email address will not be published. Required fields are marked *