OVTG table in SAP Business One (Tax Definitions (VAT groups))

OVTG key fields

How OVTG relates to other tables

OVTG is master data. Document lines store the tax definition in VatGroup (INV1.VatGroup and the other line tables), journal entry lines store it in JDT1.VatGroup, and OCRD.ECVatGroup holds a default tax group of the business partner. OVTG.Account points to the tax account in OACT.

Common joins

• INV1 T0 INNER JOIN OVTG T1 ON T1.Code = T0.VatGroup (tax group of the invoice line)
• JDT1 T2 INNER JOIN OVTG T1 ON T1.Code = T2.VatGroup (tax group of the journal line)
• OVTG T1 INNER JOIN OACT T3 ON T3.AcctCode = T1.Account (tax account)

When you use the OVTG table

Use OVTG to total sales and tax per VAT code before a return, to check that the right rate is used on invoice lines, and to list output and input codes with their accounts.

Example query: invoice lines and tax per vat group this month

SQL Server

SELECT T1.Code, T1.Name, T1.Rate, T1.Category, SUM(T0.LineTotal) AS NetAmount, SUM(T0.VatSum) AS TaxAmount
FROM INV1 T0
INNER JOIN OINV T2 ON T2.DocEntry = T0.DocEntry
INNER JOIN OVTG T1 ON T1.Code = T0.VatGroup
WHERE T2.CANCELED = 'N' AND T2.DocDate >= DATEADD(day, 1 - DAY(GETDATE()), CAST(GETDATE() AS date))
GROUP BY T1.Code, T1.Name, T1.Rate, T1.Category
ORDER BY T1.Code

SAP HANA (column names in double quotes)

SELECT T1."Code", T1."Name", T1."Rate", T1."Category", SUM(T0."LineTotal") AS "NetAmount", SUM(T0."VatSum") AS "TaxAmount"
FROM INV1 T0
INNER JOIN OINV T2 ON T2."DocEntry" = T0."DocEntry"
INNER JOIN OVTG T1 ON T1."Code" = T0."VatGroup"
WHERE T2."CANCELED" = 'N' AND T2."DocDate" >= ADD_DAYS(CAST(CURRENT_TIMESTAMP AS date), 1 - DAYOFMONTH(CURRENT_TIMESTAMP))
GROUP BY T1."Code", T1."Name", T1."Rate", T1."Category"
ORDER BY T1."Code"

Paste either version in Tools → Queries → Query Generator. Need the other dialect for your own query? Use the SQL to HANA converter.

Common pitfall: Using today's OVTG.Rate for old documents

OVTG.Rate is the current rate of the code, valid from EffecDate. After a rate change, old invoices keep the rate they were posted with. For historic reports read the tax amounts stored on the document lines (VatSum) instead of recalculating them from OVTG.Rate. See JDT1 table (journal entry lines).

Ready-made queries that use OVTG

• G/L account transactions: Journal lines for one G/L account in a period, with debit, credit and reference.

Questions about the OVTG table

What is the difference between OVTG and OSTC?

OVTG holds tax definitions (VAT groups) and OSTC holds sales tax codes. Lines carry VatGroup for OVTG and TaxCode for OSTC; which one your company uses depends on its localization.

How do I tell output VAT from input VAT in OVTG?

Category O is output tax and I is input tax.

Which account does a VAT group post to?

OVTG.Account, which joins to OACT.AcctCode.

Related tables

JDT1 (Journal Entry Lines) · INV1 (A/R Invoice (lines)) · OSTC (Sales Tax Codes) · OACT (G/L Accounts (chart of accounts)) · search all SAP Business One tables

Sources: SAP Business One documentation and SDK (DI API database tables reference), SAP Help Portal.