In this blog post, we are once again going to devote our attention to the topic of Journal Entry Testing. I\u2019ll 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!<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
Let\u2019s 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 \u2013 BetrVG<\/em>) specifies:<\/p>\n\n\n\n \u201cSection 87 Right of co-determination<\/p>\n\n\n\n (1) The works council shall have a say in the following matters insofar as there is no statutory or collective regulation:<\/p>\n\n\n\n \u2026<\/p>\n\n\n\n \u2026\u201d<\/p>\n\n\n\n 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).<\/p>\n\n\n\n So without further ado, here\u2019s the appropriate SQL query you need to do just that. You can use a DIY approach by calling the \u201cDBACOCKPIT\u201d transaction in SAP and navigating to the \u201cSQL Editor\u201d via \u201cDiagnostics\u201d. Or, alternatively, you can also use SAP HANA Studio. The following SQL queries have already been tested on an SAP HANA database:<\/p>\n\n\n\n Replace MANDT=\u2019800\u2032 and BUKRS=\u20191000\u2032 with your client and company code and GJAHR=1997 with the fiscal year you wish to examine.<\/p>\n\n\n\n My test data set gives the following results (excerpt):<\/p>\n\n\n\n The table shows which user (USNAM) has posted how much (DOCUMENTS). The user type (USTYP) is also specified. It means:<\/p>\n\n\n\n \u201cREVERSED\u201d specifies the number of reversed documents below (that is, the number of reversed documents plus the reversal documents).<\/p>\n\n\n\n \u201cPORTION\u201d is the percentage of all postings.<\/p>\n\n\n\n \u201cMIN(CPUDT)\u201d is the entry date of the earliest document and \u201cMAX(CPUDT)\u201d is the entry date of the latest document for the user.<\/p>\n\n\n\n With the help of this simple statistic, you can answer the following questions:<\/p>\n\n\n\n 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 \u2013 with its integrated pseudonymization \u2013 do the work for you, leaving you to concentrate on what is most important: Auditing!<\/p>\n","protected":false},"excerpt":{"rendered":" In this blog post, we are once again going to devote our attention to the topic of Journal Entry Testing. I\u2019ll 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 […]<\/p>\n","protected":false},"author":4,"featured_media":10699,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"content-type":"","inline_featured_image":false,"footnotes":""},"categories":[38,37,40],"tags":[],"class_list":["post-11148","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-en-audit","category-en-compliance","category-en-finance"],"acf":[],"yoast_head":"\nSELECT 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<\/code><\/p>\n\n\n\nUSNAM<\/th> USTYP<\/th> DOCUMENTS<\/th> REVERSED<\/th> PORTION<\/th> MIN
(CPUDT)<\/th>MAX (CPUDT)<\/th><\/tr> USER_130<\/td> B<\/td> 1.830<\/td> 0<\/td> 25.92<\/td> 19970409<\/td> 19970413<\/td><\/tr> USER_90<\/td> A<\/td> 1.565<\/td> 0<\/td> 22.17<\/td> 19970122<\/td> 19971219<\/td><\/tr> USER_127<\/td> B<\/td> 1.085<\/td> 0<\/td> 15.37<\/td> 19980917<\/td> 19980917<\/td><\/tr> USER_131<\/td> A<\/td> 721<\/td> 0<\/td> 10.21<\/td> 19970417<\/td> 19971222<\/td><\/tr> USER_128<\/td> B<\/td> 368<\/td> 14<\/td> 5.21<\/td> 19970116<\/td> 19971006<\/td><\/tr> USER_149<\/td> A<\/td> 322<\/td> 0<\/td> 4.56<\/td> 19981209<\/td> 19990514<\/td><\/tr> \u2026<\/td> \u2026<\/td> \u2026<\/td> \u2026<\/td> \u2026<\/td> \u2026<\/td> \u2026<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n