issue 19771: Wrong conversion rate used in the Sales Dimensional Report.
authorJavier Etxarri <javier.echarri@openbravo.com>
Sat, 18 Feb 2012 14:51:00 +0100
changeset 15545 15271104ea08
parent 15544 d5795a102734
child 15546 4a020fd924d0
issue 19771: Wrong conversion rate used in the Sales Dimensional Report.
We find more reports with the same problem, and apart from that, we have put the column c_orderline.dateordered like deprecated
src-db/database/sourcedata/AD_COLUMN.xml
src/org/openbravo/erpCommon/ad_reports/OrderEdition_data.xsql
src/org/openbravo/erpCommon/ad_reports/ReportOrderNotInvoice_data.xsql
src/org/openbravo/erpCommon/ad_reports/ReportPurchaseOrder.jrxml
src/org/openbravo/erpCommon/ad_reports/ReportSalesOrderInvoicedJasper.jrxml
src/org/openbravo/erpCommon/ad_reports/ReportSalesOrderInvoiced_data.xsql
src/org/openbravo/erpCommon/ad_reports/ReportSalesOrderJR.jrxml
src/org/openbravo/erpCommon/ad_reports/ReportSalesOrderProvidedJR.jrxml
src/org/openbravo/erpCommon/ad_reports/ReportSalesOrderProvided_data.xsql
--- a/src-db/database/sourcedata/AD_COLUMN.xml	Fri Feb 17 12:42:15 2012 +0100
+++ b/src-db/database/sourcedata/AD_COLUMN.xml	Sat Feb 18 14:51:00 2012 +0100
@@ -30872,7 +30872,7 @@
 <!--2216-->  <ISSESSIONATTR><![CDATA[Y]]></ISSESSIONATTR>
 <!--2216-->  <ISSECONDARYKEY><![CDATA[N]]></ISSECONDARYKEY>
 <!--2216-->  <ISDESENCRYPTABLE><![CDATA[N]]></ISDESENCRYPTABLE>
-<!--2216-->  <DEVELOPMENTSTATUS><![CDATA[RE]]></DEVELOPMENTSTATUS>
+<!--2216-->  <DEVELOPMENTSTATUS><![CDATA[DP]]></DEVELOPMENTSTATUS>
 <!--2216-->  <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID>
 <!--2216-->  <POSITION><![CDATA[13]]></POSITION>
 <!--2216-->  <ISTRANSIENT><![CDATA[N]]></ISTRANSIENT>
--- a/src/org/openbravo/erpCommon/ad_reports/OrderEdition_data.xsql	Fri Feb 17 12:42:15 2012 +0100
+++ b/src/org/openbravo/erpCommon/ad_reports/OrderEdition_data.xsql	Sat Feb 18 14:51:00 2012 +0100
@@ -31,14 +31,14 @@
       SELECT C_ORDER.DOCUMENTNO AS DOCUMENTNO, C_ORDER.DATEORDERED AS DATEORDERED, C_BPARTNER.NAME AS CLIENT_NAME, SUM(C_ORDERLINE.QTYORDERED) AS QUANTITYORDER, 
       C_ORDERLINE.PRICEACTUAL AS PRICEACTUAL, 
       C_CURRENCY_SYMBOL (COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), C_ORDERLINE.PRICEACTUAL, 'Y') AS PRICEACTUALSYM,	  
-	  C_CURRENCY_CONVERT(C_ORDERLINE.PRICEACTUAL, COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), ?, TO_DATE(COALESCE(C_ORDERLINE.DATEORDERED, C_ORDER.DATEORDERED, NOW())), NULL, C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID) AS CONVPRICEACTUAL,      
+	  C_CURRENCY_CONVERT(C_ORDERLINE.PRICEACTUAL, COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), ?, TO_DATE(COALESCE(C_ORDER.DATEORDERED, NOW())), NULL, C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID) AS CONVPRICEACTUAL,      
       SUM(C_ORDERLINE.LINENETAMT) AS PRICELIST,
       C_CURRENCY_SYMBOL (COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), SUM(C_ORDERLINE.LINENETAMT), 'Y') AS PRICELISTSYM,
-	  SUM(C_CURRENCY_CONVERT(C_ORDERLINE.LINENETAMT, COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), ?, TO_DATE(COALESCE(C_ORDERLINE.DATEORDERED, C_ORDER.DATEORDERED, NOW())), NULL, C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID)) AS CONVPRICELIST, 
+	  SUM(C_CURRENCY_CONVERT(C_ORDERLINE.LINENETAMT, COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), ?, TO_DATE(COALESCE(C_ORDER.DATEORDERED, NOW())), NULL, C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID)) AS CONVPRICELIST, 
       M_PRODUCT.NAME AS PRODUCT_NAME, C_UOM.NAME AS UOMNAME,
       C_CURRENCY_SYMBOL(?, '0', 'Y') AS CONVSYM,	  
 	  C_CURRENCY_ISOSYM(?) AS CONVISOSYM,	  
-	  TO_DATE(COALESCE(C_ORDERLINE.DATEORDERED, C_ORDER.DATEORDERED, NOW())) AS TRANSDATE,	  
+	  TO_DATE(COALESCE(C_ORDER.DATEORDERED, NOW())) AS TRANSDATE,	  
 	  COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID) AS TRANSCURRENCYID,      
 	  C_ORDERLINE.AD_CLIENT_ID AS TRANSCLIENTID, 	  
 	  C_ORDERLINE.AD_ORG_ID AS TRANSORGID 
@@ -54,7 +54,7 @@
       AND 1=1
       AND C_ORDER.ISSOTRX = 'Y'
       GROUP BY C_BPARTNER.NAME, C_ORDER.DOCUMENTNO, M_PRODUCT.NAME,C_ORDERLINE.PRICEACTUAL, C_UOM.NAME, C_ORDER.DATEORDERED,
-      COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), TO_DATE(COALESCE(C_ORDERLINE.DATEORDERED, C_ORDER.DATEORDERED, NOW())),
+      COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), TO_DATE(COALESCE(C_ORDER.DATEORDERED, NOW())),
 	  C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID
 	  ORDER BY C_BPARTNER.NAME, C_ORDER.DOCUMENTNO, M_PRODUCT.NAME,C_ORDERLINE.PRICEACTUAL, C_UOM.NAME, C_ORDER.DATEORDERED
      ]]></Sql>
--- a/src/org/openbravo/erpCommon/ad_reports/ReportOrderNotInvoice_data.xsql	Fri Feb 17 12:42:15 2012 +0100
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportOrderNotInvoice_data.xsql	Sat Feb 18 14:51:00 2012 +0100
@@ -38,9 +38,9 @@
 	  C_CURRENCY_CONVERT(C_ORDER.GRANDTOTAL, C_ORDER.C_CURRENCY_ID, ?, TO_DATE(COALESCE(C_ORDER.DATEORDERED, NOW())), NULL, C_ORDER.AD_CLIENT_ID, C_ORDER.AD_ORG_ID) AS CONVGRANDTOTAL, 
 	  (CASE C_ORDER.INVOICERULE WHEN 'S' THEN COALESCE(AD_REF_LIST_TRL.NAME, AD_REF_LIST.NAME)||' ('||C_INVOICESCHEDULE.NAME||')' ELSE COALESCE(AD_REF_LIST_TRL.NAME, AD_REF_LIST.NAME) END) AS INVOICERULE,      
 	  C_ORDERLINE.LINE AS LINE, COALESCE(M_PRODUCT.NAME, M_PRODUCT.DESCRIPTION) AS PRODUCT, C_ORDERLINE.PRICEACTUAL AS PRICE,
-	  C_CURRENCY_CONVERT(C_ORDERLINE.PRICEACTUAL, COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), ?, TO_DATE(COALESCE(C_ORDERLINE.DATEORDERED, C_ORDER.DATEORDERED, NOW())), NULL, C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID) AS CONVPRICE,        
+	  C_CURRENCY_CONVERT(C_ORDERLINE.PRICEACTUAL, COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), ?, TO_DATE(COALESCE(C_ORDER.DATEORDERED, NOW())), NULL, C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID) AS CONVPRICE,        
 	  C_ORDERLINE.QTYORDERED, C_UOM.UOMSYMBOL, NULL AS TAX, NULL AS TAXBASE, NULL AS CONVTAXBASE, C_ORDERLINE.LINENETAMT,
