In this blog post, we are once again going to devote our attention to the topic of Journal Entry Testing. I’ll show you how to use SQL in SAP to find out how much each SAP user has posted, as well as how many reversals there have been and in what time span. This gives you a good impression of how many accounting errors have been made and whether, for example, documents were posted late. A good introduction to posting data!
In this blog post, we are going to generate a statistic that shows which user has posted how much and get an impression of how many people are involved in accounting and how much has been posted automatically.
Statistics: Number of postings per user
Let’s start by taking a look at which users have been the most diligent accountants. At this point, we should point out that there are certain aspects related to works council rights that need to be taken into account when performing such an evaluation, because such an evaluation could be considered to constitute a check on employee performance. In such a case, the works council has to have a say in co-determining any such checks, as specified in Section 87 (1) Point 6 of the German Works Constitution Act (Betriebsverfassungsgesetz – BetrVG) specifies:
“Section 87 Right of co-determination
(1) The works council shall have a say in the following matters insofar as there is no statutory or collective regulation:
…
- the introduction and use of technical devices designed to monitor the behaviour or performance of the employees;
…”
Nevertheless, such forms of evaluation do appear to be used as a standard means of evaluation in the field of Journal Entry Testing (see also: Journal Entry Testing by Droste and Tritschler published by IDW Verlag).
So without further ado, here’s the appropriate SQL query you need to do just that. You can use a DIY approach by calling the “DBACOCKPIT” transaction in SAP and navigating to the “SQL Editor” via “Diagnostics”. Or, alternatively, you can also use SAP HANA Studio. The following SQL queries have already been tested on an SAP HANA database:
SELECT USNAM, USR02.USTYP, COUNT(*) DOCUMENTS, COUNT(CASE WHEN STBLG != '' THEN BELNR ELSE null END) REVERSED, ROUND(CAST(COUNT(*) AS INTEGER)/(SELECT COUNT(*) FROM BKPF WHERE BKPF.MANDT = '800' AND BKPF.BUKRS = '1000' AND GJAHR = 1997) * 100,2) PORTION, MIN(CPUDT), MAX(CPUDT)
FROM BKPF
LEFT JOIN USR02 ON (USR02.MANDT = BKPF.MANDT AND USR02.BNAME = BKPF.USNAM) WHERE BKPF.MANDT = '800' AND BKPF.BUKRS = '1000' AND GJAHR = 1997
GROUP BY USNAM, USTYP
ORDER BY COUNT(*) DESC
Replace MANDT=’800′ and BUKRS=’1000′ with your client and company code and GJAHR=1997 with the fiscal year you wish to examine.
My test data set gives the following results (excerpt):
| USNAM | USTYP | DOCUMENTS | REVERSED | PORTION | MIN (CPUDT) | MAX (CPUDT) |
|---|---|---|---|---|---|---|
| USER_130 | B | 1.830 | 0 | 25.92 | 19970409 | 19970413 |
| USER_90 | A | 1.565 | 0 | 22.17 | 19970122 | 19971219 |
| USER_127 | B | 1.085 | 0 | 15.37 | 19980917 | 19980917 |
| USER_131 | A | 721 | 0 | 10.21 | 19970417 | 19971222 |
| USER_128 | B | 368 | 14 | 5.21 | 19970116 | 19971006 |
| USER_149 | A | 322 | 0 | 4.56 | 19981209 | 19990514 |
| … | … | … | … | … | … | … |
The table shows which user (USNAM) has posted how much (DOCUMENTS). The user type (USTYP) is also specified. It means:
- A = Dialog user
- B = System user
“REVERSED” specifies the number of reversed documents below (that is, the number of reversed documents plus the reversal documents).
“PORTION” is the percentage of all postings.
“MIN(CPUDT)” is the entry date of the earliest document and “MAX(CPUDT)” is the entry date of the latest document for the user.
With the help of this simple statistic, you can answer the following questions:
- How many postings were made automatically, and how many manually? You can determine this by looking at the user type. System users (B) tend to indicate automatic postings, while dialog users (A) tend to correspond to manual postings. On the basis of this, it is possible to gain an idea of the degree of automation or digitization of accounting.
- How much has each user posted? This can be seen from the total number of documents per user (DOCUMENTS) or the percentage share (PORTION). Plus, the most diligent users are to be found at the top of the list.
- How many incorrect postings have been made? This can easily be seen by looking at the number of reversed documents (REVERSED) in relation to the total number of documents for the user (DOCUMENTS).
- Has a late posting been made again to a previous period? You can see this in the MAX(CPUDT) column. The fiscal year in this case is 1997. However, you can see that some users posted late in 1998 to the fiscal year 1997. From this, you can gain some idea of how quickly the year-end closing was prepared.
Perhaps SQL, SAP tables, BKPF and SELECT are terms which mean nothing to you? Or maybe you have concerns about analyzing personal data? Then let zap Audit – with its integrated pseudonymization – do the work for you, leaving you to concentrate on what is most important: Auditing!
