src/org/openbravo/erpCommon/info/InvoiceLine_data.xsql
author Iván Perdomo <ivan.perdomo@openbravo.com>
Mon, 21 Apr 2008 19:06:47 +0000
changeset 712 aa0c40278c5a
parent 423 ecf368072c48
child 1605 8a0fe0193bef
permissions -rw-r--r--
Merged changes from ajaxgrids branch.
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="InvoiceLineData" package="org.openbravo.erpCommon.info">
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[
ivan@712
    31
      	SELECT * FROM ( SELECT '0' AS RN1, A.* FROM (
ivan@712
    32
	          SELECT IL.C_INVOICELINE_ID, b.NAME AS BPARTNER_NAME, p.NAME AS PRODUCT_NAME, I.DATEINVOICED, I.DOCUMENTNO, 
ivan@712
    33
	          I.ISSOTRX, IL.QTYINVOICED AS QTY, IL.PRICEACTUAL, IL.LINENETAMT, 
ivan@712
    34
	          (I.DOCUMENTNO || ' - ' || I.DATEINVOICED || ' - ' || I.GRANDTOTAL || ' - ' || IL.LINE || ' - ' || IL.LINENETAMT) AS LINE_TEXT,
ivan@712
    35
	          IL.C_INVOICELINE_ID || '#' || (I.DOCUMENTNO || ' - ' || I.DATEINVOICED || ' - ' || I.GRANDTOTAL || ' - ' || IL.LINE || ' - ' || IL.LINENETAMT) AS ROWKEY
ivan@712
    36
	          FROM C_INVOICE I, C_INVOICELINE IL, M_PRODUCT p, C_BPARTNER b
ivan@712
    37
	          WHERE I.C_INVOICE_ID = IL.C_INVOICE_ID 
ivan@712
    38
	          AND I.C_BPARTNER_ID = b.C_BPARTNER_ID 
ivan@712
    39
	          AND IL.M_PRODUCT_ID = p.M_PRODUCT_ID
ivan@712
    40
	          AND I.AD_CLIENT_ID IN ('1') 
ivan@712
    41
	          AND I.AD_ORG_ID IN ('1') 
ivan@712
    42
	          AND I.IsActive='Y' 
ivan@712
    43
	          AND I.ISSOTRX = 'Y'
ivan@712
    44
	          ORDER BY b.NAME
ivan@712
    45
          	) A ) B
ivan@712
    46
        WHERE 1=1
carlos@0
    47
        ]]>
carlos@0
    48
        </Sql>
carlos@0
    49
        <Field name="position" value="count"/>
ivan@712
    50
        <Parameter name="rownum" type="replace" optional="true" after="FROM ( SELECT " text="'0'" />
carlos@0
    51
        <Parameter name="adUserClient" type="replace" optional="true" after="I.AD_CLIENT_ID IN (" text="'1'"/>
carlos@0
    52
        <Parameter name="adUserOrg" type="replace" optional="true" after="I.AD_ORG_ID IN (" text="'1'"/>
carlos@0
    53
        <Parameter name="documentno" optional="true" after="AND I.IsActive='Y'"><![CDATA[ AND UPPER(I.DocumentNo) LIKE UPPER(?) ]]></Parameter>
carlos@0
    54
        <Parameter name="description" optional="true" after="AND I.IsActive='Y'"><![CDATA[ AND UPPER(I.Description) LIKE UPPER(?) ]]></Parameter>
carlos@0
    55
        <Parameter name="orderReference" optional="true" after="AND I.IsActive='Y'"><![CDATA[ AND UPPER(I.POReference) LIKE UPPER(?) ]]></Parameter>
carlos@423
    56
        <Parameter name="businesPartner" optional="true" after="AND I.IsActive='Y'"><![CDATA[ AND I.C_BPartner_ID = TO_NUMBER(?) ]]></Parameter>
ivan@712
    57
        <Parameter name="dateFrom" optional="true" after="AND I.IsActive='Y'"><![CDATA[ AND I.DATEINVOICED >= TO_DATE(?) ]]></Parameter>
ivan@712
    58
        <Parameter name="dateTo" optional="true" after="AND I.IsActive='Y'"><![CDATA[ AND I.DATEINVOICED < TO_DATE(?) ]]></Parameter>
ivan@712
    59
        <Parameter name="grandTotalFrom" optional="true" after="AND I.IsActive='Y'"><![CDATA[ AND I.GrandTotal >= TO_NUMBER(?) ]]></Parameter>
ivan@712
    60
        <Parameter name="grandTotalTo" optional="true" after="AND I.IsActive='Y'"><![CDATA[ AND I.GrandTotal <= TO_NUMBER(?) ]]></Parameter>
carlos@423
    61
        <Parameter name="product" optional="true" after="AND I.IsActive='Y'"><![CDATA[ AND IL.M_Product_ID = TO_NUMBER(?) ]]></Parameter>
ivan@712
    62
        <Parameter name="orderBy" type="replace" optional="true" after="ORDER BY " text="b.NAME" />
ivan@712
    63
        <Parameter name="oraLimit" type="argument" optional="true" after="WHERE 1=1"><![CDATA[AND RN1 BETWEEN ]]></Parameter>
ivan@712
    64
        <Parameter name="pgLimit" type="argument" optional="true" after="WHERE 1=1"><![CDATA[LIMIT ]]></Parameter>
carlos@0
    65
      </SqlMethod>
carlos@0
    66
   <SqlMethod name="set" type="constant" return="multiple">
carlos@0
    67
      <SqlMethodComment></SqlMethodComment>
carlos@0
    68
      <Sql></Sql>
carlos@0
    69
   </SqlMethod>
carlos@0
    70
   <!--SqlMethod name="selectSOTrx" type="preparedStatement" return="multiple">
carlos@0
    71
      <SqlMethodComment></SqlMethodComment>
carlos@0
    72
      <Sql>
carlos@0
    73
      <![CDATA[
carlos@0
    74
          SELECT ol.C_ORDERLINE_ID, b.NAME AS BPARTNER_NAME, p.NAME AS PRODUCT_NAME, o.DATEORDERED, o.DOCUMENTNO, 
carlos@0
    75
          o.ISSOTRX, ol.QTYORDERED AS QTY, ol.PRICEACTUAL, ol.LINENETAMT, 
carlos@0
    76
          (o.DOCUMENTNO || ' - ' || o.DATEORDERED || ' - ' || o.GRANDTOTAL || ' - ' || ol.LINE || ' - ' || ol.LINENETAMT) AS LINE_TEXT
carlos@0
    77
          FROM C_ORDERLINE ol left join M_MATCHPO mp on ol.C_ORDERLINE_ID = mp.C_ORDERLINE_ID 
carlos@0
    78
                                                    AND mp.C_INVOICELINE_ID IS NOT NULL
carlos@0
    79
                              left join M_MATCHPO mp2 on ol.C_ORDERLINE_ID = mp2.C_ORDERLINE_ID
carlos@0
    80
                                                    AND mp2.M_INOUTLINE_ID IS NOT NULL,
carlos@0
    81
                C_ORDER o,  M_PRODUCT p, C_BPARTNER b
carlos@0
    82
          WHERE o.C_ORDER_ID = ol.C_ORDER_ID 
carlos@0
    83
          AND o.C_BPARTNER_ID = b.C_BPARTNER_ID 
carlos@0
    84
          AND ol.M_PRODUCT_ID = p.M_PRODUCT_ID 
carlos@0
    85
          AND o.AD_CLIENT_ID IN ('1') 
carlos@0
    86
          AND o.AD_ORG_ID IN ('1') 
carlos@0
    87
          AND o.IsActive='Y' 
carlos@0
    88
          AND o.ISSOTRX = 'N'
carlos@0
    89
          GROUP BY ol.C_ORDERLINE_ID, b.NAME, p.NAME, o.DATEORDERED, o.DOCUMENTNO, 
carlos@0
    90
          o.ISSOTRX, ol.QTYORDERED, ol.PRICEACTUAL, ol.LINENETAMT, o.GRANDTOTAL, ol.LINE, ol.LINENETAMT
carlos@0
    91
          HAVING 1=1 
carlos@0
    92
          ORDER BY b.NAME, o.DATEORDERED, o.DOCUMENTNO, ol.LINE
carlos@0
    93
        ]]>
carlos@0
    94
        </Sql>
carlos@0
    95
        <Field name="position" value="count"/>
carlos@0
    96
        <Parameter name="adUserClient" type="replace" optional="true" after="o.AD_CLIENT_ID IN (" text="'1'"/>
carlos@0
    97
        <Parameter name="adUserOrg" type="replace" optional="true" after="o.AD_ORG_ID IN (" text="'1'"/>
carlos@0
    98
        <Parameter name="documentno" optional="true" after="AND o.IsActive='Y'"><![CDATA[ AND UPPER(o.DocumentNo) LIKE UPPER(?) ]]></Parameter>
carlos@0
    99
        <Parameter name="description" optional="true" after="AND o.IsActive='Y'"><![CDATA[ AND UPPER(o.Description) LIKE UPPER(?) ]]></Parameter>
