


package ibase.webitm.ejb.wms;

import ibase.system.config.*;
import ibase.webitm.ejb.*;
import ibase.webitm.ejb.dis.DistCommon;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;
import ibase.webitm.utility.wms.CommonWmsUtil;

import java.io.File;
import java.rmi.RemoteException;
import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;

import org.w3c.dom.*;

@javax.ejb.Stateless
public class MultiplePtcnWiz extends ValidatorEJB implements MultiplePtcnWizLocal, MultiplePtcnWizRemote 
{
	/**
	 * The method is defined without any parameter and returns blank string
	 */	
	@Override
	
	public String wfValData() throws RemoteException, ITMException
	{
		return "";
	}

	/**
	 * The public method is used for converting the current form data into a document(DOM)
	 * The dom is then given as argument to the overloaded function wfValData to perform validation
	 * Returns validation string if exists else returns null in XML format
	 * @param xmlString contains the current form data in XML format
	 * @param xmlString1 contains all the header information in the XML format
	 * @param xmlString2 contains the data of all the forms in XML format
	 * @param objContext represents the form number
	 * @param editFlag represents the mode of transaction(A-Add or E-Edit)
	 * @param xtraParams contains additional information such as loginEmpCode,loginCode,chgTerm etc
	 */	
	@Override
	public String wfValData(String xmlString, String xmlString1, String xmlString2, String objContext,String editFlag, String xtraParams) throws RemoteException, ITMException
	{
		Document dom = null;
		Document dom1 = null;
		Document dom2 = null;
		String errString = null;
		GenericUtility genericUtility = GenericUtility.getInstance();    

		try
		{
			System.out.println( "xmlString ::" + xmlString);
			System.out.println( "xmlString1 ::" + xmlString1);
			System.out.println( "xmlString2 ::" + xmlString2);
			if(xmlString != null && xmlString.trim().length()!=0)
			{
				dom = genericUtility.parseString(xmlString); 
			}
			if(xmlString1 != null && xmlString1.trim().length()!=0)
			{
				dom1 = genericUtility.parseString(xmlString1); 
			}
			if(xmlString2 != null && xmlString2.trim().length()!=0)
			{
				dom2 = genericUtility.parseString(xmlString2); 
			}
			errString = wfValData( dom, dom1, dom2, objContext, xtraParams);
			System.out.println( "ErrString: " + errString);
		}
		catch(Exception e)
		{
			errString = genericUtility.createErrorString(e);
			e.printStackTrace();
			throw new ITMException(e);
		}
		return (errString); 
	}

	/**
	 * The public overloaded method takes a document as input and is used for the validation of required fields 
	 * Returns validation string if exist otherwise returns null in XML format
	 * @param currFormDataDom contains the current form data as a document object model
	 * @param hdrDataDom contains all the header information
	 * @param allFormDataDom contains the field data of all the forms 
	 * @param objContext represents form number
	 * @param editFlag represents the mode of transaction(A-Add or E-Edit)
	 * @param xtraParams contains additional information such as loginEmpCode,loginCode,chgTerm etc
	 */
	@Override
	public String wfValData(Document dom, Document dom1, Document dom2, String objContext, String xtraParams) throws RemoteException, ITMException
	{   				
		
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;
		StringBuffer errStringXml = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root><Errors>");
		String errorType = "", errCode = "", docLocCode = "" ,statusPkord= "", locCode = "";
		ArrayList <String> errList = new ArrayList<String>();
		ArrayList <String>errFields = new ArrayList <String> ();
		GenericUtility genericUtility;
		HashMap itmVolumeMap = new HashMap();
		ResultSet rs= null;
		PreparedStatement pstmt = null;
		Connection conn = null;
		String assignTo = "",userId = "",childNodeName = "",errString = "",remarks = "",qtyStr = "",pickOrder = "",
				sql = "";
		int ctr = 0, currentFormNo = 0, childNodeListLength = 0, cnt = 0;
		double quantity = 0;
		try
		{	
			genericUtility = GenericUtility.getInstance();			
			System.out.println("xtraParam----->>["+xtraParams+"]");
			System.out.println("DOM---->>["+genericUtility.serializeDom(dom).toString()+"]");
			System.out.println("DOM1----->>["+genericUtility.serializeDom(dom1).toString()+"]");
			System.out.println("DOM2----->>["+genericUtility.serializeDom(dom2).toString()+"]");	

			ConnDriver connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");
			conn.setAutoCommit(false);
			connDriver = null;
			userId = getValueFromXTRA_PARAMS(xtraParams, "loginCode");
			
			if (objContext != null && objContext.trim().length() > 0) {
				currentFormNo = Integer.parseInt(objContext);
			}	
			
			
			switch ( currentFormNo )  // Switch Case for form number
			{
			case 1:
				System.out.println("------in detail1 validation----------------");
				System.out.println("DOM---->>["+genericUtility.serializeDom(dom).toString()+"]");
				System.out.println("DOM1----->>["+genericUtility.serializeDom(dom1).toString()+"]");
				System.out.println("DOM2----->>["+genericUtility.serializeDom(dom2).toString()+"]");	
				parentNodeList = dom.getElementsByTagName("Detail1");
				parentNode = parentNodeList.item(0);
				childNodeList = parentNode.getChildNodes();
				childNodeListLength = childNodeList.getLength();

				for (ctr = 0; ctr < childNodeListLength; ctr++) 
				{					
					childNode = childNodeList.item(ctr);
					childNodeName = childNode.getNodeName();
					System.out.println("childNodeName11------->>["+childNodeName+"]");
					if("remarks".equalsIgnoreCase(childNodeName))
					{
						remarks = checkNull(genericUtility.getColumnValue("remarks",dom));						
						System.out.println("remarks l---------->>["+remarks.length()+"]");	
						if(remarks.length() > 60)
						{
							errCode = "VTREMKL";//Entered remarks should not be greater than 60 character.
							errList.add( errCode );
							errFields.add( childNodeName.toLowerCase() );
						}
						
					}
					
				}
				break;			

			case 2:
				System.out.println("------in detail2 validation----------------");
				System.out.println("DOM---->>["+genericUtility.serializeDom(dom).toString()+"]");
				System.out.println("DOM1----->>["+genericUtility.serializeDom(dom1).toString()+"]");
				System.out.println("DOM2----->>["+genericUtility.serializeDom(dom2).toString()+"]");	
				parentNodeList = dom2.getElementsByTagName("Detail2");
				parentNode = parentNodeList.item(0);
				System.out.println("parentNode >>>{"+parentNode+"}");
				childNodeList = parentNode.getChildNodes();
				childNodeListLength = childNodeList.getLength();
				for(ctr = 0; ctr < childNodeListLength; ctr++)
				{
					childNode = childNodeList.item(ctr);
					childNodeName = childNode.getNodeName();
					System.out.println("value of child node name ["+childNodeName + "]");
					if("ptcn".equalsIgnoreCase(childNodeName))
					{
						String ptcn = "",status = "",holdStatus = "",cancel = "";
						int ptcnYCount = 0,rowCnt = 0; 
						ptcn = checkNull(genericUtility.getColumnValue("ptcn",dom2));
						System.out.println("PTCN11------>>["+ptcn+"]");	
						//rowCnt = getDBRowCount(conn, "MULTI_PICK_ISS_HDR", "PTCN", ptcn);
						//System.out.println("rowCnt MULTI_PICK_ISS_HDR------>>["+rowCnt+"]");	
						if(isExistPtcn(dom2,conn))
						{
							errCode = "VTPTCNAS";//Entered PTCN already scanned.
							errList.add( errCode );
							errFields.add( childNodeName.toLowerCase() );
						}
						if(ptcn.length() == 0)
						{
							errCode = "VTPTCNNN";// PTCN blank validation
							errList.add( errCode );
							errFields.add( childNodeName.toLowerCase() );
						}
						else
						{
							cnt=getDBRowCount(conn, "wave_task_det", "ptcn", ptcn);	
							if(cnt == 0)
							{
								errCode = "VTPTCNNE";//Entered PTCN is not defined in wave_task_det master.
								errList.add( errCode );
								errFields.add( childNodeName.toLowerCase() );
							}
						}
						//validation for check PTCN confirmed or not.
						rowCnt = 0;
						rowCnt = getDBRowCount(conn, "WAVE_TASK_DET", "PTCN", ptcn);						
						sql ="SELECT STATUS FROM WAVE_TASK_DET WHERE PTCN = ?";						
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, ptcn);
						rs = pstmt.executeQuery();
						while(rs.next())
						{
							status = checkNull(rs.getString("STATUS"));
							if("Y".equalsIgnoreCase(status))
							{
								ptcnYCount++;
							}
						}
						if(rs != null)
						{
							rs.close();
							rs = null;
						}
						if(pstmt != null)
						{
							pstmt.close();
							pstmt = null;
						}
						System.out.println("rowCnt---->["+rowCnt+"  ptcnYCount----->>["+ptcnYCount+"]");
						if(ptcnYCount == rowCnt)
						{
							errCode = "VTPTCNAC";//Entered PTCN confirmed.
							errList.add( errCode );
							errFields.add( childNodeName.toLowerCase() );
						}
						
						
						sql = "SELECT T.HOLD_STATUS,T.CANCEL FROM WAVE_TASK_DET D ,WAVE_TASK T "
								+ "WHERE D.WAVE_ID = T.WAVE_ID AND D.PTCN = ?";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, ptcn);
						rs = pstmt.executeQuery();
						while(rs.next())
						{
							holdStatus = checkNull(rs.getString(1));
							cancel = checkNull(rs.getString(2));
							
							if("Y".equalsIgnoreCase(holdStatus))
							{
								errCode = "VTPTCNOH";//Entered PTCN on hold
								errList.add( errCode );
								errFields.add( childNodeName.toLowerCase() );
								break;
							}
							if("Y".equalsIgnoreCase(cancel))
							{
								errCode = "VTPTCNIC";//Entered PTCN is cancel.
								errList.add( errCode );
								errFields.add( childNodeName.toLowerCase() );
								break;
							}
						}
						if(rs != null)
						{
							rs.close();
							rs = null;
						}
						if(pstmt != null)
						{
							pstmt.close();
							pstmt = null;
						}
						status = "";
						sql ="SELECT STATUS FROM WAVE_TASK_DET WHERE REF_ID IN ("
								+ "SELECT REPL_ORDER FROM PICK_ORD_DET WHERE PICK_ORDER IN ("
								+ "SELECT REF_ID FROM WAVE_TASK_DET WHERE PTCN = ?))";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, ptcn);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							status = checkNull(rs.getString("STATUS"));
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						System.out.println("status--------->>["+status+"]");
						
						if("N".equalsIgnoreCase(status))
						{
							errCode = "VTPTCNTNC";//Dependent task not completed
							errList.add( errCode );
							errFields.add( childNodeName.toLowerCase() );
							break;
						}
					
					}
				} //end detail2 for loop

