package ibase.webitm.ejb.wms;
/*
*	Author: Pawan
*	Date:	23/08/11
*/
import ibase.ejb.*;
import ibase.system.config.*;
import ibase.utility.BaseLogger;
import ibase.webitm.ejb.*;
import ibase.webitm.ejb.dis.*;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;
import java.sql.Timestamp;
import java.io.*;
import java.rmi.RemoteException;
import java.sql.*;
import java.text.*;
import java.util.GregorianCalendar;
import java.util.Date;
import java.util.ArrayList;
import java.util.Properties;
import javax.ejb.*;
import javax.naming.InitialContext;
import javax.xml.parsers.*;
import org.w3c.dom.*;
import java.net.InetAddress;

import ibase.utility.CommonConstants;
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.util.Calendar;
import java.text.SimpleDateFormat;

@javax.ejb.Stateless
public class AsnIC extends ValidatorEJB implements AsnICRemote,AsnICLocal
{

	public String wfValData() throws RemoteException, ITMException
	{
		return "";
	}
	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();
		System.out.println("WFVALDATA 111111");
		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, editFlag, xtraParams );
			System.out.println ( "ErrString: " + errString);
		}
		catch(Exception e)
		{
			System.out.println ( "Exception: AsnIC: wfValData(String xmlString): " + e.getMessage() + ":" );
			errString = genericUtility.createErrorString(e);
			e.printStackTrace();
		}
		System.out.println ( "Returning from AsnIC wfValData" );
		return ( errString ); 
	}
	
	public String wfValData(Document dom, Document dom1, Document dom2, String objContext,String editFlag, String xtraParams ) throws RemoteException, ITMException
	{
		String errString = "",currTranId = null;
		Statement stmt = null;
		ResultSet rs = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
        GenericUtility genericUtility = GenericUtility.getInstance();
		
		DistCommon distComm = new DistCommon();
		
		StringBuffer errStringXml = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root><Errors>");
		String errorType = "", errCode = "";
		ArrayList errList = new ArrayList();
		ArrayList errFields = new ArrayList();
		String suppCode = "", qtyStr = "", priceList = "";
		double quantity = 0d, rate = 0d;
		NodeList parentList = null;
		NodeList childList = null;
		int noOfChilds ;
		String childNodeName = "";
		Node childNode =null;
		String userId = "";
        //Changed by Rohan on 08-07-13 for define variables.start
		String confirm = "";
		String purcOrder = "";
		int count = 0;
		//Changed by Rohan on 08-07-13 for define variables.end
		java.util.Date currDate = null;
		//changed by sankara on 07/06/14 for period stat
		java.util.Date tranDate = null;
		String siteCode = "",prdCode = "";
		String dbDateFormat = null;
		String applDateFormat = null;
		DateFormat dateFormat = null;
		String currDateStr = null;
		int cnt = 0;
		String columnValue = "";
		String sql = "", itemCode= "", stkOpt = "0";			
		int currentFormNo = 0;			
		AppConnectParm appConnect = new AppConnectParm();
		Properties p = null;
		InitialContext ctx = null;
		ConnDriver connDriver = null;
		//changed by sankara on 18/11/13 for black_list supp code
		String blackList = "";

		try
		{
			
			//ITMDBAccessRemote itmDBAccess = (ITMDBAccessRemote)ctx.lookup("ibase/ITMDBAccessEJB/remote");//Gulzar - 12/12/2011
			ITMDBAccessEJB itmDBAccess = new ITMDBAccessEJB();//Gulzar - 12/12/2011

			p = appConnect.getProperty();
			ctx = new InitialContext(p);
			connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			
			userId = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginCode" );
			 //changed by sankara on 07/06/14 for period stat
			SimpleDateFormat sdf1 = new SimpleDateFormat(genericUtility.getApplDateFormat());
			currDate = new java.util.Date();
			dbDateFormat = genericUtility.getDBDateFormat();
			applDateFormat = genericUtility.getApplDateFormat();
			dateFormat = new SimpleDateFormat(applDateFormat);
			currDateStr = dateFormat.format(currDate);
			SimpleDateFormat simpleDateFormatDB = new SimpleDateFormat(genericUtility.getDBDateFormat());
			Timestamp timestamp = Timestamp.valueOf(simpleDateFormatDB.format(currDate).toString() + " 00:00:00.0");
			
			if ( objContext != null && objContext.trim().length() > 0 )
			{
				currentFormNo = Integer.parseInt( objContext );
			}
			switch(currentFormNo)
			{
				case 1 :
				{
					parentList = dom.getElementsByTagName( "Detail" + currentFormNo );
					childList = parentList.item( 0 ).getChildNodes();
					noOfChilds = childList.getLength();
					for (int ctr = 0; ctr < noOfChilds; ctr++)
					{	
						childNode = childList.item( ctr );
						childNodeName = childNode.getNodeName();
						if ( childNode != null && childNode.getFirstChild() != null )
						{
							columnValue = childNode.getFirstChild().getNodeValue();
						}
						System.out.println(" columnName [" + childNodeName + "] columnValue [" + columnValue + "]");
						if ( "asn_no".equalsIgnoreCase( childNodeName ) )
						{
							if ( childNode.getFirstChild() == null )
							{
								// 26/10/11 manoharan all the errors should be returned so that warnings can be processed
								//errString = getErrorString("asn_no","NULLASNNUM",userId);
								//break;
								errList.add( "NULLASNNUM" );
								errFields.add( childNodeName.toLowerCase() );
								// end 26/10/11 manoharan all the errors should be returned so that warnings can be processed
							}			
							else 
							{
								// 16/10/11 manoharan
								currTranId = genericUtility.getColumnValue( "tran_id", dom );
								if (currTranId == null || currTranId.trim().length() == 0)
								{
									currTranId = "@@@@@@@@@@";
								}
								// end 16/10/11 manoharan
								columnValue =  childNode.getFirstChild().getNodeValue();
								if( columnValue == null || columnValue.trim().length() == 0 )
								{
									// 26/10/11 manoharan all the errors should be returned so that warnings can be processed
									//errString = itmDBAccess.getErrorString("asn_no","NULLASNNUM",userId);
									//break;
									errList.add( "NULLASNNUM" );
									errFields.add( childNodeName.toLowerCase() );
									// end 26/10/11 manoharan all the errors should be returned so that warnings can be processed
								}	
								else
								{
									if ( editFlag != null && "A".equalsIgnoreCase(editFlag.trim()) )
									{
										// 16/10/11 manoharan consider only other transactions
										//sql = " SELECT COUNT(*) AS CNT FROM ASN_HDR WHERE ASN_NO = ?";
										sql = " SELECT COUNT(*) AS CNT FROM ASN_HDR WHERE ASN_NO = ? AND tran_id <> ? ";
										// end 126/10/11 manoharan
										pstmt = conn.prepareStatement(sql);
										pstmt.setString(1, columnValue);
										// 16/10/11 manoharan
										pstmt.setString(2, currTranId);
										// end 16/10/11 manoharan
										
										rs = pstmt.executeQuery();
										if( rs.next() )
										{		
											cnt = rs.getInt("CNT");
										}
										if( rs != null )
										{
											rs.close();
											rs = null;
										}
										if( pstmt != null )
										{
											pstmt.close();
											pstmt = null;
										}
										if( cnt > 0 )
										{
											// 26/10/11 manoharan all the errors should be returned so that warnings can be processed
											//errString = getErrorString("asn_no","INVASNNUMR",userId);
											//break; 
											errList.add( "INVASNNUMR" );
											errFields.add( childNodeName.toLowerCase() );
											// end 26/10/11 manoharan all the errors should be returned so that warnings can be processed
										}
									}
								}
							}
						}
						else if ( "asn_date".equalsIgnoreCase( childNodeName ) )
						{
							if ( childNode.getFirstChild() == null )
							{
								// 26/10/11 manoharan all the errors should be returned so that warnings can be processed
								//errString = getErrorString("asn_date","NULLASNDAT",userId);
								//break;
								errList.add( "NULLASNDAT" );
								errFields.add( childNodeName.toLowerCase() );
								// end 26/10/11 manoharan all the errors should be returned so that warnings can be processed
							}
						}	
						else if ( "scac_code".equalsIgnoreCase( childNodeName ) )
						{
							if ( childNode.getFirstChild() == null )
							{
								// 26/10/11 manoharan all the errors should be returned so that warnings can be processed
								//errString = getErrorString("scac_code","NULLTRANCD",userId);
								//break;
								errList.add( "NULLTRANCD" );
								errFields.add( childNodeName.toLowerCase() );
								// end 26/10/11 manoharan all the errors should be returned so that warnings can be processed
							}			
							else 
							{
								columnValue =  childNode.getFirstChild().getNodeValue();	
								if( columnValue != null || columnValue.trim().length() != 0 )
								{
									sql = " SELECT COUNT(TRAN_CODE) AS CNT FROM TRANSPORTER WHERE SCAC_CODE = ?";
									System.out.println("sql ["+sql+" ]");
									System.out.println("columnValue ["+columnValue+" ]");
									pstmt = conn.prepareStatement(sql);
									pstmt.setString(1, columnValue);	
									rs = pstmt.executeQuery();
									if( rs.next() )
									{								
										cnt = rs.getInt("CNT");
									}
									if( cnt == 0 )
									{
										// 26/10/11 manoharan all the errors should be returned so that warnings can be processed
										//errString = getErrorString("tran_code","INVTRANCOD ",userId);
										errList.add( "INVTRANCOD" );
										errFields.add( childNodeName.toLowerCase() );
										// end 26/10/11 manoharan all the errors should be returned so that warnings can be processed
									}
									if( rs != null )
									{
										rs.close();
										rs = null;
									}
									if( pstmt != null )
									{
										pstmt.close();
										pstmt = null;
									}
								}
							}
						}					
						else if ( "purc_order".equalsIgnoreCase( childNodeName ) ) //Changed by sumit on date 27/09/11 for purchase validation start.
						{
							if(childNode.getFirstChild() != null)
							{
								columnValue =  childNode.getFirstChild().getNodeValue();
							}
							else
							{
								columnValue = "";
							}
							
							if( columnValue != null && columnValue.trim().length() != 0 )
							{
								//sql = " SELECT COUNT(PURC_ORDER) AS CNT FROM PORCP WHERE PURC_ORDER = ?";//Commented by Gulzar on 12/26/2011
								//Changed by Rohan on 08-07-13 to check purchse order confirm.start 
								//sql = " SELECT COUNT(PURC_ORDER) AS CNT FROM PORDER WHERE PURC_ORDER = ?";//Added by Gulzar on 12/26/2011
								sql = "SELECT CONFIRMED FROM PORDER WHERE PURC_ORDER = ? ";//Added by Gulzar on 12/26/2011
								System.out.println("sql ["+sql+" ]");
								System.out.println("columnValue ["+columnValue+" ]");
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, columnValue);	
								rs = pstmt.executeQuery();
								//Changed by Rohan on 08-07-13 to check purchse order confirm.start
								/*
								if( rs.next() )
								{								
									cnt = rs.getInt("CNT");
								}
								if( cnt == 0 )
								{
									// 26/10/11 manoharan all the errors should be returned so that warnings can be processed
									//errString = getErrorString("purc_order","INVPUROCOD ",userId);
									errList.add( "INVPUROCOD" );
									errFields.add( childNodeName.toLowerCase() );
									// end 26/10/11 manoharan all the errors should be returned so that warnings can be processed
								}
								*/
								if( rs.next() )
								{								
									confirm = rs.getString("CONFIRMED");
								}
								if(!"".equalsIgnoreCase(confirm) && !"Y".equalsIgnoreCase(confirm) )	
								{
									errList.add( "VTPUCNTCNF" );
									errFields.add( childNodeName.toLowerCase() );
								}
								//Changed by Rohan on 08-07-13 to check purchse order confirm.end
								if( rs != null )
								{
									rs.close();
									rs = null;
								}
								if( pstmt != null )
								{
									pstmt.close();
									pstmt = null;
								}
							}
						} //Changed by sumit on date 27/09/11 for purchase validation end.
						else if ( "supp_code".equalsIgnoreCase( childNodeName ) ) //Changed by sumit on date 27/09/11 for SUPPLIER CODE validation start.
						{
							if(childNode.getFirstChild() == null)
							{
								// 6/10/11 manoharan all the errors should be returned so that warnings can be processed
								//errString = getErrorString("supp_code","INVSUPPCOD ",userId);
								//break;
								errList.add( "INVSUPPCOD" );
								errFields.add( childNodeName.toLowerCase() );
								// end 26/10/11 manoharan all the errors should be returned so that warnings can be processed
							}
							System.out.println("Column Value for Supplier Code--------------["+columnValue+" ]");
						
							if( columnValue != null && columnValue.trim().length() > 0 )
							{
								sql = " SELECT COUNT(*) AS COUNT FROM SUPPLIER WHERE SUPP_CODE = ?";
								System.out.println("columnValue ["+columnValue+" ]");
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, columnValue);	
								rs = pstmt.executeQuery();
								
								if( rs.next() )
								{								
									cnt = rs.getInt("COUNT");
								}
								if( cnt == 0 )
								{
									// 26/10/11 manoharan all the errors should be returned so that warnings can be processed
									//errString = getErrorString("item_code","INVSUPPCOD ",userId);
									//break;
									errList.add( "INVSUPPCOD" );
									errFields.add( childNodeName.toLowerCase() );
									// end 26/10/11 manoharan all the errors should be returned so that warnings can be processed
								}
								rs.close(); rs = null;
								pstmt.close(); pstmt = null;
							}
							//changed by sankara on 18/11/13 required validation for black_list supp code start.
							sql = " SELECT BLACK_LIST FROM SUPPLIER WHERE SUPP_CODE = ? ";
							System.out.println("columnValue ["+columnValue+" ]");
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, columnValue);	
							rs = pstmt.executeQuery();
							if( rs.next() )
							{								
								blackList = checkNull (rs.getString("BLACK_LIST"));
								System.out.println("blackList:"+blackList);
							}
							if( blackList == null || blackList.trim().length() == 0 )  
							{
								blackList ="N";
							}
							if( blackList != null && blackList.trim().length() > 0 && blackList.trim().equalsIgnoreCase("Y") )
							{
								errList.add( "VTSUPPBL" );
								errFields.add( childNodeName.toLowerCase() );
							}
							rs.close(); 
							rs = null;
							pstmt.close();
							pstmt = null;
							//changed by sankara on 18/11/13 required validation for black_list supp code end.
						} //Changed by sumit on date 27/09/11 for purchase validation end.
						//Changed By Pragyan 05/Mar/14 check period status.start
						if( childNodeName.equalsIgnoreCase( "site_code" ))
						{ 
							if(childNode.getFirstChild() == null)
							{
								errString = getErrorString("site_code","VMSITSUPP",userId);
								errList.add( "VMSITSUPP" );
								errFields.add( childNodeName.toLowerCase() );
								break;								
							}
							else
							{	
								sql = "SELECT COUNT(*) AS COUNT FROM SITE WHERE SITE_CODE = ?";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1,columnValue);
								rs = pstmt.executeQuery();
								if( rs.next() )
								{
									count = rs.getInt("COUNT");
								}
								if(pstmt != null)
								{
									pstmt.close();
									pstmt = null;									 
								}

								if(rs != null)
								{
									rs.close();
									rs = null;
								}
								if( count == 0 )
								{
									//errString = getErrorString("site_code","INVSITECOD",userId);
									errList.add( "INVSITECOD" );
									errFields.add( childNodeName.toLowerCase() );
									//break;
								}
								else //added by kunal on 15-feb-14 for period code
								{
									
									siteCode = genericUtility.getColumnValue("site_code", dom);
									
									System.out.println("364 site code ="+siteCode);
									if (genericUtility.getColumnValue("tran_date", dom) != null)
									{
										tranDate = sdf1.parse(genericUtility.getColumnValue("tran_date", dom));
										sql = "select code from period where ?  between fr_date and to_date";
										pstmt = conn.prepareStatement(sql);
										pstmt.setDate(1, new java.sql.Date(tranDate.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)
										{
											//errString = getErrorString("site_code","VTSITEPD",userId);
											errList.add( "VTSITEPD" );
											errFields.add( childNodeName.toLowerCase() );
											break;
										}
										// nvo_functions_adv.nf_check_period('SAL',mdate1,msite_code)
										errCode = nfCheckPeriod("IC", tranDate, siteCode);
										System.out.println("425 Error Code = " + errCode);
										if (errCode != null && errCode.trim().length() > 0)
										{
											//errString = getErrorString("site_code",errCode,userId);
											//break;
											errList.add(errCode);
											errFields.add( childNodeName.toLowerCase() );
										}
									} 							 
								}//end 

							}
						}
						
						//Changed By Pragyan 05/Mar/14 check period status.end
					}// for end
				}//case 1 end	
				// case 2 for asn_det
				break;
      			case 2 :
				{
					//changed by dhanraj 20/08/14 for date calculation
					String flag="F";
					parentList = dom.getElementsByTagName( "Detail" + currentFormNo );
					childList = parentList.item( 0 ).getChildNodes();
					noOfChilds = childList.getLength();
					for (int ctr = 0; ctr < noOfChilds; ctr++)
					{	
						childNode = childList.item( ctr );
						childNodeName = childNode.getNodeName();
						if ( childNode != null && childNode.getFirstChild() != null )
						{
							columnValue = childNode.getFirstChild().getNodeValue();
						}
						System.out.println(" columnName [" + childNodeName + "] columnValue [" + columnValue + "]");
						if ( "item_code".equalsIgnoreCase( childNodeName ) )
						{
							if ( childNode.getFirstChild() == null )
							{
								// 26/10/11 manoharan all the errors should be returned so that warnings can be processed
								//errString = getErrorString("item_code","NULITEMCOD",userId);
								//break;
								errList.add( "NULITEMCOD" );
								errFields.add( childNodeName.toLowerCase() );
								// end 26/10/11 manoharan all the errors should be returned so that warnings can be processed
							}
							else 
							{
								sql = " SELECT COUNT(*) AS CNT FROM ITEM WHERE ITEM_CODE = ?";
								System.out.println("sql ["+sql+" ]");
								System.out.println("columnValue ["+columnValue+" ]");
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, columnValue);
								rs = pstmt.executeQuery();
								if( rs.next() )
								{								
									cnt = rs.getInt("CNT");
								}
								if( cnt == 0 )
								{
									// 26/10/11 manoharan all the errors should be returned so that warnings can be processed
									//errString = getErrorString("item_code","IVITMCDASN ",userId);
									//break;
									errList.add( "IVITMCDASN" );
									errFields.add( childNodeName.toLowerCase() );
									// end 26/10/11 manoharan all the errors should be returned so that warnings can be processed
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
								
								/*

								suppCode = genericUtility.getColumnValue( "supp_code", dom1 );
								qtyStr = genericUtility.getColumnValue( "quantity", dom );
								priceList = "";
								quantity = 0d;
								rate = 0d;
								if ( qtyStr != null && qtyStr.trim().length() > 0 )
								{
									quantity = Double.parseDouble(qtyStr);
								}

								sql = " SELECT PRICE_LIST FROM SUPPLIER WHERE SUPP_CODE = ?";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, suppCode);	
								rs = pstmt.executeQuery();
								
								if( rs.next() )
								{								
									priceList = rs.getString("PRICE_LIST");
								}
								rs.close(); rs = null;
								pstmt.close(); pstmt = null;
								

								rate = distComm.pickRate( priceList, currDateStr, columnValue, " ", "L", quantity, conn );
								if ( rate <= 0 )
								{
									// 26/10/11 manoharan all the errors should be returned so that warnings can be processed
									//errString = getErrorString("rate","INVRATE",userId);
									errList.add( "INVRATE" );
									errFields.add( childNodeName.toLowerCase() );
									// end 26/10/11 manoharan all the errors should be returned so that warnings can be processed
								}
								*/
								//start by shamim as discussed on 30 Aug by manoharan Sir
								
//								sql = " select rate  from pricelist where item_code = ?  and price_list=? ;
								//changed by shamim as per mail on 5/sep/12 by manoharan Sir
								
								//Changed by sumit on 24/01/13 this validation will now happen on confirmation start.
								/*sql="select rate  from pricelist where item_code = ?  and price_list= ? and ? >= eff_from and ? <= valid_upto  ";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, columnValue);
								pstmt.setString(2,checkNull(priceList));
								pstmt.setTimestamp(3,timestamp);
								pstmt.setTimestamp(4,timestamp );
								rs = pstmt.executeQuery();
								
								if( rs.next() )
								{								
									rate = rs.getDouble("rate");
								}
								rs.close(); rs = null;
								pstmt.close(); pstmt = null;
								if ( rate == 0 )
								{
									// 26/10/11 manoharan all the errors should be returned so that warnings can be processed
									//errString = getErrorString("rate","INVRATE",userId);
									errList.add( "INVRATE" );
									errFields.add( childNodeName.toLowerCase() );
									// end 26/10/11 manoharan all the errors should be returned so that warnings can be processed
								}*/
								//Changed by sumit on 24/01/13 this validation will now happen on confirmation end.							
								
								//end by shamim
							}
						}
						else if ( "quantity".equalsIgnoreCase( childNodeName ) )
						{
							if ( childNode.getFirstChild() == null )
							{
								// 26/10/11 manoharan all the errors should be returned so that warnings can be processed
								//errString = getErrorString("quantity","NULLQUANTY",userId);
								//break;
								errList.add( "NULLQUANTY" );
								errFields.add( childNodeName.toLowerCase() );
								// end 26/10/11 manoharan all the errors should be returned so that warnings can be processed
							}
							else 
							{
								int qty = Integer.parseInt( columnValue );
								if( qty <= 0 )
								{
									// 26/10/11 manoharan all the errors should be returned so that warnings can be processed
									//errString = itmDBAccess.getErrorString("quantity","INVQUNTITY",userId);
									//break;
									errList.add( "INVQUNTITY" );
									errFields.add( childNodeName.toLowerCase() );
									// end 26/10/11 manoharan all the errors should be returned so that warnings can be processed
								}
							} 
						}
						else if ( "lot_no".equalsIgnoreCase( childNodeName ) )
						{

							// 26/10/11 manoharan validate in case stk_opt = 2
							itemCode = genericUtility.getColumnValue( "item_code", dom );
							sql = "SELECT CASE WHEN STK_OPT IS NULL THEN '0' ELSE STK_OPT END AS STK_OPT FROM ITEM WHERE ITEM_CODE = ?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, itemCode);	
							rs = pstmt.executeQuery();
							
							if( rs.next() )
							{								
								stkOpt = rs.getString("STK_OPT");
							}
							rs.close(); rs = null;
							pstmt.close(); pstmt = null;
							if ("2".equals(stkOpt))
							{
								//
								//Changed By Amaya on 31/08/12 to check Null Validation
								//if ( columnValue == null || columnValue.trim().length() == 0)
								if ( childNode.getFirstChild() == null )
								{
									// 26/10/11 manoharan all the errors should be returned so that warnings can be processed
									//errString = getErrorString("lot_no","NULLLOTNUM",userId);
									//break;
									errList.add( "NULLLOTNUM" );
									errFields.add( childNodeName.toLowerCase() );
									// end 26/10/11 manoharan all the errors should be returned so that warnings can be processed
								}
							}
						}
						// 16/10/11 manoharan not required column to be removed
						/*if ( "supp_item__ref".equalsIgnoreCase( childNodeName ) )
						{
							if ( childNode.getFirstChild() == null )
							{
								errString = getErrorString("supp_item__ref","NULSUPPREF",userId);
								break;
							}
						}*/
						else if ( "exp_date".equalsIgnoreCase( childNodeName ) )
						{
							if ( childNode.getFirstChild() == null )
							{
								// 26/10/11 manoharan all the errors should be returned so that warnings can be processed
								//errString = getErrorString("exp_date","NULLEXPDT",userId);
								//break;
								errList.add( "NULLEXPDT" );
								errFields.add( childNodeName.toLowerCase() );
								// end 26/10/11 manoharan all the errors should be returned so that warnings can be processed
							}
						}
						//changed by dhanraj on 20/08/14 for mfg site code and mfg code start.
						else if ( "site_code__mfg".equalsIgnoreCase( childNodeName ) )
						{
							System.out.println("call Dhanraj site_code__mfg");
							itemCode = genericUtility.getColumnValue( "item_code", dom );
							sql = "SELECT supp_sour FROM siteitem WHERE ITEM_CODE = ? ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, itemCode);	
							rs = pstmt.executeQuery();
							
							if( rs.next() )
							{								
							    if("M".equalsIgnoreCase(rs.getString("supp_sour")))
							    {
							    	if ( childNode.getFirstChild() == null ){
							    		//blank error
							    		  errList.add( "VTNULLPMFG" );
									      errFields.add( childNodeName.toLowerCase() );
							    	}
							    }
							}else{
								sql = "SELECT supp_sour FROM ITEM WHERE ITEM_CODE = ? ";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, itemCode);	
								rs = pstmt.executeQuery();
								if( rs.next() )
								{
									 if("M".equalsIgnoreCase(rs.getString("supp_sour")))
								 	    {
										  if ( childNode.getFirstChild() == null ){
									    		//blank error
											  errList.add( "VTNULLPMFG" );
										      errFields.add( childNodeName.toLowerCase());
									    	}    	
									    }
								}
							}
							rs.close(); rs = null;
							pstmt.close(); pstmt = null;
							
						}
						else if ( "mfg_date".equalsIgnoreCase( childNodeName ) )
						{
							if ( childNode.getFirstChild() == null )
							{
	                                  //Null Allowed.  
							}else{
								String mfgDate = genericUtility.getColumnValue( "mfg_date", dom );
							    flag=DateValidation(mfgDate);
								//For check manfr date greator than sys date.
							    if(flag.equals("T")){
							    	System.out.println("mfg Date Less or equal than sys date");
							    	//for exp date greator than mfg date.
							    	String expDate = genericUtility.getColumnValue( "exp_date", dom );
									mfgDate = genericUtility.getColumnValue( "mfg_date", dom );
									flag=DateValidation(expDate,mfgDate);
	                                if(flag.equals("T")){//Then check exp date
	                                 System.out.println("Exp Date Greater than mfg date");
	                                }else{
										  errList.add( "VTMFGLXPDT" );//for exp date greater than mfg date.
									      errFields.add( childNodeName.toLowerCase() );
									}
							    	  
								}else{
									errList.add( "VTMFSYSPDT" );//mfg date less or = to sys date
									errFields.add( childNodeName.toLowerCase() );
								}
							   
							}
						}
						//changed by dhanraj on 20/08/14 for mfg site code and mfg code end.
						//Changed by Rohan on 09-07-13 for adding validation if purchase order and line no exist in porddet.start
						else if ( "line_no__ord".equalsIgnoreCase( childNodeName ) )
						{
							purcOrder = checkNull(genericUtility.getColumnValue( "purc_order", dom1 ));
					
							if((!"".equalsIgnoreCase(purcOrder) || purcOrder != null) && purcOrder.trim().length() > 0)
							{
								if ( childNode.getFirstChild() == null )
								{
									errList.add( "NULLPOLINE" );
									errFields.add( childNodeName.toLowerCase() );
								}
								else
								{	
									columnValue = "   " + columnValue; 
									columnValue = columnValue.substring(columnValue.length()-3);
									
									sql = "SELECT COUNT(*) AS PURCNT FROM PORDDET WHERE PURC_ORDER = ? AND LINE_NO = ?";
									pstmt = conn.prepareStatement(sql);
									pstmt.setString(1, purcOrder);
									pstmt.setString(2, columnValue);
									rs = pstmt.executeQuery();
									if( rs.next() )
									{	
										count = rs.getInt("PURCNT");
									}
									
									if(count <= 0)
									{
										errList.add( "VTINVPUORD" );
										errFields.add( childNodeName.toLowerCase() );
									}
									if( pstmt != null )
									{
										pstmt.close();
										pstmt = null;
									}
									if ( rs != null )
									{
										rs.close();
										rs = null;
									}
								}	
							}	
						}
						//Changed by Rohan on 09-07-13 for adding validation if purchase order and line no exist in porddet.end
					}// for end
				}//case 2 end	
			} //END switch
			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();
		}//END TRY
		catch(Exception e)
		{
			System.out.println("Exception in AsnICEJB  == >");
			e.printStackTrace();
		}
		finally
		{
			try
			{
				if( conn != null && !conn.isClosed() )
				{
					if( rs != null )
					{
						rs.close();
						rs = null;
					}
					if( pstmt != null )
					{
						pstmt.close();
						pstmt = null;
					}
					conn.close();
				}
			}
			catch(Exception e)
			{
				System.out.println( "Exception :AsnIC:wfValData :==>\n"+e.getMessage());
				throw new ITMException(e);
			}
		}
		return errString;
	}
	//changed by Dhanraj on 20/08/14 for date validation start.
		private String DateValidation(String strDate) throws ParseException{
			GenericUtility genericUtility = GenericUtility.getInstance();
			try {  

				String applDateFormat = genericUtility.getApplDateFormat();
				SimpleDateFormat formatter5=new SimpleDateFormat(applDateFormat);
				//SimpleDateFormat formatter5=new SimpleDateFormat("dd/MM/yy");
				java.util.Date mfgDate1 = (java.util.Date)formatter5.parse(strDate);

				java.sql.Date dd =new java.sql.Date(System.currentTimeMillis());
				String orcaleDate = formatter5.format(dd);
				java.util.Date sysDate = (java.util.Date)formatter5.parse(orcaleDate);

				if(sysDate.getTime()>=mfgDate1.getTime())
					return "T";
				else
					return "F"; 
			}
			catch (Exception e) {
				e.printStackTrace();
			}
			return "F";
		}
		private String DateValidation(String expDate,String mnfrDate) throws ParseException
		{
			GenericUtility genericUtility = GenericUtility.getInstance();
			try {   

				String applDateFormat = genericUtility.getApplDateFormat();
				SimpleDateFormat formatter=new SimpleDateFormat(applDateFormat);
				//SimpleDateFormat formatter=new SimpleDateFormat("dd/MM/yy");
				java.util.Date expDate1 = (java.util.Date)formatter.parse(expDate);

				java.util.Date manfrDate1 = (java.util.Date)formatter.parse(mnfrDate);

				if(expDate1.getTime()>manfrDate1.getTime())
					return "T";
				else
					return "F"; 
			}
			catch (Exception e) {
				e.printStackTrace();
			}
			return "F";
		}
		//changed by Dhanraj on 20/08/14 for date validation end.
	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;
	}

