SAP B1 query for turnover in sales sorted by item and supplier.

[former_member377478]

Hello experts,

please, is it possible to create a query SAP B1 for finding turnover in sales by item and supplier? I would need to know exact total quantity of sold items sorted by supplier.

Is it possible?

Thank you in advance

jonmar7

[former_member377478 · Accepted solution]

Hello,

the content of a query what i need to see is right, but quantity still not correspond to figures from Sales Analysis Reports which are made directly from SAP...

Maybe stock transfers or other transactions ,which are not related to sales = invoices and credit notes and correction invoices, are also included.

Thank you and regards

Jonmar7

[Former Member · Accepted solution]

Try to include Credit Memo in this way:

SELECT T4.CardCode,T4.CardName, SUM(T0.Price * T0.Quantity) AS turnover,SUM(T0.Quantity) AS QTY, T2.ItmsGrpNam, T5.GroupName, T1.ItemCode, T1.ItemName
FROM INV1 T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OITB T2 ON T2.ItmsGrpCod = T1.ItmsGrpCod INNER JOIN OINV T3 ON T3.DocEntry = T0.DocEntry INNER JOIN OCRD T4 ON T1.CardCode = T4.CardCode INNER JOIN OCRG T5 ON T4.GroupCode = T5.GroupCode
WHERE T4.CardName LIKE '[%0]%'AND T3.DocDate Between [%1] AND [%2]
GROUP BY T4.CardCode, T4.CardName, T2.ItmsGrpNam, T5.GroupName, T1.ItemCode, T1.ItemName
UNION ALL
SELECT T4.CardCode,T4.CardName, SUM(-T0.Price * T0.Quantity) AS turnover,SUM(-T0.Quantity) AS QTY, T2.ItmsGrpNam, T5.GroupName, T1.ItemCode, T1.ItemName
FROM RIN1 T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OITB T2 ON T2.ItmsGrpCod = T1.ItmsGrpCod INNER JOIN ORIN T3 ON T3.DocEntry = T0.DocEntry INNER JOIN OCRD T4 ON T1.CardCode = T4.CardCode INNER JOIN OCRG T5 ON T4.GroupCode = T5.GroupCode
WHERE T4.CardName LIKE '[%0]%'AND T3.DocDate Between [%1] AND [%2]
GROUP BY T4.CardCode, T4.CardName, T2.ItmsGrpNam, T5.GroupName, T1.ItemCode, T1.ItemName

Inventory transfer should not affect your sales at all. As for A/R Correction Invoice, you need to include OCSI and CSI1 tables. We don't have those data to try. You have to add them by yourself.

[Former Member · Accepted solution]

Hello

this is a fantastic query but only brings up suppliers is there the a similar query for customers. My knowledge is limited and I can't even see where the query limits it to suppliers only.

Any advice would be greatly welcomed.

thank you

[Former Member]

Try this one to get better selection:

SELECT T4.CardCode,T4.CardName, SUM(T0.Price * T0.Quantity) AS turnover,SUM(T0.Quantity) AS QTY, T2.ItmsGrpNam, T5.GroupName, T1.ItemCode, T1.ItemName

FROM INV1 T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OITB T2 ON T2.ItmsGrpCod = T1.ItmsGrpCod INNER JOIN OINV T3 ON T3.DocEntry = T0.DocEntry INNER JOIN OCRD T4 ON T1.CardCode = T4.CardCode INNER JOIN OCRG T5 ON T4.GroupCode = T5.GroupCode

WHERE T4.CardName LIKE '[%0\]%'AND T3.DocDate Between [%1\] AND [%2\]

GROUP BY T4.CardCode, T4.CardName, T2.ItmsGrpNam, T5.GroupName, T1.ItemCode, T1.ItemName

[Former Member]

Hi Jonmar,

Try this,

SELECT T0.CardCode, Max(T0.CardName) 'Customer Name', 
T1.ItemCode, Max(T1.Dscription) AS 'Item Discription',  SUM(T1.Quantity) AS 'Turn Over',
T0.DocTotal
FROM OINV T0
INNER JOIN INV1 T1 ON T1.DocEntry = T0.DocEntry
WHERE T0.CANCELED = 'N'
GROUP BY T0.ItemCode, T1.CardCode, T0.DocTotal
ORDER BY T0.ItemCode, T1.CardCode, T0.DocTotal

OR

SELECT T0.CardCode, Max(T0.CardName) 'Customer Name', 
T1.ItemCode, Max(T1.Dscription) AS 'Item Discription',  SUM(T1.Quantity) AS 'Turn Over',
T0.DocTotal
FROM OINV T0
INNER JOIN INV1 T1 ON T1.DocEntry = T0.DocEntry
WHERE T0.CANCELED = 'N'
T0.DocType = 'I' 
AND 
T1.LineStatus != 'O'
GROUP BY T0.CardCode, T1.ItemCode, T0.DocTotal
ORDER BY T0.CardCode, T1.ItemCode, T0.DocTotal

Regards,

Madhan.

[former_member377478]

dear Gordon,

It is not the same whatś I meant, I have only total summary, I can´t select from... to..., could you try to add something? I can´t check if the shown quantity contents invoices + credits...and the period of turnover...

SELECT OCRD.CardCode,OCRD.CardName, SUM(INV1.Price * INV1.Quantity) AS turnover,SUM(INV1.Quantity) ,OITB.ItmsGrpNam, OCRG.GroupName, OITM.ItemCode, OITM.ItemName

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

GROUP BY OCRD.CardCode, OCRD.CardName, OITB.ItmsGrpNam, OCRG.GroupName, OITM.ItemCode, OITM.ItemName

Thank you and regards

Jonmar7

[Former Member]

Hi,

You may try this one:

SELECT T0.ItemCode, Max(T0.Dscription) 'Item Discription', T1.CardCode, Max(T1.CardName) 'Customer Name', SUM(T0.Quantity) 'Turn Over'

FROM dbo.INV1 T0

INNER JOIN dbo.OINV T1 on T1.DocEntry = T0.DocEntry

WHERE T1.DocType = 'I' AND T0.LineStatus != 'O' AND T1.CANCELED ='N'

GROUP BY T0.ItemCode, T1.CardCode

ORDER BY T0.ItemCode, T1.CardCode

Thanks,

Gordon

[former_member206437]

Hi

Check this Query:

SELECT OCRD.CardCode, OCRD.CardName, SUM(INV1.Price * INV1.Quantity) AS turnover, OITB.ItmsGrpNam, OCRG.GroupName, OITM.ItemCode, OITM.ItemName
FROM  INV1 INNER JOIN OITM ON INV1.ItemCode = OITM.ItemCode INNER JOIN OITB ON OITB.ItmsGrpCod = OITM.ItmsGrpCod INNER JOIN OINV ON OINV.DocEntry = INV1.DocEntry INNER JOIN OCRD ON OINV.CardCode = OCRD.CardCode INNER JOIN OCRG ON OCRD.GroupCode = OCRG.GroupCode
GROUP BY OCRD.CardCode, OCRD.CardName, OITB.ItmsGrpNam, OCRG.GroupName, OITM.ItemCode, OITM.ItemName

Giri