package ibase.webitm.ejb.mfg.adv;
import ibase.system.config.ConnDriver;
import ibase.utility.CommonConstants;
import ibase.webitm.ejb.ActionHandlerEJB;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.ejb.dis.DistCommon;
import ibase.webitm.ejb.dis.StockUpdate;
import ibase.webitm.ejb.fin.FinCommon;
import ibase.webitm.ejb.fin.FinCommonInvAcct;
import ibase.webitm.ejb.fin.InvAcct;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;
import ibase.webitm.utility.TransIDGenerator;
import ibase.webitm.ejb.fin.FinCommon;	
import ibase.webitm.ejb.mfg.MfgCommon;	
import ibase.webitm.ejb.dis.DistCommon;	
import ibase.webitm.ejb.dis.InvAllocTraceBean;	
import ibase.webitm.ejb.mfg.LotWiseScanBean;	
import ibase.webitm.ejb.mfg.WOBean;	
import ibase.webitm.ejb.mfg.GenWOIssue;	
import ibase.webitm.ejb.mfg.adv.WorkorderIssConf;	
import ibase.webitm.ejb.sys.UtilMethods;

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.text.DecimalFormat;
import java.text.NumberFormat;
import java.util.Calendar;
import java.util.HashMap;
import java.util.*; 
import java.sql.*;

import javax.ejb.Stateless;

@Stateless 
public class WorkorderIssConf extends ActionHandlerEJB implements WorkorderIssConfLocal,WorkorderIssConfRemote
{
	
	public String confirm(String tranID,String xtraParams, String forcedFlag) throws RemoteException,ITMException
	{
		String retString = "";		
		Connection conn = null;	
		try
		{
			retString = confirm( tranID, xtraParams, forcedFlag,conn, "I");		
				
		}
		catch(Exception exception)
		{
			System.out.println("Exception in [RcpBackflushConfirm] confirm " + exception.getMessage());
		}
		return retString;
	}
	
	public String confirm( String tranID, String xtraParams,String forcedFlag,Connection conn, String issType ) throws RemoteException,ITMException
	{		
		System.out.println("************woiss_confirm ***************");
		ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
		PreparedStatement pstmt = null,pstmt1 = null,pstmtDet =null,pstmtSL=null;
		ResultSet rs = null,rs1 = null,rsDet =null,rsSL=null;
		String sql = "",errCode ="",errString ="";
		String ledgpostconf ="";
		Timestamp  tranDate =null,effDate =null;
		String siteCode="",deptCode="",workOrder="",tranType="",tranIdReq="";
		int operation =0,upd=0,stkNoArt=0, LINEnO =1;
		String acctCodeWp="",cctrCodeWp="",orderType="",stkopt =null;
		String itemCode="",locCode="",lotNo="",lotSl="",unit="",expLev="",qcReqd="",reasCode="",calcPotency="";
		double quantity=0,shortQty=0,grossWeight=0,tareWeight=0,netWeight=0,potencyAdj=0;
		int lineNo=0,noArt=0;
		String itemSer="",itemunit="",trackShelfLife="";
		double stkQty=0,stkRate=0,grossRate=0,qtystduom=0;
		String issCriteria ="";
		String stkGrade="",suppCodeMfg="",acct_code__inv="",cctr_code__inv="",acct_code__oh="",cctr_code__oh="";
		HashMap stkUpdMap =null;
		String userId="",siteCodeVal="",useInvstatus="";
		String autoReqc="",itemUnit="",stkOpt="",tranSer="";
		String itemCodeRef="",detTable = "workorder_issdet", hdrTable ="workorder_iss";;
		double qtyPerArt = 0;
		ArrayList qtyStd = new ArrayList();
		GenericUtility genericUtility = null;
		boolean flag =false;
		boolean localConnection = false;
		try
		{
			if ( conn == null )
			{
				ConnDriver connDriver = null;
				connDriver = new ConnDriver();
				conn = connDriver.getConnectDB("DriverITM");
				conn.setAutoCommit(false);
				connDriver = null;
				localConnection = true;
			}
			errString = preConfirmChk(tranID, issType, xtraParams, conn);
			System.out.println("After preConfirmChk errString ["+ errString+ "] length [" + errString.trim().length() +"]");
			if (errString != null && errString.trim().length() > 0)
			{
				return errString;
			}
			System.out.println("After preConfirmChk processing");
			genericUtility = GenericUtility.getInstance();
			stkUpdMap = new HashMap();
			DistCommon distCommon = new DistCommon();
			MfgCommon mfgCommon = new MfgCommon();
			userId = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginCode");
			sql = "select (case when LEDG_POST_CONF is null then 'N' else LEDG_POST_CONF end ) "
			+" from transetup where lower(tran_window) = 'w_worder_iss'  ";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
					ledgpostconf = rs.getString(1);
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			if(ledgpostconf == null )
			{
				ledgpostconf ="N";
			}
			if("R".equalsIgnoreCase(issType))
			{
				detTable ="WORDER_ISSDET_RND";
				hdrTable ="WORDER_ISS_RND";
			}
			else
			{
				detTable ="workorder_issdet";
				hdrTable ="workorder_iss";
			}
			sql = "select site_code 		, tran_date 		, dept_code 	, work_order, "
					+" tran_type 		, operation 		, eff_date  	, tran_id__req "
					+" from 	 "+hdrTable+" where  tran_id ='"+tranID+"' ";
			pstmt1 = conn.prepareStatement(sql);
			rs1 = pstmt1.executeQuery();
			if(rs1.next())
			{
				siteCode	= rs1.getString("site_code");
				tranDate	= rs1.getTimestamp("tran_date");
				deptCode	= rs1.getString("dept_code");
				workOrder	= rs1.getString("work_order");
				tranType	= rs1.getString("tran_type");
				operation	= rs1.getInt("operation");
				effDate		= rs1.getTimestamp("eff_date");
				tranIdReq	= rs1.getString("tran_id__req");
			}
			rs1.close();
			rs1 = null;
			pstmt1.close();
			pstmt1 = null;
			if("Y".equalsIgnoreCase(ledgpostconf))
			{
				SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd");
				tranDate =	java.sql.Timestamp.valueOf(sdf1.format(new Timestamp(System.currentTimeMillis())) + " 00:00:00.0");
				sql =" update "+hdrTable+" set tran_date = ? "
					+" where tran_id = ? ";
				pstmt = conn.prepareStatement(sql); //conf_date
				pstmt.setTimestamp(1,tranDate);
				pstmt.setString(2,tranID);
				upd = pstmt.executeUpdate();
				pstmt.close();
				pstmt = null ;
			}
			sql =" select	acct_code__wp	, cctr_code__wp,	order_type "
				 +" from	  	workorder  where		work_order ='"+workOrder+"' ";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				acctCodeWp	= rs.getString("acct_code__wp");
				cctrCodeWp	= rs.getString("cctr_code__wp");
				orderType	= rs.getString("order_type");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			if("R".equalsIgnoreCase(tranType))
			{
				 tranSer ="W-IRTN";
			}
			else
			{
				 tranSer ="W-ISS";
			}
			sql = "select 	item_code	,	loc_code		,	lot_no		,	lot_sl		,"
				  +"  quantity		,	unit			,	line_no		,	exp_lev		,	 "
				  +"  qc_reqd		,	nvl(potency_adj,0) as potency_adj	, 	reas_code	, 	gross_weight,	 "
				  +"  tare_weight	,	net_weight	, 	no_art		, 	short_qty	,	 "
				  +"  case when calc_potency is null then 'N' else calc_potency end	 calc_potency "
				  +" from 	 "+detTable+" where  tran_id ='"+tranID+"' ";
			pstmtDet = conn.prepareStatement(sql);
			rsDet = pstmtDet.executeQuery();
			while (rsDet.next())
			{
				itemCode		= rsDet.getString("item_code");
				locCode			= rsDet.getString("loc_code");
				lotNo			= rsDet.getString("lot_no");
				lotSl			= rsDet.getString("lot_sl");
				quantity		= rsDet.getDouble("quantity");
				unit			= rsDet.getString("unit");
				lineNo			= rsDet.getInt("line_no");
				expLev			= rsDet.getString("exp_lev");
				qcReqd			= rsDet.getString("qc_reqd");
				potencyAdj		= rsDet.getDouble("potency_adj");
				reasCode		= rsDet.getString("reas_code");
				grossWeight		= rsDet.getDouble("gross_weight");
				tareWeight		= rsDet.getDouble("tare_weight");
				netWeight		= rsDet.getDouble("net_weight");
				noArt			= rsDet.getInt("no_art");
				shortQty		= rsDet.getDouble("short_qty");
				calcPotency		= rsDet.getString("calc_potency");
				stkOpt = null;
				if("T".equalsIgnoreCase(orderType))
				{
					String strValue = mfgCommon.getEnvMfg("999999","RND_NO_STK_SITE",conn);
					if(! strValue.equals("NULLFOUND"))
					{
						strValue = strValue.trim();
						StringTokenizer st = new StringTokenizer(strValue,",");
						while (st.hasMoreTokens())
						{
							siteCodeVal = st.nextToken();
							if(siteCodeVal.equalsIgnoreCase(siteCode))
							{
								stkOpt = "0" ;
								break;
							}
						}
					}
				}
				if(stkOpt == null)
				{
					sql  ="select case when stk_opt is null then 'N' else stk_opt end "
					   +" from siteitem where item_code ='"+itemCode+"' "
					   +" and    site_code ='"+siteCode+"' ";
					pstmt = conn.prepareStatement(sql);
					rs = pstmt.executeQuery();
					if (rs.next())
					{
						stkOpt = rs.getString(1);
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null ;
				}
				if(stkOpt == null || "N".equalsIgnoreCase(stkOpt) )
				{
					sql  ="select case when stk_opt is null then '0' else stk_opt end "
					   +" from item where item_code ='"+itemCode+"' " ;
					pstmt = conn.prepareStatement(sql);
					rs = pstmt.executeQuery();
					if (rs.next())
					{
						stkOpt = rs.getString(1);
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null ;
				}
				if(! "0".equalsIgnoreCase(stkOpt))
				{
					///for qc_reqd to be first checked from siteitem &if not
					//then from item
					sql  ="select case when qc_reqd is null then 'N' else qc_reqd end "
							 +" from	siteitem where	item_code ='"+itemCode+"' "
							 +" and	site_code ='"+siteCode+"' ";
					pstmt = conn.prepareStatement(sql);
					rs = pstmt.executeQuery();
					if (rs.next())
					{
								qcReqd = rs.getString(1);
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null ;
					if(qcReqd ==null && qcReqd.trim().length() ==0)
					{
						sql  ="select case when qc_reqd is null then 'N' else qc_reqd end "
								+" from	item where	item_code ='"+itemCode+"' " ;
						pstmt = conn.prepareStatement(sql);
						rs = pstmt.executeQuery();
						if (rs.next())
						{
							qcReqd = rs.getString(1);
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null ;
					}
					sql  ="select case when  auto_reqc is null then 'Y' else auto_reqc end"
						+" from siteitem where	site_code ='"+siteCode+"' "
						+" and item_code ='"+itemCode+"' ";
					pstmt = conn.prepareStatement(sql);
					rs = pstmt.executeQuery();
					if (rs.next())
					{
						autoReqc = rs.getString(1);
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null ;
					if(autoReqc ==null && autoReqc.trim().length() ==0)
					{
						sql  ="select case when  auto_reqc is null then 'Y' else auto_reqc end "
								+" from	item where	item_code ='"+itemCode+"' " ;
						pstmt = conn.prepareStatement(sql);
						rs = pstmt.executeQuery();
						if (rs.next())
						{
							autoReqc = rs.getString(1);
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null ;
					}
					sql  =" select item_ser, unit, nvl(track_shelf_life,'N') "
								+" from	item where	item_code ='"+itemCode+"' " ;
					pstmt = conn.prepareStatement(sql);
					rs = pstmt.executeQuery();
					if (rs.next())
					{
						itemSer = rs.getString(1);
						itemUnit = rs.getString(2);
						trackShelfLife = rs.getString(3);
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null ;
					double stkquantity =0,stkalloc_qty=0;
					stkUpdMap.put("item_code",itemCode);
					stkUpdMap.put("item_ser",itemSer);
					stkUpdMap.put("site_code",siteCode);
					stkUpdMap.put("loc_code",locCode);
					stkUpdMap.put("lot_no",lotNo);
					stkUpdMap.put("lot_sl",lotSl);
					stkUpdMap.put("no_art",Integer.toString(Math.abs(noArt)) ); // 04/03/14 manoharan to pass no_art so that it will be reduced 
					
					
					
					if(! "R".equalsIgnoreCase(tranType))
					{
						if("Y".equalsIgnoreCase(calcPotency))
						{
							//genWOIssue.writeLog(this.curTranId, "In woiss_confirm() update stock  potencyAdj + quantity [" + potencyAdj + quantity + "]"); // 23/01/11 manoharan write the log
							stkUpdMap.put("quantity",Double.toString(potencyAdj + quantity));
						}
						else
						{
							//genWOIssue.writeLog(this.curTranId, "In woiss_confirm() update stock  quantity [" + quantity + "]"); // 23/01/11 manoharan write the log
							stkUpdMap.put("quantity",Double.toString(quantity));
						}
					}
					else
					{
						if("Y".equalsIgnoreCase(calcPotency))
						{
							//genWOIssue.writeLog(this.curTranId, "In woiss_confirm() update stock  potencyAdj + quantity [" + potencyAdj + quantity + "]"); // 23/01/11 manoharan write the log
							stkUpdMap.put("quantity",Double.toString(Math.abs(quantity + potencyAdj)));
						}
						else
						{
							//genWOIssue.writeLog(this.curTranId, "In woiss_confirm() update stock  quantity [" + quantity + "]"); // 23/01/11 manoharan write the log
							stkUpdMap.put("quantity",Double.toString(Math.abs(quantity)) );
						}
					}
					// 11/07/13 manoharan to avoid null pointer exception
					//genWOIssue.writeLog(this.curTranId, "In woiss_confirm() workorder_issdet unit was null");
					if (unit == null || unit.trim().length() == 0)
					{
						unit = itemUnit;
					}
					// 11/07/13 manoharan to avoid null pointer exception
					stkUpdMap.put("unit",unit);
					if(! unit.equalsIgnoreCase(itemUnit))
					{
						if("Y".equalsIgnoreCase(calcPotency))
						{
							qtyStd   = distCommon.getConvQuantityFact(unit,itemUnit,itemCode,potencyAdj + quantity,0.0,conn);
							qtystduom = Double.parseDouble(qtyStd.get(1).toString());
							// qtystduom = gf_conv_qty_fact(ls_unit, ls_itemunit, ls_item, ld_qty + ld_potency_adj_qty, ld_convfact)
						}
						else
						{
							 qtyStd = distCommon.getConvQuantityFact(unit,itemUnit,itemCode,quantity,0.0,conn);
							 qtystduom = Double.parseDouble(qtyStd.get(1).toString());
							//qtystduom = gf_conv_qty_fact(ls_unit, ls_itemunit, ls_item, ld_qty , ld_convfact)
						}
						if(qtystduom < 0 && !"R".equalsIgnoreCase(tranType))
						{
							errCode = "VTUNIT3";
						}
					}
					else
					{
						if("Y".equalsIgnoreCase(calcPotency))
						{
							qtystduom = potencyAdj + quantity;
							qtystduom =getReqDecimal(qtystduom,3);
						}
						else
						{
							qtystduom = quantity;
							qtystduom =getReqDecimal(qtystduom,3);
						}
					}
					if(! "R".equalsIgnoreCase(tranType))
					{
						stkUpdMap.put("qty_stduom",Double.toString(qtystduom));
					}
					else
					{
						stkUpdMap.put("qty_stduom",Double.toString(Math.abs(qtystduom)));
					}
					//genWOIssue.writeLog(this.curTranId, "In woiss_confirm() update stock  qty_stduom [" + qtystduom + "]"); // 23/01/11 manoharan write the log
					//stkUpdMap.put("qty_stduom",Double.toString(stkquantity));
					stkUpdMap.put("tran_type","ID");
					stkUpdMap.put("tran_date",tranDate);
					stkUpdMap.put("tran_id",tranID);
					stkUpdMap.put("tran_ser",tranSer);
					stkUpdMap.put("sorder_no",workOrder);
					//stkUpdMap.put("line_no",Integer.toString(lineNo));
					System.out.println("manoharan 30/11/12 2  substring 3  [" + ("   " +lineNo).substring(("   " +lineNo).length()-3) + "] lineNoIss [" + lineNo + "]");
					stkUpdMap.put("line_no",("   " +lineNo).substring(("   " +lineNo).length()-3));
					//	stkUpdMap.put("lineno",workOrder);left('   ',3 -len(trim(String(ll_lineno)))) + trim(String(ll_lineno)
					stkUpdMap.put("site_code__mfg",siteCode);
					stkUpdMap.put("sundry_code",deptCode);
					stkUpdMap.put("sundry_type","D");
					if( "I".equalsIgnoreCase(tranType) || "O".equalsIgnoreCase(tranType) || "D".equalsIgnoreCase(tranType) || "F".equalsIgnoreCase(tranType) )
					{
						sql   =" select item_code, item_code__ref "
							+" from workorder_bill  where work_order  ='"+workOrder+"' "
							+" and operation ="+operation
							+" and exp_lev ='"+expLev+"'";
						pstmt = conn.prepareStatement(sql);
						rs = pstmt.executeQuery();
						if (rs.next())
						{
							itemCode 	= rs.getString(1);
							itemCodeRef = rs.getString(2);
							sql =" select use_invstatus from bomdet where bom_code = "
								+" (select bom_code from workorder where work_order = '"+workOrder+"' ) "
								+" and item_code ='"+itemCode+"' "
								+" and item_ref ='"+itemCodeRef+"'";
							pstmt = conn.prepareStatement(sql);
							rs = pstmt.executeQuery();
							if (rs.next())
							{
								useInvstatus = rs.getString(1);
							}
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null ;
						if(useInvstatus == null)
						{
							useInvstatus = "U";
						}
						sql =" Select a.quantity,rate, a.grade, a.supp_code__mfg, a.acct_code__inv, a.cctr_code__inv, "
							+" a.acct_code__oh,a.cctr_code__oh,a.gross_rate ,a.qty_per_art  "
							+" from stock a, location b, invstat c where  "
							+" a.loc_code = b.loc_code and b.inv_stat = c.inv_stat "
							+" and a.item_code ='"+itemCode+"' "
							+" and a.site_code ='"+siteCode+"'"
							+" and a.lot_no ='"+lotNo+"' "
							+" and a.lot_sl ='"+lotSl+"' "
							+" and a.loc_code ='"+locCode+"' "
							+" and c.stat_type <> 'S' ";

						if("Y".equalsIgnoreCase(trackShelfLife) || "U".equalsIgnoreCase(useInvstatus))
						{
							sql = sql+" and (( a.exp_date >  ? or  a.exp_date is null )) ";
						}
						if("U".equalsIgnoreCase(useInvstatus))
						{
							sql = sql+" and c.available = 'Y' ";
						}
						if( "Y".equalsIgnoreCase(qcReqd) || "Y".equalsIgnoreCase(autoReqc) )
						{
							sql = sql+" and ( a.retest_date > ? ) ";
						}
						pstmt = conn.prepareStatement(sql);
						if("Y".equalsIgnoreCase(trackShelfLife) || "U".equalsIgnoreCase(useInvstatus))
						{
							pstmt.setTimestamp(1,effDate);
							flag =true;
						}
						if( "Y".equalsIgnoreCase(qcReqd) || "Y".equalsIgnoreCase(autoReqc) )
						{
							if(flag)
							{
								pstmt.setTimestamp(2,effDate);
							}
							else
							{
								pstmt.setTimestamp(1,effDate);
							}
						}
						rs = pstmt.executeQuery();
						if (rs.next())
						{
							stkQty			= rs.getDouble(1);
							stkRate			= rs.getDouble(2);
							stkGrade		= rs.getString(3);
							suppCodeMfg		= rs.getString(4);
							acct_code__inv	= rs.getString(5);
							cctr_code__inv	= rs.getString(6);
							acct_code__oh	= rs.getString(7);
							cctr_code__oh	= rs.getString(8);
							grossRate		= rs.getDouble(9);
							qtyPerArt		= rs.getDouble(10);
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null ;
						// if any of the weight or no_art is zero get from stock as per old logic
						sql =" select a.no_art, a.gross_weight,a.tare_weight,a.net_weight "
						  +" from stock a  where a.item_code ='"+itemCode+"' "
						  +" and a.site_code ='"+siteCode+"' "
						  +" and a.lot_no ='"+lotNo+"' "
						  +" and a.lot_sl ='"+lotSl+"' "
						  +" and a.loc_code ='"+locCode+"' ";
						pstmt = conn.prepareStatement(sql);
						rs = pstmt.executeQuery();
						if (rs.next())
						{
							stkNoArt			= rs.getInt(1);
							grossWeight			= rs.getDouble(2);
							tareWeight			= rs.getDouble(3);
							netWeight			= rs.getDouble(4);
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null ;
						if("R".equalsIgnoreCase(tranType))
						{
						  stkUpdMap.put("acct_code__dr",acct_code__inv);
						  stkUpdMap.put("cctr_code__dr",cctr_code__inv);
						  stkUpdMap.put("acct_code__cr",acctCodeWp);
						  stkUpdMap.put("cctr_code__cr",cctrCodeWp);
						}
						else
						{
						  stkUpdMap.put("acct_code__dr",acctCodeWp);
						  stkUpdMap.put("cctr_code__dr",cctrCodeWp);
						  stkUpdMap.put("acct_code__cr",acct_code__inv);
						  stkUpdMap.put("cctr_code__cr",cctr_code__inv);
						}
						sql =" select iss_criteria  from item  where  item_code ='"+itemCode+"'";
						pstmt = conn.prepareStatement(sql);
						rs = pstmt.executeQuery();
						if (rs.next())
						{
							issCriteria	= rs.getString(1);
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null ;
						double noOfArt =0;
						int noOfArticles = 0;
						quantity=  Double.parseDouble(stkUpdMap.get("quantity").toString());  //04/06/09
						quantity = getReqDecimal(quantity,3);
						if(quantity > qtyPerArt )
						{
							noOfArt = quantity / qtyPerArt;
							if(noOfArt > (int)noOfArt)
							{
								 noOfArticles  =(int)noOfArt+1;
							}
							else
							{
							 noOfArticles = (int)noOfArt;
							}
						}
						else
						{
							noOfArticles =1;
						}

						// 19/05/10 manoharan no_art to be rounded to integer
						//stkUpdMap.put("gross_weight",Double.toString(grossWeight));
						//stkUpdMap.put("tare_weight",Double.toString(tareWeight));
						//stkUpdMap.put("net_weight",Double.toString(netWeight));
						stkUpdMap.put("gross_weight", getReqDecString(grossWeight, 3));
						stkUpdMap.put("tare_weight",getReqDecString(tareWeight, 3));
						stkUpdMap.put("net_weight",getReqDecString(netWeight, 3));


						//stkUpdMap.put("no_art",Integer.toString(noOfArticles));

						noOfArticles = (new Double(noOfArticles)).intValue();
						// end 19/05/10 manoharan



						stkUpdMap.put("grade",stkGrade);
						stkUpdMap.put("gross_rate",Double.toString(grossRate));
						stkUpdMap.put("supp_code__mfg",suppCodeMfg);
						stkUpdMap.put("acct_code_inv",acct_code__inv);
						stkUpdMap.put("cctr_code_inv",cctr_code__inv);
						stkUpdMap.put("acct_code_oh",acct_code__oh);
						stkUpdMap.put("cctr_code_oh",cctr_code__oh);
						//genWOIssue.writeLog(this.curTranId, "In woiss_confirm() 04/03/14  stkUpdMap [" + stkUpdMap.toString() + "]"); // 04/03/14 manoharan write the log
						StockUpdate stkUpd =  new StockUpdate();
						errString = stkUpd.updateStock(stkUpdMap,xtraParams,conn);
						stkUpd =null;
						if(errString != null && errString.indexOf("Error") != -1  )
						{
							return errString;
						}
						stkUpdMap.clear();
						//stkOpt ="0";
					}
				}
				if("0".equalsIgnoreCase(stkOpt))
				{
					upd =0;
					sql ="update workorder_bill set iss_qty =  quantity + nvl(potency_adj,0) , "
					  +" iss_date = ? where  work_order ='"+workOrder+"'  and operation ="+operation
					  +"and exp_lev ='"+expLev+"'";
					pstmt = conn.prepareStatement(sql);
					pstmt.setTimestamp(1,new Timestamp(System.currentTimeMillis()));
					upd = pstmt.executeUpdate();
					pstmt.close();
					pstmt = null ;
				}
				else
				{
					upd =0;
					sql ="update workorder_bill set iss_qty =  case when iss_qty is null then 0 else iss_qty end + " + quantity
					  +", iss_date = ? where  work_order ='"+workOrder+"'  and operation ="+operation
					  +"and exp_lev ='"+expLev+"'";
					pstmt = conn.prepareStatement(sql);
					pstmt.setTimestamp(1,new Timestamp(System.currentTimeMillis()));
					upd = pstmt.executeUpdate();
					pstmt.close();
					pstmt = null ;
				}
			}
			rsDet.close();
			rsDet = null;
			pstmtDet.close();
			pstmtDet = null;
			String empCodeAprv = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginEmpCode");

			sql =" update "+hdrTable+" set emp_code__aprv ='"+empCodeAprv+"' ,confirmed ='Y', chg_term ='"+userId+"', tran_date =?,conf_date=? "
				+" where tran_id ='"+tranID+"' ";
			pstmt = conn.prepareStatement(sql); //conf_date
			pstmt.setTimestamp(1,tranDate);
			//CHANGED BY HKUMAR ON 18-03-10
			//pstmt.setTimestamp(2,new Timestamp(System.currentTimeMillis()));//conf_date added on 04/01/2010 at dadra
			Timestamp confDate = new Timestamp(System.currentTimeMillis());
			SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getDBDateFormat());
			java.util.Date date = sdf.parse(confDate.toString());
			confDate = Timestamp.valueOf(sdf.format(date).toString() + " 00:00:00.0");
			pstmt.setTimestamp(2,confDate);
			upd = pstmt.executeUpdate();
			pstmt.close();
			pstmt = null ;
			if ("R".equals(tranType) )
			{
				tranSer = "W-IRTN";
			}
			else
			{
				tranSer = "W-ISS";
			}
			if ( errString == null || errString.trim().length() == 0 ) 
			{
				InvAcct invAcct = new InvAcct();
				errString = invAcct.wipPost( tranID,tranSer,  conn );
			}
			if (errString == null || errString.trim().length() == 0)
			{
				if( localConnection )
				{
					conn.commit();
				}
				errString = itmDBAccessEJB.getErrorString("","VTCNFSUCC","");
			}
			else
			{
				if( localConnection )
				{
					conn.rollback();
				}
			}
			
		}
		catch(SQLException ex)
		{
			ex.printStackTrace();
			errString = "VTCONFERR";
			throw new ITMException(ex);
		}
		catch(Exception e)
		{
			e.printStackTrace();
			errString = "VTCONFERR";
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if(rs != null)
				{
					rs.close();
					rs = null;					
				}		
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;					
				}		
				//if( conn != null && ! conn.isClosed() ) // Gulzar - 25/11/11
				if( conn != null && !conn.isClosed() && localConnection ) //Gulzar - 25/11/11
				{
					conn.close();
					conn = null;
				}
			}
			catch(Exception e)
			{
				System.out.println(e.getMessage());
				throw new ITMException(e);
			}
		}
		return errString;
	}	
	////////////////////////////////////////////////////////////////
	private String preConfirmChk(String tranID, String issType, String xtraParams, Connection conn)  throws RemoteException,ITMException
	{
		String errCode ="",errString = "", confirmed = "",runOpt ="I", siteCode = "", itemCode = "", locCode = "",autoReQc = "";
		String lotNo = "", lotSl = "", unit = "", deptCode = "", workOrder = "",expLev = "";
		String tranType = "",qcReqd = "", itemCodeser = "", matched = "", itemCodeunit = "",scanBarcode = "";
		String trackShelfLife = "",itemCodecodemain = "",itemCodecoderef = "",useInvStatus = "",bomCode  ="";
		String sql ="",scannedBarcode = "",itemCodeDetail = "",scanned = "", stkOpt = "", scannedbarcode = "";
		String qorderNo = "", xmlString = "" ,xmlString2 = "",itemCodeCode = "";
		String procMth = "",siteCodecode = "", detTable = "", hdrTable = "";
		int	lineNo =0,operation = 0, cnt = 0, preOp = 0, ctr = 0 , count = 0, noArt = 0, curRow  = 0;
		double qty = 0, stkQty = 0, rate = 0,drate = 0,potencyAdjQty = 0, qtystduom = 0;
		Timestamp	today = null ,tranDate = null,effDate = null, expDate = null,retestDate = null; 
		PreparedStatement pstmt = null,pstmt1 = null;
		ResultSet rs = null,rs1 = null;

		try
		{
			ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
			MfgCommon mfgCommon = new MfgCommon();
			GenericUtility genericUtility = GenericUtility.getInstance();
			if("R".equalsIgnoreCase(issType))
			{
				detTable ="WORDER_ISSDET_RND";
				hdrTable ="WORDER_ISS_RND";
			}
			else
			{
				detTable ="workorder_issdet";
				hdrTable ="workorder_iss";
			}
			//xtraParams :[loginCode=BASE~~loginEmpCode=E01712    ~~loginSiteCode=SP110~~entityCode=E08135    ~~profileId=SUPER     ~~userType=E~~runMode=I~~user_lang=en~~user_country=US~~charEnc=UTF-8~~timeZone=Asia/Calcutta~~termId=192.168.0.78~~chgTerm=localhost.local~~detCnt=1~~focusRow=1~~mfr_date=01/07/14~~mto_date=31/07/14~~REC_CTR=-1~~saveLevel=0]
			runOpt = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"runMode");
			if (runOpt == null || runOpt.trim().length() == 0)
			{
				runOpt = "I";
			}
			today = new java.sql.Timestamp(System.currentTimeMillis()) ;
			SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getDBDateFormat());
			tranDate = java.sql.Timestamp.valueOf(sdf.format(today) + " 00:00:00.000");

			sql = "select confirmed , site_code,tran_type,tran_date,dept_code,work_order, operation, eff_date "
				+ " from " + hdrTable + " where tran_id = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,tranID);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				confirmed = rs.getString("confirmed");
				siteCode = rs.getString("site_code");
				tranType = rs.getString("tran_type");
				tranDate = rs.getTimestamp("tran_date");
				deptCode = rs.getString("dept_code");
				workOrder = rs.getString("work_order");
				operation = rs.getInt("operation");
				effDate = rs.getTimestamp("eff_date");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			if ("Y".equals(confirmed))
			{
				errCode = "VTMCONF1";
				errString = itmDBAccessEJB.getErrorString("",errCode,"","",conn);
				return errString;
			}
			
			if (!"R".equals(tranType))
			{
				sql = "select max(operation) "
					+ " from workorder_bill where work_order = ? and operation  < ? and usage_type = 'R' ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,workOrder);
				pstmt.setInt(2,operation);
				rs = pstmt.executeQuery();
				if (rs.next())
				{
					preOp = rs.getInt(1);
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
				if (preOp > 0 )
				{
					sql = "select count(*) from " + hdrTable + " where work_order =  ? and operation  < ? "
						+ " and tran_type <> 'R' and case when confirmed is null then 'N' else confirmed end  <> 'Y' ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,workOrder);
					pstmt.setInt(2,preOp);
					rs = pstmt.executeQuery();
					if (rs.next())
					{
						ctr = rs.getInt(1);
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					if (ctr > 0 )
					{
						errCode = "VTWOINEOPE";
						errString = itmDBAccessEJB.getErrorString("",errCode,"","",conn);
						return errString;
					}
					else if (ctr == 0 )
					{
						sql = "select count(*) from " + hdrTable + " where work_order =  ? and operation  = ? "
							+ " and tran_type <> 'R' and case when confirmed is null then 'N' else confirmed end  <> 'Y' "
							+ " and case when status is null then 'O' else status end <> 'X'";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1,workOrder);
						pstmt.setInt(2,preOp);
						rs = pstmt.executeQuery();
						if (rs.next())
						{
							ctr = rs.getInt(1);
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						if (ctr > 0 )
						{
							errCode = "VTWOINEOPE";
							errString = itmDBAccessEJB.getErrorString("",errCode,"","",conn);
							return errString;
						}
					}
				}
				//Added By Rutuja on 02-06-12 - MF2CSUN003		
				sql = "select count(*) from woi_scan_hdr where WO_ISS_TRANID  = ? "
					+ " and case when confirmed is null then 'N' else confirmed end  = 'N' ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,tranID);
				rs = pstmt.executeQuery();
				if (rs.next())
				{
					count = rs.getInt(1);
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
				if (count > 0 )
				{
					errCode = "VTWOICONF" ;
					errString = itmDBAccessEJB.getErrorString("",errCode,"","",conn);
					return errString;
				}
				//Ended by Rutuja on 02-06-12 - MF2CSUN003				
				// Start pravin --20/02/2012--MF89SUN092////////////
				// 07/06/13 manoharan in case scan only update the scanned as Y if record found in workorder_issdet_sl as per no_art
				itemCodeDetail = "";
				sql = " select b.item_code,b.loc_code,b.lot_no,b.lot_sl,b.quantity ,b.unit,b.line_no,b.exp_lev, "
					+ " b.qc_reqd,b.potency_adj,b.scanned,a.site_code, case when c.scanned_barcode is null then '0' else c.scanned_barcode end as scanned_barcode,  "
					+ " b.no_art from workorder_iss a,workorder_issdet b,siteitem c "
					+ " where	a.tran_id = b.tran_id "
					+ " AND c.item_code = b.item_code "
					+ " and a.site_code = c.site_code "
					+ " and (trim(case when c.scanned_barcode is null then '0' else c.scanned_barcode end) = '1' or trim(case when c.scanned_barcode is null then '0' else c.scanned_barcode end) = '2' ) "
					+ " and b.tran_id = ? ";
				pstmt1 = conn.prepareStatement(sql);
				pstmt1.setString(1,tranID);
				rs1 = pstmt1.executeQuery();
				while (rs1.next())
				{
				
					itemCode = rs1.getString("item_code");
					locCode = rs1.getString("loc_code");
					lotNo = rs1.getString("lot_no");
					lotSl = rs1.getString("lot_sl");
					qty = rs1.getDouble("quantity");
					unit = rs1.getString("unit");
					lineNo = rs1.getInt("line_no");
					expLev = rs1.getString("exp_lev");
					qcReqd = rs1.getString("qc_reqd");
					potencyAdjQty = rs1.getDouble("potency_adj");
					scanned = rs1.getString("scanned");
					//siteCode = rs1.getString("");
					scannedbarcode = rs1.getString("scanned_barcode");
					noArt = rs1.getInt("no_art");
					if ( scanned == null || "null".equals(scanned) || scanned.trim().length() == 0 || "N".equals(scanned) )
					{
						if ("1".equals(scannedbarcode) )
						{

							sql = "select count(1) from workorder_issdet_sl "
								+ " where tran_id__iss = ? and line_no__iss = ? ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1,tranID);
							pstmt.setInt(2,lineNo);
							rs = pstmt.executeQuery();
							if (rs.next())
							{
								count = rs.getInt(1);
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;
							if (count >= noArt )
							{
								sql = "update workorder_issdet set scanned = 'Y' "
									+ " where tran_id = ? and line_no = ? ";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1,tranID);
								pstmt.setInt(2,lineNo);
								count = pstmt.executeUpdate();
							}
							else
							{
								itemCodeDetail = itemCodeDetail + "Line_no ["+ lineNo +"]\t Item Code [" + itemCode + "]\t Exp_Lev ["+expLev+ "]\t Lot_no ["+lotNo+ "]\t Lot_sl ["+lotSl+ "]\t" ;
							}
						}
						else
						{
							itemCodeDetail = itemCodeDetail + "Line_no ["+ lineNo+"]\t Item Code ["+itemCode + "]\t Exp_Lev ["+expLev+ "]\t Lot_no ["+lotNo+ "]\t Lot_sl ["+lotSl+ "]\t";
						}
					}
				}
				rs1.close();
				rs1 = null;
				pstmt1.close();
				pstmt1 = null;

				if (itemCodeDetail.trim().length() > 0 )
				{
					errCode = "VTNOWEIGH";
					errString = itmDBAccessEJB.getErrorString("",errCode,"","",conn);
					return errString;
				}
			}
			// End Pravin --20/02/12--////////////////
			sql = " select item_code,loc_code,lot_no,lot_sl,quantity,unit,line_no,exp_lev,qc_reqd,potency_adj "
				+ "from 	workorder_issdet  "
				+ " where	tran_id = ? ";
			pstmt1 = conn.prepareStatement(sql);
			pstmt1.setString(1,tranID);
			rs1 = pstmt1.executeQuery();
			while (rs1.next())
			{
				
				itemCode = rs1.getString("item_code");
				locCode = rs1.getString("loc_code");
				lotNo = rs1.getString("lot_no");
				lotSl = rs1.getString("lot_sl");
				qty = rs1.getDouble("quantity");
				unit = rs1.getString("unit");
				lineNo = rs1.getInt("line_no");
				expLev = rs1.getString("exp_lev");
				qcReqd = rs1.getString("qc_reqd");
				potencyAdjQty = rs1.getDouble("potency_adj");
				System.out.println("Current lineNo [" + lineNo + "] itemCode ["+itemCode + "] qcReqd [" + qcReqd + "]" );
				// below condition added by stkOpt added by pravin -- 23/04/12--
				
				sql  ="select case when stk_opt is null then '0' else stk_opt end, item_ser, unit, nvl(track_shelf_life,'N') "
				   +" from item where item_code = ? " ;
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,itemCode);
				rs = pstmt.executeQuery();
				if (rs.next())
				{
					stkOpt = rs.getString(1);
					itemCodeser = rs.getString(2);
					itemCodeunit = rs.getString(3);
					trackShelfLife = rs.getString(4);
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null ;
				if (!"0".equals(stkOpt))
				{
					//rahul on 07-08-03 for qc_reqd to be checked first from siteitem & then from item
					qcReqd = mfgCommon.chkQcReqd(siteCode, itemCode, conn);
					autoReQc = mfgCommon.chkAutoQcReqd(siteCode, itemCode, conn);

					if ("I".equals(tranType) || "O".equals(tranType) || "D".equals(tranType) || "F".equals(tranType) )
					{
						sql  ="select exp_date,retest_date "
							+ " 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,locCode);
						pstmt.setString(4,lotNo);
						pstmt.setString(5,lotSl);
						rs = pstmt.executeQuery();
						if (rs.next())
						{
							expDate = rs.getTimestamp("exp_date");
							retestDate = rs.getTimestamp("retest_date");
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null ;
						if ("I".equals(runOpt))
						{
							if ("Y".equals(qcReqd) && "Y".equals(autoReQc) && "Y".equals(trackShelfLife))
							{
								if (expDate != null)
								{
									if (expDate.compareTo(today) <= 0)
									{
										errCode = "VTLOTAEUR";
										errString = itmDBAccessEJB.getErrorString("",errCode,"","",conn);
										break;
									}
								}
								if (retestDate != null )
								{
									if (retestDate.compareTo(today) <= 0)
									{
										errCode = "VTLOTAEUR";
										errString = itmDBAccessEJB.getErrorString("",errCode,"","",conn);
										break;
									}
								}
							}
							else if ("Y".equals(qcReqd) && "N".equals(autoReQc) && "Y".equals(trackShelfLife))
							{
								if (expDate != null)
								{
									if (expDate.compareTo(today) <= 0)
									{
										errCode = "VTLOTAEUR";
										errString = itmDBAccessEJB.getErrorString("",errCode,"","",conn);
										break;
									}
								}
							}
							else if ("N".equals(qcReqd) && "Y".equals(trackShelfLife))
							{
								if (expDate != null)
								{
									if (expDate.compareTo(today) <= 0)
									{
										errCode = "VTLOTAEUR";
										errString = itmDBAccessEJB.getErrorString("",errCode,"","",conn);
										break;
									}
								}
							}
							else if  ("Y".equals(qcReqd) && "Y".equals(autoReQc) && "N".equals(trackShelfLife))
							{
								if (retestDate != null )
								{
									if (retestDate.compareTo(today) <= 0)
									{
										errCode = "VTLOTAEUR";
										errString = itmDBAccessEJB.getErrorString("",errCode,"","",conn);
										break;
									}
								}
							}
						}

						sql  ="select bom_code from workorder where work_order = ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1,workOrder);
						rs = pstmt.executeQuery();
						if (rs.next())
						{
							bomCode = rs.getString("bom_code");
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null ;

						sql  ="select item_code, item_code__ref from workorder_bill "
							+ " where work_order = ? "
							+ " and operation = ? "
							+ " and exp_lev = ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1,workOrder);
						pstmt.setInt(2,operation);
						pstmt.setString(3,expLev);
						rs = pstmt.executeQuery();
						if (rs.next())
						{
							itemCodecodemain = rs.getString("item_code");
							itemCodecoderef = rs.getString("item_code__ref");
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null ;
						sql  ="select case when use_invstatus is null then 'U' else use_invstatus end as use_invstatus "
							+ " from bomdet where bom_code = ? "
							+ " and item_code = ? "
							+ " and item_ref = ?  ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1,bomCode);
						pstmt.setString(2,itemCodecodemain);
						pstmt.setString(3,itemCodecoderef);
						rs = pstmt.executeQuery();
						if (rs.next())
						{
							useInvStatus = rs.getString("use_invstatus");
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null ;
						
						sql ="select a.quantity,rate from stock a ,location b, invstat c " 
							+ "where a.loc_code = b.loc_code  "
							+ " and b.inv_stat = c.inv_stat  " 
							+ " and a.item_code = ? "
							+ " and a.site_code = ? " 
							+ " and a.loc_code = ? " 
							+ " and a.lot_no = ? "
							+ " and a.lot_sl = ? " ;
						if ("I".equals(runOpt))
						{
							sql = sql + " and c.stat_type <> 'S'";
							if ("Y".equals(trackShelfLife)  &&  "U".equals(useInvStatus))
							{
								sql = sql +	" and ( a.exp_date > ? or a.exp_date is null) " ;
								sql = sql + " and c.available = 'Y' " ;
								if ("Y".equals(qcReqd) && "Y".equals(autoReQc))
								{
									sql = sql + " and a.retest_date > ? " ;
								}
							}
						}
						else
						{
							if ("U".equals(useInvStatus))
							{
								sql = sql + "and c.available = 'Y' ";
							}
							else
							{
								sql = sql + "and c.stat_type <> 'S'";
							}
						}
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1,itemCode);
						pstmt.setString(2,siteCode);
						pstmt.setString(3,locCode);
						pstmt.setString(4,lotNo);
						pstmt.setString(5,lotSl);
						if ("I".equals(runOpt))
						{
							if ("Y".equals(trackShelfLife)  &&  "U".equals(useInvStatus))
							{
								pstmt.setTimestamp(6,effDate);
								if ("Y".equals(qcReqd) && "Y".equals(autoReQc))
								{
									pstmt.setTimestamp(7,effDate);
								}
							}
						}
						rs = pstmt.executeQuery();
						if (rs.next())
						{
							stkQty = rs.getDouble(1);
							rate = rs.getDouble(2);
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null ;
						if ("I".equals(tranType) || "O".equals(tranType) || "D".equals(tranType) || "F".equals(tranType) )
						{
							if (qtystduom > stkQty )
							{
								errCode = "VTSTK5";
								errString = itmDBAccessEJB.getErrorString("",errCode,"","",conn);
								break;
							}
						}
					}
				}
				if (!"0".equals(stkOpt) && "R".equals(tranType) && "Y".equals(qcReqd))
				{
					System.out.println("Calling CreateQcorder for tranID ["+tranID + "] siteCode [ "+ siteCode + "] itemCode [" + itemCode + "] lineNo [" + lineNo + "]" );
					errString = CreateQcorder(tranID,siteCode,itemCode,lineNo, xtraParams, conn);
					System.out.println("After Calling CreateQcorder errString ["+errString + "]" );
					if(  errString != null && errString.trim().length() > 0 )
					{
						break;
					}
				}
			}
			rs1.close();
			rs1 = null;
			pstmt1.close();
			pstmt1 = null;
			if (errString != null && errString.trim().length() > 0 )
			{
				return errString;
			}
			/*if isnull(errCode) or len(trim(errCode)) = 0 then	
				If tranType = 'R' Then
					for curRow = 1 to upperbound(ls_qcord_no[])
						qorderNo = ls_qcord_no[curRow]			
						if lds_qcorder.retrieve(qorderNo) = 1 then											
							itemCodeCode = lds_qcorder.getitemstring (1,"item_code")				
							select proc_mth into :procMth from siteitem
									where item_code =:itemCodeCode
									and   site_code =:siteCode ;
							if sqlca.sqlcode < 0 then
								errCode = 'DS000' + trim(string(sqlca.SqlDbCode)) + "~t" + sqlca.sqlerrtext
								exit
							elseif sqlca.sqlcode = 100  or isnull(procMth) or len(trim(procMth)) =0 then
								select proc_mth into :procMth from item 
								where item_code =:itemCodeCode;
							end if						
							if (not isnull(procMth)) and len(trim(procMth)) > 0 then 								
								gf_add_glob_args(is_extra_arg)
								gs_start_xml = true
								gs_end_xml = true
								xmlString = gbf_convert_xml_tofile_ds(lds_qcorder,'1',"N",'A','1') 
								xmlString2 = xmlString
								errCode = nvo_webservice.gbf_webservice_call("w_worder_iss",xmlString,xmlString2,"process",is_extra_arg)
								if len(trim(errCode)) > 0 then 
									exit
								end if
								lds_qcorder.reset()
							end if
							setnull(is_extra_arg)
						end if
					next
					destroy nvo_webservice
					destroy lds_qcorder		
				end if
			end if
			//Ended by shahid 02/12/2008 for MF78SUN040
			*/
		}
		catch(SQLException ex)
		{
			ex.printStackTrace();
			errString = "VTCONFERR";
			throw new ITMException(ex);
		}
		catch(Exception e)
		{
			e.printStackTrace();
			errString = "VTCONFERR";
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if(rs != null)
				{
					rs.close();
					rs = null;					
				}		
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;					
				}		
			}
			catch(Exception e)
			{
				System.out.println(e.getMessage());
				throw new ITMException(e);
			}
		}
		return errString;
	}
////////////////////////////////////////////////////////////////
	public double getReqDecimal(double actVal, int prec)  throws ITMException, Exception
	{
		DecimalFormat decFormat = null;
		try
		 {
			String fmtStr = "############0";
			String strValue = null;
			//System.out.println("manohar 18/08/10 actVal  [" + actVal + "]");
			if (prec > 0)
			{
				fmtStr = fmtStr + "." + "000000000".substring(0, prec);
			}
			decFormat = new DecimalFormat(fmtStr);
			//System.out.println("manohar 03/08/10 confirm DecimalFormat(fmtStr) [" + decFormat + "]");
			//System.out.println("manohar 03/08/10 confirm Double.parseDouble(decFormat.format(actVal)) [" + Double.parseDouble(decFormat.format(actVal)) + "]");
		}
		catch(Exception e)
		{
			System.out.println("Exception ::" + e.getMessage() + ":");
			e.printStackTrace();
			actVal = 0;
			System.out.println("manohar 03/08/10 confirm Double.parseDouble(decFormat.format(actVal)) [" + Double.parseDouble(decFormat.format(actVal)) + "]");
			throw new ITMException(e);
		}
		//
		return Double.parseDouble(decFormat.format(actVal));
	}
	public String getReqDecString(double actVal, int prec)
	{
		NumberFormat numberFormat = NumberFormat.getIntegerInstance ();
		Double DoubleValue = new Double (actVal);
		numberFormat.setMaximumFractionDigits(3);
		String strValue = numberFormat.format(DoubleValue);
		strValue = strValue.replaceAll(",","");
		//double reqVal = Double.parseDouble(strValue);
		//return reqVal;
		return strValue;
	}
	
	//added by prajakta 27/12/06
	// 22/08/00 manohar batch_no, expiry_date added in qc_order
	private String CreateQcorder(String tranID,String siteCode,String itemCode,int lineNo, String xtraParams, Connection conn)  throws ITMException, Exception
	{
		String 	errCode = "", errString = "", qcReqd = "", lotNo = "", lotNoRcp = "", lotSl = "", keyString = "", xmlValues = "";
		String winName = "", qcorderNo = "", lotRcp = "", locCode = "",	 tranId = "", unit = "",batchNo = "", userid = "SYSTEM", termid  = "SYSTEM";
		String locCodeAprv = "", locCodeRej = "", itemSer = "",	routeCode = "", suppCodeMfg = "", suppCode = "", sql = "";
		double quantity	= 0;
		Timestamp today = null, expiryDate = null,	qcDueDate = null, 	relDate = null,	retestDate= null , expDate = null, mfgDate = null, tranDate = null;
		int count = 0,qcLeadTime = 0;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try
		{
			GenericUtility genericUtility = GenericUtility.getInstance();
			ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
			today = new java.sql.Timestamp(System.currentTimeMillis()) ;
			SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getDBDateFormat());
			tranDate = java.sql.Timestamp.valueOf(sdf.format(today) + " 00:00:00.000");
			//xtraParams :[loginCode=BASE~~loginEmpCode=E01712    ~~loginSiteCode=SP110~~entityCode=E08135    ~~profileId=SUPER     ~~userType=E~~runMode=I~~user_lang=en~~user_country=US~~charEnc=UTF-8~~timeZone=Asia/Calcutta~~termId=192.168.0.78~~chgTerm=localhost.local~~detCnt=1~~focusRow=1~~mfr_date=01/07/14~~mto_date=31/07/14~~REC_CTR=-1~~saveLevel=0]
			userid = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginCode");
			termid = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"termId");
			retestDate = tranDate;
			winName = "w_qcorder_new";

			sql  ="select loc_code,lot_no,	lot_sl,quantity, unit from workorder_issdet  "
				+ " where tran_id = ? "
				+ " and line_no = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,tranID);
			pstmt.setInt(2,lineNo);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				locCode = rs.getString("loc_code");
				lotNoRcp = rs.getString("lot_no");
				lotSl = rs.getString("lot_sl");
				quantity = rs.getDouble("quantity");
				unit = rs.getString("unit");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null ;

			sql  ="select item_ser from	siteitem "
				+ " where item_code = ? "
				+ " and site_code = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,itemCode);
			pstmt.setString(2,siteCode);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				itemSer = rs.getString("item_ser");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null ;

			// Navin : 29/08/00 Calculate QC Due Date
			//select :today + nvl(qc_lead_time,0) into :qcDueDate from item where item_code = :itemCode ;//Commented by Tushar instr by subu 12/02/10.
			sql  ="select  qc_lead_time from item "
				+ " where item_code = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,itemCode);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				qcLeadTime = rs.getInt("qc_lead_time");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null ;
			UtilMethods utilMethods = UtilMethods.getInstance();
			qcDueDate = utilMethods.RelativeDate(tranDate, qcLeadTime);
			
			sql  ="select key_string from transetup "
				+ " where tran_window = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,winName);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				keyString = rs.getString("key_string");
			}
			else
			{
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null ;
				sql  ="select key_string from transetup "
					+ " where tran_window = 'GENERAL' ";
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
				if (rs.next())
				{
					keyString = rs.getString("key_string");
				}
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null ;

			sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
			 

			xmlValues ="<?xml version=\"1.0\" encoding=\"utf-8\"?><Root>";
			xmlValues = xmlValues + "<Header></Header>";
			xmlValues = xmlValues + "<Detail1>";
			xmlValues = xmlValues +        "<tran_id></tran_id>";
			xmlValues = xmlValues +        "<qorder_no></qorder_no>";
			xmlValues = xmlValues +        "<site_code>" + siteCode + "</site_code>";
			xmlValues = xmlValues +        "<tran_date>" + sdf.format(tranDate) + "</tran_date>";
			xmlValues = xmlValues +        "<qorder_date>" + sdf.format(tranDate) + "</qorder_date>";
			xmlValues = xmlValues +        "<lot_no>" + lotNoRcp + "</lot_no>";
			xmlValues = xmlValues +        "<qorder_type>M</qorder_type>";
			xmlValues = xmlValues +        "<item_ser>" + itemSer + "</item_ser>";
			xmlValues = xmlValues +        "<porcp_no>" + tranID + "</porcp_no>";
			xmlValues = xmlValues +        "<porcp_line_no>" + lineNo + "</porcp_line_no>";
			xmlValues = xmlValues + "</Detail1></Root>";
			System.out.println("xmlValues  :["+xmlValues+"]");
			TransIDGenerator tg = new TransIDGenerator(xmlValues, "BASE", CommonConstants.DB_NAME);
			qcorderNo = tg.generateTranSeqID("QC-ORD", "", keyString, conn);
			System.out.println("New QC order number ["+qcorderNo + "]");

			if ("ERROR".equals(qcorderNo))
			{
				errCode = "VTTRANID";
				errString = itmDBAccessEJB.getErrorString("",errCode,"","",conn);
				return errString;
				
			}
			if (quantity < 0 )
			{
				quantity = quantity * -1;
			}
			sql  ="select loc_code__aprv,loc_code__rej 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())
			{
				locCodeAprv = rs.getString("loc_code__aprv");
				locCodeRej = rs.getString("loc_code__rej");
			}
			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,itemCode);
			pstmt.setString(2,lotNoRcp);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				count = rs.getInt(1);
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null ;
					
			if (count > 0 )
			{
				sql  ="select supp_code__mfg , supp_code, exp_date, mfg_date,retest_date from item_lot_info  "
					+ " where item_code = ?  and lot_no = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,itemCode);
				pstmt.setString(2,lotNoRcp);
				rs = pstmt.executeQuery();
				if (rs.next())
				{
					suppCodeMfg = rs.getString("supp_code__mfg");
					suppCode = rs.getString("supp_code");
					expDate = rs.getTimestamp("exp_date");
					mfgDate = rs.getTimestamp("mfg_date");
					retestDate = rs.getTimestamp("retest_date");
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null ;
			}
			else
			{
				sql  ="select exp_date, mfg_date, supp_code__mfg from stock  "
					+ " where item_code = ?  and lot_no = ? and lot_sl = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,itemCode);
				pstmt.setString(2,lotNoRcp);
				pstmt.setString(3,lotSl);
				rs = pstmt.executeQuery();
				if (rs.next())
				{
					suppCodeMfg = rs.getString("supp_code__mfg");
					expDate = rs.getTimestamp("exp_date");
					mfgDate = rs.getTimestamp("mfg_date");
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null ;
			}
					
			//Navin:26/12/00 - set qorder_type = 'S', it was 'M' ( Tarun)	
			//by rahul 01-09-2003 to add new column item_code__new
			sql  ="insert into qc_order "
				+ " (qorder_no,	qorder_type, qorder_date, site_code, "
				+ " item_code, 	route_code,	quantity,	qty_passed, "
				+ " qty_rejected, 	start_date,	due_date,	rel_date, "
				+ " porcp_no,	porcp_line_no,	lot_no,	lot_sl, "
				+ " chg_date,	chg_user,	chg_term,	loc_code, "
				+ " qty_sample,	status,	unit,	qc_create_type, "
				+ " unit__sample,	loc_code__aprv,	loc_code__rej,	lot_no__new, "
				+ " retest_date, 	expiry_date,	item_code__new, mfg_date, "
				+ " supp_code, supp_code__mfg) "
			+ " values "
			+ " (	?, ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?, ?,?) ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,qcorderNo);
			pstmt.setString(2,"S");
			pstmt.setTimestamp(3,tranDate);
			pstmt.setString(4,siteCode);
			pstmt.setString(5,itemCode);
			pstmt.setString(6,routeCode);
			pstmt.setDouble(7,quantity);
			pstmt.setDouble(8,0);
			pstmt.setDouble(9,0);
			pstmt.setTimestamp(10,tranDate);
			pstmt.setTimestamp(11,qcDueDate);
			pstmt.setTimestamp(12,relDate);
			pstmt.setString(13,tranID);
			pstmt.setString(14, "" +lineNo);
			pstmt.setString(15,lotNoRcp);
			pstmt.setString(16,lotSl);
			pstmt.setTimestamp(17,today);
			pstmt.setString(18,userid);
			pstmt.setString(19,termid);
			pstmt.setString(20,locCode);
			pstmt.setDouble(21,0);
			pstmt.setString(22,"U");
			pstmt.setString(23,unit);
			pstmt.setString(24,"A");
			pstmt.setString(25,unit);
			pstmt.setString(26,locCodeAprv);
			pstmt.setString(27,locCodeRej);
			pstmt.setString(28,lotNoRcp);
			pstmt.setTimestamp(29,retestDate);
			pstmt.setTimestamp(30,expDate);
			pstmt.setString(31,itemCode);
			pstmt.setTimestamp(32,mfgDate);
			pstmt.setString(33,suppCode);
			pstmt.setString(34,suppCodeMfg);
			count = pstmt.executeUpdate();
			
		}
		catch(SQLException ex)
		{
			ex.printStackTrace();
			errString = "VTCONFERR";
			throw new ITMException(ex);
		}
		catch(Exception e)
		{
			e.printStackTrace();
			errString = "VTCONFERR";
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if(rs != null)
				{
					rs.close();
					rs = null;					
				}		
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;					
				}		
			}
			catch(Exception e)
			{
				System.out.println(e.getMessage());
				throw new ITMException(e);
			}
		}
		return errCode;
	}
	//end added by prajakta 27/12/06
}
