picture of Prof. Dr. Nick Gehrke

written by

Prof. Dr. Nick Gehrke

Young handsome barber making haircut of attractive bearded man in barbershop

Giving sales revenues a "haircut" is often a way of helping managing directors and sales managers when their targets are slipping away from them. We look at this specific pattern of manipulation of sales revenues and how you can identify it in your SAP data.

 

The sales revenue "haircut" with no effect on net income

The fraud pattern which I am going to present here is what I will refer to "manipulation of sales revenue not affecting net income". Postings are made which lead to higher sales, but which are offset by postings of expenses of a different nature, i.e. figures are being "pushed around" within the profit & loss (P&L) statement. Overall, the net income for the year remains the same; there are just figures that have been reallocated within the profit & loss statement. An example would be a posting of the following type:

Personnel expenses (debit) of €1,000 booked against €1,000 of sales revenue (credit)

The next step is to find out whether such postings exist in the SAP system.

 

Criteria for data delimitation

The question now arises as to which criteria should be used in order to query the financial accounting data in this respect. The criteria that match the type of scenario we are looking for can be formulated as follows:

  1. All postings that have only been posted to the profit & loss statement in debit and credit (i.e. not affecting net income).
  2. Different financial accounting accounts were used for debit and credit.

To test the SQL queries described in your SAP system, use the "DBACOCKPIT" transaction as usual and navigate to "SQL Editor" via "Diagnostics".

The following query lists all the relevant documents:

 

1

SELECT DISTINCT BKPF.MANDT||'-'||BKPF.GJAHR||'-'||BKPF.BUKRS||'-'||BKPF.BELNR DOCID FROM BKPF
JOIN BSEG ON (BKPF.MANDT=BSEG.MANDT AND BKPF.BUKRS=BSEG.BUKRS AND BKPF.GJAHR=BSEG.GJAHR AND BKPF.BELNR=BSEG.BELNR)
WHERE
BSEG.SHKZG='H' AND (BSEG.XBILK IS NULL OR BSEG.XBILK='')
AND (BKPF.STBLG IS NULL OR BKPF.STBLG='')
AND EXISTS (SELECT * FROM BSEG B WHERE B.MANDT=BSEG.MANDT AND B.BUKRS=BSEG.BUKRS AND B.GJAHR=BSEG.GJAHR AND B.BELNR=BSEG.BELNR AND B.SHKZG='S' AND B.HKONT!=BSEG.HKONT AND (B.XBILK IS NULL OR B.XBILK='') )
AND NOT EXISTS (SELECT * FROM BSEG B WHERE B.MANDT=BSEG.MANDT AND B.BUKRS=BSEG.BUKRS AND B.GJAHR=BSEG.GJAHR AND B.BELNR=BSEG.BELNR AND B.SHKZG='S' AND B.HKONT=BSEG.HKONT)
AND NOT EXISTS (SELECT * FROM BSEG B WHERE B.MANDT=BSEG.MANDT AND B.BUKRS=BSEG.BUKRS AND B.GJAHR=BSEG.GJAHR AND B.BELNR=BSEG.BELNR AND B.SHKZG='S' AND B.XBILK='X')
AND NOT EXISTS (SELECT * FROM BSEG B WHERE B.MANDT=BSEG.MANDT AND B.BUKRS=BSEG.BUKRS AND B.GJAHR=BSEG.GJAHR AND B.BELNR=BSEG.BELNR AND B.SHKZG='H' AND B.XBILK='X')

 

However, this is "only" a list of documents. Here it is worth making a few remarks which will help you to understand the query better. The places marked in red are explained in the last column:

 

1a

SELECT DISTINCT BKPF.MANDT||'-'||BKPF.GJAHR||'-'||BKPF.BUKRS||'-'||BKPF.BELNR DOCID FROM BKPF

Removal of duplicate documents

1b

JOIN BSEG ON (BKPF.MANDT=BSEG.MANDT AND BKPF.BUKRS=BSEG.BUKRS AND BKPF.GJAHR=BSEG.GJAHR AND BKPF.BELNR=BSEG.BELNR) 

Classic "Join" between BKPF and BSEG

1c

WHERE 
BSEG.SHKZG='H' AND (BSEG.XBILK IS NULL OR BSEG.XBILK='') 

Restricts to items that have been posted to "credit" in the profit & loss statement...

1d

AND (BKPF.STBLG IS NULL OR BKPF.STBLG='') 

... and which are not a reversal...

1e

AND EXISTS (SELECT * FROM BSEG B WHERE B.MANDT=BSEG.MANDT AND B.BUKRS=BSEG.BUKRS AND B.GJAHR=BSEG.GJAHR AND B.BELNR=BSEG.BELNR AND B.SHKZG='S' AND B.HKONT!=BSEG.HKONT AND (B.XBILK IS NULL OR B.XBILK='') ) 

