package ibase.webitm.ejb.wms;

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

import java.io.*;
import java.rmi.RemoteException;
import java.sql.*;
import java.util.*;
import java.text.*;
import javax.ejb.*;
import javax.naming.InitialContext;
import javax.ejb.Stateless; 
import org.w3c.dom.*;

import java.util.Date;


@Stateless 
public class ReplIssConf extends ActionHandlerEJB implements ReplIssConfRemote, ReplIssConfLocal
{
	/**
	 * 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 [InvHoldConfEJB] getCurrdateAppFormat " + exception.getMessage());
		}
		return retString;
	}
	public String confirm( String tranId, String xtraParams,String forcedFlag, Connection conn, boolean conStat ) throws RemoteException,ITMException
	{	
		System.out.println("=====>confirm called");
		String replOrder = "";
		String confirmed = "";
		String retString = "";
		String itemCode = "";
		String siteCode = "";
		String locCode = "";
		String lotNo = "";
		String lotSl = "";		
		String locCodeTo = "";
		String ptcn = "";

		String sql = "";
		String updateSql = "";
		String updateReplSql = "";
		
		java.sql.Timestamp expDate = null;
		java.sql.Timestamp mfgDate = null;
		java.sql.Timestamp restestDate = null;
		String packCode = "";
		String siteCodeMfg = "";
		String packInstr = "";
		String suppCodeMfg = "";
		String unitAlt = "";
		String batchNo = "";
		String unit = "";
		String grade = "";
		String remarks = "";
		String dimension = "";
		String ediOption = "";
		String dataStr = "";
		String acctCodeDr = "";
		String cctrCodeDr = "";
		String saleOrder = "";
		String lineNoSord = "";
		String pickOrder = "";
		String userId = " ";
		String termId = " ";
		String lotSlNew = "", orderType = "";
		//Changed By Sumit To check whether all replenishment close
		String replStatus = "";
		
		//Changed by Rohan on 12-03-13 for define variables.start
		String invState = "";
		String invPndReserve = "";
		String replOrder1 = "";
		int lineNo1 = 0;
		String dsoInvstat = "";
        //Changed by Rohan on 12-03-13 for define variables.end
				
		int replOrderLineNo = 0;
		double grossRate = 0d;
		double convQtyStduom = 0d;
		double batchSize = 0d;
		double stkGrossRate = 0d;
		double stkRate = 0d;

		double quantity = 0.0,pickQty = 0, actualQty = 0,qtyOrd = 0.0,deallocQty = 0.0;
		double noArt = 0.0;
		double shipperSize = 0,itemGrossWeight = 0,	caseGrossWeight = 0, itemNetWeight = 0, caseNetWeight = 0;

		int updCnt = 0, lineNo = 0 , lineNoPick = 0 ;

		HashMap updateRowMap = null;
		HashMap strAllocate = null;
		HashMap itmVolumeMap = null;

		StockUpdate stkUpdate = null;
		InvAllocTraceBean invAllocTrace = null;
		//		commented by chaitali 12/11/11		
		//		Connection connectionObject = null;		
		PreparedStatement pstmt = null;
		PreparedStatement pstmtStock = null;
		PreparedStatement pstmtUpd = null, pstmt1 = null,pstmtPick = null;
		PreparedStatement pstmtReplUpd = null;
		ResultSet rsStock = null,rsPick = null;
		ResultSet rs = null, rs1 = null;
		boolean isError = false;

		GenericUtility genericUtility = GenericUtility.getInstance();

		ITMDBAccessEJB itmDBAccess = new ITMDBAccessEJB();
		//Changed by sumit on 09/08/12 for getting value of pick_order start
		PreparedStatement pstmt2 = null;
		ResultSet rs2 = null;
		//Changed by sumit on 09/08/12 for getting value of pick_order end.
		// 18/09/12 manoharan
		String  expLev = "", itemCodeOrd = "", itemRef = "",  status = "", itemGrade = "", allocMode = "";
		String reasCode = "",unitStd = "";
		//double convQtyStduom = 0;
		java.sql.Timestamp dateAlloc = null;
		//  end 18/09/12 manoharan
		boolean isLastCaseUpDFlag = false;
		double actualAllocQty = 0.0,lastCaseUpdQty = 0.0;
		
		//Changed By Pragyan 17-APR-14 To get Hold Qty from the stock
		double holdQuantity =0.0;
	
		try
		{

			/*-- commented by chaitali 12/11/11
			ConnDriver connDriver = null;
			connDriver = new ConnDriver();
			connectionObject = connDriver.getConnectDB("DriverITM");
			 */
			System.out.println("====>extraparams((" +xtraParams+ "))");
			//Changed by Rohna on 25-03-13 for set Actual qty zero when repanishment confirm.start
			DistCommon discommon = new DistCommon();	
			invPndReserve = discommon.getDisparams("999999","PND_INVSTAT",conn);
			dsoInvstat = discommon.getDisparams( "999999", "DEEP_STORE_INVSTAT", conn );
			//Changed by Rohna on 25-03-13 for set Actual qty zero when repanishment confirm.end

			conn.setAutoCommit(false);

			java.sql.Timestamp currDate = new java.sql.Timestamp( System.currentTimeMillis() );
			String empCode = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginEmpCode" );			

			String dbDateFormat = genericUtility.getDBDateFormat();
			String applDateFormat = genericUtility.getApplDateFormat();
			java.util.Date currDate1 = new java.util.Date();
			SimpleDateFormat sdf = new SimpleDateFormat(applDateFormat);
			String currDateStr = sdf.format(currDate1);

			java.sql.Timestamp tranDate = java.sql.Timestamp.valueOf(genericUtility.getValidDateString( currDateStr , applDateFormat, dbDateFormat ) + " 00:00:00.0") ;

			updateRowMap = new HashMap();
			stkUpdate = new StockUpdate();
			invAllocTrace = new InvAllocTraceBean();
			//Changed by sumit on 27/09/12 initialize itmVolumeMap object
			itmVolumeMap = new HashMap();

