R10 SR1 tax report discrepancies

For general questions and discussions specific to the AbleCommerce GOLD ASP.Net shopping cart software.
Post Reply
User avatar
jmestep
AbleCommerce Angel
Posts: 8164
Joined: Sun Feb 29, 2004 8:04 pm
Location: Dayton, OH
Contact:

R10 SR1 tax report discrepancies

Post by jmestep » Tue Nov 28, 2017 12:25 am

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?
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

User avatar
Katie
AbleCommerce Admin
AbleCommerce Admin
Posts: 2651
Joined: Tue Dec 02, 2003 1:54 am
Contact:

Re: R10 SR1 tax report discrepancies

Post by Katie » Tue Nov 28, 2017 3:23 am

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
Thank you for choosing AbleCommerce!

http://help.ablecommerce.com - product support
http://wiki.ablecommerce.com - developer support

User avatar
jmestep
AbleCommerce Angel
Posts: 8164
Joined: Sun Feb 29, 2004 8:04 pm
Location: Dayton, OH
Contact:

Re: R10 SR1 tax report discrepancies

Post by jmestep » Mon Dec 04, 2017 1:29 am

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):
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
The problem is the sum(this_.ProductSubtotal) as y4. Why should that be summed?

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

Post Reply