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

View Issue DetailsJump to Notes ] Issue History ] Print ]
ID
0005047
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Openbravo ERP] 08. Project and service managementmajoralways2008-09-15 09:392008-12-03 13:57
ReporterrafarodaView Statuspublic 
Assigned Torafaroda 
PrioritynormalResolutionfixedFixed in Version
StatusclosedFix in branchFixed in SCM revision7343
ProjectionnoneETAnoneTarget Version
OSLinux 32 bitDatabasePostgreSQLJava version1.5
OS VersionUbuntu 7.10Database version8.3Ant version1.7
Product VersionpiSCM revision7284 
Review Assigned To
Web browser
ModulesCore
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0005047: 'Project Profitability' nor 'Expense Report' work in PostgreSQL due to UUID

Description'Project Profitability' nor 'Expense Report' work in PostgreSQL due to UUID with error messages, respectively:
07:15:40 [ajp-8009-5] ERROR org.openbravo.erpCommon.ad_reports.ReportProjectProfitabilityData - SQL error in query: SELECT P.NAME AS PROJECTNAME, P.DATECONTRACT AS INITDATE, BPRESP.NAME AS RESPONSIBLE, P.AD_ORG_ID AS ORG, BPCLIENT.NAME AS PARTNER, ORG.NAME AS ORGNAME,PT.NAME AS PROJECTTYPE, COALESCE(P.SERVREVENUE,0) AS PLANREVENUE, COALESCE(P.SERVCOST,0) AS PLANCOST, COALESCE(P.EXPREINVOICING,0) AS PLANREINVOICING, COALESCE(P.EXPEXPENSES,0) AS PLANEXPENSES, COALESCE(SERREV.AMOUNT,0) AS REALREVENUE, COALESCE(COST.COST,0) AS REALCOST, COALESCE(EXPREI.AMOUNT,0) AS REALREINVOICED, COALESCE(EXPEXP.AMOUNT,0) AS REALEXPENSES, COALESCE(COLLECTED.AMOUNT,0) AS COLLECTED, '' AS NODE_ID, '' AS ISSUMMARY FROM C_BPartner bpclient, AD_Org org, C_Project p LEFT JOIN C_BPartner bpresp ON p.Responsible_ID = bpresp.C_BPartner_ID LEFT JOIN C_Projecttype pt ON pt.C_Projecttype_ID = p.C_Projecttype_ID LEFT JOIN (SELECT S_TimeExpenseLine.C_Project_ID, SUM((CASE S_TimeExpenseLine.IsTimereport WHEN 'Y' THEN S_TimeExpenseLine.qty ELSE 0 END)* C_CALCULATECOST_CATSALARY(C_CALCULATE_CATSALARY(C_BPartner.c_bpartner_id, s_timeexpense.DATEREPORT), s_timeexpense.DATEREPORT)) AS cost FROM S_TimeExpenseLine , S_TimeExpense, C_BPartner WHERE S_TimeExpense.S_TimeExpense_ID = S_TimeExpenseLine.S_TimeExpense_ID AND S_TimeExpense.C_BPartner_ID = C_BPartner.C_BPartner_ID AND S_TimeExpense.Processed = 'Y' AND 1=1 GROUP BY S_TimeExpenseLine.C_Project_ID) cost ON p.C_Project_ID = cost.C_Project_ID LEFT JOIN (SELECT c_Project, sum(amount) as amount FROM ( SELECT CASE WHEN ila.C_InvoiceLine_AcctDimension_ID IS NULL THEN i.c_project_id ELSE ila.c_project_id END AS C_Project, CASE WHEN ila.C_InvoiceLine_AcctDimension_ID IS NULL THEN COALESCE(il.linenetamt,0) * (1 + (CASE WHEN i.totallines IS NULL OR disc.dis_amt IS NULL THEN 0 ELSE (COALESCE(disc.dis_amt,0)/(ABS(i.totallines) + ABS(disc.dis_amt))) END)) ELSE COALESCE(ila.amt,0) * (1 + (CASE WHEN i.totallines IS NULL OR disc.dis_amt IS NULL THEN 0 ELSE (COALESCE(disc.dis_amt,0)/(ABS(i.totallines) + ABS(disc.dis_amt))) END)) END AS amount FROM M_Product p, C_InvoiceLine il LEFT JOIN C_InvoiceLine_AcctDimension ila on il.C_InvoiceLine_ID = ila.C_InvoiceLine_ID, C_Invoice i left join (SELECT c_invoice_id, sum(linenetamt) as dis_amt FROM c_invoiceline WHERE c_invoice_discount_id IS NOT NULL GROUP BY c_invoice_id) disc ON i.C_Invoice_ID = disc.C_Invoice_ID WHERE i.C_Invoice_ID = il.C_Invoice_ID AND il.M_Product_ID = p.M_Product_ID AND i.issotrx = 'Y' AND i.docStatus IN ('CO', 'CL') AND 2=2 AND il.c_invoice_discount_id IS NULL AND p.producttype = 'S') AAA GROUP BY c_Project) serrev ON p.C_Project_ID = serrev.C_Project LEFT JOIN (SELECT c_Project, sum(amount) as amount FROM ( SELECT CASE WHEN ila.C_InvoiceLine_AcctDimension_ID IS NULL THEN i.c_project_id ELSE ila.c_project_id END AS C_Project, CASE WHEN ila.C_InvoiceLine_AcctDimension_ID IS NULL THEN COALESCE(il.linenetamt,0) * (1 + (CASE WHEN i.totallines IS NULL OR disc.dis_amt IS NULL THEN 0 ELSE (COALESCE(disc.dis_amt,0)/(ABS(i.totallines) + ABS(disc.dis_amt))) END)) ELSE COALESCE(ila.amt,0) * (1 + (CASE WHEN i.totallines IS NULL OR disc.dis_amt IS NULL THEN 0 ELSE (COALESCE(disc.dis_amt,0)/(ABS(i.totallines) + ABS(disc.dis_amt))) END)) END AS amount FROM M_Product p, C_InvoiceLine il LEFT JOIN C_InvoiceLine_AcctDimension ila on il.C_InvoiceLine_ID = ila.C_InvoiceLine_ID, C_Invoice i left join (SELECT c_invoice_id, sum(linenetamt) as dis_amt FROM c_invoiceline WHERE c_invoice_discount_id IS NOT NULL GROUP BY c_invoice_id) disc ON i.C_Invoice_ID = disc.C_Invoice_ID WHERE i.C_Invoice_ID = il.C_Invoice_ID AND il.M_Product_ID = p.M_Product_ID AND i.issotrx = 'Y' AND i.docStatus IN ('CO', 'CL') AND 3=3 AND il.c_invoice_discount_id IS NULL AND p.producttype <> 'S') BBB GROUP BY c_Project) exprei ON p.C_Project_ID = exprei.C_Project LEFT JOIN (SELECT c_Project, sum(amount) as amount FROM ( SELECT CASE WHEN ila.C_InvoiceLine_AcctDimension_ID IS NULL THEN i.c_project_id ELSE ila.c_project_id END AS C_Project, CASE WHEN ila.C_InvoiceLine_AcctDimension_ID IS NULL THEN COALESCE(il.linenetamt,0) * (1 + (CASE WHEN i.totallines IS NULL OR disc.dis_amt IS NULL THEN 0 ELSE (COALESCE(disc.dis_amt,0)/(ABS(i.totallines) + ABS(disc.dis_amt))) END)) ELSE COALESCE(ila.amt,0) * (1 + (CASE WHEN i.totallines IS NULL OR disc.dis_amt IS NULL THEN 0 ELSE (COALESCE(disc.dis_amt,0)/(ABS(i.totallines) + ABS(disc.dis_amt))) END)) END AS amount FROM M_Product p, C_InvoiceLine il LEFT JOIN C_InvoiceLine_AcctDimension ila on il.C_InvoiceLine_ID = ila.C_InvoiceLine_ID, C_Invoice i left join (SELECT c_invoice_id, sum(linenetamt) as dis_amt FROM c_invoiceline WHERE c_invoice_discount_id IS NOT NULL GROUP BY c_invoice_id) disc ON i.C_Invoice_ID = disc.C_Invoice_ID WHERE i.C_Invoice_ID = il.C_Invoice_ID AND il.M_Product_ID = p.M_Product_ID AND i.docStatus IN ('CO', 'CL') AND 4=4 AND i.issotrx = 'N') CCC GROUP BY c_Project) expexp ON p.C_Project_ID = expexp.C_Project LEFT JOIN (SELECT c_Project, sum(amount) as amount FROM ( SELECT CASE WHEN ila.C_InvoiceLine_AcctDimension_ID IS NULL THEN i.c_project_id ELSE ila.c_project_id END AS C_Project, CASE WHEN ila.C_InvoiceLine_AcctDimension_ID IS NULL THEN il.linenetamt*C_Invoice_Percentage_Paid(i.C_Invoice_ID) ELSE ila.amt*C_Invoice_Percentage_Paid(i.C_Invoice_ID) END AS amount FROM C_Invoice i, C_InvoiceLine il LEFT JOIN C_InvoiceLine_AcctDimension ila on il.C_InvoiceLine_ID = ila.C_InvoiceLine_ID, M_Product p WHERE i.C_Invoice_ID = il.C_Invoice_ID AND il.M_Product_ID = p.M_Product_ID AND i.docStatus IN ('CO', 'CL') AND 5=5 AND i.issotrx = 'Y') DDD GROUP BY c_Project) collected ON p.C_Project_ID = collected.C_Project WHERE p.C_BPartner_ID = bpclient.C_BPartner_ID AND p.AD_Org_ID = org.AD_Org_ID AND p.AD_Org_ID IN (1000000,1000002,1000003,1000004,1000005,1000006,1000008,1000009,1000007) AND p.AD_Client_ID IN ('0','1000000') AND 6=6 ORDER BY orgname, partner, initdateException:org.postgresql.util.PSQLException: ERROR: IN types character varying and integer cannot be matched
07:15:40 [ajp-8009-5] ERROR org.openbravo.erpCommon.ad_reports.ReportProjectProfitabilityJR - Error captured: javax.servlet.ServletException: @CODE=0@ERROR: IN types character varying and integer cannot be matched

