Are your receivables in the right place in the balance sheet?

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:

1SELECT 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.

2FROM 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.

3GROUP 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:

4SELECT 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:

ACCOUNTACCOUNT_TITLEKOARTINTER-
COMPANY
EUINLANDPERSONENTRIES
140000Trade Receivables – domesticDNOYESYESNO685
140000Trade Receivables – domesticDNOYESYESYES327
141000Trade Receivable – foreignDNONONONO10
144006Receivables CC 2000DYESYESNONO2
196900IS-RE Advance payment receivable – operating costsDNOYESYESYES360
196910IS-RE Advance payment – operating costsDNOYESYESYES224
196920IS-RE Advance payment – sales-based rentDNOYESYESYES13
196930IS-RE Rent despositDNOYESYESYES3

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.

Artikel teilen

Facebook
Twitter
XING
LinkedIn