/*******************************************    
Title : DocTranPostSaveEJB
    Date  : 03/08/12
    Author: CPandey

 ********************************************************/
package ibase.webitm.ejb.wms;
import ibase.system.config.*;
import ibase.webitm.ejb.*;
import ibase.webitm.ejb.dis.DistCommon;
import ibase.webitm.ejb.dis.InvAllocTraceBean;
import ibase.webitm.ejb.dis.StockUpdate;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.utility.TransIDGenerator;
import ibase.utility.CommonConstants;

import java.io.*;
import java.rmi.RemoteException;
import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;

import org.w3c.dom.*;

import javax.ejb.*;
import javax.naming.InitialContext;
@javax.ejb.Stateless
public class DockTranConf extends ValidatorEJB implements DockTranConfLocal,DockTranConfRemote
{
	/*public String postSave()throws RemoteException,ITMException
	{
		return "";
	}*/
	//Changed By Pragyan 09-MAY-14 to Take Local connection 
	//public String postSave(String domString, String tranId,String editFlag, String xtraParams, Connection conn ) throws RemoteException,ITMException
	public String confirm(String domString, String docktranID,String editFlag, String xtraParams, Connection conn ) throws RemoteException,ITMException
	{
		
		System.out.println( "Calling DockTranConfejb.........................................." );
		String forcedFlag = "false" ;
		String errString = "";
		String sql = "";
		String pickOrder = "";
		String pickType = "";
		String palletNo = "";
		String itemCode = "",siteCode = "",locCode = "",lotNo = "",lotSl = "",locCodeSys = "",
				saleOrder = "",lineNoSord = "",replOrder = "",retString = "",ptcn="";
		double quantity = 0.0,noArt = 0.0;
		int totOrdDet = 0,lineNo = 0;
		int ordDetScaned = 0;
		int updCnt = 0;
		boolean toCommit = false;

		java.sql.Timestamp expDate = null,mfgDate = null,restestDate = null;
		Document dom = null;
		GenericUtility genericUtility = GenericUtility.getInstance();

		java.util.Date currDate1 = new java.util.Date();

		java.sql.Timestamp currDate = new java.sql.Timestamp( System.currentTimeMillis() );
		StockUpdate stkUpdate = null;
		String acctCodeDr = "",cctrCodeDr = "",packCode = "",unit = "",grade = "",remarks = "",
				unitAlt = "",stagingLoc = "", batchNo = "",siteCodeMfg = "",packInstr = "",
				suppCodeMfg = "",dimension = "",locCodeTo = "", pickisstranId = "";
		String updateSql = "";
		boolean isError = false;
		String empCode = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginEmpCode" );
		//StockUpdate stkUpdate = null;
		InvAllocTraceBean invAllocTrace = null;
		HashMap strAllocate = null;
		DistCommon distCommon = new DistCommon();
		//stagingLoc = distCommon.getDisparams( "999999", "STAGING_LOC", conn );
		stkUpdate = new StockUpdate();
		invAllocTrace = new InvAllocTraceBean();		
		double stkRate = 0.0,stkGrossRate = 0.0,convQtyStduom = 0.0,batchSize = 0.0;
		PreparedStatement pstmt = null,pstmtStock = null,pstmtUpd = null;
		ResultSet rs = null,rsStock = null;
		HashMap updateRowMap = null;
		PickIssConf pickConf = new PickIssConf();
		// 10/10/12 manoharan
		HashMap itmVolumeMap = new HashMap();
		double shipperSize = 0, itemGrossWeight = 0, caseGrossWeight = 0, itemNetWeight = 0, caseNetWeight = 0;
		// end 10/10/12 manoharan
		
		//Changed By 02/05/13 Pragyan To check the sordalloc allocated quantity > quantity  then insert new record deducte quantity from old location.start
		PreparedStatement pstmt1 = null,pstmtSord = null;
		ResultSet rs1 = null,rsSord = null;
		String expLev = "",itemCodeOrd = "",itemRef = "",status = "",allocMode = "",
				itemGrade = "",reasCode = "",unitStd = "",sSQL = "";
		java.sql.Timestamp dateAlloc = null;
		double qtyOrd = 0.0,sordAllocQty = 0.0;
		ITMDBAccessEJB itmDBAccess = new ITMDBAccessEJB();
		//Changed By 02/05/13 Pragyan To check the sordalloc allocated quantity > quantity  then insert new record deducte quantity from old location.end
		
		//Changed By Pragyan 09-MAY-14 to Take Local connection.Start
		/*ConnDriver connDriver = null;
		Connection conn=null;*/
		
		try
		{
			//Changed By Pragyan 09-MAY-14 to Take Local connection.Start
			/*connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverValidator");
			if(conn==null)
			{
				System.out.println("Connection is null");
				return "";
			}*/
			conn.setAutoCommit(false);
			stagingLoc = distCommon.getDisparams( "999999", "STAGING_LOC", conn );
			//Changed By Pragyan 09-MAY-14 to Take Local connection.end
			
			updateRowMap = new HashMap() ;
			String applDateFormat = genericUtility.getApplDateFormat();
			String dbDateFormat = genericUtility.getDBDateFormat();
			SimpleDateFormat sdf = new SimpleDateFormat(applDateFormat);
			String currDateStr = sdf.format(currDate1);
			java.sql.Timestamp tranDate = java.sql.Timestamp.valueOf(genericUtility.getValidDateString( currDateStr , applDateFormat, dbDateFormat ) + " 00:00:00.0") ;
			//conn.setAutoCommit(false);

			dom = genericUtility.parseString(domString);

			palletNo = genericUtility.getColumnValue("pallet_no", dom, "1", "1");
			//locCodeSys = genericUtility.getColumnValue("loc_code__sys", dom, "1", "1");
			System.out.println("Location code to ->>["+locCodeSys+"]");
			locCodeTo = genericUtility.getColumnValue("loc_code__to", dom, "2" );
			//pickOrder = genericUtility.getColumnValueFromNode("pick_order", dom.getElementsByTagName("Detail2").item(0));
			System.out.println("palert no from generic utility ["+palletNo+"]  locCodeTo [" + locCodeTo  + "]");
			//sql = "SELECT HDR1.tran_id tran_id,HDR.PICK_TYPE,HDR1.PICK_ORDER FROM PICK_ORD_HDR HDR,PICK_ISS_HDR HDR1 WHERE HDR.PICK_ORDER = HDR1.PICK_ORDER AND HDR1.PALLET_NO = ?";//PICK_ISS_HDR
			//Changed By Pragyan 06/03/12 To check only "N" trf status
			//sql = "SELECT HDR.PICK_TYPE,HDR1.PICK_ORDER FROM PICK_ORD_HDR HDR,PICK_ISS_HDR HDR1 WHERE HDR.PICK_ORDER = HDR1.PICK_ORDER AND HDR1.PALLET_NO = ?";//PICK_ISS_HDR
		/*	sql = "SELECT HDR1.TRAN_ID TRAN_ID,HDR.PICK_TYPE,HDR1.PICK_ORDER FROM PICK_ORD_HDR HDR,PICK_ISS_HDR HDR1 WHERE HDR.PICK_ORDER = HDR1.PICK_ORDER AND HDR1.PALLET_NO = ? AND (CASE WHEN HDR1.TRF_STATUS IS NULL THEN 'N' ELSE HDR1.TRF_STATUS END) = ?";//PICK_ISS_HDR
			pstmt = conn.prepareStatement(sql);
			pstmt.setString( 1, palletNo);
			pstmt.setString( 2, "N");
			rs = pstmt.executeQuery();
			
			if(rs.next())
			{
				tranId = rs.getString("tran_id")==null?"":rs.getString("tran_id");
				pickType = rs.getString("pick_type")==null?"":rs.getString("pick_type");
				pickOrder = rs.getString("pick_order")==null?"":rs.getString("pick_order");
				System.out.println("pick order and pick type----  ["+palletNo+"] and pickType is ----["+pickType+"]");
			}
			rs.close(); rs = null;
			pstmt.close(); pstmt = null;*/
			
			sql = " SELECT PICK_TYPE FROM DOCK_TRAN WHERE TRAN_ID = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString( 1, docktranID);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				pickType = rs.getString("PICK_TYPE");
			}
			rs.close(); rs = null;
			pstmt.close(); pstmt = null;
		   System.out.println("pickType:::::::::"+pickType);
			/*sql = "UPDATE PICK_ISS_DET SET LOC_CODE__TO = ? WHERE TRAN_ID = ?  ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString( 1, locCodeTo);
			pstmt.setString( 2, tranId);
			pstmt.executeUpdate();
			pstmt.close(); pstmt = null;*/
			
			sql = "UPDATE DOCK_TRAN_DET SET LOC_CODE__TO = ? WHERE TRAN_ID = ?  ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString( 1, locCodeTo);
			pstmt.setString( 2, docktranID);
			pstmt.executeUpdate();
			pstmt.close(); pstmt = null;

			//Update the carton master
			/*
			sql = "UPDATE CARTON_MASTER SET STATUS = 'U' WHERE CARTON_NO IN(SELECT CARTON_NO FROM PICK_ISS_DET WHERE PICK_ORDER = ? )";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, pickOrder);
			int updCnt = pstmt.executeUpdate();
			pstmt.close(); pstmt = null;
			if(updCnt > 0)
			{
				System.out.println("---row updated sucessfully of master pick---->"+updCnt);
			}
			*/
			
			//validation for null
			//stagingLoc = distCommon.getDisparams( "999999", "STAGING_LOC", conn )==null?"":distCommon.getDisparams( "999999", "STAGING_LOC", conn );
			if("M".equalsIgnoreCase(pickType))
			{
			
				stagingLoc = distCommon.getDisparams( "999999", "STAGING_LOC_MPK", conn)==null?"":distCommon.getDisparams( "999999", "STAGING_LOC_MPK", conn);
			}
			else
			{
				stagingLoc = distCommon.getDisparams( "999999", "STAGING_LOC", conn)==null?"":distCommon.getDisparams( "999999", "STAGING_LOC", conn);
			}
			//allocation for dock location 
			
			sql = "select acct_code__inv, cctr_code__inv, "
					+ "exp_date, pack_code, mfg_date, site_code__mfg, "
					+ "pack_instr, supp_code__mfg, retest_date, rate, "
					+ "gross_rate, conv__qty_stduom, unit__alt, batch_no, batch_size, unit, grade, remarks, dimension "
					+ "from stock "
					+ "where item_code = ? "
					+ "and site_code = ? "
					+ "and loc_code = ? "
					+ "and lot_no = ? "
					+ "and lot_sl = ? ";
			pstmtStock = conn.prepareStatement(sql);

			/*sql = "SELECT R.LINE_NO LINE_NO, R.ITEM_CODE ITEM_CODE, R.SITE_CODE SITE_CODE, R.LOC_CODE LOC_CODE, "
					//Changed by sumit on 14/03/13 getting quantity as (quantity - dealloc_qty)
					//+ " R.LOT_NO LOT_NO, R.LOT_SL LOT_SL, R.QUANTITY QUANTITY, R.NO_ART NO_ART, "
					+ " R.LOT_NO LOT_NO, R.LOT_SL LOT_SL, ( R.QUANTITY - CASE WHEN R.DEALLOC_QTY IS NULL THEN 0 ELSE R.DEALLOC_QTY END) AS QUANTITY, R.NO_ART NO_ART, "
					+ " R.LOC_CODE__TO LOC_CODE__TO, O.SALE_ORDER SALE_ORDER, O.LINE_NO__SORD LINE_NO__SORD, O.PICK_ORDER,H.PICK_TYPE  "
					+ " FROM PICK_ISS_DET R,  PICK_ORD_DET O,PICK_ORD_HDR H" 
					+ " WHERE R.TRAN_ID = ? "
					+ " AND R.PICK_ORDER = O.PICK_ORDER "   
					+"AND H.PICK_ORDER = O.PICK_ORDER"  
					+ " AND O.LINE_NO = R.LINE_NO__ORD " 
					//Changed by Rohan on 29-06-13 for adding condition of quantity > 0
					+" AND ( R.QUANTITY - CASE WHEN R.DEALLOC_QTY IS NULL THEN 0 ELSE R.DEALLOC_QTY END) > 0 "
					+" AND R.LOC_CODE <> R.LOC_CODE__TO ";*/
			
			sql = " SELECT D.SITE_CODE, D.TRAN_ID__ISS, DT.ITEM_CODE, DT.LOC_CODE, DT.LOT_NO, DT.LOT_SL, DT.QUANTITY, DT.NO_ART, DT.LOC_CODE__TO, DT.SALE_ORDER, DT.LINE_NO__SORD, " +
				  " DT.PICK_ORDER, DT.LINE_NO__PICK, D.PICK_TYPE FROM DOCK_TRAN D, DOCK_TRAN_DET DT WHERE D.TRAN_ID = DT.TRAN_ID AND DT.TRAN_ID = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, docktranID);
			rs = pstmt.executeQuery();
			while(rs.next())
			{

				itemCode = rs.getString( "item_code" );
				siteCode = rs.getString( "site_code" );
				locCode = rs.getString( "loc_code" );
				lotNo = rs.getString( "lot_no" );
				lotSl = rs.getString( "lot_sl" );
				quantity = rs.getDouble( "quantity" );
				noArt = rs.getDouble( "no_art" );
				locCodeTo = rs.getString( "loc_code__to" );
				saleOrder = rs.getString( "SALE_ORDER" );
				lineNoSord = rs.getString( "LINE_NO__SORD" );
				pickOrder = rs.getString( "PICK_ORDER" );
				lineNo = rs.getInt( "LINE_NO__PICK" );
				pickType = rs.getString( "PICK_TYPE" );
				pickisstranId =   rs.getString( "TRAN_ID__ISS" );
				System.out.println("pick type from hdr->> ["+pickType+"]");
				System.out.println("tranIdissue->> ["+pickisstranId+"]");
				// 16/10/11 manoharan check whether allocation against sales order is there
				// if allocation against sales order found change allocation to point to loc_code__to
				// and deallocate the same for loc_code in stock
				// update sordalloc with loc_code__to
				// This utility methods to update old and new location should be in 
				// a separate probably in stockupdate so that we can call the same from various places 
				//
				// 
			

				pstmtStock.setString(1,itemCode);
				pstmtStock.setString(2,siteCode);
				pstmtStock.setString(3,locCode);
				pstmtStock.setString(4,lotNo);
				pstmtStock.setString(5,lotSl);
				rsStock = pstmtStock.executeQuery();
				if ( rsStock.next() )
				{ 

					acctCodeDr = rsStock.getString("acct_code__inv");
					cctrCodeDr = rsStock.getString("cctr_code__inv");

					expDate = rsStock.getTimestamp("exp_date");
					packCode = rsStock.getString("pack_code");
					mfgDate = rsStock.getTimestamp("mfg_date");
					siteCodeMfg = rsStock.getString("site_code__mfg");
					packInstr = rsStock.getString("pack_instr");
					suppCodeMfg = rsStock.getString("supp_code__mfg");
					restestDate = rsStock.getTimestamp("retest_date");
					stkRate = rsStock.getDouble("rate");
					stkGrossRate = rsStock.getDouble("gross_rate");
					convQtyStduom = rsStock.getDouble("conv__qty_stduom");
					unitAlt = rsStock.getString("unit__alt");
					batchNo = rsStock.getString("batch_no");
					batchSize = rsStock.getDouble("batch_size");
					unit = rsStock.getString("unit");
					grade = rsStock.getString("grade");
					remarks = rsStock.getString("remarks");
					dimension = rsStock.getString("dimension");
				}
				rsStock.close(); rsStock = null;
				pstmtStock.clearParameters();
				// 10/10/12 manoharan
				itmVolumeMap.clear();
				itmVolumeMap = getItemVoumeMap(itemCode,lotNo,conn);
				shipperSize = (Double) itmVolumeMap.get("SHIPPER_SIZE");
				itemGrossWeight = (Double) itmVolumeMap.get("ITEM_GROSS_WEIGHT");
				caseGrossWeight = (Double) itmVolumeMap.get("CASE_GROSS_WEIGHT");
				itemNetWeight = (Double) itmVolumeMap.get("ITEM_NET_WEIGHT");
				caseNetWeight = (Double) itmVolumeMap.get("CASE_NET_WEIGHT");
				// end 10/10/12 manoharan
				System.out.println("locCode@@@------->>"+locCode);				
				updateRowMap.put("item_code", itemCode);
				updateRowMap.put("site_code", siteCode);
				//changes by Dadaso pawar on 05/02/15 [start]
				//updateRowMap.put("loc_code", stagingLoc);				
				updateRowMap.put("loc_code", locCode);
				//changes by Dadaso pawar on 05/02/15 [End]
				updateRowMap.put("lot_no", lotNo);
				updateRowMap.put("lot_sl", lotSl);
				updateRowMap.put("quantity", ""+quantity);
				updateRowMap.put("no_art", noArt);
				updateRowMap.put("tran_ser", "PICISS");
				updateRowMap.put("acct_code__cr",acctCodeDr);
				updateRowMap.put("cctr_code__cr",cctrCodeDr);
				updateRowMap.put("acct_code_inv",acctCodeDr);
				updateRowMap.put("cctr_code_inv",cctrCodeDr);
				updateRowMap.put("rate",Double.toString(stkRate));
				updateRowMap.put("gross_rate",Double.toString(stkGrossRate));
				updateRowMap.put("tran_id", pickisstranId );
				// 17/10/11 manoharan
				updateRowMap.put("line_no", "" + lineNo );
				// end 17/10/11 manoharan
				updateRowMap.put("tran_date", tranDate );
				//Changed By Pragyan
				//updateRowMap.put("tran_type","I"); 
				updateRowMap.put("tran_type","ID");

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

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

				//changed by gulzar on 12/24/2011
				updateRowMap.put("exp_date", expDate);
				updateRowMap.put("pack_code", packCode);
				updateRowMap.put("mfg_date", mfgDate);
				updateRowMap.put("site_code__mfg", siteCodeMfg);
				updateRowMap.put("pack_instr", packInstr);
				updateRowMap.put("supp_code__mfg", suppCodeMfg);
				updateRowMap.put("retest_date", restestDate);
				updateRowMap.put("conv__qty_stduom", ""+convQtyStduom);
				updateRowMap.put("unit__alt", unitAlt);
				updateRowMap.put("batch_no", batchNo);
				updateRowMap.put("batch_size", ""+batchSize);
				//End changes by gulzar on 12/24/2011
				// 10/10/12 manoharan weight and no_art to be set properly
				if("A".equalsIgnoreCase(pickType))
				{
					noArt = 1;
				}
				else
				{
					noArt = Math.floor((quantity) / shipperSize) ;
				}
				updateRowMap.put("no_art", noArt);
				updateRowMap.put("gross_weight", caseGrossWeight * noArt);
				updateRowMap.put("net_weight", caseNetWeight * noArt);
				updateRowMap.put("tare_weight", (caseGrossWeight - caseNetWeight) * noArt);
				// end 10/10/12  manoharan
				retString = stkUpdate.updateStock( updateRowMap, xtraParams, conn );
				if ( retString != null && retString.trim().length() > 0 )
				{
					isError = true;
					break;
				}
					updateRowMap.put("loc_code", locCodeTo);
//				else 
//				{
//					updateRowMap.put("loc_code", locCodeTo);
//				}
				updateRowMap.put("tran_type", "R");//Creating receipt

				retString = stkUpdate.updateStock( updateRowMap, xtraParams, conn );
				if ( retString != null && retString.trim().length() > 0 )
				{
					isError = true;
					break;
				}

				updateRowMap.clear();
				// 16/10/11 manoharan check whether allocation/hold are there
				// if so update the same
				//added by cpandey on 
				//if(!("A".equalsIgnoreCase(pickType)))
				//{
				strAllocate = new HashMap();
				strAllocate.put("tran_date",tranDate);
				strAllocate.put("ref_ser","PICISS");
				strAllocate.put("ref_id",pickisstranId);
				strAllocate.put("ref_line", "" + lineNo);
				strAllocate.put("site_code",siteCode);
				strAllocate.put("item_code",itemCode);
				strAllocate.put("loc_code",locCodeTo);
				strAllocate.put("lot_no",lotNo);
				strAllocate.put("lot_sl",lotSl);
				strAllocate.put("alloc_qty",new Double(quantity));
				strAllocate.put("chg_user",GenericUtility.getInstance().getValueFromXTRA_PARAMS(xtraParams,"loginCode"));
				strAllocate.put("chg_term",GenericUtility.getInstance().getValueFromXTRA_PARAMS(xtraParams,"termId"));
				strAllocate.put("chg_win","w_pick_iss");
				//}

				retString = invAllocTrace.updateInvallocTrace(strAllocate, conn);
				if ( retString != null && retString.trim().length() > 0 )
				{
					isError = true;
					break;
				}
				System.out.println("retString ::: " + retString);
				if (retString != null && retString.trim().length() > 0 )
				{
					isError = true;
					break;
				}
				strAllocate = null;
				
				lineNoSord = "   " + lineNoSord; 
				lineNoSord = lineNoSord.substring(lineNoSord.length()-3);
				//Changed By 02/05/13 Pragyan To check the sordalloc allocated quantity > quantity  then insert new record deducte quantity from old location.start
				/*// end 16/10/11 manoharan allocation stock for new location
				// 16/10/11 manoharan update sordalloc
				updateSql = "UPDATE SORDALLOC SET LOC_CODE = ? "
						+ " WHERE SALE_ORDER = ? AND LINE_NO = ? "
						+ " AND ITEM_CODE = ? "
						+ " AND LOC_CODE = ? "
						+ " AND LOT_NO = ? "
						+ " AND LOT_SL = ? "
						+ " AND QTY_ALLOC > 0";
				pstmtUpd = conn.prepareStatement(updateSql);
				pstmtUpd.setString(1, locCodeTo);
				pstmtUpd.setString(2, saleOrder);
				pstmtUpd.setString(3, lineNoSord);				
				pstmtUpd.setString(4, itemCode);				
				pstmtUpd.setString(5, locCode);				
				pstmtUpd.setString(6, lotNo);				
				pstmtUpd.setString(7, lotSl);				

				updCnt = pstmtUpd.executeUpdate(); //change done by kunal on 14/09/12 uncomment sql query     
				// end 16/10/11 manoharan update sordalloc
				 */
				
						
				sql = " SELECT EXP_LEV, ITEM_CODE__ORD, "
					+ " ITEM_REF, UNIT, DATE_ALLOC, STATUS, ITEM_GRADE, EXP_DATE, ALLOC_MODE, SITE_CODE, "
					+ " CONV__QTY_STDUOM, UNIT__STD,  MFG_DATE, SITE_CODE__MFG, REAS_CODE,QUANTITY,QTY_ALLOC FROM SORDALLOC  "
					+ " WHERE SALE_ORDER = ? AND LINE_NO = ? "
					+ " AND ITEM_CODE = ? "
					+ " AND LOC_CODE = ? "
					+ " AND LOT_NO = ? "
					+ " AND LOT_SL = ? "
					+ " AND QTY_ALLOC > 0";
					pstmt1 = conn.prepareStatement(sql);
					pstmt1.setString(1, saleOrder);
					pstmt1.setString(2, lineNoSord);				
					pstmt1.setString(3, itemCode);				
					pstmt1.setString(4, locCode);				
					pstmt1.setString(5, lotNo);				
					pstmt1.setString(6, lotSl);				
					rs1 = pstmt1.executeQuery();
					
					if( rs1.next())
					{
						expLev = rs1.getString( "EXP_LEV" );
						itemCodeOrd = rs1.getString( "ITEM_CODE__ORD" );
						itemRef = rs1.getString( "ITEM_REF" );
						unit = rs1.getString( "UNIT" );
						status = rs1.getString( "STATUS" );
						itemGrade = rs1.getString( "ITEM_GRADE" );
						allocMode = rs1.getString( "ALLOC_MODE" );
						siteCode = rs1.getString( "SITE_CODE" );
						siteCodeMfg = rs1.getString( "SITE_CODE__MFG" );
						reasCode = rs1.getString( "REAS_CODE" );
						unitStd = rs1.getString( "UNIT__STD" );
						convQtyStduom = rs1.getDouble( "CONV__QTY_STDUOM" );
						dateAlloc = rs1.getTimestamp("DATE_ALLOC");
						expDate = rs1.getTimestamp("EXP_DATE");
						mfgDate = rs1.getTimestamp("MFG_DATE");
						qtyOrd = rs1.getDouble( "QUANTITY" );
						sordAllocQty = rs1.getDouble( "QTY_ALLOC" );
					}
					else
					{
						isError = true;
						retString = itmDBAccess.getErrorString("","RECNOTSORA","");
						return retString;
					}
					rs1.close();
					rs1 = null;
					pstmt1.close();
					pstmt1 = null;
		
					
				sSQL = "SELECT COUNT(*) AS COUNT FROM SORDALLOC WHERE " 
						+ " SALE_ORDER = ? AND LINE_NO = ? "
						+ " AND ITEM_CODE = ? "
						+ " AND LOC_CODE = ? "
						+ " AND LOT_NO = ? "
						+ " AND LOT_SL = ? ";
				int count = 0;
				
				if(sordAllocQty > quantity)
				{
					//Changed by sumit on 09/05/13 substituting QUANTITY__STDUOM too.
					//updateSql = "UPDATE SORDALLOC SET   QTY_ALLOC = QTY_ALLOC - ? "
					//changed by sankara on 17/10/13 for update wave_flag
					//updateSql = "UPDATE SORDALLOC SET   QTY_ALLOC = QTY_ALLOC - ? ,QUANTITY__STDUOM = QUANTITY__STDUOM - ?" //LOC_CODE = ? ,LOT_SL = ? "
					  updateSql = "UPDATE SORDALLOC SET   QTY_ALLOC = QTY_ALLOC - ? ,QUANTITY__STDUOM = QUANTITY__STDUOM - ?, WAVE_FLAG = 'Y' "			
							+ " WHERE SALE_ORDER = ? AND LINE_NO = ? "
							+ " AND ITEM_CODE = ? "
							+ " AND LOC_CODE = ? "
							+ " AND LOT_NO = ? "
							+ " AND LOT_SL = ? "
							+ " AND QTY_ALLOC > 0";
						pstmtUpd = conn.prepareStatement(updateSql);
						pstmtUpd.setDouble(1, quantity);
						////Changed by sumit on 09/05/13 setting value as per sql.
						pstmtUpd.setDouble(2, quantity * convQtyStduom);
						pstmtUpd.setString(3, saleOrder);
						pstmtUpd.setString(4, lineNoSord);				
						pstmtUpd.setString(5, itemCode);				
						pstmtUpd.setString(6, locCode);				
						pstmtUpd.setString(7, lotNo);				
						pstmtUpd.setString(8, lotSl);				
	
						updCnt = pstmtUpd.executeUpdate();
						System.out.println("Changed By 02/05/13 Pragyan sordalloc updated  updCnt [" + updCnt + "]");
									
						pstmtSord = conn.prepareStatement(sSQL);
						pstmtSord.setString(1, saleOrder);
						pstmtSord.setString(2, lineNoSord);				
						pstmtSord.setString(3, itemCode);				
						/*if(("A".equalsIgnoreCase(pickType)) || ("M".equalsIgnoreCase(pickType)))
						{
							pstmtSord.setString(4, stagingLoc);
						}
						else
						{	
							pstmtSord.setString(4, locCodeTo);
						}			
						*/
						pstmtSord.setString(4, locCodeTo);
						pstmtSord.setString(5, lotNo);				
						pstmtSord.setString(6, lotSl);				
	
						rsSord = pstmtSord.executeQuery();
						
						if(rsSord.next())
						{
							count = rsSord.getInt(1);
						}
						
						
						if(count > 0)
						{
							
								System.out.println("Changed By 02/05/13 Pragyan sordalloc If alrady exist Location Update Inside grater  updCnt1st [" + updCnt + "]");
							  //changed by sankara on 17/10/13 for update wave_flag	
							  //updateSql = "UPDATE SORDALLOC SET QTY_ALLOC = QTY_ALLOC + ? ,QUANTITY__STDUOM = QUANTITY__STDUOM + ?"
								updateSql = "UPDATE SORDALLOC SET QTY_ALLOC = QTY_ALLOC + ? ,QUANTITY__STDUOM = QUANTITY__STDUOM + ?, WAVE_FLAG = 'Y' "
								+ " WHERE SALE_ORDER = ? AND LINE_NO = ? "
								+ " AND ITEM_CODE = ? "
								+ " AND LOC_CODE = ? "
								+ " AND LOT_NO = ? "
								+ " AND LOT_SL = ? "
								+ " AND QTY_ALLOC > 0";
								pstmtUpd = conn.prepareStatement(updateSql);
								pstmtUpd.setDouble(1, quantity);
								pstmtUpd.setDouble(2, quantity);
								pstmtUpd.setString(3, saleOrder);
								pstmtUpd.setString(4, lineNoSord);				
								pstmtUpd.setString(5, itemCode);				
								/*if(("A".equalsIgnoreCase(pickType)) || ("M".equalsIgnoreCase(pickType)))
								{
									pstmtUpd.setString(6, stagingLoc);
								}
								else
								{	
									pstmtUpd.setString(6, locCodeTo);
								}				
								*/
								pstmtUpd.setString(6, locCodeTo);
								pstmtUpd.setString(7, lotNo);				
								pstmtUpd.setString(8, lotSl);				
			
								updCnt = pstmtUpd.executeUpdate();
								System.out.println("Changed By 02/05/13 Pragyan sordalloc If alrady exist Location Update  Inside grater  updCnt2nd [" + updCnt + "]");
						}
						else
						{
							updateSql = "insert into sordalloc( SALE_ORDER, LINE_NO, EXP_LEV, ITEM_CODE__ORD, "
								+ " ITEM_CODE, LOT_NO, LOT_SL, LOC_CODE, ITEM_REF, QUANTITY, UNIT, "
								+ " QTY_ALLOC, DATE_ALLOC, STATUS, ITEM_GRADE, EXP_DATE, ALLOC_MODE, SITE_CODE, "
								+ " CONV__QTY_STDUOM, UNIT__STD, QUANTITY__STDUOM, MFG_DATE, SITE_CODE__MFG, "
								//changed by sankara on 15/10/13 for update wave_flag start.	
								/*	+ " REAS_CODE ) VALUES (?, ?, ?, ?, "
									+ " ?, ?, ? , ?, ?, ?, ?, "
									+ " ?,?, ?, ?, ?, ?, ?, "
									+ " ?, ?, ? , ?, ?, ?) ";   */
									+ " REAS_CODE, WAVE_FLAG ) VALUES (?, ?, ?, ?, "
									+ " ?, ?, ? , ?, ?, ?, ?, "
									+ " ?,?, ?, ?, ?, ?, ?, "
									+ " ?, ?, ? , ?, ?, ?, ?) ";
								//changed by sankara on 15/10/13 for update wave_flag end.	
		
							pstmtUpd = conn.prepareStatement(updateSql);
							pstmtUpd.setString(1, saleOrder);
							pstmtUpd.setString(2, lineNoSord);
							pstmtUpd.setString(3, expLev);
							pstmtUpd.setString(4, itemCodeOrd);
							pstmtUpd.setString(5, itemCode);
							pstmtUpd.setString(6, lotNo);
							pstmtUpd.setString(7, lotSl);
							//pstmtUpd.setString(8, locCodeTo);
							/*if(("A".equalsIgnoreCase(pickType)) || ("M".equalsIgnoreCase(pickType)))
							{
								pstmtUpd.setString(8, stagingLoc);
							}
							else
							{	
								pstmtUpd.setString(8, locCodeTo);
							}	*/
							//Changed by sumit on 09/05/13 
							pstmtUpd.setString(8, locCodeTo);
							pstmtUpd.setString(9, itemRef);
							pstmtUpd.setDouble(10, qtyOrd);
							pstmtUpd.setString(11, unit);
							pstmtUpd.setDouble(12, quantity);
							pstmtUpd.setTimestamp(13, dateAlloc);
							pstmtUpd.setString(14, status);
							pstmtUpd.setString(15, itemGrade);
							pstmtUpd.setTimestamp(16, expDate);
							pstmtUpd.setString(17, allocMode);
							pstmtUpd.setString(18, siteCode);
							pstmtUpd.setDouble(19, convQtyStduom ); 
							pstmtUpd.setString(20,  unitStd); 
							pstmtUpd.setDouble(21, quantity * convQtyStduom); 
							pstmtUpd.setTimestamp(22, mfgDate ); 
							pstmtUpd.setString(23, siteCodeMfg);
							pstmtUpd.setString(24, reasCode);
							//changed by sankara on 15/10/13 for update of wave_flag
							pstmtUpd.setString( 25, "Y");
							updCnt = pstmtUpd.executeUpdate();
							System.out.println("Changed By 02/05/13 Pragyan sordalloc inserted  updCnt [" + updCnt + "]");
						}
				}
				else
				{
				
						pstmtSord = conn.prepareStatement(sSQL);
						pstmtSord.setString(1, saleOrder);
						pstmtSord.setString(2, lineNoSord);				
						pstmtSord.setString(3, itemCode);				
						/*if(("A".equalsIgnoreCase(pickType)) || ("M".equalsIgnoreCase(pickType)))
						{
							pstmtSord.setString(4, stagingLoc);
						}
						else
						{	
							pstmtSord.setString(4, locCodeTo);
						}			*/
						pstmtSord.setString(4, locCodeTo);
						pstmtSord.setString(5, lotNo);				
						pstmtSord.setString(6, lotSl);				
	
						rsSord = pstmtSord.executeQuery();
						
						if(rsSord.next())
						{
							count = rsSord.getInt(1);
						}
						
						
						if(count > 0)
						{
						  //changed by sankara on 17/10/13 update wave_flag  	
						  //updateSql = "UPDATE SORDALLOC SET   QTY_ALLOC = QTY_ALLOC - ? ,QUANTITY__STDUOM = QUANTITY__STDUOM - ?" //LOC_CODE = ? ,LOT_SL = ? "
							updateSql = "UPDATE SORDALLOC SET   QTY_ALLOC = QTY_ALLOC - ? ,QUANTITY__STDUOM = QUANTITY__STDUOM - ?, WAVE_FLAG = 'Y' "
								+ " WHERE SALE_ORDER = ? AND LINE_NO = ? "
								+ " AND ITEM_CODE = ? "
								+ " AND LOC_CODE = ? "
								+ " AND LOT_NO = ? "
								+ " AND LOT_SL = ? "
								+ " AND QTY_ALLOC > 0";
								pstmtUpd = conn.prepareStatement(updateSql);
								pstmtUpd.setDouble(1, quantity); 
								pstmtUpd.setDouble(2, quantity); 
								pstmtUpd.setString(3, saleOrder);
								pstmtUpd.setString(4, lineNoSord);				
								pstmtUpd.setString(5, itemCode);				
								pstmtUpd.setString(6, locCode);				
								pstmtUpd.setString(7, lotNo);				
								pstmtUpd.setString(8, lotSl);				
								updCnt = pstmtUpd.executeUpdate();
								System.out.println("Changed By 02/05/13 Pragyan If alrady exist Location Update  updCnt1st [" + updCnt + "]");
						     //changed by sankara on 17/10/13 update wave_flag  		
							//updateSql = "UPDATE SORDALLOC SET QTY_ALLOC = QTY_ALLOC + ? ,QUANTITY__STDUOM = QUANTITY__STDUOM + ?"
							  updateSql = "UPDATE SORDALLOC SET QTY_ALLOC = QTY_ALLOC + ? ,QUANTITY__STDUOM = QUANTITY__STDUOM + ?, WAVE_FLAG = 'Y' "
								+ " WHERE SALE_ORDER = ? AND LINE_NO = ? "
								+ " AND ITEM_CODE = ? "
								+ " AND LOC_CODE = ? "
								+ " AND LOT_NO = ? "
								+ " AND LOT_SL = ? "
								+ " AND QTY_ALLOC > 0";
								pstmtUpd = conn.prepareStatement(updateSql);
								pstmtUpd.setDouble(1, quantity);
								pstmtUpd.setDouble(2, quantity);
								pstmtUpd.setString(3, saleOrder);
								pstmtUpd.setString(4, lineNoSord);				
								pstmtUpd.setString(5, itemCode);				
								/*if(("A".equalsIgnoreCase(pickType)) || ("M".equalsIgnoreCase(pickType)))
								{
									pstmtUpd.setString(6, stagingLoc);
								}
								else
								{	
									pstmtUpd.setString(6, locCodeTo);
								}				*/
								pstmtUpd.setString(6, locCodeTo);
								pstmtUpd.setString(7, lotNo);				
								pstmtUpd.setString(8, lotSl);				
			
								updCnt = pstmtUpd.executeUpdate();
								System.out.println("Changed By 02/05/13 Pragyan sordalloc If alrady exist Location Update  updCnt2nd [" + updCnt + "]");
						}
						else
						{
							//changed by sankara on 15/10/13 for update wave_flag
							//updateSql = "UPDATE SORDALLOC SET LOC_CODE = ? "
						      updateSql = "UPDATE SORDALLOC SET LOC_CODE = ?, WAVE_FLAG = 'Y' "
									+ " WHERE SALE_ORDER = ? AND LINE_NO = ? "
									+ " AND ITEM_CODE = ? "
									+ " AND LOC_CODE = ? "
									+ " AND LOT_NO = ? "
									+ " AND LOT_SL = ? "
									+ " AND QTY_ALLOC > 0";
							pstmtUpd = conn.prepareStatement(updateSql);
							pstmtUpd.setString(1, locCodeTo);
							pstmtUpd.setString(2, saleOrder);
							pstmtUpd.setString(3, lineNoSord);				
							pstmtUpd.setString(4, itemCode);				
							pstmtUpd.setString(5, locCode);				
							pstmtUpd.setString(6, lotNo);				
							pstmtUpd.setString(7, lotSl);				
	
							updCnt = pstmtUpd.executeUpdate(); 
						}																			
			}	
			//Changed By 02/05/13 Pragyan To check the sordalloc allocated quantity > quantity  then insert new record deducte quantity from old location.end

				
				updateSql = "UPDATE PICK_ISS_DET SET LOC_CODE__TO = ? "
					+ " WHERE TRAN_ID = ? AND LINE_NO = ? ";
					
				
				pstmtUpd = conn.prepareStatement(updateSql);
				pstmtUpd.setString(1, locCodeTo);
				pstmtUpd.setString(2, pickisstranId);
				pstmtUpd.setInt(3, lineNo);				
				updCnt = pstmtUpd.executeUpdate();
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			if (retString != null && retString.trim().length() > 0 )
			{
				isError = true;
				return retString;
			}

			updateSql = "UPDATE PICK_ISS_HDR SET TRF_STATUS = 'Y' "
				+ " WHERE TRAN_ID = ?  ";
		
			pstmtUpd = conn.prepareStatement(updateSql);
			pstmtUpd.setString(1, pickisstranId);
			updCnt = pstmtUpd.executeUpdate();
		
			pstmtUpd.close();
			pstmtUpd = null;
			
			//Changed by Sankara on 03/oct/13 adding validation to prevent doc transfer in case of pending/aborted picking.start
			int countConf = 0;
			sql =  " SELECT COUNT(*) FROM PICK_ISS_HDR PIH, PICK_ISS_DET PH WHERE " 
					+" PIH.PICK_ORDER = PH.PICK_ORDER AND PH.TRAN_ID = PIH.TRAN_ID AND PIH.CONFIRMED = ? "
					+" AND PIH.PICK_ORDER = ? AND PICK_TYPE IN ('A','M') AND   PIH.TRF_STATUS IS NULL ";
			pstmt = conn.prepareStatement( sql );
			pstmt.setString( 1, "Y");
			pstmt.setString( 2, pickOrder);
			
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				countConf = rs.getInt(1);
				System.out.println(" Pending Transfer Count["+countConf);
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			
			if( countConf > 0)
			{
				return errString = "";
			}
			//Changed by Sankara on 03/oct/13 adding validation to prevent doc transfer in case of pending/aborted picking.start
			
			//update the status of pick order in wave_task_det table
			//Changed by sumit on 10/08/12 update wave_status 'V' with updating status = 'Y' 
			//updateSql = "UPDATE WAVE_TASK_DET SET STATUS = 'Y' WHERE REF_ID = ? ";
			updateSql = "UPDATE WAVE_TASK_DET SET STATUS = 'Y', WAVE_STATUS = 'V' WHERE REF_ID = ? ";
			pstmt = conn.prepareStatement(updateSql);
			pstmt.setString(1, pickOrder);
			updCnt = pstmt.executeUpdate();
			
			if( updCnt > 0 )
			{					
				System.out.println( updCnt + " rows updated successfully" );
			}	
			pstmt.close();pstmt = null;
			
			
			//Changed by sumit on 30/08/12 for updating wave_status start.
			//Changed by sumit on 21/01/13 for updating wave_status for M-PACK task start
			System.out.println(" ** pick_type ["+pickType+"]");
			if( "M".equalsIgnoreCase(pickType))
			{
				updateSql =" UPDATE WAVE_TASK_DET SET STATUS = 'Y', WAVE_STATUS = 'V' " +
						" WHERE REF_ID IN (SELECT TRAN_ID FROM PACK_HDR WHERE PICK_ORDER = ?)";
				pstmt = conn.prepareStatement(updateSql);
				pstmt.setString(1, pickOrder);
				updCnt = pstmt.executeUpdate();				
				if( updCnt > 0 )
				{					
					System.out.println( updCnt + " rows updated successfully" );
				}	
				pstmt.close();pstmt = null;
			}
			//Changed by sumit on 21/01/13 for updating wave_status for M-PACK task end
			updCnt= 0;
			updCnt = waveStatusUpdate(pickOrder, conn);
			if( updCnt > 0)
			{
				System.out.println( updCnt + " row of wave_status updated successfully" );
			}
			
			
			//Changed by sumit on 30/08/12 for updating wave_status end.
	
			sql = "select ptcn from wave_task_det where ref_id = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,pickOrder);
			rs = pstmt.executeQuery();
			if( rs.next() )
			{
				ptcn = rs.getString("ptcn") == null?"":rs.getString("ptcn");
			}
			if( rs != null)
			{
				rs.close();
				rs = null;
			}
			if(pstmt != null)
			{
				pstmt.close();
				pstmt = null;
			}
			
			//Changed By 02/05/13 Pragyan To Close The Connections.start
			if( rs1 != null)
			{
				rs1.close();
				rs1 = null;
			}
			if( rsSord != null)
			{
				rsSord.close();
				rsSord = null;
			}
			
			if(pstmt1 != null)
			{
				pstmt1.close();
				pstmt1 = null;
			}
			
			if(pstmtSord != null)
			{
				pstmtSord.close();
				pstmtSord = null;
			}
			
			//Changed By 02/05/13 Pragyan Close The Connections.end

			ConsolidatToDoc conToDoc = new ConsolidatToDoc();
			//Changed by Rohan on 10-06-12 to add xtraParams in process as argument for hazmap report to get chg user
			//retString = conToDoc.process(ptcn, conn);
			retString = conToDoc.process(ptcn, conn,xtraParams);
	

			System.out.println("pick type from pick_ord_hdr table--->>["+pickType+"]");
		}
		catch(Exception e)
		{
			System.out.println("Exception before rollback "+e.getMessage());
			try
			{
				
				conn.rollback();
			}
			catch (SQLException e1) 
			{
				
				e1.printStackTrace();
			}
						
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if( !isError  )
				{
					System.out.println("commmit");
					conn.commit(); 
				}
				else if ( isError )
				{
					System.out.println("rollback");
					conn.rollback();
				}
				if( rs != null )
				{
					rs.close();
					rs = null;
				}
				if( pstmt != null )
				{
					pstmt.close();
					pstmt = null;
				}
				
				//Changed By 02/05/13 Pragyan To Close The Connections.start
				if( rs1 != null)
				{
					rs1.close();
					rs1 = null;
				}
				if( rsSord != null)
				{
					rsSord.close();
					rsSord = null;
				}
				
				if(pstmt1 != null)
				{
					pstmt1.close();
					pstmt1 = null;
				}
				
				if(pstmtSord != null)
				{
					pstmtSord.close();
					pstmtSord = null;
				}
				
				//Changed By 02/05/13 Pragyan Close The Connections.end
			}
			catch(Exception e)
			{
				System.err.println("Exception :PickIssPos :\n"+e.getMessage());
				e.printStackTrace();
				throw new ITMException(e);
			}
		}
		return errString;
	}
	
	//Changed by sumit on 30/08/12 update wave_status after all task completed for wave_id and sale order start.
	private int waveStatusUpdate(String replOrder, Connection conn) throws ITMException
	{
		String sql = "", sql1 = "";
		String waveId = "";
		String saleOrder = "";
		PreparedStatement pstmt = null, pstmt1 = null;
		ResultSet rs = null, rs1 = null;
		int updateCount = 0;
		int firstCount = 0, secCount = 0;
		
		
		try
		{
			sql = "SELECT WAVE_ID, SALE_ORDER FROM WAVE_TASK_DET WHERE REF_ID = ? ";			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, replOrder);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				waveId = rs.getString("WAVE_ID");
				saleOrder = rs.getString("SALE_ORDER");	
				
				sql1 = "SELECT COUNT(*) AS CONT FROM WAVE_TASK_DET WHERE WAVE_ID = ? AND SALE_ORDER = ? AND REF_SER <> 'S-DSP' ";
				pstmt1 = conn.prepareStatement(sql1);
				pstmt1.setString(1, waveId);
			    pstmt1.setString(2, saleOrder);
			    rs1 = pstmt1.executeQuery();
			    if(rs1.next())
			    {
			    	firstCount = rs1.getInt("CONT");
			    }
			    rs1.close();rs1 = null;
			    pstmt1.close();pstmt1 = null;
				
			    //Changed by sumit on 19/03/13 changing condition check it from status flag
			    //sql1 = "SELECT COUNT(*) AS CONT FROM WAVE_TASK_DET WHERE WAVE_STATUS = 'V' AND WAVE_ID = ? AND SALE_ORDER = ? AND REF_SER <> 'S-DSP' ";
			    sql1 = "SELECT COUNT(*) AS CONT FROM WAVE_TASK_DET WHERE STATUS = 'Y' AND WAVE_ID = ? AND SALE_ORDER = ? AND REF_SER <> 'S-DSP' ";
				pstmt1 = conn.prepareStatement(sql1);
				pstmt1.setString(1, waveId);
			    pstmt1.setString(2, saleOrder);
			    rs1 = pstmt1.executeQuery();
			    if(rs1.next())
			    {
			    	secCount = rs1.getInt("CONT");
			    }
			    rs1.close();rs1 = null;
			    pstmt1.close();pstmt1 = null;
				
				
			}
			rs.close();rs = null;
			pstmt.close();pstmt = null;
			
			if( secCount == firstCount)
			{
				sql = "UPDATE WAVE_TASK_DET SET WAVE_STATUS = 'C' WHERE WAVE_ID = ? AND SALE_ORDER = ? AND REF_SER = 'S-DSP' ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, waveId);
				pstmt.setString(2, saleOrder);
				updateCount = pstmt.executeUpdate();					
				pstmt.close();pstmt = null;
			}
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		return updateCount;
	}
	//Changed by sumit on 30/08/12 update wave_status after all task completed for wave_id and sale order end.
	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
		{
			try
			{
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;
			
				}
				if(rs != null)
				{
					rs.close();
					rs = null;
			
				}
			}
			catch (Exception e) {
				// TODO: handle exception
	
				throw e;
			}
		}
		
		return dataVolumeMap;
	}
}

