May 13, 2022 at 08:31 AM

SAP Invoice Query

135 Views

Hi All

I want to retrieve Invoices from OINV table, but I only want to retrieve Invoices that are not either canceled directly through OINV or canceled through A/R Credit Memo. I've written the following query, but it doesn't execute what I want. Do I make mistakes on my query? Your help is really appreciated

=============================

SELECT DISTINCT T0.[DocNum], T0.[DocDate], T0.[DocDueDate], T0.[CardCode], T0.[CardName], T3.SlpName AS 'Sales Employee', T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[LineTotal], T1.LineTotal - (T1.LineTotal*(T0.DiscPrcnt/100)) AS 'Amount After Discount', T1.[BaseDocNum], T0.[U_VATONO], T1.[DiscPrcnt], T0.[DiscPrcnt], T0.[U_TAXRATE]

FROM OINV T0

INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN OITM T2 ON T2.ItemCode = T1.ItemCode

INNER JOIN OSLP T3 ON T3.SlpCode = T0.SlpCode

INNER JOIN RIN1 T4 ON T4.BaseEntry = T1.DocEntry

WHERE T0.[DocDate] BETWEEN [%0] AND [%1] AND T0.Canceled = 'N' AND T2.InvntItem = 'Y'

=====================================