Bank statement reconciliation report

[jiri_medved]

Hello Experts,

I need to to create a bank statement analysis in SAP (ECC6). The requirement is following:

1. Select all movements on a bank GL account for a date range
2. Find the clearing postings
3. Recursively trace back the subsequent clearing untila P&L or uncleared posting is found.

The report should consinst of the list of records from step 3.

Example:

Paid creditor invoice

1. Bank statement Debit Bank payment clearing account / Credit Bank acount
2. Payment proposal Debit Creditor account / Credit Bank payment clearing acount
3. Creditor invoice Debit Cost P&L account(s), VAT Account / Credit Creditor account

Received payment for debtor invoice

1. Bank statement Debit Bank Account / Credit Debtor payment clearing accoutn
2. Debtor clearing Debit Debtor clearing account / Credit Debtor account
3. Debtor invoice Debit Debtor Account / Credit P&L Revenue account(s) + VAT Account

Unfortunately I was not able to find any standard report like that so I tried developing a custom one.

I can get all the bank statements by bank GL code from table `BSIS` on fields `HKONT` and `BLDAT`.

Then the clearing document (`AUGBL` and `AUGDT`) postings from table `BSAS` using `BELNR`, `GJAHR` and `BUZEI` from the previous query.

Then the invoice document (`BELNR`, `GJAHR` and `BUZEI`) again from BSAS using `AUGBL` and `AUGDT` from previous query (excluding the clearing document).

Finally the P&L and VAT accunts on the invoice from table BSEG using `BELNR`, `GJAHR` and `BUZEI` (excluding the debtor account).

My code work fine with simple scenarios like above. But if there are more complex scenarios with downpayment, credit cards, bank fees etc. I always got some records missing or end up in an endless loop.

By looking into the table `BSEG` I have found two more fields `AUGGJ` and `AGZEI` that somehow relates the lines of the cleared documents. But I was not able to identify the logic.

For example I have following records in the `BSEG` table:

    +------+----------+-----+----------+----------+-----+-----+-----+-----+---------+--------+
    | GJAHR|  BELNR   |ZUONR|  AUGDT   |  AUGBL   |KOART|AUGGJ|AGZEI|SHKZG|  DBRTR  | WRBTR  |
    +------+----------+-----+----------+----------+-----+-----+-----+-----+---------+--------+
    |  2015| 200000383|   1 |07.01.2015| 200000383| S   | 2015|   1 | H   |37.928,55|1.363,89|
    |  2015| 200000383|   2 |07.01.2015| 200000383| S   | 2015|   4 | S   |37.670,64|1.363,89|
    |  2015| 200000383|   3 |          |          | S   |     |   0 | S   |   148,80|    0,00|
    |  2015| 200000383|   4 |          |          | S   |     |   0 | S   |   109,11|    0,00|
    |  2015|3100000583|   1 |07.01.2015| 200000383| S   | 2015|   1 | S   |51.522,00|1.860,00|
    |  2015|3100000583|   2 |          |          | S   |     |   0 | H   |51.522,00|1.860,00|
    |  2015|3100000585|   1 |07.01.2015| 200000383| S   | 2015|   1 | S   |37.779,75|1.363,89|
    |  2015|3100000585|   2 |          |          | S   |     |   0 | H   |37.779,75|1.363,89|
    |  2015|6000000227|   1 |07.01.2015|6000000227| K   | 2015|   2 | S   |51.373,20|1.860,00|
    |  2015|6000000227|   2 |07.01.2015| 200000383| S   | 2015|   1 | H   |51.373,20|1.860,00|
    |  2015|6000000228|   1 |07.01.2015|6000000228| K   | 2015|   2 | S   |37.670,64|1.363,89|
    |  2015|6000000228|   2 |07.01.2015| 200000383| S   | 2015|   4 | H   |37.670,64|1.363,89|
    +------+----------+-----+----------+----------+-----+-----+-----+-----+---------+--------+

and following content in `BSAS` table:

    +------------+-----------+-------+------------+-------+-------+-----------+----------+
    |   AUGDT    |   AUGBL   | GJAHR |   BELNR    | ZUONR | SHKZG |   DMBTR   |  WRBTR   |
    +------------+-----------+-------+------------+-------+-------+-----------+----------+
    | 07.01.2015 | 200000383 |  2015 |  200000383 |     1 | H     | 37.928,55 | 1.363,89 |
    | 07.01.2015 | 200000383 |  2015 |  200000383 |     2 | S     | 37.670,64 | 1.363,89 |
    | 07.01.2015 | 200000383 |  2015 | 3100000583 |     1 | S     | 51.522,00 | 1.860,00 |
    | 07.01.2015 | 200000383 |  2015 | 3100000585 |     1 | S     | 37.779,75 | 1.363,89 |
    | 07.01.2015 | 200000383 |  2015 | 6000000227 |     2 | H     | 51.373,20 | 1.860,00 |
    | 07.01.2015 | 200000383 |  2015 | 6000000228 |     2 | H     | 37.670,64 | 1.363,89 |
    +------------+-----------+-------+------------+-------+-------+-----------+----------+

(sorry for the table formating - it looks OK in the preview but not after posting - it can be viewed by copying and pasting to Notepad)

The line `2015/3100000585/2` (`GJAHR/BELNR/BUZEI`) is the bank statement posting. The target postings are `2015/6000000227/1` - automatic payment and `2015/200000383/3' - exchange rate difference.

I know the target posting by knowing the accounting logic and by comparing DMBTR and WRBTR only. But I was not able to discover the join logic in the tables and how to follow it programmaticaly.

If I use the simple algorithm described earlier I get `2015/3100000583/1` from `BSEG` offseting the bank statement. Then `AUGBL` `200000383` from the same line in `BSEG`. In table `BSAS` with key `AUGDT` and `AUGBL` excluding the original line `2015/3100000583/1` I get all the lines in the second table above - i.e. 5 records including `2015/3100000585/1` and `2015/6000000228/2` that realates to a different payment. I can try excluding the posting `2015/60000002282/2` somehow as it has a different `AGZEI` = `4` and not `1`. But how to exclude `2015/200000383/4` while keeping `2015/200000383/3` as `AGZEI` for both is `0`?

Can anyone help?

Regards

Jiri