/**
 * PURPOSE : Validation and Item Change implementation for Master Packing component.
 * AUTHOR : Chaitali
 */
package ibase.webitm.ejb.wms;

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

import javax.ejb.*;
import javax.naming.InitialContext;
import javax.xml.parsers.*;
import org.w3c.dom.*;

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

import javax.xml.transform.*;
import javax.xml.transform.dom.*;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.Transformer;
import javax.xml.transform.stream.StreamSource;
import javax.xml.transform.stream.StreamResult;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerConfigurationException;
import org.xml.sax.InputSource;
import java.net.InetAddress;

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

	/**
	 * The public method is used for converting the current form data into a document(DOM)
	 * The currDom 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 currFrmXmlStr contains the current form data in XML format
	 * @param hdrFrmXmlStr contains all the header information in the XML format
	 * @param allFrmXmlStr 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
	 */
	public String wfValData(String currFrmXmlStr, String hdrFrmXmlStr,String allFrmXmlStr, String objContext, String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		Document currDom = null;
		Document hdrDom = null;
		Document allDom = null;
		String errString = "";

		try
		{
			System.out.println("currFrmXmlStr :: "+ currFrmXmlStr);
			System.out.println("hdrFrmXmlStr :: "+ hdrFrmXmlStr);
			System.out.println("allFrmXmlStr :: "+ allFrmXmlStr);
			if(currFrmXmlStr != null && currFrmXmlStr.trim().length()!=0)
			{
				currDom = parseString(currFrmXmlStr); 
			}
			if(hdrFrmXmlStr != null && hdrFrmXmlStr.trim().length()!=0)
			{
				hdrDom = parseString(hdrFrmXmlStr); 
			}
			if(allFrmXmlStr != null && allFrmXmlStr.trim().length()!=0)
			{
				allDom = parseString(allFrmXmlStr);
			}
			System.out.println("Before call valdata");
			errString = wfValData(currDom,hdrDom,allDom,objContext,editFlag,xtraParams);
		}
		catch(Exception e)
		{
			System.out.println("Exception : [MasterPackIC][wfValData(String xmlCurrFrmString)] : ==>\n"+e.getMessage());
			throw new ITMException(e);
		}
		return (errString); 
	}

	/**
	 * The public overloaded method takes a document as input and is used for the validation of specified fields 
	 * Returns validation string if exist otherwise returns null in XML format
	 * @param currDom contains the current form data as a document object model
	 * @param hdrDom contains all the header information
	 * @param allDom 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
	 */	
	public String wfValData(Document currDom, Document hdrDom, Document allDom, String objContext, String editFlag, String xtraParams) throws RemoteException, ITMException 	
	{
		String errString = "";
		String siteCode = "";

		ResultSet rs = null;
		ResultSet rsInner = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		PreparedStatement pstmtInner = null;

		System.out.println("curr dom:::"+currDom);
		System.out.println("hdr dom::::"+hdrDom);
		System.out.println("all dom::::"+allDom);
		System.out.println("obj context::"+objContext);
		System.out.println("edit flg::"+editFlag);
		System.out.println("xtraparm::"+xtraParams);

		GenericUtility genericUtility = GenericUtility.getInstance();

		StringBuffer errStringXml = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root><Errors>");
		String errorType = "", errCode = "";
		ArrayList errList = new ArrayList();
		ArrayList errFields = new ArrayList();
		String holdStatus = "N";
		String cancelStatus = "N";
        //changed by sankara on 07/jun/14 for period stat
		java.util.Date tranDateDt = null;
		String prdCode = "";
		try
		{
			String columnValue = "";
			String sql = "";	
			String tranID = "";
			String ptcn = "";
			String tranDate = "";
			String pickType = "";
			String confirmed = "";
			String pickOrder = "";
			String outerCarton = "";
			String innerCarton = "";
			String status = "";
			String itemCode = "";
			String sqlInner = "";
			double weight = 0.0;		
			double cartVolume = 0.0;		
			double selectCartVolume = 0.0;	
			int noCases = 0;
		//	changed by sankara on 07/jun/14 for period stat
			SimpleDateFormat sdf1 = new SimpleDateFormat(genericUtility.getApplDateFormat());
			double masterPackCount  = 0.0, masterPackWeight = 0.0; // Added by sumit on 16/01/13
			DistCommon discommon = new DistCommon(); // Added by sumit on 16/01/13
			String lotNo = "";//added by sumit on 08/02/13
			Node parentNode = null;//added by sumit on 08/02/13
			int currentFormNo = 0, cnt = 0, count = 0;		

			ConnDriver connDriver = null;
			connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");

			NodeList parentList = null;
			NodeList childList = null;

			int noOfChilds = 0;

			String childNodeName = "";

			Node childNode =null;

			String userId = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginCode" );
			if ( objContext != null && objContext.trim().length() > 0 )
			{
				currentFormNo = Integer.parseInt( objContext );
			}

			switch(currentFormNo)
			{
			case 1 :
			{
				parentList = currDom.getElementsByTagName( "Detail" + currentFormNo );
				System.out.println("parent list="+parentList);
				childList = parentList.item( 0 ).getChildNodes();
				System.out.println("child list"+childList);
				noOfChilds = childList.getLength();
				for (int ctr = 0; ctr < noOfChilds; ctr++)
				{	
					childNode = childList.item( ctr );
					if( childNode.getNodeType() != Node.ELEMENT_NODE )
					{
						continue;
					}
					childNodeName = childNode.getNodeName();						
					if ( childNode != null && childNode.getFirstChild() != null )
					{
						columnValue = childNode.getFirstChild().getNodeValue();
					}
					System.out.println(" columnName [" + childNodeName + "] columnValue [" + columnValue + "]");

					/*if ( "tran_id".equalsIgnoreCase( childNodeName ) )
					{							
						tranID = genericUtility.getColumnValue("tran_id", currDom );

						if( tranID == null || tranID.trim().length() == 0 )
						{
							errList.add( "NULLTASKID" );
							errFields.add( childNodeName.toLowerCase() );
						}

						sql = "SELECT COUNT(*) AS COUNT FROM PACK_HDR WHERE TRAN_ID = ?";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, tranID);

						rs = pstmt.executeQuery();
						if (rs.next())
						{
							cnt = rs.getInt("COUNT");
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;

						if(cnt == 0)
						{
							errList.add( "INVTASKID" );
							errFields.add( childNodeName.toLowerCase() );
						}
						//Added by gulzar on 01/12/11
						sql = "SELECT COUNT(*) AS COUNT FROM PACK_HDR WHERE TRAN_ID = ? AND CONFIRMED = 'Y' ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, tranID);

						rs = pstmt.executeQuery();
						if (rs.next())
						{
							cnt = rs.getInt("COUNT");
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;

						if(cnt > 0)
						{
							errList.add( "PACKIDCONF" );
							errFields.add( childNodeName.toLowerCase() );
						}
						//End changes by gulzar on 01/12/11

						//added by chaitali on 28 nov 2011
						sql = "select pick_ord_hdr.pick_order as pickOrder" +
								" from pack_hdr pack_hdr, pick_ord_hdr pick_ord_hdr"+
								" where pack_hdr.pick_order = pick_ord_hdr.pick_order"+
								" and pack_hdr.tran_id = ? AND pick_ord_hdr.pick_type = 'M'";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1,tranID);
						rs = pstmt.executeQuery();
						if (rs.next())
						{
							pickOrder = checkNullAndTrim(rs.getString("pickOrder"));
						}
						else
						{
							//if pick_type is not master 
							errList.add( "VTPICKID1" );
							errFields.add( childNodeName.toLowerCase() );
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;

						//checking confirmed value in pick_iss_hdr
						sql = "select confirmed from pick_iss_hdr where pick_order = ?";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, pickOrder);
						rs = pstmt.executeQuery();
						if (rs.next())
						{
							confirmed = checkNullAndTrim(rs.getString("confirmed"));
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						if( ! "Y".equalsIgnoreCase(confirmed.trim()) )
						{
							errList.add( "VTPCKCONF1" );
							errFields.add( childNodeName.toLowerCase() );
						}
					}*/
					if ( "ptcn".equalsIgnoreCase( childNodeName ) )
					{							
						ptcn = genericUtility.getColumnValue("ptcn", currDom );

						if( ptcn == null || ptcn.trim().length() == 0 )
						{
							errList.add( "NULLTASKID" );
							errFields.add( childNodeName.toLowerCase() );
						}

						sql = "SELECT COUNT(*) AS COUNT FROM PACK_HDR WHERE PTCN = ?";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, ptcn);

						rs = pstmt.executeQuery();
						if (rs.next())
						{
							cnt = rs.getInt("COUNT");
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;

						if(cnt == 0)
						{
							errList.add( "INVPTCNID" );
							errFields.add( childNodeName.toLowerCase() );
						}
						sql = "SELECT  CASE WHEN H.HOLD_STATUS IS NULL THEN 'N' ELSE H.HOLD_STATUS END AS HOLD_STATUS , "
							+ " CASE WHEN H.CANCEL IS NULL THEN 'N' ELSE H.CANCEL END AS CANCEL FROM WAVE_TASK H, WAVE_TASK_DET D "
							+ " WHERE H.WAVE_ID = D.WAVE_ID "
							+ " AND D.PTCN = ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, ptcn);
					 
						rs = pstmt.executeQuery();
						if (rs.next())
						{
							holdStatus = rs.getString("HOLD_STATUS");
							cancelStatus = rs.getString("CANCEL");
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						
						//Changed by Pragyan on 08/01/14 adding validation in case of label not reprinted. start.
						String printStatus = "";
						sql = "SELECT PRINT_STATUS FROM WAVE_STATUS_ORG  WHERE PTCN = ?";
						pstmt = conn.prepareStatement( sql );
						pstmt.setString( 1, ptcn );							
						rs = pstmt.executeQuery();
						while( rs.next() )
						{
							printStatus = rs.getString("PRINT_STATUS");
							
							//if("N".equalsIgnoreCase(printStatus))
							if("N".equalsIgnoreCase(printStatus))
							{
								errList.add( "INVPRINTST" );
								errFields.add( childNodeName.toLowerCase() );
							}
						}
						rs.close();	rs = null;
						pstmt.close(); pstmt = null;
						//Changed by sumit on 08/01/1 adding validation in case of label not reprinted. end.

						//Added by gulzar on 01/12/11
						/*sql = "SELECT COUNT(*) AS COUNT FROM PACK_HDR WHERE TRAN_ID = ? AND CONFIRMED = 'Y' ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, tranID);

						rs = pstmt.executeQuery();
						if (rs.next())
						{
							cnt = rs.getInt("COUNT");
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;

						if(cnt > 0)
						{
							errList.add( "PACKIDCONF" );
							errFields.add( childNodeName.toLowerCase() );
						}
						//End changes by gulzar on 01/12/11

						//added by chaitali on 28 nov 2011
						sql = "select pick_ord_hdr.pick_order as pickOrder" +
								" from pack_hdr pack_hdr, pick_ord_hdr pick_ord_hdr"+
								" where pack_hdr.pick_order = pick_ord_hdr.pick_order"+
								" and pack_hdr.tran_id = ? AND pick_ord_hdr.pick_type = 'M'";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1,tranID);
						rs = pstmt.executeQuery();
						if (rs.next())
						{
							pickOrder = checkNullAndTrim(rs.getString("pickOrder"));
						}
						else
						{
							//if pick_type is not master 
							errList.add( "VTPICKID1" );
							errFields.add( childNodeName.toLowerCase() );
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;

						//checking confirmed value in pick_iss_hdr
						sql = "select confirmed from pick_iss_hdr where pick_order = ?";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, pickOrder);
						rs = pstmt.executeQuery();
						if (rs.next())
						{
							confirmed = checkNullAndTrim(rs.getString("confirmed"));
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						if( ! "Y".equalsIgnoreCase(confirmed.trim()) )
						{
							errList.add( "VTPCKCONF1" );
							errFields.add( childNodeName.toLowerCase() );
						}*/
					}
					else if ( "outer_carton_no".equalsIgnoreCase( childNodeName ) )
					{
						if ( childNode.getFirstChild() == null)
						{
							errList.add( "NULLCARTNO" );
							errFields.add( childNodeName.toLowerCase() );
						}
						else 
						{
							outerCarton = checkNull(genericUtility.getColumnValue( "outer_carton_no", currDom ));
							ptcn = checkNull(genericUtility.getColumnValue( "ptcn", currDom ));
							
							//Changed by sumit on 10/12/12 adding validation for cartion_number start.
							sql = "SELECT COUNT(*) AS COUNT FROM CARTON_MASTER WHERE CARTON_NO = ? ";
							pstmt = conn.prepareStatement( sql );
							pstmt.setString( 1, outerCarton );
							rs = pstmt.executeQuery();
							if( rs.next() )
							{
								cnt = rs.getInt(1);								
							}
							rs.close();
							rs = null;
							pstmt.close(); 
							pstmt = null;
							if( cnt == 0)
							{
								errList.add( "VTCARTNO4" );
								errFields.add( childNodeName.toLowerCase() );
							}
							cnt = 0;
							//Changed by sumit on 10/12/12 adding validation for cartion_number end.
							
							/*sql = "SELECT COUNT(*) FROM CARTON_MASTER WHERE CARTON_TYPE = 'M' AND CARTON_NO = ? AND status = 'A' and " +
									" PTCN IN (SELECT PTCN FROM WAVE_TASK_DET WHERE REF_SER = 'M-PACK' and ref_id = ?)";*/
							sql = "SELECT COUNT(*),PICK_ORDER FROM CARTON_MASTER WHERE CARTON_TYPE = 'M' AND CARTON_NO = ? AND status = 'A' and " +
							" PTCN  = ? GROUP BY PICK_ORDER";
							pstmt = conn.prepareStatement( sql );
							pstmt.setString( 1, outerCarton );
							pstmt.setString(2, ptcn);
							rs = pstmt.executeQuery();
							if( rs.next() )
							{
								cnt = rs.getInt(1);
								tranID = rs.getString("PICK_ORDER");
							}
							rs.close();
							rs = null;
							pstmt.close(); 
							pstmt = null;

							if(cnt == 0)
							{
								//Changed by sumit on 10/12/12 changed msg_id
								//errList.add( "VTCARTNO4" );
								errList.add( "INVCARTONO" );
								errFields.add( childNodeName.toLowerCase() );
							}
							
							
							//Added by gulzar on 01/12/11
							sql = "SELECT COUNT(*) AS COUNT FROM PACK_HDR WHERE TRAN_ID = ? AND CONFIRMED = 'Y' ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, tranID);

							rs = pstmt.executeQuery();
							if (rs.next())
							{
								cnt = rs.getInt("COUNT");
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;

							if(cnt > 0)
							{
								errList.add( "PACKIDCONF" );
								errFields.add( childNodeName.toLowerCase() );
							}
							//End changes by gulzar on 01/12/11

							//added by chaitali on 28 nov 2011
							//Changed by sumit on 28/09/12 for getting wave_task cancel column value start.
							/*sql = "select pick_ord_hdr.pick_order as pickOrder" +
									" from pack_hdr pack_hdr, pick_ord_hdr pick_ord_hdr"+
									" where pack_hdr.pick_order = pick_ord_hdr.pick_order"+
									" and pack_hdr.tran_id = ? AND pick_ord_hdr.pick_type = 'M'";*/
							sql = "SELECT PICK_ORD_HDR.PICK_ORDER AS PICKORDER, WAVE_TASK.CANCEL" +
									" FROM PACK_HDR PACK_HDR, PICK_ORD_HDR PICK_ORD_HDR, WAVE_TASK WAVE_TASK "+
									" WHERE PACK_HDR.PICK_ORDER = PICK_ORD_HDR.PICK_ORDER"+
									" AND PACK_HDR.WAVE_ID = WAVE_TASK.WAVE_ID "+
									" AND PACK_HDR.TRAN_ID = ? AND PICK_ORD_HDR.PICK_TYPE = 'M'";
							//Changed by sumit on 28/09/12 for getting wave_task cancel column value end.
							
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1,tranID);
							rs = pstmt.executeQuery();
							if (rs.next())
							{
								pickOrder = checkNullAndTrim(rs.getString("pickOrder"));
								//Changed by sumit on 28/09/12 adding validation in case of wave canceled start.
								if( "Y".equalsIgnoreCase(rs.getString("CANCEL")))
								{
									errList.add( "PACKCANCEL" );
									errFields.add( childNodeName.toLowerCase() );
								}
								//Changed by sumit on 28/09/12 adding validation in case of wave canceled end.
							}
							else
							{
								//if pick_type is not master 
								errList.add( "VTPICKID1" );
								errFields.add( childNodeName.toLowerCase() );
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;

							//checking confirmed value in pick_iss_hdr
							
							//Changed By Pragyan 15-feb-13 to check validation 
							//sql = "select confirmed from pick_iss_hdr where pick_order = ?";
							sql = "SELECT COUNT(*) FROM PICK_ISS_HDR P,PICK_ISS_DET D "+
									" WHERE P.TRAN_ID = D.TRAN_ID AND P.PICK_ORDER= ? "+ 
									" AND P.CONFIRMED= ? ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, pickOrder);
							pstmt.setString(2, "N");
							
							rs = pstmt.executeQuery();
							int cCtr = 0;
							
							if (rs.next())
							{
								//confirmed = checkNullAndTrim(rs.getString("confirmed"));
								cCtr = rs.getInt(1);
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;
							//if( ! "Y".equalsIgnoreCase(confirmed.trim()) )
							if( cCtr > 0)
							{
								errList.add( "VTPCKCONF1" );
								errFields.add( childNodeName.toLowerCase() );
							}
						}
					}//outer_carton_no case
					/*else if ( "outer_carton_no".equalsIgnoreCase( childNodeName ) )
					{
						if ( childNode.getFirstChild() == null)
						{
							errList.add( "NULLCARTNO" );
							errFields.add( childNodeName.toLowerCase() );
						}
						else 
						{
							outerCarton = checkNull(genericUtility.getColumnValue( "outer_carton_no", currDom ));
							tranID = checkNull(genericUtility.getColumnValue( "tran_id", currDom ));
							//Commented By Mahesh Patidar on 16/06/12
							//								sql = "SELECT STATUS FROM CARTON_MASTER WHERE CARTON_NO = ? ";
							//								pstmt = conn.prepareStatement( sql );
							//								pstmt.setString( 1, outerCarton );
							//								rs = pstmt.executeQuery();	
							//								if( rs.next() )
							//								{
							//									status = checkNull(rs.getString("STATUS"));
							//								}
							//								else
							//								{
							//									errList.add( "VTCARTNO4" );
							//									errFields.add( childNodeName.toLowerCase() );
							//								}
							//								rs.close();
							//								rs = null;
							//								pstmt.close(); 
							//								pstmt = null;
							//								
							//								if ( !"A".equalsIgnoreCase(status.trim()) )
							//								{
							//									errList.add( "VTCARTNO1" );
							//									errFields.add( childNodeName.toLowerCase() );
							//								}
							//Ended Comment By Mahesh Patidar

							//Added By Mahesh Patidar on 16/06/12
							sql = "SELECT COUNT(*) FROM CARTON_MASTER WHERE CARTON_TYPE = 'M' AND CARTON_NO = ? AND status = 'A' and " +
									" PTCN IN (SELECT PTCN FROM WAVE_TASK_DET WHERE REF_SER = 'M-PACK' and ref_id = ?)";
							pstmt = conn.prepareStatement( sql );
							pstmt.setString( 1, outerCarton );
							pstmt.setString(2, tranID);
							rs = pstmt.executeQuery();
							if( rs.next() )
							{
								cnt = rs.getInt(1);
							}
							rs.close();
							rs = null;
							pstmt.close(); 
							pstmt = null;

							if(cnt == 0)
							{
								errList.add( "VTCARTNO4" );
								errFields.add( childNodeName.toLowerCase() );
							}
							//Ended By Mahesh Patidar
						}
					}//outer_carton_no case
*/				}// for loop end
			}//case 1 end
			break;
			case 2 : //for hdr win val....
			{
				parentList = currDom.getElementsByTagName( "Detail" + currentFormNo );
				childList = parentList.item( 0 ).getChildNodes();
				noOfChilds = childList.getLength();
				for (int ctr = 0; ctr < noOfChilds; ctr++)
				{	
					childNode = childList.item( ctr );
					if( childNode.getNodeType() != Node.ELEMENT_NODE )
					{
						continue;
					}
					childNodeName = childNode.getNodeName();						
					if ( childNode != null && childNode.getFirstChild() != null )
					{
						columnValue = childNode.getFirstChild().getNodeValue();
					}
					System.out.println(" columnName [" + childNodeName + "] columnValue [" + columnValue + "]");

					if ( "tran_date".equalsIgnoreCase( childNodeName ) )
					{							
						tranDate = genericUtility.getColumnValue("tran_date", currDom );

						if( tranDate == null || tranDate.trim().length() == 0 )
						{
							errList.add( "NULLTRANDT" );
							errFields.add( childNodeName.toLowerCase() );
						}
					}
					else if ( "site_code".equalsIgnoreCase( childNodeName ) )
					{							
						siteCode = genericUtility.getColumnValue("site_code", currDom );

						if( siteCode == null || siteCode.trim().length() == 0 )
						{
							errList.add( "NULLSITECD" );
							errFields.add( childNodeName.toLowerCase() );
						}

						sql = "SELECT COUNT(*) AS COUNT FROM SITE WHERE SITE_CODE = ?";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, siteCode);

						rs = pstmt.executeQuery();
						if (rs.next())
						{
							cnt = rs.getInt("COUNT");
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;

						if(cnt == 0)
						{
							errList.add( "INVSITECD" );
							errFields.add( childNodeName.toLowerCase() );
						}
						//Changed by Pragyan.start
						else 
						{
														
							System.out.println("364 site code ="+siteCode);
							if (genericUtility.getColumnValue("tran_date", currDom) != null)
							{
								tranDateDt = sdf1.parse(genericUtility.getColumnValue("tran_date", currDom));
								sql = "select code from period where ?  between fr_date and to_date";
								pstmt = conn.prepareStatement(sql);
								pstmt.setDate(1, new java.sql.Date(tranDateDt.getTime()));
								rs = pstmt.executeQuery();
								if (rs.next())
								{
									prdCode = rs.getString(1);

								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;

								sql = "select count(*) from period_stat where site_code = ? and prd_code = ? ";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, siteCode);
								pstmt.setString(2, prdCode);
								rs = pstmt.executeQuery();
								if (rs.next())
								{
									count = rs.getInt(1);

								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
								if (count == 0)
								{
									errList.add( "VTSITEPD" );
									errFields.add( childNodeName.toLowerCase() );
									break;
								}
								// nvo_functions_adv.nf_check_period('SAL',mdate1,msite_code)
								errCode = nfCheckPeriod("IC", tranDateDt, siteCode);
								System.out.println("425 Error Code = " + errCode);
								if (errCode != null && errCode.trim().length() > 0)
								{
									errList.add(errCode);
									errFields.add( childNodeName.toLowerCase() );
									
								}
							} 							 
						}//end 
						//Changed by Pragyan.End
					}
				}// for loop end
			}//case 2 end
			break;
			case 3 :
			{
				boolean isCurrentItemHaz = false;
				System.out.println("Inside case3 validation");
				//Changed By Pragyan 21/01/13.start
				outerCarton = checkNull(genericUtility.getColumnValue( "outer_carton_no", hdrDom ));
				System.out.println("outerCarton["+outerCarton+"]");
				sql = "SELECT C.PACK_CODE,(P.LENGTH * P.WIDTH * P.HEIGHT)   FROM CARTON_MASTER C,PACKING P WHERE C.CARTON_TYPE = 'M' AND C.CARTON_NO = ? AND " +
					  " C.PACK_CODE = P.PACK_CODE ";
				pstmt = conn.prepareStatement( sql );
				pstmt.setString( 1, outerCarton );
				rs = pstmt.executeQuery();
				if( rs.next() )
				{
					cartVolume = rs.getDouble(2);
				}
				rs.close();
				rs = null;
				pstmt.close(); 
				pstmt = null;
				
				//Changed By Pragyan 21/01/12.end
				String currCarton = "", currItemCode = "",currLotNo = "";
				currCarton = genericUtility.getColumnValue( "carton_no", currDom );
				currItemCode = genericUtility.getColumnValue( "item_code", currDom );
				currLotNo = genericUtility.getColumnValue( "lot_no", currDom );
				System.out.println(" currCarton["+currCarton+"] currItemCode["+currItemCode+"] currLotNo["+currLotNo+"]");
				sqlInner = "select gross_weight,(length*width*height) from item_lot_packsize where item_code = ? " +
						" and lot_no__from <= ? and lot_no__to >= ? ";				
				pstmtInner = conn.prepareStatement( sqlInner );
				pstmtInner.setString( 1,currItemCode );				
				pstmtInner.setString( 2,currLotNo );
				pstmtInner.setString( 3,currLotNo );				
				rsInner = pstmtInner.executeQuery();				
				if( rsInner.next() )
				{
					weight += rsInner.getDouble("gross_weight");
					selectCartVolume += rsInner.getDouble(2);
				}
				rsInner.close();
				rsInner = null;
				pstmtInner.close(); 
				pstmtInner = null;
				isCurrentItemHaz = isHazardOusItem(currItemCode, conn);
				System.out.println("Selected Item Haz="+isCurrentItemHaz);
				parentList = allDom.getElementsByTagName( "Detail" + currentFormNo );
				int noOfParent = parentList.getLength();
				System.out.println("no of parent="+noOfParent);
				for ( int ctr1 = 0; ctr1 < noOfParent; ctr1++ ) 
				{
					//Changed by sumit on 08/02/13 only selected record allow start
					parentNode = parentList.item(ctr1);					
					String selected = getAttribValue(parentNode, "attribute", "selected");
					System.out.println(" value of selected variable ["+selected+"]");
					
					if(!"Y".equalsIgnoreCase(selected))
					{
						continue;
					}
					//Changed by sumit on 08/02/13 only selected record allow end 		
						
					innerCarton = genericUtility.getColumnValueFromNode( "carton_no", parentList.item(ctr1) );
					itemCode = genericUtility.getColumnValueFromNode( "item_code", parentList.item(ctr1) );
					//Changed by sumit on 08/02/13 getting lot_no value
					lotNo = genericUtility.getColumnValueFromNode( "lot_no", parentList.item(ctr1) );
					ptcn = genericUtility.getColumnValue("ptcn", hdrDom );
					System.out.println("carton no == "+innerCarton);
					System.out.println("item  code == ["+itemCode+"] lot_no ["+lotNo+"]");
					System.out.println("ptcn== "+ptcn);
					boolean checkItemHaz = isHazardOusItem(itemCode, conn);
					System.out.println("Current Item Haz="+checkItemHaz);
					
					if(!checkItemHaz && isCurrentItemHaz)
					{
						errList.add( "INVCURITEM" );
						errFields.add( childNodeName.toLowerCase() );
					}
					if(checkItemHaz && !isCurrentItemHaz)
					{
						errList.add( "INVHAZITEM" );
						errFields.add( childNodeName.toLowerCase() );
					}
					
					if( innerCarton == null || innerCarton.trim().length() == 0 )
					{
						errList.add( "NULLCRTNO" );
						errFields.add( childNodeName.toLowerCase() );
					}
					else
					{

						sql = "SELECT COUNT(*) FROM CARTON_MASTER WHERE CARTON_TYPE = 'C' AND CARTON_NO = ? and " +
								" PTCN = ? ";
						pstmt = conn.prepareStatement( sql );
						pstmt.setString( 1, innerCarton );
						pstmt.setString( 2, ptcn );
						rs = pstmt.executeQuery();
						if( rs.next() )
						{
							cnt = rs.getInt(1);
						}
						rs.close();
						rs = null;
						pstmt.close(); 
						pstmt = null;

						if(cnt == 0)
						{
							errList.add( "VTCARTNO4" );
							errFields.add( childNodeName.toLowerCase() );
						}
						//Changed by sumit on 08/02/13 below code will execute in else as Manoharan Sir.
						else
						{

							System.out.println("item Code="+itemCode+"  weight ="+weight+"   no of item"+noCases);
							noCases++;
							//sqlInner = "select gross_weight from item_lot_packsize where item_code = ? ";
							//Changed by sumit on 08/02/13 added lot in sql 
							//sqlInner = "select gross_weight,(length*width*height) from item_lot_packsize where item_code = ? ";
							sqlInner = "select gross_weight,(length*width*height) from item_lot_packsize where item_code = ? " +
									" and lot_no__from <= ? and lot_no__to >= ? ";
							
							pstmtInner = conn.prepareStatement( sqlInner );
							pstmtInner.setString( 1,itemCode );
							//Changed by sumit on 08/02/13added parameter as per sql start
							pstmtInner.setString( 2,lotNo );
							pstmtInner.setString( 3,lotNo );
							//Changed by sumit on 08/02/13added parameter as per sql end
							rsInner = pstmtInner.executeQuery();
							while( rsInner.next() )
							{
								weight += rsInner.getDouble("gross_weight");
								selectCartVolume += rsInner.getDouble(2);
							}
							rsInner.close();
							rsInner = null;
							pstmtInner.close(); 
							pstmtInner = null;
						}
					}
				}
					
					System.out.println(" weight ="+weight+"   no of item"+noCases);
					//Changed by sumit on 16/01/13 changing weight size as well getting these value from dispram start
					//if(weight > 70 || noCases > 60)
					masterPackCount  = Double.parseDouble(discommon.getDisparams("999999","MASTER_PACK_CASE_COUNT",conn));
					masterPackWeight  = Double.parseDouble(discommon.getDisparams("999999","MASTER_PACK_WEIGHT",conn));
					//if(weight > masterPackWeight || noCases > masterPackCount )
					if(weight > masterPackWeight || selectCartVolume > cartVolume ||  noCases > masterPackCount )
					{
					//Changed by sumit on 16/01/13 changing weight size as well getting these value from dispram end
						errList.add( "VTWGTEXD" );
						errFields.add( childNodeName.toLowerCase() );
					}
				
					/*innerCarton = genericUtility.getColumnValueFromNode( "carton_no", parentList.item(ctr1) );
					itemCode = genericUtility.getColumnValueFromNode( "item_code", parentList.item(ctr1) );
					tranID = genericUtility.getColumnValue("tran_id", hdrDom );
					System.out.println("carton no == "+innerCarton);
					System.out.println("item  code == "+itemCode);

					if( innerCarton == null || innerCarton.trim().length() == 0 )
					{
						errList.add( "NULLCRTNO" );
						errFields.add( childNodeName.toLowerCase() );
					}
					else
					{

						//Commented By Mahesh Patidar on 16/06/12
						//								sql = "select pick_ord_hdr.pick_order"+
						//										" from pack_hdr pack_hdr, pick_ord_hdr pick_ord_hdr"+
						//										" where pack_hdr.pick_order = pick_ord_hdr.pick_order"+
						//										" and pack_hdr.tran_id = ? AND pick_ord_hdr.pick_type = 'M'";
						//								pstmt = conn.prepareStatement(sql);
						//								pstmt.setString(1,tranID);
						//								rs = pstmt.executeQuery();
						//								if (rs.next())
						//								{
						//									pickOrder = checkNullAndTrim(rs.getString("pick_order"));
						//								}
						//								sql = "SELECT COUNT(*) AS COUNT FROM PICK_ISS_DET WHERE PICK_ORDER = ? AND CARTON_NO = ?";
						//								pstmt = conn.prepareStatement(sql);
						//								pstmt.setString(1, pickOrder);
						//								pstmt.setString(2, innerCarton);
						//							 	rs = pstmt.executeQuery();
						//								if (rs.next())
						//								{
						//									cnt = rs.getInt("COUNT");
						//								}
						//								rs.close();
						//								rs = null;
						//								pstmt.close();
						//								pstmt = null;
						//								if( cnt == 0 )
						//								{
						//									errList.add( "VTCARTNO2" );
						//									errFields.add( childNodeName.toLowerCase() );
						//								}
						//End Comment By Mahesh Patidar

						//Added By Mahesh Patidar on 16/06/12
						sql = "SELECT COUNT(*) FROM CARTON_MASTER WHERE CARTON_TYPE = 'C' AND CARTON_NO = ? and " +
								" PTCN IN (SELECT PTCN FROM WAVE_TASK_DET WHERE ref_id = ? ) ";
						pstmt = conn.prepareStatement( sql );
						pstmt.setString( 1, innerCarton );
						pstmt.setString( 2, tranID );
						rs = pstmt.executeQuery();
						if( rs.next() )
						{
							cnt = rs.getInt(1);
						}
						rs.close();
						rs = null;
						pstmt.close(); 
						pstmt = null;

						if(cnt == 0)
						{
							errList.add( "VTCARTNO4" );
							errFields.add( childNodeName.toLowerCase() );
						}

						//Ended By Mahesh Patidar
						//Added By Kunal Mandhre on 25/06/12
						System.out.println("item Code="+itemCode+"  weight ="+weight+"   no of item"+noCases);
						noCases++;
						sqlInner = "select gross_weight from item_lot_packsize where item_code = ? ";
						pstmtInner = conn.prepareStatement( sqlInner );
						pstmtInner.setString( 1,itemCode );
						rsInner = pstmtInner.executeQuery();
						while( rsInner.next() )
						{
							weight += rsInner.getDouble("gross_weight");
						}
						rsInner.close();
						rsInner = null;
						pstmtInner.close(); 
						pstmtInner = null;
					}
				}
				
				 * Take data from current dom
				parentList = currDom.getElementsByTagName( "Detail" + currentFormNo );
				noOfParent = parentList.getLength();
				System.out.println("no of parent="+noOfParent);
				for ( int ctr1 = 0; ctr1 < noOfParent; ctr1++ )  //Loop for each node of current detail
				{
					innerCarton = genericUtility.getColumnValueFromNode( "carton_no", parentList.item(ctr1) );
					itemCode = genericUtility.getColumnValueFromNode( "item_code", parentList.item(ctr1) );
					tranID = genericUtility.getColumnValue("tran_id", hdrDom );
					System.out.println("carton no == "+innerCarton);
					System.out.println("item  code == "+itemCode);

					if( innerCarton == null || innerCarton.trim().length() == 0 )
					{
						errList.add( "NULLCRTNO" );
						errFields.add( childNodeName.toLowerCase() );
					}
					else
					{

						sql = "SELECT COUNT(*) FROM CARTON_MASTER WHERE CARTON_TYPE = 'C' AND CARTON_NO = ? and " +
								" PTCN IN (SELECT PTCN FROM WAVE_TASK_DET WHERE ref_id = ? ) ";
						pstmt = conn.prepareStatement( sql );
						pstmt.setString( 1, innerCarton );
						pstmt.setString( 2, tranID );
						rs = pstmt.executeQuery();
						if( rs.next() )
						{
							cnt = rs.getInt(1);
						}
						rs.close();
						rs = null;
						pstmt.close(); 
						pstmt = null;

						if(cnt == 0)
						{
							errList.add( "VTCARTNO4" );
							errFields.add( childNodeName.toLowerCase() );
						}
						
						noCases++;
						sqlInner = "select gross_weight from item_lot_packsize where item_code = ? ";
						pstmtInner = conn.prepareStatement( sqlInner );
						pstmtInner.setString( 1,itemCode );
						rsInner = pstmtInner.executeQuery();
						while( rsInner.next() )
						{
							weight += rsInner.getDouble("gross_weight");
						}
						rsInner.close();
						rsInner = null;
						pstmtInner.close(); 
						pstmtInner = null;
					}
				}
				//Added By Kunal Mandhre on 25/06/12 
				System.out.println(" weight ="+weight+"   no of item"+noCases);
				if(weight > 70 || noCases > 60)
				{
					errList.add( "VTWGTEXD" );
					errFields.add( childNodeName.toLowerCase() );
				}*/

			}//CASE 3 END
			} //switch end
			int errListSize = errList.size();
			cnt =0;
			String errFldName = null;
			if ( errList != null && errListSize > 0 )
			{
				for (cnt = 0; cnt < errListSize; cnt++ )
				{
					errCode = (String)errList.get(cnt);
					errFldName = (String)errFields.get(cnt);
					System.out.println("errCode .........."+errCode);
					//String errMsg = hashMap.get(errCode)!=null ? hashMap.get(errCode).toString():"";
					//System.out.println("errMsg .........."+errMsg);
					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;//+"<trace>"+errMsg+"</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();
		}//try block end
		catch(Exception e)
		{
			System.out.println("Exception in MasterPackIC  == >");
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if (rs != null)
				{
					rs.close();
					rs = null;
				}
				if (pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
				if( conn != null && ! conn.isClosed() )
				{
					conn.close();
					conn = null;
				}
			}
			catch(Exception e)
			{
				System.out.println( "Exception :MasterPackIC:wfValData :==>\n"+e.getMessage());
				throw new ITMException(e);
			}
		}
		return errString;
	}
	private String errorType( Connection conn , String errorCode ) throws ITMException
	{
		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();
			throw new ITMException(ex);
		}		
		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);
			}
		}		
		return msgType;
	}

	// Item Change Functionality. Start.		
	/**
	 * The public method is defined without any parameters and returns blank string
	 */
	public String itemChanged() throws RemoteException, ITMException
	{
		return "";
	}

	/**
	 * The public method is used for converting the current form data into a document(dom)
	 * The currDom 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 currFrmXmlStr contains the current form data in XML format
	 * @param hdrFrmXmlStr contains all the header information in the XML format
	 * @param allFrmXmlStr contains the data of all the forms in XML format
	 * @param objContext represents the form number
	 * @param currentColumn represents the value of current field.
	 * @param editFlag represents the mode of transaction(A-Add or E-Edit)
	 * @param xtraParams contains additional information such as loginEmpCode,loginCode,chgTerm etc
	 */
	public String itemChanged(String currFrmXmlStr, String hdrFrmXmlStr, String allFrmXmlStr, String objContext, String currentColumn, String editFlag, String xtraParams) throws RemoteException, ITMException
	{
		Document currDom = null;
		Document hdrDom = null;
		Document allDom = null;

		String errString = null;

		GenericUtility genericUtility = GenericUtility.getInstance();

		try
		{
			System.out.println("currFrmXmlStr :: "+ currFrmXmlStr);
			System.out.println("hdrFrmXmlStr :: "+ hdrFrmXmlStr);
			System.out.println("allFrmXmlStr :: "+ allFrmXmlStr);
			if (currFrmXmlStr != null && currFrmXmlStr.trim().length()!=0)
			{
				currDom = genericUtility.parseString(currFrmXmlStr); 
			}
			if (hdrFrmXmlStr != null && hdrFrmXmlStr.trim().length()!=0)
			{
				hdrDom = genericUtility.parseString(hdrFrmXmlStr); 
			}
			if (allFrmXmlStr != null && allFrmXmlStr.trim().length()!=0)
			{
				allDom = genericUtility.parseString(allFrmXmlStr); 
			}
			errString = itemChanged( currDom, hdrDom, allDom, objContext, currentColumn, editFlag, xtraParams );
			System.out.println ( "ErrString :" + errString);
		}
		catch (Exception e)
		{
			System.out.println ( "Exception : Master Pack:defaul_ItemChanged(String,String):" + e.getMessage() + ":" );
			errString = genericUtility.createErrorString(e);
			throw new ITMException(e);
		}
		System.out.println ( "returning from Master Pack default_Itemchanged" );
		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 currDom contains the current form data as a document object model
	 * @param hdrDom contains all the header information
	 * @param allDom 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
	 * 
	 */


	public String itemChanged( Document currDom, Document hdrDom, Document allDom, String objContext, String currentColumn, String editFlag, String xtraParams ) throws RemoteException,ITMException
	{
		String retValue = "";
		try
		{
			retValue = default_ItemChanged( currDom, hdrDom, allDom,  objContext, currentColumn, editFlag, xtraParams );
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		return retValue;

	}
	public String default_ItemChanged( Document currDom, Document hdrDom, Document allDom, String objContext, String currentColumn, String editFlag, String xtraParams ) throws RemoteException,ITMException
	{
		String sql = "";
		String sql1 = "";
		String waveId = "";
		String siteCode = "";
		String siteDescr = "";
		String tranID = "";	
		String saleOrder ="";
		String empCodeAprv = "";		
		String tranDate = "";
		String confDate = "";
		String pickOrder = "";
		String outerCarton = "";
		String objName = "";//added by sumit on 13/01/13
		String palletNo = "";//Added by sumit on 25/01/13
		int lineNo = 0;
		int count= 0;


		int currentFormNo = 0;
		int domID = 0;

		double quantity = 0d;

		ResultSet rs = null;
		Connection conn = null;
		PreparedStatement pstmt = null;

		ResultSet rs1 = null;
		PreparedStatement pstmt1 = null;

		
		
		StringBuffer valueXmlString = new StringBuffer();		

		GenericUtility genericUtility = GenericUtility.getInstance();

		try
		{
			String userId = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginCode" );
			String chgTerm = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "chgTerm" );
			
			//Changed by sumit on 16/01/13 getting site_code
			siteCode = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginSiteCode" );			
			
			DateFormat dateFormat = new SimpleDateFormat(genericUtility.getApplDateFormat());
			String dbDateFormat = genericUtility.getDBDateFormat();
			String applDateFormat = genericUtility.getApplDateFormat();

			ConnDriver connDriver = null;
			connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;

			if( objContext != null && objContext.trim().length() > 0 )
			{
				currentFormNo = Integer.parseInt( objContext );
			}
			valueXmlString = new StringBuffer( "<?xml version=\"1.0\"?>\r\n<Root>\r\n<Header>\r\n<editFlag>" );
			valueXmlString.append( editFlag ).append( "</editFlag>\r\n</Header>\r\n" );
			//Changed by sumit on 12/01/13 getting obj_name start.
			objName = getObjName(hdrDom);
			switch ( currentFormNo )
			{
			case 1:
			{
				//valueXmlString.append( "<Detail1>\r\n" );
				valueXmlString.append("<Detail1 domID='1' selected=\"Y\">\r\n");
				valueXmlString.append("<attribute selected=\"Y\" updateFlag=\"E\" status=\"O\" pkNames=\"\" />\r\n"); //added by Kunal on 22/08/12
				if( currentColumn.trim().equalsIgnoreCase( "itm_default" ) )							
				{

					//Commented By Mahesh Patidar on 16/06/12
					//						sql = "select carton_no from carton_master where status = 'A' order by carton_no";
					//						pstmt = conn.prepareStatement(sql);
					//						rs = pstmt.executeQuery();
					//						if(rs.next())
					//						{
					//							outerCarton = rs.getString("carton_no");
					//						}
					//						rs.close();
					//						rs = null;
					//						pstmt.close();
					//						pstmt = null;
					//Ended By Mahesh Patidar
					//Added By Mahesh Patidar on 16/06/12
					/*tranID = genericUtility.getColumnValue( "tran_id", currDom );
					
					sql = "select carton_no from carton_master where status = 'A' and carton_type = 'M' and " +
							" PTCN IN (SELECT PTCN FROM WAVE_TASK_DET WHERE REF_SER = 'M-PACK' and ref_id = ?) " +
							" order by carton_no";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, tranID);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						outerCarton = rs.getString("carton_no");
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					//Ended By Mahesh Patidar
					 */					
					valueXmlString.append( "<outer_carton_no><![CDATA[" ).append(  outerCarton ).append( "]]></outer_carton_no>\r\n" );
				}
				valueXmlString.append( "</Detail1>\r\n" );
			}
			break;
			case 2:
			{
				if( currentColumn.trim().equalsIgnoreCase( "itm_default" ) )							
				{
					/*tranID = genericUtility.getColumnValue( "tran_id", hdrDom );
					outerCarton = genericUtility.getColumnValue( "outer_carton_no", hdrDom ); 
					java.util.Date currDate = new java.util.Date();
					SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
					String currDateStr = sdf.format(currDate);

					java.sql.Date tranDateDb = null;
					java.sql.Date confDateDb = null;

					sql = "SELECT PHDR.TRAN_ID, PHDR.TRAN_DATE, PHDR.SITE_CODE, PHDR.SALE_ORDER, PHDR.WAVE_ID, "+
							" PHDR.CONFIRMED, PHDR.PICK_ORDER, SITE.DESCR SITE_DESCR " +
							" FROM PACK_HDR PHDR , SITE SITE WHERE TRAN_ID = ?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, tranID);							
					rs = pstmt.executeQuery();
					//valueXmlString.append( "<Detail2 domID='1'>\r\n" );
					valueXmlString.append("<Detail2 domID='1' selected=\"Y\">\r\n");
					valueXmlString.append("<attribute selected=\"Y\" updateFlag=\"E\" status=\"O\" pkNames=\"\" />\r\n"); //added by Kunal on 14/08/12

					if(rs.next())
					{

						valueXmlString.append( "<tran_id><![CDATA[" ).append( checkNull(rs.getString("TRAN_ID"))  ).append( "]]></tran_id>\r\n" );

						tranDateDb = rs.getDate("TRAN_DATE");
						if ( tranDateDb != null )
						{
							tranDate = genericUtility.getValidDateString( tranDateDb.toString() , dbDateFormat, applDateFormat );
							valueXmlString.append( "<tran_date><![CDATA[" ).append(  tranDate ).append( "]]></tran_date>\r\n" );
						}
						siteCode = rs.getString("site_code");
						saleOrder = rs.getString("sale_order");
						waveId = rs.getString("wave_id");
						siteDescr = rs.getString("site_descr");
						pickOrder = rs.getString("pick_order");//changed by chaitali
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;

					valueXmlString.append( "<sale_order><![CDATA[" ).append( checkNull(saleOrder) ).append( "]]></sale_order>\r\n" );
					valueXmlString.append( "<site_code><![CDATA[" ).append( checkNull(siteCode) ).append( "]]></site_code>\r\n" );
					valueXmlString.append( "<site_descr><![CDATA[" ).append( checkNull(siteDescr) ).append( "]]></site_descr>\r\n" );
					valueXmlString.append( "<wave_id><![CDATA[" ).append( checkNull(waveId) ).append( "]]></wave_id>\r\n" );
					valueXmlString.append( "<confirmed><![CDATA[" ).append("N" ).append( "]]></confirmed>\r\n" );
					valueXmlString.append( "<outer_carton_no><![CDATA[" ).append( checkNull(outerCarton) ).append( "]]></outer_carton_no>\r\n" );
					valueXmlString.append( "<pick_order><![CDATA[" ).append( checkNull(pickOrder) ).append( "]]></pick_order>\r\n" );//changed by chaitali
					valueXmlString.append( "<chg_user><![CDATA[" ).append( userId ).append( "]]></chg_user>\r\n" );
					valueXmlString.append( "<chg_date><![CDATA[" ).append( currDateStr ).append( "]]></chg_date>\r\n" );
					valueXmlString.append( "<chg_term><![CDATA[" ).append( chgTerm ).append( "]]></chg_term>\r\n" );
					valueXmlString.append( "</Detail2>" );*/
					
					String PTCN = genericUtility.getColumnValue( "ptcn", hdrDom );
					outerCarton = genericUtility.getColumnValue( "outer_carton_no", hdrDom ); 
					java.util.Date currDate = new java.util.Date();
					SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
					String currDateStr = sdf.format(currDate);

					java.sql.Date tranDateDb = null;
					java.sql.Date confDateDb = null;

					/*sql = "SELECT PHDR.TRAN_ID, PHDR.TRAN_DATE, PHDR.SITE_CODE, PHDR.SALE_ORDER, PHDR.WAVE_ID, "+
							" PHDR.CONFIRMED, PHDR.PICK_ORDER, SITE.DESCR SITE_DESCR " +
							//Changed by sumit on 16/01/13 adding site code in below line
							//" FROM PACK_HDR PHDR , SITE SITE WHERE TRAN_ID =(SELECT PICK_ORDER FROM CARTON_MASTER WHERE CARTON_NO = ? AND PTCN = ?)";
							" FROM PACK_HDR PHDR , SITE SITE WHERE TRAN_ID =(SELECT PICK_ORDER FROM CARTON_MASTER WHERE CARTON_NO = ? AND PTCN = ?) AND SITE.SITE_CODE = ? ";*/
					sql = "SELECT PHDR.TRAN_ID, PHDR.TRAN_DATE, PHDR.SITE_CODE, PHDR.SALE_ORDER, PHDR.WAVE_ID, "+
							" PHDR.CONFIRMED, PHDR.PICK_ORDER, SITE.DESCR SITE_DESCR, PIH.PALLET_NO " +							
							" FROM PACK_HDR PHDR , SITE SITE, PICK_ISS_HDR PIH WHERE PHDR.TRAN_ID =(SELECT PICK_ORDER FROM CARTON_MASTER WHERE CARTON_NO = ? AND PTCN = ?) " +
							" AND PHDR.SITE_CODE = SITE.SITE_CODE AND SITE.SITE_CODE = ? AND PIH.PICK_ORDER = PHDR.PICK_ORDER ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, outerCarton);		
					pstmt.setString(2, PTCN);
					//Changed by sumit 16/01/13 as per sql adding site code
					pstmt.setString(3, siteCode);
					rs = pstmt.executeQuery();
					//valueXmlString.append( "<Detail2 domID='1'>\r\n" );
					valueXmlString.append("<Detail2 domID='1' selected=\"Y\">\r\n");
					valueXmlString.append("<attribute selected=\"Y\" updateFlag=\"E\" status=\"O\" pkNames=\"\" />\r\n"); //added by Kunal on 14/08/12

					if(rs.next())
					{

						valueXmlString.append( "<tran_id><![CDATA[" ).append( checkNull(rs.getString("TRAN_ID"))  ).append( "]]></tran_id>\r\n" );

						tranDateDb = rs.getDate("TRAN_DATE");
						if ( tranDateDb != null )
						{
							tranDate = genericUtility.getValidDateString( tranDateDb.toString() , dbDateFormat, applDateFormat );
							valueXmlString.append( "<tran_date><![CDATA[" ).append(  tranDate ).append( "]]></tran_date>\r\n" );
						}
						siteCode = rs.getString("site_code");
						saleOrder = rs.getString("sale_order");
						waveId = rs.getString("wave_id");
						siteDescr = rs.getString("site_descr");
						pickOrder = rs.getString("pick_order");//changed by chaitali
						palletNo = rs.getString("PALLET_NO");//Changed by sumit on 25/01/13
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					System.out.println(" pallet_no ["+palletNo+"]");
					valueXmlString.append( "<sale_order><![CDATA[" ).append( checkNull(saleOrder) ).append( "]]></sale_order>\r\n" );
					valueXmlString.append( "<site_code><![CDATA[" ).append( checkNull(siteCode) ).append( "]]></site_code>\r\n" );
					valueXmlString.append( "<site_descr><![CDATA[" ).append( checkNull(siteDescr) ).append( "]]></site_descr>\r\n" );
					valueXmlString.append( "<wave_id><![CDATA[" ).append( checkNull(waveId) ).append( "]]></wave_id>\r\n" );
					valueXmlString.append( "<confirmed><![CDATA[" ).append("N" ).append( "]]></confirmed>\r\n" );
					valueXmlString.append( "<outer_carton_no><![CDATA[" ).append( checkNull(outerCarton) ).append( "]]></outer_carton_no>\r\n" );
					valueXmlString.append( "<pick_order><![CDATA[" ).append( checkNull(pickOrder) ).append( "]]></pick_order>\r\n" );
					valueXmlString.append( "<ptcn><![CDATA[" ).append( PTCN ).append( "]]></ptcn>\r\n" );
					valueXmlString.append( "<chg_user><![CDATA[" ).append( userId ).append( "]]></chg_user>\r\n" );
					valueXmlString.append( "<chg_date><![CDATA[" ).append( currDateStr ).append( "]]></chg_date>\r\n" );
					valueXmlString.append( "<chg_term><![CDATA[" ).append( chgTerm ).append( "]]></chg_term>\r\n" );
					//Changed by sumit on 25/01/13 adding pallet_no 
					valueXmlString.append( "<pallet_no><![CDATA[" ).append( palletNo ).append( "]]></pallet_no>\r\n" );
					
					valueXmlString.append( "</Detail2>" );
				}
			}//case 2 end here
			break;
			case 3:
			{	
				if( currentColumn.trim().equalsIgnoreCase( "itm_default" ) )							
				{
					
				String pickOrder1 = "",lineNo1 = "",itemCode = "",lotNo = "";
				double packSize = 0;
				tranID = genericUtility.getColumnValue("tran_id", allDom, "2");
				pickOrder = genericUtility.getColumnValue("pick_order",  allDom, "2");
				
				//Changed by sumit on 13/01/13 getting ptcn value
				String ptcn = "";
				ptcn = genericUtility.getColumnValue("ptcn",  hdrDom, "1");			

				//Changed by sumit on 12/01/13 modified sql ( Getting all selected record from carton master)
				sql = "SELECT TRAN_ID,LINE_NO, CARTON_NO,SALE_ORDER,LINE_NO__SORD,ITEM_CODE,SITE_CODE,LOC_CODE,LOT_NO,LOT_SL,QUANTITY,NO_ART FROM PACK_DET WHERE TRAN_ID = ? ORDER BY CARTON_NO";
				pstmt = conn.prepareStatement(sql);
				//Changed by sumit on 13/01/13 setting input as per sql
				pstmt.setString(1, tranID);
				/*pstmt.setString(1, ptcn);
				pstmt.setString(2, ptcn);		*/		
				rs = pstmt.executeQuery();
				//Changed by sumit on 13/01/13 showing seleted detail in case of master unpacking only 
				//while(rs.next())
				while(rs.next())
				{
					//domID++;
					domID = rs.getInt("LINE_NO");
					//valueXmlString.append( "<Detail3 domID='"+ domID +"' objContext='"+currentFormNo+"' objName=\"master_pack\" selected=\"Y\">\r\n" );
					valueXmlString.append( "<Detail3 domID='"+ domID +"' objContext = '"+currentFormNo+"' objName='master_pack' selected=\"Y\">\r\n" );
					valueXmlString.append("<attribute selected=\"Y\" updateFlag=\"E\" status=\"O\" pkNames=\"\" />\r\n"); //added by Kunal on 22/08/12
					valueXmlString.append( "<tran_id><![CDATA[" ).append( rs.getString("TRAN_ID")).append( "]]></tran_id>\r\n" );
					valueXmlString.append( "<line_no><![CDATA[" ).append( rs.getString("LINE_NO") ).append( "]]></line_no>\r\n" );
					valueXmlString.append( "<carton_no><![CDATA[" ).append( checkNullAndTrim(rs.getString("CARTON_NO")) ).append( "]]></carton_no>\r\n" );
					valueXmlString.append( "<sale_order><![CDATA[" ).append( checkNullAndTrim(rs.getString("SALE_ORDER")) ).append( "]]></sale_order>\r\n" );
					valueXmlString.append( "<line_no__sord><![CDATA[" ).append( checkNullAndTrim(rs.getString("LINE_NO__SORD")) ).append( "]]></line_no__sord>\r\n" );
					valueXmlString.append( "<item_code><![CDATA[" ).append( checkNullAndTrim(rs.getString("ITEM_CODE")) ).append( "]]></item_code>\r\n" );
					valueXmlString.append( "<site_code><![CDATA[" ).append( checkNullAndTrim(rs.getString("SITE_CODE")) ).append( "]]></site_code>\r\n" );
					valueXmlString.append( "<loc_code><![CDATA[" ).append( checkNullAndTrim(rs.getString("LOC_CODE")) ).append( "]]></loc_code>\r\n" );
					valueXmlString.append( "<lot_no><![CDATA[" ).append( checkNullAndTrim(rs.getString("LOT_NO")) ).append( "]]></lot_no>\r\n" );
					valueXmlString.append( "<lot_sl><![CDATA[" ).append( checkNullAndTrim(rs.getString("LOT_SL")) ).append( "]]></lot_sl>\r\n" );
					valueXmlString.append( "<quantity><![CDATA[" ).append( checkNullAndTrim(rs.getString("QUANTITY")) ).append( "]]></quantity>\r\n" );
					valueXmlString.append( "<no_art><![CDATA[" ).append( checkNullAndTrim(rs.getString("NO_ART")) ).append( "]]></no_art>\r\n" );


					valueXmlString.append("</Detail3>\r\n");
					count ++;
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;

				sql = "SELECT MAX(LINE_NO) AS MAXLINE FROM PACK_DET WHERE TRAN_ID = ?";   
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, tranID);
				rs = pstmt.executeQuery();
				
				if(rs.next())
				{
					domID = rs.getInt(1);
					
				}
				
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
				//change done by Kunal on 08/08/12

				//sql =  "select  pick_order,line_no__pick  from carton_master where PTCN  in (SELECT PTCN FROM WAVE_TASK_DET WHERE REF_SER = 'M-PACK' and ref_id = ?) " ;
				sql = "select carton_no,pick_order,line_no__pick,item_code from carton_master where carton_type = 'C' and " +
						" PTCN IN (SELECT PTCN FROM WAVE_TASK_DET WHERE ref_id = ?  ) AND CARTON_NO NOT IN (SELECT CARTON_NO FROM PACK_DET WHERE TRAN_ID = ? ) AND STATUS = 'A' " +
						"  order by carton_no";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, tranID);
				pstmt.setString(2, tranID);
				rs = pstmt.executeQuery();
				//Changed by sumit on 13/01/13 showing un-seleted detail in case of master unpacking only 
				//while(rs.next())
				while(rs.next() && "master_pack".equalsIgnoreCase(objName))
				{
					pickOrder1 =  checkNullAndTrim(rs.getString("pick_order"));
					lineNo1 =  checkNullAndTrim(rs.getString("line_no__pick"));
					itemCode =  checkNullAndTrim(rs.getString("item_code"));


					sql1 = "SELECT SALE_ORDER,LINE_NO__SORD,ITEM_CODE,SITE_CODE,LOC_CODE,LOT_NO,LOT_SL,QUANTITY,NO_ART FROM PICK_ORD_DET WHERE PICK_ORDER = ?  AND LINE_NO = ? "; //change done by kunal on 7/08/12 as per S Manoharan Sir intruction.  
					pstmt1 = conn.prepareStatement(sql1);
					pstmt1.setString(1, pickOrder);
					pstmt1.setString(2, lineNo1);
					rs1 = pstmt1.executeQuery();				
							
					if(rs1.next())
					{
						
						//itemCode = checkNullAndTrim(rs1.getString("ITEM_CODE"));
						lotNo = checkNullAndTrim(rs1.getString("LOT_NO"));
						HashMap ItemPackMap = getItemVoumeMap(itemCode,lotNo,conn);
						packSize = (Double)ItemPackMap.get("PACK_SIZE");
						domID++;
						//valueXmlString.append( "<Detail3 domID='"+ domID +"' objContext='"+currentFormNo+"' objName=\"master_pack\" selected=\"N\">\r\n" );
						valueXmlString.append( "<Detail3 domID='"+ domID +"' objContext = '"+currentFormNo+"' objName='master_pack' selected=\"N\">\r\n" );
						valueXmlString.append("<attribute selected=\"N\" updateFlag=\"A\" status=\"N\" pkNames=\"\" />\r\n"); //added by Kunal on 22/08/12
						valueXmlString.append( "<tran_id><![CDATA[" ).append( tranID ).append( "]]></tran_id>\r\n" );
						valueXmlString.append( "<line_no><![CDATA[" ).append( domID ).append( "]]></line_no>\r\n" );
						valueXmlString.append( "<carton_no><![CDATA[" ).append( checkNullAndTrim(rs.getString("CARTON_NO")) ).append( "]]></carton_no>\r\n" );


						valueXmlString.append( "<sale_order><![CDATA[" ).append( checkNullAndTrim(rs1.getString("SALE_ORDER")) ).append( "]]></sale_order>\r\n" );
						valueXmlString.append( "<line_no__sord><![CDATA[" ).append( checkNullAndTrim(rs1.getString("LINE_NO__SORD")) ).append( "]]></line_no__sord>\r\n" );
						valueXmlString.append( "<item_code><![CDATA[" ).append( itemCode  ).append( "]]></item_code>\r\n" );
						valueXmlString.append( "<site_code><![CDATA[" ).append( checkNullAndTrim(rs1.getString("SITE_CODE")) ).append( "]]></site_code>\r\n" );
						valueXmlString.append( "<loc_code><![CDATA[" ).append( checkNullAndTrim(rs1.getString("LOC_CODE")) ).append( "]]></loc_code>\r\n" );
						valueXmlString.append( "<lot_no><![CDATA[" ).append( lotNo ).append( "]]></lot_no>\r\n" );
						valueXmlString.append( "<lot_sl><![CDATA[" ).append( checkNullAndTrim(rs1.getString("LOT_SL")) ).append( "]]></lot_sl>\r\n" );
						valueXmlString.append( "<quantity><![CDATA[" ).append( packSize).append( "]]></quantity>\r\n" );
						valueXmlString.append( "<no_art><![CDATA[" ).append( "1" ).append( "]]></no_art>\r\n" );

						valueXmlString.append("</Detail3>\r\n");
						count ++;
					}
					rs1.close();
					rs1 = null;
					pstmt1.close();
					pstmt1 = null;
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;

				String updatedDetailDomStr = addSelectedRecToDom( allDom, valueXmlString.toString() );

				valueXmlString = null;
				valueXmlString = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root>\r\n<Header>\r\n<editFlag>").append( editFlag );
				valueXmlString.append("</editFlag>\r\n</Header>\r\n");
				updatedDetailDomStr = updatedDetailDomStr.substring(updatedDetailDomStr.indexOf("<Root>") + "<Root>".length(), updatedDetailDomStr.indexOf("</Root>") );
				valueXmlString.append(updatedDetailDomStr);

				System.out.println("\n**************************\n");
				System.out.println("updatedDetailDomStr[1]:>>>>>>>>>>> "+updatedDetailDomStr);
					/*--Commented and changed by chaitali on 05/12/11
			  			saleOrder = genericUtility.getColumnValue("sale_order", allDom, "2");
						waveId = genericUtility.getColumnValue("wave_id", allDom, "2");

						sql = "SELECT PICK_ORDER FROM PICK_ORD_HDR WHERE SALE_ORDER = ? AND WAVE_ID = ? AND PICK_TYPE = 'M'";
						pstmt =  conn.prepareStatement(sql);
						pstmt.setString(1, saleOrder);
						pstmt.setString(2 , waveId);
						rs = pstmt.executeQuery();
						if(rs.next())
						{								
							pickOrder = rs.getString("PICK_ORDER");								
						}
						rs.close(); rs = null;
						pstmt.close(); pstmt = null;
					 
					String pickOrder1 = "",lineNo1 = "";
					tranID = genericUtility.getColumnValue("tran_id", allDom, "2");
					pickOrder = genericUtility.getColumnValue("pick_order",  allDom, "2");
					//Commented By Mahesh Patidar on 16/06/12
					//						sql = "SELECT DISTINCT(CARTON_NO) AS CARTON_NO FROM PICK_ISS_DET WHERE PICK_ORDER = ? ";
					//						pstmt =  conn.prepareStatement(sql);
					//						pstmt.setString(1, pickOrder);
					//						rs = pstmt.executeQuery();
					//
					//						while(rs.next())
					//						{
					//							domID++;
					//							valueXmlString.append( "<Detail3 domID='"+ domID +"' objContext = '"+currentFormNo+"' objName=\"master_pack\" >\r\n" );
					//							//valueXmlString.append( "<attribute selected=\"Y\"/>\r\n" );
					//							valueXmlString.append( "<tran_id><![CDATA[" ).append( tranID ).append( "]]></tran_id>\r\n" );
					//							valueXmlString.append( "<line_no><![CDATA[" ).append( domID ).append( "]]></line_no>\r\n" );
					//							valueXmlString.append( "<carton_no><![CDATA[" ).append( checkNullAndTrim(rs.getString("CARTON_NO")) ).append( "]]></carton_no>\r\n" );
					//							valueXmlString.append("</Detail3>\r\n");
					//						}
					//						rs.close(); rs = null;
					//						pstmt.close(); pstmt = null;
					//Ended Commented By Mahesh Patidar
					//Added By Mahesh Patidar on 16/06/12
					//sql = "SELECT CARTON_NO FROM PACK_DET WHERE TRAN_ID = ? ORDER BY CARTON_NO";

					sql = "SELECT TRAN_ID,LINE_NO, CARTON_NO,SALE_ORDER,LINE_NO__SORD,ITEM_CODE,SITE_CODE,LOC_CODE,LOT_NO,LOT_SL,QUANTITY,NO_ART FROM PACK_DET WHERE TRAN_ID = ? ORDER BY CARTON_NO";   
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, tranID);
					rs = pstmt.executeQuery();
					while(rs.next())
					{
						domID++;
						//valueXmlString.append( "<Detail3 domID='"+ domID +"' objContext='"+currentFormNo+"' objName=\"master_pack\" selected=\"Y\">\r\n" );
						valueXmlString.append( "<Detail3 domID='"+ domID +"' objContext = '"+currentFormNo+"' objName='master_pack' selected=\"Y\">\r\n" );
						valueXmlString.append("<attribute selected=\"Y\" updateFlag=\"E\" status=\"O\" pkNames=\"\" />\r\n"); //added by Kunal on 22/08/12
						valueXmlString.append( "<tran_id><![CDATA[" ).append( rs.getString("TRAN_ID")).append( "]]></tran_id>\r\n" );
						valueXmlString.append( "<line_no><![CDATA[" ).append( rs.getString("LINE_NO") ).append( "]]></line_no>\r\n" );
						valueXmlString.append( "<carton_no><![CDATA[" ).append( checkNullAndTrim(rs.getString("CARTON_NO")) ).append( "]]></carton_no>\r\n" );
						valueXmlString.append( "<sale_order><![CDATA[" ).append( checkNullAndTrim(rs.getString("SALE_ORDER")) ).append( "]]></sale_order>\r\n" );
						valueXmlString.append( "<line_no__sord><![CDATA[" ).append( checkNullAndTrim(rs.getString("LINE_NO__SORD")) ).append( "]]></line_no__sord>\r\n" );
						valueXmlString.append( "<item_code><![CDATA[" ).append( checkNullAndTrim(rs.getString("ITEM_CODE")) ).append( "]]></item_code>\r\n" );
						valueXmlString.append( "<site_code><![CDATA[" ).append( checkNullAndTrim(rs.getString("SITE_CODE")) ).append( "]]></site_code>\r\n" );
						valueXmlString.append( "<loc_code><![CDATA[" ).append( checkNullAndTrim(rs.getString("LOC_CODE")) ).append( "]]></loc_code>\r\n" );
						valueXmlString.append( "<lot_no><![CDATA[" ).append( checkNullAndTrim(rs.getString("LOT_NO")) ).append( "]]></lot_no>\r\n" );
						valueXmlString.append( "<lot_sl><![CDATA[" ).append( checkNullAndTrim(rs.getString("LOT_SL")) ).append( "]]></lot_sl>\r\n" );
						valueXmlString.append( "<quantity><![CDATA[" ).append( checkNullAndTrim(rs.getString("QUANTITY")) ).append( "]]></quantity>\r\n" );
						valueXmlString.append( "<no_art><![CDATA[" ).append( checkNullAndTrim(rs.getString("NO_ART")) ).append( "]]></no_art>\r\n" );


						valueXmlString.append("</Detail3>\r\n");
						count ++;
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;

					//change done by Kunal on 08/08/12

					//sql =  "select  pick_order,line_no__pick  from carton_master where PTCN  in (SELECT PTCN FROM WAVE_TASK_DET WHERE REF_SER = 'M-PACK' and ref_id = ?) " ;
					sql = "select carton_no,pick_order,line_no__pick from carton_master where carton_type = 'C' and " +
							" PTCN IN (SELECT PTCN FROM WAVE_TASK_DET WHERE ref_id = ?  ) AND CARTON_NO NOT IN (SELECT CARTON_NO FROM PACK_DET WHERE TRAN_ID = ? ) AND STATUS = 'A' " +
							"  order by carton_no";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, tranID);
					pstmt.setString(2, tranID);
					rs = pstmt.executeQuery();
					while(rs.next())
					{
						pickOrder1 =  checkNullAndTrim(rs.getString("pick_order"));
						lineNo1 =  checkNullAndTrim(rs.getString("line_no__pick"));



						sql1 = "SELECT SALE_ORDER,LINE_NO__SORD,ITEM_CODE,SITE_CODE,LOC_CODE,LOT_NO,LOT_SL,QUANTITY,NO_ART FROM PICK_ORD_DET WHERE PICK_ORDER = ?  AND LINE_NO = ? "; //change done by kunal on 7/08/12 as per S Manoharan Sir intruction.  
						pstmt1 = conn.prepareStatement(sql1);
						pstmt1.setString(1, pickOrder);
						pstmt1.setString(2, lineNo1);
						rs1 = pstmt1.executeQuery();
						if(rs1.next())
						{
							domID++;
							//valueXmlString.append( "<Detail3 domID='"+ domID +"' objContext='"+currentFormNo+"' objName=\"master_pack\" selected=\"N\">\r\n" );
							valueXmlString.append( "<Detail3 domID='"+ domID +"' objContext = '"+currentFormNo+"' objName='master_pack' selected=\"N\">\r\n" );
							valueXmlString.append("<attribute selected=\"N\" updateFlag=\"A\" status=\"N\" pkNames=\"\" />\r\n"); //added by Kunal on 22/08/12
							valueXmlString.append( "<tran_id><![CDATA[" ).append( tranID ).append( "]]></tran_id>\r\n" );
							valueXmlString.append( "<line_no><![CDATA[" ).append( domID ).append( "]]></line_no>\r\n" );
							valueXmlString.append( "<carton_no><![CDATA[" ).append( checkNullAndTrim(rs.getString("CARTON_NO")) ).append( "]]></carton_no>\r\n" );


							valueXmlString.append( "<sale_order><![CDATA[" ).append( checkNullAndTrim(rs1.getString("SALE_ORDER")) ).append( "]]></sale_order>\r\n" );
							valueXmlString.append( "<line_no__sord><![CDATA[" ).append( checkNullAndTrim(rs1.getString("LINE_NO__SORD")) ).append( "]]></line_no__sord>\r\n" );
							valueXmlString.append( "<item_code><![CDATA[" ).append( checkNullAndTrim(rs1.getString("ITEM_CODE")) ).append( "]]></item_code>\r\n" );
							valueXmlString.append( "<site_code><![CDATA[" ).append( checkNullAndTrim(rs1.getString("SITE_CODE")) ).append( "]]></site_code>\r\n" );
							valueXmlString.append( "<loc_code><![CDATA[" ).append( checkNullAndTrim(rs1.getString("LOC_CODE")) ).append( "]]></loc_code>\r\n" );
							valueXmlString.append( "<lot_no><![CDATA[" ).append( checkNullAndTrim(rs1.getString("LOT_NO")) ).append( "]]></lot_no>\r\n" );
							valueXmlString.append( "<lot_sl><![CDATA[" ).append( checkNullAndTrim(rs1.getString("LOT_SL")) ).append( "]]></lot_sl>\r\n" );
							valueXmlString.append( "<quantity><![CDATA[" ).append( checkNullAndTrim(rs1.getString("QUANTITY")) ).append( "]]></quantity>\r\n" );
							valueXmlString.append( "<no_art><![CDATA[" ).append( checkNullAndTrim(rs1.getString("NO_ART")) ).append( "]]></no_art>\r\n" );

							valueXmlString.append("</Detail3>\r\n");
							count ++;
						}
						rs1.close();
						rs1 = null;
						pstmt1.close();
						pstmt1 = null;
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;



					

					sql = "select carton_no from carton_master where carton_type = 'C' and " +
							" PTCN IN (SELECT PTCN FROM WAVE_TASK_DET WHERE ref_id = ?  ) AND CARTON_NO NOT IN (SELECT CARTON_NO FROM PACK_DET WHERE TRAN_ID = ? ) AND STATUS = 'A' " +
							"  order by carton_no";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, tranID);
					pstmt.setString(2, tranID);
					rs = pstmt.executeQuery();
					while(rs.next())
					{
						domID++;
						valueXmlString.append( "<Detail3 domID='"+ domID +"' objContext='"+currentFormNo+"' objName=\"master_pack\" selected=\"N\">\r\n" );
						valueXmlString.append( "<tran_id><![CDATA[" ).append( tranID ).append( "]]></tran_id>\r\n" );
						valueXmlString.append( "<line_no><![CDATA[" ).append( domID ).append( "]]></line_no>\r\n" );
						valueXmlString.append( "<carton_no><![CDATA[" ).append( checkNullAndTrim(rs.getString("CARTON_NO")) ).append( "]]></carton_no>\r\n" );
						valueXmlString.append("</Detail3>\r\n");
						count ++;
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					 

					//Ended By Mahesh Patidar
					///////////
					String updatedDetailDomStr = addSelectedRecToDom( allDom, valueXmlString.toString() );

					valueXmlString = null;
					valueXmlString = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root>\r\n<Header>\r\n<editFlag>").append( editFlag );
					valueXmlString.append("</editFlag>\r\n</Header>\r\n");
					updatedDetailDomStr = updatedDetailDomStr.substring(updatedDetailDomStr.indexOf("<Root>") + "<Root>".length(), updatedDetailDomStr.indexOf("</Root>") );
					valueXmlString.append(updatedDetailDomStr);

					System.out.println("\n**************************\n");
					System.out.println("updatedDetailDomStr[1]:>>>>>>>>>>> "+updatedDetailDomStr);*/

				}//case itm_default
			}// case 3
			}//switch statement ends here
		}//try block ends here
		catch(Exception e)
		{
			System.out.println( "Exception :MasterPackIC :itemChanged(Document,String):" + e.getMessage() + ":" );
			valueXmlString = valueXmlString.append( genericUtility.createErrorString( e ) );
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if (rs != null)
				{
					rs.close();
					rs = null;
				}
				if (pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
				if( conn != null && ! conn.isClosed() )
				{
					conn.close();
				}
			}
			catch(Exception e)
			{
				System.out.println( "Exception :MasterPackIC:itemChanged :==>\n"+e.getMessage());
				throw new ITMException(e);
			}
		}
		valueXmlString.append( "</Root>\r\n" );	
		System.out.println( "\n****ValueXmlString :" + 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 inputVal )
	{
		if ( inputVal == null )
		{
			inputVal = "";
		}
		return inputVal;
	}
	/**
	 * 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 String addSelectedRecToDom( Document allFormDataDom, String detailString ) throws Exception
	{
		ArrayList selectedList = new ArrayList();
		NodeList detailList = null;
		Node currDetail = null;
		NodeList currDetailList = null;
		String selectedVal = "", nodeName = "";
		String domIDVal = "";
		String updatedDetailDomStr = "";
		String attributeVal = "";
		String cartonNo = "";
		String columnValue = "";

		HashMap toUpdateMap = new HashMap();

		Node elementName = null;

		int currDetailListLength = 0;
		int	detailListLength = 0;

		int selectedValCnt = 0;

		GenericUtility genericUtility = GenericUtility.getInstance();

		try
		{
			detailList = allFormDataDom.getElementsByTagName("Detail3");
			detailListLength = detailList.getLength();
			for ( int ctr = 0; ctr < detailListLength; ctr++ )
			{
				currDetail = detailList.item(ctr);
				currDetailList = currDetail.getChildNodes();
				currDetailListLength = currDetailList.getLength();

				attributeVal = "";
				columnValue = "";

				ArrayList toUpdNodeList = new ArrayList();
				HashMap dataMap = new HashMap();

				for ( int i=0; i< currDetailListLength; i++ )
				{
					Node childNode = currDetailList.item(i);
					nodeName = childNode.getNodeName();
					if (nodeName.equalsIgnoreCase("Attribute"))
					{
						attributeVal = checkNull(currDetailList.item(i).getAttributes().getNamedItem("selected").getNodeValue());
						System.out.println("attributeVal =["+attributeVal+"]");
					}//if (nodeName = Attribute
					else if (nodeName.equalsIgnoreCase("carton_no"))
					{
						if ( childNode != null && childNode.getFirstChild() != null )
						{
							cartonNo = childNode.getFirstChild().getNodeValue();
							//toUpdNodeList.add( 1, childNode );
							dataMap.put("carton_no", childNode);
						}
					}
				}//End of inner for loop
				if ( "Y".equalsIgnoreCase(attributeVal.trim()) )
				{
					if ( currDetail.getAttributes().getNamedItem( "domID" ) != null )
					{
						domIDVal = checkNull(currDetail.getAttributes().getNamedItem( "domID" ).getNodeValue());
						System.out.println("domIDVal =["+domIDVal+"]");
					}
					toUpdateMap.put( domIDVal, dataMap );
				}
			}//End of for loop

			System.out.println("toUpdateMap:>>>>>>>>>>>: "+toUpdateMap);

			detailList = null;

			detailString = detailString + "</Root>\r\n" ;	
			System.out.println("detailString:>>>>>>>>>>> "+detailString);
			Document detailDom = genericUtility.parseString(detailString); 

			Element elementAttr = null;

			detailList = detailDom.getElementsByTagName("Detail3");
			detailListLength = detailList.getLength();
			for ( int ctr = 0; ctr < detailListLength; ctr++ )
			{
				currDetail = detailList.item(ctr);

				if ( currDetail.getAttributes().getNamedItem( "selected" ) != null )
				{
					selectedVal = checkNull(currDetail.getAttributes().getNamedItem( "selected" ).getNodeValue());
					System.out.println("selectedVal =["+selectedVal+"]");
					if ( "N".equalsIgnoreCase(selectedVal.trim()) )
					{
						if ( currDetail.getAttributes().getNamedItem( "domID" ) != null )
						{
							//ArrayList aList = null;

							HashMap dataMap = new HashMap();

							domIDVal = checkNull(currDetail.getAttributes().getNamedItem( "domID" ).getNodeValue());
							System.out.println("domIDVal =["+domIDVal+"]");
							System.out.println("toUpdateMap.containsKey(domIDVal) =["+toUpdateMap.containsKey(domIDVal)+"]");
							if ( toUpdateMap.containsKey(domIDVal) )
							{
								elementAttr = (Element)currDetail;
								elementAttr.setAttribute( "selected" , "Y" );

								/*--not required
								Node cartonNoNode = null;

								dataMap = (HashMap)toUpdateMap.get(domIDVal);

								if ( dataMap.get("carton_no") != null )
								{
									cartonNoNode = (Node)dataMap.get("carton_no");
								}

								Node updatedNode = null;

								currDetailList = currDetail.getChildNodes();
								currDetailListLength = currDetailList.getLength();

								for ( int i = 0; i < currDetailListLength; i++ )
								{
									elementName = currDetailList.item(i);
									nodeName = elementName.getNodeName();
									if( elementName.getNodeType() != Node.ELEMENT_NODE || nodeName.equalsIgnoreCase("#text") )
									{
										continue;
									}
									System.out.println("nodeName =["+nodeName+"]");
									if ( nodeName.equalsIgnoreCase("lot_sl") )
									{
										//System.out.println("LocCodeToNode =["+genericUtility.serializeDom( LocCodeToNode )+"]");
										updatedNode = detailDom.importNode(cartonNoNode, true);
										//System.out.println("updatedNode =["+genericUtility.serializeDom( updatedNode )+"]");
										(detailDom.getElementsByTagName("Detail3").item(ctr)).replaceChild(updatedNode, elementName );
									}
								}//inner for loop
								 */
								//End commented gulzar
							}//if ( toUpdateMap.containsKey(domIDVal) )
						}//if ( currDetail.getAttributes().getNamedItem( "domID" )
					}//if ( "N".equalsIgnoreCase(selectedVal.trim()) )
				}//if ( currDetail.getAttributes().getNamedItem( "selected" )
			}//outer for loop
			updatedDetailDomStr = genericUtility.serializeDom( detailDom );
			System.out.println("updatedDetailDomStr:>>>>>>>>>>> "+updatedDetailDomStr);
		}
		catch (Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}

		return updatedDetailDomStr;
	}
	
	private HashMap getItemVoumeMap(String itemCode,String lotNo,Connection con)throws Exception
	{
		double packSize = 0,itemSize = 0,lotSize = 0;
		PreparedStatement pstmt = null;
		String sql="";
		ResultSet rs = null;
		double itmLen = 0,itmWidth = 0,itmHeight = 0,itemWeight = 0,lotLen = 0 ,lotHeight = 0,lotWidth = 0,lotWeight = 0;
		HashMap dataVolumeMap = new HashMap();
		
		try {
			
			sql = "SELECT I.LENGTH ITEM_LEN,I.WIDTH ITEM_WID,I.HEIGHT ITEM_HEIGHT,I.GROSS_WEIGHT ITEM_WEIGHT,"
				  +" L.LENGTH LITEM_LEN,L.WIDTH LITEM_WID,L.HEIGHT LITEM_HEIGHT,L.SHIPPER_SIZE SHIPSIZE,L.GROSS_WEIGHT LOT_WEIGHT FROM"
				  +" ITEM I,ITEM_LOT_PACKSIZE L"
				  +" WHERE I.ITEM_CODE = L.ITEM_CODE"
				  +" AND L.LOT_NO__FROM <= ? AND L.LOT_NO__TO >= ?"
				  +" AND  I.ITEM_CODE = ?";
				  
			
			pstmt = con.prepareStatement(sql);
			if(lotNo != null && lotNo.length() > 0)
			{
				pstmt.setString(1, lotNo);
				pstmt.setString(2, lotNo);
			}
			else
			{
				pstmt.setString(1, "00");
				pstmt.setString(2, "ZZ");
			}
			pstmt.setString(3, itemCode);
			rs = pstmt.executeQuery();
			
			if(rs.next())
			{
				itmLen = rs.getDouble("ITEM_LEN");
				itmWidth = rs.getDouble("ITEM_WID");
				itmHeight = rs.getDouble("ITEM_HEIGHT");
				itemWeight = rs.getDouble("ITEM_WEIGHT");
				lotLen = rs.getDouble("LITEM_LEN");
				lotWidth = rs.getDouble("LITEM_WID");
				lotHeight = rs.getDouble("LITEM_HEIGHT");				
				packSize = rs.getDouble("SHIPSIZE");				
				lotWeight = rs.getDouble("LOT_WEIGHT");				
			}
			
			//packSize = (lotHeight * lotWidth * lotLen)/(itmLen * itmWidth * itmHeight);
			itemSize = (itmLen * itmWidth * itmHeight);
			lotSize = (lotHeight * lotWidth * lotLen);
			
			dataVolumeMap.put("PACK_SIZE", packSize);
			dataVolumeMap.put("ITEM_SIZE", itemSize);
			dataVolumeMap.put("LOT_SIZE", lotSize);
			dataVolumeMap.put("ITEM_WEIGHT", itemWeight);
			dataVolumeMap.put("PACK_WEIGHT", lotWeight);
			
			if(pstmt != null)
			{
				pstmt.close();
				pstmt = null;
		
			}
			if(rs != null)
			{
				rs.close();
				rs = null;
		
			}
			
			
			
		} catch (Exception e) {
			// TODO: handle exception

			throw e;
		}
		finally
		{
			if(pstmt != null)
			{
				pstmt.close();
				pstmt = null;
		
			}
			if(rs != null)
			{
				rs.close();
				rs = null;
		
			}
		}
		
		return dataVolumeMap;
	}
	
	//Changed by sumit on 12/01/13 Getting obj_name from dom start.
	private String getObjName(Document dom) throws ITMException
	{		
		NodeList parentNodeList = null;		
		int parentNodeListLength ;		
		String objName = "";
		try
		{			
			parentNodeList = dom.getElementsByTagName("Detail1");
			parentNodeListLength = parentNodeList.getLength();				
			for(int row = 0; row < parentNodeListLength; row++)
			{
				Node childNode = parentNodeList.item(row);						
				objName = childNode.getAttributes().getNamedItem("objName").getNodeValue();										
			}				
		}
		catch (Exception e)
		{
			throw new ITMException(e);
		}
		return objName;
	}
	//Changed by sumit on 12/01/13 Getting obj_name from dom end.
	
	//Changed by sumit on 08/02/13 reteriving selected value start.
	private String getAttribValue(Node detailNode, String nodeName, String attribStr)
	{
		String attribValue = "";
		try
		{
			String domStr = GenericUtility.getInstance().serializeDom(detailNode);
			Document dom = GenericUtility.getInstance().parseString(domStr);
			if( dom != null /*&& dom.getAttributes() != null*/)
			{
				Node attributeNode = dom.getElementsByTagName( nodeName ).item(0);
				attribValue = getAttribValue(attributeNode, attribStr);
			}
		}
		catch(Exception e)
		{
			System.out.println("Exception : getAttribValue :" + e.getMessage());
		}
		return attribValue;
	}

	private String getAttribValue(Node detailNode, String attribStr)
	{
		String attribValue = "";
		try
		{
			if( detailNode != null && detailNode.getAttributes() != null)
			{
				Node attribNode = detailNode.getAttributes().getNamedItem( attribStr );
				if( attribNode != null )
				{
					attribValue = checkNull( attribNode.getNodeValue() );
				}
			}
		}
		catch(Exception e)
		{
			System.out.println("Exception : getAttribValue :" + e.getMessage());
		}
		return attribValue;
	}
	//Changed by sumit on 08/02/13 reteriving selected value end.
	
	
	//Changeed by Pragyan WM2LSUN015 26/03/13 To check the item is hazardous or not.start
	private boolean  isHazardOusItem(String itemCode,Connection conn) throws ITMException
	{
		String sql = "";
		boolean isHazardous = false;
		ResultSet rs = null;
		PreparedStatement pstmt = null;	
		String hazardous = "N";
		try
		{
									
			sql = "SELECT HAZARDOUS FROM ITEM WHERE ITEM_CODE = ? ";
	
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,itemCode);
			rs = pstmt.executeQuery();
			
			if(rs.next())
			{
				hazardous = rs.getString("HAZARDOUS");
			}
			
			
			if( rs != null )
			{
				rs.close();
				rs = null;
			}
			if( pstmt != null )
			{
				pstmt.close();
				pstmt = null;
			}
			
			System.out.println("Checking Hazardous ["+hazardous+"]");
			if("Y".equalsIgnoreCase(hazardous))
			{
				System.out.println("Checking Hazardous 1["+hazardous+"]");
				isHazardous = true;
			}
		
		}
		catch(Exception e)
		{
			System.out.println("Exception in  == >"+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 e1)
			{
				throw new ITMException(e1);
			}
		}
	 return isHazardous;	
	}
	//Changeed by Pragyan WM2LSUN015 26/03/13 To check the item is hazardous or not.end
}