... for which there is an item on the "debit side" in the document that was posted to another account and belongs in the profit & loss statement...
1f

AND NOT EXISTS (SELECT * FROM BSEG B WHERE B.MANDT=BSEG.MANDT AND B.BUKRS=BSEG.BUKRS AND B.GJAHR=BSEG.GJAHR AND B.BELNR=BSEG.BELNR AND B.SHKZG='S' AND B.HKONT=BSEG.HKONT) 

 ... and there is an item on the "debit side" that was posted to the same P&L account as in "credit"...
1g

AND NOT EXISTS (SELECT * FROM BSEG B WHERE B.MANDT=BSEG.MANDT AND B.BUKRS=BSEG.BUKRS AND B.GJAHR=BSEG.GJAHR AND B.BELNR=BSEG.BELNR AND B.SHKZG='S' AND B.XBILK='X') 

... and there is an item on the "debit side" that was posted to the balance sheet...
1h

AND NOT EXISTS (SELECT * FROM BSEG B WHERE B.MANDT=BSEG.MANDT AND B.BUKRS=BSEG.BUKRS AND B.GJAHR=BSEG.GJAHR AND B.BELNR=BSEG.BELNR AND B.SHKZG='H' AND B.XBILK='X')

..., but also no "credit" item that was posted to the balance sheet.

 

We now want to determine which financial accounting accounts were used in debit and credit for each of these documents. The above query is reused with a WHERE condition:

 

2

SELECT BSEG.MANDT,BSEG.BUKRS,BSEG.GJAHR,BSEG.BELNR,STRING_AGG(CASE WHEN BSEG.SHKZG='S' THEN SKAT.TXT50 ELSE null END,', ') DEBIT, STRING_AGG(CASE WHEN BSEG.SHKZG='H' THEN SKAT.TXT50 ELSE null END,', ') CREDIT
FROM (SELECT DISTINCT MANDT,BUKRS,GJAHR,BELNR,HKONT,SHKZG,XBILK FROM BSEG) BSEG
LEFT JOIN T001 ON (T001.MANDT=BSEG.MANDT AND T001.BUKRS=BSEG.BUKRS)
LEFT JOIN SKAT ON (SKAT.MANDT=BSEG.MANDT AND SKAT.KTOPL=T001.KTOPL AND SKAT.SAKNR=BSEG.HKONT AND SKAT.SPRAS='D')
WHERE
BSEG.MANDT||'-'||BSEG.GJAHR||'-'||BSEG.BUKRS||'-'||BSEG.BELNR IN
(
SELECT DISTINCT BKPF.MANDT||'-'||BKPF.GJAHR||'-'||BKPF.BUKRS||'-'||BKPF.BELNR DOCID FROM BKPF
JOIN BSEG ON (BKPF.MANDT=BSEG.MANDT AND BKPF.BUKRS=BSEG.BUKRS AND BKPF.GJAHR=BSEG.GJAHR AND BKPF.BELNR=BSEG.BELNR)
WHERE
BSEG.SHKZG='H' AND (BSEG.XBILK IS NULL OR BSEG.XBILK='')
AND (BKPF.STBLG IS NULL OR BKPF.STBLG='')
AND EXISTS (SELECT * FROM BSEG B WHERE B.MANDT=BSEG.MANDT AND B.BUKRS=BSEG.BUKRS AND B.GJAHR=BSEG.GJAHR AND B.BELNR=BSEG.BELNR AND B.SHKZG='S' AND B.HKONT!=BSEG.HKONT AND (B.XBILK IS NULL OR B.XBILK=''))
AND NOT EXISTS (SELECT * FROM BSEG B WHERE B.MANDT=BSEG.MANDT AND B.BUKRS=BSEG.BUKRS AND B.GJAHR=BSEG.GJAHR AND B.BELNR=BSEG.BELNR AND B.SHKZG='S' AND B.HKONT=BSEG.HKONT)
AND NOT EXISTS (SELECT * FROM BSEG B WHERE B.MANDT=BSEG.MANDT AND B.BUKRS=BSEG.BUKRS AND B.GJAHR=BSEG.GJAHR AND B.BELNR=BSEG.BELNR AND B.SHKZG='S' AND B.XBILK='X')
AND NOT EXISTS (SELECT * FROM BSEG B WHERE B.MANDT=BSEG.MANDT AND B.BUKRS=BSEG.BUKRS AND B.GJAHR=BSEG.GJAHR AND B.BELNR=BSEG.BELNR AND B.SHKZG='H' AND B.XBILK='X')
)
GROUP BY BSEG.MANDT,BSEG.BUKRS,BSEG.GJAHR,BSEG.BELNR

 

The result of this query shows which documents used which accounts.

