webERP Forum
gltrans amount uses double and not decimal - Printable Version

+- webERP Forum (http://www.weberp.org/forum)
+-- Forum: webERP Discussion (/forumdisplay.php?fid=1)
+--- Forum: Development Discussion & Specification (/forumdisplay.php?fid=10)
+--- Thread: gltrans amount uses double and not decimal (/showthread.php?tid=2759)



gltrans amount uses double and not decimal - Bill Schlaerth - 05-18-2016 05:41 AM

Hi All,

We are a live customer and using webERP pretty well. Here is what we found in the gltrans table. The amount is a numeric defined as double. As a result we are finding amount going out to many more decimal places than 2. This causes the Trial balance and the Balance Sheet to be out of balance by pennies.

I understand when using exchange rates, but we were not expecting it in calculating Cost Of Goods when the qty is integers and the std cost is only to two places. Is the problem because the amount in gltrans is defined as double and maybe should be defined as decimal (14,2) or similar format?

We also saw an article on DOUBLE vs DECIMAL in MySQL that said financial amounts should not be defined as DOUBLE.

Your input is greatly appreciated.

Bill


RE: gltrans amount uses double and not decimal - agaluski - 05-18-2016 10:49 PM

I have seen this problem cause the balance to be off by very tiny portions of a penny - never multiple pennies. Are you sure that is the problem?
There have been a few times that rounding on W/O close has caused us to have an unbalanced entry off by .01.
Change this program: Z_CheckGLTransBalance.php.
Replace: HAVING ABS(SUM(amount))>0.01";
with: HAVING ABS(SUM(amount))>0.005";
Run it in the application - see if you have any unbalanced type "Work Order Variance" transactions.


RE: gltrans amount uses double and not decimal - Bill Schlaerth - 05-21-2016 04:57 AM

(05-18-2016 10:49 PM)agaluski Wrote:  I have seen this problem cause the balance to be off by very tiny portions of a penny - never multiple pennies. Are you sure that is the problem?
There have been a few times that rounding on W/O close has caused us to have an unbalanced entry off by .01.
Change this program: Z_CheckGLTransBalance.php.
Replace: HAVING ABS(SUM(amount))>0.01";
with: HAVING ABS(SUM(amount))>0.005";
Run it in the application - see if you have any unbalanced type "Work Order Variance" transactions.

Thanks agaluski I will go locate problems and let you know.



RE: gltrans amount uses double and not decimal - falkoner - 05-24-2016 12:20 AM

The problem with using the DECIMAL type is that the number of digits precision is hard coded into the database schema, and different currencies require different levels of precision. The only guaranteed way to maintain financial amounts is to have two fields, one for an integer amount and one for the number of decimal places, so $123.45 is stored as 12345 in the integer field, and 2 in the decimal places. This is how the likes of SAP and Oracle store their financial amounts.

However to change at this stage would just be too big a job so we are left with this issue, and trying to work around problems.

Thanks
Tim


RE: gltrans amount uses double and not decimal - joviermark - 07-05-2017 09:34 PM

The Decimal, Double, and Float variable types are different in the way that they store the values. Precision is the main difference where float is a single precision (32 bit) floating point data type, double is a double precision (64 bit) floating point data type and decimal is a 128-bit floating point data type.

Float - 32 bit (7 digits)

Double - 64 bit (15-16 digits)

Decimal - 128 bit (28-29 significant digits)

More about...Difference between Decimal, Float and Double

Mark


RE: gltrans amount uses double and not decimal - Bill Schlaerth - 07-27-2017 06:29 AM

Thanks Mark for the article. That explains it very well.
Here is what I have discovered over the past year.
double should probably not be used in gltrans, debtortrans or debtortranstaxes. (I am only commenting on the Sales and Receivable side). I have spoken with several people about how their currencies function (India and Mexico for specific) and found that they have decimal only to two places like USD even though an exchange rate may go to several places, you can only complete the trans action to two places. We have using discount percent and Sales Tax (VAT Tax) to 4 places (QUEBEC uses 9.975% for tax and NY will go to multiples of .125 (eg. 7.875%) in some local auth's. We have made changes to make the correct rounding calculations for price extensions and calculated taxes at the line level. We also isolated discount rounding problems by making use of the salesglpostings by separating the discounts from the qty * price when posting into gl (hats off to webERP for allowing the salesglppostings to break this out!). So much more was found. So little space to discuss.
Later, BillSmile