/**
 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.utility.CommonConstants;
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 ibase.webitm.utility.TransIDGenerator;

import java.io.File;
import java.io.FileWriter;
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.Calendar;
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.DistCommon;
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;
import ibase.webitm.ejb.fin.FinCommon;

@Stateless
public class WoTransferPrc extends ProcessEJB implements WoTransferPrcLocal,WoTransferPrcRemote 
{
	
	CommonConstants commonConstants  = new CommonConstants();
	DistCommon disCommon = new DistCommon();
	String userId = null;
	String termId = null;
	double quantity=0,qtyStduom=0,rate = 0,potencyPerc=0,netAmt= 0,grossWeight=0;
	double tareWeight=0,netWeight=0,rateOh=0,grossRate=0,convQtyStduom=0,noArt=0;
	double actualRate=0,batchSize = 0;
	String errString ="",itemCode = "",unit="",tranType = "",siteCode="",locationCode="";
	String lotNo="",lotSl="",checkExpiry="",tranSer="",tranId="",acctCodeCr="",packRef = "";
	String acctCodeDr="",cctrCodeCr = "",cctrCodeDr = "",lineNo="",sorderNo ="",siteCodeMfg="";
	String packCode="",itemSer="",reasCode="",invStat="",sundryType="",sundryCode="";
	String packInstr="",dimension="",acctCodeInv="",cctrCodeInv="",acctCodeOh="",cctrCodeOh="";
	String suppCodeMfg="",grade="",batchNo="",unitAlt="",remarks="",refIdFor="",refSerFor="";
	Timestamp creaDateStr=null,date=null,mfgDate=null,expDate = null,currDate=null,retestDateDate=null;
	Timestamp lastPhycDate=null,tranDate= null,retestDate =null;
	String suppCode="";  
	String chgTerm="",chgUser="";     
	//
	String considerAllocate = "Y",	partialUsed = "";
	//
	String lotSlOrg="";
	
	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="",campNo="";
		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 5-4-0-36 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));
			campNo = checkNull(genericUtility.getColumnValue("campgn_no", headerDom));
			if(campNo ==null || campNo.trim().length() <=0)
			{
				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  in ('R') ";
				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;
			    }
			}
			if(worder != null && worder.trim().length() > 0)
			{
				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(campNo != null && campNo.trim().length() > 0)
			{
				sql = "select sum(quantity) from workorder where CAMPGN_NO = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,campNo);
			    rs = pstmt.executeQuery();
			    if(rs.next())
			    {
			    	quantity = rs.getDouble(1);
			    }
			    if(rs != null)
			    	rs.close();
			    rs = null;
			    if(pstmt != null)
			    	pstmt.close();
			    pstmt = null;
			}
			System.out.println("@@@ work order quantity ["+quantity+"]");
		    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;
			    }
			}
			if(worder != null && worder.trim().length() > 0)
			{
				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;
			}
			if(campNo != null && campNo.trim().length() > 0)
			{
//				sql = "SELECT TRAN_ID FROM INV_ALLOCATE WHERE WORK_ORDER  IN (SELECT WORK_ORDER FROM WORKORDER WHERE CAMPGN_NO = ? ) ";
//				pstmt = conn.prepareStatement(sql);
//				pstmt.setString(1,campNo);
//				rs = pstmt.executeQuery();
//				while(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 Creation::::");
//			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 = ?) ";
			if(worder != null && worder.trim().length() > 0)
			{
				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);
			}
			if(campNo != null && campNo.trim().length() > 0)
			{
				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 IN (SELECT TRAN_ID FROM INV_ALLOCATE WHERE WORK_ORDER  IN (SELECT WORK_ORDER FROM WORKORDER WHERE CAMPGN_NO = ? AND STATUS = 'R')) "
			    		+" and loc_code in(select loc_code from location where inv_stat = ? )  ORDER BY TRAN_ID";
				
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,campNo);
				pstmt.setString(2,invstat1);
			}
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				do
				{
					issQty = rs.getDouble("quantity");
					if(campNo != null && campNo.trim().length() > 0)
					{
						sql = " SELECT WORK_ORDER FROM INV_ALLOCATE WHERE TRAN_ID = ? ";
						pstmt1 = conn.prepareStatement(sql);
						pstmt1.setString(1, rs.getString("tran_id"));
						rs1 = pstmt1.executeQuery();
						if ( rs1.next() )
						{
							worder = rs1.getString(1);
						}
						if(rs1 != null)
							rs1.close();
						rs1 = null;
						if(pstmt1 != null)
							pstmt1.close();
						pstmt1 = null;
					}
					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)
					{
						if(campNo == null || campNo.trim().length() <= 0)
						{
						//allocQty = ((issQty-allocatedQty)/quantity) * Double.parseDouble(batchQuantity);
						allocQty = (issQty/quantity) * Double.parseDouble(batchQuantity);
						}
						if(campNo != null && campNo.trim().length() > 0)
						{
							allocQty = issQty;
						}
					}
					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(integralQty > 0)
					{
						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>");
						}
					}
					else 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));
			if(batchQuantityStr != null && batchQuantityStr.trim().length() > 0)
			batchQuantity = Double.parseDouble(batchQuantityStr);
			System.out.println(" batchQuantity ::"+batchQuantity);
			String campNo = checkNull(genericUtility.getColumnValue("campgn_no", headerDom));
			System.out.println( "campgn no @@@["+ campNo+"]");
			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"))
					{
						if(childNode !=null)
						{
							if(childNode.getFirstChild() != null)
							{
								tranId = childNode.getFirstChild().getNodeValue();
							}
						}
					}
					if (childNodeName.equals("line_no"))
					{
						if(childNode !=null)
						{
							if(childNode.getFirstChild() != null)
							{
								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"))
					{
						if(childNode !=null)
						{
							if(childNode.getFirstChild() != null)
							{
								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(campNo != null && campNo.trim().length() > 0)
				{
					sql = "select quantity from workorder where work_order = ? ";
					pstmt = connOne.prepareStatement(sql);
					pstmt.setString(1,worder);
				    rs = pstmt.executeQuery();
				    if(rs.next())
				    {
				    	batchQuantity = rs.getDouble(1);
				    }
				    if(rs != null)
				    	rs.close();
				    rs = null;
				    if(pstmt != null)
				    	pstmt.close();
				    pstmt = null;
				}
				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);
					
					StockTransferConf stconf = new StockTransferConf();
					retString = stconf.confirm(tranIdForIssue,xtraParams,"");
					System.out.println("retString from StockTransferConf  :: @:: "+retString);//CONFSUCC
					if(retString.indexOf("CONFSUCC") > -1)
					{ 
							
							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)
							{
								retString = "CONFSUCC";
								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 ;
	//	Connection conn1 = null;
		try
		{
			//ConnDriver connDriver = new ConnDriver();
		//	conn1 = connDriver.getConnectDB("DriverITM");
			//connDriver = null;
			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 = updateStock( stockMap, xtraParams, conn ); //updateStock ALLOC_QTY 
				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 = 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);  // COMMENTED FOR USE EXISTING METHOD 
							errString = updateInvallocTrace(hashMap, conn);
							System.out.println("updateInvallocTrace 2.LOCAL...."+errString);
							if ( errString != null && errString.trim().length() > 0 )
							{
								retValue.add("Error");
								retValue.add(new Double(quantitydet));
								return retValue;
							}
							else  
							{
//								if(conn1 != null)
//								{
//									conn1.commit();
//									conn1.close();
//									conn = null;
//								}
								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(1,locCodeTo);
										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 = 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 = 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
	
	public  String updateStock(HashMap updateStockMap,String xtraParams, Connection conn) throws ITMException, Exception
	{
	     PreparedStatement pstmt = null;
	     ResultSet rs=null;
	    
	    String sql=null,errString=null,mbaseUnit=null,stkOpt=null,qcReqd=null;
		String orderType = null,stockValuation=null,minvStat=null,moverIssue=null,minvtraceNo=null;
		String mfinEntity = null,mcurrCode=null,issCriteria = null,value=null,invAcct=null;		
	    Timestamp stkRetestDate=null,creaDt=null;
		String parmValueStr=null;
		double mrate=0,meffQty=0,mbefQty=0,mafterQty =0,effQty=0,mamount=0,mexcRate=0,mstkQty=0;
		double mallocQty=0,oldGrossValue=0,oldvalue=0,oldQty=0,newValue=0,newGrossValue=0;
		double totalValue=0,totalGrossValue=0,oldValue=0,qtyPerArt=0,tarewtPerArt = 0;
		double grosswtPerArt = 0,parmValue=0;
		double convFact=1;
		int count=0,mshLife=0,update=0;
		String acctCodeDr = "",cctrCodeDr = "", sqlState ="", errorCode = "";
	    Timestamp mtoday = null;
	    Timestamp tempTestDate = null;
	    
		try
		{
			DistCommon distCommon = new DistCommon();
	        FinCommon finCommon = new FinCommon();
	        
	        puplateCommonParameters(updateStockMap);
	        
	        tempTestDate = Timestamp.valueOf("1900-01-01 00:00:00");
	        userId = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginCode");  
			termId =  genericUtility.getValueFromXTRA_PARAMS(xtraParams, "termId");
			
			chgTerm = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "chgTerm" );
			chgUser = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "chgUser" );
			
			if (userId == null || userId.trim().length() == 0)
			{
				userId = "SYSTEM";
			}
			if (termId == null || termId.trim().length() == 0)
			{
				termId = "SYSTEM";
			}
			if(this.tranType.equalsIgnoreCase("R"))
			{
				sql="SELECT MIN(CREA_DATE) FROM STOCK WHERE ITEM_CODE = '"+this.itemCode+"' AND SITE_CODE = '"+this.siteCode+"'"
				+" AND LOT_NO = '"+this.lotNo+"'";
				System.out.println("Query :::- ["+sql+"]");
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					creaDt = rs.getTimestamp(1);
					System.out.println("creaDt Initialized with  MIN(CREA_DATE) of stock:::- ["+creaDt+"]");
				}
				pstmt.close();
				rs.close();
			}
			else
			{
				creaDt = new Timestamp(System.currentTimeMillis()) ;
				System.out.println("creaDt Initialized Current date:::- ["+creaDt+"]");
			}
	
			if (this.tranType.equalsIgnoreCase("I") || this.tranType.equalsIgnoreCase("ID") )
			{
				sql =" select count(1) 	from stock 	where item_code = ? "
				    +" and site_code = ? " 
					+" and loc_code  = ? " 
					+" and lot_no = ? "
					+" and lot_sl =  ? ";
	
	                pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,this.itemCode);
					pstmt.setString(2,this.siteCode);
					pstmt.setString(3,this.locationCode);
					pstmt.setString(4,this.lotNo);
					pstmt.setString(5,this.lotSl);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						count = rs.getInt(1);
					}
					pstmt.close();
					pstmt = null;
					rs.close();
					rs = null;
					if (count == 0)
					{
						errString = "VTSTOCK1";
						errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);
						return errString;
					}
					
			}
	        
	        /*if gs_run_mode <> 'B' then//Batch load to be checked, currently the current Date 
	            s_updatestock.currdate = ldt_today // and create date is taken as current timestamp
	        else
	            s_updatestock.currdate = s_updatestock.trandate
	        end if*/
	        
	        if(currDate == null){
	            this.currDate = new Timestamp(System.currentTimeMillis());
	        }
			if(creaDt == null){
				creaDt = this.currDate;                
			}
	        System.out.println("this.currDate "+this.currDate);
			System.out.println("creaDt :::- ["+creaDt+"]");
	        if(this.invStat == null || this.invStat.trim().length()==0)
			{
				sql="SELECT INV_STAT FROM LOCATION 	WHERE LOC_CODE = '"+this.locationCode+"'";
				System.out.println("Query :::- ["+sql+"]");
	            pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					this.invStat = rs.getString(1);
					if(this.invStat!=null)
	                    System.out.println("INV_STAT FROM LOCATION :::- ["+this.invStat+"]");
				}
				pstmt.close();
				rs.close();
			}
			
			sql =" SELECT COST_RATE,UNIT,SHELF_LIFE,(CASE WHEN STK_OPT IS NULL THEN '0' ELSE STK_OPT END ),"
			+" (CASE WHEN QC_REQD IS NULL THEN 'N' ELSE QC_REQD END ) FROM ITEM WHERE ITEM_CODE = '"+this.itemCode+"'";
			System.out.println("Query ::- ["+sql+"]");
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				mrate = rs.getDouble(1);
				mbaseUnit = rs.getString(2);
				mshLife = rs.getInt(3);
				stkOpt = rs.getString(4);
				qcReqd =  rs.getString(5);
				
			}
			pstmt.close();
			rs.close();
			
			sql = "SELECT ORDER_TYPE FROM WORKORDER WHERE WORK_ORDER ='"+this.sorderNo+"'";
			System.out.println("Query :::- ["+sql+"]");
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				orderType = rs.getString(1);
			}
			pstmt.close();
			rs.close();
						
			if(orderType != null && orderType.equalsIgnoreCase("T"))
			{
				stkOpt = checkStkOptRnd(this.itemCode,this.siteCode,conn);
			}
			if(stkOpt == null || stkOpt.trim().length() == 0)
			{
				stkOpt = checkStkOpt(this.itemCode,this.siteCode,conn);
				System.out.println("stkOpt[gfChkStkOpt] :::- ["+stkOpt+"]");
			}else{
	            System.out.println("[gbfChkStkOptRnd] :::- ["+stkOpt+"]");
			}
			if(this.itemSer == null || this.itemSer.trim().length()==0)
			{
				sql = "SELECT ITEM_SER FROM ITEM WHERE ITEM_CODE = '"+this.itemCode+"'" ; 
				System.out.println("Query :::- ["+sql+"]");
	            pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					this.itemSer = rs.getString(1);
				}
				pstmt.close();
				rs.close();
			}
			sql = "	SELECT 	(CASE WHEN STOCK_VALUATION IS NULL THEN 'N' ELSE STOCK_VALUATION END )"
			+" FROM ITEMSER WHERE 	ITEM_SER = '"+this.itemSer+"'" ;
			System.out.println("Query :::- ["+sql+"]");
	        pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				stockValuation = rs.getString(1);
			}
			pstmt.close();
			rs.close();
			if(stockValuation == null) stockValuation="N";
			if (this.grade == null || this.grade.trim().length() == 0) this.grade = "   ";
			System.out.println("stkOpt :::- ["+stkOpt+"]");
			if(stkOpt.equalsIgnoreCase("0"))
			{
				System.out.println("Not To Update Stock>>>>>>>>>>>>>>>>>>>>>>>>");
	            return errString;
			}
			if(stkOpt.equalsIgnoreCase("1"))
			{
				if (this.tranType.equalsIgnoreCase("R"))
				{
					String lotNoChg = this.grade +"               ";
					this.lotNo = lotNoChg.substring(0,15);
					this.lotSl= "     ";
				}
			}
			//*********Calculating conversion quantity
			meffQty =0;
			if(this.qtyStduom == 0)
			{
				if(!this.unit.equalsIgnoreCase(mbaseUnit))
				{
					if(this.convQtyStduom != 0)
					{
						convFact = this.convQtyStduom;
					}
					ArrayList convQtyList = disCommon.getConvQuantityFact(this.unit, mbaseUnit, this.itemCode, this.quantity, convFact,conn);
					meffQty = Double.parseDouble(convQtyList.get(1).toString());
					if(meffQty == -999999999)
					{
						errString = "VMUCNV1";
						errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);
						return errString;
					
					}
					if (this.convQtyStduom == 0)
					{
						this.convQtyStduom= Double.parseDouble(convQtyList.get(0).toString());
					}
					
				}
				else
				{
					meffQty = this.quantity ;
					if (this.convQtyStduom == 0) 
					{
						this.convQtyStduom=1;
					}
					
				}
				this.qtyStduom = meffQty;
			}
			if(this.lotNo == null || this.lotNo.trim().length() == 0)
			{
				this.lotNo ="               ";
			}
			if(this.lotSl == null || this.lotSl.trim().length()==0)
			{
				this.lotSl = "     ";
			}
	
			if ((!this.tranSer.equalsIgnoreCase("QC-ORD")) &&  (!this.tranSer.equalsIgnoreCase("I-PKR")) && (!this.tranSer.equalsIgnoreCase("I-PKI")) && (!this.tranType.equalsIgnoreCase("R")) && (!this.tranSer.equalsIgnoreCase("S-ISS")) && (!this.tranSer.equalsIgnoreCase("QC-TRF") )&& (!this.tranSer.equalsIgnoreCase("PR-AMD")) && (!this.tranSer.equalsIgnoreCase("W-QCS")) )
			{
				sql=" SELECT COUNT(*) FROM QC_ORDER WHERE  SITE_CODE = '"+this.siteCode+"'"
					+"AND	 LOC_CODE  = '"+this.locationCode+"'"
					+"AND	 ITEM_CODE = '"+this.itemCode+"'"
					+"AND	 LOT_NO 	  = '"+this.lotNo+"'"
					+"AND	 LOT_SL 	  = '"+this.lotSl+"'"
					+"AND	 STATUS 	 <> 'C'"; 
				System.out.println("Query :::- ["+sql+"]");
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
				if(rs.next()){
	                count = rs.getInt(1);
				}
				pstmt.close();
				rs.close();
	            System.out.println("Record from QC_ORDER:::- ["+count+"]");
	            
	            if(count == 0)
	            {
	                sql =" SELECT COUNT(*)  FROM  QC_ORDER WHERE  SITE_CODE = '"+this.siteCode+"'"
	                    +" AND LOC_CODE  = '"+this.locationCode+"'"
	                    +"AND ITEM_CODE = '"+this.itemCode+"'"
	                    +"AND  LOT_NO     = '"+this.lotNo+"'"
	                    +"AND    LOT_SL IS NULL "
	                    +"  AND  STATUS      <> 'C' ";
	                    System.out.println("Query :::- ["+sql+"]");
	                    pstmt = conn.prepareStatement(sql);
	                    rs = pstmt.executeQuery();
	                    if(rs.next())
	                    {
	                        count = rs.getInt(1);
	                    }
	                    pstmt.close();
	                    rs.close();
	                    System.out.println("Record from QC_ORDER:::- ["+count+"]");
	            }
			/*	//Script for updating loc_code & lot_sl as null if qc order is found during stock transfer
				//Error While be bypass if transer = 'XFRX' and trantype = 'ID' i.e Stock transfer and quantity < 0 i.e Not Receipt 
				if s_updatestock.transer = 'XFRX' and s_updatestock.trantype = 'ID' and s_updatestock.quantity < 0 Then 
				else
					populateerror(9999,'populateerror')
					ls_errcode = 'VTUNCFQC'
					ls_errcode = gf_error_location(ls_errcode)
					return ls_errcode
				end if
			*/
				if( (count > 0) && !("XFRX".equalsIgnoreCase(this.tranSer) && "ID".equalsIgnoreCase(this.tranType)  && meffQty < 0) )
				{
	                errString = "VTUNCFQC";
	                errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);
	                return errString;
	            }
			}
	
			if (this.tranType.equalsIgnoreCase("I") || this.tranType.equalsIgnoreCase("ID") )
			{
				sql =" select rate 	from stock 	where item_code = '"+this.itemCode+"'"
				    +" and site_code = '"+this.siteCode+"'" 
					+" and loc_code  = '"+this.locationCode+"'" 
					+" and lot_no = '"+this.lotNo+"'"
					+" and lot_sl = '"+this.lotSl+"'";
					System.out.println("Query :::- ["+sql+"]");
	                pstmt = conn.prepareStatement(sql);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						this.rate = rs.getDouble(1);
					}
					pstmt.close();
					rs.close();
	                ///////////////////
	                errString = calcWeights(conn);
	                if(errString != null && errString.trim().length() >0){ 
	                    errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);
	                    return errString;
	                }
			}
			if (CommonConstants.DB_NAME.equalsIgnoreCase("DB2")){
				sql =" select quantity, inv_stat from stock where item_code = '"+this.itemCode+"'"
			  	+" and site_code = '"+this.siteCode+"'" 
				+" and loc_code  = '"+this.locationCode+"'" 
				+" and lot_no = '"+this.lotNo+"'"
				+"and lot_sl = '"+this.lotSl+"' for update ";
			}
			else if (CommonConstants.DB_NAME.equalsIgnoreCase("MSSQL")){
				sql =" select quantity, inv_stat from stock (updlock) where item_code = '"+this.itemCode+"'"
			  	+" and site_code = '"+this.siteCode+"'" 
				+" and loc_code  = '"+this.locationCode+"'" 
				+" and lot_no = '"+this.lotNo+"'"
				+"and lot_sl = '"+this.lotSl+"'  ";
			}
			else
			{
				sql =" select quantity, inv_stat from stock where item_code = '"+this.itemCode+"'"
			  	+" and site_code = '"+this.siteCode+"'" 
				+" and loc_code  = '"+this.locationCode+"'" 
				+" and lot_no = '"+this.lotNo+"'"
				+"and lot_sl = '"+this.lotSl+"' for update nowait ";
		
			}
			System.out.println("Query :::- ["+sql+"]");
	        pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				mbefQty=rs.getDouble(1);
				minvStat=rs.getString(2);				
			}
			pstmt.close();
			rs.close();
			if (this.tranType.equalsIgnoreCase("R"))
			{
				mafterQty = mbefQty + this.qtyStduom;
				effQty = this.qtyStduom;
			}
			else
			{
				mafterQty = mbefQty - this.qtyStduom;
				effQty = this.qtyStduom * -1;
			}
			//****************
			if((!this.tranType.equalsIgnoreCase("R")) && (!this.tranType.equalsIgnoreCase("D")))
			{
				if(mbefQty < this.qtyStduom)
				{
					if(this.invStat != null && this.invStat.trim().length() > 0){
						sql = "select overiss  from invstat where inv_stat = '"+this.invStat+"'";
					}
					else{
						sql = "select overiss  from invstat	where inv_stat = '"+minvStat+"'";
					}
	                System.out.println("Query :::- ["+sql+"]");
					pstmt = conn.prepareStatement(sql);
					rs = pstmt.executeQuery();
					if(rs.next()){
						moverIssue=(rs.getString(1)== null ? "":rs.getString(1));
					}
					pstmt.close();
					rs.close();
					if(!moverIssue.equalsIgnoreCase("Y")){
						errString = "VTOVERISS1";
						errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);
						return errString;
					}
				
				}
			}
	        if(this.grade != null && this.grade.trim().length() == 0){
	            this.grade = "   ";
	        }
			if(!this.tranType.equalsIgnoreCase("R")){
				if((this.acctCodeCr==null )|| this.acctCodeCr.trim().length()==0 || this.cctrCodeCr== null || this.cctrCodeCr.trim().length()==0 ){
					sql = "select acct_code__inv,cctr_code__inv from stock where item_code = '"+this.itemCode+"'"
				  	+" and site_code = '"+this.siteCode+"'" 
					+" and loc_code  = '"+this.locationCode+"'" 
					+" and lot_no = '"+this.lotNo+"'"
					+" and lot_sl = '"+this.lotSl+"'  ";
	                
					System.out.println("Query :::- ["+sql+"]");
					pstmt = conn.prepareStatement(sql);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						this.acctCodeCr=rs.getString(1);
						this.cctrCodeCr = rs.getString(2);
					}
					pstmt.close();
					rs.close();
	            }
			}
			
			if (!this.tranSer.equalsIgnoreCase("SCRRCP")){
				System.out.println("Transer "+tranSer);
			    String tranDateStr = genericUtility.getValidDateString(this.tranDate.toString(),genericUtility.getDBDateFormat(),genericUtility.getApplDateFormat());
				String xmlValues = "<?xml version=\"1.0\" encoding=\"utf-8\"?><Root>";
				xmlValues = xmlValues + "<Header></Header>";
				xmlValues = xmlValues + "<Detail1>";
				xmlValues = xmlValues +	"<tran_id></tran_id>";
				xmlValues = xmlValues + "<site_code>" + this.siteCode + "</site_code>";
				xmlValues = xmlValues + "<tran_date>"+ tranDateStr + "</tran_date>";
				xmlValues = xmlValues + "</Detail1></Root>";
	            minvtraceNo= generateTranId("T_INVTRACE",xmlValues,conn);
	            System.out.println("Generated Tran id ::- ["+minvtraceNo+"]");
	            mamount = effQty * this.rate;
	            sql = "Insert into invtrace (tran_id, tran_date, ref_ser, ref_id, ref_line, order_id, item_code, site_code, loc_code, unit,"
	             	+" lot_no, quantity, eff_qty, eff_date, chg_win,  chg_user, chg_term, lot_sl, qty_before, qty_after,"
					+" reas_code,sundry_type,sundry_code, ref_date, rate, gross_rate, grade, acct_code__dr, cctr_code__dr, acct_code__cr, cctr_code__cr,no_art, amount,remarks,"
					+ " gross_weight,tare_weight,net_weight, chg_date, inv_stat,ref_ser__for, ref_id__for) "
					+" values (?, ?, ?, ?,?,?, ?,?,?,?,?, ?,?,?,?, ?,?, ?,?, ?, ?,?,?,?,?,?,?,?,?,?,?, ?,?,?,"
					+"?,?,?, ?, ?, ?,?) ";
	         		pstmt = conn.prepareStatement(sql);
	                System.out.println("Query :::- ["+sql+"]");
	             	pstmt.setString(1,minvtraceNo);
	             	pstmt.setTimestamp(2,this.tranDate);
	             	pstmt.setString(3,this.tranSer);
	             	pstmt.setString(4,this.tranId);
	             	pstmt.setString(5,this.lineNo);
	             	pstmt.setString(6,this.sorderNo);
	             	pstmt.setString(7,this.itemCode);
	             	pstmt.setString(8,this.siteCode);
	             	pstmt.setString(9,this.locationCode);
	             	pstmt.setString(10,mbaseUnit);
	             	pstmt.setString(11,this.lotNo);
	             	pstmt.setDouble(12,Math.abs(this.qtyStduom));
	             	pstmt.setDouble(13,effQty);
	             	pstmt.setTimestamp(14,this.tranDate);
	             	pstmt.setString(15," ");
	             	pstmt.setString(16,userId);
	             	pstmt.setString(17,termId);
	             	pstmt.setString(18,this.lotSl);
	             	pstmt.setDouble(19,mbefQty);
	             	pstmt.setDouble(20,mafterQty);
	             	pstmt.setString(21,this.reasCode);
	             	pstmt.setString(22,this.sundryType);
	             	pstmt.setString(23,this.sundryCode);
	             	pstmt.setTimestamp(24,this.tranDate);
	             	pstmt.setDouble(25,this.rate);
	             	pstmt.setDouble(26,this.grossRate);
	             	pstmt.setString(27,this.grade);
	             	pstmt.setString(28,this.acctCodeDr);
	             	pstmt.setString(29,this.cctrCodeDr);
	             	pstmt.setString(30,this.acctCodeCr);
	             	pstmt.setString(31,this.cctrCodeCr);
	             	pstmt.setDouble(32,this.noArt); 
	             	pstmt.setDouble(33,mamount);
	             	pstmt.setString(34,this.remarks);
	             	pstmt.setDouble(35,this.grossWeight);
	             	pstmt.setDouble(36,this.tareWeight);
	             	pstmt.setDouble(37,this.netWeight);
	             	pstmt.setTimestamp(38,new Timestamp(System.currentTimeMillis()));//chgdate
	             	pstmt.setString(39,this.invStat);
	             	pstmt.setString(40,this.refSerFor);
	             	pstmt.setString(41,this.refIdFor);
					
					update = pstmt.executeUpdate();
	                System.out.println("No recore Insert invtrace ::- ["+update+"]");
					pstmt.close();
		
			}
	        //errString = updateStockMovement(conn);
	        if(errString != null && errString.trim().length() > 0) 
	            return errString;
	        
			
			sql = "select fin_entity  from site where site_code = '"+this.siteCode+"'";
	        System.out.println("Query :::- ["+sql+"]");
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				mfinEntity=rs.getString(1);				
			}
			pstmt.close();
			rs.close();
			
			sql = "select curr_code  from parameter";
	        System.out.println("Query :::- ["+sql+"]");
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				mcurrCode=rs.getString(1);				
			}
			pstmt.close();
			rs.close();
			
			mexcRate = 1;
	        
	        //This is commented as this is exp date not used further in PB logic 
	        //if required the code will be added
			/*if isnull(mshlife) or mshlife = 0 then
			setnull(mexpdate)
			else
			mnewexpdt = relativedate(date(s_updatestock.trandate), mshlife)
			mexpdate  = datetime(mnewexpdt)
			end if*/
			//End
	        
	        mamount = this.quantity * this.rate;
			count=0;
	//		*********xxxxxxxxxxxxxxxx********************
			sql = "select count(*) from stock where item_code ='"+this.itemCode.trim()+"'" 
			+" and site_code ='"+this.siteCode.trim()+"'"
			+" and loc_code = '"+this.locationCode.trim()+"'"
			+" and lot_no = '"+this.lotNo.trim()+"'" 
			+" and lot_sl = '"+this.lotSl.trim()+"'";
	        System.out.println("Query :::- ["+sql+"]");
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				count=rs.getInt(1);				
			}
			System.out.println("@@@@test :count::::["+count+"]");
			pstmt.close();
			rs.close();
			if(count != 0)
			{
				sql =" select quantity,alloc_qty, inv_stat 	from stock 	where item_code = '"+this.itemCode+"'"
			  	+" and site_code = '"+this.siteCode+"'" 
				+" and loc_code  = '"+this.locationCode+"'" 
				+" and lot_no = '"+this.lotNo+"'"
				+"and lot_sl = '"+this.lotSl+"'  ";
	            System.out.println("Query :::- ["+sql+"]");
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					mstkQty=rs.getDouble(1);
					mallocQty = rs.getDouble(2);
					minvStat = rs.getString(3);					
				}
	            pstmt.close();
	            rs.close();				
			}
			mstkQty = finCommon.getRequiredDecimal(mstkQty,4);
	        mallocQty = finCommon.getRequiredDecimal(mallocQty,4);
	        meffQty = finCommon.getRequiredDecimal(meffQty,4);
			if(this.tranType.equalsIgnoreCase("R"))
			{
				if (stkOpt.equalsIgnoreCase("1") || stockValuation.equalsIgnoreCase("M"))
				{
					sql ="Select   (Case when (sum(Case When quantity IS NULL Then 0 Else quantity End)) is null Then 0 else (sum(Case When quantity IS NULL Then 0 Else quantity End)) end),"
						+"(case when (sum((Case When quantity IS NULL Then 0 Else quantity End )  * (Case When rate IS NULL Then 0 Else rate End ))) is null Then 0 else (sum((Case When quantity IS NULL Then 0 Else quantity End )  * (Case When rate IS NULL Then 0 Else rate End ))) end),"
						+"(case when sum((Case When quantity IS NULL Then 0 Else quantity End ) * (Case When gross_rate IS NULL Then 0 Else gross_rate End )) is null then 0 else sum((Case When quantity IS NULL Then 0 Else quantity End ) * (Case When gross_rate IS NULL Then 0 Else gross_rate End )) end )"
						+"	from 	 stock"
						+" where  item_code = '"+this.itemCode+"'"
						+" and 	 site_code = '"+this.siteCode+"'" 
						+" and 	 grade 	  = '"+this.grade+"'" ;
					System.out.println("Query Fired :::- ["+sql+"]");
	                pstmt = conn.prepareStatement(sql);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						oldQty=rs.getDouble(1);
						oldvalue = rs.getDouble(2);
						oldGrossValue =rs.getDouble(3);
					}
	                pstmt.close();
					rs.close();
					newValue = Math.abs(this.qtyStduom) * this.rate;
					newGrossValue = Math.abs(this.qtyStduom) * this.grossRate;
					totalValue = oldValue + newValue;
					totalGrossValue = oldGrossValue + newGrossValue	;
					if((oldQty + Math.abs(this.qtyStduom)) != 0)
					{
						this.rate = (totalValue)/(oldQty+Math.abs(this.qtyStduom));
						this.grossRate = totalGrossValue / (oldQty + Math.abs(qtyStduom));
					}
				}
				else if(stockValuation.equalsIgnoreCase("C")){
					sql =" Select   (Case when (sum(Case When quantity IS NULL Then 0 Else quantity End)) is null Then 0 else (sum(Case When quantity IS NULL Then 0 Else quantity End)) end),"
						+" (case when (sum((Case When quantity IS NULL Then 0 Else quantity End )  * (Case When rate IS NULL Then 0 Else rate End ))) is null Then 0 else (sum((Case When quantity IS NULL Then 0 Else quantity End )  * (Case When rate IS NULL Then 0 Else rate End ))) end),"
						+" (case when sum((Case When quantity IS NULL Then 0 Else quantity End ) * (Case When gross_rate IS NULL Then 0 Else gross_rate End )) is null then 0 else sum((Case When quantity IS NULL Then 0 Else quantity End ) * (Case When gross_rate IS NULL Then 0 Else gross_rate End )) end )"
						+" from stock "
						+" where  item_code = '"+this.itemCode+"'"
						+" and 	 site_code = '"+this.siteCode+"'" 
						+" and 	 grade 	  = '"+this.grade+"'" 
						+" And	cctr_code__inv = :'"+this.cctrCodeInv+"'";
						System.out.println("Query :::- ["+sql+"]");
	                    pstmt = conn.prepareStatement(sql);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							oldQty=rs.getDouble(1);
							oldvalue = rs.getDouble(2);
							oldGrossValue =rs.getDouble(3);
						}
	                    pstmt.close();
						rs.close();
						newValue = Math.abs(this.qtyStduom) * this.rate;
						newGrossValue = Math.abs(this.qtyStduom) * this.grossRate;
						totalValue = oldValue + newValue;	
						totalGrossValue = oldGrossValue + newGrossValue	;
						if((oldQty + Math.abs(this.qtyStduom))!= 0)
						{
							this.rate = totalValue / (oldQty + Math.abs(this.qtyStduom));
							this.grossRate = totalGrossValue / (oldQty + Math.abs(this.qtyStduom));
				
						}
				}
				
			}//end of tranType =R
	
			if(this.tranType.equalsIgnoreCase("I")){
				mstkQty = (mstkQty - meffQty);
				if (count > 0)
				{
					if(this.tranSer.equalsIgnoreCase("QC-ORD"))
					{
//						sql=" update stock set quantity   = quantity - ? ,"
//						+" ltran_date = ? ,"
//						 +" last_iss_date = ? ,"
//						 +" chg_user   = ? ," 
//						 +" chg_term   = ? , " 
//						 +" chg_date   = ? ,"
//						 +" gross_weight = gross_weight - ? ,"
//						 +" net_weight = net_weight - ? ,"
//						 +" tare_weight = tare_weight - ? ,"
//						 +" last_phyc_date = ? ,"
//						 +" potency_perc = ? " 
//						 +" where item_code = '"+this.itemCode+"'"
//						 +" and site_code = '"+this.siteCode+"'"
//						 +" and loc_code  = '"+this.locationCode+"'" 
//						 +" and lot_no 	 = '"+this.lotNo+"'"
//						 +" and lot_sl 	 = '"+this.lotSl+"'";
//	                    System.out.println("Query :::- ["+sql+"]");
//						pstmt = conn.prepareStatement(sql);
//						pstmt.setDouble(1,this.qtyStduom);
//						pstmt.setTimestamp(2,this.tranDate);
//						pstmt.setTimestamp(3,this.tranDate);
//						pstmt.setString(4,userId);
//						pstmt.setString(5,termId);
//						pstmt.setTimestamp(6,new java.sql.Timestamp(System.currentTimeMillis()));
//						pstmt.setDouble(7,this.grossWeight);
//						pstmt.setDouble(8,this.netWeight);
//						pstmt.setDouble(9,this.tareWeight);
//						pstmt.setTimestamp(10,this.lastPhycDate);
//						pstmt.setDouble(11,this.potencyPerc);
//		
//						
//						pstmt.executeUpdate();
//						pstmt.close();
//						rs.close();
					}else{
//	                    sql=" update stock set quantity   = quantity - ? ,"
//	                        +" ltran_date = ? ,"
//	                        +" last_iss_date = ? ,"
//	                        +" chg_user   = ? ," 
//	                        +" chg_term   = ? , " 
//	                        +" chg_date   = ? ,"
//	                        +" gross_weight = gross_weight - ? ,"
//	                        +" net_weight = net_weight - ? ,"
//	                        +" tare_weight = tare_weight - ? ,"
//	                        +" last_phyc_date = ? "
//	                        +" where item_code = '"+this.itemCode+"'"
//	                        +" and site_code = '"+this.siteCode+"'"
//	                        +" and loc_code  = '"+this.locationCode+"'" 
//	                        +" and lot_no   = '"+this.lotNo+"'"
//	                        +" and lot_sl   = '"+this.lotSl+"'";
//	                        System.out.println("Query :::- ["+sql+"]");
//	                        pstmt = conn.prepareStatement(sql);
//	                        pstmt.setDouble(1,this.qtyStduom);
//	                        pstmt.setTimestamp(2,this.tranDate);
//	                        pstmt.setTimestamp(3,this.tranDate);
//	                        pstmt.setString(4,userId);
//	                        pstmt.setString(5,termId);
//	                        pstmt.setTimestamp(6,new java.sql.Timestamp(System.currentTimeMillis()));
//	                        pstmt.setDouble(7,this.grossWeight);
//	                        pstmt.setDouble(8,this.netWeight);
//	                        pstmt.setDouble(9,this.tareWeight);
//	                        pstmt.setTimestamp(10,this.lastPhycDate);
//	                        
//	                        pstmt.executeUpdate();
//	                        pstmt.close();
//	                        rs.close();
	                }
				}else{
					sql = "select inv_stat from location where loc_code = '"+this.locationCode+"'";
	                pstmt = conn.prepareStatement(sql);
	                rs = pstmt.executeQuery();
	                if(rs.next()){
	                    this.invStat = rs.getString(1);
	                }
	                rs.close();
	                pstmt.close();
	                
	                sql= "select (case when iss_criteria is null then 'I' else iss_criteria end) "
							+" from item where item_code = ? "; //'"+this.itemCode+"'" ;
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,this.itemCode);
					rs = pstmt.executeQuery();
					if(rs.next()){
						issCriteria=rs.getString(1);
					}
	                rs.close();
	                pstmt.close();
					if (this.noArt==0)
					{
						this.noArt = 1;
					}
					if( issCriteria != null && issCriteria.equalsIgnoreCase("W"))
					{
						qtyPerArt=this.qtyStduom;
						tarewtPerArt = this.tareWeight;
						grosswtPerArt = this.grossWeight;
					}
					else
					{
						qtyPerArt = this.qtyStduom/this.noArt;
						tarewtPerArt = this.tareWeight/this.noArt;
						grosswtPerArt = this.grossWeight/this.noArt;
					}
					
					sql = "insert into stock  (item_code,site_code,loc_code, lot_no,lot_sl,unit, quantity,crea_date,chg_user,"
					 	+" chg_date,chg_term,exp_date, alloc_qty,item_ser,mfg_date, site_code__mfg,potency_perc,pack_code,"
						+" inv_stat,ltran_date,last_iss_date,gross_weight,tare_weight,net_weight,pack_instr,dimension,retest_date,"
						+"rate,rate__oh,acct_code__inv, cctr_code__inv,acct_code__oh,cctr_code__oh,supp_code__mfg,grade,gross_rate,"
						+" conv__qty_stduom,unit__alt,batch_no,last_phyc_date,qty_per_art,gross_wt_per_art,tare_wt_per_art,actual_rate,remarks, no_art,pack_ref" 
						+" ,lot_sl__org )"    
						+" values( ?,?,?, ?,?,?, ?,?,?, ?,?,?,?,?,?, ?,?,?, ?,?,?, ?,?,?, ?,?,?, ?,?,?, ?,?,?,"
						+" ?,?,?, ?,?,?, ?,?,?, ?,?,?, ?,?,?)" ;
					 pstmt = conn.prepareStatement(sql);
	
					 pstmt.setString(1,this.itemCode);
					 pstmt.setString(2,this.siteCode);
					 pstmt.setString(3,this.locationCode);
					 pstmt.setString(4,this.lotNo);
					 pstmt.setString(5,this.lotSl);
					 pstmt.setString(6,mbaseUnit);
					 pstmt.setDouble(7,(-1 * this.qtyStduom));
					 pstmt.setTimestamp(8,creaDt);
					 pstmt.setString(9,userId);
					 pstmt.setTimestamp(10,new java.sql.Timestamp(System.currentTimeMillis()));
	
					 pstmt.setString(11,termId);
					 pstmt.setTimestamp(12,this.expDate);
					 pstmt.setString(13,"0");
					 pstmt.setString(14,this.itemSer);
					 pstmt.setTimestamp(15,this.mfgDate);
					 pstmt.setString(16,this.siteCodeMfg);
					 pstmt.setDouble(17,this.potencyPerc);
					 pstmt.setString(18,this.packCode);
					 pstmt.setString(19,this.invStat);
	
					 pstmt.setTimestamp(20,this.currDate);
					 pstmt.setTimestamp(21,this.tranDate);
					 pstmt.setDouble(22,this.grossWeight);
					 pstmt.setDouble(23,this.tareWeight);
					 pstmt.setDouble(24,this.netWeight);
					 pstmt.setString(25,this.packInstr);
					 pstmt.setString(26,this.dimension);
					 pstmt.setTimestamp(27,this.retestDate);
	
					 pstmt.setDouble(28,this.rate);
					 pstmt.setDouble(29,this.rateOh);
					 pstmt.setString(30,this.acctCodeInv);
					 pstmt.setString(31,this.cctrCodeInv);
					 pstmt.setString(32,this.acctCodeOh);
					 pstmt.setString(33,this.cctrCodeOh);
					 pstmt.setString(34,this.suppCodeMfg);
					 pstmt.setString(35,this.grade);
					 pstmt.setDouble(36,this.grossRate);
					 pstmt.setDouble(37,this.convQtyStduom);
					 pstmt.setString(38,this.unitAlt);
	
	
					 pstmt.setString(39,this.batchNo);
					 pstmt.setTimestamp(40,this.lastPhycDate);
					 pstmt.setDouble(41,qtyPerArt);
					 pstmt.setDouble(42,grosswtPerArt);
					 pstmt.setDouble(43,tarewtPerArt);
					 pstmt.setDouble(44,this.actualRate);
					 pstmt.setString(45,this.remarks);
					 pstmt.setString(46,"0");
	                pstmt.setString(47,this.packRef);
	                pstmt.setString(48,this.lotSlOrg);
	                 System.out.println("Query ::- ["+sql+"]");
		             
					update = pstmt.executeUpdate();
	                System.out.println("No recore Insert invtrace ::- ["+update+"]");
					pstmt.close();
					
	            }
	        }
			else if(this.tranType.equalsIgnoreCase("L")){
				mstkQty = (mstkQty - meffQty);
				if (count != 0){
//					sql =" update stock  set quantity = ? ," 
//						+" ltran_date = ? "
//						+" where item_code = '"+this.itemCode+"'" 
//						+" and site_code = '"+this.siteCode +"'"
//						+" and loc_code = '"+this.locationCode +"'"
//						+" and lot_no = '"+this.lotNo+"'"
//						+" and lot_sl = '"+this.lotSl+"'";
//	                System.out.println("Query :::- ["+sql+"]");
//					pstmt = conn.prepareStatement(sql);
//					pstmt.setDouble(1,mstkQty);
//					pstmt.setTimestamp(2,this.tranDate);
//					pstmt.executeUpdate();
//	                rs.close();
//	                pstmt.close();					
	            }
			}//end of tranType (L)
			else if(this.tranType.equalsIgnoreCase("A")){
			    mallocQty = mallocQty + meffQty;
				if (count != 0){
					sql =" update stock  set alloc_qty = ? ," 
						+" ltran_date = ? "
						+" where item_code = '"+this.itemCode+"'" 
						+" and site_code = '"+this.siteCode +"'"
						+" and loc_code = '"+this.locationCode +"'"
						+" and lot_no = '"+this.lotNo+"'"
						+" and lot_sl = '"+this.lotSl+"'";
	                System.out.println("Query :::- ["+sql+"]");
					pstmt = conn.prepareStatement(sql);
					pstmt.setDouble(1,mallocQty);
					pstmt.setTimestamp(2,this.tranDate);
					pstmt.executeUpdate();
	                rs.close();
					pstmt.close();
				}
			}//end of tranType (A)
			else if(this.tranType.equalsIgnoreCase("D")){
				mallocQty = mallocQty - meffQty;
				if (count != 0){
					sql =" update stock  set alloc_qty = ? ," 
						+" ltran_date = ? "
						+" where item_code = '"+this.itemCode+"'" 
						+" and site_code = '"+this.siteCode +"'"
						+" and loc_code = '"+this.locationCode +"'"
						+" and lot_no = '"+this.lotNo+"'"
						+" and lot_sl = '"+this.lotSl+"'";
					System.out.println("Query :::- ["+sql+"]");
	                pstmt = conn.prepareStatement(sql);
					pstmt.setDouble(1,mallocQty);
					pstmt.setTimestamp(2,this.tranDate);
					pstmt.executeUpdate();
					pstmt.close();
					rs.close();
				}
			}//end of tranType (D)
			else if(this.tranType.equalsIgnoreCase("ID")){
				if (count != 0){
	                HashMap invAllocTraceMap = new HashMap();
	                invAllocTraceMap.put("ref_ser",this.tranSer);
	                invAllocTraceMap.put("ref_id",this.tranId);
	                invAllocTraceMap.put("ref_line",this.lineNo);
	                invAllocTraceMap.put("site_code",this.siteCode);
	                invAllocTraceMap.put("item_code",this.itemCode);
	                invAllocTraceMap.put("loc_code",this.locationCode);
	                invAllocTraceMap.put("lot_no",this.lotNo);
	                invAllocTraceMap.put("lot_sl",this.lotSl);
	                invAllocTraceMap.put("alloc_qty",new Double(-1 * this.qtyStduom));
	                invAllocTraceMap.put("chg_user",userId);
	                invAllocTraceMap.put("chg_term",termId);
	                invAllocTraceMap.put("chg_win","  ");
	                
	                InvAllocTraceBean invBean = new InvAllocTraceBean(); 
	                errString = invBean.updateInvallocTrace(invAllocTraceMap,conn);
	                if(errString != null && errString.trim().length() > 0){
	                    errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);
	                    return errString;
	                }
//	                sql ="update stock set quantity = (case when quantity is null then 0 else quantity end) - ? ," 
//					+" ltran_date 	= ? ,"
//					+" last_iss_date = ?,"
//					+" no_art 			= no_art - ?, "
//					+" chg_user   	= ?, "
//					+" chg_term   	= ?, "
//					+" chg_date   	= ?,"
//					+" gross_weight 	= (case when gross_weight is null then 0 else gross_weight end) - ?,"
//					+" net_weight 	= (case when net_weight is null then 0 else net_weight end) - ?,"
//					+" tare_weight 	= (case when tare_weight is null then 0 else tare_weight end) - ? "
//					+" where item_code = '"+this.itemCode+"'" 
//						+"and site_code = '"+this.siteCode +"'"
//						+"and loc_code = '"+this.locationCode +"'"
//						+"and lot_no = '"+this.lotNo+"'"
//						+"and lot_sl = '"+this.lotSl+"'";
//	                System.out.println("Query :::- ["+sql+"]");
//					pstmt = conn.prepareStatement(sql);
//					pstmt.setDouble(1,this.qtyStduom);
//					pstmt.setTimestamp(2,this.tranDate);
//					pstmt.setTimestamp(3,this.tranDate);
//					pstmt.setDouble(4,this.noArt);
//					pstmt.setString(5,userId);
//					pstmt.setString(6,termId);
//					pstmt.setTimestamp(7,new java.sql.Timestamp(System.currentTimeMillis()));
//					pstmt.setDouble(8,this.grossWeight);
//					pstmt.setDouble(9,this.netWeight);
//					pstmt.setDouble(10,this.tareWeight);
//					pstmt.executeUpdate();
//					pstmt.close();
//					rs.close();
				}
	        }
			else if(this.tranType.equalsIgnoreCase("R"))
			{ 
				updateStockMap = updateLotInfo( updateStockMap , conn); 
				
				//if( errString != null && errString.trim().length() > 0)
				//{
				//	return errString;
				//}
				
				if (count > 0)
				{
					sql ="select (case when acct_code__inv is null then '    ' else acct_code__inv end), (case when cctr_code__inv is null then '    ' else cctr_code__inv end), qty_per_art"
						+" from stock "
						+" where item_code = '"+this.itemCode+"'" 
						+" and site_code = '"+this.siteCode +"'"
						+" and loc_code = '"+this.locationCode +"'"
						+" and lot_no = '"+this.lotNo+"'"
						+" and lot_sl = '"+this.lotSl+"'";
	                System.out.println("Query ::- ["+sql+"]");
					pstmt = conn.prepareStatement(sql);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						acctCodeDr = rs.getString(1);
						cctrCodeDr = rs.getString(2);
						qtyPerArt = rs.getDouble(3);
					}
					value = distCommon.getDisparams("999999","STKTRFR_SUBSTORE_CCTR_CHECK",conn);
					if (value.equalsIgnoreCase("NULLFOUND")) value = "Y";
					if(value.equalsIgnoreCase("Y"))
					{
						if (!(acctCodeDr.equalsIgnoreCase(this.acctCodeInv)) ||(!(cctrCodeDr.equalsIgnoreCase(this.cctrCodeInv))))
						{
							invAcct = finCommon.getFinparams("999999", "INVENTORY_ACCT",conn); 
							if(invAcct.equalsIgnoreCase("Y"))
							{
	                            errString = "VTACTMIS";
								errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);
								return errString;
							}
						}
					}
					
					sql = "select retest_date  from stock"
						+" where item_code = '"+this.itemCode+"'" 
						+" and site_code = '"+this.siteCode +"'"
						+" and loc_code = '"+this.locationCode +"'"
						+" and lot_no = '"+this.lotNo+"'"
						+" and lot_sl = '"+this.lotSl+"'";
	                System.out.println("Query ::- ["+sql+"]");
					pstmt = conn.prepareStatement(sql);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						stkRetestDate=rs.getTimestamp(1);
					}
	                if(this.retestDate != null && this.retestDate.compareTo(tempTestDate) == 0){
	                    this.retestDate = stkRetestDate;
	                }
	                if(this.retestDate != null && this.retestDate.compareTo(tempTestDate) == 0){
	                    this.retestDate = null;
	                }
					
					if (this.tranSer.equalsIgnoreCase("QC-ORD"))
					{
//						sql ="update stock 	set quantity   	 = quantity + ? ,"
//							+"ltran_date 	 = ?,"
//							+"last_rcp_date  = ? ,"
//							+"rate 		     = ?,"
//							+"gross_rate  	 = ?,"
//							+"chg_user    	 = ?,"
//							+"chg_term    	 = ?,"
//							+"chg_date    	 = ?,"
//							+"gross_weight	 = gross_weight + ?,"
//							+"net_weight  	 = net_weight + ?,"
//							+"tare_weight 	 = tare_weight + ?,"
//							+"retest_date 	 = ?,"
//							+"no_art		 = ?,"
//							+"dimension   	 = ?,"
//							+"last_phyc_date = ?,"
//							+"potency_perc 	 = ? "
//							+"where item_code = '"+this.itemCode+"'" 
//							+"and site_code = '"+this.siteCode +"'"
//							+"and loc_code = '"+this.locationCode +"'"
//							+"and lot_no = '"+this.lotNo+"'"
//							+"and lot_sl = '"+this.lotSl+"'";
//	                    System.out.println("Query :::- ["+sql+"]");
//						pstmt = conn.prepareStatement(sql);
//						
//	                    pstmt.setDouble(1,this.qtyStduom);
//						pstmt.setTimestamp(2,this.tranDate);
//						pstmt.setTimestamp(3,this.tranDate);
//						pstmt.setDouble(4,this.rate);
//						pstmt.setDouble(5,this.grossRate);
//						pstmt.setString(6,userId);
//						pstmt.setString(7,termId);
//						pstmt.setTimestamp(8,new java.sql.Timestamp(System.currentTimeMillis()));
//						pstmt.setDouble(9,this.grossWeight);
//						pstmt.setDouble(10,this.netWeight);
//						pstmt.setDouble(11,this.tareWeight);
//						pstmt.setTimestamp(12,this.retestDate);
//						pstmt.setDouble(13,this.noArt);
//						pstmt.setString(14,this.dimension);
//						pstmt.setTimestamp(15,this.lastPhycDate);
//						pstmt.setDouble(16,this.potencyPerc);
//						
//	                    pstmt.executeUpdate();
//						pstmt.close();
//						rs.close();
					}
					else
					{
//						sql ="update stock 	set quantity   	 = quantity + ? ,"
//							+"ltran_date 	 = ?,"
//							+"last_rcp_date  = ? ,"
//							+"rate 			 = ?,"
//							+"gross_rate  	 = ?,"
//							+"chg_user    	 = ?,"
//							+"chg_term    	 = ?,"
//							+"chg_date    	 = ?,"
//							+"gross_weight	 = gross_weight + ?,"
//							+"net_weight  	 = net_weight + ?,"
//							+"tare_weight 	 = tare_weight + ?,"
//							+"retest_date 	 = ?,"
//							+"no_art		 	 = no_art + ?,"
//							+"dimension   	 = ?,"
//							+"last_phyc_date = ?,"
//							+"supp_code__mfg 	 = ?," 
//	                        +"batch_no = ? "
//							+"where item_code = '"+this.itemCode+"'" 
//							+"and site_code = '"+this.siteCode+"'"
//							+"and loc_code = '"+this.locationCode+"'"
//							+"and lot_no = '"+this.lotNo+"'"
//							+"and lot_sl = '"+this.lotSl+"'";
//	                    System.out.println("Query :::- ["+sql+"]");
//						pstmt = conn.prepareStatement(sql);
//						pstmt.setDouble(1,this.qtyStduom);
//						pstmt.setTimestamp(2,this.tranDate);
//						pstmt.setTimestamp(3,this.tranDate);
//						pstmt.setDouble(4,this.rate);
//						pstmt.setDouble(5,this.grossRate);
//						pstmt.setString(6,userId);
//						pstmt.setString(7,termId);
//						pstmt.setTimestamp(8,new java.sql.Timestamp(System.currentTimeMillis()));
//						pstmt.setDouble(9,this.grossWeight);
//						pstmt.setDouble(10,this.netWeight);
//						pstmt.setDouble(11,this.tareWeight);
//						pstmt.setTimestamp(12,this.retestDate);
//						pstmt.setDouble(13,this.noArt);
//						pstmt.setString(14,this.dimension);
//						pstmt.setTimestamp(15,this.lastPhycDate);
//						pstmt.setString(16,this.suppCodeMfg);
//	                    pstmt.setString(17,this.batchNo);
//						
//						pstmt.executeUpdate();
//						pstmt.close();
//						rs.close();
					}
				}//end of count
				else
				{
					sql = "select inv_stat from location where loc_code = '"+this.locationCode+"'";                    
					pstmt = conn.prepareStatement(sql);
					rs = pstmt.executeQuery();
	                System.out.println("Query :::- ["+sql+"]");
	                if(rs.next())
					{
	                    this.invStat =rs.getString(1);
					}
					pstmt.close();
					rs.close();
					if(this.tranSer.equalsIgnoreCase("W-IRTN") || this.tranSer.equalsIgnoreCase("C-IRTN") || this.tranSer.equalsIgnoreCase("XFRX"))
					{
						sql = " select Min(crea_date)  from stock "
							+"where item_code = '"+this.itemCode+"'" 
							+"and site_code = '"+this.siteCode +"'"
							+"and lot_no = '"+this.lotNo+"'"
							//+"and lot_sl = '"+this.lotSl+"'" 
							+"and crea_date is not null ";                            
							pstmt = conn.prepareStatement(sql);
							rs = pstmt.executeQuery();
	                        System.out.println("Query :::- ["+sql+"]");
	                        if(rs.next())
							{
								creaDt = rs.getTimestamp(1) == null ? this.currDate : rs.getTimestamp(1); //handle null date ,change done by kunal on 07/nov/13 
							}
							pstmt.close();
							rs.close();
							if(creaDt != null && creaDt.compareTo(tempTestDate) != 0){
								mtoday = creaDt;
							}
					}
					sql="select (case when iss_criteria is null then 'I' else iss_criteria end) "
						+" from item where item_code = '"+this.itemCode+"'" ;
	                System.out.println("Query :::- ["+sql+"]");
					pstmt = conn.prepareStatement(sql);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						issCriteria = rs.getString(1);
					}
					pstmt.close();
					rs.close();
						
					if (issCriteria != null && issCriteria.equalsIgnoreCase("W")){
						qtyPerArt		= this.qtyStduom;
						tarewtPerArt	= this.tareWeight;
						grosswtPerArt	= this.grossWeight;
					}else{
	                    if(this.noArt == 0){
							qtyPerArt		= this.qtyStduom;
							tarewtPerArt	= this.tareWeight;
							grosswtPerArt	= this.grossWeight;
						}
						else{
							qtyPerArt		= this.qtyStduom / this.noArt;
							tarewtPerArt	= this.tareWeight / this.noArt;
							grosswtPerArt	= this.grossWeight / this.noArt;		
						}
					}
		
										
					sql="insert into stock "
						+" (item_code , site_code, loc_code," 
						+" lot_no, lot_sl, unit,"
						+" quantity,crea_date, chg_user,"
						+" chg_date,chg_term, exp_date,"
						+" alloc_qty,item_ser, mfg_date,"
						+" site_code__mfg,potency_perc,pack_code,"
						+" inv_stat,ltran_date,last_rcp_date,"
						+" rate,gross_weight,tare_weight,"
						+" net_weight,pack_instr,dimension,"
						+" retest_date,rate__oh,acct_code__inv,"
						+" cctr_code__inv,acct_code__oh,cctr_code__oh,"
						+" supp_code__mfg,grade, gross_rate,"
						+" conv__qty_stduom,unit__alt,batch_no,"
						+" no_art,last_phyc_date,qty_per_art,"
						+" gross_wt_per_art,tare_wt_per_art,actual_rate,"
						+" remarks,pack_ref,consider_allocate,	partial_used, batch_size" 
						+" ,lot_sl__org ) values "
						+"  (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,"
					    +" ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,"
					    +" ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
					//System.out.println("SQL :"+sql);    
					pstmt=conn.prepareStatement(sql);
					pstmt.setString(1,this.itemCode);
					pstmt.setString(2,this.siteCode);
					pstmt.setString(3,this.locationCode);
	
					pstmt.setString(4,this.lotNo);
					pstmt.setString(5,this.lotSl);
					pstmt.setString(6,mbaseUnit);
	
					pstmt.setDouble(7,this.qtyStduom);
					pstmt.setTimestamp(8,creaDt);
					pstmt.setString(9,userId);
	
					pstmt.setTimestamp(10,new Timestamp(System.currentTimeMillis()));
					pstmt.setString(11,termId);
					pstmt.setTimestamp(12,this.expDate);
	
					pstmt.setString(13,"0");
					pstmt.setString(14,this.itemSer);
					pstmt.setTimestamp(15,this.mfgDate);
	
					pstmt.setString(16,this.siteCodeMfg);
					pstmt.setDouble(17,this.potencyPerc);
					pstmt.setString(18,this.packCode);
	
					pstmt.setString(19,this.invStat);
					pstmt.setTimestamp(20,this.currDate);
					pstmt.setTimestamp(21,this.tranDate);
	
					pstmt.setDouble(22,this.rate);
					pstmt.setDouble(23,this.grossWeight);
					pstmt.setDouble(24,this.tareWeight);
	
					pstmt.setDouble(25,this.netWeight);
					pstmt.setString(26,this.packInstr);
					pstmt.setString(27,this.dimension);
	
					pstmt.setTimestamp(28,this.retestDate);
					pstmt.setDouble(29,this.rateOh);
					pstmt.setString(30,this.acctCodeInv);
	
					pstmt.setString(31,this.cctrCodeInv);
					pstmt.setString(32,this.acctCodeOh);
					pstmt.setString(33,this.cctrCodeOh);
	
					pstmt.setString(34,this.suppCodeMfg);
					pstmt.setString(35,this.grade);
					pstmt.setDouble(36,this.grossRate);
	
					pstmt.setDouble(37,this.convQtyStduom);
					pstmt.setString(38,this.unitAlt);
					pstmt.setString(39,this.batchNo);
	
					pstmt.setDouble(40,this.noArt);
					pstmt.setTimestamp(41,this.lastPhycDate);
					pstmt.setDouble(42,qtyPerArt);
	
					pstmt.setDouble(43,grosswtPerArt);
					pstmt.setDouble(44,tarewtPerArt);
					pstmt.setDouble(45,this.actualRate);
	
					pstmt.setString(46,this.remarks);
	                pstmt.setString(47,this.packRef);
	                pstmt.setString(48,this.considerAllocate);
	                pstmt.setString(49,this.partialUsed);
					pstmt.setDouble(50,this.batchSize);
					pstmt.setString(51,this.lotSlOrg);       
					
	                System.out.println("ITEM_CODE :"+this.itemCode);
	                System.out.println("SITE_CODE :"+this.siteCode);
	                System.out.println("LOC_CODE :"+this.locationCode);
	                System.out.println("LOT_NO :"+this.lotNo);
	                System.out.println("LOT_SL :"+this.lotSl);
	                System.out.println("QUANTITY :"+this.qtyStduom);
	                System.out.println("UNIT :"+mbaseUnit);
	                System.out.println("CREA_DATE :"+creaDt);
	               
					pstmt.executeUpdate();
					System.out.println("@@@@@@@ executed sucessfully............");
				}
			}
			if(this.tranType.equalsIgnoreCase("I") || this.tranType.equalsIgnoreCase("ID"))
			{
				sql ="select (case when grade is null then '   ' else grade end ),rate "
					+" from stock"
					+" where item_code = '"+this.itemCode+"'" 
					+" and site_code = '"+this.siteCode +"'"
					+" and loc_code = '"+this.locationCode +"'"
					+" and lot_no = '"+this.lotNo+"'"
					+" and lot_sl = '"+this.lotSl+"'";
	            System.out.println("Query :::- ["+sql+"]");
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
	                this.grade =rs.getString(1);
	                this.rate = rs.getDouble(2);
				}
				pstmt.close();
				rs.close(); 
			}
			if(!stockValuation.equalsIgnoreCase("N")){
				if(this.tranType.equalsIgnoreCase("R")){
					if (stkOpt.equalsIgnoreCase("1") || stockValuation.equalsIgnoreCase("M")) 
					{
						sql =" update stock set  rate = (case when '"+this.rate+"' is null then 0 else "+this.rate+" end),"
							+" gross_rate 	= (case when '"+this.grossRate+"' is null then 0 else "+this.grossRate+" end)"
							+" where item_code ='"+this.itemCode+"'" 
							+" and   site_code ='"+this.siteCode+"'" 
							+" and 	grade 	 = '"+this.grade+"'";
	                        System.out.println("Query :::- ["+sql+"]");
							pstmt = conn.prepareStatement(sql);
							pstmt.executeUpdate();
					}
					else if (stockValuation.equalsIgnoreCase("C"))
					{
						sql = " update stock set rate = (case when '"+this.rate+"' is null then 0 else '"+this.rate+"' end) ,"
						+" gross_rate 	=  (case when '"+this.grossRate+"' is null then 0 else '"+this.grossRate+"' end) "
						+" where item_code ='"+this.itemCode+"'" 
						+" and   site_code ='"+this.siteCode+"'" 
						+" and 	grade 	 = '"+this.grade+"'"
						+" And 	cctr_code__inv = '"+this.cctrCodeInv+"'";
	                    System.out.println("Query :::- ["+sql+"]");
						pstmt = conn.prepareStatement(sql);
						pstmt.executeUpdate();
					}
	            }
				sql = "select count(*) from stockvalue "
				+" where item_code ='"+this.itemCode+"'" 
				+" and   site_code ='"+this.siteCode+"'" 
				+" and VALUE_KEY = '"+this.grade+"'";
	            System.out.println("Query ::- ["+sql+"]");
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					count =rs.getInt(1);
				}
				pstmt.close();
				rs.close();
				if(count == 1){
					if (stkOpt.equalsIgnoreCase("1") || stockValuation.equalsIgnoreCase("M")){
						if (this.tranType.equalsIgnoreCase("R")){
							sql ="update stockvalue set quantity = (case when quantity is null then 0 else quantity end) + (case when "+effQty+" is null then 0 else "+effQty+" end), "
	                        +"value = ((case when "+oldQty+" is null then 0 else "+oldQty+" end) * (case when "+this.rate+" is null then 0 else "+this.rate+" end)) + ((case when "+effQty+" is null then 0 else "+effQty+" end) * (case when "+this.rate+" is null then 0 else "+this.rate+" end))"
							+" where item_code ='"+this.itemCode+"'" 
							+" and   site_code ='"+this.siteCode+"'" 
							+" and 	VALUE_KEY 	 = '"+this.grade+"'";
	                        System.out.println("Query :::- ["+sql+"]");
							pstmt = conn.prepareStatement(sql);
							pstmt.executeUpdate();
						}else{
							sql =" update stockvalue set quantity = (case when quantity is null then 0 else quantity end) + (case when "+effQty+" is null then 0 else "+effQty+" end),	"
	                        +"value = (case when value is null then 0 else value end) + ((case when "+effQty+" is null then 0 else "+effQty+" end) * (case when "+this.rate+" is null then 0 else "+this.rate+" end)) "
							+" where item_code ='"+this.itemCode+"'" 
							+" and   site_code ='"+this.siteCode+"'" 
							+" and 	VALUE_KEY 	 = '"+this.grade+"'";
	                        System.out.println("Query :::- ["+sql+"]");
							pstmt = conn.prepareStatement(sql);
							pstmt.executeUpdate();					
						}
					}else{
						sql ="update stockvalue set quantity = (case when quantity is null then 0 else quantity end) + (case when "+effQty+" is null then 0 else "+effQty+" end),"
	                    +"value = (case when value is null then 0 else value end) + ((case when "+effQty+" is null then 0 else "+effQty+" end) * (case when "+this.rate+" is null then 0 else "+this.rate+" end)) " 
						+" where item_code ='"+this.itemCode+"'" 
						+" and   site_code ='"+this.siteCode+"'" 
						+" and 	VALUE_KEY 	 = '"+this.grade+"'";
	                    System.out.println("Query :::- ["+sql+"]");
						pstmt = conn.prepareStatement(sql);
						pstmt.executeUpdate();
					}
					sql =" select (case when quantity is null then 0 else quantity end), (case when value is null then 0 else value end)"
					+" from stockvalue"
					+" where item_code ='"+this.itemCode+"'" 
					+" and   site_code ='"+this.siteCode+"'" 
					+" and 	VALUE_KEY 	 = '"+this.grade+"'";
	                System.out.println("Query :::- ["+sql+"]");
					pstmt = conn.prepareStatement(sql);
					rs = pstmt.executeQuery();
					double value1=0;
					double quantity1=0;
					if(rs.next())
					{
						quantity1 = rs.getDouble(1);
						value1 = rs.getDouble(2);
					}
	                System.out.println("Stock Value Quantity : "+quantity1+" Stock Value Value :"+value1);
					if (quantity1 == 0 && value1 != 0 ){
					    parmValueStr = distCommon.getDisparams("999999","ON_STKVALUE_ADJENTRY",conn);
						if (!parmValueStr.equalsIgnoreCase("NULLFOUND")){
							parmValue = Double.parseDouble(parmValueStr);
							System.out.println("Param Value "+parmValue);
	                        if (Math.abs(value1) > parmValue){
							    errString = "VTSTKVERR";	
							    errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);
							    return errString;
							}
	                    }else{
							if(Math.abs(value1) > 1){
								errString = "VTSTKVERR"	;
								errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);
								return errString;
							}
	                    }
	                    errString = adjustStkValue(conn);
	                    if(errString.trim().length() > 0){
	                        return errString;
	                    }
						sql =" update stockvalue " 
	                    +"set value = (case when value is null then 0 else value end) + (case when '"+this.netAmt+"' is null then 0 else '"+this.netAmt+"' end) "
						+" where item_code ='"+this.itemCode+"'" 
						+" and   site_code ='"+this.siteCode+"'" 
						+" and 	VALUE_KEY 	 = '"+this.grade+"'";
	                    
	                    System.out.println("Query :::- ["+sql+"]");
	                    
	                    pstmt = conn.prepareStatement(sql);
	                    pstmt.executeUpdate();
	                }
	            }else if(count == 0){
					if (this.tranType.equalsIgnoreCase("R")){
					   sql =" insert into stockvalue (site_code,item_code, value_key, quantity,	value )"
					   	+" values (?,?,?,?,?) ";
	                   System.out.println("Query :::- ["+sql+"]");
					   pstmt = conn.prepareStatement(sql);
					   pstmt.setString(1,this.siteCode);
					   pstmt.setString(2,this.itemCode);
	                   pstmt.setString(3,this.grade);
					   pstmt.setDouble(4,effQty);
					   pstmt.setDouble(5,effQty*this.rate);
					   pstmt.executeUpdate();
					}else{
	                    System.out.println("StockValue update failed, there is no record in stockvalue for " + this.siteCode);
	                    errString = "VTSTKVERR";    
	                    errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);
	                    return errString;// STOCK VALUE ERROR
	                }
				}
			}//end of stockValuation="N"
		}
		catch (SQLException sqx) 
		{
			
			SQLException ex ;
			
			while (sqx != null)
			{
				if((CommonConstants.DB_NAME).equalsIgnoreCase("oracle") && (sqx).toString().indexOf("ORA-00054") > -1)
				{
					System.out.println("The SQLException occurs in UpdatStock [Stockupdate] getSQLState [" + sqlState + "] getErrorCode [" + errorCode + "]  Exception[Recod is locked try after some time]");
					errString = itmDBAccessEJB.getErrorString("","VTLCKERR","");
				}
				else
				{
					System.out.println("The SQLException occurs in UpdatStock [Stockupdate] getSQLState [" + sqlState + "] getErrorCode [" + errorCode + "]  Exception["+sqx + "]");
				}
				ex = sqx;
				
				sqx.printStackTrace();
				sqx = sqx.getNextException();
				if(sqx == null)
				{
					sqx = ex;
					break;
				}
			}
			
	        throw new ITMException(sqx);
		}
		catch(Exception se12){
		    System.out.println("Exception in UpdatStock [Stockupdate]"+se12);
	        se12.printStackTrace();
	        throw new ITMException(se12);
		}finally{
		    try{
	            if(rs != null){rs.close();rs = null;}
	            if(pstmt != null){pstmt.close();pstmt = null;}
	        }catch(Exception t){}
	    }
		return errString;
		}
	
	private void puplateCommonParameters(HashMap updateStockMap) throws ITMException, Exception
	{		
        System.out.println("Comming Map :::- ["+updateStockMap+"]");
        try{
            
        
    		if(updateStockMap.get("item_code")!= null)
    		{
    			this.itemCode = updateStockMap.get("item_code").toString();
    		}
    		if(updateStockMap.get("site_code")!= null)
    		{
    			this.siteCode = updateStockMap.get("site_code").toString();
    		}
    		if(updateStockMap.get("loc_code")!= null)
    		{
    			this.locationCode = updateStockMap.get("loc_code").toString();
    		}
    		if(updateStockMap.get("lot_no")!= null)
    		{
    			this.lotNo = updateStockMap.get("lot_no").toString();
    		}
    		if(updateStockMap.get("lot_sl")!= null)
    		{
    			this.lotSl = updateStockMap.get("lot_sl").toString();
    		}
    		if(updateStockMap.get("quantity")!= null)
    		{
    			this.quantity = Double.parseDouble(updateStockMap.get("quantity").toString());
    		}
    		if(updateStockMap.get("unit") != null)
    		{
    			this.unit = updateStockMap.get("unit").toString();
    		}
    		if(updateStockMap.get("tran_type")!= null)
    		{
    			this.tranType = updateStockMap.get("tran_type").toString();
    		}
    		if(updateStockMap.get("check_expiry")!= null)
    		{
    			this.checkExpiry = updateStockMap.get("check_expiry").toString();
    		}
    		if(updateStockMap.get("tran_date")!= null)
    		{
    			this.tranDate = (Timestamp)updateStockMap.get("tran_date");			
    		}
    		if(updateStockMap.get("tran_ser") != null)
    		{
    			this.tranSer = updateStockMap.get("tran_ser").toString();
    		}
    		if(updateStockMap.get("tran_id")!=null)
    		{
    			this.tranId = updateStockMap.get("tran_id").toString();
    		}
    		if(updateStockMap.get("acct_code__cr")!=null)
    		{
    			this.acctCodeCr = updateStockMap.get("acct_code__cr").toString();
    		}
    		if(updateStockMap.get("acct_code__dr") != null)
    		{
    			this.acctCodeDr = updateStockMap.get("acct_code__dr").toString();
    		}
    		if(updateStockMap.get("cctr_code__cr") != null)
    		{
    			this.cctrCodeCr = 	updateStockMap.get("cctr_code__cr").toString();
    		}
    		if(updateStockMap.get("cctr_code__dr") != null)
    		{
    			this.cctrCodeDr = updateStockMap.get("cctr_code__dr").toString();
    		}
    		if(updateStockMap.get("line_no")!=null)
    		{
    			this.lineNo = updateStockMap.get("line_no").toString();
    		}
    		if(updateStockMap.get("sorder_no")!=null)
    		{
    			this.sorderNo = updateStockMap.get("sorder_no").toString();
    		}
    		if(updateStockMap.get("qty_stduom")!=null)
    		{
    			this.qtyStduom = Double.parseDouble(updateStockMap.get("qty_stduom").toString());
    		}
    		if(updateStockMap.get("rate")!=null)
    		{
    			this.rate = Double.parseDouble(updateStockMap.get("rate").toString());
    		}
    		if(updateStockMap.get("site_code__mfg")!= null)
    		{
    			this.siteCodeMfg = 	updateStockMap.get("site_code__mfg").toString();
    		}
    		if(updateStockMap.get("mfg_date")!=null)
    		{
    			this.mfgDate = 	(Timestamp)updateStockMap.get("mfg_date");
    		}
    		if(updateStockMap.get("potency_perc")!=null)
    		{
    			this.potencyPerc = 	Double.parseDouble(updateStockMap.get("potency_perc").toString());
    		}
    		if(updateStockMap.get("exp_date")!=null)
    		{
    			this.expDate = 	(Timestamp)updateStockMap.get("exp_date");
    		}
    		if(updateStockMap.get("pack_code")!=null)
    		{
    			this.packCode = 	updateStockMap.get("pack_code").toString();
    		}
    		if(updateStockMap.get("item_ser")!=null)
    		{
    			this.itemSer = 	updateStockMap.get("item_ser").toString();
    		}
    		if(updateStockMap.get("reas_code")!=null)
    		{
    			this.reasCode = updateStockMap.get("reas_code").toString();
    		}
    		if(updateStockMap.get("inv_stat")!=null)
    		{
    			this.invStat = 	updateStockMap.get("inv_stat").toString();
    		}
    		if(updateStockMap.get("net_amt")!=null)
    		{
    			this.netAmt = 	Double.parseDouble(updateStockMap.get("net_amt").toString());
    		}
    		if(updateStockMap.get("sundry_type")!=null)
    		{
    			this.sundryType = 	updateStockMap.get("sundry_type").toString();
    		}
    		if(updateStockMap.get("sundry_code")!=null)
    		{
    			this.sundryCode = 	updateStockMap.get("sundry_code").toString();
    		}
    		if(updateStockMap.get("curr_date")!= null)
    		{
    			this.currDate = (Timestamp)	updateStockMap.get("curr_date");
    		}
    		if(updateStockMap.get("gross_weight")!=null)
    		{
    			this.grossWeight = Double.parseDouble(updateStockMap.get("gross_weight").toString());
    		}
    		if(updateStockMap.get("tare_weight")!=null)
    		{
    			this.tareWeight = Double.parseDouble(updateStockMap.get("tare_weight").toString());
    		}
    			if(updateStockMap.get("net_weight")!=null)
    		{
    				this.netWeight = Double.parseDouble(updateStockMap.get("net_weight").toString());
    		}
    		if(updateStockMap.get("pack_instr")!=null)
    		{
    			this.packInstr = updateStockMap.get("pack_instr").toString();
    		}
    		if(updateStockMap.get("dimension")!=null)
    		{
    			this.dimension = 	updateStockMap.get("dimension").toString();
    		}
    		if(updateStockMap.get("acct_code_inv")!=null)
    		{
    			this.acctCodeInv = 	updateStockMap.get("acct_code_inv").toString();
    		}
    		if(updateStockMap.get("cctr_code_inv")!=null)
    		{
    			this.cctrCodeInv = 	updateStockMap.get("cctr_code_inv").toString();
    		}
    		if(updateStockMap.get("rate_oh")!= null)
    		{
    			this.rateOh = 	Double.parseDouble(updateStockMap.get("rate_oh").toString());
    		}
    		if(updateStockMap.get("acct_code_oh")!= null)
    		{
    			this.acctCodeOh = 	updateStockMap.get("acct_code_oh").toString();
    		}
    		if(updateStockMap.get("cctr_code_oh")!=null)
    		{
    			this.cctrCodeOh = 	updateStockMap.get("cctr_code_oh").toString();
    		}
    		if(updateStockMap.get("retest_date")!=null)
    		{
    			this.retestDate = (Timestamp)updateStockMap.get("retest_date");
    		}
    		if(updateStockMap.get("supp_code__mfg")!=null)
    		{
    			this.suppCodeMfg = 	updateStockMap.get("supp_code__mfg").toString();
    		}
    		if(updateStockMap.get("grade")!=null)
    		{
    			this.grade = updateStockMap.get("grade").toString();
    		}
    		if(updateStockMap.get("gross_rate")!=null)
    		{
    			this.grossRate = Double.parseDouble(updateStockMap.get("gross_rate").toString());
    		}
    		if(updateStockMap.get("conv__qty_stduom")!=null)
    		{
    			this.convQtyStduom = Double.parseDouble(updateStockMap.get("conv__qty_stduom").toString());
    		}
    		if(updateStockMap.get("batch_no")!= null)
    		{
    			this.batchNo = 	updateStockMap.get("batch_no").toString();
    		}
    		if(updateStockMap.get("unit__alt")!= null)
    		{
    			this.unitAlt = 	updateStockMap.get("unit__alt").toString();
    		}
    		if(updateStockMap.get("no_art")!=null)
    		{
    			this.noArt = Double.parseDouble(updateStockMap.get("no_art").toString());
    		}
    		if(updateStockMap.get("last_phyc_date")!=null)
    		{
    			this.lastPhycDate = (Timestamp)updateStockMap.get("last_phyc_date");
    		}
    		if(updateStockMap.get("remarks")!=null)
    		{
    			this.remarks = 	updateStockMap.get("remarks").toString();
    		}
    		if(updateStockMap.get("ref_id__for")!=null)
    		{
    			this.refIdFor = updateStockMap.get("ref_id__for").toString();
    		}
    		if(updateStockMap.get("ref_ser__for")!=null)
    		{
    			this.refSerFor = updateStockMap.get("ref_ser__for").toString();
    		}
    		if(updateStockMap.get("actual_rate")!=null)
    		{
    			this.actualRate = Double.parseDouble(updateStockMap.get("actual_rate").toString());
    		}
            if(updateStockMap.get("pack_ref")!= null)
            {
                this.packRef = updateStockMap.get("pack_ref").toString();
            }
            if(updateStockMap.get("consider_allocate")!= null)
            {
                this.considerAllocate = updateStockMap.get("consider_allocate").toString();
            }
            if(updateStockMap.get("partial_used")!= null)
            {
                this.partialUsed = updateStockMap.get("partial_used").toString();
            }
    		if(updateStockMap.get("batch_size")!=null)
    		{
    			this.batchSize = 	Double.parseDouble(updateStockMap.get("batch_size").toString());
    		}
    		if(updateStockMap.get("lot_sl__org")!=null)
    		{
    			this.lotSlOrg = 	updateStockMap.get("lot_sl__org").toString();
    		}

        }catch(Exception e){
            System.out.println("Exception "+e);
            e.printStackTrace();
            throw new ITMException(e);
        }
	}
	public String checkStkOpt(String itemCode,String siteCode,Connection conn) throws ITMException, Exception
	{
		String stkOpt=null;
		ResultSet rs=null;
		PreparedStatement pstmt=null;
		String sql=null;
		try{
            sql = "SELECT CASE WHEN STK_OPT IS NULL THEN 'N' ELSE STK_OPT END "
			+" FROM SITEITEM WHERE ITEM_CODE='"+itemCode+"' AND SITE_CODE='"+siteCode+"'";
            System.out.println("SQL :"+sql);
			pstmt=conn.prepareStatement(sql);
			rs=pstmt.executeQuery();
			if(rs.next())
			{
				stkOpt=rs.getString(1);
			}
			rs.close();
			pstmt.close();
			if(stkOpt == null || stkOpt.equals("") || stkOpt.equalsIgnoreCase("N")){
				sql = "SELECT CASE WHEN STK_OPT IS NULL THEN '0' ELSE STK_OPT END "
					+" FROM ITEM WHERE ITEM_CODE='"+itemCode+"' ";
                System.out.println("SQL :"+sql);
				pstmt=conn.prepareStatement(sql);
				rs=pstmt.executeQuery();
				if(rs.next()){
					stkOpt=rs.getString(1);
				}
			}
		}catch (Exception e){
			System.out.println("Exception in checkStkOpt "+e);            
            e.printStackTrace();
            throw new ITMException(e);
		}finally{
		    try{
                if(rs != null){rs.close();rs = null;}
                if(pstmt != null){pstmt.close();pstmt = null;}
            }catch(Exception t){throw new ITMException(t);}
        }
        System.out.println("Returning Stock Option : "+stkOpt);
		return stkOpt;
	}	
	public String checkStkOptRnd(String siteCode,String itemCode,Connection conn) throws ITMException, Exception
	{
		ResultSet rs=null;
		PreparedStatement pstmt=null;
		String varValue=null;
		String lsStkOpt = null;
		GenericUtility genericUtility = new GenericUtility();
        ArrayList siteArrList = null;
        String paramSite = "";
    	try{
            siteArrList = new ArrayList();
    		String sql="SELECT VAR_VALUE FROM MFGPARM WHERE PRD_CODE = '999999' AND VAR_NAME = 'RND_NO_STK_SITE' ";
            System.out.println("SQL :"+sql);
    		pstmt = conn.prepareStatement(sql);
    		rs = pstmt.executeQuery();
            if(rs.next()){
                varValue = rs.getString(1);
            }
            if(varValue != null && varValue.trim().length() > 0){
                siteArrList = genericUtility.getTokenList(varValue,",");
            }
            if(siteArrList.size() > 0){
                for(int i = 0;i < siteArrList.size();i++){
                    paramSite = siteArrList.get(i).toString();
                    if(paramSite.equalsIgnoreCase(siteCode)){
                        lsStkOpt = "0";
                        break;
                    }
                }
            }
        }catch(Exception e){
            System.out.println("Exception checkStkOptRnd "+e.getMessage());
            e.printStackTrace();      
            throw new ITMException(e);
    	}finally{
    	    try{
                if(rs != null){rs.close();rs = null;}
                if(pstmt != null){pstmt.close();pstmt = null;}
            }catch(Exception t){throw new ITMException(t);}
        }
        System.out.println("Returning Stock Option RND :"+lsStkOpt);
        return lsStkOpt;
	}
	private String calcWeights(Connection conn) throws ITMException, Exception
    {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String issCriteria = "";
        String sql = "";
        double stockQty = 0,stkGrossWt = 0,stkNetWt = 0,stkTareWt = 0,qtyPerArt = 0;
        double grossWtPerArt = 0,tareWtPerArt = 0;
        long stkNoArt = 0;
        try{
            if(this.tranSer.equalsIgnoreCase("S-ISS")){
                return "";
            }
            sql = "select case when iss_criteria is null then 'I' else iss_criteria end from item where item_code = '"+this.itemCode+"'";
            System.out.println("SQL : "+sql);
            pstmt = conn.prepareStatement(sql);
            
            rs = pstmt.executeQuery();
            if(rs.next()){
                issCriteria = rs.getString(1);
            }
            rs.close();
            pstmt.close();
            sql = "select quantity,gross_weight, net_weight, tare_weight, no_art," +
                    "qty_per_art, gross_wt_per_art, tare_wt_per_art " +
                    "from stock " +
                    "where item_code = '"+this.itemCode+"' and " +
                    "site_code = '"+this.siteCode+"' and loc_code = '"+this.locationCode+"' "+
                    "and lot_no = '"+this.lotNo+"' and lot_sl = '"+this.lotSl+"'";        
                
            System.out.println("SQL :"+sql);
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            if(rs.next()){
                stockQty = rs.getDouble(1);
                stkGrossWt = rs.getDouble(2);
                stkNetWt = rs.getDouble(3);
                stkTareWt = rs.getDouble(4);
                stkNoArt = rs.getLong(5);
                qtyPerArt = rs.getDouble(6);
                grossWtPerArt = rs.getDouble(7);
                tareWtPerArt = rs.getDouble(8);
            }
            rs.close();
            pstmt.close();
            if(issCriteria != null && issCriteria.trim().equalsIgnoreCase("W")){
                if(stockQty > 0){
                    this.netWeight = stkNetWt / stockQty * this.qtyStduom;
                    this.grossWeight = this.netWeight + this.tareWeight;
                }
            }else{
                if(qtyPerArt == 0){
                    if(stkNoArt == 0) stkNoArt = 1;
                    qtyPerArt = stockQty / stkNoArt;
                }
                if(qtyPerArt != 0){
                    if(stockQty > 0 && this.qtyStduom > 0){
                        this.netWeight = stkNetWt / stockQty * this.qtyStduom;
                    }
                    this.tareWeight = tareWtPerArt * this.noArt;
                    this.grossWeight = this.netWeight + this.tareWeight;
                }
            }
        }catch(Exception e){
            System.out.println("Exception e "+e);
            e.printStackTrace();
            throw new ITMException(e);
        }finally{
            try{
                if(rs != null){rs.close();rs = null;}
                if(pstmt != null){pstmt.close();pstmt = null;}
            }catch(Exception t){throw new ITMException(t);}
        }
        return "";
    }	
	private String generateTranId(String windowName,String xmlValues,Connection conn) throws ITMException, Exception
	{
        System.out.println("Generating tran ID.....");
		Statement stmt = null;
		ResultSet rs = null;
		String sql = "";
		String tranId = "";
		String newKeystring = "";
		String srType = "RS";
		 try
	     {
	    	sql = "SELECT KEY_STRING, TRAN_ID_COL, REF_SER FROM TRANSETUP WHERE UPPER(TRAN_WINDOW)= '"+windowName+"'";
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			
			String tranSer1 = "";
			String keyString = "";
			String keyCol = "";
			if (rs.next())
			{
				keyString = rs.getString(1);
				keyCol = rs.getString(2);
				tranSer1 = rs.getString(3);				
			}
			System.out.println("keyString :"+keyString);
			System.out.println("keyCol :"+keyCol);
			System.out.println("tranSer1 :"+tranSer1);
			System.out.println("xmlValues  :["+xmlValues+"]");
			TransIDGenerator tg = new TransIDGenerator(xmlValues, "SYSTEM", CommonConstants.DB_NAME);
			tranId = tg.generateTranSeqID(tranSer1, keyCol, keyString, conn);
			System.out.println("tranId :"+tranId);
		}
		catch (SQLException ex)
		{
			System.out.println("Exception ::" +sql+ ex.getMessage() + ":");
			ex.printStackTrace();		
            throw new ITMException(ex);
		}
		catch (Exception e)
		{
			System.out.println("Exception ::" + e.getMessage() + ":");
			e.printStackTrace();
            throw new ITMException(e);
		}finally{
            try{
                if(rs != null){rs.close();rs = null;}
                if(stmt != null){stmt.close();stmt = null;}
            }catch(Exception t){throw new ITMException(t);}
        }
		return tranId;
	}//generateTranTd()
	private HashMap updateLotInfo(HashMap updateStockMap ,Connection conn) throws ITMException, Exception
	{		
       String sql="",errCode="",sql2="";
       ResultSet rs=null, rs2=null;
       PreparedStatement pstmt=null, pstmt2=null;
       String shelfLifeType="",lotNo="",itemCode="",grade="",stkOpt="";
       int cnt=0,cnt1=0;
       Timestamp expDate=null , mfgDate=null, retestDate=null ,retestDate_upd3=null;
       //Date today=null;
       double grossrate=0;
       String packCode="",siteCodeMfg="",acctCodeInv="",cctrCodeInv="",batchNo="",suppCodeMfg="",suppCode="";
       double  potencyPerc=0 , grossRate=0 , rate=0 , batchSize=0;
       java.util.Date retestDate3=null,expDate3=null,mfgDate3=null,retestDate_upd2=null;
       Calendar currentDate = Calendar.getInstance();
       SimpleDateFormat simpleDateFormat = new SimpleDateFormat(genericUtility.getDBDateFormat());
       Timestamp today = Timestamp.valueOf(simpleDateFormat.format(simpleDateFormat.parse(simpleDateFormat.format(currentDate.getTime()))).toString() + " 00:00:00.0");
       System.out.println("Now the date is :=>  " + today);
		String available="";
      // today  = new java.sql.Date(System.currentTimeMillis());
       System.out.println("today:::["+today+"]");
       
       
       
       System.out.println("@@@@@ updateLotInfo() method called..............");
       
       System.out.println("Comming Map :::- ["+updateStockMap+"]");

	try{
                    
    		if(updateStockMap.get("item_code")!= null)
    		{
    			this.itemCode = updateStockMap.get("item_code").toString();
    		}
    		if(updateStockMap.get("site_code")!= null)
    		{
    			this.siteCode = updateStockMap.get("site_code").toString();
    		}
    		if(updateStockMap.get("loc_code")!= null)
    		{
    			this.locationCode = updateStockMap.get("loc_code").toString();
    		}
    		if(updateStockMap.get("lot_no")!= null)
    		{
    			this.lotNo = updateStockMap.get("lot_no").toString();
    		}
    		if(updateStockMap.get("lot_sl")!= null)
    		{
    			this.lotSl = updateStockMap.get("lot_sl").toString();
    		}
    		if(updateStockMap.get("quantity")!= null)
    		{
    			this.quantity = Double.parseDouble(updateStockMap.get("quantity").toString());
    		}
    		if(updateStockMap.get("unit") != null)
    		{
    			this.unit = updateStockMap.get("unit").toString();
    		}
    		if(updateStockMap.get("tran_type")!= null)
    		{
    			this.tranType = updateStockMap.get("tran_type").toString();
    		}
    		if(updateStockMap.get("check_expiry")!= null)
    		{
    			this.checkExpiry = updateStockMap.get("check_expiry").toString();
    		}
    		if(updateStockMap.get("tran_date")!= null)
    		{
    			this.tranDate = (Timestamp)updateStockMap.get("tran_date");			
    		}
    		if(updateStockMap.get("tran_ser") != null)
    		{
    			this.tranSer = updateStockMap.get("tran_ser").toString();
    		}
    		if(updateStockMap.get("tran_id")!=null)
    		{
    			this.tranId = updateStockMap.get("tran_id").toString();
    		}
    		if(updateStockMap.get("acct_code__cr")!=null)
    		{
    			this.acctCodeCr = updateStockMap.get("acct_code__cr").toString();
    		}
    		if(updateStockMap.get("acct_code__dr") != null)
    		{
    			this.acctCodeDr = updateStockMap.get("acct_code__dr").toString();
    		}
    		if(updateStockMap.get("cctr_code__cr") != null)
    		{
    			this.cctrCodeCr = 	updateStockMap.get("cctr_code__cr").toString();
    		}
    		if(updateStockMap.get("cctr_code__dr") != null)
    		{
    			this.cctrCodeDr = updateStockMap.get("cctr_code__dr").toString();
    		}
    		if(updateStockMap.get("line_no")!=null)
    		{
    			this.lineNo = updateStockMap.get("line_no").toString();
    		}
    		if(updateStockMap.get("sorder_no")!=null)
    		{
    			this.sorderNo = updateStockMap.get("sorder_no").toString();
    		}
    		if(updateStockMap.get("qty_stduom")!=null)
    		{
    			this.qtyStduom = Double.parseDouble(updateStockMap.get("qty_stduom").toString());
    		}
    		if(updateStockMap.get("rate")!=null)
    		{
    			this.rate = Double.parseDouble(updateStockMap.get("rate").toString());
    		}
    		if(updateStockMap.get("site_code__mfg")!= null)
    		{
    			this.siteCodeMfg = 	updateStockMap.get("site_code__mfg").toString();
    		}
    		if(updateStockMap.get("mfg_date")!=null)
    		{
    			this.mfgDate = 	(Timestamp)updateStockMap.get("mfg_date");
    		}
    		if(updateStockMap.get("potency_perc")!=null)
    		{
    			this.potencyPerc = 	Double.parseDouble(updateStockMap.get("potency_perc").toString());
    		}
    		if(updateStockMap.get("exp_date")!=null)
    		{
    			this.expDate = 	(Timestamp)updateStockMap.get("exp_date");
    		}
    		if(updateStockMap.get("pack_code")!=null)
    		{
    			this.packCode = 	updateStockMap.get("pack_code").toString();
    		}
    		if(updateStockMap.get("item_ser")!=null)
    		{
    			this.itemSer = 	updateStockMap.get("item_ser").toString();
    		}
    		if(updateStockMap.get("reas_code")!=null)
    		{
    			this.reasCode = updateStockMap.get("reas_code").toString();
    		}
    		if(updateStockMap.get("inv_stat")!=null)
    		{
    			this.invStat = 	updateStockMap.get("inv_stat").toString();
    		}
    		if(updateStockMap.get("net_amt")!=null)
    		{
    			this.netAmt = 	Double.parseDouble(updateStockMap.get("net_amt").toString());
    		}
    		if(updateStockMap.get("sundry_type")!=null)
    		{
    			this.sundryType = 	updateStockMap.get("sundry_type").toString();
    		}
    		if(updateStockMap.get("sundry_code")!=null)
    		{
    			this.sundryCode = 	updateStockMap.get("sundry_code").toString();
    		}
    		if(updateStockMap.get("curr_date")!= null)
    		{
    			this.currDate = (Timestamp)	updateStockMap.get("curr_date");
    		}
    		if(updateStockMap.get("gross_weight")!=null)
    		{
    			this.grossWeight = Double.parseDouble(updateStockMap.get("gross_weight").toString());
    		}
    		if(updateStockMap.get("tare_weight")!=null)
    		{
    			this.tareWeight = Double.parseDouble(updateStockMap.get("tare_weight").toString());
    		}
    			if(updateStockMap.get("net_weight")!=null)
    		{
    				this.netWeight = Double.parseDouble(updateStockMap.get("net_weight").toString());
    		}
    		if(updateStockMap.get("pack_instr")!=null)
    		{
    			this.packInstr = updateStockMap.get("pack_instr").toString();
    		}
    		if(updateStockMap.get("dimension")!=null)
    		{
    			this.dimension = 	updateStockMap.get("dimension").toString();
    		}
    		if(updateStockMap.get("acct_code_inv")!=null)
    		{
    			this.acctCodeInv = 	updateStockMap.get("acct_code_inv").toString();
    		}
    		if(updateStockMap.get("cctr_code_inv")!=null)
    		{
    			this.cctrCodeInv = 	updateStockMap.get("cctr_code_inv").toString();
    		}
    		if(updateStockMap.get("rate_oh")!= null)
    		{
    			this.rateOh = 	Double.parseDouble(updateStockMap.get("rate_oh").toString());
    		}
    		if(updateStockMap.get("acct_code_oh")!= null)
    		{
    			this.acctCodeOh = 	updateStockMap.get("acct_code_oh").toString();
    		}
    		if(updateStockMap.get("cctr_code_oh")!=null)
    		{
    			this.cctrCodeOh = 	updateStockMap.get("cctr_code_oh").toString();
    		}
    		if(updateStockMap.get("retest_date")!=null)
    		{
    			this.retestDate = (Timestamp)updateStockMap.get("retest_date");
    		}
    		if(updateStockMap.get("supp_code__mfg")!=null)
    		{
    			this.suppCodeMfg = 	updateStockMap.get("supp_code__mfg").toString();
    		}
    		if(updateStockMap.get("grade")!=null)
    		{
    			this.grade = updateStockMap.get("grade").toString();
    		}
    		if(updateStockMap.get("gross_rate")!=null)
    		{
    			this.grossRate = Double.parseDouble(updateStockMap.get("gross_rate").toString());
    		}
    		if(updateStockMap.get("conv__qty_stduom")!=null)
    		{
    			this.convQtyStduom = Double.parseDouble(updateStockMap.get("conv__qty_stduom").toString());
    		}
    		if(updateStockMap.get("batch_no")!= null)
    		{
    			this.batchNo = 	updateStockMap.get("batch_no").toString();
    		}
    		if(updateStockMap.get("unit__alt")!= null)
    		{
    			this.unitAlt = 	updateStockMap.get("unit__alt").toString();
    		}
    		if(updateStockMap.get("no_art")!=null)
    		{
    			this.noArt = Double.parseDouble(updateStockMap.get("no_art").toString());
    		}
    		if(updateStockMap.get("last_phyc_date")!=null)
    		{
    			this.lastPhycDate = (Timestamp)updateStockMap.get("last_phyc_date");
    		}
    		if(updateStockMap.get("remarks")!=null)
    		{
    			this.remarks = 	updateStockMap.get("remarks").toString();
    		}
    		if(updateStockMap.get("ref_id__for")!=null)
    		{
    			this.refIdFor = updateStockMap.get("ref_id__for").toString();
    		}
    		if(updateStockMap.get("ref_ser__for")!=null)
    		{
    			this.refSerFor = updateStockMap.get("ref_ser__for").toString();
    		}
    		if(updateStockMap.get("actual_rate")!=null)
    		{
    			this.actualRate = Double.parseDouble(updateStockMap.get("actual_rate").toString());
    		}
            if(updateStockMap.get("pack_ref")!= null)
            {
                this.packRef = updateStockMap.get("pack_ref").toString();
            }
            if(updateStockMap.get("consider_allocate")!= null)
            {
                this.considerAllocate = updateStockMap.get("consider_allocate").toString();
            }
            if(updateStockMap.get("partial_used")!= null)
            {
                this.partialUsed = updateStockMap.get("partial_used").toString();
            }
    		if(updateStockMap.get("batch_size")!=null)
    		{
    			this.batchSize = Double.parseDouble(updateStockMap.get("batch_size").toString());
    		}
    		if(updateStockMap.get("supp_code")!=null)
    		{
    			this.suppCode = updateStockMap.get("supp_code").toString();
    		}
    		

    		stkOpt = checkStkOpt(this.itemCode,this.siteCode,conn);
			System.out.println("@@@@@ stkOpt[gfChkStkOpt] :::- ["+stkOpt+"]");
    		
			if( "2".equalsIgnoreCase(stkOpt))
			{	                     
				         //Change shelf_life_type TO shelf_life__type.  CHANGE DONE  BY RITESH TIWARI ON 15/11/13
				 sql = " select shelf_life__type from item where item_code = ?  ";
		           pstmt = conn.prepareStatement(sql);
		   		   pstmt.setString(1,this.itemCode);
		   		   rs = pstmt.executeQuery();
		           if(rs.next())
		           {
		        	  shelfLifeType = rs.getString(1);
		           	  System.out.println("@@@@ shelfLifeType:"+shelfLifeType);
		           }
		           rs.close();
		   		   rs = null;
		   		   pstmt.close();
		   		   pstmt = null;
		   		   
		   sql = " select count (*) from item_lot_info where item_code = ? and lot_no = ? ";
           pstmt = conn.prepareStatement(sql);
   		   pstmt.setString(1,this.itemCode);
   		   pstmt.setString(2,this.lotNo);
   		   rs = pstmt.executeQuery();
           if(rs.next())
           {
           	cnt = rs.getInt(1);
           	System.out.println("@@@@ cnt:"+cnt);
           }
           rs.close();
   		   rs = null;
   		   pstmt.close();
   		   pstmt = null;
           
           if( cnt > 0 )
           {
        	     sql = " select item_code,lot_no,grade,exp_date,mfg_date,retest_date,pack_code,potency_perc,site_code__mfg,rate," +
        	     	   " gross_rate,acct_code__inv,cctr_code__inv,batch_no,batch_size,supp_code__mfg,shelf_life_type,supp_code " +
        	    	   " from item_lot_info where item_code = ? and lot_no = ? " ; 
        	    pstmt = conn.prepareStatement(sql);
         		pstmt.setString(1,this.itemCode);
         		pstmt.setString(2,this.lotNo);
         		rs = pstmt.executeQuery();
                if(rs.next())
                 {
                	 itemCode  = rs.getString("item_code");
                	 lotNo 	   =  rs.getString("lot_no");
                	 grade 	   =  rs.getString("grade");
                	 expDate   =  rs.getTimestamp("exp_date");
                	 mfgDate   =  rs.getTimestamp("mfg_date");
                	 retestDate  = rs.getTimestamp("retest_date");
                	 packCode    = rs.getString("pack_code");
                	 potencyPerc = rs.getDouble("potency_perc");
                	 siteCodeMfg = rs.getString("site_code__mfg");
                	 rate  		 = rs.getDouble("rate");
                	 grossRate 	 = rs.getDouble("gross_rate");
                	 acctCodeInv = rs.getString("acct_code__inv");
                	 cctrCodeInv = rs.getString("cctr_code__inv");
                	 batchNo  	 = rs.getString("batch_no");
                	 batchSize 	 = rs.getDouble("batch_size");
                	 suppCodeMfg   = rs.getString("supp_code__mfg");
                	 shelfLifeType = rs.getString("shelf_life_type");
                	 suppCode = rs.getString("supp_code");
                	 
                 }
                rs.close();
        		rs = null;
        		pstmt.close();
        		pstmt = null;
               
                //if isnull(s_updatestock.itemcode) then s_updatestock.itemcode =  ls_item_code
                if(updateStockMap.get("item_code")== null)
        		{
        			updateStockMap.put("item_code", itemCode);
        		}
                //if isnull(s_updatestock.mfg_date) then s_updatestock.mfg_date = ldt_mfgdt
                if(updateStockMap.get("mfg_date")== null)
        		{
        			updateStockMap.put("mfg_date", mfgDate);
        		}
                //if isnull(s_updatestock.potency_perc) then s_updatestock.potency_perc =  lc_potency_perc
                if(updateStockMap.get("potency_perc")== null)
        		{
        			updateStockMap.put("potency_perc", potencyPerc);
        		}
                //if isnull(s_updatestock.exp_date) then s_updatestock.exp_date = mexpdate
                if(updateStockMap.get("exp_date")== null)
        		{
        			updateStockMap.put("exp_date", expDate);
        		}
                //if isnull(s_updatestock.retest_date) then s_updatestock.retest_date =  mretest_date
               // System.out.println("@@@@ updateStockMap.get(retest_date) ["+updateStockMap.get("retest_date").toString()+"]"); 
             
               if(  updateStockMap.get("retest_date")!= null  &&  updateStockMap.get("retest_date").toString() != "01/01/1990" ) 
               {
            	    sql2 = " select available from invstat where inv_stat = ( select inv_stat from location where loc_code =  ? ) ";
            	    pstmt2 = conn.prepareStatement(sql2);
             		pstmt2.setString(1,this.locationCode);
             		rs2 = pstmt2.executeQuery();
                    if(rs2.next())
                     { 	
                       available = rs2.getString("available");	
                     }
                    rs2.close();
            		rs2 = null;
            		pstmt2.close();
            		pstmt2 = null;
            	       System.out.println("@@@@ this.tranSer ["+this.tranSer+"]::::available ["+available+"]");	  
            	      if  ( "QC-ORD".equalsIgnoreCase(this.tranSer) && "Y".equalsIgnoreCase( available ) )
            	      {
            	          sql2 = " update item_lot_info set retest_date = ? where item_code = ? and lot_no = ?  " ;
            	          pstmt2=conn.prepareStatement(sql2);
            	          String retestDate_upd = updateStockMap.get("retest_date")==null?null:updateStockMap.get("retest_date").toString();
            	          if( retestDate_upd != null )
            	          {
            	           	   SimpleDateFormat simpleDateFormat1 = new SimpleDateFormat(genericUtility.getDBDateFormat());
            	               retestDate_upd2 = simpleDateFormat1.parse(retestDate_upd);
            	               System.out.println("@@@@@@@@@retestDate_upd2["+retestDate_upd2+"]");
            	               retestDate_upd3 = new Timestamp(retestDate_upd2.getTime());
             	               System.out.println("@@@@@@@@@retestDate_upd3["+retestDate_upd3+"]");
            	          }
            	          pstmt2.setTimestamp(1,retestDate_upd3);
            	          //pstmt2.setDate(1,retestDate_upd2 == null?null:new java.sql.Date(retestDate_upd2.getTime()));
            	          pstmt2.setString(2,updateStockMap.get("item_code")==null?null:updateStockMap.get("item_code").toString());
            	          pstmt2.setString(3,updateStockMap.get("lot_no")==null?null:updateStockMap.get("lot_no").toString());
            	          cnt1 = pstmt2.executeUpdate();
            	          pstmt2.close();
            	          pstmt2 = null;
            	          System.out.println("@@@@ cnt1:::"+cnt1);
            	      }
               }          
               else
               {
            	if(updateStockMap.get("retest_date")== null)
        		{
        			updateStockMap.put("retest_date", retestDate);
        		}
               }
               //if isnull(s_updatestock.lotno) then s_updatestock.lotno = ls_lot_no
                if(updateStockMap.get("lotno")== null)
        		{
        			updateStockMap.put("lotno", lotNo);
        		}
                //if isnull(s_updatestock.grade) then s_updatestock.grade = ls_grade
                if(updateStockMap.get("grade")== null)
        		{
        			updateStockMap.put("grade", grade);
        		}
                //if isnull(s_updatestock.pack_code) then s_updatestock.pack_code =  ls_pack_code
                if(updateStockMap.get("pack_code")== null)
        		{
        			updateStockMap.put("pack_code", packCode);
        		}
                //if isnull(s_updatestock.sitecode_mfg) then s_updatestock.sitecode_mfg =  ls_site_code__mfg
                if(updateStockMap.get("site_code__mfg")== null)
        		{
        			updateStockMap.put("site_code__mfg", siteCodeMfg);
        		}
                //if isnull(s_updatestock.rate) then s_updatestock.rate = mrate
                if(updateStockMap.get("rate")== null)
        		{
        			updateStockMap.put("rate", rate);
        		}
                //if isnull(s_updatestock.acct_code_inv) then s_updatestock.acct_code_inv  = ls_acct_code__inv
                if(updateStockMap.get("acct_code_inv")== null)
        		{
        			updateStockMap.put("acct_code_inv", acctCodeInv);
        		}
                //if isnull(s_updatestock.cctr_code_inv) then s_updatestock.cctr_code_inv  = ls_ctr_code__inv
                if(updateStockMap.get("cctr_code_inv")== null)
        		{
        			updateStockMap.put("cctr_code_inv", cctrCodeInv);
        		}
                //if isnull(s_updatestock.batch_no) then s_updatestock.batch_no = ls_batch_no
                if(updateStockMap.get("batch_no")== null)
        		{
        			updateStockMap.put("batch_no", batchNo);
        		}
                //if isnull(s_updatestock.gross_rate) then s_updatestock.gross_rate =  ld_grossrate
                if(updateStockMap.get("gross_rate")== null)
        		{
        			updateStockMap.put("gross_rate", grossRate);
        		}
                //if isnull(s_updatestock.batch_size) then s_updatestock.batch_size =  ld_batch_size
                if(updateStockMap.get("batch_size")== null)
        		{
        			updateStockMap.put("batch_size", batchSize);
        		}
                //if isnull(s_updatestock.supp_code__mfg) then  s_updatestock.supp_code__mfg = ls_supp_code__mfg
                if(updateStockMap.get("supp_code__mfg")== null)
        		{
        			updateStockMap.put("supp_code__mfg", suppCodeMfg);
        		}
                if(updateStockMap.get("supp_code")== null)
        		{
        			updateStockMap.put("supp_code", suppCode);
        		}
                
                //int mapsize = updateStockMap.size();
                
               // for(int i=0;i<mapsize;i++)
                {
                	System.out.println("@@@@@ for update map ..........");
                	System.out.println("itemCode::["+updateStockMap.get("item_code")+"]");
             	   	System.out.println("lotNo["+updateStockMap.get("lot_no") +"]");
     				System.out.println("grade["+updateStockMap.get("grade")+"]");
     				System.out.println("retestDate["+updateStockMap.get("retest_date")+"]");
     				System.out.println("expDate["+updateStockMap.get("exp_date")+"]");
     				System.out.println("mfgDate["+updateStockMap.get("mfg_date")+"]");
     				System.out.println("packCode["+updateStockMap.get("pack_code")+"]");
     				System.out.println("potencyPerc["+updateStockMap.get("potency_perc ")+"]");
     				System.out.println("siteCodeMfg["+updateStockMap.get("site_code__mfg")+"]");
     				System.out.println("rate ["+updateStockMap.get("rate")+"]");
     				System.out.println("grossRate["+updateStockMap.get("gross_rate")+"]");
     				System.out.println("acctCodeInv["+updateStockMap.get("acct_code_inv")+"]");
     				System.out.println("cctrCodeInv["+updateStockMap.get("cctr_code_inv")+"]");
     				System.out.println("batchNo["+updateStockMap.get("batch_no")+"]");
     				System.out.println("batchSize["+updateStockMap.get("batch_size")+"]");
     				System.out.println("suppCodeMfg["+updateStockMap.get("supp_code__mfg")+"]");
     				System.out.println("suppCode["+updateStockMap.get("supp_code")+"]");
              /*  	
                
               
                sql = " select count(1) from stock where item_code = ? and lot_no = ?  and site_code= ? and loc_code = ? and lot_sl = ? ";
                pstmt = conn.prepareStatement(sql);
         		pstmt.setString(1,this.itemCode);
         		pstmt.setString(2,this.lotNo);
         		pstmt.setString(3,this.siteCode);
         		pstmt.setString(4,this.lotCode);
         		pstmt.setString(5,this.lotSl);
         		rs = pstmt.executeQuery();
                if(rs.next())
                 {
                	 cnt6  = rs.getInt(1);
                	 
                 }
                rs.close();
        		rs = null;
        		pstmt.close();
        		pstmt = null;
                
                if(cnt6 > 0 )
     			{
     			
     				sql = " update stock set " +
                			" mfg_date = ?,potency_perc = ?,exp_date = ?,retest_date = ?,lot_no = ?,grade = ?,pack_code = ?," +
                			" site_code__mfg = ?,rate = ?,acct_code__inv = ?," +
                            " cctr_code__inv = ? , batch_no = ? ,gross_rate = ? , " +
         					" batch_size = ? , supp_code__mfg = ?  " +
         					" where item_code = ? and lot_no = ?  and site_code= ? and loc_code = ? and lot_sl = ? ";

     				

                	pstmt=conn.prepareStatement(sql);
                	String mfgDate2 = updateStockMap.get("mfg_date")==null?null:updateStockMap.get("mfg_date").toString();
                	if( mfgDate2 != null )
                	{
                		SimpleDateFormat simpleDateFormat1 = new SimpleDateFormat(genericUtility.getDBDateFormat());
                        mfgDate3 = simpleDateFormat1.parse(mfgDate2);
                    }
                	pstmt.setDate(1,mfgDate3 ==null?null:new java.sql.Date(mfgDate3.getTime()));
            		System.out.println("@@@@ mfgDate3::"+mfgDate3);
                	
                	pstmt.setString(2,updateStockMap.get("potency_perc")==null?null:updateStockMap.get("potency_perc").toString());
                	
                	String expDate2 = updateStockMap.get("exp_date")==null?null:updateStockMap.get("exp_date").toString();
                	if( expDate2 != null )
                	{
                		SimpleDateFormat simpleDateFormat1 = new SimpleDateFormat(genericUtility.getDBDateFormat());
                        expDate3 = simpleDateFormat1.parse(expDate2);
                    }
                	pstmt.setDate(3,expDate3 ==null?null:new java.sql.Date(expDate3.getTime()));
            		System.out.println("@@@@ expDate3::"+expDate3);
            	
                	String retestDate2 = updateStockMap.get("retest_date")==null?null:updateStockMap.get("retest_date").toString();
                	if( retestDate2 != null )
                	{
                		SimpleDateFormat simpleDateFormat1 = new SimpleDateFormat(genericUtility.getDBDateFormat());
                        retestDate3 = simpleDateFormat1.parse(retestDate2);
                    }
                	pstmt.setDate(4,retestDate3 ==null?null:new java.sql.Date(retestDate3.getTime()));
            		System.out.println("@@@@ retestDate3::"+retestDate3);
                	
                	pstmt.setString(5,updateStockMap.get("lot_no")==null?null:updateStockMap.get("lot_no").toString());
 					pstmt.setString(6,updateStockMap.get("grade")==null?null:updateStockMap.get("grade").toString());
 					pstmt.setString(7,updateStockMap.get("pack_pode")==null?null:updateStockMap.get("pack_pode").toString());
 					pstmt.setString(8,updateStockMap.get("site_code__mfg")==null?null:updateStockMap.get("site_code__mfg").toString());
 					pstmt.setString(9,updateStockMap.get("rate")==null?null:updateStockMap.get("rate").toString());
 					pstmt.setString(10,updateStockMap.get("acct_code_inv")==null?null:updateStockMap.get("acct_code_inv").toString());
 					pstmt.setString(11,updateStockMap.get("cctr_code_inv")==null?null:updateStockMap.get("cctr_code_inv").toString());
 					pstmt.setString(12,updateStockMap.get("batch_no")==null?null:updateStockMap.get("batch_no").toString());
 					pstmt.setString(13,updateStockMap.get("gross_rate ")==null?null:updateStockMap.get("gross_rate").toString());
 					pstmt.setString(14,updateStockMap.get("batch_size")==null?null:updateStockMap.get("batch_size").toString());
 					pstmt.setString(15,updateStockMap.get("supp_code__mfg")==null?null:updateStockMap.get("supp_code__mfg").toString());
 					pstmt.setString(16,updateStockMap.get("item_code")==null?null:updateStockMap.get("item_code").toString());
 					pstmt.setString(17,updateStockMap.get("lot_no")==null?null:updateStockMap.get("lot_no").toString());
 					pstmt.setString(18,updateStockMap.get("site_code")==null?null:updateStockMap.get("site_code").toString());
 					pstmt.setString(19,updateStockMap.get("loc_code")==null?null:updateStockMap.get("loc_code").toString());
 					pstmt.setString(20,updateStockMap.get("lot_sl")==null?null:updateStockMap.get("lot_sl").toString());
 					
     					cnt1 = pstmt.executeUpdate();
    					pstmt.close();
    		     		pstmt = null;
    		     		System.out.println("@@@@ cnt1:::"+cnt1);
    					if ( cnt1 == 0)
    					{
    						 errCode = "VTUPDDET";
    						 errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);
    						 return errString;
    					}
    				}  // if cnt6
    			*/
 					}
           }
           else
           {
        	    System.out.println("@@@@@ for insert..........");
        	    System.out.println("itemCode::["+updateStockMap.get("item_code")+"]");
         	    System.out.println("lotNo["+updateStockMap.get("lot_no") +"]");
 				System.out.println("grade["+updateStockMap.get("grade")+"]");
 				System.out.println("retestDate[today]"+today+"]");
 				System.out.println("expDate["+updateStockMap.get("exp_date")+"]");
 				System.out.println("mfgDate["+updateStockMap.get("mfg_date ")+"]");
 				System.out.println("packCode["+updateStockMap.get("pack_code ")+"]");
 				System.out.println("potencyPerc["+updateStockMap.get("potency_perc ")+"]");
 				System.out.println("siteCodeMfg["+updateStockMap.get("site_code__mfg")+"]");
 				System.out.println("rate ["+updateStockMap.get("rate")+"]");
 				System.out.println("grossRate["+updateStockMap.get("gross_rate ")+"]");
 				System.out.println("acctCodeInv["+updateStockMap.get("acct_code_inv ")+"]");
 				System.out.println("cctrCodeInv["+updateStockMap.get("cctr_code_inv")+"]");
 				System.out.println("batchNo["+updateStockMap.get("batch_no")+"]");
 				System.out.println("batchSize["+updateStockMap.get("batch_size")+"]");
 				System.out.println("suppCodeMfg["+updateStockMap.get("supp_code__mfg")+"]");
 				System.out.println("shelfLifeType value ["+shelfLifeType+"]");
 				System.out.println("suppCode ["+suppCode+"]");
 				
        	  sql = " insert into item_lot_info (item_code,lot_no,grade,crea_date,exp_date,mfg_date,retest_date,pack_code," +
        	  		" potency_perc,site_code__mfg,rate,gross_rate,acct_code__inv," +
                    " cctr_code__inv,batch_no,batch_size,supp_code__mfg,shelf_life_type,supp_code,chg_date,chg_term,chg_user) " +
                    " values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ";           // added by cpatil on 07-03-13 start for change term,date,user
        	  
              pstmt=conn.prepareStatement(sql);
                
                pstmt.setString(1,updateStockMap.get("item_code")==null?null:updateStockMap.get("item_code").toString());
				pstmt.setString(2,updateStockMap.get("lot_no")==null?null:updateStockMap.get("lot_no").toString());
				pstmt.setString(3,updateStockMap.get("grade")==null?null:updateStockMap.get("grade").toString());
				pstmt.setTimestamp(4,today);
				
				String expDate2 = updateStockMap.get("exp_date")==null?null:updateStockMap.get("exp_date").toString();
            	if( expDate2 != null )
            	{
            		SimpleDateFormat simpleDateFormat1 = new SimpleDateFormat(genericUtility.getDBDateFormat());
                    expDate3 = simpleDateFormat1.parse(expDate2);
                }
            	pstmt.setDate(5,expDate3 ==null?null:new java.sql.Date(expDate3.getTime()));
        		System.out.println("@@@@ expDate3::"+expDate3);
				
        		String mfgDate2 = updateStockMap.get("mfg_date")==null?null:updateStockMap.get("mfg_date").toString();
            	if( mfgDate2 != null )
            	{
            		SimpleDateFormat simpleDateFormat1 = new SimpleDateFormat(genericUtility.getDBDateFormat());
                   mfgDate3 = simpleDateFormat1.parse(mfgDate2);
                }
            	pstmt.setDate(6,mfgDate3 ==null?null:new java.sql.Date(mfgDate3.getTime()));
        		System.out.println("@@@@ mfgDate3::"+mfgDate3);
            	
        		String retestDate2 = updateStockMap.get("retest_date")==null?null:updateStockMap.get("retest_date").toString();
            	if( retestDate2 != null )
            	{
            		SimpleDateFormat simpleDateFormat1 = new SimpleDateFormat(genericUtility.getDBDateFormat());
                    retestDate3 = simpleDateFormat1.parse(retestDate2);
                }
            	pstmt.setDate(7,retestDate3 ==null?null:new java.sql.Date(retestDate3.getTime()));
        		System.out.println("@@@@ retestDate3::"+retestDate3);
            	
        		pstmt.setString(8,updateStockMap.get("pack_pode ")==null?null:updateStockMap.get("pack_pode").toString());
				pstmt.setString(9,updateStockMap.get("potency_perc ")==null?null:updateStockMap.get("potency_perc").toString());
				pstmt.setString(10,updateStockMap.get("site_code__mfg")==null?null:updateStockMap.get("site_code__mfg").toString());
				pstmt.setString(11,updateStockMap.get("rate")==null?null:updateStockMap.get("rate").toString());
				pstmt.setString(12,updateStockMap.get("gross_rate ")==null?null:updateStockMap.get("gross_rate").toString());
				pstmt.setString(13,updateStockMap.get("acct_code_inv ")==null?null:updateStockMap.get("acct_code_inv").toString());
				pstmt.setString(14,updateStockMap.get("cctr_code_inv")==null?null:updateStockMap.get("cctr_code_inv").toString());
				pstmt.setString(15,updateStockMap.get("batch_no")==null?null:updateStockMap.get("batch_no").toString());
				pstmt.setString(16,updateStockMap.get("batch_size")==null?null:updateStockMap.get("batch_size").toString());
				pstmt.setString(17,updateStockMap.get("supp_code_mfg")==null?null:updateStockMap.get("supp_code__mfg").toString());
				pstmt.setString(18,shelfLifeType);
				pstmt.setString(19,updateStockMap.get("supp_code")==null?null:updateStockMap.get("supp_code").toString());
				pstmt.setTimestamp(20,today);
				//pstmt.setDate(20,today);
				pstmt.setString(21,chgTerm);
				pstmt.setString(22,chgUser);
				
				cnt1 = pstmt.executeUpdate();
				pstmt.close();
		     	pstmt = null;
				//	if ( cnt1 == 0)
				//	{
				//		 errCode = "VINSERT5";
				//		 errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);
				//		 return errString;
				//	}
			
           }
			//} //add
		 } 
        }
        catch(Exception e)
        {
            System.out.println("Exception "+e);
            e.printStackTrace();
            throw new ITMException(e);
        }
        return updateStockMap;
	}
	private String adjustStkValue(Connection conn) throws ITMException, Exception
    {
        String prdCode = "",sql = "";
        String invLink = "",errString = "",acctAdj = "",cctrAdj = "";
        double effQty = 0,afterQty = 0,beforeQty = 0;
        String updStkCctrCodeInv = "";
        String remarks = "";
        String refSer = "",adjWin = "";
        String acctCodeCr = "",cctrCodeCr = "",acctCodeDr = "",cctrCodeDr = "";
        FinCommon finCommon = null;
        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        String keyString = "",keyCol = "",tranSer = "";
        String invTraceNo = "",tranIDAdj = "",finEntity = "",currCode = "";
        double exchRate = 0;
        HashMap glTrace = null;
        try{
            finCommon = new FinCommon();
            updStkCctrCodeInv = this.cctrCodeInv;
            stmt = conn.createStatement();
            /*if gs_run_mode <> 'B' then
                s_updatestock.currdate = ldt_today
                s_updatestock.trandate = ldt_today
            else
                if isnull(s_updatestock.currdate) then
                    s_updatestock.currdate = ldt_today
                    s_updatestock.trandate = ldt_today
                else
                    s_updatestock.trandate = s_updatestock.currdate
                    end if
            end if 
            ls_prdcode = string(s_updatestock.trandate,'yyyymm')*/
            
            invLink = finCommon.getFinparams("999999", "INV_ACCT_AISS",conn);
            System.out.println("invLink :"+invLink);
            if(invLink == null || invLink.equalsIgnoreCase("NULLFOUND")){
                errString = itmDBAccessEJB.getErrorString("","VTFINPARM1","","",conn);
                return errString;
            }
            acctAdj = finCommon.getFinparams("999999", "CONS_ACCT_RNDOFF",conn);
            System.out.println("acctAdj :"+acctAdj);
            if(acctAdj == null || acctAdj.equalsIgnoreCase("") || acctAdj.equalsIgnoreCase("NULLFOUND")){
                errString = itmDBAccessEJB.getErrorString("","VTFINPARM1","","",conn);
                return errString;
            }
            cctrAdj = finCommon.getFinparams("999999", "CONS_CCTR_RNDOFF",conn);
            if(cctrAdj == null || cctrAdj.equalsIgnoreCase("") || cctrAdj.equalsIgnoreCase("NULLFOUND")){
                cctrAdj = "    ";
            }
            if(updStkCctrCodeInv == null || updStkCctrCodeInv.trim().length() == 0){
                updStkCctrCodeInv = "   ";
            }
            remarks = "Adjustment of Stock Value by System";
            
            if(this.netAmt > 0){ // adj_rcp
                refSer = "ADJRCP";
                adjWin = "w_adj_rcp";
                acctCodeDr = this.acctCodeInv;
                cctrCodeDr = this.cctrCodeInv;
                acctCodeCr = acctAdj;   
                cctrCodeCr =  cctrAdj;  
            }else{//adj_rcp
                refSer = "ADJISS";
                adjWin = "w_adj_iss";
                acctCodeDr = acctAdj;
                cctrCodeDr = cctrAdj;
                acctCodeCr = this.acctCodeInv;   
                cctrCodeCr = this.cctrCodeInv;  
            }
            sql = "select key_string from transetup where tran_window = '"+adjWin+"'";
            System.out.println("SQL :"+sql);
            rs = stmt.executeQuery(sql);
            if(rs.next()){
                keyString = rs.getString(1);
            }
            if(keyString == null || keyString.trim().length() == 0){
                sql = "select key_string from transetup where tran_window = 'GENERAL'";
                System.out.println("SQL :"+sql);
                rs = stmt.executeQuery(sql);
                if(rs.next()){
                    keyString = rs.getString(1);
                }
            }
            
            String tranDate = genericUtility.getValidDateString(new java.sql.Date(this.tranDate.getTime()).toString(),genericUtility.getDBDateFormat(),genericUtility.getApplDateFormat());
            String XMLString = "";
            XMLString = "<?xml version=\"1.0\"?>\r\n<Root>\r\n<header>"+
                        "\r\n</header><Detail1><tran_id></tran_id><tran_date>"+tranDate+"</tran_date>"+
                        "\r\n<site_code>"+this.siteCode+"</site_code>"+
                        "</Detail1></Root>";
            TransIDGenerator tg = new TransIDGenerator(XMLString, userId, CommonConstants.DB_NAME);
            tranIDAdj = tg.generateTranSeqID(refSer, "tran_id", keyString, conn);
            
            keyString = "";
            sql = "select key_string from transetup where tran_window = 'T_INVTRACE'";
            System.out.println("SQL :"+sql);
            rs = stmt.executeQuery(sql);
            if(rs.next()){
                keyString = rs.getString(1);                
            }
            rs.close();
            
            
            TransIDGenerator tg1 = new TransIDGenerator(XMLString, userId, CommonConstants.DB_NAME);
            invTraceNo = tg1.generateTranSeqID("ITRACE", "tran_id", keyString, conn);
            
           sql = "Insert into invtrace (tran_id, tran_date, ref_ser, ref_id, ref_line, order_id, " +
                "item_code, site_code, loc_code, unit, lot_no, quantity, eff_qty, eff_date, " +
                "chg_win,rate, gross_rate, amount,chg_user, chg_term, lot_sl, qty_before, " +
                "qty_after,reas_code,sundry_type,sundry_code, ref_date,remarks," +
                "acct_code__dr, cctr_code__dr, acct_code__cr, cctr_code__cr, grade) " +
                "values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
               
           System.out.println("SQL :"+sql);
           pstmt = conn.prepareStatement(sql);
           pstmt.setString(1,invTraceNo);
           pstmt.setTimestamp(2,this.currDate);
           pstmt.setString(3,refSer);
           pstmt.setString(4,this.tranId);
           pstmt.setString(5,this.lineNo);
           pstmt.setString(6,this.sorderNo);
           pstmt.setString(7,this.itemCode);
           pstmt.setString(8,this.siteCode);
           pstmt.setString(9,this.locationCode);
           pstmt.setString(10,this.unit);
           pstmt.setString(11,this.lotNo);
           pstmt.setDouble(12,this.qtyStduom);
           pstmt.setDouble(13,effQty); 
           pstmt.setTimestamp(14,this.tranDate);
           pstmt.setString(15," ");
           pstmt.setDouble(16,this.rate);
           pstmt.setDouble(17,this.rate);
           pstmt.setDouble(18,this.netAmt);
           pstmt.setString(19,userId);
           pstmt.setString(20,termId);
           pstmt.setString(21,this.lotSl);
           pstmt.setDouble(22,beforeQty);
           pstmt.setDouble(23,afterQty);
           pstmt.setString(24,this.reasCode);
           pstmt.setString(25,this.sundryType);
           pstmt.setString(26,this.sundryCode);
           pstmt.setTimestamp(27,this.tranDate);
           pstmt.setString(28,remarks);
           pstmt.setString(29,acctCodeDr);
           pstmt.setString(30,cctrCodeDr);
           pstmt.setString(31,acctCodeCr);
           pstmt.setString(32,cctrCodeCr);
           pstmt.setString(33,this.grade);
           
           System.out.println("TRAN_ID :"+invTraceNo);
           System.out.println("TRAN_DATE :"+this.currDate);
           System.out.println("ITEM_CODE :"+this.itemCode);
           System.out.println("SITE_CODE :"+this.siteCode);
           System.out.println("LOC_CODE :["+this.locationCode+"]");
           System.out.println("UNIT :"+this.unit);
           System.out.println("EFF_DATE :"+this.tranDate);
           System.out.println("CHG_USER :");
           System.out.println("CHG_TERM :");
           
           pstmt.executeUpdate();
           
           sql = "select fin_entity from site where site_code = '"+this.siteCode+"'";
           System.out.println("SQL :"+sql);
           rs = stmt.executeQuery(sql);
           if(rs.next()){
               finEntity = rs.getString(1);
           }
           rs.close();
           
           sql = "select curr_code from parameter";
           System.out.println("SQL :"+sql);
           rs = stmt.executeQuery(sql);
           if(rs.next()){
               currCode = rs.getString(1);
           }
           rs.close();
           exchRate = 1;
           System.out.println("this.netAmt : "+this.netAmt);
           if(invLink.equalsIgnoreCase("Y")){
               if(this.netAmt != 0){
                   glTrace = new HashMap();
                   glTrace.put("tran_date",this.tranDate);
                   glTrace.put("eff_date",this.tranDate);
                   glTrace.put("fin_entity",finEntity);
                   glTrace.put("site_code",this.siteCode);
                   glTrace.put("sundry_type","O");
                   glTrace.put("sundry_code","");
                   glTrace.put("emp_code","");
                   glTrace.put("anal_code","");
                   glTrace.put("curr_code",currCode);
                   glTrace.put("exch_rate","1");
                   
                   if(this.netAmt > 0){
                       glTrace.put("dr_amt","0");
                       glTrace.put("cr_amt",Double.toString(this.netAmt));
                       glTrace.put("acct_code",acctAdj);
                       glTrace.put("cctr_code",cctrAdj);
                   }else{
                       glTrace.put("dr_amt","0");
                       glTrace.put("cr_amt",Double.toString( 0 - this.netAmt));
                       glTrace.put("acct_code",this.acctCodeInv);
                       glTrace.put("cctr_code",this.cctrCodeInv);
                   }
                   glTrace.put("ref_type","D");
                   glTrace.put("remarks",remarks);
                   glTrace.put("ref_ser",refSer);
                   glTrace.put("ref_id",tranIDAdj);
                   
                   errString = finCommon.glTraceUpdate(glTrace,conn);
                   System.out.println("Returning String :"+errString);
                   if(errString.trim().length() > 0){
                       return errString;
                   }
                   if(this.netAmt > 0){
                       glTrace.put("dr_amt",Double.toString(this.netAmt));
                       glTrace.put("cr_amt","0");
                       glTrace.put("acct_code",this.acctCodeInv);
                       glTrace.put("cctr_code",this.cctrCodeInv);
                   }else{
                       glTrace.put("dr_amt",Double.toString(0 - this.netAmt));
                       glTrace.put("cr_amt","0");
                       glTrace.put("acct_code",acctAdj);
                       glTrace.put("cctr_code",cctrAdj);
                   }
                   errString = finCommon.glTraceUpdate(glTrace,conn);
                   System.out.println("Returning String :"+errString);
                   if(errString.trim().length() > 0){
                       return errString;
                   }
                   
                   errString = finCommon.checkGlTranDrCr(refSer,tranIDAdj,conn);
                   
               }
           }
           
        }catch(Exception e){
            System.out.println("Exception e "+e);
            e.printStackTrace();
            throw new ITMException(e);
        }finally{
            try{
                if(rs != null){rs.close();rs = null;}
                if(stmt != null){stmt.close();stmt = null;}
                if(pstmt != null){pstmt.close();pstmt = null;}
            }catch(Exception t){throw new ITMException(t);}
        }
        System.out.println("Returning Value from adjustStkValue : "+errString);
        return errString;
    }
	public String updateInvallocTrace(HashMap invallocTraceMap, Connection conn) throws ITMException,Exception
	{

		String fileDest = "";
		double tempAllocQty = 0;
		try
		{
			//changes by gulzar on 12/12/2011 to write the log file in jboss log location  
			//f = new FileWriter("invalloc.log", true); 
			fileDest = CommonConstants.JBOSSHOME + File.separator + "server" + File.separator + "default" + File.separator + "log" + File.separator + "invalloc.log";
			//f = new FileWriter( fileDest, true); 
		}
		catch(Exception e)
		{
			System.out.println(e.getMessage());
		//	writeException(e, fileDest );
		}
		String errString = "";
		String errCode = "" ;
		String sql = null ;
		String sqlUpdate = null ;
		String keyStringQuery = null ;
		CommonConstants commonConstants = new CommonConstants();
		String refSer = (String)invallocTraceMap.get("ref_ser");
		String refId = (String)invallocTraceMap.get("ref_id");
		String refLine = (String)invallocTraceMap.get("ref_line");
		String siteCode = (String)invallocTraceMap.get("site_code");
		String itemCode = (String)invallocTraceMap.get("item_code");
		String locCode = (String)invallocTraceMap.get("loc_code");
		String lotNo = (String)invallocTraceMap.get("lot_no");
		String lotSl = (String)invallocTraceMap.get("lot_sl");
		double allocQty = ((Double)invallocTraceMap.get("alloc_qty")).doubleValue();
		String chgUser = (String)invallocTraceMap.get("chg_user");
	    String chgTerm = (String)invallocTraceMap.get("chg_term");
	    String chgWin = (String)invallocTraceMap.get("chg_win");
	    if(lotNo == null)
		lotNo = " ";
		if(lotSl == null)
		lotSl = " ";

		//Changed by gulzar on 12/12/2011
		if ( chgUser == null || chgUser.trim().length() == 0 )
		{
			chgUser = "SYSTEM";
		}
		if ( chgTerm == null || chgTerm.trim().length() == 0 )
		{
			chgTerm = "SYSTEM";
		}
		//End changes by gulzar on 12/12/2011
	    
		java.sql.Date chgDate = new java.sql.Date(System.currentTimeMillis());
		java.sql.Date tranDate = new java.sql.Date(System.currentTimeMillis());
		PreparedStatement pstmt = null ;
		ResultSet rSet = null, rs = null ;
		Statement stmt = null ;
		String tranId = null ;
	   
	    try
	    {
	    	keyStringQuery = "SELECT KEY_STRING, TRAN_ID_COL, REF_SER FROM TRANSETUP WHERE UPPER(TRAN_WINDOW) = 'T_ALLOCTRACE'";
	    	System.out.println("keyStringQuery--------->>"+keyStringQuery);
	 
			stmt = conn.createStatement();
			rSet = stmt.executeQuery(keyStringQuery);
			System.out.println("keyString :"+rSet.toString());
			String tranSer1 = "";
			String keyString = "";
			String keyCol = "";
			if (rSet.next())
			{
				keyString = rSet.getString(1);
				keyCol = rSet.getString(2);
				tranSer1 = rSet.getString(3);				
			}
			rSet.close();
			stmt.close();
			rSet = null;
			stmt = null;
			// 31/10/13 manoharan locking to be done before allocation
			if (CommonConstants.DB_NAME.equalsIgnoreCase("DB2") || CommonConstants.DB_NAME.equalsIgnoreCase("MYSQL") ){
				sql =" select alloc_qty from stock where item_code = ?"
			  	+" and site_code = ?" 
				+" and loc_code  = ?" 
				+" and lot_no = ?"
				+"and lot_sl = ? for update ";
			}
			else if (CommonConstants.DB_NAME.equalsIgnoreCase("MSSQL")){
				sql =" select alloc_qty from stock (updlock) where item_code = ?"
			  	+" and site_code = ?" 
				+" and loc_code  = ?" 
				+" and lot_no = ?"
				+"and lot_sl = ?  ";
			}
			else
			{
				sql =" select alloc_qty from stock where item_code = ?"
			  	+" and site_code = ?" 
				+" and loc_code  = ?" 
				+" and lot_no = ?"
				+"and lot_sl = ? for update nowait ";
				
			}
			
            pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,itemCode);
			pstmt.setString(2,siteCode);
			pstmt.setString(3,locCode);
			pstmt.setString(4,lotNo);
			pstmt.setString(5,lotSl);
			
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				tempAllocQty = rs.getDouble(1);
			}
			else
			{
				errString = "VTLCKERR";
			}
			pstmt.close();
			rs.close();
			if (errString != null && errString.trim().length() > 0)
			{
				return errString;
			}

				
			// end 
			
		//	f.write(keyString + " " + keyCol + " " + tranSer1+ "\r\n");
			System.out.println("keyString :"+ keyString);
			System.out.println("keyCol :"+ keyCol);
			System.out.println("tranSer1 :"+ tranSer1);
			String xmlValues = "";
			String tranDateStr = getCurrdateAppFormat(); //added by rajendra on 17/04/09 for tran date
			xmlValues ="<?xml version=\"1.0\" encoding=\"utf-8\"?><Root>";
			xmlValues = xmlValues + "<Header></Header>";
			xmlValues = xmlValues + "<Detail1>";
			xmlValues = xmlValues +	"<tran_id></tran_id>";
			xmlValues = xmlValues + "<site_code>" + siteCode + "</site_code>";
			xmlValues = xmlValues + "<tran_date>" + tranDateStr + "</tran_date>"; //added by rajendra on 17/04/09
			xmlValues = xmlValues +"</Detail1></Root>";
			System.out.println("xmlValues  :["+xmlValues+"]");
			TransIDGenerator tg = new TransIDGenerator(xmlValues, "BASE", commonConstants.DB_NAME);
			tranId = tg.generateTranSeqID(tranSer1, keyCol, keyString, conn);
		//	f.write("trace tran id ... " +tranId + "\r\n");
			System.out.println("tranId :"+tranId);
		
			sql = "INSERT INTO INVALLOC_TRACE (TRAN_ID, TRAN_DATE, REF_SER, REF_ID,"
				+"REF_LINE,ITEM_CODE, SITE_CODE, LOC_CODE,LOT_NO, LOT_SL, ALLOC_QTY, CHG_WIN," 
				+"CHG_USER, CHG_TERM, CHG_DATE )VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
				
			pstmt = conn.prepareStatement(sql);
		
			System.out.println("trans id="+ tranId);
			System.out.println("tranDate="+ tranDate);
			System.out.println("refSer="+ refSer);
			System.out.println("refId="+ refId);
			System.out.println("refLine="+ refLine);
			System.out.println("itemCode="+ itemCode);
			System.out.println("siteCodeShip="+ siteCode);
			System.out.println("locCode="+ locCode);
			System.out.println("lotNo="+ lotNo);
			System.out.println("LOT_SL="+ lotSl);
			System.out.println("ALLOC_QTY="+ allocQty);
			System.out.println("chgWin="+ chgWin);
			System.out.println("chgUser="+ chgUser);
			System.out.println("chgTerm="+ chgTerm);
			System.out.println("chgDate="+ chgDate);
//			f.write("trans id="+ tranId+"\n");
//			f.write("tranDate="+ tranDate+"\n");
//			f.write("refSer="+ refSer+"\n");
//			f.write("refId="+ refId+"\n");
//			f.write("refLine="+ refLine+"\n");
//			f.write("itemCode="+ itemCode+"\n");
//			f.write("locCode="+ locCode+"\n");
//			f.write("lotNo="+ lotNo+"\n");
//			f.write("lotSl="+ lotSl+"\n");
//			f.write("ALLOC_QTY="+ allocQty+"\n");
			
			
		
			pstmt.setString(1,tranId);
			pstmt.setDate(2,tranDate);
			pstmt.setString(3,refSer);
			pstmt.setString(4,refId);
			pstmt.setString(5,refLine);
			pstmt.setString(6,itemCode );
			pstmt.setString(7,siteCode);
			pstmt.setString(8,locCode);
			pstmt.setString(9,lotNo );
			pstmt.setString(10,lotSl);
			pstmt.setDouble(11,allocQty);
			pstmt.setString(12,chgWin);
			pstmt.setString(13,chgUser);
			pstmt.setString(14,chgTerm);
			pstmt.setDate(15,chgDate);
			pstmt.executeUpdate();
			
			//added by msalam on 281209 start
			pstmt.close();
			pstmt = null;
			//added by msalam on 281209 end
			System.out.println("insertion of sql inside updateInvallocTrace success on date "+ chgDate);
			
			System.out.println("Allocate trace Updated...............................");
		
			sqlUpdate = "UPDATE STOCK SET ALLOC_QTY =(CASE WHEN ALLOC_QTY IS NULL THEN 0 ELSE ALLOC_QTY END) + ? "
					  +"WHERE ITEM_CODE = '"+itemCode+"' AND SITE_CODE = '"+ siteCode+"' AND LOC_CODE ='"+locCode+"'  AND LOT_NO = '"+lotNo+"' AND LOT_SL = '"+lotSl+"'";
			pstmt = conn.prepareStatement(sqlUpdate);
			pstmt.setDouble(1,allocQty) ;
			
				System.out.println("sqlUpdate "+ sqlUpdate);
				int i = pstmt.executeUpdate() ;
				pstmt.close();
				pstmt = null;

				System.out.println("Records updated "+ i);