carlos@0
   100
        <Parameter name="orderReference" optional="true" after="AND o.IsActive='Y'"><![CDATA[ AND UPPER(o.POReference) LIKE UPPER(?) ]]></Parameter>
carlos@423
   101
        <Parameter name="businesPartner" optional="true" after="AND o.IsActive='Y'"><![CDATA[ AND o.C_BPartner_ID = TO_NUMBER(?) ]]></Parameter>
carlos@0
   102
        <Parameter name="dateFrom" optional="true" after="AND o.IsActive='Y'"><![CDATA[ AND o.DateOrdered >= to_date(?) ]]></Parameter>
carlos@0
   103
        <Parameter name="dateTo" optional="true" after="AND o.IsActive='Y'"><![CDATA[ AND o.DateOrdered < to_date(?) ]]></Parameter>
carlos@0
   104
        <Parameter name="grandTotalFrom" optional="true" after="AND o.IsActive='Y'"><![CDATA[ AND o.GrandTotal >= ? ]]></Parameter>
carlos@0
   105
        <Parameter name="grandTotalTo" optional="true" after="AND o.IsActive='Y'"><![CDATA[ AND o.GrandTotal <= ? ]]></Parameter>
carlos@423
   106
        <Parameter name="product" optional="true" after="AND o.IsActive='Y'"><![CDATA[ AND ol.M_Product_ID = TO_NUMBER(?) ]]></Parameter>
carlos@0
   107
        <Parameter name="isdelivered" optional="true" type="none" after="HAVING 1=1 "><![CDATA[ AND (ol.QTYORDERED - COALESCE(SUM(mp2.QTY), 0)) <> 0 ]]></Parameter>
carlos@0
   108
        <Parameter name="isinvoiced" optional="true" type="none" after="HAVING 1=1 "><![CDATA[ AND (ol.QTYORDERED - COALESCE(SUM(mp.QTY),0)) <> 0 ]]></Parameter>
carlos@0
   109
   </SqlMethod-->
carlos@0
   110
   <SqlMethod name="selectKey" type="preparedStatement" return="multiple">
carlos@0
   111
      <SqlMethodComment></SqlMethodComment>
carlos@0
   112
      <Sql> 
carlos@0
   113
      <![CDATA[
carlos@0
   114
        SELECT IL.C_INVOICELINE_ID, b.NAME AS BPARTNER_NAME, p.NAME AS PRODUCT_NAME, I.DATEINVOICED, I.DOCUMENTNO, 
carlos@0
   115
        I.ISSOTRX, IL.QTYINVOICED AS QTY, IL.PRICEACTUAL, IL.LINENETAMT, 
carlos@0
   116
        (I.DOCUMENTNO || ' - ' || I.DATEINVOICED || ' - ' || I.GRANDTOTAL || ' - ' || IL.LINE || ' - ' || IL.LINENETAMT) AS LINE_TEXT
carlos@0
   117
        FROM C_INVOICE I, C_INVOICELINE Il, M_PRODUCT p, C_BPARTNER b
carlos@0
   118
        WHERE I.C_INVOICE_ID = IL.C_INVOICE_ID 
carlos@0
   119
        AND I.C_BPARTNER_ID = b.C_BPARTNER_ID 
carlos@0
   120
        AND IL.M_PRODUCT_ID = p.M_PRODUCT_ID
carlos@0
   121
        AND I.AD_CLIENT_ID IN ('1') 
carlos@0
   122
        AND I.AD_ORG_ID IN ('1') 
carlos@0
   123
        AND I.IsActive='Y' 
carlos@0
   124
        AND UPPER(I.DOCUMENTNO) LIKE UPPER(?) 
carlos@0
   125
       /* AND (IL.QTYORDERED - COALESCE(IL.QTYINVOICED, 0)) > 0 
carlos@0
   126
        AND (IL.QTYORDERED - COALESCE(IL.QTYDELIVERED, 0)) > 0 */
carlos@0
   127
        AND I.ISSOTRX = 'Y'
carlos@0
   128
        ORDER BY b.NAME, I.DATEINVOICED, I.DOCUMENTNO, IL.LINE
carlos@0
   129
      ]]>
carlos@0
   130
      </Sql>
carlos@0
   131
        <Parameter name="adUserClient" type="replace" optional="true" after="I.AD_Client_ID IN (" text="'1'"/>
carlos@0
   132
        <Parameter name="adUserOrg" type="replace" optional="true" after="I.AD_Org_ID IN (" text="'1'"/>
carlos@0
   133
        <Parameter name="key"/>
carlos@0
   134
   </SqlMethod>
carlos@0
   135
   <!--SqlMethod name="selectKeySOTrx" type="preparedStatement" return="multiple">
carlos@0
   136
      <SqlMethodComment></SqlMethodComment>
carlos@0
   137
      <Sql> 
carlos@0
   138
      <![CDATA[
carlos@0
   139
        SELECT ol.C_ORDERLINE_ID, b.NAME AS BPARTNER_NAME, p.NAME AS PRODUCT_NAME, o.DATEORDERED, o.DOCUMENTNO, 
carlos@0
   140
        o.ISSOTRX, ol.QTYORDERED AS QTY, ol.PRICEACTUAL, ol.LINENETAMT, 
carlos@0
   141
        (o.DOCUMENTNO || ' - ' || o.DATEORDERED || ' - ' || o.GRANDTOTAL || ' - ' || ol.LINE || ' - ' || ol.LINENETAMT) AS LINE_TEXT
carlos@0
   142
        FROM  C_ORDERLINE ol left join M_MATCHPO mp on ol.C_ORDERLINE_ID = mp.C_ORDERLINE_ID 
carlos@0
   143
                                                    AND mp.C_INVOICELINE_ID  IS NOT NULL
carlos@0
   144
                             left join M_MATCHPO mp2 on  ol.C_ORDERLINE_ID = mp2.C_ORDERLINE_ID 
carlos@0
   145
                                                     and mp2.M_INOUTLINE_ID IS NOT NULL,
carlos@0
   146
              C_ORDER o, M_PRODUCT p, C_BPARTNER b 
carlos@0
   147
        WHERE o.C_ORDER_ID = ol.C_ORDER_ID 
carlos@0
   148
        AND o.C_BPARTNER_ID = b.C_BPARTNER_ID 
carlos@0
   149
        AND ol.M_PRODUCT_ID = p.M_PRODUCT_ID 
carlos@0
   150
        AND o.AD_CLIENT_ID IN ('1') 
carlos@0
   151
        AND o.AD_ORG_ID IN ('1') 
carlos@0
   152
        AND o.IsActive='Y' 
carlos@0
   153
        AND UPPER(o.DOCUMENTNO) LIKE UPPER(?) 
carlos@0
   154
        AND o.ISSOTRX = 'N'
carlos@0
   155
        GROUP BY ol.C_ORDERLINE_ID, b.NAME, p.NAME, o.DATEORDERED, o.DOCUMENTNO, 
carlos@0
   156
        o.ISSOTRX, ol.QTYORDERED, ol.PRICEACTUAL, ol.LINENETAMT, o.GRANDTOTAL, ol.LINE, ol.LINENETAMT
carlos@0
   157
        HAVING (ol.QTYORDERED - COALESCE(SUM(mp.QTY),0)) > 0 
carlos@0
   158
        AND (ol.QTYORDERED - COALESCE(SUM(mp2.QTY), 0)) > 0
carlos@0
   159
        ORDER BY b.NAME, o.DATEORDERED, o.DOCUMENTNO, ol.LINE
carlos@0
   160
      ]]>
carlos@0
   161
      </Sql>
carlos@0
   162
        <Parameter name="adUserClient" type="replace" optional="true" after="o.AD_Client_ID IN (" text="'1'"/>
carlos@0
   163
        <Parameter name="adUserOrg" type="replace" optional="true" after="o.AD_Org_ID IN (" text="'1'"/>
carlos@0
   164
        <Parameter name="key"/>
carlos@0
   165
   </SqlMethod-->
carlos@0
   166
   <SqlMethod name="selectBPartner" type="preparedStatement" return="String" default="">
carlos@0
   167
      <SqlMethodComment></SqlMethodComment>
carlos@0
   168
      <Sql> 
carlos@0
   169
      <![CDATA[
carlos@423
   170
        SELECT NAME FROM C_BPARTNER WHERE C_BPARTNER_ID = TO_NUMBER(?)
carlos@0
   171
      ]]>
carlos@0
   172
      </Sql>
carlos@0
   173
        <Parameter name="cBpartnerId"/>
carlos@0
   174
   </SqlMethod>
carlos@0
   175
   <SqlMethod name="selectProduct" type="preparedStatement" return="String" default="">
carlos@0
   176
      <SqlMethodComment></SqlMethodComment>
carlos@0
   177
      <Sql> 
carlos@0
   178
      <![CDATA[
carlos@423
   179
        SELECT NAME FROM M_PRODUCT WHERE M_PRODUCT_ID = TO_NUMBER(?)
carlos@0
   180
      ]]>
carlos@0
   181
      </Sql>
carlos@0
   182
        <Parameter name="mProductId"/>
carlos@0
   183
   </SqlMethod>
carlos@0
   184
</SqlClass>