			sql = "SELECT CONFIRMED, REPL_ORDER FROM REPL_ISS_HDR WHERE TRAN_ID = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranId);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				confirmed = rs.getString( "CONFIRMED" );
				replOrder = rs.getString( "REPL_ORDER" );
			}
			rs.close();rs = null;
			pstmt.close();pstmt = null;
			//  16/10/11 manoharan
			if (confirmed == null)
			{
				confirmed = "N";
			}
			// end 16/10/11 manoharan
			if( confirmed.equalsIgnoreCase("Y") )
			{
				retString = itmDBAccess.getErrorString("","CONFREPISS","");
				return retString;
			}
			else
			{
				sql = "select acct_code__inv, cctr_code__inv, "
						+ "exp_date, pack_code, mfg_date, site_code__mfg, "
						+ "pack_instr, supp_code__mfg, retest_date, "
						+ "gross_rate, conv__qty_stduom, unit__alt, batch_no, "
						+ " batch_size, unit, grade, remarks, dimension, rate "
						//Changed By Pragyan 17-APR-14 To get Hold Qty from the stock
						+ " ,hold_qty "
						+ "from stock "
						+ "where item_code = ? "
						+ "and site_code = ? "
						+ "and loc_code = ? "
						+ "and lot_no = ? "
						+ "and lot_sl = ? ";
				pstmtStock = conn.prepareStatement(sql);
				//Changed by sumit sarkar 
				//sql = "SELECT R.LINE_NO LINE_NO, R.ITEM_CODE ITEM_CODE, R.SITE_CODE SITE_CODE, R.LOC_CODE LOC_CODE, "
				sql = "SELECT R.LINE_NO LINE_NO, R.ITEM_CODE ITEM_CODE, R.SITE_CODE SITE_CODE, R.LOC_CODE LOC_CODE, O.ACTUAL_QTY, "
						+ " R.LOT_NO LOT_NO, R.LOT_SL LOT_SL, R.QUANTITY QUANTITY, R.NO_ART NO_ART,case when R.LOT_SL__NEW is null then R.LOT_SL else R.LOT_SL__NEW end  LOT_SL__NEW ,"
						//+ " R.LOC_CODE__TO LOC_CODE__TO, O.SALE_ORDER SALE_ORDER, O.LINE_NO__SORD LINE_NO__SORD, O.PICK_ORDER PICK_ORDER, O.LINE_NO__PICK  LINE_NO__PICK, H.ORDER_TYPE "
						+ " R.LOC_CODE__TO LOC_CODE__TO, O.SALE_ORDER SALE_ORDER, O.LINE_NO__SORD LINE_NO__SORD, O.PICK_ORDER PICK_ORDER,O.LINE_NO ORDER_lINE_NO, O.LINE_NO__PICK  LINE_NO__PICK, H.ORDER_TYPE, O.EXP_LEV "
						+ " FROM REPL_ISS_DET R, REPL_ORD_DET O , REPL_ORD_HDR H"
						+ " WHERE R.TRAN_ID = ? "
						+ " AND R.REPL_ORDER = O.REPL_ORDER "   
						+ " AND O.LINE_NO = R.LINE_NO__ORD "  
						+ " AND H.REPL_ORDER = O.REPL_ORDER "
						+ " AND R.LOC_CODE <> R.LOC_CODE__TO ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, tranId);
				rs = pstmt.executeQuery();
				while(rs.next())
				{

					itemCode = checkNullAndTrim(rs.getString( "item_code" ));
					siteCode = checkNullAndTrim(rs.getString( "site_code" ));
					locCode = checkNullAndTrim(rs.getString( "loc_code" ));
					lotNo = checkNullAndTrim(rs.getString( "lot_no" ));
					lotSl = checkNullAndTrim(rs.getString( "lot_sl" ));
					quantity = rs.getDouble( "quantity" );
					noArt = rs.getDouble( "no_art" );
					locCodeTo = checkNullAndTrim(rs.getString( "loc_code__to" ));
					saleOrder = checkNullAndTrim(rs.getString( "SALE_ORDER" ));
					lineNoSord = checkNullAndTrim(rs.getString( "LINE_NO__SORD" ));
					lineNoSord = "   " + lineNoSord; 
					lineNoSord = lineNoSord.substring(lineNoSord.length()-3);
					pickOrder = checkNullAndTrim(rs.getString( "PICK_ORDER" ));
					lineNoPick = rs.getInt( "LINE_NO__PICK" );
					orderType = checkNullAndTrim(rs.getString( "ORDER_TYPE" ));
					// 09/02/13 manoharan actual quantity (balance quantity) is updated in multiples of number of cases so this has to be taken as the total pick quantity
					//Changed by sumit sarkar on 25/07/12 getting actual value from repl_ord_det
					//actualQty = rs.getInt( "ACTUAL_QTY" );
					System.out.println("Order Type"+orderType);
					//Chnaged by Rohan on 15-02-13 for calculating actual qty from repanishment in case of stock to dock.start
					if (!"D".equals(orderType) && !"H".equals(orderType) && !"L".equals(orderType))
					{
						//Chnaged by Rohan on 15-02-13 for calculating actual qty from repanishment in case of stock to dock.end	
						replOrderLineNo  = rs.getInt("ORDER_lINE_NO");
						
						if(!"".equalsIgnoreCase(orderType) && ("Q".equals(orderType) || "T".equals(orderType)  || "P".equals(orderType) || "E".equals(orderType) || "I".equals(orderType) ))
						{
				
							sql = " SELECT SUM(QUANTITY) AS QUANTITY FROM " +
							" ( SELECT SUM(QUANTITY) AS QUANTITY FROM (SELECT SUM(QUANTITY - CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE DEALLOC_QTY END) AS QUANTITY FROM PICK_ORD_DET P " +
							" WHERE P.REPL_ORDER = ? AND P.LINE_NO__REPL = ?   AND FN_IS_VALID_PICK_ORDER(P.PICK_ORDER) = 'Y' " +
							" UNION ALL " +
							" SELECT SUM(QUANTITY - CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE DEALLOC_QTY END) FROM PICK_ORD_DET  WHERE (REPL_ORDER, LINE_NO__REPL) IN " +
							" (SELECT REPL_ORDER , LINE_NO FROM REPL_ORD_DET  WHERE PICK_ORDER = ? AND LINE_NO__PICK = ? ) " +
							" AND FN_IS_VALID_PICK_ORDER(PICK_ORDER) = 'Y' " +
							" UNION ALL " +
							" SELECT SUM(QUANTITY - CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE DEALLOC_QTY END) FROM PICK_ORD_DET  WHERE (REPL_ORDER, LINE_NO__REPL) IN ( " +
							" SELECT REPL_ORDER,LINE_NO FROM REPL_ORD_DET WHERE (PICK_ORDER,LINE_NO__PICK) IN ( " +
							" (SELECT REPL_ORDER , LINE_NO FROM REPL_ORD_DET  WHERE PICK_ORDER = ? AND LINE_NO__PICK = ? )))" +
							" AND FN_IS_VALID_PICK_ORDER(PICK_ORDER) = 'Y' ))";
							//Changed by sumit on 13/03/13 getting quantity as (quantity-dealloc_qty).end
							
							pstmtPick = conn.prepareStatement(sql);
							pstmtPick.setString(1, replOrder);	
							pstmtPick.setInt(2, replOrderLineNo);	
							pstmtPick.setString(3, replOrder);	
							pstmtPick.setInt(4, replOrderLineNo);	
							pstmtPick.setString(5, replOrder);	
							pstmtPick.setInt(6, replOrderLineNo);
							rsPick = pstmtPick.executeQuery();
							
							if (rsPick.next())
							{
								pickQty = rsPick.getDouble("QUANTITY");
							}
							rsPick.close();
							rsPick = null;
							pstmtPick.close();
							pstmtPick = null;
							
							actualQty = quantity - pickQty;
							//Changed By Pragyan 03-May-14 for changed Last case logic if pick quantity > last case.start
							actualAllocQty = getStockQuantity(itemCode,siteCode,locCode,lotNo,lotSl,quantity,true,pickQty,conn);
							//if(actualQty > actualAllocQty)
							if(actualAllocQty != 0.0 && pickQty < actualAllocQty)
							{
								
								System.out.println("Found Last case Found with Other allocation pickQty["+pickQty+"]quantity ["+quantity+"]Stock alloc qty["+actualAllocQty+"]");
								isLastCaseUpDFlag = true;
								lastCaseUpdQty = actualQty;
								//actualQty = 0.0;
								actualQty = quantity - actualAllocQty;
								System.out.println("Now Actual Quantity to issue ["+actualQty+"]");
								
							}
							//Changed By Pragyan 03-May-14 for changed Last case logic if pick quantity > last case.end
						}
						else
						{
							actualAllocQty = getStockQuantity(itemCode,siteCode,locCode,lotNo,lotSl,0.0,false,pickQty,conn);
							actualQty = quantity - actualAllocQty;
						}
					//Chnaged by Rohan on 15-02-13 for calculating actual qty from repanishment in case of stock to dock	
					}	
					// end 09/02/13 manoharan actual quantity (balance quantity) is updated in multiples of number of cases so this has to be taken as the total pick quantity
					
					String dExpLev = checkNullAndTrim(rs.getString( "EXP_LEV" ));
					//Changed by sumit on 27/09/12 getting SHIPPER_SIZE
					itmVolumeMap.clear();
					itmVolumeMap = getItemVoumeMap(itemCode,lotNo,conn);
					// 10/10/12 manoharan
					shipperSize = (Double) itmVolumeMap.get("SHIPPER_SIZE");
					itemGrossWeight = (Double) itmVolumeMap.get("ITEM_GROSS_WEIGHT");
					caseGrossWeight = (Double) itmVolumeMap.get("CASE_GROSS_WEIGHT");
					itemNetWeight = (Double) itmVolumeMap.get("ITEM_NET_WEIGHT");
					caseNetWeight = (Double) itmVolumeMap.get("CASE_NET_WEIGHT");
					lineNo = rs.getInt( "LINE_NO" );
					lotSlNew = rs.getString("lot_sl__new");
					if(lotSlNew == null || lotSlNew.trim().length() == 0) //added by Kunal on 11/09/12
					{
						lotSlNew = lotSl ;
					}
					pstmtStock.setString(1,itemCode);
					pstmtStock.setString(2,siteCode);
					pstmtStock.setString(3,locCode);
					pstmtStock.setString(4,lotNo);
					pstmtStock.setString(5,lotSl);
					rsStock = pstmtStock.executeQuery();
					if ( rsStock.next() )
					{
						acctCodeDr = checkNullAndTrim(rsStock.getString("acct_code__inv"));
						cctrCodeDr = checkNullAndTrim(rsStock.getString("cctr_code__inv"));
						expDate = rsStock.getTimestamp("exp_date");
						packCode = checkNullAndTrim(rsStock.getString("pack_code"));
						mfgDate = rsStock.getTimestamp("mfg_date");
						siteCodeMfg = checkNullAndTrim(rsStock.getString("site_code__mfg"));
						packInstr = checkNullAndTrim(rsStock.getString("pack_instr"));
						suppCodeMfg = checkNullAndTrim(rsStock.getString("supp_code__mfg"));
						restestDate = rsStock.getTimestamp("retest_date");
						stkGrossRate = rsStock.getDouble("gross_rate");
						stkRate = rsStock.getDouble("rate");
						convQtyStduom = rsStock.getDouble("conv__qty_stduom");
						unitAlt = checkNullAndTrim(rsStock.getString("unit__alt"));
						batchNo = checkNullAndTrim(rsStock.getString("batch_no"));
						batchSize = rsStock.getDouble("batch_size");
						unit = checkNullAndTrim(rsStock.getString("unit"));
						grade = checkNullAndTrim(rsStock.getString("grade"));
						remarks = checkNullAndTrim(rsStock.getString("remarks"));
						dimension = checkNullAndTrim(rsStock.getString("dimension"));
						//Changed By Pragyan 17-APR-14 To return error string for Hold Qty from the stoc
						holdQuantity = rsStock.getDouble("hold_qty");
					}
					rsStock.close(); rsStock = null;
					pstmtStock.clearParameters();
					
					//Changed By Pragyan 17-APR-14 To return error string for Hold Qty from the stock.start
					if(holdQuantity > 0)
					{
						
						isError = true;
						retString = getError(siteCode, lotNo, lotSl, itemCode, locCode, "INVITMQTST", conn);
						return retString;

					}
					//Changed By Pragyan 17-APR-14 To return error string for Hold Qty from the stock.end

					updateRowMap.put("item_code", itemCode);
					updateRowMap.put("site_code", siteCode);
					updateRowMap.put("loc_code", locCode);
					updateRowMap.put("lot_no", lotNo);
					updateRowMap.put("lot_sl", lotSl);
					updateRowMap.put("no_art", noArt);
					updateRowMap.put("tran_ser", "REPISS");
					updateRowMap.put("acct_code__cr",acctCodeDr);
					updateRowMap.put("cctr_code__cr",cctrCodeDr);
					updateRowMap.put("acct_code_inv",acctCodeDr);
					updateRowMap.put("cctr_code_inv",cctrCodeDr);
					updateRowMap.put("rate",Double.toString(stkRate));
					updateRowMap.put("gross_rate",Double.toString(stkGrossRate));
					updateRowMap.put("tran_id", tranId );
					// 17/10/11 manoharan
					updateRowMap.put("line_no", "" + lineNo );
					// end 17/10/11 manoharan
					updateRowMap.put("tran_date", tranDate );
					updateRowMap.put("unit",unit);
					updateRowMap.put("grade",grade);
					updateRowMap.put("remarks",remarks);
					updateRowMap.put("dimension",dimension);

					//updateRowMap.put("tran_type", "I");//Creating issue

					//changed by gulzar on 12/24/2011
					updateRowMap.put("exp_date", expDate);
					updateRowMap.put("pack_code", packCode);
					updateRowMap.put("mfg_date", mfgDate);
					updateRowMap.put("site_code__mfg", siteCodeMfg);
					updateRowMap.put("pack_instr", packInstr);
					updateRowMap.put("supp_code__mfg", suppCodeMfg);
					updateRowMap.put("retest_date", restestDate);
					updateRowMap.put("conv__qty_stduom", ""+convQtyStduom);
					updateRowMap.put("unit__alt", unitAlt);
					updateRowMap.put("batch_no", batchNo);
					updateRowMap.put("batch_size", ""+batchSize);
					
					System.out.println(" Active actualQty ["+actualQty+"]"+"quantity"+quantity);
					updateRowMap.put("tran_type","ID"); 
					updateRowMap.put("qty_stduom", ""+  (quantity - actualQty));//140
					updateRowMap.put("quantity", ""+ (quantity - actualQty));
					
					//Chnaged By Rohan on 03-08-13 for if stock is transfer from caspk to actpk then update no of art bug fixing.start	
					//noArt = Math.floor((quantity - actualQty) / shipperSize) ;
					if(!"".equalsIgnoreCase(orderType) && ("Q".equals(orderType) || "T".equals(orderType)  || "P".equals(orderType) || "E".equals(orderType) || "I".equals(orderType) ))
					{
						noArt = Math.floor((quantity) / shipperSize) ;
					}
					else
					{
						noArt = Math.floor((quantity - actualQty) / shipperSize) ;
					}
					//Chnaged By Rohan on 03-08-13 for if stock is transfer from caspk to actpk then update no of art bug fixing.end
					
					updateRowMap.put("no_art", noArt);
					updateRowMap.put("gross_weight", caseGrossWeight * noArt);
					updateRowMap.put("net_weight", caseNetWeight * noArt);
					updateRowMap.put("tare_weight", (caseGrossWeight - caseNetWeight) * noArt);
					// end 10/10/12  manoharan
					retString = stkUpdate.updateStock( updateRowMap, xtraParams, conn );
					if ( retString != null && retString.trim().length() > 0 )
					{
						isError = true;
						break;
					}
					
					//Changed By Pragyan 03-May-14 to check if zero issue quantity not to allow.start
					if(actualQty > 0.0)
					{
						updateRowMap.put("tran_type","I"); 
						updateRowMap.put("qty_stduom", ""+ actualQty);//360
						updateRowMap.put("quantity", ""+actualQty);
						noArt = Math.floor((actualQty) / shipperSize) ;
						updateRowMap.put("no_art", noArt);
						updateRowMap.put("gross_weight", caseGrossWeight * noArt);
						updateRowMap.put("net_weight", caseNetWeight * noArt);
						updateRowMap.put("tare_weight", (caseGrossWeight - caseNetWeight) * noArt);
						
						retString = stkUpdate.updateStock( updateRowMap, xtraParams, conn );
						if ( retString != null && retString.trim().length() > 0 )
						{
							isError = true;
							break;
						}
					}
					//Changed By Pragyan 03-May-14 to check if zero issue quantity not to allow.end
					
					updateRowMap.put("qty_stduom", ""+quantity);
					updateRowMap.put("quantity", ""+quantity);
					updateRowMap.put("loc_code", locCodeTo);
                    updateRowMap.put("lot_sl", lotSlNew);//added by akhilesh on 23/jun/2012 // 24/06/12 manoharan it should be lot_sl not lot_sl__new
					updateRowMap.put("tran_type", "R");//Creating receipt
					noArt = Math.floor((quantity) / shipperSize) ;
					updateRowMap.put("no_art", noArt);
					updateRowMap.put("gross_weight", caseGrossWeight * noArt);
					updateRowMap.put("net_weight", caseNetWeight * noArt);
					updateRowMap.put("tare_weight", (caseGrossWeight - caseNetWeight) * noArt);
					
					retString = stkUpdate.updateStock( updateRowMap, xtraParams, conn );
					if ( retString != null && retString.trim().length() > 0 )
					{
						isError = true;
						break;
					}
					updateRowMap.clear();
					// 16/10/11 manoharan check whether allocation/hold are there
					// if so update the same
					strAllocate = new HashMap();
					strAllocate.put("tran_date",tranDate);
					strAllocate.put("ref_ser","REPISS");
					strAllocate.put("ref_id",tranId);
					strAllocate.put("ref_line", "" + lineNo);
					strAllocate.put("site_code",siteCode);
					strAllocate.put("item_code",itemCode);
					strAllocate.put("loc_code",locCodeTo);
					strAllocate.put("lot_no",lotNo);
					strAllocate.put("lot_sl",lotSlNew);
					//changed by chaitali 12.11.11
					//					strAllocate.put("chg_user",GenericUtility.getInstance().getValueFromXTRA_PARAMS(xtraParams,"loginCode"));
					//					strAllocate.put("chg_term",GenericUtility.getInstance().getValueFromXTRA_PARAMS(xtraParams,"termId"));
					strAllocate.put("chg_user",	 userId);
					strAllocate.put("chg_term",	termId);
					//change by chaitali 12/11/11
					//strAllocate.put("chg_win","w_pick_iss");
					strAllocate.put("chg_win","w_repl_iss");

					strAllocate.put("alloc_qty",new Double(quantity - actualQty));

					retString = invAllocTrace.updateInvallocTrace(strAllocate, conn);
					System.out.println("retString ::: " + retString);
					if (retString != null && retString.trim().length() > 0 )
					{
						isError = true;
						break;
					}
					strAllocate = null;
					
					//Changed By Pragyan 21/02/13 To add Order type "I".
					// end 16/10/11 manoharan allocation stock for new location
					// 16/10/11 manoharan update sordalloc
					// 18/09/12 manoharan as the lot_sl also changed and allocation may be for case pick as well as active pick
					// reduce the quantity in existing and insert new for the active
					//if ("A".equals(orderType) || "E".equals(orderType) || "Q".equals(orderType) || "T".equals(orderType) )
					//if ("E".equals(orderType) || "P".equals(orderType) || "Q".equals(orderType) || "T".equals(orderType)) // all active replenishment
					/*if ("E".equals(orderType) || "P".equals(orderType) || "Q".equals(orderType) || "T".equals(orderType) || "I".equals(orderType)) // all active replenishment
					{*/
						//Changed By Pragyan 06/12/2012 To get valid Pick Order.start
						/*sql = " SELECT * FROM PICK_ORD_DET "
							//+ " WHERE SALE_ORDER = ? AND LINE_NO__SORD = ? "
							+ " WHERE ITEM_CODE = ? "
							+ " AND LOC_CODE = ? "
							+ " AND LOT_NO = ? "
							+ " AND LOT_SL = ? "
							+ " AND REPL_ORDER = ? "
							+ " AND LINE_NO__REPL = ? ";*/
						
						//Changed by sumit on 08/02/13 changed the sql ( Told Manoharan Sir) start
						/*sql = " SELECT * FROM PICK_ORD_DET P"
							//+ " WHERE SALE_ORDER = ? AND LINE_NO__SORD = ? "
							+ " WHERE P.ITEM_CODE = ? "
							//+ " AND P.LOC_CODE = ? "
							+ " AND P.LOT_NO = ? "
							+ " AND P.LOT_SL = ? "
							+ " AND P.REPL_ORDER = ? "
							+ " AND P.LINE_NO__REPL = ?  AND FN_IS_VALID_PICK_ORDER(P.PICK_ORDER) = 'Y'";*/
						//Changed By Pragyan 06/12/2012 To get valid Pick Order.end
						
						//Changed By Pragyan 21/02/13 Logic change as above by manohran sir
						/*sql = " SELECT * FROM PICK_ORD_DET P WHERE P.REPL_ORDER = ? AND P.LINE_NO__REPL = ?  " +
								" AND FN_IS_VALID_PICK_ORDER(P.PICK_ORDER) = 'Y'";*/
						
						sql = "SELECT * FROM PICK_ORD_DET P WHERE P.REPL_ORDER = ? AND P.LINE_NO__REPL =? "
								+ " AND FN_IS_VALID_PICK_ORDER(P.PICK_ORDER) = 'Y' AND (QUANTITY  -(CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE DEALLOC_QTY END)) > 0 "
								+ " UNION ALL " 
								+ " SELECT * FROM PICK_ORD_DET  WHERE (REPL_ORDER, LINE_NO__REPL) IN ( " 
								+ " SELECT REPL_ORDER , LINE_NO FROM REPL_ORD_DET  WHERE PICK_ORDER = ? AND LINE_NO__PICK =? ) " 
								+ " AND FN_IS_VALID_PICK_ORDER(PICK_ORDER) = 'Y' AND (QUANTITY  -(CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE DEALLOC_QTY END)) > 0 " 
								+ " UNION ALL "
								+ " SELECT * FROM PICK_ORD_DET  WHERE (REPL_ORDER, LINE_NO__REPL) IN ( " 
								+ " SELECT REPL_ORDER,LINE_NO FROM REPL_ORD_DET WHERE (PICK_ORDER,LINE_NO__PICK) IN ( "
								+ " (SELECT REPL_ORDER , LINE_NO FROM REPL_ORD_DET  WHERE PICK_ORDER =? AND LINE_NO__PICK = ? ))) "
								+ " AND FN_IS_VALID_PICK_ORDER(PICK_ORDER) = 'Y'  AND (QUANTITY  -(CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE DEALLOC_QTY END)) > 0";

						
						//Changed by sumit on 08/02/13 changed the sql ( Told Manoharan Sir) end
						
							pstmtPick = conn.prepareStatement(sql);
							pstmtPick.setString(1, replOrder);	
							pstmtPick.setInt(2, replOrderLineNo);	
							//Changed By Pragyan 21/02/13 Logic change as above by manohran sir.start
							pstmtPick.setString(3, replOrder);	
							pstmtPick.setInt(4, replOrderLineNo);
							pstmtPick.setString(5, replOrder);	
							pstmtPick.setInt(6, replOrderLineNo);
							//Changed By Pragyan 21/02/13 Logic change as above by manohran sir.end
							rsPick = pstmtPick.executeQuery();
							
							//Changed by sumit on 08/02/13 change to while
							//if(rsPick.next())
							while(rsPick.next())
							{
								pickQty = rsPick.getDouble("QUANTITY");
								deallocQty = rsPick.getDouble("DEALLOC_QTY");
								System.out.println("19/03/13 pickQty [" + pickQty + "]deallocQty ["+deallocQty+"]");
								pickQty = pickQty - deallocQty;
								System.out.println("19/03/13 after calculating pickQty [" + pickQty + "]");
								
								saleOrder = rsPick.getString("SALE_ORDER");
								lineNoSord = rsPick.getString("LINE_NO__SORD");
								lineNoSord = "   " + lineNoSord; 
								lineNoSord = lineNoSord.substring(lineNoSord.length()-3);
								
								sql = " SELECT EXP_LEV, ITEM_CODE__ORD, "
									+ " ITEM_REF, UNIT, DATE_ALLOC, STATUS, ITEM_GRADE, EXP_DATE, ALLOC_MODE, SITE_CODE, "
									+ " CONV__QTY_STDUOM, UNIT__STD,  MFG_DATE, SITE_CODE__MFG, REAS_CODE,QUANTITY FROM SORDALLOC  "
									+ " WHERE SALE_ORDER = ? AND LINE_NO = ? "
									+ " AND ITEM_CODE = ? "
									+ " AND LOC_CODE = ? "
									+ " AND LOT_NO = ? "
									+ " AND LOT_SL = ? "
									+ " AND QTY_ALLOC > 0";
									pstmt1 = conn.prepareStatement(sql);
									pstmt1.setString(1, saleOrder);
									pstmt1.setString(2, lineNoSord);				
									pstmt1.setString(3, itemCode);				
									pstmt1.setString(4, locCode);				
									pstmt1.setString(5, lotNo);				
									pstmt1.setString(6, lotSl);				
									rs1 = pstmt1.executeQuery();
									
									if( rs1.next())
									{
									
										System.out.println("Inside the Query the Exp level["+expLev+"]");
										expLev = rs1.getString( "EXP_LEV" );
										System.out.println("Inside the Query the Exp level["+expLev+"]");
										if (expLev == null || expLev.trim().length() == 0)
										{
											expLev = dExpLev;
										}
										if (expLev == null || expLev.trim().length() == 0)
										{
											expLev = " ";
										}
										itemCodeOrd = rs1.getString( "ITEM_CODE__ORD" );
										itemRef = rs1.getString( "ITEM_REF" );
										unit = rs1.getString( "UNIT" );
										status = rs1.getString( "STATUS" );
										itemGrade = rs1.getString( "ITEM_GRADE" );
										allocMode = rs1.getString( "ALLOC_MODE" );
										siteCode = rs1.getString( "SITE_CODE" );
										siteCodeMfg = rs1.getString( "SITE_CODE__MFG" );
										reasCode = rs1.getString( "REAS_CODE" );
										unitStd = rs1.getString( "UNIT__STD" );
										convQtyStduom = rs1.getDouble( "CONV__QTY_STDUOM" );
										dateAlloc = rs1.getTimestamp("DATE_ALLOC");
										expDate = rs1.getTimestamp("EXP_DATE");
										mfgDate = rs1.getTimestamp("MFG_DATE");
										qtyOrd = rs1.getDouble( "QUANTITY" );
									}
									else
									{
										retString = itmDBAccess.getErrorString("","RECNOTSORA","");
										return retString;
									}
									rs1.close();
									rs1 = null;
									pstmt1.close();
									pstmt1 = null;
									
									
									
								//updateSql = "UPDATE SORDALLOC SET  QUANTITY = quantity - ?, QTY_ALLOC = QTY_ALLOC - ? " //LOC_CODE = ? ,LOT_SL = ? "
								//changed by sankara on 17/10/13 update wave_flag = 'Y'	
								//updateSql = "UPDATE SORDALLOC SET   QTY_ALLOC = QTY_ALLOC - ? " //LOC_CODE = ? ,LOT_SL = ? "
								  updateSql = "UPDATE SORDALLOC SET   QTY_ALLOC = QTY_ALLOC - ?, WAVE_FLAG = 'Y' "
										+ " WHERE SALE_ORDER = ? AND LINE_NO = ? "
										+ " AND ITEM_CODE = ? "
										+ " AND LOC_CODE = ? "
										+ " AND LOT_NO = ? "
										+ " AND LOT_SL = ? "
										+ " AND QTY_ALLOC > 0";
								pstmtUpd = conn.prepareStatement(updateSql);
								//pstmtUpd.setDouble(1, actualQty); //locCodeTo);
								//pstmtUpd.setDouble(1, actualQty); //lotSlNew); //added by Kunal on 11/09/12 as per S Manoharan sir intruction 
								pstmtUpd.setDouble(1, pickQty); //lotSlNew); //added by Kunal on 11/09/12 as per S Manoharan sir intruction
								pstmtUpd.setString(2, saleOrder);
								pstmtUpd.setString(3, lineNoSord);				
								pstmtUpd.setString(4, itemCode);				
								pstmtUpd.setString(5, locCode);				
								pstmtUpd.setString(6, lotNo);				
								pstmtUpd.setString(7, lotSl);				

								updCnt = pstmtUpd.executeUpdate();
								System.out.println("manohar 18/09/12 sordalloc updated  updCnt [" + updCnt + "]");
								//pstmtUpd.close();
								//pstmtUpd = null;
									
								
							
								if (expLev == null || expLev.trim().length() == 0)
								{
									System.out.println("Check1l["+expLev+"]");
									expLev = dExpLev;
								}
								System.out.println("Finally the Exp level["+expLev+"]");
								//Changed by sumit on 25/02/13 checking whether already insertion done in sordalloc if yes then update start 
												
								
								sql = " SELECT SALE_ORDER, LINE_NO, EXP_LEV, ITEM_CODE, LOT_NO, LOT_SL, LOC_CODE FROM SORDALLOC " +
									  " WHERE  SALE_ORDER = ? AND LINE_NO = ? AND EXP_LEV = ? AND ITEM_CODE = ? AND LOT_NO = ? " +
									  " AND LOT_SL = ? AND LOC_CODE = ? ";
								pstmt1 = conn.prepareStatement(sql);								
								pstmt1.setString(1, saleOrder);
								pstmt1.setString(2, lineNoSord);
								pstmt1.setString(3, expLev);
								pstmt1.setString(4, itemCode);
								pstmt1.setString(5, lotNo);
								pstmt1.setString(6, lotSlNew);
								pstmt1.setString(7, locCodeTo);
								
								rs1 = pstmt1.executeQuery();
								if( rs1.next())
								{
									    //changed by sankara on 17/10/13 update wave_flag
										//updateSql = "UPDATE SORDALLOC SET   QTY_ALLOC = QTY_ALLOC + ? " //LOC_CODE = ? ,LOT_SL = ? "
									     updateSql = "UPDATE SORDALLOC SET   QTY_ALLOC = QTY_ALLOC + ?, WAVE_FLAG = 'Y' "
										+ " WHERE SALE_ORDER = ? AND LINE_NO = ? "
										+ " AND EXP_LEV = ? "
										+ " AND ITEM_CODE = ? "
										+ " AND LOC_CODE = ? "
										+ " AND LOT_NO = ? "
										+ " AND LOT_SL = ? "
										+ " AND QTY_ALLOC > 0";									
										pstmtUpd = conn.prepareStatement(updateSql);
										pstmtUpd.setDouble(1, pickQty);
										pstmtUpd.setString(2, saleOrder);
										pstmtUpd.setString(3, lineNoSord);
										pstmtUpd.setString(4, expLev);								
										pstmtUpd.setString(5, itemCode); //pickQty
										pstmtUpd.setString(6, locCodeTo);
										pstmtUpd.setString(7, lotNo);
										pstmtUpd.setString(8, lotSlNew);
										updCnt = pstmtUpd.executeUpdate();
										if( updCnt > 0 )
										{
											System.out.println( " updated successfullt in sordalloc ");
										}
										else
										{
											throw new Exception(" Update fail in sordalloc");
										}
										pstmtUpd.close(); pstmtUpd = null;
								
									/*if ("E".equals(orderType) || "P".equals(orderType) || "Q".equals(orderType) || "T".equals(orderType) || "I".equals(orderType)) // all active replenishment
									{
										updateSql = "UPDATE SORDALLOC SET   QTY_ALLOC = QTY_ALLOC + ? " //LOC_CODE = ? ,LOT_SL = ? "
												+ " WHERE SALE_ORDER = ? AND LINE_NO = ? "
												+ " AND EXP_LEV = ? "
												+ " AND ITEM_CODE = ? "
												+ " AND LOC_CODE = ? "
												+ " AND LOT_NO = ? "
												+ " AND LOT_SL = ? "
												+ " AND QTY_ALLOC > 0";									
										pstmtUpd = conn.prepareStatement(updateSql);
										pstmtUpd.setDouble(1, pickQty);
										pstmtUpd.setString(2, saleOrder);
										pstmtUpd.setString(3, lineNoSord);
										pstmtUpd.setString(4, expLev);								
										pstmtUpd.setString(5, itemCode); //pickQty
										pstmtUpd.setString(6, locCodeTo);
										pstmtUpd.setString(7, lotNo);
										pstmtUpd.setString(8, lotSlNew);
										updCnt = pstmtUpd.executeUpdate();
										if( updCnt > 0 )
										{
											System.out.println( " updated successfullt in sordalloc ");
										}
										else
										{
											throw new Exception(" Update fail in sordalloc");
										}
										pstmtUpd.close(); pstmtUpd = null;
									}
									else
									{
										System.out.println("manohar 26/10/12 ");
										updateSql = "UPDATE SORDALLOC SET LOC_CODE = ? ,LOT_SL = ? "
												+ " WHERE " 
												+" SALE_ORDER = ? AND LINE_NO = ?  AND " 
												+ " ITEM_CODE = ? "
												+ " AND LOC_CODE = ? "
												+ " AND LOT_NO = ? "
												+ " AND LOT_SL = ? "
												+ " AND QTY_ALLOC > 0";
										pstmtUpd = conn.prepareStatement(updateSql);
										pstmtUpd.setString(1, locCodeTo);
										pstmtUpd.setString(2, lotSlNew); //added by Kunal on 11/09/12 as per S Manoharan sir intruction 
										//To Check whether th other saleorder exist with replenishmnet.start
										pstmtUpd.setString(3, saleOrder);
										pstmtUpd.setString(4, lineNoSord);				
										//To Check whether th other saleorder exist with replenishmnet.end
										pstmtUpd.setString(5, itemCode);				
										pstmtUpd.setString(6, locCode);				
										pstmtUpd.setString(7, lotNo);				
										pstmtUpd.setString(8, lotSl);				

										updCnt = pstmtUpd.executeUpdate();
										pstmtUpd.close();
										pstmtUpd = null;
									}
									
*/								}
								else
								{
									updateSql = "insert into sordalloc( SALE_ORDER, LINE_NO, EXP_LEV, ITEM_CODE__ORD, "
											+ " ITEM_CODE, LOT_NO, LOT_SL, LOC_CODE, ITEM_REF, QUANTITY, UNIT, "
											+ " QTY_ALLOC, DATE_ALLOC, STATUS, ITEM_GRADE, EXP_DATE, ALLOC_MODE, SITE_CODE, "
											+ " CONV__QTY_STDUOM, UNIT__STD, QUANTITY__STDUOM, MFG_DATE, SITE_CODE__MFG, "
											//changed by sankara on 15/10/13 for insert into wave_flag start.	
											/*	+ " REAS_CODE ) VALUES (?, ?, ?, ?, "
												+ " ?, ?, ? , ?, ?, ?, ?, "
												+ " ?,?, ?, ?, ?, ?, ?, "
												+ " ?, ?, ? , ?, ?, ?) ";    */
												+ " REAS_CODE, WAVE_FLAG ) VALUES (?, ?, ?, ?, "
												+ " ?, ?, ? , ?, ?, ?, ?, "
												+ " ?,?, ?, ?, ?, ?, ?, "
												+ " ?, ?, ? , ?, ?, ?, ?) ";
										  //changed by sankara on 15/10/13 for insert into wave_flag end.	

										pstmtUpd = conn.prepareStatement(updateSql);
										pstmtUpd.setString(1, saleOrder);
										pstmtUpd.setString(2, lineNoSord);
										pstmtUpd.setString(3, expLev);
										pstmtUpd.setString(4, itemCodeOrd);
										pstmtUpd.setString(5, itemCode);
										pstmtUpd.setString(6, lotNo);
										pstmtUpd.setString(7, lotSlNew);
										pstmtUpd.setString(8, locCodeTo);
										pstmtUpd.setString(9, itemRef);
										//pstmtUpd.setDouble(10, actualQty);
										pstmtUpd.setDouble(10, qtyOrd);
										pstmtUpd.setString(11, unit);
										//pstmtUpd.setDouble(12, actualQty);
										pstmtUpd.setDouble(12, pickQty);
										pstmtUpd.setTimestamp(13, dateAlloc);
										pstmtUpd.setString(14, status);
										pstmtUpd.setString(15, itemGrade);
										pstmtUpd.setTimestamp(16, expDate);
										pstmtUpd.setString(17, allocMode);
										pstmtUpd.setString(18, siteCode);
										pstmtUpd.setDouble(19, convQtyStduom ); 
										pstmtUpd.setString(20,  unitStd); 
										pstmtUpd.setDouble(21, actualQty * convQtyStduom); 
										pstmtUpd.setTimestamp(22, mfgDate ); 
										pstmtUpd.setString(23, siteCodeMfg);
										pstmtUpd.setString(24, reasCode);	
										//changed by sankara on 15/10/13 insert wave_flag
										pstmtUpd.setString(25, "Y");	
										updCnt = pstmtUpd.executeUpdate();
										if(updCnt > 0 )
										{
											System.out.println(" insertion done in sordalloc");
										}
										else
										{
											throw new Exception(" INSERTION FAIL IN SORDALLOC TABLE");
										}
										pstmtUpd.close(); pstmtUpd = null;
								}
								rs1.close();
								rs1 = null;
								pstmt1.close();
								pstmt1 = null;
								//Changed by sumit on 25/02/13 checking whether already insertion done in sordalloc if yes then update end

								System.out.println("manohar 18/09/12 sordalloc inserted  updCnt [" + updCnt + "]");
							}
							rsPick.close();
							rsPick = null;
							pstmtPick.close();
							pstmtPick = null;
						
						
					//}
					/*else
					{
						
						System.out.println("manohar 26/10/12 ");
						updateSql = "UPDATE SORDALLOC SET LOC_CODE = ? ,LOT_SL = ? "
								+ " WHERE " 
								//"SALE_ORDER = ? AND LINE_NO = ?  AND " +
								+ " ITEM_CODE = ? "
								+ " AND LOC_CODE = ? "
								+ " AND LOT_NO = ? "
								+ " AND LOT_SL = ? "
								+ " AND QTY_ALLOC > 0";
						pstmtUpd = conn.prepareStatement(updateSql);
						pstmtUpd.setString(1, locCodeTo);
						pstmtUpd.setString(2, lotSlNew); //added by Kunal on 11/09/12 as per S Manoharan sir intruction 
						//To Check whether th other saleorder exist with replenishmnet.start
						pstmtUpd.setString(3, saleOrder);
						pstmtUpd.setString(4, lineNoSord);				
						//To Check whether th other saleorder exist with replenishmnet.end
						pstmtUpd.setString(3, itemCode);				
						pstmtUpd.setString(4, locCode);				
						pstmtUpd.setString(5, lotNo);				
						pstmtUpd.setString(6, lotSl);				

						updCnt = pstmtUpd.executeUpdate();
						pstmtUpd.close();
						pstmtUpd = null;
					}*/
						
							
					
					//Changed by Pragyan 05-Apr-14 to un comment for stock-to-dock moving to dock location
					
					/*if ("D".equals(orderType) || "H".equals(orderType) || "L".equals(orderType))
					{*/
					if(!"Q".equalsIgnoreCase(orderType) && ! "T".equalsIgnoreCase(orderType) && !"P".equalsIgnoreCase(orderType) && !"E".equalsIgnoreCase(orderType) && !"I".equalsIgnoreCase(orderType) )
					{
						
						System.out.println("Updating Our New logic orderType ["+orderType+"]");
						
						updateSql = "UPDATE SORDALLOC SET LOC_CODE = ? ,LOT_SL = ? "
								+ " WHERE " 
								//"SALE_ORDER = ? AND LINE_NO = ?  AND " +
								+ " ITEM_CODE = ? "
								+ " AND LOC_CODE = ? "
								+ " AND LOT_NO = ? "
								+ " AND LOT_SL = ? "
								+ " AND QTY_ALLOC > 0";
						pstmtUpd = conn.prepareStatement(updateSql);
						pstmtUpd.setString(1, locCodeTo);
						pstmtUpd.setString(2, lotSlNew); //added by Kunal on 11/09/12 as per S Manoharan sir intruction 
						pstmtUpd.setString(3, itemCode);				
						pstmtUpd.setString(4, locCode);				
						pstmtUpd.setString(5, lotNo);				
						pstmtUpd.setString(6, lotSl);				

						updCnt = pstmtUpd.executeUpdate();
						
						if(pstmtUpd != null)
						{
							pstmtUpd.close();
							pstmtUpd = null;
						}
					}
					else
					{
						if(isLastCaseUpDFlag)
						{
							double remainQty = 0.0,spltblQty = 0.0;
							String sordUpdNo = "",updLineNo = "";
							sql = " SELECT S.SALE_ORDER, S.LINE_NO,S.QTY_ALLOC, S.EXP_LEV, S.ITEM_CODE, S.LOT_NO, S.LOT_SL, S.LOC_CODE, " +
									" S.ALLOC_MODE,S.QUANTITY FROM SORDALLOC S " +
									  " WHERE   S.ITEM_CODE = ? AND S.LOT_NO = ? AND S.LOT_SL = ? AND S.LOC_CODE = ? AND   CASE WHEN WAVE_FLAG IS NULL THEN 'N'  ELSE WAVE_FLAG END <> 'Y' " +
									  " AND NVL((SELECT COUNT(1) FROM REPL_ORD_DET R WHERE R.SALE_ORDER = S.SALE_ORDER AND R.LINE_NO__SORD = S.LINE_NO ),0) = 0 " +
									  " AND S.QTY_ALLOC >0 ORDER BY QTY_ALLOC ";
								pstmt1 = conn.prepareStatement(sql);								
								pstmt1.setString(1, itemCode);
								pstmt1.setString(2, lotNo);
								pstmt1.setString(3, lotSl);
								pstmt1.setString(4, locCode);
								
								
								rs1 = pstmt1.executeQuery();
								remainQty = lastCaseUpdQty;
								while( rs1.next())
								{
									sordUpdNo = rs1.getString("SALE_ORDER");
									updLineNo = rs1.getString("LINE_NO");
									spltblQty = rs1.getDouble("QTY_ALLOC");
									allocMode = rs1.getString("ALLOC_MODE");
									qtyOrd =  	rs1.getDouble("QUANTITY");
									
									updLineNo = "   " + updLineNo; 
									updLineNo = updLineNo.substring(updLineNo.length()-3);
									
									if(lastCaseUpdQty > 0)
									{
										if( spltblQty > lastCaseUpdQty)
										{
											
											
											updateSql = "UPDATE SORDALLOC SET   QTY_ALLOC = QTY_ALLOC - ?"
													+ " WHERE SALE_ORDER = ? AND LINE_NO = ? "
													+ " AND EXP_LEV = ? "
													+ " AND ITEM_CODE = ? "
													+ " AND LOC_CODE = ? "
													+ " AND LOT_NO = ? "
													+ " AND LOT_SL = ? "
													+ " AND QTY_ALLOC > 0";									
													pstmtUpd = conn.prepareStatement(updateSql);
													pstmtUpd.setDouble(1, lastCaseUpdQty);
													pstmtUpd.setString(2, sordUpdNo);
													pstmtUpd.setString(3, updLineNo);
													pstmtUpd.setString(4, expLev);								
													pstmtUpd.setString(5, itemCode); //pickQty
													pstmtUpd.setString(6, locCode);
													pstmtUpd.setString(7, lotNo);
													pstmtUpd.setString(8, lotSl);
													updCnt = pstmtUpd.executeUpdate();
													if( updCnt > 0 )
													{
														System.out.println( " updated successfullt in sordalloc ");
													}
													else
													{
														throw new Exception(" Update fail in sordalloc");
													}
													pstmtUpd.close(); pstmtUpd = null;
													
													updateSql = "insert into sordalloc( SALE_ORDER, LINE_NO, EXP_LEV, ITEM_CODE__ORD, "
															+ " ITEM_CODE, LOT_NO, LOT_SL, LOC_CODE, ITEM_REF, QUANTITY, UNIT, "
															+ " QTY_ALLOC, DATE_ALLOC, STATUS, ITEM_GRADE, EXP_DATE, ALLOC_MODE, SITE_CODE, "
															+ " CONV__QTY_STDUOM, UNIT__STD, QUANTITY__STDUOM, MFG_DATE, SITE_CODE__MFG, "
															+ " REAS_CODE, WAVE_FLAG ) VALUES (?, ?, ?, ?, "
																+ " ?, ?, ? , ?, ?, ?, ?, "
																+ " ?,?, ?, ?, ?, ?, ?, "
																+ " ?, ?, ? , ?, ?, ?, ?) ";
												
	
														pstmtUpd = conn.prepareStatement(updateSql);
														pstmtUpd.setString(1, sordUpdNo);
														pstmtUpd.setString(2, updLineNo);
														pstmtUpd.setString(3, expLev);
														pstmtUpd.setString(4, itemCodeOrd);
														pstmtUpd.setString(5, itemCode);
														pstmtUpd.setString(6, lotNo);
														pstmtUpd.setString(7, lotSlNew);
														pstmtUpd.setString(8, locCodeTo);
														pstmtUpd.setString(9, itemRef);
														pstmtUpd.setDouble(10, lastCaseUpdQty);
														pstmtUpd.setString(11, unit);
														pstmtUpd.setDouble(12, lastCaseUpdQty);
														pstmtUpd.setTimestamp(13, dateAlloc);
														pstmtUpd.setString(14, status);
														pstmtUpd.setString(15, itemGrade);
														pstmtUpd.setTimestamp(16, expDate);
														pstmtUpd.setString(17, allocMode);
														pstmtUpd.setString(18, siteCode);
														pstmtUpd.setDouble(19, convQtyStduom ); 
														pstmtUpd.setString(20,  unitStd); 
														pstmtUpd.setDouble(21, lastCaseUpdQty * convQtyStduom); 
														pstmtUpd.setTimestamp(22, mfgDate ); 
														pstmtUpd.setString(23, siteCodeMfg);
														pstmtUpd.setString(24, reasCode);	
														//changed by sankara on 15/10/13 insert wave_flag
														pstmtUpd.setString(25, "N");	
														updCnt = pstmtUpd.executeUpdate();
														if(updCnt > 0 )
														{
															System.out.println(" insertion done in sordalloc");
															
															lastCaseUpdQty = 0.0;
														}
														else
														{
															throw new Exception(" INSERTION FAIL IN SORDALLOC TABLE");
														}
														pstmtUpd.close(); pstmtUpd = null;
														
														
										}//if( spltblQty > lastCaseUpdQty)
										else 
										{
																				
											updateSql = "UPDATE SORDALLOC SET   LOC_CODE = ?,LOT_SL = ?"
													+ " WHERE SALE_ORDER = ? AND LINE_NO = ? "
													+ " AND EXP_LEV = ? "
													+ " AND ITEM_CODE = ? "
													+ " AND LOC_CODE = ? "
													+ " AND LOT_NO = ? "
													+ " AND LOT_SL = ? "
													+ " AND QTY_ALLOC > 0";									
													pstmtUpd = conn.prepareStatement(updateSql);
													pstmtUpd.setString(1, locCodeTo);
													pstmtUpd.setString(2, lotSlNew);
													pstmtUpd.setString(3, sordUpdNo);
													pstmtUpd.setString(4, updLineNo);
													pstmtUpd.setString(5, expLev);								
													pstmtUpd.setString(6, itemCode); 
													pstmtUpd.setString(7, locCode);
													pstmtUpd.setString(8, lotNo);
													pstmtUpd.setString(9, lotSl);
													updCnt = pstmtUpd.executeUpdate();
													if( updCnt > 0 )
													{
														System.out.println( " updated successfullt in sordalloc ");
														
														lastCaseUpdQty = lastCaseUpdQty - spltblQty;
													}
													else
													{
														throw new Exception(" Update fail in sordalloc");
													}
													pstmtUpd.close(); pstmtUpd = null;
										}//else
										 
										
									}//if(lastCaseUpdQty > 0)
									else
									{
										System.out.println( " Inside break for no qty avl ");
										break;
									}
								}//while( rs1.next())
								
								rs1.close();
								rs1 = null;
								pstmt1.close();
								pstmt1 = null;
						}
					}
					// end 18/09/12 manoharan as the lot_sl also changed and allocation may be for case pick as well as active pick
					// end 16/10/11 manoharan update sordalloc

					/////////////////
					//Changed By Pragyan 24/12/12 To update location code and lot sl for replenished record.start
					if(!locCodeTo.equalsIgnoreCase(locCode) || lotSl.equalsIgnoreCase(lotSlNew))
					{
						updateReplSql ="UPDATE REPL_ORD_DET SET LOC_CODE__TO = ? , LOT_SL = ?  "
									   + " WHERE REPL_ORDER = ? AND LINE_NO = ? ";
						pstmtReplUpd = conn.prepareStatement(updateReplSql);
						pstmtReplUpd.setString(1, locCodeTo);
						pstmtReplUpd.setString(2, lotSlNew);
						pstmtReplUpd.setString(3, replOrder);
						pstmtReplUpd.setInt(4, replOrderLineNo);
						int updCntRepl =  pstmtReplUpd.executeUpdate();
						System.out.println("Updating Replenishment change in location and lot sl ["+updCntRepl+"]");
						
						if(pstmtReplUpd != null)
						{
							pstmtReplUpd.close();
							pstmtReplUpd = null;
						}
					}
					
					//Changed By Pragyan 24/12/12 To update location code and lot sl for replenished record.end
					System.out.println("orderType [" + orderType + "]");
					//Changed by sumit on 06/02/12 commenting condition. start
					/*if ( "E".equals(orderType) || "F".equals(orderType) || "G".equals(orderType) || "H".equals(orderType) )
					{
						//updateSql = "UPDATE REPL_ORD_DET SET LOC_CODE = ? , LOT_SL = ?  "
						//	+ " WHERE REPL_ORDER = ? AND LINE_NO = ? ";
						updateSql = "UPDATE REPL_ORD_DET SET LOC_CODE = ? , LOT_SL = ?  "
							+ " WHERE PICK_ORDER = ? AND LINE_NO__PICK = ? ";
						pstmtUpd = conn.prepareStatement(updateSql);
						pstmtUpd.setString(1, locCodeTo);
						pstmtUpd.setString(2, lotSlNew);
						pstmtUpd.setString(3, pickOrder);
						pstmtUpd.setInt(4, lineNoPick);		
						pstmtUpd.setString(3, replOrder);
						pstmtUpd.setInt(4, replOrderLineNo);		
						//pstmtUpd.clearParameters();
					}
					else
					{
						if ( "J".equals(orderType) || "K".equals(orderType) || "I".equals(orderType))
						{
							updateSql = "UPDATE REPL_ORD_DET SET LOC_CODE = ? , LOT_SL = ?  "
							+ " WHERE PICK_ORDER = ? AND LINE_NO__PICK = ? AND REPL_TYPE IN ('Q','I','T')";
							
							pstmtUpd = conn.prepareStatement(updateSql);
							pstmtUpd.setString(1, locCodeTo);
							pstmtUpd.setString(2, lotSlNew);
							pstmtUpd.setString(3, replOrder);
							pstmtUpd.setInt(4, replOrderLineNo);
							updCnt = pstmtUpd.executeUpdate();
							pstmtUpd.clearParameters();
						}
						//Changed By Pragyan To update only on valid Pick Orders AND FN_IS_VALID_PICK_ORDER(P.PICK_ORDER) = 'Y'
						
						updateSql = "UPDATE PICK_ORD_DET SET LOC_CODE = ?, LOT_SL = ?, lot_sl__new = ?  "
							+ " WHERE PICK_ORDER = ? AND LINE_NO = ? ";
						updateSql = "UPDATE PICK_ORD_DET SET LOC_CODE = ?, LOT_SL = ?, lot_sl__new = ?  "
							+ " WHERE REPL_ORDER = ? AND LINE_NO__REPL = ? ";
						updateSql = "UPDATE PICK_ORD_DET SET LOC_CODE = ?, LOT_SL = ?, lot_sl__new = ?  "
							+ " WHERE REPL_ORDER = ? AND LINE_NO__REPL = ? AND FN_IS_VALID_PICK_ORDER(PICK_ORDER) = 'Y'";
						
						pstmtUpd = conn.prepareStatement(updateSql);
						pstmtUpd.setString(1, locCodeTo);
						pstmtUpd.setString(2, lotSlNew);
						pstmtUpd.setString(3, lotSlNew);
						/*pstmtUpd.setString(4, pickOrder);
						pstmtUpd.setInt(5, lineNoPick);	*/			
						/*pstmtUpd.setString(4, replOrder);
						pstmtUpd.setInt(5, replOrderLineNo);	
					}*/
					
					updateSql = "UPDATE REPL_ORD_DET SET LOC_CODE = ? , LOT_SL = ?  "
						+ " WHERE PICK_ORDER = ? AND LINE_NO__PICK = ? ";
					pstmtUpd = conn.prepareStatement(updateSql);
					pstmtUpd.setString(1, locCodeTo);
					pstmtUpd.setString(2, lotSlNew);
					pstmtUpd.setString(3, replOrder);
					pstmtUpd.setInt(4, replOrderLineNo);		
					updCnt = pstmtUpd.executeUpdate();
					pstmtUpd.close(); pstmtUpd = null;
					if( updCnt > 0 )
					{
						System.out.println(replOrder+ " repl_order is updated successfully");
					}
					
					updateSql = "UPDATE PICK_ORD_DET SET LOC_CODE = ?, LOT_SL = ?, lot_sl__new = ?  "
							+ " WHERE REPL_ORDER = ? AND LINE_NO__REPL = ? AND FN_IS_VALID_PICK_ORDER(PICK_ORDER) = 'Y'";
						
					pstmtUpd = conn.prepareStatement(updateSql);
					pstmtUpd.setString(1, locCodeTo);
					pstmtUpd.setString(2, lotSlNew);
					pstmtUpd.setString(3, lotSlNew);						
					pstmtUpd.setString(4, replOrder);
					pstmtUpd.setInt(5, replOrderLineNo);
					updCnt = pstmtUpd.executeUpdate();
					pstmtUpd.close(); pstmtUpd = null;
					if( updCnt > 0 )
					{
						System.out.println(" pick order is updated successfully");
					}
					//Changed by sumit on 06/02/12 commenting condition. end
					//Changed by sumit on 06/02/12 no need below line start.
					/*if ( "E".equals(orderType) || "F".equals(orderType) || "G".equals(orderType) || "H".equals(orderType) )
					{
						System.out.println("updCnt REPL_ORD_DET = ["+updCnt+"]");
					}
					else
					{
						System.out.println("updCnt PICK_ORD_DET = ["+updCnt+"]");
					}*/
					//Changed by sumit on 06/02/12 no need below line end.
					////////////////
				}//while(rs.next())
				rs.close();rs = null;
				pstmt.close();pstmt = null;
				if ( retString == null || retString.trim().length() == 0 )
				{
					// //added by kunal mandhre on 06/JUN/12 - WM1ESUN007
					// updateSql = "update pick_ord_det  set lot_sl__new = ?  where pick_order = ? and line_no = ? ";
					// pstmt = conn.prepareStatement(updateSql);
					// pstmt.setString(2, lotSlNew);
					// pstmt.setString(3, replOrder);				
					// pstmt.setInt(3, lineNo);

					// updCnt = pstmt.executeUpdate();

					// if( updCnt > 0 )
					// {					
						// System.out.println( updCnt + " rows updated successfully in pick_ord_det" );
					// }	
					// pstmt.close();
					// pstmt = null;

					updateSql = "UPDATE REPL_ISS_HDR SET CONFIRMED = 'Y', CONF_DATE = ?, EMP_CODE__APRV = ?"
							+ " WHERE TRAN_ID = ? ";
					pstmt = conn.prepareStatement(updateSql);
					pstmt.setTimestamp(1, currDate);
					pstmt.setString(2, empCode);
					pstmt.setString(3, tranId);				

					updCnt = pstmt.executeUpdate();

					if( updCnt > 0 )
					{					
						System.out.println( updCnt + " rows updated successfully" );
					}	
					pstmt.close();pstmt = null;
					
					//changed by Rohan on 21-03-13 for update actual qty to zero when scan is confirm.start
					
					sql = "SELECT R.REPL_ORDER,R.LINE_NO__ORD,L.INV_STAT FROM REPL_ISS_DET R,LOCATION L"
						  +" WHERE R.TRAN_ID = ? AND R.LOC_CODE = L.LOC_CODE AND L.INV_STAT NOT IN (?) ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, tranId);
					pstmt.setString(2, dsoInvstat);	
					rs = pstmt.executeQuery();
					
					while(rs.next())
					{
						replOrder1 = rs.getString("REPL_ORDER");
						lineNo1 = rs.getInt("LINE_NO__ORD");
						invState = rs.getString("INV_STAT");
						
						if(invState.trim().equalsIgnoreCase(invPndReserve.trim()))
						{
							updateSql = "UPDATE REPL_ORD_DET SET ACTUAL_QTY = 0 WHERE (REPL_ORDER,LINE_NO) IN(SELECT PICK_ORDER,LINE_NO__PICK FROM "
										+" REPL_ORD_DET WHERE REPL_ORDER = ? AND LINE_NO = ? )"; 
						}
						else
						{	
							updateSql = "UPDATE REPL_ORD_DET SET ACTUAL_QTY = 0 WHERE REPL_ORDER = ? AND LINE_NO = ? ";
						}	
						pstmt1 = conn.prepareStatement(updateSql);
						pstmt1.setString(1,replOrder1);
						pstmt1.setInt(2,lineNo1);
						updCnt = pstmt1.executeUpdate();
						if(updCnt > 0)
						{
							System.out.println("Repl Order"+replOrder1+"Line no"+lineNo1+"Update with actual qty 0");
						}
						pstmt.clearParameters();
						
					}
					
					if(pstmt1 != null)
					{
						pstmt1.close();
						pstmt1 = null;
					}
					if(pstmt != null)
					{
						pstmt.close();
						pstmt = null;
					}
					if(rs != null)
					{
						rs.close();
						rs= null;
					}
					//changed by Rohan on 21-03-13 for  update actual qty to zero when scan is confirm.end

					
					//update the status of repl order in wave_task_det table
					//Changed by sumit on 31/07/12 updating wave_status  
					//updateSql = "UPDATE WAVE_TASK_DET SET STATUS = 'Y' WHERE REF_ID = ? ";
					updateSql = "UPDATE WAVE_TASK_DET SET STATUS = 'Y', WAVE_STATUS = 'V' WHERE REF_ID = ? ";
					pstmt = conn.prepareStatement(updateSql);
					pstmt.setString(1, replOrder);	

					updCnt = pstmt.executeUpdate();

					if( updCnt > 0 )
					{					
						System.out.println( updCnt + " rows updated successfully" );
					}	
					pstmt.close();pstmt = null;
					
					//Changed by sumit on 31/07/12 for updating wave_status for next task start.
					/*String WaveId = "";
					int lineNO = 0;*/
					updateSql = " UPDATE WAVE_TASK_DET SET WAVE_STATUS = 'C' WHERE STATUS = ? AND REF_ID IN (SELECT PICK_ORDER FROM REPL_ORD_DET WHERE REPL_ORDER = ? ) ";
					pstmt = conn.prepareStatement(updateSql);
					pstmt.setString(1, "N");
					pstmt.setString(2, replOrder);						
					updCnt = pstmt.executeUpdate();						
					if( updCnt > 0 )
					{					
						System.out.println( updCnt + " wave_task rows updated successfully" );
					}	
					pstmt.close();pstmt = null;	
					
					//Changed by sumit on 11/09/12 updating wave_status of pick_order of wave_task_det start.
					//updateSql = " UPDATE WAVE_TASK_DET SET WAVE_STATUS = 'C' WHERE STATUS = ? AND REF_ID IN (SELECT REPL_ORDER FROM REPL_ORD_DET WHERE PICK_ORDER = ? )";
					/*pstmt = conn.prepareStatement(updateSql);
					pstmt.setString(1, "N");
					pstmt.setString(2, replOrder);					
					updCnt = pstmt.executeUpdate();						
					if( updCnt > 0 )
					{					
						System.out.println( updCnt + " wave_task rows updated successfully" );
					}	
					pstmt.close();pstmt = null;*/
					String updReplOrd = "";
					updateSql = " UPDATE WAVE_TASK_DET SET WAVE_STATUS = 'C' WHERE STATUS = ? AND REF_ID = ? ";
					pstmt = conn.prepareStatement(updateSql);
					
					updateSql = "SELECT REPL_ORDER FROM REPL_ORD_DET WHERE PICK_ORDER = ?";
					PreparedStatement replPstmt = null;
					replPstmt = conn.prepareStatement(updateSql);
					replPstmt.setString(1, replOrder);
					rs = replPstmt.executeQuery();
					while( rs.next())
					{						
						updReplOrd = rs.getString(1);
						replStatus = isAllDependentTaskConfForRepl( updReplOrd, conn);
						updCnt = 0;
						if("Y".equalsIgnoreCase(replStatus))
						{
							pstmt.setString(1, "N");
							pstmt.setString(2, updReplOrd);	
							updCnt = pstmt.executeUpdate();
							pstmt.clearParameters();
						}
						if( updCnt > 0 )
						{					
							System.out.println( updCnt + " wave_task rows updated successfully" );
						}
					}
					if( rs != null )
					{
						rs.close(); rs = null;
					}
					if( replPstmt!= null)
					{
						replPstmt.close();
						replPstmt = null;
					}
					if( pstmt != null )
					{
						pstmt.close(); pstmt = null;
					}
					
					//Changed By Pragyan 28/02/13 To check all the pick status
					
					/*updateSql = " UPDATE WAVE_TASK_DET SET WAVE_STATUS = 'C' WHERE STATUS = ? AND REF_ID IN (SELECT PICK_ORDER FROM PICK_ORD_DET WHERE REPL_ORDER = ? )";
					pstmt = conn.prepareStatement(updateSql);
					pstmt.setString(1, "N");
					pstmt.setString(2, replOrder);					
					//Changed by sumit on 21/02/13 if all repl_order confirmed then updating start
					//updCnt = pstmt.executeUpdate();
					updCnt = 0;
					replStatus = isAllDependentTaskConf(replOrder, conn);
						
					if("Y".equalsIgnoreCase(replStatus))
					{
						updCnt = pstmt.executeUpdate();
					}
					//Changed by sumit on 21/02/13 if all repl_order confirmed then updating end					
					if( updCnt > 0 )
					{					
						System.out.println( updCnt + " wave_task rows updated successfully" );
					}	*/
					updateSql = "  UPDATE WAVE_TASK_DET SET WAVE_STATUS = 'C' WHERE STATUS = ? AND REF_ID = ?";
					PreparedStatement updPstmt = conn.prepareStatement(updateSql);
					
					String updPickOrd = "";
					sql = "SELECT PICK_ORDER FROM PICK_ORD_DET WHERE REPL_ORDER = ?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,replOrder);
					rs = pstmt.executeQuery();
					while( rs.next() )
					{
						updPickOrd = rs.getString(1);
						replStatus = isAllDependentTaskConf(replOrder, updPickOrd, conn);
						if("Y".equalsIgnoreCase(replStatus))
						{
							updPstmt.setString(1, "N");
							updPstmt.setString(2, updPickOrd);	
							updCnt = updPstmt.executeUpdate();
							updPstmt.clearParameters();
						}
						if( updCnt > 0 )
						{					
							System.out.println( updCnt + " wave_task rows updated successfully" );
						}
					}
					if( rs != null)
					{
						rs.close();
						rs = null;
					}
					if(pstmt != null)
					{
						pstmt.close();
						pstmt = null;
					}
					//pstmt.close();pstmt = null;	
					//Changed by sumit on 11/09/12 updating wave_status of pick_order of wave_task_det end.
					
					updCnt= 0;
					updCnt = waveStatusUpdate(pickOrder, conn);
					if( updCnt > 0)
					{
						System.out.println( updCnt + " row of wave_status updated successfully" );
					}					
					//Changed by sumit on 31/07/12 for updating wave_status for next task end.
					
					//change done by kunal on 09/08/12 consolidateToDoc called as per S Manoharan Sir intruction
					
					sql = "select ptcn from wave_task_det where ref_id = ?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,replOrder);
					rs = pstmt.executeQuery();
					if( rs.next() )
					{
						ptcn = rs.getString("ptcn") == null?"":rs.getString("ptcn");
					}
					if( rs != null)
					{
						rs.close();
						rs = null;
					}
					if(pstmt != null)
					{
						pstmt.close();
						pstmt = null;
					}
					if(pstmtUpd != null)
					{
						pstmtUpd.close();
						pstmtUpd = null;
					}
					
					//Changed By Pragyan WM3DSUN015 To update status in Sorder as InProcess after start scaning .start
					sql = "UPDATE SORDER SET SHIP_STATUS = ? WHERE SALE_ORDER = ?";
					pstmtUpd = conn.prepareStatement(sql);
					int updateSorder = 0;
					pstmtUpd.setString(1, "P");
					pstmtUpd.setString(2, saleOrder);
					updateSorder = pstmtUpd.executeUpdate();
					
					if(updateSorder > 0)
					{
						System.out.println(" SORDER UPDATE COUNT ["+updateSorder+"]");
					}
					pstmtUpd.clearParameters();
					
					if(pstmtUpd != null)
					{
						pstmtUpd.close();
						pstmtUpd = null;
					}
					//Changed By Pragyan WM3DSUN015 To update status in Sorder as InProcess after after start scaning .end
					
					
					ConsolidatToDoc conToDoc = new ConsolidatToDoc();
					//Changed by Rohan on 10-06-12 to add xtraParams in process as argument for hazmap report to get chg user
					//retString =  conToDoc.process(ptcn, conn);
					retString = conToDoc.process(ptcn, conn,xtraParams);
					if ( retString != null && retString.trim().length() > 0 )
					{
						isError = true;
						return retString;
					}
					System.out.println("ret string :"+retString);
					
					//Changed by sumit on 08/12/12 deleting record of sordalloc if aty_alloc is <= 0 start
					if( retString == null || retString.trim().length() == 0)
					{						
						sql = " DELETE FROM SORDALLOC WHERE SALE_ORDER = ? AND QTY_ALLOC <= 0 ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, saleOrder);
						updCnt = pstmt.executeUpdate();
						if( updCnt > 0 )
						{
							System.out.println(" record deleted successfully "+updCnt);
						}
						if(pstmt != null)
						{
							pstmt.close();
							pstmt = null;
						}
						
					}
					//Changed by sumit on 08/12/12 deleting record of sordalloc if aty_alloc is <= 0 end
					//end kunal
				}
			}
		}
		catch( Exception e )
		{
			isError = true;
			System.out.println("manohar 18/09/12 exception [" + e.getMessage() + "]");
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				stkUpdate = null;
				invAllocTrace = null;
				System.out.println("manohar 18/09/12 conStat [" + conStat + "] isError [" + isError + "]");
				if( !isError && conStat == true )
				{
					conn.commit();// For committing the transaction update
					retString = itmDBAccess.getErrorString("","REPISSCONF","");
				}
				//changed by chaitali 12/11/11
				//else 
				else if(conStat == true || isError)
				{
					conn.rollback();

				}
				if(rs != null )
				{
					rs.close();
					rs = null;
				}
				if(pstmt != null )
				{
					pstmt.close();
					pstmt = null;
				}
				if(pstmtStock != null )
				{
					pstmtStock.close();
					pstmtStock = null;
				}
				if( conn != null && conStat == true )
				{
					conn.close();
					conn = null;
				}
			}
			catch(Exception e)
			{
				e.printStackTrace();
				throw new ITMException(e);
			}
		}
		return retString;
	}
	private String checkNullAndTrim( String inputVal )
	{
		if ( inputVal == null )
		{
			inputVal = "";
		}
		else
		{
			inputVal = inputVal.trim();
		}
		return inputVal;
	}
	
	//Changed by sumit on 10/08/12 update wave_status after all task completed for wave_id and sale order start.
	private int waveStatusUpdate(String replOrder, Connection conn) throws ITMException
	{
		String sql = "", sql1 = "";
		String waveId = "";
		String saleOrder = "";
		PreparedStatement pstmt = null, pstmt1 = null;
		ResultSet rs = null, rs1 = null;
		int updateCount = 0;
		int firstCount = 0, secCount = 0;
		
		
		try
		{
			sql = "SELECT WAVE_ID, SALE_ORDER FROM REPL_ORD_HDR WHERE REPL_ORDER = ? ";			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, replOrder);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				waveId = rs.getString("WAVE_ID");
				saleOrder = rs.getString("SALE_ORDER");	
				
				sql1 = "SELECT COUNT(*) AS CONT FROM WAVE_TASK_DET WHERE WAVE_ID = ? AND SALE_ORDER = ? AND REF_SER <> 'S-DSP' ";
				pstmt1 = conn.prepareStatement(sql1);
				pstmt1.setString(1, waveId);
			    pstmt1.setString(2, saleOrder);
			    rs1 = pstmt1.executeQuery();
			    if(rs1.next())
			    {
			    	firstCount = rs1.getInt("CONT");
			    }
			    rs1.close();rs1 = null;
			    pstmt1.close();pstmt1 = null;
				
			    //Changed by sumit on 19/03/13 changing condition
			    //sql1 = "SELECT COUNT(*) AS CONT FROM WAVE_TASK_DET WHERE WAVE_STATUS = 'V' AND WAVE_ID = ? AND SALE_ORDER = ? AND REF_SER <> 'S-DSP' ";
			    sql1 = "SELECT COUNT(*) AS CONT FROM WAVE_TASK_DET WHERE STATUS = 'Y' AND WAVE_ID = ? AND SALE_ORDER = ? AND REF_SER <> 'S-DSP' ";
				pstmt1 = conn.prepareStatement(sql1);
				pstmt1.setString(1, waveId);
			    pstmt1.setString(2, saleOrder);
			    rs1 = pstmt1.executeQuery();
			    if(rs1.next())
			    {
			    	secCount = rs1.getInt("CONT");
			    }
			    rs1.close();rs1 = null;
			    pstmt1.close();pstmt1 = null;
				
				
			}
			rs.close();rs = null;
			pstmt.close();pstmt = null;
			
			if( secCount == firstCount)
			{
				sql = "UPDATE WAVE_TASK_DET SET WAVE_STATUS = 'C' WHERE WAVE_ID = ? AND SALE_ORDER = ? AND REF_SER = 'S-DSP' ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, waveId);
				pstmt.setString(2, saleOrder);
				updateCount = pstmt.executeUpdate();					
				pstmt.close();pstmt = null;
			}
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		return updateCount;
	}
	//Changed by sumit on 10/08/12 update wave_status after all task completed for wave_id and sale order end.
	
	//Changed by sumit on 
	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");
				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");				
				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 21/02/13 checking whether all repl is confirmed or not start.
	private String isAllDependentTaskConf(String replOrder, Connection conn) throws ITMException
	{
		String sql = "";
		String status = "";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try
		{
			sql = " SELECT STATUS, REF_ID FROM WAVE_TASK_DET  WHERE REF_ID IN (" +
				  " SELECT DISTINCT REPL_ORDER FROM PICK_ORD_DET WHERE PICK_ORDER IN (" +
				  " SELECT PICK_ORDER FROM PICK_ORD_DET WHERE REPL_ORDER = ? ) AND REPL_ORDER IS NOT NULL)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, replOrder);
			rs = pstmt.executeQuery();
			while(rs.next())
			{
				status = rs.getString("STATUS");
				System.out.println(" status of repl_id ["+rs.getString("REF_ID")+"] is ["+status+"]");
				if( "N".equalsIgnoreCase(status))
				{
					return status;
				}
			}
			rs.close(); rs= null;
			pstmt.close(); pstmt = null;			
				
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if(pstmt != null)
				{
					pstmt.close(); pstmt = null;
				}
				if( rs != null)
				{
					rs.close(); rs = null;
				}				
			}
			catch(Exception ex)
			{
				ex.printStackTrace();
				throw new ITMException(ex);
			}
		}
		return status;
	}
	//Changed by sumit on 21/02/13 checking whether all repl is confirmed or not end.
