/**
 DEVELOPED BY RITESH TIWARI ON 02/11/13 
 PURPUSE : WORK ORDER STOCK TRANSFER (MF3GSUP004)
 */
package ibase.webitm.ejb.mfg;
import ibase.system.config.AppConnectParm;
import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.ejb.MasterStatefulLocal;
import ibase.webitm.ejb.ProcessEJB;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;
import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.ejb.Stateless;
import javax.naming.InitialContext;
import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
//import ibase.webitm.ejb.dis.InvAllocTrace;
import ibase.webitm.ejb.dis.adv.StockTransferConf;

@Stateless
public class WoTransferPrc extends ProcessEJB implements WoTransferPrcLocal,WoTransferPrcRemote 
{
	static
	{
		System.out.println("-- WoTransferPrc called -- ");
	}
	GenericUtility genericUtility = GenericUtility.getInstance();
	ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
	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 :WoTransferPrcEJB :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 errString = "";
		String sql= "",worder="",locCodeTo="",batchQuantity="";
		String resultString = "",errCode="";
		String invstat="",invstat1="",tranIdInvAlloc="";
		int cnt=0;
		Connection conn = null;
		ResultSet rs = null,rs1= null;
		double issQty=0.0,allocQty=0,allocatedQty=0.0,integralQty=0.0;
		PreparedStatement pstmt = null,pstmt1=null;
		double quantity=0.0;
		StringBuffer retTabSepStrBuff = new StringBuffer();
		MfgCommon mfgComm  = new MfgCommon();
		DecimalFormat df = new DecimalFormat("0.000");

		retTabSepStrBuff = new StringBuffer("<?xml version = \"1.0\"?>");
		retTabSepStrBuff.append("<DocumentRoot>");
		retTabSepStrBuff.append("<description>").append("Datawindow Root").append("</description>");
		retTabSepStrBuff.append("<group0>");
		retTabSepStrBuff.append("<description>").append("Group0 description").append("</description>");
		retTabSepStrBuff.append("<Header0>");

		try
		{
			System.out.println("WoTransferPrc getData called :: ::");
			ConnDriver connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			worder = checkNull(genericUtility.getColumnValue("work_order", headerDom));
			locCodeTo = checkNull(genericUtility.getColumnValue("loc_code__to", headerDom));
			batchQuantity = checkNull(genericUtility.getColumnValue("batch_quantity", headerDom));

			if(worder == null || worder.trim().length()  <= 0)
			{
				System.out.println("worder:: is null "+ worder);
				errString = itmDBAccessEJB.getErrorString("","VTWKORD1    ","","",conn);
				return errString;
			}
			if(locCodeTo == null || locCodeTo.trim().length()  <= 0)
			{
				System.out.println("loc code to is null:: "+ worder);
				errString = itmDBAccessEJB.getErrorString("","VTLOCCDTO2 ","","",conn);
				return errString;
			}
			if(worder != null && worder.trim().length()  > 0)
			{
				sql = "select count(*) from workorder where work_order = ?  and status not in ('F') ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,worder);
			    rs = pstmt.executeQuery();
			    if(rs.next())
			    {
			    	cnt = rs.getInt(1);
			    }
			    if(rs != null)
			    	rs.close();
			    rs = null;
			    if(pstmt != null)
			    	pstmt.close();
			    pstmt = null;
			    if(cnt == 0)
			    {
			    	errString = itmDBAccessEJB.getErrorString("","VPWOINV    ","","",conn);
					return errString;
			    }
			}
			sql = "select quantity from workorder where work_order = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,worder);
		    rs = pstmt.executeQuery();
		    if(rs.next())
		    {
		    	quantity = rs.getDouble(1);
		    }
		    if(rs != null)
		    	rs.close();
		    rs = null;
		    if(pstmt != null)
		    	pstmt.close();
		    pstmt = null;
		    if(batchQuantity != null && batchQuantity.trim().length()  > 0)
			{
			    if(Double.parseDouble(batchQuantity) > quantity)
			    {
			    	errString = itmDBAccessEJB.getErrorString("","VPBATNOT    ","","",conn);
					return errString;
			    }
			}
			if(locCodeTo != null && locCodeTo.trim().length()  > 0)
			{
				cnt=0;
				invstat = mfgComm.getEnvMfg("999999","SUB_ST_INVSTAT", conn); 
				invstat1 = mfgComm.getEnvMfg("999999","MAIN_ST_INVSTAT", conn); 
				if(invstat.equalsIgnoreCase(invstat1))
				{
					errString = itmDBAccessEJB.getErrorString("","VTDISPVAR    ","","",conn);
					return errString;
				}
				sql = "select count(1) from location where loc_code  = ? and inv_stat = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,locCodeTo);
				pstmt.setString(2,invstat);
			    rs = pstmt.executeQuery();
			    if(rs.next())
			    {
			    	cnt = rs.getInt(1);
			    }
			    if(rs != null)
			    	rs.close();
			    rs = null;
			    if(pstmt != null)
			    	pstmt.close();
			    pstmt = null;
			    if(cnt == 0)
			    {
			    	errString = itmDBAccessEJB.getErrorString("","VTLOCCDTO1    ","","",conn);
					return errString;
			    }
			}
			sql = "select tran_id from inv_allocate where WORK_ORDER = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,worder);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				tranIdInvAlloc = checkNull(rs.getString("tran_id"));
				
			}
			if(rs != null)
				rs.close();
			rs = null;
			if(pstmt != null)
				pstmt.close();
			pstmt = null;
			
