InventoryValue


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.process;
15  
16  import java.sql.*;
17  import java.math.*;
18  
19  import org.compiere.util.*;
20  
21  
22  /**
23   *  Process to fill T_InventoryValue
24   *
25   *  @author     Jorg Janke
26   *  @version    $Id: InventoryValue.java,v 1.7 2003/08/04 03:54:46 jjanke Exp $
27   */
28  public class InventoryValue extends SvrProcess
29  {
30      /**
31       *  Empty Constructor
32       */
33      public InventoryValue()
34      {
35          super();
36          Log.trace(Log.l1_User, "InventoryValue");
37      }   //  Inventory Value
38  
39      /** Price List Used         */
40      private int         m_M_PriceList_Version_ID;
41      /** Valuation Date          */
42      private Timestamp   m_DateValue;
43      /** Warehouse               */
44      private int         m_M_Warehouse_ID;
45      /** Currency                */
46      private int         m_C_Currency_ID;
47  
48      /**
49       *  Prepare - get Parameters.
50       */
51      protected void prepare()
52      {
53          ProcessInfoParameter[] para = getParameter();
54          for (int i = 0; i < para.length; i++)
55          {
56              String   name = para[i].getParameterName();
57              if (para[i].getParameter() == null)
58                  ;
59              else if (name.equals("M_PriceList_Version_ID"))
60                  m_M_PriceList_Version_ID = ((BigDecimal)para[i].getParameter()).intValue();
61              else if (name.equals("DateValue"))
62                  m_DateValue = (Timestamp)para[i].getParameter();
63              else if (name.equals("M_Warehouse_ID"))
64                  m_M_Warehouse_ID = ((BigDecimal)para[i].getParameter()).intValue();
65              else if (name.equals("C_Currency_ID"))
66                  m_C_Currency_ID = ((BigDecimal)para[i].getParameter()).intValue();
67          }
68          if (m_DateValue == null)
69              m_DateValue = new Timestamp (System.currentTimeMillis());
70      }   //  prepare
71  
72      /**
73       *  Perrform process.
74       *  <pre>
75       *  - Fill Table with QtyOnHand for Warehouse and Valuation Date
76       *  - Perform Price Calculations
77       *  </pre>
78       * @return Message
79       * @throws Exception
80       */
81      protected String   doIt() throws Exception  
82      {
83          //  Delete (just to be sure)
84          StringBuffer   sql = new StringBuffer   ("DELETE T_InventoryValue WHERE M_Warehouse_ID=");
85          sql.append(m_M_Warehouse_ID);
86          int no = DB.executeUpdate(sql.toString());
87  
88          //  Insert Products
89          sql = new StringBuffer   ("INSERT INTO T_InventoryValue "
90              + "(AD_Client_ID,AD_Org_ID,M_Warehouse_ID,M_Product_ID)");
91          sql.append("SELECT AD_Client_ID,AD_Org_ID,")
92              .append(m_M_Warehouse_ID).append(",M_Product_ID")
93              .append(" FROM M_Product WHERE IsStocked='Y'");
94          int noPrd = DB.executeUpdate(sql.toString());
95          Log.trace(Log.l6_Database, "InventoryValue.doIt - Inserted=" + noPrd);
96          if (noPrd == 0)
97              return "No Products";
98  
99          //  Update Constants
100         sql = new StringBuffer   ("UPDATE T_InventoryValue SET ");
101         //  YYYY-MM-DD HH24:MI:SS.mmmm  JDBC Timestamp format
102         String   myDate = m_DateValue.toString();
103         sql.append("DateValue=TO_DATE('").append(myDate.substring(0,10))
104             .append(" 23:59:59','YYYY-MM-DD HH24:MI:SS'),")
105             .append("M_PriceList_Version_ID=").append(m_M_PriceList_Version_ID).append(",")
106             .append("C_Currency_ID=").append(m_C_Currency_ID);
107         no = DB.executeUpdate(sql.toString());
108 
109         //  Get current QtyOnHand
110         no = DB.executeUpdate ("UPDATE T_InventoryValue iv "
111             + "SET QtyOnHand = (SELECT SUM(QtyOnHand) FROM M_Storage s, M_Locator l"
112             + " WHERE iv.M_Product_ID=s.M_Product_ID"
113             + " AND l.M_Locator_ID=s.M_Locator_ID"
114             + " AND l.M_Warehouse_ID=iv.M_Warehouse_ID)");
115 
116         //  Adjust for Valuation Date
117         no = DB.executeUpdate ("UPDATE T_InventoryValue iv SET QtyOnHand = "
118             + "(SELECT iv.QtyOnHand - NVL(SUM(t.MovementQty), 0) FROM M_Transaction t, M_Locator l"
119             + " WHERE t.M_Product_ID=iv.M_Product_ID AND t.MovementDate > iv.DateValue"
120             + " AND t.M_Locator_ID=l.M_Locator_ID AND l.M_Warehouse_ID=iv.M_Warehouse_ID)");
121 
122         //  Delete Recotds w/o OnHand Qty
123         int noQty = DB.executeUpdate ("DELETE T_InventoryValue WHERE QtyOnHand=0 OR QtyOnHand IS NULL");
124         Log.trace(Log.l6_Database, "InventoryValue.doIt - NoQty=" + noQty);
125         if (noPrd == noQty)
126             return "No OnHand";
127 
128         //  Update Prices
129         no = DB.executeUpdate ("UPDATE T_InventoryValue iv "
130             + "SET PricePO = "
131                 + "(SELECT C_Currency_Convert (po.PriceList,po.C_Currency_ID,iv.C_Currency_ID,iv.DateValue,null, po.AD_Client_ID,po.AD_Org_ID)"
132                 + " FROM M_Product_PO po WHERE po.M_Product_ID=iv.M_Product_ID"
133                 + " AND po.IsCurrentVendor='Y' AND RowNum=1), "
134             + "PriceList = "
135                 + "(SELECT C_Currency_Convert(pp.PriceList,pl.C_Currency_ID,iv.C_Currency_ID,iv.DateValue,null, pl.AD_Client_ID,pl.AD_Org_ID)"
136                 + " FROM M_PriceList pl, M_PriceList_Version plv, M_ProductPrice pp"
137                 + " WHERE pp.M_Product_ID=iv.M_Product_ID AND pp.M_PriceList_Version_ID=iv.M_PriceList_Version_ID"
138                 + " AND pp.M_PriceList_Version_ID=plv.M_PriceList_Version_ID"
139                 + " AND plv.M_PriceList_ID=pl.M_PriceList_ID), "
140             + "PriceStd = "
141                 + "(SELECT C_Currency_Convert(pp.PriceStd,pl.C_Currency_ID,iv.C_Currency_ID,iv.DateValue,null, pl.AD_Client_ID,pl.AD_Org_ID)"
142                 + " FROM M_PriceList pl, M_PriceList_Version plv, M_ProductPrice pp"
143                 + " WHERE pp.M_Product_ID=iv.M_Product_ID AND pp.M_PriceList_Version_ID=iv.M_PriceList_Version_ID"
144                 + " AND pp.M_PriceList_Version_ID=plv.M_PriceList_Version_ID"
145                 + " AND plv.M_PriceList_ID=pl.M_PriceList_ID), "
146             + "PriceLimit = "
147                 + "(SELECT C_Currency_Convert(pp.PriceLimit,pl.C_Currency_ID,iv.C_Currency_ID,iv.DateValue,null, pl.AD_Client_ID,pl.AD_Org_ID)"
148                 + " FROM M_PriceList pl, M_PriceList_Version plv, M_ProductPrice pp"
149                 + " WHERE pp.M_Product_ID=iv.M_Product_ID AND pp.M_PriceList_Version_ID=iv.M_PriceList_Version_ID"
150                 + " AND pp.M_PriceList_Version_ID=plv.M_PriceList_Version_ID"
151                 + " AND plv.M_PriceList_ID=pl.M_PriceList_ID), "
152             + "CostStandard = "
153                 + "(SELECT C_Currency_Convert(pc.CurrentCostPrice,acs.C_Currency_ID,iv.C_Currency_ID,iv.DateValue,null, pc.AD_Client_ID,pc.AD_Org_ID)"
154                 + " FROM AD_ClientInfo ci, C_AcctSchema acs, M_Product_Costing pc"
155                 + " WHERE iv.AD_Client_ID=ci.AD_Client_ID AND ci.C_AcctSchema1_ID=acs.C_AcctSchema_ID"
156                 + " AND acs.C_AcctSchema_ID=pc.C_AcctSchema_ID"
157                 + " AND iv.M_Product_ID=pc.M_Product_ID)");
158         String   msg = "";
159         if (no == 0)
160             msg = "No Prices";
161 
162         //  Update Values
163         no = DB.executeUpdate("UPDATE T_InventoryValue SET "
164             + "PricePOAmt = QtyOnHand * PricePO, "
165             + "PriceListAmt = QtyOnHand * PriceList, "
166             + "PriceStdAmt = QtyOnHand * PriceStd, "
167             + "PriceLimitAmt = QtyOnHand * PriceLimit, "
168             + "CostStandardAmt = QtyOnHand * CostStandard");
169         Log.trace(Log.l6_Database, "InventoryValue.doIt - Valued=" + no);
170         //
171         return msg;
172     }   //  doIt
173 
174 }   //  InventoryValue
175
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags