/**
 *
 *  Window Name : w_receipt_adv
 *  Button Name : Default
 *	Action      : Adding Records in default
*/

package ibase.webitm.ejb.fin.adv;

import java.rmi.RemoteException;
import java.sql.*;
import org.w3c.dom.*;
import javax.ejb.*;
import ibase.webitm.utility.ITMException;
import ibase.webitm.ejb.*;
import ibase.webitm.utility.GenericUtility;
import ibase.system.config.*;
import java.text.SimpleDateFormat;
import javax.ejb.Stateless; // added for ejb3
import ibase.webitm.ejb.fin.FinCommon;

@Stateless // added for ejb3

public class ReceiptAdvAct extends ActionHandlerEJB implements ReceiptAdvActLocal,ReceiptAdvActRemote//SessionBean
{
	//updated by nisar on 04/01/08 for RID : SY6BAS0059
	GenericUtility genericUtility = GenericUtility.getInstance();//added by nisar on 04/01/08
	String act_type=null;

	/*public void ejbCreate() throws RemoteException, CreateException 
	{
	}

	public void ejbRemove()
	{
	}

	public void ejbActivate() 
	{
	}

	public void ejbPassivate() 
	{
	}*/

  public String actionHandler() throws RemoteException,ITMException
	{
		return "";
	}