			invstat1 = mfgComm.getEnvMfg("999999","MAIN_ST_INVSTAT", conn); 
		 
			System.out.println("::: XML Cretation::::");
			sql = "select tran_id,line_no,item_code,site_code,lot_no,lot_sl,remarks,loc_code,reas_code,quantity,exp_lev,potency_adj from inv_alloc_det where tran_id = ? " +
					" and loc_code in(select loc_code from location where inv_stat = ?) ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,tranIdInvAlloc);
			pstmt.setString(2,invstat1);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				do
				{
					issQty = rs.getDouble("quantity");
					
					sql = "select SUM(quantity) from stock_transfer_det where tran_id in (select tran_id from stock_transfer " +
							   "where  ref_id__for = ?) and item_code = ? ";
					pstmt1 = conn.prepareStatement(sql);
					pstmt1.setString(1, worder);
					pstmt1.setString(2,  rs.getString("item_code"));
					rs1 = pstmt1.executeQuery();
					if ( rs1.next() )
					{
						allocatedQty = rs1.getDouble(1);
					}
					if(rs1 != null)
						rs1.close();
					rs1 = null;
					if(pstmt1 != null)
						pstmt1.close();
					pstmt1 = null;
					if(quantity > 0)
					{
						//allocQty = ((issQty-allocatedQty)/quantity) * Double.parseDouble(batchQuantity);
						allocQty = (issQty/quantity) * Double.parseDouble(batchQuantity);
					}
					System.out.println(" ALLOCATED QUANTITY ::"+allocQty);

					//if(allocQty > 0)
					//{
					retTabSepStrBuff.append("<Detail2>");

					retTabSepStrBuff.append("<tran_id>").append("<![CDATA[" + rs.getString("tran_id") +"]]>").append("</tran_id>");
					
					retTabSepStrBuff.append("<line_no>").append("<![CDATA[" + rs.getInt("line_no") +"]]>").append("</line_no>");
					
					retTabSepStrBuff.append("<site_code>").append("<![CDATA[" + rs.getString("site_code") +"]]>").append("</site_code>");
					
					retTabSepStrBuff.append("<item_code>").append("<![CDATA[" + rs.getString("item_code") +"]]>").append("</item_code>");
					
					retTabSepStrBuff.append("<lot_no>").append("<![CDATA[" + rs.getString("lot_no") +"]]>").append("</lot_no>");
					
					retTabSepStrBuff.append("<lot_sl>").append("<![CDATA[" + rs.getString("lot_sl") +"]]>").append("</lot_sl>");
					
					retTabSepStrBuff.append("<actual_qty>").append("<![CDATA[" + df.format(issQty) +"]]>").append("</actual_qty>");
					
					sql = "select INTEGRAL_QTY from item where item_code = ?";
					pstmt1 = conn.prepareStatement(sql);
					pstmt1.setString(1,  rs.getString("item_code"));
					rs1 = pstmt1.executeQuery();
					if ( rs1.next() )
					{
						integralQty = rs1.getDouble(1);
					}
					if(rs1 != null)
						rs1.close();
					rs1 = null;
					if(pstmt1 != null)
						pstmt1.close();
					pstmt1 = null;
					
					if(allocQty > integralQty ){
						if(allocQty % integralQty != 0)
						{
							retTabSepStrBuff.append("<quantity>").append("<![CDATA[" + df.format((integralQty - (allocQty % integralQty) + allocQty)) +"]]>").append("</quantity>");
						}
						else
						{
							retTabSepStrBuff.append("<quantity>").append("<![CDATA[" + df.format(allocQty) +"]]>").append("</quantity>");
						}
					}else{
						retTabSepStrBuff.append("<quantity>").append("<![CDATA[" + df.format(integralQty) +"]]>").append("</quantity>");
					}
					retTabSepStrBuff.append("<allocated_qty>").append("<![CDATA[" + df.format(allocatedQty) +"]]>").append("</allocated_qty>");

					retTabSepStrBuff.append("<remarks>").append("<![CDATA[" + rs.getString("remarks") +"]]>").append("</remarks>");
					
					retTabSepStrBuff.append("<reas_code>").append("<![CDATA[" + checkNull(rs.getString("reas_code")) +"]]>").append("</reas_code>");
					
					retTabSepStrBuff.append("<loc_code>").append("<![CDATA[" + rs.getString("loc_code") +"]]>").append("</loc_code>");
					
					retTabSepStrBuff.append("<exp_lev>").append("<![CDATA[" + rs.getString("exp_lev") +"]]>").append("</exp_lev>");
					
					retTabSepStrBuff.append("<potency_adj>").append("<![CDATA[" + rs.getDouble("potency_adj") +"]]>").append("</potency_adj>");
					
					retTabSepStrBuff.append("<loc_code__to>").append("<![CDATA[" +locCodeTo +"]]>").append("</loc_code__to>");
					
					retTabSepStrBuff.append("<work_order>").append("<![CDATA[" + worder +"]]>").append("</work_order>");

					retTabSepStrBuff.append("</Detail2>");
					//}
					
				}while(rs.next());
				
			retTabSepStrBuff.append("</Header0>");
			retTabSepStrBuff.append("</group0>");
			retTabSepStrBuff.append("</DocumentRoot>");
			resultString = retTabSepStrBuff.toString();
			pstmt.clearParameters();
			}
			else
			{
				errCode ="VTNOREC1";
			}
			if (!errCode.equals(""))
			{
				resultString = itmDBAccessEJB.getErrorString("", errCode, "", "", conn);
				System.out.println("resultString: "+resultString);
			}
			System.out.println("ResultString....." + resultString);

		}
		catch (SQLException e)
		{
			System.out.println("SQLException :WoTransferPrcEJB :getData(Document headerDom, Document detailDom, String windowName, String xtraParams):" + e.getMessage() + ":");
			throw new ITMException(e);
		}
		catch (Exception e)
		{
			System.out.println("Exception :WoTransferPrcEJB :getData(Document headerDom, Document detailDom, String windowName, String xtraParams):" + e.getMessage() + ":");
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				retTabSepStrBuff = null;
				if(conn != null)
				{					
					if(pstmt != null)
					{
						pstmt.close();
						pstmt=null;
					}
					if(rs != null){
						rs.close();
						rs = null;
					}
					conn.close();
					conn = null;
				}
			}
			catch(Exception e)
			{
				errString = e.getMessage();
				e.printStackTrace();
				throw new ITMException(e);
			}
		}		
		return resultString;	
	}
	
	
	public String process() throws RemoteException,ITMException
	{
		return "";
	}
	public String process(String xmlString, String xmlString2, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		Document detailDom = null;
		Document headerDom = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		String retStr = "";
		try
		{				
			System.out.println("xmlString[process]::::::::::;;;"+xmlString);
			System.out.println("xmlString2[process]::::::::::;;;"+xmlString2);
			System.out.println("windowName[process]::::::::::;;;"+windowName);
			System.out.println("xtraParams[process]:::::::::;;;"+xtraParams);
		}
		catch(Exception e)
		{
			System.out.println(e.getMessage());
			e.printStackTrace();
		}
		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 :WoTransferPrc :process(String xmlString, String xmlString2, String windowName, String xtraParams):" + e.getMessage() + ":");
			e.printStackTrace();
		}
		return retStr;
	
	} //end of confirm method
	public String process(Document headerDom, Document detailDom, String windowName,String xtraParams) throws RemoteException, ITMException 
	{
		System.out.println("WoTransferPrc process called........");
		String allocQty="",itemCode="";
		String errString = null,sql="";
		String siteCode="",explev="",lineno="",lotno="",lotsl="",locCodeFr="",reasCode="";
		String  worder = "",locCodeTo="",remarks="",tranId="";
		Connection connOne = null;
		ConnDriver connDriver = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		double actualQty =0.0,trfedQty = 0.0,allocQty2=0.0;
		ITMDBAccessEJB itmDBAccessEJB = null;
		String childNodeName = "";
		Node parentNode = null;
		NodeList childNodeList = null;
		NodeList parentNodeList = null;
		int parentNodeListLength = 0;
		int childNodeListLength = 0;
		Node childNode = null;
		int elements = 0;
		boolean allocqtyflag = false;
		ArrayList list = new ArrayList();
		DecimalFormat df = new DecimalFormat("0.000");
		try
		{
			genericUtility = new GenericUtility();
			itmDBAccessEJB = new ITMDBAccessEJB();
			connDriver = new ConnDriver();
			connOne = connDriver.getConnectDB("DriverITM");
			connDriver = null;            			
			connOne.setAutoCommit(false);
			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("tran_id"))
					{
						tranId = childNode.getFirstChild().getNodeValue();
					}
					if (childNodeName.equals("line_no"))
					{
						lineno = childNode.getFirstChild().getNodeValue();
					}

					if (childNodeName.equals("site_code"))
					{
						siteCode = childNode.getFirstChild().getNodeValue();
					}

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

					if (childNodeName.equals("lot_no"))
					{
						lotno = childNode.getFirstChild().getNodeValue();
					}
					if (childNodeName.equals("lot_sl"))
					{
						lotsl = childNode.getFirstChild().getNodeValue();
					}
					if (childNodeName.equals("quantity"))
					{
						allocQty = childNode.getFirstChild().getNodeValue();
						allocQty2 = Double.parseDouble(childNode.getFirstChild().getNodeValue());
					}
					if (childNodeName.equals("reas_code"))
					{
						if(childNode !=null)
						{
							if(childNode.getFirstChild() != null)
							{
								reasCode = childNode.getFirstChild().getNodeValue();
							}
						}
						
					}if (childNodeName.equals("loc_code"))
					{
						locCodeFr = childNode.getFirstChild().getNodeValue();
					}if (childNodeName.equals("exp_lev"))
					{
						explev = childNode.getFirstChild().getNodeValue();
					}
					if (childNodeName.equals("loc_code__to"))
					{
						locCodeTo = childNode.getFirstChild().getNodeValue();
					}
					if (childNodeName.equals("work_order"))
					{
						worder = childNode.getFirstChild().getNodeValue();
					}
					if (childNodeName.equals("remarks"))
					{
						remarks = childNode.getFirstChild().getNodeValue();
					}
					if (childNodeName.equals("actual_qty"))
					{
						actualQty = Double.parseDouble(childNode.getFirstChild().getNodeValue());
					}
					if (childNodeName.equals("allocated_qty"))
					{
						trfedQty = Double.parseDouble(childNode.getFirstChild().getNodeValue());
					}
				}//inner for loop
				System.out.println(" amount values "+actualQty+"::"+trfedQty+"::"+allocQty2);
				if(Double.parseDouble(df.format(actualQty - trfedQty)) >= Double.parseDouble(df.format(allocQty2)))
				{
					errString = allocStockToWo(lineno,siteCode,itemCode,lotno,lotsl,allocQty,reasCode,locCodeFr,explev,locCodeTo,worder,remarks,tranId,xtraParams,connOne);
					System.out.println("err String from STOCKTRANS ="+errString);
				}
				else
				{
					allocqtyflag = true;
					list.add(lineno);
					list.add(itemCode);
					elements ++;
					System.out.println(" QUANTITY TO BE ALLOCATED NOT VALID");
				}

			}// out for loop 
			if(allocqtyflag == true)
			{
				allocqtyflag= false;
				errString = itmDBAccessEJB.getErrorString("","VTALCQTY","","",connOne);	
				String begPart = errString.substring( 0, errString.indexOf("<trace>") + 7 );
				String endPart = errString.substring( errString.indexOf("</trace>"));
				String mainStr = begPart + "Following error has occured\n" ;
				if(elements > 0 )
				{ 
					mainStr	= mainStr + "Invalid allocate quantity.\n";
				}
				for(int i = 0; i < elements * 2; i++ )
				{
					if( i > 0)
					{
						i -= 1;
					}
					mainStr = mainStr + 
							"line no :"+list.get(i++)+ ",item code :"+ list.get(i++) + "\n" ;
				}
				mainStr = mainStr +  endPart;	
				errString = mainStr;
				begPart =null;
				endPart =null;
				mainStr =null;	
				return errString;
			}
			if((errString != null ) &&  (errString.indexOf("Success") > -1 || errString.indexOf("CONFSUCC") >-1))
			{
				connOne.commit();
				System.out.println("--process completed--");
				errString = itmDBAccessEJB.getErrorString("","VPSUCC1    ","","",connOne);
			}
			else if(errString != null && errString.indexOf("VTITRF") > -1)
			{
				System.out.println("Item code can not we same)]]");;
				errString = itmDBAccessEJB.getErrorString("","VTITRF    ","","",connOne);
			}
			else 
			{
				connOne.rollback();
				System.out.println("--process not completed--");
				errString = itmDBAccessEJB.getErrorString("","VTPRCERR ","","",connOne);
			}
		}
		catch( Exception e)
		{			
				try 
				{
					connOne.rollback();
				} 
				catch (SQLException ex) 
				{
					Logger.getLogger(WoTransferPrc.class.getName()).log(Level.SEVERE, null, ex);
				}
				e.printStackTrace();
		}
		finally
		{		
				try
				{
					if(errString != null && errString.trim().length() > 0)
					{
						if(errString.indexOf("VPSUCC1") > -1 )
						{
							connOne.commit();
							System.out.println("--transaction commited--");
						}
						else
						{
							connOne.rollback();
							System.out.println("--transaction rollback--");
						}
					}
					if(pstmt != null)
					{
						pstmt.close();
						pstmt = null;					
					}		
					if( connOne != null && ! connOne.isClosed() )
					{
						connOne.close();
						connOne = null;
					}
					
					if(rs != null)
					{
						rs.close();
						rs = null;
					}
				}
				catch(Exception e)
				{
					System.out.println("Exception : "+e);e.printStackTrace();
					throw new ITMException(e);
				}
			}
		return errString;
	}
	private String allocStockToWo(String lineno,String siteCode,String itemCode,String lotno,String lotsl,String allocQty,String reasCode,String locCodeFr,String explev,String locCodeTo,String worder,String remarks,String tranIdInvAlloc,String xtraParams,Connection connOne) throws RemoteException, ITMException
	{	
		String  userId = "";
		String  termId = "";
		String sql = "",sql1="", acctCodeInv1 = "",cctrCodeInv1="";
		String itemSer="",tranType="";
		Date orddate = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		GenericUtility genericUtility = null;
		Timestamp currDate = null;
		String retString  = null;
		String empCode = "";
		String xmlString = "";
		StringBuffer xmlBuff = null;
		try   
		{
			System.out.println("@@@@@@@ wostock trans.  function called::");
			xmlBuff = new StringBuffer();
			genericUtility = GenericUtility.getInstance();
			SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
			SimpleDateFormat sdf1 = new SimpleDateFormat(genericUtility.getDBDateFormat());
			currDate =  java.sql.Timestamp.valueOf(sdf1.format(new java.util.Date()).toString() + " 00:00:00.0");
			userId = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginCode");  System.out.println("--login code--"+userId);
			termId =  genericUtility.getValueFromXTRA_PARAMS(xtraParams, "termId"); System.out.println("--term id--"+termId);
			
			sql = "SELECT EMP_CODE FROM USERS WHERE CODE = ? ";
			pstmt = connOne.prepareStatement(sql);
			pstmt.setString(1, userId);
			rs = pstmt.executeQuery();
			if ( rs.next() )
			{
				empCode = rs.getString("EMP_CODE")== null ?"":rs.getString("EMP_CODE");
			}
			if(rs != null)
				rs.close();
			rs = null;
			if(pstmt != null)
				pstmt.close();
			pstmt = null;
			
			sql = "select item_code,site_code,bom_code,lot_no,lot_sl,ref_no,ord_date " +
				  " from workorder where work_order = ? ";
					
			pstmt = connOne.prepareStatement(sql);
			pstmt.setString(1,worder);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				orddate = rs.getDate("ord_date");
			}
			if(rs != null)
				rs.close();
			rs = null;
			if(pstmt != null)
				pstmt.close();
			pstmt = null;
			
			sql = "select item_ser from item where item_code = ? ";
			pstmt = connOne.prepareStatement(sql);
			pstmt.setString(1,itemCode);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				itemSer = rs.getString("item_ser");
				
			}
			if(rs != null)
				rs.close();
			rs = null;
			if(pstmt != null)
				pstmt.close();
			pstmt = null;
		
			sql1 = "select acct_code__inv, cctr_code__inv  from stock where item_code = ? and site_code = ?  and loc_code = ? and lot_no = ? and lot_sl = ? ";
			pstmt = connOne.prepareStatement(sql1);
			pstmt.setString(1,itemCode);
			pstmt.setString(2,siteCode);
			pstmt.setString(3,locCodeFr);
			pstmt.setString(4,lotno);
			pstmt.setString(5,lotsl);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				 acctCodeInv1 = rs.getString("acct_code__inv")==null?"1440":rs.getString("acct_code__inv");
				 cctrCodeInv1 = rs.getString("cctr_code__inv")==null?"H651":rs.getString("cctr_code__inv");
			}
			if(rs != null)
				rs.close();
			rs = null;
			if(pstmt != null)
				pstmt.close();
			pstmt = null;
			
			    System.out.println("::: XML Cretation::::");
			
				xmlBuff.append("<?xml version='1.0' encoding='ISO-8859-1'?>\n");
				xmlBuff.append("<DocumentRoot>");
				xmlBuff.append("<description>").append("Datawindow Root").append("</description>");
				xmlBuff.append("<group0>");
				xmlBuff.append("<description>").append("Group0 description").append("</description>");
				xmlBuff.append("<Header0>");
				xmlBuff.append("<objName><![CDATA[").append("stock_transfer").append("]]></objName>");  
				xmlBuff.append("<pageContext><![CDATA[").append("1").append("]]></pageContext>");
				xmlBuff.append("<objContext><![CDATA[").append("1").append("]]></objContext>");
				xmlBuff.append("<editFlag><![CDATA[").append("A").append("]]></editFlag>");
				xmlBuff.append("<focusedColumn><![CDATA[").append("").append("]]></focusedColumn>");
				xmlBuff.append("<action><![CDATA[").append("SAVE").append("]]></action>");
				xmlBuff.append("<elementName><![CDATA[").append("").append("]]></elementName>");
				xmlBuff.append("<keyValue><![CDATA[").append("1").append("]]></keyValue>");
				xmlBuff.append("<taxKeyValue><![CDATA[").append("").append("]]></taxKeyValue>");
				xmlBuff.append("<saveLevel><![CDATA[").append("1").append("]]></saveLevel>");
				xmlBuff.append("<forcedSave><![CDATA[").append(true).append("]]></forcedSave>");
				xmlBuff.append("<taxInFocus><![CDATA[").append(false).append("]]></taxInFocus>");
				xmlBuff.append("<description>").append("Header0 members").append("</description>");	
				xmlBuff.append("<Detail1 dbID=\"\" domID=\"1\" objName=\"stock_transfer\" objContext=\"1\">");  
				xmlBuff.append("<attribute selected=\"N\" updateFlag=\"A\" status=\"N\" pkNames=\"\"/>");
				xmlBuff.append("<tran_id/>");
				
				xmlBuff.append("<tran_date><![CDATA["+ sdf.format(currDate).toString()+"]]></tran_date>");
				
				xmlBuff.append("<ref_date><![CDATA[" + sdf.format(orddate).toString() +"]]></ref_date>");
				
				xmlBuff.append("<tran_type><![CDATA["+ tranType +"]]></tran_type>");
				
				xmlBuff.append("<site_code><![CDATA["+ siteCode +"]]></site_code>");
				
				xmlBuff.append("<item_ser><![CDATA[" + itemSer  +"]]></item_ser>");
				
				xmlBuff.append("<emp_code__aprv><![CDATA["+ empCode +"]]></emp_code__aprv>");
				
				xmlBuff.append("<ref_id__for><![CDATA["   + worder   +"]]></ref_id__for>");
				
				xmlBuff.append("<REF_SER__FOR><![CDATA["   + "w_ord"   +"]]></REF_SER__FOR>");
				
				xmlBuff.append("<reas_code><![CDATA["     + reasCode+" ]]></reas_code>");
				
				xmlBuff.append("<truck_no><![CDATA[ ]]></truck_no>");
				
				xmlBuff.append("</Detail1>");
				
				xmlBuff.append("<Detail2 dbID='' domID=\"1\" objName=\"stock_transfer\" objContext=\"2\">"); 
				xmlBuff.append("<attribute pkNames=\"\" selected=\"N\" updateFlag=\"A\" status=\"N\" />");
				xmlBuff.append("<tran_id/>");
				
				xmlBuff.append("<item_code><![CDATA["+ itemCode+"]]></item_code>");
				
				xmlBuff.append("<line_no><![CDATA[1]]></line_no>");
				
				xmlBuff.append("<quantity><![CDATA[" + allocQty +"]]></quantity>");
				
				xmlBuff.append("<remarks><![CDATA["  + remarks  +"]]></remarks>");
				
				xmlBuff.append("<loc_code__fr><![CDATA[" + locCodeFr +"]]></loc_code__fr>");
				
				xmlBuff.append("<loc_code__to><![CDATA[" + locCodeTo +"]]></loc_code__to>");
				
				xmlBuff.append("<lot_no__fr><![CDATA["   + lotno +"]]></lot_no__fr>");
				
				xmlBuff.append("<lot_sl__fr><![CDATA["   + lotsl +"]]></lot_sl__fr>");
				
				xmlBuff.append("<lot_no__to><![CDATA["   + lotno +"]]></lot_no__to>");
				
				xmlBuff.append("<lot_sl__to><![CDATA["   + lotsl +"]]></lot_sl__to>");
				
				xmlBuff.append("<acct_code__dr><![CDATA["+ acctCodeInv1 +"]]></acct_code__dr>");
				
				xmlBuff.append("<acct_code__cr><![CDATA["+ acctCodeInv1 +"]]></acct_code__cr>");
				
				xmlBuff.append("<cctr_code__cr><![CDATA["+ cctrCodeInv1 +"]]></cctr_code__cr>");
				
				xmlBuff.append("<cctr_code__dr><![CDATA["+ cctrCodeInv1 +"]]></cctr_code__dr>");
				
				xmlBuff.append("</Detail2>");
				xmlBuff.append("</Header0>");
				xmlBuff.append("</group0>");
				xmlBuff.append("</DocumentRoot>");
				
				xmlString = xmlBuff.toString();
				System.out.println("@@@@@2: xmlString :::: "+xmlBuff.toString());
				System.out.println("...............just before savdata() ");
				siteCode = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginSiteCode");
				System.out.println("== site code == @"+siteCode);
				retString = saveData(siteCode,xmlString,connOne);
				System.out.println("@@@@@2: retString: @"+retString);
				System.out.println("--retString finished--");
				if (retString.indexOf("Success") > -1)
				{
					connOne.commit();
					System.out.println("@@@@@@3: retString @ "+retString);
					String[] arrayForTranId = retString.split("<TranID>");
					int endIndex = arrayForTranId[1].indexOf("</TranID>");
					String tranIdForIssue = arrayForTranId[1].substring(0,endIndex);
					System.out.println("-tranIdForIssue-"+tranIdForIssue);
					StockTransferConf stconf = new StockTransferConf();
					retString = stconf.confirm(tranIdForIssue,xtraParams,"");
					System.out.println("retString from confirmStocktrans  :: @:: "+retString);//CONFSUCC
				}
			
