/*
    Window Name : w_sord_dealloc
    Dharmendra Debata
*/

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 StockDeallocationPrcEJB extends ProcessEJB implements SessionBean
public class StockDeallocationPrc extends ProcessEJB implements StockDeallocationPrcLocal,StockDeallocationPrcRemote //SessionBean
{
	String siteCode = null;
	String custCodeFr = null;
	String custCodeTo = null;

	String saleOrderFr = null;
	String saleOrderTo = null;
	String chgUser = null;
	String chgTerm = null;

	InvAllocTraceBean invallocTraceBean = new InvAllocTraceBean();
	GenericUtility genericUtility = GenericUtility.getInstance();
	ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
	//changed by rajendra on 02/11/07 to remove global connection
	//ConnDriver connDriver = new ConnDriver();
	CommonConstants commonConstants = new CommonConstants();
	HashMap invallocTraceMap = new HashMap();


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

		}
		catch (Exception e)
		{
			System.out.println("Exception :StockDeallocationPrcEJB :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 :StockDeallocationPrcEJB :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 resultString = "";
		String sqlDealloc ="";
		Connection conn = null;
		ResultSet rs=null,rsDealloc= null;
		PreparedStatement pstmt = null ,pstmtDeAlloc=null;
		Statement st = null;
		StringBuffer retTabSepStrBuff = new StringBuffer();
		ConnDriver connDriver = new ConnDriver();	 //added by rajendra on 02/11/07
		try
		{
			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);

	    	custCodeFr = genericUtility.getColumnValue("cust_code__from",headerDom);
	    	if ( custCodeFr == null || custCodeFr.trim().length() == 0 )
			{
				custCodeFr = "";
				System.out.println("Cust Code From is Null...");
			}
			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...");

			}
			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...");

			}

			saleOrderTo = genericUtility.getColumnValue("sale_order__to",headerDom);
			if ( saleOrderTo == null || saleOrderTo.trim().length() == 0 )
			{
				saleOrderTo = "";
				System.out.println("Sale Order To is Null...");

			}
         	if(saleOrderFr.trim().length()==0 && saleOrderTo.trim().length()==0 && custCodeFr.trim().length()==0 && custCodeTo.trim().length()==0)
         	{
         		errCode ="VTMRPPARM";
         		resultString = itmDBAccessEJB.getErrorString("", errCode, "", "", conn);
				System.out.println("resultString: "+resultString);
				return resultString;

         	}
			if(	saleOrderFr.trim().length()>0 && saleOrderTo.trim().length()>0)
			{
				getDataSql = "SELECT SORDALLOC.SALE_ORDER,SORDDET.LINE_NO,SORDITEM.EXP_LEV ,"
						   +" SORDITEM.ITEM_CODE,ITEM.DESCR,SORDITEM.QUANTITY,"
					       +" SORDITEM.QTY_ALLOC - SORDITEM.QTY_DESP  PENDING_QUANTITY,"
					       +" SORDALLOC.LOT_NO,SORDALLOC.LOT_SL,SORDALLOC.LOC_CODE "
					       +" FROM SORDDET,SORDALLOC,SORDITEM,CUSTOMER,ITEM, SORDER "
						   +" WHERE SORDER.SALE_ORDER = SORDDET.SALE_ORDER AND "
						   +"(SORDALLOC.SALE_ORDER = SORDDET.SALE_ORDER ) AND "
						   +"( SORDALLOC.LINE_NO = SORDDET.LINE_NO ) AND "
						   +"( SORDALLOC.EXP_LEV = SORDITEM.EXP_LEV ) AND "
						   +"( SORDITEM.SALE_ORDER = SORDDET.SALE_ORDER ) AND "
						   +"( SORDITEM.LINE_NO = SORDDET.LINE_NO ) AND "
						   +"( SORDITEM.ITEM_CODE = ITEM.ITEM_CODE ) AND "
						   +"( SORDER.CUST_CODE = CUSTOMER.CUST_CODE ) AND "
						   +" SORDDET.SITE_CODE = ? AND "
						   +" SORDER.SALE_ORDER >= ? AND "
						   +" SORDER.SALE_ORDER <= ? "
						   +"AND CASE WHEN SORDER.STATUS IS NULL THEN 'P' ELSE SORDER.STATUS end = 'P' "
						   +"AND CASE WHEN SORDDET.STATUS IS NULL THEN 'P' ELSE SORDDET.STATUS end <> 'C' "
						   +"AND CASE WHEN SORDALLOC.STATUS IS NULL THEN 'P' ELSE SORDALLOC.STATUS end = 'P' "
						   +"AND SORDITEM.QTY_ALLOC - SORDITEM.QTY_DESP > 0 "
						   +"AND SORDITEM.LINE_TYPE = 'I'"
						   +"ORDER BY SORDER.SALE_ORDER,SORDITEM.EXP_LEV,SORDITEM.ITEM_CODE__ORD";
				pstmt = conn.prepareStatement(getDataSql);
				pstmt.setString(1,siteCode);
				pstmt.setString(2,saleOrderFr);
				pstmt.setString(3,saleOrderTo);
				st = conn.createStatement();
				rs = pstmt.executeQuery();

				if(rs.next())
				{
					do
					{

						//SALE_ORDER
						retTabSepStrBuff.append(rs.getString(1)).append("\t");
						//LINE_NO
						retTabSepStrBuff.append(rs.getString(2)).append("\t");
						//EXP_LEV
						retTabSepStrBuff.append(rs.getString(3)).append("\t");
						//CUST_CODE
						retTabSepStrBuff.append(" ").append("\t");
						//ITEM_CODE
						retTabSepStrBuff.append(rs.getString(4)).append("\t");
						//ITEM_DESCR
						retTabSepStrBuff.append(rs.getString(5)).append("\t");
						//QUANTITY
						retTabSepStrBuff.append(rs.getDouble(6)).append("\t");
						//PENDING_QTY
						retTabSepStrBuff.append(rs.getDouble(7)).append("\t");
						//LOT_NO
						retTabSepStrBuff.append(rs.getString(8)).append("\t");
						//LOT_SL
						retTabSepStrBuff.append(rs.getString(9)).append("\t");
						//LOC_CODE
						retTabSepStrBuff.append(rs.getString(10)).append("\t");
						//ALLOC_TRANID
						retTabSepStrBuff.append(" ").append("\t");
						//ALLOC_LINENO
						retTabSepStrBuff.append(" ").append("\t");

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

						resultString = retTabSepStrBuff.toString();
						pstmt.clearParameters();

					}while(rs.next());

				}


			}
		 	else if(custCodeFr.trim().length()>0 && custCodeTo.trim().length()>0 )
			{

				sqlDealloc= "SELECT SORD_ALLOC.CUST_CODE,SORD_ALLOC_DET.ITEM_CODE, ITEM.DESCR, SORD_ALLOC_DET.QUANTITY,"
						   +"SORD_ALLOC_DET.QUANTITY - SORD_ALLOC_DET.DEALLOC_QTY PENDING_QUANTY ,"
						   +"SORD_ALLOC_DET.LOT_NO,SORD_ALLOC_DET.LOT_SL,SORD_ALLOC_DET.LOC_CODE , "
						   +"SORD_ALLOC_DET.TRAN_ID,SORD_ALLOC_DET.LINE_NO "
						   +"FROM SORD_ALLOC,SORD_ALLOC_DET, ITEM  "
						   +"WHERE SORD_ALLOC.TRAN_ID = SORD_ALLOC_DET.TRAN_ID "
						   +"AND SORD_ALLOC_DET.ITEM_CODE = ITEM.ITEM_CODE "
						   +"AND SORD_ALLOC.SALE_ORDER IS NULL "
						   +"AND SORD_ALLOC.SITE_CODE= ? "
						   +"AND SORD_ALLOC.CUST_CODE >=? AND SORD_ALLOC.CUST_CODE <=? "
		   				   +"AND SORD_ALLOC_DET.QUANTITY -(CASE WHEN SORD_ALLOC_DET.DEALLOC_QTY IS NULL THEN 0  ELSE SORD_ALLOC_DET.DEALLOC_QTY END) > 0 ";

		  		pstmtDeAlloc = conn.prepareStatement(sqlDealloc);
		  		pstmtDeAlloc.setString(1,siteCode);
				pstmtDeAlloc.setString(2,custCodeFr);
				pstmtDeAlloc.setString(3,custCodeTo);
				rsDealloc = pstmtDeAlloc.executeQuery();
				if(rsDealloc.next())
				{

					do
					{

						//SALE_ORDER
						retTabSepStrBuff.append(" ").append("\t");
						//LINE_NO
						retTabSepStrBuff.append(" ").append("\t");
						//EXP_LEV
						retTabSepStrBuff.append(" ").append("\t");
						//CUST_CODE
						retTabSepStrBuff.append(rsDealloc.getString(1)).append("\t");
						//ITEM_CODE
						retTabSepStrBuff.append(rsDealloc.getString(2)).append("\t");
						//ITEM_DESCR
						retTabSepStrBuff.append(rsDealloc.getString(3)).append("\t");
						//QUANTITY
						retTabSepStrBuff.append(rsDealloc.getDouble(4)).append("\t");
						//PENDING_QTY
						retTabSepStrBuff.append(rsDealloc.getDouble(5)).append("\t");
						//LOT_NO
						retTabSepStrBuff.append(rsDealloc.getString(6)).append("\t");
						//LOT_SL
						retTabSepStrBuff.append(rsDealloc.getString(7)).append("\t");
						//LOC_CODE
						retTabSepStrBuff.append(rsDealloc.getString(8).trim()).append("\t");
						//ALLOC_TRANID
						retTabSepStrBuff.append(rsDealloc.getString(9).trim()).append("\t");
						//ALLOC_LINENO
						retTabSepStrBuff.append(rsDealloc.getString(10)).append("\t");

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

						resultString = retTabSepStrBuff.toString();


						pstmtDeAlloc.clearParameters();

					}while(rsDealloc.next());
				}


				pstmtDeAlloc.close();
				rsDealloc.close();
			}


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

			try
			{
				cleanup();
				retTabSepStrBuff = null;

				if(conn != null)
				{
					if(pstmt != null)
					{
						pstmt.close();
						pstmt=null;
					}
					if(rs != null)
					{
						rs.close();
					}
					if(pstmtDeAlloc !=null)
					{
						pstmtDeAlloc.close();
						pstmtDeAlloc = null;
					}
					if(rsDealloc !=null)
					{
						rsDealloc.close();
					}
					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 :StockDeallocationPrcEJB :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;
		String saleOrder = " ";
		String custCode=" ";
		String expLev = " " ;
		String itemCode = " ";
		String lineNo = " ";
		String locCode = " ";
		String lotSl= " ";
		String lotNo = " " ;
		String tranId=" ";
		String allocLineno=" ";
		double DeallocQty = 0;
		double quantity = 0;
		String errString = " ";

		Connection conn = null;

		int parentNodeListLength = 0;
		int childNodeListLength = 0;
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;

		PreparedStatement pstmt = null;
		Statement st = null;

	    ConnDriver connDriver = new ConnDriver();	//added by rajendra on 02/11/07
		ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
		try
		{
			chgUser = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginCode");
			chgTerm = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "termId");

			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("quantity"))
					{
						quantity = Double.parseDouble(childNode.getFirstChild().getNodeValue());
						System.out.print("quantity::::"+ quantity );
					}

					if (childNodeName.equals("sale_order"))
					{
						saleOrder = childNode.getFirstChild().getNodeValue();
						System.out.print("saleOrder::::"+ saleOrder );

					}

					if (childNodeName.equals("line_no"))
					{
						lineNo = childNode.getFirstChild().getNodeValue();
						System.out.print("lineNo::::"+ lineNo );
					}

					if (childNodeName.equals("item_code"))
					{
						itemCode = childNode.getFirstChild().getNodeValue();
						System.out.print("itemCode::::"+ itemCode );
					}

					if (childNodeName.equals("dealloc_qty"))
					{
						DeallocQty = Double.parseDouble(childNode.getFirstChild().getNodeValue());
						System.out.print("DeallocQty::::"+ DeallocQty );
					}
					if (childNodeName.equals("exp_lev"))
					{
						expLev = childNode.getFirstChild().getNodeValue();
						System.out.print("expLev::::"+ expLev );
					}
					if (childNodeName.equals("cust_code"))
					{
						custCode = childNode.getFirstChild().getNodeValue();
						System.out.print("custCode::::"+ custCode );
					}
					if (childNodeName.equals("lot_sl"))
					{
						lotSl = childNode.getFirstChild().getNodeValue();
						System.out.print("lineNo::::"+ lineNo );
					}
					if (childNodeName.equals("lot_no"))
					{
						lotNo = childNode.getFirstChild().getNodeValue();
						System.out.print("lotSl::::"+ lotSl );
					}
					if (childNodeName.equals("loc_code"))
					{
						locCode = childNode.getFirstChild().getNodeValue();
						System.out.print("locCode::::"+ locCode );
					}
					if (childNodeName.equals("tranid"))
					{
						tranId = childNode.getFirstChild().getNodeValue();
						System.out.print("alloc_tranid::::"+ tranId );
					}
					if (childNodeName.equals("alloc_lineno"))
					{
						allocLineno = childNode.getFirstChild().getNodeValue();
						System.out.print("allocLineno::::"+ allocLineno );
					}

				}//inner for loop
				if(DeallocQty > 0)
				{
					System.out.println("if calling........");
					errString = sorderDeAllocate(saleOrder, lineNo, itemCode, DeallocQty, expLev,custCode,lotSl,lotNo,locCode,tranId,allocLineno);
					if (errString != null && errString.trim().length() > 0)
					{
						System.out.println("errString :"+errString);
						return errString;
					}
				}

			}// out for loop


		}//try end
		catch(Exception e)
		{
			System.out.println("Exception :StockDeallocationPrcEJB :process(String xmlString2, String xmlString2, String windowName, String xtraParams):" + e.getMessage() + ":");
			errString = e.getMessage();
			e.printStackTrace();
			return errString ;
		}
		finally
		{
			System.out.println("Closing Connection....");
			try
			{
				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 sorderDeAllocate(String saleOrder, String lineNo, String itemCode, double DeallocQty, String expLev,String custCode,String lotSl,String lotNo,String locCode,String tranId,String allocLineno ) throws ITMException
	{
		System.out.println("updateSOrder calling ..............");
		String updateSorditem = null,updateSordAllocDet=null;
		String updateSql= null,DelQtyAlloc= null,SqlQtyAlloc= null ;
		String errString = "";
		String error = "";
		String errCode = "";
		Connection conn = null;
		PreparedStatement pstmtDeAlloc=null;
		Statement st = null;
		ResultSet rsQtyAlloc=null;
		ConnDriver connDriver = new ConnDriver();	 //added by manoharan
		try
		{
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			st = conn.createStatement();
			invallocTraceMap.put("ref_ser","D-ALOC");
			invallocTraceMap.put("site_code",siteCode);
			invallocTraceMap.put("item_code",itemCode);
			invallocTraceMap.put("loc_code",locCode);
			invallocTraceMap.put("lot_no",lotNo);
			invallocTraceMap.put("lot_sl",lotSl);

			invallocTraceMap.put("alloc_qty",new Double(-1 * DeallocQty));

			System.out.print("DeallocQty = " + (-1*DeallocQty));
			invallocTraceMap.put("chg_user",chgUser);
			invallocTraceMap.put("chg_term",chgTerm);
			invallocTraceMap.put("chg_win","W_SORD_DEALLOC");

			if (DeallocQty > 0 && saleOrder.trim().length()>0)
			{
				invallocTraceMap.put("ref_id",saleOrder);
				invallocTraceMap.put("ref_line",lineNo);
				errString = invallocTraceBean.updateInvallocTrace(invallocTraceMap,conn);

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

				updateSorditem ="UPDATE SORDITEM  SET QTY_ALLOC = QTY_ALLOC - " + new Double(DeallocQty).toString()
							   +" WHERE SALE_ORDER = '" + saleOrder + "' "
							   +" AND LINE_NO = '" + lineNo + "' "
							   +" AND EXP_LEV = '" + expLev + "' ";

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

				updateSql = " UPDATE SORDALLOC SET QTY_ALLOC =  QTY_ALLOC - " + new Double(DeallocQty).toString()
							  + " WHERE SALE_ORDER = '" + saleOrder + " ' "
							  + " AND LINE_NO = '" + lineNo + "' "
							  + " AND EXP_LEV = '" + expLev + "' "
							  + " AND ITEM_CODE = '" + itemCode + "' "
							  + " AND LOT_NO = '" + lotNo + "' "
							  + " AND LOT_SL = '" + lotSl + "' "
							  + " AND LOC_CODE = '" + locCode + "' " ;
				System.out.println("updateSql:::>>>>"+ updateSql);
				st.executeUpdate(updateSql);
				System.out.println("UPDATE  SUCCESS FOR SORDALLOC....");
				SqlQtyAlloc = "SELECT QTY_ALLOC FROM SORDALLOC "
							+ " WHERE SALE_ORDER = '" + saleOrder + " ' "
							+ " AND LINE_NO = '" + lineNo + "' "
							+ " AND EXP_LEV = '" + expLev + "' "
							+ " AND ITEM_CODE = '" + itemCode + "' "
							+ " AND LOT_NO = '" + lotNo + "' "
							+ " AND LOT_SL = '" + lotSl + "' "
							+ " AND LOC_CODE = '" + locCode + "' " ;
				rsQtyAlloc  = st.executeQuery(SqlQtyAlloc);
				if (rsQtyAlloc.next())
				{
					System.out.println("Updated Allocated Qty :::"+rsQtyAlloc.getDouble(1));
					if(rsQtyAlloc.getDouble(1)<=0)
					{
						DelQtyAlloc = "DELETE FROM SORDALLOC"
									+ " WHERE SALE_ORDER = '" + saleOrder + " ' "
									+ " AND LINE_NO = '" + lineNo + "' "
									+ " AND EXP_LEV = '" + expLev + "' "
									+ " AND ITEM_CODE = '" + itemCode + "' "
									+ " AND LOT_NO = '" + lotNo + "' "
									+ " AND LOT_SL = '" + lotSl + "' "
									+ " AND LOC_CODE = '" + locCode + "' " ;
						System.out.println("DelQtyAlloc:::"+DelQtyAlloc);
						st.executeUpdate(DelQtyAlloc);
						System.out.println("Delete completed ");
					}
				}
				rsQtyAlloc.close();
			}
			//For saleOrder is null and you have cust_code
			else if(DeallocQty > 0 && tranId.trim().length()>0)
			{
				invallocTraceMap.put("ref_id",tranId);
				System.out.println("alloc_tranid::----"+tranId);
				invallocTraceMap.put("ref_line",allocLineno);
				System.out.println("siteCode::"+siteCode+"itemCode:"+itemCode+"locCode::"+locCode +"lotNo:"+lotNo+"lotSl:"+"QtyToBeDeallocated::"+(-1*DeallocQty)+"alloc_tranid::"+tranId+"allocLineno::"+allocLineno);
				errString = invallocTraceBean.updateInvallocTrace(invallocTraceMap,conn);
				if (errString != null && errString.trim().length() > 0)
				{
					System.out.println("errString :::"+ errString );
					return errString;
				}
				updateSordAllocDet= " UPDATE SORD_ALLOC_DET SET DEALLOC_QTY = DEALLOC_QTY + "+  new Double(DeallocQty).toString()
									+" WHERE SORD_ALLOC_DET.TRAN_ID='"+ tranId +"'"
									+" AND SORD_ALLOC_DET.LINE_NO ="+ allocLineno ;
				System.out.println("updateSordAllocDet::"+ updateSordAllocDet);
				st.executeUpdate(updateSordAllocDet);
				System.out.println("updateSordAllocDet completed successfully");
			}
			st.close();

			conn.commit();
		}
		catch(SQLException se)
		{
			System.out.println("SQLException :" + se);
			se.printStackTrace();
			return errString;
		}

		catch(Exception e)
		{
			System.out.println("Exception :" + e);
			errString = e.getMessage();
			e.printStackTrace();
			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()
	{
		invallocTraceMap.clear();
	}

}