/**
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.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.ObjectInput;
import java.io.ObjectInputStream;
import java.net.URL;
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 java.util.HashSet;
import java.util.Set;

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

import com.adsionline.ADSIShippingWS.ADSIShippingLocator;
import com.adsionline.ADSIShippingWS.ADSIShippingSoap;
import com.adsionline.ADSIShippingWS.Service;
import com.adsionline.ADSIShippingWS.ShipmentResult2;
import com.adsionline.ADSIShippingWS.ShipmentResultReturn2;
import com.adsionline.ADSIShippingWS.StringReturn;

@Stateless 
public class WaveCancelEJB extends ActionHandlerEJB implements WaveCancelEJBLocal, WaveCancelEJBRemote 
{
	HashMap<String,String> replHashMap = new HashMap<String,String>();
	//changed by sankara on 08/05/14 for update calcel_mode in repl_ord_det through wave cancel
	//HashMap<String,String> manStkReplMap = 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,pstmtSord = 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 = "",sSQLUpdatSord = "";
		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 30-05-13 for 
		long msnNo = 0;
   	    String response  = "";
   	    String serviceCode = "";
		//Changed by Rohan on 22-01-13 to cancle independent Repanishment.end
   	    String repalnishmentOrd = "";
   	    String repalnishmentline = "";
   	    String sqlMan = "";
		PreparedStatement pstmtMan = null;
		ResultSet rsMan = null;
		String sqlMan1 = "";
		PreparedStatement pstmtMan1 = null;
		ResultSet rsMan1 = null;
		repalnishmentOrd = replOrder;
		repalnishmentline = replLineNo;
		//changed by sankara on 08/05/14 for update calcel_mode in repl_ord_det through wave cancel
		//boolean isManualReplMap = false;
   	    boolean isManualStock = false;
   	   //changed by sankara on 08/05/14 for update calcel_mode in repl_ord_det through wave cancel
   	    //manStkReplMap.clear();
		PreparedStatement pstmtTask = null;
		ResultSet rsTask = null;
		PreparedStatement pstmtInv = null;
		ResultSet rsInv = null;
		//Changed by Rohan on 16-07-13 for define map of ptcn for void packages
		HashMap<String,String> ptcnVoidPackagesMap = new HashMap<String,String>();
		
		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();
		ADSIShippingSoap  soap = null;
		int updateSorder = 0;
		//Changed By Pragyan 18-DEC-13 To check ADSI Connection is required.(KB/Ashish mail Dt.121413) 
		String isADSIConnReq = "N";
		
		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);
			//isADSIConnReq = discommon.getDisparams("999999","CONN_TO_ADSI_APP",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();
			
			sSQLUpdatSord = "UPDATE SORDER SET SHIP_STATUS = ? WHERE SALE_ORDER = ? AND SHIP_STATUS <> ?";
			pstmtSord = conn.prepareStatement(sSQLUpdatSord);
			
			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") );
				//Changed by Rohan on 30-05-13 for get ptcn 
				ptcn = checkNull( rsWave.getString("PTCN") );
				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") );
						lineNoSord = "   " + lineNoSord; 
						lineNoSord = lineNoSord.substring(lineNoSord.length()-3);
						System.out.println("quantity to Deallocate::::"+quantity);
						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"));
							
							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 sankara on 08/05/14 for update calcel_mode in repl_ord_det through wave cancel start.
						//Creating map of repanishment which contains manual repanishment.start
						/*if(!isManualReplMap)
						{	
							System.out.println("*******going for populating Manual Map*************");
							sqlMan1 = "SELECT PICK_ORDER,LINE_NO__PICK FROM REPL_ORD_DET WHERE REPL_ORDER = ? AND LINE_NO = ? ";
							pstmtMan1 = conn.prepareStatement(sqlMan1);
							
							sqlMan =  "SELECT REPL_ORDER,LINE_NO__REPL FROM PICK_ORD_DET WHERE (SALE_ORDER,LINE_NO__SORD) IN(SELECT SALE_ORDER,LINE_NO " +
									"FROM SORDALLOC WHERE ALLOC_MODE ='M' AND (SALE_ORDER,LINE_NO) IN(SELECT SALE_ORDER,LINE_NO FROM PICK_ORD_DET " +
									"WHERE PICK_ORDER IN (SELECT REF_ID FROM WAVE_TASK_DET WHERE WAVE_ID = ? AND REF_SER IN ('C-PICK','M-PICK','A-PICK','P-PICK'))))";
							pstmtMan = conn.prepareStatement(sqlMan);
							pstmtMan.setString(1,waveID);
							rsMan = pstmtMan.executeQuery();
							while(rsMan.next())
							{
								repalnishmentOrd = checkNull(rsMan.getString("REPL_ORDER"));
								repalnishmentline = checkNull(rsMan.getString("LINE_NO__REPL"));
								System.out.println("Original repalnishmentOrd"+repalnishmentOrd+"repalnishmentline"+repalnishmentline);
								if(!"".equalsIgnoreCase(repalnishmentOrd.trim()) && repalnishmentOrd.trim().length() > 0)
								{	
									manStkReplMap.put(repalnishmentOrd.trim()+":"+repalnishmentline.trim(),"M");
									System.out.println("Origianl manaula map"+manStkReplMap.toString());
									do
									{	
										System.out.println("******  Enter In do *******************");
										pstmtMan1.setString(1,repalnishmentOrd);
										pstmtMan1.setString(2,repalnishmentline);
										rsMan1 = pstmtMan1.executeQuery();
										while(rsMan1.next())
										{
											repalnishmentOrd = checkNull(rsMan1.getString("PICK_ORDER"));
											repalnishmentline = checkNull(rsMan1.getString("LINE_NO__PICK"));
											if(!"".equalsIgnoreCase(repalnishmentOrd.trim()) && repalnishmentOrd.trim().length() > 0)
											{	
												manStkReplMap.put(repalnishmentOrd.trim()+":"+repalnishmentline.trim(),"M");
											}	
											System.out.println("Afer adding to map depe************["+manStkReplMap.toString());
										}
										pstmtMan.clearParameters();
										pstmtMan.clearParameters();
									}	
									while(!"".equalsIgnoreCase(repalnishmentOrd.trim()) && repalnishmentOrd.trim().length() > 0);
								}
								
								
								
							}
							isManualReplMap = true;
						}	
						System.out.println("Final manual stock Map:::"+manStkReplMap.toString()+"[isManualStock"+isManualStock+"]");
						//Creating map of repanishment which contains manual repanishment.end
						//Changed by Rohan on 01-08-13 to check sale order line item is manually allocate.If stk is manual allocate then do not deallacate qty for line item.
						if(isManualReplMap)
						{	
							isManualStock = isManualAllocStock(saleOrder,lineNoSord,conn);
						}  */
							
						isManualStock = isManualAllocStock(saleOrder,lineNoSord,conn);
						System.out.println("isManualStock:::"+isManualStock);
						//changed by sankara on 08/05/14 for update calcel_mode in repl_ord_det through wave cancel end
						
						//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);
						//Changed by Rohan on 01-08-13 to check sale order line item is manually allocate.If stk is manual allocate then do not deallacate qty for line item.
						//if(quantity > 0 || !(refSer.trim().equalsIgnoreCase("S-DOC") || refSer.equalsIgnoreCase("RP-PND") || refSer.equalsIgnoreCase("RP-PSO")))
						//changed by sankara on 08/05/14 for manual stock alloc case wave_cancel update in repl ord det
						//if((quantity > 0 || !(refSer.trim().equalsIgnoreCase("S-DOC") || refSer.equalsIgnoreCase("RP-PND") || refSer.equalsIgnoreCase("RP-PSO"))) && !isManualStock)
						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()))
							//{	
							
							//Changed by Rohan on 21-06-13 for bug fixing.start
						    //lineNoSord = "  " + lineNoSord; 
						    lineNoSord = "   " + lineNoSord; 
						    lineNoSord = lineNoSord.substring(lineNoSord.length()-3);
						    //Chnaged by Rohan on 21-06-13 for bug fixing.end
								
								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+"]");
								//changed by sankara on 08/05/14 for update calcel_mode in repl_ord_det through wave cancel in manual tock alloc 
								if(!isManualStock)
								{
									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 sankara on 08/05/14 for update calcel_mode in repl_ord_det through wave cancel

							  //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
							
						}
							
					 }
				 }
				//Changed by Rohan on 30-05-13 for void packages duning master pick and parcel picking.start
				System.out.println("RefSeries checking for void packages"+refSer+"}");
				
				System.out.println("PTCN LIST"+ptcnVoidPackagesMap.toString()+"}");
				//changed by sankara on 25/08/14 for update active repl quantity to normal repl in repl_ord_det at wave calcel start.
				if( "RD-DSA".equalsIgnoreCase(refSer) || "RR-DSA".equalsIgnoreCase(refSer) )
				{
					System.out.println("calling repl ord det update");
					updateReplorderqty(refId,refSer ,conn);
				}
				//changed by sankara on 25/08/14 for update active repl quantity to normal repl in repl_ord_det at wave calcel end.
				
				//isADSIConnReq
				//Changed by Rohan on 16-07-13 for define map of ptcn for void packages.start
				if(!(ptcnVoidPackagesMap.containsKey(ptcn)) && isTrackingNoExist(ptcn,conn))
				///if("Y".equalsIgnoreCase(isADSIConnReq) && !(ptcnVoidPackagesMap.containsKey(ptcn)) && isTrackingNoExist(ptcn,conn))
				 {
					/* if(refSer.equalsIgnoreCase("M-PICK") || refSer.equalsIgnoreCase("P-PICK") )
					 {*/
							 
						 ptcnVoidPackagesMap.put(ptcn,refSer);
						 //Changed by Rohan on 16-07-13 for define map of ptcn for void packages.end 
						 String serviceSymbol = "";
						 String sqlSymbol = "";
						 PreparedStatement pstmt1 = null;
						 ResultSet rs1 = null;
						 String adsiShippingURL = discommon.getDisparams("999999","WMS_ADSI_SOAP_URL",conn);
						 ADSIShippingLocator locator = new ADSIShippingLocator();
				    	 //ADSIShippingSoap  soap = locator.getADSIShippingSoap(new URL("http://172.20.0.137/ADSIShippingWS/ADSIShipping.asmx"));
						long timeBefore = System.currentTimeMillis();
						locator = new ADSIShippingLocator();
						if(soap == null)
						{
							System.out.println("Connecting ADSI Server.......");
							soap = locator.getADSIShippingSoap(new URL(adsiShippingURL));
						}
				    	long timeAfter = System.currentTimeMillis();
				    	System.out.println("Differance In Tme for connecting to ADSI Server time differance in seconds["+(timeAfter-timeBefore)/1000+"]");				    					    	 
				    	
				    	if(soap.testADSIServer())
				    	{
					    	 /*if(refSer.equalsIgnoreCase("M-PICK"))
					    	 {	 
					    		// sql = "SELECT MSN_NO,CARRIER_SYMBOL FROM CARTON_MASTER WHERE PTCN = ? AND REF_SER = 'M-PACK' AND CARTON_TYPE = 'M'";
					    		 sql = "SELECT MSN_NO,CARRIER_SYMBOL FROM CARTON_MASTER WHERE PTCN = ? AND REF_SER IN ('M-PACK','A-PICK') AND CARTON_TYPE IN('M','A') AND TRACKING_NO IS NOT NULL";
					    	 }
					    	 else
					    	 {
					    		 //sql = "SELECT MSN_NO,CARRIER_SYMBOL FROM CARTON_MASTER WHERE PTCN = ? AND REF_SER='P-PICK' AND CARTON_TYPE= 'P' ";
					    		 sql = "SELECT MSN_NO,CARRIER_SYMBOL FROM CARTON_MASTER WHERE PTCN = ? AND REF_SER IN ('P-PICK','A-PICK') AND CARTON_TYPE IN('P','A') AND TRACKING_NO IS NOT NULL";
					    	 }*/
				    		
				    		//changed by sankara on 18/06/14 consider carton master status 
				    		//sql = "SELECT MSN_NO,CARRIER_SYMBOL FROM CARTON_MASTER WHERE PTCN = ? AND MSN_NO > 0  AND TRACKING_NO IS NOT NULL";
				    		sql = "SELECT MSN_NO, CARRIER_SYMBOL FROM CARTON_MASTER WHERE PTCN = ? AND MSN_NO > 0  AND TRACKING_NO IS NOT NULL AND STATUS <> 'R' ";
							pstmt =  conn.prepareStatement(sql);	
							pstmt.setString(1, ptcn);
							rs = pstmt.executeQuery();
							 
							 while(rs.next())
							 {
								 msnNo = rs.getLong("MSN_NO");
								 serviceSymbol = checkNull(rs.getString("CARRIER_SYMBOL"));
								
					  	         System.out.println("Service Symbol final"+serviceSymbol);
					  	         System.out.println("Service Symbol for void packages:["+serviceSymbol+"]:MSN NO:["+msnNo+"]");
						    	 StringReturn testReturn = soap.voidPackage(serviceSymbol,msnNo);
								 response =  testReturn.getResponse();
								 System.out.println("response ["+response+"]");
								 
								 if(!response.contains("No Error"))
								 {
									//changed by sankara on 20/06/14 for void package wave cancel
								  /* isError = true;
									 retString = getError(response,"NTVOIDPKGS",conn);
									 return retString;*/
									 
								 }
								
							 }
							 
							 
							 if(rs != null)
							 {
								 rs.close();
								 rs = null;
							 }
							 if(rs1 != null)
							 {
								 rs1.close();
								 rs1 = null;
							 }
							 if(pstmt1 != null)
							 {
								 pstmt1.close();
								 pstmt1 = null;
							 }
							 if(pstmt != null)
							 {
								 pstmt.close();
								 pstmt = null;
							 }
						 }
				    	else
				    	{
				    		System.out.println("---ADSI Server Not Connected throw error---");
				    		isError = true;
				    		retString = itmDBAccess.getErrorString("","ADSISRVERR","","",conn);
			    			return retString;
				    	}
				    	
					 //}
					 
					/*if(!isOrderIsInProcess(saleOrder, ptcn, conn))
					{
					 
						//Changed By Pragyan WM3DSUN015 To update status in Sorder as InProcess after .start
						pstmtSord.setString(1, "O");
						pstmtSord.setString(2, saleOrder);
						pstmtSord.setString(3, "C");
						
						updateSorder = pstmtSord.executeUpdate();
						
						if(updateSorder > 0)
						{
							System.out.println(" SORDER UPDATE COUNT ["+updateSorder+"]");
						}
						pstmtSord.clearParameters();
						//Changed By Pragyan WM3DSUN015 To update status in Sorder as InProcess after .end
					}*/
		        	 	
			    }//if close
				
				if(!isOrderIsInProcess(saleOrder, ptcn, conn))
				{
					 
						//Changed By Pragyan WM3DSUN015 To update status in Sorder as InProcess after .start
						pstmtSord.setString(1, "O");
						pstmtSord.setString(2, saleOrder);
						pstmtSord.setString(3, "C");
						
						updateSorder = pstmtSord.executeUpdate();
						
						if(updateSorder > 0)
						{
							System.out.println(" SORDER UPDATE COUNT ["+updateSorder+"]");
						}
						pstmtSord.clearParameters();
						//Changed By Pragyan WM3DSUN015 To update status in Sorder as InProcess after .end
				}
				else if(isDespExistForOrder(saleOrder, ptcn, conn))
				{
					pstmtSord.setString(1, "C");
					pstmtSord.setString(2, saleOrder);
					pstmtSord.setString(3, "C");
					
					updateSorder = pstmtSord.executeUpdate();
					
					if(updateSorder > 0)
					{
						System.out.println(" SORDER UPDATE COUNT DESP EXIST["+updateSorder+"]");
					}
					pstmtSord.clearParameters();

				}
				
			    //Changed by Rohan on 30-05-13 for void packages duning master pick and parcel picking.end
			 }//while close
			
			//changed by Rohan on 26/03/13 for updating wave status.start
			System.out.println("Retun string["+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
			
			if( pstmtSord!= null)
			{
				pstmtSord.close();
				pstmtSord = null;
			}
			
		}
		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( pstmtSord!= null)
				{
					pstmtSord.close();
					pstmtSord = 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  String getError(String saleOrder,String Code,Connection conn)  throws ITMException, Exception
    {
        String mainStr ="";
        
        try
        {
        	String errString = "";
        	errString =  new ITMDBAccessEJB().getErrorString("",Code,"","",conn);
			String begPart = errString.substring(0,errString.indexOf("<message>")+9);
			String endDesc = errString.substring(errString.indexOf("</description>"));
			mainStr= begPart+"Invalid Data"+"</message><description>";
			mainStr= mainStr+"Sale Order ="+saleOrder+""+endDesc;
			System.out.println("mainStr:::::::::::::::::: "+mainStr);
			begPart = null;
        }
        catch(Exception e)
        {
            e.printStackTrace();
            throw new ITMException(e);
        }
        return mainStr;
    }
	
	
	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;
		boolean isManStk = 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;
				
				}
			}
			//changed by sankara on 08/05/14 for update calcel_mode in repl_ord_det through wave cancel start.
			//Changed by Rohan on 02-08-13 for cheking stock is allocate from manual shipment.start
		   /*	System.out.println("manStkReplMap"+manStkReplMap.toString()+"replOrdOrg["+replOrdOrg+"]replLineOrg["+replLineOrg+"]");
			if(manStkReplMap.containsKey(replOrdOrg.trim()+":"+replLineOrg.trim()))
			{
				isManStk = true;
				System.out.println("************************Manually Stock Allocated************************");
			}
			//Changed by Rohan on 02-08-13 for cheking stock is allocate from manual shipment.end
			 */
			//changed by sankara on 08/05/14 for update calcel_mode in repl_ord_det through wave cancel end.
			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
	//Changed By Pragyan WM3DSUN015 To update status in Sorder as InProcess after .start
	private boolean isOrderIsInProcess(String saleOrder,String ptcn,Connection conn) throws ITMException
	{

		PreparedStatement pstmt = null;
		String sql = "";
		ResultSet rSet = null;
		boolean isOrderIsInProcess = false;
		int count = 0;
		
		try
		{
			sql = "SELECT COUNT(*) from WAVE_TASK W,WAVE_TASK_DET T" 
					+ " WHERE T.PTCN <> ? " 
					+ " AND T.SALE_ORDER = ? " 
					+ " AND W.WAVE_ID = T.WAVE_ID "
					+ " AND W.CANCEL <> ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,ptcn);
			pstmt.setString(2,saleOrder);
			pstmt.setString(3,"Y");
			
			rSet = pstmt.executeQuery();
			
			if( rSet.next())
			{
				count = rSet.getInt(1);		
				System.out.println("isOrderIsInProcesst==============>>>count["+count+"]");	
				if(count > 0)
				{
					return isOrderIsInProcess = true;
				}
			}
			
			if(rSet != null)
			{
				rSet.close();
				rSet = null;
			}
			
			if(pstmt != null)
			{
				pstmt.close();
				pstmt = null;
			}
			
		}
		catch(Exception e)
		{
			System.out.println("Exception in  == >"+e);
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if( rSet != null )
				{
					rSet.close();
					rSet = null;
				}
				if( pstmt != null )
				{
					pstmt.close();
					pstmt = null;
				}
			}
			catch (Exception e1)
			{
				throw new ITMException(e1);
			}
		}
	 System.out.println("isOrderIsInProcesst==============>>>"+isOrderIsInProcess);	
	 return isOrderIsInProcess;
	}
	//Changed By Pragyan WM3DSUN015 To update status in Sorder as InProcess after .end
	private boolean isDespExistForOrder(String saleOrder,String ptcn,Connection conn) throws ITMException
	{

		PreparedStatement pstmt = null;
		String sql = "";
		ResultSet rSet = null;
		boolean isDespExistForOrder = false;
		int count = 0;
		
		try
		{
			sql = "SELECT COUNT(*) from despatch d" 
					+ " WHERE d.SORD_NO = ? " 
					+ " AND d.CONFIRMED = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,saleOrder);
			pstmt.setString(2,"Y");
			
			rSet = pstmt.executeQuery();
			
			if( rSet.next())
			{
				count = rSet.getInt(1);		
				System.out.println("isDespExistForOrder==============>>>count["+count+"]");	
				if(count > 0)
				{
					return isDespExistForOrder = true;
				}
			}
			
			if(rSet != null)
			{
				rSet.close();
				rSet = null;
			}
			
			if(pstmt != null)
			{
				pstmt.close();
				pstmt = null;
			}
			
		}
		catch(Exception e)
		{
			System.out.println("Exception in  == >"+e);
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if( rSet != null )
				{
					rSet.close();
					rSet = null;
				}
				if( pstmt != null )
				{
					pstmt.close();
					pstmt = null;
				}
			}
			catch (Exception e1)
			{
				throw new ITMException(e1);
			}
		}
	 System.out.println("isDespExistForOrder==============>>>"+isDespExistForOrder);	
	 return isDespExistForOrder;
	}
	//Changed By Pragyan WM3DSUN015 To update status in Sorder as InProcess after .end

	private boolean isManualAllocStock(String saleOrder,String lineNoSord,Connection conn) throws ITMException
	{
		String sql = "",updateSql = "";
		String sqlShipStanCode = "";
		ResultSet rs = null;
		PreparedStatement pstmt = null,pstmtUpd = null;
		boolean isManAlloc = false;
		try
		{
			
			updateSql = "UPDATE SORDALLOC SET WAVE_FLAG = 'N' WHERE SALE_ORDER = ? AND LINE_NO = ? AND ALLOC_MODE ='M' ";
			pstmtUpd = conn.prepareStatement(updateSql);
			pstmtUpd.setString(1, saleOrder);
			pstmtUpd.setString(2, lineNoSord);
			
			sql = " SELECT * FROM SORDALLOC WHERE SALE_ORDER = ? AND LINE_NO = ? AND ALLOC_MODE ='M' ";
				
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, saleOrder);
			pstmt.setString(2, lineNoSord);
			rs = pstmt.executeQuery();

			if (rs.next())
			{
				isManAlloc = true;
				
				
			}
			
			if(isManAlloc)
			{
				int count = pstmtUpd.executeUpdate();
				
				if(count > 0)
				{
					System.out.println("Update count["+count+"]");
				}
				
				
			}
			
			
			if(rs != null)
			{
				rs.close();
				rs = null;
			}
			
			if(pstmtUpd != null)
			{
				pstmtUpd.close();
				pstmtUpd = null;
				
			}
		
			if(pstmt != null)
			{
				pstmt.close();
				pstmt = null;
				
			}
		}
		catch(Exception e)
		{
			System.out.println("Exception in  == >"+e);
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if( rs != null )
				{
					rs.close();
					rs = null;
				}
				if( pstmt != null )
				{
					pstmt.close();
					pstmt = null;
				}
			}
			catch (Exception e1)
			{
				throw new ITMException(e1);
			}
		}
	 return isManAlloc;	
	}
	
	private boolean isTrackingNoExist(String ptcn,Connection conn) throws ITMException
	{
		String sql = "",updateSql = "";
		String sqlShipStanCode = "";
		ResultSet rs = null;
		PreparedStatement pstmt = null,pstmtUpd = null;
		boolean isAparcelWave = false;
		int count = 0;
		
		try
		{
			
						
			sql = " SELECT COUNT(*) AS COUNT FROM CARTON_MASTER WHERE PTCN = ? AND TRACKING_NO IS NOT NULL";
				
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, ptcn);
			
			rs = pstmt.executeQuery();

			if (rs.next())
			{
				//isAparcelWave = true;			
				count = rs.getInt("COUNT");
			}
			
			
			
			if(count > 0)
			{
				isAparcelWave = true;		
				System.out.println("Update count isAparcelWave["+isAparcelWave+"]");
			}
				
			
			if(rs != null)
			{
				rs.close();
				rs = null;
			}
		
		
			if(pstmt != null)
			{
				pstmt.close();
				pstmt = null;
				
			}
		}
		catch(Exception e)
		{
			System.out.println("Exception in  == >"+e);
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if( rs != null )
				{
					rs.close();
					rs = null;
				}
				if( pstmt != null )
				{
					pstmt.close();
					pstmt = null;
				}
			}
			catch (Exception e1)
			{
				throw new ITMException(e1);
			}
		}
	 return isAparcelWave;	
	}
	//changed by sankara on 25/08/14 for update active repl quantity to normal repl in repl_ord_det at wave calcel start.
	private int updateReplorderqty(String replOrd, String refser ,Connection conn) throws ITMException
	{
		String sql = "";
		PreparedStatement pstmt = null, pstmt1=null, pstmt2 = null;
		ResultSet rs = null, rs1=null;
		String refSeries = "";
		int updateCount = 0;
		int deallocQty = 0;
		int count = 0;
		String ptcn = "";
		String siteCode = "";
		String itemCode = "";
		String locCode = "";
		String lotNo = "";
		String Lotsl = "";
		String replOrder = "";
		String dsoReplOrder = "";
		String replOrderpnd = "";
		int activereplquantity = 0;
		int repllineNo = 0;
        double shipperSize = 0;
        double numberofArt = 0;
    	HashMap itmVolumeMap = new HashMap();
		try
		{
			System.out.println("replOrd["+replOrd+"]");
			System.out.println("refser["+refser+"]");

			sql = " SELECT RT.QUANTITY,RT.SITE_CODE, RT.ITEM_CODE, RT.LOC_CODE, RT.LOT_NO, RT.LOT_SL FROM REPL_ORD_DET RT " +
					" WHERE RT.REPL_ORDER = ? ";
			pstmt1 = conn.prepareStatement(sql);
			pstmt1.setString(1, replOrd);
			rs1 = pstmt1.executeQuery();				
			if(rs1.next())
			{
				activereplquantity = rs1.getInt(1); 
				siteCode = checkNull(rs1.getString(2));  
				itemCode = checkNull(rs1.getString(3));  
				locCode = checkNull(rs1.getString(4));  
				lotNo = checkNull(rs1.getString(5));  
				Lotsl = checkNull(rs1.getString(6));  
			}
			rs1.close();rs1 = null;
			pstmt1.close(); pstmt1 = null;

			System.out.println("activereplquantity["+activereplquantity+"]");
			System.out.println("siteCode["+siteCode+"]");
			System.out.println("itemCode["+itemCode+"]");
			System.out.println("locCode["+locCode+"]");
			System.out.println("lotNo["+lotNo+"]");
			System.out.println("Lotsl["+Lotsl+"]");
			

			itmVolumeMap = getItemVoumeMap(itemCode,lotNo,conn);
			shipperSize = (Double) itmVolumeMap.get("SHIPPER_SIZE");
			
			System.out.println("shipperSize["+shipperSize+"]");
			numberofArt = activereplquantity / shipperSize ;
			System.out.println("numberofArt["+numberofArt+"]");

			sql = " SELECT RD.REPL_ORDER,RD.LINE_NO FROM REPL_ORD_DET RD WHERE RD.SITE_CODE = ? AND RD.ITEM_CODE = ? AND RD.LOC_CODE  = ? " +
					" AND RD.LOT_NO  = ? AND RD.LOT_SL = ? AND ( CANCEL_MODE IS NULL OR CANCEL_MODE = 'N' ) " ;
			pstmt1 = conn.prepareStatement(sql);
			pstmt1.setString(1, siteCode);
			pstmt1.setString(2, itemCode);
			pstmt1.setString(3, locCode);
			pstmt1.setString(4, lotNo);
			pstmt1.setString(5, Lotsl);
			rs1 = pstmt1.executeQuery();				
			while(rs1.next())
			{
				replOrder =  checkNull(rs1.getString(1));  
				repllineNo =  rs1.getInt(2);
				System.out.println("replOrder:::::"+replOrder);
				sql = " SELECT REF_SER FROM WAVE_TASK_DET WHERE REF_ID = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, replOrder);
				rs = pstmt.executeQuery();	
				if(rs.next())
				{
					refSeries = checkNull(rs.getString(1));
					System.out.println("refSeries:::::::"+refSeries);
				}
				rs.close();rs = null;
				pstmt.close(); pstmt = null;	

				if("R-DSO".equalsIgnoreCase(refSeries.trim()) || "R-TASK".equalsIgnoreCase(refSeries.trim()))                      //DSO Update
				{
					sql = " UPDATE REPL_ORD_DET SET QUANTITY = ( QUANTITY + ? ), ACTUAL_QTY = ( ACTUAL_QTY + ? ),  NO_ART = ( NO_ART + ? ) WHERE REPL_ORDER = ? AND LINE_NO = ? " ;
					pstmt2 = conn.prepareStatement(sql);
					pstmt2.setInt(1, activereplquantity);
					pstmt2.setInt(2, activereplquantity);
					pstmt2.setDouble(3, numberofArt);
					pstmt2.setString(4, replOrder);
					pstmt2.setInt(5, repllineNo);
					updateCount = pstmt2.executeUpdate();				
					if(updateCount > 0)
					{
						System.out.println("wave_task_det Has been updated["+updateCount+"]");
					}			
					if(pstmt2 != null)
					{
						pstmt2.close();
						pstmt2 = null;
					}
				}
				if("R-DSO".equalsIgnoreCase(refSeries.trim()))
				{
					sql = " SELECT REPL_ORDER,LINE_NO FROM REPL_ORD_DET WHERE PICK_ORDER = ? AND LINE_NO__PICK = ? " ;
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, replOrder);
					pstmt.setInt(2, repllineNo);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						replOrderpnd =  checkNull(rs.getString(1));    
						System.out.println("replOrderpnd:::::"+replOrderpnd);
						repllineNo =  rs.getInt(2);
						System.out.println("repllineNo:::::"+repllineNo);
						sql = " UPDATE REPL_ORD_DET SET QUANTITY = ( QUANTITY + ? ), NO_ART = ( NO_ART + ? ) WHERE REPL_ORDER = ? AND LINE_NO = ? " ;
						pstmt2 = conn.prepareStatement(sql);
						pstmt2.setInt(1, activereplquantity);
						pstmt2.setDouble(2, numberofArt);
						pstmt2.setString(3, replOrderpnd);
						pstmt2.setInt(4, repllineNo);
						updateCount = pstmt2.executeUpdate();					
						if(updateCount > 0)
						{
							System.out.println("repl_ord_det Has been updated["+updateCount+"]");
						}
						if(pstmt2 != null)
						{
							pstmt2.close();
							pstmt2 = null;
						}
					}
					rs.close();rs = null;
					pstmt.close(); pstmt = null;
				}
			}
			rs1.close();rs1 = null;
			pstmt1.close(); pstmt1 = null;
		}
		catch(Exception e)
		{
			System.out.println("Exception in  == >"+e);
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if( pstmt != null )
				{
					pstmt.close();
					pstmt = null;
				}
				if(rs != null)
				{
					rs.close();
					rs = null;
				}
				if(pstmt1 != null)
				{
					pstmt1.close();
					pstmt1 = null;
				}
				if(rs1 != null)
				{
					rs1.close();
					rs1 = null;
				}					
				if(pstmt2 != null)
				{
					pstmt2.close();
					pstmt2 = null;
				}
			}
			catch (Exception e1)
			{
				throw new ITMException(e1);
			}
		}
		return updateCount;	
	}
	//changed by sankara on 25/08/14 for update active repl quantity to normal repl in repl_ord_det at wave calcel end.
	//Changed by sankara on 04/09/14 for getting shipper size start.
	private HashMap getItemVoumeMap(String itemCode,String lotNo,Connection con)throws Exception
	{
		double shipperSize = 0,itemVolume = 0,caseVolume = 0;
		PreparedStatement pstmt = null;
		String sql="";
		ResultSet rs = null;
		double itmLen = 0,itmWidth = 0,itmHeight = 0,itemGrossWeight = 0,lotLen = 0 ,lotHeight = 0,lotWidth = 0,caseGrossWeight = 0,itemNetWeight = 0, caseNetWeight = 0;
		HashMap dataVolumeMap = new HashMap();

		try {

			sql = "SELECT I.LENGTH ITEM_LEN,I.WIDTH ITEM_WID,I.HEIGHT ITEM_HEIGHT,I.GROSS_WEIGHT ITEM_GROSS_WEIGHT,I.NET_WEIGHT ITEM_NET_WEIGHT,"
					+" L.LENGTH LITEM_LEN,L.WIDTH LITEM_WID,L.HEIGHT LITEM_HEIGHT,L.SHIPPER_SIZE SHIPSIZE,L.GROSS_WEIGHT CASE_GROSS_WEIGHT,L.NET_WEIGHT CASE_NET_WEIGHT FROM"
					+" ITEM I,ITEM_LOT_PACKSIZE L"
					+" WHERE I.ITEM_CODE = L.ITEM_CODE"
					+" AND L.LOT_NO__FROM <= ? AND L.LOT_NO__TO >= ?"
					+" AND  I.ITEM_CODE = ?";


			pstmt = con.prepareStatement(sql);
			if(lotNo != null && lotNo.length() > 0)
			{
				pstmt.setString(1, lotNo);
				pstmt.setString(2, lotNo);
			}
			else
			{
				pstmt.setString(1, "00");
				pstmt.setString(2, "ZZ");
			}
			pstmt.setString(3, itemCode);
			rs = pstmt.executeQuery();

			if(rs.next())
			{
				itmLen = rs.getDouble("ITEM_LEN");
				itmWidth = rs.getDouble("ITEM_WID");
				itmHeight = rs.getDouble("ITEM_HEIGHT");
				itemGrossWeight = rs.getDouble("ITEM_GROSS_WEIGHT"); //GROSS_WEIGHT
				lotLen = rs.getDouble("LITEM_LEN");
				lotWidth = rs.getDouble("LITEM_WID");
				lotHeight = rs.getDouble("LITEM_HEIGHT");				
				shipperSize = rs.getDouble("SHIPSIZE");				
				caseGrossWeight = rs.getDouble("CASE_GROSS_WEIGHT");				
				itemNetWeight = rs.getDouble("ITEM_NET_WEIGHT");	//	NET_WEIGHT		
				caseNetWeight = rs.getDouble("CASE_NET_WEIGHT");				
			}

			//shipperSize = (lotHeight * lotWidth * lotLen)/(itmLen * itmWidth * itmHeight);
			itemVolume = (itmLen * itmWidth * itmHeight);
			caseVolume = (lotHeight * lotWidth * lotLen);

			dataVolumeMap.put("SHIPPER_SIZE", shipperSize);
			dataVolumeMap.put("ITEM_VOLUME", itemVolume);
			dataVolumeMap.put("CASE_VOLUME", caseVolume);
			dataVolumeMap.put("ITEM_GROSS_WEIGHT", itemGrossWeight);
			dataVolumeMap.put("CASE_GROSS_WEIGHT", caseGrossWeight);
			dataVolumeMap.put("ITEM_NET_WEIGHT", itemNetWeight);
			dataVolumeMap.put("CASE_NET_WEIGHT", caseNetWeight);

			if(pstmt != null)
			{
				pstmt.close();
				pstmt = null;

			}
			if(rs != null)
			{
				rs.close();
				rs = null;

			}

		} 
		catch (Exception e) 
		{
			throw e;
		}
		finally
		{
			if(pstmt != null)
			{
				pstmt.close();
				pstmt = null;

			}
			if(rs != null)
			{
				rs.close();
				rs = null;

			}
		}

		return dataVolumeMap;
	}
	//Changed by sankara on 04/09/14 for getting shipper size end.
}
