Reconciliation in 1.3

[John Locke <..hidden..>]

Hi,

My bookkeeper is making headway on reconciling in LSMB 1.3, and we've
hit a couple issues.

1. No payee on reconciliation detail

Direct feedback from my bookkeeper, that it can be quite difficult to
match up transactions to statements when there's no payee listed on the
reconciliation detail.

I think the real fix is to change the schema of the cr_report_line table
to include Payee, and change reconciliation__pending_transactions to
insert payee data into the report.

Since we're actively using this, rather than messing with cascade
deletes/re-inserts, I added a view to do the heavy lifting:

CREATE VIEW recon_payee AS
    SELECT n.name AS payee, rr.id, rr.report_id, rr.scn, rr.their_balance, 
       rr.our_balance, rr.errorcode, rr."user", rr.clear_time, rr.insert_time, 
       rr.trans_type, rr.post_date, rr.ledger_id, rr.voucher_id, 
       rr.overlook, rr.cleared 
    FROM (((cr_report_line rr 
      LEFT JOIN acc_trans ac ON ((rr.ledger_id = ac.entry_id))) 
      LEFT JOIN gl ON ((ac.trans_id = gl.id))) 
      LEFT JOIN ((SELECT ap.id, e.name 
           FROM ((ap JOIN entity_credit_account eca ON ((ap.entity_credit_account = eca.id))) 
           JOIN entity e ON ((eca.entity_id = e.id))) 
        UNION SELECT ar.id, e.name 
           FROM ((ar JOIN entity_credit_account eca ON ((ar.entity_credit_account = eca.id)))
           JOIN entity e ON ((eca.entity_id = e.id)))) 
        UNION SELECT gl.id, gl.description FROM gl) n ON ((n.id = ac.trans_id)));


... then created a reconciliation__pending_transactions_payee that
returned a row of this view instead of cr_report_line, and modified the
Perl code to use this function instead of the original.

Perhaps something along these lines can be integrated into the original
function?


2. Reconciliation of transactions that pay for multiple AP transactions
only show one.

e.g. Bookkeeper enters several AP transactions, with different dates and
different amounts. Then pays them all with one check. Only one of the AP
transactions appears in the reconciliation report.

That's my task for today -- figure out how to get all the appropriate
lines to show up in the reconciliation report. Chris, do you have a
quick fix for this?


oh, and here's one more issue:

3. Reconciliation of one account marks other side of transaction
reconciled. E.g. a payment from a cash account to a credit card account,
after being reconciled in the cash account, no longer shows up in
pending transactions to reconcile on the credit card account. Not sure
if this is an issue with imported data, or if this is a bug in 1.3.

Cheers,


-- 
John Locke
"Open Source Solutions for Small Business Problems"
published by Charles River Media, June 2004
Follow me on Twitter: http://twitter.com/freelock
http://www.freelock.com