src/org/openbravo/erpCommon/ad_reports/ReportProductMovement_data.xsql
changeset 0 0247c26f10c6
child 423 ecf368072c48
equal deleted inserted replaced
-1:000000000000 0:0247c26f10c6
       
     1 <?xml version="1.0" encoding="UTF-8" ?>
       
     2 <!--
       
     3  *************************************************************************
       
     4  * The contents of this file are subject to the Openbravo  Public  License
       
     5  * Version  1.0  (the  "License"),  being   the  Mozilla   Public  License
       
     6  * Version 1.1  with a permitted attribution clause; you may not  use this
       
     7  * file except in compliance with the License. You  may  obtain  a copy of
       
     8  * the License at http://www.openbravo.com/legal/license.html 
       
     9  * Software distributed under the License  is  distributed  on  an "AS IS"
       
    10  * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
       
    11  * License for the specific  language  governing  rights  and  limitations
       
    12  * under the License. 
       
    13  * The Original Code is Openbravo ERP. 
       
    14  * The Initial Developer of the Original Code is Openbravo SL 
       
    15  * All portions are Copyright (C) 2001-2006 Openbravo SL 
       
    16  * All Rights Reserved. 
       
    17  * Contributor(s):  ______________________________________.
       
    18  ************************************************************************
       
    19 -->
       
    20 
       
    21 
       
    22 
       
    23 
       
    24 
       
    25 <SqlClass name="ReportProductMovementData" package="org.openbravo.erpCommon.ad_reports">
       
    26   <SqlClassComment></SqlClassComment>
       
    27   <SqlMethod name="select" type="preparedStatement" return="multiple">
       
    28     <SqlMethodComment></SqlMethodComment>
       
    29     <Sql>
       
    30     <![CDATA[
       
    31       SELECT M_PRODUCT.VALUE, M_PRODUCT.NAME, M_TRANSACTION.MOVEMENTDATE, C_BPARTNER.NAME AS PARTNERNAME,
       
    32       (CASE M_INOUT.ISSOTRX WHEN 'Y' THEN TO_CHAR(M_LOCATOR.X) ELSE '-' END) AS X_ORIGIN, 
       
    33       (CASE M_INOUT.ISSOTRX WHEN 'Y' THEN TO_CHAR(M_LOCATOR.Y) ELSE '-' END) AS Y_ORIGIN, 
       
    34       (CASE M_INOUT.ISSOTRX WHEN 'Y' THEN TO_CHAR(M_LOCATOR.Z) ELSE '-' END) AS Z_ORIGIN, 
       
    35       (CASE M_INOUT.ISSOTRX WHEN 'Y' THEN '-' ELSE TO_CHAR(M_LOCATOR.X) END) AS X_DESTINY, 
       
    36       (CASE M_INOUT.ISSOTRX WHEN 'Y' THEN '-' ELSE TO_CHAR(M_LOCATOR.Y) END) AS Y_DESTINY, 
       
    37       (CASE M_INOUT.ISSOTRX WHEN 'Y' THEN '-' ELSE TO_CHAR(M_LOCATOR.Z) END) AS Z_DESTINY,
       
    38       (CASE M_INOUT.ISSOTRX WHEN 'Y' THEN 'Si' ELSE 'No' END) AS OUT,
       
    39       (SUM(M_TRANSACTION.MOVEMENTQTY)||' '||C_UOM.NAME) AS MOVEMENTQTY, M_INOUT.ISSOTRX, M_INOUT.M_INOUT_ID,
       
    40       M_INOUT.DOCUMENTNO AS DOCUMENTNO, '' AS M_MOVEMENTLINE_ID, '' AS MOVEMENT, '' AS MOVEMENT_DESCRIPTION,
       
    41       '' AS M_INVENTORY_ID, '' AS M_MOVEMENT_ID, '' AS M_PRODUCTIONPLAN_ID, '' AS PRODUCTION_PLAN
       
    42       FROM M_PRODUCT left join C_BPARTNER on M_PRODUCT.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
       
    43                      right join M_TRANSACTION on M_TRANSACTION.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID,
       
    44            M_LOCATOR, M_INOUTLINE, M_INOUT, C_UOM
       
    45       WHERE M_TRANSACTION.M_LOCATOR_ID = M_LOCATOR.M_LOCATOR_ID
       
    46       AND M_TRANSACTION.M_INOUTLINE_ID = M_INOUTLINE.M_INOUTLINE_ID
       
    47       AND M_INOUTLINE.M_INOUT_ID = M_INOUT.M_INOUT_ID
       
    48       AND M_TRANSACTION.C_UOM_ID = C_UOM.C_UOM_ID
       
    49       AND M_TRANSACTION.M_INOUTLINE_ID IS NOT NULL 
       
    50       AND M_TRANSACTION.AD_CLIENT_ID IN ('1')
       
    51       AND M_TRANSACTION.AD_ORG_ID IN ('1')
       
    52       AND 1=1
       
    53       GROUP BY M_PRODUCT.VALUE, M_PRODUCT.NAME, M_TRANSACTION.MOVEMENTDATE, C_BPARTNER.NAME, M_LOCATOR.X, M_LOCATOR.Y, 
       
    54       M_LOCATOR.Z, M_INOUT.ISSOTRX, C_UOM.NAME, M_INOUT.M_INOUT_ID, M_INOUT.DOCUMENTNO
       
    55       ORDER BY C_BPARTNER.NAME, M_TRANSACTION.MOVEMENTDATE DESC
       
    56      ]]></Sql>
       
    57     <Field name="rownum" value="count"/>
       
    58     <Parameter name="adUserClient" type="replace" optional="true" after="AND M_TRANSACTION.AD_CLIENT_ID IN (" text="'1'"/>
       
    59     <Parameter name="adOrgClient" type="replace" optional="true" after="AND M_TRANSACTION.AD_ORG_ID IN (" text="'1'"/>
       
    60     <Parameter name="parDateFrom" optional="true" after="AND 1=1"><![CDATA[ AND M_TRANSACTION.MOVEMENTDATE >= to_date(?)]]></Parameter>
       
    61     <Parameter name="parDateTo" optional="true" after="AND 1=1"><![CDATA[ AND M_TRANSACTION.MOVEMENTDATE < to_date(?)]]></Parameter>
       
    62     <Parameter name="cBpartner" optional="true" after="AND 1=1"><![CDATA[ AND C_BPARTNER.C_BPARTNER_ID = ?]]></Parameter>
       
    63     <Parameter name="mProductId" optional="true" after="AND 1=1"><![CDATA[ AND M_PRODUCT.M_PRODUCT_ID = ?]]></Parameter>
       
    64   </SqlMethod>
       
    65 
       
    66   <SqlMethod name="selectInventory" type="preparedStatement" return="multiple">
       
    67     <SqlMethodComment></SqlMethodComment>
       
    68     <Sql>
       
    69     <![CDATA[
       
    70       SELECT M_PRODUCT.VALUE, M_PRODUCT.NAME, M_LOCATOR.X AS X_ORIGIN, M_LOCATOR.Y AS Y_ORIGIN, M_LOCATOR.Z AS Z_ORIGIN, C_BPARTNER.NAME AS PARTNERNAME, 
       
    71       (SUM(M_TRANSACTION.MOVEMENTQTY)||' '||C_UOM.NAME) AS MOVEMENTQTY, M_TRANSACTION.MOVEMENTDATE, 'No' AS OUT,
       
    72       M_INVENTORY.M_INVENTORY_ID, M_INVENTORY.NAME AS MOVEMENT, M_INVENTORY.NAME AS MOVEMENT_DESCRIPTION
       
    73       FROM M_PRODUCT left join C_BPARTNER on M_PRODUCT.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
       
    74                      right join M_TRANSACTION on M_TRANSACTION.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID,
       
    75           M_LOCATOR,  C_UOM, M_INVENTORYLINE, M_INVENTORY
       
    76       WHERE  M_TRANSACTION.M_LOCATOR_ID = M_LOCATOR.M_LOCATOR_ID
       
    77       AND M_TRANSACTION.C_UOM_ID = C_UOM.C_UOM_ID
       
    78       AND M_TRANSACTION.M_INVENTORYLINE_ID = M_INVENTORYLINE.M_INVENTORYLINE_ID
       
    79       AND M_INVENTORYLINE.M_INVENTORY_ID = M_INVENTORY.M_INVENTORY_ID
       
    80       AND M_TRANSACTION.M_INVENTORYLINE_ID IS NOT NULL
       
    81       AND M_TRANSACTION.AD_CLIENT_ID IN ('1')
       
    82       AND M_TRANSACTION.AD_ORG_ID IN ('1')
       
    83       AND 1=1
       
    84       GROUP BY M_PRODUCT.VALUE, M_PRODUCT.NAME, M_LOCATOR.X, M_LOCATOR.Y, M_LOCATOR.Z, C_BPARTNER.NAME, C_UOM.NAME, 
       
    85       M_TRANSACTION.MOVEMENTDATE, M_INVENTORY.M_INVENTORY_ID, M_INVENTORY.NAME
       
    86       HAVING SUM(M_TRANSACTION.MOVEMENTQTY) <> 0
       
    87       ORDER BY C_BPARTNER.NAME, M_TRANSACTION.MOVEMENTDATE DESC
       
    88      ]]></Sql>
       
    89     <Field name="rownum" value="count"/>
       
    90     <Parameter name="adUserClient" type="replace" optional="true" after="AND M_TRANSACTION.AD_CLIENT_ID IN (" text="'1'"/>
       
    91     <Parameter name="adOrgClient" type="replace" optional="true" after="AND M_TRANSACTION.AD_ORG_ID IN (" text="'1'"/>
       
    92     <Parameter name="parDateFrom" optional="true" after="AND 1=1"><![CDATA[ AND M_TRANSACTION.MOVEMENTDATE >= to_date(?)]]></Parameter>
       
    93     <Parameter name="parDateTo" optional="true" after="AND 1=1"><![CDATA[ AND M_TRANSACTION.MOVEMENTDATE < to_date(?)]]></Parameter>
       
    94     <Parameter name="cBpartner" optional="true" after="AND 1=1"><![CDATA[ AND C_BPARTNER.C_BPARTNER_ID = ?]]></Parameter>
       
    95     <Parameter name="mProductId" optional="true" after="AND 1=1"><![CDATA[ AND M_PRODUCT.M_PRODUCT_ID = ?]]></Parameter>
       
    96   </SqlMethod>
       
    97 
       
    98   <SqlMethod name="selectMovement" type="preparedStatement" return="multiple">
       
    99     <SqlMethodComment></SqlMethodComment>
       
   100     <Sql>
       
   101     <![CDATA[
       
   102         SELECT M_PRODUCT.VALUE, M_PRODUCT.NAME, M_LOCATOR.X AS X_ORIGIN, M_LOCATOR.Y AS Y_ORIGIN, M_LOCATOR.Z AS Z_ORIGIN, 
       
   103         M_L.X AS X_DESTINY, M_L.Y AS Y_DESTINY, M_L.Z AS Z_DESTINY, C_BPARTNER.NAME AS PARTNERNAME, 
       
   104         (M_TRANSACTION.MOVEMENTQTY||' '||C_UOM.NAME) AS MOVEMENTQTY, M_TRANSACTION.MOVEMENTDATE, 'No' AS OUT,
       
   105         M_MOVEMENTLINE.M_MOVEMENT_ID, M_MOVEMENT.NAME AS MOVEMENT
       
   106         FROM  M_PRODUCT left join C_BPARTNER on M_PRODUCT.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
       
   107                         right join M_TRANSACTION on M_TRANSACTION.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID,
       
   108          M_MOVEMENTLINE, M_LOCATOR, M_LOCATOR M_L,  C_UOM,
       
   109         M_MOVEMENT
       
   110         WHERE M_TRANSACTION.M_MOVEMENTLINE_ID = M_MOVEMENTLINE.M_MOVEMENTLINE_ID
       
   111         AND M_MOVEMENTLINE.M_LOCATOR_ID = M_LOCATOR.M_LOCATOR_ID
       
   112         AND M_MOVEMENTLINE.M_LOCATORTO_ID = M_L.M_LOCATOR_ID
       
   113         AND M_TRANSACTION.C_UOM_ID = C_UOM.C_UOM_ID
       
   114         AND M_TRANSACTION.M_MOVEMENTLINE_ID IS NOT NULL
       
   115         AND M_MOVEMENTLINE.M_MOVEMENT_ID = M_MOVEMENT.M_MOVEMENT_ID
       
   116         AND M_TRANSACTION.AD_CLIENT_ID IN ('1')
       
   117         AND M_TRANSACTION.AD_ORG_ID IN ('1')
       
   118         AND 1=1
       
   119         AND M_TRANSACTION.MOVEMENTQTY >0
       
   120         ORDER BY C_BPARTNER.NAME, M_TRANSACTION.MOVEMENTDATE DESC
       
   121      ]]></Sql>
       
   122     <Field name="rownum" value="count"/>
       
   123     <Parameter name="adUserClient" type="replace" optional="true" after="AND M_TRANSACTION.AD_CLIENT_ID IN (" text="'1'"/>
       
   124     <Parameter name="adOrgClient" type="replace" optional="true" after="AND M_TRANSACTION.AD_ORG_ID IN (" text="'1'"/>
       
   125     <Parameter name="parDateFrom" optional="true" after="AND 1=1"><![CDATA[ AND M_TRANSACTION.MOVEMENTDATE >= to_date(?)]]></Parameter>
       
   126     <Parameter name="parDateTo" optional="true" after="AND 1=1"><![CDATA[ AND M_TRANSACTION.MOVEMENTDATE < to_date(?)]]></Parameter>
       
   127     <Parameter name="cBpartner" optional="true" after="AND 1=1"><![CDATA[ AND C_BPARTNER.C_BPARTNER_ID = ?]]></Parameter>
       
   128     <Parameter name="mProductId" optional="true" after="AND 1=1"><![CDATA[ AND M_PRODUCT.M_PRODUCT_ID = ?]]></Parameter>
       
   129   </SqlMethod>
       
   130 
       
   131   <SqlMethod name="selectProduction" type="preparedStatement" return="multiple">
       
   132     <SqlMethodComment></SqlMethodComment>
       
   133     <Sql>
       
   134     <![CDATA[
       
   135         SELECT M_PRODUCT.VALUE, M_PRODUCT.NAME, M_LOCATOR.X AS X_ORIGIN, M_LOCATOR.Y AS Y_ORIGIN, M_LOCATOR.Z AS Z_ORIGIN,
       
   136         C_BPARTNER.NAME AS PARTNERNAME, '-' AS X_DESTINY, '-' AS Y_DESTINY, '-' AS Z_DESTINY, 
       
   137         (SUM(M_TRANSACTION.MOVEMENTQTY)||' '||C_UOM.NAME) AS MOVEMENTQTY, M_TRANSACTION.MOVEMENTDATE, 'No' AS OUT,
       
   138         M_PRODUCTIONPLAN.M_PRODUCTIONPLAN_ID, (M_PRODUCTION.NAME || ' - ' || M_PRODUCTIONPLAN.LINE) AS PRODUCTION_PLAN
       
   139         FROM M_PRODUCT left join C_BPARTNER on M_PRODUCT.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
       
   140                        right join M_TRANSACTION on M_TRANSACTION.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID,
       
   141              M_LOCATOR, C_UOM, M_PRODUCTIONLINE, M_PRODUCTIONPLAN, M_PRODUCTION
       
   142         WHERE M_TRANSACTION.M_LOCATOR_ID = M_LOCATOR.M_LOCATOR_ID
       
   143         AND M_TRANSACTION.C_UOM_ID = C_UOM.C_UOM_ID
       
   144         AND M_TRANSACTION.M_PRODUCTIONLINE_ID = M_PRODUCTIONLINE.M_PRODUCTIONLINE_ID
       
   145         AND M_PRODUCTIONLINE.M_PRODUCTIONPLAN_ID = M_PRODUCTIONPLAN.M_PRODUCTIONPLAN_ID
       
   146         AND M_PRODUCTIONPLAN.M_PRODUCTION_ID = M_PRODUCTION.M_PRODUCTION_ID
       
   147         AND M_TRANSACTION.M_PRODUCTIONLINE_ID IS NOT NULL
       
   148         AND M_TRANSACTION.AD_CLIENT_ID IN ('1')
       
   149         AND M_TRANSACTION.AD_ORG_ID IN ('1')
       
   150         AND 1=1
       
   151         GROUP BY M_PRODUCT.VALUE, M_PRODUCT.NAME, M_LOCATOR.X, M_LOCATOR.Y, M_LOCATOR.Z, C_BPARTNER.NAME, C_UOM.NAME, 
       
   152         M_TRANSACTION.MOVEMENTDATE,M_PRODUCTIONPLAN.M_PRODUCTIONPLAN_ID, M_PRODUCTION.NAME, M_PRODUCTIONPLAN.LINE
       
   153         HAVING SUM(M_TRANSACTION.MOVEMENTQTY) <> 0
       
   154         ORDER BY C_BPARTNER.NAME, M_TRANSACTION.MOVEMENTDATE DESC
       
   155      ]]></Sql>
       
   156     <Field name="rownum" value="count"/>
       
   157     <Parameter name="adUserClient" type="replace" optional="true" after="AND M_TRANSACTION.AD_CLIENT_ID IN (" text="'1'"/>
       
   158     <Parameter name="adOrgClient" type="replace" optional="true" after="AND M_TRANSACTION.AD_ORG_ID IN (" text="'1'"/>
       
   159     <Parameter name="parDateFrom" optional="true" after="AND 1=1"><![CDATA[ AND M_TRANSACTION.MOVEMENTDATE >= to_date(?)]]></Parameter>
       
   160     <Parameter name="parDateTo" optional="true" after="AND 1=1"><![CDATA[ AND M_TRANSACTION.MOVEMENTDATE < to_date(?)]]></Parameter>
       
   161     <Parameter name="cBpartner" optional="true" after="AND 1=1"><![CDATA[ AND C_BPARTNER.C_BPARTNER_ID = ?]]></Parameter>
       
   162     <Parameter name="mProductId" optional="true" after="AND 1=1"><![CDATA[ AND M_PRODUCT.M_PRODUCT_ID = ?]]></Parameter>
       
   163   </SqlMethod>
       
   164 
       
   165   <SqlMethod name="selectBpartner" type="preparedStatement" return="String" default="">
       
   166     <SqlMethodComment></SqlMethodComment>
       
   167     <Sql>
       
   168     <![CDATA[
       
   169       SELECT C_BPARTNER.NAME
       
   170       FROM C_BPARTNER
       
   171       WHERE C_BPARTNER.C_BPARTNER_ID = ?
       
   172      ]]></Sql>
       
   173     <Parameter name="cBpartnerId"/>
       
   174   </SqlMethod>
       
   175 
       
   176   <SqlMethod name="selectMproduct" type="preparedStatement" return="String" default="">
       
   177     <SqlMethodComment></SqlMethodComment>
       
   178     <Sql>
       
   179     <![CDATA[
       
   180       SELECT M_PRODUCT.NAME
       
   181       FROM M_PRODUCT
       
   182       WHERE M_PRODUCT.M_PRODUCT_ID = ?
       
   183      ]]></Sql>
       
   184     <Parameter name="mProductId"/>
       
   185   </SqlMethod>
       
   186 
       
   187   <SqlMethod name="set" type="constant" return="multiple">
       
   188       <SqlMethodComment></SqlMethodComment>
       
   189       <Sql></Sql>
       
   190   </SqlMethod>
       
   191 </SqlClass>