Customer Invoices: By Date Range

Returns all customer (accounts receivable) invoices for a given date range, with totals and statuses.

-- Contributor: Tim Dietrich (timdietrich@me.com)
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		

Click here to return to the list of available queries.