KickJava   Java API By Example, From Geeks To Geeks.

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


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.math.*;
17 import java.util.*;
18 import java.sql.*;
19
20 import org.compiere.util.*;
21 import org.compiere.model.*;
22
23 /**
24  * Post Invoice Documents.
25  * <pre>
26  * Table: C_Invoice (318)
27  * Document Types: ARI, ARC, ARF, API, APC
28  * </pre>
29  * @author Jorg Janke
30  * @version $Id: Doc_Invoice.java,v 1.20 2003/10/10 01:03:42 jjanke Exp $
31  */
32 public class Doc_Invoice extends Doc
33 {
34     /** AD_Table_ID */
35     public static final int C_Invoice_TABLE_ID = 318;
36
37     /**
38      * Constructor
39      * @param AD_Client_ID AD_Client_ID
40      */
41     protected Doc_Invoice(int AD_Client_ID)
42     {
43         super(AD_Client_ID);
44     }
45
46     /** Contained Optional Tax Lines */
47     private DocTax[] m_taxes = null;
48     /** Loaction */
49     private int C_BPartner_Location_ID = 0;
50
51     /**
52      * Return TableName of Document
53      * @return C_Invoice
54      */
55     public String JavaDoc getTableName()
56     {
57         return "C_Invoice";
58     } // getTableName
59
60     /**
61      * Get Table ID
62      * @return 318
63      */
64     public int getAD_Table_ID()
65     {
66         return C_Invoice_TABLE_ID;
67     } // getAD_Table_ID
68
69     /**
70      * Load Specific Document Details
71      * @param rs Result Set
72      * @return true if loadDocumentType was set
73      */
74     protected boolean loadDocumentDetails (ResultSet rs)
75     {
76         try
77         {
78             p_vo.DateDoc = rs.getTimestamp("DateInvoiced");
79             p_vo.TaxIncluded = rs.getString("IsTaxIncluded").equals("Y");
80             C_BPartner_Location_ID = rs.getInt("C_BPartner_Location_ID");
81
82             // Amounts
83 p_vo.Amounts[Doc.AMTTYPE_Gross] = rs.getBigDecimal("GrandTotal");
84             if (p_vo.Amounts[Doc.AMTTYPE_Gross] == null)
85                 p_vo.Amounts[Doc.AMTTYPE_Gross] = Env.ZERO;
86             p_vo.Amounts[Doc.AMTTYPE_Net] = rs.getBigDecimal("TotalLines");
87             if (p_vo.Amounts[Doc.AMTTYPE_Net] == null)
88                 p_vo.Amounts[Doc.AMTTYPE_Net] = Env.ZERO;
89             p_vo.Amounts[Doc.AMTTYPE_Charge] = rs.getBigDecimal("ChargeAmt");
90             if (p_vo.Amounts[Doc.AMTTYPE_Charge] == null)
91                 p_vo.Amounts[Doc.AMTTYPE_Charge] = Env.ZERO;
92         }
93         catch (SQLException e)
94         {
95             log.error("loadDocumentDetails", e);
96         }
97
98         loadDocumentType(); // lines require doc type
99 // Contained Objects
100 p_lines = loadLines();
101         m_taxes = loadTaxes();
102         log.debug("Lines=" + p_lines.length + ", Taxes=" + m_taxes.length);
103         return true;
104     } // loadDocumentDetails
105
106
107     /**
108      * Load Invoice Line
109      * @return DocLine Array
110      */
111     private DocLine[] loadLines()
112     {
113         ArrayList list = new ArrayList();
114         String JavaDoc sql = "SELECT * FROM C_InvoiceLine WHERE C_Invoice_ID=? ORDER BY Line";
115         try
116         {
117             PreparedStatement pstmt = DB.prepareStatement(sql);
118             pstmt.setInt(1, p_vo.Record_ID);
119             ResultSet rs = pstmt.executeQuery();
120             //
121 while (rs.next())
122             {
123                 int Line_ID = rs.getInt("C_InvoiceLine_ID");
124                 DocLine_Invoice docLine = new DocLine_Invoice (p_vo.DocumentType, p_vo.Record_ID, Line_ID);
125                 docLine.loadAttributes(rs, p_vo);
126                 BigDecimal Qty = rs.getBigDecimal("QtyInvoiced");
127                 docLine.setQty(Qty);
128                 BigDecimal LineNetAmt = rs.getBigDecimal("LineNetAmt");
129                 BigDecimal PriceList = rs.getBigDecimal("PriceList");
130                 docLine.setAmount (LineNetAmt, PriceList, Qty);
131                 //
132 log.debug(docLine.toString());
133                 list.add (docLine);
134             }
135             //
136 rs.close();
137             pstmt.close();
138         }
139         catch (SQLException e)
140         {
141             log.error ("loadLines", e);
142         }
143
144         // Return Array
145 DocLine[] dl = new DocLine[list.size()];
146         list.toArray(dl);
147         return dl;
148     } // loadLines
149
150     /**
151      * Load Invoice Taxes
152      * @return DocTax Array
153      */
154     private DocTax[] loadTaxes()
155     {
156         ArrayList list = new ArrayList();
157         String JavaDoc sql = "SELECT it.C_Tax_ID, t.Name, t.Rate, it.TaxBaseAmt, it.TaxAmt "
158             + "FROM C_Tax t, C_InvoiceTax it "
159             + "WHERE t.C_Tax_ID=it.C_Tax_ID AND it.C_Invoice_ID=?";
160         try
161         {
162             PreparedStatement pstmt = DB.prepareStatement(sql);
163             pstmt.setInt(1, p_vo.Record_ID);
164             ResultSet rs = pstmt.executeQuery();
165             //
166 while (rs.next())
167             {
168                 int C_Tax_ID = rs.getInt(1);
169                 String JavaDoc name = rs.getString(2);
170                 BigDecimal rate = rs.getBigDecimal(3);
171                 BigDecimal taxBaseAmt = rs.getBigDecimal(4);
172                 BigDecimal amount = rs.getBigDecimal(5);
173                 //
174 DocTax taxLine = new DocTax(C_Tax_ID, name, rate, taxBaseAmt, amount);
175                 log.debug(taxLine.toString());
176                 list.add(taxLine);
177             }
178             //
179 rs.close();
180             pstmt.close();
181         }
182         catch (SQLException e)
183         {
184             log.error ("loadTaxes", e);
185         }
186
187         // Return Array
188 DocTax[] tl = new DocTax[list.size()];
189         list.toArray(tl);
190         return tl;
191     } // loadTaxes
192
193     /*************************************************************************/
194
195     /**
196      * Get Source Currency Balance - subtracts line and tax amounts from total - no rounding
197      * @return positive amount, if total invoice is bigger than lines
198      */
199     public BigDecimal getBalance()
200     {
201         BigDecimal retValue = Env.ZERO;
202         StringBuffer JavaDoc sb = new StringBuffer JavaDoc (" [");
203         // Total
204 retValue = retValue.add(getAmount(Doc.AMTTYPE_Gross));
205         sb.append(getAmount(Doc.AMTTYPE_Gross));
206         // - Charge
207 retValue = retValue.subtract(getAmount(Doc.AMTTYPE_Charge));
208         sb.append("-").append(getAmount(Doc.AMTTYPE_Charge));
209         // - Tax
210 for (int i = 0; i < m_taxes.length; i++)
211         {
212             retValue = retValue.subtract(m_taxes[i].getAmount());
213             sb.append("-").append(m_taxes[i].getAmount());
214         }
215         // - Lines
216 for (int i = 0; i < p_lines.length; i++)
217         {
218             retValue = retValue.subtract(p_lines[i].getAmount());
219             sb.append("-").append(p_lines[i].getAmount());
220         }
221         sb.append("]");
222         //
223 log.debug(toString() + " Balance=" + retValue + sb.toString());
224         return retValue;
225     } // getBalance
226
227     /**
228      * Create Facts (the accounting logic) for
229      * ARI, ARC, ARF, API, APC.
230      * <pre>
231      * ARI, ARF
232      * Receivables DR
233      * Charge CR
234      * TaxDue CR
235      * Revenue CR
236      *
237      * ARC
238      * Receivables CR
239      * Charge DR
240      * TaxDue DR
241      * Revenue RR
242      *
243      * API
244      * Payables CR
245      * Charge DR
246      * TaxCredit DR
247      * Expense DR
248      *
249      * APC
250      * Payables DR
251      * Charge CR
252      * TaxCredit CR
253      * Expense CR
254      * </pre>
255      * @param as accounting schema
256      * @return Fact
257      */
258     public Fact createFact (AcctSchema as)
259     {
260         // create Fact Header
261 Fact fact = new Fact(this, as, Fact.POST_Actual);
262
263         // Cash based accounting
264 if (!as.isAccrual())
265         {
266             p_vo.Error = "Not Accrual";
267             log.error("createFact - " + p_vo.Error);
268             return null;
269         }
270
271         /** @todo Assumes TaxIncluded = N */
272
273         // ARI, ARF
274 if (p_vo.DocumentType.equals(DocVO.DOCTYPE_ARInvoice) || p_vo.DocumentType.equals(DocVO.DOCTYPE_ARProForma))
275         {
276             // Receivables DR
277 fact.createLine(null, getAccount(Doc.ACCTTYPE_C_Receivable, as),
278                 p_vo.C_Currency_ID, getAmount(Doc.AMTTYPE_Gross), null);
279             // Header Charge CR
280 fact.createLine(null, getAccount(Doc.ACCTTYPE_Charge, as),
281                 p_vo.C_Currency_ID, null, getAmount(Doc.AMTTYPE_Charge));
282             // TaxDue CR
283 for (int i = 0; i < m_taxes.length; i++)
284             {
285                 fact.createLine(null, m_taxes[i].getAccount(DocTax.ACCTTYPE_TaxDue, as),
286                     p_vo.C_Currency_ID, null, m_taxes[i].getAmount());
287             }
288             // Revenue CR
289 for (int i = 0; i < p_lines.length; i++)
290             {
291                 fact.createLine (p_lines[i],
292                     ((DocLine_Invoice)p_lines[i]).getAccount (ProductInfo.
293                     ACCTTYPE_P_Revenue, as),
294                     p_vo.C_Currency_ID, null, p_lines[i].getAmount ());
295             }
296             // Set Locations
297 FactLine[] fLines = fact.getLines();
298             for (int i = 0; i < fLines.length; i++)
299             {
300                 if (fLines[i] != null)
301                 {
302                     fLines[i].setLocationFromOrg(fLines[i].getAD_Org_ID(), true); // from Loc
303 fLines[i].setLocationFromBPartner(C_BPartner_Location_ID, false); // to Loc
304 }
305             }
306         }
307         // ARC
308 else if (p_vo.DocumentType.equals(DocVO.DOCTYPE_ARCredit))
309         {
310             // Receivables CR
311 fact.createLine(null, getAccount(Doc.ACCTTYPE_C_Receivable, as),
312                 p_vo.C_Currency_ID, null, getAmount(Doc.AMTTYPE_Gross));
313             // Header Charge DR
314 fact.createLine(null, getAccount(Doc.ACCTTYPE_Charge, as),
315                 p_vo.C_Currency_ID, getAmount(Doc.AMTTYPE_Charge), null);
316             // TaxDue DR
317 for (int i = 0; i < m_taxes.length; i++)
318                 fact.createLine(null, m_taxes[i].getAccount(DocTax.ACCTTYPE_TaxDue, as),
319                     p_vo.C_Currency_ID, m_taxes[i].getAmount(), null);
320             // Revenue CR
321 for (int i = 0; i < p_lines.length; i++)
322             {
323                 fact.createLine (p_lines[i],
324                     ((DocLine_Invoice)p_lines[i]).getAccount (ProductInfo.
325                     ACCTTYPE_P_Revenue, as),
326                     p_vo.C_Currency_ID, p_lines[i].getAmount (), null);
327             }
328             // Set Locations
329 FactLine[] fLines = fact.getLines();
330             for (int i = 0; i < fLines.length; i++)
331             {
332                 if (fLines[i] != null)
333                 {
334                     fLines[i].setLocationFromOrg(fLines[i].getAD_Org_ID(), true); // from Loc
335 fLines[i].setLocationFromBPartner(C_BPartner_Location_ID, false); // to Loc
336 }
337             }
338         }
339         // API
340 else if (p_vo.DocumentType.equals(DocVO.DOCTYPE_APInvoice))
341         {
342             // Liability CR
343 fact.createLine(null, getAccount(Doc.ACCTTYPE_V_Liability, as),
344                 p_vo.C_Currency_ID, null, getAmount(Doc.AMTTYPE_Gross));
345             // Charge DR
346 fact.createLine(null, getAccount(Doc.ACCTTYPE_Charge, as),
347                 p_vo.C_Currency_ID, getAmount(Doc.AMTTYPE_Charge), null);
348             // TaxCredit DR
349 for (int i = 0; i < m_taxes.length; i++)
350                 fact.createLine(null, m_taxes[i].getAccount(DocTax.ACCTTYPE_TaxCredit, as),
351                     p_vo.C_Currency_ID, m_taxes[i].getAmount(), null);
352             // Expense DR
353 for (int i = 0; i < p_lines.length; i++)
354             {
355                 fact.createLine (p_lines[i],
356                     ((DocLine_Invoice)p_lines[i]).getAccount (ProductInfo.
357                     ACCTTYPE_P_Expense, as),
358                     p_vo.C_Currency_ID, p_lines[i].getAmount (), null);
359             }
360             // Set Locations
361 FactLine[] fLines = fact.getLines();
362             for (int i = 0; i < fLines.length; i++)
363             {
364                 if (fLines[i] != null)
365                 {
366                     fLines[i].setLocationFromBPartner(C_BPartner_Location_ID, true); // from Loc
367 fLines[i].setLocationFromOrg(fLines[i].getAD_Org_ID(), false); // to Loc
368 }
369             }
370             updateProductInfo (as.getC_AcctSchema_ID()); // only API
371 }
372         // APC
373 else if (p_vo.DocumentType.equals(DocVO.DOCTYPE_APCredit))
374         {
375             // Liability DR
376 fact.createLine (null, getAccount(Doc.ACCTTYPE_V_Liability, as),
377                 p_vo.C_Currency_ID, getAmount(Doc.AMTTYPE_Gross), null);
378             // Charge CR
379 fact.createLine (null, getAccount(Doc.ACCTTYPE_Charge, as),
380                 p_vo.C_Currency_ID, null, getAmount(Doc.AMTTYPE_Charge));
381             // TaxCredit CR
382 for (int i = 0; i < m_taxes.length; i++)
383                 fact.createLine (null, m_taxes[i].getAccount(DocTax.ACCTTYPE_TaxCredit, as),
384                     p_vo.C_Currency_ID, null, m_taxes[i].getAmount());
385             // Expense CR
386 for (int i = 0; i < p_lines.length; i++)
387             {
388                 fact.createLine (p_lines[i],
389                     ((DocLine_Invoice)p_lines[i]).getAccount (ProductInfo.
390                     ACCTTYPE_P_Expense, as),
391                     p_vo.C_Currency_ID, null, p_lines[i].getAmount ());
392             }
393             // Set Locations
394 FactLine[] fLines = fact.getLines();
395             for (int i = 0; i < fLines.length; i++)
396             {
397                 if (fLines[i] != null)
398                 {
399                     fLines[i].setLocationFromBPartner(C_BPartner_Location_ID, true); // from Loc
400 fLines[i].setLocationFromOrg(fLines[i].getAD_Org_ID(), false); // to Loc
401 }
402             }
403         }
404         else
405         {
406             p_vo.Error = "DocumentType unknown: " + p_vo.DocumentType;
407             log.error("createFact - " + p_vo.Error);
408             fact = null;
409         }
410         return fact;
411     } // createFact
412
413     /**
414      * Update Product Info.
415      * - Costing (PriceLastInv)
416      * - PO (PriceLastInv)
417      * @param C_AcctSchema_ID accounting schema
418      */
419     private void updateProductInfo (int C_AcctSchema_ID)
420     {
421         log.debug("updateProductInfo - C_Invoice_ID=" + p_vo.Record_ID);
422
423         /** @todo Last.. would need to compare document/last updated date
424          * would need to maintain LastPriceUpdateDate on _PO and _Costing */
425
426         // update Product PO info
427 // should only be once, but here for every AcctSchema
428 // ignores multiple lines with same product - just uses first
429 StringBuffer JavaDoc sql = new StringBuffer JavaDoc (
430             "UPDATE M_Product_PO po "
431             + "SET PriceLastInv = "
432             // select
433 + "(SELECT C_Currency_Convert(il.PriceActual,i.C_Currency_ID,po.C_Currency_ID,i.DateInvoiced,null,i.AD_Client_ID,i.AD_Org_ID) "
434             + "FROM C_Invoice i, C_InvoiceLine il "
435             + "WHERE i.C_Invoice_ID=il.C_Invoice_ID"
436             + " AND po.M_Product_ID=il.M_Product_ID AND po.C_BPartner_ID=i.C_BPartner_ID"
437             + " AND ROWNUM=1 AND i.C_Invoice_ID=").append(p_vo.Record_ID).append(") ")
438             // update
439 .append("WHERE EXISTS (SELECT * "
440             + "FROM C_Invoice i, C_InvoiceLine il "
441             + "WHERE i.C_Invoice_ID=il.C_Invoice_ID"
442             + " AND po.M_Product_ID=il.M_Product_ID AND po.C_BPartner_ID=i.C_BPartner_ID"
443             + " AND i.C_Invoice_ID=").append(p_vo.Record_ID).append(")");
444         int no = DB.executeUpdate(sql.toString());
445         log.debug("M_Product_PO - Updated=" + no);
446
447         // update Product Costing
448 // requires existence of currency conversion !!
449 // if there are multiple lines of the same product last price uses first
450 sql = new StringBuffer JavaDoc (
451             "UPDATE M_Product_Costing pc "
452             + "SET (PriceLastInv, TotalInvAmt,TotalInvQty) = "
453             // select
454 + "(SELECT C_Currency_Convert(il.PriceActual,i.C_Currency_ID,a.C_Currency_ID,i.DateInvoiced,null,i.AD_Client_ID,i.AD_Org_ID),"
455             + " C_Currency_Convert(il.LineNetAmt,i.C_Currency_ID,a.C_Currency_ID,i.DateInvoiced,null),il.QtyInvoiced "
456             + "FROM C_Invoice i, C_InvoiceLine il, C_AcctSchema a "
457             + "WHERE i.C_Invoice_ID=il.C_Invoice_ID"
458             + " AND pc.M_Product_ID=il.M_Product_ID AND pc.C_AcctSchema_ID=a.C_AcctSchema_ID"
459             + " AND ROWNUM=1"
460             + " AND pc.C_AcctSchema_ID=").append(C_AcctSchema_ID).append(" AND i.C_Invoice_ID=")
461             .append(p_vo.Record_ID).append(") ")
462             // update
463 .append("WHERE EXISTS (SELECT * "
464             + "FROM C_Invoice i, C_InvoiceLine il, C_AcctSchema a "
465             + "WHERE i.C_Invoice_ID=il.C_Invoice_ID"
466             + " AND pc.M_Product_ID=il.M_Product_ID AND pc.C_AcctSchema_ID=a.C_AcctSchema_ID"
467             + " AND pc.C_AcctSchema_ID=").append(C_AcctSchema_ID).append(" AND i.C_Invoice_ID=").append(p_vo.Record_ID).append(")");
468         no = DB.executeUpdate(sql.toString());
469         log.debug("M_Product_Costing - Updated=" + no);
470     } // updateProductInfo
471
472 } // Doc_Invoice
473
Popular Tags