0005349: Wrong HAVING filtering clause in some reports

Openbravo Issue Tracking System - Openbravo ERP

View Issue Details

Project

Category

View Status

Date Submitted

Last Update

0005349

Openbravo ERP

07. Sales management

public

2008-10-01 08:54

2008-12-09 15:02

Reporter

rafaroda

Assigned To

Hennadzi

Priority

normal

Severity

minor

Reproducibility

always

Status

closed

Resolution

fixed

Platform

OS Version

Ubuntu 7.10

Product Version

Target Version

Fixed in Version

2.35MP10

Merge Request Status

Review Assigned To

OBNetwork customer

Web browser

Modules

Core

Support ticket

Regression level

Regression date

Regression introduced in release

Regression introduced by commit

Triggers an Emergency Pack

Summary

0005349: 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.

Additional Information