Searching for unusual posting texts in SAP
Performing a check for unusual posting texts is one kind of standard analysis within the framework of Journal Entry Testing (JET). In this blog article, we will show you two possible analyses to get a quick overview of how posting texts are being handled in SAP.
What is considered to be unusual and what is not is often a matter which remains in the eye of the beholder. It is sometimes difficult to arrive at any objective conclusion. Our suggestion is therefore first to examine whether accounting documentation has been kept correctly and that texts have been entered for postings, and then only in the second step to analyze whether any unusual posting texts actually exist. So, now, let’s get started!
Are there posting texts or not? – That is the question
Let us first create an overview of whether posting texts have been used at all or not. To perform this analysis, we first open the “DBACOCKPIT” transaction in the SAP GUI. Under the “Diagnostics” menu item, you will find the “SQL-Editor”, which we will use for the following evaluation. The SQL query for a comparison of postings with and without posting texts then looks like this (tried and tested on SAP HANA):
SELECT MANDT, BUKRS, GJAHR, BLART, AWTYP, GRPID,
COUNT(CASE WHEN(BKTXT ='' OR TRIM(BKTXT)='') THEN BELNR ELSE null END) WITHOUT_TXT,
COUNT(CASE WHEN(BKTXT !='' OR TRIM(BKTXT)!='') THEN BELNR ELSE null END) WITH_TXT
WHERE GJAHR=2018 AND BUKRS='1000'
GROUP BY MANDT, BUKRS, GJAHR, BLART, AWTYP, GRPID
ORDER BY MANDT, BUKRS, GJAHR, BLART, AWTYP, GRPID, COUNT(*) DESC;
Replace GJAHR and BUKRS with the subject of your audit
In addition to information on the relationship between missing and existing texts (WITHOUT_TXT and WITH_TXT), the query also contains information on the client, company code, fiscal year and document type, which should not come as any big surprise. However, we have included the fields “AWTYP” (reference activity) and “GRPID” (name of batch input session). The reference activity describes the system from which the respective document/document type originates. The name of the field “GRPID” already suggests why we included the field in the analysis. If this field has a value, then the posting was carried out automatically by the system and indicates a systematic problem if the column “WITHOUT_TXT” also shows a value.
The results for the test system were as follows:
First of all, the results show that no postings were made using a batch input session. You can read more about what batch input sessions have to do with the digitization of accounting here.
In addition, the third line highlighted in green is particularly noteworthy in the results. The posting texts are missing for all documents that come from the SAP MM system (table MKPF = material document). In any case, this is reason enough to sit down and discuss the matter further with your colleagues over another cup of coffee.
So far so good then – at least the risk appears to be manageable. But what about really unusual posting texts?
The basics of unusual posting texts in SAP
Now, let’s take a look at things in more detail. First of all, each company must determine for itself what unusual posting texts are. In general, however, it is certainly possible to borrow some interesting terms from the field of tax law. Certain operating expenses, for example, can only be deducted to a limited extent (for tax purposes). It could therefore be worthwhile to examine posting texts which point to such business expenses more closely. The business expenses in question appear in Section 4 (5) of the German Income Tax Act (Einkommensteuergesetz – EStG). Here are some examples:
“Section 4 The concept of profit in general
[…] (5) 1 The following operating expenses shall not reduce profit:
- expenditure on gifts to persons who are not employees of the taxable person. Sentence 1 shall not apply if the acquisition or manufacturing costs of the objects turned over to the recipient in the financial year do not exceed a total of 35 euros;
- expenses for the hospitality of persons for business purposes, insofar as they exceed 70 percent of the expenses, […].
- expenditure on hunting or fishing, on sailing yachts or motor yachts and for similar purposes and on related catering; […]
- fines, penalties and warnings imposed by any court or authority within the scope of this Act or by any institution of the European Union”.
On the basis of these provisions, a list of unusual accounting texts could contain the following terms:
- ADMINISTRATIVE FINE
- TAX PENALTY
- BLACK MONEY
Armed with this list, we can now search for appropriate postings. In order to improve the probability of obtaining hits, the posting text is first converted to upper case (UPPER(BKTXT)), so that upper/lower case has no influence on the results of the analysis:
SELECT MANDT, BUKRS, GJAHR, BELNR, BLART, AWTYP, GRPID, BKTXT, STBLG, XREVERSAL
WHERE UPPER(BKTXT) LIKE '% boat%'
In addition to the basic fields such as BUKRS, GJAHR, BELNR, etc., we also have the fields “STBLG”. (reversal document) and “XREVERSAL” (indicates whether a document is a reversal document or a reversed document). In this way, reversals can be effectively excluded, if desired. Just another little trick for you from the data analysis experts ;).
For reasons of simplification, we have filtered the results down to only one term here, though you can of course simply add any other words with an “OR”, using the following formulation at the end (note that this is also case-sensitive):
OR UPPER(BKTXT) LIKE '% gift%'
My test system did not show any results. Can you think of any terms that should perhaps be added to the list and that I should check for again? If so, then please let me know in the comments below.