	public String actionHandler(String actionType, String xmlString, String objContext, String xtraParams) throws RemoteException,ITMException
	{
		System.out.println("Calling actionHandler from ReceiptAdvEJB");
		Document dom = null;
		String  resString = null;
		
		try
		{
		   
			if ("actionDefaultSer".equalsIgnoreCase(actionType) || "Default".equalsIgnoreCase(actionType) || "ord_receivable".equalsIgnoreCase(actionType) )
			{ 
				if(xmlString != null && xmlString.trim().length()!=0)
				{
					GenericUtility genericUtility = GenericUtility.getInstance(); 
					System.out.println("XML String :"+xmlString);
					dom = genericUtility.parseString(xmlString); 
				}
				//resString = actionDefault(dom,objContext,xtraParams);
				// parameter actionType added by Mukesh  20/03/09
				resString = actionDefault(actionType,dom,objContext,xtraParams);
			}
			
			
			
			
		}
	   	catch(Exception e)
		{
			System.out.println("Exception :ReceiptAdvActEJB :actionHandler(String xmlString):" + e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		System.out.println("returning from ReceiptAdvActEJB actionHandler"+resString);
	    return (resString);
	}

	public String actionHandler(String actionType, String xmlString, String xmlString1, String objContext, String xtraParams) throws RemoteException,ITMException
	{
	   //Added by nisar on 04/01/08 for RID : SY6BAS0059
		Document dom = null;
		Document dom1 = null;
        
		String  retString = null;
		try
		{
			if(xmlString != null && xmlString.trim().length()!=0)
			{
				System.out.println("XML String ===================>:"+xmlString);
				dom = genericUtility.parseString(xmlString); 
				System.out.println("dom :"+dom);
			}
			if(xmlString1 != null && xmlString1.trim().length()!=0)
			{
				System.out.println("@@@@@XML String1 ===================>:"+xmlString1);
				dom1 = genericUtility.parseString(xmlString1);
				
			}
			System.out.println("actionType:"+actionType+":");
						
			if (actionType.equalsIgnoreCase("GPList"))
			{
				retString = getGPListDetails(dom,dom1,objContext,xtraParams);//function to fetch data
			}
			
			
			 
		}
	   	catch(Exception e)
		{
			System.out.println("Exception :ModelHandler :actionHandler(String xmlString):" + e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		System.out.println("returning String from ModelHandler : actionHandler"+retString);
	    return retString;
	}
	public String actionHandlerTransform(String actionType, String xmlString, String xmlString1, String objContext, String xtraParams, String selDataStr) throws RemoteException,ITMException
	{
		System.out.println("[ReceiptAdvActEJB] actionHandlerTransform is calling.............");
		Document dom = null;
		Document dom1 = null;
		Document selDataDom = null;

		String  retString = null;
		try
		{
			if(xmlString != null && xmlString.trim().length()!=0)
			{
				dom = GenericUtility.getInstance().parseString(xmlString); 
			}
			if(xmlString1 != null && xmlString1.trim().length()!=0)
			{
				dom1 = GenericUtility.getInstance().parseString(xmlString1);
			}
			if(selDataStr != null && selDataStr.length() > 0)
			{
				selDataDom = GenericUtility.getInstance().parseString(selDataStr);
			}

			System.out.println("actionType:"+actionType+":");
						
			if (actionType.equalsIgnoreCase("GPList"))
			{
				retString = gpListTransform(dom, dom1, objContext, xtraParams, selDataDom);
			}
		}
	   	catch(Exception e)
		{
			System.out.println("Exception :ReceiptAdvActEJB :actionHandlerTransform(String xmlString):" + e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		System.out.println("returning String from ReceiptAdvActEJB : actionHandlerTransform"+retString);
	    return retString;
	}

	private String actionDefault(String actionType,Document dom, String objContext, String xtraParams) throws RemoteException , ITMException
	{
		String siteCode = "";
		String currCode = "";
		String custCode = "";
		String exchRateHeader = "";
		String  chqAmt = "";
		String advAmt = "";
		StringBuffer valueXmlString = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root>\r\n");
		String sql = "";
		String sql1 = "";
		String sql2 = "";
		String sql3 = "";
		String detCnt = "0";
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		ResultSet rs1 = null;
		ResultSet rs2 = null;
		ResultSet rs3 = null;
		String siteCodeInv="";
		String errCode = "" ,errString = "";
		String itemSer=null;
		String sorder = "";
		ConnDriver connDriver = new ConnDriver();
		ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
		
		try
		{
			System.out.println("<---- IN RECEIPTADVACT IN TRY ----->");
			
			
			//detCnt = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"detCnt"); //Will be Uncommented later - Jiten
			
			if (detCnt.equals("0"))
			{
				System.out.println("<-----IN RECEIPTADVACT IN TRY detCnt equal 0----->");
				conn = connDriver.getConnectDB("DriverITM");
				siteCode = genericUtility.getColumnValue("site_code",dom);
				siteCodeInv = genericUtility.getColumnValue("site_code__inv",dom);
				
				custCode = genericUtility.getColumnValue("cust_code",dom);
				exchRateHeader = genericUtility.getColumnValue("exch_rate",dom);
				itemSer = genericUtility.getColumnValue("item_ser",dom);
				chqAmt = genericUtility.getColumnValue("chq_amt",dom);
				currCode = genericUtility.getColumnValue("curr_code",dom);
				advAmt = genericUtility.getColumnValue("adv_amt",dom);
				System.out.println("siteCodeInv:"+siteCodeInv);
				sorder = genericUtility.getColumnValue("sale_order",dom);
				
				System.out.println("sorder::"+sorder);
				
				//added new if condition by swati on 3rd june 2013 to check for outstanding of customer of invoicing site,RID:FI2JALL001
				if((siteCodeInv != null) && (siteCodeInv.trim().length()>0))
				{
					siteCode=siteCodeInv;					
				}
				
				if (chqAmt.equals("0"))
				{
					System.out.println("Cheque Amount is Zero.");
					valueXmlString.append("</Root>\r\n");
					return valueXmlString.toString(); 
				}
				//double balAmt = Double.parseDouble(advAmt);//Commented by Jiten 04/10/06
				//double balAmt1 = Double.parseDouble(advAmt);//Commented by Jiten 04/10/06
				double balAmt = Double.parseDouble(chqAmt);//Changed from advAmt to chqAmt as changed in PB
				double balAmt1 = Double.parseDouble(chqAmt);
				System.out.println("BalAmt :=" +balAmt);
				System.out.println("actionType [" +actionType + "] siteCode [" + siteCode + "] itemSer  [" + itemSer + "] custCode [" + custCode + "]" );
				if (objContext.equalsIgnoreCase("2"))
				{
				    if(actionType.equals("actionDefaultSer"))
					{
				
					        sql = "SELECT TRAN_SER, REF_NO, TOT_AMT, ADJ_AMT FROM RECEIVABLES "+
							" WHERE SITE_CODE = '"+siteCode+"' AND ITEM_SER='"+itemSer+"' AND CUST_CODE = '"+custCode+"' AND CURR_CODE = '"+currCode+"' AND TOT_AMT - ADJ_AMT > 0 ORDER BY DUE_DATE";
					
					}
					else if(actionType.equals("default"))
					{
					        sql = "SELECT TRAN_SER, REF_NO, TOT_AMT, ADJ_AMT FROM RECEIVABLES "+
							" WHERE SITE_CODE = '"+siteCode+"' AND CUST_CODE = '"+custCode+"' AND CURR_CODE = '"+currCode+"' AND TOT_AMT - ADJ_AMT > 0 ORDER BY DUE_DATE";
					
					
					}
					else if(actionType.equals("ord_receivable"))//added by kunal on 27/jan/14 for dosti client FI3JDOS002
					{
					        sql = "SELECT TRAN_SER, REF_NO, TOT_AMT, ADJ_AMT FROM RECEIVABLES "+
							" WHERE SITE_CODE = '"+siteCode+"' AND CUST_CODE = '"+custCode+"' AND CURR_CODE = '"+currCode+"' AND SALE_ORDER = '"+ sorder+"'   ORDER BY DUE_DATE ";					
					}
				}	
				else if (objContext.equalsIgnoreCase("3"))
				{
					sql = " SELECT TRAN_SER, REF_NO, TOT_AMT, ADJ_AMT FROM RECEIVABLES WHERE SITE_CODE = '"+siteCode+"' AND CUST_CODE = '"+custCode+"' AND CURR_CODE = '"+ currCode+"' AND TOT_AMT - ADJ_AMT < 0 ORDER BY DUE_DATE";  
				}
				System.out.println("sql::"+sql);
				
				stmt = conn.createStatement();
				rs = stmt.executeQuery(sql);
				double rcpAmt=0;
				double totAmt=0;
				double diffamtexch=0;
				double exchRate=0;
				double advAmt1=0;

				String tranSer = "";
				String refNo = "";
				String acctCode = "";
				String descrCurr = "";
				String descrAccnt = "";
				//aviprash Start 26/10/05
				java.sql.Date refDate = null;
				java.sql.Date dueDate = null;
				String refDate1 = "";
				String dueDate1 = "";
				//End
				while (rs.next() && balAmt1 > 0)
				{
				   
					tranSer = rs.getString(1);
					refNo = rs.getString(2);
					totAmt = rs.getDouble(3);
					advAmt1 = rs.getDouble(4);
					System.out.println("tranSer :="+tranSer);
					System.out.println("refNo :="+refNo);
					System.out.println("totAmt :="+totAmt);
					System.out.println("advAmt1 :="+advAmt1);
					rcpAmt = totAmt - advAmt1; // Take Receipt Amount
					if (tranSer.equalsIgnoreCase("R-ADV"))
					{
						rcpAmt = (rcpAmt * (-1));
					}
					if (rcpAmt > balAmt)
					{
						rcpAmt = balAmt;
					}
					balAmt1 = balAmt1 - rcpAmt;
					//balAmt = advAmt;
					
					sql1 = "SELECT TRAN_DATE, REF_DATE, EXCH_RATE, ACCT_CODE, CCTR_CODE, DUE_DATE, TOT_AMT, ADJ_AMT, DISCOUNT, TAX_AMT FROM RECEIVABLES WHERE TRAN_SER = '" + tranSer +"'" + " AND REF_NO = '"+ refNo +"'"; 
					
					rs1 = conn.createStatement().executeQuery(sql1);
					if (rs1.next())
					{
						acctCode = rs1.getString(4);
						advAmt1 =  rs1.getDouble(8);
						totAmt = rs1.getDouble(7);
						//Aviprash Start 26/10/05
						refDate = rs1.getDate(2);
						dueDate = rs1.getDate(6);
						SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
						refDate1 = sdf.format(refDate);
						dueDate1 = sdf.format(dueDate);
						
						//End
						exchRate = rs1.getDouble(3);
					}
					sql2 = "SELECT DESCR FROM CURRENCY WHERE CURR_CODE = '" + currCode +"'";				
					
					rs2 = conn.createStatement().executeQuery(sql2);
					if (rs2.next())
					{
						descrCurr = rs2.getString(1);
					}
					sql3 = "SELECT DESCR FROM ACCOUNTS WHERE ACCT_CODE = '" + acctCode +"'";
					
					rs3 = conn.createStatement().executeQuery(sql3);
					if (rs3.next())
					{
						descrAccnt = rs3.getString(1);
					}
					
					rcpAmt = totAmt	- advAmt1;
					
					if (tranSer.trim().equals("R_ADV"))
					{
						rcpAmt = (rcpAmt * (-1));
					}
					if (rcpAmt > balAmt)
					{
						rcpAmt = balAmt;
					}
					refNo = refNo.trim();
					//Aviprash add trim()26/10/05
					if (objContext.equalsIgnoreCase("2"))
					{
						
						valueXmlString.append("<Detail>\r\n");
						valueXmlString.append("<ref_ser isSrvCallOnChg='1'>").append("<![CDATA[").append(rs.getString(1).trim()).append("]]>").append("</ref_ser>\r\n");
						valueXmlString.append("<ref_no isSrvCallOnChg='1'>").append("<![CDATA[").append(rs.getString(2).trim()).append("]]>").append("</ref_no>\r\n");//Added srvCallOnChng by jiten 04/10/06
						valueXmlString.append("<rcp_amt>").append("<![CDATA[").append(rcpAmt).append("]]>").append("</rcp_amt>\r\n");
						valueXmlString.append("<curr_code>").append("<![CDATA[").append(currCode).append("]]>").append("</curr_code>\r\n");
						valueXmlString.append("<exch_rate>").append("<![CDATA[").append(exchRateHeader).append("]]>").append("</exch_rate>\r\n");
						valueXmlString.append("<exch_rate__rcv>").append("<![CDATA[").append(rs1.getString(3)).append("]]>").append("</exch_rate__rcv>\r\n");
						diffamtexch = ((rcpAmt * exchRate) - (rcpAmt * Double.parseDouble(exchRateHeader)));
						valueXmlString.append("<diff_amt__exch>").append("<![CDATA[").append(diffamtexch).append("]]>").append("</diff_amt__exch>\r\n");
						balAmt = balAmt - rcpAmt;
						valueXmlString.append("<disc_amt>").append("<![CDATA[").append(rs1.getString(9)).append("]]>").append("</disc_amt>\r\n");
						valueXmlString.append("<tax_amt>").append("<![CDATA[").append(rs1.getString(10)).append("]]>").append("</tax_amt>\r\n");
						valueXmlString.append("<net_amt>").append("<![CDATA[").append(rs1.getString(7)).append("]]>").append("</net_amt>\r\n");
						valueXmlString.append("<ref_date>").append("<![CDATA[").append(refDate1).append("]]>").append("</ref_date>\r\n");
						valueXmlString.append("<due_date>").append("<![CDATA[").append(dueDate1).append("]]>").append("</due_date>\r\n");
						valueXmlString.append("<acct_code__ar>").append("<![CDATA[").append(rs1.getString(4)).append("]]>").append("</acct_code__ar>\r\n");
						valueXmlString.append("<accounts_descr>").append("<![CDATA[").append(rs3.getString(1).trim()).append("]]>").append("</accounts_descr>\r\n");
						valueXmlString.append("<cctr_code__ar>").append("<![CDATA[").append(rs1.getString(5)).append("]]>").append("</cctr_code__ar>\r\n");
						valueXmlString.append("<cust_code>").append("<![CDATA[").append(custCode).append("]]>").append("</cust_code>\r\n");
						valueXmlString.append("<currency_descr>").append("<![CDATA[").append(rs2.getString(1).trim()).append("]]>").append("</currency_descr>\r\n");
						//End
					}
					else if (objContext.equalsIgnoreCase("3"))
					{
						
						rcpAmt = (rcpAmt * (-1));
						//Aviprash adds trim()26/10/05
						valueXmlString.append("<ref_ser isSrvCallOnChg='0'>").append("<![CDATA[").append(rs.getString(1).trim()).append("]]>").append("</ref_ser>\r\n");
						valueXmlString.append("<ref_no>").append("<![CDATA[").append(rs.getString(2).trim()).append("]]>").append("</ref_no>\r\n");
						valueXmlString.append("<curr_code>").append("<![CDATA[").append(currCode).append("]]>").append("</curr_code>\r\n");
						valueXmlString.append("<exch_rate__vouch>").append("<![CDATA[").append(rs1.getString(3)).append("]]>").append("</exch_rate__vouch>\r\n");
						valueXmlString.append("<tot_amt>").append("<![CDATA[").append(rs1.getString(7)).append("]]>").append("</tot_amt>\r\n");
						valueXmlString.append("<adj_amt>").append("<![CDATA[").append(rcpAmt).append("]]>").append("</adj_amt>\r\n");
						valueXmlString.append("<acct_code>").append("<![CDATA[").append(rs1.getString(4)).append("]]>").append("</acct_code>\r\n");
						valueXmlString.append("<cctr_code>").append("<![CDATA[").append(rs1.getString(5)).append("]]>").append("</cctr_code>\r\n");
						diffamtexch = ((advAmt1 * exchRate) - (advAmt1 * Double.parseDouble(exchRateHeader)));
						valueXmlString.append("<diff_amt__exch>").append("<![CDATA[").append(diffamtexch).append("]]>").append("</diff_amt__exch>\r\n");
						balAmt = (balAmt - (rcpAmt * (-1)));
						//End
					}					
					valueXmlString.append("</Detail>\r\n");
				}
				stmt.close();
				valueXmlString.append("</Root>\r\n");			
			}
			else
			{
				errCode = "VTDETCNT";
			}
			if (!errCode.equals(""))
			{
				errString = itmDBAccessEJB.getErrorString("",errCode,"","",conn);
				conn.close();
				conn = null;
				return errString;				
			}
		}
		catch(Exception e)
		{
			System.out.println("Exception : ReceiptAdvActEJB : actionHandler :(Document dom)" +e.getMessage());
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				System.out.println("Closing Connection.....");
				conn.close();
				conn = null;
			}
			catch(Exception e){}
		}
		return valueXmlString.toString();
	}
	 
	 
	private String getGPListDetails(Document dom, Document dom1, String objContext, String xtraParams) throws RemoteException, ITMException
	{
	    //Added by nisar on 04/01/08 for RID : SY6BAS0059
		String siteCode = null,currCode = "",hCurrCode = "",bdFluctuationCf = "N",rcpAutoFinchg = "N";
		String siteCodeInv = null, billDisc = "N",lineNoRef = "1",refSer = "",refNo = "",fchgType = "";
		String custCode = null,crTrem = "";
		String custCodeS = null;
		String detCnt = null;
		String tranSer = null;
		String sql = null;
		String exchRateHeader = null;

		String gpNo = null;
		String remark = null;
		StringBuffer valueXmlString = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root>\r\n");
		
		Date dueDate = null,refDate=null;
		PreparedStatement pstmt = null,pstmt1 = null;
		ResultSet rs = null, rs1 = null;
		Connection conn = null;
		
		double exchRateD = 0.0,exchRateRcv = 0, discExchRate = 0,billDiscAmt = 0;
		double totAmt = 0.0,adjAmt = 0.0,balance = 0.0,taxAmt = 0.0;
		double discount = 0.0,rcptAmt= 0.0,diffamtexch=0.0,badDebtAmt = 0,discountLc = 0,finChg = 0;
		
		ConnDriver connDriver = new ConnDriver();		
		ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
		GenericUtility genericUtility = GenericUtility.getInstance();
		NodeList parentNodeList = null;
		Node parentNode=null;
		String winName="";
		int days = 0;
		Date tranDate = null;
		try
		{
			FinCommon finCommon = new FinCommon();
			SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
			SimpleDateFormat dateFormat1 = new SimpleDateFormat("yyyy-mm-dd");
			//detCnt = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"detCnt"); 
			//if (detCnt.equals("0"))
			//{
			System.out.println("dom1--> "+dom1);
			conn = connDriver.getConnectDB("DriverITM");
			siteCode = genericUtility.getColumnValue("site_code",dom1);
			siteCodeInv = genericUtility.getColumnValue("site_code__inv",dom1);	
			hCurrCode = genericUtility.getColumnValue("curr_code", dom1);
			custCode = genericUtility.getColumnValue("cust_code",dom1);
			tranSer = genericUtility.getColumnValue("ref_ser",dom);
			exchRateHeader = genericUtility.getColumnValue("exch_rate",dom1);//from header
			exchRateD = Double.parseDouble(exchRateHeader==null?"0":exchRateHeader.trim());	
			parentNodeList = dom1.getElementsByTagName("Detail1");				

			//parentNode = dom.getElementsByTagName("Detail2")[0];
			parentNode = parentNodeList.item(0);
			winName=getObjName(parentNode);					
			//}
			//added new if condition by swati on 3rd june 2013 for outstanding of customer of invoicing site, RID:FI2JALL001
			if((siteCodeInv != null) && (siteCodeInv.trim().length()>0))
			{
				siteCode=siteCodeInv;				
			}
			rcpAutoFinchg = finCommon.getFinparams("999999", "RCP_AUTO_FINCHG",conn).trim();
			if (rcpAutoFinchg.equals("") || rcpAutoFinchg == null )
			{
				rcpAutoFinchg = "N";
			}
			bdFluctuationCf = finCommon.getFinparams("999999", "BD_FLUCTUATION_CF",conn);
			
			sql= "SELECT r.cust_code custcode,r.ref_no ref_no,r.tran_ser, r.gp_no dsp_gpno, r.due_date due_date," +
					" r.tot_amt,    r.adj_amt,((r.tot_amt) - (r.adj_amt)) Balance,fn_remark(r.ref_no, r.tran_ser) remark," +
					" r.tran_date, r.ref_no, r.ref_date,r.curr_code,  c.descr as curr_descr, r.exch_rate, r.acct_code, " +
					" a.descr as acct_descr,r.cctr_code, r.discount, r.tax_amt, r.bad_debt_amt, r.bill_disc,r.line_no__ref, "+
					" case when r.bad_debt_amt is null then 0 else r.bad_debt_amt end as bad_debt_amt " +
					" FROM " +
					"(("+
					" receivables r inner join customer cu on r.cust_code = cu.cust_code)   " +
					" inner join currency c on r.curr_code = c.curr_code) inner join accounts a on r.acct_code = a.acct_code   " +
					" WHERE cu.group_code in ( select group_code from customer where cust_code = '"+custCode+"')" +
					" AND r.site_code = '"+siteCode+"' AND r.tran_ser = '"+tranSer+"'  AND ( r.TOT_AMT- r.ADJ_AMT <> 0 ) " +
					" order by r.cust_code,r.ref_no,dsp_gpno,due_date asc";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();

			while(rs.next())
			{
				custCodeS = rs.getString("custcode");
				gpNo = rs.getString("dsp_gpno");
				dueDate = rs.getDate("due_date");
				refDate = rs.getDate("ref_date");
				totAmt = rs.getDouble("tot_amt");
				adjAmt = rs.getDouble("adj_amt");
				taxAmt = rs.getDouble("tax_amt");
				balance = rs.getDouble("Balance");
				remark = rs.getString("remark");
				discount = rs.getDouble("discount");
				rcptAmt = totAmt - adjAmt;
				// 04/07/14 manoharan
				refSer = rs.getString("tran_ser");
				refNo = rs.getString("ref_no");
				billDisc = rs.getString("bill_disc");
				lineNoRef = rs.getString("line_no__ref");
				currCode = rs.getString("curr_code");
				exchRateRcv = rs.getDouble("exch_rate");
				badDebtAmt = rs.getDouble("bad_debt_amt");
				
				if ((lineNoRef == null) || "null".equals(lineNoRef) || (lineNoRef.trim().length() == 0))
				{
					lineNoRef = "1";
				}
				
				if ( !hCurrCode.trim().equals(currCode.trim()) )
				{
					exchRateD = finCommon.getDailyExchRateSellBuy(currCode,"",siteCode, genericUtility.getColumnValue("tran_date", dom1), "S", conn);
				}
				else
				{
					exchRateD = Double.parseDouble(exchRateHeader==null?"0":exchRateHeader.trim());	
				}
				valueXmlString.append("<Detail>\r\n");
				valueXmlString.append("<ref_ser>").append("<![CDATA[").append(tranSer.trim()).append("]]>").append("</ref_ser>\r\n");				
				valueXmlString.append("<ref_no>").append("<![CDATA[").append(rs.getString("ref_no").trim()).append("]]>").append("</ref_no>\r\n");//Added srvCallOnChng by jiten 04/10/06
				valueXmlString.append("<line_no__ref>").append("<![CDATA[" + lineNoRef + "]]>").append("</line_no__ref>\r\n");//Added srvCallOnChng by jiten 04/10/06
				valueXmlString.append("<ref_date>").append("<![CDATA[").append(sdf.format(refDate)).append("]]>").append("</ref_date>\r\n");
				valueXmlString.append("<curr_code>").append("<![CDATA[").append(rs.getString("curr_code")).append("]]>").append("</curr_code>\r\n");
				valueXmlString.append("<exch_rate__rcv>").append("<![CDATA[").append(rs.getDouble("exch_rate")).append("]]>").append("</exch_rate__rcv>\r\n");
				valueXmlString.append("<disc_amt>").append("<![CDATA[").append(discount).append("]]>").append("</disc_amt>\r\n");
				valueXmlString.append("<tax_amt>").append("<![CDATA[0]]>").append("</tax_amt>\r\n");
				valueXmlString.append("<exch_rate>").append("<![CDATA[").append(exchRateD).append("]]>").append("</exch_rate>\r\n");
				valueXmlString.append("<net_amt>").append("<![CDATA[").append(totAmt).append("]]>").append("</net_amt>\r\n");
				valueXmlString.append("<rcp_amt>").append("<![CDATA[").append(rcptAmt).append("]]>").append("</rcp_amt>\r\n");
				if ((billDisc == null) || (billDisc.trim().length() == 0))
				{
					billDisc = "N";
				}
				if (billDisc.trim().equalsIgnoreCase("Y"))
				{
					sql = "select export_fund.exch_rate,export_fundet.amount from export_fund, export_fundet where ( export_fund.tran_id = export_fundet.tran_id ) and ( export_fund.confirmed = 'Y' ) and ( ( export_fundet.ref_ser = ? ) and ( export_fundet.ref_no = ? ) )";
					pstmt1 = conn.prepareStatement(sql);
					pstmt1.setString(1, refSer);
					pstmt1.setString(2, refNo);
					rs1 = pstmt1.executeQuery();
					if (rs1.next())
					{
						discExchRate = rs1.getDouble(1);
						billDiscAmt  = rs1.getDouble(2);
					}
					rs1.close();
					rs1 = null;
					pstmt1.close();
					pstmt1 = null;

					valueXmlString.append("<bill_disc_amt>").append("<![CDATA[" + billDiscAmt + "]]>").append("</bill_disc_amt>\r\n");
				}	
				else
				{
					valueXmlString.append("<bill_disc_amt>").append("<![CDATA[0]]>").append("</bill_disc_amt>\r\n");
				}				
				valueXmlString.append("<bill_disc>").append("<![CDATA[" + billDisc + "]]>").append("</bill_disc>\r\n");
				//Added by manoj dtd 13102012 to make workable for Customer remittence
				System.out.println("04/07/14 winName [" +  winName + "]");
				if(!"w_rcp_tr".equalsIgnoreCase(winName))
				{
					if (("Y".equals(billDisc)) && ("Y".equals(bdFluctuationCf)))
					{
						valueXmlString.append("<exch_rate>").append("<![CDATA[" + discExchRate + "]]>").append("</exch_rate>\r\n");	
						diffamtexch = (rcptAmt * exchRateRcv) - (rcptAmt * discExchRate);
						
						System.out.println("04/07/14 1 winName [" +  winName + "] rcptAmt [" + rcptAmt + "] exchRateRcv [" + exchRateRcv + "] discExchRate [" + discExchRate + "] diffamtexch [" + diffamtexch +"]");

						valueXmlString.append("<diff_amt__exch>").append("<![CDATA[" + diffamtexch + "]]>").append("</diff_amt__exch>\r\n");
					}
					else
					{
						diffamtexch = (rcptAmt * exchRateRcv) - (rcptAmt * exchRateD);
						System.out.println("04/07/14 2 winName [" +  winName + "] rcpAmt [" + rcptAmt + "] exchRateRcv [" + exchRateRcv + "] discExchRate [" + discExchRate + "] diffamtexch [" + diffamtexch +"]");
						valueXmlString.append("<diff_amt__exch>").append("<![CDATA[").append(diffamtexch).append("]]>").append("</diff_amt__exch>\r\n");
					}
				}
				valueXmlString.append("<cust_code>").append("<![CDATA[").append(custCodeS).append("]]>").append("</cust_code>\r\n");
				valueXmlString.append("<due_date>").append("<![CDATA[").append(sdf.format(dueDate)).append("]]>").append("</due_date>\r\n");
				valueXmlString.append("<acct_code__ar>").append("<![CDATA[").append(rs.getString("acct_code")).append("]]>").append("</acct_code__ar>\r\n");
				valueXmlString.append("<cctr_code__ar>").append("<![CDATA[").append(rs.getString("cctr_code")).append("]]>").append("</cctr_code__ar>\r\n");
				valueXmlString.append("<dsp_gpno>").append("<![CDATA[").append(gpNo==null?" ":gpNo.trim()).append("]]>").append("</dsp_gpno>\r\n");
				valueXmlString.append("<adj_amt>").append("<![CDATA[").append(adjAmt).append("]]>").append("</adj_amt>\r\n");
				valueXmlString.append("<balance>").append("<![CDATA[").append(balance).append("]]>").append("</balance>\r\n");
				valueXmlString.append("<remark>").append("<![CDATA[").append(remark==null?" ":remark.trim()).append("]]>").append("</remark>\r\n");
				valueXmlString.append("<due_date>").append("<![CDATA[").append(sdf.format(dueDate)).append("]]>").append("</due_date>\r\n");
			    valueXmlString.append("<bad_debt_amt>").append("<![CDATA[").append(badDebtAmt).append("]]>").append("</bad_debt_amt>\r\n");
				valueXmlString.append("<accounts_descr>").append("<![CDATA[").append(rs.getString("acct_descr").trim()).append("]]>").append("</accounts_descr>\r\n");
				valueXmlString.append("<currency_descr>").append("<![CDATA[").append(rs.getString("curr_descr").trim()).append("]]>").append("</currency_descr>\r\n");			
				discountLc = 0;
				if ("Y".equals(rcpAutoFinchg))
				{

					if ("S-INV".equals(refSer.trim()))
					{
						sql = "select due_date,cr_term from invoice where invoice_id = ?";
						pstmt1 = conn.prepareStatement(sql);
						pstmt1.setString(1, refNo);
						rs1 = pstmt1.executeQuery();
						if (rs1.next())
						{
							dueDate = rs1.getDate(1);
							crTrem = rs1.getString(2);
						}
						rs1.close();
						rs1 = null;
						pstmt1.close();
						pstmt1 = null;

						tranDate = new java.sql.Date(new java.util.Date().getTime()); // dateFormat1.parse(genericUtility.getColumnValue("tran_date", dom1));
						if (tranDate.before(dueDate))
						{
							days = (int)(dueDate.getTime() - tranDate.getTime()) / 361440000;
							if (days > 0)
							{
								sql = "select fchg_type,case when fin_chg is null then 0 else fin_chg end from crtermfc where (min_day <= ? and max_day >= ? ) and cr_term = ?";

								pstmt1 = conn.prepareStatement(sql);
								pstmt1.setInt(1, days);
								pstmt1.setInt(2, days);
								pstmt1.setString(3, crTrem);
								rs1 = pstmt1.executeQuery();
								if (rs1.next())
								{
									finChg = rs1.getDouble(1);
									fchgType = rs1.getString(2);

									if (finChg == 0.0D) finChg = 0.0D;
									if (discountLc == 0.0D) discountLc = 0.0D;
									if ("P".equals(fchgType))
									{
										discountLc = rcptAmt * (finChg / 36500.0D) * days;
									}
									else
									{
										discountLc = finChg;
									}
									discountLc = Math.round(discountLc) * -1L;
								}
								rs1.close();
								rs1 = null;
								pstmt1.close();
								pstmt1 = null;
							}
						}
					}
				}
				valueXmlString.append("<fin_chg>").append("<![CDATA[" + discountLc + "]]>").append("</fin_chg>\r\n");
				
				valueXmlString.append("</Detail>\r\n");

			}//end of while(rs.next();
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			valueXmlString.append("</Root>\r\n");		
		}
		catch(Exception e)
		{
			System.out.println("Exception : ReceiptAdvActEJB : actionHandler :(Document dom)" +e.getMessage());
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				System.out.println("Closing Connection.....");
				conn.close();
				conn = null;
			}
			catch(Exception e){}
		}
		return valueXmlString.toString();		
	}//End of getGPListDetails

	private String gpListTransform(Document dom,Document dom1, String objContext, String xtraParams, Document selDataDom)throws ITMException
	{
		System.out.println("[ReceiptAdvActEJB] gpListTransform is calling.............");
		Connection conn = null;
		StringBuffer valueXmlString = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root>\r\n");
		Node currDetail1 = null; 
		int count = 0, ctr = 0;
		double stkQty = 0;
		ConnDriver connDriver = new ConnDriver();
		ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
		GenericUtility genericUtility = GenericUtility.getInstance();
		String lineNoRef = "1", refSer = "", refNo = "", sql = "",billDisc = "N";
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try
		{
			conn = connDriver.getConnectDB("DriverITM");
			NodeList detailList = selDataDom.getElementsByTagName("Detail");
			int noOfDetails = detailList.getLength();
			/*for(int ctr = 0; ctr < noOfDetails; ctr++)
			{
				currDetail = detailList.item(ctr);
			}*/
			if (noOfDetails > 0)
			{
				for(int ctrTmp = 0; ctrTmp < noOfDetails ; ctrTmp++)
				{
					valueXmlString.append("<Detail>");
					currDetail1 = detailList.item(ctrTmp);

					valueXmlString.append("<ref_ser isSrvCallOnChg='0'>").append("<![CDATA[").append(genericUtility.getColumnValueFromNode("ref_ser", currDetail1)).append("]]>").append("</ref_ser>\r\n");
					valueXmlString.append("<ref_no isSrvCallOnChg='0'>").append("<![CDATA[").append(genericUtility.getColumnValueFromNode("ref_no", currDetail1)).append("]]>").append("</ref_no>\r\n");//Added srvCallOnChng by jiten 04/10/06
					valueXmlString.append("<line_no__ref isSrvCallOnChg='0'>").append("<![CDATA[" + lineNoRef + "]]>").append("</line_no__ref>\r\n");//Added srvCallOnChng by jiten 04/10/06
					valueXmlString.append("<rcp_amt isSrvCallOnChg='0'>").append("<![CDATA[").append(genericUtility.getColumnValueFromNode("rcp_amt", currDetail1)).append("]]>").append("</rcp_amt>\r\n");
					valueXmlString.append("<curr_code isSrvCallOnChg='0'>").append("<![CDATA[").append(genericUtility.getColumnValueFromNode("curr_code", currDetail1)).append("]]>").append("</curr_code>\r\n");
					valueXmlString.append("<exch_rate isSrvCallOnChg='0'>").append("<![CDATA[").append(genericUtility.getColumnValueFromNode("exch_rate", currDetail1)).append("]]>").append("</exch_rate>\r\n");
					valueXmlString.append("<exch_rate__rcv isSrvCallOnChg='0'>").append("<![CDATA[").append(genericUtility.getColumnValueFromNode("exch_rate__rcv", currDetail1)).append("]]>").append("</exch_rate__rcv>\r\n");
					valueXmlString.append("<diff_amt__exch isSrvCallOnChg='0'>").append("<![CDATA[").append(genericUtility.getColumnValueFromNode("diff_amt__exch", currDetail1)).append("]]>").append("</diff_amt__exch>\r\n");
					valueXmlString.append("<disc_amt isSrvCallOnChg='0'>").append("<![CDATA[").append(genericUtility.getColumnValueFromNode("disc_amt", currDetail1)).append("]]>").append("</disc_amt>\r\n");
					valueXmlString.append("<tax_amt isSrvCallOnChg='0'>").append("<![CDATA[0.00]]>").append("</tax_amt>\r\n");
					valueXmlString.append("<net_amt isSrvCallOnChg='0'>").append("<![CDATA[").append(genericUtility.getColumnValueFromNode("net_amt", currDetail1)).append("]]>").append("</net_amt>\r\n");
					valueXmlString.append("<ref_date isSrvCallOnChg='0'>").append("<![CDATA[").append(genericUtility.getColumnValueFromNode("ref_date", currDetail1)).append("]]>").append("</ref_date>\r\n");
					valueXmlString.append("<due_date isSrvCallOnChg='0'>").append("<![CDATA[").append(genericUtility.getColumnValueFromNode("due_date", currDetail1)).append("]]>").append("</due_date>\r\n");
					valueXmlString.append("<acct_code__ar isSrvCallOnChg='0'>").append("<![CDATA[").append(genericUtility.getColumnValueFromNode("acct_code__ar", currDetail1)).append("]]>").append("</acct_code__ar>\r\n");
					valueXmlString.append("<accounts_descr isSrvCallOnChg='0'>").append("<![CDATA[").append(genericUtility.getColumnValueFromNode("accounts_descr", currDetail1)).append("]]>").append("</accounts_descr>\r\n");
					valueXmlString.append("<cctr_code__ar isSrvCallOnChg='0'>").append("<![CDATA[").append(genericUtility.getColumnValueFromNode("cctr_code__ar", currDetail1)).append("]]>").append("</cctr_code__ar>\r\n");
					valueXmlString.append("<cust_code isSrvCallOnChg='0'>").append("<![CDATA[").append(genericUtility.getColumnValueFromNode("cust_code", currDetail1)).append("]]>").append("</cust_code>\r\n");
					valueXmlString.append("<currency_descr isSrvCallOnChg='0'>").append("<![CDATA[").append(genericUtility.getColumnValueFromNode("currency_descr", currDetail1)).append("]]>").append("</currency_descr>\r\n");			
					valueXmlString.append("<dsp_gpno isSrvCallOnChg='0'>").append("<![CDATA[").append(genericUtility.getColumnValueFromNode("dsp_gpno", currDetail1)).append("]]>").append("</dsp_gpno>\r\n");			
					valueXmlString.append("<bill_disc_amt isSrvCallOnChg='0'>").append("<![CDATA[").append(genericUtility.getColumnValueFromNode("bill_disc_amt", currDetail1)).append("]]>").append("</bill_disc_amt>\r\n");
					valueXmlString.append("<bill_disc isSrvCallOnChg='0'>").append("<![CDATA[").append(genericUtility.getColumnValueFromNode("bill_disc", currDetail1)).append("]]>").append("</bill_disc>\r\n");
					valueXmlString.append("<bad_debt_amt isSrvCallOnChg='0'>").append("<![CDATA[").append(genericUtility.getColumnValueFromNode("bad_debt_amt", currDetail1)).append("]]>").append("</bad_debt_amt>\r\n");
					valueXmlString.append("<fin_chg isSrvCallOnChg='0'>").append("<![CDATA[").append(genericUtility.getColumnValueFromNode("fin_chg", currDetail1)).append("]]>").append("</fin_chg>\r\n");
					
					
					valueXmlString.append("</Detail>");
					
				}// for end
			}// end if
			valueXmlString.append("</Root>");
		}// end try
		catch(ITMException itme)
		{
			throw itme;
		}
		catch(Exception e)
		{
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				System.out.println("Closing Connection...");
				conn.close();
				conn = null;
			}
			catch (Exception e){}
		}
		System.out.println("[ReceiptAdvActEJB] gpListTransform() : valueXmlString from :"+valueXmlString.toString());
		return valueXmlString.toString();
	}
	
	
	
	private String getObjName(Node node) throws Exception

	{

		String objName = null;

		NodeList nodeList = null;

		Node detaulNode = null;

		Node detailNode = null;

		nodeList = node.getChildNodes();

		NamedNodeMap attrMap = node.getAttributes();

		objName = attrMap.getNamedItem( "objName" ).getNodeValue();

		/*

		for(int ctr = 0; ctr < nodeList.getLength(); ctr++ )

		{

			detailNode = nodeList.item(ctr);

			if(detailNode.getNodeName().equalsIgnoreCase("attribute") )

			{

				objName = detailNode.getAttributes().getNamedItem("objName").getNodeValue();

			}

		}

		*/

		return "w_" + objName;
	}
	

}