*/
	
//Changed by sumit on 21/02/13 checking whether all repl is confirmed or not start.
	private String isAllDependentTaskConf(String replOrder,String pickOrder , Connection conn) throws ITMException
	{
		String sql = "";
		String status = "";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try
		{
			sql = " SELECT STATUS, REF_ID FROM WAVE_TASK_DET  WHERE REF_ID IN (" 
				+ " SELECT  REPL_ORDER FROM PICK_ORD_DET WHERE PICK_ORDER = ?)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, pickOrder);
			rs = pstmt.executeQuery();
			while(rs.next())
			{
				status = rs.getString("STATUS");
				System.out.println(" status of repl_order ["+rs.getString("REF_ID")+"] is ["+status+"]");
				if( "N".equalsIgnoreCase(status))
				{
					return status;
				}
			}
			rs.close(); rs= null;
			pstmt.close(); pstmt = null;			
				
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if(pstmt != null)
				{
					pstmt.close(); pstmt = null;
				}
				if( rs != null)
				{
					rs.close(); rs = null;
				}				
			}
			catch(Exception ex)
			{
				ex.printStackTrace();
				throw new ITMException(ex);
			}
		}
		return status;
	}
	//Changed by sumit on 21/02/13 checking whether all repl is confirmed or not end.
	
	//Changed by sumit on 25/03/13 checking whether all repl is confirmed or not start.
	private String isAllDependentTaskConfForRepl(String pickOrder , Connection conn) throws ITMException
	{
		String sql = "";
		String status = "";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try
		{
			sql = " SELECT STATUS, REF_ID FROM WAVE_TASK_DET  WHERE REF_ID IN (" 
				+ " SELECT  PICK_ORDER FROM REPL_ORD_DET WHERE REPL_ORDER = ?)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, pickOrder);
			rs = pstmt.executeQuery();
			while(rs.next())
			{
				status = rs.getString("STATUS");
				System.out.println(" status of repl_order ["+rs.getString("REF_ID")+"] is ["+status+"]");
				if( "N".equalsIgnoreCase(status))
				{
					return status;
				}
			}
			rs.close(); rs= null;
			pstmt.close(); pstmt = null;			
				
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if(pstmt != null)
				{
					pstmt.close(); pstmt = null;
				}
				if( rs != null)
				{
					rs.close(); rs = null;
				}				
			}
			catch(Exception ex)
			{
				ex.printStackTrace();
				throw new ITMException(ex);
			}
		}
		return status;
	}
	//Changed by sumit on 25/03/13 checking whether all repl is confirmed or not end.
	public double getStockQuantity(String itemCode,String siteCode,String locCode,String lotNo,String lotSl,double replQty,boolean isActRepl ,double pickQty,Connection conn) throws Exception
	{
	
		PreparedStatement pstmt = null;
		ResultSet rs = null;
	    String sql = "";
	   
		double quantity = 0.0;
		double allocQty = 0.0;
		double stockQty = 0.0;
		double holdQty = 0.0;
		
		double sordAllocQty = 0.0;
		double activeReplQty = 0.0;
		double finalQty = 0.0;
		
		
		try
		{
			sql= "SELECT QUANTITY,ALLOC_QTY,(CASE WHEN STOCK.HOLD_QTY IS NULL THEN 0 ELSE STOCK.HOLD_QTY END) AS HOLD_QTY " +
				"  FROM STOCK WHERE ITEM_CODE = ? AND SITE_CODE = ? "
				 +" AND LOC_CODE = ? AND LOT_NO = ? AND LOT_SL = ?";
			pstmt =  conn.prepareStatement(sql);
			pstmt.setString(1, itemCode);
			pstmt.setString(2, siteCode);
			pstmt.setString(3, locCode);
			pstmt.setString(4, lotNo);
			pstmt.setString(5, lotSl);
			
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				quantity = rs.getDouble("QUANTITY");
				allocQty = rs.getDouble("ALLOC_QTY");
				holdQty = rs.getDouble("HOLD_QTY");
			}
			
			if(rs != null)
			{
				rs.close();
				rs = null;
			}
			if(pstmt != null)
			{
				pstmt.close();
				pstmt = null;
			}
			
			System.out.println("For deduction ["+replQty+"]");
			System.out.println("quantity["+quantity+"]allocQty["+allocQty+"]holdQty["+holdQty+"]");
			/*System.out.println("For Result ["+(quantity - (allocQty - qtyAllocate))+"]");
			stockQty = quantity - (allocQty - qtyAllocate);
			System.out.println("For stockQty ["+stockQty+"]");
			stockQty = stockQty - holdQty;*/
			
			sql= "SELECT SUM(QTY_ALLOC) as QTY_ALLOC FROM SORDALLOC WHERE ITEM_CODE = ? AND SITE_CODE = ? AND LOC_CODE = ? AND LOT_NO = ? AND LOT_SL = ?";
			pstmt =  conn.prepareStatement(sql);
			pstmt.setString(1, itemCode);
			pstmt.setString(2, siteCode);
			pstmt.setString(3, locCode);
			pstmt.setString(4, lotNo);
			pstmt.setString(5, lotSl);
			
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				sordAllocQty = rs.getDouble("QTY_ALLOC");
			}
			
			System.out.println("sordAllocQty["+sordAllocQty+"]");
			finalQty = quantity - (allocQty - sordAllocQty);
			
			
			if(rs != null)
			{
				rs.close();
				rs = null;
			}
			if(pstmt != null)
			{
				pstmt.close();
				pstmt = null;
			}
			
			if(isActRepl)
			{
				System.out.println("Active repl stock["+quantity+"]Alloc Qty["+allocQty+"]replQty["+replQty+"]sordAllocQty["+sordAllocQty+"]pickQty["+pickQty+"]");
				//Changed By Pragyan to proper handle last case.start
				if(quantity == replQty)
				{
					//sordAllocQty = quantity - allocQty ;
					System.out.println("Inside repl quantity is equal");
					sordAllocQty = allocQty ;
				}
				//else if((sordAllocQty == allocQty) && (replQty - pickQty) > 0)
				else if((quantity == sordAllocQty) && (replQty - pickQty) > 0)
				{
					System.out.println("Inside repl quantity is equal sordAllocQty and (replQty - pickQty) > 0");
					sordAllocQty = replQty ;
				}
				//else if((sordAllocQty == allocQty) && (replQty - pickQty) > 0)
				else if(((quantity - allocQty) <  replQty) && (replQty - pickQty) > 0)
				{
					System.out.println("Inside repl quantity (quantity - allocQty) <  replQty");
					sordAllocQty = pickQty + ( (replQty -(pickQty + (quantity - allocQty)) ));
					System.out.println("Inside repl quantitypickQty + ( (replQty -(pickQty + (quantity - allocQty))["+sordAllocQty+"]");
				}
				else
				{
					sordAllocQty = 0.0;
				}
				//Changed By Pragyan to proper handle last case.end
			}
			
			System.out.println("sordAllocQty["+sordAllocQty+"]");
			/*sql = "SELECT SUM(D.QUANTITY) AS QUANTITY FROM REPL_ORD_DET D, REPL_ORD_HDR H WHERE D.ITEM_CODE = ? " 
					+" AND D.SITE_CODE = ? AND D.LOC_CODE = ? AND D.LOT_NO = ? AND D.LOT_SL = ? "
    				+" AND CASE WHEN CANCEL_MODE IS NULL THEN 'N' ELSE CANCEL_MODE END <> 'Y'  "
    				+" AND H.REPL_ORDER = D.REPL_ORDER AND H.ORDER_TYPE IN('Q','T','P','E','I') ";
			pstmt =  conn.prepareStatement(sql);
			pstmt.setString(1, itemCode);
			pstmt.setString(2, siteCode);
			pstmt.setString(3, locCode);
			pstmt.setString(4, lotNo);
			pstmt.setString(5, lotSl);
			
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				activeReplQty = rs.getDouble("QUANTITY");
			}
			
			System.out.println("activeReplQty["+activeReplQty+"]");
			finalQty = quantity - activeReplQty;
			System.out.println("finalQty["+finalQty+"]");*/
			
			if(rs != null)
			{
				rs.close();
				rs = null;
			}
			if(pstmt != null)
			{
				pstmt.close();
				pstmt = null;
			}
					
        }
		catch(Exception e)
		{
			throw e;
		}
		finally
		{
			try
			{
				if(rs != null)
				{
					rs.close();
					rs = null;
				}
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
			}
			catch(Exception e2)
			{
				throw new ITMException(e2);
			}
		
		}
	return sordAllocQty;  	
	}
	
	//Changed By Pragyan 17-APR-14 To return error string for Hold Qty from the stock.start
	private  String getError(String siteCode,String lotNo,String lotSl,String itemCode,String locCode,String Code,Connection conn)  throws ITMException, Exception
	{
	    String mainStr ="";
	    
	    try
	    {
	    	String errString = "";
	    	errString =  new ITMDBAccessEJB().getErrorString("",Code,"","",conn);
			String begPart = errString.substring(0,errString.indexOf("<message>")+9);
			String endDesc = errString.substring(errString.indexOf("</description>"));
			mainStr= begPart+"Inventory is on hold"+"</message><description>";
			mainStr= mainStr+"Hold Quantity present in Site [" + siteCode + "] Lot No ["+lotNo+"] and Lot Sl ["+lotSl+"] Item Code["+itemCode+"] Location ="+locCode+" "+endDesc;
			System.out.println("mainStr:::::::::::::::::: "+mainStr);
			begPart = null;
	    }
	    catch(Exception e)
	    {
	        e.printStackTrace();
	        throw new ITMException(e);
	    }
	    return mainStr;
	}
	//Changed By Pragyan 17-APR-14 To return error string for Hold Qty from the stock.end
}
