Thread Rating:
  • 2 Vote(s) - 3 Average
  • 1
  • 2
  • 3
  • 4
  • 5
gltrans amount uses double and not decimal
05-18-2016, 05:41 AM,
#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
Reply
05-18-2016, 10:49 PM,
#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.


Reply
05-21-2016, 04:57 AM,
#3
RE: gltrans amount uses double and not decimal
(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.

Reply
05-24-2016, 12:20 AM,
#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
Reply
07-05-2017, 09:34 PM,
#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 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
Reply
07-27-2017, 06:29 AM,
#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, BillSmile
Reply


Forum Jump:


Users browsing this thread: 1 Guest(s)