Disbursements in SAP should follow strictly defined processes in accounting. A good internal control system can only be based on a well-defined process. Since outgoing payments have a direct effect on the assets of every company, every auditor – and of course every accounting manager too – should have a major interest in ensuring a good payment process. In this blog post, I will show you how to find payments that have been made “by roundabout means” in the data of your SAP system.
In the three steps outlined below, I would like to show you how you can find payment transactions in your SAP data that do not correspond to the standard process. To do this, you must first determine all disbursements (step 1). The system then determines which process steps or transactions were used in SAP to make the payment (step 2). Finally, all payments are aggregated according to the SAP transaction used so that you can see how often and in which way they were paid.
Step 1: Determine disbursements
To determine all disbursements, you must first determine which Accounting accounts represent your bank accounts. In my test data set, the following two accounts are bank accounts
- 0000113100
- 0000113101
with Deutsche Bank.
Before proceeding with the analysis, we also determine which company (company code) and which fiscal year are to be examined. You must also know the SAP client to be analyzed. For testing purposes, we have chosen the company as follows:
- SAP client (MANDT): 800
- Company code (BUKRS): 1000
- Fiscal year (YEAR): 1997
We can now determine all disbursements by using all postings that have been posted to accounts 0000113100 or 0000113101 and where the sum of the amount items in credit is greater than those in debit, because bank postings in credit are outflows of money.
The following SQL query selects all these cases. If you want to test the query yourself on your own SAP system, use the “DBACOCKPIT” transaction in SAP and navigate to the “SQL Editor” via “Diagnostics”.
| Part of query | Description |
|---|---|
SELECT BSEG.MANDT, BSEG.BUKRS, BSEG.GJAHR, BSEG.BELNR, | Selection of relevant fields |
SUM(CASE WHEN SHKZG='S' THEN DMBTR ELSE 0 END) CASH_IN, | Total of bank posting lines in debit = cash inflows |
SUM(CASE WHEN SHKZG='H' THEN DMBTR ELSE 0 END ) CASH_OUT, | Total of bank posting lines in credit = cash outflows |
SUM((CASE WHEN SHKZG='S' THEN DMBTR ELSE 0 END)-(CASE WHEN SHKZG='H' THEN DMBTR ELSE 0 END)) CASH_NET | Inflows – outflows = net amount |
FROM BSEG | |
WHERE HKONT IN ('0000113100', '0000113101') | The relevant line items must have been posted to a bank account. |
AND BSEG.GJAHR = 1997 AND BSEG.MANDT = 800 AND BSEG.BUKRS = 1000 | Restrict to the company under consideration and the relevant fiscal year |
GROUP BY BSEG.MANDT, BSEG.BUKRS, BSEG.GJAHR, BSEG.BELNR | |
HAVING CASH_NET<0 | Ultimately, only cash outflows are to be taken into consideration |
If you are trying out the query for yourself, remember to specify your own bank accounts in
HKONT IN ('0000113100','0000113101')
And to specify your own company at
AND BSEG.GJAHR = 1997 AND BSEG.MANDT = 800 AND BSEG.BUKRS = 1000
On my test data set, for example, I obtain the following results (excerpt):
| MANDT | BUKRS | GJAHR | BELNR | CASH_IN | CASH_OUT | CASH_NET |
|---|---|---|---|---|---|---|
| 800 | 1000 | 1997 | 2000000249 | 0 | 8077.39 | -8077.39 |
| 800 | 1000 | 1997 | 2000000247 | 0 | 3887.48 | -3887.48 |
| 800 | 1000 | 1997 | 2000000245 | 0 | 6102.70 | -6102.70 |
| 800 | 1000 | 1997 | 100007610 | 0 | 4371.55 | -4371.55 |
| 800 | 1000 | 1997 | 100007611 | 0 | 8743.09 | -8743.09 |
| … | … | … | … | … | … | … |
The list is without doubt a long one as disbursements are hardly a rare occurrence In any case, the list is already useful in determining the order of magnitude of disbursements. If you want to sort by size, add an “ORDER BY CASH_NET” to the query.
Step 2: Determine the process steps with which payments were initiated
In step 1, we have determined all disbursements. The question now arises of how to determine to what extent the payments were made outside the defined process.
Or, in other words, which payments were made “by roundabout means”?
We look at the SAP transaction used to post the payment. Usually we should expect that payments are normally posted with the payment run. This should correspond to the normal control process. The SAP transaction for the payment run is F110.
So, now let’s retrieve the transaction code (TCODE) for each disbursement:
SELECT BSEG.MANDT, BSEG.BUKRS, BSEG.GJAHR, BSEG.BELNR, SUM(CASE WHEN SHKZG='S' THEN DMBTR ELSE 0 END) CASH_IN, SUM(CASE WHEN SHKZG='H' THEN DMBTR ELSE 0 END) CASH_OUT, SUM( (CASE WHEN SHKZG='S' THEN DMBTR ELSE 0 END)-(CASE WHEN SHKZG='H' THEN DMBTR ELSE 0 END)) CASH_NET, TCODE FROM BSEG LEFT JOIN BKPF ON (BSEG.MANDT = BKPF.MANDT AND BSEG.BUKRS = BKPF.BUKRS AND BSEG.GJAHR = BKPF.GJAHR AND BSEG.BELNR = BKPF.BELNR) WHERE HKONT IN ('0000113100','0000113101') AND BSEG.GJAHR = 1997 AND BSEG.MANDT = 800 AND BSEG.BUKRS = 1000 GROUP BY BSEG.MANDT, BSEG.BUKRS, BSEG.GJAHR, BSEG.BELNR HAVING CASH_NET<0
The query is largely the same as the one in step 1, only the selected components have been added. An excerpt from the results for my test data set shows the following:
| MANDT | BUKRS | GJAHR | BELNR | CASH_IN | CASH_OUT | CASH_NET | TCODE |
|---|---|---|---|---|---|---|---|
| 800 | 1000 | 1997 | 2000000249 | 0 | 8077.39 | -8077.39 | F110 |
| 800 | 1000 | 1997 | 2000000247 | 0 | 3887.48 | -3887.48 | F110 |
| 800 | 1000 | 1997 | 2000000245 | 0 | 6102.70 | -6102.70 | F110 |
| 800 | 1000 | 1997 | 100007610 | 0 | 4371.55 | -4371.55 | FB01 |
| 800 | 1000 | 1997 | 100007611 | 0 | 8743.09 | -8743.09 | FB01 |
| … | … | … | … | … | … | … | … |
Special attention should be paid to the TCODE column. We can see from the excerpt that many payments have been made with the code F110 (payment run in SAP). Here one can assume the regular process has been followed, so that such cases need not arouse our suspicion.
However, some documents were posted with the code FB01. A quick Google search with the keywords “SAP FB01” shows that the transaction is “Post document”. Postings with this simple transaction can certainly be described as manual postings. This is a rather interesting type of transaction for payment transactions and therefore you definitely should carry out a random spot check of the documents with the FB01 code in this case!
Step 3: Identifying the extent of the problem
In the last step 3, we want to make a “cash check” and count how often which transaction code was used for the disbursements. To do this, we simply use the query from step 2, group by the TCODE and then have all documents counted:
SELECT TCODE ,COUNT(*) FROM (SELECT BSEG.MANDT, BSEG.BUKRS, BSEG.GJAHR, BSEG.BELNR, SUM(CASE WHEN SHKZG='S' THEN DMBTR ELSE 0 END) CASH_IN, SUM(CASE WHEN SHKZG='H' THEN DMBTR ELSE 0 END ) CASH_OUT, SUM( (CASE WHEN SHKZG='S' THEN DMBTR ELSE 0 END)-(CASE WHEN SHKZG='H' THEN DMBTR ELSE 0 END) ) CASH_NET, TCODE FROM BSEG LEFT JOIN BKPF ON (BSEG.MANDT = BKPF.MANDT AND BSEG.BUKRS = BKPF.BUKRS AND BSEG.GJAHR = BKPF.GJAHR AND BSEG.BELNR = BKPF.BELNR) WHERE HKONT IN ('0000113100', '0000113101') AND BSEG.GJAHR=1997 AND BSEG.MANDT=800 AND BSEG.BUKRS=1000 GROUP BY BSEG.MANDT, BSEG.BUKRS, BSEG.GJAHR, BSEG.BELNR) GROUP BY TCODE ORDER BY COUNT(*) DESC;
Only the part of the query marked in orange has changed from the query used in section 2 or has been added to it. Otherwise, the query is the same as that used in section 2.
On my test data set, I get the following result for the frequency of the transaction codes used for payment:
| TCODE | COUNT(*) |
|---|---|
| F110 | 131 |
| FB01 | 29 |
| FB08 | 4 |
| FBA7 | 1 |
The output shows that most cases were posted with the normal payment run (F110). FB01 was the second most frequently-used code. I have already pointed to the potentially damaging impact of posting payments in this way (“manual posting”) in step 2.
However, you can see that the transaction codes “FB08” and “FBA7” were also used. A quick Google search provides the following insight:
- FB08 = Reverse document
- FBA7 = Post vendor down payment
FB08 (Reverse document) is not critical because the document has already been reversed.
FBA7 (Post vendor down payment) was only used once. This is a transaction that is specifically intended for down payments. However, it is certainly worth asking someone in accounting why such down payments were not also processed using the normal payment run (F110), as the normal payment run is certainly the one that is best secured with regard to the internal control system.
Sounds too complicated?
Not everyone likes executing database queries. So why not give zap Audit a try? It will take care of all the technical details for you. Plus zap Audit is free for small company codes!
So, to adapt a well-known tagline:
“Just do(wnload) it!”
