/**
 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.Statement;
import java.sql.Timestamp;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
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.InvAllocTraceBean;
import ibase.webitm.ejb.dis.StockUpdate;
//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,lineno=1;
		Connection conn = null;
		ResultSet rs = null,rs1= null;
		double issQty=0.0,allocQty=0,allocatedQty=0.0,integralQty=0.0,potencyadj=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");
		String siteCode="",remarks="",reasCode="",explev="";
		
		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 = ?) ";
		    sql = " select tran_id,item_code,lot_no,lot_sl,loc_code,sum(quantity) as quantity  "
		    		+" from inv_alloc_det GROUP BY tran_id, item_code, lot_no, lot_sl, loc_code having  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 = ? and lot_no__fr = ?  ";
					pstmt1 = conn.prepareStatement(sql);
					pstmt1.setString(1, worder);
					pstmt1.setString(2,  rs.getString("item_code"));
					pstmt1.setString(3,  rs.getString("lot_no"));
					rs1 = pstmt1.executeQuery();
					if ( rs1.next() )
					{
						allocatedQty = rs1.getDouble(1);
					}
					if(rs1 != null)
						rs1.close();
					rs1 = null;
					if(pstmt1 != null)
						pstmt1.close();
					pstmt1 = null;
					
					sql = " select site_code,remarks,reas_code,potency_adj,line_no,exp_lev  " +
							" from inv_alloc_det where  tran_id = ? and item_code = ? and lot_no = ? and lot_sl = ? and loc_code = ?  ";
					pstmt1 = conn.prepareStatement(sql);
					pstmt1.setString(1, rs.getString("tran_id"));
					pstmt1.setString(2,  rs.getString("item_code"));
					pstmt1.setString(3,  rs.getString("lot_no"));
					pstmt1.setString(4,  rs.getString("lot_sl"));
					pstmt1.setString(5,  rs.getString("loc_code"));
					rs1 = pstmt1.executeQuery();
					if(rs1.next())
					{
						siteCode = rs1.getString("site_code");
						remarks = rs1.getString("remarks");
						reasCode = rs1.getString("reas_code");
						potencyadj = rs1.getDouble("potency_adj");
						lineno = rs1.getInt("line_no");
						explev = rs1.getString("exp_lev");
					}
					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[" + lineno +"]]>").append("</line_no>");
					
					retTabSepStrBuff.append("<site_code>").append("<![CDATA[" + siteCode +"]]>").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>");
					retTabSepStrBuff.append("<actual_propqty>").append("<![CDATA[" + df.format(allocQty) +"]]>").append("</actual_propqty>");

					
					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 && 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>");
					}
					if(integralQty == 0)
					{
						retTabSepStrBuff.append("<quantity>").append("<![CDATA[" + df.format(allocQty) +"]]>").append("</quantity>");

					}
					retTabSepStrBuff.append("<allocated_qty>").append("<![CDATA[" + df.format(allocatedQty) +"]]>").append("</allocated_qty>");

					retTabSepStrBuff.append("<remarks>").append("<![CDATA[" + remarks +"]]>").append("</remarks>");
					
					retTabSepStrBuff.append("<reas_code>").append("<![CDATA[" + checkNull(reasCode) +"]]>").append("</reas_code>");
					
					retTabSepStrBuff.append("<loc_code>").append("<![CDATA[" + rs.getString("loc_code") +"]]>").append("</loc_code>");
					
					retTabSepStrBuff.append("<exp_lev>").append("<![CDATA[" + explev +"]]>").append("</exp_lev>");
					
					retTabSepStrBuff.append("<potency_adj>").append("<![CDATA[" + potencyadj +"]]>").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="",locCodeFrom="";
		Connection connOne = null;
		ConnDriver connDriver = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		double actualQty =0.0,trfedQty = 0.0,allocQty2=0.0,actualPropQty=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();
		double integralQty = 0.0,batchQuantity=0.0,issueQtyDet=0.0;
		DecimalFormat df = new DecimalFormat("0.000");
		try
		{
			connDriver = null;
			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);
			String batchQuantityStr = checkNull(genericUtility.getColumnValue("batch_quantity", headerDom));
			batchQuantity = Double.parseDouble(batchQuantityStr);
			System.out.println(" batchQuantity ::"+batchQuantity);
			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"))
					{
						if(childNode !=null)
						{
							if(childNode.getFirstChild() != null)
							{
								siteCode = childNode.getFirstChild().getNodeValue();
							}
						}
					}

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

					if (childNodeName.equals("lot_no"))
					{
						if(childNode !=null)
						{
							if(childNode.getFirstChild() != null)
							{
						lotno = childNode.getFirstChild().getNodeValue();
							}
						}
					}
					if (childNodeName.equals("lot_sl"))
					{
						if(childNode !=null)
						{
							if(childNode.getFirstChild() != null)
							{
								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"))
					{
						if(childNode !=null)
						{
							if(childNode.getFirstChild() != null)
							{
								locCodeFr = childNode.getFirstChild().getNodeValue();
							}
						}
					}if (childNodeName.equals("exp_lev"))
					{
						if(childNode !=null)
						{
							if(childNode.getFirstChild() != null)
							{
								explev = childNode.getFirstChild().getNodeValue();
							}
						}
					}
					if (childNodeName.equals("loc_code__to"))
					{
						if(childNode !=null)
						{
							if(childNode.getFirstChild() != null)
							{
								locCodeTo = childNode.getFirstChild().getNodeValue();
							}
						}
					}
					if (childNodeName.equals("loc_code"))
					{
						if(childNode !=null)
						{
							if(childNode.getFirstChild() != null)
							{
								locCodeFrom = childNode.getFirstChild().getNodeValue();
							}
						}
					}
					if (childNodeName.equals("work_order"))
					{
						worder = childNode.getFirstChild().getNodeValue();
					}
					if (childNodeName.equals("remarks"))
					{
						if(childNode !=null)
						{
							if(childNode.getFirstChild() != null)
							{
								remarks = childNode.getFirstChild().getNodeValue();
							}
						}
					}
					if (childNodeName.equals("actual_qty"))
					{
						if(childNode !=null)
						{
							if(childNode.getFirstChild() != null)
							{
								actualQty = Double.parseDouble(childNode.getFirstChild().getNodeValue());
							}
						}
					}
					if (childNodeName.equals("actual_propqty"))
					{
						if(childNode !=null)
						{
							if(childNode.getFirstChild() != null)
							{
								actualPropQty = Double.parseDouble(childNode.getFirstChild().getNodeValue());
							}
						}
					}
					if (childNodeName.equals("allocated_qty"))
					{
						if(childNode !=null)
					   {
							if(childNode.getFirstChild() != null)
							{
								trfedQty = Double.parseDouble(childNode.getFirstChild().getNodeValue());
							}
					   }	
					}
				}//inner for loop 
				sql = "select INTEGRAL_QTY from item where item_code = ?";
				pstmt = connOne.prepareStatement(sql);
				pstmt.setString(1, itemCode);
				rs = pstmt.executeQuery();
				if ( rs.next() )
				{
					integralQty = rs.getDouble(1);
				}
				if(rs != null)
					rs.close();
				rs = null;
				if(pstmt != null)
					pstmt.close();
				pstmt = null;
				System.out.println(" amount values "+actualQty+"::"+trfedQty+"::"+allocQty2);
//				sql = "SELECT ISSUE_QTY FROM INV_ALLOC_DET WHERE  tran_id = ? and item_code  = ? and lot_no = ? and lot_sl = ? and loc_code =  ? and site_code = ? ";
//				pstmt = connOne.prepareStatement(sql);
//				pstmt.setString(1, tranId);
//				pstmt.setString(2, itemCode);
//				pstmt.setString(3, lotno);
//				pstmt.setString(4, lotsl);
//				pstmt.setString(5, locCodeFrom);
//				pstmt.setString(6, siteCode);
//				rs = pstmt.executeQuery();
//				if ( rs.next() )
//				{
//					issueQtyDet = rs.getDouble(1);
//				}
//				if(rs != null)
//					rs.close();
//				rs = null;
//				if(pstmt != null)
//					pstmt.close();
//				pstmt = null;
				//if((Double.parseDouble(df.format(actualQty - trfedQty)) >= Double.parseDouble(df.format(allocQty2))) || (Double.parseDouble(df.format((allocQty2 + trfedQty)-actualQty)) <= integralQty))
			//	if((Double.parseDouble(df.format(actualQty - trfedQty)) >= Double.parseDouble(df.format(allocQty2))) || (Double.parseDouble(df.format((allocQty2 + trfedQty)-issueQtyDet)) <= integralQty))
				if(actualQty > 0.0)
				{
					if((Double.parseDouble(df.format(actualQty - trfedQty)) >= Double.parseDouble(df.format(allocQty2))) || (Double.parseDouble(df.format(allocQty2 - actualPropQty)) <= integralQty))
					{
						errString = allocStockToWo(tranId,lineno,siteCode,itemCode,lotno,lotsl,allocQty,reasCode,locCodeFr,explev,locCodeTo,worder,remarks,tranId,actualPropQty,actualQty,batchQuantity,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 tranId,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,double actualPropQty,double actualQty,double batchQuantity,String xtraParams,Connection connOne) throws RemoteException, ITMException
	{	
		String  userId = "";
		String  termId = "";
		String sql = "",sql1="", acctCodeInv1 = "",cctrCodeInv1="";
		String itemSer="",tranType="",noArt="";
		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;
		String invStatLocCodeTo ="";
		HashMap stockMap = null;
		ArrayList errList = null;
		
		java.sql.Timestamp expDate = null;
		java.sql.Timestamp mfgDate = null;
		java.sql.Timestamp restestDate = null;
		String packCode = "";
		String siteCodeMfg = "";
		String packInstr = "";
		String suppCodeMfg = "";
		String unitAlt = "";
		String batchNo = "";
		String unit = "";
		String grade = "";
		String remarks1 = "";
		String dimension = "";
		String acctCodeDr = "";
		String ediOption = "";
		String dataStr = "";
		String chgTerm = "";
		double totWoQty= 0.0;
		double grossRate = 0d;
		double convQtyStduom = 0d;
		double batchSize = 0d;
		double stkGrossRate = 0d;
		double stkRate = 0d;
		double actualRate = 0d;
		double stkQuantity = 0,holdQuantity = 0;
		double holdQty = 0;
		double potencyPerc=0,grossWeight=0,tareWeight=0,netWeight=0;
		DecimalFormat df = new DecimalFormat("0.000");
		
		
		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);
			stockMap = new HashMap();
			errList  = new ArrayList();
			
			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 quantity,item_code,site_code,bom_code,lot_no,lot_sl,ref_no,ord_date,no_art " +
				  " from workorder where work_order = ? ";
					
			pstmt = connOne.prepareStatement(sql);
			pstmt.setString(1,worder);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				totWoQty = Double.parseDouble(df.format(rs.getDouble("quantity")));
				orddate = rs.getDate("ord_date");
				noArt = rs.getString("no_art")==null?"0":rs.getString("no_art");
			}
			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("<no_art><![CDATA["+ noArt +"]]></no_art>");

				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);
					
					stockMap.put("item_code", itemCode);
					stockMap.put("site_code", siteCode);
					stockMap.put("loc_code", locCodeFr);
					stockMap.put("lot_no", lotno);
					stockMap.put("lot_sl", lotsl);
					double quantity = 0.0;
					if(allocQty != null)
					{
						quantity = Double.parseDouble(allocQty);
					}
					stockMap.put("quantity", ""+quantity);
					
					sql = "SELECT INV_STAT FROM LOCATION WHERE LOC_CODE = ? ";
					pstmt = connOne.prepareStatement(sql);
					pstmt.setString(1,locCodeTo);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						invStatLocCodeTo = checkNull(rs.getString("INV_STAT"));
						
					}
					if(rs != null)
						rs.close();
					rs = null;
					if(pstmt != null)
						pstmt.close();
					pstmt = null;

//					//if(invStatLocCodeTo.trim().equalsIgnoreCase(locCodeTo.trim()) && invStatLocCodeTo.trim().equalsIgnoreCase(partialGrlLoc.trim()))
//					{
//						stockMap.put("no_art", allocQty);
//					}
//					else
//					{
						stockMap.put("no_art", noArt);
//					}

					stockMap.put("tran_ser", "XFRX");
					stockMap.put("acct_code__cr",acctCodeInv1);
					stockMap.put("cctr_code__cr",cctrCodeInv1);
					stockMap.put("acct_code_inv",acctCodeInv1);
					stockMap.put("cctr_code_inv",cctrCodeInv1);
					
					sql = "select acct_code__inv, cctr_code__inv, "
							+ "exp_date, pack_code, mfg_date, site_code__mfg, "
							+ "pack_instr, supp_code__mfg, retest_date, "
							+ "gross_rate, rate, conv__qty_stduom, unit__alt, batch_no, batch_size, "
							+ "unit, grade, remarks, dimension, quantity ,hold_qty,actual_rate ,partial_used  "
							+ ",potency_perc, inv_stat, gross_weight, tare_weight, net_weight, lot_sl__org 	  "   // added by cpatil on 20/11/13 compare with pb code and adding missing field
	                        + "from stock "
							+ "where item_code = ? "
							+ "and site_code = ? "
							+ "and loc_code = ? "
							+ "and lot_no = ? "
							+ "and lot_sl = ? ";
					pstmt = connOne.prepareStatement(sql);
					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() )
					{
						expDate = rs.getTimestamp("exp_date");
						packCode = rs.getString("pack_code");
						mfgDate = rs.getTimestamp("mfg_date");
						siteCodeMfg = rs.getString("site_code__mfg");
						packInstr = rs.getString("pack_instr");
						suppCodeMfg = rs.getString("supp_code__mfg");
						restestDate = rs.getTimestamp("retest_date");
						stkGrossRate = rs.getDouble("gross_rate");
						stkRate = rs.getDouble("rate");
						System.out.println("stkGrossRate"+stkGrossRate+"stkRate"+stkRate);
						convQtyStduom = rs.getDouble("conv__qty_stduom");
						unitAlt = rs.getString("unit__alt");
						batchNo = rs.getString("batch_no");
						batchSize = rs.getDouble("batch_size");
						unit = rs.getString("unit");
						grade = rs.getString("grade");
						remarks1 = rs.getString("remarks");
						dimension = rs.getString("dimension");
						stkQuantity = rs.getDouble("quantity");
						holdQuantity = rs.getDouble("hold_qty");
						actualRate = rs.getDouble("actual_rate");
						//partialUsed =  checkNull(rs.getString("partial_used"));
						
						potencyPerc = rs.getDouble("potency_perc");    
						grossWeight = rs.getDouble("gross_weight");    
						tareWeight = rs.getDouble("tare_weight");      
						netWeight = rs.getDouble("net_weight");        
						//lotSlOrg = rs.getString("lot_sl__org"); 
					
						stockMap.put("rate",Double.toString(stkRate));
						stockMap.put("gross_rate",Double.toString(stkGrossRate));
						System.out.println("stkGrossRate:::"+Double.toString(stkRate)+"stkRate::::"+Double.toString(stkGrossRate));
						stockMap.put("tran_id", tranIdForIssue );
						stockMap.put("tran_date",currDate  );
	
						stockMap.put("qty_stduom", ""+quantity);
						stockMap.put("unit",unit);
						stockMap.put("grade",grade);
						stockMap.put("remarks",remarks1);
						stockMap.put("dimension",dimension);
						stockMap.put("tran_type", "I");
	
						stockMap.put("exp_date", expDate);
						stockMap.put("pack_code", packCode);
						stockMap.put("mfg_date", mfgDate);
						stockMap.put("site_code__mfg", siteCodeMfg);
						stockMap.put("pack_instr", packInstr);
						stockMap.put("supp_code__mfg", suppCodeMfg);
						stockMap.put("retest_date", restestDate);
						stockMap.put("conv__qty_stduom", ""+convQtyStduom);
						stockMap.put("unit__alt", unitAlt);
						stockMap.put("batch_no", batchNo);
						stockMap.put("batch_size", ""+batchSize);
						stockMap.put("actual_rate", actualRate);
						
					}
					if(rs != null)
						rs.close();
					rs = null;
					if(pstmt != null)
						pstmt.close();
					pstmt = null;
					
					boolean isError = false;
					errList = allocTransfer(siteCode,itemCode,locCodeFr ,
							  lotno,lotsl,locCodeTo ,lotno,lotsl,
							  actualPropQty,stockMap, xtraParams , connOne );
					System.out.println("from alloc transfer=="+errList.toString());
					retString = errList.get(0).toString() ;
					System.out.println("FROM allocTransfer 0 : "+retString);
					if ( retString != null && retString.trim().length() > 0 )
					{
						isError = true;
					}
					stockMap.clear();
					if(isError == false)
					{
						System.out.println(" STOCK ALLOCATED SUCCESSFULL");
						StockTransferConf stconf = new StockTransferConf();
						retString = stconf.confirm(tranIdForIssue,xtraParams,"");
						System.out.println("retString from confirmStocktrans  :: @:: "+retString);//CONFSUCC
						if(retString.indexOf("CONFSUCC") > -1)
						{ 
							int count = 0,recIn=0 ;
							sql = " select count(*) from inv_alloc_det where tran_id = ? and item_code  = ? and lot_no = ? and lot_sl = ? and loc_code =  ? and site_code = ? ";
							pstmt = connOne.prepareStatement(sql);
							pstmt.setString(1,tranId);
							pstmt.setString(2,itemCode);
							pstmt.setString(3,lotno);
							pstmt.setString(4,lotsl);
							pstmt.setString(5,locCodeTo);
							pstmt.setString(6,siteCode);
							rs = pstmt.executeQuery();
							if ( rs.next() )
							{
								count = rs.getInt(1);
							}
							if(rs != null)
								rs.close();
							rs = null;
							if(pstmt != null)
								pstmt.close();
							pstmt = null;
							if(count == 0)
							{
								if( batchQuantity == totWoQty)
								{
									sql = " update inv_alloc_det set loc_code = ? where tran_id = ? and item_code  = ? and lot_no = ? and lot_sl = ? and loc_code =  ? and site_code = ? ";
									pstmt = connOne.prepareStatement(sql);
									pstmt.setString(1,locCodeTo);
									pstmt.setString(2,tranId);
									pstmt.setString(3,itemCode);
									pstmt.setString(4,lotno);
									pstmt.setString(5,lotsl);
									pstmt.setString(6,locCodeFr);
									pstmt.setString(7,siteCode);
									pstmt.executeUpdate();
									if(pstmt != null)
										pstmt.close();
									pstmt = null;
								}else
								{

									int maxlineNo = 0;
									sql = " select max(line_no) as max_line_no from inv_alloc_det where tran_id = ? ";
									pstmt = connOne.prepareStatement(sql);
									pstmt.setString(1,tranId);
									rs = pstmt.executeQuery();
									if ( rs.next() )
									{
										maxlineNo = rs.getInt(1);
									}
									System.out.println("maxlineNo::"+maxlineNo);
									if(rs != null)
										rs.close();
									rs = null;
									if(pstmt != null)
										pstmt.close();
									pstmt = null;
									sql = " Insert into inv_alloc_det(TRAN_ID,LINE_NO,SITE_CODE,ITEM_CODE,LOT_NO,LOT_SL,QUANTITY,REMARKS,REAS_CODE,LOC_CODE,EXP_LEV,ISSUE_QTY)" +
											" values (?,?,?,?,?,?,?,?,?,?,?,?)";
									pstmt = connOne.prepareStatement(sql);
									pstmt.setString(1,tranId);
									pstmt.setInt(2,maxlineNo+1);
									pstmt.setString(3,siteCode);
									pstmt.setString(4,itemCode);
									pstmt.setString(5,lotno);
									pstmt.setString(6,lotsl);
									pstmt.setDouble(7,actualPropQty);
									pstmt.setString(8,remarks);
									pstmt.setString(9,reasCode);
									pstmt.setString(10,locCodeTo);
									pstmt.setString(11,explev);
									pstmt.setDouble(12,actualPropQty);
									pstmt.executeUpdate();
									if(pstmt != null)
										pstmt.close();
									pstmt = null;
									sql = " update inv_alloc_det set quantity = ? where tran_id = ? and item_code  = ? and lot_no = ? and lot_sl = ? and loc_code =  ? and site_code = ?  ";
									System.out.println(" SQL::update"+sql);
									pstmt = connOne.prepareStatement(sql);
									pstmt.setDouble(1,(actualQty - actualPropQty));
									pstmt.setString(2,tranId);
									pstmt.setString(3,itemCode);
									pstmt.setString(4,lotno);
									pstmt.setString(5,lotsl);
									pstmt.setString(6,locCodeFr);
									pstmt.setString(7,siteCode);
									pstmt.executeUpdate();
									if(pstmt != null)
										pstmt.close();
									pstmt = null;
								}
							}
							else
							{
								
								sql = " update inv_alloc_det set quantity = ? where tran_id = ? and item_code  = ? and lot_no = ? and lot_sl = ? and loc_code =  ? and site_code = ?  ";
								System.out.println(" SQL::update"+sql);
								pstmt = connOne.prepareStatement(sql);
								pstmt.setDouble(1,(actualQty - actualPropQty));
								pstmt.setString(2,tranId);
								pstmt.setString(3,itemCode);
								pstmt.setString(4,lotno);
								pstmt.setString(5,lotsl);
								pstmt.setString(6,locCodeFr);
								pstmt.setString(7,siteCode);
								pstmt.executeUpdate();
								if(pstmt != null)
									pstmt.close();
								pstmt = null;
								double lcToqty=0.0;
								sql = " select quantity from inv_alloc_det where tran_id = ? and item_code  = ? and lot_no = ? and lot_sl = ? and loc_code =  ? and site_code = ? ";
								pstmt = connOne.prepareStatement(sql);
								pstmt.setString(1,tranId);
								pstmt.setString(2,itemCode);
								pstmt.setString(3,lotno);
								pstmt.setString(4,lotsl);
								pstmt.setString(5,locCodeTo);
								pstmt.setString(6,siteCode);
								rs = pstmt.executeQuery();
								if ( rs.next() )
								{
									lcToqty = rs.getDouble(1);
								}
								if(rs != null)
									rs.close();
								rs = null;
								if(pstmt != null)
									pstmt.close();
								pstmt = null;
								sql = "update inv_alloc_det set quantity = ? ,ISSUE_QTY = ? where tran_id = ? and item_code  = ? and lot_no = ? and lot_sl = ? and loc_code =  ? and site_code = ? ";
							
								pstmt = connOne.prepareStatement(sql);
								pstmt.setDouble(1,(lcToqty + actualPropQty));
								pstmt.setDouble(2,(lcToqty + actualPropQty));
								pstmt.setString(3,tranId);
								pstmt.setString(4,itemCode);
								pstmt.setString(5,lotno);
								pstmt.setString(6,lotsl);
								pstmt.setString(7,locCodeTo);
								pstmt.setString(8,siteCode);
								pstmt.executeUpdate();
								if(pstmt != null)
									pstmt.close();
								pstmt = null;
							}
							}
						}
					}
				
			
//			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;
	}
	private ArrayList allocTransfer(String siteCode,String itemCode,String locCodeFr ,String lotNoFr,String lotSlFr,String locCodeTo ,String lotNoTo,String lotSlTo,double quantitydet,HashMap stockMap,String xtraParams ,Connection conn ) throws ITMException
	{
		String errString = "";
		String sql = "";
		String refSer = "", refId = "" ,tranId = "",workorder = "";
		//double pendingQty = 0 ;
		int refLine = 0,count = 0,k = 0 ,lineNo = 0 , maxLine = 0;
		double quantity = 0 , refQty = 0 ,stockAllocQty = 0 ,invAllocQty = 0,stockQty = 0 ,pendingQty = 0 ;
		double splitQty = 0, rate = 0;
		boolean checkWOIssue = false;
		PreparedStatement pstmt = null ,pstmt1 = null,pstmt2 = null;
		Statement stmt = null;
		ResultSet rs = null,rs1 = null,rs2 = null;
		InvAllocTraceBean invAllocTrace = null;
		StockUpdate stkUpdate = null;
		ArrayList retValue = new ArrayList();
		HashMap hashMap = null;
		HashMap tempMap = null ;
		try
		{
			String dbDateFormat = genericUtility.getDBDateFormat();
			String applDateFormat = genericUtility.getApplDateFormat();//--
			SimpleDateFormat sdfD = new SimpleDateFormat(applDateFormat);//--
			java.util.Date currDate1 = new java.util.Date();
			String currDateStr = sdfD.format(currDate1);
			java.sql.Timestamp tranDate = java.sql.Timestamp.valueOf(genericUtility.getValidDateString( currDateStr , applDateFormat, dbDateFormat ) + " 00:00:00.0") ;
			
			System.out.println("allocTransfer called........");
			invAllocTrace = new InvAllocTraceBean();
			stkUpdate = new StockUpdate();
			pendingQty = quantitydet ;
			tempMap = stockMap;

			sql = "select  alloc_qty from  stock where site_code = ? and loc_code = ? and item_code = ? "
					+"  and lot_no = ? and lot_sl = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, siteCode);
			pstmt.setString(2, locCodeFr);
			pstmt.setString(3, itemCode);
			pstmt.setString(4, lotNoFr);
			pstmt.setString(5, lotSlFr);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				stockAllocQty = rs.getDouble("alloc_qty");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;

			sql = " select sum(a.qty) from "
					+" (select ref_ser,ref_id ,trim(ref_line), sum(alloc_qty) as qty  from invalloc_trace  where site_code = ? and loc_code = ? and item_code = ? "
					+" and lot_no = ? and lot_sl = ?  group by ref_ser, ref_id, trim(ref_line)  having sum(alloc_qty) > 0 order by  sum(alloc_qty)  ) a  ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, siteCode);
			pstmt.setString(2, locCodeFr);
			pstmt.setString(3, itemCode);
			pstmt.setString(4, lotNoFr);
			pstmt.setString(5, lotSlFr);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				invAllocQty = rs.getDouble(1);
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;

			System.out.println("invAllocQty="+invAllocQty+"    stockAllocQty="+stockAllocQty);
			if(invAllocQty < stockAllocQty) //check stock allocate qty and invalloc_trace qty
			{
				checkWOIssue = true;
			}


			sql = "select ref_ser,ref_id ,trim(ref_line) as ref_line, sum(alloc_qty)  from invalloc_trace  where "
					+" site_code = ? and loc_code = ? and item_code = ? and lot_no = ? and lot_sl = ? "
					+"  group by ref_ser, ref_id ,trim( ref_line)  having sum(alloc_qty) > 0 order by  sum(alloc_qty)  desc  ";
			pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY,ResultSet.HOLD_CURSORS_OVER_COMMIT);
			pstmt.setString(1, siteCode);
			pstmt.setString(2, locCodeFr);
			pstmt.setString(3, itemCode);
			pstmt.setString(4, lotNoFr);
			pstmt.setString(5, lotSlFr);
			rs = pstmt.executeQuery();
			while(rs.next())
			{
				refSer = checkNull(rs.getString( "ref_ser" )) ;
				refId = checkNull(rs.getString( "ref_id" ));
				//refLine = rs.getInt("ref_line");
				refLine = Integer.parseInt( checkNull(rs.getString("ref_line")).trim().length() == 0 ?"0":rs.getString("ref_line").trim()) ;
				refQty = rs.getDouble(4);
				System.out.println("pendingQty="+pendingQty);
				System.out.println("refSer="+refSer);
				System.out.println("refId="+refId);
				System.out.println("refLine="+refLine);
				System.out.println("refQty="+refQty);
				if(pendingQty <= 0)
				{
					break;
				}
				if(pendingQty <= refQty)
				{
					quantity = pendingQty;
					pendingQty = 0;
				}
				else
				{
					quantity = refQty;
					pendingQty = pendingQty - refQty ;
				}
				stockQty = stockQty + quantity;


				hashMap = new HashMap();
				
				
				hashMap.put("tran_date",tranDate);
				hashMap.put("ref_ser",refSer);
				hashMap.put("ref_id",refId);
				hashMap.put("ref_line",String.valueOf(refLine));					
				hashMap.put("site_code",siteCode);
				hashMap.put("item_code",itemCode);
				hashMap.put("loc_code",locCodeFr);
				hashMap.put("lot_no",lotNoFr);
				hashMap.put("lot_sl",lotSlFr);
				hashMap.put("alloc_qty",new Double(-1*quantity)); 
				hashMap.put("chg_user",GenericUtility.getInstance().getValueFromXTRA_PARAMS(xtraParams,"loginCode"));
				hashMap.put("chg_term",GenericUtility.getInstance().getValueFromXTRA_PARAMS(xtraParams,"termId"));
				errString = invAllocTrace.updateInvallocTrace(hashMap, conn);
				System.out.println("updateInvallocTrace  1....."+errString);
				if ( errString != null && errString.trim().length() > 0 )
				{
					retValue.add("Error");
					retValue.add(new Double(quantitydet));
					return retValue;
				}
			}//end rs loop
			System.out.println("Stock Qty="+stockQty+"  pendingQty="+pendingQty);
			if (stockQty > 0 && ( errString == null || errString.trim().length() == 0)) 
			{
				stockMap.put("quantity", ""+stockQty);
				stockMap.put("qty_stduom", ""+stockQty);
				//stock update for tran type = "I"
				errString = stkUpdate.updateStock( stockMap, xtraParams, conn ); //updateStock called only once's
				System.out.println("stock update FOR I ....."+errString);
				if ( errString != null && errString.trim().length() > 0 )
				{
					retValue.add("Error");
					retValue.add(new Double(quantitydet));
					return retValue;
				}
				else
				{
					stockMap = tempMap;
					stockMap.put("loc_code", locCodeTo);
					stockMap.put("lot_no", lotNoTo);
					stockMap.put("lot_sl", lotSlTo);
					stockMap.put("tran_type", "R");
					//stock update for tran type = "R"
					errString = stkUpdate.updateStock( stockMap, xtraParams, conn ); 
					System.out.println("stock update FOR R ....."+errString);
					if ( errString != null && errString.trim().length() > 0 )
					{
						retValue.add("Error");
						retValue.add(new Double(quantitydet));
						return retValue;
					}
				}

				if ( errString == null || errString.trim().length() == 0)
				{
					if(rs != null)
					{
						rs.first();
						System.out.println("iterate rs for deallcation ");
						pendingQty = quantitydet;
						do  //iterate rs for deallcation 
						{
							splitQty = 0;
						    refSer = checkNull(rs.getString( "ref_ser" )) ;
							refId = checkNull(rs.getString( "ref_id" ));
						//	//refLine = rs.getInt("ref_line");
							refLine = Integer.parseInt( checkNull(rs.getString("ref_line")).trim().length() == 0 ?"0":rs.getString("ref_line").trim()) ;
							refQty = rs.getDouble(4);
							System.out.println("quantitydet="+pendingQty);
							System.out.println("refSer="+refSer);
							System.out.println("refId="+refId);
							System.out.println("refLine="+refLine);
							System.out.println("refQty="+refQty);
							if(pendingQty <= 0)
							{
								break;
							}
							if(pendingQty <= refQty)
							{
								quantity = pendingQty;
								pendingQty = 0;
								splitQty =  refQty - quantity;
							}
							else
							{
								quantity = refQty;
								pendingQty = pendingQty - refQty ;
							}
							hashMap = new HashMap();
						//	hashMap.put("tran_date",new java.sql.Date(System.currentTimeMillis()));
							hashMap.put("tran_date",tranDate);
							hashMap.put("ref_ser",refSer);
							hashMap.put("ref_id",refId);
							hashMap.put("ref_line",String.valueOf(refLine));					
							hashMap.put("site_code",siteCode);
							hashMap.put("item_code",itemCode);
							hashMap.put("loc_code",locCodeTo);
							hashMap.put("lot_no",lotNoTo);
							hashMap.put("lot_sl",lotSlTo);
							hashMap.put("alloc_qty",new Double(quantity)); 
							hashMap.put("chg_user",GenericUtility.getInstance().getValueFromXTRA_PARAMS(xtraParams,"loginCode"));
							hashMap.put("chg_term",GenericUtility.getInstance().getValueFromXTRA_PARAMS(xtraParams,"termId"));
							errString = invAllocTrace.updateInvallocTrace(hashMap, conn);
							System.out.println("updateInvallocTrace 2....."+errString);
							if ( errString != null && errString.trim().length() > 0 )
							{
								retValue.add("Error");
								retValue.add(new Double(quantitydet));
								return retValue;
							}
							else  
							{
								System.out.println("DeAlloc updateInvallocTrace(HashMap, Connection) : Sucessuful!");
								System.out.println("refSer:::::"+refSer);
								if("S-ORD".equalsIgnoreCase(refSer.trim())) //sale order
								{
									System.out.println("update for sale order");
									maxLine = 0;

									System.out.println("update...  "+quantity+"       "+splitQty);

									if(splitQty > 0)//insert new record for split quantity
									{
										sql = "update sord_alloc_det set  loc_code = ? ,lot_no = ? ,lot_sl = ? ,quantity = ? where sale_order = ? and line_no__sord = ?  ";
										pstmt1 =conn.prepareStatement(sql);
										pstmt1.setString(1,locCodeFr);
										pstmt1.setString(2,lotNoFr);
										pstmt1.setString(3,lotSlFr);
										pstmt1.setDouble(4,splitQty);
										pstmt1.setString(5,refId);
										pstmt1.setInt(6,refLine);
										k = pstmt1.executeUpdate();
										System.out.println("No of row updated sord_alloc_det ="+k);
										pstmt1.close();
										pstmt1 = null;
										
										if(k > 0)
										{
											sql = " select max(line_no) from sord_alloc_det where sale_order = ? ";
											pstmt2 = conn.prepareStatement(sql);
											pstmt2.setString(1, refId);
											rs2 = pstmt2.executeQuery();
											if(rs2.next())
											{
												maxLine = rs2.getInt(1);
											}
											rs2.close();
											rs2 = null;
											pstmt2.close();
											pstmt2 = null;

											maxLine++;

											sql = " insert into sord_alloc_det (tran_id,line_no,sale_order,line_no__sord,item_code,loc_code,lot_no,lot_sl,quantity,"
											     +" dealloc_qty,site_code,exp_lev,pending_qty,wave_flag) "
												 +" select tran_id,"+maxLine+",sale_order,line_no__sord,item_code,'"+locCodeTo+"','"+lotNoTo+"','"+lotSlTo+"',"+quantity+",dealloc_qty,"
											     +" site_code,exp_lev,pending_qty,wave_flag from sord_alloc_det where sale_order = ? and line_no__sord = ? ";
											System.out.println("insert qry="+sql);
											pstmt1 =conn.prepareStatement(sql);
											pstmt1.setString(1,refId);
											pstmt1.setInt(2,refLine);
											k = pstmt1.executeUpdate();
											System.out.println("No of row inserted sord_alloc_det ="+k);
											pstmt1.close();
											pstmt1 = null;
										}
									}
									else
									{
										sql = "update sord_alloc_det set  loc_code = ? ,lot_no = ? ,lot_sl = ? where sale_order = ? and line_no__sord = ?  ";
										pstmt1 =conn.prepareStatement(sql);
										pstmt1.setString(1,locCodeTo);
										pstmt1.setString(2,lotNoTo);
										pstmt1.setString(3,lotSlTo);
										pstmt1.setString(4,refId);
										pstmt1.setInt(5,refLine);
										k = pstmt1.executeUpdate();
										System.out.println("No of row updated sord_alloc_det ="+k);
										pstmt1.close();
										pstmt1 = null;
									}
								
								}
								else if("D-ISS".equalsIgnoreCase(refSer.trim())) //1.Distribution Issue (DISS)
								{
									
									System.out.println("update for Distribution Issue (DISS)");
									rate = 0;
									maxLine = 0;
									sql = " select rate from distord_issdet where tran_id = ? and line_no = ?  ";
									pstmt2 = conn.prepareStatement(sql);
									pstmt2.setString(1, refId);
									pstmt2.setInt(2, refLine);
									rs2 = pstmt2.executeQuery();
									if(rs2.next())
									{
										rate = rs2.getDouble("rate");
									}
									rs2.close();
									rs2 = null;
									pstmt2.close();
									pstmt2 = null;

									System.out.println("update..  "+rate+"    "+quantity+"       "+splitQty);

									if(splitQty > 0)//insert new record for split quantity
									{
										sql = " update distord_issdet set  loc_code = ? ,lot_no = ? ,lot_sl = ? , quantity = ?,amount = ? where tran_id = ?  and line_no = ? ";
										pstmt1 =conn.prepareStatement(sql);
										pstmt1.setString(1,locCodeFr);
										pstmt1.setString(2,lotNoFr);
										pstmt1.setString(3,lotSlFr);
										pstmt1.setDouble(4,splitQty);
										pstmt1.setDouble(5,splitQty*rate);
										pstmt1.setString(6,refId);
										pstmt1.setInt(7,refLine);
										k = pstmt1.executeUpdate();
										System.out.println("No of row updated distord_issdet ="+k);
										pstmt1.close();
										pstmt1 = null;

										if(k > 0)
										{
											sql = " select max(line_no) from distord_issdet where tran_id = ?  ";
											pstmt2 = conn.prepareStatement(sql);
											pstmt2.setString(1, refId);
											rs2 = pstmt2.executeQuery();
											if(rs2.next())
											{
												maxLine = rs2.getInt(1);
											}
											rs2.close();
											rs2 = null;
											pstmt2.close();
											pstmt2 = null;

											maxLine++;

											sql = " insert into distord_issdet (tran_id,line_no,dist_order,line_no_dist_order,item_code,quantity,unit,tax_class,tax_chap,tax_env,loc_code,lot_no,lot_sl,"
											     +" pack_code,rate,amount,tax_amt,net_amt,site_code__mfg,mfg_date,exp_date,potency_perc,no_art,gross_weight,tare_weight,net_weight,pack_instr,dimension," 
												 +" supp_code__mfg,batch_no,grade,retest_date,rate_clg,rate__clg,discount,disc_amt,remarks,cost_rate,unit__alt,conv__qty__alt,qty_order__alt,pallet_wt,"
											     +" rate__alt,conv__rate_alt,return_qty,return_date,returned,batch_size,carton_no,pallet_no) "
												 +" select tran_id,"+maxLine+",dist_order,line_no_dist_order,item_code,"+quantity+",unit,tax_class,tax_chap,tax_env,'"+locCodeTo+"','"+lotNoTo+"','"+lotSlTo+"',"
											     +" pack_code,rate,15*2,tax_amt,net_amt,site_code__mfg,mfg_date,exp_date,potency_perc,no_art,gross_weight,tare_weight,net_weight,"
												 +" pack_instr,dimension,supp_code__mfg,batch_no,grade,retest_date,rate_clg,rate__clg,discount,disc_amt,remarks,cost_rate,unit__alt,"
											     +" conv__qty__alt,qty_order__alt,pallet_wt,rate__alt,conv__rate_alt,return_qty,return_date,returned,batch_size,carton_no,pallet_no  from distord_issdet where tran_id = ? and line_no =  ? ";
																					
											System.out.println("insert qry="+sql);
											pstmt1 =conn.prepareStatement(sql);
											pstmt1.setString(1,refId);
											pstmt1.setInt(2,refLine);
											k = pstmt1.executeUpdate();
											System.out.println("No of row inserted distord_issdet ="+k);
											pstmt1.close();
											pstmt1 = null;
										}
									}
									else
									{
										sql = " update distord_issdet set  loc_code = ? ,lot_no = ? ,lot_sl = ? where tran_id = ?  and line_no = ? ";
										pstmt1 =conn.prepareStatement(sql);
										pstmt1.setString(1,locCodeTo);
										pstmt1.setString(2,lotNoTo);
										pstmt1.setString(3,lotSlTo);
										pstmt1.setString(4,refId);
										pstmt1.setInt(5,refLine);
										k = pstmt1.executeUpdate();
										System.out.println("No of row updated distord_issdet ="+k);
										pstmt1.close();
										pstmt1 = null;
									}


								}
								else if("S-DSP".equalsIgnoreCase(refSer.trim())) //Despatch
								{
									System.out.println("update for Despatch");
									maxLine = 0;
									System.out.println("update... "+quantity+"       "+splitQty);

									if(splitQty > 0)//insert new record for split quantity
									{
										sql = " update despatchdet set  loc_code = ? ,lot_no = ? ,lot_sl = ? ,quantity = ?,quantity__ord = ?,quantity__stduom = ?  where desp_id = ? and line_no = ? ";
										pstmt1 =conn.prepareStatement(sql);
										pstmt1.setString(1,locCodeFr);
										pstmt1.setString(2,lotNoFr);
										pstmt1.setString(3,lotSlFr);
										pstmt1.setDouble(4,splitQty);
										pstmt1.setDouble(5,splitQty);
										pstmt1.setDouble(6,splitQty);
										pstmt1.setString(7,refId);
										pstmt1.setInt(8,refLine);
										k = pstmt1.executeUpdate();
										System.out.println("No of row updated despatchdet ="+k);
										pstmt1.close();
										pstmt1 = null;

										if(k > 0)
										{
											sql = " select max( line_no)   from despatchdet where desp_id = ? ";
											pstmt2 = conn.prepareStatement(sql);
											pstmt2.setString(1, refId);
											rs2 = pstmt2.executeQuery();
											if(rs2.next())
											{
												maxLine = rs2.getInt(1);
											}
											rs2.close();
											rs2 = null;
											pstmt2.close();
											pstmt2 = null;

											maxLine++;
											sql = " insert into despatchdet (desp_id,line_no,sord_no,line_no__sord,exp_lev,item_code__ord,item_code,lot_no,lot_sl,quantity__ord,quantity,loc_code,status,conv__qty_stduom,"
											     +" unit__std,unit,quantity__stduom,quantity_real,rate__stduom,invoice_id,quantity_inv,pack_instr,no_art,site_code,pack_qty,gross_weight,tare_weight,nett_weight,mfg_date,exp_date,"
												 +" chg_date,chg_user,chg_term,site_code__mfg,rate__clg,dimension,tax_amt,disc_amt,conf_diff_amt,rate__std,cost_rate,frequency,down_payment,down_payment_int,inst_amount,inst_int_amount,"
											     +" no_of_inst,line_type,conv__rtuom_stduom,pallet_wt,cust_item__ref,tran_id__invpack,retest_date,part_no,disc_schem_billback_amt,disc_schem_offinv_amt,sscc_18,pack_code,pallet_no) "
												 +"  select desp_id,"+maxLine+",sord_no,line_no__sord,exp_lev,item_code__ord,item_code,'"+lotNoTo+"','"+lotSlTo+"',"+quantity+","+quantity+",'"+locCodeTo+"',status,conv__qty_stduom,unit__std,unit,"+quantity+",quantity_real,rate__stduom,"
											     +" invoice_id,quantity_inv,pack_instr,no_art,site_code,pack_qty,gross_weight,tare_weight,nett_weight,mfg_date,exp_date,chg_date,chg_user,chg_term,site_code__mfg,rate__clg,dimension,"
												 +" tax_amt,disc_amt,conf_diff_amt,rate__std,cost_rate,frequency,down_payment,down_payment_int,inst_amount,inst_int_amount,no_of_inst,line_type,conv__rtuom_stduom,pallet_wt,cust_item__ref,"
											     +" tran_id__invpack,retest_date,part_no,disc_schem_billback_amt,disc_schem_offinv_amt,sscc_18,pack_code,pallet_no "
												 +" from despatchdet where desp_id = ? and  line_no = ?  ";
											
											System.out.println("insert qry="+sql);
											pstmt1 =conn.prepareStatement(sql);
											pstmt1.setString(1,refId);
											pstmt1.setInt(2,refLine);
											k = pstmt1.executeUpdate();
											System.out.println("No of row inserted despatchdet ="+k);
											pstmt1.close();
											pstmt1 = null;
										}
									}
									else
									{
										sql = " update despatchdet set  loc_code = ? ,lot_no = ? ,lot_sl = ?  where desp_id = ? and line_no = ? ";
										pstmt1 =conn.prepareStatement(sql);
										pstmt1.setString(1,locCodeTo);
										pstmt1.setString(2,lotNoTo);
										pstmt1.setString(3,lotSlTo);
										pstmt1.setString(4,refId);
										pstmt1.setInt(5,refLine);
										k = pstmt1.executeUpdate();
										System.out.println("No of row updated despatchdet ="+k);
										pstmt1.close();
										pstmt1 = null;
									}

								}
								else if("XFRX".equalsIgnoreCase(refSer.trim())) // Stock Transfer Multiple
								{
									System.out.println("update for Stock Transfer Multiple");
									maxLine = 0;
									System.out.println("update...  "+quantity+"       "+splitQty);

									if(splitQty > 0)//insert new record for split quantity
									{
										sql = " update stock_transfer_det set   loc_code__to = ? ,lot_no__to = ? , lot_sl__to  = ? ,quantity = ? where tran_id = ? and line_no = ?   ";
										pstmt1 =conn.prepareStatement(sql);
										pstmt1.setString(1,locCodeFr);
										pstmt1.setString(2,lotNoFr);
										pstmt1.setString(3,lotSlFr);
										pstmt1.setDouble(4,splitQty);
										pstmt1.setString(5,refId);
										pstmt1.setInt(6,refLine);
										k = pstmt1.executeUpdate();
										System.out.println("No of row updated stock_transfer_det ="+k);
										pstmt1.close();
										pstmt1 = null;
										
										if(k > 0)
										{
											sql = " select max(line_no) from stock_transfer_det where tran_id = ? ";
											pstmt2 = conn.prepareStatement(sql);
											pstmt2.setString(1, refId);
											rs2 = pstmt2.executeQuery();
											if(rs2.next())
											{
												maxLine = rs2.getInt(1);
											}
											rs2.close();
											rs2 = null;
											pstmt2.close();
											pstmt2 = null;

											maxLine++;
											
											sql = " insert into stock_transfer_det (tran_id,line_no,item_code,quantity,loc_code__fr,loc_code__to,lot_no__fr,lot_no__to,lot_sl__fr,lot_sl__to,"
											     +" remarks,acct_code__cr,acct_code__dr,cctr_code__dr,cctr_code__cr,no_art,loc_code__sys) "
												 +" select tran_id,"+maxLine+",item_code,"+quantity+",'"+locCodeTo+"',loc_code__to,'"+lotNoTo+"',lot_no__to,'"+lotSlTo+"',lot_sl__to, "
											     +" remarks,acct_code__cr,acct_code__dr,cctr_code__dr,cctr_code__cr,no_art,loc_code__sys from stock_transfer_det where  tran_id = ? and line_no = ?  ";
											System.out.println("insert qry="+sql);
											pstmt1 =conn.prepareStatement(sql);
											pstmt1.setString(1,refId);
											pstmt1.setInt(2,refLine);
											k = pstmt1.executeUpdate();
											System.out.println("No of row inserted stock_transfer_det ="+k);
											pstmt1.close();
											pstmt1 = null;
										}
									}
									else
									{
										sql = " update stock_transfer_det set   loc_code__to = ? ,lot_no__to = ? , lot_sl__to  = ?  where tran_id = ? and line_no = ?   ";
										pstmt1 =conn.prepareStatement(sql);
										pstmt1.setString(1,locCodeTo);
										pstmt1.setString(2,lotNoTo);
										pstmt1.setString(3,lotSlTo);
										pstmt1.setString(4,refId);
										pstmt1.setInt(5,refLine);
										k = pstmt1.executeUpdate();
										System.out.println("No of row updated stock_transfer_det ="+k);
										pstmt1.close();
										pstmt1 = null;
									}
								}
								else if("C-ISS".equalsIgnoreCase(refSer.trim())) // Consumption Issue(CISS).
								{
									System.out.println("update for Consumption Issue");
									maxLine = 0;
									rate = 0;
									sql = " select rate  from consume_iss_det where cons_issue = ? and line_no = ?  ";
									pstmt2 = conn.prepareStatement(sql);
									pstmt2.setString(1, refId);
									pstmt2.setInt(2, refLine);
									rs2 = pstmt2.executeQuery();
									if(rs2.next())
									{
										rate = rs2.getDouble("rate");
									}
									rs2.close();
									rs2 = null;
									pstmt2.close();
									pstmt2 = null;
									System.out.println("update... "+rate+"    "+quantity+"       "+splitQty);
									if(splitQty > 0)//insert new record for split quantity
									{
										sql = " update consume_iss_det set  loc_code = ?,lot_no = ?,lot_sl = ? ,quantity = ? ,quantity__std = ? ,amount = ? where cons_issue = ? and line_no = ?  ";
										pstmt1 =conn.prepareStatement(sql);
										pstmt1.setString(1,locCodeFr);
										pstmt1.setString(2,lotNoFr);
										pstmt1.setString(3,lotSlFr);
										pstmt1.setDouble(4,splitQty);
										pstmt1.setDouble(5,splitQty);
										pstmt1.setDouble(6,splitQty*rate);
										pstmt1.setString(7,refId);
										pstmt1.setInt(8,refLine);
										k = pstmt1.executeUpdate();
										System.out.println("No of row updated consume_iss_det ="+k);
										pstmt1.close();
										pstmt1 = null;
										
										if(k > 0)
										{
											sql = " select max( line_no) from consume_iss_det where cons_issue = ? ";
											pstmt2 = conn.prepareStatement(sql);
											pstmt2.setString(1, refId);
											rs2 = pstmt2.executeQuery();
											if(rs2.next())
											{
												maxLine = rs2.getInt(1);
											}
											rs2.close();
											rs2 = null;
											pstmt2.close();
											pstmt2 = null;

											maxLine++;
											
											sql = " insert into consume_iss_det (cons_issue,line_no,cons_order,line_no__ord,item_code,quantity,unit,rate,amount,tax_class,tax_chap,tax_env,tax_amt,net_amt,"
											     +" acct_code,cctr_code,loc_code,lot_no,lot_sl,quantity__std,unit__std,conv_qty_stduom,qc_reqd,acct_code__inv,cctr_code__inv,no_art,anal_code,carton_no,pallet_no) "
												 +" select cons_issue,"+maxLine+",cons_order,line_no__ord,item_code,"+quantity+",unit,rate,"+rate*quantity+",tax_class,tax_chap,tax_env,tax_amt,net_amt,acct_code,"
											     +" cctr_code,'"+locCodeTo+"','"+lotNoTo+"','"+lotSlTo+"',"+quantity+",unit__std,conv_qty_stduom,qc_reqd,acct_code__inv,cctr_code__inv,no_art,anal_code,carton_no,pallet_no "
												 +" from consume_iss_det where CONS_ISSUE = ? and line_no = ?  ";
											System.out.println("insert qry="+sql);
											pstmt1 =conn.prepareStatement(sql);
											pstmt1.setString(1,refId);
											pstmt1.setInt(2,refLine);
											k = pstmt1.executeUpdate();
											System.out.println("No of row inserted consume_iss_det ="+k);
											pstmt1.close();
											pstmt1 = null;
										}
									}
									else
									{
										sql = " update consume_iss_det set  loc_code = ?,lot_no = ?,lot_sl = ? where cons_issue = ? and line_no = ?  ";
										pstmt1 =conn.prepareStatement(sql);
										pstmt1.setString(1,locCodeTo);
										pstmt1.setString(2,lotNoTo);
										pstmt1.setString(3,lotSlTo);
										pstmt1.setString(4,refId);
										pstmt1.setInt(5,refLine);
										k = pstmt1.executeUpdate();
										System.out.println("No of row updated consume_iss_det ="+k);
										pstmt1.close();
										pstmt1 = null;
										
									}
								}
								else if("ADJISS".equalsIgnoreCase(refSer.trim())) // Adjustment Issue(ADJISS). 
								{
									System.out.println("update for Adjustment Issue");
									rate = 0;
									maxLine = 0;
									sql = " select rate from  adj_issrcpdet where  tran_id = ? and line_no = ?  ";
									pstmt2 = conn.prepareStatement(sql);
									pstmt2.setString(1, refId);
									pstmt2.setInt(2, refLine);
									rs2 = pstmt2.executeQuery();
									if(rs2.next())
									{
										rate = rs2.getDouble("rate");
									}
									rs2.close();
									rs2 = null;
									pstmt2.close();
									pstmt2 = null;

									System.out.println("1420  "+rate+"    "+quantity+"       "+splitQty);

									if(splitQty > 0)//insert new record for split quantity
									{
										sql = " update adj_issrcpdet set  loc_code = ? ,lot_no = ? ,lot_sl = ? ,quantity = ? , amount = ? where tran_id = ? and line_no = ? ";
										pstmt1 =conn.prepareStatement(sql);
										pstmt1.setString(1,locCodeFr);
										pstmt1.setString(2,lotNoFr);
										pstmt1.setString(3,lotSlFr);
										pstmt1.setDouble(4,splitQty);
										pstmt1.setDouble(5,rate * splitQty);
										pstmt1.setString(6,refId);
										pstmt1.setInt(7,refLine);
										k = pstmt1.executeUpdate();
										System.out.println("No of row updated adj_issrcpdet ="+k);
										pstmt1.close();
										pstmt1 = null;

										if(k > 0)
										{
											sql = " select max(line_no) from  adj_issrcpdet where  tran_id = ? ";
											pstmt2 = conn.prepareStatement(sql);
											pstmt2.setString(1, refId);
											rs2 = pstmt2.executeQuery();
											if(rs2.next())
											{
												maxLine = rs2.getInt(1);
											}
											rs2.close();
											rs2 = null;
											pstmt2.close();
											pstmt2 = null;

											maxLine++;

											sql = " insert into  adj_issrcpdet (tran_id,line_no,item_code,unit,loc_code,lot_no,lot_sl,quantity,sundry_type,sundry_code,rate,gross_rate,grade,dimension,no_art, "
													+" amount,gross_weight,tare_weight,net_weight,acct_code__dr,cctr_code__dr,acct_code__cr,cctr_code__cr,potency_perc,pack_code,mfg_date,exp_date, "
													+" site_code__mfg,conv__qty_stduom,unit__alt,conv_qty_stduom,unit_alt,theoretical_wt,retest_date,supp_code__mfg,batch_no) "
													+" select tran_id,"+maxLine+",item_code,unit,'"+locCodeTo+"','"+lotNoTo+"','"+lotSlTo+"', "+quantity+", sundry_type,sundry_code,rate,gross_rate,grade,dimension,no_art, "
													+ rate * quantity +",gross_weight,tare_weight,net_weight,acct_code__dr,cctr_code__dr,acct_code__cr,cctr_code__cr,potency_perc,pack_code,mfg_date,exp_date, "
													+"  site_code__mfg,conv__qty_stduom,unit__alt,conv_qty_stduom,unit_alt,theoretical_wt,retest_date,supp_code__mfg,batch_no from adj_issrcpdet  "
													+" where tran_id = ? and line_no = ?  ";
											System.out.println("insert qry="+sql);
											pstmt1 =conn.prepareStatement(sql);
											pstmt1.setString(1,refId);
											pstmt1.setInt(2,refLine);
											k = pstmt1.executeUpdate();
											System.out.println("No of row inserted adj_issrcpdet ="+k);
											pstmt1.close();
											pstmt1 = null;
										}
									}
									else
									{
										sql = " update adj_issrcpdet set  loc_code = ? ,lot_no = ? ,lot_sl = ? ,quantity = ? , amount = ? where tran_id = ? and line_no = ? ";
										pstmt1 =conn.prepareStatement(sql);
										pstmt1.setString(1,locCodeTo);
										pstmt1.setString(2,lotNoTo);
										pstmt1.setString(3,lotSlTo);
										pstmt1.setDouble(4,quantity);
										pstmt1.setDouble(5,rate * quantity);
										pstmt1.setString(6,refId);
										pstmt1.setInt(7,refLine);
										k = pstmt1.executeUpdate();
										System.out.println("No of row updated adj_issrcpdet ="+k);
										pstmt1.close();
										pstmt1 = null;
									}


								}
								else if("P-RET".equalsIgnoreCase(refSer.trim())) // Purchase Order Return(PRETURN). 
								{
									System.out.println("update for PRETURN");
									rate = 0;
									maxLine = 0;
									sql = " select rate  from porcpdet where tran_id = ? and line_no = ?  ";
									pstmt2 = conn.prepareStatement(sql);
									pstmt2.setString(1, refId);
									pstmt2.setInt(2, refLine);
									rs2 = pstmt2.executeQuery();
									if(rs2.next())
									{
										rate = rs2.getDouble("rate");
									}
									rs2.close();
									rs2 = null;
									pstmt2.close();
									pstmt2 = null;

									System.out.println("update..  "+rate+"    "+quantity+"       "+splitQty);

									if(splitQty > 0)//insert new record for split quantity
									{
										sql = " update porcpdet set  loc_code = ? ,lot_no = ? ,lot_sl = ? ,quantity = ?, quantity__stduom = ?,net_amt = ? where tran_id = ? and line_no = ?  ";
										pstmt1 =conn.prepareStatement(sql);
										pstmt1.setString(1,locCodeFr);
										pstmt1.setString(2,lotNoFr);
										pstmt1.setString(3,lotSlFr);
										pstmt1.setDouble(4,splitQty);
										pstmt1.setDouble(5,splitQty);
										pstmt1.setDouble(6,rate * splitQty);
										pstmt1.setString(7,refId);
										pstmt1.setInt(8,refLine);
										k = pstmt1.executeUpdate();
										System.out.println("No of row updated porcpdet ="+k);
										pstmt1.close();
										pstmt1 = null;
										
										if(k > 0)
										{
											sql = " select max(line_no)  from porcpdet where tran_id = ?  ";
											pstmt2 = conn.prepareStatement(sql);
											pstmt2.setString(1, refId);
											rs2 = pstmt2.executeQuery();
											if(rs2.next())
											{
												maxLine = rs2.getInt(1);
											}
											rs2.close();
											rs2 = null;
											pstmt2.close();
											pstmt2 = null;

											maxLine++;

											sql = " insert into porcpdet (tran_id,line_no,item_code,purc_order,quantity,unit,rate,discount,tax_amt,net_amt,loc_code,lot_no,lot_sl,line_no__ord,"
											     +" canc_bo,vouch_qty,acct_code__dr,cctr_code__dr,acct_code__cr,cctr_code__cr,unit__rate,conv__qty_stduom,conv__rtuom_stduom,unit__std,"
												 +" quantity__stduom,rate__stduom,pack_code,no_art,pack_instr,batch_no,mfg_date,expiry_date,gross_weight,tare_weight,net_weight,status,"
											     +" potency_perc,supp_code__mnfr,site_code__mfg,reas_code,remarks,challan_qty,grade,tax_class,tax_chap,tax_env,specific_instr,special_instr,"
												 +" loc_code__excess_short,excess_short_qty,additional_cost,rate__clg,supp_challan_qty,realised_qty,item_code__mfg,spec_ref,std_rate,dept_code,"
											     +" effect_stock,physical_status,benefit_type,licence_no,acct_code__prov_dr,cctr_code__prov_dr,acct_code__prov_cr,cctr_code__prov_cr,form_no,"
												 +" retest_date,duty_paid,batch_size,damage_qty,sample_qty,shelf_life__type,qc_reqd,sh_qty,rejc_qty,assetinstall_qty,part_qty,partial_yn,anal_code,carton_no,pallet_no) "
											     +" select tran_id,"+maxLine+",item_code,purc_order,"+quantity+",unit,rate,discount,tax_amt,"+quantity*rate+",'"+locCodeTo+"','"+lotNoTo+"','"+lotSlTo+"',line_no__ord,"
												 +" canc_bo,vouch_qty,acct_code__dr,cctr_code__dr,acct_code__cr,cctr_code__cr,unit__rate,conv__qty_stduom,conv__rtuom_stduom,unit__std, "
											     + quantity+",rate__stduom,pack_code,no_art,pack_instr,batch_no,mfg_date,expiry_date,gross_weight,tare_weight,net_weight,status,potency_perc,supp_code__mnfr,"
												 +" site_code__mfg,reas_code,remarks,challan_qty,grade,tax_class,tax_chap,tax_env,specific_instr,special_instr,loc_code__excess_short,excess_short_qty,"
											     +" additional_cost,rate__clg,supp_challan_qty,realised_qty,item_code__mfg,spec_ref,std_rate,dept_code,effect_stock,physical_status,benefit_type,licence_no,"
												 +" acct_code__prov_dr,cctr_code__prov_dr,acct_code__prov_cr,cctr_code__prov_cr,form_no,retest_date,duty_paid,batch_size,damage_qty,sample_qty,shelf_life__type,"
											     +" qc_reqd,sh_qty,rejc_qty,assetinstall_qty,part_qty,partial_yn,anal_code,carton_no,pallet_no  from porcpdet where tran_id = ? and line_no = ?  ";
											System.out.println("insert qry="+sql);
											
											pstmt1 =conn.prepareStatement(sql);
											pstmt1.setString(1,refId);
											pstmt1.setInt(2,refLine);
											k = pstmt1.executeUpdate();
											System.out.println("No of row inserted porcpdet ="+k);
											pstmt1.close();
											pstmt1 = null;
										}
									}
									else
									{
										sql = " update porcpdet set  loc_code = ? ,lot_no = ? ,lot_sl = ?  where tran_id = ? and line_no = ?  ";
										pstmt1 =conn.prepareStatement(sql);
										pstmt1.setString(1,locCodeTo);
										pstmt1.setString(2,lotNoTo);
										pstmt1.setString(3,lotSlTo);
										pstmt1.setString(4,refId);
										pstmt1.setInt(5,refLine);
										k = pstmt1.executeUpdate();
										System.out.println("No of row updated porcpdet ="+k);
										pstmt1.close();
										pstmt1 = null;
										
									}
									
								}
								else if("R-BFS".equalsIgnoreCase(refSer.trim())) // Receipt Back Flush  
								{
									System.out.println("update for Receipt Back Flush");
									rate = 0;
									maxLine = 0;
									sql = " select rate from receipt_backflush_det  where tran_id = ?   ";
									pstmt2 = conn.prepareStatement(sql);
									pstmt2.setString(1, refId);
									pstmt2.setInt(2, refLine);
									rs2 = pstmt2.executeQuery();
									if(rs2.next())
									{
										rate = rs2.getDouble("rate");
									}
									rs2.close();
									rs2 = null;
									pstmt2.close();
									pstmt2 = null;

									System.out.println("update..  "+rate+"    "+quantity+"       "+splitQty);

									if(splitQty > 0)//insert new record for split quantity
									{
										sql = " update receipt_backflush_det set  loc_code = ? ,lot_no = ? ,lot_sl = ? ,quantity = ?,amount = ?,net_amt = ? where tran_id = ? and line_no = ?   ";
										pstmt1 =conn.prepareStatement(sql);
										pstmt1.setString(1,locCodeFr);
										pstmt1.setString(2,lotNoFr);
										pstmt1.setString(3,lotSlFr);
										pstmt1.setDouble(4,splitQty);
										pstmt1.setDouble(5,rate * splitQty);
										pstmt1.setDouble(6,rate * splitQty);
										pstmt1.setString(7,refId);
										pstmt1.setInt(8,refLine);
										k = pstmt1.executeUpdate();
										System.out.println("No of row updated receipt_backflush_det ="+k);
										pstmt1.close();
										pstmt1 = null;
										if(k > 0)
										{
											sql = " select select max(line_no) from receipt_backflush_det  where tran_id = ?  ";
											pstmt2 = conn.prepareStatement(sql);
											pstmt2.setString(1, refId);
											rs2 = pstmt2.executeQuery();
											if(rs2.next())
											{
												maxLine = rs2.getInt(1);
											}
											rs2.close();
											rs2 = null;
											pstmt2.close();
											pstmt2 = null;

											maxLine++;

											sql = " insert into receipt_backflush_det (tran_id,line_no,site_code,loc_code,lot_no,lot_sl,item_code,"
											     +" ref_no,quantity,rate,amount,tax_amt,discount,net_amt,unit,unit__doc,conv_qty_doc,qty_doc,rate_doc,no_art) "
												 +" select tran_id,"+maxLine+",site_code,'"+locCodeTo+"','"+lotNoTo+"','"+lotSlTo+"',item_code,ref_no,"+quantity+",rate,"+quantity*rate+",tax_amt,"
											     +" discount," +quantity*rate+",unit,unit__doc,conv_qty_doc,qty_doc,rate_doc,no_art from receipt_backflush_det where tran_id = ? and line_no = ? ";
											System.out.println("insert qry="+sql);
											pstmt1 =conn.prepareStatement(sql);
											pstmt1.setString(1,refId);
											pstmt1.setInt(2,refLine);
											k = pstmt1.executeUpdate();
											System.out.println("No of row inserted receipt_backflush_det ="+k);
											pstmt1.close();
											pstmt1 = null;
										}
									}
									else
									{
										sql = " update receipt_backflush_det set  loc_code = ? ,lot_no = ? ,lot_sl = ?  where tran_id = ? and line_no = ?   ";
										pstmt1 =conn.prepareStatement(sql);
										pstmt1.setString(1,locCodeTo);
										pstmt1.setString(2,lotNoTo);
										pstmt1.setString(3,lotSlTo);
										pstmt1.setString(4,refId);
										pstmt1.setInt(5,refLine);
										k = pstmt1.executeUpdate();
										System.out.println("No of row updated receipt_backflush_det ="+k);
										pstmt1.close();
										pstmt1 = null;
									}
								}
								else if("S-RET".equalsIgnoreCase(refSer.trim())) // (Sales Return)SRETURN.   
								{
									System.out.println("update for SRETURN.");
									rate = 0;
									maxLine = 0;
									sql = " select rate from receipt_backflush_det where tran_id = ? and line_no = ?  ";
									pstmt2 = conn.prepareStatement(sql);
									pstmt2.setString(1, refId);
									pstmt2.setInt(2, refLine);
									rs2 = pstmt2.executeQuery();
									if(rs2.next())
									{
										rate = rs2.getDouble("rate");
									}
									rs2.close();
									rs2 = null;
									pstmt2.close();
									pstmt2 = null;
									

									System.out.println("update...  "+rate+"    "+quantity+"       "+splitQty);

									if(splitQty > 0)//insert new record for split quantity
									{
										sql = " update sreturndet set  loc_code = ? ,lot_no = ? ,lot_sl = ? ,quantity = ?,quantity__stduom = ?,net_amt = ?  where tran_id = ? and line_no = ?   ";
										pstmt1 =conn.prepareStatement(sql);
										pstmt1.setString(1,locCodeFr);
										pstmt1.setString(2,lotNoFr);
										pstmt1.setString(3,lotSlFr);
										pstmt1.setDouble(4,splitQty);
										pstmt1.setDouble(5,rate * splitQty);
										pstmt1.setDouble(6,rate * splitQty);
										pstmt1.setString(7,refId);
										pstmt1.setInt(8,refLine);
										k = pstmt1.executeUpdate();
										System.out.println("No of row updated sreturndet ="+k);
										pstmt1.close();
										pstmt1 = null;

										if(k > 0)
										{
											sql = " select max(line_no)   from sreturndet  where  tran_id = ? ";
											pstmt2 = conn.prepareStatement(sql);
											pstmt2.setString(1, refId);
											rs2 = pstmt2.executeQuery();
											if(rs2.next())
											{
												maxLine = rs2.getInt(1);
											}
											rs2.close();
											rs2 = null;
											pstmt2.close();
											pstmt2 = null;

											maxLine++;

											sql = " insert into sreturndet (tran_id,line_no,invoice_id,line_no__inv,item_code,quantity,net_amt,status,"
											     +" reas_code,loc_code,stk_opt,rate,lot_no,lot_sl,tax_class,tax_chap,tax_env,unit,chg_date,chg_user,chg_term,"
												 +" ret_rep_flag,eff_net_amt,conv__qty_stduom,conv__rtuom_stduom,unit__std,quantity__stduom,rate__stduom,exp_date,discount,tax_amt,"
											     +" site_code__mfg,mfg_date,unit__rate,pack_code,full_ret,item_ser,reas_code__org,no_art,rate__clg,rate__stk,cost_rate,"
												 +" expiry_deduction,rate__std,claim_qty,physical_qty,rate_std,qc_reqd,line_no__invtrace,mrp_value,sale_order,physical_status,line_type,"
											     +" invoice_id__club,part_qty,cust_item__ref,gross_weight,tare_weight,net_weight,cust_item__code,sord_line_no,contract_no,line_no__sform) "
												 +" select  tran_id,"+maxLine+",invoice_id,line_no__inv,item_code,"+quantity+","+quantity * rate+",status,reas_code,'"+locCodeTo+"',stk_opt,"
											     +" rate,'"+lotNoTo+"','"+lotSlTo+"',tax_class,tax_chap,tax_env,unit,chg_date,chg_user,chg_term,ret_rep_flag,eff_net_amt,conv__qty_stduom,"
												 +" conv__rtuom_stduom,unit__std,"+quantity+",rate__stduom,exp_date,discount,tax_amt,site_code__mfg,mfg_date,unit__rate,pack_code,full_ret,"
											     +" item_ser,reas_code__org,no_art,rate__clg,rate__stk,cost_rate,expiry_deduction,rate__std,claim_qty,physical_qty,rate_std,qc_reqd,"
												 +" line_no__invtrace,mrp_value,sale_order,physical_status,line_type,invoice_id__club,part_qty,cust_item__ref,gross_weight,tare_weight,"
											     +" net_weight,cust_item__code,sord_line_no,contract_no,line_no__sform from sreturndet where  tran_id = ? and  line_no = ? ";
											System.out.println("insert qry="+sql);
											pstmt1 =conn.prepareStatement(sql);
											pstmt1.setString(1,refId);
											pstmt1.setInt(2,refLine);
											k = pstmt1.executeUpdate();
											System.out.println("No of row inserted adj_issrcpdet ="+k);
											pstmt1.close();
											pstmt1 = null;
										}
									}
									else
									{
										sql = " update sreturndet set  loc_code = ? ,lot_no = ? ,lot_sl = ?  where tran_id = ? and line_no = ?   ";
										pstmt1 =conn.prepareStatement(sql);
										pstmt1.setString(1,locCodeTo);
										pstmt1.setString(2,lotNoTo);
										pstmt1.setString(3,lotSlTo);
										pstmt1.setString(4,refId);
										pstmt1.setInt(5,refLine);
										k = pstmt1.executeUpdate();
										System.out.println("No of row updated sreturndet ="+k);
										pstmt1.close();
										pstmt1 = null;
									}
								}
								else if("W-ISS".equalsIgnoreCase(refSer.trim())) // Work order ISS .   
								{
									sql = " update workorder_issdet set  loc_code = ? ,lot_no = ? ,lot_sl = ?  where tran_id = ? and line_no = ?   ";
									pstmt1 =conn.prepareStatement(sql);
									pstmt1.setString(1,locCodeTo);
									pstmt1.setString(2,lotNoTo);
									pstmt1.setString(3,lotSlTo);
									pstmt1.setString(4,refId);
									pstmt1.setInt(5,refLine);
									k = pstmt1.executeUpdate();
									System.out.println("No of row updated workorder_issdet ="+k);
									pstmt1.close();
									pstmt1 = null;

								}
								else if("S-ALC".equalsIgnoreCase(refSer.trim())) // Manual Sorder Allocation   
								{
									System.out.println("update for Manual Sorder Allocation");
									
									System.out.println("update..  "+quantity+"       "+splitQty);

									if(splitQty > 0)//insert new record for split quantity
									{
										sql = " update sordalloc set  loc_code = ? ,lot_no = ?  ,lot_sl = ? , quantity = ?,quantity__stduom = ? where sale_order = ? and line_no = ?  ";
										pstmt1 =conn.prepareStatement(sql);
										pstmt1.setString(1,locCodeFr);
										pstmt1.setString(2,lotNoFr);
										pstmt1.setString(3,lotSlFr);
										pstmt1.setDouble(4,splitQty);
										pstmt1.setDouble(5,splitQty);
										pstmt1.setString(6,refId);
										pstmt1.setInt(7,refLine);
										k = pstmt1.executeUpdate();
										System.out.println("No of row updated sordalloc ="+k);
										pstmt1.close();
										pstmt1 = null;

										if(k > 0)
										{

											sql = " insert into sordalloc (sale_order,line_no,exp_lev,item_code__ord,item_code,lot_no,lot_sl,loc_code,item_ref,quantity,unit,qty_alloc,"
											     +" date_alloc,status,item_grade,exp_date,alloc_mode,site_code,conv__qty_stduom,unit__std,quantity__stduom,mfg_date,site_code__mfg,reas_code,ref_id__alloc,ref_line__no,wave_flag) "
												 +" select sale_order,line_no,exp_lev,item_code__ord,item_code,'"+lotNoTo+"','"+lotSlTo+"','"+locCodeTo+"',item_ref,"+quantity+",unit,qty_alloc,date_alloc,"
											     +" status,item_grade,exp_date,alloc_mode,site_code,conv__qty_stduom,unit__std,"+quantity+",mfg_date,site_code__mfg,reas_code,"
												 +" ref_id__alloc,ref_line__no,wave_flag from  sordalloc where  SALE_ORDER = ? and  LINE_NO = ? "
											     +" and site_code = ? and loc_code = ? and item_code = ? and lot_no = ? and lot_sl = ?  ";
											System.out.println("insert qry="+sql);
											pstmt1 =conn.prepareStatement(sql);
											pstmt1.setString(1,refId);
											pstmt1.setInt(2,refLine);
											pstmt1.setString(3,siteCode);
											pstmt1.setString(4,locCodeFr);
											pstmt1.setString(5,itemCode);
											pstmt1.setString(6,lotNoFr);
											pstmt1.setString(7,lotSlFr);
											k = pstmt1.executeUpdate();
											System.out.println("No of row inserted adj_issrcpdet ="+k);
											pstmt1.close();
											pstmt1 = null;
										}
									}
									else
									{
										sql = " update sordalloc set  loc_code = ? ,lot_no = ?  ,lot_sl = ?  where sale_order = ? and line_no = ?  ";
										pstmt1 =conn.prepareStatement(sql);
										pstmt1.setString(1,locCodeTo);
										pstmt1.setString(2,lotNoTo);
										pstmt1.setString(3,lotSlTo);
										pstmt1.setString(4,refId);
										pstmt1.setInt(5,refLine);
										k = pstmt1.executeUpdate();
										System.out.println("No of row updated sordalloc ="+k);
										pstmt1.close();
										pstmt1 = null;

									}
								}
							}

						}while(rs.next());
						//end rs loop

					}
				}
			}

			rs.close();
			rs = null;
			if(pstmt != null)
				pstmt.close();
			pstmt = null;
			if(stmt != null)
				stmt.close();
			stmt = null;
			if(conn != null)
			 conn.commit();
			System.out.println("after invtracealloc check .. pendingQty =="+pendingQty+"  quantitydet="+quantitydet);
			quantitydet = pendingQty;

			if(pendingQty > 0)
			{

				if(checkWOIssue)//if qty alloc in work order
				{
					stockQty = 0;
					System.out.println("qty alloc in work order ");
					sql = " select inv_allocate.work_order ,inv_alloc_det.tran_id, inv_alloc_det.line_no ,inv_alloc_det.alloc_qty from inv_allocate ,  inv_alloc_det where  inv_allocate.tran_id  =  inv_alloc_det.tran_id "
							+" and case when inv_alloc_det.deallocated is null then 'N' else inv_alloc_det.deallocated end != 'Y' "
							+" and inv_alloc_det.site_code = ? and inv_alloc_det.loc_code = ? and inv_alloc_det.item_code = ? "
							+" and inv_alloc_det.lot_no = ? and inv_alloc_det.lot_sl = ?  order by inv_alloc_det.alloc_qty desc  " ;
					pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY,ResultSet.HOLD_CURSORS_OVER_COMMIT);
					pstmt.setString(1, siteCode);
					pstmt.setString(2, locCodeFr);
					pstmt.setString(3, itemCode);
					pstmt.setString(4, lotNoFr);
					pstmt.setString(5, lotSlFr);
					rs = pstmt.executeQuery();
					while(rs.next())
					{

						refId = checkNull(rs.getString( "tran_id" ));
						//refLine = rs.getInt("ref_line");
						refLine = Integer.parseInt( checkNull(rs.getString("line_no")).trim().length() == 0 ?"0":rs.getString("line_no").trim()) ;
						refQty = rs.getDouble("alloc_qty");
						workorder = rs.getString("work_order");
						System.out.println("pendingQty="+pendingQty);
						System.out.println("refSer="+refSer);
						System.out.println("refId="+refId);
						System.out.println("refLine="+refLine);
						System.out.println("refQty="+refQty);
						if(pendingQty <= 0)
						{
							break;
						}
						if(pendingQty <= refQty)
						{
							quantity = pendingQty;
							pendingQty = 0;
						}
						else
						{
							quantity = refQty;
							pendingQty = pendingQty - refQty ;
						}
						stockQty = stockQty + quantity;

						sql = " select workorder_issdet.tran_id ,workorder_issdet.line_no  from workorder_iss , workorder_issdet  where workorder_iss.tran_id = workorder_issdet.tran_id "
								+" and workorder_iss.work_order = ?  and workorder_iss.site_code = ? "
								+" and workorder_issdet.loc_code = ? and workorder_issdet.item_code = ?  and workorder_issdet.lot_no = ? and workorder_issdet.lot_sl = ? ";
						pstmt1 = conn.prepareStatement(sql);
						pstmt1.setString(1, workorder);
						pstmt1.setString(2, siteCode);
						pstmt1.setString(3, locCodeFr);
						pstmt1.setString(4, itemCode);
						pstmt1.setString(5, lotNoFr);
						pstmt1.setString(6, lotSlFr);
						rs1 = pstmt1.executeQuery();
						while(rs1.next())
						{
							tranId = rs1.getString("tran_id");
							lineNo = rs1.getInt("line_no");

							sql = "  select count(*) from workorder_issdet_sl  where tran_id   = ? ";
							pstmt2 = conn.prepareStatement(sql);
							pstmt2.setString(1, tranId);
							rs2 = pstmt2.executeQuery();
							if(rs2.next())
							{
								count = rs2.getInt(1);
							}
							rs2.close();
							rs2 = null;
							pstmt2.close();
							pstmt2 = null;
							if(count > 0) //if weighing is done then give error 
							{
								retValue.add("Error");
								retValue.add(new Double(quantitydet));
								return retValue;
							}
						}
						rs1.close();
						rs1 = null;
						pstmt1.close();
						pstmt1 = null;

						hashMap = new HashMap();
					//	hashMap.put("tran_date",new java.sql.Date(System.currentTimeMillis()));
						hashMap.put("tran_date",tranDate);
						hashMap.put("ref_ser","W-ISS");
						hashMap.put("ref_id",refId);
						hashMap.put("ref_line",String.valueOf(refLine));					
						hashMap.put("site_code",siteCode);
						hashMap.put("item_code",itemCode);
						hashMap.put("loc_code",locCodeFr);
						hashMap.put("lot_no",lotNoFr);
						hashMap.put("lot_sl",lotSlFr);
						hashMap.put("alloc_qty",new Double(-1*quantity)); 
						hashMap.put("chg_user",GenericUtility.getInstance().getValueFromXTRA_PARAMS(xtraParams,"loginCode"));
						hashMap.put("chg_term",GenericUtility.getInstance().getValueFromXTRA_PARAMS(xtraParams,"termId"));
						errString = invAllocTrace.updateInvallocTrace(hashMap, conn);
						System.out.println("updateInvallocTrace....."+errString);
						if(errString != null && errString.trim().length() > 0)
						{
							retValue.add("Error");
							retValue.add(new Double(quantitydet));
							return retValue;
						}
					}//rs loop end
					System.out.println("Stock Qty="+stockQty+"  pendingQty="+pendingQty);
					if (stockQty > 0 && ( errString == null || errString.trim().length() == 0)) 
					{
						stockMap = tempMap;
						stockMap.put("loc_code", locCodeFr);
						stockMap.put("lot_no", lotNoFr);
						stockMap.put("lot_sl", lotSlFr);
						stockMap.put("tran_type", "I");
						stockMap.put("quantity", ""+stockQty);
						stockMap.put("qty_stduom", ""+stockQty);
						//stock update for tran type = "I"
						errString = stkUpdate.updateStock( stockMap, xtraParams, conn ); //updateStock called only once's 
						System.out.println("stock update FOR I ....."+errString);
						if ( errString != null && errString.trim().length() > 0 )
						{
							retValue.add("Error");
							retValue.add(new Double(quantitydet));
							return retValue;
						}
						else
						{
							stockMap.put("loc_code", locCodeTo);
							stockMap.put("lot_no", lotNoTo);
							stockMap.put("lot_sl", lotSlTo);
							stockMap.put("tran_type", "R");
							//stock update for tran type = "R"
							errString = stkUpdate.updateStock( stockMap, xtraParams, conn ); //updateStock called only once's 
							System.out.println("stock update FOR R ....."+errString);
							if ( errString != null && errString.trim().length() > 0 )
							{
								retValue.add("Error");
								retValue.add(new Double(quantitydet));
								return retValue;
							}
						}
						if ( errString == null || errString.trim().length() == 0)
						{
							if(rs != null)
							{
								rs.first();
								pendingQty = quantitydet;
								System.out.println("iterate rs for deallcation ");
								do 
								{

								    refId = checkNull(rs.getString( "tran_id" ));
									//refLine = rs.getInt("ref_line");
									refLine = Integer.parseInt( checkNull(rs.getString("line_no")).trim().length() == 0 ?"0":rs.getString("line_no").trim()) ;
									refQty = rs.getDouble("alloc_qty");
					    			workorder = rs.getString("work_order");

									System.out.println("pendingQty="+pendingQty);
									System.out.println("refSer="+refSer);
									System.out.println("refId="+refId);
									System.out.println("refLine="+refLine);
									System.out.println("refQty="+refQty);
									if(pendingQty <= 0)
									{
										break;
									}
									if(pendingQty <= refQty)
									{
										quantity = pendingQty;
										pendingQty = 0;
									}
									else
									{
										quantity = refQty;
										pendingQty = pendingQty - refQty ;
									}
									hashMap = new HashMap();
								//	hashMap.put("tran_date",new java.sql.Date(System.currentTimeMillis()));
									hashMap.put("tran_date",tranDate);
									hashMap.put("ref_ser",refSer);
									hashMap.put("ref_id",refId);
									hashMap.put("ref_line",String.valueOf(refLine));					
									hashMap.put("site_code",siteCode);
									hashMap.put("item_code",itemCode);
									hashMap.put("loc_code",locCodeTo);
									hashMap.put("lot_no",lotNoTo);
									hashMap.put("lot_sl",lotSlTo);
									hashMap.put("alloc_qty",new Double(quantity)); 
									hashMap.put("chg_user",GenericUtility.getInstance().getValueFromXTRA_PARAMS(xtraParams,"loginCode"));
									hashMap.put("chg_term",GenericUtility.getInstance().getValueFromXTRA_PARAMS(xtraParams,"termId"));
									errString = invAllocTrace.updateInvallocTrace(hashMap, conn);
									System.out.println("updateInvallocTrace 2....."+errString);
									if ( errString != null && errString.trim().length() > 0 )
									{
										retValue.add("Error");
										retValue.add(new Double(quantitydet));
										return retValue;
									}
									else 
									{

										sql = " update inv_alloc_det set  loc_code = ? ,lot_no = ?  ,lot_sl = ?  where tran_id = ? and line_no = ?  ";
										pstmt1 =conn.prepareStatement(sql);
										pstmt1.setString(1,locCodeTo);
										pstmt1.setString(2,lotNoTo);
										pstmt1.setString(3,lotSlTo);
										pstmt1.setString(4,refId);
										pstmt1.setInt(5,refLine);
										k = pstmt1.executeUpdate();
										System.out.println("No of row updated inv_alloc_det ="+k);
										pstmt1.close();
										pstmt1 = null;

										sql = " update workorder_issdet set   loc_code = ? ,lot_no = ? ,lot_sl = ? where tran_id in  (select tran_id from workorder_iss where work_order = ? and site_code = ? )  "
												+" and loc_code  = ? and item_code = ? and lot_no = ? and lot_sl = ? ";
										pstmt1 =conn.prepareStatement(sql);
										pstmt1.setString(1,locCodeTo);
										pstmt1.setString(2,lotNoTo);
										pstmt1.setString(3,lotSlTo);
										pstmt1.setString(4,workorder);
										pstmt1.setString(5,siteCode);
										pstmt1.setString(6,locCodeFr);
										pstmt1.setString(7,itemCode);
										pstmt1.setString(8,locCodeFr);
										pstmt1.setString(9,lotSlFr);
										k = pstmt1.executeUpdate();
										System.out.println("No of row updated workorder_issdet ="+k);
										pstmt1.close();
										pstmt1 = null;

									}


								}while(rs.next()); //rs 2nd lopp end 
								
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
								if(conn != null)
									 conn.commit();


							}
							
						}

					}
					quantitydet = pendingQty;
				}//check for work order alloc qty
			}//check pending qty 


		} catch (SQLException e) 
		{
			System.out.println("SQLException :allocDeAlloc :SQLException :==>\n"+e.getMessage());
			errString = e.getMessage();
			e.printStackTrace();
			throw new ITMException(e);
		}
		catch (Exception e) 
		{
			System.out.println("Exception :allocDeAlloc : :Exception :==>\n"+e.getMessage());
			errString = e.getMessage();
			e.printStackTrace();
			throw new ITMException(e);
		}
		System.out.println("Befor Map");
		System.out.println("err string::"+errString);
		System.out.println("pending qty::"+quantitydet);
		System.out.println("Befor Map");
		retValue.add(errString);
		retValue.add(new Double(quantitydet));

		return retValue;
	}//end allocTransfer
}