src/org/openbravo/erpCommon/ad_reports/ReportOrderNotInvoice_data.xsql
changeset 33201 1f43ecba24a4
parent 22050 c3bb94a4b13f
child 35159 1a4041af1fa1
--- a/src/org/openbravo/erpCommon/ad_reports/ReportOrderNotInvoice_data.xsql	Thu Jan 11 10:20:02 2018 +0100
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportOrderNotInvoice_data.xsql	Mon Jan 22 00:18:10 2018 +0530
@@ -12,7 +12,7 @@
  * under the License. 
  * The Original Code is Openbravo ERP. 
  * The Initial Developer of the Original Code is Openbravo SLU 
- * All portions are Copyright (C) 2001-2010 Openbravo SLU 
+ * All portions are Copyright (C) 2001-2018 Openbravo SLU 
  * All Rights Reserved. 
  * Contributor(s):  ______________________________________.
  ************************************************************************
@@ -28,117 +28,117 @@
     <SqlMethodComment></SqlMethodComment>
     <Sql>
     <![CDATA[
-      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_ORDER.DATEORDERED, NOW())), NULL, C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID) AS CONVPRICE,        
-	  C_ORDERLINE.QTYORDERED - C_ORDERLINE.QTYINVOICED AS 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_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_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 = ?,
-           C_ORDERLINE left join M_PRODUCT on C_ORDERLINE.M_PRODUCT_ID=M_PRODUCT.M_PRODUCT_ID,
-           C_ORDER, AD_ORG, C_UOM, C_CURRENCY
-      WHERE C_ORDER.C_ORDER_ID=C_ORDERLINE.C_ORDER_ID
-      AND C_ORDER.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
-      AND C_ORDER.INVOICERULE = AD_REF_LIST.VALUE
-      AND C_ORDER.INVOICERULE <> 'N'
-      AND C_ORDERLINE.C_UOM_ID = C_UOM.C_UOM_ID
-      AND C_ORDER.AD_ORG_ID = AD_ORG.AD_ORG_ID
-      AND C_ORDER.C_CURRENCY_ID = C_CURRENCY.C_CURRENCY_ID
-      AND AD_REF_LIST.AD_REFERENCE_ID = '150'
-      AND C_ORDER.PROCESSED = 'Y'
-      AND C_ORDER.DOCSTATUS NOT IN ('CJ', 'UE', 'CA')
-      AND C_ORDER.ISSOTRX = 'Y'
-      AND C_ORDERLINE.QTYORDERED<>C_ORDERLINE.QTYINVOICED 
-      AND C_ORDER.AD_Client_ID IN ('1')
-      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_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 = ? ,
-      C_ORDER C ,C_ORDERTAX, AD_ORG,  C_TAX, C_CURRENCY      
-      WHERE C.C_ORDER_ID=C_ORDERTAX.C_ORDER_ID
-      AND C.C_BPARTNER_ID=C_BPARTNER.C_BPARTNER_ID
-      AND C.C_CURRENCY_ID = C_CURRENCY.C_CURRENCY_ID
-      AND C.INVOICERULE = AD_REF_LIST.VALUE
-      AND C.INVOICERULE <> 'N'
-      AND AD_REF_LIST.AD_REFERENCE_ID = '150'
-      AND C_ORDERTAX.C_TAX_ID = C_TAX.C_TAX_ID
-      AND C.AD_ORG_ID = AD_ORG.AD_ORG_ID
-      AND C.PROCESSED = 'Y'
-      AND C.DOCSTATUS NOT IN ('CJ', 'UE', 'CA')
-      AND C.ISSOTRX = 'Y'
-      AND EXISTS (SELECT 1 FROM C_ORDER C2, C_ORDERLINE CL
-                 WHERE C2.c_order_id = C.c_order_id
-                 and c2.c_order_id=cl.c_order_id
-                 and cl.QTYORDERED<>cl.QTYINVOICED)
-      AND C.AD_Client_ID IN ('2')
-      AND C.AD_ORG_ID IN ('2')
-      AND 2=2
-      ) AAA
-      ORDER BY ORGNAME, BPARTNERNAME, DATEORDERED DESC, DOCUMENTNO, LINE
-      ]]></Sql>
+     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
+       (
+         WITH pendingOrders AS
+         (
+           SELECT C.C_ORDER_ID, C.DOCUMENTNO, C.DATEORDERED, C.GRANDTOTAL,
+             C.C_CURRENCY_ID, C.AD_CLIENT_ID, C.AD_ORG_ID, C.C_BPARTNER_ID, C.INVOICERULE,
+             CL.LINE, CL.AD_CLIENT_ID AS CL_AD_CLIENT_ID, CL.AD_ORG_ID AS CL_AD_ORG_ID,
+             CL.C_CURRENCY_ID AS CL_C_CURRENCY_ID, CL.LINENETAMT, CL.QTYORDERED,
+             CL.QTYINVOICED, CL.PRICEACTUAL, CL.M_PRODUCT_ID, CL.C_UOM_ID,
+             AD_REF_LIST.AD_REF_LIST_ID,
+             AD_REF_LIST.NAME AS INVOICERULENAME
+           FROM c_order C JOIN C_Orderline CL on CL.C_Order_ID = C.C_Order_ID
+             JOIN AD_REF_LIST ON C.INVOICERULE = AD_REF_LIST.VALUE
+             AND AD_REF_LIST.AD_REFERENCE_ID = '150'
+           WHERE  C.invoicerule <> 'N'
+             AND C.processed = 'Y'
+             AND C.docstatus NOT IN ( 'CJ', 'UE', 'CA', 'DR', 'CL')
+             AND C.issotrx = 'Y'
+             AND OBEQUALS(cl.qtyordered, cl.qtyinvoiced) = 'N'
+             AND C.AD_Client_ID IN ('1')
+             AND C.AD_ORG_ID IN ('1')
+             AND 1=1
+        )
+        SELECT AD_ORG.NAME AS ORGNAME, C_BPARTNER.C_BPARTNER_ID, C_BPARTNER.NAME AS BPARTNERNAME,
+          pendingOrders.C_ORDER_ID, pendingOrders.DOCUMENTNO, pendingOrders.DATEORDERED,
+          pendingOrders.GRANDTOTAL,
+          CASE WHEN pendingorders.C_CURRENCY_ID = ? THEN pendingorders.GRANDTOTAL ELSE C_CURRENCY_CONVERT(pendingorders.GRANDTOTAL, pendingorders.C_CURRENCY_ID, ?, TO_DATE(COALESCE(pendingorders.DATEORDERED, NOW())), NULL, pendingorders.AD_CLIENT_ID, pendingorders.AD_ORG_ID) END AS CONVGRANDTOTAL,
+          CASE WHEN pendingOrders.INVOICERULE = 'S' THEN COALESCE(AD_REF_LIST_TRL.NAME, pendingOrders.INVOICERULENAME)||' ('||C_INVOICESCHEDULE.NAME||')'
+               ELSE COALESCE(AD_REF_LIST_TRL.NAME, pendingOrders.INVOICERULENAME) END AS INVOICERULE,
+          pendingOrders.LINE AS LINE,
+          COALESCE(P.NAME, P.DESCRIPTION) AS PRODUCT, pendingOrders.PRICEACTUAL AS PRICE,
+          CASE WHEN COALESCE(pendingOrders.CL_C_CURRENCY_ID, pendingorders.C_CURRENCY_ID) = ? THEN pendingOrders.PRICEACTUAL ELSE C_CURRENCY_CONVERT(pendingOrders.PRICEACTUAL, COALESCE(pendingOrders.CL_C_CURRENCY_ID, pendingorders.C_CURRENCY_ID), ?, TO_DATE(COALESCE(pendingorders.DATEORDERED, NOW())), NULL, pendingOrders.CL_AD_CLIENT_ID, pendingOrders.CL_AD_ORG_ID) END AS CONVPRICE,
+          pendingOrders.QTYORDERED - pendingOrders.QTYINVOICED AS QTYORDERED,
+          U.UOMSYMBOL, NULL AS TAX, NULL AS TAXBASE, NULL AS CONVTAXBASE, pendingOrders.LINENETAMT,
+          CASE WHEN COALESCE(pendingOrders.CL_C_CURRENCY_ID, pendingorders.C_CURRENCY_ID) = ? THEN pendingOrders.LINENETAMT ELSE C_CURRENCY_CONVERT(pendingOrders.LINENETAMT, COALESCE(pendingOrders.CL_C_CURRENCY_ID, pendingorders.C_CURRENCY_ID), ?, TO_DATE(COALESCE(pendingorders.DATEORDERED, NOW())), NULL, pendingOrders.CL_AD_CLIENT_ID, pendingOrders.CL_AD_ORG_ID) END AS CONVLINENETAMT,
+          C_CURRENCY_SYMBOL(pendingorders.C_CURRENCY_ID, 0, 'Y') AS ORDERCURRENCYSYM,
+          pendingorders.C_CURRENCY_ID AS TRANSCURRENCYIDORDER, pendingorders.DATEORDERED AS TRANSDATEORDER,
+          pendingorders.AD_CLIENT_ID AS TRANSCLIENTIDORDER, pendingorders.AD_ORG_ID AS TRANSORGIDORDER,
+          C_CURRENCY_SYMBOL(COALESCE(pendingOrders.CL_C_CURRENCY_ID, pendingorders.C_CURRENCY_ID), 0, 'Y') AS LINECURRENCYSYM,
+          COALESCE(pendingOrders.CL_C_CURRENCY_ID, pendingorders.C_CURRENCY_ID) AS TRANSCURRENCYIDLINE,
+          TO_DATE(COALESCE(pendingorders.DATEORDERED, NOW())) AS TRANSDATELINE,
+          pendingOrders.CL_AD_CLIENT_ID AS TRANSCLIENTIDLINE, pendingOrders.CL_AD_ORG_ID AS TRANSORGIDLINE
+        FROM pendingOrders join m_product p on p.m_product_id = pendingOrders.m_product_id
+          join c_uom u on u.c_uom_id = pendingOrders.c_uom_id
+          LEFT JOIN AD_REF_LIST_TRL ON pendingOrders.AD_REF_LIST_ID = AD_REF_LIST_TRL.AD_REF_LIST_ID
+            AND AD_REF_LIST_TRL.AD_LANGUAGE = ?,
+          C_BPARTNER LEFT JOIN C_INVOICESCHEDULE ON C_BPARTNER.C_INVOICESCHEDULE_ID = C_INVOICESCHEDULE.C_INVOICESCHEDULE_ID,
+          AD_ORG
+        WHERE pendingorders.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
+          AND pendingorders.AD_ORG_ID = AD_ORG.AD_ORG_ID
+
+       UNION ALL
+
+         SELECT MAX(AD_ORG.NAME) AS ORGNAME, MAX(C_BPARTNER.C_BPARTNER_ID) AS C_BPARTNER_ID, MAX(C_BPARTNER.NAME) AS BPARTNERNAME,
+           pendingOrders.C_ORDER_ID AS C_ORDER_ID, MAX(pendingOrders.DOCUMENTNO) AS DOCUMENTNO,
+           MAX(pendingOrders.DATEORDERED) AS DATEORDERED, MAX(pendingOrders.GRANDTOTAL) AS GRANDTOTAL,
+           CASE WHEN MAX(pendingOrders.C_CURRENCY_ID) = ? THEN MAX(pendingOrders.GRANDTOTAL)
+             ELSE C_CURRENCY_CONVERT(MAX(pendingOrders.GRANDTOTAL), MAX(pendingOrders.C_CURRENCY_ID), ?, TO_DATE(COALESCE(MAX(pendingOrders.DATEORDERED), NOW())), NULL, MAX(pendingOrders.AD_CLIENT_ID), MAX(pendingOrders.AD_ORG_ID)) END AS CONVGRANDTOTAL,
+           CASE WHEN MAX(pendingOrders.INVOICERULE) = 'S'
+             THEN COALESCE(MAX(AD_REF_LIST_TRL.NAME), MAX(pendingOrders.INVOICERULENAME))||' ('||MAX(C_INVOICESCHEDULE.NAME)||')'
+		     ELSE COALESCE(MAX(AD_REF_LIST_TRL.NAME), MAX(pendingOrders.INVOICERULENAME)) END AS INVOICERULE,
+           NULL AS LINE, MAX(C_TAX.NAME) AS PRODUCT, NULL AS PRICE, NULL AS CONVPRICE, NULL AS QTYORDERED, NULL AS UOMSYMBOL,
+           MAX(C_TAX.RATE) AS TAX, MAX(C_ORDERTAX.TAXBASEAMT) AS TAXBASE,
+           CASE WHEN MAX(pendingOrders.C_CURRENCY_ID) = ? THEN MAX(C_ORDERTAX.TAXBASEAMT) ELSE C_CURRENCY_CONVERT(MAX(C_ORDERTAX.TAXBASEAMT), MAX(pendingOrders.C_CURRENCY_ID), ?, TO_DATE(COALESCE(MAX(pendingOrders.DATEORDERED), NOW())), NULL, MAX(C_ORDERTAX.AD_CLIENT_ID), MAX(C_ORDERTAX.AD_ORG_ID)) END AS CONVTAXBASE,
+           MAX(C_ORDERTAX.TAXAMT) AS LINENETAMT,
+           CASE WHEN MAX(pendingOrders.C_CURRENCY_ID) = ? THEN MAX(C_ORDERTAX.TAXAMT) ELSE C_CURRENCY_CONVERT(MAX(C_ORDERTAX.TAXAMT), MAX(pendingOrders.C_CURRENCY_ID), ?, TO_DATE(COALESCE(MAX(pendingOrders.DATEORDERED), NOW())), NULL, MAX(C_ORDERTAX.AD_CLIENT_ID), MAX(C_ORDERTAX.AD_ORG_ID)) END AS CONVLINENETAMT,
+           C_CURRENCY_SYMBOL(MAX(pendingOrders.C_CURRENCY_ID), 0, 'Y') AS ORDERCURRENCYSYM, MAX(pendingOrders.C_CURRENCY_ID) AS TRANSCURRENCYIDORDER,
+           MAX(pendingOrders.DATEORDERED) AS TRANSDATEORDER, MAX(pendingOrders.AD_CLIENT_ID) AS TRANSCLIENTIDORDER,
+           MAX(pendingOrders.AD_ORG_ID) AS TRANSORGIDORDER, C_CURRENCY_SYMBOL(MAX(pendingOrders.C_CURRENCY_ID), 0, 'Y') AS LINECURRENCYSYM,
+           MAX(pendingOrders.C_CURRENCY_ID) AS TRANSCURRENCYIDLINE, TO_DATE(COALESCE(MAX(pendingOrders.DATEORDERED), NOW())) AS TRANSDATELINE,
+           MAX(C_ORDERTAX.AD_CLIENT_ID) AS TRANSCLIENTIDLINE, MAX(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,
+           C_ORDERTAX, AD_ORG, C_TAX,
+           pendingOrders left join AD_REF_LIST_TRL ON pendingOrders.AD_REF_LIST_ID = AD_REF_LIST_TRL.AD_REF_LIST_ID
+           AND AD_REF_LIST_TRL.AD_LANGUAGE = ?
+         WHERE pendingOrders.C_ORDER_ID = C_ORDERTAX.C_ORDER_ID
+           AND pendingOrders.C_BPARTNER_ID=C_BPARTNER.C_BPARTNER_ID
+           AND C_ORDERTAX.C_TAX_ID = C_TAX.C_TAX_ID
+           AND pendingOrders.AD_ORG_ID = AD_ORG.AD_ORG_ID
+         GROUP BY pendingOrders.C_ORDER_ID, C_ORDERTAX.C_TAX_ID
+     ) AAA ORDER BY ORGNAME, BPARTNERNAME, DATEORDERED DESC, DOCUMENTNO, LINE
+     ]]></Sql>
     <Parameter name="cCurrencyConv"/>
 	<Parameter name="cCurrencyConv"/>
