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.
<?xml version="1.0" encoding="UTF-8" ?>
<!--
 *************************************************************************
 * The contents of this file are subject to the Openbravo  Public  License
 * Version  1.0  (the  "License"),  being   the  Mozilla   Public  License
 * Version 1.1  with a permitted attribution clause; you may not  use this
 * file except in compliance with the License. You  may  obtain  a copy of
 * the License at http://www.openbravo.com/legal/license.html 
 * Software distributed under the License  is  distributed  on  an "AS IS"
 * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
 * License for the specific  language  governing  rights  and  limitations
 * under the License. 
 * The Original Code is Openbravo ERP. 
 * The Initial Developer of the Original Code is Openbravo SL 
 * All portions are Copyright (C) 2001-2008 Openbravo SL 
 * All Rights Reserved. 
 * Contributor(s):  ______________________________________.
 ************************************************************************
-->





<SqlClass name="ProductCompleteData" package="org.openbravo.erpCommon.info">
   <SqlClassComment></SqlClassComment>
   <SqlMethod name="select" type="preparedStatement" return="multiple">
      <SqlMethodComment></SqlMethodComment>
      <Sql> <![CDATA[
      SELECT * FROM ( SELECT '0' AS RN1, A.* FROM (      				   
        SELECT T.M_PRODUCT_ID, P.DISCONTINUED, P.VALUE, P.NAME, P.NAME AS NAME_HIDDEN, 
        T.QtyOnHand AS QTY, U1.NAME AS C_UOM1, COALESCE(T.QtyOrderOnHand, 0) AS QTYORDER, U2.NAME AS C_UOM2, 
        A.DESCRIPTION AS ATTRIBUTE,
        (W.NAME || ' - ' || L.VALUE || '-' || L.X || '-' || L.Y || '-' || L.Z) AS LOCATOR,  
        T.M_ATTRIBUTESETINSTANCE_ID, T.M_LOCATOR_ID, 
        T.C_UOM_ID AS C_UOM1_ID, T.M_PRODUCT_UOM_ID AS C_UOM2_ID, COALESCE(T.PREQTYONHAND, 0) AS QTY_REF, 
        COALESCE(T.PREQTYORDERONHAND, 0) AS QUANTITYORDER_REF, 
        (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,
        T.M_PRODUCT_ID || '#' || p.name || '#' || T.M_LOCATOR_ID || '#' || T.M_ATTRIBUTESETINSTANCE_ID || '#' || 
          COALESCE(T.QtyOrderOnHand, 0) || '#' || T.M_PRODUCT_UOM_ID || '#' || T.QtyOnHand || '#' || T.C_UOM_ID as rowkey
        FROM M_PRODUCT_UOM PU right join M_STORAGE_DETAIL T on T.M_PRODUCT_UOM_ID = PU.M_PRODUCT_UOM_ID
                              left join C_UOM U2 on PU.C_UOM_ID = U2.C_UOM_ID
                              left join  M_ATTRIBUTESETINSTANCE A on T.M_ATTRIBUTESETINSTANCE_ID = A.M_ATTRIBUTESETINSTANCE_ID,
             M_PRODUCT P, C_UOM U1,
        M_LOCATOR L, M_Warehouse W, AD_ORG O, AD_ROLE_ORGACCESS RO
        WHERE T.M_PRODUCT_ID = P.M_PRODUCT_ID 
        AND W.M_WAREHOUSE_ID = L.M_WAREHOUSE_ID 
        AND T.M_LOCATOR_ID = L.M_LOCATOR_ID 
        AND T.C_UOM_ID = U1.C_UOM_ID 
        AND W.AD_ORG_ID = O.AD_ORG_ID 
        AND O.AD_ORG_ID = RO.AD_ORG_ID 
        AND W.ISACTIVE = 'Y' 
        AND P.ISACTIVE = 'Y'
        AND RO.AD_ROLE_ID = TO_NUMBER(?) 
        AND (P.C_BPARTNER_ID IS NULL OR C_BPARTNER_ID = COALESCE(to_number(?), C_BPARTNER_ID)) 
        AND W.AD_CLIENT_ID IN ('1') 
        AND (T.QtyOnHand <> 0 OR COALESCE(T.QtyOrderOnHand, 0) <> 0) 
        ORDER BY P.VALUE
			) A ) B
		WHERE 1=1
      ]]></Sql>
        <Field name="position" value="count"/>
        <Parameter name="rownum" type="replace" optional="true" after="FROM ( SELECT " text="'0'" />
        <Parameter name="key" optional="true" after="WHERE T.M_PRODUCT_ID = P.M_PRODUCT_ID "><![CDATA[ AND UPPER(P.Value) LIKE UPPER(?) ]]></Parameter>
        <Parameter name="name" optional="true" after="WHERE T.M_PRODUCT_ID = P.M_PRODUCT_ID "><![CDATA[ AND UPPER(P.Name) LIKE UPPER(?) ]]></Parameter>
        <Parameter name="warehouse" optional="true" after="WHERE T.M_PRODUCT_ID = P.M_PRODUCT_ID "><![CDATA[ AND W.M_Warehouse_ID = TO_NUMBER(?) ]]></Parameter>
        <Parameter name="adRoleId"/>
        <Parameter name="cBpartnerId"/>
        <Parameter name="adUserClient" type="replace" optional="true" after="W.AD_CLIENT_ID IN (" text="'1'"/>
        <Parameter name="orderBy" type="replace" optional="true" after="ORDER BY " text="P.VALUE" />
        <Parameter name="oraLimit" type="argument" optional="true" after="WHERE 1=1"><![CDATA[AND RN1 BETWEEN ]]></Parameter>
        <Parameter name="pgLimit" type="argument" optional="true" after="WHERE 1=1"><![CDATA[LIMIT ]]></Parameter>
   </SqlMethod>
   <SqlMethod name="set" type="constant" return="multiple">
      <SqlMethodComment></SqlMethodComment>
      <Sql></Sql>
   </SqlMethod>
   <SqlMethod name="selecttrl" type="preparedStatement" return="multiple">
      <SqlMethodComment></SqlMethodComment>
      <Sql> <![CDATA[
      SELECT * FROM ( SELECT '0' AS RN1, A.* FROM ( 
        SELECT T.M_PRODUCT_ID, P.DISCONTINUED, P.VALUE, P.NAME, P.NAME AS NAME_HIDDEN, 
        T.QtyOnHand AS QTY, U1TRL.NAME AS C_UOM1, COALESCE(T.QtyOrderOnHand, 0) AS QTYORDER, U2.NAME AS C_UOM2, 
        A.DESCRIPTION AS ATTRIBUTE,
        (W.NAME || ' - ' || L.VALUE || '-' || L.X || '-' || L.Y || '-' || L.Z) AS LOCATOR,  
        T.M_ATTRIBUTESETINSTANCE_ID, T.M_LOCATOR_ID, 
        T.C_UOM_ID AS C_UOM1_ID, T.M_PRODUCT_UOM_ID AS C_UOM2_ID, COALESCE(T.PREQTYONHAND, 0) AS QTY_REF, 
        COALESCE(T.PREQTYORDERONHAND, 0) AS QUANTITYORDER_REF, 
        (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,
        T.M_PRODUCT_ID || '#' || p.name || '#' || T.M_LOCATOR_ID || '#' || T.M_ATTRIBUTESETINSTANCE_ID || '#' || 
          COALESCE(T.QtyOrderOnHand, 0) || '#' || T.M_PRODUCT_UOM_ID || '#' || T.QtyOnHand || '#' || T.C_UOM_ID as rowkey
        FROM C_UOM U1 left join C_UOM_TRL U1TRL on U1.C_UOM_ID=U1TRL.C_UOM_ID
                                                and U1TRL.AD_LANGUAGE= ?,
             M_PRODUCT_UOM PU right join M_STORAGE_DETAIL T on T.M_PRODUCT_UOM_ID = PU.M_PRODUCT_UOM_ID
                              left join C_UOM U2 on PU.C_UOM_ID = U2.C_UOM_ID
                              left join M_ATTRIBUTESETINSTANCE A on T.M_ATTRIBUTESETINSTANCE_ID = A.M_ATTRIBUTESETINSTANCE_ID,
             M_PRODUCT P,
             M_LOCATOR L, M_Warehouse W, AD_ORG O, AD_ROLE_ORGACCESS RO
        WHERE T.M_PRODUCT_ID = P.M_PRODUCT_ID 
        AND W.M_WAREHOUSE_ID = L.M_WAREHOUSE_ID 
        AND T.M_LOCATOR_ID = L.M_LOCATOR_ID 
        AND T.C_UOM_ID = U1.C_UOM_ID 
        AND W.AD_ORG_ID = O.AD_ORG_ID 
        AND O.AD_ORG_ID = RO.AD_ORG_ID 
        AND W.ISACTIVE = 'Y' 
        AND P.ISACTIVE = 'Y'
        AND RO.AD_ROLE_ID = TO_NUMBER(?) 
        AND (P.C_BPARTNER_ID IS NULL OR C_BPARTNER_ID = COALESCE(to_number(?), C_BPARTNER_ID)) 
        AND W.AD_CLIENT_ID IN ('1') 
        AND (T.QtyOnHand <> 0 OR COALESCE(T.QtyOrderOnHand, 0) <> 0) 
        ORDER BY P.VALUE
        ) A ) B
      WHERE 1=1        
      ]]></Sql>
        <Field name="position" value="count"/>
        <Parameter name="rownum" type="replace" optional="true" after="FROM ( SELECT " text="'0'" />
        <Parameter name="adLanguage"/>
        <Parameter name="key" optional="true" after="WHERE T.M_PRODUCT_ID = P.M_PRODUCT_ID "><![CDATA[ AND UPPER(P.Value) LIKE UPPER(?) ]]></Parameter>
        <Parameter name="name" optional="true" after="WHERE T.M_PRODUCT_ID = P.M_PRODUCT_ID "><![CDATA[ AND UPPER(P.Name) LIKE UPPER(?) ]]></Parameter>
        <Parameter name="warehouse" optional="true" after="WHERE T.M_PRODUCT_ID = P.M_PRODUCT_ID "><![CDATA[ AND W.M_Warehouse_ID = TO_NUMBER(?) ]]></Parameter>
        <Parameter name="adRoleId"/>
        <Parameter name="cBpartnerId"/>
        <Parameter name="adUserClient" type="replace" optional="true" after="W.AD_CLIENT_ID IN (" text="'1'"/>  
        <Parameter name="orderBy" type="replace" optional="true" after="ORDER BY " text="P.VALUE" />
        <Parameter name="oraLimit" type="argument" optional="true" after="WHERE 1=1"><![CDATA[AND RN1 BETWEEN ]]></Parameter>
        <Parameter name="pgLimit" type="argument" optional="true" after="WHERE 1=1"><![CDATA[LIMIT ]]></Parameter>    
   </SqlMethod>

   <SqlMethod name="selectNotStored" type="preparedStatement" return="multiple">
      <SqlMethodComment></SqlMethodComment>
      <Sql> <![CDATA[
      SELECT * FROM ( SELECT '0' AS RN1, A.* FROM ( 
        SELECT P.M_PRODUCT_ID, P.DISCONTINUED, P.VALUE, P.NAME, P.NAME AS NAME_HIDDEN, 0 AS QTY, U1.NAME AS C_UOM1, 
        0 AS QTYORDER, NULL AS C_UOM2, A.DESCRIPTION AS ATTRIBUTE, 
        NULL AS LOCATOR, 
        P.M_ATTRIBUTESETINSTANCE_ID, NULL AS M_LOCATOR_ID, 
        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,
        P.M_PRODUCT_ID || '#' || p.name || '#' || NULL || '#' || P.M_ATTRIBUTESETINSTANCE_ID || '#' || 
          0 || '#' || NULL || '#' || 0 || '#' || P.C_UOM_ID as rowkey
        FROM M_PRODUCT P left join M_ATTRIBUTESETINSTANCE A  on P.M_ATTRIBUTESETINSTANCE_ID = A.M_ATTRIBUTESETINSTANCE_ID, 
             C_UOM U1
        WHERE P.C_UOM_ID = U1.C_UOM_ID 
        AND (P.C_BPARTNER_ID IS NULL OR C_BPARTNER_ID = COALESCE(to_number(?), C_BPARTNER_ID)) 
        AND P.AD_CLIENT_ID IN ('1') 
        AND P.AD_ORG_ID IN ('1') 
        AND P.ISACTIVE = 'Y'
        ORDER BY P.VALUE
        ) A ) B
      WHERE 1=1   
      ]]></Sql>
        <Field name="position" value="count"/>
        <Parameter name="rownum" type="replace" optional="true" after="FROM ( SELECT " text="'0'" />
        <Parameter name="key" optional="true" after="WHERE "><![CDATA[ UPPER(P.Value) LIKE UPPER(?) AND ]]></Parameter>
        <Parameter name="name" optional="true" after="WHERE "><![CDATA[ UPPER(P.Name) LIKE UPPER(?) AND ]]></Parameter>
        <Parameter name="cBpartnerId"/>
        <Parameter name="adUserClient" type="replace" optional="true" after="P.AD_CLIENT_ID IN (" text="'1'"/>
        <Parameter name="adUserOrg" type="replace" optional="true" after="P.AD_ORG_ID IN (" text="'1'"/>
        <Parameter name="orderBy" type="replace" optional="true" after="ORDER BY " text="P.VALUE" />
        <Parameter name="oraLimit" type="argument" optional="true" after="WHERE 1=1"><![CDATA[AND RN1 BETWEEN ]]></Parameter>
        <Parameter name="pgLimit" type="argument" optional="true" after="WHERE 1=1"><![CDATA[LIMIT ]]></Parameter>
   </SqlMethod>
   <SqlMethod name="selectNotStoredtrl" type="preparedStatement" return="multiple">
      <SqlMethodComment></SqlMethodComment>
      <Sql> <![CDATA[
      SELECT * FROM ( SELECT '0' AS RN1, A.* FROM ( 
        SELECT P.M_PRODUCT_ID, P.DISCONTINUED, P.VALUE, P.NAME, P.NAME AS NAME_HIDDEN, 0 AS QTY, U1TRL.NAME AS C_UOM1, 
        0 AS QTYORDER, NULL AS C_UOM2, A.DESCRIPTION AS ATTRIBUTE, 
        NULL AS LOCATOR, 
        P.M_ATTRIBUTESETINSTANCE_ID, NULL AS M_LOCATOR_ID, 
        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,
        P.M_PRODUCT_ID || '#' || p.name || '#' || NULL || '#' || P.M_ATTRIBUTESETINSTANCE_ID || '#' || 
          0 || '#' || NULL || '#' || 0 || '#' || P.C_UOM_ID as rowkey
        FROM M_PRODUCT P left join M_ATTRIBUTESETINSTANCE A on P.M_ATTRIBUTESETINSTANCE_ID = A.M_ATTRIBUTESETINSTANCE_ID,
             C_UOM U1    left join C_UOM_TRL U1TRL on U1TRL.C_UOM_ID= U1.C_UOM_ID 
                                                  AND U1TRL.AD_LANGUAGE= ?
        WHERE P.C_UOM_ID = U1.C_UOM_ID 
        AND (P.C_BPARTNER_ID IS NULL OR C_BPARTNER_ID = COALESCE(to_number(?), C_BPARTNER_ID)) 
        AND P.AD_CLIENT_ID IN ('1') 
        AND P.AD_ORG_ID IN ('1') 
        AND P.ISACTIVE = 'Y'
        ORDER BY P.VALUE
        ) A ) B
      WHERE 1=1 
      ]]></Sql>
        <Field name="position" value="count"/>
        <Parameter name="rownum" type="replace" optional="true" after="FROM ( SELECT " text="'0'" />
        <Parameter name="adLanguage"/>
        <Parameter name="key" optional="true" after="WHERE "><![CDATA[ UPPER(P.Value) LIKE UPPER(?) AND ]]></Parameter>
        <Parameter name="name" optional="true" after="WHERE "><![CDATA[ UPPER(P.Name) LIKE UPPER(?) AND ]]></Parameter>
        <Parameter name="cBpartnerId"/>
        <Parameter name="adUserClient" type="replace" optional="true" after="P.AD_CLIENT_ID IN (" text="'1'"/>
        <Parameter name="adUserOrg" type="replace" optional="true" after="P.AD_ORG_ID IN (" text="'1'"/> 
        <Parameter name="orderBy" type="replace" optional="true" after="ORDER BY " text="P.VALUE" />
        <Parameter name="oraLimit" type="argument" optional="true" after="WHERE 1=1"><![CDATA[AND RN1 BETWEEN ]]></Parameter>
        <Parameter name="pgLimit" type="argument" optional="true" after="WHERE 1=1"><![CDATA[LIMIT ]]></Parameter>       
   </SqlMethod>
   <SqlMethod name="existsActualValue" type="preparedStatement" return="String" default="">
      <SqlMethodComment></SqlMethodComment>
      <Sql> <![CDATA[
        SELECT value as value
        FROM M_Product 
        WHERE UPPER(AD_COLUMN_IDENTIFIER(TO_CHAR('M_Product'), TO_CHAR(M_Product_ID), TO_CHAR(?))) = UPPER(?)
        AND M_Product_ID = TO_NUMBER(?) 
        ORDER BY value
      ]]></Sql>
        <Parameter name="language"/>
        <Parameter name="description"/>
        <Parameter name="cBpartnerId"/>
   </SqlMethod>
</SqlClass>