src/org/openbravo/erpCommon/ad_reports/ReportInvoiceCustomerDimensionalAnalysesJR_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
<SqlClass name="ReportInvoiceCustomerDimensionalAnalysesJRData" package="org.openbravo.erpCommon.ad_reports">
carlos@0
    22
  <SqlClassComment></SqlClassComment>
carlos@0
    23
  <SqlMethod name="select" type="preparedStatement" return="multiple">
carlos@0
    24
    <SqlMethodComment></SqlMethodComment>
carlos@0
    25
    <Sql>
carlos@0
    26
    <![CDATA[
carlos@0
    27
      SELECT NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5, NIVEL6, NIVEL7, NIVEL8, NIVEL9, NIVEL10, SUM(LINENETAMT) AS AMOUNT, SUM(QTYINVOICED) AS QTY, SUM(COST) AS COST, SUM(LINENETREF) AS AMOUNTREF, SUM(QTYINVOICEDREF) AS QTYREF, SUM(COSTREF) AS COSTREF, SUM(WEIGHT) AS WEIGHT, SUM(WEIGHT_REF) AS WEIGHTREF, '' AS ID, '' AS NAME, UOMSYMBOL
carlos@0
    28
      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('16') AS NIVEL8, to_char('18') AS NIVEL9, to_char('20') AS NIVEL10,
carlos@0
    29
      C_INVOICELINE.LINENETAMT, C_INVOICELINE.QTYINVOICED, GET_PRODUCT_COST(M_PRODUCT.M_PRODUCT_ID, C_INVOICE.DATEINVOICED, NULL)*C_INVOICELINE.QTYINVOICED AS COST, C_INVOICELINE.QTYINVOICED*M_PRODUCT.WEIGHT AS WEIGHT, 0 AS LINENETREF, 0 AS QTYINVOICEDREF, 0 AS COSTREF, 0 AS WEIGHT_REF, C_UOM.UOMSYMBOL
carlos@0
    30
      FROM C_INVOICE left join AD_USER on C_INVOICE.SALESREP_ID = AD_USER.AD_USER_ID
carlos@0
    31
                     left join C_PROJECT on C_INVOICE.C_PROJECT_ID = C_PROJECT.C_PROJECT_ID,
carlos@0
    32
           C_INVOICELINE left join C_UOM on C_INVOICELINE.C_UOM_ID = C_UOM.C_UOM_ID
carlos@0
    33
                         left join M_PRODUCT on C_INVOICELINE.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID
carlos@0
    34
                         left join M_PRODUCT_CATEGORY on M_PRODUCT.M_PRODUCT_CATEGORY_ID = M_PRODUCT_CATEGORY.M_PRODUCT_CATEGORY_ID
carlos@0
    35
                         left join M_INOUTLINE on C_INVOICELINE.M_INOUTLINE_ID = M_INOUTLINE.M_INOUTLINE_ID
carlos@0
    36
                         left join  M_INOUT ON M_INOUTLINE.M_INOUT_ID = M_INOUT.M_INOUT_ID,
carlos@0
    37
           C_BPARTNER left join C_BPARTNER CB on C_BPARTNER.SALESREP_ID = CB.C_BPARTNER_ID,
carlos@0
    38
           C_BP_GROUP, AD_ORG
carlos@0
    39
      WHERE C_INVOICE.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
carlos@0
    40
      AND C_BPARTNER.C_BP_GROUP_ID = C_BP_GROUP.C_BP_GROUP_ID
carlos@0
    41
      AND C_INVOICE.C_INVOICE_ID = C_INVOICELINE.C_INVOICE_ID
carlos@0
    42
      AND C_INVOICE.AD_ORG_ID = AD_ORG.AD_ORG_ID
carlos@0
    43
      AND C_INVOICE.ISSOTRX = 'Y'
carlos@0
    44
      AND C_INVOICE.PROCESSED = 'Y'
carlos@0
    45
      AND 0=0 AND AD_ORG.AD_ORG_ID IN ('1')
carlos@0
    46
      AND C_INVOICE.AD_CLIENT_ID IN ('7')
carlos@0
    47
      AND 1=1
carlos@0
    48
      UNION ALL SELECT to_char('8') AS NIVEL1 , to_char('9') AS NIVEL2 , to_char('10') AS NIVEL3 , to_char('11') AS NIVEL4 , to_char('12') AS NIVEL5 , to_char('13') AS NIVEL6 , to_char('14') AS NIVEL7 , to_char('17') AS NIVEL8 , to_char('19') AS NIVEL9 , to_char('21') AS NIVEL10,
carlos@0
    49
      0 AS LINENETAMT, 0 AS QTYINVOICED, 0 AS COST, 0 AS COST, C_INVOICELINE.LINENETAMT AS LINENETREF, C_INVOICELINE.QTYINVOICED AS QTYINVOICEDREF, GET_PRODUCT_COST(M_PRODUCT.M_PRODUCT_ID, C_INVOICE.DATEINVOICED, NULL)*C_INVOICELINE.QTYINVOICED AS COSTREF, C_INVOICELINE.QTYINVOICED*M_PRODUCT.WEIGHT AS WEIGHT_REF, C_UOM.UOMSYMBOL
carlos@0
    50
      FROM C_INVOICE left join AD_USER on C_INVOICE.SALESREP_ID = AD_USER.AD_USER_ID
carlos@0
    51
                     left join C_PROJECT on C_INVOICE.C_PROJECT_ID = C_PROJECT.C_PROJECT_ID, 
carlos@0
    52
           C_INVOICELINE left join M_PRODUCT on C_INVOICELINE.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID
carlos@0
    53
                         left join C_UOM     on C_INVOICELINE.C_UOM_ID = C_UOM.C_UOM_ID
carlos@0
    54
                         left join M_PRODUCT_CATEGORY on M_PRODUCT.M_PRODUCT_CATEGORY_ID = M_PRODUCT_CATEGORY.M_PRODUCT_CATEGORY_ID
carlos@0
    55
                         left join M_INOUTLINE on C_INVOICELINE.M_INOUTLINE_ID = M_INOUTLINE.M_INOUTLINE_ID
carlos@0
    56
                         left join  M_INOUT ON M_INOUTLINE.M_INOUT_ID = M_INOUT.M_INOUT_ID,
carlos@0
    57
           C_BPARTNER left join C_BPARTNER CB on C_BPARTNER.SALESREP_ID = CB.C_BPARTNER_ID,
carlos@0
    58
           C_BP_GROUP, AD_ORG
carlos@0
    59
      WHERE C_INVOICE.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
carlos@0
    60
      AND C_BPARTNER.C_BP_GROUP_ID = C_BP_GROUP.C_BP_GROUP_ID
carlos@0
    61
      AND C_INVOICE.C_INVOICE_ID = C_INVOICELINE.C_INVOICE_ID
carlos@0
    62
      AND C_INVOICE.AD_ORG_ID = AD_ORG.AD_ORG_ID
carlos@0
    63
      AND C_INVOICE.ISSOTRX = 'Y'
carlos@0
    64
      AND C_INVOICE.PROCESSED = 'Y'
carlos@0
    65
      AND 3=3 AND AD_ORG.AD_ORG_ID IN ('1')
carlos@0
    66
      AND C_INVOICE.AD_CLIENT_ID IN('14')
carlos@0
    67
      AND 2=2
carlos@0
    68
      ORDER BY NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5, NIVEL6, NIVEL7, NIVEL8, NIVEL9, NIVEL10) AA
carlos@0
    69
      GROUP BY NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5, NIVEL6, NIVEL7, NIVEL8, NIVEL9, NIVEL10, UOMSYMBOL
carlos@0
    70
     ]]></Sql>
carlos@0
    71
    <Parameter name="nivel1" type="replace" optional="true" after="FROM (SELECT to_char(" text="'1'"/>
carlos@0
    72
    <Parameter name="nivel2" type="replace" optional="true" after="AS NIVEL1, to_char(" text="'2'"/>
carlos@0
    73
    <Parameter name="nivel3" type="replace" optional="true" after="AS NIVEL2, to_char(" text="'3'"/>
carlos@0
    74
    <Parameter name="nivel4" type="replace" optional="true" after="AS NIVEL3, to_char(" text="'4'"/>
carlos@0
    75
    <Parameter name="nivel5" type="replace" optional="true" after="AS NIVEL4, to_char(" text="'5'"/>
carlos@0
    76
    <Parameter name="nivel6" type="replace" optional="true" after="AS NIVEL5, to_char(" text="'6'"/>
carlos@0
    77
    <Parameter name="nivel7" type="replace" optional="true" after="AS NIVEL6, to_char(" text="'7'"/>
carlos@0
    78
    <Parameter name="nivel8" type="replace" optional="true" after="AS NIVEL7, to_char(" text="'16'"/>
carlos@0
    79
    <Parameter name="nivel9" type="replace" optional="true" after="AS NIVEL8, to_char(" text="'18'"/>
carlos@0
    80
    <Parameter name="nivel10" type="replace" optional="true" after="AS NIVEL9, to_char(" text="'20'"/>
carlos@0
    81
    <Parameter name="adOrgId" optional="true" type="replace" after="AND 0=0 AND AD_ORG.AD_ORG_ID IN (" text="'1'"/>
carlos@0
    82
    <Parameter name="adUserClient" type="replace" optional="true" after="AND C_INVOICE.AD_CLIENT_ID IN (" text="'7'"/>
carlos@0
    83
    <Parameter name="dateFrom" optional="true" after="AND 1=1"><![CDATA[AND C_INVOICE.DATEINVOICED >= to_date(?)]]></Parameter>
carlos@0
    84
    <Parameter name="dateTo" optional="true" after="AND 1=1"><![CDATA[AND C_INVOICE.DATEINVOICED < to_date(?)]]></Parameter>
carlos@423
    85
    <Parameter name="cBpartnerGroupId" optional="true" after="AND 1=1"><![CDATA[AND C_BP_GROUP.C_BP_GROUP_ID = TO_NUMBER(?)]]></Parameter>
carlos@0
    86
    <Parameter name="cBpartnerId" optional="true" type="argument" after="AND 1=1"><![CDATA[AND C_BPARTNER.C_BPARTNER_ID IN]]></Parameter>
carlos@423
    87
    <Parameter name="mProductCategoryId" optional="true" after="AND 1=1"><![CDATA[AND M_PRODUCT_CATEGORY.M_PRODUCT_CATEGORY_ID = TO_NUMBER(?)]]></Parameter>
carlos@0
    88
    <Parameter name="mProductId" optional="true" type="argument" after="AND 1=1"><![CDATA[AND M_PRODUCT.M_PRODUCT_ID IN]]></Parameter>
carlos@423
    89
    <Parameter name="salesrepId" optional="true" after="AND 1=1"><![CDATA[AND C_INVOICE.SALESREP_ID = TO_NUMBER(?)]]></Parameter>
carlos@423
    90
    <Parameter name="partnerSalesrepId" optional="true" after="AND 1=1"><![CDATA[AND CB.C_BPARTNER_ID = TO_NUMBER(?)]]></Parameter>
carlos@423
    91
    <Parameter name="cProjectId" optional="true" after="AND 1=1"><![CDATA[AND C_PROJECT.C_PROJECT_ID = TO_NUMBER(?)]]></Parameter>
carlos@0
    92
    <Parameter name="producttype" optional="true" after="AND 1=1"><![CDATA[AND M_PRODUCT.PRODUCTTYPE = ?]]></Parameter>
carlos@0
    93
    <Parameter name="nivel1" type="replace" optional="true" after="UNION ALL SELECT to_char(" text="'8'"/>
carlos@0
    94
    <Parameter name="nivel2" type="replace" optional="true" after="AS NIVEL1 , to_char(" text="'9'"/>
carlos@0
    95
    <Parameter name="nivel3" type="replace" optional="true" after="AS NIVEL2 , to_char(" text="'10'"/>
carlos@0
    96
    <Parameter name="nivel4" type="replace" optional="true" after="AS NIVEL3 , to_char(" text="'11'"/>
carlos@0
    97
    <Parameter name="nivel5" type="replace" optional="true" after="AS NIVEL4 , to_char(" text="'12'"/>
carlos@0
    98
    <Parameter name="nivel6" type="replace" optional="true" after="AS NIVEL5 , to_char(" text="'13'"/>
carlos@0
    99
    <Parameter name="nivel7" type="replace" optional="true" after="AS NIVEL6 , to_char(" text="'14'"/>
carlos@0
   100
    <Parameter name="nivel8" type="replace" optional="true" after="AS NIVEL7 , to_char(" text="'17'"/>
carlos@0
   101
    <Parameter name="nivel9" type="replace" optional="true" after="AS NIVEL8 , to_char(" text="'19'"/>
carlos@0
   102
    <Parameter name="nivel10" type="replace" optional="true" after="AS NIVEL9 , to_char(" text="'21'"/>
carlos@0
   103
    <Parameter name="adOrgId" optional="true" type="replace" after="AND 3=3 AND AD_ORG.AD_ORG_ID IN (" text="'1'"/>
carlos@0
   104
    <Parameter name="adUserClient" type="replace" optional="true" after="AND C_INVOICE.AD_CLIENT_ID IN(" text="'14'"/>
carlos@0
   105
    <Parameter name="dateFromRef" optional="true" after="AND 2=2"><![CDATA[AND C_INVOICE.DATEINVOICED >= to_date(?)]]></Parameter>
carlos@0
   106
    <Parameter name="dateToRef" optional="true" after="AND 2=2"><![CDATA[AND C_INVOICE.DATEINVOICED < to_date(?)]]></Parameter>
carlos@423
   107
    <Parameter name="cBpartnerGroupId" optional="true" after="AND 2=2"><![CDATA[AND C_BP_GROUP.C_BP_GROUP_ID = TO_NUMBER(?)]]></Parameter>
carlos@0
   108
    <Parameter name="cBpartnerId" optional="true" type="argument" after="AND 2=2"><![CDATA[AND C_BPARTNER.C_BPARTNER_ID IN]]></Parameter>
carlos@423
   109
    <Parameter name="mProductCategoryId" optional="true" after="AND 2=2"><![CDATA[AND M_PRODUCT_CATEGORY.M_PRODUCT_CATEGORY_ID = TO_NUMBER(?)]]></Parameter>
carlos@0
   110
    <Parameter name="mProductId" optional="true" type="argument" after="AND 2=2"><![CDATA[AND M_PRODUCT.M_PRODUCT_ID IN]]></Parameter>
carlos@423
   111
    <Parameter name="salesrepId" optional="true" after="AND 2=2"><![CDATA[AND C_INVOICE.SALESREP_ID = TO_NUMBER(?)]]></Parameter>
carlos@423
   112
    <Parameter name="partnerSalesrepId" optional="true" after="AND 2=2"><![CDATA[AND CB.C_BPARTNER_ID = TO_NUMBER(?)]]></Parameter>
carlos@423
   113
    <Parameter name="cProjectId" optional="true" after="AND 2=2"><![CDATA[AND C_PROJECT.C_PROJECT_ID = TO_NUMBER(?)]]></Parameter>
carlos@0
   114
    <Parameter name="producttype" optional="true" after="AND 2=2"><![CDATA[AND M_PRODUCT.PRODUCTTYPE = ?]]></Parameter>
