/*
    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 saleOrderFr = null;
	String saleOrderTo = null;
	String postOrderFg = null;
	String 	sql = null;
	String sDateTo = null;
	String sDateFr = null;
	double balStockQty = 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;
		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;
			}
			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,SORDITEM.EXP_LEV "
					   +"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 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' " 
					   +"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);
			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 - STOCK.ALLOC_QTY ) " 
		  					+"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 = '"+ rs.getString(6) + "'"
		     				+"AND STOCK.SITE_CODE = '"+ rs.getString(12) +"'" 
		     				+"AND (STOCK.QUANTITY - STOCK.ALLOC_QTY) > 0  ";
						rs1 = st.executeQuery(sql);
						if (rs1.next())
						{
							bappend = true;
							balStockQty = rs1.getDouble(1);
							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);
						}
						rs1.close();
					}
					
					//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)).append("\t");
						//SITE_CODE
						retTabSepStrBuff.append(rs.getString(12)).append("\t");
						//EXP_LEV
						retTabSepStrBuff.append(rs.getString(13)).append("\t");
						retTabSepStrBuff.append("  ").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;
		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 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();
					}
				
							
				}//inner for loop
				if(allocQty > 0)
				{
					System.out.println("if calling........");	
					errString = sorderAllocate(saleOrder, lineNo, itemCode, allocQty, expLev);
					if (errString != null && errString.trim().length() > 0)
					{
						System.out.println("errString :"+errString);
						return errString;
					}
				}
						
			}// 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;
				}
			}*/				
					
		}//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) 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;
			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);
				
					if (errString != null && errString.trim().length() > 0)
					{
						System.out.println("errString :::"+ errString );
						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();
						
					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.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);
					}
					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.executeUpdate(updateSql);
						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();
					
				}//end of if(lotQtyToBeAllocated > 0)
			}//end of while 
			rs.close();
			pstmtStock.clearParameters();
			pstmtStock.close();
			conn.commit();
		}
		catch(SQLException se)
		{
			System.out.println("SQLException :" + se);
			se.printStackTrace();
			errString = se.getMessage();
			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(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) 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);				
			}
		
			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>";
			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...............................");
		
			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);
			if(allocQty >= 0)
			{
				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("Exception :updateInvallocTrace :"  + 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();
	}

}