Room for improvement in reconcilation algorithm

[sraps (Alistek)]

Reconcilation algorithm bears some dangerous problems. I will describe just a few of them... I am now working on my own version for this part.

1.case: payment connected to the wrong partner, because search by invoice number matches wrong payment and no distinction by partner is ever being performed:
in database invoice number is "7", but in payment there is number "AB2342777" defined...

2.case: Matches outgoing payments to outgoing invoices, because no distinction by movement type and account type is ever being done.

3.case: Matches several payments from several partners to a movement of single invoice, not looking the total of the invoice or partner.

4.case: when there are several invoices with the same total, algorithm is unable to decide, because the matching is not being done in multiple stages.

there are much more cases, the algorithm now is unusable.

[Pieter J. Kersten (EduSense BV) (pieterj)]

I'm afraid no matching algorithm isn't going to be 'full proof' to cover all possibilities. Even humans make mistakes. I can see your point, but there is a reason why all this was coded the way it is.

1. This covers two cases:
  a) The payer adds text to the reference number, say invoice was '2009.03' and payment says 'Factuur 2009.03'
  b) There is a payer is a different party than the one the invoice was sent to, which is quite common.

  This can be improved by requiring an exact match for b) and extending a) with a similarity match on name, although neither are very sound.
  Also it helps big time to make your invoice references longer. The shorter, the greater chance of false positives.

2. When the invoice was credited, this is a valid match. Same for incoming payments and incoming invoices.

3. This creates false positives, indeed. Can be enhanced by keeping track of matched invoices. I'll pick that up.

4. I'm not sure I understand your point. Amount matching is only performed when no exact match on other identifiers can be made. When there are multiple identical positives within this context, I can see no way to make a safe and valid choice. If you have some smart ideas, I welcome your input.

[sraps (Alistek) (erpsraps)]

I have made some alpha version of code, which works way better than the original, at least it does more or less correct matches. The original algorithm made matches 30% correct to 70% incorrect so it is unacceptable. I would better not have any match at all, if this is the only option.

Again will do the test here and report back.

[Pieter J. Kersten (EduSense BV) (pieterj)]

Hi sraps. I'm looking forward to your contribution on this. Can you tell me what's happening?

[Pieter J. Kersten (EduSense BV) (pieterj)]

Matching now improved, including partial and combined payments. Please check it out.