Revenue Amount are different between P&L report and ARInvoice Query

[Former Member]

Hi ,

I pull data from AR Invoice by "posting date" (oinv.docdate) that are include [INV1] detail information for last year. then

compare with Profit and Loss statement, but total amount has almost 370K different. does anybody can let me know did the query

has wrong ?

Thanks.

SELECT T0.[DocNum] as InvNo, T0.[DocStatus], T0.[DocDate] as InvDocDate, Year(T0.[DocDate]) as InvYear, T0.[CardCode], T1.[BaseRef] as DRNo, T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[ShipDate] as DRShipDate, T1.[OpenQty] as InvOpenQty, T1.[LineTotal] as InvTotal, T2.[Segment_0], T2.[Segment_1], T3.[Quantity] as DRShipQty, T3.[OpenQty] as DROpenQty, T3.[LineTotal] as DRTotal FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OACT T2 ON T1.AcctCode = T2.AcctCode INNER JOIN DLN1 T3 ON (T1.BaseEntry = T3.DocEntry and T1.ItemCode = T3.ItemCode) WHERE T0.[CANCELED] = 'N' and T0.[DocType] = 'I'

[Former Member · Accepted solution]

Hi,

You need to include Credit Memo and Manual JE into the query to make the end meet.

Thanks,

Gordon