/********************************************************
	Title : ShipmentIC
	Date  : 01/06/2012
	Developer: Mahesh Patidar
 ********************************************************/

package ibase.webitm.ejb.wms;

import ibase.system.config.ConnDriver;
import ibase.utility.CommonConstants;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.ejb.ITMDBAccessLocal;
import ibase.webitm.ejb.ValidatorEJB;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;
import ibase.webitm.utility.TransIDGenerator;

import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;

import org.w3c.dom.CDATASection;
import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

@javax.ejb.Stateless
public class ShipmentIC extends ValidatorEJB implements ShipmentICRemote,ShipmentICLocal
{

	public String wfValData() throws RemoteException, ITMException
	{
		return "";
	}
	public String wfValData(String xmlString, String xmlString1, String xmlString2, String objContext, String editFlag, String xtraParams) throws RemoteException, ITMException
	{
		Document dom = null;
		Document dom1 = null;
		Document dom2 = null;
		String errString = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		System.out.println("WFVALDATA 111111");
		try
		{
			System.out.println("xmlString [" + xmlString + "]");
			System.out.println("xmlString1 [" + xmlString1 + "]");
			System.out.println("xmlString2 [" + xmlString2 + "]");
			if(xmlString != null && xmlString.trim().length()!=0)
			{
				dom = genericUtility.parseString(xmlString); 
			}
			if(xmlString1 != null && xmlString1.trim().length()!=0)
			{
				dom1 = genericUtility.parseString(xmlString1); 
			}
			if(xmlString2 != null && xmlString2.trim().length()!=0)
			{
				dom2 = genericUtility.parseString(xmlString2);
			}
			errString = wfValData( dom, dom1, dom2, objContext, editFlag, xtraParams );
			System.out.println ( "ErrString: " + errString);
		}
		catch(Exception e)
		{
			System.out.println ( "Exception: ShipmentIC: wfValData(String xmlString): " + e.getMessage() + ":" );
			errString = genericUtility.createErrorString(e);
			e.printStackTrace();

			throw new ITMException(e);
		}
		System.out.println ( "Returning from ShipmentIC wfValData" );
		return ( errString ); 
	}
	
	public String wfValData(Document dom, Document dom1, Document dom2, String objContext,String editFlag, String xtraParams ) throws RemoteException, ITMException
	{
		String errString = "";
		//Changed by sumit on 22/09/12 create refSer variable.
		String refSer = "";
		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<String> errList = new ArrayList<String>();
		ArrayList<String> errFields = new ArrayList<String>();
		NodeList parentList = null;
		NodeList parentNodeList = null;
		NodeList childList = null;
		Node parentNode= null;
		int noOfChilds ;
		String childNodeName = "";
		Node childNode =null;
		String userId = "",tranCodeHdr="";

		int cnt = 0;
		int ctr2 = 0;
		int lineNo = 0;
		int parentNodeListLength= 0;
		String columnValue = "";
		String columnValue1 = "",tranCode="";
		String sql = "";		
		int currentFormNo = 0;		
		long pronoFromAmt = 0;		
		long pronoToAmt = 0;		
		long pronoLastAmt = 0;
		ConnDriver connDriver = null;

		try
		{
			connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			
			userId = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginCode" );

			
			if ( objContext != null && objContext.trim().length() > 0 )
			{
				currentFormNo = Integer.parseInt( objContext );
			}
			switch(currentFormNo)
			{
				case 1 :
				{
					parentList = dom.getElementsByTagName( "Detail" + currentFormNo );
					childList = parentList.item( 0 ).getChildNodes();
					noOfChilds = childList.getLength();
					for (int ctr = 0; ctr < noOfChilds; ctr++)
					{	
						childNode = childList.item( ctr );
						childNodeName = childNode.getNodeName();
						if ( childNode != null && childNode.getFirstChild() != null )
						{
							columnValue = childNode.getFirstChild().getNodeValue();
						}
						System.out.println(" columnName [" + childNodeName + "] columnValue [" + columnValue + "]");
						if ( childNodeName.equalsIgnoreCase("shipment_date") )
						{
							columnValue = genericUtility.getColumnValue("shipment_date", dom);
							if(columnValue == null || columnValue.trim().length() == 0)
							{
								errList.add( "VTSHPDT" );
								errFields.add( childNodeName.toLowerCase() );
							}
						}
						/*else if ( childNodeName.equalsIgnoreCase("shipment_id") )
						{
							columnValue = genericUtility.getColumnValue("shipment_id", dom);
							if(columnValue == null || columnValue.trim().length() == 0)
							{
								errList.add( "VMSHIDNULL" );
								errFields.add( childNodeName.toLowerCase() );
							}
						}*/
						else if ( childNodeName.equalsIgnoreCase("site_code") )
						{
							columnValue = genericUtility.getColumnValue("site_code", dom);
							if(columnValue == null || columnValue.trim().length() == 0)
							{
								errList.add( "VMSITECD1" );
								errFields.add( childNodeName.toLowerCase() );
							}
							else
							{
								sql = "SELECT COUNT(*) FROM SITE WHERE SITE_CODE IN (SELECT SITE_CODE__SHIP FROM SORDER WHERE SITE_CODE__SHIP = ?)";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, columnValue);
								rs = pstmt.executeQuery();
								if( rs.next() )
								{
									cnt = rs.getInt(1);
									if(cnt == 0)
									{
										errList.add( "VMSITE" );
										errFields.add( childNodeName.toLowerCase() );		
									}
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
							}
						}
						/*
						else if ( childNodeName.equalsIgnoreCase("tran_code") )
						{
							columnValue = genericUtility.getColumnValue("tran_code", dom);
							if(columnValue != null && columnValue.trim().length() > 0)
							{
								sql = "SELECT COUNT(*) from transporter where tran_code = ? ";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, columnValue);
								rs = pstmt.executeQuery();
								if( rs.next() )
								{
									cnt = rs.getInt(1);
									if(cnt == 0)
									{
										errList.add( "VMTRAN1" );
										errFields.add( childNodeName.toLowerCase() );		
									}
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
							}
							else
							{
								errList.add( "VMTRANCD1" );
								errFields.add( childNodeName.toLowerCase() );
							}
						}
						*/
						else if ( childNodeName.equalsIgnoreCase("stan_code__from") )
						{
							columnValue = genericUtility.getColumnValue("stan_code__from", dom);
							if(columnValue != null && columnValue.trim().length() > 0)
							{
								sql = "SELECT COUNT(*) from station where stan_code = ? ";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, columnValue);
								rs = pstmt.executeQuery();
								if( rs.next() )
								{
									cnt = rs.getInt(1);
									if(cnt == 0)
									{
										errList.add( "VMSTANCD1" );
										errFields.add( childNodeName.toLowerCase() );		
									}
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
							}
						}
						
						else if ( childNodeName.equalsIgnoreCase("stan_code__to") )
						{
							columnValue = genericUtility.getColumnValue("stan_code__to", dom);
							if(columnValue != null && columnValue.trim().length() > 0)
							{
								sql = "SELECT COUNT(*) from station where stan_code = ? ";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, columnValue);
								rs = pstmt.executeQuery();
								if( rs.next() )
								{
									cnt = rs.getInt(1);
									if(cnt == 0)
									{
										errList.add( "VMSTANCD1" );
										errFields.add( childNodeName.toLowerCase() );		
									}
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
							}
						}
						
						else if ( childNodeName.equalsIgnoreCase("frt_list") )
						{
							columnValue = genericUtility.getColumnValue("frt_list", dom);
							if(columnValue != null && columnValue.trim().length() > 0)
							{
								sql = "SELECT COUNT(*) from freight_list where frt_list = ? ";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, columnValue);
								rs = pstmt.executeQuery();
								if( rs.next() )
								{
									cnt = rs.getInt(1);
									if(cnt == 0)
									{
										errList.add( "VTFRLEXT" );
										errFields.add( childNodeName.toLowerCase() );		
									}
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
							}
						}
						
						else if(childNodeName.equalsIgnoreCase("curr_code")) 
						{
							columnValue = genericUtility.getColumnValue("curr_code", dom);
							if(columnValue != null && columnValue.trim().length() > 0)
							{
								sql = "Select Count(*) from currency where curr_code = ? ";
								pstmt =  conn.prepareStatement(sql);
								pstmt.setString(1, columnValue);
								rs = pstmt.executeQuery();
								if(rs.next())
								{
									cnt = rs.getInt(1);
									if(cnt == 0)
									{
										errCode = "VMCUR1";
										errList.add(errCode);
										errFields.add(childNodeName.toLowerCase());
									}
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
							}
						}
						
						else if ( childNodeName.equalsIgnoreCase("load_type") )
						{
							columnValue = genericUtility.getColumnValue("load_type",dom);
							if(columnValue != null && columnValue.trim().length() > 0)
							{
								sql = "select count(*) from gencodes where fld_name = 'LOAD_TYPE' and MOD_NAME = 'X' and fld_value = ?" ;
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, columnValue);
								rs = pstmt.executeQuery();
								if( rs.next() )
								{
									cnt = rs.getInt(1);
									if(cnt == 0)
									{
										errCode = "VMLOADTY";
										errList.add(errCode);
										errFields.add(childNodeName.toLowerCase());
									}
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
							}
						}

						else if ( childNodeName.equalsIgnoreCase("licence_no") )
						{
							columnValue = genericUtility.getColumnValue("licence_no",dom);
							if(columnValue != null && columnValue.trim().length() > 0)
							{
								sql = "select count(*) from gencodes where fld_name = 'LICENCE_NO'  and fld_value = ?" ;
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, columnValue);
								rs = pstmt.executeQuery();
								if( rs.next() )
								{
									cnt = rs.getInt(1);
									if(cnt == 0)
									{
										errCode = "VTLICENCE";
										errList.add(errCode);
										errFields.add(childNodeName.toLowerCase());
									}
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
							}
						}

						else if ( childNodeName.equalsIgnoreCase("sale_order") )
						{
							columnValue = genericUtility.getColumnValue("sale_order",dom);
							if(columnValue != null && columnValue.trim().length() > 0)
							{
								sql = "select count(*) from SORDER WHERE SALE_ORDER = ?" ;
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, columnValue);
								rs = pstmt.executeQuery();
								if( rs.next() )
								{
									cnt = rs.getInt(1);
									if(cnt == 0)
									{
										errCode = "VTSORDCD1";
										errList.add(errCode);
										errFields.add(childNodeName.toLowerCase());
									}
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
							}
						}

						else if ( childNodeName.equalsIgnoreCase("dist_route") )
						{
							columnValue = genericUtility.getColumnValue("dist_route",dom);
							if(columnValue != null && columnValue.trim().length() > 0)
							{
								sql = "select count(*) from DISTROUTE WHERE DIST_ROUTE = ?" ;
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, columnValue);
								rs = pstmt.executeQuery();
								if( rs.next() )
								{
									cnt = rs.getInt(1);
									if(cnt == 0)
									{
										errCode = "VTDISTRT1";
										errList.add(errCode);
										errFields.add(childNodeName.toLowerCase());
									}
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
							}
						}
						
						else if ( childNodeName.equalsIgnoreCase("pro_no") )   //changes lr_no to pro_no as per manoharan  sir
						{
						/*	columnValue = checkNull(genericUtility.getColumnValue("tran_code", dom));
							columnValue1 = checkNull(genericUtility.getColumnValue("pro_no", dom));
							if(columnValue1.trim().length() > 0)
							{
								//columnValue1=columnValue1+"L";
								//pronoLastAmt = Integer.parseInt(columnValue1);
								pronoLastAmt = Long.parseLong(columnValue1);
								sql = "SELECT PRONO_FROM, PRONO_TO from transporter where tran_code = ? ";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, columnValue);
								rs = pstmt.executeQuery();
								if( rs.next() )
								{
									String pronoFrom = checkNull(rs.getString(1));
									String pronoTo = checkNull(rs.getString(2));
									if(pronoFrom.trim().length() > 0)
									{
										pronoFromAmt = Long.parseLong(pronoFrom);
									}
									if(pronoTo.trim().length() > 0)
									{
										pronoToAmt = Long.parseLong(pronoTo);
									}									
								}
								System.out.println("@@@@@ :: pronoLastAmt:["+pronoLastAmt+"]::pronoFromAmt:["+pronoFromAmt+"]::pronoToAmt:["+pronoToAmt+"]");
							        // comment by cpatil as per suggestion sir.	
								//if(pronoLastAmt < pronoFromAmt || pronoLastAmt > pronoToAmt)
								//{
								//	errList.add( "VMPRONOCD" );   //VMLRNOCD replace
								//	errFields.add( childNodeName.toLowerCase() );
								//}
								
							}
							else
							{
								errList.add( "VTPRONONUL" );  //VTLRNOCD replace
								errFields.add( childNodeName.toLowerCase() );
							}
						*/
						}
					}
				}	
				break;
				
      			case 2 :
				{
					parentList = dom.getElementsByTagName( "Detail" + currentFormNo );
					childList = parentList.item( 0 ).getChildNodes();
					noOfChilds = childList.getLength();
					for (int ctr = 0; ctr < noOfChilds; ctr++)
					{	
						childNode = childList.item( ctr );
						childNodeName = childNode.getNodeName();
						if ( childNode != null && childNode.getFirstChild() != null )
						{
							columnValue = childNode.getFirstChild().getNodeValue();
						}
						
						if ( childNodeName.equalsIgnoreCase("ptcn") )
						{
							columnValue = genericUtility.getColumnValue("ptcn", dom);
							tranCodeHdr = genericUtility.getColumnValue("tran_code", dom1);
							//Changed by sumit on 04/10/12 getting value of ref_ser  and do not validating ptcn other despatch.
							refSer = genericUtility.getColumnValue("ref_ser", dom);
							System.out.println(" ref ser -->["+refSer+"]");
							//Changed by sumit on 04/10/12 getting value of ref_ser  and do not validating ptcn other despatch.
							//if(columnValue != null && columnValue.trim().length() > 0 )
							if(columnValue != null && columnValue.trim().length() > 0 && "S-DSP".equalsIgnoreCase(refSer.trim()))
							{
								//added by shamim on 28/07/12
								ConsolidatToDoc consolidatToDoc= new ConsolidatToDoc();
								HashMap dataMap = consolidatToDoc.getAllTaskID(columnValue,conn);
								boolean isTasksConfirmed = consolidatToDoc.isAllTaskConfirmed(dataMap,conn);
								if(!isTasksConfirmed)
								{
									System.out.println(" All PTCN activity not completed");
									errList.add( "VTPTCNNC");
									errFields.add( childNodeName.toLowerCase() );		
									
								}

								sql = "SELECT COUNT(*) from wave_task_det where ptcn = ? ";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, columnValue);
								rs = pstmt.executeQuery();
								if( rs.next() )
								{
									cnt = rs.getInt(1);
									if(cnt == 0)
									{
										errList.add( "VTPTCNCD1");
										errFields.add( childNodeName.toLowerCase() );		
									}
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
								
								// added by cpatil
								sql = "SELECT D.TRAN_CODE  FROM WAVE_TASK_DET W, DESPATCH D WHERE REF_ID = D.DESP_ID AND D.CONFIRMED != 'Y' and  W.PTCN= ?  ";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, columnValue);
								rs = pstmt.executeQuery();
								if( rs.next() )
								{
									tranCode = rs.getString("TRAN_CODE");
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
								
								System.out.println("@@@@@ tranCodeHdr : ["+tranCodeHdr+"] ::::tran_code :["+tranCode+"]");
								if(tranCodeHdr != null && tranCodeHdr.trim().length() > 0 )
								{
									if(!(tranCodeHdr.equalsIgnoreCase(tranCode)))
									{
										errList.add( "VMTRANCDIF" );
										errFields.add( childNodeName.toLowerCase() );	
									}
								}
								// end
							}
					
						}
						
						else if ( childNodeName.equalsIgnoreCase("ref_id") )
						{
							//Changed by sumit on 22/09/12 getting value of ref_ser.
							refSer = genericUtility.getColumnValue("ref_ser", dom);
							System.out.println("  refSer  ----> ["+refSer+"]");
							columnValue = genericUtility.getColumnValue("ref_id", dom);
							columnValue1 = genericUtility.getColumnValue("line_no", dom);
							if(columnValue1 != null && columnValue1.trim().length() > 0)
							{
								lineNo = Integer.parseInt(columnValue1.trim());
							}
							System.out.println("Line no["+lineNo+"]");
							
							if(columnValue != null && columnValue.trim().length() > 0)
							{
								//Changed by sumit on 22/09/12 validation for all reference series start.
								//sql = "SELECT COUNT(*) from despatch where desp_id = ? and confirmed != 'Y' ";
								//S-DSP DESPATCH - DESP_ID
								//D-ISS DISTORD_ISS - TRAN_ID, DIST_ORDER
								//C-ISS CONSUME_ISS - CONS_ISSUE
								//P-ORD   PORDER    - PURC_ORDER
								if("S-DSP".equalsIgnoreCase(refSer.trim()))
								{									
									sql = "SELECT COUNT(*) FROM DESPATCH WHERE DESP_ID = ? AND CONFIRMED != 'Y' ";
								}
								else if ("D-ISS".equalsIgnoreCase(refSer.trim()))
								{									
									sql = "SELECT COUNT(*) FROM DISTORD_ISS WHERE TRAN_ID = ? AND CONFIRMED != 'Y' ";
								}
								else if ("C-ISS".equalsIgnoreCase(refSer.trim()))
								{									
									sql = "SELECT COUNT(*) FROM CONSUME_ISS WHERE CONS_ISSUE = ? AND CONFIRMED != 'Y' ";
								}
								else if ("P-ORD".equalsIgnoreCase(refSer.trim()))
								{									
									sql = "SELECT COUNT(*) FROM PORDER WHERE PURC_ORDER = ? AND CONFIRMED != 'Y' ";
								}
								//Changed by sumit on 22/09/12 validation for all reference series end.
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, columnValue);
								rs = pstmt.executeQuery();
								if( rs.next() )
								{
									cnt = rs.getInt(1);
									if(cnt == 0 )
									{
										errList.add( "VTDSPCON" );
										errFields.add( childNodeName.toLowerCase() );		
									}
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
								
								parentNodeList = dom2.getElementsByTagName("Detail2");
								parentNodeListLength = parentNodeList.getLength();
								System.out.println("PARENT NODE LENGTH [ "+parentNodeListLength+"]");
								if("S-DSP".equalsIgnoreCase(refSer.trim()))
								{
									//Changed by sumit on 04/10/12 validation in case of despatch 
									//for(ctr2 = 0; ctr2 < (parentNodeListLength -1) ; ctr2++ )
									for(ctr2 = 0; ctr2 < (parentNodeListLength -1); ctr2++ )
									{
										parentNode = parentNodeList.item(ctr2);
										//Changed by sumit on 04/10/12 getting value of ref_ser  and do not validating ptcn other despatch.
										columnValue1 = genericUtility.getColumnValueFromNode("ref_id", parentNode);
										System.out.println("Desp_id["+columnValue1+"]");
										sql = "SELECT COUNT(*) from despatch where desp_id = ? and cust_code__dlv = (select cust_code__dlv from despatch where desp_id = ?) ";
										pstmt = conn.prepareStatement(sql);
										pstmt.setString(1, columnValue);
										pstmt.setString(2, columnValue1);
										rs = pstmt.executeQuery();
										if( rs.next() )
										{
											cnt = rs.getInt(1);
										}
										rs.close();
										rs = null;
										pstmt.close();
										pstmt = null;
										
										if(cnt == 0)
										{
											errList.add( "VTDSPINV" );
											errFields.add( childNodeName.toLowerCase() );		
										}
									}
								}
								
							}
							else
							{
								errList.add( "REFSERCD" );
								errFields.add( childNodeName.toLowerCase() );	
							}
						}
						
						else if(childNodeName.equalsIgnoreCase("curr_code")) 
						{
							columnValue = genericUtility.getColumnValue("curr_code", dom);
							if(columnValue != null && columnValue.trim().length() > 0)
							{
								sql = "Select Count(*) from currency where curr_code = ? ";
								pstmt =  conn.prepareStatement(sql);
								pstmt.setString(1, columnValue);
								rs = pstmt.executeQuery();
								if(rs.next())
								{
									cnt = rs.getInt(1);
									if(cnt == 0)
									{
										errCode = "VMCUR1";
										errList.add(errCode);
										errFields.add(childNodeName.toLowerCase());
									}
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
							}
						}
						
						else if ( childNodeName.equalsIgnoreCase("stan_code__ship") )
						{
							columnValue = genericUtility.getColumnValue("stan_code__ship", dom);
							if(columnValue != null && columnValue.trim().length() > 0)
							{
								sql = "SELECT COUNT(*) from station where stan_code = ? ";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, columnValue);
								rs = pstmt.executeQuery();
								if( rs.next() )
								{
									cnt = rs.getInt(1);
									if(cnt == 0)
									{
										errList.add( "VMSTANCD1" );
										errFields.add( childNodeName.toLowerCase() );		
									}
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
							}
						}
						
						else if ( childNodeName.equalsIgnoreCase("stan_code__dlv") )
						{
							columnValue = genericUtility.getColumnValue("stan_code__dlv", dom);
							if(columnValue != null && columnValue.trim().length() > 0)
							{
								sql = "SELECT COUNT(*) from station where stan_code = ? ";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, columnValue);
								rs = pstmt.executeQuery();
								if( rs.next() )
								{
									cnt = rs.getInt(1);
									if(cnt == 0)
									{
										errList.add( "VMSTANCD1" );
										errFields.add( childNodeName.toLowerCase() );		
									}
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
							}
						}
						
						else if(childNodeName.equalsIgnoreCase("curr_code__frt")) 
						{
							columnValue = genericUtility.getColumnValue("curr_code__frt", dom);
							if(columnValue != null && columnValue.trim().length() > 0)
							{
								sql = "Select Count(*) from currency where curr_code = ? ";
								pstmt =  conn.prepareStatement(sql);
								pstmt.setString(1, columnValue);
								rs = pstmt.executeQuery();
								if(rs.next())
								{
									cnt = rs.getInt(1);
									if(cnt == 0)
									{
										errCode = "VMCUR1";
										errList.add(errCode);
										errFields.add(childNodeName.toLowerCase());
									}
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
							}
						}
						
						else if(childNodeName.equalsIgnoreCase("curr_code__bc")) 
						{
							columnValue = genericUtility.getColumnValue("curr_code__bc", dom);
							if(columnValue != null && columnValue.trim().length() > 0)
							{
								sql = "Select Count(*) from currency where curr_code = ? ";
								pstmt =  conn.prepareStatement(sql);
								pstmt.setString(1, columnValue);
								rs = pstmt.executeQuery();
								if(rs.next())
								{
									cnt = rs.getInt(1);
									if(cnt == 0)
									{
										errCode = "VMCUR1";
										errList.add(errCode);
										errFields.add(childNodeName.toLowerCase());
									}
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
							}
						}
						
						else if ( childNodeName.equalsIgnoreCase("tran_code") )
						{
							columnValue = genericUtility.getColumnValue("tran_code", dom);
									// genericUtility.getColumnValue("tran_code", dom);
							if(columnValue != null && columnValue.trim().length() > 0)
							{
								sql = "SELECT COUNT(*) from transporter where tran_code = ? ";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, columnValue);
								rs = pstmt.executeQuery();
								if( rs.next() )
								{
									cnt = rs.getInt(1);
									if(cnt == 0)
									{
										errList.add( "VMTRAN1" );
										errFields.add( childNodeName.toLowerCase() );		
									}
									
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
							}
							else
							{
								errList.add( "VMTRANCD1" );
								errFields.add( childNodeName.toLowerCase() );
							}
						}
					}
				}
			}
			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+errString.substring( errString.indexOf("</trace>") + 8,errString.indexOf("</Errors>"));
						errStringXml.append(bifurErrString);
						System.out.println("errStringXml .........."+errStringXml);
						errString = "";
					}
					if ( errorType.equalsIgnoreCase("E"))
					{
						break;
					}
				}
				errList.clear();
				errList = null;
				errFields.clear();
				errFields = null;
				
				errStringXml.append("</Errors></Root>\r\n");
			}
			else
			{
				errStringXml = new StringBuffer( "" );
			}
			errString = errStringXml.toString();
		}//END TRY
		catch(Exception e)
		{
			System.out.println("Exception in ShipmentIC  == >");
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if( conn != null && !conn.isClosed() )
				{
					if( rs != null )
					{
						rs.close();
						rs = null;
					}
					if( pstmt != null )
					{
						pstmt.close();
						pstmt = null;
					}
					conn.close();
				}
			}
			catch(Exception e)
			{
				System.out.println( "Exception :ShipmentIC: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;
	}

public String itemChanged() throws RemoteException, ITMException
{
	return "";
}
public String itemChanged(String xmlString, String xmlString1, String xmlString2, String objContext, String currentColumn, String editFlag, String xtraParams) throws RemoteException, ITMException
{
	Document dom = null;
	Document dom1 = null;
	Document dom2 = null;
	String errString = null;
	GenericUtility genericUtility = GenericUtility.getInstance();
	try
	{
		if (xmlString != null && xmlString.trim().length()!=0)
		{
			dom = genericUtility.parseString(xmlString); 
		}
		if (xmlString1 != null && xmlString1.trim().length()!=0)
		{
			dom1 = genericUtility.parseString(xmlString1); 
		}
		if (xmlString2 != null && xmlString2.trim().length()!=0)
		{
			dom2 = genericUtility.parseString(xmlString2); 
		}
		errString = itemChanged( dom, dom1, dom2, objContext, currentColumn, editFlag, xtraParams );
		System.out.println ( "ErrString :" + errString);
	}
	catch (Exception e)
	{
		System.out.println ( "Exception :ShipmentIC :itemChanged(String,String):" + e.getMessage() + ":" );
		errString = genericUtility.createErrorString(e);
		throw new ITMException(e);
	}
	System.out.println ( "returning from ShipmentIC itemChanged" );
	return errString;
}
public String itemChanged( Document dom, Document dom1, Document dom2, String objContext, String currentColumn, String editFlag, String xtraParams ) throws RemoteException,ITMException
{
	ResultSet rs = null;
	String sql = "";
	String columnValue = "";
	String columnValue1 = "";
	String siteCode = "";
	String childNodeName = "";
	String descr = "";
	String descr1 = "";
	String currCode = "";
	String loadType = "";
	String transitType = "";
	String freightType = "";
	String reStr = "";
	String frtTerm = "", prefix = "";
	int stdPickUp = 0;
	int pos = 0;
	int amt1 = 0;
	int amt2 = 0;
	int amt3 = 0;
	long pronoLast = 0;
	int stdTransitTime = 0;
	int minValue = 0;
	Date effFrom = null;
	Date validUpto = null;
	NodeList parentNodeList = null;
	NodeList childNodeList = null;
	Node parentNode = null;
	Node childNode = null;
	int childNodeListLength = 0;
	int count = 0;
	int ctr = 0;
	String tranCode="",tranName="", sProno = "";
	Connection conn = null;
	PreparedStatement pstmt = null;
	SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yy");
	StringBuffer valueXmlString = new StringBuffer();
	int currentFormNo = 0;
	String lrNo = "";
	
	GenericUtility genericUtility = GenericUtility.getInstance();
	try
	{
		siteCode = getValueFromXTRA_PARAMS(xtraParams, "loginSiteCode");
		columnValue = genericUtility.getColumnValue( currentColumn, dom );
		ConnDriver connDriver = null;
		connDriver = new ConnDriver();
		conn = connDriver.getConnectDB("DriverValidator");
		if( objContext != null && objContext.trim().length() > 0 )
		{
			currentFormNo = Integer.parseInt( objContext );
		}
		valueXmlString = new StringBuffer( "<?xml version=\"1.0\"?>\r\n<Root>\r\n<Header>\r\n<editFlag>" );
		valueXmlString.append( editFlag ).append( "</editFlag>\r\n</Header>\r\n" );

		switch ( currentFormNo )
		{
			case 1:
				parentNodeList = dom.getElementsByTagName("Detail1");
				parentNode = parentNodeList.item(0);
				childNodeList = parentNode.getChildNodes();
				valueXmlString.append( "<Detail1>\r\n" );
				childNodeListLength = childNodeList.getLength();
				do
				{   
					childNode = childNodeList.item(ctr);
					childNodeName = childNode.getNodeName();
					ctr ++;
				}while(ctr < childNodeListLength  && ! childNodeName.equals(currentColumn));
				
				if( currentColumn.trim().equalsIgnoreCase( "itm_default" ) )
				{
					valueXmlString.append( "<shipment_date><![CDATA[" ).append( genericUtility.getValidDateTimeString(new Date(new java.util.Date().getTime()), genericUtility.getApplDateFormat()) ).append( "]]></shipment_date>\r\n" );
					valueXmlString.append( "<lr_date><![CDATA[" ).append( genericUtility.getValidDateTimeString(new Date(new java.util.Date().getTime()), genericUtility.getApplDateFormat()) ).append( "]]></lr_date>\r\n" );
					valueXmlString.append( "<site_code><![CDATA[" ).append( siteCode ).append( "]]></site_code>\r\n" );
				}
								
				else if( currentColumn.trim().equalsIgnoreCase( "site_code" ) )
				{
					siteCode = genericUtility.getColumnValue("site_code",dom);
					sql = "SELECT descr,stan_code FROM SITE WHERE SITE_CODE = ? ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, siteCode);
					rs = pstmt.executeQuery();
					if( rs.next() )
					{
						descr = rs.getString(1);
						descr1 = rs.getString(2);
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					valueXmlString.append( "<site_descr><![CDATA[" ).append( checkNull( descr) ).append( "]]></site_descr>\r\n" );
					
					valueXmlString.append( "<stan_code__from><![CDATA[" ).append( checkNull( descr1) ).append( "]]></stan_code__from>\r\n" );
					
					setNodeValue( dom, "stan_code__from", descr1 );
					reStr = itemChanged(dom, dom1, dom2, objContext, "stan_code__from", editFlag, xtraParams);
					pos = reStr.indexOf("<Detail1>");
					reStr = reStr.substring(pos + 9);
					pos = reStr.indexOf("</Detail1>");
					reStr = reStr.substring(0,pos);
					valueXmlString.append(reStr);
					
				}
				
				else if( currentColumn.trim().equalsIgnoreCase( "tran_code" ) )
				{
					//cpatil
					columnValue = genericUtility.getColumnValue("tran_code",dom1);
					lrNo = genericUtility.getColumnValue("lr_no",dom1);
					// 02/08/12 prefix considered to generate SSCC18 complaint number
					if( columnValue != null && columnValue.trim().length() > 0)
					{
					sql = "SELECT tran_name, PRONO_LAST , PRONO_FROM, PREFIX FROM transporter WHERE tran_code = ? ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, columnValue);
					rs = pstmt.executeQuery();
					if( rs.next() )
					{
						descr = rs.getString(1);
						descr1 = rs.getString(2);
						prefix = rs.getString(4);
						if(descr1 == null || descr1.trim().length() == 0)
						{
							descr1 = checkNull(rs.getString(3));
						}
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
				
					if(descr1.trim().length() > 0)
					{
						pronoLast = Long.parseLong(descr1) + 1;
					}
					
					//Changed by sumit on 10/11/12 commented this as pro_no and lr_no will insert from shipment, this logic is now written in shipmentconf start.
					/*if(pronoLast != 0)
					{
						//valueXmlString.append( "<lr_no><![CDATA[" ).append( pronoLast ).append( "]]></lr_no>\r\n" ); // commented to generate SSCC18 complaint number
				
						//valueXmlString.append( "<lr_no><![CDATA[" ).append( getSSCC(prefix, pronoLast, conn) ).append( "]]></lr_no>\r\n" ); // added to generate SSCC18 complaint number //commented as per suggestion   
							sProno = ("" + pronoLast).trim();
							System.out.println("manohar before prefix["+prefix+"]::::sProno["+sProno+"]");
							sql = "SELECT fn_prono(?,to_char(?)) FROM dual";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, tranCode);
							pstmt.setString(2, sProno);
							rs = pstmt.executeQuery();
							if( rs.next() )
							{
								sProno = checkNull(rs.getString(1));
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;
						System.out.println("manohar after prefix["+prefix+"]::::sProno["+sProno+"]");
						//valueXmlString.append( "<pro_no><![CDATA[" ).append( getSSCC(prefix, pronoLast, conn) ).append( "]]></pro_no>\r\n" );
						//valueXmlString.append( "<lr_no><![CDATA[" ).append( getLrNo(prefix,pronoLast,xtraParams,conn) ).append( "]]></lr_no>\r\n" ); 
						valueXmlString.append( "<pro_no><![CDATA[" ).append( sProno ).append( "]]></pro_no>\r\n" );

						if (lrNo == null || lrNo.trim().length() ==0 )
						{
							sProno = getLrNo(xtraParams,conn);
							System.out.println("manohar before lr_no ["+sProno+"]");
							sql = "SELECT fn_prono(?,to_char(?)) FROM dual";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, tranCode);
							pstmt.setString(2, sProno);
							rs = pstmt.executeQuery();
							if( rs.next() )
							{
								sProno = checkNull(rs.getString(1));
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;
						
							System.out.println("manohar after lr_no ["+sProno+"]");
							
							valueXmlString.append( "<lr_no><![CDATA[" ).append( sProno  ).append( "]]></lr_no>\r\n" ); 
						}
					}*/
					//Changed by sumit on 10/11/12 commented this as pro_no and lr_no will insert from shipment, this logic is now written in shipmentconf end.
					valueXmlString.append( "<transporter_tran_name><![CDATA[" ).append( checkNull( descr) ).append( "]]></transporter_tran_name>\r\n" );
					//valueXmlString.append( "<lr_no><![CDATA[" ).append( getLrNo(prefix,pronoLast,xtraParams,conn) ).append( "]]></lr_no>\r\n" ); // added to generate SSCC18 complaint number //commented as per suggestion
				}
				
				}
				else if( currentColumn.trim().equalsIgnoreCase( "stan_code__from" ) )
				{
					columnValue = genericUtility.getColumnValue("stan_code__from",dom);
					columnValue1 = genericUtility.getColumnValue("stan_code__to",dom);
					sql = "select descr from station  WHERE stan_code = ? ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, columnValue);
					rs = pstmt.executeQuery();
					if( rs.next() )
					{
						descr = rs.getString(1);
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					if(columnValue != null && columnValue1 != null)
					{
						sql = "select distance from distance  WHERE stan_code__from = ? and stan_code__to = ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, columnValue1);
						pstmt.setString(2, columnValue);
						rs = pstmt.executeQuery();
						if( rs.next() )
						{
							count = rs.getInt(1);
							if(count == 0)
							{
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
								
								sql = "select distance from distance  WHERE stan_code__from = ? and stan_code__to = ? ";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, columnValue);
								pstmt.setString(2, columnValue1);
								rs = pstmt.executeQuery();
								if( rs.next() )
								{
									count = rs.getInt(1);								
								}
							}
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
					}
					valueXmlString.append( "<station_a_descr><![CDATA[" ).append( checkNull( descr) ).append( "]]></station_a_descr>\r\n" );
					valueXmlString.append( "<distance><![CDATA[" ).append(count).append( "]]></distance>\r\n" );
				}
				
				else if( currentColumn.trim().equalsIgnoreCase( "stan_code__to" ) )
				{
					columnValue = genericUtility.getColumnValue("stan_code__to",dom);
					columnValue1 = genericUtility.getColumnValue("stan_code__from",dom);
					sql = "select descr from station  WHERE stan_code = ? ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, columnValue);
					rs = pstmt.executeQuery();
					if( rs.next() )
					{
						descr = rs.getString(1);
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					if(columnValue != null && columnValue1 != null)
					{
						sql = "select distance from distance  WHERE stan_code__from = ? and stan_code__to = ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, columnValue1);
						pstmt.setString(2, columnValue);
						rs = pstmt.executeQuery();
						if( rs.next() )
						{
							count = rs.getInt(1);
							if(count == 0)
							{
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
								
								sql = "select distance from distance  WHERE stan_code__from = ? and stan_code__to = ? ";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, columnValue);
								pstmt.setString(2, columnValue1);
								rs = pstmt.executeQuery();
								if( rs.next() )
								{
									count = rs.getInt(1);								
								}
							}
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
					}
					valueXmlString.append( "<station_b_descr><![CDATA[" ).append( checkNull( descr) ).append( "]]></station_b_descr>\r\n" );
					valueXmlString.append( "<distance><![CDATA[" ).append(count).append( "]]></distance>\r\n" );
				}
				
				else if( currentColumn.trim().equalsIgnoreCase( "frt_list" ) )
				{
					columnValue = genericUtility.getColumnValue("frt_list",dom);
					sql = "SELECT DESCR, CURR_CODE, LOAD_TYPE, FRT_TERM, EFF_FROM, VALID_UPTO FROM  freight_list where frt_list = ? ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, columnValue);
					rs = pstmt.executeQuery();
					if( rs.next() )
					{
						descr = rs.getString(1);
						currCode = rs.getString(2);
						loadType = rs.getString(3);
						frtTerm = rs.getString(4);
						effFrom = rs.getDate(5);
						validUpto = rs.getDate(6);
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					
					sql = "SELECT std_pick_up, std_transit_time, transit_type, freight_type, min_value FROM  freight_rate where frt_list = ? ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, columnValue);
					rs = pstmt.executeQuery();
					if( rs.next() )
					{
						stdPickUp = rs.getInt(1);
						stdTransitTime = rs.getInt(2);
						transitType = rs.getString(3);
						freightType = rs.getString(4);
						minValue = rs.getInt(5);
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					
					valueXmlString.append( "<freight_list_descr><![CDATA[" ).append( checkNull( descr) ).append( "]]></freight_list_descr>\r\n" );

					if(genericUtility.getColumnValue("curr_code", dom) == null || genericUtility.getColumnValue("curr_code", dom).trim().length() == 0)
					{
						valueXmlString.append( "<curr_code><![CDATA[" ).append( checkNull( currCode) ).append( "]]></curr_code>\r\n" );
						setNodeValue( dom, "curr_code", currCode );
						reStr = itemChanged(dom, dom1, dom2, objContext, "curr_code", editFlag, xtraParams);
						pos = reStr.indexOf("<Detail1>");
						reStr = reStr.substring(pos + 9);
						pos = reStr.indexOf("</Detail1>");
						reStr = reStr.substring(0,pos);
						valueXmlString.append(reStr);
					}
					
					if(genericUtility.getColumnValue("load_type", dom) == null || genericUtility.getColumnValue("load_type", dom).trim().length() == 0)
					{
						valueXmlString.append( "<load_type><![CDATA[" ).append( checkNull( loadType) ).append( "]]></load_type>\r\n" );
						setNodeValue( dom, "load_type", loadType );
						reStr = itemChanged(dom, dom1, dom2, objContext, "load_type", editFlag, xtraParams);
						pos = reStr.indexOf("<Detail1>");
						reStr = reStr.substring(pos + 9);
						pos = reStr.indexOf("</Detail1>");
						reStr = reStr.substring(0,pos);
						valueXmlString.append(reStr);
					}
					
					valueXmlString.append( "<frt_term><![CDATA[" ).append( checkNull( frtTerm) ).append( "]]></frt_term>\r\n" );
					if(genericUtility.getColumnValue("call_date", dom) == null || genericUtility.getColumnValue("call_date", dom).trim().length() == 0)
					{
						valueXmlString.append( "<call_date><![CDATA[" ).append( sdf.format(effFrom) ).append( "]]></call_date>\r\n" );
					}
					if(genericUtility.getColumnValue("pick_up_date", dom) == null || genericUtility.getColumnValue("pick_up_date", dom).trim().length() == 0)
					{
						valueXmlString.append( "<pick_up_date><![CDATA[" ).append( sdf.format(validUpto) ).append( "]]></pick_up_date>\r\n" );
					}
					
					valueXmlString.append( "<std_pick_up><![CDATA[" ).append( stdPickUp ).append( "]]></std_pick_up>\r\n" );
					valueXmlString.append( "<std_transit_time><![CDATA[" ).append( stdTransitTime ).append( "]]></std_transit_time>\r\n" );
					valueXmlString.append( "<transit_type><![CDATA[" ).append( checkNull( transitType) ).append( "]]></transit_type>\r\n" );
					valueXmlString.append( "<freight_type><![CDATA[" ).append( checkNull( freightType) ).append( "]]></freight_type>\r\n" );
					valueXmlString.append( "<min_value><![CDATA[" ).append(  minValue ).append( "]]></min_value>\r\n" );
				}
				else if( currentColumn.trim().equalsIgnoreCase( "curr_code" ) )
				{
					columnValue = genericUtility.getColumnValue("curr_code",dom);
					sql = "select descr from currency where curr_code = ?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, columnValue);
					rs = pstmt.executeQuery();
					if( rs.next() )
					{
						descr = rs.getString(1);
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					valueXmlString.append( "<currency_descr><![CDATA[" ).append( checkNull( descr) ).append( "]]></currency_descr>\r\n" );
				}
				
				else if( currentColumn.trim().equalsIgnoreCase( "load_type" ) )
				{
					columnValue = genericUtility.getColumnValue("load_type",dom);
					if(columnValue != null && columnValue.trim().length() > 0)
					{
						sql = "select descr from gencodes where fld_name = 'LOAD_TYPE' and MOD_NAME = 'X' and fld_value = ?" ;
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, columnValue);
						rs = pstmt.executeQuery();
						if( rs.next() )
						{
							descr = rs.getString(1);
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
					}
					valueXmlString.append( "<gencodes_descr><![CDATA[" ).append( checkNull( descr) ).append( "]]></gencodes_descr>\r\n" );
				}
				
				else if( currentColumn.trim().equalsIgnoreCase( "gross_weight" ) )
				{
					columnValue = checkNull(genericUtility.getColumnValue("gross_weight",dom));
					columnValue1 = checkNull(genericUtility.getColumnValue("tare_weight",dom));
					if(columnValue.trim().length() > 0)
					{
						amt1 = Integer.parseInt(columnValue);
					}
					if(columnValue1.trim().length() > 0)
					{
						amt2 = Integer.parseInt(columnValue1);
					}
					amt3 = amt1 - amt2 ;
					valueXmlString.append( "<net_amt><![CDATA[" ).append( amt3 ).append( "]]></net_amt>\r\n" );
				}
				
				else if( currentColumn.trim().equalsIgnoreCase( "tare_weight" ) )
				{
					columnValue = checkNull(genericUtility.getColumnValue("gross_weight",dom));
					columnValue1 = checkNull(genericUtility.getColumnValue("tare_weight",dom));
					if(columnValue.trim().length() > 0)
					{
						amt1 = Integer.parseInt(columnValue);
					}
					if(columnValue1.trim().length() > 0)
					{
						amt2 = Integer.parseInt(columnValue1);
					}
					amt3 = amt1 - amt2 ;
					valueXmlString.append( "<net_amt><![CDATA[" ).append( amt3 ).append( "]]></net_amt>\r\n" );
				}
				
				else if( currentColumn.trim().equalsIgnoreCase( "freight_amt" ) )
				{
					columnValue = checkNull(genericUtility.getColumnValue("freight_amt",dom));
					columnValue1 = checkNull(genericUtility.getColumnValue("add_chgs",dom));
					if(columnValue.trim().length() > 0)
					{
						amt1 = Integer.parseInt(columnValue);
					}
					if(columnValue1.trim().length() > 0)
					{
						amt2 = Integer.parseInt(columnValue1);
					}
					amt3 = amt1 + amt2 ;
					valueXmlString.append( "<total_freight><![CDATA[" ).append( amt3 ).append( "]]></total_freight>\r\n" );
				}
				
				valueXmlString.append( "</Detail1>\r\n" );
				break;
			
			
			case 2:
			{
				parentNodeList = dom.getElementsByTagName("Detail2");
				parentNode = parentNodeList.item(0);
				childNodeList = parentNode.getChildNodes();
				valueXmlString.append("<Detail2>");
				childNodeListLength = childNodeList.getLength();
				do
				{   
					childNode = childNodeList.item(ctr);
					childNodeName = childNode.getNodeName();
					ctr ++;
				}while(ctr < childNodeListLength  && ! childNodeName.equals(currentColumn));

				if( currentColumn.trim().equalsIgnoreCase( "itm_default" ) )
				{
					
					columnValue = genericUtility.getColumnValue("shipment_id", dom1);
					columnValue1 = genericUtility.getColumnValue("stan_code__from", dom1);
					valueXmlString.append( "<shipment_id><![CDATA[" ).append( checkNull(columnValue) ).append( "]]></shipment_id>\r\n" );
					
					valueXmlString.append( "<stan_code__ship><![CDATA[" ).append( checkNull(columnValue1) ).append( "]]></stan_code__ship>\r\n" );
					setNodeValue( dom, "stan_code__ship", columnValue1 );
					/*
					reStr = itemChanged(dom, dom1, dom2, objContext, "stan_code__ship", editFlag, xtraParams);
					pos = reStr.indexOf("<Detail2>");
					reStr = reStr.substring(pos + 9);
					pos = reStr.indexOf("</Detail2>");
					reStr = reStr.substring(0,pos);
					valueXmlString.append(reStr);
					*/
					columnValue1 = genericUtility.getColumnValue("tran_code", dom1);
					
					valueXmlString.append( "<tran_code><![CDATA[" ).append( checkNull(columnValue1) ).append( "]]></tran_code>\r\n" );
					setNodeValue( dom, "tran_code", columnValue1 );
					//Changed by sumit on 06/10/12 for setting default value 'N'
					valueXmlString.append( "<desp_confirmed><![CDATA[" ).append( "N" ).append( "]]></desp_confirmed>\r\n" );
					/*
					reStr = itemChanged(dom, dom1, dom2, objContext, "tran_code", editFlag, xtraParams);
					pos = reStr.indexOf("<Detail2>");
					reStr = reStr.substring(pos + 9);
					pos = reStr.indexOf("</Detail2>");
					reStr = reStr.substring(0,pos);
					valueXmlString.append(reStr);
					 */
				}
				
				else if( currentColumn.trim().equalsIgnoreCase( "ptcn" ) )
				{
					columnValue = genericUtility.getColumnValue("ptcn",dom);
					columnValue1 = genericUtility.getColumnValue("ref_id",dom);
					if(columnValue != null && columnValue.trim().length() > 0 )
					{
						sql = "select REF_ID from wave_task_det where ptcn = ? AND REF_SER = ?";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, columnValue);
						pstmt.setString(2, "S-DSP");
						rs = pstmt.executeQuery();
						if( rs.next() )
						{
							valueXmlString.append( "<ref_id><![CDATA[" ).append( checkNull( rs.getString(1)) ).append( "]]></ref_id>\r\n" );
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						
						// added by cpatil on 8-sept-12 start 
						System.out.println("@@@@@ cpatil test");
						sql = "SELECT D.TRAN_CODE  FROM WAVE_TASK_DET W, DESPATCH D WHERE REF_ID = D.DESP_ID AND D.CONFIRMED != 'Y' and  W.PTCN= ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, columnValue);
						rs = pstmt.executeQuery();
						if( rs.next() )
						{
							tranCode = rs.getString("tran_code");
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						valueXmlString.append( "<tran_code><![CDATA[" ).append( checkNull(tranCode) ).append( "]]></tran_code>\r\n" );
						
						//
						sql = "SELECT tran_name, PRONO_LAST , PRONO_FROM, PREFIX,tran_name FROM transporter WHERE tran_code = ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, tranCode);
						rs = pstmt.executeQuery();
						if( rs.next() )
						{
							descr = rs.getString(1);
							descr1 = rs.getString(2);
							prefix = rs.getString(4);
							if(descr1 == null || descr1.trim().length() == 0)
							{
								descr1 = checkNull(rs.getString(3));
							}
							tranName=rs.getString(5);
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						if(descr1.trim().length() > 0)
						{
							pronoLast = Long.parseLong(descr1) + 1;
						}
						// 18/09/12 manoharan
						sql = "SELECT tran_name, PRONO_LAST , PRONO_FROM, PREFIX,tran_name FROM transporter WHERE tran_code = ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, tranCode);
						rs = pstmt.executeQuery();
						if( rs.next() )
						{
							descr = rs.getString(1);
							descr1 = rs.getString(2);
							prefix = rs.getString(4);
							if(descr1 == null || descr1.trim().length() == 0)
							{
								descr1 = checkNull(rs.getString(3));
							}
							tranName=rs.getString(5);
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						
						
						// start tran_code check for previous
						String 	tranCodeHdr = genericUtility.getColumnValue("tran_code", dom1);
						// end tran code check for previous
						System.out.println("Header tran_code:"+tranCodeHdr);
						if(descr1.trim().length() > 0)
						{
							pronoLast = Long.parseLong(descr1) + 1;
						}
						//Changed by sumit on 10/11/12 commented this as this logic is now written in shipmentconf start.
						/*if(pronoLast != 0)
						{
							// 18/09/12 manoharan get from database function
							System.out.println("@@@@@ prefix["+prefix+"]::::pronoLast["+pronoLast+"]");
							System.out.println("@@@@@ tranCodeHdr["+tranCodeHdr+"]::::tranCode["+tranCode+"]");
							
							sProno = ("" + pronoLast).trim();
							System.out.println("manohar before sprono ["+sProno+"]");
							sql = "SELECT fn_prono(?,to_char(?)) FROM dual";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, tranCode);
							pstmt.setString(2, sProno);
							rs = pstmt.executeQuery();
							if( rs.next() )
							{
								sProno = checkNull(rs.getString(1));
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;
							System.out.println("manohar after sprono ["+sProno+"]");
						}*/
						//Changed by sumit on 10/11/12 commented this as this logic is now written in shipmentconf start.
						if(tranCodeHdr != null && tranCodeHdr.trim().length() > 0 )
						{
							if( tranCodeHdr.equalsIgnoreCase(tranCode))
							{
								//valueXmlString.append( "<Detail1>\r\n" );
								//valueXmlString.append( "<tran_code><![CDATA[" ).append( checkNull(tranCode) ).append( "]]></tran_code>\r\n" );
								//valueXmlString.append( "<transporter_tran_name><![CDATA[" ).append( checkNull( tranName) ).append( "]]></transporter_tran_name>\r\n" );
								//valueXmlString.append( "<pro_no><![CDATA[" ).append( getSSCC(prefix, pronoLast, conn) ).append( "]]></pro_no>\r\n" );
								//valueXmlString.append( "<lr_no><![CDATA[" ).append( getLrNo(prefix,pronoLast,xtraParams,conn) ).append( "]]></lr_no>\r\n" );
								//valueXmlString.append( "</Detail1>\r\n" );
								//valueXmlString.append( "<Detail2>\r\n" );
								valueXmlString.append( "<tran_code><![CDATA[" ).append( checkNull(tranCode) ).append( "]]></tran_code>\r\n" );
								valueXmlString.append( "<transporter_tran_name><![CDATA[" ).append( checkNull( tranName) ).append( "]]></transporter_tran_name>\r\n" );
								//valueXmlString.append( "</Detail2>\r\n" );							
							}
							else
							{
								System.out.println("@@@@@ tran_code of detail is different from header tran_code");
								//valueXmlString.append( "</Detail2>\r\n" );
								//valueXmlString.append( "<Detail1>\r\n" );
								//valueXmlString.append( "<tran_code><![CDATA[" ).append("").append( "]]></tran_code>\r\n" );
								//valueXmlString.append( "<transporter_tran_name><![CDATA[" ).append("" ).append( "]]></transporter_tran_name>\r\n" );
								//valueXmlString.append( "<pro_no><![CDATA[" ).append("").append( "]]></pro_no>\r\n" );
								//valueXmlString.append( "<lr_no><![CDATA[" ).append("").append( "]]></lr_no>\r\n" );
								//valueXmlString.append( "</Detail1>\r\n" );
								//valueXmlString.append( "<Detail2>\r\n" );
								valueXmlString.append( "<tran_code><![CDATA[" ).append( checkNull(tranCode) ).append( "]]></tran_code>\r\n" );
								valueXmlString.append( "<transporter_tran_name><![CDATA[" ).append( checkNull( tranName) ).append( "]]></transporter_tran_name>\r\n" );
								//valueXmlString.append( "</Detail2>\r\n" );
							}
						}
						else
						{
							valueXmlString.append( "</Detail2>\r\n" );
							valueXmlString.append( "<Detail1>\r\n" );
							valueXmlString.append( "<tran_code><![CDATA[" ).append( checkNull(tranCode) ).append( "]]></tran_code>\r\n" );
							valueXmlString.append( "<transporter_tran_name><![CDATA[" ).append( checkNull( tranName) ).append( "]]></transporter_tran_name>\r\n" );
							//valueXmlString.append( "<pro_no><![CDATA[" ).append( getSSCC(prefix, pronoLast, conn) ).append( "]]></pro_no>\r\n" );							
							//valueXmlString.append( "<lr_no><![CDATA[" ).append( getLrNo(prefix,pronoLast,xtraParams,conn) ).append( "]]></lr_no>\r\n" );
							//Changed by sumit on 10/11/12 commented this as pro number will update from shipment confirmation.
							//valueXmlString.append( "<pro_no><![CDATA[" ).append(sProno).append( "]]></pro_no>\r\n" );							
							lrNo = genericUtility.getColumnValue("lr_no",dom1);
							//Changed by sumit on 10/11/12 this code copied to ShipmentConf, commented from here (as per manoharan sir) start
							/*if (lrNo == null || lrNo.trim().length() == 0)
							{
								sProno = getLrNo(xtraParams,conn);
								System.out.println("manohar before lr_no ["+sProno+"]");
								sql = "SELECT fn_prono(?,to_char(?)) FROM dual";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, tranCode);
								pstmt.setString(2, sProno);
								rs = pstmt.executeQuery();
								if( rs.next() )
								{
									sProno = checkNull(rs.getString(1));
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
								System.out.println("manohar after lr_no ["+sProno+"]");
								valueXmlString.append( "<lr_no><![CDATA[" ).append( sProno  ).append( "]]></lr_no>\r\n" ); 
							}*/
							//Changed by sumit on 10/11/12 this code copied to ShipmentConf, commented from here (as per manoharan sir) end
							//valueXmlString.append( "<lr_no><![CDATA[" ).append( getLrNo(xtraParams,conn) ).append( "]]></lr_no>\r\n" ); 
							valueXmlString.append( "</Detail1>\r\n" );
							valueXmlString.append( "<Detail2>\r\n" );
							valueXmlString.append( "<tran_code><![CDATA[" ).append( checkNull(tranCode) ).append( "]]></tran_code>\r\n" );
							valueXmlString.append( "<transporter_tran_name><![CDATA[" ).append( checkNull( tranName) ).append( "]]></transporter_tran_name>\r\n" );
							//valueXmlString.append( "</Detail2>\r\n" );
						}
						//}
						// added by cpatil on 8-sept-12 end
						//valueXmlString.append( "<Detail2>\r\n" );
					}
					valueXmlString.append( "<ref_ser><![CDATA[S-DSP]]></ref_ser>\r\n" );
					
				}
				
				else if( currentColumn.trim().equalsIgnoreCase( "curr_code" ) )
				{
					columnValue = genericUtility.getColumnValue("curr_code",dom);
					sql = "select descr from currency where curr_code = ?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, columnValue);
					rs = pstmt.executeQuery();
					if( rs.next() )
					{
						descr = rs.getString(1);
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					valueXmlString.append( "<currency_a_descr><![CDATA[" ).append( checkNull( descr) ).append( "]]></currency_a_descr>\r\n" );
				}
				
				else if( currentColumn.trim().equalsIgnoreCase( "stan_code__ship" ) )
				{
					columnValue = genericUtility.getColumnValue("stan_code__ship",dom);
					sql = "select descr from station  WHERE stan_code = ? ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, columnValue);
					rs = pstmt.executeQuery();
					if( rs.next() )
					{
						descr = rs.getString(1);
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					valueXmlString.append( "<station_a_descr><![CDATA[" ).append( checkNull( descr ) ).append( "]]></station_a_descr>\r\n" );
				}
				
				else if( currentColumn.trim().equalsIgnoreCase( "stan_code__dlv" ) )
				{
					columnValue = genericUtility.getColumnValue("stan_code__dlv",dom);
					sql = "select descr from station  WHERE stan_code = ? ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, columnValue);
					rs = pstmt.executeQuery();
					if( rs.next() )
					{
						descr = rs.getString(1);
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					valueXmlString.append( "<station_b_descr><![CDATA[" ).append( checkNull( descr ) ).append( "]]></station_b_descr>\r\n" );
				}
				
				else if( currentColumn.trim().equalsIgnoreCase( "curr_code__frt" ) )
				{
					columnValue = genericUtility.getColumnValue("curr_code__frt",dom);
					sql = "select descr from currency where curr_code = ?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, columnValue);
					rs = pstmt.executeQuery();
					if( rs.next() )
					{
						descr = rs.getString(1);
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					valueXmlString.append( "<currency_b_descr><![CDATA[" ).append( checkNull( descr) ).append( "]]></currency_b_descr>\r\n" );
				}
				
				else if( currentColumn.trim().equalsIgnoreCase( "curr_code__bc" ) )
				{
					columnValue = genericUtility.getColumnValue("curr_code__bc",dom);
					sql = "select descr from currency where curr_code = ?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, columnValue);
					rs = pstmt.executeQuery();
					if( rs.next() )
					{
						descr = rs.getString(1);
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					valueXmlString.append( "<currency_c_descr><![CDATA[" ).append( checkNull( descr) ).append( "]]></currency_c_descr>\r\n" );
				}
				
				else if( currentColumn.trim().equalsIgnoreCase( "tran_code" ) )
				{
					columnValue = genericUtility.getColumnValue("tran_code",dom);
					sql = "SELECT tran_name FROM transporter WHERE tran_code = ? ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, columnValue);
					rs = pstmt.executeQuery();
					if( rs.next() )
					{
						descr = rs.getString(1);
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					valueXmlString.append( "<transporter_tran_name><![CDATA[" ).append( checkNull( descr) ).append( "]]></transporter_tran_name>\r\n" );
					
				}

				
				else if( currentColumn.trim().equalsIgnoreCase( "gross_weight" ) )
				{
					System.out.println("inside the gross_weight detail2");
					columnValue = checkNull(genericUtility.getColumnValue("gross_weight",dom));
					columnValue1 = checkNull(genericUtility.getColumnValue("tare_weight",dom));
					if(columnValue.trim().length() > 0)
					{
						amt1 = Integer.parseInt(columnValue);
					}
					if(columnValue1.trim().length() > 0)
					{
						amt2 = Integer.parseInt(columnValue1);
					}
					amt3 = amt1 - amt2 ;
					System.out.println("amount1 "+amt1 + " amount2 "+amt2);
					valueXmlString.append( "<net_amt><![CDATA[" ).append( amt3 ).append( "]]></net_amt>\r\n" );
				}
				
				else if( currentColumn.trim().equalsIgnoreCase( "tare_weight" ) )
				{
					System.out.println("inside the tare_weight detail2");
					columnValue = checkNull(genericUtility.getColumnValue("gross_weight",dom));
					columnValue1 = checkNull(genericUtility.getColumnValue("tare_weight",dom));
					if(columnValue.trim().length() > 0)
					{
						amt1 = Integer.parseInt(columnValue);
					}
					if(columnValue1.trim().length() > 0)
					{
						amt2 = Integer.parseInt(columnValue1);
					}
					amt3 = amt1 - amt2 ;
					System.out.println("amount1 "+amt1 + " amount2 "+amt2);
					valueXmlString.append( "<net_amt><![CDATA[" ).append( amt3 ).append( "]]></net_amt>\r\n" );
				}
				valueXmlString.append( "</Detail2>\r\n" );
				System.out.println("------------------------COMPLETE DESCRIPTION-------------------");
				break;
			}
		}
	}
	catch(Exception e)
	{
		System.out.println( "Exception :Shipment :itemChanged(Document,String):" + e.getMessage() + ":" );
		valueXmlString = valueXmlString.append( genericUtility.createErrorString( e ) );
		throw new ITMException(e);
	}
	finally
	{
		try
		{
			if( conn != null && ! conn.isClosed() )
			{
				if( rs != null )
				{
					rs.close();
					rs = null;
				}
				if( pstmt != null )
				{
					pstmt.close();
					pstmt = null;
				}
				
				conn.close();
			}
			//System.out.println("------------------------INSIDE FINALLY-------------------");
		}
		catch(Exception e)
		{
			System.out.println( "Exception :Shipment:itemChanged :==>\n"+e.getMessage());
			throw new ITMException(e);
		}
	}
	valueXmlString.append( "</Root>\r\n" );	
	System.out.println( "\n****ValueXmlString :" + valueXmlString.toString() + ":********" );
	return valueXmlString.toString();
}
private static void setNodeValue( Document dom, String nodeName, String nodeVal ) throws Exception
{
	Node tempNode = dom.getElementsByTagName( nodeName ).item(0);

	if( tempNode != null )
	{
		if( tempNode.getFirstChild() == null )
		{
			CDATASection cDataSection = dom.createCDATASection( nodeVal );
			tempNode.appendChild( cDataSection );
		}
		else
		{
			tempNode.getFirstChild().setNodeValue(nodeVal);
		}
	}
	tempNode = null;
}	
private String checkNull( String input )
{
	if ( input == null )
	{
		input = "";
	}
	return input;
}	
	// 02/08/12 manoharan following method added to generate SSCC18 complaint number
	private String getSSCC(String mfgCode, long runNumber, Connection conn) throws Exception 
	{
		boolean isOdd = false;
		long sumOdd = 0, sumEven = 0;
		String inputStr = "", sscc18 = "",  runNumberStr =""; //mfgCode = "0351672",
		//ibase.utility.GenericUtility genUtility = ibase.utility.GenericUtility.getInstance();
		long  chkDigit = 0,curDigit = 0;
		int ctr=0,  padLen = 0; // runNumber = 4967235
		try
		{
			if (mfgCode == null) 
			{
				mfgCode = "";
			}
			sumOdd = 0;
			sumEven = 0;
			padLen = 16 - mfgCode.length(); // total length should be 16 excluding extension digit and check digit
			System.out.println("@@@@@ mfgCode.length()["+mfgCode.length()+"]");
			System.out.println("@@@@@ padLen["+padLen+"]");
			runNumberStr = String.valueOf(runNumber); //"0000000000000000" + 
			runNumberStr = runNumberStr.substring(runNumberStr.length() - padLen ,runNumberStr.length()); 
			inputStr = mfgCode + runNumberStr;
			for (int pos = inputStr.length() - 1; pos >= 0 ; pos--)
			{
				curDigit = Long.parseLong(inputStr.substring(pos,pos+1));
				if  ( (pos % 2) == 0)
				{
					sumEven +=  curDigit;
				}
				else
				{
					sumOdd += curDigit;
				}
				
			}
			// step 1  - multiple odd sum with 3
			sumOdd = sumOdd * 3;
			chkDigit =  10 - ((sumEven + sumOdd) % 10) ;
			//System.out.println(  " Integer(chkDigit).toString() [" + String.valueOf(chkDigit) +"]" );
			//sscc18 = 
			sscc18 = inputStr + String.valueOf(chkDigit);
			System.out.println(" chkDigit [" + chkDigit + "] sumOdd [" + sumOdd + "] sumEven [" + sumEven + "] inputStr [" + inputStr + "] sscc18 [" + sscc18 +"]" );
			
				
		}
		catch(Exception e)
		{
			throw new ITMException(e);
		}
		return sscc18;
	}
	
	private String getLrNo( String xtraParams,Connection conn) throws Exception
	{

		boolean isOdd = false;
		int sumOdd = 0, sumEven = 0;
		String inputStr = "", lrNo = "",  runNumberStr =""; //mfgCode = "0351672",
		//ibase.utility.GenericUtility genUtility = ibase.utility.GenericUtility.getInstance();
		int ctr, curDigit = 0, chkDigit = 0, padLen = 0; // runNumber = 4967235
		long runNumber=0;
		//TransIDGenerator tg = new TransIDGenerator(xmlValues.toString(), "BASE",CommonConstants.DB_NAME);
		String windowName="";
		PreparedStatement pSel = null;
		PreparedStatement pIns = null;	
		ResultSet rsSel = null;
	//	Connection conn = null;
		String mfgCode = null;
		String sSel = "";
		String sIns = "";
		String siteCode = "";
		String labelType = "";
		String labelID = "";
		String keyString = "";
		String tranIDCol = "";
		String refSer = "";
		String retString = "";
		GenericUtility genericUtility = GenericUtility.getInstance();
		
		try
		{
			if (mfgCode == null) 
			{
				mfgCode = "04";
			}
			System.out.println("@@@@@ getLrNo called..........");
			windowName = "w_bol_gen";
			sSel = "SELECT KEY_STRING, TRAN_ID_COL,REF_SER FROM TRANSETUP WHERE TRAN_WINDOW = ? ";
			pSel = conn.prepareStatement(sSel);
			pSel.setString(1, windowName);
			rsSel = pSel.executeQuery();
			if( rsSel.next() )
			{
				keyString = rsSel.getString( "KEY_STRING" );
				tranIDCol = rsSel.getString("TRAN_ID_COL");
				refSer = rsSel.getString("REF_SER");
			}
			//refSer="BOL-NO";
			String loginCode = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginCode");
			siteCode = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"site_code");
			String chgTerm = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"chgTerm");
			java.util.Date date = null;
			Timestamp timestamp = new Timestamp(System.currentTimeMillis());
			System.out.println(genericUtility.getDBDateFormat());

			SimpleDateFormat simpledateformat = new SimpleDateFormat(genericUtility.getDBDateFormat());
			date = simpledateformat.parse(timestamp.toString());
			timestamp = Timestamp.valueOf(simpledateformat.format(date).toString() + " 00:00:00.0");
			String currDate = (new SimpleDateFormat(genericUtility.getApplDateFormat())).format(timestamp).toString();
			StringBuffer xmlValues = new StringBuffer("<?xml version=\"1.0\" encoding=\"utf-8\"?><Root>");
			xmlValues = xmlValues.append("<Header></Header>");
			xmlValues = xmlValues.append("<Detail1>");
			xmlValues.append( "<dummy>").append("<![CDATA[]]>").append("</dummy>\r\n" );
			xmlValues.append( "<site_code>").append("<![CDATA[" + siteCode + "]]>").append("</site_code>\r\n" );
			xmlValues.append( "<tran_date>").append("<![CDATA[" + currDate + "]]>").append("</tran_date>\r\n");
			xmlValues.append( "<loginCode><![CDATA[" ).append(loginCode).append( "]]></loginCode>\r\n" );
			xmlValues.append( "<lr_no></lr_no>\r\n");
			xmlValues.append( "<bol_no></bol_no>\r\n");
			xmlValues = xmlValues.append( "</Detail1></Root>");
			
			System.out.println("refSer:["+refSer+"]:::tranIDCol:["+tranIDCol+"]::::keyString:["+keyString+"]");
			TransIDGenerator tg = new TransIDGenerator(xmlValues.toString(), "BASE",CommonConstants.DB_NAME);
			
			runNumber = Long.parseLong(tg.generateTranSeqID(refSer, tranIDCol, keyString, conn));
			conn.commit();
			System.out.println("runNumber:["+runNumber+"]");
	
			padLen = 16 - mfgCode.length(); // total length should be 16 excluding extension digit and check digit
			
			runNumberStr = "0000000000000000" + String.valueOf(runNumber);
			runNumberStr = runNumberStr.substring(runNumberStr.length() - padLen ,runNumberStr.length()); 
			lrNo = mfgCode + runNumberStr;
			System.out.println(  " lrNo [" + lrNo + "] lrNo.length() [" +  lrNo.length() + "]" );
		}
		catch(Exception e)
		{
			throw new ITMException(e);
		}
		return lrNo;
	
	}
	
 
}