carlos@0
   115
    <Parameter name="orderby" type="replace" optional="true" after="GROUP BY NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5, NIVEL6, NIVEL7, NIVEL8, NIVEL9, NIVEL10, UOMSYMBOL" text=", 1"/>
carlos@0
   116
  </SqlMethod>
carlos@0
   117
  <SqlMethod name="set" type="constant" return="multiple">
carlos@0
   118
      <SqlMethodComment></SqlMethodComment>
carlos@0
   119
      <Sql></Sql>
carlos@0
   120
  </SqlMethod>
carlos@0
   121
  <SqlMethod name="selectNoComparative" type="preparedStatement" return="multiple">
carlos@0
   122
    <SqlMethodComment></SqlMethodComment>
carlos@0
   123
    <Sql>
carlos@0
   124
    <![CDATA[
carlos@0
   125
      SELECT NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5, NIVEL6, NIVEL7, NIVEL8, NIVEL9, NIVEL10, SUM(LINENETAMT) AS AMOUNT, SUM(QTYINVOICED) AS QTY, SUM(COST) AS COST, SUM(LINENETREF) AS AMOUNTREF, SUM(QTYINVOICEDREF) AS QTYREF, SUM(WEIGHT) AS WEIGHT, SUM(WEIGHT_REF) AS WEIGHTREF, '' AS ID, '' AS NAME, UOMSYMBOL
carlos@0
   126
      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('9') AS NIVEL8, to_char('10') AS NIVEL9, to_char('11') AS NIVEL10,
carlos@0
   127
      C_INVOICELINE.LINENETAMT, C_INVOICELINE.QTYINVOICED, GET_PRODUCT_COST(M_PRODUCT.M_PRODUCT_ID, C_INVOICE.DATEINVOICED, NULL)*C_INVOICELINE.QTYINVOICED AS COST, 0 AS LINENETREF, 0 AS QTYINVOICEDREF, C_UOM.UOMSYMBOL, C_INVOICELINE.QTYINVOICED*M_PRODUCT.WEIGHT AS WEIGHT, 0 AS WEIGHT_REF
carlos@0
   128
      FROM C_INVOICE left join AD_USER on C_INVOICE.SALESREP_ID = AD_USER.AD_USER_ID
carlos@0
   129
                     left join C_PROJECT on C_INVOICE.C_PROJECT_ID = C_PROJECT.C_PROJECT_ID,
carlos@0
   130
           C_INVOICELINE left join M_PRODUCT on C_INVOICELINE.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID
carlos@0
   131
                         left join C_UOM on C_INVOICELINE.C_UOM_ID = C_UOM.C_UOM_ID
carlos@0
   132
                         left join M_PRODUCT_CATEGORY on M_PRODUCT.M_PRODUCT_CATEGORY_ID = M_PRODUCT_CATEGORY.M_PRODUCT_CATEGORY_ID
carlos@0
   133
                         left join M_INOUTLINE on C_INVOICELINE.M_INOUTLINE_ID = M_INOUTLINE.M_INOUTLINE_ID
carlos@0
   134
                         left join  M_INOUT ON M_INOUTLINE.M_INOUT_ID = M_INOUT.M_INOUT_ID,
carlos@0
   135
           C_BPARTNER    left join C_BPARTNER CB on C_BPARTNER.SALESREP_ID = CB.C_BPARTNER_ID,
carlos@0
   136
           C_BP_GROUP, AD_ORG 
carlos@0
   137
      WHERE C_INVOICE.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
carlos@0
   138
      AND C_BPARTNER.C_BP_GROUP_ID = C_BP_GROUP.C_BP_GROUP_ID
carlos@0
   139
      AND C_INVOICE.C_INVOICE_ID = C_INVOICELINE.C_INVOICE_ID
carlos@0
   140
      AND C_INVOICE.AD_ORG_ID = AD_ORG.AD_ORG_ID
carlos@0
   141
      AND C_INVOICE.ISSOTRX = 'Y'
carlos@0
   142
      AND C_INVOICE.PROCESSED = 'Y'
carlos@0
   143
      AND AD_ORG.AD_ORG_ID IN ('1')
carlos@0
   144
      AND C_INVOICE.AD_CLIENT_ID IN ('7')
carlos@0
   145
      AND 1=1
carlos@0
   146
      ORDER BY NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5, NIVEL6, NIVEL7, NIVEL8, NIVEL9, NIVEL10) AA
carlos@0
   147
      GROUP BY NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5, NIVEL6, NIVEL7, NIVEL8, NIVEL9, NIVEL10, UOMSYMBOL
carlos@0
   148
     ]]></Sql>
carlos@0
   149
    <Parameter name="nivel1" type="replace" optional="true" after="FROM (SELECT to_char(" text="'1'"/>
carlos@0
   150
    <Parameter name="nivel2" type="replace" optional="true" after="AS NIVEL1, to_char(" text="'2'"/>
carlos@0
   151
    <Parameter name="nivel3" type="replace" optional="true" after="AS NIVEL2, to_char(" text="'3'"/>
carlos@0
   152
    <Parameter name="nivel4" type="replace" optional="true" after="AS NIVEL3, to_char(" text="'4'"/>
