/**
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 = "";
		String  invStatLoc = "";
		String invPndReserve = "";
		String sqlLoc = "";
		
		//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 );
			invPndReserve = discommon.getDisparams("999999","PND_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
						//Changed by Rohan on 18-03-13 for for select Quantity as Quantity - Dealloc_qty
						//replDet ="SELECT ITEM_CODE,LINE_NO,LOC_CODE,LOT_NO,LOT_SL,QUANTITY,"
						replDet ="SELECT ITEM_CODE,LINE_NO,LOC_CODE,LOT_NO,LOT_SL,(QUANTITY - CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE DEALLOC_QTY END) AS 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
						//Changed by Rohan on 18-03-13 for for select Quantity as Quantity - Dealloc_qty
						//pickDet ="SELECT ITEM_CODE,LINE_NO,LOC_CODE,LOT_NO,LOT_SL,QUANTITY," +
						pickDet ="SELECT ITEM_CODE,LINE_NO,LOC_CODE,LOT_NO,LOT_SL,(QUANTITY - CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE DEALLOC_QTY END) AS 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") );
						System.out.println("quantity to Deallocate::::"+quantity);
						//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
						 */
						//Changed by Rohan on 21-03-13 for not call wave Cancellation
						System.out.println("Quantity"+quantity+"refSer"+refSer);
						if(quantity > 0 || !(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"))
							{	
								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);
									//Changed by Rohan on 23-02-13 for not updating actual qty if repanishment is confirm
									//updateRecord = updateReplanishment(replOrder,replLineNo,pickOrder,pickLineNo,quantity,true,waveID,replOrdOrg,replLineOrg,conn);
									updateRecord = updateReplanishment(replOrder,replLineNo,pickOrder,pickLineNo,quantity,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);
											//Changed by Rohan on 23-02-13 for not updating actual qty if repanishment is confirm
											//updateRecord = updateReplanishment(replOrder,replLineNo,pickOrder,pickLineNo,quantity,true,waveID,replOrdOrg,replLineOrg,conn);
											//Changed by Rohan on 26-03-13 not allow to deallocate form pnd location.start
											updateRecord = updateReplanishment(replOrder,replLineNo,pickOrder,pickLineNo,quantity,waveID,replOrdOrg,replLineOrg,conn);
											/*
											sqlLoc = "SELECT INV_STAT FROM LOCATION WHERE LOC_CODE = ? ";
											pstmtDesp = conn.prepareStatement(sqlLoc);
											pstmtDesp.setString(1,locCode);
											rsDesp = pstmtDesp.executeQuery();
											if(rsDesp.next())
											{
												invStatLoc = rsDesp.getString("INV_STAT");
											}											
										
											if(pstmtDesp != null)
											{
												pstmtDesp.close();
												pstmtDesp = null;
											}
											if(rsDesp != null)
											{
												rsDesp.close();
												rsDesp = null;
											}
											if(!invStatLoc.trim().equals(invPndReserve.trim()))
											{	
												updateRecord = updateReplanishment(replOrder,replLineNo,pickOrder,pickLineNo,quantity,waveID,replOrdOrg,replLineOrg,conn);
											}
											*/
											//Changed by Rohan on 26-03-13 not allow to deallocate form pnd location.end
										}
										//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);
												//Changed by Rohan on 23-02-13 for not updating actual qty if repanishment is confirm
												//updateRecord = updateReplanishment(replOrder,replLineNo,pickOrder,pickLineNo,quantity,false,waveID,replOrdOrg,replLineOrg,conn);
												updateRecord = updateReplanishment(replOrder,replLineNo,pickOrder,pickLineNo,quantity,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);
											 //Changed by Rohan on 23-02-13 for not updating actual qty if repanishment is confirm
											 //updateRecord = updateReplanishment(replPickOrder,replPickLineNo,pickOrder,pickLineNo,quantity,false,waveID,replOrdOrg,replLineOrg,conn);
											 updateRecord = updateReplanishment(replPickOrder,replPickLineNo,pickOrder,pickLineNo,quantity,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);
							//Changed by Rohan on 26-03-13 not allow to deallocate form pnd location.end
							//if(!invStatLoc.trim().equals(invPndReserve.trim()))
							//{	
							
								
								//Chnaged by Rohan on 20-06-13 for bug fixing.start
							    //lineNoSord = "  " + lineNoSord; 
							    lineNoSord = "   " + lineNoSord; 
							    lineNoSord = lineNoSord.substring(lineNoSord.length()-3);
							    //Chnaged by Rohan on 20-06-13 for bug fixing.end
							    System.out.println("Line No:["+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
								System.out.println("qtyAlloc["+qtyAlloc+"]");
								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!");
									}
									//Changed by Rohna on 26-03-13 for update wave cancel at end.start
									/*
									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
							  //Changed by Rohna on 26/03/13 for wave Cancellation error if qty <= 0.start
							  //else
							  //{
								  //retString = itmDBAccess.getErrorString("","NTWAVEREV","");
								  
							  //}
							  //Changed by Rohna on 26/03/13 for wave Cancellation error if qty <= 0.end
							
						}
							
					 }
				 }
			 }//while close
			
			//changed by Rohan on 26/03/13 for updating wave status.start
			System.out.println("Error in wave Cancel"+retString);
			if(("".equalsIgnoreCase(retString) || retString != null)  && retString.trim().length() == 0)
			{	
				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;
				}
				
				
			}	
			//changed by Rohan on 26/03/13 for updating wave status.end	
			
		}
		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;
					}
					
				}
				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
	//Changed by Rohan on 23-02-13 for not updating actual qty if repanishment is confirm 
	//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
	public int updateReplanishment(String replOrder,String lineNo,String pickOrder,String lineNoPick,double quantity,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 sqlReplWave ="";
		String waveIDCmp = "";
		String sqlAct ="";
		String sqlPickWave ="";
		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;
		//Chnaged by Rohan on 08-03-13 for define variables
        boolean isReplConf = false;
		PreparedStatement pstmt2 = null;
		ResultSet rs2 = null;
		boolean isDepRepl = false;
		boolean isPndRepl = 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 ="";
		String checkInvStat = "";
		//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);
	    String pndLoc = discommon.getDisparams("999999","PND_INVSTAT",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;
			}
			
			
			//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 = ?";
			sqlReplConf = "SELECT COUNT(*) AS COUNT FROM REPL_ISS_HDR WHERE REPL_ORDER = ? AND CONFIRMED = 'Y' ";
			pstmt2 =  conn.prepareStatement(sqlReplConf);
			
			//Changed by Rohan on 06-03-13 for checking pnd repalinshment
			//sql = "SELECT PICK_ORDER,LINE_NO__PICK FROM REPL_ORD_DET WHERE REPL_ORDER = ? AND LINE_NO = ? ";
			sql = "SELECT R.PICK_ORDER,R.LINE_NO__PICK,L.INV_STAT FROM REPL_ORD_DET R,LOCATION L " +
					"WHERE R.REPL_ORDER = ? AND R.LINE_NO = ? AND L.LOC_CODE = R.LOC_CODE ";
			pstmt1 =  conn.prepareStatement(sql);
			
			replOrd1 = replOrdOrg;
			replLine1 = replLineOrg;
			do
			{	
				//Changed by Rohan on 26-03-13 for p
				//isReplConf = false;
				pstmt2.setString(1, replOrd1);
				//pstmt2.setString(2, replLine1);
				rs2 = pstmt2.executeQuery();
				if(rs2.next())
				{
					cnt = rs2.getInt("COUNT");
				}
				if(cnt > 0)
				{
					//Changed by Rohan on 08-03-13 for checking replanishment confirm or not.start
					/*
					isDepRepl = true;
					break;
					*/
					isReplConf = true;
					//isPndRepl = false;
					System.out.println("is pnd false and is repl conf");
					break;
					//Changed by Rohan on 08-03-13 for checking replanishment confirm or not.end
				}
				//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");
					checkInvStat = checkNull(rs1.getString("INV_STAT"));
				}
				//to check dso-pnd repanishment exist.end
				
				//Changed by Rohan on 08-03-13 for checking replanishment confirm or not.start
				System.out.println("checkInvStat"+checkInvStat+"pndLoc"+pndLoc);
				if(!"".trim().equalsIgnoreCase(replOrd1) && checkInvStat.trim().equalsIgnoreCase(pndLoc.trim()))
				{
					isPndRepl = true;
					System.out.println("isPndRepl["+isPndRepl+"]");
				}
				//Changed by Rohan on 08-03-13 for checking replanishment confirm or not.end
				
				pstmt1.clearParameters();
				pstmt2.clearParameters();
				
			}
			//Chnaged by Rohan on 29-03-13 for check invstat not null
			while(!"".equalsIgnoreCase(replOrd1) && !"".equalsIgnoreCase(checkInvStat) && checkInvStat.trim().equalsIgnoreCase(pndLoc.trim()));
			
			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
			
			//Changed by Rohan on 04-03-13 for check Depended repanishment.start
			
			sqlReplWave = " SELECT W.WAVE_ID FROM PICK_ORD_HDR H , PICK_ORD_DET D ,WAVE_TASK W,WAVE_TASK_DET T "
						 + " WHERE H.PICK_ORDER = D.PICK_ORDER AND D.REPL_ORDER = ? AND LINE_NO__REPL = ? "
                         + " AND W.CANCEL <> 'Y' AND T.WAVE_ID = W.WAVE_ID AND H.WAVE_ID = W.WAVE_ID AND " 
                         + " T.REF_ID = H.PICK_ORDER AND W.WAVE_ID <> ? ";
			
			pstmt1 =  conn.prepareStatement(sqlReplWave);
			pstmt1.setString(1,replOrdOrg);
			pstmt1.setString(2,replLineOrg);
			pstmt1.setString(3,waveID);
			rs1 = pstmt1.executeQuery();
			if(rs1.next())
			{
				System.out.println(" :::::Pick Depended :::::::");
				isDepRepl = true;
				
			}
			else
			{
				
				sqlPickWave = "SELECT W.WAVE_ID FROM REPL_ORD_HDR H , REPL_ORD_DET D ,WAVE_TASK	W,WAVE_TASK_DET T "
						 +" WHERE H.REPL_ORDER = D.REPL_ORDER AND D.PICK_ORDER = ? AND LINE_NO__PICK = ? "
						 +"	AND W.CANCEL <> 'Y'AND T.WAVE_ID = W.WAVE_ID AND H.WAVE_ID = W.WAVE_ID "
						 +"	AND T.REF_ID = H.REPL_ORDER AND W.WAVE_ID <> ? ";
				
				pstmt2 =  conn.prepareStatement(sqlPickWave);
				pstmt2.setString(1,replOrdOrg);
				pstmt2.setString(2,replLineOrg);
				pstmt2.setString(3,waveID);
				rs2 = pstmt2.executeQuery();
				if(rs2.next())
				{
					System.out.println(":::::::::Repanishment Depended ::::");
					isDepRepl = true;
				
				}
			}
			
			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 04-03-13 for check Depended repanishment.start
			System.out.println("Repanishement Confirm["+isReplConf+"]Is pick dependent["+isDepRepl+"]"+"isPndRepl]"+isPndRepl);
			if(isDepRepl)
			{
				//Changed by Rohan on 23-02-13 for not updating actual qty if repanishment is confirm.start
				
			    //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()))
										{
											//Changed by Rohan on 22-03-13 for adding one shipper into actual qty
											/*
											updateSql= " UPDATE REPL_ORD_DET SET ACTUAL_QTY = ACTUAL_QTY - ?"
													   +" WHERE REPL_ORDER = ? AND LINE_NO = ? ";
											*/
				/*
											updateSql= " UPDATE REPL_ORD_DET SET ACTUAL_QTY = ACTUAL_QTY + ?"
													   +" WHERE REPL_ORDER = ? AND LINE_NO = ? ";
										}
										else
										{
											//Changed by Rohan on 22-03-13 for adding one shipper into actual qty
											/*
											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 = ?)";
											
										}
										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
				{
				*/	
					
				//}
				
				System.out.println("isReplConf[[["+isReplConf+"]isPndRepl["+isPndRepl);
				//Changed by rohan on 28/03/13 for updating actaul qty if dso-pnd repanishmnet sacn
				//if(isReplConf)
				if(!isReplConf || isPndRepl)
				{	
					ArrayList ReplActQty = updateReplActualQty(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;
					}
					
					System.out.println("Updating Final Repl Order:::::"+replOrder+"ANd Line No"+lineNo);
					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);
				
				System.out.println("repl type:::"+replType);
				if("Q".equalsIgnoreCase(replType))
				{		
					pstmt.setString(1, replOrdOrg);
					pstmt.setString(2, replLineOrg);
				}
				else
				{
					pstmt.setString(1, replOrder);
					pstmt.setString(2, lineNo);
				}
				count = pstmt.executeUpdate();
				System.out.println("replOrdOrg>>"+replOrdOrg+"replLineOrg<<<"+replLineOrg);
				if(!"Q".equalsIgnoreCase(replType) && !"C".equalsIgnoreCase(replType))
				{
					//Update dependent Repl
					updateCancelMode(replOrdOrg,replLineOrg,"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 updateReplActualQty(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 = "";
		String invStatDso ="";
		String updateSql = "";
		
		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;
		PreparedStatement pstmtUpadtePnd = 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 D.PICK_ORDER,D.LINE_NO__PICK FROM REPL_ORD_DET D WHERE REPL_ORDER = ? AND LINE_NO = ? ";
				
				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())
				{
					replOrd = checkNull(rs.getString("PICK_ORDER"));
					lineNoRepl = checkNull(rs.getString("LINE_NO__PICK"));
					invStatDso = checkNull(rs.getString("INV_STAT"));
				}
				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(invStatDso.trim().equalsIgnoreCase(deepStoreLoc.trim()))
				{
					
					updateSql = "UPDATE REPL_ORD_DET SET CANCEL_MODE = 'Y', ACTUAL_QTY = 0 WHERE PICK_ORDER = ? AND LINE_NO__PICK = ?";
					pstmtUpadtePnd = conn.prepareStatement(updateSql);
					pstmtUpadtePnd.setString(1, replOrder);
					pstmtUpadtePnd.setString(2, replLineNo);
					pstmtUpadtePnd.executeUpdate();	
					
				}
			}
			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"));
					}
				}
				else if(pndLoc.trim().equalsIgnoreCase(invStat.trim()))
				{
					System.out.println("Enetr in Pnd Repanishemrnt zero");
					//set cancel mode Y and actual qty 0  of an pnd repl repanishment 
					pstmtUpadte.clearParameters();
					pstmtUpadte.setString(1, replOrder);
					pstmtUpadte.setString(2, replLineNo);
					int cntUpdte1 = pstmtUpadte.executeUpdate();
					if(cntUpdte1 > 0)
					{
						System.out.println("Repanishment Cancle for "+replOrder+" and Repl line no "+replLineNo);		
					}
				}
					
				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))
			{
				//Changed by rohan on 23-03-13 for not update actual qty zero if parent repanishment is scan.start
				sqlReplOrd = "";
				//Changed by rohan on 23-03-13 for not update actual qty zero if parent repanishment is scan.end
				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' ";
					*/		  
					updateReplOrd= " UPDATE REPL_ORD_DET D 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' " 
							   +" AND D.REPL_ORDER NOT IN (SELECT REPL_ORDER FROM REPL_ISS_HDR WHERE CONFIRMED = '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' ";
					*/
					updateReplOrd= " UPDATE REPL_ORD_DET D 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' "
					           +" AND D.REPL_ORDER NOT IN (SELECT REPL_ORDER FROM REPL_ISS_HDR WHERE CONFIRMED = '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(pstmtUpadtePnd != null)
			{
				pstmtUpadtePnd.close();
				pstmtUpadtePnd = 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
 
}