Now, to put it all together, we want to find out how often the various account assignments occur in accounting documents. To do this, we aggregate the query that we just used above to count the number of occurrences accordingly:

 

3

SELECT DEBIT,CREDIT,COUNT(DISTINCT BELNR) AMOUNT_BELNR FROM
(
SELECT BSEG.MANDT,BSEG.BUKRS,BSEG.GJAHR,BSEG.BELNR,STRING_AGG(CASE WHEN BSEG.SHKZG='S' THEN SKAT.TXT50 ELSE null END,', ') DEBIT, STRING_AGG(CASE WHEN BSEG.SHKZG='H' THEN SKAT.TXT50 ELSE null END,', ') CREDIT
FROM (SELECT DISTINCT MANDT,BUKRS,GJAHR,BELNR,HKONT,SHKZG,XBILK FROM BSEG) BSEG
LEFT JOIN T001 ON (T001.MANDT=BSEG.MANDT AND T001.BUKRS=BSEG.BUKRS)
LEFT JOIN SKAT ON (SKAT.MANDT=BSEG.MANDT AND SKAT.KTOPL=T001.KTOPL AND SKAT.SAKNR=BSEG.HKONT AND SKAT.SPRAS='D')
WHERE
BSEG.MANDT||'-'||BSEG.GJAHR||'-'||BSEG.BUKRS||'-'||BSEG.BELNR IN
(
SELECT DISTINCT BKPF.MANDT||'-'||BKPF.GJAHR||'-'||BKPF.BUKRS||'-'||BKPF.BELNR DOCID FROM BKPF
JOIN BSEG ON (BKPF.MANDT=BSEG.MANDT AND BKPF.BUKRS=BSEG.BUKRS AND BKPF.GJAHR=BSEG.GJAHR AND BKPF.BELNR=BSEG.BELNR)
WHERE
BSEG.SHKZG='H' AND (BSEG.XBILK IS NULL OR BSEG.XBILK='')
AND (BKPF.STBLG IS NULL OR BKPF.STBLG='')
AND EXISTS (SELECT * FROM BSEG B WHERE B.MANDT=BSEG.MANDT AND B.BUKRS=BSEG.BUKRS AND B.GJAHR=BSEG.GJAHR AND B.BELNR=BSEG.BELNR AND B.SHKZG='S' AND B.HKONT!=BSEG.HKONT AND (B.XBILK IS NULL OR B.XBILK=''))
AND NOT EXISTS (SELECT * FROM BSEG B WHERE B.MANDT=BSEG.MANDT AND B.BUKRS=BSEG.BUKRS AND B.GJAHR=BSEG.GJAHR AND B.BELNR=BSEG.BELNR AND B.SHKZG='S' AND B.HKONT=BSEG.HKONT)
AND NOT EXISTS (SELECT * FROM BSEG B WHERE B.MANDT=BSEG.MANDT AND B.BUKRS=BSEG.BUKRS AND B.GJAHR=BSEG.GJAHR AND B.BELNR=BSEG.BELNR AND B.SHKZG='S' AND B.XBILK='X')
AND NOT EXISTS (SELECT * FROM BSEG B WHERE B.MANDT=BSEG.MANDT AND B.BUKRS=BSEG.BUKRS AND B.GJAHR=BSEG.GJAHR AND B.BELNR=BSEG.BELNR AND B.SHKZG='H' AND B.XBILK='X')
)
GROUP BY BSEG.MANDT,BSEG.BUKRS,BSEG.GJAHR,BSEG.BELNR
) DOCS
GROUP BY DEBIT,CREDIT
ORDER BY COUNT(BELNR) DESC

 

The most frequently used account assignments are at the top. An example of a result would look something like this:

 

Debit Credit AMOUNT_BELNR
Rent RE Revenue from rent - own use 243
RE Revenue from operating costs flat rates, RE Rental - sales deduction (third party/own) RE Revenue from operating costs flat rates, RE Revenue from third-party usage rent 236
RE Imputed expense vacancy rent RE Imputed revenue from vacancy rent 217
Occupancy costs RE Revenue from rent - own use 205
Personnel expenditures Sales 102

 

Not all rows are necessarily critical. There can be perfectly legitimate account assignments where only expenses have been simply transferred.

You as the auditor must now go through all the lines and consider whether the "sales hairdresser" has been at work. To do this, you need to look for account assignments to revenue accounts in credit (CREDIT). If such lines exist, look at the offsetting account on the debit side and critically ask yourself the question how such postings could have been made.

 

Sounds too complicated?

Is all this technically too complicated for you? If so, then no problem – that after all is exactly why we have developed zap Audit. With zap Audit, data analysis becomes straightforward, leaving you to concentrate on evaluating the results. So feel free to contact us:

 Contact