details: https://code.openbravo.com/erp/devel/pi/rev/d0c75cf1e622
changeset: 34179:d0c75cf1e622
user: Atul Gaware <atul.gaware <at> openbravo.com>
date: Fri Jun 08 09:45:12 2018 +0530
summary: Fixes Issue 38645: [Reset Unit Cost] Wrong Stock shown in Product -
Unit Cost if there exists a transaction cost with a different
accounting date
Use LAG function to check whether movementqty for previous
m_transaction_id is already considered so that once it is used
for next records with same m_transaction_id consider it as 0
so that sum(qty) is correct.
diffstat:
src-db/database/model/functions/M_INITIALIZE_STOCK_VALUATION.xml | 3 ++-
1 files changed, 2 insertions(+), 1 deletions(-)
diffs (13 lines):
diff -r ff88a330e965 -r d0c75cf1e622
src-db/database/model/functions/M_INITIALIZE_STOCK_VALUATION.xml
--- a/src-db/database/model/functions/M_INITIALIZE_STOCK_VALUATION.xml Fri Jun
08 05:29:33 2018 +0000
+++ b/src-db/database/model/functions/M_INITIALIZE_STOCK_VALUATION.xml Fri Jun
08 09:45:12 2018 +0530
@@ -68,7 +68,8 @@
SELECT SUM(a.qty) as stock, ROUND(SUM(sumcost), v_costprecission) as
valuation, aux_warehouse_id as m_warehouse_id
FROM (
SELECT trx.m_product_id, w.m_warehouse_id AS aux_warehouse_id,
- trx.movementqty AS qty, c_currency_convert_precision(SUM (CASE
WHEN trx.movementqty < 0 THEN -tc.cost ELSE tc.cost END), tc.c_currency_id,
v_currency_id, tc.dateacct, NULL, p_client, Cur_legal_entities.ad_org_id, 'C')
AS sumcost
+ CASE WHEN (LAG(trx.m_transaction_id, 1) OVER (ORDER BY
trx.m_transaction_id) = trx.m_transaction_id) THEN 0 ELSE trx.movementqty END
AS qty,
+ c_currency_convert_precision(SUM (CASE WHEN trx.movementqty <
0 THEN -tc.cost ELSE tc.cost END), tc.c_currency_id, v_currency_id,
tc.dateacct, NULL, p_client, Cur_legal_entities.ad_org_id, 'C') AS sumcost
FROM m_transaction trx
JOIN m_locator l ON (trx.m_locator_id=l.m_locator_id)
LEFT JOIN m_warehouse w ON w.m_warehouse_id = CASE
Cur_legal_entities.warehouse_dimension WHEN 'Y' THEN l.m_warehouse_id ELSE '-1'
END
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Openbravo-commits mailing list
Openbravo-commits@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/openbravo-commits
