gt: 				name += "%";
context: /******************************************************************************
   * Product: Adempiere ERP & CRM Smart Business Solution                       *
   * Copyright (C) 1999-2006 ComPiere, Inc. All Rights Reserved.                *
   * This program is free software; you can redistribute it and/or modify it    *
   * under the terms version 2 of the GNU General Public License as published   *
   * by the Free Software Foundation. This program is distributed in the hope   *
   * that it will be useful, but WITHOUT ANY WARRANTY; without even the implied *
   * warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.           *
   * See the GNU General Public License for more details.                       *
   * You should have received a copy of the GNU General Public License along    *
   * with this program; if not, write to the Free Software Foundation, Inc.,    *
   * 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA.                     *
   * For the text or an alternative of this public license, you may reach us    *
   * ComPiere, Inc., 2620 Augustine Dr. #245, Santa Clara, CA 95054, USA        *
   * or via info@compiere.org or http://www.compiere.org/license.html           *
   *****************************************************************************/
  package org.compiere.apps.search;
  
  import java.awt.Dimension;
  import java.awt.Frame;
  import java.awt.Insets;
  import java.awt.event.ActionEvent;
  import java.awt.event.ActionListener;
  import java.awt.event.KeyAdapter;
  import java.awt.event.KeyEvent;
  import java.awt.event.MouseAdapter;
  import java.awt.event.MouseEvent;
  import java.math.BigDecimal;
  import java.sql.PreparedStatement;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.sql.Timestamp;
  import java.util.ArrayList;
  import java.util.Vector;
  import java.util.logging.Level;
  
  import javax.swing.JScrollPane;
  import javax.swing.event.ChangeEvent;
  import javax.swing.event.ChangeListener;
  import javax.swing.plaf.ColorUIResource;
  import javax.swing.table.DefaultTableModel;
  
  import org.adempiere.plaf.AdempierePLAF;
  import org.adempiere.plaf.AdempiereTaskPaneUI;
  import org.compiere.apps.AEnv;
  import org.compiere.apps.ALayout;
  import org.compiere.apps.ALayoutConstraint;
  import org.compiere.apps.ConfirmPanel;
  import org.compiere.grid.ed.VComboBox;
  import org.compiere.minigrid.ColumnInfo;
  import org.compiere.minigrid.IDColumn;
  import org.compiere.minigrid.MiniTable;
  import org.compiere.model.MDocType;
  import org.compiere.model.MQuery;
  import org.compiere.model.MRole;
  import org.compiere.swing.CButton;
  import org.compiere.swing.CLabel;
  import org.compiere.swing.CPanel;
  import org.compiere.swing.CTabbedPane;
  import org.compiere.swing.CTextArea;
  import org.compiere.swing.CTextField;
  import org.compiere.util.CLogMgt;
  import org.compiere.util.DB;
  import org.compiere.util.Env;
  import org.compiere.util.KeyNamePair;
  import org.compiere.util.Msg;
  import org.compiere.util.Util;
  import org.jdesktop.swingx.JXTaskPane;
  
  
  /**
   *	Search Product and return selection
   *
   *  @author     Jorg Janke
   *  @version    $Id: InfoProduct.java,v 1.4 2006/07/30 00:51:27 jjanke Exp $
   * 
   * @author Bogdan Ioan, SC ARHIPAC SERVICE SRL
   * 				<li>FR [ 2012362 ] Info Product: Add Product Category 
   */
  public class InfoProduct extends Info implements ActionListener, ChangeListener
  {
  	/**
  	 * 
  	 */
  	private static final long serialVersionUID = 2076229793041196087L;
  
  	/**
  	 *	Standard Constructor
  	 * 	@param frame frame
  	 * 	@param modal modal
  	 * 	@param WindowNo window no
  	 * 	@param M_Warehouse_ID warehouse
  	 * 	@param M_PriceList_ID price list
  	 * 	@param value    Query Value or Name if enclosed in @
  	 * 	@param multiSelection multiple selections
  	 * 	@param whereClause where clause
  	 */
  	public InfoProduct(Frame frame, boolean modal, int WindowNo,
  		int M_Warehouse_ID, int M_PriceList_ID, String value,
  		boolean multiSelection, String whereClause)
  	{
  		super (frame, modal, WindowNo, "p", "M_Product_ID", multiSelection, whereClause);
  		log.info(value + ", Wh=" + M_Warehouse_ID + ", PL=" + M_PriceList_ID + ", WHERE=" + whereClause);
  		setTitle(Msg.getMsg(Env.getCtx(), "InfoProduct"));
  		//
  		statInit();
  		initInfo (value, M_Warehouse_ID, M_PriceList_ID);
  		m_C_BPartner_ID = Env.getContextAsInt(Env.getCtx(), WindowNo, "C_BPartner_ID");
  
  		//
  		int no = p_table.getRowCount();
  		setStatusLine(Integer.toString(no) + " " + Msg.getMsg(Env.getCtx(), "SearchRows_EnterQuery"), false);
  		setStatusDB(Integer.toString(no));
  		//	AutoQuery
  		if (value != null && value.length() > 0)
  			executeQuery();
  		p_loadedOK = true;
  		//	Focus
  		fieldValue.requestFocus();
  		
  		//Begin - fer_luck @ centuryon
  		mWindowNo = WindowNo;
  		//End - fer_luck @ centuryon
  		AEnv.positionCenterWindow(frame, this);
  	}	//	InfoProduct
  
  	/** SQL From				*/
  	private static final String s_productFrom =
  		"M_Product p"
  		+ " LEFT OUTER JOIN M_ProductPrice pr ON (p.M_Product_ID=pr.M_Product_ID AND pr.IsActive='Y')"
  		+ " LEFT OUTER JOIN M_AttributeSet pa ON (p.M_AttributeSet_ID=pa.M_AttributeSet_ID)"
  		+ " LEFT OUTER JOIN M_Product_PO ppo ON (p.M_Product_ID=ppo.M_Product_ID and ppo.IsCurrentVendor='Y' and ppo.IsActive='Y')"
  		+ " LEFT OUTER JOIN C_BPartner bp ON (ppo.C_BPartner_ID=bp.C_BPartner_ID)";
  
  	/**  Array of Column Info    */
  	private static Info_Column[] s_productLayout = null;
  	private static int INDEX_NAME = 0;
  	private static int INDEX_PATTRIBUTE = 0;
  
  	//
  	private CLabel labelValue = new CLabel();
  	private CTextField fieldValue = new CTextField(10);
  	private CLabel labelName = new CLabel();
  	private CTextField fieldName = new CTextField(10);
  	private CLabel labelUPC = new CLabel();
  	private CTextField fieldUPC = new CTextField(10);
  	private CLabel labelSKU = new CLabel();
  	private CTextField fieldSKU = new CTextField(10);
  	private CLabel labelPriceList = new CLabel();
  	private VComboBox pickPriceList = new VComboBox();
  	private CLabel labelWarehouse = new CLabel();
  	private VComboBox pickWarehouse = new VComboBox();
  	private CLabel labelVendor = new CLabel();
  	private CTextField fieldVendor = new CTextField(10);
  	private CLabel labelProductCategory = new CLabel();
  	private VComboBox pickProductCategory = new VComboBox();
  	private CLabel labelAS = new CLabel(); // @Trifon
  	private VComboBox pickAS = new VComboBox(); // @Trifon
  	
  	//Begin - fer_luck @ centuryon
  	private CTextArea fieldDescription = new CTextArea();
  	JXTaskPane warehouseStockPanel = new JXTaskPane();
      CPanel tablePanel = new CPanel();
      MiniTable warehouseTbl = new MiniTable();
      String m_sqlWarehouse;
      MiniTable substituteTbl = new MiniTable();
      String m_sqlSubstitute;
      MiniTable relatedTbl = new MiniTable();
      String m_sqlRelated;
      //Available to Promise Tab
  	private MiniTable 			m_tableAtp = new MiniTable();
  	private DefaultTableModel 	m_modelAtp = null;
  	private int 				m_M_Product_ID = 0;
      int mWindowNo = 0;
  	//End - fer_luck @ centuryon
  
  	/**	Search Button				*/
  	private CButton		m_InfoPAttributeButton = new CButton(Env.getImageIcon("PAttribute16.gif"));
  	/** Instance Button				*/
  	private CButton		m_PAttributeButton = null;
  	/** ASI							*/
  	private int			m_M_AttributeSetInstance_ID = -1;
  	/** Locator						*/
  	private int			m_M_Locator_ID = 0;
  
  	private String		m_pAttributeWhere = null;
  	private int			m_C_BPartner_ID = 0;
  
  	/**
  	 *	Static Setup - add fields to parameterPanel
  	 */
  	private void statInit()
  	{
  		labelValue.setText(Msg.getMsg(Env.getCtx(), "Value"));
  		fieldValue.setBackground(AdempierePLAF.getInfoBackground());
  		fieldValue.addActionListener(this);
  		
  		labelName.setText(Msg.getMsg(Env.getCtx(), "Name"));
  		fieldName.setBackground(AdempierePLAF.getInfoBackground());
  		fieldName.addActionListener(this);
  
  		labelUPC.setText(Msg.translate(Env.getCtx(), "UPC"));
  		fieldUPC.setBackground(AdempierePLAF.getInfoBackground());
  		fieldUPC.addActionListener(this);
  
  		labelSKU.setText(Msg.translate(Env.getCtx(), "SKU"));
  		fieldSKU.setBackground(AdempierePLAF.getInfoBackground());
  		fieldSKU.addActionListener(this);
  		
  		labelWarehouse.setText(Msg.getMsg(Env.getCtx(), "Warehouse"));
  		pickWarehouse.setBackground(AdempierePLAF.getInfoBackground());
  		
  		labelPriceList.setText(Msg.getMsg(Env.getCtx(), "PriceListVersion"));
  		pickPriceList.setBackground(AdempierePLAF.getInfoBackground());
  
  		labelProductCategory.setText(Msg.translate(Env.getCtx(), "M_Product_Category_ID"));
  		pickProductCategory.setBackground(AdempierePLAF.getInfoBackground());
  		
  		// @Trifon
  		labelAS.setText(Msg.translate(Env.getCtx(), "M_AttributeSet_ID"));
  		pickAS.setBackground(AdempierePLAF.getInfoBackground());
  		
  		m_InfoPAttributeButton.setMargin(new Insets(2,2,2,2));
  		m_InfoPAttributeButton.setToolTipText(Msg.getMsg(Env.getCtx(), "InfoPAttribute"));
  		m_InfoPAttributeButton.addActionListener(this);
  		
  		labelVendor.setText(Msg.translate(Env.getCtx(), "Vendor"));
  		fieldVendor.setBackground(AdempierePLAF.getInfoBackground());
  		fieldVendor.addActionListener(this);
  
  		//	Line 1
  		parameterPanel.setLayout(new ALayout());
  		parameterPanel.add(labelValue, new ALayoutConstraint(0,0));
  		parameterPanel.add(fieldValue, null);
  		parameterPanel.add(labelUPC, null);
  		parameterPanel.add(fieldUPC, null);
  		parameterPanel.add(labelWarehouse, null);
  		parameterPanel.add(pickWarehouse, null);
  		parameterPanel.add(m_InfoPAttributeButton);
  		//	Line 2
  		parameterPanel.add(labelName, new ALayoutConstraint(1,0));
  		parameterPanel.add(fieldName, null);
  		parameterPanel.add(labelSKU, null);
  		parameterPanel.add(fieldSKU, null);
  		parameterPanel.add(labelVendor, null);
  		parameterPanel.add(fieldVendor, null);
  		
  		// Line 3
  		parameterPanel.add(labelPriceList, new ALayoutConstraint(2,0));
  		parameterPanel.add(pickPriceList, null);
  		parameterPanel.add(labelProductCategory, null);
  		parameterPanel.add(pickProductCategory, null);
  		parameterPanel.add(labelAS, null); // @Trifon
  		parameterPanel.add(pickAS, null);  // @Trifon
  		
  		//	Product Attribute Instance
  		m_PAttributeButton = ConfirmPanel.createPAttributeButton(true);
  		confirmPanel.addButton(m_PAttributeButton);
  		m_PAttributeButton.addActionListener(this);
  		m_PAttributeButton.setEnabled(false);
  		
  		//Begin - fer_luck @ centuryon
  		//add taskpane
  		fieldDescription.setBackground(AdempierePLAF.getInfoBackground());
  		fieldDescription.setEditable(false);
  		fieldDescription.setPreferredSize(new Dimension(INFO_WIDTH - 100, 100));
  
          warehouseStockPanel.setTitle(Msg.translate(Env.getCtx(), "WarehouseStock"));
          warehouseStockPanel.setUI(new AdempiereTaskPaneUI());
          warehouseStockPanel.getContentPane().setBackground(new ColorUIResource(251,248,241));
          warehouseStockPanel.getContentPane().setForeground(new ColorUIResource(251,0,0));
          
          ColumnInfo[] s_layoutWarehouse = new ColumnInfo[]{
          		new ColumnInfo(Msg.translate(Env.getCtx(), "Warehouse"), "Warehouse", String.class),
          		new ColumnInfo(Msg.translate(Env.getCtx(), "QtyAvailable"), "sum(QtyAvailable)", Double.class),
          		new ColumnInfo(Msg.translate(Env.getCtx(), "QtyOnHand"), "sum(QtyOnHand)", Double.class),
             		new ColumnInfo(Msg.translate(Env.getCtx(), "QtyReserved"), "sum(QtyReserved)", Double.class)};
          /**	From Clause							*/
          String s_sqlFrom = " M_PRODUCT_STOCK_V ";
          /** Where Clause						*/
          String s_sqlWhere = "Value = ?";
          m_sqlWarehouse = warehouseTbl.prepareTable(s_layoutWarehouse, s_sqlFrom, s_sqlWhere, false, "M_PRODUCT_STOCK_V");
  		m_sqlWarehouse += " Group By Warehouse, documentnote ";
  		warehouseTbl.setRowSelectionAllowed(true);
  		warehouseTbl.setMultiSelection(false);
  		warehouseTbl.addMouseListener(this);
  		warehouseTbl.getSelectionModel().addListSelectionListener(this);
  		warehouseTbl.setShowTotals(true);
          warehouseTbl.autoSize();
          
          ColumnInfo[] s_layoutSubstitute = new ColumnInfo[]{
          		new ColumnInfo(Msg.translate(Env.getCtx(), "Warehouse"), "orgname", String.class),
          		new ColumnInfo(
      					Msg.translate(Env.getCtx(), "Value"),
      					"(Select Value from M_Product p where p.M_Product_ID=M_PRODUCT_SUBSTITUTERELATED_V.Substitute_ID)",
      					String.class), 
      			new ColumnInfo(Msg.translate(Env.getCtx(), "Name"), "Name", String.class),
      			new ColumnInfo(Msg.translate(Env.getCtx(), "QtyAvailable"), "QtyAvailable", Double.class),
    	        	new ColumnInfo(Msg.translate(Env.getCtx(), "QtyOnHand"), "QtyOnHand", Double.class),
      	        new ColumnInfo(Msg.translate(Env.getCtx(), "QtyReserved"), "QtyReserved", Double.class),
    	        	new ColumnInfo(Msg.translate(Env.getCtx(), "PriceStd"), "PriceStd", Double.class)};
          s_sqlFrom = "M_PRODUCT_SUBSTITUTERELATED_V";
          s_sqlWhere = "M_Product_ID = ? AND M_PriceList_Version_ID = ? and RowType = 'S'";
          m_sqlSubstitute = substituteTbl.prepareTable(s_layoutSubstitute, s_sqlFrom, s_sqlWhere, false, "M_PRODUCT_SUBSTITUTERELATED_V");
          substituteTbl.setRowSelectionAllowed(false);
          substituteTbl.setMultiSelection(false);
          substituteTbl.addMouseListener(this);
          substituteTbl.getSelectionModel().addListSelectionListener(this);
          substituteTbl.autoSize();
          
          ColumnInfo[] s_layoutRelated = new ColumnInfo[]{
          		new ColumnInfo(Msg.translate(Env.getCtx(), "Warehouse"), "orgname", String.class),
          		new ColumnInfo(
      					Msg.translate(Env.getCtx(), "Value"),
      					"(Select Value from M_Product p where p.M_Product_ID=M_PRODUCT_SUBSTITUTERELATED_V.Substitute_ID)",
      					String.class),
      			new ColumnInfo(Msg.translate(Env.getCtx(), "Name"), "Name", String.class),
      			new ColumnInfo(Msg.translate(Env.getCtx(), "QtyAvailable"), "QtyAvailable", Double.class),
    	        	new ColumnInfo(Msg.translate(Env.getCtx(), "QtyOnHand"), "QtyOnHand", Double.class),
      	        new ColumnInfo(Msg.translate(Env.getCtx(), "QtyReserved"), "QtyReserved", Double.class),
    	        	new ColumnInfo(Msg.translate(Env.getCtx(), "PriceStd"), "PriceStd", Double.class)};
          s_sqlFrom = "M_PRODUCT_SUBSTITUTERELATED_V";
          s_sqlWhere = "M_Product_ID = ? AND M_PriceList_Version_ID = ? and RowType = 'R'";
          m_sqlRelated = relatedTbl.prepareTable(s_layoutRelated, s_sqlFrom, s_sqlWhere, false, "M_PRODUCT_SUBSTITUTERELATED_V");
          relatedTbl.setRowSelectionAllowed(false);
          relatedTbl.setMultiSelection(false);
          relatedTbl.addMouseListener(this);
          relatedTbl.getSelectionModel().addListSelectionListener(this);
          relatedTbl.autoSize();
          
          //Available to Promise Tab
          m_tableAtp.setRowSelectionAllowed(false);
          m_tableAtp.setMultiSelection(false);
                
          CTabbedPane jTab  = new CTabbedPane();
          jTab.addTab(Msg.translate(Env.getCtx(), "Warehouse"), new JScrollPane(warehouseTbl));
          jTab.setPreferredSize(new Dimension(INFO_WIDTH, SCREEN_HEIGHT > 600 ? 250 : 105));
          jTab.addTab(Msg.translate(Env.getCtx(), "Description"), new JScrollPane(fieldDescription));
          jTab.addTab(Msg.translate(Env.getCtx(), "Substitute_ID"), new JScrollPane(substituteTbl));
          jTab.addTab(Msg.translate(Env.getCtx(), "RelatedProduct_ID"), new JScrollPane(relatedTbl));
  		jTab.addTab (Msg.getMsg(Env.getCtx(), "ATP"), new JScrollPane(m_tableAtp));
  		jTab.addChangeListener(this);
          tablePanel.setPreferredSize(new Dimension(INFO_WIDTH, SCREEN_HEIGHT > 600 ? 255 : 110));
          tablePanel.add(jTab);        
  
          warehouseStockPanel.setCollapsed(true);
          warehouseStockPanel.add(tablePanel);
          this.addonPanel.add(warehouseStockPanel);
          
          this.p_table.addKeyListener(new KeyAdapter() {
          	public void keyReleased(KeyEvent ke){
          		int row = ((MiniTable)ke.getSource()).getSelectedRow();
          		refresh(
          			((MiniTable)ke.getSource()).getValueAt(row,2),
          			new BigDecimal(pickWarehouse.getValue().toString()).intValue(),
          			new BigDecimal(pickPriceList.getValue().toString()).intValue()
          			);
          		warehouseStockPanel.setCollapsed(false);
          	}
          });
          
          this.p_table.addMouseListener(new MouseAdapter() {
              public void mouseClicked(MouseEvent me) {
              	int row = ((MiniTable)me.getSource()).getSelectedRow();
              	refresh(
              		((MiniTable)me.getSource()).getValueAt(row,2),
              		new BigDecimal(pickWarehouse.getValue().toString()).intValue(),
              		new BigDecimal(pickPriceList.getValue().toString()).intValue()
              		);
              	warehouseStockPanel.setCollapsed(false);
              }
          });
  		//End - fer_luck @ centuryon
  	}	//	statInit
  
  	//Begin - fer_luck @ centuryon
  	/**
  	 * 	Refresh Query
  	 */
  	private void refresh(Object obj, int M_Warehouse_ID, int M_PriceList_Version_ID)
  	{
  		//int M_Product_ID = 0;
  		String sql = m_sqlWarehouse;
  		//Add description to the query
  		sql = sql.replace(" FROM", ", DocumentNote FROM");
  		log.finest(sql);
  		PreparedStatement pstmt = null;
  		ResultSet rs = null;
  		try
  		{
  			pstmt = DB.prepareStatement(sql, null);
  			pstmt.setString(1, (String)obj);
  			rs = pstmt.executeQuery();
  			fieldDescription.setText("");
  			warehouseTbl.loadTable(rs);
  			rs = pstmt.executeQuery();
  			if(rs.next())
  				if(rs.getString("DocumentNote") != null)
  					fieldDescription.setText(rs.getString("DocumentNote"));
  		}
  		catch (Exception e)
  		{
  			log.log(Level.WARNING, sql, e);
  		}
  		finally
  		{
  			DB.close(rs, pstmt);
  			rs = null; pstmt = null;
  		}
  		
  		m_M_Product_ID = getSelectedRowKey();
  		
  		sql = m_sqlSubstitute;
  		log.finest(sql);
  		try {
  			pstmt = DB.prepareStatement(sql, null);
  			pstmt.setInt(1, m_M_Product_ID);
  			pstmt.setInt(2, M_PriceList_Version_ID);
  			rs = pstmt.executeQuery();
  			substituteTbl.loadTable(rs);
  			rs.close();
  		} catch (Exception e) {
  			log.log(Level.WARNING, sql, e);
  		}
  		finally
  		{
  			DB.close(rs, pstmt);
  			rs = null; pstmt = null;
  		}
  		
  		sql = m_sqlRelated;
  		log.finest(sql);
  		try {
  			pstmt = DB.prepareStatement(sql, null);
  			pstmt.setInt(1, m_M_Product_ID);
  			pstmt.setInt(2, M_PriceList_Version_ID);
  			rs = pstmt.executeQuery();
  			relatedTbl.loadTable(rs);
  			rs.close();
  		} catch (Exception e) {
  			log.log(Level.WARNING, sql, e);
  		}
  		finally
  		{
  			DB.close(rs, pstmt);
  			rs = null; pstmt = null;
  		}
  		initAtpTab(M_Warehouse_ID);
  	}	//	refresh
  	//End - fer_luck @ centuryon
  	
  	/**
  	 *	Dynamic Init
  	 *
  	 * @param value value
  	 * @param M_Warehouse_ID warehouse
  	 * @param M_PriceList_ID price list
  	 */
  	private void initInfo (String value, int M_Warehouse_ID, int M_PriceList_ID)
  	{
  		//	Pick init
  		fillPicks(M_PriceList_ID);
  		int M_PriceList_Version_ID = findPLV (M_PriceList_ID);
  		//	Set Value or Name
  		if (value.startsWith("@") && value.endsWith("@"))
  			fieldName.setText(value.substring(1,value.length()-1));
  		else
  			fieldValue.setText(value);
  		//	Set Warehouse
  		if (M_Warehouse_ID == 0)
  			M_Warehouse_ID = Env.getContextAsInt(Env.getCtx(), "#M_Warehouse_ID");
  		if (M_Warehouse_ID != 0)
  			setWarehouse (M_Warehouse_ID);
  		// 	Set PriceList Version
  		if (M_PriceList_Version_ID != 0)
  			setPriceListVersion (M_PriceList_Version_ID);
  
  		//	Create Grid
  		StringBuffer where = new StringBuffer();
  		where.append("p.IsActive='Y'");
  		if (M_Warehouse_ID != 0)
  			where.append(" AND p.IsSummary='N'");
  		//  dynamic Where Clause
  		if (p_whereClause != null && p_whereClause.length() > 0)
  			where.append(" AND ")   //  replace fully qualified name with alias
  				.append(Util.replace(p_whereClause, "M_Product.", "p."));
  		//
  		prepareTable(getProductLayout(),
  			s_productFrom,
  			where.toString(),
  			"QtyAvailable DESC, Margin DESC");
  		p_table.setShowTotals(false);
  		//
  		pickWarehouse.addActionListener(this);
  		pickPriceList.addActionListener(this);
  		pickProductCategory.addActionListener(this);
  		pickAS.addActionListener(this); // @Trifon
  	}	//	initInfo
  
  	/**
  	 *	Fill Picks with values
  	 *
  	 * @param M_PriceList_ID price list
  	 */
  	private void fillPicks (int M_PriceList_ID)
  	{
  		//	Price List
  		String SQL = "SELECT M_PriceList_Version.M_PriceList_Version_ID,"
  			+ " M_PriceList_Version.Name || ' (' || c.Iso_Code || ')' AS ValueName "
  			+ "FROM M_PriceList_Version, M_PriceList pl, C_Currency c "
  			+ "WHERE M_PriceList_Version.M_PriceList_ID=pl.M_PriceList_ID"
  			+ " AND pl.C_Currency_ID=c.C_Currency_ID"
  			+ " AND M_PriceList_Version.IsActive='Y' AND pl.IsActive='Y'";
  		//	Same PL currency as original one
  		if (M_PriceList_ID != 0)
  			SQL += " AND EXISTS (SELECT * FROM M_PriceList xp WHERE xp.M_PriceList_ID=" + M_PriceList_ID
  				+ " AND pl.C_Currency_ID=xp.C_Currency_ID)";
  		//	Add Access & Order
  		SQL = MRole.getDefault().addAccessSQL (SQL, "M_PriceList_Version", true, false)	// fully qualified - RO 
  			+ " ORDER BY M_PriceList_Version.Name";
  		PreparedStatement pstmt = null;
  		ResultSet rs = null;
  		try
  		{
  			pickPriceList.addItem(new KeyNamePair (0, ""));
  			pstmt = DB.prepareStatement(SQL, null);
  			rs = pstmt.executeQuery();
  			while (rs.next())
  			{
  				KeyNamePair kn = new KeyNamePair (rs.getInt(1), rs.getString(2));
  				pickPriceList.addItem(kn);
  			}
  			DB.close(rs, pstmt);
  
  			//	Warehouse
  			SQL = MRole.getDefault().addAccessSQL (
  				"SELECT M_Warehouse_ID, Value || ' - ' || Name AS ValueName "
  				+ "FROM M_Warehouse "
  				+ "WHERE IsActive='Y'",
  					"M_Warehouse", MRole.SQL_NOTQUALIFIED, MRole.SQL_RO)
  				+ " ORDER BY Value";
  			pickWarehouse.addItem(new KeyNamePair (0, ""));
  			pstmt = DB.prepareStatement(SQL, null);
  			rs = pstmt.executeQuery();
  			while (rs.next())
  			{
  				KeyNamePair kn = new KeyNamePair
  					(rs.getInt("M_Warehouse_ID"), rs.getString("ValueName"));
  				pickWarehouse.addItem(kn);
  			}
  			DB.close(rs, pstmt);
  			
  			//	Product Category
  			SQL = MRole.getDefault().addAccessSQL (
  				"SELECT M_Product_Category_ID, Value || ' - ' || Name FROM M_Product_Category WHERE IsActive='Y'",
  					"M_Product_Category", MRole.SQL_NOTQUALIFIED, MRole.SQL_RO)
  				+ " ORDER BY Value";
  			for (KeyNamePair kn : DB.getKeyNamePairs(SQL, true)) {
  				pickProductCategory.addItem(kn);
  			}
  			
  			// Attribute Set - @Trifon
  			SQL = MRole.getDefault().addAccessSQL (
  					"SELECT M_AttributeSet_ID, Name FROM M_AttributeSet WHERE IsActive='Y'",
  						"M_AttributeSet", MRole.SQL_NOTQUALIFIED, MRole.SQL_RO)
  					+ " ORDER BY Name";
  				for (KeyNamePair kn : DB.getKeyNamePairs(SQL, true)) {
  					pickAS.addItem(kn);
  				}
  		}
  		catch (SQLException e)
  		{
  			log.log(Level.SEVERE, SQL, e);
  			setStatusLine(e.getLocalizedMessage(), true);
  		}
  		finally
  		{
  			DB.close(rs, pstmt);
  			rs = null; pstmt = null;
  		}
  	}	//	fillPicks
  
  	/**
  	 *	Set Warehouse
  	 *
  	 * 	@param M_Warehouse_ID warehouse
  	 */
  	private void setWarehouse(int M_Warehouse_ID)
  	{
  		for (int i = 0; i < pickWarehouse.getItemCount(); i++)
  		{
  			KeyNamePair kn = (KeyNamePair)pickWarehouse.getItemAt(i);
  			if (kn.getKey() == M_Warehouse_ID)
  			{
  				pickWarehouse.setSelectedIndex(i);
  				return;
  			}
  		}
  	}	//	setWarehouse
  
  	/**
  	 *	Set PriceList
  	 *
  	 * @param M_PriceList_Version_ID price list
  	 */
  	private void setPriceListVersion(int M_PriceList_Version_ID)
  	{
  		log.config("M_PriceList_Version_ID=" + M_PriceList_Version_ID);
  		for (int i = 0; i < pickPriceList.getItemCount(); i++)
  		{
  			KeyNamePair kn = (KeyNamePair)pickPriceList.getItemAt(i);
  			if (kn.getKey() == M_PriceList_Version_ID)
  			{
  				pickPriceList.setSelectedIndex(i);
  				return;
  			}
  		}
  		log.fine("NOT found");
  	}	//	setPriceList
  
  	/**
  	 *	Find Price List Version and update context
  	 *
  	 * @param M_PriceList_ID price list
  	 * @return M_PriceList_Version_ID price list version
  	 */
  	private int findPLV (int M_PriceList_ID)
  	{
  		Timestamp priceDate = null;
  		//	Sales Order Date
  		String dateStr = Env.getContext(Env.getCtx(), p_WindowNo, "DateOrdered"); 
  		if (dateStr != null && dateStr.length() > 0)
  			priceDate = Env.getContextAsDate(Env.getCtx(), p_WindowNo, "DateOrdered");
  		else	//	Invoice Date
  		{
  			dateStr = Env.getContext(Env.getCtx(), p_WindowNo, "DateInvoiced");
  			if (dateStr != null && dateStr.length() > 0)
  				priceDate = Env.getContextAsDate(Env.getCtx(), p_WindowNo, "DateInvoiced");
  		}
  		//	Today
  		if (priceDate == null) 
  			priceDate = new Timestamp(System.currentTimeMillis());
  		//
  		log.config("M_PriceList_ID=" + M_PriceList_ID + " - " + priceDate);
  		int retValue = 0;
  		String sql = "SELECT plv.M_PriceList_Version_ID, plv.ValidFrom "
  			+ "FROM M_PriceList pl, M_PriceList_Version plv "
  			+ "WHERE pl.M_PriceList_ID=plv.M_PriceList_ID"
  			+ " AND plv.IsActive='Y'"
  			+ " AND pl.M_PriceList_ID=? "					//	1
  			+ "ORDER BY plv.ValidFrom DESC";
  		//	find newest one
  		PreparedStatement pstmt = null;
  		ResultSet rs = null;
  		try
  		{
  			pstmt = DB.prepareStatement(sql, null);
  			pstmt.setInt(1, M_PriceList_ID);
  			rs = pstmt.executeQuery();
  			while (rs.next() && retValue == 0)
  			{
  				Timestamp plDate = rs.getTimestamp(2);
  				if (!priceDate.before(plDate))
  					retValue = rs.getInt(1);
  			}
  		}
  		catch (SQLException e)
  		{
  			log.log(Level.SEVERE, sql, e);
  		}
  		finally {
  			DB.close(rs, pstmt);
  			rs = null; pstmt = null;
  		}
  		Env.setContext(Env.getCtx(), p_WindowNo, "M_PriceList_Version_ID", retValue);
  		return retValue;
  	}	//	findPLV
  
  	
  	/**************************************************************************
  	 *	Construct SQL Where Clause and define parameters
  	 *  (setParameters needs to set parameters)
  	 *  Includes first AND
  	 *  @return SQL WHERE clause
  	 */
  	protected String getSQLWhere()
  	{
  		StringBuffer where = new StringBuffer();
  		
  		//	Optional PLV
  		int M_PriceList_Version_ID = 0;
  		KeyNamePair pl = (KeyNamePair)pickPriceList.getSelectedItem();
  		if (pl != null)
  			M_PriceList_Version_ID = pl.getKey();
  		if (M_PriceList_Version_ID != 0)
  			where.append(" AND pr.M_PriceList_Version_ID=?");
  		
  		//  Optional Product Category
  		if (getM_Product_Category_ID() > 0) {
  			where.append(" AND p.M_Product_Category_ID=?");
  		}
  		
  		//  Optional Attribute Set
  		if (getM_AttributeSet_ID() > 0) {
  			where.append(" AND p.M_AttributeSet_ID=?");
  		}
  		
  		//	Product Attribute Search
  		if (m_pAttributeWhere != null)
  		{
  			where.append(m_pAttributeWhere);
  			return where.toString();
  		}
  
  		//  => Value
  		String value = fieldValue.getText().toUpperCase();
  		if (!(value.equals("") || value.equals("%")))
  			where.append(" AND UPPER(p.Value) LIKE ?");
  
  		//  => Name
  		String name = fieldName.getText().toUpperCase();
  		if (!(name.equals("") || name.equals("%")))
  			where.append(" AND UPPER(p.Name) LIKE ?");
  
  		//  => UPC
  		String upc = fieldUPC.getText().toUpperCase();
  		if (!(upc.equals("") || upc.equals("%")))
  			where.append(" AND UPPER(p.UPC) LIKE ?");
  
  		//  => SKU
  		String sku = fieldSKU.getText().toUpperCase();
  		if (!(sku.equals("") || sku.equals("%")))
  			where.append(" AND UPPER(p.SKU) LIKE ?");
  		//	=> Vendor
  		String vendor = fieldVendor.getText().toUpperCase();
  		if (!(vendor.equals("") || vendor.equals("%")))
  			where.append(" AND UPPER(bp.Name) LIKE ? AND ppo.IsCurrentVendor='Y'");
  		
  		return where.toString();
  	}	//	getSQLWhere
  
  	/**
  	 *  Set Parameters for Query
  	 *  (as defined in getSQLWhere)
  	 *
  	 * @param pstmt pstmt
  	 *  @param forCount for counting records
  	 * @throws SQLException
  	 */
  	protected void setParameters(PreparedStatement pstmt, boolean forCount) throws SQLException
  	{
  		int index = 1;
  
  		//  => Warehouse
  		int M_Warehouse_ID = 0;
  		KeyNamePair wh = (KeyNamePair)pickWarehouse.getSelectedItem();
  		if (wh != null)
  			M_Warehouse_ID = wh.getKey();
  		if (!forCount)	//	parameters in select
  		{
  			for (int i = 0; i < p_layout.length; i++)
  			{
  				if (p_layout[i].getColSQL().indexOf('?') != -1)
  					pstmt.setInt(index++, M_Warehouse_ID);
  			}
  		}
  		log.fine("M_Warehouse_ID=" + M_Warehouse_ID + " (" + (index-1) + "*)");
  
  		//  => PriceList
  		int M_PriceList_Version_ID = 0;
  		KeyNamePair pl = (KeyNamePair)pickPriceList.getSelectedItem();
  		if (pl != null)
  			M_PriceList_Version_ID = pl.getKey();
  		if (M_PriceList_Version_ID != 0)
  		{
  			pstmt.setInt(index++, M_PriceList_Version_ID);
  			log.fine("M_PriceList_Version_ID=" + M_PriceList_Version_ID);
  		}
  		//  => Product Category
  		int M_Product_Category_ID = getM_Product_Category_ID();
  		if (M_Product_Category_ID > 0) {
  			pstmt.setInt(index++, M_Product_Category_ID);
  			log.fine("M_Product_Category_ID=" + M_Product_Category_ID);
  		}
  		//  => Attribute Set - @Trifon
  		int M_AttributeSet_ID = getM_AttributeSet_ID();
  		if (M_AttributeSet_ID > 0) {
  			pstmt.setInt(index++, M_AttributeSet_ID);
  			log.fine("M_AttributeSet_ID=" + M_AttributeSet_ID);
  		}
  		//	Rest of Parameter in Query for Attribute Search
  		if (m_pAttributeWhere != null)
  			return;
  
  		//  => Value
  		String value = fieldValue.getText().toUpperCase();
  		if (!(value.equals("") || value.equals("%")))
  		{
  			if (!value.endsWith("%"))
  				value += "%";
  			pstmt.setString(index++, value);
  			log.fine("Value: " + value);
  		}
  
  		//  => Name
  		String name = fieldName.getText().toUpperCase();
  		if (!(name.equals("") || name.equals("%")))
  		{
  			if (!name.endsWith("%"))
  