FactLine


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.sql.*;
18  
19  import org.apache.log4j.Logger;
20  
21  import org.compiere.util.Env;
22  import org.compiere.util.DB;
23  import org.compiere.model.*;
24  
25  /**
26   *  Accounting Fact Entry.
27   *
28   *  @author     Jorg Janke
29   *  @version    $Id: FactLine.java,v 1.23 2003/11/06 07:10:40 jjanke Exp $
30   */
31  public final class FactLine
32  {
33      /**
34       *  Constructor
35       *
36       *  @param AD_Table_ID  - Table of Document Source
37       *  @param Record_ID    - Record of document
38       *  @param Line_ID      - Optional line id
39       */
40      public FactLine (int AD_Table_ID, int Record_ID, int Line_ID)
41      {
42      //  Log.trace(this,Log.l1_User, "FactLine " + AD_Table_ID + ":" + Record_ID);
43          m_AD_Table_ID = AD_Table_ID;
44          m_Record_ID = Record_ID;
45          m_Line_ID = Line_ID;
46      }   //  FactLine
47  
48      /**
49       *  Public variables are set by Fact.createLine
50       */
51      private int         m_Fact_Acct_ID;
52      //  Doc ID
53      private int         m_AD_Table_ID;
54      private int         m_Record_ID;
55      private int         m_Line_ID;
56  
57      //  Account
58      private Account     m_acct = null;
59      private int         m_C_AcctSchema_ID = 0;
60      private AcctSchema  m_acctSchema = null;
61  
62      //  Source Amt
63      private int         m_C_Currency_ID = 0;
64      private BigDecimal  m_AmtSourceDr = null;
65      private BigDecimal  m_AmtSourceCr = null;
66      //  Acct Amt
67      private BigDecimal  m_AmtAcctDr = null;
68      private BigDecimal  m_AmtAcctCr = null;
69  
70      //  General Info
71      private DocVO       m_docVO = null;
72      private DocLine     m_docLine = null;
73  
74      //  Journal Info
75      private String        m_PostingType = null;
76      private int         m_GL_Budget_ID = 0;
77      private int         m_GL_Category_ID = 0;
78  
79      //  Direstly set
80      private int         m_M_Locator_ID = 0;
81      private int         m_C_LocFrom_ID = 0;
82      private int         m_C_LocTo_ID = 0;
83  
84      //  Balancing Segments
85      private int         m_AD_Org_ID = -1;
86  
87      private Logger      log = Logger.getLogger(getClass());
88  
89      /**
90       *  Dispose
91       */
92      public void dispose()
93      {
94          m_docVO = null;
95          m_docLine = null;
96          m_acct = null;
97      }   //  dispose
98  
99      /**
100      *  Set Account Info
101      *  @param acctSchema account schema
102      *  @param acct account
103      */
104     public void setAccount (AcctSchema acctSchema, Account acct)
105     {
106         m_acctSchema = acctSchema;
107         m_C_AcctSchema_ID = acctSchema.getC_AcctSchema_ID();
108         m_acct = acct;
109     }   //  setAccount
110 
111     /**
112      *  Set Source Amounts
113      *  @param C_Currency_ID currency
114      *  @param AmtSourceDr source amount dr
115      *  @param AmtSourceCr source amount cr
116      *  @return true, if any if the amount is not zero
117      */
118     public boolean setAmtSource (int C_Currency_ID, BigDecimal AmtSourceDr, BigDecimal AmtSourceCr)
119     {
120         m_C_Currency_ID = C_Currency_ID;
121         m_AmtSourceDr = AmtSourceDr;
122         if (m_AmtSourceDr == null)
123             m_AmtSourceDr = Env.ZERO;
124         m_AmtSourceCr = AmtSourceCr;
125         if (m_AmtSourceCr == null)
126             m_AmtSourceCr = Env.ZERO;
127         //  one needs to be non zero
128         if (m_AmtSourceDr.equals(Env.ZERO) && m_AmtSourceCr.equals(Env.ZERO))
129             return false;
130         return true;
131     }   //  setAmtSource
132 
133     /**
134      *  Set Accounted Amounts (alternative: call convert)
135      *  @param AmtAcctDr acct amount dr
136      *  @param AmtAcctCr acct amount cr
137      */
138     public void setAmtAcct(BigDecimal AmtAcctDr, BigDecimal AmtAcctCr)
139     {
140         m_AmtAcctDr = AmtAcctDr;
141         m_AmtAcctCr = AmtAcctCr;
142     }   //  setAmtAcct
143 
144     /**
145      *  Set Document Info
146      *  @param docVO document value object
147      *  @param docLine doc line
148      */
149     public void setDocumentInfo(DocVO docVO, DocLine docLine)
150     {
151         m_docVO = docVO;
152         m_docLine = docLine;
153     }   //  setDocumentInfo
154 
155     /**
156      *  Set Journal Info
157      *  @param GL_Budget_ID budget
158      *  @param GL_Category_ID category
159      */
160     public void setJournalInfo(int GL_Budget_ID, int GL_Category_ID)
161     {
162         m_GL_Budget_ID = GL_Budget_ID;
163         m_GL_Category_ID = GL_Category_ID;
164     }   //  setJournalInfo
165 
166     /**
167      *  Set Posting Type
168      *  @param PostingType posting type
169      */
170     public void setPostingType(String   PostingType)
171     {
172         m_PostingType = PostingType;
173     }   //  setPostingType
174 
175     /**
176      *  Set Warehouse Locator.
177      *  - will overwrite Organization -
178      *  @param M_Locator_ID locator
179      */
180     public void setM_Locator_ID (int M_Locator_ID)
181     {
182         m_M_Locator_ID = M_Locator_ID;
183         //  should not happen - consequence is potentially screwed Org segment balancing
184         if (m_AD_Org_ID != -1)
185             log.error("setM_Locator_ID - Organization already calculated");
186     }   //  setM_Locator_ID
187 
188     /*************************************************************************/
189 
190     /**
191      *  Set Location
192      *  @param C_Location_ID location
193      *  @param isFrom from
194      */
195     public void setLocation (int C_Location_ID, boolean isFrom)
196     {
197         if (isFrom)
198             m_C_LocFrom_ID = C_Location_ID;
199         else
200             m_C_LocTo_ID = C_Location_ID;
201     }   //  setLocator
202 
203     /**
204      *  Set Location from Locator
205      *  @param M_Locator_ID locator
206      *  @param isFrom from
207      */
208     public void setLocationFromLocator (int M_Locator_ID, boolean isFrom)
209     {
210         if (M_Locator_ID == 0)
211             return;
212         int C_Location_ID = 0;
213         String   sql = "SELECT w.C_Location_ID FROM M_Warehouse w, M_Locator l "
214             + "WHERE w.M_Warehouse_ID=l.M_Warehouse_ID AND l.M_Locator_ID=?";
215         try
216         {
217             PreparedStatement pstmt = DB.prepareStatement(sql);
218             pstmt.setInt(1, M_Locator_ID);
219             ResultSet rs = pstmt.executeQuery();
220             if (rs.next())
221                 C_Location_ID = rs.getInt(1);
222             rs.close();
223             pstmt.close();
224         }
225         catch (SQLException e)
226         {
227             log.error ("setLocationFromLocator", e);
228             return;
229         }
230         if (C_Location_ID != 0)
231             setLocation (C_Location_ID, isFrom);
232     }   //  setLocationFromLocator
233 
234     /**
235      *  Set Location from Busoness Partner Location
236      *  @param C_BPartner_Location_ID bp location
237      *  @param isFrom from
238      */
239     public void setLocationFromBPartner (int C_BPartner_Location_ID, boolean isFrom)
240     {
241         if (C_BPartner_Location_ID == 0)
242             return;
243         int C_Location_ID = 0;
244         String   sql = "SELECT C_Location_ID FROM C_BPartner_Location WHERE C_BPartner_Location_ID=?";
245         try
246         {
247             PreparedStatement pstmt = DB.prepareStatement(sql);
248             pstmt.setInt(1, C_BPartner_Location_ID);
249             ResultSet rs = pstmt.executeQuery();
250             if (rs.next())
251                 C_Location_ID = rs.getInt(1);
252             rs.close();
253             pstmt.close();
254         }
255         catch (SQLException e)
256         {
257             log.error ("setLocationFromBPartner", e);
258             return;
259         }
260         if (C_Location_ID != 0)
261             setLocation (C_Location_ID, isFrom);
262     }   //  setLocationFromBPartner
263 
264     /**
265      *  Set Location from Organization
266      *  @param AD_Org_ID org
267      *  @param isFrom from
268      */
269     public void setLocationFromOrg (int AD_Org_ID, boolean isFrom)
270     {
271         if (AD_Org_ID == 0)
272             return;
273         int C_Location_ID = 0;
274         String   sql = "SELECT C_Location_ID FROM AD_OrgInfo WHERE AD_Org_ID=?";
275         try
276         {
277             PreparedStatement pstmt = DB.prepareStatement(sql);
278             pstmt.setInt(1, AD_Org_ID);
279             ResultSet rs = pstmt.executeQuery();
280             if (rs.next())
281                 C_Location_ID = rs.getInt(1);
282             rs.close();
283             pstmt.close();
284         }
285         catch (SQLException e)
286         {
287             log.error ("setLocationFromBPartner", e);
288             return;
289         }
290         if (C_Location_ID != 0)
291             setLocation (C_Location_ID, isFrom);
292     }   //  setLocationFromOrg
293 
294     /*************************************************************************/
295 
296     /**
297      *  Returns Source Balance of line
298      *  @return source balance
299      */
300     public BigDecimal getSourceBalance()
301     {
302         if (m_AmtSourceDr == null)
303             m_AmtSourceDr = Env.ZERO;
304         if (m_AmtSourceCr == null)
305             m_AmtSourceCr = Env.ZERO;
306         //
307         return m_AmtSourceDr.subtract(m_AmtSourceCr);
308     }   //  getSourceBalance
309 
310     /**
311      *  Is Debit Source Balance
312      *  @return true if DR source balance
313      */
314     public boolean isDrSourceBalance()
315     {
316         return getSourceBalance().compareTo(Env.ZERO) != -1;
317     }   //  isDrSourceBalance
318 
319     /**
320      *  Get Accounted Balance
321      *  @return accounting balance
322      */
323     public BigDecimal getAcctBalance()
324     {
325         if (m_AmtAcctDr == null)
326             m_AmtAcctDr = Env.ZERO;
327         if (m_AmtAcctCr == null)
328             m_AmtAcctCr = Env.ZERO;
329         return m_AmtAcctDr.subtract(m_AmtAcctCr);
330     }   //  getAcctBalance
331 
332     /**
333      *  Is Account on Balance Sheet
334      *  @return true if account is a balance sheet account
335      */
336     public boolean isBalanceSheet()
337     {
338         return m_acct.isBalanceSheet();
339     }   //  isBalanceSheet
340 
341     /**
342      *  Currect Accounting Amount.
343      *  <pre>
344      *  Example:    1       -1      1       -1
345      *  Old         100/0   100/0   0/100   0/100
346      *  New         101/0   99/0    0/99    0/101
347      *  </pre>
348      *  @param deltaAmount delta amount
349      */
350     public void currencyCorrect (BigDecimal deltaAmount)
351     {
352         boolean negative = deltaAmount.compareTo(Env.ZERO) < 0;
353         boolean adjustDr = m_AmtAcctDr.compareTo(m_AmtAcctCr) > 0;
354 
355         log.debug ("currencyCorrect - " + deltaAmount.toString()
356             + "; Old-AcctDr=" + m_AmtAcctDr.toString() + ",AcctCr=" + m_AmtAcctCr.toString()
357             + "; Negative=" + negative + "; AdjustDr=" + adjustDr);
358 
359         if (adjustDr)
360             if (negative)
361                 m_AmtAcctDr = m_AmtAcctDr.subtract(deltaAmount);
362             else
363                 m_AmtAcctDr = m_AmtAcctDr.add(deltaAmount);
364         else
365             if (negative)
366                 m_AmtAcctCr = m_AmtAcctCr.add(deltaAmount);
367             else
368                 m_AmtAcctCr = m_AmtAcctCr.subtract(deltaAmount);
369 
370         log.debug("currencyCorrect - New-AcctDr=" + m_AmtAcctDr.toString() + ",AcctCr=" + m_AmtAcctCr.toString());
371     }   //  currencyCorrect
372 
373     /**
374      *  Convert to Accounted Currency
375      *
376      *  @param Acct_Currency_ID acct currency
377      *  @param ConversionDate conversion date
378      *  @param CurrencyRateType rate type
379      *  @return true if converted
380      */
381     public boolean convert (int Acct_Currency_ID, Timestamp ConversionDate, String   CurrencyRateType)
382     {
383         //  Document has no currency
384         if (m_C_Currency_ID == Doc.NO_CURRENCY)
385             m_C_Currency_ID = Acct_Currency_ID;
386 
387         if (Acct_Currency_ID == m_C_Currency_ID)
388         {
389             m_AmtAcctDr = m_AmtSourceDr;
390             m_AmtAcctCr = m_AmtSourceCr;
391             return true;
392         }
393         if (m_docVO == null)
394         {
395             log.error ("convert - No Document VO");
396             return false;
397         }
398         m_AmtAcctDr = DB.getConvertedAmt (m_AmtSourceDr, m_C_Currency_ID, Acct_Currency_ID,
399             ConversionDate, CurrencyRateType, m_docVO.AD_Client_ID, m_docVO.AD_Org_ID);
400         if (m_AmtAcctDr == null)
401             return false;
402         m_AmtAcctCr = DB.getConvertedAmt (m_AmtSourceCr, m_C_Currency_ID, Acct_Currency_ID,
403             ConversionDate, CurrencyRateType, m_docVO.AD_Client_ID, m_docVO.AD_Org_ID);
404         return true;
405     }   //  convert
406 
407     /**
408      *  To String
409      *  @return String
410      */
411     public String   toString()
412     {
413         StringBuffer   sb = new StringBuffer  ("FactLine=[");
414         sb.append(m_AD_Table_ID).append(":").append(m_Record_ID);
415         sb.append(",").append(m_acct);
416         sb.append(",Cur=").append(m_C_Currency_ID);
417         sb.append(", DR=").append(m_AmtSourceDr).append("|").append(m_AmtAcctDr);
418         sb.append(", CR=").append(m_AmtSourceCr).append("|").append(m_AmtAcctCr);
419         sb.append("]");
420         return sb.toString();
421     }   //  toString
422 
423     /*************************************************************************/
424 
425     /**
426      *  Get AD_Client
427      *  @return AD_Client_ID
428      */
429     private int getAD_Client_ID()
430     {
431         int AD_Client_ID = m_docVO.AD_Client_ID;
432         if (AD_Client_ID == 0)
433             AD_Client_ID = m_acct.getAD_Client_ID();
434         return AD_Client_ID;
435     }   //  getAD_Client_ID
436 
437     /**
438      *  Get AD_Org_ID (balancing segment).
439      *  (if not set directly - from document line, document, account, locator)
440      *  <p>
441      *  Note that Locator needs to be set before - otherwise
442      *  segment balancing might produce the wrong results
443      *  @return AD_Org_ID
444      */
445     public int getAD_Org_ID ()
446     {
447         if (m_AD_Org_ID != -1)      //  set earlier
448             return m_AD_Org_ID;
449         //  Prio 1 - get from locator - if exist
450         if (m_M_Locator_ID != 0)
451         {
452             String   sql = "SELECT AD_Org_ID FROM M_Locator WHERE M_Locator_ID=? AND AD_Client_ID=?";
453             try
454             {
455                 PreparedStatement pstmt = DB.prepareStatement(sql);
456                 pstmt.setInt(1, m_M_Locator_ID);
457                 pstmt.setInt(2, getAD_Client_ID());
458                 ResultSet rs = pstmt.executeQuery();
459                 if (rs.next())
460                 {
461                     m_AD_Org_ID = rs.getInt (1);
462                     log.debug("setAD_Org_ID=" + m_AD_Org_ID + " (1 from M_Locator_ID=" + m_M_Locator_ID + ")");
463                 }
464                 else
465                     log.error("getAD_Org_ID - Did not find M_Locator_ID=" + m_M_Locator_ID);
466                 rs.close();
467                 pstmt.close();
468             }
469             catch (SQLException e)
470             {
471                 log.error ("getAD_Org_ID", e);
472             }
473         }   //  M_Locator_ID != 0
474 
475         //  Prio 2 - get from doc line - if exists (document context overwrites)
476         if (m_AD_Org_ID <= 0 && m_docLine != null)
477         {
478             m_AD_Org_ID = m_docLine.getAD_Org_ID ();
479             log.debug ("setAD_Org_ID=" + m_AD_Org_ID + " (2 from DocumentLine)");
480         }
481         //  Prio 3 - get from doc - if not GL
482         if (m_AD_Org_ID <= 0)
483         {
484             if (DocVO.DOCTYPE_GLJournal.equals (m_docVO.DocumentType))
485             {
486                 m_AD_Org_ID = m_acct.getAD_Org_ID (); //    inter-company GL
487                 log.debug ("setAD_Org_ID=" + m_AD_Org_ID + " (3 from Acct)");
488             }
489             else
490             {
491                 m_AD_Org_ID = m_docVO.AD_Org_ID;
492                 log.debug("setAD_Org_ID=" + m_AD_Org_ID + " (3 from Document)");
493             }
494         }
495         //  Prio 4 - get from account - if not GL
496         if (m_AD_Org_ID <= 0)
497         {
498             if (DocVO.DOCTYPE_GLJournal.equals (m_docVO.DocumentType))
499             {
500                 m_AD_Org_ID = m_docVO.AD_Org_ID;
501                 log.debug("setAD_Org_ID=" + m_AD_Org_ID + " (4 from Document)");
502             }
503             else
504             {
505                 m_AD_Org_ID = m_acct.getAD_Org_ID ();
506                 log.debug ("setAD_Org_ID=" + m_AD_Org_ID + " (4 from Acct)");
507             }
508         }
509         //
510         return m_AD_Org_ID;
511     }   //  getAD_Org_ID
512 
513     /**
514      *  Set AD_Org_ID   (balancing segment)
515      *  @param AD_Org_ID org
516      */
517     public void setAD_Org_ID (int AD_Org_ID)
518     {
519         if (AD_Org_ID > 0)
520             m_AD_Org_ID = AD_Org_ID;
521     }   //  setAD_Org_ID
522 
523     /**
524      *  Get/derive Sales Region
525      *  @return Sales Region
526      */
527     private int getC_SalesRegion_ID ()
528     {
529         int C_SalesRegion_ID = 0;
530         if (m_docLine != null)
531             C_SalesRegion_ID = m_docLine.getC_SalesRegion_ID();
532         if (C_SalesRegion_ID == 0)
533             C_SalesRegion_ID = m_docVO.C_SalesRegion_ID;
534         if (C_SalesRegion_ID == 0 && m_docVO.BP_C_SalesRegion_ID > 0)
535             C_SalesRegion_ID = m_docVO.BP_C_SalesRegion_ID;
536         //  derive SalesRegion if AcctSegment
537         if (C_SalesRegion_ID == 0
538             && m_docVO.C_BPartner_Location_ID != 0
539             && m_docVO.BP_C_SalesRegion_ID == -1    //  never tried
540             && m_acctSchema.isAcctSchemaElement(AcctSchemaElement.SEGMENT_SalesRegion))
541         {
542             String   sql = "SELECT C_SalesRegion_ID FROM C_BPartner_Location WHERE C_BPartner_Location_ID=?";
543             C_SalesRegion_ID = DB.getSQLValue(sql, m_docVO.C_BPartner_Location_ID);
544             if (C_SalesRegion_ID != 0)      //  save
545                 m_docVO.BP_C_SalesRegion_ID = C_SalesRegion_ID;
546             else
547                 m_docVO.BP_C_SalesRegion_ID = -2;   //  don't try again
548             log.debug("getC_SalesRegion_ID=" + C_SalesRegion_ID + " (from BPL)" );
549         }
550         if (C_SalesRegion_ID == 0)
551             C_SalesRegion_ID = m_acct.getC_SalesRegion_ID();
552         //
553     //  log.debug("getC_SalesRegion_ID=" + C_SalesRegion_ID + ", C_BPartner_Location_ID=" + m_docVO.C_BPartner_Location_ID
554     //      + ", BP_C_SalesRegion_ID=" + m_docVO.BP_C_SalesRegion_ID + ", SR=" + m_acctSchema.isAcctSchemaElement(AcctSchemaElement.SEGMENT_SalesRegion));
555         return C_SalesRegion_ID;
556     }   //  getC_SalesRegion_ID
557 
558     /*************************************************************************/
559 
560     /**
561      *  Save to Disk.
562      *  Get Info from this, doc-line, document, account
563      *  Optionally create Revenue Recognition Plan
564      *  @param con connection
565      *  @return true if saved
566      */
567     public boolean save (Connection con)
568     {
569         log.debug("save " + toString());
570 
571         /**
572          *  Fill variables
573          */
574         int AD_Client_ID = getAD_Client_ID();
575         int AD_Org_ID = getAD_Org_ID();
576 
577         //  Set Account
578         int Account_ID = m_acct.getAccount_ID();
579 
580         //  Doc Dates
581         Timestamp   DateDoc = null;
582         if (m_docLine != null)
583             DateDoc = m_docLine.getDateDoc();
584         if (DateDoc == null)
585             DateDoc = m_docVO.DateDoc;
586         Timestamp   DateAcct = null;
587         if (m_docLine != null)
588             DateAcct = m_docLine.getDateDoc();
589         if (DateAcct == null)
590             DateAcct = m_docVO.DateAcct;
591         int C_Period_ID = m_docVO.C_Period_ID;
592 
593         //  Set Line Optional Info
594         int C_UOM_ID = 0;
595         BigDecimal Qty = m_docVO.Qty;
596         int C_Tax_ID = 0;
597         if (m_docLine != null)
598         {
599             C_UOM_ID = m_docLine.getC_UOM_ID();
600             Qty = m_docLine.getQty();
601             C_Tax_ID = m_docLine.getC_Tax_ID();
602         }
603 
604         //  Set Account Info
605         int M_Product_ID = 0;
606         if (m_docLine != null)
607             M_Product_ID = m_docLine.getM_Product_ID();
608         if (M_Product_ID == 0)
609             M_Product_ID = m_docVO.M_Product_ID;
610         if (M_Product_ID == 0)
611             M_Product_ID = m_acct.getM_Product_ID();
612 
613         int C_LocFrom_ID = m_C_LocFrom_ID;
614         if (C_LocFrom_ID == 0 && m_docLine != null)
615             C_LocFrom_ID = m_docLine.getC_LocFrom_ID();
616         if (C_LocFrom_ID == 0)
617             C_LocFrom_ID = m_docVO.C_LocFrom_ID;
618         if (C_LocFrom_ID == 0)
619             C_LocFrom_ID = m_acct.getC_LocFrom_ID();
620 
621         int C_LocTo_ID = m_C_LocFrom_ID;
622         if (C_LocTo_ID == 0 && m_docLine != null)
623             C_LocTo_ID = m_docLine.getC_LocTo_ID();
624         if (C_LocTo_ID == 0)
625             C_LocTo_ID = m_docVO.C_LocTo_ID;
626         if (C_LocTo_ID == 0)
627             C_LocTo_ID = m_acct.getC_LocTo_ID();
628 
629         int C_BPartner_ID = 0;
630         if (m_docLine != null)
631             C_BPartner_ID = m_docLine.getC_BPartner_ID();
632         if (C_BPartner_ID == 0)
633             C_BPartner_ID = m_docVO.C_BPartner_ID;
634         if (C_BPartner_ID == 0)
635             C_BPartner_ID = m_acct.getC_BPartner_ID();
636 
637         int AD_OrgTrx_ID = 0;
638         if (m_docLine != null)
639             AD_OrgTrx_ID = m_docLine.getAD_OrgTrx_ID();
640         if (AD_OrgTrx_ID == 0)
641             AD_OrgTrx_ID = m_docVO.AD_OrgTrx_ID;
642         if (AD_OrgTrx_ID == 0)
643             AD_OrgTrx_ID = m_acct.getAD_OrgTrx_ID();
644 
645         int C_SalesRegion_ID = getC_SalesRegion_ID();
646 
647         int C_Project_ID = 0;
648         if (m_docLine != null)
649             C_Project_ID = m_docLine.getC_Project_ID();
650         if (C_Project_ID == 0)
651             C_Project_ID = m_docVO.C_Project_ID;
652         if (C_Project_ID == 0)
653             C_Project_ID = m_acct.getC_Project_ID();
654 
655         int C_Campaign_ID = 0;
656         if (m_docLine != null)
657             C_Campaign_ID = m_docLine.getC_Campaign_ID();
658         if (C_Campaign_ID == 0)
659             C_Campaign_ID = m_docVO.C_Campaign_ID;
660         if (C_Campaign_ID == 0)
661             C_Campaign_ID = m_acct.getC_Campaign_ID();
662 
663         int C_Activity_ID = 0;
664         if (m_docLine != null)
665             C_Activity_ID = m_docLine.getC_Activity_ID();
666         if (C_Activity_ID == 0)
667             C_Activity_ID = m_docVO.C_Activity_ID;
668         if (C_Activity_ID == 0)
669             C_Activity_ID = m_acct.getC_Activity_ID();
670 
671         int User1_ID = 0;
672         if (m_docLine != null)
673             User1_ID = m_docLine.getUser1_ID();
674         if (User1_ID == 0)
675             User1_ID = m_docVO.User1_ID;
676         if (User1_ID == 0)
677             User1_ID = m_acct.getUser1_ID();
678 
679         int User2_ID = 0;
680         if (m_docLine != null)
681             User2_ID = m_docLine.getUser2_ID();
682         if (User2_ID == 0)
683             User2_ID = m_docVO.User2_ID;
684         if (User2_ID == 0)
685             User2_ID = m_acct.getUser2_ID();
686 
687         //  Revenue Recognition for AR Invoices
688         if (m_docVO.DocumentType.equals(DocVO.DOCTYPE_ARInvoice) &&
689             m_docLine != null && m_docLine.getC_RevenueRecognition_ID() != 0)
690         {
691             Account_ID = createRevenueRecognition (con,
692                 m_docLine.getC_RevenueRecognition_ID(), m_docLine.getTrxLine_ID(),
693                 AD_Client_ID, AD_Org_ID, 0, Account_ID,
694                 M_Product_ID, C_BPartner_ID, AD_OrgTrx_ID,
695                 C_LocFrom_ID, C_LocTo_ID, C_SalesRegion_ID, C_Project_ID,
696                 C_Campaign_ID, C_Activity_ID, User1_ID, User2_ID);
697         }
698 
699         //  Description
700         StringBuffer   description = new StringBuffer  ();
701         description.append(m_docVO.DocumentNo);
702         //  ... line
703         if (m_docLine != null)
704         {
705             description.append(" # ").append(m_docLine.getLine());
706             if (m_docLine.getDescription() != null)
707                 description.append(" (").append(m_docLine.getDescription()).append(")");
708         }
709         //  ... cannot distinguish between header and tax
710 
711         if (description.length() > 255)
712             description = new StringBuffer  (description.substring(0,254));
713 
714         /**
715          *  Create SQL Statement
716          */
717         StringBuffer   sql = new StringBuffer   ("INSERT INTO Fact_Acct "
718             + "(Fact_Acct_ID,AD_Client_ID,AD_Org_ID,"
719             + " IsActive,Created,CreatedBy,Updated,UpdatedBy,"
720             + " C_AcctSchema_ID,Account_ID,DateTrx,DateAcct,"
721             + " C_Period_ID,AD_Table_ID,Record_ID,Line_ID,"
722             + " GL_Category_ID,GL_Budget_ID,"
723             + " C_Tax_ID,PostingType,"
724             + " C_Currency_ID,AmtSourceDR,AmtSourceCR,AmtAcctDR,AmtAcctCR,"
725             + " C_UOM_ID,Qty,M_Locator_ID,"
726             + " M_Product_ID,C_BPartner_ID,AD_OrgTrx_ID,"
727             + " C_LocFrom_ID,C_LocTo_ID,C_SalesRegion_ID,"
728             + " C_Project_ID,C_Campaign_ID,C_Activity_ID,"
729             + " User1_ID,User2_ID,Description) VALUES (");
730 
731         //  Fact_Acct_ID, AD_Client_ID, AD_Org_ID,
732         m_Fact_Acct_ID = DB.getKeyNextNo(AD_Client_ID, "Fact_Acct");
733         sql.append(m_Fact_Acct_ID).append(",")
734             .append(AD_Client_ID).append(",")
735             .append(AD_Org_ID).append(", ");
736 
737         //  IsActive, Created, CreatedBy, Updated, UpdatedBy,
738         sql.append("'Y',SysDate,0,SysDate,0, ");
739 
740         //  C_AcctSchema_ID, Account_ID, DateTrx, DateAcct,
741         sql.append(m_C_AcctSchema_ID).append(",")
742             .append(Account_ID).append(",")
743             .append(DB.TO_DATE(DateDoc)).append(",")
744             .append(DB.TO_DATE(DateAcct)).append(", ");
745 
746         //  C_Period_ID, AD_Table_ID, Record_ID, Line_ID,
747         if (C_Period_ID == 0)
748             sql.append("NULL,");
749         else
750             sql.append(C_Period_ID).append(",");
751         sql.append(m_AD_Table_ID).append(",")
752             .append(m_Record_ID).append(",");
753         if (m_Line_ID == 0)
754             sql.append("NULL, ");
755         else
756             sql.append(m_Line_ID).append(", ");
757 
758         //  GL_Category_ID, GL_Budget_ID, (optional)
759         if (m_GL_Category_ID == 0)
760             sql.append("NULL,");
761         else
762             sql.append(m_GL_Category_ID).append(",");
763         if (m_GL_Budget_ID == 0)
764             sql.append("NULL, ");
765         else
766             sql.append(m_GL_Budget_ID).append(", ");
767 
768         //  C_Tax_ID, PostingType,
769         if (C_Tax_ID == 0)
770             sql.append("NULL,");
771         else
772             sql.append(C_Tax_ID).append(",");
773         sql.append("'").append(m_PostingType).append("', ");
774 
775         //  C_Currency_ID, AmtSourceDR, AmtSourceCR, AmtAcctDR, AmtAcctCR,
776         sql.append(m_C_Currency_ID).append(",")
777             .append(m_AmtSourceDr).append(",")
778             .append(m_AmtSourceCr).append(",")
779             .append(m_AmtAcctDr).append(",")
780             .append(m_AmtAcctCr).append(", ");
781 
782         //  C_UOM_ID, Qty, M_Locator_ID,
783         if (C_UOM_ID == 0)
784             sql.append("NULL,");
785         else
786             sql.append(C_UOM_ID).append(",");
787         if (Qty == null)
788             sql.append("NULL, ");
789         else
790             sql.append(Qty).append(", ");
791         if (m_M_Locator_ID == 0)
792             sql.append("NULL,");
793         else
794             sql.append(m_M_Locator_ID).append(",");
795 
796         //  M_Product_ID, C_BPartner_ID, AD_OrgTrx_ID,
797         if (M_Product_ID == 0)
798             sql.append("NULL,");
799         else
800             sql.append(M_Product_ID).append(",");
801         if (C_BPartner_ID == 0)
802             sql.append("NULL,");
803         else
804             sql.append(C_BPartner_ID).append(",");
805         if (AD_OrgTrx_ID == 0)
806             sql.append("NULL, ");
807         else
808             sql.append(AD_OrgTrx_ID).append(", ");
809 
810         //  C_LocFrom_ID, C_LocTo_ID, C_SalesRegion_ID,
811         if (C_LocFrom_ID == 0)
812             sql.append("NULL,");
813         else
814             sql.append(C_LocFrom_ID).append(",");
815         if (C_LocTo_ID == 0)
816             sql.append("NULL,");
817         else
818             sql.append(C_LocTo_ID).append(",");
819         if (C_SalesRegion_ID == 0)
820             sql.append("NULL, ");
821         else
822             sql.append(C_SalesRegion_ID).append(", ");
823 
824         //  C_Project_ID, C_Campaign_ID, C_Activity_ID,
825         if (C_Project_ID == 0)
826             sql.append("NULL,");
827         else
828             sql.append(C_Project_ID).append(",");
829         if (C_Campaign_ID == 0)
830             sql.append("NULL,");
831         else
832             sql.append(C_Campaign_ID).append(",");
833         if (C_Activity_ID == 0)
834             sql.append("NULL, ");
835         else
836             sql.append(C_Activity_ID).append(", ");
837 
838         //  User1_ID, User2_ID, Description
839         if (User1_ID == 0)
840             sql.append("NULL,");
841         else
842             sql.append(User1_ID).append(",");
843         if (User2_ID == 0)
844             sql.append("NULL,");
845         else
846             sql.append(User2_ID).append(",");
847         sql.append(DB.TO_STRING(description.toString(), 255)).append(")");
848 
849         /**
850          *  Save to DB
851          */
852     //  Log.trace(Log.l6_Database, "FactLine.save SQL=" + sql.toString());
853         int no = 0;
854         try
855         {
856             Statement stmt = con.createStatement();
857             no = stmt.executeUpdate(sql.toString());
858             stmt.close();
859         }
860         catch (SQLException e)
861         {
862             log.error ("save\n" + sql.toString(), e);
863             no = 0;
864         }
865         return no == 1;
866     }   //  save
867 
868     /*************************************************************************/
869 
870     /**
871      *  Revenue Recognition.
872      *  Called from FactLine.save
873      *  <p>
874      *  Create Revenue recognition plan and return Unearned Revenue account
875      *  to be used instead of Revenue Account. If not found, it returns
876      *  the revenue account.
877      *
878      *  @param con connection
879      *  @param C_RevenueRecognition_ID revenue recognition
880      *  @param C_InvoiceLine_ID invoice line
881      *  @param AD_Client_ID client
882      *  @param AD_Org_ID org
883      *  @param AD_User_ID user
884      *  @param Account_ID of Revenue Account
885      *  @param M_Product_ID product
886      *  @param C_BPartner_ID bpartner
887      *  @param AD_OrgTrx_ID trx org
888      *  @param C_LocFrom_ID loc from
889      *  @param C_LocTo_ID loc to
890      *  @param C_SRegion_ID sales region
891      *  @param C_Project_ID project
892      *  @param C_Campaign_ID campaign
893      *  @param C_Activity_ID activity
894      *  @param User1_ID user1
895      *  @param User2_ID user2
896      *  @return Account_ID for Unearned Revenue or Revenue Account if not found
897      */
898     private int createRevenueRecognition (Connection con,
899         int C_RevenueRecognition_ID, int C_InvoiceLine_ID,
900         int AD_Client_ID, int AD_Org_ID, int AD_User_ID, int Account_ID,
901         int M_Product_ID, int C_BPartner_ID, int AD_OrgTrx_ID,
902         int C_LocFrom_ID, int C_LocTo_ID, int C_SRegion_ID, int C_Project_ID,
903         int C_Campaign_ID, int C_Activity_ID, int User1_ID, int User2_ID)
904     {
905     //  Log.trace(Log.l6_Database, "FactLine.createRevenueRecognition from Accout_ID=" + Account_ID);
906         //  get VC for P_Revenue (from Product)
907         int P_Revenue_Acct = DB.getValidCombination(AD_Client_ID, AD_Org_ID,
908             m_C_AcctSchema_ID, Account_ID, 0, null, AD_User_ID,
909             M_Product_ID, C_BPartner_ID, AD_OrgTrx_ID,
910             C_LocFrom_ID, C_LocTo_ID, C_SRegion_ID, C_Project_ID,
911             C_Campaign_ID, C_Activity_ID, User1_ID, User2_ID);
912         if (P_Revenue_Acct == 0)
913         {
914             log.error ("createRevenueRecognition - Revenue_Acct not found");
915             return Account_ID;
916         }
917 
918         //  get Unearned Revenue Acct from BPartner Group
919         int UnearnedRevenue_Acct = 0;
920         int new_Account_ID = 0;
921         String   sql = "SELECT ga.UnearnedRevenue_Acct, vc.Account_ID "
922             + "FROM C_BP_Group_Acct ga, C_BPartner p, C_ValidCombination vc "
923             + "WHERE ga.C_BP_Group_ID=p.C_BP_Group_ID"
924             + " AND ga.UnearnedRevenue_Acct=vc.C_ValidCombination_ID"
925             + " AND ga.C_AcctSchema_ID=? AND p.C_BPartner_ID=?";
926         try
927         {
928             PreparedStatement pstmt = DB.prepareStatement(sql);
929             pstmt.setInt(1, m_C_AcctSchema_ID);
930             pstmt.setInt(2, C_BPartner_ID);
931             ResultSet rs = pstmt.executeQuery();
932             if (rs.next())
933             {
934                 UnearnedRevenue_Acct = rs.getInt(1);
935                 new_Account_ID = rs.getInt(2);
936             }
937             rs.close();
938             pstmt.close();
939         }
940         catch (SQLException e)
941         {
942             log.error ("createRevenueRecognition_1", e);
943         }
944         if (new_Account_ID == 0)
945         {
946             log.error ("createRevenueRecognition - UnearnedRevenue_Acct not found");
947             return Account_ID;
948         }
949 
950         //  Insert record in C_RevenueRecognition_Plan to start batch process generation
951         StringBuffer   isql = new StringBuffer  ();
952         isql.append("INSERT INTO C_RevenueRecognition_Plan "
953             + "(C_RevenueRecognition_Plan_ID,C_RevenueRecognition_ID,C_AcctSchema_ID, "
954             + "AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy, "
955             + "C_InvoiceLine_ID,UnearnedRevenue_Acct,P_Revenue_Acct, "
956             + "C_Currency_ID,TotalAmt,RecognizedAmt) VALUES (");
957 
958         //  C_RevenueRecognition_Plan_ID, C_RevenueRecognition_ID, C_AcctSchema_ID,
959         int C_RevenueRecognition_Plan_ID = DB.getKeyNextNo(AD_Client_ID, "C_RevenueRecognition_Plan");
960         isql.append(C_RevenueRecognition_Plan_ID).append(",")
961             .append(C_RevenueRecognition_ID).append(",")
962             .append(m_C_AcctSchema_ID).append(", ");
963         //  AD_Client_ID,AD_Org_ID, IsActive, Created,CreatedBy, Updated,UpdatedBy,
964         isql.append(AD_Client_ID).append(",")
965             .append(AD_Org_ID).append(",'Y',SysDate,")
966             .append(AD_User_ID).append(",SysDate,").append(AD_User_ID).append(", ");
967         //  C_InvoiceLine_ID, UnearnedRevenue_Acct, P_Revenue_Acct,
968         isql.append(C_InvoiceLine_ID).append(",")
969             .append(UnearnedRevenue_Acct).append(",")
970             .append(P_Revenue_Acct).append(", ");
971         //  C_Currency_ID, TotalAmt, RecognizedAmt)
972         isql.append(m_C_Currency_ID).append(",")
973             .append(getAcctBalance()).append(",0)");
974 
975         int no = 0;
976         try
977         {
978             Statement stmt = con.createStatement();
979             no = stmt.executeUpdate(isql.toString());
980             stmt.close();
981         }
982         catch (SQLException e)
983         {
984             log.error ("createRevenueRecognition_2 SQL=" + isql.toString(), e);
985             no = 0;
986         }
987         if (no != 1)
988         {
989             log.error ("createRevenueRecognition - Plan NOT created");
990             return Account_ID;
991         }
992         log.debug ("createRevenueRecognition From Acctount_ID=" + Account_ID + " to " + new_Account_ID
993             + " - Plan from UnearnedRevenue_Acct=" + UnearnedRevenue_Acct + " to Revenue_Acct=" + P_Revenue_Acct);
994         return new_Account_ID;
995     }   //  createRevenueRecognition
996 
997 
998     /*************************************************************************/
999 
1000    /**
1001     * Update Line with reversed Original in Accounting Currency
1002     * @param AD_Table_ID table
1003     * @param Line_ID line
1004     * @param multiplier targetQty/documentQty
1005     * @return true if success
1006     */
1007    public boolean updateReverseLine (int AD_Table_ID, int Line_ID, BigDecimal multiplier)
1008    {
1009        boolean success = false;
1010
1011        String   sql = "SELECT AmtAcctDr,AmtAcctCr FROM Fact_Acct "
1012            + "WHERE C_AcctSchema_ID=? AND AD_Table_ID=?"
1013            + " AND Line_ID=? AND Account_ID=?";
1014        try
1015        {
1016            PreparedStatement pstmt = DB.prepareStatement(sql);
1017            pstmt.setInt(1, m_C_AcctSchema_ID);
1018            pstmt.setInt(2, AD_Table_ID);
1019            pstmt.setInt(3, Line_ID);
1020            pstmt.setInt(4, m_acct.getAccount_ID());
1021            ResultSet rs = pstmt.executeQuery();
1022            if (rs.next())
1023            {
1024                //  Accounted Amounts - reverse
1025                BigDecimal cr = rs.getBigDecimal("AmtAcctCr");
1026                BigDecimal dr = rs.getBigDecimal("AmtAcctDr");
1027                m_AmtAcctDr = cr.multiply(multiplier);
1028                m_AmtAcctCr = dr.multiply(multiplier);
1029                //  Source Amounts
1030                m_AmtSourceDr = m_AmtAcctDr;
1031                m_AmtSourceCr = m_AmtAcctCr;
1032                //
1033                success = true;
1034                log.debug (new StringBuffer  ("updateReverseLine (Table=").append(AD_Table_ID)
1035                    .append(",Line=").append(Line_ID)
1036                    .append(",Account=").append(m_acct)
1037                    .append(",dr=").append(dr).append(",cr=").append(cr)
1038                    .append(") - DR=").append(m_AmtSourceDr).append("|").append(m_AmtAcctDr)
1039                    .append(", CR=").append(m_AmtSourceCr).append("|").append(m_AmtAcctCr)
1040                    .toString());
1041            }
1042            else
1043                log.error (new StringBuffer  ("updateReverseLine - Not Found ")
1044                    .append(", C_AcctSchema_ID=").append(m_C_AcctSchema_ID)
1045                    .append(", AD_Table_ID=").append(AD_Table_ID)
1046                    .append(", Line_ID=").append(Line_ID)
1047                    .append(", Account_ID=").append(m_acct.getAccount_ID()).toString());
1048            rs.close();
1049            pstmt.close();
1050        }
1051        catch (SQLException e)
1052        {
1053            log.error ("updateReverseLine", e);
1054        }
1055        return success;
1056    }   //  updateReverseLine
1057
1058}   //  FactLine
1059
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags