Everybody is talking about it – but we are actually doing it: Journal Entry Testing in SAP

Journal Entry Testing (JET) contains standard analyses for use in auditing and especially in the context of annual audits. JET analyzes the accounting material in the accounts with regard to matters which are of general interest, i.e. those which are not tailored to the specifics of the company: the largest postings, most frequent postings, postings at weekends or strange posting text entries and much more. In this series of blog posts, I am going to show you how to do JET analysis in SAP directly in SQL. This is quick to do and allows you to obtain results in a very short timeframe!

How can Journal Entry Testing be performed efficiently?

Journal Entry Testing (JET) analyses are standard analyses. If you look at a standard ERP system like SAP, you can soon see why some automation is worthwhile. It is often a very tedious task to obtain the data and import it into an analysis tool such as Excel. It often takes longer to obtain the data than to perform the analyses themselves! This is the reason why, in this blog series, I am going to show you how to perform such analyses directly in SQL. Then you no longer need to extract the data from the SAP system and you can save yourself the tedious task of data procurement.

Of course, you need the relevant access privileges to the SAP database. This access is often well protected, but there is a noticeable trend that, with the new SAP HANA, it is becoming increasingly easier and more common to have direct access to the SAP database, for example with the SAP HANA Studio:

Journal Entry Testing in SAP HANA Studio

Otherwise, you can also execute SQL commands directly using the “DBACOCKPIT” transaction and navigating via “Diagnostics” to “SQL Editor”.

What are the most commonly used Journal Entry Testing analyses?

There are no binding standard analyses for Journal Entry Testing. However, a glance at the literature quickly provides an insight into which analyses are used most commonly. So we have done just that and referred to the book entitled “Journal Entry Testing” by Kersten Christian Droste and Jonas Tritschler, recently published by IDW-Verlag in 2018 (in German).

The following JET analyses are proposed there:

These are certainly not all conceivable JET analyses, but undoubtedly many very important ones. We also think that the following topics belong to the Journal Entry Testing:

This is certainly not an exhaustive list of all conceivable JET analyses, but it undoubtedly covers most of the important ones.

A First JET Analysis – Document Type Statistics

As an introduction to a JET analysis, I recommend gaining an overview of document types and the frequency of use of document types. This will give you a good initial insight into which business transactions are occurring and how often.

The corresponding SQL on a SAP HANA database is:

SELECT BKPF.BLART, T003T.LTEXT, COUNT(*) DOCUMENTS,COUNT(DISTINCT USNAM) POSTING_USERS FROM BKPF
LEFT JOIN T003T ON (BKPF.MANDT=T003T.MANDT AND BKPF.BLART=T003T.BLART AND T003T.SPRAS='D')
WHERE BUKRS='1000' AND GJAHR=2018
GROUP BY BKPF.BLART, T003T.LTEXT
ORDER BY COUNT(*) DESC

Replace BUKRS=’1000′ with your company code and GJAHR=2018 with your fiscal year.

On my test data set, for example, this gives the following output:

BLARTLTEXTDOCUMENTSPOSTING_USERS
KRVendor invoice15265
SAG/L account document117610
ABAccounting document11246
WEGoods receipt52815
RVBilling doc.transfer4327

A visualization in Excel illustrates the situation as follows:

Absolute frequency of document types for journal entry testing in SAP

From this bar chart, you can see very well which business transactions occur frequently and which ones are rather rare.

In zap Audit, such overview-like analyses are automatically generated in the dashboard. Which means that, thankfully, you don’t have to be familiar with SQL and you don’t even have to use Excel:document types for journal entry testing

Our series on Journal Entry Testing is to be continued in future blog posts…!

Artikel teilen

Facebook
Twitter
XING
LinkedIn