				break;
			case 3:
				System.out.println("------in detail3 validation----------------");
				System.out.println("DOM---->>["+genericUtility.serializeDom(dom).toString()+"]");
				System.out.println("DOM1----->>["+genericUtility.serializeDom(dom1).toString()+"]");
				System.out.println("DOM2----->>["+genericUtility.serializeDom(dom2).toString()+"]");	
				parentNodeList = dom2.getElementsByTagName("Detail3");
				parentNode = parentNodeList.item(0);
				childNodeList = parentNode.getChildNodes();
				childNodeListLength = childNodeList.getLength();

				for(ctr = 0; ctr < childNodeListLength; ctr++)
				{
					childNode = childNodeList.item(ctr);
					childNodeName = childNode.getNodeName();
					System.out.println("childNodeName11------->>["+childNodeName+"]");
					System.out.println("value of child node : "+childNode);
					if("ptcn".equalsIgnoreCase(childNodeName))
					{
						String ptcn = "";
						ptcn = checkNull(genericUtility.getColumnValue("ptcn",dom1,"2"));
						System.out.println("PTCN11------>>["+ptcn+"]");	
						ptcn = checkNull(genericUtility.getColumnValue("ptcn",dom2,"3"));
						System.out.println("PTCN12------>>["+ptcn+"]");	
						if(ptcn.length() == 0)
						{
							errCode = "VTPTCNNN";// PTCN blank validation
							errList.add( errCode );
							errFields.add( childNodeName.toLowerCase() );
						}	
						
						
					}
					else if("item_code".equalsIgnoreCase( childNodeName ))
					{
						String itemCd = "",pickOrd = "",sqlitem = "",hazMaterial = "",sqlptcn = "",ptcnHaz = "";
						String sqlTran = "",transMode = "",waveIdHaz = "";
						PreparedStatement pstmtitem = null;
						ResultSet rsitem = null;
						//to check item is hazarous.start
						itemCd = checkNull(genericUtility.getColumnValue( "item_code", dom2, "3" ));
						pickOrd =  genericUtility.getColumnValue("pick_order", dom2, "3" );
						System.out.println("itemCd------>>["+itemCd+"] pickOrd : ["+pickOrd+"]");						
						sqlitem = "SELECT HAZARDOUS FROM ITEM WHERE ITEM_CODE=? ";
						pstmtitem = conn.prepareStatement(sqlitem);
						
						pstmtitem.setString(1,itemCd);
						
						rsitem = pstmtitem.executeQuery();
						if(rsitem.next())
						{
							hazMaterial = checkNull(rsitem.getString("HAZARDOUS"));									
							System.out.println("hazardous :"+hazMaterial);
						}
						if(pstmtitem != null)
						{
							pstmtitem.close();
							pstmtitem = null;
						}
						if(rsitem != null)
						{
							rsitem.close();
							rsitem = null;
						}
						//to check item is hazarous.end
						//to check transporter mode.start
						
						
						sqlptcn ="SELECT PTCN,WAVE_ID FROM WAVE_TASK_DET WHERE REF_ID = ?";
						pstmtitem = conn.prepareStatement(sqlptcn);
						
						pstmtitem.setString(1,pickOrd);
						
						rsitem = pstmtitem.executeQuery();
						if(rsitem.next())
						{
							ptcnHaz = rsitem.getString("PTCN") == null?"":rsitem.getString("PTCN");
							waveIdHaz = rsitem.getString("WAVE_ID") == null?"":rsitem.getString("WAVE_ID");
										
							System.out.println("ptcnHaz*"+ptcnHaz+"waveIdHaz"+waveIdHaz);
						}
						if(pstmtitem != null)
						{
							pstmtitem.close();
							pstmtitem = null;
						}
						if(rsitem != null)
						{
							rsitem.close();
							rsitem = null;
						}
						
						/*
						sqlTran  = "SELECT TRANS_MODE FROM DESPATCH WHERE DESP_ID IN(SELECT REF_ID FROM WAVE_TASK_DET "
								   +" WHERE REF_SER = ? AND WAVE_ID  IN(SELECT WAVE_ID FROM WAVE_TASK_DET WHERE REF_ID = ?) )";
						*/
						sqlTran  = "SELECT TRANS_MODE FROM DESPATCH WHERE DESP_ID IN(SELECT REF_ID FROM WAVE_TASK_DET "
								   +" WHERE REF_SER = ? AND WAVE_ID = ? AND PTCN = ?)";
						pstmtitem = conn.prepareStatement(sqlTran);
						
						pstmtitem.setString(1,"S-DSP");
						pstmtitem.setString(2,waveIdHaz);
						pstmtitem.setString(3,ptcnHaz);
						
						rsitem = pstmtitem.executeQuery();
						if(rsitem.next())
						{
							transMode = checkNull(rsitem.getString("TRANS_MODE"));										
							System.out.println("transMode : ["+transMode+"]");							
						}
						if(pstmtitem != null)
						{
							pstmtitem.close();
							pstmtitem = null;
						}
						if(rsitem != null)
						{
							rsitem.close();
							rsitem = null;
						}
						//to check transporter mode.end
						if("Y".equalsIgnoreCase(hazMaterial) && "A".equalsIgnoreCase(transMode))
						{
							errList.add( "VTHAZMAT" );
							errFields.add( childNodeName.toLowerCase() );		
						}	
						
					
					}
					else if("quantity".equalsIgnoreCase(childNodeName))
					{
						String itemCode = "",lineNoPickord = "",pickType = "",locCodeL = "";
						double ordQuantity = 0,quantityL = 0;
						System.out.println("quantity validation............");
						qtyStr = checkNull(genericUtility.getColumnValue("quantity",dom2,"3"));
						locCodeL = checkNull(genericUtility.getColumnValue("loc_code",dom2,"3"));
						lineNoPickord = genericUtility.getColumnValue("pick_ord__line_no",dom2,"3");
						lineNoPickord = lineNoPickord == null ? "" :lineNoPickord;
						System.out.println("lineNoPickord----->>["+lineNoPickord+"]");
						
						//lineNoSord = checkNull(genericUtility.getColumnValue("line_no__sord",dom2,"3"));
						qtyStr = qtyStr.length() == 0 ? "0" :qtyStr;
						quantityL = Double.parseDouble(qtyStr);
						System.out.println("qtyStr----->>["+qtyStr+"]");
						pickOrder = checkNull(genericUtility.getColumnValue("pick_order",dom2,"3"));
						System.out.println("pickOrder----->>["+pickOrder+"]");
						itemCode = checkNull(genericUtility.getColumnValue("item_code",dom2,"3"));
						System.out.println("itemCode----->>["+itemCode+"]");
						
						
						if (qtyStr == null || "null".equals(qtyStr) || qtyStr.trim().length() == 0)
						{
							qtyStr = "0";
						}
						quantityL  = Double.parseDouble(qtyStr);
						if(qtyStr == null || qtyStr.trim().length() == 0)
						{
							errList.add( "VTQUNT2" );
							errFields.add( childNodeName.toLowerCase() );
						}
						else
						{
							
							pickType = checkNull(getColumnDescr(conn, "PICK_TYPE", "PICK_ORD_HDR", "PICK_ORDER", pickOrder));							
							
							sql = "SELECT (QUANTITY - CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE DEALLOC_QTY END ) AS QUANTITY " +
									" ,NO_ART FROM PICK_ORD_DET WHERE PICK_ORDER = ? AND LINE_NO = ?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, pickOrder);
							pstmt.setString(2, lineNoPickord);
							
							rs = pstmt.executeQuery();
							if(rs.next())
							{
								ordQuantity = rs.getDouble(1);
							}
							System.out.println("quantityL--ordQuantity--->>["+quantityL+"] ["+ordQuantity+"]");
							System.out.println("pickType--->>["+pickType+"]");
							if("A".equals(pickType) && quantityL != ordQuantity)
							{
								errCode = "VQUANTI";
								errList.add( errCode );
								errFields.add( childNodeName.toLowerCase() );
							}
						}
					}
				}
				break;
			case 4:

			{

			}

			}//End of switch statement
			int errListSize = errList.size();
			cnt =0;
			String errFldName = null;
			if ( errList != null && errListSize > 0 )
			{
				for (cnt = 0; cnt < errListSize; cnt++ )
				{
					errCode = errList.get(cnt);
					errFldName = errFields.get(cnt);
					System.out.println("errCode .........."+errCode);
					errString = getErrorString( errFldName, errCode, userId );
					errorType =  errorType( conn, errCode );
					if ( errString.length() > 0)
					{
						String bifurErrString = errString.substring( errString.indexOf("<Errors>") + 8,errString.indexOf("<trace>"));
						bifurErrString =bifurErrString+errString.substring( errString.indexOf("</trace>") + 8,errString.indexOf("</Errors>"));
						errStringXml.append(bifurErrString);
						System.out.println("errStringXml .........."+errStringXml);
						errString = "";
					}
					if ( errorType.equalsIgnoreCase("E"))
					{
						break;
					}
				}
				errList.clear();
				errList = null;
				errFields.clear();
				errFields = null;
				
				errStringXml.append("</Errors></Root>\r\n");
			}
			else
			{
				errStringXml = new StringBuffer( "" );
			}	
			errString = errStringXml.toString();
		}
		catch ( Exception e )
		{
			System.out.println ( "Exception: MultiplePtcnWiz: wfValData( Document currFormDataDom ): " + e.getMessage() + ":" );
			//throw new ITMException(e);
			e.printStackTrace();			
		}
		finally
		{
			try
			{
				if (conn != null)
				{
					conn.close();
					conn = null;
				}
			}
			catch(Exception e)
			{
				System.out.println( "Exception : MultiplePtcnWiz:wfValData : " + e.getMessage() );
				throw new ITMException(e);
			}
		}
		System.out.println( "errString>>>>>>>::"+errString );
		return errString;
	}
	private String errorType( Connection conn , String errorCode )
	{
		String msgType = "";
		PreparedStatement pstmt = null ; 
		ResultSet rs = null;
		try
		{			
			String  sql = " SELECT MSG_TYPE FROM MESSAGES WHERE MSG_NO =   ? ";

			pstmt = conn.prepareStatement( sql );			
			pstmt.setString(1, errorCode);			
			rs = pstmt.executeQuery();
			while( rs.next() )
			{
				msgType = rs.getString("MSG_TYPE");
			}			
		}
		catch (Exception ex)
		{
			ex.printStackTrace();
		}		
		finally
		{
			try
			{
				if ( rs != null )
				{
					rs.close();
					rs = null;
				}
				if ( pstmt != null )
				{
					pstmt.close();
					pstmt = null;
				}
			}
			catch ( Exception e )
			{
				e.printStackTrace();
			}
		}		
		return msgType;
	}


	/**
	 * The public method is defined without any parameters and returns blank string
	 */
	@Override
	public String itemChanged() throws RemoteException, ITMException
	{
		return "";
	}

	/**
	 * The public method is used for converting the current form data into a document(dom)
	 * The dom is then given as argument to the overloaded function wfValData to perform validation
	 * Returns validation string if exists else returns null in XML format
	 * @param xmlString contains the current form data in XML format
	 * @param xmlString1 contains all the header information in the XML format
	 * @param xmlString2 contains the data of all the forms in XML format
	 * @param objContext represents the form number
	 * @param editFlag represents the mode of transaction(A-Add or E-Edit)
	 * @param xtraParams contains additional information such as loginEmpCode,loginCode,chgTerm etc
	 */
	@Override
	public String itemChanged(String xmlString, String xmlString1, String xmlString2, String objContext, String currentColumn, String editFlag, String xtraParams) throws RemoteException, ITMException
	{
		Document currFormDataDom = null;
		Document hdrDataDom = null;
		Document allFormDataDom = null;
		String errString = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		System.out.println("xmlString ["+xmlString+"]");
		System.out.println("xmlString1 ["+xmlString1+"]");
		System.out.println("xmlString2 ["+xmlString2+"]");
		try
		{
			if (xmlString != null && xmlString.trim().length()!=0)
			{
				currFormDataDom = genericUtility.parseString(xmlString); 
			}
			if (xmlString1 != null && xmlString1.trim().length()!=0)
			{
				hdrDataDom = genericUtility.parseString(xmlString1); 
			}
			if (xmlString2 != null && xmlString2.trim().length()!=0)
			{
				allFormDataDom = genericUtility.parseString(xmlString2); 
			}
			System.out.println ( "Calling  itemChanged( currFormDataDom, hdrDataDom, allFormDataDom, objContext, currentColumn, editFlag, xtraParams )");
			errString = itemChanged( currFormDataDom, hdrDataDom, allFormDataDom, objContext, currentColumn, editFlag, xtraParams );
			System.out.println ( "ErrString :" + errString);
		}
		catch (Exception e)
		{
			System.out.println ( "Exception :MultiplePtcnWiz :itemChanged(String,String):" + e.getMessage() + ":" );
			throw new ITMException(e);
		}
		System.out.println ( "returning from MultiplePtcnWiz itemChanged \n[" + errString + "]" );

		return errString;
	}

	/**
	 * The public overloaded method takes a document as input and is used for the validation of required fields 
	 * Returns validation string if exist otherwise returns null in XML format
	 * @param currFormDataDom contains the current form data as a document object model
	 * @param hdrDataDom contains all the header information
	 * @param allFormDataDom contains the field data of all the forms 
	 * @param objContext represents form number
	 * @param currentColumn represents the current field 
	 * @param editFlag represents the mode of transaction(A-Add or E-Edit)
	 * @param xtraParams contains additional information
	 */
	@Override
	public String itemChanged( Document currFormDataDom, Document hdrDataDom, Document allFormDataDom, String objContext, String currentColumn,String editFlag, String xtraParams ) throws RemoteException,ITMException
	{	
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs =null;		
		HashMap itmVolumeMap = new HashMap();
		StringBuffer valueXmlString;
		Date tranDateD = null,statusDate = null;
		String retString = "",sql = "",tranId = "" , tranDate = "",siteCode = "", siteCodeDescr = "" ,confirmed = "";		
		String remarks = "" , chgUser = "",chgTerm = "",loginCode= "",loginEmpCode = "",loginSite = "",empCodeUser = "";		
		String siteCodeShip = "", waveId = "",refId = "",status = "",saleOrder = "",ptcnL = "" ;	
		int currentFormNo = 0;
		int multiCnt = 0;
		System.out.println("xtraParams=["+xtraParams+"]");
		System.out.println("MultiplePtcnWizCalled123........................... : ["+currentColumn+"]");
		int lineNoPickOrd = 0;
		DistCommon distCommon = new DistCommon();
		CommonWmsUtil CommonWmsUtilObj = CommonWmsUtil.getInstance();
		GenericUtility genericUtility = GenericUtility.getInstance();		

		valueXmlString = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root>\r\n<Header>\r\n<editFlag>").append( editFlag );

		try
		{
			Calendar currentDate = Calendar.getInstance();
			 SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
			 String currDate = sdf.format(currentDate.getTime());	
			DateFormat dtFormat = new SimpleDateFormat(genericUtility.getApplDateFormat());
			
			loginSite = checkNull(genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginSiteCode" ));			
			loginEmpCode = checkNull(genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginEmpCode"));	
			chgUser = checkNull(genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginCode"));
			chgTerm = checkNull(genericUtility.getValueFromXTRA_PARAMS(xtraParams, "termId"));
			String dbDateFormat = genericUtility.getDBDateFormat();
			String applDateFormat = genericUtility.getApplDateFormat();
			System.out.println("loginEmpCode---->>["+loginEmpCode+"] chgUser---->>["+chgUser+"]");	
			
			ConnDriver connDriver = new ConnDriver();
			conn = connDriver.getConnectDB( "DriverITM" );	
			connDriver = null;

			if( objContext != null && objContext.trim().length() > 0 )
			{
				currentFormNo = Integer.parseInt( objContext );
			}
			System.out.println("current form no["+currentFormNo+"]");
			valueXmlString.append("</editFlag>\r\n</Header>\r\n");
			switch ( currentFormNo )  
			{
			case 1:
			{
				//valueXmlString.append( "<Detail1 domID='1'>\r\n" );

				if( currentColumn.trim().equalsIgnoreCase( "itm_default" ) )
				{
					System.out.println("in itm_default.........."+loginSite);
					sql = "SELECT TRAN_ID,TRAN_DATE,SITE_CODE,CONFIRMED,REMARKS,EMP_CODE__USER,STATUS,STATUS_DATE FROM MULTI_PICK_ISS "
							+ "WHERE CHG_USER = ? AND SITE_CODE = ? "
							+ "AND CASE WHEN CONFIRMED IS NULL THEN 'N' ELSE CONFIRMED END = 'N'";
					
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,chgUser);
					pstmt.setString(2,loginSite);
					rs = pstmt.executeQuery();
					if(rs.next())
					{						
						tranId = checkNull(rs.getString("TRAN_ID"));
						tranDateD = rs.getDate("TRAN_DATE");						
						siteCode = checkNull(rs.getString("SITE_CODE"));
						confirmed = checkNull(rs.getString("CONFIRMED"));
						remarks = checkNull(rs.getString("REMARKS"));
						empCodeUser = checkNull(rs.getString("EMP_CODE__USER"));
						status = checkNull(rs.getString("STATUS"));
						statusDate = rs.getDate("STATUS_DATE");
						
						
						valueXmlString.append("<Detail1 domID='1' selected=\"Y\">\r\n");
						valueXmlString.append("<attribute selected=\"Y\" updateFlag=\"E\" status=\"O\" pkNames=\"\" />\r\n"); 
						valueXmlString.append( "<tran_id><![CDATA[" ).append( tranId ).append( "]]></tran_id>\r\n" );
						valueXmlString.append( "<tran_date><![CDATA[" ).append( sdf.format(tranDateD).toString() ).append( "]]></tran_date>\r\n" );
						valueXmlString.append( "<site_code><![CDATA[" ).append( siteCode ).append( "]]></site_code>\r\n" );
						siteCodeDescr=checkNull(getColumnDescr(conn, "descr", "site", "site_code", siteCode));
						valueXmlString.append( "<site_descr><![CDATA[" ).append( siteCodeDescr ).append( "]]></site_descr>\r\n" );
						
						valueXmlString.append( "<status><![CDATA[" ).append( status ).append( "]]></status>\r\n" );
						valueXmlString.append( "<status_date><![CDATA[" ).append( sdf.format(statusDate).toString() ).append( "]]></status_date>\r\n" );
						valueXmlString.append( "<confirmed><![CDATA[" ).append( confirmed ).append( "]]></confirmed>\r\n" );
						valueXmlString.append( "<remarks><![CDATA[" ).append( remarks ).append( "]]></remarks>\r\n" );
						valueXmlString.append( "<emp_code__user><![CDATA[" ).append( empCodeUser ).append( "]]></emp_code__user>\r\n" );
						valueXmlString.append( "<chg_user><![CDATA[" ).append( chgUser ).append( "]]></chg_user>\r\n" );
						valueXmlString.append( "<chg_date><![CDATA[" ).append( currDate ).append( "]]></chg_date>\r\n" );
						valueXmlString.append( "<chg_term><![CDATA[" ).append( chgTerm ).append( "]]></chg_term>\r\n" );
					}
					else
					{
						valueXmlString.append("<Detail1 domID='1' selected=\"N\">\r\n");
						valueXmlString.append("<attribute selected=\"N\" updateFlag=\"A\" status=\"N\" pkNames=\"\" />\r\n");
						valueXmlString.append( "<tran_id><![CDATA[" ).append( "" ).append( "]]></tran_id>\r\n" );
						valueXmlString.append( "<tran_date><![CDATA[" ).append( currDate ).append( "]]></tran_date>\r\n" );
						valueXmlString.append( "<site_code><![CDATA[" ).append( loginSite ).append( "]]></site_code>\r\n" );
						siteCodeDescr=checkNull(getColumnDescr(conn, "descr", "site", "site_code", loginSite));
						valueXmlString.append( "<site_descr><![CDATA[" ).append( siteCodeDescr ).append( "]]></site_descr>\r\n" );
						valueXmlString.append( "<status><![CDATA[" ).append( "O" ).append( "]]></status>\r\n" );
						valueXmlString.append( "<status_date><![CDATA[" ).append( currDate ).append( "]]></status_date>\r\n" );
						
						valueXmlString.append( "<confirmed><![CDATA[" ).append( "N" ).append( "]]></confirmed>\r\n" );
						valueXmlString.append( "<remarks><![CDATA[" ).append( "" ).append( "]]></remarks>\r\n" );
						valueXmlString.append( "<emp_code__user><![CDATA[" ).append( loginEmpCode ).append( "]]></emp_code__user>\r\n" );
						valueXmlString.append( "<chg_user><![CDATA[" ).append( chgUser ).append( "]]></chg_user>\r\n" );
						valueXmlString.append( "<chg_date><![CDATA[" ).append( currDate ).append( "]]></chg_date>\r\n" );
						valueXmlString.append( "<chg_term><![CDATA[" ).append( chgTerm ).append( "]]></chg_term>\r\n" );
					}
					
					if(rs != null)
					{
						rs.close();
						rs = null;
					}
					if(pstmt != null)
					{
						pstmt.close();
						pstmt = null;
					}
					
					
				}
				valueXmlString.append("</Detail1>\r\n");
			} //Case 1. End
			break;
			case 2:
			{
				System.out.println("inside case 2 itemchange");
				//valueXmlString.append( "<Detail2 domID='1'>\r\n" );
				tranId = checkNull(genericUtility.getColumnValue( "tran_id", hdrDataDom ));				
				System.out.println("Tran_id123@----------->>["+tranId+"]");		
				System.out.println("hdrDataDom------->>["+genericUtility.serializeDom(hdrDataDom)+"]");	
				System.out.println("currFormDataDom------>>["+genericUtility.serializeDom(currFormDataDom)+"]");
				System.out.println("allFormDataDom------>>["+genericUtility.serializeDom(allFormDataDom)+"]");
				
				
				
				if( currentColumn.trim().equalsIgnoreCase( "itm_default" ) )
				{
					String lineNoStr = "";					
					System.out.println("in itm_default@@@@@.........."+tranId);
					if(tranId.length() > 0)
					{						
						sql = "SELECT LINE_NO,WAVE_ID,SALE_ORDER,PTCN FROM "
								+ "MULTI_PICK_ISS_HDR WHERE TRAN_ID = ?";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, tranId);
						rs = pstmt.executeQuery();
						while(rs.next())
						{							
							lineNoStr = checkNull(rs.getString("LINE_NO"));
							waveId = checkNull(rs.getString("WAVE_ID"));							
							saleOrder = checkNull(rs.getString("SALE_ORDER"));
							ptcnL = checkNull(rs.getString("PTCN"));
							valueXmlString.append("<Detail2 domID='"+lineNoStr+"' selected=\"Y\">\r\n");
							valueXmlString.append("<attribute selected=\"Y\" updateFlag=\"E\" status=\"O\" pkNames=\"\" />\r\n"); 
							valueXmlString.append( "<tran_id><![CDATA[" ).append( tranId ).append( "]]></tran_id>\r\n" );
							valueXmlString.append( "<line_no><![CDATA[" ).append( lineNoStr ).append( "]]></line_no>\r\n" );
							valueXmlString.append( "<ptcn><![CDATA[" ).append( ptcnL ).append( "]]></ptcn>\r\n" );
							valueXmlString.append( "<sale_order><![CDATA[" ).append( saleOrder ).append( "]]></sale_order>\r\n" );
							valueXmlString.append( "<wave_id><![CDATA[" ).append( waveId ).append( "]]></wave_id>\r\n" );
							valueXmlString.append("</Detail2>\r\n");
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						
					}
					else
					{
						System.out.println("----------in else statement------------------");						
						sql = "SELECT D.LINE_NO,D.WAVE_ID,D.SALE_ORDER,D.PTCN FROM MULTI_PICK_ISS H,MULTI_PICK_ISS_HDR D"
							+ "  WHERE H.TRAN_ID = D.TRAN_ID AND H.CHG_USER =  ? "
							+ "AND CASE WHEN H.CONFIRMED IS NULL THEN 'N' ELSE H.CONFIRMED END = 'N'";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, chgUser);
						rs = pstmt.executeQuery();
						while(rs.next())
						{
							multiCnt++;							
							lineNoStr = checkNull(rs.getString("LINE_NO"));
							waveId = checkNull(rs.getString("WAVE_ID"));							
							saleOrder = checkNull(rs.getString("SALE_ORDER"));
							ptcnL = checkNull(rs.getString("PTCN"));
							valueXmlString.append("<Detail2 domID='"+lineNoStr+"' selected=\"Y\">\r\n");
							valueXmlString.append("<attribute selected=\"Y\" updateFlag=\"E\" status=\"O\" pkNames=\"\" />\r\n"); 
							valueXmlString.append( "<tran_id><![CDATA[" ).append( tranId ).append( "]]></tran_id>\r\n" );
							valueXmlString.append( "<line_no><![CDATA[" ).append( lineNoStr ).append( "]]></line_no>\r\n" );
							valueXmlString.append( "<ptcn><![CDATA[" ).append( ptcnL ).append( "]]></ptcn>\r\n" );
							valueXmlString.append( "<sale_order><![CDATA[" ).append( saleOrder ).append( "]]></sale_order>\r\n" );
							valueXmlString.append( "<wave_id><![CDATA[" ).append( waveId ).append( "]]></wave_id>\r\n" );
							valueXmlString.append("</Detail2>\r\n");
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						System.out.println("multiCnt----->>"+multiCnt);	
						if(multiCnt == 0)
						{
							//valueXmlString.append("<Detail2 domID='1' selected=\"N\">\r\n");
							//valueXmlString.append("<attribute selected=\"N\" updateFlag=\"A\" status=\"N\" pkNames=\"\" />\r\n");
							//valueXmlString.append( "<tran_id><![CDATA[" ).append( tranId ).append( "]]></tran_id>\r\n" );
							//valueXmlString.append( "<ptcn><![CDATA[" ).append( ptcnL ).append( "]]></ptcn>\r\n" );
							/*valueXmlString.append( "<line_no><![CDATA[" ).append( lineNo ).append( "]]></line_no>\r\n" );
							
							valueXmlString.append( "<sale_order><![CDATA[" ).append( saleOrder ).append( "]]></sale_order>\r\n" );
							valueXmlString.append( "<wave_id><![CDATA[" ).append( waveId ).append( "]]></wave_id>\r\n" );*/
							//valueXmlString.append("</Detail2>\r\n");
						}
						
					}
					
					sql = "";					
					
				}
				if( currentColumn.trim().equalsIgnoreCase( "ptcn" ) )
				{	
					System.out.println("------------PTCN item change@@@------------------");	
					System.out.println("hdrDataDom PTCN------->>["+genericUtility.serializeDom(hdrDataDom)+"]");	
					System.out.println("currFormDataDom PTCN------>>["+genericUtility.serializeDom(currFormDataDom)+"]");
					System.out.println("allFormDataDom PTCN------>>["+genericUtility.serializeDom(allFormDataDom)+"]");
									
					int lineNoL = 0 ;
					
					String ptcn = checkNull(genericUtility.getColumnValue( "ptcn", allFormDataDom ));	
					tranId = checkNull(genericUtility.getColumnValue( "tran_id", hdrDataDom ));	
					String line_no = checkNull(genericUtility.getColumnValue( "line_no", allFormDataDom ));
					String line_no1 = checkNull(genericUtility.getColumnValue( "line_no", currFormDataDom ));
					System.out.println("line_no----->>"+line_no+" line_no1:["+line_no1+"]");
					System.out.println("in ptcn item change.............. : "+ptcn+" ["+tranId+"]");
					sql = "SELECT WAVE_ID,REF_ID,STATUS,SALE_ORDER FROM WAVE_TASK_DET WHERE PTCN = ?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, ptcn);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						waveId = checkNull(rs.getString("WAVE_ID"));
						refId = checkNull(rs.getString("REF_ID"));
						status = checkNull(rs.getString("STATUS"));
						saleOrder = checkNull(rs.getString("SALE_ORDER"));						
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					if(tranId.length() > 0 && line_no1.length() == 0)
					{
						System.out.println("tranId.length() > 0 && line_no1.length() == 0----->>"+line_no1);	
						int lineNoMax = getMaxLineNo(tranId,conn);
						lineNoMax++;
						String sale_order = checkNull(genericUtility.getColumnValue( "sale_order", allFormDataDom ));
						String wave_id = checkNull(genericUtility.getColumnValue( "wave_id", allFormDataDom ));
						
						System.out.println("sale_order----->>"+sale_order);	
						System.out.println("wave_id----->>"+wave_id);	
							
							
						valueXmlString.append("<Detail2 domID='"+lineNoMax+"' selected=\"N\">\r\n");
						valueXmlString.append("<attribute selected=\"N\" updateFlag=\"A\" status=\"N\" pkNames=\"\" />\r\n");
						valueXmlString.append( "<tran_id><![CDATA[" ).append( tranId ).append( "]]></tran_id>\r\n" );
						valueXmlString.append( "<line_no><![CDATA[" ).append( lineNoMax ).append( "]]></line_no>\r\n" );
						valueXmlString.append( "<ptcn><![CDATA[" ).append(ptcn ).append( "]]></ptcn>\r\n" );					
						valueXmlString.append( "<sale_order><![CDATA[" ).append( saleOrder).append( "]]></sale_order>\r\n" );
						valueXmlString.append( "<wave_id><![CDATA[" ).append( waveId).append( "]]></wave_id>\r\n" );
						valueXmlString.append("</Detail2>\r\n");
					}
					else if(tranId.length() > 0 && line_no1.length() > 0)
					{
						System.out.println("tranId.length() > 0 && line_no1.length() > 0----->>"+line_no1);	
						int lineNoMax = Integer.parseInt(line_no1);
						//valueXmlString.append("<Detail2 domID='"+lineNoMax+"' selected=\"N\">\r\n");
						//valueXmlString.append("<attribute selected=\"N\" updateFlag=\"A\" status=\"N\" pkNames=\"\" />\r\n");
						valueXmlString.append("<Detail2 domID='"+lineNoMax+"' selected=\"Y\">\r\n");
						valueXmlString.append("<attribute selected=\"Y\" updateFlag=\"E\" status=\"O\" pkNames=\"\" />\r\n"); 
						valueXmlString.append( "<tran_id><![CDATA[" ).append( tranId ).append( "]]></tran_id>\r\n" );
						valueXmlString.append( "<line_no><![CDATA[" ).append( lineNoMax ).append( "]]></line_no>\r\n" );
						valueXmlString.append( "<ptcn><![CDATA[" ).append(ptcn ).append( "]]></ptcn>\r\n" );					
						valueXmlString.append( "<sale_order><![CDATA[" ).append( saleOrder).append( "]]></sale_order>\r\n" );
						valueXmlString.append( "<wave_id><![CDATA[" ).append( waveId).append( "]]></wave_id>\r\n" );
						valueXmlString.append("</Detail2>\r\n");
					}
					else
					{
						//lineNoL = getLineNo(allFormDataDom);
						lineNoL = getMaxLineNo(conn,chgUser);
						lineNoL++;
						System.out.println("lineNoL in getLineNo----->>"+lineNoL);					
						
						valueXmlString.append("<Detail2 domID='"+lineNoL+"' selected=\"N\">\r\n");
						valueXmlString.append("<attribute selected=\"N\" updateFlag=\"A\" status=\"N\" pkNames=\"\" />\r\n");						
						valueXmlString.append( "<tran_id><![CDATA[" ).append( tranId ).append( "]]></tran_id>\r\n" );
						valueXmlString.append( "<line_no><![CDATA[" ).append( lineNoL ).append( "]]></line_no>\r\n" );
						valueXmlString.append( "<ptcn><![CDATA[" ).append(ptcn ).append( "]]></ptcn>\r\n" );					
						valueXmlString.append( "<sale_order><![CDATA[" ).append( saleOrder).append( "]]></sale_order>\r\n" );
						valueXmlString.append( "<wave_id><![CDATA[" ).append( waveId).append( "]]></wave_id>\r\n" );
						valueXmlString.append("</Detail2>\r\n");
					}
				
				}
				if( currentColumn.trim().equalsIgnoreCase( "itm_default_add" ) )
				{
					System.out.println("itm_default_add item change22..............");
					System.out.println("allFormDataDom@@------->>["+genericUtility.serializeDom(allFormDataDom)+"]");
					NodeList nodeList = allFormDataDom.getElementsByTagName("Detail2");
					System.out.println("sds : "+nodeList.item(0).getNodeName());
					String domIdLL =  nodeList.item(0).getAttributes().getNamedItem("domID").getNodeValue();
					System.out.println("domIdLL=="+domIdLL);
					
					
					tranId = checkNull(genericUtility.getColumnValue( "tran_id", hdrDataDom ));	
					int lineNoL = 0, lineNoMax = 0 ;
					if(tranId.length() > 0)
					{
						 lineNoMax = getMaxLineNo(tranId,conn);
						 lineNoMax++;
						 valueXmlString.append("<Detail2 domID='"+lineNoMax+"' selected=\"N\">\r\n");
						 valueXmlString.append("<attribute selected=\"N\" updateFlag=\"A\" status=\"N\" pkNames=\"\" />\r\n");
						 valueXmlString.append( "<tran_id><![CDATA[" ).append( tranId ).append( "]]></tran_id>\r\n" );
						 valueXmlString.append( "<line_no><![CDATA[" ).append( lineNoMax ).append( "]]></line_no>\r\n" );
						 valueXmlString.append( "<ptcn><![CDATA[]]></ptcn>\r\n" );
						 valueXmlString.append("</Detail2>\r\n");
					}	
					else
					{
						//lineNoMax = getLineNo(allFormDataDom);
						lineNoMax = getMaxLineNo(conn,chgUser);
						lineNoMax++;
						System.out.println("lineNoMax@----->>"+lineNoMax);						
						valueXmlString.append("<Detail2 domID='"+lineNoMax+"' selected=\"N\">\r\n");
						valueXmlString.append("<attribute selected=\"N\" updateFlag=\"A\" status=\"N\" pkNames=\"\" />\r\n");
						valueXmlString.append( "<ptcn><![CDATA[]]></ptcn>\r\n" );
						//valueXmlString.append( "<tran_id><![CDATA[" ).append( tranId ).append( "]]></tran_id>\r\n" );
						valueXmlString.append( "<line_no><![CDATA[" ).append( lineNoMax ).append( "]]></line_no>\r\n" );
						 valueXmlString.append("</Detail2>\r\n");
					}

				}
				
				
			} //Case 2. End
			break;
			case 3://Case 3. Start
			{
				System.out.println("item change form 3........");
				ArrayList<String> ptcnList = new ArrayList<String>();
				ArrayList<String> detailList = null;
				
				String pickOrder = "" ,saleOrderL = "",itemCode = "" ,locCode = "" ,lotNo = "" ,lotSl = "",subSql = "",
						ptcnNo = "" ,mark = "",cartonNo = "",itemDescr = "",outerCartenNo = "",sorderLine = "",stkOpt = "";
				int noArt = 0,count = 0,domId = 0,pstmtCount=0 ;
				double quantity = 0,deAllQuantity = 0;
				tranId = checkNull(genericUtility.getColumnValue( "tran_id", hdrDataDom ));	
				String qty = checkNull(genericUtility.getColumnValue( "quantity", allFormDataDom ));
				String qty1 = checkNull(genericUtility.getColumnValue( "quantity", currFormDataDom ));
				System.out.println("allFormDataDom qty........:"+qty);
				System.out.println("currFormDataDom qty1........:"+qty1);
				System.out.println("hdrDataDom------->>["+genericUtility.serializeDom(hdrDataDom)+"]");	
				System.out.println("currFormDataDom------>>["+genericUtility.serializeDom(currFormDataDom)+"]");
				System.out.println("allFormDataDom------>>["+genericUtility.serializeDom(allFormDataDom)+"]");
				if( currentColumn.trim().equalsIgnoreCase( "itm_default" ) )
				{
					System.out.println("item change form 3........itm_default");
					ptcnList = getPtcnNo(allFormDataDom, "2",tranId,conn);
					System.out.println("ptcnList@----->"+ptcnList);					
					for(String temp : ptcnList)
					{
						subSql = subSql + ","+temp;
					}
					if(subSql !=null && subSql.length() > 0)
					{
						subSql = subSql.substring(1,subSql.length());
					}
						
					System.out.println("@@@ sql in @@@ :["+subSql+"]");		
					StringBuffer sqlBuf = new StringBuffer();					
					sqlBuf.append("SELECT W.PTCN, PODET.SALE_ORDER,PODET.LINE_NO__SORD,PODET.PICK_ORDER,PODET.LINE_NO,PODET.ITEM_CODE,PODET.SITE_CODE,PODET.LOC_CODE,PODET.LOT_NO, "
							+ "PODET.LOT_SL, PODET.NO_ART, (PODET.QUANTITY - (CASE WHEN PODET.DEALLOC_QTY IS NULL THEN 0 ELSE PODET.DEALLOC_QTY END )) AS "
							+ "QUANTITY ,PODET.CARTON_NO, PODET.DEALLOC_QTY,PODET.PACK_CODE, ITEM.DESCR ITEM_DESCR, ITEM.UNIT UNIT, LOC_CODE__TO  "
							+ "FROM PICK_ORD_DET PODET, ITEM ITEM,WAVE_TASK_DET W WHERE W.PTCN IN (");
							

					for(String temp : ptcnList)
					{
						mark = mark + "?,";
					}
					if(mark !=null && mark.length() > 0)
					{
						mark = mark.substring(0,mark.length() - 1);
					}
					
					sqlBuf.append(mark);
					sqlBuf.append( " ) AND W.STATUS = 'N' AND W.WAVE_STATUS = 'C'  AND W.REF_SER  IN ('A-PICK','C-PICK','M-PICK') ");
					sqlBuf.append(" AND PODET.ITEM_CODE = ITEM.ITEM_CODE ");
					sqlBuf.append("AND PODET.PICK_ORDER = W.REF_ID  AND (PODET.QUANTITY - (CASE WHEN PODET.DEALLOC_QTY IS NULL THEN 0 ELSE PODET.DEALLOC_QTY END )) > 0 ");
					sqlBuf.append("ORDER BY PODET.LOC_CODE, PODET.ITEM_CODE");				
							
					
					
					System.out.println("sqlBuf : "+sqlBuf.toString());	
					pstmt = conn.prepareStatement(sqlBuf.toString());
					for(String temp : ptcnList)
					{
						pstmt.setString(++pstmtCount,temp);						
					}
					rs =pstmt.executeQuery();
					
					if(tranId.length() > 0)
					{
						System.out.println("--------------Edit mode----------------------");						
						while(rs.next())
						{
							detailList = new ArrayList<String>();
							domId++;
							ptcnNo = checkNull(rs.getString("PTCN"));
							saleOrderL = checkNull(rs.getString("SALE_ORDER"));
							pickOrder = checkNull(rs.getString("PICK_ORDER"));
							lineNoPickOrd = rs.getInt("LINE_NO");
							itemCode = checkNull(rs.getString("ITEM_CODE"));
							siteCode = checkNull(rs.getString("SITE_CODE"));
							locCode = checkNull(rs.getString("LOC_CODE"));
							stkOpt = CommonWmsUtilObj.getStockOption(itemCode, conn);
							System.out.println("Edit Mode itemCode--->>["+itemCode+" stkOpt-->["+stkOpt+"]");							
							if("2".equalsIgnoreCase(stkOpt))							
							{
								lotNo = checkNull(rs.getString("LOT_NO"));
								lotSl = checkNull(rs.getString("LOT_SL"));
							}
							else
							{
								lotNo = rs.getString("LOT_NO");
								lotSl = rs.getString("LOT_SL");
							}
							System.out.println("lotNo----->>["+lotNo+"] lotSl--->["+lotSl+"]");							
							noArt = rs.getInt("NO_ART");
							quantity = rs.getDouble("QUANTITY");
							cartonNo = checkNull(rs.getString("CARTON_NO"));
							deAllQuantity = rs.getDouble("DEALLOC_QTY");
							sorderLine = checkNull(rs.getString("LINE_NO__SORD"));

							/*if(ptcnList !=null && ptcnList.size() > 0)
							{
								ptcnNo = ptcnList.get(count);
							}*/													
							count++;
							
							int cnt = getDBRowCount(conn, "MULTI_PICK_ISS_DET", "TRAN_ID", tranId);
							System.out.println("MULTI_PICK_ISS_DET cnt1----->>["+cnt+"]");
							if(cnt==0)
							{
								valueXmlString.append("<Detail3 domID='"+domId+"' selected=\"N\">\r\n");
								valueXmlString.append("<attribute selected=\"N\" updateFlag=\"A\" status=\"N\" pkNames=\"\" />\r\n");
							}
							else
							{								
								if(isScanningDone(siteCode,saleOrderL,ptcnNo,itemCode,pickOrder,locCode,lotNo,tranId,conn))
								{
									valueXmlString.append("<Detail3 domID='"+domId+"' selected=\"Y\">\r\n");
									valueXmlString.append("<attribute selected=\"Y\" updateFlag=\"E\" status=\"O\" pkNames=\"\" />\r\n");
								}
								else
								{
									valueXmlString.append("<Detail3 domID='"+domId+"' selected=\"N\">\r\n");
									valueXmlString.append("<attribute selected=\"N\" updateFlag=\"E\" status=\"O\" pkNames=\"\" />\r\n");
								}
								
							}
							//valueXmlString.append("<Detail3 domID='"+domId+"' selected=\"N\">\r\n");
							//valueXmlString.append("<attribute selected=\"N\" updateFlag=\"A\" status=\"N\" pkNames=\"\" />\r\n");
							//valueXmlString.append("<attribute selected=\"Y\" updateFlag=\"E\" status=\"O\" pkNames=\"\" />\r\n");

							valueXmlString.append( "<tran_id><![CDATA[" ).append( "").append( "]]></tran_id>\r\n" );
							valueXmlString.append( "<line_no><![CDATA[" ).append( domId).append( "]]></line_no>\r\n" );
							/*valueXmlString.append( "<site_code><![CDATA[" ).append( siteCode).append( "]]></site_code>\r\n" );
							siteCodeDescr = checkNull(getColumnDescr(conn, "descr", "site", "site_code", siteCode));
							valueXmlString.append( "<site_descr><![CDATA[" ).append( siteCodeDescr).append( "]]></site_descr>\r\n" );
							valueXmlString.append( "<ptcn><![CDATA[" ).append(ptcnNo ).append( "]]></ptcn>\r\n" );
							valueXmlString.append( "<pick_order><![CDATA[" ).append( pickOrder).append( "]]></pick_order>\r\n" );
							valueXmlString.append( "<sale_order><![CDATA[" ).append( saleOrderL).append( "]]></sale_order>\r\n" );
							valueXmlString.append( "<line_no__sord><![CDATA[" ).append( sorderLine).append( "]]></line_no__sord>\r\n" );
							
							valueXmlString.append( "<item_code><![CDATA[" ).append( itemCode).append( "]]></item_code>\r\n" );
							 itemDescr = checkNull(getColumnDescr(conn, "descr", "item", "item_code", itemCode));
							valueXmlString.append( "<item_descr><![CDATA[" ).append( itemDescr).append( "]]></item_descr>\r\n" );
							valueXmlString.append( "<lot_no><![CDATA[" ).append( lotNo).append( "]]></lot_no>\r\n" );
							valueXmlString.append( "<lot_sl><![CDATA[" ).append( lotSl).append( "]]></lot_sl>\r\n" );
							valueXmlString.append( "<quantity><![CDATA[" ).append( quantity).append( "]]></quantity>\r\n" );
							valueXmlString.append( "<loc_code><![CDATA[" ).append( locCode).append( "]]></loc_code>\r\n" );*/
							
							
							valueXmlString.append( "<site_code><![CDATA[" ).append( siteCode).append( "]]></site_code>\r\n" );
							siteCodeDescr = checkNull(getColumnDescr(conn, "descr", "site", "site_code", siteCode));
							valueXmlString.append( "<site_descr><![CDATA[" ).append( siteCodeDescr).append( "]]></site_descr>\r\n" );
							valueXmlString.append( "<ptcn><![CDATA[" ).append(ptcnNo ).append( "]]></ptcn>\r\n" );
							valueXmlString.append( "<pick_order><![CDATA[" ).append( pickOrder).append( "]]></pick_order>\r\n" );
							valueXmlString.append( "<sale_order><![CDATA[" ).append( saleOrderL).append( "]]></sale_order>\r\n" );
							valueXmlString.append( "<item_code><![CDATA[" ).append( itemCode).append( "]]></item_code>\r\n" );
							 itemDescr = checkNull(getColumnDescr(conn, "descr", "item", "item_code", itemCode));
							valueXmlString.append( "<item_descr><![CDATA[" ).append( itemDescr).append( "]]></item_descr>\r\n" );
							valueXmlString.append( "<lot_no><![CDATA[").append( lotNo).append("]]></lot_no>\r\n" );
							valueXmlString.append( "<lot_sl><![CDATA[").append( lotSl).append("]]></lot_sl>\r\n" );
							valueXmlString.append( "<quantity><![CDATA[" ).append( quantity).append( "]]></quantity>\r\n" );
							valueXmlString.append( "<loc_code><![CDATA[" ).append( locCode).append( "]]></loc_code>\r\n" );
							
							valueXmlString.append( "<no_art><![CDATA[" ).append( noArt == 0 ? "" : noArt).append( "]]></no_art>\r\n" );
							valueXmlString.append( "<line_no__sord><![CDATA[" ).append( sorderLine).append( "]]></line_no__sord>\r\n" );
							valueXmlString.append( "<pick_ord__line_no><![CDATA[" ).append( lineNoPickOrd).append( "]]></pick_ord__line_no>\r\n" );
							
							valueXmlString.append( "<carton_no><![CDATA[" ).append( cartonNo).append( "]]></carton_no>\r\n" );
							valueXmlString.append( "<dealloc_qty><![CDATA[" ).append( deAllQuantity).append( "]]></dealloc_qty>\r\n" );
							if(cartonNo.length() > 0)
							{
								outerCartenNo = checkNull(getColumnDescr(conn, "master_carton", "carton_master", "carton_no", cartonNo));
								valueXmlString.append( "<outer_carton_no><![CDATA[" ).append( outerCartenNo).append( "]]></outer_carton_no>\r\n" );
							}
							valueXmlString.append("</Detail3>\r\n");
						}
					}
					else
					{
						System.out.println("--------------Add mode----------------------");
						while(rs.next()) 
						{
							domId++;
							ptcnNo = checkNull(rs.getString("PTCN"));
							saleOrderL = checkNull(rs.getString("SALE_ORDER"));
							pickOrder = checkNull(rs.getString("PICK_ORDER"));
							lineNoPickOrd = rs.getInt("LINE_NO");
							itemCode = checkNull(rs.getString("ITEM_CODE"));
							siteCode = checkNull(rs.getString("SITE_CODE"));
							locCode = checkNull(rs.getString("LOC_CODE"));
							stkOpt = CommonWmsUtilObj.getStockOption(itemCode, conn);
							System.out.println("ADD Mode itemCode--->>["+itemCode+" stkOpt-->["+stkOpt+"]");							
							if("2".equalsIgnoreCase(stkOpt))							
							{
								lotNo = checkNull(rs.getString("LOT_NO"));
								lotSl = checkNull(rs.getString("LOT_SL"));
							}
							else
							{
								lotNo = rs.getString("LOT_NO");
								lotSl = rs.getString("LOT_SL");
							}
							System.out.println("lotNo----->>["+lotNo+"] lotSl--->["+lotSl+"]");
							noArt = rs.getInt("NO_ART");
							quantity = rs.getDouble("QUANTITY");
							cartonNo = checkNull(rs.getString("CARTON_NO"));
							deAllQuantity = rs.getDouble("DEALLOC_QTY");
							sorderLine = checkNull(rs.getString("LINE_NO__SORD"));
							/*if(ptcnList !=null && ptcnList.size() > 0)
							{
								ptcnNo = ptcnList.get(count);
							}*/													
							count++;						
							
							if(isScanningDone(siteCode,saleOrderL,ptcnNo,itemCode,pickOrder,locCode,lotNo,tranId,conn))
							{
								valueXmlString.append("<Detail3 domID='"+domId+"' selected=\"Y\">\r\n");
								valueXmlString.append("<attribute selected=\"Y\" updateFlag=\"E\" status=\"O\" pkNames=\"\" />\r\n");
							}
							else
							{
								valueXmlString.append("<Detail3 domID='"+domId+"' selected=\"N\">\r\n");
								valueXmlString.append("<attribute selected=\"N\" updateFlag=\"A\" status=\"N\" pkNames=\"\" />\r\n");
							}
							

							valueXmlString.append( "<tran_id><![CDATA[" ).append( "").append( "]]></tran_id>\r\n" );
							valueXmlString.append( "<line_no><![CDATA[" ).append( domId).append( "]]></line_no>\r\n" );
							valueXmlString.append( "<site_code><![CDATA[" ).append( siteCode).append( "]]></site_code>\r\n" );
							siteCodeDescr = checkNull(getColumnDescr(conn, "descr", "site", "site_code", siteCode));
							valueXmlString.append( "<site_descr><![CDATA[" ).append( siteCodeDescr).append( "]]></site_descr>\r\n" );
							valueXmlString.append( "<ptcn><![CDATA[" ).append(ptcnNo ).append( "]]></ptcn>\r\n" );
							valueXmlString.append( "<pick_order><![CDATA[" ).append( pickOrder).append( "]]></pick_order>\r\n" );
							valueXmlString.append( "<sale_order><![CDATA[" ).append( saleOrderL).append( "]]></sale_order>\r\n" );
							valueXmlString.append( "<item_code><![CDATA[" ).append( itemCode).append( "]]></item_code>\r\n" );
							 itemDescr = checkNull(getColumnDescr(conn, "descr", "item", "item_code", itemCode));
							valueXmlString.append( "<item_descr><![CDATA[" ).append( itemDescr).append( "]]></item_descr>\r\n" );
							valueXmlString.append( "<lot_no><![CDATA[" ).append( lotNo).append( "]]></lot_no>\r\n" );
							valueXmlString.append( "<lot_sl><![CDATA[" ).append( lotSl).append( "]]></lot_sl>\r\n" );
							valueXmlString.append( "<quantity><![CDATA[" ).append( quantity).append( "]]></quantity>\r\n" );
							valueXmlString.append( "<loc_code><![CDATA[" ).append( locCode).append( "]]></loc_code>\r\n" );
							
							valueXmlString.append( "<no_art><![CDATA[" ).append( noArt == 0 ? "" : noArt).append( "]]></no_art>\r\n" );
							valueXmlString.append( "<line_no__sord><![CDATA[" ).append( sorderLine).append( "]]></line_no__sord>\r\n" );
							valueXmlString.append( "<pick_ord__line_no><![CDATA[" ).append( lineNoPickOrd).append( "]]></pick_ord__line_no>\r\n" );
							
							valueXmlString.append( "<carton_no><![CDATA[" ).append( cartonNo).append( "]]></carton_no>\r\n" );
							valueXmlString.append( "<dealloc_qty><![CDATA[" ).append( deAllQuantity).append( "]]></dealloc_qty>\r\n" );
							if(cartonNo.length() > 0)
							{
								outerCartenNo = checkNull(getColumnDescr(conn, "master_carton", "carton_master", "carton_no", cartonNo));
								valueXmlString.append( "<outer_carton_no><![CDATA[" ).append( outerCartenNo).append( "]]></outer_carton_no>\r\n" );
							}

							valueXmlString.append("</Detail3>\r\n");
						}
					}

				}
				if( currentColumn.trim().equalsIgnoreCase( "itm_default_add" ) )
				{
					System.out.println("item change form 3........itm_default_add");
					valueXmlString.append("<Detail3 domID='1' selected=\"N\">\r\n");
					valueXmlString.append("<attribute selected=\"N\" updateFlag=\"A\" status=\"N\" pkNames=\"\" />\r\n");
					
					valueXmlString.append( "<tran_id><![CDATA[" ).append( "").append( "]]></tran_id>\r\n" );
					valueXmlString.append( "<line_no><![CDATA[" ).append( "").append( "]]></line_no>\r\n" );
					valueXmlString.append( "<site_code><![CDATA[" ).append( "").append( "]]></site_code>\r\n" );
					valueXmlString.append( "<ptcn><![CDATA[" ).append( "").append( "]]></ptcn>\r\n" );
					valueXmlString.append( "<pick_order><![CDATA[" ).append( "").append( "]]></pick_order>\r\n" );

					valueXmlString.append( "<item_code><![CDATA[" ).append( "").append( "]]></item_code>\r\n" );
					valueXmlString.append( "<lot_no><![CDATA[" ).append( "").append( "]]></lot_no>\r\n" );
					valueXmlString.append( "<lot_sl><![CDATA[" ).append( "").append( "]]></lot_sl>\r\n" );
					valueXmlString.append( "<quantity><![CDATA[" ).append( "").append( "]]></quantity>\r\n" );
					valueXmlString.append( "<loc_code><![CDATA[" ).append( "").append( "]]></loc_code>\r\n" );

					valueXmlString.append("</Detail3>\r\n");

				}


			} //Case 3. End
			break;
			case 4:
			{					

					if( currentColumn.trim().equalsIgnoreCase( "itm_default" ) )
					{
						
					}
					
			}//Case 4. End
			}//End of switch block
			valueXmlString.append( "</Root>\r\n" );	 
		}
		catch (Exception e)
		{				
			e.printStackTrace();			
			
		}
		finally
		{	
			try
			{				
				if ( conn != null )
				{
					conn.close();
					conn = null;
				}
			}
			catch (Exception e)
			{
				e.printStackTrace();
				throw new ITMException(e);
			}
		}
		System.out.println( "valueXmlString.toString()>>>>>>>::"+valueXmlString.toString());
		return valueXmlString.toString();
	}

	/**
	 * The function checks whether the input string is null. 
	 * If null, the function returns blank string.  
	 * @param input
	 */
	private String checkNull( String input )	
	{
		if ( input == null )
		{
			input = "";
		}
		return input.trim();
	}
	/**
	 * The function checks whether the input string is null. 
	 * If null, the function returns blank string otherwise trim the input string and return.  
	 * @param input
	 */
	private String checkNullAndTrim( String inputVal )
	{
		if ( inputVal == null )
		{
			inputVal = "";
		}
		else
		{
			inputVal = inputVal.trim();
		}
		return inputVal;
	}
	private int getLineNo(Document dom)
	{
		String lineNoStr = "",ptcn = "",sql = "";
		int lineNo = 0,totalNodeLength = 0;
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;
		int childNodeListLength;
		String childNodeName = null;
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		try
		{			
			/*DocumentBuilderFactory dbFactory = DocumentBuilderFactory.newInstance();
			DocumentBuilder dBuilder = dbFactory.newDocumentBuilder();
			Document doc = dBuilder.parse(dom);*/
			if(dom != null)
			{
				NodeList list = dom.getElementsByTagName("Detail2");
				totalNodeLength = list.getLength();
				for (int ctr = 0; ctr < list.getLength() ; ctr++)
				{
					childNode = list.item(ctr);			
					childNodeName = childNode.getNodeName();
					System.out.println("Child name --->> "+childNodeName);	

					if (childNode.getNodeType() == Node.ELEMENT_NODE)
					{
						Element eElement = (Element) childNode;					
						ptcn =  eElement.getElementsByTagName("ptcn").item(0).getTextContent();
						String ptcn1 =  eElement.getElementsByTagName("wave_id").item(0).getTextContent();
						System.out.println("ctr --->> "+ctr);	
						System.out.println("ptcn --->> "+ptcn);	
						System.out.println("ptcn1 --->> "+ptcn1);
					}
				}
				if(ptcn.length() == 0 && totalNodeLength == 1)
				{
					lineNo = 1;
				}
				else
				{
					System.out.println("else..........."+lineNo);
					lineNo = totalNodeLength;
				}
				System.out.println("Total of elements : " + list.getLength());
				System.out.println("New of lineNo : " + lineNo);
			}
			else
			{
				System.out.println("dom is null...................");
			}

		}
		catch(Exception e)
		{
			e.printStackTrace();

		}
		System.out.println("return from getLineNo@---->>["+lineNo+"]");
		
		return lineNo;
	}
	private boolean isExistPtcn(Document dom,Connection conn)
	{
		System.out.println("--------isExistPtcn --------");
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;
		int childNodeListLength = 0;
		String childNodeName = null,ptcn = "",sql = "";
		GenericUtility genericUtility = GenericUtility.getInstance();
		int count = 0;
		ResultSet rs= null;
		PreparedStatement pstmt = null;

		ArrayList<String> ptcnList = new ArrayList<String>();
		boolean isExist = false;
		try
		{
			parentNodeList = dom.getElementsByTagName("Detail2");
			childNodeListLength = parentNodeList.getLength();
			System.out.println("childNodeListLength----->>"+childNodeListLength);
			for (int ctr = 0; ctr < childNodeListLength; ctr++)
			{				
				childNode = parentNodeList.item(ctr);			
				childNodeName = childNode.getNodeName();
				System.out.println("Child name --->> "+childNodeName);	

				if (childNode.getNodeType() == Node.ELEMENT_NODE)
				{
					Element eElement = (Element) childNode;					
					ptcn =  eElement.getElementsByTagName("ptcn").item(0).getTextContent();
					System.out.println("ctr --->> "+ctr);	
					System.out.println("ptcn --->> "+ptcn);	
					ptcnList.add(ptcn);

				}

			} //end for 
			System.out.println("ptcnList in isExistPtcn@----->>"+ptcnList);
			if(ptcnList !=null && ptcnList.size() > 0)
			{
				Set<String> uniqueSet = new HashSet<String>(ptcnList);
				for (String temp : uniqueSet)
				{
					System.out.println(temp + ": " + Collections.frequency(ptcnList, temp));
					int i = Collections.frequency(ptcnList, temp);
					if(i > 1)
					{
						isExist = true;	
						break;
					}
				}
				if(!isExist)				
				{
					for (String tempPtcn : uniqueSet)
					{						
						sql = "SELECT COUNT(*) FROM  MULTI_PICK_ISS D,MULTI_PICK_ISS_HDR H WHERE PTCN = ? "
								+ " AND D.TRAN_ID = H.TRAN_ID AND D.CONFIRMED = ? ";
						pstmt = conn.prepareStatement(sql);						
						pstmt.setString(1, tempPtcn);
						pstmt.setString(2, "Y");						
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							count=rs.getInt(1);				
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						System.out.println("@@@@test :count::::["+count+"]");
						if(count > 0)
						{
							return true;

						}
					}

				}

			}

		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return isExist;
	}
	private int getMaxLineNo(String tranId,Connection conn)
	{
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		String sql = "",lineNoS = "";
		int lineNo = 0;
		try
		{
			sql ="SELECT MAX(LINE_NO) FROM MULTI_PICK_ISS_HDR WHERE TRAN_ID = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,tranId);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				lineNoS = checkNull(rs.getString(1));
			}
			rs.close();
			rs = null;	
			lineNoS = lineNoS.length() == 0 ? "0" :lineNoS;
			lineNo = Integer.parseInt(lineNoS);
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return lineNo;
	}
	public ArrayList<String> getPtcnNo(Document dom, String formNo,String tranId,Connection conn) 
	{
		ArrayList<String> colVal = new ArrayList<String>();		
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;
		int childNodeListLength = 0;
		String childNodeName = null,ptcn = "",oldPtcn = "",saleOrder = "",sql ="";
		GenericUtility genericUtility = GenericUtility.getInstance();
		String columnName = "";
		String columnValue = null;
		boolean continueLoop = true;
		boolean isFormNo = false;		
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		try
		{
			tranId = tranId == null ? "" : tranId.trim();
			System.out.println("tranId in detail3--------->>["+tranId+"]");			
			if(tranId.length() > 0)
			{
				sql = "SELECT PTCN FROM MULTI_PICK_ISS_HDR WHERE TRAN_ID = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, tranId);
				 rs = pstmt.executeQuery();
				 while(rs.next())
				 {					 
					 colVal.add(checkNull(rs.getString(1)));					 
				 }
				
			}
			else
			{
				if(dom != null)
				{
					parentNodeList = dom.getElementsByTagName("Detail2");
					childNodeListLength = parentNodeList.getLength();
					System.out.println("childNodeListLength----->>"+childNodeListLength);
					for (int ctr = 0; ctr < childNodeListLength; ctr++)
					{				
						childNode = parentNodeList.item(ctr);			
						childNodeName = childNode.getNodeName();
						System.out.println("Child name --->> "+childNodeName);	

						if (childNode.getNodeType() == Node.ELEMENT_NODE)
						{
							Element eElement = (Element) childNode;					
							ptcn =  checkNull(eElement.getElementsByTagName("ptcn").item(0).getTextContent());					
							saleOrder =  checkNull(eElement.getElementsByTagName("sale_order").item(0).getTextContent());					
							System.out.println("sale_order0----->"+saleOrder+ " PTCN --->["+ptcn+"]");					
							colVal.add(ptcn);
						}
					}
				}
				else
				{
					System.out.println("dom is null please check................");
				}
			}
			if(rs!=null)
			{
				rs.close();
				rs = null;
			}
			if(pstmt !=null)
			{
				pstmt.close();
				pstmt = null;
			}
			
		}
		catch(Exception e)
		{
			System.out.println("Exception : [getPtcnNo(2)] :==>\n"+e.getMessage());
			
		}
		System.out.println("Return List from getPtcnNo------>>"+colVal);		
		return colVal;
	}
	private int getMaxLineNo (Connection conn,String user) throws Exception
	{	
		
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		String sql = "",maxTranId = "",lineNoStr = "";
		int maxLineNo = 0;
		sql = "SELECT  MAX(TRAN_ID) FROM MULTI_PICK_ISS WHERE CHG_USER = ? ";
		pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, user);		
		rs= pstmt.executeQuery();
		if(rs.next())
		{
			maxTranId = checkNull(rs.getString(1));			
		}
		rs.close();
		rs = null;
		pstmt.close();
		pstmt = null;
		
		sql = "SELECT MAX(LINE_NO) AS LINE_MAX  FROM MULTI_PICK_ISS_HDR WHERE TRAN_ID = ? ";
		
		pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, maxTranId);		
		rs= pstmt.executeQuery();
		if(rs.next())
		{
			lineNoStr = checkNull(rs.getString(1));			
		}
		rs.close();
		rs = null;
		pstmt.close();
		pstmt = null;
		lineNoStr = lineNoStr.length() == 0 ? "0" : lineNoStr;
		
		maxLineNo = Integer.parseInt(lineNoStr);
		
	return maxLineNo;
	}
	private boolean isScanningDone(String siteCode,String saleOrderL,String ptcnNo,String itemCode,String pickOrder,String locCode,String lotNo,String tranId,Connection conn) throws ITMException,Exception
	{
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		String sql = "";
		int count = 0;
		tranId =tranId ==null ?"":tranId.trim();
		System.out.println("in isScanningDone--------->"+tranId);		
		if(tranId.length() > 0)
		{
			sql = "SELECT COUNT(*) FROM MULTI_PICK_ISS_DET WHERE TRAN_ID = ? AND SITE_CODE = ? "
					+ "AND PTCN = ? AND SALE_ORDER = ? AND PICK_ORDER = ? "
					+ "AND ITEM_CODE = ? AND LOT_NO= ? AND LOC_CODE = ? ";
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,tranId);
			pstmt.setString(2,siteCode);
			pstmt.setString(3,ptcnNo);
			pstmt.setString(4,saleOrderL);
			pstmt.setString(5,pickOrder);
			
			pstmt.setString(6,itemCode);
			pstmt.setString(7,lotNo);
			pstmt.setString(8,locCode);
		}
		else
		{
			sql = "SELECT COUNT(*) FROM MULTI_PICK_ISS_DET WHERE SITE_CODE = ? "
					+ "AND PTCN = ? AND SALE_ORDER = ? AND PICK_ORDER = ? "
					+ "AND ITEM_CODE = ? AND LOT_NO= ? AND LOC_CODE = ? ";
			
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1,siteCode);
			pstmt.setString(2,ptcnNo);
			pstmt.setString(3,saleOrderL);
			pstmt.setString(4,pickOrder);

			pstmt.setString(5,itemCode);
			pstmt.setString(6,lotNo);
			pstmt.setString(7,locCode);
		}		
		
		rs= pstmt.executeQuery();
		if(rs.next())
		{
			count = rs.getInt(1);
		}
		rs.close();
		rs = null;
		pstmt.close();
		pstmt = null;
		System.out.println("count in isScanningDone@@----->>["+count+"]");
		
		if(count > 0)
		{
			return true;
		}
		return false;
	}
	private String getSiteCode( String xtraParams, Connection conn ) throws ITMException,Exception
	{
		String defaultSite = "";
		String empSite = "";
		String workSite = "";
		String sql = "";
		String loginCode = "";

		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try
		{
			GenericUtility genericUtility = GenericUtility.getInstance();
			loginCode = checkNull(genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginCode" ));

			sql = "SELECT USERS.EMP_CODE, USERS.ENTITY_CODE, USERS.SITE_CODE__DEF, EMP.EMP_SITE, EMP.WORK_SITE "+
					"FROM USERS USERS, EMPLOYEE EMP WHERE USERS.EMP_CODE = EMP.EMP_CODE AND USERS.CODE = ? ";
			pstmt = conn.prepareStatement( sql );
			pstmt.setString( 1, loginCode );						
			rs = pstmt.executeQuery();	
			if( rs.next() )
			{
				defaultSite = checkNull(rs.getString("SITE_CODE__DEF"));
				empSite = checkNull(rs.getString("EMP_SITE"));
				workSite = checkNull(rs.getString("WORK_SITE"));
			}
			if ( defaultSite.trim().length() == 0 && empSite.trim().length() > 0 )
			{
				defaultSite = empSite;
			}
			rs.close();rs = null;
			pstmt.close();pstmt = null;
		}
		catch (Exception e)
		{				
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{	
			try
			{
				if ( rs != null )
				{
					rs.close();
					rs = null;
				}
				if ( pstmt != null )
				{
					pstmt.close();
					pstmt = null;
				}
			}
			catch (Exception e)
			{
				e.printStackTrace();
				throw new ITMException(e);
			}
		}
		System.out.println("defaultSite = ["+defaultSite+"]");
		return defaultSite;
	}
	private int getDBRowCount(Connection conn, String table_name, String whrCondCol, String whrCondVal)
	{
		int count=0;				
		ResultSet rs=null;
		PreparedStatement pstmt = null;
		
		String sql="select count(*) from "+table_name+" where "+whrCondCol+" = ?";
		System.out.println("SQL in getDBRowCount method : "+sql);
		try{
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,whrCondVal);
			rs = pstmt.executeQuery();
			if(rs.next()){
			count = rs.getInt(1);
			}
			if(pstmt!=null){
			pstmt.close();
			pstmt = null;
			}
			if(rs!=null){
			rs.close();
			rs = null;
			}
		}
		
		catch(Exception ex){
			System.out.println("Exception In getDBRowCount method of MultiplePtcnWiz Class : "+ex.getMessage());
			ex.printStackTrace();
		}
		System.out.println("Return count from getDBRowCount ----->>["+count+"]");					
		return count;
	}
	private String getColumnDescr(Connection conn, String columnName ,String tableName, String columnName2, String value) 
	{

			PreparedStatement pstmt = null ;
			ResultSet rs = null ; 
			String sql = "";
			String findValue = "";
			try
			{			
				sql = "SELECT " + columnName + " from " + tableName + " where " + columnName2 +"= ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,value);
				rs = pstmt.executeQuery();
				if(rs.next())
				{					
					findValue = rs.getString(1);
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;	        
			}
			catch(Exception e)
			{
				System.out.println("Exception in getColumnDescr ");
				e.printStackTrace();
			}
			System.out.println("returning String from getColumnDescr " + findValue);
			return findValue;
		 
	}

	
	
}