//			if((retString != null ) &&  retString.indexOf("Success") > -1 || retString.indexOf("CONFSUCC") >-1)
//			{
////				sql = " update inv_alloc_det set loc_Code = ? where tran_id = ? and item_code = ? ";
////				pstmt = connOne.prepareStatement(sql);
////				pstmt.setString(1,locCodeTo);
////				pstmt.setString(2,tranIdInvAlloc);
////				pstmt.setString(3,itemCode);
////				int rows = pstmt.executeUpdate();
////				System.out.println("--update rows in process --"+ rows);
////				if(pstmt != null)
////					pstmt.close();
////				pstmt = null;
//			}
		}
		
		catch(Exception e)
		{
			e.printStackTrace();
			retString = e.getMessage();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
				if(rs != null)
				{
					rs.close();
					rs = null;
				}	
			}
			catch(Exception e)
			{
				e.printStackTrace();
				retString = e.getMessage();
				throw new ITMException(e);
			}
		}
		System.out.println("Returning Result [" + retString + "]");
		return retString;
	}
	private String checkNull(String input) 
	{
		if(input == null)
		{
			input = "";
		}
		return input;
	}
	private String saveData(String siteCode,String xmlString, Connection conn) throws ITMException
	{
		System.out.println("saving data...........");
		InitialContext ctx = null;
		String retString = null;
		MasterStatefulLocal masterStateful = null; // for ejb3
		try
		{
			AppConnectParm appConnect = new AppConnectParm();
			ctx = new InitialContext(appConnect.getProperty());
			masterStateful = (MasterStatefulLocal)ctx.lookup("ibase/MasterStatefulEJB/local");
			System.out.println("-----------masterStateful------- " + masterStateful);
			String [] authencate = new String[2];
			authencate[0] = "";
			authencate[1] = "";
			System.out.println("xmlString to masterstateful [" + xmlString + "]");
			retString = masterStateful.processRequest(authencate, siteCode, true, xmlString,true,conn);
			System.out.println("--retString - -"+retString);
		}
		catch(ITMException itme)
		{
			System.out.println("ITMException :CreateDistOrder :saveData :==>");
			throw itme;
		}
		catch(Exception e)
		{
			e.printStackTrace();
			System.out.println("Exception :CreateDistOrder :saveData :==>");
			throw new ITMException(e);
		}
		return retString;
	}
}