src/org/openbravo/erpCommon/info/ProductComplete_data.xsql
changeset 712 aa0c40278c5a
parent 423 ecf368072c48
child 871 b9084eea924e
equal deleted inserted replaced
711:4e8209a9d749 712:aa0c40278c5a
    10  * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
    10  * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
    11  * License for the specific  language  governing  rights  and  limitations
    11  * License for the specific  language  governing  rights  and  limitations
    12  * under the License. 
    12  * under the License. 
    13  * The Original Code is Openbravo ERP. 
    13  * The Original Code is Openbravo ERP. 
    14  * The Initial Developer of the Original Code is Openbravo SL 
    14  * The Initial Developer of the Original Code is Openbravo SL 
    15  * All portions are Copyright (C) 2001-2006 Openbravo SL 
    15  * All portions are Copyright (C) 2001-2008 Openbravo SL 
    16  * All Rights Reserved. 
    16  * All Rights Reserved. 
    17  * Contributor(s):  ______________________________________.
    17  * Contributor(s):  ______________________________________.
    18  ************************************************************************
    18  ************************************************************************
    19 -->
    19 -->
    20 
    20 
    25 <SqlClass name="ProductCompleteData" package="org.openbravo.erpCommon.info">
    25 <SqlClass name="ProductCompleteData" package="org.openbravo.erpCommon.info">
    26    <SqlClassComment></SqlClassComment>
    26    <SqlClassComment></SqlClassComment>
    27    <SqlMethod name="select" type="preparedStatement" return="multiple">
    27    <SqlMethod name="select" type="preparedStatement" return="multiple">
    28       <SqlMethodComment></SqlMethodComment>
    28       <SqlMethodComment></SqlMethodComment>
    29       <Sql> <![CDATA[
    29       <Sql> <![CDATA[
       
    30       SELECT * FROM ( SELECT '0' AS RN1, A.* FROM (      				   
    30         SELECT T.M_PRODUCT_ID, P.DISCONTINUED, P.VALUE, P.NAME, P.NAME AS NAME_HIDDEN, 
    31         SELECT T.M_PRODUCT_ID, P.DISCONTINUED, P.VALUE, P.NAME, P.NAME AS NAME_HIDDEN, 
    31         T.QtyOnHand AS QTY, U1.NAME AS C_UOM1, COALESCE(T.QtyOrderOnHand, 0) AS QTYORDER, U2.NAME AS C_UOM2, 
    32         T.QtyOnHand AS QTY, U1.NAME AS C_UOM1, COALESCE(T.QtyOrderOnHand, 0) AS QTYORDER, U2.NAME AS C_UOM2, 
    32         A.DESCRIPTION AS ATTRIBUTE,
    33         A.DESCRIPTION AS ATTRIBUTE,
    33         (W.NAME || ' - ' || L.VALUE || '-' || L.X || '-' || L.Y || '-' || L.Z) AS LOCATOR,  
    34         (W.NAME || ' - ' || L.VALUE || '-' || L.X || '-' || L.Y || '-' || L.Z) AS LOCATOR,  
    34         T.M_ATTRIBUTESETINSTANCE_ID, T.M_LOCATOR_ID, 
    35         T.M_ATTRIBUTESETINSTANCE_ID, T.M_LOCATOR_ID, 
    35         T.C_UOM_ID AS C_UOM1_ID, T.M_PRODUCT_UOM_ID AS C_UOM2_ID, COALESCE(T.PREQTYONHAND, 0) AS QTY_REF, 
    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, 
    36         COALESCE(T.PREQTYORDERONHAND, 0) AS QUANTITYORDER_REF, 
    37         COALESCE(T.PREQTYORDERONHAND, 0) AS QUANTITYORDER_REF, 
    37         (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
    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,
       
    39         T.M_PRODUCT_ID || '#' || p.name || '#' || T.M_LOCATOR_ID || '#' || T.M_ATTRIBUTESETINSTANCE_ID || '#' || 
       
    40           COALESCE(T.QtyOrderOnHand, 0) || '#' || T.M_PRODUCT_UOM_ID || '#' || T.QtyOnHand || '#' || T.C_UOM_ID as rowkey
    38         FROM M_PRODUCT_UOM PU right join M_STORAGE_DETAIL T on T.M_PRODUCT_UOM_ID = PU.M_PRODUCT_UOM_ID
    41         FROM M_PRODUCT_UOM PU right join M_STORAGE_DETAIL T on T.M_PRODUCT_UOM_ID = PU.M_PRODUCT_UOM_ID
    39                               left join C_UOM U2 on PU.C_UOM_ID = U2.C_UOM_ID
    42                               left join C_UOM U2 on PU.C_UOM_ID = U2.C_UOM_ID
    40                               left join  M_ATTRIBUTESETINSTANCE A on T.M_ATTRIBUTESETINSTANCE_ID = A.M_ATTRIBUTESETINSTANCE_ID,
    43                               left join  M_ATTRIBUTESETINSTANCE A on T.M_ATTRIBUTESETINSTANCE_ID = A.M_ATTRIBUTESETINSTANCE_ID,
    41              M_PRODUCT P, C_UOM U1,
    44              M_PRODUCT P, C_UOM U1,
    42         M_LOCATOR L, M_Warehouse W, AD_ORG O, AD_ROLE_ORGACCESS RO
    45         M_LOCATOR L, M_Warehouse W, AD_ORG O, AD_ROLE_ORGACCESS RO
    50         AND P.ISACTIVE = 'Y'
    53         AND P.ISACTIVE = 'Y'
    51         AND RO.AD_ROLE_ID = TO_NUMBER(?) 
    54         AND RO.AD_ROLE_ID = TO_NUMBER(?) 
    52         AND (P.C_BPARTNER_ID IS NULL OR C_BPARTNER_ID = COALESCE(to_number(?), C_BPARTNER_ID)) 
    55         AND (P.C_BPARTNER_ID IS NULL OR C_BPARTNER_ID = COALESCE(to_number(?), C_BPARTNER_ID)) 
    53         AND W.AD_CLIENT_ID IN ('1') 
    56         AND W.AD_CLIENT_ID IN ('1') 
    54         AND (T.QtyOnHand <> 0 OR COALESCE(T.QtyOrderOnHand, 0) <> 0) 
    57         AND (T.QtyOnHand <> 0 OR COALESCE(T.QtyOrderOnHand, 0) <> 0) 
    55         ORDER BY P.NAME, W.NAME, L.VALUE, L.X, L.Y, L.Z, C_UOM1, QTY DESC, ATTRIBUTE
    58         ORDER BY P.VALUE
    56       ]]></Sql>
    59 			) A ) B
    57         <Field name="position" value="count"/>
    60 		WHERE 1=1
       
    61       ]]></Sql>
       
    62         <Field name="position" value="count"/>
       
    63         <Parameter name="rownum" type="replace" optional="true" after="FROM ( SELECT " text="'0'" />
    58         <Parameter name="key" optional="true" after="WHERE T.M_PRODUCT_ID = P.M_PRODUCT_ID "><![CDATA[ AND UPPER(P.Value) LIKE UPPER(?) ]]></Parameter>
    64         <Parameter name="key" optional="true" after="WHERE T.M_PRODUCT_ID = P.M_PRODUCT_ID "><![CDATA[ AND UPPER(P.Value) LIKE UPPER(?) ]]></Parameter>
    59         <Parameter name="name" optional="true" after="WHERE T.M_PRODUCT_ID = P.M_PRODUCT_ID "><![CDATA[ AND UPPER(P.Name) LIKE UPPER(?) ]]></Parameter>
    65         <Parameter name="name" optional="true" after="WHERE T.M_PRODUCT_ID = P.M_PRODUCT_ID "><![CDATA[ AND UPPER(P.Name) LIKE UPPER(?) ]]></Parameter>
    60         <Parameter name="warehouse" optional="true" after="WHERE T.M_PRODUCT_ID = P.M_PRODUCT_ID "><![CDATA[ AND W.M_Warehouse_ID = TO_NUMBER(?) ]]></Parameter>
    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>
    61         <Parameter name="adRoleId"/>
    67         <Parameter name="adRoleId"/>
    62         <Parameter name="cBpartnerId"/>
    68         <Parameter name="cBpartnerId"/>
    63         <Parameter name="adUserClient" type="replace" optional="true" after="W.AD_CLIENT_ID IN (" text="'1'"/>
    69         <Parameter name="adUserClient" type="replace" optional="true" after="W.AD_CLIENT_ID IN (" text="'1'"/>
       
    70         <Parameter name="orderBy" type="replace" optional="true" after="ORDER BY " text="P.VALUE" />
       
    71         <Parameter name="oraLimit" type="argument" optional="true" after="WHERE 1=1"><![CDATA[AND RN1 BETWEEN ]]></Parameter>
       
    72         <Parameter name="pgLimit" type="argument" optional="true" after="WHERE 1=1"><![CDATA[LIMIT ]]></Parameter>
    64    </SqlMethod>
    73    </SqlMethod>
    65    <SqlMethod name="set" type="constant" return="multiple">
    74    <SqlMethod name="set" type="constant" return="multiple">
    66       <SqlMethodComment></SqlMethodComment>
    75       <SqlMethodComment></SqlMethodComment>
    67       <Sql></Sql>
    76       <Sql></Sql>
    68    </SqlMethod>
    77    </SqlMethod>
    69    <SqlMethod name="selecttrl" type="preparedStatement" return="multiple">
    78    <SqlMethod name="selecttrl" type="preparedStatement" return="multiple">
    70       <SqlMethodComment></SqlMethodComment>
    79       <SqlMethodComment></SqlMethodComment>
    71       <Sql> <![CDATA[
    80       <Sql> <![CDATA[
       
    81       SELECT * FROM ( SELECT '0' AS RN1, A.* FROM ( 
    72         SELECT T.M_PRODUCT_ID, P.DISCONTINUED, P.VALUE, P.NAME, P.NAME AS NAME_HIDDEN, 
    82         SELECT T.M_PRODUCT_ID, P.DISCONTINUED, P.VALUE, P.NAME, P.NAME AS NAME_HIDDEN, 
    73         T.QtyOnHand AS QTY, U1TRL.NAME AS C_UOM1, COALESCE(T.QtyOrderOnHand, 0) AS QTYORDER, U2.NAME AS C_UOM2, 
    83         T.QtyOnHand AS QTY, U1TRL.NAME AS C_UOM1, COALESCE(T.QtyOrderOnHand, 0) AS QTYORDER, U2.NAME AS C_UOM2, 
    74         A.DESCRIPTION AS ATTRIBUTE,
    84         A.DESCRIPTION AS ATTRIBUTE,
    75         (W.NAME || ' - ' || L.VALUE || '-' || L.X || '-' || L.Y || '-' || L.Z) AS LOCATOR,  
    85         (W.NAME || ' - ' || L.VALUE || '-' || L.X || '-' || L.Y || '-' || L.Z) AS LOCATOR,  
    76         T.M_ATTRIBUTESETINSTANCE_ID, T.M_LOCATOR_ID, 
    86         T.M_ATTRIBUTESETINSTANCE_ID, T.M_LOCATOR_ID, 
    77         T.C_UOM_ID AS C_UOM1_ID, T.M_PRODUCT_UOM_ID AS C_UOM2_ID, COALESCE(T.PREQTYONHAND, 0) AS QTY_REF, 
    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, 
    78         COALESCE(T.PREQTYORDERONHAND, 0) AS QUANTITYORDER_REF, 
    88         COALESCE(T.PREQTYORDERONHAND, 0) AS QUANTITYORDER_REF, 
    79         (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
    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,
       
    90         T.M_PRODUCT_ID || '#' || p.name || '#' || T.M_LOCATOR_ID || '#' || T.M_ATTRIBUTESETINSTANCE_ID || '#' || 
       
    91           COALESCE(T.QtyOrderOnHand, 0) || '#' || T.M_PRODUCT_UOM_ID || '#' || T.QtyOnHand || '#' || T.C_UOM_ID as rowkey
    80         FROM C_UOM U1 left join C_UOM_TRL U1TRL on U1.C_UOM_ID=U1TRL.C_UOM_ID
    92         FROM C_UOM U1 left join C_UOM_TRL U1TRL on U1.C_UOM_ID=U1TRL.C_UOM_ID
    81                                                 and U1TRL.AD_LANGUAGE= ?,
    93                                                 and U1TRL.AD_LANGUAGE= ?,
    82              M_PRODUCT_UOM PU right join M_STORAGE_DETAIL T on T.M_PRODUCT_UOM_ID = PU.M_PRODUCT_UOM_ID
    94              M_PRODUCT_UOM PU right join M_STORAGE_DETAIL T on T.M_PRODUCT_UOM_ID = PU.M_PRODUCT_UOM_ID
    83                               left join C_UOM U2 on PU.C_UOM_ID = U2.C_UOM_ID
    95                               left join C_UOM U2 on PU.C_UOM_ID = U2.C_UOM_ID
    84                               left join M_ATTRIBUTESETINSTANCE A on T.M_ATTRIBUTESETINSTANCE_ID = A.M_ATTRIBUTESETINSTANCE_ID,
    96                               left join M_ATTRIBUTESETINSTANCE A on T.M_ATTRIBUTESETINSTANCE_ID = A.M_ATTRIBUTESETINSTANCE_ID,
    94         AND P.ISACTIVE = 'Y'
   106         AND P.ISACTIVE = 'Y'
    95         AND RO.AD_ROLE_ID = TO_NUMBER(?) 
   107         AND RO.AD_ROLE_ID = TO_NUMBER(?) 
    96         AND (P.C_BPARTNER_ID IS NULL OR C_BPARTNER_ID = COALESCE(to_number(?), C_BPARTNER_ID)) 
   108         AND (P.C_BPARTNER_ID IS NULL OR C_BPARTNER_ID = COALESCE(to_number(?), C_BPARTNER_ID)) 
    97         AND W.AD_CLIENT_ID IN ('1') 
   109         AND W.AD_CLIENT_ID IN ('1') 
    98         AND (T.QtyOnHand <> 0 OR COALESCE(T.QtyOrderOnHand, 0) <> 0) 
   110         AND (T.QtyOnHand <> 0 OR COALESCE(T.QtyOrderOnHand, 0) <> 0) 
    99         ORDER BY P.NAME, W.NAME, L.VALUE, L.X, L.Y, L.Z, C_UOM1, QTY DESC, ATTRIBUTE 
   111         ORDER BY P.VALUE
   100       ]]></Sql>
   112         ) A ) B
   101         <Field name="position" value="count"/>
   113       WHERE 1=1        
       
   114       ]]></Sql>
       
   115         <Field name="position" value="count"/>
       
   116         <Parameter name="rownum" type="replace" optional="true" after="FROM ( SELECT " text="'0'" />
   102         <Parameter name="adLanguage"/>
   117         <Parameter name="adLanguage"/>
   103         <Parameter name="key" optional="true" after="WHERE T.M_PRODUCT_ID = P.M_PRODUCT_ID "><![CDATA[ AND UPPER(P.Value) LIKE UPPER(?) ]]></Parameter>
   118         <Parameter name="key" optional="true" after="WHERE T.M_PRODUCT_ID = P.M_PRODUCT_ID "><![CDATA[ AND UPPER(P.Value) LIKE UPPER(?) ]]></Parameter>
   104         <Parameter name="name" optional="true" after="WHERE T.M_PRODUCT_ID = P.M_PRODUCT_ID "><![CDATA[ AND UPPER(P.Name) LIKE UPPER(?) ]]></Parameter>
   119         <Parameter name="name" optional="true" after="WHERE T.M_PRODUCT_ID = P.M_PRODUCT_ID "><![CDATA[ AND UPPER(P.Name) LIKE UPPER(?) ]]></Parameter>
   105         <Parameter name="warehouse" optional="true" after="WHERE T.M_PRODUCT_ID = P.M_PRODUCT_ID "><![CDATA[ AND W.M_Warehouse_ID = TO_NUMBER(?) ]]></Parameter>
   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>
   106         <Parameter name="adRoleId"/>
   121         <Parameter name="adRoleId"/>
   107         <Parameter name="cBpartnerId"/>
   122         <Parameter name="cBpartnerId"/>
   108         <Parameter name="adUserClient" type="replace" optional="true" after="W.AD_CLIENT_ID IN (" text="'1'"/>      
   123         <Parameter name="adUserClient" type="replace" optional="true" after="W.AD_CLIENT_ID IN (" text="'1'"/>  
       
   124         <Parameter name="orderBy" type="replace" optional="true" after="ORDER BY " text="P.VALUE" />
       
   125         <Parameter name="oraLimit" type="argument" optional="true" after="WHERE 1=1"><![CDATA[AND RN1 BETWEEN ]]></Parameter>
       
   126         <Parameter name="pgLimit" type="argument" optional="true" after="WHERE 1=1"><![CDATA[LIMIT ]]></Parameter>    
   109    </SqlMethod>
   127    </SqlMethod>
   110 
   128 
   111    <SqlMethod name="selectNotStored" type="preparedStatement" return="multiple">
   129    <SqlMethod name="selectNotStored" type="preparedStatement" return="multiple">
   112       <SqlMethodComment></SqlMethodComment>
   130       <SqlMethodComment></SqlMethodComment>
   113       <Sql> <![CDATA[
   131       <Sql> <![CDATA[
       
   132       SELECT * FROM ( SELECT '0' AS RN1, A.* FROM ( 
   114         SELECT P.M_PRODUCT_ID, P.DISCONTINUED, P.VALUE, P.NAME, P.NAME AS NAME_HIDDEN, 0 AS QTY, U1.NAME AS C_UOM1, 
   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, 
   115         0 AS QTYORDER, NULL AS C_UOM2, A.DESCRIPTION AS ATTRIBUTE, 
   134         0 AS QTYORDER, NULL AS C_UOM2, A.DESCRIPTION AS ATTRIBUTE, 
   116         NULL AS LOCATOR, 
   135         NULL AS LOCATOR, 
   117         P.M_ATTRIBUTESETINSTANCE_ID, NULL AS M_LOCATOR_ID, 
   136         P.M_ATTRIBUTESETINSTANCE_ID, NULL AS M_LOCATOR_ID, 
   118         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
   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,
       
   138         P.M_PRODUCT_ID || '#' || p.name || '#' || NULL || '#' || P.M_ATTRIBUTESETINSTANCE_ID || '#' || 
       
   139           0 || '#' || NULL || '#' || 0 || '#' || P.C_UOM_ID as rowkey
   119         FROM M_PRODUCT P left join M_ATTRIBUTESETINSTANCE A  on P.M_ATTRIBUTESETINSTANCE_ID = A.M_ATTRIBUTESETINSTANCE_ID, 
   140         FROM M_PRODUCT P left join M_ATTRIBUTESETINSTANCE A  on P.M_ATTRIBUTESETINSTANCE_ID = A.M_ATTRIBUTESETINSTANCE_ID, 
   120              C_UOM U1
   141              C_UOM U1
   121         WHERE P.C_UOM_ID = U1.C_UOM_ID 
   142         WHERE P.C_UOM_ID = U1.C_UOM_ID 
   122         AND (P.C_BPARTNER_ID IS NULL OR C_BPARTNER_ID = COALESCE(to_number(?), C_BPARTNER_ID)) 
   143         AND (P.C_BPARTNER_ID IS NULL OR C_BPARTNER_ID = COALESCE(to_number(?), C_BPARTNER_ID)) 
   123         AND P.AD_CLIENT_ID IN ('1') 
   144         AND P.AD_CLIENT_ID IN ('1') 
   124         AND P.AD_ORG_ID IN ('1') 
   145         AND P.AD_ORG_ID IN ('1') 
   125         AND P.ISACTIVE = 'Y'
   146         AND P.ISACTIVE = 'Y'
   126       ]]></Sql>
   147         ORDER BY P.VALUE
   127         <Field name="position" value="count"/>
   148         ) A ) B
       
   149       WHERE 1=1   
       
   150       ]]></Sql>
       
   151         <Field name="position" value="count"/>
       
   152         <Parameter name="rownum" type="replace" optional="true" after="FROM ( SELECT " text="'0'" />
   128         <Parameter name="key" optional="true" after="WHERE "><![CDATA[ UPPER(P.Value) LIKE UPPER(?) AND ]]></Parameter>
   153         <Parameter name="key" optional="true" after="WHERE "><![CDATA[ UPPER(P.Value) LIKE UPPER(?) AND ]]></Parameter>
   129         <Parameter name="name" optional="true" after="WHERE "><![CDATA[ UPPER(P.Name) LIKE UPPER(?) AND ]]></Parameter>
   154         <Parameter name="name" optional="true" after="WHERE "><![CDATA[ UPPER(P.Name) LIKE UPPER(?) AND ]]></Parameter>
   130         <Parameter name="cBpartnerId"/>
   155         <Parameter name="cBpartnerId"/>
   131         <Parameter name="adUserClient" type="replace" optional="true" after="P.AD_CLIENT_ID IN (" text="'1'"/>
   156         <Parameter name="adUserClient" type="replace" optional="true" after="P.AD_CLIENT_ID IN (" text="'1'"/>
   132         <Parameter name="adUserOrg" type="replace" optional="true" after="P.AD_ORG_ID IN (" text="'1'"/>
   157         <Parameter name="adUserOrg" type="replace" optional="true" after="P.AD_ORG_ID IN (" text="'1'"/>
       
   158         <Parameter name="orderBy" type="replace" optional="true" after="ORDER BY " text="P.VALUE" />
       
   159         <Parameter name="oraLimit" type="argument" optional="true" after="WHERE 1=1"><![CDATA[AND RN1 BETWEEN ]]></Parameter>
       
   160         <Parameter name="pgLimit" type="argument" optional="true" after="WHERE 1=1"><![CDATA[LIMIT ]]></Parameter>
   133    </SqlMethod>
   161    </SqlMethod>
   134    <SqlMethod name="selectNotStoredtrl" type="preparedStatement" return="multiple">
   162    <SqlMethod name="selectNotStoredtrl" type="preparedStatement" return="multiple">
   135       <SqlMethodComment></SqlMethodComment>
   163       <SqlMethodComment></SqlMethodComment>
   136       <Sql> <![CDATA[
   164       <Sql> <![CDATA[
       
   165       SELECT * FROM ( SELECT '0' AS RN1, A.* FROM ( 
   137         SELECT P.M_PRODUCT_ID, P.DISCONTINUED, P.VALUE, P.NAME, P.NAME AS NAME_HIDDEN, 0 AS QTY, U1TRL.NAME AS C_UOM1, 
   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, 
   138         0 AS QTYORDER, NULL AS C_UOM2, A.DESCRIPTION AS ATTRIBUTE, 
   167         0 AS QTYORDER, NULL AS C_UOM2, A.DESCRIPTION AS ATTRIBUTE, 
   139         NULL AS LOCATOR, 
   168         NULL AS LOCATOR, 
   140         P.M_ATTRIBUTESETINSTANCE_ID, NULL AS M_LOCATOR_ID, 
   169         P.M_ATTRIBUTESETINSTANCE_ID, NULL AS M_LOCATOR_ID, 
   141         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
   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,
       
   171         P.M_PRODUCT_ID || '#' || p.name || '#' || NULL || '#' || P.M_ATTRIBUTESETINSTANCE_ID || '#' || 
       
   172           0 || '#' || NULL || '#' || 0 || '#' || P.C_UOM_ID as rowkey
   142         FROM M_PRODUCT P left join M_ATTRIBUTESETINSTANCE A on P.M_ATTRIBUTESETINSTANCE_ID = A.M_ATTRIBUTESETINSTANCE_ID,
   173         FROM M_PRODUCT P left join M_ATTRIBUTESETINSTANCE A on P.M_ATTRIBUTESETINSTANCE_ID = A.M_ATTRIBUTESETINSTANCE_ID,
   143              C_UOM U1    left join C_UOM_TRL U1TRL on U1TRL.C_UOM_ID= U1.C_UOM_ID 
   174              C_UOM U1    left join C_UOM_TRL U1TRL on U1TRL.C_UOM_ID= U1.C_UOM_ID 
   144                                                   AND U1TRL.AD_LANGUAGE= ?
   175                                                   AND U1TRL.AD_LANGUAGE= ?
   145         WHERE P.C_UOM_ID = U1.C_UOM_ID 
   176         WHERE P.C_UOM_ID = U1.C_UOM_ID 
   146         AND (P.C_BPARTNER_ID IS NULL OR C_BPARTNER_ID = COALESCE(to_number(?), C_BPARTNER_ID)) 
   177         AND (P.C_BPARTNER_ID IS NULL OR C_BPARTNER_ID = COALESCE(to_number(?), C_BPARTNER_ID)) 
   147         AND P.AD_CLIENT_ID IN ('1') 
   178         AND P.AD_CLIENT_ID IN ('1') 
   148         AND P.AD_ORG_ID IN ('1') 
   179         AND P.AD_ORG_ID IN ('1') 
   149         AND P.ISACTIVE = 'Y'
   180         AND P.ISACTIVE = 'Y'
   150       ]]></Sql>
   181         ORDER BY P.VALUE
   151         <Field name="position" value="count"/>
   182         ) A ) B
       
   183       WHERE 1=1 
       
   184       ]]></Sql>
       
   185         <Field name="position" value="count"/>
       
   186         <Parameter name="rownum" type="replace" optional="true" after="FROM ( SELECT " text="'0'" />
   152         <Parameter name="adLanguage"/>
   187         <Parameter name="adLanguage"/>
   153         <Parameter name="key" optional="true" after="WHERE "><![CDATA[ UPPER(P.Value) LIKE UPPER(?) AND ]]></Parameter>
   188         <Parameter name="key" optional="true" after="WHERE "><![CDATA[ UPPER(P.Value) LIKE UPPER(?) AND ]]></Parameter>
   154         <Parameter name="name" optional="true" after="WHERE "><![CDATA[ UPPER(P.Name) LIKE UPPER(?) AND ]]></Parameter>
   189         <Parameter name="name" optional="true" after="WHERE "><![CDATA[ UPPER(P.Name) LIKE UPPER(?) AND ]]></Parameter>
   155         <Parameter name="cBpartnerId"/>
   190         <Parameter name="cBpartnerId"/>
   156         <Parameter name="adUserClient" type="replace" optional="true" after="P.AD_CLIENT_ID IN (" text="'1'"/>
   191         <Parameter name="adUserClient" type="replace" optional="true" after="P.AD_CLIENT_ID IN (" text="'1'"/>
   157         <Parameter name="adUserOrg" type="replace" optional="true" after="P.AD_ORG_ID IN (" text="'1'"/>        
   192         <Parameter name="adUserOrg" type="replace" optional="true" after="P.AD_ORG_ID IN (" text="'1'"/> 
       
   193         <Parameter name="orderBy" type="replace" optional="true" after="ORDER BY " text="P.VALUE" />
       
   194         <Parameter name="oraLimit" type="argument" optional="true" after="WHERE 1=1"><![CDATA[AND RN1 BETWEEN ]]></Parameter>
       
   195         <Parameter name="pgLimit" type="argument" optional="true" after="WHERE 1=1"><![CDATA[LIMIT ]]></Parameter>       
   158    </SqlMethod>
   196    </SqlMethod>
   159    <SqlMethod name="existsActualValue" type="preparedStatement" return="String" default="">
   197    <SqlMethod name="existsActualValue" type="preparedStatement" return="String" default="">
   160       <SqlMethodComment></SqlMethodComment>
   198       <SqlMethodComment></SqlMethodComment>
   161       <Sql> <![CDATA[
   199       <Sql> <![CDATA[
   162         SELECT value as value
   200         SELECT value as value