ImportInvoice


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-2003 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.model.*;
20  import org.compiere.util.*;
21  
22  /**
23   *  Import Invoice from I_Invoice
24   *
25   *  @author     Jorg Janke
26   *  @version    $Id: ImportInvoice.java,v 1.5 2003/09/29 04:07:28 jjanke Exp $
27   */
28  public class ImportInvoice extends SvrProcess
29  {
30      /**
31       *  Import BPartner Constructor
32       */
33      public ImportInvoice()
34      {
35          super();
36      }   //  ImportInvoice
37  
38      /** Client to be imported to        */
39      private int             m_AD_Client_ID = 0;
40      /** Organization to be imported to      */
41      private int             m_AD_Org_ID = 0;
42      /** Delete old Imported             */
43      private boolean         m_deleteOldImported = false;
44      /** Document Action                 */
45      private String            m_docAction = MInvoice.DOCACTION_Process;
46  
47  
48      /** Effective                       */
49      private Timestamp       m_DateValue = null;
50  
51      /**
52       *  Prepare - e.g., get Parameters.
53       */
54      protected void prepare()
55      {
56          ProcessInfoParameter[] para = getParameter();
57          for (int i = 0; i < para.length; i++)
58          {
59              String   name = para[i].getParameterName();
60              if (name.equals("AD_Client_ID"))
61                  m_AD_Client_ID = ((BigDecimal)para[i].getParameter()).intValue();
62              else if (name.equals("AD_Org_ID"))
63                  m_AD_Org_ID = ((BigDecimal)para[i].getParameter()).intValue();
64              else if (name.equals("DeleteOldImported"))
65                  m_deleteOldImported = "Y".equals(para[i].getParameter());
66              else if (name.equals("DocAction"))
67                  m_docAction = (String  )para[i].getParameter();
68              else
69                  log.error("prepare - Unknown Parameter: " + name);
70          }
71          if (m_DateValue == null)
72              m_DateValue = new Timestamp (System.currentTimeMillis());
73      }   //  prepare
74  
75  
76      /**
77       *  Perrform process.
78       *  @return Message
79       *  @throws Exception
80       */
81      protected String   doIt() throws java.lang.Exception  
82      {
83          StringBuffer   sql = null;
84          int no = 0;
85          String   clientCheck = " AND AD_Client_ID=" + m_AD_Client_ID;
86  
87          //  ****    Prepare ****
88  
89          //  Delete Old Imported
90          if (m_deleteOldImported)
91          {
92              sql = new StringBuffer   ("DELETE I_Invoice "
93                    + "WHERE I_IsImported='Y'").append (clientCheck);
94              no = DB.executeUpdate (sql.toString ());
95              log.debug ("doIt - Delete Old Impored =" + no);
96          }
97  
98          //  Set Client, Org, IsActive, Created/Updated
99          sql = new StringBuffer   ("UPDATE I_Invoice "
100               + "SET AD_Client_ID = COALESCE (AD_Client_ID,").append (m_AD_Client_ID).append ("),"
101               + " AD_Org_ID = COALESCE (AD_Org_ID,").append (m_AD_Org_ID).append ("),"
102               + " IsActive = COALESCE (IsActive, 'Y'),"
103               + " Created = COALESCE (Created, SysDate),"
104               + " CreatedBy = COALESCE (CreatedBy, 0),"
105               + " Updated = COALESCE (Updated, SysDate),"
106               + " UpdatedBy = COALESCE (UpdatedBy, 0),"
107               + " I_ErrorMsg = NULL,"
108               + " I_IsImported = 'N' "
109               + "WHERE I_IsImported<>'Y' OR I_IsImported IS NULL");
110         no = DB.executeUpdate (sql.toString ());
111         log.info ("doIt - Reset=" + no);
112 
113         sql = new StringBuffer   ("UPDATE I_Invoice o "
114             + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Org, '"
115             + "WHERE (AD_Org_ID IS NULL OR AD_Org_ID=0"
116             + " OR EXISTS (SELECT * FROM AD_Org oo WHERE o.AD_Org_ID=oo.AD_Org_ID AND (oo.IsSummary='Y' OR oo.IsActive='N')))"
117             + " AND I_IsImported<>'Y'").append (clientCheck);
118         no = DB.executeUpdate (sql.toString ());
119         if (no != 0)
120             log.warn ("doIt - Invalid Org=" + no);
121 
122         //  Document Type - PO - SO
123         sql = new StringBuffer   ("UPDATE I_Invoice o "
124               + "SET C_DocType_ID=(SELECT C_DocType_ID FROM C_DocType d WHERE d.Name=o.DocTypeName"
125               + " AND d.DocBaseType='POO' AND o.AD_Client_ID=d.AD_Client_ID) "
126               + "WHERE C_DocType_ID IS NULL AND IsSOTrx='N' AND DocTypeName IS NOT NULL AND I_IsImported<>'Y'").append (clientCheck);
127         no = DB.executeUpdate (sql.toString ());
128         log.debug ("doIt - Set PO DocType=" + no);
129         sql = new StringBuffer   ("UPDATE I_Invoice o "
130               + "SET C_DocType_ID=(SELECT C_DocType_ID FROM C_DocType d WHERE d.Name=o.DocTypeName"
131               + " AND d.DocBaseType='SOO' AND o.AD_Client_ID=d.AD_Client_ID) "
132               + "WHERE C_DocType_ID IS NULL AND IsSOTrx='Y' AND DocTypeName IS NOT NULL AND I_IsImported<>'Y'").append (clientCheck);
133         no = DB.executeUpdate (sql.toString ());
134         log.debug ("doIt - Set SO DocType=" + no);
135         sql = new StringBuffer   ("UPDATE I_Invoice o "
136               + "SET C_DocType_ID=(SELECT C_DocType_ID FROM C_DocType d WHERE d.Name=o.DocTypeName"
137               + " AND d.DocBaseType IN ('SOO','POO') AND o.AD_Client_ID=d.AD_Client_ID) "
138             //+ "WHERE C_DocType_ID IS NULL AND IsSOTrx IS NULL AND DocTypeName IS NOT NULL AND I_IsImported<>'Y'").append (clientCheck);
139               + "WHERE C_DocType_ID IS NULL AND DocTypeName IS NOT NULL AND I_IsImported<>'Y'").append (clientCheck);
140         no = DB.executeUpdate (sql.toString ());
141         log.debug ("doIt - Set DocType=" + no);
142         sql = new StringBuffer   ("UPDATE I_Invoice "
143               + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid DocTypeName, ' "
144               + "WHERE C_DocType_ID IS NULL AND DocTypeName IS NOT NULL"
145               + " AND I_IsImported<>'Y'").append (clientCheck);
146         no = DB.executeUpdate (sql.toString ());
147         if (no != 0)
148             log.warn ("doIt - Invalid DocTypeName=" + no);
149         //  DocType Default
150         sql = new StringBuffer   ("UPDATE I_Invoice o "
151               + "SET C_DocType_ID=(SELECT C_DocType_ID FROM C_DocType d WHERE d.IsDefault='Y'"
152               + " AND d.DocBaseType='POO' AND ROWNUM=1 AND o.AD_Client_ID=d.AD_Client_ID) "
153               + "WHERE C_DocType_ID IS NULL AND IsSOTrx='N' AND I_IsImported<>'Y'").append (clientCheck);
154         no = DB.executeUpdate (sql.toString ());
155         log.debug ("doIt - Set PO Default DocType=" + no);
156         sql = new StringBuffer   ("UPDATE I_Invoice o "
157               + "SET C_DocType_ID=(SELECT C_DocType_ID FROM C_DocType d WHERE d.IsDefault='Y'"
158               + " AND d.DocBaseType='SOO' AND ROWNUM=1 AND o.AD_Client_ID=d.AD_Client_ID) "
159               + "WHERE C_DocType_ID IS NULL AND IsSOTrx='Y' AND I_IsImported<>'Y'").append (clientCheck);
160         no = DB.executeUpdate (sql.toString ());
161         log.debug ("doIt - Set SO Default DocType=" + no);
162         sql = new StringBuffer   ("UPDATE I_Invoice o "
163               + "SET C_DocType_ID=(SELECT C_DocType_ID FROM C_DocType d WHERE d.IsDefault='Y'"
164               + " AND d.DocBaseType IN('SOO','POO') AND ROWNUM=1 AND o.AD_Client_ID=d.AD_Client_ID) "
165               + "WHERE C_DocType_ID IS NULL AND IsSOTrx IS NULL AND I_IsImported<>'Y'").append (clientCheck);
166         no = DB.executeUpdate (sql.toString ());
167         log.debug ("doIt - Set Default DocType=" + no);
168         sql = new StringBuffer   ("UPDATE I_Invoice "
169               + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=No DocType, ' "
170               + "WHERE C_DocType_ID IS NULL"
171               + " AND I_IsImported<>'Y'").append (clientCheck);
172         no = DB.executeUpdate (sql.toString ());
173         if (no != 0)
174             log.warn ("doIt - No DocType=" + no);
175 
176         //  Set IsSOTrx
177         sql = new StringBuffer   ("UPDATE I_Invoice o SET IsSOTrx='Y' "
178               + "WHERE EXISTS (SELECT * FROM C_DocType d WHERE o.C_DocType_ID=d.C_DocType_ID AND d.DocBaseType='SOO' AND o.AD_Client_ID=d.AD_Client_ID)"
179               + " AND C_DocType_ID IS NOT NULL"
180               + " AND I_IsImported<>'Y'").append (clientCheck);
181         no = DB.executeUpdate (sql.toString ());
182         log.debug ("doIt - Set IsSOTrx=Y=" + no);
183         sql = new StringBuffer   ("UPDATE I_Invoice o SET IsSOTrx='N' "
184               + "WHERE EXISTS (SELECT * FROM C_DocType d WHERE o.C_DocType_ID=d.C_DocType_ID AND d.DocBaseType='POO' AND o.AD_Client_ID=d.AD_Client_ID)"
185               + " AND C_DocType_ID IS NOT NULL"
186               + " AND I_IsImported<>'Y'").append (clientCheck);
187         no = DB.executeUpdate (sql.toString ());
188         log.debug ("doIt - Set IsSOTrx=N=" + no);
189 
190         //  Price List
191         sql = new StringBuffer   ("UPDATE I_Invoice o "
192               + "SET M_PriceList_ID=(SELECT M_PriceList_ID FROM M_PriceList p WHERE p.IsDefault='Y'"
193               + " AND p.C_Currency_ID=o.C_Currency_ID AND p.IsSOPriceList=o.IsSOTrx AND ROWNUM=1 AND o.AD_Client_ID=p.AD_Client_ID) "
194               + "WHERE M_PriceList_ID IS NULL AND I_IsImported<>'Y'").append (clientCheck);
195         no = DB.executeUpdate (sql.toString ());
196         log.debug("doIt - Set Default Currency PriceList=" + no);
197         sql = new StringBuffer   ("UPDATE I_Invoice o "
198               + "SET M_PriceList_ID=(SELECT M_PriceList_ID FROM M_PriceList p WHERE p.IsDefault='Y'"
199               + " AND p.IsSOPriceList=o.IsSOTrx AND ROWNUM=1 AND o.AD_Client_ID=p.AD_Client_ID) "
200               + "WHERE M_PriceList_ID IS NULL AND C_Currency_ID IS NULL AND I_IsImported<>'Y'").append (clientCheck);
201         no = DB.executeUpdate (sql.toString ());
202         log.debug("doIt - Set Default PriceList=" + no);
203         sql = new StringBuffer   ("UPDATE I_Invoice o "
204               + "SET M_PriceList_ID=(SELECT M_PriceList_ID FROM M_PriceList p "
205               + " WHERE p.C_Currency_ID=o.C_Currency_ID AND p.IsSOPriceList=o.IsSOTrx AND ROWNUM=1 AND o.AD_Client_ID=p.AD_Client_ID) "
206               + "WHERE M_PriceList_ID IS NULL AND I_IsImported<>'Y'").append (clientCheck);
207         no = DB.executeUpdate (sql.toString ());
208         log.debug("doIt - Set Currency PriceList=" + no);
209         sql = new StringBuffer   ("UPDATE I_Invoice o "
210               + "SET M_PriceList_ID=(SELECT M_PriceList_ID FROM M_PriceList p "
211               + " WHERE p.IsSOPriceList=o.IsSOTrx AND ROWNUM=1 AND o.AD_Client_ID=p.AD_Client_ID) "
212               + "WHERE M_PriceList_ID IS NULL AND C_Currency_ID IS NULL AND I_IsImported<>'Y'").append (clientCheck);
213         no = DB.executeUpdate (sql.toString ());
214         log.debug("doIt - Set PriceList=" + no);
215         //
216         sql = new StringBuffer   ("UPDATE I_Invoice "
217               + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=No PriceList, ' "
218               + "WHERE M_PriceList_ID IS NULL"
219               + " AND I_IsImported<>'Y'").append (clientCheck);
220         no = DB.executeUpdate (sql.toString ());
221         if (no != 0)
222             log.warn("doIt - No PriceList=" + no);
223 
224         //  Payment Term
225         sql = new StringBuffer   ("UPDATE I_Invoice o "
226               + "SET C_PaymentTerm_ID=(SELECT C_PaymentTerm_ID FROM C_PaymentTerm p"
227               + " WHERE o.PaymentTermValue=p.Value AND o.AD_Client_ID=p.AD_Client_ID) "
228               + "WHERE C_PaymentTerm_ID IS NULL AND PaymentTermValue IS NOT NULL AND I_IsImported<>'Y'").append (clientCheck);
229         no = DB.executeUpdate (sql.toString ());
230         log.debug("doIt - Set PaymentTerm=" + no);
231         sql = new StringBuffer   ("UPDATE I_Invoice o "
232               + "SET C_PaymentTerm_ID=(SELECT C_PaymentTerm_ID FROM C_PaymentTerm p"
233               + " WHERE p.IsDefault='Y' AND ROWNUM=1 AND o.AD_Client_ID=p.AD_Client_ID) "
234               + "WHERE C_PaymentTerm_ID IS NULL AND o.PaymentTermValue IS NULL AND I_IsImported<>'Y'").append (clientCheck);
235         no = DB.executeUpdate (sql.toString ());
236         log.debug("doIt - Set Default PaymentTerm=" + no);
237         //
238         sql = new StringBuffer   ("UPDATE I_Invoice "
239               + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=No PaymentTerm, ' "
240               + "WHERE C_PaymentTerm_ID IS NULL"
241               + " AND I_IsImported<>'Y'").append (clientCheck);
242         no = DB.executeUpdate (sql.toString ());
243         if (no != 0)
244             log.warn ("doIt - No PaymentTerm=" + no);
245 
246         //  BP from EMail
247         sql = new StringBuffer   ("UPDATE I_Invoice o "
248               + "SET (C_BPartner_ID,AD_User_ID)=(SELECT C_BPartner_ID,AD_User_ID FROM AD_User u"
249               + " WHERE o.EMail=u.EMail AND o.AD_Client_ID=u.AD_Client_ID AND u.C_BPartner_ID IS NOT NULL) "
250               + "WHERE C_BPartner_ID IS NULL AND EMail IS NOT NULL"
251               + " AND I_IsImported<>'Y'").append (clientCheck);
252         no = DB.executeUpdate (sql.toString ());
253         log.debug("doIt - Set BP from EMail=" + no);
254         //  BP from ContactName
255         sql = new StringBuffer   ("UPDATE I_Invoice o "
256               + "SET (C_BPartner_ID,AD_User_ID)=(SELECT C_BPartner_ID,AD_User_ID FROM AD_User u"
257               + " WHERE o.ContactName=u.Name AND o.AD_Client_ID=u.AD_Client_ID AND u.C_BPartner_ID IS NOT NULL) "
258               + "WHERE C_BPartner_ID IS NULL AND ContactName IS NOT NULL"
259               + " AND EXISTS (SELECT Name FROM AD_User u WHERE o.ContactName=u.Name AND o.AD_Client_ID=u.AD_Client_ID AND u.C_BPartner_ID IS NOT NULL GROUP BY Name HAVING COUNT(*)=1)"
260               + " AND I_IsImported<>'Y'").append (clientCheck);
261         no = DB.executeUpdate (sql.toString ());
262         log.debug("doIt - Set BP from ContactName=" + no);
263         //  BP from Value
264         sql = new StringBuffer   ("UPDATE I_Invoice o "
265               + "SET C_BPartner_ID=(SELECT C_BPartner_ID FROM C_BPartner bp"
266               + " WHERE o.BPartnerValue=bp.Value AND o.AD_Client_ID=bp.AD_Client_ID AND ROWNUM=1) "
267               + "WHERE C_BPartner_ID IS NULL AND BPartnerValue IS NOT NULL"
268               + " AND I_IsImported<>'Y'").append (clientCheck);
269         no = DB.executeUpdate (sql.toString ());
270         log.debug("doIt - Set BP from Value=" + no);
271         //  Default BP
272         sql = new StringBuffer   ("UPDATE I_Invoice o "
273               + "SET C_BPartner_ID=(SELECT C_BPartnerCashTrx_ID FROM AD_ClientInfo c"
274               + " WHERE o.AD_Client_ID=c.AD_Client_ID) "
275               + "WHERE C_BPartner_ID IS NULL AND BPartnerValue IS NULL AND Name IS NULL"
276               + " AND I_IsImported<>'Y'").append (clientCheck);
277         no = DB.executeUpdate (sql.toString ());
278         log.debug("doIt - Set Default BP=" + no);
279 
280         //  Existing Location ? Exact Match
281         sql = new StringBuffer   ("UPDATE I_Invoice o "
282               + "SET C_BPartner_Location_ID=(SELECT C_BPartner_Location_ID"
283               + " FROM C_BPartner_Location bpl INNER JOIN C_Location l ON (bpl.C_Location_ID=l.C_Location_ID)"
284               + " WHERE o.C_BPartner_ID=bpl.C_BPartner_ID AND bpl.AD_Client_ID=o.AD_Client_ID"
285               + " AND DUMP(o.Address1)=DUMP(l.Address1) AND DUMP(o.Address2)=DUMP(l.Address2)"
286               + " AND DUMP(o.City)=DUMP(l.City) AND DUMP(o.Postal)=DUMP(l.Postal)"
287               + " AND DUMP(o.C_Region_ID)=DUMP(l.C_Region_ID) AND DUMP(o.C_Country_ID)=DUMP(l.C_Country_ID)) "
288               + "WHERE C_BPartner_ID IS NOT NULL AND C_BPartner_Location_ID IS NULL"
289               + " AND I_IsImported='N'").append (clientCheck);
290         no = DB.executeUpdate (sql.toString ());
291         log.debug("doIt - Found Location=" + no);
292         //  Set Location from BPartner
293         sql = new StringBuffer   ("UPDATE I_Invoice o "
294               + "SET C_BPartner_Location_ID=(SELECT C_BPartner_Location_ID FROM C_BPartner_Location l"
295               + " WHERE l.C_BPartner_ID=o.C_BPartner_ID AND o.AD_Client_ID=l.AD_Client_ID"
296               + " AND ((l.IsBillTo='Y' AND o.IsSOTrx='Y') OR o.IsSOTrx='N')"
297               + " AND ROWNUM=1) "
298               + "WHERE C_BPartner_ID IS NOT NULL AND C_BPartner_Location_ID IS NULL"
299               + " AND I_IsImported<>'Y'").append (clientCheck);
300         no = DB.executeUpdate (sql.toString ());
301         log.debug("doIt - Set BP Location from BP=" + no);
302         //
303         sql = new StringBuffer   ("UPDATE I_Invoice "
304               + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=No BP Location, ' "
305               + "WHERE C_BPartner_ID IS NOT NULL AND C_BPartner_Location_ID IS NULL"
306               + " AND I_IsImported<>'Y'").append (clientCheck);
307         no = DB.executeUpdate (sql.toString ());
308         if (no != 0)
309             log.warn ("doIt - No BP Location=" + no);
310 
311         //  Set Country
312         sql = new StringBuffer   ("UPDATE I_Invoice o "
313               + "SET CountryCode=(SELECT CountryCode FROM C_Country c WHERE c.IsDefault='Y'"
314               + " AND c.AD_Client_ID IN (0, o.AD_Client_ID) AND ROWNUM=1) "
315               + "WHERE C_BPartner_ID IS NULL AND CountryCode IS NULL AND C_Country_ID IS NULL"
316               + " AND I_IsImported<>'Y'").append (clientCheck);
317         no = DB.executeUpdate (sql.toString ());
318         log.debug("doIt - Set Country Default=" + no);
319         sql = new StringBuffer   ("UPDATE I_Invoice o "
320               + "SET C_Country_ID=(SELECT C_Country_ID FROM C_Country c"
321               + " WHERE o.CountryCode=c.CountryCode AND c.AD_Client_ID IN (0, o.AD_Client_ID)) "
322               + "WHERE C_BPartner_ID IS NULL AND C_Country_ID IS NULL AND CountryCode IS NOT NULL"
323               + " AND I_IsImported<>'Y'").append (clientCheck);
324         no = DB.executeUpdate (sql.toString ());
325         log.debug("doIt - Set Country=" + no);
326         //
327         sql = new StringBuffer   ("UPDATE I_Invoice "
328               + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Country, ' "
329               + "WHERE C_BPartner_ID IS NULL AND C_Country_ID IS NULL"
330               + " AND I_IsImported<>'Y'").append (clientCheck);
331         no = DB.executeUpdate (sql.toString ());
332         if (no != 0)
333             log.warn ("doIt - Invalid Country=" + no);
334 
335         //  Set Region
336         sql = new StringBuffer   ("UPDATE I_Invoice o "
337               + "Set RegionName=(SELECT Name FROM C_Region r"
338               + " WHERE r.IsDefault='Y' AND r.C_Country_ID=o.C_Country_ID"
339               + " AND r.AD_Client_ID IN (0, o.AD_Client_ID) AND ROWNUM=1) "
340               + "WHERE C_BPartner_ID IS NULL AND C_Region_ID IS NULL AND RegionName IS NULL"
341               + " AND I_IsImported<>'Y'").append (clientCheck);
342         no = DB.executeUpdate (sql.toString ());
343         log.debug("doIt - Set Region Default=" + no);
344         //
345         sql = new StringBuffer   ("UPDATE I_Invoice o "
346               + "Set C_Region_ID=(SELECT C_Region_ID FROM C_Region r"
347               + " WHERE r.Name=o.RegionName AND r.C_Country_ID=o.C_Country_ID"
348               + " AND r.AD_Client_ID IN (0, o.AD_Client_ID)) "
349               + "WHERE C_BPartner_ID IS NULL AND C_Region_ID IS NULL AND RegionName IS NOT NULL"
350               + " AND I_IsImported<>'Y'").append (clientCheck);
351         no = DB.executeUpdate (sql.toString ());
352         log.debug("doIt - Set Region=" + no);
353         //
354         sql = new StringBuffer   ("UPDATE I_Invoice o "
355               + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Region, ' "
356               + "WHERE C_BPartner_ID IS NULL AND C_Region_ID IS NULL "
357               + " AND EXISTS (SELECT * FROM C_Country c"
358               + " WHERE c.C_Country_ID=o.C_Country_ID AND c.HasRegion='Y')"
359               + " AND I_IsImported<>'Y'").append (clientCheck);
360         no = DB.executeUpdate (sql.toString ());
361         if (no != 0)
362             log.warn ("doIt - Invalid Region=" + no);
363 
364         //  Product
365         sql = new StringBuffer   ("UPDATE I_Invoice o "
366               + "SET M_Product_ID=(SELECT M_Product_ID FROM M_Product p"
367               + " WHERE o.ProductValue=p.Value AND o.AD_Client_ID=p.AD_Client_ID AND ROWNUM=1) "
368               + "WHERE M_Product_ID IS NULL AND ProductValue IS NOT NULL"
369               + " AND I_IsImported<>'Y'").append (clientCheck);
370         no = DB.executeUpdate (sql.toString ());
371         log.debug("doIt - Set Product from Value=" + no);
372         sql = new StringBuffer   ("UPDATE I_Invoice o "
373               + "SET M_Product_ID=(SELECT M_Product_ID FROM M_Product p"
374               + " WHERE o.UPC=p.UPC AND o.AD_Client_ID=p.AD_Client_ID AND ROWNUM=1) "
375               + "WHERE M_Product_ID IS NULL AND UPC IS NOT NULL"
376               + " AND I_IsImported<>'Y'").append (clientCheck);
377         no = DB.executeUpdate (sql.toString ());
378         log.debug("doIt - Set Product from UPC=" + no);
379         sql = new StringBuffer   ("UPDATE I_Invoice o "
380               + "SET M_Product_ID=(SELECT M_Product_ID FROM M_Product p"
381               + " WHERE o.SKU=p.SKU AND o.AD_Client_ID=p.AD_Client_ID AND ROWNUM=1) "
382               + "WHERE M_Product_ID IS NULL AND SKU IS NOT NULL"
383               + " AND I_IsImported<>'Y'").append (clientCheck);
384         no = DB.executeUpdate (sql.toString ());
385         log.debug("doIt - Set Product fom SKU=" + no);
386         sql = new StringBuffer   ("UPDATE I_Invoice "
387               + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Product, ' "
388               + "WHERE M_Product_ID IS NULL AND (ProductValue IS NOT NULL OR UPC IS NOT NULL OR SKU IS NOT NULL)"
389               + " AND I_IsImported<>'Y'").append (clientCheck);
390         no = DB.executeUpdate (sql.toString ());
391         if (no != 0)
392             log.warn ("doIt - Invalid Product=" + no);
393 
394         //  Tax
395         sql = new StringBuffer   ("UPDATE I_Invoice o "
396               + "SET C_Tax_ID=(SELECT C_Tax_ID FROM C_Tax t"
397               + " WHERE o.TaxIndicator=t.TaxIndicator AND o.AD_Client_ID=t.AD_Client_ID AND ROWNUM=1) "
398               + "WHERE C_Tax_ID IS NULL AND TaxIndicator IS NOT NULL"
399               + " AND I_IsImported<>'Y'").append (clientCheck);
400         no = DB.executeUpdate (sql.toString ());
401         log.debug ("doIt - Set Tax=" + no);
402         sql = new StringBuffer   ("UPDATE I_Invoice "
403               + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Tax, ' "
404               + "WHERE C_Tax_ID IS NULL AND TaxIndicator IS NOT NULL"
405               + " AND I_IsImported<>'Y'").append (clientCheck);
406         no = DB.executeUpdate (sql.toString ());
407         if (no != 0)
408             log.warn ("doIt - Invalid Tax=" + no);
409 
410         //  -- New BPartner ---------------------------------------------------
411 
412         //  Go through Invoice Records w/o
413         sql = new StringBuffer   ("SELECT * FROM I_Invoice "
414               + "WHERE I_IsImported='N' AND C_BPartner_ID IS NULL").append (clientCheck);
415         try
416         {
417             PreparedStatement pstmt = DB.prepareStatement (sql.toString ());
418             ResultSet rs = pstmt.executeQuery ();
419             while (rs.next ())
420             {
421                 X_I_Invoice imp = new X_I_Invoice (getCtx (), rs);
422                 if (imp.getBPartnerValue () == null)
423                 {
424                     if (imp.getEmail () != null)
425                         imp.setBPartnerValue (imp.getEmail ());
426                     else if (imp.getName () != null)
427                         imp.setBPartnerValue (imp.getName ());
428                     else
429                         continue;
430                 }
431                 if (imp.getName () == null)
432                 {
433                     if (imp.getContactName () != null)
434                         imp.setName (imp.getContactName ());
435                     else
436                         imp.setName (imp.getBPartnerValue ());
437                 }
438                 //  BPartner
439                 MBPartner bp = new MBPartner (getCtx (), -1);
440                 bp.setClientOrg (imp.getAD_Client_ID (), imp.getAD_Org_ID ());
441                 bp.setValue (imp.getBPartnerValue ());
442                 bp.setName (imp.getName ());
443                 if (!bp.save ())
444                     continue;
445                 imp.setC_BPartner_ID (bp.getC_BPartner_ID ());
446                 //  Location
447                 X_C_Location loc = new X_C_Location (getCtx (), 0);
448                 loc.setAddress1 (imp.getAddress1 ());
449                 loc.setAddress2 (imp.getAddress2 ());
450                 loc.setCity (imp.getCity ());
451                 loc.setPostal (imp.getPostal ());
452                 if (imp.getC_Region_ID () != 0)
453                     loc.setC_Region_ID (imp.getC_Region_ID ());
454                 loc.setC_Country_ID (imp.getC_Country_ID ());
455                 if (!loc.save ())
456                     continue;
457                 imp.setC_Location_ID (loc.getC_Location_ID ());
458                 //  BP Location
459                 MBPartner_Location bpl = new MBPartner_Location (getCtx (), 0, imp.getC_BPartner_ID ());
460                 bpl.setC_Location_ID (imp.getC_Location_ID ());
461                 bpl.setClientOrg (imp.getAD_Client_ID (), imp.getAD_Org_ID ());
462                 if (!bpl.save ())
463                     continue;
464                 imp.setC_BPartner_Location_ID (bpl.getC_BPartner_Location_ID ());
465                 //  User/Contact
466                 if (imp.getContactName () != null || imp.getEmail () != null || imp.getPhone () != null)
467                 {
468                     MUser u = new MUser (getCtx (), 0, imp.getC_BPartner_ID ());
469                     u.setClientOrg (imp.getAD_Client_ID (), imp.getAD_Org_ID ());
470                     if (imp.getContactName () == null)
471                         u.setName (imp.getName ());
472                     else
473                         u.setName (imp.getContactName ());
474                     u.setEmail (imp.getEmail ());
475                     u.setPhone (imp.getPhone ());
476                     if (u.save ())
477                         imp.setAD_User_ID (u.getAD_User_ID ());
478                 }
479                 imp.save ();
480             } //    for all new BPartners
481             rs.close ();
482             pstmt.close ();
483             //
484         }
485         catch (SQLException e)
486         {
487             log.error ("doIt - CreateBP", e);
488         }
489         sql = new StringBuffer   ("UPDATE I_Invoice "
490               + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=No BPartner, ' "
491               + "WHERE C_BPartner_ID IS NULL"
492               + " AND I_IsImported<>'Y'").append (clientCheck);
493         no = DB.executeUpdate (sql.toString ());
494         if (no != 0)
495             log.warn ("doIt - No BPartner=" + no);
496 
497         //  -- New Invoices -----------------------------------------------------
498 
499         int noInsert = 0;
500         int noInsertLine = 0;
501 
502         //  Go through Invoice Records w/o
503         sql = new StringBuffer   ("SELECT * FROM I_Invoice "
504               + "WHERE I_IsImported='N'").append (clientCheck)
505             .append(" ORDER BY C_BPartner_ID, C_BPartner_Location_ID, I_Invoice_ID");
506         try
507         {
508             PreparedStatement pstmt = DB.prepareStatement (sql.toString ());
509             ResultSet rs = pstmt.executeQuery ();
510             //
511             int C_BPartner_ID = 0;
512             int C_BPartner_Location_ID = 0;
513             MInvoice invoice = null;
514             int lineNo = 0;
515             while (rs.next ())
516             {
517                 X_I_Invoice imp = new X_I_Invoice (getCtx (), rs);
518                 //  New Invoice
519                 if (C_BPartner_ID != imp.getC_BPartner_ID() || C_BPartner_Location_ID != imp.getC_BPartner_Location_ID())
520                 {
521                     if (invoice != null)
522                         invoice.process(m_docAction);
523                     C_BPartner_ID = imp.getC_BPartner_ID();
524                     C_BPartner_Location_ID = imp.getC_BPartner_Location_ID();
525                     invoice = new MInvoice (getCtx(), 0);
526                     invoice.setClientOrg (imp.getAD_Client_ID(), imp.getAD_Org_ID());
527                     invoice.setC_DocTypeTarget_ID(imp.getC_DocType_ID());
528                     invoice.setIsSOTrx(imp.isSOTrx());
529                     if (imp.getDocumentNo() != null)
530                         invoice.setDocumentNo(imp.getDocumentNo());
531                     //
532                     invoice.setC_BPartner_ID(imp.getC_BPartner_ID());
533                     invoice.setC_BPartner_Location_ID(imp.getC_BPartner_Location_ID());
534                     if (imp.getAD_User_ID() != 0)
535                         invoice.setAD_User_ID(imp.getAD_User_ID());
536                     //
537                     if (imp.getDescription() != null)
538                         invoice.setDescription(imp.getDescription());
539                     invoice.setC_PaymentTerm_ID(imp.getC_PaymentTerm_ID());
540                     invoice.setM_PriceList_ID(imp.getM_PriceList_ID());
541                     if (imp.getSalesRep_ID() != 0)
542                         invoice.setSalesRep_ID(imp.getSalesRep_ID());
543                     //
544                     if (imp.getAD_OrgTrx_ID() != 0)
545                         invoice.setAD_OrgTrx_ID(imp.getAD_OrgTrx_ID());
546                     if (imp.getC_Activity_ID() != 0)
547                         invoice.setC_Activity_ID(imp.getC_Activity_ID());
548                     if (imp.getC_Campaign_ID() != 0)
549                         invoice.setC_Campaign_ID(imp.getC_Campaign_ID());
550                     if (imp.getC_Project_ID() != 0)
551                         invoice.setC_Project_ID(imp.getC_Project_ID());
552                     //
553                     invoice.save();
554                     noInsert++;
555                     lineNo = 10;
556                 }
557                 imp.setC_Invoice_ID (invoice.getC_Invoice_ID());
558                 //  New InvoiceLine
559                 MInvoiceLine line = new MInvoiceLine (invoice);
560                 line.setLine(lineNo);
561                 lineNo += 10;
562                 if (imp.getM_Product_ID() != 0)
563                     line.setM_Product_ID(imp.getM_Product_ID());
564                 line.setQtyInvoiced(imp.getQtyOrdered());
565                 line.setPrice();
566                 if (imp.getPriceActual().compareTo(Env.ZERO) != 0)
567                     line.setPriceActual(imp.getPriceActual());
568                 if (imp.getC_Tax_ID() != 0)
569                     line.setC_Tax_ID(imp.getC_Tax_ID());
570                 else
571                 {
572                     line.setTax();
573                     imp.setC_Tax_ID(line.getC_Tax_ID());
574                 }
575                 line.save();
576                 imp.setC_InvoiceLine_ID(line.getC_InvoiceLine_ID());
577                 imp.setI_IsImported(true);
578                 imp.setProcessed(true);
579                 //
580                 if (imp.save())
581                     noInsertLine++;
582             }
583             if (invoice != null)
584                 invoice.process (m_docAction);
585             rs.close();
586             pstmt.close();
587         }
588         catch (Exception   e)
589         {
590             log.error ("doIt - CreateInvoice", e);
591         }
592 
593         //  Set Error to indicator to not imported
594         sql = new StringBuffer   ("UPDATE I_Invoice "
595             + "SET I_IsImported='N', Updated=SysDate "
596             + "WHERE I_IsImported<>'Y'").append(clientCheck);
597         no = DB.executeUpdate(sql.toString());
598         addLog (0, null, new BigDecimal (no), "@Errors@");
599         //
600         addLog (0, null, new BigDecimal (noInsert), "@C_Invoice_ID@: @Inserted@");
601         addLog (0, null, new BigDecimal (noInsertLine), "@C_InvoiceLine_ID@: @Inserted@");
602         return "";
603     }   //  doIt
604 
605 }   //  ImportInvoice
606
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags