/*
authored by Pawan 10/7/2010 to confirm the transaction

*/

package ibase.webitm.ejb.wms;

import ibase.webitm.utility.*;
import ibase.system.config.*;
import ibase.webitm.ejb.*;
import ibase.webitm.ejb.dis.*;
import ibase.webitm.ejb.fin.*;

import java.rmi.RemoteException;
import java.util.*;
import java.util.Date;
import java.text.*;
import java.sql.*;

import javax.ejb.*;
import ibase.webitm.ejb.MasterStatefulLocal;
import javax.naming.InitialContext;


@Stateless
public class DeallocArtConf extends ActionHandlerEJB implements DeallocArtConfLocal, DeallocArtConfRemote
{
	String loginEmpCode = "";
	String confDateStr = "";
	GenericUtility genericUtility = GenericUtility.getInstance();
	DistCommon discommon = new DistCommon();
	/**
	 * The public function is used to confirm a transaction corresponding to the tran id it takes as an argument
	 * @param : tranId
	 * @param : xtraParams
	 */
	public String confirm(String tranID,String xtraParams, String forcedFlag) throws RemoteException,ITMException
	{
		String retString = "";
		Connection conn = null;
		ConnDriver connDriver = new ConnDriver();		
		boolean isConn= true;
		try
		{
			conn = connDriver.getConnectDB("DriverITM");
			conn.setAutoCommit(false);
			connDriver = null;

			retString = this.confirm( tranID,xtraParams, forcedFlag ,conn ,isConn);

			if ( retString != null && retString.length() > 0 )
			{
				throw new Exception("Exception while calling confirm for tran  Id:["+tranID+"]");
			}

		}
		catch(Exception exception)
		{
			System.out.println("Exception in [DeallocArtConf]  " + exception.getMessage());
		}
		return retString;
	}
	
	public String confirm( String tranId, String xtraParams, String forcedFlag , Connection conn, boolean conStat ) throws RemoteException,ITMException
	{	
		System.out.println("DeallocArtConf Called........");
		
		boolean isError = false;
		int updCnt = 0;
		
		String confirmed = "";
		String sql = "", sql1 = "";
		//String siteCode = "";
		ResultSet rs = null, rs2 = null ;
		PreparedStatement pstmt = null, pstmt1 = null, pstmt2 = null;			
		String errString = "";
		String saleOrder = "", saleOrderLine = "", itemCode = "", custCode = "", expLev = "";
		String lotSl = "", lotNo = "", locCode = "", waveId = "", pickOrder = "", locCodeTo ="";
		String replOrder = "", siteCode = "";
		String currentReplOrder = "";
		String pickType = "";
		String pPickType = "";
		String cartonNo = "";
		String trfStatus = "";
		String supRerplOrder = "";
		//chnaged by sankara on 13/06/2013
		String unit = "";
		String standardUnit = "";
		double convQtyStduom = 0.0;
		int supReplLine = 0;
		String ptcn = "";
		double DeallocQty = 0.0, quantity = 0.0;
		int pickLineNo = 0;
		int replOrderLine = 0;
		int currReplOrdLine = 0;
		boolean isPickIssueDone = false;
		boolean isReplDone = false;
		HashMap replIss = new HashMap();
		Set ptcnSET = new HashSet(); //
		ITMDBAccessEJB itmdbAccess = new ITMDBAccessEJB();
		
		try
		{	
			conn.setAutoCommit(false);
			System.out.println(" force flag ["+forcedFlag+"]");
			System.out.println(" xtraParams --> ["+xtraParams+"]");
			loginEmpCode = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginEmpCode");
			siteCode = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"site_code");
			String empCode = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginEmpCode" );
			System.out.println(" site code ["+siteCode+"]");
			/*ConnDriver connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverValidator");
			conn.setAutoCommit(false);*/
			
			SimpleDateFormat sdf = new SimpleDateFormat( genericUtility.getDBDateTimeFormat());
			Date confDate = new Date();
			confDateStr = sdf.format(confDate);
			System.out.println("confDate is :"+confDate+" confDate str is : "+confDateStr);
			sql = "SELECT CONFIRMED FROM DEALLOC_ART WHERE TRAN_ID = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranId);
			rs = pstmt.executeQuery();
			if( rs.next() )
			{
				confirmed = checkNull( rs.getString("CONFIRMED") );
			}	
			if( pstmt != null )
			{
				pstmt.close();
				pstmt = null;
			}
			if ( rs != null )
			{
				rs.close();
				rs = null;
			}
			if( confirmed != null && confirmed.equalsIgnoreCase("Y") )
			{
				errString = itmdbAccess.getErrorString("","VTALCONF",loginEmpCode);
				return errString;
			}
			
			/*sql = " SELECT D.CONFIRMED FROM DESPATCH D, WAVE_TASK_DET W , dealloc_art da " +
					" WHERE W.PTCN = DA.PTCN  AND D.WAVE_ID = W.WAVE_ID AND W.REF_ID = D.DESP_ID " +
					" and da.tran_id = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranId);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				System.out.println(" confirmed ["+rs.getString("CONFIRMED")+"]");
				if("Y".equalsIgnoreCase(rs.getString("CONFIRMED")))
				{
					errString = itmdbAccess.getErrorString("","INVPTCNDSP",loginEmpCode);
					return errString;
				}
			}
			rs.close(); rs = null;
			pstmt.close(); pstmt = null;*/
			
			//Checking is one of the carton is already confirmed 
			sql = " SELECT C.STATUS FROM CARTON_MASTER C, DEALLOC_ART_DET DRD WHERE DRD.TRAN_ID = ? AND DRD.CARTON_NO = C.CARTON_NO ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranId);
			rs = pstmt.executeQuery();
			while(rs.next())
			{
				System.out.println(" STATUS ["+rs.getString("STATUS")+"]");
				if("R".equalsIgnoreCase(rs.getString("STATUS")))
				{
					errString = itmdbAccess.getErrorString("","CARTALLCOF",loginEmpCode);
					return errString;
				}
			}
			rs.close(); rs = null;
			pstmt.close(); pstmt = null;
						
			sql =   " SELECT DISTINCT D.CARTON_NO, PD.SALE_ORDER , PD.LINE_NO__SORD, PD.ITEM_CODE, PD.EXP_LEV, PD.LOC_CODE__TO, D.LOT_SL, " +
					//Changed by sumit on 30/03/13 added ptcn column
					//" D.LOC_CODE, PD.LOT_NO, D.QUANTITY, S.CUST_CODE, PH.WAVE_ID, D.PICK_ORDER, D.LINE_NO__PICK, PH.PICK_TYPE " +
				 	//  changed by sankara on 13-06-2013
				    //  " D.LOC_CODE, PD.LOT_NO, D.QUANTITY, S.CUST_CODE, PH.WAVE_ID, D.PICK_ORDER, D.LINE_NO__PICK, PH.PICK_TYPE, D.PTCN "+
			   
					" D.LOC_CODE, PD.LOT_NO, D.QUANTITY, S.CUST_CODE, PH.WAVE_ID, D.PICK_ORDER, D.LINE_NO__PICK, PH.PICK_TYPE, D.PTCN,SD.UNIT,SD.UNIT__STD,SD.CONV__QTY_STDUOM " +
					//" FROM PICK_ORD_DET PD, PICK_ORD_HDR PH , DEALLOC_ART_DET D, SORDER S , CARTON_MASTER CM " +
					// changed by sankara on 13-06-2013 
					//" FROM PICK_ORD_DET PD, PICK_ORD_HDR PH , DEALLOC_ART_DET D, SORDER S " +
					" FROM PICK_ORD_DET PD, PICK_ORD_HDR PH , DEALLOC_ART_DET D, SORDER S,SORDDET SD " +
					" WHERE D.TRAN_ID = ? " +
					//" AND D.CARTON_NO = CM.CARTON_NO " +
					" AND PD.PICK_ORDER = D.PICK_ORDER " +
					" AND PD.LINE_NO = D.LINE_NO__PICK " +
					" AND PH.PICK_ORDER = PD.PICK_ORDER " +
					" AND PH.SALE_ORDER = S.SALE_ORDER "+
					//changed by sankara on 13-06-2013 start...
					" AND PH.SALE_ORDER = SD.SALE_ORDER " +
					" AND D.SALE_ORDER = SD.SALE_ORDER " +
					" AND PD.LINE_NO__SORD = SD.LINE_NO " +
					//changed by sankara on 13-06-2013 end...
					" UNION ALL "+
					" SELECT DISTINCT D.CARTON_NO, RD.SALE_ORDER , RD.LINE_NO__SORD, RD.ITEM_CODE, RD.EXP_LEV, RD.LOC_CODE__TO, D.LOT_SL," +
					//Changed by sumit on 30/03/13 added ptcn column
					//" D.LOC_CODE, D.LOT_NO, D.QUANTITY, S.CUST_CODE, D.WAVE_ID, D.PICK_ORDER RDER, D.LINE_NO__PICK, D.PICK_TYPE " +
				// changed by sankara on 13-06-2013 	
				//	" D.LOC_CODE, D.LOT_NO, D.QUANTITY, S.CUST_CODE, D.WAVE_ID, D.PICK_ORDER RDER, D.LINE_NO__PICK, D.PICK_TYPE, D.PTCN "+				  	
					" D.LOC_CODE, D.LOT_NO, D.QUANTITY, S.CUST_CODE, D.WAVE_ID, D.PICK_ORDER RDER, D.LINE_NO__PICK, D.PICK_TYPE, D.PTCN,SD.UNIT,SD.UNIT__STD,SD.CONV__QTY_STDUOM " +
				// changed by sankara on 13-06-2013 	
			   //	" FROM REPL_ORD_DET RD, REPL_ORD_HDR RH , DEALLOC_ART_DET D, SORDER S"+
			    	" FROM REPL_ORD_DET RD, REPL_ORD_HDR RH , DEALLOC_ART_DET D, SORDER S, SORDDET SD " +
					" WHERE D.TRAN_ID = ?  AND RD.REPL_ORDER = D.PICK_ORDER " +
					" AND RD.LINE_NO = D.LINE_NO__PICK  AND RH.REPL_ORDER = RD.REPL_ORDER " +
					" AND RH.SALE_ORDER = S.SALE_ORDER" +
			 //   chnaged by sankara on 13-06-2013 start
					" AND RH.SALE_ORDER = SD.SALE_ORDER" +
					" AND RD.SALE_ORDER = SD.SALE_ORDER " +
					" AND RD.LINE_NO__SORD = SD.LINE_NO " +
					" AND D.SALE_ORDER = SD.SALE_ORDER " ;
					//" AND RD.LINE_NO__SRD = SD.LINE_NO" ;
					
			     
			
			sql1 = "UPDATE CARTON_MASTER SET STATUS ='R' WHERE CARTON_NO  = ? ";
			pstmt1 = conn.prepareStatement(sql1);
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranId);
			pstmt.setString(2, tranId);
			rs = pstmt.executeQuery();
			while (rs.next())
			{
				//sorderDeAllocate(saleOrder, lineNo, itemCode, DeallocQty, expLev, custCode, lotSl, lotNo, locCode, tranId, allocLineno)
				saleOrder = rs.getString("SALE_ORDER");
				saleOrderLine = rs.getString("LINE_NO__SORD");
				itemCode = rs.getString("ITEM_CODE");
				custCode = rs.getString("CUST_CODE");
				expLev = rs.getString("EXP_LEV");
				lotSl = rs.getString("LOT_SL");
				lotNo = rs.getString("LOT_NO");
				locCode = rs.getString("LOC_CODE");
				locCodeTo = rs.getString("LOC_CODE__TO");
				DeallocQty = rs.getDouble("QUANTITY");
				waveId = rs.getString("WAVE_ID");
				pickOrder = rs.getString("PICK_ORDER");
				pickLineNo = rs.getInt("LINE_NO__PICK");
				pPickType = rs.getString("PICK_TYPE");
				cartonNo = rs.getString("CARTON_NO");
				//Changed by sumit on 30/03/13 
				ptcn = rs.getString("PTCN");
				//changed by sankara on 13/06/2013
				unit = rs.getString("UNIT");
				standardUnit = rs.getString("UNIT__STD");
				convQtyStduom = rs.getDouble("CONV__QTY_STDUOM");
				ptcnSET.add(ptcn);
				saleOrderLine = "   " + saleOrderLine; 
				saleOrderLine = saleOrderLine.substring(saleOrderLine.length()-3);
				System.out.println(" sord line no ["+saleOrderLine+"] locCode ["+locCode+"] locCodeTo["+locCodeTo+"] pickLineNo ["+pickLineNo+"] ptcn ["+ptcn+"]");
				
				if("S".equalsIgnoreCase(pPickType))
				{
					errString = deallocatedRepl(pickOrder, pickLineNo,pPickType, siteCode, itemCode, lotNo, lotSl, DeallocQty, saleOrder, saleOrderLine, expLev, custCode, xtraParams, waveId,cartonNo, conn);
				//	changed by sankara on 13-06-2013
				//	errString = sorderDeAllocate(saleOrder, saleOrderLine, itemCode, DeallocQty, expLev, custCode, lotSl, lotNo, locCode, xtraParams, waveId, "",conn);
					errString = sorderDeAllocate(saleOrder, saleOrderLine, itemCode, DeallocQty, expLev, custCode, lotSl, lotNo, locCode, xtraParams, waveId, "",unit,standardUnit,convQtyStduom,conn);
					continue;
				}
				
				
				
				sql = "";
				
				if(standardUnit == null || standardUnit.trim().length() <= 0 )
				{
					sql = " SELECT UNIT FROM ITEM WHERE ITEM_CODE = ?";
					pstmt2 = conn.prepareStatement(sql);
					pstmt2.setString(1, pickOrder);
					pstmt2.setInt(2, pickLineNo);
					rs2 = pstmt2.executeQuery();
					if( rs2.next())
					{		
						standardUnit = rs2.getString("UNIT");
					}
					
					rs2.close(); rs2 = null;
					pstmt2.close(); pstmt2 = null;	
				}
				
				sql = " SELECT PIH.PICK_ORDER, PIH.PICK_TYPE, PIH.CONFIRMED, PID.LOC_CODE__TO, " +
						" (CASE WHEN PIH.TRF_STATUS IS NULL THEN 'N' ELSE PIH.TRF_STATUS END) AS TRF_STATUS  ,PID.LOC_CODE " +
						" FROM PICK_ISS_HDR PIH, PICK_ISS_DET PID WHERE PIH.PICK_ORDER = ? AND PIH.PICK_ORDER = PID.PICK_ORDER " +
						" AND PID.LINE_NO__ORD = ? ";
				pstmt2 = conn.prepareStatement(sql);
				pstmt2.setString(1, pickOrder);
				pstmt2.setInt(2, pickLineNo);
				rs2 = pstmt2.executeQuery();
				if( rs2.next())
				{									
					pickType = rs2.getString("PICK_TYPE");
					confirmed = rs2.getString("CONFIRMED");
					trfStatus = rs2.getString("TRF_STATUS");
					System.out.println(" pickType ["+pickType+"] confirmed["+confirmed+"] trfStatus ["+trfStatus+"]");
					if( "Y".equalsIgnoreCase(confirmed))
					{
						isPickIssueDone = true;
						if( ("A".equalsIgnoreCase(pickType) || "M".equalsIgnoreCase(pickType)) && "Y".equalsIgnoreCase(trfStatus))
						{
							locCode = rs2.getString("LOC_CODE__TO");
						}
						else
						{
							locCode = rs2.getString("LOC_CODE");
						}
						
						if( "C".equalsIgnoreCase(pickType) )
						{
							locCode = rs2.getString("LOC_CODE__TO");
						}
						
					}
					/*else
					{
						locCode = locCodeTo;
					}*/
				}
				rs2.close(); rs2 = null;
				pstmt2.close(); pstmt2 = null;				
				System.out.println(" locCode ["+locCode+"] locCodeTo ["+locCodeTo+"]");
				
				//Deallocating from pick order in case of issue not done
				if( !isPickIssueDone )
				{
					
					replIss = new HashMap();
					replIss = isReplIssueDone(pickOrder, pickLineNo, conn);
					if("YES".equalsIgnoreCase(replIss.get("isRepl").toString()))
					{
						replOrder = replIss.get("repl_order").toString();
						replOrderLine = Integer.parseInt(replIss.get("repl_order_line").toString());
						System.out.println(" 88 replOrder ["+replOrder+"] replOrderLine ["+replOrderLine+"]");
						
						HashMap replParent = new HashMap();
						replParent = isParentExist(replOrder,replOrderLine, conn);						
						confirmed = replParent.get("confirmed").toString();//confirmed						
						System.out.println("  confirmed ["+confirmed+"] paren_exist ["+replParent.get("paren_exist").toString()+"] ");
						if("Y".equalsIgnoreCase(replParent.get("confirmed").toString()))
						{
							//locCode = replParent.get("loc_code__to").toString();
							//lotNo = replParent.get("lot_no").toString();
							//lotSl = replParent.get("lot_sl").toString();
							System.out.println(" locCode ["+locCode+"] lotNo ["+lotNo+"] lotSl ["+lotSl+"]");	
							//Updating pick_order
							isReplDone = true;
							updateReplOrder(replOrder,replOrderLine,pPickType, siteCode, itemCode, locCode, lotNo, lotSl, DeallocQty, conn, isReplDone, "",0,"",0);	
							updatePickOrder(pickOrder, pickLineNo, DeallocQty, conn, isPickIssueDone,pPickType);
							confReplOrder(replOrder,replOrderLine,pPickType, siteCode, itemCode, locCode, lotNo, lotSl, DeallocQty, conn, isReplDone, "",0,"",0);	
							
						}
						else if ("N".equalsIgnoreCase(confirmed) && "NO".equalsIgnoreCase(replParent.get("paren_exist").toString()))        //parentExist.put("paren_exist", "NO");
						{
							//locCode = replParent.get("loc_code__to").toString();
							//lotNo = replParent.get("lot_no").toString();
							//lotSl = replParent.get("lot_sl").toString();
							System.out.println(" locCode ["+locCode+"] lotNo ["+lotNo+"] lotSl ["+lotSl+"]");
							isReplDone = false;
							updateReplOrder(replOrder,replOrderLine, siteCode,pPickType, itemCode, locCode, lotNo, lotSl, DeallocQty, conn, isReplDone, "",0,"",0);	
							updatePickOrder(pickOrder, pickLineNo, DeallocQty, conn, isPickIssueDone,pPickType);							
							updateReplOrderForActive(pPickType, replOrder,replOrderLine, empCode, conn, "",0,"",0);
							confReplOrder(replOrder,replOrderLine, siteCode,pPickType, itemCode, locCode, lotNo, lotSl, DeallocQty, conn, isReplDone, "",0,"",0);	
						}
						else if ( "N".equalsIgnoreCase(confirmed) && "YES".equalsIgnoreCase(replParent.get("paren_exist").toString()))
						{
							
							System.out.println(" repl order going to search it's ["+replOrder+"] parent ");
							currentReplOrder = replOrder;
							currReplOrdLine = replOrderLine;
							replOrder = "";
							replOrder = replParent.get("pick_order").toString();
							replOrderLine = Integer.parseInt(replParent.get("line_no__pick").toString());
							System.out.println(" new repl order going to serch it's parent ["+replOrder+"] replOrderLine ["+replOrderLine+"]");
							
							replParent.clear();
							replParent = isParentExist(replOrder,replOrderLine, conn);
							
							
							
							confirmed = "";
							confirmed = replParent.get("confirmed").toString();
							System.out.println(" *-* confirmed ["+confirmed+"]");
							if("Y".equalsIgnoreCase(confirmed))
							{
								//locCode = replParent.get("loc_code__to").toString();
								//lotNo = replParent.get("lot_no").toString();
								//lotSl = replParent.get("lot_sl").toString();
								//System.out.println(" locCode ["+locCode+"] lotNo ["+lotNo+"] lotSl ["+lotSl+"]");
								
								isReplDone = true;
								updateReplOrder(currentReplOrder,currReplOrdLine,pPickType, siteCode, itemCode, locCode, lotNo, lotSl, DeallocQty, conn, isReplDone, "",0, "", 0);
								updatePickOrder(pickOrder, pickLineNo, DeallocQty, conn, isPickIssueDone,pPickType);								
								updateReplOrderForActive(pPickType, currentReplOrder,currReplOrdLine, empCode, conn, "",0, "", 0);
								confReplOrder(currentReplOrder,currReplOrdLine,pPickType, siteCode, itemCode, locCode, lotNo, lotSl, DeallocQty, conn, isReplDone, "",0, "", 0);
							}
							else if ("N".equalsIgnoreCase(confirmed) && "NO".equalsIgnoreCase(replParent.get("paren_exist").toString()))        //parentExist.put("paren_exist", "NO");
							{
								//locCode = replParent.get("loc_code__to").toString();
								//lotNo = replParent.get("lot_no").toString();
								//lotSl = replParent.get("lot_sl").toString();
								//System.out.println(" locCode ["+locCode+"] lotNo ["+lotNo+"] lotSl ["+lotSl+"]");
								
								isReplDone = false;
								updateReplOrder(currentReplOrder,currReplOrdLine,pPickType, siteCode, itemCode, locCode, lotNo, lotSl, DeallocQty, conn, isReplDone, replOrder,replOrderLine,"", 0);
								updatePickOrder(pickOrder, pickLineNo, DeallocQty, conn, isPickIssueDone,pPickType);								
								updateReplOrderForActive(pPickType, currentReplOrder,currReplOrdLine, empCode, conn,replOrder,replOrderLine,"", 0);
								confReplOrder(currentReplOrder,currReplOrdLine,pPickType, siteCode, itemCode, locCode, lotNo, lotSl, DeallocQty, conn, isReplDone, replOrder,replOrderLine,"", 0);
							}
							else if ( "N".equalsIgnoreCase(confirmed) && "YES".equalsIgnoreCase(replParent.get("paren_exist").toString()))
							{
								
								replParent.clear();
								replParent = isParentExist(replOrder,replOrderLine, conn);
								supRerplOrder = replParent.get("pick_order").toString();
								supReplLine = Integer.parseInt(replParent.get("line_no__pick").toString());
								
								System.out.println(" DSO REPL_ORDER ["+replOrder+"] replOrderLine ["+supRerplOrder+"]");
								
								confirmed = "";
								confirmed = replParent.get("confirmed").toString();
								System.out.println(" *-* confirmed ["+confirmed+"]");
								if("Y".equalsIgnoreCase(confirmed))
								{
									isReplDone = true;
									updateReplOrder(currentReplOrder,currReplOrdLine,pPickType, siteCode, itemCode, locCode, lotNo, lotSl, DeallocQty, conn, isReplDone, replOrder,replOrderLine,"", 0);
									updatePickOrder(pickOrder, pickLineNo, DeallocQty, conn, isPickIssueDone,pPickType);									
									updateReplOrderForActive(pPickType, currentReplOrder,currReplOrdLine, empCode, conn, replOrder,replOrderLine,"", 0);
									confReplOrder(currentReplOrder,currReplOrdLine,pPickType, siteCode, itemCode, locCode, lotNo, lotSl, DeallocQty, conn, isReplDone, replOrder,replOrderLine,"", 0);
								}
								else if ("N".equalsIgnoreCase(confirmed) )        //parentExist.put("paren_exist", "NO");
								{
									//confReplOrder
									isReplDone = false;
									updateReplOrder(currentReplOrder,currReplOrdLine,pPickType, siteCode, itemCode, locCode, lotNo, lotSl, DeallocQty, conn, isReplDone, replOrder,replOrderLine,supRerplOrder, supReplLine);
									updatePickOrder(pickOrder, pickLineNo, DeallocQty, conn, isPickIssueDone,pPickType);									
									updateReplOrderForActive(pPickType, currentReplOrder,currReplOrdLine, empCode, conn,replOrder,replOrderLine,supRerplOrder, supReplLine);
									confReplOrder(currentReplOrder,currReplOrdLine,pPickType, siteCode, itemCode, locCode, lotNo, lotSl, DeallocQty, conn, isReplDone, replOrder,replOrderLine,supRerplOrder, supReplLine);
								}								
								
							}
							
						}						
					}
					else
					{
						updatePickOrder(pickOrder, pickLineNo, DeallocQty, conn, isPickIssueDone,pPickType);						
					}
				}
				else
				{
					updatePickOrder(pickOrder, pickLineNo, DeallocQty, conn, isPickIssueDone,pPickType);					
					
				}			
				
				int update = updateWaveTaskDet(pickOrder, "PICK", conn, pPickType, empCode);
				if( update > 0 )
				{
					System.out.println(update+" wave_task_det update syuccessfully");
				}
			 // changed by sankara on 13-06-2013	
			//	errString = sorderDeAllocate(saleOrder, saleOrderLine, itemCode, DeallocQty, expLev, custCode, lotSl, lotNo, locCode, xtraParams, waveId, pickOrder,conn);
				errString = sorderDeAllocate(saleOrder, saleOrderLine, itemCode, DeallocQty, expLev, custCode, lotSl, lotNo, locCode, xtraParams, waveId, pickOrder,unit,standardUnit,convQtyStduom,conn);
				if (errString != null && errString.trim().length() > 0)
				{
					System.out.println("errString :"+errString);
					return errString;
				}
				
				//Updating  carton_no of carton master
				System.out.println(" ** cartonNo ["+cartonNo+"]");
				if( "A".equalsIgnoreCase(pPickType))
				{
					sql = " SELECT SUM(QUANTITY - CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE DEALLOC_QTY END) AS QUANTITY " +
							" FROM PICK_ORD_DET WHERE CARTON_NO = ? ";
					pstmt2 = conn.prepareStatement(sql);
					pstmt2.setString(1, cartonNo);
					rs2 = pstmt2.executeQuery();
					if( rs2.next())
					{
						quantity = rs2.getDouble("QUANTITY");
					}
					rs2.close(); rs2 = null;
					pstmt2.close(); pstmt2 = null;
					System.out.println( " quantity --> ["+quantity+"]");
					if( quantity == 0 )
					{
						pstmt1.setString(1, cartonNo);
						if( pstmt1.executeUpdate() > 0)
						{
							System.out.println(rs.getString("CARTON_NO")+" carton_no updated successfully ");
						}
						pstmt1.clearParameters();
					}
				}
				else
				{	
					if("M".equalsIgnoreCase(pPickType))
					{
						sql = "UPDATE PACK_DET SET STATUS = 'R' WHERE CARTON_NO = ? ";
						pstmt2 = conn.prepareStatement(sql);
						pstmt2.setString(1, cartonNo);
											
						if( pstmt2.executeUpdate() > 0 )
						{
							System.out.println(cartonNo+" cartonNo of PACK_DET updated successfully from PACK_DET table ");
						}
						pstmt2.close(); pstmt2 = null;
					}
					
					pstmt1.setString(1, cartonNo);
					if( pstmt1.executeUpdate() > 0)
					{
						System.out.println(rs.getString("CARTON_NO")+" carton_no updated successfully ");
					}
					pstmt1.clearParameters();
				
				}
				
				//
				
				if(!"A".equalsIgnoreCase(pPickType))
				{
					update = 0;
					sql = 	" UPDATE CARTON_MASTER SET CHANGE_STATUS = ? WHERE MASTER_CARTON = " +
							" (SELECT MASTER_CARTON FROM CARTON_MASTER WHERE CARTON_NO = ? AND MASTER_CARTON IS NOT NULL)";
					pstmt2 = conn.prepareStatement(sql);
					pstmt2.setString(1, "Y");
					pstmt2.setString(2, cartonNo);
					update = pstmt2.executeUpdate();
					if( update > 0 )
					{
						System.out.println(" WAVE_STATUS_ORG_DET updated succefully for Masters----");
					}
					pstmt2.close(); pstmt2 = null;
				}
				else
				{
						update = 0;
						sql = 	" UPDATE CARTON_MASTER SET CHANGE_STATUS = ? WHERE CARTON_NO = ? AND STATUS <> ? " ;
					
						pstmt2 = conn.prepareStatement(sql);
						pstmt2.setString(1, "Y");
						pstmt2.setString(2, cartonNo);
						pstmt2.setString(3, "R");
						
						update = pstmt2.executeUpdate();
						if( update > 0 )
						{
							System.out.println(" WAVE_STATUS_ORG_DET updated succefully for Actives ------ ");
						}
						pstmt2.close(); pstmt2 = null;
				}
				
			}
			rs.close(); rs = null;
			pstmt.close(); pstmt = null;
			if( pstmt1 != null )
			{
				pstmt1.close();
				pstmt1 = null;
			}
			
			/*sql = " ";
			sql =   " SELECT DISTINCT D.CARTON_NO FROM PICK_ORD_DET PD, PICK_ORD_HDR PH , DEALLOC_ART_DET D, SORDER S " +
					" WHERE D.TRAN_ID = ? AND D.PICK_ORDER = PH.PICK_ORDER AND PH.PICK_ORDER = PD.PICK_ORDER "+
					" AND D.LINE_NO__PICK = PD.LINE_NO AND PH.SALE_ORDER = S.SALE_ORDER ";
			
			sql1 = "UPDATE CARTON_MASTER SET STATUS ='R' WHERE CARTON_NO  = ? ";
			pstmt1 = conn.prepareStatement(sql1);
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranId);
			rs = pstmt.executeQuery();
			while (rs.next())
			{				
				pstmt1.setString(1, rs.getString("CARTON_NO"));
				if( pstmt1.executeUpdate() > 0)
				{
					System.out.println(rs.getString("CARTON_NO")+" carton_no updated successfully ");
				}
				pstmt1.clearParameters();
				
			}
			rs.close(); rs = null;
			pstmt.close(); pstmt = null;	*/		
			
			
			sql = "UPDATE DEALLOC_ART SET CONFIRMED = 'Y', CONF_DATE = ? WHERE TRAN_ID = ?";
			pstmt = conn.prepareStatement( sql );	 
			pstmt.setTimestamp(1, Timestamp.valueOf( confDateStr ) );				
			pstmt.setString(2, tranId); 
			updCnt = pstmt.executeUpdate();
			if ( updCnt > 0 )
			{
				System.out.println("DEALLOC_ART Updated Successfully ::"+updCnt);
			}
			pstmt.close();
			pstmt = null;		
			
			System.out.println(" ptcnSET "+ptcnSET);
			System.out.println(" errString "+errString);
			
			//Changed by sumit on 04/06/13 updating WAVE_STATUS_ORG to re-print label start.
			sql = "UPDATE WAVE_STATUS_ORG SET PRINT_STATUS = ? WHERE PTCN = ? ";
			pstmt = conn.prepareStatement(sql);	
			//Changed by sumit on 04/06/13 updating WAVE_STATUS_ORG to re-print label end
			
			if(ptcnSET.size() > 0 && errString.trim().length() == 0)
			{
				String tempPTCN = "";
				Iterator it = ptcnSET.iterator();
				while( it.hasNext())
				{
					tempPTCN = it.next().toString();
					System.out.println(" tempPTCN --> ["+tempPTCN+"]ptcn["+ptcn);
					
					//Changed by sumit on 04/06/13 updating WAVE_STATUS_ORG to re-print label start
					pstmt.setString(1, "N");
					pstmt.setString(2, tempPTCN);
					if( pstmt.executeUpdate() > 0 )
					{
						System.out.println(" WAVE_STATUS_ORG updated successfully");
					}
					pstmt.clearParameters();
					//Changed by sumit on 04/06/13 updating WAVE_STATUS_ORG to re-print label end.
					
					ConsolidatToDoc conToDoc = new ConsolidatToDoc();				
					//errString = conToDoc.process(ptcn, conn,xtraParams);
					errString = conToDoc.process(tempPTCN, conn,xtraParams);
					if ( errString != null && errString.trim().length() > 0 )
					{
						isError = true;
						return errString;
					}
					//Changed By Pragyan 02-JUL-14 (Issue tracker No 396)To update status as C if consolidated to doc called.start
					else
					{
						updateWaveStatusDespatch(tempPTCN,conn);
						updateCartonMasterstatus(tempPTCN, conn);
					}
					//Changed By Pragyan 02-JUL-14 (Issue tracker No 396)To get proper count from pick order and pick line no.
				}				
				
				
			}
			//Changed by sumit on 04/06/13 closing prepared statement start 
			if(pstmt != null)
			{
				pstmt.close(); pstmt = null;
			}
			//Changed by sumit on 04/06/13 closing prepared statement end
			System.out.println("ret string :"+errString);
			//changed by sankara on 04/02/14 update carton_master status from pick deallocation start.
			/*//int updatecount =  updateCartonMasterstatus(ptcn, conn);
			System.out.println("updatecount::::"+updatecount);
			if( updatecount > 0 )
			{
				System.out.println("update status in carton_master sucessfully");
			}*/
			//changed by sankara on 04/02/14 update carton_master status from pick deallocation end.
			//changed by sankara on 21/06/14 update sale order ship status from pick deallocation start.
			int shipstatuscount = updatesaleOrdershipStatus(saleOrder,ptcn,conn);
			System.out.println("shipstatuscount::::"+shipstatuscount);
			if( shipstatuscount > 0 )
			{
				System.out.println("shipstatuscount["+shipstatuscount+"]");
			}
			//changed by sankara on 21/06/14 update sale order ship status from pick deallocation end.
		}
		catch(Exception e)
		{
			isError = true;
			/*try
			{
				conn.rollback();
			}
			catch(Exception e1)
			{
				e1.printStackTrace();
			}*/
			
			e.printStackTrace();
			errString = e.getMessage();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				System.out.println("isError =["+isError+"]");
				
				/*if ( !(isError) && conStat == true )
				{	
					System.out.println("Commiting and Closing Connection..........");
					conn.commit();
					errString = itmdbAccess.getErrorString("","VTSUCC1",loginEmpCode);
				}
				else if(conStat == true || isError)
				{
					conn.rollback();

				}*/
				
				if (rs != null )
				{
					rs.close(); rs = null;
				}
				if( pstmt != null )
				{					
					pstmt.close();
					pstmt = null;
				}
				/*if( conn != null && conStat == true && !conn.isClosed() )
				{
					System.out.println(" commiting connection ");
					conn.commit();
					conn.close();
					conn = null;
				}*/
				
			}
			catch( Exception e)
			{
				e.printStackTrace();
				errString = e.getMessage();
				throw new ITMException(e);
			}
		}		
		return errString;		
	}
	private String checkNull(String input)
	{
		if(input == null)
		{
			input = "";
		}
		return input;
	}
	
	//changed by sankara on 13-06-2013
	//private String sorderDeAllocate(String saleOrder, String lineNo, String itemCode, double DeallocQty, String expLev,String custCode,String lotSl,String lotNo,String locCode, String xtraParams, String waveId, String pickOrder, Connection conn ) throws ITMException
	private String sorderDeAllocate(String saleOrder, String lineNo, String itemCode, double DeallocQty, String expLev,String custCode,String lotSl,String lotNo,String locCode, String xtraParams, String waveId, String pickOrder,String unit,String mbaseUnit,double convQtyStduom, Connection conn ) throws ITMException
	{
		System.out.println("updateSOrder calling ..............");
		String updateSorditem = null,updateSordAllocDet=null;
		String updateSql= null,DelQtyAlloc= null,SqlQtyAlloc= null ;
		String errString = "";
		String error = "";
		String errCode = "";
		String siteCode = "", chgUser = "", chgTerm = "", sql = "", despId = "";
		String stockSql = "";
		double quantity = 0.0;
		double shipperSize = 0;
		double totalGrossWeight = 0,totalTareWeight = 0,totalNetWeight = 0; 
		double grossWeight = 0d, tareWeight = 0d, netWeight = 0d;
		double grossWtPerArt = 0d;
		double tareWtPerArt = 0d;
		double grossWeightItem = 0d;
		double netWeightItem = 0d;
		int noArt = 0, totalNoArt = 0;
		
		//Changed By sankara
		double convFact = 0,convertQty = 0,qtyStduom = 0,meffQty;
		DistCommon disCommon = new DistCommon();
		//Connection conn = null;
		PreparedStatement pstmt=null, pstmt1 = null,pstmtStock = null;
		Statement st = null;
		ResultSet rsQtyAlloc=null, rs = null, rs1 = null;
		HashMap invallocTraceMap = new HashMap();
		//ConnDriver connDriver = new ConnDriver();
		InvAllocTraceBean invallocTraceBean = new InvAllocTraceBean();
		
		try
		{
			siteCode = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "site_code");
			chgUser = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginCode");
			chgTerm = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "termId");
			System.out.println(" site code :["+siteCode+"] chgUser:["+chgUser+"] chgTerm:["+chgTerm+"] locCode ["+locCode+"]");
			
			/*conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;*/
			st = conn.createStatement(); 
			invallocTraceMap.put("ref_ser","DEART");
			invallocTraceMap.put("site_code",siteCode);
			invallocTraceMap.put("item_code",itemCode);
			invallocTraceMap.put("loc_code",locCode);
			invallocTraceMap.put("lot_no",lotNo);
			invallocTraceMap.put("lot_sl",lotSl);

			invallocTraceMap.put("alloc_qty",new Double(-1 * DeallocQty));

			System.out.print("DeallocQty = " + (-1*DeallocQty));
			invallocTraceMap.put("chg_user",chgUser);
			invallocTraceMap.put("chg_term",chgTerm);
			//invallocTraceMap.put("chg_win","W_SORD_DEALLOC"); dealloc_art
			invallocTraceMap.put("chg_win","W_DEALLOC_ART");			
			System.out.println(" *******invallocTraceMap****** "+invallocTraceMap);
			
			if (DeallocQty > 0 && saleOrder.trim().length()>0)
			{
				
				
				
				
				//Change by Sankara.start
				if(!unit.equalsIgnoreCase(mbaseUnit))											
				{
					convFact = 0;					
					ArrayList convQtyList = disCommon.getConvQuantityFact(mbaseUnit, unit, itemCode, DeallocQty, convFact,conn);												
					System.out.println("convQtyList = "+convQtyList);						
					convertQty = Double.parseDouble(convQtyList.get(1).toString());												
					if(convertQty == -999999999)
					{
						convertQty = 0;								
					}
					
				}
				else
				{
					convertQty = DeallocQty;
					
				}
				
				
				/*if(qtyStduom == 0)
				{											
					if(!unit.equalsIgnoreCase(mbaseUnit))											
					{
						if(convQtyStduom != 0)
						{
							convFact = convQtyStduom;
						}
						ArrayList convQtyList = disCommon.getConvQuantityFact(unit, mbaseUnit, itemCode, DeallocQty, convFact,conn);												
						System.out.println("convQtyList = "+convQtyList);
						meffQty = Double.parseDouble(convQtyList.get(1).toString());
						System.out.println("meffQty ["+meffQty+"]");

						if(meffQty == -999999999)
						{
							meffQty = 0;								
						}
						if (convQtyStduom == 0)
						{
							convQtyStduom= Double.parseDouble(convQtyList.get(0).toString());
						}
					}
					else
					{
						meffQty = DeallocQty ;
						if (convQtyStduom == 0) 
						{
							convQtyStduom=1;
						}

					}
					qtyStduom = meffQty;
				}
				 */			
				
				qtyStduom = DeallocQty;
				
				System.out.println("Convertd Quantity convertQty  BASEUNIT unit------->"+convertQty);
				System.out.println("Convertd Quantity qtyStduom UNIT BASEUNIT------->"+qtyStduom);
				//Changed by Sankara.end
				//changed by sankara on 06/05/14 for manual stock allocation start.
				String allocMode = "";
				sql = " SELECT ALLOC_MODE FROM SORDALLOC "
						+ " WHERE SALE_ORDER = '" + saleOrder + " ' "
						+ " AND LINE_NO = '" + lineNo + "' "
						+ " AND EXP_LEV = '" + expLev + "' "
						+ " AND ITEM_CODE = '" + itemCode + "' "
						+ " AND LOT_NO = '" + lotNo + "' "
						+ " AND LOT_SL = '" + lotSl + "' "
						+ " AND LOC_CODE = '" + locCode + "' " ;
				pstmt =  conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					allocMode = checkNull(rs.getString("ALLOC_MODE"));
					System.out.println("allocMode:::::::::"+allocMode);
				}
				pstmt.close();
				pstmt=null;
				rs.close();
				rs=null;
				//changed by sankara on 06/05/14 for manual stock allocation end.
				invallocTraceMap.put("ref_id",saleOrder);
				invallocTraceMap.put("ref_line",lineNo);
				//changed by sankara on 06/05/14 for manual stock allocation start. 
				//errString = invallocTraceBean.updateInvallocTrace(invallocTraceMap,conn);
				if( !"M".trim().equalsIgnoreCase(allocMode) )
				{
					errString = invallocTraceBean.updateInvallocTrace(invallocTraceMap,conn);
				}
				//changed by sankara on 06/05/14 for manual stock allocation end. 

				if (errString != null && errString.trim().length() > 0)
				{
					System.out.println("errString :::"+ errString );
					return errString;
				}
                
				//changed by sankara on 06/05/14 added if condition for checking manual stock allocation case start
				if( !"M".trim().equalsIgnoreCase(allocMode) )
				{
					updateSorditem ="UPDATE SORDITEM  SET QTY_ALLOC = QTY_ALLOC - " + new Double(DeallocQty).toString()
							+" WHERE SALE_ORDER = '" + saleOrder + "' "
							+" AND LINE_NO = '" + lineNo + "' "
							+" AND EXP_LEV = '" + expLev + "' ";

					System.out.println("updateSql------->"+updateSorditem);
					st.executeUpdate(updateSorditem);
					System.out.println("UPDATE  SUCCESS FOR SORDITEM....>>>>>>>>");

					updateSql = " UPDATE SORDALLOC SET QTY_ALLOC =  QTY_ALLOC - " + new Double(DeallocQty).toString()
							+ " WHERE SALE_ORDER = '" + saleOrder + " ' "
							+ " AND LINE_NO = '" + lineNo + "' "
							+ " AND EXP_LEV = '" + expLev + "' "
							+ " AND ITEM_CODE = '" + itemCode + "' "
							+ " AND LOT_NO = '" + lotNo + "' "
							+ " AND LOT_SL = '" + lotSl + "' "
							+ " AND LOC_CODE = '" + locCode + "' " ;
					System.out.println("updateSql:::>>>>"+ updateSql);
					st.executeUpdate(updateSql);
					System.out.println("UPDATE  SUCCESS FOR SORDALLOC....");
				}
				if( "M".trim().equalsIgnoreCase(allocMode) )
				{
					System.out.println("inside if1.....");
					updateSql = " UPDATE SORDALLOC SET WAVE_FLAG = ? " 
							+ " WHERE SALE_ORDER = '" + saleOrder + " ' "
							+ " AND LINE_NO = '" + lineNo + "' "
							+ " AND EXP_LEV = '" + expLev + "' "
							+ " AND ITEM_CODE = '" + itemCode + "' "
							+ " AND LOT_NO = '" + lotNo + "' "
							+ " AND LOT_SL = '" + lotSl + "' "
							+ " AND LOC_CODE = '" + locCode + "' " ;
					System.out.println("updateSql:::>>>>"+ updateSql);
					pstmt = conn.prepareStatement(updateSql);
					pstmt.setString(1, "N");
					int updateCount = pstmt.executeUpdate();
					if( updateCount > 0 )
					{
						System.out.println("wave flag updateCount in sordalloc::::::::"+updateCount);
					}
					pstmt.close();
					pstmt = null;		
				}
				//changed by sankara on 06/05/14 added if condition for checking manual stock allocation case end
				SqlQtyAlloc = "SELECT QTY_ALLOC FROM SORDALLOC "
							+ " WHERE SALE_ORDER = '" + saleOrder + " ' "
							+ " AND LINE_NO = '" + lineNo + "' "
							+ " AND EXP_LEV = '" + expLev + "' "
							+ " AND ITEM_CODE = '" + itemCode + "' "
							+ " AND LOT_NO = '" + lotNo + "' "
							+ " AND LOT_SL = '" + lotSl + "' "
							+ " AND LOC_CODE = '" + locCode + "' " ;
				rsQtyAlloc  = st.executeQuery(SqlQtyAlloc);
				if (rsQtyAlloc.next())
				{
					System.out.println("Updated Allocated Qty :::"+rsQtyAlloc.getDouble(1));
					if(rsQtyAlloc.getDouble(1)<=0)
					{
						DelQtyAlloc = "DELETE FROM SORDALLOC"
									+ " WHERE SALE_ORDER = '" + saleOrder + " ' "
									+ " AND LINE_NO = '" + lineNo + "' "
									+ " AND EXP_LEV = '" + expLev + "' "
									+ " AND ITEM_CODE = '" + itemCode + "' "
									+ " AND LOT_NO = '" + lotNo + "' "
									+ " AND LOT_SL = '" + lotSl + "' "
									+ " AND LOC_CODE = '" + locCode + "' " ;
						System.out.println("DelQtyAlloc:::"+DelQtyAlloc);
						st.executeUpdate(DelQtyAlloc);
						System.out.println("Delete completed ");
					}
				}
				rsQtyAlloc.close();
				
				/*sql = "SELECT DESP_ID FROM DESPATCHDET WHERE SORD_NO = ? AND LINE_NO__SORD = ? AND EXP_LEV = ? " +
						" D.DESP_ID = (SELECT REF_ID FROM WAVE_TASK_DET WHERE WAVE_ID = ? AND REF_SER ='S-DSP' AND SALE_ORDER = ? ) " +
						" AND ITEM_CODE = ? AND LOT_NO = ? AND LOT_SL = ? AND LOC_CODE = ? ";
				pstmt1 = conn.prepareStatement(sql);
				pstmt1.setString(1, saleOrder);
				pstmt1.setString(2, lineNo);
				pstmt1.setString(3, expLev);
				pstmt1.setString(4, waveId);
				pstmt1.setString(5, saleOrder);
				pstmt1.setString(6, itemCode);
				pstmt1.setString(7, lotNo);
				pstmt1.setString(8, lotSl);
				pstmt1.setString(9, locCode);
				rs1 = pstmt1.executeQuery();
				if( rs1.next())
				{
					
				}*/
				
				stockSql = "SELECT (CASE WHEN GROSS_WT_PER_ART IS NULL THEN 0 ELSE GROSS_WT_PER_ART END) AS GROSS_WT_PER_ART,"
						+ " (CASE WHEN TARE_WT_PER_ART IS NULL THEN 0 ELSE TARE_WT_PER_ART END) AS TARE_WT_PER_ART,"
						+ " (CASE WHEN PALLET_WT IS NULL THEN 0 ELSE PALLET_WT END) AS PALLET_WT, "
						+ " MFG_DATE, EXP_DATE, SITE_CODE__MFG, PACK_CODE, RETEST_DATE, RATE FROM STOCK "								
						+ " WHERE ITEM_CODE = ? AND SITE_CODE = ? AND LOC_CODE = ? AND LOT_NO = ? AND LOT_SL = ? ";
				pstmtStock = conn.prepareStatement(stockSql);
				
				sql = "";
				sql = " SELECT ( QUANTITY - ?) AS QUANTITY, DESP_ID FROM DESPATCHDET WHERE SORD_NO = ? AND LINE_NO__SORD = ? AND EXP_LEV = ? " +
						" AND DESP_ID = (SELECT REF_ID FROM WAVE_TASK_DET WHERE WAVE_ID = ? AND REF_SER = 'S-DSP' AND SALE_ORDER = ? )" +
						" AND ITEM_CODE = ? AND LOT_NO = ? AND LOT_SL = ? AND LOC_CODE = ? ";
				pstmt = conn.prepareStatement(sql);
				//Changed By Sankara
				//pstmt.setDouble(1, DeallocQty);
				pstmt.setDouble(1, convertQty);
				pstmt.setString(2, saleOrder);
				pstmt.setString(3, lineNo);
				pstmt.setString(4, expLev);
				pstmt.setString(5, waveId);
				pstmt.setString(6, saleOrder);
				pstmt.setString(7, itemCode);
				pstmt.setString(8, lotNo);
				pstmt.setString(9, lotSl);
				pstmt.setString(10, locCode);
				rs = pstmt.executeQuery();
				if( rs.next())
				{
					quantity = rs.getDouble("QUANTITY");
					despId = rs.getString("DESP_ID");				
				
					if( quantity <= 0 )
					{
						sql = "";
						sql = " DELETE FROM DESPATCHDET WHERE SORD_NO = ? AND LINE_NO__SORD = ? AND EXP_LEV = ? " +
								" AND DESP_ID = (SELECT REF_ID FROM WAVE_TASK_DET WHERE WAVE_ID = ? AND REF_SER = 'S-DSP' AND SALE_ORDER = ? )" +
								" AND ITEM_CODE = ? AND LOT_NO = ? AND LOT_SL = ? AND LOC_CODE = ? ";
						pstmt1 = conn.prepareStatement(sql);					
						pstmt1.setString(1, saleOrder);
						pstmt1.setString(2, lineNo);
						pstmt1.setString(3, expLev);
						pstmt1.setString(4, waveId);
						pstmt1.setString(5, saleOrder);
						pstmt1.setString(6, itemCode);
						pstmt1.setString(7, lotNo);
						pstmt1.setString(8, lotSl);
						pstmt1.setString(9, locCode);
						
						if ( pstmt1.executeUpdate() > 0 )
						{
							System.out.println(despId+" deleted successfully ");
						}
						pstmt1.close(); pstmt1 = null;				
						
					}
					else
					{
						sql = ""; //QUANTITY__STDUOM, QUANTITY_REAL
						sql = " UPDATE DESPATCHDET SET QUANTITY = (QUANTITY - ?), QUANTITY__STDUOM = ( QUANTITY__STDUOM - ?), " +
								" QUANTITY_REAL = (QUANTITY_REAL - ? )  WHERE  SORD_NO = ? AND LINE_NO__SORD = ? AND EXP_LEV = ? " +
								" AND DESP_ID = (SELECT REF_ID FROM WAVE_TASK_DET WHERE WAVE_ID = ? AND REF_SER = 'S-DSP' AND SALE_ORDER = ? )" +
								" AND ITEM_CODE = ? AND LOT_NO = ? AND LOT_SL = ? AND LOC_CODE = ? ";
						pstmt1 = conn.prepareStatement(sql);
						/*pstmt1.setDouble(1, DeallocQty);
						pstmt1.setDouble(2, DeallocQty);
						pstmt1.setDouble(3, DeallocQty);*/
						pstmt1.setDouble(1, convertQty);
						pstmt1.setDouble(2, qtyStduom);
						pstmt1.setDouble(3, convertQty);
						pstmt1.setString(4, saleOrder);
						pstmt1.setString(5, lineNo);
						pstmt1.setString(6, expLev);
						pstmt1.setString(7, waveId);
						pstmt1.setString(8, saleOrder);
						pstmt1.setString(9, itemCode);
						pstmt1.setString(10, lotNo);
						pstmt1.setString(11, lotSl);
						pstmt1.setString(12, locCode);
						
						if ( pstmt1.executeUpdate() > 0 )
						{
							System.out.println(despId+" updated successfully ");
						}
						pstmt1.close(); pstmt1 = null;
						
						//Updating no_art if deallocated quantity after despatch populated
						System.out.println(" Updating NO_ART in ");
						//sql = " SELECT QUANTITY, DESP_ID FROM DESPATCHDET WHERE SORD_NO = ? AND LINE_NO__SORD = ? AND EXP_LEV = ? " +
						sql = " SELECT QUANTITY__STDUOM, DESP_ID FROM DESPATCHDET WHERE SORD_NO = ? AND LINE_NO__SORD = ? AND EXP_LEV = ? " +
								" AND DESP_ID = (SELECT REF_ID FROM WAVE_TASK_DET WHERE WAVE_ID = ? AND REF_SER = 'S-DSP' AND SALE_ORDER = ? )" +
								" AND ITEM_CODE = ? AND LOT_NO = ? AND LOT_SL = ? AND LOC_CODE = ? ";
						pstmt1 = conn.prepareStatement(sql);						
						pstmt1.setString(1, saleOrder);
						pstmt1.setString(2, lineNo);
						pstmt1.setString(3, expLev);
						pstmt1.setString(4, waveId);
						pstmt1.setString(5, saleOrder);
						pstmt1.setString(6, itemCode);
						pstmt1.setString(7, lotNo);
						pstmt1.setString(8, lotSl);
						pstmt1.setString(9, locCode);
						rs1 = pstmt1.executeQuery();
						if( rs1.next())
						{
							//quantity = rs1.getDouble("QUANTITY");
							quantity = rs1.getDouble("QUANTITY__STDUOM");
						}
						rs1.close(); rs1 = null;
						pstmt1.close(); pstmt1.close();
						
						HashMap itmVolumeMap = new HashMap();
						itmVolumeMap = getItemVoumeMap(itemCode,lotNo,conn);
						shipperSize = (Double) itmVolumeMap.get("SHIPPER_SIZE");
						grossWeightItem = (Double) itmVolumeMap.get("ITEM_GROSS_WEIGHT");
						netWeightItem = (Double) itmVolumeMap.get("CASE_GROSS_WEIGHT");	
						System.out.println("shipperSize ["+shipperSize+"] grossWeightItem ["+grossWeightItem+"] netWeightItem ["+netWeightItem+"]");
						
						//+ " WHERE ITEM_CODE = ? AND SITE_CODE = ? AND LOC_CODE = ? AND LOT_NO = ? AND LOT_SL = ? ";
						pstmtStock.setString(1, itemCode);
						pstmtStock.setString(2, siteCode);
						pstmtStock.setString(3, locCode);
						pstmtStock.setString(4, lotNo);
						pstmtStock.setString(5, lotSl);
						rs1 = pstmtStock.executeQuery();
						if( rs1.next())
						{
							grossWtPerArt = rs1.getDouble("GROSS_WT_PER_ART");
							tareWtPerArt = rs1.getDouble("TARE_WT_PER_ART");
						}
						rs1.close(); rs1 = null;						
						pstmtStock.clearParameters();
						System.out.println("grossWtPerArt ["+grossWtPerArt+"] tareWtPerArt["+tareWtPerArt+"]");
						
						noArt = (int) (quantity / shipperSize);						
						if(quantity < shipperSize)
						{
							grossWeight = (grossWeightItem * quantity);
							netWeight  = (netWeightItem * quantity);
						}
						else
						{
							grossWeight = (grossWtPerArt * noArt);							
							tareWeight = (tareWtPerArt * noArt);
							netWeight  = grossWeight -  tareWeight;
						}
						System.out.println(" NO_ART ["+noArt+"] grossWeight["+grossWeight+"] tareWeight["+tareWeight+"] netWeight["+netWeight+"]");
						/*totalNoArt = totalNoArt + noArt;
						totalGrossWeight = totalGrossWeight + grossWeight;
						totalTareWeight = totalTareWeight + tareWeight ;  
						totalNetWeight = totalNetWeight + netWeight;*/
						
						sql = " UPDATE DESPATCHDET SET GROSS_WEIGHT = ?, TARE_WEIGHT = ?, NETT_WEIGHT = ?, NO_ART = ? WHERE  SORD_NO = ? AND LINE_NO__SORD = ? AND EXP_LEV = ? " +
								" AND DESP_ID = (SELECT REF_ID FROM WAVE_TASK_DET WHERE WAVE_ID = ? AND REF_SER = 'S-DSP' AND SALE_ORDER = ? )" +
								" AND ITEM_CODE = ? AND LOT_NO = ? AND LOT_SL = ? AND LOC_CODE = ? ";
						pstmt1 = conn.prepareStatement(sql);
						pstmt1.setDouble(1, grossWeight);
						pstmt1.setDouble(2, tareWeight);
						pstmt1.setDouble(3, netWeight);
						pstmt1.setDouble(4, noArt);
						pstmt1.setString(5, saleOrder);
						pstmt1.setString(6, lineNo);
						pstmt1.setString(7, expLev);
						pstmt1.setString(8, waveId);
						pstmt1.setString(9, saleOrder);
						pstmt1.setString(10, itemCode);
						pstmt1.setString(11, lotNo);
						pstmt1.setString(12, lotSl);
						pstmt1.setString(13, locCode);
						if( pstmt1.executeUpdate() > 0 )
						{
							System.out.println(despId+" updated DESPATCHDET successfully ");
						}
						pstmt1.close(); pstmt1 = null;
						
					}
					
					sql = " SELECT SUM(NO_ART) AS NO_ART, SUM(GROSS_WEIGHT) AS GROSS_WEIGHT , SUM(TARE_WEIGHT) AS TARE_WEIGHT , " +
						  " SUM(NETT_WEIGHT) AS NETT_WEIGHT FROM DESPATCHDET WHERE DESP_ID = ?";
					pstmt1 = conn.prepareStatement(sql);
					pstmt1.setString(1, despId);
					rs1 = pstmt1.executeQuery();
					if( rs1.next() )
					{
						totalNoArt = rs1.getInt("NO_ART");
						totalGrossWeight = rs1.getDouble("GROSS_WEIGHT");
						totalTareWeight = rs1.getDouble("TARE_WEIGHT");
						totalNetWeight = rs1.getDouble("NETT_WEIGHT");
					}
					rs1.close(); rs1 = null;
					pstmt1.close(); pstmt1 = null;
					
					sql = "UPDATE  DESPATCH SET  GROSS_WEIGHT = ?, TARE_WEIGHT = ? ,NETT_WEIGHT = ?, NO_ART = ?   WHERE DESP_ID = ? ";
					pstmt1 = conn.prepareStatement(sql);
					pstmt1.setDouble(1, totalGrossWeight);
					pstmt1.setDouble(2, totalTareWeight);
					pstmt1.setDouble(3, totalNetWeight);
					pstmt1.setInt(4, totalNoArt);
					pstmt1.setString(5, despId);
					if( pstmt1.executeUpdate() > 0 )
					{
						System.out.println(despId+" of DESPATCH update successfully");
					}
					pstmt1.close(); pstmt1 = null;
					
				}
				rs.close();rs = null;
				pstmt.close(); pstmt = null;
				
				if( pstmtStock != null)
				{
					pstmtStock.close(); pstmtStock = null;
				}
				
			}
			st.close();
			invallocTraceMap.clear();
			//conn.commit();
		}		
		catch(Exception e)
		{
			System.out.println("Exception :" + e);
			errString = e.getMessage();
			e.printStackTrace();
			/*try
			{
				conn.rollback();
			}
			catch(Exception e1)
			{
				e = e1;
			}*/
			
			return errString ;
		}
		finally
		{
			try
			{
				/*if(conn != null)
				{*/
					if( rs != null)
					{
						rs.close(); rs = null;
					}
					if( rs1 != null)
					{
						rs1.close(); rs1 = null;
					}
					if( pstmt != null )
					{
						pstmt.close(); pstmt = null;
					}
					if( pstmt1 != null )
					{
						pstmt1.close(); pstmt1 = null;
					}
					if( pstmtStock != null )
					{
						pstmtStock.close();
						pstmtStock = null;
					}
					if(st != null)
					{
						st.close();
						st = null;
					}
					/*conn.close();
					conn = null;*/
				//}
			}
			catch(Exception e)
			{
				errString = e.getMessage();
				e.printStackTrace();
			}			
		}	
		
		return errString;
	}
	
	//Checking where parent repl exist 
	private HashMap isReplIssueDone(String pickOrder, int pickOrderLine, Connection conn) throws ITMException
	{
		String sql = "";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		boolean isRepl = false;
		HashMap replOrder = new HashMap();
		String tempReplOrder = "";
		try
		{
			sql = "SELECT REPL_ORDER , LINE_NO__REPL FROM PICK_ORD_DET WHERE PICK_ORDER = ? AND LINE_NO = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, pickOrder);
			pstmt.setInt(2, pickOrderLine);			
			rs = pstmt.executeQuery();
			if( rs.next() )
			{
				tempReplOrder = checkNull(rs.getString("REPL_ORDER"));				
			}			
			if( tempReplOrder.trim().length() > 0 )
			{
				replOrder.put("isRepl", "YES");
				replOrder.put("repl_order", rs.getString("REPL_ORDER"));
				replOrder.put("repl_order_line", rs.getString("LINE_NO__REPL"));
			}
			else
			{
				replOrder.put("isRepl", "NO");
				replOrder.put("repl_order", "");
				replOrder.put("repl_order_line", "" );
			}
			rs.close();rs = null;
			pstmt.close(); pstmt = null;			
			System.out.println(" is replOrder exist ["+replOrder+"]");				
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}		
		return replOrder;
	}
	private HashMap isParentExist(String replOrder,int replOrderLine, Connection conn) throws ITMException
	{
		String sql = "";
		String pickOrder = "";
		String confirmed = "";
		int lineNoPick = 0;
		PreparedStatement pstmt = null, pstmt1 = null, pstmt2 = null;
		ResultSet rs = null, rs1 = null, rs2 = null;
		HashMap parentExist =  new HashMap();
		try
		{
			sql = "SELECT PICK_ORDER, LINE_NO__PICK FROM REPL_ORD_DET WHERE REPL_ORDER = ? AND LINE_NO = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, replOrder);
			pstmt.setInt(2, replOrderLine);
			rs = pstmt.executeQuery();
			if( rs.next() )
			{
				pickOrder = checkNull(rs.getString("PICK_ORDER"));
				lineNoPick = rs.getInt("LINE_NO__PICK");
			}
			
			if( pickOrder.trim().length() > 0)
			{				
				parentExist.put("paren_exist", "YES");
				parentExist.put("pick_order", pickOrder);
				parentExist.put("line_no__pick", lineNoPick);
			}
			else
			{
				parentExist.put("paren_exist", "NO");
				parentExist.put("pick_order", " ");	
				parentExist.put("line_no__pick", 0);
			}
			rs.close();rs = null;
			pstmt.close(); pstmt = null;
				
			/*if( pickOrder.trim().length() > 0 )
			{*/				
				/*parentExist.put("paren_exist", "YES");
				parentExist.put("pick_order", pickOrder);*/
				
				sql = "";
				sql = " SELECT RID.REPL_ORDER, RIH.CONFIRMED, RID.ITEM_CODE, RID.LOT_NO, RID.LOT_SL, RID.LOC_CODE__TO, RID.LOC_CODE " +
						" FROM REPL_ISS_DET RID, REPL_ISS_HDR RIH WHERE RIH.REPL_ORDER = ? " +
						" AND RIH.TRAN_ID = RID.TRAN_ID AND RID.REPL_ORDER = ? AND RID.LINE_NO__ORD = ? ";
				pstmt1 = conn.prepareStatement(sql);
				pstmt1.setString(1, replOrder);
				pstmt1.setString(2, replOrder);
				pstmt1.setInt(3, replOrderLine);
				rs1 = pstmt1.executeQuery();
				if( rs1.next() )
				{
					confirmed = checkNull(rs1.getString("CONFIRMED"));
					System.out.println( replOrder+" is confirmed ["+confirmed+"]");
					if("Y".equalsIgnoreCase(confirmed))
					{
						parentExist.put("confirmed", confirmed);
						parentExist.put("item_code", rs1.getString("ITEM_CODE"));
						parentExist.put("lot_no", rs1.getString("LOT_NO"));
						parentExist.put("lot_sl", rs1.getString("LOT_SL"));
						parentExist.put("loc_code__to", rs1.getString("LOC_CODE__TO"));
						
					}
					else
					{
						parentExist.put("confirmed", "N");
						parentExist.put("item_code", rs1.getString("ITEM_CODE"));
						parentExist.put("lot_no", rs1.getString("LOT_NO"));
						parentExist.put("lot_sl", rs1.getString("LOT_SL"));
						parentExist.put("loc_code__to", rs1.getString("LOC_CODE"));
					}
				}
				else
				{
					//Getting record from repl_order 
					sql = "";
					sql = " SELECT ITEM_CODE, LOC_CODE, LOT_NO, LOT_SL FROM REPL_ORD_DET WHERE REPL_ORDER = ? AND LINE_NO = ?";
					pstmt2 = conn.prepareStatement(sql);
					pstmt2.setString(1, replOrder);
					pstmt2.setInt(2, replOrderLine);
					rs2 = pstmt2.executeQuery();
					if( rs2.next() )
					{
						parentExist.put("item_code", rs2.getString("ITEM_CODE"));
						parentExist.put("lot_no", rs2.getString("LOT_NO"));
						parentExist.put("lot_sl", rs2.getString("LOT_SL"));
						parentExist.put("loc_code__to", rs2.getString("LOC_CODE"));
					}
					parentExist.put("confirmed", "N");
					rs2.close();rs2 = null;
					pstmt2.close();pstmt2 = null;					
					
				}
				rs1.close();rs1 = null;
				pstmt1.close(); pstmt1 = null;
				
			//}
			/*else
			{*/
				
			//}
							
			System.out.println(" parentExist --> "+parentExist);
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if(rs != null)
				{
					rs.close();rs = null;
				}
				if( rs1 != null)
				{
					rs1.close(); rs1 = null;
				}
				if( rs2 != null)
				{
					rs2.close(); rs2 = null;
				}
				if( pstmt != null)
				{
					pstmt.close(); pstmt = null;
				}
				if( pstmt1 != null )
				{
					pstmt1.close(); pstmt1 = null;
				}
				if( pstmt2 != null )
				{
					pstmt2.close(); pstmt2 = null;
				}
			}
			catch(Exception ex)
			{
				ex.printStackTrace();
				throw new ITMException(ex);				
			}
		}
		
		return parentExist;
	}
	// Updating repl order
	private int updateReplOrder(String currentReplOrder,int replOrderLine, String pickTye, String siteCode, String itemCode, String locCode, String lotNo, String lotSl, double DeallocQty, Connection conn, boolean isReplDone, String parentReplOrder, int parentReplOrdLine, String supRerplOrder,int supReplLine) throws ITMException
	{
		String sql = "";
		String dsoInvStat = "";
		String psoInvStat = "";
		String invStat = "";
		double quantity = 0;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		//boolean isDsoloc = false;
		int update = 0;
		try
		{
			System.out.println(" updateReplOrder pickTye ["+pickTye+"] currentReplOrder ["+currentReplOrder+"] isReplDone ["+isReplDone+"] parentReplOrder["+parentReplOrder+"] supRerplOrder ["+supRerplOrder+"] supReplLine ["+supReplLine+"]");
			//sql = "";
			//sql = "UPDATE REPL_ORD_DET SET ACTUAL_QTY = ( ACTUAL_QTY + ?) WHERE REPL_ORDER = ? ";
			/*dsoInvStat  = discommon.getDisparams("999999","DEEP_STORE_INVSTAT",conn).trim();
			psoInvStat  = discommon.getDisparams("999999","RESERVE_LOCATION",conn).trim();
			System.out.println(" dsoInvStat ["+dsoInvStat+"] psoInvStat["+psoInvStat+"]");
			sql = "SELECT INV_STAT FROM LOCATION WHERE LOC_CODE = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, locCode);			
			rs = pstmt.executeQuery();
			if( rs.next())
			{
				if(dsoInvStat.equalsIgnoreCase(rs.getString("INV_STAT").trim()))
				{
					invStat = "DSO";
				}
				else if(psoInvStat.equalsIgnoreCase(rs.getString("INV_STAT").trim()))
				{
					invStat = "PSO";
				}
				else
				{
					invStat = "PND";
				}
			}
			System.out.println(" inv_stat ["+invStat+"]");
			rs.close(); rs = null;
			pstmt.close(); pstmt = null;*/
			if( isReplDone && parentReplOrder.trim().length() == 0)
			{				
				sql = "UPDATE REPL_ORD_DET SET ACTUAL_QTY = ( ACTUAL_QTY + ?) WHERE REPL_ORDER = ? AND LINE_NO = ? AND ACTUAL_QTY IS NOT NULL";
				pstmt = conn.prepareStatement(sql);
				pstmt.setDouble(1, DeallocQty );				
				pstmt.setString(2, currentReplOrder);
				pstmt.setInt(3, replOrderLine);
				//pstmt2.setInt(3, pickLineNo);
				update = pstmt.executeUpdate();
				if( update > 0 )
				{
					System.out.println(currentReplOrder+" repl_order updated successfully ");
				}			
				pstmt.close();pstmt = null;
				
				sql = "UPDATE REPL_ORD_DET SET DEALLOC_QTY = (CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE DEALLOC_QTY END + ?) WHERE REPL_ORDER = ? AND LINE_NO = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setDouble(1, DeallocQty );				
				pstmt.setString(2, currentReplOrder);
				pstmt.setInt(3, replOrderLine);
				//pstmt2.setInt(3, pickLineNo);
				update = pstmt.executeUpdate();
				if( update > 0 )
				{
					System.out.println(currentReplOrder+" repl_order updated successfully ");
				}			
				pstmt.close();pstmt = null;
				
			}
			if( !isReplDone && parentReplOrder.trim().length() == 0)
			{				
				sql = "UPDATE REPL_ORD_DET SET ACTUAL_QTY = ( ACTUAL_QTY + ?) WHERE REPL_ORDER = ? AND LINE_NO = ? AND ACTUAL_QTY IS NOT NULL";
				pstmt = conn.prepareStatement(sql);
				pstmt.setDouble(1, DeallocQty );				
				pstmt.setString(2, currentReplOrder);
				pstmt.setInt(3, replOrderLine);
				//pstmt2.setInt(3, pickLineNo);
				update = pstmt.executeUpdate();
				if( update > 0 )
				{
					System.out.println(currentReplOrder+" repl_order updated successfully ");
				}			
				pstmt.close();pstmt = null;
				
				sql = "UPDATE REPL_ORD_DET SET DEALLOC_QTY = (CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE DEALLOC_QTY END + ?) WHERE REPL_ORDER = ? AND LINE_NO = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setDouble(1, DeallocQty );				
				pstmt.setString(2, currentReplOrder);
				pstmt.setInt(3, replOrderLine);
				//pstmt2.setInt(3, pickLineNo);
				update = pstmt.executeUpdate();
				if( update > 0 )
				{
					System.out.println(currentReplOrder+" repl_order updated successfully ");
				}			
				pstmt.close();pstmt = null;
				
			}	
			else if( isReplDone && parentReplOrder.trim().length() > 0 && supRerplOrder.trim().length() == 0)
			{				
				sql = "UPDATE REPL_ORD_DET SET ACTUAL_QTY = ( ACTUAL_QTY + ?) WHERE REPL_ORDER = ? AND LINE_NO = ? AND ACTUAL_QTY IS NOT NULL";
				pstmt = conn.prepareStatement(sql);
				pstmt.setDouble(1, DeallocQty );				
				pstmt.setString(2, currentReplOrder);
				pstmt.setInt(3, replOrderLine);
				//pstmt2.setInt(3, pickLineNo);
				update = pstmt.executeUpdate();
				if( update > 0 )
				{
					System.out.println(currentReplOrder+" repl_order updated successfully ");
				}			
				pstmt.close();pstmt = null;
				
				sql = "UPDATE REPL_ORD_DET SET DEALLOC_QTY = (CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE DEALLOC_QTY END + ?) WHERE REPL_ORDER = ? AND LINE_NO = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setDouble(1, DeallocQty );				
				pstmt.setString(2, currentReplOrder);
				pstmt.setInt(3, replOrderLine);
				//pstmt2.setInt(3, pickLineNo);
				update = pstmt.executeUpdate();
				if( update > 0 )
				{
					System.out.println(currentReplOrder+" repl_order updated successfully ");
				}			
				pstmt.close();pstmt = null;
				
			}
			else if( !isReplDone && parentReplOrder.trim().length() > 0 && supRerplOrder.trim().length() == 0)
			{
				sql = "UPDATE REPL_ORD_DET SET ACTUAL_QTY = ( ACTUAL_QTY + ?) WHERE REPL_ORDER = ? AND LINE_NO = ? AND ACTUAL_QTY IS NOT NULL ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setDouble(1, DeallocQty );				
				pstmt.setString(2, currentReplOrder);
				pstmt.setInt(3, replOrderLine);
				//pstmt2.setInt(3, pickLineNo);
				update = pstmt.executeUpdate();
				if( update > 0 )
				{
					System.out.println(currentReplOrder+" repl_order updated successfully ");
				}			
				pstmt.close();pstmt = null;
				
				sql = "UPDATE REPL_ORD_DET SET DEALLOC_QTY = (CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE DEALLOC_QTY END + ?) WHERE REPL_ORDER = ? AND LINE_NO = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setDouble(1, DeallocQty );				
				pstmt.setString(2, currentReplOrder);
				pstmt.setInt(3, replOrderLine);
				//pstmt2.setInt(3, pickLineNo);
				update = pstmt.executeUpdate();
				if( update > 0 )
				{
					System.out.println(currentReplOrder+" repl_order updated successfully ");
				}			
				pstmt.close();pstmt = null;
				
				sql = "UPDATE REPL_ORD_DET SET DEALLOC_QTY = (CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE DEALLOC_QTY END + ?) WHERE REPL_ORDER = ? AND LINE_NO = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setDouble(1, DeallocQty );				
				pstmt.setString(2, parentReplOrder);
				pstmt.setInt(3, parentReplOrdLine);
				//pstmt2.setInt(3, pickLineNo);
				update = pstmt.executeUpdate();
				if( update > 0 )
				{
					System.out.println(parentReplOrder+" repl_order OF REPL_ORD_DET updated successfully ");
				}			
				pstmt.close();pstmt = null;
				
				if( !"A".equalsIgnoreCase(pickTye))
				{
					sql = "UPDATE REPL_ORD_DET SET ACTUAL_QTY = ( ACTUAL_QTY + ?) WHERE REPL_ORDER = ? AND LINE_NO = ? AND ACTUAL_QTY IS NOT NULL ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setDouble(1, DeallocQty );				
					pstmt.setString(2, parentReplOrder);
					pstmt.setInt(3, parentReplOrdLine);
					//pstmt2.setInt(3, pickLineNo);
					update = pstmt.executeUpdate();
					if( update > 0 )
					{
						System.out.println(parentReplOrder+" repl_order REPL_ORD_DET updated successfully ");
					}			
					pstmt.close();pstmt = null;
				}
				//changed by sankara on 03/05/14 for update actual_qty in repl ord det start.			
				if( "A".equalsIgnoreCase(pickTye))
				{
					System.out.println("inside active.....");
					sql = "UPDATE REPL_ORD_DET SET ACTUAL_QTY = ( ACTUAL_QTY + ?) WHERE REPL_ORDER = ? AND LINE_NO = ? AND ACTUAL_QTY >= 0  ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setDouble(1, DeallocQty );			
					pstmt.setString(2, parentReplOrder);
					pstmt.setInt(3, parentReplOrdLine);
					update = pstmt.executeUpdate();
					if( update > 0 )
					{
						System.out.println(currentReplOrder+" repl_order of REPL_ISS_DET updated actual_qty successfully ");
					}			
					pstmt.close();pstmt = null;
				}
				//changed by sankara on 03/05/14 for update actual_qty in repl ord det end.	
				sql = "UPDATE REPL_ISS_DET SET DEALLOC_QTY = (CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE DEALLOC_QTY END + ?) WHERE REPL_ORDER = ? AND LINE_NO__ORD = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setDouble(1, DeallocQty );			
				pstmt.setString(2, parentReplOrder);
				pstmt.setInt(3, parentReplOrdLine);
				//pstmt2.setInt(3, pickLineNo);
				update = pstmt.executeUpdate();
				if( update > 0 )
				{
					System.out.println(currentReplOrder+" repl_order of REPL_ISS_DET updated successfully ");
				}			
				pstmt.close();pstmt = null;			
				
			}			
			else if( isReplDone && parentReplOrder.trim().length() > 0 && supRerplOrder.trim().length() == 0)
			{
				sql = " UPDATE REPL_ORD_DET SET ACTUAL_QTY = ( ACTUAL_QTY + ?) WHERE REPL_ORDER = ? AND LINE_NO = ? AND ACTUAL_QTY IS NOT NULL ";
				pstmt = conn.prepareStatement(sql);				
				pstmt.setDouble(1, DeallocQty );			
				pstmt.setString(2, currentReplOrder);
				pstmt.setInt(3, replOrderLine);
				//pstmt2.setInt(3, pickLineNo);
				update = pstmt.executeUpdate();
				if( update > 0 )
				{
					System.out.println(parentReplOrder+" repl_order of REPL_ORD_DET updated successfully ");
				}			
				pstmt.close();pstmt = null;				
				
				sql = "UPDATE REPL_ORD_DET SET DEALLOC_QTY = (CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE DEALLOC_QTY END + ?) WHERE REPL_ORDER = ? AND LINE_NO = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setDouble(1, DeallocQty );				
				pstmt.setString(2, currentReplOrder);
				pstmt.setInt(3, replOrderLine);
				//pstmt2.setInt(3, pickLineNo);
				update = pstmt.executeUpdate();
				if( update > 0 )
				{
					System.out.println(parentReplOrder+" repl_order OF REPL_ORD_DET updated successfully ");
				}			
				pstmt.close();pstmt = null;
				
				/*sql = " UPDATE REPL_ORD_DET SET ACTUAL_QTY = ( ACTUAL_QTY + ?) WHERE REPL_ORDER = ? AND LINE_NO = ? AND ACTUAL_QTY IS NOT NULL ";
				pstmt = conn.prepareStatement(sql);				
				pstmt.setDouble(1, DeallocQty );			
				pstmt.setString(2, parentReplOrder);
				pstmt.setInt(3, parentReplOrdLine);
				//pstmt2.setInt(3, pickLineNo);
				update = pstmt.executeUpdate();
				if( update > 0 )
				{
					System.out.println(parentReplOrder+" repl_order of REPL_ORD_DET updated successfully ");
				}			
				pstmt.close();pstmt = null;
				
				sql = "UPDATE REPL_ISS_DET SET DEALLOC_QTY = (CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE DEALLOC_QTY END + ?) WHERE REPL_ORDER = ? AND LINE_NO__ORD = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setDouble(1, DeallocQty );			
				pstmt.setString(2, parentReplOrder);
				pstmt.setInt(3, parentReplOrdLine);
				//pstmt2.setInt(3, pickLineNo);
				update = pstmt.executeUpdate();
				if( update > 0 )
				{
					System.out.println(currentReplOrder+" repl_order of REPL_ISS_DET updated successfully ");
				}			
				pstmt.close();pstmt = null;	*/			
				
			}
			
			if( !isReplDone && supRerplOrder.trim().length() > 0 )
			{
				
				sql = " UPDATE REPL_ORD_DET SET ACTUAL_QTY = ( ACTUAL_QTY + ?) WHERE REPL_ORDER = ? AND LINE_NO = ? AND ACTUAL_QTY IS NOT NULL ";
				pstmt = conn.prepareStatement(sql);				
				pstmt.setDouble(1, DeallocQty );			
				pstmt.setString(2, currentReplOrder);
				pstmt.setInt(3, replOrderLine);
				//pstmt2.setInt(3, pickLineNo);
				update = pstmt.executeUpdate();
				if( update > 0 )
				{
					System.out.println(parentReplOrder+" repl_order of REPL_ORD_DET updated successfully ");
				}			
				pstmt.close();pstmt = null;				
				
				sql = "UPDATE REPL_ORD_DET SET DEALLOC_QTY = (CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE DEALLOC_QTY END + ?) WHERE REPL_ORDER = ? AND LINE_NO = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setDouble(1, DeallocQty );				
				pstmt.setString(2, currentReplOrder);
				pstmt.setInt(3, replOrderLine);
				//pstmt2.setInt(3, pickLineNo);
				update = pstmt.executeUpdate();
				if( update > 0 )
				{
					System.out.println(parentReplOrder+" repl_order OF REPL_ORD_DET updated successfully ");
				}			
				pstmt.close();pstmt = null;
				
				////////////////for pnd 
				if( !"A".equalsIgnoreCase(pickTye))
				{	
					sql = " UPDATE REPL_ORD_DET SET ACTUAL_QTY = ( ACTUAL_QTY + ?) WHERE REPL_ORDER = ? AND LINE_NO = ? AND ACTUAL_QTY IS NOT NULL ";
					pstmt = conn.prepareStatement(sql);				
					pstmt.setDouble(1, DeallocQty );			
					pstmt.setString(2, parentReplOrder);
					pstmt.setInt(3, parentReplOrdLine);
					//pstmt2.setInt(3, pickLineNo);
					update = pstmt.executeUpdate();
					if( update > 0 )
					{
						System.out.println(parentReplOrder+" repl_order of REPL_ORD_DET updated successfully ");
					}			
					pstmt.close();pstmt = null;
				}
				
				sql = "UPDATE REPL_ORD_DET SET DEALLOC_QTY = (CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE DEALLOC_QTY END + ?) WHERE REPL_ORDER = ? AND LINE_NO = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setDouble(1, DeallocQty );				
				pstmt.setString(2, parentReplOrder);
				pstmt.setInt(3, parentReplOrdLine);
				//pstmt2.setInt(3, pickLineNo);
				update = pstmt.executeUpdate();
				if( update > 0 )
				{
					System.out.println(parentReplOrder+" repl_order OF REPL_ORD_DET updated successfully ");
				}			
				pstmt.close();pstmt = null;
				
				/////for dso repl
				if( !"A".equalsIgnoreCase(pickTye))
				{
					sql = " UPDATE REPL_ORD_DET SET ACTUAL_QTY = ( ACTUAL_QTY + ?) WHERE REPL_ORDER = ? AND LINE_NO = ? AND ACTUAL_QTY IS NOT NULL ";
					pstmt = conn.prepareStatement(sql);				
					pstmt.setDouble(1, DeallocQty );			
					pstmt.setString(2, supRerplOrder);
					pstmt.setInt(3, supReplLine);
					//pstmt2.setInt(3, pickLineNo);
					update = pstmt.executeUpdate();
					if( update > 0 )
					{
						System.out.println(supRerplOrder+" repl_order of REPL_ORD_DET updated successfully ");
					}			
					pstmt.close();pstmt = null;
				}
				
				sql = "UPDATE REPL_ORD_DET SET DEALLOC_QTY = (CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE DEALLOC_QTY END + ?) WHERE REPL_ORDER = ? AND LINE_NO = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setDouble(1, DeallocQty );				
				pstmt.setString(2, supRerplOrder);
				pstmt.setInt(3, supReplLine);
				//pstmt2.setInt(3, pickLineNo);
				update = pstmt.executeUpdate();
				if( update > 0 )
				{
					System.out.println(parentReplOrder+" repl_order OF REPL_ORD_DET updated successfully ");
				}			
				pstmt.close();pstmt = null;
				
				
				/*sql = "UPDATE REPL_ISS_DET SET DEALLOC_QTY = (CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE DEALLOC_QTY END + ?) WHERE REPL_ORDER = ? AND LINE_NO__ORD = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setDouble(1, DeallocQty );			
				pstmt.setString(2, supRerplOrder);
				pstmt.setInt(3, supReplLine);
				//pstmt2.setInt(3, pickLineNo);
				update = pstmt.executeUpdate();
				if( update > 0 )
				{
					System.out.println(supRerplOrder+" repl_order of REPL_ISS_DET updated successfully ");
				}			
				pstmt.close();pstmt = null;	*/
			}
			else if ( isReplDone && supRerplOrder.trim().length() > 0 )
			{
				sql = " UPDATE REPL_ORD_DET SET ACTUAL_QTY = ( ACTUAL_QTY + ?) WHERE REPL_ORDER = ? AND LINE_NO = ? AND ACTUAL_QTY IS NOT NULL ";
				pstmt = conn.prepareStatement(sql);				
				pstmt.setDouble(1, DeallocQty );			
				pstmt.setString(2, currentReplOrder);
				pstmt.setInt(3, replOrderLine);
				//pstmt2.setInt(3, pickLineNo);
				update = pstmt.executeUpdate();
				if( update > 0 )
				{
					System.out.println(parentReplOrder+" repl_order of REPL_ORD_DET updated successfully ");
				}			
				pstmt.close();pstmt = null;				
				
				sql = "UPDATE REPL_ORD_DET SET DEALLOC_QTY = (CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE DEALLOC_QTY END + ?) WHERE REPL_ORDER = ? AND LINE_NO = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setDouble(1, DeallocQty );				
				pstmt.setString(2, currentReplOrder);
				pstmt.setInt(3, replOrderLine);
				//pstmt2.setInt(3, pickLineNo);
				update = pstmt.executeUpdate();
				if( update > 0 )
				{
					System.out.println(parentReplOrder+" repl_order OF REPL_ORD_DET updated successfully ");
				}			
				pstmt.close();pstmt = null;
				
				////////////////for pnd 
				sql = " UPDATE REPL_ORD_DET SET ACTUAL_QTY = ( ACTUAL_QTY + ?) WHERE REPL_ORDER = ? AND LINE_NO = ? AND ACTUAL_QTY IS NOT NULL ";
				pstmt = conn.prepareStatement(sql);				
				pstmt.setDouble(1, DeallocQty );			
				pstmt.setString(2, parentReplOrder);
				pstmt.setInt(3, parentReplOrdLine);
				//pstmt2.setInt(3, pickLineNo);
				update = pstmt.executeUpdate();
				if( update > 0 )
				{
					System.out.println(parentReplOrder+" repl_order of REPL_ORD_DET updated successfully ");
				}			
				pstmt.close();pstmt = null;				
				
				sql = "UPDATE REPL_ORD_DET SET DEALLOC_QTY = (CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE DEALLOC_QTY END + ?) WHERE REPL_ORDER = ? AND LINE_NO = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setDouble(1, DeallocQty );				
				pstmt.setString(2, parentReplOrder);
				pstmt.setInt(3, parentReplOrdLine);
				//pstmt2.setInt(3, pickLineNo);
				update = pstmt.executeUpdate();
				if( update > 0 )
				{
					System.out.println(parentReplOrder+" repl_order OF REPL_ORD_DET updated successfully ");
				}			
				pstmt.close();pstmt = null;
				
				
				/*sql = " UPDATE REPL_ORD_DET SET ACTUAL_QTY = ( ACTUAL_QTY + ?) WHERE REPL_ORDER = ? AND LINE_NO = ? AND ACTUAL_QTY IS NOT NULL ";
				pstmt = conn.prepareStatement(sql);				
				pstmt.setDouble(1, DeallocQty );			
				pstmt.setString(2, supRerplOrder);
				pstmt.setInt(3, supReplLine);
				//pstmt2.setInt(3, pickLineNo);
				update = pstmt.executeUpdate();
				if( update > 0 )
				{
					System.out.println(supRerplOrder+" repl_order of REPL_ORD_DET updated successfully ");
				}			
				pstmt.close();pstmt = null;
				
				sql = "UPDATE REPL_ISS_DET SET DEALLOC_QTY = (CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE DEALLOC_QTY END + ?) WHERE REPL_ORDER = ? AND LINE_NO__ORD = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setDouble(1, DeallocQty );			
				pstmt.setString(2, supRerplOrder);
				pstmt.setInt(3, supReplLine);
				//pstmt2.setInt(3, pickLineNo);
				update = pstmt.executeUpdate();
				if( update > 0 )
				{
					System.out.println(supRerplOrder+" repl_order of REPL_ISS_DET updated successfully ");
				}			
				pstmt.close();pstmt = null;*/	
			}
			
			////////////////////// replenishment deallocating
			/*if( currentReplOrder.trim().length() > 0 && "PSO".equalsIgnoreCase(invStat) && parentReplOrder.trim().length() == 0)
			{
				boolean recordFound = false;
				sql = " SELECT (QUANTITY - CASE WHEN ACTUAL_QTY IS NULL THEN 0 ELSE ACTUAL_QTY END ) AS QUANTITY " +
						  " FROM REPL_ORD_DET WHERE REPL_ORDER = ? AND LINE_NO = ? AND ACTUAL_QTY IS NOT NULL ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, currentReplOrder);
				pstmt.setInt(2, replOrderLine);
				rs = pstmt.executeQuery();
				if( rs.next())
				{
					recordFound = true;
					quantity = rs.getDouble("QUANTITY");
				}
				else
				{
					recordFound = false;
				}
				rs.close(); rs = null;
				pstmt.close(); pstmt = null;
				
				if( quantity == 0 && recordFound)
				{
					sql = "UPDATE REPL_ORD_DET SET CANCEL_MODE = 'Y' WHERE REPL_ORDER = ? AND LINE_NO = ? ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, currentReplOrder);
					pstmt.setInt(2, replOrderLine);
					if ( pstmt.executeUpdate() > 0 )
					{
						System.out.println(" REPL_ORD_DET updated succesfully ");
					}
					pstmt.close(); pstmt = null;
					
					sql = "UPDATE WAVE_TASK_DET SET STATUS = 'Y' WHERE REF_ID = ?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, currentReplOrder);				
					if ( pstmt.executeUpdate() > 0 )
					{
						System.out.println(" WAVE_TASK_DET updated succesfully ");
					}
					pstmt.close(); pstmt = null;
				}
			}
			// this below task is usefull for stock to dock case start
			if( parentReplOrder.trim().length() > 0 )
			{				
				// -- PSO			
				
				boolean recordFound = false;
				System.out.println(" inside repl deallocation pickTye["+pickTye+"]");
				if( "S".equalsIgnoreCase(pickTye))
				{
					sql = " SELECT (QUANTITY - CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE DEALLOC_QTY END ) AS QUANTITY " +
							  " FROM REPL_ORD_DET WHERE REPL_ORDER = ? AND LINE_NO = ?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, parentReplOrder);
					pstmt.setInt(2, parentReplOrdLine);
					rs = pstmt.executeQuery();
					if( rs.next())
					{
						recordFound = true;
						quantity = rs.getDouble("QUANTITY");
					}
					else
					{
						recordFound = false;
					}
					rs.close(); rs = null;
					pstmt.close(); pstmt = null;
					
					if( quantity == 0 && recordFound)
					{						
						sql = "UPDATE REPL_ORD_DET SET CANCEL_MODE = 'Y' WHERE REPL_ORDER = ? AND LINE_NO = ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, parentReplOrder);
						pstmt.setInt(2, parentReplOrdLine);
						if ( pstmt.executeUpdate() > 0 )
						{
							System.out.println(" REPL_ORD_DET updated succesfully ");
						}
						pstmt.close(); pstmt = null;
						
						sql = "UPDATE WAVE_TASK_DET SET STATUS = 'Y' WHERE REF_ID = ?";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, parentReplOrder);				
						if ( pstmt.executeUpdate() > 0 )
						{
							System.out.println(" WAVE_TASK_DET updated succesfully ");
						}
						pstmt.close(); pstmt = null;	
						
					}
				}
				else
				{
					////////////// FOR PSO TASK CANCELLING IF NO DEPENDANCY START
					sql = " SELECT (QUANTITY - CASE WHEN ACTUAL_QTY IS NULL THEN 0 ELSE ACTUAL_QTY END ) AS QUANTITY " +
							  " FROM REPL_ORD_DET WHERE REPL_ORDER = ? AND LINE_NO = ? AND ACTUAL_QTY IS NOT NULL ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, parentReplOrder);
					pstmt.setInt(2, parentReplOrdLine);
					rs = pstmt.executeQuery();
					if( rs.next())
					{
						recordFound = true;
						quantity = rs.getDouble("QUANTITY");
					}
					else
					{
						recordFound = false;
					}
					rs.close(); rs = null;
					pstmt.close(); pstmt = null;
					
					if( quantity == 0 && recordFound)
					{
						if( "DSO".equalsIgnoreCase(invStat) )
						{
							sql = "UPDATE REPL_ORD_DET SET CANCEL_MODE = 'Y' WHERE REPL_ORDER = ? AND LINE_NO = ? ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, parentReplOrder);
							pstmt.setInt(2, parentReplOrdLine);
							if ( pstmt.executeUpdate() > 0 )
							{
								System.out.println(" REPL_ORD_DET updated succesfully ");
							}
							pstmt.close(); pstmt = null;
							
							sql = "UPDATE WAVE_TASK_DET SET STATUS = 'Y' WHERE REF_ID = ?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, parentReplOrder);				
							if ( pstmt.executeUpdate() > 0 )
							{
								System.out.println(" WAVE_TASK_DET updated succesfully ");
							}
							pstmt.close(); pstmt = null;
							
							sql = "UPDATE REPL_ORD_DET SET CANCEL_MODE = 'Y' WHERE REPL_ORDER = ? AND LINE_NO = ? ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, currentReplOrder);
							pstmt.setInt(2, replOrderLine);
							if ( pstmt.executeUpdate() > 0 )
							{
								System.out.println(" REPL_ORD_DET updated succesfully ");
							}
							pstmt.close(); pstmt = null;
							
							sql = "UPDATE WAVE_TASK_DET SET STATUS = 'Y' WHERE REF_ID = ?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, currentReplOrder);				
							if ( pstmt.executeUpdate() > 0 )
							{
								System.out.println(" WAVE_TASK_DET updated succesfully ");
							}
							pstmt.close(); pstmt = null;
						}
						else if( "PSO".equalsIgnoreCase(invStat) )
						{
							sql = "UPDATE REPL_ORD_DET SET CANCEL_MODE = 'Y' WHERE REPL_ORDER = ? AND LINE_NO = ? ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, parentReplOrder);
							pstmt.setInt(2, parentReplOrdLine);
							if ( pstmt.executeUpdate() > 0 )
							{
								System.out.println(" REPL_ORD_DET updated succesfully ");
							}
							pstmt.close(); pstmt = null;
							
							sql = "UPDATE WAVE_TASK_DET SET STATUS = 'Y' WHERE REF_ID = ?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, parentReplOrder);				
							if ( pstmt.executeUpdate() > 0 )
							{
								System.out.println(" WAVE_TASK_DET updated succesfully ");
							}
							pstmt.close(); pstmt = null;
						}						
					}
					////////////// FOR PSO TASK CANCELLING IF NO DEPENDANCY END
				}
			}
			if( supRerplOrder.trim().length() > 0 )
			{
				boolean recordFound = false;
				System.out.println(" inside DSO repl deallocation pickTye["+pickTye+"]");
				if( "S".equalsIgnoreCase(pickTye))
				{			
					sql = " SELECT (QUANTITY - CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE DEALLOC_QTY END ) AS QUANTITY " +
							  " FROM REPL_ORD_DET WHERE REPL_ORDER = ? AND LINE_NO = ?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, supRerplOrder);
					pstmt.setInt(2, supReplLine);
					rs = pstmt.executeQuery();
					if( rs.next())
					{
						recordFound = true;
						quantity = rs.getDouble("QUANTITY");
					}
					rs.close(); rs = null;
					pstmt.close(); pstmt = null;
					
					if( quantity == 0 && recordFound )
					{
						sql = "UPDATE REPL_ORD_DET SET CANCEL_MODE = 'Y' WHERE REPL_ORDER = ? AND LINE_NO = ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, supRerplOrder);
						pstmt.setInt(2, supReplLine);
						if ( pstmt.executeUpdate() > 0 )
						{
							System.out.println(" REPL_ORD_DET updated succesfully ");
						}
						pstmt.close(); pstmt = null;
						
						sql = "UPDATE WAVE_TASK_DET SET STATUS = 'Y' WHERE REF_ID = ?";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, supRerplOrder);				
						if ( pstmt.executeUpdate() > 0 )
						{
							System.out.println(" WAVE_TASK_DET updated succesfully ");
						}
						pstmt.close(); pstmt = null;						
					}
				}
				else
				{
					////////////// FOR DSO TASK CANCELLING IF NO DEPENDANCY START
					sql = " SELECT (QUANTITY - CASE WHEN ACTUAL_QTY IS NULL THEN 0 ELSE ACTUAL_QTY END ) AS QUANTITY " +
							  " FROM REPL_ORD_DET WHERE REPL_ORDER = ? AND LINE_NO = ? AND ACTUAL_QTY IS NOT NULL ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, supRerplOrder);
					pstmt.setInt(2, supReplLine);
					rs = pstmt.executeQuery();
					if( rs.next())
					{
						recordFound = true;
						quantity = rs.getDouble("QUANTITY");
					}
					else
					{
						recordFound = false;
					}
					rs.close(); rs = null;
					pstmt.close(); pstmt = null;
					
					System.out.println("*********quantity ["+quantity+"] recordFound ["+recordFound+"]");
					if( quantity == 0 && recordFound)
					{
						System.out.println(" PND deallocating ");
						sql = "UPDATE REPL_ORD_DET SET CANCEL_MODE = 'Y' WHERE REPL_ORDER = ? AND LINE_NO = ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, parentReplOrder);
						pstmt.setInt(2, parentReplOrdLine);
						if ( pstmt.executeUpdate() > 0 )
						{
							System.out.println(parentReplOrder+" REPL_ORD_DET updated succesfully ");
						}
						pstmt.close(); pstmt = null;
						
						sql = "UPDATE WAVE_TASK_DET SET STATUS = 'Y' WHERE REF_ID = ?";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, parentReplOrder);				
						if ( pstmt.executeUpdate() > 0 )
						{
							System.out.println(" WAVE_TASK_DET updated succesfully ");
						}
						pstmt.close(); pstmt = null;
						
						///////////////////////////////////////////////
						System.out.println(" DSO repl deallocating ");
						sql = "UPDATE REPL_ORD_DET SET CANCEL_MODE = 'Y' WHERE REPL_ORDER = ? AND LINE_NO = ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, supRerplOrder);
						pstmt.setInt(2, supReplLine);
						if ( pstmt.executeUpdate() > 0 )
						{
							System.out.println(supRerplOrder+" REPL_ORD_DET updated succesfully ");
						}
						pstmt.close(); pstmt = null;
						
						
						sql = "UPDATE WAVE_TASK_DET SET STATUS = 'Y' WHERE REF_ID = ?";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, supRerplOrder);				
						if ( pstmt.executeUpdate() > 0 )
						{
							System.out.println(" WAVE_TASK_DET updated succesfully ");
						}
						pstmt.close(); pstmt = null;
						
					}
					////////////// FOR DSO TASK CANCELLING IF NO DEPENDANCY END
				}
			}*/
			// this below task is usefull for stock to dock case end
					
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if( pstmt != null)
				{
					pstmt.close(); pstmt = null;
				}
			}
			catch(Exception ex)
			{
				ex.printStackTrace();
				throw new ITMException(ex);
			}
		}
		
		return update;
	}
	private int confReplOrder(String currentReplOrder,int replOrderLine, String pickTye, String siteCode, String itemCode, String locCode, String lotNo, String lotSl, double DeallocQty, Connection conn, boolean isReplDone, String parentReplOrder, int parentReplOrdLine, String supRerplOrder,int supReplLine) throws ITMException
	{
		String sql = "";
		String dsoInvStat = "";
		String psoInvStat = "";
		String invStat = "";
		double quantity = 0;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		//boolean isDsoloc = false;
		int update = 0;
		try
		{
			System.out.println("confReplOrder pickTye ["+pickTye+"] currentReplOrder ["+currentReplOrder+"] isReplDone ["+isReplDone+"] parentReplOrder["+parentReplOrder+"] supRerplOrder ["+supRerplOrder+"] supReplLine ["+supReplLine+"]");
						
			//sql = "";
			//sql = "UPDATE REPL_ORD_DET SET ACTUAL_QTY = ( ACTUAL_QTY + ?) WHERE REPL_ORDER = ? ";
			dsoInvStat  = discommon.getDisparams("999999","DEEP_STORE_INVSTAT",conn).trim();
			psoInvStat  = discommon.getDisparams("999999","RESERVE_LOCATION",conn).trim();
			System.out.println(" dsoInvStat ["+dsoInvStat+"] psoInvStat["+psoInvStat+"]");
			sql = "SELECT INV_STAT FROM LOCATION WHERE LOC_CODE = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, locCode);			
			rs = pstmt.executeQuery();
			if( rs.next())
			{
				if(dsoInvStat.equalsIgnoreCase(rs.getString("INV_STAT").trim()))
				{
					invStat = "DSO";
				}
				else if(psoInvStat.equalsIgnoreCase(rs.getString("INV_STAT").trim()))
				{
					invStat = "PSO";
				}
				else
				{
					invStat = "PND";
				}
			}
			System.out.println(" inv_stat ["+invStat+"]");
			rs.close(); rs = null;
			pstmt.close(); pstmt = null;			
						
			////////////////////// replenishment deallocating
			if( currentReplOrder.trim().length() > 0 && "PSO".equalsIgnoreCase(invStat) && parentReplOrder.trim().length() == 0)
			{
				boolean recordFound = false;
				sql = " SELECT (QUANTITY - CASE WHEN ACTUAL_QTY IS NULL THEN 0 ELSE ACTUAL_QTY END ) AS QUANTITY " +
						  " FROM REPL_ORD_DET WHERE REPL_ORDER = ? AND LINE_NO = ? AND ACTUAL_QTY IS NOT NULL ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, currentReplOrder);
				pstmt.setInt(2, replOrderLine);
				rs = pstmt.executeQuery();
				if( rs.next())
				{
					recordFound = true;
					quantity = rs.getDouble("QUANTITY");
				}
				else
				{
					recordFound = false;
				}
				rs.close(); rs = null;
				pstmt.close(); pstmt = null;
				
				if( quantity == 0 && recordFound)
				{
					sql = "UPDATE REPL_ORD_DET SET CANCEL_MODE = 'Y',ACTUAL_QTY = 0 WHERE REPL_ORDER = ? AND LINE_NO = ? ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, currentReplOrder);
					pstmt.setInt(2, replOrderLine);
					if ( pstmt.executeUpdate() > 0 )
					{
						System.out.println(" REPL_ORD_DET updated succesfully ");
					}
					pstmt.close(); pstmt = null;
					
					sql = "UPDATE WAVE_TASK_DET SET STATUS = 'Y' WHERE REF_ID = ?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, currentReplOrder);				
					if ( pstmt.executeUpdate() > 0 )
					{
						System.out.println(" WAVE_TASK_DET updated succesfully ");
						updateWaveStatusTasks(currentReplOrder, "R", conn);
					}
					pstmt.close(); pstmt = null;
				}
			}
			// this below task is usefull for stock to dock case start
			
			if( parentReplOrder.trim().length() > 0 )
			{				
				// -- PSO			
				
				boolean recordFound = false;
				System.out.println(" inside repl deallocation pickTye["+pickTye+"]");
				
				////////////// FOR PSO TASK CANCELLING IF NO DEPENDANCY START
				sql = " SELECT (QUANTITY - CASE WHEN ACTUAL_QTY IS NULL THEN 0 ELSE ACTUAL_QTY END ) AS QUANTITY " +
						  " FROM REPL_ORD_DET WHERE REPL_ORDER = ? AND LINE_NO = ? AND ACTUAL_QTY IS NOT NULL ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, parentReplOrder);
				pstmt.setInt(2, parentReplOrdLine);
				rs = pstmt.executeQuery();
				if( rs.next())
				{
					recordFound = true;
					quantity = rs.getDouble("QUANTITY");
				}
				else
				{
					recordFound = false;
				}
				rs.close(); rs = null;
				pstmt.close(); pstmt = null;
				
				if( quantity == 0 && recordFound)
				{
					if( "DSO".equalsIgnoreCase(invStat) )
					{
						boolean isParentUpd = false;
						sql = "UPDATE REPL_ORD_DET SET CANCEL_MODE = 'Y',ACTUAL_QTY = 0 WHERE REPL_ORDER = ? AND LINE_NO = ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, parentReplOrder);
						pstmt.setInt(2, parentReplOrdLine);
						if ( pstmt.executeUpdate() > 0 )
						{
							System.out.println(" REPL_ORD_DET updated succesfully ");
							//changed by sankara on 26/06/14 update cancel mode in repl_ord_det start.
							sql = "UPDATE REPL_ORD_DET SET CANCEL_MODE = 'Y' WHERE PICK_ORDER = ? AND LINE_NO__PICK = ? ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, parentReplOrder);
							pstmt.setInt(2, parentReplOrdLine);
							int replpickcount = pstmt.executeUpdate();
							if( replpickcount > 0 )
							{
								System.out.println("replpickcount["+replpickcount+"]");
							}
							//changed by sankara on 26/06/14 update cancel mode in repl_ord_det end.
						}
						pstmt.close(); pstmt = null;
						//changed by sankara on 20/06/14 update status in wave_task_det start.
						int replcount = 0;
						sql = " SELECT COUNT(*) AS COUNT FROM REPL_ORD_DET RO WHERE RO.REPL_ORDER = ? AND ( RO.CANCEL_MODE <> ? OR RO.CANCEL_MODE IS NULL ) " ; 
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, parentReplOrder);
						pstmt.setString(2, "Y");
						rs = pstmt.executeQuery();
						if( rs.next())
						{
							replcount = rs.getInt("COUNT");
							System.out.println("repl count["+replcount+"]");
						}
						rs.close(); rs = null;						
						pstmt.close(); pstmt = null;
						
						if(replcount == 0)
						{
							sql = "UPDATE WAVE_TASK_DET SET STATUS = 'Y' WHERE REF_ID = ?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, parentReplOrder);				
							if ( pstmt.executeUpdate() > 0 )
							{
								isParentUpd = true;
								System.out.println(" WAVE_TASK_DET updated succesfully ");
								updateWaveStatusTasks(parentReplOrder, "R", conn);
							}
							pstmt.close(); pstmt = null;
						}
						//changed by sankara on 20/06/14 update status in wave_task_det end.
						//changed by sankara on 03/05/14 update cancel_mode for parent repl order only
						//sql = "UPDATE REPL_ORD_DET SET CANCEL_MODE = 'Y' WHERE REPL_ORDER = ? AND LINE_NO = ? ";
						//changed by sankara on 26/06/14 update status in wave_task_det start.
						//sql = "UPDATE REPL_ORD_DET SET CANCEL_MODE = 'Y' WHERE REPL_ORDER = ? AND LINE_NO = ? AND ACTUAL_QTY IS NOT NULL ";
						if(isParentUpd)
						{
							sql = " UPDATE REPL_ORD_DET SET CANCEL_MODE = 'Y' WHERE REPL_ORDER = ? ";
						}
						else
						{
							sql = "UPDATE REPL_ORD_DET SET CANCEL_MODE = 'Y' WHERE REPL_ORDER = ? AND LINE_NO = ? AND ACTUAL_QTY IS NOT NULL ";
						}
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, currentReplOrder);
						//pstmt.setInt(2, replOrderLine);
						if(!isParentUpd)
						{
							pstmt.setInt(2, replOrderLine);
						}
						if ( pstmt.executeUpdate() > 0 )
						{
							System.out.println(" REPL_ORD_DET updated succesfully ");
						}
						pstmt.close(); pstmt = null;
						//changed by sankara on 20/06/14 update status in wave_task_det start
						sql = " SELECT COUNT(*) AS COUNT FROM REPL_ORD_DET RO WHERE RO.REPL_ORDER = ? AND ( RO.CANCEL_MODE <> ? OR RO.CANCEL_MODE IS NULL ) " ; 
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, currentReplOrder);
						pstmt.setString(2, "Y");
						rs = pstmt.executeQuery();
						if( rs.next())
						{
							replcount = rs.getInt("COUNT");
							System.out.println("repl count["+replcount+"]");
						}
						rs.close(); rs = null;						
						pstmt.close(); pstmt = null;
						
						if(replcount == 0)
						{
							sql = "UPDATE WAVE_TASK_DET SET STATUS = 'Y' WHERE REF_ID = ?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, currentReplOrder);				
							if ( pstmt.executeUpdate() > 0 )
							{
								System.out.println(" WAVE_TASK_DET updated succesfully ");
								updateWaveStatusTasks(currentReplOrder, "R", conn);
							}
							pstmt.close(); pstmt = null;
						}
						//changed by sankara on 20/06/14 update status in wave_task_det end.
					}
					else if( "PSO".equalsIgnoreCase(invStat) )
					{
						sql = "UPDATE REPL_ORD_DET SET CANCEL_MODE = 'Y',ACTUAL_QTY = 0 WHERE REPL_ORDER = ? AND LINE_NO = ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, parentReplOrder);
						pstmt.setInt(2, parentReplOrdLine);
						if ( pstmt.executeUpdate() > 0 )
						{
							System.out.println(" REPL_ORD_DET updated succesfully ");
						}
						pstmt.close(); pstmt = null;
						
						sql = "UPDATE WAVE_TASK_DET SET STATUS = 'Y' WHERE REF_ID = ?";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, parentReplOrder);				
						if ( pstmt.executeUpdate() > 0 )
						{
							System.out.println(" WAVE_TASK_DET updated succesfully ");
							updateWaveStatusTasks(parentReplOrder, "R", conn);
						}
						pstmt.close(); pstmt = null;
					}						
				}
				////////////// FOR PSO TASK CANCELLING IF NO DEPENDANCY END
				
			}
			if( supRerplOrder.trim().length() > 0 )
			{
				boolean recordFound = false;
				System.out.println(" inside DSO repl deallocation pickTye["+pickTye+"]");
				
				////////////// FOR DSO TASK CANCELLING IF NO DEPENDANCY START
				sql = " SELECT (QUANTITY - CASE WHEN ACTUAL_QTY IS NULL THEN 0 ELSE ACTUAL_QTY END ) AS QUANTITY " +
						  " FROM REPL_ORD_DET WHERE REPL_ORDER = ? AND LINE_NO = ? AND ACTUAL_QTY IS NOT NULL ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, supRerplOrder);
				pstmt.setInt(2, supReplLine);
				rs = pstmt.executeQuery();
				if( rs.next())
				{
					recordFound = true;
					quantity = rs.getDouble("QUANTITY");
				}
				else
				{
					recordFound = false;
				}
				rs.close(); rs = null;
				pstmt.close(); pstmt = null;
				
				System.out.println("*********quantity ["+quantity+"] recordFound ["+recordFound+"]");
				if( quantity == 0 && recordFound)
				{
					System.out.println(" PND deallocating ");
					sql = "UPDATE REPL_ORD_DET SET CANCEL_MODE = 'Y' WHERE REPL_ORDER = ? AND LINE_NO = ? ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, parentReplOrder);
					pstmt.setInt(2, parentReplOrdLine);
					if ( pstmt.executeUpdate() > 0 )
					{
						System.out.println(parentReplOrder+" REPL_ORD_DET updated succesfully ");
					}
					pstmt.close(); pstmt = null;
					
					sql = "UPDATE WAVE_TASK_DET SET STATUS = 'Y' WHERE REF_ID = ?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, parentReplOrder);				
					if ( pstmt.executeUpdate() > 0 )
					{
						System.out.println(" WAVE_TASK_DET updated succesfully ");
						updateWaveStatusTasks(parentReplOrder, "R", conn);
					}
					pstmt.close(); pstmt = null;
					
					///////////////////////////////////////////////
					System.out.println(" DSO repl deallocating ");
					sql = "UPDATE REPL_ORD_DET SET CANCEL_MODE = 'Y',ACTUAL_QTY = 0 WHERE REPL_ORDER = ? AND LINE_NO = ? ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, supRerplOrder);
					pstmt.setInt(2, supReplLine);
					if ( pstmt.executeUpdate() > 0 )
					{
						System.out.println(supRerplOrder+" REPL_ORD_DET updated succesfully ");
					}
					pstmt.close(); pstmt = null;
					
					
					sql = "UPDATE WAVE_TASK_DET SET STATUS = 'Y' WHERE REF_ID = ?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, supRerplOrder);				
					if ( pstmt.executeUpdate() > 0 )
					{
						System.out.println(" WAVE_TASK_DET updated succesfully ");
						updateWaveStatusTasks(supRerplOrder, "R", conn);
					}
					pstmt.close(); pstmt = null;
					
				}
				////////////// FOR DSO TASK CANCELLING IF NO DEPENDANCY END				
			}
			// this below task is usefull for stock to dock case end
					
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if( pstmt != null)
				{
					pstmt.close(); pstmt = null;
				}
			}
			catch(Exception ex)
			{
				ex.printStackTrace();
				throw new ITMException(ex);
			}
		}
		
		return update;
	}
	private int confStockToDockReplOrder(String currentReplOrder,int replOrderLine, String pickTye, String siteCode, String itemCode, String locCode, String lotNo, String lotSl, double DeallocQty, Connection conn, boolean isReplDone, String parentReplOrder, int parentReplOrdLine, String supRerplOrder,int supReplLine) throws ITMException
	{
		String sql = "";
		String dsoInvStat = "";
		String psoInvStat = "";
		String invStat = "";
		double quantity = 0;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		//boolean isDsoloc = false;
		int update = 0;
		try
		{
			System.out.println("confStockToDockReplOrder pickTye ["+pickTye+"] currentReplOrder ["+currentReplOrder+"] isReplDone ["+isReplDone+"] parentReplOrder["+parentReplOrder+"] supRerplOrder ["+supRerplOrder+"] supReplLine ["+supReplLine+"]");
			
			if("S".equalsIgnoreCase(pickTye))
			{
				if( parentReplOrder.trim().length() > 0 )
				{
					sql = " UPDATE REPL_ORD_DET SET ACTUAL_QTY = ( ACTUAL_QTY + ?) WHERE REPL_ORDER = ? AND LINE_NO = ? AND ACTUAL_QTY IS NOT NULL ";
					pstmt = conn.prepareStatement(sql);				
					pstmt.setDouble(1, DeallocQty );			
					pstmt.setString(2, parentReplOrder);
					pstmt.setInt(3, parentReplOrdLine);
					//pstmt2.setInt(3, pickLineNo);
					update = pstmt.executeUpdate();
					if( update > 0 )
					{
						System.out.println(parentReplOrder+" repl_order of REPL_ORD_DET updated successfully ");
					}			
					pstmt.close();pstmt = null;
					
					sql = "UPDATE REPL_ORD_DET SET DEALLOC_QTY = (CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE DEALLOC_QTY END + ?) WHERE REPL_ORDER = ? AND LINE_NO = ?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setDouble(1, DeallocQty );				
					pstmt.setString(2, parentReplOrder);
					pstmt.setInt(3, parentReplOrdLine);
					//pstmt2.setInt(3, pickLineNo);
					update = pstmt.executeUpdate();
					if( update > 0 )
					{
						System.out.println(parentReplOrder+" repl_order OF REPL_ORD_DET updated successfully ");
					}			
					pstmt.close();pstmt = null;
					
					sql = " UPDATE REPL_ORD_DET SET ACTUAL_QTY = ( ACTUAL_QTY + ?) WHERE REPL_ORDER = ? AND LINE_NO = ? AND ACTUAL_QTY IS NOT NULL ";
					pstmt = conn.prepareStatement(sql);				
					pstmt.setDouble(1, DeallocQty );			
					pstmt.setString(2, currentReplOrder);
					pstmt.setInt(3, replOrderLine);
					//pstmt2.setInt(3, pickLineNo);
					update = pstmt.executeUpdate();
					if( update > 0 )
					{
						System.out.println(parentReplOrder+" repl_order of REPL_ORD_DET updated successfully ");
					}			
					pstmt.close();pstmt = null;
					
					sql = "UPDATE REPL_ORD_DET SET DEALLOC_QTY = (CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE DEALLOC_QTY END + ?) WHERE REPL_ORDER = ? AND LINE_NO = ?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setDouble(1, DeallocQty );				
					pstmt.setString(2, currentReplOrder);
					pstmt.setInt(3, replOrderLine);
					//pstmt2.setInt(3, pickLineNo);
					update = pstmt.executeUpdate();
					if( update > 0 )
					{
						System.out.println(parentReplOrder+" repl_order OF REPL_ORD_DET updated successfully ");
					}			
					pstmt.close();pstmt = null;			
					
				}
				if( parentReplOrder.trim().length() == 0 )
				{				
					
					sql = " UPDATE REPL_ORD_DET SET ACTUAL_QTY = ( ACTUAL_QTY + ?) WHERE REPL_ORDER = ? AND LINE_NO = ? AND ACTUAL_QTY IS NOT NULL ";
					pstmt = conn.prepareStatement(sql);				
					pstmt.setDouble(1, DeallocQty );			
					pstmt.setString(2, currentReplOrder);
					pstmt.setInt(3, replOrderLine);
					//pstmt2.setInt(3, pickLineNo);
					update = pstmt.executeUpdate();
					if( update > 0 )
					{
						System.out.println(parentReplOrder+" repl_order of REPL_ORD_DET updated successfully ");
					}			
					pstmt.close();pstmt = null;
					
					sql = "UPDATE REPL_ORD_DET SET DEALLOC_QTY = (CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE DEALLOC_QTY END + ?) WHERE REPL_ORDER = ? AND LINE_NO = ?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setDouble(1, DeallocQty );				
					pstmt.setString(2, currentReplOrder);
					pstmt.setInt(3, replOrderLine);
					//pstmt2.setInt(3, pickLineNo);
					update = pstmt.executeUpdate();
					if( update > 0 )
					{
						System.out.println(parentReplOrder+" repl_order OF REPL_ORD_DET updated successfully ");
					}			
					pstmt.close();pstmt = null;			
					
				}
			}
			
			// this below task is usefull for stock to dock case start
			if( parentReplOrder.trim().length() == 0 && "S".equalsIgnoreCase(pickTye))
			{
				boolean recordFound = false;
				sql = " SELECT (QUANTITY - CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE DEALLOC_QTY END ) AS QUANTITY " +
						  " FROM REPL_ORD_DET WHERE REPL_ORDER = ? AND LINE_NO = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, currentReplOrder);
				pstmt.setInt(2, replOrderLine);
				rs = pstmt.executeQuery();
				if( rs.next())
				{
					recordFound = true;
					quantity = rs.getDouble("QUANTITY");
				}
				else
				{
					recordFound = false;
				}
				rs.close(); rs = null;
				pstmt.close(); pstmt = null;
				
				if( quantity == 0 && recordFound)
				{
					sql = "UPDATE REPL_ORD_DET SET CANCEL_MODE = 'Y' WHERE REPL_ORDER = ? AND LINE_NO = ? ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, currentReplOrder);
					pstmt.setInt(2, replOrderLine);
					if ( pstmt.executeUpdate() > 0 )
					{
						System.out.println(" REPL_ORD_DET updated succesfully ");
					}
					pstmt.close(); pstmt = null;
					
					sql = "UPDATE WAVE_TASK_DET SET STATUS = 'Y' WHERE REF_ID = ?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, currentReplOrder);				
					if ( pstmt.executeUpdate() > 0 )
					{
						System.out.println(" WAVE_TASK_DET updated succesfully ");
						updateWaveStatusTasks(currentReplOrder, "R", conn);
					}
					pstmt.close(); pstmt = null;
				}
			}
			if( parentReplOrder.trim().length() > 0 )
			{				
				// -- PSO			
				
				boolean recordFound = false;
				System.out.println(" inside repl deallocation pickTye["+pickTye+"]");
				if( "S".equalsIgnoreCase(pickTye))
				{
					sql = " SELECT (QUANTITY - CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE DEALLOC_QTY END ) AS QUANTITY " +
							  " FROM REPL_ORD_DET WHERE REPL_ORDER = ? AND LINE_NO = ?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, parentReplOrder);
					pstmt.setInt(2, parentReplOrdLine);
					rs = pstmt.executeQuery();
					if( rs.next())
					{
						recordFound = true;
						quantity = rs.getDouble("QUANTITY");
					}
					else
					{
						recordFound = false;
					}
					rs.close(); rs = null;
					pstmt.close(); pstmt = null;
					
					if( quantity == 0 && recordFound)
					{						
						sql = "UPDATE REPL_ORD_DET SET CANCEL_MODE = 'Y' WHERE REPL_ORDER = ? AND LINE_NO = ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, parentReplOrder);
						pstmt.setInt(2, parentReplOrdLine);
						if ( pstmt.executeUpdate() > 0 )
						{
							System.out.println(" REPL_ORD_DET updated succesfully ");
						}
						pstmt.close(); pstmt = null;
						
						sql = "UPDATE WAVE_TASK_DET SET STATUS = 'Y' WHERE REF_ID = ?";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, parentReplOrder);				
						if ( pstmt.executeUpdate() > 0 )
						{
							System.out.println(" WAVE_TASK_DET updated succesfully ");
							updateWaveStatusTasks(parentReplOrder, "R", conn);
						}
						pstmt.close(); pstmt = null;	
						
						sql = "UPDATE REPL_ORD_DET SET CANCEL_MODE = 'Y' WHERE REPL_ORDER = ? AND LINE_NO = ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, currentReplOrder);
						pstmt.setInt(2, replOrderLine);
						if ( pstmt.executeUpdate() > 0 )
						{
							System.out.println(" REPL_ORD_DET updated succesfully ");
						}
						pstmt.close(); pstmt = null;
						
						sql = "UPDATE WAVE_TASK_DET SET STATUS = 'Y' WHERE REF_ID = ?";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, currentReplOrder);				
						if ( pstmt.executeUpdate() > 0 )
						{
							updateWaveStatusTasks(currentReplOrder, "R", conn);
							System.out.println(" WAVE_TASK_DET updated succesfully ");
						}
						pstmt.close(); pstmt = null;
						
					}
				}
				
			}
			/*if( supRerplOrder.trim().length() > 0 )
			{
				boolean recordFound = false;
				System.out.println(" inside DSO repl deallocation pickTye["+pickTye+"]");
				if( "S".equalsIgnoreCase(pickTye))
				{			
					sql = " SELECT (QUANTITY - CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE DEALLOC_QTY END ) AS QUANTITY " +
							  " FROM REPL_ORD_DET WHERE REPL_ORDER = ? AND LINE_NO = ?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, supRerplOrder);
					pstmt.setInt(2, supReplLine);
					rs = pstmt.executeQuery();
					if( rs.next())
					{
						recordFound = true;
						quantity = rs.getDouble("QUANTITY");
					}
					rs.close(); rs = null;
					pstmt.close(); pstmt = null;
					
					if( quantity == 0 && recordFound )
					{
						sql = "UPDATE REPL_ORD_DET SET CANCEL_MODE = 'Y' WHERE REPL_ORDER = ? AND LINE_NO = ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, supRerplOrder);
						pstmt.setInt(2, supReplLine);
						if ( pstmt.executeUpdate() > 0 )
						{
							System.out.println(" REPL_ORD_DET updated succesfully ");
						}
						pstmt.close(); pstmt = null;
						
						sql = "UPDATE WAVE_TASK_DET SET STATUS = 'Y' WHERE REF_ID = ?";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, supRerplOrder);				
						if ( pstmt.executeUpdate() > 0 )
						{
							System.out.println(" WAVE_TASK_DET updated succesfully ");
						}
						pstmt.close(); pstmt = null;						
					}
				}
				else
				{
					////////////// FOR DSO TASK CANCELLING IF NO DEPENDANCY START
					sql = " SELECT (QUANTITY - CASE WHEN ACTUAL_QTY IS NULL THEN 0 ELSE ACTUAL_QTY END ) AS QUANTITY " +
							  " FROM REPL_ORD_DET WHERE REPL_ORDER = ? AND LINE_NO = ? AND ACTUAL_QTY IS NOT NULL ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, supRerplOrder);
					pstmt.setInt(2, supReplLine);
					rs = pstmt.executeQuery();
					if( rs.next())
					{
						recordFound = true;
						quantity = rs.getDouble("QUANTITY");
					}
					else
					{
						recordFound = false;
					}
					rs.close(); rs = null;
					pstmt.close(); pstmt = null;
					
					System.out.println("*********quantity ["+quantity+"] recordFound ["+recordFound+"]");
					if( quantity == 0 && recordFound)
					{
						System.out.println(" PND deallocating ");
						sql = "UPDATE REPL_ORD_DET SET CANCEL_MODE = 'Y' WHERE REPL_ORDER = ? AND LINE_NO = ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, parentReplOrder);
						pstmt.setInt(2, parentReplOrdLine);
						if ( pstmt.executeUpdate() > 0 )
						{
							System.out.println(parentReplOrder+" REPL_ORD_DET updated succesfully ");
						}
						pstmt.close(); pstmt = null;
						
						sql = "UPDATE WAVE_TASK_DET SET STATUS = 'Y' WHERE REF_ID = ?";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, parentReplOrder);				
						if ( pstmt.executeUpdate() > 0 )
						{
							System.out.println(" WAVE_TASK_DET updated succesfully ");
						}
						pstmt.close(); pstmt = null;
						
						///////////////////////////////////////////////
						System.out.println(" DSO repl deallocating ");
						sql = "UPDATE REPL_ORD_DET SET CANCEL_MODE = 'Y' WHERE REPL_ORDER = ? AND LINE_NO = ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, supRerplOrder);
						pstmt.setInt(2, supReplLine);
						if ( pstmt.executeUpdate() > 0 )
						{
							System.out.println(supRerplOrder+" REPL_ORD_DET updated succesfully ");
						}
						pstmt.close(); pstmt = null;
						
						
						sql = "UPDATE WAVE_TASK_DET SET STATUS = 'Y' WHERE REF_ID = ?";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, supRerplOrder);				
						if ( pstmt.executeUpdate() > 0 )
						{
							System.out.println(" WAVE_TASK_DET updated succesfully ");
						}
						pstmt.close(); pstmt = null;
						
					}
					////////////// FOR DSO TASK CANCELLING IF NO DEPENDANCY END
				}
			}*/
			// this below task is usefull for stock to dock case end
					
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if( pstmt != null)
				{
					pstmt.close(); pstmt = null;
				}
			}
			catch(Exception ex)
			{
				ex.printStackTrace();
				throw new ITMException(ex);
			}
		}
		
		return update;
	}
	private int updatePickOrder(String pickOrder, int pickLineNo, double DeallocQty, Connection conn, boolean isPickIssueDone,String pickType) throws ITMException
	{
		String sql = "";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int update = 0;
		boolean isAllDeallocated = false;
		try
		{
			System.out.println(" inside updatePickOrder *****");
			if( !isPickIssueDone )
			{
				sql =   " UPDATE PICK_ORD_DET SET DEALLOC_QTY = ( CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE  DEALLOC_QTY END + ?)" +
						" WHERE PICK_ORDER = ? AND LINE_NO = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setDouble(1, DeallocQty );
				pstmt.setString(2, pickOrder);
				pstmt.setInt(3, pickLineNo);
				update = pstmt.executeUpdate();
				if( update > 0 )
				{
					System.out.println(pickOrder+" pick_order OF PICK_ORD_DET updated successfully ");
				}			
				pstmt.close();pstmt = null;
				
				sql = "UPDATE PICK_ISS_DET SET DEALLOC_QTY = ( CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE  DEALLOC_QTY END + ?) WHERE PICK_ORDER = ? AND LINE_NO__ORD = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setDouble(1, DeallocQty );
				pstmt.setString(2, pickOrder);
				pstmt.setInt(3, pickLineNo);
				update = pstmt.executeUpdate();
				if( update > 0 )
				{
					System.out.println(pickOrder+" pick_order OF PICK_ISS_DET updated successfully ");
				}			
				pstmt.close();pstmt = null;	
				
				//Setting status of pick_ord_det if all case deallocated
				sql = "SELECT QUANTITY, (CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE DEALLOC_QTY END) AS DEALLOC_QTY " +
						" FROM PICK_ORD_DET WHERE PICK_ORDER = ? AND LINE_NO = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, pickOrder);
				pstmt.setInt(2, pickLineNo);
				rs = pstmt.executeQuery();
				if( rs.next())
				{
					if( rs.getDouble("QUANTITY") == rs.getDouble("DEALLOC_QTY") )
					{
						isAllDeallocated = true;
					}
				}
				rs.close(); rs = null; 
				pstmt.close(); pstmt = null;
				System.out.println(" isAllDeallocated ["+isAllDeallocated+"]");
				if(isAllDeallocated)
				{
					sql = " UPDATE PICK_ORD_DET SET STATUS = 'C' WHERE PICK_ORDER = ? AND LINE_NO = ?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, pickOrder);
					pstmt.setInt(2, pickLineNo);
					update = pstmt.executeUpdate();
					if( update > 0 )
					{
						System.out.println(pickOrder+" status OF PICK_ORD_DET updated successfully ");
					}			
					pstmt.close();pstmt = null;
				}
			}
			else
			{
				sql = "UPDATE PICK_ORD_DET SET DEALLOC_QTY = ( CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE  DEALLOC_QTY END + ?) WHERE PICK_ORDER = ? AND LINE_NO = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setDouble(1, DeallocQty );
				pstmt.setString(2, pickOrder);
				pstmt.setInt(3, pickLineNo);
				update = pstmt.executeUpdate();
				if( update > 0 )
				{
					System.out.println(pickOrder+" pick_order OF PICK_ORD_DET updated successfully ");
				}			
				pstmt.close();pstmt = null;
				
				//sql = "UPDATE PICK_ISS_DET SET NO_ART = ( NO_ART - 1), QUANTITY = (QUANTITY - ?) WHERE PICK_ORDER = ? AND LINE_NO__ORD = ? ";
				sql = "UPDATE PICK_ISS_DET SET DEALLOC_QTY = ( CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE  DEALLOC_QTY END + ?) WHERE PICK_ORDER = ? AND LINE_NO__ORD = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setDouble(1, DeallocQty );
				pstmt.setString(2, pickOrder);
				pstmt.setInt(3, pickLineNo);
				update = pstmt.executeUpdate();
				if( update > 0 )
				{
					System.out.println(pickOrder+" pick_order OF PICK_ISS_DET updated successfully ");
				}			
				pstmt.close();pstmt = null;	
			}		
					
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if( pstmt != null)
				{
					pstmt.close(); pstmt = null;
				}
			}
			catch(Exception ex)
			{
				ex.printStackTrace();
				throw new ITMException(ex);
			}
		}
		
		return update;
	}
	
	private int updateWaveTaskDet(String refId, String task, Connection conn , String pickType, String empCode) throws ITMException
	{
		String sql = "";
		String pickOrder = "";//Added by sumit on 01/APR/13
		String confirmed = "";
		int pickLine = 0;//Added by sumit on 01/APR/13
		double quantity = 0.0;
		PreparedStatement pstmt = null;
		PreparedStatement pstmt1 = null;//Added by sumit on 01/APR/13
		ResultSet rs = null;
		ResultSet rs1 = null;//Added by sumit on 01/APR/13
		int update = 0;
		java.sql.Timestamp currDate = new java.sql.Timestamp( System.currentTimeMillis() );
		
		try
		{
			System.out.println(" refId --- ["+refId+"] pick_type ["+pickType+"]");
			if( "PICK".equalsIgnoreCase(task))
			{
				sql = "SELECT SUM(QUANTITY - CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE DEALLOC_QTY END) AS QUANTITY FROM PICK_ORD_DET WHERE PICK_ORDER = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, refId);
				rs = pstmt.executeQuery();
				if( rs.next())
				{
					quantity = rs.getDouble("QUANTITY");
				}
				rs.close(); rs = null;
				pstmt.close(); pstmt = null;
				System.out.println(" PICK quantity -- ["+quantity+"]");
				
				if( quantity == 0 )
				{
					
					sql = "UPDATE WAVE_TASK_DET SET STATUS = 'Y' WHERE REF_ID = ? AND STATUS = 'N'";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, refId);
					update = pstmt.executeUpdate();
					pstmt.close(); pstmt = null;
					if(update > 0 )
					{
						System.out.println(refId+ " WAVE_TASK_DET is updated susessfully ");
						
						updateWaveStatusTasks(refId, "P", conn);
					}
					
					if("M".equalsIgnoreCase(pickType))
					{
						sql = "UPDATE PACK_HDR SET CONFIRMED = 'Y', CONF_DATE = ?, EMP_CODE__APRV = ? WHERE PICK_ORDER = ? AND CONFIRMED = 'N'";
						pstmt = conn.prepareStatement(sql);
						pstmt.setTimestamp(1, currDate);
						pstmt.setString(2, empCode);
						pstmt.setString(3, refId);
						if( pstmt.executeUpdate() > 0 )
						{
							System.out.println(" PACK_HDR confirmed sussccessfully ");
						}
						pstmt.close(); pstmt = null;
						
						sql = "UPDATE WAVE_TASK_DET SET STATUS = 'Y' WHERE REF_ID IN (SELECT TRAN_ID FROM PACK_HDR WHERE PICK_ORDER = ? ) AND STATUS = 'N'";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, refId);
						update = pstmt.executeUpdate();
						pstmt.close(); pstmt = null;
						if(update > 0 )
						{
							System.out.println(refId+ " WAVE_TASK_DET is updated susessfully ");
						}
					}
				}
				
				//changed by sumit on 01/apr/13 to update status of wave_task_det if one line scanned and another line deallocated start.
				if("C".equalsIgnoreCase(pickType))
				{
					sql = "SELECT PICK_ORDER, LINE_NO FROM PICK_ORD_DET WHERE PICK_ORDER = ? " +
							" AND (QUANTITY - CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE DEALLOC_QTY END ) > 0 ";
					pstmt = conn.prepareStatement(sql);
					
					sql = " SELECT CONFIRMED  FROM PICK_ISS_DET PID, PICK_ISS_HDR PIH, PICK_ORD_DET POD WHERE PID.PICK_ORDER = ? " +
							  " AND PID.LINE_NO__ORD = ? AND PID.PICK_ORDER = POD.PICK_ORDER " +
						    //Changed By Pragyan 04-JUN-14 To get the confirm flag in proper manner
						   //" AND PID.LINE_NO__ORD = POD.LINE_NO AND PID.PICK_ORDER = PIH.PICK_ORDER ";
							 " AND PIH.TRAN_ID = PID.TRAN_ID AND PID.LINE_NO__ORD = POD.LINE_NO AND PID.PICK_ORDER = PIH.PICK_ORDER ";
					pstmt1 = conn.prepareStatement(sql);
					
					pstmt.setString(1, refId);
					rs = pstmt.executeQuery();
					while(rs.next())
					{
						pickOrder = rs.getString("PICK_ORDER");
						pickLine = rs.getInt("LINE_NO");
						System.out.println(" pick_order = ["+pickOrder+"] pickLine ["+pickLine+"]");
						
						pstmt1.setString(1,pickOrder);
						pstmt1.setInt(2, pickLine);
						rs1 = pstmt1.executeQuery();
						if( rs1.next())
						{
							//changed by sankara on 12/06/14 checked null for confirmed.
							//confirmed = rs1.getString("CONFIRMED");
							confirmed = checkNull(rs1.getString("CONFIRMED"));
							if( "N".equalsIgnoreCase(confirmed) || confirmed.trim().length() == 0 )
							{
								break;
							}
						}
						//chnaged by sankara on 16/06/14 update status in wave task det start.
						else
						{
							confirmed = "N";	
							System.out.println("inside else confimed::::"+confirmed);
							break;						
						}
						//chnaged by sankara on 16/06/14 update status in wave task det end.
						rs1.close(); rs1 = null;
						pstmt1.clearParameters();
					}					
					rs.close(); rs = null;
					pstmt.close(); pstmt = null;
					if(pstmt1 != null)
					{
						pstmt1.close(); pstmt1 = null;
					}
					if( "Y".equalsIgnoreCase(confirmed))
					{
						sql = "UPDATE WAVE_TASK_DET SET STATUS = 'Y' WHERE REF_ID = ? AND STATUS = 'N'";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, refId);
						update = pstmt.executeUpdate();
						pstmt.close(); pstmt = null;
						if(update > 0 )
						{
							updateWaveStatusTasks(refId, "P", conn);
							System.out.println(refId+ " WAVE_TASK_DET is updated susessfully ");
						}
						
					}
				}
				//changed by sumit on 01/apr/13 to update status of wave_task_det if one line scanned and another line deallocated end.
				
				
			}
			else if( "REPL".equalsIgnoreCase(task))
			{				
				sql = "SELECT SUM(QUANTITY - CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE DEALLOC_QTY END) AS QUANTITY FROM REPL_ORD_DET WHERE REPL_ORDER = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, refId);
				rs = pstmt.executeQuery();
				if( rs.next())
				{
					quantity = rs.getDouble("QUANTITY");
				}
				rs.close(); rs = null;
				pstmt.close(); pstmt = null;
				System.out.println(" REPL quantity -- ["+quantity+"]");
				
				if( quantity == 0 )
				{
					sql = "UPDATE WAVE_TASK_DET SET STATUS = 'Y' WHERE REF_ID = ? AND STATUS = 'N'";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, refId);
					update = pstmt.executeUpdate();			
					
					if(update > 0)
					{
						updateWaveStatusTasks(refId, "R", conn);
					}
				}
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if(rs != null)
				{
					rs.close(); rs = null;
				}
				if(pstmt != null )
				{
					pstmt.close(); pstmt = null;
				}
				if(pstmt1 != null)
				{
					pstmt1.close(); pstmt1 = null;
				}
			}
			catch(Exception ex)
			{
				ex.printStackTrace();
				throw new ITMException(ex);
			}
		}
		return update;
	}
	
	//Changed by sumit on 13/03/12 to reterive 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) {
			// TODO: handle exception
	
			throw e;
		}
		finally
		{
			if(pstmt != null)
			{
				pstmt.close();
				pstmt = null;
		
			}
			if(rs != null)
			{
				rs.close();
				rs = null;
		
			}
		}
		
		return dataVolumeMap;
	}
	//Changed by sumit on 13/03/12 to reterive shipper size end
	//Changed by sumit on 14/03/12 to findding type of pick start
	private String checkForReplOrPick(String refId, Connection conn )throws ITMException
	{
		String task = "";
		String sql = "";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try
		{
			sql = " SELECT PICK_ORDER FROM PICK_ORD_HDR WHERE PICK_ORDER = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, refId);			
			rs = pstmt.executeQuery();
			if( rs.next())
			{
				task = "PICK";
			}
			else
			{
				if( pstmt != null )
				{
					pstmt.close(); pstmt = null;
				}
				if( rs != null)
				{
					rs.close(); rs =null;
				}
				sql = " SELECT REPL_ORDER FROM REPL_ORD_HDR WHERE REPL_ORDER = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, refId);
				rs = pstmt.executeQuery();
				if( rs.next())
				{
					task = "REPL";
				}
				rs.close(); rs = null;
				pstmt.close(); pstmt = null;
			}
			if( rs != null)
			{
				rs.close(); rs = null;
			}
			if( pstmt != null)
			{
				pstmt.close(); pstmt = null;
			}
			
		}
		catch(Exception 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 ex)
			{
				ex.printStackTrace();
				throw new ITMException(ex);
			}
		}
		return task;
	}
	
	private String updateReplOrderForActive(String pickType, String replOrder,int replLine, String empCode, Connection conn, String parentReplOrder, int parentReplOrdLine, String supRerplOrder,int supReplLine) throws ITMException
	{
		String sql = "";
		double quantity = 0.0;
		double realQty = 0.0;
		int replcount = 0;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		java.sql.Timestamp currDate = new java.sql.Timestamp( System.currentTimeMillis() );
		try
		{ 
			System.out.println(" current repl ["+replOrder+"] currreplLine ["+replLine+"] parentReplOrder ["+parentReplOrder+"] parentReplOrdLine ["+parentReplOrdLine+"] supRerplOrder ["+supRerplOrder+"] supReplLine ["+supReplLine+"]");
			if("A".equalsIgnoreCase(pickType))
			{
				sql = " SELECT QUANTITY AS REL_QTY, (ROD.QUANTITY - CASE WHEN ROD.ACTUAL_QTY IS NULL THEN 0 ELSE ROD.ACTUAL_QTY END ) AS QUANTITY " +
					  " FROM REPL_ORD_DET ROD, REPL_ORD_HDR ROH WHERE ROD.REPL_ORDER = ? AND ROD.LINE_NO = ? " +
					  //Changed by Rohan on 27-04-13 for adding order type p and Q	
					  //" AND ROH.REPL_ORDER = ROD.REPL_ORDER AND ROH.ORDER_TYPE = 'Q' ";
					  //Changed by Sankara on 20/06/14 considered all order type
					  //" AND ROH.REPL_ORDER = ROD.REPL_ORDER AND ROH.ORDER_TYPE IN ('P','Q') ";
					    " AND ROH.REPL_ORDER = ROD.REPL_ORDER AND ROH.ORDER_TYPE IN ('E','Q','T','P','I') ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, replOrder);
				pstmt.setInt(2, replLine);
				rs = pstmt.executeQuery();
				if( rs.next())
				{
					quantity = rs.getDouble("QUANTITY");
					realQty = rs.getDouble("REL_QTY");
				}
				rs.close(); rs = null;						
				pstmt.close(); pstmt = null;
				
				if( quantity == 0)
				{
					sql = " UPDATE REPL_ORD_DET SET CANCEL_MODE = 'Y', ACTUAL_QTY = 0 WHERE REPL_ORDER = ? AND LINE_NO = ?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, replOrder);
					pstmt.setInt(2, replLine);
					if( pstmt.executeUpdate() > 0 )
					{
						System.out.println(" REPL_ORD_DET updated succesfully");
					}
					pstmt.close(); pstmt = null;
					//changed by sankara on 21/06/14 not update confirmed in replisshdr start.
					/*sql = " UPDATE REPL_ISS_HDR SET CONFIRMED ='Y', CONF_DATE = ? , EMP_CODE__APRV = ? WHERE REPL_ORDER = ? AND CONFIRMED = 'N'";
					pstmt = conn.prepareStatement(sql);
					pstmt.setTimestamp(1, currDate);
					pstmt.setString(2, empCode);
					pstmt.setString(3, replOrder);
					if( pstmt.executeUpdate() > 0 )
					{
						System.out.println(" confirmed sussccessfully ");
					}
					pstmt.close(); pstmt = null;*/
					//changed by sankara on 21/06/14 not update confirmed in replisshdr end.
					//changed by sankara on 16/06/14 for wave_task_det status update from active repl start.
					sql = " SELECT COUNT(*) AS COUNT FROM REPL_ORD_DET RO WHERE RO.REPL_ORDER = ? AND ( RO.CANCEL_MODE <> ? OR RO.CANCEL_MODE IS NULL ) " ; 
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, replOrder);
					pstmt.setString(2, "Y");
					rs = pstmt.executeQuery();
					if( rs.next())
					{
						replcount = rs.getInt("COUNT");
						System.out.println("repl count["+replcount+"]");
					}
					rs.close(); rs = null;						
					pstmt.close(); pstmt = null;
					
					if(replcount == 0)
					{
						//changed by sankara on 16/06/14 for wave_task_det status update from active repl end.
						sql = " UPDATE WAVE_TASK_DET SET STATUS ='Y' WHERE REF_ID = ? AND STATUS = 'N'";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, replOrder);
						if ( pstmt.executeUpdate() > 0)
						{
							System.out.println(" wave_task_det updated succesfully");
						}
						pstmt.close(); pstmt = null;
					} // 16/06/14 end.
					
					if(parentReplOrder.trim().length() > 0)
					{
						sql = " UPDATE REPL_ORD_DET SET ACTUAL_QTY = ( ACTUAL_QTY + ? ) WHERE REPL_ORDER = ? AND LINE_NO = ? AND ACTUAL_QTY IS NOT NULL ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setDouble(1, realQty);
						pstmt.setString(2, parentReplOrder);
						pstmt.setInt(3, parentReplOrdLine);
						if( pstmt.executeUpdate() > 0 )
						{
							System.out.println(" REPL_ORD_DET updated succesfully");
						}
						pstmt.close(); pstmt = null;
					}
					if(supRerplOrder.trim().length() > 0)
					{
						sql = " UPDATE REPL_ORD_DET SET ACTUAL_QTY = ( ACTUAL_QTY + ? ) WHERE REPL_ORDER = ? AND LINE_NO = ? AND ACTUAL_QTY IS NOT NULL ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setDouble(1, realQty);
						pstmt.setString(2, supRerplOrder);
						pstmt.setInt(3, supReplLine);
						if( pstmt.executeUpdate() > 0 )
						{
							System.out.println(" REPL_ORD_DET updated succesfully");
						}
						pstmt.close(); pstmt = null;
					}
				}
				
				sql = " SELECT SUM(ROD.QUANTITY - CASE WHEN ROD.ACTUAL_QTY IS NULL THEN 0 ELSE ROD.ACTUAL_QTY END ) AS QUANTITY " +
					  " FROM REPL_ORD_DET ROD, REPL_ORD_HDR ROH WHERE ROD.REPL_ORDER = ? AND ROH.REPL_ORDER = ROD.REPL_ORDER AND ROH.ORDER_TYPE = 'Q' ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, replOrder);
				rs = pstmt.executeQuery();
				if( rs.next() )
				{
					quantity = rs.getDouble("QUANTITY");
					
				}				
				rs.close(); rs = null;						
				pstmt.close(); pstmt = null;	
				
				if(quantity == 0)
				{
					//changed by sankara on 20/06/14 for wave_task_det status update from active repl start.
					sql = " SELECT COUNT(*) AS COUNT FROM REPL_ORD_DET RO WHERE RO.REPL_ORDER = ? AND ( RO.CANCEL_MODE <> ? OR RO.CANCEL_MODE IS NULL ) " ; 
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, replOrder);
					pstmt.setString(2, "Y");
					rs = pstmt.executeQuery();
					if( rs.next())
					{
						replcount = rs.getInt("COUNT");
						System.out.println("repl count["+replcount+"]");
					}
					rs.close(); rs = null;						
					pstmt.close(); pstmt = null;
					
					if(replcount == 0)
					{
						sql = " UPDATE WAVE_TASK_DET SET STATUS ='Y' WHERE REF_ID = ? AND STATUS = 'N'";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, replOrder);
						if ( pstmt.executeUpdate() > 0)
						{
							System.out.println(" wave_task_det updated succesfully");
						}
						pstmt.close(); pstmt = null;
					}
					//changed by sankara on 20/06/14 for wave_task_det status update from active repl ned.
				}
			}
		}
		catch(Exception 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 ex)
			{
				ex.printStackTrace();
				throw new ITMException(ex);
			}
		}
		return null;
	}
	
	private String deallocatedRepl(String refId, int pickLineNo,String pickType,String siteCode, String itemCode, String lotNo, String lotSl, double DeallocQty,String saleOrder, String saleOrderLine, String expLev,String custCode,String xtraParams,String waveId,String cartonNo, Connection conn) throws ITMException
	{
		String sql = "";
		String confirmed ="";
		//String pickType = "";
		String locCode ="";
		String locCodeTo = "";
		String replOrder = "";
		String currentRepl = "";
		int currReplLineNo = 0;
		String errString = "";
		String replPickOrder = "";
		//String lotNo = "";
		// lotSl = "";
		double quantity = 0.0;
		int replOrderLine = 0;
		int replPickLine = 0;
		boolean isIssueDone = false;
		PreparedStatement pstmt = null, pstmt2 = null;
		ResultSet rs = null; // rs2 = null;
		//HashMap replIss = new HashMap();
		//HashMap stockDetail = new HashMap();
		try
		{
			sql = " SELECT RIH.CONFIRMED, RID.LOC_CODE__TO  FROM REPL_ISS_HDR RIH, REPL_ISS_DET RID WHERE RIH.REPL_ORDER = ? AND RID.LINE_NO__ORD = ? " +
					" AND RIH.REPL_ORDER = RID.REPL_ORDER ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, refId);
			pstmt.setInt(2, pickLineNo);
			rs = pstmt.executeQuery();
			confirmed = "";
			if( rs.next())
			{			
				confirmed = rs.getString("CONFIRMED");
				System.out.println(" confirmed["+confirmed+"]");
				if( "Y".equalsIgnoreCase(confirmed))
				{
					locCode = rs.getString("LOC_CODE__TO");
					isIssueDone = true;
					//return locCode;
				}				
			}
			rs.close(); rs = null;
			pstmt.close(); pstmt = null;		
			
			if( !isIssueDone )
			{
				sql = "SELECT PICK_ORDER, LINE_NO__PICK FROM REPL_ORD_DET WHERE REPL_ORDER = ? AND LINE_NO = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, refId);
				pstmt.setInt(2, pickLineNo);
				rs = pstmt.executeQuery();
				if( rs.next() )
				{
					replPickOrder = checkNull(rs.getString("PICK_ORDER"));
					replPickLine = rs.getInt("LINE_NO__PICK");
				}
				rs.close(); rs = null;
				pstmt.close(); pstmt = null;
				System.out.println(" parent repl_order ["+replPickOrder+"] replPickLine ["+replPickLine+"]");
				
				if(replPickOrder.trim().length() > 0 )
				{
					//replOrder = replIss.get("repl_order").toString();
					//System.out.println(" 88 replOrder ["+replOrder+"]"); line_no__pick
					HashMap replParent = new HashMap();
					replParent = isParentExist(replPickOrder,replPickLine, conn);
					
					replOrder = replParent.get("pick_order").toString();
					replOrderLine = Integer.parseInt(replParent.get("line_no__pick").toString());					
					confirmed = replParent.get("confirmed").toString();//confirmed	
					
					System.out.println("  confirmed ["+confirmed+"] paren_exist ["+replParent.get("paren_exist").toString()+"] repl_order ["+replOrder+"] replOrderLine ["+replOrderLine+"]");
					if("Y".equalsIgnoreCase(replParent.get("confirmed").toString()))
					{
						locCode = replParent.get("loc_code__to").toString();
						lotNo = replParent.get("lot_no").toString();
						lotSl = replParent.get("lot_sl").toString();
						//System.out.println(" locCode ["+locCode+"] lotNo ["+lotNo+"] lotSl ["+lotSl+"]");						
												
						//updateReplOrder(refId, pickLineNo,pickType, siteCode, itemCode, locCodeTo, lotNo, lotSl, DeallocQty, conn, true, "", 0,"",0);
						confStockToDockReplOrder(refId, pickLineNo,pickType, siteCode, itemCode, locCodeTo, lotNo, lotSl, DeallocQty, conn, true, "", 0,"",0);
						
					}
					else if ("N".equalsIgnoreCase(confirmed) /*&& "NO".equalsIgnoreCase(replParent.get("paren_exist").toString())*/)        //parentExist.put("paren_exist", "NO");
					{
						locCode = replParent.get("loc_code__to").toString();
						lotNo = replParent.get("lot_no").toString();
						lotSl = replParent.get("lot_sl").toString();
						System.out.println(" locCode ["+locCode+"] lotNo ["+lotNo+"] lotSl ["+lotSl+"]");
						
						//updateReplOrder(replOrder, siteCode, itemCode, locCodeTo, lotNo, lotSl, DeallocQty, conn);	
						//updatePickOrder(pickOrder, pickLineNo, DeallocQty, conn);
						
						//updateReplOrder(refId, pickLineNo,pickType, siteCode, itemCode, locCodeTo, lotNo, lotSl, DeallocQty, conn, false, replPickOrder, replPickLine,"",0);
						confStockToDockReplOrder(refId, pickLineNo,pickType, siteCode, itemCode, locCodeTo, lotNo, lotSl, DeallocQty, conn, false, replPickOrder, replPickLine,"",0);
					}
					/*else if ( "N".equalsIgnoreCase(confirmed) && "YES".equalsIgnoreCase(replParent.get("paren_exist").toString()))
					{
						System.out.println(" repl order going to search it's ["+replOrder+"] parent ");
						currentRepl = replOrder;
						currReplLineNo = replOrderLine;
						replOrder = "";
						replOrder = replParent.get("pick_order").toString();
						replOrderLine = Integer.parseInt(replParent.get("line_no__pick").toString());
						System.out.println(" new repl order going to serch it's parent ["+replOrder+"] replOrderLine ["+replOrderLine+"]");
						
						replParent.clear();
						//replParent = isParentExistAndConfirmed(replOrder,replOrderLine, conn);
						replParent = isParentExist(replOrder,replOrderLine, conn);
						
						confirmed = "";
						confirmed = replParent.get("confirmed").toString();
						System.out.println(" *-* confirmed ["+confirmed+"]");
						if("Y".equalsIgnoreCase(confirmed))
						{
							locCode = replParent.get("loc_code__to").toString();
							lotNo = replParent.get("lot_no").toString();
							lotSl = replParent.get("lot_sl").toString();
							System.out.println(" locCode ["+locCode+"] lotNo ["+lotNo+"] lotSl ["+lotSl+"]");
							
							//updatePickOrder(pickOrder, pickLineNo, DeallocQty, conn);
							
							updateReplOrder(currentRepl, currReplLineNo, siteCode, itemCode, locCodeTo, lotNo, lotSl, DeallocQty, conn, true, replOrder, replOrderLine);
						}
						else if ("N".equalsIgnoreCase(confirmed) && "NO".equalsIgnoreCase(replParent.get("paren_exist").toString()))        //parentExist.put("paren_exist", "NO");
						{
							locCode = replParent.get("loc_code__to").toString();
							lotNo = replParent.get("lot_no").toString();
							lotSl = replParent.get("lot_sl").toString();
							System.out.println(" locCode ["+locCode+"] lotNo ["+lotNo+"] lotSl ["+lotSl+"]");
							
							//updateReplOrder(replOrder, siteCode, itemCode, locCodeTo, lotNo, lotSl, DeallocQty, conn);
							//updatePickOrder(pickOrder, pickLineNo, DeallocQty, conn);
							
							updateReplOrder(currentRepl, currReplLineNo, siteCode, itemCode, locCodeTo, lotNo, lotSl, DeallocQty, conn, true, replOrder, replOrderLine);
						}
					}	*/	
				}
				else
				{					
					//updateReplOrder(refId, pickLineNo,pickType, siteCode, itemCode, locCodeTo, lotNo, lotSl, DeallocQty, conn, true, "", 0,"",0);
					confStockToDockReplOrder(refId, pickLineNo,pickType, siteCode, itemCode, locCodeTo, lotNo, lotSl, DeallocQty, conn, true, "", 0,"",0);
				}
				
			}	
			else
			{
				sql = "UPDATE REPL_ORD_DET SET DEALLOC_QTY = (CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE DEALLOC_QTY END + ?) WHERE REPL_ORDER = ? AND LINE_NO = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setDouble(1, DeallocQty );				
				pstmt.setString(2, refId);
				pstmt.setInt(3, pickLineNo);
				//pstmt2.setInt(3, pickLineNo);				
				if( pstmt.executeUpdate() > 0 )
				{
					System.out.println(refId+" repl_order OF REPL_ORD_DET updated successfully ");
				}			
				pstmt.close();pstmt = null;
				
				sql = "UPDATE REPL_ISS_DET SET DEALLOC_QTY = (CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE DEALLOC_QTY END + ?) WHERE REPL_ORDER = ? AND LINE_NO__ORD = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setDouble(1, DeallocQty );				
				pstmt.setString(2, refId);
				pstmt.setInt(3, pickLineNo);
				//pstmt2.setInt(3, pickLineNo);
				
				if( pstmt.executeUpdate() > 0 )
				{
					System.out.println(refId+" repl_order OF REPL_ISS_DET updated successfully ");
				}			
				pstmt.close();pstmt = null;
				//updateReplOrder(refId, pickLineNo,pickType, siteCode, itemCode, locCodeTo, lotNo, lotSl, DeallocQty, conn, true, "", 0,"",0);
			}
			System.out.println(" final locCode ["+locCode+"]");
			
			sql = " SELECT (QUANTITY - CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE DEALLOC_QTY END ) AS QUANTITY " +
				  " FROM REPL_ORD_DET WHERE REPL_ORDER = ? AND LINE_NO = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, refId);
			pstmt.setInt(2, pickLineNo);
			rs = pstmt.executeQuery();
			if( rs.next())
			{
				quantity = rs.getDouble("QUANTITY");
			}
			rs.close(); rs = null;
			pstmt.close(); pstmt = null;
			
			if( quantity == 0 )
			{
				sql = "UPDATE REPL_ORD_DET SET CANCEL_MODE = 'Y' WHERE REPL_ORDER = ? AND LINE_NO = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, refId);
				pstmt.setInt(2, pickLineNo);
				if ( pstmt.executeUpdate() > 0 )
				{
					System.out.println(" REPL_ORD_DET updated succesfully ");
				}
				pstmt.close(); pstmt = null;
				
				sql = "UPDATE WAVE_TASK_DET SET STATUS = 'Y' WHERE REF_ID = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, refId);				
				if ( pstmt.executeUpdate() > 0 )
				{
					System.out.println(" WAVE_TASK_DET updated succesfully ");
					updateWaveStatusTasks(refId, "R", conn);
				}
				pstmt.close(); pstmt = null;
				
				/*sql = "UPDATE CARTON_MASTER SET STATUS = 'R' WHERE PICK_ORDER = ? AND LINE_NO__PICK = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, refId);	
				pstmt.setInt(2, pickLineNo);
				if ( pstmt.executeUpdate() > 0 )
				{
					System.out.println(" CARTON_MASTER updated succesfully ");
				}
				pstmt.close(); pstmt = null;*/
				
			}
			
			sql = "UPDATE CARTON_MASTER SET STATUS = 'R' WHERE PICK_ORDER = ? AND LINE_NO__PICK = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, refId);	
			pstmt.setInt(2, pickLineNo);
			if ( pstmt.executeUpdate() > 0 )
			{
				System.out.println(" CARTON_MASTER updated succesfully ");
			}
			pstmt.close(); pstmt = null;
			
			if (errString != null && errString.trim().length() > 0)
			{
				System.out.println("errString :"+errString);
				return errString;
			}
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
			errString = e.getMessage();
			throw new ITMException(e);
			
		}
		finally
		{
			try
			{
				if( rs != null )
				{
					rs.close(); rs = null;						
				}
				if( pstmt != null )
				{					
					pstmt.close(); pstmt = null;	
				}
			}
			catch( Exception ex)
			{				
				ex.printStackTrace();
				errString = ex.getMessage();
				throw new ITMException(ex);
			}
		}
		return errString;
	}
	//changed by sankara on 04/02/14 update carton_master status from pick deallocation start.
	private int updateCartonMasterstatus(String ptcn,Connection conn) throws ITMException
	{
		String sql = "";
		String sql1 = "";
		PreparedStatement pstmt = null,pstmt1 = null;
		ResultSet rs = null,rs1 = null;
		String masterCarton = "";
		String errString = "";
		int update = 0;
		int count = 0;
		System.out.println("hai");
		try
		{
			
			sql = " SELECT DISTINCT A.MASTER_CARTON FROM CARTON_MASTER A, PACK_HDR P WHERE P.PTCN = A.PTCN AND A.PTCN = ? " +
					//Changed By Pragyan 03-JUL-14 to update satus Rejected for unused master cartons
				  //"	AND A.CARTON_TYPE = 'M' AND P.CONFIRMED = 'Y' AND A.STATUS = 'U' ";
				  "	AND A.CARTON_TYPE = 'M' AND P.CONFIRMED = 'Y' AND A.STATUS IN ('U','A') ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, ptcn);
			rs = pstmt.executeQuery();
			while(rs.next())
			{
				masterCarton = rs.getString(1);		
				System.out.println("masterCarton:::::"+masterCarton);
				sql1 = " SELECT COUNT(*) AS COUNT FROM CARTON_MASTER WHERE MASTER_CARTON = ? AND CARTON_TYPE = 'C' AND STATUS = 'U' ";
				pstmt1 = conn.prepareStatement(sql1);
				pstmt1.setString(1, masterCarton);
				rs1 = pstmt1.executeQuery();
				if(rs1.next())
				{
					count = rs1.getInt("COUNT"); 
					System.out.println("count::::::"+count);
					if(count == 0 )
					{
						System.out.println("inside count block");
						sql1 = " UPDATE CARTON_MASTER SET STATUS = 'R' WHERE CARTON_NO = ? AND REF_SER = 'M-PACK' ";
						pstmt1 = conn.prepareStatement(sql1);
						pstmt1.setString(1,masterCarton);
						update = pstmt1.executeUpdate();	
						if( update > 0)
						{
							System.out.println(update+" column of carton_master is successfuly ");
						}		
					}
				}
				rs1.close();
				rs1 = null;
				pstmt1.close(); 
				pstmt1 = null;
			}
			rs.close();
			rs = null;
			pstmt.close(); 
			pstmt = null;      
		}	
		catch(Exception e)
		{
			e.printStackTrace();
			errString = e.getMessage();
			throw new ITMException(e);

		}
		finally
		{
			try
			{
				if( rs != null )
				{
					rs.close(); rs = null;						
				}
				if( rs1 != null )
				{
					rs1.close(); rs1 = null;						
				}
				if( pstmt != null )
				{					
					pstmt.close(); pstmt = null;	
				}
				if( pstmt1 != null )
				{					
					pstmt1.close(); pstmt1 = null;	
				}
			}
			catch( Exception ex)
			{				
				ex.printStackTrace();
				errString = ex.getMessage();
				throw new ITMException(ex);
			}
		}
		return update;
	}
	//changed by sankara on 04/02/14 update carton_master status from pick deallocation end.
	//changed by sankara on 21/06/14 update sorder ship_status from pick deallocation start
	private int updatesaleOrdershipStatus(String saleOrder,String ptcn,Connection conn) throws ITMException
	{
		System.out.println("sankara for ship status for inside method.............");
		String sql = "",sql1 = ""; 
		PreparedStatement pstmt = null,pstmt1= null;
		ResultSet rs = null;
		double allocqty = 0.0;
		double despqty = 0.0;
		String errString = "";
		int updatecount = 0;
		System.out.println("hai");
		try
		{
			System.out.println("saleOrder::::::::"+saleOrder);
			sql = " SELECT SUM(QTY_ALLOC) AS ALLOC_QTY, SUM(QTY_DESP) AS DESP_QTY FROM SORDITEM WHERE SALE_ORDER = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, saleOrder);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				allocqty = rs.getDouble("ALLOC_QTY");		
				System.out.println("allocqty:::::"+allocqty);	
				despqty =  rs.getDouble("DESP_QTY");	
				System.out.println("despqty:::::"+despqty);	
			}
			if(allocqty == 0 && despqty == 0 )
			{
				System.out.println("from sorditem.......");
				sql1 = " UPDATE SORDER SET SHIP_STATUS = ? WHERE SALE_ORDER = ? AND SHIP_STATUS <> ? ";
				pstmt1 = conn.prepareStatement(sql1);
				pstmt1.setString(1, "O");
				pstmt1.setString(2, saleOrder);
				pstmt1.setString(3, "C");
				updatecount = pstmt1.executeUpdate();	
				if(updatecount > 0)
				{
					System.out.println("updatecount["+updatecount+ "saleOrder["+saleOrder+"]");
				}
				pstmt1.close(); 
				pstmt1 = null;	
			}
			else if( despqty > 0 )
			{
				sql1 = " UPDATE SORDER SET SHIP_STATUS = ? WHERE SALE_ORDER = ? AND SHIP_STATUS <> ? ";
				pstmt1 = conn.prepareStatement(sql1);
				pstmt1.setString(1, "C");
				pstmt1.setString(2, saleOrder);
				pstmt1.setString(3, "C");				
				updatecount = pstmt1.executeUpdate();
				if(updatecount > 0)
				{
					System.out.println(" SORDER UPDATE COUNT DESP EXIST["+updatecount+"]");
				}
				pstmt1.close(); 
				pstmt1 = null;		
			}
			System.out.println("sankara......");
		}	
		catch(Exception e)
		{
			e.printStackTrace();
			errString = e.getMessage();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if( rs != null )
				{
					rs.close(); rs = null;						
				}

				if( pstmt != null )
				{					
					pstmt.close(); pstmt = null;	
				}
				if( pstmt1 != null )
				{					
					pstmt1.close(); pstmt1 = null;	
				}
			}
			catch( Exception ex)
			{				
				ex.printStackTrace();
				errString = ex.getMessage();
				throw new ITMException(ex);
			}
		}
		return updatecount;
	}	
	//changed by sankara on 21/06/14 update sorder ship_status from pick deallocation end.
	
	//Changed By Pragyan 02-JUL-14 (Issue tracker No 396)To update status as C if consolidated to doc called.start
	private int updateWaveStatusDespatch(String PTCN, Connection conn) throws ITMException
	{
		String sql = "", sql1 = "";
		String waveId = "";
		String saleOrder = "";
		PreparedStatement pstmt = null;
		ResultSet rs = null, rs1 = null;
		int updateCount = 0;
		int count = 0;
		
		
		try
		{
			sql = "SELECT COUNT(*) FROM WAVE_TASK_DET WHERE PTCN = ? AND REF_SER <> 'S-DSP' AND STATUS = 'N'";			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, PTCN);
			rs = pstmt.executeQuery();
			
			if(rs.next())
			{
				count = rs.getInt(1);
			}
			
			if(rs != null)
			{
				rs.close();
				rs = null;
			}
					
			if(pstmt != null)
			{
				pstmt.close();
				pstmt = null;
			}
			
			
			if( count == 0)
			{
				sql = "UPDATE WAVE_TASK_DET SET WAVE_STATUS = 'C' WHERE  PTCN = ? AND REF_SER = 'S-DSP' ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, PTCN);
				updateCount = pstmt.executeUpdate();
				
				if(updateCount > 0)
				{
					System.out.println("Despatch Has been updated as created["+updateCount+"]");
				}
				else
				{
					System.out.println("Despatch Has Not been updated as created["+updateCount+"]");
				}
				
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
			}
			
		}
		catch(Exception 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 e2) 
			{
				e2.printStackTrace();
				throw new ITMException(e2);
			}
		}
		return updateCount;
	}
	
	private int updateWaveStatusTasks(String refID,String taskType, Connection conn) throws ITMException
	{
		String sql = "", sql1 = "";
		String waveId = "";
		String saleOrder = "";
		PreparedStatement pstmt = null;
		ResultSet rs = null, rs1 = null;
		int updateCount = 0;
		int COUNT = 0;
		
		
		try
		{
			
			if("R".equalsIgnoreCase(taskType))
			{
				sql = "SELECT COUNT(*) AS COUNT " +
						" FROM REPL_ORD_DET WHERE REPL_ORDER = ?  AND (CASE WHEN CANCEL_MODE IS NULL THEN 'N' ELSE CANCEL_MODE END) = 'N'";
			}
			else if("P".equalsIgnoreCase(taskType))
			{
				sql = "SELECT (QUANTITY - CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE DEALLOC_QTY END ) AS COUNT " +
						  " FROM PICK_ORD_DET WHERE PICK_ORDER = ?";
					
			}
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, refID);
			rs = pstmt.executeQuery();
			
			if(rs.next())
			{
				COUNT = rs.getInt(1);
			}
			
			if(rs != null)
			{
				rs.close();
				rs = null;
			}
					
			if(pstmt != null)
			{
				pstmt.close();
				pstmt = null;
			}
			
			
				if( COUNT > 0)
				{
					sql = "UPDATE WAVE_TASK_DET SET WAVE_STATUS = 'V' WHERE  REF_ID = ? ";
				}
				else
				{
					sql = "UPDATE WAVE_TASK_DET SET WAVE_STATUS = 'W' WHERE  REF_ID = ? ";
				}
			
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, refID);
				updateCount = pstmt.executeUpdate();
				
				if(updateCount > 0)
				{
					System.out.println("Task Has been updated as ["+sql+"]");
				}
				else
				{
					System.out.println("sql Not been updated as created["+sql+"]");
				}
				
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
			
			
		}
		catch(Exception 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 e2) 
			{
				e2.printStackTrace();
				throw new ITMException(e2);
			}
		}
		return updateCount;
	}
	//Changed By Pragyan 02-JUL-14 (Issue tracker No 396)To update status as C if consolidated to doc called.start
}
