[Bug 23981] New: Inconsistencies with the different prices
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23981 Bug ID: 23981 Summary: Inconsistencies with the different prices Change sponsored?: --- Product: Koha Version: master Hardware: All OS: All Status: NEW Severity: enhancement Priority: P5 - low Component: Acquisitions Assignee: koha-bugs@lists.koha-community.org Reporter: caroline.cyr-la-rose@inlibro.com QA Contact: testopia@bugs.koha-community.org I was investigating bug 18639 and I came across some inconsistencies in the prices, where they are used, the calculations, etc. To reproduce : (Note: my vendor setting is "tax excluded" for both list and invoice price, I tried with different settings and as far as I can tell, the result is the same) 1- Create a new basket 2- Add an order 3- In the financial details, enter the following informations (I know it doesn't make sense IRL, but we are entering different prices just to see what goes where): - Vendor price: 10.99 - Tax rate: 0% - Discount: 0% - Retail price: 12.99 - Replacement cost: 15.99 - Actual cost: 18.99 4- Note that Budgeted cost and Total are based on *vendor* price (10.99) 5- Save your order 6- If you have access to your database, check the info that is entered (if you don't have access to your database, you can do an SQL report with the following query SELECT * from aqorders where basketno = X; Substitute de X for your basket number My db shows the following: listprice: 10.990000 unitprice: 18.990000 rrp: 12.99 replacementprice: 15.990000 rrp_tax_excluded: 12.990000 rrp_tax_included: 12.990000 ecost: 10.99 ecost_tax_excluded: 12.990000 ecost_tax_included: 12.990000 Note that the ecost_tax_excluded and ecost_tax_included are not based on ecost, but on rrp. 7- Go back to acquisitions home page and click on the amount ordered in your budget 8- Find your order and note that the estimated cost per unit is the retail price (12.99), not the vendor price (10.99) and not the budgeted cost as calculated during the order (10.99). 9- Close the basket 10- Receive the order 11- On the receipt summary page (which lists all the items yet to be received), the unit cost is the retail price (12.99) and the replacement cost is correct 12- On the item receiving page, the retail price is correct, the replacement price is correct, the budgeted cost is the retail price not the vendor price (listprice) nor the budgeted cost calculated during the order (ecost), the actual price is correct. 13- In the already received page, the prices are correct 14- In the "Spent column" of the budget, the price taken out of the fund is the actual price, which is correct. Summary: - The vendor price/listprice is not used anywhere else than during the first step of the order - The budgeted cost (ecost) is based on the retail price, the order form needs to reflect that Propositions: - Unless there is a good reason to keep it (like someone wanting to discriminate between how much the vendor sells it vs how much another vendor might sell it), I would remove the vendor price completely and replace it with the retail price - Correct the order form to show the real estimated price, based on the retail price - Correct the db calculations to make sure the ecost is based on the rrp and that the tax are calculated on the ecost I'm sorry if this is all over the place, hopefully by following the steps, you will see what I mean. -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23981 Caroline Cyr La Rose <caroline.cyr-la-rose@inlibro.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |katrin.fischer@bsz-bw.de, | |martin.renvoize@ptfs-europe | |.com, | |nick@bywatersolutions.com -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23981 Caroline Cyr La Rose <caroline.cyr-la-rose@inlibro.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |caroline.cyr-la-rose@inlibr | |o.com -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23981 --- Comment #1 from Caroline Cyr La Rose <caroline.cyr-la-rose@inlibro.com> --- Another thing I noticed, when importing files into a basket, the fields are simply labeled "Price" and "Replacement price". Price is copied into every db field that is not replacementprice (rrp, ecost, listprice, unitprice, etc), so there is no risk of confusion here. However, we might want to relabel it Retail price? -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23981 --- Comment #2 from Caroline Cyr La Rose <caroline.cyr-la-rose@inlibro.com> --- Created attachment 95118 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=95118&action=edit Screenshot showing the price fields Sorry again, just a minor thing. I noticed there is not prefilled "0.00" in the retail price field as there is with other price fields. See attached screenshot -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23981 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Severity|enhancement |normal -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23981 Marjorie Barry-Vila <marjorie.barry-vila@collecto.ca> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |marjorie.barry-vila@collect | |o.ca -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23981 Séverine Queune <severine.queune@bulac.fr> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |severine.queune@bulac.fr -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23981 Caroline Cyr La Rose <caroline.cyr-la-rose@inlibro.com> changed: What |Removed |Added ---------------------------------------------------------------------------- See Also| |https://bugs.koha-community | |.org/bugzilla3/show_bug.cgi | |?id=5062 -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23981 --- Comment #3 from Caroline Cyr La Rose <caroline.cyr-la-rose@inlibro.com> --- I tested this again and it's still valid. The most annoying part is the ecost_tax_* is based on rrp rather than ecost. This means that the "budgeted cost" changes in various pages. Just from observations in the interface (i.e. without looking in the code), this is what is used where: When placing a new order (empty) (neworderempty.pl) - Vendor price = listprice (10.99) - Actual cost = uniprice (18.99) - Retail price = rrp (12.99) - Replacement cost = replacementprice (15.99) - Budgeted cost = ecost (10.99) ** When viewing the basket (basket.pl) - Actual cost tax exc. = unit_price_tax_excluded (18.99) - Replacement price = replacementprice (15.99) - RRP tax exc. = rrp_tax_excluded (12.99) - Budgeted cost tax exc. = ecost_tax_excluded (12.99) ** - Budgeted cost tax inc. = ecost_tax_included (12.99) ** When viewing ordered funds (ordered.pl) - Estimated cost per unit = rrp (12.99) ** When receiving the order (parcel.pl) - Unit cost = listprice (10.99) - Replacement price = replacementprice (15.99) - Order cost = ecost (10.99) ** When receiving the order (orderreceive.pl) - Actual cost = uniprice (18.99) - Retail price = rrp (12.99) - Replacement price = replacementprice (15.99) - Budgeted cost = ecost_tex_excluded (12.99) ** When viewing the invoice (invoice.pl) - Actual cost tax exc. = unitprice_tax_excluded (18.99) - Replacement price = replacementprice (15.99) When viewing spent funds (spent.pl) - Unit price = unitprice (18.99) -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23981 --- Comment #4 from Caroline Cyr La Rose <caroline.cyr-la-rose@inlibro.com> --- I made a table with the various terms used where : https://snipboard.io/wLaNtq.jpg -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23981 Thibaud Guillot <thibaud.guillot@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- See Also| |https://bugs.koha-community | |.org/bugzilla3/show_bug.cgi | |?id=35114 -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23981 Mark Hofstetter <koha@trust-box.at> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |koha@trust-box.at --- Comment #5 from Mark Hofstetter <koha@trust-box.at> --- hi, I am not sure if our customer/we have the same problem but it seems to be related - I just tested it with 23.06.00.079, just to clarify things for me if I am looking the right place(s): IMHO the price should be deducted from fund WITHOUT the tax (but it is with the the tax) 0) create a vendor List prices: Include tax Invoice prices: Don't include tax 1) placing an order: cgi-bin/koha/acqui/neworderempty.pl?booksellerid=3&basketno=4 Vendor price: 10 (tax inclusive) Tax rate: 10% Discount: 13% Retail price: 10.00 (adjusted for USD, tax inclusive) Replacement cost: 10.00 Budgeted cost: 8.70 (tax inclusive) Total: 8.70 (budgeted cost * quantity) Actual cost: 0.00 ==> 10 * 0.87 2) cgi-bin/koha/acqui/basket.pl?basketno=4 RRP => 9.09 (10 / 1.1) Price - Tax ie Budgeted cost tax ex => 7.91 ( 9.09 * 0.87 (13% Discount)) total ex tax => 7.91 GST => 0.79 (7,91 * 0.1 (10% Tax)) 3) Close Basket => Yes 4) cgi-bin/koha/acqui/booksellers.pl?booksellerid=3 Click receive Shipment fill in: Vendor invoice: 123345 (some random number) add no shippment costs 5) mark create book and click "Receive selected(1)" 6) mark item as received Actual cost: 7.91 (tax exclusive) 7) Total tax exc. 7.91 Total (GST 10%) 0.79 Total tax inc. 8.70 there should be no tax because the vendor is tax excluded 8) Secondary fund Secondary fund 1,000.00 0.00 0.00 8.70 8.70 991.30 991.30 the full amount is deducted even if you are tax exempt -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23981 --- Comment #6 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- (In reply to Mark Hofstetter from comment #5)
hi,
I am not sure if our customer/we have the same problem but it seems to be related - I just tested it with 23.06.00.079, just to clarify things for me if I am looking the right place(s):
IMHO the price should be deducted from fund WITHOUT the tax (but it is with the the tax)
I believe with tax is standard as most libraries have to pay it and you need the funds to reflect that. But there is CalculateFundValuesIncludingTax in 23.11 to change it.
0) create a vendor List prices: Include tax Invoice prices: Don't include tax
1) placing an order: cgi-bin/koha/acqui/neworderempty.pl?booksellerid=3&basketno=4
Vendor price: 10 (tax inclusive) Tax rate: 10% Discount: 13% Retail price: 10.00 (adjusted for USD, tax inclusive) Replacement cost: 10.00 Budgeted cost: 8.70 (tax inclusive) Total: 8.70 (budgeted cost * quantity) Actual cost: 0.00
==> 10 * 0.87
2) cgi-bin/koha/acqui/basket.pl?basketno=4 RRP => 9.09 (10 / 1.1) Price - Tax ie Budgeted cost tax ex => 7.91 ( 9.09 * 0.87 (13% Discount)) total ex tax => 7.91 GST => 0.79 (7,91 * 0.1 (10% Tax))
3) Close Basket => Yes
4) cgi-bin/koha/acqui/booksellers.pl?booksellerid=3 Click receive Shipment
fill in: Vendor invoice: 123345 (some random number) add no shippment costs
5) mark create book and click "Receive selected(1)"
6) mark item as received Actual cost: 7.91 (tax exclusive)
7) Total tax exc. 7.91 Total (GST 10%) 0.79 Total tax inc. 8.70
there should be no tax because the vendor is tax excluded
The vendor is not tax excluded, the setting means: the price is entered with or without tax. If you set a tax, it will still be calculated. You would need to set the tax percentage to 0 if you don't want tax.
8) Secondary fund Secondary fund 1,000.00 0.00 0.00 8.70 8.70 991.30 991.30
the full amount is deducted even if you are tax exempt
There is no setting for tax exempt. -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23981 Thibaud Guillot <thibaud.guillot@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |thibaud.guillot@biblibre.co | |m --- Comment #7 from Thibaud Guillot <thibaud.guillot@biblibre.com> --- Hello You may wish to consult bug 35114, which I've opened. It's about price differences and miscalculations on baskets;) -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23981 Caroline Cyr La Rose <caroline.cyr-la-rose@inlibro.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |philippe.blouin@inlibro.com -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23981 Michaela Sieber <michaela.sieber@kit.edu> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |clemens.tubach@kit.edu, | |michaela.sieber@kit.edu -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23981 Matt Blenkinsop <matt.blenkinsop@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|NEW |Needs Signoff -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23981 --- Comment #8 from Matt Blenkinsop <matt.blenkinsop@openfifth.co.uk> --- Created attachment 198954 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=198954&action=edit Bug 23981: Use ecost instead of rrp to calculate ecost fields Test plan: 1) Create a new basket for the vendor above. 2) Add a new order (empty) and enter the following in the financial details: Vendor price: 10.99, Tax rate: 0%, Discount: 0%, Retail price: 12.99, Replacement cost: 15.99, Actual cost: 18.99. 3) Confirm the Budgeted cost shown on the order form is 10.99 (vendor price), not 12.99 (retail price). 4) Save the order. 5) Check the database using SELECT ecost, ecost_tax_excluded, ecost_tax_included FROM aqorders WHERE basketno = X (substitute your basket number). Verify ecost_tax_excluded and ecost_tax_included are both 10.99, not 12.99. 6) From the acquisitions home page, click the Ordered amount for your fund. Verify the Estimated cost per unit column shows 10.99, not 12.99. 7) Close the basket. 8) Receive a shipment. On the parcel page (list of items to receive), verify the Order cost column shows 10.99. 9) Click to receive the order. On the order receive page, verify the Budgeted cost field shows 10.99, not 12.99. 10) Complete receipt. Verify the fund's Spent column deducts the actual cost (18.99), not the retail price. 11) prove t/db_dependent/Acquisition/populate_order_with_prices.t -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23981 Matt Blenkinsop <matt.blenkinsop@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- Assignee|koha-bugs@lists.koha-commun |matt.blenkinsop@openfifth.c |ity.org |o.uk CC| |matt.blenkinsop@openfifth.c | |o.uk -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23981 Andrew Fuerste-Henry <andrew@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |andrew@bywatersolutions.com -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23981 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23981 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #198954|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23981 --- Comment #9 from David Nind <david@davidnind.com> --- Created attachment 199215 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=199215&action=edit Bug 23981: Use ecost instead of rrp to calculate ecost fields Test plan: 1) Create a new basket for the vendor above. 2) Add a new order (empty) and enter the following in the financial details: Vendor price: 10.99, Tax rate: 0%, Discount: 0%, Retail price: 12.99, Replacement cost: 15.99, Actual cost: 18.99. 3) Confirm the Budgeted cost shown on the order form is 10.99 (vendor price), not 12.99 (retail price). 4) Save the order. 5) Check the database using SELECT ecost, ecost_tax_excluded, ecost_tax_included FROM aqorders WHERE basketno = X (substitute your basket number). Verify ecost_tax_excluded and ecost_tax_included are both 10.99, not 12.99. 6) From the acquisitions home page, click the Ordered amount for your fund. Verify the Estimated cost per unit column shows 10.99, not 12.99. 7) Close the basket. 8) Receive a shipment. On the parcel page (list of items to receive), verify the Order cost column shows 10.99. 9) Click to receive the order. On the order receive page, verify the Budgeted cost field shows 10.99, not 12.99. 10) Complete receipt. Verify the fund's Spent column deducts the actual cost (18.99), not the retail price. 11) prove t/db_dependent/Acquisition/populate_order_with_prices.t Signed-off-by: David Nind <david@davidnind.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23981 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Depends on| |42603 Keywords| |release-notes-needed CC| |david@davidnind.com --- Comment #10 from David Nind <david@davidnind.com> --- For main you need to apply bug 42603 - Cannot 'receive selected' because of typo in variable name, otherwise you can't receive order in step 8. Have added this as a dependency. Testing notes (using KTD) ========================= I listed out all the details before and after the patch for each step, to show the workings for each step. Step 2 - Order entry (Accounting details section) ------------------------------------------------- No change before and after the patch: Vendor price: 10.99 --> 10.99 (no change) Tax rate: 0% --> 0% (no change) Discount: 0% --> 0% (no change) Retail price: 12.99 --> 12.99 (no change) Replacement cost: 15.99 --> 15.99 (no change) Budgeted cost: 10.99 --> 10.99 (no change) Total: 10.99 --> 10.99 (no change) Actual cost: 18.99 --> 18.99 (no change) Step 3 - Confirmed (Orders list for the basket) ----------------------------------------------- - Before the patch the 'Budgeted cost tax exc.' and 'Budgeted cost tax inc.' both show as 12.99 - After the patch the 'Budgeted cost tax exc.' and 'Budgeted cost tax inc.' both show as 10.99 Step 5 - Confirmed ------------------ Before the patch: SELECT ecost, ecost_tax_excluded, ecost_tax_included FROM aqorders WHERE basketno = 1; +-------+--------------------+--------------------+ | ecost | ecost_tax_excluded | ecost_tax_included | +-------+--------------------+--------------------+ | 10.99 | 12.990000 | 12.990000 | +-------+--------------------+--------------------+ 1 row in set (0.001 sec) After the patch: SELECT ecost, ecost_tax_excluded, ecost_tax_included FROM aqorders WHERE basketno = 1; +-------+--------------------+--------------------+ | ecost | ecost_tax_excluded | ecost_tax_included | +-------+--------------------+--------------------+ | 10.99 | 10.990000 | 10.990000 | +-------+--------------------+--------------------+ 1 row in set (0.000 sec) Step 6 - Confirmed ------------------ - Before the patch "Estimated cost per unit" for the fund shows as 12.99 - After the patch "Estimated cost per unit" for the fund shows as 10.99 Step 8 - Confirmed ------------------ - Before the patch "Order cost" shows as 10.99 - After the patch "Order cost" shows as 10.99 Step 9 - Confirmed ------------------ - Retail price: 12.99 --> 12.99 (no change) - Replacement price: 15.99 --> 15.99 (no change) - Budgeted cost: 12.99 --> 10.99 (CHANGE) - Actual cost: 18.99 --> 18.99 (no change) Step 10 - Confirmed ------------------- Already received table: - Replacement price: 15.99 --> 15.99 (no change) - Est cost: 12.99 --> 10.99 (CHANGE) - Actual cost: 18.99 --> 18.99 (no change) - Total: 18.99 --> 18.99 (no change) Acquisitions home page, all available funds section: - Spent (for fund): 18.99 > 18.99 (no change) Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42603 [Bug 42603] Cannot 'receive selected' because of typo in variable name -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23981 Bug 23981 depends on bug 42603, which changed state. Bug 42603 Summary: Cannot 'receive selected' because of typo in variable name https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42603 What |Removed |Added ---------------------------------------------------------------------------- Status|Needs documenting |RESOLVED Resolution|--- |FIXED -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23981 Michaela Sieber <michaela.sieber@kit.edu> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |Failed QA --- Comment #11 from Michaela Sieber <michaela.sieber@kit.edu> --- Hi all, we should definitly not change the calculations of prices now ! The values stored in the database are used for the migration to the new ACQ module. If we change the calculation now, then we have different concepts of how to calculate prices. The rrp is the field that is used to calculate the encumbered amount when ordering. (ecost_tax_excluded & ecost_tax_included) The existing bug in todays order form is the display of the budgeted costs as long as you are in the edit mode. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23981 --- Comment #12 from Michaela Sieber <michaela.sieber@kit.edu> --- Please see also the Description (#c0) of this bug : Propositions: - Unless there is a good reason to keep it (like someone wanting to discriminate between how much the vendor sells it vs how much another vendor might sell it), I would remove the vendor price completely and replace it with the retail price - Correct the order form to show the real estimated price, based on the retail price - Correct the db calculations to make sure the ecost is based on the rrp and that the tax are calculated on the ecost In the refreshed ACQ, there will be only one price field to avoid this situation and any resulting user confusion. It will still be possible to enter a different item replacement price; however, this value is not relevant for financial calculations or encumbrance processing. -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org