package ibase.webitm.ejb.mfg;

import ibase.system.config.AppConnectParm;
import ibase.system.config.ConnDriver;
import ibase.utility.E12GenericUtility;
import ibase.webitm.ejb.ActionHandlerEJB;
import ibase.webitm.ejb.E12CreateBatchLoadEjb;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.ejb.MasterStatefulLocal;
import ibase.webitm.ejb.dis.DistCommon;
import ibase.webitm.ejb.dis.StockUpdate;
import ibase.webitm.ejb.sys.CreateRCPXML;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;

import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.HashMap;

import javax.ejb.Stateless;
import javax.naming.InitialContext;

import org.w3c.dom.Document;


@Stateless
public class QcOdrProvisionalReleaseConf extends ActionHandlerEJB implements
QcOdrProvisionalReleaseConfLocal, QcOdrProvisionalReleaseConfRemote                                                                            
{                      

	public String actionHandler() throws RemoteException, ITMException
	{
		return "";
	}

	public String actionHandler(String actionType, String xmlString,
			String objContext, String xtraParams) throws RemoteException,
			ITMException 
			{
		System.out.println("item actionHandler(...) called............");
		String str = "";
		System.out.println("actionType---" + actionType);
		System.out.println("xmlString---" + xmlString);
		System.out.println("objContext---" + objContext);
		System.out.println("xtraParams---" + xtraParams);
		return str;
			}

	public String confirm(String tranId, String xtraParams, String forcedFlag)
			throws RemoteException, ITMException
			{
		System.out.println("QCTransferConf confirm called...230914...........");
		String confirmed = "",locCodeTo="",locCodeFrm="",trnfQty="",loginSiteCode="",itemCode="",locCode="",porcpNo="",lotNo="",lotSl="";
		String batchNo="",siteCode="",locDescr="",itemDescr="",siteDescr="",userId="",termId="",xmlString=null,retString=null,quantity=null;
//		int trnfQty=0;
//		double quantity=0.0;
		String sql = "";
		Connection conn = null;
		PreparedStatement pstmt = null;
		ITMDBAccessEJB itmDBAccessEJB = null;
		String errString = "",errCode="";
		ResultSet rs = null;
		int recCnt = 0, cnt = 0;
		StringBuffer xmlBuff = null;
		ITMDBAccessEJB itmDBAccessLocal = new ITMDBAccessEJB();

		try 
		{
			E12GenericUtility genericUtility=new E12GenericUtility();
			Calendar currentDate = Calendar.getInstance();
			SimpleDateFormat sdf = new SimpleDateFormat(
					genericUtility.getApplDateFormat());
			String sysDate = sdf.format(currentDate.getTime());
			System.out.println("Now the date is :=>  " + sysDate);
			
			itmDBAccessEJB = new ITMDBAccessEJB();
			ConnDriver connDriver = null;
			connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");
			conn.setAutoCommit(false);

			if (errString == null || errString.trim().length() == 0)
			{
				//if ( tranIdFr.equalsIgnoreCase(tranIdTo) )
				//{
				//	tranId = tranIdFr;
				//}

				if (tranId != null && tranId.trim().length() > 0) 
				{

					sql = "select status from qc_order where QORDER_NO=?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, tranId);
					rs = pstmt.executeQuery();
					if (rs.next()) 
					{
						confirmed = rs.getString("status");
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					
					System.out.println("Status from QC OREDER &&&&&&&&&&&:- "+confirmed);
					// TEMP Comment VALLABH KADAM 
					if((confirmed.equalsIgnoreCase("C"))){
						errCode = "QCTRFORDNC";   // QC ord not confirmed.
						errString = itmDBAccessLocal.getErrorString("",errCode,"");
					}
					
					sql = "select count(*) as cnt from qc_transfer where qorder_no=? and confirmed='N'";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, tranId);
					rs = pstmt.executeQuery();
					if (rs.next()) 
					{
						cnt+=rs.getInt("cnt");
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					
					System.out.println("Qc transfer unconfirmed count :- "+cnt);
					if(cnt>0)
					{
						errCode = "QCTRFUNCNF"; //QC transfer is still unconfirmed.  
						errString = itmDBAccessLocal.getErrorString("",errCode,"");
					}
					
					sql = "select LOC_CODE,QTY_TRANSFERED from qc_order where QORDER_NO=?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, tranId);
					rs = pstmt.executeQuery();
					if (rs.next()) 
					{
						locCodeFrm=rs.getString("LOC_CODE").trim();
						trnfQty=rs.getString("QTY_TRANSFERED");
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					
					System.out.println("Location code from &&&&&&&&&:- "+locCodeFrm);
					locCodeTo=locCodeFrm+"Q";        //Generate Location To
					System.out.println("Location code to &&&&&&&&&&&:- "+locCodeTo);
					
//					if(trnfQty==null || trnfQty.equalsIgnoreCase("") || Integer.parseInt(trnfQty)<=0){
//						errCode = "QCTRFQTYNV"; //QC transfer quantity is not valid.  
//						errString = itmDBAccessLocal.getErrorString("",errCode,"");
//					}
					
					sql = "select count(*) as cnt from location where loc_code=?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, locCodeTo);
					rs = pstmt.executeQuery();
					if (rs.next()) 
					{
						cnt=rs.getInt("cnt");
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					                                               //TEMP COMENT VALLABH KADAM
					if(cnt<=0){
						errCode = "QCTRLOCNVL"; //QC transfer Location code TO not valid.  
						errString = itmDBAccessLocal.getErrorString("",errCode,"");
					}
					
					
				}
				if (errString == null || errString.trim().length() == 0) 
				{
//					errCode = "VTCONFIRM";
//					errString = itmDBAccessLocal.getErrorString("",errCode,"");
//					return errString;
//					loginSiteCode = genericUtility.getValueFromXTRA_PARAMS(
//							xtraParams, "loginSiteCode");
					userId = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginCode"); 
					System.out.println("--login code--"+userId);
					termId =  genericUtility.getValueFromXTRA_PARAMS(xtraParams, "termId"); 
					System.out.println("--term id--"+termId);
					//Got sysdate above.
					
					sql = " Select quantity, item_code, loc_code, porcp_no, lot_no, lot_sl, batch_no, site_code,QTY_TRANSFERED"
							+ " from 	 qc_order where  qorder_no = ? ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, tranId);
					rs = pstmt.executeQuery();
					if (rs.next()) {
//						quantity = rs.getDouble("quantity");
						quantity = checkNull(rs.getString("quantity").trim());
						itemCode = checkNull(rs.getString("item_code").trim());
						locCodeFrm = checkNull(rs.getString("loc_code").trim());
						porcpNo = checkNull(rs.getString("porcp_no").trim());
						lotNo = checkNull(rs.getString("lot_no").trim());
						lotSl = checkNull(rs.getString("lot_sl").trim());
						batchNo = checkNull(rs.getString("batch_no").trim());
						siteCode = checkNull(rs.getString("site_code").trim());
						trnfQty=checkNull(rs.getString("QTY_TRANSFERED").trim());
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;	

					System.out.println("Location code from &&&&&&&&&:- "+locCodeFrm);
					locCodeTo=locCodeFrm+"Q";        //Generate Location To
					System.out.println("Location code to &&&&&&&&&&&:- "+locCodeTo);
					
					trnfQty=trnfQty==null?"0":trnfQty;
					quantity=quantity==null?"0":quantity;
					
					System.out.println("Lot sl &&&&&&&&&&&&:-"+lotSl);
					System.out.println("Lot No &&&&&&&&&&&&:-"+lotNo);
					
					xmlBuff = new StringBuffer();
					System.out.println("--XML CREATION --");
					
				    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("qc_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=\"sorder\" objContext=\"1\">");  
					xmlBuff.append("<attribute selected=\"N\" updateFlag=\"A\" status=\"N\" pkNames=\"\"/>");
					xmlBuff.append("<tran_id/>");
					xmlBuff.append("<site_code><![CDATA["+siteCode+"]]></site_code>");
					xmlBuff.append("<tran_date><![CDATA["+sysDate+"]]></tran_date>");
					xmlBuff.append("<qorder_no><![CDATA["+tranId+"]]></qorder_no>");
					xmlBuff.append("<quantity__o><![CDATA["+quantity+"]]></quantity__o>");
					xmlBuff.append("<quantity><![CDATA["+trnfQty+"]]></quantity>");
					xmlBuff.append("<loc_code__trf><![CDATA["+locCodeTo+"]]></loc_code__trf>");
					xmlBuff.append("<confirmed><![CDATA["+'N'+"]]></confirmed>");
					xmlBuff.append("<conf_date><![CDATA["+sysDate+"]]></conf_date>");
					xmlBuff.append("<chg_user><![CDATA["+userId+"]]></chg_user>");
					xmlBuff.append("<chg_date><![CDATA["+sysDate+"]]></chg_date>");
					xmlBuff.append("<chg_term><![CDATA["+termId+"]]></chg_term>");
					xmlBuff.append("<lot_sl><![CDATA["+lotSl+"]]></lot_sl>");
					xmlBuff.append("<porcp_no><![CDATA["+porcpNo+"]]></porcp_no>");
					xmlBuff.append("<loc_code><![CDATA["+locCodeFrm+"]]></loc_code>");
					xmlBuff.append("<item_code><![CDATA["+itemCode+"]]></item_code>");
					xmlBuff.append("<lot_no><![CDATA["+lotNo+"]]></lot_no>");
					xmlBuff.append("<remarks><![CDATA[]]></remarks>");
					xmlBuff.append("<net_weight><![CDATA[]]></net_weight>");
					xmlBuff.append("<no_art><![CDATA[]]></no_art>");
					xmlBuff.append("<dimension><![CDATA[]]></dimension>");
					
					xmlBuff.append("</Detail1>");
					
					//Now Save generated XML
					
					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,conn);
					System.out.println("@@@@@2: retString:"+retString);
					System.out.println("--retString finished--");
					System.out.println("RetString after Transaction :- "+retString);
					if (retString.indexOf("Success") > -1)
					{
						System.out.println("Success *************************************");
						String[] arrayForTranId = retString.split("<TranID>");
						int endIndex = arrayForTranId[1].indexOf("</TranID>");
						String tranIdForDesp = arrayForTranId[1].substring(0,endIndex);
						System.out.println("-tranIdForDesp &&&&&&&&&&& :- "+tranIdForDesp);
						
						
						System.out.println("In CASE 2 #######");	
						sql="UPDATE qc_order set test_stat='R' where QORDER_NO=?";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1,tranId);
						cnt = pstmt.executeUpdate();
						pstmt.close();
						pstmt = null;
						
						System.out.println("Qc orser update count &&&&&&&:-"+cnt);
						
						errCode = "VTQCTRNSCC";// Message after successful Transfer
						errString = itmDBAccessLocal.getErrorString("",errCode,"");
						return errString;
					}
					else{
						errCode = "VTWOCFAIL";// Transaction fail.
						errString = itmDBAccessLocal.getErrorString("",errCode,"");
						return errString;
					}
				}					
				else
				{
					System.out.println("In Else Found Validation error &&&&&&&");
					return errString;
				}
			} // end if errstrng


		} catch (Exception e) 
		{
			e.printStackTrace();
			throw new ITMException(e);
		} 
		finally 
		{
			try 
			{
				if (errString != null && errString.trim().length() > 0	&& !("VTQCTRNSCC".equalsIgnoreCase(errCode))) 
				{
					conn.rollback();
					System.out.println("@@@@ Transaction rollback...["+errString+"]::errCode["+errCode+"]");
					conn.close();
					conn = null;
				}
				else 
				{
					conn.commit(); // test
					System.out.println("@@@@ Transaction commit....["+errString+"]::errCode["+errCode+"]");
					conn.close();
					conn = null;
				}
			}
			catch (Exception e) 
			{
				e.printStackTrace();
				throw new ITMException(e);
			}
		}
		return errString;
			}

	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 String confirmQcTransfer(String tranId, int i, String xtraParams, Connection conn)
//			throws RemoteException, ITMException
//
//			{
//		PreparedStatement pstmt = null, pstmtSql = null;
//		ResultSet rs = null;
//		int cnt = 0,updCnt=0;
//		String retString = "", sql = "", errCode = "";
//		Timestamp sysDate = null, tranDate = null;
//		GenericUtility genericUtility = GenericUtility.getInstance();
//		ITMDBAccessEJB itmDBAccessLocal = new ITMDBAccessEJB();
//		String confirmed="",qorderNo="",siteCode="",toLoc="",lotSl="",lotNo="",porcpNo="",fromLoc="",itemCode="";
//		String status="",ediOption="",dataStr = "";
//		double qty=0,qcQty=0,stkGrossWt=0,stkTareWt=0,stkNetWt=0,qtyPerArt=0,grossWtPerArt=0,tareWtPerArt=0,stockQty=0;
//		HashMap stockUpd = new HashMap();
//
//		String available="";
//		double aprvLeadTime = 0;
//		Timestamp qcOrderDate = null,newQCOrderDate=null; // added by cpatil on 02/11/13
//		String qcOrderItem="";
//		String qcOrdItemUnit="",byprodUnit="";
//		double convertedQty=0,netWeight=0;
//		boolean isByProduct=false;
//		
//		double noArt=0,convertedNoArt=0,convertedNetWeight=0;
//		double rate=0;
//		double grossRate=0;
//		String dimension="";
//		boolean isFound=false;
//		double noArtQCTransfer=0,netWeightQCTransfer=0;
//		try 
//		{
//			StockUpdate stockUpdate = new StockUpdate();
//			DistCommon dcommon=new DistCommon();
//			System.out.println("@@@@@@@@@@@@@@@ confirmQcTransfer method called next..............");
//			Calendar currentDate = Calendar.getInstance();
//			SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
//			String sysDateStr = sdf.format(currentDate.getTime());
//			System.out.println("Now the date is :=>  " + sysDateStr);
//			sysDate= Timestamp.valueOf(genericUtility.getValidDateString(sysDateStr, genericUtility.getApplDateFormat(),genericUtility.getDBDateFormat()) + " 00:00:00.0");
//
//			sql = " Select confirmed, qorder_no, tran_date ,site_code,loc_code__trf,quantity," +
//					" lot_sl,lot_no,porcp_no, loc_code,item_code,case when dimension is null then '' else dimension end as dimension" +
//					" , no_art, net_weight " +    //added by cpatil on 10/10/14
//					" from qc_transfer where tran_id = ? ";
//			pstmt = conn.prepareStatement(sql);
//			pstmt.setString(1, tranId);
//			rs = pstmt.executeQuery();
//			if (rs.next())
//			{
//				confirmed = rs.getString("confirmed");
//				qorderNo= rs.getString("qorder_no");
//				tranDate= rs.getTimestamp("tran_date");
//				siteCode= rs.getString("site_code");
//				toLoc= rs.getString("loc_code__trf");
//				qty= rs.getDouble("quantity");
//				lotSl= rs.getString("lot_sl");
//				lotNo= rs.getString("lot_no");
//				porcpNo= rs.getString("porcp_no");
//				fromLoc= rs.getString("loc_code");
//				itemCode= rs.getString("item_code");
//				dimension=rs.getString("dimension");   
//				noArtQCTransfer= rs.getDouble("no_art");     // remove comment by cpatil on 10/10/14
//				netWeightQCTransfer= rs.getDouble("net_weight");    // remove comment by cpatil on 10/10/14
//				
//			}
//			rs.close();
//			rs = null;
//			pstmt.close();
//			pstmt = null;
//
//
//			sql = " Select status,Quantity, item_code, net_weight, no_art from qc_order where qorder_no = ? ";  // added net_weight by cpatil on 05/04/14
//			pstmt = conn.prepareStatement(sql);
//			pstmt.setString(1, qorderNo);
//			rs = pstmt.executeQuery();
//			if (rs.next())
//			{
//				cnt++;
//				status = rs.getString("status");
//				qcQty= rs.getDouble("Quantity");
//				qcOrderItem= rs.getString("item_code");
//				netWeight= rs.getDouble("net_weight");
//				noArt= rs.getDouble("no_art");
//			}
//			rs.close();
//			rs = null;
//			pstmt.close();
//			pstmt = null;
//
//
//			if ( cnt == 0)
//			{
//				errCode = "QCTRFORD";   //'QC order is not found'
//				return itmDBAccessLocal.getErrorString("",errCode,"");
//				//return errCode;//Commented by Manoj dtd 03/06/2014
//			}
//			else if("C".equalsIgnoreCase(status)) 
//			{
//				errCode = "QCTRFORD";    //'QC order is CONFIRMED cannot Transfer Quantity'
//				//return errCode;
//				return itmDBAccessLocal.getErrorString("",errCode,"");
//				//return errCode;//Commented by Manoj dtd 03/06/2014
//			}
//
//			///added by cpatil on 12/12/13 for date validation with approval lead time start
//
//			System.out.println("@@@@ tranDate new validation start----qorderNo["+qorderNo+"]---status["+status+"]-- ");
//
//			if (toLoc != null)
//			{
//				sql = " Select Available from location a, invstat b "
//						+ " Where a.loc_code = ? and a.inv_stat = b.inv_stat ";
//				pstmt = conn.prepareStatement(sql);
//				pstmt.setString(1, toLoc);
//				rs = pstmt.executeQuery();
//				if (rs.next())
//				{
//					available = rs.getString("available");
//				}
//				rs.close();
//				rs = null;
//				pstmt.close();
//				pstmt = null;
//
//				if("Y".equalsIgnoreCase(available))	
//				{
//					sql = " Select aprv_lead_time from siteitem where   site_code =  ? and item_code = ? ";
//					pstmt = conn.prepareStatement(sql);
//					pstmt.setString(1, siteCode);
//					pstmt.setString(2, itemCode);
//					rs = pstmt.executeQuery();
//					if (rs.next()) 
//					{
//						aprvLeadTime = rs.getDouble("aprv_lead_time");
//					}
//					rs.close();
//					rs = null;
//					pstmt.close();
//					pstmt = null;
//
//					sql = " select qorder_date from qc_order where qorder_no = ? ";
//					pstmt = conn.prepareStatement(sql);
//					pstmt.setString(1, qorderNo);
//					rs = pstmt.executeQuery();
//					if (rs.next())
//					{
//						qcOrderDate = rs.getTimestamp("qorder_date");
//					}
//					rs.close();
//					rs = null;
//					pstmt.close();
//					pstmt = null;
//
//					System.out.println("@@@@ site code[" + siteCode+ "]::item code[" + itemCode+ "]::aprvLeadTime[" + aprvLeadTime
//							+ "]::qcOrderDate[" + qcOrderDate + "]:::tranDate["+tranDate+"]");
//
//					Calendar cal = Calendar.getInstance();
//					cal.setTime(qcOrderDate);
//					cal.add(Calendar.DAY_OF_WEEK,(int) Math.round(aprvLeadTime));
//					newQCOrderDate = new Timestamp(cal.getTime().getTime());
//					System.out.println("@@@@ tranDate ["+ tranDate + "]:::: newQCOrderDate ["+ newQCOrderDate + "]:::::");
//
//					if( tranDate.before(newQCOrderDate)) 
//					{
//						errCode = "VTAPPRLEAD";
//						return itmDBAccessLocal.getErrorString("",errCode,"");
//						//return errCode;//Commented by Manoj dtd 03/06/2014
//						
//					}
//
//				}
//			}
//			System.out.println("@@@@ tranDate new validation end---------errCode["+ errCode + "]");
//
//			///added by cpatil on 12/12/13 for date validation with approval lead time end
//
//			if( errCode == null || errCode.trim().length() == 0 ) 
//			{
//				sql = " Select quantity from qc_order where qorder_no = ? ";
//				pstmt = conn.prepareStatement(sql);
//				pstmt.setString(1, qorderNo);
//				rs = pstmt.executeQuery();
//				if (rs.next())
//				{
//					cnt++;
//					qcQty= rs.getDouble("Quantity");
//				}
//				rs.close();
//				rs = null;
//				pstmt.close();
//				pstmt = null;
//				sql="SELECT UNIT FROM ITEM WHERE ITEM_CODE=?";
//				pstmt=conn.prepareStatement(sql);
//				pstmt.setString(1,itemCode);
//				rs=pstmt.executeQuery();
//				if(rs.next())
//				{
//					byprodUnit=rs.getString("UNIT");
//				}
//				rs.close();
//				rs=null;
//				pstmt.close();
//				pstmt=null;
//				
//				sql="SELECT UNIT FROM ITEM WHERE ITEM_CODE=?";
//				pstmt=conn.prepareStatement(sql);
//				pstmt.setString(1,qcOrderItem);
//				rs=pstmt.executeQuery();
//				if(rs.next())
//				{
//					qcOrdItemUnit=rs.getString("UNIT");
//				}
//				rs.close();
//				rs=null;
//				pstmt.close();
//				pstmt=null;
//				
//				//if(!byprodUnit.equalsIgnoreCase(qcOrdItemUnit))
//				if(!qcOrderItem.equalsIgnoreCase(itemCode))
//				{
//					/*  // comment this code as per suggestion of sandip
//					convertedQty=roundVal(dcommon.convQtyFactor(byprodUnit, qcOrdItemUnit, "X", qty, conn),3);  // commented by cpatil on 05/04/14  // remove comment on 08/10/14 by cpatil
//					
//					// modify by cpatil 05/04/14 start  //  // modify by cpatil on 09/04/14
//					
//					System.out.println("@@@@ qc order Qty[qcQty]["+qcQty+"]qc transfer qty[qty]["+qty+"]netWeight["+netWeight+"]noArt["+noArt+"]");
//					
//					// convertedQty =   qty * qcQty  / netWeight ;   // comment line as per suggestion on 08/10/14 by cpatil and remove comment from above line of convertedQty calculation 
//					
//					//convertedNetWeight =  netWeight ;
//					convertedNetWeight =  netWeight * convertedQty / qcQty  ;
//					
//					convertedNoArt =  noArt * qty  / netWeight ;
//					
//					System.out.println("@@@@convertedQty["+convertedQty+"]convertedNetWeight["+convertedNetWeight+"]convertedNoArt["+convertedNoArt+"]");
//					
//					//// cpatil end   // modify by cpatil on 09/04/14  end
//					
//					System.out.println("@@@----qcQty["+qcQty+"]convertedQty["+convertedQty+"]");
//					if(qcQty < convertedQty )
//					{
//						errCode = "QCTRFQTY";    //'QC Transfer quantity more then QC Order Quantity'
//						return itmDBAccessLocal.getErrorString("",errCode,"");
//						//return errCode;//Commented by Manoj dtd 03/06/2014
//					}
//					isByProduct=true;
//					//itemCode=qcOrderItem;
//				 */					
//					// added by cpatil start on 10/10/14 as per suggestion and logic of sandip maurya 
//					if(byprodUnit.equalsIgnoreCase(qcOrdItemUnit))
//					{
//						convertedQty=roundVal(dcommon.convQtyFactor(byprodUnit, qcOrdItemUnit, "X", qty, conn),3);  // commented by cpatil on 05/04/14  // remove comment on 08/10/14 by cpatil
//					}
//					else
//					{
//						convertedQty =   qty * qcQty  / netWeight ;
//					}
//					
//					System.out.println("@@@----qcQty order ["+qcQty+"]qty qc transfer["+qty+"]");
//					System.out.println("@@@@ qc order netWeight["+netWeight+"]qc transfer netWeightQCTransfer["+netWeightQCTransfer+"]");
//					System.out.println("@@@@ qc order noArt["+noArt+"]qc transfer noArtQCTransfer["+noArtQCTransfer+"]");
//					if( netWeight < netWeightQCTransfer )
//					{
//						errCode = "QCTRFNTWET";    //'QC Transfer net weight more then QC Order net weight'
//						return itmDBAccessLocal.getErrorString("",errCode,"");
//					}
//					if( noArt < noArtQCTransfer )
//					{
//						errCode = "QCTRFNOART";    //'QC Transfer no art more then QC Order no art'
//						return itmDBAccessLocal.getErrorString("",errCode,"");
//					}
//					isByProduct=true;
//					
//					convertedNetWeight = netWeightQCTransfer;	
//					convertedNoArt = noArtQCTransfer;
//					System.out.println("@@@@convertedQty["+convertedQty+"]convertedNetWeight["+convertedNetWeight+"]convertedNoArt["+convertedNoArt+"]");
//					// ended by cpatil 10/10/14
//				}
//				else
//				{
//					System.out.println("@@@----qcQty["+qcQty+"]qty["+qty+"]");
//					if(qcQty < qty )
//					{
//						errCode = "QCTRFQTY";    //'QC Transfer quantity more then QC Order Quantity'
//						return itmDBAccessLocal.getErrorString("",errCode,"");
//						//return errCode;//Commented by Manoj dtd 03/06/2014
//					}
//				}
//			}
//
//			if ( errCode == null || errCode.trim().length() == 0 )
//			{		
//				cnt=0;
//				sql = " Select item_ser,mfg_date,exp_date,retest_date,site_code__mfg,potency_perc,pack_code,inv_stat," +
//						" unit,gross_weight,tare_weight, net_weight,pack_instr,dimension,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,no_art,qty_per_art,gross_wt_per_art,tare_wt_per_art,quantity " +
//						" from  Stock" +
//						" Where item_code = ? and site_code = ? and loc_code = ? and lot_no = ? and lot_Sl = ? ";
//				pstmt = conn.prepareStatement(sql);
//				if(isByProduct)
//				{
//					pstmt.setString(1, qcOrderItem);
//				}
//				else
//				{
//					pstmt.setString(1, itemCode);	
//				}
//				
//				pstmt.setString(2, siteCode);
//				pstmt.setString(3, fromLoc);
//				pstmt.setString(4, lotNo);
//				pstmt.setString(5, lotSl);
//				rs = pstmt.executeQuery();
//				if (rs.next())
//				{
//					cnt++;
//					stockUpd.put("item_ser",rs.getString("item_ser"));
//					stockUpd.put("mfg_date", rs.getTimestamp("mfg_date"));
//					stockUpd.put("exp_date", rs.getTimestamp("exp_date"));
//					stockUpd.put("retest_date", rs.getTimestamp("retest_date"));
//					stockUpd.put("site_code__mfg", rs.getString("site_code__mfg"));
//					stockUpd.put("potency_perc", rs.getString("potency_perc"));
//					stockUpd.put("pack_code", rs.getString("pack_code"));
//					stockUpd.put("inv_stat", rs.getString("inv_stat"));
//					stockUpd.put("unit", rs.getString("unit"));
//					stkGrossWt =  rs.getDouble("gross_weight");   
//					stkTareWt =  rs.getDouble("tare_weight");     
//					stkNetWt = rs.getDouble("net_weight");       
//					stockUpd.put("pack_instr", rs.getString("pack_instr"));
//					//stockUpd.put("dimension", rs.getString("dimension")); //Commented by Manoj dtd 30/05/2014 set from qc_transfer
//					stockUpd.put("dimension", dimension);
//					stockUpd.put("rate", rs.getString("rate"));
//					stockUpd.put("rate_oh", rs.getString("rate__oh"));  
//					stockUpd.put("acct_code_inv", rs.getString("acct_code__inv"));  
//					stockUpd.put("cctr_code_inv", rs.getString("cctr_code__inv")); 
//					stockUpd.put("acct_code_oh", rs.getString("acct_code__oh"));    
//					stockUpd.put("cctr_code_oh", rs.getString("cctr_code__oh"));    
//					stockUpd.put("supp_code__mfg", rs.getString("supp_code__mfg"));
//					stockUpd.put("grade", rs.getString("grade"));
//					stockUpd.put("gross_rate", rs.getString("gross_rate"));
//					stockUpd.put("conv__qty_stduom", rs.getString("conv__qty_stduom"));
//					stockUpd.put("unit__alt", rs.getString("unit__alt"));
//					stockUpd.put("batch_no", rs.getString("batch_no"));
//					stockUpd.put("no_art", rs.getString("no_art"));
//					qtyPerArt =  rs.getDouble("qty_per_art");    
//					grossWtPerArt =  rs.getDouble("gross_wt_per_art");  
//					tareWtPerArt = rs.getDouble("tare_wt_per_art");   
//					stockQty = rs.getDouble("quantity");
//					rate=rs.getDouble("rate");
//					grossRate=rs.getDouble("gross_rate");
//				}
//				rs.close();
//				rs = null;
//				pstmt.close();
//				pstmt = null;
//
//				if( cnt == 0 )
//				{	
//					errCode = "QCTRFSTK";   //'Item Not Found in Stock...'
//					System.out.println("@@@@@@@@@@@@  [QCTRFSTK]");
//					return itmDBAccessLocal.getErrorString("",errCode,"");
//					//return errCode;//Commented by Manoj dtd 03/06/2014
//				}//End if
//
//				if( errCode == null || errCode.trim().length() == 0 )
//				{	
//					//Added by manoj dtd 31/05/2014 to set rate for byproduct
//					if(isByProduct)
//					{
//						String considerYield="N";
//						double costRate=0;
//						sql="select case when consider_yield is null then 'N' else consider_yield end consider_yield from byproduct where item_code__byprod=? and item_code=?";
//						pstmt=conn.prepareStatement(sql);
//						pstmt.setString(1,itemCode);
//						pstmt.setString(2,qcOrderItem);
//						rs = pstmt.executeQuery();
//						if(rs.next())
//						{   
//							considerYield=rs.getString(1);
//						}
//						rs.close();
//						rs=null;
//						pstmt.close();
//						pstmt=null;
//						if(considerYield.equalsIgnoreCase("N"))
//						{
//							
//							sql="select cost_rate from siteitem where site_code=? and item_code=?";
//							pstmt=conn.prepareStatement(sql);
//							pstmt.setString(1,siteCode);
//							pstmt.setString(2,itemCode);
//							rs=pstmt.executeQuery();
//							if(rs.next())
//							{
//								costRate=rs.getDouble(1);
//								isFound=true;
//								
//							}
//							rs.close();
//							rs=null;
//							pstmt.close();
//							pstmt=null;
//							
//							if(!isFound)
//							{
//								sql="select cost_rate from item where item_code=?";
//								pstmt=conn.prepareStatement(sql);
//								pstmt.setString(1,itemCode);
//								rs=pstmt.executeQuery();
//								if(rs.next())
//								{
//									costRate=rs.getDouble(1);
//								}
//								rs.close();
//								rs=null;
//								pstmt.close();
//								pstmt=null;
//							}
//							stockUpd.put("rate", costRate);
//							stockUpd.put("gross_rate", costRate);
//
//						}
//						else
//						{
//							double convQty=0;
//							
//							convQty=dcommon.convQtyFactor(qcOrdItemUnit,byprodUnit, "X", convertedQty, conn);
//							
//							System.out.println("convQty-----"+convQty);
//							System.out.println("rate before conversion-----"+rate);
//							rate=roundVal((rate/convertedQty)*convQty,3);
//							System.out.println("rate after conversion-----"+rate);
//							grossRate=roundVal((grossRate/convertedQty)*convQty,3);
//							stockUpd.put("rate", rate);
//							stockUpd.put("gross_rate", grossRate);
//							
//						}
//						
//						
//						
//					}
//					sql = " Select unit From item where item_code = ? ";
//					pstmt =  conn.prepareStatement(sql);
//					if(isByProduct)
//					{
//						pstmt.setString(1,qcOrderItem);
//					}
//					else
//					{
//						pstmt.setString(1,itemCode);
//					}
//					rs = pstmt.executeQuery();
//					if(rs.next())
//					{   
//						stockUpd.put("unit", rs.getString("unit"));
//					}
//					rs.close();
//					rs = null;
//					pstmt.close(); 
//					pstmt = null;
//
//					sql = "	Select inv_stat from location Where loc_code = ? ";
//					pstmt =  conn.prepareStatement(sql);
//					pstmt.setString(1,fromLoc);
//					rs = pstmt.executeQuery();
//					if(rs.next())
//					{   
//						stockUpd.put("inv_stat", rs.getString("inv_stat"));
//					}
//					rs.close();
//					rs = null;
//					pstmt.close(); 
//					pstmt = null;
//
//					stockUpd.put("tran_type", "I");
//					stockUpd.put("tran_ser","QC-TRF" );
//					stockUpd.put("tranid",tranId );   //pending
//					if(isByProduct)
//					{
//						stockUpd.put("item_code", qcOrderItem);
//					}
//					else
//					{
//						stockUpd.put("item_code", itemCode);	
//					}
//					
//					stockUpd.put("site_code", siteCode);
//					stockUpd.put("loc_code", fromLoc );
//					stockUpd.put("lot_no",lotNo );
//					stockUpd.put("lot_sl",lotSl );
//					stockUpd.put("tran_date", tranDate);
//					if(isByProduct)
//					{
//						stockUpd.put("quantity",convertedQty );
//						stockUpd.put("qty_stduom",convertedQty );
//					}
//					else
//					{
//						stockUpd.put("quantity",qty );
//						stockUpd.put("qty_stduom",qty );	
//					}
//					
//
//					if ( qtyPerArt == 0 )		//isnull(lc_qtyperart) or lc_qtyperart = 0 then 
//					{
//						qtyPerArt = 1;
//					}
//
//					stockUpd.put("no_art",Math.ceil(Double.parseDouble(stockUpd.get("qty_stduom").toString())/qtyPerArt));
//					if(stockQty != 0 )
//					{
//						stockUpd.put("net_weight",stkNetWt / stockQty * Double.parseDouble( stockUpd.get("qty_stduom").toString()));
//						stockUpd.put("tare_weight", tareWtPerArt *  Double.parseDouble(stockUpd.get("no_art").toString()));
//						stockUpd.put("gross_weight", ( Double.parseDouble(stockUpd.get("net_weight").toString()) + Double.parseDouble(stockUpd.get("tare_weight").toString())) );
//					}//end if
//					// ls_errcode = nvo_dis.gbf_update_stock(lstr_stkupd)
//					errCode	= stockUpdate.updateStock(stockUpd,xtraParams, conn);
//					if(errCode !=null && errCode.trim().length() >0)  //if(retString !=null && retString.trim().length() >0)
//					{
//						return errCode;
//					}
//
//				}//end if
//
//				if( errCode == null || errCode.trim().length() == 0 )
//				{	
//					sql = " Select inv_stat from location Where loc_code = ? ";
//					pstmt =  conn.prepareStatement(sql);
//					pstmt.setString(1,toLoc);
//					rs = pstmt.executeQuery();
//					if(rs.next())
//					{   
//						stockUpd.put("inv_stat", rs.getString("inv_stat"));
//					}
//					rs.close();
//					rs = null;
//					pstmt.close(); 
//					pstmt = null;
//
//
//					if(isByProduct)
//					{
//						stockUpd.put("quantity",qty );
//						stockUpd.put("qty_stduom",qty );	
//						stockUpd.put("item_code",itemCode);
//					}
//					stockUpd.put("tran_type", "R");
//					stockUpd.put("loc_code", toLoc );
//
//					if ( qtyPerArt == 0 )
//					{
//						qtyPerArt = 1;
//					}		
//
//					stockUpd.put("no_art", Math.ceil( Double.parseDouble(stockUpd.get("qty_stduom").toString()) / qtyPerArt));		
//
//					if ( stockQty != 0 )
//					{
//						stockUpd.put("net_weight",stkNetWt / stockQty *  Double.parseDouble(stockUpd.get("qty_stduom").toString()));
//						stockUpd.put("tare_weight", tareWtPerArt *  Double.parseDouble(stockUpd.get("no_art").toString()));
//						stockUpd.put("gross_weight", ( Double.parseDouble(stockUpd.get("net_weight").toString()) +  Double.parseDouble(stockUpd.get("tare_weight").toString())));
//					}//end if
//					//ls_errcode = nvo_dis.gbf_update_stock(lstr_stkupd)
//					errCode	= stockUpdate.updateStock(stockUpd,xtraParams, conn);
//
//					if(errCode !=null && errCode.trim().length() >0)  //if(retString !=null && retString.trim().length() >0)
//					{
//						return errCode;
//					}
//				} //End if
//			} //End if
//
//
//			if (errCode == null || errCode.trim().length() == 0 )
//			{																			//if  gs_run_mode = 'I' then
//				
//				
//				
//				
//				if(isByProduct)
//				{
//					if ( qcQty > convertedQty )
//					{	
//						sql = " Update qc_order set quantity = ( quantity - ? ) " +
//							  " , net_weight = ( net_weight - ? ) , no_art = ( no_art - ? ) " +    // modify by cpatil on 09/04/14
//							  "  where qorder_no = ? ";
//						pstmt = conn.prepareStatement(sql);
//						pstmt.setDouble(1, convertedQty );
//						pstmt.setDouble(2, convertedNetWeight );
//						pstmt.setDouble(3, convertedNoArt );
//						pstmt.setString(4, qorderNo);
//						updCnt = pstmt.executeUpdate();
//						pstmt.close();
//						pstmt = null;
//					}
//					else
//					{
//						sql = " Update qc_order set quantity = (quantity - ? ) " +
//						 //   " , status = 'C' " +                                                 //  modify by cpatil on 20/12/14 -- comment condition
//							  " , net_weight = ( net_weight - ? ) , no_art = ( no_art - ? ) " +    // modify by cpatil on 09/04/14
//							  " where qorder_no = ? " ;
//						pstmt = conn.prepareStatement(sql);
//						pstmt.setDouble(1, convertedQty );
//						pstmt.setDouble(2, convertedNetWeight );
//						pstmt.setDouble(3, convertedNoArt );
//						pstmt.setString(4, qorderNo);
//						updCnt = pstmt.executeUpdate();
//						pstmt.close();
//						pstmt = null;
//					} //end if
//					
//				}
//				else
//				{	
//					System.out.println("@@@ noArt["+noArt+"]qty["+qty+"]qcQty["+qcQty+"]netWeight["+netWeight+"]");  // modify by cpatil on 09/04/14 
//					
//					convertedNoArt = noArt * qty / qcQty ;
//					convertedNetWeight = netWeight * qty / qcQty ;
//					
//					System.out.println("@@@ convertedNoArt["+convertedNoArt+"]convertedNetWeight["+convertedNetWeight+"]");
//					
//					if ( qcQty > qty )
//					{	
//						sql = " Update qc_order set quantity = (quantity - ? ) " +
//						      " , net_weight = ( net_weight - ? ) , no_art = ( no_art - ? ) " +    // modify by cpatil on 09/04/14
//							  " where qorder_no = ? ";
//						pstmt = conn.prepareStatement(sql);
//						pstmt.setDouble(1, qty);
//						pstmt.setDouble(2, convertedNetWeight);
//						pstmt.setDouble(3, convertedNoArt);
//						pstmt.setString(4, qorderNo);
//						updCnt = pstmt.executeUpdate();
//						pstmt.close();
//						pstmt = null;
//					}
//					else
//					{
//						sql = " Update qc_order set quantity = (quantity - ? ) " +   // remove  update status = 'C' on 05/12/14 
//							  " , net_weight = ( net_weight - ? ) , no_art = ( no_art - ? ) " +    // modify by cpatil on 09/04/14
//							  " where qorder_no = ? " ;
//						pstmt = conn.prepareStatement(sql);
//						pstmt.setDouble(1, qty);
//						pstmt.setDouble(2, convertedNetWeight);
//						pstmt.setDouble(3, convertedNoArt);
//						pstmt.setString(4, qorderNo);
//						updCnt = pstmt.executeUpdate();
//						pstmt.close();
//						pstmt = null;
//					} //end if
//				}
//
//				if (updCnt == 0)   //         get_sqlcode() <> 0 then
//				{	
//					errCode = "QCTRFUPDT";   //'Unable to confirm QC Transfer ' + sqlca.sqlerrtext
//					return itmDBAccessLocal.getErrorString("",errCode,"");
//					//return errCode;//Commented by Manoj dtd 03/06/2014
//				} //End if
//
//				// Update to qc_order_lot table if it QC is split into to different lots
//				if( errCode == null || errCode.trim().length() == 0 )
//				{
//					sql = " update qc_order_lots set quantity = (quantity - ? ) " +
//							" where qc_order = ? and lot_no = ? and lot_sl = ? ";
//					pstmt = conn.prepareStatement(sql);
//					//if(convertedQty>0)
//					if(isByProduct)
//					{
//						pstmt.setDouble(1, convertedQty);
//					}
//					else
//					{
//						pstmt.setDouble(1, qty);
//					}
//					pstmt.setString(2, qorderNo);
//					pstmt.setString(3, lotNo);
//					pstmt.setString(4, lotSl);
//					updCnt = pstmt.executeUpdate();
//					pstmt.close();
//					pstmt = null;
//
//					//	if( updCnt == 0 ) 
//					//	{
//					//		errCode = "QCTRFUPDT";         //'Unable to confirm QC Transfer ' + sqlca.sqlerrtext
//					//		return errCode;
//					//	}//End if
//				} //end if
//
//				// update po receipt as accept on external approval
//				if(( errCode == null || errCode.trim().length() == 0 )  && ( porcpNo != null && porcpNo.trim().length() == 0  ))
//				{	
//					sql = " select count(1) from porcp where tran_id = ? ";
//					pstmt =  conn.prepareStatement(sql);
//					pstmt.setString(1,porcpNo);
//					rs = pstmt.executeQuery();
//					if(rs.next())
//					{   
//						cnt = rs.getInt(1);
//					}
//					rs.close();
//					rs = null;
//					pstmt.close(); 
//					pstmt = null;
//
//					if( cnt==1 )   // ll_count = 1 then
//					{	
//						sql = " update porcp set ACCEPT_CRITERIA = 'E' 	where tran_id = ? ";
//						pstmt = conn.prepareStatement(sql);
//						pstmt.setString(1, qorderNo);
//						updCnt = pstmt.executeUpdate();
//						pstmt.close();
//						pstmt = null;
//
//						if( updCnt == 0 )  // get_sqlcode() <> 0 then
//						{
//							System.out.println("@@@@@@@@@@@@ no record");
//						}//end if
//					} //end if
//				} //end if
//
//				if( errCode == null || errCode.trim().length() == 0 )
//				{	
//					sql = " Update qc_transfer Set confirmed = 'Y', conf_date = ? Where tran_id = ?  ";
//					pstmt = conn.prepareStatement(sql);
//					pstmt.setTimestamp(1, sysDate );
//					pstmt.setString(2, tranId);
//					updCnt = pstmt.executeUpdate();
//					pstmt.close();
//					pstmt = null;
//
//					/////
//					if( updCnt == 0 )    //get_sqlcode() <> 0 then
//					{
//						errCode = "QCTRFUPDT"; //'Unable to confirm QC Transfer ' + sqlca.sqlerrtext
//						return itmDBAccessLocal.getErrorString("",errCode,"");
//						//return errCode;//Commented by Manoj dtd 03/06/2014
//					}//End if
//					// ADDED BY RITESH ON 23/SEP/2014 START
//					else
//					{
//						double holdQty = 0d;
//						sql = " SELECT HOLD_QTY FROM STOCK  "
//								+ " WHERE ITEM_CODE = ?  "
//								+ " AND SITE_CODE = ? "
//								+ " AND LOC_CODE = ? "
//								+ " AND LOT_NO = ? "
//								+ " AND LOT_SL = ? ";
//						pstmt =  conn.prepareStatement(sql);
//						pstmt.setString(1,itemCode);
//						pstmt.setString(2,siteCode);
//						pstmt.setString(3,fromLoc);
//						pstmt.setString(4,lotNo);
//						pstmt.setString(5,lotSl);
//						rs = pstmt.executeQuery();
//						if(rs.next())
//						{
//							holdQty = rs.getDouble("HOLD_QTY");
//						}
//						pstmt.close();
//						pstmt = null;
//						rs.close();
//						rs = null;
//						if(holdQty > 0)
//						{
//							sql = "UPDATE STOCK SET HOLD_QTY = HOLD_QTY - ?"
//									+ " WHERE ITEM_CODE = ?  "
//									+ " AND SITE_CODE = ? "
//									+ " AND LOC_CODE = ? "
//									+ " AND LOT_NO = ? "
//									+ " AND LOT_SL = ? ";
//							pstmt = conn.prepareStatement(sql);
//							pstmt.setDouble(1, qty );
//							pstmt.setString(2,itemCode);
//							pstmt.setString(3,siteCode);
//							pstmt.setString(4,fromLoc);
//							pstmt.setString(5,lotNo);
//							pstmt.setString(6,lotSl);
//							updCnt = pstmt.executeUpdate();
//							pstmt.close();
//							pstmt = null;
//							System.out.println("hiold_qty updated  in from location "+updCnt);
//							sql = "UPDATE STOCK SET HOLD_QTY =  ?"
//									+ " WHERE ITEM_CODE = ?  "
//									+ " AND SITE_CODE = ? "
//									+ " AND LOC_CODE = ? "
//									+ " AND LOT_NO = ? "
//									+ " AND LOT_SL = ? ";
//							pstmt = conn.prepareStatement(sql);
//							pstmt.setDouble(1, qty );
//							pstmt.setString(2,itemCode);
//							pstmt.setString(3,siteCode);
//							pstmt.setString(4,toLoc);
//							pstmt.setString(5,lotNo);
//							pstmt.setString(6,lotSl);
//							updCnt = pstmt.executeUpdate();
//							pstmt.close();
//							pstmt = null;
//							System.out.println("hiold_qty updated  in to location "+updCnt);
//						}
//					}
//					// ADDED BY RITESH ON 23/SEP/2014 END
//				} //end if
//
//
//				sql = "select edi_option from transetup where tran_window = 'w_qc_transfer' ";
//				pstmtSql = conn.prepareStatement(sql);
//				rs = pstmtSql.executeQuery();
//				if (rs.next())
//				{
//					ediOption = rs.getString("EDI_OPTION");
//				}
//				rs.close();
//				rs = null;
//				pstmtSql.close();
//				pstmtSql = null;
//				System.out.println("@@@@@@@@@@@@@@@ ediOption  called next..............");
//				if ("2".equals(ediOption)) 
//				{
//					CreateRCPXML createRCPXML = new CreateRCPXML("w_qc_transfer","tran_id");
//					dataStr = createRCPXML.getTranXML(tranId, conn);
//					System.out.println("dataStr =[ " + dataStr + "]");
//					Document ediDataDom = genericUtility.parseString(dataStr);
//
//					E12CreateBatchLoadEjb e12CreateBatchLoad = new E12CreateBatchLoadEjb();
//					retString = e12CreateBatchLoad.createBatchLoad(ediDataDom,"w_qc_transfer", "2", xtraParams, conn);
//					createRCPXML = null;
//					e12CreateBatchLoad = null;
//
//					if (retString != null && "SUCCESS".equalsIgnoreCase(retString)) 
//					{
//						System.out.println("retString from batchload = [" + retString + "]");
//					}
//				}
//
//				else 
//				{
//					CreateRCPXML createRCPXML = new CreateRCPXML("w_qc_transfer","tran_id");
//					dataStr = createRCPXML.getTranXML(tranId, conn);
//					System.out.println("dataStr =[ " + dataStr + "]");
//					Document ediDataDom = genericUtility.parseString(dataStr);
//
//					E12CreateBatchLoadEjb e12CreateBatchLoad = new E12CreateBatchLoadEjb();
//					retString = e12CreateBatchLoad.createBatchLoad(ediDataDom,"w_qc_transfer", ediOption, xtraParams, conn);
//					createRCPXML = null;
//					e12CreateBatchLoad = null;
//
//					if (retString != null && "SUCCESS".equalsIgnoreCase(retString)) 
//					{
//						System.out.println("retString from batchload = ["+ retString + "]");
//					}
//				}
//				System.out.println("@@@@@@@@@@@@@@@ ediOption  called end..............");
//
//			} //End if
//
//		}  // end try
//		catch (Exception e)
//		{
//			e.printStackTrace();
//			System.out.println("Exception ::" + e.getMessage());
//			throw new ITMException(e);
//		}
//
//		return errCode;
//			}
	/*
	private String checkNull(String string)
	{
		if (string == null) 
		{
			string = "";
		}
		return string;
	}
	 */
	private double roundVal(double round,int scale)
	{
		return Math.round(round*Math.pow(10, scale))/Math.pow(10, scale);
	}
}