picture of Prof. Dr. Nick Gehrke

written by

Prof. Dr. Nick Gehrke

Mid section of man calculating bills on mobile phone

Entering business transactions in the correct accounts is one of the basic requirements for proper accounting. In today's blog post, we will therefore consider the correct way of entering receivables in SAP. I will show you how you can check in SAP whether your receivables accounts contain similar business transactions or whether postings are being made to receivables accounts in an unsystematic way which can be a cause of quite some irritation. Because if receivables accounts are used in an incorrect way, in the worst case scenario, this can lead to misstatements on the balance sheet. And that’s always something auditors should be on the lookout for!

 

How are receivables accounts structured?

If you take a look at the usual charts of accounts, you will often find names of receivables accounts such as:

  • Domestic accounts receivable
  • Receivables from foreign customers
  • Receivables from affiliated companies

These examples show what the structuring features for receivables accounts are: namely domestic/international procurement and intercompany/associated group companies. If you think about it a little more, you can boil it down to the following set of characteristics, which are of relevance for the structuring of receivables accounts:

  • Customer located in national territory (YES/NO)
  • Debtor is based in the EU (YES/NO)
  • Customer is an affiliated company (YES/NO)
  • Customer is a private person (YES/NO)

Looking at these characteristics, you would expect it to be possible to clearly define the status of each receivables account with regard to each of these characteristics.

 

How do I find out in SAP whether the business transactions on my receivables accounts are homogeneous?

In what follows, I will explain how to put together an SQL query that shows how many line items with which characteristics were posted to the receivables accounts for each receivables account used. To do this, use the "DBACOCKPIT" transaction in SAP and navigate to the SQL Editor by choosing Diagnostics.

For each of the characteristics 1-4, the SQL query shows if the postings on the account apply to the respective characteristic:

 

1

SELECT HKONT ACCOUNT ,
SKAT.TXT50 ACCOUNT_TITLE, KOART,
BSEG.GJAHR, BSEG.MANDT, BSEG.BUKRS,
CASE WHEN BSEG.VBUND='' THEN 'NO' ELSE 'YES' END INTERCOMPANY,
CASE WHEN T005.XEGLD='X' THEN 'YES' ELSE 'NO' END EU,
CASE WHEN T001.LAND1=KNA1.LAND1 THEN 'YES'ELSE 'NO' END INLAND,
CASE WHEN KNA1.STKZN='X' THEN 'YES' ELSE 'NO'END PERSON,
COUNT (*) ENTRIES

 

First, we define the necessary fields for the final output of the SQL query as usual. In addition to the account number (ACCOUNT), the account description (ACCOUNT_TITLE) and some standard output, such as the client (MANDT), the company code (BUKRS) and the fiscal year (GJAHR), there are various CASE WHEN expressions. These only replace the respective values of the fields in the result, so that the result is easier to read.

 

2

FROM BSEG
LEFT JOIN T001 ON (BSEG.MANDT=T001.MANDT AND BSEG.BUKRS=T001.BUKRS)
LEFT JOIN SKAT ON (BSEG.MANDT=SKAT.MANDT AND T001.KTOPL=SKAT.KTOPL AND BSEG.HKONT=SKAT.SAKNR AND SKAT.SPRAS='D')
LEFT JOIN KNA1 ON (BSEG.MANDT=KNA1.MANDT AND BSEG.KUNNR=KNA1.KUNNR)
LEFT JOIN T005 ON (KNA1.MANDT=T005.MANDT AND KNA1.LAND1=T005.LAND1)
WHERE KOART='D'

 

The tables required for the analysis are then linked together using a LEFT JOIN to access the individual fields of the tables (T001, SKAT, KNA1, T005). After that, we limit ourselves to customers (KOART='D'), since these are to be receivables.

 

3

GROUP BY HKONT, KOART, BSEG.MANDT, BSEG.BUKRS, BSEG.GJAHR
CASE WHEN BSEG.VBUND='' THEN 'NO' ELSE 'YES' END,
CASE WHEN T005.XEGLD='X' THEN 'YES' ELSE 'NO' END ,
CASE WHEN T001.LAND1=KNA1.LAND1 THEN 'YES' ELSE 'NO' END,
CASE WHEN KNA1.STKZN='X' THEN 'YES' ELSE 'NO' END,
SKAT.TXT50
ORDER BY HKONT,
COUNT(*) DESC

 

