
/*
 * JvYearEndPrc.java
 * Developer - Kunal Mandhre
 * Date - 09/APR/14 
 * FI3LFRA001
 */
package ibase.webitm.ejb.fin;
import ibase.system.config.ConnDriver;
import ibase.utility.CommonConstants;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.ejb.ProcessEJB;
import ibase.webitm.ejb.fin.adv.JvConfirm;
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.ArrayList;
import java.util.Iterator;
import java.util.Set;
import java.util.TreeSet;

import javax.ejb.Stateless;
import javax.xml.rpc.ParameterMode;

import org.apache.axis.client.Call;
import org.apache.axis.client.Service;
import org.apache.axis.encoding.XMLType;
import org.w3c.dom.Document;

@Stateless // added for ejb3
public class JvYearEndPrc extends ProcessEJB implements JvYearEndPrcLocal,JvYearEndPrcRemote
{	
	String loginSiteCode = null;
	GenericUtility genericUtility = GenericUtility.getInstance();
	String currDateTs = null;
	String chgUser = "";
	String chgTerm = ""; 

	public void remove()
	{
	}
	public String process() throws RemoteException,ITMException
	{
		return "";
	}

	JournalDetBean dtlItems = null;

	public String process(String xmlString, String xmlString2, String windowName, String xtraParams)
			throws RemoteException,ITMException
			{
		Document detailDom = null;
		Document headerDom = null;
		String retStr = "";
		System.out.println("Process method called......");

		try
		{	
			if(xmlString != null && xmlString.trim().length()!=0)
			{
				headerDom = genericUtility.parseString(xmlString); 
				System.out.println("headerDom" + headerDom);
			}
			if(xmlString2 != null && xmlString2.trim().length()!=0)
			{
				detailDom = genericUtility.parseString(xmlString2); 
				System.out.println("detailDom" + detailDom);
			}
			retStr = process(headerDom, detailDom, windowName, xtraParams);
		}
		catch (Exception e)
		{			
			System.out.println("Exception :JvYearEndPrc :process(String xmlString, String xmlString2, String windowName, String xtraParams):" + e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		return retStr;
			}//END OF PROCESS (1)

	public String process(Document headerDom, Document detailDom, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		Connection conn = null;	
		String resultString = "", errString = "";
		boolean isError = false;
		PreparedStatement pstmt = null ,pstmt1 = null;	
		ResultSet rs = null ,rs1 = null;
		int count = 0,count1 = 0;
		String sql = "";
		ArrayList jourList = null;
		//String tranId = "";
		//String acctCodeFrom = "";
		//String acctCodeTo = "";
		String acctPrdFrom = "";
		String acctPrdTo = "";
		String siteCode = "";
		String genTranId = "";
		//String effDate = "";
		String finEntity = "" ,acctCodePL = "" , acctCodeBS = "";
		java.sql.Timestamp effDateTs = null ,tranDate = null;
		ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();	
		String userId = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"userId");
		loginSiteCode = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginSiteCode");
		chgUser = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginCode");
		chgTerm = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"termId");
		try
		{	
			ConnDriver connDriver = new ConnDriver();			
			conn = connDriver.getConnectDB("DriverITM");			
			connDriver = null;
			conn.setAutoCommit(false);
			SundryTransferPrc sundryTransferPrc = new SundryTransferPrc();
			GLTransferPrc  glTransferPrc = new GLTransferPrc();
			JvConfirm jvConfirm = new JvConfirm();
			Set<String> sundryTypes = new TreeSet<String>();
			sundryTypes.add("C");
			sundryTypes.add("S");
			sundryTypes.add("E");
			sundryTypes.add("T");
			sundryTypes.add("P");
			sundryTypes.add("L");
			sundryTypes.add("X");
			sundryTypes.add("B");
			sundryTypes.add("R");

			acctPrdFrom = genericUtility.getColumnValue("acct_prd__fr",headerDom );
			acctPrdTo = genericUtility.getColumnValue("acct_prd__to",headerDom );
			//siteCode   = genericUtility.getColumnValue("site_code",headerDom );
			finEntity   = genericUtility.getColumnValue("fin_entity",headerDom );
			acctCodePL = genericUtility.getColumnValue("acct_code_pl",headerDom );
			acctCodeBS = genericUtility.getColumnValue("acct_code_bs",headerDom );

			tranDate = Timestamp.valueOf(genericUtility.getValidDateString(genericUtility.getColumnValue("tran_date", headerDom), genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat()) + " 00:00:00.0");
			sql = " select site_code from site where fin_entity = ? ";
			pstmt1 = conn.prepareStatement( sql );
			pstmt1.setString(1,finEntity);
			rs1 = pstmt1.executeQuery();
			while (rs1.next())
			{
				siteCode = rs1.getString( "site_code" );
				System.out.println("siteCode is =====>"+siteCode);
				jourList = null;
				jourList = new ArrayList();
				double sumCreditAmt = 0 ,sumDebitAmt = 0 ;
				double drAmtHdr = 0, crAmtHdr = 0;
				count = 0;
				sql = "SELECT A.ACCT_CODE, SUM(CASE WHEN A.DR_AMT__BASE IS NULL THEN 0 ELSE A.DR_AMT__BASE END) AMT_DR,"  
						+"SUM(CASE WHEN A.CR_AMT__BASE IS NULL THEN 0 ELSE A.CR_AMT__BASE END) AMT_CR "//A.CCTR_CODE, "
						//  +" A.FIN_ENTITY "
						+" FROM ACCTBAL A, "
						+"ACCOUNTS B, ACCTSGRP C, ACCTGRP D "
						+"WHERE A.ACCT_CODE = B.ACCT_CODE "
						+"AND B.SGROUP_CODE = C.SGROUP_CODE "
						+"AND C.GROUP_CODE = D.GROUP_CODE "
						+"AND D.ACCT_CAT IN ('I','E') "
						//  +"AND A.SITE_CODE IN ( ? ) "
						//  +"AND A.SITE_CODE IN ('"+siteCode+"') "
						+"AND A.SITE_CODE = ? "
						/*+"AND A.PRD_CODE >= ? "
						+"AND A.PRD_CODE <=  ? "*/
						+"AND A.ACCT_PRD >= ? "
						+"AND A.ACCT_PRD <=  ? "
						//+"AND A.ACCT_CODE >= ? "
						//+"AND A.ACCT_CODE <= ? "
						//  +"GROUP BY A.PRD_CODE, A.ACCT_CODE, A.CCTR_CODE, A.FIN_ENTITY "
						+"GROUP BY A.ACCT_CODE "
						+"HAVING SUM(CASE WHEN A.DR_AMT__BASE IS NULL THEN 0 ELSE A.DR_AMT__BASE END) <> SUM(CASE WHEN A.CR_AMT__BASE " +"IS NULL THEN 0 ELSE A.CR_AMT__BASE END)";

				//System.out.println("sql ::"+sql);
				pstmt = conn.prepareStatement( sql );

				pstmt.setString( 1, siteCode );
				pstmt.setString( 2, acctPrdFrom );
				pstmt.setString( 3, acctPrdTo );
				//pstmt.setString( 4, acctCodeFrom );
				//pstmt.setString( 5, acctCodeTo );
				//System.out.println("After setting data");
				rs = pstmt.executeQuery();
				while (rs.next())
				{
					count++;
					count1++;
					//dtlItems.acct_code = rs.getString(1);
					//	dtlItems.cctr_code = rs.getString(4);
					double amtDr = Double.parseDouble(rs.getString(2));
					double amtCr = Double.parseDouble(rs.getString(3));
					//	finEntity = rs.getString(5);
					sumCreditAmt = sumCreditAmt + amtCr;
					sumDebitAmt = sumDebitAmt + amtDr;

					/*if (amtDr >= amtCr)
					{
						dtlItems.acct_code = acctCodePL ;
						dtlItems.drcrFlag = "C";
						dtlItems.amount = amtDr - amtCr;
						crAmtHdr = crAmtHdr + dtlItems.amount;
						jourList.add( dtlItems );
						dtlItems = null;
						dtlItems = new JournalDetBean();

						dtlItems.acct_code = acctCodeBS ;
						dtlItems.drcrFlag = "D";
						dtlItems.amount = amtDr - amtCr;
						drAmtHdr = drAmtHdr + dtlItems.amount;
						jourList.add( dtlItems );
					}
					else
					{
						dtlItems.acct_code = acctCodeBS ;
						dtlItems.drcrFlag = "C";
						dtlItems.amount = amtCr - amtDr;
						crAmtHdr = crAmtHdr + dtlItems.amount;
						jourList.add( dtlItems );
						dtlItems = null;
						dtlItems = new JournalDetBean();


						dtlItems.acct_code = acctCodePL ;
						dtlItems.drcrFlag = "D";
						dtlItems.amount = amtCr - amtDr;
						drAmtHdr = drAmtHdr + dtlItems.amount;
						jourList.add( dtlItems );

					}*/
					//jourList.add( dtlItems );


				}
				pstmt.close();
				pstmt = null;
				System.out.println("Site Code::"+siteCode+"  sum of debit amt::"+sumDebitAmt+"   sum of credit amt::"+sumCreditAmt);
				dtlItems = new JournalDetBean();
				System.out.println("Count=="+count+"  Count Record=="+count1);

				if(count > 0)
				{
					if (sumDebitAmt >= sumCreditAmt)
					{
						dtlItems.acct_code = acctCodePL ;
						dtlItems.drcrFlag = "C";
						dtlItems.amount = sumDebitAmt - sumCreditAmt;
						crAmtHdr = crAmtHdr + dtlItems.amount;
						jourList.add( dtlItems );
						dtlItems = null;
						dtlItems = new JournalDetBean();

						dtlItems.acct_code = acctCodeBS ;
						dtlItems.drcrFlag = "D";
						dtlItems.amount = sumDebitAmt - sumCreditAmt;
						//drAmtHdr = drAmtHdr + dtlItems.amount;
						drAmtHdr = dtlItems.amount;
						jourList.add( dtlItems );
					}
					else
					{
						dtlItems.acct_code = acctCodeBS ;
						dtlItems.drcrFlag = "C";
						dtlItems.amount = sumCreditAmt - sumDebitAmt;
						crAmtHdr = crAmtHdr + dtlItems.amount;
						jourList.add( dtlItems );
						dtlItems = null;
						dtlItems = new JournalDetBean();

						dtlItems.acct_code = acctCodePL ;
						dtlItems.drcrFlag = "D";
						dtlItems.amount = sumCreditAmt - sumDebitAmt;
						//drAmtHdr = drAmtHdr + dtlItems.amount;
						drAmtHdr = dtlItems.amount;
						jourList.add( dtlItems );

					}


					if( jourList.size() > 0 )
					{
						//sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
						//Timestamp currDate = new Timestamp(System.currentTimeMillis()); // 15/06/11 manoharan commented 
						/*currDateTs = sdf.format(currDate).toString(); // 15/06/11 manoharan commented 
					effDate   = genericUtility.getColumnValue("eff_date",headerDom );*/

						/*effDateTs = Timestamp.valueOf(genericUtility.getValidDateString(effDate, genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat()) + " 00:00:00.0");
					currDateTs = sdf.format(effDateTs).toString(); // 15/06/11 manoharan
						 */					//genTranId = generateTranId("W_CLOSINGDTL_PROC", conn);
						genTranId = generateTranId("W_CLOSINGDTL_PROC",siteCode, conn);
						drAmtHdr = Math.abs(drAmtHdr);
						crAmtHdr = Math.abs(crAmtHdr);
						errString = insertJournal( jourList, genTranId, drAmtHdr,crAmtHdr, finEntity, siteCode, tranDate, conn );
						if(errString == null || errString.trim().length() == 0)
						{
							errString = jvConfirm.confirm(genTranId, xtraParams, "", conn);
							//errString = "PROCSUCC";
						}
						System.out.println("errString 302=="+errString);
						if(errString != null && errString.indexOf("CONFSUCCES") == -1)
						{
							isError = true;
							conn.rollback();
							return errString;
						}
						
					}
					else
					{
						System.out.println("No record found for site [" + siteCode + "]");
						//errString = "VTNORECFND";
					}

				}
			}//end while site code loop 
			pstmt1.close();
			pstmt1 = null;
			rs1.close();
			rs1 = null;

			if(count1 <= 0)
			{
				isError = true;
				resultString = itmDBAccessEJB.getErrorString("","VTNOREC1  ",userId);
				return resultString ;
			}


			System.out.println("errString="+errString);
			if(errString != null && errString.trim().length() > 0 && errString.indexOf("CONFSUCCES") > -1)
			{
				System.out.println("sundry bal process callled");
				System.out.println("sundryTypes="+sundryTypes.toString());
				Document argDom = null;
				//sundry bal process callled 
				Iterator<String> iterator = sundryTypes.iterator();
				while(iterator.hasNext()) 
				{
					String sundryType  = iterator.next();
					StringBuffer argData = new StringBuffer();
					argData = new StringBuffer("<?xml version=\"1.0\"?><Root><Header><editFlag>");
					argData.append("A").append("</editFlag></Header>");
					argData.append("<Detail1\r\n>");
					argData.append("<fin_entity>").append(finEntity).append("</fin_entity>\r\n");
					argData.append("<sundry_type>").append(sundryType).append("</sundry_type>\r\n");
					argData.append("<sundry_code__fr>").append("00").append("</sundry_code__fr>\r\n");
					argData.append("<sundry_code__to>").append("ZZ").append("</sundry_code__to>\r\n");
					argData.append("<acct_prd__fr>").append(acctPrdFrom).append("</acct_prd__fr\r\n>");
					argData.append("<acct_prd__to>").append(acctPrdTo).append("</acct_prd__to\r\n>");
					argData.append("</Detail1>");
					argData.append("</Root>");
					System.out.println("xml string::"+argData);
					argDom = genericUtility.parseString(argData.toString());
					String retString = "";
					/*SundryTransferIC sundryTransferIC = new SundryTransferIC();
					retString = sundryTransferIC.wfValData(argData.toString(), "", "", "1", "A", xtraParams);
					System.out.println("317 retString IC::"+retString);
					retString = sundryTransferIC.wfValData(argDom, null, "1", "A", xtraParams);
					System.out.println("retString IC::"+retString);
					if(retString != null && retString.trim().length() > 0 )
					{
						return retString;
					}
					else
					{*/
						retString = sundryTransferPrc.process(argDom, null, "sundrybal_xfr", xtraParams,conn,false);
					//}

					System.out.println("retString PRC::"+retString);
					if(retString != null && retString.indexOf("PROCSUCC") == -1)
					{
						isError = true;
						conn.rollback();
						return retString;
					}
					else
					{
						errString = retString.toString();
					}
				}
				//GL transfer process called 
				System.out.println("gl transfer process callled");
				StringBuffer argData = new StringBuffer();
				argData = new StringBuffer("<?xml version=\"1.0\"?><Root><Header><editFlag>");
				argData.append("A").append("</editFlag></Header>");
				argData.append("<Detail1\r\n>");
				argData.append("<fin_entity>").append(finEntity).append("</fin_entity>\r\n");
				argData.append("<acct_code__fr>").append("00").append("</acct_code__fr>\r\n");
				argData.append("<acct_code__to>").append("ZZ").append("</acct_code__to>\r\n");
				argData.append("<acct_prd__fr>").append(acctPrdFrom).append("</acct_prd__fr\r\n>");
				argData.append("<acct_prd__to>").append(acctPrdTo).append("</acct_prd__to\r\n>");
				argData.append("</Detail1>");
				argData.append("</Root>");
				System.out.println("xml string=="+argData);
				argDom = genericUtility.parseString(argData.toString());
				String retString = "";
				/*GLTransferIC transferIC = new GLTransferIC();
				retString = transferIC.wfValData(argData.toString(), "", "", "1", "A", xtraParams);
				//retString = transferIC.wfValData(argDom, null, "1", "A", xtraParams);
				System.out.println("trans retString IC::"+retString);
				if(retString != null && retString.trim().length() > 0 )
				{
					return retString;
				}
				else
				{*/
					retString = glTransferPrc.process(argDom, null, "acctbal_xfr", xtraParams,conn,false);
				//}

				System.out.println("trans retString prc::"+retString);
				if(retString != null && retString.indexOf("PROCSUCC") == -1)
				{
					isError = true;
					conn.rollback();
					return retString;
				}
				else
				{
					errString = retString.toString();
				}
			}
			else
			{
				isError = true;
				conn.rollback();
			}




		} // end of try code 
		catch(Exception e)
		{
			isError = true;
			e.printStackTrace();
			errString = e.getMessage();
			throw  new ITMException(e);
		}		
		finally
		{
			try
			{
				if(rs != null)rs.close();
				rs = null;
				if(pstmt != null)pstmt.close();
				pstmt = null;				
				if(conn != null)
				{
					if(isError)
					{
						conn.rollback();
						System.out.println("connection rollback.............");
						resultString = itmDBAccessEJB.getErrorString("","PROCFAILED",userId);
					}	
					else
					{
						conn.commit();
						System.out.println("commiting connection.............");
						resultString = itmDBAccessEJB.getErrorString("","PROCSUCC",userId);

					}	
					conn.close();
					conn = null;	
				}
			}
			catch(SQLException sqle)
			{
				sqle.printStackTrace();
			}
		}	
		System.out.println("returning from     "+resultString);
		return resultString;
	} //end process

