Skip to Content

SAP B1 A/R Invoice Less A/R Credit Memo Query

May 31, 2017 at 01:35 AM


avatar image
Former Member

Hi experts,

Im looking for a good query for the following report i need. i want to get a report of all A/R Invoice less CM and other transactions for the particular Customer. these are my query and i want to less all CM per Customer or per Transaction.

SELECT DISTINCT 'INV' As DocType, T0.DocDate As Date, T1.Dscription As ItemName, T1.unitMsr As UoM, T3.ItmsGrpNam As Category, T2.U_SMC_Sub_Category As SubCategory, SUM(T1.LineTotal) As Amount, SUM(T1.Quantity) As Qty, T6.County As Area, T6.CardName As CustomerName, T2.U_U_SMC_Vendor As Vendor, T5.[SlpName] As SalesEmployee, SUM(T2.OnHand) As RemainingStock
INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry 
LEFT OUTER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode  
LEFT OUTER JOIN OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod 
INNER JOIN INV12 T4 ON T0.DocEntry = T4.DocEntry
INNER JOIN OSLP T5 ON T0.SlpCode = T5.SlpCode
INNER JOIN OCRD T6 ON T0.CardCode = T6.CardCode
WHERE T0.DocDate BETWEEN [%0] AND [%1]
GROUP BY T0.DocDate,  T6.CardName, T5.SlpName, T1.Dscription, T1.unitMsr,  T3.ItmsGrpNam, T2.U_SMC_Sub_Category, T6.County, T2.U_U_SMC_Vendor
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Jimmy Liang Mar 02 at 09:52 AM

Hi Mervin,

The Credit Memos are using the ORIN and RIN1 tables. You may need to add a UNION ALL section to combine the invoices and credit notes before applying the grouping.

Hope this helps.


10 |10000 characters needed characters left characters exceeded