/********************************************************
	Title : InvoiceAmdPrs
	Date  : 16/03/2012
	Author: Mahesh Patidar
 ********************************************************/
package ibase.webitm.ejb.fin;
import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.E12CreateBatchLoadEjb;
import ibase.webitm.ejb.ProcessEJB;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;
import java.io.FileNotFoundException;
import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;

import ibase.webitm.ejb.ValidatorEJB;
import ibase.webitm.ejb.sys.CreateRCPXML;

import java.sql.Date;
import org.apache.axis.client.Service;
import javax.ejb.Stateless;
import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.apache.axis.client.Call;
import org.apache.axis.encoding.XMLType;
import javax.xml.rpc.ParameterMode;

@Stateless 
public class InvoiceAmdPrs extends ProcessEJB  implements InvoiceAmdPrsLocal ,InvoiceAmdPrsRemote
{
	GenericUtility genericUtility = GenericUtility.getInstance();
	ValidatorEJB vejb = new ValidatorEJB();

	public String getData(String xmlString, String xmlString2, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		System.out.println(":: In getData() of InvoiceAmdPrs EJB");
		String rtrStr = "";
		Document headerDom = null;
		Document detailDom = null;
		try
		{
			if(xmlString != null && xmlString.trim().length()!=0 )
			{
				headerDom = genericUtility.parseString(xmlString);
			}
			if(xmlString2 != null && xmlString2.trim().length()!=0)
			{
				detailDom = genericUtility.parseString(xmlString2);
			}
			rtrStr = getData(headerDom, detailDom, windowName, xtraParams);
		}
		catch (Exception e)
		{
			System.out.println("Exception @ InvoiceAmdPrs-getData(xmlString, xmlString2, windowName, xtraParams)::" + e.getMessage());
			throw new ITMException(e);
		}
		return rtrStr;
	}

