src/org/openbravo/erpCommon/info/ProductComplete_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 871 b9084eea924e
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 
ivan@712
    15
 * All portions are Copyright (C) 2001-2008 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="ProductCompleteData" 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> <![CDATA[
ivan@712
    30
      SELECT * FROM ( SELECT '0' AS RN1, A.* FROM (      				   
carlos@0
    31
        SELECT T.M_PRODUCT_ID, P.DISCONTINUED, P.VALUE, P.NAME, P.NAME AS NAME_HIDDEN, 
carlos@0
    32
        T.QtyOnHand AS QTY, U1.NAME AS C_UOM1, COALESCE(T.QtyOrderOnHand, 0) AS QTYORDER, U2.NAME AS C_UOM2, 
carlos@0
    33
        A.DESCRIPTION AS ATTRIBUTE,
carlos@0
    34
        (W.NAME || ' - ' || L.VALUE || '-' || L.X || '-' || L.Y || '-' || L.Z) AS LOCATOR,  
carlos@0
    35
        T.M_ATTRIBUTESETINSTANCE_ID, T.M_LOCATOR_ID, 
carlos@0
    36
        T.C_UOM_ID AS C_UOM1_ID, T.M_PRODUCT_UOM_ID AS C_UOM2_ID, COALESCE(T.PREQTYONHAND, 0) AS QTY_REF, 
carlos@0
    37
        COALESCE(T.PREQTYORDERONHAND, 0) AS QUANTITYORDER_REF, 
ivan@712
    38
        (CASE COALESCE(T.PREQTYONHAND, 0) WHEN 0 THEN (CASE COALESCE(T.PREQTYORDERONHAND, 0) WHEN 0 THEN 0 ELSE -1 END) ELSE -1 END) AS SERVIDO_BINARY,
ivan@712
    39
        T.M_PRODUCT_ID || '#' || p.name || '#' || T.M_LOCATOR_ID || '#' || T.M_ATTRIBUTESETINSTANCE_ID || '#' || 
ivan@712
    40
          COALESCE(T.QtyOrderOnHand, 0) || '#' || T.M_PRODUCT_UOM_ID || '#' || T.QtyOnHand || '#' || T.C_UOM_ID as rowkey
carlos@0
    41
        FROM M_PRODUCT_UOM PU right join M_STORAGE_DETAIL T on T.M_PRODUCT_UOM_ID = PU.M_PRODUCT_UOM_ID
carlos@0
    42
                              left join C_UOM U2 on PU.C_UOM_ID = U2.C_UOM_ID
carlos@0
    43
                              left join  M_ATTRIBUTESETINSTANCE A on T.M_ATTRIBUTESETINSTANCE_ID = A.M_ATTRIBUTESETINSTANCE_ID,
carlos@0
    44
             M_PRODUCT P, C_UOM U1,
carlos@0
    45
        M_LOCATOR L, M_Warehouse W, AD_ORG O, AD_ROLE_ORGACCESS RO
carlos@0
    46
        WHERE T.M_PRODUCT_ID = P.M_PRODUCT_ID 
carlos@0
    47
        AND W.M_WAREHOUSE_ID = L.M_WAREHOUSE_ID 
carlos@0
    48
        AND T.M_LOCATOR_ID = L.M_LOCATOR_ID 
carlos@0
    49
        AND T.C_UOM_ID = U1.C_UOM_ID 
carlos@0
    50
        AND W.AD_ORG_ID = O.AD_ORG_ID 
carlos@0
    51
        AND O.AD_ORG_ID = RO.AD_ORG_ID 
carlos@0
    52
        AND W.ISACTIVE = 'Y' 
carlos@0
    53
        AND P.ISACTIVE = 'Y'
carlos@423
    54
        AND RO.AD_ROLE_ID = TO_NUMBER(?) 
carlos@0
    55
        AND (P.C_BPARTNER_ID IS NULL OR C_BPARTNER_ID = COALESCE(to_number(?), C_BPARTNER_ID)) 
carlos@0
    56
        AND W.AD_CLIENT_ID IN ('1') 
carlos@0
    57
        AND (T.QtyOnHand <> 0 OR COALESCE(T.QtyOrderOnHand, 0) <> 0) 
ivan@712
    58
        ORDER BY P.VALUE
ivan@712
    59
			) A ) B
ivan@712
    60
		WHERE 1=1
carlos@0
    61
      ]]></Sql>
carlos@0
    62
        <Field name="position" value="count"/>
ivan@712
    63
        <Parameter name="rownum" type="replace" optional="true" after="FROM ( SELECT " text="'0'" />
carlos@0
    64
        <Parameter name="key" optional="true" after="WHERE T.M_PRODUCT_ID = P.M_PRODUCT_ID "><![CDATA[ AND UPPER(P.Value) LIKE UPPER(?) ]]></Parameter>
carlos@0
    65
        <Parameter name="name" optional="true" after="WHERE T.M_PRODUCT_ID = P.M_PRODUCT_ID "><![CDATA[ AND UPPER(P.Name) LIKE UPPER(?) ]]></Parameter>
carlos@423
    66
        <Parameter name="warehouse" optional="true" after="WHERE T.M_PRODUCT_ID = P.M_PRODUCT_ID "><![CDATA[ AND W.M_Warehouse_ID = TO_NUMBER(?) ]]></Parameter>
carlos@0
    67
        <Parameter name="adRoleId"/>
carlos@0
    68
        <Parameter name="cBpartnerId"/>
carlos@0
    69
        <Parameter name="adUserClient" type="replace" optional="true" after="W.AD_CLIENT_ID IN (" text="'1'"/>
ivan@712
    70
        <Parameter name="orderBy" type="replace" optional="true" after="ORDER BY " text="P.VALUE" />
ivan@712
    71
        <Parameter name="oraLimit" type="argument" optional="true" after="WHERE 1=1"><![CDATA[AND RN1 BETWEEN ]]></Parameter>
ivan@712
    72
        <Parameter name="pgLimit" type="argument" optional="true" after="WHERE 1=1"><![CDATA[LIMIT ]]></Parameter>
carlos@0
    73
   </SqlMethod>
carlos@0
    74
   <SqlMethod name="set" type="constant" return="multiple">
carlos@0
    75
      <SqlMethodComment></SqlMethodComment>
carlos@0
    76
      <Sql></Sql>
carlos@0
    77
   </SqlMethod>
carlos@0
    78
   <SqlMethod name="selecttrl" type="preparedStatement" return="multiple">
carlos@0
    79
      <SqlMethodComment></SqlMethodComment>
carlos@0
    80
      <Sql> <![CDATA[
ivan@712
    81
      SELECT * FROM ( SELECT '0' AS RN1, A.* FROM ( 
carlos@0
    82
        SELECT T.M_PRODUCT_ID, P.DISCONTINUED, P.VALUE, P.NAME, P.NAME AS NAME_HIDDEN, 
carlos@0
    83
        T.QtyOnHand AS QTY, U1TRL.NAME AS C_UOM1, COALESCE(T.QtyOrderOnHand, 0) AS QTYORDER, U2.NAME AS C_UOM2, 
carlos@0
    84
        A.DESCRIPTION AS ATTRIBUTE,
carlos@0
    85
        (W.NAME || ' - ' || L.VALUE || '-' || L.X || '-' || L.Y || '-' || L.Z) AS LOCATOR,  
carlos@0
    86
        T.M_ATTRIBUTESETINSTANCE_ID, T.M_LOCATOR_ID, 
carlos@0
    87
        T.C_UOM_ID AS C_UOM1_ID, T.M_PRODUCT_UOM_ID AS C_UOM2_ID, COALESCE(T.PREQTYONHAND, 0) AS QTY_REF, 
carlos@0
    88
        COALESCE(T.PREQTYORDERONHAND, 0) AS QUANTITYORDER_REF, 
ivan@712
    89
        (CASE COALESCE(T.PREQTYONHAND, 0) WHEN 0 THEN (CASE COALESCE(T.PREQTYORDERONHAND, 0) WHEN 0 THEN 0 ELSE -1 END) ELSE -1 END) AS SERVIDO_BINARY,
ivan@712
    90
        T.M_PRODUCT_ID || '#' || p.name || '#' || T.M_LOCATOR_ID || '#' || T.M_ATTRIBUTESETINSTANCE_ID || '#' || 
ivan@712
    91
          COALESCE(T.QtyOrderOnHand, 0) || '#' || T.M_PRODUCT_UOM_ID || '#' || T.QtyOnHand || '#' || T.C_UOM_ID as rowkey
carlos@0
    92
        FROM C_UOM U1 left join C_UOM_TRL U1TRL on U1.C_UOM_ID=U1TRL.C_UOM_ID
carlos@0
    93
                                                and U1TRL.AD_LANGUAGE= ?,
carlos@0
    94
             M_PRODUCT_UOM PU right join M_STORAGE_DETAIL T on T.M_PRODUCT_UOM_ID = PU.M_PRODUCT_UOM_ID
carlos@0
    95
                              left join C_UOM U2 on PU.C_UOM_ID = U2.C_UOM_ID
carlos@0
    96
                              left join M_ATTRIBUTESETINSTANCE A on T.M_ATTRIBUTESETINSTANCE_ID = A.M_ATTRIBUTESETINSTANCE_ID,
carlos@0
    97
             M_PRODUCT P,
carlos@0
    98
             M_LOCATOR L, M_Warehouse W, AD_ORG O, AD_ROLE_ORGACCESS RO
carlos@0
    99
        WHERE T.M_PRODUCT_ID = P.M_PRODUCT_ID 
carlos@0
   100
        AND W.M_WAREHOUSE_ID = L.M_WAREHOUSE_ID 
carlos@0
   101
        AND T.M_LOCATOR_ID = L.M_LOCATOR_ID 
carlos@0
   102
        AND T.C_UOM_ID = U1.C_UOM_ID 
carlos@0
   103
        AND W.AD_ORG_ID = O.AD_ORG_ID 
carlos@0
   104
        AND O.AD_ORG_ID = RO.AD_ORG_ID 
carlos@0
   105
        AND W.ISACTIVE = 'Y' 
carlos@0
   106
        AND P.ISACTIVE = 'Y'
carlos@423
   107
        AND RO.AD_ROLE_ID = TO_NUMBER(?) 
carlos@0
   108
        AND (P.C_BPARTNER_ID IS NULL OR C_BPARTNER_ID = COALESCE(to_number(?), C_BPARTNER_ID)) 
carlos@0
   109
        AND W.AD_CLIENT_ID IN ('1') 
carlos@0
   110
        AND (T.QtyOnHand <> 0 OR COALESCE(T.QtyOrderOnHand, 0) <> 0) 
ivan@712
   111
        ORDER BY P.VALUE
ivan@712
   112
        ) A ) B
ivan@712
   113
      WHERE 1=1        
carlos@0
   114
      ]]></Sql>
carlos@0
   115
        <Field name="position" value="count"/>
ivan@712
   116
        <Parameter name="rownum" type="replace" optional="true" after="FROM ( SELECT " text="'0'" />
carlos@0
   117
        <Parameter name="adLanguage"/>
carlos@0
   118
        <Parameter name="key" optional="true" after="WHERE T.M_PRODUCT_ID = P.M_PRODUCT_ID "><![CDATA[ AND UPPER(P.Value) LIKE UPPER(?) ]]></Parameter>
carlos@0
   119
        <Parameter name="name" optional="true" after="WHERE T.M_PRODUCT_ID = P.M_PRODUCT_ID "><![CDATA[ AND UPPER(P.Name) LIKE UPPER(?) ]]></Parameter>
carlos@423
   120
        <Parameter name="warehouse" optional="true" after="WHERE T.M_PRODUCT_ID = P.M_PRODUCT_ID "><![CDATA[ AND W.M_Warehouse_ID = TO_NUMBER(?) ]]></Parameter>
carlos@0
   121
        <Parameter name="adRoleId"/>
carlos@0
   122
        <Parameter name="cBpartnerId"/>
ivan@712
   123
        <Parameter name="adUserClient" type="replace" optional="true" after="W.AD_CLIENT_ID IN (" text="'1'"/>  
ivan@712
   124
        <Parameter name="orderBy" type="replace" optional="true" after="ORDER BY " text="P.VALUE" />
ivan@712
   125
        <Parameter name="oraLimit" type="argument" optional="true" after="WHERE 1=1"><![CDATA[AND RN1 BETWEEN ]]></Parameter>
ivan@712
   126
        <Parameter name="pgLimit" type="argument" optional="true" after="WHERE 1=1"><![CDATA[LIMIT ]]></Parameter>    
carlos@0
   127
   </SqlMethod>
carlos@0
   128
carlos@0
   129
   <SqlMethod name="selectNotStored" type="preparedStatement" return="multiple">
carlos@0
   130
      <SqlMethodComment></SqlMethodComment>
carlos@0
   131
      <Sql> <![CDATA[
ivan@712
   132
      SELECT * FROM ( SELECT '0' AS RN1, A.* FROM ( 
carlos@0
   133
        SELECT P.M_PRODUCT_ID, P.DISCONTINUED, P.VALUE, P.NAME, P.NAME AS NAME_HIDDEN, 0 AS QTY, U1.NAME AS C_UOM1, 
carlos@0
   134
        0 AS QTYORDER, NULL AS C_UOM2, A.DESCRIPTION AS ATTRIBUTE, 
carlos@0
   135
        NULL AS LOCATOR, 
carlos@0
   136
        P.M_ATTRIBUTESETINSTANCE_ID, NULL AS M_LOCATOR_ID, 
ivan@712
   137
        P.C_UOM_ID AS C_UOM1_ID, NULL AS C_UOM2_ID, 0 AS QTY_REF, 0 AS QUANTITYORDER_REF, 0 AS SERVIDO_BINARY,
ivan@712
   138
        P.M_PRODUCT_ID || '#' || p.name || '#' || NULL || '#' || P.M_ATTRIBUTESETINSTANCE_ID || '#' || 
ivan@712
   139
          0 || '#' || NULL || '#' || 0 || '#' || P.C_UOM_ID as rowkey
carlos@0
   140
        FROM M_PRODUCT P left join M_ATTRIBUTESETINSTANCE A  on P.M_ATTRIBUTESETINSTANCE_ID = A.M_ATTRIBUTESETINSTANCE_ID, 
carlos@0
   141
             C_UOM U1
carlos@0
   142
        WHERE P.C_UOM_ID = U1.C_UOM_ID 
carlos@0
   143
        AND (P.C_BPARTNER_ID IS NULL OR C_BPARTNER_ID = COALESCE(to_number(?), C_BPARTNER_ID)) 
carlos@0
   144
        AND P.AD_CLIENT_ID IN ('1') 
carlos@0
   145
        AND P.AD_ORG_ID IN ('1') 
carlos@0
   146
        AND P.ISACTIVE = 'Y'
ivan@712
   147
        ORDER BY P.VALUE
ivan@712
   148
        ) A ) B
ivan@712
   149
      WHERE 1=1   
carlos@0
   150
      ]]></Sql>
carlos@0
   151
        <Field name="position" value="count"/>
ivan@712
   152
        <Parameter name="rownum" type="replace" optional="true" after="FROM ( SELECT " text="'0'" />
carlos@0
   153
        <Parameter name="key" optional="true" after="WHERE "><![CDATA[ UPPER(P.Value) LIKE UPPER(?) AND ]]></Parameter>
carlos@0
   154
        <Parameter name="name" optional="true" after="WHERE "><![CDATA[ UPPER(P.Name) LIKE UPPER(?) AND ]]></Parameter>
carlos@0
   155
        <Parameter name="cBpartnerId"/>
carlos@0
   156
        <Parameter name="adUserClient" type="replace" optional="true" after="P.AD_CLIENT_ID IN (" text="'1'"/>
carlos@0
   157
        <Parameter name="adUserOrg" type="replace" optional="true" after="P.AD_ORG_ID IN (" text="'1'"/>
ivan@712
   158
        <Parameter name="orderBy" type="replace" optional="true" after="ORDER BY " text="P.VALUE" />
ivan@712
   159
        <Parameter name="oraLimit" type="argument" optional="true" after="WHERE 1=1"><![CDATA[AND RN1 BETWEEN ]]></Parameter>
ivan@712
   160
        <Parameter name="pgLimit" type="argument" optional="true" after="WHERE 1=1"><![CDATA[LIMIT ]]></Parameter>
carlos@0
   161
   </SqlMethod>
carlos@0
   162
   <SqlMethod name="selectNotStoredtrl" type="preparedStatement" return="multiple">
carlos@0
   163
      <SqlMethodComment></SqlMethodComment>
carlos@0
   164
      <Sql> <![CDATA[
ivan@712
   165
      SELECT * FROM ( SELECT '0' AS RN1, A.* FROM ( 
carlos@0
   166
        SELECT P.M_PRODUCT_ID, P.DISCONTINUED, P.VALUE, P.NAME, P.NAME AS NAME_HIDDEN, 0 AS QTY, U1TRL.NAME AS C_UOM1, 
carlos@0
   167
        0 AS QTYORDER, NULL AS C_UOM2, A.DESCRIPTION AS ATTRIBUTE, 
carlos@0
   168
        NULL AS LOCATOR, 
carlos@0
   169
        P.M_ATTRIBUTESETINSTANCE_ID, NULL AS M_LOCATOR_ID, 
ivan@712
   170
        P.C_UOM_ID AS C_UOM1_ID, NULL AS C_UOM2_ID, 0 AS QTY_REF, 0 AS QUANTITYORDER_REF, 0 AS SERVIDO_BINARY,
ivan@712
   171
        P.M_PRODUCT_ID || '#' || p.name || '#' || NULL || '#' || P.M_ATTRIBUTESETINSTANCE_ID || '#' || 
ivan@712
   172
          0 || '#' || NULL || '#' || 0 || '#' || P.C_UOM_ID as rowkey
carlos@0
   173
        FROM M_PRODUCT P left join M_ATTRIBUTESETINSTANCE A on P.M_ATTRIBUTESETINSTANCE_ID = A.M_ATTRIBUTESETINSTANCE_ID,
carlos@0
   174
             C_UOM U1    left join C_UOM_TRL U1TRL on U1TRL.C_UOM_ID= U1.C_UOM_ID 
carlos@0
   175
                                                  AND U1TRL.AD_LANGUAGE= ?
carlos@0
   176
        WHERE P.C_UOM_ID = U1.C_UOM_ID 
carlos@0
   177
        AND (P.C_BPARTNER_ID IS NULL OR C_BPARTNER_ID = COALESCE(to_number(?), C_BPARTNER_ID)) 
carlos@0
   178
        AND P.AD_CLIENT_ID IN ('1') 
carlos@0
   179
        AND P.AD_ORG_ID IN ('1') 
carlos@0
   180
        AND P.ISACTIVE = 'Y'
ivan@712
   181
        ORDER BY P.VALUE
ivan@712
   182
        ) A ) B
ivan@712
   183
      WHERE 1=1 
carlos@0
   184
      ]]></Sql>
carlos@0
   185
        <Field name="position" value="count"/>
ivan@712
   186
        <Parameter name="rownum" type="replace" optional="true" after="FROM ( SELECT " text="'0'" />
carlos@0
   187
        <Parameter name="adLanguage"/>
carlos@0
   188
        <Parameter name="key" optional="true" after="WHERE "><![CDATA[ UPPER(P.Value) LIKE UPPER(?) AND ]]></Parameter>
carlos@0
   189
        <Parameter name="name" optional="true" after="WHERE "><![CDATA[ UPPER(P.Name) LIKE UPPER(?) AND ]]></Parameter>
carlos@0
   190
        <Parameter name="cBpartnerId"/>
carlos@0
   191
        <Parameter name="adUserClient" type="replace" optional="true" after="P.AD_CLIENT_ID IN (" text="'1'"/>
ivan@712
   192
        <Parameter name="adUserOrg" type="replace" optional="true" after="P.AD_ORG_ID IN (" text="'1'"/> 
ivan@712
   193
        <Parameter name="orderBy" type="replace" optional="true" after="ORDER BY " text="P.VALUE" />
ivan@712
   194
        <Parameter name="oraLimit" type="argument" optional="true" after="WHERE 1=1"><![CDATA[AND RN1 BETWEEN ]]></Parameter>
ivan@712
   195
        <Parameter name="pgLimit" type="argument" optional="true" after="WHERE 1=1"><![CDATA[LIMIT ]]></Parameter>       
carlos@0
   196
   </SqlMethod>
carlos@0
   197
   <SqlMethod name="existsActualValue" type="preparedStatement" return="String" default="">
carlos@0
   198
      <SqlMethodComment></SqlMethodComment>
carlos@0
   199
      <Sql> <![CDATA[
carlos@0
   200
        SELECT value as value
carlos@0
   201
        FROM M_Product 
carlos@0
   202
        WHERE UPPER(AD_COLUMN_IDENTIFIER(TO_CHAR('M_Product'), TO_CHAR(M_Product_ID), TO_CHAR(?))) = UPPER(?)
carlos@423
   203
        AND M_Product_ID = TO_NUMBER(?) 
carlos@0
   204
        ORDER BY value
carlos@0
   205
      ]]></Sql>
carlos@0
   206
        <Parameter name="language"/>
carlos@0
   207
        <Parameter name="description"/>
carlos@0
   208
        <Parameter name="cBpartnerId"/>
carlos@0
   209
   </SqlMethod>
carlos@0
   210
</SqlClass>