Prof. Dr. Nick Gehrke

written by

Prof. Dr. Nick Gehrke

Nerdy scholastic young woman wearing geeky glasses standing thinking with her finger raised and a grimace of concentration in a humorous stereotypical depiction, over a dark background with copyspace

Those who are too lazy to claim back input tax leave their money sitting with the tax authorities. But who would actually do such a thing – you may be asking yourself? Well, unfortunately it is something that we see happening again and again. Because sometimes input tax can be complicated to retrieve. Especially if you have to go through a foreign tax office to do so. In this article, I will show you how to track down forgotten input tax in SAP.

Where is the input tax?

Most companies are entitled to have their input tax refunded by the tax office (entitlement to deduction of input tax). Normally, this poses no difficulties and there are normal procedures to follow for processing such refunds. Things can however become more problematic with dealing invoices with foreign partners. Many services with a foreign connection between companies are billed without VAT from the outset (issued tax-free), e.g. as part of intra-Community deliveries or by reversing the tax liability (reverse charge). In these cases, there is no input tax to be claimed back as a result and the matter is thus quite straightforward.

But there are also cases in which foreign sales tax appears on the invoice. The problem is that the domestic tax authorities (with which you are familiar) do not refund foreign input tax. You then have to contact the foreign tax office. And you may not be very familiar with how to go about this if you only have to deal with such issues in rare or sporadic cases.

Let’s take an example: An employee of a German company goes on a business trip lasting several days in Norway. Norwegian VAT is shown and paid on the hotel bill. However, the German company has no other sales in Norway and therefore no relationship with the Norwegian tax authorities.

Of course, the German company can recover the input tax from the Norwegian tax office. The problem is how to go about it. The German company has to dispute the input tax refund procedure with Norway. Since this seems complicated from a procedural point of view, the hotel bill is simply booked gross in the expenditure and the input tax is thus ignored.

If you do this too often however, perhaps even in different countries where necessary, the amounts could soon start to build up.

 

How to track down forgotten input tax

As an auditor or accounting employee, you may well be asking yourself whether and how often such cases occur in your company? Or also with which expenditures this could be the case? So let’s now look at putting together an approach that will enable us to get this information out of the SAP database with the help of SQL.

How could you formulate conditions for a relatively simple query? Here is what I suggest. The documents that are relevant in providing an answer to our question are as follows:

  • On the credit side, documents which have been posted in credit only (invoices)
  • On the debit side, documents which have been posted to only one account on the income statement (cases with several income statement accounts in one posting are therefore ignored).

 

If you delimit the posting data in this way, you can then check whether there are P&L accounts for which there are postings with a tax line and other postings without a tax line. These are then expense accounts that are "suspicious" in the sense that they may present a risk of "forgotten" input tax. The premise here is that an expense account conceals services that are either always with tax or always without tax. Mixed cases are therefore suspicious.

So much for the background. From this point on, you should however proceed with caution, because even for the die-hard analysts among you, this is where things can start to get a little creepy. We are not going to go into anymore detail here as to exactly why though, as this would go well beyond the scope of the current article.

So, now that you’ve been warned, here’s a SQL query that determines all the relevant documents. You can try this out yourself by calling up the "DBACOCKPIT" transaction in SAP and navigating to the "SQL Editor" using the "Diagnostics" function.

 

SELECT BSEG.MANDT, BSEG.BUKRS, BSEG.GJAHR, BSEG.BELNR, COUNT(DISTINCT CASE WHEN BSEG.SHKZG = 'S' AND BSEG.XBILK = '' THEN BSEG.HKONT ELSE null END) EXPENSE_ACCOUNTS, STRING_AGG(CASE WHEN BSEG.SHKZG = 'S' AND BSEG.XBILK = '' THEN SKAT.TXT50 ELSE null END, ',') EXPENSE_ACCOUNT_TXT, STRING_AGG(CASE WHEN BSEG.SHKZG = 'S' AND BSEG.XBILK = '' THEN HKONT ELSE null END, ',') EXPENSE_ACCOUNT, COUNT(DISTINCT CASE WHEN BSEG.SHKZG = 'S' AND BSEG.MWART != '' THEN 1 ELSE null END) WITH_INPUT_TAX
FROM BSEG
LEFT JOIN T001 ON (
T001.MANDT = BSEG.MANDT AND T001.BUKRS = BSEG.BUKRS)
LEFT JOIN SKAT ON (SKAT.MANDT = BSEG.MANDT AND SKAT.KTOPL = T001.KTOPL AND SKAT.SAKNR = BSEG.HKONT AND SKAT.SPRAS = 'D')
WHERE BSEG.MANDT = '800' AND BSEG.BUKRS = '1000' AND BSEG.GJAHR = 1997 AND NOT EXISTS (
SELECT B1.BUZEI
FROM BSEG B1
WHERE B1.MANDT = BSEG.MANDT AND B1.BUKRS = BSEG.BUKRS AND B1.GJAHR = BSEG.GJAHR AND B1.BELNR = BSEG.BELNR AND B1.SHKZG = 'H' AND KOART != 'K') AND NOT EXISTS (
SELECT B1.BUZEI
FROM BSEG B1
WHERE B1.MANDT = BSEG.MANDT AND B1.BUKRS = BSEG.BUKRS AND B1.GJAHR = BSEG.GJAHR AND B1.BELNR = BSEG.BELNR AND B1.SHKZG = 'S' AND B1.XBILK = 'X' AND B1.MWART = '')
GROUP BY BSEG.MANDT, BSEG.BUKRS, BSEG.GJAHR, BSEG.BELNR HAVING COUNT(DISTINCT CASE WHEN BSEG.SHKZG = 'S' AND BSEG.XBILK = '' THEN BSEG.HKONT ELSE null END) = 1 ORDER BY EXPENSE_ACCOUNT

 

An excerpt from my test dataset gives the following result:

 

BELNR

EXPENSE_
ACCOUNT_TXT

EXPENSE_
ACCOUNT

WITH_
INPUT_TAX

1900002270

Spare Parts

404000

1

1900002264

Spare Parts

404000

0

1900003236

Consumption: Packaging
Material

405000

1

1900002351

Consumption: Packaging
Material

405000

1

1900002249

Consumption: Packaging
Material

410000

1

1900002182

Consumption: Trading Goods

410000

1

1900002273

Consumption: Trading Goods

410000

1

 

Here you can see individual documents that have each been posted to three different P&L accounts (spare parts, consumption: packaging material, consumption: trading goods).

In the "WITH_INPUT_TAX" column, you can see that the first posting was posted with a tax line, but the second posting was not, although it is the same P&L account. This goes against what we would normally expect and should therefore be treated as suspicious. You should definitely take a closer look!

At the top, only an excerpt is shown. Of course, a lot more documents will probably come up as a result of the query. Therefore, we now need to summarize the results by all P&L accounts used and then only display the P&L accounts where such mixed cases occur (some with a tax line and some without a tax line):

 

SELECT EXPENSE_ACCOUNT, EXPENSE_ACCOUNT_TXT, COUNT(DISTINCT WITH_INPUT_TAX)
FROM (
SELECT BSEG.MANDT, BSEG.BUKRS, BSEG.GJAHR, BSEG.BELNR, COUNT(DISTINCT CASE WHEN BSEG.SHKZG = 'S' AND BSEG.XBILK = '' THEN BSEG.HKONT ELSE null END) EXPENSE_ACCOUNTS, STRING_AGG(CASE WHEN BSEG.SHKZG = 'S' AND BSEG.XBILK = '' THEN SKAT.TXT50 ELSE null END, ',') EXPENSE_ACCOUNT_TXT, STRING_AGG(CASE WHEN BSEG.SHKZG = 'S' AND BSEG.XBILK = '' THEN HKONT ELSE null END, ',') EXPENSE_ACCOUNT, COUNT(DISTINCT CASE WHEN BSEG.SHKZG = 'S' AND BSEG.MWART != '' THEN 1 ELSE null END) WITH_INPUT_TAX
FROM BSEG
LEFT JOIN T001 ON (
T001.MANDT = BSEG.MANDT AND T001.BUKRS = BSEG.BUKRS) LEFT JOIN SKAT ON (
SKAT.MANDT = BSEG.MANDT AND SKAT.KTOPL = T001.KTOPL AND SKAT.SAKNR = BSEG.HKONT AND SKAT.SPRAS = 'D')
WHERE BSEG.MANDT = '800' AND BSEG.BUKRS = '1000' AND BSEG.GJAHR = 1997 AND NOT EXISTS (
SELECT B1.BUZEI FROM BSEG B1 WHERE B1.MANDT = BSEG.MANDT AND B1.BUKRS = BSEG.BUKRS AND B1.GJAHR = BSEG.GJAHR AND B1.BELNR = BSEG.BELNR AND B1.SHKZG = 'H' AND KOART != 'K') AND NOT EXISTS (
SELECT B1.BUZEI
FROM BSEG B1
WHERE B1.MANDT = BSEG.MANDT AND B1.BUKRS = BSEG.BUKRS AND B1.GJAHR = BSEG.GJAHR AND B1.BELNR = BSEG.BELNR AND B1.SHKZG = 'S' AND B1.XBILK = 'X' AND B1.MWART = '') GROUP BY BSEG.MANDT, BSEG.BUKRS, BSEG.GJAHR, BSEG.BELNR
HAVING COUNT(DISTINCT CASE WHEN BSEG.SHKZG = 'S' AND BSEG.XBILK = '' THEN BSEG.HKONT ELSE null END) = 1
ORDER BY EXPENSE_ACCOUNT)
GROUP BY EXPENSE_ACCOUNT, EXPENSE_ACCOUNT_TXT
HAVING COUNT(DISTINCT WITH_INPUT_TAX)>1

 

The query gives the following result on the test data set:

 

EXPENSE_
ACCOUNT

EXPENSE_
ACCOUNT_TXT

COUNT(DISTINCT WITH_INPUT_TAX)

404000

Spare Parts

2

 

The result is logical, since the P&L account "spare parts" has already been identified above as a suspicious case. Further suspicious P&L accounts were therefore not found.

 

Conclusion

For sure, it is never easy to track down all cases of "forgotten" input tax. And the approach presented here will not find all cases either. But thanks to the relatively simple logic employed, it is at least a good starting point.

Topics: SAP Audit, Tax
zapliance Newsletter label

Sign up to receive
our latest news and special offers direct to your inbox!

zapliance customer brands

Our recommendations to you

Comments