Can some new views and reports be added to Compiere to assist with the reconciliation of AP, AR, Payment Selection, Unallocated Payment and Bank-in-Transit accounts.
Using bank-in-transit reconciliation as an example.
Each Payment accounting fact record id will be the record ID of the Payment Document. Each Bank Statement Line accounting fact reconciling that Payment will have a link to the Payment_ID in the AllocationLine. The sum of the AcctAmt for a payment that is reconciled in the bank-in-transit account will be zero since there will be equal debits and credits.
In a view similar to RV_Fact_Acct but with the bare minimum of columns add this new column called (say)the Payment_Rec_ID. It could be populated with the Payment_ID by a CASE statement).
An accounting fact details type report for a Bank-in-Transit account from the new view which
...includes all trx with a Table of Payment or Allocation and;
...excludes all accounting facts where the sum of accounted amount, grouped by Payment_Rec_ID, is zero in the selected date range;
will list the GL transactions for only those Payments that have not gone through the bank statement process and cleared to zero.. ie a bank reconciliation report to any selected end date.
[This will only work as described for users who enter all entries that affect the bank as Payments, you may need to expand the view to pick up cash book or direct bank statement line entries].
The same approach described above but with different 'reconcile' columns should be able to produce:
.. an AR/AP report showing which BP and invoices and part payments made up the GL balance of that AP/AR Control account at any date in the past. This would use use the invoice_id in invoice transactions and the invoice_id from the allocation line in Allocation trx as the reconcile column.
.. a report of the uncleared items in any Payment Selection or Unallocated Payment account, also at any date in the past. This would use use the payment_id in Payment trx and the payment_id from the allocation line in Allocation trx as the reconcile column.
Log in to post a comment.