07:16:51 [ajp-8009-1] ERROR org.openbravo.erpCommon.ad_reports.ReportExpenseData - SQL error in query: SELECT CBE.NAME AS EMPLOYEE, (CASE S_L.ISTIMEREPORT WHEN 'Y' THEN S_L.QTY ELSE 0 END) * C_CALCULATECOST_CATSALARY(C_CALCULATE_CATSALARY(s.c_bpartner_id, (CASE WHEN S_L.DATEEXPENSE IS NULL THEN S.DATEREPORT ELSE S_L.DATEEXPENSE END)), (CASE WHEN S_L.DATEEXPENSE IS NULL THEN S.DATEREPORT ELSE S_L.DATEEXPENSE END)) AS COST, CBC.NAME AS NAME, (P.VALUE || ' - ' ||P.NAME) AS DESCR, M_PRODUCT.NAME AS PRODUCTNAME, C_UOM.NAME AS UOMNAME, (CASE S_L.ISTIMEREPORT WHEN 'N' THEN S_L.QTY ELSE 0 END) AS QTY, (CASE S_L.ISTIMEREPORT WHEN 'Y' THEN S_L.QTY ELSE 0 END) AS HORAS, (CASE WHEN S_L.DATEEXPENSE IS NULL THEN S.DATEREPORT ELSE S_L.DATEEXPENSE END) AS DATEEXPENSE, (CASE WHEN S_L.ISTIMEREPORT ='N' THEN (CASE WHEN S_L.CONVERTEDAMT IS NOT NULL THEN S_L.CONVERTEDAMT ELSE COALESCE(S_L.EXPENSEAMT,0) END) ELSE 0 END) AS INVOICEPRICE, AD_MESSAGE_GET2(S.PROCESSED,?) AS PROCESSED, (CASE WHEN S_L.DESCRIPTION IS NULL THEN S.DESCRIPTION ELSE S_L.DESCRIPTION END) AS DESCRIPTION, S_L.S_TIMEEXPENSELINE_ID, S.DOCUMENTNO FROM S_TIMEEXPENSE S, C_BPARTNER CBE, M_PRODUCT, C_UOM, S_TIMEEXPENSELINE S_L LEFT JOIN C_PROJECT P ON S_L.C_PROJECT_ID = P.C_PROJECT_ID LEFT JOIN C_BPARTNER CBC ON S_L.C_BPARTNER_ID = CBC.C_BPARTNER_ID WHERE S.S_TIMEEXPENSE_ID = S_L.S_TIMEEXPENSE_ID AND s.C_BPARTNER_ID=CBE.C_BPARTNER_ID AND S_L.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID AND S_L.C_UOM_ID = C_UOM.C_UOM_ID AND S.AD_CLIENT_ID IN ('0','1000000') AND S.AD_ORG_ID IN ('0','1000000','1000002','1000003','1000004','1000005','1000006','1000007','1000008','1000009') AND 1=1 ORDER BY NAME, DESCR, EMPLOYEE, PRODUCTNAME, DATEEXPENSE DESCException:org.postgresql.util.PSQLException: ERROR: operator does not exist: character varying = numeric
07:16:51 [ajp-8009-1] ERROR org.openbravo.erpCommon.ad_reports.ReportExpense - Error captured: javax.servlet.ServletException: @CODE=0@ERROR: operator does not exist: character varying = numeric
Steps To Reproduce1) Go to 'Project & Service Management || Analysis Tools || Project Profitability' and run the report.
2) Go to 'Project & Service Management || Analysis Tools || Expense Report' and run the report.
TagsUUID project
Attached Files

