details: https://code.openbravo.com/erp/devel/pi/rev/b1cb8aa75274 changeset: 17463:b1cb8aa75274 user: Gorka Ion Damián <gorkaion.damian <at> openbravo.com> date: Fri Jul 13 13:27:22 2012 +0200 summary: Related to issue 21006.Fix issue in migration process.
details: https://code.openbravo.com/erp/devel/pi/rev/689e3dbe20be changeset: 17464:689e3dbe20be user: Gorka Ion Damián <gorkaion.damian <at> openbravo.com> date: Fri Jul 13 13:31:04 2012 +0200 summary: Related to issue 21006.Adapt db functions to currency in cost. details: https://code.openbravo.com/erp/devel/pi/rev/d651e86397b8 changeset: 17465:d651e86397b8 user: Gorka Ion Damián <gorkaion.damian <at> openbravo.com> date: Fri Jul 13 13:31:23 2012 +0200 summary: Related to issue 21006.Add constraint in m_transaction for currency. details: https://code.openbravo.com/erp/devel/pi/rev/7bd1880ac28f changeset: 17466:7bd1880ac28f user: Gorka Ion Damián <gorkaion.damian <at> openbravo.com> date: Fri Jul 13 13:31:36 2012 +0200 summary: Related to issue 21006.Add error message. details: https://code.openbravo.com/erp/devel/pi/rev/e06bb4b24ce2 changeset: 17467:e06bb4b24ce2 user: Gorka Ion Damián <gorkaion.damian <at> openbravo.com> date: Fri Jul 13 13:33:12 2012 +0200 summary: Related to issue 21006.Modify utils functions and adapt java classes. details: https://code.openbravo.com/erp/devel/pi/rev/e4b8450dc815 changeset: 17468:e4b8450dc815 user: Gorka Ion Damián <gorkaion.damian <at> openbravo.com> date: Fri Jul 13 13:33:33 2012 +0200 summary: Related to issue 21006.Adapt accounting to currency in cost. details: https://code.openbravo.com/erp/devel/pi/rev/4c25e6433b89 changeset: 17469:4c25e6433b89 user: Gorka Ion Damián <gorkaion.damian <at> openbravo.com> date: Fri Jul 13 13:33:59 2012 +0200 summary: Related to issue 21006.Adapt pareto report to currency in costs. details: https://code.openbravo.com/erp/devel/pi/rev/5ffd226b45c8 changeset: 17470:5ffd226b45c8 user: Gorka Ion Damián <gorkaion.damian <at> openbravo.com> date: Fri Jul 13 13:34:19 2012 +0200 summary: Related to issue 21006.Adapt valuation stock to currency in cost. details: https://code.openbravo.com/erp/devel/pi/rev/354939ec1bf9 changeset: 17471:354939ec1bf9 user: Gorka Ion Damián <gorkaion.damian <at> openbravo.com> date: Fri Jul 13 13:34:39 2012 +0200 summary: Related to issue 21006.Adapt dimensional report to currency in cost. diffstat: src-db/database/model/functions/M_GET_NO_TRX_PRODUCT_COST.xml | 25 ++- src-db/database/model/functions/M_GET_PARETO_ABC.xml | 56 ++++-- src-db/database/model/functions/M_GET_TRANSACTION_COST.xml | 20 +- src-db/database/model/functions/M_GET_TRX_AND_PRODUCT_COST.xml | 7 +- src-db/database/model/functions/M_UPDATE_PARETO_PRODUCT.xml | 64 ++++-- src-db/database/model/tables/M_TRANSACTION.xml | 1 + src-db/database/sourcedata/AD_MESSAGE.xml | 11 + src/org/openbravo/costing/CostingAlgorithm.java | 2 +- src/org/openbravo/costing/CostingMigrationProcess.java | 84 +++++++-- src/org/openbravo/costing/CostingStatus.java | 2 +- src/org/openbravo/costing/CostingUtils.java | 63 ++++--- src/org/openbravo/costing/StandardAlgorithm.java | 2 +- src/org/openbravo/erpCommon/ad_forms/DocInOut.java | 2 +- src/org/openbravo/erpCommon/ad_forms/DocLine_Material.java | 4 +- src/org/openbravo/erpCommon/ad_forms/ProductInfo.java | 7 +- src/org/openbravo/erpCommon/ad_reports/ReportInvoiceCustomerDimensionalAnalysesJR_data.xsql | 12 +- src/org/openbravo/erpCommon/ad_reports/ReportParetoProduct.java | 6 +- src/org/openbravo/erpCommon/ad_reports/ReportParetoProduct_data.xsql | 63 ++++-- src/org/openbravo/erpCommon/ad_reports/ReportValuationStock.java | 6 +- src/org/openbravo/erpCommon/ad_reports/ReportValuationStock_data.xsql | 74 ++++---- 20 files changed, 325 insertions(+), 186 deletions(-) diffs (truncated from 972 to 300 lines): diff -r d5055e9080bf -r 354939ec1bf9 src-db/database/model/functions/M_GET_NO_TRX_PRODUCT_COST.xml --- a/src-db/database/model/functions/M_GET_NO_TRX_PRODUCT_COST.xml Fri Jul 13 09:32:06 2012 +0200 +++ b/src-db/database/model/functions/M_GET_NO_TRX_PRODUCT_COST.xml Fri Jul 13 13:34:39 2012 +0200 @@ -16,6 +16,12 @@ <parameter name="p_warehouse_id" type="VARCHAR" mode="in"> <default/> </parameter> + <parameter name="p_currency_id" type="VARCHAR" mode="in"> + <default/> + </parameter> + <parameter name="p_islegalentity" type="CHAR" mode="in"> + <default><![CDATA[N]]></default> + </parameter> <body><![CDATA[/************************************************************************* * The contents of this file are subject to the Openbravo Public License * Version 1.1 (the "License"), being the Mozilla Public License @@ -42,13 +48,15 @@ BEGIN v_CostType := p_CostType; IF(p_Product_ID IS NULL) THEN - RETURN 0; + RETURN NULL; END IF; IF(p_MovementDate IS NULL) THEN - RETURN 0; + RETURN NULL; END IF; - IF(p_org_id IS NULL) THEN - RETURN 0; + IF(p_isLegalEntity = 'Y') THEN + v_legal_entity := p_org_id; + ELSIF(p_org_id IS NULL) THEN + RETURN NULL; ELSE SELECT ot.islegalentity INTO v_islegalentity FROM ad_org o JOIN ad_orgtype ot ON o.ad_orgtype_id = ot.ad_orgtype_id @@ -71,10 +79,11 @@ AND costtype IN ('STA', 'AVA') AND costtype = COALESCE(v_CostType, costtype); IF(v_Count = 0) THEN - RETURN 0; + RETURN NULL; END IF; + FOR Cur_Cost IN ( - SELECT cost + SELECT cost, c_currency_id, ad_client_id, ad_org_id FROM m_costing WHERE datefrom <= p_movementdate AND dateto > p_movementdate @@ -86,7 +95,11 @@ AND costtype = COALESCE(v_CostType, costtype) ORDER BY datefrom, dateto DESC, CASE WHEN m_warehouse_id IS NULL THEN 1 ELSE 0 END ) LOOP + IF (Cur_Cost.c_currency_id <> p_currency_id ) THEN + v_Cost := C_CURRENCY_CONVERT_PRECISION(Cur_Cost.COST, Cur_Cost.c_currency_id, p_currency_id, TO_DATE(p_movementdate), NULL, Cur_Cost.ad_client_id, Cur_Cost.ad_org_id,'C'); + ELSE v_Cost := Cur_Cost.COST; + END IF; EXIT; END LOOP; RETURN v_Cost; diff -r d5055e9080bf -r 354939ec1bf9 src-db/database/model/functions/M_GET_PARETO_ABC.xml --- a/src-db/database/model/functions/M_GET_PARETO_ABC.xml Fri Jul 13 09:32:06 2012 +0200 +++ b/src-db/database/model/functions/M_GET_PARETO_ABC.xml Fri Jul 13 13:34:39 2012 +0200 @@ -39,20 +39,35 @@ varComprobadoA NUMBER:=0; varComprobadoB NUMBER:=0; v_totalCost NUMBER; + v_currency VARCHAR2(32); TYPE RECORD IS REF CURSOR; Cur_Cursor RECORD; BEGIN + IF (p_org_ID IS NULL) THEN + RAISE_APPLICATION_ERROR(-20000, '@OrganizationNotNull@'); + END IF; + + SELECT C_CURRENCY_ID INTO v_currency + FROM AD_ORG + WHERE AD_ORG_ID = (AD_GET_ORG_LE_BU (p_org_ID, 'LE')); + -- Checks if Sum of all Product's cost are zero - SELECT SUM(CASE WHEN T1.MOVEMENTQTY>=0 THEN T1.TRANSACTIONCOST ELSE -T1.TRANSACTIONCOST END) INTO v_totalCost - FROM M_TRANSACTION T1 - LEFT JOIN M_LOCATOR L1 ON (T1.M_LOCATOR_ID=L1.M_LOCATOR_ID) - LEFT JOIN M_WAREHOUSE W1 ON (L1.M_WAREHOUSE_ID=W1.M_WAREHOUSE_ID) - WHERE T1.ISCOSTCALCULATED = 'Y' - AND (p_warehouse_ID IS NULL OR L1.M_WAREHOUSE_ID = p_warehouse_ID) - AND (p_org_ID IS NULL OR W1.AD_ORG_ID = p_org_ID) - AND (p_client_id IS NULL OR T1.AD_CLIENT_ID = p_client_id) - HAVING SUM(T1.MOVEMENTQTY) > 0; + SELECT SUM(COST_PER_CURRENCY) INTO v_totalCost + FROM ( + SELECT C_CURRENCY_CONVERT_PRECISION(SUM(CASE WHEN T.MOVEMENTQTY>=0 THEN T.TRANSACTIONCOST ELSE -T.TRANSACTIONCOST END), + T.C_CURRENCY_ID, v_currency, now(), NULL, p_client_id, AD_GET_ORG_LE_BU (p_org_ID, 'LE')) AS COST_PER_CURRENCY + FROM M_TRANSACTION T + LEFT JOIN M_LOCATOR L ON (T.M_LOCATOR_ID=L.M_LOCATOR_ID) + LEFT JOIN M_WAREHOUSE W ON (L.M_WAREHOUSE_ID=W.M_WAREHOUSE_ID) + WHERE T.ISCOSTCALCULATED = 'Y' + AND T.TRANSACTIONCOST IS NOT NULL + AND (p_warehouse_ID IS NULL OR L.M_WAREHOUSE_ID = p_warehouse_ID) + AND W.AD_ORG_ID = p_org_ID + AND (p_client_id IS NULL OR T.AD_CLIENT_ID = p_client_id) + GROUP BY T.C_CURRENCY_ID + HAVING SUM(T.MOVEMENTQTY) > 0 + ) A; IF (v_totalCost = 0) THEN RAISE_APPLICATION_ERROR(-20000, '@ZeroSumCost@'); END IF; @@ -61,16 +76,23 @@ varAcum :=0; varComprobadoA := 0; FOR cur_cursor IN ( - SELECT 100*(SUM(CASE WHEN T.MOVEMENTQTY>=0 THEN T.TRANSACTIONCOST ELSE -T.TRANSACTIONCOST END) / v_totalCost) AS PERCENTAGE - FROM M_TRANSACTION T + SELECT 100*(SUM(COST_PER_CURRENCY) / v_totalCost) AS PERCENTAGE + FROM ( + SELECT C_CURRENCY_CONVERT_PRECISION(SUM(CASE WHEN T.MOVEMENTQTY>=0 THEN T.TRANSACTIONCOST ELSE -T.TRANSACTIONCOST END), + T.C_CURRENCY_ID, v_currency, now(), NULL, p_client_id, AD_GET_ORG_LE_BU (p_org_ID, 'LE')) AS COST_PER_CURRENCY, + T.M_PRODUCT_ID + FROM M_TRANSACTION T LEFT JOIN M_LOCATOR L ON (T.M_LOCATOR_ID=L.M_LOCATOR_ID) LEFT JOIN M_WAREHOUSE W ON (L.M_WAREHOUSE_ID=W.M_WAREHOUSE_ID) - WHERE T.ISCOSTCALCULATED = 'Y' - AND (p_warehouse_ID IS NULL OR L.M_WAREHOUSE_ID = p_warehouse_ID) - AND (p_org_ID IS NULL OR W.AD_ORG_ID = p_org_ID) - AND (p_client_id IS NULL OR T.AD_CLIENT_ID = p_client_id) - GROUP BY T.M_PRODUCT_ID - HAVING SUM(T.MOVEMENTQTY) > 0 + WHERE T.ISCOSTCALCULATED = 'Y' + AND T.TRANSACTIONCOST IS NOT NULL + AND (p_warehouse_ID IS NULL OR L.M_WAREHOUSE_ID = p_warehouse_ID) + AND W.AD_ORG_ID = p_org_ID + AND (p_client_id IS NULL OR T.AD_CLIENT_ID = p_client_id) + GROUP BY T.M_PRODUCT_ID, T.C_CURRENCY_ID + HAVING SUM(T.MOVEMENTQTY) > 0 + ) A + GROUP BY M_PRODUCT_ID ORDER BY PERCENTAGE DESC ) LOOP varAcum := varAcum+Cur_Cursor.percentage; diff -r d5055e9080bf -r 354939ec1bf9 src-db/database/model/functions/M_GET_TRANSACTION_COST.xml --- a/src-db/database/model/functions/M_GET_TRANSACTION_COST.xml Fri Jul 13 09:32:06 2012 +0200 +++ b/src-db/database/model/functions/M_GET_TRANSACTION_COST.xml Fri Jul 13 13:34:39 2012 +0200 @@ -7,6 +7,9 @@ <parameter name="p_date" type="TIMESTAMP" mode="in"> <default/> </parameter> + <parameter name="p_currency_id" type="VARCHAR" mode="in"> + <default/> + </parameter> <body><![CDATA[/************************************************************************* * The contents of this file are subject to the Openbravo Public License * Version 1.1 (the "License"), being the Mozilla Public License @@ -45,13 +48,20 @@ IF (v_hasrule = 'Y') THEN IF (v_hascost = 'Y') THEN - SELECT sum(cost) INTO v_costamt - FROM m_transaction_cost - WHERE m_transaction_id = p_transaction_id - AND costdate <= p_date; + 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; RETURN v_costamt; ELSE - RETURN 0; + RETURN NULL; END IF; ELSE --Calculate cost using legacy engine diff -r d5055e9080bf -r 354939ec1bf9 src-db/database/model/functions/M_GET_TRX_AND_PRODUCT_COST.xml --- a/src-db/database/model/functions/M_GET_TRX_AND_PRODUCT_COST.xml Fri Jul 13 09:32:06 2012 +0200 +++ b/src-db/database/model/functions/M_GET_TRX_AND_PRODUCT_COST.xml Fri Jul 13 13:34:39 2012 +0200 @@ -25,6 +25,9 @@ <parameter name="p_precission" type="NUMERIC" mode="in"> <default/> </parameter> + <parameter name="p_currency_id" type="VARCHAR" mode="in"> + <default/> + </parameter> <body><![CDATA[/************************************************************************* * The contents of this file are subject to the Openbravo Public License * Version 1.1 (the "License"), being the Mozilla Public License @@ -43,9 +46,9 @@ ************************************************************************/ BEGIN IF (p_transaction_id IS NOT NULL) THEN - RETURN M_GET_TRANSACTION_COST(p_transaction_id, p_movementdate); + RETURN M_GET_TRANSACTION_COST(p_transaction_id, p_movementdate, p_currency_id); ELSE - RETURN ROUND(M_GET_NO_TRX_PRODUCT_COST(p_product_id, p_movementdate, p_costtype, p_org_id, p_warehouse_id) * p_qty, p_precission); + RETURN ROUND(COALESCE(M_GET_NO_TRX_PRODUCT_COST(p_product_id, p_movementdate, p_costtype, p_org_id, p_warehouse_id, p_currency_id), 0) * p_qty, p_precission); END IF; END M_GET_TRX_AND_PRODUCT_COST ]]></body> diff -r d5055e9080bf -r 354939ec1bf9 src-db/database/model/functions/M_UPDATE_PARETO_PRODUCT.xml --- a/src-db/database/model/functions/M_UPDATE_PARETO_PRODUCT.xml Fri Jul 13 09:32:06 2012 +0200 +++ b/src-db/database/model/functions/M_UPDATE_PARETO_PRODUCT.xml Fri Jul 13 13:34:39 2012 +0200 @@ -40,6 +40,7 @@ v_client_ID VARCHAR2(32); v_count_update NUMBER:= 0; v_count_insert NUMBER:= 0; + v_currency VARCHAR2(32); TYPE RECORD IS REF CURSOR; Cur_Cursor RECORD; Cur_Parameter RECORD; @@ -77,37 +78,54 @@ v_client_ID := p_client_id; END IF; BEGIN --BODY + + SELECT C_CURRENCY_ID INTO v_currency + FROM AD_ORG + WHERE AD_ORG_ID = (AD_GET_ORG_LE_BU (v_org_ID, 'LE')); -- Checks if Sum of all Product's cost are zero - SELECT SUM(CASE WHEN T1.MOVEMENTQTY>=0 THEN T1.TRANSACTIONCOST ELSE -T1.TRANSACTIONCOST END) INTO v_totalCost - FROM M_TRANSACTION T1 - LEFT JOIN M_LOCATOR L1 ON (T1.M_LOCATOR_ID=L1.M_LOCATOR_ID) - LEFT JOIN M_WAREHOUSE W1 ON (L1.M_WAREHOUSE_ID=W1.M_WAREHOUSE_ID) - WHERE T1.ISCOSTCALCULATED = 'Y' - AND (p_warehouse_ID IS NULL OR L1.M_WAREHOUSE_ID = p_warehouse_ID) - AND (p_org_ID IS NULL OR W1.AD_ORG_ID = p_org_ID) - AND (p_client_id IS NULL OR T1.AD_CLIENT_ID = p_client_id) - HAVING SUM(T1.MOVEMENTQTY) > 0; + SELECT SUM(COST_PER_CURRENCY) INTO v_totalCost + FROM ( + SELECT C_CURRENCY_CONVERT_PRECISION(SUM(CASE WHEN T.MOVEMENTQTY>=0 THEN T.TRANSACTIONCOST ELSE -T.TRANSACTIONCOST END), + T.C_CURRENCY_ID, v_currency, now(), NULL,v_client_ID, AD_GET_ORG_LE_BU (v_org_ID, 'LE')) AS COST_PER_CURRENCY + FROM M_TRANSACTION T + LEFT JOIN M_LOCATOR L ON (T.M_LOCATOR_ID=L.M_LOCATOR_ID) + LEFT JOIN M_WAREHOUSE W ON (L.M_WAREHOUSE_ID=W.M_WAREHOUSE_ID) + WHERE T.ISCOSTCALCULATED = 'Y' + AND T.TRANSACTIONCOST IS NOT NULL + AND (v_warehouse_ID IS NULL OR L.M_WAREHOUSE_ID = v_warehouse_ID) + AND W.AD_ORG_ID = v_org_ID + AND (v_client_ID IS NULL OR T.AD_CLIENT_ID = v_client_ID) + GROUP BY T.C_CURRENCY_ID + HAVING SUM(T.MOVEMENTQTY) > 0 + ) A; IF (v_totalCost = 0) THEN RAISE_APPLICATION_ERROR(-20000, '@ZeroSumCost@'); END IF; FOR Cur_Cursor IN ( - SELECT M_GET_PARETO_ABC(v_warehouse_ID, AD_ORG_ID, v_client_ID, PERCENTAGE) AS ISABC, + SELECT M_GET_PARETO_ABC(v_warehouse_ID, v_org_ID, v_client_ID, PERCENTAGE) AS ISABC, AD_ORG_ID, AD_CLIENT_ID, M_PRODUCT_ID - FROM (SELECT 100*ABS(SUM(CASE WHEN T.MOVEMENTQTY>=0 THEN T.TRANSACTIONCOST ELSE -T.TRANSACTIONCOST END) / v_totalCost) AS PERCENTAGE, - T.AD_ORG_ID, T.AD_CLIENT_ID, T.M_PRODUCT_ID - FROM M_TRANSACTION T - LEFT JOIN M_LOCATOR L ON (T.M_LOCATOR_ID=L.M_LOCATOR_ID) - LEFT JOIN M_WAREHOUSE W ON (L.M_WAREHOUSE_ID=W.M_WAREHOUSE_ID) - WHERE T.ISCOSTCALCULATED = 'Y' - AND (p_warehouse_ID IS NULL OR L.M_WAREHOUSE_ID = p_warehouse_ID) - AND (p_org_ID IS NULL OR W.AD_ORG_ID = p_org_ID) - AND (p_client_id IS NULL OR T.AD_CLIENT_ID = p_client_id) - GROUP BY T.AD_ORG_ID, T.AD_CLIENT_ID, T.M_PRODUCT_ID - HAVING SUM(T.MOVEMENTQTY) > 0 - ) BBB - ORDER BY PERCENTAGE DESC + FROM ( + SELECT 100*(SUM(COST_PER_CURRENCY) / v_totalCost) AS PERCENTAGE, AD_ORG_ID, AD_CLIENT_ID, M_PRODUCT_ID + FROM ( + SELECT C_CURRENCY_CONVERT_PRECISION(SUM(CASE WHEN T.MOVEMENTQTY>=0 THEN T.TRANSACTIONCOST ELSE -T.TRANSACTIONCOST END), + T.C_CURRENCY_ID, v_currency, now(), NULL, v_client_ID, AD_GET_ORG_LE_BU (v_org_ID, 'LE')) AS COST_PER_CURRENCY, + T.M_PRODUCT_ID, T.AD_ORG_ID, T.AD_CLIENT_ID + FROM M_TRANSACTION T + LEFT JOIN M_LOCATOR L ON (T.M_LOCATOR_ID=L.M_LOCATOR_ID) + LEFT JOIN M_WAREHOUSE W ON (L.M_WAREHOUSE_ID=W.M_WAREHOUSE_ID) + WHERE T.ISCOSTCALCULATED = 'Y' + AND T.TRANSACTIONCOST IS NOT NULL + AND (v_warehouse_ID IS NULL OR L.M_WAREHOUSE_ID = v_warehouse_ID) + AND W.AD_ORG_ID = v_org_ID + AND (v_client_ID IS NULL OR T.AD_CLIENT_ID = v_client_ID) + GROUP BY T.M_PRODUCT_ID, T.C_CURRENCY_ID, T.AD_ORG_ID, T.AD_CLIENT_ID + HAVING SUM(T.MOVEMENTQTY) > 0 + ) A + GROUP BY M_PRODUCT_ID, AD_ORG_ID, AD_CLIENT_ID + ORDER BY PERCENTAGE DESC + ) B ) LOOP SELECT COUNT(*) diff -r d5055e9080bf -r 354939ec1bf9 src-db/database/model/tables/M_TRANSACTION.xml --- a/src-db/database/model/tables/M_TRANSACTION.xml Fri Jul 13 09:32:06 2012 +0200 +++ b/src-db/database/model/tables/M_TRANSACTION.xml Fri Jul 13 13:34:39 2012 +0200 @@ -162,6 +162,7 @@ <index-column name="M_PRODUCT_ID"/> ------------------------------------------------------------------------------ Live Security Virtual Conference Exclusive live event will cover all the ways today's security and threat landscape has changed and how IT managers can respond. Discussions will include endpoint security, mobile security and the latest in malware threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ _______________________________________________ Openbravo-commits mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/openbravo-commits
