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:
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:
- Number of postings per author
- Manual postings to automatic accounts
- Debit postings for sales revenue accounts
- Postings with unusual posting texts
- Offset account analyses
- High payouts on cash accounts
- Timely entry and posting
- Postings on weekends and holidays
- Double postings
- Rounded digits before the decimal point
- Document number gap analysis
- Postings via interfaces
- Postings on the connection date
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:
- Analysis of recurring postings
- more to follow
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:
| BLART | LTEXT | DOCUMENTS | POSTING_USERS |
|---|---|---|---|
| KR | Vendor invoice | 1526 | 5 |
| SA | G/L account document | 1176 | 10 |
| AB | Accounting document | 1124 | 6 |
| WE | Goods receipt | 528 | 15 |
| RV | Billing doc.transfer | 432 | 7 |
| … | … | … | … |
A visualization in Excel illustrates the situation as follows:
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:
Our series on Journal Entry Testing is to be continued in future blog posts…!
