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 = "";
		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;
		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

		try
		{

			/*-- commented by chaitali 12/11/11
			ConnDriver connDriver = null;
			connDriver = new ConnDriver();
			connectionObject = connDriver.getConnectDB("DriverITM");
			 */
			System.out.println("====>extraparams((" +xtraParams+ "))");

			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 "
						+ "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" ));
					//Changed by sumit sarkar on 25/07/12 getting actual value from repl_ord_det
					actualQty = rs.getInt( "ACTUAL_QTY" );
					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");
					// end 10/10/12 manoharan
					// 16/10/11 manoharan check whether allocation against sales order is there
					// if allocation against sales order found change allocation to point to loc_code__to
					// and deallocate the same for loc_code in stock
					// update sordalloc with loc_code__to
					// This utility methods to update old and new location should be in 
					// a separate probably in stockupdate so that we can call the same from various places 
					//
					// 
					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 ;
					}
					replOrderLineNo  = rs.getInt("ORDER_lINE_NO");
					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"));
					}
					rsStock.close(); rsStock = null;
					pstmtStock.clearParameters();

					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);
					//End changes by gulzar on 12/24/2011

					// 24/06/12 manoharan active deallocation should be actual issue should be shipperSize
					//Changed by sumit on 27/07/12 added 'Q' orderType Condition 
					//if ("A".equals(orderType) || "E".equals(orderType) || "I".equals(orderType) || "Q".equals(orderType) || "K".equals(orderType) || "J".equals(orderType) )
					//if ("A".equals(orderType) || "E".equals(orderType) || "I".equals(orderType) || "K".equals(orderType)  )
					//if ("A".equals(orderType) || "E".equals(orderType) || "I".equals(orderType))
					if ("A".equals(orderType))
					{
						//Changed by sumit sarkar on 25/07/12 provide pick_qty for 'E' start.
						//if( "E".equals(orderType) || "K".equals(orderType) )
						/*if( "E".equals(orderType))
						{
							sql = 	"SELECT PICK_ORDER, LINE_NO  FROM REPL_ORD_DET WHERE REPL_ORDER = " +
									" (SELECT PICK_ORDER FROM REPL_ORD_DET WHERE REPL_ORDER = ? ) ";
					
							pstmt1 = conn.prepareStatement(sql);
							pstmt1.setString(1, replOrder);
							rs1 = pstmt1.executeQuery();
							if( rs1.next())
							{
								pickOrder = rs1.getString("PICK_ORDER");
								lineNoPick = rs1.getInt( "LINE_NO" );
							}
							System.out.println("pickOrder - >["+pickOrder+"] lineNoPick - >["+lineNoPick+"]");
							pstmt1.close();
							pstmt1 = null;
							rs1.close();
							rs1 = null;
						}						*/
						//Changed by sumit sarkar on 25/07/12 provide pick_qty for 'E' end.	
						
						sql = "SELECT quantity from pick_ord_det where pick_order = ? and line_no = ? ";
						pstmt1 = conn.prepareStatement(sql);
						pstmt1.setString(1, pickOrder);
						pstmt1.setInt(2,lineNoPick);
						rs1 = pstmt1.executeQuery();
						if(rs1.next())
						{
							pickQty = rs1.getDouble(1);
						}
						pstmt1.close();
						pstmt1 = null;
						rs1.close();
						rs1 = null;
						// 10/10/12 manoharan active quantity will be less than shipper_size set no_art as 1 and weight as one case weight
						shipperSize = (Double) itmVolumeMap.get("SHIPPER_SIZE");					
						noArt = 1;
						updateRowMap.put("no_art", noArt);
						updateRowMap.put("gross_weight", caseGrossWeight);
						updateRowMap.put("net_weight", caseNetWeight);
						updateRowMap.put("tare_weight", caseGrossWeight - caseNetWeight);
						// end 10/10/12 manoharan
						
						updateRowMap.put("tran_type","ID"); 
						updateRowMap.put("qty_stduom", ""+ pickQty );
						updateRowMap.put("quantity", ""+ pickQty);
						retString = stkUpdate.updateStock( updateRowMap, xtraParams, conn );
						if ( retString != null && retString.trim().length() > 0 )
						{
							isError = true;
							break;
						}
						// 10/10/12 manoharan active quantity will be less than shipper_size set no_art as 1 and weight as one case weight
						shipperSize = (Double) itmVolumeMap.get("SHIPPER_SIZE");					
						noArt = Math.floor((quantity - pickQty) / 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);
						// end 10/10/12 manoharan
						updateRowMap.put("tran_type","I"); 
						updateRowMap.put("qty_stduom", ""+ (quantity - pickQty));
						updateRowMap.put("quantity", ""+(quantity - pickQty));				
						
						System.out.println(" quantity -> ["+quantity+"] pickQty - >["+pickQty+"]");
					}
					//Changed by sumit on 27/07/12 added condition for order type 'G' start.
					//else if ("G".equals(orderType) || "F".equals(orderType) || "J".equals(orderType) || "Q".equals(orderType))
					//else if ("G".equals(orderType) || "F".equals(orderType) || "J".equals(orderType) || "Q".equals(orderType) || "C".equals(orderType) || "T".equals(orderType))
					//else if ("G".equals(orderType) || "F".equals(orderType) || "J".equals(orderType) || "Q".equals(orderType) || "C".equals(orderType) || "T".equals(orderType) || "K".equals(orderType) || "M".equals(orderType))
					else if ("G".equals(orderType) || "F".equals(orderType) || "J".equals(orderType) || "Q".equals(orderType) || "C".equals(orderType) || "T".equals(orderType) || "K".equals(orderType) || "M".equals(orderType) || "I".equals(orderType) || "E".equals(orderType) || "P".equals(orderType))
					{		
						
						//if("J".equals(orderType) || "K".equals(orderType) )
						if("J".equals(orderType) || "K".equals(orderType) || "I".equals(orderType))
						{
							System.out.println(" inside 'J' OR K block and actualQty ["+actualQty+"]");
							//Changed by sumit on 28/08/12 modifide sql.
							/*sql = 	"SELECT ACTUAL_QTY FROM REPL_ORD_DET WHERE PICK_ORDER = ? " +
									" AND LINE_NO__PICK =? ";*/
							
							sql = 	"SELECT ACTUAL_QTY FROM REPL_ORD_DET WHERE REPL_ORDER = ? " +
							" AND LINE_NO =? ";
							
					
							pstmt1 = conn.prepareStatement(sql);
							/*pstmt1.setString(1, replOrder);
							pstmt1.setInt(2, replOrderLineNo);*/
							pstmt1.setString(1, pickOrder);
							pstmt1.setInt(2, lineNoPick);
							rs1 = pstmt1.executeQuery();
							if( rs1.next())
							{
								actualQty = rs1.getInt( "ACTUAL_QTY" );
							}
							System.out.println("actualQty - inside 'J' OR 'K' >["+actualQty+"]");
							if(pstmt1 != null)
							{
								pstmt1.close();
								pstmt1 = null;
							}
							
							if(rs1 != null)
							{
								rs1.close();
								rs1 = null;
							}
						}
						
					
						System.out.println(" inside 'G' and actualQty ["+actualQty+"]");
						//Changed by sumit sarkar on 25/07/12 provide pick_qty for 'k' end.
						updateRowMap.put("tran_type","ID"); 
						updateRowMap.put("qty_stduom", ""+  (quantity - actualQty));//140
						updateRowMap.put("quantity", ""+ (quantity - actualQty));
						// 10/10/12 commented by manoharan
						////Changed by sumit on 27/09/12 start.						
						//shipperSize = (Double) itmVolumeMap.get("SHIPPER_SIZE");						
						//noArt = quantity/ shipperSize;					
						//updateRowMap.put("no_art", noArt);						
						////Changed by sumit on 27/09/12 end.
						noArt = Math.floor((quantity - 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);
						// end 10/10/12  manoharan
						retString = stkUpdate.updateStock( updateRowMap, xtraParams, conn );
						if ( retString != null && retString.trim().length() > 0 )
						{
							isError = true;
							break;
						}
						updateRowMap.put("tran_type","I"); 
						updateRowMap.put("qty_stduom", ""+ actualQty);//360
						updateRowMap.put("quantity", ""+actualQty);
						// 10/10/12 manoharan weight and no_art to be set properly
						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);
						// end 10/10/12  manoharan
					}
					/*else if("J".equals(orderType))
					{
							
							
							updateRowMap.put("tran_type","ID"); 
							updateRowMap.put("qty_stduom", ""+  (quantity - actualQty));//140
							updateRowMap.put("quantity", ""+ (quantity - actualQty));
							retString = stkUpdate.updateStock( updateRowMap, xtraParams, conn );
							if ( retString != null && retString.trim().length() > 0 )
							{
								isError = true;
								break;
							}
							updateRowMap.put("tran_type","I"); 
							updateRowMap.put("qty_stduom", ""+ actualQty);//360
							updateRowMap.put("quantity", ""+actualQty);
					}*/
					//Changed by sumit on 27/07/12 added condition for order type 'G' end.
					else
					{
						updateRowMap.put("tran_type","ID"); 
						updateRowMap.put("qty_stduom", ""+quantity);
						updateRowMap.put("quantity", ""+quantity);
						// 10/10/12 manoharan weight and no_art to be set properly
						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);
						// end 10/10/12  manoharan
					}
					// end 24/06/12 manoharan
					// 10/10/12 manoharan comented
					////Changed by sumit on 27/09/12 start.					
					//shipperSize = (Double) itmVolumeMap.get("SHIPPER_SIZE");					
					//noArt = quantity/ shipperSize;					
					//updateRowMap.put("no_art", noArt);					
					////Changed by sumit on 27/09/12 end.					
					
					retString = stkUpdate.updateStock( updateRowMap, xtraParams, conn );
					if ( retString != null && retString.trim().length() > 0 )
					{
						isError = true;
						break;
					}

					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
					// 10/10/12 manoharan  commented and new code added
					////Changed by sumit on 27/09/12 start.					
					//shipperSize = (Double) itmVolumeMap.get("SHIPPER_SIZE");					
					//noArt = quantity/ shipperSize;					
					//updateRowMap.put("no_art", noArt);					
					////Changed by sumit on 27/09/12 end.
					// 10/10/12 manoharan weight and no_art to be set properly
					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);
					// end 10/10/12  manoharan
					
					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");

					// 24/06/12 manoharan active deallocation should be actual issue should be shipperSize
					//Changed by sumit sarkar on 27/07/12 changing condition 	
					//if ("A".equals(orderType) || "E".equals(orderType) || "I".equals(orderType) )
					//if ("A".equals(orderType) || "E".equals(orderType) || "I".equals(orderType)  || "K".equals(orderType)  )
					//if ("A".equals(orderType) || "E".equals(orderType) || "I".equals(orderType)    )
					if ("A".equals(orderType) )
					{
						strAllocate.put("alloc_qty",new Double(pickQty));
					}
					//Changed by sumit on 23/07/12separated  condition 'I' start.
					//else if ("I".equals(orderType) || "Q".equals(orderType) || "K".equals(orderType) )
					/*else if ("I".equals(orderType) || "Q".equals(orderType) || "K".equals(orderType)  )
					{	
						System.out.println(" for order type 'I' and 'Q' and 'k' ["+pickQty+"");
						strAllocate.put("alloc_qty",new Double(pickQty));						
					}*/
					//else if ("G".equals(orderType) || "F".equals(orderType) )
					//else if ("G".equals(orderType) || "F".equals(orderType) || "J".equals(orderType) || "Q".equals(orderType) || "C".equals(orderType) || "T".equals(orderType) || "K".equals(orderType) ||  "M".equals(orderType))
					else if ("G".equals(orderType) || "F".equals(orderType) || "J".equals(orderType) || "Q".equals(orderType) || "C".equals(orderType) || "T".equals(orderType) || "K".equals(orderType) ||  "M".equals(orderType) || "E".equals(orderType) || "I".equals(orderType) || "P".equals(orderType) )
					{
						strAllocate.put("alloc_qty",new Double(quantity - actualQty));
					}
					//Changed by sumit on 23/07/12separated  condition 'I' end.
					else
					{
						strAllocate.put("alloc_qty",new Double(quantity));
					}

					retString = invAllocTrace.updateInvallocTrace(strAllocate, conn);
					System.out.println("retString ::: " + retString);
					if (retString != null && retString.trim().length() > 0 )
					{
						isError = true;
						break;
					}
					strAllocate = null;
					// 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 ("A".equals(orderType) || "E".equals(orderType) || "Q".equals(orderType) || "P".equals(orderType))
					{
						//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 = ? ";*/

						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
						
							pstmtPick = conn.prepareStatement(sql);
							//pstmtPick.setString(1, saleOrder);
							//pstmtPick.setString(2, lineNoSord);				
							pstmtPick.setString(1, itemCode);				
							//pstmtPick.setString(2, locCodeTo);		
							//pstmtPick.setString(2, locCode);
							pstmtPick.setString(2, lotNo);				
							pstmtPick.setString(3, lotSl);				
							pstmtPick.setString(4, replOrder);	
							pstmtPick.setInt(5, replOrderLineNo);	
							rsPick = pstmtPick.executeQuery();
							
							if(rsPick.next())
							{
								pickQty = rsPick.getDouble("QUANTITY");
								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 = ? "
								updateSql = "UPDATE SORDALLOC SET   QTY_ALLOC = QTY_ALLOC - ? " //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.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+"]");
								
								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 ) VALUES (?, ?, ?, ?, "
									+ " ?, ?, ? , ?, ?, ?, ?, "
									+ " ?,?, ?, ?, ?, ?, ?, "
									+ " ?, ?, ? , ?, ?, ?) ";

								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);				

								updCnt = pstmtUpd.executeUpdate();

								System.out.println("manohar 18/09/12 sordalloc inserted  updCnt [" + updCnt + "]");
							}
							rsPick.close();
							rsPick = null;
							pstmtPick.close();
							pstmtPick = null;
						
						/*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" );
							}
							rs1.close();
							rs1 = null;
							pstmt1.close();
							pstmt1 = null;
							
							
							
						//updateSql = "UPDATE SORDALLOC SET  QUANTITY = quantity - ?, QTY_ALLOC = QTY_ALLOC - ? " //LOC_CODE = ? ,LOT_SL = ? "
						updateSql = "UPDATE SORDALLOC SET   QTY_ALLOC = QTY_ALLOC - ? " //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.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+"]");
						
						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 ) VALUES (?, ?, ?, ?, "
							+ " ?, ?, ? , ?, ?, ?, ?, "
							+ " ?,?, ?, ?, ?, ?, ?, "
							+ " ?, ?, ? , ?, ?, ?) ";

						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);				

						updCnt = pstmtUpd.executeUpdate();

						System.out.println("manohar 18/09/12 sordalloc inserted  updCnt [" + updCnt + "]");*/
						
					}
					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;
					}
					// 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 + "]");
					
					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);	
					}

					updCnt = pstmtUpd.executeUpdate();
					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+"]");
					}
					////////////////
				}
				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;

					//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;	
					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);					
					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 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;
					}
					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;
				
				sql1 = "SELECT COUNT(*) AS CONT FROM WAVE_TASK_DET WHERE WAVE_STATUS = 'V' 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;
	}
	//
}
