VCreateFromInvoice


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.grid;
15  
16  import javax.swing.table.*;
17  import java.util.*;
18  import java.sql.*;
19  import java.math.*;
20  import java.text.*;
21  import java.beans.*;
22  import java.awt.event.*;
23  
24  import org.compiere.apps.*;
25  import org.compiere.util.*;
26  import org.compiere.model.*;
27  
28  /**
29   *  Create Transactions for Invoices
30   *
31   *  @author Jorg Janke
32   *  @version  $Id: VCreateFromInvoice.java,v 1.18 2003/08/15 17:24:12 jjanke Exp $
33   */
34  public class VCreateFromInvoice extends VCreateFrom implements VetoableChangeListener
35  {
36      /**
37       *  Protected Constructor
38       *  @param mTab MTab
39       */
40      VCreateFromInvoice(MTab mTab)
41      {
42          super (mTab);
43          Log.trace(Log.l1_User, "VCreateFromInvoice");
44      }   //  VCreateFromInvoice
45  
46      private boolean     m_actionActive = false;
47  
48      /**
49       *  Dynamic Init
50       *  @throws Exception if Lookups cannot be initialized
51       *  @return true if initialized
52       */
53      protected boolean dynInit() throws Exception  
54      {
55          Log.trace(Log.l3_Util, "VCreateFromInvoice.dynInit");
56          setTitle(Msg.getElement(Env.getCtx(), "C_Invoice_ID", false) + " .. " + Msg.translate(Env.getCtx(), "CreateFrom"));
57  
58          parameterBankPanel.setVisible(false);
59          invoiceLabel.setVisible(false);
60          invoiceField.setVisible(false);
61          locatorLabel.setVisible(false);
62          locatorField.setVisible(false);
63  
64          initBPartner(true);
65          bPartnerField.addVetoableChangeListener(this);
66          return true;
67      }   //  dynInit
68  
69      /**
70       *  Init Details - load receipts not invoiced
71       *  @param C_BPartner_ID BPartner
72       */
73      protected void initBPDetails(int C_BPartner_ID)
74      {
75          Log.trace(Log.l3_Util, "VCreateFromInvoice.iniBPDateails");
76  
77          //  load Shipments (Receipts) - Completed, Closed
78          shipmentField.removeActionListener(this);
79          shipmentField.removeAllItems();
80          //  None
81          KeyNamePair pp = new KeyNamePair(0,"");
82          shipmentField.addItem(pp);
83          //  Display
84          StringBuffer   display = new StringBuffer  ("s.DocumentNo||' - '||")
85              .append(DB.TO_CHAR("s.MovementDate", DisplayType.Date, Env.getAD_Language(Env.getCtx())));
86          //
87          StringBuffer   sql = new StringBuffer  ("SELECT s.M_InOut_ID,").append(display)
88              .append(" FROM M_InOut s "
89              + "WHERE s.C_BPartner_ID=? AND s.IsSOTrx='N' AND s.DocStatus IN ('CL','CO')"
90              + " AND s.M_InOut_ID IN "
91                  + "(SELECT sl.M_InOut_ID FROM M_InOutLine sl"
92                  + " LEFT OUTER JOIN M_MatchInv mi ON (sl.M_InOutLine_ID=mi.M_InOutLine_ID) "
93                  + "GROUP BY sl.M_InOut_ID,mi.M_InOutLine_ID,sl.MovementQty "
94                  + "HAVING (sl.MovementQty<>SUM(mi.Qty) AND mi.M_InOutLine_ID IS NOT NULL)"
95                  + " OR mi.M_InOutLine_ID IS NULL) "
96              + "ORDER BY s.MovementDate");
97          try
98          {
99              PreparedStatement pstmt = DB.prepareStatement(sql.toString());
100             pstmt.setInt(1, C_BPartner_ID);
101             ResultSet rs = pstmt.executeQuery();
102             while (rs.next())
103             {
104                 pp = new KeyNamePair(rs.getInt(1), rs.getString(2));
105                 shipmentField.addItem(pp);
106             }
107             rs.close();
108             pstmt.close();
109         }
110         catch (SQLException e)
111         {
112             Log.error ("VCreateFromInvoice.initBPDetails", e);
113         }
114         shipmentField.setSelectedIndex(0);
115         shipmentField.addActionListener(this);
116     }   //  initDetails
117 
118     /**
119      *  Action Listener
120      *  @param e event
121      */
122     public void actionPerformed(ActionEvent e)
123     {
124         super.actionPerformed(e);
125         if (m_actionActive)
126             return;
127         m_actionActive = true;
128         Log.trace(Log.l3_Util, "VCreateFromInvoice.actionPerformed - " + e.getActionCommand());
129         //  Order
130         if (e.getSource().equals(orderField))
131         {
132             KeyNamePair pp = (KeyNamePair)orderField.getSelectedItem();
133             int C_Order_ID = 0;
134             if (pp != null)
135                 C_Order_ID = pp.getKey();
136             //  set Invoice and Shipment to Null
137             invoiceField.setSelectedIndex(-1);
138             shipmentField.setSelectedIndex(-1);
139             loadOrder(C_Order_ID, true);
140         }
141         //  Shipment
142         else if (e.getSource().equals(shipmentField))
143         {
144             KeyNamePair pp = (KeyNamePair)shipmentField.getSelectedItem();
145             int M_InOut_ID = 0;
146             if (pp != null)
147                 M_InOut_ID = pp.getKey();
148             //  set Order and Invoice to Null
149             orderField.setSelectedIndex(-1);
150             invoiceField.setSelectedIndex(-1);
151             loadShipment(M_InOut_ID);
152         }
153         m_actionActive = false;
154     }   //  actionPerformed
155 
156     /**
157      *  Change Listener
158      *  @param e event
159      */
160     public void vetoableChange (PropertyChangeEvent e)
161     {
162         Log.trace(Log.l3_Util, "VCreateFromInvoice.vetoableChange " + e.getPropertyName() + "=" + e.getNewValue());
163 
164         //  BPartner - load Order/Invoice/Shipment
165         if (e.getPropertyName() == "C_BPartner_ID")
166         {
167             int C_BPartner_ID = ((Integer  )e.getNewValue()).intValue();
168             initBPartnerOIS (C_BPartner_ID, true);
169         }
170         tableChanged(null);
171     }   //  vetoableChange
172 
173 
174     /**
175      *  Load Data - Shipment not invoiced
176      *  @param M_InOut_ID InOut
177      */
178     private void loadShipment (int M_InOut_ID)
179     {
180         Log.trace(Log.l3_Util, "VCreateFromInvoice.loadShipment - " + M_InOut_ID);
181         Vector data = new Vector();
182         StringBuffer   sql = new StringBuffer  ("SELECT l.MovementQty-SUM(NVL(mi.Qty,0)),"
183             + " l.C_UOM_ID,uom.UOMSymbol,"                              //  2..3
184             + " l.M_Product_ID,p.Name, l.M_InOutLine_ID,l.Line,"        //  4..7
185             + " l.C_OrderLine_ID ");                                    //  8
186         if (Env.isBaseLanguage(Env.getCtx(), "C_UOM"))
187         {
188             sql.append("FROM C_UOM uom, M_InOutLine l, M_Product p, M_MatchInv mi ");
189             sql.append("WHERE l.C_UOM_ID=uom.C_UOM_ID");
190         }
191         else
192         {
193             sql.append("FROM C_UOM_Trl uom, M_InOutLine l, M_Product p, M_MatchInv mi ");
194             sql.append("WHERE l.C_UOM_ID=uom.C_UOM_ID AND uom.AD_Language='").append(Env.getAD_Language(Env.getCtx())).append("'");
195         }
196         sql.append(" AND l.M_Product_ID=p.M_Product_ID")
197             .append(" AND l.M_InOutLine_ID=mi.M_InOutLine_ID(+)")
198             .append("AND l.M_InOut_ID=? ")                  //  #1
199             .append("GROUP BY l.MovementQty,l.C_UOM_ID,uom.UOMSymbol,l.M_Product_ID,p.Name, l.M_InOutLine_ID,l.Line,l.C_OrderLine_ID ")
200             .append("ORDER BY l.Line");
201 
202         try
203         {
204             PreparedStatement pstmt = DB.prepareStatement(sql.toString());
205             pstmt.setInt(1, M_InOut_ID);
206             ResultSet rs = pstmt.executeQuery();
207             while (rs.next())
208             {
209                 Vector line = new Vector(7);
210                 line.add(new Boolean  (false));           //  0-Selection
211                 line.add(new Double  (rs.getDouble(1)));  //  1-Qty
212                 KeyNamePair pp = new KeyNamePair(rs.getInt(2), rs.getString(3).trim());
213                 line.add(pp);                           //  2-UOM
214                 pp = new KeyNamePair(rs.getInt(4), rs.getString(5));
215                 line.add(pp);                           //  3-Product
216                 int i = rs.getInt(8);
217                 if (rs.wasNull())
218                     line.add(null);                     //  4-Order
219                 else
220                     line.add(new KeyNamePair(i," "));
221                 pp = new KeyNamePair(rs.getInt(6), rs.getString(7));
222                 line.add(pp);                           //  5-Ship
223                 line.add(null);                         //  6-Invoice
224                 data.add(line);
225             }
226             rs.close();
227             pstmt.close();
228         }
229         catch (SQLException e)
230         {
231             Log.error ("VCreateFromInvoice.loadShipment", e);
232         }
233         loadTableOIS (data);
234     }   //  loadShipment
235 
236     /**
237      *  List number of rows selected
238      */
239     protected void info()
240     {
241         TableModel model = dataTable.getModel();
242         int rows = model.getRowCount();
243         int count = 0;
244         for (int i = 0; i < rows; i++)
245         {
246             if (((Boolean  )model.getValueAt(i, 0)).booleanValue())
247                 count++;
248         }
249         statusBar.setStatusLine(String.valueOf(count));
250     }   //  infoInvoice
251 
252     /**
253      *  Save - Create Invoice Lines
254      *  @return true if saved
255      */
256     protected boolean save()
257     {
258         Log.trace(Log.l3_Util, "VCreateFromInvoice.save");
259         //  fixed values
260         int AD_Client_ID = ((Integer  )p_mTab.getValue("AD_Client_ID")).intValue();
261         int AD_Org_ID = ((Integer  )p_mTab.getValue("AD_Org_ID")).intValue();
262         int CreatedBy = Env.getContextAsInt(Env.getCtx(), "#AD_User_ID");
263         int C_Invoice_ID = ((Integer  )p_mTab.getValue("C_Invoice_ID")).intValue();
264         Log.trace(Log.l4_Data, "Client=" + AD_Client_ID + ", Org=" + AD_Org_ID
265             + ", User=" + CreatedBy + ", Invoice=" + C_Invoice_ID);
266 
267         //  required to derive values
268         Timestamp billDate = (Timestamp)p_mTab.getValue("DateInvoiced");
269         Timestamp shipDate = billDate;                                  //  wrong, should get it from shipment (if shipment)
270         int M_Warehouse_ID = Env.getContextAsInt(Env.getCtx(), "#M_Warehouse_ID");   //  wromg, should get it from shipment or order
271         int bill_Location_ID = ((Integer  )p_mTab.getValue("C_BPartner_Location_ID")).intValue();
272         int ship_Location_ID = bill_Location_ID;                        //  wrong
273 
274         int M_PriceList_ID = ((Integer  )p_mTab.getValue("M_PriceList_ID")).intValue();;
275         int M_PriceListVersion_ID = 0;
276         String   sql0 = "SELECT plv.M_PriceList_Version_ID "
277             + "FROM M_PriceList pl,M_PriceList_Version plv "
278             + "WHERE pl.M_PriceList_ID=plv.M_PriceList_ID AND plv.IsActive='Y'"
279             + " AND plv.ValidFrom <= ? AND pl.M_PriceList_ID=? "
280             + "ORDER BY plv.ValidFrom DESC";
281         try
282         {
283             PreparedStatement pstmt = DB.prepareStatement(sql0);
284             pstmt.setTimestamp(1, billDate);
285             pstmt.setInt(2, M_PriceList_ID);
286             ResultSet rs = pstmt.executeQuery();
287             if (rs.next())
288                 M_PriceListVersion_ID = rs.getInt(1);
289             rs.close();
290             pstmt.close();
291         }
292         catch (SQLException e)
293         {
294             Log.error ("VCreateFromInvoice.save - PL_Version - " + sql0, e);
295         }
296         //  No PriceList
297         if (M_PriceListVersion_ID == 0)
298         {
299             ADialog.error(p_WindowNo, this, "PriceListVersionNotFound", String.valueOf(M_PriceList_ID) + " " + billDate.toString());
300             Log.trace(Log.l6_Database, sql0);
301             return false;
302         }
303 
304 
305         //  Lines
306         TableModel model = dataTable.getModel();
307         int rows = model.getRowCount();
308         for (int i = 0; i < rows; i++)
309         {
310             if (((Boolean  )model.getValueAt(i, 0)).booleanValue())
311             {
312                 //  variable values
313                 int C_InvoiceLine_ID = DB.getKeyNextNo(Env.getCtx(), p_WindowNo, "C_InvoiceLine");
314                 Double   d = (Double  )model.getValueAt(i, 1);              //  1-Qty
315                 BigDecimal QtyInvoiced = new BigDecimal(d.doubleValue());
316                 KeyNamePair pp = (KeyNamePair)model.getValueAt(i, 2);   //  2-UOM
317                 int C_UOM_ID = pp.getKey();
318                 //
319                 pp = (KeyNamePair)model.getValueAt(i, 3);               //  3-Product
320                 int M_Product_ID = 0;
321                 if (pp != null)
322                     M_Product_ID = pp.getKey();
323                 int C_Charge_ID = 0;
324                 //
325                 int C_OrderLine_ID = 0;
326                 pp = (KeyNamePair)model.getValueAt(i, 4);               //  4-OrderLine
327                 if (pp != null)
328                     C_OrderLine_ID = pp.getKey();
329                 int M_InOutLine_ID = 0;
330                 pp = (KeyNamePair)model.getValueAt(i, 5);               //  5-Shipment
331                 if (pp != null)
332                     M_InOutLine_ID = pp.getKey();
333                 //
334                 Log.trace(Log.l5_DData, "Line=" + C_InvoiceLine_ID + ", Qty=" + QtyInvoiced
335                     + ", Product=" + M_Product_ID + ", OrderLine=" + C_OrderLine_ID + ", ShipmentLine=" + M_InOutLine_ID);
336 
337                 //  Info
338                 String   Description = null;
339 
340                 /**
341                  *  Get Shipment Info: Description & C_OrderLine_ID
342                  */
343                 if (M_InOutLine_ID != 0)
344                 {
345                     sql0 = "SELECT Description, C_OrderLine_ID FROM M_InOutLine WHERE M_InOutLine_ID=?";
346                     try
347                     {
348                         PreparedStatement pstmt = DB.prepareStatement(sql0);
349                         pstmt.setInt(1, M_InOutLine_ID);
350                         ResultSet rs = pstmt.executeQuery();
351                         if (rs.next())
352                         {
353                             Description = rs.getString(1);
354                             C_OrderLine_ID = rs.getInt(2);
355                         }
356                         rs.close();
357                         pstmt.close();
358                     }
359                     catch (SQLException e)
360                     {
361                         Log.error ("VCreateFromInvoice.save-InOutLine", e);
362                     }
363                 }
364 
365                 /**
366                  *  Get Prices
367                  */
368                 BigDecimal PriceList = new BigDecimal(0.0);
369                 BigDecimal PriceActual = new BigDecimal(0.0);
370                 BigDecimal PriceLimit = new BigDecimal(0.0);
371                 //
372                 if (C_OrderLine_ID != 0)
373                 {
374                     //  Get Price from Order
375                     sql0 = "SELECT PriceActual,PriceList,PriceLimit,Description,C_Charge_ID FROM C_OrderLine WHERE C_OrderLine_ID=?";
376                     try
377                     {
378                         PreparedStatement pstmt = DB.prepareStatement(sql0);
379                         pstmt.setInt(1, C_OrderLine_ID);
380                         ResultSet rs = pstmt.executeQuery();
381                         if (rs.next())
382                         {
383                             PriceActual = rs.getBigDecimal(1);
384                             PriceList = rs.getBigDecimal(2);
385                             PriceLimit = rs.getBigDecimal(3);
386                             if (Description == null)    //  might be set in Shipment
387                                 Description = rs.getString(4);
388                             C_Charge_ID = rs.getInt(5);
389                         }
390                         rs.close();
391                         pstmt.close();
392                     }
393                     catch (SQLException e)
394                     {
395                         Log.error ("VCreateFromInvoice.save-OrderLine", e);
396                     }
397                 }
398                 else
399                 {
400                     //  Get Prices from PriceList
401                     sql0 = "SELECT BOM_PriceStd(pp.M_Product_ID,pp.M_PriceList_Version_ID) AS PriceStd,"
402                         + "BOM_PriceList(pp.M_Product_ID,pp.M_PriceList_Version_ID) AS PriceList,"
403                         + "BOM_PriceLimit(pp.M_Product_ID,pp.M_PriceList_Version_ID) AS PriceLimit "
404                         + "FROM M_ProductPrice pp "
405                         + "WHERE pp.M_Product_ID=?"             //  1
406                         + " AND pp.M_PriceList_Version_ID=?";   //  2
407                     try
408                     {
409                         PreparedStatement pstmt = DB.prepareStatement(sql0);
410                         pstmt.setInt(1, M_Product_ID);
411                         pstmt.setInt(2, M_PriceListVersion_ID);
412                         ResultSet rs = pstmt.executeQuery();
413                         if (rs.next())
414                         {
415                             PriceActual = rs.getBigDecimal(1);
416                             PriceList = rs.getBigDecimal(2);
417                             PriceLimit = rs.getBigDecimal(3);
418                         }
419                         rs.close();
420                         pstmt.close();
421                     }
422                     catch (SQLException e)
423                     {
424                         Log.error ("VCreateFromInvoice.save-Price - " + sql0, e);
425                     }
426                 }
427 
428                 //  Get Tax
429                 boolean IsSOTrx = false;
430                 int C_Tax_ID = Tax.get(Env.getCtx(), M_Product_ID, C_Charge_ID, billDate, shipDate,
431                     AD_Org_ID, M_Warehouse_ID, bill_Location_ID, ship_Location_ID, IsSOTrx);
432                 //
433                 BigDecimal LineNetAmt = PriceActual.multiply(QtyInvoiced).setScale(2, BigDecimal.ROUND_HALF_UP);
434                 Log.trace(Log.l5_DData, ".. PriceList=" + PriceList + ", Actual=" + PriceActual
435                     + ", Limit=" + PriceLimit + ", Net=" + LineNetAmt + ", Tax=" + C_Tax_ID);
436 
437                 //
438                 StringBuffer   sql = new StringBuffer  ("INSERT INTO C_InvoiceLine");
439                 sql.append("(C_InvoiceLine_ID,C_Invoice_ID,")
440                     .append("AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy,")
441                     .append("C_OrderLine_ID,M_InOutLine_ID,")
442                     .append("Line,Description,")
443                     .append("M_Product_ID,C_UOM_ID,QtyInvoiced,")
444                     .append("PriceList,PriceActual,PriceLimit,")
445                     .append("LineNetAmt,")
446                     .append("C_Charge_ID,ChargeAmt,")
447                     .append("C_Tax_ID)")
448                     .append(" VALUES (");
449                 //
450                 sql.append(C_InvoiceLine_ID).append(",").append(C_Invoice_ID).append(",")
451                     .append(AD_Client_ID).append(",").append(AD_Org_ID).append(",'Y',")
452                     .append("SysDate,").append(CreatedBy).append(",SysDate,").append(CreatedBy).append(",");
453                 //  C_OrderLine_ID,M_InOutLine_ID,
454                 if (C_OrderLine_ID == 0)
455                     sql.append("NULL,");
456                 else
457                     sql.append(C_OrderLine_ID).append(",");
458                 if (M_InOutLine_ID == 0)
459                     sql.append("NULL,");
460                 else
461                     sql.append(M_InOutLine_ID).append(",");
462                 //  Line,
463                 sql.append("(SELECT (NVL(Max(Line),0))+10 FROM C_InvoiceLine WHERE C_Invoice_ID=").append(C_Invoice_ID).append("),");
464                 //  Description
465                 if (Description == null || Description.length() == 0)
466                     sql.append("NULL,");
467                 else
468                     sql.append(DB.TO_STRING(Description)).append(",");
469                 //  M_Product_ID,C_UOM_ID,QtyInvoiced,
470                 if (M_Product_ID == 0)
471                     sql.append("NULL");
472                 else
473                     sql.append(M_Product_ID);
474                 sql.append(",").append(C_UOM_ID).append(",").append(QtyInvoiced).append(",");
475                 //  PriceList,PriceActual,PriceLimit,
476                 sql.append(PriceList).append(",").append(PriceActual).append(",").append(PriceLimit).append(",");
477                 sql.append(LineNetAmt).append(",");
478                 //  C_Charge_ID,ChargeAmt,
479                 if (C_Charge_ID == 0)
480                     sql.append("NULL,0,");
481                 else
482                     sql.append(C_Charge_ID).append(",0,");
483                 //  C_Tax_ID
484                 sql.append(C_Tax_ID).append(")");
485                 //
486                 int no = DB.executeUpdate(sql.toString());
487                 if (no != 1)
488                     Log.error("VCreateFromInvoice.save - Line created NOT #" + no);
489             }   //   if selected
490         }   //  for all rows
491 
492         /**
493          *  Update Header
494          *  - if linked to another order - remove link
495          *  - if no link set it
496          */
497         if (p_C_Order_ID != 0)
498         {
499             StringBuffer   sql = new StringBuffer   ("UPDATE C_Invoice SET C_Order_ID=NULL"
500                 + " WHERE C_Invoice_ID=").append(C_Invoice_ID)
501                 .append(" AND C_Order_ID IS NOT NULL AND C_Order_ID <> ").append(p_C_Order_ID);
502             int no = DB.executeUpdate(sql.toString());
503             if (no == 0)
504             {
505                 sql = new StringBuffer   ("UPDATE C_Invoice"
506                     + " SET (C_Order_ID, M_PriceList_ID, PaymentRule, C_PaymentTerm_ID)="
507                     + " (SELECT C_Order_ID, M_PriceList_ID, PaymentRule, C_PaymentTerm_ID"
508                     + " FROM C_Order WHERE C_Order_ID=").append(p_C_Order_ID).append(") "
509                     + "WHERE C_Invoice_ID=").append(C_Invoice_ID);
510                 no = DB.executeUpdate(sql.toString());
511             }
512         }
513         return true;
514     }   //  saveInvoice
515 
516 
517 }   //  VCreateFromInvoice
518
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags