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 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;

@Stateless 
public class RcpBackflushConfirm extends ActionHandlerEJB implements RcpBackflushConfirmLocal,RcpBackflushConfirmRemote
{
	
	public String confirm(String tranID,String xtraParams, String forcedFlag) throws RemoteException,ITMException
	{
		System.out.println("----------------confirmed method (RcpBackflushConfirm)---------");
		String retString = "";		
		Connection conn = null;	
		ConnDriver connDriver = null;
		boolean isConn= false;
		try
		{
			 connDriver = new ConnDriver();
	         conn = connDriver.getConnectDB("Driver");
	         conn.setAutoCommit(false);

			retString = confirmRcpBackflush( tranID, xtraParams, forcedFlag,conn,isConn);		
				
		}
		catch(Exception exception)
		{
			System.out.println("Exception in [RcpBackflushConfirm] confirm " + exception.getMessage());
		}
		return retString;
	}
	
	public String confirmRcpBackflush( String tranID, String xtraParams,String forcedFlag,Connection conn, boolean connStatus ) throws RemoteException,ITMException
	{		
		//Connection conn=null;
        PreparedStatement pstmt = null;
        ResultSet rs = null,rs1 = null;        
        ConnDriver connDriver = null;
        ITMDBAccessEJB itmDBAccessEJB = null;
        FinCommon finCommon = null;
        GenericUtility genericUtility = null;
        FinCommonInvAcct finCommonInvAcct = null;
        HashMap stkUpdMap = null;
        Timestamp tranDate = null;
        String sql = "",errString="",rcpConfirm="",siteCodeHdr="",backFlushType="",projCodeHdr="";
        double quantity=0;
        try
        {
        	if (conn==null)
        	{
	        	connDriver = new ConnDriver();
	   	        conn = connDriver.getConnectDB("Driver");
	   	        conn.setAutoCommit(false);
	   	        connStatus = true;
        	}
        	
        	 finCommon = new FinCommon();
	         finCommonInvAcct = new FinCommonInvAcct();
	         itmDBAccessEJB = new ITMDBAccessEJB();
	         genericUtility = new GenericUtility().getInstance();
	         stkUpdMap = new HashMap();
	        
	         System.out.println("tranID------------>>["+tranID+"]");
	         sql = "select confirmed from receipt_backflush where tran_id = ?";
	         System.out.println("SQL : "+sql);
	         pstmt=conn.prepareStatement(sql);
	         pstmt.setString(1, tranID);	            
	         rs1 = pstmt.executeQuery();
	         if(rs1.next())
	         {
                rcpConfirm = rs1.getString("confirmed");               
	         }
	         rcpConfirm=rcpConfirm==null ? "N" : rcpConfirm.trim();
	         rs1.close();rs1=null;
	         pstmt.close();pstmt=null;
	         System.out.println("Confirmed (receipt_backflush)------>>"+rcpConfirm);
	         if(rcpConfirm != null && rcpConfirm.trim().equalsIgnoreCase("Y"))
	         {
                System.out.println("The Selected Transaction is already confirmed..........");
                errString = itmDBAccessEJB.getErrorString("","VTCONF8","","",conn);
                return errString;
	         }
	         else
	         {
	        	 errString = retrieveBackflush(tranID,"R-BFS",1,xtraParams,conn);
	        	 System.out.println("After returning string from retrieveReceipt method-->>["+errString+"]");
	        	 if (errString.indexOf("Error") > -1) {
	        		 conn.rollback();
	        		 return errString; 
	        		
	        	 }
	        	 else if(errString.equalsIgnoreCase("Error")){
	        		 errString = itmDBAccessEJB.getErrorString("","VTDESNCONF","","",conn);
	 				conn.rollback();
	        	 }
	        	       	 
	        	
	         }   
	         System.out.println("errorString @@#---->>["+errString+"]");
	        // System.out.println("isError---->>["+isError+"]");
	         if(errString !=null && errString.trim().length() == 0  )
	         {
	        	 if(connStatus)
	        	 {
	        		 conn.commit();
	        	 }
	        	 errString = itmDBAccessEJB.getErrorString("","VTCONFIRM","","",conn);
	        	 System.out.println("----------Trasaction commited successfully---------");
	         }
	         else
	         {	        	 
	        	 if (!(errString.indexOf("Error") > 0)){
	        	 errString = itmDBAccessEJB.getErrorString("","VTDESNCONF","","",conn);
	        	 }
	        	 conn.rollback();
	        	 System.out.println("----------Trasaction Rollback---------");
	         }
	         
	  
        }//end try
        catch(ITMException ie){
            System.out.println("ITMException : "+ie);
            try{conn.rollback();}catch(Exception t){}
            ie.printStackTrace();
            errString = itmDBAccessEJB.getErrorString("","VTDESNCONF","","",conn);
            System.out.println("Returnng String From RcpBackflushConfirm :"+errString);
            return errString;
        }catch(Exception e){
            System.out.println("Exception in Confirm [RcpBackflushConfEJB]"+e);
            try{conn.rollback();}catch(Exception t){}
            e.printStackTrace();
            errString = itmDBAccessEJB.getErrorString("","VTDESNCONF","","",conn);
            System.out.println("Returnng String From RcpBackflushConfirm :"+errString);
            return errString;
        }finally{
            try{
                stkUpdMap = null;
                if(rs != null){rs.close();rs = null;}
                if(rs1 != null){rs1.close();rs1 = null;}
                if(pstmt != null){pstmt.close();pstmt = null;
                }         
                if(conn!=null && connStatus)
				{
					conn.close();
					conn = null;
				}
               
            }catch(Exception e)
            {
            	System.out.println("Exception : "+e);
            	e.printStackTrace();}
        }
        System.out.println("Returnng String From RcpBackflushConfirm :["+errString+"]");
        return errString;
	}
	
	private String retrieveBackflush(String tranID,String tranSer,int comm,String xtraParams,Connection conn)
	{
		System.out.println("------in retrieveBackflush method-----------");
		String sql="",dbName="",lsLotNo="",lsLocCode="",lsQcreqd="",lsProjCode="",lsBackflushType="",lsUnit="";
		String retString="",lsSingleUnit="",lsSumQtyDet="",lsNoArt="",lsLotsl="",lsItemCode="",locCode="",lotNo="";
		String lsSiteCode="",lsGrade="",lsPackCode="",lsIndno="", empCode = "", userId = "";
		ResultSet rs=null;
		PreparedStatement pstmt=null;
		java.sql.Timestamp ldtExpirydate=null,ldtMfgdate=null;
		double lcQuantityHdr=0,lcTotqtyDet=0,lcQuantityDet=0,lcqtyPerArt=0,lcModqty=0,lcNoart=0,lcCapacity=0;	
		double lcIntegralQty=0,lcDetamt=0,lcHdramt=0;
		int upcnt=0;
		GenericUtility genericUtility = null;
		try 
		{		
			DistCommon distCommon = new DistCommon();
			ITMDBAccessEJB itmDBAccessLocal = new ITMDBAccessEJB();
			dbName = CommonConstants.DB_NAME;
			 genericUtility = GenericUtility.getInstance();
			
			if( dbName.equalsIgnoreCase("db2") || dbName.equalsIgnoreCase("mssql"))		
				sql="select lot_no, loc_code, qc_reqd, exp_date, proj_code, quantity, backflush_type, unit" +
						" from	receipt_backflush  where tran_id = ? for update";		
			else  
				sql="select lot_no, loc_code, qc_reqd, exp_date, proj_code, quantity, backflush_type, unit" +
						" from	receipt_backflush  where tran_id = ? for update nowait";
			
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, tranID);
			rs=pstmt.executeQuery();
			if(rs.next())
			{
				lsLotNo=rs.getString("lot_no") == null ? "" :rs.getString("lot_no").trim();
				lsLocCode=rs.getString("loc_code")== null ? "" :rs.getString("loc_code").trim();
				lsQcreqd=rs.getString("qc_reqd")== null ? "" :rs.getString("qc_reqd").trim();
				ldtExpirydate=rs.getTimestamp("exp_date");
				lsProjCode=rs.getString("proj_code")== null ? "" :rs.getString("proj_code").trim();
				lcQuantityHdr=rs.getDouble("quantity");
				lsBackflushType=rs.getString("backflush_type")== null ? "" :rs.getString("backflush_type").trim();
				lsUnit=rs.getString("unit") == null ? "" :rs.getString("unit");
			}
			
			
			if(rs != null)
			{
				rs.close();
				rs=null;
			}
			if(pstmt !=null)
			{
				pstmt.close();
				pstmt=null;
			}
			System.out.println("Quantity in retrieveReceipt method---->>["+lcQuantityHdr+"]");
			System.out.println("retString --------->>["+retString+"]");
			if(retString.trim().length() == 0 && lcQuantityHdr > 0)
			{			
				int cnt1= getDBRowCount(conn,"receipt_backflush_det","tran_id",tranID);				
				System.out.println("count from receipt_backflush_det quantity cond ---->>["+cnt1+"]");		
				if(cnt1 == 0)
				{
					retString =  itmDBAccessLocal.getErrorString("","VTNOREC2","","",conn);				
					return retString;
				}
			}			
			System.out.println("Length retString in retrieveReceipt--->>["+retString.trim().length()+"]");
			System.out.println("lsBackflushType in retrieveReceipt mwthod--->>["+lsBackflushType+"]");	
			System.out.println("retString --------->>["+retString+"]");
			if(retString.trim().length() == 0 && lsBackflushType.equalsIgnoreCase("C"))
			{
				int cnt1= getDBRowCount(conn,"receipt_backflush_det","tran_id",tranID);	
				System.out.println("count from receipt_backflush_det lsBackflushType cond ---->>["+cnt1+"]");
				if(cnt1  > 1)
				{
					retString =  itmDBAccessLocal.getErrorString("","VTMOREC1","","",conn);				
					return retString;
				}
			}
			if(retString.trim().length() == 0 )
			{
				sql="";  
				sql="select single_unit, sum_qty_det from backflush_type where backflush_type = ?";
				pstmt=conn.prepareStatement(sql);
				pstmt.setString(1, lsBackflushType);
				rs=pstmt.executeQuery();
				if(rs.next())
				{
					lsSingleUnit=rs.getString(1)==null ? "N" :rs.getString(1).trim();
					lsSumQtyDet=rs.getString(2)==null ? "N" :rs.getString(2).trim();
				}
				System.out.println("lsSingleUnit--->>["+lsSingleUnit+"] lsSumQtyDet-->>["+lsSumQtyDet+"]");
				if(rs != null)
				{
					rs.close();
					rs=null;
				}
				if(pstmt !=null)
				{
					pstmt.close();
					pstmt=null;
				}
				if(lsSingleUnit.equalsIgnoreCase("Y"))
				{
					int cnt1=0;	
					sql="";
					sql="select count(1) from receipt_backflush_det where tran_id = ? and unit <> ?";
					pstmt=conn.prepareStatement(sql);
					pstmt.setString(1, tranID);
					pstmt.setString(2, lsUnit);
					rs=pstmt.executeQuery();
					if(rs.next())
						cnt1=rs.getInt(1);
					System.out.println("SingleUnit condition cnt1 value --------->>["+cnt1+"]");
					if(cnt1 > 0)
					{
						retString =  itmDBAccessLocal.getErrorString("","VTINVUNIT","","",conn);				
						return retString;
					}
					if(rs != null)
					{
						rs.close();
						rs=null;
					}
					if(pstmt !=null)
					{
						pstmt.close();
						pstmt=null;
					}
					
					
				}
				if(lsSumQtyDet.equalsIgnoreCase("Y"))  //@@@@@@@@@@@@@@@@@@@@@@@	
				{
					sql="";
					if(lsSingleUnit.equalsIgnoreCase("N"))						
						sql="select sum(case when qty_doc is null then 0 else qty_doc end) from " +
								"receipt_backflush_det where tran_id = ?";				
					else
						sql="select sum(case when quantity is null then 0 else quantity end) from " +
								"receipt_backflush_det where tran_id = ?";
					
					System.out.println("lsSingleUnit sql-------->>"+sql);
					pstmt=conn.prepareStatement(sql);
					pstmt.setString(1, tranID);					
					rs=pstmt.executeQuery();
					
					if(rs.next())
						lcTotqtyDet=rs.getDouble(1);
					
					if(rs != null)
					{
						rs.close();
						rs=null;
					}
					if(pstmt !=null)
					{
						pstmt.close();
						pstmt=null;
					}
					System.out.println("lcTotqtyDet------->>"+lcTotqtyDet);
					lcQuantityHdr=lcTotqtyDet;
					
					sql="";
					sql="update receipt_backflush set quantity = ? where tran_id = ?";
					pstmt=conn.prepareStatement(sql);
					pstmt.setDouble(1, lcQuantityHdr);
					pstmt.setString(2, tranID);	
					
					upcnt=pstmt.executeUpdate();
					System.out.println("NO. of rows updated (receipt_backflush) ------>>"+upcnt);					
					if(pstmt !=null)
					{
						pstmt.close();
						pstmt=null;
					}
					if(lsBackflushType.equalsIgnoreCase("C")) //%%%%%%%%%%%%%%
					{
						sql="";
						sql="select no_art,lot_sl,item_code,loc_code,lot_no,site_code,quantity " +
								"from receipt_backflush_det where tran_id = ?";
						
						pstmt=conn.prepareStatement(sql);
						pstmt.setString(1, tranID);
						rs=pstmt.executeQuery();
						if(rs.next()) //in PB code declare cursor and fetch only one record
						{
							lsNoArt=rs.getString(1) == null ? "" :rs.getString(1).trim();
							lsLotsl=rs.getString(2) == null ? "" :rs.getString(2).trim();
							lsItemCode=rs.getString(3) == null ? "" :rs.getString(3).trim();
							locCode=rs.getString(4) == null ? "" :rs.getString(4).trim();
							lotNo=rs.getString(5) == null ? "" :rs.getString(5).trim();
							lsSiteCode=rs.getString(6) == null ? "" :rs.getString(6).trim();
							lcQuantityDet=rs.getDouble(7);
							
						}
						System.out.println("lsNoArt----->>"+lsNoArt+" lsLotsl-->"+lsLotsl);
						System.out.println("lsItemCode----->>"+lsItemCode+" locCode-->"+locCode);
						System.out.println("lsSiteCode----->>"+lsSiteCode+" lotNo-->"+lotNo+"lcQuantityDet-->"+lcQuantityDet);
						if(rs != null)
						{
							rs.close();
							rs=null;
						}
						if(pstmt !=null)
						{
							pstmt.close();
							pstmt=null;
						}
						sql="";
						sql="select grade from stock where site_code = ? and loc_code = ? and item_code = ? and lot_no = ? and lot_sl = ?";
						pstmt=conn.prepareStatement(sql);
						pstmt.setString(1, lsSiteCode);
						pstmt.setString(2, locCode);
						pstmt.setString(3, lsItemCode);
						pstmt.setString(4, lotNo);
						pstmt.setString(5, lsLotsl);
						
						rs=pstmt.executeQuery();
						if(rs.next())						
							lsGrade=rs.getString(1)== null ? "" : rs.getString(1).trim();
						System.out.println("lsGrade----->>"+lsGrade);
						if(rs != null)
						{
							rs.close();
							rs=null;
						}
						if(pstmt !=null)
						{
							pstmt.close();
							pstmt=null;
						}
						
						sql="";
						sql="update receipt_backflush set lot_sl = ?,grade = ? where tran_id = ?";
						pstmt=conn.prepareStatement(sql);
						pstmt.setString(1, lsLotsl);
						pstmt.setString(2, lsGrade);
						pstmt.setString(3, tranID);
						int upcnt1=pstmt.executeUpdate();	
						System.out.println("No. of rows updated------>>"+upcnt1);
						if(pstmt !=null)
						{
							pstmt.close();
							pstmt=null;
						}
						//Script for calculation of no of article
						sql="select qty_per_art,pack_code from stock where item_code = ? and " +
								"site_code = ? and loc_code = ? and lot_no = ? and lot_sl = ?";
						pstmt=conn.prepareStatement(sql);
						pstmt.setString(1, lsItemCode);
						pstmt.setString(2, lsSiteCode);
						pstmt.setString(3, locCode);
						pstmt.setString(4, lotNo);
						pstmt.setString(5, lsLotsl);
						rs=pstmt.executeQuery();
						if(rs.next())
						{
							lcqtyPerArt=rs.getDouble(1);
							lsPackCode=rs.getString(2)==null ? "" : rs.getString(2).trim();
						}
						if(rs != null)
						{
							rs.close();
							rs=null;
						}
						if(pstmt !=null)
						{
							pstmt.close();
							pstmt=null;
						}
						System.out.println("qtyPerArt---->>["+lcqtyPerArt+"] packCode---->>["+lsPackCode+"]");
						if(lcqtyPerArt > 0)
						{
							sql="select mod(?,?) from dual";
							pstmt=conn.prepareStatement(sql);
							pstmt.setDouble(1, lcQuantityDet);
							pstmt.setDouble(2, lcqtyPerArt);
							rs=pstmt.executeQuery();
							if(rs.next())
								lcModqty=rs.getDouble(1);
							System.out.println("lcModqty--------->>"+lcModqty);
							if(rs != null)
							{
								rs.close();
								rs=null;
							}
							if(pstmt !=null)
							{
								pstmt.close();
								pstmt=null;
							}
							if(lcModqty > 0)
								lcNoart= (lcQuantityDet/lcqtyPerArt)+1;
							else
								lcNoart= lcQuantityDet/lcqtyPerArt;
							System.out.println("lcNoart-------->>"+lcNoart);
							
						} //lcqtyPerArt > 0
						else
						{
							sql="select capacity from packing where pack_code = ?";
							  pstmt=conn.prepareStatement(sql);
							  pstmt.setString(1, lsPackCode);
							  rs=pstmt.executeQuery();
							  if(rs.next())
							    lcCapacity=rs.getDouble(1);
							  System.out.println("lcCapacity------>>["+lcCapacity+"]");
							  if(rs != null)
							  {
								  rs.close();
								  rs=null;
							  }
							  if(pstmt !=null)
							  {
								  pstmt.close();
								  pstmt=null;
							  }
							  if(lcCapacity > 0)
							  {
								  sql="select mod(?,?) from dual";
									pstmt=conn.prepareStatement(sql);
									pstmt.setDouble(1, lcQuantityDet);
									pstmt.setDouble(2, lcCapacity);
									rs=pstmt.executeQuery();
									if(rs.next())
										lcModqty=rs.getDouble(1);
									System.out.println("lcModqty--------->>"+lcModqty);
									if(rs != null)
									{
										rs.close();
										rs=null;
									}
									if(pstmt !=null)
									{
										pstmt.close();
										pstmt=null;
									}
									
									if(lcModqty > 0)
										lcNoart= (lcQuantityDet/lcCapacity)+1;
									else
										lcNoart= lcQuantityDet/lcCapacity;
									System.out.println("lcNoart-------->>"+lcNoart);
							  }// lcCapacity > 0
							  else
							  {
									 sql="Select integral_qty from item Where item_code = ?";
									 pstmt=conn.prepareStatement(sql);
									 pstmt.setString(1, lsItemCode);
									 rs=pstmt.executeQuery();
									 if(rs.next())
										 lcIntegralQty=rs.getDouble(1);
									 System.out.println("lcIntegralQty------>>"+lcIntegralQty);
									 if(lcIntegralQty > 0)
									 {
										 sql="select mod(?,?) from dual";
											pstmt=conn.prepareStatement(sql);
											pstmt.setDouble(1, lcQuantityDet);
											pstmt.setDouble(2, lcIntegralQty);
											rs=pstmt.executeQuery();
											if(rs.next())
												lcModqty=rs.getDouble(1);
											System.out.println("lcModqty--------->>"+lcModqty);
											if(rs != null)
											{
												rs.close();
												rs=null;
											}
											if(pstmt !=null)
											{
												pstmt.close();
												pstmt=null;
											}
											if(lcModqty > 0)
												lcNoart= (lcQuantityDet/lcIntegralQty)+1;
											else
												lcNoart= lcQuantityDet/lcIntegralQty;
									 }
									 else
									 {
										 lcNoart=lcQuantityDet;
									 }
							  }
							  
							  
						}
						
						sql="update receipt_backflush set no_art = ? where tran_id = ?";
						pstmt=conn.prepareStatement(sql);
						pstmt.setDouble(1, lcNoart);
						pstmt.setString(2, tranID);
						
						upcnt=pstmt.executeUpdate();
						System.out.println("Row count for receipt_backflush table (no_art)--->>["+upcnt+"]");
						if(pstmt !=null)
						{
							pstmt.close();
							pstmt=null;
						}
					}
						
					
				}// //@@@@@@@@@@@@@@@@@@@@@@@	
				sql="select nvl(capacity,0) from location where loc_code = ?";
				pstmt=conn.prepareStatement(sql);
				pstmt.setString(1, lsLocCode);
				rs=pstmt.executeQuery();
				if(rs.next())
					lcCapacity=rs.getDouble(1);
				System.out.println("lcCapacity------>>["+lcCapacity+" lcQuantityHdr----->>["+lcQuantityHdr+"]");
				if(rs != null)
				{
					rs.close();
					rs=null;
				}
				if(pstmt !=null)
				{
					pstmt.close();
					pstmt=null;
				}
				if(lcQuantityHdr > lcCapacity)
				{
					retString =  itmDBAccessLocal.getErrorString("","VTCAPACITY","","",conn);				
					return retString;
				}
				
				
			}//$$$$$$$$
			
			System.out.println("lsBackflushType or condition-------->>"+lsBackflushType);	
			System.out.println("lcQuantityHdr -------->>"+lcQuantityHdr);
			System.out.println("retString -------->>"+retString);
			if (retString.trim().length() == 0)
			{
				if(lcQuantityHdr > 0)
				{			
					if(lsBackflushType.trim().equalsIgnoreCase("C") || lsBackflushType.trim().equalsIgnoreCase("S") )
					{
						sql="select min(mfg_date), min(exp_date) from stock a ,receipt_backflush_det b where" +
								" a.item_code = b.item_code and a.site_code = b.site_code and a.loc_code = b.loc_code" +
								" and a.lot_no = b.lot_no and a.lot_sl = b.lot_sl and b.tran_id = ? ";
						
						pstmt=conn.prepareStatement(sql);
						pstmt.setString(1, tranID);
						rs=pstmt.executeQuery();
						if(rs.next())
						{
							ldtMfgdate=rs.getTimestamp(1);
							ldtExpirydate=rs.getTimestamp(2);
						}
						System.out.println("ldtMfgdate--->>["+ldtMfgdate.toString()+"]");
						if(rs != null)
						{
							rs.close();
							rs=null;
						}
						if(pstmt !=null)
						{
							pstmt.close();
							pstmt=null;
						}
						sql="update receipt_backflush set mfg_date = ?,exp_date = ? where tran_id = ?";
						pstmt=conn.prepareStatement(sql);
						pstmt.setTimestamp(1, ldtMfgdate);
						pstmt.setTimestamp(2, ldtExpirydate);
						pstmt.setString(3, tranID);
						upcnt=pstmt.executeUpdate();
						System.out.println("No. of rows updated for table receipt_backflush (MFD date and exp date).."+upcnt);
						if(pstmt !=null)
						{
							pstmt.close();
							pstmt=null;
						}
						if(lsBackflushType.equalsIgnoreCase("C"))
						{
							sql="select lot_no from receipt_backflush_det where tran_id = ?";
							pstmt=conn.prepareStatement(sql);
							pstmt.setString(1, tranID);
							rs=pstmt.executeQuery();
							if(rs.next())
								lsLotNo=rs.getString(1)==null ? "" : rs.getString(1).trim();
							System.out.println("lsLotNo------->>"+lsLotNo);
							if(rs != null)
							{
								rs.close();
								rs=null;
							}
							if(pstmt !=null)
							{
								pstmt.close();
								pstmt=null;
							}
							
							sql="update receipt_backflush set lot_no = ? where tran_id = ?";
							pstmt=conn.prepareStatement(sql);
							pstmt.setString(1, lsLotNo);
							pstmt.setString(2,tranID);
							
							upcnt=pstmt.executeUpdate();
							System.out.println("No of rows updated in receipt_backflush table (lot_no)-->>["+upcnt+"]");
							if(pstmt !=null)
							{
								pstmt.close();
								pstmt=null;
							}
						}
						else if(lsBackflushType.equalsIgnoreCase("S"))
						{
							ldtMfgdate=null;
							ldtExpirydate=null;
							sql="update receipt_backflush set mfg_date = ?,exp_date = ? where tran_id = ?";
							pstmt=conn.prepareStatement(sql);
							pstmt.setTimestamp(1, ldtMfgdate);
							pstmt.setTimestamp(2, ldtExpirydate);
							pstmt.setString(3, tranID);
							upcnt=pstmt.executeUpdate();
							System.out.println("No. of rows updated for table receipt_backflush (MFD date and exp date).."+upcnt);
							if(pstmt !=null)
							{
								pstmt.close();
								pstmt=null;
							}
						}
						
						
					}
				}
		}
			System.out.println("retString---->>["+retString+"] lsQcreqd---->>["+lsQcreqd+"]");
			if( retString.trim().length()== 0 && lsQcreqd.equalsIgnoreCase("Y"))
			{
				// Generate QC Order
				// private String createQCOrder(String tranId,String rcpSiteCode,String rcpBackflushType,String xtraParm,Connection conn) throws ITMException
				retString=createQCOrder(tranID,lsLotNo,ldtExpirydate,xtraParams,conn);
				System.out.println("retString after createQCOrder return------->>["+retString+"]");
				if(retString !=null && retString.trim().length() > 0)
				{
					conn.rollback();
					return retString;
				}
			}
			if(lsBackflushType.trim().equalsIgnoreCase("C"))
			{
				// Do not change lot no
			}
			else if(lsBackflushType.trim().equalsIgnoreCase("S"))
			{
				sql="update receipt_backflush set lot_no = ? where tran_id = ?";
				pstmt=conn.prepareStatement(sql);
				pstmt.setString(1, lsLotNo);			
				pstmt.setString(2, tranID);
				upcnt=pstmt.executeUpdate();
				System.out.println("No. of rows updated for table receipt_backflush (lot_no).."+upcnt);
				if(pstmt !=null)
				{
					pstmt.close();
					pstmt=null;
				}
				
			}
			
			// validate that header and detail amount matches
			// otherwise inventory accounting will go wrong
			sql="select sum(net_amt) from receipt_backflush_det where tran_id = ? ";
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, tranID);
			rs=pstmt.executeQuery();
			if(rs.next())
				lcDetamt=rs.getDouble(1);
			System.out.println("lcDetamt--------->>"+lcDetamt);
			if(rs != null)
			{
				rs.close();
				rs=null;
			}
			if(pstmt !=null)
			{
				pstmt.close();
				pstmt=null;
			}
			sql="select net_amt from receipt_backflush where tran_id = ?";
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, tranID);
			rs=pstmt.executeQuery();
			if(rs.next())
				lcHdramt=rs.getDouble(1);
			System.out.println("lcHdramt ----------->>"+lcHdramt);
			
			if(lcDetamt != lcHdramt)
			{
				//Header / detail amt. mismatch
				retString =  itmDBAccessLocal.getErrorString("","VTPOST2","","",conn);				
				return retString;
			}
			//Confirmation for BackFlush 
			retString=confirmBackflush(tranID,tranSer,xtraParams,conn);
			System.out.println("after confirmBackflush retString ---->["+retString+"]");
			if(retString.equalsIgnoreCase("Error"))
			{
				retString = itmDBAccessLocal.getErrorString("","VTDESNCONF","","",conn);
				conn.rollback();
				return retString;
			}
			System.out.println("after confirmation lsProjCode ---->["+lsProjCode+"]");
			
			if (retString.trim().length()==0 && lsProjCode.trim().length() > 0)
			{
				String chgUser= "",chgTerm="";
				chgUser=GenericUtility.getInstance().getValueFromXTRA_PARAMS(xtraParams,"userId");
				chgTerm= GenericUtility.getInstance().getValueFromXTRA_PARAMS(xtraParams,"termId");
				System.out.println("Change User---------->>["+chgUser+"]");
				System.out.println("Change Term---------->>["+chgTerm+"]");
				sql="select ind_no from  project where proj_code = ?";
				pstmt=conn.prepareStatement(sql);
				pstmt.setString(1, lsProjCode);
				rs=pstmt.executeQuery();
				if(rs.next())				
					lsIndno=rs.getString(1)==null ? "" : rs.getString(1);
				
				System.out.println("lsIndno------->>["+lsIndno+"]");				
				if(rs != null)
				{
					rs.close();
					rs=null;
				}
				if(pstmt !=null)
				{
					pstmt.close();
					pstmt=null;
				}
				if(lsIndno.trim().length() > 0)
				{
					sql="update indent set status = ?, status_date = ?,chg_date = ?,chg_user = ?,chg_term = ? where ind_no = ?";
					pstmt=conn.prepareStatement(sql);
					pstmt.setString(1, "C");
					pstmt.setDate(2, new java.sql.Date(System.currentTimeMillis()));
					pstmt.setDate(3, new java.sql.Date(System.currentTimeMillis()));
					pstmt.setString(4, chgUser);
					pstmt.setString(5, chgTerm);
					pstmt.setString(5, lsIndno);
					
				    upcnt=pstmt.executeUpdate();
				    System.out.println("No. of rows updated for indent table --------->>["+upcnt+"]");
				    if(pstmt !=null)
				    {
				    	pstmt.close();
				    	pstmt=null;
				    }
					
				 }
				System.out.println("lsProjCode------>>+["+lsProjCode+"]");
				System.out.println("lcQuantityHdr------>>+["+lcQuantityHdr+"]");
				 if (retString.trim().length()==0 && lsProjCode != null && lsProjCode.trim().length() > 0)
				 {
					sql="update project set quantity = ? where proj_code = ?";
					pstmt=conn.prepareStatement(sql);
					pstmt.setDouble(1, lcQuantityHdr);
					pstmt.setString(2,lsProjCode);
					upcnt=pstmt.executeUpdate();
				    System.out.println("No. of rows updated for project table --------->>["+upcnt+"]");
				    if(pstmt !=null)
				    {
				    	pstmt.close();
				    	pstmt=null;
				    }
				 }
			
			}
			
			if (retString.trim().length()==0)
			{
				sql="update stock set lot_sl__org = ? where site_code = ? and loc_code = ? and item_code = ? and " +
						"lot_no = ? and lot_sl = ?";
				pstmt=conn.prepareStatement(sql);	
				pstmt.setString(1, lsLotsl);
				pstmt.setString(2, lsSiteCode);
				pstmt.setString(3, locCode);
				pstmt.setString(4, lsItemCode);
				pstmt.setString(5, lotNo);
				pstmt.setString(6, lsLotsl);
				upcnt=pstmt.executeUpdate();				    
			    System.out.println("No. of rows updated for stock table --------->>["+upcnt+"]");
			    if(pstmt !=null)
			    {
			    	pstmt.close();
			    	pstmt=null;
			    }			    
			    
			}
			if (retString.trim().length()==0)
			{
				// 26/12/13 manoharan MF2ESUN005 
				userId = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginCode");  
				sql = "SELECT EMP_CODE FROM USERS WHERE CODE = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, userId);
				rs = pstmt.executeQuery();
				if ( rs.next() )
				{
					empCode = rs.getString("EMP_CODE");
				}	
				pstmt.close();
				pstmt = null;					
				rs.close();
				rs = null;
				
				//sql="update receipt_backflush set confirmed = ?,conf_date = ? where tran_id = ?";
				sql="update receipt_backflush set confirmed = ?,conf_date = ?, EMP_CODE__APRV = ? where tran_id = ?";
				pstmt=conn.prepareStatement(sql);
				pstmt.setString(1, "Y");
				pstmt.setDate(2, new java.sql.Date(System.currentTimeMillis()));
				pstmt.setString(3, empCode);
				pstmt.setString(4, tranID);
				upcnt=pstmt.executeUpdate();				    
			    System.out.println("No. of rows updated for receipt_backflush table --------->>["+upcnt+"]");
			    if(upcnt > 0)
			    {                 
					// conn.commit();
                    //retString = itmDBAccessEJB.getErrorString("","VTDIST30","");			    	
                    System.out.println("ReceiptBackflush Confirmed .."+retString);
                }
			    if(pstmt !=null)
			    {
			    	pstmt.close();
			    	pstmt=null;
			    }		
				
			}
			
			System.out.println("retString ----------->>["+retString+"]");
			if (retString.trim().length() > 0)
			{
				System.out.println("transaction is rollbacking...................");
				conn.rollback();				
			}			
			
		}
		catch(Exception e)
		{
			System.out.println("Exception in retrieveReceipt method-------------["+e.getMessage()+"]");
			e.printStackTrace();
			return "Error";				
			
		}
		return retString;

	}
	
	private String createQCOrder(String tranID,String lsLotNo,java.sql.Timestamp ldtExpirydate,String xtraParams,Connection conn)
	{
		System.out.println("------in createQCOrder method-----------");
		 PreparedStatement pstmt=null;
		 ResultSet rs=null;
		 String lsWin = "w_qcorder_new",sql = "",errString="", lsQcno="",lsSitecode="",lsItemcode="";
	     String lsBatchno="",lsLotsl="",lsUnit="",lsLoccode="", lsKeystr="",lsAprv="",lsRej="",lsSuppCodeMfg="";
	     String lsBackflushType="",lsQorderType="", lsItemser="", lsSiteser="",asLotno="",lsTrackShelfLife="";
	     String lsSuppCode="",lsItemCodeDet="",lsLotnoDet="",lsLocCodedet="",lsLotslDet="",lsRouteCode="",currDate="";
	     String remarks="",invTranId="",lockCode="";
	     double lcQty=0,llSelectedrow=0;
	     int llQcLeadTime=0,llCount=0;
	     int insInvCnt=0,lineNoInv=0,insInvDetCnt=0;
	     DistCommon distCommon= new DistCommon(); 
	     java.sql.Timestamp ldtMfgdate=null,ldtMfgdt=null;
	     java.sql.Timestamp ldtRetestdt=null; java.util.Date ldtQcduedate=null;
	     GenericUtility genericUtility = null;
	     SimpleDateFormat sdf=null;	    
	     StringBuffer xmlValues=new StringBuffer("<?xml version=\"1.0\" encoding=\"utf-8\"?><Root>"); 
	    	try
	    	{
	    		sql="select site_code,item_code,quantity,unit,loc_code,lot_no,lot_sl,backflush_type," +
	    				"mfg_date from receipt_backflush where tran_id = ?";
	    		pstmt=conn.prepareStatement(sql);
	    		pstmt.setString(1,tranID);
	    		rs=pstmt.executeQuery();
	    		if(rs.next())
	    		{
	    			lsSitecode=rs.getString(1)== null ? "" :rs.getString(1).trim();
	    			lsItemcode=rs.getString(2)== null ? "" :rs.getString(2).trim();
	    			lcQty=rs.getDouble(3);
	    			lsUnit=rs.getString(4)== null ? "" :rs.getString(4).trim(); 
	    			lsLoccode=rs.getString(5)== null ? "" :rs.getString(5).trim();	    					
	    			asLotno=rs.getString(6)== null ? "" :rs.getString(6).trim();
	    			lsLotsl=rs.getString(7)== null ? "" :rs.getString(7).trim();
	    			lsBackflushType=rs.getString(8)== null ? "" :rs.getString(8).trim();
	    			ldtMfgdate=rs.getTimestamp(9);
	    		}
	    		System.out.println("lsSitecode---->>["+lsSitecode+"] lsItemcode--->>"+lsItemcode+"]");
	    		System.out.println("lsBackflushType---->>["+lsBackflushType+"] lsLoccode--->>"+lsLoccode+"]");
	    		if(rs != null)
				{
					rs.close();
					rs=null;
				}
				if(pstmt !=null)
				{
					pstmt.close();
					pstmt=null;
				}
	    		sql="select track_shelf_life from item where item_code = ?";
	    		pstmt=conn.prepareStatement(sql);
	    		pstmt.setString(1,lsItemcode);
	    		rs=pstmt.executeQuery();
	    		if(rs.next())
	    		{
	    			lsTrackShelfLife=rs.getString(1) ==null ? "" : rs.getString(1).trim();
	    		}
	    		System.out.println("lsTrackShelfLife---->>["+lsTrackShelfLife+"]");
	    		if(rs != null)
				{
					rs.close();
					rs=null;
				}
				if(pstmt !=null)
				{
					pstmt.close();
					pstmt=null;
				}
				
				if(lsBackflushType.trim().equalsIgnoreCase("C") && lsTrackShelfLife.trim().equalsIgnoreCase("Y"))
				{
					sql="select min(retest_date) from stock a ,receipt_backflush_det b where a.item_code = b.item_code " +
							"and a.site_code = b.site_code and a.loc_code = b.loc_code and a.lot_no = b.lot_no " +
							"and a.lot_sl = b.lot_sl and b.tran_id 	= ? and	a.retest_date is not null";
					pstmt=conn.prepareStatement(sql);
		    		pstmt.setString(1,tranID);
		    		rs=pstmt.executeQuery();		    		
		    		if(rs.next())
		    		{
		    			ldtRetestdt=rs.getTimestamp(1);
		    		}
		    		if(rs != null)
					{
						rs.close();
						rs=null;
					}
					if(pstmt !=null)
					{
						pstmt.close();
						pstmt=null;
					}
				}
				
				sql="select case when qc_lead_time is null then 0 else qc_lead_time end,item_ser " +
						"from item where item_code = ?";
				pstmt=conn.prepareStatement(sql);
	    		pstmt.setString(1,lsItemcode);
	    		rs=pstmt.executeQuery();
	    		if(rs.next())
	    		{
	    			llQcLeadTime=rs.getInt(1);
	    			lsItemser=rs.getString(2) ==null ? "" : rs.getString(2).trim();
	    		}
	    		System.out.println("llQcLeadTime---->>["+llQcLeadTime+"] lsItemser----->>["+lsItemser+"]");
	    		if(rs.next())
	    		{
	    			ldtRetestdt=rs.getTimestamp(1);
	    		}
	    		if(rs != null)
				{
					rs.close();
					rs=null;
				}
				if(pstmt !=null)
				{
					pstmt.close();
					pstmt=null;
				}
	    		ldtQcduedate=getRelativeDate(new java.util.Date(System.currentTimeMillis()),llQcLeadTime);
	    		sql="select item_ser from siteitem where site_code= ? and item_code = ?";
	    		pstmt=conn.prepareStatement(sql);
	    		pstmt.setString(1,lsSitecode);
	    		pstmt.setString(2,lsItemcode);
	    		rs=pstmt.executeQuery();
	    		if(rs.next())
	    		{
	    			lsSiteser=rs.getString(1)==null ? "" :rs.getString(1).trim();
	    		}
	    		System.out.println("lsSiteser---->>["+lsSiteser+"]");
	    		if(lsSiteser.trim().length() > 0)
	    			lsItemser = lsSiteser;
                
	    		if(rs != null)
				{
					rs.close();
					rs=null;
				}
				if(pstmt !=null)
				{
					pstmt.close();
					pstmt=null;
				}
	    		System.out.println("lsSiteser------->>["+lsSiteser+"]");
	    		sql="select key_string from transetup where tran_window = ?";
	    		pstmt=conn.prepareStatement(sql);
	    		pstmt.setString(1,lsWin);
	    		rs=pstmt.executeQuery();
	    		if(rs.next())
	    		{
	    			lsKeystr=rs.getString(1)==null ? "" :rs.getString(1).trim();
	    		}
	    		System.out.println("lsKeystr-------->>["+lsKeystr+"]");
	    		if(rs != null)
				{
					rs.close();
					rs=null;
				}
				if(pstmt !=null)
				{
					pstmt.close();
					pstmt=null;
				}
	    		if(lsKeystr.trim().length() == 0)
	    		{
	    			sql="select key_string from transetup where tran_window = ?";//GENERAL
	    			pstmt=conn.prepareStatement(sql);
		    		pstmt.setString(1,"GENERAL");
		    		rs=pstmt.executeQuery();
		    		if(rs.next())
		    		{
		    			lsKeystr=rs.getString(1)==null ? "" :rs.getString(1).trim();
		    		}
		    		System.out.println("lsKeystr---@@@----->>["+lsKeystr+"]");
		    		if(rs != null)
					{
						rs.close();
						rs=null;
					}
					if(pstmt !=null)
					{
						pstmt.close();
						pstmt=null;
					}
	    		}
	    		System.out.println("lsBackflushType------->>["+lsBackflushType+"]");
	    		if(lsBackflushType.trim().equalsIgnoreCase("S"))
	    		{
	    			lsQorderType = "I";
	    			//asLotno = lsQcno;
	    		}
	    		else
	    			lsQorderType = "S";
	    		
	    		 genericUtility = new GenericUtility().getInstance();
	    		sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
	            currDate = sdf.format(new java.sql.Date(System.currentTimeMillis()));
	    		xmlValues.append("<Header></Header>");
	    		xmlValues.append("<Detail1>");
	    		xmlValues.append("<tran_id></tran_id>");
	    		xmlValues.append("<site_code>"+lsSitecode+"</site_code>");
	    		xmlValues.append("<qorder_type>"+ lsQorderType + "</qorder_type>");
	    		xmlValues.append("<qorder_date>"+currDate+"</qorder_date>");
	    		
	    		xmlValues.append("<lot_no>"+asLotno+"</lot_no>");
                xmlValues.append("<item_ser>"+lsItemser+"</item_ser>");
                xmlValues.append("</Detail1></Root>");
                String userId = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginEmpCode");
                TransIDGenerator tg1 = new TransIDGenerator(xmlValues.toString(), userId, CommonConstants.DB_NAME);
                lsQcno = tg1.generateTranSeqID("QC-ORD", "qorder_no", lsKeystr, conn);
                System.out.println("lsQcno---------->>["+lsQcno+"] loginCode------>>["+userId+"]");
                if(lsQcno.indexOf("Error") != -1)
                {    
                	ITMDBAccessEJB itmDBAccessLocal = new ITMDBAccessEJB();            	
					errString =  itmDBAccessLocal.getErrorString("","VTTRANID","","",conn);					
                    return errString; 
                }
	    		if(lsBackflushType.trim().equalsIgnoreCase("S"))
	    		{
	    			asLotno = lsQcno;
	    		}
	    		if(lsBackflushType.trim().equalsIgnoreCase("C"))
	    		{
	    			sql="select item_code ,lot_no,loc_code,lot_sl from receipt_backflush_det where  tran_id = ?";
	    			pstmt=conn.prepareStatement(sql);
		    		pstmt.setString(1,tranID);
		    		rs=pstmt.executeQuery();
		    		if(rs.next())
		    		{
		    			lsItemCodeDet=rs.getString(1)==null ? "" : rs.getString(1).trim();
		    			lsLotnoDet=rs.getString(2)==null ? "" : rs.getString(2).trim();
		    			lsLocCodedet=rs.getString(3)==null ? "" : rs.getString(3).trim();
		    			lsLotslDet=rs.getString(4)==null ? "" : rs.getString(4).trim();
		    		}
		    		System.out.println("lsItemCodeDet--->>["+lsItemCodeDet+"] lsLocCodedet-->>["+lsLocCodedet+"]");
		    		if(rs != null)
					{
						rs.close();
						rs=null;
					}
					if(pstmt !=null)
					{
						pstmt.close();
						pstmt=null;
					}
					
					sql="select count(*) from item_lot_info where item_code = ?";
					pstmt=conn.prepareStatement(sql);
		    		pstmt.setString(1,lsItemCodeDet);
		    		rs=pstmt.executeQuery();
		    		if(rs.next())
		    		{
		    			llCount=rs.getInt(1);
		    		}
		    		System.out.println("llCount-------->>["+llCount+"]");
		    		if(rs != null)
					{
						rs.close();
						rs=null;
					}
					if(pstmt !=null)
					{
						pstmt.close();
						pstmt=null;
					}
					
		    		if(llCount > 0)
		    		{
		    			sql="select mfg_date,supp_code__mfg,supp_code from item_lot_info where item_code = ? and lot_no = ?";
		    			pstmt=conn.prepareStatement(sql);
			    		pstmt.setString(1,lsItemCodeDet);
			    		pstmt.setString(2,lsLotnoDet);
			    		rs=pstmt.executeQuery();
			    		if(rs.next())
			    		{
			    			ldtMfgdt=rs.getTimestamp(1);
			    			lsSuppCodeMfg=rs.getString(2)== null ? "" : rs.getString(2).trim();
			    			lsSuppCode=rs.getString(3)== null ? "" : rs.getString(2).trim();
			    		}
			    		if(rs != null)
						{
							rs.close();
							rs=null;
						}
						if(pstmt !=null)
						{
							pstmt.close();
							pstmt=null;
						}
						if(ldtMfgdt==null && lsSuppCodeMfg==null)
						{
							sql="select mfg_date,supp_code__mfg from stock where item_code = ? and site_code = ? and " +
									"loc_code = ? and lot_no = ? and lot_no = ?";
							pstmt=conn.prepareStatement(sql);
				    		pstmt.setString(1,lsItemCodeDet);
				    		pstmt.setString(2,lsSitecode);
				    		pstmt.setString(3,lsLocCodedet);
				    		pstmt.setString(4,lsLotnoDet);
				    		pstmt.setString(5,lsLotnoDet);
				    		rs=pstmt.executeQuery();
				    		if(rs.next())
				    		{
				    			ldtMfgdt=rs.getTimestamp(1);
				    			lsSuppCodeMfg=rs.getString(2)== null ? "" : rs.getString(2).trim();;				    			
				    		}
				    		if(rs != null)
							{
								rs.close();
								rs=null;
							}
							if(pstmt !=null)
							{
								pstmt.close();
								pstmt=null;
							}
							System.out.println("ldtMfgdt-------->>["+ldtMfgdt.toString()+"] lsSuppCodeMfg--->>["+lsSuppCodeMfg+"]");							
						}
						else if(ldtMfgdt==null)
						{
							sql="select mfg_date from stock where item_code = ? and site_code = ? and " +
									"loc_code = ? and lot_no = ? and lot_no = ?";
							pstmt=conn.prepareStatement(sql);
				    		pstmt.setString(1,lsItemCodeDet);
				    		pstmt.setString(2,lsSitecode);
				    		pstmt.setString(3,lsLocCodedet);
				    		pstmt.setString(4,lsLotnoDet);
				    		pstmt.setString(5,lsLotnoDet);
				    		rs=pstmt.executeQuery();
				    		if(rs.next())
				    		{
				    			ldtMfgdt=rs.getTimestamp(1);				    					    			
				    		}
				    		System.out.println("ldtMfgdt-------->>["+ldtMfgdt.toString()+"]");
				    		if(rs != null)
							{
								rs.close();
								rs=null;
							}
							if(pstmt !=null)
							{
								pstmt.close();
								pstmt=null;
							}
						}
						else if(lsSuppCodeMfg==null)
						{
							sql="select supp_code__mfg from stock where item_code = ? and site_code = ? and " +
									"loc_code = ? and lot_no = ? and lot_no = ?";
							pstmt=conn.prepareStatement(sql);
				    		pstmt.setString(1,lsItemCodeDet);
				    		pstmt.setString(2,lsSitecode);
				    		pstmt.setString(3,lsLocCodedet);
				    		pstmt.setString(4,lsLotnoDet);
				    		pstmt.setString(5,lsLotnoDet);
				    		rs=pstmt.executeQuery();
				    		if(rs.next())
				    		{				    			
				    			lsSuppCodeMfg=rs.getString(1) == null ? "" : rs.getString(1).trim();				    			
				    		}
				    		System.out.println("ldtMfgdt-------->>["+ldtMfgdt.toString()+"]");
				    		if(rs != null)
							{
								rs.close();
								rs=null;
							}
							if(pstmt !=null)
							{
								pstmt.close();
								pstmt=null;
							}
						}
						
			    		
		    		}//count condition
		    		else
		    		{
		    			sql="select mfg_date from stock where item_code = ? and site_code = ? and " +
								"loc_code = ? and lot_no = ? and lot_no = ?";
						pstmt=conn.prepareStatement(sql);
			    		pstmt.setString(1,lsItemCodeDet);
			    		pstmt.setString(2,lsSitecode);
			    		pstmt.setString(3,lsLocCodedet);
			    		pstmt.setString(4,lsLotnoDet);
			    		pstmt.setString(5,lsLotnoDet);
			    		rs=pstmt.executeQuery();
			    		if(rs.next())
			    		{
			    			ldtMfgdt=rs.getTimestamp(1);				    					    			
			    		}
			    		System.out.println("ldtMfgdt-------->>["+ldtMfgdt.toString()+"]");
			    		if(rs != null)
						{
							rs.close();
							rs=null;
						}
						if(pstmt !=null)
						{
							pstmt.close();
							pstmt=null;
						}
		    		}
	    		}
	    		else
	    		{
	    			ldtMfgdt = ldtMfgdate;
	    		}
	    		
	    		 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, batch_no,expiry_date," +
	                        "loc_code__aprv,loc_code__rej,unit__sample,lot_no__new,retest_date,emp_code,item_code__new," +
	                        "mfg_date,supp_code, supp_code__mfg) " +
	                        "values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	    		 
	    		 pstmt=conn.prepareStatement(sql);
	    		 pstmt.setString(1, lsQcno);
	    		 pstmt.setString(2, lsQorderType);
	    		 pstmt.setTimestamp(3,new Timestamp(System.currentTimeMillis()));
	    		 pstmt.setString(4, lsSitecode);
	    		 pstmt.setString(5, lsItemcode);
	    		 pstmt.setString(6, lsRouteCode);
	    		 pstmt.setDouble(7, lcQty);
	    		 pstmt.setDouble(8,0);
	             pstmt.setDouble(9,0);
	    		 pstmt.setTimestamp(10,new Timestamp(System.currentTimeMillis()));
	    		 pstmt.setTimestamp(11,new Timestamp(System.currentTimeMillis()));
	    		 pstmt.setTimestamp(12,new Timestamp(System.currentTimeMillis()));
	    		 pstmt.setString(13, tranID);
	    		 pstmt.setString(14, "1");
	    		 pstmt.setString(15, asLotno);
	    		 pstmt.setString(16, lsLotsl);
	    		 pstmt.setTimestamp(17,new Timestamp(System.currentTimeMillis()));
	    		 pstmt.setString(18,"SYSTEM");
	             pstmt.setString(19,"SYSTEM");
	             pstmt.setString(20, lsLoccode);
	             pstmt.setDouble(21,0);
	             pstmt.setString(22,"U");
	             pstmt.setString(23,lsUnit);
	             pstmt.setString(24,"T");
	             pstmt.setString(25,lsBatchno);
	    		 pstmt.setTimestamp(26, ldtExpirydate);
	    		 pstmt.setString(27,lsAprv);
	    		 pstmt.setString(28,lsRej);
	    		 pstmt.setString(29,lsUnit);
	    		 pstmt.setString(30,asLotno);
	    		 pstmt.setTimestamp(31,ldtRetestdt);
	    		 pstmt.setString(32,userId);
	             pstmt.setString(33,lsItemcode);
	             pstmt.setTimestamp(34,ldtMfgdate);
	             pstmt.setString(35,lsSuppCode);
	             pstmt.setString(36,lsSuppCodeMfg);
	    		 
	    		 int insCount=pstmt.executeUpdate();
	    		 System.out.println("Insert count for Qc order------>>["+insCount+"]");	
	    		 pstmt.close();
	    		 pstmt=null;
	    		 
	    		 //Added by Sagar on 06/10/14 Start...
	    		 lockCode= distCommon.getDisparams("999999", "QUARNTINE_LOCKCODE", conn);
	    		 System.out.println(">>>>> getDisparams lockCode:"+lockCode);
	    		 if(insCount > 0 && lockCode!=null && lockCode.trim().length() > 0 )
	    		 {
	    			 invTranId = generateTranId( "w_inv_hold", lsSitecode, conn );
	    			 System.out.println(">>>>Generate invTranId:"+invTranId);
	    			 sql = "insert into inv_hold (tran_id, tran_date, site_code, remarks, confirmed, chg_date, chg_user, chg_term, lock_code) "
								+ " values	(?,?,?,?,?,?,?,?,?) " ;
				     remarks = "Auto generated from Receipt Backflush Confirmation";
				     pstmt = conn.prepareStatement(sql);
				     pstmt.setString(1, invTranId);
				     pstmt.setTimestamp(2,new Timestamp(System.currentTimeMillis()));
				     pstmt.setString(3, lsSitecode);
				     pstmt.setString(4, remarks);
				     pstmt.setString(5, "N");
				     pstmt.setTimestamp(6, new Timestamp(System.currentTimeMillis()));
		    		 pstmt.setString(7, "SYSTEM");
		             pstmt.setString(8, "SYSTEM");
		             pstmt.setString(9, lockCode);
		             insInvCnt = pstmt.executeUpdate();
			         pstmt.close();
			         pstmt = null;	
			         System.out.println(">>>>>>>>Record inserted in inv_hold:"+insInvCnt);
			      
			         if(insInvCnt > 0)
			         {
			        	 lineNoInv++;
			        	 System.out.println(">>>>>>>>>Inv hold det lineNoInv:"+lineNoInv);
				         sql = "insert into inv_hold_det (tran_id, line_no, item_code, site_code,loc_code, lot_no, lot_sl, remarks,	hold_status) "
									+ " values	(?,?,?,?,?,?,?,?,?) ";
				         pstmt = conn.prepareStatement(sql);
				         pstmt.setString(1, invTranId);
				         pstmt.setInt(2, lineNoInv);
				         pstmt.setString(3, lsItemcode);
				         pstmt.setString(4, lsSitecode);
				         pstmt.setString(5, lsLoccode);
				         pstmt.setString(6, asLotno);
				         pstmt.setString(7, lsLotsl);
				         pstmt.setString(8, remarks);
				         pstmt.setString(9, "H");
				         insInvDetCnt = pstmt.executeUpdate();
						 pstmt.close();
						 pstmt = null;		
			         }
	    		 }
	    		//Added by Sagar on 06/10/14 End...
	    		
	    	} //end try
	    	catch(Exception e)
	    	{
	    		System.out.println("Exception: @@@@@@@createQCOrder@@@@@@@ :["+e.getMessage()+"]");
	    		errString=e.getMessage();	    		
	    		e.printStackTrace();
	    	}
	        
	        return errString;
	 }
	 private java.util.Date getRelativeDate(java.util.Date currDate,int leadTime )throws ITMException
	 {
	        java.util.Date relativeDate = null;
	        try{
	            Calendar cal = Calendar.getInstance();
	            cal.setTime(currDate);
	            cal.add(Calendar.DATE,leadTime);
	            relativeDate = cal.getTime();            
	        }catch(Exception e){
	            System.out.println("Exception getRelativeDate : "+e);
	            e.printStackTrace();   
	            throw new ITMException(e);
	        }
	        return relativeDate;
	}
	private String confirmBackflush(String tranID,String tranSer,String xtraParams,Connection conn)
	{
		ResultSet rs=null,rs1=null;
		PreparedStatement pstmt=null,pstmt1=null;
		ITMDBAccessEJB itmDBAccessLocal1 = new ITMDBAccessEJB();
		String errorCode="",sql="",lsBackflushtype= "",lsItemcodehdr="",lsSitecode="",lsLoccode="",lsLotno="";
		String lsLotsl="",lsTranType="",lsAcctcodehdr="",lsCctrcodehdr="",lsItemser="",lsUnit="",lsLineno="";
		String lsItemcode="",lsGrade="",lsBatchNo="",lsAcctcodedet="",lsCctrcodedet="",lsSuppcodeMfg="";
		String lsItemStru="",lsAcctcodedr="",lsCctrcodedr="",lsAcctcodecr="",lsCctrcodecr="",lsUnitalt="",lsQcreqd="";
		String lsSiteCodeMfg="",lsPackCode="",lsDimension="",lsItemCodeDet="",lsLotNoDet="",lsLocCodedet="",lsLotSlDet="",lsShelfLifeType=""; 
		double lcQuantityHdr=0,lcDetamt=0,lcHdramt=0,lcQuantityDet=0,lcDetrate=0,lcAmount=0,lcGrossamount=0,lcInvValue=0,lcNoArt=0;;
		double lcRate=0,lcGrossrate=0,lcPotencyPerc=0,lcConvqtystduom=0,lcGrossWeight=0, lcTareWeight=0,lcNetWeight=0,lcCapacity=0;
		java.sql.Timestamp ldtTrandate=null,ldtRetestDate=null,ldtMfgDate=null,ldtExpiryDate=null;
		boolean isRecordFound=false;
		InvAcct invAcct = new InvAcct();
		int countDet=0;
		HashMap stkUpdstk=new HashMap();
		try 
		{
			sql="select (case when backflush_type is null then ' ' else backflush_type end)," +
					"item_code,tran_date, quantity,site_code,loc_code,lot_no,lot_sl,tran_type" +
					" from receipt_backflush where tran_id = ?";
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, tranID);
			rs=pstmt.executeQuery();
			if(rs.next())
			{
				lsBackflushtype=rs.getString(1);
				lsItemcodehdr=rs.getString(2);
				ldtTrandate=rs.getTimestamp(3);
				lcQuantityHdr=rs.getDouble(4);
				lsSitecode=rs.getString(5);
				lsLoccode=rs.getString(6);
				lsLotno=rs.getString(7);
				lsLotsl=rs.getString(8);
				lsTranType=rs.getString(9);
			}
			if(rs != null)
			{
				rs.close();
				rs=null;
			}
			if(pstmt !=null)
			{
				pstmt.close();
				pstmt=null;
			}
			
			sql="select sum(net_amt) from receipt_backflush_det where tran_id = ?";
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, tranID);
			rs=pstmt.executeQuery();
			if(rs.next())
				lcDetamt=rs.getDouble(1);
			System.out.println("lcDetamt--------->>"+lcDetamt);
			if(rs != null)
			{
				rs.close();
				rs=null;
			}
			if(pstmt !=null)
			{
				pstmt.close();
				pstmt=null;
			}
			sql="select net_amt from receipt_backflush where tran_id = ?";
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, tranID);
			rs=pstmt.executeQuery();
			if(rs.next())
				lcHdramt=rs.getDouble(1);
			System.out.println("lcHdramt ----------->>"+lcHdramt);
			if(rs != null)
			{
				rs.close();
				rs=null;
			}
			if(pstmt !=null)
			{
				pstmt.close();
				pstmt=null;
			}
			if(lcDetamt != lcHdramt)
			{
				//Header / detail amt. mismatch
				errorCode =  itmDBAccessLocal1.getErrorString("","VTPOST2","","",conn);				
				return errorCode;
			}
			
			sql="select item_ser from item where item_code = ?";
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, lsItemcodehdr);
			rs=pstmt.executeQuery();
			if(rs.next())
				lsItemser=rs.getString(1);
			System.out.println("lsItemser--------->>"+lsItemser);
			
			if(rs != null)
			{
				rs.close();
				rs=null;
			}
			if(pstmt !=null)
			{
				pstmt.close();
				pstmt=null;
			}
			sql="select acct_code__inv, cctr_code__inv from stock where item_code = ? and site_code = ? " +
					"and loc_code = ? and lot_no = ? and lot_sl = ?";
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, lsItemcodehdr);
			pstmt.setString(2, lsSitecode);
			pstmt.setString(3, lsLoccode);
			pstmt.setString(4, lsLotno);
			pstmt.setString(5, lsLotsl);
			rs=pstmt.executeQuery();
			if(rs.next())
			{
				lsAcctcodehdr=rs.getString(1) == null ? "" : rs.getString(1) ;
				lsCctrcodehdr=rs.getString(2) == null ? "" : rs.getString(2) ;				
			}
			if(rs != null)
			{
				rs.close();
				rs=null;
			}
			if(pstmt !=null)
			{
				pstmt.close();
				pstmt=null;
			}
			System.out.println("lcQuantityHdr-------->>["+lcQuantityHdr+"]");
			if(lcQuantityHdr < 0)
			{				
				errorCode =  itmDBAccessLocal1.getErrorString("","VXSTK2","","",conn);				
				return errorCode;				
			}
			else if(lcQuantityHdr >= 0)
			{				
				String code[]=new String[2];
				int i=0;
			
				lsCctrcodehdr=new FinCommon().getAcctDetrTtype(lsItemcodehdr,lsItemser, "IN",lsTranType, conn);
				System.out.println("return String from getAcctDetrTtype ----->>"+lsCctrcodehdr);
				
				for (String splitStr:lsCctrcodehdr.split(","))
				{
					code[i]=splitStr;
					 i++;
				}
				lsAcctcodehdr=code[0];
				lsCctrcodehdr=code[1];
				System.out.println("lsAcctcodehdr--@->>>["+lsAcctcodehdr+"] lsCctrcodehdr---->>>["+lsCctrcodehdr+"]");
				
				
			}
			sql="select line_no,item_code,site_code,loc_code,lot_no,lot_sl,quantity,unit,rate from " +
					"receipt_backflush_det where tran_id = ?";
			
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, tranID);
			rs=pstmt.executeQuery();
			int count=0;
			while(rs.next())
			{ 
				count++;
				System.out.println("in while of condition-------------count-->>["+count+"]");
				lsLineno=rs.getString(1) == null ? "" : rs.getString(1) ;
				lsItemcode=rs.getString(2) == null ? "" : rs.getString(2) ;
				lsSitecode=rs.getString(3) == null ? "" : rs.getString(3) ;
				lsLoccode=rs.getString(4) == null ? "" : rs.getString(4) ;
				lsLotno=rs.getString(5) == null ? "" : rs.getString(5) ;
				lsLotsl=rs.getString(6) == null ? "" : rs.getString(6) ;
				lcQuantityDet=rs.getDouble(7);
				lsUnit=rs.getString(8) == null ? "" : rs.getString(8) ;
				lcDetrate=rs.getDouble(9);					
			
				
				
				sql="select rate,gross_rate,grade,batch_no,no_art,retest_date,acct_code__inv,cctr_code__inv," +
						"potency_perc,supp_code__mfg from stock where item_code = ? and site_code = ? and " +
						"loc_code = ? and lot_no = ? and lot_sl = ?";
				
				pstmt1=conn.prepareStatement(sql);
				pstmt1.setString(1, lsItemcode);
				pstmt1.setString(2, lsSitecode);
				pstmt1.setString(3, lsLoccode);
				pstmt1.setString(4, lsLotno);
				pstmt1.setString(5, lsLotsl);
				
				rs1=pstmt1.executeQuery();
				if(rs1.next())
				{
					isRecordFound=true;
					lcRate=rs1.getDouble(1);
					lcGrossrate=rs1.getDouble(2);
					lsGrade=rs1.getString(3)== null ? "" : rs1.getString(3) ;
					lsBatchNo=rs1.getString(4) == null ? "" : rs1.getString(4) ;
					lcNoArt=rs1.getDouble(5);
					ldtRetestDate=rs1.getTimestamp(6);
					lsAcctcodedet=rs1.getString(7)== null ? "" : rs1.getString(7) ;
					lsCctrcodedet=rs1.getString(8)== null ? "" : rs1.getString(8) ;
					lcPotencyPerc=rs1.getDouble(9);
					lsSuppcodeMfg=rs1.getString(10)== null ? "" : rs1.getString(10) ;
				}
				if(rs1 != null)
				{
					rs1.close();
					rs1=null;
				}
				if(pstmt1 !=null)
				{
					pstmt1.close();
					pstmt1=null;
				}				
				
				System.out.println("lcQuantityDet---------->>["+lcQuantityDet+"]");
				if(lcQuantityDet > 0 && !(isRecordFound))
				{
					//Stock not available for the parameters specified
					errorCode =  itmDBAccessLocal1.getErrorString("","VXSTK2","","",conn);				
					return errorCode;
				}
				sql="select item_ser,item_stru from item where item_code= ?";
				pstmt1=conn.prepareStatement(sql);
				pstmt1.setString(1, lsItemcode);
				rs1=pstmt1.executeQuery();
				if(rs1.next()) //i got it
				{
					lsItemser=rs1.getString(1) ==null ? "" : rs1.getString(1);
					lsItemStru=rs1.getString(2) ==null ? "" : rs1.getString(2);
					lsAcctcodedr = lsAcctcodedet;
					lsCctrcodedr = lsCctrcodedet;
				}
				if(rs1 != null)
				{
					rs1.close();
					rs1=null;
				}
				if(pstmt1 !=null)
				{
					pstmt1.close();
					pstmt1=null;
				}
				
				if(lcQuantityDet < 0)
				{					
					lcRate = lcDetrate;
					lcGrossrate = lcDetrate;
					
					System.out.println("lsItemser---->>["+lsItemser+"] lsItemStru--->>["+lsItemStru+"]");
					String []code=new String[2];
					int i=0;
					lsCctrcodehdr=new FinCommon().getAcctDetrTtype(lsItemcodehdr,lsItemser, "IN",lsTranType, conn);
					System.out.println("Return String from getAcctDetrTtype-------->>["+lsCctrcodehdr+"]");
					for (String splitStr:lsCctrcodehdr.split(","))
					{
						code[i]=splitStr;
						 i++;
					}
					lsAcctcodecr=code[0];
					lsCctrcodecr=code[1];
					System.out.println("lsAcctcodecr--->>["+lsAcctcodecr+"] lsCctrcodecr---->>["+lsCctrcodecr+"]");
					if(lsCctrcodecr ==null || lsCctrcodecr.trim().length() ==0)
					{
						lsCctrcodecr = lsCctrcodedet;
					}
				}
				else
				{					
					lsAcctcodecr = lsAcctcodedet;
					lsCctrcodecr = lsCctrcodedet;
				}
				
				if(lsCctrcodedr ==null || lsCctrcodedr.trim().length() ==0 || lcQuantityHdr >= 0)
				{
					lsCctrcodedr = lsCctrcodecr;
				}
				else if ((lsCctrcodecr ==null || lsCctrcodecr.trim().length() == 0 ) && lcQuantityHdr < 0)
				{
					lsCctrcodecr = lsCctrcodedr;
				}
				
				lsGrade=lsGrade ==null ? "" : lsGrade;
				
				if (lcQuantityDet > 0)
				{					
					lcAmount += (lcRate * lcQuantityDet);
					lcGrossamount += (lcGrossrate * lcQuantityDet);
				}
				System.out.println("lcAmount------>>["+lcAmount+"] lcGrossamount---->>["+lcGrossamount+"]");
				lsBackflushtype=lsBackflushtype.trim();
				System.out.println("lsBackflushtype---------->>["+lsBackflushtype+"]");
				if(lsBackflushtype.equalsIgnoreCase("S") || lsBackflushtype.equalsIgnoreCase("C"))
				{
						if(lsBackflushtype.equalsIgnoreCase("C"))
						{
							stkUpdstk.put("potency_perc", Double.toString(lcPotencyPerc));
							stkUpdstk.put("supp_code__mfg", lsSuppcodeMfg);
						}
						if(lsUnitalt==null || lsUnitalt.trim().length() == 0)
						{
							sql="select unit__alt from stock where item_code = ?  and lot_no = ? and unit__alt is not null and length(trim(unit__alt)) > 0";
							pstmt1=conn.prepareStatement(sql);
							pstmt1.setString(1, lsItemcode);
							pstmt1.setString(2, lsLotno);
							rs1=pstmt1.executeQuery();
							if(rs1.next())
							{
								lsUnitalt=rs1.getString(1)==null ? "" : rs1.getString(1);
							}
							System.out.println("lsUnitalt--------->>["+lsUnitalt+"]");
							if(rs1 != null)
							{
								rs1.close();
								rs1=null;
							}
							if(pstmt1 !=null)
							{
								pstmt1.close();
								pstmt1=null;
							}
							
						}
						if(lcConvqtystduom == 0)
						{
							sql = "select conv__qty_stduom from stock where item_code = ? " +
									"and lot_no =  ? and conv__qty_stduom is not null and " +
									"conv__qty_stduom <> 0 ";
							pstmt1=conn.prepareStatement(sql);
							pstmt1.setString(1, lsItemcode);
							pstmt1.setString(2, lsLotno);
	                        System.out.println("SQL : "+sql);
	                        rs1 = pstmt1.executeQuery();
	                        if(rs1.next())
	                        	lcConvqtystduom = rs1.getDouble(1);                        
	                       
	                        System.out.println("lcConvqtystduom----------->>"+lcConvqtystduom);
	                        if(rs1 != null)
	        				{
	        					rs1.close();
	        					rs1=null;
	        				}
	        				if(pstmt1 !=null)
	        				{
	        					pstmt1.close();
	        					pstmt1=null;
	        				}
						}
				}				
				 System.out.println("lsCctrcodecr---->>["+lsCctrcodecr+"]  lsCctrcodedr---->>"+lsCctrcodedr+"]");
				 if(lsCctrcodecr == null || lsCctrcodecr.trim().length() == 0)
				 {
					 lsCctrcodecr="";
				 }
				 if(lsCctrcodedr == null || lsCctrcodedr.trim().length() == 0)
				 {
					 lsCctrcodedr="";
				 }
				 stkUpdstk.put("line_no",lsLineno);
				 stkUpdstk.put("item_code",lsItemcode);
				 stkUpdstk.put("site_code",lsSitecode);
				 stkUpdstk.put("loc_code",lsLoccode);
				 stkUpdstk.put("lot_no",lsLotno);
	             stkUpdstk.put("lot_sl",lsLotsl);
	             stkUpdstk.put("unit",lsUnit);
	             stkUpdstk.put("quantity",Double.toString(lcQuantityDet));
	             stkUpdstk.put("qty_stduom",Double.toString(lcQuantityDet));
	             stkUpdstk.put("tran_id",tranID);
	             stkUpdstk.put("tran_ser","R-BFS");
	             stkUpdstk.put("tran_date",ldtTrandate);
	             stkUpdstk.put("acct_code__dr",lsAcctcodedr);
	             stkUpdstk.put("cctr_code__dr",lsCctrcodedr);
	             stkUpdstk.put("acct_code__cr",lsAcctcodecr);
	             stkUpdstk.put("cctr_code__cr",lsCctrcodecr);
	            
	             if(lcQuantityDet >= 0)
	             {
	                    stkUpdstk.put("tran_type","ID");
	              }
	             else
	             {
	            	 stkUpdstk.put("tran_type","R");
	                 stkUpdstk.put("quantity",Double.toString(-1 * lcQuantityDet));
	                 stkUpdstk.put("qty_stduom",Double.toString(-1 * lcQuantityDet));
	                 stkUpdstk.put("acct_code_inv",lsAcctcodedr);
	                 stkUpdstk.put("cctr_code_inv",lsCctrcodedr);
	             }
	             stkUpdstk.put("grade",lsGrade);
	                if(lcQuantityDet < 0)
	                {
	                    stkUpdstk.put("rate",Double.toString(lcRate));
	                    stkUpdstk.put("gross_rate",Double.toString(lcGrossrate));
	                }
	              stkUpdstk.put("item_ser","");	             
	              if(lsItemStru == null || !lsItemStru.equalsIgnoreCase("R"))
	              {
	                	StockUpdate stkUpd =  new StockUpdate();
	                	errorCode = stkUpd.updateStock(stkUpdstk,xtraParams,conn);
	                	System.out.println("After calling updateStock error string--->>["+errorCode+"]");
	                	stkUpd =  null;
	                    if(errorCode.indexOf("Error") != -1)	                                      	
	                        break;	                    
	                    
	               }
	              stkUpdstk.clear();         
				
			
		   }//end while lsAcctcodedr
			 if(rs != null)
			 {
			   rs.close();
			   rs=null;
			 }
			 if(pstmt !=null)
			 {
				pstmt.close();
				pstmt=null;
			 }
			 stkUpdstk.clear();      
			 if(lsCctrcodedr ==null || lsCctrcodedr.trim().length()==0)			 
				 lsCctrcodedr = lsCctrcodedet;
			 System.out.println("lsCctrcodedr---------->>["+lsCctrcodedr+"]");
			 System.out.println("Returning Result---->["+errorCode+"]");
			 if(errorCode.indexOf("Error") != -1)
			 {	               
	                conn.rollback();
	                return errorCode;
	         }
			 System.out.println("errorCode@@@---------->>["+errorCode+"]");
			 if(errorCode != null && errorCode.trim().length() == 0)
			 {
				 
				 sql="select item_code,site_code,loc_code,lot_no,lot_sl,quantity,unit,qc_reqd,mfg_date,exp_date," +
				 		"site_code__mfg,pack_code, grade, dimension, gross_weight,tare_weight, net_weight, " +
				 		"no_art, inv_value from receipt_backflush  where tran_id = ?";
				 pstmt=conn.prepareStatement(sql);
				 pstmt.setString(1, tranID);
				 rs=pstmt.executeQuery();
				 if(rs.next())
				 {
					 lsItemcode=rs.getString(1) == null ? "" :rs.getString(1); 
					 lsSitecode=rs.getString(2)== null ? "" :rs.getString(2);
					 lsLoccode=rs.getString(3) == null ? "" :rs.getString(3);
					 lsLotno=rs.getString(4) == null ? "" :rs.getString(4);
					 lsLotsl=rs.getString(5) == null ? "" :rs.getString(5);
					 lcQuantityHdr=rs.getDouble(6);
					 lsUnit=rs.getString(7)== null ? "" :rs.getString(7);
					 lsQcreqd=rs.getString(8) == null ? "" :rs.getString(8);
					 ldtMfgDate=rs.getTimestamp(9);
					 ldtExpiryDate=rs.getTimestamp(10);
					 lsSiteCodeMfg=rs.getString(11) == null ? "" :rs.getString(11);
					 lsPackCode=rs.getString(12) == null ? "" :rs.getString(12);
					 lsGrade=rs.getString(13)== null ? "" :rs.getString(13);
					 lsDimension=rs.getString(14) == null ? "" :rs.getString(14);
					 lcGrossWeight=rs.getDouble(15);
					 lcTareWeight=rs.getDouble(16);
					 lcNetWeight=rs.getDouble(17);
					 lcNoArt=rs.getDouble(18);
					 lcInvValue=rs.getDouble(19);
				 }
				 System.out.println("lsItemcode--->>["+lsItemcode+"] lsSitecode---->>["+lsSitecode+"] lcQuantityHdr-->>["+lcQuantityHdr+"]");
				 System.out.println("lsLotno--->>["+lsLotno+"]lsSiteCodeMfg---->>["+lsSiteCodeMfg+"] lsLoccode-->>["+lsLoccode+"]");
				 if(rs != null)
				 {
				   rs.close();
				   rs=null;
				 }
				 if(pstmt !=null)
				 {
					pstmt.close();
					pstmt=null;
				 }
				 
				 sql="select nvl(capacity,0) from location where loc_code = ?";
				 pstmt=conn.prepareStatement(sql);
				 pstmt.setString(1, lsLoccode);
				 rs=pstmt.executeQuery();
				 if(rs.next())				 
					 lcCapacity=rs.getDouble(1);
				 else
				 {
					 //Location Code not Found
					 errorCode =  itmDBAccessLocal1.getErrorString("","VTLOCCD","","",conn);				
					 return errorCode;
				 }
				 if(lcQuantityHdr > lcCapacity)
				 {
					 //Quantity is more than Capacity
					 errorCode =  itmDBAccessLocal1.getErrorString("","VTCAPACITY","","",conn);				
					 return errorCode;
				 }		 
				 
				 
			 }
			 System.out.println("errorCode@@@##---------->>["+errorCode+"]");
			 System.out.println("lcQuantityHdr @@@##---------->>["+lcQuantityHdr+"]");
			 if(errorCode != null && errorCode.trim().length() == 0)
			 {
				 if(lcQuantityHdr > 0)
				 {
					 lcRate = lcInvValue / lcQuantityHdr;
					 if (lsBackflushtype.equalsIgnoreCase("S") || lsBackflushtype.equalsIgnoreCase("C"))					 
						lcGrossrate = lcGrossamount / lcQuantityHdr;					 
					 else				 
						 lcGrossrate = lcInvValue / lcQuantityHdr;	 	
				 }
				 stkUpdstk.clear();
				 stkUpdstk.put("item_code",lsItemcode);
				 stkUpdstk.put("site_code",lsSitecode);
				 stkUpdstk.put("loc_code",lsLoccode);
				 stkUpdstk.put("lot_no",lsLotno);
	             stkUpdstk.put("lot_sl",lsLotsl);
	             stkUpdstk.put("unit",lsUnit);
	             if(lcQuantityHdr >= 0)
	             {
	            	  stkUpdstk.put("quantity",Double.toString(lcQuantityHdr));
	 	             stkUpdstk.put("qty_stduom",Double.toString(lcQuantityHdr));
	             }
	             else
	             {
	            	 stkUpdstk.put("quantity",Double.toString(-1 * lcQuantityHdr));
	 	             stkUpdstk.put("qty_stduom",Double.toString(-1 * lcQuantityHdr));
	             }
	             stkUpdstk.put("tran_id",tranID);
	             stkUpdstk.put("tran_ser","R-BFS");
	             stkUpdstk.put("tran_date",ldtTrandate);
	             
	             if (lcQuantityHdr >= 0)
	             {
        			lsAcctcodedr = lsAcctcodehdr;
        			lsCctrcodedr = lsCctrcodehdr;        			
        			stkUpdstk.put("acct_code__dr",lsAcctcodedr);
    	            stkUpdstk.put("cctr_code__dr",lsCctrcodedr);
    	            stkUpdstk.put("acct_code_inv",lsAcctcodedr);
	                stkUpdstk.put("cctr_code_inv",lsCctrcodedr);   	            
	                stkUpdstk.put("tran_type","R");
	             }
	             else
	             {
	            	 stkUpdstk.put("acct_code__cr",lsAcctcodehdr);
		             stkUpdstk.put("cctr_code__cr",lsCctrcodehdr);		             
		             stkUpdstk.put("acct_code_inv",lsAcctcodehdr);
		             stkUpdstk.put("cctr_code_inv",lsCctrcodehdr); 		             
		             stkUpdstk.put("tran_type","I");
	             }
	             stkUpdstk.put("acct_code__cr","");
	             stkUpdstk.put("cctr_code__cr","");
	             stkUpdstk.put("rate",Double.toString(lcRate));
                 stkUpdstk.put("gross_rate",Double.toString(lcGrossrate));
                 stkUpdstk.put("mfg_date",ldtMfgDate);
                 stkUpdstk.put("exp_date",ldtExpiryDate);
                 stkUpdstk.put("site_code__mfg",lsSiteCodeMfg);
                 
                 stkUpdstk.put("pack_code",lsPackCode);
                 stkUpdstk.put("retest_date",ldtRetestDate);
                 if(lsGrade==null)                	 
                	 stkUpdstk.put("grade","");
                 else
                	 stkUpdstk.put("grade",lsGrade);
                 
                 stkUpdstk.put("batch_no",lsBatchNo);
                 stkUpdstk.put("no_art",lcNoArt);
                 stkUpdstk.put("dimension",lsDimension);
                 stkUpdstk.put("gross_rate",lcGrossrate);
                 stkUpdstk.put("tare_weight",lcTareWeight);
                 stkUpdstk.put("net_weight",lcNetWeight);
                 
                 //Added by Dadaso pawar on 12/MAY/14 [Start]
                 System.out.println("lsBackflushtype in newadded----->>["+lsBackflushtype+"]");
                 if("C".equalsIgnoreCase(lsBackflushtype)){
                	 
                	sql="select item_code,lot_no,loc_code,lot_sl from receipt_backflush_det"
                			+ "	where  tran_id = ?"; 
                	
                	pstmt=conn.prepareStatement(sql);
                	pstmt.setString(1, tranID);
                	rs=pstmt.executeQuery();
                	if(rs.next()){
                		lsItemCodeDet=rs.getString(1);
                		lsLotNoDet=rs.getString(2);
                		lsLocCodedet=rs.getString(3);
                		lsLotSlDet=rs.getString(4); 			
                	}
                	lsItemCodeDet=lsItemCodeDet ==null ?"" :lsItemCodeDet.trim();
            		lsLotNoDet=lsLotNoDet ==null ?"" :lsLotNoDet.trim();
            		lsLocCodedet=lsLocCodedet ==null ?"" :lsLocCodedet.trim();
            		lsLotSlDet=lsLotSlDet ==null ?"" :lsLotSlDet.trim();
                	if(rs != null){
                	rs.close();
   				    rs=null;
   				    }
   				 	if(pstmt !=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, lsItemCodeDet);
   				 	pstmt.setString(2, lsLotNoDet);
   				 	
   				 	rs=pstmt.executeQuery();
   				 	if(rs.next()){
   				 		countDet=rs.getInt(1);
   				 		
   				 	}
   				 	System.out.println("shelfLifeType count---->>["+countDet+"]");
	   				if(rs != null){
	                rs.close();
	    		    rs=null;
	    			}
	    			if(pstmt !=null){
	    			pstmt.close();
	    			pstmt=null;
	    			}
	    			
	    			if(countDet > 0){
	    			sql="select shelf_life_type from item_lot_info where item_code = ? and	lot_no = ?";
	    			pstmt=conn.prepareStatement(sql);
   				 	pstmt.setString(1, lsItemCodeDet);
   				 	pstmt.setString(2, lsLotNoDet);
   				 	
   				 	rs=pstmt.executeQuery();
   				 	if(rs.next()){
   				 	lsShelfLifeType=rs.getString(1);   				 	
   				 	}
   				 	if(rs != null){
 	                rs.close();
 	    		    rs=null;
 	    			}
 	    			if(pstmt !=null){
 	    			pstmt.close();
 	    			pstmt=null;
 	    			}  				 	
	    			}
	    			lsShelfLifeType=lsShelfLifeType ==null? "" :lsShelfLifeType.trim();
	    			System.out.println("lsShelfLifeType12121--->>["+lsShelfLifeType+"]");
	    			stkUpdstk.put("shelf_life_type", lsShelfLifeType);
                	
                 }
                 //Added by Dadaso pawar on 12/MAY/14 [End]
                 if (lsBackflushtype.equalsIgnoreCase("S") || lsBackflushtype.equalsIgnoreCase("C"))
                 {
                	  stkUpdstk.put("unit__alt",lsUnitalt);
                	  stkUpdstk.put("conv__qty_stduom",lcConvqtystduom);
                 }
                 stkUpdstk.put("item_ser","");
                 System.out.println("calling updateStock method----------");
                 StockUpdate stkUpdObj =  new StockUpdate();
             	 errorCode = stkUpdObj.updateStock(stkUpdstk,xtraParams,conn);
             	System.out.println("After call updateStock method errorCode---------->>["+errorCode+"]");
             	 stkUpdObj =  null;
                
			 }
			 System.out.println("@@@@@@@@ errorCode @@@@@@ ["+errorCode+"]");
			 if(errorCode ==null || errorCode.trim().length() ==0)
			 {
				 
				// errorCode=new FinCommonInvAcct().rbfpost(tranID,"R-BFS","",conn);
				 //errorCode=new FinCommonInvAcct().rbfpost(tranID, "R-BFS", conn); Comment by mahesh on 23-05-2014
				 errorCode = invAcct.rbfpost(tranID,"R-BFS",conn);
				 System.out.println("@@@@@@@@ errorCode [rbfpost]@@@@@@--> ["+errorCode+"]");
			 }	
			
		}
		catch(Exception e)
		{
			System.out.println("Exception : confirmBackflush() method------------");	
			e.printStackTrace();
			return "Error";				
		}			
		
		
		return errorCode;
	}
	private String generateTranId( String windowName, String siteCode, Connection conn )throws ITMException
    {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String selSql = "";
		String tranId = "";
		String tranSer = "";
		String keyString = "";
		String keyCol = "";
		String xmlValues = "";
		String paySiteCode = "";
		String effectiveDate = "";
		java.sql.Timestamp currDate = null;
		java.sql.Date effDate = null;
		GenericUtility genericUtility = GenericUtility.getInstance();

		 try
         {

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

			currDate = new java.sql.Timestamp(System.currentTimeMillis());
			String currDateStr = sdfAppl.format(currDate);

			selSql = "SELECT KEY_STRING, TRAN_ID_COL, REF_SER FROM TRANSETUP WHERE TRAN_WINDOW = ? ";
				//System.out.println("selSql :"+selSql);
			pstmt = conn.prepareStatement(selSql);
			pstmt.setString( 1, windowName );
			rs = pstmt.executeQuery();
			if (rs.next())
			{
					keyString = rs.getString("KEY_STRING");
					keyCol = rs.getString("TRAN_ID_COL");
					tranSer = rs.getString("REF_SER");
			}
			rs.close();rs = null;
			pstmt.close();pstmt = null;
			System.out.println("keyString :"+keyString);
			System.out.println("keyCol :"+keyCol);
			System.out.println("tranSer :"+tranSer);

			xmlValues ="<?xml version=\"1.0\" encoding=\"utf-8\"?><Root>";
			xmlValues = xmlValues + "<Header></Header>";
			xmlValues = xmlValues + "<Detail1>";
			xmlValues = xmlValues +        "<tran_id></tran_id>";
			xmlValues = xmlValues +        "<site_code>" + siteCode + "</site_code>";
			xmlValues = xmlValues +        "<tran_date>" + currDateStr + "</tran_date>";
			xmlValues = xmlValues + "</Detail1></Root>";
			System.out.println("xmlValues  :["+xmlValues+"]");
			TransIDGenerator tg = new TransIDGenerator(xmlValues, "BASE", CommonConstants.DB_NAME);
			tranId = tg.generateTranSeqID(tranSer, keyCol, keyString, conn);
			System.out.println("tranId :"+tranId);
         }
		catch (SQLException ex)
		{
			System.out.println("Exception ::" +selSql+ ex.getMessage() + ":");
			ex.printStackTrace();
			throw new ITMException(ex);
		}
		catch (Exception e)
		{
			System.out.println("Exception ::" + e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if (rs != null)
				{
						rs.close();
						rs = null;
				}
				if (pstmt != null)
				{
						pstmt.close();
						pstmt = null;
				}
			}
			catch(Exception e){}
		}
        return tranId;
     }//generateTranTd()
	
	private int getDBRowCount(Connection conn, String table_name, String whrCondCol, String whrCondVal)
	{
			int count=-1;
			
			if(conn!=null){
				
				ResultSet rs=null;
				PreparedStatement pstmt = null;
				
				String sql="select count(1) from "+table_name+" where "+whrCondCol+" = ?";
				System.out.println("SQL in getDBRowCount method : "+sql);
				try
				{
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,whrCondVal);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						count = rs.getInt(1);
					}
				}
				catch(SQLException e)
				{
					System.out.println("SQL Exception In getDBRowCount method of RcpBackflushConfirm Class : "+e.getMessage());
					e.printStackTrace();					
				}
				catch(Exception ex)
				{
					System.out.println("Exception In getDBRowCount method of RcpBackflushConfirm Class : "+ex.getMessage());
					ex.printStackTrace();					
				}finally{
					
					try{
						
						if(pstmt!=null){
							pstmt.close();
							pstmt = null;
						}
						if(rs!=null){
							rs.close();
							rs = null;
						}
					}catch (SQLException se) {
						se.printStackTrace();
					}
				}
			}else{
				try {
					throw new SQLException("Connection passed to RcpBackflushConfirm.getDBRowCount() method is null");
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
				
		return count;
	}
	
	
}