//				if(i>0)
//				{
//					f.write("sqlUpdate="+ sqlUpdate+"\n"+"Stock update success fully........................."+i+"\n");
//				}
//				else
//				{
//					throw new ITMException(new Exception("Unable to update stock for [" + sqlUpdate + "]"));
//				}
					
//			}
			System.out.println("Updated End.") ;
//			f.write("Updated End..........");
		}
		catch(SQLException e)
		{
		//	f.write("TRANSATION ROLL BACK IN updateInvallocTrace"+"\n");
			try{conn.rollback();}catch(Exception ee2){}
			//writeException(e, fileDest);
			System.out.println("SQLException :updateInvallocTrace : "  + sqlUpdate + "\n" +e.getMessage());
			System.out.println("ALLOC_QTY : " + allocQty);
			System.out.println("ITEM_CODE : " + itemCode);
			System.out.println("SITE_CODE : " + siteCode);
			System.out.println("LOC_CODE : " + locCode);
			System.out.println("LOT_NO : " + lotNo);
			System.out.println("LOT_SL : " + lotSl);
			errString = e.getMessage();
			e.printStackTrace();
			//return errString;
			throw new ITMException(e);
					
		}
		catch(Exception e)
		{
			//writeException(e, fileDest);
			System.out.println("Exception :updateInvallocTrace :"  + sqlUpdate + "\n" +e.getMessage());
			errString = e.getMessage();
			e.printStackTrace();
			try
			{
			//	f.write("TRANSATION ROLL BACK IN updateInvallocTrace"+"\n");
				try{conn.rollback();}catch(Exception ee2){}
			
			}
			catch(Exception e1)
			{
			//	writeException(e, fileDest);
				errString = e1.getMessage();
				e = e1;				
			}
			//return errString;	
			throw new ITMException(e);
		}
		finally
		{
			try
			{
		//		f.close();
				if(conn != null)
				{	
					if(pstmt != null)
					{
						pstmt.close();
						pstmt = null;
					}
					if(stmt != null)
					{
						stmt.close();
						stmt = null;
					}
				//	conn.close();
				//	conn = null;
				}
			}
			catch(Exception e)
			{
				try{
					conn.rollback();
				//f.write("TRANSATION ROLL BACK IN updateInvallocTrace"+"\n");
				}catch(Exception ee2){}
				errString = e.getMessage(); 
				e.printStackTrace();
				//writeException(e, fileDest);
				return errString;	
			}
			//return errString;	
		}
		return errString;
	
	}
	private String getCurrdateAppFormat()
	{
        String s = "";
        GenericUtility genericUtility = GenericUtility.getInstance();
        try
        {
            java.util.Date date = null;
            Timestamp timestamp = new Timestamp(System.currentTimeMillis());
            System.out.println(genericUtility.getDBDateFormat());

            SimpleDateFormat simpledateformat = new SimpleDateFormat(genericUtility.getDBDateFormat());
            date = simpledateformat.parse(timestamp.toString());
            timestamp = Timestamp.valueOf(simpledateformat.format(date).toString() + " 00:00:00.0");
            s = (new SimpleDateFormat(genericUtility.getApplDateFormat())).format(timestamp).toString();
        }
        catch(Exception exception)
        {
            System.out.println("Exception in  getCurrdateAppFormat " + exception.getMessage());
        }
        return s;
    }
}