Tracing flows of cash in SAP: Where did your money go?

Nick Gehrke
February 8, 2018

In accounting, it is normal to always pay special attention to what is going on with your own bank accounts. This is where the cash flows in or out – depending on the circumstances. In today’s blog post, though, I would like to show you how to use the data structures in SAP to find out where your money has gone. This means we will concentrate on cash outflows and look at the corresponding offsetting accounts. To try it out for yourself, there some suitable queries you can execute in SQL. You’ll need to be determined though, because this time things will be get very SQL-heavy, something for real freaks of data analysis among you auditors out there. Welcome to the Audit Data Scientist’s guide to SQL DIY hacking guide.

Where do I find the bank accounts in Accounting?

First of all, you have to find out which of the Accounting accounts represent your own bank accounts. This information can be found for each company code and financial accounting account in the account master data in table “SKB1” in the field “XGKON”. If you want to try the following queries yourself, use the transaction “DBACOCKPIT – Diagnostics – SQL Editor” in your SAP System. If we assume that you are examining SAP client 800 and company code 1000, the query for finding the financial accounting bank accounts is as follows:

Query 1 (tested on a Hana):

SELECT * FROM SKB1 WHERE MANDT='800' AND BUKRS='1000' AND XGKON='X'

Okay, so far, so good. A simple selection (SELECT) applied to an SAP table (SKB1) subject to the conditions of a given client (800), a company code (1000) and the indicator for incoming / outgoing payment accounts (X for applicable).

Example of result:

Finding all cash outflows

All outflows of funds can be found by looking at all the transaction lines in the Accounting bank accounts that are in credit. These are the corresponding reductions in assets on the bank account. Now it starts to get more complicated, but don’t panic, I’ll explain everything step-by-step before we put the query together:

Query 2 (tested on a Hana):

First of all, we have to think about which fields are necessary for an evaluation. This includes a few simple details that are needed for a basic overview:

Client (MANDT)
Company code (BUKRS)
Fiscal year (GJAHR)
Document number (BELNR)
Posting line item (BUZEI)
Debit/credit indicator (SHKZG)

To assign the accounts and the corresponding values of the cash outflows, we also require the amount in local currency (DMBTR) and the corresponding account (HKONT), including the account name (TXT50) of the Accounting account. If we put the individual modules of the query together and look at the respective table from which the information can be obtained, we arrive at the following selection (SELECT):

SELECT BSEG.MANDT, BSEG.BUKRS, BSEG.GJAHR, BSEG.BELNR, BSEG.BUZEI, BSEG.HKONT ACCOUNT, SKAT1.TXT50 ACCOUNTTXT, BSEG.SHKZG, BSEG.DMBTR FROM BSEG

To link tables together in SQL (join), we need so-called table joins on all the tables from which we want to retrieve information. As mentioned at the beginning of this article, we have to take the marking “XGKON” (incoming / outgoing money account) into account, which means that we have to join up with the table “SKB1”. Unfortunately, we cannot find this information in the “BSEG” table:

JOIN SKB1 ON (BSEG.MANDT=SKB1.MANDT AND BSEG.BUKRS=SKB1.BUKRS AND BSEG.HKONT=SKB1.SAKNR AND XGKON='X')

To ensure that reversal documents (STBLG) are also excluded from the analysis, a further join to the table of document headers (BKPF) and an exclusion of reversal documents is necessary:

JOIN BKPF ON (BSEG.MANDT=BKPF.MANDT AND BSEG.BUKRS=BKPF.BUKRS AND BSEG.GJAHR=BKPF.GJAHR AND BSEG.BELNR=BKPF.BELNR AND BKPF.STBLG='')

The chart of accounts is required to obtain the name of the Accounting account. Without the chart of accounts, a join to the SKAT table would not be possible, so that the following join is a necessary evil:

LEFT JOIN T001 ON (BSEG.MANDT=T001.MANDT AND BSEG.BUKRS=T001.BUKRS)

SAP saves the name of the G/L account in another table (SKAT), so that a join to the SKAT table is necessary too. This also allows us to specify the language of the account name:

LEFT JOIN SKAT SKAT1 ON (BSEG.MANDT=SKAT1.MANDT AND BSEG.HKONT=SKAT1.SAKNR AND T001.KTOPL=SKAT1.KTOPL AND SKAT1.SPRAS='E')

