
package ibase.webitm.utility.wms;
import ibase.scheduler.utility.interfaces.Schedule;
import java.rmi.RemoteException;
import java.util.*;
import java.util.Date;
import java.sql.*;
import java.io.*;

import org.omg.CORBA.ORB;
import org.w3c.dom.*;

import java.util.Properties;

import javax.swing.text.NumberFormatter;
import javax.xml.parsers.*;
import javax.ejb.*;
import javax.naming.InitialContext;

import ibase.webitm.utility.ITMException;
import ibase.webitm.ejb.*;
import ibase.webitm.ejb.dis.DistCommon;
import ibase.webitm.ejb.dis.InvAllocTraceBean;
import ibase.webitm.ejb.dis.InvHoldGen;
import ibase.webitm.ejb.dis.adv.InvHoldRelConf;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.TransIDGenerator;

import ibase.utility.BaseException;
import ibase.utility.CommonConstants;
import ibase.utility.UserInfoBean;
import ibase.ejb.*;
import ibase.system.config.*;

import java.text.DateFormat;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.math.*;
import java.net.InetAddress;


public class NearExpirySchedule implements Schedule
{
	
	//GenericUtility genericUtility = GenericUtility.getInstance();
	//ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
	//ConnDriver connDriver = new ConnDriver();
	//CommonConstants commonConstants = new CommonConstants();
	String chgUser = null;
	String chgTerm = null;
	static long count_records=0;
	boolean isError = false;

