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