We also want to limit the subject of the investigation to one client, one company code and one fiscal year. In addition, the asset reductions are in credit on the bank account. We must therefore also apply the following restriction:

WHERE BSEG.SHKZG='H' AND BSEG.MANDT='800' AND BSEG.BUKRS='1000' AND BSEG.GJAHR=2017

To make the data displayed more clearly legible, we use the “ORDER BY” command, which sorts the following fields in ascending order by default:

ORDER BY BSEG.MANDT, BSEG.BUKRS, BSEG.GJAHR, BSEG.BELNR, BSEG.BUZEI, BSEG.SHKZG

If we sum up what we have established so far and put all the individual parts of the queries together, we obtain the following query:

SELECT BSEG.MANDT, BSEG.BUKRS, BSEG.GJAHR, BSEG.BELNR, BSEG.BUZEI,BSEG.HKONT ACCOUNT, SKAT1.TXT50 ACCOUNTTXT, BSEG.SHKZG, BSEG.DMBTR FROM BSEG
JOIN SKB1 ON (BSEG.MANDT=SKB1.MANDT AND BSEG.BUKRS=SKB1.BUKRS AND BSEG.HKONT=SKB1.SAKNR AND XGKON='X');
JOIN BKPF ON (BSEG.MANDT=BKPF.MANDT AND BSEG.BUKRS=BKPF.BUKRS AND BSEG.GJAHR=BKPF.GJAHR AND BSEG.BELNR=BKPF.BELNR AND BKPF.STBLG='')
LEFT JOIN T001 ON (BSEG.MANDT=T001.MANDT AND BSEG.BUKRS=T001.BUKRS)
LEFT JOIN SKAT SKAT1 ON (BSEG.MANDT=SKAT1.MANDT AND BSEG.HKONT=SKAT1.SAKNR AND T001.KTOPL=SKAT1.KTOPL AND SKAT1.SPRAS='E')
WHERE BSEG.SHKZG='H' AND BSEG.MANDT='800' AND BSEG.BUKRS='1000' AND BSEG.GJAHR=2017
ORDER BY BSEG.MANDT, BSEG.BUKRS, BSEG.GJAHR, BSEG.BELNR, BSEG.BUZEI, BSEG.SHKZG

Remember: it was a german SAP system. H means credit and S means debit.

But that’s not all!

For what purpose was the money paid out?

To find out what the money was used for, we need the offsetting accounts and their amounts for the document lines from query 2, which must correspond exactly to the amount of the cash outflow.

Query 3 (tested on a HANA):

The selection should be familiar to you by now. The difference with this “SELECT” statement is the sum of the amounts in local currency (SUM (DMBTR)) that represent a document balance and the proportion of the outgoing amount of money for the posting line item in the document balance (amount in local currency / proportion of the posting line item in the amount). For a more attractive presentation and to ensure the query works correctly, the result must also be converted (casted). In addition, the offsetting account for the cash outflow is listed (“CONTRAACOUNT”), along with the amount that can be assigned to the offsetting account for the cash outflow:

Repeat the exclusion of reversal documents:

JOIN BKPF ON (BSEG.MANDT=BKPF.MANDT AND BSEG.BUKRS=BKPF.BUKRS AND BSEG.GJAHR=BKPF.GJAHR AND BSEG.BELNR=BKPF.BELNR AND BKPF.STBLG='')

Only select posting items that represent cash outflows (XGKON):

JOIN SKB1 ON (BSEG.MANDT=SKB1.MANDT AND BSEG.BUKRS=SKB1.BUKRS AND BSEG.HKONT=SKB1.SAKNR AND XGKON='X')

Get the necessary offsetting items for the cash outflows:

JOIN BSEG B2 ON (BSEG.MANDT=B2.MANDT AND BSEG.BUKRS=B2.BUKRS AND BSEG.GJAHR=B2.GJAHR AND BSEG.BELNR=B2.BELNR AND BSEG.SHKZG!=B2.SHKZG)

Create the chart of accounts for the table join to the SAP SKAT table to access the account name:

LEFT JOIN T001 ON (BSEG.MANDT=T001.MANDT AND BSEG.BUKRS=T001.BUKRS)

