Putting a stop to Process Outliers: How to find Deviations in Purchase-to-Pay

Normally, outliers are referred to as extreme values that deviate highly from other observations. The causes of this can be manifold and range from fluctuations during measurement and experimental errors to more novel types of phenomena. In the following case, however, it is rather down to a lack of knowledge, omission or simple laziness in the execution of business processes. What we are going to discuss below is the matter of documents which, contrary to expectations, may or may not have a corresponding purchase order reference.

The prerequisites for documents with or without a purchase order reference

Before we can even begin to analyze any outliers, we must first establish a set of data to work from. Naturally, the first question that comes up is how a purchase order reference is actually created in SAP. It is pretty easy to find the answer to this question. A quick glance at the characteristic FI tables in SAP (BKPF and BSEG) soon reveals the following field in the accounting document segments (BSEG):

EBELN – Purchasing Document Number

By using this approach, it is quick and easy to establish the connection to an purchase order. However, it makes little sense to simply take all those documents that do not have a purchase order reference and run with them straight to the department concerned, because there are many scenarios where invoices are paid without reference to a purchase order, e.g. for recurring amounts such as rents or mobile phone contracts. For this reason, we will consider both possibilities in this article, looking at documents with and without a purchase order reference contrary to expectations.

At this point, however, there are different schools of thought as to how best to proceed and we have to decide in which direction we want to investigate more closely. On the one hand, an analysis with regard to the document types would be possible because, for example, the document type GR in MM for goods receipts for purchase orders or for orders usually requires a purchase order. On the other hand, it is also possible to evaluate suppliers / creditors. However, this may reveal the same problem as described above: If the company concludes mobile phone contracts with Deutsche Telekom, but at the same time receives new mobile phones from renewals every two years, there is rarely an order reference, so that false positives are almost certain to come up, because a mobile phone invoice is settled relatively often, but an order reference is only available in rare cases (mobile phone renewal).

Each procedure offers its own advantages and disadvantages, so that there is no right or wrong way to proceed in this context. In this case, we have opted to use the document types. However, these are not found in the “BSEG” table in SAP, but in the “BKPF” table instead.

Analysis of purchase order references in SAP

Now that we have defined the conditions and the procedure to follow, we can go into things in more detail. To do this, we use the SQL Editor in SAP, which can be accessed by using the “DBACOCKPIT” transaction and then Diagnostics > SQL Editor. First of all, we have to include some basic information so that we do not accidentally confuse the data from the different company codes and clients. With the respective number of document types, the SQL Query would look like this:

SELECT COUNT(BELNR) AS COUNT_BELNR, BLART, MANDT, BUKRS, GJAHR
FROM BKPF
WHERE GJAHR='1998' AND BUKRS='1000' AND MANDT='800'
GROUP BY BLART, MANDT, BUKRS, GJAHR
ORDER BY COUNT(BELNR) DESC

I have also restricted the company code, client and fiscal year in order to obtain a clear set of results. In this way, you can analyze each company code / client / fiscal year individually and in detail:

This gives us a simple overview of the document types and the number of documents per document type. To ensure that the tables BSEG and BKPF are linked, we must define a table join (shown in blue) of the two SAP tables using the common primary keys (shown in green) and include the additional fields for selection (shown in purple):

SELECT COUNT(DISTINCT K.BELNR) AS COUNT_BELNR, COUNT(DISTINCT CASE WHEN B.EBELN NOT LIKE '' THEN B.BELNR ELSE null END) AS COUNT_EBELN, K.BLART, K.MANDT, K.BUKRS, K.GJAHR
FROM BKPF AS K
LEFT JOIN BSEG AS B ON K.MANDT=B.MANDT AND K.BUKRS = B.BUKRS AND K.BELNR = B.BELNR AND K.GJAHR = B.GJAHR
WHERE K.GJAHR='1998' AND K.BUKRS='1000' AND K.MANDT='800'
GROUP BY K.BLART, K.MANDT, K.BUKRS, K.GJAHR
ORDER BY COUNT_BELNR DESC

The purple area indicates that a document should only be counted if it has a reference to a purchasing document, or in other words if the “EBELN” field is not empty (“NOT LIKE”).

The results table now shows both documents with purchase order reference contrary to expectations and documents without purchase order reference contrary to expectations. For example, take the document type RV. For 2 of 411 documents (ca. 0.5%) from SD there is a purchase order reference, whereas the remaining 99.5% do not have a purchase order reference. Or take the document type RE. 345 out of 346 documents (ca. 99.7%) have a purchase order reference. Only one has none. A very striking example of an outlier indeed!

Which documents it is exactly is probably the next question you would like to ask yourself, and it is one which I would like to answer by showing you another a quick SQL query you can run, and then leaving it down to you to put this analysis to optimum use (the places where you need to make changes are marked in green):

SELECT DISTINCT K.BELNR, K.BLART, K.MANDT, K.BUKRS, K.GJAHR FROM BKPF AS K
WHERE EXISTS (SELECT * FROM BSEG AS B WHERE K.MANDT=B.MANDT AND K.BUKRS = B.BUKRS AND K.BELNR = B.BELNR AND K.GJAHR = B.GJAHR AND B.EBELN NOT LIKE '')
AND K.BUKRS='1000' AND K.GJAHR='1998' AND K.BLART='RV'

Or relating to the document type RE:

SELECT DISTINCT K.BELNR, K.BLART, K.MANDT, K.BUKRS, K.GJAHR FROM BKPF AS K
WHERE NOT EXISTS (SELECT * FROM BSEG AS B WHERE K.MANDT=B.MANDT AND K.BUKRS = B.BUKRS AND K.BELNR = B.BELNR AND K.GJAHR = B.GJAHR AND B.EBELN NOT LIKE '')
AND K.BUKRS='1000' AND K.GJAHR='1998' AND K.BLART='RE'

This indicator is one of over 130 indicators integrated in zap Audit and several ways on reducing false positives exist, but there would be no surprise when using zap Audit if I would tell you every secret, right? If you have any questions about the use of zap Audit, do not hesitate to get in touch.