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 Reproduce

1) 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.

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

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 [^]

Relationships [ Relation Graph ] [ Dependency Graph ]