Related to Issue 21006: Fixes m_get_transaction_cost function.
authorDavid Miguelez <david.miguelez@openbravo.com>
Mon, 16 Jul 2012 19:31:49 +0200
changeset 17490 73a884a0a73c
parent 17489 70f3ddec1717
child 17491 e0c03882496c
Related to Issue 21006: Fixes m_get_transaction_cost function.
src-db/database/model/functions/M_GET_TRANSACTION_COST.xml
--- a/src-db/database/model/functions/M_GET_TRANSACTION_COST.xml	Mon Jul 16 19:18:56 2012 +0200
+++ b/src-db/database/model/functions/M_GET_TRANSACTION_COST.xml	Mon Jul 16 19:31:49 2012 +0200
@@ -48,17 +48,21 @@
 
   IF (v_hasrule = 'Y') THEN
     IF (v_hascost = 'Y') THEN
-      SELECT CASE WHEN mt.c_currency_id <> p_currency_id THEN C_CURRENCY_CONVERT_PRECISION(sum(cost), mt.c_currency_id , p_currency_id, TO_DATE(COALESCE (aa.dateacct, movementdate)), NULL,mt.ad_client_id, mt.ad_org_id,'C') ELSE sum(cost) END
-      INTO v_costamt
-      FROM (SELECT DATEACCT , m_inoutline_id
-            FROM m_inout mi , m_inoutline mil
-            WHERE mi.m_inout_id=mil.m_inout_id
-            AND dateacct <> movementdate ) aa
-      RIGHT JOIN m_transaction mt ON mt.m_inoutline_id= aa.m_inoutline_id 
-      LEFT JOIN m_transaction_cost mtc ON mtc.m_transaction_id=mt.m_transaction_id
-      WHERE mt.m_transaction_id = p_transaction_id
-      AND mtc.costdate <= p_date
-      GROUP BY mt.c_currency_id, aa.dateacct, movementdate ,mt.ad_client_id, mt.ad_org_id;
+			SELECT SUM(COST) 
+			INTO v_costamt
+			FROM (
+				SELECT CASE WHEN mt.c_currency_id <> p_currency_id 
+				THEN C_CURRENCY_CONVERT_PRECISION(sum(cost), mt.c_currency_id ,p_currency_id, TO_DATE(COALESCE (aa.dateacct, movementdate)), NULL,mt.ad_client_id, mt.ad_org_id,'C') 
+				ELSE sum(cost) END AS COST
+	      FROM m_transaction_cost mtc, m_transaction mt
+	      LEFT JOIN (SELECT DATEACCT , m_inoutline_id
+	            FROM m_inout mi , m_inoutline mil
+	            WHERE mi.m_inout_id=mil.m_inout_id
+	            AND dateacct <> movementdate ) aa ON mt.m_inoutline_id= aa.m_inoutline_id 
+	      WHERE mtc.m_transaction_id=mt.m_transaction_id
+	      AND mt.m_transaction_id = p_transaction_id
+	      AND mtc.costdate <= p_date
+	      GROUP BY mt.c_currency_id, aa.dateacct, movementdate ,mt.ad_client_id, mt.ad_org_id) A;
       RETURN v_costamt;
     ELSE
       RETURN NULL;