/*
    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 = "";

		Connection conn = null;
		ResultSet rs = null;
		ResultSet rs1 = null;
		PreparedStatement pstmt = null,pstmt1 = null ;
		Statement st = null;
		StringBuffer retTabSepStrBuff = new StringBuffer();
		double pendQty = 0;
		double allocQty = 0;	
		boolean bappend = false ;	

		try
		{
			ConnDriver connDriver = new ConnDriver();
			SimpleDateFormat simpleDateFormat = new SimpleDateFormat(genericUtility.getDBDateFormat());
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;

			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;
			}

			getDataSql = "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.QTY_DESP - 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 
				+"FROM SORDDET,SORDER,SORDITEM,CUSTOMER,ITEM  "
				+"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  "
				
				+" 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.QTY_DESP - 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) );
					if(itemCodeMap.containsKey(rs.getString(6)))
					{						
						balStockQty = Double.parseDouble(itemCodeMap.get(rs.getString(6)).toString());
						bappend = false ;

					}
					else
					{
						sql ="SELECT SUM(STOCK.QUANTITY - case when STOCK.ALLOC_QTY is null then 0 else STOCK.ALLOC_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 ) > 0  ";
						// 01/11/12 manoharan preparedstatement used instead of statement
						//rs1 = st.executeQuery(sql);
						pstmt1 = conn.prepareStatement(sql);
						pstmt1.setString(1,rs.getString(6) );
						pstmt1.setString(2,rs.getString(12));
						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)));
							}
							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)))
					{

						//CUST_CODE
						retTabSepStrBuff.append(rs.getString(1)).append("\t");
						//CUST_NAME
						retTabSepStrBuff.append(rs.getString(2)).append("\t");
						//LINE_NO
						retTabSepStrBuff.append(rs.getString(3)).append("\t");
						//SALE_ORDER
						retTabSepStrBuff.append(rs.getString(4)).append("\t");
						//DUE_DATE
						retTabSepStrBuff.append(rs.getTimestamp(5)).append("\t");
						dueDate = rs.getTimestamp(5);

						//ITEM_CODE
						retTabSepStrBuff.append(rs.getString(6)).append("\t");
						//DESCR
						retTabSepStrBuff.append(rs.getString(7)).append("\t");
						//QUANTITY
						retTabSepStrBuff.append(rs.getDouble(8)).append("\t");
						//PENDING_QUANTITY
						retTabSepStrBuff.append(rs.getDouble(9)).append("\t");
						//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
						if (bappend == true)
						{
							retTabSepStrBuff.append(balStockQty).append("\t");
						}
						else
						{
							retTabSepStrBuff.append("0").append("\t");
						}


						///////////////////////////////////////////////////////////////////////////////////					
						// 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");
						}
						else
						{
							retTabSepStrBuff.append("0").append("\t");
						}

						// 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");
						//SITE_CODE
						retTabSepStrBuff.append(rs.getString(12) == null?" ":rs.getString(12)).append("\t");
						//EXP_LEV
						retTabSepStrBuff.append(rs.getString(13) == null?" " :rs.getString(13) ).append("\t");

						//Hold Flag
						retTabSepStrBuff.append(rs.getString(14) == null?"N":rs.getString(14)).append("\t");// ADDED BY AKHILESH
						//qty allocated
						retTabSepStrBuff.append(rs.getDouble(10)).append("\t");// ADDED BY AKHILESH
						//Hold Qty
						retTabSepStrBuff.append(holdQty).append("\t");// added by Kunal on 22/10/12
						//Gtin Case
						retTabSepStrBuff.append(rs.getString(15)== null?" ":rs.getString(15)).append("\t");// added by Kunal on 22/10/12
						//Gtin Unit
						retTabSepStrBuff.append(rs.getString(16) == null?" ":rs.getString(16)).append("\t");// added by Kunal on 25/10/12
						

						retTabSepStrBuff.append("\n");
					}

				}while(rs.next());

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


		ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
		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++)
			{
				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"))
					{
						allocQty = Double.parseDouble(childNode.getFirstChild().getNodeValue());
					}
					if (childNodeName.equals("exp_lev"))
					{
						expLev = childNode.getFirstChild().getNodeValue();
					}
					if (childNodeName.equals("hold_flag"))
					{
						holdFlag = childNode.getFirstChild().getNodeValue();
					}


				}//inner for loop
				if(allocQty > 0)
				{
					System.out.println("if calling........");	
					errString = sorderAllocate(saleOrder, lineNo, itemCode, allocQty, expLev ,holdFlag);
					if (errString != null && errString.trim().length() > 0)
					{
						System.out.println("errString :"+errString);
						return errString;
					}
				}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 
			/*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 sorderAllocate(String saleOrder, String lineNo, String itemCode, double allocQty, String expLev ,String holdFlag) 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;
		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;
		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;

		String errString = "";
		String error = "";
		String errCode = "";
		Connection conn = null;
		PreparedStatement pstmtStock = null;
		PreparedStatement pstmtStockInsertSordAlloc = null;
		Statement st = null;
		ResultSet rs = null ;
		ResultSet rsSItem = null;

		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);


			getDataSql = "SELECT STOCK.LOT_NO,STOCK.LOT_SL,"
				+"STOCK.LOC_CODE, "
				+"STOCK.UNIT,  "
				+"(STOCK.QUANTITY - STOCK.ALLOC_QTY ) 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) > 0  "
				+"AND NOT EXISTS (SELECT 1 FROM INV_RESTR I WHERE I.INV_STAT = INVSTAT.INV_STAT AND I.REF_SER = 'S-DSP' )";
			pstmtStock = conn.prepareStatement(getDataSql);

			pstmtStock.setString(1,itemCode);
			pstmtStock.setString(2,siteCode);

			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);
				if (allocQty >= qtyAvailAlloc)
				{
					lotQtyToBeAllocated = qtyAvailAlloc;
				}
				else
				{
					lotQtyToBeAllocated = allocQty;
				}
				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);
					}
					sorditemSql = "SELECT ITEM_CODE,ITEM_CODE__ORD,UNIT, QTY_DESP, ITEM_REF,"
						+" QUANTITY - QTY_DESP PENDING_QUANTITY "
						+" 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);
					}
					rsSItem.close();
					st.close();	
					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();
				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();
	}

}