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, | 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.
