Journal Entry Testing (JET) examines the very basic requirements of accounting, the “Foundations of Accounting”, so to speak. In this blog post, we want to take a closer look at these basic requirements and show how, with SQL in your SAP, you can quickly and easily check whether document numbers are unique and whether they are systematically sequential. In other words, we are going to show you how to perform a fundamental check of the completeness of your postings and invoices. And that’s something that is always of interest to auditors – and to the tax office too.
Our Journal Entry Testing blog posts form a series of articles. For an overview of our coverage of the topic of JET, you will find the corresponding article with all the references here:
- Everybody is talking about it – but we are actually doing it: Journal Entry Testing in SAP (overview and document type statistics)
In this article, we will start by looking at how it is possible to perform an evaluation with SQL to clarify the legal basics.
Document number gap analysis
So, where better to start than by doing a good old document number gap analysis?!
It should be a matter of course that document numbers should not occur twice. This would seriously affect confidence in the accounting system. This is very easy to check with the help of SQL. You can use a DIY method to ensure you are compliant by calling the “DBACOCKPIT” transaction in SAP and navigating to the “SQL Editor” via “Diagnostics”. Alternatively, you can also use the SAP HANA Studio. All the following SQL queries have already been tested on an SAP HANA database:
SELECT MANDT, BUKRS, GJAHR, BELNR, COUNT(*) FROM BKPF
GROUP BY MANDT, BUKRS, GJAHR, BELNR
HAVING COUNT(*)>1
On my test dataset, the result is “empty” – what a stroke of luck! – anything else would simply not have been up to our expectations.
But now let’s turn our attention to document number gaps! If there are no document number gaps, then each document number should have a successor, so in theory there should be a document number higher by 1. This is a very formal definition and can be formulated very well in SQL:
SELECT BLART, MANDT, BUKRS, GJAHR, BELNR FROM BKPF B1 WHERE
MANDT='800' AND BUKRS='1000' AND GJAHR=2018
AND
NOT EXISTS (
SELECT B2.BELNR FROM BKPF B2 WHERE B1.MANDT=B2.MANDT AND B1.BUKRS=B2.BUKRS AND B1.GJAHR=B2.GJAHR AND LPAD(TO_NUMBER(B1.BELNR)+1,10,'0' ) = B2.BELNR)
AND BELNR NOT IN (SELECT MAX(B.BELNR) FROM BKPF B WHERE B.MANDT='800' AND B.BUKRS='1000' AND B.GJAHR=2018 GROUP BY B.BLART)
Note: Change the fields marked in red to the subject of your audit.
The query shows which document number has no successor. My test dataset contains the following hits (excerpt):
| BLART | MANDT | BUKRS | GJAHR | BELNR |
|---|---|---|---|---|
| RV | 100 | 1000 | 2018 | 4823965 |
| RV | 100 | 1000 | 2018 | 4823967 |
| RV | 100 | 1000 | 2018 | 4823971 |
| RV | 100 | 1000 | 2018 | 4823974 |
| RV | 100 | 1000 | 2018 | 4823980 |
| … | … | … | … | …. |
The document type RV is usually an (outgoing) invoice in SAP. There seem to be gaps occurring here over and over again. This is certainly worth investigating further and the audit department should be able to provide a systematic reason to explain such gaps.
I have already filtered out the last document numbers for a document type using the last additional condition in the SQL query, which follows “AND BELNR NOT IN”. Logically, these cannot have a successor.
So, if the query produces documents, then there are gaps in the document numbers.
Is that necessarily a bad thing?
– It depends.
If nothing else, the German Tax Office has the following to say in Section 14.5 Paragraph 10 of its Circular on the Application of Sales Tax [Umsatzsteueranwendungserlass]:
“A complete sequence of issued invoice numbers is not mandatory. It is also permissible to use only one consecutive number range within a global accounting system of different group companies located in different countries.”
This means that completeness is not mandatory. However, gaps in the document numbers will have to be explained. Otherwise, gaps in document numbers are considered an indication of incompleteness.
In addition, it says the following in the “Principles for properly maintaining and storing books, records and documents in electronic form and for data access” (German: “Grundsätze zur ordnungsmäßigen Führung und Aufbewahrung von Büchern, Aufzeichnungen und Unterlagen in elektronischer Form sowie zum Datenzugriff – GoBD“), provided by the German tax authorities (see point 32 on page 9):
“The bookkeeping must be such that it can provide an expert third party with an overview of the business transactions and the situation of the company within a reasonable period of time. It must be possible to track the origin and processing of business transactions seamlessly (progressive and retrograde verifiability).”
