src/org/openbravo/erpCommon/ad_reports/ReportRefundSalesDimensionalAnalyses_data.xsql
changeset 35426 9141b6a4a4e8
parent 35410 41c88f9c32e6
parent 35425 fa495023e638
child 35427 e36d52a51aee
equal deleted inserted replaced
35410:41c88f9c32e6 35426:9141b6a4a4e8
     1 <?xml version="1.0" encoding="UTF-8" ?>
       
     2 <!--
       
     3  *************************************************************************
       
     4  * The contents of this file are subject to the Openbravo  Public  License
       
     5  * Version  1.1  (the  "License"),  being   the  Mozilla   Public  License
       
     6  * Version 1.1  with a permitted attribution clause; you may not  use this
       
     7  * file except in compliance with the License. You  may  obtain  a copy of
       
     8  * the License at http://www.openbravo.com/legal/license.html 
       
     9  * Software distributed under the License  is  distributed  on  an "AS IS"
       
    10  * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
       
    11  * License for the specific  language  governing  rights  and  limitations
       
    12  * under the License. 
       
    13  * The Original Code is Openbravo ERP. 
       
    14  * The Initial Developer of the Original Code is Openbravo SLU 
       
    15  * All portions are Copyright (C) 2001-2010 Openbravo SLU 
       
    16  * All Rights Reserved. 
       
    17  * Contributor(s):  ______________________________________.
       
    18  ************************************************************************
       
    19 -->
       
    20 
       
    21 
       
    22 
       
    23 
       
    24 
       
    25 <SqlClass name="ReportRefundSalesDimensionalAnalysesData" package="org.openbravo.erpCommon.ad_reports">
       
    26   <SqlClassComment></SqlClassComment>
       
    27   <SqlMethod name="select" type="preparedStatement" return="multiple">
       
    28     <SqlMethodComment></SqlMethodComment>
       
    29     <Sql>
       
    30     <![CDATA[
       
    31       SELECT NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5, NIVEL6, NIVEL7, 
       
    32 	  SUM(LINENETAMT) AS LINENETAMT, SUM(REFUNDAMT) AS REFUNDAMT, 
       
    33 	  C_DIVIDE(SUM(REFUNDAMT), (SUM(LINENETAMT)+SUM(REFUNDAMT)))*100 AS RATIO, 
       
    34 	  SUM(QTYINVOICED) AS QTYINVOICED, SUM(REFUNDQTY) AS REFUNDQTY, 
       
    35 	  SUM(LINENETREF) AS LINENETREF, SUM(REFUNDAMTREF) AS REFUNDAMTREF,
       
    36 	  C_DIVIDE(SUM(REFUNDAMTREF), (SUM(LINENETREF)+SUM(REFUNDAMTREF)))*100 AS RATIOREF, 
       
    37 	  SUM(QTYINVOICEDREF) AS QTYINVOICEDREF, SUM(REFUNDQTYREF) AS REFUNDQTYREF,
       
    38 	  C_CURRENCY_SYMBOL(?, 0, 'Y') AS CONVSYM,	  	  
       
    39 	  C_CURRENCY_ISOSYM(?) AS CONVISOSYM,
       
    40 	  '' AS ID, '' AS NAME, '' AS TRANSCURRENCYID, '' AS TRANSDATE, '' AS TRANSCLIENTID, '' AS TRANSORGID 
       
    41 	  FROM (SELECT NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5, NIVEL6, NIVEL7, 
       
    42       C_CURRENCY_CONVERT(SUM(LINENETAMT), TRCURRENCYID, ?, TO_DATE(TRDATE), NULL, TRCLIENTID, TRORGID) AS LINENETAMT, 
       
    43 	  C_CURRENCY_CONVERT(SUM(REFUNDAMT), TRCURRENCYID, ?, TO_DATE(TRDATE), NULL, TRCLIENTID, TRORGID) AS REFUNDAMT,  
       
    44       SUM(QTYINVOICED) AS QTYINVOICED, SUM(REFUNDQTY) AS REFUNDQTY, 
       
    45       C_CURRENCY_CONVERT(SUM(LINENETREF), TRCURRENCYID, ?, TO_DATE(TRDATE), NULL, TRCLIENTID, TRORGID) AS LINENETREF, 
       
    46 	  C_CURRENCY_CONVERT(SUM(REFUNDAMTREF), TRCURRENCYID, ?, TO_DATE(TRDATE), NULL, TRCLIENTID, TRORGID) AS REFUNDAMTREF,  
       
    47       SUM(QTYINVOICEDREF) AS QTYINVOICEDREF, SUM(REFUNDQTYREF) AS REFUNDQTYREF, 
       
    48       TRCURRENCYID, TRDATE, TRCLIENTID, TRORGID
       
    49       FROM (SELECT to_char('1') AS NIVEL1, to_char('2') AS NIVEL2, to_char('3') AS NIVEL3, to_char('4') AS NIVEL4, to_char('5') AS NIVEL5, to_char('6') AS NIVEL6, to_char('7') AS NIVEL7,
       
    50       CASE SIGN(C_ORDERLINE.LINENETAMT) WHEN  -1 THEN  0 ELSE  C_ORDERLINE.LINENETAMT END AS LINENETAMT, 
       
    51       CASE SIGN(C_ORDERLINE.LINENETAMT) WHEN  -1 THEN  -1*C_ORDERLINE.LINENETAMT ELSE  0 END AS REFUNDAMT, 
       
    52       CASE SIGN(C_ORDERLINE.QTYORDERED) WHEN  -1 THEN  0 ELSE  C_ORDERLINE.QTYORDERED END AS QTYINVOICED, 
       
    53       CASE SIGN(C_ORDERLINE.QTYORDERED) WHEN  -1 THEN  (-1*C_ORDERLINE.QTYORDERED) ELSE  0 END AS REFUNDQTY, 
       
    54       0 AS LINENETREF, 0 AS REFUNDAMTREF, 0 AS QTYINVOICEDREF, 0 AS REFUNDQTYREF, C_UOM.UOMSYMBOL,
       
    55       COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID) AS TRCURRENCYID,      
       
    56 	  TO_DATE(COALESCE(C_ORDER.DATEORDERED, NOW())) AS TRDATE,
       
    57       C_ORDERLINE.AD_CLIENT_ID AS TRCLIENTID,      
       
    58       C_ORDERLINE.AD_ORG_ID AS TRORGID
       
    59       FROM C_ORDER left join AD_USER on C_ORDER.SALESREP_ID = AD_USER.AD_USER_ID,
       
    60            C_ORDERLINE left join M_PRODUCT on C_ORDERLINE.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID
       
    61                        left join M_PRODUCT_CATEGORY on M_PRODUCT.M_PRODUCT_CATEGORY_ID = M_PRODUCT_CATEGORY.M_PRODUCT_CATEGORY_ID
       
    62                        left join C_UOM on C_ORDERLINE.C_UOM_ID = C_UOM.C_UOM_ID,
       
    63          C_BPARTNER, C_BP_GROUP, M_WAREHOUSE
       
    64       WHERE C_ORDER.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
       
    65       AND C_BPARTNER.C_BP_GROUP_ID = C_BP_GROUP.C_BP_GROUP_ID
       
    66       AND C_ORDER.C_ORDER_ID = C_ORDERLINE.C_ORDER_ID
       
    67       AND C_ORDER.M_WAREHOUSE_ID = M_WAREHOUSE.M_WAREHOUSE_ID
       
    68       AND C_ORDER.ISSOTRX = 'Y'
       
    69       AND C_ORDER.PROCESSED = 'Y'
       
    70       AND 0=0 AND C_ORDER.AD_ORG_ID IN ('1')
       
    71       AND C_ORDER.AD_CLIENT_ID IN ('8')
       
    72       AND 1=1
       
    73       UNION ALL SELECT to_char('9') AS NIVEL1 , to_char('10') AS NIVEL2 , to_char('11') AS NIVEL3 , to_char('12') AS NIVEL4 , to_char('13') AS NIVEL5 , to_char('14') AS NIVEL6 , to_char('15') AS NIVEL7 ,
       
    74       0 AS LINENETAMT, 0 AS REFUNDAMT, 0 AS QTYINVOICED, 0 AS REFUNDQTY, 
       
    75       CASE SIGN(C_ORDERLINE.LINENETAMT) WHEN  -1 THEN  0 ELSE  C_ORDERLINE.LINENETAMT END AS LINENETREF, 
       
    76       CASE SIGN(C_ORDERLINE.LINENETAMT) WHEN  -1 THEN  (-1*C_ORDERLINE.LINENETAMT) ELSE  0 END AS REFUNDAMTREF, 
       
    77       CASE SIGN(C_ORDERLINE.QTYORDERED) WHEN  -1 THEN  0 ELSE  C_ORDERLINE.QTYORDERED END AS QTYINVOICEDREF, 
       
    78       CASE SIGN(C_ORDERLINE.QTYORDERED) WHEN  -1 THEN  (-1*C_ORDERLINE.QTYORDERED) ELSE  0 END AS REFUNDQTYREF, C_UOM.UOMSYMBOL,
       
    79       COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID) AS TRCURRENCYID,      
       
    80 	  TO_DATE(COALESCE(C_ORDER.DATEORDERED, NOW())) AS TRDATE,
       
    81       C_ORDERLINE.AD_CLIENT_ID AS TRCLIENTID,      
       
    82       C_ORDERLINE.AD_ORG_ID AS TRORGID
       
    83       FROM C_ORDER left join AD_USER on C_ORDER.SALESREP_ID = AD_USER.AD_USER_ID,
       
    84            C_ORDERLINE left join M_PRODUCT on C_ORDERLINE.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID 
       
    85                        left join M_PRODUCT_CATEGORY on M_PRODUCT.M_PRODUCT_CATEGORY_ID = M_PRODUCT_CATEGORY.M_PRODUCT_CATEGORY_ID
       
    86                        left join C_UOM on C_ORDERLINE.C_UOM_ID = C_UOM.C_UOM_ID,
       
    87            C_BPARTNER, C_BP_GROUP, M_WAREHOUSE
       
    88       WHERE C_ORDER.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
       
    89       AND C_BPARTNER.C_BP_GROUP_ID = C_BP_GROUP.C_BP_GROUP_ID
       
    90       AND C_ORDER.C_ORDER_ID = C_ORDERLINE.C_ORDER_ID
       
    91       AND C_ORDER.M_WAREHOUSE_ID = M_WAREHOUSE.M_WAREHOUSE_ID
       
    92       AND C_ORDER.ISSOTRX = 'Y'
       
    93       AND C_ORDER.PROCESSED = 'Y'
       
    94       AND 3=3 AND C_ORDER.AD_ORG_ID IN ('1')
       
    95       AND C_ORDER.AD_CLIENT_ID IN('16')
       
    96       AND 2=2) AA
       
    97       WHERE LINENETAMT <> 0
       
    98       OR REFUNDAMT <> 0
       
    99       OR LINENETREF <> 0
       
   100       OR REFUNDAMTREF <> 0
       
   101       GROUP BY NIVEL1,  NIVEL2, NIVEL3, NIVEL4, NIVEL5, NIVEL6, NIVEL7, TRCURRENCYID, TRDATE, TRCLIENTID, TRORGID) ZZ      
       
   102 	  GROUP BY NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5, NIVEL6, NIVEL7, 1
       
   103      ]]></Sql>
       
   104     <Parameter name="cCurrencyConv"/>
       
   105     <Parameter name="cCurrencyConv"/>
       
   106     <Parameter name="cCurrencyConv"/>
       
   107     <Parameter name="cCurrencyConv"/>
       
   108     <Parameter name="cCurrencyConv"/>
       
   109     <Parameter name="cCurrencyConv"/>
       
   110     <Parameter name="nivel1" type="replace" optional="true" after="FROM (SELECT to_char(" text="'1'"/>
       
   111     <Parameter name="nivel2" type="replace" optional="true" after="AS NIVEL1, to_char(" text="'2'"/>
       
   112     <Parameter name="nivel3" type="replace" optional="true" after="AS NIVEL2, to_char(" text="'3'"/>
       
   113     <Parameter name="nivel4" type="replace" optional="true" after="AS NIVEL3, to_char(" text="'4'"/>
       
   114     <Parameter name="nivel5" type="replace" optional="true" after="AS NIVEL4, to_char(" text="'5'"/>
       
   115     <Parameter name="nivel6" type="replace" optional="true" after="AS NIVEL5, to_char(" text="'6'"/>
       
   116     <Parameter name="nivel7" type="replace" optional="true" after="AS NIVEL6, to_char(" text="'7'"/>
       
   117     <Parameter name="adOrgId" optional="true" type="replace" after="AND 0=0 AND C_ORDER.AD_ORG_ID IN (" text="'1'"/>
       
   118     <Parameter name="adUserClient" type="replace" optional="true" after="AND C_ORDER.AD_CLIENT_ID IN (" text="'8'"/>
       
   119     <Parameter name="dateFrom" optional="true" after="AND 1=1"><![CDATA[AND C_ORDER.DATEORDERED >= to_date(?)]]></Parameter>
       
   120     <Parameter name="dateTo" optional="true" after="AND 1=1"><![CDATA[AND C_ORDER.DATEORDERED < to_date(?)]]></Parameter>
       
   121     <Parameter name="cBpartnerGroupId" optional="true" after="AND 1=1"><![CDATA[AND C_BP_GROUP.C_BP_GROUP_ID = ?]]></Parameter>
       
   122     <Parameter name="cBpartner" optional="true" type="argument" after="AND 1=1" text=" AND C_BPARTNER.C_BPARTNER_ID IN"/>
       
   123     <Parameter name="mProductCategoryId" optional="true" after="AND 1=1"><![CDATA[AND M_PRODUCT_CATEGORY.M_PRODUCT_CATEGORY_ID = ?]]></Parameter>
       
   124     <Parameter name="mProduct" optional="true" type="argument" after="AND 1=1"><![CDATA[ AND M_PRODUCT.M_PRODUCT_ID IN]]></Parameter>
       
   125     <Parameter name="salesrepId" optional="true" after="AND 1=1"><![CDATA[AND C_ORDER.SALESREP_ID = ?]]></Parameter>
       
   126     <Parameter name="mWarehouseId" optional="true" after="AND 1=1"><![CDATA[AND M_WAREHOUSE.M_WAREHOUSE_ID = ?]]></Parameter>
       
   127     <Parameter name="nivel1" type="replace" optional="true" after="UNION ALL SELECT to_char(" text="'9'"/>
       
   128     <Parameter name="nivel2" type="replace" optional="true" after="AS NIVEL1 , to_char(" text="'10'"/>
       
   129     <Parameter name="nivel3" type="replace" optional="true" after="AS NIVEL2 , to_char(" text="'11'"/>
       
   130     <Parameter name="nivel4" type="replace" optional="true" after="AS NIVEL3 , to_char(" text="'12'"/>
       
   131     <Parameter name="nivel5" type="replace" optional="true" after="AS NIVEL4 , to_char(" text="'13'"/>
       
   132     <Parameter name="nivel6" type="replace" optional="true" after="AS NIVEL5 , to_char(" text="'14'"/>
       
   133     <Parameter name="nivel7" type="replace" optional="true" after="AS NIVEL6 , to_char(" text="'15'"/>
       
   134     <Parameter name="adOrgId" optional="true" type="replace" after="AND 3=3 AND C_ORDER.AD_ORG_ID IN (" text="'1'"/>
       
   135     <Parameter name="adUserClient" type="replace" optional="true" after="AND C_ORDER.AD_CLIENT_ID IN(" text="'16'"/>
       
   136     <Parameter name="dateFromRef" optional="true" after="AND 2=2"><![CDATA[AND C_ORDER.DATEORDERED >= to_date(?)]]></Parameter>
       
   137     <Parameter name="dateToRef" optional="true" after="AND 2=2"><![CDATA[AND C_ORDER.DATEORDERED < to_date(?)]]></Parameter>
       
   138     <Parameter name="cBpartnerGroupId" optional="true" after="AND 2=2"><![CDATA[AND C_BP_GROUP.C_BP_GROUP_ID = ?]]></Parameter>
       
   139     <Parameter name="cBpartner" optional="true" type="argument" after="AND 2=2" text=" AND C_BPARTNER.C_BPARTNER_ID IN"/>
       
   140     <Parameter name="mProductCategoryId" optional="true" after="AND 2=2"><![CDATA[AND M_PRODUCT_CATEGORY.M_PRODUCT_CATEGORY_ID = ?]]></Parameter>
       
   141     <Parameter name="mProduct" optional="true" type="argument" after="AND 2=2"><![CDATA[ AND M_PRODUCT.M_PRODUCT_ID IN]]></Parameter>
       
   142     <Parameter name="salesrepId" optional="true" after="AND 2=2"><![CDATA[AND C_ORDER.SALESREP_ID = ?]]></Parameter>
       
   143     <Parameter name="mWarehouseId" optional="true" after="AND 2=2"><![CDATA[AND M_WAREHOUSE.M_WAREHOUSE_ID = ?]]></Parameter>
       
   144     <Parameter name="orderby" type="replace" optional="true" after="GROUP BY NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5, NIVEL6, NIVEL7" text=", 1"/>
       
   145   </SqlMethod>
       
   146   <SqlMethod name="set" type="constant" return="multiple">
       
   147       <SqlMethodComment></SqlMethodComment>
       
   148       <Sql></Sql>
       
   149   </SqlMethod>
       
   150   <SqlMethod name="selectNoComparative" type="preparedStatement" return="multiple">
       
   151     <SqlMethodComment></SqlMethodComment>
       
   152     <Sql>
       
   153     <![CDATA[
       
   154       SELECT NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5, NIVEL6, NIVEL7, 
       
   155 	  SUM(LINENETAMT) AS LINENETAMT, SUM(REFUNDAMT) AS REFUNDAMT, 
       
   156 	  C_DIVIDE(SUM(REFUNDAMT), (SUM(LINENETAMT)+SUM(REFUNDAMT)))*100 AS RATIO, 
       
   157 	  SUM(QTYINVOICED) AS QTYINVOICED, SUM(REFUNDQTY) AS REFUNDQTY, 
       
   158 	  SUM(LINENETREF) AS LINENETREF, SUM(REFUNDAMTREF) AS REFUNDAMTREF, 
       
   159 	  SUM(QTYINVOICEDREF) AS QTYINVOICEDREF, SUM(REFUNDQTYREF) AS REFUNDQTYREF,
       
   160 	  C_CURRENCY_SYMBOL(?, 0, 'Y') AS CONVSYM,        
       
   161 	  C_CURRENCY_ISOSYM(?) AS CONVISOSYM
       
   162       FROM (SELECT NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5, NIVEL6, NIVEL7, 
       
   163       C_CURRENCY_CONVERT(SUM(LINENETAMT), TRCURRENCYID, ?, TO_DATE(TRDATE), NULL, TRCLIENTID, TRORGID) AS LINENETAMT, 
       
   164       C_CURRENCY_CONVERT(SUM(REFUNDAMT), TRCURRENCYID, ?, TO_DATE(TRDATE), NULL, TRCLIENTID, TRORGID) AS REFUNDAMT,  
       
   165       SUM(QTYINVOICED) AS QTYINVOICED, SUM(REFUNDQTY) AS REFUNDQTY, 
       
   166       SUM(LINENETREF) AS LINENETREF, SUM(REFUNDAMTREF) AS REFUNDAMTREF, 
       
   167       SUM(QTYINVOICEDREF) AS QTYINVOICEDREF, SUM(REFUNDQTYREF) AS REFUNDQTYREF,
       
   168 	  TRCURRENCYID, TRDATE, TRCLIENTID, TRORGID
       
   169       FROM (SELECT to_char('1') AS NIVEL1, to_char('2') AS NIVEL2, to_char('3') AS NIVEL3, to_char('4') AS NIVEL4, to_char('5') AS NIVEL5, to_char('6') AS NIVEL6, to_char('7') AS NIVEL7,
       
   170       CASE SIGN(C_ORDERLINE.LINENETAMT) WHEN  -1 THEN  0 ELSE  C_ORDERLINE.LINENETAMT END AS LINENETAMT, 
       
   171       CASE SIGN(C_ORDERLINE.LINENETAMT) WHEN  -1 THEN  -1*C_ORDERLINE.LINENETAMT ELSE  0 END AS REFUNDAMT, 
       
   172       CASE SIGN(C_ORDERLINE.QTYORDERED) WHEN  -1 THEN  0 ELSE  C_ORDERLINE.QTYORDERED END AS QTYINVOICED, 
       
   173       CASE SIGN(C_ORDERLINE.QTYORDERED) WHEN  -1 THEN  (-1*C_ORDERLINE.QTYORDERED) ELSE  0 END AS REFUNDQTY, 
       
   174       0 AS LINENETREF, 0 AS REFUNDAMTREF, 0 AS QTYINVOICEDREF, 0 AS REFUNDQTYREF, C_UOM.UOMSYMBOL,
       
   175       COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID) AS TRCURRENCYID,      
       
   176 	  TO_DATE(COALESCE(C_ORDER.DATEORDERED, NOW())) AS TRDATE,
       
   177 	  C_ORDERLINE.AD_CLIENT_ID AS TRCLIENTID,      
       
   178 	  C_ORDERLINE.AD_ORG_ID AS TRORGID
       
   179       FROM C_ORDER left join AD_USER on C_ORDER.SALESREP_ID = AD_USER.AD_USER_ID,
       
   180            C_ORDERLINE left join M_PRODUCT on C_ORDERLINE.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID
       
   181                        left join M_PRODUCT_CATEGORY on M_PRODUCT.M_PRODUCT_CATEGORY_ID = M_PRODUCT_CATEGORY.M_PRODUCT_CATEGORY_ID
       
   182                        left join C_UOM on C_ORDERLINE.C_UOM_ID = C_UOM.C_UOM_ID,
       
   183        C_BPARTNER, C_BP_GROUP, M_WAREHOUSE
       
   184       WHERE C_ORDER.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
       
   185       AND C_BPARTNER.C_BP_GROUP_ID = C_BP_GROUP.C_BP_GROUP_ID
       
   186       AND C_ORDER.C_ORDER_ID = C_ORDERLINE.C_ORDER_ID
       
   187       AND C_ORDER.M_WAREHOUSE_ID = M_WAREHOUSE.M_WAREHOUSE_ID
       
   188       AND C_ORDER.ISSOTRX = 'Y'
       
   189       AND C_ORDER.PROCESSED = 'Y'
       
   190       AND 0=0 AND C_ORDER.AD_ORG_ID IN ('1')
       
   191       AND C_ORDER.AD_CLIENT_ID IN ('8')
       
   192       AND 1=1) AA
       
   193       WHERE LINENETAMT <> 0
       
   194       OR REFUNDAMT <> 0
       
   195       OR LINENETREF <> 0
       
   196       OR REFUNDAMTREF <> 0
       
   197       GROUP BY NIVEL1,  NIVEL2, NIVEL3, NIVEL4, NIVEL5, NIVEL6, NIVEL7, TRCURRENCYID, TRDATE, TRCLIENTID, TRORGID) ZZ
       
   198       GROUP BY NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5, NIVEL6, NIVEL7, 1 
       
   199      ]]></Sql>
       
   200     <Parameter name="cCurrencyConv"/>
       
   201     <Parameter name="cCurrencyConv"/>
       
   202     <Parameter name="cCurrencyConv"/>
       
   203     <Parameter name="cCurrencyConv"/>
       
   204     <Parameter name="nivel1" type="replace" optional="true" after="FROM (SELECT to_char(" text="'1'"/>
       
   205     <Parameter name="nivel2" type="replace" optional="true" after="AS NIVEL1, to_char(" text="'2'"/>
       
   206     <Parameter name="nivel3" type="replace" optional="true" after="AS NIVEL2, to_char(" text="'3'"/>
       
   207     <Parameter name="nivel4" type="replace" optional="true" after="AS NIVEL3, to_char(" text="'4'"/>
       
   208     <Parameter name="nivel5" type="replace" optional="true" after="AS NIVEL4, to_char(" text="'5'"/>
       
   209     <Parameter name="nivel6" type="replace" optional="true" after="AS NIVEL5, to_char(" text="'6'"/>
       
   210     <Parameter name="nivel7" type="replace" optional="true" after="AS NIVEL6, to_char(" text="'7'"/>
       
   211     <Parameter name="adOrgId" optional="true" type="replace" after="AND 0=0 AND C_ORDER.AD_ORG_ID IN (" text="'1'"/>
       
   212     <Parameter name="adUserClient" type="replace" optional="true" after="AND C_ORDER.AD_CLIENT_ID IN (" text="'8'"/>
       
   213     <Parameter name="dateFrom" optional="true" after="AND 1=1"><![CDATA[AND C_ORDER.DATEORDERED >= to_date(?)]]></Parameter>
       
   214     <Parameter name="dateTo" optional="true" after="AND 1=1"><![CDATA[AND C_ORDER.DATEORDERED < to_date(?)]]></Parameter>
       
   215     <Parameter name="cBpartnerGroupId" optional="true" after="AND 1=1"><![CDATA[AND C_BP_GROUP.C_BP_GROUP_ID = ?]]></Parameter>
       
   216     <Parameter name="cBpartner" optional="true" type="argument" after="AND 1=1" text=" AND C_BPARTNER.C_BPARTNER_ID IN"/>
       
   217     <Parameter name="mProductCategoryId" optional="true" after="AND 1=1"><![CDATA[AND M_PRODUCT_CATEGORY.M_PRODUCT_CATEGORY_ID = ?]]></Parameter>
       
   218     <Parameter name="mProduct" optional="true" type="argument" after="AND 1=1"><![CDATA[ AND M_PRODUCT.M_PRODUCT_ID IN]]></Parameter>
       
   219     <Parameter name="salesrepId" optional="true" after="AND 1=1"><![CDATA[AND C_ORDER.SALESREP_ID = ?]]></Parameter>
       
   220     <Parameter name="mWarehouseId" optional="true" after="AND 1=1"><![CDATA[AND M_WAREHOUSE.M_WAREHOUSE_ID = ?]]></Parameter>
       
   221     <Parameter name="orderby" type="replace" optional="true" after="GROUP BY NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5, NIVEL6, NIVEL7" text=", 1"/>
       
   222   </SqlMethod>
       
   223 
       
   224   <SqlMethod name="selectBpgroup" type="preparedStatement" return="String" default="">
       
   225     <SqlMethodComment></SqlMethodComment>
       
   226     <Sql>
       
   227     <![CDATA[
       
   228       SELECT C_BP_GROUP.NAME
       
   229       FROM C_BP_GROUP
       
   230       WHERE C_BP_GROUP.C_BP_GROUP_ID = ?
       
   231      ]]></Sql>
       
   232     <Parameter name="cBpGroupId"/>
       
   233   </SqlMethod>
       
   234 
       
   235   <SqlMethod name="selectProductCategory" type="preparedStatement" return="String" default="">
       
   236     <SqlMethodComment></SqlMethodComment>
       
   237     <Sql>
       
   238     <![CDATA[
       
   239       SELECT M_PRODUCT_CATEGORY.NAME
       
   240       FROM M_PRODUCT_CATEGORY
       
   241       WHERE M_PRODUCT_CATEGORY.M_PRODUCT_CATEGORY_ID = ?
       
   242      ]]></Sql>
       
   243     <Parameter name="mProductCategoryId"/>
       
   244   </SqlMethod>
       
   245 
       
   246   <SqlMethod name="selectSalesrep" type="preparedStatement" return="String" default="">
       
   247     <SqlMethodComment></SqlMethodComment>
       
   248     <Sql>
       
   249     <![CDATA[
       
   250       SELECT AD_USER.FIRSTNAME||' '||AD_USER.LASTNAME
       
   251       FROM AD_USER
       
   252       WHERE AD_USER.AD_USER_ID = ?
       
   253      ]]></Sql>
       
   254     <Parameter name="salesrepId"/>
       
   255   </SqlMethod>
       
   256 
       
   257   <SqlMethod name="selectMwarehouse" type="preparedStatement" return="String" default="">
       
   258     <SqlMethodComment></SqlMethodComment>
       
   259     <Sql>
       
   260     <![CDATA[
       
   261       SELECT M_WAREHOUSE.NAME
       
   262       FROM M_WAREHOUSE
       
   263       WHERE M_WAREHOUSE.M_WAREHOUSE_ID = ?
       
   264      ]]></Sql>
       
   265     <Parameter name="mWarehouseId"/>
       
   266   </SqlMethod>
       
   267 
       
   268   <SqlMethod name="selectNotShown" type="preparedStatement" return="multiple">
       
   269     <SqlMethodComment></SqlMethodComment>
       
   270     <Sql>
       
   271     <![CDATA[
       
   272       SELECT ID, NAME
       
   273       FROM (SELECT VALUE AS ID, NAME 
       
   274               FROM AD_REF_LIST 
       
   275              WHERE AD_REFERENCE_ID = '800087'
       
   276              AND AD_REF_LIST.VALUE IN ('1', '2', '3', '4', '5', '6', '7')
       
   277              ORDER BY TO_NUMBER(VALUE)) AA
       
   278       WHERE 1=1 
       
   279      ]]></Sql>
       
   280      <Parameter name="notShown" optional="true" type="argument" after="WHERE 1=1 "><![CDATA[AND ID NOT IN]]></Parameter>
       
   281   </SqlMethod>
       
   282   <SqlMethod name="selectShown" type="preparedStatement" return="multiple">
       
   283     <SqlMethodComment></SqlMethodComment>
       
   284     <Sql>
       
   285     <![CDATA[
       
   286       SELECT ID, NAME
       
   287       FROM (SELECT VALUE AS ID, NAME 
       
   288               FROM AD_REF_LIST 
       
   289              WHERE AD_REFERENCE_ID = '800087' 
       
   290              AND AD_REF_LIST.VALUE IN ('1', '2', '3', '4', '5', '6', '7')
       
   291              ORDER BY TO_NUMBER(VALUE)) AA
       
   292       WHERE 1=1 
       
   293      ]]></Sql>
       
   294      <Parameter name="shown" optional="true" type="argument" after="WHERE 1=1 "><![CDATA[AND ID IN]]></Parameter>
       
   295   </SqlMethod>
       
   296 
       
   297   <SqlMethod name="selectNotShownTrl" type="preparedStatement" return="multiple">
       
   298     <SqlMethodComment></SqlMethodComment>
       
   299     <Sql>
       
   300     <![CDATA[
       
   301       SELECT ID, NAME
       
   302       FROM (SELECT VALUE AS ID, T.NAME 
       
   303               FROM AD_REF_LIST_trl T,
       
   304                    AD_REF_LIST     L
       
   305              WHERE l.AD_REFERENCE_ID = '800087'
       
   306                AND l.AD_REF_LIST_ID  = t.AD_REF_LIST_ID
       
   307                AND L.VALUE IN ('1', '2', '3', '4', '5', '6', '7')
       
   308                AND t.AD_LANGUAGE = ?
       
   309              ORDER BY TO_NUMBER(VALUE)) AA
       
   310       WHERE 1=1 
       
   311      ]]></Sql>
       
   312      <Parameter name="lang"/> 
       
   313      <Parameter name="notShown" optional="true" type="argument" after="WHERE 1=1 "><![CDATA[AND ID NOT IN]]></Parameter>
       
   314   </SqlMethod>
       
   315   <SqlMethod name="selectShownTrl" type="preparedStatement" return="multiple">
       
   316     <SqlMethodComment></SqlMethodComment>
       
   317     <Sql>
       
   318     <![CDATA[
       
   319       SELECT ID, NAME
       
   320       FROM (SELECT VALUE AS ID, T.NAME 
       
   321               FROM AD_REF_LIST_trl T,
       
   322                    AD_REF_LIST     L
       
   323              WHERE l.AD_REFERENCE_ID = '800087'
       
   324                AND l.AD_REF_LIST_ID  = t.AD_REF_LIST_ID
       
   325                AND L.VALUE IN ('1', '2', '3', '4', '5', '6', '7')
       
   326                AND t.AD_LANGUAGE = ?
       
   327              ORDER BY TO_NUMBER(VALUE)) AA
       
   328       WHERE 1=1 
       
   329      ]]></Sql>
       
   330      <Parameter name="lang"/> 
       
   331      <Parameter name="shown" optional="true" type="argument" after="WHERE 1=1 "><![CDATA[AND ID IN]]></Parameter>
       
   332   </SqlMethod>
       
   333 </SqlClass>