/**
s* PURPOSE : To cancle wave task.
* AUTHOR : Changed by Rohan
*/ 

package ibase.webitm.ejb.wms;

import ibase.system.config.AppConnectParm;
import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.ActionHandlerEJB;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.ejb.dis.DistCommon;
import ibase.webitm.ejb.dis.DistStkUpdLocal;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;

import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;

import javax.ejb.Stateless;
import javax.naming.InitialContext;

@Stateless 
public class WaveCancelEJB extends ActionHandlerEJB implements WaveCancelEJBLocal, WaveCancelEJBRemote 
{
	HashMap<String,String> replHashMap = new HashMap<String,String>();
    /**
	 * The public method is used for confirming the service complaint transaction
	 * Returns confirmation message on successfull confirm otherwise returns error message
	 * @param tranID is the transaction id to be confirmed
	 * @param xtraParams contais additional information such as loginEmpCode,loginCode,chgTerm etc
	 * @param forcedFlag (true or false)
	 */
	public String confirm( String waveID, String xtraParams, String forcedFlag ) throws RemoteException,ITMException
	{
		String retString = "";
		Connection conn = null;	
		boolean isConn= false;
		
		try
		{
			retString = this.confirm( waveID,xtraParams, forcedFlag ,conn ,isConn);
			if ( retString != null && retString.length() > 0 )
			{
				throw new Exception("Exception while calling confirm for wave  Id:["+waveID+"]");
			}
		}
		catch( Exception exception )
		{
			System.out.println("Exception in [WaveHoldEJB] getCurrdateAppFormat " + exception.getMessage());
		}
		return retString;
	}
	public String confirm(String waveID, String xtraParams, String forcedFlag, Connection conn, boolean connStatus) throws RemoteException, ITMException
	{
		
		String sql = "";		
		String retString = "";
		
		//Changed by Rohan on 21-09-12 for wave generation reversal.start
		String refId = "";
		String refSer = "";
		String pickDet = "";
		String itemCode = "";
		String lineNo = "";
		String locCode= "";
		String lotNo = "";
		String lotSl = "";
		double quantity = 0.0;
		String expLev = "";
		String saleOrder = "";
		String lineNoSord = "";
		//double qtyStduom = 0.0;
		//double qtyAlloc = 0.0;
		PreparedStatement pstmt = null;
		Statement stmt = null,stmt1 = null;
		
		String updateStatus = "";
		String sqlRepl = "";
		String replOrd = "";
		String lineNorepl = "";
		String sqlPick = "";
		String sqlItem = "";
		String sqlPickUpt = "";
		String invStat = "";
		String casePickLoc = "";
		String resrvLoc = "";
		String sqlReplOrd1 = "";
		String sqlSordAlloc = "", sqlSord = "", sqlSordItem = "", line = "";
		DistCommon discommon = new DistCommon();
		double qtyAlloc = 0d, qtyStduom = 0d;
		int updateCnt = 0, retVal = 0, count =0;
		HashMap strAllocate = new HashMap();
		DistStkUpdLocal distStkUpd = null;
		String sqlReplCase = "";
		String itemCodeOrd = "";
		String siteCode = "";
		java.sql.Date tranDate = null;
		double caseSize = 0d;
		String sqlReplUpt = "";
		double domQty = 0.0;
		ResultSet  rs1 = null;
		PreparedStatement pstmtWave = null;
		ResultSet rsWave = null;
		String status = "";

		//PreparedStatement pstmtRepl = null;
		//ResultSet rsRepl = null;
		//String sqlReplOrd = "";
		String replOrder = "";
		String replLineNo = "";
		String waveCancel = "";
		String sqlUpdate = "";
		String pickOrder = "";
		String pickLineNo = "";
		String sqlpickDet = "";
		String locCodeTo = "";
		String locCode1 = "";
		String dockLoc = "";
		//String sqlreplDet = "";
		//Changed by Rohan on 21-09-12 for wave generation reversal.end
		//Changed by Rohan 10-04-12 to define variables.start
		String invState = "";
		String pickOrd = "";
		String lineNoPick = "";
		String stagingLocMpick = "";
		//Changed by Rohan on 10-04-12 to define variables.end
		
		//Changed by Rohan on 05-10-12 to define variable.start
		String confirm = "";
		String transferStatus = "";
		String stagingLoc = "";
		String replPickOrder = "";
		String replPickLineNo = "";
		String locCodeRepl = "";
		String confirmRepl = "";
		String lotSlNew = "";
		String waveId = "";
		String ptcn = "";
		String sqlDesp = "";
		String replDet = "";
		String dsoInvstat = "";
		int cnt = 0;
		int updateRecord = 0;
		String sqlInv = "";
		String replOrderDso = "";
		String replLineNoDso = "";
		String replOrderAct = "";
		String replPickLineNoAct = "";
		String pickOrdRepl = "";
		String lineNoRepl = "";
		String sqlWave = "";
		String refSeries = "";
		//Changed by Rohan on 22-01-13 to cancle independent Repanishment.start
		String replOrdOrg = "";
		String replLineOrg = "";
		//Changed by Rohan on 22-01-13 to cancle independent Repanishment.end
		PreparedStatement pstmtTask = null;
		ResultSet rsTask = null;
		PreparedStatement pstmtInv = null;
		ResultSet rsInv = null;
		
		
		PreparedStatement pstmtDesp = null;
		PreparedStatement pstmtReplDet = null;
		
		PreparedStatement pstmtPickIss = null;
		ResultSet rsPickIss = null;
		
		ResultSet rsDesp = null;
		boolean isDespatch = true;
		//Changed by Rohan on 10-05-12 to define variable.end
		
		boolean isError = false;
		
		int updCnt = 0;
		PreparedStatement pstmtUpd = null;
		PreparedStatement pstmtSql = null;
		ResultSet rs = null;
		ResultSet rsPick = null;
		
		ITMDBAccessEJB itmDBAccess = new ITMDBAccessEJB();
		
		try
		{
			
			if ( conn == null )
			{
				ConnDriver connDriver = new ConnDriver();
				conn = connDriver.getConnectDB("DriverITM");
				connDriver = null;
				conn.setAutoCommit(false);
				connStatus = true;
			}
			
			
			
			dockLoc = discommon.getDisparams("999999","DOCKLOC_INVSTAT",conn);
			//Changed by Rohan on 10-05-12 to get disparm variable
			stagingLoc = discommon.getDisparams( "999999", "STAGING_LOC", conn );
			stagingLocMpick = discommon.getDisparams( "999999", "STAGING_LOC_MPK", conn );
			
			dsoInvstat = discommon.getDisparams( "999999", "DEEP_STORE_INVSTAT", conn );
			
			GenericUtility genericUtility = GenericUtility.getInstance();
			java.sql.Timestamp currDate = new java.sql.Timestamp( System.currentTimeMillis() );
			distStkUpd = getComponent("DistStkUpd");
			
			//Changed by Rohan on 21-09-12 for wave generation reversal.start
			siteCode = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginSiteCode");
			tranDate = new java.sql.Date(System.currentTimeMillis());
			
			sql = "SELECT CANCEL FROM WAVE_TASK "
				+ " WHERE WAVE_ID = ? ";
			pstmtSql = conn.prepareStatement(sql);
			pstmtSql.setString(1, waveID);
			rs = pstmtSql.executeQuery();
			
			if( rs.next() )
			{
				
				waveCancel = checkNull( rs.getString("CANCEL") );
				if(waveCancel.equalsIgnoreCase("Y"))
				{
					retString = itmDBAccess.getErrorString("","WAVECANCEL","");
					return retString;
					
				}
			}
			
			rs.close();
			rs = null;
			pstmtSql.close();
			pstmtSql = null;
			replHashMap.clear();
			
			sqlDesp = "SELECT COUNT(*) AS COUNT FROM DESPATCHDET WHERE DESP_ID IN(SELECT REF_ID FROM WAVE_TASK_DET WHERE WAVE_ID = ? AND REF_SER = ?)";
			
			sql = "SELECT REF_SER,REF_ID,PTCN,WAVE_ID FROM WAVE_TASK_DET WHERE WAVE_ID = ?";
			pstmtWave = conn.prepareStatement(sql);
			pstmtWave.setString(1, waveID);
			
			rsWave = pstmtWave.executeQuery();
			while( rsWave.next() )
			{
				
				//if despachdet generate then not allow to cancle wave.start 
				waveId = checkNull(rsWave.getString("WAVE_ID"));
				if(isDespatch)
				{	
				
					pstmtDesp = conn.prepareStatement(sqlDesp);
					pstmtDesp.setString(1,waveId);
					pstmtDesp.setString(2,"S-DSP");
					rsDesp = pstmtDesp.executeQuery();
					if(rsDesp.next())
					{
						cnt = rsDesp.getInt("COUNT");
					}
					if(cnt > 0)
					{
						retString = itmDBAccess.getErrorString("","NTWAVECAN","");
						return retString;
					}
					else
					{
						isDespatch = false;
					}
				
					if(pstmtDesp != null)
					{
						pstmtDesp.close();
						pstmtDesp = null;
					}
					if(rsDesp != null)
					{
						rsDesp.close();
						rsDesp = null;
					}
				}	
				//if despachdet generate then not allow to cancle wave.start 
				refSer = checkNull( rsWave.getString("REF_SER".trim()));
				refId = checkNull( rsWave.getString("REF_ID") );
				
				System.out.println("Curren refser:"+refSer+"REF_ID"+refId);
				
				//if(refSer.equalsIgnoreCase("C-PICK") || refSer.equalsIgnoreCase("M-PICK") || refSer.equalsIgnoreCase("A-PICK") || refSer.trim().equalsIgnoreCase("S-DOC") || refSer.equalsIgnoreCase("RP-PND") || refSer.equalsIgnoreCase("RP-PSO"))
				if(refSer.equalsIgnoreCase("C-PICK") || refSer.equalsIgnoreCase("M-PICK") || refSer.equalsIgnoreCase("A-PICK") || refSer.equalsIgnoreCase("P-PICK") || refSer.trim().equalsIgnoreCase("S-DOC") || refSer.equalsIgnoreCase("RP-PND") || refSer.equalsIgnoreCase("RP-PSO"))
				{
					if(refSer.trim().equalsIgnoreCase("S-DOC") || refSer.equalsIgnoreCase("RP-PND") || refSer.equalsIgnoreCase("RP-PSO"))
					{
						
						//for stock to dock
						replDet ="SELECT ITEM_CODE,LINE_NO,LOC_CODE,LOT_NO,LOT_SL,QUANTITY,"
								+" EXP_LEV,SALE_ORDER,LINE_NO__SORD,LOC_CODE__TO,REPL_ORDER,LINE_NO " 
								+" FROM REPL_ORD_DET WHERE REPL_ORDER = ?";
						pstmtSql = conn.prepareStatement(replDet);
					}
					else
					{	
						//for active,master,case picking
						pickDet ="SELECT ITEM_CODE,LINE_NO,LOC_CODE,LOT_NO,LOT_SL,QUANTITY," +
							 "EXP_LEV,SALE_ORDER,LINE_NO__SORD,REPL_ORDER,LINE_NO__REPL,PICK_ORDER,LINE_NO FROM PICK_ORD_DET WHERE PICK_ORDER = ? ";
						pstmtSql = conn.prepareStatement(pickDet);
					}
					//replHashMap.clear();
					
					pstmtSql.setString(1, refId);
					rsPick = pstmtSql.executeQuery();
					while( rsPick.next() )
					{
						
						boolean isPickConf = true;
						replPickOrder = "";
						replPickLineNo = "";
						
						itemCode = checkNull( rsPick.getString("ITEM_CODE") );
						lineNo = checkNull( rsPick.getString("LINE_NO") );
						locCode = checkNull( rsPick.getString("LOC_CODE") );
						
						lotNo = checkNull( rsPick.getString("LOT_NO") );
						lotSl = checkNull( rsPick.getString("LOT_SL") );
						quantity = rsPick.getDouble("QUANTITY");
						expLev = checkNull( rsPick.getString("EXP_LEV") );
						saleOrder = checkNull( rsPick.getString("SALE_ORDER") );
						lineNoSord = checkNull( rsPick.getString("LINE_NO__SORD") );
						
						//Changed by Rohan on 22-01-13 to cancle independent Repanishment.start
						/*
						if(!refSer.trim().equalsIgnoreCase("S-DOC") && !refSer.equalsIgnoreCase("RP-PND") && !refSer.equalsIgnoreCase("RP-PSO"))
						{	
							replOrdOrg = checkNull( rsPick.getString("REPL_ORDER") );
							replLineOrg = checkNull( rsPick.getString("LINE_NO__REPL") );
						}	
						//Changed by Rohan on 22-01-13 to cancle independent Repanishment.end
						 */
								
						if(!refSer.trim().equalsIgnoreCase("S-DOC") && !refSer.equalsIgnoreCase("RP-PND") && !refSer.equalsIgnoreCase("RP-PSO"))
						{	
							replOrder = checkNull( rsPick.getString("REPL_ORDER") );
							replLineNo = checkNull( rsPick.getString("LINE_NO__REPL"));
							//Changed by Rohan on 07-02-13 to get original repanishment code.start
							/*
							//Changed by Rohan on 22-01-13 to cancle independent Repanishment.start
							replOrdOrg = replOrder;
							replLineOrg = replLineNo;
							*/
							//Changed by Rohan on 22-01-13 to cancle independent Repanishment.end
							
							pickOrder = checkNull( rsPick.getString("PICK_ORDER") );
							pickLineNo = checkNull( rsPick.getString("LINE_NO") );
						}
						else
						{
							replOrder = checkNull( rsPick.getString("REPL_ORDER") );
							replLineNo = checkNull( rsPick.getString("LINE_NO") );
						}
						//Changed by Rohan on 07-02-13 to get original repanishment code.start
						replOrdOrg = replOrder;
						replLineOrg = replLineNo;
						//Changed by Rohan on 07-02-13 to get original repanishment code.end
						
						System.out.println("Original Repl Order"+replOrdOrg+"Original Repl line no"+replLineOrg);
						System.out.println("LOCCode***"+locCode);
						qtyStduom = quantity;
						
						do
						{
							//to check pick issue confirm.start
							confirm = "";
							locCodeTo = "";
							transferStatus = "";
							
							if(!refSer.trim().equalsIgnoreCase("S-DOC") && !refSer.equalsIgnoreCase("RP-PND") && !refSer.equalsIgnoreCase("RP-PSO") && isPickConf)
							{	
								ArrayList tempPickConfirm = isPickConfirm(pickOrder,pickLineNo,conn);
								
								locCodeTo = (String) tempPickConfirm.get(0);
								confirm = (String) tempPickConfirm.get(1);
								transferStatus = (String) tempPickConfirm.get(2);
								
							}
							if("Y".equalsIgnoreCase(confirm))
							{
								//to update actual quantity in replanishment
								//Changed by Rohan on 22-01-13 to cancle independent Repanishment
								//updateRecord = updateReplanishment(replOrder,replLineNo,pickOrder,pickLineNo,quantity,true,conn);
								updateRecord = updateReplanishment(replOrder,replLineNo,pickOrder,pickLineNo,quantity,true,waveID,replOrdOrg,replLineOrg,conn);
								
								if(updateRecord > 0)
								{
									System.out.println("Replenishment updated with actual quantity "+quantity+" For replenishment oder :"+replOrder +" and line no :"+replLineNo);
								}
								//to check pick type
								//if(refSer.equalsIgnoreCase("C-PICK"))
								if(refSer.equalsIgnoreCase("C-PICK"))
								{
									locCode = locCodeTo;
									System.out.println("Caspick loc code"+locCode);
									break;
								}
								else if(refSer.equalsIgnoreCase("M-PICK") || refSer.equalsIgnoreCase("A-PICK") || refSer.equalsIgnoreCase("P-PICK"))
								{
									//to check transfer status
									if("Y".equalsIgnoreCase(transferStatus))
									{
										locCode = locCodeTo;
										break;
									}
									else
									{
										//stock allocate from staging location
										if(refSer.equalsIgnoreCase("M-PICK") || refSer.equalsIgnoreCase("P-PICK")) 
										{
											locCode = stagingLocMpick;
											break;
										}
										else
										{
											locCode = stagingLoc;
											break;
										}
										
									}
							      }
							}
							else//pick iss confirm else
							{
								isPickConf = false;
								//To check previous replanishment task confirm.start
								locCodeRepl = "";
								confirmRepl = "";
								
								if(!"".equalsIgnoreCase(replPickOrder))
								{
									replOrder = replPickOrder;
									replLineNo = replPickLineNo;
								}
								
								System.out.println(" New replOrder"+replOrder+"replLineNo"+replLineNo);
								//Changed by Rohan Bhogale on 30-10-12 for if case pick is there that not depended on repalnishment then need not to check repalnishment confirm.start
								if("".equalsIgnoreCase(replOrder))
								{
									break;	
								}
								//Changed by Rohan Bhogale on 30-10-12 for if case pick is there that not depended on repalnishment then need not to check repalnishment confirm.end	
								ArrayList tempPrvReplConfirm = isReplinshmentConfirm(replOrder, replLineNo, conn);
								
								locCodeRepl =(String) tempPrvReplConfirm.get(0);
								confirmRepl = (String) tempPrvReplConfirm.get(1);
								
								System.out.println("locCodeRepl***"+locCodeRepl+"confirmRepl***"+confirmRepl);
								
								//replanishment issue is confirm
								if("Y".equalsIgnoreCase(confirmRepl))
								{
									locCode = locCodeRepl;
									//RD-DSA Repanishment dso active is present
									
									sqlWave = "SELECT REF_SER FROM WAVE_TASK_DET WHERE REF_ID = ?";
									pstmtTask = conn.prepareStatement(sqlWave);
									pstmtTask.setString(1,replOrder);
									rsTask = pstmtTask.executeQuery();
									if(rsTask.next())
									{
										refSeries = rsTask.getString("REF_SER") == null?"":rsTask.getString("REF_SER");
							
										
									}
									if(pstmtTask != null)
									{
										pstmtTask.close();
										pstmtTask = null;
									}
									if(rsTask != null)
									{
										rsTask.close();
										rsTask = null;
									}
									if("RD-DSA".equalsIgnoreCase(refSeries))
									{
										lotSl = (String) tempPrvReplConfirm.get(2);
										
									}
									//to update actual quantity in replanishment
									//Test
									
									//Changed by Rohan on 03/12/11 not update repanishment in case of stock to dock
									//updateRecord = updateReplanishment(replOrder,replLineNo,pickOrder,pickLineNo,quantity,true,conn);
									//Changed by Rohan on 28-Jan-13 for not update actual qty in case of stock to dock 
									//if(!refSer.trim().equalsIgnoreCase("S-DOC") || !refSer.equalsIgnoreCase("RP-PND") || !refSer.equalsIgnoreCase("RP-PSO"))
									System.out.println("REFSERIEEEEEEEEEE"+refSer);
									if(!refSer.trim().equalsIgnoreCase("S-DOC") && !refSer.equalsIgnoreCase("RP-PND") && !refSer.equalsIgnoreCase("RP-PSO"))
									{
										//Changed by Rohan on 22-01-13 to cancle independent Repanishment
										//updateRecord = updateReplanishment(replOrder,replLineNo,pickOrder,pickLineNo,quantity,true,conn);
										System.out.println("*#$S#$#$#$#$#$#$#$#$#$#$#$#$#$");
										updateRecord = updateReplanishment(replOrder,replLineNo,pickOrder,pickLineNo,quantity,true,waveID,replOrdOrg,replLineOrg,conn);
									}
									//Changed by Rohan on 03/12/11 not update repanishment in case of stock to dock.end
									//Changed by Rohan on 30-01-13 to set wave cancel mode Y in case of stock to dock.start
									else
									{
										System.out.println("Cancle Repl"+replOrder+"replLineNo"+replLineNo);
										updateCancelMode(replOrder, replLineNo,"N",conn);
									}
									//Changed by Rohan on 30-01-13 to set wave cancel mode Y in case of stock to dock.start
									if(updateRecord > 0)
									{
										System.out.println("Replenishment updated with actual quantity "+quantity+" For replenishment oder :"+replOrder +" and line no :"+replLineNo);
									}
									
									break;
								}
								else
								{
									locCode = "";
									replPickOrder = "";
									replPickLineNo = "";
									//to get previous repanishment task
									ArrayList tempRepl = getpreviousReplTask(replOrder, replLineNo, conn);
									
									locCode =(String) tempRepl.get(0);
									replPickOrder = (String) tempRepl.get(1);
									replPickLineNo = (String) tempRepl.get(2);
									
									System.out.println("locCode--"+locCode+"replPickOrder"+replPickOrder+"replPickLineNo"+replPickLineNo);
									//update actual quantity in repl_order_det.start
									if("".equalsIgnoreCase(replPickOrder))
									{	//to update actual quantity in repanishment
										//Changed by Rohan on 03/12/11 not update repanishment in case of stock to dock.start
										/*
										if(refSer.trim().equalsIgnoreCase("S-DOC") || refSer.equalsIgnoreCase("RP-PND") || refSer.equalsIgnoreCase("RP-PSO"))
										{
											updateRecord = updateReplanishment(replOrder,replLineNo,pickOrder,pickLineNo,quantity,true,conn);//test1sucess
										}
										else
										{	
											updateRecord = updateReplanishment(replOrder,replLineNo,pickOrder,pickLineNo,quantity,false,conn);//test1sucess
										}
										*/
										//Changed by Rohan on 24-01-13 do not allocate actual Quantity in Repl_ord_det in case of stock to dock
										//if(!refSer.trim().equalsIgnoreCase("S-DOC") || !refSer.equalsIgnoreCase("RP-PND") || !refSer.equalsIgnoreCase("RP-PSO"))
										//Changed by Rohan on 24-01-13 do not allocate actual Quantity in Repl_ord_det in case of stock to dock
										//if(!refSer.trim().equalsIgnoreCase("S-DOC") || !refSer.equalsIgnoreCase("RP-PND") || !refSer.equalsIgnoreCase("RP-PSO"))
										if(!refSer.trim().equalsIgnoreCase("S-DOC") && !refSer.equalsIgnoreCase("RP-PND") && !refSer.equalsIgnoreCase("RP-PSO"))
										{
											//Changed by Rohan on 22-01-13 to cancle independent Repanishment
											//updateRecord = updateReplanishment(replOrder,replLineNo,pickOrder,pickLineNo,quantity,false,conn);
											updateRecord = updateReplanishment(replOrder,replLineNo,pickOrder,pickLineNo,quantity,false,waveID,replOrdOrg,replLineOrg,conn);
										}
										//Changed by Rohan on 03/12/11 not update repanishment in case of stock to dock.start
										//Changed by Rohan on 30-01-13 to set wave cancel mode Y in case of stock to dock.start
										else
										{
											System.out.println("Cancle Repl1"+replOrder+"replLineNo"+replLineNo);
											updateCancelMode(replOrder, replLineNo,"N",conn);
										}
										//Changed by Rohan on 30-01-13 to set wave cancel mode Y in case of stock to dock.start
										
										//Changed by Rohan on 03/12/11 not update repanishment in case of stock to dock.end
										//Changed by Rohan on 03/12/11 not update repanishment in case of stock to dock.start
										
										break;
									}
									//update actual quantity in repl_order_det.end

							     }
								 //To check previous repalnishment task confirm.end
								
								 //if case to active replanishment present which depend upon dso-pnd,pnd-case pick replanishment
								 sqlInv = "SELECT INV_STAT FROM LOCATION WHERE LOC_CODE = ?";
								 pstmtInv = conn.prepareStatement(sqlInv);
								 pstmtInv.setString(1,locCode);
								
								 rsInv = pstmtInv.executeQuery();
								 if(rsInv.next())
								 {
									invState = checkNull(rsInv.getString("INV_STAT"));
								 }
								 if(rsInv != null)
								 {
									rsInv.close();
									rsInv = null;
								 }		
								 if(pstmtInv != null)
								 {
									pstmtInv.close();
									pstmtInv = null;
								 }
								 if(dsoInvstat.trim().equalsIgnoreCase(invState.trim()))
								 {
									//to update actual quantity in replanishment
									//Changed by Rohan on 03/12/11 not update repanishment in case of stock to dock.start
									 /*
									 if(refSer.trim().equalsIgnoreCase("S-DOC") || refSer.equalsIgnoreCase("RP-PND") || refSer.equalsIgnoreCase("RP-PSO"))
									 {
											updateRecord = updateReplanishment(replPickOrder,replPickLineNo,pickOrder,pickLineNo,quantity,true,conn);//test1sucess
									 }
									 else
									 {	
										 updateRecord = updateReplanishment(replPickOrder,replPickLineNo,pickOrder,pickLineNo,quantity,false,conn);
									 }
									 */
									//Changed by Rohan on 24-01-13 do not allocate actual Quantity in Repl_ord_det in case of stock to dock
									 //if(!refSer.trim().equalsIgnoreCase("S-DOC") || !refSer.equalsIgnoreCase("RP-PND") || !refSer.equalsIgnoreCase("RP-PSO"))
									 if(!refSer.trim().equalsIgnoreCase("S-DOC") && !refSer.equalsIgnoreCase("RP-PND") && !refSer.equalsIgnoreCase("RP-PSO"))
									 {
										//Changed by Rohan on 22-01-13 to cancle independent Repanishment
										 //updateRecord = updateReplanishment(replPickOrder,replPickLineNo,pickOrder,pickLineNo,quantity,false,conn);
										 updateRecord = updateReplanishment(replPickOrder,replPickLineNo,pickOrder,pickLineNo,quantity,false,waveID,replOrdOrg,replLineOrg,conn);
									 }
									//Changed by Rohan on 03/12/11 not update repanishment in case of stock to dock.end 
									//Changed by Rohan on 30-01-13 to set wave cancel mode Y in case of stock to dock.start
									else
									{
										System.out.println("Cancle Repl2"+replPickOrder+"replLineNo"+replPickLineNo);
										updateCancelMode(replPickOrder, replPickLineNo,"N",conn);
									}
									//Changed by Rohan on 30-01-13 to set wave cancel mode Y in case of stock to dock.end
									 if(updateRecord > 0)
									 {
										System.out.println("Replenishment updated with actual quantity "+quantity+" For replenishment oder :"+replPickOrder +" and line no :"+replPickLineNo);
									 }
										
									 break;
								 }
							}
							
						}
						while(!"".equalsIgnoreCase(replPickOrder) && !"".equalsIgnoreCase(replPickLineNo));	
						
						System.out.println("Final location code*****"+locCode+"LoT Sl***"+lotSl);
						lineNoSord = "  " + lineNoSord; 
						
						sql = "SELECT QTY_ALLOC AS COUNT FROM SORDALLOC "
							  +"WHERE SALE_ORDER = ? "
							  +"AND LINE_NO = ? "
							  +"AND EXP_LEV = ? "
							  +"AND ITEM_CODE__ORD = ? "
							  +"AND ITEM_CODE = ? "
							  +"AND LOT_NO = ? "
							  +"AND LOT_SL = ? "
							  +"AND LOC_CODE = ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1,saleOrder);
						pstmt.setString(2,  lineNoSord);
						pstmt.setString(3,expLev);
						pstmt.setString(4,itemCode);
						pstmt.setString(5,itemCode);
						pstmt.setString(6,lotNo);
						pstmt.setString(7,lotSl);
						pstmt.setString(8,locCode);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							
							qtyAlloc = rs.getDouble(1);
						}
						else
						
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
	
						// 22/02/10 manoharan if allocation found in sordalloc then deallocate
						//if (count > 0 ) // 04/04/12 manoharan
						if (qtyAlloc > 0) // 04/04/12 manoharan
						{
							if (qtyAlloc - qtyStduom <= 0)
							{
								sqlSord = "DELETE FROM SORDALLOC WHERE SALE_ORDER = ? "
									 +"AND LINE_NO = ? "
									 +"AND EXP_LEV = ? " 
									 +"AND ITEM_CODE__ORD = ? " 
									 +"AND ITEM_CODE = ? " 
									 +"AND LOT_NO = ? " 
									 +"AND LOT_SL = ? "
									 +"AND LOC_CODE = ? ";
								System.out.println("Delete sql sqlSord :"+sqlSord);
								pstmt = conn.prepareStatement(sqlSord);
								pstmt.setString(1,saleOrder);
								pstmt.setString(2,lineNoSord);
								pstmt.setString(3,expLev);
								pstmt.setString(4,itemCode);
								pstmt.setString(5,itemCode);
								pstmt.setString(6,lotNo);
								pstmt.setString(7,lotSl);
								pstmt.setString(8,locCode);
								updateCnt = pstmt.executeUpdate();
								pstmt.close();
								pstmt = null;
							}
							else
							{
								sqlSord = "UPDATE SORDALLOC SET QTY_ALLOC = QTY_ALLOC - ? "
									  +"WHERE SALE_ORDER = ? "
									  +"AND LINE_NO = ? "
									  +"AND EXP_LEV = ? "
									  +"AND ITEM_CODE__ORD = ? "
									  +"AND ITEM_CODE = ? "
									  +"AND LOT_NO = ? "
									  +"AND LOT_SL = ? "
									  +"AND LOC_CODE = ? ";
								System.out.println("Update sql sqlSord :"+sqlSord);
								pstmt = conn.prepareStatement(sqlSord);
								pstmt.setDouble(1,qtyStduom);
								pstmt.setString(2,saleOrder);
								pstmt.setString(3,lineNoSord);
								pstmt.setString(4,expLev);
								pstmt.setString(5,itemCode);
								pstmt.setString(6,itemCode);
								pstmt.setString(7,lotNo);
								pstmt.setString(8,lotSl);
								pstmt.setString(9,locCode);
								updateCnt = pstmt.executeUpdate();
								pstmt.close();
								pstmt = null;
								System.out.println("Updated the no of records are : updateCnt :"+updateCnt);
							}
							sqlSordItem = "UPDATE SORDITEM SET QTY_ALLOC = QTY_ALLOC - ? "
										 +"WHERE SALE_ORDER = ? "
										 +"AND LINE_NO = ? "
										 +"AND EXP_LEV = ? ";
							System.out.println("Update sql sqlSordItem :"+sqlSordItem);
							pstmt = conn.prepareStatement(sqlSordItem);
							pstmt.setDouble(1,qtyStduom);
							pstmt.setString(2,saleOrder);
							pstmt.setString(3,lineNoSord);
							pstmt.setString(4,expLev);
							updateCnt = pstmt.executeUpdate();
							pstmt.close();
							pstmt = null;
							System.out.println("Updated the no of records : updateCnt :"+updateCnt);
	
							line = "   " + lineNo;
							System.out.println("line :"+line);
							
							//Changed by Rohan on 10/8/12 to give proper loc code.start
	
							
							//Changed by Rohan on 10/8/12 to give proper loc code.end
							strAllocate.put("tran_date",tranDate);
							strAllocate.put("ref_ser","P-TASK");
							//strAllocate.put("ref_id",tranId);
							strAllocate.put("ref_id",refId);
							strAllocate.put("ref_line",line.substring(line.length()-3));
							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(-1*qtyStduom)); 
							strAllocate.put("chg_user",genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginCode"));
							strAllocate.put("chg_term",genericUtility.getValueFromXTRA_PARAMS(xtraParams,"termId"));
							strAllocate.put("chg_win","W_PICK_ORD");
							//Calling DistStkUpdEJB
							System.out.println("Calling DistStkUpdEJB.....");
							if (distStkUpd.updAllocTrace(strAllocate, conn) > 0)
							{
								System.out.println("distStkUpd.UpdAllocTrace(hashMap) : Sucessuful!");
							}
							
							sqlUpdate = "UPDATE WAVE_TASK SET CANCEL ='Y' WHERE WAVE_ID = ?" ;						
							pstmtUpd = conn.prepareStatement(sqlUpdate);
							pstmtUpd.setString(1, waveID);
							updCnt = pstmtUpd.executeUpdate();
							
							if( updCnt > 0 )
							{					
								retString = itmDBAccess.getErrorString("","WAVEREV","");
							}
							else
							{
								retString = itmDBAccess.getErrorString("","NTWAVEREV","");
							}
							
							if(pstmtUpd != null)
							{
								pstmtUpd.close();
								pstmtUpd = null;
							}
							
								
						  }//qty allocate > 0
					 }
				 }
			 }//while close
		
			
		}
		catch(Exception e)
		{
			isError = true;
			try
			{
				conn.rollback();
			}
			catch (Exception e1)
			{
			}
			e.printStackTrace();
			System.out.println("Exception ::"+e.getMessage());
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if( !isError && connStatus )
				{
					conn.commit(); 
					
					if(pstmtReplDet != null)
					{
						pstmtReplDet.close();
						pstmtReplDet = null;
					}
					
					if("Y".equalsIgnoreCase(status))
					{
						retString = itmDBAccess.getErrorString("","WAVENTREV","");
					}
					
				}
				else if (isError && connStatus)
				{
					conn.rollback();
				}
				if(rs != null)
				{
					rs.close();
					rs = null;					
				}
				if(pstmtUpd != null)
				{
					pstmtUpd.close();
					pstmtUpd = null;					
				}
				if( conn != null && ! conn.isClosed() && connStatus)
				{
					conn.close();
					conn = null;
				}
				if(pstmtWave != null)
				{
					pstmtWave.close();
					pstmtWave = null;
				}
				if(rsWave != null)
				{
					rsWave.close();
					rsWave = null;
				}
			}
			catch(Exception e)
			{
				System.out.println(e.getMessage());
				throw new ITMException(e);
			}
		}
		System.out.println("Returning Result ::"+retString);
		return retString;
	}
	private String checkNull( String input )
	{
		if( input == null )
		{
			input = "";
		}
		return input;
	}
	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;
	}
	private ArrayList isReplinshmentConfirm(String replOrder ,String replLineNo,Connection conn) throws Exception
	{
	
		PreparedStatement pstmtReplDet = null;
		ResultSet rsRepl = null;
	  
		
		//String LocCodeTo = ""; 

		String lotSlNew = "";
		
		String confirmRepl = "";
		String locCodeRepl = "";
		String sqlreplDet = "";
		String pickOrder = "";
		String lineNoPick = "";
		
		ArrayList tempList = new ArrayList();
		
		try
		{
			sqlreplDet = "SELECT D.LOC_CODE__TO AS LOC_CODE__TO,D.LOT_SL__NEW AS LOT_SL__NEW,H.CONFIRMED AS CONFIRMED FROM REPL_ISS_DET D,REPL_ISS_HDR H "
					+" WHERE D.REPL_ORDER = ? AND D.LINE_NO__ORD = ?"
					+" AND D.TRAN_ID = H.TRAN_ID";
			pstmtReplDet = conn.prepareStatement(sqlreplDet);
			
			pstmtReplDet.setString(1,replOrder);
			pstmtReplDet.setString(2,replLineNo);
			rsRepl = pstmtReplDet.executeQuery();
			if(rsRepl.next())
			{
				
				locCodeRepl = checkNull( rsRepl.getString("LOC_CODE__TO") );
				confirmRepl = checkNull( rsRepl.getString("CONFIRMED") );
				lotSlNew = checkNull( rsRepl.getString("LOT_SL__NEW") );
				
			}
			
			
			tempList.add(locCodeRepl);
			tempList.add(confirmRepl);
			tempList.add(lotSlNew);
			tempList.add(pickOrder);
			tempList.add(lineNoPick);
			
					
        }
		catch(Exception e)
		{
			throw new ITMException(e);
		}
		finally
		{
			if(rsRepl != null)
			{
				rsRepl.close();
				rsRepl = null;
			}
			if(pstmtReplDet != null)
			{
				pstmtReplDet.close();
				pstmtReplDet = null;
			}
		
		}
	return tempList;  	
	}
	
	private ArrayList isPickConfirm(String pickOrder ,String pickLineNo,Connection conn) throws Exception
	{
	
		PreparedStatement pstmtPickIss = null;
		ResultSet rsPickIss = null;
	  
	
		String confirm = "";
		String locCodeTo = "";
		String transferStatus = "";
		String sqlpickDet = "";
		
		ArrayList tempPickList = new ArrayList();
		
		try
		{
			//To check pick is confirm or not.start
			locCodeTo = "";
			confirm = "";
			transferStatus = "";
			
			
			sqlpickDet = "SELECT D.LOC_CODE__TO AS LOC_CODE__TO,H.CONFIRMED AS CONFIRMED,H.TRF_STATUS AS TRF_STATUS FROM PICK_ISS_DET D,PICK_ISS_HDR H " 
						+" WHERE D.PICK_ORDER = ? AND D.LINE_NO__ORD = ? "
					    +" AND D.TRAN_ID = H.TRAN_ID";
			
			
			pstmtPickIss = conn.prepareStatement(sqlpickDet);
			pstmtPickIss.setString(1,pickOrder);
			pstmtPickIss.setString(2,pickLineNo);
			rsPickIss = pstmtPickIss.executeQuery();
			if(rsPickIss.next())
			{
				locCodeTo = checkNull( rsPickIss.getString("LOC_CODE__TO") );
				confirm = checkNull( rsPickIss.getString("CONFIRMED") );
				transferStatus = checkNull( rsPickIss.getString("TRF_STATUS") );
				
			}
			if(rsPickIss != null)
			{
				rsPickIss.close();
				rsPickIss = null;
			}
			
			if(pstmtPickIss != null)
			{
				pstmtPickIss.close();
				pstmtPickIss = null;
			}
			
			tempPickList.add(locCodeTo);
			tempPickList.add(confirm);
			tempPickList.add(transferStatus);
			//Pick issue confirm
			
        }
		catch(Exception e)
		{
			throw new ITMException(e);
		}
		finally
		{
			if(rsPickIss != null)
			{
				rsPickIss.close();
				rsPickIss = null;
			}
			if(pstmtPickIss != null)
			{
				pstmtPickIss.close();
				pstmtPickIss = null;
			}
		
		}
	return tempPickList;  	
	}

	private ArrayList getpreviousReplTask(String replOrder ,String replLineNo,Connection conn) throws Exception
	{
	
		PreparedStatement pstmtRepl = null;
		ResultSet rsReplDet = null;
	  
		String replPickLineNo = "";
		String replPickOrder = "";
		String locCode = "";
		String sqlReplOrd = "";
		
		ArrayList tempList = new ArrayList();
		
		try
		{
			sqlReplOrd= "SELECT PICK_ORDER,LINE_NO__PICK,LOC_CODE FROM REPL_ORD_DET WHERE REPL_ORDER = ? AND LINE_NO = ?";
			pstmtRepl = conn.prepareStatement(sqlReplOrd);
			
			
			pstmtRepl.clearParameters();
			pstmtRepl.setString(1, replOrder);
			pstmtRepl.setString(2, replLineNo);
			rsReplDet = pstmtRepl.executeQuery();
			if(rsReplDet.next())
			{
				
				locCode = checkNull( rsReplDet.getString("LOC_CODE") );
				replPickOrder = checkNull( rsReplDet.getString("PICK_ORDER") );
				replPickLineNo = checkNull( rsReplDet.getString("LINE_NO__PICK") );
				
				
			}
			
			tempList.add(locCode);
			tempList.add(replPickOrder);
			tempList.add(replPickLineNo);
				
        }
		catch(Exception e)
		{
			throw new ITMException(e);
		}
		finally
		{
			if(rsReplDet != null)
			{
				rsReplDet.close();
				rsReplDet = null;
			}
			if(pstmtRepl != null)
			{
				pstmtRepl.close();
				pstmtRepl = null;
			}
		
		}
	return tempList;  	
	}
	//Changed by Rohan on 22-01-13 to cancle independent Repanishment
	//public int updateReplanishment(String replOrder,String lineNo,String pickOrder,String lineNoPick,double quantity,boolean confirm,Connection conn)throws Exception
	public int updateReplanishment(String replOrder,String lineNo,String pickOrder,String lineNoPick,double quantity,boolean confirm,String waveID,String replOrdOrg,String replLineOrg,Connection conn)throws Exception
	{
	
		PreparedStatement pstmt = null;
		PreparedStatement pstmt1 = null;
		PreparedStatement pstmtRepl = null;
		ResultSet rs = null;
		ResultSet rs1 = null;
	    String sql = "";
	    String getRepl = "";
	    String orderType = "";
	    String getActualQty,updateSql,replOrd,lineNoRepl,sqlOrderType,ordType ="";
	    String locCode,repl,lineRepl,sqlPickOrder,invStat,invstatChk = "";
	    String pick = "";
	    String sqlRepl="";
	    String replOrderCopy = "";
	    String lineNoPk = "";
	    String replOrdList = "";
		String replLineNoList = "";
		String invS = "";
		String replOrdChk = "";
		String replLineChk = "";
		String ordRepl = "";
		//Changed by Rohan on 22-01-13 to cancle independent Repanishment.start
		String sqlPick ="";
		String sqlWave ="";
		String waveIDCmp = "";
		String sqlAct ="";
		String sqlDep ="";
		String replType = "";
		//Changed by Rohan on 07-02-13 for define variables.strat
		String replOrdDep = "";
		String replOrdDepLine = "";
		int cnt = 0;
		String sqlDep1 = "";
		//Changed by Rohan on 11-02-13 for define variables
		double shipperSize = 0.0;
		
		PreparedStatement pstmt2 = null;
		ResultSet rs2 = null;
		boolean isDepRepl = false;
		//Changed by Rohan on 22-01-13 to cancle independent Repanishment.end
		//Changed by Rohan on 18-02-13 for checking repanishment confirm.start
		String sqlReplConf = "";
		String replOrd1 = "";
		String replLine1 ="";
		//Changed by Rohan on 18-02-13 for checking repanishment confirm.end
	    DistCommon discommon = new DistCommon();
	    String casePickLoc  = discommon.getDisparams("999999","CASE_PICK_INVSTAT",conn);
	    String activePickLoc  = discommon.getDisparams("999999","ACTIVE_PICK_INVSTAT",conn);
	    String deepStoreLoc = discommon.getDisparams("999999","DEEP_STORE_INVSTAT",conn);
	    String psoLoc  = discommon.getDisparams("999999","RESERV_LOCATION",conn);
	    
		double actQty = 0d;
		  
		int count = 0;
		try
		{
			System.out.println("replOrdOrg::::::"+replOrdOrg+"replLineOrg:::::"+replLineOrg);
			
			//Changed by Rohan on 22-01-13 to cancle independent Repanishment.start
			//to check case to active Repanishment.start
			
			sqlAct = "SELECT ORDER_TYPE FROM REPL_ORD_HDR WHERE REPL_ORDER = ? ";
			pstmt2 =  conn.prepareStatement(sqlAct);	
			pstmt2.setString(1, replOrdOrg);
			rs2 = pstmt2.executeQuery();
			if(rs2.next())
			{
				replType = rs2.getString("ORDER_TYPE");
			}
			if(pstmt2 != null)
			{
				pstmt2.close();
				pstmt2 = null;
			}
			if(rs2 != null)
			{
				rs2.close();
				rs2 = null;
			}
			
			System.out.println("repl type:::"+replType);
			//Changed by Rohan on 18-02-13 for checking repanishment confirm.start
			sqlReplConf = "SELECT COUNT(*) AS COUNT FROM REPL_ISS_DET WHERE REPL_ORDER = ? AND LINE_NO__ORD = ?";
			pstmt2 =  conn.prepareStatement(sqlReplConf);
			
			sql = "SELECT PICK_ORDER,LINE_NO__PICK FROM REPL_ORD_DET WHERE REPL_ORDER = ? AND LINE_NO = ? ";
			pstmt1 =  conn.prepareStatement(sql);
			
			replOrd1 = replOrdOrg;
			replLine1 = replLineOrg;
			do
			{	
					
				pstmt2.setString(1, replOrd1);
				pstmt2.setString(2, replLine1);
				rs2 = pstmt2.executeQuery();
				if(rs2.next())
				{
					cnt = rs2.getInt("COUNT");
				}
				if(cnt > 0)
				{
					isDepRepl = true;
					break;
				}
				//to check dso-pnd repanishment exist.start
				pstmt1.setString(1, replOrd1);
				pstmt1.setString(2, replLine1);
				rs1 = pstmt1.executeQuery();
				replOrd1 = "";
				replLine1 = "";
				
				if(rs1.next())
				{
					replOrd1 = checkNull(rs1.getString("PICK_ORDER"));
					replLine1 = rs1.getString("LINE_NO__PICK");
				}
				//to check dso-pnd repanishment exist.end
				
				pstmt1.clearParameters();
				pstmt2.clearParameters();
				
			}	
			while(!"".equalsIgnoreCase(replOrd1));
			
			if(pstmt1 != null)
			{
				pstmt1.close();
				pstmt1 = null;
			}
			if(pstmt2 != null)
			{
				pstmt2.close();
				pstmt2 = null;
			}
			if(rs2 != null)
			{
				rs2.close();
				rs2 = null;
			}
			if(rs1 != null)
			{
				rs1.close();
				rs1 = null;
			}
			//Changed by Rohan on 18-02-13 for checking repanishment confirm.end
			//to check PICK wave id and reference Repanishment WAVE_ID different.start
			//sqlWave = "SELECT WAVE_ID FROM WAVE_TASK_DET WHERE REF_ID = ? ";
			sqlWave = " SELECT D.WAVE_ID FROM WAVE_TASK_DET D,WAVE_TASK H WHERE D.REF_ID = ? "
					 +" AND D.WAVE_ID = H.WAVE_ID AND CASE WHEN CANCEL IS NULL THEN 'N' ELSE CANCEL END <> 'Y' ";
			pstmt1 =  conn.prepareStatement(sqlWave);
			pstmt1.setString(1,replOrdOrg);
			rs1 = pstmt1.executeQuery();
			while(rs1.next())
			{
				waveIDCmp =  rs1.getString("WAVE_ID");
				if(!waveIDCmp.trim().equalsIgnoreCase(waveID.trim()) && !"".equalsIgnoreCase(waveIDCmp))
				{
					isDepRepl = true;
					break;
				}
			}
			//to check PICK wave id and reference Repanishment WAVE_ID different.end
			
			//Changed by Rohan on 21-02-13 for checkig
			if(!isDepRepl)
			{
				
				/*
				if("Q".equalsIgnoreCase(replType))
				{
					sqlDep = "SELECT PICK_ORDER,LINE_NO__PICK FROM REPL_ORD_DET WHERE REPL_ORDER = ? AND LINE_NO = ?";
				}
				else
				{
					sqlDep = "SELECT REPL_ORDER,LINE_NO FROM REPL_ORD_DET WHERE PICK_ORDER = ? AND LINE_NO__PICK = ? ";
				}
				*/
				
				sqlDep = "SELECT REPL_ORDER,LINE_NO FROM REPL_ORD_DET WHERE PICK_ORDER = ? AND LINE_NO__PICK = ? "
						+" AND CASE WHEN CANCEL_MODE IS NULL THEN 'N' ELSE CANCEL_MODE END <> 'Y' ";
				
				//Changed by Rohan on 26-02-13 for pick reference in all cases.start
				/*
				if(!"Q".equalsIgnoreCase(replType))
				{
					
					//if dso-pnd,pnd-caspk repl dependent two pick in two wave IDS
					//Changed by Rohan on 21-02-13 for to check repanishment wave cancel or not
					//sqlDep1 = "SELECT PICK_ORDER,LINE_NO FROM PICK_ORD_DET WHERE REPL_ORDER = ? AND LINE_NO__REPL = ? AND PICK_ORDER <> ? ";
					sqlDep1 = "SELECT P.PICK_ORDER,P.LINE_NO FROM PICK_ORD_DET P ,WAVE_TASK W,WAVE_TASK_DET D WHERE REPL_ORDER = ? AND LINE_NO__REPL = ? "
							  +" AND PICK_ORDER <> ? AND D.REF_ID = P.PICK_ORDER AND D.WAVE_ID = W.WAVE_ID AND CASE WHEN W.CANCEL IS NULL THEN 'N' " 
							  +" ELSE CANCEL END <> 'Y' ";
				}
				*/
				sqlDep1 = "SELECT P.PICK_ORDER,P.LINE_NO FROM PICK_ORD_DET P ,WAVE_TASK W,WAVE_TASK_DET D WHERE REPL_ORDER = ? AND LINE_NO__REPL = ? "
						  +" AND PICK_ORDER <> ? AND D.REF_ID = P.PICK_ORDER AND D.WAVE_ID = W.WAVE_ID AND CASE WHEN W.CANCEL IS NULL THEN 'N' " 
						  +" ELSE CANCEL END <> 'Y' ";
				//Changed by Rohan on 26-02-13 for pick reference in all cases.end
				pstmt2 =  conn.prepareStatement(sqlDep);	
				pstmt2.setString(1, replOrdOrg);
				pstmt2.setString(2, replLineOrg);
				/*if(!"Q".equalsIgnoreCase(replType) && !"J".equalsIgnoreCase(replType) && !"K".equalsIgnoreCase(replType) && !"I".equalsIgnoreCase(replType) )
				{
					pstmt2.setString(3, pickOrder);
				}*/	
				rs2 = pstmt2.executeQuery();
				if(rs2.next())
				{
					//ordRepl = rs2.getString(1);
					 replOrdDep = checkNull(rs2.getString("REPL_ORDER"));
					 replOrdDepLine = checkNull(rs2.getString("LINE_NO"));
					
				}
				if(pstmt2 != null)
				{
					pstmt2.close();
					pstmt2 = null;
				}
				if(rs2 != null)
				{
					rs2.close();
					rs2 = null;
				}
				//Changed by Rohan on 26-02-13 for pick reference in all cases.start
				//if("".equalsIgnoreCase(replOrdDep) && !"Q".equalsIgnoreCase(replType))
				if("".equalsIgnoreCase(replOrdDep))
				{
					pstmt2 =  conn.prepareStatement(sqlDep1);	
					pstmt2.setString(1, replOrdOrg);
					pstmt2.setString(2, replLineOrg);
					pstmt2.setString(3, pickOrder);
						
					rs2 = pstmt2.executeQuery();
					if(rs2.next())
					{
						//ordRepl = rs2.getString(1);
						 replOrdDep = checkNull(rs2.getString("PICK_ORDER"));
						 replOrdDepLine = checkNull(rs2.getString("LINE_NO"));
					}	 
				}
				
				pstmt1.clearParameters();
				waveIDCmp = "";
				
				if(!"".equalsIgnoreCase(replOrdDep) && replOrdDep.trim().length() > 0)
				{	
					pstmt1.setString(1, replOrdDep);
					rs1 = pstmt1.executeQuery();
					while(rs1.next())
					{
						waveIDCmp =  rs1.getString("WAVE_ID");
						
						if(!waveIDCmp.trim().equalsIgnoreCase(waveID.trim()) && !"".equalsIgnoreCase(waveIDCmp))
						{
							isDepRepl = true;
							break;
						}
					}			
			
				}
			
				if(rs1 != null)
				{
					rs1.close();
					rs1 = null;
				}
			}
			if(isDepRepl)
			{
			//Changed by Rohan on 22-01-13 to cancle independent Repanishment.end
				if(confirm)
				{
					//if(!"C".equalsIgnoreCase(orderType))
					//{	
						getRepl = "SELECT D.PICK_ORDER,D.LINE_NO__PICK,H.ORDER_TYPE FROM REPL_ORD_DET D," 
								+" REPL_ORD_HDR H WHERE D.REPL_ORDER = ? AND D.LINE_NO = ? AND H.REPL_ORDER = D.REPL_ORDER";
						pstmtRepl =  conn.prepareStatement(getRepl);	
						pstmtRepl.setString(1, replOrder);
						pstmtRepl.setString(2, lineNo);
						rs = pstmtRepl.executeQuery();
						if(rs.next())
						{
							orderType = rs.getString("ORDER_TYPE");
							System.out.println("Order type:::"+orderType);
							if(!"C".equalsIgnoreCase(orderType) && !"K".equalsIgnoreCase(orderType.trim()) && !"J".equalsIgnoreCase(orderType.trim()) && !"M".equalsIgnoreCase(orderType.trim()))
							{	
								if(!"Q".equalsIgnoreCase(orderType))
								{	
									replOrderCopy = checkNull(rs.getString("PICK_ORDER")); 
									
									if(!"".equalsIgnoreCase(replOrderCopy))
									{
										replOrder = replOrderCopy;
										lineNo = rs.getString("LINE_NO__PICK");
									}
								}
								else 
								{
									
									//to reverse actual_qty in case repanishment
									
									replOrdChk = "";
									replLineChk = "";
									//Changed by Rohan on 11-02-13 for reduce one case from actual qty
									/*
									getActualQty ="SELECT D.ACTUAL_QTY,L.INV_STAT,D.PICK_ORDER,D.LINE_NO__PICK FROM REPL_ORD_DET D,LOCATION L "
									              +" WHERE REPL_ORDER = ? AND LINE_NO = ? "
									              + " AND L.LOC_CODE = D.LOC_CODE ";
									*/
									getActualQty ="SELECT D.ACTUAL_QTY,L.INV_STAT,D.PICK_ORDER,D.LINE_NO__PICK,I.SHIPPER_SIZE " 
												  +" FROM REPL_ORD_DET D,LOCATION L,ITEM_LOT_PACKSIZE I " 
												  +" WHERE REPL_ORDER = ? AND LINE_NO = ? "
												  + " AND L.LOC_CODE = D.LOC_CODE AND I.ITEM_CODE = D.ITEM_CODE";
								
									pstmt1 =  conn.prepareStatement(getActualQty);	
									pstmt1.setString(1, replOrder);
									pstmt1.setString(2, lineNo);
									rs1 = pstmt1.executeQuery();
									if(rs1.next())
									{
										actQty = rs1.getDouble("ACTUAL_QTY");
										invS =  rs1.getString("INV_STAT");
										replOrdChk = rs1.getString("PICK_ORDER");
										replLineChk = rs1.getString("LINE_NO__PICK");
										//Changed by Rohan on 11-02-13 for reduce one case from actual qty
										shipperSize = rs1.getDouble("SHIPPER_SIZE");
									}
									if(rs1 != null)
									{
										rs1.close();
										rs1 = null;
									}
									if(pstmt1 != null)
									{
										pstmt1.close();
										pstmt1 = null;
									}
									
									replOrd = checkNull(rs.getString("PICK_ORDER"));
									lineNoRepl = rs.getString("LINE_NO__PICK");
									
									if(!"".equalsIgnoreCase(replOrd))
									{	
										
										if(invS.trim().equalsIgnoreCase(casePickLoc.trim()))
										{
											sqlOrderType = "SELECT D.PICK_ORDER,D.LINE_NO__PICK,L.INV_STAT FROM REPL_ORD_DET D,LOCATION L " 
												 +" WHERE REPL_ORDER = ? AND LINE_NO = ? "
												 +" AND L.LOC_CODE = D.LOC_CODE ";
											pstmt1 =  conn.prepareStatement(sqlOrderType);	
											pstmt1.setString(1, replOrdChk);
											pstmt1.setString(2, replLineChk);
											rs1 = pstmt1.executeQuery();
											if(rs1.next())
											{
												
												invS =  rs1.getString("INV_STAT");
												replOrdChk = checkNull(rs1.getString("PICK_ORDER"));
												replLineChk = checkNull(rs1.getString("LINE_NO__PICK"));
											}
											if(rs1 != null)
											{
												rs1.close();
												rs1 = null;
											}
											if(pstmt1 != null)
											{
												pstmt1.close();
												pstmt1 = null;
											}
											
										}
										
										if(invS.trim().equalsIgnoreCase(psoLoc.trim()))
										{
											updateSql= " UPDATE REPL_ORD_DET SET ACTUAL_QTY = ACTUAL_QTY - ?"
													   +" WHERE REPL_ORDER = ? AND LINE_NO = ? ";
										}
										else
										{
											updateSql= " UPDATE REPL_ORD_DET SET ACTUAL_QTY = ACTUAL_QTY - ?"
													  +" WHERE (REPL_ORDER,LINE_NO) IN(SELECT PICK_ORDER,LINE_NO__PICK FROM REPL_ORD_DET"
													   +" WHERE REPL_ORDER = ? AND LINE_NO = ?)";
										}
										pstmt1 =  conn.prepareStatement(updateSql);
										//Changed by Rohan on 11-02-13 for reduce one case from actual qty
										//pstmt1.setDouble(1, actQty);
										pstmt1.setDouble(1, shipperSize);
										if("".equalsIgnoreCase(replOrdChk) && invS.trim().equalsIgnoreCase(casePickLoc.trim()))
										{
											pstmt1.setString(2, replOrdChk);
											pstmt1.setString(3, replLineChk);
										}
										else
										{	
											pstmt1.setString(2, replOrd);
											pstmt1.setString(3, lineNoRepl);
										}	
										pstmt1.executeUpdate();
										if(pstmt1 != null)
										{
											pstmt1.close();
											pstmt1 = null;
										}
									}
								}
							}
							//Changed by Rohan on 25-10-12
							//else if("K".equalsIgnoreCase(orderType.trim()) || "J".equalsIgnoreCase(orderType.trim()) || "M".equalsIgnoreCase(orderType.trim()))
							else if("K".equalsIgnoreCase(orderType.trim()) || "J".equalsIgnoreCase(orderType.trim()) || "M".equalsIgnoreCase(orderType.trim()) ||  "C".equalsIgnoreCase(orderType.trim()))
							{
								
								ArrayList ReplActQty = updateReplActQty(pickOrder,lineNoPick,conn);
								
								if(ReplActQty.size() > 0)
								{	
									replOrdList = checkNull((String)ReplActQty.get(0));
									replLineNoList = checkNull((String)ReplActQty.get(1));
								}
								if(!"".equalsIgnoreCase(replOrdList))
								{
									
									replOrder = replOrdList;
									lineNo = replLineNoList;
								}
								
				
					         }
						}
				}//confirm
				else
				{
					ArrayList ReplActQty = updateReplActQty(pickOrder,lineNoPick,conn);
					if(ReplActQty.size() > 0)
					{
						replOrdList = checkNull((String)ReplActQty.get(0));
						replLineNoList = checkNull((String)ReplActQty.get(1));
					}
					if(!"".equalsIgnoreCase(replOrdList))
					{
						replOrder = replOrdList;
						lineNo = replLineNoList;
					}	
				}
				
				sql= "UPDATE REPL_ORD_DET SET ACTUAL_QTY = CASE WHEN ACTUAL_QTY IS NULL THEN 0 ELSE ACTUAL_QTY END + ?" +
						 " WHERE REPL_ORDER = ? AND LINE_NO = ?" ;
				pstmt =  conn.prepareStatement(sql);
				pstmt.setDouble(1, quantity);
				pstmt.setString(2, replOrder);
				pstmt.setString(3, lineNo);
				count = pstmt.executeUpdate();
			//Changed by Rohan on 22-01-13 to cancle independent Repanishment.start	
			}
			else
			{
				
				sql= "UPDATE REPL_ORD_DET SET ACTUAL_QTY = 0,CANCEL_MODE = 'Y'" +
						 " WHERE REPL_ORDER = ? AND LINE_NO = ?" ;
				pstmt =  conn.prepareStatement(sql);
				if("Q".equalsIgnoreCase(replType))
				{		
					pstmt.setString(1, replOrdOrg);
					pstmt.setString(2, replLineOrg);
				}
				else
				{
					pstmt.setString(1, replOrder);
					pstmt.setString(2, lineNo);
				}
				count = pstmt.executeUpdate();
				
				if(!"Q".equalsIgnoreCase(replType) && !"C".equalsIgnoreCase(replType))
				{
					//Update dependent Repl
					updateCancelMode(replOrder,lineNo,"Y",conn);
				}
				else if("Q".equalsIgnoreCase(replType))
				{
					updateCancelMode(replOrdOrg,replLineOrg,"Q",conn);
				}
					
			}
			//Changed by Rohan on 22-01-13 to cancle independent Repanishment.end
						
        }
		catch(Exception e)
		{
			throw new ITMException(e) ;
		}
		finally
		{
			if(rs != null)
			{
				rs.close();
				rs = null;
			}
			if(pstmt != null)
			{
				pstmt.close();
				pstmt = null;
			}
			if(pstmtRepl != null)
			{	
				pstmtRepl.close();
				pstmtRepl = null;
			}
			if(rs1 != null)
			{
				rs1.close();
				rs1 = null;
			}
			if(pstmt1 != null)
			{
				pstmt1.close();
				pstmt1 = null;
			}
		
		}
		return count;
	}
	//updateReplActQty(pickOrder,lineNoPick,conn);
	public ArrayList updateReplActQty(String pickOrder,String lineNoPick,Connection conn) throws Exception
	{

		PreparedStatement pstmt = null;
		ResultSet rs = null;
	    String sql = "";
	   
	   
		PreparedStatement pstmt1 = null;
		PreparedStatement pstmtRepl = null;
		
		ResultSet rs1 = null;
	    
	    String getRepl = "";
	    String orderType = "";
	    String getActualQty,updateSql,replOrd,lineNoRepl,sqlOrderType,ordType ="";
	    String locCode,repl,lineRepl,sqlPickOrder,invStat,invstatChk = "";
	    String pick = "";
	    String sqlRepl="";
	    String replOrderCopy = "";
	    String lineNoPk = "";
	    //Changed by Rohan on 11-02-13 for define variable
	    double shipperSize = 0.0;
	    boolean isUpdate = true;
	    DistCommon discommon = new DistCommon();
	    String casePickLoc  = discommon.getDisparams("999999","CASE_PICK_INVSTAT",conn);
	    String activePickLoc  = discommon.getDisparams("999999","ACTIVE_PICK_INVSTAT",conn);
	    String deepStoreLoc = discommon.getDisparams("999999","DEEP_STORE_INVSTAT",conn);
	    String psoLoc  = discommon.getDisparams("999999","RESERV_LOCATION",conn);
	    
	    double actQty = 0d;
		
	    ArrayList tempReplList = new ArrayList();
	    
		try
		{
			replOrd = "";
		    lineNoRepl = "";
		    repl = "";
			lineRepl = "";
			invStat = "";
			
			sqlPickOrder = "SELECT P.REPL_ORDER,P.LINE_NO__REPL,L.INV_STAT " 
	    		       +" FROM PICK_ORD_DET P,LOCATION L "
	    		       +" WHERE PICK_ORDER = ? AND LINE_NO = ? AND P.LOC_CODE = L.LOC_CODE";
	    
			pstmt1 =  conn.prepareStatement(sqlPickOrder);	
			pstmt1.setString(1, pickOrder);
			pstmt1.setString(2, lineNoPick);
			
			rs1 = pstmt1.executeQuery();
			if(rs1.next())
			{
				//locCode = rs1.getString("LOC_CODE");
				repl = rs1.getString("REPL_ORDER");
				lineRepl = rs1.getString("LINE_NO__REPL");
				invStat = rs1.getString("INV_STAT");
			}
			System.out.println("repl"+repl+"lineRepl"+lineRepl+"invStat"+invStat);
			
			if(rs1 != null)
			{
				rs1.close();
				rs1 = null;
			}
			if(pstmt1 != null)
			{  
				pstmt1.close();
				pstmt1 = null;
			}
		
			System.out.println("invStat LOCATION:::"+invStat);
			if(invStat.trim().equalsIgnoreCase(activePickLoc))
			{
				System.out.println("Original hashmap"+replHashMap.toString());
				if(replHashMap.containsKey(repl))
				{
					String repLine = replHashMap.get(repl);
					
					if(!repLine.equalsIgnoreCase(lineRepl))
					{
						replHashMap.put(repl,lineRepl);
						System.out.println("HashMap after add"+replHashMap.toString());
					}
					else
					{
						isUpdate = false;
					}
				}
				else
				{
					replHashMap.put(repl,lineRepl);
					System.out.println("HashMap else case"+replHashMap.toString());
				}
					
				
				System.out.println("HASHMAPINING VALUES"+replHashMap.toString());
				//Changed by Rohan on 11-02-13 for reduce one case from actual qty
				/*
				sqlRepl = " SELECT H.ORDER_TYPE,D.ACTUAL_QTY,D.PICK_ORDER FROM REPL_ORD_HDR H,REPL_ORD_DET D"
						+ " WHERE D.REPL_ORDER = ? AND LINE_NO = ? "
						+" AND H.REPL_ORDER = D.REPL_ORDER ";*/
				sqlRepl = " SELECT H.ORDER_TYPE,D.ACTUAL_QTY,D.PICK_ORDER,I.SHIPPER_SIZE" +
						" FROM REPL_ORD_HDR H,REPL_ORD_DET D ,ITEM_LOT_PACKSIZE I"
						+ " WHERE D.REPL_ORDER = ? AND LINE_NO = ? "
						+" AND H.REPL_ORDER = D.REPL_ORDER AND  I.ITEM_CODE = D.ITEM_CODE";
				pstmt1 =  conn.prepareStatement(sqlRepl);
				pstmt1.setString(1, repl);
				pstmt1.setString(2, lineRepl);
				rs1 = pstmt1.executeQuery();
				if(rs1.next())
				{
					orderType =  rs1.getString("ORDER_TYPE");
					actQty = rs1.getDouble("ACTUAL_QTY");
					pick = checkNull(rs1.getString("PICK_ORDER"));
					//Changed by Rohan on 11-02-13 for reduce one case from actual qty
					shipperSize = rs1.getDouble("SHIPPER_SIZE");
				}
				
				if(rs1 != null)
				{
					rs1.close();
					rs1 = null;
				}
				if(pstmt1 != null)
				{  
					pstmt1.close();
					pstmt1 = null;
				}
				System.out.println("Oredr type"+orderType);
				
				if("Q".equalsIgnoreCase(orderType.trim()))
				{
					
					sqlOrderType = "SELECT L.INV_STAT FROM REPL_ORD_DET D,LOCATION L "
							     +" WHERE (REPL_ORDER,LINE_NO) IN (SELECT PICK_ORDER,LINE_NO__PICK "
								  +" FROM REPL_ORD_DET WHERE REPL_ORDER = ? AND LINE_NO = ?) "
								  +	" AND D.LOC_CODE = L.LOC_CODE";
					pstmt1 =  conn.prepareStatement(sqlOrderType);
					pstmt1.setString(1, repl);
					pstmt1.setString(2, lineRepl);
					rs1 = pstmt1.executeQuery();
					if(rs1.next())
					{
						invstatChk =  checkNull(rs1.getString("INV_STAT"));
						
					}
					if(rs1 != null)
					{
						rs1.close();
						rs1 = null;
					}
					if(pstmt1 != null)
					{  
						pstmt1.close();
						pstmt1 = null;
					}
					System.out.println("INVSTART"+invstatChk);
					//if("".equalsIgnoreCase(pick) && "".equalsIgnoreCase(invstatChk.trim()))
					if(isUpdate)
					{
						if(!"".equalsIgnoreCase(pick) && !"".equalsIgnoreCase(invstatChk.trim()))
						{	
							if(invstatChk.trim().equalsIgnoreCase(psoLoc))
							{	
								/*
								updateSql= " UPDATE REPL_ORD_DET SET ACTUAL_QTY = ACTUAL_QTY - ?"
										   +" WHERE (REPL_ORDER,LINE_NO) IN(SELECT PICK_ORDER,LINE_NO__PICK "
										   +" FROM REPL_ORD_DET WHERE REPL_ORDER = ? AND LINE_NO = ? ) ";
								*/
								updateSql= " UPDATE REPL_ORD_DET SET ACTUAL_QTY = ACTUAL_QTY - ?"
										   +" WHERE (REPL_ORDER,LINE_NO) IN(SELECT PICK_ORDER,LINE_NO__PICK "
										   +" FROM REPL_ORD_DET WHERE REPL_ORDER = ? AND LINE_NO = ? ) " 
										   +" AND CASE WHEN CANCEL_MODE IS NULL THEN 'N' ELSE CANCEL_MODE END <> 'Y' ";
							}
							else 
							{
								/*
								updateSql= " UPDATE REPL_ORD_DET SET ACTUAL_QTY = ACTUAL_QTY - ?"
										   +" WHERE (REPL_ORDER,LINE_NO) IN(SELECT PICK_ORDER,LINE_NO__PICK "
										   +" FROM REPL_ORD_DET WHERE (REPL_ORDER,LINE_NO) " +
										   " IN (SELECT PICK_ORDER,LINE_NO__PICK  FROM REPL_ORD_DET WHERE REPL_ORDER = ? AND LINE_NO = ?)) ";
								*/
								updateSql= " UPDATE REPL_ORD_DET SET ACTUAL_QTY = ACTUAL_QTY - ?"
										   +" WHERE (REPL_ORDER,LINE_NO) IN(SELECT PICK_ORDER,LINE_NO__PICK "
										   +" FROM REPL_ORD_DET WHERE (REPL_ORDER,LINE_NO) " +
										   " IN (SELECT PICK_ORDER,LINE_NO__PICK  FROM REPL_ORD_DET WHERE REPL_ORDER = ? AND LINE_NO = ?)) "
								           +" AND CASE WHEN CANCEL_MODE IS NULL THEN 'N' ELSE CANCEL_MODE END <> 'Y' ";
	
							}
							pstmt1 =  conn.prepareStatement(updateSql);
							//Changed by Rohan on 11-02-13 for reduce one case from actual qty
							//pstmt1.setDouble(1, actQty);
							pstmt1.setDouble(1,shipperSize);
							pstmt1.setString(2, repl);
							//pstmt1.setString(3, lineNo);
							pstmt1.setString(3, lineRepl);
							
							pstmt1.executeUpdate();
							if(pstmt1 != null)
							{
								pstmt1.close();
								pstmt1 = null;
							}
						}
					}
					/*
					replOrder = repl;
					lineNo = lineRepl;
					*/
					tempReplList.add(repl);
					tempReplList.add(lineRepl);
		
				}
			}
			else if(invStat.trim().equalsIgnoreCase(casePickLoc))
			{
				sqlRepl = "SELECT PICK_ORDER,LINE_NO__PICK FROM REPL_ORD_DET WHERE REPL_ORDER = ? AND LINE_NO__PICK = ? ";
				pstmt1 =  conn.prepareStatement(sqlRepl);
				pstmt1.setString(1, repl);
				pstmt1.setString(2, lineRepl);
				rs1 = pstmt1.executeQuery();
				if(rs1.next())
				{
					
					pick = checkNull(rs1.getString("PICK_ORDER"));
					lineNoPk = checkNull(rs1.getString("LINE_NO__PICK"));
				}
				if(rs1 != null)
				{
					rs1.close();
					rs1 = null;
				}
				if(pstmt1 != null)
				{  
					pstmt1.close();
					pstmt1 = null;
				}
				
				if(!"".equalsIgnoreCase(pick))
				{
					tempReplList.add(pick);
					tempReplList.add(lineNoPk);

				}
			}
					
	    }
		catch(Exception e)
		{
			throw e;
		}
		finally
		{
			if(rs != null)
			{
				rs.close();
				rs = null;
			}
			if(pstmt != null)
			{
				pstmt.close();
				pstmt = null;
			}
		
		}
		
	return tempReplList;  	
	}
	//Changed by Rohan on 30-01-13 to set cancle mode Y in case of stock to dock.start
	private void updateCancelMode(String replOrder ,String replLineNo,String depRepl,Connection conn) throws Exception
	{	
		String sqlReplOrd = "";
		String updateReplOrd = "";
		String replOrd = "";
		String lineNoRepl = "";
		String invStat = "";
		String sqlPnd = "";
		double shipperSize = 0.0;
		 DistCommon discommon = new DistCommon();
		 
		//String pndLoc = discommon.getDisparams("999999","PND_INVSTAT",conn);
		 String deepStoreLoc = discommon.getDisparams("999999","DEEP_STORE_INVSTAT",conn);
		 String psoLoc  = discommon.getDisparams("999999","RESERV_LOCATION",conn);
		 
		PreparedStatement pstmtRepl = null;
		PreparedStatement pstmtUpadte = null;
		
		ResultSet rs = null;
		try
		{
			//updateReplOrd= "UPDATE REPL_ORD_DET SET CANCEL_MODE = 'Y' WHERE REPL_ORDER = ? AND LINE_NO = ? " ;
			
			if(!"Q".equalsIgnoreCase(depRepl))
			{	
				updateReplOrd= "UPDATE REPL_ORD_DET SET CANCEL_MODE = 'Y', ACTUAL_QTY = 0 WHERE REPL_ORDER = ? AND LINE_NO = ? " ;
				pstmtUpadte = conn.prepareStatement(updateReplOrd);
			}	
			
			
			System.out.println("depRepl:::::"+depRepl);
			//setting stock to dock repanishmnet cancel
			if("N".equalsIgnoreCase(depRepl))
			{	/*
				updateReplOrd= "UPDATE REPL_ORD_DET SET CANCEL_MODE = 'Y' WHERE REPL_ORDER = ? AND LINE_NO = ? " ;
				pstmtUpadte = conn.prepareStatement(updateReplOrd);
				*/
				pstmtUpadte.clearParameters();
				pstmtUpadte.setString(1, replOrder);
				pstmtUpadte.setString(2, replLineNo);
				int cntUpdte = pstmtUpadte.executeUpdate();
				if(cntUpdte > 0)
				{
					System.out.println("Repanishment Cancle for "+replOrder+" and Repl line no "+replLineNo);		
				}
			
				sqlReplOrd = "SELECT PICK_ORDER,LINE_NO__PICK FROM REPL_ORD_DET WHERE REPL_ORDER = ? AND LINE_NO = ? ";
				pstmtRepl = conn.prepareStatement(sqlReplOrd);
				pstmtRepl.setString(1, replOrder);
				pstmtRepl.setString(2, replLineNo);
				rs = pstmtRepl.executeQuery();
				if(rs.next())
				{
					replOrd = checkNull(rs.getString("PICK_ORDER"));
					lineNoRepl = checkNull(rs.getString("LINE_NO__PICK"));
				}
				if(!"".equalsIgnoreCase(replOrd) && replOrd.trim().length() > 0)
				{
					pstmtUpadte.clearParameters();
					pstmtUpadte.setString(1, replOrd);
					pstmtUpadte.setString(2, lineNoRepl);
					int cntUpdte1 = pstmtUpadte.executeUpdate();
					if(cntUpdte1 > 0)
					{
						System.out.println("Repanishment Cancle for "+replOrd+" and Repl line no "+lineNoRepl);		
					}
				}
			}
			else if("Y".equalsIgnoreCase(depRepl))
			{
				/*
				sqlReplOrd = "SELECT PICK_ORDER,LINE_NO__PICK,LOC_CODE " +
						" FROM REPL_ORD_DET WHERE REPL_ORDER = ? AND LINE_NO = ? ";
				
				*/
				System.out.println("replOrder:::::::>>>>>"+replOrder+"replLineNo"+replLineNo);
				/*
				sqlReplOrd = " SELECT D.REPL_ORDER,D.LINE_NO,L.INV_STAT "
				            +" FROM REPL_ORD_DET D,LOCATION L WHERE D.PICK_ORDER = ? AND D.LINE_NO__PICK = ? AND D.LOC_CODE = L.LOC_CODE ";
				*/
				sqlReplOrd = " SELECT D.PICK_ORDER,D.LINE_NO__PICK,L.INV_STAT "
			            +" FROM REPL_ORD_DET D,LOCATION L WHERE D.REPL_ORDER = ? AND D.LINE_NO = ? AND D.LOC_CODE = L.LOC_CODE ";
				
				pstmtRepl = conn.prepareStatement(sqlReplOrd);
				pstmtRepl.setString(1, replOrder);
				pstmtRepl.setString(2, replLineNo);
				rs = pstmtRepl.executeQuery();
				if(rs.next())
				{
					System.out.println("Inside case pick");
					replOrd = checkNull(rs.getString("PICK_ORDER"));
					System.out.println("replOrd*8*"+replOrd);
					lineNoRepl = checkNull(rs.getString("LINE_NO__PICK"));
					System.out.println("lineNoRepl*8*"+lineNoRepl);
					invStat = checkNull(rs.getString("INV_STAT"));
					System.out.println("replOrd>>"+replOrd+"lineNoRepl:::::"+lineNoRepl+"invStat"+invStat);
				}
				if(rs != null)
				{
					rs.close();
					rs = null;
				}
				if(pstmtRepl != null)
				{
					pstmtRepl.close();
					pstmtRepl = null;
				}
				System.out.println("deepStoreLoc"+deepStoreLoc+"invStat"+invStat);
				if(deepStoreLoc.trim().equalsIgnoreCase(invStat.trim()) || psoLoc.trim().equalsIgnoreCase(invStat.trim()))
				{
					//SELECT REPL_ORDER,LINE_NO FROM REPL_ORD_DET WHERE PICK_ORDER = ? AND LINE_NO__PICK = ?
					sqlPnd = "SELECT REPL_ORDER,LINE_NO  FROM REPL_ORD_DET WHERE PICK_ORDER = ? AND LINE_NO__PICK = ? ";
					
					pstmtRepl = conn.prepareStatement(sqlPnd);
					pstmtRepl.setString(1, replOrder);
					pstmtRepl.setString(2, replLineNo);
					rs = pstmtRepl.executeQuery();
					if(rs.next())
					{
						replOrd = checkNull(rs.getString("REPL_ORDER"));
						System.out.println("replOrd*8*"+replOrd);
						lineNoRepl = checkNull(rs.getString("LINE_NO"));
					}
				}
				if(!"".equalsIgnoreCase(replOrd.trim()))
				{
					pstmtUpadte.clearParameters();
					pstmtUpadte.setString(1, replOrd);
					pstmtUpadte.setString(2, lineNoRepl);
					int cntUpdte1 = pstmtUpadte.executeUpdate();
					if(cntUpdte1 > 0)
					{
						System.out.println("Repanishment Cancle for "+replOrd+" and Repl line no "+lineNoRepl);		
					}
				}
			}
			else if("Q".equalsIgnoreCase(depRepl))
			{
				sqlReplOrd = "SELECT L.INV_STAT,I.SHIPPER_SIZE FROM REPL_ORD_DET D,LOCATION L,ITEM_LOT_PACKSIZE I "
					     +" WHERE (REPL_ORDER,LINE_NO) IN (SELECT PICK_ORDER,LINE_NO__PICK "
						  +" FROM REPL_ORD_DET WHERE REPL_ORDER = ? AND LINE_NO = ?) "
						  +	" AND D.LOC_CODE = L.LOC_CODE AND D.ITEM_CODE = I.ITEM_CODE ";
				pstmtRepl =  conn.prepareStatement(sqlReplOrd);
				pstmtRepl.setString(1, replOrder);
				pstmtRepl.setString(2, replLineNo);
				rs = pstmtRepl.executeQuery();
				if(rs.next())
				{
					invStat =  checkNull(rs.getString("INV_STAT"));
					shipperSize = rs.getDouble("SHIPPER_SIZE");
					
				}
				if(rs != null)
				{
					rs.close();
					rs = null;
				}
				if(pstmtRepl != null)
				{  
					pstmtRepl.close();
					pstmtRepl = null;
				}
				System.out.println("INVSTART"+invStat);
		
				if(invStat.trim().equalsIgnoreCase(psoLoc))
				{	
					/*updateReplOrd= " UPDATE REPL_ORD_DET SET ACTUAL_QTY = ACTUAL_QTY + ?"
							   +" WHERE (REPL_ORDER,LINE_NO) IN(SELECT PICK_ORDER,LINE_NO__PICK "
							   +" FROM REPL_ORD_DET WHERE REPL_ORDER = ? AND LINE_NO = ? ) ";*/
					updateReplOrd= " UPDATE REPL_ORD_DET SET ACTUAL_QTY = ACTUAL_QTY + ? "
							   +" WHERE (REPL_ORDER,LINE_NO) IN(SELECT PICK_ORDER,LINE_NO__PICK "
							   +" FROM REPL_ORD_DET WHERE REPL_ORDER = ? AND LINE_NO = ? ) " 
							   +" AND CASE WHEN CANCEL_MODE IS NULL THEN 'N' ELSE CANCEL_MODE END <> 'Y' ";
					
				}
				else 
				{
					/*updateReplOrd= " UPDATE REPL_ORD_DET SET ACTUAL_QTY = ACTUAL_QTY + ?"
							   +" WHERE (REPL_ORDER,LINE_NO) IN(SELECT PICK_ORDER,LINE_NO__PICK "
							   +" FROM REPL_ORD_DET WHERE (REPL_ORDER,LINE_NO) " +
							   " IN (SELECT PICK_ORDER,LINE_NO__PICK  FROM REPL_ORD_DET WHERE REPL_ORDER = ? AND LINE_NO = ?)) ";*/
					updateReplOrd= " UPDATE REPL_ORD_DET SET ACTUAL_QTY = ACTUAL_QTY + ?"
							   +" WHERE (REPL_ORDER,LINE_NO) IN(SELECT PICK_ORDER,LINE_NO__PICK "
							   +" FROM REPL_ORD_DET WHERE (REPL_ORDER,LINE_NO) " +
							   " IN (SELECT PICK_ORDER,LINE_NO__PICK  FROM REPL_ORD_DET WHERE REPL_ORDER = ? AND LINE_NO = ?)) "
					           +" AND CASE WHEN CANCEL_MODE IS NULL THEN 'N' ELSE CANCEL_MODE END <> 'Y' ";

				}
				pstmtUpadte =  conn.prepareStatement(updateReplOrd);
				pstmtUpadte.setDouble(1,shipperSize);
				pstmtUpadte.setString(2, replOrder);
				pstmtUpadte.setString(3, replLineNo);
				
				pstmtUpadte.executeUpdate();
				if(pstmtUpadte != null)
				{
					pstmtUpadte.close();
					pstmtUpadte = null;
				}
				
			}

        }
		catch(Exception e)
		{
			throw new ITMException(e);
		}
		finally
		{
			
			if(pstmtRepl != null)
			{
				pstmtRepl.close();
				pstmtRepl = null;
			}
			if(pstmtUpadte != null)
			{
				pstmtUpadte.close();
				pstmtUpadte = null;
			}
			if(rs != null)
			{
				rs.close();
				rs = null;
			}
		
		}
	  	
	}
	//Changed by Rohan on 30-01-13 to set cancle mode Y in case of stock to dock.end
 
}
