src/org/openbravo/erpCommon/ad_reports/ReportInventory_data.xsql
author David Baz Fayos <david.baz@openbravo.com>
Tue, 27 May 2008 15:26:10 +0000
changeset 1044 8691bbc94032
parent 423 ecf368072c48
child 1605 8a0fe0193bef
permissions -rw-r--r--
Removed old frame parameter of windowTableId
<?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-2006 Openbravo SL 
 * All Rights Reserved. 
 * Contributor(s):  ______________________________________.
 ************************************************************************
-->





<SqlClass name="ReportInventoryData" package="org.openbravo.erpCommon.ad_reports">
  <SqlClassComment></SqlClassComment>
  <SqlMethod name="select" type="preparedStatement" return="multiple">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
    <![CDATA[
      SELECT T.M_LOCATOR_ID, M.VALUE AS LOCATOR, T.M_PRODUCT_ID, P.NAME AS PRODUCT, 
      ROUND(SUM(T.QTYONHAND), 2) AS PRODUCT_QTY, T.C_UOM_ID, C1.NAME AS PRODUCT_UOM, 
      T.M_PRODUCT_UOM_ID, C2.NAME AS ORDER_UOM, 
      ROUND(SUM(T.QTYORDERONHAND), 2) AS ORDER_QTY, 
      ATT.DESCRIPTION AS LOT
      FROM M_STORAGE_DETAIL T left join M_ATTRIBUTESETINSTANCE ATT on T.M_ATTRIBUTESETINSTANCE_ID = ATT.M_ATTRIBUTESETINSTANCE_ID 
                              right join M_PRODUCT_UOM P_UOM        on T.M_PRODUCT_UOM_ID = P_UOM.M_PRODUCT_UOM_ID 
                              left join C_UOM C2                   on P_UOM.C_UOM_ID = C2.C_UOM_ID,
           M_LOCATOR M, M_PRODUCT P,  C_UOM C1
      WHERE T.M_PRODUCT_ID = TO_NUMBER(?) 
      AND T.M_PRODUCT_ID = P.M_PRODUCT_ID 
      AND T.M_LOCATOR_ID = M.M_LOCATOR_ID 
      AND T.C_UOM_ID = C1.C_UOM_ID 
      AND COALESCE(C1.C_UOM_ID,0) = COALESCE(to_number(?), 0)
      AND T.M_LOCATOR_ID = TO_NUMBER(?) 
      AND T.AD_CLIENT_ID IN ('1') 
      AND T.AD_ORG_ID IN ('2') 
      AND 1=1
      AND ROUND(T.QTYONHAND, 2) <> 0 
      GROUP BY T.M_LOCATOR_ID, M.VALUE, T.M_PRODUCT_ID, P.NAME, T.C_UOM_ID, C1.NAME, T.M_PRODUCT_UOM_ID, C2.NAME, ATT.DESCRIPTION
      ORDER BY M.VALUE, P.NAME , ATT.DESCRIPTION 
      ]]></Sql>
      <Parameter name="mProductId"/>
      <Parameter name="mProductUom"/>
      <Parameter name="mLocatorId"/>
      <Parameter name="adClientId" optional="true" type="replace" after="AND T.AD_CLIENT_ID IN (" text="'1'"/>
      <Parameter name="adOrgId" optional="true" type="replace" after="AND T.AD_ORG_ID IN (" text="'2'"/>
  </SqlMethod>
  <SqlMethod name="selectSinDetalle" type="preparedStatement" return="multiple">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
    <![CDATA[
      SELECT  T.M_LOCATOR_ID, M.VALUE AS LOCATOR, T.M_PRODUCT_ID, P.NAME AS PRODUCT, 
      ROUND(SUM(T.QTYONHAND), 2) AS PRODUCT_QTY, T.C_UOM_ID, C1.NAME AS PRODUCT_UOM, 
      T.M_PRODUCT_UOM_ID, C2.NAME AS ORDER_UOM, 
      ROUND(SUM(T.QTYORDERONHAND), 2) AS ORDER_QTY 
      FROM M_PRODUCT_UOM P_UOM left join C_UOM C2 on P_UOM.C_UOM_ID = C2.C_UOM_ID 
                               right join M_STORAGE_DETAIL T on T.M_PRODUCT_UOM_ID = P_UOM.M_PRODUCT_UOM_ID,
           M_LOCATOR M, M_WAREHOUSE MW, M_PRODUCT P,  C_UOM C1
      WHERE T.M_PRODUCT_ID = P.M_PRODUCT_ID 
      AND T.M_LOCATOR_ID = M.M_LOCATOR_ID 
      AND MW.M_WAREHOUSE_ID = M.M_WAREHOUSE_ID
      AND T.C_UOM_ID = C1.C_UOM_ID 
      AND T.AD_CLIENT_ID IN ('1') 
      AND T.AD_ORG_ID IN ('2') 
      AND (ROUND(T.QTYONHAND, 2) <> 0 OR ROUND(T.QTYORDERONHAND, 2) <> 0)
      GROUP BY T.M_LOCATOR_ID, M.VALUE, T.M_PRODUCT_ID, P.NAME, T.C_UOM_ID, C1.NAME, T.M_PRODUCT_UOM_ID, C2.NAME
      ORDER BY LOCATOR, PRODUCT
      ]]></Sql>
      <Parameter name="mWarehouseId" optional="true" after="WHERE " text=" MW.M_WAREHOUSE_ID = TO_NUMBER(?) AND "/>
      <Parameter name="adClientId" optional="true" type="replace" after="AND T.AD_CLIENT_ID IN (" text="'1'"/>
      <Parameter name="adOrgId" optional="true" type="replace" after="AND T.AD_ORG_ID IN (" text="'2'"/>
  </SqlMethod>
  <SqlMethod name="set" type="constant" return="multiple">
      <SqlMethodComment></SqlMethodComment>
      <Sql></Sql>
  </SqlMethod>
  <SqlMethod name="warehouseDescription" type="preparedStatement" return="String" default="">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
    SELECT VALUE FROM M_WAREHOUSE
    WHERE M_WAREHOUSE_ID = TO_NUMBER(?)
    </Sql>
    <Parameter name="mLocatorId"/>
  </SqlMethod>
</SqlClass>