Attached Files | avgcostsbyorg.diff [^] (23,156 bytes) 2010-11-08 18:25 [Show Content] [Hide Content]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 @@
<!--800069--> <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID>
<!--800069--> <ISACTIVE><![CDATA[Y]]></ISACTIVE>
<!--800069--> <VALUE><![CDATA[AV]]></VALUE>
-<!--800069--> <NAME><![CDATA[Average]]></NAME>
+<!--800069--> <NAME><![CDATA[Global Average]]></NAME>
+<!--800069--> <DESCRIPTION><![CDATA[Average costing per Client.]]></DESCRIPTION>
<!--800069--> <AD_REFERENCE_ID><![CDATA[800025]]></AD_REFERENCE_ID>
<!--800069--> <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID>
<!--800069--></AD_REF_LIST>
@@ -5854,7 +5855,8 @@
<!--800070--> <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID>
<!--800070--> <ISACTIVE><![CDATA[Y]]></ISACTIVE>
<!--800070--> <VALUE><![CDATA[ST]]></VALUE>
-<!--800070--> <NAME><![CDATA[Standard]]></NAME>
+<!--800070--> <NAME><![CDATA[Global Standard]]></NAME>
+<!--800070--> <DESCRIPTION><![CDATA[Standard costing per Client.]]></DESCRIPTION>
<!--800070--> <AD_REFERENCE_ID><![CDATA[800025]]></AD_REFERENCE_ID>
<!--800070--> <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID>
<!--800070--></AD_REF_LIST>
@@ -8853,6 +8855,18 @@
<!--8F5FF4C1DAE84C0A8B6B3115FEC8D206--> <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID>
<!--8F5FF4C1DAE84C0A8B6B3115FEC8D206--></AD_REF_LIST>
+<!--90DF9E2DFEAD442CB518FDC462A6EAF5--><AD_REF_LIST>
+<!--90DF9E2DFEAD442CB518FDC462A6EAF5--> <AD_REF_LIST_ID><![CDATA[90DF9E2DFEAD442CB518FDC462A6EAF5]]></AD_REF_LIST_ID>
+<!--90DF9E2DFEAD442CB518FDC462A6EAF5--> <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
+<!--90DF9E2DFEAD442CB518FDC462A6EAF5--> <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID>
+<!--90DF9E2DFEAD442CB518FDC462A6EAF5--> <ISACTIVE><![CDATA[Y]]></ISACTIVE>
+<!--90DF9E2DFEAD442CB518FDC462A6EAF5--> <VALUE><![CDATA[AVO]]></VALUE>
+<!--90DF9E2DFEAD442CB518FDC462A6EAF5--> <NAME><![CDATA[Organization Average]]></NAME>
+<!--90DF9E2DFEAD442CB518FDC462A6EAF5--> <DESCRIPTION><![CDATA[Average costing per Organization.]]></DESCRIPTION>
+<!--90DF9E2DFEAD442CB518FDC462A6EAF5--> <AD_REFERENCE_ID><![CDATA[800025]]></AD_REFERENCE_ID>
+<!--90DF9E2DFEAD442CB518FDC462A6EAF5--> <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID>
+<!--90DF9E2DFEAD442CB518FDC462A6EAF5--></AD_REF_LIST>
+
<!--927411F7E68A436BB8230ADD159782E4--><AD_REF_LIST>
<!--927411F7E68A436BB8230ADD159782E4--> <AD_REF_LIST_ID><![CDATA[927411F7E68A436BB8230ADD159782E4]]></AD_REF_LIST_ID>
<!--927411F7E68A436BB8230ADD159782E4--> <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"/>
|