0015070: Generate Average Costs per Organization - Openbravo Issue Tracking System

diff -r 552b095f5c6c -r 3f092bb5d4eb src-db/database/model/functions/M_GENERATE_AVERAGE_COSTS.xml --- a/src-db/database/model/functions/M_GENERATE_AVERAGE_COSTS.xml Wed Dec 16 19:00:45 2009 +0100 +++ b/src-db/database/model/functions/M_GENERATE_AVERAGE_COSTS.xml Tue Dec 22 17:07:41 2009 +0100 @@ -36,6 +36,7 @@ v_count NUMBER; v_Deleted NUMBER; v_ProductOld VARCHAR2(32); + v_OrgOld VARCHAR2(32); v_Client VARCHAR2(32); v_BaseCurrency VARCHAR2(32); -- Parameter @@ -52,6 +53,7 @@ v_Price:=0; v_CumQty:=0; v_ProductOld:='0'; + v_OrgOld:='0'; -- Update AD_PInstance DBMS_OUTPUT.PUT_LINE('Updating PInstance - Processing ' || p_PInstance_ID) ; v_ResultStr:='PInstanceNotFound'; @@ -86,7 +88,7 @@ FROM M_COSTING WHERE ISPERMANENT='N' AND ISMANUAL='N' - AND COSTTYPE='AV' + AND COSTTYPE <> 'ST' AND ISPRODUCTION = 'N' AND AD_CLIENT_ID = v_Client; v_rowcount:=SQL%ROWCOUNT; @@ -95,11 +97,13 @@ DBMS_OUTPUT.PUT_LINE(' deleted=' || v_rowcount) ; --RECALCULATE THE DATETO OF MANUAL COSTS FOR Cur_ManualCost IN - (SELECT M_COSTING_ID, M_PRODUCT_ID, DATEFROM - FROM M_COSTING - WHERE AD_CLIENT_ID = v_Client - ORDER BY M_PRODUCT_ID, DATEFROM DESC) LOOP - IF (v_ProductOld <> Cur_ManualCost.M_PRODUCT_ID) THEN + (SELECT M_COSTING_ID, M_PRODUCT.M_PRODUCT_ID, DATEFROM, CASE WHEN M_PRODUCT.COSTTYPE = 'AVO' THEN M_COSTING.AD_ORG_ID ELSE '0' END AS AD_ORG_ID + FROM M_COSTING, M_PRODUCT + WHERE M_COSTING.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID + AND M_COSTING.AD_CLIENT_ID = v_Client + AND ISPRODUCTION = 'N' + ORDER BY M_PRODUCT.M_PRODUCT_ID, M_COSTING.AD_ORG_ID, DATEFROM DESC, M_COSTING.AD_ORG_ID) LOOP + IF (v_ProductOld <> Cur_ManualCost.M_PRODUCT_ID AND v_OrgOld <> Cur_ManualCost.AD_ORG_ID) THEN v_Date := TO_DATE('31-12-9999', 'DD-MM-YYYY'); END IF; UPDATE M_COSTING @@ -107,16 +111,19 @@ WHERE M_COSTING_ID = Cur_ManualCost.M_Costing_ID; v_Date := cur_manualcost.DATEFROM; v_ProductOld := cur_manualcost.M_PRODUCT_ID; + v_OrgOld := Cur_ManualCost.AD_ORG_ID; END LOOP; v_ProductOld:='0'; + v_OrgOld:='0'; v_DateOld:=TO_DATE('01-01-1900', 'DD-MM-YYYY') ; FOR Cur_InOutLine IN (SELECT M_INOUTLINE.M_INOUTLINE_ID, M_INOUTLINE.MOVEMENTQTY, M_PRODUCT.M_PRODUCT_ID, M_INOUT.MOVEMENTDATE, - M_INOUTLINE.AD_CLIENT_ID, M_INOUTLINE.AD_ORG_ID, M_INOUT.C_BPARTNER_ID + M_INOUTLINE.AD_CLIENT_ID, CASE WHEN M_PRODUCT.COSTTYPE <> 'AVO' THEN '0' ELSE M_INOUTLINE.AD_ORG_ID END AS AD_ORG_ID, + M_PRODUCT.COSTTYPE, CASE WHEN M_PRODUCT.COSTTYPE = 'AVO' THEN M_INOUTLINE.AD_ORG_ID ELSE '0' END, M_INOUT.C_BPARTNER_ID FROM M_INOUT, M_INOUTLINE, M_PRODUCT WHERE M_INOUT.M_INOUT_ID=M_INOUTLINE.M_INOUT_ID AND M_INOUTLINE.M_PRODUCT_ID=M_PRODUCT.M_PRODUCT_ID - AND M_PRODUCT.COSTTYPE='AV' + AND M_PRODUCT.COSTTYPE IN ('AV', 'AVO') AND M_INOUT.ISSOTRX='N' AND M_INOUT.PROCESSED='Y' AND M_INOUTLINE.MOVEMENTQTY>0 @@ -124,11 +131,11 @@ AND M_INOUT.AD_CLIENT_ID = v_Client AND NOT EXISTS (SELECT 1 FROM M_COSTING WHERE M_INOUTLINE_ID=M_INOUTLINE.M_INOUTLINE_ID) - ORDER BY M_PRODUCT.M_PRODUCT_ID, M_INOUT.MOVEMENTDATE, M_INOUTLINE.MOVEMENTQTY DESC + ORDER BY M_PRODUCT.M_PRODUCT_ID, M_INOUT.AD_ORG_ID, M_INOUT.MOVEMENTDATE, M_INOUTLINE.MOVEMENTQTY DESC ) LOOP v_ResultStr:='new albaran: '||Cur_InOutLine.M_INOUTLINE_ID; - IF(Cur_InOutLine.M_PRODUCT_ID<>v_ProductOld) THEN + IF(Cur_InOutLine.M_PRODUCT_ID<>v_ProductOld AND Cur_InOutLine.AD_ORG_ID <> v_OrgOld) THEN v_Date:=NULL; v_CumQty:=0; ELSIF (Cur_InOutLine.MOVEMENTDATE<>v_DateOld) THEN @@ -138,7 +145,8 @@ INTO v_Count FROM M_COSTING WHERE M_PRODUCT_ID=Cur_InOutLine.M_PRODUCT_ID - AND AD_CLIENT_ID = v_Client; + AND AD_CLIENT_ID = v_Client + AND AD_ORG_ID = CASE WHEN Cur_InOutLine.COSTTYPE <> 'AVO' THEN AD_ORG_ID ELSE Cur_InOutLine.AD_ORG_ID END; IF(v_Count>0) THEN FOR Cur_ProdCost IN (SELECT COST, DATETO, M_COSTING_ID @@ -146,6 +154,7 @@ WHERE M_PRODUCT_ID=Cur_InOutLine.M_PRODUCT_ID AND DATEFROM <= Cur_InOutLine.MOVEMENTDATE AND AD_CLIENT_ID = v_Client + AND AD_ORG_ID = CASE WHEN Cur_InOutLine.COSTTYPE <> 'AVO' THEN AD_ORG_ID ELSE Cur_InOutLine.AD_ORG_ID END ORDER BY DATETO DESC, M_COSTING_ID ) LOOP @@ -161,6 +170,7 @@ FROM M_COSTING WHERE M_PRODUCT_ID=Cur_InOutLine.M_PRODUCT_ID AND DATEFROM > Cur_InOutLine.MOVEMENTDATE + AND AD_ORG_ID = CASE WHEN Cur_InOutLine.COSTTYPE <> 'AVO' THEN AD_ORG_ID ELSE Cur_InOutLine.AD_ORG_ID END AND AD_CLIENT_ID = v_Client; END IF; ELSE @@ -172,6 +182,7 @@ FROM M_TRANSACTION WHERE M_PRODUCT_ID=Cur_InOutLine.M_PRODUCT_ID AND MOVEMENTDATE<Cur_InOutLine.MOVEMENTDATE + AND AD_ORG_ID = CASE WHEN Cur_InOutLine.COSTTYPE <> 'AVO' THEN AD_ORG_ID ELSE Cur_InOutLine.AD_ORG_ID END AND AD_CLIENT_ID = v_Client; ELSE v_Qty:=0; @@ -184,6 +195,7 @@ WHERE M_MATCHINV.C_INVOICELINE_ID=C_INVOICELINE.C_INVOICELINE_ID AND M_MATCHINV.M_INOUTLINE_ID=Cur_InOutLine.M_INOUTLINE_ID AND C_INVOICE.C_INVOICE_ID = C_INVOICELINE.C_INVOICE_ID + AND C_INVOICELINE.AD_ORG_ID = CASE WHEN Cur_InOutLine.COSTTYPE <> 'AVO' THEN C_INVOICELINE.AD_ORG_ID ELSE Cur_InOutLine.AD_ORG_ID END AND QTYINVOICED<>0; IF(v_Count=0) THEN v_ResultStr:='order albaran: '||Cur_InOutLine.M_INOUTLINE_ID; @@ -195,6 +207,7 @@ AND M_MATCHPO.C_INVOICELINE_ID IS NULL AND M_MATCHPO.M_INOUTLINE_ID=Cur_InOutLine.M_INOUTLINE_ID AND C_ORDER.C_ORDER_ID = C_ORDERLINE.C_ORDER_ID + AND C_ORDERLINE.AD_ORG_ID = CASE WHEN Cur_InOutLine.COSTTYPE <> 'AVO' THEN C_ORDERLINE.AD_ORG_ID ELSE Cur_InOutLine.AD_ORG_ID END AND C_ORDERLINE.QTYORDERED<>0; END IF; @@ -240,12 +253,13 @@ 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), - 'AV', 'N' + Cur_InOutLine.COSTTYPE, CASE WHEN Cur_InOutLine.COSTTYPE <> 'AVO' THEN 'N' ELSE 'Y' END ) ; v_CumQty:=v_CumQty + Cur_InOutLine.MOVEMENTQTY; v_DateOld:=Cur_InOutLine.MOVEMENTDATE; v_ProductOld:=Cur_InOutLine.M_PRODUCT_ID; + v_OrgOld:=Cur_InOutLine.AD_ORG_ID; END LOOP; --<<FINISH_PROCESS>> -- Update AD_PInstance diff -r 552b095f5c6c -r 3f092bb5d4eb src-db/database/model/functions/M_GET_PRODUCT_COST.xml --- a/src-db/database/model/functions/M_GET_PRODUCT_COST.xml Wed Dec 16 19:00:45 2009 +0100 +++ b/src-db/database/model/functions/M_GET_PRODUCT_COST.xml Tue Dec 22 17:07:41 2009 +0100 @@ -4,6 +4,9 @@ <parameter name="p_product_id" type="VARCHAR" mode="in"> <default/> </parameter> + <parameter name="p_organization_id" type="VARCHAR" mode="in"> + <default/> + </parameter> <parameter name="p_movementdate" type="TIMESTAMP" mode="in"> <default/> </parameter> @@ -29,28 +32,34 @@ v_Cost NUMBER; v_Count NUMBER; v_CostType VARCHAR2(60) ; + v_Organization_Id VARCHAR2(60) ; TYPE RECORD IS REF CURSOR; Cur_Cost RECORD; BEGIN v_CostType := p_CostType; + v_Organization_Id := p_organization_id; IF(p_Product_ID IS NULL) THEN RETURN 0; END IF; IF(p_MovementDate IS NULL) THEN RETURN 0; END IF; - /*IF (v_CostType IS NULL) THEN + IF (v_CostType IS NULL) THEN SELECT COSTTYPE INTO v_CostType FROM M_PRODUCT WHERE M_PRODUCT_ID=p_Product_ID; - END IF;*/ + END IF; + IF (v_CostType <> 'AVO') THEN + v_Organization_Id := NULL; + END IF; SELECT COUNT(*) INTO v_Count FROM M_COSTING WHERE p_MovementDate BETWEEN DATEFROM AND DATETO AND M_PRODUCT_ID = p_Product_ID AND(v_CostType IS NULL - OR v_CostType = COSTTYPE) ; + OR v_CostType = COSTTYPE) + AND AD_ORG_ID = COALESCE(v_Organization_Id, AD_ORG_ID); IF(v_Count = 0) THEN RETURN 0; ELSE @@ -62,6 +71,7 @@ AND M_PRODUCT_ID = p_Product_ID AND(v_CostType IS NULL OR v_CostType = COSTTYPE) + AND AD_ORG_ID = COALESCE(v_Organization_Id, AD_ORG_ID) ORDER BY DATEFROM, DATETO DESC ) diff -r 552b095f5c6c -r 3f092bb5d4eb src-db/database/sourcedata/AD_REF_LIST.xml --- a/src-db/database/sourcedata/AD_REF_LIST.xml Wed Dec 16 19:00:45 2009 +0100 +++ b/src-db/database/sourcedata/AD_REF_LIST.xml Tue Dec 22 17:07:41 2009 +0100 @@ -5843,7 +5843,8 @@  <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID>  <ISACTIVE><![CDATA[Y]]></ISACTIVE>  <VALUE><![CDATA[AV]]></VALUE> - <NAME><![CDATA[Average]]></NAME> + <NAME><![CDATA[Global Average]]></NAME> + <DESCRIPTION><![CDATA[Average costing per Client.]]></DESCRIPTION>  <AD_REFERENCE_ID><![CDATA[800025]]></AD_REFERENCE_ID>  <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID> </AD_REF_LIST> @@ -5854,7 +5855,8 @@  <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID>  <ISACTIVE><![CDATA[Y]]></ISACTIVE>  <VALUE><![CDATA[ST]]></VALUE> - <NAME><![CDATA[Standard]]></NAME> + <NAME><![CDATA[Global Standard]]></NAME> + <DESCRIPTION><![CDATA[Standard costing per Client.]]></DESCRIPTION>  <AD_REFERENCE_ID><![CDATA[800025]]></AD_REFERENCE_ID>  <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID> </AD_REF_LIST> @@ -8853,6 +8855,18 @@  <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID> </AD_REF_LIST> +<AD_REF_LIST> + <AD_REF_LIST_ID><![CDATA[90DF9E2DFEAD442CB518FDC462A6EAF5]]></AD_REF_LIST_ID> + <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID> + <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID> + <ISACTIVE><![CDATA[Y]]></ISACTIVE> + <VALUE><![CDATA[AVO]]></VALUE> + <NAME><![CDATA[Organization Average]]></NAME> + <DESCRIPTION><![CDATA[Average costing per Organization.]]></DESCRIPTION> + <AD_REFERENCE_ID><![CDATA[800025]]></AD_REFERENCE_ID> + <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID> +</AD_REF_LIST> + <AD_REF_LIST>  <AD_REF_LIST_ID><![CDATA[927411F7E68A436BB8230ADD159782E4]]></AD_REF_LIST_ID>  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID> diff -r 552b095f5c6c -r 3f092bb5d4eb src/org/openbravo/erpCommon/ad_forms/DocLine_Material.java --- a/src/org/openbravo/erpCommon/ad_forms/DocLine_Material.java Wed Dec 16 19:00:45 2009 +0100 +++ b/src/org/openbravo/erpCommon/ad_forms/DocLine_Material.java Tue Dec 22 17:07:41 2009 +0100 @@ -66,7 +66,7 @@ * @return costs */ public String getProductCosts(String date, AcctSchema as, ConnectionProvider conn, Connection con) { - return p_productInfo.getProductCosts(date, "", as, conn, con); + return p_productInfo.getProductCosts(date, "", m_AD_Org_ID, as, conn, con); } // getProductCosts /** diff -r 552b095f5c6c -r 3f092bb5d4eb src/org/openbravo/erpCommon/ad_forms/ProductInfo.java --- a/src/org/openbravo/erpCommon/ad_forms/ProductInfo.java Wed Dec 16 19:00:45 2009 +0100 +++ b/src/org/openbravo/erpCommon/ad_forms/ProductInfo.java Tue Dec 22 17:07:41 2009 +0100 @@ -46,6 +46,7 @@ public String m_C_UOM_ID = ""; public String m_qty = "0"; + public String m_costType = ""; /** * Get Product Info (Service, Revenue Recognition). automatically called by constructor @@ -62,6 +63,7 @@ try { data = ProductInfoData.select(conn, m_M_Product_ID); if (data.length == 1) { + m_costType = data[0].costtype; m_productType = data[0].producttype; m_ProductCategory = data[0].value; m_C_UOM_ID = data[0].cUomId; @@ -235,17 +237,15 @@ /** * Get Total Costs in Accounting Schema Currency * - * @param as - * accounting schema * @return cost or null, if qty or costs cannot be determined */ - public String getProductCosts(String date, String strQty, AcctSchema as, ConnectionProvider conn, - Connection con) { - if (m_qty == null || m_qty.equals("")) { + public String getProductCosts(String date, String strQty, String stradOrgId, AcctSchema as, + ConnectionProvider conn, Connection con) { + if ((m_qty == null || m_qty.equals("")) && (strQty == null || strQty.equals(""))) { log4jProductInfo.debug("getProductCosts - No Qty"); return null; } - BigDecimal cost = new BigDecimal(getProductItemCost(date, as, "", conn, con)); + BigDecimal cost = new BigDecimal(getProductItemCost(date, as, stradOrgId, conn, con)); if (cost == null) { log4jProductInfo.debug("getProductCosts - No Costs"); return null; @@ -261,13 +261,14 @@ } // getProductCosts - public String getProductItemCost(String date, AcctSchema as, String costType, + public String getProductItemCost(String date, AcctSchema as, String stradOrgId, ConnectionProvider conn, Connection con) { String cost = ""; log4jProductInfo.debug("getProductItemCost - m_M_Product_ID(" + m_M_Product_ID + ") - date(" + date + ")"); try { - cost = ProductInfoData.selectProductAverageCost(conn, m_M_Product_ID, date); + cost = ProductInfoData.selectProductAverageCost(conn, m_M_Product_ID, stradOrgId, date, + m_costType); } catch (ServletException e) { log4jProductInfo.warn(e); } @@ -277,8 +278,6 @@ /** * Get PO Cost from Purchase Info - and convert it to AcctSchema Currency * - * @param as - * accounting schema * @return po cost */ private String getPOCost(AcctSchema as, ConnectionProvider conn) { diff -r 552b095f5c6c -r 3f092bb5d4eb src/org/openbravo/erpCommon/ad_forms/ProductInfo_data.xsql --- a/src/org/openbravo/erpCommon/ad_forms/ProductInfo_data.xsql Wed Dec 16 19:00:45 2009 +0100 +++ b/src/org/openbravo/erpCommon/ad_forms/ProductInfo_data.xsql Tue Dec 22 17:07:41 2009 +0100 @@ -26,11 +26,11 @@ <SqlMethodComment></SqlMethodComment> <Sql> <![CDATA[ - SELECT P.PRODUCTTYPE, PC.VALUE,P.C_UOM_ID,P.AD_CLIENT_ID,P.AD_ORG_ID, + SELECT P.PRODUCTTYPE, PC.VALUE,P.C_UOM_ID,P.AD_CLIENT_ID,P.AD_ORG_ID, P.COSTTYPE, '' AS REVENUE, '' AS EXPENSE, '' AS ASSET, '' AS COGS, '' AS PURCHASEPRICEVARIANCE, '' AS INVOICEPRICEVARIANCE, '' AS DISCOUNTREC, '' AS DISCOUNTGRANT, '' AS CONVERTED, '' AS CURRENTCOSTPRICE, '' AS COSTAVERAGE, '' AS PRICELASTPO, '' AS COSTSTANDARD, '' AS C_CURRENCY_ID, '' AS PRICELIST, '' AS PRICESTD, '' AS PRICELIMIT, '' AS PRICEPO - FROM M_PRODUCT_CATEGORY PC, M_PRODUCT P + FROM M_PRODUCT_CATEGORY PC, M_PRODUCT P WHERE PC.M_PRODUCT_CATEGORY_ID=P.M_PRODUCT_CATEGORY_ID AND P.M_PRODUCT_ID=? ]]> @@ -41,11 +41,11 @@ <SqlMethodComment></SqlMethodComment> <Sql> <![CDATA[ - SELECT P_Revenue_Acct as revenue, P_Expense_Acct as expense, P_Asset_Acct as asset, P_Cogs_Acct as cogs, - P_PurchasePriceVariance_Acct as purchasepricevariance, P_InvoicePriceVariance_Acct as invoicepricevariance, + SELECT P_Revenue_Acct as revenue, P_Expense_Acct as expense, P_Asset_Acct as asset, P_Cogs_Acct as cogs, + P_PurchasePriceVariance_Acct as purchasepricevariance, P_InvoicePriceVariance_Acct as invoicepricevariance, P_TradeDiscountRec_Acct as discountrec, P_TradeDiscountGrant_Acct as discountgrant FROM M_Product_Acct - WHERE M_Product_ID=? + WHERE M_Product_ID=? AND C_AcctSchema_ID=? ]]> </Sql> @@ -56,12 +56,12 @@ <SqlMethodComment></SqlMethodComment> <Sql> <![CDATA[ - SELECT P_Revenue_Acct as revenue, P_Expense_Acct as expense, P_Asset_Acct as asset, P_Cogs_Acct as cogs, - P_PurchasePriceVariance_Acct as purchasepricevariance, P_InvoicePriceVariance_Acct as invoicepricevariance, + SELECT P_Revenue_Acct as revenue, P_Expense_Acct as expense, P_Asset_Acct as asset, P_Cogs_Acct as cogs, + P_PurchasePriceVariance_Acct as purchasepricevariance, P_InvoicePriceVariance_Acct as invoicepricevariance, P_TradeDiscountRec_Acct as discountrec, P_TradeDiscountGrant_Acct as discountgrant - FROM M_Product_Category pc, M_Product_Category_Acct pca + FROM M_Product_Category pc, M_Product_Category_Acct pca WHERE pc.M_Product_Category_ID=pca.M_Product_Category_ID - AND pca.C_AcctSchema_ID=? + AND pca.C_AcctSchema_ID=? ORDER BY pc.IsDefault DESC, pc.Created ]]> </Sql> @@ -80,16 +80,18 @@ <SqlMethod name="selectProductAverageCost" type="preparedStatement" return="string"> <SqlMethodComment></SqlMethodComment> <Sql> - SELECT COALESCE(M_GET_PRODUCT_COST(?, TO_DATE(?), TO_CHAR(NULL)),0) FROM DUAL + SELECT COALESCE(M_GET_PRODUCT_COST(?, ?, TO_DATE(?), TO_CHAR(?)),0) FROM DUAL </Sql> <Parameter name="M_Product_ID"/> + <Parameter name="AD_Org_ID"/> <Parameter name="date"/> + <Parameter name="costType"/> </SqlMethod> <SqlMethod name="selectPriceList" type="preparedStatement" return="multiple"> <SqlMethodComment></SqlMethodComment> <Sql> - SELECT pl.C_Currency_ID, pp.PriceList, pp.PriceStd, pp.PriceLimit - FROM M_PriceList pl, M_PriceList_Version plv, M_ProductPrice pp + SELECT pl.C_Currency_ID, pp.PriceList, pp.PriceStd, pp.PriceLimit + FROM M_PriceList pl, M_PriceList_Version plv, M_ProductPrice pp WHERE pl.M_PriceList_ID = plv.M_PriceList_ID AND plv.M_PriceList_Version_ID = pp.M_PriceList_Version_ID AND pp.M_Product_ID=? @@ -102,9 +104,9 @@ <SqlMethod name="selectPOCost" type="preparedStatement" return="multiple"> <SqlMethodComment></SqlMethodComment> <Sql> - SELECT C_Currency_ID, PriceList,PricePO,PriceLastPO - FROM M_Product_PO - WHERE M_Product_ID = ? + SELECT C_Currency_ID, PriceList,PricePO,PriceLastPO + FROM M_Product_PO + WHERE M_Product_ID = ? ORDER BY IsCurrentVendor DESC </Sql> <Parameter name="M_Product_ID"/> diff -r 552b095f5c6c -r 3f092bb5d4eb src/org/openbravo/erpCommon/ad_reports/ReportValuationStock_data.xsql --- a/src/org/openbravo/erpCommon/ad_reports/ReportValuationStock_data.xsql Wed Dec 16 19:00:45 2009 +0100 +++ b/src/org/openbravo/erpCommon/ad_reports/ReportValuationStock_data.xsql Tue Dec 22 17:07:41 2009 +0100 @@ -5,15 +5,15 @@ * 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 + * 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) 2001-2008 Openbravo SL - * All Rights Reserved. + * under the License. + * The Original Code is Openbravo ERP. + * The Initial Developer of the Original Code is Openbravo SL + * All portions are Copyright (C) 2001-2009 Openbravo SL + * All Rights Reserved. * Contributor(s): ______________________________________. ************************************************************************ --> @@ -28,24 +28,25 @@ <SqlMethodComment></SqlMethodComment> <Sql> <![CDATA[ - SELECT CATEGORY_NAME, M_PRODUCT_ID, PRODUCT_NAME, QTY, UOM_NAME, AVERAGE_COST, TOTAL_COST FROM ( - SELECT M_PRODUCT_CATEGORY.NAME AS CATEGORY_NAME, A.M_PRODUCT_ID, AD_COLUMN_IDENTIFIER(TO_CHAR('M_Product'), TO_CHAR(M_PRODUCT.M_PRODUCT_ID), TO_CHAR(?)) AS PRODUCT_NAME, - A.QTY, C_UOM.NAME AS UOM_NAME, - C_CURRENCY_CONVERT(M_GET_PRODUCT_COST(A.M_PRODUCT_ID,TO_DATE(?), NULL), ?, ?, COALESCE(MC.DATEFROM,TO_DATE(?)), NULL, MC.AD_CLIENT_ID, MC.AD_ORG_ID) AS AVERAGE_COST, - A.QTY * C_CURRENCY_CONVERT(M_GET_PRODUCT_COST(A.M_PRODUCT_ID,TO_DATE(?), NULL), ?, ?, COALESCE(MC.DATEFROM,TO_DATE(?)), NULL, MC.AD_CLIENT_ID, MC.AD_ORG_ID) AS TOTAL_COST + SELECT CATEGORY_NAME, M_PRODUCT_ID, PRODUCT_NAME, SUM(QTY) AS QTY, UOM_NAME, SUM(QTY*AVERAGE_COST)/SUM(QTY) AS AVERAGE_COST, SUM(TOTAL_COST) AS TOTAL_COST FROM ( + SELECT M_PRODUCT_CATEGORY.NAME AS CATEGORY_NAME, A.M_PRODUCT_ID, AD_COLUMN_IDENTIFIER(TO_CHAR('M_Product'), TO_CHAR(M_PRODUCT.M_PRODUCT_ID), TO_CHAR(?)) AS PRODUCT_NAME, + A.QTY, C_UOM.NAME AS UOM_NAME, + C_CURRENCY_CONVERT(M_GET_PRODUCT_COST(A.M_PRODUCT_ID, A.AD_ORG_ID,TO_DATE(?), M_PRODUCT.COSTTYPE), ?, ?, COALESCE(MC.DATEFROM,TO_DATE(?)), NULL, MC.AD_CLIENT_ID, MC.AD_ORG_ID) AS AVERAGE_COST, + A.QTY * C_CURRENCY_CONVERT(M_GET_PRODUCT_COST(A.M_PRODUCT_ID, A.AD_ORG_ID,TO_DATE(?), M_PRODUCT.COSTTYPE), ?, ?, COALESCE(MC.DATEFROM,TO_DATE(?)), NULL, MC.AD_CLIENT_ID, MC.AD_ORG_ID) AS TOTAL_COST FROM M_PRODUCT_CATEGORY, - (SELECT M_PRODUCT_ID, SUM(MOVEMENTQTY) AS QTY, C_UOM_ID FROM M_TRANSACTION, M_LOCATOR + (SELECT M_PRODUCT_ID, SUM(MOVEMENTQTY) AS QTY, C_UOM_ID, M_TRANSACTION.AD_ORG_ID + FROM M_TRANSACTION, M_LOCATOR WHERE M_TRANSACTION.M_LOCATOR_ID = M_LOCATOR.M_LOCATOR_ID AND MOVEMENTDATE < to_date(?) AND M_LOCATOR.M_WAREHOUSE_ID = ? - GROUP BY M_PRODUCT_ID, C_UOM_ID) A, C_UOM, + GROUP BY M_PRODUCT_ID, C_UOM_ID, M_TRANSACTION.AD_ORG_ID) A, C_UOM, M_PRODUCT LEFT JOIN M_COSTING MC ON MC.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID WHERE A.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID AND A.C_UOM_ID = C_UOM.C_UOM_ID AND M_PRODUCT.M_PRODUCT_CATEGORY_ID = M_PRODUCT_CATEGORY.M_PRODUCT_CATEGORY_ID AND 1=1 AND A.QTY <>0) ZZ - GROUP BY M_PRODUCT_ID, CATEGORY_NAME , PRODUCT_NAME, QTY, UOM_NAME, AVERAGE_COST, TOTAL_COST + GROUP BY M_PRODUCT_ID, CATEGORY_NAME , PRODUCT_NAME, UOM_NAME ORDER BY CATEGORY_NAME, PRODUCT_NAME ]]></Sql> <Field name="rownum" value="count"/>