-	  C_CURRENCY_CONVERT(C_ORDERLINE.LINENETAMT, COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), ?, TO_DATE(COALESCE(C_ORDERLINE.DATEORDERED, C_ORDER.DATEORDERED, NOW())), NULL, C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID) AS CONVLINENETAMT,        
+	  C_CURRENCY_CONVERT(C_ORDERLINE.LINENETAMT, COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), ?, TO_DATE(COALESCE(C_ORDER.DATEORDERED, NOW())), NULL, C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID) AS CONVLINENETAMT,        
 	  C_CURRENCY_SYMBOL(C_ORDER.C_CURRENCY_ID, 0, 'Y') AS ORDERCURRENCYSYM,
 	  C_ORDER.C_CURRENCY_ID AS TRANSCURRENCYIDORDER,
 	  C_ORDER.DATEORDERED AS TRANSDATEORDER,
@@ -48,7 +48,7 @@
 	  C_ORDER.AD_ORG_ID AS TRANSORGIDORDER,
 	  C_CURRENCY_SYMBOL(COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), 0, 'Y') AS LINECURRENCYSYM,
 	  COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID) AS TRANSCURRENCYIDLINE,
-	  TO_DATE(COALESCE(C_ORDERLINE.DATEORDERED, C_ORDER.DATEORDERED, NOW())) AS TRANSDATELINE,
+	  TO_DATE(COALESCE(C_ORDER.DATEORDERED, NOW())) AS TRANSDATELINE,
 	  C_ORDERLINE.AD_CLIENT_ID AS TRANSCLIENTIDLINE,
 	  C_ORDERLINE.AD_ORG_ID AS TRANSORGIDLINE 
       FROM C_BPARTNER left join C_INVOICESCHEDULE on C_BPARTNER.C_INVOICESCHEDULE_ID = C_INVOICESCHEDULE.C_INVOICESCHEDULE_ID,
--- a/src/org/openbravo/erpCommon/ad_reports/ReportPurchaseOrder.jrxml	Fri Feb 17 12:42:15 2012 +0100
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportPurchaseOrder.jrxml	Sat Feb 18 14:51:00 2012 +0100
@@ -84,12 +84,12 @@
 	FROM (
 	SELECT C_ORDER.DOCUMENTNO AS DOCUMENTNO, C_ORDER.DATEORDERED AS DATEORDERED, C_BPARTNER.NAME AS CLIENT_NAME, 
 	SUM(C_ORDERLINE.QTYORDERED) AS QUANTITYORDER, C_ORDERLINE.PRICEACTUAL AS PRICEACTUAL,
-	C_CURRENCY_CONVERT(C_ORDERLINE.PRICEACTUAL, COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), $P{C_Currency_ID}, TO_DATE(COALESCE(C_ORDERLINE.DATEORDERED, C_ORDER.DATEORDERED, NOW())), NULL, C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID) AS CONVPRICEACTUAL,
+	C_CURRENCY_CONVERT(C_ORDERLINE.PRICEACTUAL, COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), $P{C_Currency_ID}, TO_DATE(COALESCE(C_ORDER.DATEORDERED, NOW())), NULL, C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID) AS CONVPRICEACTUAL,
 	SUM(C_ORDERLINE.LINENETAMT) AS PRICELIST, 
-	C_CURRENCY_CONVERT(SUM(C_ORDERLINE.LINENETAMT), COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), $P{C_Currency_ID}, TO_DATE(COALESCE(C_ORDERLINE.DATEORDERED, C_ORDER.DATEORDERED, NOW())), NULL, C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID) AS CONVPRICELIST,
+	C_CURRENCY_CONVERT(SUM(C_ORDERLINE.LINENETAMT), COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), $P{C_Currency_ID}, TO_DATE(COALESCE(C_ORDER.DATEORDERED, NOW())), NULL, C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID) AS CONVPRICELIST,
 	M_PRODUCT.NAME AS PRODUCT_NAME, C_UOM.NAME AS UOMNAME,
 	COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID) AS TRANSCURRENCYID,
-	TO_DATE(COALESCE(C_ORDERLINE.DATEORDERED, C_ORDER.DATEORDERED, NOW())) AS TRANSDATE,
+	TO_DATE(COALESCE(C_ORDER.DATEORDERED, NOW())) AS TRANSDATE,
 	C_ORDERLINE.AD_CLIENT_ID AS TRANSCLIENTID, 
 	C_ORDERLINE.AD_ORG_ID AS TRANSORGID
 	FROM C_ORDER, C_ORDERLINE, C_BPARTNER, M_PRODUCT, C_UOM
@@ -107,8 +107,7 @@
 	AND C_ORDER.AD_CLIENT_ID IN ($P!{USER_CLIENT})
 	AND C_ORDER.AD_ORG_ID IN ($P!{USER_ORG})
 	GROUP BY C_BPARTNER.NAME, C_ORDERLINE.PRICEACTUAL, M_PRODUCT.NAME, C_UOM.NAME, C_ORDER.DOCUMENTNO, 
-	C_ORDER.DATEORDERED, C_ORDERLINE.DATEORDERED,
-	C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID,
+	C_ORDER.DATEORDERED, C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID,
 	C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID
 	) ZZ
 	GROUP BY CLIENT_NAME, PRICEACTUAL, CONVPRICEACTUAL, PRODUCT_NAME, UOMNAME, DOCUMENTNO, DATEORDERED,
--- a/src/org/openbravo/erpCommon/ad_reports/ReportSalesOrderInvoicedJasper.jrxml	Fri Feb 17 12:42:15 2012 +0100
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportSalesOrderInvoicedJasper.jrxml	Sat Feb 18 14:51:00 2012 +0100
@@ -62,14 +62,14 @@
       M_PRODUCT.NAME AS PRODUCTNAME, SUM(C_ORDERLINE.QTYINVOICED) AS QTYINVOICED,
       SUM(C_ORDERLINE.QTYINVOICED*C_ORDERLINE.PRICEACTUAL) AS LINENETAMT, 
       C_CURRENCY_SYMBOL(COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), C_ORDERLINE.PRICEACTUAL, 'Y') AS LINENETAMTSYM,	  
-	  SUM(C_CURRENCY_CONVERT(C_ORDERLINE.QTYINVOICED*C_ORDERLINE.PRICEACTUAL, COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), '102', TO_DATE(COALESCE(C_ORDERLINE.DATEORDERED, C_ORDER.DATEORDERED, NOW())), NULL, C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID)) AS CONVLINENETAMT,
+	  SUM(C_CURRENCY_CONVERT(C_ORDERLINE.QTYINVOICED*C_ORDERLINE.PRICEACTUAL, COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), '102', TO_DATE(COALESCE(C_ORDER.DATEORDERED, NOW())), NULL, C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID)) AS CONVLINENETAMT,
       C_UOM.NAME AS UOMNAME, 
       C_ORDERLINE.PRICEACTUAL, 
       C_CURRENCY_SYMBOL(COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), C_ORDERLINE.PRICEACTUAL, 'Y') AS PRICEACTUALSYM,	  	  
-	  C_CURRENCY_CONVERT(C_ORDERLINE.PRICEACTUAL, COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), '102', TO_DATE(COALESCE(C_ORDERLINE.DATEORDERED, C_ORDER.DATEORDERED, NOW())), NULL, C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID) AS CONVPRICEACTUAL,
+	  C_CURRENCY_CONVERT(C_ORDERLINE.PRICEACTUAL, COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), '102', TO_DATE(COALESCE(C_ORDER.DATEORDERED, NOW())), NULL, C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID) AS CONVPRICEACTUAL,
 	  C_CURRENCY_SYMBOL('102', '0', 'Y') AS CONVSYM,	  	  
 	  C_CURRENCY_ISOSYM('102') AS CONVISOSYM,	  	  
-	  TO_DATE(COALESCE(C_ORDERLINE.DATEORDERED, C_ORDER.DATEORDERED, NOW())) AS TRANSDATE,	  	  
+	  TO_DATE(COALESCE(C_ORDER.DATEORDERED, NOW())) AS TRANSDATE,	  	  
 	  COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID) AS TRANSCURRENCYID,      	  
 	  C_ORDERLINE.AD_CLIENT_ID AS TRANSCLIENTID, 	  	  
 	  C_ORDERLINE.AD_ORG_ID AS TRANSORGID, '' AS NAME
