Rounding errors
More on practice stats. If I add up amounts running through the invoice etc line items, then the totals are different to those I get if I run through the invoice etc headers.
The following illustrates the differences:
My guess is that the differences are due to rounding in OpenVPMS. That is
sum(line item amounts) is not the same as sum(transaction amounts) which is in fact sum(rounded sum(line items amounts for each transaction)).
Note that the above data is for POSTED transactions.
Am I correct?
Regards, Tim G
Re: Rounding errors
You shouldn't get a difference between the sum of invoice items and invoices.
Item totals are rounded to 2 decimal places, before they are summed, using the expression:
<node name="total" path="/total" type="org.openvpms.component.business.domain.im.datatypes.quantity.Money" derived="true" derivedValue="math:roundAmount(number(/quantity > 0) * /fixedAmount - /details/discount + /quantity * /unitAmount)"> </node>You can verify items are rounded correctly using:
select * from financial_acts i join acts a on i.financial_act_id = a.act_id where i.total <> round(i.total, 2) and a.arch_short_name in ("act.customerAccountInvoiceItem", "act.customerAccountCounterItem", "act.customerAccountCreditItem") order by activity_start_time descThere should be no rows.
Make sure that the total of charge items corresponds to those of the charges. The following will list any charges that don't add up.
select fa.financial_act_id, fa.total, sum(fi.total), a.activity_start_time, a.arch_short_name from acts a join financial_acts fa on fa.financial_act_id = a.act_id join act_relationships r on a.act_id = r.source_id join acts i on r.target_id = i.act_id and i.arch_short_name in ("act.customerAccountInvoiceItem", "act.customerAccountCounterItem", "act.customerAccountCreditItem") join financial_acts fi on fi.financial_act_id = i.act_id where a.arch_short_name in ("act.customerAccountChargesInvoice", "act.customerAccountChargesCounter", "act.customerAccountChargesCredit") and a.status = "POSTED" group by fa.financial_act_id having fa.total <> sum(fi.total)Again there should be no rows.
Re: Rounding errors
Tim - thanks for the diagnostic SQL. The rounding check found nothing. However, the second found 14 errors as follows:
Now let us put aside the pre 6Jan 2014 stuff for the moment - because that was generated my conversion code and I used Kettle to load in the transaction headers and separately loaded the line items - so it is possible that I screwed up. Alternatively (and I suspect more likely) the RxWorks data was screwed.
However, taking the last three rows we have for Joey Yip:
I tweaked your sql to show the details and got:
So there is something very queer going on: a) the invoice has got a credit line item in it; b) its total is screwed up. Note also the line item start times - you can see invoice line 1 entered at 19:17 then the second at 19:37 - but when the reversal is done at 19:47, so is the credit line item added to the invoice.
Looking at the Laura Chat credit on 10 Jan we have:
As you see there is a major correction effort going one (this was 4 days after OpenVPMS went live).
Looking at the details of this credit we have:
So the credit has only one line item and that is $0 but the credit total is $2435
I suspect that a useful thing to do is to run the 'compare transaction amount to total of line items' sql query on another system. This will check whether it is just mine that is screwed up, or whether this bug is present in other systems.
Note that I have just run the query on our production system (because my development system was cloned off production about 6 months ago) and it shows no further errors.
Checking back, in Feb 2014 we were running a beta2 version of 1.7 so perhaps at that time there was an error in the reversal logic.
I think that my next step is to use some sql to fix the transaction amounts in my system so that the line item totals match the transaction total.
I am also fairly certain that this problem does not exist in the release 1.7 and 1.7.1 systems (but as I said, it would be interesting to run the sql check code on someone else's system).
Regards, Tim G
Re: Rounding errors
I ran the first code no errors
Second code
I havent had a chance to determine who they belong too.
Tim can you drop me a text version of your adjusted sql code to determine if its the same sort of error.
I would say this error would have been in the 1.6 era
Re: Rounding errors
This could be related to https://openvpms.atlassian.net/browse/OVPMS-1335
Re: Rounding errors
There may be something wrong with the invoice, but it doesn't have a credit item in it. In your output, it shows a relationship to an act.customerAccountChargesCredit.
You're probably picking up an actRelationship.customerAccountAllocation between the invoice and credit. Add r.arch_short_name to your select clause to verify.
At this stage, I can't see how you'd end up with mismatch amounts between the charge and its items. There is code that was added towards the end of 2011 to prevent against this, although it was primarily designed to detect migration problems. One mechanism would be if the charge editor had failed to construct, and the default editor was instead used, which doesn't perform the validation checks. This is extremely unlikely however. If this had occurred, I would expect it have occurred for all charges.
Re: Rounding errors
Tim A - you are correct - the 'extra' is an allocation.
Here is the 'show me all the details of act' sql:
select fa.financial_act_id as fa_id, fa.total as fa_total, fi.financial_act_id as fi_id,fi.total as fi_total, a.activity_start_time as a_start, a.arch_short_name as a_arch_sn, r.arch_short_name as ar_arch_sn, i.activity_start_time as i_start, i.arch_short_name as i_arch_sn, ec.entity_id as custID, ec.name from acts a left join financial_acts fa on fa.financial_act_id = a.act_id left join act_relationships r on a.act_id = r.source_id left join acts i on r.target_id = i.act_id /* and i.arch_short_name in ("act.customerAccountInvoiceItem", "act.customerAccountCounterItem", "act.customerAccountCreditItem") */ left join financial_acts fi on fi.financial_act_id = i.act_id left join participations pc on pc.act_id = a.act_id and pc.arch_short_name = 'participation.customer' left join entities ec on ec.entity_id = pc.entity_id where a.act_id = 2504408;Note that I commented out the i.arch_short_name selection (because I wanted to see everything) - but this was a mistake because I got the allocation without realising that it was an allocation.
Here is the output:
Ben - here is the 'find the problems' sql with my 'include customer' tweaks:
select fa.financial_act_id as fa_id, fa.total as fa_total, fi.financial_act_id as fi_id,sum(fi.total) as fi_total, a.activity_start_time as a_start, a.arch_short_name as a_arch_sn, r.arch_short_name as ar_arch_sn, i.activity_start_time as i_start, i.arch_short_name as i_arch_sn, ec.entity_id as custID, ec.name from acts a join financial_acts fa on fa.financial_act_id = a.act_id join act_relationships r on a.act_id = r.source_id join acts i on r.target_id = i.act_id and i.arch_short_name in ("act.customerAccountInvoiceItem", "act.customerAccountCounterItem", "act.customerAccountCreditItem") join financial_acts fi on fi.financial_act_id = i.act_id left join participations pc on pc.act_id = a.act_id and pc.arch_short_name = 'participation.customer' left join entities ec on ec.entity_id = pc.entity_id where a.arch_short_name in ("act.customerAccountChargesInvoice", "act.customerAccountChargesCounter", "act.customerAccountChargesCredit") and a.status = "POSTED" group by fa.financial_act_id having fa.total <> sum(fi.total) ;