Skip to Content

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

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

screenshot.jpg (99.0 kB)
Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Best Answer
    Posted on Nov 07, 2019 at 07:05 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Nov 07, 2019 at 04:13 AM

    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"

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Nov 07, 2019 at 01:06 AM

    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.

    Add a comment
    10|10000 characters needed characters exceeded