Project:
| View Issue Details[ Jump to Notes ] | [ Issue History ] [ Print ] | |||||||
| ID | ||||||||
| 0005347 | ||||||||
| Type | Category | Severity | Reproducibility | Date Submitted | Last Update | |||
| defect | [Openbravo ERP] 07. Sales management | minor | always | 2008-10-01 08:54 | 2008-12-13 20:29 | |||
| Reporter | rafaroda | View Status | public | |||||
| Assigned To | Hennadzi | |||||||
| Priority | normal | Resolution | fixed | Fixed in Version | ||||
| Status | closed | Fix in branch | pi | Fixed in SCM revision | 8122 | |||
| Projection | none | ETA | none | Target Version | ||||
| OS | Linux 32 bit | Database | PostgreSQL | Java version | 1.5 | |||
| OS Version | Ubuntu 7.10 | Database version | 8.3 | Ant version | 1.7 | |||
| Product Version | pi | SCM revision | 8115 | |||||
| Merge Request Status | ||||||||
| Review Assigned To | ||||||||
| OBNetwork customer | No | |||||||
| Web browser | ||||||||
| Modules | Core | |||||||
| Support ticket | ||||||||
| Regression level | ||||||||
| Regression date | ||||||||
| Regression introduced in release | ||||||||
| Regression introduced by commit | ||||||||
| Triggers an Emergency Pack | No | |||||||
| Summary | 0005347: Wrong HAVING filtering clause in some reports | |||||||
| Description | The 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 Reproduce | For 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 Solution | Patch attached for ad_reports/ReportRefundSalesDimensionalAnalyses.java. | |||||||
| Tags | No tags attached. | |||||||
| Attached Files |  patch-ReportRefundSalesDimensionalAnalyses.diff [^] (1,878 bytes) 2008-10-01 08:54 [Show Content]
| |||||||
 Relationships [ Relation Graph ]
[ Dependency Graph ]
|
|||||||||||||||
|
|||||||||||||||
 Relationships |
|
Notes |
|
|
(0009300) svnbot (viewer) 2008-10-01 13:33 |
Repository: openbravo Revision: 8122 Author: hennadzi Date: 2008-10-01 13:33:32 +0200 (Wed, 01 Oct 2008) Fixed bug 0005347: Wrong HAVING filtering clause in some reports --- U trunk/src/org/openbravo/erpCommon/ad_reports/ReportDimensionalAnalysesPDF.java U trunk/src/org/openbravo/erpCommon/ad_reports/ReportRefundInvoiceCustomerDimensionalAnalyses.java U trunk/src/org/openbravo/erpCommon/ad_reports/ReportRefundSalesDimensionalAnalyses.java U trunk/src/org/openbravo/erpCommon/ad_reports/ReportRefundSalesDimensionalAnalysesPDF.java --- https://dev.openbravo.com/websvn/openbravo/?rev=8122&sc=1 [^] |
|
Notes |
|
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 | sf_bug_id | 0 => 2139789 |
| 2008-10-01 08:54 | rafaroda | File Added: patch-ReportRefundSalesDimensionalAnalyses.diff | |
| 2008-10-01 08:54 | rafaroda | OBNetwork customer | => No |
| 2008-10-01 08:54 | rafaroda | Regression testing | => No |
| 2008-10-01 08:54 | rafaroda | Status | new => scheduled |
| 2008-10-01 08:54 | rafaroda | fix_in_branch | => trunk |
| 2008-10-01 13:33 | svnbot | Checkin | |
| 2008-10-01 13:33 | svnbot | Note Added: 0009300 | |
| 2008-10-01 13:33 | svnbot | Status | scheduled => resolved |
| 2008-10-01 13:33 | svnbot | Resolution | open => fixed |
| 2008-10-01 13:33 | svnbot | svn_revision | => 8122 |
| 2008-12-13 20:29 | psarobe | Status | resolved => closed |
|
Issue History |
| Copyright © 2000 - 2009 MantisBT Group |
 |