0012431: It is not possible to execute Project profitability

diff -r 6a836f38131f src/org/openbravo/erpCommon/ad_reports/ReportProjectProfitability_data.xsql --- a/src/org/openbravo/erpCommon/ad_reports/ReportProjectProfitability_data.xsql Wed Oct 28 10:19:13 2009 +0100 +++ b/src/org/openbravo/erpCommon/ad_reports/ReportProjectProfitability_data.xsql Wed Feb 24 17:42:38 2010 +0100 @@ -61,11 +61,13 @@ ELSE COALESCE(C_CURRENCY_CONVERT(ila.amt, ia.C_CURRENCY_ID, ?, TO_DATE(COALESCE(ia.DATEINVOICED, NOW())), NULL, ila.AD_CLIENT_ID, ila.AD_ORG_ID),0) * (1 + (CASE WHEN ia.totallines IS NULL OR disc.dis_amt IS NULL THEN 0 ELSE (COALESCE(disc.dis_amt,0)/(C_CURRENCY_CONVERT(ia.totallines, ia.C_CURRENCY_ID, ?, TO_DATE(COALESCE(ia.DATEINVOICED, NOW())), NULL, ia.AD_CLIENT_ID, ia.AD_ORG_ID) - 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 ia left join (SELECT il.c_invoice_id, sum(C_CURRENCY_CONVERT(il.linenetamt, ia.C_CURRENCY_ID, ?, TO_DATE(COALESCE(ia.DATEINVOICED, NOW())), NULL, il.AD_CLIENT_ID, il.AD_ORG_ID)) as dis_amt - FROM c_invoiceline il, c_invoice ia - WHERE il.c_invoice_discount_id IS NOT NULL - AND il.c_invoice_id= ia.c_invoice_id - GROUP BY il.c_invoice_id) disc ON ia.C_Invoice_ID = disc.C_Invoice_ID + C_Invoice ia left join (SELECT il1.c_invoice_id, sum(C_CURRENCY_CONVERT(il1.linenetamt, ia.C_CURRENCY_ID, ?, TO_DATE(COALESCE(ia.DATEINVOICED, NOW())), NULL, il1.AD_CLIENT_ID, il1.AD_ORG_ID)) as dis_amt + FROM c_invoiceline il1, c_invoice ia + WHERE il1.c_invoice_discount_id IS NOT NULL + AND il1.c_invoice_id= ia.c_invoice_id + AND il1.AD_Org_ID IN ('10') + AND il1.AD_Client_ID IN ('10') + GROUP BY il1.c_invoice_id) disc ON ia.C_Invoice_ID = disc.C_Invoice_ID WHERE ia.C_Invoice_ID = il.C_Invoice_ID AND il.M_Product_ID = p.M_Product_ID AND ia.issotrx = 'Y' @@ -86,11 +88,13 @@ ELSE COALESCE(C_CURRENCY_CONVERT(ila.amt, ib.C_CURRENCY_ID, ?, TO_DATE(COALESCE(ib.DATEINVOICED, NOW())), NULL, ila.AD_CLIENT_ID, ila.AD_ORG_ID),0) * (1 + (CASE WHEN ib.totallines IS NULL OR disc.dis_amt IS NULL THEN 0 ELSE (COALESCE(disc.dis_amt,0)/(C_CURRENCY_CONVERT(ib.totallines, ib.C_CURRENCY_ID, ?, TO_DATE(COALESCE(ib.DATEINVOICED, NOW())), NULL, ib.AD_CLIENT_ID, ib.AD_ORG_ID) - 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 ib left join (SELECT il.c_invoice_id, sum(C_CURRENCY_CONVERT(il.linenetamt, ib.C_CURRENCY_ID, ?, TO_DATE(COALESCE(ib.DATEINVOICED, NOW())), NULL, il.AD_CLIENT_ID, il.AD_ORG_ID)) as dis_amt - FROM c_invoiceline il, c_invoice ib - WHERE il.c_invoice_discount_id IS NOT NULL - AND il.c_invoice_id= ib.c_invoice_id - GROUP BY il.c_invoice_id) disc ON ib.C_Invoice_ID = disc.C_Invoice_ID + C_Invoice ib left join (SELECT il2.c_invoice_id, sum(C_CURRENCY_CONVERT(il2.linenetamt, ib.C_CURRENCY_ID, ?, TO_DATE(COALESCE(ib.DATEINVOICED, NOW())), NULL, il2.AD_CLIENT_ID, il2.AD_ORG_ID)) as dis_amt + FROM c_invoiceline il2, c_invoice ib + WHERE il2.c_invoice_discount_id IS NOT NULL + AND il2.c_invoice_id= ib.c_invoice_id + AND il2.AD_Org_ID IN ('11') + AND il2.AD_Client_ID IN ('11') + GROUP BY il2.c_invoice_id) disc ON ib.C_Invoice_ID = disc.C_Invoice_ID WHERE ib.C_Invoice_ID = il.C_Invoice_ID AND il.M_Product_ID = p.M_Product_ID AND ib.issotrx = 'Y' @@ -111,11 +115,13 @@ ELSE COALESCE(C_CURRENCY_CONVERT(ila.amt, ic.C_CURRENCY_ID, ?, TO_DATE(COALESCE(ic.DATEINVOICED, NOW())), NULL, ila.AD_CLIENT_ID, ila.AD_ORG_ID),0) * (1 + (CASE WHEN ic.totallines IS NULL OR disc.dis_amt IS NULL THEN 0 ELSE (COALESCE(disc.dis_amt,0)/(C_CURRENCY_CONVERT(ic.totallines, ic.C_CURRENCY_ID, ?, TO_DATE(COALESCE(ic.DATEINVOICED, NOW())), NULL, ic.AD_CLIENT_ID, ic.AD_ORG_ID) - 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 ic left join (SELECT il.c_invoice_id, sum(C_CURRENCY_CONVERT(il.linenetamt, ic.C_CURRENCY_ID, ?, TO_DATE(COALESCE(ic.DATEINVOICED, NOW())), NULL, il.AD_CLIENT_ID, il.AD_ORG_ID)) as dis_amt - FROM c_invoiceline il, c_invoice ic - WHERE il.c_invoice_discount_id IS NOT NULL - AND il.c_invoice_id= ic.c_invoice_id - GROUP BY il.c_invoice_id) disc ON ic.C_Invoice_ID = disc.C_Invoice_ID + C_Invoice ic left join (SELECT il3.c_invoice_id, sum(C_CURRENCY_CONVERT(il3.linenetamt, ic.C_CURRENCY_ID, ?, TO_DATE(COALESCE(ic.DATEINVOICED, NOW())), NULL, il3.AD_CLIENT_ID, il3.AD_ORG_ID)) as dis_amt + FROM c_invoiceline il3, c_invoice ic + WHERE il3.c_invoice_discount_id IS NOT NULL + AND il3.c_invoice_id= ic.c_invoice_id + AND il3.AD_Org_ID IN ('12') + AND il3.AD_Client_ID IN ('12') + GROUP BY il3.c_invoice_id) disc ON ic.C_Invoice_ID = disc.C_Invoice_ID WHERE ic.C_Invoice_ID = il.C_Invoice_ID AND il.M_Product_ID = p.M_Product_ID AND ic.processed = 'Y' @@ -164,8 +170,11 @@ <Parameter name="cCurrencyConv"/> <Parameter name="cCurrencyConv"/> <Parameter name="cCurrencyConv"/> + <Parameter name="org" optional="true" type="replace" after="AND il1.AD_Org_ID IN (" text="'10'"/> + <Parameter name="client" optional="true" type="replace" after="AND il1.AD_Client_ID IN (" text="'10'"/> <Parameter name="datefrom2" optional="true" after="AND 2=2"><![CDATA[ AND ia.dateinvoiced >= to_date(?) ]]></Parameter> <Parameter name="dateto2" optional="true" after="AND 2=2"><![CDATA[ AND ia.dateinvoiced < to_date(?) ]]></Parameter> + <Parameter name="org" optional="true" type="replace" after="AND ia.AD_Org_ID IN (" text="'1'"/> <Parameter name="client" optional="true" type="replace" after="AND ia.AD_Client_ID IN (" text="'1'"/> <Parameter name="cCurrencyConv"/> @@ -173,8 +182,11 @@ <Parameter name="cCurrencyConv"/> <Parameter name="cCurrencyConv"/> <Parameter name="cCurrencyConv"/> + <Parameter name="org" optional="true" type="replace" after="AND il2.AD_Org_ID IN (" text="'11'"/> + <Parameter name="client" optional="true" type="replace" after="AND il2.AD_Client_ID IN (" text="'11'"/> <Parameter name="datefrom2" optional="true" after="AND 3=3"><![CDATA[ AND ib.dateinvoiced >= to_date(?) ]]></Parameter> <Parameter name="dateto2" optional="true" after="AND 3=3"><![CDATA[ AND ib.dateinvoiced < to_date(?) ]]></Parameter> + <Parameter name="org" optional="true" type="replace" after="AND ib.AD_Org_ID IN (" text="'2'"/> <Parameter name="client" optional="true" type="replace" after="AND ib.AD_Client_ID IN (" text="'2'"/> <Parameter name="cCurrencyConv"/> @@ -182,8 +194,11 @@ <Parameter name="cCurrencyConv"/> <Parameter name="cCurrencyConv"/> <Parameter name="cCurrencyConv"/> + <Parameter name="org" optional="true" type="replace" after="AND il3.AD_Org_ID IN (" text="'12'"/> + <Parameter name="client" optional="true" type="replace" after="AND il3.AD_Client_ID IN (" text="'12'"/> <Parameter name="datefrom2" optional="true" after="AND 4=4"><![CDATA[ AND ic.dateinvoiced >= to_date(?) ]]></Parameter> <Parameter name="dateto2" optional="true" after="AND 4=4"><![CDATA[ AND ic.dateinvoiced < to_date(?) ]]></Parameter> + <Parameter name="org" optional="true" type="replace" after="AND ic.AD_Org_ID IN (" text="'3'"/> <Parameter name="client" optional="true" type="replace" after="AND ic.AD_Client_ID IN (" text="'3'"/> <Parameter name="cCurrencyConv"/>

Notes
(0024790) rafaroda (viewer) 2010-02-24 17:28	Please have a look at issue 0009043 since it seems very related. In some place in the report there it a proper filter by client and organization.

(0024792) rafaroda (viewer) 2010-02-24 17:40	I attached a possible cause of the problem (please notice that this diff is NOT the fix).

(0024795) rafaroda (viewer) 2010-02-24 17:45	If issue 0009043 is duplicated please close issue 0009043 rather than this one (since this one is OBPS).

(0025117) sureshbabu (viewer) 2010-03-08 11:42	Hi To test the issue, you need to create a new client other than the Smallbazaar(which comes along with sample data)

(0025176) hgbot (developer) 2010-03-09 13:27	Repository: erp/devel/pi Changeset: dbfe99032a08d06a12e1c55b007eb9acca30115b Author: Sivaraman Rajagopal <sivaraman.rajagopal <at> openbravo.com> Date: Tue Mar 09 17:59:31 2010 +0530 URL: http://code.openbravo.com/erp/devel/pi/rev/dbfe99032a08d06a12e1c55b007eb9acca30115b [^] Fixes issue 12431: It is not possible to execute Project profitability --- M src/org/openbravo/erpCommon/ad_reports/ReportProjectProfitability_data.xsql ---

(0025201) sivaraman (viewer) 2010-03-10 06:37	Issue: It is not possible to execute Project profitability for client than Smallbazaar and other currency than EUR. Steps to reproduce: 1. Log on to Openbravo 2. Change user role to Accounting Test Admin and entity to Accounting Test 3. Go to Project and Service management > Analysis tools > Project Profitability 4. Set "Accounting test Org"as organization 5. Set "Currency" as the client base currency (other than EUR) 6. Click "HTML Format" button

(0025206) rafaroda (viewer) 2010-03-10 11:00	Should we close issue 0009043 as duplicated? Thanks.

(0025410) hudsonbot (viewer) 2010-03-12 22:41	A changeset related to this issue has been promoted to main after passing a series of tests and an OBX has been generated: Changeset: http://code.openbravo.com/erp/devel/main/rev/dbfe99032a08 [^] Merge Changeset: http://code.openbravo.com/erp/devel/main/rev/631c626866c1 [^] Tests: http://builds.openbravo.com/view/devel-int/ [^] OBX: http://builds.openbravo.com/erp/core/obx/OpenbravoERP-2.50CI.16685.obx [^]

(0025447) arunkumar (viewer) 2010-03-15 08:38	Tested & Working fine

Issue History
Date Modified	Username	Field	Change
2010-02-24 10:33	networkb	New Issue
2010-02-24 10:33	networkb	Assigned To	=> adrianromero
2010-02-24 10:33	networkb	OBNetwork customer	=> Yes
2010-02-24 17:27	rafaroda	Relationship added	related to 0009043
2010-02-24 17:28	rafaroda	Note Added: 0024790
2010-02-24 17:40	rafaroda	Note Added: 0024792
2010-02-24 17:41	rafaroda	File Added: issue-12431-possible-patch.diff
2010-02-24 17:41	rafaroda	Issue Monitored: rafaroda
2010-02-24 17:45	rafaroda	Note Added: 0024795
2010-02-24 17:47	rafaroda	Relationship added	related to 0012014
2010-02-24 18:05	adrianromero	Status	new => scheduled
2010-02-24 18:05	adrianromero	fix_in_branch	=> pi
2010-03-08 11:42	sureshbabu	Note Added: 0025117
2010-03-08 16:05	jpabloae	Target Version	2.50MP13 => 2.50MP14
2010-03-09 13:27	hgbot	Checkin
2010-03-09 13:27	hgbot	Note Added: 0025176
2010-03-09 13:27	hgbot	Status	scheduled => resolved
2010-03-09 13:27	hgbot	Resolution	open => fixed
2010-03-09 13:27	hgbot	Fixed in SCM revision	=> http://code.openbravo.com/erp/devel/pi/rev/dbfe99032a08d06a12e1c55b007eb9acca30115b [^]
2010-03-10 06:37	sivaraman	Note Added: 0025201
2010-03-10 11:00	rafaroda	Note Added: 0025206
2010-03-10 18:16	adrianromero	Relationship replaced	has duplicate 0009043
2010-03-12 22:41	hudsonbot	Checkin
2010-03-12 22:41	hudsonbot	Note Added: 0025410
2010-03-15 08:38	arunkumar	Note Added: 0025447
2010-03-15 08:38	arunkumar	Status	resolved => closed
2010-03-16 00:00	anonymous	sf_bug_id	0 => 2970935