package ibase.webitm.ejb.wms;

import ibase.system.config.ConnDriver;
import ibase.utility.CommonConstants;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.ejb.dis.DistCommon;
import ibase.webitm.reports.utility.XSDParser;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;

import java.io.File;
import java.rmi.RemoteException;
import java.sql.*;
import java.util.*;
import java.text.*;

import org.w3c.dom.Document;


public class ConsolidatToDoc {
	GenericUtility genericUtility = GenericUtility.getInstance();
	ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
	
	//Changed by Rohan on 10-06-12 to add xtraParams in process as argument for hazmap report to get chg user
	//public String process(String ptcn,Connection conn) throws RemoteException,ITMException
	public String process(String ptcn,Connection conn,String xtraParams) throws RemoteException,ITMException
	{
		String waveId = "", pickOrder = "", siteCode = "", itemCode = "", locCode = "", locCodeTo="";
		String lotNo = "", lotSl = "", saleOrder = "", despId = "";
		String sql = "", sql1 = "",sqlOrd="", partNo = "", siteCodeMfg = "", packCode = "", packInstr = ""; 		
		String errString = "", rateClg = "", custItemRef = "", rateStduom = "", lineType = "I";		
		String refId = "",refSer = "", refType = "";
		String mbaseUnit = "", unit = "", expLev = "",unitStd = "";
		String palletNo = "",sqlHdr = "";
		//Changed by Rohan on 06-10-12 to define variable for hazmap report.start
		String reportObjName = "";
		String sqlitem = "";
		String hazMaterial = "";
		String sqlPtcn = "";
		String ptcnReport = "";
		boolean isPick = false;
		PreparedStatement pstmtitem = null;
		ResultSet rsitem = null;
		String tranMode = "";
		String sqlTran = "";
		String sql2 = "";
		PreparedStatement pstmt2 = null;
		ResultSet rs2 = null;
		String target = "";
		String tarHaz = "";
		String source = "";
		String user = "";
		
		PreparedStatement pstmt = null, pstmt1 = null,pstmtHdr = null;
		ResultSet rs = null, rs1 = null;

		PreparedStatement pstmtSordItem = null;
		PreparedStatement pstmtSordDet = null;
		PreparedStatement pstmtStock= null;
		PreparedStatement pstmtItem= null;
		PreparedStatement pstmtIss= null;
		PreparedStatement pstmtSord= null;
		ResultSet rsSordItem = null;
		ResultSet rsSordDet = null;
		ResultSet rsStock = null;
		ResultSet rsItem = null;
		ResultSet rsIss = null;
		ResultSet rsSord = null;

		int lineNo = 0 , lineNoOrd = 0; 
		int update = 0, count = 0, totalNoArt = 0;
		double meffQty = 0, qtyStduom = 0,qtyStduom1= 0, convQtyStduom = 0, conRtuStduom = 0, convFact = 1, quantity = 0;
		double grossWeight = 0d, tareWeight = 0d, netWeight = 0d;//Gulzar on 29/12/11
		double rate = 0d;
		//Changed by sumit on 01/02/13 
		double convertQty = 0.0;
		double rateStd = 0.0;//added by sumit on 18/02/13
		int noArt = 0;
		double shipperSize = 0;
		double totalGrossWeight = 0,totalTareWeight = 0,totalNetWeight = 0; 
		boolean isError = false, isConfirmed = true;

		Timestamp mfgDate = null, expDate = null, retestDate = null;
		DistCommon disCommon = new DistCommon();

		try
		{
			System.out.println("*********inside try block** CONSOLIDATE TO DOCK *****");

			System.out.println("ptcn ======>["+ptcn+"]");

			SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
			java.util.Date sysDate = new java.util.Date();
			String sysDateStr = sdf.format(sysDate);
			//Changed by rohna on 13-10-12 to user from xtraparam
			user = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginCode");	

			if(ptcn == null || ptcn.trim().length() == 0)
			{
				isError = true;
				System.out.println("PTCN number is null");
				errString = itmDBAccessEJB.getErrorString("","NULLPTCNID","");
				return errString;
			}
			else
			{
				HashMap dataMap = getAllTaskID(ptcn,conn);
				System.out.println("dataMap====>"+dataMap);
				boolean isTasksConfirmed = isAllTaskConfirmed(dataMap,conn);
				ArrayList tempList = null;
				//Changed by Rohan on 10-11-12 for hazmaterial report
				ArrayList tempList1 = null;
				
				if(isTasksConfirmed)
				{

					
					despId = (String)((ArrayList)dataMap.get("S-DSP")).get(0);
					System.out.println("Despatch ID::::::::::::::: ["+despId+"]");

					boolean isShipExist = isShipmentExist(despId,conn);

					if(!isShipExist)
					{
						/*sql = "";
							sql =   "SELECT DISTINCT P.PICK_ORDER, P.LINE_NO__ORD, P.SITE_CODE, P.ITEM_CODE, P.LOC_CODE, P.LOT_NO, " +
									"P.LOT_SL, P.NO_ART, P.QUANTITY , PI.SALE_ORDER , W.WAVE_ID " +
									"FROM PICK_ISS_DET P, WAVE_TASK_DET W , PICK_ORD_HDR PI, WAVE_TASK WI " +
									"WHERE W.REF_ID  = P.PICK_ORDER " +
									"AND PI.WAVE_ID = W.WAVE_ID " +
									"AND W.WAVE_ID = WI.WAVE_ID " +
									"AND WI.PTCN = ? ";*/
						/*sql = "SELECT DISTINCT P.PICK_ORDER, P.LINE_NO__ORD, P.SITE_CODE, P.ITEM_CODE, P.LOC_CODE, " +
									"P.LOT_NO, P.LOT_SL, P.NO_ART, P.QUANTITY , PO.SALE_ORDER, W.WAVE_ID , POD.EXP_LEV " +
									"FROM PICK_ISS_DET P, WAVE_TASK_DET W , PICK_ORD_HDR PO , PICK_ORD_DET POD " +
									"WHERE P.PICK_ORDER = W.REF_ID " +
									"AND PO.PICK_ORDER = P.PICK_ORDER " +
									"AND POD.PICK_ORDER = PO.PICK_ORDER " +
									"AND POD.LINE_NO = P.LINE_NO " +
									"AND W.PTCN = ? ";
						 */
						sql1 =  "INSERT INTO DESPATCHDET (DESP_ID, LINE_NO, SORD_NO, LINE_NO__SORD, EXP_LEV, " +
								"ITEM_CODE__ORD, ITEM_CODE, LOT_NO, LOT_SL, QUANTITY__ORD, QUANTITY, LOC_CODE, " +
								"STATUS, CONV__QTY_STDUOM, UNIT__STD, UNIT, QUANTITY__STDUOM, QUANTITY_REAL, " +
								"RATE__STDUOM, PACK_INSTR, NO_ART, SITE_CODE, PACK_QTY, GROSS_WEIGHT, TARE_WEIGHT,  " +
								"NETT_WEIGHT, MFG_DATE, EXP_DATE, SITE_CODE__MFG, RATE__CLG, COST_RATE, LINE_TYPE, " +
								//Changed by sumit on 18/02/13 adding rate__std column 
								//"CONV__RTUOM_STDUOM, PACK_CODE, CUST_ITEM__REF, RETEST_DATE, PART_NO,PALLET_NO) " +
								"CONV__RTUOM_STDUOM, PACK_CODE, CUST_ITEM__REF, RETEST_DATE, PART_NO,PALLET_NO,RATE__STD ) " +
								"VALUES (?,?,?,?,?,?,?,?,?,?, ?,?,?,?,?,?,?,?,?,?, ?,?,?,?,?,?,?,?,?,?, ?,?,?,?,?,?,?,?,?) ";  //chnage done by Kunal Mandhre insert pallet no as per S Manoharan Sir instruction 

						pstmt1 = conn.prepareStatement(sql1);

						/*--Commented by gulzar on 29/12/11
							sqlOrd = "SELECT DISTINCT I.UNIT, I.MFR_PART_NO, S.GROSS_WEIGHT, S.TARE_WEIGHT, S.NET_WEIGHT, " +
												"S.MFG_DATE, S.EXP_DATE, S.SITE_CODE__MFG, S.PACK_CODE, S.RETEST_DATE, S.RATE, " +
												"SD.PACK_INSTR, SD.RATE__CLG, SD.CUST_ITEM__REF, SD.RATE__STDUOM, " +
												"SI.LINE_TYPE, ILP.SHIPPER_SIZE " +
												"FROM ITEM I, STOCK S, SORDER SO, SORDDET SD, SORDITEM SI, ITEM_LOT_PACKSIZE ILP " +
												"WHERE I.ITEM_CODE = ? " +
												"AND S.SITE_CODE = ? " +
												"AND S.LOC_CODE = ? " +
												"AND S.LOT_NO = ? " +
												"AND S.LOT_SL =  ? " +
												"AND S.ITEM_CODE = I.ITEM_CODE " +
												"AND SO.SALE_ORDER= ? " +
												"AND SD.SALE_ORDER = SO.SALE_ORDER " +
												"AND ILP.ITEM_CODE = I.ITEM_CODE " +
												"AND SI.SALE_ORDER = SO.SALE_ORDER " +
												"AND SI.LINE_NO =  ? " +
												"AND SI.EXP_LEV = ? ";

							pstmtOrder  = conn.prepareStatement(sqlOrd);
						 */

						Set itmList = dataMap.keySet();
						Iterator it = itmList.iterator();

						String lineNoSord = "";
						String despLineNo = "";

						//changes made by gulzar on 29/12/11
						String sordItemSql = "";
						String sordDetSql = "";
						String stockSql = "";
						String itemSql = "";
						String itemCodeOrd = "";
						String siteCodeSord = "";
						String sordSql = "";

						double quantityOrd = 0d;
						double grossWtPerArt = 0d;
						double tareWtPerArt = 0d;
						double palletWt = 0d;
						double stkRate = 0d;
						//Changed By Pragyan 26/12/12 To take each item gross weight & net weight
						double grossWeightItem = 0d;
						double netWeightItem = 0d;
						

						sordItemSql = "SELECT ITEM_CODE__ORD, (CASE WHEN QUANTITY IS NULL THEN 0 ELSE QUANTITY END - CASE WHEN QTY_DESP IS NULL THEN 0 ELSE QTY_DESP END ) AS QUANTITY__ORD FROM SORDITEM WHERE SALE_ORDER = ? AND LINE_NO = ? AND LINE_TYPE = 'I'  ";
						pstmtSordItem = conn.prepareStatement(sordItemSql);

						sordSql = "SELECT PRICE_LIST FROM SORDER WHERE SALE_ORDER = ? ";
						pstmtSord = conn.prepareStatement(sordSql);
						
						//Changed by sumit on 18/02/13 getting rate__std and conv__rtuom_stduom
						//sordDetSql = "SELECT PACK_INSTR, SITE_CODE, CUST_ITEM__REF, RATE, RATE__STDUOM, RATE__CLG, UNIT, CONV__QTY_STDUOM,UNIT__STD " +
						sordDetSql = "SELECT PACK_INSTR, SITE_CODE, CUST_ITEM__REF, RATE, RATE__STDUOM, RATE__CLG, UNIT, CONV__QTY_STDUOM,UNIT__STD, RATE__STD, CONV__RTUOM_STDUOM  " +
								" FROM SORDDET WHERE SALE_ORDER = ? AND LINE_NO = ? ";
						pstmtSordDet = conn.prepareStatement(sordDetSql);

						stockSql = "SELECT (CASE WHEN GROSS_WT_PER_ART IS NULL THEN 0 ELSE GROSS_WT_PER_ART END) AS GROSS_WT_PER_ART,"
								+ " (CASE WHEN TARE_WT_PER_ART IS NULL THEN 0 ELSE TARE_WT_PER_ART END) AS TARE_WT_PER_ART,"
								+ " (CASE WHEN PALLET_WT IS NULL THEN 0 ELSE PALLET_WT END) AS PALLET_WT, "
								+ " MFG_DATE, EXP_DATE, SITE_CODE__MFG, PACK_CODE, RETEST_DATE, RATE FROM STOCK "								
								+ " WHERE ITEM_CODE = ? AND SITE_CODE = ? AND LOC_CODE = ? AND LOT_NO = ? AND LOT_SL = ? ";
						pstmtStock = conn.prepareStatement(stockSql);

						//Changed By Pragyan 26/12/12 To take each item gross weight & net weight
						/*itemSql = "SELECT I.UNIT AS UNIT_STD, I.MFR_PART_NO, ILP.SHIPPER_SIZE FROM ITEM I , ITEM_LOT_PACKSIZE ILP "
								+ " WHERE I.ITEM_CODE = ? AND I.ITEM_CODE = ILP.ITEM_CODE ";*/
						itemSql = "SELECT I.UNIT AS UNIT_STD, I.MFR_PART_NO, ILP.SHIPPER_SIZE,I.GROSS_WEIGHT,I.NET_WEIGHT FROM ITEM I , ITEM_LOT_PACKSIZE ILP "
							+ " WHERE I.ITEM_CODE = ? AND I.ITEM_CODE = ILP.ITEM_CODE ";
						pstmtItem = conn.prepareStatement(itemSql);

						String issSql = "SELECT LOC_CODE__TO FROM PICK_ISS_DET WHERE PICK_ORDER = ? AND LINE_NO__ORD = ? ";
						pstmtIss = conn.prepareStatement(issSql);

						//End changes by gulzar on 29/12/11

						while( it.hasNext())			
						{
							String key = (String)it.next();
							System.out.println("CURRENT KEY ["+key+"]");

							//if(key.equalsIgnoreCase("P-TASK"))
							//Changed By Pragyan 02/01/13 to check the consolidetion
							// 23/09/12 manoharan M-PICK and M-PACK to considered together and split accordingly to number of M-PACK
							//if("A-PICK".equalsIgnoreCase(key) || "C-PICK".equalsIgnoreCase(key)  || "M-PICK".equalsIgnoreCase(key)  || "M-PACK".equalsIgnoreCase(key) )
							//if("A-PICK".equalsIgnoreCase(key) || "C-PICK".equalsIgnoreCase(key))
							if("A-PICK".equalsIgnoreCase(key) || "C-PICK".equalsIgnoreCase(key) || "P-PICK".equalsIgnoreCase(key) || "M-PICK".equalsIgnoreCase(key))
							{
								//Changed by rohan on 10-08-12 
								//isPick = true;
								//System.out.println(" inside  M-PICK ");
								sql = "SELECT POD.PICK_ORDER AS ID, POD.LINE_NO AS LINE_NO__ORD, POD.SITE_CODE,"+ //sql added by Kunal On 03/08/12 as per S Monoharan Sir intruction 
								" POD.ITEM_CODE,P.ITEM_CODE, P.LOC_CODE, P.LOC_CODE__TO, P.LOT_NO, P.LOT_SL,"+
								//Changed by sumit on 11/03/13 getting quantity = quantity - dealloc_qty
								//" POD.NO_ART, P.QUANTITY , POD.SALE_ORDER, POD.EXP_LEV, PO.WAVE_ID,"+ 
								" POD.NO_ART, (P.QUANTITY - CASE WHEN P.DEALLOC_QTY IS NULL THEN 0 ELSE P.DEALLOC_QTY END) AS QUANTITY  , POD.SALE_ORDER, POD.EXP_LEV, PO.WAVE_ID,"+ 
								" POD.LINE_NO__SORD,PH.PALLET_NO "+ // change done by Kunal on 03/08/12 select pallet no
								" FROM PICK_ISS_DET P, PICK_ORD_HDR PO , PICK_ORD_DET POD,PICK_ISS_HDR PH "+
								" WHERE  POD.PICK_ORDER = PO.PICK_ORDER "+
								" AND POD.PICK_ORDER = P.PICK_ORDER "+
								" AND POD.LINE_NO = P.LINE_NO__ORD "+
								" AND PH.PICK_ORDER = P.PICK_ORDER "+
								//Changed By Pragyan 26/12/12 to check the join with detail for valid records.
								" AND PH.TRAN_ID = P.TRAN_ID "+
								//Changed by sumit on 11/03/13 adding condition only quantity avilable record should be taken
								" AND (P.QUANTITY - CASE WHEN P.DEALLOC_QTY IS NULL THEN 0 ELSE P.DEALLOC_QTY END) > 0 "+
								" AND PO.PICK_ORDER = ? ";

								//End changes by gulzar on 12/27/2011

							}
							//Changed by sumit on 25/01/13 commenting not require, This would happen against "M-PICK"  end.
							/*else if("M-PACK".equalsIgnoreCase(key))
							{
								sql = "SELECT POD.PICK_ORDER AS ID, POD.LINE_NO AS LINE_NO__ORD, POD.SITE_CODE,"+ //sql added by Kunal On 03/08/12 as per S Monoharan Sir intruction 
										" POD.ITEM_CODE,P.ITEM_CODE, P.LOC_CODE, P.LOC_CODE__TO, P.LOT_NO, P.LOT_SL,"+
										" POD.NO_ART, P.QUANTITY , POD.SALE_ORDER, POD.EXP_LEV, PO.WAVE_ID,"+ 
										" POD.LINE_NO__SORD,PH.PALLET_NO "+ // change done by Kunal on 03/08/12 select pallet no
										" FROM PICK_ISS_DET P, PICK_ORD_HDR PO , PICK_ORD_DET POD,PICK_ISS_HDR PH "+
										" WHERE  POD.PICK_ORDER = PO.PICK_ORDER "+
										" AND POD.PICK_ORDER = P.PICK_ORDER "+
										" AND POD.LINE_NO = P.LINE_NO__ORD "+
										" AND PH.PICK_ORDER = P.PICK_ORDER "+
										" AND PO.PICK_ORDER = ? ";

								sql = 	"SELECT POD.PICK_ORDER AS ID, POD.LINE_NO AS LINE_NO__ORD, POD.SITE_CODE, "
											+ " POD.ITEM_CODE,PKD.ITEM_CODE, P.LOC_CODE, P.LOC_CODE__TO, P.LOT_NO, P.LOT_SL, "
											+ " sum(PKD.NO_ART) NO_ART, SUM(PKD.QUANTITY) QUANTITY, POD.SALE_ORDER, POD.EXP_LEV, PO.WAVE_ID, "
											+ " POD.LINE_NO__SORD,PKH.OUTER_CARTON_NO  PALLET_NO "
											+ " FROM PACK_DET PKD, PACK_HDR PKH, PICK_ISS_DET P, PICK_ORD_HDR PO , PICK_ORD_DET POD,PICK_ISS_HDR PH "
											+ " WHERE  PKD.TRAN_ID =  PKH.TRAN_ID "
											+ " AND POD.PICK_ORDER = PO.PICK_ORDER "
											+ " AND PKD.ITEM_CODE = P.ITEM_CODE "
											+ " AND PO.PICK_ORDER IN ( SELECT REF_ID FROM WAVE_TASK_DET W WHERE W.WAVE_ID = PO.WAVE_ID "
											+ " AND W.REF_SER = 'M-PICK' AND W.REF_ID = PO.PICK_ORDER and w.ptcn = pkh.ptcn ) "
											+ " AND POD.PICK_ORDER = P.PICK_ORDER "
											+ " AND POD.LINE_NO = P.LINE_NO__ORD "
											+ "	AND PH.TRAN_ID = P.TRAN_ID "
											+ " AND PKH.TRAN_ID = ? "
											+ " GROUP BY POD.PICK_ORDER, POD.LINE_NO, POD.SITE_CODE,POD.ITEM_CODE, "
											+ " PKD.ITEM_CODE, P.LOC_CODE, P.LOC_CODE__TO, P.LOT_NO, P.LOT_SL, POD.SALE_ORDER, POD.EXP_LEV, PO.WAVE_ID, "
											+ " POD.LINE_NO__SORD,PKH.OUTER_CARTON_NO " ;
									

							}*/
							//Changed by sumit on 25/01/13 commenting not require, This would happen against "M-PICK"  end.
							//
							//CHANGED BY PRAGYAN 07-SEP-12 To ADD PND LOCATION 
							//CHANGED BY PRAGYAN 12-JUL-12 To ADD PND LOCATION  
							//else if(key.equalsIgnoreCase("R-TASK") || key.equalsIgnoreCase("S-DOC") || key.equalsIgnoreCase("RS-DSO") || key.equalsIgnoreCase("RO-DSO") || key.equalsIgnoreCase("R-DSO") || key.equalsIgnoreCase("R-PSO") )
							//else if("RS-DSO".equalsIgnoreCase(key) || "RP-PSO".equalsIgnoreCase(key) || "RP-PSO".equalsIgnoreCase(key) )
							//else if(key.equalsIgnoreCase("R-TASK") || key.equalsIgnoreCase("S-DOC") || key.equalsIgnoreCase("RS-DSO") || key.equalsIgnoreCase("RO-DSO") || key.equalsIgnoreCase("R-DSO") || key.equalsIgnoreCase("R-PSO") || "RP-PSO".equalsIgnoreCase(key) )
							else if(key.equalsIgnoreCase("R-TASK") || key.equalsIgnoreCase("S-DOC") || key.equalsIgnoreCase("RS-DSO") || key.equalsIgnoreCase("RO-DSO") || key.equalsIgnoreCase("R-DSO") || key.equalsIgnoreCase("R-PND") || "RP-PND".equalsIgnoreCase(key) )
							{
									sql =	" SELECT P.REPL_ORDER AS ID, P.LINE_NO__ORD, P.SITE_CODE, P.ITEM_CODE,P.LOC_CODE, P.LOC_CODE__TO, " +
									//Changed by sumit on 11/03/13 getting quantity = quantity - dealloc_qty
									//" P.LOT_NO, P.LOT_SL, P.NO_ART, P.QUANTITY , PO.SALE_ORDER, POD.EXP_LEV,PO.WAVE_ID, POD.LINE_NO__SORD, RPH.CARTON_NO " + // change done by Kunal on 03/08/12 select carton no 
									" P.LOT_NO, P.LOT_SL, P.NO_ART, (P.QUANTITY - CASE WHEN P.DEALLOC_QTY IS NULL THEN 0 ELSE P.DEALLOC_QTY END) AS QUANTITY , PO.SALE_ORDER, POD.EXP_LEV,PO.WAVE_ID, POD.LINE_NO__SORD, RPH.CARTON_NO " + 
									" FROM REPL_ISS_DET P, REPL_ORD_HDR PO , REPL_ORD_DET POD ,REPL_ISS_HDR RPH " +
									" WHERE  PO.REPL_ORDER = P.REPL_ORDER " +
									" AND POD.REPL_ORDER = PO.REPL_ORDER " +
									" AND RPH.REPL_ORDER = P.REPL_ORDER " +
									//"AND POD.LINE_NO = P.LINE_NO " +//Commented by Gulzar on 12/27/2011
									" AND POD.LINE_NO = P.LINE_NO__ORD " +//Added by Gulzar on 12/27/2011
									//Changed By Pragyan 26/12/12 to check the join with detail for valid records.
									" AND RPH.TRAN_ID = P.TRAN_ID " +
									" AND PO.ORDER_TYPE in ('D','L') " +
									//Changed by sumit on 11/03/13 adding condition 
									" AND (P.QUANTITY - CASE WHEN P.DEALLOC_QTY IS NULL THEN 0 ELSE P.DEALLOC_QTY END) > 0 "+
									" AND PO.REPL_ORDER = ?";
							}
							else
							{
								continue;
							}

							System.out.println("CURRENT SQL ["+sql+"]");

							tempList = (ArrayList)dataMap.get(key);
							pstmt = conn.prepareStatement(sql);

							int ordLen = tempList.size();
							if ( ordLen > 0 )
							{
								for(int i = 0;i < ordLen; i++)
								{
									String 	refIdCtr = (String)tempList.get(i);	
									System.out.println("CURRENT REF ID ["+refIdCtr+"]");
									pstmt.setString(1,refIdCtr);
									rs = pstmt.executeQuery(); 
									System.out.println("after excute query---------------");
									while(rs.next())
									{
										System.out.println("** inside while block *** ");
										pickOrder = rs.getString("ID");
										lineNoOrd = rs.getInt("LINE_NO__ORD");
										siteCode = rs.getString("SITE_CODE");
										itemCode = rs.getString("ITEM_CODE");
										locCode = rs.getString("LOC_CODE");

										lotNo = rs.getString("LOT_NO");
										lotSl = rs.getString("LOT_SL");
										noArt = rs.getInt("NO_ART");
										quantity = rs.getDouble("QUANTITY");
										saleOrder = rs.getString("SALE_ORDER");		
										waveId = rs.getString("WAVE_ID");
										System.out.println("Wave ID======>"+waveId);
										expLev = rs.getString("EXP_LEV");
										lineNoSord = rs.getString("LINE_NO__SORD");//Gulzar on 12/28/2011	
										//Changed by sumit on 12/03/13 calculating number of art start
										HashMap itmVolumeMap = new HashMap();
										itmVolumeMap = getItemVoumeMap(itemCode,lotNo,conn);
										shipperSize = (Double) itmVolumeMap.get("SHIPPER_SIZE");
										noArt = (int) (quantity / shipperSize);
										if( noArt == 0 )
										{
											noArt = 1;
										}
										shipperSize = 0;
										//Changed by sumit on 12/03/13 calculating number of art end
										//Changed by Rohan on 10-11-12 to call report in at end of despacth populate.start
										/*
										//Changed by Rohan on 10-06-12 to get reports for hazardous material.start
										if(!key.equalsIgnoreCase("R-TASK") || !key.equalsIgnoreCase("S-DOC") || !key.equalsIgnoreCase("RS-DSO") || !key.equalsIgnoreCase("RO-DSO") || !key.equalsIgnoreCase("R-DSO") || !key.equalsIgnoreCase("R-PND") || !"RP-PND".equalsIgnoreCase(key))
										{	
											
											sqlitem = "SELECT HAZARDOUS FROM ITEM WHERE ITEM_CODE=? ";
											pstmtitem = conn.prepareStatement(sqlitem);
											
											pstmtitem.setString(1,itemCode);
											
											rsitem = pstmtitem.executeQuery();
											if(rsitem.next())
											{
												hazMaterial = rsitem.getString("HAZARDOUS") == null?"":rsitem.getString("HAZARDOUS");
															
												System.out.println("hazardous*"+hazMaterial);
											}
											if(pstmtitem != null)
											{
												pstmtitem.close();
												pstmtitem = null;
											}
											if(rsitem != null)
											{
												rsitem.close();
												rsitem = null;
											}
											//to check item is hazarous.end
											//to check transporter mode.start
											
											
											sqlTran = "SELECT TRANS_MODE FROM DESPATCH WHERE DESP_ID IN(SELECT REF_ID FROM WAVE_TASK_DET " 
													+" WHERE WAVE_ID = ? AND REF_SER = ? AND PTCN = ? )";
											
											pstmtitem = conn.prepareStatement(sqlTran);
											
											pstmtitem.setString(1,waveId);
											pstmtitem.setString(2,"S-DSP");
											pstmtitem.setString(3,ptcn);
											rsitem = pstmtitem.executeQuery();
											if(rsitem.next())
											{
												tranMode = rsitem.getString("TRANS_MODE") == null?"":rsitem.getString("TRANS_MODE");
															
												System.out.println("tranMode*****"+tranMode);
											}
											if(pstmtitem != null)
											{
												pstmtitem.close();
												pstmtitem = null;
											}
											if(rsitem != null)
											{
												rsitem.close();
												rsitem = null;
											}
											//to check transporter mode.end
											if("Y".equalsIgnoreCase(hazMaterial) && "A".equalsIgnoreCase(tranMode))
											{
												reportObjName = disCommon.getDisparams("999999","HAZMAT_LABLE_OBJ_NAME",conn);
												errString = generateReport(ptcn,reportObjName,"Label",conn,xtraParams);
												
												if( errString.trim().length() == 0)
												{
													System.out.println(" report genetrated successfully====1 ");
												}
												reportObjName = disCommon.getDisparams("999999","HAZMAT_FORM_OBJ_NAME",conn);
												errString = generateReport(ptcn,reportObjName,"Form",conn,xtraParams);
												if( errString.trim().length() == 0)
												{
													System.out.println(" report genetrated successfully====2 ");
												}		
											}	
										}
										//Changed by Rohan on 10-06-12 to get reports for hazardous material.end
										 Changed by Rohan on 10-11-12 to call report in at end of despacth populate.end
										*/
										
										//CHANGED BY PRAGYAN 12-JUL-12 To ADD PND  LOCATION  
										//if( key.equalsIgnoreCase("R-TASK"))
										// 11/10/12 manoharan ref_ser as per latest stock2dock
										//if(key.equalsIgnoreCase("R-TASK") || key.equalsIgnoreCase("S-DOC") || "RP-PSO".equalsIgnoreCase(key)) //Need To add PND to Doc also 
										if(key.equalsIgnoreCase("R-TASK") || key.equalsIgnoreCase("S-DOC") || key.equalsIgnoreCase("RS-DSO") ||  key.equalsIgnoreCase("RP-PND") )
										{
											locCodeTo = rs.getString("LOC_CODE__TO");

											palletNo = rs.getString("CARTON_NO");
										}
										//else if ( key.equalsIgnoreCase("P-TASK") || key.equalsIgnoreCase("A-PICK") || key.equalsIgnoreCase("C-PICK") || key.equalsIgnoreCase("M-PICK") || key.equalsIgnoreCase("M-PACK"))
										//Changed by sumit on 25/01/13 removing M-PACK from if condition
										//else if ( key.equalsIgnoreCase("P-TASK") || key.equalsIgnoreCase("A-PICK") || key.equalsIgnoreCase("C-PICK") || key.equalsIgnoreCase("M-PICK") || key.equalsIgnoreCase("M-PACK") || key.equalsIgnoreCase("P-PICK"))
										else if ( key.equalsIgnoreCase("P-TASK") || key.equalsIgnoreCase("A-PICK") || key.equalsIgnoreCase("C-PICK") || key.equalsIgnoreCase("M-PICK") || key.equalsIgnoreCase("P-PICK"))
										{
											//Selecting loc_code__to from pick_iss_det
											pstmtIss.setString(1, pickOrder);
											//Changed By Pragyan
											//pstmtIss.setString(2, lineNoSord);
											palletNo = rs.getString("PALLET_NO");
											pstmtIss.setInt(2, lineNoOrd);

											rsIss = pstmtIss.executeQuery();

											if ( rsIss.next() )
											{
												locCodeTo = rsIss.getString("LOC_CODE__TO");

											}
											rsIss.close(); rsIss = null;
											pstmtIss.clearParameters();
										}

										System.out.println("pick/repl order ["+pickOrder.trim()+"] site code ["+siteCode.trim()+"] item code ["+itemCode.trim()+"] loc code ["+locCode.trim()+"]");
										System.out.println("lot no ["+lotNo.trim()+"] lot sl["+lotSl.trim()+"] no art ["+noArt+"] quantity["+quantity+"] sale_order["+saleOrder.trim()+"]");

										//Changes by gulzar on 29/12/11
										//Form sorditem
										pstmtSordItem.setString(1, saleOrder);

										//Changed By Pragyan this needs to be change after solving design bug in pick order and pick issue has line_no__sord number(3) to char(3).start
										if(lineNoSord.length() == 2)
										{
											pstmtSordItem.setString(2, " "+lineNoSord);
										}
										else if(lineNoSord.length() == 1)
										{
											pstmtSordItem.setString(2, "  "+lineNoSord);

										}
										else
										{
											pstmtSordItem.setString(2, lineNoSord);
										}
										//Changed By Pragyan this needs to be change after solving design bug in pick order and pick issue has line_no__sord number(3) to char(3).end

										rsSordItem = pstmtSordItem.executeQuery();
										if ( rsSordItem.next() )
										{
											itemCodeOrd = checkNull(rsSordItem.getString("ITEM_CODE__ORD"));
											quantityOrd = rsSordItem.getDouble("QUANTITY__ORD");
										}
										rsSordItem.close();rsSordItem = null;
										pstmtSordItem.clearParameters();

										//Form sorddet
										pstmtSordDet.setString(1, saleOrder);
										//Changed By Pragyan this needs to be change after solving design bug in pick order and pick issue has line_no__sord number(3) to char(3).start
										if(lineNoSord.length() == 2)
										{
											pstmtSordDet.setString(2, " "+lineNoSord);
										}
										else if(lineNoSord.length() == 1)
										{
											pstmtSordDet.setString(2, "  "+lineNoSord);

										}
										else
										{
											pstmtSordDet.setString(2, lineNoSord);
										}
										//Changed By Pragyan this needs to be change after solving design bug in pick order and pick issue has line_no__sord number(3) to char(3).start
										rsSordDet = pstmtSordDet.executeQuery();
										if ( rsSordDet.next() )
										{
											packInstr = checkNull(rsSordDet.getString("PACK_INSTR"));
											siteCodeSord = checkNull(rsSordDet.getString("SITE_CODE"));
											custItemRef = checkNull(rsSordDet.getString("CUST_ITEM__REF"));
											rateStduom = checkNull(rsSordDet.getString("RATE__STDUOM"));
											rateClg = checkNull(rsSordDet.getString("RATE__CLG"));
											unit = checkNull(rsSordDet.getString("UNIT"));
											convQtyStduom = rsSordDet.getDouble("CONV__QTY_STDUOM");
											unitStd = checkNull(rsSordDet.getString("UNIT__STD"));
											rate = rsSordDet.getDouble("RATE");//Gulzar on 5/14/2012
											//Changed by sumit on 18/02/13 getting rate__std and conv__rtuom_stduom start
											rateStd = rsSordDet.getDouble("RATE__STD");
											conRtuStduom = rsSordDet.getDouble("CONV__RTUOM_STDUOM");
											//Changed by sumit on 18/02/13 getting rate__std and conv__rtuom_stduom end
										}
										rsSordDet.close();rsSordDet = null;
										pstmtSordDet.clearParameters();
										System.out.println("packInstr["+packInstr+"] siteCodeSord["+siteCodeSord+"] custItemRef ["+custItemRef+"] rateStduom["+rateStduom+"] rateClg["+rateClg+"]");
										System.out.println("unit =["+unit+"]");
										System.out.println("convQtyStduom =["+convQtyStduom+"]");
										System.out.println("unitStd =["+unitStd+"]");
										System.out.println("rate from sorddet =["+rate+"] rateStd ["+rateStd+"] conRtuStduom["+conRtuStduom+"]");
										String priceList = "";

										if ( rate == 0 )
										{
											pstmtSord.setString(1,saleOrder);
											rsSord = pstmtSord.executeQuery();
											if ( rsSord.next() )
											{
												priceList = checkNull(rsSord.getString("PRICE_LIST"));
											}
											rsSord.close(); rsSord = null;
											pstmtSord.clearParameters();
											if ( priceList.trim().length() > 0 )
											{
												rate = disCommon.pickRate( priceList, sysDateStr, itemCode, " ", "L", quantity, conn );
											}
										}
										//Enc changes by Gulzar on 5/14/2012
										//Form stock
										pstmtStock.setString(1, itemCode);
										pstmtStock.setString(2, siteCode);
										pstmtStock.setString(3, locCodeTo);
										pstmtStock.setString(4, lotNo);
										pstmtStock.setString(5, lotSl);

										rsStock = pstmtStock.executeQuery();
										if ( rsStock.next() )
										{
											System.out.println("unitStd["+unitStd+"]");
											//MFG_DATE, EXP_DATE, SITE_CODE__MFG, PACK_CODE, RETEST_DATE, RATE 
											grossWtPerArt = rsStock.getDouble("GROSS_WT_PER_ART");
											tareWtPerArt = rsStock.getDouble("TARE_WT_PER_ART");
											palletWt = rsStock.getDouble("PALLET_WT");
											mfgDate = rsStock.getTimestamp("MFG_DATE");
											expDate = rsStock.getTimestamp("EXP_DATE");
											siteCodeMfg = checkNull(rsStock.getString("SITE_CODE__MFG"));
											packCode = checkNull(rsStock.getString("PACK_CODE"));
											retestDate = rsStock.getTimestamp("RETEST_DATE");
											stkRate = rsStock.getDouble("RATE");//Gulzar on 5/14/2012
											System.out.println("INSIDE STOCKS DATA>>>>>>>>>>>>>>>>>>>>>>>>>>["+grossWtPerArt+"]");
										}


										rsStock.close();rsStock = null;
										pstmtStock.clearParameters();
										//Changed By Pragyan 26/12/12 To take each item gross weight & net weight.START
										pstmtItem.setString(1, itemCode);
										rsItem = pstmtItem.executeQuery();
										if ( rsItem.next() )
										{
											//UNIT_STD, I.MFR_PART_NO, ILP.SHIPPER_SIZE,EACH GROSS WEIGHT AND NET WEIGHT
											mbaseUnit = checkNull(rsItem.getString("UNIT_STD"));
											partNo = checkNull(rsItem.getString("MFR_PART_NO"));
											shipperSize = rsItem.getDouble("SHIPPER_SIZE");
											grossWeightItem = rsItem.getDouble("GROSS_WEIGHT");
											netWeightItem = rsItem.getDouble("NET_WEIGHT");
										}
										rsItem.close();rsItem = null;
										pstmtItem.clearParameters();
										
										
										/*totalNoArt = totalNoArt + noArt;
										grossWeight = (grossWtPerArt * noArt);
										totalGrossWeight = totalGrossWeight + grossWeight;   //added by Kunal on 08/09/12
										tareWeight = (tareWtPerArt * noArt);
										totalTareWeight = totalTareWeight + tareWeight ;  //added by Kunal on 10/09/12
										netWeight  = grossWeight -  tareWeight	;
										totalNetWeight = totalNetWeight + netWeight; */ //added by Kunal on 10/09/12
										if(quantity < shipperSize)
										{
											grossWeight = (grossWeightItem * quantity);
											netWeight  = (netWeightItem * quantity);
										}
										else
										{
											grossWeight = (grossWtPerArt * noArt);
											//totalGrossWeight = totalGrossWeight + grossWeight;   //added by Kunal on 08/09/12
											tareWeight = (tareWtPerArt * noArt);
											netWeight  = grossWeight -  tareWeight	;
										}
										totalNoArt = totalNoArt + noArt;
										totalGrossWeight = totalGrossWeight + grossWeight;
										totalTareWeight = totalTareWeight + tareWeight ;  //added by Kunal on 10/09/12
										totalNetWeight = totalNetWeight + netWeight; 
										//Changed By Pragyan 26/12/12 To take each item gross weight & net weight.END
										//Form item and item_lot_packsize
										//Changed By Pragyan 26/12/12 To take each item gross weight & net weight.START
										/*pstmtItem.setString(1, itemCode);
										rsItem = pstmtItem.executeQuery();
										if ( rsItem.next() )
										{
											//UNIT_STD, I.MFR_PART_NO, ILP.SHIPPER_SIZE
											mbaseUnit = checkNull(rsItem.getString("UNIT_STD"));
											partNo = checkNull(rsItem.getString("MFR_PART_NO"));
											shipperSize = rsItem.getDouble("SHIPPER_SIZE");
										}
										rsItem.close();rsItem = null;
										pstmtItem.clearParameters();	*/
										//Changed By Pragyan 26/12/12 To take each item gross weight & net weight.END
																

										if(unitStd != "")
										{
											mbaseUnit = unitStd;
										}
										//End changes by gulzar on 29/12/11

										/*												
											pstmtOrder.setString(1, itemCode);
											pstmtOrder.setString(2, siteCode);
											//pstmtOrder.setString(3, locCode);//Commented by Gulzar on 12/27/2011
											pstmtOrder.setString(3, locCodeTo);//Changes added by Gulzar on 12/27/2011
											pstmtOrder.setString(4, lotNo);
											pstmtOrder.setString(5, lotSl);
											pstmtOrder.setString(6, saleOrder);
											pstmtOrder.setInt(7, lineNoOrd);
											pstmtOrder.setString(8, expLev);
											rs1 = pstmtOrder.executeQuery();

											if(rs1.next())
											{
												mbaseUnit = rs1.getString("UNIT");
												partNo = rs1.getString("MFR_PART_NO");
												grossWeight = rs1.getInt("GROSS_WEIGHT");
												tareWeight = rs1.getInt("TARE_WEIGHT");
												netWeight = rs1.getInt("NET_WEIGHT");
												mfgDate = rs1.getTimestamp("MFG_DATE");
												expDate = rs1.getTimestamp("EXP_DATE");
												siteCodeMfg = rs1.getString("SITE_CODE__MFG");
												packCode = rs1.getString("PACK_CODE");
												retestDate = rs1.getTimestamp("RETEST_DATE");
												rate = rs1.getFloat("RATE");
												packInstr = rs1.getString("PACK_INSTR");
												rateClg = rs1.getString("RATE__CLG");
												custItemRef = rs1.getString("CUST_ITEM__REF");
												rateStduom = rs1.getString("RATE__STDUOM");
												lineType = rs1.getString("LINE_TYPE");
												shipperSize = rs1.getLong("SHIPPER_SIZE");								
											}
											if(rs1 !=null)
											{
												rs1.close();
												rs1 = null;
											}

											pstmtOrder.clearParameters();
											// calculating weight
											netWeight = netWeight*noArt;
											System.out.println("--netWeight-->"+netWeight);
											grossWeight = grossWeight*noArt;
											System.out.println("--grossWeight-->"+grossWeight);
											tareWeight =tareWeight*noArt;
											System.out.println("--tareWeight-->"+tareWeight);		

											//*********Calculating conversion quantity start
											meffQty =0;
										 */
										if(qtyStduom == 0)
										{											
											if(!unit.equalsIgnoreCase(mbaseUnit))											
											{
												if(convQtyStduom != 0)
												{
													convFact = convQtyStduom;
												}
												ArrayList convQtyList = disCommon.getConvQuantityFact(unit, mbaseUnit, itemCode, quantity, convFact,conn);												
												System.out.println("convQtyList = "+convQtyList);
												meffQty = Double.parseDouble(convQtyList.get(1).toString());
												System.out.println("meffQty ["+meffQty+"]");

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

											}
											qtyStduom = meffQty;
										}
										//*********Calculating conversion quantity end

										//*********Calculating reverse conversion quantity start
										if(qtyStduom1 == 0)
										{
											if(!unit.equalsIgnoreCase(mbaseUnit))											
											{
												//Changed by sumit on 01/02/13 setting convFact = 0 as this need for reverse the quantity start
												/*if(conRtuStduom != 0)
												{
													convFact = conRtuStduom;
												}*/
												convFact = 0;
												//Changed by sumit on 01/02/13 setting convFact = 0 as this need for reverse the quantity end.
												
												ArrayList convQtyList = disCommon.getConvQuantityFact(mbaseUnit, unit, itemCode, quantity, convFact,conn);												
												System.out.println("convQtyList = "+convQtyList);
												//Changed by sumit on 01/02/13 converting from stock unit to order unit start									
												convertQty = Double.parseDouble(convQtyList.get(1).toString());												
												if(convertQty == -999999999)
												{
													convertQty = 0;								
												}
												//Changed by sumit on 01/02/13 converting from stock unit to order unit end

												//Changed by sumit on 18/02/13 commenting this and reciving conRtuStduom column value from sorddet start.
												/*if (conRtuStduom == 0)
												{
													conRtuStduom= Double.parseDouble(convQtyList.get(0).toString());
												}*/
												//Changed by sumit on 18/02/13 commenting this and reciving conRtuStduom column value from sorddet end.
												
											}
											else
											{
												//Changed by sumit on 04/02/12 
												convertQty = quantity;
												
												//Changed by sumit on 18/02/13 commenting this and reciving conRtuStduom column value from sorddet start.
												/*if (conRtuStduom == 0) 
												{
													conRtuStduom=1;
												}*/
												//Changed by sumit on 18/02/13 commenting this and reciving conRtuStduom column value from sorddet end.
											}

										}
										lineNo++;										
										//Changes by gulzar on 12/28/2011
										despLineNo = "   " + lineNo;
										despLineNo = despLineNo.substring( despLineNo.length()-3 );

										lineNoSord = "   " + lineNoSord;
										lineNoSord = lineNoSord.substring( lineNoSord.length()-3 );
										//End changes by gulzar on 12/28/2011

										//DESP_ID, LINE_NO, SORD_NO, LINE_NO__SORD, EXP_LEV, " +
										pstmt1.setString(1, despId);
										//pstmt1.setInt(2, lineNo);//Gulzar on 12/28/2011
										pstmt1.setString(2, despLineNo);//Gulzar on 12/28/2011
										pstmt1.setString(3, saleOrder.trim());
										//pstmt1.setInt(4, lineNo);//Commented by gulzar on 12/28/2011
										pstmt1.setString(4, lineNoSord);//Added by gulzar on 12/28/2011

										pstmt1.setString(5, expLev);

										//"ITEM_CODE__ORD, ITEM_CODE, LOT_NO, LOT_SL, QUANTITY__ORD, QUANTITY, LOC_CODE, " +
										if(itemCodeOrd != null && itemCodeOrd.length() > 0)
										{
											pstmt1.setString(6, itemCodeOrd.trim());
										}
										else
										{
											pstmt1.setString(6, itemCode);
										}										
										
										pstmt1.setString(7, itemCode.trim());
										pstmt1.setString(8, lotNo.trim());
										pstmt1.setString(9, lotSl.trim());
										pstmt1.setDouble(10, quantityOrd);
										//Changed by sumit on 01/02/13 inserting qty as per ordered unit
										//pstmt1.setDouble(11, quantity);
										pstmt1.setDouble(11, convertQty);
										pstmt1.setString(12, checkNull(locCodeTo));

										//Changed By Pragyan to set " " one blank character in status as per manoharan sir
										//"STATUS, CONV__QTY_STDUOM, UNIT__STD, UNIT, QUANTITY_STDUOM, QUANTITY_REAL, " +
										//pstmt1.setString(13,"0");
										pstmt1.setString(13," ");
										pstmt1.setDouble(14 , convQtyStduom );
										pstmt1.setString(15 , mbaseUnit);
										pstmt1.setString(16 , unit);
										//Changed by sumit on 01/02/13 like  QUANTITY__STDUOM = QUANTITY_REAL = SORDDET.QUANTITY__STDUOM
										//pstmt1.setDouble(17 , qtyStduom );
										pstmt1.setDouble(17 , quantity );
										//Changed by sumit on 18/02/13 QUANTITY_REAL should be as per quantity ordered
										//pstmt1.setDouble(18 , quantity);
										pstmt1.setDouble(18 , convertQty);

										//"RATE_STDUOM, PACK_INSTR, NO_ART, SITE_CODE, PACK_QTY, GROSS_WEIGHT, TARE_WEIGHT,  " +
										pstmt1.setDouble(19 , rate );
										pstmt1.setString(20 , checkNull(packInstr) );
										pstmt1.setDouble( 21 , noArt);
										pstmt1.setString( 22 , checkNull(siteCode));
										pstmt1.setDouble( 23 , shipperSize); // pack_qty is shipper_size 
										pstmt1.setDouble( 24 , grossWeight );
										pstmt1.setDouble( 25 , tareWeight );

										//"NETT_WEIGHT, MFG_DATE, EXP_DATE, SITE_CODE__MFG, RATE__CLG, COST_RATE, LINE_TYPE, " +
										pstmt1.setDouble( 26 , netWeight);
										pstmt1.setTimestamp( 27 , mfgDate );
										pstmt1.setTimestamp( 28 , expDate );
										pstmt1.setString( 29 , checkNull(siteCodeMfg));
										pstmt1.setString( 30 , checkNull(rateClg));
										pstmt1.setDouble( 31 , stkRate);
										pstmt1.setString( 32 , checkNull(lineType));

										//"CONV_RTUOM_STDUOM, PACK_CODE, CUST_ITEM__REF, RETEST_DATE, PART_NO) " + // PALLET_WT Hided 
										pstmt1.setDouble( 33 , conRtuStduom);
										pstmt1.setString( 34 , checkNull(packCode));
										//pstmt1.setString( 34 , pallet_wt);
										pstmt1.setString( 35, checkNull(custItemRef));
										pstmt1.setTimestamp( 36 ,retestDate);
										pstmt1.setString( 37 , checkNull(partNo));
										pstmt1.setString( 38 , checkNull(palletNo)); //added by Kunal Mandhre on 02/08/12 as per S Manoharan Sir intruction .
										pstmt1.setDouble( 39 , rateStd);//added by sumit on 18/02/13 
										
										pstmt1.addBatch();
										pstmt1.clearParameters();
										System.out.println("lOOPPPPPPPPPPPPPPPPPPPPPP111");

										itemCodeOrd = ""; quantityOrd = 0d; itemCode = ""; lotNo = ""; lotSl = ""; quantity = 0d;
										locCodeTo = ""; noArt = 0; mbaseUnit = ""; packInstr = ""; siteCode = ""; qtyStduom = 0d;
										rate = 0d; convQtyStduom = 0d; shipperSize = 0d; grossWeight = 0d; tareWeight = 0d;
										netWeight = 0d; mfgDate = null; expDate = null; siteCodeMfg = ""; rateClg = ""; 
										conRtuStduom = 0d; packCode = ""; custItemRef = ""; retestDate = null; partNo = "";

									}//While loop
									System.out.println("lOOPPPPPPPPPPPPPPPPPPPPPP2222");
									pstmt.clearParameters();
								}//for(int i = 0;i < ordLen; i++)
							}
							
							if(pstmt != null)
							{
								pstmt.close();
								pstmt = null;
							}
							
							
						}

						System.out.println("BEFORE BATCH UPDATE================");
						pstmt1.executeBatch();
						System.out.println("AFTER BATCH UPDATE================");
						pstmt1.clearBatch();	


						if(pstmt1 != null)
						{
							pstmt1.close();
							pstmt1 = null;
						}	
						
						//Changed by Dhanraj on 19-SEP-14 for update despatch tare and filler weight  [W14FSUN003]	
					     System.out.println("PTCN ======For updatedespatch method====>> "+ptcn);
						 errString = updatedespatch(ptcn, conn);
						
						
                        //Changed by Dharnaj on 19-SEP-14 for  [W14FSUN003]	 
						//sqlHdr = "update  despatch set  gross_weight = ?, tare_weight = ? ,nett_weight = ?, no_art = ?   where desp_id = ? "; //added by Kunal on 08/09/12 as per S Manoharan Sir instruction
						sqlHdr = "update  despatch set  gross_weight = ?, tare_weight = tare_weight + ? ,nett_weight = ?, no_art = ?   where desp_id = ? "; //added by Kunal on 08/09/12 as per S Manoharan Sir instruction
						pstmtHdr = conn.prepareStatement(sqlHdr);
						pstmtHdr.setDouble(1, totalGrossWeight);
						pstmtHdr.setDouble(2, totalTareWeight);
						pstmtHdr.setDouble(3, totalNetWeight);
						pstmtHdr.setInt(4, totalNoArt);
						pstmtHdr.setString(5, despId);
						int upctr = pstmtHdr.executeUpdate();	
						System.out.println("NO OF ROW UPDATED = "+upctr);
						pstmtHdr.close();
						pstmtHdr = null;
						
						System.out.println("***************START***********************");
						//Changed by Rohan on 10-06-12 to get reports for hazardous material.start
						
						Set itemList = dataMap.keySet();
						Iterator itr = itemList.iterator();
						while( itr.hasNext())			
						{
							String keyList = (String)itr.next();
							System.out.println("CURRENT KEY ["+keyList+"]");
						
							//if("A-PICK".equalsIgnoreCase(keyList) || "C-PICK".equalsIgnoreCase(keyList))
							if("A-PICK".equalsIgnoreCase(keyList) || "C-PICK".equalsIgnoreCase(keyList) || "P-PICK".equalsIgnoreCase(keyList) || "M-PICK".equalsIgnoreCase(keyList))
							{
								
								sql2 = 	" SELECT P.ITEM_CODE,PO.WAVE_ID "+  
										" FROM PICK_ISS_DET P, PICK_ORD_HDR PO , PICK_ORD_DET POD,PICK_ISS_HDR PH "+
										" WHERE  POD.PICK_ORDER = PO.PICK_ORDER "+
										" AND POD.PICK_ORDER = P.PICK_ORDER "+
										" AND POD.LINE_NO = P.LINE_NO__ORD "+
										" AND PH.PICK_ORDER = P.PICK_ORDER "+
										//Changed By Pragyan 26/12/12 to check the join with detail for valid records.
										" AND PH.TRAN_ID = P.TRAN_ID "+
										" AND PO.PICK_ORDER = ? ";


							}
							//Changed by sumit on 25/01/13 commenting, this would happen against "M-PICK" start
							/*else if("M-PACK".equalsIgnoreCase(keyList))
							{
								sql2 = 	"SELECT PKD.ITEM_CODE,PO.WAVE_ID "
									+ " FROM PACK_DET PKD, PACK_HDR PKH, PICK_ISS_DET P, PICK_ORD_HDR PO , PICK_ORD_DET POD,PICK_ISS_HDR PH "
									+ " WHERE  PKD.TRAN_ID =  PKH.TRAN_ID "
									+ " AND POD.PICK_ORDER = PO.PICK_ORDER "
									+ " AND PKD.ITEM_CODE = P.ITEM_CODE "
									+ " AND PO.PICK_ORDER IN ( SELECT REF_ID FROM WAVE_TASK_DET W WHERE W.WAVE_ID = PO.WAVE_ID "
									+ " AND W.REF_SER = 'M-PICK' AND W.REF_ID = PO.PICK_ORDER and w.ptcn = pkh.ptcn ) "
									+ " AND POD.PICK_ORDER = P.PICK_ORDER "
									+ " AND POD.LINE_NO = P.LINE_NO__ORD "
									+ "	AND PH.TRAN_ID = P.TRAN_ID "
									+ " AND PKH.TRAN_ID = ? "
									+ " GROUP BY POD.PICK_ORDER, POD.LINE_NO, POD.SITE_CODE,POD.ITEM_CODE, "
									+ " PKD.ITEM_CODE, P.LOC_CODE, P.LOC_CODE__TO, P.LOT_NO, P.LOT_SL, POD.SALE_ORDER, POD.EXP_LEV, PO.WAVE_ID, "
									+ " POD.LINE_NO__SORD,PKH.OUTER_CARTON_NO " ;
									

							}*/
							//Changed by sumit on 25/01/13 commenting, this would happen against "M-PICK" end
							else if(keyList.equalsIgnoreCase("R-TASK") || keyList.equalsIgnoreCase("S-DOC") || keyList.equalsIgnoreCase("RS-DSO") || keyList.equalsIgnoreCase("RO-DSO") || keyList.equalsIgnoreCase("R-DSO") || keyList.equalsIgnoreCase("R-PND") || "RP-PND".equalsIgnoreCase(keyList) )
							{
								sql2 =	" SELECT P.ITEM_CODE,PO.WAVE_ID " + 
										" FROM REPL_ISS_DET P, REPL_ORD_HDR PO , REPL_ORD_DET POD ,REPL_ISS_HDR RPH " +
										" WHERE  PO.REPL_ORDER = P.REPL_ORDER " +
										" AND POD.REPL_ORDER = PO.REPL_ORDER " +
										" AND RPH.REPL_ORDER = P.REPL_ORDER " +
										" AND POD.LINE_NO = P.LINE_NO__ORD " +
										//Changed By Pragyan 26/12/12 to check the join with detail for valid records.
										" AND RPH.TRAN_ID = P.TRAN_ID " +
										" AND PO.ORDER_TYPE in ('D','L') " +
										" AND PO.REPL_ORDER = ?";
							}
							else
							{
								continue;
							}
							
							System.out.println("CURRENT SQL ["+sql2+"]");

							tempList1 = (ArrayList)dataMap.get(keyList);
							pstmt2 = conn.prepareStatement(sql2);
							
							int orderLen = tempList1.size();
							if ( orderLen > 0 )
							{
								
								for(int i = 0;i < orderLen; i++)
								{
									String 	refIdList = (String)tempList1.get(i);	
									System.out.println("CURRENT REF ID ["+refIdList+"]");
									pstmt2.setString(1,refIdList);
									rs2 = pstmt2.executeQuery(); 
									
									while(rs2.next())
									{
										
										itemCode = rs2.getString("ITEM_CODE");
										waveId = rs2.getString("WAVE_ID");
										
										sqlitem = "SELECT HAZARDOUS FROM ITEM WHERE ITEM_CODE=? ";
										pstmtitem = conn.prepareStatement(sqlitem);
										
										pstmtitem.setString(1,itemCode);
										
										rsitem = pstmtitem.executeQuery();
										if(rsitem.next())
										{
											hazMaterial = rsitem.getString("HAZARDOUS") == null?"":rsitem.getString("HAZARDOUS");
														
											System.out.println("hazardous*"+hazMaterial);
										}
										if(pstmtitem != null)
										{
											pstmtitem.close();
											pstmtitem = null;
										}
										if(rsitem != null)
										{
											rsitem.close();
											rsitem = null;
										}
										//to check item is hazarous.end
										//to check transporter mode.start
										sqlTran = "SELECT TRANS_MODE FROM DESPATCH WHERE DESP_ID IN(SELECT REF_ID FROM WAVE_TASK_DET " 
												+" WHERE WAVE_ID = ? AND REF_SER = ? AND PTCN = ? )";
										
										pstmtitem = conn.prepareStatement(sqlTran);
										
										pstmtitem.setString(1,waveId);
										pstmtitem.setString(2,"S-DSP");
										pstmtitem.setString(3,ptcn);
										rsitem = pstmtitem.executeQuery();
										if(rsitem.next())
										{
											tranMode = rsitem.getString("TRANS_MODE") == null?"":rsitem.getString("TRANS_MODE");
														
											System.out.println("tranMode*****"+tranMode);
										}
										if(pstmtitem != null)
										{
											pstmtitem.close();
											pstmtitem = null;
										}
										if(rsitem != null)
										{
											rsitem.close();
											rsitem = null;
										}
										//to check transporter mode.end
										if("Y".equalsIgnoreCase(hazMaterial) && "A".equalsIgnoreCase(tranMode))
										{
											reportObjName = disCommon.getDisparams("999999","HAZMAT_LABLE_OBJ_NAME",conn);
											errString = generateReport(ptcn,reportObjName,"Label",conn,xtraParams);
											
											//Changed by Rohan on 27-10-12 for bug fixing.start
											tarHaz =  CommonConstants.JBOSSHOME+File.separator+"server"+File.separator+"default"+File.separator+
									        		"deploy"+File.separator+"ibase.ear"+File.separator+"ibase.war"+File.separator+"webitm"+File.separator+
									        		"reports"+File.separator+"hazardous";
											File dir1=new File(tarHaz);
											
											dir1.mkdir();
											
											//Changed by Rohan on 27-10-12 for bug fixing.end
											target = CommonConstants.JBOSSHOME+File.separator+"server"+File.separator+"default"+File.separator+
									        		"deploy"+File.separator+"ibase.ear"+File.separator+"ibase.war"+File.separator+"webitm"+File.separator+
									        		"reports"+File.separator+"hazardous"+File.separator+user;
											
											File dir=new File(target);
											
											boolean exists = dir.exists();
											if (!exists) 
										    {
											    if(dir.mkdir())
											    {	  
											    	System.out.println("Hazardous Directory created");
										        }
											    else
											    {	  
											    	System.out.println("Hazardous Directory not created");
											    }	  
											}
											   
											if( errString.trim().length() == 0)
											{
												System.out.println(" Lable report genetrated successfully");
												source = CommonConstants.JBOSSHOME+File.separator+"server"+File.separator+"default"+File.separator+
										        		"deploy"+File.separator+"ibase.ear"+File.separator+"ibase.war"+File.separator+"webitm"+File.separator+
										        		"reports"+File.separator+"finalreport"+File.separator+user+File.separator+ptcn+"_Label.pdf";
												
										        File sourceFile = new File(source);
										        String name = sourceFile.getName();
										     
										        if(sourceFile.renameTo(new File(target+File.separator+sourceFile.getName())))
										        {
									    		   System.out.println(ptcn+"+_Label move succesfully");
										        }
										        else
										        {
									    		   System.out.println(ptcn+"_Lable is failed to move");
										        }
										       
									
											}
											reportObjName = disCommon.getDisparams("999999","HAZMAT_FORM_OBJ_NAME",conn);
											errString = generateReport(ptcn,reportObjName,"Form",conn,xtraParams);
											if( errString.trim().length() == 0)
											{
												System.out.println(" Form report genetrated successfully");
												source = CommonConstants.JBOSSHOME+File.separator+"server"+File.separator+"default"+File.separator+
										        		"deploy"+File.separator+"ibase.ear"+File.separator+"ibase.war"+File.separator+"webitm"+File.separator+
										        		"reports"+File.separator+"finalreport"+File.separator+user+File.separator+ptcn+"_Form.pdf";
												
										        File sourceFile = new File(source);
										        String name = sourceFile.getName();
										     
										        if(sourceFile.renameTo(new File(target+File.separator+sourceFile.getName())))
										        {
									    		   System.out.println(ptcn+"+_Form move succesfully");
										        }
										        else
										        {
									    		   System.out.println(ptcn+"_Form is failed to move");
										        }
										        
											}		
										}	
									 }//while close
								}//for close
							}//if close
							if(pstmt2 != null)
							{
								pstmt2.close();
								pstmt2 = null;
							}
							if(rs2 != null)
							{
								rs2.close();
								rs2 = null;
							}
						}//while itr close
						System.out.println("***************END***********************");
						//Changed by Rohan on 10-06-12 to get reports for hazardous material.end

					}//if(!isShipExist)
					else
					{
						isError = false;
						System.out.println("SHIPMENT ALREADY CONSOLIDATED");
						//errString = itmDBAccessEJB.getErrorString("","CONALEXIST","");

						return "";
					}

				}//if(isTasksConfirmed)
				else
				{
					//isError = true;
					//System.out.println("ALL TASK NOT COFIRMED SYSTEM SHOULD NOT ALLOW CONSOLIDATED TO DOCK");
					//errString = itmDBAccessEJB.getErrorString("","ALLTASKNOF","");
					isError = false;
					errString = "";
					return errString;
				}
				 //Changed by Rohan on 2=30-03-13 to delete sordalloc where stock alloc qty is zero.start
				System.out.println("isError::"+isError+"errString[::"+errString+"]");
				if(!isError && ("".equalsIgnoreCase(errString)|| errString == null) && errString.trim().length() == 0)
				{	
					System.out.println("Delete records:::::::");
					//Changed by sumit on 01/apr/13 adding sale_order condition 
					//sql = " DELETE FROM SORDALLOC WHERE QTY_ALLOC = 0 ";
					sql = " DELETE FROM SORDALLOC WHERE QTY_ALLOC <= 0 AND SALE_ORDER  IN (SELECT DISTINCT SALE_ORDER FROM WAVE_TASK_DET WHERE PTCN = ?)";
					pstmt = conn.prepareStatement(sql);
					//Changed by sumit 01/04/13 setting parameter
					pstmt.setString(1, ptcn);
					int updCnt = pstmt.executeUpdate();
					if( updCnt > 0 )
					{
						System.out.println(" No of record deleted successfully "+updCnt);
					}
					if(pstmt != null)
					{
						pstmt.close();
						pstmt = null;
					}
			    }
				//Changed by Rohan on 2=30-03-13 to delete sordalloc where stock alloc qty is zero.end

			}

		}
		catch(BatchUpdateException b)
		{
			isError = true;
			System.out.println("BatchUpdateException :" + b);
			b.printStackTrace();

			errString = b.getMessage();

			//Changed by Rohan on 10-12-12 to throw error.start
			/*
			try
			{
				if(conn != null)
				{
					conn.rollback();
				}
			}
			catch(Exception e1)
			{
				e1.printStackTrace();
				throw new ITMException(e1) ;
			}
			
			return errString;
			*/
			throw new ITMException(b) ;
			//Changed by Rohan on 10-12-12 to throw error.end

		}
		catch(SQLException se)
		{
			isError = true;
			System.out.println("SQLException :" + se);
			se.printStackTrace();
			System.out.println("error ****"+se.getMessage());
			errString = se.getMessage();
			//Changed by Rohan on 10-12-12 to throw error.start		
			/*
			try
			{
				if(conn != null)
				{
					conn.rollback();
				}
			}
			catch(SQLException e1)
			{
				se = e1;
				throw new ITMException(se) ;
			}
			
			return errString;
			*/
			throw new ITMException(se) ;
			//Changed by Rohan on 10-12-12 to throw error.end
		}
		catch(Exception e)
		{
			isError = true;
			System.out.println("Exception :" + e);
			errString = e.getMessage();
			//Changed by Rohan on 10-12-12 to throw error.start
			/*
			System.out.println("error-->"+e.getMessage());
			try
			{
				if(conn != null)
				{
					conn.rollback();
				}
			}
			catch(Exception e1)
			{
				e = e1;
				throw new ITMException(e) ;

			}
			return errString ;
			*/
			throw new ITMException(e) ;
			//Changed by Rohan on 10-12-12 to throw error.end
		}
		//Changed by Rohan on 10-12-12 to rollback connection.start			
		finally
		{
			try
			{
				if(conn != null && isError)
				{
					System.out.println("rollbacking the changes*************");
					conn.rollback();
				}
			}
			catch(Exception e1)
			{
				throw new ITMException(e1) ;
			}
		}
		//Changed by Rohan on 10-12-12 to rollback connection.end

		return errString;

	}

	private String checkNull( String input )
	{
		if ( input == null )
		{
			input = "";
		}
		return input;
	}	

	public HashMap getAllTaskID(String PTCN, Connection conn)throws Exception
	{
		String sql = "";
		PreparedStatement pstmt = null;
		ResultSet rs = null;	
		HashMap hasMap = new HashMap();
		ArrayList tempList = null; 
		ArrayList arrayList = null; 
		String refSer = "";
		String refId = "";


		try
		{
			sql = "SELECT REF_SER, REF_ID FROM WAVE_TASK_DET WHERE PTCN = ?";
			System.out.println("sql ["+sql+"]");
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, PTCN);
			rs = pstmt.executeQuery();

			while(rs.next())
			{
				refSer = rs.getString("REF_SER");
				refId =  rs.getString("REF_ID");

				//Changed By Pragyan 08/01/13 as bug found in Stock To dock
				//if(hasMap.containsKey(refSer))
				if(hasMap.containsKey(refSer.trim()))
				{
					tempList = (ArrayList)hasMap.get(refSer.trim());
					tempList.add(refId);
					hasMap.put(refSer,tempList);
				}
				else
				{
					arrayList = new ArrayList();
					arrayList.add(refId);
					hasMap.put(refSer.trim(),arrayList);
				}

			}
		}
		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 e)
			{
				throw new ITMException(e) ;
			}
		}

		return 	hasMap;
	}

	public boolean isAllTaskConfirmed(HashMap taskMap , Connection conn)throws Exception
	{
		String sql = "",sql1 = null;
		String cofirmed  = "";
		boolean isConfirmed  = true;		
		PreparedStatement pstmt = null,pstmt1 = null;
		ResultSet rs = null,rs1 = null;	
		String status = "";
		try
		{
			Set itmList = taskMap.keySet();
			Iterator it = itmList.iterator();

			while( it.hasNext())			
			{
				String key = (String)it.next();

				System.out.println("Key  ["+key+"]");
				//Changed by sumit on 19/03/12 checking confirmation status from wave_task_det start
				//if(key.equalsIgnoreCase("P-TASK") || key.equalsIgnoreCase("C-PICK"))
				/*if(key.equalsIgnoreCase("P-TASK") || key.equalsIgnoreCase("C-PICK") || key.equalsIgnoreCase("P-PICK"))
				{

					sql = "select status from pick_ord_hdr where pick_order =  ? ";
					
				}
				else if( key.equalsIgnoreCase("A-PICK") ||  key.equalsIgnoreCase("M-PICK"))
				{

					sql = "select status from pick_ord_hdr where pick_order =  ? ";
					
				}
				else if(key.equalsIgnoreCase("R-TASK") || key.equalsIgnoreCase("S-DOC") || key.equalsIgnoreCase("RS-DSO") || key.equalsIgnoreCase("RO-DSO") || key.equalsIgnoreCase("R-DSO") || key.equalsIgnoreCase("R-PSO") )
				{
					//Changed By Pragyan 26/12/12 to check the join with detail for valid records.
					//sql = "SELECT CONFIRMED FROM REPL_ISS_HDR WHERE REPL_ORDER = ?";
					sql = "SELECT R.CONFIRMED FROM REPL_ISS_HDR R,REPL_ISS_DET D WHERE R.TRAN_ID = D.TRAN_ID AND R.REPL_ORDER=?";
				}

				else if(key.equalsIgnoreCase("M-PACK"))
				{
					//Changed By Pragyan 26/12/12 to check the join with detail for valid records.
					sql = "SELECT CONFIRMED FROM PACK_HDR WHERE TRAN_ID = ?";
					//sql = "SELECT P.CONFIRMED FROM PACK_HDR P,PACK_DET D WHERE P.TRAN_ID = ? AND P.TRAN_ID = D.TRAN_ID";
				}*/
				if(!"S-DSP".equalsIgnoreCase(key))
				{
					sql = "SELECT STATUS FROM WAVE_TASK_DET WHERE REF_ID = ? ";
				}
				//Changed by sumit on 19/03/12 checking confirmation status from wave_task_det end
				else
				{
					continue;
				}


				ArrayList tempList = (ArrayList)taskMap.get(key);
				int ordLen = tempList.size();
				pstmt = conn.prepareStatement(sql);

				for(int i = 0;i < ordLen; i++)
				{
					String 	refId = (String)tempList.get(i);				
					pstmt.setString(1,refId);
					rs = pstmt.executeQuery();

					if(rs.next())
					{
						//Changed by sumit on 19/03/13 changing logic of checking of confirmation start 
						//if(key.equalsIgnoreCase("P-TASK")  || key.equalsIgnoreCase("C-PICK") )  //added by Kunal on 11/09/12
						//Changed by sumit on 24/01/13 removed "p-pick" ref ser from if condition 
						//if(key.equalsIgnoreCase("P-TASK")  || key.equalsIgnoreCase("C-PICK")  || key.equalsIgnoreCase("P-PICK") )  //added by Kunal on 11/09/12
						/*if(key.equalsIgnoreCase("P-TASK")  || key.equalsIgnoreCase("C-PICK") )  //added by Kunal on 11/09/12
						{
							status = rs.getString("status");
							if(!"C".equalsIgnoreCase(status))
							{
								return isConfirmed = false;
							}
						}
						//Changed by sumit on 24/01/13 add "p-pick" ref ser in if condition 
						//else if( key.equalsIgnoreCase("A-PICK") ||  key.equalsIgnoreCase("M-PICK") )
						else if( key.equalsIgnoreCase("A-PICK") ||  key.equalsIgnoreCase("M-PICK") || key.equalsIgnoreCase("P-PICK") )
						{
							status = rs.getString("status");
							if(!"C".equalsIgnoreCase(status))
							{
								return isConfirmed = false;
							}
							else
							{
								// check all the issues are 
								int countTrf = 0;
								//Changed By Pragyan 26/12/12 to check the join with detail for valid records.
								//sql1 = "SELECT count(1)  FROM pick_iss_hdr WHERE pick_order = ? and case when TRF_STATUS is null then 'N' else TRF_STATUS end = 'N'";
								sql1 = "SELECT COUNT(1)  FROM PICK_ISS_HDR P,PICK_ISS_DET D WHERE P.PICK_ORDER = ? AND " +
										" CASE  WHEN P.TRF_STATUS IS NULL THEN 'N' ELSE P.TRF_STATUS END = 'N' AND P.TRAN_ID = D.TRAN_ID";
								pstmt1 = conn.prepareStatement(sql1);
								pstmt1.setString(1,refId);
								rs1 = pstmt1.executeQuery();
								if (rs1.next())
								{
									countTrf = rs1.getInt(1);
								}
								rs1.close();
								rs1 = null;
								pstmt1.close();
								pstmt1 = null;
								
								if(countTrf > 0)
								{
									return isConfirmed = false;
								}
								
							}
							
						}

						else
						{
							String conf = rs.getString("CONFIRMED");

							if(conf != null && conf.equalsIgnoreCase("N"))
							{
								return isConfirmed = false;
							}
						}*/
						status = rs.getString("status");
						if( "N".equalsIgnoreCase(status))
						{
							return isConfirmed = false;
						}
						//Changed by sumit on 19/03/13 changing logic of checking of confirmation end

					}
					else
					{
						return isConfirmed = false;
					}

					pstmt.clearParameters();
				}

			}
		}
		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 e)
			{
				throw new ITMException(e) ;
			}
		}

		return isConfirmed;
	}

	//Chnaged by Rohan on 30-03-13 to change modifier to public to call from ConsolidatToDocPrc.java
	//private boolean isShipmentExist(String despId , Connection conn)throws Exception
	public boolean isShipmentExist(String despId , Connection conn)throws Exception
	{
		String sql = "";
		boolean isDespExist  = false;		
		PreparedStatement pstmt = null;
		ResultSet rs = null;	

		try
		{

			sql = "SELECT COUNT(*) AS COUNT FROM DESPATCHDET WHERE DESP_ID = ?";


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

			if(rs.next())
			{
				int count = rs.getInt(1);
				if(count >= 1)
				{
					return isDespExist = true;
				}
			}
		}
		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 e)
			{
				throw new ITMException(e) ;
			}
		}

		return isDespExist;
	}
	//Changed by Rohan 06/10/12 to printing Hazmat repoprt start.
    private String generateReport(String ptcn,String reportObjName,String name, Connection conn,String xtraParams ) throws ITMException
    {
    	String errString ="";
    	String printMode = "";
    	String outputFilename = "";
    	TreeMap argMap = null;
    	DistCommon discommon = new DistCommon();
    	GenericUtility genericUtility = GenericUtility.getInstance();
    	try
    	{    		
    		//String printerStr  = discommon.getDisparams("999999","STOCK_TO_DOCK_PRINTER",conn);
    		String printerStr  = discommon.getDisparams("999999","HAZMAT_PRINTER",conn);
    		System.out.println("PRINT MODE CALLED&&&&&&&*****"+printerStr);
    		String chgUser = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginCode");								
			System.out.println(" printerStr ->["+printerStr+"]");				
			if(printerStr.trim().length() > 0)
			{					
				//String reportObjName = discommon.getDisparams("999999","STOCK_TO_DOCK_OBJ_NAME",conn);
				//String reportObjName = discommon.getDisparams("999999","HAZMAT_OBJ_NAME",conn);
				//String reportSavetype = "NATIVE";
				String reportSavetype = discommon.getDisparams("999999","HAZMAT_REPORT_SAVE_TYPE",conn);
				
				ibase.webitm.reports.utility.JasperReportGenerator jrGenerator = new ibase.webitm.reports.utility.JasperReportGenerator();
				String xsdString ="<Arguments>" +
							"<Argument id=\"PTCN\" name=\"PTCN\">" +
							"<argType>java.lang.String</argType>" +
							//"<value>"+ptcnList.get(i)+"</value>" +
							"<value>"+ptcn+"</value>" +
							"</Argument>" +
							/*"<Argument id=\"PTCN\" name=\"PTCN\">" +
							"<argType>java.lang.String</argType>" +
							//"<value>"+ptcnList.get(i)+"</value>" +
							"<value>"+ptcn+"</value>" +
							"</Argument>" +*/
							"<Argument id=\"x_path\" name=\"x_path\">" +
							"<argType>java.lang.String</argType>" +
							"<value>/DocumentRoot/wave_task_main</value>" +
							"</Argument>" +
							"<Argument id=\"design_source\" name=\"design_source\">" +
							"<argType>java.lang.String</argType>" +
							"<value>wave_task_main.jrxml</value>" +
							"</Argument>" +
							"<Argument id=\"data_source\" name=\"data_source\">" +
							"<argType>java.lang.String</argType>" +
							"<value>SQL</value>" +
							"</Argument>" +
							"<Argument id=\"bind_type\" name=\"bind_type\">" +
							"<argType>java.lang.Integer</argType>" +
							"<value>0</value>" +
							"</Argument>" +
							"<Argument id=\"report_type\" name=\"report_type\">" +
							"<argType>java.lang.String</argType>" +
							"<value>JASPER</value>" +
							"</Argument>" +
							"<Argument id=\"report_save_type\" name=\"report_save_type\">" +
							"<argType>java.lang.String</argType>" +
							//Changed by sumit on 07/09/12  changed NATIVE to PDF
							//"<value>NATIVE</value>" +									
							"<value>"+reportSavetype+"</value>" +
							"</Argument>" +
							"</Arguments>";
				System.out.println(" XSD parser **->["+xsdString+"]");						
				//errString = jrGenerator.printReport(reportObjName, xsdString, chgUser, printerStr, 1, conn);
				if( "PDF".equalsIgnoreCase(reportSavetype))
				{
					System.out.println("Enter in pdf)))))=================");
					XSDParser xsdParser = new XSDParser(reportObjName);
					argMap = jrGenerator.createArgMap(xsdString);
					System.out.println("TreeMap:::->"+argMap);
					outputFilename = ptcn+"_"+name;//file name for report
					jrGenerator.createReport(reportObjName , argMap, xsdParser, chgUser, outputFilename, conn);
				}
					
				//}
				System.out.println(" PRINTING task completed--------**------->");
			}			
    	}
    	catch (Exception e)
    	{
    		e.printStackTrace();
    		throw new ITMException(e);
    	}
    	
    	return errString;
    }
    //Changed by Rohan 06/10/12 to printing Hazmat repoprt.end
  //Changed by sumit on 31/01/12 convert qty as per as unit start
    private double getStockQtyAfterConvert(String saleOrder, String lineNo,String itemCode,double quantity,int check, Connection conn) throws ITMException
    {
	   	 String sql = "";
	   	 String itemUnit = "";
	   	 String orderUnit = "";
	   	 String unitStd = "";	 
	   	 double convQtyStduom = 0.0;
	   	 double newQty = 0.0;
	   	 double convFact = 0;
	   	 PreparedStatement pstmt = null;
	   	 ResultSet rs = null;
	   	 
	   	 try
	   	 {
	   		DistCommon discommon = new DistCommon();
	   		 //System.out.println(" -- saleOrder ["+saleOrder+"] lineNo ["+lineNo+"] itemCode ["+itemCode+"] ");
	   		 sql = "SELECT DISTINCT ITEM.UNIT ,SORDDET.UNIT AS ORDER_UNIT,SORDDET.UNIT__STD AS  UNIT__STD, " +
	   			 		" SORDDET.CONV__QTY_STDUOM FROM ITEM, SORDDET WHERE ITEM.ITEM_CODE = ? " +
	   			 		" AND SORDDET.SALE_ORDER = ? AND SORDDET.LINE_NO = ? AND ITEM.ITEM_CODE = SORDDET.ITEM_CODE ";
	   		 pstmt = conn.prepareStatement(sql);
	   		 pstmt.setString(1, itemCode);
	   		 pstmt.setString(2, saleOrder);
	   		 pstmt.setString(3, lineNo);
	   		 rs = pstmt.executeQuery();
	   		 if( rs.next())
	   		 {
	   			 itemUnit = rs.getString("UNIT");
	   			 orderUnit = rs.getString("ORDER_UNIT");
	   			 unitStd = rs.getString("UNIT__STD");
	   			 convQtyStduom = rs.getDouble("CONV__QTY_STDUOM");			
	   		 }
	   		 rs.close(); rs = null;
	   		 pstmt.close(); pstmt = null;
	   		 ArrayList convQtyList = new ArrayList();
	   		 
	   		 if(!orderUnit.equalsIgnoreCase(unitStd))
	   		 {
	   			if(convQtyStduom != 0 && check == 1)
	   			{
	   				convFact = convQtyStduom;
	   			}
	   			System.out.println(" orderUnit ["+orderUnit+"] itemUnit ["+itemUnit+"] unitStd ["+unitStd+"] convQtyStduom ["+convQtyStduom+"]  convFact["+convFact+"]");
	   			if( check == 1 )
	   			{
	   				convQtyList = new ArrayList();
	   				convQtyList = discommon.getConvQuantityFact(orderUnit, unitStd, itemCode, quantity, convFact,conn);
	   			}
	   			else if ( check == 2 )
	   			{
	   				convQtyList = new ArrayList();
	   				convQtyList = discommon.getConvQuantityFact(unitStd, orderUnit, itemCode, quantity, convFact,conn);
	   			}
	   			System.out.println("convQtyList = "+convQtyList);
	   			newQty = Double.parseDouble(convQtyList.get(1).toString());
	   			System.out.println("newQty ["+newQty+"]");
	   	
	   			if(newQty == -999999999)
	   			{
	   				newQty = 0;								
	   			}
	   			if (convQtyStduom == 0)
	   			{
	   				convQtyStduom= Double.parseDouble(convQtyList.get(0).toString());
	   			}
	   		 }
	   		 else
	   		 {
	   			 newQty = quantity;
	   		 }
	   		//pendingQty = meffQty;
	   		 
	   	 }
	   	 catch(Exception ex)
	   	 {
	   		 ex.printStackTrace();
	   		 throw new ITMException(ex);
	   	 }
	   	 return newQty;
    }
   //Changed by sumit on 31/01/12 convert qty as per as unit end
    //Changed by sumit on 13/03/12 to reterive shipper size start
	private HashMap getItemVoumeMap(String itemCode,String lotNo,Connection con)throws Exception
	{
		double shipperSize = 0,itemVolume = 0,caseVolume = 0;
		PreparedStatement pstmt = null;
		String sql="";
		ResultSet rs = null;
		double itmLen = 0,itmWidth = 0,itmHeight = 0,itemGrossWeight = 0,lotLen = 0 ,lotHeight = 0,lotWidth = 0,caseGrossWeight = 0,itemNetWeight = 0, caseNetWeight = 0;
		HashMap dataVolumeMap = new HashMap();
		
		try {
			
			sql = "SELECT I.LENGTH ITEM_LEN,I.WIDTH ITEM_WID,I.HEIGHT ITEM_HEIGHT,I.GROSS_WEIGHT ITEM_GROSS_WEIGHT,I.NET_WEIGHT ITEM_NET_WEIGHT,"
				  +" L.LENGTH LITEM_LEN,L.WIDTH LITEM_WID,L.HEIGHT LITEM_HEIGHT,L.SHIPPER_SIZE SHIPSIZE,L.GROSS_WEIGHT CASE_GROSS_WEIGHT,L.NET_WEIGHT CASE_NET_WEIGHT FROM"
				  +" ITEM I,ITEM_LOT_PACKSIZE L"
				  +" WHERE I.ITEM_CODE = L.ITEM_CODE"
				  +" AND L.LOT_NO__FROM <= ? AND L.LOT_NO__TO >= ?"
				  +" AND  I.ITEM_CODE = ?";
				  
			
			pstmt = con.prepareStatement(sql);
			if(lotNo != null && lotNo.length() > 0)
			{
				pstmt.setString(1, lotNo);
				pstmt.setString(2, lotNo);
			}
			else
			{
				pstmt.setString(1, "00");
				pstmt.setString(2, "ZZ");
			}
			pstmt.setString(3, itemCode);
			rs = pstmt.executeQuery();
			
			if(rs.next())
			{
				itmLen = rs.getDouble("ITEM_LEN");
				itmWidth = rs.getDouble("ITEM_WID");
				itmHeight = rs.getDouble("ITEM_HEIGHT");
				itemGrossWeight = rs.getDouble("ITEM_GROSS_WEIGHT");
				lotLen = rs.getDouble("LITEM_LEN");
				lotWidth = rs.getDouble("LITEM_WID");
				lotHeight = rs.getDouble("LITEM_HEIGHT");				
				shipperSize = rs.getDouble("SHIPSIZE");				
				caseGrossWeight = rs.getDouble("CASE_GROSS_WEIGHT");				
				itemNetWeight = rs.getDouble("ITEM_NET_WEIGHT");				
				caseNetWeight = rs.getDouble("CASE_NET_WEIGHT");				
			}
			
			//shipperSize = (lotHeight * lotWidth * lotLen)/(itmLen * itmWidth * itmHeight);
			itemVolume = (itmLen * itmWidth * itmHeight);
			caseVolume = (lotHeight * lotWidth * lotLen);
			
			dataVolumeMap.put("SHIPPER_SIZE", shipperSize);
			dataVolumeMap.put("ITEM_VOLUME", itemVolume);
			dataVolumeMap.put("CASE_VOLUME", caseVolume);
			dataVolumeMap.put("ITEM_GROSS_WEIGHT", itemGrossWeight);
			dataVolumeMap.put("CASE_GROSS_WEIGHT", caseGrossWeight);
			dataVolumeMap.put("ITEM_NET_WEIGHT", itemNetWeight);
			dataVolumeMap.put("CASE_NET_WEIGHT", caseNetWeight);
			
			if(pstmt != null)
			{
				pstmt.close();
				pstmt = null;
		
			}
			if(rs != null)
			{
				rs.close();
				rs = null;
		
			}
			
			
			
		} catch (Exception e) {
			// TODO: handle exception
	
			throw e;
		}
		finally
		{
			if(pstmt != null)
			{
				pstmt.close();
				pstmt = null;
		
			}
			if(rs != null)
			{
				rs.close();
				rs = null;
		
			}
		}
		
		return dataVolumeMap;
	}
	//Changed by sumit on 13/03/12 to reterive shipper size end
	
	//Changed by Dhanraj on 19-SEP-14  [W14FSUN003]	
	private String updatedespatch(String ptcn,Connection conn) throws Exception
	{  
		String errString = "";		
		String sql=null;
		double tareWt=0.0;
		double fillerWt=0.0;
		System.out.println("Call Despatch update method in consolidateToDoc");
		PreparedStatement pstmt = null;
		ResultSet rSet = null ;
		try
		{
			sql = " SELECT SUM(NVL(FILLER_WEIGHT,0)) FILLER_WEIGHT , SUM(NVL(TARE_WEIGHT,0)) TARE_WEIGHT  FROM CARTON_MASTER "
				+" WHERE PTCN = ? AND CARTON_TYPE = 'M' AND STATUS <> 'R' ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,ptcn);
		    rSet = pstmt.executeQuery();
			
			if (rSet.next())
			{
				fillerWt = rSet.getDouble("FILLER_WEIGHT");
				tareWt = rSet.getDouble("TARE_WEIGHT");
			}
			System.out.println("Sum fillerWt consolidateToDoc" +fillerWt);
			System.out.println("sum tare wate consolidateToDoc "+tareWt);
			
			if( rSet != null)
			{
				rSet.close();
				rSet = null;
			}
			if(pstmt != null)
			{
				pstmt.close();
				pstmt = null;
			}
			
			sql = " UPDATE DESPATCH SET TARE_WEIGHT= ? ,FILLER_WEIGHT= ? WHERE PTCN= ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setDouble(1, tareWt);
			pstmt.setDouble(2, fillerWt);
			pstmt.setString(3, ptcn);
			int updateCnt=pstmt.executeUpdate();
		    
			if(updateCnt > 0){
				System.out.println("<============Updation done ConsolidateToDoc==========>");
			}
			if( pstmt != null )
			{
				pstmt.close();
				pstmt = null;
			}
			
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw e;
		}
		finally
		{
			try
			{
				if( rSet != null)
				{
					rSet.close();
					rSet = null;
				}
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
			}
			catch(Exception e)
			{
				e.printStackTrace();	
				throw e;
			}		
		}
		return errString;
	}
	//End Changed by Dhanraj on 19-SEP-14  [W14FSUN003]	
}