details: https://code.openbravo.com/erp/devel/pi/rev/57fbddd23c42 changeset: 17497:57fbddd23c42 user: Eduardo Argal Guibert <eduardo.argal <at> openbravo.com> date: Tue Jul 17 17:37:56 2012 +0200 summary: fixes bug 21115
details: https://code.openbravo.com/erp/devel/pi/rev/6fc71a314f4a changeset: 17498:6fc71a314f4a user: Eduardo Argal Guibert <eduardo.argal <at> openbravo.com> date: Tue Jul 17 17:38:43 2012 +0200 summary: Fixes bug 9099 diffstat: src-db/database/model/functions/M_GET_PARETO_ABC.xml | 39 ++- src-db/database/model/functions/M_GET_TRX_AND_PRODUCT_COST.xml | 2 +- src-db/database/model/functions/M_UPDATE_PARETO_PRODUCT.xml | 28 +- src/org/openbravo/erpCommon/ad_reports/ReportParetoProduct.html | 86 +++++----- src/org/openbravo/erpCommon/ad_reports/ReportParetoProduct.java | 31 +++- src/org/openbravo/erpCommon/ad_reports/ReportParetoProduct_data.xsql | 10 +- 6 files changed, 115 insertions(+), 81 deletions(-) diffs (truncated from 435 to 300 lines): diff -r 85cf00e297e8 -r 6fc71a314f4a src-db/database/model/functions/M_GET_PARETO_ABC.xml --- a/src-db/database/model/functions/M_GET_PARETO_ABC.xml Tue Jul 17 16:37:50 2012 +0200 +++ b/src-db/database/model/functions/M_GET_PARETO_ABC.xml Tue Jul 17 17:38:43 2012 +0200 @@ -13,6 +13,9 @@ <parameter name="p_percentageactual" type="NUMERIC" mode="in"> <default/> </parameter> + <parameter name="p_targetcurrency_id" type="VARCHAR" mode="in"> + <default><![CDATA[NULL]]></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 @@ -48,24 +51,29 @@ 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')); + IF(p_targetCurrency_id IS NULL) THEN + SELECT C_CURRENCY_ID INTO v_currency + FROM AD_ORG + WHERE AD_ORG_ID = (AD_GET_ORG_LE_BU (p_org_ID, 'LE')); + ELSE + v_currency := p_targetCurrency_id; + END IF; -- Checks if Sum of all Product's cost are zero 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 + SELECT C_CURRENCY_CONVERT_PRECISION(SUM(CASE WHEN T.MOVEMENTQTY>=0 THEN TC.COST ELSE -TC.COST END), + TC.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_COST TC, 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' + WHERE TC.M_TRANSACTION_ID = T.M_TRANSACTION_ID + AND 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 ad_isorgincluded(W.AD_ORG_ID, p_org_ID, T.AD_CLIENT_ID) <> -1 AND (p_client_id IS NULL OR T.AD_CLIENT_ID = p_client_id) - GROUP BY T.C_CURRENCY_ID + GROUP BY TC.C_CURRENCY_ID HAVING SUM(T.MOVEMENTQTY) > 0 ) A; IF (v_totalCost = 0) THEN @@ -78,18 +86,19 @@ FOR cur_cursor IN ( 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, + SELECT C_CURRENCY_CONVERT_PRECISION(SUM(CASE WHEN T.MOVEMENTQTY>=0 THEN TC.COST ELSE -TC.COST END), + TC.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 + FROM M_TRANSACTION_COST TC, 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' + WHERE TC.M_TRANSACTION_ID = T.M_TRANSACTION_ID + AND 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 ad_isorgincluded(W.AD_ORG_ID, p_org_ID, T.AD_CLIENT_ID) <> -1 AND (p_client_id IS NULL OR T.AD_CLIENT_ID = p_client_id) - GROUP BY T.M_PRODUCT_ID, T.C_CURRENCY_ID + GROUP BY T.M_PRODUCT_ID, TC.C_CURRENCY_ID HAVING SUM(T.MOVEMENTQTY) > 0 ) A GROUP BY M_PRODUCT_ID diff -r 85cf00e297e8 -r 6fc71a314f4a 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 Tue Jul 17 16:37:50 2012 +0200 +++ b/src-db/database/model/functions/M_GET_TRX_AND_PRODUCT_COST.xml Tue Jul 17 17:38:43 2012 +0200 @@ -48,7 +48,7 @@ IF (p_transaction_id IS NOT NULL) THEN RETURN M_GET_TRANSACTION_COST(p_transaction_id, p_movementdate, p_currency_id); ELSE - 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); + RETURN ROUND(M_GET_NO_TRX_PRODUCT_COST(p_product_id, p_movementdate, p_costtype, p_org_id, p_warehouse_id, p_currency_id)* p_qty, p_precission); END IF; END M_GET_TRX_AND_PRODUCT_COST ]]></body> diff -r 85cf00e297e8 -r 6fc71a314f4a src-db/database/model/functions/M_UPDATE_PARETO_PRODUCT.xml --- a/src-db/database/model/functions/M_UPDATE_PARETO_PRODUCT.xml Tue Jul 17 16:37:50 2012 +0200 +++ b/src-db/database/model/functions/M_UPDATE_PARETO_PRODUCT.xml Tue Jul 17 17:38:43 2012 +0200 @@ -86,17 +86,18 @@ -- Checks if Sum of all Product's cost are zero 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 + SELECT C_CURRENCY_CONVERT_PRECISION(SUM(CASE WHEN T.MOVEMENTQTY>=0 THEN TC.COST ELSE -TC.COST END), + TC.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_COST TC, 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' + WHERE TC.M_TRANSACTION_ID = T.M_TRANSACTION_ID + AND 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 ad_isorgincluded(W.AD_ORG_ID, v_org_ID, T.AD_CLIENT_ID) <> -1 AND (v_client_ID IS NULL OR T.AD_CLIENT_ID = v_client_ID) - GROUP BY T.C_CURRENCY_ID + GROUP BY TC.C_CURRENCY_ID HAVING SUM(T.MOVEMENTQTY) > 0 ) A; IF (v_totalCost = 0) THEN @@ -104,23 +105,24 @@ END IF; FOR Cur_Cursor IN ( - SELECT M_GET_PARETO_ABC(v_warehouse_ID, v_org_ID, v_client_ID, PERCENTAGE) AS ISABC, + SELECT M_GET_PARETO_ABC(v_warehouse_ID, v_org_ID, v_client_ID, PERCENTAGE, v_currency) AS ISABC, AD_ORG_ID, AD_CLIENT_ID, M_PRODUCT_ID 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, + SELECT C_CURRENCY_CONVERT_PRECISION(SUM(CASE WHEN T.MOVEMENTQTY>=0 THEN TC.COST ELSE -TC.COST END), + TC.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 + FROM M_TRANSACTION_COST TC, 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' + WHERE TC.M_TRANSACTION_ID = T.M_TRANSACTION_ID + AND 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 ad_isorgincluded(W.AD_ORG_ID, v_org_ID, T.AD_CLIENT_ID) <> -1 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 + GROUP BY T.M_PRODUCT_ID, TC.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 diff -r 85cf00e297e8 -r 6fc71a314f4a src/org/openbravo/erpCommon/ad_reports/ReportParetoProduct.html --- a/src/org/openbravo/erpCommon/ad_reports/ReportParetoProduct.html Tue Jul 17 16:37:50 2012 +0200 +++ b/src/org/openbravo/erpCommon/ad_reports/ReportParetoProduct.html Tue Jul 17 17:38:43 2012 +0200 @@ -61,7 +61,7 @@ } function onloadFunctions() { } -</script> +</script> <script language="JavaScript" type="text/javascript"> function refreshWarehouses() { @@ -80,7 +80,7 @@ function onLoadDo(){ this.windowTables = new Array( new windowTableId('client', 'buttonSearch'), - new windowTableId('tdbottomButtons', 'buttonUpdateABC') + new windowTableId('tdbottomButtons', 'buttonUpdateABC') ); setWindowTableParentElement(); this.tabsTables = new Array( @@ -230,7 +230,7 @@ </tr> </tbody> </table> - <!-- MessageBox END --> + <!-- MessageBox END --> <!-- USER CONTROLS BEGIN --> <table class="Main_Client_TableEdition"> <tr> @@ -245,20 +245,20 @@ <td colspan="6"> <table class="FieldGroup" cellspacing="0" cellpadding="0" border="0"> <tbody> - <tr class="FieldGroup_TopMargin"/> + <tr class="FieldGroup_TopMargin"></tr> <tr> <td class="FieldGroupTitle_Left"><img class="FieldGroupTitle_Left_bg" border="0" src="../../../../../web/images/blank.gif"/></td> <td class="FieldGroupTitle">Filter</td> <td class="FieldGroupTitle_Right"><img class="FieldGroupTitle_Right_bg" border="0" src="../../../../../web/images/blank.gif"/></td> - <td class="FieldGroupContent"/> + <td class="FieldGroupContent"></td> </tr> - <tr class="FieldGroup_BottomMargin"/> + <tr class="FieldGroup_BottomMargin"></td> </tbody> </table> </td> </tr> <tr> - <td class="TitleCell"><span class="LabelText">Organization</span></td> + <td class="TitleCell"><span class="LabelText">Organization</span></td> <td class="Combo_ContentCell" colspan="2"> <select name="inpadOrgId" id="inpadOrgId" class="ComboKey Combo_TwoCells_width" onchange="refreshWarehouses();logChanges(this);return true;" required="true"> <option value=""> <div id="reportAD_Org_ID"></div></option> @@ -266,31 +266,31 @@ </td> <td class="TitleCell"><span class="LabelText">Currency</span></td> <td class="Combo_ContentCell"> - <select name="inpCurrencyId" id="inpCurrencyId" class="ComboKey Combo_OneCell_width" required="true"> - <option value=""><div id="reportC_Currency_ID"></div></option> - </select> - </td> + <select name="inpCurrencyId" id="inpCurrencyId" class="ComboKey Combo_OneCell_width" required="true"> + <option value=""><div id="reportC_Currency_ID"></div></option> + </select> + </td> </tr> - <tr> - <td class="TitleCell"> <span class="LabelText">Warehouse</span></td> + <tr> + <td class="TitleCell"> <span class="LabelText">Warehouse</span></td> <td class="Combo_ContentCell" colspan="2"> <select name="inpmWarehouseId" id="inpmWarehouseId" class="Combo Combo_TwoCells_width" onchange="logChanges(this);return true;"> <option value=""> <div id="reportM_Warehouse_ID"></div></option> </select> </td> </tr> - <tr> + <tr> <td colspan="6"> <table class="FieldGroup" cellspacing="0" cellpadding="0" border="0"> <tbody> - <tr class="FieldGroup_TopMargin"/> + <tr class="FieldGroup_TopMargin"></tr> <tr> <td class="FieldGroupTitle_Left"><img class="FieldGroupTitle_Left_bg" border="0" src="../../../../../web/images/blank.gif"/></td> <td class="FieldGroupTitle">View Results</td> <td class="FieldGroupTitle_Right"><img class="FieldGroupTitle_Right_bg" border="0" src="../../../../../web/images/blank.gif"/></td> - <td class="FieldGroupContent"/> + <td class="FieldGroupContent"></td> </tr> - <tr class="FieldGroup_BottomMargin"/> + <tr class="FieldGroup_BottomMargin"></tr> </tbody> </table> </td> @@ -331,54 +331,54 @@ </table> <!-- OUTPUT --> - <div id="sectionOrg"> + <div id="sectionOrg"> <table class="Main_Client_TableEdition"> <tr> <td colspan="6"> <div style="height:25px;" /> - + <table cellspacing="0" cellpadding="0" width="100%" class="DataGrid_Header_Table DataGrid_Body_Table" style="table-layout: auto;" id="selEliminar"> - + <tr class="DataGrid_Body_Row"> <th colspan="9" class="DataGrid_Header_Cell"><span>Organization<span>: </span><span id="fieldAdOrg">xxOrganization</span></span></th> </tr> - <div id="sectionPartner"> + <div id="sectionPartner"> <tr class="DataGrid_Body_Row"> <th class="DataGrid_Header_Cell">Search Key</th> <th class="DataGrid_Header_Cell">Name</th> <th class="DataGrid_Header_Cell">Quantity</th> - <th class="DataGrid_Header_Cell">Unit</th> - <th class="DataGrid_Header_Cell">Cost</th> - <th class="DataGrid_Header_Cell">Value</th> - <th class="DataGrid_Header_Cell">Percentage</th> - <th class="DataGrid_Header_Cell">Category</th> - </tr> - <div id="sectionDetail"> - <tr class="DataGrid_Body_Row DataGrid_Body_Row_yy" id="funcEvenOddRow1xx"> - <td width="10%" class="DataGrid_Body_Cell" id="fieldSearchKey">xxName</td> - <td width="40%" class="DataGrid_Body_Cell" id="fieldName">xxY</td> - <td width="10%" class="DataGrid_Body_Cell" id="fieldQty">xxY</td> - <td width="10%" class="DataGrid_Body_Cell" id="fieldUnit">xxY</td> - <td width="8%" class="DataGrid_Body_Cell" id="fieldCost">xxY</td> - <td width="8%" class="DataGrid_Body_Cell" id="fieldValue">xxY</td> - <td width="7%" class="DataGrid_Body_Cell" id="fieldPercentage">xxY</td> + <th class="DataGrid_Header_Cell">Unit</th> + <th class="DataGrid_Header_Cell">Cost</th> + <th class="DataGrid_Header_Cell">Value</th> + <th class="DataGrid_Header_Cell">Percentage</th> + <th class="DataGrid_Header_Cell">Category</th> + </tr> + <div id="sectionDetail"> + <tr class="DataGrid_Body_Row DataGrid_Body_Row_yy" id="funcEvenOddRow1xx"> + <td width="10%" class="DataGrid_Body_Cell" id="fieldSearchKey">xxName</td> + <td width="40%" class="DataGrid_Body_Cell" id="fieldName">xxY</td> + <td width="10%" class="DataGrid_Body_Cell_Amount" id="fieldQty">xxY</td> + <td width="10%" class="DataGrid_Body_Cell" id="fieldUnit">xxY</td> + <td width="8%" class="DataGrid_Body_Cell_Amount" id="fieldCost">xxY</td> ------------------------------------------------------------------------------ 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 Openbravo-commits@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/openbravo-commits
