/**
* PURPOSE : Confirmation of Stock transfer transaction
* AUTHOR : Gulzar on 13/09/11
*/ 

package ibase.webitm.ejb.wms;

import ibase.webitm.utility.*;
import ibase.webitm.ejb.*;
import ibase.webitm.ejb.dis.*;
import ibase.webitm.ejb.sys.*;
import ibase.system.config.*;
import ibase.webitm.utility.ITMException;

import java.io.*;
import java.rmi.RemoteException;
import java.sql.*;
import java.util.*;
import java.util.Date;
import java.text.*;
import javax.ejb.*;
import javax.naming.InitialContext;
import javax.ejb.Stateless; 
import org.w3c.dom.*;

@Stateless 
public class StockTransferConf extends ActionHandlerEJB implements StockTransferConfRemote, StockTransferConfLocal
{
	/**
	 * The public function is used to confirm a transaction corresponding to the tran id it takes as an argument
	 * @param : tranId
	 * @param : xtraParams
	 */
	//changes by chitranjan for overriding of confirm method 29/11/11
	public String confirm(String tranID,String xtraParams, String forcedFlag) throws RemoteException,ITMException
	{

		String retString = "";
		Connection conn = null;
		boolean isConn= false;
		try
		{
			retString = this.confirm(tranID, xtraParams, forcedFlag, conn, isConn);

			if ( retString != null && retString.length() > 0 )
			{
				throw new Exception("Exception while calling confirm for tran  Id:["+tranID+"]");
			}
		}
		catch(Exception e)
		{
			System.out.println("Exception in [StockTransferConf] confirm " + e.getMessage());
			throw new ITMException(e);
		}
		return retString;
	}
	//changes by chitranjan in confirm method parameter add connection ,connStatus  
	public String confirm( String tranId, String xtraParams, String forcedFlag, Connection conn, boolean connStatus ) throws RemoteException,ITMException
	{		
		String replOrder = "";
		String confirmed = "";
		String retString = "";
		String itemCode = "";
		String siteCode = "";
		String locCodeFr = "";
		String locCodeTo = "";
		String lotNo = "";
		String lotSl = "";
		String lotSlTo = "";
		String acctCodeCr = "";		
		String cctrCodeCr = "";		
		
		String sql = "";
		String updateSql = "";
		
		double quantity = 0.0;
		double noArt = 0.0;
		
		java.sql.Timestamp expDate = null;
		java.sql.Timestamp mfgDate = null;
		java.sql.Timestamp restestDate = null;
		String packCode = "";
		String siteCodeMfg = "";
		String packInstr = "";
		String suppCodeMfg = "";
		String unitAlt = "";
		String batchNo = "";
		String unit = "";
		String grade = "";
		String remarks = "";
		String dimension = "";
		String acctCodeDr = "";
		String ediOption = "";
		String dataStr = "";
		String userId = "";
		String chgTerm = "";

		double grossRate = 0d;
		double convQtyStduom = 0d;
		double batchSize = 0d;
		double stkGrossRate = 0d;
		double stkRate = 0d;
		// 16/10/11 manoharan
		double stkQuantity = 0,holdQuantity = 0;
		PreparedStatement pstmtUpd = null, pstmtHold = null, pstmtRel = null;
		ResultSet rsHold = null, rsRel = null;
		String tranIdHold = "", sqlRel  = "", sqlHold = "",nearExpLoc = "";
		int lineNoHold = 0;
		// end 16/10/11 manoharan
		String partialGrlLoc = ""; //added by Ashish Sonawane on 05/SEP/12 for partial GRL location Inventory Status. 
		String invStatLocCodeTo =""; //added by Ashish Sonawane on 05/SEP/12 for partial GRL location Inventory Status.
		PreparedStatement pstmtInvStat =null;
		ResultSet rsInvStat =null;
		
		
		int lineNo = 0, updCnt = 0;
		
		boolean isError = false;
		
		HashMap updateRowMap = null;
		HashMap strAllocate = null;

		StockUpdate stkUpdate = null;
		
		//Connection conn = null;		
		PreparedStatement pstmt = null;
		PreparedStatement pstmtStock = null;
		ResultSet rs = null;
		ResultSet rsStock = null;

		GenericUtility genericUtility = GenericUtility.getInstance();
		
		ITMDBAccessEJB itmDBAccess = new ITMDBAccessEJB();
		InvAllocTraceBean invAllocTrace = new InvAllocTraceBean();
		DistCommon dComm = new DistCommon();
		double balQty = 0;
		try
		{
			if ( conn == null )
			{
				ConnDriver connDriver = new ConnDriver();
				conn = connDriver.getConnectDB("DriverITM");
				connDriver = null;
				conn.setAutoCommit(false);
				connStatus = true;
			}

			String dbDateFormat = genericUtility.getDBDateFormat();
			String applDateFormat = genericUtility.getApplDateFormat();
			
			java.sql.Timestamp currDate = new java.sql.Timestamp( System.currentTimeMillis() );
			java.util.Date currDate1 = new java.util.Date();
			SimpleDateFormat sdf = new SimpleDateFormat(applDateFormat);
			String currDateStr = sdf.format(currDate1);
			String empCode = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginEmpCode" );
			userId = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginCode" );
			chgTerm = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "chgTerm" );

			java.sql.Timestamp tranDate = java.sql.Timestamp.valueOf(genericUtility.getValidDateString( currDateStr , applDateFormat, dbDateFormat ) + " 00:00:00.0") ;
			
			updateRowMap = new HashMap();
			stkUpdate = new StockUpdate();
			
			sql = "SELECT CONFIRMED, SITE_CODE FROM STOCK_TRANSFER 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" );
			}
			rs.close();rs = null;
			pstmt.close();pstmt = null;
			if( confirmed.equalsIgnoreCase("Y") )
			{
				retString = itmDBAccess.getErrorString("","CONFIRMED","");
				return retString;
			}
			else
			{
				// 24/06/12 manoharan if transfer to near expiry not to transfer the hold quantity
				nearExpLoc = dComm.getDisparams("999999","NEAREXP_LOC",conn);
				partialGrlLoc = dComm.getDisparams("999999","PGRL_INVSTAT",conn);
				
				sql = "SELECT LINE_NO, ITEM_CODE, QUANTITY, LOC_CODE__FR, LOC_CODE__TO, LOT_NO__FR, LOT_SL__FR, LOT_SL__TO, ACCT_CODE__CR, CCTR_CODE__CR, NO_ART " +
					  "FROM STOCK_TRANSFER_DET WHERE TRAN_ID = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, tranId);
				rs = pstmt.executeQuery();

				// 16/10/11 manoharan quantity, hold_qty added
				sql = "select acct_code__inv, cctr_code__inv, "
                        + "exp_date, pack_code, mfg_date, site_code__mfg, "
                        + "pack_instr, supp_code__mfg, retest_date, "
                        + "gross_rate, rate, conv__qty_stduom, unit__alt, batch_no, batch_size, "
						+ "unit, grade, remarks, dimension, quantity ,hold_qty "
                        + "from stock "
                        + "where item_code = ? "
                        + "and site_code = ? "
                        + "and loc_code = ? "
                        + "and lot_no = ? "
                        + "and lot_sl = ? ";
				// end 16/10/11 manoharan hold_qty added
                pstmtStock = conn.prepareStatement(sql);

				while(rs.next())
				{
					lineNo = rs.getInt( "LINE_NO" );
					itemCode = rs.getString( "ITEM_CODE" );
					quantity = rs.getDouble( "QUANTITY" );
					locCodeFr = rs.getString( "LOC_CODE__FR" );
					locCodeTo = rs.getString( "LOC_CODE__TO" );
					lotNo = rs.getString( "LOT_NO__FR" );
					lotSl = rs.getString( "LOT_SL__FR" );
					lotSlTo = rs.getString( "LOT_SL__TO" );
					noArt = rs.getDouble( "NO_ART" );
					acctCodeCr = rs.getString( "ACCT_CODE__CR" );
					cctrCodeCr = rs.getString( "CCTR_CODE__CR" );
					
					// 16/10/11 manoharan initialise quantity, hold_qty
					stkQuantity = 0;
					holdQuantity = 0;
					// end 16/10/11 manoharan hold_qty added
					/*Added by Ashish Sonawane on 05-Sep-12 [START]*/
					
					String sqlInvStat = "SELECT INV_STAT FROM LOCATION WHERE LOC_CODE = ?";
					pstmtInvStat = conn.prepareStatement( sqlInvStat );
					pstmtInvStat.setString( 1, locCodeTo );
					rsInvStat = pstmtInvStat.executeQuery();
					if( rsInvStat.next() )
					{
						invStatLocCodeTo = checkNull(rsInvStat.getString("INV_STAT"));
					}
					rsInvStat.close();rsInvStat = null;
					pstmtInvStat.close();pstmtInvStat = null;
					
					/*Added by Ashish Sonawane on 05-Sep-12 [END]*/
					
					
					pstmtStock.setString(1,itemCode);
					pstmtStock.setString(2,siteCode);
					pstmtStock.setString(3,locCodeFr);
					pstmtStock.setString(4,lotNo);
					pstmtStock.setString(5,lotSl);
					rsStock = pstmtStock.executeQuery();
					if ( rsStock.next() )
					{
						//acctCodeDr = rsStock.getString("acct_code__inv");
						//cctrCodeDr = rsStock.getString("cctr_code__inv");
						
						expDate = rsStock.getTimestamp("exp_date");
						packCode = rsStock.getString("pack_code");
						mfgDate = rsStock.getTimestamp("mfg_date");
						siteCodeMfg = rsStock.getString("site_code__mfg");
						packInstr = rsStock.getString("pack_instr");
						suppCodeMfg = rsStock.getString("supp_code__mfg");
						restestDate = rsStock.getTimestamp("retest_date");
						stkGrossRate = rsStock.getDouble("gross_rate");
						stkRate = rsStock.getDouble("rate");
						convQtyStduom = rsStock.getDouble("conv__qty_stduom");
						unitAlt = rsStock.getString("unit__alt");
						batchNo = rsStock.getString("batch_no");
						batchSize = rsStock.getDouble("batch_size");
						unit = rsStock.getString("unit");
						grade = rsStock.getString("grade");
						remarks = rsStock.getString("remarks");
						dimension = rsStock.getString("dimension");
						// 16/10/11 manoharan hold_qty added
						stkQuantity = rsStock.getDouble("quantity");
						holdQuantity = rsStock.getDouble("hold_qty");
						// end 16/10/11 manoharan hold_qty added
					}
					rsStock.close(); rsStock = null;
					pstmtStock.clearParameters();

					updateRowMap.put("item_code", itemCode);
					updateRowMap.put("site_code", siteCode);
					updateRowMap.put("loc_code", locCodeFr);
					updateRowMap.put("lot_no", lotNo);
					updateRowMap.put("lot_sl", lotSl);
					updateRowMap.put("quantity", ""+quantity);
					/* Added by Ashish Sonawane on 07-sep-12 as per Manoharan Sir in case of partial GRL no_art to be set as 0 for issue [START] */
					if(invStatLocCodeTo.trim().equalsIgnoreCase(locCodeTo.trim()))
					{
						noArt = 0;
						updateRowMap.put("no_art", noArt);
					}
					else
					{
						updateRowMap.put("no_art", noArt);
					}
					/* Added by Ashish Sonawane on 07-sep-12 as per Manoharan Sir in case of partial GRL no_art to be set as 0 for issue [END] */
					
					updateRowMap.put("tran_ser", "XFRX");
					updateRowMap.put("acct_code__cr",acctCodeCr);
					updateRowMap.put("cctr_code__cr",cctrCodeCr);
					updateRowMap.put("acct_code_inv",acctCodeCr);
					updateRowMap.put("cctr_code_inv",cctrCodeCr);
					updateRowMap.put("rate",Double.toString(stkRate));
					updateRowMap.put("gross_rate",Double.toString(stkGrossRate));
					updateRowMap.put("tran_id", tranId );
					updateRowMap.put("tran_date", tranDate );
					updateRowMap.put("tran_type","ID"); 

					updateRowMap.put("qty_stduom", ""+quantity);
					updateRowMap.put("unit",unit);
					updateRowMap.put("grade",grade);
					updateRowMap.put("remarks",remarks);
					updateRowMap.put("dimension",dimension);
					//updateRowMap.put("tran_type", "I");

					//changed by gulzar on 12/24/2011
					updateRowMap.put("exp_date", expDate);
					updateRowMap.put("pack_code", packCode);
					updateRowMap.put("mfg_date", mfgDate);
					updateRowMap.put("site_code__mfg", siteCodeMfg);
					updateRowMap.put("pack_instr", packInstr);
					updateRowMap.put("supp_code__mfg", suppCodeMfg);
					updateRowMap.put("retest_date", restestDate);
					updateRowMap.put("conv__qty_stduom", ""+convQtyStduom);
					updateRowMap.put("unit__alt", unitAlt);
					updateRowMap.put("batch_no", batchNo);
					updateRowMap.put("batch_size", ""+batchSize);
					//End changes by gulzar on 12/24/2011
					
					retString = stkUpdate.updateStock( updateRowMap, xtraParams, conn );
					if ( retString != null && retString.trim().length() > 0 )
					{
						isError = true;
						break;
					}
					
					updateRowMap.put("loc_code", locCodeTo);
					updateRowMap.put("lot_sl", lotSlTo);
					updateRowMap.put("tran_type", "R");
					/* Added by Ashish Sonawane on 07-sep-12 as per Manoharan Sir in case of partial GRL no_art to be set as quantity at the time of receipt [START] */
					if(invStatLocCodeTo.trim().equalsIgnoreCase(locCodeTo.trim()))
					{
						updateRowMap.put("no_art", quantity);
					}
					/* Added by Ashish Sonawane on 07-sep-12 as per Manoharan Sir in case of partial GRL no_art to be set as quantity at the time of receipt [END] */
					retString = stkUpdate.updateStock( updateRowMap, xtraParams, conn );
					if ( retString != null && retString.trim().length() > 0 )
					{
						isError = true;
						break;
					}
					updateRowMap.clear();
					
					// 16/10/11 manoharan check whether inv hold is there
					// if hold_qty > 0 update the same stock hold_qty for loc_code__to
					// also reduce the hold_qty for old loc_code
					// This utility methods to update old and new location should be in 
					// a separate probably in stockupdate so that we can call the same from various places 
					if (holdQuantity > 0)
					{
						// update old location with - hold_qty
						sqlRel = "UPDATE STOCK SET HOLD_QTY = HOLD_QTY - ?"
							+ " WHERE ITEM_CODE = ?  "
							+ " AND SITE_CODE = ? "
							+ " AND LOC_CODE = ? "
							+ " AND LOT_NO = ? "
							+ " AND LOT_SL = ? ";
						pstmtUpd = conn.prepareStatement(sqlRel);
						//pstmtUpd.setDouble(1,holdQuantity); //Commented by Ashish Sonawane on 01/SEP/12 as implication on stock due to PGRL location for partial qty putaway
						pstmtUpd.setDouble(1,quantity); //Added by Ashish Sonawane on 01/SEP/12 as implication on stock due to PGRL location for partial qty putaway 
						pstmtUpd.setString(2,itemCode);
						pstmtUpd.setString(3,siteCode);
						pstmtUpd.setString(4,locCodeFr);
						pstmtUpd.setString(5,lotNo);
						pstmtUpd.setString(6,lotSl);
						pstmtUpd.executeUpdate();
						// 24/06/12 manoharan if not neare expiry location hold quantity to be transferred
						if (!nearExpLoc.trim().equalsIgnoreCase(locCodeTo.trim()) )
						{
							// update new location with + hold_qty
							sqlRel = "UPDATE STOCK SET HOLD_QTY = CASE WHEN HOLD_QTY IS NULL THEN 0 ELSE HOLD_QTY END  + ? "
								+ " WHERE ITEM_CODE = ?  "
								+ " AND SITE_CODE = ? "
								+ " AND LOC_CODE = ? "
								+ " AND LOT_NO = ? "
								+ " AND LOT_SL = ? ";
							pstmtUpd = conn.prepareStatement(sqlRel);
							//pstmtUpd.setDouble(1,holdQuantity); //Commented by Ashish Sonawane on 01/SEP/12 as implication on stock due to PGRL location for partial qty putaway
							pstmtUpd.setDouble(1,quantity); //Added by Ashish Sonawane on 01/SEP/12 as implication on stock due to PGRL location for partial qty putaway
							pstmtUpd.setString(2,itemCode);
							pstmtUpd.setString(3,siteCode);
							pstmtUpd.setString(4,locCodeTo);
							pstmtUpd.setString(5,lotNo);
							//pstmtUpd.setString(6,lotSl);//Gulzar on 21/12/11
							pstmtUpd.setString(6,lotSlTo);//Gulzar on 21/12/11
							pstmtUpd.executeUpdate();


							pstmtHold = null;
							rsHold = null;
							tranIdHold = "";
							lineNoHold = 0;
							
							
							
							sqlHold = "SELECT D.TRAN_ID AS TRAN_ID, D.LINE_NO AS LINE_NO, "
								+ " D.ITEM_CODE AS ITEM_CODE, D.SITE_CODE AS SITE_CODE, "
								+ " D.LOC_CODE AS LOC_CODE, D.LOT_NO AS LOT_NO,  D.LOT_SL AS LOT_SL "
								+ " FROM INV_HOLD_DET D, INV_HOLD H "
								+ " WHERE H.TRAN_ID = D.TRAN_ID "
								+ " AND ((D.ITEM_CODE = ? ) OR (D.ITEM_CODE IS NULL OR LENGTH(TRIM(D.ITEM_CODE)) = 0 ) ) "
								+ " AND ((D.SITE_CODE = ? ) OR (D.SITE_CODE IS NULL OR LENGTH(TRIM(D.SITE_CODE)) = 0 ) ) "
								+ " AND ((D.LOC_CODE = ? ) OR (D.LOC_CODE IS NULL OR LENGTH(TRIM(D.LOC_CODE)) = 0 ) ) "
								+ " AND ((D.LOT_NO = ? ) OR (D.LOT_NO IS NULL OR LENGTH(TRIM(D.LOT_NO)) = 0 ) ) "
								+ " AND ((D.LOT_SL = ? ) OR (D.LOT_SL IS NULL OR LENGTH(TRIM(D.LOT_SL)) = 0 ) ) "
								+ " AND D.HOLD_STATUS = 'H'"
								+ " AND H.CONFIRMED  = 'Y' ";
							pstmtHold = conn.prepareStatement(sqlHold);
							pstmtHold.setString(1,itemCode);
							pstmtHold.setString(2,siteCode);
							pstmtHold.setString(3,locCodeFr);
							pstmtHold.setString(4,lotNo);
							pstmtHold.setString(5,lotSl);
							rsHold = pstmtHold.executeQuery();
							balQty = holdQuantity;
							while ( rsHold.next() )
							{
								/*//check whether  there is a confirmed release hold for the same 
								
								sqlRel = "select count(1) from inv_hold_rel  h, inv_hold_rel_det "
									+ " where h.tran_id = d.tran_id "
									+ " and d.tran_id__hold  = ? "
									+ " and d.line_no__hold  = ? "
									+ " and h.confirmed = 'Y'";
								pstmtRel =  conn.prepareStatement(sqlRel);
								pstmtRel.SetString(1,rsHold.getString("TRAN_ID"));
								pstmtRel.setInt(2,rsHold.getInt("LINE_NO"));
								rsRel = pstmtRel.executeQuery();
								if (rsRelel.next())
								{
								}
								rsRel.close();
								rsRel = null;
								pstmtRel.close();
								pstmtRel = null;
								*/
								tranIdHold = rsHold.getString("TRAN_ID");// added by Ashish Sonawane on 05-sep-12
								if(invStatLocCodeTo.trim().equalsIgnoreCase(locCodeTo.trim()))
								{
									insertIntoInvHold(itemCode,siteCode,locCodeTo,lotNo,lotSlTo,tranIdHold,conn);
									System.out.println("@@@@ End of insertion...");
								}
								else
								{
										// if not update INV_HOLD_DET with new loc_code (loc_code__to)
									sqlRel = "UPDATE INV_HOLD_DET SET LOC_CODE = ?, STATUS_DATE = ? "
										+ " WHERE TRAN_ID = ?  AND LINE_NO = ?";
									pstmtUpd = conn.prepareStatement(sqlRel);
									pstmtUpd.setString(1,locCodeTo);
									pstmtUpd.setTimestamp(2,new java.sql.Timestamp( System.currentTimeMillis() ));
									pstmtUpd.setString(3,rsHold.getString("TRAN_ID"));
									pstmtUpd.setInt(4,rsHold.getInt("LINE_NO"));
									pstmtUpd.executeUpdate();
									
									// 23/06/12 manoharan
									//tranIdHold = rsHold.getString("TRAN_ID"); //commented by Ashish Sonawane on 05-Sep-12 and moved to line no 421 
									sqlRel = "UPDATE INV_HOLD_REL_TRACE SET LOC_CODE = ? "
										+ " WHERE REF_NO = ? "
										+ " AND ITEM_CODE = ? "
										+ " AND SITE_CODE = ? "
										+ " AND LOC_CODE = ? "
										+ " AND LOT_NO = ? " 
										+ " AND LOT_SL = ? " ;
									pstmtUpd = conn.prepareStatement(sqlRel);
									pstmtUpd.setString(1,locCodeTo);
									pstmtUpd.setString(2,tranIdHold);
									pstmtUpd.setString(3,itemCode);
									pstmtUpd.setString(4,siteCode);
									pstmtUpd.setString(5,locCodeFr);
									pstmtUpd.setString(6,lotNo);
									pstmtUpd.setString(7,lotSl);
									updCnt = pstmtUpd.executeUpdate();
									System.out.println( "[" + updCnt + "] rows updated successfully in INV_HOLD_REL_TRACE" );
									// end 23/06/12 manoharan
								}
							}
							rsHold.close();
							rsHold = null;
							pstmtHold.close();
							pstmtHold = null;
							
						} // end 24/06/12 manoharan not near expiry
					}
					// end 16/10/11 manoharan
				}
				rs.close();rs = null;
				pstmt.close();pstmt = null;
				pstmtStock.close();pstmtStock = null;

				if ( retString == null || retString.trim().length() == 0 )
				{
					updateSql = "UPDATE STOCK_TRANSFER SET CONFIRMED = 'Y', CONF_DATE = ?, EMP_CODE__APRV = ? " +
								"WHERE TRAN_ID = ? ";
					pstmt = conn.prepareStatement(updateSql);
					pstmt.setTimestamp(1, currDate);
					pstmt.setString(2, empCode);
					pstmt.setString(3, tranId);				
					
					updCnt = pstmt.executeUpdate();
					pstmt.close();pstmt = null;
					
					if( updCnt > 0 )
					{					
						System.out.println( updCnt + " rows updated successfully" );
					}
					////////////////////// EDI creation
					/*
					sql = "SELECT EDI_OPTION FROM TRANSETUP WHERE TRAN_WINDOW = 'w_stock_transfer_wiz' ";
					pstmt = conn.prepareStatement(sql);
					rs = pstmt.executeQuery();
					if ( rs.next() )
					{
						ediOption = checkNull(rs.getString("EDI_OPTION"));
					}
					rs.close();rs = null;
					pstmt.close();pstmt = null;
					if ( "1".equals(ediOption.trim()) )
					{
						CreateRCPXML createRCPXML = new CreateRCPXML("w_stockinvStatLocCodeTo_transfer","tran_id");
						dataStr = createRCPXML.getTranXML( tranId, conn );
						System.out.println( "dataStr =[ "+ dataStr + "]" );
						Document ediDataDom = genericUtility.parseString(dataStr);

						E12CreateBatchLoadEjb e12CreateBatchLoad = new E12CreateBatchLoadEjb();
						e12CreateBatchLoad.createBatchLoad( ediDataDom, "w_stock_transfer", "0", xtraParams, conn );
						createRCPXML = null;
						e12CreateBatchLoad = null;
					}
					*/
					/////////////////////
				}
				////////////
			}
		}
		catch( Exception e )
		{
			try
			{
				conn.rollback();
				isError = true;
				e.printStackTrace();
			}
			catch (Exception e1)
			{
			}
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				//added by chitranjan connStatus if connstatus is true then commit.
				if( !isError && connStatus )
				{
					conn.commit(); 
					retString = itmDBAccess.getErrorString("","CONFSUCC","");
				}
				else if(connStatus)
				{
					conn.rollback();
				}
				if(rs != null )
				{
					rs.close();
					rs = null;
				}
				if(pstmt != null )
				{
					pstmt.close();
					pstmt = null;
				}
				if(pstmtStock != null )
				{
					pstmtStock.close();
					pstmtStock = null;
				}
				if( conn != null && connStatus )
				{
					conn.close();
					conn = null;
				}
			}
			catch(Exception e)
			{
				e.printStackTrace();
				throw new ITMException(e);
			}
		}
		return retString;
	}
	private String checkNull( String input )	
	{
		if ( input == null )
		{
			input = "";
		}
		return input;
	}
	
	private void insertIntoInvHold(String itemCode ,String siteCode, String locCode ,String lotNo, String lotSl, String tranIdHold ,Connection conn) throws ITMException  
	{
		PreparedStatement pstmtInsertInvHold =null;
		PreparedStatement pstmtMaxLine =null;
		ResultSet rsMaxLine =null;
		int maxLineNo=0;
		
		
		try {
				String sqlMaxLine ="SELECT MAX(LINE_NO) FROM INV_HOLD_DET WHERE TRAN_ID =?";
				pstmtMaxLine = conn.prepareStatement(sqlMaxLine);
				pstmtMaxLine.setString(1,tranIdHold);
				rsMaxLine =pstmtMaxLine.executeQuery();
				if(rsMaxLine.next())
				{
					maxLineNo =(rsMaxLine.getInt(1))+1;
					System.out.println("line no to be inserted :" +maxLineNo);
				}
				else
				{
					maxLineNo =maxLineNo++;
				}
				
				GenericUtility genericUtility = GenericUtility.getInstance();
				SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getDBDateTimeFormat());
				Date sysDate = new Date();
				String sysDateStr = sdf.format(sysDate);
				
				String sqlInsertInvHold = "INSERT INTO INV_HOLD_DET (TRAN_ID, LINE_NO, ITEM_CODE, SITE_CODE, LOC_CODE, LOT_NO, LOT_SL, HOLD_STATUS, STATUS_DATE , SCH_REL_DATE ) " +
										  " VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
				
				pstmtInsertInvHold = conn.prepareStatement(sqlInsertInvHold);
				
				pstmtInsertInvHold.setString(1, tranIdHold);
				pstmtInsertInvHold.setInt(2, maxLineNo);
				pstmtInsertInvHold.setString(3, itemCode);
				pstmtInsertInvHold.setString(4, siteCode);
				pstmtInsertInvHold.setString(5, locCode);
				pstmtInsertInvHold.setString(6, lotNo);
				pstmtInsertInvHold.setString(7, lotSl);
				pstmtInsertInvHold.setString(8, "H");
				pstmtInsertInvHold.setTimestamp(9, Timestamp.valueOf( sysDateStr));
				pstmtInsertInvHold.setTimestamp(10, Timestamp.valueOf( sysDateStr));
				int isInserted = pstmtInsertInvHold.executeUpdate();
				
				System.out.println("@@@@ Hey Row inserted !!!!!!!! .....Total :["+isInserted+"]");
				
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				throw new ITMException(e);
			}
		
	}
}