- Relationships Relation Graph ] Dependency Graph ]

-  Notes
(0009026)
svnbot (reporter)
2008-09-16 09:56

Repository: openbravo
Revision: 7343
Author: rafaroda
Date: 2008-09-16 09:55:52 +0200 (Tue, 16 Sep 2008)

Fixes bug 0005047 Now 'Project Profitability' and 'Expense Report' work in PostgreSQL and UUID.

---
U trunk/src/org/openbravo/erpCommon/ad_reports/ReportProjectProfitabilityJR.java
U trunk/src-db/database/model/functions/C_CALCULATECOST_CATSALARY.xml
U trunk/src-db/database/model/functions/C_CALCULATE_CATSALARY.xml
---

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

- Issue History
Date Modified Username Field Change
2008-09-15 09:39 rafaroda New Issue
2008-09-15 09:39 rafaroda Assigned To => rafaroda
2008-09-15 09:39 rafaroda sf_bug_id 0 => 2111720
2008-09-15 09:39 rafaroda Regression testing => No
2008-09-15 09:40 rafaroda Tag Attached: UUID project
2008-09-16 09:56 svnbot Checkin
2008-09-16 09:56 svnbot Note Added: 0009026
2008-09-16 09:56 svnbot Status new => resolved
2008-09-16 09:56 svnbot Resolution open => fixed
2008-09-16 09:56 svnbot svn_revision => 7343
2008-12-03 13:57 krishna Status resolved => closed


Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker