/*
    Window Name : w_wavegen
*/

package ibase.webitm.ejb.wms;

import ibase.bi.ejb.session.TotalManager;
import ibase.system.config.AppConnectParm;
import ibase.system.config.ConnDriver;
import ibase.utility.CommonConstants;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.ejb.ITMDBAccessLocal;
import ibase.webitm.ejb.MasterStatefulLocal;
import ibase.webitm.ejb.ProcessEJB;
import ibase.webitm.ejb.dis.DistCommon;
import ibase.webitm.ejb.dis.InvAllocTraceBean;
import ibase.webitm.reports.utility.JasperReportGenerator;
import ibase.webitm.reports.utility.XSDParser;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;
import ibase.webitm.utility.TransIDGenerator;

import java.awt.print.PrinterJob;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

import java.sql.Statement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Properties;
import java.util.Random;
import java.util.Set;
import java.util.StringTokenizer;
import java.util.TreeMap;
import java.util.TreeSet;

import javax.mail.Flags.Flag;
import javax.naming.InitialContext;
import javax.print.PrintService;

//import net.sf.jasperreports.governors.GovernorException;

import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

@javax.ejb.Stateless
public class DeallocArtPrc extends ProcessEJB implements WaveGenerationPrcLocal,WaveGenerationPrcRemote //SessionBean
{
	
	
	InvAllocTraceBean invAllocTrace = new InvAllocTraceBean();
	GenericUtility genericUtility = GenericUtility.getInstance();
	ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();	
	CommonConstants commonConstants = new CommonConstants();
	DistCommon discommon = new DistCommon();
	
	public String process() throws RemoteException,ITMException
	{
		return "";
	}
	
	public String getData(String xmlString, String xmlString2, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		String rtrStr = "";
		Document headerDom = null;
		Document detailDom = null;
		System.out.println("getData(xmlString,xmlString2,windowName,xtraParams CALLED..."+ xmlString);
		try
		{
			if(xmlString != null && xmlString.trim().length()!=0)
			{
				System.out.println("XML String :"+xmlString);
				headerDom = genericUtility.parseString(xmlString); 				
			}
			if(xmlString2 != null && xmlString2.trim().length()!=0)
			{
				System.out.println("XML String :"+xmlString2);
				detailDom = genericUtility.parseString(xmlString2); 				
			}
			rtrStr = getData(headerDom, detailDom, windowName, xtraParams);
		}
		catch (Exception e)
		{
			System.out.println("Exception :DeallocArtPrc :getData(String xmlString, String xmlString2, String windowName, String xtraParams):" + e.getMessage() + ":");
			rtrStr = e.getMessage();
		}
		return rtrStr; 
	}

	public String getData(Document headerDom, Document detailDom, String windowName, String xtraParams) throws RemoteException,ITMException
	{
  
		String errCode = "";
		String errString = "";
		String getDataSql= "" ;
		String sql= "" ;
		String resultString = "";
		String ptcn ="";
		String lineNoSord = "";	
		String siteCode = "";
		String pickType = "";
		String pickOrder = "";		
		String saleOrderFr = "";
		String saleOrderTo = "";
		String custCodeFr = "";
		String custCodeTo = "";
		String custCodeDlvFr = "";
		String custCodeDlvTo = "";
		String itemSerFr = "";
		String itemSerTo = "";
		String itemCodeFr = "";
		String itemCodeTo = "";
		String lotNoFr = "";
		String lotNoTo = "";
		String cartonNoFr = "";
		String cartonNoTo = "";
		String locCode = "";
		String replOrPick = "";
		String itemCode = "", tempItemCode = "";
		int lineNoPick = 0;
		double quantity = 0.0;
		
		Connection conn = null;
		ResultSet rs = null;
		ResultSet rs1 = null;
		PreparedStatement pstmt = null;
		Statement st = null;
		HashMap stockDetail = new HashMap();
		StringBuffer retTabSepStrBuff = new StringBuffer();			
		
		try
		{
			ConnDriver connDriver = new ConnDriver();
			SimpleDateFormat simpleDateFormat = new SimpleDateFormat(genericUtility.getDBDateFormat());
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;			
			//siteCode = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "site_code");
			/*ptcn = genericUtility.getColumnValue("ptcn",headerDom);
			if ( ptcn == null || ptcn.trim().length() == 0 )
			{
				ptcn = "";
				System.out.println("PTCN From is Null...");
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			} */
			siteCode = genericUtility.getColumnValue("site_code",headerDom);
			if ( siteCode == null || siteCode.trim().length() == 0 )
			{
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			System.out.println(" site code ["+siteCode+"]");
			saleOrderFr = genericUtility.getColumnValue("sale_order__fr",headerDom);
			if ( saleOrderFr == null || saleOrderFr.trim().length() == 0 )
			{
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			System.out.println(" saleOrderFr ["+saleOrderFr+"]");
			
			saleOrderTo = genericUtility.getColumnValue("sale_order__to",headerDom);         
			if ( saleOrderTo == null || saleOrderTo.trim().length() == 0 )
			{
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			System.out.println(" saleOrderTo ["+saleOrderTo+"]");
			
			custCodeFr = genericUtility.getColumnValue("cust_code__fr",headerDom);
			if ( custCodeFr == null || custCodeFr.trim().length() == 0 )
			{
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			System.out.println(" custCodeFr ["+custCodeFr+"]");
			
			custCodeTo = genericUtility.getColumnValue("cust_code__to",headerDom); 
			if ( custCodeTo == null || custCodeTo.trim().length() == 0 )
			{
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}  
			custCodeDlvFr = genericUtility.getColumnValue("cust_code__dlv_fr",headerDom);
			if ( custCodeDlvFr == null || custCodeDlvFr.trim().length() == 0 )
			{
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			custCodeDlvTo = genericUtility.getColumnValue("cust_code__dlv_to",headerDom); 
			if ( custCodeDlvTo == null || custCodeDlvTo.trim().length() == 0 )
			{
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}  
			itemSerFr = genericUtility.getColumnValue("item_ser__fr",headerDom);
			if ( itemSerFr == null || itemSerFr.trim().length() == 0 )
			{
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			itemSerTo = genericUtility.getColumnValue("item_ser__to",headerDom);
			if ( itemSerTo == null || itemSerTo.trim().length() == 0 )
			{
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			itemCodeFr = genericUtility.getColumnValue("item_code__fr",headerDom);
			if ( itemCodeFr == null || itemCodeFr.trim().length() == 0 )
			{
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			itemCodeTo = genericUtility.getColumnValue("item_code__to",headerDom);
			if ( itemCodeTo == null || itemCodeTo.trim().length() == 0 )
			{
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			lotNoFr = genericUtility.getColumnValue("lot_no__fr",headerDom);
			if ( lotNoFr == null || lotNoFr.trim().length() == 0 )
			{
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			lotNoTo = genericUtility.getColumnValue("lot_no__to",headerDom);
			if ( lotNoTo == null || lotNoTo.trim().length() == 0 )
			{
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			cartonNoFr = genericUtility.getColumnValue("carton_no__fr",headerDom);
			if ( cartonNoFr == null || cartonNoFr.trim().length() == 0 )
			{
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			cartonNoTo = genericUtility.getColumnValue("carton_no__to",headerDom);
			if ( cartonNoTo == null || cartonNoTo.trim().length() == 0 )
			{
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			
			
			/*getDataSql = " SELECT CM.CARTON_NO, POD.SALE_ORDER, POD.LINE_NO__SORD, CM.PICK_ORDER, CM.LINE_NO__PICK, " +
					" POD.ITEM_CODE,  POD.LOC_CODE, POD.LOT_NO, POD.LOT_SL, WTD.WAVE_ID, POH.PICK_TYPE , POD.QUANTITY " +
					" FROM CARTON_MASTER CM, PICK_ORD_DET POD , PICK_ORD_HDR POH, WAVE_TASK_DET WTD WHERE CM.PTCN = ? " +
					" AND CM.PICK_ORDER = POD.PICK_ORDER AND POH.PICK_ORDER = POD.PICK_ORDER " +
					" AND CM.LINE_NO__PICK = POD.LINE_NO AND WTD.PTCN = CM.PTCN " +
					" AND WTD.REF_ID = CM.PICK_ORDER  " +
					" AND POH.PICK_TYPE IN (?,?) " +
					//" AND ( FN_CHECK_DEALLOC_ARTDET(CM.PTCN, CM.CARTON_NO ) = 0 ) "+					
					" AND CM.STATUS <> ? ORDER BY CM.CARTON_NO ";*/	
			
			getDataSql = " SELECT CM.CARTON_NO, POD.SALE_ORDER, POD.LINE_NO__SORD, CM.PICK_ORDER, CM.LINE_NO__PICK," +
					" POD.ITEM_CODE,  POD.LOC_CODE, POD.LOT_NO, POD.LOT_SL, WTD.WAVE_ID, POD.QUANTITY , CUSTOMER.CUST_CODE, " +
					" SORDER.ITEM_SER, WTD.PTCN, POH.PICK_TYPE FROM CARTON_MASTER CM, PICK_ORD_DET POD , PICK_ORD_HDR POH, WAVE_TASK_DET WTD , " +
					" WAVE_TASK WAVE_TASK, SORDER SORDER, CUSTOMER CUSTOMER , DESPATCH DESPATCH " +
					" WHERE CM.PICK_ORDER = POD.PICK_ORDER " +
					" AND POH.PICK_ORDER = POD.PICK_ORDER " +
					" AND CM.LINE_NO__PICK = POD.LINE_NO AND WTD.PTCN = CM.PTCN " +
					" AND WTD.REF_ID = CM.PICK_ORDER " +
					
					" AND POH.PICK_TYPE IN ('C','M') " +
					" AND SORDER.SITE_CODE__SHIP = ? "+
					" AND SORDER.SALE_ORDER >= ? AND SORDER.SALE_ORDER <= ? " +
					" AND CUSTOMER.CUST_CODE >= ? AND CUSTOMER.CUST_CODE <= ? " +	
					" AND  SORDER.CUST_CODE__DLV >= ? AND  SORDER.CUST_CODE__DLV <= ? " +
					" AND  SORDER.ITEM_SER >= ?  AND SORDER.ITEM_SER <= ? "+									
					" AND POD.ITEM_CODE >= ? AND POD.ITEM_CODE <= ? " +
					" AND POD.LOT_NO >= ? AND POD.LOT_NO <= ? " +
					" AND CM.CARTON_NO >= ? AND CM.CARTON_NO <= ? " +
					" AND SORDER.SALE_ORDER = POH.SALE_ORDER " +
					" AND SORDER.CUST_CODE = CUSTOMER.CUST_CODE " +
					" AND WAVE_TASK.WAVE_ID = WTD.WAVE_ID " +
					" AND DESPATCH.WAVE_ID = WAVE_TASK.WAVE_ID " +
					" AND DESPATCH.SORD_NO = WTD.SALE_ORDER " +
					" AND DESPATCH.CONFIRMED <> 'Y' AND WAVE_TASK.CANCEL <> 'Y' " +
					//Changed By Rohan on 21-06-13 [] for Adding Parcel carton type C
					//" AND CM.STATUS <> 'R' AND CM.CARTON_TYPE = 'C' "+
					" AND CM.STATUS <> 'R' AND CM.CARTON_TYPE IN('C','P') "+
					/////////////////////////////////////
					" UNION ALL "+
					" SELECT DISTINCT CM.CARTON_NO, POD.SALE_ORDER, POD.LINE_NO__SORD, POD.PICK_ORDER, POD.LINE_NO, " +
					" POD.ITEM_CODE,  POD.LOC_CODE, POD.LOT_NO, POD.LOT_SL, WTD.WAVE_ID, (POD.QUANTITY - CASE WHEN POD.DEALLOC_QTY IS NULL THEN 0 ELSE POD.DEALLOC_QTY END) AS QUANTITY , CUSTOMER.CUST_CODE, " +
					" SORDER.ITEM_SER, WTD.PTCN, POH.PICK_TYPE FROM CARTON_MASTER CM, PICK_ORD_DET POD , PICK_ORD_HDR POH, WAVE_TASK_DET WTD, " +
					" WAVE_TASK WAVE_TASK, SORDER SORDER, CUSTOMER CUSTOMER , DESPATCH DESPATCH " +
					" WHERE CM.PICK_ORDER = POD.PICK_ORDER  AND POH.PICK_ORDER = POD.PICK_ORDER " +					
					" AND WTD.PTCN = CM.PTCN AND WTD.REF_ID = CM.PICK_ORDER  " +
					" AND POH.PICK_TYPE = 'A' " +
					" AND SORDER.SITE_CODE__SHIP = ? " +
					" AND SORDER.SALE_ORDER >= ? AND SORDER.SALE_ORDER <= ? " +
					" AND CUSTOMER.CUST_CODE >= ? AND CUSTOMER.CUST_CODE <= ? " +
					" AND  SORDER.CUST_CODE__DLV >= ? AND  SORDER.CUST_CODE__DLV <= ? " +
					" AND  SORDER.ITEM_SER >= ? AND SORDER.ITEM_SER <= ? " +
					" AND POD.ITEM_CODE >= ? AND POD.ITEM_CODE <= ? " +
					" AND POD.LOT_NO >= ? AND POD.LOT_NO <= ? " +
					" AND CM.CARTON_NO >= ? AND CM.CARTON_NO <= ? " +
					" AND SORDER.SALE_ORDER = POH.SALE_ORDER " +
					" AND SORDER.CUST_CODE = CUSTOMER.CUST_CODE " +
					" AND WAVE_TASK.WAVE_ID = WTD.WAVE_ID " +
					" AND DESPATCH.WAVE_ID = WAVE_TASK.WAVE_ID " +
					" AND DESPATCH.SORD_NO = WTD.SALE_ORDER " +
					" AND DESPATCH.CONFIRMED <> 'Y' AND WAVE_TASK.CANCEL <> 'Y' " +
					" AND CM.STATUS <> 'R' AND CM.CARTON_TYPE = 'A' " +
					//Changed By Pragyan 21-MAY-14 tO join carton master with pick_ord_det
					" AND CM.CARTON_NO = POD.CARTON_NO "+
					" AND (POD.QUANTITY - CASE WHEN POD.DEALLOC_QTY IS NULL THEN 0 ELSE POD.DEALLOC_QTY END) > 0 "+
					/////////////////////////////////////
					" UNION ALL "+
					" SELECT CM.CARTON_NO, ROD.SALE_ORDER, ROD.LINE_NO__SORD, CM.PICK_ORDER, CM.LINE_NO__PICK, ROD.ITEM_CODE,  ROD.LOC_CODE, ROD.LOT_NO, ROD.LOT_SL," +
					" WTD.WAVE_ID, ROD.QUANTITY, CUSTOMER.CUST_CODE,  SORDER.ITEM_SER, WTD.PTCN, ' ' AS PICK_TYPE " +
					" FROM CARTON_MASTER CM, REPL_ORD_DET ROD , REPL_ORD_HDR ROH, WAVE_TASK_DET WTD ," +
					" WAVE_TASK WAVE_TASK, SORDER SORDER, CUSTOMER CUSTOMER , DESPATCH DESPATCH " +
					" WHERE CM.PICK_ORDER = ROD.REPL_ORDER  AND ROH.REPL_ORDER = ROD.REPL_ORDER " +
					" AND CM.LINE_NO__PICK = ROD.LINE_NO AND WTD.PTCN = CM.PTCN " +
					" AND WTD.REF_ID = CM.PICK_ORDER " +
					" AND SORDER.SITE_CODE__SHIP = ? " +
					" AND SORDER.SALE_ORDER >= ? AND SORDER.SALE_ORDER <= ?" +
					" AND CUSTOMER.CUST_CODE >= ? AND CUSTOMER.CUST_CODE <= ? " +
					" AND  SORDER.CUST_CODE__DLV >= ? AND  SORDER.CUST_CODE__DLV <= ? " +
					" AND  SORDER.ITEM_SER >= ? AND SORDER.ITEM_SER <= ? " +
					" AND ROD.ITEM_CODE >= ? AND ROD.ITEM_CODE <= ? " +
					" AND ROD.LOT_NO >= ? AND ROD.LOT_NO <= ? " +
					" AND CM.CARTON_NO >= ? AND CM.CARTON_NO <= ? " +
					" AND SORDER.SALE_ORDER = ROH.SALE_ORDER " +
					" AND SORDER.CUST_CODE = CUSTOMER.CUST_CODE " +
					" AND WAVE_TASK.WAVE_ID = WTD.WAVE_ID " +
					" AND DESPATCH.WAVE_ID = WAVE_TASK.WAVE_ID " +
					" AND DESPATCH.SORD_NO = WTD.SALE_ORDER " +
					" AND DESPATCH.CONFIRMED <> 'Y' AND WAVE_TASK.CANCEL <> 'Y' " +
					" AND CM.STATUS <> 'R' AND CM.CARTON_TYPE = 'S' " +
					" ORDER BY WAVE_ID, PTCN, SALE_ORDER,LINE_NO__SORD, ITEM_CODE, CARTON_NO "; 
					
			pstmt = conn.prepareStatement(getDataSql);		
			pstmt.setString(1, siteCode);		
			pstmt.setString(2,saleOrderFr);
			pstmt.setString(3,saleOrderTo);
			pstmt.setString(4,custCodeFr);
			pstmt.setString(5,custCodeTo);
			pstmt.setString(6,custCodeDlvFr);
			pstmt.setString(7,custCodeDlvTo);
			pstmt.setString(8,itemSerFr);
			pstmt.setString(9,itemSerTo);
			pstmt.setString(10,itemCodeFr);
			pstmt.setString(11,itemCodeTo);
			pstmt.setString(12,lotNoFr);
			pstmt.setString(13,lotNoTo);
			pstmt.setString(14,cartonNoFr);
			pstmt.setString(15,cartonNoTo);
			
			pstmt.setString(16, siteCode);		
			pstmt.setString(17,saleOrderFr);
			pstmt.setString(18,saleOrderTo);
			pstmt.setString(19,custCodeFr);
			pstmt.setString(20,custCodeTo);
			pstmt.setString(21,custCodeDlvFr);
			pstmt.setString(22,custCodeDlvTo);
			pstmt.setString(23,itemSerFr);
			pstmt.setString(24,itemSerTo);
			pstmt.setString(25,itemCodeFr);
			pstmt.setString(26,itemCodeTo);
			pstmt.setString(27,lotNoFr);
			pstmt.setString(28,lotNoTo);
			pstmt.setString(29,cartonNoFr);
			pstmt.setString(30,cartonNoTo);
			
			pstmt.setString(31, siteCode);		
			pstmt.setString(32,saleOrderFr);
			pstmt.setString(33,saleOrderTo);
			pstmt.setString(34,custCodeFr);
			pstmt.setString(35,custCodeTo);
			pstmt.setString(36,custCodeDlvFr);
			pstmt.setString(37,custCodeDlvTo);
			pstmt.setString(38,itemSerFr);
			pstmt.setString(39,itemSerTo);
			pstmt.setString(40,itemCodeFr);
			pstmt.setString(41,itemCodeTo);
			pstmt.setString(42,lotNoFr);
			pstmt.setString(43,lotNoTo);
			pstmt.setString(44,cartonNoFr);
			pstmt.setString(45,cartonNoTo);
			
			rs = pstmt.executeQuery();
			if( rs.next() )
			{
				do
				{
					pickType = checkNull(rs.getString("PICK_TYPE"));
					lineNoSord = rs.getString("LINE_NO__SORD");
					pickOrder = rs.getString("PICK_ORDER");
					lineNoPick = rs.getInt("LINE_NO__PICK");
					itemCode = rs.getString("ITEM_CODE");
					
					replOrPick = checkForReplOrPick(pickOrder, conn);
					System.out.println(" replOrPick ["+replOrPick+"]");
					
					if( "PICK".equalsIgnoreCase(replOrPick))
					{
						stockDetail.clear();
						stockDetail = getLocCodForPick(pickOrder, lineNoPick, conn);
					}
					else if( "REPL".equalsIgnoreCase(replOrPick))
					{
						stockDetail.clear();
						stockDetail = getLocCodForRepl(pickOrder, lineNoPick, conn);
						pickType = "S";
					}
					System.out.println(" stockDetail  "+stockDetail);
					
					
					//WAVE_ID
					retTabSepStrBuff.append(rs.getString("WAVE_ID")).append("\t");	
					
					//PTCN 
					retTabSepStrBuff.append(rs.getString("PTCN")).append("\t");					
					
					//SALE_ORDER
					retTabSepStrBuff.append(rs.getString("SALE_ORDER")).append("\t");					
					
					//LINE_NO__SORD
					lineNoSord = "   " + lineNoSord; 
					lineNoSord = lineNoSord.substring(lineNoSord.length()-3);
					System.out.println(" sord line no ["+lineNoSord+"]");
					retTabSepStrBuff.append(lineNoSord).append("\t");				
					
					//CARTON_NO
					retTabSepStrBuff.append(rs.getString("CARTON_NO")).append("\t");					
					
					//PICK_ORDER
					retTabSepStrBuff.append(stockDetail.get("ref_id").toString()).append("\t");					
					
					//LINE_NO__PICK
					retTabSepStrBuff.append(stockDetail.get("ref_line_no").toString()).append("\t");
					
					//ITEM_CODE
					retTabSepStrBuff.append(itemCode).append("\t");
					
					//LOC_CODE					
					locCode = stockDetail.get("loc_code").toString();
					retTabSepStrBuff.append(locCode).append("\t");
					
					//LOT_NO
					retTabSepStrBuff.append(rs.getString("LOT_NO")).append("\t");
					
					//QUANTITY
					if("A".equalsIgnoreCase(pickType) || "S".equalsIgnoreCase(pickType))
					{
						quantity = rs.getDouble("QUANTITY");
					}
					else
					{
						System.out.println(" tempItemCode ["+tempItemCode+"] itemcode ["+itemCode+"]");
						if(!tempItemCode.equalsIgnoreCase(itemCode))
						{
							HashMap itmVolumeMap = getItemVoumeMap(itemCode,"",conn);
							double packSizeActive = (Double)itmVolumeMap.get("PACK_SIZE");
							quantity = packSizeActive;	
						}
					}
					retTabSepStrBuff.append(quantity).append("\t");	
									
					//LOT_SL
					retTabSepStrBuff.append(rs.getString("LOT_SL")).append("\t");
					
					//PICK_TYPE
					retTabSepStrBuff.append( pickType ).append("\t");
					
					//Pick_order
					retTabSepStrBuff.append(rs.getString("PICK_ORDER")).append("\t");
					
					//Pick_line 
					retTabSepStrBuff.append(rs.getString("LINE_NO__PICK")).append("\n");
					
					
				 }while(rs.next());	
				
				
			}			
			else
			{
				errCode ="VTNOREC2";
			}
			rs.close(); rs = null;
			pstmt.close(); pstmt = null;			
			resultString = retTabSepStrBuff.toString();
			System.out.println(" -- ResultString....." + resultString);			
			if (!errCode.equals(""))
			{
				resultString = itmDBAccessEJB.getErrorString("", errCode, "", "", conn);
				System.out.println("resultString: "+resultString);
			}		
			
		}
		catch (SQLException e)
		{
			System.out.println("SQLException : :getData(Document headerDom, Document detailDom, String windowName, String xtraParams):" + e.getMessage() + ":");
			throw new ITMException(e);
		}
		catch (Exception e)
		{
			System.out.println("Exception : :getData(Document headerDom, Document detailDom, String windowName, String xtraParams):" + e.getMessage() + ":");
			throw new ITMException(e);
		}
		finally
		{		

			try
			{				
				if(conn != null)
				{		
					
					if(rs != null)
					{
						rs.close();
						rs = null;
					}
					if(rs1 != null)
					{
						rs1.close();
						rs1 = null;
					}
					
					if(pstmt != null)
					{
						pstmt.close();
						pstmt=null;
					}
					
					conn.close();
					conn = null;
				}
			}
			catch(Exception e)
			{
				errString = e.getMessage();
				e.printStackTrace();
				throw new ITMException(e);
			}
		}		
		return resultString;	
	}
	//Process Method

	public String process(String xmlString, String xmlString2, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		String retStr = "";
		Document detailDom = null;
		Document headerDom = null;
		
		GenericUtility genericUtility = GenericUtility.getInstance();
		System.out.println("below genericUtility--------------->>>>>>>>>");
		try
		{	
			if(xmlString != null && xmlString.trim().length()!=0)
			{
				headerDom = genericUtility.parseString(xmlString); 
				System.out.println("headerDom" + headerDom);
			}
			if(xmlString2 != null && xmlString2.trim().length()!=0)
			{
				detailDom = genericUtility.parseString(xmlString2); 
				System.out.println("detailDom" + detailDom);
			}
									
		    retStr = process(headerDom, detailDom, windowName, xtraParams);
		    
		}
		catch (Exception e)
		{
			System.out.println("Exception :StockAllocationPrcEJB :process(String xmlString, String xmlString2, String windowName, String xtraParams):" + e.getMessage() + ":");
			e.printStackTrace();
			retStr = e.getMessage();
			throw new ITMException(e);
		}
		return retStr;
		
	}
	public String process(Document headerDom, Document detailDom, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		System.out.println("detailDom------------------->"+detailDom);
		String childNodeName = "";
		String errCode = "";
		//String siteCode = this.siteCode;
		String waveId = "";
		//String saleOrder = null;
		String ptcn = "";
		String itemCode = null;
		String lineNo = null;
		String pickOrder = "";
		String cartonNo= "";
		String lineNoPick = "";
		String pickType = "";
		String sql = "";
		double quantity = 0;
		double realQty = 0;
		String errString = "";	
		String chgUser = "";
		String chgTerm = "";
		String siteCode = "";
		String tranId = "";
		String locCode ="";
		String lotNo ="";
		String lotSl ="", saleOrder ="";
		String lineNoSord = "",cartonPickOrder = "";
		String cartonPickLine = "", confirmed = "";
		String pndInvStat = "";
		int tranLineNo = 0;
		java.sql.Date currDate = new java.sql.Date( System.currentTimeMillis() );	
		
		Connection conn = null;
		ResultSet rs = null, rs1 = null;
		PreparedStatement pstmt = null, pstmt1 =null, pstmt2 = null;		
		
		int parentNodeListLength = 0;
		int childNodeListLength = 0;		
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;	
		ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
		DeallocArtConfLocal deallocArtConfLocal = null;
		try
		{
			System.out.println(" xtraParams -->"+xtraParams);
			chgUser = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginCode");
			chgTerm = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "termId");
			siteCode = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "site_code");
			//ptcn = genericUtility.getColumnValue("ptcn",headerDom);
			
			System.out.println(" chgUser ["+chgUser+"] chgTerm["+chgTerm+"] siteCode["+siteCode+"]");
			
			ConnDriver connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");
			conn.setAutoCommit(false);
			connDriver = null;
			
			/*sql = " SELECT DISTINCT WTD.WAVE_ID FROM CARTON_MASTER CM, PICK_ORD_DET POD , PICK_ORD_HDR POH, WAVE_TASK_DET WTD " +
					" WHERE CM.PTCN = ? AND CM.PICK_ORDER = POD.PICK_ORDER AND POH.PICK_ORDER = POD.PICK_ORDER " +
					" AND CM.LINE_NO__PICK = POD.LINE_NO AND WTD.PTCN = CM.PTCN AND WTD.REF_ID = CM.PICK_ORDER " ;
					//Changed by sumit on 04/mar/13 for all type of pick it will work.
					//" AND POH.PICK_TYPE IN (?,?) AND CM.CARTON_TYPE = 'C'";
			pstmt =conn.prepareStatement(sql);
			pstmt.setString(1, ptcn);
			//Changed by sumit on 04/04/13 commenting as per sql.
			pstmt.setString(2, "C");
			pstmt.setString(3, "M");
			rs =pstmt.executeQuery();
			if( rs.next() )
			{
				waveId = rs.getString("WAVE_ID");
			}*/
			
			tranId = getTranId("w_dealloc_art", siteCode, conn);
			sql = "";
			sql = " INSERT INTO DEALLOC_ART (TRAN_ID, SITE_CODE, CONFIRMED, CHG_DATE, CHG_USER, CHG_TERM) " +
					" VALUES (?,?,?,?,?,?)";  
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranId);
			pstmt.setString(2, siteCode);
			/*pstmt.setString(3, ptcn);
			pstmt.setString(4, waveId);*/
			pstmt.setString(3, "N");
			pstmt.setDate(4, currDate);
			pstmt.setString(5, chgUser );
			pstmt.setString(6, chgTerm );
			if( pstmt.executeUpdate() > 0)
			{
				System.out.println(" insertion in DEALLOC_ART successfully ");
			}			
			pstmt.close();pstmt = null;
			
			sql = "";
			sql = "INSERT INTO DEALLOC_ART_DET (TRAN_ID, LINE_NO, CARTON_NO, PICK_ORDER, LINE_NO__PICK, ITEM_CODE, QUANTITY, " +
					" LOC_CODE, LOT_NO, WAVE_ID, PTCN, SALE_ORDER, LINE_NO__SORD, LOT_SL, PICK_TYPE) VALUES (?,?,?,?, ?,?,?,?,?,?,?,?,?,?,?)";  
			pstmt = conn.prepareStatement(sql);
			
			sql = " SELECT (QUANTITY - CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE DEALLOC_QTY  END) AS QUANTITY " +
					  " FROM PICK_ORD_DET WHERE PICK_ORDER = ? AND LINE_NO = ?";
			pstmt2 = conn.prepareStatement(sql);
 
			
			parentNodeList = detailDom.getElementsByTagName("Detail2");
			parentNodeListLength = parentNodeList.getLength(); 
			System.out.println("parentNodeListLength------------------->"+parentNodeListLength);		
			
			for (int selectedRow = 0; selectedRow < parentNodeListLength; selectedRow++)
			{
				
				parentNode = parentNodeList.item(selectedRow);			
				childNodeList = parentNode.getChildNodes();
				childNodeListLength = childNodeList.getLength();
				System.out.println("childNodeListLength---->>> "+ childNodeListLength);
				
				for (int childRow = 0; childRow < childNodeListLength; childRow++)
				{
					childNode = childNodeList.item(childRow);
					childNodeName = childNode.getNodeName();
 					System.out.println("childNodeName---->>> "+ childNodeName);
					
					if (childNodeName.equals("carton_no"))
					{
						cartonNo = childNode.getFirstChild().getNodeValue();
					}

					if (childNodeName.equals("pick_order"))
					{
						pickOrder = childNode.getFirstChild().getNodeValue();
					}

					if (childNodeName.equals("line_no__pick"))
					{
						lineNoPick = childNode.getFirstChild().getNodeValue();
					}
					if (childNodeName.equals("item_code"))
					{
						itemCode = childNode.getFirstChild().getNodeValue();
					}
					if (childNodeName.equals("pick_type"))
					{
						if(childNode.getFirstChild() != null)
						{
							pickType = childNode.getFirstChild().getNodeValue();
						}						
					}
					if (childNodeName.equals("quantity"))
					{	
						if(childNode.getFirstChild() != null)
						{							
							quantity = Double.parseDouble(childNode.getFirstChild().getNodeValue());
							System.out.println(" quantity ["+quantity+"]");							
						}
						else
						{
							quantity = 0.0;
						}
						
						//quantity = Double.parseDouble(childNode.getFirstChild().getNodeValue());
						System.out.println("quantity *======>"+quantity);
					}
					if (childNodeName.equals("loc_code"))
					{
						locCode = childNode.getFirstChild().getNodeValue();
					}
					if (childNodeName.equals("sale_order"))
					{
						saleOrder = childNode.getFirstChild().getNodeValue();
					}
					if (childNodeName.equals("line_no__sord"))
					{
						lineNoSord = childNode.getFirstChild().getNodeValue();
					}
					if (childNodeName.equals("lot_no"))
					{
						lotNo = childNode.getFirstChild().getNodeValue();
					}	
					if (childNodeName.equals("wave_id"))
					{
						waveId = childNode.getFirstChild().getNodeValue();
					}
					if (childNodeName.equals("ptcn"))
					{
						ptcn = childNode.getFirstChild().getNodeValue();
					}
					if (childNodeName.equals("lot_sl"))
					{
						lotSl = childNode.getFirstChild().getNodeValue();
					}
					if (childNodeName.equals("carton_pick_order"))
					{
						cartonPickOrder = childNode.getFirstChild().getNodeValue();
					}
					if (childNodeName.equals("carton_pick_line"))
					{
						cartonPickLine = childNode.getFirstChild().getNodeValue();
					}
			
				}
				
				System.out.println("cartonPickOrder ["+cartonPickOrder+"] cartonPickLine ["+cartonPickLine+"]");
				if( quantity <= 0)
				{
					System.out.println(" quantity quantity zero ");
					errString="INVDEALLQ1";
					errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);
					//errString = getError(saleOrder,errString,conn);
					return errString;
				}
				else if( "A".equalsIgnoreCase(pickType))
				{
					pstmt2.setString(1, cartonPickOrder);
					pstmt2.setString(2, cartonPickLine);
					rs = pstmt2.executeQuery();
					if( rs.next())
					{
						realQty = rs.getDouble("QUANTITY");
					}
					rs.close(); rs = null;
					pstmt2.clearParameters();
					
					if( quantity > realQty )
					{
						errString="INVDEALLQ2";
						errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);						
						return errString;
					}
				}
				else if( "C".equalsIgnoreCase(pickType) || "M".equalsIgnoreCase(pickType) )
				{
					HashMap itmVolumeMap = getItemVoumeMap(itemCode,"",conn);
					double packSizeActive = (Double)itmVolumeMap.get("PACK_SIZE");
					System.out.println(" packSizeActive ["+packSizeActive+"] quantity ["+quantity+"]");
					if( packSizeActive != quantity)
					{
						errString="INVDEALLQ3";
						errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);						
						return errString;
					}
				}
				else if( "S".equalsIgnoreCase(pickType) )
				{
					double packSizeActive = 0.0;
					sql = "SELECT QUANTITY FROM REPL_ORD_DET WHERE REPL_ORDER = ? AND LINE_NO = ?";					
					pstmt1 = conn.prepareStatement(sql);
					pstmt1.setString(1, cartonPickOrder);
					pstmt1.setString(2, cartonPickLine);
					rs = pstmt1.executeQuery();
					if( rs.next())
					{
						packSizeActive = rs.getDouble("QUANTITY");
					}
					rs.close(); rs = null;
					pstmt1.close(); pstmt1 = null;
					
					if(packSizeActive !=  quantity)
					{
						errString="INVDEALLQ4";
						errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);						
						return errString;
					}
					
				}
				// CHECKING QUANTITY AGAINST 
				if( !"S".equalsIgnoreCase(pickType))
				{
					pndInvStat  = discommon.getDisparams("999999","PND_INVSTAT",conn);
					sql = "SELECT LOC_CODE FROM LOCATION WHERE LOC_CODE = ? AND INV_STAT = ? ";
					pstmt1 = conn.prepareStatement(sql);
					pstmt1.setString(1, locCode);
					pstmt1.setString(2, pndInvStat);
					rs = pstmt1.executeQuery();
					if( rs.next())
					{
						errString="INVDEALLQ5";
						errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);						
						return errString;
					}
					rs.close(); rs = null;
					pstmt1.close(); pstmt1 = null;
				}
				
				String refId = checkForReplOrPick(pickOrder, conn);
				if("REPL".equalsIgnoreCase(refId))
				{
					sql = "SELECT (CASE WHEN RIH.CONFIRMED IS NULL THEN 'N' ELSE RIH.CONFIRMED END) AS CONFIRMED " +
							" FROM REPL_ISS_DET RID, REPL_ISS_HDR RIH WHERE RID.REPL_ORDER = RIH.REPL_ORDER " +
							" AND RID.REPL_ORDER = ? AND RID.LINE_NO__ORD = ? AND RID.SITE_CODE = ? AND RID.ITEM_CODE = ? " +
							" AND RID.LOC_CODE = ? AND RID.LOT_NO = ? AND RID.LOT_SL = ? ";
					pstmt1 = conn.prepareStatement(sql);
					pstmt1.setString(1, pickOrder);
					pstmt1.setString(2, lineNoPick);
					pstmt1.setString(3, siteCode);
					pstmt1.setString(4, itemCode);
					pstmt1.setString(5, locCode);
					pstmt1.setString(6, lotNo);
					pstmt1.setString(7, lotSl);
					rs = pstmt1.executeQuery();
					if( rs.next())
					{
						confirmed = rs.getString("CONFIRMED"); 						
						if("N".equalsIgnoreCase(confirmed))
						{
							errString="INVREFIDDE";
							errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);						
							return errString;
						}
					}
					rs.close(); rs = null;
					pstmt1.close(); pstmt1 = null;
				}				
				//changed by sankara on 05/06/14 validation for pick dealloc start.
				if ( "C".equalsIgnoreCase(pickType) || "M".equalsIgnoreCase(pickType) || "A".equalsIgnoreCase(pickType) )
				{
					System.out.println("pickOrder::::: Changed By Pragyan["+pickOrder+"]");
					System.out.println("lineNoPick:::::Changed By Pragyan["+lineNoPick+"]");
					int pickCount = 0;
					sql = " SELECT COUNT(*) AS COUNT FROM PICK_ORD_DET PO, PICK_ISS_HDR PH, PICK_ISS_DET PT WHERE PT.PICK_ORDER = PO.PICK_ORDER " +
							" AND PT.LINE_NO__ORD = PO.LINE_NO AND PT.PICK_ORDER = PH.PICK_ORDER AND PH.CONFIRMED = 'N' AND ( PO.STATUS <>'C' OR PO.STATUS IS NULL ) " +
							" AND (PO.QUANTITY - (CASE WHEN PO.DEALLOC_QTY IS NULL THEN 0 ELSE PO.DEALLOC_QTY END )) > 0 " +
							//Changed By Pragyan 02-JUL-14 (Issue tracker No 404)To get proper count from pick order and pick line no.
							//" AND PT.PICK_ORDER = ? AND PT.LINE_NO = ? " ;					
							" AND PO.PICK_ORDER = ? AND PO.LINE_NO = ? " ;
					
					pstmt1 = conn.prepareStatement(sql);
					pstmt1.setString(1, pickOrder);
					pstmt1.setString(2, lineNoPick);
					rs = pstmt1.executeQuery();
					if(rs.next())
					{
						pickCount = rs.getInt("COUNT");
						System.out.println("pickCount::::::"+pickCount);
					}
					if(pickCount > 0)
					{
						errString="VPPICKNOTA";
						errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);						
						if(errString.trim().length() > 0)
						{
							errString = getError(pickOrder,lineNoPick,errString,conn);
							return errString; 
						}
					}
					rs.close(); rs = null;
					pstmt1.close(); pstmt1 = null;
				}
				//changed by sankara on 05/06/14 validation for pick dealloc end.
				pstmt.setString(1, tranId);
				pstmt.setInt(2, ++tranLineNo);
				pstmt.setString(3, cartonNo);
				/*pstmt.setString(4, pickOrder);
				pstmt.setString(5, lineNoPick);*/
				pstmt.setString(4, cartonPickOrder);
				pstmt.setString(5, cartonPickLine);
				pstmt.setString(6, itemCode);
				pstmt.setDouble(7, quantity);				
				pstmt.setString(8, locCode);
				pstmt.setString(9, lotNo);
				pstmt.setString(10, waveId);
				pstmt.setString(11, ptcn);
				pstmt.setString(12, saleOrder);
				pstmt.setString(13, lineNoSord);	
				pstmt.setString(14, lotSl);
				pstmt.setString(15, pickType);
				if( pstmt.executeUpdate() > 0 )
				{
					System.out.println(" insert in DEALLOC_ART_DET succesfully done ");
				}
				else
				{
					throw new Exception("Insertion fail in DEALLOC_ART_DET table");
				}
				pstmt.clearParameters();
			}
			if( pstmt != null )
			{
				pstmt.close();
				pstmt = null;
			}			
			if(pstmt2 != null)
			{
				pstmt2.close(); pstmt2 = null;
			}
			
			AppConnectParm appConnect = new AppConnectParm();
			Properties p = appConnect.getProperty();
			InitialContext ctx = new InitialContext(p);
			deallocArtConfLocal = (DeallocArtConfLocal)ctx.lookup("ibase/DeallocArtConf/local"); 
			errString = deallocArtConfLocal.confirm(tranId, xtraParams, "", conn, false);
			System.out.println(" ************ errString ******** ["+errString+"]");		
			/*if (errString.trim().length() > 0)
			{
				errString = "";
			}*/
			
		}//try end	
		catch(Exception e)
		{
			e.printStackTrace();
			try
			{
				if ( conn != null && !conn.isClosed())
				{
					conn.rollback();
				}
			}
			catch(Exception se )
			{
				se.printStackTrace();
				e = se;
			}			
			errString = e.getMessage();			
			throw new ITMException(e);
		}
		finally
		{
			System.out.println("Closing Connection....");
			try
			{
				if (errString == null || errString.trim().length() == 0 )
				{	
					conn.commit();
					//errString="VTCOMPL";
					errString="VTSUCC1";
					errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);
					//return errString;				
				}			
				else
				{
					System.out.println("errString.indexOf(<Errors>)---------------"+errString.indexOf("<Errors>"));
					/*if(errString.indexOf("<Errors>") == -1 )
					{*/
						if( errString.trim().length() > 0)
						{
							conn.rollback();
							System.out.println("Inside Block for ErrOr Not Found---------------");
							//changed by sankara on 21/06/14 display proper error details
							String errorString = errString;
							System.out.println("errorStringsankara:::::::::"+errorString);
							errString="VTPRCERR";
							errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);
							//changed by sankara on 21/06/14 display proper error details start.
							if( errString.trim().length() > 0 )
							{	
								System.out.println("calling our own ex method......");
								errString = printExceptionMessage( errString, conn, errorString);
								return errString;
							}
							//changed by sankara on 21/06/14 display proper error details end.
						}
					//}			
										
				}
				if( rs != null )
				{
					rs.close();
					rs = null;
				}
				if( pstmt != null )
				{
					pstmt.close();
					pstmt = null;
				}
				if( pstmt1 != null )
				{
					pstmt1.close();
					pstmt1 = null;
				}
				if( pstmt2 != null )
				{
					pstmt2.close();
					pstmt2 = null;
				}
				if( conn != null )
				{
					conn.close();
					conn = null;
				}
			}
			catch(Exception e)
			{
				errString = e.getMessage();
				e.printStackTrace();
				return errString ;
			
			}
			/*if (errString == null || errString.trim().length() == 0 || errString.contains("VTSUCC1"))
			{				
				//errString="VTCOMPL";
				errString="VTSUCC1";
				errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);
				//return errString;				
			}			
			else
			{
				System.out.println("errString.indexOf(<Errors>)---------------"+errString.indexOf("<Errors>"));
				if(errString.indexOf("<Errors>") == -1 )
				{
					if( errString.trim().length() > 0)
					{
						System.out.println("Inside Block for ErrOr Not Found---------------");
						errString="VTPRCERR";
						errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);
					}
				}
				
				return errString;
				
			}*/
			
		}
		
		return errString;	
	}	
	
	private String checkNull( String input )
	{
		if( input == null )
		{
			input = "";
		}
		return input;
	}
	private HashMap getItemVoumeMap(String itemCode,String lotNo,Connection con)throws Exception
	{
		double packSize = 0,itemSize = 0,lotSize = 0;
		PreparedStatement pstmt = null;
		String sql="";
		ResultSet rs = null;
		double itmLen = 0,itmWidth = 0,itmHeight = 0,itemWeight = 0,lotLen = 0 ,lotHeight = 0,lotWidth = 0,lotWeight = 0;
		HashMap dataVolumeMap = new HashMap();
		
		try {
			
			sql = "SELECT I.LENGTH ITEM_LEN,I.WIDTH ITEM_WID,I.HEIGHT ITEM_HEIGHT,I.GROSS_WEIGHT ITEM_WEIGHT,"
				  +" L.LENGTH LITEM_LEN,L.WIDTH LITEM_WID,L.HEIGHT LITEM_HEIGHT,L.SHIPPER_SIZE SHIPSIZE,L.GROSS_WEIGHT LOT_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");
				itemWeight = rs.getDouble("ITEM_WEIGHT");
				lotLen = rs.getDouble("LITEM_LEN");
				lotWidth = rs.getDouble("LITEM_WID");
				lotHeight = rs.getDouble("LITEM_HEIGHT");				
				packSize = rs.getDouble("SHIPSIZE");				
				lotWeight = rs.getDouble("LOT_WEIGHT");				
			}
			
			//packSize = (lotHeight * lotWidth * lotLen)/(itmLen * itmWidth * itmHeight);
			/*itemSize = Math.floor(itmLen * itmWidth * itmHeight);
			lotSize = Math.floor((lotHeight * lotWidth * lotLen));*/
			itemSize = itmLen * itmWidth * itmHeight;
			lotSize = lotHeight * lotWidth * lotLen;
			
			dataVolumeMap.put("PACK_SIZE", packSize);
			dataVolumeMap.put("ITEM_SIZE", itemSize);
			dataVolumeMap.put("LOT_SIZE", lotSize);
			dataVolumeMap.put("ITEM_WEIGHT", itemWeight);
			dataVolumeMap.put("PACK_WEIGHT", lotWeight);
			
			if(pstmt != null)
			{
				pstmt.close();
				pstmt = null;
		
			}
			if(rs != null)
			{
				rs.close();
				rs = null;
		
			}
			
			
			
		} catch (Exception e) {			

			throw e;
		}
		finally
		{
			if(pstmt != null)
			{
				pstmt.close();
				pstmt = null;
		
			}
			if(rs != null)
			{
				rs.close();
				rs = null;
		
			}
		}
		
		return dataVolumeMap;
	}
	private String getTranId(String windowName, String siteCode, Connection conn) throws ITMException
	{
		String keyStringQuery = "";
		String xmlValues = "";
		String tranId = "";	
		ResultSet rSet = null ;
		PreparedStatement pstmt = null;
		CommonConstants commonConstants = new CommonConstants();

		java.sql.Timestamp currDate = null;

		try
		{
			
			keyStringQuery = "SELECT KEY_STRING, TRAN_ID_COL, REF_SER FROM TRANSETUP WHERE TRAN_WINDOW = ?";
	    	System.out.println("keyStringQuery--------->>"+keyStringQuery);
	    	
	    	pstmt = conn.prepareStatement(keyStringQuery);
	    	pstmt.setString(1,windowName);
	    	rSet = pstmt.executeQuery();	    	
			String tranSer1 = "";
			String keyString = "";
			String keyCol = "";
			if (rSet.next())
			{
				keyString = rSet.getString("KEY_STRING");
				keyCol = rSet.getString("TRAN_ID_COL");
				tranSer1 = rSet.getString("REF_SER");
			}
			

			SimpleDateFormat sdfAppl = new SimpleDateFormat(genericUtility.getApplDateFormat());

			currDate = new java.sql.Timestamp(System.currentTimeMillis());
			String currDateStr = sdfAppl.format(currDate);
			//End Changes by Gulzar on 5/12/2012
			
			System.out.println("keyString :"+keyString);
			System.out.println("keyCol :"+keyCol);
			System.out.println("tranSer1 :"+tranSer1);
			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 +		"<desp_date>" + currDateStr + "</desp_date>"; 
			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);
			// Creating wave_id 
			tranId = tg.generateTranSeqID(tranSer1, keyCol, keyString, conn);
			
			if(rSet != null)
			{
				rSet.close();
				rSet=null;
			}	
			if(pstmt != null)
			{
				pstmt.close();
				pstmt=null;
			}			
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{	
				if(rSet != null)
				{
					rSet.close();
					rSet=null;
				}	
				if(pstmt != null)
				{
					pstmt.close();
					pstmt=null;
				}				
			}
			catch(Exception e)
			{
				e.printStackTrace();
				throw new ITMException(e);
			}
		}
		return checkNull( tranId);
	}
	private String checkForReplOrPick(String refId, Connection conn )throws ITMException
	{
		String task = "";
		String sql = "";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try
		{
			sql = " SELECT PICK_ORDER FROM PICK_ORD_HDR WHERE PICK_ORDER = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, refId);			
			rs = pstmt.executeQuery();
			if( rs.next())
			{
				task = "PICK";
			}
			else
			{
				if( pstmt != null )
				{
					pstmt.close(); pstmt = null;
				}
				if( rs != null)
				{
					rs.close(); rs =null;
				}
				sql = " SELECT REPL_ORDER FROM REPL_ORD_HDR WHERE REPL_ORDER = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, refId);
				rs = pstmt.executeQuery();
				if( rs.next())
				{
					task = "REPL";
				}
				rs.close(); rs = null;
				pstmt.close(); pstmt = null;
			}
			if( rs != null)
			{
				rs.close(); rs = null;
			}
			if( pstmt != null)
			{
				pstmt.close(); pstmt = null;
			}
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if( rs != null)
				{
					rs.close(); rs = null;
				}
				if( pstmt != null)
				{
					pstmt.close(); pstmt = null;
				}
			}
			catch(Exception ex)
			{
				ex.printStackTrace();
				throw new ITMException(ex);
			}
		}
		return task;
	}
	private HashMap getLocCodForPick(String pickOrder, int pickLineNo, Connection conn) throws ITMException
	{
		String sql = "";
		String confirmed ="";
		String pickType = "";
		String locCode ="";
		String locCodeTo = "";
		String replOrder = "";
		String lotNo = "";
		String lotSl = "";
		String trfStatus = "";
		int currentLine = 0; 
		String currentReplOrder = "";
		int replOrderLine = 0;
		boolean isIssueDone = false;
		PreparedStatement pstmt = null, pstmt2 = null;
		ResultSet rs = null, rs2 = null;
		HashMap replIss = new HashMap();
		HashMap stockDetail = new HashMap();
		try
		{
			sql = " SELECT PIH.PICK_ORDER, PIH.PICK_TYPE, PIH.CONFIRMED, PID.LOC_CODE__TO  FROM PICK_ISS_HDR PIH, PICK_ISS_DET PID WHERE PIH.PICK_ORDER = ? " +
			     //changed by sankara on 12/06/14 added tran_id join
				//" AND PIH.PICK_ORDER = PID.PICK_ORDER";
				  " AND PIH.PICK_ORDER = PID.PICK_ORDER AND PIH.TRAN_ID = PID.TRAN_ID ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, pickOrder);
			rs = pstmt.executeQuery();
			confirmed = "";
			if( rs.next())
			{									
				pickType = rs.getString("PICK_TYPE");
				confirmed = rs.getString("CONFIRMED");
				System.out.println(" pickType ["+pickType+"] confirmed["+confirmed+"]");
				if( "Y".equalsIgnoreCase(confirmed))
				{
					locCode = rs.getString("LOC_CODE__TO");
					isIssueDone = true;					
					//return stockDetail;
				}				
			}
			if( rs != null )
			{
				rs.close(); rs = null;						
			}
			if( pstmt != null )
			{					
				pstmt.close(); pstmt = null;	
			}		
			
			if( !isIssueDone )
			{
				replIss = new HashMap();
				replIss = isParentReplExist(pickOrder, pickLineNo, conn);
				if("YES".equalsIgnoreCase(replIss.get("isRepl").toString()))
				{
					replOrder = replIss.get("repl_order").toString();
					replOrderLine = Integer.parseInt(replIss.get("repl_order_line").toString());					
					System.out.println(" 88 replOrder ["+replOrder+"] replOrderLine ["+replOrderLine+"]");
					HashMap replParent = new HashMap();
					replParent = isParentExistAndConfirmed(replOrder,replOrderLine, conn);						
					confirmed = replParent.get("confirmed").toString();//confirmed						
					System.out.println("  confirmed ["+confirmed+"] paren_exist ["+replParent.get("paren_exist").toString()+"] ");
					if("Y".equalsIgnoreCase(replParent.get("confirmed").toString()))
					{
						locCode = replParent.get("loc_code__to").toString();
						lotNo = replParent.get("lot_no").toString();
						lotSl = replParent.get("lot_sl").toString();
						//System.out.println(" locCode ["+locCode+"] lotNo ["+lotNo+"] lotSl ["+lotSl+"]");	
						//Updating pick_order
						//updatePickOrder(pickOrder, pickLineNo, DeallocQty, conn);	
						stockDetail.put("loc_code", locCode);
						stockDetail.put("ref_id", pickOrder);
						stockDetail.put("ref_line_no", pickLineNo);
						
					}
					else if ("N".equalsIgnoreCase(confirmed) && "NO".equalsIgnoreCase(replParent.get("paren_exist").toString()))        //parentExist.put("paren_exist", "NO");
					{
						locCode = replParent.get("loc_code__to").toString();
						lotNo = replParent.get("lot_no").toString();
						lotSl = replParent.get("lot_sl").toString();
						//System.out.println(" locCode ["+locCode+"] lotNo ["+lotNo+"] lotSl ["+lotSl+"]");
						
						//updateReplOrder(replOrder, siteCode, itemCode, locCodeTo, lotNo, lotSl, DeallocQty, conn);	
						//updatePickOrder(pickOrder, pickLineNo, DeallocQty, conn);
						stockDetail.put("loc_code", locCode);
						stockDetail.put("ref_id", replOrder);
						stockDetail.put("ref_line_no", replOrderLine);
					}
					else if ( "N".equalsIgnoreCase(confirmed) && "YES".equalsIgnoreCase(replParent.get("paren_exist").toString()))
					{
						System.out.println(" repl order going to search it's ["+replOrder+"] parent ");
						
						currentReplOrder = replOrder;
						currentLine = replOrderLine;
						replOrder = "";
						replOrder = replParent.get("pick_order").toString();
						replOrderLine = Integer.parseInt(replParent.get("line_no__pick").toString());
						System.out.println(" new repl order going to serch it's parent ["+replOrder+"]");
						
						replParent.clear();
						replParent = isParentExistAndConfirmed(replOrder,replOrderLine, conn);
						
						confirmed = "";
						confirmed = replParent.get("confirmed").toString();
						System.out.println(" *-* confirmed ["+confirmed+"]");
						if("Y".equalsIgnoreCase(confirmed))
						{
							locCode = replParent.get("loc_code__to").toString();
							lotNo = replParent.get("lot_no").toString();
							lotSl = replParent.get("lot_sl").toString();
							//System.out.println(" locCode ["+locCode+"] lotNo ["+lotNo+"] lotSl ["+lotSl+"]");
							
							//updatePickOrder(pickOrder, pickLineNo, DeallocQty, conn);	
							stockDetail.put("loc_code", locCode);
							stockDetail.put("ref_id", currentReplOrder);
							stockDetail.put("ref_line_no", currentLine);
						}
						else if ("N".equalsIgnoreCase(confirmed) && "NO".equalsIgnoreCase(replParent.get("paren_exist").toString()))        //parentExist.put("paren_exist", "NO");
						{
							locCode = replParent.get("loc_code__to").toString();
							lotNo = replParent.get("lot_no").toString();
							lotSl = replParent.get("lot_sl").toString();
							//System.out.println(" locCode ["+locCode+"] lotNo ["+lotNo+"] lotSl ["+lotSl+"]");
							
							//updateReplOrder(replOrder, siteCode, itemCode, locCodeTo, lotNo, lotSl, DeallocQty, conn);
							//updatePickOrder(pickOrder, pickLineNo, DeallocQty, conn);
							stockDetail.put("loc_code", locCode);
							stockDetail.put("ref_id", replOrder);
							stockDetail.put("ref_line_no", replOrderLine);
						}
						else if ("N".equalsIgnoreCase(confirmed) && "YES".equalsIgnoreCase(replParent.get("paren_exist").toString()))        //parentExist.put("paren_exist", "NO");
						{
							replOrder = "";
							replOrder = replParent.get("pick_order").toString();
							replOrderLine = Integer.parseInt(replParent.get("line_no__pick").toString());
							System.out.println(" DSO repl order going to serch it's parent ["+replOrder+"]");
							
							replParent.clear();
							replParent = isParentExistAndConfirmed(replOrder,replOrderLine, conn);
							
							confirmed = "";
							confirmed = replParent.get("confirmed").toString();
							System.out.println(" *-* confirmed ["+confirmed+"]");
							
							if("Y".equalsIgnoreCase(confirmed))
							{
								locCode = replParent.get("loc_code__to").toString();
								lotNo = replParent.get("lot_no").toString();
								lotSl = replParent.get("lot_sl").toString();
								//System.out.println(" locCode ["+locCode+"] lotNo ["+lotNo+"] lotSl ["+lotSl+"]");
								
								//updatePickOrder(pickOrder, pickLineNo, DeallocQty, conn);	
								stockDetail.put("loc_code", locCode);
								stockDetail.put("ref_id", currentReplOrder);
								stockDetail.put("ref_line_no", currentLine);
							}
							else if ("N".equalsIgnoreCase(confirmed) /*&& "NO".equalsIgnoreCase(replParent.get("paren_exist").toString())*/)        //parentExist.put("paren_exist", "NO");
							{
								locCode = replParent.get("loc_code__to").toString();
								lotNo = replParent.get("lot_no").toString();
								lotSl = replParent.get("lot_sl").toString();
								//System.out.println(" locCode ["+locCode+"] lotNo ["+lotNo+"] lotSl ["+lotSl+"]");
								
								//updateReplOrder(replOrder, siteCode, itemCode, locCodeTo, lotNo, lotSl, DeallocQty, conn);
								//updatePickOrder(pickOrder, pickLineNo, DeallocQty, conn);
								stockDetail.put("loc_code", locCode);
								stockDetail.put("ref_id", replOrder);
								stockDetail.put("ref_line_no", replOrderLine);
							}
							
							
							//System.out.println(" locCode ["+locCode+"] lotNo ["+lotNo+"] lotSl ["+lotSl+"]");
							
							//updateReplOrder(replOrder, siteCode, itemCode, locCodeTo, lotNo, lotSl, DeallocQty, conn);
							//updatePickOrder(pickOrder, pickLineNo, DeallocQty, conn);
							/*stockDetail.put("loc_code", locCode);
							stockDetail.put("ref_id", replOrder);
							stockDetail.put("ref_line_no", replOrderLine);*/
						}
					}
				}
				else
				{
					sql = "SELECT LOC_CODE FROM PICK_ORD_DET WHERE PICK_ORDER = ? AND LINE_NO = ?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,pickOrder);
					pstmt.setInt(2, pickLineNo);
					rs = pstmt.executeQuery();
					if( rs.next())
					{
						locCode = rs.getString("LOC_CODE");
					}
					else
					{
						stockDetail.put("loc_code", "");
						stockDetail.put("ref_id", "");
						stockDetail.put("ref_line_no", "");
					}
					
					rs.close(); rs = null;
					pstmt.close(); pstmt = null;
					
					stockDetail.put("loc_code", locCode);
					stockDetail.put("ref_id", pickOrder);
					stockDetail.put("ref_line_no", pickLineNo);
				}
			}
			else
			{
				//String status = "";
				//sql = "SELECT (CASE WHEN STATUS IS NULL THEN 'N' ELSE STATUS END) AS STATUS FROM PICK_ORD_DET WHERE PICK_ORDER = ?  ";
				/*sql =	" SELECT (CASE WHEN POD.STATUS IS NULL THEN 'N' ELSE POD.STATUS END) AS STATUS, PIH.PICK_TYPE, " +
						" ( CASE WHEN PIH.TRF_STATUS IS NULL THEN 'N' ELSE PIH.TRF_STATUS END ) AS TRF_STATUS " +
						" FROM PICK_ORD_DET POD, PICK_ISS_DET PID, PICK_ISS_HDR PIH " +
						" WHERE POD.PICK_ORDER = ? AND POD.LINE_NO = ? " +
						" AND POD.PICK_ORDER = PID.PICK_ORDER AND POD.LINE_NO = PID.LINE_NO__ORD " +
						" AND PID.PICK_ORDER = PIH.PICK_ORDER ";
					
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,pickOrder);
				pstmt.setInt(2,pickLineNo);
				rs = pstmt.executeQuery();
				while( rs.next())
				{
					status = rs.getString("STATUS");
					trfStatus = rs.getString("TRF_STATUS");
					pickType = rs.getString("PICK_TYPE");
					if( "N".equalsIgnoreCase(status))
					{
						break;
					}
				}
				rs.close(); rs = null;
				pstmt.close(); pstmt = null;*/
				boolean isAllTaskConfirmed = isAllTaskConf(pickOrder, conn);
				System.out.println( " isAllTaskConfirmed ["+isAllTaskConfirmed+"]");
				//if( ("C".equalsIgnoreCase(pickType) && "C".equalsIgnoreCase(status) ) || ("C".equalsIgnoreCase(status) && "Y".equalsIgnoreCase(trfStatus) && ( "A".equalsIgnoreCase(pickType) || "M".equalsIgnoreCase(pickType))))
				if( isAllTaskConfirmed )
				{
					sql = " SELECT DT.DESP_ID, DT.LINE_NO, DT.LOC_CODE FROM DESPATCH D, DESPATCHDET DT , PICK_ORD_DET POD , WAVE_TASK_DET WTD " +
							" WHERE POD.PICK_ORDER = ? AND POD.LINE_NO = ? AND POD.PICK_ORDER = WTD.REF_ID " +
							" AND POD.SALE_ORDER = WTD.SALE_ORDER " +
						// Changed by Sankara on 04-07-13 added new join for getting correct location[Start..]	
							" AND DT.SORD_NO = WTD.SALE_ORDER " +
						// Changed by Sankara on 04-07-13 added new join for getting correct location[End..]	
							" AND WTD.WAVE_ID = D.WAVE_ID " +
							" AND D.DESP_ID = DT.DESP_ID ";
					
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,pickOrder);
					pstmt.setInt(2, pickLineNo);
					rs = pstmt.executeQuery();
					if( rs.next())
					{
						stockDetail.put("loc_code", rs.getString("LOC_CODE"));
						stockDetail.put("ref_id", rs.getString("DESP_ID"));
						stockDetail.put("ref_line_no", rs.getString("LINE_NO"));
					}
					rs.close(); rs = null;
					pstmt.close(); pstmt = null;
				}
				else
				{
					boolean isIssAvail = false;
					sql = "SELECT LOC_CODE FROM PICK_ISS_DET WHERE PICK_ORDER = ? AND line_no__ord = ?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,pickOrder);
					pstmt.setInt(2, pickLineNo);
					rs = pstmt.executeQuery();
					if( rs.next())
					{
						isIssAvail = true;
						stockDetail.put("loc_code", rs.getString("LOC_CODE"));
						stockDetail.put("ref_id", pickOrder);
						stockDetail.put("ref_line_no", pickLineNo);
					}
					rs.close(); rs = null;
					pstmt.close(); pstmt = null;
					
					if( !isIssAvail )
					{
						sql = "SELECT LOC_CODE FROM PICK_ORD_DET WHERE PICK_ORDER = ? AND LINE_NO = ?";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1,pickOrder);
						pstmt.setInt(2, pickLineNo);
						rs = pstmt.executeQuery();
						if( rs.next())
						{
							locCode = rs.getString("LOC_CODE");
						}
						rs.close(); rs = null;
						pstmt.close(); pstmt = null;
						
						stockDetail.put("loc_code", locCode);
						stockDetail.put("ref_id", pickOrder);
						stockDetail.put("ref_line_no", pickLineNo);
					}
				}
			}
			//System.out.println(" locCode ["+locCode+"] locCodeTo ["+locCodeTo+"]");
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if( rs != null )
				{
					rs.close(); rs = null;						
				}
				if( pstmt != null )
				{					
					pstmt.close(); pstmt = null;	
				}
			}
			catch( Exception ex)
			{				
				ex.printStackTrace();
				throw new ITMException(ex);
			}
		}
		return stockDetail;
	}
	private HashMap getLocCodForRepl(String refId, int pickLineNo, Connection conn) throws ITMException
	{
		String sql = "";
		String confirmed ="";
		String pickType = "";
		String locCode ="";
		String locCodeTo = "";
		String replOrder = "";
		String lotNo = "";
		String lotSl = "";
		String replPickOrder = "";
		int replOrderLine = 0;
		int replPickLine = 0;
		boolean isIssueDone = false;
		PreparedStatement pstmt = null, pstmt2 = null;
		ResultSet rs = null; // rs2 = null;
		//HashMap replIss = new HashMap();
		HashMap stockDetail = new HashMap();
		try
		{
			sql = " SELECT RIH.CONFIRMED, RID.LOC_CODE__TO  FROM REPL_ISS_HDR RIH, REPL_ISS_DET RID WHERE RIH.REPL_ORDER = ? " +
					" AND RIH.REPL_ORDER = RID.REPL_ORDER ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, refId);
			rs = pstmt.executeQuery();
			confirmed = "";
			if( rs.next())
			{			
				confirmed = rs.getString("CONFIRMED");
				System.out.println(" confirmed["+confirmed+"]");
				if( "Y".equalsIgnoreCase(confirmed))
				{
					locCode = rs.getString("LOC_CODE__TO");
					isIssueDone = true;
					//return locCode;
				}				
			}
			rs.close(); rs = null;
			pstmt.close(); pstmt = null;		
			
			if( !isIssueDone )
			{
				
				sql = "SELECT PICK_ORDER, LINE_NO__PICK FROM REPL_ORD_DET WHERE REPL_ORDER = ? AND LINE_NO = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, refId);
				pstmt.setInt(2, pickLineNo);
				rs = pstmt.executeQuery();
				if( rs.next() )
				{
					replPickOrder = checkNull(rs.getString("PICK_ORDER"));
					replPickLine = rs.getInt("LINE_NO__PICK");
				}
				rs.close(); rs = null;
				pstmt.close(); pstmt = null;
				System.out.println(" parent repl_order ["+replPickOrder+"] replPickLine ["+replPickLine+"]");
				
				if( replPickOrder.trim().length() > 0)
				{
				
					HashMap replParent = new HashMap();
					replParent = isParentExistAndConfirmed(replPickOrder,replPickLine, conn);	
					replOrder = replParent.get("pick_order").toString();
					replOrderLine = Integer.parseInt(replParent.get("line_no__pick").toString());
					confirmed = replParent.get("confirmed").toString();//confirmed						
					System.out.println("  confirmed ["+confirmed+"] paren_exist ["+replParent.get("paren_exist").toString()+"] repl_order ["+replParent.get("pick_order").toString()+"]");
					if("Y".equalsIgnoreCase(replParent.get("confirmed").toString()))
					{
						locCode = replParent.get("loc_code__to").toString();
						lotNo = replParent.get("lot_no").toString();
						lotSl = replParent.get("lot_sl").toString();
						//System.out.println(" locCode ["+locCode+"] lotNo ["+lotNo+"] lotSl ["+lotSl+"]");	
						
						stockDetail.put("loc_code", locCode);
						stockDetail.put("ref_id", refId);
						stockDetail.put("ref_line_no", pickLineNo);
					}
					else if ("N".equalsIgnoreCase(confirmed) /*&& "NO".equalsIgnoreCase(replParent.get("paren_exist").toString())*/)        //parentExist.put("paren_exist", "NO");
					{
						locCode = replParent.get("loc_code__to").toString();
						lotNo = replParent.get("lot_no").toString();
						lotSl = replParent.get("lot_sl").toString();
						System.out.println(" locCode ["+locCode+"] lotNo ["+lotNo+"] lotSl ["+lotSl+"]");
						
						//updateReplOrder(replOrder, siteCode, itemCode, locCodeTo, lotNo, lotSl, DeallocQty, conn);	
						//updatePickOrder(pickOrder, pickLineNo, DeallocQty, conn);
						stockDetail.put("loc_code", locCode);
						stockDetail.put("ref_id", replPickOrder);
						stockDetail.put("ref_line_no", replPickLine);
					}
					/*else if ( "N".equalsIgnoreCase(confirmed) && "YES".equalsIgnoreCase(replParent.get("paren_exist").toString()))
					{
						System.out.println(" repl order going to search it's ["+replOrder+"] parent ");
						replOrder = "";
						replOrder = replParent.get("pick_order").toString();
						replOrderLine = Integer.parseInt(replParent.get("line_no__pick").toString());
						System.out.println(" new repl order going to serch it's parent ["+replOrder+"] replOrderLine ["+replOrderLine+"]");
						
						replParent.clear();
						replParent = isParentExistAndConfirmed(replOrder,replOrderLine, conn);
						
						confirmed = "";
						confirmed = replParent.get("confirmed").toString();
						System.out.println(" *-* confirmed ["+confirmed+"]");
						if("Y".equalsIgnoreCase(confirmed))
						{
							locCode = replParent.get("loc_code__to").toString();
							lotNo = replParent.get("lot_no").toString();
							lotSl = replParent.get("lot_sl").toString();
							System.out.println(" locCode ["+locCode+"] lotNo ["+lotNo+"] lotSl ["+lotSl+"]");
							
							//updatePickOrder(pickOrder, pickLineNo, DeallocQty, conn);
							stockDetail.put("loc_code", locCode);
							stockDetail.put("ref_id", replOrder);
							stockDetail.put("ref_line_no", replOrderLine);						
						}
						else if ("N".equalsIgnoreCase(confirmed) && "NO".equalsIgnoreCase(replParent.get("paren_exist").toString()))        //parentExist.put("paren_exist", "NO");
						{
							locCode = replParent.get("loc_code__to").toString();
							lotNo = replParent.get("lot_no").toString();
							lotSl = replParent.get("lot_sl").toString();
							System.out.println(" locCode ["+locCode+"] lotNo ["+lotNo+"] lotSl ["+lotSl+"]");
							
							//updateReplOrder(replOrder, siteCode, itemCode, locCodeTo, lotNo, lotSl, DeallocQty, conn);
							//updatePickOrder(pickOrder, pickLineNo, DeallocQty, conn);
							stockDetail.put("loc_code", locCode);
							stockDetail.put("ref_id", replParent.get("pick_order").toString());
							stockDetail.put("ref_line_no", replOrderLine);
						}
					}*/
				}
				else
				{
					sql = "SELECT LOC_CODE FROM REPL_ORD_DET WHERE REPL_ORDER = ? AND LINE_NO = ?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,refId);
					pstmt.setInt(2, pickLineNo);
					rs = pstmt.executeQuery();
					if( rs.next())
					{
						locCode = rs.getString("LOC_CODE");
					}
					rs.close(); rs = null;
					pstmt.close(); pstmt = null;
					stockDetail.put("loc_code", locCode);
					stockDetail.put("ref_id", refId);
					stockDetail.put("ref_line_no", pickLineNo);
				}
					
			}	
			else
			{
				boolean isAllTaskConfirmed = isAllTaskConf(refId, conn);
				System.out.println( " isAllTaskConfirmed ["+isAllTaskConfirmed+"]");
				//if( ("C".equalsIgnoreCase(pickType) && "C".equalsIgnoreCase(status) ) || ("C".equalsIgnoreCase(status) && "Y".equalsIgnoreCase(trfStatus) && ( "A".equalsIgnoreCase(pickType) || "M".equalsIgnoreCase(pickType))))
				if( isAllTaskConfirmed )
				{
					sql = " SELECT DT.DESP_ID, DT.LINE_NO, DT.LOC_CODE FROM DESPATCH D, DESPATCHDET DT , REPL_ORD_DET ROD , WAVE_TASK_DET WTD " +
							" WHERE ROD.REPL_ORDER = ? AND ROD.LINE_NO = ? AND ROD.REPL_ORDER = WTD.REF_ID " +
							" AND ROD.SALE_ORDER = WTD.SALE_ORDER AND WTD.WAVE_ID = D.WAVE_ID  AND D.DESP_ID = DT.DESP_ID ";
					
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,refId);
					pstmt.setInt(2, pickLineNo);
					rs = pstmt.executeQuery();
					if( rs.next())
					{
						stockDetail.put("loc_code", rs.getString("LOC_CODE"));
						stockDetail.put("ref_id", rs.getString("DESP_ID"));
						stockDetail.put("ref_line_no", rs.getString("LINE_NO"));
					}
					rs.close(); rs = null;
					pstmt.close(); pstmt = null;
				}
				else
				{
					boolean isIssAvail = false;
					sql = "SELECT LOC_CODE__TO FROM REPL_ISS_DET WHERE REPL_ORDER = ? AND LINE_NO__ORD = ?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,refId);
					pstmt.setInt(2, pickLineNo);
					rs = pstmt.executeQuery();
					if( rs.next())
					{
						isIssAvail = true;
						stockDetail.put("loc_code", rs.getString("LOC_CODE__TO"));
						stockDetail.put("ref_id", refId);
						stockDetail.put("ref_line_no", pickLineNo);
					}
					rs.close(); rs = null;
					pstmt.close(); pstmt = null;
					
					if( !isIssAvail )
					{
						sql = "SELECT LOC_CODE FROM REPL_ORD_DET WHERE REPL_ORDER = ? AND LINE_NO = ?";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1,refId);
						pstmt.setInt(2, pickLineNo);
						rs = pstmt.executeQuery();
						if( rs.next())
						{
							locCode = rs.getString("LOC_CODE");
						}
						rs.close(); rs = null;
						pstmt.close(); pstmt = null;
						
						stockDetail.put("loc_code", locCode);
						stockDetail.put("ref_id", refId);
						stockDetail.put("ref_line_no", pickLineNo);
					}
				}
				
			}
			System.out.println(" stockDetail ["+stockDetail+"]");
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if( rs != null )
				{
					rs.close(); rs = null;						
				}
				if( pstmt != null )
				{					
					pstmt.close(); pstmt = null;	
				}
			}
			catch( Exception ex)
			{				
				ex.printStackTrace();
				throw new ITMException(ex);
			}
		}
		return stockDetail;
	}
	private HashMap isParentReplExist(String pickOrder, int pickOrderLine, Connection conn) throws ITMException
	{
		String sql = "";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		boolean isRepl = false;
		HashMap replOrder = new HashMap();
		String tempReplOrder = "";
		try
		{
			sql = "SELECT REPL_ORDER , LINE_NO__REPL FROM PICK_ORD_DET WHERE PICK_ORDER = ? AND LINE_NO = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, pickOrder);
			pstmt.setInt(2, pickOrderLine);			
			rs = pstmt.executeQuery();
			if( rs.next() )
			{
				tempReplOrder = checkNull(rs.getString("REPL_ORDER"));				
			}			
			if( tempReplOrder.trim().length() > 0 )
			{
				replOrder.put("isRepl", "YES");
				replOrder.put("repl_order", rs.getString("REPL_ORDER"));
				replOrder.put("repl_order_line", rs.getString("LINE_NO__REPL"));
			}
			else
			{
				replOrder.put("isRepl", "NO");
				replOrder.put("repl_order", "");
				replOrder.put("repl_order_line", "");
			}
			rs.close();rs = null;
			pstmt.close(); pstmt = null;			
			System.out.println(" is replOrder exist ["+replOrder+"]");				
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}		
		return replOrder;
	}
	
	private HashMap isParentExistAndConfirmed(String replOrder,int replOrderLine, Connection conn) throws ITMException
	{
		String sql = "";
		String pickOrder = "";
		int lineNoPick = 0;
		String confirmed = "";
		PreparedStatement pstmt = null, pstmt1 = null, pstmt2 = null;
		ResultSet rs = null, rs1 = null, rs2 = null;
		HashMap parentExist =  new HashMap();
		try
		{
			sql = "SELECT PICK_ORDER, LINE_NO__PICK FROM REPL_ORD_DET WHERE REPL_ORDER = ? AND LINE_NO = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, replOrder);
			pstmt.setInt(2, replOrderLine);
			rs = pstmt.executeQuery();
			if( rs.next() )
			{
				pickOrder = checkNull(rs.getString("PICK_ORDER"));
				lineNoPick = rs.getInt("LINE_NO__PICK");
			}
			
			if( pickOrder.trim().length() > 0)
			{				
				parentExist.put("paren_exist", "YES");
				parentExist.put("pick_order", pickOrder);
				parentExist.put("line_no__pick", lineNoPick);
			}
			else
			{
				parentExist.put("paren_exist", "NO");
				parentExist.put("pick_order", " ");	
				parentExist.put("line_no__pick", 0);
			}
			rs.close();rs = null;
			pstmt.close(); pstmt = null;
				
			/*if( pickOrder.trim().length() > 0 )
			{*/				
				/*parentExist.put("paren_exist", "YES");
				parentExist.put("pick_order", pickOrder);*/
				
				sql = "";
				sql = " SELECT RID.REPL_ORDER, RIH.CONFIRMED, RID.ITEM_CODE, RID.LOT_NO, RID.LOT_SL, RID.LOC_CODE__TO, RID.LOC_CODE " +
						" FROM REPL_ISS_DET RID, REPL_ISS_HDR RIH WHERE RIH.REPL_ORDER = ? " +
						" AND RIH.TRAN_ID = RID.TRAN_ID AND RID.REPL_ORDER = ? AND RID.LINE_NO__ORD = ? ";
				pstmt1 = conn.prepareStatement(sql);
				pstmt1.setString(1, replOrder);
				pstmt1.setString(2, replOrder);
				pstmt1.setInt(3, replOrderLine);
				rs1 = pstmt1.executeQuery();
				if( rs1.next() )
				{
					confirmed = checkNull(rs1.getString("CONFIRMED"));
					System.out.println( replOrder+" is confirmed ["+confirmed+"]");
					if("Y".equalsIgnoreCase(confirmed))
					{
						parentExist.put("confirmed", confirmed);
						parentExist.put("item_code", rs1.getString("ITEM_CODE"));
						parentExist.put("lot_no", rs1.getString("LOT_NO"));
						parentExist.put("lot_sl", rs1.getString("LOT_SL"));
						parentExist.put("loc_code__to", rs1.getString("LOC_CODE__TO"));
						
					}
					else
					{
						parentExist.put("confirmed", "N");
						parentExist.put("item_code", rs1.getString("ITEM_CODE"));
						parentExist.put("lot_no", rs1.getString("LOT_NO"));
						parentExist.put("lot_sl", rs1.getString("LOT_SL"));
						parentExist.put("loc_code__to", rs1.getString("LOC_CODE"));
					}
				}
				else
				{
					//Getting record from repl_order 
					sql = "";
					sql = " SELECT ITEM_CODE, LOC_CODE, LOT_NO, LOT_SL FROM REPL_ORD_DET WHERE REPL_ORDER = ? AND LINE_NO = ?";
					pstmt2 = conn.prepareStatement(sql);
					pstmt2.setString(1, replOrder);
					pstmt2.setInt(2, replOrderLine);
					rs2 = pstmt2.executeQuery();
					if( rs2.next() )
					{
						parentExist.put("item_code", rs2.getString("ITEM_CODE"));
						parentExist.put("lot_no", rs2.getString("LOT_NO"));
						parentExist.put("lot_sl", rs2.getString("LOT_SL"));
						parentExist.put("loc_code__to", rs2.getString("LOC_CODE"));
					}
					else
					{
						parentExist.put("item_code", "");
						parentExist.put("lot_no", "");
						parentExist.put("lot_sl", "");
						parentExist.put("loc_code__to", "");
					}
					parentExist.put("confirmed", "N");
					rs2.close();rs2 = null;
					pstmt2.close();pstmt2 = null;					
					
				}
				rs1.close();rs1 = null;
				pstmt1.close(); pstmt1 = null;
				
			//}
			/*else
			{*/
				
			//}
							
			System.out.println(" parentExist --> "+parentExist);
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if(rs != null)
				{
					rs.close();rs = null;
				}
				if( rs1 != null)
				{
					rs1.close(); rs1 = null;
				}
				if( rs2 != null)
				{
					rs2.close(); rs2 = null;
				}
				if( pstmt != null)
				{
					pstmt.close(); pstmt = null;
				}
				if( pstmt1 != null )
				{
					pstmt1.close(); pstmt1 = null;
				}
				if( pstmt2 != null )
				{
					pstmt2.close(); pstmt2 = null;
				}
			}
			catch(Exception ex)
			{
				ex.printStackTrace();
				throw new ITMException(ex);				
			}
		}
		
		return parentExist;
	}
	
	private boolean isAllTaskConf(String refId, 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;
		boolean isAllTaskCompleted = false;
		
		try
		{
			sql = "SELECT WAVE_ID, SALE_ORDER FROM WAVE_TASK_DET WHERE REF_ID = ? ";			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, refId);
			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 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)
			{
				isAllTaskCompleted = true;
			}
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}		
		return isAllTaskCompleted;
	}
	//changed by sankara on 05/06/14 for pick deallocation start.
	private  String getError(String Pickorder,String lineNoPick,String errString,Connection conn)  throws ITMException, Exception
	{
		String mainStr ="";       
		try
		{
			System.out.println("errString:::::::::::"+errString);
			String begPart = errString.substring(0,errString.indexOf("</message>")+10);
			System.out.println("begPart::::::"+begPart);
			String firstdescr = errString.substring(errString.lastIndexOf("<description>"), errString.indexOf("<description>")+13);
			System.out.println("firstdescr::::::::"+firstdescr);
			String seconddescr = errString.substring(errString.indexOf("<description>")+13, errString.indexOf("</description>"));
			String endDesc = errString.substring(errString.indexOf("</description>"));
			System.out.println("endDesc::::::::"+endDesc);
			System.out.println("sankara");
			String description = " The Pick Order [" +Pickorder+ "] Line Number [" +lineNoPick+ "] ";
			mainStr= begPart + firstdescr + description + seconddescr + endDesc ;
			System.out.println("mainStr:::::::::::::::::: "+mainStr);
			begPart = null;
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		return mainStr;
	}
	//changed by sankara on 21/06/14 for display proper error details bug fixing start.
	private String printExceptionMessage( String errString, Connection conn, String errorString)  throws ITMException 
	{
		String mainStr = "";  
		String errMessage = "";  
		String begPart = "";
		String firstdescr = "";
		String seconddescr = "";
		String endDesc = "";
		try
		{
			System.out.println("errString------->"+errString);
			System.out.println("errorString::::::"+errorString);
			if(errString != null && (errString.indexOf("</message>")+10 ) != -1 )
			{
				begPart = errString.substring(0,errString.indexOf("</message>")+10);
			}
			if(errString != null && errString.lastIndexOf("<description>") != -1 && (errString.indexOf("<description>")+13)!= 1 )
			{
				firstdescr = errString.substring(errString.lastIndexOf("<description>"), errString.indexOf("<description>")+13);
			}
			if(errorString !=null && (errorString.indexOf("<description>")+13) != -1  && errorString.indexOf("</description>") != -1 )
			{
				seconddescr = errorString.substring(errorString.indexOf("<description>")+13, errorString.indexOf("</description>"));
			}
			if(errString.indexOf("</description>") != -1)
			{
				endDesc = errString.substring(errString.indexOf("</description>"));
			}		
			mainStr =  begPart + firstdescr + seconddescr + endDesc ;
			System.out.println("mainStr::::::::"+mainStr);
			begPart = null;
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		return mainStr;
	}
	//changed by sankara on 21/06/14 for display proper error details bug fixing end.
	//changed by sankara on 05/06/14 for pick deallocation end.
}