@@ -86,7 +86,7 @@
       AND C_ORDER.AD_ORG_ID IN ('1')
       AND 1=1
       GROUP BY COALESCE(CB.NAME, C_BPARTNER.NAME), C_PROJECT.VALUE, C_PROJECT.NAME, M_PRODUCT.NAME, C_UOM.NAME, C_ORDERLINE.PRICEACTUAL,
-      COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), TO_DATE(COALESCE(C_ORDERLINE.DATEORDERED, C_ORDER.DATEORDERED, NOW())),	  
+      COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), TO_DATE(COALESCE(C_ORDER.DATEORDERED, NOW())),	  
 	  C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID	
       HAVING SUM(C_ORDERLINE.QTYDELIVERED) > 0
       ORDER BY COALESCE(CB.NAME, C_BPARTNER.NAME), C_PROJECT.NAME, M_PRODUCT.NAME]]>
--- a/src/org/openbravo/erpCommon/ad_reports/ReportSalesOrderInvoiced_data.xsql	Fri Feb 17 12:42:15 2012 +0100
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportSalesOrderInvoiced_data.xsql	Sat Feb 18 14:51:00 2012 +0100
@@ -32,14 +32,14 @@
       M_PRODUCT.NAME AS PRODUCTNAME, CASE WHEN C_DOCTYPE.DOCBASETYPE='ARC' THEN SUM(C_ORDERLINE.QTYINVOICED)*-1 ELSE SUM(C_ORDERLINE.QTYINVOICED) END AS QTYINVOICED,
       SUM((CASE WHEN C_DOCTYPE.DOCBASETYPE='ARC' THEN C_ORDERLINE.QTYINVOICED*-1 ELSE C_ORDERLINE.QTYINVOICED END)*C_ORDERLINE.PRICEACTUAL) AS LINENETAMT, 
       C_CURRENCY_SYMBOL(COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), C_ORDERLINE.PRICEACTUAL, 'Y') AS LINENETAMTSYM,	  
-	  SUM(C_CURRENCY_CONVERT((CASE WHEN C_DOCTYPE.DOCBASETYPE='ARC' THEN C_ORDERLINE.QTYINVOICED*-1 ELSE C_ORDERLINE.QTYINVOICED END)*C_ORDERLINE.PRICEACTUAL, COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), ?, TO_DATE(COALESCE(C_ORDERLINE.DATEORDERED, C_ORDER.DATEORDERED, NOW())), NULL, C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID)) AS CONVLINENETAMT,
+	  SUM(C_CURRENCY_CONVERT((CASE WHEN C_DOCTYPE.DOCBASETYPE='ARC' THEN C_ORDERLINE.QTYINVOICED*-1 ELSE C_ORDERLINE.QTYINVOICED END)*C_ORDERLINE.PRICEACTUAL, COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), ?, TO_DATE(COALESCE(C_ORDER.DATEORDERED, NOW())), NULL, C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID)) AS CONVLINENETAMT,
       C_UOM.NAME AS UOMNAME, 
       C_ORDERLINE.PRICEACTUAL, 
       C_CURRENCY_SYMBOL(COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), C_ORDERLINE.PRICEACTUAL, 'Y') AS PRICEACTUALSYM,	  	  
-	  C_CURRENCY_CONVERT(C_ORDERLINE.PRICEACTUAL, COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), ?, TO_DATE(COALESCE(C_ORDERLINE.DATEORDERED, C_ORDER.DATEORDERED, NOW())), NULL, C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID) AS CONVPRICEACTUAL,
+	  C_CURRENCY_CONVERT(C_ORDERLINE.PRICEACTUAL, COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), ?, TO_DATE(COALESCE(C_ORDER.DATEORDERED, NOW())), NULL, C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID) AS CONVPRICEACTUAL,
 	  C_CURRENCY_SYMBOL(?, '0', 'Y') AS CONVSYM,	  	  
 	  C_CURRENCY_ISOSYM(?) AS CONVISOSYM,	  	  
-	  TO_DATE(COALESCE(C_ORDERLINE.DATEORDERED, C_ORDER.DATEORDERED, NOW())) AS TRANSDATE,	  	  
+	  TO_DATE(COALESCE(C_ORDER.DATEORDERED, NOW())) AS TRANSDATE,	  	  
 	  COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID) AS TRANSCURRENCYID,      	  
 	  C_ORDERLINE.AD_CLIENT_ID AS TRANSCLIENTID, 	  	  
 	  C_ORDERLINE.AD_ORG_ID AS TRANSORGID, '' AS NAME
