/**
 * PURPOSE : Validation and Item Change implementation for purchase order and receipt (PORCP) component.
 * AUTHOR : BALU
 */
package ibase.webitm.ejb.wms;

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

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 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 PoRcpIC extends ValidatorEJB implements PoRcpICRemote, PoRcpICLocal  
{
	// Validation Code. Start	
	/**
	 * 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 xmlCurrFrmString contains the current form data in XML format
	 * @param xmlHdrString contains all the header information in the XML format
	 * @param xmlAllString 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 xmlCurrFrmString, String xmlHdrFrmString,String xmlAllFrmString, String objContext, String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		Document currDom = null;
		Document hdrDom = null;
		Document allDom = null;
		String errString = "";

		try
		{			
			if(xmlCurrFrmString != null && xmlCurrFrmString.trim().length()!=0)
			{
				currDom = parseString(xmlCurrFrmString); 
			}
			else
			{
				System.out.println("xmlCurrFrmstring is null");
			}
			if(xmlHdrFrmString != null && xmlHdrFrmString.trim().length()!=0)
			{
				hdrDom = parseString(xmlHdrFrmString); 
			}
			else
			{
				System.out.println("xmlHdrFrmString is null");
			}
			if(xmlAllFrmString != null && xmlAllFrmString.trim().length()!=0)
			{
				allDom = parseString(xmlAllFrmString);
			}
			else
			{
				System.out.println("xmlAllFrmstring is null");
			}
			System.out.println("Before call valdata");
			errString = wfValData(currDom,hdrDom,allDom,objContext,editFlag,xtraParams);
		}
		catch(Exception e)
		{
			System.out.println("Exception : [SavexWizDataEJB][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 = "";
		String suppCode = "";
		String lpnNo = "";

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

		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 singleLotSl = "";
		String itemLotOpt = "";
		String itemCode = "";
		String lotNo = "";
		int count = 0;
		double quantity = 0d;
		String columnValue = "";
		String sql = "";	
		String purcOrder = "";
		String lotSl = "";
		String locCode = "";
		String asnNo = "";
		String siteDescr = "";
		String rowCountStr = "";
		String confirmed = "";
		String grlInvStat = "";
		String partialGrlLoc="";
		String locInvStat = "";
		String altLotNo="";
		String pordSiteCode = "";
		java.util.Date tranDate = null;
		String prdCode = "";
		int currentFormNo = 0, cnt = 0;
		int counter = 0;

		AppConnectParm appConnect = new AppConnectParm();
		Properties p = null;
		NodeList parentList = null;
		NodeList childList = null;

		int noOfChilds = 0;
		int noOfParents = 0;
		String childNodeName = "", stkOpt = "0";

		Node childNode =null;
		ConnDriver connDriver = null;
		String userId = "", asnReqd = "N";

		DistCommon distComm = new DistCommon();

		ResultSet rsLotMaster = null ; //Added by Ashish
		ResultSet rsAltLotMaster = null ; //Added by Ashish
		PreparedStatement pstmtLotMaster=null; //added by Ashish
		PreparedStatement pstmtAltLotMaster=null; //added by Ashish
		PreparedStatement pstmtLocCode =null; //added by Ashish
		ResultSet rsLocCode =null; //added by Ashish
		//String tranID = "";
		
		double pndRcpQty = 0.0;

		try
		{

			connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverValidator");
			connDriver = null;
			p = appConnect.getProperty();
			SimpleDateFormat sdf1 = new SimpleDateFormat(genericUtility.getApplDateFormat());
			userId = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginCode" );
			//Changes by Dadaso pawar on 19/FEB/15 [Start]
			CommonWmsUtil commonWmsUtility =CommonWmsUtil.getInstance();
			String loginSite = getValueFromXTRA_PARAMS(xtraParams, "loginSiteCode");
			loginSite = loginSite == null ? "" :loginSite.trim();
			System.out.println("loginSite1111---------------->>["+loginSite+"]");	
			//Changes by Dadaso pawar on 19/FEB/15 [End]
			if ( objContext != null && objContext.trim().length() > 0 )
			{
				currentFormNo = Integer.parseInt( objContext );
			}

			switch(currentFormNo)
			{
			case 1 :
			{
				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 ( "asn_no".equalsIgnoreCase( childNodeName ) )
					{							
						asnNo = genericUtility.getColumnValue("asn_no", currDom );

						if( asnNo == null || asnNo.trim().length() == 0 )
						{
							// 26/10/11 manoharan all the errors should be returned so that warnings can be processed
							//errString = getErrorString("asn_no","NULLASNNO",userId);
							//break;
							errList.add( "NULLASNNO" );
							errFields.add( childNodeName.toLowerCase() );
							// end 26/10/11 manoharan all the errors should be returned so that warnings can be processed
						}
						else
						{
							sql = "SELECT CONFIRMED FROM ASN_HDR WHERE ASN_NO = ?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, asnNo);

							rs = pstmt.executeQuery();
							if (rs.next())
							{
								confirmed = checkNull(rs.getString("CONFIRMED"));
							}
							else
							{
								// 26/10/11 manoharan all the errors should be returned so that warnings can be processed
								//errString = getErrorString( "asn_no" , "ASNNOTEXT" , userId );
								//break;
								errList.add( "ASNNOTEXT" );
								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;
							if ( !"Y".equalsIgnoreCase(confirmed.trim()) )
							{
								// 26/10/11 manoharan all the errors should be returned so that warnings can be processed
								//errString = getErrorString( "asn_no" , "UNCONFASN" , userId );
								//break;
								errList.add( "UNCONFASN" );
								errFields.add( childNodeName.toLowerCase() );
								// end 26/10/11 manoharan all the errors should be returned so that warnings can be processed
							}
							//changes made by gulzar - 25/11/11 - 
							//if porcp is confirmed for the entered asn_no then system should validate
							confirmed = "";
							//Changed By Pragyan 
							
							sql = "SELECT SUM(QUANTITY-DLV_QTY) AS QTY FROM PORDDET WHERE PURC_ORDER IN (SELECT PURC_ORDER FROM ASN_HDR  WHERE ASN_NO = ?)  ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, asnNo);
							
							rs = pstmt.executeQuery();
							if (rs.next())
							{
								//confirmed = checkNull(rs.getString("CONFIRMED"));
								pndRcpQty = rs.getDouble(1);
							}
							
							if(!(pndRcpQty > 0))
							{
								errList.add( "PORCPCONF" );
								errFields.add( childNodeName.toLowerCase() );
							}
						
							/*sql = "SELECT CONFIRMED FROM PORCP WHERE ASN_NO = ? ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, asnNo);

							rs = pstmt.executeQuery();
							if (rs.next())
							{
								confirmed = checkNull(rs.getString("CONFIRMED"));
							}
							if ( "Y".equalsIgnoreCase(confirmed.trim()) )
							{
								errList.add( "PORCPCONF" );
								errFields.add( childNodeName.toLowerCase() );

							}*/
							//End changes gulzar - 25/11/11
						}
					}
					else if ( "loc_code".equalsIgnoreCase( childNodeName ) )
					{
						locCode = genericUtility.getColumnValue("loc_code", currDom );
						if( locCode == null || locCode.trim().length() == 0 )				
						{

							errList.add( "NULLLOCCD" );
							errFields.add( childNodeName.toLowerCase() );

						}
						else
						{
							grlInvStat = checkNull(distComm.getDisparams("999999","GRL_INVSTAT",conn));
							//Added By Manoj dtd 17/09/2012 to allow partial grl location discussed with manohar Sir
							partialGrlLoc = checkNull(distComm.getDisparams("999999","PGRL_INVSTAT",conn));
							String sqlLocCode = "SELECT INV_STAT FROM LOCATION WHERE LOC_CODE = ?";
							pstmtLocCode = conn.prepareStatement(sqlLocCode);
							pstmtLocCode.setString(1, locCode);

							rsLocCode = pstmtLocCode.executeQuery();
							if( rsLocCode.next() )
							{
								locInvStat = checkNull(rsLocCode.getString("INV_STAT"));
								System.out.println("Manohar locInvStat [" + locInvStat + "] grlInvStat [" + grlInvStat + "] partialGrlLoc [" + partialGrlLoc + "]");
								if ( !locInvStat.trim().equalsIgnoreCase(grlInvStat.trim()) && !locInvStat.trim().equalsIgnoreCase(partialGrlLoc.trim()) )
								{
									errList.add( "VTLOCCODE9" );
									errFields.add( childNodeName.toLowerCase() );
								}
								
							}
							else
							{

								errList.add( "INVLOCCODE" );
								errFields.add( childNodeName.toLowerCase() );

							}
							//Changes by Dadaso pawar on 19/FEB/15 [Start]
							if(!commonWmsUtility.isValFacLocation(locCode, loginSite, conn))
							{
								System.out.println("indise facility........");
								errList.add( "VMFACI2" ); // 'Diffrent Loc Code from location and site' (set same error Code @base)
								errFields.add( childNodeName.toLowerCase() );								
							}
							//Changes by Dadaso pawar on 19/FEB/15 [End]
							rsLocCode.close();
							rsLocCode= null;
							pstmtLocCode.close();
							pstmtLocCode = null;

						}
					}
				}// for loop end
			}//case 2 end
			break;
			case 2 :
			{
				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 ( "site_code".equalsIgnoreCase( childNodeName ) )
					{							
						siteCode = genericUtility.getColumnValue("site_code", currDom );
						
						if( siteCode == null || siteCode.trim().length() == 0 )
						{
							// 26/10/11 manoharan all the errors should be returned so that warnings can be processed
							//errString = getErrorString("site_code","NULLSITE",userId);
							//break;
							errList.add( "NULLSITE" );
							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 COUNT FROM SITE WHERE SITE_CODE = ?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, siteCode);

							rs = pstmt.executeQuery();
							if (rs.next())
							{
								cnt = rs.getInt("COUNT");
							}
							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( "site_code" , "SITENOTEXT" , userId );
								//break;
								errList.add( "SITENOTEXT" );
								errFields.add( childNodeName.toLowerCase() );
								// end 26/10/11 manoharan all the errors should be returned so that warnings can be processed
							}
							//Changed By Pragyan to add validation for diffrent site code in purchase order and receipt.start
							else
							{
								
								asnNo = genericUtility.getColumnValue( "asn_no", hdrDom );
								//SELECT TRAN_CODE,TRANS_MODE,PORD_TYPE FROM PORDER WHERE PURC_ORDER =
								sql = "SELECT P.SITE_CODE__DLV FROM ASN_HDR A,PORDER P WHERE " 
										+" A.PURC_ORDER = P.PURC_ORDER  AND A.ASN_NO = ?";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, asnNo);
								rs = pstmt.executeQuery();

								if(rs.next())
								{
									pordSiteCode = checkNull(rs.getString("SITE_CODE__DLV"));
								}
								if (rs != null)
								{
									rs.close();
									rs = null;
								}
								if (pstmt != null)
								{
									pstmt.close();
									pstmt = null;
								}
								
								if(!pordSiteCode.equalsIgnoreCase(siteCode))
								{
									errList.add( "VTCPORDS" );
									errFields.add( childNodeName.toLowerCase() );
								}
								else //added by kunal on 15-feb-14 for period code
								{
									
									
									
									System.out.println("364 site code ="+siteCode);
									if (genericUtility.getColumnValue("tran_date", currDom) != null)
									{
										tranDate = 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(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)
										{
											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)
										{
											errList.add(errCode );
											errFields.add( childNodeName.toLowerCase() );
										
										}
									} 							 
								}//end 

								
							}
							//Changed By Pragyan to add validation for diffrent site code in purchase order and receipt.end
						}
					}
					else if ( "supp_code".equalsIgnoreCase( childNodeName ) )
					{
						suppCode = genericUtility.getColumnValue( "supp_code", currDom );
						asnNo = genericUtility.getColumnValue( "asn_no", hdrDom );

						if( suppCode == null || suppCode.trim().length() == 0 )
						{
							// 26/10/11 manoharan all the errors should be returned so that warnings can be processed
							//errString = getErrorString("supp_code","NULLSUPPCD",userId);
							//break;
							errList.add( "NULLSUPPCD" );
							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 COUNT FROM SUPPLIER WHERE SUPP_CODE = ?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, suppCode);					 
							rs = pstmt.executeQuery();							
							if (rs.next())
							{
								cnt = rs.getInt("COUNT");
							}
							rs.close(); rs = 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( "supp_code" , "INVSUPPCODE" , userId );
								//break;
								errList.add( "INVSUPPCODE" );
								errFields.add( childNodeName.toLowerCase() );
								// end 26/10/11 manoharan all the errors should be returned so that warnings can be processed
							}

							sql = "SELECT COUNT(*) AS COUNT FROM ASN_HDR WHERE SUPP_CODE = ? AND ASN_NO = ?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString( 1, suppCode );					 
							pstmt.setString( 2, asnNo );
							rs = pstmt.executeQuery();							
							if (rs.next())
							{
								cnt = rs.getInt("COUNT");
							}
							rs.close();
							rs = 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( "supp_code" , "INVASNNO" , userId );
								//break;
								errList.add( "INVASNNO" );
								errFields.add( childNodeName.toLowerCase() );
								// end 26/10/11 manoharan all the errors should be returned so that warnings can be processed
							}
						}
					}
					else if ( "purc_order".equalsIgnoreCase( childNodeName ) )
					{
						purcOrder = checkNull(genericUtility.getColumnValue( "purc_order", currDom ));
						asnNo = genericUtility.getColumnValue( "asn_no", hdrDom );

						if( purcOrder.trim().length() == 0 )
						{
							// 26/10/11 manoharan all the errors should be returned so that warnings can be processed
							//errString = getErrorString("purc_order","NULLPORD",userId);
							//break;
							errList.add( "NULLPORD" );
							errFields.add( childNodeName.toLowerCase() );
							// end 26/10/11 manoharan all the errors should be returned so that warnings can be processed
						}
						else
						{
							sql = "SELECT CONFIRMED FROM PORDER WHERE PURC_ORDER = ?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, purcOrder);					 
							rs = pstmt.executeQuery();							
							if (rs.next())
							{
								confirmed = checkNull(rs.getString("CONFIRMED"));
							}
							rs.close(); rs = null;
							pstmt.close(); pstmt = null;

							if( !"Y".equalsIgnoreCase(confirmed.trim()) )
							{
								// 26/10/11 manoharan all the errors should be returned so that warnings can be processed
								//errString = getErrorString( "purc_order" , "UNCONFPORD" , userId );
								//break;
								errList.add( "UNCONFPORD" );
								errFields.add( childNodeName.toLowerCase() );
								// end 26/10/11 manoharan all the errors should be returned so that warnings can be processed
							}
						}
					}
				}// for loop end
			}//case 2 end
			break;
			case 3:
			{
				parentList = currDom.getElementsByTagName( "Detail" + currentFormNo );
				childList = parentList.item( 0 ).getChildNodes();
				noOfChilds = childList.getLength();
				//asnNo=genericUtility.getColumnValue("asn_no", dom);
				//					asnNo=checkNull(genericUtility.getColumnValue("asn_no", allDom, "2" ));
				//					sql="select tran_id from porcp where asn_no=?";
				//					pstmt=conn.prepareStatement(sql);
				//					pstmt.setString(1,asnNo);
				//					rs=pstmt.executeQuery();
				//					if(rs.next())
				//					{
				//						tranId=rs.getString("tran_id");
				//					}
				//					rs.close();
				//					rs=null;
				//					pstmt.close();
				//					pstmt=null;
				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( "lot_sl".equalsIgnoreCase( childNodeName ) )
					{
						lotSl = checkNull(genericUtility.getColumnValue("lot_sl", allDom, "3" ));
						siteCode = checkNull(genericUtility.getColumnValue("site_code", allDom, "2" ));
						System.out.println("siteCode2::::::::"+siteCode);
						if ( lotSl.trim().length() == 0 )
						{
							errList.add( "NULLLPNNO " );
							errFields.add( childNodeName.toLowerCase() );
						}
						else
						{
							//Changed by Dharnaj on 02/08/14 for genValidation hide this code,this validation call by genValidation(GENMST) Start.
							//Chnaged by Rohan on 10-02-12 allow lpn no start with NJ & CN & allow only 10 Character.start
							/*if(!lotSl.startsWith("NJ") && !lotSl.startsWith("CN") && !lotSl.startsWith("DM") && !lotSl.startsWith("EX"))
							{
								errList.add( "VTINVLPN" );
								errFields.add( childNodeName.toLowerCase());
							}
							
							if(lotSl.length() != 10)
							{
								errList.add( "VTINVLEN" );
								errFields.add( childNodeName.toLowerCase());
							}*/
							//Chnaged by Rohan on 10-02-12 allow lpn no start with NJ & CN & allow only 10 Character.end
							//End Changed by Dharnaj on 02/08/14 for genValidation hide this code,this validation call by genValidation(GENMST).  
							//Changes made to check duplicate LPN no
							String tranID = "";
							String lpnNoExist = "";
							NodeList lpnNoNodeList = null;
							ArrayList dupLPNList = new ArrayList();
							int noOfLPN = 0;
							int listLen = 0;
							int recCnt = 0;
							int lpnNoCntDb = 0;

							tranID = checkNull(genericUtility.getColumnValue("tran_id", allDom, "2" ));

							lpnNoNodeList = allDom.getElementsByTagName("lot_sl");
							noOfLPN = lpnNoNodeList.getLength();
							for( int idx = 0; idx < noOfLPN; idx++ )
							{
								if ( lpnNoNodeList.item( idx ).getFirstChild() != null )
								{
									dupLPNList.add((String)( lpnNoNodeList.item( idx ).getFirstChild() ).getNodeValue());
								}
							}
							listLen = dupLPNList.size();
							if ( listLen > 0 )
							{
								recCnt = 1;
								for( int i = 0; i < listLen - 1; i++ )
								{
									for ( int j = i+1; j < listLen; j++ )
									{
										if ( ((String)dupLPNList.get(i)).equalsIgnoreCase((String)dupLPNList.get(j)) )
										{
											recCnt++;
											break;
										}
									}
									if( recCnt > 1 )
									{
										break;
									}
								}
							}
							System.out.println("recCnt = ["+recCnt+"]");

							if( recCnt > 1 )
							{
								System.out.println("----1------");
								errList.add( "DUPLPNNO" );
								errFields.add( childNodeName.toLowerCase() );
							}

							else if ( tranID != null && tranID.trim().length() > 0 )
							{
								//Changed by sankara on 04/08/14 for multiple site canada
								//sql = "SELECT count(*) AS COUNT FROM PORCPDET WHERE TRAN_ID <> ? AND LOT_SL = ? ";
								 sql = " SELECT COUNT(*) AS COUNT FROM PORCPDET PT, PORCP P WHERE P.TRAN_ID = PT.TRAN_ID AND P.PURC_ORDER = PT.PURC_ORDER AND PT.TRAN_ID <> ?  " +
								 	   " AND PT.LOT_SL = ? AND P.SITE_CODE = ? ";
								pstmt = conn.prepareStatement(sql);

								for( int idx = 0; idx < noOfLPN; idx++ )
								{
									if ( lpnNoNodeList.item( idx ).getFirstChild() != null )
									{
										lpnNoExist = ( lpnNoNodeList.item( idx ).getFirstChild() ).getNodeValue();

										pstmt.setString( 1, tranID );
										pstmt.setString( 2, lpnNoExist );
										//Changed by sankara on 04/08/14 for multiple site canada
										pstmt.setString( 3, siteCode );
										rs = pstmt.executeQuery();
										if( rs.next() )
										{
											lpnNoCntDb = rs.getInt("COUNT");
										}
										rs.close(); rs = null;
										pstmt.clearParameters();
										System.out.println( "lpnNoCntDb =[" +lpnNoCntDb+ "]" );
										if ( lpnNoCntDb > 0 )
										{
											System.out.println("----2------");
											errList.add( "DUPLPNNO" );
											errFields.add( childNodeName.toLowerCase() );
											break;
										}
									}
								}
								pstmt.close(); pstmt = null;
							}
							else if ( tranID == null || tranID.trim().length() == 0 )
							{
								//Changed by sankara on 04/08/14 for multiple site canada
								//sql = "SELECT count(*) AS COUNT FROM PORCPDET WHERE LOT_SL = ? ";
								sql = " SELECT COUNT(*) AS COUNT FROM PORCPDET PT, PORCP P WHERE P.TRAN_ID = PT.TRAN_ID AND P.PURC_ORDER = PT.PURC_ORDER " +
									  " AND PT.LOT_SL = ? AND P.SITE_CODE = ? ";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString( 1, lotSl );
								//Changed by sankara on 04/08/14 for multiple site canada
								pstmt.setString( 2, siteCode );
								rs = pstmt.executeQuery();
								if( rs.next() )
								{
									lpnNoCntDb = rs.getInt("COUNT");
								}
								rs.close();
								rs=null;

								if ( lpnNoCntDb > 0 )
								{
									System.out.println("----3------");
									errList.add( "DUPLPNNO" );
									errFields.add( childNodeName.toLowerCase() );
									//break;
								}
								/*for( int idx = 0; idx < noOfLPN; idx++ )
									{
										if ( lpnNoNodeList.item( idx ).getFirstChild() != null )
										{
											lpnNoExist = ( lpnNoNodeList.item( idx ).getFirstChild() ).getNodeValue();

											pstmt.setString( 1, lpnNoExist );
											rs = pstmt.executeQuery();
											if( rs.next() )
											{
												lpnNoCntDb = rs.getInt("COUNT");
											}
											rs.close(); rs = null;
											pstmt.clearParameters();
											System.out.println( "lpnNoCntDb =[" +lpnNoCntDb+ "]" );
											if ( lpnNoCntDb > 0 )
											{
												errList.add( "DUPLPNNO" );
												errFields.add( childNodeName.toLowerCase() );
												break;
											}
										}
									}*/
								pstmt.close(); pstmt = null;
							}
							//End changes by gulzar - 12/28/2011
							//Added By Mahesh Patidar on 29/06/12
							//changed by sankara on 04/08/14 for multiple site code canada
							//sql = "SELECT QUANTITY FROM STOCK WHERE LOT_SL = ? ";
							//changes by Dadaso pawar on 18/03/15 [Start]
							/*sql = "SELECT QUANTITY FROM STOCK WHERE LOT_SL = ? AND SITE_CODE = ? ";
							pstmt = conn.prepareStatement(sql);	
							pstmt.setString(1, lotSl);	
							//changed by sankara on 04/08/14 for multiple site code canada
							pstmt.setString(2, siteCode);	
							rs = pstmt.executeQuery();
							while( rs.next() )
							{
								quantity = rs.getDouble(1);
								if(quantity > 0)
								{
									System.out.println("----4------");
									errList.add( "DUPLPNNO" );
									errFields.add( childNodeName.toLowerCase() );
								}
							}*/
							sql = "SELECT COUNT(*) FROM STOCK STK, LOCATION LOC, INVSTAT ISTAT "
									+ "WHERE STK.LOC_CODE = LOC.LOC_CODE AND LOC.INV_STAT = STK.INV_STAT "
									+ "AND ISTAT.AVAILABLE = 'Y' AND STK.INV_STAT  = ISTAT.INV_STAT "
									+ "AND STK.LOT_SL = ?  AND STK.SITE_CODE = ? AND STK.QUANTITY > 0 "
									+ "AND (CASE WHEN ISTAT.STAT_TYPE IS NULL THEN ' ' ELSE ISTAT.STAT_TYPE END) <> 'S'" ;
							pstmt = conn.prepareStatement(sql);	
							pstmt.setString(1, lotSl);							
							pstmt.setString(2, siteCode);	
							rs = pstmt.executeQuery();
							
							if(rs.next())
							{
								cnt = rs.getInt(1);
							}
							System.out.println("Count Duplicate LPN checking----->>"+cnt);
							if(cnt > 0)
							{
								errList.add( "DUPLPNNO" );
								errFields.add( childNodeName.toLowerCase() );
							}
							//changes by Dadaso pawar on 18/03/15 [End]
							rs.close();  
							rs = null;
							pstmt.close(); 
							pstmt = null;
							//Ended By Mahesh Patidar
						}//else
					}
					else if( "loc_code".equalsIgnoreCase( childNodeName ) )
					{
						itemCode = checkNull(genericUtility.getColumnValue("item_code", allDom, "3" ));
						siteCode = checkNull(genericUtility.getColumnValue("site_code", allDom, "3" ));
						lotNo = checkNull(genericUtility.getColumnValue("lot_no", allDom, "3" ));
						lpnNo = checkNull(genericUtility.getColumnValue("lot_sl", allDom, "3" ));
						locCode = checkNull(genericUtility.getColumnValue( "loc_code", allDom, "3" ));

						// 26/10/11 manoharan consider stk_opt and asn_reqd from supplieritem / supplier
						suppCode = checkNull(genericUtility.getColumnValue( "supp_code", allDom, "2" ));
						purcOrder = checkNull(genericUtility.getColumnValue( "purc_order", allDom, "2" ));

						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 (!"0".equals(stkOpt))
						{
							// check supplieritem for asn_reqd
							sql = "SELECT CASE WHEN ASN_REQD IS NULL THEN 'N' ELSE ASN_REQD END AS ASN_REQD "
									+ " FROM SUPPLIERITEM "
									+ " WHERE SUPP_CODE = ? "
									+ " AND ITEM_CODE = ?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, suppCode);	
							pstmt.setString(2, itemCode);	
							rs = pstmt.executeQuery();

							if( rs.next() )
							{								
								asnReqd = rs.getString("ASN_REQD");
							}
							else
							{
								rs.close(); rs = null;
								pstmt.close(); pstmt = null;
								sql = "SELECT CASE WHEN ASN_REQD IS NULL THEN 'N' ELSE ASN_REQD END AS ASN_REQD "
										+ " FROM SUPPLIER "
										+ " WHERE SUPP_CODE = ? ";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, suppCode);	
								rs = pstmt.executeQuery();

								if( rs.next() )
								{								
									asnReqd = rs.getString("ASN_REQD");
								}
							}
							rs.close(); rs = null;
							pstmt.close(); pstmt = null;
							if ("Y".equals(asnReqd))
							{
								sql = "SELECT COUNT(1) AS COUNT  "
										+ " FROM ASN_HDR "
										+ " WHERE PURC_ORDER = ? ";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, purcOrder);	
								rs = pstmt.executeQuery();
								if (rs.next())
								{
									count = rs.getInt("COUNT");
								}
								pstmt.close(); pstmt = null;
								rs.close(); rs = null;
								if( count == 0)
								{
									errList.add( "INVASNNO" );
									errFields.add( childNodeName.toLowerCase() );
								}
							}

						}
						// end 26/10/11 manoharan consider stk_opt and asn_reqd from supplieritem / supplier

						if( locCode.trim().length() == 0 )				
						{
							// 26/10/11 manoharan all the errors should be returned so that warnings can be processed
							//errString = getErrorString("loc_code","NULLLOCCD",userId);
							//break;
							errList.add( "NULLLOCCD" );
							errFields.add( childNodeName.toLowerCase() );
							// end 26/10/11 manoharan all the errors should be returned so that warnings can be processed
						}
						String locTypeItem = "";
						String locTypeLocation = "";
						sql = "SELECT LOC_TYPE FROM ITEM WHERE ITEM_CODE = ? ";								  
						pstmt = conn.prepareStatement( sql );
						pstmt.setString( 1, itemCode );
						rs = pstmt.executeQuery();	
						if( rs.next() )
						{
							locTypeItem = rs.getString(1);
						}
						rs.close(); rs = null;
						pstmt.close(); pstmt = null;
						sql = "SELECT SINGLE_LOT_SL, ITEM_LOT_OPT, INV_STAT,LOC_TYPE FROM LOCATION WHERE LOC_CODE = ?";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, locCode);

						rs = pstmt.executeQuery();
						if( rs.next() )
						{
							singleLotSl = checkNull(rs.getString("SINGLE_LOT_SL"));
							itemLotOpt = checkNull(rs.getString("ITEM_LOT_OPT"));
							locInvStat = checkNull(rs.getString("INV_STAT"));//Changed by gulzar on 31/12/11
							locTypeLocation = checkNull(rs.getString("LOC_TYPE"));
						}
						else
						{
							// 26/10/11 manoharan all the errors should be returned so that warnings can be processed
							//errString = getErrorString( "loc_code", "INVLOCCODE", userId );
							//break;
							errList.add( "INVLOCCODE" );
							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;
						if( !locTypeLocation.equalsIgnoreCase(locTypeItem) )
						{
							errList.add( "VMLOCTYP1" );
							errFields.add( childNodeName.toLowerCase() );
							//errString = getErrorString("loc_code__to","INVLOCCODE",userId);
							break;
						}

						grlInvStat = checkNull(distComm.getDisparams("999999","GRL_INVSTAT",conn));//Changed by gulzar on 31/12/11
						partialGrlLoc = checkNull(distComm.getDisparams("999999","PGRL_INVSTAT",conn));
						if ( !locInvStat.trim().equalsIgnoreCase(grlInvStat.trim()) && !locInvStat.trim().equalsIgnoreCase(partialGrlLoc.trim()) )
						{
							errList.add( "VTLOCCODE9" );
							errFields.add( childNodeName.toLowerCase() );
						}
						//End changes by gulzar on 31/12/11

						if( "Y".equalsIgnoreCase(singleLotSl) )
						{
							sql = "SELECT QUANTITY FROM STOCK WHERE SITE_CODE = ?  AND LOC_CODE = ? ";
							pstmt = conn.prepareStatement( sql );
							pstmt.setString( 1, siteCode );
							pstmt.setString( 2, locCode );
							rs = pstmt.executeQuery();	
							if( rs.next() )
							{
								quantity = rs.getDouble("QUANTITY");
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;

							if( quantity != 0 )
							{
								// 26/10/11 manoharan all the errors should be returned so that warnings can be processed
								//errString = getErrorString( "loc_code", "VTLOCCODE1", userId );
								//break;
								errList.add( "VTLOCCODE1" );
								errFields.add( childNodeName.toLowerCase() );
								// end 26/10/11 manoharan all the errors should be returned so that warnings can be processed
							}
							//Changed by Gulzar on 5/12/2012
							String locCodeExist = "";
							NodeList locCodeNodeList = null;
							ArrayList dupLocCodeList = new ArrayList();

							int noOfLocCode = 0;
							int listLen = 0;
							int recCnt = 0;
							int locCodeCntDb = 0;

							locCodeNodeList = allDom.getElementsByTagName("locCode");
							noOfLocCode = locCodeNodeList.getLength();

							for( int idx = 0; idx < noOfLocCode; idx++ )
							{
								if ( locCodeNodeList.item( idx ).getFirstChild() != null )
								{
									dupLocCodeList.add((String)( locCodeNodeList.item( idx ).getFirstChild() ).getNodeValue());
								}
							}
							listLen = dupLocCodeList.size();
							if ( listLen > 0 )
							{
								recCnt = 1;
								for( int i = 0; i < listLen - 1; i++ )
								{
									for ( int j = i+1; j < listLen; j++ )
									{
										if ( ((String)dupLocCodeList.get(i)).equalsIgnoreCase((String)dupLocCodeList.get(j)) )
										{
											recCnt++;
											break;
										}
									}
									if( recCnt > 1 )
									{
										break;
									}
								}
							}
							System.out.println("recCnt = ["+recCnt+"]");

							if( recCnt > 1 )
							{
								errList.add( "DUPLOCCODE" );
								errFields.add( childNodeName.toLowerCase() );
							}
							//End changed by Gulzar on 5/12/2012
						}
						else
						{
							if( itemLotOpt.trim().equals("0") )  // Validation for single item_code single lot_no
							{
								sql = "SELECT COUNT(*) AS COUNT FROM STOCK WHERE ITEM_CODE = ? AND SITE_CODE = ? AND LOC_CODE = ? AND LOT_NO <> ?";
								pstmt = conn.prepareStatement( sql );
								pstmt.setString( 1, itemCode );
								pstmt.setString( 2, siteCode );
								pstmt.setString( 3, locCode );
								pstmt.setString( 4, lotNo );
								rs = pstmt.executeQuery();
								if( rs.next() )
								{
									count = rs.getInt("COUNT");
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;

								if( count > 0 )
								{
									// 26/10/11 manoharan all the errors should be returned so that warnings can be processed
									//errString = getErrorString("loc_code", "VTLOCCODE3", userId );
									//break;
									errList.add( "VTLOCCODE3" );
									errFields.add( childNodeName.toLowerCase() );
									// end 26/10/11 manoharan all the errors should be returned so that warnings can be processed
								}
								//changed by sankara on 14/09/2012 to add quantity
								//sql = "SELECT COUNT(*) AS COUNT FROM STOCK WHERE ITEM_CODE <> ? AND SITE_CODE = ? AND LOC_CODE = ? AND LOT_NO <> ?";
								sql = "SELECT COUNT(*) AS COUNT FROM STOCK WHERE (ITEM_CODE <> ? OR LOT_NO <> ?) AND SITE_CODE = ? AND LOC_CODE = ? AND QUANTITY > 0"; 
								//changed by sankara on 14/09/2012 to set the string in a row based on sql[Satrt]
							/*	pstmt.setString( 1, itemCode );
								pstmt.setString( 2, siteCode );
								pstmt.setString( 3, locCode );
								pstmt.setString( 4, lotNo );     */
								pstmt.setString( 1, itemCode );
								pstmt.setString( 2, lotNo );
								pstmt.setString( 3, siteCode );
								pstmt.setString( 4, locCode );
								//changed by sankara on 14/09/2012 to set the string in a row based on sql[End]
								rs = pstmt.executeQuery();
								if( rs.next() )
								{
									count = rs.getInt("COUNT");
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;

								if( count > 0 )
								{
									// 26/10/11 manoharan all the errors should be returned so that warnings can be processed
									//errString = getErrorString("loc_code", "VTLOCCODE2", userId );
									//break;
									errList.add( "VTLOCCODE2" );
									errFields.add( childNodeName.toLowerCase() );
									// end 26/10/11 manoharan all the errors should be returned so that warnings can be processed
								}
							}
							else if( itemLotOpt.trim().equals("1") )  //Validation for single item_code multiple lot_no
							{
								//changed by sankara on 14/09/2012 to add quantity
								//sql = "SELECT COUNT(*) AS COUNT FROM STOCK WHERE  ITEM_CODE <> ? AND SITE_CODE = ? AND LOC_CODE = ? AND LOT_NO = ? ";
								sql = "SELECT COUNT(*) AS COUNT FROM STOCK WHERE  ITEM_CODE <> ? AND SITE_CODE = ? AND LOC_CODE = ? AND QUANTITY > 0";
								pstmt = conn.prepareStatement( sql );
								//changed by sankara on 14/09/2012 to set the string in a row based on sql[Satrt]
							/*	pstmt.setString( 1, itemCode );
								pstmt.setString( 2, siteCode );
								pstmt.setString( 3, locCode );
								pstmt.setString( 4, lotNo );    */
								pstmt.setString( 1, itemCode );
								pstmt.setString( 2, siteCode );
								pstmt.setString( 3, locCode );
								//changed by sankara on 14/09/2012 to set the string in a row based on sql[End]
								rs = pstmt.executeQuery();
								if( rs.next() )
								{
									count = rs.getInt("COUNT");
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
								if( count > 0 )
								{
									// 26/10/11 manoharan all the errors should be returned so that warnings can be processed
									//errString = getErrorString("loc_code", "VTLOCCODE4", userId );
									//break;
									errList.add( "VTLOCCODE4" );
									errFields.add( childNodeName.toLowerCase() );
									// end 26/10/11 manoharan all the errors should be returned so that warnings can be processed
								}	
							}
						}
					}
					/*Added By Ashish Sonawane on 02/AUG/12 for checking lot no from the lot master and validating with alt_lot_no [START] */ 
					else if( "lot_no".equalsIgnoreCase( childNodeName ))
					{
						lotNo = checkNull(genericUtility.getColumnValue("lot_no", allDom, "3" ));
						String isMaintainLotMaster =  distComm.getDisparams("999999","MAINTAIN_LOT_MASTER",conn);
						System.out.println("validation Lot No :["+lotNo+"]");
						int cntLotMaster=0;
						if("Y".equalsIgnoreCase(isMaintainLotMaster))
						{
							String sqlLotMaster = "SELECT COUNT(*) FROM ITEM_LOT_INFO WHERE ITEM_CODE =?  AND LOT_NO =?";
							pstmtLotMaster=conn.prepareStatement(sqlLotMaster);
							pstmtLotMaster.setString(1,itemCode);
							pstmtLotMaster.setString(2,lotNo);
							rsLotMaster = pstmtLotMaster.executeQuery();
							if(rsLotMaster.next())
							{
								cntLotMaster=rsLotMaster.getInt(1);

							}
							System.out.println("validation cntLotMaster:"+cntLotMaster+"]");
							if(cntLotMaster == 0)
							{
								String sqlAltLotMaster = "SELECT LOT_NO FROM ITEM_LOT_INFO WHERE ITEM_CODE =?  AND ALT_LOT_NO =?";
								pstmtAltLotMaster=conn.prepareStatement(sqlAltLotMaster);
								pstmtAltLotMaster.setString(1,itemCode);
								pstmtAltLotMaster.setString(2,lotNo);
								rsAltLotMaster = pstmtAltLotMaster.executeQuery();
								if(rsAltLotMaster.next())
								{
									altLotNo=rsAltLotMaster.getString("LOT_NO");
									System.out.println("Alternate Lot No :["+altLotNo+"]");
								}
								else
								{
									errList.add( "INVLOTNMST" );
									errFields.add( childNodeName.toLowerCase() );
								}
							}
						}

					}
					else if("no_art".equalsIgnoreCase( childNodeName ))
					{
						purcOrder = checkNull(genericUtility.getColumnValue("purc_order", allDom, "3" ));
						String lineNoOrder=  checkNull(genericUtility.getColumnValue("line_no__ord", allDom, "3" ));
						itemCode="";
						int noArt=0;
						int noPallet=0;
						quantity=0;
						int noCase=0;
						int noCaseperLine=0;
						lotNo="";
						double shipperSize=0;
						double perPalletVolume =  Double.parseDouble(findValueFromDisparm("999999","PER_PALLET__VOLUME",conn));
						double perPalletWeight =  Double.parseDouble(findValueFromDisparm("999999","PER_PALLET__WEIGHT",conn));
						//pstmt=conn.prepareStatement("select item_code,lot_no,no_art from porddet where purc_order=? and line_no=? ");
						//noCase=Integer.parseInt(genericUtility.getColumnValue("no_art", allDom, "3" ));
						noCase=Integer.parseInt(genericUtility.getColumnValue("no_art", allDom, "3" ) == null ?"0":genericUtility.getColumnValue("no_art", allDom, "3" )); //chnage by  KUNAL


						pstmt=conn.prepareStatement("select adet.lot_no,pdet.quantity,pdet.item_code,pdet.no_art,no_pallet from asn_hdr ahdr,asn_det adet,porddet pdet where " +
								" ahdr.tran_id=adet.tran_id and ahdr.purc_order=pdet.purc_order and " +
								" pdet.line_no=adet.line_no__ord and pdet.purc_order=? and pdet.line_no=? ");
						pstmt.setString(1,purcOrder);
						pstmt.setString(2,lineNoOrder);
						rs=pstmt.executeQuery();
						if(rs.next())
						{
							itemCode=rs.getString("item_code");
							noArt=rs.getInt("no_art");
							noPallet=rs.getInt("no_pallet");
							quantity=rs.getDouble("quantity");
							lotNo=rs.getString("lot_no");
						}

						rs.close();
						rs=null;
						pstmt.close();
						pstmt=null;
						noCaseperLine=findNosOfCase(itemCode, lotNo, quantity, perPalletVolume, perPalletWeight, conn);
						System.out.println("noCaseperLine----"+noCaseperLine+"----noCase----"+noCase);
						//Changed by sumit on 09/11/12 this warnning removed as per issue tracker start.
						/*if(noCaseperLine!=noCase)
						{
							errList.add( "CASNTMT" );
							errFields.add( childNodeName.toLowerCase() );
						}*/
						//Changed by sumit on 09/11/12 this warnning removed as per issue tracker 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 >= ? ";
						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");								
						}
						rs=pstmt.executeQuery();
						if(rs.next())
						{
							shipperSize=rs.getDouble(1);
						}
						rs.close();
						rs=null;
						pstmt.close();
						pstmt=null;
						String domLine="";
						String prevdomLine="";
						String prevInnerdomLine="";
						NodeList lineNodeList = null;
						NodeList lineOrdList = null;
						int domCase=0;
						double domPartQty=0;
						double totDomQty=0;

						String partDomQty="";
						String noDomArt="";
						String currentLine="";
						//lineNodeList = allDom.getElementsByTagName("line_no");
						//lineOrdList= allDom.getElementsByTagName( "Detail" + currentFormNo );

						//for(int j = 0; j<lineOrdList.getLength(); j++)
						//{
						domLine=checkNull(genericUtility.getColumnValue("line_no__ord", allDom, "3" ));
						System.out.println("domLine----"+domLine);

						parentList= allDom.getElementsByTagName( "Detail" + currentFormNo );
						//currentLine=genericUtility.getColumnValue("line_no", currDom);
						counter = 0;
						System.out.println("parentList.getLength()="+parentList.getLength());

						for(int i=0;i<parentList.getLength();i++)
						{
							prevInnerdomLine = genericUtility.getColumnValueFromNode( "line_no__ord", parentList.item(i) );
							if(prevInnerdomLine.equalsIgnoreCase(domLine))
							{
								counter++;
								noDomArt=checkNull(genericUtility.getColumnValueFromNode( "no_art", parentList.item(i) ));
								partDomQty=checkNull(genericUtility.getColumnValueFromNode( "part_qty", parentList.item(i) ));
								System.out.println("noDomArt----"+noDomArt);
								System.out.println("partDomQty----"+partDomQty);
								if(partDomQty.trim().length()==0)
									partDomQty="0";
								if(noDomArt.trim().length()==0)
									noDomArt="0";

								domCase+=Integer.parseInt(noDomArt);
								domPartQty+=Double.parseDouble(partDomQty);	

							}
						}
						//											if(currentLine.equalsIgnoreCase(genericUtility.getColumnValueFromNode( "line_no", parentList.item(i) )))
						//											{
						//												
						//											}


						System.out.println("domCase----"+domCase);
						System.out.println("domPartQty----"+domPartQty);
						totDomQty=domPartQty+(domCase*shipperSize);
						
						System.out.println("No of pallet ="+noPallet);
						System.out.println("No of pallet scan="+counter);
						//Changed by sumit on 18/12/12 commented this as per Ashish Mehta no need of this check start
						/*if(counter == noPallet) //added by Kunal on 05/09/12
						{
							if(totDomQty<quantity)
							{
								errList.add( "QTYNTMT" );
								errFields.add( childNodeName.toLowerCase() );
							}
						}
						if(totDomQty>quantity)
						{
							errList.add( "INVALIDQTY" );
							errFields.add( childNodeName.toLowerCase() );
						}*/
						//Changed by sumit on 18/12/12 commented this as per Ashish Mehta no need of this check end

					}
					//Changed by Pragyan 03/12/14 CCF to check for quantity,part qty valid data input.start
					else if("quantity".equalsIgnoreCase( childNodeName ))
					{
						double inputQuantity = Double.parseDouble(genericUtility.getColumnValue("quantity", allDom, "3" ) == null ?"0":genericUtility.getColumnValue("quantity", allDom, "3" ));
						double shipperSize = 0.0;
						double reminder = 0.0;
						System.out.println("Quantity Received inputQuantity="+inputQuantity+"]");
						
						if(inputQuantity > 0)
						{
							itemCode = checkNull(genericUtility.getColumnValue("item_code", allDom, "3" ));
							siteCode = checkNull(genericUtility.getColumnValue("site_code", allDom, "3" ));
							lotNo = checkNull(genericUtility.getColumnValue("lot_no", allDom, "3" ));
							
							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 >= ? ";
							pstmt =conn.prepareStatement(sql);
							pstmt.setString(1,itemCode);
							System.out.println("lotNo="+lotNo+"]itemCode["+itemCode+"]");
							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");								
							}
							rs=pstmt.executeQuery();
							if(rs.next())
							{
								shipperSize=rs.getDouble(1);
							}
							rs.close();
							rs=null;
							pstmt.close();
							pstmt=null;
							System.out.println("shipperSize="+shipperSize+"]");
							if(shipperSize > 0)
							{
								reminder = inputQuantity % shipperSize;
								System.out.println("reminder="+reminder+"]");
								if(reminder > 0)
								{
									errList.add( "VTINVQTYIN" );
									errFields.add( childNodeName.toLowerCase() );
								}
								
							}
							
						}
					}
					else if("part_qty".equalsIgnoreCase( childNodeName ))
					{
						double partQtyIn = Double.parseDouble(genericUtility.getColumnValue("part_qty", allDom, "3" ) == null ?"0":genericUtility.getColumnValue("part_qty", allDom, "3" ));
						double shipperSize = 0.0;
						System.out.println("Part Quantity Received ="+partQtyIn+"]");
						
						if(partQtyIn > 0)
						{
							itemCode = checkNull(genericUtility.getColumnValue("item_code", allDom, "3" ));
							siteCode = checkNull(genericUtility.getColumnValue("site_code", allDom, "3" ));
							lotNo = checkNull(genericUtility.getColumnValue("lot_no", allDom, "3" ));
							
							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 >= ? ";
							pstmt =conn.prepareStatement(sql);
							pstmt.setString(1,itemCode);
							System.out.println("lotNo="+lotNo+"]itemCode["+itemCode+"]");
							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");								
							}
							rs=pstmt.executeQuery();
							if(rs.next())
							{
								shipperSize=rs.getDouble(1);
							}
							rs.close();
							rs=null;
							pstmt.close();
							pstmt=null;
							System.out.println("shipperSize="+shipperSize+"]");
							if(partQtyIn > shipperSize)
							{
								errList.add( "VTINVPRTIN" );
								errFields.add( childNodeName.toLowerCase() );
								
							}
							
						}
					}
					//Changed by Pragyan 03/12/14 CCF to check for quantity,part qty valid data input.end
					/*Added By Ashish Sonawane on 02/AUG/12 for checking lot no from the lot master and validating with alt_lot_no [END] */
					/* Commented by shamim as discused with manohran sir Validation is on post save
						else if( "quantity".equalsIgnoreCase( childNodeName ) )//Case added by Dipak on 6/01/2012
						{
							double qtyDom = 0;
							double qtyDb = 0;
							String qtyDomStr = checkNull(genericUtility.getColumnValue("quantity", currDom ));
							purcOrder = checkNull(genericUtility.getColumnValue("purc_order", currDom ));
							String lineNo = checkNull(genericUtility.getColumnValue("line_no", currDom ));

							try
							{
								qtyDom = Double.parseDouble(qtyDomStr);
							}
							catch (NumberFormatException ne)
							{
								qtyDom = 0;
							}
							sql = "select quantity from porddet where purc_order= ? and line_no=?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, purcOrder);
							pstmt.setString(2, lineNo);
							rs = pstmt.executeQuery();
							if (rs.next())
							{
								qtyDb = rs.getDouble("quantity");
							}
							if(qtyDb != qtyDom)
							{
								errCode = "VTPOQTY4";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;
						}*/
				}//inner for loop
				/*
					parentList = currDom.getElementsByTagName( "Detail" + currentFormNo );
					noOfParents = parentList.getLength();
					for( int prnt = 0; prnt < noOfParents; prnt++ )
					{					
						childList = parentList.item( prnt ).getChildNodes();
						noOfChilds = childList.getLength();
						rowCountStr = ""+(prnt+1);

						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( "loc_code".equalsIgnoreCase( childNodeName ) )
							{
								locCode = genericUtility.getColumnValue( "loc_code", allDom, "3", rowCountStr );
								if( locCode == null || locCode.trim().length() == 0 )				
								{
									errString = getErrorString("loc_code","NULLLOCCD",userId);
									break;
								}
								sql = "SELECT COUNT(*) AS COUNT FROM LOCATION WHERE LOC_CODE = ?";
							 	pstmt = conn.prepareStatement(sql);
							 	pstmt.setString(1, locCode);

								rs = pstmt.executeQuery();
								if (rs.next())
								{
									cnt = rs.getInt("COUNT");
								}
								if (rs != null)
								{
									rs.close();
									rs = null;
								}
								if (pstmt != null)
								{
									pstmt.close();
									pstmt = null;
								}
								if(cnt == 0)
								{
									errString = getErrorString( "loc_code" , "INVLOCCODE" , userId );
									break;
								}								
							}
						}//inner for loop
						if ( errString != null && errString.trim().length() > 0 )
						{
							break;
						}
					}//outer for loop
				 */
				break;
			}//case3 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 PoRcpIC  == >");
			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();
				}
			}
			catch(Exception e)
			{
				System.out.println( "Exception :PoRcpIC: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
	 */
	@Override
	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 xmlCurrFrmString contains the current form data in XML format
	 * @param xmlHdrrFrmString contains all the header information in the XML format
	 * @param xmlAllFrmString 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
	 */
	@Override
	public String itemChanged(String xmlCurrFrmString, String xmlHdrFrmString, String xmlAllFrmString, 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
		{
			if (xmlCurrFrmString != null && xmlCurrFrmString.trim().length()!=0)
			{
				currDom = genericUtility.parseString(xmlCurrFrmString); 
			}
			if (xmlHdrFrmString != null && xmlHdrFrmString.trim().length()!=0)
			{
				hdrDom = genericUtility.parseString(xmlHdrFrmString); 
			}
			if (xmlAllFrmString != null && xmlAllFrmString.trim().length()!=0)
			{
				allDom = genericUtility.parseString(xmlAllFrmString); 
			}
			System.out.println("xmlCurrFrmString--------"+xmlCurrFrmString);
			System.out.println("xmlHdrFrmString--------"+xmlHdrFrmString);
			System.out.println("xmlAllFrmString--------"+xmlAllFrmString);
			errString = itemChanged( currDom, hdrDom, allDom, objContext, currentColumn, editFlag, xtraParams );
			System.out.println ( "ErrString :" + errString);
		}
		catch (Exception e)
		{
			System.out.println ( "Exception : Porcp:defaul_ItemChanged(String,String):" + e.getMessage() + ":" );
			errString = genericUtility.createErrorString(e);
			throw new ITMException(e);
		}
		System.out.println ( "returning from Porcp 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
	 * 
	 */


	@Override
	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 itemCode = "";
		String lotNo = "";
		String tranId = "";		
		String packCode = "";
		String purcOrder ="";
		String dcNo = "";
		String dcDate = "";		
		String taxDate = "";		
		String recDate = "";
		String revTran ="";		
		String columnValue = "";
		String suppName = "";
		String suppCode = "";		
		String count = "";		
		String siteCode = "";	
		String siteDescr = "";
		String asnNo = "";
		String asnDate = "";				
		String asndate = "";
		String lrNo = "";
		String tranDate = "";
		String effDate = "";
		String expDate = "";	
		String confDate = "";
		String lrDate = "";	
		String invDate = "";
		String taxRefDate = "";
		String exciseRefDate = "";
		//Changed by Pragyan as per KB & Manoj sharma
		String pordType = "";
		String invoiceNo = "";
		String tranCode = "";
		String remarks = "";
		String currCodeBase = "";
		String expDateStr = "";
		String loginEmpCode = "";
		String currCode = "";
		String status = "O";
		String unit = "", locType = "";
		String scacCode = "";
		String itemSer = "";
		String qcReqd = "N"; // 26/10/11 manoharan
		String scanLocCode ="";
		String accptCriteria="";//Ashish Sonawane 28/08/12
		String siteCodeMf="";//Ashish Sonawane 28/08/12
		String  purcOrderdb = "";
		//Changed by Rohan on 12-June-13 for define variable.start
		String invStat = "";
		String pgrlInvStat = "";
		//Changed by Rohan on 12-June-13 for define variable.end
		java.sql.Date asnDateDb = null;
		java.sql.Timestamp expDateDb = null;
		java.sql.Timestamp mfgDate = null;
		java.sql.Timestamp lotMfgDate =null,lotExpDate =null;
		int selfLife = 0;
		double shipperSize = 0d, grossWeight = 0d, netWeight = 0d, tareWeight = 0d;
		double exchRate = 0d, noArt = 0d, rate = 0d, netAmt = 0d , amount = 0d;

		int currentFormNo = 0;
		int cnt = 0;
		int domID = 0;
		String  lineNo = "";
		String transMode = "";
		String  lineNoOrd = "";
		String postType="";
		double quantity = 0d;

		ResultSet rs = null;
		ResultSet rs1 = null;
		ResultSet rsItemLotPack = null ;
		ResultSet rsLotMaster = null ; //Added by Ashish
		ResultSet rsAltLotMaster = null ; //Added by Ashish
		Connection conn = null;
		PreparedStatement pstmt = null;
		PreparedStatement pstmt1 = null;
		PreparedStatement pstmtItemLotPack = null;
		PreparedStatement pstmtLotMaster=null; //added by Ashish
		PreparedStatement pstmtAltLotMaster=null; //added by Ashish
		int line =0;
		int nosOfArtPerPallet=0;
		int extraArtForLastPallet =0;
		String []arr;
		int maxLine = 0;
		int totalNoOfPallet =0;

		StringBuffer valueXmlString = new StringBuffer();		

		System.out.println("hello:");

		GenericUtility genericUtility = GenericUtility.getInstance();

		FinCommon finCommon = new FinCommon();
		DistCommon distComm = new DistCommon();

		try
		{
			loginEmpCode = checkNull(genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginEmpCode" ));
			String loginSite = checkNull(genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginSiteCode" ));
			String userId = checkNull(genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginCode" ));
			String chgTerm = checkNull(genericUtility.getValueFromXTRA_PARAMS( xtraParams, "chgTerm" ));
			if ( chgTerm.trim().length() == 0 )
			{
				chgTerm = "SYSTEM";
			}
			if ( userId.trim().length() == 0 )
			{
				userId = "BASE";
			}

			columnValue = genericUtility.getColumnValue( currentColumn, currDom );

			AppConnectParm appConnect = new AppConnectParm();

			Properties p = appConnect.getProperty();			
			InitialContext ctx = new InitialContext(p);			
			String dbDateFormat = genericUtility.getDBDateFormat();
			String applDateFormat = genericUtility.getApplDateFormat();
			DateFormat dateFormat = new SimpleDateFormat(applDateFormat);

			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 2:
			{
				if( currentColumn.trim().equalsIgnoreCase( "itm_default" ) )
				{
					asnNo = genericUtility.getColumnValue( "asn_no", hdrDom );
					sql = "SELECT PURC_ORDER FROM ASN_HDR WHERE ASN_NO = ?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, asnNo);
					rs = pstmt.executeQuery();

					if(rs.next())
					{
						purcOrder = checkNull(rs.getString("PURC_ORDER"));
					}
					if (rs != null)
					{
						rs.close();
						rs = null;
					}
					if (pstmt != null)
					{
						pstmt.close();
						pstmt = null;
					}

					java.util.Date currDate = new java.util.Date();
					String currDateStr = dateFormat.format(currDate);

					java.sql.Date tranDateDb = null;
					java.sql.Date effDateDb = null;
					java.sql.Date dcDateDb = null;
					java.sql.Date taxDateDb = null;

					java.sql.Date recDateDb = null;
					java.sql.Date confDateDb = null;
					java.sql.Date lrDateDb = null;
					java.sql.Date invDateDb = null;
					java.sql.Date exciseRefDateDb = null;
					java.sql.Date taxRefDateDb = null;

					//if( purcOrder.trim().length() > 0 )
					{
						sql = "SELECT TRAN_ID, TRAN_DATE, EFF_DATE, TRAN_TYPE, SITE_CODE, PURC_ORDER, SUPP_CODE, DC_NO, "+
								"DC_DATE, TRAN_CODE, AMOUNT, DISCOUNT, ADJ_AMT, TAX, CURR_CODE, EXCH_RATE, TRAN_SER , "+
								"TAX_DATE, GR_NO, REC_DATE, EXCISE_REF, TRAN_ID__REF, CONFIRMED, CONF_DATE, REMARKS, "+
								"CHG_DATE, CHG_USER, CHG_TERM, VOUCH_CREATED, LR_NO, LR_DATE, LORRY_NO, TRANS_MODE, "+
								"ITEM_SER, FRT_AMT, INSURANCE_AMT, ACCT_INV_ONLINE, ACCT_PORCP_ONLINE, STATUS, "+
								"EMP_CODE,EMP_CODE__APRV, RET_OPT, CLEARING_CHARGES, BENEFIT_TYPE, TOTAL_ADDITIONAL_COST, "+
								"CURR_CODE__CLR, EXCH_RATE__CLR, CURR_CODE__INS, EXCH_RATE__INS, CURR_CODE__FRT, "+
								"EXCH_RATE__FRT, QC_REQD, INVOICE_NO,INVOICE_DATE, EXCISE_REF_DATE, SUPP_CODE__CHA, "+
								"STAN_CODE__LOAD, STAN_CODE__DEST, TAX_REF, TAX_REF_DATE, RECIEPT_TYPE, FRT_TYPE, FRT_RATE, "+
								"SUPP_EXC_INV, ACCEPT_CRITERIA, SUPP_CODE__SHIP, PRINT_STAT, OTHER_CHARGES, CURR_CODE__OTHCH, "+
								"EXCH_RATE__OTHCH, FREIGHT_STATUS, OCTROI_NO, POST_TYPE, GP_SER, PARENT__TRAN_ID, REV__TRAN, "+
								"SHIPMENT_ID, WORKFLOW_STATUS, GIT_REF, FN_GET_VOUCHNO, UDF__STR1, POST_PROV, LOT_NO, "+
								"ANAL_CODE, TRANSFER_FLAG, SHELF_LIFE__TYPE, FRT_AMT__FIXED, FRT_AMT__QTY, TOT_AMT,RECIEPT_TYPE "+
								"FROM PORCP WHERE PURC_ORDER = ?  AND CASE WHEN CONFIRMED IS NULL THEN 'N'  ELSE CONFIRMED END = 'N' ";
						pstmt =  conn.prepareStatement(sql);
						pstmt.setString( 1, purcOrder );
						rs = pstmt.executeQuery();
						if(rs.next())
						{

							loginSite = rs.getString("SITE_CODE");
							tranCode = rs.getString("TRAN_CODE");//added by kunal on 05/09/12
							transMode = rs.getString("TRANS_MODE");//added by kunal on 05/09/12
							//valueXmlString.append( "<Detail2 domID='1' >\r\n" );
							//Added by Manoj dtd 20/08/2012
							valueXmlString.append( "<Detail2 domID='1' selected=\"Y\">\r\n" );
							valueXmlString.append( "<attribute selected=\"Y\" updateFlag=\"E\" status=\"O\" pkNames=\"\"/>\r\n" );
							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" );
							}
							effDateDb = rs.getDate("EFF_DATE");
							if ( effDateDb != null )
							{
								effDate = genericUtility.getValidDateString( effDateDb.toString() , dbDateFormat,	applDateFormat );
								valueXmlString.append( "<eff_date><![CDATA[" ).append(  effDate ).append( "]]></eff_date>\r\n" );
							}
							dcDateDb = rs.getDate("DC_DATE");
							if ( dcDateDb != null )
							{
								dcDate = genericUtility.getValidDateString( dcDateDb.toString() , dbDateFormat, applDateFormat );
								valueXmlString.append( "<dc_date><![CDATA[" ).append(  dcDate ).append( "]]></dc_date>\r\n" );
							}
							taxDateDb = rs.getDate("TAX_DATE");
							if ( taxDateDb != null )
							{
								taxDate = genericUtility.getValidDateString( taxDateDb.toString() , dbDateFormat, applDateFormat );
								valueXmlString.append( "<tax_date><![CDATA[" ).append(  taxDate ).append( "]]></tax_date>\r\n" );
							}
							recDateDb = rs.getDate("REC_DATE");
							if ( recDateDb != null )
							{
								recDate = genericUtility.getValidDateString( recDateDb.toString() , dbDateFormat, applDateFormat );
								valueXmlString.append( "<rec_date><![CDATA[" ).append(  recDate ).append( "]]></rec_date>\r\n" );
							}
							confDateDb = rs.getDate("CONF_DATE");
							if ( confDateDb != null )
							{
								confDate = genericUtility.getValidDateString( confDateDb.toString() , dbDateFormat, applDateFormat );
								valueXmlString.append( "<conf_date><![CDATA[" ).append(  confDate ).append( "]]></conf_date>\r\n" );
							}
							lrDateDb = rs.getDate("LR_DATE");
							if ( lrDateDb != null )
							{
								lrDate = genericUtility.getValidDateString( lrDateDb.toString() , dbDateFormat, applDateFormat );
								valueXmlString.append( "<lr_date><![CDATA[" ).append( lrDate ).append( "]]></lr_date>\r\n" );
							}
							invDateDb = rs.getDate("INVOICE_DATE");
							if ( invDateDb != null )
							{
								invDate = genericUtility.getValidDateString( invDateDb.toString() , dbDateFormat, applDateFormat );
								valueXmlString.append( "<invoice_date><![CDATA[" ).append(  invDate ).append( "]]></invoice_date>\r\n" );
							}
							exciseRefDateDb = rs.getDate("EXCISE_REF_DATE");
							if ( exciseRefDateDb != null )
							{
								exciseRefDate = genericUtility.getValidDateString( exciseRefDateDb.toString() , dbDateFormat, applDateFormat );
								valueXmlString.append( "<excise_ref_date><![CDATA[" ).append( exciseRefDate ).append( "]]></excise_ref_date>\r\n" );
							}
							taxRefDateDb = rs.getDate("TAX_REF_DATE");
							if ( taxRefDateDb != null )
							{
								taxRefDate = genericUtility.getValidDateString( taxRefDateDb.toString() , dbDateFormat, applDateFormat );
								valueXmlString.append( "<tax_ref_date><![CDATA[" ).append(  taxRefDate ).append( "]]></tax_ref_date>\r\n" );
							}
							suppCode = rs.getString("SUPP_CODE");
							siteCode = rs.getString("SITE_CODE");
							//Added By Mahesh Patidar on 29/06/12
                            //commet by kunal on 05/09/12
                            /*
							sql = "SELECT TRANS_MODE FROM TRANSPORTER_MODE WHERE TRAN_CODE = ? ";
							pstmt1 = conn.prepareStatement(sql);
							pstmt1.setString(1, tranCode);
							rs1 = pstmt1.executeQuery();
							if(rs1.next())
							{
								transMode = rs1.getString( "TRANS_MODE" );
							}
							rs1.close();rs1 = null;
							pstmt1.close();pstmt1 = null;
							*/
							//Ended By Mahesh Patidar on 29/06/12
							sql = "SELECT SITE.DESCR, SUPPLIER.SUPP_NAME, SUPPLIER.QC_REQD FROM SITE SITE, SUPPLIER SUPPLIER WHERE SITE.SITE_CODE = ? AND SUPPLIER.SUPP_CODE = ?";
							pstmt1 = conn.prepareStatement(sql);
							pstmt1.setString(1, loginSite);
							pstmt1.setString(2, suppCode);
							rs1 = pstmt1.executeQuery();
							if(rs1.next())
							{										
								siteDescr = rs1.getString("DESCR");
								suppName = rs1.getString("SUPP_NAME");
								qcReqd = rs1.getString("QC_REQD");
							}
							if (rs1 != null)
							{
								rs1.close();
								rs1 = null;
							}
							if (pstmt1 != null)
							{
								pstmt1.close();
								pstmt1 = null;
							}

							// added by cpatil on 19-07-12 as per manoharan sir said start 

							sql = "select post_type from itemser where item_ser in (select  item_ser  from porder where purc_order = ? )";
							pstmt1 = conn.prepareStatement(sql);
							pstmt1.setString(1,purcOrder );
							rs1 = pstmt1.executeQuery();
							if(rs1.next())
							{										
								postType = rs1.getString("post_type") == null ? "" : rs.getString("post_type");
							}
							if (rs1 != null)
							{
								rs1.close();
								rs1 = null;
							}
							if (pstmt1 != null)
							{
								pstmt1.close();
								pstmt1 = null;
							}

							if(postType==null)
							{
								postType="A";
							}
							System.out.println("@@@@@---- post_type :["+postType+"]");
							// end by cpatil

							valueXmlString.append( "<tran_type><![CDATA[" ).append(  checkNull(rs.getString("TRAN_TYPE")) ).append( "]]></tran_type>\r\n" );
							valueXmlString.append( "<site_code><![CDATA[" ).append(  checkNull(rs.getString("SITE_CODE")) ).append( "]]></site_code>\r\n" );
							valueXmlString.append( "<site_descr><![CDATA[" ).append( checkNull(siteDescr) ).append( "]]></site_descr>\r\n" );
							valueXmlString.append( "<asn_no><![CDATA[" ).append( asnNo ).append( "]]></asn_no>\r\n" );
							valueXmlString.append( "<purc_order><![CDATA[" ).append(  checkNull(rs.getString("PURC_ORDER")) ).append( "]]></purc_order>\r\n" );
							valueXmlString.append( "<supp_code><![CDATA[" ).append(  checkNull(rs.getString("SUPP_CODE")) ).append( "]]></supp_code>\r\n" );
							valueXmlString.append( "<supp_name><![CDATA[" ).append( checkNull(suppName) ).append( "]]></supp_name>\r\n" );
							valueXmlString.append( "<dc_no><![CDATA[" ).append(  checkNull(rs.getString("DC_NO")) ).append( "]]></dc_no>\r\n" );
							valueXmlString.append( "<amount><![CDATA[" ).append(  checkNull(rs.getString("AMOUNT")) ).append( "]]></amount>\r\n" );
							valueXmlString.append( "<discount><![CDATA[" ).append(  checkNull(rs.getString("DISCOUNT")) ).append( "]]></discount>\r\n" );
							valueXmlString.append( "<adj_amt><![CDATA[" ).append(  checkNull(rs.getString("ADJ_AMT")) ).append( "]]></adj_amt>\r\n" );
							valueXmlString.append( "<tax><![CDATA[" ).append(  checkNull(rs.getString("TAX")) ).append( "]]></tax>\r\n" );
							valueXmlString.append( "<curr_code><![CDATA[" ).append(  checkNull(rs.getString("CURR_CODE")) ).append( "]]></curr_code>\r\n" );
							valueXmlString.append( "<exch_rate><![CDATA[" ).append(  checkNull(rs.getString("EXCH_RATE")) ).append( "]]></exch_rate>\r\n" );
							valueXmlString.append( "<tran_ser><![CDATA[" ).append(  checkNull(rs.getString("TRAN_SER")) ).append( "]]></tran_ser>\r\n" );
							valueXmlString.append( "<gr_no><![CDATA[" ).append(  checkNull(rs.getString("GR_NO")) ).append( "]]></gr_no>\r\n" );
							valueXmlString.append( "<excise_ref><![CDATA[" ).append(  checkNull(rs.getString("EXCISE_REF")) ).append( "]]></excise_ref>\r\n" );
							valueXmlString.append( "<tran_id__ref><![CDATA[" ).append(  checkNull(rs.getString("TRAN_ID__REF")) ).append( "]]></tran_id__ref>\r\n" );
							valueXmlString.append( "<confirmed><![CDATA[" ).append(  checkNull(rs.getString("CONFIRMED")) ).append( "]]></confirmed>\r\n" );
							valueXmlString.append( "<remarks><![CDATA[" ).append( checkNull(rs.getString("REMARKS")) ).append( "]]></remarks>\r\n" );
							valueXmlString.append( "<chg_date><![CDATA[" ).append(  currDateStr ).append( "]]></chg_date>\r\n" );
							valueXmlString.append( "<chg_user><![CDATA[" ).append( checkNull(userId) ).append( "]]></chg_user>\r\n" );
							valueXmlString.append( "<chg_term><![CDATA[" ).append( checkNull(rs.getString("CHG_TERM")) ).append( "]]></chg_term>\r\n" );
							valueXmlString.append( "<vouch_created><![CDATA[" ).append(  checkNull(rs.getString("VOUCH_CREATED")) ).append( "]]></vouch_created>\r\n" );
							valueXmlString.append( "<lr_no><![CDATA[" ).append(  checkNull(rs.getString("LR_NO")) ).append( "]]></lr_no>\r\n" );
							valueXmlString.append( "<lorry_no><![CDATA[" ).append(  checkNull(rs.getString("LORRY_NO")) ).append( "]]></lorry_no>\r\n" );
							valueXmlString.append( "<trans_mode><![CDATA[" ).append(  transMode ).append( "]]></trans_mode>\r\n" );
							valueXmlString.append( "<item_ser><![CDATA[" ).append(  checkNull(rs.getString("ITEM_SER")) ).append( "]]></item_ser>\r\n" );
							//valueXmlString.append( "<frt_amt><![CDATA[" ).append(  checkNull(rs.getString("FRT_AMT")) ).append( "]]></frt_amt>\r\n" );//Commented By Mahesh Patidar on 29/06/12
							valueXmlString.append( "<frt_amt><![CDATA[0]]></frt_amt>\r\n" );//Added By Mahesh Patidar on 29/06/12
							//valueXmlString.append( "<insurance_amt><![CDATA[" ).append(  checkNull(rs.getString("INSURANCE_AMT")) ).append( "]]></insurance_amt>\r\n" );//Commented By Mahesh Patidar on 29/06/12
							valueXmlString.append( "<insurance_amt><![CDATA[0]]></insurance_amt>\r\n" );//Added By Mahesh Patidar on 29/06/12
							valueXmlString.append( "<acct_inv_online><![CDATA[" ).append(  checkNull(rs.getString("ACCT_INV_ONLINE")) ).append( "]]></acct_inv_online>\r\n" );
							valueXmlString.append( "<acct_porcp_online><![CDATA[" ).append(  checkNull(rs.getString("ACCT_PORCP_ONLINE")) ).append( "]]></acct_porcp_online>\r\n" );
							valueXmlString.append( "<status><![CDATA[" ).append(  checkNull(rs.getString("STATUS")) ).append( "]]></status>\r\n" );
							valueXmlString.append( "<emp_code><![CDATA[" ).append(  checkNull(rs.getString("EMP_CODE")) ).append( "]]></emp_code>\r\n" );
							valueXmlString.append( "<emp_code__aprv><![CDATA[" ).append(  checkNull(rs.getString("EMP_CODE__APRV")) ).append( "]]></emp_code__aprv>\r\n" );
							valueXmlString.append( "<ret_opt><![CDATA[" ).append(  checkNull(rs.getString("RET_OPT")) ).append( "]]></ret_opt>\r\n" );
							valueXmlString.append( "<clearing_charges><![CDATA[" ).append(  checkNull(rs.getString("CLEARING_CHARGES")) ).append( "]]></clearing_charges>\r\n" );
							valueXmlString.append( "<benefit_type><![CDATA[" ).append(  checkNull(rs.getString("BENEFIT_TYPE")) ).append( "]]></benefit_type>\r\n" );
							valueXmlString.append( "<total_additional_cost><![CDATA[" ).append(  checkNull(rs.getString("TOTAL_ADDITIONAL_COST")) ).append( "]]></total_additional_cost>\r\n" );
							valueXmlString.append( "<curr_code__clr><![CDATA[" ).append(  checkNull(rs.getString("CURR_CODE__CLR")) ).append( "]]></curr_code__clr>\r\n" );
							valueXmlString.append( "<exch_rate__clr><![CDATA[" ).append(  checkNull(rs.getString("EXCH_RATE__CLR")) ).append( "]]></exch_rate__clr>\r\n" );
							valueXmlString.append( "<curr_code__ins><![CDATA[" ).append(  checkNull(rs.getString("CURR_CODE__INS")) ).append( "]]></curr_code__ins>\r\n" );
							valueXmlString.append( "<exch_rate__ins><![CDATA[" ).append(  checkNull(rs.getString("EXCH_RATE__INS")) ).append( "]]></exch_rate__ins>\r\n" );
							valueXmlString.append( "<curr_code__frt><![CDATA[" ).append(  checkNull(rs.getString("CURR_CODE__FRT")) ).append( "]]></curr_code__frt>\r\n" );
							valueXmlString.append( "<exch_rate__frt><![CDATA[" ).append(  checkNull(rs.getString("EXCH_RATE__FRT")) ).append( "]]></exch_rate__frt>\r\n" );								
							valueXmlString.append( "<qc_reqd><![CDATA[" ).append(  checkNull(rs.getString("QC_REQD")) ).append( "]]></qc_reqd>\r\n" );
							valueXmlString.append( "<invoice_no><![CDATA[" ).append(  checkNull(rs.getString("INVOICE_NO")) ).append( "]]></invoice_no>\r\n" );
							valueXmlString.append( "<supp_code__cha><![CDATA[" ).append(  checkNull(rs.getString("SUPP_CODE__CHA")) ).append( "]]></supp_code__cha>\r\n" );
							valueXmlString.append( "<stan_code__load><![CDATA[" ).append(  checkNull(rs.getString("STAN_CODE__LOAD")) ).append( "]]></stan_code__load>\r\n" );
							valueXmlString.append( "<stan_code__dest><![CDATA[" ).append(  checkNull(rs.getString("STAN_CODE__DEST")) ).append( "]]></stan_code__dest>\r\n" );								
							valueXmlString.append( "<tax_ref><![CDATA[" ).append(  checkNull(rs.getString("TAX_REF")) ).append( "]]></tax_ref>\r\n" );
							valueXmlString.append( "<reciept_type><![CDATA[" ).append(  checkNull(rs.getString("RECIEPT_TYPE")) ).append( "]]></reciept_type>\r\n" );
							valueXmlString.append( "<frt_type><![CDATA[" ).append(  checkNull(rs.getString("FRT_TYPE")) ).append( "]]></frt_type>\r\n" );
							valueXmlString.append( "<frt_rate><![CDATA[" ).append(  checkNull(rs.getString("FRT_RATE")) ).append( "]]></frt_rate>\r\n" );
							valueXmlString.append( "<supp_exc_inv><![CDATA[" ).append(  checkNull(rs.getString("SUPP_EXC_INV")) ).append( "]]></supp_exc_inv>\r\n" );
							valueXmlString.append( "<accept_criteria><![CDATA[" ).append(  checkNull(rs.getString("ACCEPT_CRITERIA")) ).append( "]]></accept_criteria>\r\n" );								
							valueXmlString.append( "<supp_code__ship><![CDATA[" ).append(  checkNull(rs.getString("SUPP_CODE__SHIP")) ).append( "]]></supp_code__ship>\r\n" );
							valueXmlString.append( "<print_stat><![CDATA[" ).append(  checkNull(rs.getString("PRINT_STAT")) ).append( "]]></print_stat>\r\n" );
							valueXmlString.append( "<other_charges><![CDATA[" ).append(  checkNull(rs.getString("OTHER_CHARGES")) ).append( "]]></other_charges>\r\n" );
							valueXmlString.append( "<curr_code__othch><![CDATA[" ).append(  checkNull(rs.getString("CURR_CODE__OTHCH")) ).append( "]]></curr_code__othch>\r\n" );
							valueXmlString.append( "<exch_rate__othch><![CDATA[" ).append(  checkNull(rs.getString("EXCH_RATE__OTHCH")) ).append( "]]></exch_rate__othch>\r\n" );
							valueXmlString.append( "<freight_status><![CDATA[" ).append(  checkNull(rs.getString("FREIGHT_STATUS")) ).append( "]]></freight_status>\r\n" );
							valueXmlString.append( "<octroi_no><![CDATA[" ).append(  checkNull(rs.getString("OCTROI_NO")) ).append( "]]></octroi_no>\r\n" );								
							//valueXmlString.append( "<post_type><![CDATA[" ).append(  checkNull(rs.getString("POST_TYPE")) ).append( "]]></post_type>\r\n" );
							valueXmlString.append( "<post_type><![CDATA[" ).append( postType ).append( "]]></post_type>\r\n" );        // modified by cpatil 19-07-12 for post_type
							valueXmlString.append( "<gp_ser><![CDATA[" ).append(  checkNull(rs.getString("GP_SER")) ).append( "]]></gp_ser>\r\n" );
							valueXmlString.append( "<parent__tran_id><![CDATA[" ).append(  checkNull(rs.getString("PARENT__TRAN_ID")) ).append( "]]></parent__tran_id>\r\n" );
							valueXmlString.append( "<rev__tran><![CDATA[" ).append(  checkNull(rs.getString("REV__TRAN")) ).append( "]]></rev__tran>\r\n" );
							valueXmlString.append( "<shipment_id><![CDATA[" ).append(  checkNull(rs.getString("SHIPMENT_ID")) ).append( "]]></shipment_id>\r\n" );
							valueXmlString.append( "<workflow_status><![CDATA[" ).append(  checkNull(rs.getString("WORKFLOW_STATUS")) ).append( "]]></workflow_status>\r\n" );
							valueXmlString.append( "<git_ref><![CDATA[" ).append(  checkNull(rs.getString("GIT_REF")) ).append( "]]></git_ref>\r\n" );								
							valueXmlString.append( "<fn_get_vouchno><![CDATA[" ).append(  checkNull(rs.getString("FN_GET_VOUCHNO")) ).append( "]]></fn_get_vouchno>\r\n" );
							valueXmlString.append( "<udf__str1><![CDATA[" ).append(  checkNull(rs.getString("UDF__STR1")) ).append( "]]></udf__str1>\r\n" );
							valueXmlString.append( "<post_prov><![CDATA[" ).append(  checkNull(rs.getString("POST_PROV")) ).append( "]]></post_prov>\r\n" );
							valueXmlString.append( "<lot_no><![CDATA[" ).append(  checkNull(rs.getString("LOT_NO")) ).append( "]]></lot_no>\r\n" );
							valueXmlString.append( "<anal_code><![CDATA[" ).append(  checkNull(rs.getString("ANAL_CODE")) ).append( "]]></anal_code>\r\n" );
							valueXmlString.append( "<transfer_flag><![CDATA[" ).append(  checkNull(rs.getString("TRANSFER_FLAG")) ).append( "]]></transfer_flag>\r\n" );
							valueXmlString.append( "<shelf_life__type><![CDATA[" ).append(  checkNull(rs.getString("SHELF_LIFE__TYPE")) ).append( "]]></shelf_life__type>\r\n" );
							valueXmlString.append( "<frt_amt__fixed><![CDATA[" ).append(  checkNull(rs.getString("FRT_AMT__FIXED")) ).append( "]]></frt_amt__fixed>\r\n" );
							valueXmlString.append( "<frt_amt__qty><![CDATA[" ).append(  checkNull(rs.getString("FRT_AMT__QTY")) ).append( "]]></frt_amt__qty>\r\n" );
							valueXmlString.append( "<tot_amt><![CDATA[" ).append(  checkNull(rs.getString("TOT_AMT")) ).append( "]]></tot_amt>\r\n" );
							valueXmlString.append( "<reciept_type><![CDATA[" ).append(  checkNull(rs.getString("RECIEPT_TYPE")) ).append( "]]></reciept_type>\r\n" );
							valueXmlString.append("<tran_code><![CDATA[").append(checkNull(tranCode)).append("]]></tran_code>\r\n"); //added by kunal on 05/09/12

							valueXmlString.append("</Detail2>\r\n");
						}
						else
						{
							if (rs != null)
							{
								rs.close();
								rs = null;
							}
							if (pstmt != null)
							{
								pstmt.close();
								pstmt = null;
							}
							sql = "SELECT PURC_ORDER, SITE_CODE, SUPP_CODE, SCAC_CODE, INVOICE_NO, ASN_DATE,  LR_NO FROM ASN_HDR WHERE ASN_NO = ? ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, asnNo);
							rs = pstmt.executeQuery();
							if(rs.next())
							{										
								purcOrderdb = rs.getString("PURC_ORDER");
								siteCode = rs.getString("SITE_CODE");
								suppCode = rs.getString("SUPP_CODE");
								invoiceNo = checkNull( rs.getString("INVOICE_NO") );
								asnDateDb = rs.getDate("ASN_DATE");
								scacCode = checkNull( rs.getString("SCAC_CODE") );
								lrNo = checkNull( rs.getString("LR_NO") );
								
								asnDate = dateFormat.format(asnDateDb);
							}
							rs.close(); rs = null;
							pstmt.close(); pstmt = null;
							if ("INVALID_SITE".equals(loginSite))
							{
								loginSite = siteCode;
							}
							
							//sql = "SELECT TRAN_CODE,TRANS_MODE FROM PORDER WHERE PURC_ORDER = ? "; //added by Kunal on 05/09/12 
							sql = "SELECT TRAN_CODE,TRANS_MODE,PORD_TYPE FROM PORDER WHERE PURC_ORDER = ? "; //added by Kunal on 05/09/12
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1,purcOrderdb );
							rs = pstmt.executeQuery();
							if(rs.next())
							{										
								tranCode = rs.getString("TRAN_CODE");
								transMode = rs.getString("TRANS_MODE");
								pordType = rs.getString("PORD_TYPE");
							}
							rs.close(); rs = null;
							pstmt.close(); pstmt = null;
							
							//comment bt kunal on 05/09/12
							/*
							sql = "SELECT TRAN_CODE FROM TRANSPORTER WHERE SCAC_CODE = ? ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, scacCode);
							rs = pstmt.executeQuery();
							if(rs.next())
							{										
								tranCode = rs.getString("TRAN_CODE");
							}
							rs.close(); rs = null;
							pstmt.close(); pstmt = null;

							//*Added By Ashish Sonawane [START]
							sql = "SELECT TRANS_MODE FROM TRANSPORTER_MODE WHERE TRAN_CODE = ? ";
							pstmt1 = conn.prepareStatement(sql);
							pstmt1.setString(1, tranCode);
							rs1 = pstmt1.executeQuery();
							if(rs1.next())
							{
								transMode = rs1.getString( "TRANS_MODE" );
							}
							rs1.close();rs1 = null;
							pstmt1.close();pstmt1 = null;
							//Added By Ashish Sonawane [END]
							
							*/

							//sql = "SELECT ITEM_SER, CURR_CODE FROM PORDER WHERE PURC_ORDER = ? ";
							sql = "SELECT ITEM_SER, CURR_CODE, ACCEPT_CRITERIA FROM PORDER WHERE PURC_ORDER = ? ";//ACCEPT_CRITERIA Added by Ashish Sonawane on 28/08/12
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, purcOrder);
							rs = pstmt.executeQuery();
							if(rs.next())
							{										
								itemSer = checkNull(rs.getString("ITEM_SER"));
								currCode = checkNull(rs.getString("CURR_CODE"));
								accptCriteria=checkNull(rs.getString("ACCEPT_CRITERIA"));
							}
							rs.close(); rs = null;
							pstmt.close(); pstmt = null;


							// 26/10/11 manoharan qc_reqd added
							sql = "SELECT SITE.DESCR, SUPPLIER.SUPP_NAME, SUPPLIER.CURR_CODE, SUPPLIER.QC_REQD FROM SITE SITE, SUPPLIER SUPPLIER WHERE SITE.SITE_CODE = ? AND SUPPLIER.SUPP_CODE = ?";
							// end 26/10/11 manoharan qc_reqd added
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, loginSite);
							pstmt.setString(2, suppCode);
							rs = pstmt.executeQuery();
							if(rs.next())
							{										
								siteDescr = rs.getString("DESCR");
								suppName = rs.getString("SUPP_NAME");									
								currCode = rs.getString("CURR_CODE");									
								// 26/10/11 manoharan qc_reqd added
								qcReqd = rs.getString("QC_REQD");
								// end 26/10/11 manoharan qc_reqd added
							}
							rs.close(); rs = null;
							pstmt.close(); pstmt = null;
							// 26/10/11 manoharan qc_reqd added
							if (qcReqd == null)
							{
								qcReqd = "N";
							}
							// end 26/10/11 manoharan qc_reqd added

							sql = "SELECT A.CURR_CODE FROM FINENT A, SITE B WHERE B.FIN_ENTITY = A.FIN_ENTITY AND B.SITE_CODE = ? ";
							pstmt = conn.prepareStatement( sql );
							pstmt.setString(1, loginSite );
							rs = pstmt.executeQuery();
							if( rs.next() )
							{
								currCodeBase = checkNull( rs.getString("CURR_CODE") );
							}


							sql = "SELECT SUM(CASE WHEN QUANTITY IS NULL THEN 0 ELSE QUANTITY END *  CASE WHEN RATE IS NULL THEN 0 ELSE RATE END) " +
									" AS AMOUNT FROM PORDDET WHERE PURC_ORDER = ? ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, purcOrder);
							rs = pstmt.executeQuery();
							if ( rs.next() )
							{
								amount = rs.getDouble("AMOUNT");
							}
							rs.close();rs = null;
							pstmt.close();pstmt = null;

							System.out.println("currDateStr..>>>>>>>>>>>>>["+currDateStr+"]");
							exchRate = finCommon.getDailyExchRateSellBuy(currCode.trim(),"",loginSite.trim(),currDateStr.trim(),"B", conn);
							System.out.println("exchRate..>>>>>>>>>>>>>["+exchRate+"]");

							//valueXmlString.append( "<Detail2 domID='1'>\r\n" );
							//Added by Manoj dtd 20/08/2012
							valueXmlString.append("<Detail2 domID='1'  selected=\"N\">");
							valueXmlString.append( "<attribute selected=\"N\" updateFlag=\"A\" status=\"N\" pkNames=\"\"/>\r\n" );
							valueXmlString.append( "<tran_id/>" );
							valueXmlString.append( "<asn_no><![CDATA[" ).append( asnNo ).append( "]]></asn_no>\r\n" );
							valueXmlString.append( "<purc_order><![CDATA[" ).append(  checkNull(purcOrder) ).append( "]]></purc_order>\r\n" );
							//Changed Pragyan as per KB & Manoj sharma.start
							valueXmlString.append( "<tran_type><![CDATA[" ).append(pordType).append( "]]></tran_type>\r\n" );
							valueXmlString.append( "<site_code><![CDATA[" ).append( checkNull(loginSite) ).append( "]]></site_code>\r\n" );
							valueXmlString.append( "<site_descr><![CDATA[" ).append( checkNull(siteDescr) ).append( "]]></site_descr>\r\n" );
							valueXmlString.append( "<supp_code><![CDATA[" ).append( checkNull(suppCode) ).append( "]]></supp_code>\r\n" );
							valueXmlString.append("<supp_code__ship><![CDATA[").append(checkNull(suppCode)).append("]]></supp_code__ship>");
							valueXmlString.append( "<supp_name><![CDATA[" ).append( checkNull(suppName) ).append( "]]></supp_name>\r\n" );
							valueXmlString.append( "<tran_date><![CDATA[" ).append( checkNull(currDateStr) ).append( "]]></tran_date>\r\n" );
							valueXmlString.append( "<eff_date><![CDATA[" ).append( checkNull(currDateStr) ).append( "]]></eff_date>\r\n" );
							valueXmlString.append("<dc_no><![CDATA[").append(checkNull(invoiceNo)).append("]]></dc_no>");
							valueXmlString.append("<invoice_no><![CDATA[").append(checkNull(invoiceNo)).append("]]></invoice_no>");
							//changed by sankara on 30/09/13 for set Invoice Date start.
							valueXmlString.append("<invoice_date><![CDATA[").append(asnDate).append("]]></invoice_date>");
						   //changed by sankara on 30/09/13 for set Invoice Date end.	
							valueXmlString.append("<dc_date><![CDATA[").append(asnDate).append("]]></dc_date>");
							valueXmlString.append("<tran_code><![CDATA[").append(checkNull(tranCode)).append("]]></tran_code>");
							valueXmlString.append("<asn_no><![CDATA[").append(checkNull(asnNo)).append("]]></asn_no>");
							valueXmlString.append("<discount><![CDATA[").append("0").append("]]></discount>");
							valueXmlString.append("<amount><![CDATA[" ).append( amount ).append( "]]></amount>\r\n" );
							valueXmlString.append("<tot_amt><![CDATA[" ).append( amount ).append( "]]></tot_amt>\r\n" );
							valueXmlString.append("<adj_amt><![CDATA[").append("0").append("]]></adj_amt>");
							valueXmlString.append("<curr_code><![CDATA[").append(checkNull(currCode)).append("]]></curr_code>");
							valueXmlString.append("<tran_ser><![CDATA[").append("P-RCP").append("]]></tran_ser>");
							valueXmlString.append("<item_ser><![CDATA[").append( itemSer ).append("]]></item_ser>");
							valueXmlString.append("<tax_date><![CDATA[").append(currDateStr).append("]]></tax_date>");
							valueXmlString.append("<rec_date><![CDATA[").append(currDateStr).append("]]></rec_date>");
							valueXmlString.append("<confirmed><![CDATA[").append("N").append("]]></confirmed>");
							valueXmlString.append("<remarks><![CDATA[").append(checkNull(remarks)).append("]]></remarks>");
							valueXmlString.append("<vouch_created><![CDATA[").append("N").append("]]></vouch_created>");
							valueXmlString.append("<lr_no><![CDATA[").append( checkNull(lrNo) ).append("]]></lr_no>");
							valueXmlString.append("<lr_date><![CDATA[").append( currDateStr ).append("]]></lr_date>");
							valueXmlString.append("<emp_code><![CDATA[").append( checkNull(loginEmpCode) ).append("]]></emp_code>");
							valueXmlString.append("<status><![CDATA[").append( checkNull(status) ).append("]]></status>");
							valueXmlString.append("<exch_rate><![CDATA[").append( exchRate ).append("]]></exch_rate>");
							valueXmlString.append("<curr_code__clr><![CDATA[").append( checkNull(currCodeBase )).append("]]></curr_code__clr>");
							valueXmlString.append("<curr_code__ins><![CDATA[").append( checkNull(currCodeBase) ).append("]]></curr_code__ins>");
							valueXmlString.append("<curr_code__frt><![CDATA[").append( checkNull(currCodeBase) ).append("]]></curr_code__frt>");
							valueXmlString.append("<exch_rate__clr><![CDATA[").append( exchRate ).append("]]></exch_rate__clr>");
							valueXmlString.append("<exch_rate__ins><![CDATA[").append( exchRate ).append("]]></exch_rate__ins>");
							valueXmlString.append("<exch_rate__frt><![CDATA[").append( exchRate ).append("]]></exch_rate__frt>");
							valueXmlString.append( "<chg_date><![CDATA[" ).append( checkNull(currDateStr) ).append( "]]></chg_date>\r\n" );
							valueXmlString.append( "<chg_user><![CDATA[" ).append( checkNull(userId) ).append( "]]></chg_user>\r\n" );
							valueXmlString.append( "<chg_term><![CDATA[" ).append( checkNull(chgTerm) ).append( "]]></chg_term>\r\n" );
							valueXmlString.append( "<freight_status><![CDATA[B]]></freight_status>\r\n" );
							// 26/10/11 manoharan qc_reqd added
							valueXmlString.append( "<qc_reqd><![CDATA[" ).append(  qcReqd ).append( "]]></qc_reqd>\r\n" );
							// end 26/10/11 manoharan qc_reqd added
							/* Added By Ashish Sonawane on 28/08/12 as per manoharan Sir [START] */
							valueXmlString.append( "<frt_amt><![CDATA[0]]></frt_amt>\r\n" );
							valueXmlString.append( "<insurance_amt><![CDATA[0]]></insurance_amt>\r\n" );
							valueXmlString.append( "<trans_mode><![CDATA[" ).append(  transMode ).append( "]]></trans_mode>\r\n" );
							valueXmlString.append( "<accept_criteria><![CDATA[" ).append(  accptCriteria ).append( "]]></accept_criteria>\r\n" );
							valueXmlString.append( "<reciept_type><![CDATA[" ).append( "F" ).append( "]]></reciept_type>\r\n" );
							/* Added By Ashish Sonawane on 28/08/12 as per manoharan Sir [END] */

							valueXmlString.append("</Detail2>\r\n");
						}
						if (rs != null)
						{
							rs.close();
							rs = null;
						}
						if (pstmt != null)
						{
							pstmt.close();
							pstmt = null;
						}
					}
				}
				break;
			}//case 1 end here
			case 3:
			{
				if( currentColumn.trim().equalsIgnoreCase( "itm_default" ) )							
				{
					purcOrder = checkNull(genericUtility.getColumnValue("purc_order", allDom, "2"));


					sql = "SELECT TRAN_ID FROM PORCP WHERE PURC_ORDER = ?  AND CASE WHEN PORCP.CONFIRMED IS NULL THEN 'N'  ELSE PORCP.CONFIRMED END = 'N'";
					pstmt =  conn.prepareStatement(sql);
					pstmt.setString(1, purcOrder);
					rs = pstmt.executeQuery();
					if(rs.next())
					{								
						tranId = rs.getString("TRAN_ID");								
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					//Added By Mahesh Patidar on 29/06/12
					sql = "SELECT PACK_CODE FROM PORDDET WHERE PURC_ORDER = ?";
					pstmt =  conn.prepareStatement(sql);
					pstmt.setString(1, purcOrder);
					rs = pstmt.executeQuery();
					if(rs.next())
					{								
						packCode = rs.getString("PACK_CODE");								
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					//Ended By Mahesh Patidar 
					domID = 1;
					scanLocCode = genericUtility.getColumnValue( "loc_code", hdrDom );
					if( tranId != null && tranId.trim().length() > 0 )
					{
						//Changed By Pragyan 21-APR-14 To check confirmed flag
						//sql = "SELECT MAX(PORCPDET.LINE_NO) AS MAX_LINE FROM PORCPDET WHERE TRAN_ID = ? ";
						sql = "SELECT MAX(PORCPDET.LINE_NO) AS MAX_LINE FROM PORCPDET PORCPDET,PORCP PORCP WHERE " 
							+" PORCP.TRAN_ID = PORCPDET.TRAN_ID AND  "
							+" PORCP.TRAN_ID = ? AND CASE WHEN PORCP.CONFIRMED IS NULL THEN 'N'  ELSE PORCP.CONFIRMED END = 'N'";
						pstmt =  conn.prepareStatement(sql);
						pstmt.setString(1, tranId);
											
						rs = pstmt.executeQuery();
						if(rs.next())
						{								
							maxLine = rs.getInt("MAX_LINE"); //added by kunal Mandhre on 01/09/12								
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						System.out.println("max line="+maxLine);
						domID = maxLine;

						sql = "SELECT  PORCPDET.TRAN_ID, PORCPDET.LINE_NO, PORCPDET.ITEM_CODE, PORCPDET.PURC_ORDER, PORCPDET.QUANTITY, PORCPDET.UNIT, PORCPDET.RATE, PORCPDET.DISCOUNT, PORCPDET.TAX_AMT,"+
								"PORCPDET.NET_AMT, PORCPDET.LOC_CODE, PORCPDET.LOT_NO, PORCPDET.LOT_SL, PORCPDET.LINE_NO__ORD, PORCPDET.CANC_BO, PORCPDET.VOUCH_QTY, PORCPDET.ACCT_CODE__DR, "+
								"PORCPDET.CCTR_CODE__DR, PORCPDET.ACCT_CODE__CR, PORCPDET.CCTR_CODE__CR, PORCPDET.UNIT__RATE, PORCPDET.CONV__QTY_STDUOM,"+
								"CONV__RTUOM_STDUOM, PORCPDET.UNIT__STD, PORCPDET.QUANTITY__STDUOM, PORCPDET.RATE__STDUOM, PORCPDET.PACK_CODE, PORCPDET.NO_ART,"+
								"PORCPDET.PACK_INSTR, PORCPDET.BATCH_NO, PORCPDET.MFG_DATE, PORCPDET.EXPIRY_DATE, PORCPDET.GROSS_WEIGHT, PORCPDET.TARE_WEIGHT, PORCPDET.NET_WEIGHT, "+
								"PORCPDET.STATUS, PORCPDET.POTENCY_PERC, PORCPDET.SUPP_CODE__MNFR, PORCPDET.SITE_CODE__MFG , PORCPDET.REAS_CODE, PORCPDET.REMARKS, PORCPDET.CHALLAN_QTY, PORCPDET."+
								"GRADE, PORCPDET.TAX_CLASS, PORCPDET.TAX_CHAP, PORCPDET.TAX_ENV, PORCPDET.SPECIFIC_INSTR, PORCPDET.SPECIAL_INSTR, PORCPDET.LOC_CODE__EXCESS_SHORT, PORCPDET."+
								"EXCESS_SHORT_QTY, PORCPDET.ADDITIONAL_COST, PORCPDET.RATE__CLG, PORCPDET.SUPP_CHALLAN_QTY, PORCPDET.REALISED_QTY, PORCPDET.ITEM_CODE__MFG, PORCPDET."+
								"SPEC_REF, PORCPDET.STD_RATE, PORCPDET.EFFECT_STOCK, PORCPDET.DEPT_CODE, PORCPDET.PHYSICAL_STATUS, PORCPDET.BENEFIT_TYPE, PORCPDET.LICENCE_NO, PORCPDET."+
								"ACCT_CODE__PROV_DR, PORCPDET.CCTR_CODE__PROV_DR, PORCPDET.ACCT_CODE__PROV_CR, PORCPDET.CCTR_CODE__PROV_CR, PORCPDET.FORM_NO, PORCPDET."+
								"RETEST_DATE, PORCPDET.ASSETINSTALL_QTY, PORCPDET.BATCH_SIZE, PORCPDET.DUTY_PAID, PORCPDET.SAMPLE_QTY, PORCPDET.DAMAGE_QTY, PORCPDET."+
								"SHELF_LIFE__TYPE, PORCPDET.REJC_QTY, PORCPDET.SH_QTY, ITEM.DESCR ITEM_DESCR, PORCPDET.QC_REQD, ITEM.SITE_CODE,PORCPDET.PARTIAL_YN "+
								//Changed By Pragyan 21-APR-14 To check confirmed flag
								//"FROM PORCPDET PORCPDET, ITEM ITEM WHERE PORCPDET.TRAN_ID = ? AND PORCPDET.ITEM_CODE = ITEM.ITEM_CODE ORDER BY PORCPDET.TRAN_ID, PORCPDET.LINE_NO ";
								"FROM PORCPDET PORCPDET, ITEM ITEM,PORCP PORCP WHERE PORCP.TRAN_ID = PORCPDET.TRAN_ID AND  PORCPDET.TRAN_ID = ? AND CASE WHEN PORCP.CONFIRMED IS NULL THEN 'N'  ELSE PORCP.CONFIRMED END = 'N' AND PORCPDET.ITEM_CODE = ITEM.ITEM_CODE ORDER BY PORCPDET.TRAN_ID, PORCPDET.LINE_NO ";
						pstmt =  conn.prepareStatement(sql);
						pstmt.setString(1, tranId);
						
						
						rs = pstmt.executeQuery();

						String retestDate = "";
						String mfgDateStr = "";
						String lpnNo = "";
						String locCode = "";
						String siteCodeMfg = "";//Added By Mahesh Patidar on 29/06/12
						//String expDate = "";

						java.sql.Date retestDateDb = null;
						java.sql.Date mfgDateDb = null;
						java.sql.Date expDateDb1 = null;

						while(rs.next())
						{

							//domID ++;

							//changed by gulzar on 30/12/11
							//lineNo = rs.getString("LINE_NO");
							//lineNo = "   " + domID;
							lineNo = "   " + rs.getString("LINE_NO");
							lineNo = lineNo.substring( lineNo.length()-3 );
							//End changes by gulzar on 30/12/11

							lotNo = checkNullAndTrim(rs.getString("LOT_NO"));
							lpnNo = checkNullAndTrim(rs.getString("LOT_SL"));
							locCode = checkNull(rs.getString("LOC_CODE"));
							siteCodeMfg = checkNull(rs.getString("SITE_CODE"));//Added By Mahesh Patidar on 29/06/12

							if ( lotNo.trim().length() > 0 && lpnNo.trim().length() > 0 && locCode.trim().length() > 0 )
							{
								valueXmlString.append( "<Detail3 domID='"+ rs.getString("LINE_NO").trim() +"' objContext = '"+currentFormNo+"' objName=\"porcp_wiz\" selected=\"Y\">\r\n" );
								valueXmlString.append( "<attribute selected=\"Y\" updateFlag=\"E\" status=\"O\" pkNames=\"\"/>\r\n" );
								//valueXmlString.append( "<attribute selected=\"Y\"/>\r\n" );
							}
							else
							{
								valueXmlString.append( "<Detail3 domID='"+ rs.getString("LINE_NO").trim() +"' objContext = '"+currentFormNo+"' objName=\"porcp_wiz\" selected=\"N\">\r\n" );
								valueXmlString.append( "<attribute selected=\"N\" updateFlag=\"A\" status=\"N\" pkNames=\"\"/>\r\n" );
							}
							//Added by manoj dtd 20/08/2012

							valueXmlString.append("<tran_id><![CDATA[").append(checkNull(rs.getString("TRAN_ID"))).append("]]></tran_id>\r\n");
							//valueXmlString.append("<line_no><![CDATA[").append(checkNull(rs.getString("LINE_NO"))).append("]]></line_no>\r\n");//Gulzar on 30/12/11
							valueXmlString.append("<line_no><![CDATA[").append( lineNo ).append("]]></line_no>\r\n"); //Gulzar on 30/12/11
							valueXmlString.append("<item_code><![CDATA[").append(checkNullAndTrim(rs.getString("ITEM_CODE"))).append("]]></item_code>\r\n");
							valueXmlString.append("<item_descr><![CDATA[").append(checkNull(rs.getString("ITEM_DESCR"))).append("]]></item_descr>\r\n");
							valueXmlString.append("<purc_order><![CDATA[").append(checkNull(rs.getString("PURC_ORDER"))).append("]]></purc_order>\r\n");
							valueXmlString.append("<quantity><![CDATA[").append(rs.getDouble("QUANTITY")).append("]]></quantity>\r\n");
							valueXmlString.append("<unit><![CDATA[").append(checkNull(rs.getString("UNIT"))).append("]]></unit>\r\n");
							valueXmlString.append("<rate><![CDATA[").append(rs.getDouble("RATE")).append("]]></rate>\r\n");
							valueXmlString.append("<discount><![CDATA[").append(rs.getDouble("DISCOUNT")).append("]]></discount>\r\n");
							//valueXmlString.append("<tax_amt><![CDATA[").append(rs.getDouble("TAX_AMT") ).append("]]></tax_amt>\r\n");//Commented By Mahesh Patidar on 29/06/12
							valueXmlString.append("<tax_amt><![CDATA[0]]></tax_amt>\r\n");//Changed By Mahesh Patidar on 29/06/12
							valueXmlString.append("<net_amt><![CDATA[").append(rs.getDouble("NET_AMT")).append("]]></net_amt>\r\n");
							valueXmlString.append("<loc_code><![CDATA[").append(locCode).append("]]></loc_code>\r\n");
							valueXmlString.append("<lot_no><![CDATA[").append(lotNo).append("]]></lot_no>\r\n");
							valueXmlString.append("<lot_sl><![CDATA[").append(lpnNo).append("]]></lot_sl>\r\n");
							valueXmlString.append("<line_no__ord><![CDATA[").append(checkNull(rs.getString("LINE_NO__ORD"))).append("]]></line_no__ord>\r\n");
							valueXmlString.append("<canc_bo><![CDATA[").append(checkNull(rs.getString("CANC_BO"))).append("]]></canc_bo>\r\n");
							valueXmlString.append("<vouch_qty><![CDATA[").append(rs.getDouble("VOUCH_QTY")).append("]]></vouch_qty>\r\n");
							valueXmlString.append("<acct_code__dr><![CDATA[").append(checkNull(rs.getString("ACCT_CODE__DR"))).append("]]></acct_code__dr>\r\n");
							valueXmlString.append("<cctr_code__dr><![CDATA[").append(checkNull(rs.getString("CCTR_CODE__DR"))).append("]]></cctr_code__dr>\r\n");
							valueXmlString.append("<acct_code__cr><![CDATA[").append(checkNull(rs.getString("ACCT_CODE__CR"))).append("]]></acct_code__cr>\r\n");
							valueXmlString.append("<cctr_code__cr><![CDATA[").append(checkNull(rs.getString("CCTR_CODE__CR"))).append("]]></cctr_code__cr>\r\n");
							valueXmlString.append("<unit__rate><![CDATA[").append(checkNull(rs.getString("UNIT__RATE"))).append("]]></unit__rate>\r\n");
							valueXmlString.append("<conv__qty_stduom><![CDATA[").append(rs.getDouble("CONV__QTY_STDUOM")).append("]]></conv__qty_stduom>\r\n");
							valueXmlString.append("<conv__rtuom_stduom><![CDATA[").append(rs.getDouble("CONV__RTUOM_STDUOM")).append("]]></conv__rtuom_stduom>\r\n");
							valueXmlString.append("<unit__std><![CDATA[").append(checkNull(rs.getString("UNIT__STD"))).append("]]></unit__std>\r\n");
							valueXmlString.append("<quantity__stduom><![CDATA[").append(rs.getDouble("QUANTITY__STDUOM")).append("]]></quantity__stduom>\r\n");
							valueXmlString.append("<rate__stduom><![CDATA[").append(rs.getDouble("RATE__STDUOM")).append("]]></rate__stduom>\r\n");
							//valueXmlString.append("<pack_code><![CDATA[").append(checkNull(rs.getString("PACK_CODE"))).append("]]></pack_code>\r\n");//Commented By Mahesh Patidar on 29/06/12
							valueXmlString.append("<pack_code><![CDATA[").append(packCode).append("]]></pack_code>\r\n");//Changed By Mahesh Patidar on 29/06/12
							valueXmlString.append("<no_art><![CDATA[").append(rs.getInt("NO_ART")).append("]]></no_art>\r\n");
							valueXmlString.append("<pack_instr><![CDATA[").append(checkNull(rs.getString("PACK_INSTR"))).append("]]></pack_instr>\r\n");
							valueXmlString.append("<batch_no><![CDATA[").append(checkNull(rs.getString("BATCH_NO"))).append("]]></batch_no>\r\n");

							mfgDateDb = rs.getDate("MFG_DATE");
							if ( mfgDateDb != null )
							{
								mfgDateStr = genericUtility.getValidDateString( mfgDateDb.toString() , dbDateFormat, applDateFormat );
								valueXmlString.append( "<mfg_date><![CDATA[" ).append( mfgDateStr ).append( "]]></mfg_date>\r\n" );
							}
							expDateDb1 = rs.getDate("EXPIRY_DATE");
							if ( expDateDb1 != null )
							{
								expDate = genericUtility.getValidDateString( expDateDb1.toString() , dbDateFormat, applDateFormat );
								valueXmlString.append( "<expiry_date><![CDATA[" ).append( expDate ).append( "]]></expiry_date>\r\n" );
							}
							valueXmlString.append("<gross_weight><![CDATA[").append(rs.getDouble("GROSS_WEIGHT")).append("]]></gross_weight>\r\n");
							valueXmlString.append("<tare_weight><![CDATA[").append(rs.getDouble("TARE_WEIGHT")).append("]]></tare_weight>\r\n");
							valueXmlString.append("<net_weight><![CDATA[").append(rs.getDouble("NET_WEIGHT")).append("]]></net_weight>\r\n");
							//valueXmlString.append("<status><![CDATA[").append(checkNull(rs.getString("STATUS"))).append("]]></status>\r\n");//Commented By Mahesh Patidar on 29/06/12
							valueXmlString.append("<status><![CDATA[P]]></status>\r\n");//Added By Mahesh Patidar on 29/06/12
							valueXmlString.append("<potency_perc><![CDATA[").append(rs.getDouble("POTENCY_PERC")).append("]]></potency_perc>\r\n");
							valueXmlString.append("<supp_code__mnfr><![CDATA[").append(checkNull(rs.getString("SUPP_CODE__MNFR"))).append("]]></supp_code__mnfr>\r\n");
							//valueXmlString.append("<site_code__mfg><![CDATA[").append(checkNull(rs.getString("SITE_CODE__MFG"))).append("]]></site_code__mfg>\r\n");//Commented By Mahesh Patidar on 29/06/12
							valueXmlString.append("<site_code__mfg><![CDATA[").append(siteCodeMfg).append("]]></site_code__mfg>\r\n");//Changed By Mahesh Patidar on 29/06/12
							valueXmlString.append("<reas_code><![CDATA[").append(checkNull(rs.getString("REAS_CODE"))).append("]]></reas_code>\r\n");
							valueXmlString.append("<remarks><![CDATA[").append(checkNull(rs.getString("REMARKS"))).append("]]></remarks>\r\n");
							valueXmlString.append("<challan_qty><![CDATA[").append(rs.getDouble("CHALLAN_QTY")).append("]]></challan_qty>\r\n");
							valueXmlString.append("<grade><![CDATA[").append( checkNull(rs.getString("GRADE"))).append("]]></grade>\r\n");
							valueXmlString.append("<tax_class><![CDATA[").append( checkNull(rs.getString("TAX_CLASS"))).append("]]></tax_class>\r\n");
							valueXmlString.append("<tax_chap><![CDATA[").append(checkNull(rs.getString("TAX_CHAP"))).append("]]></tax_chap>\r\n");
							valueXmlString.append("<tax_env><![CDATA[").append(checkNull(rs.getString("TAX_ENV"))).append("]]></tax_env>\r\n");
							valueXmlString.append("<specific_instr><![CDATA[").append(checkNull(rs.getString("SPECIFIC_INSTR"))).append("]]></specific_instr>\r\n");
							valueXmlString.append("<special_instr><![CDATA[").append(checkNull(rs.getString("SPECIAL_INSTR"))).append("]]></special_instr>\r\n");
							valueXmlString.append("<loc_code__excess_short><![CDATA[").append(checkNull(rs.getString("LOC_CODE__EXCESS_SHORT"))).append("]]></loc_code__excess_short>\r\n");
							valueXmlString.append("<excess_short_qty><![CDATA[").append(rs.getDouble("LOC_CODE__EXCESS_SHORT")).append("]]></excess_short_qty>\r\n");
							valueXmlString.append("<additional_cost><![CDATA[").append(rs.getDouble("ADDITIONAL_COST")).append("]]></additional_cost>\r\n");
							valueXmlString.append("<rate__clg><![CDATA[").append(rs.getDouble("RATE__CLG")).append("]]></rate__clg>\r\n");
							valueXmlString.append("<supp_challan_qty><![CDATA[").append(rs.getDouble("SUPP_CHALLAN_QTY")).append("]]></supp_challan_qty>\r\n");
							valueXmlString.append("<realised_qty><![CDATA[").append(rs.getDouble("REALISED_QTY")).append("]]></realised_qty>\r\n");
							valueXmlString.append("<item_code__mfg><![CDATA[").append(checkNull(rs.getString("ITEM_CODE__MFG"))).append("]]></item_code__mfg>\r\n");
							valueXmlString.append("<spec_ref><![CDATA[").append(checkNull(rs.getString("SPEC_REF"))).append("]]></spec_ref>\r\n");
							valueXmlString.append("<std_rate><![CDATA[").append(rs.getDouble("STD_RATE")).append("]]></std_rate>\r\n");
							valueXmlString.append("<effect_stock><![CDATA[").append(checkNull(rs.getString("EFFECT_STOCK"))).append("]]></effect_stock>\r\n");
							valueXmlString.append("<dept_code><![CDATA[").append(checkNull(rs.getString("DEPT_CODE"))).append("]]></dept_code>\r\n");
							valueXmlString.append("<physical_status><![CDATA[").append(checkNull(rs.getString("PHYSICAL_STATUS"))).append("]]></physical_status>\r\n");
							valueXmlString.append("<benefit_type><![CDATA[").append(checkNull(rs.getString("BENEFIT_TYPE"))).append("]]></benefit_type>\r\n");
							valueXmlString.append("<licence_no><![CDATA[").append(checkNull(rs.getString("LICENCE_NO"))).append("]]></licence_no>\r\n");
							valueXmlString.append("<acct_code__prov_dr><![CDATA[").append(checkNull(rs.getString("ACCT_CODE__PROV_DR"))).append("]]></acct_code__prov_dr>\r\n");
							valueXmlString.append("<cctr_code__prov_dr><![CDATA[").append(checkNull(rs.getString("CCTR_CODE__PROV_DR"))).append("]]></cctr_code__prov_dr>\r\n");
							valueXmlString.append("<acct_code__prov_cr><![CDATA[").append(checkNull(rs.getString("ACCT_CODE__PROV_CR"))).append("]]></acct_code__prov_cr>\r\n");
							valueXmlString.append("<cctr_code__prov_cr><![CDATA[").append(checkNull(rs.getString("CCTR_CODE__PROV_CR"))).append("]]></cctr_code__prov_cr>\r\n");
							valueXmlString.append("<form_no><![CDATA[").append(checkNull(rs.getString("FORM_NO"))).append("]]></form_no>\r\n");
							valueXmlString.append("<partial_yn><![CDATA[").append(rs.getString("PARTIAL_YN")).append("]]></partial_yn>\r\n");
							retestDateDb = rs.getDate("RETEST_DATE");
							if ( retestDateDb != null )
							{
								retestDate = genericUtility.getValidDateString( retestDateDb.toString() , dbDateFormat, applDateFormat );
								valueXmlString.append( "<retest_date><![CDATA[" ).append( retestDate ).append( "]]></retest_date>\r\n" );
							}

							valueXmlString.append("<assetinstall_qty><![CDATA[").append(rs.getDouble("ASSETINSTALL_QTY")).append("]]></assetinstall_qty>\r\n");
							valueXmlString.append("<batch_size><![CDATA[").append(rs.getDouble("BATCH_SIZE")).append("]]></batch_size>\r\n");
							valueXmlString.append("<duty_paid><![CDATA[").append(checkNull(rs.getString("DUTY_PAID"))).append("]]></duty_paid>\r\n");
							valueXmlString.append("<sample_qty><![CDATA[").append(rs.getDouble("SAMPLE_QTY")).append("]]></sample_qty>\r\n");
							valueXmlString.append("<damage_qty><![CDATA[").append(rs.getDouble("DAMAGE_QTY")).append("]]></damage_qty>\r\n");
							valueXmlString.append("<shelf_life__type><![CDATA[").append(checkNull(rs.getString("SHELF_LIFE__TYPE"))).append("]]></shelf_life__type>\r\n");
							valueXmlString.append("<rejc_qty><![CDATA[").append(rs.getDouble("REJC_QTY")).append("]]></rejc_qty>\r\n");
							valueXmlString.append("<sh_qty><![CDATA[").append(rs.getDouble("SH_QTY")).append("]]></sh_qty>\r\n");
							// 26/10/11 manoharan qc_reqd added
							valueXmlString.append("<qc_reqd><![CDATA[").append(checkNull(rs.getString("QC_REQD"))).append("]]></qc_reqd>\r\n");
							// end 26/10/11 manoharan qc_reqd added
							valueXmlString.append("</Detail3>\r\n");
						}
					}
					else
					{
						asnNo = checkNull(genericUtility.getColumnValue("asn_no", hdrDom));
						scanLocCode = genericUtility.getColumnValue( "loc_code", hdrDom );
						sql = "SELECT TRAN_ID, PURC_ORDER FROM ASN_HDR WHERE ASN_NO = ? ";
						pstmt =  conn.prepareStatement(sql);
						pstmt.setString(1, asnNo);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							tranId = rs.getString("TRAN_ID");
							purcOrder = rs.getString("PURC_ORDER");
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;


						/*
							sql = "SELECT ITEM_CODE, LOT_NO, QUANTITY FROM ASN_DET WHERE TRAN_ID = ? ";
							pstmt =  conn.prepareStatement(sql);
							pstmt.setString(1, tranId);
							rs = pstmt.executeQuery();

							domID = 0;
							while( rs.next() )
							{
								itemCode = checkNullAndTrim(rs.getString("ITEM_CODE"));
								lotNo = checkNullAndTrim(rs.getString("LOT_NO"));
								quantity = rs.getDouble("QUANTITY");

								domID++;
								valueXmlString.append( "<Detail3 domID='"+ domID +"'>\r\n" );
								valueXmlString.append("<tran_id/>");
								valueXmlString.append("<line_no/>");
								valueXmlString.append("<purc_order><![CDATA[").append(purcOrder).append("]]></purc_order>");
								valueXmlString.append("<item_code><![CDATA[").append(itemCode).append("]]></item_code>");
								valueXmlString.append("<lot_no><![CDATA[").append(lotNo).append("]]></lot_no>");
								valueXmlString.append("<quantity><![CDATA[").append(quantity).append("]]></quantity>");
								valueXmlString.append("<loc_code/>");									
								valueXmlString.append("<lot_sl/>");
								valueXmlString.append("<unit/>");								
								valueXmlString.append("<no_art/>");
								valueXmlString.append("</Detail3>\r\n");
							}
						 */
					}
					
					asnNo = checkNull(genericUtility.getColumnValue("asn_no", hdrDom));
					
					System.out.println(" ASN NO PORCP IC --------------["+asnNo+"]");
					//Changed by Rohan on 17-June-13 for getting inv stat of location.start 
					//sql = "SELECT case when loc_type is null then ' ' else loc_type end from location where loc_code =  ? ";
					pgrlInvStat =  distComm.getDisparams("999999","PGRL_INVSTAT",conn);
					sql = "SELECT case when loc_type is null then ' ' else loc_type end,INV_STAT from location where loc_code =  ? ";
					//Changed by Rohan on 17-June-13 for getting inv stat of location.end
					pstmt =  conn.prepareStatement(sql);
					pstmt.setString(1, scanLocCode);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						locType = rs.getString(1);
						//Changed by Rohan on 17-June-13 for getting inv stat of location
						invStat = checkNull(rs.getString("INV_STAT"));
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					sql  = "SELECT SHIPPER_SIZE, GROSS_WEIGHT, NET_WEIGHT FROM ITEM_LOT_PACKSIZE WHERE ITEM_CODE = ? AND LOT_NO__FROM <= ? AND LOT_NO__TO >= ? ";
					pstmtItemLotPack = conn.prepareStatement(sql);
					scanLocCode = genericUtility.getColumnValue( "loc_code", hdrDom );
					//Changhed By Pragyan 21-apr-14 to take the quantity from asn detail
					//sql = 	"SELECT ADET.NO_PALLET,PDET.LINE_NO, PDET.ITEM_CODE, PDET.QUANTITY, PDET.UNIT, PDET.RATE, "+
					sql = 	"SELECT ADET.NO_PALLET,PDET.LINE_NO, PDET.ITEM_CODE, ADET.QUANTITY, PDET.UNIT, PDET.RATE, "+
							"PDET.DISCOUNT, PDET.TAX_AMT, PDET.TOT_AMT, PDET.LOC_CODE,  "+
							"PDET.REQ_DATE, PDET.DLV_QTY, PDET.STATUS, PDET.STATUS_DATE,  "+
							"PDET.UNIT__RATE, PDET.CONV__QTY_STDUOM, PDET.CONV__RTUOM_STDUOM,   "+
							"PDET.UNIT__STD, PDET.QUANTITY__STDUOM, PDET.RATE__STDUOM,   "+
							"PDET.NO_ART, PDET.ACCT_CODE__DR, PDET.CCTR_CODE__DR, PDET.ACCT_CODE__CR,   "+
							"PDET.CCTR_CODE__CR, PDET.RATE__CLG, PDET.STD_RATE, ADET.LOT_NO, ADET.EXP_DATE,   "+
							"ITEM.CANC_BO_MODE, ITEM.SHELF_LIFE, ITEM.DESCR ITEM_DESCR  , PDET.PACK_CODE ,ITEM.SITE_CODE "+ //ITEM.SITE_CODE ADDED BY ASHISH as per manoharan Sir 28/08/12 
							"FROM PORDDET PDET, ASN_DET ADET, ITEM ITEM, ASN_HDR ASN_HDR "+
							"WHERE ASN_HDR.PURC_ORDER =  PDET.PURC_ORDER  "+
							"AND ASN_HDR.TRAN_ID = ADET.TRAN_ID  "+
							"AND PDET.PURC_ORDER = ?  "+
							"AND case when item.loc_type is null then ' '  else item.loc_type end = '" + locType + "' " +
							"AND ASN_HDR.ASN_NO = ?  " +
							"AND PDET.LINE_NO = ADET.LINE_NO__ORD   "+
							//changed by Dhanraj on 18/07/14 for unconfirmed item only.
							//"AND PDET.ITEM_CODE = ITEM.ITEM_CODE  " ;
							"AND PDET.ITEM_CODE = ITEM.ITEM_CODE " ;
					//Changed by Pragyan on 08-Aug-14 Remove Dlv_qty condition.
							 //" AND PDET.DLV_QTY < ADET.QUANTITY " ;
					//Commented by Manoj to Show Unscanned records in Edit mode also
					//"AND PDET.LINE_NO NOT IN(SELECT LINE_NO__ORD FROM PORCPDET WHERE ASN_HDR.PURC_ORDER = PORCPDET.PURC_ORDER )";

					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, purcOrder);
					pstmt.setString(2, asnNo);
					
					rs = pstmt.executeQuery();
					//domID = 0;//Gulzar on 30/12/11
					while( rs.next())
					{

						lineNoOrd = rs.getString("LINE_NO");

						lineNoOrd = "   " + lineNoOrd;
						lineNoOrd = lineNoOrd.substring( lineNoOrd.length()-3 );

                        //changed by sankara on 14/10/14 for added checknull condition 
						itemCode = checkNull(rs.getString("ITEM_CODE")) ;
						quantity = rs.getDouble("QUANTITY");
						unit = checkNull(rs.getString("UNIT"));
						rate = rs.getDouble("RATE");
						expDateDb = rs.getTimestamp("EXP_DATE");
						selfLife = rs.getInt("SHELF_LIFE");
						lotNo = checkNull(rs.getString("LOT_NO"));
						siteCodeMf= checkNull(rs.getString("SITE_CODE"));
						System.out.println("@@@@siteCodeMf ["+siteCodeMf+"]");
						
						//Changed by Pragyan on 08-Aug-14 Add function  for get available for receipt .
						HashMap dataHashMap = getAvilableQtyForReceipt(itemCode, lotNo, purcOrder,asnNo, quantity, conn);
						
						if(dataHashMap.size() > 0)
						{
							quantity = (Double)dataHashMap.get("REQ_SCAN_QTY");
						}
						
						if(quantity <= 0.0)
						{
							System.out.println("Call Continue if the value :["+quantity+"]");
							continue;
						}
						/*Added By Ashish Sonawane on 03/Aug/12 for condition when maintaining lot master is true [END] */
						String isMaintainLotMaster =  distComm.getDisparams("999999","MAINTAIN_LOT_MASTER",conn);
						System.out.println("isMaintainLotMaster :["+isMaintainLotMaster+"]");
						int cntLotMaster=0;


						if("Y".equalsIgnoreCase(isMaintainLotMaster))
						{
							String sqlLotMaster = "SELECT MFG_DATE , EXP_DATE  FROM ITEM_LOT_INFO WHERE ITEM_CODE =?  AND LOT_NO =?";
							pstmtLotMaster=conn.prepareStatement(sqlLotMaster);
							pstmtLotMaster.setString(1,itemCode);
							pstmtLotMaster.setString(2,lotNo);
							rsLotMaster = pstmtLotMaster.executeQuery();
							if(rsLotMaster.next())
							{
								lotMfgDate=rsLotMaster.getTimestamp("MFG_DATE");
								lotExpDate=rsLotMaster.getTimestamp("EXP_DATE");
								expDateDb=lotExpDate;
								cntLotMaster=1;
							}
							System.out.println("cntLotMaster :["+cntLotMaster+"]");
							if(cntLotMaster == 0)
							{
								String sqlAltLotMaster = "SELECT LOT_NO ,MFG_DATE , EXP_DATE FROM ITEM_LOT_INFO WHERE ITEM_CODE =?  AND ALT_LOT_NO =?";
								pstmtAltLotMaster=conn.prepareStatement(sqlAltLotMaster);
								pstmtAltLotMaster.setString(1,itemCode);
								pstmtAltLotMaster.setString(2,lotNo);
								rsAltLotMaster = pstmtAltLotMaster.executeQuery();
								if(rsAltLotMaster.next())
								{
									lotNo=rsAltLotMaster.getString("LOT_NO");
									lotMfgDate=rsAltLotMaster.getTimestamp("MFG_DATE");
									lotExpDate=rsAltLotMaster.getTimestamp("EXP_DATE");
									expDateDb=lotExpDate;
									System.out.println("Lot No :["+lotNo+"]");
								}
								else
								{
									System.out.println("ohhhh!! No Record in Lot Master for item_code ["+itemCode+"] and lot no ["+lotNo+"]");
								}
								rsAltLotMaster.close();
								rsAltLotMaster =null;

								pstmtAltLotMaster.close();
								pstmtAltLotMaster =null;
							}
							pstmtLotMaster.close();
							pstmtLotMaster =null;
							rsLotMaster.close();
							rsLotMaster =null ;


						}
						/*Added By Ashish Sonawane on 03/Aug/12 for condition when maintaining lot master is true [END] */
						packCode = checkNull(rs.getString("PACK_CODE"));
						System.out.println("@@@@packCode ["+packCode+"]");
						//Added By Mahesh Patidar on 29/06/12
						String cancBoMode = checkNull(rs.getString("CANC_BO_MODE"));
						if(cancBoMode.equals("A"))
						{
							cancBoMode = "Y";	
						}
						else
						{
							cancBoMode = "N";
						}
						//Ended By Mahesh Patidar
						if ( expDateDb != null )
						{
							expDate = dateFormat.format(expDateDb);
						}
						if("Y".equalsIgnoreCase(isMaintainLotMaster))  //Condition Added by Ashish Sonawane on 07/Aug/12 for setting manufacturing date from the Item_lot_info 
						{
							mfgDate=lotMfgDate;
						}
						else
						{	
							mfgDate = distComm.CalcExpiry( expDateDb, ((-1)*selfLife) );
						}
						if ( mfgDate != null )
						{
							expDateStr = dateFormat.format(mfgDate);
						}
						netAmt = (quantity * rate);

						pstmtItemLotPack.setString(1, itemCode);
						pstmtItemLotPack.setString(2, lotNo);
						pstmtItemLotPack.setString(3, lotNo);
						rsItemLotPack = pstmtItemLotPack.executeQuery();
						if ( rsItemLotPack.next() )
						{
							shipperSize =  rsItemLotPack.getDouble("SHIPPER_SIZE");
							grossWeight =  rsItemLotPack.getDouble("GROSS_WEIGHT");
							netWeight =  rsItemLotPack.getDouble("NET_WEIGHT");
							tareWeight = grossWeight - netWeight;
							if ( shipperSize > 0 )
							{
								noArt = (int)(quantity / shipperSize);
							}
						}
						rsItemLotPack.close();
						rsItemLotPack = null;
						pstmtItemLotPack.clearParameters();
						//Shamim Splitting as discussion with manohran sir on 25/06/12
						//							this function is used only for calculation of nos of article per pallet not for nos of LPN
						// nos of LPN will come from asn det							
						arr=findNosOfPallet(itemCode, quantity, lotNo, conn).split(";");

						//							totalNoOfPallet =Integer.parseInt(arr[0]);
						totalNoOfPallet=rs.getInt("NO_PALLET");
						//							System.out.println("<!@#>totalNoOfPallet" +arr);
						System.out.println("<!@#>totalNoOfPallet" +totalNoOfPallet);
						//							nosOfArtPerPallet=Integer.parseInt(arr[1]);
						nosOfArtPerPallet=Integer.parseInt(arr[1]);
						//							nosOfArtPerPallet="";
						System.out.println("<!@#>nosOfArtPerPallet" +nosOfArtPerPallet);
						//							extraArtForLastPallet=new Double (noArt -(noArt/nosOfArtPerPallet)).intValue();
						//							System.out.println("<!@#>extraArtForLastPallet" +extraArtForLastPallet);
						System.out.println("<!@#>quantity" +quantity);
						//calculating extraArticles
						extraArtForLastPallet=(int)noArt;
						//domID = maxLine;	//added by Kunal on 01/09/12 
						int i=0 ;
						//Changed By Pragyan 21-apr-14 to remove only specific lot
						//Added by Manoj dtd 03/09/2012 to show unscanned records in edit mode
						//pstmt1=conn.prepareStatement("select count(*) from porcpdet a where a.purc_order=? and line_no__ord=?");
						pstmt1=conn.prepareStatement("select count(*) from porcpdet a , porcp p where a.purc_order=? and a.line_no__ord=? and a.lot_no = ? " +
								" and p.tran_id = a.tran_id and case when p.confirmed is null then 'N'  else p.confirmed end = 'N'");
						pstmt1.setString(1,purcOrder);
						pstmt1.setString(2,lineNoOrd);
						//Changed By Pragyan 21-apr-14 to remove only specific lot
						pstmt1.setString(3,lotNo);
						
						rs1=pstmt1.executeQuery();
						if(rs1.next())
						{
							i=rs1.getInt(1);
						}
						rs1.close();
						rs1=null;
						pstmt1.close();
						pstmt1=null;
						
						for(;i< totalNoOfPallet ;i++)
						{

							domID++;
							lineNo = "   " + domID;
							lineNo = lineNo.substring( lineNo.length()-3 );
							//								extraArtForLastPallet-=nosOfArtPerPallet;
							valueXmlString.append( "<Detail3 domID='"+ domID +"' objContext = '"+currentFormNo+"' objName=\"porcp_wiz\" selected=\"N\">\r\n" );
							//Added by manoj dtd 20/08/2012
							valueXmlString.append( "<attribute selected=\"N\" updateFlag=\"A\" status=\"N\" pkNames=\"\"/>\r\n" );
							valueXmlString.append("<tran_id/>");
							valueXmlString.append("<line_no><![CDATA[").append(lineNo).append("]]></line_no>\r\n");
							valueXmlString.append("<purc_order><![CDATA[").append(purcOrder).append("]]></purc_order>\r\n");
							valueXmlString.append("<line_no__ord><![CDATA[").append(lineNoOrd).append("]]></line_no__ord>\r\n");	 
							valueXmlString.append("<item_code><![CDATA[").append( checkNullAndTrim(itemCode) ).append("]]></item_code>\r\n");
							valueXmlString.append("<item_descr><![CDATA[").append( checkNullAndTrim(rs.getString("ITEM_DESCR")) ).append("]]></item_descr>\r\n");
							//valueXmlString.append("<quantity><![CDATA[").append(quantity).append("]]></quantity>\r\n");//6/1/2012
							valueXmlString.append("<quantity><![CDATA[").append("0").append("]]></quantity>\r\n");//6/1/2012
							valueXmlString.append("<unit><![CDATA[").append(unit).append("]]></unit>\r\n");
							valueXmlString.append("<rate><![CDATA[").append(rs.getDouble("RATE")).append("]]></rate>\r\n");
							valueXmlString.append("<std_rate><![CDATA[").append(rs.getDouble("STD_RATE")).append("]]></std_rate>\r\n");
							valueXmlString.append("<discount><![CDATA[").append(rs.getDouble("DISCOUNT")).append("]]></discount>\r\n");
							valueXmlString.append("<tax_amt><![CDATA[").append(rs.getDouble("TAX_AMT")).append("]]></tax_amt>\r\n");
							valueXmlString.append("<net_amt><![CDATA[").append(netAmt).append("]]></net_amt>\r\n");
							//valueXmlString.append("<loc_code><![CDATA[").append( checkNull(rs.getString("LOC_CODE")) ).append("]]></loc_code>\r\n"); //commented By Ashish on 03/Aug/12
							valueXmlString.append("<loc_code><![CDATA[").append( scanLocCode ).append("]]></loc_code>\r\n"); //Added By Ashish on 03/Aug/12
							//valueXmlString.append("<lot_no><![CDATA[").append( checkNullAndTrim(rs.getString("LOT_NO")) ).append("]]></lot_no>\r\n"); //commented By Ashish on 03/Aug/12
							valueXmlString.append("<lot_no><![CDATA[").append( checkNullAndTrim(lotNo) ).append("]]></lot_no>\r\n"); //Added By Ashish on 03/Aug/12
							//valueXmlString.append("<canc_bo><![CDATA[").append( checkNull(rs.getString("CANC_BO_MODE")) ).append("]]></canc_bo>\r\n");//Commented By Mahesh Patidar on 29/06/12
							valueXmlString.append("<canc_bo><![CDATA[").append( cancBoMode ).append("]]></canc_bo>\r\n");//Changed By Mahesh Patidar on 29/06/12
							valueXmlString.append("<vouch_qty><![CDATA[").append( "0" ).append("]]></vouch_qty>\r\n");
							valueXmlString.append("<acct_code__dr><![CDATA[").append( checkNull(rs.getString("ACCT_CODE__DR")) ).append("]]></acct_code__dr>\r\n");
							valueXmlString.append("<cctr_code__dr><![CDATA[").append( checkNull(rs.getString("CCTR_CODE__DR")) ).append("]]></cctr_code__dr>\r\n");
							valueXmlString.append("<acct_code__cr><![CDATA[").append( checkNull(rs.getString("ACCT_CODE__CR")) ).append("]]></acct_code__cr>\r\n");
							valueXmlString.append("<cctr_code__cr><![CDATA[").append( checkNull(rs.getString("CCTR_CODE__CR")) ).append("]]></cctr_code__cr>\r\n");
							valueXmlString.append("<unit__rate><![CDATA[").append( checkNull(rs.getString("UNIT__RATE")) ).append("]]></unit__rate>\r\n");
							valueXmlString.append("<conv__qty_stduom><![CDATA[").append( checkNull(rs.getString("CONV__QTY_STDUOM")) ).append("]]></conv__qty_stduom>\r\n");
							valueXmlString.append("<conv__rtuom_stduom><![CDATA[").append( checkNull(rs.getString("CONV__RTUOM_STDUOM")) ).append("]]></conv__rtuom_stduom>\r\n");
							valueXmlString.append("<unit__std><![CDATA[").append( checkNull(rs.getString("UNIT__STD")) ).append("]]></unit__std>\r\n");
							valueXmlString.append("<quantity__stduom><![CDATA[").append( checkNull(rs.getString("QUANTITY__STDUOM")) ).append("]]></quantity__stduom>\r\n");
							valueXmlString.append("<rate__stduom><![CDATA[").append( checkNull(rs.getString("RATE__STDUOM")) ).append("]]></rate__stduom>\r\n");
							//Added by manoj for Conditional scanning computed field
							valueXmlString.append("<partial_yn><![CDATA[").append("N").append("]]></partial_yn>\r\n");
							//changes 
							if(extraArtForLastPallet >0 && (i==totalNoOfPallet-1))
							{
								//	valueXmlString.append("<no_art><![CDATA[").append( extraArtForLastPallet ).append("]]></no_art>\r\n");  // commented by Ashish  02/AUG/12
								System.out.println("<!@#> appending extraArtForLastPallet"+extraArtForLastPallet);

							}else
							{
								System.out.println("<!@#> appending nosOfArtPerPallet"+nosOfArtPerPallet);
								//	valueXmlString.append("<no_art><![CDATA[").append( nosOfArtPerPallet ).append("]]></no_art>\r\n"); // commented by Ashish  02/AUG/12

							}
							extraArtForLastPallet-=nosOfArtPerPallet;
							//Changed by Rohan 17-June-13 for appending no of art 0 in case of pgrl location.start  
							if(invStat.trim().equalsIgnoreCase(pgrlInvStat.trim()))
							{
								valueXmlString.append("<no_art><![CDATA[").append( "0" ).append("]]></no_art>\r\n"); 
							}
							//Changed by Rohan 17-June-13 for appending no of art 0 in case of pgrl location.end
							valueXmlString.append("<batch_no><![CDATA[").append( checkNull(rs.getString("LOT_NO")) ).append("]]></batch_no>\r\n");
							valueXmlString.append("<status><![CDATA[").append("O").append("]]></status>\r\n");
							valueXmlString.append("<potency_perc><![CDATA[").append("0").append("]]></potency_perc>\r\n");
							valueXmlString.append("<challan_qty><![CDATA[").append(quantity).append("]]></challan_qty>\r\n");
							valueXmlString.append("<supp_challan_qty><![CDATA[").append(quantity).append("]]></supp_challan_qty>\r\n");
							//valueXmlString.append("<realised_qty><![CDATA[").append(quantity).append("]]></realised_qty>\r\n");
							valueXmlString.append("<realised_qty><![CDATA[").append("0").append("]]></realised_qty>\r\n");//chnage done by kunal on 05/09/12
							valueXmlString.append("<excess_short_qty><![CDATA[").append("0").append("]]></excess_short_qty>\r\n");
							valueXmlString.append("<additional_cost><![CDATA[").append("0").append("]]></additional_cost>\r\n");
							valueXmlString.append("<rate__clg><![CDATA[").append(rs.getDouble("RATE__CLG")).append("]]></rate__clg>\r\n");//Added By Mahesh Patidar on 03/07/12
							//valueXmlString.append("<rate__clg><![CDATA[").append("0").append("]]></rate__clg>\r\n");//Commented By Mahesh Patidar on 03/07/12
							valueXmlString.append("<effect_stock><![CDATA[").append("Y").append("]]></effect_stock>\r\n");
							valueXmlString.append("<retest_date><![CDATA[").append(expDate).append("]]></retest_date>\r\n");
							valueXmlString.append("<expiry_date><![CDATA[").append(expDate).append("]]></expiry_date>\r\n");
							valueXmlString.append("<mfg_date><![CDATA[").append(expDateStr).append("]]></mfg_date>\r\n");
							valueXmlString.append("<gross_weight><![CDATA[").append(grossWeight * noArt).append("]]></gross_weight>\r\n");
							valueXmlString.append("<tare_weight><![CDATA[").append(tareWeight * noArt).append("]]></tare_weight>\r\n");
							valueXmlString.append("<net_weight><![CDATA[").append(netWeight * noArt).append("]]></net_weight>\r\n");
							//valueXmlString.append("<duty_paid><![CDATA[]]></duty_paid>");
							//valueXmlString.append("<form_no><![CDATA[]]></form_no>");
							//valueXmlString.append("<site_code__mfg><![CDATA[]]></site_code__mfg>");
							//valueXmlString.append("<cctr_code__prov_dr><![CDATA[]]></cctr_code__prov_dr>");
							//valueXmlString.append("<cctr_code__prov_cr><![CDATA[]]></cctr_code__prov_cr>");
							//valueXmlString.append("<licence_no><![CDATA[]]></licence_no>");
							//valueXmlString.append("<loc_code__excess_short><![CDATA[]]></loc_code__excess_short>");
							//valueXmlString.append("<pack_code><![CDATA[]]></pack_code>\r\n");//Commented By Mahesh Patidar on 29/06/12
							valueXmlString.append("<pack_code><![CDATA[").append(packCode).append("]]></pack_code>\r\n");//Changed By Mahesh Patidar on 29/06/12
							//valueXmlString.append("<site_code__mfg><![CDATA[]]></site_code__mfg>");
							//valueXmlString.append("<benefit_type><![CDATA[]]></benefit_type>");
							//valueXmlString.append("<dept_code><![CDATA[]]></dept_code>");
							//valueXmlString.append("<tax_env><![CDATA[]]></tax_env>");
							//valueXmlString.append("<tax_chap><![CDATA[]]></tax_chap>");
							//valueXmlString.append("<tax_class><![CDATA[]]></tax_class>");
							//valueXmlString.append("<reas_code><![CDATA[]]></reas_code>");
							valueXmlString.append("<damage_qty><![CDATA[").append("0").append("]]></damage_qty>\r\n");
							valueXmlString.append("<sample_qty><![CDATA[").append("0").append("]]></sample_qty>\r\n");
							//valueXmlString.append("<physical_status/>");
							valueXmlString.append("<batch_size><![CDATA[").append("0").append("]]></batch_size>\r\n");
							valueXmlString.append("<challan_qty><![CDATA[").append("0").append("]]></challan_qty>\r\n");
							//valueXmlString.append("<reas_code><![CDATA[]]></reas_code>");
							//valueXmlString.append("<acct_code__prov_dr><![CDATA[]]></acct_code__prov_dr>");
							//valueXmlString.append("<acct_code__prov_cr><![CDATA[]]></acct_code__prov_cr>");
							//valueXmlString.append("<special_instr><![CDATA[]]></special_instr>");
							//valueXmlString.append("<spec_ref><![CDATA[]]></spec_ref>");
							//valueXmlString.append("<pack_instr><![CDATA[]]></pack_instr>");
							//valueXmlString.append("<grade><![CDATA[]]></grade>");
							//valueXmlString.append("<specific_instr><![CDATA[]]></specific_instr>");
							//valueXmlString.append("<supp_code__mnfr><![CDATA[]]></supp_code__mnfr>");
							//valueXmlString.append("<item_code__mfg><![CDATA[]]></item_code__mfg>");

							// 26/10/11 manoharan qc_reqd as per siteitem / item
							sql = "SELECT CASE WHEN QC_REQD IS NULL THEN 'N' ELSE QC_REQD END AS QC_REQD "
									+ " FROM SITEITEM "
									+ " WHERE SITE_CODE = ? "
									+ " AND ITEM_CODE = ? ";
							pstmt1 = conn.prepareStatement(sql);
							pstmt1.setString(1, siteCode);
							pstmt1.setString(2, itemCode);
							rs1 = pstmt1.executeQuery();
							if(rs1.next())
							{
								qcReqd = rs.getString(1);
							}
							else
							{
								pstmt1.close();
								pstmt1 = null;
								rs1.close();
								rs1 = null;
								sql = "SELECT (CASE WHEN QC_REQD IS NULL THEN 'N' ELSE QC_REQD END) AS QC_REQD "
										+ " FROM ITEM "
										+ " WHERE ITEM_CODE = ? ";
								pstmt1 = conn.prepareStatement(sql);
								pstmt1.setString(1, itemCode);
								rs1 = pstmt1.executeQuery();
								if(rs1.next())
								{
									qcReqd = rs.getString(1);
								}
							}
							pstmt1.close();
							pstmt1 = null;
							rs1.close();
							rs1 = null;

							valueXmlString.append("<qc_reqd><![CDATA[").append(qcReqd).append("]]></qc_reqd>\r\n");
							// end 26/10/11 manoharan qc_reqd as per siteitem / item

							valueXmlString.append("<lot_sl><![CDATA[").append("").append("]]></lot_sl>\r\n");//changed by gulzar on 12/19/2011
							valueXmlString.append("<site_code__mfg><![CDATA[").append(siteCodeMf).append("]]></site_code__mfg>"); //Added by Ashish Sonawane 28/08/12 
							//change by shamim increasing line_no as discussed manohran sir
							//line =(Integer.parseInt(lineNo.trim()));
							//lineNo = "   " + ++line;
							//lineNo = lineNo.substring( lineNo.length()-3 );


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


						}

					}

					if ( pstmtItemLotPack != null )
					{
						pstmtItemLotPack.close();
						pstmtItemLotPack = null;
					}
					if (rs != null)
					{
						rs.close();
						rs = null;
					}
					if (pstmt != 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);

					///////////


				}//itm_default

				break;				  		
			}// case 2
			}//switch statement ends here
		}//try block ends here
		catch(Exception e)
		{
			System.out.println( "Exception :Porcp :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 :Porcp: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 = "", updateStatus = "";
		String domIDVal = "";
		String updatedDetailDomStr = "";
		String attributeVal = "";
		String locCode = "";
		String lpnNo = "";
		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("lot_sl"))
					{
						if ( childNode != null && childNode.getFirstChild() != null )
						{
							lpnNo = childNode.getFirstChild().getNodeValue();
							//toUpdNodeList.add( 1, childNode );
							dataMap.put("lot_sl", childNode);
						}
					}
					else if (nodeName.equalsIgnoreCase("loc_code"))
					{
						if ( childNode != null && childNode.getFirstChild() != null )
						{
							locCode = childNode.getFirstChild().getNodeValue();
							//toUpdNodeList.add( 1, childNode );
							dataMap.put("loc_code", 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" );

								Node lpnNoNode = null;
								Node locCodeNode = null;

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

								if ( dataMap.get("lot_sl") != null )
								{
									lpnNoNode = (Node)dataMap.get("lot_sl");
								}
								if ( dataMap.get("loc_code") != null )
								{
									locCodeNode = (Node)dataMap.get("loc_code");
								}

								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(lpnNoNode, true);
										//System.out.println("updatedNode =["+genericUtility.serializeDom( updatedNode )+"]");
										(detailDom.getElementsByTagName("Detail3").item(ctr)).replaceChild(updatedNode, elementName );
									}
									else if ( nodeName.equalsIgnoreCase("loc_code") )
									{
										//System.out.println("LocCodeToNode =["+genericUtility.serializeDom( LocCodeToNode )+"]");
										updatedNode = detailDom.importNode(locCodeNode, true);
										//System.out.println("updatedNode =["+genericUtility.serializeDom( updatedNode )+"]");
										(detailDom.getElementsByTagName("Detail3").item(ctr)).replaceChild(updatedNode, elementName );
									}
								}//inner for loop
							}//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;
	}
	//method to find value  from table conn ,no_pallet, asn_det, asn_no
	private String findValue(Connection conn, String columnName ,String tableName, String columnName2, String value) throws  ITMException, RemoteException
	{

		PreparedStatement pstmt = null ;
		ResultSet rs = null ; 
		String sql = "";
		String findValue = "";

		try
		{			
			sql = "SELECT " + columnName + " from " + tableName + " where " + columnName2 +"  = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,value);
			rs = pstmt.executeQuery();

			if(rs.next())
			{					
				findValue = rs.getString(columnName);
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;	        
		}
		catch(Exception e)
		{
			System.out.println("Exception in findValue ");
			e.printStackTrace();
			throw new ITMException(e); 
		}
		System.out.println("returning String from findValue ");
		return findValue;
	}
	private int findNosOfCase(String itemCode,String lotNo, double quantity ,double perPalletVolume , double perPalletWeight,Connection conn)  throws RemoteException,ITMException
	{

		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;
		int noCase=0;

		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);
			perPalletArticleWeight= (int)(((int)(((int)( (int)(perPalletWeight*shipperSize)/grossWeight))/shipperSize))*shipperSize);
			System.out.println("perPalletArticleWeight="+perPalletArticleWeight);
			perPalletArticleVolume= (int)(((int)(((int)(perPalletVolume*shipperSize/ (length*width*height) ) )/shipperSize))*shipperSize) ;

			//quantity
			System.out.println("perPalletArticleVolume="+perPalletArticleVolume);

			//
			if(perPalletArticleWeight>perPalletArticleVolume)
			{
				noCase=(int) (perPalletArticleVolume/shipperSize);
			}
			if(perPalletArticleVolume>perPalletArticleWeight)
			{
				noCase=(int) (perPalletArticleWeight/shipperSize);
			}



		} 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 noCase; 
	}
	private String findNosOfPallet(String itemCode, double quantity ,String lotNo, Connection conn) throws ITMException
	{
		int findNosOfPallet=0;
		int nosOfPalletPerVolume=0;
		int nosOfPalletPerWeight=0;
		int perPalletArticle=0;
		int extraArticle=0;
		String nosOfPalletAndperPalletArticle="";


		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;
		double perPalletWeight =0.0;
		double perPalletVolume=0.0;

		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);
			pstmt.setString(2,lotNo);
			pstmt.setString(3,lotNo);
			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;
			//calculation
			perPalletWeight=(Double.parseDouble(findValueFromDisparm("999999","PER_PALLET__WEIGHT",conn)));
			System.out.println("<!@#>nosOfPalletPerWeight" +nosOfPalletPerWeight);

			nosOfPallet=(quantity*grossWeight/shipperSize)/(perPalletWeight);
			//			System.out.println("<!@#>nosOfPalletPerWeight" +nosOfPallet);

			perPalletArticle=(new Double (Math.floor((perPalletWeight/grossWeight)))).intValue();
			System.out.println("<!@#>perPalletArticle" +perPalletArticle);

			nosOfPalletPerWeight=(new Double((Math.floor(nosOfPallet)))).intValue();
			System.out.println("<!@#>nosOfPalletPerWeight" +nosOfPalletPerWeight);

			nosOfPallet=(quantity*length*width*height/shipperSize)/(Double.parseDouble(findValueFromDisparm("999999","PER_PALLET__VOLUME",conn)));
			//			System.out.println("<!@#>nosOfPallet" +nosOfPallet);
			nosOfPalletPerVolume=(new Double((Math.floor(nosOfPallet)))).intValue();
			System.out.println("<!@#>nosOfPalletPerVolume" +nosOfPalletPerVolume);
			if(nosOfPalletPerWeight < nosOfPalletPerVolume )
			{
				//				findNosOfPallet=nosOfPalletPerWeight;
				nosOfPalletAndperPalletArticle=nosOfPalletPerWeight +";"+perPalletArticle;
			}else
			{
				findNosOfPallet=nosOfPalletPerVolume;
				perPalletVolume=(Double.parseDouble(findValueFromDisparm("999999","PER_PALLET__VOLUME",conn)));
				System.out.println("<!@#>nosOfPalletPerWeight" +perPalletVolume);

				nosOfPallet=(quantity*length*width*height/shipperSize)/(perPalletVolume);
				//				System.out.println("<!@#>nosOfPalletPerWeight" +nosOfPallet);

				perPalletArticle=(new Double (Math.floor((perPalletVolume/(length*width*height))))).intValue();
				System.out.println("<!@#>perPalletArticle" +perPalletArticle);
				nosOfPalletAndperPalletArticle=nosOfPalletPerVolume +";"+perPalletArticle;


			}

			System.out.println("<!@#>nosOfPallet And perPalletArticle" +nosOfPalletAndperPalletArticle);

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new ITMException(e);
		}
		//		nosOfPalletAndperPalletArticle=nosOfPalletPerWeight +";"+perPalletArticle;

		return nosOfPalletAndperPalletArticle; 
	}
	//find value from disparm
	private String findValueFromDisparm(String prdCode,String varName,Connection conn) throws 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);
			}
			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; 
	}
	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 Pragyan on 08-Aug-14 Add function  for get available for receipt .
	private HashMap getAvilableQtyForReceipt(String itemCode,String lotNo,String purcCorder,String asnNo,double asnQty,Connection conn)
	{
		double qtyAvilForScan = 0.0;
		String sSQL = "";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int palletCount = 0;
		HashMap rcpMap = new HashMap();
		
		
		try
		{
			sSQL = "SELECT SUM(QUANTITY),COUNT(D.LOT_SL) FROM PORCPDET D,PORCP P WHERE D.TRAN_ID = P.TRAN_ID AND P.ASN_NO = ?" 
					+" AND D.ITEM_CODE = ? AND  D.LOT_NO = ? AND P.PURC_ORDER = ? GROUP BY D.ITEM_CODE,D.LOT_NO ";
			
			pstmt = conn.prepareStatement(sSQL);
			pstmt.setString(1, asnNo);
			pstmt.setString(2, itemCode);
			pstmt.setString(3, lotNo);
			pstmt.setString(4, purcCorder);

			rs = pstmt.executeQuery();
			
			if(rs.next())
			{
				qtyAvilForScan = qtyAvilForScan +  rs.getDouble(1);
				palletCount = palletCount  +  rs.getInt(2);
			}
			
			System.out.println("qtyAvilForScan before["+qtyAvilForScan+"]");
			qtyAvilForScan = asnQty - qtyAvilForScan;
			System.out.println("qtyAvilForScan after["+qtyAvilForScan+"]");
			
			rcpMap.put("REQ_SCAN_QTY", qtyAvilForScan) ;
			rcpMap.put("REQ_PALLET_COUNT", palletCount) ;
			
			if(rs != null)
			{
				rs.close();
				rs = null;
			}
			
			if(pstmt != null)
			{
				pstmt.close();
				pstmt = null;
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		
		return rcpMap;
	}



}
