src/org/openbravo/erpCommon/ad_reports/ReportParetoProduct_data.xsql
author David Baz Fayos <david.baz@openbravo.com>
Tue, 27 May 2008 15:26:10 +0000
changeset 1044 8691bbc94032
parent 629 84ded58ee77f
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-2008 Openbravo SL 
 * All Rights Reserved. 
 * Contributor(s):  ______________________________________.
 ************************************************************************
-->





<SqlClass name="ReportParetoProductData" package="org.openbravo.erpCommon.ad_reports">
  <SqlClassComment></SqlClassComment>
  <SqlMethod name="select" type="preparedStatement" return="multiple">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
    <![CDATA[
     SELECT ORGID, SEARCHKEY, NAME, PORCENTAJE, QTY, UNIT, COST, QTY*COST AS VALUE,
     GET_PARETO_ABC(TO_NUMBER(?), TO_NUMBER(?), PORCENTAJE) AS ISABC,
     '' AS PADRE, '' AS ID
     FROM  
     (
     SELECT 
      SUM(MSD.QTYONHAND) AS QTY,
      AD_ORG.NAME AS ORGID,
      PR.VALUE AS SEARCHKEY,
      PR.NAME,
      GET_PRODUCT_COST(PR.M_PRODUCT_ID,to_date(now()),'AV') AS COST, 
      100*(GET_PRODUCT_COST(PR.M_PRODUCT_ID,to_date(now()),'AV')/ (SELECT SUM(GET_PRODUCT_COST(PR1.M_PRODUCT_ID,to_date(now()),'AV')) 
                                                           from M_WAREHOUSE MW1  
                                                           LEFT JOIN M_LOCATOR ML1 ON ML1.M_WAREHOUSE_ID=MW1.M_WAREHOUSE_ID
                                                           LEFT JOIN M_STORAGE_DETAIL MSD1 ON ML1.M_LOCATOR_ID=MSD1.M_LOCATOR_ID
                                                           LEFT JOIN M_PRODUCT PR1 ON MSD1.M_PRODUCT_ID=PR1.M_PRODUCT_ID   
                                                           WHERE MSD1.QTYONHAND>0
                                                           AND 1=1
                                                           AND 2=2 
                                                           ) ) as PORCENTAJE,
      PR.M_PRODUCT_ID,
      MW.AD_ORG_ID,
      C_UOM.NAME AS UNIT
     FROM
     M_WAREHOUSE MW 
       LEFT JOIN M_LOCATOR ML ON ML.M_WAREHOUSE_ID=MW.M_WAREHOUSE_ID
       LEFT JOIN M_STORAGE_DETAIL MSD ON ML.M_LOCATOR_ID=MSD.M_LOCATOR_ID
       LEFT JOIN M_PRODUCT PR ON MSD.M_PRODUCT_ID=PR.M_PRODUCT_ID, 
       AD_ORG, C_UOM   
     WHERE 3=3
       AND 4=4
       AND MSD.QTYONHAND>0
       AND GET_PRODUCT_COST(PR.M_PRODUCT_ID,to_date(now()),'AV') IS NOT NULL
       AND GET_PRODUCT_COST(PR.M_PRODUCT_ID,to_date(now()),'AV') <> 0
       AND MW.AD_ORG_ID = AD_ORG.AD_ORG_ID
       AND PR.C_UOM_ID = C_UOM.C_UOM_ID
     GROUP BY AD_ORG.NAME ,PR.VALUE, PR.NAME, PR.M_PRODUCT_ID, MW.AD_ORG_ID, C_UOM.NAME
     ORDER BY ORGID ASC, PORCENTAJE DESC       
     ) BBB
    ]]></Sql>
    <Field name="rownum" value="count"/>
    <Parameter name="mWarehouseId"/>
    <Parameter name="adOrgId"/>    
    <Parameter name="mWarehouseId" optional="true" after="1=1" text=" AND MW1.M_WAREHOUSE_ID = TO_NUMBER(?) "/>
    <Parameter name="adOrgId" optional="true" after="2=2" text=" AND MW1.AD_ORG_ID = TO_NUMBER(?) "/>    
    <Parameter name="mWarehouseId" optional="true" after="3=3" text=" AND MW.M_WAREHOUSE_ID = TO_NUMBER(?) "/>    
    <Parameter name="adOrgId" optional="true" after="4=4" text=" AND MW.AD_ORG_ID = TO_NUMBER(?) "/>
  </SqlMethod>

  <SqlMethod name="set" type="constant" return="multiple">
      <SqlMethodComment></SqlMethodComment>
      <Sql></Sql>
  </SqlMethod>

  <SqlMethod name="selectWarehouseDouble" type="preparedStatement" return="Multiple">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
      <![CDATA[
        SELECT M_WAREHOUSE.AD_ORG_ID AS PADRE, M_WAREHOUSE.M_WAREHOUSE_ID AS ID, TO_CHAR(M_WAREHOUSE.NAME) AS NAME
        FROM M_WAREHOUSE
        WHERE 1=1
         AND M_WAREHOUSE.AD_Client_ID IN('1')
         UNION 
        SELECT null AS PADRE, M_WAREHOUSE.M_WAREHOUSE_ID AS ID, TO_CHAR(M_WAREHOUSE.NAME) AS NAME
        FROM M_WAREHOUSE
        WHERE 2=2 AND M_WAREHOUSE.AD_Client_ID IN('1')    
        ORDER BY PADRE, NAME
      ]]></Sql>
    <Parameter name="adUserClient" type="replace" optional="true" after="M_WAREHOUSE.AD_Client_ID IN(" text="'1'"/>
    <Parameter name="adUserClient" type="replace" optional="true" after=" 2=2 AND M_WAREHOUSE.AD_Client_ID IN(" text="'1'"/>    
  </SqlMethod> 
    
  <SqlMethod name="mUpdateParetoProduct0" type="callableStatement" return="object" object="ReportParetoProductData">
      <SqlMethodComment></SqlMethodComment>
      <Sql><![CDATA[
        CALL M_UPDATE_PARETO_PRODUCT0(TO_NUMBER(?))
      ]]></Sql>
      <Parameter name="adPinstanceId"/>    
   </SqlMethod> 
          
</SqlClass>