//
/*******************************************    
Title : ExpStkTransferPos
    Date  : 03/06/12
    Author: Rohan Bhogale.

 ********************************************************/
package ibase.webitm.ejb.wms;

import ibase.system.config.*;
import ibase.webitm.ejb.*;
//Changed by Rohan on 06-08-13 for importing stock transfer confirmation
import ibase.webitm.ejb.dis.adv.StockTransferConf;

import ibase.webitm.ejb.dis.adv.InvHoldRelConf;
import ibase.webitm.ejb.dis.InvAllocTraceBean;
import ibase.webitm.ejb.dis.DistStkUpdLocal;
//Changed by sumit on 10/09/12 import  StockTransferPos to call stock_allocation.
import ibase.webitm.ejb.dis.DistCommon;
import ibase.webitm.ejb.dis.StockTransferPos;
import ibase.webitm.ejb.dis.InvHoldGen;
import ibase.webitm.ejb.dis.StockUpdate;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.utility.TransIDGenerator;
import ibase.utility.CommonConstants;
import java.io.*;
import java.rmi.RemoteException;
import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;

import org.w3c.dom.*;

import javax.ejb.*;
import javax.naming.InitialContext;

@javax.ejb.Stateless
public class ExpStkTransferPos extends ValidatorEJB implements ExpStkTransferPosLocal, ExpStkTransferPosRemote
{
	public String postSave()throws RemoteException,ITMException
	{
		return "";
	}
	public String postSave(String domString, String tranId,String editFlag, String xtraParams, Connection conn ) throws RemoteException,ITMException
	{
		String errString = "";
		String sql = "";
		String sqlDet = "";
		String chgUser = null;
		String chgTerm = null;
		String siteCode = null;
		String loginEmpCode = null;
		String tranIdHold = "";
		String lineNoHold = "";
		String lockCode = "";
		String trfNexpLock = "",trfExprLock = "",nearExpInv = "",expInv = "";
		String locCodeTo = "",invStatLoc = "",sqlInv = "";
		ArrayList stockList = new ArrayList();
		ArrayList stockNormList = new ArrayList();
		//ArrayList<String> tranIdRel = new ArrayList<String>();
		String tranIdRel = "";
		
		int invRel = 0;
		int invRelDet = 0;
		boolean isError = false;
		
		GenericUtility genericUtility = GenericUtility.getInstance();
		ITMDBAccessEJB itmDBAccess = new ITMDBAccessEJB();
		Document dom = null;
		
		PreparedStatement pstmt = null;
		PreparedStatement pstmtDet = null;
        ResultSet rs = null;
        
        InvHoldRelConf invHldRelCon = new InvHoldRelConf();
        StockTransferConf stkTrnsConf = new StockTransferConf();
        StockTransferPos stockTransferPos = new StockTransferPos();
        InvHoldGen holdGen =  new InvHoldGen();
        DistCommon distCommon = new DistCommon();
        Exception unChkException = null;
        
		try
		{
			//conn.setAutoCommit(false);
			
			siteCode = getValueFromXTRA_PARAMS(xtraParams, "loginSiteCode");
	        loginEmpCode = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginEmpCode" );
	        
	        chgUser = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginCode");
			chgTerm = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "termId");	
			
			java.util.Date date = new java.util.Date(); 
			java.sql.Date currDate = new java.sql.Date(date.getTime());
			
			dom = genericUtility.parseString(domString);
		
			NodeList parentNodeList = null;
			NodeList childList = null;
			int parentNodeListLength ;
			int detLine = 0;
			
			trfNexpLock = checkNull(distCommon.getDisparams("999999","TRANSFERED_NEXP_LOCK",conn));
			trfExprLock = checkNull(distCommon.getDisparams("999999","TRANSFERED_EXPR_LOCK",conn));
			nearExpInv = checkNull(distCommon.getDisparams("999999","NEXPR_INVSTAT",conn));
			expInv = checkNull(distCommon.getDisparams("999999","EXPR_INVSTAT",conn));
			
			parentNodeList = dom.getElementsByTagName("Detail1");
			parentNodeListLength = parentNodeList.getLength();
			for(int row = 0; row < parentNodeListLength; row++)
			{
				lockCode = GenericUtility.getInstance().getColumnValueFromNode("lock_type", dom.getElementsByTagName("Detail1").item(row));
			}
			System.out.println(" lockCode ["+lockCode+"]");
			//Chnaged by sumit in 08/09/12 changed detail number.
			//parentNodeList = dom.getElementsByTagName("Detail2");
			parentNodeList = dom.getElementsByTagName("Detail3");
			parentNodeListLength = parentNodeList.getLength(); 			
			
			sql = "INSERT INTO INV_HOLD_REL(TRAN_ID,TRAN_DATE,SITE_CODE,CONFIRMED,EMP_CODE__APRV,CHG_USER,CHG_TERM,CHG_DATE)"+
		      " VALUES(?,?,?,?,?,?,?,?)";
			
			sqlDet = "INSERT INTO INV_HOLD_REL_DET(TRAN_ID,LINE_NO,TRAN_ID__HOLD,LINE_NO__HOLD)"+
	         "VALUES(?,?,?,?)";
			
			System.out.println("parentNodeListLength***************************************"+parentNodeListLength);
			//tranIdRel.add(getTranId("w_inv_hold_rel", conn));
        	//Added by manoj sharma 29/01/2013 to generate tranid as per keystring
        	tranIdRel=generateTranId( "w_inv_hold_rel", siteCode, conn );
			pstmt = conn.prepareStatement(sql);
			pstmt.setString( 1, tranIdRel );
			pstmt.setDate( 2, currDate );
			pstmt.setString( 3, siteCode );
			pstmt.setString( 4, "N" );
			pstmt.setString( 5, loginEmpCode );
			pstmt.setString( 6, chgUser );
			pstmt.setString( 7, chgTerm );
			pstmt.setDate( 8,currDate );
			invRel = pstmt.executeUpdate();
	        for(int row = 0; row < parentNodeListLength; row++)
			{
	        	detLine++;
	        	
				
				//Changed by sumit in 08/09/12 changed detail number start
	        	/*lineNoHold = GenericUtility.getInstance().getColumnValueFromNode("line_no_hold", dom.getElementsByTagName("Detail2").item(row));
	        	tranIdHold = GenericUtility.getInstance().getColumnValueFromNode("tran_id_hold", dom.getElementsByTagName("Detail2").item(row));*/
				lineNoHold = GenericUtility.getInstance().getColumnValueFromNode("line_no_hold", dom.getElementsByTagName("Detail3").item(row));
	        	tranIdHold = GenericUtility.getInstance().getColumnValueFromNode("tran_id_hold", dom.getElementsByTagName("Detail3").item(row));
	        	//Changed by sumit in 08/09/12 changed detail number end
	        	/*itemCode = "";
				siteCode = "";
				locCode = "";
				lotNo = "";
				lotSl = "";*/
	        	//Changed by sumit in 01/10/12 getting detail start
	        	locCodeTo = GenericUtility.getInstance().getColumnValueFromNode("loc_code__to", dom.getElementsByTagName("Detail3").item(row));
	        	sqlInv = "SELECT INV_STAT FROM LOCATION WHERE LOC_CODE = ?";
				pstmt = conn.prepareStatement( sqlInv );
				pstmt.setString( 1, locCodeTo );
				rs = pstmt.executeQuery();
				
				if( rs.next() )       
				{
					invStatLoc = checkNull(rs.getString("INV_STAT"));	
				}
				
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
				
				if(rs != null)
				{
					rs.close();
					rs = null;
				}
				
	        	HashMap dataMap = new HashMap();
	        	dataMap.put("item_code",GenericUtility.getInstance().getColumnValueFromNode("item_code", dom.getElementsByTagName("Detail3").item(row)));
	        	dataMap.put("site_code",siteCode);
	        	dataMap.put("loc_code",locCodeTo);
	        	dataMap.put("lot_sl",GenericUtility.getInstance().getColumnValueFromNode("lot_sl__to", dom.getElementsByTagName("Detail3").item(row)));
	        	dataMap.put("lot_no",GenericUtility.getInstance().getColumnValueFromNode("lot_no__to", dom.getElementsByTagName("Detail3").item(row)));
	        	
	        	if(nearExpInv.trim().equalsIgnoreCase(invStatLoc.trim()) || expInv.trim().equalsIgnoreCase(invStatLoc.trim()))
	        	{
	        		stockList.add(dataMap);
	        	}
	        	else
	        	{
	        		stockNormList.add(dataMap);
	        	}
	        	//Changed by sumit in 08/09/12 getting detail end
	        	
	        	pstmtDet = conn.prepareStatement(sqlDet);
	        	pstmtDet.setString( 1,tranIdRel);
	        	pstmtDet.setInt( 2,detLine );
	        	pstmtDet.setString( 3,tranIdHold );
	        	pstmtDet.setInt( 4,Integer.parseInt(lineNoHold.trim()) );
	   
	        	invRelDet = pstmtDet.executeUpdate();
	        	
	        	if(pstmt != null)
	        	{
	        		pstmt.close();
	        		pstmt = null;
	        	}
	        	if(pstmtDet != null)
	        	{
	        		pstmtDet.close();
	        		pstmtDet = null;
	        	}	        	
			}	        
	        if(invRel > 0 && invRelDet > 0)
	        {
	        	//Chnaged by sumit on
	        	//conn.commit();
	        	//for(int j = 0;j<tranIdRel.size();j++)
	        	//{
	        		System.out.println("Confirmation is called for Tran ID"+tranIdRel);
	        		//errString = invHldRelCon.confirm(tranIdRel.get(j),xtraParams,"F");	        		
	        		//errString = invHldRelCon.confirm(tranIdRel.get(j),xtraParams,"F", conn, false);	        		      	        
	        		//Changed by manoj dtd 30/01/2013 to confirm  release transaction 
	        		errString = invHldRelCon.confirm(tranIdRel,xtraParams,"F", conn, false);
	        	//}	
	        }
	        //Changed by sumit on 17/09/12 as per manoharan and client do not release lock end.
	        
		        executepostSaveRec(dom, "3", editFlag, xtraParams, conn);
	    		System.out.println("executepostSaveRec  SUCESS -->");
		        //conn.commit();
				
	    		//Changed by sumit on 17/09/12 changing if condition 
		        /*if(errString.contains("VTCNFSUCC"))
		        {*/        
		        	errString = stkTrnsConf.confirm(tranId,xtraParams,"F",conn,false );
		       // }       
		        //Changed by Sankar on 04/10/13 insert new hold start.
	        	if( "NEXP_LOCK".equalsIgnoreCase(lockCode) && stockNormList.size() > 0 )
				{
					holdGen.generateHoldTrans( trfNexpLock, tranIdHold,"NEXP", siteCode, stockNormList, xtraParams, conn );
				}
				else if( "EXP_LOCK".equalsIgnoreCase(lockCode) && stockNormList.size() > 0 )
				{
					holdGen.generateHoldTrans( trfExprLock, tranIdHold,"EXP", siteCode, stockNormList, xtraParams, conn );
				}
	        	//Changed by Sankar on 04/10/13 insert new hold end.
	        	
		        //Changed by sumit on 01/10/12 insert new hold start.
	        	if( "NEXP_LOCK".equalsIgnoreCase(lockCode) && stockList.size() > 0 )
				{
					holdGen.generateHoldTrans( lockCode, tranIdHold,"NEXP", siteCode, stockList, xtraParams, conn );
				}
				else if( "EXP_LOCK".equalsIgnoreCase(lockCode) && stockList.size() > 0 )
				{
					holdGen.generateHoldTrans( lockCode, tranIdHold,"EXP", siteCode, stockList, xtraParams, conn );
				}
	        	//Changed by sumit on 01/10/12 insert new hold end.
				
	        
		}
		catch(Exception e)
		{
			try
			{
				isError = true;
				System.out.println("Exception "+e.getMessage());
				e.printStackTrace();
				conn.rollback();			
				throw new ITMException(e);
			}
			catch (Exception e1)
			{
				unChkException = e;
				throw new ITMException(e);
			}
		}
		finally
		{
			try
			{   
				/*if( !isError )
				{
					//conn.commit(); 
					errString = itmDBAccess.getErrorString("","CONFSUCC","");
					
				}*/
				if( rs != null )
				{
					rs.close();
					rs = null;
				}
				if( pstmt != null )
				{
					pstmt.close();
					pstmt = null;
				}
				
				if(isError)
				{
					
					throw new ITMException(unChkException);
				}
				
			}
			catch(Exception e)
			{
				e.printStackTrace();
				throw new ITMException(e);
			}
		}
		return errString;
	}
	// Method For Generating Tran id
	private String getTranId(String windowName, Connection conn) throws Exception
	{
		
		String keyStringQuery = "", errString = "";
		String xmlValues = "";
		String tranId = "";	
		ResultSet rSet = null ;
		PreparedStatement pstmt = null;
		
		try
		{
			
			keyStringQuery = "SELECT KEY_STRING, TRAN_ID_COL, REF_SER FROM TRANSETUP WHERE TRAN_WINDOW = ?";
	    	System.out.println("keyStringQuery--------->>"+keyStringQuery);
	    	
	    	pstmt = conn.prepareStatement(keyStringQuery);
	    	pstmt.setString(1,windowName);
	    	rSet = pstmt.executeQuery();	    	
			String tranSer1 = "";
			String keyString = "";
			String keyCol = "";
			if (rSet.next())
			{
				keyString = rSet.getString("KEY_STRING");
				keyCol = rSet.getString("TRAN_ID_COL");
				tranSer1 = rSet.getString("REF_SER");				
			}
			rSet.close(); rSet = null;
			pstmt.close();pstmt =null;
			
			System.out.println("keyString :"+keyString);
			System.out.println("keyCol :"+keyCol);
			System.out.println("tranSer1 :"+tranSer1);
			
			xmlValues ="<?xml version=\"1.0\" encoding=\"utf-8\"?><Root>";
			xmlValues = xmlValues + "<Header></Header>";
			xmlValues = xmlValues + "<Detail1>";
			xmlValues = xmlValues +"</Detail1></Root>";
			System.out.println("xmlValues  :["+xmlValues+"]");
			TransIDGenerator tg = new TransIDGenerator(xmlValues, "BASE", CommonConstants.DB_NAME);
			// Creating tran_id 
			tranId = tg.generateTranSeqID(tranSer1, keyCol, keyString, conn);
			System.out.println("************ Tran id ************"+tranId);
						
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{	
				if(rSet != null)
				{
					rSet.close();
					rSet=null;
				}	
				if(pstmt != null)
				{
					pstmt.close();
					pstmt=null;
				}				
				
			}
			catch(Exception e)
			{
				e.printStackTrace();
				throw new ITMException(e);
			}
		}
		return tranId;
	}
	
	//Changed by sumit on 10/09/12 to allocate quantity as StockTransferPos not called start.
	private String executepostSaveRec(Document dom ,String objContext,String editFlag, String xtraParams, Connection conn)throws RemoteException,ITMException
	{

		HashMap hashMap = new HashMap(); 
		//changed by sankara on 21/08/14 changed refser XRFX to XFRX
		String lineNo = "", tranId = "", tranSer = "XFRX", line = "", updateStatus = "";
		String siteCode = "", itemCode = "", quantity = "", locCode = "", lotNo = "", lotSl = "";
		DistStkUpdLocal distStkUpd = null;
		NodeList hdrDom = null;
		//Node currDetail = null;
		try
		{
			NodeList parentNodeList = null;
			NodeList childList = null;
			int parentNodeListLength ;
			int detLine = 0;
			
			
			System.out.println("objContext *****************=[" + objContext + "]");
			//System.out.println("domID =[" + domID + "]");

			distStkUpd = getComponent("DistStkUpd");
			
			hdrDom = dom.getElementsByTagName("Detail1");
			siteCode = GenericUtility.getInstance().getColumnValueFromNode("site_code",hdrDom.item(0));
			tranId = GenericUtility.getInstance().getColumnValueFromNode("tran_id",hdrDom.item(0));
			System.out.println("tran_id : " + tranId);

			//currDetail = getCurrentDetailFromDom(dom,domID); Commented by gulzar on 12/24/2011
			//currDetail = getCurrentDetailFromDom( dom, domID, objContext );//Change added by gulzar on 12/24/2011
			//updateStatus = getCurrentUpdateFlag(currDetail);

			/*if (currDetail != null && !updateStatus.equalsIgnoreCase("D"))
			{*/
			//Chnaged by sumit in 08/09/12 changed detail number.
			//parentNodeList = dom.getElementsByTagName("Detail2");
			parentNodeList = dom.getElementsByTagName("Detail3");
			parentNodeListLength = parentNodeList.getLength(); 
			for(int row = 0; row < parentNodeListLength; row++)
			{
				hashMap.clear();				
				itemCode = GenericUtility.getInstance().getColumnValueFromNode("item_code",dom.getElementsByTagName("Detail3").item(row));
				locCode = GenericUtility.getInstance().getColumnValueFromNode("loc_code__fr",dom.getElementsByTagName("Detail3").item(row));
				lotNo = GenericUtility.getInstance().getColumnValueFromNode("lot_no__fr",dom.getElementsByTagName("Detail3").item(row));
				lotSl = GenericUtility.getInstance().getColumnValueFromNode("lot_sl__fr",dom.getElementsByTagName("Detail3").item(row));
				quantity = GenericUtility.getInstance().getColumnValueFromNode("quantity",dom.getElementsByTagName("Detail3").item(row));
				lineNo = GenericUtility.getInstance().getColumnValueFromNode("line_no",dom.getElementsByTagName("Detail3").item(row));
				System.out.println("lineNo -----> :"+lineNo+" itemCode :"+itemCode+" locCodeFr :"+locCode+" lotNoFr :"+lotNo+" lotSlFr :"+lotSl+" quantity :"+quantity);
				String stlOption = chkStockOption(itemCode, siteCode);
				System.out.println("stlOption  :"+stlOption);
				if (!stlOption.equals("0"))
				{
					if (quantity == null)
					{
						quantity = "0.000";
					}
					hashMap.put("tran_date", new java.sql.Date(System.currentTimeMillis()));
					hashMap.put("ref_ser",tranSer);
					hashMap.put("ref_id", tranId);
					hashMap.put("ref_line", lineNo);
					hashMap.put("item_code", itemCode);
					hashMap.put("site_code", siteCode);
					hashMap.put("loc_code",locCode);
					hashMap.put("lot_no",lotNo);
					hashMap.put("lot_sl",lotSl);
					hashMap.put("alloc_qty", new Double(quantity.trim().length() == 0?"0":quantity));
					hashMap.put("chg_win","W_STOCK_TRANSFER_MULTI");
					hashMap.put("chg_user", GenericUtility.getInstance().getValueFromXTRA_PARAMS(xtraParams, "loginCode"));
					hashMap.put("chg_term", GenericUtility.getInstance().getValueFromXTRA_PARAMS(xtraParams, "termId"));
					//Calling DistStkUpdEJB
					System.out.println("Calling DistStkUpdEJB.....");					
					if (distStkUpd.updAllocTrace(hashMap, conn) > 0)
					{
						System.out.println(" ExpStkTransferPos : distStkUpd.UpdAllocTrace(HashMap, Connection) : Sucessuful!");
					}
					
				}//end if
			}//end if
		}//end try
		catch(Exception e)
		{
			System.out.println("Exception : ExpStkTransferPos: StockTransferPosEJB : actionHandler :" +e.getMessage());
			throw new ITMException(e);
		}
		return "";
	}

	private String chkStockOption(String itemCode, String siteCode)throws RemoteException,ITMException
	{
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		String sql = "", stkOption = "";
		ConnDriver connDriver = new ConnDriver();
		try
		{
			conn = connDriver.getConnectDB("DriverITM");
			stmt = conn.createStatement();
			sql = "SELECT CASE WHEN STK_OPT IS NULL THEN 'N' ELSE STK_OPT END "
				 +"FROM SITEITEM "
				 +"WHERE ITEM_CODE = '"+itemCode+"' "
				 +"AND SITE_CODE = '"+siteCode+"' ";
			System.out.println("sql :"+sql);
			rs = stmt.executeQuery(sql);
			if (rs.next())
			{
				stkOption = rs.getString(1);
			}
			if (stkOption == null || stkOption.equals("N"))
			{
				sql = "SELECT CASE WHEN STK_OPT IS NULL THEN '0' ELSE STK_OPT END "
					 +"FROM ITEM "
					 +"WHERE ITEM_CODE = '"+itemCode+"'";
				System.out.println("sql :"+sql);
				rs = stmt.executeQuery(sql);
				if (rs.next())
				{
					stkOption = rs.getString(1);
				}
			}//end if
			System.out.println("stkOption :"+stkOption);
		}//end try
		catch(SQLException e)
		{
			System.out.println("Exception : ExpStkTransferPos: chkStockOption : " +e.getMessage());
			throw new ITMException(e);
		}
		catch(Exception e)
		{
			System.out.println("Exception : ExpStkTransferPos : chkStockOption :" +e.getMessage());
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if (stmt != null)
				{
					stmt.close();
					stmt = null;
				}
			}
			catch(Exception e)
			{
				System.out.println("The Exception occurs in ExpStkTransferPos "+e);	
			}
		}
		return stkOption;
	}
	//private Node getCurrentDetailFromDom(Document dom,String domId)//Commented by gulzar on 12/24/2011
	private Node getCurrentDetailFromDom( Document dom, String domId, String objContext ) //changed added by gulzar on 12/24/2011
	{
		NodeList detailList = null;
		Node currDetail = null,reqDetail = null;
		String currDomId = "";
		int	detailListLength = 0;

		//detailList = dom.getElementsByTagName("Detail2");//Commented by gulzar on 12/24/2011
		detailList = dom.getElementsByTagName("Detail"+objContext);//change added by gulzar on 12/24/2011
		detailListLength = detailList.getLength();
		for (int ctr = 0;ctr < detailListLength;ctr++)
		{
			currDetail = detailList.item(ctr);
			currDomId = currDetail.getAttributes().getNamedItem("domID").getNodeValue();
			if (currDomId.equals(domId))
			{
				reqDetail = currDetail;
				break;
			}			
		}
		return reqDetail;
	}

	private String getCurrentUpdateFlag(Node currDetail)
	{
		NodeList currDetailList = null;
		String updateStatus = "",nodeName = "";
		int currDetailListLength = 0;

		currDetailList = currDetail.getChildNodes();
		currDetailListLength = currDetailList.getLength();
		for (int i=0;i< currDetailListLength;i++)
		{
			nodeName = currDetailList.item(i).getNodeName();
			if (nodeName.equalsIgnoreCase("Attribute"))
			{
				updateStatus = currDetailList.item(i).getAttributes().getNamedItem("updateFlag").getNodeValue();
				break;
			}
		}
		return updateStatus;		
	}	
	
	private DistStkUpdLocal getComponent(String componentName)throws Exception
    {
		DistStkUpdLocal comp = null;
        try
        {
			System.out.println("Looking For Component.... :: " + componentName);
			AppConnectParm appConnect = new AppConnectParm();
			java.util.Properties p = appConnect.getProperty();
			InitialContext ctx = new InitialContext(p);
			 comp = (DistStkUpdLocal)ctx.lookup("ibase/DistStkUpd/local"); // for ejb3
			//comp = distStkUpdHome.create();
			System.out.println(componentName+" Found.....");			
        }
        catch(Exception e)
        {
			throw e;
        }
		return comp;
	}
	//Changed by sumit on 10/09/12 to allocate quantity as StockTransferPos not called end.
	//Added by manoj sharma 29/01/2013 to generate tranid as per keystring
	private String generateTranId( String windowName, String siteCode, Connection conn )throws ITMException
    {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String selSql = "";
		String tranId = "";
		String tranSer = "";
		String keyString = "";
		String keyCol = "";
		String xmlValues = "";
		String paySiteCode = "";
		String effectiveDate = "";
		java.sql.Timestamp currDate = null;
		java.sql.Date effDate = null;
		GenericUtility genericUtility = GenericUtility.getInstance();

		 try
         {

			SimpleDateFormat sdfAppl = new SimpleDateFormat(genericUtility.getApplDateFormat());

			currDate = new java.sql.Timestamp(System.currentTimeMillis());
			String currDateStr = sdfAppl.format(currDate);

			selSql = "SELECT KEY_STRING, TRAN_ID_COL, REF_SER FROM TRANSETUP WHERE TRAN_WINDOW = ? ";
				//System.out.println("selSql :"+selSql);
			pstmt = conn.prepareStatement(selSql);
			pstmt.setString( 1, windowName );
			rs = pstmt.executeQuery();
			if (rs.next())
			{
					keyString = rs.getString("KEY_STRING");
					keyCol = rs.getString("TRAN_ID_COL");
					tranSer = rs.getString("REF_SER");
			}
			rs.close();rs = null;
			pstmt.close();pstmt = null;
			System.out.println("keyString :"+keyString);
			System.out.println("keyCol :"+keyCol);
			System.out.println("tranSer :"+tranSer);

			xmlValues ="<?xml version=\"1.0\" encoding=\"utf-8\"?><Root>";
			xmlValues = xmlValues + "<Header></Header>";
			xmlValues = xmlValues + "<Detail1>";
			xmlValues = xmlValues +        "<tran_id></tran_id>";
			xmlValues = xmlValues +        "<site_code>" + siteCode + "</site_code>";
			xmlValues = xmlValues +        "<tran_date>" + currDateStr + "</tran_date>";
			xmlValues = xmlValues + "</Detail1></Root>";
			System.out.println("xmlValues  :["+xmlValues+"]");
			TransIDGenerator tg = new TransIDGenerator(xmlValues, "BASE", CommonConstants.DB_NAME);
			tranId = tg.generateTranSeqID(tranSer, keyCol, keyString, conn);
			System.out.println("tranId :"+tranId);
         }
		catch (SQLException ex)
		{
			System.out.println("Exception ::" +selSql+ ex.getMessage() + ":");
			ex.printStackTrace();
			throw new ITMException(ex);
		}
		catch (Exception e)
		{
			System.out.println("Exception ::" + e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if (rs != null)
				{
						rs.close();
						rs = null;
				}
				if (pstmt != null)
				{
						pstmt.close();
						pstmt = null;
				}
			}
			catch(Exception e){}
		}
        return tranId;
     }//generateTranTd()	
	private String checkNull( String inputVal )
	{
		if ( inputVal == null )
		{
			inputVal = "";
		}
		return inputVal;
	}
}

