Skip to Content
Former Member
Feb 06, 2011 at 04:48 AM

Filtering invoices


I am running this query to pull invoice data, but it doesn't filter payments that were voided or credited. How can I filter this data?

SELECT T0.DocNum, T1.InvoiceId, 'xxx' + T0.CardCode, QUOTENAME(T0.CardName,'"'), Convert(Decimal(10,2),T0.DocTotal),

CONVERT(VARCHAR(10), T0.UpdateDate, 101) AS DocDate, Convert(Decimal(10,2),T1.SumApplied), T0.NumAtCard, T3.ShipToDef,


FROM xxx.dbo.VPM2 T1

INNER JOIN xxx.dbo.OPCH T0 ON T1.DocEntry = T0.DocEntry

INNER JOIN xxx.dbo.OVPM T2 ON T2.DocNum = T1.DocNum

INNER JOIN xxx.dbo.OCRD T3 ON T0.CardCode = T3.CardCode

WHERE T0.UpdateDate = '02/03/2011'