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