src/org/openbravo/erpCommon/ad_reports/ReportOrderNotInvoice_data.xsql
changeset 1868 b462ce988fa2
parent 1605 8a0fe0193bef
child 2526 3b09206e90cf
--- a/src/org/openbravo/erpCommon/ad_reports/ReportOrderNotInvoice_data.xsql	Thu Oct 30 10:10:27 2008 +0000
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportOrderNotInvoice_data.xsql	Thu Oct 30 17:30:39 2008 +0000
@@ -12,7 +12,7 @@
  * under the License. 
  * The Original Code is Openbravo ERP. 
  * The Initial Developer of the Original Code is Openbravo SL 
- * All portions are Copyright (C) 2001-2006 Openbravo SL 
+ * All portions are Copyright (C) 2001-2008 Openbravo SL 
  * All Rights Reserved. 
  * Contributor(s):  ______________________________________.
  ************************************************************************
@@ -28,13 +28,29 @@
     <SqlMethodComment></SqlMethodComment>
     <Sql>
     <![CDATA[
-      SELECT ORGNAME, C_BPARTNER_ID, BPARTNERNAME, C_ORDER_ID, DOCUMENTNO, DATEORDERED, GRANDTOTAL, ISO_CODE, INVOICERULE, 
-      LINE, PRODUCT, PRICE, QTYORDERED, UOMSYMBOL, TAX, TAXBASE, LINENETAMT
-      FROM (
-      SELECT AD_ORG.NAME AS ORGNAME, C_BPARTNER.C_BPARTNER_ID, C_BPARTNER.NAME AS BPARTNERNAME, C_ORDER.C_ORDER_ID, C_ORDER.DOCUMENTNO,
-      C_ORDER.DATEORDERED, C_ORDER.GRANDTOTAL, C_CURRENCY.ISO_CODE, (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_ORDERLINE.QTYORDERED, C_UOM.UOMSYMBOL, NULL AS TAX, NULL AS TAXBASE, C_ORDERLINE.LINENETAMT
+      SELECT ORGNAME, C_BPARTNER_ID, BPARTNERNAME, C_ORDER_ID, DOCUMENTNO, DATEORDERED, GRANDTOTAL, CONVGRANDTOTAL, INVOICERULE,       
+	  LINE, PRODUCT, PRICE, CONVPRICE, QTYORDERED, UOMSYMBOL, TAX, TAXBASE, CONVTAXBASE, LINENETAMT, CONVLINENETAMT,
+	  ORDERCURRENCYSYM, TRANSCURRENCYIDORDER, TRANSDATEORDER, TRANSCLIENTIDORDER, TRANSORGIDORDER,
+	  LINECURRENCYSYM, TRANSCURRENCYIDLINE, TRANSDATELINE, TRANSCLIENTIDLINE, TRANSORGIDLINE,
+	  C_CURRENCY_SYMBOL(?, 0, 'Y') AS CONVSYM, C_CURRENCY_ISOSYM(?) AS CONVISOSYM
+	  FROM (
+	  SELECT AD_ORG.NAME AS ORGNAME, C_BPARTNER.C_BPARTNER_ID, C_BPARTNER.NAME AS BPARTNERNAME, C_ORDER.C_ORDER_ID, C_ORDER.DOCUMENTNO, C_ORDER.DATEORDERED, C_ORDER.GRANDTOTAL,
+	  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_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_SYMBOL(C_ORDER.C_CURRENCY_ID, 0, 'Y') AS ORDERCURRENCYSYM,
+	  C_ORDER.C_CURRENCY_ID AS TRANSCURRENCYIDORDER,
+	  C_ORDER.DATEORDERED AS TRANSDATEORDER,
+	  C_ORDER.AD_CLIENT_ID AS TRANSCLIENTIDORDER,
+	  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,
+	  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,
            AD_REF_LIST left join AD_REF_LIST_TRL  on AD_REF_LIST.AD_REF_LIST_ID = AD_REF_LIST_TRL.AD_REF_LIST_ID  
                                                      and AD_REF_LIST_TRL.AD_LANGUAGE = ?,
@@ -54,10 +70,24 @@
       AND C_ORDER.AD_ORG_ID IN ('1')
       AND 1=1
       UNION ALL
-      SELECT AD_ORG.NAME AS ORGNAME, C_BPARTNER.C_BPARTNER_ID, C_BPARTNER.NAME AS BPARTNERNAME, C.C_ORDER_ID, C.DOCUMENTNO,
-      C.DATEORDERED, C.GRANDTOTAL, C_CURRENCY.ISO_CODE AS ISO_CODE, (CASE C.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,
-      NULL AS LINE , C_TAX.NAME AS PRODUCT, NULL AS PRICE, 
-      NULL AS QTYORDERED, NULL AS UOMSYMBOL, C_TAX.RATE AS TAX, C_ORDERTAX.TAXBASEAMT AS TAXBASE, C_ORDERTAX.TAXAMT AS LINENETAMT
+      SELECT AD_ORG.NAME AS ORGNAME, C_BPARTNER.C_BPARTNER_ID, C_BPARTNER.NAME AS BPARTNERNAME, C.C_ORDER_ID, C.DOCUMENTNO, C.DATEORDERED, C.GRANDTOTAL,
+	  C_CURRENCY_CONVERT(C.GRANDTOTAL, C.C_CURRENCY_ID, ?, TO_DATE(COALESCE(C.DATEORDERED, NOW())), NULL, C.AD_CLIENT_ID, C.AD_ORG_ID) AS CONVGRANDTOTAL, 
+	  (CASE C.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,      
+	  NULL AS LINE , C_TAX.NAME AS PRODUCT, NULL AS PRICE, NULL AS CONVPRICE, NULL AS QTYORDERED, NULL AS UOMSYMBOL, 
+	  C_TAX.RATE AS TAX,  C_ORDERTAX.TAXBASEAMT AS TAXBASE,
+	  C_CURRENCY_CONVERT(C_ORDERTAX.TAXBASEAMT, C.C_CURRENCY_ID, ?, TO_DATE(COALESCE(C.DATEORDERED, NOW())), NULL, C_ORDERTAX.AD_CLIENT_ID, C_ORDERTAX.AD_ORG_ID) AS CONVTAXBASE,         
+	  C_ORDERTAX.TAXAMT AS LINENETAMT,
+	  C_CURRENCY_CONVERT(C_ORDERTAX.TAXAMT, C.C_CURRENCY_ID, ?, TO_DATE(COALESCE(C.DATEORDERED, NOW())), NULL, C_ORDERTAX.AD_CLIENT_ID, C_ORDERTAX.AD_ORG_ID) AS CONVLINENETAMT,        
+	  C_CURRENCY_SYMBOL(C.C_CURRENCY_ID, 0, 'Y') AS ORDERCURRENCYSYM,
+	  C.C_CURRENCY_ID AS TRANSCURRENCYIDORDER,
+	  C.DATEORDERED AS TRANSDATEORDER,
+	  C.AD_CLIENT_ID AS TRANSCLIENTIDORDER,
+	  C.AD_ORG_ID AS TRANSORGIDORDER,
+	  C_CURRENCY_SYMBOL(C.C_CURRENCY_ID, 0, 'Y') AS LINECURRENCYSYM,
+	  C.C_CURRENCY_ID AS TRANSCURRENCYIDLINE,
+	  TO_DATE(COALESCE(C.DATEORDERED, NOW())) AS TRANSDATELINE,
+	  C_ORDERTAX.AD_CLIENT_ID AS TRANSCLIENTIDLINE,
+	  C_ORDERTAX.AD_ORG_ID AS TRANSORGIDLINE
       FROM C_BPARTNER left join C_INVOICESCHEDULE on C_BPARTNER.C_INVOICESCHEDULE_ID = C_INVOICESCHEDULE.C_INVOICESCHEDULE_ID,
            AD_REF_LIST left join AD_REF_LIST_TRL on AD_REF_LIST.AD_REF_LIST_ID = AD_REF_LIST_TRL.AD_REF_LIST_ID 
                                                  AND AD_REF_LIST_TRL.AD_LANGUAGE = ? ,
@@ -81,6 +111,11 @@
       ) AAA
       ORDER BY ORGNAME, BPARTNERNAME, DATEORDERED DESC, DOCUMENTNO, LINE
       ]]></Sql>
+    <Parameter name="cCurrencyConv"/>
+	<Parameter name="cCurrencyConv"/>
+	<Parameter name="cCurrencyConv"/>
+	<Parameter name="cCurrencyConv"/>
+	<Parameter name="cCurrencyConv"/>
     <Parameter name="adLanguage"/>
     <Parameter name="adUserClient" type="replace" optional="true" after="C_ORDER.AD_Client_ID IN (" text="'1'"/>
     <Parameter name="adUserOrg" type="replace" optional="true" after="C_ORDER.AD_ORG_ID IN (" text="'1'"/>
@@ -89,6 +124,9 @@
     <Parameter name="invoiceRule" optional="true" after="1=1">AND C_ORDER.INVOICERULE = ?</Parameter>
     <Parameter name="dateFrom" optional="true" after="1=1"><![CDATA[ AND C_ORDER.DATEORDERED >= to_date(?) ]]></Parameter>
     <Parameter name="dateTo" optional="true" after="1=1"><![CDATA[ AND C_ORDER.DATEORDERED < to_date(?) ]]></Parameter>
+    <Parameter name="cCurrencyConv"/>
+	<Parameter name="cCurrencyConv"/>
+	<Parameter name="cCurrencyConv"/>
     <Parameter name="adLanguage"/>
     <Parameter name="adUserClient" type="replace" optional="true" after="C.AD_Client_ID IN (" text="'2'"/>
     <Parameter name="adUserOrg" type="replace" optional="true" after="C.AD_ORG_ID IN (" text="'2'"/>