+	<Parameter name="adUserClient" type="replace" optional="true" after="C.AD_Client_ID IN (" text="'1'"/>
+	<Parameter name="adUserOrg" type="replace" optional="true" after="C.AD_ORG_ID IN (" text="'1'"/>
+	<Parameter name="cBpartnerId" optional="true" after="1=1">AND C.C_BPARTNER_ID = ?</Parameter>
+	<Parameter name="cOrgId" optional="true" after="1=1">AND C.AD_ORG_ID = ?</Parameter>
+	<Parameter name="invoiceRule" optional="true" after="1=1">AND C.INVOICERULE = ?</Parameter>
+	<Parameter name="dateFrom" optional="true" after="1=1"><![CDATA[ AND C.DATEORDERED >= to_date(?) ]]></Parameter>
+	<Parameter name="dateTo" optional="true" after="1=1"><![CDATA[ AND C.DATEORDERED < to_date(?) ]]></Parameter>
 	<Parameter name="cCurrencyConv"/>
 	<Parameter name="cCurrencyConv"/>
 	<Parameter name="cCurrencyConv"/>
+	<Parameter name="cCurrencyConv"/>
+	<Parameter name="cCurrencyConv"/>
+	<Parameter name="cCurrencyConv"/>
+	<Parameter name="adLanguage"/>
+	<Parameter name="cCurrencyConv"/>
+	<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'"/>
-    <Parameter name="cBpartnerId" optional="true" after="1=1">AND C_ORDER.C_BPARTNER_ID = ?</Parameter>
-    <Parameter name="cOrgId" optional="true" after="1=1">AND C_ORDER.AD_ORG_ID = ?</Parameter>
-    <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'"/>
-    <Parameter name="cBpartnerId" optional="true" after="2=2">AND C.C_BPARTNER_ID = ?</Parameter>
-    <Parameter name="cOrgId" optional="true" after="2=2">AND C.AD_ORG_ID = ?</Parameter>
-    <Parameter name="invoiceRule" optional="true" after="2=2">AND C.INVOICERULE = ?</Parameter>
-    <Parameter name="dateFrom" optional="true" after="2=2"><![CDATA[ AND C.DATEORDERED >= to_date(?) ]]></Parameter>
-    <Parameter name="dateTo" optional="true" after="2=2"><![CDATA[ AND C.DATEORDERED < to_date(?) ]]></Parameter>
   </SqlMethod>
   <SqlMethod name="bPartnerDescription" type="preparedStatement" return="String" default="">
     <SqlMethodComment></SqlMethodComment>