src/org/openbravo/erpCommon/ad_reports/ReportValuationStock_data.xsql
author Rafa Roda Palacios <rafa.roda@ubiquads.com>
Tue, 10 Jun 2014 21:31:49 +0200
changeset 23476 e861ed85d496
parent 19649 c2faeaac0dbd
child 24909 f63ce24fa159
permissions -rw-r--r--
Fixes issue 26823 Cost Date of M_TRANSACTION_COST removed when filtering Stock Valuation Report
carlos@0
     1
<?xml version="1.0" encoding="UTF-8" ?>
carlos@0
     2
<!--
carlos@0
     3
 *************************************************************************
carlos@0
     4
 * The contents of this file are subject to the Openbravo  Public  License
priya@9085
     5
 * Version  1.1  (the  "License"),  being   the  Mozilla   Public  License
carlos@0
     6
 * Version 1.1  with a permitted attribution clause; you may not  use this
carlos@0
     7
 * file except in compliance with the License. You  may  obtain  a copy of
carlos@0
     8
 * the License at http://www.openbravo.com/legal/license.html 
carlos@0
     9
 * Software distributed under the License  is  distributed  on  an "AS IS"
carlos@0
    10
 * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
carlos@0
    11
 * License for the specific  language  governing  rights  and  limitations
carlos@0
    12
 * under the License. 
carlos@0
    13
 * The Original Code is Openbravo ERP. 
ggi@6702
    14
 * The Initial Developer of the Original Code is Openbravo SLU 
rafa@23476
    15
 * All portions are Copyright (C) 2001-2014 Openbravo SLU 
carlos@0
    16
 * All Rights Reserved. 
carlos@0
    17
 * Contributor(s):  ______________________________________.
carlos@0
    18
 ************************************************************************
carlos@0
    19
-->
carlos@0
    20
carlos@0
    21
carlos@0
    22
carlos@0
    23
carlos@0
    24
carlos@0
    25
<SqlClass name="ReportValuationStockData" package="org.openbravo.erpCommon.ad_reports">
carlos@0
    26
  <SqlClassComment></SqlClassComment>
carlos@0
    27
  <SqlMethod name="select" type="preparedStatement" return="multiple">
carlos@0
    28
    <SqlMethodComment></SqlMethodComment>
carlos@0
    29
    <Sql>
carlos@0
    30
    <![CDATA[
gorkaion@17470
    31
        SELECT CATEGORY_NAME, M_PRODUCT_ID, PRODUCT_NAME, SUM(QTY) AS QTY, UOM_NAME, CASE ISCOSTCALCULATED
gorkaion@17470
    32
                       WHEN 'Y' THEN SUM(TOTAL_COST) / SUM(QTY)
gorkaion@17470
    33
                       ELSE NULL
gorkaion@17470
    34
                     END AS AVERAGE_COST,
gorkaion@17470
    35
               SUM(TOTAL_COST) AS TOTAL_COST
gorkaion@17470
    36
        FROM (SELECT M_PRODUCT_CATEGORY.NAME AS CATEGORY_NAME, A.M_PRODUCT_ID, AD_COLUMN_IDENTIFIER (to_char('M_Product'),to_char(M_PRODUCT.M_PRODUCT_ID),to_char(?)) AS PRODUCT_NAME, 
gorkaion@17470
    37
              A.QTY, C_UOM.NAME AS UOM_NAME,
gorkaion@17470
    38
                     CASE a.iscostcalculated
gorkaion@17470
    39
                       WHEN 'Y' THEN C_CURRENCY_CONVERT_PRECISION (Suma,A.c_currency_id,?,A.movementdate,NULL,A.AD_CLIENT_ID,?,'C')
gorkaion@17470
    40
                       ELSE NULL
gorkaion@17470
    41
                     END AS TOTAL_COST,  A.ISCOSTCALCULATED, A.AD_CLIENT_ID, A.MOVEMENTDATE, A.C_CURRENCY_ID
gorkaion@17470
    42
              FROM M_PRODUCT_CATEGORY,
gorkaion@17470
    43
                   (SELECT trx.M_PRODUCT_ID, sum(trx.MOVEMENTQTY) AS QTY, sum(CASE WHEN trx.MOVEMENTQTY < 0 THEN- tc.trxcost ELSE tc.trxcost END) AS Suma, 
gorkaion@17470
    44
                   trx.C_UOM_ID, trx.AD_CLIENT_ID, trx.iscostcalculated, tc.c_currency_id, coalesce(io.dateacct,trx.movementdate) as movementdate
gorkaion@17470
    45
                    FROM M_TRANSACTION trx 
gorkaion@17470
    46
                      JOIN M_LOCATOR l ON trx.M_LOCATOR_ID = l.M_LOCATOR_ID 
gorkaion@17470
    47
                      LEFT JOIN M_INOUTLINE iol ON trx.M_INOUTLINE_ID = iol.M_INOUTLINE_ID 
gorkaion@17470
    48
                      LEFT JOIN M_INOUT io ON iol.M_INOUT_ID = io.M_INOUT_ID 
gorkaion@17470
    49
                      LEFT JOIN (SELECT sum(cost) AS trxcost, m_transaction_id, c_currency_id
gorkaion@17470
    50
                                 FROM M_TRANSACTION_COST
gorkaion@17470
    51
                                 GROUP BY m_transaction_id, c_currency_id) tc ON trx.m_transaction_id = tc.m_transaction_id
gorkaion@17470
    52
                    WHERE trx.MOVEMENTDATE < to_date(?)
gorkaion@17470
    53
              AND l.M_WAREHOUSE_ID = ?
gorkaion@17470
    54
                    GROUP BY trx.M_PRODUCT_ID, trx.C_UOM_ID, trx.AD_CLIENT_ID, trx.iscostcalculated, tc.c_currency_id, coalesce(io.dateacct,trx.movementdate)) A,
gorkaion@17470
    55
                   C_UOM,
gorkaion@17470
    56
                   M_PRODUCT
gorkaion@17470
    57
              WHERE A.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID
gorkaion@17470
    58
              AND   A.C_UOM_ID = C_UOM.C_UOM_ID
gorkaion@17470
    59
              AND   M_PRODUCT.M_PRODUCT_CATEGORY_ID = M_PRODUCT_CATEGORY.M_PRODUCT_CATEGORY_ID
gorkaion@17470
    60
              AND   1 = 1
javier@19649
    61
              AND   (A.QTY <> 0 OR A.Suma <> 0)) ZZ
gorkaion@17470
    62
        GROUP BY ZZ.M_PRODUCT_ID, CATEGORY_NAME, PRODUCT_NAME, UOM_NAME, ISCOSTCALCULATED
gorkaion@17470
    63
        HAVING SUM(QTY) <>0
gorkaion@17470
    64
        ORDER BY CATEGORY_NAME, PRODUCT_NAME      
carlos@0
    65
     ]]></Sql>
carlos@0
    66
      <Field name="rownum" value="count"/>
rafael@1649
    67
      <Parameter name="adLanguage"/>
rafael@1868
    68
      <Parameter name="cCurrencyConv"/>
gorkaion@17415
    69
      <Parameter name="legalEntity"/>
gorkaion@17415
    70
      <Parameter name="datePlus"/>
carlos@0
    71
      <Parameter name="warehouse"/>
gorkaion@17470
    72
      <Parameter name="categoryProduct" optional="true" after="AND   1 = 1"><![CDATA[ AND M_PRODUCT.M_PRODUCT_CATEGORY_ID= ? ]]></Parameter>
carlos@0
    73
  </SqlMethod>
carlos@0
    74
  <SqlMethod name="set" type="constant" return="multiple">
carlos@0
    75
      <SqlMethodComment></SqlMethodComment>
carlos@0
    76
      <Sql></Sql>
carlos@0
    77
  </SqlMethod>
carlos@0
    78
</SqlClass>