Our topic today looks at a “classic” in the area of internal controls in purchasing: the 3-Way Match. The 3-Way Match as a control assumes that order quantities, incoming goods quantities and quantities invoiced by the supplier must correspond. As usual, we want to take a data-oriented approach. So I’ll be showing you how you can see in the SAP data how your orders shape up according to the 3-Way Match. This time round, we will be looking at the extent to which goods receipt and order match as a use case.
In one of our recent blog posts, we examined what are known as delivery quantity tolerance limits in SAP. Delivery tolerance limits can be used to determine how much can be delivered for an order in terms of quantity. However, it is not possible to find out whether an overdelivery has actually taken place by analyzing the delivery tolerance limits. But in the end what is of course decisive is whether it actually happened and not whether it could have happened in theory!
How much was delivered?
In order to determine whether as much was delivered as was ordered, all goods receipts for an order item must be added together on a quantitative basis. Fortunately, there is a central source in SAP where this data can be found. In the “EKBE” (“Purchasing document history”) table, you will find the goods receipts and also the invoice receipts for each order item. We are interested in the goods receipts and we will then determine the quantity using the following SQL query. If you want to try this in your SAP system, use the SAP transaction “DBACOCKPIT” and navigate via “Diagnosis” to “SQL Editor”:
| 1 | SELECT MANDT, EBELN, EBELP, | The number of the purchase order and the purchase order item |
SUM(CASE WHEN SHKZG='S' THEN MENGE ELSE 0 END) QUANTITY_IN, | Total of all incoming quantities | |
SUM(CASE WHEN SHKZG='H' THEN MENGE ELSE 0 END) QUANTITY_OUT, | Total of all outgoing quantities | |
SUM(CASE WHEN SHKZG='S' THEN MENGE ELSE 0 END) - SUM(CASE WHEN SHKZG='H' THEN MENGE ELSE 0 END) QUANTITY_TOTAL, | Quantities received and received on balance | |
MAX(CPUDT) CPUDT | Date on which the last goods receipt was entered | |
FROM EKBE | “Purchasing document history” | |
WHERE BEWTP='E' | Indicator for “goods receipt” | |
GROUP BY MANDT, EBELN, EBELP |
In my test dataset, this gives the following output:
| MANDT | EBELN | EBELP | QUANTITY_IN | QUANTITY_OUT | QUANTITY_TOTAL | BLDAT |
|---|---|---|---|---|---|---|
| 100 | 5600276136 | 10 | 2.000 | 0 | 2.000 | 27.12.2016 |
| 100 | 5600246681 | 10 | 10.000 | 0 | 10.000 | 20.09.2016 |
| 100 | 5600239977 | 10 | 110.000 | 0 | 1.100.000 | 18.11.2016 |
| 100 | 5600256169 | 10 | 3.754.730 | 0 | 3.754.730 | 03.02.2017 |
| 100 | 5600229943 | 10 | 10.000 | 0 | 10.000 | 22.01.2016 |
| … | … | … | … | … | … | … |
QUANTITY_TOTAL displays the total quantity delivered for the order item.
This is already half the battle. These quantities must now be compared with the order quantities.
Comparison with the original purchase order
Purchase orders and purchase order items are located in the SAP tables EKKO (“Purchasing document header”) and EKPO (“Purchasing document item”). Now the quantities of the order items must be compared with the quantities delivered from the goods receipts. Of course, the cases where actual overdeliveries took place are particularly interesting. This can be done using SQL as follows:
| 2 | SELECT EKPO.MANDT, EKPO.EBELN, EKPO.EBELP, EKPO.MENGE, GR.QUANTITY_TOTAL FROM ( | The purchase order document number, purchase order item, quantity ordered, and total quantity delivered. |
SELECT MANDT, EBELN, EBELP, | The query exactly as above | |
JOIN EKPO ON (GR.MANDT=EKPO.MANDT AND GR.EBELN=EKPO.EBELN AND GR.EBELP=EKPO.EBELP) | JOIN to the original purchase order item, | |
WHERE EKPO.MENGE < GR.QUANTITY_TOTAL | if the quantity delivered is greater than the quantity ordered |
The result quantity of this query shows you all purchase order items that were actually delivered.
While we’re at it: Measuring delivery reliability
While we’re at it, let’s pause for a moment and see what else we could evaluate. Given that we are already dealing with goods receipts, one could also evaluate how quickly the goods were delivered. This could be used to examine delivery reliability. To do this, we need to examine how long it took from the order to the last goods delivery for an order item:
| 3 | SELECT EKPO.MANDT, EKPO.EBELN, EKPO.EBELP, EKPO.AEDAT, GR.CPUDT, DAYS_BETWEEN(TO_DATE(AEDAT), TO_DATE(GR.CPUDT)) DAY_INTERVAL FROM ( | The purchase order document number, purchase order item, date of the purchase order item, date of entry of the last goods receipt, the difference between date of the purchase order item and entry of the last goods receipt |
SELECT MANDT, EBELN, EBELP, | The query exactly as above | |
JOIN EKPO ON (GR.MANDT=EKPO.MANDT AND GR.EBELN=EKPO.EBELN AND GR.EBELP=EKPO.EBELP) | JOIN to the original purchase order item |
In my test dataset, this gives the following output:
| MANDT | EBELN | EBELP | AEDAT | CPUDT | DAY_INTERVAL |
|---|---|---|---|---|---|
| 100 | 6800251136 | 10 | 22.12.2016 | 27.12.2016 | 5 |
| 100 | 6800246683 | 10 | 16.09.2016 | 20.09.2016 | 4 |
| 100 | 6800249972 | 10 | 01.11.2016 | 18.11.2016 | 17 |
| 100 | 6800258269 | 10 | 20.04.2017 | 03.02.2017 | -76 |
| 100 | 6800229443 | 10 | 19.01.2016 | 22.01.2016 | 3 |
| … | … | … | … | … | … |
DAY_INTERVAL shows how many days there were between the date of the purchase order item and the date of entry of the last goods receipt. To make sure that the AEDAT field is the creation date of the purchase order item, you only need to look at the change documents (CDPOS/CDHDR tables). If there are no entries for the purchase order item to be examined here, there have been no changes after the date in AEDAT – this can thus only be the date of creation of the purchase order item. If this is taken into account, then one can in this way deduce the speed of the delivery (= delivery reliability). But a degree of caution is required here: Of course, it is only possible to do this if the purchase orders were actually created according to the usual purchasing process. In the result provided as an example above, one could reasonably have doubts about this, because there is also a negative period of -76 days. Which means: The goods receipt came before order. But how can that be? Probably what has happened is what happens in so many companies: The order was created after the goods receipt. Then of course it is no wonder that the 3-Way Match works! Negative daily intervals are therefore an indication of a process anomaly in purchasing and can in the worst case scenario also be interpreted as a circumvention of an internal control…
Are you looking for an automated solution?
The data indicator described in this blog article is already included in zap Audit. Whether you simply lack the necessary rights to execute SQL in SAP or you want to evaluate some of the more than 135 other data indicators that we offer in addition to this indicator, zap Audit is able to automate all manual activities that it is possible to automate. All you have to do is sit back and evaluate the results.
