Re: Reconciliation (workflow) -- observations and next steps

["Yves Lavoie, GaYLi" <..hidden..>]

On 2017-01-04 18:43, Erik Huelsmann wrote:

See my comments in the text.

> 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).
Currently, nothing prevent having multiple reports for the same date.

1. Create one report
2- quit without saving (close the window)
3- Come back and create it again. Transactions duplicated and balances
wrong.
>
> 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
> As per
> https://github.com/ledgersmb/LedgerSMB/blob/master/lib/LedgerSMB/DBObject/Reconciliation.pm#L380,
> the cleared balance is looked up exactly one month before the end date
> of the current report.
> 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).
6. There is no way currently to produce history reports with proper
cleared and outstanding at the moment of the report because we do not
keep status of when was the transactions reconciled. It also make
migration impossible because for every report submitted, we consider
transactions which are in the future for the balances.
> 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)?
It should revert the submit status.
> 2. Should we prevent transactions "allocated" to a reconciliation
> report from showing up as eligible on a work-in-progress
> reconciliation report?
Should we keep that cache? Could the cr_report_lines be generated each
time until a report gets submitted and would indexes be enough to
provide good performance?
> 3. Should we introduce hard constraints in the database that a single
> transaction can be reconciled only once?
I think that we must.
> 4. Should we remove the hard-coded month? (What should it be replaced
> with??)
It should come from the user defaults. We assume that bank statements
are monthly, but that ain't true everywhere.
> 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.)
I think we should. That cache has proven hard to maintain.
> Any further items to be discussed?
We currently do not record approval times. I think that we should. The
current mechanism may be correct, or correctable, but we prevent users
from being to dig into the database for other purposes and have complete
information. One of the LSMB strength over SL has been to have integrity
ensured at database level and the current mechanism relies on server code.

Yves

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most 
engaging tech sites, SlashDot.org! http://sdm.link/slashdot
_______________________________________________
Ledger-smb-devel mailing list
..hidden..
https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel