I couldn't find if there were any bug reports or patches, but did see that there are quite a few changes to the R12 SR1 tax report data source compared to the same code in R10.
The tax detail report is showing incorrect amounts in the product subtotal column. It seems like the only time it is correct is if there is only one item on an order. Otherwise, on a sampling of a few orders, it is 2 or 3 times the actual product subtotal in the order. Also, the summary report totals and detail report totals don't match for the same group of orders. Here are some screenshots to illustrate. $1523.75 and $69.36 are correct figures. Has there been a bug report and is it patched later?
R10 SR1 tax report discrepancies
- jmestep
- AbleCommerce Angel
- Posts: 8164
- Joined: Sun Feb 29, 2004 8:04 pm
- Location: Dayton, OH
- Contact:
R10 SR1 tax report discrepancies
Judy Estep
Web Developer
jestep@web2market.com
http://www.web2market.com
708-653-3100 x209
New search report plugin for business intelligence:
http://www.web2market.com/Search-Report ... -P154.aspx
Web Developer
jestep@web2market.com
http://www.web2market.com
708-653-3100 x209
New search report plugin for business intelligence:
http://www.web2market.com/Search-Report ... -P154.aspx
Re: R10 SR1 tax report discrepancies
Hi Judy,
I checked all the tax report files and nothing has been changed since 2014 - this is about the age of Gold R8 and R9.
I would like to know why they have the Per Unit Calculation option enabled? This is for VAT - not US merchants.
It is the only reason I can think of that might be causing some issues...
Thanks
Katie
I checked all the tax report files and nothing has been changed since 2014 - this is about the age of Gold R8 and R9.
I would like to know why they have the Per Unit Calculation option enabled? This is for VAT - not US merchants.
It is the only reason I can think of that might be causing some issues...
Thanks
Katie
Thank you for choosing AbleCommerce!
http://help.ablecommerce.com - product support
http://wiki.ablecommerce.com - developer support
http://help.ablecommerce.com - product support
http://wiki.ablecommerce.com - developer support
- jmestep
- AbleCommerce Angel
- Posts: 8164
- Joined: Sun Feb 29, 2004 8:04 pm
- Location: Dayton, OH
- Contact:
Re: R10 SR1 tax report discrepancies
I don't have an R12 site that has enough orders I can check to see if the changes in the source code for TaxReportDataSource.LoadDetail make any difference, but I did test on an R9 local site so I could get the sql queries that are generated by the report. The report generated one like this (without rounding code):
If I change the query to the following, it seems to be OK- I took out the sum at that point and grouped by this_.ProductSubtotal. I don't know if this would have any other repercussions, though. The nHibernate code is a little hairy. if the sum is causing the problem, that line of code is still present in R12.
The problem is the sum(this_.ProductSubtotal) as y4. Why should that be summed?SELECT TOP (20) this_.OrderId as y0_, this_.OrderNumber as y1_, this_.OrderDate as y2_, this_.TotalCharges as y3_,
sum(this_.ProductSubtotal) as y4_, sum((oi1_.Price * oi1_.Quantity)) as y5_,
this_.OrderId as y6_, this_.OrderNumber as y7_, this_.OrderDate as y8_, this_.TotalCharges as y9_ FROM ac_Orders this_
inner join ac_OrderItems oi1_ on this_.OrderId=oi1_.OrderId
WHERE this_.StoreId = 1 and oi1_.OrderItemTypeId = 3 and oi1_.Name = 'MA Luxury Tax (over $175)' and this_.BillToCountryCode = 'US' and this_.BillToProvince = 'MA' and
this_.OrderDate >= '2016-01-01 05:00:00' and this_.OrderDate <= '2017-12-05 04:59:59' and (this_.OrderStatusId =3 or this_.OrderStatusId = 9
or this_.OrderStatusId = 10 or this_.OrderStatusId = 12 or this_.OrderStatusId = 13 or this_.OrderStatusId =14
or this_.OrderStatusId = 18 or this_.OrderStatusId = 24)
GROUP BY this_.OrderId, this_.OrderNumber, this_.OrderDate, this_.TotalCharges
ORDER BY this_.OrderId asc
If I change the query to the following, it seems to be OK- I took out the sum at that point and grouped by this_.ProductSubtotal. I don't know if this would have any other repercussions, though. The nHibernate code is a little hairy. if the sum is causing the problem, that line of code is still present in R12.
SELECT TOP (20) this_.OrderId as y0_, this_.OrderNumber as y1_, this_.OrderDate as y2_, this_.TotalCharges as y3_,
this_.ProductSubtotal as y4_, sum((oi1_.Price * oi1_.Quantity)) as y5_,
this_.OrderId as y6_, this_.OrderNumber as y7_, this_.OrderDate as y8_, this_.TotalCharges as y9_ FROM ac_Orders this_
inner join ac_OrderItems oi1_ on this_.OrderId=oi1_.OrderId
WHERE this_.StoreId = 1 and oi1_.OrderItemTypeId = 3 and oi1_.Name = 'MA Luxury Tax (over $175)' and this_.BillToCountryCode = 'US' and this_.BillToProvince = 'MA' and
this_.OrderDate >= '2016-01-01 05:00:00' and this_.OrderDate <= '2017-12-05 04:59:59' and (this_.OrderStatusId =3 or this_.OrderStatusId = 9
or this_.OrderStatusId = 10 or this_.OrderStatusId = 12 or this_.OrderStatusId = 13 or this_.OrderStatusId =14
or this_.OrderStatusId = 18 or this_.OrderStatusId = 24)
GROUP BY this_.OrderId, this_.OrderNumber, this_.OrderDate, this_.TotalCharges,this_.ProductSubtotal
ORDER BY this_.OrderId asc
Judy Estep
Web Developer
jestep@web2market.com
http://www.web2market.com
708-653-3100 x209
New search report plugin for business intelligence:
http://www.web2market.com/Search-Report ... -P154.aspx
Web Developer
jestep@web2market.com
http://www.web2market.com
708-653-3100 x209
New search report plugin for business intelligence:
http://www.web2market.com/Search-Report ... -P154.aspx