
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 ibase.utility.CommonConstants;

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.*;


@Stateless 
public class MultiplePtcnWizConf extends ActionHandlerEJB implements MultiplePtcnWizConfLocal, MultiplePtcnWizConfRemote
{
	boolean isError = false;
	
	GenericUtility genericUtility = GenericUtility.getInstance();
	String chgUser = "",chgTerm = "",empCode = "";
	
	public String confirm(String tranID,String xtraParams, String forcedFlag) throws RemoteException,ITMException
	{

		String retString = "";
		boolean isConn= false;
		Connection conn = null;		
		try
		{
			retString = confirm( tranID, xtraParams, forcedFlag, conn, isConn );
			System.out.println("retString after confirmed------>>["+retString+"]");			
			if ( retString != null && retString.length() > 0 )
			{
				throw new Exception("Exception while calling confirm for tran  Id:["+tranID+"] "+retString);
			}
		}
		catch(Exception exception)
		{
			System.out.println("Exception in [MultiplePtcnWizConf] getCurrdateAppFormat " + exception.getMessage());
			try {
				throw new Exception("Exception while calling confirm for tran  Id:["+tranID+"] "+retString);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return retString;
	}
	public String confirm( String tranId, String xtraParams,String forcedFlag, Connection conn, boolean connStatus ) throws Exception
	{
		System.out.println("@@@@@@@@ Call MultiplePtcnWizConf confirm Method  @@@@@@@@");
		ResultSet rs = null,rsStock = null,rs1 = null,rsSord = null;
		PreparedStatement pstmt = null,pstmtStock = null,pstmt1 = null,pstmt2 = null,pstmtUpd = null,pstmtSord = null;
		PreparedStatement pstmtUpdPickOrdDet =null;
		String sql = "",errorString = "",confirmed = "";
		String itemCode = "",siteCode = "",locCode = "",lotNo = "",lotSl = "",locCodeTo = "",saleOrder = "",
				lineNoSord = "",replOrder = "",pickType = "",dExpLev = "",remarks = "",dimension = "";
		String acctCodeDr = "",cctrCodeDr = "",packCode = "",siteCodeMfg = "",stagingLoc = "",
				packInstr = "",suppCodeMfg = "",unitAlt = "",batchNo = "",unit = "",grade = "",		
				expLev = "",itemCodeOrd = "",itemRef = "",status = "",itemGrade = "",waveId = "",retString = "",
				allocMode = "",reasCode = "",unitStd = "",sSQL = "",updateSql = "",ptcn = "",refSer = "";
		int lineNoOrd= 0,lineNo= 0,updCnt = 0,rowsCount = 0;  
		double quantity = 0,noArt =0,stkGrossRate = 0,convQtyStduom = 0,batchSize = 0,holdQuantity = 0,stkRate = 0;
		double shipperSize = 0,	itemGrossWeight = 0, caseGrossWeight = 0, itemNetWeight = 0, caseNetWeight = 0,
				qtyOrd = 0,sordAllocQty = 0;
		isError = false;		
		boolean isUpdatehdr= false;
		java.sql.Timestamp expDate = null,currDate = null, mfgDate = null,restestDate = null,dateAlloc = null;		

		HashMap updateRowMap = new HashMap();
		GenericUtility genericUtility = GenericUtility.getInstance();
		ITMDBAccessEJB itmDBAccess = new ITMDBAccessEJB();
		DistCommon distCommon = new DistCommon();
		StockUpdate stkUpdate = null;
		InvAllocTraceBean invAllocTrace = null;
		HashMap itmVolumeMap = new HashMap();
		HashMap strAllocate = null;
		HashMap <String,String>dataMap = new HashMap<String,String>();
		ArrayList<String> ptcnScanList = new ArrayList<String>();
		try
		{
			currDate = new java.sql.Timestamp( System.currentTimeMillis() );
			empCode = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginEmpCode" );	
			chgUser = checkNull(genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginCode"));			
			chgTerm = checkNull(genericUtility.getValueFromXTRA_PARAMS(xtraParams, "termId"));		
			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") ;

			confirmed = checkNull(getColumnDescr(conn, "confirmed", "multi_pick_iss", "tran_id", tranId));
			System.out.println("confirmed----->>["+confirmed+"]");
			confirmed = confirmed.length() == 0 ? "N" :confirmed;
			if("Y".equalsIgnoreCase(confirmed))
			{
				errorString = itmDBAccess.getErrorString("","PCKISSCONF","");
				return errorString;
			}
			else
			{
				stagingLoc = checkNull(distCommon.getDisparams( "999999", "IMEDIATE_LOC", conn));

				System.out.println("stagingLoc1212------>>["+stagingLoc+"]");
				stkUpdate = new StockUpdate();
				invAllocTrace = new InvAllocTraceBean();

				sql = "select acct_code__inv, cctr_code__inv, "
						+ "exp_date, pack_code, mfg_date, site_code__mfg, "
						+ "pack_instr, supp_code__mfg, retest_date, rate, "
						+ "gross_rate, conv__qty_stduom, unit__alt, batch_no, batch_size, unit, grade, remarks, "
						+ "dimension ,hold_qty from stock "
						+ "where item_code = ? "
						+ "and site_code = ? "
						+ "and loc_code = ? "
						+ "and lot_no = ? "
						+ "and lot_sl = ? ";

				pstmtStock = conn.prepareStatement(sql);

				sql = "SELECT O.LINE_NO LINE_NO__ORD,H.WAVE_ID WAVE_ID,R.PTCN PTCN,R.TRAN_ID,R.LINE_NO LINE_NO, R.ITEM_CODE ITEM_CODE, R.SITE_CODE SITE_CODE, "
						+ "R.LOC_CODE LOC_CODE, R.LOT_NO LOT_NO, R.LOT_SL LOT_SL, "
						+ "(R.QUANTITY - (CASE WHEN R.DEALLOC_QTY IS NULL THEN 0 ELSE R.DEALLOC_QTY END )) AS QUANTITY,"
						+ " R.NO_ART NO_ART,O.SALE_ORDER SALE_ORDER, O.LINE_NO__SORD LINE_NO__SORD, O.PICK_ORDER,H.PICK_TYPE,"
						+ "O.EXP_LEV EXP_LEV "
						+ " FROM MULTI_PICK_ISS_DET R,  PICK_ORD_DET O ,PICK_ORD_HDR H  WHERE R.TRAN_ID = ? "
						+ " AND R.PICK_ORDER = O.PICK_ORDER AND H.PICK_ORDER = O.PICK_ORDER "
						//+ " AND O.LINE_NO = R.LINE_NO__SORD "
						+ " AND R.ITEM_CODE = O.ITEM_CODE  "
						+ " AND (R.QUANTITY - (CASE WHEN R.DEALLOC_QTY IS NULL THEN 0 ELSE R.DEALLOC_QTY END )) > 0 ";
				//+ " AND R.LOC_CODE <> R.LOC_CODE__TO";

				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, tranId);
				rs = pstmt.executeQuery();

				while(rs.next())
				{
					if(dataMap != null && dataMap.size() > 0)
					{
						System.out.println("dataMap clear..............");
						dataMap.clear();
					}
					System.out.println("WHILE CONDITION12.........."+(++rowsCount));					
					lineNoOrd = rs.getInt( "LINE_NO__ORD" );
					waveId = checkNull(rs.getString( "WAVE_ID" ));
					ptcn = checkNull(rs.getString( "PTCN" ));
					itemCode = rs.getString( "ITEM_CODE" );
					siteCode = rs.getString( "SITE_CODE" );
					locCode = rs.getString( "LOC_CODE" );
					lotNo = rs.getString( "LOT_NO" );
					lotSl = rs.getString( "LOT_SL" );
					quantity = rs.getDouble( "QUANTITY" );
					//noArt = rs.getDouble( "NO_ART" );
					//locCodeTo = rs.getString( "loc_code__to" );
					saleOrder = rs.getString( "SALE_ORDER" );
					lineNoSord = rs.getString( "LINE_NO__SORD" );
					lineNoSord = "   " + lineNoSord; 
					lineNoSord = lineNoSord.substring(lineNoSord.length()-3);
					replOrder = rs.getString( "PICK_ORDER" );
					pickType = rs.getString( "PICK_TYPE" );
					//lineNoOrd = rs.getInt( "LINE_NO__SORD" ); 
					dExpLev = checkNull(rs.getString( "EXP_LEV" ));
					System.out.println("dExpLev->> ["+dExpLev+"]");
					System.out.println("locCode->> ["+locCode+"]");
					ptcnScanList.add(checkNull(ptcn));	
					itmVolumeMap.clear();
					itmVolumeMap = getItemVoumeMap(itemCode,lotNo,conn);
					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");

					dataMap.put("wave_id", waveId);
					dataMap.put("ptcn", ptcn);
					dataMap.put("item_code", itemCode);
					dataMap.put("site_code", siteCode);
					dataMap.put("lot_no", lotNo);
					dataMap.put("lot_sl", lotSl);
					dataMap.put("quantity", ""+quantity);					

					dataMap.put("sale_order", saleOrder);
					dataMap.put("line_no__sord", lineNoSord);
					dataMap.put("line_no__ord", ""+lineNoOrd);
					dataMap.put("line_no", ""+rowsCount);
					dataMap.put("pick_order", replOrder);
					dataMap.put("pick_type", pickType);
					dataMap.put("stag_loc", stagingLoc);
					if(lotNo == null || lotNo.trim().length() == 0)
					{
						lotNo ="               ";
					}
					if(lotSl == null || lotSl.trim().length()==0)
					{
						lotSl = "     ";
					}

					lineNo = rs.getInt( "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 = rsStock.getString("acct_code__inv");
						cctrCodeDr = rsStock.getString("cctr_code__inv");

						expDate = rsStock.getTimestamp("exp_date");
						packCode = rsStock.getString("pack_code");
						mfgDate = rsStock.getTimestamp("mfg_date");
						siteCodeMfg = rsStock.getString("site_code__mfg");
						packInstr = rsStock.getString("pack_instr");
						suppCodeMfg = rsStock.getString("supp_code__mfg");
						restestDate = rsStock.getTimestamp("retest_date");
						stkRate = rsStock.getDouble("rate");
						stkGrossRate = rsStock.getDouble("gross_rate");
						convQtyStduom = rsStock.getDouble("conv__qty_stduom");
						unitAlt = rsStock.getString("unit__alt");
						batchNo = rsStock.getString("batch_no");
						batchSize = rsStock.getDouble("batch_size");
						unit = rsStock.getString("unit");
						grade = rsStock.getString("grade");
						remarks = rsStock.getString("remarks");
						dimension = rsStock.getString("dimension");					
						holdQuantity = rsStock.getDouble("hold_qty");				
					}
					rsStock.close(); rsStock = null;
					pstmtStock.clearParameters();


					if(holdQuantity > 0)
					{					

						errorString = getError(siteCode, lotNo, lotSl, itemCode, locCode, "INVITMQTST", conn);
						return errorString;

					}

					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("quantity", ""+quantity);
					updateRowMap.put("no_art", noArt);
					updateRowMap.put("tran_ser", "PICISS");
					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 );					
					updateRowMap.put("line_no", "" + lineNo );					
					updateRowMap.put("tran_date", tranDate );
					updateRowMap.put("tran_type","ID"); 

					updateRowMap.put("qty_stduom", ""+quantity);
					updateRowMap.put("unit",unit);
					updateRowMap.put("grade",grade);
					updateRowMap.put("remarks",remarks);
					updateRowMap.put("dimension",dimension);

					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("quantity---->>["+quantity+" shipperSize--->>["+shipperSize+"]");
					if(quantity < shipperSize || shipperSize == 0)
					{
						noArt = 1;
					}
					else
					{
						noArt = Math.floor((quantity) / shipperSize) ;
					}

					dataMap.put("no_art", ""+noArt);

					updateRowMap.put("no_art", noArt);
					updateRowMap.put("gross_weight", caseGrossWeight * noArt);
					updateRowMap.put("net_weight", caseNetWeight * noArt);
					updateRowMap.put("tare_weight", (caseGrossWeight - caseNetWeight) * noArt);
					System.out.println("errorString BEFORE updateStock1----->["+errorString+"]");
					errorString = stkUpdate.updateStock( updateRowMap, xtraParams, conn );
					System.out.println("errorString after updateStock1----->["+errorString+"]");

					if ( errorString != null && errorString.trim().length() > 0 )
					{
						isError = true;
						break;
					}

					updateRowMap.put("loc_code", stagingLoc);
					updateRowMap.put("tran_type", "R");//Creating receipt
					System.out.println("errorString BEFORE updateStock2..........");
					errorString = stkUpdate.updateStock( updateRowMap, xtraParams, conn );
					System.out.println("errorString after updateStock2----->["+errorString+"]");
					if ( errorString != null && errorString.trim().length() > 0 )
					{
						isError = true;
						break;
					}

					updateRowMap.clear();

					strAllocate = new HashMap();
					strAllocate.put("tran_date",tranDate);
					strAllocate.put("ref_ser","M-PTCN");
					strAllocate.put("ref_id",tranId);
					strAllocate.put("ref_line", "" + lineNo);
					strAllocate.put("site_code",siteCode);
					strAllocate.put("item_code",itemCode);

					strAllocate.put("loc_code", stagingLoc);

					strAllocate.put("lot_no",lotNo);
					strAllocate.put("lot_sl",lotSl);
					strAllocate.put("alloc_qty",new Double(quantity));
					strAllocate.put("chg_user",GenericUtility.getInstance().getValueFromXTRA_PARAMS(xtraParams,"loginCode"));
					strAllocate.put("chg_term",GenericUtility.getInstance().getValueFromXTRA_PARAMS(xtraParams,"termId"));
					strAllocate.put("chg_win","w_multi_pick_iss");
					errorString = invAllocTrace.updateInvallocTrace(strAllocate, conn);
					System.out.println("errorString after updateInvallocTrace----->["+errorString+"]");

					// To check the sordalloc allocated quantity > quantity  then insert new record deducte quantity from old location.
					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,QTY_ALLOC 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" );
						sordAllocQty = rs1.getDouble( "QTY_ALLOC" );
					}
					else
					{
						isError = true;
						errorString = itmDBAccess.getErrorString("","RECNOTSORA","");
						return errorString;
					}
					rs1.close();
					rs1 = null;
					pstmt1.close();
					pstmt1 = null;

					sSQL = "SELECT COUNT(*) AS COUNT FROM SORDALLOC WHERE " 
							+ " SALE_ORDER = ? AND LINE_NO = ? "
							+ " AND ITEM_CODE = ? "
							+ " AND LOC_CODE = ? "
							+ " AND LOT_NO = ? "
							+ " AND LOT_SL = ? ";
					int count = 0;
					System.out.println("sordAllocQty---->>["+sordAllocQty+" quantity--->["+quantity+"]");						

					if(sordAllocQty > quantity)
					{
						System.out.println("sordAllocQty in greater.................");	

						updateSql = "UPDATE SORDALLOC SET   QTY_ALLOC = QTY_ALLOC - ? ,QUANTITY__STDUOM = QUANTITY__STDUOM - ?, 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, quantity); 
						//Changed by sumit on 10/09/13 setting 
						pstmtUpd.setDouble(2, quantity * convQtyStduom);
						pstmtUpd.setString(3, saleOrder);
						pstmtUpd.setString(4, lineNoSord);				
						pstmtUpd.setString(5, itemCode);				
						pstmtUpd.setString(6, locCode);				
						pstmtUpd.setString(7, lotNo);				
						pstmtUpd.setString(8, lotSl);				

						updCnt = pstmtUpd.executeUpdate();

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

						System.out.println(" sordalloc updated  updCnt [" + updCnt + "]");

						if (expLev == null || expLev.trim().length() == 0)
						{
							System.out.println("Check1l["+expLev+"]");
							expLev = dExpLev;
						}
						System.out.println("Finally the Exp level["+expLev+"]");								

						pstmtSord = conn.prepareStatement(sSQL);
						pstmtSord.setString(1, saleOrder);
						pstmtSord.setString(2, lineNoSord);				
						pstmtSord.setString(3, itemCode);			
						pstmtSord.setString(4, stagingLoc);

						pstmtSord.setString(5, lotNo);				
						pstmtSord.setString(6, lotSl);				

						rsSord = pstmtSord.executeQuery();

						if(rsSord.next())
						{
							count = rsSord.getInt(1);
						}

						System.out.println("count SORDALLOC@@------>>["+count+"]");							

						if(count > 0)
						{

							System.out.println(" sordalloc If alrady exist Location Update Inside grater  updCnt1st [" + updCnt + "]");

							//updateSql = "UPDATE SORDALLOC SET QTY_ALLOC = QTY_ALLOC + ? ,QUANTITY__STDUOM = QUANTITY__STDUOM + ?"
							updateSql = "UPDATE SORDALLOC SET QTY_ALLOC = QTY_ALLOC + ? ,QUANTITY__STDUOM = QUANTITY__STDUOM + ?, 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, quantity);
							pstmtUpd.setDouble(2, quantity);
							pstmtUpd.setString(3, saleOrder);
							pstmtUpd.setString(4, lineNoSord);				
							pstmtUpd.setString(5, itemCode);	
							pstmtUpd.setString(6, stagingLoc);
							pstmtUpd.setString(7, lotNo);				
							pstmtUpd.setString(8, lotSl);				

							updCnt = pstmtUpd.executeUpdate();

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

							System.out.println(" sordalloc If alrady exist Location Update  Inside grater  updCnt2nd [" + updCnt + "]");
						}								
						else
						{
							System.out.println("--------insert record in SORDALLOC table--------------");									

							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, saleOrder);
							pstmtUpd.setString(2, lineNoSord);
							pstmtUpd.setString(3, expLev);
							pstmtUpd.setString(4, itemCodeOrd);
							pstmtUpd.setString(5, itemCode);
							pstmtUpd.setString(6, lotNo);
							pstmtUpd.setString(7, lotSl);
							pstmtUpd.setString(8, stagingLoc);										
							pstmtUpd.setString(9, itemRef);
							pstmtUpd.setDouble(10, qtyOrd);

							pstmtUpd.setString(11, unit);
							pstmtUpd.setDouble(12, quantity);
							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, quantity * convQtyStduom); 
							pstmtUpd.setTimestamp(22, mfgDate ); 
							pstmtUpd.setString(23, siteCodeMfg);
							pstmtUpd.setString(24, reasCode);									
							pstmtUpd.setString(25, "Y");
							updCnt = pstmtUpd.executeUpdate();
							System.out.println(" INSERT in sordalloc  updCnt [" + updCnt + "]");

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

						}

					}
					else
					{	
						System.out.println("sordAllocQty in lesser.................");	
						pstmtSord = conn.prepareStatement(sSQL);
						pstmtSord.setString(1, saleOrder);
						pstmtSord.setString(2, lineNoSord);				
						pstmtSord.setString(3, itemCode);		
						pstmtSord.setString(4, stagingLoc);

						pstmtSord.setString(5, lotNo);				
						pstmtSord.setString(6, lotSl);				

						rsSord = pstmtSord.executeQuery();

						if(rsSord.next())
						{
							count = rsSord.getInt(1);
						}
						System.out.println("count SORDALLOC--->>"+count);								

						if(count > 0)
						{									
							//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, quantity); 
							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(" sordalloc If alrady exist Location Update  updCnt1st [" + updCnt + "]");

							if(pstmtUpd !=null)
							{
								pstmtUpd.close(); 
								pstmtUpd=null;
							}										
							//updateSql = "UPDATE SORDALLOC SET QTY_ALLOC = QTY_ALLOC + ? ,QUANTITY__STDUOM = QUANTITY__STDUOM + ?"
							updateSql = "UPDATE SORDALLOC SET QTY_ALLOC = QTY_ALLOC + ? ,QUANTITY__STDUOM = QUANTITY__STDUOM + ?, 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, quantity);
							pstmtUpd.setDouble(2, quantity);
							pstmtUpd.setString(3, saleOrder);
							pstmtUpd.setString(4, lineNoSord);				
							pstmtUpd.setString(5, itemCode);	
							pstmtUpd.setString(6, stagingLoc);														
							pstmtUpd.setString(7, lotNo);				
							pstmtUpd.setString(8, lotSl);				

							updCnt = pstmtUpd.executeUpdate();
							System.out.println(" sordalloc If alrady exist Location Update  updCnt2nd [" + updCnt + "]");

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

						}
						else
						{									
							//updateSql = "UPDATE SORDALLOC SET LOC_CODE = ? "
							updateSql = "UPDATE SORDALLOC SET LOC_CODE = ?, 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.setString(1, stagingLoc);
							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();

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

						}						

						System.out.println(" sordalloc Update  updCnt [" + updCnt + "]");

					}
					if(rsSord != null)
					{
						rsSord.close(); 
						rsSord=null;
					}
					if(pstmtSord != null)
					{
						pstmtSord.close();
						pstmtSord=null;
					}

					String updatePickOrdSql = "UPDATE PICK_ORD_DET SET STATUS = ? "
							+ " WHERE PICK_ORDER = ? AND LINE_NO = ? ";

					pstmtUpdPickOrdDet = conn.prepareStatement(updatePickOrdSql);
					pstmtUpdPickOrdDet.setString(1, "C");
					pstmtUpdPickOrdDet.setString(2, replOrder);
					pstmtUpdPickOrdDet.setInt(3, lineNoOrd);


					int updCntPickOrdDet = pstmtUpdPickOrdDet.executeUpdate();

					if(updCntPickOrdDet > 0)
					{
						isUpdatehdr = updatePickOrderHeader(replOrder,conn);
					}

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

					updatePickOrdSql = "UPDATE MULTI_PICK_ISS_DET SET LOC_CODE = ? "
							+ " WHERE TRAN_ID = ? AND LINE_NO = ? ";

					pstmtUpdPickOrdDet = conn.prepareStatement(updatePickOrdSql);								
					pstmtUpdPickOrdDet.setString(1, stagingLoc);
					pstmtUpdPickOrdDet.setString(2, tranId);
					pstmtUpdPickOrdDet.setInt(3, lineNo);										

					updCntPickOrdDet = pstmtUpdPickOrdDet.executeUpdate();

					if(updCntPickOrdDet > 0)
					{
						isUpdatehdr = updatePickOrderHeader(replOrder,conn);
					}

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

					String str = insertPickIssHdrDet(dataMap, conn);							
					System.out.println("Return from insertPickIssHdrDet------->>["+str+"]");		
					str = insertPackHdrDet(dataMap,conn);
					System.out.println("Return from insertPackHdrDet------->>["+str+"]");
					System.out.println("isUpdatehdr121------->>["+isUpdatehdr+"]");
					if(isUpdatehdr)
					{								
						sql = "SELECT REF_SER,PTCN FROM WAVE_TASK_DET WHERE REF_ID = ?";
						pstmt1 = conn.prepareStatement(sql);
						pstmt1.setString(1,replOrder);
						rs1 = pstmt1.executeQuery();
						if( rs1.next() )
						{
							refSer = checkNull(rs1.getString("REF_SER"));							
							ptcn = checkNull(rs1.getString("PTCN"));							
							
						}
						System.out.println(" ref_ser ["+refSer+"]");
						rs1.close();rs1 = null;
						pstmt1.close();pstmt1 = null;
						System.out.println("pickType IN CONFIRMED-------->>["+pickType+"]");
						if(!("A".equalsIgnoreCase(pickType)) && !"P-PICK".equalsIgnoreCase(refSer) && !"A-PICK".equalsIgnoreCase(refSer) )
						{
							//update wave_status of wave_task_det, same time of updating status 'Y'							    	
							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;
						}							


						/*sql = 	" SELECT * FROM PICK_ORD_HDR WHERE PICK_ORDER = ? AND PICK_TYPE = 'M' ";
						pstmt1 = conn.prepareStatement(sql);
						pstmt1.setString(1, replOrder);
						rs1 = pstmt1.executeQuery();
						if( rs1.next())
						{
							updateSql = "UPDATE WAVE_TASK_DET SET WAVE_STATUS = 'C' WHERE REF_SER = 'M-PACK'" +
									" AND WAVE_ID = ?  AND SALE_ORDER = ? ";
							pstmt2 = conn.prepareStatement(updateSql);
							pstmt2.setString(1, rs1.getString("WAVE_ID"));
							pstmt2.setString(2, rs1.getString("SALE_ORDER"));
							updCnt = pstmt2.executeUpdate();
							if( updCnt > 0 )
							{					
								System.out.println( updCnt + " M-PACK rows updated successfully" );
							}
							pstmt2.close();pstmt2 = null;
						}
						rs1.close(); rs1 = null;
						pstmt1.close(); pstmt1 = null;*/

						updCnt= 0;
						// Checking whether all task are done for particular wave_id and sale order
						updCnt = waveStatusUpdate(replOrder, conn);
						if( updCnt > 0)
						{
							System.out.println( updCnt + " row of wave_status updated successfully" );
						}


						sql = "UPDATE SORDER SET SHIP_STATUS = ? WHERE SALE_ORDER = ?";
						pstmtSord = conn.prepareStatement(sql);
						int updateSorder = 0;
						pstmtSord.setString(1, "P");
						pstmtSord.setString(2, saleOrder);
						updateSorder = pstmtSord.executeUpdate();

						if(updateSorder > 0)
						{
							System.out.println(" SORDER UPDATE COUNT ["+updateSorder+"]");
						}
						pstmtSord.clearParameters();

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


						System.out.println("Header updated successfully");
						System.out.println("pickType -------->>["+pickType+"]");
						if("C".equalsIgnoreCase(pickType))
						{
							ConsolidatToDoc conToDoc = new ConsolidatToDoc();
							retString = conToDoc.process(ptcn, conn,xtraParams);
							System.out.println("ConsolidatToDoc retString  :"+retString);
							if (retString != null && retString.trim().length() > 0 )
							{
								isError = true;
								return retString;
							}
							
						}
									

					}
					

				} //end while
				
				if(rs != null)
				{
					rs.close();
					rs = null;
				}
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;//While close.
				}
				if(pstmtStock != null)
				{
					pstmtStock.close();
					pstmtStock = null;
				}

				if (errorString != null && errorString.trim().length() > 0 )
				{
					isError = true;
					return errorString;					
				}
				//insert record in pallet_no and update carton master table
				String str = insertPalletNoAndUpdCartonMast(ptcnScanList,conn);
				System.out.println("After insertPalletNo str----->>["+str+"]");	
				updateSql = "UPDATE MULTI_PICK_ISS SET CONFIRMED = 'Y', CONF_DATE = ?, EMP_CODE__USER = ?"
						+ " 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 + " MULTI_PICK_ISS rows updated successfully" );
				}	
				pstmt.close();
				pstmt = null;



			}//end else of confirmed cndition


		}
		catch(Exception e)
		{
			isError = true;
			e.printStackTrace();
			throw new Exception (e);			
		}
		finally
		{
			System.out.println("not commited.....................");
			/*if(isError)
			{
				conn.rollback();
			}
			else
			{
				conn.commit();
			}*/
			
		}
		return errorString;

	}
	private String insertPickIssHdrDet(HashMap<String,String>issueDataMap,Connection conn) throws Exception
	{
		System.out.println("insertPickIssHdrDet123----------->>"+issueDataMap);		
		ResultSet rs = null;
		PreparedStatement pstmt = null; //currDate
		java.sql.Timestamp currDate = null;
		String sql = "",keyString = "",keyCol = "",tranSer = "",xmlValues = "",siteCode = "",tranId = "",
				currDateStr = "",cartonNo = "",packCode ="",locCodeTo = "";
		int count = 0,rowIns = 0 ;
		try
		{
			if(issueDataMap != null && issueDataMap.size() > 0)
			{
				siteCode = checkNull(issueDataMap.get("site_code"));
				SimpleDateFormat sdfAppl = new SimpleDateFormat(genericUtility.getApplDateFormat());

				currDate = new java.sql.Timestamp(System.currentTimeMillis());
				currDateStr = sdfAppl.format(currDate);
				sql = "SELECT KEY_STRING, TRAN_ID_COL, REF_SER FROM TRANSETUP WHERE TRAN_WINDOW = ? ";
				//System.out.println("selSql :"+selSql);
				pstmt = conn.prepareStatement(sql);
				pstmt.setString( 1, "w_active_pick" );
				rs = pstmt.executeQuery();
				if (rs.next())
				{
					keyString = rs.getString("KEY_STRING");
					keyCol = rs.getString("TRAN_ID_COL");
					tranSer = rs.getString("REF_SER");
				}
				rs.close();rs = null;
				pstmt.close();pstmt = null;
				System.out.println("keyString :"+keyString);
				System.out.println("keyCol :"+keyCol);
				System.out.println("tranSer :"+tranSer);

				xmlValues ="<?xml version=\"1.0\" encoding=\"utf-8\"?><Root>";
				xmlValues = xmlValues + "<Header></Header>";
				xmlValues = xmlValues + "<Detail1>";
				xmlValues = xmlValues +        "<tran_id></tran_id>";
				xmlValues = xmlValues +        "<site_code>" + siteCode + "</site_code>";
				xmlValues = xmlValues +        "<tran_date>" + currDateStr + "</tran_date>";
				xmlValues = xmlValues + "</Detail1></Root>";
				System.out.println("xmlValues  :["+xmlValues+"]");				

				TransIDGenerator tg = new TransIDGenerator(xmlValues, "BASE", CommonConstants.DB_NAME);
				tranId = tg.generateTranSeqID(tranSer, keyCol, keyString, conn);					

				sql = "INSERT INTO PICK_ISS_HDR (TRAN_DATE,EMP_CODE__APRV,CHG_DATE,CONFIRMED,PICK_ORDER,CHG_TERM,"
						+ "CHG_USER,TRAN_ID,PICK_TYPE,SITE_CODE,CONF_DATE,PALLET_NO ) "
						+ "values (?,?,?,?,?,?,?,?,?,?,?,?)";

				pstmt = conn.prepareStatement(sql);
				pstmt.setTimestamp(1, currDate);
				pstmt.setString(2, empCode);
				pstmt.setTimestamp(3, currDate);
				pstmt.setString(4, "Y");
				pstmt.setString(5, checkNull(issueDataMap.get("pick_order")));			
				pstmt.setString(6, chgTerm);
				pstmt.setString(7, chgUser);
				pstmt.setString(8, tranId);
				pstmt.setString(9, checkNull(issueDataMap.get("pick_type")));
				pstmt.setString(10, siteCode);
				//pstmt.setString(11, checkNull(issueDataMap.get("ptcn")));
				pstmt.setTimestamp(11, currDate);
				pstmt.setString(12, checkNull(issueDataMap.get("ptcn")));

				rowIns = pstmt.executeUpdate();
				System.out.println("rowIns------>>["+rowIns+"]");
				if(rowIns > 0)
				{
					count++;
					System.out.println("row insert in PICK_ISS_HDR table.................");
				}			
				pstmt.close();
				pstmt =null;


				System.out.println("@@tranId@@ ---->>["+tranId+"]");



				sql = " SELECT CARTON_NO,PACK_CODE FROM CARTON_MASTER WHERE PICK_ORDER = ?"
						+ " AND LINE_NO__PICK = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, checkNull(issueDataMap.get("pick_order")));
				pstmt.setString(2, checkNull(issueDataMap.get("line_no__sord")));			
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					cartonNo = checkNull(rs.getString("CARTON_NO"));
					packCode = checkNull(rs.getString("PACK_CODE"));
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt =null;
				if(issueDataMap.containsKey("sale_order"))
				{
					locCodeTo =  checkNull(getColumnDescr(conn, "LOC_CODE__TO", "PICK_ORD_DET", "SALE_ORDER", issueDataMap.get("sale_order")));
					System.out.println("PICK_ORD_DET locCodeTo------>>["+locCodeTo+"]");					
				}
				else
				{
					locCodeTo = "";
				}

				sql = "INSERT INTO PICK_ISS_DET(LOC_CODE__SYS,CARTON_NO,LOT_SL,PICK_ORDER,PACK_CODE,QUANTITY,LOC_CODE__TO,"
						+ "TRAN_ID,LINE_NO,SITE_CODE,NO_ART,LINE_NO__ORD,LOT_NO,ITEM_CODE,LOC_CODE) "
						+ "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, locCodeTo);
				pstmt.setString(2, cartonNo);
				pstmt.setString(3, issueDataMap.get("lot_sl"));
				pstmt.setString(4, checkNull(issueDataMap.get("pick_order")));			
				pstmt.setString(5, packCode);
				pstmt.setDouble(6, Double.parseDouble(checkNull(issueDataMap.get("quantity"))));
				pstmt.setString(7, locCodeTo);
				pstmt.setString(8, tranId);
				pstmt.setString(9, checkNull(issueDataMap.get("line_no")));
				pstmt.setString(10, siteCode);
				pstmt.setString(11, checkNull(issueDataMap.get("no_art")));			
				pstmt.setString(12, issueDataMap.get("line_no__ord"));
				pstmt.setString(13, issueDataMap.get("lot_no"));
				pstmt.setString(14, checkNull(issueDataMap.get("item_code")));
				pstmt.setString(15, checkNull(issueDataMap.get("stag_loc")));

				rowIns = pstmt.executeUpdate();
				System.out.println("rowIns------>>["+rowIns+"]");
				if(rowIns > 0)
				{
					count++;
					System.out.println("row insert in PICK_ISS_DET table.................");
				}
				if(count == 2 )
				{
					return "Success";
				}
			}
			
		}
		catch(Exception e)
		{
			isError = true;
			throw e;
		}
		return "Error";
	}
	private String insertPackHdrDet(HashMap<String,String>packDataMap,Connection conn) throws Exception
	{

		System.out.println("insertPackHdrDet----------->>"+packDataMap);		
		ResultSet rs = null,rs1 = null;
		PreparedStatement pstmt = null,pstmt1= null,pstmt2 = null,pstmtIns =null; //currDate
		java.sql.Timestamp currDate = null;
		String sql = "",keyString = "",keyCol = "",tranSer = "",xmlValues = "",siteCode = "",tranId = "",
				currDateStr = "",cartonNo = "",packCode ="",status = "",cartonType = "",pickOrder = "",ptcn = "",
				masterCarton = "",updSql = "",sql1 = "",insSql ="",updateSql= "";
		double tareWgt = 0,fillerWgt = 0; 
		int count = 0,rowIns = 0,successCnt = 0,updCnt = 0 ;
		try
		{
			siteCode = checkNull(packDataMap.get("site_code"));
			SimpleDateFormat sdfAppl = new SimpleDateFormat(genericUtility.getApplDateFormat());

			currDate = new java.sql.Timestamp(System.currentTimeMillis());
			currDateStr = sdfAppl.format(currDate);

			sql = "SELECT CARTON_NO,STATUS,PTCN,CARTON_TYPE,PICK_ORDER,PACK_CODE,MASTER_CARTON,TARE_WEIGHT,"
					+ "FILLER_WEIGHT FROM CARTON_MASTER WHERE PTCN = ? AND CARTON_TYPE =  ?";

			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, checkNull(packDataMap.get("ptcn")));
			pstmt.setString(2, "M");
			rs = pstmt.executeQuery();

			while(rs.next())
			{
				cartonNo = checkNull(rs.getString("CARTON_NO"));
				status = checkNull(rs.getString("STATUS"));
				ptcn = checkNull(rs.getString("PTCN"));
				cartonType = checkNull(rs.getString("CARTON_TYPE"));
				pickOrder = checkNull(rs.getString("PICK_ORDER"));

				packCode = checkNull(rs.getString("PACK_CODE"));
				masterCarton = checkNull(rs.getString("MASTER_CARTON"));
				tareWgt = rs.getDouble("TARE_WEIGHT");
				fillerWgt = rs.getDouble("FILLER_WEIGHT");

				sql1 = "SELECT COUNT(*) FROM PACK_HDR WHERE TRAN_ID = ? ";				
				pstmt1 = conn.prepareStatement(sql1);
				pstmt1.setString(1, pickOrder);				
				rs1 = pstmt1.executeQuery();
				if(rs1.next())
				{
					count = rs1.getInt(1);
				}
				rs1.close();
				rs1 = null;
				pstmt1.close();
				pstmt1 = null;
				System.out.println("PACK_HDR count---->>"+count);				
				if(count > 0)
				{
					successCnt++;
					updSql = "UPDATE PACK_HDR SET CONFIRMED = ?,CONF_DATE = ?,EMP_CODE__APRV = ? ,CHG_DATE = ?,CHG_TERM = ?,"
							+ "CHG_USER = ?,TARE_WEIGHT = ?,FILLER_WEIGHT = ?,OUTER_CARTON_NO = ?,PALLET_NO = ?  WHERE TRAN_ID = ?"; 
					
					pstmt1 = conn.prepareStatement(updSql);
					pstmt1.setString(1, "Y");
					pstmt1.setTimestamp(2, currDate);
					pstmt1.setString(3, empCode);
					pstmt1.setTimestamp(4, currDate);
					pstmt1.setString(5, chgTerm);
					pstmt1.setString(6, chgUser);
					pstmt1.setDouble(7, tareWgt);
					pstmt1.setDouble(8, fillerWgt);//pickOrder
					pstmt1.setString(9, masterCarton);
					pstmt1.setString(10, ptcn);
					pstmt1.setString(11, pickOrder);
					
					rowIns = pstmt1.executeUpdate();
					System.out.println("Rows updated for PACK_HDR------>>["+rowIns+"]");					
					if(rowIns > 0)
					{
						System.out.println("row successfully updated for pack_hdr table......."+rowIns);
						updateSql = "UPDATE WAVE_TASK_DET SET WAVE_STATUS = 'V',STATUS = 'Y' WHERE REF_SER = 'M-PACK'" +
								" AND  PTCN = ? AND REF_ID = ?";
						pstmt2 = conn.prepareStatement(updateSql);
						pstmt2.setString(1, ptcn);
						pstmt2.setString(2, pickOrder);
						updCnt = pstmt2.executeUpdate();
						if( updCnt > 0 )
						{					
							System.out.println( updCnt + " M-PACK rows updated successfully" );
						}
						pstmt2.close();pstmt2 = null;
					}
					pstmt1.close();
					pstmt1 = null;
				}
				
				sql1 = "SELECT CARTON_NO,STATUS,PTCN,CARTON_TYPE,PICK_ORDER,MASTER_CARTON,PACK_CODE"
						+ " FROM CARTON_MASTER WHERE MASTER_CARTON = ? AND  CARTON_TYPE = ?";
				
				pstmt1 = conn.prepareStatement(sql1);
				pstmt1.setString(1, cartonNo);
				pstmt1.setString(2, "C");
				rs1 = pstmt1.executeQuery();
				int lineNoCount = 0;
				while(rs1.next())
				{
					String cartonNoL ="",statusL = "",ptcnL = "",cartonTypeL = "",pickOrderL ="",masterCartonL ="",packCodeL ="";
					lineNoCount++;
					successCnt++;
					cartonNoL = checkNull(rs1.getString("CARTON_NO"));
					statusL = checkNull(rs1.getString("STATUS"));
					ptcnL = checkNull(rs1.getString("PTCN"));
					cartonTypeL = checkNull(rs1.getString("CARTON_TYPE"));
					pickOrderL = checkNull(rs1.getString("PICK_ORDER"));					
					masterCartonL = checkNull(rs1.getString("MASTER_CARTON"));
					packCodeL = checkNull(rs1.getString("PACK_CODE"));
					
					insSql = "INSERT INTO PACK_DET (TRAN_ID,LINE_NO,SALE_ORDER,LINE_NO__SORD,SITE_CODE,ITEM_CODE,LOC_CODE,LOT_NO,"
							+ " LOT_SL,QUANTITY,LOC_CODE__TO,CARTON_NO,NO_ART,STATUS ) "
							+ "VALUES (?,?,?,?,?, ?,?,?,?,?, ?,?,?,? )";
					
					pstmtIns = conn.prepareStatement(insSql);
					pstmtIns.setString(1, pickOrder);
					pstmtIns.setInt(2, lineNoCount);
					pstmtIns.setString(3,  checkNull(packDataMap.get("sale_order")));
					pstmtIns.setString(4,  packDataMap.get("line_no__sord"));
					pstmtIns.setString(5,  checkNull(packDataMap.get("site_code")));
					pstmtIns.setString(6,  checkNull(packDataMap.get("item_code")));
					
					pstmtIns.setString(7,  checkNull(packDataMap.get("stag_loc")));
					pstmtIns.setString(8,  checkNull(packDataMap.get("lot_no")));
					pstmtIns.setString(9,  checkNull(packDataMap.get("lot_sl")));
					
					pstmtIns.setDouble(10,  Double.parseDouble(packDataMap.get("quantity")));
					pstmtIns.setString(11,  checkNull(packDataMap.get("stag_loc")));//LOC_CODE__TO
					pstmtIns.setString(12, cartonNoL);
					pstmtIns.setInt(13, 1);
					pstmtIns.setString(14, "P");
					
					rowIns = pstmtIns.executeUpdate();	
					if(pstmtIns != null)
					{
						pstmtIns.close();
						pstmtIns = null;
					}
					
				} //end detail while
				if(rs1 != null)
				{
					rs1.close();
					rs1 = null;
				}
				if(pstmt1 !=null)
				{
					pstmt1.close();
					pstmt1 = null;
				}
				

			}//end while
			if(rs != null)
			{
				rs.close();
				rs = null;
			}
			if(pstmt !=null)
			{
				pstmt.close();
				pstmt = null;
			}			

		}
		catch(Exception e)
		{
			isError = true;
			throw e;
		}
		if(successCnt > 1)
		{
			return "Success";
		}
		return "Error";
	}
	private boolean updatePickOrderHeader(String pickOrder ,Connection conn) throws ITMException
	{
		PreparedStatement pstmtPickOrdHdr =null;
		PreparedStatement pstmtUpdPickOrdHdr=null;
		ResultSet rsPicKordHdr =null;
		String status = "";
		Boolean isUpdateHdr=true;
		int updCntPickOrdHdr= 0;
		double pickQty = 0;
		
		try {
			
			String sqlPickOrd = "SELECT STATUS,(QUANTITY - (CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE DEALLOC_QTY END )) AS QUANTITY FROM PICK_ORD_DET WHERE PICK_ORDER = ? ";
			pstmtPickOrdHdr = conn.prepareStatement(sqlPickOrd);
			pstmtPickOrdHdr.setString(1, pickOrder);
			
			rsPicKordHdr = pstmtPickOrdHdr.executeQuery();
			
			while(rsPicKordHdr.next())
			{
				status = rsPicKordHdr.getString("STATUS") == null ? "": rsPicKordHdr.getString("STATUS").trim();
				pickQty = rsPicKordHdr.getDouble("QUANTITY");
				
				System.out.println("@@@@ Inside Check Status["+status+"]pickQty["+pickQty+"]");
				if( !("C".equalsIgnoreCase(status) ) && pickQty > 0)
				{
					isUpdateHdr =false;
					break;
				}
			}
			
			if(pstmtPickOrdHdr != null)
			{
				pstmtPickOrdHdr.close();
				pstmtPickOrdHdr=null;
			}
			System.out.println("@@@@ isUpdateHdr------->>["+isUpdateHdr+"]");
			if(isUpdateHdr)
			{
				
				System.out.println("@@@@ Updating hader status as C ");
				String updatePickOrdSql = "UPDATE PICK_ORD_HDR SET STATUS = ? "
						+ " WHERE PICK_ORDER = ? ";
						
				pstmtUpdPickOrdHdr = conn.prepareStatement(updatePickOrdSql);
				pstmtUpdPickOrdHdr.setString(1, "C");
				pstmtUpdPickOrdHdr.setString(2, pickOrder);
								

				updCntPickOrdHdr = pstmtUpdPickOrdHdr.executeUpdate();
				
				if(pstmtUpdPickOrdHdr !=null)
				{
					pstmtUpdPickOrdHdr.close();
					pstmtUpdPickOrdHdr=null;
				}
				
				
				if(updCntPickOrdHdr > 0)
				{
					System.out.println("@@@@ Update Successfully");
					return true;
				}
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			isError = true;
			throw new ITMException(e);
		}
	
		return false;
	}
	private String insertPalletNoAndUpdCartonMast(ArrayList<String> ptcnList,Connection conn) throws Exception
	{		
		PreparedStatement pstmt = null;
		String sql = "",mark = "";
		int updCnt = 0,cnt = 0,pstmtCnt = 2 ;
		StringBuffer sqlBuf = new StringBuffer();
		System.out.println("PTCN scan List---------->>"+ptcnList);		
		try
		{
			if(ptcnList != null && ptcnList.size() > 0)
			{
				sql = " INSERT INTO PALLET_NO (PALLET_NO,STATUS) VALUES (?,?) "; 				
				for(int i = 0 ; i < ptcnList.size(); i++)
				{
					pstmt = conn.prepareStatement(sql);			
					pstmt.setString(1,ptcnList.get(i));
					pstmt.setString(2,"U");
					updCnt = pstmt.executeUpdate();
					pstmt.close();
					pstmt = null;		

				}


				sqlBuf.append("UPDATE CARTON_MASTER SET STATUS = ?,CHANGE_STATUS = ?,PALLET_NO = PTCN WHERE PTCN IN (");
				for(int i = 0; i < ptcnList.size(); i++)
				{
					mark = mark + "?,";
				}
				mark = mark.substring(0,mark.length() - 1);
				sqlBuf.append(mark);
				sqlBuf.append( ")  AND STATUS <> ? AND CARTON_TYPE IN ('A','C')");

				pstmt = conn.prepareStatement(sqlBuf.toString());
				pstmt.setString(1,"U");	
				pstmt.setString(2,"N");

				for (String list : ptcnList)
				{						
					pstmt.setString(++pstmtCnt,list);	
				}

				pstmt.setString(++pstmtCnt,"R");
				
				cnt = pstmt.executeUpdate();				
				if(pstmt !=null)
				{
					pstmt.close();
					pstmt = null;
				}
				
			}
			else
			{
				System.out.println("No PTCN scanned..................");
			}
			System.out.println(":Rows inserted pallet_no:["+updCnt+"] and updated carton master: ["+cnt+"]");
			if(updCnt > 0 && cnt > 0)
			{
				System.out.println("Record successfully inserted in pallet_no and updated in carton master...... ");
				return "Success";
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new Exception(e);
		}
		return "Error";
	}
	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 PICK_ORD_HDR WHERE PICK_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)
		{
			isError = true;
			e.printStackTrace();
			throw new ITMException(e);
		}		
		return updateCount;
	}
	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
			isError = true;
			throw e;
		}
		finally
		{
			if(pstmt != null)
			{
				pstmt.close();
				pstmt = null;
		
			}
			if(rs != null)
			{
				rs.close();
				rs = null;
		
			}
		}
		
		return dataVolumeMap;
	}
	private String getColumnDescr(Connection conn, String columnName ,String tableName, String columnName2, String value) throws Exception 
	{

			PreparedStatement pstmt = null ;
			ResultSet rs = null ; 
			String sql = "";
			String findValue = "";
			try
			{			
				sql = "SELECT " + columnName + " from " + tableName + " where " + columnName2 +"= ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,value);
				rs = pstmt.executeQuery();
				if(rs.next())
				{					
					findValue = rs.getString(1);
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;	        
			}
			catch(Exception e)
			{
				System.out.println("Exception in getColumnDescr ");
				isError = true;
				e.printStackTrace();
				throw new Exception(e);
				
			}
			System.out.println("returning String from getColumnDescr " + findValue);
			return findValue;
		 
	}
	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)
        {
        	isError = true;
            e.printStackTrace();
            throw new ITMException(e);
        }
        return mainStr;
    }
	private String checkNull(String str)
	{
		if(str == null)
		{
			str = "";
		}
		return str.trim();
	}
	
}