Set the account name of the account and offsetting account, as well as the language of the account name to English (‘E’):

LEFT JOIN SKAT SKAT1 ON (BSEG.MANDT=SKAT1.MANDT AND BSEG.HKONT=SKAT1.SAKNR AND T001.KTOPL=SKAT1.KTOPL AND SKAT1.SPRAS='E')
LEFT JOIN SKAT SKAT2 ON (B2.MANDT=SKAT2.MANDT AND B2.HKONT=SKAT2.SAKNR AND T001.KTOPL=SKAT2.KTOPL AND SKAT2.SPRAS='E')

On the condition that only cash outflows are selected (debit/credit indicator in “credit”), client 800, company code 1000 and the fiscal year corresponds to the previous year 2017:

WHERE BSEG.SHKZG='H' AND BSEG.MANDT='800' AND BSEG.BUKRS='1000' AND BSEG.GJAHR=2017

Sorted in ascending order according to client, company code, fiscal year, document number and posting line item:

ORDER BY BSEG.MANDT, BSEG.BUKRS, BSEG.GJAHR, BSEG.BELNR, BSEG.BUZEI

Putting everything together, the query then looks like this:

SELECT BSEG.MANDT, BSEG.BUKRS, BSEG.GJAHR, BSEG.BELNR, BSEG.BUZEI, BSEG.HKONT ACCOUNT, SKAT1.TXT50 ACCOUNTTXT, BSEG.SHKZG, BSEG.DMBTR, (SELECT SUM(B.DMBTR) FROM BSEG B WHERE B.MANDT=BSEG.MANDT AND B.BUKRS=BSEG.BUKRS AND B.GJAHR=BSEG.GJAHR AND B.BELNR=BSEG.BELNR AND B.SHKZG=BSEG.SHKZG) BELEGSALDO, CAST(BSEG.DMBTR/(SELECT SUM(B.DMBTR) FROM BSEG B WHERE B.MANDT=BSEG.MANDT AND B.BUKRS=BSEG.BUKRS AND B.GJAHR=BSEG.GJAHR AND B.BELNR=BSEG.BELNR AND B.SHKZG=BSEG.SHKZG) AS DECIMAL) PORTION,
B2.HKONT CONTRAACCOUNT, SKAT2.TXT50 CONTRAACCOUNTTXT, CAST(B2.DMBTR*BSEG.DMBTR/(SELECT SUM(B.DMBTR) FROM BSEG B WHERE B.MANDT=BSEG.MANDT AND B.BUKRS=BSEG.BUKRS AND B.GJAHR=BSEG.GJAHR AND B.BELNR=BSEG.BELNR AND B.SHKZG=BSEG.SHKZG) AS DECIMAL) CONTRAAMOUNT,B2.SHKZG CONTRASHKZG
FROM BSEG
JOIN BKPF ON (BSEG.MANDT=BKPF.MANDT AND BSEG.BUKRS=BKPF.BUKRS AND BSEG.GJAHR=BKPF.GJAHR AND BSEG.BELNR=BKPF.BELNR AND BKPF.STBLG='')
JOIN SKB1 ON (BSEG.MANDT=SKB1.MANDT AND BSEG.BUKRS=SKB1.BUKRS AND BSEG.HKONT=SKB1.SAKNR AND XGKON='X')
JOIN BSEG B2 ON (BSEG.MANDT=B2.MANDT AND BSEG.BUKRS=B2.BUKRS AND BSEG.GJAHR=B2.GJAHR AND BSEG.BELNR=B2.BELNR AND BSEG.SHKZG!=B2.SHKZG)
LEFT JOIN T001 ON (BSEG.MANDT=T001.MANDT AND BSEG.BUKRS=T001.BUKRS)
LEFT JOIN SKAT SKAT1 ON (BSEG.MANDT=SKAT1.MANDT AND BSEG.HKONT=SKAT1.SAKNR AND T001.KTOPL=SKAT1.KTOPL AND SKAT1.SPRAS='E')
LEFT JOIN SKAT SKAT2 ON (B2.MANDT=SKAT2.MANDT AND B2.HKONT=SKAT2.SAKNR AND T001.KTOPL=SKAT2.KTOPL AND SKAT2.SPRAS='E')
WHERE BSEG.SHKZG='H' AND BSEG.MANDT='800' AND BSEG.BUKRS='1000' AND BSEG.GJAHR=2017
ORDER BY BSEG.MANDT, BSEG.BUKRS, BSEG.GJAHR, BSEG.BELNR, BSEG.BUZEI

