Bug 20150 - Acquisitions wizard falsely uses list price for calculating amounts spent
Summary: Acquisitions wizard falsely uses list price for calculating amounts spent
Status: Failed QA
Alias: None
Product: Koha
Classification: Unclassified
Component: Reports (show other bugs)
Version: Main
Hardware: All All
: P5 - low major with 31 votes (vote)
Assignee: Jonathan Druart
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2018-02-07 13:03 UTC by Katrin Fischer
Modified: 2020-12-21 12:59 UTC (History)
3 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:


Attachments
Bug 20150: Try to fix Acquisitions wizard (1.62 KB, patch)
2020-08-12 13:24 UTC, Jonathan Druart
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Katrin Fischer 2018-02-07 13:03:18 UTC
When using the acquisitions wizard, you can choose between different options:

- Amount:
- Ordered amount:
- Spent amount:

The calculations are always done with listprice. Especially for the amount spent this makes the wizard not match up with the amounts on the acquisition homepage as the actual amount spent can be quite different from the list price used when ordering. 

I think it probably should be calculated like this:

- Amount: list price of ordered items + actual price of received items
- Ordered amount: list price
- Spent amount: actual price
Comment 1 Maura Stephens 2020-01-15 08:22:58 UTC
Katrin,
It is vital that this bug is resolved asap, and those running end of year stats need to be aware of it. Acquisitions financial data integrity is key. I will have had issued statistics for 2017, 2018 and 2019 without being aware of this problem. It is by pure chance that I noticed something for 2019 that brought this bug to my attention.
Comment 2 Katrin Fischer 2020-01-15 08:41:35 UTC
Hi Maura, I'd love to see this fixed, but probably won't have the time tow ork on it myself. I reported this, as one of our libraries brought it to my attention - but it didn't get any attention so far. I am updating the severity a bit.
Comment 3 Maura Stephens 2020-01-15 09:11:11 UTC
Thanks Katrin,
I've emailed some colleagues in the hope of getting a few more votes also. This is quite major for our libraries as we only use Koha Acquisitions for our full budget, and rely completely on it for accurate stats.
Comment 4 Jonathan Druart 2020-08-12 13:24:18 UTC
Created attachment 108105 [details] [review]
Bug 20150: Try to fix Acquisitions wizard

It is always using listprice when we need to do the same math as the
acq home page.
This patch is reusing the queries from GetBudgetHierarchy
Comment 5 Jonathan Druart 2020-08-12 13:25:15 UTC
Hi Maura, this report script looks quite obsolete as it has not been updated for years.
I tried to fix the different amount to match the ones we have on the acquisiton home page. Can you give it a try please?
Comment 6 Maura Stephens 2020-08-14 09:31:53 UTC
Hi Jonathan,

Firstly on Koha Acq none of the price fields in an order record field are called 'list price' there are 'vendor price' and 'retail price' so I had to open a call with Interleaf to work out which one this is. This make it confusing for End of Year Stats

I've run the Wizard reports in question again but as I had to do some investigations when I was trying to figure out what was happening here I guess it will be end 2021 when I get true figures.

I've said this before to Interleaf that Annual Acq stats should be solely based on encumbrances and expenditure per annum. The reopening of baskets skewing stats is not a good feature when you have to make annual reports to Finance departments.

Thanks for looking at it.
Comment 7 Katrin Fischer 2020-09-16 06:38:15 UTC
(In reply to Maura Stephens from comment #6)
> Firstly on Koha Acq none of the price fields in an order record field are
> called 'list price' there are 'vendor price' and 'retail price' so I had to
> open a call with Interleaf to work out which one this is. This make it
> confusing for End of Year Stats

Hi Maura,
we need to do some more work to make the terminology match on all pages. :( Maybe I can help (or confuse even more)

list price = vendor price (list_price in db) (first price field entered on an order)
Retail price = (rrp in db) (price in active currency calculated from list price)
Budgeted cost = (ecost in db) estimated price with discount etc. that the library will have to pay
Actual cost = (unit_price in db) entered on receive 

I'll try to verify Jonathan's changes here - on first glance it does look ok.
Comment 8 Katrin Fischer 2020-09-16 06:45:36 UTC
Hi Jonathan,

the calculation for "Amount" is not right:

+    } elsif ( $process == 3 ) { # Amount
+        $strcalc .= q|SUM(| . C4::Acquisition::get_rounding_sql(q|ecost_tax_included|) . q| * quantity ) + SUM(| . C4::Acquisition::get_rounding_sql(q|COALESCE(unitprice_tax_included, ecost_tax_included)|) . q| * quantity ) AS budget_total |;


I see about double of the value I should be seening. I think this is because in case of a received order, we do add up ecost + unit_price here for the same order line.