	public String schedulePriority( String wrkflwPriority )throws Exception
	{
		return "";
	}
	public String schedule(HashMap map)throws Exception
	{
		return "";
	}
	public String schedule(String scheduleParamXML)throws Exception
	{
		String siteCode = "";
		ibase.utility.UserInfoBean userInfo = null;
		try
		{
			System.out.println("************ ["+scheduleParamXML+"]");
			userInfo = new ibase.utility.UserInfoBean( scheduleParamXML );
			siteCode = userInfo.getSiteCode();
			System.out.println("Site code = "+siteCode);
			//changed by sumit on 02/10/12 changing method parameter
			//insNearExpProducts(siteCode);			
			insNearExpProducts(siteCode,scheduleParamXML);
			//insNearExpProducts("TA821");
		}
		catch(Exception e)
		{
			throw new Exception(e);
		}
		return "";
	}
	//changed by sumit on 02/10/12 changing method parameter
	//public String insNearExpProducts(String siteCode) throws RemoteException,ITMException
	public String insNearExpProducts(String siteCode , String scheduleParamXML) throws RemoteException,ITMException
	{
		String salesPerson = "";
		String itemCode = "";
		String retString = "";
		String selectSql = "";
		String locCode = "";
		//String siteCode = "";
		String lotNo = "";
		String lotSl = "";
		String dataVal1 = "";
		String dataVal2 = "";
		String reservLoc = "";
		String lockORTransfer = "";
		String nexpLoc = "";
		String expLoc = "";
		String tranID = "";
		String loginSiteCode = "";
		String userId = "";
		String chgTerm = "";
		String acctCodeInv = "";
		String cctrCodeInv = "";
		String insertSql = "";
		String defReasCode = "";
		//Changed by sumit sarkar on 24/06/12 START
		String dStoreInvstat ="";
		String casePickLoc ="";
		String activePickLoc = "";
		String siteCodeFromDisPa = "";
		String forInvState = "";
		StringBuffer stringBuffer = new StringBuffer();
		int count1 = 0;
		//Changed by sumit sarkar on 24/06/12 END
		//Changed by sumit sarkar on 02/10/12 
		String holdStatus="",sql="";
		int count = 0;
		int updCnt = 0;
		int lineNo = 0;

		double quantity = 0d;
		double allocQty = 0d;
		double noArt = 0d;
		double minSelfLife = 0d;

		boolean isError = false;
		
		Connection conn = null;
		ResultSet rsStock = null;
		ResultSet rsItem = null;
		PreparedStatement pstmtStock = null;
		PreparedStatement pstmtItem = null;
		//Changed by sumit on 02/10/12
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		HashMap strAllocate = null;

		GenericUtility genericUtility = GenericUtility.getInstance();

		ITMDBAccessEJB itmDBAccess = new ITMDBAccessEJB();
		InvAllocTraceBean invAllocTrace = new InvAllocTraceBean();

		try
		{
			ConnDriver connDriver = new ConnDriver(); 
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			conn.setAutoCommit(false);
			
			DistCommon distComm = new DistCommon();
			
			//Changed By Pragyan as the veriable name RESERV_LOC  has been changed to RESERV_LOCATION
			//reservLoc = distComm.getDisparams("999999","RESERV_LOC",conn);
			//Changed by sumit sarkar on 04/07/12 commenting, because of no use.
			//reservLoc = distComm.getDisparams("999999","RESERV_LOCATION",conn);
			lockORTransfer = distComm.getDisparams("999999","LOCK_OR_TRANSFER",conn);
			//Changed by sumit sarkar on 24/06/12 uncomment below code and change var_name in parameter. start.
			//nexpLoc = distComm.getDisparams("999999","NEAREXP_LOC",conn);
			//expLoc = distComm.getDisparams("999999","EXP_LOC",conn);
			nexpLoc = distComm.getDisparams("999999","NEAR_EXP_LOCK",conn);
			expLoc = distComm.getDisparams("999999","EXP_LOCK",conn);
			//casePickLoc  = distComm.getDisparams("999999","CASE_PICK_INVSTAT",conn);
			//activePickLoc  = distComm.getDisparams("999999","ACTIVE_PICK_INVSTAT",conn);
			//dStoreInvstat = distComm.getDisparams("999999","DEEP_STORE_INVSTAT",conn);
			forInvState = checkNull(distComm.getDisparams("999999","FOR_INV_STAT",conn));
			siteCodeFromDisPa = distComm.getDisparams("999999","FOR_LOGIN_SITE",conn);
			System.out.println("nexpLoc ->["+nexpLoc+"] expLoc ->["+expLoc+"]");
			//Changed by sumit sarkar on 24/06/12 uncomment below code and change var_name in parameter. end.

			/*dataVal1 = genericUtility.getColumnValue("lock_code_exp",hdrDom);
			dataVal2 = genericUtility.getColumnValue("lock_code_nexp",hdrDom);
			siteCode = genericUtility.getColumnValue("site_code",hdrDom);
			*/
		
			if ( "T".equalsIgnoreCase(lockORTransfer.trim()) )//For Transger
			{
				dataVal1 = distComm.getDisparams("999999","NEAREXP_LOC",conn);
				dataVal2 = distComm.getDisparams("999999","EXP_LOC",conn);
			}
			else if ( "L".equalsIgnoreCase(lockORTransfer.trim()) )//For Locking
			{
				dataVal1 = distComm.getDisparams("999999","NEAR_EXP_LOCK",conn);
				dataVal2 = distComm.getDisparams("999999","EXP_LOCK",conn);
			}

			//Changed by sumit sarkar on 21/06/12 insert CHECK_PHYSCAN_VALUE function in sql start.
			/*selectSql = "SELECT A.ITEM_CODE, A.SITE_CODE, A.LOC_CODE, A.LOT_NO, A.LOT_SL, A.NO_ART, " +
						"C.ITEM_SER, A.QUANTITY, A.UNIT, A.GRADE, A.EXP_DATE, A.LTRAN_DATE,"+
						"A.REMARKS, D.INV_STAT, A.PACK_CODE, A.ALLOC_QTY, A.MFG_DATE," +
						"A.SITE_CODE__MFG, A.POTENCY_PERC, A.RATE, A.GROSS_RATE, A.RETEST_DATE,"+
						"A.CONV__QTY_STDUOM, A.UNIT__ALT, A.BATCH_NO, A.ACCT_CODE__INV,A.CCTR_CODE__INV "+
						"FROM STOCK A, INVSTAT B, ITEM C, LOCATION D "+
						"WHERE A.LOC_CODE = D.LOC_CODE AND D.INV_STAT = B.INV_STAT "+
						"AND A.ITEM_CODE = C.ITEM_CODE AND B.AVAILABLE = 'Y' "+
						"AND (CASE WHEN B.STAT_TYPE IS NULL THEN ' ' ELSE B.STAT_TYPE END) <> 'S' " +
						//"AND (A.QUANTITY - A.ALLOC_QTY - CASE WHEN A.HOLD_QTY IS NULL THEN 0 ELSE A.HOLD_QTY END) > 0 "+ //Commented by gulzar on 1/12/2012
						"AND (CASE WHEN A.QUANTITY IS NULL THEN 0 ELSE A.QUANTITY END) > 0 "+//Changed by gulzar on 1/12/2012
						"AND D.INV_STAT = ? AND A.SITE_CODE = ? AND A.EXP_DATE IS NOT NULL ";*/
			
			stringBuffer.append("(");
			if(forInvState == null || "".equalsIgnoreCase(forInvState.trim()))
			{				
				stringBuffer.append("''");
			}
			StringTokenizer st3 = new StringTokenizer(forInvState, ",");
			
			while (st3.hasMoreElements()) 
			{
				stringBuffer.append("'").append(st3.nextElement()).append("'");				
				if(st3.hasMoreElements())
				{
					stringBuffer.append(",");
				}
			}
			stringBuffer.append(")");
			
			System.out.println("Created input string --> "+stringBuffer);
			
			//SQL For expiry process 
			System.out.println(" Calling expiry process *************$$$$$$$$**********************");
			selectSql = "SELECT A.ITEM_CODE, A.SITE_CODE, A.LOC_CODE, A.LOT_NO, A.LOT_SL, A.NO_ART, " +
						"C.ITEM_SER, A.QUANTITY, A.UNIT, A.GRADE, A.EXP_DATE, A.LTRAN_DATE,"+
						"A.REMARKS, D.INV_STAT, A.PACK_CODE, A.ALLOC_QTY, A.MFG_DATE," +
						"A.SITE_CODE__MFG, A.POTENCY_PERC, A.RATE, A.GROSS_RATE, A.RETEST_DATE,"+
						"A.CONV__QTY_STDUOM, A.UNIT__ALT, A.BATCH_NO, A.ACCT_CODE__INV,A.CCTR_CODE__INV "+
						"FROM STOCK A, INVSTAT B, ITEM C, LOCATION D "+
						"WHERE A.LOC_CODE = D.LOC_CODE AND D.INV_STAT = B.INV_STAT "+
						"AND A.ITEM_CODE = C.ITEM_CODE AND B.AVAILABLE = 'Y' "+
						"AND (CASE WHEN B.STAT_TYPE IS NULL THEN ' ' ELSE B.STAT_TYPE END) <> 'S' " +
						//"AND (A.QUANTITY - A.ALLOC_QTY - CASE WHEN A.HOLD_QTY IS NULL THEN 0 ELSE A.HOLD_QTY END) > 0 "+ //Commented by gulzar on 1/12/2012
						"AND (CASE WHEN A.QUANTITY IS NULL THEN 0 ELSE A.QUANTITY END) > 0 "+//Changed by gulzar on 1/12/2012
						//Changed by sumit sarkar on 24/06/12 putting condition of dStoreInvstat (DSO, pso, active pick, case pick)
						//"AND D.INV_STAT = ? AND A.EXP_DATE IS NOT NULL "+
						" AND D.INV_STAT IN "+stringBuffer.toString()+" AND A.EXP_DATE IS NOT NULL "+
						//Changed by sumit sarkar on 03/07/12 
						" AND nvl(CHECK_PHYSCAN_VALUE(A.ITEM_CODE, A.SITE_CODE, A.LOC_CODE, A.LOT_NO, A.LOT_SL, ? , ?),0) <> 1 " +
						" AND A.EXP_DATE <= SYSDATE ";
			if( "YES".equalsIgnoreCase(siteCodeFromDisPa))
			{
				//taking site as a login site code 
				selectSql = selectSql + " AND A.SITE_CODE =  '"+siteCode+"'";
			}
			System.out.println(" selectSql ["+selectSql+"]");
			
			//Changed by sumit sarkar on 21/06/12 insert CHECK_PHYSCAN_VALUE function in sql end.
			
			pstmtStock = conn.prepareStatement(selectSql);
			//Changed by sumit sarkar on 22/06/12 to comment bellow parameter and add nexpLoc, expLoc parameter start.
			//pstmtStock.setString(1, reservLoc);			
			//pstmtStock.setString(2, siteCode);
			/*pstmtStock.setString(2, dStoreInvstat);
			pstmtStock.setString(3, casePickLoc);
			pstmtStock.setString(4, activePickLoc);	*/		
			pstmtStock.setString(1, expLoc);
			//Changed by sumit on 02/10/12 
			//pstmtStock.setString(2, nexpLoc);			
			pstmtStock.setString(2, expLoc);
			//Changed by sumit sarkar on 22/06/12 to comment bellow parameter and add nexpLoc, expLoc parameter end.
			rsStock = pstmtStock.executeQuery();

			InvHoldGen holdGen = new InvHoldGen();
			ArrayList stockExpList = new ArrayList();
			ArrayList stockNexpList = new ArrayList();
			ArrayList lockRelList = new ArrayList();
			java.sql.Timestamp currDate = new java.sql.Timestamp( System.currentTimeMillis() );
			java.sql.Timestamp expDateCalc = null;
			java.sql.Timestamp expDateStock = null;

			selectSql = "SELECT MIN_SHELF_LIFE FROM ITEM WHERE ITEM_CODE = ? ";
			pstmtItem = conn.prepareStatement(selectSql);

			while (rsStock.next())
			{
				HashMap dataMap = new HashMap();
				itemCode = rsStock.getString("ITEM_CODE");
				siteCode = rsStock.getString("SITE_CODE");
				locCode = rsStock.getString("LOC_CODE");
				lotNo = rsStock.getString("LOT_NO");
				lotSl = rsStock.getString("LOT_SL");
				expDateStock = rsStock.getTimestamp("EXP_DATE");
				quantity = rsStock.getDouble("QUANTITY");
				allocQty = rsStock.getDouble("ALLOC_QTY");
				noArt = rsStock.getDouble("NO_ART");
				acctCodeInv = rsStock.getString("ACCT_CODE__INV");
				cctrCodeInv = rsStock.getString("CCTR_CODE__INV");
				
				/*if ( currDate.compareTo(expDateStock) >= 0 )//Check for Expired stock
				{
					count++;
					dataMap.put( "item_code", itemCode );
					dataMap.put( "site_code", siteCode );
					dataMap.put( "loc_code", locCode );
					dataMap.put( "lot_no", lotNo );
					dataMap.put( "lot_sl", lotSl );
					dataMap.put( "loc_code__to", dataVal1 );//expired location to which stock will be transfer
					dataMap.put( "quantity", ""+quantity );
					dataMap.put( "alloc_qty", ""+allocQty );
					dataMap.put( "no_art", ""+noArt );
					dataMap.put( "acct_code__inv", acctCodeInv );
					dataMap.put( "cctr_code__inv", cctrCodeInv );
					stockExpList.add(dataMap);
				}
				else//Check for Near Expiry stock
				{
					pstmtItem.setString( 1, itemCode );
					rsItem = pstmtItem.executeQuery();
					if ( rsItem.next() )
					{
						minSelfLife = rsItem.getDouble("MIN_SHELF_LIFE");
					}
					pstmtItem.clearParameters();
					if ( minSelfLife == 0 )
					{
						continue;
					}
					expDateCalc = distComm.CalcExpiry( currDate, minSelfLife );
					
					if ( expDateCalc.compareTo(expDateStock) >= 0 )
					{
						count++;
						dataMap.put( "item_code", itemCode );
						dataMap.put( "site_code", siteCode );
						dataMap.put( "loc_code", locCode );
						dataMap.put( "lot_no", lotNo );
						dataMap.put( "lot_sl", lotSl );
						dataMap.put( "loc_code__to", dataVal2 );//near expiry location to which stock will be transfer
						dataMap.put( "quantity", ""+quantity );
						dataMap.put( "alloc_qty", ""+allocQty );
						dataMap.put( "no_art", ""+noArt );
						dataMap.put( "acct_code__inv", acctCodeInv );
						dataMap.put( "cctr_code__inv", cctrCodeInv );
						stockNexpList.add(dataMap);
						System.out.println("**********************"+stockNexpList);
					}
				}*/
				
				count++;
				dataMap.put( "item_code", itemCode );
				dataMap.put( "site_code", siteCode );
				dataMap.put( "loc_code", locCode );
				dataMap.put( "lot_no", lotNo );
				dataMap.put( "lot_sl", lotSl );
				dataMap.put( "loc_code__to", dataVal1 );//expired location to which stock will be transfer
				dataMap.put( "quantity", ""+quantity );
				dataMap.put( "alloc_qty", ""+allocQty );
				dataMap.put( "no_art", ""+noArt );
				dataMap.put( "acct_code__inv", acctCodeInv );
				dataMap.put( "cctr_code__inv", cctrCodeInv );
				stockExpList.add(dataMap);
				System.out.println("stockExpList**********************"+stockExpList);
				
				//Changed by sumit on 02/10/12 start
				System.out.println("..............check for lock..................");
				sql ="select D.HOLD_STATUS ,D.tran_id ,D.line_no from INV_HOLD H, INV_HOLD_DET D where H.tran_id= D.tran_id and H.lock_code =? and D.ITEM_CODE  =? and D.SITE_CODE= ? and D.LOC_CODE=?  and D.LOT_NO= ? "
					+"	and D.LOT_SL =? ";
				pstmt=conn.prepareStatement(sql);
				pstmt.setString(1, nexpLoc);
				pstmt.setString(2, itemCode);
				pstmt.setString(3, siteCode);
				pstmt.setString(4, locCode);
				pstmt.setString(5, lotNo);
				pstmt.setString(6, lotSl);
				rs=pstmt.executeQuery();
				if(rs.next())
				{
					holdStatus=rs.getString(1);
					if("H".equalsIgnoreCase(holdStatus))
					{
						HashMap lockRelMap = new HashMap();
						lockRelMap.put("tran_id", rs.getString("tran_id"));
						lockRelMap.put("line_no", rs.getString("line_no"));
						lockRelList.add(lockRelMap);
						System.out.println("lockRelList**********************"+lockRelList);
					}
				}
				pstmt.close();
				pstmt=null;
				rs.close();
				rs=null;
				//Changed by sumit on 02/10/12 end
				
			}			
			if ( rsStock != null)
			{
				rsStock.close();
				rsStock = null;
			}
			if ( pstmtStock != null )
			{
				pstmtStock.close();
				pstmtStock = null;
			}
			
			//Changed by sumit sarkar on 03/06/12  SQL For near expiry process start.
			System.out.println(" Calling near expiry *******************************");
			selectSql = "";
			selectSql = "SELECT A.ITEM_CODE, A.SITE_CODE, A.LOC_CODE, A.LOT_NO, A.LOT_SL, A.NO_ART, " +
						"C.ITEM_SER, A.QUANTITY, A.UNIT, A.GRADE, A.EXP_DATE, A.LTRAN_DATE,"+
						"A.REMARKS, D.INV_STAT, A.PACK_CODE, A.ALLOC_QTY, A.MFG_DATE," +
						"A.SITE_CODE__MFG, A.POTENCY_PERC, A.RATE, A.GROSS_RATE, A.RETEST_DATE,"+
						"A.CONV__QTY_STDUOM, A.UNIT__ALT, A.BATCH_NO, A.ACCT_CODE__INV,A.CCTR_CODE__INV "+
						"FROM STOCK A, INVSTAT B, ITEM C, LOCATION D "+
						"WHERE A.LOC_CODE = D.LOC_CODE AND D.INV_STAT = B.INV_STAT "+
						"AND A.ITEM_CODE = C.ITEM_CODE AND B.AVAILABLE = 'Y' "+
						"AND (CASE WHEN B.STAT_TYPE IS NULL THEN ' ' ELSE B.STAT_TYPE END) <> 'S' " +
						//"AND (A.QUANTITY - A.ALLOC_QTY - CASE WHEN A.HOLD_QTY IS NULL THEN 0 ELSE A.HOLD_QTY END) > 0 "+ //Commented by gulzar on 1/12/2012
						"AND (CASE WHEN A.QUANTITY IS NULL THEN 0 ELSE A.QUANTITY END) > 0 "+//Changed by gulzar on 1/12/2012
						//Changed by sumit sarkar on 24/06/12 putting condition of dStoreInvstat (DSO, pso, active pick, case pick)
						//"AND D.INV_STAT = ? AND A.EXP_DATE IS NOT NULL "+
						" AND D.INV_STAT IN "+stringBuffer.toString()+" AND A.EXP_DATE IS NOT NULL AND A.EXP_DATE > SYSDATE"+
						//Changed by sumit sarkar on 03/07/12  start
						" AND nvl(CHECK_PHYSCAN_VALUE(A.ITEM_CODE, A.SITE_CODE, A.LOC_CODE, A.LOT_NO, A.LOT_SL, ? , ?),0) <> 1 " +
						" AND A.EXP_DATE <= ADD_MONTHS(SYSDATE,C.MIN_SHELF_LIFE) ";
			if( "YES".equalsIgnoreCase(siteCodeFromDisPa))
			{
				//taking site as a login site code 
				selectSql = selectSql + " AND A.SITE_CODE =  '"+siteCode+"'";
			}
			// otherwise for all site code.
			System.out.println(" selectSql ["+selectSql+"]");
			
			pstmtStock = conn.prepareStatement(selectSql);
			/*pstmtStock.setString(1, reservLoc);			
			pstmtStock.setString(2, dStoreInvstat);
			pstmtStock.setString(3, casePickLoc);
			pstmtStock.setString(4, activePickLoc);*/
			//Changed by sumit on 02/10/12 setting parameter for nexp for both parameter
			//pstmtStock.setString(1, expLoc);
			pstmtStock.setString(1, nexpLoc);
			pstmtStock.setString(2, nexpLoc);			
			rsStock = pstmtStock.executeQuery();
			
			while (rsStock.next())
			{
				HashMap dataMap = new HashMap();
				itemCode = rsStock.getString("ITEM_CODE");
				siteCode = rsStock.getString("SITE_CODE");
				locCode = rsStock.getString("LOC_CODE");
				lotNo = rsStock.getString("LOT_NO");
				lotSl = rsStock.getString("LOT_SL");
				expDateStock = rsStock.getTimestamp("EXP_DATE");
				quantity = rsStock.getDouble("QUANTITY");
				allocQty = rsStock.getDouble("ALLOC_QTY");
				noArt = rsStock.getDouble("NO_ART");
				acctCodeInv = rsStock.getString("ACCT_CODE__INV");
				cctrCodeInv = rsStock.getString("CCTR_CODE__INV");
				
				count1++;
				dataMap.put( "item_code", itemCode );
				dataMap.put( "site_code", siteCode );
				dataMap.put( "loc_code", locCode );
				dataMap.put( "lot_no", lotNo );
				dataMap.put( "lot_sl", lotSl );
				dataMap.put( "loc_code__to", dataVal2 );//near expiry location to which stock will be transfer
				dataMap.put( "quantity", ""+quantity );
				dataMap.put( "alloc_qty", ""+allocQty );
				dataMap.put( "no_art", ""+noArt );
				dataMap.put( "acct_code__inv", acctCodeInv );
				dataMap.put( "cctr_code__inv", cctrCodeInv );
				stockNexpList.add(dataMap);
				System.out.println("stockNexpList **********************"+stockNexpList);
			}
			if ( rsStock != null)
			{
				rsStock.close();
				rsStock = null;
			}
			if ( pstmtStock != null )
			{
				pstmtStock.close();
				pstmtStock = null;
			}
			System.out.println("count = ["+count+"]");
			if ( !isError  )
			{
				//siteCode = genericUtility.getColumnValue("site_code",hdrDom);
				if ( "L".equalsIgnoreCase(lockORTransfer.trim()) ) //For Locking
				{
					//calling for Expiry lock
					if ( stockExpList.size() > 0 && count > 0)
					{
						System.out.println("Calling EXPIRED-------------------->");
						//Changed by Rohan on 3/7/12
						//retString = holdGen.generateHoldTrans( dataVal1, "EXPIRED","EXP", siteCode, stockExpList, "", conn );
						
						//changes by shamim start 
						//as per discussion with manoharan Sir :- genrate inv-Release transaction and confirm it
						if(lockRelList.size() >0)
						{
							System.out.println("going to insert in inv_hold__Rel ");
							retString=genInvRelTrans(lockRelList, scheduleParamXML , conn);
							System.out.println("retString" +retString);
							
						}
						if ( retString != null && retString.trim().length() > 0 && retString.trim().equalsIgnoreCase("ERROR") )
						{
							isError = true;
						}
						System.out.println("going to insert in inv_hold EXPIRED");
						
						retString = holdGen.generateHoldTrans( dataVal2, "EXPIRED","EXP", siteCode, stockExpList, "", conn );
					}
					if ( retString != null && retString.trim().length() > 0 && retString.trim().equalsIgnoreCase("ERROR") )
					{
						isError = true;
					}
					/*else 
					{
						//calling for Near Expiry lock
						if ( stockNexpList.size() > 0 )
						{
							retString = holdGen.generateHoldTrans( dataVal2, "NEAREXPIRY","NEXP", siteCode, stockNexpList, "", conn );
						}
						if ( retString != null && retString.trim().length() > 0 && retString.trim().equalsIgnoreCase("ERROR") )
						{
							isError = true;
						}
					}*/
					System.out.println("in count1 > 0 if loop");
					System.out.println("stockNexpList.size() ["+stockNexpList.size()+"] and count1 -> ["+count1+"]");
					if ( stockNexpList.size() > 0 && count1 > 0 )
					{
						System.out.println("going to insert in inv_hold for NEAREXPIRY----> ");
						//Changed by Rohan on 3/7/12
						//retString = holdGen.generateHoldTrans( dataVal2, "NEAREXPIRY","NEXP", siteCode, stockNexpList, "", conn );
						retString = holdGen.generateHoldTrans( dataVal1, "NEAREXPIRY","NEXP", siteCode, stockNexpList, "", conn );
					}
					if ( retString != null && retString.trim().length() > 0 && retString.trim().equalsIgnoreCase("ERROR") )
					{
						isError = true;
					}
					
				}
				else if ( "T".equalsIgnoreCase(lockORTransfer.trim()) ) //For Transfer
				{
					if ( stockExpList.size() > 0 && count > 0 )
					{
						retString = inventoryTransfer( stockExpList, "w_stock_transfer", "", conn );
					}
					if ( retString != null && retString.trim().length() > 0 && retString.trim().equalsIgnoreCase("ERROR") )
					{
						isError = true;
						//return retString;
					}
					if ( stockNexpList.size() > 0 && count1 > 0)
					{
						retString = inventoryTransfer( stockNexpList, "w_stock_transfer", "", conn );
					}
					if ( retString != null && retString.trim().length() > 0 && retString.trim().equalsIgnoreCase("ERROR") )
					{
						isError = true;
						//return retString;
					}
				}
			}
		}
		catch(SQLException se)
		{
			isError = true;
			System.out.println("Exception : NearExpiryPrc : process()" +se.getMessage());
			try
			{
				conn.rollback();
			}
			catch(Exception se1){}
			throw new ITMException(se);
		}
		catch(Exception e)
		{
			isError = true;
			System.out.println("Exception :NearExpiryPrc :process():"+e.getMessage());
			try
			{
				conn.rollback();
			}
			catch(Exception e1){}
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				invAllocTrace = null;
				//Changed by sumit on 05/07/12 change the condition.
				//if ( count == 0 )
				if ( count == 0 && count1 == 0)
				{
					retString = itmDBAccess.getErrorString("","NODATAERR","");
				}
				else if ( !isError )
				{
					System.out.println("COMMITING the changes......");
					conn.commit();
					retString = itmDBAccess.getErrorString("","PRCSUCCESS","");
				}
				else
				{
					System.out.println("ROLLBACK the changes......");
					conn.rollback();
					retString = itmDBAccess.getErrorString("","PRCFAILED","");
				}
				System.out.println("Closing Database Connection......");
				if (rsItem != null)
				{
					rsItem.close();
					rsItem = null;
				}
				if (pstmtItem != null)
				{
					pstmtItem.close();
					pstmtItem = null;
				}
				if (rsStock != null)
				{
					rsStock.close();
					rsStock = null;
				}
				if (pstmtStock != null)
				{
					pstmtStock.close();
					pstmtStock = null;
				}
				if ( conn != null && !conn.isClosed() )
				{
					conn.close();
					conn = null;
				}
			}catch(Exception se){}
		}
		System.out.println("retString........:: " + retString);
		return retString;  	
	}
	public String inventoryTransfer( ArrayList stockList, String windowName,String xtraParams, Connection conn ) throws RemoteException,ITMException
	{
		String salesPerson = "";
		String itemCode = "";
		String retString = "";
		String selectSql = "";
		String locCode = "";
		String siteCode = "";
		String lotNo = "";
		String lotSl = "";
		String locCodeTo = "";
		String reservLoc = "";
		String nexpLoc = "";
		String expLoc = "";
		String tranID = "";
		String loginSiteCode = "";
		String userId = "";
		String chgTerm = "";
		String acctCodeInv = "";
		String cctrCodeInv = "";
		String insertSql = "";
		String defReasCode = "";

		int count = 0;
		int updCnt = 0;
		int lineNo = 0;

		double quantity = 0d;
		double allocQty = 0d;
		double noArt = 0d;
		double minSelfLife = 0d;

		boolean isError = false;
		
		ResultSet rsStock = null;
		ResultSet rsItem = null;
		PreparedStatement pstmtStock = null;
		PreparedStatement pstmtItem = null;
		PreparedStatement pstmtHdr = null;
		PreparedStatement pstmtDet = null;

		HashMap strAllocate = null;

		GenericUtility genericUtility = GenericUtility.getInstance();

		ITMDBAccessEJB itmDBAccess = new ITMDBAccessEJB();
		InvAllocTraceBean invAllocTrace = new InvAllocTraceBean();

		try
		{
			DistCommon distComm = new DistCommon();
			defReasCode = distComm.getDisparams("999999","DEFAULT_REAS_CODE",conn);

			loginSiteCode = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginSiteCode" );
			userId = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginCode" );
			chgTerm = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "chgTerm" );

			java.sql.Timestamp currDate = new java.sql.Timestamp( System.currentTimeMillis() );
			java.sql.Timestamp expDateCalc = null;
			java.sql.Timestamp expDateStock = null;

			insertSql = "INSERT INTO STOCK_TRANSFER(TRAN_ID, TRAN_DATE, SITE_CODE, REF_SER__FOR, CONFIRMED, CHG_DATE, CHG_TERM, CHG_USER, SITE_CODE__FR, SITE_CODE__TO, REAS_CODE) VALUES(?,?,?,?,?,?,?,?,?,?,?)";
			pstmtHdr = conn.prepareStatement(insertSql);

			insertSql = "INSERT INTO STOCK_TRANSFER_DET(TRAN_ID, LINE_NO, ITEM_CODE, QUANTITY, LOC_CODE__FR, LOC_CODE__TO, LOT_NO__FR, LOT_NO__TO, LOT_SL__FR, LOT_SL__TO, ACCT_CODE__CR, ACCT_CODE__DR, CCTR_CODE__DR, CCTR_CODE__CR, NO_ART) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			pstmtDet = conn.prepareStatement(insertSql);
			
			//generate the tran id for w_stock_transfer
			tranID = generateTranId( "w_stock_transfer", conn );
			
			//Insert into header table i.e stock_transfer
			pstmtHdr.setString( 1, tranID );
			pstmtHdr.setTimestamp( 2, currDate );
			pstmtHdr.setString( 3, loginSiteCode );
			pstmtHdr.setString( 4, "XFRX" );
			pstmtHdr.setString( 5, "N" );
			pstmtHdr.setTimestamp( 6, currDate );
			pstmtHdr.setString( 7, chgTerm );
			pstmtHdr.setString( 8, userId );
			pstmtHdr.setString( 9, siteCode );
			pstmtHdr.setString( 10, siteCode );
			pstmtHdr.setString( 11, defReasCode );
			updCnt = pstmtHdr.executeUpdate();
			pstmtHdr.close();
			pstmtHdr = null;

			for ( int ctr = 0; ctr < stockList.size(); ctr++ )
			{
				lineNo++;
				HashMap dataMap = null;
				dataMap = (HashMap)stockList.get(ctr);
				
				itemCode = "";
				siteCode = "";
				locCode = "";
				lotNo = "";
				lotSl = "";
				acctCodeInv = "";
				cctrCodeInv = "";
				locCodeTo = "";

				quantity = 0d;
				allocQty = 0d;
				noArt = 0d;
				
				if ( dataMap.get("item_code") != null )
				{
					itemCode = (String)dataMap.get("item_code");
				}
				if ( dataMap.get("site_code") != null )
				{
					siteCode = (String)dataMap.get("site_code");
				}
				if ( dataMap.get("loc_code") != null )
				{
					locCode = (String)dataMap.get("loc_code");
				}
				if ( dataMap.get("loc_code__to") != null )
				{
					locCodeTo = (String)dataMap.get("loc_code__to");
				}
				if ( dataMap.get("lot_no") != null )
				{
					lotNo = (String)dataMap.get("lot_no");
				}
				if ( dataMap.get("lot_sl") != null )
				{
					lotSl = (String)dataMap.get("lot_sl");
				}
				if ( dataMap.get("quantity") != null )
				{
					quantity = Double.parseDouble(dataMap.get("quantity").toString());
				}
				if ( dataMap.get("alloc_qty") != null )
				{
					allocQty = Double.parseDouble(dataMap.get("alloc_qty").toString());
				}
				if ( dataMap.get("no_art") != null )
				{
					noArt = Double.parseDouble(dataMap.get("no_art").toString());
				}
				if ( dataMap.get("acct_code__inv") != null )
				{
					acctCodeInv = (String)dataMap.get("acct_code__inv");
				}
				if ( dataMap.get("cctr_code__inv") != null )
				{
					cctrCodeInv = (String)dataMap.get("cctr_code__inv");
				}
				pstmtDet.setString( 1, tranID );
				pstmtDet.setInt( 2, lineNo );
				pstmtDet.setString( 3, itemCode );
				pstmtDet.setDouble( 4, (quantity - allocQty) );
				pstmtDet.setString( 5, locCode );
				pstmtDet.setString( 6, locCodeTo );//expired / Near Expiry location to which stock will be transfer
				pstmtDet.setString( 7, lotNo );
				pstmtDet.setString( 8, lotNo );
				pstmtDet.setString( 9, lotSl );
				pstmtDet.setString( 10, lotSl );
				pstmtDet.setString( 11, acctCodeInv );
				pstmtDet.setString( 12, acctCodeInv );
				pstmtDet.setString( 13, cctrCodeInv );
				pstmtDet.setString( 14, cctrCodeInv );
				pstmtDet.setDouble( 15, noArt );

				pstmtDet.addBatch();
				pstmtDet.clearParameters();
				dataMap.clear();

				strAllocate = new HashMap();

				strAllocate.put("tran_date",currDate);
				strAllocate.put("ref_ser","XFRX");
				strAllocate.put("ref_id",tranID);
				strAllocate.put("ref_line", "" + lineNo);
				strAllocate.put("site_code",siteCode);
				strAllocate.put("item_code",itemCode);
				strAllocate.put("loc_code",locCode);
				strAllocate.put("lot_no",lotNo);
				strAllocate.put("lot_sl",lotSl);
				strAllocate.put("alloc_qty",new Double(quantity));
				strAllocate.put("chg_user",userId);
				strAllocate.put("chg_term",chgTerm);
				strAllocate.put("chg_win","w_stock_transfer");

				retString = invAllocTrace.updateInvallocTrace(strAllocate, conn);
				strAllocate = null;
				System.out.println("retString ::: " + retString);
				if (retString != null && retString.trim().length() > 0 )
				{
					isError = true;
					retString = "ERROR";
					return retString;
				}
			}
			pstmtDet.executeBatch();
			pstmtDet.clearBatch();
		}
		catch(BatchUpdateException buex)
        {
			isError = true;
			retString = "ERROR";
			int [] updateCounts = buex.getUpdateCounts();
			for (int i = 0; i < updateCounts.length; i++)
			{
				System.err.println("  Statement " + i + ":" + updateCounts[i]);
			}
			System.err.println(" Message: " + buex.getMessage());
			System.err.println(" SQLSTATE: " + buex.getSQLState());
			System.err.println(" Error code: " + buex.getErrorCode());
			SQLException ex = buex.getNextException();
			while (ex != null)
			{ 
				System.err.println("SQL exception:");
				System.err.println(" Message: " + ex.getMessage());
				System.err.println(" SQLSTATE: " + ex.getSQLState());
				System.err.println(" Error code: " + ex.getErrorCode());
				ex = ex.getNextException();
			}
        }
		catch(SQLException se)
		{
			isError = true;
			retString = "ERROR";
			System.out.println("Exception : NearExpiryPrc : process()" +se.getMessage());
			try
			{
				conn.rollback();
			}
			catch(Exception se1){}
			//throw new ITMException(se);
		}
		catch(Exception e)
		{
			isError = true;
			retString = "ERROR";
			System.out.println("Exception :NearExpiryPrc :process():"+e.getMessage());
			try
			{
				conn.rollback();
			}
			catch(Exception e1){}
			//throw new ITMException(e);
		}
		finally
		{
			try
			{
				invAllocTrace = null;
				strAllocate = null;
				if ( isError )
				{
					System.out.println("ROLLBACK the changes......");
					conn.rollback();
					retString = "ERROR";
				}
				System.out.println("Closing Database Connection......");
				if (rsItem != null)
				{
					rsItem.close();
					rsItem = null;
				}
				if (pstmtItem != null)
				{
					pstmtItem.close();
					pstmtItem = null;
				}
				if (rsStock != null)
				{
					rsStock.close();
					rsStock = null;
				}
				if (pstmtStock != null)
				{
					pstmtStock.close();
					pstmtStock = null;
				}
				if (pstmtHdr != null)
				{
					pstmtHdr.close();
					pstmtHdr = null;
				}
				if (pstmtDet != null)
				{
					pstmtDet.close();
					pstmtDet = null;
				}

			}catch(Exception se){}
		}
		System.out.println("retString........:: " + retString);
		return retString;  	
	}
	private String generateTranId( String windowName, 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.Date effDate = null;

		 try
         {
			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 + "</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 Connection getConnection()throws BaseException
	{
		Connection con = null;
		try
		{
			ConnDriver connDriver = new ConnDriver();
			con = connDriver.getConnectDB(CommonConstants.WRKFLW_DB);
		}
		catch(BaseException be)
		{
			throw be;
		}
		catch (Exception e)
		{
			throw new BaseException(e);
		}
		return con;
	}
	
	// Changed by sumit sarkar on 04/07/12 checking null start.
	private String checkNull(String str)
	{
		if(str == null)
		{
			str = "";
		}
		return str;
	}
	// Changed by sumit sarkar on 04/07/12 checking null end.

	
	/*
	public String insertSchedule1() throws RemoteException,ITMException
	{
		String errString = "";
		PreparedStatement pstmt = null;
	    Connection conn = null;
	    String tranId = "";
	    String sSQL = "";
	    String siteCode = "";
	    String dateFrom = "",dateTo = "", invStat = "";
	    String confirmed = "N";
	    String itemCode = "",locCode = "",lotNo = "",lotSl = "", prioritySeq = "", priorityDate = "" ;
	    String childNodeName = "";
	    String analysisClass = ""; 

		boolean isError = false;

	    int parentNodeListLength = 0;
	    int lineNo = 0;

	    java.sql.Date currDate = new java.sql.Date( System.currentTimeMillis() );

	    NodeList parentNodeList = null, childNodeList  = null;
	    Node parentNode = null, childNode = null;

	    ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();

		try
		{
			/*chgUser = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginCode");
			chgTerm = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "termId");			
			ConnDriver connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			parentNodeList = detailDom.getElementsByTagName("Detail2");
			parentNodeListLength = parentNodeList.getLength(); 
			
			siteCode = genericUtility.getColumnValue("site_code", headerDom);
			sSQL =  "INSERT INTO ITEM_PROCESS (TRAN_DATE, ITEM_CODE, " +
					"analysis_class, LOC_CODE,SITE_CODE,CHG_USER, CHG_TERM, CHG_DATE,priority_seq) VALUES (?,?,?,?,?,?,?,?,?)";
			pstmt = conn.prepareStatement(sSQL);
			for(int row = 0; row < parentNodeList.getLength(); row++)
			{
				parentNode = parentNodeList.item(row);
				childNodeList = parentNode.getChildNodes();
				
				itemCode = "";
				locCode = "";
				lotNo = "";
				lotSl = "";
				itemCode = "";
				locCode = "";
				lotNo = "";
				lotSl = "";
				
				
				System.out.println("************* first For loop *************["+parentNode.getNodeName()+"]");
				System.out.println("************* first For loop *************["+childNodeList.toString()+"]");
				System.out.println("childNodeList.getLength()=="+childNodeList.getLength());
				for(int col = 0; col < childNodeList.getLength(); col++)
				{
					childNode = childNodeList.item(col);
					
					if(childNode != null && childNode.getNodeType() == childNode.ELEMENT_NODE  )
					{
						childNodeName = childNode.getNodeName();
						
						if("priority_seq".equalsIgnoreCase(childNodeName))
						{
							prioritySeq = childNode.getFirstChild().getNodeValue();
							prioritySeq = prioritySeq != null ? prioritySeq : "";
							System.out.println("prioritySeq-->"+prioritySeq);							
						}
						
						else if("analysis_class".equalsIgnoreCase(childNodeName))
						{
							analysisClass = childNode.getFirstChild().getNodeValue();
							
							analysisClass = analysisClass != null ? analysisClass : "";
							System.out.println("*********analysisClass-->"+analysisClass);
						}
						else if("item_code".equalsIgnoreCase(childNodeName))
						{
							
							itemCode = childNode.getFirstChild().getNodeValue();
							itemCode = itemCode != null ? itemCode : "";
							
							System.out.println("itemCode-->["+itemCode+"]");
						}
						
						else if("site_code".equalsIgnoreCase(childNodeName))
						{
							siteCode ="";
							if (childNode.getFirstChild() != null)
							{
								siteCode = childNode.getFirstChild().getNodeValue();
							}
							System.out.println("siteCode-->["+siteCode+"]");
						}
						
						else if("loc_code".equalsIgnoreCase(childNodeName))
						{
							System.out.println("ENTER IN LOC CODE");
							locCode = "";
							if (childNode.getFirstChild() != null)
							{
								locCode = childNode.getFirstChild().getNodeValue();
							}
							
							System.out.println("locCode-->["+locCode+"]");
						}
						
						
					}
										
				}
				pstmt.setDate(1,currDate);
				pstmt.setString(2,itemCode);
				pstmt.setString(3,analysisClass);
				pstmt.setString(4,locCode);
				pstmt.setString(5,siteCode);
				pstmt.setString(6,chgUser);
				pstmt.setString(7,chgTerm);
				pstmt.setDate(8,currDate);
				pstmt.setString(9,prioritySeq);
				pstmt.executeUpdate();
				pstmt.clearParameters();
				
				System.out.println("Insertion Successfull in table PHY_CNT_SCH_DET table -->");
				
			 }
			
		}
		//Change by Rohan 05/04/12 [WM1FSUN005] To remove feilds on header window.end
		catch(SQLException se)
		{
			isError = true;
			System.out.println("SQLException :" + se);
			se.printStackTrace();
			errString = se.getMessage();
			return errString;
		}
		catch(Exception e)
		{
			isError = true;
			System.out.println("Exception :" + e);
			errString = e.getMessage();
			e.printStackTrace();
			return errString ;
			
			
		}
		finally
		{
			try
			{
				if(conn != null)
				{
					if ( !isError )
					{
						System.out.println("COMMITING the changes......");
						conn.commit();
						errString = itmDBAccessEJB.getErrorString("","PRCSUCCESS","");
					}
					else
					{
						System.out.println("ROLLBACK the changes......");
						conn.rollback();
						errString = itmDBAccessEJB.getErrorString("","PRCFAILED","");
					}
					if( pstmt != null )
					{
						pstmt.close();
						pstmt = null;
					}
					conn.close();
					conn = null;
				}
			}
			catch(Exception e)
			{
				errString = e.getMessage();
				e.printStackTrace();
				return errString ;
				
			}
			
		}
		return errString;
		
	}
	/*	
	//Changed by Rohan 04/04/12 To get list of holiday.start
	private java.util.ArrayList getHolidayList( Connection conn, String employeeCode,java.sql.Date dateFrom, int daysInMonth ) 
	{
		System.out.println("Getting Holidays Information ..... ");
		
		java.sql.PreparedStatement pstmtHolday = null;
		java.sql.ResultSet rsetHolday = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
	
		java.util.ArrayList holidayArray = new java.util.ArrayList();
		java.util.Calendar calendar = java.util.Calendar.getInstance();		
		
		try
		{
			String periodCode = "";
			String formatWorkDate = "";
			String holidayDate = "";
			String prdCodeSql = "SELECT CODE FROM PERIOD WHERE FR_DATE = ?";

			pstmtHolday = conn.prepareStatement( prdCodeSql );
			pstmtHolday.setDate(1, dateFrom );
			rsetHolday = pstmtHolday.executeQuery();
			if ( rsetHolday.next() )
			{
				periodCode = rsetHolday.getString("CODE");
			}

			java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat( genericUtility.getApplDateFormat() );
			calendar.setTimeInMillis( dateFrom.getTime() );

			for (int count=0; count!=daysInMonth; count++ )
			{
				//System.out.println("ENTER");
				calendar.setTimeInMillis( calendar.getTimeInMillis() );
				formatWorkDate = sdf.format( calendar.getTime() );
				java.sql.Timestamp  workDate = java.sql.Timestamp.valueOf(genericUtility.getValidDateTimeString(formatWorkDate,genericUtility.getApplDateFormat(),genericUtility.getDBDateFormat()));
                System.out.println("WORK DATES iN TIMESTAMP FORMA"+workDate);
				String getHolidaySQL = "SELECT DDF_GET_EMP_HOLIDAY( ?, ?, ?, ? )AS HOL_DATE FROM DUAL";
				pstmtHolday = conn.prepareStatement( getHolidaySQL );
				pstmtHolday.setString(1, employeeCode );
				pstmtHolday.setString(2, periodCode );
				pstmtHolday.setString(3, periodCode );
				pstmtHolday.setTimestamp(4, workDate );

				rsetHolday = pstmtHolday.executeQuery();

				if( rsetHolday.next() )
				{
					holidayDate = checkNull(rsetHolday.getString("HOL_DATE"));
					if( holidayDate.trim().length() > 0 )
					{
						System.out.println("ENTER IN  if( holidayDate.trim().length() > 0 )");
						//Changed By Sumit Sakore On 24/02/2009 [ This DateFormat will be there As per Nilesh Parikh ]
						//holidayArray.add( genericUtility.getValidDateString(holidayDate, genericUtility.getDBDateFormat(), genericUtility.getApplDateFormat()).toUpperCase() ) ;
						holidayArray.add( genericUtility.getValidDateString(holidayDate, "dd-MMM-yyyy", genericUtility.getApplDateFormat()).toUpperCase() ) ;
					}					
				}
				calendar.add( calendar.DATE, 1 );
			}
			     if(pstmtHolday != null)//added by darshan for ejb 3
				{
					pstmtHolday.close();
				}
				if (rsetHolday !=null )//added by darshan for ejb 3
				{
					rsetHolday.close();
				}
			System.out.println("PhysicalCountPrcEJB :: getHolidayList :: holidayArray ==> ["+holidayArray+"]");
		}
		catch ( Exception eHolday )
		{
			System.out.println("ExpenseProcessEJB : getHolidayList => ");
			eHolday.printStackTrace();
		}
		finally
		{
			try
			{
				if(pstmtHolday != null)
				{
					pstmtHolday.close();
				}
				if (rsetHolday !=null )
				{
					rsetHolday.close();
				}
			}
			catch(Exception closeRsc )
			{
				closeRsc.printStackTrace();
			}
		}
		return holidayArray;
	}
	//Changed by Rohan 04/04/12 To get list of holiday.end
//Changed By Rohan Sakore On 04/04/12 to check null value.start
private String checkNull(String input)
{
	if (input==null)
	{
		input="";
	}
	return input;
}*/
//Changed By Rohan Sakore On 04/04/12 to check null value.end

	//Changed by sumit on 02/10/12 modified code as per process start.
	private String  genInvRelTrans(ArrayList nexpLoc,String xtraParams ,Connection conn) throws BaseException
	{
//		String invHoldRelSql="",invHoldRelDetSql="";
//		PreparedStatement pRelHdr=null ,pRelDet=null;
//		java.sql.Timestamp currDate =null;
//		Date date =null;
//		String userId="";
//		GenericUtility genericUtility =GenericUtility.getInstance(); 
		String tranIdRel = "";
		String lineNoRel = "";
		String itemCode = "";
		String lotNo = "";
		String sql = "";
		String invHoldRelSql = "";
		String invHoldRelDetSql = "";
		String siteCode = "";
		String confirmed = "";
		String empCode = "";
		String remark = "";
		String itemCodeDet = "";
		String lotNoDet = "";
		int lineNo = 0;
		String tranIdGenerate = "";
		String errString="";

		boolean flag = false;

		int count = 0;
		int updCnt = 0;
//		int ctr = 0;

		java.sql.Timestamp chgDate = null,currDate = null;
		Date date = null;
		String chgTerm = "";
		String userId = "";
		String lotStatus = "";
		int []batchUpd =null;

		Document dom = null;

		PreparedStatement pStmt = null;
		PreparedStatement pRelHdr = null;
		PreparedStatement pRelDet = null;
		ResultSet rs = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		ITMDBAccessEJB itmDBAccessLocal = new ITMDBAccessEJB();
		
		try
		{
			ibase.utility.UserInfoBean userInfo = new UserInfoBean(xtraParams);
			invHoldRelSql = "INSERT INTO INV_HOLD_REL(TRAN_ID, TRAN_DATE, SITE_CODE, REMARKS, CONFIRMED, EMP_CODE__APRV,CHG_USER ,CHG_DATE, CHG_TERM) VALUES(?,?,?,?,?,?,?,?,?)";
			pRelHdr = conn.prepareStatement(invHoldRelSql);
			invHoldRelDetSql = "INSERT INTO INV_HOLD_REL_DET(TRAN_ID, LINE_NO, TRAN_ID__HOLD, LINE_NO__HOLD, REMARKS) VALUES(?,?,?,?,?)";
			pRelDet = conn.prepareStatement(invHoldRelDetSql);

			currDate = new java.sql.Timestamp(System.currentTimeMillis());
			SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getDBDateFormat());
			date = sdf.parse(currDate.toString());
			/*userId = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginCode");
			chgTerm = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "chgTerm" );
			siteCode =  genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginSiteCode");*/
			
			userInfo = new ibase.utility.UserInfoBean( xtraParams );
			siteCode = userInfo.getSiteCode(); 
			userId = userInfo.getLoginCode();
			chgTerm = userId;			
			System.out.println(" --- new site code ["+siteCode+"] user id ["+userId+"] chg term ["+chgTerm+"]");
			confirmed = "N";
			empCode = "";

			SimpleDateFormat sdfAppl = new SimpleDateFormat(genericUtility.getApplDateFormat());
			String currDateStr = sdfAppl.format(currDate);

			tranIdGenerate = generateTranId( "w_inv_hold_rel", siteCode, currDateStr, conn );

			pRelHdr.setString( 1, tranIdGenerate );
			pRelHdr.setTimestamp( 2, currDate );
			pRelHdr.setString( 3, siteCode );
			pRelHdr.setString( 4, " " );
			pRelHdr.setString( 5, confirmed );
			pRelHdr.setString( 6, empCode );
			pRelHdr.setString( 7, userId );
			pRelHdr.setTimestamp( 8, currDate );
			pRelHdr.setString( 9, chgTerm );

			updCnt = pRelHdr.executeUpdate();
			pRelHdr.clearParameters();
			if( updCnt > 0 )
			{					
				System.out.println( updCnt + " rows updated successfully FOR "+tranIdGenerate );
			}
			//Changed by sumit on 17/12/12 closing statement
			if(pRelHdr != null)
			{
				pRelHdr.close();
				pRelHdr = null;
			}
			//Changed by sumit on 17/12/12 closing statement
			//making details
			HashMap dataMap = new HashMap();

			for ( int ctr = 0; ctr < nexpLoc.size(); ctr++ )
			{
				lineNo++;
				dataMap = (HashMap)nexpLoc.get(ctr);
				
				
				pRelDet.setString( 1, tranIdGenerate );
				pRelDet.setInt( 2,lineNo);
				pRelDet.setString( 3, (String)dataMap.get("tran_id"));
				pRelDet.setString( 4, (String)dataMap.get("line_no") );
				pRelDet.setString( 5, remark );
				pRelDet.addBatch();

				dataMap.clear(); 
			}
			batchUpd = pRelDet.executeBatch();
			System.out.println("nos of row updated "+batchUpd);
			//Changed by sumit on 17/12/12 closing statement
			if(pRelDet != null)
			{
				pRelDet.close();
				pRelDet = null;
			}
			//Changed by sumit on 17/12/12 closing statement
//			if(batchUpd.length >0)
//				conn.commit();
			InvHoldRelConf invHoldRelConf = new InvHoldRelConf();			
			//errString = invHoldRelConf.confirm( tranIdGenerate, xtraParams, "",conn,false);
			errString = invHoldRelConf.confirm( tranIdGenerate, "", "",conn,false);
			if (errString.indexOf("VTCNFSUCC") > -1)
			{
				errString = "";
			}
			
		}catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if(pRelHdr != null)
				{
					pRelHdr.close();
					pRelHdr = null;
				}
				if(pRelDet != null)
				{
					pRelDet.close();
					pRelDet = null;
				}
			}
			catch(Exception e)
			{ 
				throw new ITMException(e); 
			}
		}
		return errString;
	}
	
	private String generateTranId( String windowName, String siteCode, String tranDateStr, 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.Date effDate = null;

		try
		{
			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>" + tranDateStr + "</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()
	
	//Changed by sumit on 02/10/12 modified code as per process end
}