carlos@0
   153
    <Parameter name="nivel5" type="replace" optional="true" after="AS NIVEL4, to_char(" text="'5'"/>
carlos@0
   154
    <Parameter name="nivel6" type="replace" optional="true" after="AS NIVEL5, to_char(" text="'6'"/>
carlos@0
   155
    <Parameter name="nivel7" type="replace" optional="true" after="AS NIVEL6, to_char(" text="'7'"/>
carlos@0
   156
    <Parameter name="nivel8" type="replace" optional="true" after="AS NIVEL7, to_char(" text="'9'"/>
carlos@0
   157
    <Parameter name="nivel9" type="replace" optional="true" after="AS NIVEL8, to_char(" text="'10'"/>
carlos@0
   158
    <Parameter name="nivel10" type="replace" optional="true" after="AS NIVEL9, to_char(" text="'11'"/>
carlos@0
   159
    <Parameter name="adOrgId" optional="true" type="replace" after="AND AD_ORG.AD_ORG_ID IN (" text="'1'"/>
carlos@0
   160
    <Parameter name="adUserClient" type="replace" optional="true" after="AND C_INVOICE.AD_CLIENT_ID IN (" text="'7'"/>
carlos@0
   161
    <Parameter name="dateFrom" optional="true" after="AND 1=1"><![CDATA[AND C_INVOICE.DATEINVOICED >= to_date(?)]]></Parameter>
carlos@0
   162
    <Parameter name="dateTo" optional="true" after="AND 1=1"><![CDATA[AND C_INVOICE.DATEINVOICED < to_date(?)]]></Parameter>
carlos@423
   163
    <Parameter name="cBpartnerGroupId" optional="true" after="AND 1=1"><![CDATA[AND C_BP_GROUP.C_BP_GROUP_ID = TO_NUMBER(?)]]></Parameter>
carlos@0
   164
    <Parameter name="cBpartnerId" optional="true" type="argument" after="AND 1=1"><![CDATA[AND C_BPARTNER.C_BPARTNER_ID IN]]></Parameter>
carlos@423
   165
    <Parameter name="mProductCategoryId" optional="true" after="AND 1=1"><![CDATA[AND M_PRODUCT_CATEGORY.M_PRODUCT_CATEGORY_ID = TO_NUMBER(?)]]></Parameter>
carlos@0
   166
    <Parameter name="mProductId" optional="true" type="argument" after="AND 1=1"><![CDATA[AND M_PRODUCT.M_PRODUCT_ID IN]]></Parameter>
carlos@423
   167
    <Parameter name="salesrepId" optional="true" after="AND 1=1"><![CDATA[AND C_INVOICE.SALESREP_ID = TO_NUMBER(?)]]></Parameter>
carlos@423
   168
    <Parameter name="partnerSalesrepId" optional="true" after="AND 1=1"><![CDATA[AND CB.C_BPARTNER_ID = TO_NUMBER(?)]]></Parameter>
carlos@423
   169
    <Parameter name="cProjectId" optional="true" after="AND 1=1"><![CDATA[AND C_PROJECT.C_PROJECT_ID = TO_NUMBER(?)]]></Parameter>
carlos@0
   170
    <Parameter name="producttype" optional="true" after="AND 1=1"><![CDATA[AND M_PRODUCT.PRODUCTTYPE = ?]]></Parameter>
carlos@0
   171
    <Parameter name="orderby" type="replace" optional="true" after="GROUP BY NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5, NIVEL6, NIVEL7, NIVEL8, NIVEL9, NIVEL10, UOMSYMBOL" text=", 1"/>
carlos@0
   172
  </SqlMethod>
carlos@0
   173
  <SqlMethod name="selectTotal" type="preparedStatement" return="String" default="0">
carlos@0
   174
    <SqlMethodComment></SqlMethodComment>
carlos@0
   175
    <Sql>
carlos@0
   176
    <![CDATA[
carlos@0
   177
      SELECT COALESCE(SUM(LINENETAMT), 0) AS LINENETAMT
carlos@0
   178
      FROM C_INVOICE left join AD_USER   on C_INVOICE.SALESREP_ID = AD_USER.AD_USER_ID 
carlos@0
   179
                     left join C_PROJECT on C_INVOICE.C_PROJECT_ID = C_PROJECT.C_PROJECT_ID,
carlos@0
   180
           C_INVOICELINE left join M_PRODUCT on C_INVOICELINE.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID
carlos@0
   181
                         left join C_UOM     on C_INVOICELINE.C_UOM_ID = C_UOM.C_UOM_ID
carlos@0
   182
                         left join M_PRODUCT_CATEGORY on M_PRODUCT.M_PRODUCT_CATEGORY_ID = M_PRODUCT_CATEGORY.M_PRODUCT_CATEGORY_ID
carlos@0
   183
                         left join M_INOUTLINE on C_INVOICELINE.M_INOUTLINE_ID = M_INOUTLINE.M_INOUTLINE_ID
carlos@0
   184
                         left join  M_INOUT ON M_INOUTLINE.M_INOUT_ID = M_INOUT.M_INOUT_ID,
carlos@0
   185
           C_BPARTNER, C_BP_GROUP, AD_ORG
carlos@0
   186
      WHERE C_INVOICE.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
carlos@0
   187
      AND C_BPARTNER.C_BP_GROUP_ID = C_BP_GROUP.C_BP_GROUP_ID
carlos@0
   188
      AND C_INVOICE.C_INVOICE_ID = C_INVOICELINE.C_INVOICE_ID
carlos@0
   189
      AND C_INVOICE.AD_ORG_ID = AD_ORG.AD_ORG_ID
carlos@0
   190
      AND C_INVOICE.ISSOTRX = 'Y'
carlos@0
   191
      AND C_INVOICE.PROCESSED = 'Y'
carlos@0
   192
      AND C_INVOICE.AD_ORG_ID IN ('1')
carlos@0
   193
      AND C_INVOICE.AD_CLIENT_ID IN ('7')
carlos@0
   194
      AND 1=1
carlos@0
   195
     ]]></Sql>
carlos@0
   196
    <Parameter name="adOrgId" optional="true" type="replace" after="AND C_INVOICE.AD_ORG_ID IN (" text="'1'"/>
carlos@0
   197
    <Parameter name="adUserClient" type="replace" optional="true" after="AND C_INVOICE.AD_CLIENT_ID IN (" text="'7'"/>
carlos@0
   198
    <Parameter name="dateFrom" optional="true" after="AND 1=1"><![CDATA[AND C_INVOICE.DATEINVOICED >= to_date(?)]]></Parameter>
carlos@0
   199
    <Parameter name="dateTo" optional="true" after="AND 1=1"><![CDATA[AND C_INVOICE.DATEINVOICED < to_date(?)]]></Parameter>
carlos@423
   200
    <Parameter name="cBpartnerGroupId" optional="true" after="AND 1=1"><![CDATA[AND C_BP_GROUP.C_BP_GROUP_ID = TO_NUMBER(?)]]></Parameter>
carlos@0
   201
    <Parameter name="cBpartnerId" optional="true" type="argument" after="AND 1=1"><![CDATA[AND C_BPARTNER.C_BPARTNER_ID IN]]></Parameter>
carlos@423
   202
    <Parameter name="mProductCategoryId" optional="true" after="AND 1=1"><![CDATA[AND M_PRODUCT_CATEGORY.M_PRODUCT_CATEGORY_ID = TO_NUMBER(?)]]></Parameter>
carlos@0
   203
    <Parameter name="mProductId" optional="true" type="argument" after="AND 1=1"><![CDATA[AND M_PRODUCT.M_PRODUCT_ID IN]]></Parameter>
carlos@423
   204
    <Parameter name="salesrepId" optional="true" after="AND 1=1"><![CDATA[AND C_INVOICE.SALESREP_ID = TO_NUMBER(?)]]></Parameter>
carlos@423
   205
    <Parameter name="partnerSalesrepId" optional="true" after="AND 1=1"><![CDATA[AND C_BPARTNER.SALESREP_ID = TO_NUMBER(?)]]></Parameter>
carlos@423
   206
    <Parameter name="cProjectId" optional="true" after="AND 1=1"><![CDATA[AND C_PROJECT.C_PROJECT_ID = TO_NUMBER(?)]]></Parameter>
carlos@0
   207
    <Parameter name="producttype" optional="true" after="AND 1=1"><![CDATA[AND M_PRODUCT.PRODUCTTYPE = ?]]></Parameter>
carlos@0
   208
  </SqlMethod>
carlos@0
   209
  <SqlMethod name="selectBpartner" type="preparedStatement" return="multiple">
carlos@0
   210
    <SqlMethodComment></SqlMethodComment>
carlos@0
   211
    <Sql>
carlos@0
   212
    <![CDATA[
carlos@0
   213
      SELECT C_BPARTNER_ID AS ID, C_BPARTNER.NAME
carlos@0
   214
      FROM C_BPARTNER
carlos@0
   215
      WHERE AD_ORG_ID IN ('1') 
carlos@0
   216
      AND AD_CLIENT_ID IN ('1') 
carlos@0
   217
      AND (1=2 )
carlos@0
   218
     ]]></Sql>
carlos@0
   219
    <Parameter name="adOrgClient" type="replace" optional="true" after="AD_ORG_ID IN (" text="'1'"/>
carlos@0
   220
     <Parameter name="adUserClient" type="replace" optional="true" after="AND AD_CLIENT_ID IN (" text="'1'"/>
carlos@0
   221
     <Parameter name="cBpartnerId" optional="true" type="argument" after="(1=2" text=" OR C_BPARTNER_ID IN"/>
carlos@0
   222
  </SqlMethod>
carlos@0
   223
carlos@0
   224
  <SqlMethod name="selectProject" type="preparedStatement" return="String" default="">
carlos@0
   225
    <SqlMethodComment></SqlMethodComment>
carlos@0
   226
    <Sql>
carlos@0
   227
    <![CDATA[
carlos@0
   228
      SELECT C_PROJECT.NAME
carlos@0
   229
      FROM C_PROJECT
carlos@423
   230
      WHERE C_PROJECT_ID = TO_NUMBER(?)
carlos@0
   231
     ]]></Sql>
carlos@0
   232
     <Parameter name="cProjectId"/>
carlos@0
   233
  </SqlMethod>
carlos@0
   234
carlos@0
   235
  <SqlMethod name="selectMproduct" type="preparedStatement" return="multiple">
carlos@0
   236
    <SqlMethodComment></SqlMethodComment>
carlos@0
   237
    <Sql>
carlos@0
   238
    <![CDATA[
carlos@0
   239
      SELECT M_PRODUCT.M_PRODUCT_ID AS ID,M_PRODUCT.NAME
carlos@0
   240
      FROM M_PRODUCT
carlos@0
   241
      WHERE AD_ORG_ID IN ('1') 
carlos@0
   242
      AND AD_CLIENT_ID IN ('1') 
carlos@0
   243
      AND (1=2 )
carlos@0
   244
     ]]></Sql>
carlos@0
   245
    <Parameter name="adOrgClient" type="replace" optional="true" after="AD_ORG_ID IN (" text="'1'"/>
carlos@0
   246
     <Parameter name="adUserClient" type="replace" optional="true" after="AND AD_CLIENT_ID IN (" text="'1'"/>
carlos@0
   247
     <Parameter name="mProductId" optional="true" type="argument" after="(1=2" text=" OR M_PRODUCT_ID IN"/>
carlos@0
   248
  </SqlMethod>
carlos@0
   249
carlos@0
   250
  <SqlMethod name="selectBpgroup" type="preparedStatement" return="String" default="">
carlos@0
   251
    <SqlMethodComment></SqlMethodComment>
carlos@0
   252
    <Sql>
carlos@0
   253
    <![CDATA[
carlos@0
   254
      SELECT C_BP_GROUP.NAME
carlos@0
   255
      FROM C_BP_GROUP
carlos@423
   256
      WHERE C_BP_GROUP.C_BP_GROUP_ID = TO_NUMBER(?)
carlos@0
   257
     ]]></Sql>
carlos@0
   258
    <Parameter name="cBpGroupId"/>
carlos@0
   259
  </SqlMethod>
carlos@0
   260
carlos@0
   261
  <SqlMethod name="selectProductCategory" type="preparedStatement" return="String" default="">
carlos@0
   262
    <SqlMethodComment></SqlMethodComment>
carlos@0
   263
    <Sql>
carlos@0
   264
    <![CDATA[
carlos@0
   265
      SELECT M_PRODUCT_CATEGORY.NAME
carlos@0
   266
      FROM M_PRODUCT_CATEGORY
carlos@423
   267
      WHERE M_PRODUCT_CATEGORY.M_PRODUCT_CATEGORY_ID = TO_NUMBER(?)
carlos@0
   268
     ]]></Sql>
carlos@0
   269
    <Parameter name="mProductCategoryId"/>
carlos@0
   270
  </SqlMethod>
carlos@0
   271
carlos@0
   272
  <SqlMethod name="selectSalesrep" type="preparedStatement" return="String" default="">
carlos@0
   273
    <SqlMethodComment></SqlMethodComment>
carlos@0
   274
    <Sql>
carlos@0
   275
    <![CDATA[
carlos@0
   276
      SELECT AD_USER.FIRSTNAME||' '||AD_USER.LASTNAME
carlos@0
   277
      FROM AD_USER
carlos@423
   278
      WHERE AD_USER.AD_USER_ID = TO_NUMBER(?)
carlos@0
   279
     ]]></Sql>
carlos@0
   280
    <Parameter name="mWarehouseId"/>
carlos@0
   281
  </SqlMethod>
carlos@0
   282
carlos@0
   283
  <SqlMethod name="selectEntity" type="preparedStatement" return="String" default="">
carlos@0
   284
    <SqlMethodComment></SqlMethodComment>
carlos@0
   285
    <Sql>
carlos@0
   286
    <![CDATA[
carlos@0
   287
      SELECT AD_CLIENT.NAME
carlos@0
   288
      FROM AD_CLIENT
carlos@423
   289
      WHERE AD_CLIENT.AD_CLIENT_ID = TO_NUMBER(?)
carlos@0
   290
     ]]></Sql>
carlos@0
   291
    <Parameter name="adClientId"/>
carlos@0
   292
  </SqlMethod>
carlos@0
   293
carlos@0
   294
  <SqlMethod name="selectProducttype" type="preparedStatement" return="String" default="">
carlos@0
   295
    <SqlMethodComment></SqlMethodComment>
carlos@0
   296
    <Sql>
carlos@0
   297
    <![CDATA[
carlos@0
   298
      SELECT ad_ref_list_trl.NAME FROM ad_ref_list, ad_ref_list_trl 
carlos@0
   299
      WHERE ad_ref_list.AD_REF_LIST_ID = ad_ref_list_trl.AD_REF_LIST_ID
carlos@423
   300
      AND ad_ref_list.ad_reference_ID = TO_NUMBER(?)
carlos@0
   301
      AND ad_ref_list_trl.ad_language = ?
carlos@0
   302
      AND ad_ref_list.VALUE = ?
carlos@0
   303
     ]]></Sql>
carlos@0
   304
    <Parameter name="adReferenceId"/>
carlos@0
   305
    <Parameter name="adLanguage"/>
carlos@0
   306
    <Parameter name="value"/>
carlos@0
   307
  </SqlMethod>
carlos@0
   308
carlos@0
   309
  <SqlMethod name="selectNotShown" type="preparedStatement" return="multiple">
carlos@0
   310
    <SqlMethodComment></SqlMethodComment>
carlos@0
   311
    <Sql>
carlos@0
   312
    <![CDATA[
carlos@0
   313
      SELECT ID, NAME
carlos@573
   314
      FROM (SELECT VALUE AS ID, NAME 
carlos@0
   315
              FROM AD_REF_LIST 
carlos@0
   316
             WHERE AD_REFERENCE_ID = 800087
carlos@0
   317
             AND AD_REF_LIST.VALUE IN ('1', '2', '3', '4', '5', '6', '8', '9', '10', '11')
carlos@573
   318
             ORDER BY TO_NUMBER(VALUE)) AA
carlos@0
   319
      WHERE 1=1 
carlos@0
   320
     ]]></Sql>
carlos@0
   321
     <Parameter name="notShown" optional="true" type="argument" after="WHERE 1=1 "><![CDATA[AND ID NOT IN]]></Parameter>
carlos@0
   322
  </SqlMethod>
carlos@0
   323
  <SqlMethod name="selectShown" type="preparedStatement" return="multiple">
carlos@0
   324
    <SqlMethodComment></SqlMethodComment>
carlos@0
   325
    <Sql>
carlos@0
   326
    <![CDATA[
carlos@0
   327
      SELECT ID, NAME
carlos@573
   328
      FROM (SELECT VALUE AS ID, NAME 
carlos@0
   329
              FROM AD_REF_LIST 
carlos@0
   330
             WHERE AD_REFERENCE_ID = 800087 
carlos@0
   331
             AND AD_REF_LIST.VALUE IN ('1', '2', '3', '4', '5', '6', '8', '9', '10', '11')
carlos@573
   332
             ORDER BY TO_NUMBER(VALUE)) AA
carlos@0
   333
      WHERE 1=1 
carlos@0
   334
     ]]></Sql>
carlos@0
   335
     <Parameter name="shown" optional="true" type="argument" after="WHERE 1=1 "><![CDATA[AND ID IN]]></Parameter>
carlos@0
   336
  </SqlMethod>
carlos@0
   337
carlos@0
   338
  <SqlMethod name="selectNotShownTrl" type="preparedStatement" return="multiple">
carlos@0
   339
    <SqlMethodComment></SqlMethodComment>
carlos@0
   340
    <Sql>
carlos@0
   341
    <![CDATA[
carlos@0
   342
      SELECT ID, NAME
carlos@573
   343
      FROM (SELECT VALUE AS ID, T.NAME 
carlos@0
   344
              FROM AD_REF_LIST_trl T,
carlos@0
   345
                   AD_REF_LIST     L
carlos@0
   346
             WHERE l.AD_REFERENCE_ID = 800087
carlos@0
   347
             AND L.VALUE IN ('1', '2', '3', '4', '5', '6', '8', '9', '10', '11')
carlos@0
   348
               AND l.AD_REF_LIST_ID  = t.AD_REF_LIST_ID
carlos@0
   349
               AND t.AD_LANGUAGE = ?
carlos@573
   350
             ORDER BY TO_NUMBER(VALUE)) AA
carlos@0
   351
      WHERE 1=1 
carlos@0
   352
     ]]></Sql>
carlos@0
   353
     <Parameter name="lang"/> 
carlos@0
   354
     <Parameter name="notShown" optional="true" type="argument" after="WHERE 1=1 "><![CDATA[AND ID NOT IN]]></Parameter>
carlos@0
   355
  </SqlMethod>
carlos@0
   356
  <SqlMethod name="selectShownTrl" type="preparedStatement" return="multiple">
carlos@0
   357
    <SqlMethodComment></SqlMethodComment>
carlos@0
   358
    <Sql>
carlos@0
   359
    <![CDATA[
carlos@0
   360
      SELECT ID, NAME
carlos@573
   361
      FROM (SELECT VALUE AS ID, T.NAME 
carlos@0
   362
              FROM AD_REF_LIST_trl T,
carlos@0
   363
                   AD_REF_LIST     L
carlos@0
   364
             WHERE l.AD_REFERENCE_ID = 800087
carlos@0
   365
               AND l.AD_REF_LIST_ID  = t.AD_REF_LIST_ID
carlos@0
   366
               AND L.VALUE IN ('1', '2', '3', '4', '5', '6', '8', '9', '10', '11')
carlos@0
   367
               AND t.AD_LANGUAGE = ?
carlos@573
   368
             ORDER BY TO_NUMBER(VALUE)) AA
carlos@0
   369
      WHERE 1=1 
carlos@0
   370
     ]]></Sql>
carlos@0
   371
     <Parameter name="lang"/> 
carlos@0
   372
     <Parameter name="shown" optional="true" type="argument" after="WHERE 1=1 "><![CDATA[AND ID IN]]></Parameter>
carlos@0
   373
  </SqlMethod>
carlos@0
   374
carlos@0
   375
</SqlClass>