picture of Prof. Dr. Nick Gehrke

written by

Prof. Dr. Nick Gehrke

Woman sigining electronic receipt of delivered package

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,
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_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,
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:

 

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. If you have any questions about zap Audit, then simply get in touch with us so we can set up an appointment with you to discuss them – with no obligation whatsoever:

 

Make an appointment

 

Comments