Finally, the results are grouped together, where all values are the same. These groupings are then sorted by account number (ORDER BY HKONT). For the same account number, different specifications and different numbers of documents, the system also sorts in descending order by the number of documents (COUNT(*) DESC).

 

The complete SQL query then looks like this:

 

4

SELECT HKONT ACCOUNT,
SKAT.TXT50 ACCOUNT_TITLE,
KOART, BSEG.GJAHR,
BSEG.MANDT,
BSEG.BUKRS,
CASE WHEN BSEG.VBUND='' THEN 'NO' ELSE 'YES' END INTERCOMPANY,
CASE WHEN T005.XEGLD='X' THEN 'YES'ELSE 'NO' END EU,
CASE WHEN T001.LAND1=KNA1.LAND1 THEN 'YES'ELSE 'NO' END INLAND,
CASE WHEN KNA1.STKZN='X'THEN 'YES'ELSE 'NO'END PERSON,
COUNT (*) ENTRIES

FROM BSEG LEFT JOIN T001 ON (BSEG.MANDT=T001.MANDT AND BSEG.BUKRS=T001.BUKRS)
LEFT JOIN SKAT ON (BSEG.MANDT=SKAT.MANDT AND T001.KTOPL=SKAT.KTOPL AND BSEG.HKONT=SKAT.SAKNR AND SKAT.SPRAS='D')
LEFT JOIN KNA1 ON (BSEG.MANDT=KNA1.MANDT AND BSEG.KUNNR=KNA1.KUNNR)
LEFT JOIN T005 ON (KNA1.MANDT=T005.MANDT AND KNA1.LAND1=T005.LAND1)

WHERE KOART='D'
GROUP BY HKONT,
KOART,
BSEG.MANDT,
BSEG.BUKRS,
BSEG.GJAHR,
CASE WHEN BSEG.VBUND='' THEN 'NO' ELSE 'YES' END ,
CASE WHEN T005.XEGLD='X' THEN 'YES' ELSE 'NO' END ,
CASE WHEN T001.LAND1=KNA1.LAND1 THEN 'YES' ELSE 'NO' END,
CASE WHEN KNA1.STKZN='X' THEN 'YES' ELSE 'NO' END,
SKAT.TXT50
ORDER BY HKONT, COUNT(*) DESC

 

The SQL query returns at least one result for each receivables account used. If several entries for a receivables account exist, this means that characteristics 1-4 of the postings to the receivables account are not clearly defined in all cases. It could then be the case that the receivables account was used for non-uniform business transactions.

 

An example of a possible result can be found in the following table:

 

ACCOUNT ACCOUNT_TITLE KOART INTER-
COMPANY
EU INLAND PERSON ENTRIES
140000 Trade Receivables - domestic D NO YES YES NO 685
140000 Trade Receivables - domestic D NO YES YES YES 327
141000 Trade Receivable - foreign D NO NO NO NO 10
144006 Receivables CC 2000 D YES YES NO NO 2
196900 IS-RE Advance payment receivable - operating costs D NO YES YES YES 360
196910 IS-RE Advance payment - operating costs D NO YES YES YES 224
196920 IS-RE Advance payment - sales-based rent D NO YES YES YES 13
196930 IS-RE Rent desposit D NO YES YES YES 3

 

You can see that all receivables accounts only occur once, except for account 140000 "Domestic receivables", which occurs twice. You should look at all accounts that occur more than once. In the case of account 140000, you can see that receivables from business customers and private customers were recorded there. In such a case it would be worth asking whether this is actually an instance of unsystematic posting or whether it is a matter of the characteristic actually being different in this case.

 

Too complicated?

We have already integrated an evaluation for checking receivables accounts in zap Audit. So if you need to run an automatic check on your data, just give zap Audit a try. zap Audit is free for smaller amount of company codes:

 

Pricing

 

Comments