details: /erp/devel/pi/rev/8dd14059ac22 changeset: 6524:8dd14059ac22 user: Gorka Ion Damián <gorkaion.damian <at> openbravo.com> date: Fri Feb 26 18:46:56 2010 +0100 summary: Fixes changeset 825d188f527d
details: /erp/devel/pi/rev/f718ec3e327c changeset: 6525:f718ec3e327c user: Gorka Ion Damián <gorkaion.damian <at> openbravo.com> date: Fri Feb 26 18:58:16 2010 +0100 summary: Fixes issue 12487. m_generate_average_costs doesn't round to Cost Precision diffstat: src-db/database/model/functions/C_CURRENCY_CONVERT.xml | 18 +-- src-db/database/model/functions/C_CURRENCY_CONVERT_PRECISION.xml | 67 ++++++++++ src-db/database/model/functions/C_GET_CURRENCY_PRECISION.xml | 4 +- src-db/database/model/functions/M_GENERATE_AVERAGE_COSTS.xml | 12 +- 4 files changed, 77 insertions(+), 24 deletions(-) diffs (166 lines): diff -r b3e9df729fb8 -r f718ec3e327c src-db/database/model/functions/C_CURRENCY_CONVERT.xml --- a/src-db/database/model/functions/C_CURRENCY_CONVERT.xml Fri Feb 26 18:28:52 2010 +0100 +++ b/src-db/database/model/functions/C_CURRENCY_CONVERT.xml Fri Feb 26 18:58:16 2010 +0100 @@ -37,7 +37,7 @@ * parts created by ComPiere are Copyright (C) ComPiere, Inc.; * All Rights Reserved. * Contributor(s): Openbravo SL -* Contributions are Copyright (C) 2001-2009 Openbravo, S.L. +* Contributions are Copyright (C) 2001-2010 Openbravo, S.L. * * Specifically, this derivative work is based upon the following Compiere * file and version. @@ -54,21 +54,7 @@ ************************************************************************/ v_Rate NUMBER; BEGIN - -- Return Amount - IF(p_Amount=0 OR p_CurFrom_ID=p_CurTo_ID) THEN - RETURN p_Amount; - END IF; - -- Return NULL - IF(p_Amount IS NULL OR p_CurFrom_ID IS NULL OR p_CurTo_ID IS NULL) THEN - RETURN NULL; - END IF; - -- Get Rate - v_Rate:=C_Currency_Rate(p_CurFrom_ID, p_CurTo_ID, p_ConvDate, p_RateType, p_Client_ID, p_Org_ID) ; - IF(v_Rate IS NULL) THEN - RETURN NULL; - END IF; - -- Standard Precision - RETURN C_Currency_Round(p_Amount * v_Rate, p_CurTo_ID, null) ; + RETURN C_CURRENCY_CONVERT_PRECISION(p_amount, p_curfrom_id, p_curto_id, p_convdate, p_ratetype, p_client_id, p_org_id, 'A'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM) ; diff -r b3e9df729fb8 -r f718ec3e327c src-db/database/model/functions/C_CURRENCY_CONVERT_PRECISION.xml --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/src-db/database/model/functions/C_CURRENCY_CONVERT_PRECISION.xml Fri Feb 26 18:58:16 2010 +0100 @@ -0,0 +1,67 @@ +<?xml version="1.0"?> + <database name="FUNCTION C_CURRENCY_CONVERT_PRECISION"> + <function name="C_CURRENCY_CONVERT_PRECISION" type="NUMERIC"> + <parameter name="p_amount" type="NUMERIC" mode="in"> + <default/> + </parameter> + <parameter name="p_curfrom_id" type="VARCHAR" mode="in"> + <default/> + </parameter> + <parameter name="p_curto_id" type="VARCHAR" mode="in"> + <default/> + </parameter> + <parameter name="p_convdate" type="TIMESTAMP" mode="in"> + <default/> + </parameter> + <parameter name="p_ratetype" type="VARCHAR" mode="in"> + <default/> + </parameter> + <parameter name="p_client_id" type="VARCHAR" mode="in"> + <default><![CDATA[0]]></default> + </parameter> + <parameter name="p_org_id" type="VARCHAR" mode="in"> + <default><![CDATA[0]]></default> + </parameter> + <parameter name="p_precisiontype" type="CHAR" mode="in"> + <default><![CDATA[A]]></default> + </parameter> + <body><![CDATA[/************************************************************************* + * The contents of this file are subject to the Openbravo Public License + * Version 1.0 (the "License"), being the Mozilla Public License + * Version 1.1 with a permitted attribution clause; you may not use this + * file except in compliance with the License. You may obtain a copy of + * the License at http://www.openbravo.com/legal/license.html + * Software distributed under the License is distributed on an "AS IS" + * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the + * License for the specific language governing rights and limitations + * under the License. + * The Original Code is Openbravo ERP. + * The Initial Developer of the Original Code is Openbravo SL + * All portions are Copyright (C) 2010 Openbravo SL + * All Rights Reserved. +************************************************************************/ + v_Rate NUMBER; +BEGIN + -- Return Amount + IF(p_Amount=0 OR p_CurFrom_ID=p_CurTo_ID) THEN + RETURN p_Amount; + END IF; + -- Return NULL + IF(p_Amount IS NULL OR p_CurFrom_ID IS NULL OR p_CurTo_ID IS NULL) THEN + RETURN NULL; + END IF; + -- Get Rate + v_Rate:=C_Currency_Rate(p_CurFrom_ID, p_CurTo_ID, p_ConvDate, p_RateType, p_Client_ID, p_Org_ID) ; + IF(v_Rate IS NULL) THEN + RETURN NULL; + END IF; + -- Standard Precision + RETURN ROUND(p_Amount * v_Rate, C_GET_CURRENCY_PRECISION(p_CurTo_ID, p_precisiontype)) ; +EXCEPTION +WHEN OTHERS THEN + DBMS_OUTPUT.PUT_LINE(SQLERRM) ; + RAISE; +END C_CURRENCY_CONVERT_PRECISION +]]></body> + </function> + </database> diff -r b3e9df729fb8 -r f718ec3e327c src-db/database/model/functions/C_GET_CURRENCY_PRECISION.xml --- a/src-db/database/model/functions/C_GET_CURRENCY_PRECISION.xml Fri Feb 26 18:28:52 2010 +0100 +++ b/src-db/database/model/functions/C_GET_CURRENCY_PRECISION.xml Fri Feb 26 18:58:16 2010 +0100 @@ -41,9 +41,9 @@ WHERE C_Currency_ID = p_CurTo_ID; IF(p_prec_type = 'P') THEN RETURN v_PricePrecision; - ELSEIF(p_prec_type = 'C') THEN + ELSIF(p_prec_type = 'C') THEN RETURN v_CostPrecision; - ELSEIF(p_prec_type = 'A') THEN + ELSIF(p_prec_type = 'A') THEN RETURN v_StdPrecision; END IF; END C_GET_CURRENCY_PRECISION diff -r b3e9df729fb8 -r f718ec3e327c src-db/database/model/functions/M_GENERATE_AVERAGE_COSTS.xml --- a/src-db/database/model/functions/M_GENERATE_AVERAGE_COSTS.xml Fri Feb 26 18:28:52 2010 +0100 +++ b/src-db/database/model/functions/M_GENERATE_AVERAGE_COSTS.xml Fri Feb 26 18:58:16 2010 +0100 @@ -177,7 +177,7 @@ v_Qty:=0; END IF; v_ResultStr:='invoice albaran: '||Cur_InOutLine.M_INOUTLINE_ID; - SELECT CASE WHEN SUM(QTYINVOICED) <=0 THEN 0 ELSE SUM(C_INVOICELINE.QTYINVOICED*C_CURRENCY_CONVERT(C_INVOICELINE.PRICEACTUAL, C_INVOICE.C_CURRENCY_ID, v_BaseCurrency, TO_DATE(COALESCE(C_INVOICE.DATEINVOICED, Cur_InOutLine.MOVEMENTDATE)), NULL, C_INVOICELINE.AD_CLIENT_ID, C_INVOICELINE.AD_ORG_ID)) /SUM(QTYINVOICED) END, + SELECT CASE WHEN SUM(QTYINVOICED) <=0 THEN 0 ELSE SUM(C_INVOICELINE.QTYINVOICED*C_CURRENCY_CONVERT_PRECISION(C_INVOICELINE.PRICEACTUAL, C_INVOICE.C_CURRENCY_ID, v_BaseCurrency, TO_DATE(COALESCE(C_INVOICE.DATEINVOICED, Cur_InOutLine.MOVEMENTDATE)), NULL, C_INVOICELINE.AD_CLIENT_ID, C_INVOICELINE.AD_ORG_ID, 'C')) /SUM(QTYINVOICED) END, CASE WHEN SUM(QTYINVOICED) <=0 THEN 0 ELSE COUNT(C_INVOICELINE.C_INVOICELINE_ID) END INTO v_Price, v_Count FROM M_MATCHINV, C_INVOICELINE, C_INVOICE @@ -187,7 +187,7 @@ AND QTYINVOICED<>0; IF(v_Count=0) THEN v_ResultStr:='order albaran: '||Cur_InOutLine.M_INOUTLINE_ID; - SELECT CASE WHEN SUM(C_ORDERLINE.QTYORDERED) <=0 THEN 0 ELSE SUM(C_ORDERLINE.QTYORDERED*C_CURRENCY_CONVERT(C_ORDERLINE.PRICEACTUAL, COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), v_BaseCurrency, TO_DATE(COALESCE(C_ORDERLINE.DATEORDERED, C_ORDER.DATEORDERED, Cur_InOutLine.MOVEMENTDATE)), NULL, C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID)) /SUM(C_ORDERLINE.QTYORDERED) END, + SELECT CASE WHEN SUM(C_ORDERLINE.QTYORDERED) <=0 THEN 0 ELSE SUM(C_ORDERLINE.QTYORDERED*C_CURRENCY_CONVERT_PRECISION(C_ORDERLINE.PRICEACTUAL, COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), v_BaseCurrency, TO_DATE(COALESCE(C_ORDERLINE.DATEORDERED, C_ORDER.DATEORDERED, Cur_InOutLine.MOVEMENTDATE)), NULL, C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID, 'C')) /SUM(C_ORDERLINE.QTYORDERED) END, CASE WHEN SUM(C_ORDERLINE.QTYORDERED) <=0 THEN 0 ELSE COUNT(C_ORDERLINE.C_ORDERLINE_ID) END INTO v_Price, v_Count FROM M_MATCHPO, C_ORDERLINE, C_ORDER @@ -200,7 +200,7 @@ END IF; IF(v_Count=0) THEN FOR Cur_PriceList IN - (SELECT C_CURRENCY_CONVERT(M_PRODUCTPRICE.PRICESTD, M_PRICELIST.C_CURRENCY_ID, v_BaseCurrency, TO_DATE(Cur_InOutLine.MOVEMENTDATE), NULL, M_PRODUCTPRICE.AD_CLIENT_ID, M_PRODUCTPRICE.AD_ORG_ID) AS PRICESTD + (SELECT C_CURRENCY_CONVERT_PRECISION(M_PRODUCTPRICE.PRICESTD, M_PRICELIST.C_CURRENCY_ID, v_BaseCurrency, TO_DATE(Cur_InOutLine.MOVEMENTDATE), NULL, M_PRODUCTPRICE.AD_CLIENT_ID, M_PRODUCTPRICE.AD_ORG_ID, 'C') AS PRICESTD FROM M_PRICELIST, M_PRICELIST_VERSION, M_PRODUCTPRICE WHERE M_PRICELIST_VERSION.M_PRICELIST_ID= (SELECT po_pricelist_id @@ -235,11 +235,11 @@ Ad_Sequence_Nextno('M_Costing'), now(), v_User, now(), v_User, Cur_InOutLine.AD_CLIENT_ID, Cur_InOutLine.AD_ORG_ID, Cur_InOutLine.M_PRODUCT_ID, COALESCE(v_Date, TO_DATE('31-12-9999', 'DD-MM-YYYY')), Cur_InOutLine.MOVEMENTDATE, 'N', Cur_InOutLine.M_INOUTLINE_ID, - NULL, Cur_InOutLine.MOVEMENTQTY, v_Price, COALESCE(v_Qty, 0) + Cur_InOutLine.MOVEMENTQTY + v_CumQty,( - CASE + NULL, Cur_InOutLine.MOVEMENTQTY, v_Price, COALESCE(v_Qty, 0) + Cur_InOutLine.MOVEMENTQTY + v_CumQty, + ROUND((CASE WHEN v_Qty+v_CumQty<0 THEN v_Price WHEN (v_Qty+Cur_InOutLine.MOVEMENTQTY+v_CumQty) = 0 THEN 0 - ELSE (((v_Qty+v_CumQty)*COALESCE(v_Cost, v_Price)) +(Cur_InOutLine.MOVEMENTQTY *v_Price)) /(v_Qty+Cur_InOutLine.MOVEMENTQTY+v_CumQty) END), + ELSE (((v_Qty+v_CumQty)*COALESCE(v_Cost, v_Price)) +(Cur_InOutLine.MOVEMENTQTY *v_Price)) /(v_Qty+Cur_InOutLine.MOVEMENTQTY+v_CumQty) END), c_get_currency_precision(v_basecurrency, 'C')), 'AV', 'N' ) ; ------------------------------------------------------------------------------ Download Intel® Parallel Studio Eval Try the new software tools for yourself. Speed compiling, find bugs proactively, and fine-tune applications for parallel performance. See why Intel Parallel Studio got high marks during beta. http://p.sf.net/sfu/intel-sw-dev _______________________________________________ Openbravo-commits mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/openbravo-commits
