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

package ibase.webitm.ejb.wms;

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

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

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

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

		//PreparedStatement pstmtRepl = null;
		//ResultSet rsRepl = null;
		//String sqlReplOrd = "";
		String replOrder = "";
		String replLineNo = "";
		String waveCancel = "";
		String sqlUpdate = "";
		String pickOrder = "";
		String pickLineNo = "";
		String sqlpickDet = "";
		String locCodeTo = "";
		String locCode1 = "";
		String dockLoc = "";
		//String sqlreplDet = "";
		//Changed by Rohan on 21-09-12 for wave generation reversal.end
		//Changed by Rohan 10-04-12 to define variables.start
		String invState = "";
		String pickOrd = "";
		String lineNoPick = "";
		String stagingLocMpick = "";
		//Changed by Rohan on 10-04-12 to define variables.end
		
		//Changed by Rohan on 05-10-12 to define variable.start
		String confirm = "";
		String transferStatus = "";
		String stagingLoc = "";
		String replPickOrder = "";
		String replPickLineNo = "";
		String locCodeRepl = "";
		String confirmRepl = "";
		String lotSlNew = "";
		String waveId = "";
		String ptcn = "";
		String sqlDesp = "";
		String replDet = "";
		String dsoInvstat = "";
		int cnt = 0;
		int updateRecord = 0;
		String sqlInv = "";
		String replOrderDso = "";
		String replLineNoDso = "";
		String replOrderAct = "";
		String replPickLineNoAct = "";
		String pickOrdRepl = "";
		String lineNoRepl = "";
		String sqlWave = "";
		String refSeries = "";
		PreparedStatement pstmtTask = null;
		ResultSet rsTask = null;
		PreparedStatement pstmtInv = null;
		ResultSet rsInv = null;
		
		
		PreparedStatement pstmtDesp = null;
		PreparedStatement pstmtReplDet = null;
		
		PreparedStatement pstmtPickIss = null;
		ResultSet rsPickIss = null;
		
		ResultSet rsDesp = null;
		boolean isDespatch = true;
		//Changed by Rohan on 10-05-12 to define variable.end
		
		boolean isError = false;
		
		int updCnt = 0;
		PreparedStatement pstmtUpd = null;
		PreparedStatement pstmtSql = null;
		ResultSet rs = null;
		ResultSet rsPick = null;
		
		ITMDBAccessEJB itmDBAccess = new ITMDBAccessEJB();
		
		try
		{
			
			if ( conn == null )
			{
				ConnDriver connDriver = new ConnDriver();
				conn = connDriver.getConnectDB("DriverITM");
				connDriver = null;
				conn.setAutoCommit(false);
				connStatus = true;
			}
			
			
			
			dockLoc = discommon.getDisparams("999999","DOCKLOC_INVSTAT",conn);
			//Changed by Rohan on 10-05-12 to get disparm variable
			stagingLoc = discommon.getDisparams( "999999", "STAGING_LOC", conn );
			stagingLocMpick = discommon.getDisparams( "999999", "STAGING_LOC_MPK", conn );
			
			dsoInvstat = discommon.getDisparams( "999999", "DEEP_STORE_INVSTAT", conn );
			
			GenericUtility genericUtility = GenericUtility.getInstance();
			java.sql.Timestamp currDate = new java.sql.Timestamp( System.currentTimeMillis() );
			distStkUpd = getComponent("DistStkUpd");
			
			//Changed by Rohan on 21-09-12 for wave generation reversal.start
			siteCode = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginSiteCode");
			tranDate = new java.sql.Date(System.currentTimeMillis());
			
			sql = "SELECT CANCEL FROM WAVE_TASK "
				+ " WHERE WAVE_ID = ? ";
			pstmtSql = conn.prepareStatement(sql);
			pstmtSql.setString(1, waveID);
			rs = pstmtSql.executeQuery();
			
			if( rs.next() )
			{
				
				waveCancel = checkNull( rs.getString("CANCEL") );
				if(waveCancel.equalsIgnoreCase("Y"))
				{
					retString = itmDBAccess.getErrorString("","WAVECANCEL","");
					return retString;
					
				}
			}
			
			rs.close();
			rs = null;
			pstmtSql.close();
			pstmtSql = null;
			replHashMap.clear();
			
			sqlDesp = "SELECT COUNT(*) AS COUNT FROM DESPATCHDET WHERE DESP_ID IN(SELECT REF_ID FROM WAVE_TASK_DET WHERE WAVE_ID = ? AND REF_SER = ?)";
			
			sql = "SELECT REF_SER,REF_ID,PTCN,WAVE_ID FROM WAVE_TASK_DET WHERE WAVE_ID = ?";
			pstmtWave = conn.prepareStatement(sql);
			pstmtWave.setString(1, waveID);
			
			rsWave = pstmtWave.executeQuery();
			while( rsWave.next() )
			{
				
				//if despachdet generate then not allow to cancle wave.start 
				waveId = checkNull(rsWave.getString("WAVE_ID"));
				if(isDespatch)
				{	
				
					pstmtDesp = conn.prepareStatement(sqlDesp);
					pstmtDesp.setString(1,waveId);
					pstmtDesp.setString(2,"S-DSP");
					rsDesp = pstmtDesp.executeQuery();
					if(rsDesp.next())
					{
						cnt = rsDesp.getInt("COUNT");
					}
					if(cnt > 0)
					{
						retString = itmDBAccess.getErrorString("","NTWAVECAN","");
						return retString;
					}
					else
					{
						isDespatch = false;
					}
				
					if(pstmtDesp != null)
					{
						pstmtDesp.close();
						pstmtDesp = null;
					}
					if(rsDesp != null)
					{
						rsDesp.close();
						rsDesp = null;
					}
				}	
				//if despachdet generate then not allow to cancle wave.start 
				refSer = checkNull( rsWave.getString("REF_SER".trim()));
				refId = checkNull( rsWave.getString("REF_ID") );
				
				System.out.println("Curren refser:"+refSer+"REF_ID"+refId);
				
				if(refSer.equalsIgnoreCase("C-PICK") || refSer.equalsIgnoreCase("M-PICK") || refSer.equalsIgnoreCase("A-PICK") || refSer.trim().equalsIgnoreCase("S-DOC") || refSer.equalsIgnoreCase("RP-PND") || refSer.equalsIgnoreCase("RP-PSO"))
				{
					if(refSer.trim().equalsIgnoreCase("S-DOC") || refSer.equalsIgnoreCase("RP-PND") || refSer.equalsIgnoreCase("RP-PSO"))
					{
						
						//for stock to dock
						replDet ="SELECT ITEM_CODE,LINE_NO,LOC_CODE,LOT_NO,LOT_SL,QUANTITY,"
								+" EXP_LEV,SALE_ORDER,LINE_NO__SORD,LOC_CODE__TO,REPL_ORDER,LINE_NO " 
								+" FROM REPL_ORD_DET WHERE REPL_ORDER = ?";
						pstmtSql = conn.prepareStatement(replDet);
					}
					else
					{	
						//for active,master,case picking
						pickDet ="SELECT ITEM_CODE,LINE_NO,LOC_CODE,LOT_NO,LOT_SL,QUANTITY," +
							 "EXP_LEV,SALE_ORDER,LINE_NO__SORD,REPL_ORDER,LINE_NO__REPL,PICK_ORDER,LINE_NO FROM PICK_ORD_DET WHERE PICK_ORDER = ? ";
						pstmtSql = conn.prepareStatement(pickDet);
					}
					//replHashMap.clear();
					
					pstmtSql.setString(1, refId);
					rsPick = pstmtSql.executeQuery();
					while( rsPick.next() )
					{
						
						boolean isPickConf = true;
						replPickOrder = "";
						replPickLineNo = "";
						
						itemCode = checkNull( rsPick.getString("ITEM_CODE") );
						lineNo = checkNull( rsPick.getString("LINE_NO") );
						locCode = checkNull( rsPick.getString("LOC_CODE") );
						
						lotNo = checkNull( rsPick.getString("LOT_NO") );
						lotSl = checkNull( rsPick.getString("LOT_SL") );
						quantity = rsPick.getDouble("QUANTITY");
						expLev = checkNull( rsPick.getString("EXP_LEV") );
						saleOrder = checkNull( rsPick.getString("SALE_ORDER") );
						lineNoSord = checkNull( rsPick.getString("LINE_NO__SORD") );
						
						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") );
						}
						
						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
								updateRecord = updateReplanishment(replOrder,replLineNo,pickOrder,pickLineNo,quantity,true,conn);//test1sucess
								
								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"))
								{
									locCode = locCodeTo;
									System.out.println("Caspick loc code"+locCode);
									break;
								}
								else if(refSer.equalsIgnoreCase("M-PICK") || refSer.equalsIgnoreCase("A-PICK"))
								{
									//to check transfer status
									if("Y".equalsIgnoreCase(transferStatus))
									{
										locCode = locCodeTo;
										break;
									}
									else
									{
										//stock allocate from staging location
										if(refSer.equalsIgnoreCase("M-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);//test1sucess
									if(!refSer.trim().equalsIgnoreCase("S-DOC") || !refSer.equalsIgnoreCase("RP-PND") || !refSer.equalsIgnoreCase("RP-PSO"))
									{
										updateRecord = updateReplanishment(replOrder,replLineNo,pickOrder,pickLineNo,quantity,true,conn);//test1sucess
									}
									//Changed by Rohan on 03/12/11 not update repanishment in case of stock to dock.end
									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
										}
										*/
										if(!refSer.trim().equalsIgnoreCase("S-DOC") || !refSer.equalsIgnoreCase("RP-PND") || !refSer.equalsIgnoreCase("RP-PSO"))
										{
											updateRecord = updateReplanishment(replOrder,replLineNo,pickOrder,pickLineNo,quantity,false,conn);
										}
										//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);
									 }
									 */
									 if(!refSer.trim().equalsIgnoreCase("S-DOC") || !refSer.equalsIgnoreCase("RP-PND") || !refSer.equalsIgnoreCase("RP-PSO"))
									 {
										 updateRecord = updateReplanishment(replPickOrder,replPickLineNo,pickOrder,pickLineNo,quantity,false,conn);
									 }
									//Changed by Rohan on 03/12/11 not update repanishment in case of stock to dock.end 
									 if(updateRecord > 0)
									 {
										System.out.println("Replenishment updated with actual quantity "+quantity+" For replenishment oder :"+replPickOrder +" and line no :"+replPickLineNo);
									 }
										
									 break;
								 }
							}
							
						}
						while(!"".equalsIgnoreCase(replPickOrder) && !"".equalsIgnoreCase(replPickLineNo));	
						
						System.out.println("Final location code*****"+locCode+"LoT Sl***"+lotSl);
						lineNoSord = "  " + lineNoSord; 
						
						sql = "SELECT QTY_ALLOC AS COUNT FROM SORDALLOC "
							  +"WHERE SALE_ORDER = ? "
							  +"AND LINE_NO = ? "
							  +"AND EXP_LEV = ? "
							  +"AND ITEM_CODE__ORD = ? "
							  +"AND ITEM_CODE = ? "
							  +"AND LOT_NO = ? "
							  +"AND LOT_SL = ? "
							  +"AND LOC_CODE = ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1,saleOrder);
						pstmt.setString(2,  lineNoSord);
						pstmt.setString(3,expLev);
						pstmt.setString(4,itemCode);
						pstmt.setString(5,itemCode);
						pstmt.setString(6,lotNo);
						pstmt.setString(7,lotSl);
						pstmt.setString(8,locCode);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							
							qtyAlloc = rs.getDouble(1);
						}
						else
						
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
	
						// 22/02/10 manoharan if allocation found in sordalloc then deallocate
						//if (count > 0 ) // 04/04/12 manoharan
						if (qtyAlloc > 0) // 04/04/12 manoharan
						{
							if (qtyAlloc - qtyStduom <= 0)
							{
								sqlSord = "DELETE FROM SORDALLOC WHERE SALE_ORDER = ? "
									 +"AND LINE_NO = ? "
									 +"AND EXP_LEV = ? " 
									 +"AND ITEM_CODE__ORD = ? " 
									 +"AND ITEM_CODE = ? " 
									 +"AND LOT_NO = ? " 
									 +"AND LOT_SL = ? "
									 +"AND LOC_CODE = ? ";
								System.out.println("Delete sql sqlSord :"+sqlSord);
								pstmt = conn.prepareStatement(sqlSord);
								pstmt.setString(1,saleOrder);
								pstmt.setString(2,lineNoSord);
								pstmt.setString(3,expLev);
								pstmt.setString(4,itemCode);
								pstmt.setString(5,itemCode);
								pstmt.setString(6,lotNo);
								pstmt.setString(7,lotSl);
								pstmt.setString(8,locCode);
								updateCnt = pstmt.executeUpdate();
								pstmt.close();
								pstmt = null;
							}
							else
							{
								sqlSord = "UPDATE SORDALLOC SET QTY_ALLOC = QTY_ALLOC - ? "
									  +"WHERE SALE_ORDER = ? "
									  +"AND LINE_NO = ? "
									  +"AND EXP_LEV = ? "
									  +"AND ITEM_CODE__ORD = ? "
									  +"AND ITEM_CODE = ? "
									  +"AND LOT_NO = ? "
									  +"AND LOT_SL = ? "
									  +"AND LOC_CODE = ? ";
								System.out.println("Update sql sqlSord :"+sqlSord);
								pstmt = conn.prepareStatement(sqlSord);
								pstmt.setDouble(1,qtyStduom);
								pstmt.setString(2,saleOrder);
								pstmt.setString(3,lineNoSord);
								pstmt.setString(4,expLev);
								pstmt.setString(5,itemCode);
								pstmt.setString(6,itemCode);
								pstmt.setString(7,lotNo);
								pstmt.setString(8,lotSl);
								pstmt.setString(9,locCode);
								updateCnt = pstmt.executeUpdate();
								pstmt.close();
								pstmt = null;
								System.out.println("Updated the no of records are : updateCnt :"+updateCnt);
							}
							sqlSordItem = "UPDATE SORDITEM SET QTY_ALLOC = QTY_ALLOC - ? "
										 +"WHERE SALE_ORDER = ? "
										 +"AND LINE_NO = ? "
										 +"AND EXP_LEV = ? ";
							System.out.println("Update sql sqlSordItem :"+sqlSordItem);
							pstmt = conn.prepareStatement(sqlSordItem);
							pstmt.setDouble(1,qtyStduom);
							pstmt.setString(2,saleOrder);
							pstmt.setString(3,lineNoSord);
							pstmt.setString(4,expLev);
							updateCnt = pstmt.executeUpdate();
							pstmt.close();
							pstmt = null;
							System.out.println("Updated the no of records : updateCnt :"+updateCnt);
	
							line = "   " + lineNo;
							System.out.println("line :"+line);
							
							//Changed by Rohan on 10/8/12 to give proper loc code.start
	
							
							//Changed by Rohan on 10/8/12 to give proper loc code.end
							strAllocate.put("tran_date",tranDate);
							strAllocate.put("ref_ser","P-TASK");
							//strAllocate.put("ref_id",tranId);
							strAllocate.put("ref_id",refId);
							strAllocate.put("ref_line",line.substring(line.length()-3));
							strAllocate.put("site_code",siteCode);
							strAllocate.put("item_code",itemCode);
							strAllocate.put("loc_code",locCode);
							strAllocate.put("lot_no",lotNo);
							strAllocate.put("lot_sl",lotSl);
							strAllocate.put("alloc_qty",new Double(-1*qtyStduom)); 
							strAllocate.put("chg_user",genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginCode"));
							strAllocate.put("chg_term",genericUtility.getValueFromXTRA_PARAMS(xtraParams,"termId"));
							strAllocate.put("chg_win","W_PICK_ORD");
							//Calling DistStkUpdEJB
							System.out.println("Calling DistStkUpdEJB.....");
							if (distStkUpd.updAllocTrace(strAllocate, conn) > 0)
							{
								System.out.println("distStkUpd.UpdAllocTrace(hashMap) : Sucessuful!");
							}
							
							sqlUpdate = "UPDATE WAVE_TASK SET CANCEL ='Y' WHERE WAVE_ID = ?" ;						
							pstmtUpd = conn.prepareStatement(sqlUpdate);
							pstmtUpd.setString(1, waveID);
							updCnt = pstmtUpd.executeUpdate();
							
							if( updCnt > 0 )
							{					
								retString = itmDBAccess.getErrorString("","WAVEREV","");
							}
							else
							{
								retString = itmDBAccess.getErrorString("","NTWAVEREV","");
							}
							
							if(pstmtUpd != null)
							{
								pstmtUpd.close();
								pstmtUpd = null;
							}
							
								
						  }//qty allocate > 0
					 }
				 }
			 }//while close
		
			
		}
		catch(Exception e)
		{
			isError = true;
			try
			{
				conn.rollback();
			}
			catch (Exception e1)
			{
			}
			e.printStackTrace();
			System.out.println("Exception ::"+e.getMessage());
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if( !isError && connStatus )
				{
					conn.commit(); 
					
					if(pstmtReplDet != null)
					{
						pstmtReplDet.close();
						pstmtReplDet = null;
					}
					
					if("Y".equalsIgnoreCase(status))
					{
						retString = itmDBAccess.getErrorString("","WAVENTREV","");
					}
					
				}
				else if (isError && connStatus)
				{
					conn.rollback();
				}
				if(rs != null)
				{
					rs.close();
					rs = null;					
				}
				if(pstmtUpd != null)
				{
					pstmtUpd.close();
					pstmtUpd = null;					
				}
				if( conn != null && ! conn.isClosed() && connStatus)
				{
					conn.close();
					conn = null;
				}
				if(pstmtWave != null)
				{
					pstmtWave.close();
					pstmtWave = null;
				}
				if(rsWave != null)
				{
					rsWave.close();
					rsWave = null;
				}
			}
			catch(Exception e)
			{
				System.out.println(e.getMessage());
				throw new ITMException(e);
			}
		}
		System.out.println("Returning Result ::"+retString);
		return retString;
	}
	private String checkNull( String input )
	{
		if( input == null )
		{
			input = "";
		}
		return input;
	}
	private DistStkUpdLocal getComponent(String componentName)throws Exception
    {
		DistStkUpdLocal comp = null;
        try
        {
			System.out.println("Looking For Component.... :: " + componentName);
			AppConnectParm appConnect = new AppConnectParm();
			java.util.Properties p = appConnect.getProperty();
			InitialContext ctx = new InitialContext(p);
			 comp = (DistStkUpdLocal)ctx.lookup("ibase/DistStkUpd/local"); // for ejb3
			//comp = distStkUpdHome.create();
			System.out.println(componentName+" Found.....");			
        }
        catch(Exception e)
        {
			throw e;
        }
		return comp;
	}
	private ArrayList isReplinshmentConfirm(String replOrder ,String replLineNo,Connection conn) throws Exception
	{
	
		PreparedStatement pstmtReplDet = null;
		ResultSet rsRepl = null;
	  
		
		//String LocCodeTo = ""; 

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

	private ArrayList getpreviousReplTask(String replOrder ,String replLineNo,Connection conn) throws Exception
	{
	
		PreparedStatement pstmtRepl = null;
		ResultSet rsReplDet = null;
	  
		String replPickLineNo = "";
		String replPickOrder = "";
		String locCode = "";
		String sqlReplOrd = "";
		
		ArrayList tempList = new ArrayList();
		
		try
		{
			sqlReplOrd= "SELECT PICK_ORDER,LINE_NO__PICK,LOC_CODE FROM REPL_ORD_DET WHERE REPL_ORDER = ? AND LINE_NO = ?";
			pstmtRepl = conn.prepareStatement(sqlReplOrd);
			
			
			pstmtRepl.clearParameters();
			pstmtRepl.setString(1, replOrder);
			pstmtRepl.setString(2, replLineNo);
			rsReplDet = pstmtRepl.executeQuery();
			if(rsReplDet.next())
			{
				
				locCode = checkNull( rsReplDet.getString("LOC_CODE") );
				replPickOrder = checkNull( rsReplDet.getString("PICK_ORDER") );
				replPickLineNo = checkNull( rsReplDet.getString("LINE_NO__PICK") );
				
				
			}
			
			tempList.add(locCode);
			tempList.add(replPickOrder);
			tempList.add(replPickLineNo);
				
        }
		catch(Exception e)
		{
			throw new ITMException(e);
		}
		finally
		{
			if(rsReplDet != null)
			{
				rsReplDet.close();
				rsReplDet = null;
			}
			if(pstmtRepl != null)
			{
				pstmtRepl.close();
				pstmtRepl = null;
			}
		
		}
	return tempList;  	
	}
	
	public int updateReplanishment(String replOrder,String lineNo,String pickOrder,String lineNoPick,double quantity,boolean confirm,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 = "";
	    DistCommon discommon = new DistCommon();
	    String casePickLoc  = discommon.getDisparams("999999","CASE_PICK_INVSTAT",conn);
	    String activePickLoc  = discommon.getDisparams("999999","ACTIVE_PICK_INVSTAT",conn);
	    String deepStoreLoc = discommon.getDisparams("999999","DEEP_STORE_INVSTAT",conn);
	    String psoLoc  = discommon.getDisparams("999999","RESERV_LOCATION",conn);
	    
		double actQty = 0d;
		
	    
	    int count = 0;
		try
		{
			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 = "";
								
								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 ";
								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");
								}
								if(rs1 != null)
								{
									rs1.close();
									rs1 = null;
								}
								if(pstmt1 != null)
								{
									pstmt1.close();
									pstmt1 = null;
								}
								
								replOrd = checkNull(rs.getString("PICK_ORDER"));
								lineNoRepl = rs.getString("LINE_NO__PICK");
								
								if(!"".equalsIgnoreCase(replOrd))
								{	
									
									if(invS.trim().equalsIgnoreCase(casePickLoc.trim()))
									{
										sqlOrderType = "SELECT D.PICK_ORDER,D.LINE_NO__PICK,L.INV_STAT FROM REPL_ORD_DET D,LOCATION L " 
											 +" WHERE REPL_ORDER = ? AND LINE_NO = ? "
											 +" AND L.LOC_CODE = D.LOC_CODE ";
										pstmt1 =  conn.prepareStatement(sqlOrderType);	
										pstmt1.setString(1, replOrdChk);
										pstmt1.setString(2, replLineChk);
										rs1 = pstmt1.executeQuery();
										if(rs1.next())
										{
											
											invS =  rs1.getString("INV_STAT");
											replOrdChk = checkNull(rs1.getString("PICK_ORDER"));
											replLineChk = checkNull(rs1.getString("LINE_NO__PICK"));
										}
										if(rs1 != null)
										{
											rs1.close();
											rs1 = null;
										}
										if(pstmt1 != null)
										{
											pstmt1.close();
											pstmt1 = null;
										}
										
									}
									
									if(invS.trim().equalsIgnoreCase(psoLoc.trim()))
									{
										updateSql= " UPDATE REPL_ORD_DET SET ACTUAL_QTY = ACTUAL_QTY - ?"
												   +" WHERE REPL_ORDER = ? AND LINE_NO = ? ";
									}
									else
									{
										updateSql= " UPDATE REPL_ORD_DET SET ACTUAL_QTY = ACTUAL_QTY - ?"
												  +" WHERE (REPL_ORDER,LINE_NO) IN(SELECT PICK_ORDER,LINE_NO__PICK FROM REPL_ORD_DET"
												   +" WHERE REPL_ORDER = ? AND LINE_NO = ?)";
									}
									pstmt1 =  conn.prepareStatement(updateSql);
									pstmt1.setDouble(1, actQty);
									if("".equalsIgnoreCase(replOrdChk) && invS.trim().equalsIgnoreCase(casePickLoc.trim()))
									{
										pstmt1.setString(2, replOrdChk);
										pstmt1.setString(3, replLineChk);
									}
									else
									{	
										pstmt1.setString(2, replOrd);
										pstmt1.setString(3, lineNoRepl);
									}	
									pstmt1.executeUpdate();
									if(pstmt1 != null)
									{
										pstmt1.close();
										pstmt1 = null;
									}
								}
							}
						}
						//Changed by Rohan on 25-10-12
						//else if("K".equalsIgnoreCase(orderType.trim()) || "J".equalsIgnoreCase(orderType.trim()) || "M".equalsIgnoreCase(orderType.trim()))
						else if("K".equalsIgnoreCase(orderType.trim()) || "J".equalsIgnoreCase(orderType.trim()) || "M".equalsIgnoreCase(orderType.trim()) ||  "C".equalsIgnoreCase(orderType.trim()))
						{
							
							ArrayList ReplActQty = updateReplActQty(pickOrder,lineNoPick,conn);
							
							if(ReplActQty.size() > 0)
							{	
								replOrdList = checkNull((String)ReplActQty.get(0));
								replLineNoList = checkNull((String)ReplActQty.get(1));
							}
							if(!"".equalsIgnoreCase(replOrdList))
							{
								
								replOrder = replOrdList;
								lineNo = replLineNoList;
							}
							
			
				         }
					}
			}//confirm
			else
			{
				ArrayList ReplActQty = updateReplActQty(pickOrder,lineNoPick,conn);
				if(ReplActQty.size() > 0)
				{
					replOrdList = checkNull((String)ReplActQty.get(0));
					replLineNoList = checkNull((String)ReplActQty.get(1));
				}
				if(!"".equalsIgnoreCase(replOrdList))
				{
					replOrder = replOrdList;
					lineNo = replLineNoList;
				}	
			}
			
			sql= "UPDATE REPL_ORD_DET SET ACTUAL_QTY = CASE WHEN ACTUAL_QTY IS NULL THEN 0 ELSE ACTUAL_QTY END + ?" +
					 " WHERE REPL_ORDER = ? AND LINE_NO = ?" ;
			pstmt =  conn.prepareStatement(sql);
			pstmt.setDouble(1, quantity);
			pstmt.setString(2, replOrder);
			pstmt.setString(3, lineNo);
			count = pstmt.executeUpdate();
					
        }
		catch(Exception e)
		{
			throw new ITMException(e) ;
		}
		finally
		{
			if(rs != null)
			{
				rs.close();
				rs = null;
			}
			if(pstmt != null)
			{
				pstmt.close();
				pstmt = null;
			}
			if(pstmtRepl != null)
			{	
				pstmtRepl.close();
				pstmtRepl = null;
			}
			if(rs1 != null)
			{
				rs1.close();
				rs1 = null;
			}
			if(pstmt1 != null)
			{
				pstmt1.close();
				pstmt1 = null;
			}
		
		}
		return count;
	}
	//updateReplActQty(pickOrder,lineNoPick,conn);
	public ArrayList updateReplActQty(String pickOrder,String lineNoPick,Connection conn) throws Exception
	{

		PreparedStatement pstmt = null;
		ResultSet rs = null;
	    String sql = "";
	   
	   
		PreparedStatement pstmt1 = null;
		PreparedStatement pstmtRepl = null;
		
		ResultSet rs1 = null;
	    
	    String getRepl = "";
	    String orderType = "";
	    String getActualQty,updateSql,replOrd,lineNoRepl,sqlOrderType,ordType ="";
	    String locCode,repl,lineRepl,sqlPickOrder,invStat,invstatChk = "";
	    String pick = "";
	    String sqlRepl="";
	    String replOrderCopy = "";
	    String lineNoPk = "";
	    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());
					
				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 ";
				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"));
				}
				
				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 = ? ) ";
							}
							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 = ?)) ";
	
							}
							pstmt1 =  conn.prepareStatement(updateSql);
							pstmt1.setDouble(1, actQty);
							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;  	
	}
 
}
