3-Way Match Part 1: Delivered as ordered? – Here’s how to find out!

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”:

1SELECT 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) CPUDTDate 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:

MANDTEBELNEBELPQUANTITY_INQUANTITY_OUTQUANTITY_TOTALBLDAT
1005600276136102.00002.00027.12.2016
10056002466811010.000010.00020.09.2016
100560023997710110.00001.100.00018.11.2016
1005600256169103.754.73003.754.73003.02.2017
10056002299431010.000010.00022.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:

2SELECT 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,
SUM(CASE WHEN SHKZG='S' THEN MENGE ELSE 0 END) QUANTITY_IN,
SUM(CASE WHEN SHKZG='H' THEN MENGE ELSE 0 END) QUANTITY_OUT,
SUM(CASE WHEN SHKZG='S' THEN MENGE ELSE 0 END) - SUM(CASE WHEN SHKZG='H' THEN MENGE ELSE 0 END) QUANTITY_TOTAL,
MAX(CPUDT) CPUDT
FROM EKBE
WHERE BEWTP='E'
GROUP BY MANDT, EBELN, EBELP) GR
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_TOTALif 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:

3SELECT 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,
SUM(CASE WHEN SHKZG='S' THEN MENGE ELSE 0 END) QUANTITY_IN,
SUM(CASE WHEN SHKZG='H' THEN MENGE ELSE 0 END) QUANTITY_OUT,
SUM(CASE WHEN SHKZG='S' THEN MENGE ELSE 0 END) - SUM(CASE WHEN SHKZG='H' THEN MENGE ELSE 0 END) QUANTITY_TOTAL,
MAX(CPUDT) CPUDT
FROM EKBE
WHERE BEWTP='E'
GROUP BY MANDT, EBELN, EBELP) GR
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:

MANDTEBELNEBELPAEDATCPUDTDAY_INTERVAL
10068002511361022.12.201627.12.20165
10068002466831016.09.201620.09.20164
10068002499721001.11.201618.11.201617
10068002582691020.04.201703.02.2017-76
10068002294431019.01.201622.01.20163

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.

Artikel teilen

Facebook
Twitter
XING
LinkedIn