@@ -58,7 +58,7 @@
       AND C_ORDER.AD_ORG_ID IN ('1')
       AND 1=1
       GROUP BY COALESCE(CB.NAME, C_BPARTNER.NAME), C_PROJECT.VALUE, C_PROJECT.NAME, M_PRODUCT.NAME, C_UOM.NAME, C_ORDERLINE.PRICEACTUAL,
-      COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), TO_DATE(COALESCE(C_ORDERLINE.DATEORDERED, C_ORDER.DATEORDERED, NOW())),	  
+      COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), TO_DATE(COALESCE(C_ORDER.DATEORDERED, NOW())),	  
 	  C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID, C_DOCTYPE.DOCBASETYPE	
       HAVING SUM(C_ORDERLINE.QTYINVOICED) > 0
       ORDER BY COALESCE(CB.NAME, C_BPARTNER.NAME), C_PROJECT.NAME, M_PRODUCT.NAME
--- a/src/org/openbravo/erpCommon/ad_reports/ReportSalesOrderJR.jrxml	Fri Feb 17 12:42:15 2012 +0100
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportSalesOrderJR.jrxml	Sat Feb 18 14:51:00 2012 +0100
@@ -64,14 +64,14 @@
 		<![CDATA[SELECT C_ORDER.DOCUMENTNO AS DOCUMENTNO, C_ORDER.DATEORDERED AS DATEORDERED, C_BPARTNER.NAME AS CLIENT_NAME, SUM(C_ORDERLINE.QTYORDERED) AS QUANTITYORDER, 
       C_ORDERLINE.PRICEACTUAL AS PRICEACTUAL, 
       C_CURRENCY_SYMBOL (COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), C_ORDERLINE.PRICEACTUAL, 'Y') AS PRICEACTUALSYM,	  
-	  C_CURRENCY_CONVERT(C_ORDERLINE.PRICEACTUAL, COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), '102', TO_DATE(COALESCE(C_ORDERLINE.DATEORDERED, C_ORDER.DATEORDERED, NOW())), NULL, C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID) AS CONVPRICEACTUAL,      
+	  C_CURRENCY_CONVERT(C_ORDERLINE.PRICEACTUAL, COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), '102', TO_DATE(COALESCE(C_ORDER.DATEORDERED, NOW())), NULL, C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID) AS CONVPRICEACTUAL,      
       SUM(C_ORDERLINE.LINENETAMT) AS PRICELIST,
       C_CURRENCY_SYMBOL (COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), C_ORDERLINE.PRICEACTUAL, 'Y') AS PRICELISTSYM,
-	  SUM(C_CURRENCY_CONVERT(C_ORDERLINE.LINENETAMT, COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), '102', TO_DATE(COALESCE(C_ORDERLINE.DATEORDERED, C_ORDER.DATEORDERED, NOW())), NULL, C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID)) AS CONVPRICELIST, 
+	  SUM(C_CURRENCY_CONVERT(C_ORDERLINE.LINENETAMT, COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), '102', TO_DATE(COALESCE(C_ORDER.DATEORDERED, NOW())), NULL, C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID)) AS CONVPRICELIST, 
       M_PRODUCT.NAME AS PRODUCT_NAME, C_UOM.NAME AS UOMNAME,
       C_CURRENCY_SYMBOL('102', '0', 'Y') AS CONVSYM,	  
 	  C_CURRENCY_ISOSYM('102') AS CONVISOSYM,	  
-	  TO_DATE(COALESCE(C_ORDERLINE.DATEORDERED, C_ORDER.DATEORDERED, NOW())) AS TRANSDATE,	  
+	  TO_DATE(COALESCE(C_ORDER.DATEORDERED, NOW())) AS TRANSDATE,	  
 	  COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID) AS TRANSCURRENCYID,      
 	  C_ORDERLINE.AD_CLIENT_ID AS TRANSCLIENTID, 	  
 	  C_ORDERLINE.AD_ORG_ID AS TRANSORGID 
@@ -87,7 +87,7 @@
       AND 1=1
       AND C_ORDER.ISSOTRX = 'Y'
       GROUP BY C_BPARTNER.NAME, C_ORDER.DOCUMENTNO, M_PRODUCT.NAME,C_ORDERLINE.PRICEACTUAL, C_UOM.NAME, C_ORDER.DATEORDERED,
-      COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), TO_DATE(COALESCE(C_ORDERLINE.DATEORDERED, C_ORDER.DATEORDERED, NOW())),
+      COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), TO_DATE(COALESCE(C_ORDER.DATEORDERED, NOW())),
 	  C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID
 	  ORDER BY C_BPARTNER.NAME, C_ORDER.DOCUMENTNO, M_PRODUCT.NAME,C_ORDERLINE.PRICEACTUAL, C_UOM.NAME, C_ORDER.DATEORDERED]]>
 	</queryString>
--- a/src/org/openbravo/erpCommon/ad_reports/ReportSalesOrderProvidedJR.jrxml	Fri Feb 17 12:42:15 2012 +0100
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportSalesOrderProvidedJR.jrxml	Sat Feb 18 14:51:00 2012 +0100
@@ -55,13 +55,13 @@
       M_PRODUCT.NAME AS PRODUCTNAME, SUM(C_ORDERLINE.QTYDELIVERED) AS QTYDELIVERED, 
       SUM(C_ORDERLINE.QTYDELIVERED*C_ORDERLINE.PRICEACTUAL) AS LINENETAMT, 
       C_CURRENCY_SYMBOL (COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), C_ORDERLINE.PRICEACTUAL, 'Y') AS LINENETAMTSYM,	  
-	  SUM(C_CURRENCY_CONVERT(C_ORDERLINE.QTYDELIVERED*C_ORDERLINE.PRICEACTUAL, COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), '102', TO_DATE(COALESCE(C_ORDERLINE.DATEORDERED, C_ORDER.DATEORDERED, NOW())), NULL, C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID)) AS CONVLINENETAMT,
+	  SUM(C_CURRENCY_CONVERT(C_ORDERLINE.QTYDELIVERED*C_ORDERLINE.PRICEACTUAL, COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), '102', TO_DATE(COALESCE(C_ORDER.DATEORDERED, NOW())), NULL, C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID)) AS CONVLINENETAMT,
       C_UOM.NAME AS UOMNAME, C_ORDERLINE.PRICEACTUAL, 
       C_CURRENCY_SYMBOL (COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), C_ORDERLINE.PRICEACTUAL, 'Y') AS PRICEACTUALSYM,	  	  
-	  C_CURRENCY_CONVERT(C_ORDERLINE.PRICEACTUAL, COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), '102', TO_DATE(COALESCE(C_ORDERLINE.DATEORDERED, C_ORDER.DATEORDERED, NOW())), NULL, C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID) AS CONVPRICEACTUAL,
+	  C_CURRENCY_CONVERT(C_ORDERLINE.PRICEACTUAL, COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), '102', TO_DATE(COALESCE(C_ORDER.DATEORDERED, NOW())), NULL, C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID) AS CONVPRICEACTUAL,
 	  C_CURRENCY_SYMBOL('102', '0', 'Y') AS CONVSYM,	  	  
 	  C_CURRENCY_ISOSYM('102') AS CONVISOSYM,	  	  
-	  TO_DATE(COALESCE(C_ORDERLINE.DATEORDERED, C_ORDER.DATEORDERED, NOW())) AS TRANSDATE,	  	  
+	  TO_DATE(COALESCE(C_ORDER.DATEORDERED, NOW())) AS TRANSDATE,	  	  
 	  COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID) AS TRANSCURRENCYID,      	  
 	  C_ORDERLINE.AD_CLIENT_ID AS TRANSCLIENTID, 	  	  
 	  C_ORDERLINE.AD_ORG_ID AS TRANSORGID, '' AS NAME
@@ -77,7 +77,7 @@
       AND C_ORDER.AD_ORG_ID IN ('1')
       AND 1=1
       GROUP BY C_BPARTNER.NAME, C_PROJECT.VALUE, C_PROJECT.NAME, M_PRODUCT.NAME, C_UOM.NAME, C_ORDERLINE.PRICEACTUAL,
-      COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), TO_DATE(COALESCE(C_ORDERLINE.DATEORDERED, C_ORDER.DATEORDERED, NOW())),	  
+      COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), TO_DATE(COALESCE(C_ORDER.DATEORDERED, NOW())),	  
 	  C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID
       HAVING SUM(C_ORDERLINE.QTYDELIVERED) > 0
       ORDER BY BPARTNERNAME, PROJECTNAME, PRODUCTNAME]]>
--- a/src/org/openbravo/erpCommon/ad_reports/ReportSalesOrderProvided_data.xsql	Fri Feb 17 12:42:15 2012 +0100
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportSalesOrderProvided_data.xsql	Sat Feb 18 14:51:00 2012 +0100
@@ -32,13 +32,13 @@
       M_PRODUCT.NAME AS PRODUCTNAME, SUM(C_ORDERLINE.QTYDELIVERED) AS QTYDELIVERED, 
       SUM(C_ORDERLINE.QTYDELIVERED*C_ORDERLINE.PRICEACTUAL) AS LINENETAMT, 
       C_CURRENCY_SYMBOL (COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), C_ORDERLINE.PRICEACTUAL, 'Y') AS LINENETAMTSYM,	  
-	  SUM(C_CURRENCY_CONVERT(C_ORDERLINE.QTYDELIVERED*C_ORDERLINE.PRICEACTUAL, COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), ?, TO_DATE(COALESCE(C_ORDERLINE.DATEORDERED, C_ORDER.DATEORDERED, NOW())), NULL, C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID)) AS CONVLINENETAMT,
+	  SUM(C_CURRENCY_CONVERT(C_ORDERLINE.QTYDELIVERED*C_ORDERLINE.PRICEACTUAL, COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), ?, TO_DATE(COALESCE(C_ORDER.DATEORDERED, NOW())), NULL, C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID)) AS CONVLINENETAMT,
       C_UOM.NAME AS UOMNAME, C_ORDERLINE.PRICEACTUAL, 
       C_CURRENCY_SYMBOL (COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), C_ORDERLINE.PRICEACTUAL, 'Y') AS PRICEACTUALSYM,	  	  
-	  C_CURRENCY_CONVERT(C_ORDERLINE.PRICEACTUAL, COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), ?, TO_DATE(COALESCE(C_ORDERLINE.DATEORDERED, C_ORDER.DATEORDERED, NOW())), NULL, C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID) AS CONVPRICEACTUAL,
+	  C_CURRENCY_CONVERT(C_ORDERLINE.PRICEACTUAL, COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), ?, TO_DATE(COALESCE(C_ORDER.DATEORDERED, NOW())), NULL, C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID) AS CONVPRICEACTUAL,
 	  C_CURRENCY_SYMBOL(?, '0', 'Y') AS CONVSYM,	  	  
 	  C_CURRENCY_ISOSYM(?) AS CONVISOSYM,	  	  
-	  TO_DATE(COALESCE(C_ORDERLINE.DATEORDERED, C_ORDER.DATEORDERED, NOW())) AS TRANSDATE,	  	  
+	  TO_DATE(COALESCE(C_ORDER.DATEORDERED, NOW())) AS TRANSDATE,	  	  
 	  COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID) AS TRANSCURRENCYID,      	  
 	  C_ORDERLINE.AD_CLIENT_ID AS TRANSCLIENTID, 	  	  
 	  C_ORDERLINE.AD_ORG_ID AS TRANSORGID, '' AS NAME
@@ -54,7 +54,7 @@
       AND C_ORDER.AD_ORG_ID IN ('1')
       AND 1=1
       GROUP BY C_BPARTNER.NAME, C_PROJECT.VALUE, C_PROJECT.NAME, M_PRODUCT.NAME, C_UOM.NAME, C_ORDERLINE.PRICEACTUAL,
-      COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), TO_DATE(COALESCE(C_ORDERLINE.DATEORDERED, C_ORDER.DATEORDERED, NOW())),	  
+      COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), TO_DATE(COALESCE(C_ORDER.DATEORDERED, NOW())),	  
 	  C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID
       HAVING SUM(C_ORDERLINE.QTYDELIVERED) > 0
       ORDER BY BPARTNERNAME, PROJECTNAME, PRODUCTNAME