/*
    Window Name : w_sordalloc
 */

package ibase.webitm.ejb.dis;

import java.rmi.RemoteException;
import java.util.*;
import java.util.Date;
import java.sql.*;
import java.io.*;
//import webitm4.*;
//import SessionManager.*;
import org.omg.CORBA.ORB;
import org.w3c.dom.*;
import java.util.Properties;
import javax.xml.parsers.*;
import javax.ejb.*;
import javax.naming.InitialContext;

import ibase.webitm.utility.ITMException;
import ibase.webitm.ejb.*;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.TransIDGenerator;

import ibase.utility.CommonConstants;
import ibase.ejb.*;
import ibase.system.config.*;
import java.text.SimpleDateFormat;
import java.util.*;
import javax.ejb.Stateless; // added for ejb3
@Stateless // added for ejb3

//public class StockAllocationPrcEJB extends ProcessEJB implements SessionBean
public class StockAllocationPrc extends ProcessEJB implements StockAllocationPrcLocal,StockAllocationPrcRemote //SessionBean
{
	String siteCode = null;
	String itemSerFr = null;
	String itemSerTo = null;
	String custCodeFr = null;
	String custCodeTo = null;
	String custCode = null;
	String DefaultQtyFlag= null;
	String itemCodeFr = null;
	String itemCodeTo = null;

	String saleOrderFr = null;
	String saleOrderTo = null;
	String postOrderFg = null;
	String 	sql = null;
	String sDateTo = null;
	String sDateFr = null;
	double balStockQty = 0;
	double holdQty = 0;

	java.sql.Timestamp dateTo =  null;
	java.sql.Timestamp dueDate =  null;
	java.sql.Timestamp dateFr =  null;
	HashMap itemCodeMap = new HashMap();
	ArrayList saleOrderArr = new ArrayList();
	ArrayList custCodeArr = new ArrayList();
	ArrayList dueDateArr = new ArrayList();

	InvAllocTrace invallocTrace = new InvAllocTrace();
	GenericUtility genericUtility = GenericUtility.getInstance();
	ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
	//ConnDriver connDriver = new ConnDriver();
	CommonConstants commonConstants = new CommonConstants();
	String chgUser = null;
	String chgTerm = null;

	/*public void ejbCreate() throws RemoteException, CreateException
	{
		try
		{
			System.out.println("StockAllocationPrcEJB ejbCreate called.........");

		}
		catch (Exception e)
		{
			System.out.println("Exception :StockAllocationPrcEJB :ejbCreate :==>"+e);
			throw new CreateException();
		}
	}
	public void ejbRemove()
	{
	}
	public void ejbActivate() 
	{
	}
	public void ejbPassivate() 
	{
	}
	 */
	public String process() throws RemoteException,ITMException
	{
		return "";
	}

	//getData Method
	public String getData(String xmlString, String xmlString2, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		String rtrStr = "";
		Document headerDom = null;
		Document detailDom = null;
		System.out.println("getData(xmlString,xmlString2,windowName,xtraParams CALLED..."+ xmlString);
		try
		{
			if(xmlString != null && xmlString.trim().length()!=0)
			{
				System.out.println("XML String :"+xmlString);
				headerDom = genericUtility.parseString(xmlString); 				
			}
			if(xmlString2 != null && xmlString2.trim().length()!=0)
			{
				System.out.println("XML String :"+xmlString2);
				detailDom = genericUtility.parseString(xmlString2); 				
			}
			rtrStr = getData(headerDom, detailDom, windowName, xtraParams);
		}
		catch (Exception e)
		{
			System.out.println("Exception :StockAllocationPrcEJB :getData(String xmlString, String xmlString2, String windowName, String xtraParams):" + e.getMessage() + ":");
			rtrStr = e.getMessage();
		}
		return rtrStr; 
	}

	public String getData(Document headerDom, Document detailDom, String windowName, String xtraParams) throws RemoteException,ITMException
	{

		String errCode = "";
		String errString = "";
		String getDataSql= "" ;
		String sql= "" ;
		String resultString = "";
		String sql1 ="",custCodeSord = "",waveType = "",	activeAllow="";
		String resrvLoc = "",casePickLoc = "",activePickLoc = "" ,deepStoreLoc = "" ,partialResrvLoc = "";//added by kunal on 15/NOV/13
		String orderByStkStr = "" , disOpt = "";
		int waveType1 = 0;
		double pendingQty = 0, minSelfLife = 0;
		Connection conn = null;
		ResultSet rs = null;
		ResultSet rs1 = null;
		ResultSet rs2 = null;
		PreparedStatement pstmt = null,pstmt1 = null,pstmt2 = null ;
		Statement st = null;
		StringBuffer retTabSepStrBuff = new StringBuffer();
		double pendQty = 0;
		double allocQty = 0;	
		boolean bappend = false ;
		boolean checkLocation = false;
		boolean isActives = false;
		SimpleDateFormat sdf=null;
		DistCommon dComm = new DistCommon();
		retTabSepStrBuff = new StringBuffer("<?xml version = \"1.0\"?>");
		retTabSepStrBuff.append("<DocumentRoot>");
		retTabSepStrBuff.append("<description>").append("Datawindow Root").append("</description>");
		retTabSepStrBuff.append("<group0>");
		retTabSepStrBuff.append("<description>").append("Group0 description").append("</description>");
		retTabSepStrBuff.append("<Header0>");

		try
		{
			sdf=new SimpleDateFormat(genericUtility.getApplDateFormat());
			ConnDriver connDriver = new ConnDriver();
			SimpleDateFormat simpleDateFormat = new SimpleDateFormat(genericUtility.getDBDateFormat());
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			//added by kunal on 15/NOV/13 DI3ESUN009
			resrvLoc  = checkNull(dComm.getDisparams("999999","RESERV_LOCATION",conn));
			casePickLoc  = checkNull(dComm.getDisparams("999999","CASE_PICK_INVSTAT",conn));
			activePickLoc  = checkNull(dComm.getDisparams("999999","ACTIVE_PICK_INVSTAT",conn));
			deepStoreLoc = checkNull(dComm.getDisparams("999999","DEEP_STORE_INVSTAT",conn));
			partialResrvLoc = checkNull(dComm.getDisparams("999999","PRSRV_INVSTAT",conn));	
			if(resrvLoc.trim().length() == 0 || resrvLoc.trim().equalsIgnoreCase("NULLFOUND"))
			{
				resrvLoc = "";
			}
			if(casePickLoc.trim().length() == 0 || casePickLoc.trim().equalsIgnoreCase("NULLFOUND"))
			{
				casePickLoc = "";
			}
			if(activePickLoc.trim().length() == 0 || activePickLoc.trim().equalsIgnoreCase("NULLFOUND"))
			{
				activePickLoc = "";
			}
			if(deepStoreLoc.trim().length() == 0 || deepStoreLoc.trim().equalsIgnoreCase("NULLFOUND"))
			{
				deepStoreLoc = "";
			}
			if(partialResrvLoc.trim().length() == 0 || partialResrvLoc.trim().equalsIgnoreCase("NULLFOUND"))
			{
				partialResrvLoc = "";
			}

			System.out.println("CASE_PICK_INVSTAT="+casePickLoc+"     ACTIVE_PICK_INVSTAT="+activePickLoc+"       RESERV_LOCATION="+resrvLoc+"     DEEP_STORE_INVSTAT="+deepStoreLoc+"     PARTIAL RESERVE LOC="+partialResrvLoc);
			if(resrvLoc.trim().length() > 0 || casePickLoc.trim().length() > 0 || activePickLoc.trim().length() > 0 || deepStoreLoc.trim().length() > 0  || partialResrvLoc.trim().length() > 0)
			{
				checkLocation = true;
			}
			System.out.println("checkLocation="+checkLocation);
			//added by kunal on 15/NOV/13 DI3ESUN009 end

			siteCode = genericUtility.getColumnValue("site_code",headerDom);
			if ( siteCode == null || siteCode.trim().length() == 0 )
			{
				siteCode = "";
				System.out.println("Site Code From is Null...");
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			} 
			System.out.println("SITE CODE "+ siteCode);
			postOrderFg = genericUtility.getColumnValue("post_order_flag",headerDom);
			if(postOrderFg == null || postOrderFg.trim().length() == 0)
			{
				postOrderFg = "";
				System.out.println("Site Code From is Null...");
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			itemSerFr = genericUtility.getColumnValue("item_ser__from",headerDom);
			if ( itemSerFr == null || itemSerFr.trim().length() == 0 )
			{
				itemSerFr = "";
				System.out.println("Item Series From is Null...");
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			System.out.println("Item Ser FR "+ itemSerFr);
			itemSerTo = genericUtility.getColumnValue("item_ser__to",headerDom);
			if ( itemSerTo == null || itemSerTo.trim().length() == 0 )
			{
				itemSerTo = "";
				System.out.println("Item Series To is Null...");
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			itemCodeFr = genericUtility.getColumnValue("item_code__from",headerDom);
			if ( itemCodeFr == null || itemCodeFr.trim().length() == 0 )
			{
				itemCodeFr = "";
				System.out.println("Item Code From is Null...");
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			System.out.println("Item Code FR "+ itemCodeFr);
			itemCodeTo = genericUtility.getColumnValue("item_code__to",headerDom);
			if ( itemCodeTo == null || itemCodeTo.trim().length() == 0 )
			{
				itemCodeTo = "";
				System.out.println("Item Code To is Null...");
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			System.out.println("Item Ser To"+itemSerTo);
			custCodeFr = genericUtility.getColumnValue("cust_code__from",headerDom);
			if ( custCodeFr == null || custCodeFr.trim().length() == 0 )
			{
				custCodeFr = "";
				System.out.println("Cust Code From is Null...");
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			System.out.println("custCodeFr"+ custCodeFr);
			custCodeTo = genericUtility.getColumnValue("cust_code__to",headerDom); 
			if ( custCodeTo == null || custCodeTo.trim().length() == 0 )
			{
				custCodeTo = "";
				System.out.println("Cust Code To is Null...");
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}  
			System.out.println("custCodeTo"+ custCodeTo);      
			saleOrderFr = genericUtility.getColumnValue("sale_order__from",headerDom);
			if ( saleOrderFr == null || saleOrderFr.trim().length() == 0 )
			{
				saleOrderFr = "";
				System.out.println("Sale Order From is Null...");
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}

			saleOrderTo = genericUtility.getColumnValue("sale_order__to",headerDom);         
			if ( saleOrderTo == null || saleOrderTo.trim().length() == 0 )
			{
				saleOrderTo = "";
				System.out.println("Sale Order To is Null...");
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			sDateFr = genericUtility.getColumnValue("due_date__from",headerDom);
			if ( sDateFr == null || sDateFr.trim().length() == 0 )
			{
				sDateFr = "";
				System.out.println("Date From is Null...");
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			sDateFr = genericUtility.getValidDateString(sDateFr, genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat());
			dateFr = java.sql.Timestamp.valueOf(sDateFr + " 00:00:00");

			sDateTo = genericUtility.getColumnValue("due_date__to",headerDom);            
			if ( sDateTo == null || sDateTo.trim().length() == 0 )
			{
				sDateTo = "";
				System.out.println("Date To is Null...");
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			sDateTo = genericUtility.getValidDateString(sDateTo, genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat());
			dateTo = java.sql.Timestamp.valueOf(sDateTo + " 00:00:00");

			DefaultQtyFlag = genericUtility.getColumnValue("default_qty_flag",headerDom);
			if(DefaultQtyFlag == null || DefaultQtyFlag.trim().length() == 0)
			{
				DefaultQtyFlag = "";
				System.out.println("Default Qty flag is Null...");
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			//added by Kunal on 18/NOV/13
			disOpt = checkNull( genericUtility.getColumnValue("dis_opt",headerDom));
			System.out.println("Display option="+disOpt);

			getDataSql = "SELECT SORDER.CUST_CODE, CUSTOMER.CUST_NAME,SORDDET.LINE_NO, "
					+"SORDER.SALE_ORDER,SORDER.DUE_DATE,SORDITEM.ITEM_CODE,"
					+"ITEM.DESCR,SORDITEM.QUANTITY,"
					//Changed by Manoj dtd 18/06/2013 to get Pending Quantity in Std Unit
					//+"(SORDITEM.QUANTITY*SORDITEM.CONV__QTY_STDUOM) - SORDITEM.QTY_DESP - SORDITEM.QTY_ALLOC PENDING_QUANTITY,"
					//Changed by Manoj dtd 12/11/2013 to get Pending Quantity in Std Unit
					+"(SORDITEM.QUANTITY*SORDITEM.CONV__QTY_STDQTY) - (SORDITEM.QTY_DESP*SORDITEM.CONV__QTY_STDQTY) - SORDITEM.QTY_ALLOC PENDING_QUANTITY,"
					+"SORDITEM.QTY_ALLOC,SORDDET.PACK_INSTR,"
					+"SORDER.SITE_CODE__SHIP,SORDITEM.EXP_LEV , "
					+"CASE WHEN SORDDET.HOLD_FLAG IS NULL THEN 'N' ELSE SORDDET.HOLD_FLAG END, "// ADDED BY AKHILESH FOR NEW COLUNM
					+" ITEM.GTIN_CASE,ITEM.GTIN_UNIT  "   //added by Kunal on 22/10/12
					+",SORDDET.UNIT,SORDDET.UNIT__STD,SORDDET.CONV__QTY_STDUOM,SORDDET.QUANTITY__STDUOM , "
					+" SORDER.PROM_DATE,SORDER.CUST_CODE__DLV,CUSTA.CUST_NAME AS CUST_CODE__DLV_NAME,SORDER.STATE_CODE__DLV, CUSTOMER.GROUP_CODE,CUSTB.CUST_NAME AS PCUST_NAME, " //added by kunal on 02/07/13
					+" SORDER.CUST_PORD,SORDER.PORD_DATE " //added by kunal on 02/07/13
					+"FROM SORDDET,SORDER,SORDITEM,CUSTOMER,ITEM ,customer custa,customer custb " 
					+"WHERE ( SORDER.SALE_ORDER = SORDDET.SALE_ORDER ) AND "
					+"( SORDITEM.SALE_ORDER = SORDER.SALE_ORDER ) AND "
					+"( SORDDET.LINE_NO = SORDITEM.LINE_NO ) AND "
					+"( SORDITEM.ITEM_CODE = ITEM.ITEM_CODE ) AND "
					+"( SORDER.CUST_CODE = CUSTOMER.CUST_CODE )  "
					+" and ( sorder.cust_code__dlv = custa.cust_code ) " 
					+" and ( customer.group_code = custb.cust_code ) " 

				+"AND ( SORDDET.SITE_CODE = SORDITEM.SITE_CODE ) AND  "

				+" SORDER.SITE_CODE = ? AND "
				+" ITEM.ITEM_SER >=? AND "
				+" ITEM.ITEM_SER <=? AND "  
				+" CUSTOMER.CUST_CODE >=? AND "
				+" CUSTOMER.CUST_CODE <=? AND "  
				+" SORDER.SALE_ORDER >= ? AND "
				+" SORDER.SALE_ORDER <= ? AND " 
				+" SORDER.DUE_DATE >=  ?  AND" 
				+" SORDER.DUE_DATE <= ?  AND "
				+" SORDITEM.ITEM_CODE >= ? AND "
				+" SORDITEM.ITEM_CODE <= ?  " 
				+"AND CASE WHEN SORDDET.STATUS IS NULL THEN 'P' ELSE SORDDET.STATUS end <> 'C' "
				+"AND CASE WHEN SORDER.STATUS IS NULL THEN 'P' ELSE SORDER.STATUS end  IN ('P','H') "  //change done by Kunal on 22/10/12 as per S Manoharan instruction ,consider Hold status  
				+"AND (SORDITEM.QUANTITY*SORDITEM.CONV__QTY_STDQTY) - (SORDITEM.QTY_DESP*SORDITEM.CONV__QTY_STDQTY) - SORDITEM.QTY_ALLOC > 0 "
				+"AND SORDITEM.LINE_TYPE = 'I' "
				+"ORDER BY SORDER.CUST_CODE,SORDER.SALE_ORDER,SORDITEM.ITEM_CODE__ORD"; 

			pstmt = conn.prepareStatement(getDataSql);

			pstmt.setString(1,siteCode);
			pstmt.setString(2,itemSerFr);
			pstmt.setString(3,itemSerTo);
			pstmt.setString(4,custCodeFr);
			pstmt.setString(5,custCodeTo);
			pstmt.setString(6,saleOrderFr);
			pstmt.setString(7,saleOrderTo);
			pstmt.setTimestamp(8,dateFr);
			pstmt.setTimestamp(9,dateTo);
			pstmt.setString(10,itemCodeFr);
			pstmt.setString(11,itemCodeTo);
			st = conn.createStatement();
			rs = pstmt.executeQuery();

			if(rs.next())
			{

				do
				{

					System.out.println("Processing Item ....." + rs.getString(6) );
					//added by ritesh 0n 7/8/13  for request DI3ESUN009 START
					sql1 = " select wave_type from customer where cust_code = ? ";
					pstmt2 = conn.prepareStatement(sql1);
					pstmt2.setString(1,rs.getString("CUST_CODE__DLV"));
					rs2 = pstmt2.executeQuery();
					if(rs2.next())
					{
						waveType = rs2.getString("wave_type");
					}
					rs2.close();
					rs2 = null;
					pstmt2.close();
					pstmt2 = null;
					sql1 = " select case when ACTIVE_PICK_ALLOW is null then 'Y' else ACTIVE_PICK_ALLOW end as ACTIVE_PICK_ALLOW from wave_type where wave_type = ? ";
					pstmt2 = conn.prepareStatement(sql1);
					pstmt2.setString(1,waveType);
					rs2 = pstmt2.executeQuery();
					if(rs2.next())
					{
						activeAllow = rs2.getString("ACTIVE_PICK_ALLOW");
					}
					else
					{
						activeAllow = "Y";
					}
					rs2.close();
					rs2 = null;
					pstmt2.close();
					pstmt2 = null;	
					//added by ritesh 0n 7/8/13  for request DI3ESUN009 END

					if(itemCodeMap.containsKey(rs.getString(6)) )
					{						
						balStockQty = Double.parseDouble(itemCodeMap.get(rs.getString(6)).toString());
						bappend = false ;

					}
					else
					{

						if(checkLocation)
						{

							sql =" SELECT SORDER.CUST_CODE, CUSTOMER.CUST_NAME,SORDDET.LINE_NO, "
									+"SORDER.SALE_ORDER,SORDER.DUE_DATE,SORDITEM.ITEM_CODE,"
									+"ITEM.DESCR,SORDITEM.QUANTITY,"
									+"((SORDITEM.QUANTITY*SORDITEM.CONV__QTY_STDQTY) - (SORDITEM.QTY_DESP*SORDITEM.CONV__QTY_STDQTY) - SORDITEM.QTY_ALLOC) PENDING_QUANTITY,"
									+"SORDITEM.QTY_ALLOC,SORDDET.PACK_INSTR,"
									+"SORDER.SITE_CODE,SORDITEM.EXP_LEV, "
									+"SORDER.PART_QTY AS PART_QTY, CUSTOMER.SINGLE_LOT AS SINGLE_LOT, SORDITEM.MIN_SHELF_LIFE AS MIN_SHELF_LIFE "//Gulzar 5/13/2012
									+" ,SORDER.ALLOC_FLAG AS ALLOC_FLAG , WAVE_TYPE.MASTER_PACK_ALLOW , WAVE_TYPE.ACTIVE_PICK_ALLOW , WAVE_TYPE.STOCK_TO_DOCK_ALLOW "		   
									+"FROM SORDDET,SORDER,SORDITEM,CUSTOMER,ITEM , WAVE_TYPE "
									//Changed by sumit 20/08/12 getting column data end.
									+"WHERE ( SORDER.SALE_ORDER = SORDDET.SALE_ORDER ) AND "
									+"( SORDITEM.SALE_ORDER = SORDER.SALE_ORDER ) AND "
									+"( SORDDET.LINE_NO = SORDITEM.LINE_NO ) AND "
									+"( SORDITEM.ITEM_CODE = ITEM.ITEM_CODE ) AND "
									+"( SORDER.CUST_CODE = CUSTOMER.CUST_CODE ) AND  "
									+"( SORDDET.SITE_CODE = SORDITEM.SITE_CODE ) AND  "
									//Changed by sumit on 20/08/12 join customer with wave_type
									+"( CUSTOMER.WAVE_TYPE = WAVE_TYPE.WAVE_TYPE(+)) AND "
									//+" SORDER.SITE_CODE = ? AND "							   
									+" SORDER.SALE_ORDER = ? "
									+" AND SORDITEM.LINE_NO = ? "
									+"AND CASE WHEN SORDDET.STATUS IS NULL THEN 'P' ELSE SORDDET.STATUS end <> 'C' "
									+"AND CASE WHEN SORDER.STATUS IS NULL THEN 'P' ELSE SORDER.STATUS end = 'P' "
									//Changed by sumit on 12/09/12 considering hold_flag from sorddet 
									+" AND CASE WHEN SORDDET.HOLD_FLAG IS NULL THEN 'N' ELSE SORDDET.HOLD_FLAG end <> 'Y'"
									//Chnaged by Rohan on 11/07/12 revert Changes of Manual Stock Allocation
									//Changed by Rohan on 22/06/12
									//+"AND SORDITEM.QUANTITY - SORDITEM.QTY_DESP - SORDITEM.QTY_ALLOC > 0 "
									//+"AND (SORDITEM.QUANTITY - SORDITEM.QTY_DESP - SORDITEM.QTY_ALLOC > 0 OR SORDER.ALLOC_FLAG='M' )"
									//Changed by sumit on 13/08/12 as per manual stock allocation start.
									//+"AND SORDITEM.QUANTITY - SORDITEM.QTY_DESP - SORDITEM.QTY_ALLOC > 0 "
									+" AND (((SORDITEM.QUANTITY*SORDITEM.CONV__QTY_STDQTY) - (SORDITEM.QTY_DESP*SORDITEM.CONV__QTY_STDQTY) - SORDITEM.QTY_ALLOC > 0 ) "  
									+" OR (FN_CHECK_MANUAL_STOCK_ALLOC(SORDER.SALE_ORDER, SORDDET.LINE_NO) = 0 ) )"	 
									+"AND SORDITEM.LINE_TYPE = 'I'";
							pstmt1 = conn.prepareStatement(sql);
							pstmt1.setString(1,rs.getString(4));
							pstmt1.setString(2, rs.getString(3));
							rs1 = pstmt1.executeQuery();
							if(rs1.next())
							{
								pendingQty = rs1.getInt("PENDING_QUANTITY");
							}
							rs1.close();
							rs1 = null;
							pstmt1.close();
							pstmt1 = null;

							HashMap itemVolMap = getItemVoumeMap( rs.getString(6), "", conn);
							double packSize = (Double)itemVolMap.get("PACK_SIZE");
							System.out.println(" pendingQty = "+pendingQty+"  packSize ="+packSize);
							if((pendingQty % packSize) > 0)
							{
								isActives = true;
								orderByStkStr = " AND LOCATION.INV_STAT IN(?,?,?,?,?) ";
							}
							else
							{
								isActives = false;
								orderByStkStr = " AND LOCATION.INV_STAT IN(?,?,?,?) ";
							}
						}
						minSelfLife = 0;
						sql =" SELECT MIN_SHELF_LIFE  FROM SORDITEM WHERE SALE_ORDER = ?  AND LINE_NO = ?  AND EXP_LEV = ? "; 
						pstmt1 = conn.prepareStatement(sql);
						pstmt1.setString(1,rs.getString(4));
						pstmt1.setString(2, rs.getString(3));
						pstmt1.setString(3, rs.getString(13));
						rs1 = pstmt1.executeQuery();
						if(rs1.next())
						{
							minSelfLife= rs1.getDouble("MIN_SHELF_LIFE");
						}
						rs1.close();
						rs1 = null;
						pstmt1.close();
						pstmt1 = null;

						sql1 ="SELECT SUM(STOCK.QUANTITY - case when STOCK.ALLOC_QTY is null then 0 else STOCK.ALLOC_QTY end - case when STOCK.HOLD_QTY is null then 0 else STOCK.HOLD_QTY end  ),SUM(case when STOCK.HOLD_QTY is null then 0 else STOCK.HOLD_QTY end ) " 
								+"FROM STOCK,ITEM,LOCATION,INVSTAT " 
								+"WHERE (ITEM.ITEM_CODE = STOCK.ITEM_CODE) "
								+"AND (LOCATION.LOC_CODE = STOCK.LOC_CODE ) "
								+"AND (LOCATION.INV_STAT = INVSTAT.INV_STAT) "
								+"AND INVSTAT.AVAILABLE = 'Y' "
								+"AND STOCK.ITEM_CODE = ? "
								+"AND STOCK.SITE_CODE = ? " 
								+"AND (STOCK.QUANTITY - case when STOCK.ALLOC_QTY is null then 0 else STOCK.ALLOC_QTY end - case when STOCK.HOLD_QTY is null then 0 else STOCK.HOLD_QTY end ) > 0   "
								+"AND NOT EXISTS (SELECT 1 FROM INV_RESTR I WHERE I.INV_STAT = INVSTAT.INV_STAT AND I.REF_SER = 'S-DSP' ) "; //added by kunal on 20/NOV/13 SAME COND. FOR GETDATA AND PROCESS 

						if("N".equalsIgnoreCase(activeAllow))//added by kunal on 20/NOV/13 SAME COND. FOR GETDATA AND PROCESS
						{
							sql1 = sql1.concat(" AND LOCATION.INV_STAT NOT IN(?,?) ");
						}
						//Added by manoj dtd 09/08/2013 to implement FEFO concept  
						sql1= sql1+" AND (MONTHS_BETWEEN(TO_DATE(STOCK.EXP_DATE),SYSDATE) > ? ) ";


						// 01/11/12 manoharan preparedstatement used instead of statement
						//rs1 = st.executeQuery(sql);

						if(checkLocation) //added by kunal on 15/NOV/13  DI3ESUN009 
						{
							sql1=sql1+orderByStkStr;
						}
						pstmt1 = conn.prepareStatement(sql1);
						pstmt1.setString(1,rs.getString(6) );
						pstmt1.setString(2,rs.getString(12));
						pstmt1.setDouble(3,minSelfLife);
						if("N".equalsIgnoreCase(activeAllow))
						{
							pstmt1.setString(3,activePickLoc);
							pstmt1.setString(4,partialResrvLoc);
							pstmt1.setDouble(5,minSelfLife);
							if(checkLocation) //added by kunal on 15/NOV/13  DI3ESUN009
							{
								pstmt1.setString(6,resrvLoc);
								pstmt1.setString(7,casePickLoc);
								pstmt1.setString(8,activePickLoc);
								pstmt1.setString(9,deepStoreLoc);
								if(isActives)
								{
									pstmt1.setString(10,partialResrvLoc);
								}
							}
						}
						else
						{
							if(checkLocation) //added by kunal on 15/NOV/13  DI3ESUN009
							{
								pstmt1.setString(4,resrvLoc);
								pstmt1.setString(5,casePickLoc);
								pstmt1.setString(6,activePickLoc);
								pstmt1.setString(7,deepStoreLoc);
								if(isActives)
								{
									pstmt1.setString(8,partialResrvLoc);
								}
							}

						}
						rs1 = pstmt1.executeQuery();

						if (rs1.next())
						{
							bappend = true;
							balStockQty = rs1.getDouble(1);
							holdQty = rs1.getDouble(2); //added by Kunal on 22/10/12 
							if (balStockQty > 0)
							{
								itemCodeMap.put(rs.getString(6),new Double(rs1.getDouble(1)));
							}
							else
							{
								if ("A".equalsIgnoreCase(disOpt))// To display all records, added by Kunal on 18/NOV/13
								{
									itemCodeMap.put(rs.getString(6),new Double(rs1.getDouble(1)));
								}
							}
							System.out.println("Bal stk qty for item ....." + rs.getString(6) + " is " + balStockQty+"  hold qty="+holdQty);
						}
						rs1.close();
						rs1 = null;
						pstmt1.close();
						pstmt1 = null;
					}

					//if (balStockQty > 0) 
					if(itemCodeMap.containsKey(rs.getString(6)) )
					{

						retTabSepStrBuff.append("<Detail2>");

						//DUE_DATE
						//retTabSepStrBuff.append(rs.getTimestamp(5)).append("\t");
						if(rs.getTimestamp(5) != null)
						{
							retTabSepStrBuff.append("<due_date>").append("<![CDATA[" + sdf.format(rs.getTimestamp(5)) +"]]>").append("</due_date>");
						}
						else
						{
							retTabSepStrBuff.append("<due_date>").append("<![CDATA[]]>").append("</due_date>");
						}
						dueDate = rs.getTimestamp(5);

						//PROM_DATE
						if(rs.getTimestamp("PROM_DATE") != null)
						{
							retTabSepStrBuff.append("<prom_date>").append("<![CDATA[" + sdf.format(rs.getTimestamp("PROM_DATE")) +"]]>").append("</prom_date>");
						}
						else
						{
							retTabSepStrBuff.append("<prom_date>").append("<![CDATA[]]>").append("</prom_date>");
						}

						//DLV CUST_CODE
						retTabSepStrBuff.append("<cust_code__dlv>").append("<![CDATA[" + checkNull(rs.getString("CUST_CODE__DLV")) +"]]>").append("</cust_code__dlv>");
						//DLV CUST_CODE NAME
						retTabSepStrBuff.append("<cust_name>").append("<![CDATA[" + checkNull(rs.getString("CUST_CODE__DLV_NAME")) +"]]>").append("</cust_name>");
						//DLV CUST_CODE STATE 
						retTabSepStrBuff.append("<state_code__dlv>").append("<![CDATA[" + checkNull(rs.getString("STATE_CODE__DLV")) +"]]>").append("</state_code__dlv>");

						//SALE_ORDER
						//retTabSepStrBuff.append(rs.getString(4)).append("\t");
						retTabSepStrBuff.append("<sale_order>").append("<![CDATA[" + rs.getString(4) +"]]>").append("</sale_order>");

						//LINE_NO
						//retTabSepStrBuff.append(rs.getString(3)).append("\t");
						retTabSepStrBuff.append("<line_no>").append("<![CDATA[" + rs.getString(3) +"]]>").append("</line_no>");

						//change done by kunal on 27/NOV/13 ,change the display order 
						//Hold Flag
						//retTabSepStrBuff.append(rs.getString(14) == null?"N":rs.getString(14)).append("\t");// ADDED BY AKHILESH
						retTabSepStrBuff.append("<hold_flag>").append("<![CDATA[" + (rs.getString(14) == null? " ":rs.getString(14)) +"]]>").append("</hold_flag>");



						//ITEM_CODE
						//retTabSepStrBuff.append(rs.getString(6)).append("\t");
						retTabSepStrBuff.append("<item_code>").append("<![CDATA[" + rs.getString(6) +"]]>").append("</item_code>");
						//DESCR
						//retTabSepStrBuff.append(rs.getString(7)).append("\t");
						retTabSepStrBuff.append("<item_descr>").append("<![CDATA[" + rs.getString(7) +"]]>").append("</item_descr>");

						//QUANTITY
						//retTabSepStrBuff.append(rs.getDouble(8)).append("\t");
						retTabSepStrBuff.append("<quantity>").append("<![CDATA[" + rs.getDouble(8) +"]]>").append("</quantity>");
						//UNIT
						retTabSepStrBuff.append("<unit>").append("<![CDATA[" + rs.getString("UNIT") +"]]>").append("</unit>");
						//UNIT__STD
						retTabSepStrBuff.append("<unit__std>").append("<![CDATA[" + rs.getString("UNIT__STD") +"]]>").append("</unit__std>");
						//UNIT__STD
						retTabSepStrBuff.append("<conv__qty_stduom>").append("<![CDATA[" + rs.getString("CONV__QTY_STDUOM") +"]]>").append("</conv__qty_stduom>");
						//Hold Qty
						//retTabSepStrBuff.append(holdQty).append("\t");// added by Kunal on 22/10/12
						retTabSepStrBuff.append("<hold_qty>").append("<![CDATA[" + holdQty +"]]>").append("</hold_qty>");
						//STOCK QTY
						retTabSepStrBuff.append("<stock_quantity>").append("<![CDATA[" + balStockQty +"]]>").append("</stock_quantity>");
						//QTY__STD
						retTabSepStrBuff.append("<quantity__stduom>").append("<![CDATA[" + rs.getString("QUANTITY__STDUOM") +"]]>").append("</quantity__stduom>");
						//PENDING_QUANTITY
						//retTabSepStrBuff.append(rs.getDouble(9)).append("\t");
						//retTabSepStrBuff.append("<pending_quantity>").append("<![CDATA[" + rs.getDouble(9) +"]]>").append("</pending_quantity>");
						//Manoj dtd 18/06/2013 Pending Quantity in standard unit
						retTabSepStrBuff.append("<pending_quantity>").append("<![CDATA[" + (rs.getDouble(9)) +"]]>").append("</pending_quantity>");

						//STOCK_QUANTITY
						pendQty = rs.getDouble(9);				

						//***** check itemhashmap wheather current item_code already exists
						// if not add to itemhashmap and get the stock as follows and set in 
						//  tabdelimited string else nothing is to be done just consider the stock as 0



						///////////////////////////////////////////////////////////////////////////////////					
						// alloc_qty to be set based on stock availability
						// it should not be more than pending quantity
						// the balance quantity to be updated in itemCodeMap
						// and to be used for the item's next iteration

						//QTY_ALLOC
						if(balStockQty >= pendQty)
						{
							allocQty = pendQty;
							balStockQty -= pendQty;
						}
						else
						{
							allocQty = balStockQty;
							balStockQty = 0;
						}
						if(DefaultQtyFlag.equals("Y"))
						{
							//retTabSepStrBuff.append(allocQty).append("\t");
							retTabSepStrBuff.append("<qty_alloc>").append("<![CDATA[" + (allocQty) +"]]>").append("</qty_alloc>");
						}
						else
						{
							//retTabSepStrBuff.append("0").append("\t");
							retTabSepStrBuff.append("<qty_alloc>").append("<![CDATA[" + 0 +"]]>").append("</qty_alloc>");
						}

						//active_pick_allow
						retTabSepStrBuff.append("<active_pick_allow>").append("<![CDATA[" + activeAllow +"]]>").append("</active_pick_allow>");

						//qty allocated												
						//retTabSepStrBuff.append(rs.getDouble(10)).append("\t");// ADDED BY AKHILESH
						retTabSepStrBuff.append("<qty_allocated>").append("<![CDATA[" + (rs.getDouble(10)) +"]]>").append("</qty_allocated>");

						//PARENT CUST_CODE
						retTabSepStrBuff.append("<group_code>").append("<![CDATA[" + checkNull(rs.getString("GROUP_CODE")) +"]]>").append("</group_code>");
						//PARENT CUST_CODE NAME
						retTabSepStrBuff.append("<cust_name_1>").append("<![CDATA[" + checkNull(rs.getString("PCUST_NAME")) +"]]>").append("</cust_name_1>");

						//PARENT CUST_CODE NAME
						retTabSepStrBuff.append("<cust_pord>").append("<![CDATA[" + checkNull(rs.getString("CUST_PORD")) +"]]>").append("</cust_pord>");

						//PO DATE
						if(rs.getTimestamp("PORD_DATE") != null)
						{
							retTabSepStrBuff.append("<pord_date>").append("<![CDATA[" + sdf.format(rs.getTimestamp("PORD_DATE")) +"]]>").append("</pord_date>");
						}
						else
						{
							retTabSepStrBuff.append("<pord_date>").append("<![CDATA[]]>").append("</pord_date>");
						}

						//CUST_CODE
						//retTabSepStrBuff.append(rs.getString(1)).append("\t");
						retTabSepStrBuff.append("<cust_code>").append("<![CDATA[" + rs.getString(1) +"]]>").append("</cust_code>");
						//CUST_NAME
						//retTabSepStrBuff.append(rs.getString(2)).append("\t");
						retTabSepStrBuff.append("<cust_name_2>").append("<![CDATA[" + rs.getString(2) +"]]>").append("</cust_name_2>");
						
						//Gtin Case
						//retTabSepStrBuff.append(rs.getString(15)== null?" ":rs.getString(15)).append("\t");// added by Kunal on 22/10/12
						retTabSepStrBuff.append("<gtin_case>").append("<![CDATA[" + (rs.getString(15) == null? " ":rs.getString(15)) +"]]>").append("</gtin_case>");
						//Gtin Unit
						//retTabSepStrBuff.append(rs.getString(16) == null?" ":rs.getString(16)).append("\t");// added by Kunal on 25/10/12
						retTabSepStrBuff.append("<gtin_unit>").append("<![CDATA[" + (rs.getString(16) == null? " ":rs.getString(16)) +"]]>").append("</gtin_unit>");


						// this line has to be commented later
						// as this will be a input from the user							
						itemCodeMap.put(rs.getString(6), new Double(balStockQty));
						//PACK_INSTR
						//retTabSepStrBuff.append(rs.getString(11) == null? " ":rs.getString(11)).append("\t");
						retTabSepStrBuff.append("<pack_instr>").append("<![CDATA[" + (rs.getString(11) == null? " ":rs.getString(11)) +"]]>").append("</pack_instr>");

						//SITE_CODE
						//retTabSepStrBuff.append(rs.getString(12) == null?" ":rs.getString(12)).append("\t");
						retTabSepStrBuff.append("<site_code>").append("<![CDATA[" + (rs.getString(12) == null? " ":rs.getString(12)) +"]]>").append("</site_code>");
						//EXP_LEV
						//retTabSepStrBuff.append(rs.getString(13) == null?" " :rs.getString(13) ).append("\t");
						retTabSepStrBuff.append("<exp_lev>").append("<![CDATA[" + (rs.getString(13) == null? " ":rs.getString(13)) +"]]>").append("</exp_lev>");



						/*if (bappend == true)
						{
							//retTabSepStrBuff.append(balStockQty).append("\t");
							retTabSepStrBuff.append("<stock_quantity>").append("<![CDATA[" + balStockQty +"]]>").append("</stock_quantity>");
						}
						else
						{
							//retTabSepStrBuff.append("0").append("\t");
							retTabSepStrBuff.append("<stock_quantity>").append("<![CDATA[" + 0 +"]]>").append("</stock_quantity>");
						}*/


						retTabSepStrBuff.append("</Detail2>");

						//retTabSepStrBuff.append("\n");
					}


				}while(rs.next());
				retTabSepStrBuff.append("</Header0>");
				retTabSepStrBuff.append("</group0>");
				retTabSepStrBuff.append("</DocumentRoot>");
				resultString = retTabSepStrBuff.toString();
				System.out.println("ResultString....." + resultString);
				pstmt.clearParameters();	
			}

			else
			{
				errCode ="VTNOREC2";
			}
			if (!errCode.equals(""))
			{
				resultString = itmDBAccessEJB.getErrorString("", errCode, "", "", conn);
				System.out.println("resultString: "+resultString);
			}

			rs.close();
		}
		catch (SQLException e)
		{
			System.out.println("SQLException :StockAllocationPrcEJB :getData(Document headerDom, Document detailDom, String windowName, String xtraParams):" + e.getMessage() + ":");
			throw new ITMException(e);
		}
		catch (Exception e)
		{
			System.out.println("Exception :StockAllocationPrcEJB :getData(Document headerDom, Document detailDom, String windowName, String xtraParams):" + e.getMessage() + ":");
			throw new ITMException(e);
		}
		finally
		{
			cleanup();

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

	//Process Method

	public String process(String xmlString, String xmlString2, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		String retStr = "";
		Document detailDom = null;
		Document headerDom = null;

		GenericUtility genericUtility = GenericUtility.getInstance();
		System.out.println("below genericUtility--------------->>>>>>>>>");
		try
		{	
			if(xmlString != null && xmlString.trim().length()!=0)
			{
				headerDom = genericUtility.parseString(xmlString); 
				System.out.println("headerDom" + headerDom);
			}
			if(xmlString2 != null && xmlString2.trim().length()!=0)
			{
				detailDom = genericUtility.parseString(xmlString2); 
				System.out.println("detailDom" + detailDom);
			}

			retStr = process(headerDom, detailDom, windowName, xtraParams);

		}
		catch (Exception e)
		{
			System.out.println("Exception :StockAllocationPrcEJB :process(String xmlString, String xmlString2, String windowName, String xtraParams):" + e.getMessage() + ":");
			e.printStackTrace();
			retStr = e.getMessage();
		}
		return retStr;

	}
	public String process(Document headerDom, Document detailDom, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		System.out.println("detailDom------------------->"+detailDom);
		String childNodeName = "";
		String errCode = "";
		//String siteCode = this.siteCode; 			// 21/08/12 manoharan get the shipping site
		String postOrderFg = this.postOrderFg;
		String saleOrder = null;
		String expLev = null ;
		String itemCode = null;
		String lineNo = null;
		String unit = null;
		String locCode = null;
		String lotSl= null;
		String lotNo = null;
		String locDescr = null;
		String itemShDescr = null;
		double allocQty = 0;
		double quantity = 0;
		double qtyAvailAlloc = 0;
		String stockQuantity = "";
		String errString = "";
		String holdFlag = "";
		String activeAllow = "";
		String getDataSql= null;
		String insertSql = null;
		String updateSql = null;

		Connection conn = null;

		ResultSet rs = null;
		ResultSet rs1 = null;
		ResultSet rs2 = null;

		int updCnt = 0;
		int parentNodeListLength = 0;
		int childNodeListLength = 0;

		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;

		PreparedStatement pstmt = null;
		Statement st = null; 
		int elements = 0,elements1 = 0;
		ArrayList list = new ArrayList();
		ArrayList list1 = new ArrayList();
		boolean flag  = false,isFlag = true,allocqtyflag = false;
		ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
		String sql = "",shipStatus="";
		
		try
		{
			chgUser = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginCode");
			chgTerm = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "termId");

			ConnDriver connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			parentNodeList = detailDom.getElementsByTagName("Detail2");
			parentNodeListLength = parentNodeList.getLength(); 
			System.out.println("parentNodeListLength------------------->"+parentNodeListLength);
			for (int selectedRow = 0; selectedRow < parentNodeListLength; selectedRow++)
			{
				flag  = false;
				parentNode = parentNodeList.item(selectedRow);

				childNodeList = parentNode.getChildNodes();
				childNodeListLength = childNodeList.getLength();
				System.out.println("childNodeListLength---->>> "+ childNodeListLength);
				for (int childRow = 0; childRow < childNodeListLength; childRow++)
				{
					childNode = childNodeList.item(childRow);
					childNodeName = childNode.getNodeName();
					System.out.println("childNodeName---->>> "+ childNodeName);
					if (childNodeName.equals("stock_quantity"))
					{
						stockQuantity = childNode.getFirstChild().getNodeValue();
					}

					if (childNodeName.equals("sale_order"))
					{
						saleOrder = childNode.getFirstChild().getNodeValue();
					}

					if (childNodeName.equals("line_no"))
					{
						lineNo = childNode.getFirstChild().getNodeValue();
					}

					if (childNodeName.equals("item_code"))
					{
						itemCode = childNode.getFirstChild().getNodeValue();
					}

					if (childNodeName.equals("qty_alloc"))  //qty_alloc
					{
						allocQty = Double.parseDouble(childNode.getFirstChild().getNodeValue());
					}
					if (childNodeName.equals("exp_lev"))
					{
						expLev = childNode.getFirstChild().getNodeValue();
					}
					if (childNodeName.equals("hold_flag"))
					{
						holdFlag = childNode.getFirstChild().getNodeValue();
					}
					//added by ritesh on 7/8/13  for request DI3ESUN009 START
					if (childNodeName.equals("active_pick_allow"))
					{
						if(childNode.getFirstChild() != null)
						{
							activeAllow = childNode.getFirstChild().getNodeValue();
						}

					}											//added by ritesh 0n 7/8/13  for request DI3ESUN009 END

				}//inner for loop
				if(!"Y".equalsIgnoreCase(activeAllow) )
				{
					if(!"N".equalsIgnoreCase(activeAllow))
					{
						System.out.println("==activeAllow validate==");
						errString = itmDBAccessEJB.getErrorString("","VTACTALW","","",conn);
						return errString;
					}
				}                 			//added by ritesh on 07/10/13 for request DI3ESUN009 
				if("N".equalsIgnoreCase(activeAllow))
				{
					String sql1 = "";
					double shipperSize = 0;
					sql1 = "select shipper_size from item_lot_packsize where item_code = ? ";
					pstmt = conn.prepareStatement(sql1);
					pstmt.setString(1,itemCode);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						shipperSize = rs.getDouble(1); 
					}
					pstmt.close();pstmt = null;
					rs.close();rs = null;
					try{
						if(allocQty % shipperSize != 0)
						{
							System.out.println("==activeAllow validate 4 N ==");
							flag = true;
							errString = "VTACTALW2"; 
							errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);
						}
					}
					catch(ArithmeticException ex)
					{
						System.out.println("==ArithmeticException==");
						flag = true;
						errString = "VTACTALW3";
						errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);

					}
					if( errString != null && errString.trim().length() > 0 && flag == true)
					{
						isFlag = false;
						list.add(saleOrder);
						list.add(lineNo);
						list.add(itemCode);
						list.add(allocQty);
						elements ++;
					}
				}		//added by ritesh on 07/10/13 for request DI3ESUN009 END
				
				// added by cpatil on 27/11/13 as per manoj sir instruction start
				
				if(saleOrder != null && saleOrder.trim().length() > 0 )
				{
					sql = " select ship_status from sorder where sale_order = ?  ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,saleOrder);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						shipStatus = rs.getString("ship_status"); 
					}
					pstmt.close();pstmt = null;
					rs.close();rs = null;
					
					if( "P".equalsIgnoreCase(shipStatus))
					{
						if( allocQty > 0 )
						{
							System.out.println("@@@@@@saleOrder["+saleOrder+"]::shipStatus["+shipStatus+"]::allocQty["+allocQty+"]");
							flag = true;
							errString = "PROCSUCC"; //  "VTALLQTYSO";
							errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);
						}
						if( errString != null && errString.trim().length() > 0 && flag == true )
						{
							if( !(list1.contains(saleOrder)))
							{
								isFlag = false;
								list1.add(saleOrder);
								elements1 ++;
							}
						}
					}
				}
				// added by cpatil on 27/11/13 as per manoj sir instruction end
				
				
				if(allocQty > 0 && flag == false)			
				{
					System.out.println("if calling........");	
					errString = sorderAllocate(saleOrder, lineNo, itemCode, allocQty, expLev ,holdFlag,activeAllow);
					if (errString != null && errString.trim().length() > 0)
					{
						System.out.println("@@@errString :"+errString);
						return errString;
					}else
					{
						allocqtyflag = true;
					}
				}else if(allocQty == 0) //added by kunal on 27/12/12 as per manoj instruction 
				{
					updateSql = "UPDATE SORDDET SET HOLD_FLAG ='"+holdFlag+"'"
							+ " WHERE SALE_ORDER = '" + saleOrder + " ' "
							+ " AND LINE_NO = '" + lineNo + "' ";

					System.out.println("updateSql::"+ updateSql);	
					pstmt = conn.prepareStatement(updateSql);
					int noRowupdated = pstmt.executeUpdate();
					System.out.println("no of row updated in sorddet = "+noRowupdated);
					pstmt.close();
					pstmt = null;
				}
			}// out for loop 
			//added by ritesh on 07/10/13 for request DI3ESUN009 start
			//if (errString != null && errString.trim().length() > 0)
			if(isFlag == false)
			{
				if(allocqtyflag = true)
				{
					System.out.println("@@@@@ inside if["+errString+"]");
					// errString = "VTACTALW2"; 
					errString = "PROCSUCC"; 
					errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);
				}
				String begPart = errString.substring( 0, errString.indexOf("<trace>") + 7 );
				String endPart = errString.substring( errString.indexOf("</trace>"));
				String mainStr = begPart + "Following error has occured\n" ;
				if(elements > 0 )
				{ 
					mainStr	= mainStr + "Invalid quantity,Entered quantity is not in multiple of Case/shipper Quantity.\n";
				}
				for(int i = 0; i < elements * 4;i++)
				{
					if( i > 0)
					{
						i -= 1;
					}
					mainStr = mainStr + 
							"sale order :"+list.get(i++)+ ",line no :"+ list.get(i++) + ",item code :"+list.get(i++)+",quantity :"+list.get(i++)+"\n" ;
				}
				if(elements1 > 0 )
				{ 
					mainStr = mainStr + "\n pending / incomplete Wave found against sale order.\n";
					mainStr = mainStr + "\n Sale Order : ";
				}
								
				for(int i = 0; i < elements1 ;i++)
				{
					mainStr = mainStr + "  "+list1.get(i)+ "," ;
				}
				
				mainStr = mainStr +  endPart;	
				errString = mainStr;
				begPart =null;
				endPart =null;
				mainStr =null;	
				return errString;

			}
			//added by ritesh on 07/10/13 for request DI3ESUN009 end

			/*Commented By Manoj Sharma dtd 29/032012 Not required as discussed with Manohar Sir
			if(postOrderFg.equals("Y"))
			{

				errString = postOrder(saleOrderArr, xtraParams);
				if (errString != null && errString.trim().length() > 0)
				{
					System.out.println("errString :"+ errString);
					return errString;
				}
			}*/	
			//added by kunal on 27/12/12 
			if (errString == null || errString.trim().length() == 0)
			{
				conn.commit();	
				errString="VTCOMPL";
				errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);
				return errString;
			}
			else
			{
				errString="VTPRCERR";	
				errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);
				return errString;

			}
			//added by kunal on 27/12/12

		}//try end	
		catch(Exception e)
		{
			System.out.println("Exception :StockAllocationPrcEJB :process(String xmlString2, String xmlString2, String windowName, String xtraParams):" + e.getMessage() + ":");
			errString = e.getMessage();
			return errString ;
		}
		finally
		{
			System.out.println("Closing Connection....");
			try
			{
				saleOrderArr.clear();
				if(conn != null)
				{					
					if(pstmt != null)
					{
						pstmt.close();
						pstmt=null;
					}
					conn.close();
					conn = null;
				}

			}
			catch(Exception e)
			{
				errString = e.getMessage();
				e.printStackTrace();
				return errString ;

			}
			/*if (errString == null || errString.trim().length() == 0)
			{
				errString="VTCOMPL";
				errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);
				return errString;
			}
			else
			{
				errString="VTPRCERR";
				errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);
				return errString;

			}*/
		}

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

	/**
	 * @param saleOrder
	 * @param lineNo
	 * @param itemCode
	 * @param allocQty
	 * @param expLev
	 * @param holdFlag
	 * @param activeAllow
	 * @return
	 * @throws ITMException
	 */
	private String sorderAllocate(String saleOrder, String lineNo, String itemCode, double allocQty, String expLev ,String holdFlag,String activeAllow) throws ITMException
	{
		System.out.println("updateSOrder calling ..............");
		String getDataSql = null;
		String sorditemSql=null;
		String itemCodeOrd = null ;
		String unitStd = null ;
		String lotSl= null;
		String lotNo = null;
		String locCode = null;
		String itemShDescr = null;
		String locDescr = null;
		String unit = null;
		String grade = null;
		String siteCodeMfg =null;
		String itemRef = null;
		String status =null;
		String resrvLoc = "",casePickLoc = "",activePickLoc = "" ,deepStoreLoc = "" ,partialResrvLoc = "";//added by kunal on 14/NOV/13
		java.sql.Date expDate = new java.sql.Date(System.currentTimeMillis());
		java.sql.Date mfgDate = new java.sql.Date(System.currentTimeMillis());
		java.sql.Date dateAlloc= new java.sql.Date(System.currentTimeMillis());

		String updateSorditem = null;
		String insertSql = null;
		String updateSordalloc = null;
		String updateSql= null;
		String flag = null;
		String sql1 = "",sql = "";
		String orderByStkStr = "";
		double stockQuantity = 0;
		double qtyAvailAlloc = 0;
		double lotQtyToBeAllocated = 0;
		double qtyDesp =0;
		double quantity =0;
		double convQtyStduom = 0 ;
		double quantityStduom = 0 ;
		double pendingQuantity = 0;
		int intCnt=0;
		int updCnt=0;
		int pendingQty = 0;
		String varValue1 = "",varValue2 = "";
		String errString = "";
		String error = "";
		String errCode = "";
		boolean checkLocation = false;
		boolean isActives = false;
		Connection conn = null;
		PreparedStatement pstmtStock = null ,pstmt1 = null;
		PreparedStatement pstmtStockInsertSordAlloc = null;
		Statement st = null;
		ResultSet rs = null ;
		ResultSet rsSItem = null;
		DistCommon dComm = new DistCommon();
		double minSelfLife=0;
		try
		{
			ConnDriver connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");
			//connDriver = null;
			// 21/08/12 manoharan get the shipping site
			getDataSql = "SELECT SITE_CODE__SHIP FROM SORDER WHERE SALE_ORDER = ? ";
			pstmtStock = conn.prepareStatement(getDataSql);

			pstmtStock.setString(1,saleOrder);

			rs = pstmtStock.executeQuery();

			System.out.println("siteCode------"+ siteCode);
			if(rs.next())
			{
				siteCode = rs.getString(1);
			}
			rs.close();
			rs = null;
			pstmtStock.close();
			pstmtStock = null;
			// end 21/08/12 manoharan get the shipping site

			System.out.println("siteCode------"+ siteCode);
			sorditemSql = "SELECT ITEM_CODE,ITEM_CODE__ORD,UNIT, QTY_DESP, ITEM_REF,"
					+" QUANTITY - QTY_DESP PENDING_QUANTITY,MIN_SHELF_LIFE "
					+" FROM SORDITEM WHERE SALE_ORDER = '" + saleOrder + "' "
					+" AND LINE_NO = '" + lineNo + "' "
					+" AND EXP_LEV = '" + expLev + "' ";
			System.out.println("sorditemSql:::"+sorditemSql);
			st = conn.createStatement();
			rsSItem = st.executeQuery(sorditemSql);
			if (rsSItem.next())
			{
				//ITEM_CODE
				itemCode = rsSItem.getString(1);
				System.out.println("itemCode::::"+ itemCode);
				//ITEM_CODE__ORD
				itemCodeOrd = rsSItem.getString(2);	
				//UNIT
				unitStd = rsSItem.getString(3);	
				//QTY_DESP
				qtyDesp = 0;
				//ITEM_REF
				itemRef = rsSItem.getString(5);	
				//PENDING_QUANTITY
				pendingQuantity = rsSItem.getDouble(6);
				minSelfLife= rsSItem.getDouble("MIN_SHELF_LIFE");
			}
			rsSItem.close();
			st.close();	

			//added by kunal on 14/NOV/13 DI3ESUN009
			resrvLoc  = checkNull(dComm.getDisparams("999999","RESERV_LOCATION",conn));
			casePickLoc  = checkNull(dComm.getDisparams("999999","CASE_PICK_INVSTAT",conn));
			activePickLoc  = checkNull(dComm.getDisparams("999999","ACTIVE_PICK_INVSTAT",conn));
			deepStoreLoc = checkNull(dComm.getDisparams("999999","DEEP_STORE_INVSTAT",conn));
			partialResrvLoc = checkNull(dComm.getDisparams("999999","PRSRV_INVSTAT",conn));	
			if(resrvLoc.trim().length() == 0 || resrvLoc.trim().equalsIgnoreCase("NULLFOUND"))
			{
				resrvLoc = "";
			}
			if(casePickLoc.trim().length() == 0 || casePickLoc.trim().equalsIgnoreCase("NULLFOUND"))
			{
				casePickLoc = "";
			}
			if(activePickLoc.trim().length() == 0 || activePickLoc.trim().equalsIgnoreCase("NULLFOUND"))
			{
				activePickLoc = "";
			}
			if(deepStoreLoc.trim().length() == 0 || deepStoreLoc.trim().equalsIgnoreCase("NULLFOUND"))
			{
				deepStoreLoc = "";
			}
			if(partialResrvLoc.trim().length() == 0 || partialResrvLoc.trim().equalsIgnoreCase("NULLFOUND"))
			{
				partialResrvLoc = "";
			}

			System.out.println("CASE_PICK_INVSTAT="+casePickLoc+"     ACTIVE_PICK_INVSTAT="+activePickLoc+"       RESERV_LOCATION="+resrvLoc+"     DEEP_STORE_INVSTAT="+deepStoreLoc+"     PARTIAL RESERVE LOC="+partialResrvLoc);
			if(resrvLoc.trim().length() > 0 || casePickLoc.trim().length() > 0 || activePickLoc.trim().length() > 0 || deepStoreLoc.trim().length() > 0  || partialResrvLoc.trim().length() > 0)
			{
				checkLocation = true;
			}

			if(checkLocation)
			{
				sql =" SELECT SORDER.CUST_CODE, CUSTOMER.CUST_NAME,SORDDET.LINE_NO, "
						+"SORDER.SALE_ORDER,SORDER.DUE_DATE,SORDITEM.ITEM_CODE,"
						+"ITEM.DESCR,SORDITEM.QUANTITY,"
						+"((SORDITEM.QUANTITY*SORDITEM.CONV__QTY_STDQTY) - (SORDITEM.QTY_DESP*SORDITEM.CONV__QTY_STDQTY) - SORDITEM.QTY_ALLOC) PENDING_QUANTITY,"
						+"SORDITEM.QTY_ALLOC,SORDDET.PACK_INSTR,"
						+"SORDER.SITE_CODE,SORDITEM.EXP_LEV, "
						+"SORDER.PART_QTY AS PART_QTY, CUSTOMER.SINGLE_LOT AS SINGLE_LOT, SORDITEM.MIN_SHELF_LIFE AS MIN_SHELF_LIFE "//Gulzar 5/13/2012
						+" ,SORDER.ALLOC_FLAG AS ALLOC_FLAG , WAVE_TYPE.MASTER_PACK_ALLOW , WAVE_TYPE.ACTIVE_PICK_ALLOW , WAVE_TYPE.STOCK_TO_DOCK_ALLOW "		   
						+"FROM SORDDET,SORDER,SORDITEM,CUSTOMER,ITEM , WAVE_TYPE "
						//Changed by sumit 20/08/12 getting column data end.
						+"WHERE ( SORDER.SALE_ORDER = SORDDET.SALE_ORDER ) AND "
						+"( SORDITEM.SALE_ORDER = SORDER.SALE_ORDER ) AND "
						+"( SORDDET.LINE_NO = SORDITEM.LINE_NO ) AND "
						+"( SORDITEM.ITEM_CODE = ITEM.ITEM_CODE ) AND "
						+"( SORDER.CUST_CODE = CUSTOMER.CUST_CODE ) AND  "
						+"( SORDDET.SITE_CODE = SORDITEM.SITE_CODE ) AND  "
						//Changed by sumit on 20/08/12 join customer with wave_type
						+"( CUSTOMER.WAVE_TYPE = WAVE_TYPE.WAVE_TYPE(+)) AND "
						//+" SORDER.SITE_CODE = ? AND "							   
						+" SORDER.SALE_ORDER = ? "
						+" AND SORDITEM.LINE_NO = ? "
						+"AND CASE WHEN SORDDET.STATUS IS NULL THEN 'P' ELSE SORDDET.STATUS end <> 'C' "
						+"AND CASE WHEN SORDER.STATUS IS NULL THEN 'P' ELSE SORDER.STATUS end = 'P' "
						//Changed by sumit on 12/09/12 considering hold_flag from sorddet 
						+" AND CASE WHEN SORDDET.HOLD_FLAG IS NULL THEN 'N' ELSE SORDDET.HOLD_FLAG end <> 'Y'"
						//Chnaged by Rohan on 11/07/12 revert Changes of Manual Stock Allocation
						//Changed by Rohan on 22/06/12
						//+"AND SORDITEM.QUANTITY - SORDITEM.QTY_DESP - SORDITEM.QTY_ALLOC > 0 "
						//+"AND (SORDITEM.QUANTITY - SORDITEM.QTY_DESP - SORDITEM.QTY_ALLOC > 0 OR SORDER.ALLOC_FLAG='M' )"
						//Changed by sumit on 13/08/12 as per manual stock allocation start.
						//+"AND SORDITEM.QUANTITY - SORDITEM.QTY_DESP - SORDITEM.QTY_ALLOC > 0 "
						+" AND (((SORDITEM.QUANTITY*SORDITEM.CONV__QTY_STDQTY) - (SORDITEM.QTY_DESP*SORDITEM.CONV__QTY_STDQTY) - SORDITEM.QTY_ALLOC > 0 ) "  
						+" OR (FN_CHECK_MANUAL_STOCK_ALLOC(SORDER.SALE_ORDER, SORDDET.LINE_NO) = 0 ) )"	 
						+"AND SORDITEM.LINE_TYPE = 'I'";
				pstmt1 = conn.prepareStatement(sql);
				pstmt1.setString(1,saleOrder);
				pstmt1.setString(2,lineNo);
				rs = pstmt1.executeQuery();
				if(rs.next())
				{
					pendingQty = rs.getInt("PENDING_QUANTITY");
				}
				rs.close();
				rs = null;
				pstmt1.close();
				pstmt1 = null;

				HashMap itemVolMap = getItemVoumeMap(itemCode, "", conn);
				double packSize = (Double)itemVolMap.get("PACK_SIZE");
				System.out.println(" pendingQty = "+pendingQty+"  packSize ="+packSize);
				if((pendingQty % packSize) > 0)
				{
					isActives = true;
					orderByStkStr = " AND LOCATION.INV_STAT IN(?,?,?,?,?) ";
				}
				else
				{
					isActives = false;
					orderByStkStr = " AND LOCATION.INV_STAT IN(?,?,?,?) ";
				}
			}

			//added by kunal on 14/NOV/13  DI3ESUN009 END


			getDataSql = "SELECT STOCK.LOT_NO,STOCK.LOT_SL,"
					+"STOCK.LOC_CODE, "
					+"STOCK.UNIT,  "
					+"(STOCK.QUANTITY - STOCK.ALLOC_QTY - case when STOCK.HOLD_QTY is null then 0 else STOCK.HOLD_QTY end ) AS QTY_AVAIL_ALLOC ,"
					+"STOCK.GRADE,STOCK.EXP_DATE,STOCK.CONV__QTY_STDUOM,STOCK.QUANTITY, " 
					+"STOCK.MFG_DATE,STOCK.SITE_CODE__MFG "
					+"FROM STOCK,ITEM,LOCATION,INVSTAT " 
					+"WHERE (ITEM.ITEM_CODE = STOCK.ITEM_CODE) "
					+"AND (LOCATION.LOC_CODE = STOCK.LOC_CODE ) "
					+"AND (LOCATION.INV_STAT = INVSTAT.INV_STAT) "
					+"AND INVSTAT.AVAILABLE = 'Y' "
					+"AND STOCK.ITEM_CODE = ? AND STOCK.SITE_CODE = ? "
					+"AND (STOCK.QUANTITY - STOCK.ALLOC_QTY - case when STOCK.HOLD_QTY is null then 0 else STOCK.HOLD_QTY end) > 0  "
					+"AND NOT EXISTS (SELECT 1 FROM INV_RESTR I WHERE I.INV_STAT = INVSTAT.INV_STAT AND I.REF_SER = 'S-DSP' )";


			if("N".equalsIgnoreCase(activeAllow))
			{
				getDataSql = getDataSql.concat(" AND LOCATION.INV_STAT NOT IN(?,?) ");
			}
			//Added by manoj dtd 09/08/2013 to implement FEFO concept  
			getDataSql=getDataSql+"AND (MONTHS_BETWEEN(TO_DATE(STOCK.EXP_DATE),SYSDATE) > ? ) ";

			if(checkLocation) //added by kunal on 14/NOV/13  DI3ESUN009 
			{
				getDataSql=getDataSql+orderByStkStr;
			}

			getDataSql=getDataSql+"  ORDER BY CASE WHEN STOCK.EXP_DATE IS NULL THEN STOCK.CREA_DATE ELSE STOCK.EXP_DATE END,INVSTAT.ALLOC_PREF, STOCK.LOC_CODE ";
			pstmtStock = conn.prepareStatement(getDataSql);
			pstmtStock.setString(1,itemCode);
			pstmtStock.setString(2,siteCode);
			pstmtStock.setDouble(3,minSelfLife);
			//added by ritesh 0n 7/8/13  for request DI3ESUN009 START
			if("N".equalsIgnoreCase(activeAllow))			
			{	  
				varValue1 = dComm.getDisparams("999999","ACTIVE_PICK_INVSTAT", conn);
				varValue2 = dComm.getDisparams("999999","PRSRV_INVSTAT", conn); 
				pstmtStock.setString(3,varValue1);
				pstmtStock.setString(4,varValue2);
				pstmtStock.setDouble(5,minSelfLife);
				if(checkLocation) //added by kunal on 14/NOV/13  DI3ESUN009
				{
					pstmtStock.setString(6,resrvLoc);
					pstmtStock.setString(7,casePickLoc);
					pstmtStock.setString(8,activePickLoc);
					pstmtStock.setString(9,deepStoreLoc);
					if(isActives)
					{
						pstmtStock.setString(10,partialResrvLoc);
					}
				}
			}
			else
			{
				if(checkLocation) //added by kunal on 14/NOV/13  DI3ESUN009
				{
					pstmtStock.setString(4,resrvLoc);
					pstmtStock.setString(5,casePickLoc);
					pstmtStock.setString(6,activePickLoc);
					pstmtStock.setString(7,deepStoreLoc);
					if(isActives)
					{
						pstmtStock.setString(8,partialResrvLoc);
					}
				}
			}
			//added by ritesh 0n 7/8/13  for request DI3ESUN009 START


			rs = pstmtStock.executeQuery();
			System.out.println("siteCode------"+ siteCode);
			System.out.println("itemCode------"+ itemCode);
			System.out.println("Select completed");
			while(rs.next())
			{
				System.out.println( "INSIDE WHILE LOOP.............");
				//LOT_NO
				lotNo = rs.getString(1);
				//LOT_SL
				lotSl = rs.getString(2);
				//LOC_CODE
				locCode = rs.getString(3);
				//UNIT
				unit = rs.getString(4);
				//QTY_AVAIL_ALLO
				qtyAvailAlloc = rs.getDouble(5);
				//GRADE
				grade = rs.getString(6);
				//EXP_DATE
				expDate =rs.getDate(7);
				//CONV__QTY_STDUOM
				convQtyStduom=rs.getDouble(8);
				//QUANTITY
				quantity= rs.getDouble(9);
				//MFG_DATE
				mfgDate = rs.getDate(10);
				//SITE_CODE__MFG
				siteCodeMfg = rs.getString(11);

				invallocTrace.setRefSer("S-ALC");
				invallocTrace.setRefId(saleOrder);
				invallocTrace.setRefLine(lineNo);
				invallocTrace.setSiteCode(siteCode);
				invallocTrace.setItemCode(itemCode);
				invallocTrace.setLocCode(locCode);
				invallocTrace.setLotNo(lotNo);
				invallocTrace.setLotSl(lotSl);
				PreparedStatement pstmt = null;
				ResultSet rs1 = null;
				String sqls = "";
				double shipperSize = 0;
				lotQtyToBeAllocated = 0;

				//added by ritesh on 07/10/13 for request DI3ESUN009 start
				if(!"N".equalsIgnoreCase(activeAllow))
				{
					if (allocQty >= qtyAvailAlloc)
					{
						lotQtyToBeAllocated = qtyAvailAlloc;
					}
					else
					{
						lotQtyToBeAllocated = allocQty;
					}
				}
				else
				{
					sqls = "select shipper_size from item_lot_packsize where item_code = ? and '"+lotNo+"' between lot_no__from and lot_no__to";
					pstmt = conn.prepareStatement(sqls);
					pstmt.setString(1,itemCode);
					rs1 = pstmt.executeQuery();
					if(rs1.next())
					{
						shipperSize = rs1.getDouble(1); 
					}
					pstmt.close();pstmt = null;
					rs1.close();rs1 = null;	
					System.out.print(":: qtyAvailAlloc ::"+qtyAvailAlloc);
					System.out.print(":: shipperSize ::"+shipperSize);
					if(qtyAvailAlloc >= shipperSize )
					{
						int div = 0;
						if(shipperSize > 0)
						{
							div = (int) (allocQty / shipperSize);
							lotQtyToBeAllocated=div*shipperSize;
							if(lotQtyToBeAllocated > qtyAvailAlloc)
							{
								lotQtyToBeAllocated = 0; 
								System.out.println("::::::uncommon");
							}
						}
						System.out.println("::lotQtyToBeAllocated::"+lotQtyToBeAllocated);
					}
					else
					{
						continue;
					}

				}

				//added by ritesh on 07/10/13 for request DI3ESUN009 end		

				invallocTrace.setAllocQty(lotQtyToBeAllocated);	
				//	
				allocQty -= lotQtyToBeAllocated ;

				System.out.print("AllocQty = " + lotQtyToBeAllocated);

				if (lotQtyToBeAllocated > 0) 
				{
					invallocTrace.setChgUser(chgUser);
					invallocTrace.setChgTerm(chgTerm);
					invallocTrace.setChgWin("W_SORDALLOC" );
					errString = updateInvallocTrace(invallocTrace, conn);

					if (errString != null && errString.trim().length() > 0)
					{
						System.out.println("manohar connection rolledback errString :::"+ errString );
						conn.rollback();
						return errString;
					}

					if (!saleOrderArr.contains(saleOrder))
					{
						saleOrderArr.add(saleOrderArr.size() , saleOrder);
					}

					updateSorditem ="UPDATE SORDITEM  SET QTY_ALLOC = CASE WHEN QTY_ALLOC IS NULL THEN 0 ELSE QTY_ALLOC END  +   " + new Double(lotQtyToBeAllocated).toString() 
							+" WHERE SALE_ORDER = '" + saleOrder + "' "
							+" AND LINE_NO = '" + lineNo + "' "
							+" AND EXP_LEV = '" + expLev + "' ";

					System.out.println("updateSql------->"+updateSorditem);
					st = conn.createStatement();
					st.executeUpdate(updateSorditem);
					System.out.println("UPDATE  SUCCESS FOR SORDITEM....>>>>>>>>");

					sorditemSql = "SELECT COUNT(1) FROM SORDALLOC " 
							+ " WHERE SALE_ORDER = '" + saleOrder + "' "
							+ " AND LINE_NO = '" + lineNo + "' "
							+ " AND EXP_LEV = '" + expLev + "' "
							+ " AND ITEM_CODE__ORD = '" + itemCodeOrd + "' "
							+ " AND ITEM_CODE = '" + itemCode + "' "
							+ " AND LOT_NO = '" + lotNo + "' "
							+ " AND LOT_SL = '" + lotSl + "' "
							+ " AND LOC_CODE = '" + locCode + "' " ;
					rsSItem =  st.executeQuery(sorditemSql);
					int count = 0 ;
					if (rsSItem.next())
					{
						count = rsSItem.getInt(1);
					}
					rsSItem.close();
					rsSItem=null;
					st.close();
					st=null;
					if (count > 0 )
					{
						updateSql = "UPDATE SORDALLOC SET QTY_ALLOC =  QTY_ALLOC + " + new Double(lotQtyToBeAllocated).toString()
								+ " WHERE SALE_ORDER = '" + saleOrder + " ' "
								+ " AND LINE_NO = '" + lineNo + "' "
								+ " AND EXP_LEV = '" + expLev + "' "
								+ " AND ITEM_CODE__ORD = '" + itemCodeOrd + "' "
								+ " AND ITEM_CODE = '" + itemCode + "' "
								+ " AND LOT_NO = '" + lotNo + "' "
								+ " AND LOT_SL = '" + lotSl + "' "
								+ " AND LOC_CODE = '" + locCode + "' " ;
						System.out.println("updateSql:::>>>>"+ updateSql);	
						st = conn.createStatement();
						st.executeUpdate(updateSql);
						st.close();
						st=null;
						System.out.println("UPDATE  SUCCESS FOR SORDALLOC....");
					}
					else
					{
						insertSql ="INSERT INTO SORDALLOC (SALE_ORDER,LINE_NO,EXP_LEV,ITEM_CODE__ORD,SITE_CODE ,"
								+"ITEM_CODE,QUANTITY ,LOT_NO, LOT_SL, LOC_CODE, UNIT, QTY_ALLOC,"
								+"ITEM_REF, DATE_ALLOC, STATUS,ITEM_GRADE, EXP_DATE, ALLOC_MODE, "
								+" CONV__QTY_STDUOM, UNIT__STD, QUANTITY__STDUOM, "
								+"MFG_DATE, SITE_CODE__MFG ) "
								+"VALUES ( ?, ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ,"
								+" ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) " ;

						pstmtStockInsertSordAlloc = conn.prepareStatement(insertSql);

						System.out.println("Insert Sql :"+ insertSql );
						//SALE_ORDER
						pstmtStockInsertSordAlloc.setString(1, saleOrder);
						System.out.println("saleOrder------->"+saleOrder);
						//LINE_NO
						pstmtStockInsertSordAlloc.setString(2, lineNo);
						System.out.println("lineNo------->"+lineNo);
						//EXP_LEV
						pstmtStockInsertSordAlloc.setString(3, expLev);
						System.out.println("EXP_LEV------->"+expLev);
						//ITEM_CODE__ORD
						pstmtStockInsertSordAlloc.setString(4, itemCodeOrd);
						System.out.println("itemCode------->"+itemCodeOrd);
						//SITE_CODE
						pstmtStockInsertSordAlloc.setString(5, siteCode);
						System.out.println("siteCode------->"+siteCode);
						//ITEM_CODE
						pstmtStockInsertSordAlloc.setString(6, itemCode);
						System.out.println("itemCode------->"+itemCode);
						//QUANTITY***** set pending qty
						pstmtStockInsertSordAlloc.setDouble(7, pendingQuantity);
						System.out.println("pendingQuantity------->"+pendingQuantity);
						//LOT_NO
						pstmtStockInsertSordAlloc.setString(8, lotNo);
						System.out.println("lotNo------->"+lotNo);
						//LOT_SL
						pstmtStockInsertSordAlloc.setString(9, lotSl);
						System.out.println("lotSl------->"+lotSl);
						//LOC_CODE
						pstmtStockInsertSordAlloc.setString(10, locCode);
						System.out.println("locCode------->"+locCode);
						//UNIT
						pstmtStockInsertSordAlloc.setString(11, unit);
						System.out.println("unit------->"+unit);
						//QTY_ALLOC
						pstmtStockInsertSordAlloc.setDouble(12, lotQtyToBeAllocated);
						System.out.println("lotQtyToBeAllocated------->"+lotQtyToBeAllocated);
						//ITEM_REF
						pstmtStockInsertSordAlloc.setString(13,itemRef);
						System.out.println("itemRef------->"+itemRef);
						//DATE_ALLOC
						pstmtStockInsertSordAlloc.setDate(14,dateAlloc);
						System.out.println("dateAlloc------->"+dateAlloc);
						//STATUS
						pstmtStockInsertSordAlloc.setString(15,"P");
						System.out.println("status------->P");
						//ITEM_GRADE
						pstmtStockInsertSordAlloc.setString(16,grade);
						System.out.println("grade------->"+grade);
						//EXP_DATE
						pstmtStockInsertSordAlloc.setDate(17,expDate);
						System.out.println("expDate------->"+expDate);
						//ALLOC_MODE
						pstmtStockInsertSordAlloc.setString(18,"M");
						//CONV__QTY_STDUOM
						pstmtStockInsertSordAlloc.setDouble(19,convQtyStduom);
						System.out.println("convQtyStduom------->"+convQtyStduom);
						//UNIT__STD
						pstmtStockInsertSordAlloc.setString(20,unitStd);
						System.out.println("unitStd------->"+unitStd);

						quantityStduom = convQtyStduom * lotQtyToBeAllocated ;
						//QUANTITY__STDUOM
						pstmtStockInsertSordAlloc.setDouble(21,quantityStduom);
						System.out.println("quantityStduom------->"+quantityStduom);
						//MFG_DATE
						pstmtStockInsertSordAlloc.setDate(22,mfgDate);
						System.out.println("mfgDate------->"+mfgDate);
						//SITE_CODE__MFG
						pstmtStockInsertSordAlloc.setString(23,siteCodeMfg);
						System.out.println("siteCodeMfg------->"+siteCodeMfg);
						//QTY_DESP

						intCnt = pstmtStockInsertSordAlloc.executeUpdate();
						System.out.println("insertion  success ...............>>>>>>>>");
						pstmtStockInsertSordAlloc.close();
					}//end else
					//st.close();

					//added by akhilesh to update hold_flag
					updateSql = "UPDATE SORDDET SET HOLD_FLAG ='"+holdFlag+"'"
							+ " WHERE SALE_ORDER = '" + saleOrder + " ' "
							+ " AND LINE_NO = '" + lineNo + "' ";

					System.out.println("updateSql::>>>>"+ updateSql);	
					st = conn.createStatement();
					st.executeUpdate(updateSql);
					st.close();
					st=null;
					System.out.println("UPDATE  SUCCESS FOR SORDDET....");
					//ended by akhilesh

				}//end of if(lotQtyToBeAllocated > 0)


			}//end of while 
			rs.close();
			pstmtStock.clearParameters();
			pstmtStock.close();
			System.out.println("manohar connection commited errString :::"+ errString );
			conn.commit();
		}
		catch(SQLException se)
		{
			System.out.println("SQLException :" + se);
			se.printStackTrace();
			errString = se.getMessage();
			System.out.println("manohar connection rolledback errString 3 :::"+ errString );
			conn.rollback();
			return errString;
		}

		catch(Exception e)
		{
			System.out.println("Exception :" + e);
			errString = e.getMessage();
			e.printStackTrace();
			try
			{
				System.out.println("manohar connection rolledback errString 2 :::"+ errString );
				conn.rollback();
			}
			catch(Exception e1)
			{
				e = e1;
			}
			return errString ;


		}
		finally
		{
			try
			{
				if (errString == null || errString.trim().length() == 0)
				{
					System.out.println("manohar connection commited errString :::"+ errString );
					conn.commit();
				}
				if(conn != null)
				{	
					if(pstmtStock != null)
					{
						pstmtStock.close();
						pstmtStock = null;
					}
					if(pstmtStockInsertSordAlloc != null)
					{
						pstmtStockInsertSordAlloc.close();
						pstmtStockInsertSordAlloc = null;
					}
					if(st != null)
					{
						st.close();
						st = null;
					}
					conn.close();
					conn = null;
				}
			}
			catch(Exception e)
			{
				errString = e.getMessage();
				e.printStackTrace();
				System.out.println("::@errString@:::"+errString);
				return errString ;

			}
			return errString;
		}

	}

	//	updateInvallocTrace
	private String updateInvallocTrace(InvAllocTrace invallocTrace, Connection conn) throws ITMException
	{
		String errString = "";
		String errCode = "" ;
		String sql = null;
		String sqlUpdate = null;
		String keyStringQuery = null;
		java.sql.Date tranDate = invallocTrace.getTranDate();
		String refSer = invallocTrace.getRefSer();
		String refId = invallocTrace.getRefId();
		String refLine = invallocTrace.getRefLine();
		String siteCode = invallocTrace.getSiteCode();
		String itemCode = invallocTrace.getItemCode();
		String locCode = invallocTrace.getLocCode();
		String lotNo = invallocTrace.getLotNo();
		String lotSl = invallocTrace.getLotSl();
		double allocQty = invallocTrace.getAllocQty();
		String chgUser = invallocTrace.getChgUser();
		String chgTerm = invallocTrace.getChgTerm();
		String chgWin = invallocTrace.getChgWin();
		java.sql.Date chgDate = new java.sql.Date(System.currentTimeMillis());
		//Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rSet = null ;
		Statement stmt = null;
		String tranId = null;

		try
		{
			keyStringQuery = "SELECT KEY_STRING, TRAN_ID_COL, REF_SER FROM TRANSETUP WHERE UPPER(TRAN_WINDOW) = 'T_ALLOCTRACE'";
			System.out.println("keyStringQuery--------->>"+keyStringQuery);
			//ConnDriver connDriver = new ConnDriver();
			//conn = connDriver.getConnectDB("DriverITM");
			//connDriver = null;
			stmt = conn.createStatement();
			rSet = stmt.executeQuery(keyStringQuery);
			System.out.println("keyString :"+rSet.toString());
			String tranSer1 = "";
			String keyString = "";
			String keyCol = "";
			if (rSet.next())
			{
				keyString = rSet.getString(1);
				keyCol = rSet.getString(2);
				tranSer1 = rSet.getString(3);				
			}

			java.util.Date date = null;
			Timestamp timestamp = new Timestamp(System.currentTimeMillis());
			System.out.println(genericUtility.getDBDateFormat());

			SimpleDateFormat simpledateformat = new SimpleDateFormat(genericUtility.getDBDateFormat());
			date = simpledateformat.parse(timestamp.toString());
			timestamp = Timestamp.valueOf(simpledateformat.format(date).toString() + " 00:00:00.0");
			String tranDateStr = (new SimpleDateFormat(genericUtility.getApplDateFormat())).format(timestamp).toString();
			System.out.println("keyString :"+keyString);
			System.out.println("keyCol :"+keyCol);
			System.out.println("tranSer1 :"+tranSer1);

			String xmlValues = "";
			xmlValues ="<?xml version=\"1.0\" encoding=\"utf-8\"?><Root>";
			xmlValues = xmlValues + "<Header></Header>";
			xmlValues = xmlValues + "<Detail1>";
			//Added by Manoj dtd 10/09/2012 to generate key
			xmlValues = xmlValues + "<dummy>D</dummy>";
			xmlValues = xmlValues + "<site_code>" + siteCode + "</site_code>";
			//Added by Manoj dtd 10/09/2012 to generate key
			xmlValues = xmlValues + "<tran_date>" + tranDateStr + "</tran_date>";
			xmlValues = xmlValues +"</Detail1></Root>";
			System.out.println("xmlValues  :["+xmlValues+"]");
			TransIDGenerator tg = new TransIDGenerator(xmlValues, "BASE", commonConstants.DB_NAME);
			tranId = tg.generateTranSeqID(tranSer1, keyCol, keyString, conn);
			System.out.println("tranId :"+tranId);

			sql = "INSERT INTO INVALLOC_TRACE (TRAN_ID, TRAN_DATE, REF_SER, REF_ID,"
					+"REF_LINE,ITEM_CODE, SITE_CODE, LOC_CODE,LOT_NO, LOT_SL, ALLOC_QTY, CHG_WIN," 
					+"CHG_USER, CHG_TERM, CHG_DATE )VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

			pstmt = conn.prepareStatement(sql);

			System.out.println("trans id="+tranId);
			System.out.println("tranDate="+tranDate);
			System.out.println("refSer="+refSer);
			System.out.println("refId="+refId);
			System.out.println("refLine="+refLine);
			System.out.println("itemCode="+itemCode);
			System.out.println("siteCode="+siteCode);
			System.out.println("locCode="+locCode);
			System.out.println("lotNo="+lotNo);
			System.out.println("LOT_SL="+lotSl);
			System.out.println("LLOC_QTY="+allocQty);
			System.out.println("chgWin="+chgWin);
			System.out.println("chgUser="+chgUser);
			System.out.println("chgTerm="+chgTerm);
			System.out.println("chgDate="+chgDate);

			pstmt.setString(1,tranId);
			pstmt.setDate(2,tranDate);
			pstmt.setString(3,refSer);
			pstmt.setString(4,refId);
			pstmt.setString(5,refLine);
			pstmt.setString(6,itemCode );
			pstmt.setString(7,siteCode);
			pstmt.setString(8,locCode);
			pstmt.setString(9,lotNo );
			pstmt.setString(10,lotSl);
			pstmt.setDouble(11,allocQty);
			pstmt.setString(12,chgWin);
			pstmt.setString(13,chgUser);
			pstmt.setString(14,chgTerm);
			pstmt.setDate(15,chgDate);
			pstmt.executeUpdate();
			System.out.println("insertion of sql inside updateInvallocTrace success on date "+ chgDate);

			System.out.println("Stock Updated...............................");
			if(allocQty >= 0)
			{

				sqlUpdate = "UPDATE STOCK SET ALLOC_QTY =(CASE WHEN ALLOC_QTY IS NULL THEN 0 ELSE ALLOC_QTY END) + ? "
						+"WHERE ITEM_CODE = ? AND SITE_CODE = ? AND LOC_CODE = ? AND LOT_NO = ? AND LOT_SL = ?";
				pstmt = conn.prepareStatement(sqlUpdate);
				pstmt.setDouble(1,allocQty);
				pstmt.setString(2,itemCode);
				pstmt.setString(3, siteCode);
				pstmt.setString(4,locCode);
				pstmt.setString(5,lotNo);
				pstmt.setString(6,lotSl);
				pstmt.executeUpdate();
			}
			System.out.println("Updated End.");
		}
		catch(SQLException e)
		{
			System.out.println("SQLException :updateInvallocTrace : "  + sqlUpdate + "\n" +e.getMessage());
			System.out.println("ALLOC_QTY : " + allocQty);
			System.out.println("ITEM_CODE : " + itemCode);
			System.out.println("SITE_CODE : " + siteCode);
			System.out.println("LOC_CODE : " + locCode);
			System.out.println("LOT_NO : " + lotNo);
			System.out.println("LOT_SL : " + lotSl);
			errString = e.getMessage();
			e.printStackTrace();
			return errString;

		}
		catch(Exception e)
		{
			System.out.println("manohar :updateInvallocTrace connection rollback:"  + sqlUpdate + "\n" +e.getMessage());
			errString = e.getMessage();
			e.printStackTrace();
			try
			{
				conn.rollback();
			}
			catch(Exception e1)
			{
				e = e1;
			}
			return errString;	

		}
		finally
		{
			try
			{
				//if(conn != null)
				//{	
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
				if(stmt != null)
				{
					stmt.close();
					stmt = null;
				}
				//conn.close();
				//conn = null;
				//}
			}
			catch(Exception e)
			{
				errString = e.getMessage(); 
				e.printStackTrace();
				return errString;	
			}
			return errString;	
		}

	}
	/*Commented By Manoj Sharma dtd 29/032012 Not required as discussed with Manohar Sir
	private String postOrder(ArrayList saleOrderArr, String xtraParams) throws ITMException
	{
		String errString = "";
		String lotSl= null ;
		String tranId = null ;
		String sql = null;
		String adjDrcr = null ;
		String adjCustAdv = null ;
		String adjAdvMode = null ;
		String custCode = "";
		String dueDate = "";
		String orderType = "";
		Connection conn = null;
		ResultSet rs = null;
		Statement st = null;
		StringBuffer TabSepStrBuff = new StringBuffer();

		nvo_business_object_win_methods comp = null;
		try
		{
			System.out.println("Looking Up for NVO..........");
			Properties props = new Properties();
			props.put("org.omg.CORBA.ORBClass", "com.sybase.CORBA.ORB");
			ORB orb = ORB.init(((String []) (null)), props);
			SessionManager.Manager manager = ManagerHelper.narrow(orb.string_to_object("iiop://192.168.0.217:9000"));
			SessionManager.Session session = manager.createSession("jagadmin", "");
			SessionManager.Factory factory = FactoryHelper.narrow(session.lookup("webitm4/nvo_business_object_win_methods"));
			comp = nvo_business_object_win_methodsHelper.narrow(factory.create());

			System.out.println("saleOrderArr.size() ::" + saleOrderArr.size());
			ConnDriver connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;

			for(int saleOrderCtr = 0;saleOrderCtr < saleOrderArr.size(); saleOrderCtr++)
			{
				System.out.println("sale order from is ::: "+(saleOrderArr.get(saleOrderCtr)).toString());
				String saleOrderStr = (saleOrderArr.get(saleOrderCtr)).toString();
				System.out.println("saleOrderStr ---- "+ saleOrderStr);
				sql = "SELECT CUST_CODE, DUE_DATE, ORDER_TYPE FROM SORDER WHERE SALE_ORDER ='"+ ((saleOrderArr.get(saleOrderCtr)).toString()) +"'" ;  
				st = conn.createStatement();
				rs = st.executeQuery(sql);
				System.out.println(sql);
				while(rs.next())
				{
					custCode = rs.getString(1);
					dueDate = rs.getString(2);
					orderType = rs.getString(3);
					TabSepStrBuff.append(saleOrderStr).append("\t") ;
					System.out.println("sale order from is ::: "+saleOrderStr);
					TabSepStrBuff.append(saleOrderStr).append("\t") ;
					System.out.println("sale order to is ::: "+saleOrderStr);
					TabSepStrBuff.append(custCode).append("\t");
					System.out.println("custCode from::"+ custCode);
					TabSepStrBuff.append(custCode).append("\t");
					System.out.println("custCode to ::"+custCode);
					TabSepStrBuff.append(dueDate).append("\t");
					System.out.println("dueDate from ::"+ dueDate);
					TabSepStrBuff.append(dueDate).append("\t");
					System.out.println("dueDate to ::"+ dueDate);
					//lot_sl 
					TabSepStrBuff.append(" ").append("\t");

					TabSepStrBuff.append(" ").append("\t");
					System.out.println("orderType:::"+orderType);
					adjDrcr = itmDBAccessEJB.getEnvDis("999999", "ADJUST_DR_CR_NOTE",conn);
					if(adjDrcr.equals("NULLFOUND"))
					{
						adjDrcr = "N";
					}
					TabSepStrBuff.append(adjDrcr).append("\t");
					adjCustAdv = itmDBAccessEJB.getEnvDis("999999", "ADJUST_CUST_ADV",conn);
					if(adjCustAdv.equals("NULLFOUND"))
					{
						adjCustAdv = "N";
					}
					TabSepStrBuff.append(adjCustAdv).append("\t");
					//club_pend_ord
					TabSepStrBuff.append("N").append("\t");
					//alloc_stock
					TabSepStrBuff.append("N").append("\t");
					//club_order
					TabSepStrBuff.append("N").append("\t");
					//rfresh_db
					TabSepStrBuff.append("N").append("\t");
					//ib_changed
					TabSepStrBuff.append("0").append("\t");
					adjAdvMode = itmDBAccessEJB.getEnvDis("999999", "ADJ_ADV_CUST_SALE",conn);
					if(adjAdvMode.equals("NULLFOUND"))
					{
						adjAdvMode = "C";
					}
					TabSepStrBuff.append(adjAdvMode).append("\t");
					System.out.println("adjAdvMode::"+ adjAdvMode);
					TabSepStrBuff.append(siteCode).append("\t");
					System.out.println("siteCode::"+ siteCode);

					TabSepStrBuff.append("TEMP SITE").append("\n");

					String retTabSepStr = TabSepStrBuff.toString();

					System.out.println("Tabstring :" + retTabSepStr);

					errString = comp.gbf_process("nvo_bo_post_order",retTabSepStr , "" , xtraParams);

					if (errString != null && errString.trim().length() > 0)
					{
						System.out.println("errString :"+ errString);
						return errString ;
					}
				}

			}
		}//try
		catch(SQLException se)
		{
			System.out.println("SQLException :" + se);
			se.printStackTrace();
			errString = se.getMessage();
			return errString ;

		}
		catch(Exception e)
		{
			System.out.println("Exception :ProcessNVOService :getComponent :==>\n"+e.getMessage());
			e.printStackTrace();
			errString = e.getMessage();
			try
			{
				conn.rollback();
			}
			catch(Exception e1)
			{
				e = e1;
			}
			return errString ;

		}
		finally
		{
			try
			{
				if(conn != null)
				{	
					if(st != null)
					{
						st.close();
						st = null;
					}
					conn.close();
					conn = null;
				}
			}
			catch(Exception e)
			{
				errString = e.getMessage();
				e.printStackTrace();
				return errString ; 
			}
			return errString ;
		}

	}*/
	void cleanup()
	{
		itemCodeMap.clear();
	}
	private HashMap getItemVoumeMap(String itemCode,String lotNo,Connection con)throws Exception
	{
		double packSize = 0,itemSize = 0,lotSize = 0;
		PreparedStatement pstmt = null;
		String sql="";
		ResultSet rs = null;
		double itmLen = 0,itmWidth = 0,itmHeight = 0,itemWeight = 0,lotLen = 0 ,lotHeight = 0,lotWidth = 0,lotWeight = 0;
		HashMap dataVolumeMap = new HashMap();

		try {

			sql = "SELECT I.LENGTH ITEM_LEN,I.WIDTH ITEM_WID,I.HEIGHT ITEM_HEIGHT,I.GROSS_WEIGHT ITEM_WEIGHT,"
					+" L.LENGTH LITEM_LEN,L.WIDTH LITEM_WID,L.HEIGHT LITEM_HEIGHT,L.SHIPPER_SIZE SHIPSIZE,L.GROSS_WEIGHT LOT_WEIGHT FROM"
					+" ITEM I,ITEM_LOT_PACKSIZE L"
					+" WHERE I.ITEM_CODE = L.ITEM_CODE"
					+" AND L.LOT_NO__FROM <= ? AND L.LOT_NO__TO >= ?"
					+" AND  I.ITEM_CODE = ?";


			pstmt = con.prepareStatement(sql);
			if(lotNo != null && lotNo.length() > 0)
			{
				pstmt.setString(1, lotNo);
				pstmt.setString(2, lotNo);
			}
			else
			{
				pstmt.setString(1, "00");
				pstmt.setString(2, "ZZ");
			}
			pstmt.setString(3, itemCode);
			rs = pstmt.executeQuery();

			if(rs.next())
			{
				itmLen = rs.getDouble("ITEM_LEN");
				itmWidth = rs.getDouble("ITEM_WID");
				itmHeight = rs.getDouble("ITEM_HEIGHT");
				itemWeight = rs.getDouble("ITEM_WEIGHT");
				lotLen = rs.getDouble("LITEM_LEN");
				lotWidth = rs.getDouble("LITEM_WID");
				lotHeight = rs.getDouble("LITEM_HEIGHT");				
				packSize = rs.getDouble("SHIPSIZE");				
				lotWeight = rs.getDouble("LOT_WEIGHT");				
			}

			//packSize = (lotHeight * lotWidth * lotLen)/(itmLen * itmWidth * itmHeight);
			/*itemSize = Math.floor(itmLen * itmWidth * itmHeight);
			lotSize = Math.floor((lotHeight * lotWidth * lotLen));*/
			itemSize = itmLen * itmWidth * itmHeight;
			lotSize = lotHeight * lotWidth * lotLen;

			dataVolumeMap.put("PACK_SIZE", packSize);
			dataVolumeMap.put("ITEM_SIZE", itemSize);
			dataVolumeMap.put("LOT_SIZE", lotSize);
			dataVolumeMap.put("ITEM_WEIGHT", itemWeight);
			dataVolumeMap.put("PACK_WEIGHT", lotWeight);

			if(pstmt != null)
			{
				pstmt.close();
				pstmt = null;

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

			}



		} catch (Exception e) {
			// TODO: handle exception

			throw e;
		}
		finally
		{
			if(pstmt != null)
			{
				pstmt.close();
				pstmt = null;

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

			}
		}

		return dataVolumeMap;
	}

}