|
1
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
28 public class ImportGLJournal extends SvrProcess
29 {
30
31 private int m_AD_Client_ID = 0;
32
33 private int m_AD_Org_ID = 0;
34
35 private int m_C_AcctSchema_ID = 0;
36
37 private Timestamp m_DateAcct = null;
38
39 private boolean m_DeleteOldImported = false;
40
41 private boolean m_IsValidateOnly = false;
42
43 private boolean m_IsImportOnlyNoErrors = true;
44
45
46
49 protected void prepare()
50 {
51 ProcessInfoParameter[] para = getParameter();
52 for (int i = 0; i < para.length; i++)
53 {
54 String  name = para[i].getParameterName();
55 if (para[i].getParameter() == null)
56 ;
57 else if (name.equals("AD_Client_ID"))
58 m_AD_Client_ID = ((BigDecimal)para[i].getParameter()).intValue();
59 else if (name.equals("AD_Org_ID"))
60 m_AD_Org_ID = ((BigDecimal)para[i].getParameter()).intValue();
61 else if (name.equals("C_AcctSchema_ID"))
62 m_C_AcctSchema_ID = ((BigDecimal)para[i].getParameter()).intValue();
63 else if (name.equals("DateAcct"))
64 m_DateAcct = (Timestamp)para[i].getParameter();
65 else if (name.equals("IsValidateOnly"))
66 m_IsValidateOnly = "Y".equals(para[i].getParameter());
67 else if (name.equals("IsImportOnlyNoErrors"))
68 m_IsImportOnlyNoErrors = "Y".equals(para[i].getParameter());
69 else if (name.equals("DeleteOldImported"))
70 m_DeleteOldImported = "Y".equals(para[i].getParameter());
71 else
72 log.error("prepare - Unknown Parameter: " + name);
73 }
74 }
76
77
82 protected String doIt() throws java.lang.Exception
83 {
84 log.info("IsValidateOnly=" + m_IsValidateOnly + ", IsImportOnlyNoErrors=" + m_IsImportOnlyNoErrors);
85 StringBuffer sql = null;
86 int no = 0;
87 String clientCheck = " AND AD_Client_ID=" + m_AD_Client_ID;
88
89
91 if (m_DeleteOldImported)
93 {
94 sql = new StringBuffer ("DELETE I_GLJournal "
95 + "WHERE I_IsImported='Y'").append (clientCheck);
96 no = DB.executeUpdate (sql.toString ());
97 log.debug ("doIt - Delete Old Impored =" + no);
98 }
99
100 sql = new StringBuffer ("UPDATE I_GLJournal "
102 + "SET 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_GLJournal i "
115 + "SET AD_Client_ID=(SELECT c.AD_Client_ID FROM AD_Client c WHERE c.Value=i.ClientValue) "
116 + "WHERE (AD_Client_ID IS NULL OR AD_Client_ID=0) AND ClientValue IS NOT NULL"
117 + " AND I_IsImported<>'Y'");
118 no = DB.executeUpdate (sql.toString ());
119 log.debug ("doIt - Set Client from Value=" + no);
120
121 sql = new StringBuffer ("UPDATE I_GLJournal "
123 + "SET AD_Client_ID = COALESCE (AD_Client_ID,").append (m_AD_Client_ID).append ("),"
124 + " AD_OrgDoc_ID = COALESCE (AD_OrgDoc_ID,").append (m_AD_Org_ID).append ("),");
125 if (m_C_AcctSchema_ID != 0)
126 sql.append(" C_AcctSchema_ID = COALESCE (C_AcctSchema_ID,").append (m_C_AcctSchema_ID).append ("),");
127 if (m_DateAcct != null)
128 sql.append(" DateAcct = COALESCE (DateAcct,").append (DB.TO_DATE(m_DateAcct)).append ("),");
129 sql.append(" Updated = COALESCE (Updated, SysDate) "
130 + "WHERE I_IsImported<>'Y' OR I_IsImported IS NULL");
131 no = DB.executeUpdate (sql.toString ());
132 log.debug ("doIt - Client/DocOrg/Default=" + no);
133
134 sql = new StringBuffer ("UPDATE I_GLJournal o "
136 + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Doc Org, '"
137 + "WHERE (AD_OrgDoc_ID IS NULL OR AD_OrgDoc_ID=0"
138 + " OR EXISTS (SELECT * FROM AD_Org oo WHERE o.AD_Org_ID=oo.AD_Org_ID AND (oo.IsSummary='Y' OR oo.IsActive='N')))"
139 + " AND I_IsImported<>'Y'").append (clientCheck);
140 no = DB.executeUpdate (sql.toString ());
141 if (no != 0)
142 log.warn ("doIt - Invalid Doc Org=" + no);
143
144 sql = new StringBuffer ("UPDATE I_GLJournal i "
146 + "SET C_AcctSchema_ID=(SELECT a.C_AcctSchema_ID FROM C_AcctSchema a"
147 + " WHERE i.AcctSchemaName=a.Name AND i.AD_Client_ID=a.AD_Client_ID) "
148 + "WHERE C_AcctSchema_ID IS NULL AND AcctSchemaName IS NOT NULL"
149 + " AND I_IsImported<>'Y'").append (clientCheck);
150 no = DB.executeUpdate (sql.toString ());
151 log.debug ("doIt - Set AcctSchema from Name=" + no);
152 sql = new StringBuffer ("UPDATE I_GLJournal i "
153 + "SET C_AcctSchema_ID=(SELECT c.C_AcctSchema1_ID FROM AD_ClientInfo c WHERE c.AD_Client_ID=i.AD_Client_ID) "
154 + "WHERE C_AcctSchema_ID IS NULL AND AcctSchemaName IS NULL"
155 + " AND I_IsImported<>'Y'").append (clientCheck);
156 no = DB.executeUpdate (sql.toString ());
157 log.debug ("doIt - Set AcctSchema from Client=" + no);
158 sql = new StringBuffer ("UPDATE I_GLJournal i "
160 + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid AcctSchema, '"
161 + "WHERE (C_AcctSchema_ID IS NULL OR C_AcctSchema_ID=0"
162 + " OR NOT EXISTS (SELECT * FROM C_AcctSchema a WHERE i.AD_Client_ID=a.AD_Client_ID))"
163 + " AND I_IsImported<>'Y'").append (clientCheck);
164 no = DB.executeUpdate (sql.toString ());
165 if (no != 0)
166 log.warn ("doIt - Invalid AcctSchema=" + no);
167
168 sql = new StringBuffer ("UPDATE I_GLJournal i "
170 + "SET DateAcct=SysDate "
171 + "WHERE DateAcct IS NULL"
172 + " AND I_IsImported<>'Y'").append (clientCheck);
173 no = DB.executeUpdate (sql.toString ());
174 log.debug ("doIt - Set DateAcct=" + no);
175
176 sql = new StringBuffer ("UPDATE I_GLJournal i "
178 + "SET C_DocType_ID=(SELECT d.C_DocType_ID FROM C_DocType d"
179 + " WHERE d.Name=i.DocTypeName AND d.DocBaseType='GLJ' AND i.AD_Client_ID=d.AD_Client_ID) "
180 + "WHERE C_DocType_ID IS NULL AND DocTypeName IS NOT NULL"
181 + " AND I_IsImported<>'Y'").append (clientCheck);
182 no = DB.executeUpdate (sql.toString ());
183 log.debug ("doIt - Set DocType=" + no);
184 sql = new StringBuffer ("UPDATE I_GLJournal i "
185 + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid DocType, '"
186 + "WHERE (C_DocType_ID IS NULL OR C_DocType_ID=0"
187 + " OR NOT EXISTS (SELECT * FROM C_DocType d WHERE i.AD_Client_ID=d.AD_Client_ID AND d.DocBaseType='GLJ'))"
188 + " AND I_IsImported<>'Y'").append (clientCheck);
189 no = DB.executeUpdate (sql.toString ());
190 if (no != 0)
191 log.warn ("doIt - Invalid DocType=" + no);
192
193 sql = new StringBuffer ("UPDATE I_GLJournal i "
195 + "SET GL_Category_ID=(SELECT c.GL_Category_ID FROM GL_Category c"
196 + " WHERE c.Name=i.CategoryName AND i.AD_Client_ID=c.AD_Client_ID) "
197 + "WHERE GL_Category_ID IS NULL AND CategoryName IS NOT NULL"
198 + " AND I_IsImported<>'Y'").append (clientCheck);
199 no = DB.executeUpdate (sql.toString ());
200 log.debug ("doIt - Set DocType=" + no);
201 sql = new StringBuffer ("UPDATE I_GLJournal i "
202 + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Category, '"
203 + "WHERE (GL_Category_ID IS NULL OR GL_Category_ID=0)"
204 + " AND I_IsImported<>'Y'").append (clientCheck);
205 no = DB.executeUpdate (sql.toString ());
206 if (no != 0)
207 log.warn ("doIt - Invalid Category=" + no);
208
209 sql = new StringBuffer ("UPDATE I_GLJournal i "
211 + "SET C_Currency_ID=(SELECT c.C_Currency_ID FROM C_Currency c"
212 + " WHERE c.ISO_Code=i.ISO_Code AND c.AD_Client_ID IN (0,i.AD_Client_ID)) "
213 + "WHERE C_Currency_ID IS NULL AND ISO_Code IS NOT NULL"
214 + " AND I_IsImported<>'Y'").append (clientCheck);
215 no = DB.executeUpdate (sql.toString ());
216 log.debug ("doIt - Set Currency from ISO=" + no);
217 sql = new StringBuffer ("UPDATE I_GLJournal i "
218 + "SET C_Currency_ID=(SELECT a.C_Currency_ID FROM C_AcctSchema a"
219 + " WHERE a.C_AcctSchema_ID=i.C_AcctSchema_ID AND a.AD_Client_ID=i.AD_Client_ID)"
220 + "WHERE C_Currency_ID IS NULL AND ISO_Code IS NULL"
221 + " AND I_IsImported<>'Y'").append (clientCheck);
222 no = DB.executeUpdate (sql.toString ());
223 log.debug ("doIt - Set Default Currency=" + no);
224 sql = new StringBuffer ("UPDATE I_GLJournal i "
225 + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Currency, '"
226 + "WHERE (C_Currency_ID IS NULL OR C_Currency_ID=0)"
227 + " AND I_IsImported<>'Y'").append (clientCheck);
228 no = DB.executeUpdate (sql.toString ());
229 if (no != 0)
230 log.warn ("doIt - Invalid Currency=" + no);
231
232 sql = new StringBuffer ("UPDATE I_GLJournal i "
234 + "SET CurrencyRate=1, CurrencyRateType='S'"
235 + "WHERE EXISTS (SELECT * FROM C_AcctSchema a"
236 + " WHERE a.C_AcctSchema_ID=i.C_AcctSchema_ID AND a.C_Currency_ID=i.C_Currency_ID)"
237 + " AND C_Currency_ID IS NOT NULL AND I_IsImported<>'Y'").append (clientCheck);
238 no = DB.executeUpdate (sql.toString ());
239 log.debug ("doIt - Set Home CurrencyRate/Type=" + no);
240 sql = new StringBuffer ("UPDATE I_GLJournal i "
242 + "SET CurrencyRate=(SELECT r.MultiplyRate FROM C_Conversion_Rate r, C_AcctSchema s"
243 + " WHERE s.C_AcctSchema_ID=i.C_AcctSchema_ID AND s.AD_Client_ID=i.AD_Client_ID"
244 + " AND r.C_Currency_ID=i.C_Currency_ID AND r.C_Currency_ID_TO=s.C_Currency_ID"
245 + " AND r.AD_Client_ID=i.AD_Client_ID AND r.AD_Org_ID=i.AD_OrgDoc_ID"
246 + " AND r.ConversionRateType=i.CurrencyRateType"
247 + " AND i.DateAcct BETWEEN r.ValidFrom AND r.ValidTo AND ROWNUM=1"
248 + ") WHERE CurrencyRate IS NULL OR CurrencyRate=0 AND C_Currency_ID>0"
250 + " AND I_IsImported<>'Y'").append (clientCheck);
251 no = DB.executeUpdate (sql.toString ());
252 log.debug ("doIt - Set Org Rate=" + no);
253 sql = new StringBuffer ("UPDATE I_GLJournal i "
254 + "SET CurrencyRate=(SELECT r.MultiplyRate FROM C_Conversion_Rate r, C_AcctSchema s"
255 + " WHERE s.C_AcctSchema_ID=i.C_AcctSchema_ID AND s.AD_Client_ID=i.AD_Client_ID"
256 + " AND r.C_Currency_ID=i.C_Currency_ID AND r.C_Currency_ID_TO=s.C_Currency_ID"
257 + " AND r.AD_Client_ID=i.AD_Client_ID"
258 + " AND r.ConversionRateType=i.CurrencyRateType"
259 + " AND i.DateAcct BETWEEN r.ValidFrom AND r.ValidTo AND ROWNUM=1"
260 + ") WHERE CurrencyRate IS NULL OR CurrencyRate=0 AND C_Currency_ID>0"
262 + " AND I_IsImported<>'Y'").append (clientCheck);
263 no = DB.executeUpdate (sql.toString ());
264 log.debug ("doIt - Set Client Rate=" + no);
265 sql = new StringBuffer ("UPDATE I_GLJournal i "
266 + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=No Rate, '"
267 + "WHERE CurrencyRate IS NULL OR CurrencyRate=0"
268 + " AND I_IsImported<>'Y'").append (clientCheck);
269 no = DB.executeUpdate (sql.toString ());
270 if (no != 0)
271 log.warn ("doIt - No Rate=" + no);
272 sql = new StringBuffer ("UPDATE I_GLJournal i "
273 + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid RateType, '"
274 + "WHERE CurrencyRateType IS NULL OR NOT EXISTS"
275 + " (SELECT * FROM AD_Ref_List r WHERE r.AD_Reference_ID=111 AND i.CurrencyRateType=r.Value)"
276 + " AND I_IsImported<>'Y'").append (clientCheck);
277 no = DB.executeUpdate (sql.toString ());
278 if (no != 0)
279 log.warn ("doIt - Invalid RateType=" + no);
280
281 sql = new StringBuffer ("UPDATE I_GLJournal i "
283 + "SET C_Period_ID=(SELECT p.C_Period_ID FROM C_Period p"
284 + " INNER JOIN C_Year y ON (y.C_Year_ID=p.C_Year_ID)"
285 + " INNER JOIN AD_ClientInfo c ON (c.C_Calendar_ID=y.C_Calendar_ID)"
286 + " WHERE c.AD_Client_ID=i.AD_Client_ID"
287 + " AND i.DateAcct BETWEEN p.StartDate AND p.EndDate AND p.PeriodType='S' AND ROWNUM=1) "
288 + "WHERE C_Period_ID IS NULL"
289 + " AND I_IsImported<>'Y'").append (clientCheck);
290 no = DB.executeUpdate (sql.toString ());
291 log.debug ("doIt - Set Period=" + no);
292 sql = new StringBuffer ("UPDATE I_GLJournal i "
293 + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Period, '"
294 + "WHERE C_Period_ID IS NULL OR C_Period_ID<>"
295 + "(SELECT C_Period_ID FROM C_Period p"
296 + " INNER JOIN C_Year y ON (y.C_Year_ID=p.C_Year_ID)"
297 + " INNER JOIN AD_ClientInfo c ON (c.C_Calendar_ID=y.C_Calendar_ID) "
298 + " WHERE c.AD_Client_ID=i.AD_Client_ID"
299 + " AND i.DateAcct BETWEEN p.StartDate AND p.EndDate AND p.PeriodType='S' AND ROWNUM=1)"
300 + " AND I_IsImported<>'Y'").append (clientCheck);
301 no = DB.executeUpdate (sql.toString ());
302 if (no != 0)
303 log.warn ("doIt - Invalid Period=" + no);
304 sql = new StringBuffer ("UPDATE I_GLJournal i "
305 + "SET I_ErrorMsg=I_ErrorMsg||'WARN=Period Closed, ' "
306 + "WHERE C_Period_ID IS NOT NULL AND NOT EXISTS"
307 + " (SELECT * FROM C_PeriodControl pc WHERE pc.C_Period_ID=i.C_Period_ID AND DocBaseType='GLJ' AND PeriodStatus='O') "
308 + " AND I_IsImported<>'Y'").append (clientCheck);
309 no = DB.executeUpdate (sql.toString ());
310 if (no != 0)
311 log.warn ("doIt - Period Closed=" + no);
312
313 sql = new StringBuffer ("UPDATE I_GLJournal i "
315 + "SET PostingType='A' "
316 + "WHERE PostingType IS NULL AND I_IsImported<>'Y'").append (clientCheck);
317 no = DB.executeUpdate (sql.toString ());
318 log.debug ("doIt - Set Actual PostingType=" + no);
319 sql = new StringBuffer ("UPDATE I_GLJournal i "
320 + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid PostingType, ' "
321 + "WHERE PostingType IS NULL OR NOT EXISTS"
322 + " (SELECT * FROM AD_Ref_List r WHERE r.AD_Reference_ID=125 AND i.PostingType=r.Value)"
323 + " AND I_IsImported<>'Y'").append (clientCheck);
324 no = DB.executeUpdate (sql.toString ());
325 if (no != 0)
326 log.warn ("doIt - Invalid PostingTypee=" + no);
327
328
329
332 sql = new StringBuffer ("UPDATE I_GLJournal i "
334 + "SET AD_Org_ID=(SELECT o.AD_Org_ID FROM AD_Org o"
335 + " WHERE o.Value=i.OrgValue AND o.IsSummary='N' AND i.AD_Client_ID=o.AD_Client_ID) "
336 + "WHERE (AD_Org_ID IS NULL OR AD_Org_ID=0) AND OrgValue IS NOT NULL"
337 + " AND (C_ValidCombination_ID IS NULL OR C_ValidCombination_ID=0) AND I_IsImported<>'Y'");
338 no = DB.executeUpdate (sql.toString ());
339 log.debug ("doIt - Set Org from Value=" + no);
340 sql = new StringBuffer ("UPDATE I_GLJournal i "
341 + "SET AD_Org_ID=AD_OrgDoc_ID "
342 + "WHERE (AD_Org_ID IS NULL OR AD_Org_ID=0) AND OrgValue IS NULL AND AD_OrgDoc_ID IS NOT NULL AND AD_OrgDoc_ID<>0"
343 + " AND (C_ValidCombination_ID IS NULL OR C_ValidCombination_ID=0) AND I_IsImported<>'Y'").append (clientCheck);
344 no = DB.executeUpdate (sql.toString ());
345 log.debug ("doIt - Set Org from Doc Org=" + no);
346 sql = new StringBuffer ("UPDATE I_GLJournal o "
348 + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Org, '"
349 + "WHERE (AD_Org_ID IS NULL OR AD_Org_ID=0"
350 + " OR EXISTS (SELECT * FROM AD_Org oo WHERE o.AD_Org_ID=oo.AD_Org_ID AND (oo.IsSummary='Y' OR oo.IsActive='N')))"
351 + " AND (C_ValidCombination_ID IS NULL OR C_ValidCombination_ID=0) AND I_IsImported<>'Y'").append (clientCheck);
352 no = DB.executeUpdate (sql.toString ());
353 if (no != 0)
354 log.warn ("doIt - Invalid Org=" + no);
355
356 sql = new StringBuffer ("UPDATE I_GLJournal i "
358 + "SET Account_ID=(SELECT ev.C_ElementValue_ID FROM C_ElementValue ev"
359 + " INNER JOIN C_Element e ON (e.C_Element_ID=ev.C_Element_ID)"
360 + " INNER JOIN C_AcctSchema_Element ase ON (e.C_Element_ID=ase.C_Element_ID AND ase.ElementType='AC')"
361 + " WHERE ev.Value=i.AccountValue AND ev.IsSummary='N'"
362 + " AND i.C_AcctSchema_ID=ase.C_AcctSchema_ID AND i.AD_Client_ID=ev.AD_Client_ID AND ROWNUM=1) "
363 + "WHERE Account_ID IS NULL AND AccountValue IS NOT NULL"
364 + " AND (C_ValidCombination_ID IS NULL OR C_ValidCombination_ID=0) AND I_IsImported<>'Y'").append (clientCheck);
365 no = DB.executeUpdate (sql.toString ());
366 log.debug ("doIt - Set Account from Value=" + no);
367 sql = new StringBuffer ("UPDATE I_GLJournal i "
368 + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Account, '"
369 + "WHERE (Account_ID IS NULL OR Account_ID=0)"
370 + " AND (C_ValidCombination_ID IS NULL OR C_ValidCombination_ID=0) AND I_IsImported<>'Y'").append (clientCheck);
371 no = DB.executeUpdate (sql.toString ());
372 if (no != 0)
373 log.warn ("doIt - Invalid Account=" + no);
374
375 sql = new StringBuffer ("UPDATE I_GLJournal i "
377 + "SET C_BPartner_ID=(SELECT bp.C_BPartner_ID FROM C_BPartner bp"
378 + " WHERE bp.Value=i.BPartnerValue AND bp.IsSummary='N' AND i.AD_Client_ID=bp.AD_Client_ID) "
379 + "WHERE C_BPartner_ID IS NULL AND BPartnerValue IS NOT NULL"
380 + " AND (C_ValidCombination_ID IS NULL OR C_ValidCombination_ID=0) AND I_IsImported<>'Y'").append (clientCheck);
381 no = DB.executeUpdate (sql.toString ());
382 log.debug ("doIt - Set BPartner from Value=" + no);
383 sql = new StringBuffer ("UPDATE I_GLJournal i "
384 + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid BPartner, '"
385 + "WHERE C_BPartner_ID IS NULL AND BPartnerValue IS NOT NULL"
386 + " AND (C_ValidCombination_ID IS NULL OR C_ValidCombination_ID=0) AND I_IsImported<>'Y'").append (clientCheck);
387 no = DB.executeUpdate (sql.toString ());
388 if (no != 0)
389 log.warn ("doIt - Invalid BPartner=" + no);
390
391 sql = new StringBuffer ("UPDATE I_GLJournal i "
393 + "SET M_Product_ID=(SELECT p.M_Product_ID FROM M_Product p"
394 + " WHERE (p.Value=i.ProductValue OR p.UPC=i.UPC OR p.SKU=i.SKU)"
395 + " AND p.IsSummary='N' AND i.AD_Client_ID=p.AD_Client_ID AND ROWNUM=1) "
396 + "WHERE M_Product_ID IS NULL AND (ProductValue IS NOT NULL OR UPC IS NOT NULL OR SKU IS NOT NULL)"
397 + " AND (C_ValidCombination_ID IS NULL OR C_ValidCombination_ID=0) AND I_IsImported<>'Y'").append (clientCheck);
398 no = DB.executeUpdate (sql.toString ());
399 log.debug ("doIt - Set Product from Value=" + no);
400 sql = new StringBuffer ("UPDATE I_GLJournal i "
401 + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Product, '"
402 + "WHERE M_Product_ID IS NULL AND (ProductValue IS NOT NULL OR UPC IS NOT NULL OR SKU IS NOT NULL)"
403 + " AND (C_ValidCombination_ID IS NULL OR C_ValidCombination_ID=0) AND I_IsImported<>'Y'").append (clientCheck);
404 no = DB.executeUpdate (sql.toString ());
405 if (no != 0)
406 log.warn ("doIt - Invalid Product=" + no);
407
408 sql = new StringBuffer ("UPDATE I_GLJournal i "
410 + "SET C_Project_ID=(SELECT p.C_Project_ID FROM C_Project p"
411 + " WHERE p.Value=i.ProjectValue AND p.IsSummary='N' AND i.AD_Client_ID=p.AD_Client_ID) "
412 + "WHERE C_Project_ID IS NULL AND ProjectValue IS NOT NULL"
413 + " AND (C_ValidCombination_ID IS NULL OR C_ValidCombination_ID=0) AND I_IsImported<>'Y'").append (clientCheck);
414 no = DB.executeUpdate (sql.toString ());
415 log.debug ("doIt - Set Project from Value=" + no);
416 sql = new StringBuffer ("UPDATE I_GLJournal i "
417 + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Project, '"
418 + "WHERE C_Project_ID IS NULL AND ProjectValue IS NOT NULL"
419 + " AND (C_ValidCombination_ID IS NULL OR C_ValidCombination_ID=0) AND I_IsImported<>'Y'").append (clientCheck);
420 no = DB.executeUpdate (sql.toString ());
421 if (no != 0)
422 log.warn ("doIt - Invalid Project=" + no);
423
424 sql = new StringBuffer ("UPDATE I_GLJournal i "
426 + "SET AD_OrgTrx_ID=(SELECT o.AD_Org_ID FROM AD_Org o"
427 + " WHERE o.Value=i.OrgValue AND o.IsSummary='N' AND i.AD_Client_ID=o.AD_Client_ID) "
428 + "WHERE AD_OrgTrx_ID IS NULL AND OrgTrxValue IS NOT NULL"
429 + " AND (C_ValidCombination_ID IS NULL OR C_ValidCombination_ID=0) AND I_IsImported<>'Y'").append (clientCheck);
430 no = DB.executeUpdate (sql.toString ());
431 log.debug ("doIt - Set OrgTrx from Value=" + no);
432 sql = new StringBuffer ("UPDATE I_GLJournal i "
433 + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid OrgTrx, '"
434 + "WHERE AD_OrgTrx_ID IS NULL AND OrgTrxValue IS NOT NULL"
435 + " AND (C_ValidCombination_ID IS NULL OR C_ValidCombination_ID=0) AND I_IsImported<>'Y'").append (clientCheck);
436 no = DB.executeUpdate (sql.toString ());
437 if (no != 0)
438 log.warn ("doIt - Invalid OrgTrx=" + no);
439
440
441 sql = new StringBuffer ("UPDATE I_GLJournal "
443 + "SET AmtSourceDr = 0 "
444 + "WHERE AmtSourceDr IS NULL"
445 + " AND I_IsImported<>'Y'").append (clientCheck);
446 no = DB.executeUpdate (sql.toString ());
447 log.debug ("doIt - Set 0 Source Dr=" + no);
448 sql = new StringBuffer ("UPDATE I_GLJournal "
449 + "SET AmtSourceCr = 0 "
450 + "WHERE AmtSourceCr IS NULL"
451 + " AND I_IsImported<>'Y'").append (clientCheck);
452 no = DB.executeUpdate (sql.toString ());
453 log.debug ("doIt - Set 0 Source Cr=" + no);
454 sql = new StringBuffer ("UPDATE I_GLJournal i "
455 + "SET I_ErrorMsg=I_ErrorMsg||'WARN=Zero Source Balance, ' "
456 + "WHERE (AmtSourceDr-AmtSourceCr)=0"
457 + " AND I_IsImported<>'Y'").append (clientCheck);
458 no = DB.executeUpdate (sql.toString ());
459 if (no != 0)
460 log.warn ("doIt - Zero Source Balance=" + no);
461
462 sql = new StringBuffer ("UPDATE I_GLJournal "
464 + "SET AmtAcctDr = ROUND(AmtSourceDr * CurrencyRate, 2) " + "WHERE AmtAcctDr IS NULL OR AmtAcctDr=0"
466 + " AND I_IsImported='N'").append (clientCheck);
467 no = DB.executeUpdate (sql.toString ());
468 log.debug ("doIt - Calculate Acct Dr=" + no);
469 sql = new StringBuffer ("UPDATE I_GLJournal "
470 + "SET AmtAcctCr = ROUND(AmtSourceCr * CurrencyRate, 2) "
471 + "WHERE AmtAcctCr IS NULL OR AmtAcctCr=0"
472 + " AND I_IsImported='N'").append (clientCheck);
473 no = DB.executeUpdate (sql.toString ());
474 log.debug ("doIt - Calculate Acct Cr=" + no);
475 sql = new StringBuffer ("UPDATE I_GLJournal i "
476 + "SET I_ErrorMsg=I_ErrorMsg||'WARN=Zero Acct Balance, ' "
477 + "WHERE (AmtSourceDr-AmtSourceCr)<>0 AND (AmtAcctDr-AmtAcctCr)=0"
478 + " AND I_IsImported<>'Y'").append (clientCheck);
479 no = DB.executeUpdate (sql.toString ());
480 if (no != 0)
481 log.warn ("doIt - Zero Acct Balance=" + no);
482 sql = new StringBuffer ("UPDATE I_GLJournal i "
483 + "SET I_ErrorMsg=I_ErrorMsg||'WARN=Check Acct Balance, ' "
484 + "WHERE ABS(AmtAcctDr-AmtAcctCr)>100000000" + " AND I_IsImported<>'Y'").append (clientCheck);
486 no = DB.executeUpdate (sql.toString ());
487 if (no != 0)
488 log.warn ("doIt - Chack Acct Balance=" + no);
489
490
491
492
493 sql = new StringBuffer ("SELECT SUM(AmtSourceDr)-SUM(AmtSourceCr), SUM(AmtAcctDr)-SUM(AmtAcctCr) "
495 + "FROM I_GLJournal "
496 + "WHERE I_IsImported='N'").append (clientCheck);
497 PreparedStatement pstmt = null;
498 try
499 {
500 pstmt = DB.prepareStatement (sql.toString());
501 ResultSet rs = pstmt.executeQuery ();
502 if (rs.next ())
503 {
504 BigDecimal source = rs.getBigDecimal(1);
505 BigDecimal acct = rs.getBigDecimal(2);
506 if (source != null && source.compareTo(Env.ZERO) == 0
507 && acct != null && acct.compareTo(Env.ZERO) == 0)
508 log.info ("doIt - Import Balance = 0");
509 else
510 log.warn("doIt - Balance Source=" + source + ", Acct=" + acct);
511 if (source != null)
512 addLog (0, null, source, "@AmtSourceDr@ - @AmtSourceCr@");
513 if (acct != null)
514 addLog (0, null, acct, "@AmtAcctDr@- @AmtAcctCr@");
515 }
516 rs.close ();
517 pstmt.close ();
518 pstmt = null;
519 }
520 catch (SQLException ex)
521 {
522 log.error ("doIt - get balance", ex);
523 }
524 try
525 {
526 if (pstmt != null)
527 pstmt.close ();
528 }
529 catch (SQLException ex1)
530 {
531 }
532 pstmt = null;
533
534 int errors = DB.getSQLValue("SELECT COUNT(*) FROM I_GLJournal WHERE I_IsImported NOT IN ('Y','N')" + clientCheck);
536
537 if (errors != 0)
538 {
539 if (m_IsValidateOnly || m_IsImportOnlyNoErrors)
540 throw new Exception ("@Errors@=" + errors);
541 }
542 else if (m_IsValidateOnly)
543 return "@Errors@=" + errors;
544
545 log.info("doIt - Validation Errors=" + errors);
546
547
548
549 int noInsert = 0;
550 int noInsertJournal = 0;
551 int noInsertLine = 0;
552
553 MJournalBatch batch = null; String BatchDocumentNo = "";
555 MJournal journal = null;
556 String JournalDocumentNo = "";
557 Timestamp DateAcct = null;
558
559 sql = new StringBuffer ("SELECT * FROM I_GLJournal "
561 + "WHERE I_IsImported='N'").append (clientCheck)
562 .append(" ORDER BY BatchDocumentNo, JournalDocumentNo, C_AcctSchema_ID, PostingType, C_DocType_ID, GL_Category_ID, C_Currency_ID, TRUNC(DateAcct), Line, I_GLJournal_ID");
563 try
564 {
565 pstmt = DB.prepareStatement (sql.toString ());
566 ResultSet rs = pstmt.executeQuery ();
567 while (rs.next())
569 {
570 X_I_GLJournal imp = new X_I_GLJournal (getCtx (), rs);
571
572 String impBatchDocumentNo = imp.getBatchDocumentNo();
574 if (impBatchDocumentNo == null)
575 impBatchDocumentNo = "";
576 if (batch == null || !BatchDocumentNo.equals(impBatchDocumentNo))
577 {
578 BatchDocumentNo = impBatchDocumentNo; batch = new MJournalBatch (getCtx(), 0);
580 batch.setClientOrg(imp.getAD_Client_ID(), imp.getAD_OrgDoc_ID());
581 batch.setDocumentNo (imp.getBatchDocumentNo());
582 String description = imp.getBatchDescription();
583 if (description == null || description.length() == 0)
584 description = "*Import-";
585 else
586 description += " *Import-";
587 description += new Timestamp(System.currentTimeMillis());
588 batch.setDescription(description);
589 if (!batch.save())
590 {
591 log.error("doIt - Batch not saved");
592 break;
593 }
594 noInsert++;
595 journal = null;
596 }
597 String impJournalDocumentNo = imp.getJournalDocumentNo();
599 if (impJournalDocumentNo == null)
600 impJournalDocumentNo = "";
601 Timestamp impDateAcct = TimeUtil.getDay(imp.getDateAcct());
602 if (journal == null || !JournalDocumentNo.equals(impJournalDocumentNo)
603 || journal.getC_AcctSchema_ID() != imp.getC_AcctSchema_ID()
604 || journal.getC_DocType_ID() != imp.getC_DocType_ID()
605 || journal.getGL_Category_ID() != imp.getGL_Category_ID()
606 || !journal.getPostingType().equals(imp.getPostingType())
607 || journal.getC_Currency_ID() != imp.getC_Currency_ID()
608 || !impDateAcct.equals(DateAcct)
609 )
610 {
611 JournalDocumentNo = impJournalDocumentNo; DateAcct = impDateAcct;
613 journal = new MJournal (getCtx(), 0);
614 journal.setGL_JournalBatch_ID(batch.getGL_JournalBatch_ID());
615 journal.setClientOrg(imp.getAD_Client_ID(), imp.getAD_OrgDoc_ID());
616 String description = imp.getBatchDescription();
618 if (description == null || description.length() == 0)
619 description = "(Import)";
620 journal.setDescription (description);
621 journal.setDocumentNo (imp.getJournalDocumentNo());
622 journal.setC_AcctSchema_ID (imp.getC_AcctSchema_ID());
624 journal.setC_DocType_ID (imp.getC_DocType_ID());
625 journal.setGL_Category_ID (imp.getGL_Category_ID());
626 journal.setPostingType (imp.getPostingType());
627 journal.setCurrency (imp.getC_Currency_ID(), imp.getCurrencyRateType(), imp.getCurrencyRate());
629 journal.setC_Period_ID(imp.getC_Period_ID());
631 journal.setDateAcct(imp.getDateAcct()); journal.setDateDoc (imp.getDateAcct());
633 if (!journal.save())
635 {
636 log.error("doIt - Journal not saved");
637 break;
638 }
639 noInsertJournal++;
640 }
641
642 MJournalLine line = new MJournalLine (getCtx(), 0);
644 line.setGL_Journal_ID (journal.getGL_Journal_ID()); line.setClientOrg(imp.getAD_Client_ID(), imp.getAD_OrgDoc_ID());
646 line.setDescription(imp.getDescription());
648 line.setCurrency (imp.getC_Currency_ID(), imp.getCurrencyRateType(), imp.getCurrencyRate());
649 if (imp.getC_ValidCombination_ID() == 0)
651 {
652 int C_ValidCombination_ID = DB.getValidCombination(imp.getAD_Client_ID(), imp.getAD_Org_ID(), imp.getC_AcctSchema_ID(),
653 imp.getAccount_ID(), 0, null, getAD_User_ID(),
654 imp.getM_Product_ID(), imp.getC_BPartner_ID(), imp.getAD_OrgTrx_ID(),
655 imp.getC_LocFrom_ID(), imp.getC_LocTo_ID(), imp.getC_SalesRegion_ID(),
656 imp.getC_Project_ID(), imp.getC_Campaign_ID(), imp.getC_Activity_ID(),
657 imp.getUser1_ID(), imp.getUser2_ID());
658 if (C_ValidCombination_ID > 0)
659 {
660 line.setC_ValidCombination_ID(C_ValidCombination_ID);
661 imp.setC_ValidCombination_ID(C_ValidCombination_ID);
662 }
663 else
664 {
665 imp.setI_ErrorMsg("ERROR getting C_ValidCombination_ID=" + C_ValidCombination_ID);
666 imp.setI_IsImported(false);
667 imp.save();
668 continue;
669 }
670 }
671 else
672 line.setC_ValidCombination_ID (imp.getC_ValidCombination_ID());
673 line.setLine (imp.getLine());
675 line.setAmtSourceCr (imp.getAmtSourceCr());
676 line.setAmtSourceDr (imp.getAmtSourceDr());
677 line.setAmtAcct (imp.getAmtAcctDr(), imp.getAmtAcctCr());
678 line.setDateAcct (imp.getDateAcct());
679 line.setC_UOM_ID(imp.getC_UOM_ID());
681 line.setQty(imp.getQty());
682 if (line.save())
684 {
685 imp.setGL_JournalBatch_ID(batch.getGL_JournalBatch_ID());
686 imp.setGL_Journal_ID(journal.getGL_Journal_ID());
687 imp.setGL_JournalLine_ID(line.getGL_JournalLine_ID());
688 imp.setI_IsImported(true);
689 imp.setProcessed(true);
690 if (imp.save())
691 noInsertLine++;
692 }
693 } rs.close();
695 pstmt.close();
696 }
697 catch (Exception e)
698 {
699 log.error("doIt", e);
700 }
701 try
703 {
704 if (pstmt != null)
705 pstmt.close ();
706 }
707 catch (SQLException ex1)
708 {
709 }
710 pstmt = null;
711
712 sql = new StringBuffer ("UPDATE I_GLJournal "
714 + "SET I_IsImported='N', Updated=SysDate "
715 + "WHERE I_IsImported<>'Y'").append(clientCheck);
716 no = DB.executeUpdate(sql.toString());
717 addLog (0, null, new BigDecimal (no), "@Errors@");
718 addLog (0, null, new BigDecimal (noInsert), "@GL_JournalBatch_ID@: @Inserted@");
720 addLog (0, null, new BigDecimal (noInsertJournal), "@GL_Journal_ID@: @Inserted@");
721 addLog (0, null, new BigDecimal (noInsertLine), "@GL_JournalLine_ID@: @Inserted@");
722 return "";
723 }
725 } |
Popular Tags |
Java API By Example, From Geeks To Geeks.