Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0005776Openbravo ERP07. Sales managementpublic2008-10-31 11:072008-11-25 18:56
Reporterrafaroda 
Assigned Torafaroda 
PriorityimmediateSeveritycriticalReproducibilityalways
StatusclosedResolutionfixed 
PlatformOS20OS VersionUbuntu 7.10
Product Versionpi 
Target VersionFixed in Version 
Merge Request Status
Review Assigned To
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0005776: Trunk revision 9471 does not compile in Oracle
DescriptionTrunk revision 9471 does not compile in Oracle:

ERROR org.openbravo.data.Sqlc - SQL error in query:
     [java]
     [java] SELECT NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5, NIVEL6, NIVEL7,
     [java] SUM(LINENETAMT) AS LINENETAMT, SUM(REFUNDAMT) AS REFUNDAMT,
     [java] C_DIVIDE(SUM(REFUNDAMT), (SUM(LINENETAMT)+SUM(REFUNDAMT)))*100 AS RATIO,
     [java] SUM(QTYINVOICED) AS QTYINVOICED, SUM(REFUNDQTY) AS REFUNDQTY,
     [java] SUM(LINENETREF) AS LINENETREF, SUM(REFUNDAMTREF) AS REFUNDAMTREF,
     [java] C_DIVIDE(SUM(REFUNDAMTREF), (SUM(LINENETREF)+SUM(REFUNDAMTREF)))*100 AS RATIOREF,
     [java] SUM(QTYINVOICEDREF) AS QTYINVOICEDREF, SUM(REFUNDQTYREF) AS REFUNDQTYREF, UOMSYMBOL,
     [java] C_CURRENCY_SYMBOL(?, 0, 'Y') AS CONVSYM,
     [java] C_CURRENCY_ISOSYM(?) AS CONVISOSYM,
     [java] '' AS ID, '' AS NAME, '' AS TRANSCURRENCYID, '' AS TRANSDATE, '' AS TRANSCLIENTID, '' AS TRANSORGID
     [java] FROM (SELECT NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5, NIVEL6, NIVEL7,
     [java] C_CURRENCY_CONVERT(SUM(LINENETAMT), TRCURRENCYID, ?, TO_DATE(TRDATE), NULL, TRCLIENTID, TRORGID) AS LINENETAMT,
     [java] C_CURRENCY_CONVERT(SUM(REFUNDAMT), TRCURRENCYID, ?, TO_DATE(TRDATE), NULL, TRCLIENTID, TRORGID) AS REFUNDAMT,
     [java] SUM(QTYINVOICED) AS QTYINVOICED, SUM(REFUNDQTY) AS REFUNDQTY,
     [java] C_CURRENCY_CONVERT(SUM(LINENETREF), TRCURRENCYID, ?, TO_DATE(TRDATE), NULL, TRCLIENTID, TRORGID) AS LINENETREF,
     [java] C_CURRENCY_CONVERT(SUM(REFUNDAMTREF), TRCURRENCYID, ?, TO_DATE(TRDATE), NULL, TRCLIENTID, TRORGID) AS REFUNDAMTREF,
     [java] SUM(QTYINVOICEDREF) AS QTYINVOICEDREF, SUM(REFUNDQTYREF) AS REFUNDQTYREF, UOMSYMBOL,
     [java] TRCURRENCYID, TRDATE, TRCLIENTID, TRORGID
     [java] FROM (SELECT to_char('1') AS NIVEL1, to_char('2') AS NIVEL2, to_char('3') AS NIVEL3, to_char('4') AS NIVEL4, to_char('5') AS NIVEL5, to_char('6') AS NIVEL6, to_char('7') AS NIVEL7,
     [java] CASE SIGN(C_ORDERLINE.LINENETAMT) WHEN -1 THEN 0 ELSE C_ORDERLINE.LINENETAMT END AS LINENETAMT,
     [java] CASE SIGN(C_ORDERLINE.LINENETAMT) WHEN -1 THEN -1*C_ORDERLINE.LINENETAMT ELSE 0 END AS REFUNDAMT,
     [java] CASE SIGN(C_ORDERLINE.QTYORDERED) WHEN -1 THEN 0 ELSE C_ORDERLINE.QTYORDERED END AS QTYINVOICED,
     [java] CASE SIGN(C_ORDERLINE.QTYORDERED) WHEN -1 THEN (-1*C_ORDERLINE.QTYORDERED) ELSE 0 END AS REFUNDQTY,
     [java] 0 AS LINENETREF, 0 AS REFUNDAMTREF, 0 AS QTYINVOICEDREF, 0 AS REFUNDQTYREF, C_UOM.UOMSYMBOL,
     [java] COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID) AS TRCURRENCYID,
     [java] TO_DATE(COALESCE(C_ORDERLINE.DATEORDERED, C_ORDER.DATEORDERED, NOW())) AS TRDATE,
     [java] C_ORDERLINE.AD_CLIENT_ID AS TRCLIENTID,
     [java] C_ORDERLINE.AD_ORG_ID AS TRORGID
     [java] FROM C_ORDER left join AD_USER on C_ORDER.SALESREP_ID = AD_USER.AD_USER_ID,
     [java] C_ORDERLINE left join M_PRODUCT on C_ORDERLINE.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID
     [java] left join M_PRODUCT_CATEGORY on M_PRODUCT.M_PRODUCT_CATEGORY_ID = M_PRODUCT_CATEGORY.M_PRODUCT_CATEGORY_ID
     [java] left join C_UOM on C_ORDERLINE.C_UOM_ID = C_UOM.C_UOM_ID,
     [java] C_BPARTNER, C_BP_GROUP, M_WAREHOUSE
     [java] WHERE C_ORDER.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
     [java] AND C_BPARTNER.C_BP_GROUP_ID = C_BP_GROUP.C_BP_GROUP_ID
     [java] AND C_ORDER.C_ORDER_ID = C_ORDERLINE.C_ORDER_ID
     [java] AND C_ORDER.M_WAREHOUSE_ID = M_WAREHOUSE.M_WAREHOUSE_ID
     [java] AND C_ORDER.ISSOTRX = 'Y'
     [java] AND C_ORDER.PROCESSED = 'Y'
     [java] AND 0=0 AND C_ORDER.AD_ORG_ID IN ('1')
     [java] AND C_ORDER.AD_CLIENT_ID IN ('8')
     [java] AND 1=1
     [java] UNION ALL SELECT to_char('9') AS NIVEL1 , to_char('10') AS NIVEL2 , to_char('11') AS NIVEL3 , to_char('12') AS NIVEL4 , to_char('13') AS NIVEL5 , to_char('14') AS NIVEL6 , to_char('15') AS NIVEL7 ,
     [java] 0 AS LINENETAMT, 0 AS REFUNDAMT, 0 AS QTYINVOICED, 0 AS REFUNDQTY,
     [java] CASE SIGN(C_ORDERLINE.LINENETAMT) WHEN -1 THEN 0 ELSE C_ORDERLINE.LINENETAMT END AS LINENETREF,
     [java] CASE SIGN(C_ORDERLINE.LINENETAMT) WHEN -1 THEN (-1*C_ORDERLINE.LINENETAMT) ELSE 0 END AS REFUNDAMTREF,
     [java] CASE SIGN(C_ORDERLINE.QTYORDERED) WHEN -1 THEN 0 ELSE C_ORDERLINE.QTYORDERED END AS QTYINVOICEDREF,
     [java] CASE SIGN(C_ORDERLINE.QTYORDERED) WHEN -1 THEN (-1*C_ORDERLINE.QTYORDERED) ELSE 0 END AS REFUNDQTYREF, C_UOM.UOMSYMBOL,
     [java] COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID) AS TRCURRENCYID,
     [java] TO_DATE(COALESCE(C_ORDERLINE.DATEORDERED, C_ORDER.DATEORDERED, NOW())) AS TRDATE,
     [java] C_ORDERLINE.AD_CLIENT_ID AS TRCLIENTID,
     [java] C_ORDERLINE.AD_ORG_ID AS TRORGID
     [java] FROM C_ORDER left join AD_USER on C_ORDER.SALESREP_ID = AD_USER.AD_USER_ID,
     [java] C_ORDERLINE left join M_PRODUCT on C_ORDERLINE.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID
     [java] left join M_PRODUCT_CATEGORY on M_PRODUCT.M_PRODUCT_CATEGORY_ID = M_PRODUCT_CATEGORY.M_PRODUCT_CATEGORY_ID
     [java] left join C_UOM on C_ORDERLINE.C_UOM_ID = C_UOM.C_UOM_ID,
     [java] C_BPARTNER, C_BP_GROUP, M_WAREHOUSE
     [java] WHERE C_ORDER.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
     [java] AND C_BPARTNER.C_BP_GROUP_ID = C_BP_GROUP.C_BP_GROUP_ID
     [java] AND C_ORDER.C_ORDER_ID = C_ORDERLINE.C_ORDER_ID
     [java] AND C_ORDER.M_WAREHOUSE_ID = M_WAREHOUSE.M_WAREHOUSE_ID
     [java] AND C_ORDER.ISSOTRX = 'Y'
     [java] AND C_ORDER.PROCESSED = 'Y'
     [java] AND 3=3 AND C_ORDER.AD_ORG_ID IN ('1')
     [java] AND C_ORDER.AD_CLIENT_ID IN('16')
     [java] AND 2=2) AA
     [java] WHERE LINENETAMT <> 0
     [java] OR REFUNDAMT <> 0
     [java] OR LINENETREF <> 0
     [java] OR REFUNDAMTREF <> 0
     [java] GROUP BY NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5, NIVEL6, NIVEL7, UOMSYMBOL, TRCURRENCYID, TRDATE, TRCLIENTID, TRORGID) ZZ
     [java] GROUP BY NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5, NIVEL6, NIVEL7, UOMSYMBOL, CONVSYM, CONVISOSYM, 1
     [java] Exception:java.sql.SQLException: ORA-00904: "CONVISOSYM": invalid identifier
     [java]
     [java] java.sql.SQLException: ORA-00904: "CONVISOSYM": invalid identifier
Steps To Reproduce
Proposed Solution
Additional Information
TagsNo tags attached.
Relationships
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2008-10-31 11:07rafarodaNew Issue
2008-10-31 11:07rafarodaAssigned To => rafaroda
2008-10-31 11:07rafarodasf_bug_id0 => 2211549
2008-10-31 11:07rafarodaRegression testing => No
2008-10-31 12:45svnbotCheckin
2008-10-31 12:45svnbotNote Added: 0009885
2008-10-31 12:45svnbotStatusnew => resolved
2008-10-31 12:45svnbotResolutionopen => fixed
2008-10-31 12:45svnbotsvn_revision => 9500
2008-11-25 18:56psarobeStatusresolved => closed

Notes
(0009885)
svnbot   
2008-10-31 12:45   
Repository: openbravo
Revision: 9500
Author: rafaroda
Date: 2008-10-31 12:45:52 +0100 (Fri, 31 Oct 2008)

Fixes bug 0005776 Changed XSQLs to work with Oracle.

---
U trunk/src/org/openbravo/erpCommon/ad_reports/ReportInvoiceCustomerDimensionalAnalysesJR_data.xsql
U trunk/src/org/openbravo/erpCommon/ad_reports/ReportInvoiceVendorDimensionalAnalysesJR_data.xsql
U trunk/src/org/openbravo/erpCommon/ad_reports/ReportMaterialDimensionalAnalysesJR_data.xsql
U trunk/src/org/openbravo/erpCommon/ad_reports/ReportParetoProduct_data.xsql
U trunk/src/org/openbravo/erpCommon/ad_reports/ReportPurchaseDimensionalAnalysesJR_data.xsql
U trunk/src/org/openbravo/erpCommon/ad_reports/ReportRefundSalesDimensionalAnalyses_data.xsql
U trunk/src/org/openbravo/erpCommon/ad_reports/ReportSalesDimensionalAnalyzeJR_data.xsql
U trunk/src/org/openbravo/erpCommon/ad_reports/ReportShipmentDimensionalAnalyzeJR_data.xsql
---

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