gltrans amount uses double and not decimal

05182016, 05:41 AM
Post: #1




gltrans amount uses double and not decimal
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 

05182016, 10:49 PM
Post: #2




RE: gltrans amount uses double and not decimal
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. 

05212016, 04:57 AM
Post: #3




RE: gltrans amount uses double and not decimal
(05182016 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? 

05242016, 12:20 AM
Post: #4




RE: gltrans amount uses double and not decimal
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 

07052017, 09:34 PM
Post: #5




RE: gltrans amount uses double and not decimal
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 128bit floating point data type.
Float  32 bit (7 digits) Double  64 bit (1516 digits) Decimal  128 bit (2829 significant digits) More about...Difference between Decimal, Float and Double Mark 

07272017, 06:29 AM
Post: #6




RE: gltrans amount uses double and not decimal
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, Bill 

« Next Oldest  Next Newest »

User(s) browsing this thread: 1 Guest(s)