Anonymous | Login
Project:
RSS
  
News | My View | View Issues | Roadmap | Summary

View Issue DetailsJump to Notes ] Issue History ] Print ]
ID
0005349
TypeCategorySeverityReproducibilityDate SubmittedLast Update
backport[Openbravo ERP] 07. Sales managementminoralways2008-10-01 08:542008-12-09 15:02
ReporterrafarodaView Statuspublic 
Assigned ToHennadzi 
PrioritynormalResolutionfixedFixed in Version2.35MP10
StatusclosedFix in branch2.3xFixed in SCM revision8124
ProjectionnoneETAnoneTarget Version
OSLinux 32 bitDatabasePostgreSQLJava version1.5
OS VersionUbuntu 7.10Database version8.3Ant version1.7
Product VersionpiSCM revision8115 
Review Assigned To
Web browser
ModulesCore
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0005349: Wrong HAVING filtering clause in some reports

DescriptionThe following reports (Java files) form a wrong HAVING clause do to a string concatenation problem:
1) ad_reports/ReportDimensionalAnalysesPDF.java
2) ad_reports/ReportRefundInvoiceCustomerDimensionalAnalyses.java
3) ad_reports/ReportRefundSalesDimensionalAnalyses.java
4) ad_reports/ReportRefundSalesDimensionalAnalysesPDF.java
Steps To ReproduceFor 3):
* Put a break point after the line: strOrderby = strHaving + strOrderby;
* Go to 'Sales Management || Analysis Tools || Sales Order Returns Dimensional Report'
* Fill the mandatory fields plus:
** Amount Greater Than (10)
** Less Than (20)
** Ratio Greater Than (-1)
** Less Than (1)
* Select some dimensions.
* Click on 'HTML Format' button.
* Report SQL query crashes with error message:
POSTGRESQL
07:43:15 [http-8883-Processor23] ERROR org.openbravo.erpCommon.ad_reports.ReportRefundSalesDimensionalAnalysesData - SQL error in query: SELECT NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5, NIVEL6, NIVEL7, SUM(LINENETAMT) AS LINENETAMT, SUM(REFUNDAMT) AS REFUNDAMT, DIVIDE(SUM(REFUNDAMT), (SUM(LINENETAMT)+SUM(REFUNDAMT)))*100 AS RATIO, SUM(QTYINVOICED) AS QTYINVOICED, SUM(REFUNDQTY) AS REFUNDQTY, SUM(LINENETREF) AS LINENETREF, SUM(REFUNDAMTREF) AS REFUNDAMTREF, SUM(QTYINVOICEDREF) AS QTYINVOICEDREF, SUM(REFUNDQTYREF) AS REFUNDQTYREF, '' AS ID, '' AS NAME, UOMSYMBOL FROM (SELECT to_char(M_WAREHOUSE.NAME) AS NIVEL1, to_char(AD_USER.FIRSTNAME||' '||' '||AD_USER.LASTNAME) AS NIVEL2, to_char(C_ORDER.DOCUMENTNO) AS NIVEL3, to_char(AD_COLUMN_IDENTIFIER(to_char('M_Product'), to_char( M_PRODUCT.M_PRODUCT_ID), to_char('en_US'))) AS NIVEL4, to_char(M_PRODUCT_CATEGORY.NAME) AS NIVEL5, to_char(AD_COLUMN_IDENTIFIER(to_char('C_Bpartner'), to_char( C_BPARTNER.C_BPARTNER_ID), to_char('en_US'))) AS NIVEL6, to_char(C_BP_GROUP.NAME) AS NIVEL7, CASE SIGN(C_ORDERLINE.LINENETAMT) WHEN -1 THEN 0 ELSE C_ORDERLINE.LINENETAMT END AS LINENETAMT, CASE SIGN(C_ORDERLINE.LINENETAMT) WHEN -1 THEN -1*C_ORDERLINE.LINENETAMT ELSE 0 END AS REFUNDAMT, CASE SIGN(C_ORDERLINE.QTYORDERED) WHEN -1 THEN 0 ELSE C_ORDERLINE.QTYORDERED END AS QTYINVOICED, CASE SIGN(C_ORDERLINE.QTYORDERED) WHEN -1 THEN (-1*C_ORDERLINE.QTYORDERED) ELSE 0 END AS REFUNDQTY, 0 AS LINENETREF, 0 AS REFUNDAMTREF, 0 AS QTYINVOICEDREF, 0 AS REFUNDQTYREF, C_UOM.UOMSYMBOL FROM C_ORDER left join AD_USER on C_ORDER.SALESREP_ID = AD_USER.AD_USER_ID, C_ORDERLINE left join M_PRODUCT on C_ORDERLINE.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID left join M_PRODUCT_CATEGORY on M_PRODUCT.M_PRODUCT_CATEGORY_ID = M_PRODUCT_CATEGORY.M_PRODUCT_CATEGORY_ID left join C_UOM on C_ORDERLINE.C_UOM_ID = C_UOM.C_UOM_ID, C_BPARTNER, C_BP_GROUP, M_WAREHOUSE WHERE C_ORDER.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID AND C_BPARTNER.C_BP_GROUP_ID = C_BP_GROUP.C_BP_GROUP_ID AND C_ORDER.C_ORDER_ID = C_ORDERLINE.C_ORDER_ID AND C_ORDER.M_WAREHOUSE_ID = M_WAREHOUSE.M_WAREHOUSE_ID AND C_ORDER.ISSOTRX = 'Y' AND C_ORDER.PROCESSED = 'Y' AND 0=0 AND C_ORDER.AD_ORG_ID IN ('1000009','1000008','1000007','1000006','1000005','1000004','1000003','1000002','1000000','0') AND C_ORDER.AD_CLIENT_ID IN ('0','1000000') AND 1=1 AND C_ORDER.DATEORDERED >= to_date(?) AND C_ORDER.DATEORDERED < to_date(?) ) AA WHERE LINENETAMT <> 0 OR REFUNDAMT <> 0 OR LINENETREF <> 0 OR REFUNDAMTREF <> 0 GROUP BY NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5, NIVEL6, NIVEL7, UOMSYMBOL AND DIVIDE(SUM(REFUNDAMT), (SUM(LINENETAMT)+SUM(REFUNDAMT)))*100 > -1 AND DIVIDE(SUM(REFUNDAMT), (SUM(LINENETAMT)+SUM(REFUNDAMT)))*100 < 1 ORDER BY NIVEL1,NIVEL1,NIVEL2,NIVEL3,NIVEL4,NIVEL5,NIVEL6Exception:org.postgresql.util.PSQLException: ERROR: argument of AND must be type boolean, not type character
07:43:15 [http-8883-Processor23] ERROR org.openbravo.erpCommon.ad_reports.ReportRefundSalesDimensionalAnalyses - Error captured: javax.servlet.ServletException: @CODE=0@ERROR: argument of AND must be type boolean, not type character

ORACLE
06:10:30 [ajp-8009-2] ERROR org.openbravo.erpCommon.ad_reports.ReportRefundSalesDimensionalAnalysesData - SQL error in query: SELECT NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5, NIVEL6, NIVEL7, SUM(LINENETAMT) AS LINENETAMT, SUM(REFUNDAMT) AS REFUNDAMT, DIVIDE(SUM(REFUNDAMT), (SUM(LINENETAMT)+SUM(REFUNDAMT)))*100 AS RATIO, SUM(QTYINVOICED) AS QTYINVOICED, SUM(REFUNDQTY) AS REFUNDQTY, SUM(LINENETREF) AS LINENETREF, SUM(REFUNDAMTREF) AS REFUNDAMTREF, SUM(QTYINVOICEDREF) AS QTYINVOICEDREF, SUM(REFUNDQTYREF) AS REFUNDQTYREF, '' AS ID, '' AS NAME, UOMSYMBOL FROM (SELECT to_char(M_WAREHOUSE.NAME) AS NIVEL1, to_char(AD_USER.FIRSTNAME||' '||' '||AD_USER.LASTNAME) AS NIVEL2, to_char(C_ORDER.DOCUMENTNO) AS NIVEL3, to_char(AD_COLUMN_IDENTIFIER(to_char('M_Product'), to_char( M_PRODUCT.M_PRODUCT_ID), to_char( 'es_ES'))) AS NIVEL4, to_char(M_PRODUCT_CATEGORY.NAME) AS NIVEL5, to_char(AD_COLUMN_IDENTIFIER(to_char('C_Bpartner'), to_char( C_BPARTNER.C_BPARTNER_ID), to_char( 'es_ES'))) AS NIVEL6, to_char(C_BP_GROUP.NAME) AS NIVEL7, CASE SIGN(C_ORDERLINE.LINENETAMT) WHEN -1 THEN 0 ELSE C_ORDERLINE.LINENETAMT END AS LINENETAMT, CASE SIGN(C_ORDERLINE.LINENETAMT) WHEN -1 THEN -1*C_ORDERLINE.LINENETAMT ELSE 0 END AS REFUNDAMT, CASE SIGN(C_ORDERLINE.QTYORDERED) WHEN -1 THEN 0 ELSE C_ORDERLINE.QTYORDERED END AS QTYINVOICED, CASE SIGN(C_ORDERLINE.QTYORDERED) WHEN -1 THEN (-1*C_ORDERLINE.QTYORDERED) ELSE 0 END AS REFUNDQTY, 0 AS LINENETREF, 0 AS REFUNDAMTREF, 0 AS QTYINVOICEDREF, 0 AS REFUNDQTYREF, C_UOM.UOMSYMBOL FROM C_ORDER left join AD_USER on C_ORDER.SALESREP_ID = AD_USER.AD_USER_ID, C_ORDERLINE left join M_PRODUCT on C_ORDERLINE.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID left join M_PRODUCT_CATEGORY on M_PRODUCT.M_PRODUCT_CATEGORY_ID = M_PRODUCT_CATEGORY.M_PRODUCT_CATEGORY_ID left join C_UOM on C_ORDERLINE.C_UOM_ID = C_UOM.C_UOM_ID, C_BPARTNER, C_BP_GROUP, M_WAREHOUSE WHERE C_ORDER.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID AND C_BPARTNER.C_BP_GROUP_ID = C_BP_GROUP.C_BP_GROUP_ID AND C_ORDER.C_ORDER_ID = C_ORDERLINE.C_ORDER_ID AND C_ORDER.M_WAREHOUSE_ID = M_WAREHOUSE.M_WAREHOUSE_ID AND C_ORDER.ISSOTRX = 'Y' AND C_ORDER.PROCESSED = 'Y' AND 0=0 AND C_ORDER.AD_ORG_ID IN (1000000,0) AND C_ORDER.AD_CLIENT_ID IN (0,1000000) AND 1=1 AND C_ORDER.DATEORDERED >= to_date(?) AND C_ORDER.DATEORDERED < to_date(?) ) AA WHERE LINENETAMT <> 0 OR REFUNDAMT <> 0 OR LINENETREF <> 0 OR REFUNDAMTREF <> 0 GROUP BY NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5, NIVEL6, NIVEL7, UOMSYMBOL AND DIVIDE(SUM(REFUNDAMT), (SUM(LINENETAMT)+SUM(REFUNDAMT)))*100 > -1 AND DIVIDE(SUM(REFUNDAMT), (SUM(LINENETAMT)+SUM(REFUNDAMT)))*100 < 1 ORDER BY NIVEL1,NIVEL1,NIVEL2,NIVEL3,NIVEL4,NIVEL5,NIVEL6Exception:java.sql.SQLException: ORA-00933: SQL command not properly ended
06:10:30 [ajp-8009-2] ERROR org.openbravo.erpCommon.ad_reports.ReportRefundSalesDimensionalAnalyses - Error captured: javax.servlet.ServletException: @CODE=933@ORA-00933: SQL command not properly ended

