SAP Business One - Help with Query on Invoice table OINV/INV1

[former_member391531]

SAP B1 9.3 PL5

I have written a query to give me a list of Invoices for a certain vat code & certain currency. At the moment the results are per line.

I'd like to be able to run it with 1 line per Invoice. (So it totals the individual invoice lines together)

Screenshot attached to show by example.

Would anyone know how to do this? Many Thanks.

Query:

SELECT T0.[DocNum], T0.[DocDate], T1.[LineTotal], T1.[VatGroup], T1.[Currency] FROM OINV T0 INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry] WHERE T1.[VatGroup] ='O1' AND T1.[Currency] ='EUR'

screenshot.jpg

[Johan_H · Accepted solution]

Hi,

SELECT T0.[DocNum], T0.[DocDate], SUM(T1.[LineTotal]) AS [Total w/o VAT], T1.[VatGroup], T1.[Currency]
FROM OINV T0 INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry]
WHERE T1.[VatGroup] ='O1' AND T1.[Currency] ='EUR'
GROUP BY T0.[DocNum], T0.[DocDate], T1.[VatGroup], T1.[Currency]

Regards,

Johan

[former_member391531 · Accepted solution]

Thank you for the reply. Much appreciated.

[MD1]

SELECT T0."DocNum", T0."DocDate", T0."DocTotal", MAX(T1."VatGroup") AS "Tax", MAX(T1."Currency") AS "PriceCurrency"
FROM OINV T0
INNER JOIN INV1 T1 ON T0."DocEntry" = T1."DocEntry"
WHERE T1."VatGroup" = 'O1' AND T1."Currency" = 'EUR'
GROUP BY T0."DocNum",T1."VatGroup", T0."DocDate", T0."DocTotal"

[former_member391531]

Thank you for the reply. Much appreciated.

[msundararaja_perumal]

Hello,

You need to use aggregate function and grouping.

SELECT T0.[DocNum], T0.[DocDate], T0.DocTotal, MAX(T1.[VatGroup]) AS Tax, Max(T1.[Currency]) AS PriceCurrency FROM OINV T0 INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry]  WHERE T1.[VatGroup] ='O1' AND T1.[Currency] ='SGD' Group By T0.[DocNum], T0.[DocDate], T0.DocTotal

Thanks.

[former_member391531]

Thank you for the reply. Much appreciated.