In this blog post, we are going to take another look at the 3-Way Match. This time it's about an order being placed at the beginning of a purchase process and there of course being an invoice at the other end. Things start to get interesting when the amounts on the order suddenly turn out to differ from the amounts on the invoice. And they can get even more interesting if the amount stated on the invoice is more than that on the order. in what follows, I'll show you how to find such cases in SAP.

 

The 3-Way Match is a classic internal control and is based on the fact that quantities and/or amounts at the three points, namely on the order + goods receipt + invoice, (should) match. This time, we’re interested in the match between order and invoice: based on value. The 3-Way Match is a topic which has been dealt with on our blog a number of times before. You can find the other articles related to the topic below:

 

How high were the invoice amounts for the services ordered?

In order to determine how high the invoice amounts were, it must be determined, for each order item, how high the invoice or the invoices for it were if several invoices were sent for one order item. Fortunately, the "fate" of an order item with all goods receipts and invoice receipts is to find itself in the EKBE ("Purchasing Document History") central table. The following query determines all invoice amounts per purchase order item in the document currency. If you want to try this on your SAP system, use the SAP transaction "DBACOCKPIT" and navigate via "Diagnosis" to "SQL Editor" (tested on a SAP HANA):

 

1 SELECT MANDT, EBELN, EBELP,

Number of the purchase order and the purchase order item

SUM(CASE WHEN SHKZG='S' THEN WRBTR ELSE 0 END) AMOUNT_IN,

Amount of invoice receipt

SUM(CASE WHEN SHKZG='H' THEN WRBTR ELSE 0 END) AMOUNT_OUT,

Total of the goods amounts claimed back (for example, if credit memos were posted to the purchase order)

SUM(CASE WHEN SHKZG='S' THEN WRBTR ELSE 0 END) - SUM(CASE WHEN SHKZG='H' THEN WRBTR ELSE 0 END) AMOUNT_TOTAL

Total net invoice value

FROM EKBE

Purchasing Document History Table

WHERE BEWTP='Q'

Only Invoice Transactions

GROUP BY MANDT, EBELN, EBELP

 

 

As a result, I get the following results on my test dataset:

 

MANDT

EBELN

EBELP

AMOUNT_IN

AMOUNT_OUT

AMOUNT_TOTAL

100

7700256133

10

6,28 €

- €

6,28 €

100

7700248864

10

38,16 €

- €

38,16 €

100

7700249972

10

867,90 €

- €

867,90 €

100

7700256269

10

3.754,73 €

- €

3.754,73 €

100

7600229141

10

23,00 €

- €

  23,00 €

100

7600256269

30

5.476,45 €

- €

5.476,45 €

 

So you can now see exactly how much was invoiced, broken down by individual order item.

Comparison with the order

Of course, this only really becomes interesting when we can check the invoice values against the order values to see where services suddenly became much more expensive than was expected when the order was placed. You can use the following query to do this:

 

2 SELECT INV.EBELN, INV.EBELP, INV.AMOUNT_TOTAL, EKPO.NETWR, 100.0 * AMOUNT_TOTAL / NETWR-100 PERCENT_TOO_MUCH FROM (

Number of the purchase order, the purchase order item in the purchase order, invoice total, purchase order total, calculation of how much more was invoiced as a percentage of the invoice.

SELECT MANDT, EBELN, EBELP,
SUM(CASE WHEN SHKZG='S' THEN WRBTR ELSE 0 END) AMOUNT_IN,
SUM(CASE WHEN SHKZG='H' THEN WRBTR ELSE 0 END) AMOUNT_OUT,
SUM(CASE WHEN SHKZG='S' THEN WRBTR ELSE 0 END) - SUM(CASE WHEN SHKZG='H' THEN WRBTR ELSE 0 END) AMOUNT_TOTAL
FROM EKBE
WHERE BEWTP='Q'
GROUP BY MANDT,EBELN,EBELP) INV

The query exactly as above

LEFT JOIN EKPO ON (EKPO.MANDT = INV.MANDT AND EKPO.EBELN = INV.EBELN AND EKPO.EBELP = INV.EBELP)

A JOIN to the SAP table EKPO because the corresponding purchase order items are in the EKPO.

WHERE INV.AMOUNT_TOTAL > EKPO.NETWR

Only list cases where the invoice total was higher than the order total

ORDER BY 100.0*AMOUNT_TOTAL/NETWR-100 DESC

Sorting by cases with the highest percentage variance

 

Based on this evaluation, you can now see very clearly in which cases much more was invoiced as a percentage than was expected when the order was placed:

 

EBELN

EBELP

AMOUNT_TOTAL

NETWR

PERCENT_TOO_
MUCH

7700226971

30

9,60 €

3,10 €

209,68

7700213638

10

7,37 €

2,52 €

192,46

7700229753

10

25,90 €

10,26 €

152,44

7700249155

80

15,10 €

8,15 €

85,28

7700242542

10

8,93 €

4,93 €

81,14

7700229244

20

11,76 €

6,86 €

71,43

 

You can see that the top lines are cases when the amount (AMOUNT_TOTAL) was much more than the order value (NETWR) when calculated as a percentage. The cases listed are impressive in percentage terms, but rather negligible in absolute terms. So in this data extract things are really only half as bad as they seem! But there is one recommendation that we can already make based on this: you should pay particular attention to cases with high percentage deviations, which are also high in terms of absolute amount. If there are such cases, make sure you examine the individual case for such orders in more detail and work together with the specialist department in a “moderator” role to establish how such cases could have occurred.

 

Sounds too complicated?

We have already implemented questions such as different amounts on orders and invoices, or overdeliveries, as beta indicators in zap Audit. If you would like to test zap Audit free of charge, you can do so at any time with a small company code. If you have any questions about taking up our offer of a free and non-binding "trial" of zap Audit, then simply make an appointment with us to discuss any queries you may have:

 

Make an appointment

 

Comments