OK - here is the SQL. I hope that someone can see an error in the logic, because it is finding a lot of errors - though the one I checked looks real.  That is, I have a horrible feeling that we have a real problem.

select
    p.name as prodName, a.act_id as invoiceID, date(a.activity_start_time) as invDate,  a.status,
    cu.entity_id as custID, fa.quantity as qty,
    fa.fixed_amount as fPriceInvoice, fp.price as fPriceProduct, fp.name as fpName, fp.type as fpType,
    fa.unit_amount as uPriceInvoice,
    up.price as uPriceProduct,
    concat ((if (up.price <> fa.unit_amount,"UP??",""))," ",if (((fa.fixed_amount > 0) and isnull(fp.price)),"FP??","")) as error
from acts a
join act_relationships ar on a.act_id = ar.source_id and ar.arch_short_name = 'actRelationship.customerAccountInvoiceItem'
join acts ai on ai.act_id = ar.target_id
join financial_acts fa on fa.financial_act_id = ai.act_id
join participations pp on pp.act_id = ai.act_id and pp.arch_short_name = 'participation.product'
join entities p on p.entity_id = pp.entity_id
join product_prices up on up.product_id = p.entity_id and up.arch_short_name ='productPrice.unitPrice'
    and ((up.start_time is null or up.start_time <= a.activity_start_time) and (up.end_time is null or up.end_time > a.activity_start_time))
left join (
    select fp.price as price, p.entity_id as pid, fp.start_time, fp.end_time, fp.name, 'fp' as type
        from entities p
        join product_prices fp on fp.product_id = p.entity_id and fp.arch_short_name ='productPrice.fixedPrice'
        where p.arch_short_name in ('product.merchandise','product.medication','product.service')
    union
     select tp.price as price, p.entity_id as pid, tp.start_time, tp.end_time, tp.name, 'tp' as type
        from entities p
        join entity_relationships erl on erl.source_id = p.entity_id and erl.arch_short_name = 'entityRelationship.productLink'
        join entities pt on pt.entity_id = erl.target_id
        join product_prices tp on tp.product_id = pt.entity_id and tp.arch_short_name ='productPrice.fixedPrice'
        where p.arch_short_name in ('product.merchandise','product.medication','product.service')
    )as fp    on fp.pid = p.entity_id and fp.price = fa.fixed_amount
        and ((fp.start_time is null or fp.start_time <= a.activity_start_time) and (fp.end_time is null or fp.end_time > a.activity_start_time))
join participations pc on pc.act_id = a.act_id and pc.arch_short_name = 'participation.customer'
join entities cu on cu.entity_id = pc.entity_id
where a.arch_short_name = 'act.customerAccountChargesInvoice'
and a.activity_start_time > '2017-02-01'
and a.act_id = 4145211
and p.entity_id = 47216
and (up.price <> fa.unit_amount or (fa.fixed_amount > 0 and isnull(fp.price)))
order by prodName,invDate;

Note the extra bits of selection at the bottom to trim down to the data presented here, ie the date must be after 1Feb2017, I am looking at one invoice, and one product.

The logic of the above is:

- for the unit prices, just compare the invoice and product price

- for the fixed prices, generate a table of all fixed and Price Template prices, and left join to that matching on product, price and date - a null result means that the fixed price on the invoice was not in the set of allowed fixed prices for the product.

This finds:

So we have two errors in each of these line items - the unit price does not match (31 vs 26) and the fixed price on the invoice ($60) does not occur is the set for the product - which using the following SQL

select fp.price as price, p.entity_id as pid, fp.start_time, fp.end_time, fp.name, 'fp' as type
    from entities p
    join product_prices fp on fp.product_id = p.entity_id and fp.arch_short_name ='productPrice.fixedPrice'
    where p.arch_short_name in ('product.merchandise','product.medication','product.service')
    and p.entity_id = 47216
union
 select tp.price as price, p.entity_id as pid, tp.start_time, tp.end_time, tp.name, 'tp' as type
    from entities p
    join entity_relationships erl on erl.source_id = p.entity_id and erl.arch_short_name = 'entityRelationship.productLink'
    join entities pt on pt.entity_id = erl.target_id
    join product_prices tp on tp.product_id = pt.entity_id and tp.arch_short_name ='productPrice.fixedPrice'
    where p.arch_short_name in ('product.merchandise','product.medication','product.service')
        and p.entity_id = 47216;

are:

Here is the product:

so just a unit price.  Here is the linked item:

Here is the invoice:

So - I have a horrible feeling that Sam is correct - the system is grabbing the wrong prices somehow.

I also ran a query to print the pricing for the Acp 2 Injection since 1/1/2015 - it appears in 84 invoice line items and some 46 of these appear to be wrong - ie are not $50 fixed, $26 unit. The csv file (renamed to .txt) is attached.

If this analysis is correct, then it is not a 'new in 1.9' problem.

Regards, Tim G

An 'Invoice Price Check' report has been developed (see attached) which enables this situation to be investigated. Running this over the Hong Kong system has found no evidence of underlying systematic errors in the OpenVPMS code.

Where "errors" have been detected, investigation shows that these are due to two things:

1. staff changing the fixed and unit amounts on the invoice line item as it is entered/edited. [For example, where they want to charge a lower amount than the standard, rather than entering a discount amount, they simply over-write the fixed or unit amount with a lower value. We also found cases where the amounts had been increased because the staff knew that that the product price was wrong (was set to $80 - should have been $800).
2. Price changes being done without 'closing off' the old price and entering a new one with new applicability dates.

The attached report (with its US Letter version) will probably have more work done to it and at some point in time I will add it to the resource library.  However, it is useful for keeping an eye whether any pricing irregularities are occuring. Note that you will probably was to set the 'ignore differences under' numbers to somethuing larger than the $1.00 default - I have been using $200 (about $AU 34) to check the Hong Kong system - otherwise it will detect errors due to incorrectly done price changes.

Note that the report is for 1.9 systems - will not work correctly on 1.8 and earlier.

Regards, Tim G