package ibase.webitm.ejb.dis;
import ibase.webitm.utility.*;
import org.w3c.dom.*;
import ibase.utility.CommonConstants;
import ibase.webitm.ejb.*;
import ibase.system.config.*;
import java.rmi.RemoteException;
import java.util.*;
import java.text.*;
import java.sql.*;
import javax.ejb.*;
import java.util.Calendar;
import java.util.Date;
import javax.naming.InitialContext;
import ibase.system.config.AppConnectParm;
//import ibase.webitm.ejb.MasterStateful;
//import ibase.webitm.ejb.MasterStatefulHome;
import org.apache.axis.client.Call;
import org.apache.axis.client.Service;
import org.apache.axis.encoding.XMLType;
import javax.xml.rpc.ParameterMode;
import javax.ejb.Stateless; // added for ejb3

@Stateless // added for ejb3
public class ChrgBckLocConf extends ActionHandlerEJB implements ChrgBckLocConfLocal, ChrgBckLocConfRemote
{
	GenericUtility genericUtility = GenericUtility.getInstance();
	String userId ="";
	String termId = "";
	String loginSite = "";
	/* public void ejbCreate() throws RemoteException, CreateException
	{
		System.out.println("Create Method Called....");
	}
	public void ejbRemove()
	{
	}
	public void ejbActivate()
	{
	}
	public void ejbPassivate()
	{
	} */
	public String confirm() throws RemoteException,ITMException
	{
		System.out.println("confirm() Method Called....");
		return "";
	}
	public String confirm(String xmlString, String xtraParams, String objContext) throws RemoteException,ITMException
	{
		String  retString = null;
		System.out.println("Xtra Params : " + xtraParams);
		//genericUtility = GenericUtility.getInstance();
		try
		{
			retString = actionConfirm(xmlString, xtraParams);
		}
	   	catch(Exception e)
		{
			System.out.println("Exception :actionHandler(String xmlString):" + e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		System.out.println("returning from  actionHandler"+retString);
	    return (retString);
	}
	private String actionConfirm(String tranID, String xtraParams) throws RemoteException,ITMException, Exception
	{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int cnt = 0;
		ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
		ConnDriver connDriver = new ConnDriver();
		String sql = "";
        String siteCode = "";
		String custCode = "";
		String empCode = "";
		String confirmed = "";
		userId = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginCode");
		loginSite = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginSiteCode");
		empCode = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginEmpCode");
		termId = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "termId" );
		java.sql.Timestamp currDate = null;
		java.sql.Timestamp tranDate = null;
		String returnString = null;
		boolean conFlag = false;
		String errStr = "";
		
		try
		{
			conn = connDriver.getConnectDB("DriverITM");
			conn.setAutoCommit(false);
			connDriver = null;
			currDate = new Timestamp( System.currentTimeMillis() );
			sql = "select tran_id, tran_date, site_code, cust_code, confirmed "
			  +"	from charge_back "
			  +" where tran_id = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString( 1, tranID );
			rs = pstmt.executeQuery();
			if( rs.next() )
			{
				tranDate = rs.getTimestamp("tran_date");
				siteCode = rs.getString("site_code");
				custCode = rs.getString("cust_code");
				confirmed = rs.getString("confirmed");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			if( confirmed != null && confirmed.trim().equals("Y") )
			{
				errStr = "TRNADYCONF";
				returnString = itmDBAccessEJB.getErrorString("",errStr,userId);
				return returnString;
			}

			sql = "update charge_back set "
				  +" confirmed = 'Y', "
				  +" VERIFY_FLAG = 'Y', "
				  +" conf_date = ?, "
				  +" emp_code__aprv = ?"
				  +" where tran_id = ? ";
				 // +" and confirmed <> 'Y' ";
			System.out.println( "upd sql " + sql );
			pstmt = conn.prepareStatement( sql );
			pstmt.setTimestamp( 1, currDate );
			pstmt.setString( 2, empCode );
			pstmt.setString( 3, tranID );
			int chkupdt = pstmt.executeUpdate();
			System.out.println("chkupdt==>"+chkupdt);

			pstmt.close();
			pstmt = null;
			
			returnString = createMiscCrnoteCback(currDate, "", siteCode, tranID, tranID, tranDate, tranDate, custCode, custCode, "Y", xtraParams, conn );
			if( returnString .equals("VTSUCC1") )
			{
				conFlag = true;
				errStr = "TRANCONFSC";
			}
			else
			{
				conFlag = false;
				errStr = returnString;
			}
			returnString = itmDBAccessEJB.getErrorString("",errStr,userId);
		}
		catch(Exception e)
		{
			System.out.println("ChrgBckLocConfEJB..."+e.getMessage());
			e.printStackTrace();
			try
			{
				conn.rollback();
			}
			catch(Exception e1)
			{
			   System.out.println("ChrgBckLocConfEJB..."+e1.getMessage());
			   e1.printStackTrace();
			}
		}
		finally
		{
			try
			{
				if(conFlag)
				{
					System.out.println("Commiting connection.......");
					conn.commit();
				}
				else
				{
					System.out.println("Connection Rollback.......");
					conn.rollback();
				}
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
				if(conn != null)
				{
					conn.close();
					conn = null;
				}
			}
			catch(Exception e)
			{
				System.out.println("Exception ChrgBckLocConfEJB....... :\n"+e.getMessage());
			}
		}
		System.out.println("retString ::"+returnString);
		return returnString;
	}
	private String createMiscCrnoteCback(Timestamp currDate, String str, String siteCode, String tranIdFr, String tranIdTo, Timestamp tranDateFr, Timestamp tranDateTo, String custCodeFr, String custCodeTo, String confirm, String xtraParams, Connection conn ) throws RemoteException,ITMException
	{
		PreparedStatement pstmt = null;
		PreparedStatement pstmt1 = null;
		PreparedStatement pstmtHdr = null;
		PreparedStatement pstmtDtl = null;
		ResultSet rs = null;
		Statement stmt = null;
		ResultSet rSet = null;
		String sql = null;
		String custCodeCr = "";
		String currCode = "";
		String finEntity = "";
		String acctCode = "";
		String cctrCode = "";
		String empCodeAprv = "";
		String tranIdSel = "";
		String pOrderNo = "";
		String siteCodeCr = "";
		String itemSer = "";
		String remarks = "";
		String detAcct = "";
		String errCode = "";
		String retString = "";
		String detCctr = "";
		String tranType = "";
		String reasonCd = "";
		String crNtTranId = "";
		String acctCodeTax = "";
		String cctrCodeTax = "";
		String round = "";
		String insDtlsql = "";
		String asPost = confirm;
		double roundTo = 0.0;
		double exchgRate = 0.0;
		double netAmt = 0.0;
		double claimAmt = 0.0;
		double grossAmount = 0.0;
		double drcrAmt = 0.0;
		double amtTax = 0.0;
		double total = 0.0;
		double roundAmt = 0.0;
		double diffAmt = 0.0;
		double discountAmt = 0.0;
		int llLineNo = 0;
		java.sql.Timestamp tranDate = null;
		java.sql.Timestamp pOrderDate = null;
		String insHdrSql = "insert into MISC_DRCR_RCP ( "
						+" TRAN_ID, TRAN_SER, TRAN_DATE, EFF_DATE, FIN_ENTITY, "
						+" SITE_CODE, SUNDRY_TYPE, SUNDRY_CODE, ACCT_CODE, CCTR_CODE, "
						+" AMOUNT, CURR_CODE, EXCH_RATE, REMARKS, DRCR_FLAG, "
						+" TRAN_ID__RCV, CONFIRMED, CHG_USER, CHG_DATE, CHG_TERM, "
						+" CONF_DATE, EMP_CODE__APRV, DUE_DATE, TRAN_TYPE, ITEM_SER, "
						+" AMOUNT__BC, SRETURN_NO, ADJ_MISC_CRN, ADJ_AMOUNT, PARENT__TRAN_ID, "
						+" REV__TRAN, ROUND_ADJ, "
						+" CUST_REF_NO, CUST_REF_DATE, CUST_REF_AMT, RND_OFF, RND_TO ) "
						+" values ( "
						+" ?, ?, ?, ?, ?, "
						+" ?, ?, ?, ?, ?, "
						+" ?, ?, ?, ?, ?, "
						+" ?, ?, ?, ?, ?, "
						+" ?, ?, ?, ?, ?, "
						+" ?, ?, ?, ?, ?, "
						+" ?, ?, ?, ?, ?, "
						+" ?, ?  ) ";
		
		String insertDtl = "insert into MISC_DRCR_RDET( "
					+" TRAN_ID, LINE_NO, ACCT_CODE, CCTR_CODE, AMOUNT, "
					+" NET_AMT, REF_NO, REAS_CODE, ANAL_CODE"
					+" ) values ( "
					+" ?, ?, ?, ?, ?, "
					+" ?, ?, ?, ? )";
		
		DistCommon disComm = new DistCommon();
		
		try
		{	
			pstmtHdr = conn.prepareStatement( insHdrSql );
			pstmtDtl = conn.prepareStatement( insertDtl );
			sql = "select a.cust_code__credit, a.curr_code, a.exch_rate, a.emp_code__aprv, a.tran_id, "
					 +" a.net_amt, a.porder_no, a.porder_date, a.claum_amt, a.site_code__cr, a.item_ser, "
					 +" a.amount, a.remarks, sum(b.discount_amt) as discAmt "
					 +" from charge_back a, charge_back_det b "
					 +"	where a.tran_id = b.tran_id "
					 +" and a.tran_id >= ? "
					 +" and	a.tran_id <= ? "
					 +" and a.tran_date >= ? "
					 +" and a.tran_date <= ? "
					 +" and a.cust_code >= ? "
					 +" and a.cust_code <= ? "
					 +" and a.site_code = ? "
					// +" and a.confirmed = 'Y' "
					 +" group by a.cust_code__credit, a.curr_code, a.exch_rate, a.emp_code__aprv, a.tran_id, "
					 +" a.net_amt, a.porder_no, a.porder_date, a.claum_amt, a.site_code__cr, a.item_ser, "
					 +" a.amount, a.remarks";

			pstmt = conn.prepareStatement( sql );
			pstmt.setString( 1, tranIdFr );
			pstmt.setString( 2, tranIdTo );
			pstmt.setTimestamp( 3, tranDateFr );
			pstmt.setTimestamp( 4, tranDateTo );
			pstmt.setString( 5, custCodeFr );
			pstmt.setString( 6, custCodeTo );
			pstmt.setString( 7, siteCode );

			rs = pstmt.executeQuery();
			while( rs.next() )
			{
				custCodeCr = rs.getString("cust_code__credit");
				currCode = rs.getString("curr_code");
				exchgRate = rs.getDouble("exch_rate");
				empCodeAprv = rs.getString("emp_code__aprv");
				tranIdSel = rs.getString("tran_id");
				netAmt = rs.getDouble("net_amt");
				pOrderNo = rs.getString("porder_no");
				pOrderDate = rs.getTimestamp("porder_date");
				claimAmt = rs.getDouble("claum_amt");
				siteCodeCr = rs.getString("site_code__cr");
				itemSer = rs.getString("item_ser");
				grossAmount = rs.getDouble("amount");
				remarks = rs.getString("remarks");
				discountAmt = rs.getDouble("discAmt");
				
				//System.out.println("claimAmt==>["+claimAmt+"]");
				//System.out.println("netAmt==>["+netAmt+"]");
				//System.out.println("grossAmount ie amount==>["+grossAmount+"]");
				if( siteCodeCr == null || siteCodeCr.trim().length() == 0 )
				{
					siteCodeCr = siteCode;
				}

				sql = "select fin_entity from site where site_code = ?" ;
				pstmt1 = conn.prepareStatement(sql);
				pstmt1.setString(1, siteCodeCr.trim() );
				rSet = pstmt1.executeQuery();
				if( rSet.next() )
				{
					finEntity = rSet.getString( "fin_entity" );
				}
				rSet.close();
				rSet = null;
				pstmt1.close();
				pstmt1 = null;

				sql = "select acct_code__ar, cctr_code__ar from customer where cust_code = ? " ;
				pstmt1 = conn.prepareStatement(sql);
				pstmt1.setString(1, custCodeCr.trim() );
				rSet = pstmt1.executeQuery();
				if( rSet.next() )
				{
					acctCode = rSet.getString( "acct_code__ar" );
					cctrCode = rSet.getString( "cctr_code__ar" );
				}
				rSet.close();
				rSet = null;
				pstmt1.close();
				pstmt1 = null;

				detAcct = disComm.getDisparams( "999999", "DRCR_ACCT_CBACK", conn );

				if( detAcct == null || detAcct.equalsIgnoreCase( "NULLFOUND" ) || detAcct.trim().length() == 0 )
				{
					errCode = "VMDRCRACCT"; //'DS000' + string(sqlca.sqldbcode)
					break;
				}

				detCctr = disComm.getDisparams( "999999", "DRCR_CCTR_CBACK", conn );

				if( detCctr == null || detCctr.equalsIgnoreCase( "NULLFOUND" ) || detCctr.trim().length() == 0 )
				{
					errCode = "VMLSCCTR"; //'DS000' + string(sqlca.sqldbcode)
					break;
				}

				tranType = disComm.getDisparams( "999999", "CHARGE_BACK_TRAN_TYPE", conn );

				if( tranType == null || tranType.equalsIgnoreCase( "NULLFOUND" ) || tranType.trim().length() == 0 )
				{
					errCode = "VMLSTRTYPE"; //'DS000' + string(sqlca.sqldbcode)
					break;
				}

				reasonCd = disComm.getDisparams( "999999", "CHARGE_BACK_REASON_CODE", conn );
				System.out.println( "reasonCd ::" + reasonCd );
				if( reasonCd == null || reasonCd.equalsIgnoreCase( "NULLFOUND" ) || reasonCd.trim().length() == 0 )
				{
					errCode = "VMREASON"; //'DS000' + string(sqlca.sqldbcode)
					break;
				}
				if( claimAmt <= 0 )
				{
					claimAmt = 0;
					drcrAmt = netAmt;
				}
				else
				{
					drcrAmt = claimAmt < netAmt ? claimAmt : netAmt;
				}
				
				crNtTranId = generateTranId("W_MISC_DRCR_RCP_CR", getCurrdateInAppFormat(), loginSite, userId, tranType );
				//-----------------------Inserting into header-----------------------------------
				pstmtHdr.setString( 1, crNtTranId );
				pstmtHdr.setString( 2, "MDRCRC" );
				pstmtHdr.setTimestamp( 3, currDate );
				pstmtHdr.setTimestamp( 4, currDate );
				pstmtHdr.setString( 5, finEntity );
				pstmtHdr.setString( 6, siteCodeCr.trim() );
				pstmtHdr.setString( 7, "C" );
				pstmtHdr.setString( 8, custCodeCr.trim() );
				pstmtHdr.setString( 9, acctCode == null || acctCode.trim().length() == 0 ? "" : acctCode.trim() );
				pstmtHdr.setString( 10, cctrCode == null || cctrCode.trim().length() == 0 ? "" : cctrCode.trim() );
				pstmtHdr.setDouble( 11, drcrAmt );
				pstmtHdr.setString( 12, currCode == null || currCode.trim().length() == 0 ? "" : currCode.trim() );
				pstmtHdr.setDouble( 13, exchgRate );

				if( remarks == null || remarks.trim().length() == 0 )
				{
					String tRemStr = null;
					System.out.println("tranIdSel->"+tranIdSel+" pOrderNo->"+pOrderNo+" pOrderDate->"+pOrderDate);
					tRemStr = ( "CB " + tranIdSel );
					pstmtHdr.setString( 14, tRemStr );
				}
				else
				{
					pstmtHdr.setString( 14, remarks.trim() );
				}
				pstmtHdr.setString( 15, "C" );
				pstmtHdr.setString( 16, "" );
				pstmtHdr.setString( 17, "N" );
				pstmtHdr.setString( 18, userId );
				pstmtHdr.setTimestamp( 19, currDate );
				pstmtHdr.setString( 20, termId );
				pstmtHdr.setNull( 21, java.sql.Types.DATE );
				pstmtHdr.setString( 22, empCodeAprv );
				pstmtHdr.setTimestamp( 23, currDate );
				pstmtHdr.setString( 24, ( tranType == null || tranType.trim().length() == 0 ? "" :tranType.trim() ) );
				pstmtHdr.setString( 25, ( itemSer == null || itemSer.trim().length() == 0 ? "" : itemSer.trim() ) );
				pstmtHdr.setDouble( 26, drcrAmt );
				pstmtHdr.setString( 27, ( tranIdSel == null || tranIdSel.trim().length() == 0 ? "" :  tranIdSel.trim() ) );
				pstmtHdr.setString( 28, "" ); //ADJ_MISC_CRN
				pstmtHdr.setDouble( 29, 0.0 ); //ADJ_AMOUNT
				pstmtHdr.setString( 30, "" ); //PARENT__TRAN_ID
				pstmtHdr.setString( 31, "" ); //REV__TRAN
				pstmtHdr.setDouble( 32, 0.0 ); //ROUND_ADJ
				//pstmt.setInt( 33, 0 ); //LINE_NO__SRET
				//pstmt.setInt( 34, 0 ); //LINE_NO__SRET
				//pstmt.setString( 35, "" ); //LOT_NO
				pstmtHdr.setString( 33, "" ); //CUST_REF_NO
				pstmtHdr.setNull( 34, java.sql.Types.DATE ); //CUST_REF_DATE
				pstmtHdr.setDouble( 35, 0.0 ); //CUST_REF_AMT
				pstmtHdr.setDouble( 36, 0.0 ); //RND_OFF
				pstmtHdr.setDouble( 37, 0.0 ); //RND_TO

				int updtCnt = pstmtHdr.executeUpdate();
				//System.out.println("updtCnt==>"+updtCnt);
				//-----------------------Inserting into detail-----------------------------------
				llLineNo = 0;
				pstmtDtl.setString( 1, crNtTranId );
				llLineNo++;
				pstmtDtl.setInt( 2, llLineNo );
				pstmtDtl.setString( 3, detAcct );
				pstmtDtl.setString( 4, detCctr );
				if( claimAmt > 0 && claimAmt < netAmt )
				{
					pstmtDtl.setDouble( 5, drcrAmt );
					pstmtDtl.setDouble( 6, drcrAmt );
				}
				else
				{
					pstmtDtl.setDouble( 5, grossAmount );
					pstmtDtl.setDouble( 6, grossAmount );
				}
				pstmtDtl.setString( 7, tranIdSel ); //ref_no
				pstmtDtl.setString( 8, ( reasonCd == null || reasonCd.trim().length() == 0 ? "" : reasonCd.trim() ) ); //reas_code
				pstmtDtl.setString( 9, "" ); //ANAL_CODE

				pstmtDtl.addBatch();

				sql = "select acct_code, cctr_code, sum(tax_amt) taxamt "
					+" from taxtran "
					+" where tran_code = 'S-CHB' and tran_id = ? "
					+" and tax_amt <> 0 "
					+" and effect <> 'N' "
					+" group by acct_code, cctr_code " ;
				//
				pstmt1 = conn.prepareStatement(sql);
				pstmt1.setString(1, tranIdSel);
				rSet = pstmt1.executeQuery();
				while( rSet.next() )
				{
					acctCodeTax = rSet.getString( "acct_code" );
					cctrCodeTax = rSet.getString( "acct_code" );
					amtTax =  rSet.getDouble( "taxamt" );

					if( acctCodeTax == null || acctCodeTax.trim().length() == 0 )
					{
						acctCodeTax = detAcct;
					}
					if( cctrCodeTax == null || cctrCodeTax.trim().length() == 0 )
					{
						cctrCodeTax = detCctr;
					}

					total = total + amtTax;

					pstmtDtl.setString( 1, crNtTranId );

					llLineNo++;
					pstmtDtl.setInt( 2, llLineNo );
					pstmtDtl.setString( 3, acctCodeTax );
					pstmtDtl.setString( 4, cctrCodeTax );
					pstmtDtl.setDouble( 5, amtTax);
					pstmtDtl.setDouble( 6, 0.0 );
					pstmtDtl.setString( 7, "" ); //ref_no
					pstmtDtl.setString( 8, "" ); //reas_code
					pstmtDtl.setString( 9, "" ); //ANAL_CODE

					pstmtDtl.addBatch();
				}
				rSet.close();
				rSet = null;
				pstmt1.close();
				pstmt1 = null;

				pstmtDtl.executeBatch();

				pstmtDtl.close();
				pstmtDtl = null;
				pstmtHdr.close();
				pstmtHdr = null;

				sql = "update charge_back set "
					+" tran_id__crn = ? "
					+" where tran_id = ? ";

				pstmt1 = conn.prepareStatement( sql );
				pstmt1.setString(1, crNtTranId );
				pstmt1.setString(2, tranIdSel );
				int updCount = 0 ;
				updCount = pstmt1.executeUpdate();

				if( updCount == 0 )
				{
					errCode = "DS000NR";
					break;
				}
				pstmt1.close();
				pstmt1 = null;

				sql = "select nvl(round,'N') ls_round, nvl(round_to,0.001) lc_round_to from customer where cust_code = ? ";
				pstmt1 = conn.prepareStatement( sql );
				pstmt1.setString(1, custCodeCr );
				rSet = pstmt1.executeQuery();
				if( rSet.next() )
				{
					round = rSet.getString( "ls_round" );
					roundTo = rSet.getDouble( "lc_round_to" );
				}
				rSet.close();
				rSet = null;
				pstmt1.close();
				pstmt1 = null;

				roundAmt = disComm.getRndamt( drcrAmt, round, roundTo );
				diffAmt = roundAmt - drcrAmt;
				if( diffAmt != 0 )
				{
					//1. update header
					//2. insert record in detail
					insDtlsql = "insert into MISC_DRCR_RDET( "
						+" TRAN_ID, LINE_NO, ACCT_CODE, CCTR_CODE, AMOUNT, "
						+" NET_AMT, REF_NO, REAS_CODE, ANAL_CODE, TAX_AMT"
						+" ) values ( "
						+" ?, ?, ?, ?, ?, "
						+" ?, ?, ?, ?, ? )";
					pstmt1 = conn.prepareStatement( insDtlsql );
					pstmt1.setString( 1, crNtTranId );

					llLineNo++;

					pstmt1.setInt( 2, llLineNo );
					pstmt1.setString( 3, detAcct );
					pstmt1.setString( 4, detCctr );
					pstmt1.setDouble( 5, -diffAmt);
					pstmt1.setDouble( 6, -diffAmt );
					pstmt1.setString( 7, "" ); //ref_no
					pstmt1.setString( 8, reasonCd.trim() ); //reas_code
					pstmt1.setString( 9, "" ); //ANAL_CODE
					pstmt1.setDouble( 10, 0 );

					pstmt1.executeUpdate();

					pstmt1.close();
					pstmt1 = null;
				}
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			
			if( "Y".equalsIgnoreCase( asPost )  )
			{
				conn.commit();
				retString = retrieveMiscDrcrRcp( "misc_drcr_rcp",crNtTranId,xtraParams,"N" );
				errCode = getErrCodeFromErrStr(retString);
			}
		}
		catch(Exception e)
		{
			System.out.println("Exception :" + e.getMessage() + ":");
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
				if(pstmtDtl != null)
				{
					pstmtDtl.close();
					pstmtDtl = null;
				}
				if(pstmtHdr != null)
				{
					pstmtHdr.close();
					pstmtHdr = null;
				}
			}
			catch(Exception e)
			{
				System.out.println("Exception ChrgBckLocConfEJB....... :\n"+e.getMessage());
			}
		}
		return errCode;
	}
	public String retrieveMiscDrcrRcp(String businessObj, String tranIdFr,String xtraParams, String forcedFlag) throws ITMException
	{
		String methodName = "";
		String compName = "";
		String retString = "";
		String serviceCode = "";
		String serviceURI = "";
		String actionURI = "";
		String sql = "";
		Connection conn = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		int cnt = 0;
		
		try
		{
			ConnDriver connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");
			conn.setAutoCommit(false);
			methodName = "gbf_post";
			actionURI = "http://NvoServiceurl.org/" + methodName;
			
			sql = "SELECT SERVICE_CODE,COMP_NAME FROM SYSTEM_EVENTS WHERE OBJ_NAME = ? AND EVENT_CODE = 'pre_confirm' ";
			pStmt = conn.prepareStatement(sql);
			pStmt.setString(1,businessObj);
			rs = pStmt.executeQuery();
			if ( rs.next() )
			{
				serviceCode = rs.getString("SERVICE_CODE");
				compName = rs.getString("COMP_NAME");
			}
			sql = "SELECT SERVICE_URI FROM SYSTEM_EVENT_SERVICES WHERE SERVICE_CODE = ? ";
			pStmt = conn.prepareStatement(sql);
			pStmt.setString(1,serviceCode);
			rs = pStmt.executeQuery();
			if ( rs.next() )
			{
				serviceURI = rs.getString("SERVICE_URI");
			}
			Service service = new Service();
			Call call = (Call)service.createCall();
			call.setTargetEndpointAddress(new java.net.URL(serviceURI));			
			call.setOperationName( new javax.xml.namespace.QName("http://NvoServiceurl.org", methodName ) );
			call.setUseSOAPAction(true);
			call.setSOAPActionURI(actionURI); 
			Object[] aobj = new Object[4];
			
			call.addParameter( new javax.xml.namespace.QName("http://NvoServiceurl.org", "component_name"), XMLType.XSD_STRING, ParameterMode.IN);
			call.addParameter( new javax.xml.namespace.QName("http://NvoServiceurl.org", "tran_id"), XMLType.XSD_STRING, ParameterMode.IN);
			call.addParameter( new javax.xml.namespace.QName("http://NvoServiceurl.org", "xtra_params"), XMLType.XSD_STRING, ParameterMode.IN);
			call.addParameter( new javax.xml.namespace.QName("http://NvoServiceurl.org", "forced_flag"), XMLType.XSD_STRING, ParameterMode.IN);
			
			aobj[0] = new String(compName);
			aobj[1] = new String(tranIdFr);
			aobj[2] = new String(xtraParams);
			aobj[3] = new String(forcedFlag);
			
			call.setReturnType(XMLType.XSD_STRING);
			retString = (String)call.invoke(aobj);
			System.out.println("Return string from NVO is:==>["+retString+"]");	
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if (pStmt != null )
				{
					pStmt.close();
					pStmt = null;
				}
				if( conn != null ){
					conn.close();
					conn = null;
				}
			}
			catch(Exception e)
			{}
		}
		return retString;
	}
	private String getCurrdateInAppFormat()
	{
		String currAppdate =null;
		java.sql.Timestamp currDate = null;
		Object date = null;
		SimpleDateFormat DBDate=null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		try
		{
				currDate =new java.sql.Timestamp(System.currentTimeMillis()) ;
				System.out.println( genericUtility.getDBDateFormat());
			 	DBDate= new SimpleDateFormat(genericUtility.getDBDateFormat());
				date = DBDate.parse(currDate.toString());
				currDate =	java.sql.Timestamp.valueOf(DBDate.format(date).toString() + " 00:00:00.0");
				currAppdate = new SimpleDateFormat(genericUtility.getApplDateFormat()).format(currDate).toString();
		}
		catch(Exception e)
		{
			System.out.println("Exception in  getCurrdateInAppFormat:::"+e.getMessage());
		}
		return (currAppdate);
	}
	private String generateTranId(String windowName, String tranDate, String siteCode ,String signBy,String tranType)
	{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String generateTranIdSql = null;
		String tranId = null;
		String xmlValues = null;
		StringBuffer xmlValuesBuff = new StringBuffer();
		String refSer = "";
		String keyString = "";
		String tranIdCol = "";
		 try
	     {
			ConnDriver connDriver = new ConnDriver();
			conn = connDriver.getConnectDB( "DriverITM" );
			conn.setAutoCommit(false);
			generateTranIdSql = "SELECT KEY_STRING, TRAN_ID_COL, REF_SER FROM TRANSETUP WHERE UPPER(TRAN_WINDOW)= ?";
			System.out.println("[ChargeBackLocConfEJB :: generateTranId : input Paramaters][windowName]["+windowName+"][tranDate]["+tranDate+"][siteCode]["+siteCode+"]");
	    	System.out.println( "[ChargeBackLocConfEJB : generateTranId : Tran generator Sql[" + generateTranIdSql+"]" );
			pstmt = conn.prepareStatement( generateTranIdSql );
			pstmt.setString(1, windowName);
			rs = pstmt.executeQuery();

			if( rs.next() )
			{
				keyString = rs.getString("KEY_STRING");
				tranIdCol = rs.getString("TRAN_ID_COL");
				refSer = rs.getString("REF_SER");
			}
			pstmt.close();
			pstmt = null;
			rs.close();
			rs = null;
			System.out.println("[Output of Tran generator Sql ][keyString]["+keyString+"][tranIdCol]["+tranIdCol+"][refSer]["+refSer+"]");	
			
			xmlValuesBuff.append("<?xml version=\"1.0\" encoding=\"utf-8\"?><Root>");
			xmlValuesBuff.append("<Header></Header>");
			xmlValuesBuff.append("<Detail1>");
			xmlValuesBuff.append("<tran_id></tran_id>");
			xmlValuesBuff.append("<site_code><![CDATA["+siteCode+"]]></site_code>");
			xmlValuesBuff.append("<tran_date><![CDATA["+tranDate+"]]></tran_date>");
			xmlValuesBuff.append("<vouch_type><![CDATA[F]]></vouch_type>");
			xmlValuesBuff.append("<tran_type><![CDATA["+tranType+"]]></tran_type>");
			xmlValuesBuff.append("</Detail1></Root>");
			xmlValues = xmlValuesBuff.toString();
			System.out.println("xmlValues  :[" + xmlValues + "]");
			TransIDGenerator tg = new TransIDGenerator(xmlValues,signBy, CommonConstants.DB_NAME);
			tranId = tg.generateTranSeqID(refSer, tranIdCol, keyString, conn);
			System.out.println("tranId :"+tranId);
			conn.commit();
		}
		catch (SQLException ex)
		{			
			ex.printStackTrace();
		}
		catch (Exception e)
		{		
			e.printStackTrace();
		}
		finally
		{
			try
			{
				if( conn != null ){
					conn.close();
					conn = null;
				}
				if( rs != null )
				{
					rs.close();
					rs = null;
				}
				if( pstmt != null )
				{
					pstmt.close();
					pstmt = null;
				}

			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
		}
		return tranId;
	}
	private String getErrCodeFromErrStr( String errStr ) throws RemoteException,ITMException
	{
		String retErrCode = null;
		
		try
        {
			Document ParseRetString = genericUtility.parseString( errStr );
			NodeList RetStringNodeList = ParseRetString.getElementsByTagName("error");
			Node RetStringNode = RetStringNodeList.item(0);
			retErrCode = RetStringNode.getAttributes().getNamedItem("id").getNodeValue();
		}
		catch(Exception exception)
        {
            exception.printStackTrace();
			throw new ITMException( exception );
        }
		return retErrCode;		
	}
}
