Bug Tracker Incident #15639

 


Summary
Project
INCDT - xTuple ERP
Incident Category
Bugs
Incident Number
15639
Visibility
public


Product Version
3.7.2
Fixed In Version
3.7.4


Summary
*MetaSQL statement “salesOrderItems” (list) incorrectly calculates “discountfromcust” when the IncludeFormatted parameter exists


Description

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( * 100, 'percent') as its result, which when using discountfromcust (a number between 0 and 100) as its 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 and custprice is 0, 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:

Change the Customer Order Acknowledgement report to include Discount on the detail line.

Create a sales order and enter a customer discount of 10% on the sales order line item. Print the sales order using the updated Customer Order Acknowledgement. The discount shown will be 1000, not 10.


Details
Reporter
krauseo
CRM Account
Emseal
Status
Closed
Assigned
reddog


Priority
None
Severity
None
Updated
06-05-13 15:11
Resolution
Fixed



Comments

DateUsernameComment
10/06/11 16:07ptyler

Thanks for this feedback, Keith. Closing based on feedback.

10/06/11 16:05krauseo

I have tested this on 3.7.4 and it behaves as it should, dividing the discountfromcust value returned by formatPrcnt by 100 to arrive at a correct value of f_discountfromcust.

09/21/11 14:07reddog

backport 3.7.4

09/12/11 14:33reddog

change metasql salesOrderItems-list as described (correctly scale f_discountfromcust)

09/12/11 14:31svn

Revision: 12424
http://postbooks.svn.sourceforge.net/postbooks/?rev=12424&view=rev
Author: sunsetsolutions
Date: 2011-09-12 18:31:45 +0000 (Mon, 12 Sep 2011)
Log Message:
-----------
Issue #15639:correctly scale f_discountfromcust

Modified Paths:
--------------
xtupleserver/trunk/dbscripts/metasql/salesOrderItems-list.mql

09/09/11 17:36krauseo

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( * 100, 'percent') as its result, which when using discountfromcust (a number between 0 and 100) as its 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 and custprice is 0, 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:

Change the Customer Order Acknowledgement report to include Discount on the detail line.

Create a sales order and enter a customer discount of 10% on the sales order line item. Print the sales order using the updated Customer Order Acknowledgement. The discount shown will be 1000, not 10.



Characteristics

Backport
No
CodeReview
Needed
Operating System
Windows XP
Doc Flag
False
Copyright Assigned
Yes
HaxTuple
No
Points
None

Files

Filename
xTuple-2011-09-09-updated-salesOrderItems-list.txt
revised metasql


Related Documents

TypeNumberDescriptionRelationshipRemove
Incident1326Exchange Rates and Effectivity Dates in 2.2.1 affecting Aging reports / invoicesRelated tox
Incident1355Create a PackageRelated tox
ProjectXTUPLEAPPSPorted From Mantisx


Subscribers

You do not have permission to view subscribers.


Incident History

DateUsernameFieldChange
10/06/11 16:07ptylerStatusStatus Changed: Resolved -> Closed
09/09/11 17:36krauseoNewIncident Added
09/09/11 17:36krauseoCharacteristic Backport Added: "No"
09/09/11 17:36krauseoCharacteristic CodeReview Added: ""
09/09/11 17:36krauseoCharacteristic Operating System Added: "Windows XP"
09/09/11 17:36krauseoCharacteristic haxTuple Added: "No"
09/09/11 17:36krauseoCharacteristic Estimated Hours Added: ""
09/09/11 17:36krauseoCharacteristic Doc Flag Added: "False"
09/09/11 17:36krauseoCharacteristic Copyright Assigned Added: "Yes"
09/09/11 17:36acdrupalFound In: -> 3.7.2
09/11/11 14:53krauseoSummary Updated: "*MetaSQL statement “salesOrderItems” (list) incorrectly calculates “discountfromcust” when the “IncludeFormatted” parameter exist" -> "*MetaSQL statement “salesOrderItems” (list) incorrectly calculates “discountfromcust” when the IncludeFormatted parameter exists"
09/11/11 14:53krauseoDescription Updated: "The MetaSQL statemen..." -> "The MetaSQL statemen..."
09/12/11 14:34reddogFixed In: -> 3.8.0
09/12/11 14:33reddogCharacteristic CodeReview Changed: "" -> "Needed"
09/12/11 14:33reddogDescription Updated: "The MetaSQL statemen..." -> "The MetaSQL statemen..."
09/12/11 14:33reddogStatusStatus Changed: New -> Resolved
09/12/11 14:33reddogAssignedAssigned to: "" -> "reddog"
09/12/11 14:33reddogResolutionResolution Changed: -> Fixed
09/21/11 10:50reddogCharacteristic Backport Changed: "No" -> "Yes"
09/21/11 14:05reddogCharacteristic Backport Changed: "Yes" -> "No"
09/21/11 14:07reddogFixed In: 3.8.0Beta -> 3.7.4