/**
 * @author : Akhilesh Sikarwar 
 * @Version : 1.0
 * Date : 02/06/13
 */
package ibase.webitm.ejb.fin.adv;


//import ibase.edi.client.system.config.AppConnectParm;
import ibase.system.config.ConnDriver;
import ibase.utility.CommonConstants;
import ibase.webitm.ejb.ActionHandlerEJB;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.ejb.MasterStatefulLocal;
import ibase.webitm.ejb.ValidatorEJB;
import ibase.webitm.ejb.dis.DistCommon;
import ibase.webitm.ejb.fin.FinCommon;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;
import ibase.webitm.utility.TransIDGenerator;
import ibase.webitm.ejb.sys.UtilMethods;
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.Date;
import java.util.HashMap;
import java.util.Hashtable;

import java.util.Properties;
import org.omg.CORBA.ORB;
import java.util.*;
import javax.ejb.*;
import ibase.system.config.*;


//import javax.ejb.Stateless;
import javax.naming.InitialContext;

import org.apache.poi.hssf.record.formula.functions.Today;
//@Stateless
public class CalculateCommission
{
	
	String tranIdDrn = "",tranIdCrn = "";
	UtilMethods utilMethods = UtilMethods.getInstance();

	public String CalCommission(String invoiceId , String callModule , String tranId ,String postCrn ,String postJv ,String xtraParams, Connection conn ) throws Exception
	{
		String commCalc = "";
		String errString = "";
		String sql = "";
		String sql1 = "";
		String refSer = "";
		String invno = "";
		String invFound = "";
		ArrayList<String> invList = null;
		double checkAmt = 0.0;
		double checkAdjAmt = 0.0;


		int facter = 0;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		PreparedStatement pstmt1 = null;
		ResultSet rs1 = null;

		FinCommon finCommon = null;
		finCommon = new  FinCommon();
		ITMDBAccessEJB itmDBAccessEJB = null;
		itmDBAccessEJB = new ITMDBAccessEJB();


		try {
			commCalc = finCommon.getFinparams("999999", "COMM_CALC", conn);
			if(!("I".equalsIgnoreCase(commCalc)) && !("R".equalsIgnoreCase(commCalc)) && !("N".equalsIgnoreCase(commCalc)) && !("P".equalsIgnoreCase(commCalc)))
			{
				errString = itmDBAccessEJB.getErrorString("","VTFINPARM","","",conn);
				return errString;
			}
			else
			{
				if("I".equalsIgnoreCase(callModule))
				{
					if("I".equalsIgnoreCase(commCalc))

					{	
						facter = 1;
						//ls_errcode = gbf_comm_credit_note(as_invoiceid,lc_factor,as_post_crn,as_call_module)
						errString = commDebitNote(invoiceId, facter, tranId,  postCrn, callModule,xtraParams, conn);
						return errString;

					}
					else if("R".equalsIgnoreCase(commCalc))
					{
						//ls_errcode = gbf_comm_journal(as_invoiceid,as_post_jv,as_call_module)
					}
					else if("P".equalsIgnoreCase(commCalc))
					{
						//ls_errcode = gbf_prov_comm_credit_note(as_invoiceid,lc_factor,as_post_crn,as_call_module)
					}
				}
				else if("R".equalsIgnoreCase(callModule)) 
				{
					invList = new ArrayList<String>();
					if("P".equalsIgnoreCase(commCalc))
					{
						// called from receipt
						sql = "select rcpdet.ref_ser, rcpdet.ref_no, ( rcpdet.rcp_amt - ( rcpdet.tax_amt * rcpdet.rcp_amt / rcpdet.net_amt ) ) / (inv_amt - invoice.disc_amt)" +
								"from rcpdet, invoice where rcpdet.ref_no = invoice.invoice_id and tran_id = ?" +
								" and ( ref_ser = 'S-INV' || ref_ser like 'R-IBC%'  )";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, tranId);
						rs = pstmt.executeQuery();
						while(rs.next())
						{
							refSer = rs.getString(1);
							invno = rs.getString(2);
							facter = rs.getInt(3);
							// Added by fatema - 30/04/2007 - DI7CHT0010
							invFound = "N";
							for(int cnt = 0 ; cnt < invList.size();cnt++)
							{
								if( invno.trim().equalsIgnoreCase( ((String) invList.get(cnt)).trim() ) )
								{
									invFound = "Y";
									break;
								}
							}
							if ("Y".equals(invFound))
							{
								continue;
							}
							// Ended by fatema - 30/04/2007 - DI7CHT0010
							// create debit note for invoice = ls_invno and factor = lc_factor

							sql1 = "select case when rcp_amt is null then 0 else rcp_amt end from rcpdet" +
									"where tran_id = ? and ref_ser = ? and ref_no  = ?";

							pstmt1 = conn.prepareStatement(sql1);
							pstmt1.setString(1, tranId);
							pstmt1.setString(2, refSer);
							pstmt1.setString(3, invno);
							rs1 = pstmt1.executeQuery();
							if(rs1.next())

							{
								checkAmt = rs1.getDouble(1);

							}
							rs1.close();
							rs1 = null;
							pstmt1.close();
							pstmt1 = null;	

							sql1 = " Select (TOT_AMT - ADJ_AMT) from receivables  where tran_ser = ? and ref_no = ?";
							pstmt1 = conn.prepareStatement(sql1);
							pstmt1.setString(1, refSer);
							pstmt1.setString(2, invno);
							rs1= pstmt1.executeQuery();
							if(rs1.next())
							{
								checkAdjAmt = rs1.getDouble(1);
							}

							rs1.close();
							rs1 = null;
							pstmt1.close();
							pstmt1 = null;	

							if(Math.round(checkAdjAmt) == 0) // Added by fatema-30/04/2007-DI7CHT0010-Coz this func is called after the receipt posting & so the invoce is already adjusted
							{
								errString = commDebitNote(invno, facter, tranId, postCrn, callModule,xtraParams, conn);
								if (errString == null || errString.trim().length() == 0)
								{
									invList.add(invno); // Added by fatema-30/04/2007-DI7CHT0010
								}
								else
								{
									break;
								}
									
							}
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;	
						if (errString == null || errString.trim().length() == 0)
						{
							return errString;
						}
					}
					if( ("R".equalsIgnoreCase(commCalc) || "P".equalsIgnoreCase(commCalc)) && (errString == null || errString.trim().length() == 0))
					{
					
						invList = null;
						invList = new ArrayList<String>();// Added by fatema - 30/04/2007 - DI7CHT0010
						sql = "select rcpdet.ref_ser, rcpdet.ref_no, ( rcpdet.rcp_amt - ( rcpdet.tax_amt * rcpdet.rcp_amt / rcpdet.net_amt ) ) / (inv_amt - invoice.disc_amt)" +
								"from rcpdet, invoice where rcpdet.ref_no = invoice.invoice_id and tran_id = ?" +
								" and ( ref_ser = 'S-INV' 	OR ref_ser like 'R-IBC%'  )";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, tranId);
						rs = pstmt.executeQuery();
						while(rs.next())
						{
							refSer = rs.getString(1);
							invno = rs.getString(2);
							facter = rs.getInt(3);
							// Added by fatema - 30/04/2007 - DI7CHT0010
							invFound = "N"; 
							for(int cnt = 0 ; cnt < invList.size();cnt++)
							{
								if( invno.trim().equalsIgnoreCase( ((String) invList.get(cnt)).trim() ) )
								{
									invFound = "Y";
									break;
								}
							}
							if ("Y".equals(invFound))
							{
								continue;
							}
							// Ended by fatema - 30/04/2007 - DI7CHT0010
							sql1 = "Select case when rcp_amt is null then 0 else rcp_amt end from rcpdet where tran_id = ?" +
									" and ref_ser = ? and  ref_no  = ?";
							pstmt1 = conn.prepareStatement(sql1);
							pstmt1.setString(1, tranId);
							pstmt1.setString(2, refSer);
							pstmt1.setString(3, invno);
							rs1 = pstmt1.executeQuery();
							if(rs1.next())
							{
								checkAmt = rs1.getDouble(1);
							}
							rs1.close();
							rs1 = null;
							pstmt1.close();
							pstmt1 = null;	

							sql1 = "Select (TOT_AMT - ADJ_AMT)  from receivables where tran_ser = ? and 	ref_no = ?" ;
							pstmt1 = conn.prepareStatement(sql1);
							pstmt1.setString(1, refSer);
							pstmt1.setString(2, invno);
							rs1 = pstmt1.executeQuery();
							if(rs1.next())
							{
								checkAdjAmt = rs1.getDouble(1);
							}

							rs1.close();
							rs1 = null;
							pstmt1.close();
							pstmt1 = null;	
							// Added by fatema-30/04/2007-DI7CHT0010-Coz this func is called after the receipt posting & so the invoce is already adjusted
							if(Math.round(checkAdjAmt) == 0)
							{
								errString = commCrditNote(invno, facter, tranId, postCrn, callModule,xtraParams, conn);
								if (errString == null || errString.trim().length() == 0)
								{
									invList.add(invno);
								}
								else
								{
									break;
								}
							}

						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;	
						if (errString == null || errString.trim().length() == 0)
						{
							return errString;
						}

					}
				}
				else if("SR".equalsIgnoreCase(callModule) && "P".equalsIgnoreCase(commCalc))
				{
					// To create DB-note for reversal of prov. CR-Note 
					// Added by fatema-11/06/2007-DI7CHT0010 - To continue process only if invoice is not completely adjusted

					sql = " Select sum((case when tot_amt is null then 0 else tot_amt end) - (case when adj_amt is null then 0 else adj_amt end)) from receivables " +
							"where (tran_ser = 'S-INV' or tran_ser like 'R-IBC%') and  ref_no = ?";

					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, tranId);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						checkAdjAmt = rs.getDouble(1);
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;	

					if(Math.round(checkAdjAmt)> 0) // Ended by fatema-11/06/2007-DI7CHT0010
					{

						errString = commDebitNote(tranId, facter, tranId, postCrn, callModule ,xtraParams, conn);
						return errString;

					}
					// Added by fatema-11/06/2007-DI7CHT0010 

				}

			}

		} catch (ITMException e) {
			System.out.println(e.getMessage());
			System.out.println("Exception : "+e);
			e.printStackTrace();
			throw new ITMException(e);
		}

		return errString;
	}


	private String commDebitNote(String invno, int factor, String tranIdRcp, String post,String calledFrom,String xtraParams, Connection conn) throws Exception
	{	
		String commCalc = "";
		String errString = "";
		String keyString = "";
		String sql = "";
		String refSer = "";
		String acctcodeExp = "";
		String cctrcodeExp = "";
		String invFound = "";
		String sretId = "";
		String sreCurrCode = "";
		String sreSiteCode = "";
		String tranIdCrn = "";
		String invoiceNo = "";
		String acctCodeCf = "";
		String cctrCodeCf = "";
		String saleOrder = "";
		double commPerc = 0, commPerc1 = 0, commperc2 = 0;
		String commHdr = "";
		String crTerm  = "";
		String taxClass  = "";
		String taxChap = "";
		String invoiceId = "";
		String refLineNo = "";
		String itemSer = "";
		String currCode = "";
		String finEntity = "";
		String siteCode = "";
		String salesPers = "";
		String dlvTerm = "";
		String sorderNo = "";
		String currCodeSp = "";
		//String exchRateSp = ""; 
		String commPercOn = "";		 
		//String commPerc = "";
		String oldSp = "";
		String currcdSpOld = "";
		String acctCode = "";
		String cctrCode = "";
		String insReqd  = "";
		String frtReqd  = "";
		String refser = "";
		String siteCodeCd = "";
		String currCodeBase = "";
		String tranId = "";
		String idDate = "";
		String tranType = "";
		String oldsp = "";
		String currcodeSp ="";
		String fchgType = "";
		String acctcodeDeduct = "";
		String cctrcodeDeduct = "";
		String transer = "DRNINV";
		String	trnofld = "tran_id";
		String remarks = "";
		String rcpRefser = "";
		String autoTranId = "";
		String curr = "";
		String site = "";
		String invibcagen = "";
		String sundryCode = "";
		String sundryType = "";
		String acct = "";
		String cctr = "";
		String siteCodePay = "";
		String linkType = "";
		String acctCodePay = "";
		String cctrCodePay = "";
		String acctCodeRcp = "";
		String cctrCodeRcp = "";
		String ibcaId = "";
		String itemCode = "";
		String commFrt = "";
		String commInsu = "";
		String finent = "";
		String taxEnv = "";
		String arg2 = "";
		String arg1 = "";
		String finEntityFr = "";
		String refType = "";
		String finEntityTo = "";
		String xmlString = "";
		StringBuffer xmlBuff = null;
		String loginEmpCode = "";
		double commAmt = 0.0;
		double ordAmt = 0.0;
		double insuAmt = 0.0;
		double invAmt = 0.0;
		double frtAmt = 0.0; 
		double exchRate = 0.0;
		double rcpAmount = 0.0;
		double checkAmt = 0.0;
		double checkAdjAmt = 0.0;
		double sretNetAmt = 0.0;
		double sretExchRate = 0.0;
		double netAmt = 0.0;
		double adjAmt = 0.0 ;
		double sretPerc = 0.0;
		double jvExchrate = 0.0;
		double crntExchrate = 0.0;
		double exchRateSp = 0.0;
		double rcpExchRateTot = 0.0;
		double lineNoInv  = 0.0;
		double invLineNo = 0.0;
		double invNetamt = 0.0 ;
		double amount = 0.0;
		double drcrAmt = 0.0;
		double taxAmt  = 0.0;
		double exchRateDiffTot = 0.0 ;
		double exchRateDiff = 0.0 ;
		double totNetAmt = 0.0;
		double netSAmt = 0.0;
		double expAmt = 0.0;
		double finChg = 0.0;
		double drcrmt = 0.0;
		double amountBc = 0.0; 
		double exch = 0.0;
		double amtInv = 0.0;
		double rateStduom = 0.0;
		double invDisc = 0.0;
		double discount = 0.0;

		PreparedStatement pstmt = null;
		ResultSet rs = null;

		SimpleDateFormat sdf  = null;
		GenericUtility genericUtility = null;

		ValidatorEJB validatorEJB = null;
		validatorEJB = new ValidatorEJB();
		java.sql.Timestamp  tranDate = null;
		java.sql.Timestamp  effDate = null;
		java.sql.Timestamp  dueDate =null;
		java.sql.Timestamp date = null;
		java.sql.Timestamp chgDate =  new java.sql.Timestamp(System.currentTimeMillis()) ;
		java.sql.Timestamp taxDate = chgDate;
		
		int facter = 0;
		int totalrow = 0;
		int crdays = 0;
		int count = 0;
		long day = 0;
		int slineNo = 0;
		int lineNo = 0;

		FinCommon finCommon = null;
		finCommon = new  FinCommon();
		ITMDBAccessEJB itmDBAccessEJB = null;
		itmDBAccessEJB = new ITMDBAccessEJB();

		HashMap commMap = new HashMap();
		ArrayList commtList = new ArrayList();
		ArrayList<String> drnList = null;
		try 
		{
			if ((!"null".equals(tranIdRcp)) && tranIdRcp != null && tranIdRcp.trim().length() > 0 )
			{
				remarks = "Agst Receipt # [" + tranIdRcp + "] of Invoice # [" + invno + "]"; //tran_id__rcv
			}
			else
			{
				remarks = "Agst Invoice # [" + invno + "]";
			}

			loginEmpCode = validatorEJB.getValueFromXTRA_PARAMS(xtraParams, "loginEmpCode");
			sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
			taxDate = java.sql.Timestamp.valueOf(sdf.format(chgDate) + " 00:00:00.000");
			date = taxDate;
			
			sql = " select key_string from transetup where upper(tran_window) = 'W_DRCRINV'";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if(rs.next())		
			{
				keyString = rs.getString(1);
			}
			else
			{
				sql = " select key_string from transetup where upper(tran_window) = 'GENERAL'";
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					keyString = rs.getString(1);
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;

			// get the a/c code for commission expense a/c
			if("I".equalsIgnoreCase(calledFrom) || "SR".equalsIgnoreCase(calledFrom))
			{
				acctcodeExp = finCommon.getFinparams("999999", "COMM_EXP_ACCT", conn);
				if("NULLFOUND".equalsIgnoreCase(acctcodeExp))
				{
					errString = itmDBAccessEJB.getErrorString("","VTFINPARM ","","",conn);
					return errString;

				}else

				{
					cctrcodeExp = finCommon.getFinparams("999999", "COMM_EXP_CCTR", conn);
					if("NULLFOUND".equalsIgnoreCase(cctrcodeExp))
					{
						errString = itmDBAccessEJB.getErrorString("","VTFINPARM ","","",conn);
						return errString;

					}
				}
			}
			else 
			{
				acctcodeExp = finCommon.getFinparams("999999", "SAL_COM_PROV_REV_ACT", conn);
				if("NULLFOUND".equalsIgnoreCase(acctcodeExp))
				{
					errString = itmDBAccessEJB.getErrorString("","VTFINPARM ","","",conn);
					return errString;

				}
				cctrcodeExp = finCommon.getFinparams("999999", "SAL_COM_PROV_REV_CTR", conn);
				if("NULLFOUND".equalsIgnoreCase(cctrcodeExp))
				{
					errString = itmDBAccessEJB.getErrorString("","VTFINPARM ","","",conn);
					return errString;

				}

			}

			commCalc = finCommon.getFinparams("999999", "COMM_CALC", conn);
			
			
			if(("SR".equalsIgnoreCase(calledFrom)) && ("P".equalsIgnoreCase(commCalc)))
			{
				sretId = invno;
				invno = "";

				sql = "select invoice_id, net_amt, curr_code, exch_rate, site_code, tran_id__crn" +
						"from sreturn where tran_id = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, sretId);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					invno = rs.getString("invoice_id");
					sretNetAmt = rs.getDouble("net_amt");
					sreCurrCode = rs.getString("curr_code");
					sretExchRate = rs.getDouble("exch_rate");
					sreSiteCode = rs.getString("site_code");
					tranIdCrn = rs.getString("tran_id__crn");
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;

				if(invno==  null || invno.trim().length() == 0)
				{
					return errString;
				}

				sql = "select net_amt from invoice  where invoice_id= ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, invno);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					netAmt = rs.getDouble("net_amt");
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;

				if(tranIdCrn != null && tranIdCrn.trim().length() > 0)
				{
					sql = "select amount  from drcr_rcp where tran_id= ?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, tranIdCrn);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						rcpAmount = rs.getDouble("amount");
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;

					sretNetAmt = rcpAmount;
				}

			}
			//  get acct_code for currency fluctuation 
			invoiceNo = invno;
			sql = "select acct_code__cf, cctr_code__cf from bank where bank_code =(Select bank_code from site where" +
					" site_code = (select site_code from invoice where invoice_id = ?))";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, invoiceNo);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				acctCodeCf = rs.getString("acct_code__cf");
				cctrCodeCf = rs.getString("cctr_code__cf");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;

			sql = "select sale_order from invoice where invoice_id = ?" ;
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, invoiceNo);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				saleOrder = rs.getString("sale_order");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;

			sql = "select comm_perc, comm_perc_1, comm_perc_2 from	sorder  where sale_order = ?" ;
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, saleOrder);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				commPerc = rs.getDouble("comm_perc");
				commPerc1 = rs.getDouble("comm_perc_1");
				commperc2 = rs.getDouble("comm_perc_2");
			}

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

			commHdr = "N";
			if((commPerc != 0)|| (commPerc1 != 0) || (commperc2 != 0))
			{
				commHdr = "Y";
			}
			if("Y".equalsIgnoreCase(commHdr))
			{
				sql = "select invoice_id, ref_line_no, inv_amt, frt_amt, item_ser, tran_date, eff_date, curr_code, exch_rate, fin_entity, site_code," +
						" sales_pers,comm_perc__on,comm_perc,dlv_term, ord_amt, insu_amt,sorder_no, curr_code_sp, exch_rate_sp,comm_amt from (" +
						" select DISTINCT a.invoice_id, 1 as ref_line_no, a.inv_amt inv_amt, a.frt_amt * d.exch_rate__frt / a.exch_rate frt_amt," +
						" a.item_ser item_ser, a.tran_date tran_date, a.eff_date eff_date,a.curr_code curr_code, a.exch_rate exch_rate, a.fin_entity fin_entity," +
						" a.site_code site_code,d.sales_pers sales_pers,(case when (case when d.comm_perc__on is null then ' ' else d.comm_perc__on end) = ' ' " +
						" then c.comm_perc_on_1 else d.comm_perc__on end) comm_perc__on, (case when (case when c.comm_perc_1 is null then 0 else c.comm_perc_1 end)  = 0 " +
						" then (case when d.comm_perc is null then 0 else d.comm_perc end) else (case when c.comm_perc_1 is null then 0 else c.comm_perc_1 end) end) comm_perc," +
						" d.dlv_term dlv_term, d.ord_amt ord_amt, (d.ins_amt * d.exch_rate__ins / d.exch_rate__comm) insu_amt,d.sale_order sorder_no, d.curr_code__comm curr_code_sp, " +
						" d.exch_rate__comm exch_rate_sp, (case when (case when b.sales_pers_comm_1 is null then 0 else b.sales_pers_comm_1 end) = 0 " +
						" then a.sales_pers_comm_1 else b.sales_pers_comm_1 end) comm_amt from invoice a, invdet b, sorddet c, sorder d 	where a.invoice_id = b.invoice_id  " +
						" and b.sord_no = c.sale_order and b.sord_line_no = c.line_no  and c.sale_order = d.sale_order  and a.invoice_id = ? and " +
						" (case when d.sales_pers is null then ' ' else d.sales_pers end) <> ' ' UNION ALL select DISTINCT a.invoice_id, 2 as ref_line_no, a.inv_amt inv_amt," +
						"  a.frt_amt * d.exch_rate__frt / a.exch_rate frt_amt, a.item_ser item_ser, a.tran_date tran_date, a.eff_date eff_date, a.curr_code curr_code,  a.exch_rate exch_rate," +
						"  a.fin_entity fin_entity, a.site_code site_code, d.sales_pers__1 sales_pers, (case when (case when d.comm_perc_on_1 is null then ' ' else d.comm_perc_on_1 end) = ' ' " +
						" then c.comm_perc_on_2 else d.comm_perc_on_1 end) comm_perc__on, (case when (case when c.comm_perc_2 is null then 0 else c.comm_perc_2 end) = 0 then " +
						" (case when d.comm_perc_1 is null then 0 else d.comm_perc_1 end) else (case when c.comm_perc_2 is null then 0 else c.comm_perc_2 end) end) comm_perc," +
						" d.dlv_term dlv_term, d.ord_amt ord_amt, (d.ins_amt * d.exch_rate__ins / d.exch_rate__comm_1) insu_amt, d.sale_order sorder_no, d.curr_code__comm_1 curr_code_sp, " +
						" d.exch_rate__comm_1 exch_rate_sp,(case when (case when b.sales_pers_comm_2 is null then 0 else b.sales_pers_comm_2 end) = 0 " +
						" then a.sales_pers_comm_2 else b.sales_pers_comm_2 end) comm_amt from invoice a,invdet b, sorddet c, sorder d 	where a.invoice_id = b.invoice_id " +
						" and b.sord_no = c.sale_order 	and b.sord_line_no = c.line_no and c.sale_order = d.sale_order  and a.invoice_id = ?  " +
						" and (case when d.sales_pers__1 is null then ' ' else d.sales_pers__1 end) <> ' ' UNION ALL SELECT DISTINCT a.invoice_id, 3 as ref_line_no, " +
						" a.inv_amt inv_amt, a.frt_amt * d.exch_rate__frt / a.exch_rate frt_amt, a.item_ser item_ser, a.tran_date tran_date, a.eff_date eff_date, a.curr_code curr_code," +
						" a.exch_rate exch_rate, a.fin_entity fin_entity, a.site_code site_code, d.sales_pers__2 sales_pers, (case when (case when d.comm_perc_on_2 is null then ' ' else d.comm_perc_on_2 end) = ' ' " +
						" then c.comm_perc_on_3 else d.comm_perc_on_2 end) comm_perc__on, (case when (case when c.comm_perc_3 is null then 0 else c.comm_perc_3 end) = 0 then " +
						" (case when d.comm_perc_2 is null then 0 else d.comm_perc_2 end) else (case when c.comm_perc_3 is null then 0 else c.comm_perc_3 end) end) comm_perc, d.dlv_term dlv_term, d.ord_amt ord_amt, " +
						" (d.ins_amt * d.exch_rate__ins / d.exch_rate__comm_2) insu_amt , d.sale_order sorder_no, d.curr_code__comm_2 curr_code_sp, d.exch_rate__comm_2 exch_rate_sp," +
						" (case when (case when b.sales_pers_comm_3 is null then 0 else b.sales_pers_comm_3 end) = 0 then a.sales_pers_comm_3 else b.sales_pers_comm_3 end) comm_amt " +
						" from invoice a, invdet b, sorddet c, sorder d where a.invoice_id = b.invoice_id and b.sord_no = c.sale_order 	and b.sord_line_no = c.line_no 	and c.sale_order = d.sale_order  " +
						" and a.invoice_id = ? and (case when d.sales_pers__2 is null then ' ' else d.sales_pers__2 end) <> ' ' ) commtab  where " +
						"((case when sales_pers is null then ' ' else sales_pers end) <> ' ')  and	((case when comm_perc__on is null then ' ' else comm_perc__on end) <> ' ') " +
						" and comm_amt > 0 order by invoice_id, sales_pers, comm_perc__on";

				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, invoiceNo);
				pstmt.setString(2, invoiceNo);
				pstmt.setString(3, invoiceNo);

				rs = pstmt.executeQuery();
				while(rs.next())
				{
					commMap.put("invoice_id",rs.getString("invoice_id"));
					commMap.put("ref_line_no" ,rs.getString("ref_line_no"));
					commMap.put("inv_amt" ,rs.getDouble("inv_amt"));
					commMap.put("frt_amt" ,rs.getDouble("frt_amt"));
					commMap.put("item_ser" ,rs.getString("item_ser"));
					commMap.put("tran_date" ,rs.getTimestamp("tran_date"));
					commMap.put("eff_date " ,rs.getTimestamp("eff_date"));
					commMap.put("curr_code" ,rs.getString("curr_code"));
					commMap.put("exch_rate" , rs.getDouble("exch_rate"));
					commMap.put("fin_entity" , rs.getString("fin_entity"));		
					commMap.put("site_code" , rs.getString("site_code"));	
					commMap.put("sales_pers" , rs.getString("sales_pers"));
					commMap.put("comm_perc__on" , rs.getString("comm_perc__on"));
					commMap.put("comm_perc" , rs.getDouble("comm_perc"));
					commMap.put("dlv_term" , rs.getString("dlv_term"));					
					commMap.put("ord_amt" , rs.getDouble("ord_amt"));
					commMap.put("insu_amt" , rs.getDouble("insu_amt"));
					commMap.put("sorder_no" , rs.getString("sorder_no"));
					commMap.put("curr_code_sp" , rs.getString("curr_code_sp"));
					commMap.put("exch_rate_sp" , rs.getDouble("exch_rate_sp"));

					commtList.add(commMap);

				}

			}
			else
			{
				sql = "select ref_line_no , invoice_id, inv_amt, frt_amt, item_ser, tran_date, eff_date, curr_code, exch_rate, fin_entity, site_code, line_no__inv, item_code, net_amt, " +
						" quantity__stduom, rate__stduom, inv_disc, comm_frt, comm_insu, amt_inv, sales_pers, comm_perc__on, comm_perc, dlv_term, ord_amt, insu_amt, sorder_no, " +
						" sline_no, curr_code_sp, exch_rate_sp, comm_amt from ( select 1 as ref_line_no,a.invoice_id, a.inv_amt inv_amt, a.frt_amt * d.exch_rate__frt / a.exch_rate frt_amt," +
						" a.item_ser item_ser, a.tran_date tran_date, a.eff_date eff_date, a.curr_code curr_code, a.exch_rate exch_rate, a.fin_entity fin_entity, a.site_code site_code,  " +
						" b.line_no line_no__inv, b.item_code, b.net_amt, b.quantity__stduom, b.rate__stduom, b.discount inv_disc,(case when a.inv_amt = 0 then 0 else (b.quantity__stduom * b.rate__stduom - b.disc_amt) " +
						" / a.inv_amt * a.frt_amt end) comm_frt, (case when d.ord_amt = 0 then 0 else (b.quantity__stduom * b.rate__stduom - b.disc_amt) / d.ord_amt * d.ins_amt end) comm_insu, " +
						" b.quantity__stduom * b.rate__stduom - b.disc_amt amt_inv,b.sord_line_no sline_no, d.sales_pers sales_pers,(case when (case when d.comm_perc__on is null then ' ' " +
						" else d.comm_perc__on end) = ' ' then c.comm_perc_on_1 else d.comm_perc__on end) comm_perc__on, (case when (case when c.comm_perc_1 is null then 0 else c.comm_perc_1 end)" +
						"  = 0 then (case when d.comm_perc is null then 0 else d.comm_perc end) else (case when c.comm_perc_1 is null then 0 else c.comm_perc_1 end) end) comm_perc," +
						" d.dlv_term dlv_term, d.ord_amt ord_amt,d.ins_amt * d.exch_rate__ins / d.exch_rate__comm insu_amt, d.sale_order sorder_no, d.curr_code__comm curr_code_sp, d.exch_rate__comm exch_rate_sp, " +
						" (case when b.sales_pers_comm_1 is null then 0 else b.sales_pers_comm_1 end)  comm_amt from invoice a, invdet b, sorddet c, sorder d where a.invoice_id = b.invoice_id  " +
						" and b.sord_no = c.sale_order 	and b.sord_line_no = c.line_no 	and c.sale_order = d.sale_order and a.invoice_id = ? and (case when d.sales_pers is null then ' ' else d.sales_pers end) <> ' '	" +
						" union all select 2 as ref_line_no, a.invoice_id, a.inv_amt inv_amt, a.frt_amt * d.exch_rate__frt / a.exch_rate frt_amt, a.item_ser item_ser, 	a.tran_date tran_date," +
						"  a.eff_date eff_date, a.curr_code curr_code, a.exch_rate exch_rate, a.fin_entity fin_entity, a.site_code site_code,b.line_no line_no__inv, b.item_code, b.net_amt, b.quantity__stduom, b.rate__stduom," +
						" b.discount inv_disc, (case when a.inv_amt = 0 then 0 else (b.quantity__stduom * b.rate__stduom - b.disc_amt) / a.inv_amt * a.frt_amt end) comm_frt, " +
						" (case when d.ord_amt = 0 then 0 else (b.quantity__stduom * b.rate__stduom - b.disc_amt)  / d.ord_amt * d.ins_amt end) comm_insu, 	b.quantity__stduom * b.rate__stduom - b.disc_amt amt_inv," +
						"  b.sord_line_no sline_no, d.sales_pers__1 sales_pers, (case when (case when d.comm_perc_on_1 is null then ' ' else d.comm_perc_on_1 end) = ' ' then c.comm_perc_on_2 else d.comm_perc_on_1 end) " +
						" comm_perc__on, (case when (case when c.comm_perc_2 is null then 0 else c.comm_perc_2 end) = 0 then (case when d.comm_perc_1 is null then 0 else d.comm_perc_1 end) else " +
						" (case when c.comm_perc_2 is null then 0 else c.comm_perc_2 end) end) comm_perc,d.dlv_term dlv_term, d.ord_amt ord_amt, d.ins_amt * d.exch_rate__ins / d.exch_rate__comm_1 insu_amt," +
						" d.sale_order sorder_no,d.curr_code__comm_1 curr_code_sp, d.exch_rate__comm_1 exch_rate_sp, (case when b.sales_pers_comm_2 is null then 0 else b.sales_pers_comm_2 end) comm_amt " +
						"  from invoice a, invdet b, sorddet c, sorder d where a.invoice_id = b.invoice_id and b.sord_no = c.sale_order and b.sord_line_no = c.line_no 	and c.sale_order = d.sale_order  	" +
						" and a.invoice_id = ? and (case when d.sales_pers__1 is null then ' ' else d.sales_pers__1 end) <> ' '	union all select 3 as ref_line_no, a.invoice_id, a.inv_amt inv_amt,  " +
						" a.frt_amt * d.exch_rate__frt / a.exch_rate frt_amt , a.item_ser item_ser, a.tran_date tran_date, a.eff_date eff_date, a.curr_code curr_code, a.exch_rate exch_rate, a.fin_entity fin_entity, " +
						" a.site_code site_code, b.line_no line_no__inv, b.item_code, b.net_amt, b.quantity__stduom, b.rate__stduom, b.discount inv_disc, 	(case when a.inv_amt = 0 then 0 else (b.quantity__stduom * b.rate__stduom - b.disc_amt)" +
						" / a.inv_amt * a.frt_amt end) comm_frt, (case when d.ord_amt = 0 then 0 else (b.quantity__stduom * b.rate__stduom - b.disc_amt)  / d.ord_amt * d.ins_amt end) comm_insu,  " +
						"  b.quantity__stduom * b.rate__stduom - b.disc_amt amt_inv, b.sord_line_no sline_no, d.sales_pers__2 sales_pers, (case when (case when d.comm_perc_on_2 is null then ' ' else d.comm_perc_on_2 end) = ' ' " +
						" then c.comm_perc_on_3 else d.comm_perc_on_2 end) comm_perc__on, (case when (case when c.comm_perc_3 is null then 0 else c.comm_perc_3 end) = 0 then (case when d.comm_perc_2 is null then 0 else d.comm_perc_2 end) else " +
						" (case when c.comm_perc_3 is null then 0 else c.comm_perc_3 end) end) comm_perc, d.dlv_term dlv_term, d.ord_amt ord_amt,d.ins_amt * d.exch_rate__ins / d.exch_rate__comm_2 insu_amt , d.sale_order sorder_no, " +
						" d.curr_code__comm_2 curr_code_sp, d.exch_rate__comm_2 exch_rate_sp, (case when b.sales_pers_comm_3 is null then 0 else b.sales_pers_comm_3 end)  comm_amt from invoice a, invdet b, sorddet c, sorder d " +
						" where a.invoice_id = b.invoice_id and b.sord_no = c.sale_order and b.sord_line_no = c.line_no and c.sale_order = d.sale_order  and a.invoice_id = ? and (case when d.sales_pers__2 is null then ' ' else d.sales_pers__2 end) <> ' ' ) " +
						" commtab  where ((case when sales_pers is null then ' ' else sales_pers end) <> ' ')  	and	((case when comm_perc__on is null then ' ' else comm_perc__on end) <> ' ') and comm_amt > 0 order by invoice_id, sales_pers, comm_perc__on ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, invoiceNo);
				pstmt.setString(2, invoiceNo);
				pstmt.setString(3, invoiceNo);

				rs = pstmt.executeQuery();
				while(rs.next())
				{
					commMap.put("invoice_id" , rs.getString("invoice_id"));
					commMap.put("ref_line_no" , rs.getString("ref_line_no"));
					commMap.put("inv_amt" ,rs.getDouble("inv_amt"));
					commMap.put("frt_amt" , rs.getDouble("frt_amt"));
					commMap.put("item_ser" , rs.getString("item_ser"));
					commMap.put("tran_date" , rs.getTimestamp("tran_date"));
					commMap.put("eff_date" , rs.getTimestamp("eff_date"));
					commMap.put("curr_code" , rs.getString("curr_code"));
					commMap.put("exch_rate" , rs.getDouble("exch_rate"));
					commMap.put("fin_entity" , rs.getString("fin_entity"));		
					commMap.put("site_code" , rs.getString("site_code"));				
					commMap.put("line_no__inv" , rs.getInt("line_no__inv"));
					commMap.put("item_code" , rs.getString("item_code"));
					commMap.put("net_amt" , rs.getInt("net_amt"));
					commMap.put("quantity__stduom" , rs.getInt("net_amt"));
					commMap.put("rate__stduom" , rs.getInt("rate__stduom"));
					commMap.put("inv_disc" , rs.getInt("inv_disc"));
					commMap.put("comm_frt" , rs.getString("comm_frt"));
					commMap.put("comm_insu" , rs.getString("comm_insu"));
					commMap.put("amt_inv" , rs.getInt("amt_inv"));
					commMap.put("sales_pers" , rs.getString("sales_pers"));
					commMap.put("comm_perc__on" , rs.getString("comm_perc__on"));
					commMap.put("comm_perc" , rs.getDouble("comm_perc"));
					commMap.put("dlv_term" , rs.getString("dlv_term"));					
					commMap.put("ord_amt" , rs.getDouble("ord_amt"));
					commMap.put("insu_amt" ,rs.getDouble("insu_amt"));
					commMap.put("sorder_no" , rs.getString("sorder_no"));
					commMap.put("sline_no " , rs.getInt("sline_no"));
					commMap.put("curr_code_sp" ,rs.getString("curr_code_sp"));
					commMap.put("exch_rate_sp" ,  rs.getDouble("exch_rate_sp"));
					commMap.put("comm_amt" , rs.getDouble("comm_amt"));

					commtList.add(commMap);

				}

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

			if(errString.trim().length() == 0)
			{
				//ll_totrow = lds_comm.retrieve(as_invno)
				if("SR".equalsIgnoreCase(calledFrom) && "P".equalsIgnoreCase(calledFrom) && commtList.size() > 0)
				{
					sql = "Select sum(tot_amt - adj_amt) from receivables where	ref_no = ?" ;
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, invno);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						adjAmt  = rs.getDouble(1);
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;

					if(Math.round(adjAmt) == 0)
					{
						errString = itmDBAccessEJB.getErrorString("","VTADJAMTSP ","","",conn);
						return errString;
					}
				}
				drnList = new ArrayList<String>();
				for(int cnt = 0 ;commtList.size() > cnt ;cnt++) //change done by kunal on 23/jun/14 loop start with 0
				{
					commMap = new HashMap();
					commMap = (HashMap) commtList.get(cnt);

					refLineNo 	= (String) commMap.get("ref_line_no");
					invoiceId	= (String) commMap.get("invoice_id");
					invAmt		= (Double) commMap.get("inv_amt");
					frtAmt		= (Double) commMap.get("frt_amt");
					itemSer		= (String) commMap.get("item_ser");
					tranDate	= (Timestamp)   commMap.get("tran_date");
					effDate		= (Timestamp)   commMap.get("eff_date") ;
					currCode	= (String) commMap.get("curr_code");
					exchRate	= (Double) commMap.get("exch_rate");
					finent		= (String) commMap.get("fin_entity");
					siteCode	= (String) commMap.get("site_code");

					if("Y".equalsIgnoreCase(commHdr))
					{
						lineNoInv = 1;
						itemCode = "";
					}
					else
					{
						lineNoInv	= (Double) commMap.get("line_no__inv");
						itemCode	= (String) commMap.get("item_code");
					}
					salesPers 	= (String) commMap.get("sales_pers");
					commPercOn	= (String) commMap.get("comm_perc__on");
					commPerc	= (Double) commMap.get("comm_perc");
					ordAmt		= (Double) commMap.get("ord_amt");
					insuAmt		= (Double) commMap.get("insu_amt");
					sorderNo	= (String) commMap.get("sorder_no");
					currCodeSp	=  (String) commMap.get("curr_code_sp") ;
					exchRateSp	= (Double) commMap.get("exch_rate_sp");
					drnList = new ArrayList<String>();
					System.out.println("commtList.size() [" + commtList.size() + "] oldSp [" + oldSp + "] salesPers [" + salesPers + "]currcdSpOld [" + currcdSpOld + "] currCodeSp [" + currCodeSp + "]" );
					if(!oldSp.equalsIgnoreCase(salesPers) || !currcdSpOld.equalsIgnoreCase(currCodeSp))
					{
						
						if( xmlBuff != null  && (xmlBuff.toString()).length() > 0 )
						{
							xmlBuff.append("</Header0>");
							xmlBuff.append("</group0>");
							xmlBuff.append("</DocumentRoot>");
							drnList.add(xmlBuff.toString());
							xmlBuff = null;	
							xmlBuff = new StringBuffer();
						}
						oldSp = salesPers;
						currcdSpOld = currCodeSp;

						sql = "select cr_term , tax_class, tax_chap , tax_env  from sales_pers 	where sales_pers = ?";		
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, salesPers);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							crTerm  = rs.getString("cr_term");
							taxClass  = rs.getString("tax_class");
							taxChap  = rs.getString("tax_chap");
						}
						else
						{
							errString = itmDBAccessEJB.getErrorString("","VTSLPERS1 ","","",conn);
							return errString;
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						taxClass  = (taxClass == null ? "" : taxClass);
						taxChap  = (taxChap== null ? "" : taxChap);
						sql = " select cr_days  from crterm where cr_term = ?";	
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, crTerm);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							crdays  = rs.getInt("cr_term");
						}
						else
						{
							errString = itmDBAccessEJB.getErrorString("","VTCRTERM1 ","","",conn);
							return errString;

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

						sql = "select  acct_code__ap,cctr_code__ap  from sales_pers where sales_pers = ?";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, salesPers);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							acctCode  = rs.getString(1);
							cctrCode  = rs.getString(2);
						}
						else
						{
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;
							sql = "select  cctr_code__sal  from invoice where invoice_id = ?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, invno);
							rs = pstmt.executeQuery();
							if(rs.next())
							{
								cctrCode  = rs.getString(1);
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;

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


						sql = "select ins_reqd, frt_reqd  from delivery_term where dlv_term = ?";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, dlvTerm);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							insReqd  = rs.getString(1);
							frtReqd  = rs.getString(2);
						}
						else
						{
							errString = itmDBAccessEJB.getErrorString("","VTDLV1 ","","",conn);
							return errString;
						}

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


						jvExchrate = 0;

						sql = "select exch_rate  from journal where ref_ser = 'S-INV' and ref_no = ? and ref_line_no = ?";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, invno);
						pstmt.setString(2, refLineNo);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							exchRate  = rs.getDouble(1);
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;

						if("SR".equalsIgnoreCase(calledFrom) ||"P".equalsIgnoreCase(commCalc))
						{
							sql = "select exch_rate from	drcr_inv 	where tran_ser = 'CRNINV'	and	drcr_flag = 'C'" +
									"and	sundry_code = ? and	invoice_id = ? and	curr_code = ?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, salesPers);
							pstmt.setString(2, invoiceId);
							pstmt.setString(3, currCodeSp);
							rs = pstmt.executeQuery();
							if(rs.next())
							{
								exchRate  = rs.getDouble(1);
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;

						}
						if("SR".equalsIgnoreCase(calledFrom))
						{
							siteCode = sreSiteCode;
						}

						if(!"SR".equalsIgnoreCase(calledFrom) && "P".equalsIgnoreCase(commCalc))
						{
							rcpRefser = "R-IBCA";
							sql = "Select distinct h.site_code from receipt h, rcpdet d where h.tran_id = d.tran_id" +
									"and d.ref_ser = ? and d.ref_no = ? and " +
									"(case when h.confirmed is null then 'N' else h.confirmed end) = 'Y'";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, rcpRefser);
							pstmt.setString(2, invoiceNo);
							rs = pstmt.executeQuery();
							while(rs.next())
							{
								siteCodeCd  = rs.getString(1);
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;


						}
						else
						{
							rcpRefser = "S-INV";
						}
						//Add by vilas for picking exchrate from JV 		
						//insert into header
						if( xmlBuff == null )
						{
							xmlBuff = new StringBuffer();
						}

						xmlBuff.append("<?xml version='1.0' encoding='ISO-8859-1'?>\n");
						xmlBuff.append("<DocumentRoot>");
						xmlBuff.append("<description>").append("Datawindow Root").append("</description>");
						xmlBuff.append("<group0>");
						xmlBuff.append("<description>").append("Group0 description").append("</description>");
						xmlBuff.append("<Header0>");
						xmlBuff.append("<objName><![CDATA[").append("drcrinv").append("]]></objName>");      
						xmlBuff.append("<pageContext><![CDATA[").append("1").append("]]></pageContext>");
						xmlBuff.append("<objContext><![CDATA[").append("1").append("]]></objContext>");
						xmlBuff.append("<editFlag><![CDATA[").append("A").append("]]></editFlag>");
						xmlBuff.append("<focusedColumn><![CDATA[").append("").append("]]></focusedColumn>");
						xmlBuff.append("<action><![CDATA[").append("SAVE").append("]]></action>");
						xmlBuff.append("<elementName><![CDATA[").append("").append("]]></elementName>");
						xmlBuff.append("<keyValue><![CDATA[").append("1").append("]]></keyValue>");
						xmlBuff.append("<taxKeyValue><![CDATA[").append("").append("]]></taxKeyValue>");
						xmlBuff.append("<saveLevel><![CDATA[").append("1").append("]]></saveLevel>");
						xmlBuff.append("<forcedSave><![CDATA[").append(true).append("]]></forcedSave>");
						xmlBuff.append("<taxInFocus><![CDATA[").append(false).append("]]></taxInFocus>");
						xmlBuff.append("<description>").append("Header0 members").append("</description>");
						xmlBuff.append("<Detail1 dbID=\"\" domID=\"1\" objName=\"drcrinv\" objContext=\"1\">"); 
						xmlBuff.append("<attribute selected=\"N\" updateFlag=\"A\" status=\"N\" pkNames=\"\"/>");
						xmlBuff.append("<tran_id/>");

						xmlBuff.append("<tran_date><![CDATA["+ sdf.format(new java.util.Date()).toString() +"]]></tran_date>");
						xmlBuff.append("<site_code><![CDATA["+ siteCode +"]]></site_code>");
						xmlBuff.append("<invoice_id><![CDATA["+ invoiceId +"]]></invoice_id>");
						xmlBuff.append("<fin_entity><![CDATA["+ finent +"]]></fin_entity>");
						xmlBuff.append("<sundry_type><![CDATA["+ "P" +"]]></sundry_type>");
						xmlBuff.append("<sundry_code><![CDATA["+ sdf.format(salesPers).toString() +"]]></sundry_code>");
						xmlBuff.append("<item_ser><![CDATA["+ itemSer +"]]></item_ser>");
						xmlBuff.append("<acct_code><![CDATA["+ acctCode +"]]></acct_code>");
						xmlBuff.append("<cctr_code><![CDATA["+ cctrCode +"]]></cctr_code>");
						xmlBuff.append("<eff_date><![CDATA["+ sdf.format(new java.util.Date()).toString() +"]]></eff_date>");
						xmlBuff.append("<curr_code><![CDATA["+ currCodeSp +"]]></curr_code>");
						if(!calledFrom.equalsIgnoreCase("SR"))
						{
							sql = "select curr_code from finent f, site s where f.fin_entity = s.fin_entity  and	s.site_code  = ?" ;
							pstmt.setString(1, siteCode);
							rs = pstmt.executeQuery();
							while(rs.next())
							{
								currCodeBase  = rs.getString(1);
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;

							sql = "Select distinct h.tran_id from  receipt h, rcpdet d where h.tran_id = d.tran_id " +
									"and d.ref_ser = ? and	d.ref_no = ? and  (case when h.confirmed is null then 'N' else h.confirmed end) = 'Y'";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, rcpRefser);
							pstmt.setString(2, invoiceNo);
							rs = pstmt.executeQuery();
							while(rs.next())
							{
								tranId= rs.getString(1);
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;

							if(!"P".equalsIgnoreCase(commCalc))
							{
								if(!currCode.equalsIgnoreCase(currCodeSp))
								{
									crntExchrate = finCommon.getDailyExchRateSellBuy(currCodeSp, currCodeBase, siteCode, idDate, "S", conn);

								}else
								{
									sql = " Select h.exch_rate from receipt h, rcpdet d where h.tran_id = d.tran_id and	d.ref_ser = ?" +
											" and	d.ref_no = ? and (case when h.confirmed is null then 'N' else h.confirmed end) = 'Y'" +
											"order by h.tran_date, h.tran_id desc";
									pstmt = conn.prepareStatement(sql);
									pstmt.setString(1, rcpRefser);
									pstmt.setString(2, invoiceNo);
									rs = pstmt.executeQuery();
									while(rs.next())
									{
										exchRate= rs.getDouble(1);
									}
									rs.close();
									rs = null;
									pstmt.close();
									pstmt = null;

									if( crntExchrate > 0 )
									{
										exchRateSp = crntExchrate;
									}
									else
									{
										crntExchrate = exchRateSp;
									}	

								}
							}
							else
							{
								remarks = "Agst Sales Return No"+ sretId+"";
							}
							xmlBuff.append("<exch_rate><![CDATA["+ crntExchrate +"]]></exch_rate>");
							if ("SR".equalsIgnoreCase(calledFrom) || "P".equalsIgnoreCase(calledFrom))
							{
								jvExchrate = crntExchrate;

							}else
							{
								if( jvExchrate >0)
								{
									jvExchrate = crntExchrate;
								}
							}

							rcpExchRateTot = 0.0;
							count = 0;

							tranType = finCommon.getFinparams("999999", "SALES_COMM_TRAN_TYPE", conn);
							if("NULLFOUND".equalsIgnoreCase(tranType))
							{
								errString = itmDBAccessEJB.getErrorString("","VTFINPARM ","","",conn);
								return errString;
							}

							xmlBuff.append("<tran_type><![CDATA["+ tranType +"]]></tran_type>");
							// 05-12-02 Arif to set the remarks
							xmlBuff.append("<drcr_flag><![CDATA["+ "D" +"]]></drcr_flag>");
							xmlBuff.append("<emp_code__aprv><![CDATA["+ loginEmpCode +"]]></emp_code__aprv>");
							xmlBuff.append("<tran_type><![CDATA["+ "FOR" +"]]></tran_type>");
							xmlBuff.append("<cr_term><![CDATA["+ crTerm +"]]></cr_term>");
							xmlBuff.append("<tran_ser><![CDATA["+ transer +"]]></tran_ser>");
							xmlBuff.append("<remarks><![CDATA["+ remarks +"]]></remarks>");
							xmlBuff.append("<due_date><![CDATA["+ sdf.format(utilMethods.RelativeDate((new java.util.Date()), crdays)).toString() +"]]></due_date>");
							
							xmlBuff.append("<acct_code__cf><![CDATA["+ acctCodeCf +"]]></acct_code__cf>");
							xmlBuff.append("<cctr_code__cf><![CDATA["+ cctrCodeCf +"]]></cctr_code__cf>");
							// 08/08/14 manoharan to use during receipt dishonour
							if ((!"null".equals(tranIdRcp)) && tranIdRcp != null && tranIdRcp.trim().length() > 0 )
							{
								xmlBuff.append("<tran_id__rcv><![CDATA["+ tranIdRcp +"]]></tran_id__rcv>");
							}
							
							xmlBuff.append("</Detail1>");
						}		
						if(errString == null || errString.trim().length() ==0)
						{
							xmlBuff.append("<Detail2 dbID='' domID=\"1\" objName=\"drcrinv\" objContext=\"2\">");  //sorder modify to sorder_insert by kunal
							xmlBuff.append("<attribute pkNames=\"\" selected=\"N\" updateFlag=\"A\" status=\"N\" />");
							xmlBuff.append("<tran_id/>");
							xmlBuff.append("<line_no><![CDATA["+ lineNo +"]]></line_no>");
							xmlBuff.append("<invoice_id><![CDATA["+ invoiceId +"]]></invoice_id>");
							xmlBuff.append("<line_no__inv><![CDATA["+ lineNoInv +"]]></line_no__inv>");
							xmlBuff.append("<item_code><![CDATA["+ itemCode +"]]></item_code>");
							xmlBuff.append("<tax_class><![CDATA["+ taxClass +"]]></tax_class>");
							xmlBuff.append("<tax_chap><![CDATA["+ taxChap +"]]></tax_chap>");
							xmlBuff.append("<tax_env><![CDATA["+ taxEnv +"]]></tax_env>");
							xmlBuff.append("<acct_code><![CDATA["+ acctcodeExp +"]]></acct_code>");
							xmlBuff.append("<cctr_code><![CDATA["+ cctrcodeExp +"]]></cctr_code>");
							xmlBuff.append("<exch_rate__inv><![CDATA["+ jvExchrate +"]]></exch_rate__inv>");
							xmlBuff.append("</Detail2>");	
							if(lineNoInv == 0)
							{
								invLineNo = lineNoInv;
							}
							if("SR".equalsIgnoreCase(calledFrom) && "P".equalsIgnoreCase(commCalc))
							{
								sql = "select net_amt from drcr_inv, drcr_invdet  where drcr_inv.tran_id = drcr_invdet.tran_id and " +
										"drcr_inv.invoice_id = ? and drcr_inv.sundry_code = ? " +
										"and drcr_inv.curr_code = ? and drcr_flag = 'C' " +
										"and (case when line_no__inv is null then 0 else line_no__inv end) = ?";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1,invoiceId );
								pstmt.setString(2, salesPers);
								pstmt.setString(3, currCodeSp);
								rs = pstmt.executeQuery();
								if(rs.next())
								{
									netAmt = rs.getDouble(1);
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
							}
							else
							{
								sql = "select sum(case when drcr_flag = 'C' then net_amt else -net_amt end)  from drcr_inv, drcr_invdet " +
										"where drcr_inv.tran_id = drcr_invdet.tran_id and drcr_inv.invoice_id = ?	and drcr_inv.sundry_code = ?" +
										" and drcr_inv.curr_code = ? and (case when line_no__inv is null then 0 else line_no__inv end) = ? ";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1,invoiceId );
								pstmt.setString(2, salesPers);
								pstmt.setString(3, currCodeSp);
								rs = pstmt.executeQuery();
								if(rs.next())
								{
									netAmt = rs.getDouble(1);
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
							}
							if("SR".equalsIgnoreCase(calledFrom) && "P".equalsIgnoreCase(commCalc))
							{
								if(invNetamt > 0)
								{
									if(sreCurrCode.equalsIgnoreCase(currCode))
									{
										sretPerc = (sretNetAmt*100)/invNetamt;
									}
									else
									{
										sretPerc = (sretNetAmt* sretExchRate *100)/ invNetamt* exchRate;
									}
									netAmt = netAmt * sretPerc / 100;
									xmlBuff.append("<drcr_amt><![CDATA["+ drcrAmt +"]]></drcr_amt>");
								}

								oldsp = salesPers;
								currcdSpOld = currcodeSp ;


								sql = "select due_date from invoice where invoice_id = ?";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, invoiceNo);
								rs = pstmt.executeQuery();
								if(rs.next())
								{
									dueDate = rs.getTimestamp(1);
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
								System.out.println("1................ dueDate ["+dueDate + "] date [" + date + "]");
								day = utilMethods.DaysAfter(dueDate, date);


								if(day >0 )
								{
									sql = "select fin_chg,fchg_type  from 	crtermfc  " +
											"where (min_day <= ? and max_day >= ? ) and  cr_term = ?";
									pstmt = conn.prepareStatement(sql);
									pstmt.setString(1, crTerm);
									rs = pstmt.executeQuery();
									if(rs.next())
									{
										finChg = rs.getDouble(1);
										fchgType = rs.getString(1);
									}
									else
									{
										if("P".equalsIgnoreCase(fchgType))
										{
											expAmt = drcrmt * (finChg/100);
											drcrmt = drcrmt - finChg;
										}
										else
										{
											expAmt = finChg/100;
											drcrmt = drcrmt - finChg;
										}
										//xmlBuff.append("<amount><![CDATA["+ totNetAmt +"]]></amount>");
										//xmlBuff.append("<amount__bc><![CDATA["+ (totNetAmt-expAmt) +"]]></amount__bc>");

										acctcodeDeduct = finCommon.getFinparams("999999", "DEDUCT_COMM_ACCT", conn);
										if("NULLFOUND".equalsIgnoreCase(acctcodeDeduct)|| acctcodeDeduct == null || acctcodeDeduct.trim().length() == 0)
										{
											acctcodeDeduct = acctcodeExp;
											cctrcodeDeduct = cctrcodeExp;
										}
										else
										{
											cctrcodeDeduct = finCommon.getFinparams("999999", "DEDUCT_COMM_CCTR", conn);
											if("NULLFOUND".equalsIgnoreCase(cctrcodeDeduct)|| cctrcodeDeduct == null || cctrcodeDeduct.trim().length() == 0)
											{
												cctrcodeDeduct = cctrcodeExp;
											}



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

									xmlBuff.append("<Detail2 dbID='' domID=\"1\" objName=\"drcrinv\" objContext=\"2\">"); 
									xmlBuff.append("<attribute pkNames=\"\" selected=\"N\" updateFlag=\"A\" status=\"N\" />");
									xmlBuff.append("<tran_id/>");
									xmlBuff.append("<line_no><![CDATA["+ lineNo++ +"]]></line_no>");
									xmlBuff.append("<invoice_id><![CDATA["+ invoiceId +"]]></invoice_id>");
									xmlBuff.append("<line_no__inv><![CDATA["+ lineNoInv +"]]></line_no__inv>");
									xmlBuff.append("<item_code><![CDATA["+ itemCode +"]]></item_code>");
									xmlBuff.append("<tax_class><![CDATA["+ taxClass +"]]></tax_class>");
									xmlBuff.append("<tax_chap><![CDATA["+ taxChap +"]]></tax_chap>");
									xmlBuff.append("<tax_env><![CDATA["+ taxEnv +"]]></tax_env>");
									xmlBuff.append("<acct_code><![CDATA["+ acctcodeDeduct +"]]></acct_code>");
									xmlBuff.append("<cctr_code><![CDATA["+ cctrcodeDeduct +"]]></cctr_code>");
									xmlBuff.append("<net_amt><![CDATA["+ expAmt * -1 +"]]></net_amt>");
									xmlBuff.append("<drcr_amt><![CDATA["+ (expAmt * -1) * (exchRate/crntExchrate) +"]]></drcr_amt>");
									xmlBuff.append("<exch_rate__inv><![CDATA["+ jvExchrate +"]]></exch_rate__inv>");
									xmlBuff.append("</Detail2>");	
								}

							}

						}

					}
				}
				if( xmlBuff != null  && (xmlBuff.toString()).length() > 0 )
				{
					xmlBuff.append("</Header0>");
					xmlBuff.append("</group0>");
					xmlBuff.append("</DocumentRoot>");
					drnList.add(xmlBuff.toString());
				}
				System.out.println("Size of drnList ["+drnList.size() + "]");

				DrCrInvConf drCrInvConf = new DrCrInvConf();
				for (int ctr = 0;  ctr < drnList.size(); ctr++)
				{
					tranIdDrn = "";
					xmlString = (String) drnList.get(ctr);
					System.out.println("saveDataDrn [" + ctr + "] xmlString ["+xmlString + "]");
					errString = saveDataDrn(siteCode,xmlString,conn);
					
					if((errString == null || errString.trim().length() ==0)	&& ("Y".equals(post)) )
					{
						errString = drCrInvConf.confirm(tranIdDrn,xtraParams,"N", conn);
						if(errString.indexOf("VTCICONF3") > -1)
						{
							errString = "";
							if("SR".equalsIgnoreCase(calledFrom) && "P".equalsIgnoreCase(commCalc))
							{
								String xmlValues = "";
								xmlValues ="<?xml version=\"1.0\" encoding=\"utf-8\"?><Root>";
								xmlValues = xmlValues + "<Header></Header>";
								xmlValues = xmlValues + "<Detail1>";
								xmlValues = xmlValues +	"<tran_id/>";
								xmlValues = xmlValues + "<site_code>" + siteCode + "</site_code>";
								xmlValues = xmlValues + "<tran_date>"+ sdf.format(tranDate) + "</tran_date>";
								xmlValues = xmlValues + "</Detail1></Root>";

								TransIDGenerator tg = new TransIDGenerator(xmlValues, "SYSTEM", CommonConstants.DB_NAME);
								autoTranId = tg.generateTranSeqID("CRNINV", "tran_id", keyString, conn);

								//ls_auto_tran_id = lds_drcr_inv_hdr.getitemstring(ll_cnt,'tran_id')
								sql = "select tran_date		,	curr_code	,	exch_rate	,	sundry_code		,	sundry_type	  ,	acct_code		," +
										" cctr_code		,  due_date		,  cr_term 		,  site_code 	   ,	invoice_id	  , 	fin_entity" +
										"from   drcr_inv where  tran_id = ? ";

								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, autoTranId);
								rs = pstmt.executeQuery();
								if(rs.next())
								{
									tranDate = rs.getTimestamp("tran_date");
									curr = rs.getString("curr_code");
									exch = rs.getDouble("exch_rate");
									sundryCode = rs.getString("sundry_code");
									sundryType = rs.getString("sundry_type");
									acct = rs.getString("acct_code");
									cctr = rs.getString("cctr_code");
									dueDate = rs.getTimestamp("due_date");
									crTerm = rs.getString("cr_term");
									site = rs.getString("site_code");
									invoiceId = rs.getString("invoice_id");
									finEntity = rs.getString("fin_entity");
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;


								sql = "select tot_amt from misc_payables where ref_no = ?  and tran_ser = 'DRNINV'";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, autoTranId);
								rs = pstmt.executeQuery();
								if(rs.next())
								{
									amount = rs.getDouble("tot_amt");
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;

								sql = "update misc_payables set hold_amt = amount where ref_no = ? and tran_ser = 'DRNINV'";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, autoTranId);
								pstmt.executeUpdate();


								invibcagen 	= finCommon.getFinparams("999999", "INV_IBCA_GEN", conn);
								if("NULLFOUND".equalsIgnoreCase(invibcagen))
								{
									invibcagen = "Y";
								}
								if("Y".equalsIgnoreCase(invibcagen))
								{
									salesPers = sundryCode;
								}

								sql = "select site_code__pay  from sales_pers where sales_pers = ?";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, salesPers);
								rs = pstmt.executeQuery();
								if(rs.next())
								{
									siteCodePay = rs.getString("site_code__pay");
								}					
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;

								if(siteCodePay== null || siteCodePay.trim().length() == 0)
								{
									errString = itmDBAccessEJB.getErrorString("","VTSPSITPAY ","","",conn);
									return errString;
								}
								if(!siteCodePay.trim().equals(site.trim()))
								{

									sql = "	select key_string from transetup  where upper(tran_window) = 'W_PAY_IBCA' ";
									pstmt = conn.prepareStatement(sql);
									rs = pstmt.executeQuery();
									if(rs.next())
									{
										keyString = rs.getString("key_string");
									}
									rs.close();
									rs = null;
									pstmt.close();
									pstmt = null;
								}

								sql = "select fin_entity  from site where site_code = ?" ;
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, siteCode);
								rs = pstmt.executeQuery();
								if(rs.next())
								{
									finEntityFr = rs.getString("fin_entity");
								}
								else
								{
									errString = itmDBAccessEJB.getErrorString("","VMSITE ","","",conn);
									return errString;
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;

								sql = "select fin_entity from site where site_code = ? ";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, siteCodePay);
								rs = pstmt.executeQuery();
								if(rs.next())
								{
									finEntityTo = rs.getString("fin_entity");
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;

								sql = "select link_type, acct_code__pay, cctr_code__pay, acct_code__rcp, cctr_code__rcp" +
										" from ibca_pay_ctrl where site_code__from 	= ? and site_code__to = ?" ;
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, siteCode);
								pstmt.setString(2, siteCodePay);
								rs = pstmt.executeQuery();
								if(rs.next())
								{
									linkType = rs.getString("link_type");
									acctCodePay = rs.getString("acct_code__pay");
									cctrCodePay = rs.getString("cctr_code__pay");
									acctCodeRcp = rs.getString("acct_code__rcp");
									cctrCodeRcp = rs.getString("cctr_code__rcp");
								}
								else
								{
									errString = itmDBAccessEJB.getErrorString("","VTIBCA5 ","","",conn);
									return errString;
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;

								xmlString = "<?xml version=\"1.0\"?>\r\n<Root>\r\n<header>"+
										"\r\n</header><Detail1><item_ser></item_ser>"+
										"\r\n<site_code__from>"+siteCode+"</site_code__from>"+
										"\r\n<site_code__to>"+siteCodePay+"</site_code__to>"+
										"\r\n<site_code>"+siteCode+"</site_code>\r\n<tran_date>"+sdf.format(tranDate)+"</tran_date></Detail1></Root>";

								tg = new TransIDGenerator(xmlString, "SYSTEM", CommonConstants.DB_NAME);
								ibcaId = tg.generateTranSeqID("P-IBCA", "tran_id", keyString, conn);

								if("ERROR".equalsIgnoreCase(ibcaId))
								{
									errString = itmDBAccessEJB.getErrorString("","VTIBCA5 ","","",conn);
									return errString;
								}


								remarks = "IBCA transfer of sales pers"+ sundryCode.trim();

								amountBc = amount * exch;
								if(amountBc != 0)

									// create the ibca
									sql = "INSERT INTO pay_ibca(tran_id, tran_date, eff_date, site_code__from, fin_entity__from, site_code__to, fin_entity__to, amount," +
											" curr_code, exch_rate, confirmed, acct_code__ifr, cctr_code__ifr, acct_code__ito, cctr_code__ito, " +
											" amount__bc, tran_type, tran_ser, link_type, remarks, sundry_type, sundry_code)" +
											" VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? , ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, ibcaId);
								pstmt.setTimestamp(2, tranDate);
								pstmt.setTimestamp(3, tranDate);
								pstmt.setString(4, site);
								pstmt.setString(5, finEntityFr);
								pstmt.setString(6, siteCodePay);
								pstmt.setString(7, finEntityTo);
								pstmt.setDouble(8, amount);

								pstmt.setString(9, curr);
								pstmt.setDouble(10, exch);
								pstmt.setString(11, "N");
								pstmt.setString(12, acctCodePay);
								pstmt.setString(13, cctrCodePay);
								pstmt.setString(14, acctCodeRcp);
								pstmt.setString(15, cctrCodeRcp);
								pstmt.setTimestamp(16, date);
								pstmt.setDouble(17, amountBc);
								pstmt.setString(18, tranType);
								pstmt.setString(19, "I");
								pstmt.setString(20, linkType);
								pstmt.setString(21, remarks);
								pstmt.setString(22, sundryType);
								pstmt.setString(23, sundryCode);
								pstmt.executeUpdate();

								sql = "INSERT INTO pay_ibca_det (tran_id, ref_ser, ref_no, ref_date, curr_code, exch_rate, acct_code, cctr_code, due_date, tot_amt, discount, tax_amt," +
										"bank_code, ref_type, cr_term, ref_ser__org, sundry_type, sundry_code ) VALUES (?, ?, ?, ? ,?,? ,?, ?, ?, ?, ?, ? , ? , ?, ? , ? , ?, ?, ?, ? )";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, ibcaId);
								pstmt.setString(2, "CRNINV");
								pstmt.setString(5, autoTranId);
								pstmt.setTimestamp(2, tranDate);
								pstmt.setString(9, curr);
								pstmt.setDouble(10, exch);
								pstmt.setString(11, acct);
								pstmt.setString(12, cctr);
								pstmt.setTimestamp(13, dueDate);
								pstmt.setDouble(14, amount);
								pstmt.setDouble(15,discount );
								pstmt.setDouble(16, taxAmt);
								pstmt.setString(17,"" );
								pstmt.setString(18,refType );
								pstmt.setString(19,crTerm );
								pstmt.setString(20,"DRNINV" );
								pstmt.setString(21,sundryType );
								pstmt.setString(22,sundryCode );
								pstmt.executeUpdate();
								pstmt.close();
								pstmt = null;
								// call posting for ibca
								PayIbcaConf payIbcaConf = new PayIbcaConf();
								errString = payIbcaConf.confirm(ibcaId, xtraParams, "N", conn);
								payIbcaConf = null;
								if(errString.indexOf("VTCICONF3") > -1)
								{
									errString = "";
								}
								if(errString != null && errString.trim().length() > 0)
								{
									return errString;
								}

							}
							sql = "update misc_payables set hold_amt = ? where ref_no = ?  and tran_ser = 'P-IBCA'";
							pstmt.setDouble(1, amount);
							pstmt.setString(2,autoTranId);
							pstmt.executeUpdate();

						}
						else
						{
							break;
						}
					}
				}
				drCrInvConf = null;


				// Added by fatema - 26/04/2007 - DI7CHT0010 - Db-Cr Note Adjustment
				if(errString == null || errString.trim().length() == 0)
				{
					//for ll_cnt = 1 to lds_drcr_inv_hdr.rowcount()
					errString = gbfCreateCrdrAdj(autoTranId,calledFrom, conn);
				}

			}


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

		return errString;

	}




	private  String gbfCreateCrdrAdj (String dbnoteId, String calledFrom , Connection conn) throws RemoteException, ITMException

	{
		String 	 invibcagen = "", site	= "", cust = "", sitePay = "", finentFr = "", finentTo = "", acctTo	= "", 
				cctrTo = "" , link	= "" , acctFr = "", cctrFr = "", ibcaId = "" , currCd = "" , acct = "", cctr = "", 
				bank = "" , reftype = "" , crterm = ""  ,  	sundryCode = ""	, sundryType = "" , finEntity = "" , 
				itemSer  = "" ,drTranId = "" ,	errcode = "" , remark = "" , salesPers = "" ,taxClass = ""	,	
				taxChap = "" , taxEnv = "" , reasCode = "" , itemCode = "" ,tranId	= "" , keystr = "" , mpadjTranid = "" ,	
				mpayTranid = "" , confDrcrOpt = "", acctCodeCf = "" , cctrCodeCf = "" ,	 crTranId = "",		
				invoiceid = "" , transerCr	= "" ,autoLinktype = "" ,	autoConfirm = "" , transerDb = "" , sql = "" ,
				keyString = "" , errString = "";
		ITMDBAccessEJB itmDBAccessEJB = null;
		itmDBAccessEJB = new ITMDBAccessEJB();

		java.sql.Timestamp now	= null;
		java.sql.Timestamp dueDate	= null;
		java.sql.Timestamp Date	= null;

		double exch	=0.0, factor=0.0, discount =0.0 , amountBc  = 0.0 ,	amount	= 0.0,	taxAmt	= 0.0 ,
				netAmt	= 0.0  , drcrAmt = 0.0 , totamt	= 0.0 , adjamt = 0.0 , rows = 0  , totrow	= 0 ;
		int cnt = 0;

		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			sql = "select invoice_id, sundry_code, amount, site_code, curr_code from  drcr_inv" +
					" where	 tran_id = ? and drcr_flag = 'D' " ;

			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, dbnoteId);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				invoiceid = rs.getString(1);
				sundryCode = rs.getString(2);
				amount = rs.getDouble(3);
				site = rs.getString(4);
				currCd = rs.getString(5);
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;

			GenericUtility genericUtility =  GenericUtility.getInstance();
			sql = "select tran_id	from drcr_inv  where  invoice_id = ? and	 sundry_code = ? and	 drcr_flag = 'C' " +
					"and  curr_code = ?" ;
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, invoiceid);
			pstmt.setString(2, sundryCode);
			pstmt.setString(3, currCd);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				crTranId  = rs.getString(1);
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;

			sql = "select count(1)   from misc_payables where ref_no = ? and tran_ser = 'P-IBCA' ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, crTranId);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				cnt  = rs.getInt(1);
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;

			if(cnt == 0 )
			{

				transerCr = "CRNINV" ;
			}
			else
			{
				transerCr = "P-IBCA" ;
			}


			if("SR".equalsIgnoreCase(calledFrom))
			{
				sql = "select site_code__pay   from sales_pers  where sales_pers = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, salesPers);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					sitePay  = rs.getString(1);
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;

				sql = " select auto_confirm from ibca_pay_ctrl where site_code__from = ? and site_code__to 	= ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, site);
				pstmt.setString(1, sitePay);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					autoConfirm  = rs.getString(1);
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;

				sql = " select link_type from pay_ibca hdr, pay_ibca_det det where hdr.tran_id = det.tran_id" +
						"and	det.sundry_code = ? and	det.ref_ser = 'DRNINV' and	det.ref_no = ?" ;
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, sundryCode);
				pstmt.setString(2, drTranId);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					link  = rs.getString(1);
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;

				if("A".equalsIgnoreCase(autoLinktype) || ("P".equalsIgnoreCase(autoLinktype) &&  "A".equalsIgnoreCase(autoConfirm)))
				{
					autoLinktype = "Y";
				}

				else
				{
					autoLinktype = "N";
				}
			}

			if(!"SR".equalsIgnoreCase(calledFrom) || ("SR".equalsIgnoreCase(calledFrom) && "Y".equalsIgnoreCase(autoLinktype)))
			{
				sql = "select tot_amt, adj_amt from	misc_payables WHERE tran_ser = ? AND ref_no = ? AND sundry_code = ?" ;
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, transerCr);
				pstmt.setString(2, crTranId);
				pstmt.setString(3, sundryCode);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					totamt = rs.getDouble(1);
					adjamt = rs.getDouble(2);
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;

				if(adjamt == totamt)
				{
					errString = itmDBAccessEJB.getErrorString("","VTADJAMTSP","","",conn);
					return errString;
				}
				else if ((totamt -adjamt) < netAmt)
				{
					errString = itmDBAccessEJB.getErrorString("","VTBALAMTSP","","",conn);
					return errString;

				}

				sql = "UPDATE misc_payables SET status = (case when (adj_amt + ?) = tot_amt then 'A' else 'P' end)," +
						"status_date = ?, adj_amt = (case when adj_amt is null then 0 else adj_amt end) + ? WHERE tran_ser = ?" +
						" AND ref_no = ? AND sundry_code = ?" ;
				pstmt = conn.prepareStatement(sql);
				pstmt.setDouble(1, netAmt);
				pstmt.setDate(2, new java.sql.Date(Date.getTime()));
				pstmt.setDouble(3, netAmt);
				pstmt.setString(4, transerCr);
				pstmt.setString(5, tranId);
				pstmt.setString(6, sundryCode);
				pstmt.executeUpdate();

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


				sql = "select key_string from transetup where upper(tran_window) = 'T_MISC_PAY_ADJ'";
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					keyString = rs.getString("key_string");
				}
				else
				{
					sql = "select key_string from transetup where upper(tran_window) = 'GENERAL'";
					pstmt = conn.prepareStatement(sql);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						keyString = rs.getString("key_string");
					}
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
				//ls_mpadj_tranid = gf_gen_key_nvo(lds_keygen, 'PAYADJ', 'tran_id', ls_keystr)
				java.sql.Timestamp tranDate = new java.sql.Timestamp(System.currentTimeMillis()) ;
				SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
				String xmlValues = "";
				xmlValues ="<?xml version=\"1.0\" encoding=\"utf-8\"?><Root>";
				xmlValues = xmlValues + "<Header></Header>";
				xmlValues = xmlValues + "<Detail1>";
				xmlValues = xmlValues +	"<tran_id/>";
				xmlValues = xmlValues + "<site_code>" + site + "</site_code>";
				xmlValues = xmlValues + "<tran_date>"+ sdf.format(tranDate) + "</tran_date>";
				xmlValues = xmlValues + "</Detail1></Root>";

				TransIDGenerator tg = new TransIDGenerator(xmlValues, "SYSTEM", CommonConstants.DB_NAME);
				mpadjTranid = tg.generateTranSeqID("PAYADJ", "tran_id", keyString, conn);


				sql = "select count(1) from misc_payables  where ref_no = ? and tran_ser = 'P-IBCA'";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, drTranId);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					cnt = rs.getInt(1);
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;

				if(cnt == 0)

				{
					transerDb = "DRNINV";
				}
				else
				{
					transerDb = "P-IBCA";
				}

				sql = "select tran_id  from misc_payables where tran_ser = 'DRNINV'  and ref_no = ? and sundry_code = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, drTranId);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					mpayTranid = rs.getString(1);
				}

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


				sql  = "UPDATE misc_payables SET adj_amt = ? * -1,  status = 'A', status_date = ? where tran_ser = ? AND ref_no = ? AND sundry_code = ?";	
				pstmt = conn.prepareStatement(sql);
				pstmt.setDate(1, new java.sql.Date(Date.getTime()));
				pstmt.setString(2, transerDb);
				pstmt.setString(3, tranId);
				pstmt.setString(4, sundryCode);
				pstmt.executeUpdate();

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


				sql = "INSERT INTO misc_payables_adj (tran_id, ref_ser, ref_no, tot_amt, adj_amt, ref_ser_adj,ref_no_adj, net_amt, tran_id__pay) VALUES (?, ?, ? , ? , ?, ?, ?, ?, ?)";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, mpadjTranid);
				pstmt.setString(2, "DRNINV");
				pstmt.setString(3, drTranId);
				pstmt.setDouble(4, netAmt);
				pstmt.setDouble(5, netAmt);
				pstmt.setString(6, "CRNINV");
				pstmt.setString(7, crTranId);
				pstmt.setDouble(8, 0);
				pstmt.setString(9, mpayTranid);
				pstmt.executeUpdate();

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

				sql = "select tran_id from misc_payables where tran_ser = 'CRNINV' and ref_no = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, crTranId);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					mpayTranid = rs.getString(1);
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;

				sql = " INSERT INTO misc_payables_adj (tran_id, ref_ser, ref_no, tot_amt, adj_amt, ref_ser_adj,ref_no_adj, net_amt, tran_id__pay)" +
						"VALUES (?,?,?,?,?,?,?,?,?)";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, mpadjTranid);
				pstmt.setString(2, "DRNINV");
				pstmt.setString(3, crTranId);
				pstmt.setDouble(4, netAmt);
				pstmt.setDouble(5, netAmt);
				pstmt.setString(6, "");
				pstmt.setString(7, "");
				pstmt.setDouble(8, 0);
				pstmt.setString(9, mpayTranid);
				pstmt.executeUpdate();	

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

			}

		} catch (Exception e) {
			System.out.println(e.getMessage());
			System.out.println("Exception : "+e);
			e.printStackTrace();
			throw new ITMException(e);
		}


		return errString;
	}
	private String saveDataDrn(String siteCode,String xmlString, Connection conn) throws ITMException
	{
		System.out.println("saving data...........");
		InitialContext ctx = null;
		String retString = null;
		MasterStatefulLocal masterStateful = null; // for ejb3
		try
		{
			AppConnectParm appConnect = new AppConnectParm();
			ctx = new InitialContext(appConnect.getProperty());
			masterStateful = (MasterStatefulLocal)ctx.lookup("ibase/MasterStatefulEJB/local");
			System.out.println("-----------masterStateful------- " + masterStateful);
			String [] authencate = new String[2];
			authencate[0] = "";
			authencate[1] = "";
			System.out.println("xmlString to masterstateful [" + xmlString + "]");
			retString = masterStateful.processRequest(authencate, siteCode, true, xmlString,true,conn);
			if (retString.indexOf("Success") > -1)
			{
				System.out.println("retString.indexOf(Success) > -1)");
				String[] arrayForTranId = retString.split("<TranID>");
				int endIndex = arrayForTranId[1].indexOf("</TranID>");

				tranIdDrn = arrayForTranId[1].substring(0,endIndex);
				//retString ="";
				System.out.println("@@@@@@3: retString ["+retString + "]");
				System.out.println("Credit Note TranID------>>>["+tranIdCrn+"]");
				if(tranIdCrn != null && tranIdCrn.trim().length() > 0)
				{
					System.out.println("tran_id__drn generated successfully----------[" + tranIdCrn + "]");	
					retString = "";
				}	
			}
		}
		catch(ITMException itme)
		{
			System.out.println("ITMException :CreateDistOrder :saveData :==>");
			throw itme;
		}
		catch(Exception e)
		{
			e.printStackTrace();
			System.out.println("Exception :CreateDistOrder :saveData :==>");
			throw new ITMException(e);
		}
		return retString;
	}

	
	
	private String checkNull( String input )
	{
		if ( input == null )
		{
			input = "";
		}
		return input;
	}

	private String commCrditNote(String invno, int factor, String tranIdRcp, String post,String calledFrom,String xtraParams , Connection conn) throws Exception

	{	
		String invoiceId = "", currCode = "", finEnt = "", siteCode = "", itemCode = "", salesPer = "", 
				common = "", dlvTerm = "", errCode = "" , oldSp = "", itemSer = "", tranSer = "" ,remarks = "", 
				trnofld = "", keyStr = "", crTerm = "", acctCode = "", cctrcode = "", autoTranId = "", 
				insReqd = "", frtReqd = "", acctcodeExp = "", cctrcodeExp = "", hdr = "", det = "", 
				saleOrder = "", slineNo = "", currcodeSp = "", str1 = "", despId = "", fchgType = "", 
				acctcodeDeduct = "" , cctrcodeDeduct = "", currCodeBase = "", rcpRefser = "", rcpSitecd = "", 
				currcdSpOld = "",  commHdr = "", sitecodeOld = "" , sql = "" , commCalc = "", acctCodecf = "" , sorderNo = "",
				cctrCodecf = ""  ,taxClass  = "" , taxChap = "" , cctrCodeAp  = "" , acctCodeAp  = "" , siteCodeRcp = "" , siteCodeCd = "" , errString = "" , tranType = "" ,  tranId = "", currCodeSp = "" , iddate = "" , taxEnv = "" ,tranIdInvdet = "", sreCurrCode = "" , xmlString = null, refLineNo = "", commPercOn = "";

		StringBuffer xmlBuff = null;
		ITMDBAccessEJB itmDBAccessEJB = null;
		itmDBAccessEJB = new ITMDBAccessEJB();

		HashMap commMap = new HashMap();
		ArrayList commtList = new ArrayList();
		SimpleDateFormat sdf  = null;
		GenericUtility genericUtility = null;
		double invAmt = 0.0 , frtAmt = 0.0, netAmt = 0.0, qtyStduom = 0.0, rateStduom = 0.0, commAmt = 0, 
				commfrt = 0.0 , commInsu = 0.0 , detInvamt = 0.0, commPerc = 0.0, ordAmt = 0.0, insuAmt = 0.0,
				totNetAmt = 0.0 , drcrAmt = 0.0, invDisc = 0.0 , val = 0.0, val1 = 0.0, commQty = 0.0, amount = 0.0,
				exchRate = 0.0 , exchrateSp , jvExchrate = 0.0, crntExchrate = 0.0, commPerc1 = 0.0, commperc2 = 0.0,
				exchRateSp = 0.0 , rcpExchRateTot = 0.0 ,invNetamt = 0.0 , sretPerc = 0.0 , sretNetAmt = 0.0 ,expAmt = 0,
				sretExchRate = 0.0 , finChg = 0.0;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		int crdays = 0 , count = 0 , lineNo = 0 , lineNoInv = 0 ,invLineNo = 0;
		long days = 0;

		java.sql.Timestamp tranDate  = null,date = null,dueDate = null, effDate = null;

		FinCommon finCommon = new FinCommon();
		tranSer = "CRNINV";
		trnofld = "tran_id";
		
		ArrayList<String> crnList = null;
		try
		{
			genericUtility = GenericUtility.getInstance();
			date = new java.sql.Timestamp(System.currentTimeMillis()) ;
			sdf = new SimpleDateFormat(genericUtility.getDBDateFormat());
			date = java.sql.Timestamp.valueOf(sdf.format(date) + " 00:00:00.000");
			sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
			
			sql = "select key_string from transetup where upper(tran_window) = 'W_DRCRINV'";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				keyStr = rs.getString(1);
			}
			else
			{
				sql = "select key_string from transetup where upper(tran_window) = 'GENERAL'";
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();	
				if(rs.next())
				{
					keyStr = rs.getString(1);
				}
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			if ((!"null".equals(tranIdRcp)) && tranIdRcp != null && tranIdRcp.trim().length() > 0 )
			{
				remarks = "Agst Receipt No [" + tranIdRcp + "] of Invoice # [" + invno + "]";
			}
			else
			{
				remarks = "Agst Invoice No [" + invno + "]";
			}
			// get the a/c code for commission expense a/c
			if(errCode == null || errCode.trim().length() == 0 )
			{
				if("I".equalsIgnoreCase(calledFrom))
				{
					acctcodeExp = finCommon.getFinparams("999999", "COMM_EXP_ACCT", conn);
					if("NULLFOUND".equalsIgnoreCase(acctcodeExp))
					{
						errString = itmDBAccessEJB.getErrorString("","VTBALAMTSP","","",conn);
						return errString;
					}
					else
					{
						cctrcodeExp = finCommon.getFinparams("999999", "COMM_EXP_CCTR", conn);
						if("NULLFOUND".equalsIgnoreCase(acctcodeExp))
						{
							errString = itmDBAccessEJB.getErrorString("","VTFINPARM","","",conn);
							return errString;
						}
					}
				}else if ("P".equalsIgnoreCase(calledFrom))
				{
					acctcodeExp = finCommon.getFinparams("999999", "SAL_COM_PROV_REV_ACT", conn);
					if("NULLFOUND".equalsIgnoreCase(acctcodeExp))
					{
						errString = itmDBAccessEJB.getErrorString("","VTFINPARM","","",conn);
						return errString;
					}
					else
					{
						cctrcodeExp = finCommon.getFinparams("999999", "SAL_COM_PROV_REV_CTR", conn);
						if("NULLFOUND".equalsIgnoreCase(acctcodeExp))
						{
							errString = itmDBAccessEJB.getErrorString("","VTFINPARM","","",conn);
							return errString;
						}
					}
				}
				else
				{
					acctcodeExp = finCommon.getFinparams("999999", "PROV_COMM_ACCT", conn);
					if("NULLFOUND".equalsIgnoreCase(acctcodeExp))
					{
						errString = itmDBAccessEJB.getErrorString("","VTFINPARM","","",conn);
						return errString;
					}
					else
					{
						cctrcodeExp = finCommon.getFinparams("999999", "PROV_COMM_CCTR", conn);
						if("NULLFOUND".equalsIgnoreCase(acctcodeExp))
						{
							errString = itmDBAccessEJB.getErrorString("","VTFINPARM","","",conn);
							return errString;
						}
					}
				}

			}
			commCalc = finCommon.getFinparams("999999", "COMM_CALC", conn);
			invoiceId = invno ;

			sql = "Select acct_code__cf, cctr_code__cf from bank where bank_code = (Select bank_code from site where" +
					" site_code = (select site_code from invoice where invoice_id = ?))";

			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,invoiceId);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				acctCodecf = rs.getString("acct_code__cf");
				cctrCodecf = rs.getString("cctr_code__cf");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;

			sql = "select sale_order from invoice where invoice_id = ?" ;
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, invoiceId);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				saleOrder = rs.getString("sale_order");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;

			sql = "select comm_perc, comm_perc_1, comm_perc_2 from	sorder  where sale_order = ?" ;
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, saleOrder);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				commPerc = rs.getDouble("comm_perc");
				commPerc1 = rs.getDouble("comm_perc_1");
				commperc2 = rs.getDouble("comm_perc_2");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;

			commHdr = "N";
			if(commPerc > 0 || (commPerc1 > 0  || commperc2 > 0 ))
			{
				commHdr = "Y";
			}
			/*if("Y".equalsIgnoreCase(commHdr))
			{

				sql = "select invoice_id, ref_line_no, inv_amt, frt_amt, item_ser, tran_date, eff_date, curr_code, exch_rate, fin_entity, site_code," +
						" sales_pers,comm_perc__on,comm_perc,dlv_term, ord_amt, insu_amt,sorder_no, curr_code_sp, exch_rate_sp,comm_amt from (" +
						" select DISTINCT a.invoice_id, 1 as ref_line_no, a.inv_amt inv_amt, a.frt_amt * d.exch_rate__frt / a.exch_rate frt_amt," +
						" a.item_ser item_ser, a.tran_date tran_date, a.eff_date eff_date,a.curr_code curr_code, a.exch_rate exch_rate, a.fin_entity fin_entity," +
						" a.site_code site_code,d.sales_pers sales_pers,(case when (case when d.comm_perc__on is null then ' ' else d.comm_perc__on end) = ' ' " +
						" then c.comm_perc_on_1 else d.comm_perc__on end) comm_perc__on, (case when (case when c.comm_perc_1 is null then 0 else c.comm_perc_1 end)  = 0 " +
						" then (case when d.comm_perc is null then 0 else d.comm_perc end) else (case when c.comm_perc_1 is null then 0 else c.comm_perc_1 end) end) comm_perc," +
						" d.dlv_term dlv_term, d.ord_amt ord_amt, (d.ins_amt * d.exch_rate__ins / d.exch_rate__comm) insu_amt,d.sale_order sorder_no, d.curr_code__comm curr_code_sp, " +
						" d.exch_rate__comm exch_rate_sp, (case when (case when b.sales_pers_comm_1 is null then 0 else b.sales_pers_comm_1 end) = 0 " +
						" then a.sales_pers_comm_1 else b.sales_pers_comm_1 end) comm_amt from invoice a, invdet b, sorddet c, sorder d 	where a.invoice_id = b.invoice_id  " +
						" and b.sord_no = c.sale_order and b.sord_line_no = c.line_no  and c.sale_order = d.sale_order  and a.invoice_id = ? and " +
						" (case when d.sales_pers is null then ' ' else d.sales_pers end) <> ' ' UNION ALL select DISTINCT a.invoice_id, 2 as ref_line_no, a.inv_amt inv_amt," +
						"  a.frt_amt * d.exch_rate__frt / a.exch_rate frt_amt, a.item_ser item_ser, a.tran_date tran_date, a.eff_date eff_date, a.curr_code curr_code,  a.exch_rate exch_rate," +
						"  a.fin_entity fin_entity, a.site_code site_code, d.sales_pers__1 sales_pers, (case when (case when d.comm_perc_on_1 is null then ' ' else d.comm_perc_on_1 end) = ' ' " +
						" then c.comm_perc_on_2 else d.comm_perc_on_1 end) comm_perc__on, (case when (case when c.comm_perc_2 is null then 0 else c.comm_perc_2 end) = 0 then " +
						" (case when d.comm_perc_1 is null then 0 else d.comm_perc_1 end) else (case when c.comm_perc_2 is null then 0 else c.comm_perc_2 end) end) comm_perc," +
						" d.dlv_term dlv_term, d.ord_amt ord_amt, (d.ins_amt * d.exch_rate__ins / d.exch_rate__comm_1) insu_amt, d.sale_order sorder_no, d.curr_code__comm_1 curr_code_sp, " +
						" d.exch_rate__comm_1 exch_rate_sp,(case when (case when b.sales_pers_comm_2 is null then 0 else b.sales_pers_comm_2 end) = 0 " +
						" then a.sales_pers_comm_2 else b.sales_pers_comm_2 end) comm_amt from invoice a,invdet b, sorddet c, sorder d 	where a.invoice_id = b.invoice_id " +
						" and b.sord_no = c.sale_order 	and b.sord_line_no = c.line_no and c.sale_order = d.sale_order  and a.invoice_id = ?  " +
						" and (case when d.sales_pers__1 is null then ' ' else d.sales_pers__1 end) <> ' ' UNION ALL SELECT DISTINCT a.invoice_id, 3 as ref_line_no, " +
						" a.inv_amt inv_amt, a.frt_amt * d.exch_rate__frt / a.exch_rate frt_amt, a.item_ser item_ser, a.tran_date tran_date, a.eff_date eff_date, a.curr_code curr_code," +
						" a.exch_rate exch_rate, a.fin_entity fin_entity, a.site_code site_code, d.sales_pers__2 sales_pers, (case when (case when d.comm_perc_on_2 is null then ' ' else d.comm_perc_on_2 end) = ' ' " +
						" then c.comm_perc_on_3 else d.comm_perc_on_2 end) comm_perc__on, (case when (case when c.comm_perc_3 is null then 0 else c.comm_perc_3 end) = 0 then " +
						" (case when d.comm_perc_2 is null then 0 else d.comm_perc_2 end) else (case when c.comm_perc_3 is null then 0 else c.comm_perc_3 end) end) comm_perc, d.dlv_term dlv_term, d.ord_amt ord_amt, " +
						" (d.ins_amt * d.exch_rate__ins / d.exch_rate__comm_2) insu_amt , d.sale_order sorder_no, d.curr_code__comm_2 curr_code_sp, d.exch_rate__comm_2 exch_rate_sp," +
						" (case when (case when b.sales_pers_comm_3 is null then 0 else b.sales_pers_comm_3 end) = 0 then a.sales_pers_comm_3 else b.sales_pers_comm_3 end) comm_amt " +
						" from invoice a, invdet b, sorddet c, sorder d where a.invoice_id = b.invoice_id and b.sord_no = c.sale_order 	and b.sord_line_no = c.line_no 	and c.sale_order = d.sale_order  " +
						" and a.invoice_id = ? and (case when d.sales_pers__2 is null then ' ' else d.sales_pers__2 end) <> ' ' ) commtab  where " +
						"((case when sales_pers is null then ' ' else sales_pers end) <> ' ')  and	((case when comm_perc__on is null then ' ' else comm_perc__on end) <> ' ') " +
						" and comm_amt > 0 order by invoice_id, sales_pers, comm_perc__on";

				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, invoiceId);
				pstmt.setString(2, invoiceId);
				pstmt.setString(3, invoiceId);

				rs = pstmt.executeQuery();
				while(rs.next())
				{
					commMap.put("invoice_id",rs.getString("invoice_id"));
					commMap.put("ref_line_no" ,rs.getString("ref_line_no"));
					commMap.put("inv_amt" ,rs.getDouble("inv_amt"));
					commMap.put("frt_amt" ,rs.getDouble("frt_amt"));
					commMap.put("item_ser" ,rs.getString("item_ser"));
					commMap.put("tran_date" ,rs.getTimestamp("tran_date"));
					commMap.put("eff_date " ,rs.getTimestamp("eff_date"));
					commMap.put("curr_code" ,rs.getString("curr_code"));
					commMap.put("exch_rate" , rs.getDouble("exch_rate"));
					commMap.put("fin_entity" , rs.getString("fin_entity"));		
					commMap.put("site_code" , rs.getString("site_code"));	
					commMap.put("sales_pers" , rs.getString("sales_pers"));
					commMap.put("comm_perc__on" , rs.getString("comm_perc__on"));
					commMap.put("comm_perc" ,  rs.getDouble("comm_perc"));
					commMap.put("dlv_term" , rs.getString("dlv_term"));					
					commMap.put("ord_amt" , rs.getDouble("ord_amt"));
					commMap.put("insu_amt" , rs.getDouble("insu_amt"));
					commMap.put("sorder_no" , rs.getString("sorder_no"));
					commMap.put("curr_code_sp" , rs.getString("curr_code_sp"));
					commMap.put("exch_rate_sp" , rs.getDouble("exch_rate_sp"));
					commMap.put("comm_amt" , rs.getDouble("comm_amt"));

					commtList.add(commMap);

				}

			}
			else
			{*/
				sql = "select ref_line_no , invoice_id, inv_amt, frt_amt, item_ser, tran_date, eff_date, curr_code, exch_rate, fin_entity, site_code, line_no__inv, item_code, net_amt, " +
						" quantity__stduom, rate__stduom, inv_disc, comm_frt, comm_insu, amt_inv, sales_pers, comm_perc__on, comm_perc, dlv_term, ord_amt, insu_amt, sorder_no, " +
						" sline_no, curr_code_sp, exch_rate_sp, comm_amt from ( select 1 as ref_line_no,a.invoice_id, a.inv_amt inv_amt, a.frt_amt * d.exch_rate__frt / a.exch_rate frt_amt," +
						" a.item_ser item_ser, a.tran_date tran_date, a.eff_date eff_date, a.curr_code curr_code, a.exch_rate exch_rate, a.fin_entity fin_entity, a.site_code site_code,  " +
						" b.line_no line_no__inv, b.item_code, b.net_amt, b.quantity__stduom, b.rate__stduom, b.discount inv_disc,(case when a.inv_amt = 0 then 0 else (b.quantity__stduom * b.rate__stduom - b.disc_amt) " +
						" / a.inv_amt * a.frt_amt end) comm_frt, (case when d.ord_amt = 0 then 0 else (b.quantity__stduom * b.rate__stduom - b.disc_amt) / d.ord_amt * d.ins_amt end) comm_insu, " +
						" b.quantity__stduom * b.rate__stduom - b.disc_amt amt_inv,b.sord_line_no sline_no, d.sales_pers sales_pers,(case when (case when d.comm_perc__on is null then ' ' " +
						" else d.comm_perc__on end) = ' ' then c.comm_perc_on_1 else d.comm_perc__on end) comm_perc__on, (case when (case when c.comm_perc_1 is null then 0 else c.comm_perc_1 end)" +
						"  = 0 then (case when d.comm_perc is null then 0 else d.comm_perc end) else (case when c.comm_perc_1 is null then 0 else c.comm_perc_1 end) end) comm_perc," +
						" d.dlv_term dlv_term, d.ord_amt ord_amt,d.ins_amt * d.exch_rate__ins / d.exch_rate__comm insu_amt, d.sale_order sorder_no, d.curr_code__comm curr_code_sp, d.exch_rate__comm exch_rate_sp, " +
						" (case when b.sales_pers_comm_1 is null then 0 else b.sales_pers_comm_1 end)  comm_amt from invoice a, invdet b, sorddet c, sorder d where a.invoice_id = b.invoice_id  " +
						" and b.sord_no = c.sale_order 	and b.sord_line_no = c.line_no 	and c.sale_order = d.sale_order and a.invoice_id = ? and (case when d.sales_pers is null then ' ' else d.sales_pers end) <> ' '	" +
						" union all select 2 as ref_line_no, a.invoice_id, a.inv_amt inv_amt, a.frt_amt * d.exch_rate__frt / a.exch_rate frt_amt, a.item_ser item_ser, 	a.tran_date tran_date," +
						"  a.eff_date eff_date, a.curr_code curr_code, a.exch_rate exch_rate, a.fin_entity fin_entity, a.site_code site_code,b.line_no line_no__inv, b.item_code, b.net_amt, b.quantity__stduom, b.rate__stduom," +
						" b.discount inv_disc, (case when a.inv_amt = 0 then 0 else (b.quantity__stduom * b.rate__stduom - b.disc_amt) / a.inv_amt * a.frt_amt end) comm_frt, " +
						" (case when d.ord_amt = 0 then 0 else (b.quantity__stduom * b.rate__stduom - b.disc_amt)  / d.ord_amt * d.ins_amt end) comm_insu, 	b.quantity__stduom * b.rate__stduom - b.disc_amt amt_inv," +
						"  b.sord_line_no sline_no, d.sales_pers__1 sales_pers, (case when (case when d.comm_perc_on_1 is null then ' ' else d.comm_perc_on_1 end) = ' ' then c.comm_perc_on_2 else d.comm_perc_on_1 end) " +
						" comm_perc__on, (case when (case when c.comm_perc_2 is null then 0 else c.comm_perc_2 end) = 0 then (case when d.comm_perc_1 is null then 0 else d.comm_perc_1 end) else " +
						" (case when c.comm_perc_2 is null then 0 else c.comm_perc_2 end) end) comm_perc,d.dlv_term dlv_term, d.ord_amt ord_amt, d.ins_amt * d.exch_rate__ins / d.exch_rate__comm_1 insu_amt," +
						" d.sale_order sorder_no,d.curr_code__comm_1 curr_code_sp, d.exch_rate__comm_1 exch_rate_sp, (case when b.sales_pers_comm_2 is null then 0 else b.sales_pers_comm_2 end) comm_amt " +
						"  from invoice a, invdet b, sorddet c, sorder d where a.invoice_id = b.invoice_id and b.sord_no = c.sale_order and b.sord_line_no = c.line_no 	and c.sale_order = d.sale_order  	" +
						" and a.invoice_id = ? and (case when d.sales_pers__1 is null then ' ' else d.sales_pers__1 end) <> ' '	union all select 3 as ref_line_no, a.invoice_id, a.inv_amt inv_amt,  " +
						" a.frt_amt * d.exch_rate__frt / a.exch_rate frt_amt , a.item_ser item_ser, a.tran_date tran_date, a.eff_date eff_date, a.curr_code curr_code, a.exch_rate exch_rate, a.fin_entity fin_entity, " +
						" a.site_code site_code, b.line_no line_no__inv, b.item_code, b.net_amt, b.quantity__stduom, b.rate__stduom, b.discount inv_disc, 	(case when a.inv_amt = 0 then 0 else (b.quantity__stduom * b.rate__stduom - b.disc_amt)" +
						" / a.inv_amt * a.frt_amt end) comm_frt, (case when d.ord_amt = 0 then 0 else (b.quantity__stduom * b.rate__stduom - b.disc_amt)  / d.ord_amt * d.ins_amt end) comm_insu,  " +
						"  b.quantity__stduom * b.rate__stduom - b.disc_amt amt_inv, b.sord_line_no sline_no, d.sales_pers__2 sales_pers, (case when (case when d.comm_perc_on_2 is null then ' ' else d.comm_perc_on_2 end) = ' ' " +
						" then c.comm_perc_on_3 else d.comm_perc_on_2 end) comm_perc__on, (case when (case when c.comm_perc_3 is null then 0 else c.comm_perc_3 end) = 0 then (case when d.comm_perc_2 is null then 0 else d.comm_perc_2 end) else " +
						" (case when c.comm_perc_3 is null then 0 else c.comm_perc_3 end) end) comm_perc, d.dlv_term dlv_term, d.ord_amt ord_amt,d.ins_amt * d.exch_rate__ins / d.exch_rate__comm_2 insu_amt , d.sale_order sorder_no, " +
						" d.curr_code__comm_2 curr_code_sp, d.exch_rate__comm_2 exch_rate_sp, (case when b.sales_pers_comm_3 is null then 0 else b.sales_pers_comm_3 end)  comm_amt from invoice a, invdet b, sorddet c, sorder d " +
						" where a.invoice_id = b.invoice_id and b.sord_no = c.sale_order and b.sord_line_no = c.line_no and c.sale_order = d.sale_order  and a.invoice_id = ? and (case when d.sales_pers__2 is null then ' ' else d.sales_pers__2 end) <> ' ' ) " +
						" commtab  where ((case when sales_pers is null then ' ' else sales_pers end) <> ' ')  	and	((case when comm_perc__on is null then ' ' else comm_perc__on end) <> ' ') and comm_amt > 0 order by invoice_id, sales_pers, comm_perc__on ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, invoiceId);
				pstmt.setString(2, invoiceId);
				pstmt.setString(3, invoiceId);

				rs = pstmt.executeQuery();
				while(rs.next())
				{
					commMap.put("invoice_id" , rs.getString("invoice_id"));
					commMap.put("ref_line_no" , rs.getString("ref_line_no"));
					commMap.put("inv_amt" ,rs.getDouble("inv_amt"));
					commMap.put("frt_amt" , rs.getDouble("frt_amt"));
					commMap.put("item_ser" , rs.getString("item_ser"));
					commMap.put("tran_date" , rs.getTimestamp("tran_date"));
					commMap.put("eff_date" , rs.getTimestamp("eff_date"));
					commMap.put("curr_code" , rs.getString("curr_code"));
					commMap.put("exch_rate" , rs.getDouble("exch_rate"));
					commMap.put("fin_entity" , rs.getString("fin_entity"));		
					commMap.put("site_code" , rs.getString("site_code"));				
					commMap.put("line_no__inv" , rs.getInt("line_no__inv"));
					commMap.put("item_code" , rs.getString("item_code"));
					commMap.put("net_amt" , rs.getInt("net_amt"));
					commMap.put("quantity__stduom" , rs.getInt("net_amt"));
					commMap.put("rate__stduom" , rs.getInt("rate__stduom"));
					commMap.put("inv_disc" , rs.getInt("inv_disc"));
					commMap.put("comm_frt" , rs.getString("comm_frt"));
					commMap.put("comm_insu" , rs.getString("comm_insu"));
					commMap.put("amt_inv" , rs.getInt("amt_inv"));
					commMap.put("sales_pers" , rs.getString("sales_pers"));
					commMap.put("comm_perc__on" , rs.getString("comm_perc__on"));
					commMap.put("comm_perc" , rs.getDouble("comm_perc"));
					commMap.put("dlv_term" , rs.getString("dlv_term"));					
					commMap.put("ord_amt" , rs.getDouble("ord_amt"));
					commMap.put("insu_amt" ,rs.getDouble("insu_amt"));
					commMap.put("sorder_no" , rs.getString("sorder_no"));
					commMap.put("sline_no " , rs.getInt("sline_no"));
					commMap.put("curr_code_sp" ,rs.getString("curr_code_sp"));
					commMap.put("exch_rate_sp" ,  rs.getDouble("exch_rate_sp"));
					commMap.put("comm_amt" , rs.getDouble("comm_amt"));

					commtList.add(commMap);

				}
			//}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			crnList = new ArrayList<String>();
			for(int cnt = 0 ;commtList.size() > cnt ;cnt++) //change done by kunal on 23/jun/14 loop start with 0
			{
				commMap = new HashMap();
				commMap = (HashMap) commtList.get(cnt);

				refLineNo 	= (String) commMap.get("ref_line_no");
				invoiceId	= (String) commMap.get("invoice_id");
				invAmt		= (Double) commMap.get("inv_amt");
				frtAmt		= (Double) commMap.get("frt_amt");
				itemSer		= (String) commMap.get("item_ser");
				tranDate	= (Timestamp)   commMap.get("tran_date");
				effDate		= (Timestamp)   commMap.get("eff_date") ;
				currCode	= (String) commMap.get("curr_code");
				exchRate	= (Double) commMap.get("exch_rate");
				finEnt		= (String) commMap.get("fin_entity");
				siteCode	= (String) commMap.get("site_code");

				if("Y".equalsIgnoreCase(commHdr))
				{
					lineNoInv = 1;
					itemCode = "";
				}
				else
				{
					lineNoInv	= (Integer) commMap.get("line_no__inv");
					itemCode	= (String) commMap.get("item_code");
				}
				salesPer 	= (String) commMap.get("sales_pers");
				commPercOn	= (String) commMap.get("comm_perc__on");
				commPerc	= (Double) commMap.get("comm_perc");
				ordAmt		= (Double) commMap.get("ord_amt");
				insuAmt		= (Double) commMap.get("insu_amt");
				sorderNo	= (String) commMap.get("sorder_no");
				currCodeSp	=  (String) commMap.get("curr_code_sp") ;
				exchRateSp	= (Double) commMap.get("exch_rate_sp");
				commAmt		= (Double) commMap.get("comm_amt");
				System.out.println("commtList.size() [" + commtList.size() + "] oldSp [" + oldSp + "] salesPer [" + salesPer + "]currcdSpOld [" + currcdSpOld + "] currCodeSp [" + currCodeSp + "]" );
				if(!oldSp.trim().equalsIgnoreCase(salesPer.trim()) || !currcdSpOld.trim().equalsIgnoreCase(currCodeSp.trim()))
				{
				
					if( xmlBuff != null  && (xmlBuff.toString()).length() > 0 )
					{
						xmlBuff.append("</Header0>");
						xmlBuff.append("</group0>");
						xmlBuff.append("</DocumentRoot>");
						crnList.add(xmlBuff.toString());
						xmlBuff = null;	
						xmlBuff = new StringBuffer();
					}
					oldSp = salesPer;
					currcdSpOld = currCodeSp;
					lineNo = 0;
					sql = "select cr_term , tax_class, tax_chap , tax_env  from sales_pers 	where sales_pers = ?";		
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, salesPer);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						crTerm  = rs.getString("cr_term");
						taxClass  = rs.getString("tax_class");
						taxChap  = rs.getString("tax_chap");
					}

					else
					{
						errString = itmDBAccessEJB.getErrorString("","VTSLPERS1 ","","",conn);
						return errString;
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					taxClass  = (taxClass== null ? "" : taxClass);
					taxChap  = (taxChap== null ? "" : taxChap);
					sql = " select cr_days  from crterm where cr_term = ?";	
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, crTerm);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						crdays  = rs.getInt("cr_days");
					}
					else
					{
						errString = itmDBAccessEJB.getErrorString("","VTCRTERM1 ","","",conn);
						return errString;

					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					sql = "select acct_code__ap,  cctr_code__ap  from sales_pers where sales_pers = ?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, salesPer);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						acctCodeAp  = rs.getString(1);
						cctrCodeAp  = rs.getString(2);
					}

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

					sql = "select ins_reqd, frt_reqd  from delivery_term where dlv_term = ?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, dlvTerm);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						insReqd  = rs.getString(1);
						frtReqd  = rs.getString(2);
					}
					else
					{
						errString = itmDBAccessEJB.getErrorString("","VTDLV1 ","","",conn);
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;

					jvExchrate = 0.0;// Added by fatema-20/04/2007-DI7CHT0010

					sql = "select exch_rate  from journal where ref_ser = 'S-INV' and ref_no = ? and ref_line_no = ?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, invno);
					pstmt.setInt(2, Integer.parseInt(refLineNo));
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						exchRate  = rs.getDouble(1);
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;

					sql = "select c.site_code__rcp  from invoice i, customer c where i.invoice_id = ? and	c.cust_code  = i.cust_code ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, invoiceId);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						siteCodeRcp  = rs.getString(1);
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					// Added by fatema - 25/04/2007 - DI7CHT0010
					if(siteCodeRcp == null || siteCodeRcp.trim().length() == 0 || siteCodeRcp.equalsIgnoreCase(siteCode))
					{
						rcpRefser = "S-INV";
					}

					else
					{
						rcpRefser = "R-IBCA";
					}
					// Ended by fatema - 25/04/2007 - DI7CHT0010

					sql = "Select distinct h.site_code from receipt h, rcpdet d where h.tran_id = d.tran_id " +
							" and d.ref_ser = ? and d.ref_no = ? and " +
							" (case when h.confirmed is null then 'N' else h.confirmed end) = 'Y'";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, rcpRefser);
					pstmt.setString(2, invoiceId);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						siteCodeCd  = rs.getString(1);
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;

					if(siteCodeRcp != null && siteCodeRcp.trim().length() > 0)
					{
						siteCode =siteCodeRcp ;
					}
					sql = "select curr_code from finent f, site s where f.fin_entity = s.fin_entity  and	s.site_code  = ?" ;
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, siteCode);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						currCodeBase  = rs.getString(1);
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;

					sql = "Select distinct h.tran_id from  receipt h, rcpdet d where h.tran_id = d.tran_id " +
							"and d.ref_ser = ? and	d.ref_no = ? and  (case when h.confirmed is null then 'N' else h.confirmed end) = 'Y'";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, rcpRefser);
					pstmt.setString(2, invoiceId);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						tranId= rs.getString(1);
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;

					if(!currCode.equalsIgnoreCase(currCodeSp))
					{
						crntExchrate = finCommon.getDailyExchRateSellBuy(currCodeSp, currCodeBase, siteCode, iddate, "S", conn);

					}
					else
					{
						sql = " Select h.exch_rate from receipt h, rcpdet d where h.tran_id = d.tran_id and	d.ref_ser = ?" +
								" and	d.ref_no = ? and (case when h.confirmed is null then 'N' else h.confirmed end) = 'Y'" +
								"order by h.tran_date, h.tran_id desc";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, rcpRefser);
						pstmt.setString(2, invoiceId);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							exchRate= rs.getDouble(1);
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;

						if( crntExchrate > 0 )
						{
							exchRateSp = crntExchrate;
						}
						else
						{
							crntExchrate = exchRateSp;
						}	

					}
					if(jvExchrate == 0)
					{
						if( "P".equalsIgnoreCase(commCalc))
						{
							jvExchrate = exchRate;
						}
						else
						{
							if(currCode.equalsIgnoreCase(currCodeSp))
							{
								jvExchrate = crntExchrate;
							}
						}
					}

					if(jvExchrate == 0)
					{
						jvExchrate = crntExchrate;
					}


					rcpExchRateTot = 0.0;
					count = 0;

					tranType = finCommon.getFinparams("999999", "SALES_COMM_TRAN_TYPE", conn);
					if("NULLFOUND".equalsIgnoreCase(tranType))
					{
						errString = itmDBAccessEJB.getErrorString("","VTFINPARM ","","",conn);
						System.out.println("@@@@@2: retString:"+errString);
					}

					//xmlBuff = null;	
					//xmlBuff = new StringBuffer();
					if( xmlBuff == null )
					{
						xmlBuff = new StringBuffer();
					}

					xmlBuff.append("<?xml version='1.0' encoding='ISO-8859-1'?>\n");
					xmlBuff.append("<DocumentRoot>");
					xmlBuff.append("<description>").append("Datawindow Root").append("</description>");
					xmlBuff.append("<group0>");
					xmlBuff.append("<description>").append("Group0 description").append("</description>");
					xmlBuff.append("<Header0>");
					xmlBuff.append("<objName><![CDATA[").append("drcrinv").append("]]></objName>");     
					xmlBuff.append("<pageContext><![CDATA[").append("1").append("]]></pageContext>");
					xmlBuff.append("<objContext><![CDATA[").append("1").append("]]></objContext>");
					xmlBuff.append("<editFlag><![CDATA[").append("A").append("]]></editFlag>");
					xmlBuff.append("<focusedColumn><![CDATA[").append("").append("]]></focusedColumn>");
					xmlBuff.append("<action><![CDATA[").append("SAVE").append("]]></action>");
					xmlBuff.append("<elementName><![CDATA[").append("").append("]]></elementName>");
					xmlBuff.append("<keyValue><![CDATA[").append("1").append("]]></keyValue>");
					xmlBuff.append("<taxKeyValue><![CDATA[").append("").append("]]></taxKeyValue>");
					xmlBuff.append("<saveLevel><![CDATA[").append("1").append("]]></saveLevel>");
					xmlBuff.append("<forcedSave><![CDATA[").append(true).append("]]></forcedSave>");
					xmlBuff.append("<taxInFocus><![CDATA[").append(false).append("]]></taxInFocus>");
					xmlBuff.append("<description>").append("Header0 members").append("</description>");
					xmlBuff.append("<Detail1 dbID=\"\" domID=\"1\" objName=\"drcrinv\" objContext=\"1\">"); 
					xmlBuff.append("<attribute selected=\"N\" updateFlag=\"A\" status=\"N\" pkNames=\"\"/>");
					xmlBuff.append("<tran_id/>");
					xmlBuff.append("<order_date><![CDATA["+ sdf.format(date).toString() +"]]></order_date>");
					xmlBuff.append("<site_code><![CDATA["+ siteCode +"]]></site_code>");
					xmlBuff.append("<invoice_id><![CDATA["+ invoiceId +"]]></invoice_id>");
					xmlBuff.append("<fin_entity><![CDATA["+ finEnt +"]]></fin_entity>");
					xmlBuff.append("<sundry_type><![CDATA["+ "P" +"]]></sundry_type>");
					xmlBuff.append("<sundry_code><![CDATA["+salesPer+"]]></sundry_code>");
					xmlBuff.append("<item_ser><![CDATA["+ itemSer +"]]></item_ser>");
					xmlBuff.append("<acct_code><![CDATA["+ acctCodeAp +"]]></acct_code>");
					xmlBuff.append("<cctr_code><![CDATA["+ cctrCodeAp +"]]></cctr_code>");
					xmlBuff.append("<eff_date><![CDATA["+ sdf.format(date).toString() +"]]></eff_date>");
					xmlBuff.append("<curr_code><![CDATA["+ currCodeSp +"]]></curr_code>");
					xmlBuff.append("<exch_rate><![CDATA["+ crntExchrate +"]]></exch_rate>");
					xmlBuff.append("<tran_type><![CDATA["+ tranType +"]]></tran_type>");
					// 05-12-02 Arif to set the remarks
					xmlBuff.append("<drcr_flag><![CDATA["+ "C" +"]]></drcr_flag>");
					xmlBuff.append("<remarks><![CDATA["+ remarks +"]]></remarks>");
					xmlBuff.append("<tran_type><![CDATA["+ tranType +"]]></tran_type>");
					xmlBuff.append("<tran_ser><![CDATA["+ tranSer +"]]></tran_ser>");
					xmlBuff.append("<cr_term><![CDATA["+ crTerm +"]]></cr_term>");
					xmlBuff.append("<due_date><![CDATA["+ sdf.format(utilMethods.RelativeDate((new java.util.Date()), crdays)).toString() +"]]></due_date>");
					xmlBuff.append("<acct_code__cf><![CDATA["+ acctCodecf +"]]></acct_code__cf>");
					xmlBuff.append("<cctr_code__cf><![CDATA["+ cctrCodecf +"]]></cctr_code__cf>");
					// 08/08/14 manoharan in order user during receipt dishonour
					if ((!"null".equals(tranIdRcp)) && tranIdRcp != null && tranIdRcp.trim().length() > 0 )
					{
						xmlBuff.append("<tran_id__rcv><![CDATA["+ tranIdRcp +"]]></tran_id__rcv>");
					}
					xmlBuff.append("</Detail1>");


				}
				
				if(lineNoInv == 0)
				{
					invLineNo = lineNoInv;
				}

				//if(errString == null || errString.trim().length() ==0)
				//{
					lineNo++;
					xmlBuff.append("<Detail2 dbID='' domID=\"" + lineNo + "\" objName=\"drcrinv\" objContext=\"2\">"); 
					xmlBuff.append("<attribute pkNames=\"\" selected=\"N\" updateFlag=\"A\" status=\"N\" />");
					xmlBuff.append("<tran_id/>");
					xmlBuff.append("<line_no><![CDATA["+ lineNo +"]]></line_no>");
					xmlBuff.append("<invoice_id><![CDATA["+ invoiceId +"]]></invoice_id>");
					xmlBuff.append("<line_no__inv><![CDATA["+ lineNoInv +"]]></line_no__inv>");
					xmlBuff.append("<item_code><![CDATA["+ itemCode +"]]></item_code>");
					xmlBuff.append("<tax_class><![CDATA["+ taxClass +"]]></tax_class>");
					xmlBuff.append("<tax_chap><![CDATA["+ taxChap +"]]></tax_chap>");
					xmlBuff.append("<tax_env><![CDATA["+ taxEnv +"]]></tax_env>");
					xmlBuff.append("<acct_code><![CDATA["+ acctcodeExp +"]]></acct_code>");
					xmlBuff.append("<cctr_code><![CDATA["+ cctrcodeExp +"]]></cctr_code>");
					xmlBuff.append("<exch_rate__inv><![CDATA["+ jvExchrate +"]]></exch_rate__inv>");
					xmlBuff.append("<drcr_amt><![CDATA["+ commAmt +"]]></drcr_amt>");
					xmlBuff.append("<net_amt><![CDATA["+ commAmt +"]]></net_amt>");
					xmlBuff.append("</Detail2>");	
				//}
				

				if(!("R".equalsIgnoreCase(calledFrom) && "P".equalsIgnoreCase(commCalc)))
				{
					/// 
				}
				else
				{
					// Added by fatema-11/07/2007-FI78CHT006
					sql = "select net_amt from drcr_inv, drcr_invdet  where drcr_inv.tran_id = drcr_invdet.tran_id and " +
							"drcr_inv.invoice_id = ? and drcr_inv.sundry_code = ? " +
							"and drcr_inv.curr_code = ? and drcr_flag = 'D' " +
							"and (case when line_no__inv is null then 0 else line_no__inv end) = ?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,tranIdInvdet );				
					pstmt.setString(2, salesPer);
					pstmt.setString(3, currCodeSp);
					pstmt.setInt(4, invLineNo);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						netAmt = rs.getDouble(1);
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;

				}


				sql = "select due_date  from invoice  where invoice_id = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, invoiceId);
				rs = pstmt.executeQuery();
				if(rs.next())
				{

					dueDate = rs.getTimestamp(1);
				}

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

				System.out.println("2..................dueDate ["+dueDate + "] date [" + date + "]");
				
				days = utilMethods.DaysAfter(dueDate, date); 
				if (days > 0 )
				{

					sql =  "select fin_chg,fchg_type from crtermfc where (min_day <= ? and max_day >= ? ) and  cr_term = ? ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setLong(1,days);
					pstmt.setLong(2, days);
					pstmt.setString(3, crTerm);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						finChg = rs.getDouble(1);
						fchgType = rs.getString(2);
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;

					if("P".equalsIgnoreCase(fchgType))
					{
						acctcodeDeduct = finCommon.getFinparams("999999", "DEDUCT_COMM_ACCT", conn);
						if("NULLFOUND".equalsIgnoreCase(acctcodeDeduct) || acctcodeDeduct== null ||acctcodeDeduct.trim().length() == 0)
						{
							acctcodeDeduct = acctcodeExp;
							cctrcodeDeduct = cctrcodeExp;
						}
						else
						{
							acctcodeDeduct = finCommon.getFinparams("999999", "DEDUCT_COMM_CCTR", conn);
							if("NULLFOUND".equalsIgnoreCase(cctrcodeDeduct) || cctrcodeDeduct== null ||cctrcodeDeduct.trim().length() == 0)
							{

								cctrcodeDeduct = cctrcodeExp;
							}
						}

						expAmt = (drcrAmt * (finChg /100));
						drcrAmt = drcrAmt - expAmt;

					}
					else
					{
						expAmt = finChg;
						drcrAmt = drcrAmt - finChg;
					}
					if (xmlBuff != null )
					{
						lineNo++;
						xmlBuff.append("<Detail2 dbID='' domID=\"" + lineNo + "\" objName=\"drcrinv\" objContext=\"2\">"); 
						xmlBuff.append("<attribute pkNames=\"\" selected=\"N\" updateFlag=\"A\" status=\"N\" />");
						xmlBuff.append("<tran_id/>");
						xmlBuff.append("<line_no><![CDATA["+ lineNo +"]]></line_no>");
						xmlBuff.append("<invoice_id><![CDATA["+ invoiceId +"]]></invoice_id>");
						xmlBuff.append("<line_no__inv><![CDATA["+ lineNoInv +"]]></line_no__inv>");
						xmlBuff.append("<item_code><![CDATA["+ itemCode +"]]></item_code>");
						xmlBuff.append("<tax_class><![CDATA["+ taxClass +"]]></tax_class>");
						xmlBuff.append("<tax_chap><![CDATA["+ taxChap +"]]></tax_chap>");
						xmlBuff.append("<tax_env><![CDATA["+ taxEnv +"]]></tax_env>");
						xmlBuff.append("<acct_code><![CDATA["+ acctcodeDeduct +"]]></acct_code>");
						xmlBuff.append("<cctr_code><![CDATA["+ cctrcodeDeduct +"]]></cctr_code>");
						xmlBuff.append("<exch_rate__inv><![CDATA["+ jvExchrate +"]]></exch_rate__inv>");
						xmlBuff.append("<net_amt><![CDATA["+ (expAmt * -1) +"]]></net_amt>");
						xmlBuff.append("<drcr_amt><![CDATA["+ ((expAmt * -1) * (exchRate/crntExchrate)) +"]]></drcr_amt>");
						xmlBuff.append("</Detail2>");	
					}
				}
			}
			System.out.println("End loop Size of crnList ["+crnList.size() + "]");
			if( xmlBuff != null  && (xmlBuff.toString()).length() > 0 )
			{
				xmlBuff.append("</Header0>");
				xmlBuff.append("</group0>");
				xmlBuff.append("</DocumentRoot>");
				crnList.add(xmlBuff.toString());
			}
			System.out.println("After Size of crnList ["+crnList.size() + "]");
			siteCode = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginSiteCode");
			DrCrInvConf drCrInvConf = new DrCrInvConf();
			for (int ctr = 0;  ctr < crnList.size(); ctr++)
			{
				tranIdCrn = "";
				xmlString = (String) crnList.get(ctr);
				System.out.println("saveDataCrn [" + ctr + "] xmlString ["+xmlString + "]");
				errString = saveDataCrn(siteCode,xmlString,conn);
				
				if((errString == null || errString.trim().length() ==0)	&& ("Y".equals(post)) )
				{
					errString = drCrInvConf.confirm(tranIdCrn,xtraParams,"N", conn);
					if(errString.indexOf("VTCICONF3") > -1)
					{
						errString = "";
					}
					else
					{
						break;
					}
				}
			}
			drCrInvConf = null;
		}catch(Exception e)
		{
			System.out.println(e.getMessage());
			System.out.println("Exception : "+e);
			e.printStackTrace();
			throw new ITMException(e);
		}
		return errString;
	}
	private String saveDataCrn(String siteCode,String xmlString, Connection conn) throws ITMException
	{
		System.out.println("saving data...........");
		InitialContext ctx = null;
		String retString = null;
		MasterStatefulLocal masterStateful = null; // for ejb3
		try
		{
			AppConnectParm appConnect = new AppConnectParm();
			ctx = new InitialContext(appConnect.getProperty());
			masterStateful = (MasterStatefulLocal)ctx.lookup("ibase/MasterStatefulEJB/local");
			System.out.println("-----------masterStateful------- " + masterStateful);
			String [] authencate = new String[2];
			authencate[0] = "";
			authencate[1] = "";
			System.out.println("xmlString to masterstateful [" + xmlString + "]");
			retString = masterStateful.processRequest(authencate, siteCode, true, xmlString,true,conn);
			if (retString.indexOf("Success") > -1)
			{
				System.out.println("retString.indexOf(Success) > -1)");
				String[] arrayForTranId = retString.split("<TranID>");
				int endIndex = arrayForTranId[1].indexOf("</TranID>");

				tranIdCrn = arrayForTranId[1].substring(0,endIndex);
				//retString ="";
				System.out.println("@@@@@@3: retString ["+retString + "]");
				System.out.println("Credit Note TranID------>>>["+tranIdCrn+"]");
				if(tranIdCrn != null && tranIdCrn.trim().length() > 0)
				{
					System.out.println("tran_id__drn generated successfully----------[" + tranIdCrn + "]");	
					retString = "";
				}	
			}
		}
		catch(ITMException itme)
		{
			System.out.println("ITMException :CreateDistOrder :saveData :==>");
			throw itme;
		}
		catch(Exception e)
		{
			e.printStackTrace();
			System.out.println("Exception :CreateDistOrder :saveData :==>");
			throw new ITMException(e);
		}
		return retString;
	}

}