But, wait, that’s still not yet all – it’ gets EVEN worse!

Cash check

With Query 2 and Query 3, we now have the cash outflows and the offsetting accounts with the corresponding amounts. Now all we need to do is to offset all documents on both sides against each other, sort through them once and we can then see where our money has gone, for the entire fiscal year.

Final Query (tested on a HANA):

Basically, we are now simply going to put the two queries together, albeit making sure that we select the debit/credit indicator, as well as the financial accounting account including the name and the sum of the amount in local currency. The query then looks like this:

SELECT * FROM (
SELECT SHKZG, ACCOUNT, ACCOUNTTXT, SUM(DMBTR) AMOUNT FROM (
SELECT BSEG.MANDT,BSEG.BUKRS,BSEG.GJAHR,BSEG.BELNR,BSEG.BUZEI,BSEG.HKONT ACCOUNT,SKAT1.TXT50 ACCOUNTTXT,BSEG.SHKZG,BSEG.DMBTR FROM BSEG JOIN SKB1 ON (BSEG.MANDT=SKB1.MANDT AND BSEG.BUKRS=SKB1.BUKRS AND BSEG.HKONT=SKB1.SAKNR AND XGKON='X') JOIN BKPF ON (BSEG.MANDT=BKPF.MANDT AND BSEG.BUKRS=BKPF.BUKRS AND BSEG.GJAHR=BKPF.GJAHR AND BSEG.BELNR=BKPF.BELNR AND BKPF.STBLG='') LEFT JOIN T001 ON (BSEG.MANDT=T001.MANDT AND BSEG.BUKRS=T001.BUKRS) LEFT JOIN SKAT SKAT1 ON (BSEG.MANDT=SKAT1.MANDT AND BSEG.HKONT=SKAT1.SAKNR AND T001.KTOPL=SKAT1.KTOPL AND SKAT1.SPRAS='E') WHERE BSEG.SHKZG='H' AND BSEG.MANDT='800' AND BSEG.BUKRS='1000' AND BSEG.GJAHR=2017 ORDER BY BSEG.MANDT,BSEG.BUKRS,BSEG.GJAHR,BSEG.BELNR,BSEG.BUZEI,BSEG.SHKZG) GROUP BY ACCOUNT,ACCOUNTTXT, SHKZG
UNION
SELECT CONTRASHKZG, CONTRAACCOUNT, CONTRAACCOUNTTXT, SUM(CONTRAAMOUNT) FROM (
SELECT BSEG.MANDT,BSEG.BUKRS,BSEG.GJAHR,BSEG.BELNR,BSEG.BUZEI,BSEG.HKONT ACCOUNT,SKAT1.TXT50 ACCOUNTTXT ,BSEG.SHKZG ,BSEG.DMBTR ,(SELECT SUM(B.DMBTR) FROM BSEG B WHERE B.MANDT=BSEG.MANDT AND B.BUKRS=BSEG.BUKRS AND B.GJAHR=BSEG.GJAHR AND B.BELNR=BSEG.BELNR AND B.SHKZG=BSEG.SHKZG ) BELEGSALDO, CAST(BSEG.DMBTR/(SELECT SUM(B.DMBTR) FROM BSEG B WHERE B.MANDT=BSEG.MANDT AND B.BUKRS=BSEG.BUKRS AND B.GJAHR=BSEG.GJAHR AND B.BELNR=BSEG.BELNR AND B.SHKZG=BSEG.SHKZG ) AS DECIMAL) PORTION,
B2.HKONT CONTRAACCOUNT, SKAT2.TXT50 CONTRAACCOUNTTXT , CAST(B2.DMBTR*BSEG.DMBTR/(SELECT SUM(B.DMBTR) FROM BSEG B WHERE B.MANDT=BSEG.MANDT AND B.BUKRS=BSEG.BUKRS AND B.GJAHR=BSEG.GJAHR AND B.BELNR=BSEG.BELNR AND B.SHKZG=BSEG.SHKZG ) AS DECIMAL) CONTRAAMOUNT ,B2.SHKZG CONTRASHKZG
FROM BSEG
JOIN BKPF ON (BSEG.MANDT=BKPF.MANDT AND BSEG.BUKRS=BKPF.BUKRS AND BSEG.GJAHR=BKPF.GJAHR AND BSEG.BELNR=BKPF.BELNR AND BKPF.STBLG='')
JOIN SKB1 ON (BSEG.MANDT=SKB1.MANDT AND BSEG.BUKRS=SKB1.BUKRS AND BSEG.HKONT=SKB1.SAKNR AND XGKON='X')
JOIN BSEG B2 ON (BSEG.MANDT=B2.MANDT AND BSEG.BUKRS=B2.BUKRS AND BSEG.GJAHR=B2.GJAHR AND BSEG.BELNR=B2.BELNR AND BSEG.SHKZG!=B2.SHKZG)
LEFT JOIN T001 ON (BSEG.MANDT=T001.MANDT AND BSEG.BUKRS=T001.BUKRS)
LEFT JOIN SKAT SKAT1 ON (BSEG.MANDT=SKAT1.MANDT AND BSEG.HKONT=SKAT1.SAKNR AND T001.KTOPL=SKAT1.KTOPL AND SKAT1.SPRAS='E')
LEFT JOIN SKAT SKAT2 ON (B2.MANDT=SKAT2.MANDT AND B2.HKONT=SKAT2.SAKNR AND T001.KTOPL=SKAT2.KTOPL AND SKAT2.SPRAS='E')
WHERE BSEG.SHKZG='H' AND BSEG.MANDT='800' AND BSEG.BUKRS='1000' AND BSEG.GJAHR=2017
ORDER BY BSEG.MANDT, BSEG.BUKRS, BSEG.GJAHR, BSEG.BELNR, BSEG.BUZEI)
GROUP BY CONTRAACCOUNT, CONTRAACCOUNTTXT, CONTRASHKZG)
ORDER BY SHKZG DESC, AMOUNT DESC