public String itemChanged() throws RemoteException, ITMException
{
	return "";
}
public String itemChanged(String xmlString, String xmlString1, String xmlString2, String objContext, String currentColumn, String editFlag, String xtraParams) throws RemoteException, ITMException
{
	Document dom = null;
	Document dom1 = null;
	Document dom2 = null;
	String errString = null;
	GenericUtility genericUtility = GenericUtility.getInstance();
	try
	{
		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 = itemChanged( dom, dom1, dom2, objContext, currentColumn, editFlag, xtraParams );
		System.out.println ( "ErrString :" + errString);
	}
	catch (Exception e)
	{
		System.out.println ( "Exception :AsnIC :itemChanged(String,String):" + e.getMessage() + ":" );
		errString = genericUtility.createErrorString(e);
	}
	System.out.println ( "returning from AsnIC itemChanged" );
	return errString;
}
public String itemChanged( Document dom, Document dom1, Document dom2, String objContext, String currentColumn, String editFlag, String xtraParams ) throws RemoteException,ITMException
{
	String chgTerm ="";
	ResultSet rs = null;
	String sql = "";
	String columnValue = "";
	String siteCode = "",lotNo = "";
	//Changed by Rohan on 09-07-13 for define variables.start
	String purcOrderLineNo = "";
	String purcOrder = "";
	//Changed by Rohan on 09-07-13 for define variables.end
	//Changed by sankara on 12-08-13 WM3ESUN007 for define variable start..
	String tranType = "";
	String fullName = "";
	//Changed by sankara on 12-08-13 WM3ESUN007 for define variable end..
	double itmQty=0;
  
	Connection conn = null;
	PreparedStatement pstmt = null;
	StringBuffer valueXmlString = new StringBuffer();
	int currentFormNo = 0;
	GenericUtility genericUtility = GenericUtility.getInstance();
	
	try
	{
		String userId = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginCode" );
		siteCode = getValueFromXTRA_PARAMS(xtraParams, "loginSiteCode");
		columnValue = genericUtility.getColumnValue( currentColumn, dom );
		
		AppConnectParm appConnect = new AppConnectParm();
		Properties p = appConnect.getProperty();
		InitialContext ctx = new InitialContext(p);
		
		InetAddress ownIP=InetAddress.getLocalHost();
		chgTerm = ownIP.getHostAddress();
			
		ITMDBAccessRemote itmDBAccess = (ITMDBAccessRemote)ctx.lookup("ibase/ITMDBAccessEJB/remote");
		
		System.out.println("<!@#> ItemChange  for ASn is Called objContext " +objContext);

		ConnDriver connDriver = null;
		connDriver = new ConnDriver();
		conn = connDriver.getConnectDB("DriverValidator");
		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" );
		
		
		switch ( currentFormNo )
		{
			case 1:
			{
				valueXmlString.append( "<Detail1>\r\n" );
				if( currentColumn.trim().equalsIgnoreCase( "itm_default" ) )
				{
					System.out.println("--------------------ITM_DEFAULT Called-----------------------");
					Calendar currentDate = Calendar.getInstance();
					SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
					String sysDate = sdf.format(currentDate.getTime());
					System.out.println("Now the date is :=>  " + sysDate);
					valueXmlString.append( "<tran_date><![CDATA[" ).append(sysDate).append( "]]></tran_date>\r\n" );
					valueXmlString.append( "<asn_date><![CDATA[" ).append(sysDate).append( "]]></asn_date>\r\n" );
					valueXmlString.append( "<site_code><![CDATA[" ).append(siteCode).append( "]]></site_code>\r\n" );
					//Changed by sumit on date 27/09/11 for default item changed for site description
					if( siteCode != null )
					{
						sql = " SELECT DESCR FROM SITE WHERE SITE_CODE	 = ?";
						System.out.println("columnValue -(site_code) ["+siteCode+" ]");
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, siteCode);
						rs = pstmt.executeQuery();
						if( rs.next() )
						{
							valueXmlString.append( "<descr><![CDATA[" ).append( checkNull( rs.getString( "DESCR" )) ).append( "]]></descr>\r\n" );
							System.out.println("------------------------APPENDING DESCRIPTION-------------------");
						}
						else
						{
							valueXmlString.append( "<descr><![CDATA[" ).append( "" ).append( "]]></descr>\r\n" );
							System.out.println("------------------------EMPTY DESCRIPTION-------------------");
						}
						if( rs != null )
						{
							rs.close();
							rs = null;
						}
						if( pstmt != null )
						{
							pstmt.close();
							pstmt = null;
						}
					}
					//Changed by sumit on date 27/09/11 for default item changed for site description
					valueXmlString.append( "<confirmed><![CDATA[" ).append("N").append( "]]></confirmed>\r\n" );					
					//valueXmlString.append( "<conf_date><![CDATA[" ).append(sysDate).append( "]]></conf_date>\r\n" );
					/*Change by sumit 
					valueXmlString.append( "<chg_user><![CDATA[" ).append(userId).append( "]]></chg_user>\r\n" );
					valueXmlString.append( "<chg_date><![CDATA[" ).append(sysDate).append( "]]></chg_date>\r\n" );
					valueXmlString.append( "<chg_term><![CDATA[" ).append(chgTerm).append( "]]></chg_term>\r\n" );
					
					*/
					valueXmlString.append( "<chg_user/>\r\n" );
					valueXmlString.append( "<chg_date/>\r\n" );
					valueXmlString.append( "<chg_term/>\r\n" );
					// change by sumit end.
				}
				else if( currentColumn.trim().equalsIgnoreCase( "tran_code" ) )
				{
					columnValue = genericUtility.getColumnValue("tran_code",dom);
					sql = " SELECT TRAN_NAME FROM TRANSPORTER WHERE TRAN_CODE = ?";
					System.out.println("columnValue ["+columnValue+" ]");
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, columnValue);
					rs = pstmt.executeQuery();
					if( rs.next() )
					{
						valueXmlString.append( "<tran_name><![CDATA[" ).append( checkNull( rs.getString( "TRAN_NAME" )) ).append( "]]></tran_name>\r\n" );
						System.out.println("------------------------APPENDING DESCRIPTION-------------------");
					}
					else
					{
						valueXmlString.append( "<tran_name><![CDATA[" ).append( "" ).append( "]]></tran_name>\r\n" );
						System.out.println("------------------------EMPTY DESCRIPTION-------------------");
					}
				}
				// Changed by sumit on date 26/09/11 for supplier code description item changed START.
			    else if( currentColumn.trim().equalsIgnoreCase( "supp_code" ) )
				{
					columnValue = genericUtility.getColumnValue("supp_code",dom);
					sql = " SELECT FULL_NAME, SUPP_TRAN_TYPE FROM SUPPLIER WHERE SUPP_CODE = ? ";
					System.out.println("columnValue ["+columnValue+" ]");
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, columnValue);
					rs = pstmt.executeQuery();
					//Changed by sankara on 12-08-13 for setting tran_type in asn_hdr WM3ESUN007 Start..	
				/*	if( rs.next() )
					{
						valueXmlString.append( "<full_name><![CDATA[" ).append( checkNull( rs.getString("FULL_NAME")) ).append( "]]></full_name>\r\n" );
						System.out.println("------------------------APPENDING DESCRIPTION-------------------");
					}
					else
					{
						valueXmlString.append( "<full_name><![CDATA[" ).append( "" ).append( "]]></full_name>\r\n" );
						System.out.println("------------------------EMPTY DESCRIPTION-------------------");
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;    */
					if( rs.next() )
					{
						fullName = rs.getString("FULL_NAME"); 		 
						tranType = rs.getString("SUPP_TRAN_TYPE"); 				
					}
					if( fullName != null   )
					{
						valueXmlString.append( "<full_name><![CDATA[" ).append( checkNull(fullName) ).append( "]]></full_name>\r\n" );
					}
					else
					{
						valueXmlString.append( "<full_name><![CDATA[" ).append( "" ).append( "]]></full_name>\r\n" );
					}
					if( tranType != null )
					{
						valueXmlString.append( "<tran_type><![CDATA[" ).append( checkNull( tranType )).append( "]]></tran_type>\r\n" );
					}
					else
					{
						tranType = findValueFromDisparm("999999","PORCP_TRAN_TYPE",conn);
						System.out.println("TRANTYPE====="+tranType);
						valueXmlString.append( "<tran_type><![CDATA[" ).append( tranType ).append( "]]></tran_type>\r\n" );		
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
				 //Changed by sankara on 12-08-13 for setting tran_type in asn_hdr WM3ESUN007 End..	
				}								
				// Changed by sumit on date 26/09/11 for suppliers code description item changed END.
				else if( currentColumn.trim().equalsIgnoreCase( "emp_code__aprv" ) )
				{
					columnValue = genericUtility.getColumnValue("emp_code__aprv",dom);
					sql = " SELECT EMP_FNAME,EMP_MNAME,EMP_LNAME FROM EMPLOYEE WHERE EMP_CODE = ?";
					System.out.println("columnValue ["+columnValue+" ]");
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, columnValue);
					rs = pstmt.executeQuery();
					if( rs.next() )
					{
						//Changed by sankara on 12/08/13 all ready declared in method...
						//String fullName = checkNull(rs.getString("EMP_FNAME"))+checkNull(rs.getString("EMP_MNAME"))+checkNull(rs.getString("EMP_LNAME"));
						fullName = checkNull(rs.getString("EMP_FNAME"))+checkNull(rs.getString("EMP_MNAME"))+checkNull(rs.getString("EMP_LNAME"));
						valueXmlString.append( "<emp_fname><![CDATA[" ).append( checkNull( fullName )).append( "]]></emp_fname>\r\n" );
						System.out.println("------------------------APPENDING DESCRIPTION-------------------");
					}
					else
					{
						valueXmlString.append( "<emp_fname><![CDATA[" ).append( "" ).append( "]]></emp_fname>\r\n" );
						System.out.println("------------------------EMPTY DESCRIPTION-------------------");
					}
				}
				
				valueXmlString.append( "</Detail1>\r\n" );
				System.out.println("------------------------COMPLETE DESCRIPTION-------------------");
				break;
			}//case 1 end here
			// case 2 for asn_det itemchanged
			case 2:
			{
				valueXmlString.append( "<Detail2>\r\n" );
				//Chnaged by Rohan on 07-09-13 for make line no ord protected if purchase order present in 1st form.start
				if( currentColumn.trim().equalsIgnoreCase( "itm_default" ) || currentColumn.trim().equalsIgnoreCase( "itm_defaultedit" ))
				{
					
					purcOrder = checkNull(genericUtility.getColumnValue("purc_order",dom1));
					purcOrderLineNo = checkNull(genericUtility.getColumnValue("line_no__ord",dom));
					
					if(("".equalsIgnoreCase(purcOrder.trim()) || purcOrder == null ) && purcOrder.trim().length() <= 0)
					{
						
						valueXmlString.append( "<line_no__ord protect ='1'><![CDATA[" ).append("").append( "]]></line_no__ord>\r\n" );
						
					}
					else
					{
						valueXmlString.append( "<line_no__ord protect ='0'><![CDATA[" ).append("").append( "]]></line_no__ord>\r\n" );
					}
					// Changed by Dhanraj 20/08/14 add default value start.
					if( currentColumn.trim().equalsIgnoreCase( "itm_default" )){
					
					   valueXmlString.append( "<shelf_life__type><![CDATA[" ).append("E").append( "]]></shelf_life__type>\r\n" );
					}
					// Changed by Dhanraj 20/08/14 add default value end.
				}
				//Chnaged by Rohan on 07-09-13 for make line no ord protected if purchase order present in 1st form.end
				else if( currentColumn.trim().equalsIgnoreCase( "item_code" )  ||currentColumn.trim().equalsIgnoreCase( "quantity" ) || currentColumn.trim().equalsIgnoreCase( "lot_no" ))
				{
					
					
					double perPalletVolume =  Double.parseDouble(findValueFromDisparm("999999","PER_PALLET__VOLUME",conn));
					double perPalletWeight =  Double.parseDouble(findValueFromDisparm("999999","PER_PALLET__WEIGHT",conn));
					
					columnValue = genericUtility.getColumnValue("item_code",dom);
					lotNo = genericUtility.getColumnValue("lot_no",dom);
					sql = " SELECT DESCR FROM ITEM WHERE ITEM_CODE = ?";
					System.out.println("columnValue ["+columnValue+" ]");
					System.out.println("-------<!@#>------------------------<!@#>-----------------");
					if (lotNo == null)
					{
						lotNo = "";
					}
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, columnValue);
					rs = pstmt.executeQuery();
					if( rs.next() )
					{
						valueXmlString.append( "<descr><![CDATA[" ).append( checkNull( rs.getString( "DESCR" )) ).append( "]]></descr>\r\n" );
						System.out.println("------------------------APPENDING DESCRIPTION-------------------");
					}
					else
					{
						valueXmlString.append( "<descr><![CDATA[" ).append( "" ).append( "]]></descr>\r\n" );
						System.out.println("------------------------EMPTY DESCRIPTION-------------------");
					}
					if(!("".equals(checkNull(genericUtility.getColumnValue("quantity",dom)))))
					{
						System.out.println("-------<!@#>-----------------quantity-------------------");
						itmQty=Double.parseDouble(genericUtility.getColumnValue("quantity",dom));
						valueXmlString.append( "<no_pallet><![CDATA[" ).append(findNosOfPallet(genericUtility.getColumnValue("item_code",dom),lotNo, itmQty ,perPalletVolume,perPalletWeight, conn)).append( "]]></no_pallet>\r\n" );
						System.out.println("------------------------APPENDING DESCRIPTION-------------------");
						
					}

				} /*else if( currentColumn.trim().equalsIgnoreCase( "quantity" )  )
					{
					System.out.println("-------<!@#>-----------------quantity-------------------");
					
						columnValue = genericUtility.getColumnValue("item_code",dom);
						if(!("".equals(checkNull(genericUtility.getColumnValue("quantity",dom)))))
						{
							itmQty=Double.parseDouble(genericUtility.getColumnValue("quantity",dom));
							valueXmlString.append( "<no_pallet><![CDATA[" ).append(findNosOfPallet(genericUtility.getColumnValue("item_code",dom),itmQty , conn)).append( "]]></no_pallet>\r\n" );
							System.out.println("------------------------APPENDING DESCRIPTION-------------------");
							
						}
					}*/
	            //Changed by Rohan on 09-07-13 for based on purchase order and line no set item code,quantity,packsize and item description.start
				 if( currentColumn.trim().equalsIgnoreCase( "line_no__ord"))
				 {
					purcOrderLineNo = genericUtility.getColumnValue("line_no__ord",dom);
					purcOrderLineNo = "   " + purcOrderLineNo; 
					purcOrderLineNo = purcOrderLineNo.substring(purcOrderLineNo.length()-3);
					purcOrder = genericUtility.getColumnValue("purc_order",dom1);
					sql = " SELECT P.ITEM_CODE,P.QUANTITY,K.SHIPPER_SIZE,I.DESCR FROM PORDDET P,ITEM I,ITEM_LOT_PACKSIZE K  WHERE PURC_ORDER = ? AND LINE_NO = ? "
						 + " AND P.ITEM_CODE = I.ITEM_CODE AND P.ITEM_CODE = K.ITEM_CODE ";
							
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, purcOrder);
					pstmt.setString(2, purcOrderLineNo);
					rs = pstmt.executeQuery();
					if( rs.next() )
					{
						valueXmlString.append( "<descr><![CDATA[" ).append( checkNull( rs.getString( "DESCR" )) ).append( "]]></descr>\r\n" );
						valueXmlString.append( "<item_code protect ='1'><![CDATA[" ).append( checkNull( rs.getString( "ITEM_CODE" )) ).append( "]]></item_code>\r\n" );
						valueXmlString.append( "<quantity><![CDATA[" ).append( checkNull( rs.getString( "QUANTITY" )) ).append( "]]></quantity>\r\n" );
						valueXmlString.append( "<pack_size protect ='1'><![CDATA[" ).append( checkNull( rs.getString( "SHIPPER_SIZE" )) ).append( "]]></pack_size>\r\n" );
						valueXmlString.append( "<line_no__ord><![CDATA[" ).append( checkNull(purcOrderLineNo)).append( "]]></line_no__ord>\r\n" );
						
					}
					else
					{
						valueXmlString.append( "<descr><![CDATA[" ).append("").append( "]]></descr>\r\n" );
						valueXmlString.append( "<item_code protect ='1'><![CDATA[" ).append("") .append( "]]></item_code>\r\n" );
						valueXmlString.append( "<quantity><![CDATA[" ).append("").append( "]]></quantity>\r\n" );
						valueXmlString.append( "<pack_size protect ='1'><![CDATA[" ).append("").append( "]]></pack_size>\r\n" );
					}
				 }
				//Changed by Rohan on 09-07-13 for based on purchase order and line no set item code,quantity,packsize and item description.end
				valueXmlString.append( "</Detail2>\r\n" );
				System.out.println("------------------------COMPLETE DESCRIPTION-------------------");
				break;
			}//case 2 end here
		}//switch statement ends here
	}//try block ends here
	catch(Exception e)
	{
		System.out.println( "Exception :AsnHdr :itemChanged(Document,String):" + e.getMessage() + ":" );
		valueXmlString = valueXmlString.append( genericUtility.createErrorString( e ) );
	}
	finally
	{
		try
		{
			if( conn != null && ! conn.isClosed() )
			{
				if( rs != null )
				{
					rs.close();
					rs = null;
				}
				if( pstmt != null )
				{
					pstmt.close();
					pstmt = null;
				}
				
				conn.close();
			}
			//System.out.println("------------------------INSIDE FINALLY-------------------");
		}
		catch(Exception e)
		{
			System.out.println( "Exception :AsnHdr:itemChanged :==>\n"+e.getMessage());
			throw new ITMException(e);
		}
	}
	valueXmlString.append( "</Root>\r\n" );	
	System.out.println( "\n****ValueXmlString :" + valueXmlString.toString() + ":********" );
	return valueXmlString.toString();
}

