details: https://code.openbravo.com/erp/devel/pi/rev/40bb5c5e4947
changeset: 32280:40bb5c5e4947
user: Armaignac <collazoandy4 <at> gmail.com>
date: Tue Jun 06 18:36:25 2017 -0400
summary: Fixes issue 36028: Wrong stock in Valued Stock Report
Some transactions are taken into account more than once as they are joint with
more than one record of the m_transaction_cost with different Accounting date
making a duplicate product stock.
The accounting date was removed from the joint query for a correct group of
records
with m_transaction_id and c_currency_id.
The transaction movement date is now used instance of accounting date for group
the stock
records
diffstat:
src/org/openbravo/erpCommon/ad_reports/ReportValuationStock_data.xsql | 16
+++++-----
1 files changed, 8 insertions(+), 8 deletions(-)
diffs (55 lines):
diff -r 198240535488 -r 40bb5c5e4947
src/org/openbravo/erpCommon/ad_reports/ReportValuationStock_data.xsql
--- a/src/org/openbravo/erpCommon/ad_reports/ReportValuationStock_data.xsql
Thu Jun 08 15:03:10 2017 +0200
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportValuationStock_data.xsql
Tue Jun 06 18:36:25 2017 -0400
@@ -44,21 +44,21 @@
END AS COSTTYPE, A.WAREHOUSE AS WAREHOUSE
FROM M_PRODUCT_CATEGORY,
(SELECT trx.M_PRODUCT_ID, sum(trx.MOVEMENTQTY) AS QTY,
sum(CASE WHEN trx.MOVEMENTQTY < 0 THEN- tc.trxcost ELSE tc.trxcost END) AS
Suma,
- trx.C_UOM_ID, trx.AD_CLIENT_ID, trx.iscostcalculated,
tc.c_currency_id, tc.movementdate, WH.NAME AS WAREHOUSE
+ trx.C_UOM_ID, trx.AD_CLIENT_ID, trx.iscostcalculated,
tc.c_currency_id, trx.movementdate, WH.NAME AS WAREHOUSE
FROM M_TRANSACTION trx
JOIN M_LOCATOR l ON trx.M_LOCATOR_ID = l.M_LOCATOR_ID
INNER JOIN M_WAREHOUSE WH ON l.M_WAREHOUSE_ID =
WH.M_WAREHOUSE_ID
- LEFT JOIN (SELECT sum(cost) AS trxcost,
m_transaction_id, c_currency_id, COALESCE(dateacct, costdate) AS movementdate
+ LEFT JOIN (SELECT sum(cost) AS trxcost,
m_transaction_id, c_currency_id
FROM M_TRANSACTION_COST
WHERE dateacct < to_date(?)
AND dateacct > to_date(?)
- GROUP BY m_transaction_id, c_currency_id,
COALESCE(dateacct, costdate)) tc ON trx.m_transaction_id = tc.m_transaction_id
+ GROUP BY m_transaction_id, c_currency_id) tc
ON trx.m_transaction_id = tc.m_transaction_id
WHERE trx.MOVEMENTDATE < to_date(?)
AND trx.MOVEMENTDATE > to_date(?)
AND trx.TRXPROCESSDATE >= to_timestamp(?, ?)
AND trx.ad_org_id IN ('1')
AND 0=0
- GROUP BY trx.M_PRODUCT_ID, trx.C_UOM_ID, trx.AD_CLIENT_ID,
trx.iscostcalculated, tc.c_currency_id, tc.movementdate,WH.NAME
+ GROUP BY trx.M_PRODUCT_ID, trx.C_UOM_ID, trx.AD_CLIENT_ID,
trx.iscostcalculated, tc.c_currency_id, trx.movementdate, WH.NAME
UNION ALL
SELECT agg.m_product_id, stock as qty, valuation as Suma,
agg.c_uom_id, agg.ad_client_id, CASE WHEN valuation <> 0 THEN 'Y' ELSE 'N' END,
agg.c_currency_id, dateto, wh.name as warehouse
@@ -292,21 +292,21 @@
END AS TOTAL_COST, A.ISCOSTCALCULATED, A.AD_CLIENT_ID,
A.MOVEMENTDATE, A.C_CURRENCY_ID, A.WAREHOUSE AS WAREHOUSE
FROM M_PRODUCT_CATEGORY,
(SELECT trx.M_PRODUCT_ID, sum(trx.MOVEMENTQTY) AS QTY,
sum(CASE WHEN trx.MOVEMENTQTY < 0 THEN- tc.trxcost ELSE tc.trxcost END) AS
Suma,
- trx.C_UOM_ID, trx.AD_CLIENT_ID, trx.iscostcalculated,
tc.c_currency_id, tc.movementdate as movementdate, WH.NAME AS WAREHOUSE
+ trx.C_UOM_ID, trx.AD_CLIENT_ID, trx.iscostcalculated,
tc.c_currency_id, trx.movementdate, WH.NAME AS WAREHOUSE
FROM M_TRANSACTION trx
JOIN M_LOCATOR l ON trx.M_LOCATOR_ID = l.M_LOCATOR_ID
INNER JOIN M_WAREHOUSE WH ON l.M_WAREHOUSE_ID =
WH.M_WAREHOUSE_ID
- LEFT JOIN (SELECT sum(cost) AS trxcost,
m_transaction_id, c_currency_id, COALESCE(dateacct, costdate) as movementdate
+ LEFT JOIN (SELECT sum(cost) AS trxcost,
m_transaction_id, c_currency_id
FROM M_TRANSACTION_COST
WHERE dateacct < to_date(?)
AND dateacct > to_date(?)
- GROUP BY m_transaction_id, c_currency_id,
COALESCE(dateacct, costdate)) tc ON trx.m_transaction_id = tc.m_transaction_id
+ GROUP BY m_transaction_id, c_currency_id) tc
ON trx.m_transaction_id = tc.m_transaction_id
WHERE trx.MOVEMENTDATE < to_date(?)
AND trx.MOVEMENTDATE > to_date(?)
AND trx.TRXPROCESSDATE >= to_timestamp(?, ?)
AND trx.ad_org_id IN ('1')
AND 0=0
- GROUP BY trx.M_PRODUCT_ID, trx.C_UOM_ID, trx.AD_CLIENT_ID,
trx.iscostcalculated, tc.c_currency_id, tc.movementdate,WH.NAME
+ GROUP BY trx.M_PRODUCT_ID, trx.C_UOM_ID, trx.AD_CLIENT_ID,
trx.iscostcalculated, tc.c_currency_id, trx.movementdate, WH.NAME
UNION ALL
SELECT agg.m_product_id, stock as qty, valuation as Suma,
agg.c_uom_id, agg.ad_client_id, CASE WHEN valuation <> 0 THEN 'Y' ELSE 'N' END,
agg.c_currency_id, dateto, wh.name as warehouse
------------------------------------------------------------------------------
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