Okay, that’s pretty intense, but, with just one SQL query, we’ve done an analysis of offsetting accounts for withdrawals from all bank accounts!

When you try out the query, remember to adjust the “WHERE” conditions to match your clients (MANDT), your company code (BUKRS) and the year you wish to analyze (GYAHR). You can also adjust the language of the accounts to be output. To do this, change SPRAS=’E’ (English, ‘D’ for German) to your desired language abbreviation.

On my small test data set, the results that came up are as follows:

The debit side contains all the offsetting items for the Accounting bank accounts, while the credit side contains the Accounting bank accounts themselves (= outflow of funds). The following also applies here: Total debit must correspond to the total credit. Fortunately, of course, everything adds up here… or at least pretty much!

You should now review the offsetting items in the debit side and check whether it is plausible that such an offsetting account has an outflow of funds. Here, in our example, it is possible to note following:

The debit side also contains some Accounting bank accounts. This means that there have been major transfers between the companies’ bank accounts.
The liability accounts on the debit side are very plausible, since it is part of the usual business process to pay your debts.
Cash discount income in debit is rather peculiar, since one would normally expect income to be in credit.
The posting of purchased services directly in respect of a bank should be considered to be rather unusual, since it would actually be expected that an invoice and thus a liability would exist, which would then be settled against an outflow of funds.
Consumption of raw materials is strange compared to cash outflows. Some further research needs to be done here.

What can I do if I am not an SQL expert?

Not everyone is an expert in SQL or has the option of evaluating his or her SAP system.

zap Audit can help you track down rare or strange account assignments in accounting, by using our indicators, which allow you, for example, to detect:

Documents with the same amount on the debit and credit sides of an account
Documents with account assignment on unexpected account side
Postings with items on bank and expense accounts

If you would like to find out more about the wide range of indicators that zap Audit offers, please visit our ePaper Download section.

Nick Gehrke

is Chief of Data & Knowledge and Co-Founder at zapliance as well as Professor of Information Systems with a Big 4 background. He prefers to work as a business information scientist and tax consultant at the converging points of finance, accounting, taxation, audit and ERP systems, data science and information technology.

ALLE BEITRÄGE