src/org/openbravo/erpCommon/ad_reports/ReportProductMovement_data.xsql
changeset 3600 973c74129894
parent 1605 8a0fe0193bef
child 3684 9bd469a5a025
--- a/src/org/openbravo/erpCommon/ad_reports/ReportProductMovement_data.xsql	Mon Apr 06 13:34:49 2009 -0400
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportProductMovement_data.xsql	Mon Apr 06 11:55:56 2009 +0200
@@ -36,7 +36,7 @@
       (CASE M_INOUT.ISSOTRX WHEN 'Y' THEN '-' ELSE TO_CHAR(M_LOCATOR.Y) END) AS Y_DESTINY, 
       (CASE M_INOUT.ISSOTRX WHEN 'Y' THEN '-' ELSE TO_CHAR(M_LOCATOR.Z) END) AS Z_DESTINY,
       AD_MESSAGE_GET2(M_INOUT.ISSOTRX,?) AS OUT,
-      (SUM(M_TRANSACTION.MOVEMENTQTY)||' '||C_UOM.NAME) AS MOVEMENTQTY, M_INOUT.ISSOTRX, M_INOUT.M_INOUT_ID,
+      (SUM(M_TRANSACTION.MOVEMENTQTY)||' '||C_UOM.NAME) AS MOVEMENTQTY, M_INOUT.ISSOTRX AS ISSOTRX, M_INOUT.M_INOUT_ID,
       M_INOUT.DOCUMENTNO AS DOCUMENTNO, '' AS M_MOVEMENTLINE_ID, '' AS MOVEMENT, '' AS MOVEMENT_DESCRIPTION,
       '' AS M_INVENTORY_ID, '' AS M_MOVEMENT_ID, '' AS M_PRODUCTIONPLAN_ID, '' AS PRODUCTION_PLAN
       FROM M_PRODUCT left join C_BPARTNER on M_PRODUCT.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
@@ -136,8 +136,8 @@
     <![CDATA[
         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, '-' AS X_DESTINY, '-' AS Y_DESTINY, '-' AS Z_DESTINY, 
-        (SUM(M_TRANSACTION.MOVEMENTQTY)||' '||C_UOM.NAME) AS MOVEMENTQTY, M_TRANSACTION.MOVEMENTDATE, 'No' AS OUT,
-        M_PRODUCTIONPLAN.M_PRODUCTIONPLAN_ID, (M_PRODUCTION.NAME || ' - ' || M_PRODUCTIONPLAN.LINE) AS PRODUCTION_PLAN
+        (SUM(M_TRANSACTION.MOVEMENTQTY)||' '||C_UOM.NAME) AS MOVEMENTQTY, M_TRANSACTION.MOVEMENTDATE, 'No' AS OUT, M_PRODUCTION.ISSOTRX AS ISSOTRX,
+        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
         FROM M_PRODUCT left join C_BPARTNER on M_PRODUCT.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
                        right join M_TRANSACTION on M_TRANSACTION.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID,
              M_LOCATOR, C_UOM, M_PRODUCTIONLINE, M_PRODUCTIONPLAN, M_PRODUCTION
@@ -151,7 +151,7 @@
         AND M_TRANSACTION.AD_ORG_ID IN ('1')
         AND 1=1
         GROUP BY M_PRODUCT.VALUE, M_PRODUCT.NAME, M_LOCATOR.X, M_LOCATOR.Y, M_LOCATOR.Z, C_BPARTNER.NAME, C_UOM.NAME, 
-        M_TRANSACTION.MOVEMENTDATE,M_PRODUCTIONPLAN.M_PRODUCTIONPLAN_ID, M_PRODUCTION.NAME, M_PRODUCTIONPLAN.LINE
+        M_TRANSACTION.MOVEMENTDATE,M_PRODUCTIONPLAN.M_PRODUCTIONPLAN_ID, M_PRODUCTION.NAME, M_PRODUCTIONPLAN.LINE, M_PRODUCTION.ISSOTRX, M_PRODUCTION.DOCUMENTNO
         HAVING SUM(M_TRANSACTION.MOVEMENTQTY) <> 0
         ORDER BY C_BPARTNER.NAME, M_TRANSACTION.MOVEMENTDATE DESC
      ]]></Sql>