Issue History
Date Modified	Username	Field	Change
2010-10-28 15:13	networkb	New Issue
2010-10-28 15:13	networkb	Assigned To	=> adrianromero
2010-10-28 15:13	networkb	Issue generated from	0012346
2010-10-28 15:13	networkb	Relationship added	related to 0012346
2010-11-08 17:07	adrianromero	Note Added: 0032475
2010-11-08 18:25	adrianromero	File Added: avgcostsbyorg.diff
2010-11-08 18:28	adrianromero	Note Added: 0032482
2010-11-08 18:28	adrianromero	Status	new => closed
2010-11-08 18:28	adrianromero	Resolution	open => no change required
2010-11-08 18:29	adrianromero	Relationship added	related to 0015007
2010-11-09 22:46	anonymous	sf_bug_id	0 => 3106264
2011-01-11 12:42	adrianromero	Type	defect => backport
2011-01-11 12:43	adrianromero	Issue cloned	0015604
2011-01-11 12:43	adrianromero	Relationship added	depends on 0015604

Notes
(0032475) adrianromero (manager) 2010-11-08 17:07	This issue is related to the following project: http://wiki.openbravo.com/wiki/Projects/Org_based_avg_costing [^]

(0032482) adrianromero (manager) 2010-11-08 18:28	Openbravo ERP always calculate costs by client, not by organization. The attached diff adds a new cost option that allows to calculate costs by organization. This diff is part of the ongoing project http://wiki.openbravo.com/wiki/Projects/Org_based_avg_costing [^] but that will not be merged into the main branch until finished. In the mean time, the attached diff does the job.