Exclude "AR Credit Memos" in report

[Former Member]

Hello,

I am running the following query to create a report. I noticed that a 'Credit Memo' was included in my report. Because of that SUM(INV1.LineTotal) does not show the right total. Probably I may need other tables to join so I can add a condition to ignore 'Credit Memo' in the report.

SELECT  distinct  SUM(INV1.quantity) AS Quantity,  SUM(INV1.LineTotal) AS Total

FROM OINV INNER JOIN INV1 ON INV1.DocEntry = OINV.DocEntry INNER JOIN OCRD ON  OCRD.CardCode = OINV.CardCode INNER JOIN OITM ON  OITM.ItemCode = INV1.ItemCode INNER JOIN OITB ON OITB.ItmsGrpCod = OITM.ItmsGrpCod

WHERE INV1.TargetType <> '14' AND OITB.ItmsGrpNam = 'Sys 2'

Thanks in advance.

[kothandaraman_nagarajan]

Hi,

Try this:

SELECT  distinct  SUM(INV1.quantity) AS Quantity,  SUM(INV1.LineTotal) AS Total

FROM OINV INNER JOIN INV1 ON INV1.DocEntry = OINV.DocEntry INNER JOIN OCRD ON  OCRD.CardCode = OINV.CardCode INNER JOIN OITM ON  OITM.ItemCode = INV1.ItemCode INNER JOIN OITB ON OITB.ItmsGrpCod = OITM.ItmsGrpCod

WHERE INV1.TargetType <> '14' AND OITB.ItmsGrpNam = 'Sys 2'

union all

SELECT  distinct  -SUM(RIN1.quantity) AS Quantity,  SUM(RIN1.LineTotal) AS Total

FROM ORIN INNER JOIN RIN1 ON RIN1.DocEntry = ORIN.DocEntry INNER JOIN OCRD ON  OCRD.CardCode = ORIN.CardCode INNER JOIN OITM ON  OITM.ItemCode = RIN1.ItemCode INNER JOIN OITB ON OITB.ItmsGrpCod = OITM.ItmsGrpCod

WHERE RIN1.TargetType <> '14' AND OITB.ItmsGrpNam = 'Sys 2'

Thanks,

Nagarajan

[Former Member]

Hi Nagarajan, The result is NULL.

[kothandaraman_nagarajan]

Try this:

SELECT    SUM(INV1.quantity) AS Quantity,  SUM(INV1.LineTotal) AS Total

FROM OINV INNER JOIN INV1 ON INV1.DocEntry = OINV.DocEntry left  JOIN OCRD ON  OCRD.CardCode = OINV.CardCode left JOIN OITM ON  OITM.ItemCode = INV1.ItemCode left JOIN OITB ON OITB.ItmsGrpCod = OITM.ItmsGrpCod

WHERE  OITB.ItmsGrpNam = 'Sys 2'

union all

SELECT   -SUM(RIN1.quantity) AS Quantity,  -SUM(RIN1.LineTotal) AS Total

FROM ORIN INNER JOIN RIN1 ON RIN1.DocEntry = ORIN.DocEntry left JOIN OCRD ON  OCRD.CardCode = ORIN.CardCode left JOIN OITM ON  OITM.ItemCode = RIN1.ItemCode left JOIN OITB ON OITB.ItmsGrpCod = OITM.ItmsGrpCod

WHERE  OITB.ItmsGrpNam = 'Sys 2'

[Former Member]

I fixed the issue. The part number I added in OITB.ItmsGrpNam was incorrect. But the number of Quantity is not matching in SAP.

[kothandaraman_nagarajan]

Please close this thread.

[Former Member]

Hi,

If all your Credit Memo is created from AR Invoice, your query should be correct. However, that may not be the case always.

Try:

SELECT ItemCode, SUM(T.Quantuty) AS QTY,SUM(T.Total) As Total

(SELECT  INV1.quantity AS Quantity,  INV1.LineTotal AS Total

FROM OINV INNER JOIN INV1 ON INV1.DocEntry = OINV.DocEntry INNER JOIN OCRD ON  OCRD.CardCode = OINV.CardCode INNER JOIN OITM ON OITM.ItemCode = INV1.ItemCode

INNER JOIN OITB ON OITB.ItmsGrpCod = OITM.ItmsGrpCod

WHERE OITB.ItmsGrpNam = 'Sys 2'

UNION

SELECT  -RIN1.quantity AS Quantity,  -RIN1.LineTotal AS Total

FROM ORIN INNER JOIN RIN1 ON RIN1.DocEntry = ORIN.DocEntry INNER JOIN OCRD ON  OCRD.CardCode = ORIN.CardCode INNER JOIN OITM ON OITM.ItemCode = RIN1.ItemCode

INNER JOIN OITB ON OITB.ItmsGrpCod = OITM.ItmsGrpCod

WHERE OITB.ItmsGrpNam = 'Sys 2') T

Thanks,

Gordon

[Former Member]

Hi Gordon,

I tried your query and it gives me an error.

Msg 102, Level 15, State 1, Line 39

Incorrect syntax near 'T'.

Thanks.

[Former Member]

Try:

SELECT T.ItemCode, SUM(T.Quantuty) AS QTY,SUM(T.Total) As Total

(SELECT  OITM.ItemCode, INV1.quantity AS Quantity,  INV1.LineTotal AS Total

FROM OINV INNER JOIN INV1 ON INV1.DocEntry = OINV.DocEntry INNER JOIN OCRD ON  OCRD.CardCode = OINV.CardCode INNER JOIN OITM ON OITM.ItemCode = INV1.ItemCode

INNER JOIN OITB ON OITB.ItmsGrpCod = OITM.ItmsGrpCod

WHERE OITB.ItmsGrpNam = 'Sys 2'

UNION

SELECT  OITM.ItemCode,-RIN1.quantity AS Quantity,  -RIN1.LineTotal AS Total

FROM ORIN INNER JOIN RIN1 ON RIN1.DocEntry = ORIN.DocEntry INNER JOIN OCRD ON  OCRD.CardCode = ORIN.CardCode INNER JOIN OITM ON OITM.ItemCode = RIN1.ItemCode

INNER JOIN OITB ON OITB.ItmsGrpCod = OITM.ItmsGrpCod

WHERE OITB.ItmsGrpNam = 'Sys 2') T

Group By T.ItemCode