	public String getData(Document headerDom, Document detailDom, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		StringBuffer retTabSepStrBuff = new StringBuffer();
		String custCode = "";
		String invoiceId = "";
		String lrNo = "";
		String octroiRcpNo = "";
		String remarks = "";
		String lorryNo = "";
		String gpNo = "";
		String transMode = "";
		String xlsName = "";
		String sql = "";
		String sql1 = "";
		String currCodeFrt = "";
		String userId = "";
		String errString = "";
		String errCode = "";
		String errFldName = "";
		String errorType = "";
		String currCodeIns = "";
		String childNodeName = "";
		String retString = "";
		int insAmt = 0;
		int noArt = 0;
		int frtAmt = 0;
		int grossWeight = 0;
		long cnt = 0;
		Date fromDate  = null;
		Date toDate = null;
		Date effDate = null;
		Date lrDate = null;
		Date gpDate = null;
		Date octroiRcpDate = null;
		ArrayList<String> errList = new ArrayList<String>();
		ArrayList<String> errFields = new ArrayList<String>();
		StringBuffer errStringXml = new StringBuffer("<?xml version = \"1.0\"?> \r\n <Root> <Errors>"); 
		SimpleDateFormat format1 = new SimpleDateFormat("dd/MM/yy");
		SimpleDateFormat format2 = new SimpleDateFormat("dd/MMM/yyyy");
		Connection conn = null;
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		ResultSet rs1 = null;
		PreparedStatement pstmt1 = null;
		ConnDriver connDriver = new ConnDriver();

		try
		{
			conn = connDriver.getConnectDB("DriverITM");	
			xlsName = genericUtility.getColumnValue("xls_name",headerDom);
			// To fetch the data from xls file if xls file name is entered
			if(xlsName != null && xlsName.trim().length() != 0)
			{
			}//if
			else
			{
				fromDate = new java.sql.Date(format2.parse(format2.format(format1.parse(genericUtility.getColumnValue("from_date", headerDom)))).getTime());
				System.out.println("FromDate.................. :"+ fromDate);
	
				toDate = new java.sql.Date(format2.parse(format2.format(format1.parse(genericUtility.getColumnValue("to_date",headerDom)))).getTime());
				System.out.println("ToDate................. :"+ toDate);
	
				custCode = genericUtility.getColumnValue("cust_code",headerDom);
				System.out.println("custCode............................:"+custCode);
				
				if(custCode !=null && custCode.trim().length() > 0)
				{
					System.out.println("BEFORE THE sql QUERY");
					sql1 = "SELECT count(*) FROM CUSTOMER WHERE CUST_CODE = ? ";
					pstmt1 = conn.prepareStatement(sql1);
					System.out.println(sql1);
					pstmt1.setString(1, custCode);
					System.out.println("BEFORE execute");
					rs1 = pstmt1.executeQuery();
					System.out.println("after execute");
					while(rs1.next())
					{
						int count = rs1.getInt(1);
						if(count > 0)
						{
							if(fromDate.before(toDate))
							{
								// Query To get the Data from Process Window
								System.out.println("BEFORE THE sql QUERY");
								sql = "SELECT INVOICE_ID,  EFF_DATE,  LR_NO, LR_DATE, OCTROI_RCP_NO, OCTROI_RCP_DATE, REMARKS, NO_ART, LORRY_NO, GP_NO, GP_DATE,CURR_CODE__FRT, FRT_AMT, CURR_CODE__INS, INS_AMT, TRANS_MODE, GROSS_WEIGHT FROM INVOICE WHERE TRAN_DATE BETWEEN ? AND ? AND CUST_CODE = ? AND CONFIRMED = 'Y' ";
								pstmt = conn.prepareStatement(sql);
								System.out.println(sql);
								pstmt.setDate(1, fromDate);
								pstmt.setDate(2, toDate);
								pstmt.setString(3, custCode);
								System.out.println("BEFORE execute");
								rs = pstmt.executeQuery();
								System.out.println("after execute");
								while (rs.next())
								{
									System.out.println(sql);
									invoiceId = rs.getString(1);
									effDate = rs.getDate(2);
									lrNo = rs.getString(3);
									lrDate = rs.getDate(4);
									octroiRcpNo = rs.getString(5);
									octroiRcpDate = rs.getDate(6);
									remarks = rs.getString(7);
									noArt = rs.getInt(8);
									lorryNo = rs.getString(9);
									gpNo = rs.getString(10);
									gpDate = rs.getDate(11);
									currCodeFrt = rs.getString(12);
									frtAmt = rs.getInt(13);
									currCodeIns = rs.getString(14);
									insAmt = rs.getInt(15);
									transMode = rs.getString(16);
									grossWeight = rs.getInt(17);
									
									retTabSepStrBuff.append(invoiceId).append("\t");
									retTabSepStrBuff.append("S").append("\t");
									if(effDate != null)
									{
										effDate = new java.sql.Date(format1.parse(format1.format(effDate)).getTime());
										retTabSepStrBuff.append(effDate).append("\t");
									}
									else
									{
										retTabSepStrBuff.append("").append("\t");
									}
									if(lrNo != null)
									{
										retTabSepStrBuff.append(lrNo).append("\t");
									}
									else
									{
										retTabSepStrBuff.append("").append("\t");
									}
									if(lrDate != null)
									{
										lrDate = new java.sql.Date(format1.parse(format1.format(lrDate)).getTime());
										retTabSepStrBuff.append(lrDate).append("\t");
									}
									else
									{
										retTabSepStrBuff.append("").append("\t");
									}
									if(octroiRcpNo != null)
									{
										retTabSepStrBuff.append(octroiRcpNo).append("\t");
									}
									else
									{
										retTabSepStrBuff.append("").append("\t");
									}
									if(octroiRcpDate != null)
									{
										octroiRcpDate = new java.sql.Date(format1.parse(format1.format(octroiRcpDate)).getTime());
										retTabSepStrBuff.append(octroiRcpDate).append("\t");
									}
									else
									{
										retTabSepStrBuff.append("").append("\t");
									}
									if(remarks != null)
									{
										retTabSepStrBuff.append(remarks).append("\t");
									}
									else
									{
										retTabSepStrBuff.append("").append("\t");
									}
									retTabSepStrBuff.append(noArt).append("\t");
									if(lorryNo != null)
									{
										retTabSepStrBuff.append(lorryNo).append("\t");
									}
									else
									{
										retTabSepStrBuff.append("").append("\t");
									}
									if(gpNo != null)
									{
										retTabSepStrBuff.append(gpNo).append("\t");
									}
									else
									{
										retTabSepStrBuff.append("").append("\t");
									}
									if(gpDate != null)
									{
										gpDate = new java.sql.Date(format1.parse(format1.format(gpDate)).getTime());
										retTabSepStrBuff.append(gpDate).append("\t");
									}
									else
									{
										retTabSepStrBuff.append("").append("\t");
									}
									if(currCodeFrt != null)
									{
										retTabSepStrBuff.append(currCodeFrt).append("\t");
									}
									else
									{
										retTabSepStrBuff.append("").append("\t");
									}
									retTabSepStrBuff.append(frtAmt).append("\t");
									if(currCodeIns != null)
									{
										retTabSepStrBuff.append(currCodeIns).append("\t");
									}
									else
									{
										retTabSepStrBuff.append("").append("\t");
									}
									retTabSepStrBuff.append(insAmt).append("\t");
									if(transMode != null)
									{
										retTabSepStrBuff.append(transMode).append("\t");
									}
									else
									{
										retTabSepStrBuff.append("").append("\t");
									}
									retTabSepStrBuff.append(grossWeight).append("\t");
									retTabSepStrBuff.append("\n"); // next line
									System.out.println("next line");
								} //while
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
					
								retString = retTabSepStrBuff.toString();
								System.out.println("Return String ["+ retString +"]");
								return retString;
							}
							else
							{
								errCode = "INVAMDDT0";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}
						else
						{
							errCode = "INVAMCD0";
							errList.add(errCode);
							errFields.add(childNodeName.toLowerCase());
						}
					}
					rs1.close();
					rs1 = null;
					pstmt1.close();
					pstmt1 = null;
				}
				else
				{
					errCode = "INVAMCD1";
					errList.add(errCode);
					errFields.add(childNodeName.toLowerCase());
				}
				int errListSize = errList.size();
				cnt = 0;
				if(errList != null && errListSize > 0)
				{
					for(cnt = 0; cnt < errListSize; cnt ++)
					{
						errCode = errList.get((int) cnt);
						System.out.println("errCode .........." + errCode);
						errString = vejb.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);
							errString = "";
						}
						if(errorType.equalsIgnoreCase("E"))
						{
							break;
						}
					}
					errList.clear();
					errList = null;
					errFields.clear();
					errFields = null;
					errStringXml.append("</Errors> </Root> \r\n");
					errString = errStringXml.toString();
				}
				System.out.println("@@ resultString is : " + errString);
			}
		} //try
		catch(FileNotFoundException fne)
		{
			System.out.println("!!! File Not Found Exception : From InvoiceAmdPrs.java " + fne.getMessage());
			throw new ITMException(fne);
		}
		catch (SQLException se)
		{
			System.out.println("!!! SQLException : From InvoiceAmdPrs.java :" + se.getMessage() + ":");
			throw new ITMException(se);
		}
		catch (Exception e)
		{
			System.out.println("!!! Exception : From InvoiceAmdPrs.java :" + e.getMessage() + ":");
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				System.out.println("!! connection closing at getData() !!");
				conn.close();
				conn = null;
			}
			catch(Exception e)
			{
				e.getMessage();
			}
		}
		return errString;
	}

	public String process(String xmlString, String xmlString2, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		String retStr = "";
		Document detailDom = null;
		Document headerDom = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		try
		{
			if(xmlString != null && xmlString.trim().length()!=0)
			{
				headerDom = genericUtility.parseString(xmlString);
				System.out.println("headerDom " + headerDom);
			}
			if(xmlString2 != null && xmlString2.trim().length()!=0)
			{
				detailDom = genericUtility.parseString(xmlString2);
				System.out.println("detailDom " + detailDom);
			}
			System.out.println("check      <"+xmlString+">");
			System.out.println("check      <"+xmlString2+">");
			retStr = process(headerDom, detailDom, windowName, xtraParams);
		}
		catch (Exception e)
		{
			System.out.println("Exception : InvoiceAmdPrs.java :process(String xmlString, String xmlString2, String windowName, String xtraParams):" + e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		return retStr;
	}

	public String process(Document headerDom, Document detailDom, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		String invoiceId = "";
		String tranCode = "";
		String lrNo = "";
		String octroiRcpNo = "";
		String remarks = "";
		String lorryNo = "";
		String gpNo = "";
		String stanCodeInit = "";
		String transMode = "";
		String empCodeAprv = "";
		String despId = "";
		String currCodeFrt = "";
		String currCodeIns = "";
		String status = "";
		String frtType = "";
		String userId = "";
		String errString = "";
		String errCode = "";
		String sql = "";
		String sql1 = "";
		String sql2 = "";
		String sql3 = "";
		String errFldName = "";
		String errorType = "";
		String lrNo1 = "";
		String octroiRcpNo1 = "";
		String remarks1 = "";
		String lorryNo1 = "";
		String gpNo1 = "";
		String transMode1 = "";
		String currCodeFrt1 = "";
		String currCodeIns1 = "";
		String childNodeName = "";
		String colunm = "";
		String refNo = "";
		String saleOrder = "";
		String grNo = "";
		String sbNo = "";
		String confirm = "";
		String shipmentId = "";
		Date sbDate = null;
		Date sailDate = null;
		Date grDate = null;
		Date refDate = null;
		Date effDate1 = null;
		Date lrDate1 = null;
		Date gpDate1 = null;
		Date octroiRcpDate1 = null;
		Date effDate = null;
		Date lrDate = null;
		Date gpDate = null;
		Date octroiRcpDate = null;
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;
		boolean isAmended = false;
		boolean isChanged = false; 
		int childNodeListLength = 0;
		int parentNodeListLength = 0;
		int ctr = 0;
		int ctr2 = 0;
		int noArt = 0;
		int insAmt = 0;
		int exchRateFrt = 0;
		int exchRateIns = 0;
		int frtAmt = 0;
		int grossWeight = 0;
		int insAmt1 = 0;
		int noArt1 = 0;
		int frtAmt1 = 0;
		int grossWeight1 = 0;
		long cnt = 0;
		ArrayList<String> errList = new ArrayList<String>();
		ArrayList<String> errFields = new ArrayList<String>();
		StringBuffer errStringXml = new StringBuffer("<?xml version = \"1.0\"?> \r\n <Root> <Errors>");
		ConnDriver connDriver = new ConnDriver();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		PreparedStatement pstmt1 = null;
		ResultSet rs2 = null;
		PreparedStatement pstmt3 = null;
		ResultSet rs3 = null;
		PreparedStatement pstmt2 = null;
		ResultSet rs1 = null;
		try
		{
			conn = connDriver.getConnectDB( "DriverITM" );
			connDriver = null;
			parentNodeList = detailDom.getElementsByTagName("Detail2");
			parentNodeListLength = parentNodeList.getLength();
			for(ctr2 = 0; ctr2 < parentNodeListLength ; ctr2++ )
			{
				parentNode = parentNodeList.item(ctr2);
				System.out.println("parent node = " + parentNode.getNodeName());
				childNodeList = parentNode.getChildNodes();
				childNodeListLength = childNodeList.getLength();
				for( ctr = 0; ctr < childNodeListLength; ctr++ )
				{
					childNode = childNodeList.item( ctr );
					childNodeName = childNode.getNodeName();
					if( childNodeName.equals("invoice_id"))
					{
						invoiceId = genericUtility.getColumnValueFromNode("invoice_id", parentNode );
						System.out.println("invoiceId "+invoiceId);
						status = genericUtility.getColumnValueFromNode( "inv_status", parentNode );
						isAmended = isAlreadyAmended( invoiceId, status, conn );
						System.out.println("isAvailable [ "+isAmended+" ]");
						if(! isAmended)
						{
							sql = "select (case when a.confirmed is null then 'N' else a.confirmed end) as confirm from receipt a, rcpdet b " 
										+" where a.tran_id =  b.tran_id and b.ref_ser = 'S-INV' and b.ref_no = ? ";
							pstmt = conn.prepareStatement(sql);
							System.out.println(sql);
							pstmt.setString(1,invoiceId);
							rs = pstmt.executeQuery();
							while (rs.next())
							{
								confirm = rs.getString(1);
								if(confirm.equals("Y"))
								{
									errCode = "VTRCPCON";
									errList.add(errCode);
									errFields.add(childNodeName.toLowerCase());	
									break;
								}
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;
						}
						else
						{
							errCode = "INVAMDAVL0";
							errList.add(errCode);
							errFields.add(childNodeName.toLowerCase());			
							break;
						}
						sql = "SELECT DESP_ID, TRAN_CODE, EXCH_RATE__FRT, EXCH_RATE__INS, STAN_CODE__INIT,"
							+" FRT_TYPE, EMP_CODE__APRV, EFF_DATE,  LR_NO, LR_DATE, OCTROI_RCP_NO,"
							+" OCTROI_RCP_DATE, REMARKS, NO_ART, LORRY_NO, GP_NO, GP_DATE,"
							+" CURR_CODE__FRT, FRT_AMT, CURR_CODE__INS, INS_AMT, TRANS_MODE, " 
									+" GROSS_WEIGHT, SALE_ORDER  FROM INVOICE WHERE INVOICE_ID = ? ";
						pstmt = conn.prepareStatement(sql);
						System.out.println(sql);
						pstmt.setString(1,invoiceId);
						rs = pstmt.executeQuery();
						while (rs.next())
						{
							System.out.println(sql);
							despId = rs.getString(1);
							tranCode = rs.getString(2);
							exchRateFrt = rs.getInt(3);
							exchRateIns = rs.getInt(4);
							stanCodeInit = rs.getString(5);
							frtType = rs.getString(6);
							empCodeAprv = rs.getString(7);
							effDate1 = getDateValue(rs.getDate(8));
							lrNo1 = rs.getString(9);
							lrDate1 = getDateValue(rs.getDate(10));
							octroiRcpNo1 = rs.getString(11);
							octroiRcpDate1 = getDateValue(rs.getDate(12));
							remarks1 = rs.getString(13);
							noArt1 = rs.getInt(14);
							lorryNo1 = rs.getString(15);
							gpNo1  = rs.getString(16);
							gpDate1 =getDateValue(rs.getDate(17));
							currCodeFrt1 = rs.getString(18);
							frtAmt1 = rs.getInt(19);
							currCodeIns1 = rs.getString(20);
							insAmt1 = rs.getInt(21);
							transMode1 = rs.getString(22);
							grossWeight1 = rs.getInt(23);
							saleOrder = rs.getString(24);
						}
						System.out.println("fetch data from invoice completed");
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						sql = "SELECT cheque_no, cheque_date FROM SORDER WHERE SALE_ORDER = ? ";
						pstmt = conn.prepareStatement(sql);
						System.out.println(sql);
						pstmt.setString(1,saleOrder);
						rs = pstmt.executeQuery();
						while (rs.next())
						{
							refNo = rs.getString(1);
							refDate = getDateValue(rs.getDate(2));
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						sql = "select tran_code,gr_no,gr_date, sail_date, sb_no,sb_date, shipment_id	 from   despatch where desp_id = ?";
						pstmt = conn.prepareStatement(sql);
						System.out.println(sql);
						pstmt.setString(1,despId);
						rs = pstmt.executeQuery();
						while (rs.next())
						{
							tranCode = rs.getString(1);
							grNo = rs.getString(2);
							grDate = getDateValue(rs.getDate(3));
							sailDate = getDateValue(rs.getDate(4));
							sbNo = rs.getString(5);
							sbDate = getDateValue(rs.getDate(6));
							shipmentId = rs.getString(7);							
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
					}
					if( childNodeName.equals("inv_status"))
					{
						status = genericUtility.getColumnValueFromNode( "inv_status", parentNode );
						if(status == null || status.trim().length() != 1)
						{
							status = "X";
						}
						System.out.println("Status "+status);
					}
					if( childNodeName.equals("eff_date"))
					{
						colunm = genericUtility.getColumnValueFromNode( "eff_date", parentNode);
						effDate = getDateValue(colunm);
						invoiceId = genericUtility.getColumnValueFromNode("invoice_id", parentNode );
						status = genericUtility.getColumnValueFromNode( "inv_status", parentNode );
						if(status.equals("B"))
						{
							sql = "select eff_date from invoice where invoice_id = ?";
							pstmt = conn.prepareStatement(sql);
							System.out.println(sql);
							pstmt.setString(1,invoiceId);
							rs = pstmt.executeQuery();
							while (rs.next())
							{
								Date eff_date2 = getDateValue(rs.getDate(1));
								if(effDate.before(eff_date2))
								{
									errCode = "VTEFF01";
									errList.add(errCode);
									errFields.add(childNodeName.toLowerCase());	
									break;
								}
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;
						}
						isChanged = isChangeDone(effDate1 , effDate , isChanged);
						System.out.println("effDate "+effDate);
					}
					if( childNodeName.equals("lr_no"))
					{
						lrNo = genericUtility.getColumnValueFromNode( "lr_no", parentNode );
						isChanged = isChangeDone(lrNo1 , lrNo , isChanged);
						System.out.println("lrNo "+lrNo);
					}
					if( childNodeName.equals("lr_date"))
					{
						colunm = genericUtility.getColumnValueFromNode( "lr_date", parentNode);
						lrDate = getDateValue(colunm);
						invoiceId = genericUtility.getColumnValueFromNode("invoice_id", parentNode );
						status = genericUtility.getColumnValueFromNode( "inv_status", parentNode );
						if( ! status.equals("D") )
						{
							if(lrDate != null)
							{
								sql = "select var_value from disparm	where  prd_code = '999999' and var_name = 'MAX_DELAY_IN_SHIP'";
								pstmt = conn.prepareStatement(sql);
								System.out.println(sql);
								rs = pstmt.executeQuery();
								while (rs.next())
								{
									long varValue = rs.getLong(1);
									if(varValue == 100)
									{
										varValue = 7;
									}
									sql1 = "select tran_date from invoice where invoice_id = ?";
									pstmt1 = conn.prepareStatement(sql1);
									System.out.println(sql1);
									pstmt1.setString(1, invoiceId);
									rs1 = pstmt1.executeQuery();
									while (rs1.next())
									{	
										Date invDate = getDateValue(rs1.getDate(1));
										int diffDate = Math.abs(invDate.compareTo(lrDate));
										if(lrDate.before(invDate) || varValue < diffDate)
										{
											errCode = "VTILRD";
											errList.add(errCode);
											errFields.add(childNodeName.toLowerCase());	
											break;
										}
									}
									rs1.close();
									rs1 = null;
									pstmt1.close();
									pstmt1 = null;
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
							}
							else
							{
								errCode = "VTILRDATE";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());	
								break;
							}
						}
						isChanged = isChangeDone(lrDate1 , lrDate , isChanged);
						System.out.println("lrDate "+lrDate);
					}
					if( childNodeName.equals("octroi_rcp_no"))
					{
						octroiRcpNo = genericUtility.getColumnValueFromNode( "octroi_rcp_no", parentNode );
						isChanged = isChangeDone(octroiRcpNo1 , octroiRcpNo , isChanged);
						System.out.println("octroiRcpNo "+octroiRcpNo);
					}
					if( childNodeName.equals("octroi_rcp_date"))
					{
						colunm = genericUtility.getColumnValueFromNode( "octroi_rcp_date", parentNode);
						octroiRcpDate = getDateValue(colunm);
						isChanged = isChangeDone(octroiRcpDate1 , octroiRcpDate , isChanged);
						System.out.println("octroiRcpDate " + octroiRcpDate);
					}
					if( childNodeName.equals("remarks"))
					{
						remarks = genericUtility.getColumnValueFromNode( "remarks", parentNode );
						isChanged = isChangeDone(remarks1 , remarks , isChanged);
						System.out.println("remarks "+remarks);
					}
					if( childNodeName.equals("no_art"))
					{
						noArt = Integer.parseInt(genericUtility.getColumnValueFromNode( "no_art", parentNode ));
						isChanged = isChangeDone(noArt1 , noArt , isChanged);
						System.out.println("noArt "+noArt);
					}
					if( childNodeName.equals("lorry_no"))
					{
						lorryNo = genericUtility.getColumnValueFromNode( "lorry_no", parentNode );
						isChanged = isChangeDone(lorryNo1 , lorryNo , isChanged);
						System.out.println("lorryNo "+lorryNo);
					}
					if( childNodeName.equals("gp_no"))
					{
						gpNo = genericUtility.getColumnValueFromNode( "gp_no", parentNode );
						isChanged = isChangeDone(gpNo1 , gpNo , isChanged);
						System.out.println("gpNo " + gpNo);
					}
					if( childNodeName.equals("gp_date"))
					{
						colunm = genericUtility.getColumnValueFromNode( "gp_date", parentNode);
						gpDate = getDateValue(colunm);
						isChanged = isChangeDone(gpDate1 , gpDate , isChanged);
						System.out.println("gpDate "+gpDate);
					}
					if( childNodeName.equals("curr_code__frt"))
					{
						currCodeFrt = genericUtility.getColumnValueFromNode( "curr_code__frt", parentNode );
						if(currCodeFrt != null && currCodeFrt.trim().length() > 0)
						{
							sql = "Select Count(*) from currency where curr_code = ?";
							pstmt = conn.prepareStatement(sql);
							System.out.println(sql);
							pstmt.setString(1,currCodeFrt);
							rs = pstmt.executeQuery();
							while (rs.next())
							{
								int count = rs.getInt(1);
								if(count == 0)
								{
									errCode = "VTCURRCD1";
									errList.add(errCode);
									errFields.add(childNodeName.toLowerCase());	
									break;
								}
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;
							sql = " select std_exrt from currency where curr_code = ? ";
							pstmt = conn.prepareStatement(sql);
							System.out.println(sql);
							pstmt.setString(1,currCodeFrt);
							rs = pstmt.executeQuery();
							while (rs.next())
							{
								exchRateFrt = rs.getInt(1);
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;
							sql = " select std_exrt from currency where curr_code = ? ";
							pstmt = conn.prepareStatement(sql);
							System.out.println(sql);
							pstmt.setString(1,currCodeIns);
							rs = pstmt.executeQuery();
							while (rs.next())
							{
								exchRateIns = rs.getInt(1);
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;
						}
						isChanged = isChangeDone(currCodeFrt1 , currCodeFrt , isChanged);
						System.out.println("currCodeFrt " + currCodeFrt);
					}
					
					if( childNodeName.equals("frt_amt"))
					{
						frtAmt = Integer.parseInt(genericUtility.getColumnValueFromNode( "frt_amt", parentNode ));
						isChanged = isChangeDone(frtAmt1 , frtAmt , isChanged);
						System.out.println("frtAmt " + frtAmt);
					}
					if( childNodeName.equals("curr_code__ins"))
					{
						currCodeIns = genericUtility.getColumnValueFromNode( "curr_code__ins", parentNode );
						if(currCodeIns != null && currCodeIns.trim().length() > 0)
						{
							sql = "Select Count(*) from currency where curr_code = ?";
							pstmt = conn.prepareStatement(sql);
							System.out.println(sql);
							pstmt.setString(1,currCodeIns);
							rs = pstmt.executeQuery();
							while (rs.next())
							{
								int count = rs.getInt(1);
								if(count == 0)
								{
									errCode = "VTCURRCD1";
									errList.add(errCode);
									errFields.add(childNodeName.toLowerCase());	
								}
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;
						}
						isChanged = isChangeDone(currCodeIns1 , currCodeIns , isChanged);
						System.out.println("currCodeIns " + currCodeIns);
					}
					
					if( childNodeName.equals("ins_amt"))
					{
						insAmt = Integer.parseInt(genericUtility.getColumnValueFromNode( "ins_amt", parentNode ));
						isChanged = isChangeDone(insAmt1 , insAmt , isChanged);
						System.out.println("insAmt " + insAmt);
					}
					if( childNodeName.equals("trans_mode"))
					{
						transMode = genericUtility.getColumnValueFromNode( "trans_mode", parentNode );
						isChanged = isChangeDone(transMode1 , transMode , isChanged);
						System.out.println("transMode "+ transMode);
					}
					if( childNodeName.equals("gross_weight"))
					{
						grossWeight = Integer.parseInt(genericUtility.getColumnValueFromNode( "gross_weight", parentNode ));
						isChanged = isChangeDone(grossWeight1 , grossWeight , isChanged);
						System.out.println("grossWeight " + grossWeight);
					}
				}
				if(status.trim().equals("B")||status.trim().equals("D")||status.trim().equals("S"))
				{
					if(invoiceId != null && invoiceId.trim().length() > 0 && !(errList.size() > 0))
					{
						if(isChanged || !status.equals("S"))
						{
							System.out.println("++++++++++++++=*********Insert into Invoice Amendment*********+++++++");
							sql="INSERT INTO INVOICE_AMENDMENT ("
									+" INVOICE_ID, STATUS, EFF_DATE, DESP_ID, TRAN_CODE, LR_NO, LR_DATE, OCTROI_RCP_NO, OCTROI_RCP_DATE,"
									+" REMARKS, NO_ART, LORRY_NO, GP_NO, GP_DATE, CURR_CODE__FRT, EXCH_RATE__FRT, FRT_AMT, CURR_CODE__INS,"
									+" EXCH_RATE__INS, INS_AMT, STAN_CODE__INIT, FRT_TYPE, TRANS_MODE, GROSS_WEIGHT, EMP_CODE__APRV,"
									+" REF_NO, REF_DATE, GR_NO, GR_DATE, SAIL_DATE, SB_NO, SB_DATE, SHIPMENT_ID"
									+") VALUES(? , ? , ? , ? , ? , ? , ? , ? , ? , ?  , ? , ? , ? , ? , ? , ?  , ? , ? , ? , ?  , ? , ? , ? , ?  , ? , ? , ? , ? , ? , ? , ? , ? , ? )";
							pstmt = conn.prepareStatement(sql);
							System.out.println("after pstmt row 522 sql["+sql+"]");
							pstmt.setString(1,invoiceId);
							pstmt.setString(2,status);
							pstmt.setDate(3,effDate);
							pstmt.setString(4,despId);
							pstmt.setString(5,tranCode);
							pstmt.setString(6,lrNo);
							pstmt.setDate(7,lrDate);
							pstmt.setString(8,octroiRcpNo);
							pstmt.setDate(9,octroiRcpDate);
							pstmt.setString(10,remarks);
							pstmt.setInt(11,noArt);
							pstmt.setString(12,lorryNo);
							pstmt.setString(13,gpNo);
							pstmt.setDate(14,gpDate);
							pstmt.setString(15,currCodeFrt);
							pstmt.setInt(16,exchRateFrt);
							pstmt.setInt(17,frtAmt);
							pstmt.setString(18,currCodeIns);
							pstmt.setInt(19,exchRateIns);
							pstmt.setInt(20,insAmt);
							pstmt.setString(21,stanCodeInit);
							pstmt.setString(22,frtType);
							pstmt.setString(23,transMode);
							pstmt.setInt(24,grossWeight);
							pstmt.setString(25,empCodeAprv);
							pstmt.setString(26,refNo);
							pstmt.setDate(27,refDate);
							pstmt.setString(28,grNo);
							pstmt.setDate(29,grDate);
							pstmt.setDate(30,sailDate);
							pstmt.setString(31,sbNo);
							pstmt.setDate(32,sbDate);
							pstmt.setString(33,shipmentId);
							pstmt.executeUpdate();
							System.out.println("after pstmt row 551");
							pstmt.close();
							pstmt = null;
							System.out.println("1 ROW INSERTED");
							
							sql = " select case when confirmed is null then 'N' else confirmed end from invoice_amendment where invoice_id = ? and	status = ? ";
							pstmt = conn.prepareStatement(sql);
							System.out.println(sql);
							pstmt.setString(1,invoiceId);
							pstmt.setString(2,status);
							rs = pstmt.executeQuery();
							if (rs.next())
							{
								confirm = rs.getString(1);
								if(confirm.trim().equals("Y"))
								{
									errCode = "VTMCONF1";
									errList.add(errCode);
									errFields.add(childNodeName.toLowerCase());
									conn.rollback();
								}
								else
								{
									sql1 = " update invoice set curr_code__ins = ?, exch_rate__ins = ?, ins_amt = ?, curr_code__frt = ?, exch_rate__frt = ? , frt_amt = ? where invoice_id = ? ";
									pstmt1 = conn.prepareStatement(sql1);
									System.out.println(sql1);
									pstmt1.setString(1,currCodeIns);
									pstmt1.setInt(2,exchRateIns);
									pstmt1.setInt(3,insAmt);
									pstmt1.setString(4,currCodeFrt);
									pstmt1.setInt(5,exchRateFrt);
									pstmt1.setInt(6,frtAmt);
									pstmt1.setString(7,invoiceId);
									pstmt1.executeUpdate();
									System.out.println("after update in invoice---1");
									pstmt1.close();
									pstmt1 = null;
									
									sql1 = "select net_amt, curr_code, exch_rate  from invoice where invoice_id = ? ";
									pstmt1 = conn.prepareStatement(sql1);
									System.out.println(sql1);
									pstmt1.setString(1,invoiceId);
									rs1 = pstmt1.executeQuery();
									while (rs1.next())
									{
										int netAmt = rs1.getInt(1);
										String currCode = rs1.getString(2);
										int exchRate = rs1.getInt(3);
										if(!currCode.trim().equals(currCodeFrt.trim()))
										{
											frtAmt = (frtAmt * exchRateFrt) / exchRate ;
										}
										if(!currCode.trim().equals(currCodeIns.trim()))
										{
											insAmt = (insAmt * exchRateIns) / exchRate ;
										}
										int totalAmt = frtAmt + insAmt ;
										
										
										sql3 = " select inv_line_no, desp_id, desp_line_no, net_amt from invoice_trace where	invoice_id =  ? ";
										pstmt3 = conn.prepareStatement(sql3);
										System.out.println(sql3);
										pstmt3.setString(1,invoiceId);
										rs3 = pstmt3.executeQuery();
										while (rs3.next())
										{
											int lineNo = rs3.getInt(1);
											String despIdTrace = rs3.getString(2);
											String despLineNo = rs3.getString(3);
											int netAmtTrace = rs3.getInt(4);
											if(netAmt == 0)
											{
												netAmt = 1;
											}
											int lcPercent =  (netAmtTrace * netAmt) * 100 ;
											int newAmt = (totalAmt * lcPercent) / 100 ;
											int fobValue = netAmtTrace - newAmt ;
											
											sql2 = " update invoice_trace set fob_value  = ? where ( invoice_id = ? ) AND ( inv_line_no = ? ) AND	 ( desp_id = ? ) AND ( desp_line_no = ? )  ";
											pstmt2 = conn.prepareStatement(sql2);
											System.out.println(sql2);
											pstmt2.setInt(1,fobValue);
											pstmt2.setString(2,invoiceId);
											pstmt2.setInt(3,lineNo);
											pstmt2.setString(4,despIdTrace);
											pstmt2.setString(5,despLineNo);
											pstmt2.executeUpdate();
											System.out.println("after update in invoice Trace---1");
											pstmt2.close();
											pstmt2 = null;
										}
										rs3.close();
										rs3 = null;
										pstmt3.close();
										pstmt3 = null;
									}
									rs1.close();
									rs1 = null;
									pstmt1.close();
									pstmt1 = null;
									if(status.trim().equals("S"))
									{
										
										effDate = lrDate;
										sql1 = " select cr_days, eff_date from invoice where invoice_id = ? ";
										pstmt1 = conn.prepareStatement(sql1);
										System.out.println(sql1);
										pstmt1.setString(1,invoiceId);
										rs1 = pstmt1.executeQuery();
										Date dueDate = lrDate;
										while (rs1.next())
										{
											int crDay = rs1.getInt(1);
									//		Date effDate2 = getDateValue(rs1.getDate(2));
											Calendar cal = Calendar.getInstance();
											cal.setTime(new java.util.Date(lrDate.getTime()));
											cal.add(Calendar.DATE, crDay);
											dueDate = getDateValue(new java.sql.Date(cal.getTime().getTime()));
										}
										rs1.close();
										rs1 = null;
										pstmt1.close();
										pstmt1 = null;
										
										sql2 = " update invoice set eff_date = ? , due_date = ? , lr_no = ? , lr_date  = ? , " +
										" lorry_no = ?, tran_code= ?, remarks  = ?, octroi_rcp_no	  = ?, octroi_rcp_date = ? , no_art = ? , " +
										" gross_weight = ?, trans_mode   = ? where invoice_id = ? ";
										pstmt2 = conn.prepareStatement(sql2);
										System.out.println(sql2);
										pstmt2.setDate(1,lrDate);
										pstmt2.setDate(2,dueDate);
										pstmt2.setString(3,lrNo);
										pstmt2.setDate(4,lrDate);
										pstmt2.setString(5,lorryNo);
										pstmt2.setString(6,tranCode);
										pstmt2.setString(7,remarks);
										pstmt2.setString(8,octroiRcpNo);
										pstmt2.setDate(9,octroiRcpDate);
										pstmt2.setInt(10,noArt);
										pstmt2.setInt(11,grossWeight);
										pstmt2.setString(12,transMode);
										pstmt2.setString(13,invoiceId);
										pstmt2.executeUpdate();
										System.out.println("after update in invoice---1");
										pstmt2.close();
										pstmt2 = null;
										
										sql2 = "update despatch set lr_date = ? , gr_date = ? , sail_date = ? , lr_no	= ? , lorry_no = ? , gr_no = ? ," +
												" gp_no = ? , gp_date = ? , no_art = ? , tran_code = ? , sb_no = ? , sb_date = ? , stan_code__init = ? ," +
												" shipment_id 	= ?,	 gross_weight = ? , octroi_rcp_no = ? , orctoi_rcp_date = ? ,	 trans_mode = ? " +
												" where desp_id = ? ";
										pstmt2 = conn.prepareStatement(sql2);
										System.out.println(sql2);
										pstmt2.setDate(1,lrDate);
										pstmt2.setDate(2,grDate);
										pstmt2.setDate(3,sailDate);
										pstmt2.setString(4,lrNo);
										pstmt2.setString(5,lorryNo);
										pstmt2.setString(6,grNo);
										pstmt2.setString(7,gpNo);
										pstmt2.setDate(8,gpDate);
										pstmt2.setInt(9,noArt);
										pstmt2.setString(10,tranCode);
										pstmt2.setString(11,sbNo);
										pstmt2.setDate(12,sbDate);
										pstmt2.setString(13,stanCodeInit);
										pstmt2.setString(14,shipmentId);
										pstmt2.setInt(15,grossWeight);
										pstmt2.setString(16,octroiRcpNo);
										pstmt2.setDate(17,octroiRcpDate);
										pstmt2.setString(18,transMode);
										pstmt2.setString(19,despId);
										pstmt2.executeUpdate();
										System.out.println("after update in despatch---1");
										pstmt2.close();
										pstmt2 = null;
										
										sql2 = " update receivables set due_date = ? where tran_ser = 'S-INV' and ref_no   = ? ";
										pstmt2 = conn.prepareStatement(sql2);
										System.out.println(sql2);
										pstmt2.setDate(1,dueDate);
										pstmt2.setString(2,invoiceId);
										pstmt2.executeUpdate();
										System.out.println("after update in receivable---1");
										pstmt2.close();
										pstmt2 = null;
										
										sql2 = " select a.tran_id , (case when a.confirmed is null then 'N' else a.confirmed end) , b.due_date " +
												" from receipt a, rcpdet b where a.tran_id =  b.tran_id and b.ref_ser = 'S-INV' and b.ref_no = ? ";
										pstmt2 = conn.prepareStatement(sql2);
										System.out.println(sql2);
										pstmt2.setString(1,invoiceId);
										rs2 = pstmt2.executeQuery();
										while (rs2.next())
										{
											String tranId = rs2.getString(1);
											String confirm1 = rs2.getString(2);
										//	Date rcpDueDate = rs2.getDate(3);
											if(! (confirm1.trim().equals("Y")))
											{
												sql1 = " update rcpdet set due_date = ?	where tran_id = ? and ref_ser = 'S-INV' and ref_no = ? ";
												pstmt1 = conn.prepareStatement(sql1);
												System.out.println(sql1);
												pstmt1.setDate(1,dueDate);
												pstmt1.setString(2,tranId);
												pstmt1.setString(3,invoiceId);
												pstmt1.executeUpdate();
												System.out.println("after update in receivable---1");
												pstmt1.close();
												pstmt1 = null;
											}
										}
										rs2.close();
										rs2 = null;
										pstmt2.close();
										pstmt2 = null;
									}										
								}
							}
							
							if(!(errList.size() > 0))
							{
								if(! (confirm.equals("Y")))
								{
									sql2 = " update invoice_amendment set confirmed = ?, conf_date = ?, emp_code__aprv = ? where invoice_id = ? and status = ? ";
									pstmt2 = conn.prepareStatement(sql2);
									System.out.println(sql2);
									pstmt2.setString(1,"Y");
									pstmt2.setDate(2, new Date(new java.util.Date().getTime()));
									pstmt2.setString(3,genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginEmpCode"));
									pstmt2.setString(4,invoiceId);
									pstmt2.setString(5,status);
									pstmt2.executeUpdate();
									System.out.println("after update in invoice confirm---1");
									pstmt2.close();
									pstmt2 = null;
								}
								String ediOption = "";
					            String dataStr = "";
					            sql1 = "SELECT EDI_OPTION FROM TRANSETUP WHERE TRAN_WINDOW = 'w_inv_amendment' ";
					            pstmt1 = conn.prepareStatement(sql1);
					            rs1 = pstmt1.executeQuery();
					            if ( rs1.next() )
					            {
					                ediOption = rs1.getString("EDI_OPTION");
					                if(ediOption == null)
				                	{
				                		ediOption = "";
				                	}
					            }
					            rs1.close();rs1 = null;
					            pstmt1.close();pstmt1 = null;
	
					            if ( "1".equals(ediOption.trim()) )
					            {
					                CreateRCPXML createRCPXML = new CreateRCPXML("w_inv_amendment","tran_id");
					                dataStr = createRCPXML.getTranXML( invoiceId+":"+status, conn );
					                System.out.println( "dataStr =[ "+ dataStr + "]" );
					                Document ediDataDom = genericUtility.parseString(dataStr);
					                E12CreateBatchLoadEjb e12CreateBatchLoad = new E12CreateBatchLoadEjb();
					                String retString = e12CreateBatchLoad.createBatchLoad(ediDataDom, "w_inv_amendment", "0", xtraParams, conn );
					                createRCPXML = null;
					                e12CreateBatchLoad = null;
					                if( retString != null && "SUCCESS".equals(retString) )
					                {
					                    System.out.println("retString from batchload = 	["+retString+"]");
					                }
					            }
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;
							conn.commit();
						}
					}
				}
				else
				{
					errCode = "INVAMDSTS0";
					errList.add(errCode);
					errFields.add(childNodeName.toLowerCase());
				}
			}
			System.out.println("xml for invoice_amendment table UPDATE complete");
			System.out.println("after commit");
			int errListSize = errList.size();
			cnt = 0;
			if(errList != null && errListSize > 0)
			{
				for(cnt = 0; cnt < errListSize; cnt ++)
				{
					errCode = errList.get((int) cnt);
					System.out.println("errCode .........." + errCode);
					errString = vejb.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);
						errString = "";
					}
					if(errorType.equalsIgnoreCase("E"))
					{
						break;
					}
				}
				errList.clear();
				errList = null;
				errFields.clear();
				errFields = null;
				errStringXml.append("</Errors> </Root> \r\n");
			}
			else
			{
				errStringXml = new StringBuffer("");
			}
		} // try
		catch(Exception e)
		{
			try{
				conn.rollback();
			}
			catch(SQLException es)
			{
				System.out.println("Rollback exception : " + es.getMessage() + ":");
			}
			System.out.println("Exception : InvoiceAmdPrs@process():" + e.getMessage() + ":");
			throw new ITMException(e);
		}
		finally
		{
			System.out.println("!! Closing Connection in process() !!");
			try
			{
				conn.close();
				conn = null;
			}
			catch(Exception se){
				System.out.println(se.getMessage());
			}
		}
		errString = errStringXml.toString();
		return errString;
	} //process


	private boolean isChangeDone(int dbValue, int domValue,	boolean isChanged) 
	{
		if(dbValue != domValue)
		{
			isChanged = true;
		}
		System.out.println("DBvALUE"+isChanged);
		return isChanged;
	}

	private boolean isChangeDone(String dbValue, String domValue, boolean isChanged)
	{
		if(dbValue != null && domValue != null)
		{
			if(!dbValue.equals(domValue))
			{
				isChanged = true;
			}	
		}
		else if(dbValue != domValue)
		{
			isChanged = true;
		}
			
		System.out.println("DBvALUE"+isChanged);
		return isChanged;
	}

	private boolean isChangeDone(Date dbValue, Date domValue, boolean isChanged) 
	{
		if(dbValue != null && domValue != null)
		{
			if(!dbValue.equals(domValue))
			{
				isChanged = true;
			}	
		}
		else if(dbValue != domValue)
		{
			isChanged = true;
		}
		System.out.println("DBvALUE"+isChanged);
		return isChanged;
	}

	private boolean isAlreadyAmended(String invoiceId, String status, Connection conn)
	{
		String sql = "";
		PreparedStatement pstmt = null;
		 ResultSet rs = null;
		try
		{
			sql = "SELECT count(*) FROM INVOICE_AMENDMENT WHERE INVOICE_ID = ? AND STATUS = ? ";
			pstmt = conn.prepareStatement(sql);
			System.out.println(sql);
			pstmt.setString(1, invoiceId);
			pstmt.setString(2, status);
			System.out.println("BEFORE A execute");
			rs = pstmt.executeQuery();
			System.out.println("after A execute");
			while (rs.next())
			{
				int count = rs.getInt(1);
				if(count>0)
				{
					System.out.println("return trueeeeeeeeeee");
					return true;
				}
			}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
		}
		catch(Exception exp)
		{
			System.out.println("return exception trueeeeeeeeee");
			return true;
		}
		System.out.println("return falseeeeee");
		return false;
	}

	private Date getDateValue(String colunm) {
		Date date = null;
		SimpleDateFormat format1 = new SimpleDateFormat("yyyy-MM-dd");
		SimpleDateFormat format2 = new SimpleDateFormat("dd/MMM/yyyy");
			try
			{
				date = new java.sql.Date(format2.parse( format2.format(format1.parse(colunm))).getTime());
			}
			catch(Exception exc)
			{	
				date = null;
			}
			System.out.println("Date is ["+date+"]");
			return date;
	}
	
	private Date getDateValue(Date date) {
		
		SimpleDateFormat format2 = new SimpleDateFormat("dd/MMM/yyyy");
			try
			{
				date = new java.sql.Date(format2.parse( format2.format(date)).getTime());
			}
			catch(Exception exc)
			{	
				date = null;
			}
			System.out.println("Date is ["+date+"]");
			return date;
	}
	
	private String errorType(Connection conn , String errorCode)
	{
		String msgType = "";
		PreparedStatement pstmt = null ; 
		ResultSet rs = null;
		try
		{			
			String  sql = " SELECT MSG_TYPE FROM MESSAGES WHERE MSG_NO = ? ";
			pstmt = conn.prepareStatement(sql);			
			pstmt.setString(1, errorCode);			
			rs = pstmt.executeQuery();
			while(rs.next())
			{
				msgType = rs.getString("MSG_TYPE");
			}			
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}		
		finally
		{
			try
			{
				if(rs != null)
				{
					rs.close();
					rs = null;
				}
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
		}		
		return msgType;
	}
}