Java > Open Source Codes > org > compiere > acct > ProductInfo


1 /******************************************************************************
2  * The contents of this file are subject to the Compiere License Version 1.1
3  * ("License"); You may not use this file except in compliance with the License
4  * You may obtain a copy of the License at http://www.compiere.org/license.html
5  * Software distributed under the License is distributed on an "AS IS" basis,
6  * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License for
7  * the specific language governing rights and limitations under the License.
8  * The Original Code is Compiere ERP & CRM Business Solution
9  * The Initial Developer of the Original Code is Jorg Janke and ComPiere, Inc.
10  * Portions created by Jorg Janke are Copyright (C) 1999-2001 Jorg Janke, parts
11  * created by ComPiere are Copyright (C) ComPiere, Inc.; All Rights Reserved.
12  * Contributor(s): ______________________________________.
13  *****************************************************************************/
14 package org.compiere.acct;
15
16 import java.sql.*;
17 import java.math.*;
18 import java.util.*;
19
20 import org.compiere.util.*;
21 import org.compiere.model.*;
22
23 /**
24  * Product Costing Information.
25  *
26  * @author Jorg Janke
27  * @version $Id: ProductInfo.java,v 1.19 2003/10/10 20:54:29 jjanke Exp $
28  */
29 public class ProductInfo
30 {
31     /**
32      * Constructor
33      * @param M_Product_ID Product
34      */
35     public ProductInfo (int M_Product_ID)
36     {
37         init (M_Product_ID);
38     } // ProductInfo
39
40     /** The Product Key */
41     private int m_M_Product_ID = 0;
42     // Product Info
43 private int m_AD_Client_ID = 0;
44     private int m_AD_Org_ID = 0;
45
46     private String m_productType = null;
47     private String m_ProductCategory = null;
48
49     private boolean m_isBOM = false;
50     private boolean m_isStocked = true;
51
52     private int m_C_RevenueRecognition_ID = 0;
53
54     private int m_C_UOM_ID = 0;
55     private BigDecimal m_qty = Env.ZERO;
56
57     /** Logger */
58     protected Logger log = Logger.getCLogger (getClass());
59
60     /**
61      * Get Product Info (Service, Revenue Recognition).
62      * automatically called by constructor
63      * @param M_Product_ID Product
64      */
65     private void init (int M_Product_ID)
66     {
67         m_M_Product_ID = M_Product_ID;
68         if (m_M_Product_ID == 0)
69             return;
70
71         String sql = "SELECT p.ProductType, pc.Value, " // 1..2
72 + "p.C_RevenueRecognition_ID,p.C_UOM_ID, " // 3..4
73 + "p.AD_Client_ID,p.AD_Org_ID, " // 5..6
74 + "p.IsBOM, p.IsStocked " // 7..8
75 + "FROM M_Product_Category pc"
76             + " INNER JOIN M_Product p ON (pc.M_Product_Category_ID=p.M_Product_Category_ID) "
77             + "WHERE p.M_Product_ID=?"; // #1
78 try
79         {
80             PreparedStatement pstmt = DB.prepareStatement(sql);
81             pstmt.setInt(1, m_M_Product_ID);
82             ResultSet rs = pstmt.executeQuery();
83             if (rs.next())
84             {
85                 m_productType = rs.getString(1);
86                 m_ProductCategory = rs.getString(2);
87                 m_C_RevenueRecognition_ID = rs.getInt(3);
88                 m_C_UOM_ID = rs.getInt(4);
89                 // reference
90 m_AD_Client_ID = rs.getInt(5);
91                 m_AD_Org_ID = rs.getInt(6);
92                 //
93 m_isBOM = "Y".equals(rs.getString(7));
94                 m_isStocked = "y".equals(rs.getString(8));
95             }
96             rs.close();
97             pstmt.close();
98         }
99         catch (SQLException e)
100         {
101             log.error ("init", e);
102         }
103     } // init
104
105     /**
106      * Is Product/Item
107      * @return true if product
108      */
109     public boolean isProduct()
110     {
111         return MProduct.PRODUCTTYPE_Item.equals(m_productType);
112     } // isProduct
113
114     /**
115      * Is it a BOM
116      * @return true if BOM
117      */
118     public boolean isBOM()
119     {
120         return m_isBOM;
121     } // isBOM
122
123     /**
124      * Is it stocked
125      * @return true if stocked
126      */
127     public boolean isStocked()
128     {
129         return m_isStocked;
130     } // isStocked
131
132     /**
133      * Is Service
134      * @return true if service
135      */
136     public boolean isService()
137     {
138         return MProduct.PRODUCTTYPE_Service.equals(m_productType);
139     } // isService
140
141     /**
142      * Get Product Category (Value)
143      * @return M_Product_Category_ID
144      */
145     public String getProductCategory()
146     {
147         return m_ProductCategory;
148     } // getProductCategory
149
150     /**
151      * Has Revenue Recognition
152      * @return true if product/service has revenue recognition
153      */
154     public boolean isRevenueRecognition()
155     {
156         return m_C_RevenueRecognition_ID != 0;
157     } // isRevenueRecognition
158
159     /**
160      * Get Revenue Recognition
161      * @return C_RevenueRecognition_ID
162      */
163     public int getC_RevenueRecognition_ID()
164     {
165         return m_C_RevenueRecognition_ID;
166     } // getC_RevenueRecognition_ID
167
168     /**
169      * Quantity UOM
170      * @return C_UOM_ID
171      */
172     public int getC_UOM_ID()
173     {
174         return m_C_UOM_ID;
175     } // getC_UOM_ID
176
177     /*************************************************************************/
178
179     /**
180      * Set Quantity in Storage UOM
181      * @param qty quantity
182      */
183     public void setQty (BigDecimal qty)
184     {
185         m_qty = qty;
186     } // setQty
187
188     /**
189      * Set Quantity in UOM
190      * @param qty quantity
191      * @param C_UOM_ID UOM
192      */
193     public void setQty (BigDecimal qty, int C_UOM_ID)
194     {
195         m_qty = UOMConversion.getConvertedQty(qty, C_UOM_ID, m_C_UOM_ID, true); // StdPrecision
196 if (qty != null && m_qty == null) // conversion error
197 {
198             log.error ("setQty - conversion error - set to " + qty);
199             m_qty = qty;
200         }
201     } // setQty
202
203     /**
204      * Get Qty in Storage UOM
205      * @return qty
206      */
207     public BigDecimal getQty()
208     {
209         return m_qty;
210     } // getQty
211
212     /*************************************************************************/
213
214     /** Product Revenue Acct */
215     public static final int ACCTTYPE_P_Revenue = 1;
216     /** Product Expense Acct */
217     public static final int ACCTTYPE_P_Expense = 2;
218     /** Product Asset Acct */
219     public static final int ACCTTYPE_P_Asset = 3;
220     /** Product COGS Acct */
221     public static final int ACCTTYPE_P_Cogs = 4;
222     /** Purchase Price Variance */
223     public static final int ACCTTYPE_P_PPV = 5;
224     /** Invoice Price Variance */
225     public static final int ACCTTYPE_P_IPV = 6;
226     /** Trade Discount Revenue */
227     public static final int ACCTTYPE_P_TDiscountRec = 7;
228     /** Trade Discount Costs */
229     public static final int ACCTTYPE_P_TDiscountGrant = 8;
230
231     /**
232      * Line Account from Product
233      *
234      * @param AcctType see ACCTTYPE_* (1..8)
235      * @param as Accounting Schema
236      * @return Requested Product Account
237      */
238     public Account getAccount(int AcctType, AcctSchema as)
239     {
240         if (AcctType < 1 || AcctType > 8)
241             return null;
242
243         // No Product - get Default from Product Category
244 if (m_M_Product_ID == 0)
245             return getAccountDefault(AcctType, as);
246
247         String sql = "SELECT P_Revenue_Acct, P_Expense_Acct, P_Asset_Acct, P_Cogs_Acct, " // 1..4
248 + "P_PurchasePriceVariance_Acct, P_InvoicePriceVariance_Acct, " // 5..6
249 + "P_TradeDiscountRec_Acct, P_TradeDiscountGrant_Acct " // 7..8
250 + "FROM M_Product_Acct "
251             + "WHERE M_Product_ID=? AND C_AcctSchema_ID=?";
252         //
253 int validCombination_ID = 0;
254         try
255         {
256             PreparedStatement pstmt = DB.prepareStatement(sql);
257             pstmt.setInt(1, m_M_Product_ID);
258             pstmt.setInt(2, as.getC_AcctSchema_ID());
259             ResultSet rs = pstmt.executeQuery();
260             if (rs.next())
261                 validCombination_ID = rs.getInt(AcctType);
262             rs.close();
263             pstmt.close();
264         }
265         catch (SQLException e)
266         {
267             log.error ("getAccount", e);
268         }
269         if (validCombination_ID == 0)
270             return null;
271         return Account.getAccount(validCombination_ID);
272     } // getAccount
273
274     /**
275      * Account from Default Product Category
276      *
277      * @param AcctType see ACCTTYPE_* (1..8)
278      * @param as accounting schema
279      * @return Requested Product Account
280      */
281     public Account getAccountDefault (int AcctType, AcctSchema as)
282     {
283         if (AcctType < 1 || AcctType > 8)
284             return null;
285
286         String sql = "SELECT P_Revenue_Acct, P_Expense_Acct, P_Asset_Acct, P_Cogs_Acct, "
287             + "P_PurchasePriceVariance_Acct, P_InvoicePriceVariance_Acct, "
288             + "P_TradeDiscountRec_Acct, P_TradeDiscountGrant_Acct "
289             + "FROM M_Product_Category pc, M_Product_Category_Acct pca "
290             + "WHERE pc.M_Product_Category_ID=pca.M_Product_Category_ID"
291             + " AND pca.C_AcctSchema_ID=? "
292             + "ORDER BY pc.IsDefault DESC, pc.Created";
293         //
294 int validCombination_ID = 0;
295         try
296         {
297             PreparedStatement pstmt = DB.prepareStatement(sql);
298             pstmt.setInt(1, as.getC_AcctSchema_ID());
299             ResultSet rs = pstmt.executeQuery();
300             if (rs.next())
301                 validCombination_ID = rs.getInt(AcctType);
302             rs.close();
303             pstmt.close();
304         }
305         catch (SQLException e)
306         {
307             log.error ("getAccountDefault", e);
308         }
309         if (validCombination_ID == 0)
310             return null;
311         return Account.getAccount(validCombination_ID);
312     } // getAccountDefault
313
314     /*************************************************************************/
315
316     /**
317      * Get Total Costs in Accounting Schema Currency
318      * @param as accounting schema
319      * @return cost or null, if qty or costs cannot be determined
320      */
321     public BigDecimal getProductCosts (AcctSchema as)
322     {
323         if (m_qty == null)
324         {
325             log.debug("getProductCosts - No Qty");
326             return null;
327         }
328         BigDecimal cost = getProductItemCost(as, null);
329         if (cost == null)
330         {
331             log.debug("getProductCosts - No Costs");
332             return null;
333         }
334         log.debug("getProductCosts - Qty(" + m_qty + ") * Cost(" + cost + ") = " + m_qty.multiply(cost));
335         return m_qty.multiply(cost);
336     } // getProductCosts
337
338     /**
339      * Get Product Costs per UOM for Accounting Schema in Accounting Schema Currency.
340      * - if costType defined - cost
341      * - else CurrentCosts
342      * @param as accounting schema
343      * @param costType - if null uses Accounting Schema Costs - see AcctSchema.COSTING_*
344      * @return product costs
345      */
346     public BigDecimal getProductItemCost(AcctSchema as, String costType)
347     {
348         BigDecimal current = null;
349         BigDecimal cost = null;
350         String cm = as.getCostingMethod();
351         StringBuffer sql = new StringBuffer ("SELECT CurrentCostPrice,"); // 1
352 //
353 if ((costType == null && AcctSchema.COSTING_AVERAGE.equals(cm))
354                 || AcctSchema.COSTING_AVERAGE.equals(costType))
355             sql.append("COSTAVERAGE"); // 2
356 // else if (AcctSchema.COSTING_FIFO.equals(cm))
357 // sql.append("COSTFIFO");
358 // else if (AcctSchema.COSTING_LIFO.equals(cm))
359 // sql.append("COSTLIFO");
360 else if ((costType == null && AcctSchema.COSTING_LASTPO.equals(cm))
361                 || AcctSchema.COSTING_LASTPO.equals(costType))
362             sql.append("PRICELASTPO");
363         else // AcctSchema.COSTING_STANDARD
364 sql.append("COSTSTANDARD");
365         sql.append(" FROM M_Product_Costing WHERE M_Product_ID=? AND C_AcctSchema_ID=?");
366
367         try
368         {
369             PreparedStatement pstmt = DB.prepareStatement(sql.toString());
370             pstmt.setInt(1, m_M_Product_ID);
371             pstmt.setInt(2, as.getC_AcctSchema_ID());
372             ResultSet rs = pstmt.executeQuery();
373             if (rs.next())
374             {
375                 current = rs.getBigDecimal(1);
376                 cost = rs.getBigDecimal(2);
377             }
378             rs.close();
379             pstmt.close();
380         }
381         catch (SQLException e)
382         {
383             log.error("getProductItemCost", e);
384         }
385
386         // Return Costs
387 if (costType != null && cost != null && !cost.equals(Env.ZERO))
388         {
389             log.debug("getProductItemCosts = " + cost);
390             return cost;
391         }
392         else if (current != null && !current.equals(Env.ZERO))
393         {
394             log.debug("getProductItemCosts - Current=" + current);
395             return current;
396         }
397
398         // Create/Update Cost Record
399 boolean create = (cost == null && current == null);
400         return updateCosts (as, create);
401     } // getProductCost
402
403     /**
404      * Update/Create initial Cost Record.
405      * Check first for Purchase Price List,
406      * then Product Purchase Costs
407      * and then Price List
408      * @param as accounting schema
409      * @param create create record
410      * @return costs
411      */
412     private BigDecimal updateCosts (AcctSchema as, boolean create)
413     {
414         // Create Zero Record
415 if (create)
416         {
417             StringBuffer sql = new StringBuffer ("INSERT INTO M_Product_Costing "
418                 + "(M_Product_ID,C_AcctSchema_ID,"
419                 + " AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy,"
420                 + " CurrentCostPrice,CostStandard,FutureCostPrice,"
421                 + " CostStandardPOQty,CostStandardPOAmt,CostStandardCumQty,CostStandardCumAmt,"
422                 + " CostAverage,CostAverageCumQty,CostAverageCumAmt,"
423                 + " PriceLastPO,PriceLastInv, TotalInvQty,TotalInvAmt) "
424                 + "VALUES (");
425             sql.append(m_M_Product_ID).append(",").append(as.getC_AcctSchema_ID()).append(",")
426                 .append(m_AD_Client_ID).append(",").append(m_AD_Org_ID).append(",")
427                 .append("'Y',SysDate,0,SysDate,0, 0,0,0, 0,0,0,0, 0,0,0, 0,0, 0,0)");
428             int no = DB.executeUpdate(sql.toString());
429             if (no == 1)
430                 log.debug("updateCosts - CostingCreated");
431         }
432
433         // Try to find non ZERO Price
434 String costSource = "PriceList-PO";
435         BigDecimal costs = getPriceList (as, true);
436         if (costs == null || costs.equals(Env.ZERO))
437         {
438             costSource = "PO Cost";
439             costs = getPOCost(as);
440         }
441         if (costs == null || costs.equals(Env.ZERO))
442         {
443             costSource = "PriceList";
444             costs = getPriceList (as, false);
445         }
446
447         // if not found use $1 (to be able to do material transactions)
448 if (costs == null || costs.equals(Env.ZERO))
449         {
450             costSource = "Not Found";
451             costs = new BigDecimal("1");
452         }
453
454         // update current costs
455 StringBuffer sql = new StringBuffer ("UPDATE M_Product_Costing ");
456         sql.append("SET CurrentCostPrice=").append(costs)
457             .append(" WHERE M_Product_ID=").append(m_M_Product_ID)
458             .append(" AND C_AcctSchema_ID=").append(as.getC_AcctSchema_ID());
459         int no = DB.executeUpdate(sql.toString());
460         if (no == 1)
461             log.debug("updateCosts - " + costSource + " - " + costs);
462         return costs;
463     } // createCosts
464
465     /**
466      * Get PO Price from PriceList - and convert it to AcctSchema Currency
467      * @param as accounting schema
468      * @param onlyPOPriceList use only PO price list
469      * @return po price
470      */
471     private BigDecimal getPriceList (AcctSchema as, boolean onlyPOPriceList)
472     {
473         StringBuffer sql = new StringBuffer (
474             "SELECT pl.C_Currency_ID, pp.PriceList, pp.PriceStd, pp.PriceLimit "
475             + "FROM M_PriceList pl, M_PriceList_Version plv, M_ProductPrice pp "
476             + "WHERE pl.M_PriceList_ID = plv.M_PriceList_ID"
477             + " AND plv.M_PriceList_Version_ID = pp.M_PriceList_Version_ID"
478             + " AND pp.M_Product_ID=?");
479         if (onlyPOPriceList)
480             sql.append(" AND pl.IsSOPriceList='N'");
481         sql.append(" ORDER BY pl.IsSOPriceList ASC, plv.ValidFrom DESC");
482         int C_Currency_ID = 0;
483         BigDecimal PriceList = null;
484         BigDecimal PriceStd = null;
485         BigDecimal PriceLimit = null;
486         try
487         {
488             PreparedStatement pstmt = DB.prepareStatement(sql.toString());
489             pstmt.setInt(1, m_M_Product_ID);
490             ResultSet rs = pstmt.executeQuery();
491             if (rs.next())
492             {
493                 C_Currency_ID = rs.getInt(1);
494                 PriceList = rs.getBigDecimal(2);
495                 PriceStd = rs.getBigDecimal(3);
496                 PriceLimit = rs.getBigDecimal(4);
497             }
498             rs.close();
499             pstmt.close();
500         }
501         catch (SQLException e)
502         {
503             log.error("getPOPrice", e);
504         }
505         // nothing found
506 if (C_Currency_ID == 0)
507             return null;
508
509         BigDecimal price = PriceLimit; // best bet
510 if (price == null || price.equals(Env.ZERO))
511             price = PriceStd;
512         if (price == null || price.equals(Env.ZERO))
513             price = PriceList;
514         // Convert
515 if (price != null && !price.equals(Env.ZERO))
516             price = DB.getConvertedAmt(price, C_Currency_ID, as.getC_Currency_ID(), as.getAD_Client_ID(), 0);
517         return price;
518     } // getPOPrice
519
520     /**
521      * Get PO Cost from Purchase Info - and convert it to AcctSchema Currency
522      * @param as accounting schema
523      * @return po cost
524      */
525     private BigDecimal getPOCost (AcctSchema as)
526     {
527         String sql = "SELECT C_Currency_ID, PriceList,PricePO,PriceLastPO "
528             + "FROM M_Product_PO WHERE M_Product_ID=? "
529             + "ORDER BY IsCurrentVendor DESC";
530
531         int C_Currency_ID = 0;
532         BigDecimal PriceList = null;
533         BigDecimal PricePO = null;
534         BigDecimal PriceLastPO = null;
535         try
536         {
537             PreparedStatement pstmt = DB.prepareStatement(sql);
538             pstmt.setInt(1, m_M_Product_ID);
539             ResultSet rs = pstmt.executeQuery();
540             if (rs.next())
541             {
542                 C_Currency_ID = rs.getInt(1);
543                 PriceList = rs.getBigDecimal(2);
544                 PricePO = rs.getBigDecimal(3);
545                 PriceLastPO = rs.getBigDecimal(4);
546             }
547             rs.close();
548             pstmt.close();
549         }
550         catch (SQLException e)
551         {
552             log.error("getPOCost", e);
553         }
554         // nothing found
555 if (C_Currency_ID == 0)
556             return null;
557
558         BigDecimal cost = PriceLastPO; // best bet
559 if (cost == null || cost.equals(Env.ZERO))
560             cost = PricePO;
561         if (cost == null || cost.equals(Env.ZERO))
562             cost = PriceList;
563         // Convert - standard precision!! - should be costing precision
564 if (cost != null && !cost.equals(Env.ZERO))
565             cost = DB.getConvertedAmt(cost, C_Currency_ID, as.getC_Currency_ID(), m_AD_Client_ID, m_AD_Org_ID);
566         return cost;
567     } // getPOCost
568
569 } // ProductInfo
570

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags