src/org/openbravo/erpCommon/ad_reports/ReportOrderNotInvoice_data.xsql
changeset 33201 1f43ecba24a4
parent 22050 c3bb94a4b13f
child 35159 1a4041af1fa1
equal deleted inserted replaced
33200:27839f3cbb27 33201:1f43ecba24a4
    10  * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
    10  * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
    11  * License for the specific  language  governing  rights  and  limitations
    11  * License for the specific  language  governing  rights  and  limitations
    12  * under the License. 
    12  * under the License. 
    13  * The Original Code is Openbravo ERP. 
    13  * The Original Code is Openbravo ERP. 
    14  * The Initial Developer of the Original Code is Openbravo SLU 
    14  * The Initial Developer of the Original Code is Openbravo SLU 
    15  * All portions are Copyright (C) 2001-2010 Openbravo SLU 
    15  * All portions are Copyright (C) 2001-2018 Openbravo SLU 
    16  * All Rights Reserved. 
    16  * All Rights Reserved. 
    17  * Contributor(s):  ______________________________________.
    17  * Contributor(s):  ______________________________________.
    18  ************************************************************************
    18  ************************************************************************
    19 -->
    19 -->
    20 
    20 
    26   <SqlClassComment></SqlClassComment>
    26   <SqlClassComment></SqlClassComment>
    27   <SqlMethod name="select" type="preparedStatement" return="multiple">
    27   <SqlMethod name="select" type="preparedStatement" return="multiple">
    28     <SqlMethodComment></SqlMethodComment>
    28     <SqlMethodComment></SqlMethodComment>
    29     <Sql>
    29     <Sql>
    30     <![CDATA[
    30     <![CDATA[
    31       SELECT ORGNAME, C_BPARTNER_ID, BPARTNERNAME, C_ORDER_ID, DOCUMENTNO, DATEORDERED, GRANDTOTAL, CONVGRANDTOTAL, INVOICERULE,       
    31      SELECT ORGNAME, C_BPARTNER_ID, BPARTNERNAME, C_ORDER_ID, DOCUMENTNO,
    32 	  LINE, PRODUCT, PRICE, CONVPRICE, QTYORDERED, UOMSYMBOL, TAX, TAXBASE, CONVTAXBASE, LINENETAMT, CONVLINENETAMT,
    32        DATEORDERED, GRANDTOTAL, CONVGRANDTOTAL, INVOICERULE, LINE, PRODUCT, PRICE, CONVPRICE,
    33 	  ORDERCURRENCYSYM, TRANSCURRENCYIDORDER, TRANSDATEORDER, TRANSCLIENTIDORDER, TRANSORGIDORDER,
    33        QTYORDERED, UOMSYMBOL, TAX, TAXBASE, CONVTAXBASE, LINENETAMT, CONVLINENETAMT,
    34 	  LINECURRENCYSYM, TRANSCURRENCYIDLINE, TRANSDATELINE, TRANSCLIENTIDLINE, TRANSORGIDLINE,
    34        ORDERCURRENCYSYM, TRANSCURRENCYIDORDER, TRANSDATEORDER, TRANSCLIENTIDORDER, TRANSORGIDORDER,
    35 	  C_CURRENCY_SYMBOL(?, 0, 'Y') AS CONVSYM, C_CURRENCY_ISOSYM(?) AS CONVISOSYM
    35        LINECURRENCYSYM, TRANSCURRENCYIDLINE, TRANSDATELINE, TRANSCLIENTIDLINE,
    36 	  FROM (
    36        TRANSORGIDLINE, C_CURRENCY_SYMBOL(?, 0, 'Y') AS CONVSYM, C_CURRENCY_ISOSYM(?) AS CONVISOSYM
    37 	  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,
    37      FROM
    38 	  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, 
    38        (
    39 	  (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,      
    39          WITH pendingOrders AS
    40 	  C_ORDERLINE.LINE AS LINE, COALESCE(M_PRODUCT.NAME, M_PRODUCT.DESCRIPTION) AS PRODUCT, C_ORDERLINE.PRICEACTUAL AS PRICE,
    40          (
    41 	  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,        
    41            SELECT C.C_ORDER_ID, C.DOCUMENTNO, C.DATEORDERED, C.GRANDTOTAL,
    42 	  C_ORDERLINE.QTYORDERED - C_ORDERLINE.QTYINVOICED AS QTYORDERED, C_UOM.UOMSYMBOL, NULL AS TAX, NULL AS TAXBASE, NULL AS CONVTAXBASE, C_ORDERLINE.LINENETAMT,
    42              C.C_CURRENCY_ID, C.AD_CLIENT_ID, C.AD_ORG_ID, C.C_BPARTNER_ID, C.INVOICERULE,
    43 	  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,        
    43              CL.LINE, CL.AD_CLIENT_ID AS CL_AD_CLIENT_ID, CL.AD_ORG_ID AS CL_AD_ORG_ID,
    44 	  C_CURRENCY_SYMBOL(C_ORDER.C_CURRENCY_ID, 0, 'Y') AS ORDERCURRENCYSYM,
    44              CL.C_CURRENCY_ID AS CL_C_CURRENCY_ID, CL.LINENETAMT, CL.QTYORDERED,
    45 	  C_ORDER.C_CURRENCY_ID AS TRANSCURRENCYIDORDER,
    45              CL.QTYINVOICED, CL.PRICEACTUAL, CL.M_PRODUCT_ID, CL.C_UOM_ID,
    46 	  C_ORDER.DATEORDERED AS TRANSDATEORDER,
    46              AD_REF_LIST.AD_REF_LIST_ID,
    47 	  C_ORDER.AD_CLIENT_ID AS TRANSCLIENTIDORDER,
    47              AD_REF_LIST.NAME AS INVOICERULENAME
    48 	  C_ORDER.AD_ORG_ID AS TRANSORGIDORDER,
    48            FROM c_order C JOIN C_Orderline CL on CL.C_Order_ID = C.C_Order_ID
    49 	  C_CURRENCY_SYMBOL(COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), 0, 'Y') AS LINECURRENCYSYM,
    49              JOIN AD_REF_LIST ON C.INVOICERULE = AD_REF_LIST.VALUE
    50 	  COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID) AS TRANSCURRENCYIDLINE,
    50              AND AD_REF_LIST.AD_REFERENCE_ID = '150'
    51 	  TO_DATE(COALESCE(C_ORDER.DATEORDERED, NOW())) AS TRANSDATELINE,
    51            WHERE  C.invoicerule <> 'N'
    52 	  C_ORDERLINE.AD_CLIENT_ID AS TRANSCLIENTIDLINE,
    52              AND C.processed = 'Y'
    53 	  C_ORDERLINE.AD_ORG_ID AS TRANSORGIDLINE 
    53              AND C.docstatus NOT IN ( 'CJ', 'UE', 'CA', 'DR', 'CL')
    54       FROM C_BPARTNER left join C_INVOICESCHEDULE on C_BPARTNER.C_INVOICESCHEDULE_ID = C_INVOICESCHEDULE.C_INVOICESCHEDULE_ID,
    54              AND C.issotrx = 'Y'
    55            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  
    55              AND OBEQUALS(cl.qtyordered, cl.qtyinvoiced) = 'N'
    56                                                      and AD_REF_LIST_TRL.AD_LANGUAGE = ?,
    56              AND C.AD_Client_ID IN ('1')
    57            C_ORDERLINE left join M_PRODUCT on C_ORDERLINE.M_PRODUCT_ID=M_PRODUCT.M_PRODUCT_ID,
    57              AND C.AD_ORG_ID IN ('1')
    58            C_ORDER, AD_ORG, C_UOM, C_CURRENCY
    58              AND 1=1
    59       WHERE C_ORDER.C_ORDER_ID=C_ORDERLINE.C_ORDER_ID
    59         )
    60       AND C_ORDER.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
    60         SELECT AD_ORG.NAME AS ORGNAME, C_BPARTNER.C_BPARTNER_ID, C_BPARTNER.NAME AS BPARTNERNAME,
    61       AND C_ORDER.INVOICERULE = AD_REF_LIST.VALUE
    61           pendingOrders.C_ORDER_ID, pendingOrders.DOCUMENTNO, pendingOrders.DATEORDERED,
    62       AND C_ORDER.INVOICERULE <> 'N'
    62           pendingOrders.GRANDTOTAL,
    63       AND C_ORDERLINE.C_UOM_ID = C_UOM.C_UOM_ID
    63           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,
    64       AND C_ORDER.AD_ORG_ID = AD_ORG.AD_ORG_ID
    64           CASE WHEN pendingOrders.INVOICERULE = 'S' THEN COALESCE(AD_REF_LIST_TRL.NAME, pendingOrders.INVOICERULENAME)||' ('||C_INVOICESCHEDULE.NAME||')'
    65       AND C_ORDER.C_CURRENCY_ID = C_CURRENCY.C_CURRENCY_ID
    65                ELSE COALESCE(AD_REF_LIST_TRL.NAME, pendingOrders.INVOICERULENAME) END AS INVOICERULE,
    66       AND AD_REF_LIST.AD_REFERENCE_ID = '150'
    66           pendingOrders.LINE AS LINE,
    67       AND C_ORDER.PROCESSED = 'Y'
    67           COALESCE(P.NAME, P.DESCRIPTION) AS PRODUCT, pendingOrders.PRICEACTUAL AS PRICE,
    68       AND C_ORDER.DOCSTATUS NOT IN ('CJ', 'UE', 'CA')
    68           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,
    69       AND C_ORDER.ISSOTRX = 'Y'
    69           pendingOrders.QTYORDERED - pendingOrders.QTYINVOICED AS QTYORDERED,
    70       AND C_ORDERLINE.QTYORDERED<>C_ORDERLINE.QTYINVOICED 
    70           U.UOMSYMBOL, NULL AS TAX, NULL AS TAXBASE, NULL AS CONVTAXBASE, pendingOrders.LINENETAMT,
    71       AND C_ORDER.AD_Client_ID IN ('1')
    71           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,
    72       AND C_ORDER.AD_ORG_ID IN ('1')
    72           C_CURRENCY_SYMBOL(pendingorders.C_CURRENCY_ID, 0, 'Y') AS ORDERCURRENCYSYM,
    73       AND 1=1
    73           pendingorders.C_CURRENCY_ID AS TRANSCURRENCYIDORDER, pendingorders.DATEORDERED AS TRANSDATEORDER,
    74       UNION ALL
    74           pendingorders.AD_CLIENT_ID AS TRANSCLIENTIDORDER, pendingorders.AD_ORG_ID AS TRANSORGIDORDER,
    75       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,
    75           C_CURRENCY_SYMBOL(COALESCE(pendingOrders.CL_C_CURRENCY_ID, pendingorders.C_CURRENCY_ID), 0, 'Y') AS LINECURRENCYSYM,
    76 	  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, 
    76           COALESCE(pendingOrders.CL_C_CURRENCY_ID, pendingorders.C_CURRENCY_ID) AS TRANSCURRENCYIDLINE,
    77 	  (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,      
    77           TO_DATE(COALESCE(pendingorders.DATEORDERED, NOW())) AS TRANSDATELINE,
    78 	  NULL AS LINE , C_TAX.NAME AS PRODUCT, NULL AS PRICE, NULL AS CONVPRICE, NULL AS QTYORDERED, NULL AS UOMSYMBOL, 
    78           pendingOrders.CL_AD_CLIENT_ID AS TRANSCLIENTIDLINE, pendingOrders.CL_AD_ORG_ID AS TRANSORGIDLINE
    79 	  C_TAX.RATE AS TAX,  C_ORDERTAX.TAXBASEAMT AS TAXBASE,
    79         FROM pendingOrders join m_product p on p.m_product_id = pendingOrders.m_product_id
    80 	  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,         
    80           join c_uom u on u.c_uom_id = pendingOrders.c_uom_id
    81 	  C_ORDERTAX.TAXAMT AS LINENETAMT,
    81           LEFT JOIN AD_REF_LIST_TRL ON pendingOrders.AD_REF_LIST_ID = AD_REF_LIST_TRL.AD_REF_LIST_ID
    82 	  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,        
    82             AND AD_REF_LIST_TRL.AD_LANGUAGE = ?,
    83 	  C_CURRENCY_SYMBOL(C.C_CURRENCY_ID, 0, 'Y') AS ORDERCURRENCYSYM,
    83           C_BPARTNER LEFT JOIN C_INVOICESCHEDULE ON C_BPARTNER.C_INVOICESCHEDULE_ID = C_INVOICESCHEDULE.C_INVOICESCHEDULE_ID,
    84 	  C.C_CURRENCY_ID AS TRANSCURRENCYIDORDER,
    84           AD_ORG
    85 	  C.DATEORDERED AS TRANSDATEORDER,
    85         WHERE pendingorders.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
    86 	  C.AD_CLIENT_ID AS TRANSCLIENTIDORDER,
    86           AND pendingorders.AD_ORG_ID = AD_ORG.AD_ORG_ID
    87 	  C.AD_ORG_ID AS TRANSORGIDORDER,
    87 
    88 	  C_CURRENCY_SYMBOL(C.C_CURRENCY_ID, 0, 'Y') AS LINECURRENCYSYM,
    88        UNION ALL
    89 	  C.C_CURRENCY_ID AS TRANSCURRENCYIDLINE,
    89 
    90 	  TO_DATE(COALESCE(C.DATEORDERED, NOW())) AS TRANSDATELINE,
    90          SELECT MAX(AD_ORG.NAME) AS ORGNAME, MAX(C_BPARTNER.C_BPARTNER_ID) AS C_BPARTNER_ID, MAX(C_BPARTNER.NAME) AS BPARTNERNAME,
    91 	  C_ORDERTAX.AD_CLIENT_ID AS TRANSCLIENTIDLINE,
    91            pendingOrders.C_ORDER_ID AS C_ORDER_ID, MAX(pendingOrders.DOCUMENTNO) AS DOCUMENTNO,
    92 	  C_ORDERTAX.AD_ORG_ID AS TRANSORGIDLINE
    92            MAX(pendingOrders.DATEORDERED) AS DATEORDERED, MAX(pendingOrders.GRANDTOTAL) AS GRANDTOTAL,
    93       FROM C_BPARTNER left join C_INVOICESCHEDULE on C_BPARTNER.C_INVOICESCHEDULE_ID = C_INVOICESCHEDULE.C_INVOICESCHEDULE_ID,
    93            CASE WHEN MAX(pendingOrders.C_CURRENCY_ID) = ? THEN MAX(pendingOrders.GRANDTOTAL)
    94            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 
    94              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,
    95                                                  AND AD_REF_LIST_TRL.AD_LANGUAGE = ? ,
    95            CASE WHEN MAX(pendingOrders.INVOICERULE) = 'S'
    96       C_ORDER C ,C_ORDERTAX, AD_ORG,  C_TAX, C_CURRENCY      
    96              THEN COALESCE(MAX(AD_REF_LIST_TRL.NAME), MAX(pendingOrders.INVOICERULENAME))||' ('||MAX(C_INVOICESCHEDULE.NAME)||')'
    97       WHERE C.C_ORDER_ID=C_ORDERTAX.C_ORDER_ID
    97 		     ELSE COALESCE(MAX(AD_REF_LIST_TRL.NAME), MAX(pendingOrders.INVOICERULENAME)) END AS INVOICERULE,
    98       AND C.C_BPARTNER_ID=C_BPARTNER.C_BPARTNER_ID
    98            NULL AS LINE, MAX(C_TAX.NAME) AS PRODUCT, NULL AS PRICE, NULL AS CONVPRICE, NULL AS QTYORDERED, NULL AS UOMSYMBOL,
    99       AND C.C_CURRENCY_ID = C_CURRENCY.C_CURRENCY_ID
    99            MAX(C_TAX.RATE) AS TAX, MAX(C_ORDERTAX.TAXBASEAMT) AS TAXBASE,
   100       AND C.INVOICERULE = AD_REF_LIST.VALUE
   100            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,
   101       AND C.INVOICERULE <> 'N'
   101            MAX(C_ORDERTAX.TAXAMT) AS LINENETAMT,
   102       AND AD_REF_LIST.AD_REFERENCE_ID = '150'
   102            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,
   103       AND C_ORDERTAX.C_TAX_ID = C_TAX.C_TAX_ID
   103            C_CURRENCY_SYMBOL(MAX(pendingOrders.C_CURRENCY_ID), 0, 'Y') AS ORDERCURRENCYSYM, MAX(pendingOrders.C_CURRENCY_ID) AS TRANSCURRENCYIDORDER,
   104       AND C.AD_ORG_ID = AD_ORG.AD_ORG_ID
   104            MAX(pendingOrders.DATEORDERED) AS TRANSDATEORDER, MAX(pendingOrders.AD_CLIENT_ID) AS TRANSCLIENTIDORDER,
   105       AND C.PROCESSED = 'Y'
   105            MAX(pendingOrders.AD_ORG_ID) AS TRANSORGIDORDER, C_CURRENCY_SYMBOL(MAX(pendingOrders.C_CURRENCY_ID), 0, 'Y') AS LINECURRENCYSYM,
   106       AND C.DOCSTATUS NOT IN ('CJ', 'UE', 'CA')
   106            MAX(pendingOrders.C_CURRENCY_ID) AS TRANSCURRENCYIDLINE, TO_DATE(COALESCE(MAX(pendingOrders.DATEORDERED), NOW())) AS TRANSDATELINE,
   107       AND C.ISSOTRX = 'Y'
   107            MAX(C_ORDERTAX.AD_CLIENT_ID) AS TRANSCLIENTIDLINE, MAX(C_ORDERTAX.AD_ORG_ID) AS TRANSORGIDLINE
   108       AND EXISTS (SELECT 1 FROM C_ORDER C2, C_ORDERLINE CL
   108          FROM C_BPARTNER LEFT JOIN C_INVOICESCHEDULE ON C_BPARTNER.C_INVOICESCHEDULE_ID = C_INVOICESCHEDULE.C_INVOICESCHEDULE_ID,
   109                  WHERE C2.c_order_id = C.c_order_id
   109            C_ORDERTAX, AD_ORG, C_TAX,
   110                  and c2.c_order_id=cl.c_order_id
   110            pendingOrders left join AD_REF_LIST_TRL ON pendingOrders.AD_REF_LIST_ID = AD_REF_LIST_TRL.AD_REF_LIST_ID
   111                  and cl.QTYORDERED<>cl.QTYINVOICED)
   111            AND AD_REF_LIST_TRL.AD_LANGUAGE = ?
   112       AND C.AD_Client_ID IN ('2')
   112          WHERE pendingOrders.C_ORDER_ID = C_ORDERTAX.C_ORDER_ID
   113       AND C.AD_ORG_ID IN ('2')
   113            AND pendingOrders.C_BPARTNER_ID=C_BPARTNER.C_BPARTNER_ID
   114       AND 2=2
   114            AND C_ORDERTAX.C_TAX_ID = C_TAX.C_TAX_ID
   115       ) AAA
   115            AND pendingOrders.AD_ORG_ID = AD_ORG.AD_ORG_ID
   116       ORDER BY ORGNAME, BPARTNERNAME, DATEORDERED DESC, DOCUMENTNO, LINE
   116          GROUP BY pendingOrders.C_ORDER_ID, C_ORDERTAX.C_TAX_ID
   117       ]]></Sql>
   117      ) AAA ORDER BY ORGNAME, BPARTNERNAME, DATEORDERED DESC, DOCUMENTNO, LINE
       
   118      ]]></Sql>
   118     <Parameter name="cCurrencyConv"/>
   119     <Parameter name="cCurrencyConv"/>
       
   120 	<Parameter name="cCurrencyConv"/>
       
   121 	<Parameter name="adUserClient" type="replace" optional="true" after="C.AD_Client_ID IN (" text="'1'"/>
       
   122 	<Parameter name="adUserOrg" type="replace" optional="true" after="C.AD_ORG_ID IN (" text="'1'"/>
       
   123 	<Parameter name="cBpartnerId" optional="true" after="1=1">AND C.C_BPARTNER_ID = ?</Parameter>
       
   124 	<Parameter name="cOrgId" optional="true" after="1=1">AND C.AD_ORG_ID = ?</Parameter>
       
   125 	<Parameter name="invoiceRule" optional="true" after="1=1">AND C.INVOICERULE = ?</Parameter>
       
   126 	<Parameter name="dateFrom" optional="true" after="1=1"><![CDATA[ AND C.DATEORDERED >= to_date(?) ]]></Parameter>
       
   127 	<Parameter name="dateTo" optional="true" after="1=1"><![CDATA[ AND C.DATEORDERED < to_date(?) ]]></Parameter>
   119 	<Parameter name="cCurrencyConv"/>
   128 	<Parameter name="cCurrencyConv"/>
   120 	<Parameter name="cCurrencyConv"/>
   129 	<Parameter name="cCurrencyConv"/>
   121 	<Parameter name="cCurrencyConv"/>
   130 	<Parameter name="cCurrencyConv"/>
   122 	<Parameter name="cCurrencyConv"/>
   131 	<Parameter name="cCurrencyConv"/>
   123     <Parameter name="adLanguage"/>
       
   124     <Parameter name="adUserClient" type="replace" optional="true" after="C_ORDER.AD_Client_ID IN (" text="'1'"/>
       
   125     <Parameter name="adUserOrg" type="replace" optional="true" after="C_ORDER.AD_ORG_ID IN (" text="'1'"/>
       
   126     <Parameter name="cBpartnerId" optional="true" after="1=1">AND C_ORDER.C_BPARTNER_ID = ?</Parameter>
       
   127     <Parameter name="cOrgId" optional="true" after="1=1">AND C_ORDER.AD_ORG_ID = ?</Parameter>
       
   128     <Parameter name="invoiceRule" optional="true" after="1=1">AND C_ORDER.INVOICERULE = ?</Parameter>
       
   129     <Parameter name="dateFrom" optional="true" after="1=1"><![CDATA[ AND C_ORDER.DATEORDERED >= to_date(?) ]]></Parameter>
       
   130     <Parameter name="dateTo" optional="true" after="1=1"><![CDATA[ AND C_ORDER.DATEORDERED < to_date(?) ]]></Parameter>
       
   131     <Parameter name="cCurrencyConv"/>
       
   132 	<Parameter name="cCurrencyConv"/>
   132 	<Parameter name="cCurrencyConv"/>
   133 	<Parameter name="cCurrencyConv"/>
   133 	<Parameter name="cCurrencyConv"/>
       
   134 	<Parameter name="adLanguage"/>
       
   135 	<Parameter name="cCurrencyConv"/>
       
   136 	<Parameter name="cCurrencyConv"/>
       
   137     <Parameter name="cCurrencyConv"/>
       
   138     <Parameter name="cCurrencyConv"/>
       
   139     <Parameter name="cCurrencyConv"/>
       
   140     <Parameter name="cCurrencyConv"/>
   134     <Parameter name="adLanguage"/>
   141     <Parameter name="adLanguage"/>
   135     <Parameter name="adUserClient" type="replace" optional="true" after="C.AD_Client_ID IN (" text="'2'"/>
       
   136     <Parameter name="adUserOrg" type="replace" optional="true" after="C.AD_ORG_ID IN (" text="'2'"/>
       
   137     <Parameter name="cBpartnerId" optional="true" after="2=2">AND C.C_BPARTNER_ID = ?</Parameter>
       
   138     <Parameter name="cOrgId" optional="true" after="2=2">AND C.AD_ORG_ID = ?</Parameter>
       
   139     <Parameter name="invoiceRule" optional="true" after="2=2">AND C.INVOICERULE = ?</Parameter>
       
   140     <Parameter name="dateFrom" optional="true" after="2=2"><![CDATA[ AND C.DATEORDERED >= to_date(?) ]]></Parameter>
       
   141     <Parameter name="dateTo" optional="true" after="2=2"><![CDATA[ AND C.DATEORDERED < to_date(?) ]]></Parameter>
       
   142   </SqlMethod>
   142   </SqlMethod>
   143   <SqlMethod name="bPartnerDescription" type="preparedStatement" return="String" default="">
   143   <SqlMethod name="bPartnerDescription" type="preparedStatement" return="String" default="">
   144     <SqlMethodComment></SqlMethodComment>
   144     <SqlMethodComment></SqlMethodComment>
   145     <Sql>
   145     <Sql>
   146     SELECT max(NAME) as name FROM C_BPARTNER 
   146     SELECT max(NAME) as name FROM C_BPARTNER