private String checkNull( String input )
{
	if ( input == null )
	{
		input = "";
	}
	return input;
}
private int findNosOfPallet(String itemCode, String lotNo, double quantity ,double perPalletVolume , double perPalletWeight,Connection conn)  throws RemoteException,ITMException
{
	int findNosOfPallet=0;
	int nosOfPalletPerVolume=0;
	int nosOfPalletPerWeight=0;
	int perPalletArticle=0;
	int perPalletArticleVolume=0;
	int perPalletArticleWeight=0;
	
	String sql="";
	PreparedStatement pstmt = null;
	ResultSet rs =null;
	double shipperSize =0.0;
	double grossWeight =0.0;
	double netWeight =0.0;
	double length=0.0;
	double width=0.0;
	double height=0.0;
	Double nosOfPallet=0.0;
	//Changed by Ameya on 19/07/2012.start
	double nosOfPalletTemp=0.0;
	int totCaseCount =0;
	double quantityIntoShippersizeDouble=0.0;
	//Changed by Ameya on 19/07/2012.end
	
	sql="select shipper_size, gross_weight, net_weight, length, width ,height from item_lot_packsize WHERE ITEM_CODE = ? AND LOT_NO__FROM <= ? AND LOT_NO__TO >= ? ";
	try {
		pstmt =conn.prepareStatement(sql);
		pstmt.setString(1,itemCode);
		System.out.println("lotNo="+lotNo);
		if(lotNo != null && lotNo.trim().length() > 0)
		{
			System.out.println("lotNo is not null ");		
			pstmt.setString(2,lotNo);
			pstmt.setString(3,lotNo);
		}
		else
		{
			System.out.println("lotNo is null ");			
			pstmt.setString(2,"00");
			pstmt.setString(3,"ZZ");
			return 0; // added by Ashish Sonawane for updating no of LPN is zero if lot no is not specified
		}
		rs=pstmt.executeQuery();
		if(rs.next())
		{
			shipperSize=rs.getDouble(1);
			grossWeight =rs.getDouble(2);
			netWeight=rs.getDouble(3);
			length=rs.getDouble(4);
			width=rs.getDouble(5);
			height=rs.getDouble(6);
		}
		rs.close();
		pstmt.close();
		rs=null;
		pstmt =null;
		System.out.println("shipperSize="+shipperSize);
		System.out.println("grossWeight="+grossWeight);
		System.out.println("quantity="+quantity);
		
		//Changed by Ameya on 27/07/2012.start
		
		totCaseCount = (int)(quantity / shipperSize);
		
		quantityIntoShippersizeDouble = (quantity % shipperSize);
		
		if(quantityIntoShippersizeDouble > 0)
		{
			totCaseCount++;
		}
		
		//perPalletArticleWeight= (int)( (int)(perPalletWeight*shipperSize)/grossWeight);
		perPalletArticleWeight= (int)(((int)(((int)( (int)(perPalletWeight*shipperSize)/grossWeight))/shipperSize))*shipperSize);
		System.out.println("perPalletArticleWeight="+perPalletArticleWeight);
			
		nosOfPallet=(quantity/perPalletArticleWeight);
		
		System.out.println("nosOfPallet="+nosOfPallet);
		
		//nosOfPallet=( (int)(totCaseCount  * grossWeight) )/(Double.parseDouble(findValueFromDisparm("999999","PER_PALLET__WEIGHT",conn)));
		//nosOfPallet=( (int)(totCaseCount  * grossWeight) )/perPalletWeight;
		
		nosOfPalletPerWeight=(new Double((Math.floor(nosOfPallet)))).intValue();
		nosOfPalletTemp=nosOfPalletPerWeight;
		
		//Changed By Sumit 01/10/12 for re write the code for Amaya
		//if(nosOfPallet!=nosOfPalletTemp)
		// changed by shamim as per discussion with manoharan sir on 30 Aug
		System.out.println("quantity % perPalletArticleWeight" +quantity % perPalletArticleWeight);
		//if(nosOfPallet!=nosOfPalletTemp && (quantity % perPalletArticleWeight) >= shipperSize)
		if(nosOfPallet!=nosOfPalletTemp)
		{
			nosOfPalletPerWeight=nosOfPalletPerWeight+1;
			
			if(quantity < perPalletArticleWeight)
			{
				System.out.println("CHECK BY PRAGYAN1 WEIGHT[" +(quantity%shipperSize) + "]");
				if(!(quantity < shipperSize) && (quantity%shipperSize) > 0)
				{
					nosOfPalletPerWeight=nosOfPalletPerWeight+1;
				}
			}
			
			if(quantity > perPalletArticleWeight)
			{
				double tempQty = quantity - (perPalletArticleWeight * nosOfPalletTemp);
				System.out.println("CHECK BY PRAGYAN1tempQty WEIGHT[" +tempQty + "]nosOfPalletTemp["+nosOfPalletTemp);
				if(tempQty > 0 && !(tempQty < shipperSize) && (tempQty%shipperSize) > 0)
				{
					nosOfPalletPerWeight=nosOfPalletPerWeight+1;
				}
			}
			
		}
		System.out.println("<nosOfPalletPerWeight after floor [" +nosOfPalletPerWeight + "]");
		
		perPalletArticleVolume= (int)(((int)(((int)(perPalletVolume*shipperSize/ (length*width*height) ) )/shipperSize))*shipperSize) ;
		
		//quantity
		System.out.println("perPalletArticleVolume="+perPalletArticleVolume);
		
		nosOfPallet=(quantity/perPalletArticleVolume);
		
		System.out.println("nosOfPallet="+nosOfPallet);
		
		//nosOfPallet=( (int) (totCaseCount * length * width * height) )/(Double.parseDouble(findValueFromDisparm("999999","PER_PALLET__VOLUME",conn)));
		//nosOfPallet=( (int) (totCaseCount * length * width * height) )/perPalletVolume;
		
		nosOfPalletPerVolume = (new Double((Math.floor(nosOfPallet)))).intValue();
		nosOfPalletTemp = nosOfPalletPerVolume;
		//if(nosOfPallet!=nosOfPalletTemp) 
		// changed by shamim as per discussion with manoharan sir on 30 Aug
		System.out.println("(quantity % perPalletArticleVolume)" +(quantity % perPalletArticleVolume));
		//Changed by sumit on 28/09/12 changed to previous code as per client.
		//if(nosOfPallet!=nosOfPalletTemp && (quantity % perPalletArticleVolume)>= shipperSize)
		if(nosOfPallet!= nosOfPalletTemp)
		{
			nosOfPalletPerVolume = nosOfPalletPerVolume + 1;
			
			if(quantity < perPalletArticleVolume)
			{
				System.out.println("CHECK BY PRAGYAN1 [" +(quantity%shipperSize) + "]");
				if(!(quantity < shipperSize) && (quantity%shipperSize) > 0)
				{
					nosOfPalletPerVolume = nosOfPalletPerVolume + 1;
				}
			}
			
			if(quantity > perPalletArticleVolume)
			{
				double tempQty = quantity - (perPalletArticleVolume * nosOfPalletTemp);
				System.out.println("CHECK BY PRAGYAN1tempQty [" +tempQty + "]nosOfPalletTemp["+nosOfPalletTemp);
				if(tempQty > 0 && !(tempQty < shipperSize) && (tempQty%shipperSize) > 0)
				{
					nosOfPalletPerVolume = nosOfPalletPerVolume + 1;
				}
			}
		}
		System.out.println("nosOfPalletPerVolume AFTER FLOR [" +nosOfPalletPerVolume + "]");
		if(nosOfPalletPerWeight > nosOfPalletPerVolume )	//Changed by Ameya on 19/07/2012
		{
			findNosOfPallet = nosOfPalletPerWeight;
		}
		else
		{
			findNosOfPallet = nosOfPalletPerVolume;
		}
		//Changed by Ameya on 27/07/2012.end
		System.out.println("<!@#>findNosOfPallet" +findNosOfPallet);
		
		
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		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();
		}
	}	
	
	
	return findNosOfPallet; 
}
//find value from disparm
private String findValueFromDisparm(String prdCode,String varName,Connection conn)  throws RemoteException,ITMException
{
	String varValue="";
	String sql="";
	PreparedStatement pstmt = null;
	ResultSet rs =null;
	double shipperSize =0.0;
	double grossWeight =0.0;
	double netWeight =0.0;
	sql="select var_value  from disparm where var_name =? and prd_code = ?";
	
	try {
		pstmt =conn.prepareStatement(sql);
		pstmt.setString(1,varName);
		pstmt.setString(2,prdCode);
		rs=pstmt.executeQuery();
		if(rs.next())
		{
			varValue=rs.getString(1);
			System.out.println("<!@#> varValue " +varValue);
		}
		rs.close();
		pstmt.close();
		rs=null;
		pstmt =null;
		
		
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
		throw new ITMException(e);
	}
	System.out.println("varName for "+varName+"varvalue"+varValue);
	
	return varValue; 
}


 
}