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
carlos@616
     1
<?xml version="1.0" encoding="UTF-8" ?>
carlos@616
     2
<!--
carlos@616
     3
 *************************************************************************
carlos@616
     4
 * The contents of this file are subject to the Openbravo  Public  License
carlos@616
     5
 * Version  1.0  (the  "License"),  being   the  Mozilla   Public  License
carlos@616
     6
 * Version 1.1  with a permitted attribution clause; you may not  use this
carlos@616
     7
 * file except in compliance with the License. You  may  obtain  a copy of
carlos@616
     8
 * the License at http://www.openbravo.com/legal/license.html 
carlos@616
     9
 * Software distributed under the License  is  distributed  on  an "AS IS"
carlos@616
    10
 * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
carlos@616
    11
 * License for the specific  language  governing  rights  and  limitations
carlos@616
    12
 * under the License. 
carlos@616
    13
 * The Original Code is Openbravo ERP. 
carlos@616
    14
 * The Initial Developer of the Original Code is Openbravo SL 
carlos@616
    15
 * All portions are Copyright (C) 2001-2008 Openbravo SL 
carlos@616
    16
 * All Rights Reserved. 
carlos@616
    17
 * Contributor(s):  ______________________________________.
carlos@616
    18
 ************************************************************************
carlos@616
    19
-->
carlos@616
    20
carlos@616
    21
carlos@616
    22
carlos@616
    23
carlos@616
    24
carlos@616
    25
<SqlClass name="ReportParetoProductData" package="org.openbravo.erpCommon.ad_reports">
carlos@616
    26
  <SqlClassComment></SqlClassComment>
carlos@616
    27
  <SqlMethod name="select" type="preparedStatement" return="multiple">
carlos@616
    28
    <SqlMethodComment></SqlMethodComment>
carlos@616
    29
    <Sql>
carlos@616
    30
    <![CDATA[
carlos@629
    31
     SELECT ORGID, SEARCHKEY, NAME, PORCENTAJE, QTY, UNIT, COST, QTY*COST AS VALUE,
carlos@629
    32
     GET_PARETO_ABC(TO_NUMBER(?), TO_NUMBER(?), PORCENTAJE) AS ISABC,
carlos@629
    33
     '' AS PADRE, '' AS ID
carlos@629
    34
     FROM  
carlos@629
    35
     (
carlos@629
    36
     SELECT 
carlos@629
    37
      SUM(MSD.QTYONHAND) AS QTY,
carlos@616
    38
      AD_ORG.NAME AS ORGID,
carlos@629
    39
      PR.VALUE AS SEARCHKEY,
carlos@629
    40
      PR.NAME,
carlos@629
    41
      GET_PRODUCT_COST(PR.M_PRODUCT_ID,to_date(now()),'AV') AS COST, 
carlos@629
    42
      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')) 
carlos@629
    43
                                                           from M_WAREHOUSE MW1  
carlos@629
    44
                                                           LEFT JOIN M_LOCATOR ML1 ON ML1.M_WAREHOUSE_ID=MW1.M_WAREHOUSE_ID
carlos@629
    45
                                                           LEFT JOIN M_STORAGE_DETAIL MSD1 ON ML1.M_LOCATOR_ID=MSD1.M_LOCATOR_ID
carlos@629
    46
                                                           LEFT JOIN M_PRODUCT PR1 ON MSD1.M_PRODUCT_ID=PR1.M_PRODUCT_ID   
carlos@616
    47
                                                           WHERE MSD1.QTYONHAND>0
carlos@629
    48
                                                           AND 1=1
carlos@616
    49
                                                           AND 2=2 
carlos@629
    50
                                                           ) ) as PORCENTAJE,
carlos@629
    51
      PR.M_PRODUCT_ID,
carlos@629
    52
      MW.AD_ORG_ID,
carlos@629
    53
      C_UOM.NAME AS UNIT
carlos@629
    54
     FROM
carlos@629
    55
     M_WAREHOUSE MW 
carlos@629
    56
       LEFT JOIN M_LOCATOR ML ON ML.M_WAREHOUSE_ID=MW.M_WAREHOUSE_ID
carlos@629
    57
       LEFT JOIN M_STORAGE_DETAIL MSD ON ML.M_LOCATOR_ID=MSD.M_LOCATOR_ID
carlos@629
    58
       LEFT JOIN M_PRODUCT PR ON MSD.M_PRODUCT_ID=PR.M_PRODUCT_ID, 
carlos@629
    59
       AD_ORG, C_UOM   
carlos@629
    60
     WHERE 3=3
carlos@629
    61
       AND 4=4
carlos@629
    62
       AND MSD.QTYONHAND>0
carlos@629
    63
       AND GET_PRODUCT_COST(PR.M_PRODUCT_ID,to_date(now()),'AV') IS NOT NULL
carlos@616
    64
       AND GET_PRODUCT_COST(PR.M_PRODUCT_ID,to_date(now()),'AV') <> 0
carlos@629
    65
       AND MW.AD_ORG_ID = AD_ORG.AD_ORG_ID
carlos@629
    66
       AND PR.C_UOM_ID = C_UOM.C_UOM_ID
carlos@629
    67
     GROUP BY AD_ORG.NAME ,PR.VALUE, PR.NAME, PR.M_PRODUCT_ID, MW.AD_ORG_ID, C_UOM.NAME
carlos@629
    68
     ORDER BY ORGID ASC, PORCENTAJE DESC       
carlos@629
    69
     ) BBB
carlos@616
    70
    ]]></Sql>
carlos@616
    71
    <Field name="rownum" value="count"/>
carlos@629
    72
    <Parameter name="mWarehouseId"/>
carlos@629
    73
    <Parameter name="adOrgId"/>    
carlos@616
    74
    <Parameter name="mWarehouseId" optional="true" after="1=1" text=" AND MW1.M_WAREHOUSE_ID = TO_NUMBER(?) "/>
carlos@629
    75
    <Parameter name="adOrgId" optional="true" after="2=2" text=" AND MW1.AD_ORG_ID = TO_NUMBER(?) "/>    
carlos@616
    76
    <Parameter name="mWarehouseId" optional="true" after="3=3" text=" AND MW.M_WAREHOUSE_ID = TO_NUMBER(?) "/>    
carlos@629
    77
    <Parameter name="adOrgId" optional="true" after="4=4" text=" AND MW.AD_ORG_ID = TO_NUMBER(?) "/>
carlos@616
    78
  </SqlMethod>
carlos@616
    79
carlos@616
    80
  <SqlMethod name="set" type="constant" return="multiple">
carlos@616
    81
      <SqlMethodComment></SqlMethodComment>
carlos@616
    82
      <Sql></Sql>
carlos@616
    83
  </SqlMethod>
carlos@616
    84
carlos@629
    85
  <SqlMethod name="selectWarehouseDouble" type="preparedStatement" return="Multiple">
carlos@629
    86
    <SqlMethodComment></SqlMethodComment>
carlos@629
    87
    <Sql>
carlos@629
    88
      <![CDATA[
carlos@629
    89
        SELECT M_WAREHOUSE.AD_ORG_ID AS PADRE, M_WAREHOUSE.M_WAREHOUSE_ID AS ID, TO_CHAR(M_WAREHOUSE.NAME) AS NAME
carlos@629
    90
        FROM M_WAREHOUSE
carlos@629
    91
        WHERE 1=1
carlos@629
    92
         AND M_WAREHOUSE.AD_Client_ID IN('1')
carlos@629
    93
         UNION 
carlos@629
    94
        SELECT null AS PADRE, M_WAREHOUSE.M_WAREHOUSE_ID AS ID, TO_CHAR(M_WAREHOUSE.NAME) AS NAME
carlos@629
    95
        FROM M_WAREHOUSE
carlos@629
    96
        WHERE 2=2 AND M_WAREHOUSE.AD_Client_ID IN('1')    
carlos@629
    97
        ORDER BY PADRE, NAME
carlos@629
    98
      ]]></Sql>
carlos@629
    99
    <Parameter name="adUserClient" type="replace" optional="true" after="M_WAREHOUSE.AD_Client_ID IN(" text="'1'"/>
carlos@629
   100
    <Parameter name="adUserClient" type="replace" optional="true" after=" 2=2 AND M_WAREHOUSE.AD_Client_ID IN(" text="'1'"/>    
carlos@629
   101
  </SqlMethod> 
carlos@629
   102
    
carlos@616
   103
  <SqlMethod name="mUpdateParetoProduct0" type="callableStatement" return="object" object="ReportParetoProductData">
carlos@616
   104
      <SqlMethodComment></SqlMethodComment>
carlos@616
   105
      <Sql><![CDATA[
carlos@616
   106
        CALL M_UPDATE_PARETO_PRODUCT0(TO_NUMBER(?))
carlos@616
   107
      ]]></Sql>
carlos@616
   108
      <Parameter name="adPinstanceId"/>    
carlos@616
   109
   </SqlMethod> 
carlos@629
   110
          
carlos@616
   111
</SqlClass>