vTiger 5.1 – Number of decimals in prices

We sell items in large quantities at very low unit price like 1,000,000 items at $0.1234, so a variation of $0.0001 is worth $100.
Out of the box, vTiger has a mix of 2 and 3 decimals to handle unit prices. Do not ask me why, but for example:

• The product unit price has 2 decimals: decimal(25,2)
• The price book unit price has 3 decimals: decimal(25,3)
• Discount is decimal(7,3) which is fine for percentage, but problematic for amounts.

In order to extend the number of decimals, it is necessary to modify the type of the fields that store the price information in the database.
The example below is based on a standard installation under windows.
Open a cmd window.
Change directory to mysql.
cd C:\Program Files\vtigercrm-5.1.0\mysql\bin
In the line below, replace xxxxxx with your password to start mysql.
mysql -u root -pxxxxxx
Look at which databases are available and select the vtiger one.
show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
| vtigercrm510       |
+--------------------+
mysql>use vtigercrm510
List the tables available:
mysql>show tables;
The one we are interested in is the products table called vtiger_products.
We can find a short description of the vtiger data model in http://wiki.vtiger.com/index.php/Database_schema. A PDF file can be downloaded, but seems incomplete. Better information can be found here: http://forums.vtiger.com/download.php?id=1755
mysql> describe vtiger_products;
+------------------+---------------+------+-----+---------+-------+
| Field            | Type          | Null | Key | Default | Extra |
+------------------+---------------+------+-----+---------+-------+
| productid        | int(11)       | NO   | PRI | NULL    |       |
| product_no       | varchar(100)  | NO   |     | NULL    |       |
| productname      | varchar(50)   | NO   |     | NULL    |       |
| productcode      | varchar(40)   | YES  |     | NULL    |       |
| productcategory  | varchar(200)  | YES  |     | NULL    |       |
| manufacturer     | varchar(200)  | YES  |     | NULL    |       |
| qty_per_unit     | decimal(11,2) | YES  |     | 0.00    |       |
| unit_price       | decimal(25,2) | YES  |     | NULL    |       |
| weight           | decimal(11,3) | YES  |     | NULL    |       |
| pack_size        | int(11)       | YES  |     | NULL    |       |
| sales_start_date | date          | YES  |     | NULL    |       |
| sales_end_date   | date          | YES  |     | NULL    |       |
| start_date       | date          | YES  |     | NULL    |       |
| expiry_date      | date          | YES  |     | NULL    |       |
| cost_factor      | int(11)       | YES  |     | NULL    |       |
| commissionrate   | decimal(7,3)  | YES  |     | NULL    |       |
| commissionmethod | varchar(50)   | YES  |     | NULL    |       |
| discontinued     | int(1)        | NO   |     | 0       |       |
| usageunit        | varchar(200)  | YES  |     | NULL    |       |
| handler          | int(11)       | YES  |     | NULL    |       |
| reorderlevel     | int(11)       | YES  |     | NULL    |       |
| website          | varchar(100)  | YES  |     | NULL    |       |
| taxclass         | varchar(200)  | YES  |     | NULL    |       |
| mfr_part_no      | varchar(200)  | YES  |     | NULL    |       |
| vendor_part_no   | varchar(200)  | YES  |     | NULL    |       |
| serialno         | varchar(200)  | YES  |     | NULL    |       |
| qtyinstock       | decimal(25,3) | YES  |     | NULL    |       |
| productsheet     | varchar(200)  | YES  |     | NULL    |       |
| qtyindemand      | int(11)       | YES  |     | NULL    |       |
| glacct           | varchar(200)  | YES  |     | NULL    |       |
| vendor_id        | int(11)       | YES  |     | NULL    |       |
| imagename        | text          | YES  |     | NULL    |       |
| currency_id      | int(19)       | NO   |     | 1       |       |
+------------------+---------------+------+-----+---------+-------+
33 rows in set (0.28 sec)
The field that we need to change in this table is unit_price, so we issue the following command:

mysql>ALTER TABLE vtiger_products MODIFY unit_price  DECIMAL(25,4);

In each price book, we have a specific price for each product.

mysql> describe vtiger_pricebookproductrel;
+--------------+---------------+------+-----+---------+-------+
| Field        | Type          | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+-------+
| pricebookid  | int(19)       | NO   | PRI | NULL    |       |
| productid    | int(19)       | NO   | PRI | NULL    |       |
| listprice    | decimal(25,3) | YES  |     | NULL    |       |
| usedcurrency | int(11)       | NO   |     | 1       |       |
+--------------+---------------+------+-----+---------+-------+
4 rows in set (0.07 sec)

mysql>ALTER TABLE vtiger_pricebookproductrel MODIFY listprice  DECIMAL(25,4);

We have changed the fields in the database, so the price information will be stored with the proper number of decimals for our application.

The fields that we found are:

vtiger_inventoryproductrel: listprice decimal(25,3)
vtiger_inventoryproductrel: discount_amount decimal(7,3)
vtiger_productcurrencyrel: converted_price decimal(25,2)
vtiger_productcurrencyrel: actual_price decimal(25,2)


vtiger_productcurrencyrel:
adjustment       | decimal(25,3)
salescommission  | decimal(25,3)
exciseduty       | decimal(25,3)
total            | decimal(25,3)
subtotal         | decimal(25,3)
discount_percent | decimal(25,3)
discount_amount  | decimal(25,3)
s_h_amount       | decimal(25,3)