#64816 Incident ID:15639 - *MetaSQL statement “salesOrderItems” (list) incorrectly calculates “discountfromcust” when the “IncludeFormatted” parameter exist



The MetaSQL statement “salesOrderItems" (type “list”) uses the coitem.custprice and coitem.price fields to calculate the customer discount, since the entered discount is not stored on coitem.

It uses the following CASE statement to do the calculation in the FROM clause of the query:

CASE WHEN (coitem_custprice = 0.0) THEN 100.0
ELSE ((1.0 - (coitem_price / coitem_custprice)) * 100.0)
END AS discountfromcust

This results in “discountfromcust” being equal to a number between 0 and 100.

In the SELECT clause of the query, the query checks for the existence of parameter “includeFormatted”. If the parameter does not exist, the query sets the returned value of f_discountfromcust to discountfromcust, and that is correct. However, if the parameter does exist, the query sets the returned value of f_discountfromcust to be formatPrcnt(discountfromcust). This is not correct, because the formatPrcnt function returns a value of SELECT formatNumeric($1 * 100, 'percent') as its result, which when using discountfromcust (a number between 0 and 100) as its $1 parameter, returns a value between 0 and 10,000. In effect, because the initial calculation of discountfromcust multiplies by 100, and the formatting function also multiplies by 100, the end result is 100 times higher than it should be. For instance, if price is $90 and custprice is $100, then discountfromcust is 10, but f_discountfromcust when “includeFormatted” exists is 1,000.

The problem shows itself when one adds f_discountfromcust to a Sales Order Acknowledgement report generated from the Sales Order entry window. In this case, xTuple must pass “includeFormatted” as a parameter to the report, because f_discountfromcust is always shown at a value 100 times higher than it should be.

This took a lot to describe, but it’s a quick fix, as shown in the attached TXT file with an updated salesOrderList MetaSQL query. When “includeFormatted” exists, the query needs to return f_discountfromcust as formatPrcnt(discountfromcust / 100), not formatPrcnt(discountfromcust).
I’m hopeful that you can check this out, confirm the fix, and move the updated MetaSQL in as a patch.

Steps to Reproduce:

Additional information

For the entire bug information, please visit