Hi all,
On various occasions and through different channels, I've had discussions about our current reconciliation functionality in order to be able to boil down to the following issues/differences in use-cases:
1. Nothing prevents one transaction from ending up in 2 recon reports
2. Only after approval of the previous recon is it possible to (meaningfully) start a new recon
3. During SL30 migration, the number of lines in the cr_report_lines table grows exponentially in the number of months/reconciliations migrated
4. Reconciliations can't span more than a single month (hard-coded)
5. When a reconciliation gets deleted, the 'reconciled' status in the line items doesn't get reset
A little terminology: an "eligible" transaction is a transaction shown on the report as available for reconciliation.
1. Ending up in 2 recon reports
Since the list of eligible transactions on a report includes all transactions for which reconciliation has not been approved and which have occurred before the end-date of the report, consider the following scenario:
1. create two reconcilation reports, with end-dates 1 day apart
2. select all transactions in one report and submit
3. go to the other report and select all transactions and submit
With this scenario, both reports can be approved, but all (common) transactions are in both reports.
It would seem to me that once a report has been submitted for approval, should the transactions in it not be shown as 'eligible' on newer reports anymore (until the report gets deleted/rejected).
2. Next recon after approval only
Only after a recon report has been approved, does the "approved balance" get updated. This means that if multiple consecutive reports get submitted before the previous one gets approved, do the second and further submitted report show an incorrect balance when approving them one by one in time-order.
The use-case that I see here (and John actually mentioned having the desire to use it) is to have an employee go over the bank statements for 6 months and submit the results to the boss/owner of the business for review and approval. [Alternatively, the scenario here could be 4 weeks with weekly bank statements.]
The current way things work, the scenario above won't work due to the starting balance calculation and the listing of eligible transactions.
Talking about it with John, we both thought it makes sense to support this workflow, requiring reports to be submitted in date-order and being approved in date-order -- but without requiring the approval before the next report is started.
Talking to Chris he voiced the concern that allowing that behaviour "banks" on future approval. Going over that with John, it would seem that -indeed- we'd need to reject all "future" reconciliations (returning them to "Saved" state) when rejecting a submitted reconciliation.
Neither of us expects this functionality to be triggered regularly though, because we expect people not to submit before being sure that the reconciliation actually is correct.
3. Exponential growth during SL30 reconciliation
Due to the fact that the current SL30 migration does not auto-approve the migrated reconciliations, each reconciliation report contains the entire transaction history of the account before the reconciliation date.
This is caused by the fact that (as I asked Chris: for records keeping) all transactions visible at the time of submitting the reconciliation, will be retained in the report for later exact reproduction of state.
4. Hard-coded 1-month maximum reconciliation period
It would seem that 1-week or 1-year reconciliation periods should be possible too (e.g. a company which has a savings account with monthly interest payments and without any further transactions would likely get a single 1-year statement...)
5. Reconciliation deleted, but reconciled/cleared status retained?
Yves pointed this one out to me. My thinking is that the cleared/reconciled status should probably come from the fact that a transaction line is in an (approved) recon report; the status itself should probably *not* be in the transaction line, to prevent this kind of "cache problems" (derived data getting out of sync).
Next steps I'd like to discuss here, addressing the items above are:
1. Should rejection of 1 recon report mean the rejection of all follow-up posted reports (returning the current and all follow-ups to "Saved" status)?
2. Should we prevent transactions "allocated" to a reconciliation report from showing up as eligible on a work-in-progress reconciliation report?
3. Should we introduce hard constraints in the database that a single transaction can be reconciled only once?
4. Should we remove the hard-coded month? (What should it be replaced with??)
5. Should we stop adding "unreconciled" transactions to transaction reports? (there are other options to record data allowing us to determine what the user will have seen when submitting the report.)
Any further items to be discussed?
--
Bye,
Erik.
Robust and Flexible. No vendor lock-in.