* Notice that 'strOrderby' has:
** The order of the dimensions selected
** A clause for the the ratio that misses from HAVING clause.

For 4) Sames steps that 3) but press on 'PDF Format' button.
Proposed SolutionPatch attached for ad_reports/ReportRefundSalesDimensionalAnalyses.java.
TagsNo tags attached.
Attached Files

- Relationships Relation Graph ] Dependency Graph ]
blocks defect 0005347 closedHennadzi Wrong HAVING filtering clause in some reports 

-  Notes
(0009302)
svnbot (reporter)
2008-10-01 13:47

Repository: openbravo
Revision: 8124
Author: hennadzi
Date: 2008-10-01 13:47:12 +0200 (Wed, 01 Oct 2008)

Fixed bug 0005349: Wrong HAVING filtering clause in some reports

---
U branches/r2.3x/src/org/openbravo/erpCommon/ad_reports/ReportDimensionalAnalysesPDF.java
U branches/r2.3x/src/org/openbravo/erpCommon/ad_reports/ReportRefundInvoiceCustomerDimensionalAnalyses.java
U branches/r2.3x/src/org/openbravo/erpCommon/ad_reports/ReportRefundSalesDimensionalAnalyses.java
U branches/r2.3x/src/org/openbravo/erpCommon/ad_reports/ReportRefundSalesDimensionalAnalysesPDF.java
---

https://dev.openbravo.com/websvn/openbravo/?rev=8124&sc=1 [^]

- Issue History
Date Modified Username Field Change
2008-10-01 08:54 rafaroda New Issue
2008-10-01 08:54 rafaroda Assigned To => Hennadzi
2008-10-01 08:54 rafaroda Status new => scheduled
2008-10-01 13:47 svnbot Checkin
2008-10-01 13:47 svnbot Note Added: 0009302
2008-10-01 13:47 svnbot Status scheduled => resolved
2008-10-01 13:47 svnbot Resolution open => fixed
2008-10-01 13:47 svnbot svn_revision => 8124
2008-12-09 15:02 psarobe Regression testing => No
2008-12-09 15:02 psarobe Status resolved => closed


Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker