src/org/openbravo/erpCommon/ad_reports/ReportProductMovement_data.xsql
changeset 16207 705c297b4a4e
parent 15925 3d5a75391c27
child 17681 73c450ac46da
equal deleted inserted replaced
16206:01c9ee1b4fb9 16207:705c297b4a4e
    10  * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
    10  * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
    11  * License for the specific  language  governing  rights  and  limitations
    11  * License for the specific  language  governing  rights  and  limitations
    12  * under the License. 
    12  * under the License. 
    13  * The Original Code is Openbravo ERP. 
    13  * The Original Code is Openbravo ERP. 
    14  * The Initial Developer of the Original Code is Openbravo SLU 
    14  * The Initial Developer of the Original Code is Openbravo SLU 
    15  * All portions are Copyright (C) 2001-2010 Openbravo SLU 
    15  * All portions are Copyright (C) 2001-2012 Openbravo SLU 
    16  * All Rights Reserved. 
    16  * All Rights Reserved. 
    17  * Contributor(s):  ______________________________________.
    17  * Contributor(s):  ______________________________________.
    18  ************************************************************************
    18  ************************************************************************
    19 -->
    19 -->
    20 
    20 
    27   <SqlMethod name="select" type="preparedStatement" return="multiple">
    27   <SqlMethod name="select" type="preparedStatement" return="multiple">
    28     <SqlMethodComment></SqlMethodComment>
    28     <SqlMethodComment></SqlMethodComment>
    29     <Sql>
    29     <Sql>
    30     <![CDATA[
    30     <![CDATA[
    31       SELECT M_PRODUCT.VALUE, M_PRODUCT.NAME, M_TRANSACTION.MOVEMENTDATE, C_BPARTNER.NAME AS PARTNERNAME,M_ATTRIBUTESETINSTANCE.DESCRIPTION AS ATTR,
    31       SELECT M_PRODUCT.VALUE, M_PRODUCT.NAME, M_TRANSACTION.MOVEMENTDATE, C_BPARTNER.NAME AS PARTNERNAME,M_ATTRIBUTESETINSTANCE.DESCRIPTION AS ATTR,
       
    32       (CASE M_INOUT.ISSOTRX WHEN 'Y' THEN TO_CHAR(M_WAREHOUSE.NAME) ELSE '-' END)AS WA_ORIGIN,
    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.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.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 TO_CHAR(M_LOCATOR.Z) ELSE '-' END) AS Z_ORIGIN,
       
    36       (CASE M_INOUT.ISSOTRX WHEN 'Y' THEN '-' ELSE TO_CHAR(M_WAREHOUSE.NAME) END) AS WA_DESTINY, 
    35       (CASE M_INOUT.ISSOTRX WHEN 'Y' THEN '-' ELSE TO_CHAR(M_LOCATOR.X) END) AS X_DESTINY, 
    37       (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, 
    38       (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,
    39       (CASE M_INOUT.ISSOTRX WHEN 'Y' THEN '-' ELSE TO_CHAR(M_LOCATOR.Z) END) AS Z_DESTINY,
    38       AD_MESSAGE_GET2(M_INOUT.ISSOTRX,?) AS OUT,
    40       AD_MESSAGE_GET2(M_INOUT.ISSOTRX,?) AS OUT,
    39       (SUM(M_TRANSACTION.MOVEMENTQTY)||' '||C_UOM.NAME) AS MOVEMENTQTY, M_INOUT.ISSOTRX AS ISSOTRX, M_INOUT.M_INOUT_ID,
    41       (SUM(M_TRANSACTION.MOVEMENTQTY)||' '||C_UOM.NAME) AS MOVEMENTQTY, M_INOUT.ISSOTRX AS ISSOTRX, M_INOUT.M_INOUT_ID,
    40       M_INOUT.DOCUMENTNO AS DOCUMENTNO, '' AS M_MOVEMENTLINE_ID, '' AS MOVEMENT, '' AS MOVEMENT_DESCRIPTION,
    42       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, '' AS M_INTERNAL_CONSUMPTION_ID
    43       '' AS M_INVENTORY_ID, '' AS M_MOVEMENT_ID, '' AS M_PRODUCTIONPLAN_ID, '' AS PRODUCTION_PLAN, '' AS M_INTERNAL_CONSUMPTION_ID
    42       FROM M_PRODUCT left join C_BPARTNER on M_PRODUCT.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
    44       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
    45                      right join M_TRANSACTION on M_TRANSACTION.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID
    44                      left join M_ATTRIBUTESETINSTANCE ON M_ATTRIBUTESETINSTANCE.M_ATTRIBUTESETINSTANCE_ID=M_TRANSACTION.M_ATTRIBUTESETINSTANCE_ID,
    46                      left join M_ATTRIBUTESETINSTANCE ON M_ATTRIBUTESETINSTANCE.M_ATTRIBUTESETINSTANCE_ID=M_TRANSACTION.M_ATTRIBUTESETINSTANCE_ID,
    45            M_LOCATOR, M_INOUTLINE, M_INOUT, C_UOM
    47            M_LOCATOR, M_INOUTLINE, M_INOUT, C_UOM, M_WAREHOUSE
    46       WHERE M_TRANSACTION.M_LOCATOR_ID = M_LOCATOR.M_LOCATOR_ID
    48       WHERE M_TRANSACTION.M_LOCATOR_ID = M_LOCATOR.M_LOCATOR_ID
    47       AND M_TRANSACTION.M_INOUTLINE_ID = M_INOUTLINE.M_INOUTLINE_ID
    49       AND M_TRANSACTION.M_INOUTLINE_ID = M_INOUTLINE.M_INOUTLINE_ID
    48       AND M_INOUTLINE.M_INOUT_ID = M_INOUT.M_INOUT_ID
    50       AND M_INOUTLINE.M_INOUT_ID = M_INOUT.M_INOUT_ID
    49       AND M_TRANSACTION.C_UOM_ID = C_UOM.C_UOM_ID
    51       AND M_TRANSACTION.C_UOM_ID = C_UOM.C_UOM_ID
       
    52       AND M_LOCATOR.M_WAREHOUSE_ID = M_WAREHOUSE.M_WAREHOUSE_ID 
    50       AND M_TRANSACTION.M_INOUTLINE_ID IS NOT NULL 
    53       AND M_TRANSACTION.M_INOUTLINE_ID IS NOT NULL 
    51       AND M_TRANSACTION.AD_CLIENT_ID IN ('1')
    54       AND M_TRANSACTION.AD_CLIENT_ID IN ('1')
    52       AND M_TRANSACTION.AD_ORG_ID IN ('1')
    55       AND M_TRANSACTION.AD_ORG_ID IN ('1')
    53       AND 1=1
    56       AND 1=1
    54       GROUP BY M_PRODUCT.VALUE, M_PRODUCT.NAME, M_TRANSACTION.MOVEMENTDATE, C_BPARTNER.NAME, M_LOCATOR.X, M_LOCATOR.Y, 
    57       GROUP BY M_PRODUCT.VALUE, M_PRODUCT.NAME, M_TRANSACTION.MOVEMENTDATE, C_BPARTNER.NAME, M_LOCATOR.X, M_LOCATOR.Y, 
    55       M_LOCATOR.Z, M_INOUT.ISSOTRX, C_UOM.NAME, M_INOUT.M_INOUT_ID, M_INOUT.DOCUMENTNO, M_ATTRIBUTESETINSTANCE.DESCRIPTION
    58       M_LOCATOR.Z, M_INOUT.ISSOTRX, C_UOM.NAME, M_INOUT.M_INOUT_ID, M_INOUT.DOCUMENTNO, M_ATTRIBUTESETINSTANCE.DESCRIPTION,
       
    59       M_WAREHOUSE.NAME
    56       ORDER BY C_BPARTNER.NAME, M_TRANSACTION.MOVEMENTDATE DESC
    60       ORDER BY C_BPARTNER.NAME, M_TRANSACTION.MOVEMENTDATE DESC
    57      ]]></Sql>
    61      ]]></Sql>
    58     <Parameter name="adLanguage"/>
    62     <Parameter name="adLanguage"/>
    59     <Field name="rownum" value="count"/>
    63     <Field name="rownum" value="count"/>
    60     <Parameter name="adUserClient" type="replace" optional="true" after="AND M_TRANSACTION.AD_CLIENT_ID IN (" text="'1'"/>
    64     <Parameter name="adUserClient" type="replace" optional="true" after="AND M_TRANSACTION.AD_CLIENT_ID IN (" text="'1'"/>
    68 
    72 
    69   <SqlMethod name="selectInventory" type="preparedStatement" return="multiple">
    73   <SqlMethod name="selectInventory" type="preparedStatement" return="multiple">
    70     <SqlMethodComment></SqlMethodComment>
    74     <SqlMethodComment></SqlMethodComment>
    71     <Sql>
    75     <Sql>
    72     <![CDATA[
    76     <![CDATA[
    73       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, 
    77       SELECT M_PRODUCT.VALUE, M_PRODUCT.NAME, M_WAREHOUSE.NAME as WA_ORIGIN, 
       
    78       M_LOCATOR.X AS X_ORIGIN, M_LOCATOR.Y AS Y_ORIGIN, M_LOCATOR.Z AS Z_ORIGIN, C_BPARTNER.NAME AS PARTNERNAME, 
    74       (SUM(M_TRANSACTION.MOVEMENTQTY)||' '||C_UOM.NAME) AS MOVEMENTQTY, M_TRANSACTION.MOVEMENTDATE, 'No' AS OUT,
    79       (SUM(M_TRANSACTION.MOVEMENTQTY)||' '||C_UOM.NAME) AS MOVEMENTQTY, M_TRANSACTION.MOVEMENTDATE, 'No' AS OUT,
    75       M_INVENTORY.M_INVENTORY_ID, M_INVENTORY.NAME AS MOVEMENT, M_INVENTORY.NAME AS MOVEMENT_DESCRIPTION
    80       M_INVENTORY.M_INVENTORY_ID, M_INVENTORY.NAME AS MOVEMENT, M_INVENTORY.NAME AS MOVEMENT_DESCRIPTION
    76       FROM M_PRODUCT left join C_BPARTNER on M_PRODUCT.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
    81       FROM M_PRODUCT left join C_BPARTNER on M_PRODUCT.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
    77                      right join M_TRANSACTION on M_TRANSACTION.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID,
    82                      right join M_TRANSACTION on M_TRANSACTION.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID,
    78           M_LOCATOR,  C_UOM, M_INVENTORYLINE, M_INVENTORY
    83           M_LOCATOR,  C_UOM, M_INVENTORYLINE, M_INVENTORY,  M_WAREHOUSE
    79       WHERE  M_TRANSACTION.M_LOCATOR_ID = M_LOCATOR.M_LOCATOR_ID
    84       WHERE  M_TRANSACTION.M_LOCATOR_ID = M_LOCATOR.M_LOCATOR_ID
    80       AND M_TRANSACTION.C_UOM_ID = C_UOM.C_UOM_ID
    85       AND M_TRANSACTION.C_UOM_ID = C_UOM.C_UOM_ID
    81       AND M_TRANSACTION.M_INVENTORYLINE_ID = M_INVENTORYLINE.M_INVENTORYLINE_ID
    86       AND M_TRANSACTION.M_INVENTORYLINE_ID = M_INVENTORYLINE.M_INVENTORYLINE_ID
    82       AND M_INVENTORYLINE.M_INVENTORY_ID = M_INVENTORY.M_INVENTORY_ID
    87       AND M_INVENTORYLINE.M_INVENTORY_ID = M_INVENTORY.M_INVENTORY_ID
       
    88       AND M_LOCATOR.M_WAREHOUSE_ID = M_WAREHOUSE.M_WAREHOUSE_ID 
    83       AND M_TRANSACTION.M_INVENTORYLINE_ID IS NOT NULL
    89       AND M_TRANSACTION.M_INVENTORYLINE_ID IS NOT NULL
    84       AND M_TRANSACTION.AD_CLIENT_ID IN ('1')
    90       AND M_TRANSACTION.AD_CLIENT_ID IN ('1')
    85       AND M_TRANSACTION.AD_ORG_ID IN ('1')
    91       AND M_TRANSACTION.AD_ORG_ID IN ('1')
    86       AND 1=1
    92       AND 1=1
    87       GROUP BY M_PRODUCT.VALUE, M_PRODUCT.NAME, M_LOCATOR.X, M_LOCATOR.Y, M_LOCATOR.Z, C_BPARTNER.NAME, C_UOM.NAME, 
    93       GROUP BY M_PRODUCT.VALUE, M_PRODUCT.NAME, M_LOCATOR.X, M_LOCATOR.Y, M_LOCATOR.Z, C_BPARTNER.NAME, C_UOM.NAME, 
    88       M_TRANSACTION.MOVEMENTDATE, M_INVENTORY.M_INVENTORY_ID, M_INVENTORY.NAME
    94       M_TRANSACTION.MOVEMENTDATE, M_INVENTORY.M_INVENTORY_ID, M_INVENTORY.NAME, M_WAREHOUSE.NAME
    89       HAVING SUM(M_TRANSACTION.MOVEMENTQTY) <> 0
    95       HAVING SUM(M_TRANSACTION.MOVEMENTQTY) <> 0
    90       ORDER BY C_BPARTNER.NAME, M_TRANSACTION.MOVEMENTDATE DESC
    96       ORDER BY C_BPARTNER.NAME, M_TRANSACTION.MOVEMENTDATE DESC
    91      ]]></Sql>
    97      ]]></Sql>
    92     <Field name="rownum" value="count"/>
    98     <Field name="rownum" value="count"/>
    93     <Parameter name="adUserClient" type="replace" optional="true" after="AND M_TRANSACTION.AD_CLIENT_ID IN (" text="'1'"/>
    99     <Parameter name="adUserClient" type="replace" optional="true" after="AND M_TRANSACTION.AD_CLIENT_ID IN (" text="'1'"/>
   100 
   106 
   101   <SqlMethod name="selectMovement" type="preparedStatement" return="multiple">
   107   <SqlMethod name="selectMovement" type="preparedStatement" return="multiple">
   102     <SqlMethodComment></SqlMethodComment>
   108     <SqlMethodComment></SqlMethodComment>
   103     <Sql>
   109     <Sql>
   104     <![CDATA[
   110     <![CDATA[
   105         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, 
   111         SELECT M_PRODUCT.VALUE, M_PRODUCT.NAME, M_WAREHOUSE.NAME as WA_ORIGIN, M_LOCATOR.X AS X_ORIGIN, 
       
   112         M_LOCATOR.Y AS Y_ORIGIN, M_LOCATOR.Z AS Z_ORIGIN, M_L_WAREHOUSE.NAME as WA_DESTINY,
   106         M_L.X AS X_DESTINY, M_L.Y AS Y_DESTINY, M_L.Z AS Z_DESTINY, C_BPARTNER.NAME AS PARTNERNAME, 
   113         M_L.X AS X_DESTINY, M_L.Y AS Y_DESTINY, M_L.Z AS Z_DESTINY, C_BPARTNER.NAME AS PARTNERNAME, 
   107         (M_TRANSACTION.MOVEMENTQTY||' '||C_UOM.NAME) AS MOVEMENTQTY, M_TRANSACTION.MOVEMENTDATE, 'No' AS OUT,
   114         (M_TRANSACTION.MOVEMENTQTY||' '||C_UOM.NAME) AS MOVEMENTQTY, M_TRANSACTION.MOVEMENTDATE, 'No' AS OUT,
   108         M_MOVEMENTLINE.M_MOVEMENT_ID, M_MOVEMENT.NAME AS MOVEMENT
   115         M_MOVEMENTLINE.M_MOVEMENT_ID, M_MOVEMENT.NAME AS MOVEMENT
   109         FROM  M_PRODUCT left join C_BPARTNER on M_PRODUCT.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
   116         FROM  M_PRODUCT left join C_BPARTNER on M_PRODUCT.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
   110                         right join M_TRANSACTION on M_TRANSACTION.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID,
   117                         right join M_TRANSACTION on M_TRANSACTION.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID,
   111          M_MOVEMENTLINE, M_LOCATOR, M_LOCATOR M_L,  C_UOM,
   118         M_MOVEMENTLINE, M_LOCATOR, M_LOCATOR M_L,  C_UOM,M_MOVEMENT,  M_WAREHOUSE, M_WAREHOUSE M_L_WAREHOUSE
   112         M_MOVEMENT
       
   113         WHERE M_TRANSACTION.M_MOVEMENTLINE_ID = M_MOVEMENTLINE.M_MOVEMENTLINE_ID
   119         WHERE M_TRANSACTION.M_MOVEMENTLINE_ID = M_MOVEMENTLINE.M_MOVEMENTLINE_ID
   114         AND M_MOVEMENTLINE.M_LOCATOR_ID = M_LOCATOR.M_LOCATOR_ID
   120         AND M_MOVEMENTLINE.M_LOCATOR_ID = M_LOCATOR.M_LOCATOR_ID
   115         AND M_MOVEMENTLINE.M_LOCATORTO_ID = M_L.M_LOCATOR_ID
   121         AND M_MOVEMENTLINE.M_LOCATORTO_ID = M_L.M_LOCATOR_ID
   116         AND M_TRANSACTION.C_UOM_ID = C_UOM.C_UOM_ID
   122         AND M_TRANSACTION.C_UOM_ID = C_UOM.C_UOM_ID
       
   123         AND M_LOCATOR.M_WAREHOUSE_ID = M_WAREHOUSE.M_WAREHOUSE_ID
       
   124         AND M_L.M_WAREHOUSE_ID = M_L_WAREHOUSE.M_WAREHOUSE_ID
   117         AND M_TRANSACTION.M_MOVEMENTLINE_ID IS NOT NULL
   125         AND M_TRANSACTION.M_MOVEMENTLINE_ID IS NOT NULL
   118         AND M_MOVEMENTLINE.M_MOVEMENT_ID = M_MOVEMENT.M_MOVEMENT_ID
   126         AND M_MOVEMENTLINE.M_MOVEMENT_ID = M_MOVEMENT.M_MOVEMENT_ID
   119         AND M_TRANSACTION.AD_CLIENT_ID IN ('1')
   127         AND M_TRANSACTION.AD_CLIENT_ID IN ('1')
   120         AND M_TRANSACTION.AD_ORG_ID IN ('1')
   128         AND M_TRANSACTION.AD_ORG_ID IN ('1')
   121         AND 1=1
   129         AND 1=1
   133 
   141 
   134   <SqlMethod name="selectProduction" type="preparedStatement" return="multiple">
   142   <SqlMethod name="selectProduction" type="preparedStatement" return="multiple">
   135     <SqlMethodComment></SqlMethodComment>
   143     <SqlMethodComment></SqlMethodComment>
   136     <Sql>
   144     <Sql>
   137     <![CDATA[
   145     <![CDATA[
   138         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,
   146         SELECT M_PRODUCT.VALUE, M_PRODUCT.NAME, M_WAREHOUSE.NAME as WA_ORIGIN, M_LOCATOR.X AS X_ORIGIN, M_LOCATOR.Y AS Y_ORIGIN, M_LOCATOR.Z AS Z_ORIGIN,
   139         C_BPARTNER.NAME AS PARTNERNAME, '-' AS X_DESTINY, '-' AS Y_DESTINY, '-' AS Z_DESTINY, 
   147         C_BPARTNER.NAME AS PARTNERNAME, '-' AS WA_DESTINY, '-' AS X_DESTINY, '-' AS Y_DESTINY, '-' AS Z_DESTINY, 
   140         (SUM(M_TRANSACTION.MOVEMENTQTY)||' '||C_UOM.NAME) AS MOVEMENTQTY, M_TRANSACTION.MOVEMENTDATE, 'No' AS OUT, M_PRODUCTION.ISSOTRX AS ISSOTRX,
   148         (SUM(M_TRANSACTION.MOVEMENTQTY)||' '||C_UOM.NAME) AS MOVEMENTQTY, M_TRANSACTION.MOVEMENTDATE, 'No' AS OUT, M_PRODUCTION.ISSOTRX AS ISSOTRX,
   141         M_PRODUCTIONPLAN.M_PRODUCTIONPLAN_ID, ((CASE WHEN M_PRODUCTION.NAME IS NULL THEN M_PRODUCTION.DOCUMENTNO ELSE M_PRODUCTION.NAME END) || ' - ' || M_PRODUCTIONPLAN.LINE) AS PRODUCTION_PLAN
   149         M_PRODUCTIONPLAN.M_PRODUCTIONPLAN_ID, ((CASE WHEN M_PRODUCTION.NAME IS NULL THEN M_PRODUCTION.DOCUMENTNO ELSE M_PRODUCTION.NAME END) || ' - ' || M_PRODUCTIONPLAN.LINE) AS PRODUCTION_PLAN
   142         FROM M_PRODUCT left join C_BPARTNER on M_PRODUCT.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
   150         FROM M_PRODUCT left join C_BPARTNER on M_PRODUCT.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
   143                        right join M_TRANSACTION on M_TRANSACTION.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID,
   151                        right join M_TRANSACTION on M_TRANSACTION.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID,
   144              M_LOCATOR, C_UOM, M_PRODUCTIONLINE, M_PRODUCTIONPLAN, M_PRODUCTION
   152              M_LOCATOR, C_UOM, M_PRODUCTIONLINE, M_PRODUCTIONPLAN, M_PRODUCTION, M_WAREHOUSE
   145         WHERE M_TRANSACTION.M_LOCATOR_ID = M_LOCATOR.M_LOCATOR_ID
   153         WHERE M_TRANSACTION.M_LOCATOR_ID = M_LOCATOR.M_LOCATOR_ID
   146         AND M_TRANSACTION.C_UOM_ID = C_UOM.C_UOM_ID
   154         AND M_TRANSACTION.C_UOM_ID = C_UOM.C_UOM_ID
   147         AND M_TRANSACTION.M_PRODUCTIONLINE_ID = M_PRODUCTIONLINE.M_PRODUCTIONLINE_ID
   155         AND M_TRANSACTION.M_PRODUCTIONLINE_ID = M_PRODUCTIONLINE.M_PRODUCTIONLINE_ID
   148         AND M_PRODUCTIONLINE.M_PRODUCTIONPLAN_ID = M_PRODUCTIONPLAN.M_PRODUCTIONPLAN_ID
   156         AND M_PRODUCTIONLINE.M_PRODUCTIONPLAN_ID = M_PRODUCTIONPLAN.M_PRODUCTIONPLAN_ID
   149         AND M_PRODUCTIONPLAN.M_PRODUCTION_ID = M_PRODUCTION.M_PRODUCTION_ID
   157         AND M_PRODUCTIONPLAN.M_PRODUCTION_ID = M_PRODUCTION.M_PRODUCTION_ID
       
   158         AND M_LOCATOR.M_WAREHOUSE_ID = M_WAREHOUSE.M_WAREHOUSE_ID
   150         AND M_TRANSACTION.M_PRODUCTIONLINE_ID IS NOT NULL
   159         AND M_TRANSACTION.M_PRODUCTIONLINE_ID IS NOT NULL
   151         AND M_TRANSACTION.AD_CLIENT_ID IN ('1')
   160         AND M_TRANSACTION.AD_CLIENT_ID IN ('1')
   152         AND M_TRANSACTION.AD_ORG_ID IN ('1')
   161         AND M_TRANSACTION.AD_ORG_ID IN ('1')
   153         AND 1=1
   162         AND 1=1
   154         GROUP BY M_PRODUCT.VALUE, M_PRODUCT.NAME, M_LOCATOR.X, M_LOCATOR.Y, M_LOCATOR.Z, C_BPARTNER.NAME, C_UOM.NAME, 
   163         GROUP BY M_PRODUCT.VALUE, M_PRODUCT.NAME, M_LOCATOR.X, M_LOCATOR.Y, M_LOCATOR.Z, C_BPARTNER.NAME, C_UOM.NAME, 
   155         M_TRANSACTION.MOVEMENTDATE,M_PRODUCTIONPLAN.M_PRODUCTIONPLAN_ID, M_PRODUCTION.NAME, M_PRODUCTIONPLAN.LINE, M_PRODUCTION.ISSOTRX, M_PRODUCTION.DOCUMENTNO
   164         M_TRANSACTION.MOVEMENTDATE,M_PRODUCTIONPLAN.M_PRODUCTIONPLAN_ID, M_PRODUCTION.NAME, M_PRODUCTIONPLAN.LINE, M_PRODUCTION.ISSOTRX, M_PRODUCTION.DOCUMENTNO, M_WAREHOUSE.NAME
   156         HAVING SUM(M_TRANSACTION.MOVEMENTQTY) <> 0
   165         HAVING SUM(M_TRANSACTION.MOVEMENTQTY) <> 0
   157         ORDER BY C_BPARTNER.NAME, M_TRANSACTION.MOVEMENTDATE DESC
   166         ORDER BY C_BPARTNER.NAME, M_TRANSACTION.MOVEMENTDATE DESC
   158      ]]></Sql>
   167      ]]></Sql>
   159     <Field name="rownum" value="count"/>
   168     <Field name="rownum" value="count"/>
   160     <Parameter name="adUserClient" type="replace" optional="true" after="AND M_TRANSACTION.AD_CLIENT_ID IN (" text="'1'"/>
   169     <Parameter name="adUserClient" type="replace" optional="true" after="AND M_TRANSACTION.AD_CLIENT_ID IN (" text="'1'"/>
   167   
   176   
   168   <SqlMethod name="selectInternalConsumption" type="preparedStatement" return="multiple">
   177   <SqlMethod name="selectInternalConsumption" type="preparedStatement" return="multiple">
   169     <SqlMethodComment></SqlMethodComment>
   178     <SqlMethodComment></SqlMethodComment>
   170     <Sql>
   179     <Sql>
   171     <![CDATA[
   180     <![CDATA[
   172       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, 
   181       SELECT M_PRODUCT.VALUE, M_PRODUCT.NAME, M_WAREHOUSE.NAME as WA_ORIGIN, 
       
   182       M_LOCATOR.X AS X_ORIGIN, M_LOCATOR.Y AS Y_ORIGIN, M_LOCATOR.Z AS Z_ORIGIN, C_BPARTNER.NAME AS PARTNERNAME, 
   173       (SUM(M_TRANSACTION.MOVEMENTQTY)||' '||C_UOM.NAME) AS MOVEMENTQTY, M_TRANSACTION.MOVEMENTDATE, 'No' AS OUT,
   183       (SUM(M_TRANSACTION.MOVEMENTQTY)||' '||C_UOM.NAME) AS MOVEMENTQTY, M_TRANSACTION.MOVEMENTDATE, 'No' AS OUT,
   174       M_INTERNAL_CONSUMPTION.M_INTERNAL_CONSUMPTION_ID, M_INTERNAL_CONSUMPTION.NAME AS MOVEMENT, M_INTERNAL_CONSUMPTION.NAME AS MOVEMENT_DESCRIPTION
   184       M_INTERNAL_CONSUMPTION.M_INTERNAL_CONSUMPTION_ID, M_INTERNAL_CONSUMPTION.NAME AS MOVEMENT, M_INTERNAL_CONSUMPTION.NAME AS MOVEMENT_DESCRIPTION
   175       FROM M_PRODUCT left join C_BPARTNER on M_PRODUCT.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
   185       FROM M_PRODUCT left join C_BPARTNER on M_PRODUCT.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
   176                      right join M_TRANSACTION on M_TRANSACTION.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID,
   186                      right join M_TRANSACTION on M_TRANSACTION.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID,
   177           M_LOCATOR,  C_UOM, M_INTERNAL_CONSUMPTIONLINE, M_INTERNAL_CONSUMPTION
   187           M_LOCATOR,  C_UOM, M_INTERNAL_CONSUMPTIONLINE, M_INTERNAL_CONSUMPTION, M_WAREHOUSE
   178       WHERE  M_TRANSACTION.M_LOCATOR_ID = M_LOCATOR.M_LOCATOR_ID
   188       WHERE  M_TRANSACTION.M_LOCATOR_ID = M_LOCATOR.M_LOCATOR_ID
   179       AND M_TRANSACTION.C_UOM_ID = C_UOM.C_UOM_ID
   189       AND M_TRANSACTION.C_UOM_ID = C_UOM.C_UOM_ID
   180       AND M_TRANSACTION.M_INTERNAL_CONSUMPTIONLINE_ID = M_INTERNAL_CONSUMPTIONLINE.M_INTERNAL_CONSUMPTIONLINE_ID
   190       AND M_TRANSACTION.M_INTERNAL_CONSUMPTIONLINE_ID = M_INTERNAL_CONSUMPTIONLINE.M_INTERNAL_CONSUMPTIONLINE_ID
   181       AND M_INTERNAL_CONSUMPTIONLINE.M_INTERNAL_CONSUMPTION_ID = M_INTERNAL_CONSUMPTION.M_INTERNAL_CONSUMPTION_ID
   191       AND M_INTERNAL_CONSUMPTIONLINE.M_INTERNAL_CONSUMPTION_ID = M_INTERNAL_CONSUMPTION.M_INTERNAL_CONSUMPTION_ID
       
   192       AND M_LOCATOR.M_WAREHOUSE_ID = M_WAREHOUSE.M_WAREHOUSE_ID
   182       AND M_TRANSACTION.M_INTERNAL_CONSUMPTIONLINE_ID IS NOT NULL
   193       AND M_TRANSACTION.M_INTERNAL_CONSUMPTIONLINE_ID IS NOT NULL
   183       AND M_TRANSACTION.AD_CLIENT_ID IN ('1')
   194       AND M_TRANSACTION.AD_CLIENT_ID IN ('1')
   184       AND M_TRANSACTION.AD_ORG_ID IN ('1')
   195       AND M_TRANSACTION.AD_ORG_ID IN ('1')
   185       AND 1=1
   196       AND 1=1
   186       GROUP BY M_PRODUCT.VALUE, M_PRODUCT.NAME, M_LOCATOR.X, M_LOCATOR.Y, M_LOCATOR.Z, C_BPARTNER.NAME, C_UOM.NAME, 
   197       GROUP BY M_PRODUCT.VALUE, M_PRODUCT.NAME, M_LOCATOR.X, M_LOCATOR.Y, M_LOCATOR.Z, C_BPARTNER.NAME, C_UOM.NAME, 
   187       M_TRANSACTION.MOVEMENTDATE, M_INTERNAL_CONSUMPTION.M_INTERNAL_CONSUMPTION_ID, M_INTERNAL_CONSUMPTION.NAME, M_INTERNAL_CONSUMPTION.DESCRIPTION
   198       M_TRANSACTION.MOVEMENTDATE, M_INTERNAL_CONSUMPTION.M_INTERNAL_CONSUMPTION_ID, M_INTERNAL_CONSUMPTION.NAME, M_INTERNAL_CONSUMPTION.DESCRIPTION, M_WAREHOUSE.NAME
   188       HAVING SUM(M_TRANSACTION.MOVEMENTQTY) <> 0
   199       HAVING SUM(M_TRANSACTION.MOVEMENTQTY) <> 0
   189       ORDER BY C_BPARTNER.NAME, M_TRANSACTION.MOVEMENTDATE DESC
   200       ORDER BY C_BPARTNER.NAME, M_TRANSACTION.MOVEMENTDATE DESC
   190      ]]></Sql>
   201      ]]></Sql>
   191     <Field name="rownum" value="count"/>
   202     <Field name="rownum" value="count"/>
   192     <Parameter name="adUserClient" type="replace" optional="true" after="AND M_TRANSACTION.AD_CLIENT_ID IN (" text="'1'"/>
   203     <Parameter name="adUserClient" type="replace" optional="true" after="AND M_TRANSACTION.AD_CLIENT_ID IN (" text="'1'"/>