	private String insertJournal( ArrayList dtlList, String tranId, double drAmtHdr, double crAmtHdr, String finEntity, String firstSite, Timestamp effDateTs, Connection conn) throws RemoteException,ITMException
	{
		PreparedStatement pstmt = null;	
		String hdrInsertSql = "";
		String dtlInsertSql = "";
		String cctrCode = null;
		String currCode = "";
		ResultSet rs = null;
		int lineNo = 0;
		try
		{
			String getCurr = "SELECT curr_code from finent where fin_entity = ?";
			pstmt = conn.prepareStatement( getCurr );
			pstmt.setString( 1,finEntity);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				currCode = rs.getString("curr_code");
			}
			pstmt.close();
			pstmt = null;
			rs.close();
			rs = null;

			hdrInsertSql = "insert into journal( " 
					+" TRAN_ID, TRAN_DATE, EFF_DATE, FIN_ENTITY, CURR_CODE,  "
					+" EXCH_RATE, DR_AMT, CR_AMT, REMARKS, REVERSIBLE, SITE_CODE, "
					+" CHG_DATE, CHG_USER, CHG_TERM, SITE_CODE__REV, SITE_CODE__FOR, "
					+" AUTO_REV, CONFIRMED, STATUS )"
					+" VALUES( ?, ?, ?, ?, ?, "
					+" ?, ?, ?, ?, ?, ?, ?, ?, ?, "
					+" ?, ?, ?, ?, ? ) ";

			System.out.println( "Hdr Insert Sql :: " + hdrInsertSql );
			pstmt = conn.prepareStatement( hdrInsertSql );
			pstmt.setString( 1, tranId );
			//pstmt.setTimestamp( 2, getCurrdateAppFormat() );
			//pstmt.setTimestamp( 3, getCurrdateAppFormat() );
			pstmt.setTimestamp( 2, effDateTs );
			pstmt.setTimestamp( 3, effDateTs );
			pstmt.setString( 4, finEntity );
			pstmt.setString( 5, currCode );
			pstmt.setDouble( 6, 1 );
			pstmt.setDouble( 7, drAmtHdr );
			pstmt.setDouble( 8, crAmtHdr );
			pstmt.setString( 9, "Autogenerated" );
			pstmt.setString( 10, "N" );
			pstmt.setString( 11, firstSite );
			pstmt.setTimestamp( 12, getCurrdateAppFormat() );
			pstmt.setString( 13, chgUser );
			pstmt.setString( 14, chgTerm );
			pstmt.setString( 15, firstSite );
			pstmt.setString( 16, firstSite );
			pstmt.setString( 17, "N" );
			pstmt.setString( 18, "N" );
			pstmt.setString( 19, "A" );

			pstmt.executeUpdate();
			pstmt.close();
			pstmt = null;

			String getCctr = "SELECT var_value from finparm "
					+" where prd_code ='999999' AND var_name = ? ";
			pstmt = conn.prepareStatement( getCctr );
			pstmt.setString( 1, "YEAR_END_CCTR" );
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				cctrCode = rs.getString("var_value");
			}
			if( cctrCode == null )
			{
				cctrCode = "    ";
			}
			pstmt.close();
			pstmt = null;
			rs.close();
			rs = null;
			System.out.println("CCTR_CODE is==>"+cctrCode+"<==");
			int dtlListLen = dtlList.size();
			for( int dtlListIdx = 0; dtlListIdx < dtlListLen ; dtlListIdx++)
			{
				dtlItems = ( JournalDetBean ) dtlList.get( dtlListIdx );
				System.out.println("JV DET DATA::"+dtlItems.acct_code +" ::  "+dtlItems.drcrFlag);
				lineNo = lineNo + 1;
				dtlInsertSql = "insert into jourdet( " 
						+" TRAN_ID, LINE_NO, ACCT_CODE, CCTR_CODE, DRCR_FLAG,  "
						+" AMOUNT, REMARKS )"
						+" VALUES( ?, ?, ?,'"+cctrCode+"', ?, ?, ?) ";

				System.out.println( "Hdr Insert Sql :: " + dtlInsertSql );
				pstmt = conn.prepareStatement( dtlInsertSql );
				pstmt.setString( 1, tranId );
				pstmt.setInt( 2, lineNo );
				pstmt.setString( 3, dtlItems.acct_code );
				//pstmt.setString( 4,"    ");
				pstmt.setString( 4, dtlItems.drcrFlag );
				pstmt.setDouble( 5, dtlItems.amount );
				pstmt.setString( 6, "Autogenerated" );

				pstmt.executeUpdate();
				pstmt.close();
				pstmt = null;
			}

		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		return "";
	}

	private Timestamp getCurrdateAppFormat()
	{
		Timestamp timestamp = null;		
		GenericUtility genericUtility = GenericUtility.getInstance();
		try
		{
			java.util.Date date = null;
			timestamp = new Timestamp(System.currentTimeMillis());

			SimpleDateFormat simpledateformat = new SimpleDateFormat(genericUtility.getDBDateFormat());
			date = simpledateformat.parse(timestamp.toString());
			timestamp = Timestamp.valueOf(simpledateformat.format(date).toString() + " 00:00:00.0");
		}
		catch(Exception exception)
		{
			System.out.println("Exception in  getCurrdateAppFormat " + exception.getMessage());
		}
		return timestamp;
	}

	private String generateTranId(String windowName,String siteCode, Connection conn) throws Exception 
	{
		ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "",errCode ="",errString ="";
		String tranId = null;
		String newKeystring = "";
		boolean found =false;
		String xmlValues = "";
		try
		{
			sql = "SELECT KEY_STRING, TRAN_ID_COL, REF_SER FROM TRANSETUP WHERE UPPER(TRAN_WINDOW)=UPPER('"+windowName+"')";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			System.out.println("keyString :"+rs.toString());
			String tranSer1 = "";
			String keyString = "";
			String keyCol = "";
			if (rs.next())
			{	
				found =true;
				keyString = rs.getString(1);
				keyCol = rs.getString(2);
				tranSer1 = rs.getString(3);				
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			if(!found)
			{	  
				sql ="SELECT key_string,TRAN_ID_COL, REF_SER from transetup where tran_window = 'GENERAL' ";
				pstmt	= conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
				if (rs.next())
				{	
					keyString = rs.getString(1);
					keyCol = rs.getString(2);
					tranSer1 = rs.getString(3);	
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null ;
			}
			if(keyString ==null || keyString.trim().length() ==0)
			{
				errCode = "VTSEQ";
				System.out.println("errcode......"+errCode);
				errString = itmDBAccessEJB.getErrorString("","VTSEQ","BASE","",conn);

			}

			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.trim()+"</site_code>" ;
			xmlValues = xmlValues + "<tran_date>"+currDateTs+"</tran_date>" ;
			xmlValues = xmlValues + "</Detail1></Root>";

			System.out.println("keyString=>"+keyString);
			System.out.println("keyCol=>"+keyCol);
			System.out.println("tranSer1"+tranSer1);

			System.out.println("xmlValues  :["+xmlValues+"]");

			TransIDGenerator tg = new TransIDGenerator(xmlValues, "BASE", CommonConstants.DB_NAME);
			tranId = tg.generateTranSeqID(tranSer1, keyCol, keyString, conn);

			System.out.println(" new tranId :"+tranId);
			
		}
		catch(SQLException ex)
		{
			System.out.println("Exception ::" +sql+ ex.getMessage() + ":");			
			ex.printStackTrace();
			tranId=null;
			throw new  Exception(ex);

		}
		catch(Exception e)
		{
			System.out.println("Exception ::" + e.getMessage() + ":");
			e.printStackTrace();
			tranId=null;
			throw new  Exception(e);
		}
		return tranId;
	}//generateTranTd()
	public String confirmJournalVoucher(String businessObj, String tranIdFr,String xtraParams, String forcedFlag, Connection conn) throws ITMException
	{
		String methodName = "";
		String compName = "";
		String retString = "";
		String serviceCode = "";
		String serviceURI = "";
		String actionURI = "";
		String sql = "";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		//System.out.println("confirmJournalVoucher(String businessObj, String tranIdFr,String xtraParams, String forcedFlag, Connection conn) called >>><!@#>");

		try
		{
			//ConnDriver connDriver = new ConnDriver();
			//conn = connDriver.getConnectDB("DriverITM");

			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");
			}
			System.out.println("serviceCode = "+serviceCode+" compName "+compName);
			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");
			}
			System.out.println("serviceURI = "+serviceURI+" compName = "+compName);
			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);
			//System.out.println("@@@@@@@@@@loginEmpCode:" +genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginEmpCode")+":");
			System.out.println("@@@@@@ call.setReturnType(XMLType.XSD_STRING) executed........");
			call.setReturnType(XMLType.XSD_STRING);

			retString = (String)call.invoke(aobj);

			System.out.println("Confirm Complete @@@@@@@@@@@ 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)
			{
				System.out.println("Exception inCalling confirmed");
				e.printStackTrace();
				try{
					conn.rollback();

				}catch (Exception s)
				{
					System.out.println("Unable to rollback");
					s.printStackTrace();
				}
				throw new ITMException(e);
			}

		}
		return retString;
	}//end confirmJournalVoucher

	private class JournalDetBean
	{
		public String acct_code = null;
		public String cctr_code = null;
		public String drcrFlag = null;
		public double amount = 0;
	}
}//end class