package ibase.webitm.ejb.fin;

import ibase.utility.CommonConstants;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.utility.TransIDGenerator;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;
import ibase.webitm.utility.*;
import ibase.system.config.*;
import ibase.webitm.ejb.*;
import java.sql.*;
import java.text.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.HashMap;
import javax.naming.InitialContext;
import org.w3c.dom.*;

import ibase.webitm.ejb.fin.FinCommon;
import ibase.webitm.ejb.dis.DistCommon;
import ibase.webitm.ejb.sys.UtilMethods;

public class CreatePoRcpVoucher
{
	GenericUtility genericUtility = GenericUtility.getInstance();
	FinCommon finCommon = new FinCommon();
	DistCommon distCommon = new DistCommon();
	ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
	public String createPoRcpVoucher(String tranId,String xtraParams, Connection conn) throws ITMException
	{
		////////////////////////////////////////////////////////////////////////////////////////////////////
		//gbf_porcp_voucher(as_tran_id, ai_commit, as_warning)
		////////////////////////////////////////////////////////////////////////////////////////////////////
		/*
		string ls_site,ls_po,ls_supp,ls_tran_code,ls_curr,ls_errcode, ls_pay_mode,ls_acct_ap_item,ls_cctr_ap_item, &
				 ls_bank,ls_cr_term, ls_fin_ent,ls_anal,ls_proj,ls_emp,ls_emp_aprv,ls_key,ls_win,ls_vouch_id,&
				 ls_rcp_line,ls_po_rcp,ls_po_line,ls_rcp_item, ls_rcp_unit,ls_taxclass,ls_taxchap,ls_taxenv,&
				 ls_itemser,ls_acct_diff,ls_cctr_diff,ls_rcp,ls_acct_ph,ls_cctr_ph,ls_item,ls_retid,ls_sqlerr,&
				 ls_transer,ls_acct_ap, ls_cctr_ap,ls_refno, ls_acctcode, ls_cctrcode,ls_curradv,ls_stype,ls_scode,&
				 ls_refser, ls_remarks, ls_item_ser, ls_ledg_post_conf, ls_bankcode, ls_rndoff, ls_rndstr, ls_type,&
				 ls_ret_opt,ls_tax_var ,li_line_pick, ls_msg_type, ls_tax_var1, ls_tax_var2, ls_conf_fin, ls_confirmed, &
				 ls_inv_online, ls_porcp_online, ls_cctr_pr, ls_acct_pr, ls_invno, ls_dcno,&
				 ls_tran_type, ls_convert, ls_advtype, ls_supp_code__pay, ls_post_prov, ls_acct_pdr,ls_cctr_pdr,&
				 ls_acct_pcr,ls_cctr_pcr, ls_bankcd_pay, ls_acct_dr,ls_cctr_dr,ls_acct_cr,ls_cctr_cr 		
		integer li_line = 0 , li_cnt = 0 , li_ln, li_rndto
		decimal{6} lc_exch, lc_exchadv, lc_amt
		datetime ldt_trandt, ldt_effdt, ldt_taxdt, chgDate, ld_duedate, ld_rcpdt, ld_dcdate, ld_invdate, &
					ldt_rec_date
		long		lc_qc, ll_cnt, ll_relafter
		boolean	lb_qc_reqd
		decimal{3} lc_amount,lc_discount,lc_tax,lc_net_amt,lc_net_amt_base,&
					  lc_ord_amt,lc_tax_amt,lc_tot_amt,lc_rcp_quantity, lc_var1, lc_var3, &
					  lc_rcp_discount,lc_rcp_tax,lc_rcp_net,lc_rcp_qty_std,lc_advtot,&
					  lc_rcp_amount,lc_rcp_disc_amt,lc_vouch_amount, lc_tax_np, lc_rcptax_np, &
					  lc_vouch_quantity, lc_totamt, lc_advamt, lc_diffexch,lc_difftot, lc_taxmult, &
					  lc_porcp_tax, lc_rate_clg, lc_purc_rate, lc_purc_rate_std, lc_purc_disc, & 
					  lc_rate__clg, lc_std_rate, ld_advperc, lc_reten_amt,lc_netamt_adj , lc_advadj, lc_netamt_bc
		dec{4}	lc_rcp_rate_std,lc_rcp_rate
		// Arif 09-04-03
		string 	ls_acct_code__cf,ls_acct_code__cf_ap,ls_cctr_code__cf,ls_cctr_code__cf_ap,&
					ls_posttype, LS_TRAN_SER, ls_dept_code
		// End Arif 09-04-03
		*/

		PreparedStatement pstmt = null, pstmt1 = null, pstmtUpd = null;
		ResultSet rs = null, rs1 = null;

		java.sql.Timestamp  tranDate = null, effectiveDate = null, taxDate = null, dueDate = null;
		java.sql.Timestamp today = null, invoiceDate = null, DcDate = null, receiptDate = null, hdrRcpDate = null;
		String errCode = "", errString = "", winName = "", roundString = "",roundOff = "", confFinVar = "N", autoConfirm = "N";
		double diffTotal = 0, advTotal = 0, amount = 0, discount = 0, taxAmt = 0, exchRate = 0, suppBillAmount = 0, rcpTaxNP = 0;
		int roundTo = 0, lineCount = 0, count = 0, ctr = 0;

		String tranType = "", purcOrder = "", suppCode = "", tranCode = "", currCode = "", tranSer = "", taxClass = "", taxEnv = "", taxChap = "";
		String remarks = "", siteCode = "", invoiceNo = "", DcNo = "", retOpt = "", postType = "", itemSer = "";
		String invOnLine = "", poRcpOnLine = "", postProv = "",	crTerm = "", projCode = "", pordType = "";
		String analCode = "", bankCodePay = "", acctCodePur = "", cctrCodePur = "", payMode = "", profileId = "";
		String finEntity = "", bankCode = "", keyString = "", ledgPostConf = "",whetherToConvert = "N", userId = "SYSTEM", termId = "SYSTEM";
		java.text.SimpleDateFormat sdf = null;
		String acctCodeCF = "", acctCodeCFAP = "", cctrCodeCF = "", cctrCodeCFAP = "", suppCodePay = "";
		String rcpLine = "", purcOrderDet = "", lineNoOrd = "", rcpItemCode = "", rcpUnit = "";
		String acctCodeDR = "", cctrCodeDR = "", deptCode = "", acctCodeCR = "", cctrCodeCR = "";
		String acctCodeProvDR = "", cctrCodeProvDR = "", acctCodeProvCR = "", cctrCodeProvCR = "";
		double rcpQuantity = 0, rcpRate = 0, rcpDiscount = 0, rcpTaxAmount = 0, rcpNetAmount = 0, rcpQtyStd = 0;
		double rcpRateStd = 0,purcRate = 0, purcDisc = 0, rateDiscount[] = {0,0}, netAmount = 0, netAmountBase = 0, rateClg = 0, stdRate = 0;		
		String acctCodePH = "", ccrtCodePH = "", taxVar = "", acctCodePPV = "", cctrCodePPV = "", returnId = "", sql = "";
		double rcpDiscAmt = 0, rcpAmount = 0, vouchQuantity = 0, vouchAmount = 0, taxMultiplier = 1, totAmount = 0, purcRateStd = 0;
		String chgDate = null, acctCodeAP = "", ccrtCodeAP = "", msgType = "", taxVar1 = "", taxVar2 = "";
		
		MasterStatefulLocal masterStatefulLocal = null;
		try
		{
			AppConnectParm appConnect = new AppConnectParm();
			Properties p = appConnect.getProperty();
			InitialContext ctx = new InitialContext(p);

			userId = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginCode");
			sql = "SELECT PROFILE_ID FROM USERS WHERE CODE = ? ";

			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,userId);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				profileId = rs.getString("PROFILE_ID");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			
			today = new java.sql.Timestamp(System.currentTimeMillis());
			sdf = new java.text.SimpleDateFormat(genericUtility.getDBDateTimeFormat());
			//chgDate = sdf.parse(today.toString());
			winName = "w_voucher";
			diffTotal = 0;
			advTotal = 0;
			roundString = "VOUCH-RND";
			roundOff = finCommon.getFinparams("999999", roundString, conn);
			if (!"NULLFOUND".equals(roundOff))
			{
				roundString = "VOUCH-RNDTO";
				roundString = finCommon.getFinparams("999999", roundString, conn);
				System.out.println("roundString.....::"+roundString);
				if (!"NULLFOUND".equals(roundString))
				{
					roundTo = Integer.parseInt(roundString);
				}
			}
			// added by deepali  18/03/04
			confFinVar = finCommon.getFinparams("999999", "AUTO_CONF_VOUCH", conn);
			if (!"NULLFOUND".equals(confFinVar))
			{
				if ("Y".equals(confFinVar))
				{
					autoConfirm = "Y";
				}
				else // for no variable value
				{
					autoConfirm = "N";			
				}
			}
			else // for null variable value
			{
					autoConfirm = "N";					
			}
			////
			//Converted nvl(porcp.discount,0) to case by Archiman 12/08/08
			//Converted nvl(ret_opt,' ') to case by Archiman 12/08/08
				//In below sql rec_date is added by jasmina 09/04/09-FI89SUN026
			sql = "SELECT TRAN_DATE, TRAN_TYPE, EFF_DATE, PURC_ORDER, SUPP_CODE, "
				+ "	TRAN_CODE, AMOUNT, (CASE WHEN DISCOUNT IS NULL THEN 0 ELSE DISCOUNT END) AS DISCOUNT, "
				+ " TAX, CURR_CODE, EXCH_RATE, TAX_DATE, TRAN_SER, REMARKS, SITE_CODE, "
				+ " INVOICE_NO, INVOICE_DATE, DC_NO, DC_DATE,(CASE WHEN RET_OPT IS NULL THEN '' ELSE RET_OPT END ) AS RET_OPT, " 
				+ " POST_TYPE, ITEM_SER, ACCT_INV_ONLINE, ACCT_PORCP_ONLINE, POST_PROV,	REC_DATE "
				+ " FROM PORCP WHERE TRAN_ID = ? " //
				+ " AND CONFIRMED = 'Y' AND (VOUCH_CREATED IS NULL OR VOUCH_CREATED = 'N') ";

			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,tranId);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				tranDate =  rs.getTimestamp("TRAN_DATE");
				tranType =  rs.getString("TRAN_TYPE");
				effectiveDate =  rs.getTimestamp("EFF_DATE");
				purcOrder =  rs.getString("PURC_ORDER");
				suppCode =  rs.getString("SUPP_CODE");
				tranCode =  rs.getString("TRAN_CODE");
				amount =  rs.getDouble("AMOUNT");
				discount =  rs.getDouble("DISCOUNT");
				taxAmt =  rs.getDouble("TAX");
				currCode =  rs.getString("CURR_CODE");
				exchRate =  rs.getDouble("EXCH_RATE");
				taxDate =  rs.getTimestamp("TAX_DATE");
				tranSer =  rs.getString("TRAN_SER");
				remarks =  rs.getString("REMARKS");
				siteCode =  rs.getString("SITE_CODE");
				invoiceNo =  rs.getString("INVOICE_NO");
				invoiceDate =  rs.getTimestamp("INVOICE_DATE");
				DcNo =  rs.getString("DC_NO");
				DcDate =  rs.getTimestamp("DC_DATE");
				retOpt =  rs.getString("RET_OPT");
				postType =  rs.getString("POST_TYPE");
				itemSer =  rs.getString("ITEM_SER");
				invOnLine =  rs.getString("ACCT_INV_ONLINE");
				poRcpOnLine =  rs.getString("ACCT_PORCP_ONLINE");
				postProv =  rs.getString("POST_PROV");
				hdrRcpDate =  rs.getTimestamp("REC_DATE");
			}
			else
			{
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
				errCode = "VTPORCP2";
				errString = itmDBAccessEJB.getErrorString("",errCode,"","",conn);
				errString = addTraceMsg(errString, "Purchase receipt not found for " + tranId, "Check transaction Id");
				return errString;
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			if (hdrRcpDate == null)
			{
				hdrRcpDate = tranDate;
			}
			// tranType added by sheetal 12/10/02
			if (retOpt == null || retOpt.trim().length() ==  0 )
			{
				retOpt = " "; // Added By Archiman 09.08.08
			}

			/// added by sachin 15-11-2002 for not creating voucher for replacment type return
			/// no voucher for ret_option = 'R'
			if ("P-RET".equals(tranSer) && "R".equals(retOpt))
			{
				errCode = "VTREPL";
				errString = itmDBAccessEJB.getErrorString("",errCode,"","",conn);
				errString = addTraceMsg(errString, "Invalid Voucher Creation for Replacement", "Check return option");

				return errString;
			}
			// neelam - added a variable to set amount from the porcp in the voucher to print in the report
			suppBillAmount = amount;
			
			// shiri 22/04/02 changed sql to include pord_type
			// shiri 09/05/02 moved sql from down, as ls_type is required first
			// In below select statement, bank_code__pay added by fatema-31/01/2008-FI78SUN034
			sql = "SELECT CR_TERM, PROJ_CODE, PORD_TYPE, ANAL_CODE, BANK_CODE__PAY "
				+ " FROM PORDER WHERE PURC_ORDER = ? " ;//
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,purcOrder);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				crTerm = rs.getString("CR_TERM");
				projCode = rs.getString("PROJ_CODE");
				pordType = rs.getString("PORD_TYPE");
				analCode = rs.getString("ANAL_CODE");
				bankCodePay = rs.getString("BANK_CODE__PAY");
			}
			hdrRcpDate = tranDate;
			// added by shiri 20/12/01 as tax should not contain the non_payable = 'Y'
			// Changed by amit - 4-3-02 - to take sum of amt. rounded linewise.
			//Removed TABLE keyword --Not work in DB2 ISeries -- Supreme --Prince --17-05-06
			// TABLE( select tran_id, line_no, round(sum(nvl(tax_amt,0)),2) tot_tax  //prince --17-05-06
			if ("db2".equalsIgnoreCase(CommonConstants.DB_NAME ))
			{
				sql = "SELECT SUM(TOT_TAX) AS TAXAMT FROM  "
					+ " ( SELECT TRAN_ID, LINE_NO, ROUND(SUM(CASE WHEN TAX_AMT IS NULL THEN 0 ELSE TAX_AMT END ),2) TOT_TAX "
					+ "	FROM TAXTRAN A, TAX B "
					+ " WHERE A.TAX_CODE = B.TAX_CODE "
					+ " AND A.TRAN_CODE = ? "
					+ " AND A.TRAN_ID = ? "
					+ " AND B.EFFECT <> 'N' "
					+ "	AND (CASE WHEN B.NON_PAYABLE IS NULL THEN 'N' ELSE B.NON_PAYABLE END ) <> 'Y' "
					+ " GROUP BY TRAN_ID, LINE_NO ) MANOHAR ";
			}
			else
			{
				sql = "SELECT SUM(TOT_TAX) AS TAXAMT FROM  "
					+ " ( SELECT TRAN_ID, LINE_NO, ROUND(SUM(CASE WHEN TAX_AMT IS NULL THEN 0 ELSE TAX_AMT END ),2) TOT_TAX "
					+ "	FROM TAXTRAN A, TAX B "
					+ "	WHERE A.TAX_CODE = B.TAX_CODE "
					+ "	AND A.TRAN_CODE = ? "
					+ "	AND A.TRAN_ID = ? "
					+ "	AND B.EFFECT <> 'N' "
					+ "	AND (CASE WHEN B.NON_PAYABLE IS NULL THEN 'N' ELSE B.NON_PAYABLE END ) <> 'Y' "
					+ "	GROUP BY TRAN_ID, LINE_NO ) ARCHIMAN ";
			}
			//// end.
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranId);
			pstmt.setString(2, tranSer);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				taxAmt = rs.getDouble("TAXAMT");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
				
			amount = amount - taxAmt;
			//Commented n shifted above- for sql optimization -jasmina-09/04/09-FI89SUN026
			//// In below select-Added post_prov - By fatema - 04/04/2007 - DI7SUN0018
			//select item_ser, acct_inv_online, acct_porcp_online, post_prov 
			//	into :itemSer, :invOnLine, :poRcpOnLine, :postProv
			//		from porcp
			//		where tran_id = :as_tran_id;
			//if get_sqlcode() < 0 then
			//	ls_errcode = 'DS000' + trim(string(sqlca.sqldbcode)) + ' ~t ' + sqlca.sqlerrtext
			//	exit
			//elseif get_sqlcode() = 100 then
			//	ls_errcode = 'VTPORCP2' + ' ~t' + ' for po rcp. :' + as_tran_id
			//	exit
			//end if
			//End, Commented n shifted above- for sql optimization -jasmina-09/04/09-FI89SUN026
			if (invOnLine == null || invOnLine.trim().length() == 0)
			{
				invOnLine = "N";
			}
			if (poRcpOnLine == null || poRcpOnLine.trim().length() == 0)
			{
				poRcpOnLine = "N";
			}
			if (postProv == null || postProv.trim().length() == 0)
			{
				postProv = "N";
			}
			// shiri modified and put the else to take a diff. credit account in case the inventory accounting is on 28/11/01
			if ("Y".equals(invOnLine) && "N".equals(postProv)) // Condn to check for postProv = 'N' Added by fatema-20/06/2007-DI7SUN0018
			{
				// shiri 22/04/02 changed func call from gbf_acct_detr to gbf_acct_detr_type for a/cing on tran type
				cctrCodePur = finCommon.getAcctDetrTtype("", itemSer, "PORCP", pordType, conn);
				if (cctrCodePur != null && cctrCodePur.trim().length() > 0)
				{
					acctCodePur = cctrCodePur.substring(0,cctrCodePur.indexOf(","));
					cctrCodePur = cctrCodePur.substring(cctrCodePur.indexOf(",")+1);
				}
				sql = "SELECT ACCT_CODE__AP, CCTR_CODE__AP " 
					+ " FROM SUPPLIER WHERE SUPP_CODE = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, suppCode);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					acctCodeAP = rs.getString("ACCT_CODE__AP");
					ccrtCodeAP = rs.getString("CCTR_CODE__AP");
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;

				if (acctCodeAP == null || acctCodeAP.trim().length() == 0)
				{
					// shiri 22/04/02 changed func call from gbf_acct_detr to gbf_acct_detr_type for a/cing on tran type
					ccrtCodeAP = finCommon.getAcctDetrTtype("",itemSer,"POVOU", pordType, conn);
					if (ccrtCodeAP != null && ccrtCodeAP.trim().length() > 0)
					{
						acctCodeAP = ccrtCodeAP.substring(0,ccrtCodeAP.indexOf(","));
						ccrtCodeAP = ccrtCodeAP.substring(ccrtCodeAP.indexOf(",")+1);
					}
				}
			}
			else
			{
				sql = "SELECT COUNT(DISTINCT ACCT_CODE__CR) LINE_NO " 
					+ " FROM PORCPDET WHERE TRAN_ID = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, tranId);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					lineCount = rs.getInt("LINE_NO");
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
				
				if (lineCount == 1)
				{
					sql = "SELECT ACCT_CODE__CR, CCTR_CODE__CR " 
						+ "FROM PORCPDET WHERE TRAN_ID = ? "
						+ " AND LINE_NO = ( SELECT MIN(LINE_NO) FROM PORCPDET WHERE TRAN_ID = ? ) ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, tranId);
					pstmt.setString(2, tranId);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						acctCodeAP = rs.getString("ACCT_CODE__CR");
						ccrtCodeAP = rs.getString("CCTR_CODE__CR");
					}
					else
					{
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						errCode = "VTPORCP2";
						errString = itmDBAccessEJB.getErrorString("",errCode,"","",conn);
						//errString = addTraceMsg(errString, "Invalid Voucher Creation for Replacement", "Check return option");

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

					if (acctCodeAP == null) 
					{
						lineCount = 2;
					}
				}
				if (lineCount > 1)
				{
					// shiri 22/04/02 changed func call from gbf_acct_detr to gbf_acct_detr_type for a/cing on tran type
					ccrtCodeAP = finCommon.getAcctDetrTtype("",itemSer,"POVOU", pordType, conn);
					if (ccrtCodeAP != null && ccrtCodeAP.trim().length() > 0)
					{
						acctCodeAP = ccrtCodeAP.substring(0,ccrtCodeAP.indexOf(","));
						ccrtCodeAP = ccrtCodeAP.substring(ccrtCodeAP.indexOf(",")+1);
					}
				}
				lineCount = 0;
			}
			// Changes end
			if (acctCodeAP == null || acctCodeAP.trim().length() == 0)
			{
				errCode = "VMACCT1";
				errString = itmDBAccessEJB.getErrorString("",errCode,"","",conn);
				errString = addTraceMsg(errString, "AP account not defined", "Check AP A/c");
				return errString;
			}
			// shiri 01/01/02 cost centre was blank
			if (ccrtCodeAP ==  null  || ccrtCodeAP.trim().length() == 0)
			{
				sql = "SELECT CCTR_CODE__CR " 
					+ "FROM PORCPDET WHERE TRAN_ID = ? "
					+ " AND LINE_NO = ( SELECT MIN(LINE_NO) FROM PORCPDET WHERE TRAN_ID = ? ) ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, tranId);
				pstmt.setString(2, tranId);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					ccrtCodeAP = rs.getString("CCTR_CODE__CR");
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
			}
			if (ccrtCodeAP == null) 
			{
				ccrtCodeAP = " ";
			}
			sql = "SELECT PAY_MODE, SUPP_CODE__PAY FROM SUPPLIER WHERE SUPP_CODE = ?" ;
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, suppCode);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
			
				payMode = rs.getString("PAY_MODE");
				suppCodePay = rs.getString("SUPP_CODE__PAY");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;

			sql = "SELECT FIN_ENTITY, BANK_CODE FROM SITE WHERE SITE_CODE = ?" ;
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, siteCode);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				finEntity = rs.getString("FIN_ENTITY");
				bankCode = rs.getString("BANK_CODE");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;

			sql = "SELECT KEY_STRING, LEDG_POST_CONF FROM TRANSETUP WHERE TRAN_WINDOW = ?" ;
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, winName);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				keyString = rs.getString("KEY_STRING");
				ledgPostConf = rs.getString("LEDG_POST_CONF");
			}
			else
			{
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
				sql = "SELECT KEY_STRING, LEDG_POST_CONF FROM TRANSETUP WHERE TRAN_WINDOW = 'GENERAL'" ;
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					keyString = rs.getString("KEY_STRING");
					ledgPostConf = rs.getString("LEDG_POST_CONF");
				}
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			// if the posting is on conf. date then set tran date to today
			if ("Y".equals(ledgPostConf) )
			{
				sdf = new java.text.SimpleDateFormat(genericUtility.getDBDateFormat());
				//Commented and changes by gulzar - 25/11/11
				//tranDate = java.sql.Timestamp.valueOf( sdf.parse(today.toString()) + " 00:00:00.0") ;
				tranDate = java.sql.Timestamp.valueOf( sdf.format(today) + " 00:00:00.0" );
				//End changes by gulzar - 25/11/11
			}

			//Updating voucher header
			if ("P-RET".equals(tranSer))
			{
				amount   = 0 - amount;
				taxAmt	   = 0 - taxAmt;
				discount = 0 - discount;
				dueDate  = tranDate;
			}
			else
			{
				//Due date = rec_date + crdays iff start_from is receipt in crterm master, 
				//for this Commented & added jasmina 09/04/09-FI89SUN026
				
				//dueDate  = gbf_calc_duedate(crTerm, receiptDate, effectiveDate, invoiceDate)
				if (receiptDate == null) 
				{
					receiptDate = hdrRcpDate;
				}

				dueDate = calcDueDate(crTerm, receiptDate, effectiveDate, invoiceDate, conn);

				//Commented & added ended jasmina 09/04/09-FI89SUN026
			}

			if (remarks == null || remarks.trim().length() == 0)
			{

				remarks = "Against purchase receipt no. : " + tranId;
			}
			totAmount = amount; //tot_amount = bill_amount+tax_amt-discount
			netAmount = totAmount;
			netAmountBase = totAmount * exchRate;
			//jyoti
			// shiri modified to insert inv no/ dc no and inv date / dc date from porcp into voucher bill no and bill date - 04/12/01
			//	if isnull(invoiceNo) then invoiceNo = DcNo
			//	if isnull(invoiceDate) then invoiceDate = DcDate
			//	remarks = trim(remarks)
			//added by jyoti 20/9/04.
			//If 'Y' only then the DC_NO will be set 
			whetherToConvert  = distCommon.getDisparams("999999","INV_TO_DC",conn);
			if ("NULLFOUND".equals(whetherToConvert))
			{
				whetherToConvert =  "N";
			}
			if ("Y".equals(whetherToConvert))
			{
				// shiri modified to insert inv no/ dc no and inv date / dc date from porcp into voucher bill no and bill date - 04/12/01
				if (invoiceNo == null) 
				{
					invoiceNo = DcNo;
				}
				if (invoiceDate == null) 
				{
					invoiceDate = DcDate;
				}
			}
			////
			
			//***************************************************************************************
			// Start Block
			//Added by Nilesh to check for duplicate bill no - 04/04/03.
			//***************************************************************************************
			//from bill_no ltrim is removed by Tushar 20/03/09
			sql = "SELECT COUNT(1) FROM VOUCHER "
				+ " WHERE SUPP_CODE  = ? "
				+ " AND BILL_NO = ? "
				+ " AND BILL_DATE = ? "
				+ " AND TRAN_ID <> ? ";
				
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, suppCode);
			pstmt.setString(2, invoiceNo);
			pstmt.setTimestamp(3, invoiceDate);
			pstmt.setString(4, tranId);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				count = rs.getInt(1);
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			//added by rajesh  FI89SUN001 -23/04/2008
			if (count == 0)
			{
				//from bill_no ltrim is removed by Tushar 20/03/09
				sql = "SELECT COUNT(1) FROM MISC_VOUCHER "
					+ " WHERE SUNDRY_CODE  = ? "
					+ " AND BILL_NO = ? "
					+ " AND BILL_DATE = ? "
					+ " AND TRAN_ID <> ? ";
					
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, suppCode);
				pstmt.setString(2, invoiceNo);
				pstmt.setTimestamp(3, invoiceDate);
				pstmt.setString(4, tranId);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					count = rs.getInt(1);
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
			}
			//end by rajesh 	FI89SUN001	
			
			if (count > 0 )
			{
				//Check if message is defined at messages level, if not then check in messages,
				//if not in messages then return message type as 'Error'.
				sql = "SELECT MSG_TYPE FROM MESSAGES_LEVEL "
					+ " PROFILE_ID = ? " //
				    + " AND MSG_NO = 'VTBILL1' "
					+ " AND WIN_NAME = ? " ; 
					
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, profileId);
				pstmt.setString(2, winName);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					msgType = rs.getString(1);
				}
				else
				{
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					sql = "SELECT MSG_TYPE FROM MESSAGES "
						+ " AND MSG_NO = 'VTBILL1' ";
					pstmt = conn.prepareStatement(sql);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						msgType = rs.getString(1);
					}
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
			
				if ("E".equals(msgType))
				{
					errCode = "VTBILL1";
					errString = itmDBAccessEJB.getErrorString("",errCode,"","",conn);
					errString = addTraceMsg(errString, "Duplicate Bill NUmber", "Check Bill Number");
					return errString;
				}
			}
			//***************************************************************************************
			// End Block
			//***************************************************************************************


			// Arif 09-04-03	
			sql = "SELECT ACCT_CODE__CF, ACCT_CODE__CF_AP, CCTR_CODE__CF, CCTR_CODE__CF_AP "
				+ " FROM BANK WHERE BANK_CODE  = ? ";
				
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, bankCode);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				acctCodeCF = rs.getString("ACCT_CODE__CF");
				acctCodeCFAP = rs.getString("ACCT_CODE__CF_AP");
				cctrCodeCF = rs.getString("CCTR_CODE__CF");
				cctrCodeCFAP = rs.getString("CCTR_CODE__CF_AP");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			
			
			if (acctCodeCFAP == null || acctCodeCFAP.trim().length() == 0)
			{
				acctCodeCFAP = acctCodeCF;
			}
			
			if (cctrCodeCFAP == null || cctrCodeCFAP.trim().length() == 0)
			{
				cctrCodeCFAP = cctrCodeCF;
			}
			
			// End Arif 09-04-03
			// Arif 09-04-03 .. added acct_code__cf & cctr_code__cf in insert statement
			//	insert into voucher  
			//         ( tran_id, tran_date, tran_type, eff_date, supp_code, bill_no, bill_date, purc_order,   
			//           curr_code, exch_rate, acct_code, cctr_code, bank_code, auto_pay, 
			//			  adv_amt, cr_term, due_date, chg_date, chg_user, chg_term, site_code, 
			//			  fin_entity, bill_amt, tax_amt, tot_amt, tax_date, vouch_type, anal_code,   
			//           proj_code, confirmed, pay_mode, net_amt, net_amt__bc, diff_amt__exch,
			//			  supp_bill_amt, remarks, rnd_off, rnd_to, tran_mode,
			//			  acct_code__cf, cctr_code__cf)  
			//  values ( :ls_vouch_id, :tranDate, :tranType, :receiptDate, :suppCode, :invoiceNo, :invoiceDate, :purcOrder,   
			//           :currCode, :exchRate, :acctCodeAP, :ccrtCodeAP, :bankCode, 'Y',
			//			  0, :crTerm, :dueDate, :chgDate, :userid, :termid, :siteCode,
			//			  :finEntity, :amount, :taxAmt, :totAmount, :taxDate, 'P', :analCode,   
			//           :projCode, 'N', :payMode, :netAmount, :netAmountBase, 0,
			//			  :totAmount, :remarks, :roundOff, :roundTo, 'A',
			//			  :acctCodeCFAP,:cctrCodeCFAP)  ;

			if ( analCode == null || analCode.trim().length() ==  0)
			{
				analCode = "";
			}
			//added by prince --18-01-07	
			if (suppCodePay == null || suppCodePay.trim().length() == 0)
			{
				suppCodePay	 = suppCode;
			}
			//ended by prince
			// prince --18-01-07--suppCode -- changed to suppCodePay --if supp_code_pay is existing in supplier master 
			// then voucher supplier code should come supp_code_pay
			// In below insert statement, bank_code__pay added by fatema-31/01/2008-FI78SUN034

			StringBuffer xmlBuff = new StringBuffer();
			String xmlString = null;
			
			String [] authencate = new String[2];
            authencate[0] = "";
            authencate[1] = "";

			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("voucher").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=\"voucher\" objContext=\"1\">");
			xmlBuff.append("<attribute selected=\"N\" updateFlag=\"A\" status=\"N\" pkNames=\"\"/>");
			xmlBuff.append("<tran_id/>");

			today = new java.sql.Timestamp(System.currentTimeMillis());
			sdf = new java.text.SimpleDateFormat(genericUtility.getApplDateFormat());

			xmlBuff.append("<tran_date><![CDATA[" + sdf.format(today) + "]]></tran_date>");
			xmlBuff.append("<tran_type><![CDATA[" + (tranType==null?"":tranType) + "]]></tran_type>");
			if ( receiptDate != null )
			{
				xmlBuff.append("<eff_date><![CDATA[" + sdf.format(receiptDate) + "]]></eff_date>");
			}
			xmlBuff.append("<supp_code><![CDATA[" + (suppCodePay==null?"":suppCodePay) + "]]></supp_code>");
			xmlBuff.append("<bill_no><![CDATA[" + (invoiceNo==null?"":invoiceNo) + "]]></bill_no>");
			if ( invoiceDate != null )
			{
				xmlBuff.append("<bill_date><![CDATA[" + sdf.format(invoiceDate) + "]]></bill_date>");
			}
			xmlBuff.append("<purc_order><![CDATA[" + (purcOrder==null?"":purcOrder) + "]]></purc_order>");
			xmlBuff.append("<curr_code><![CDATA[" + (currCode==null?"":currCode) + "]]></curr_code>");
			xmlBuff.append("<exch_rate><![CDATA[" + exchRate + "]]></exch_rate>");
			xmlBuff.append("<acct_code><![CDATA[" + (acctCodeAP==null?"":acctCodeAP) + "]]></acct_code>");
			xmlBuff.append("<cctr_code><![CDATA[" + (ccrtCodeAP==null?"":ccrtCodeAP) + "]]></cctr_code>");
			xmlBuff.append("<bank_code><![CDATA[" + (bankCode==null?"":bankCode) + "]]></bank_code>");
			xmlBuff.append("<auto_pay><![CDATA[" + "Y" + "]]></auto_pay>");
			xmlBuff.append("<adv_amt><![CDATA[0]]></adv_amt>");
			xmlBuff.append("<cr_term><![CDATA[" + (crTerm==null?"":crTerm) + "]]></cr_term>");
			if ( dueDate != null )
			{
				xmlBuff.append("<due_date><![CDATA[" + sdf.format(dueDate) + "]]></due_date>");
			}
			xmlBuff.append("<site_code><![CDATA[" + siteCode + "]]></site_code>");
			xmlBuff.append("<fin_entity><![CDATA[" + finEntity + "]]></fin_entity>");
			if ( taxDate != null )
			{
				xmlBuff.append("<tax_date><![CDATA[" + sdf.format(taxDate) + "]]></tax_date>");
			}
			xmlBuff.append("<vouch_type><![CDATA[P]]></vouch_type>");
			xmlBuff.append("<anal_code><![CDATA[" + (analCode==null?"":analCode) + "]]></anal_code>");
			xmlBuff.append("<proj_code><![CDATA[" + (projCode==null?"":projCode) + "]]></proj_code>");
			xmlBuff.append("<confirmed><![CDATA[N]]></confirmed>");
			xmlBuff.append("<pay_mode><![CDATA[" + (payMode==null?"":payMode) + "]]></pay_mode>");
			xmlBuff.append("<rnd_off><![CDATA[" + (roundOff==null?"":roundOff) + "]]></rnd_off>");
			xmlBuff.append("<rnd_to><![CDATA[" + roundTo + "]]></rnd_to>");
			xmlBuff.append("<tran_mode><![CDATA[A]]></tran_mode>");
			xmlBuff.append("<acct_code__cf><![CDATA[" + (acctCodeCFAP==null?"":acctCodeCFAP) + "]]></acct_code__cf>");
			xmlBuff.append("<cctr_code__cf><![CDATA[" + (cctrCodeCFAP==null?"":cctrCodeCFAP) + "]]></cctr_code__cf>");
			xmlBuff.append("<tran_id__rcp><![CDATA[" + tranId + "]]></tran_id__rcp>");
			xmlBuff.append("<bank_code__pay><![CDATA[" + (bankCodePay==null?"":bankCodePay) + "]]></bank_code__pay>");

			xmlBuff.append("<bill_amt><![CDATA[" + amount + "]]></bill_amt>");
			xmlBuff.append("<tax_amt><![CDATA[" + taxAmt + "]]></tax_amt>");
			xmlBuff.append("<tot_amt><![CDATA[" + totAmount + "]]></tot_amt>");
			xmlBuff.append("<net_amt><![CDATA[" + netAmount + "]]></net_amt>");
			xmlBuff.append("<net_amt__bc><![CDATA[" + netAmountBase + "]]></net_amt__bc>");
			xmlBuff.append("<diff_amt__exch><![CDATA[0]]></diff_amt__exch>");
			xmlBuff.append("<supp_bill_amt><![CDATA[" + suppBillAmount + "]]></supp_bill_amt>");

			xmlBuff.append("<chg_user><![CDATA[" + (userId==null?"BASE":userId)  + "]]></chg_user>");
			xmlBuff.append("<chg_term><![CDATA[" +  (termId==null?"BASE":termId) + "]]></chg_term>");
			sdf = new java.text.SimpleDateFormat(genericUtility.getApplDateTimeFormat());
			xmlBuff.append("<chg_date><![CDATA[" + sdf.format(today)  + "]]></chg_date>");

			xmlBuff.append("</Detail1>");

			sql = "SELECT LINE_NO,PURC_ORDER,LINE_NO__ORD,ITEM_CODE,QUANTITY,UNIT,RATE, "
				+ "(CASE WHEN DISCOUNT IS NULL THEN 0 ELSE DISCOUNT END ) AS DISCOUNT, " // CONVERTED NVL(DISCOUNT,0) TO CASE BY ARCHIMAN 09.08.08
				+ " TAX_AMT,NET_AMT,QUANTITY__STDUOM,RATE__STDUOM, "
				+ " ACCT_CODE__DR,CCTR_CODE__DR,DEPT_CODE,ACCT_CODE__CR,CCTR_CODE__CR, " //DEPT CODE ADDED FOR BUDGET CALC. SANDIP 23/07/05
				+ " ACCT_CODE__PROV_DR,CCTR_CODE__PROV_DR,ACCT_CODE__PROV_CR,CCTR_CODE__PROV_CR "
				+ " FROM  PORCPDET  WHERE TRAN_ID = ? ";
			// In below fetch-ls_acct_pdr,ls_cctr_pdr,ls_acct_pcr,ls_cctr_pcr-Added By fatema - 04/04/2007 - DI7SUN0018
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranId);
			rs = pstmt.executeQuery();
			ctr = 0;
			while(rs.next())
			{
				rcpLine = rs.getString("LINE_NO");
				purcOrderDet = rs.getString("PURC_ORDER");
				lineNoOrd = rs.getString("LINE_NO__ORD");
				rcpItemCode = rs.getString("ITEM_CODE");
				rcpQuantity = rs.getDouble("QUANTITY");
				rcpUnit = rs.getString("UNIT");
				rcpRate = rs.getDouble("RATE");
				rcpDiscount = rs.getDouble("DISCOUNT");
				rcpTaxAmount = rs.getDouble("TAX_AMT");
				rcpNetAmount = rs.getDouble("NET_AMT");
				rcpQtyStd = rs.getDouble("QUANTITY__STDUOM");
				rcpRateStd = rs.getDouble("RATE__STDUOM");
				acctCodeDR = rs.getString("ACCT_CODE__DR");
				cctrCodeDR = rs.getString("CCTR_CODE__DR");
				deptCode = rs.getString("DEPT_CODE");
				acctCodeCR = rs.getString("ACCT_CODE__CR");
				cctrCodeCR = rs.getString("CCTR_CODE__CR");
				acctCodeProvDR = rs.getString("ACCT_CODE__PROV_DR");
				cctrCodeProvDR = rs.getString("CCTR_CODE__PROV_DR");
				acctCodeProvCR = rs.getString("ACCT_CODE__PROV_CR");
				cctrCodeProvCR = rs.getString("CCTR_CODE__PROV_CR");

				// amit - 27/08/02
				rateDiscount = getPurcRate(purcOrderDet, rcpItemCode,purcRate,purcDisc, conn);
				purcRate = rateDiscount[0];
				purcDisc = rateDiscount[1];
				if (purcRate != -999)
				{
					purcRateStd = rcpRateStd * purcRate / rcpRate; 
					rcpRate = purcRate;
					rcpRateStd = purcRateStd;
					rcpDiscount = purcDisc;
				}
				// end - amit - 27/08/02
				
				// added by shiri 20/12/01 as tax should not contain the non_payable = 'Y'
				//Converted nvl to case when by Archiman
				//Commented select nvl(sum(nvl(a.tax_amt,0)),0) into :rcpTaxAmount by Archiman 12/08/08
				//Converted nvl(b.non_payable, 'N') to case when statement by Archiman 12/08/08
				//and nvl(b.non_payable, 'N') <> 'Y' 
				sql = "select sum(case when a.tax_amt is null then 0 else a.tax_amt end) as tax_amt "
					+ " from taxtran a, tax b "
					+ " where a.tax_code = b.tax_code "
					+ " and a.tran_code = ? "
					+ " and a.tran_id = ? "
					+ " and a.line_no = ? "
					+ " and (case when b.non_payable is null then 'N' else b.non_payable end) <> 'Y' "
					+ " and b.effect <> 'N'";
				pstmt1 = conn.prepareStatement(sql);
				pstmt1.setString(1, tranSer);
				pstmt1.setString(2, tranId);
				pstmt1.setString(3, rcpLine);
				rs1 = pstmt1.executeQuery();
				if(rs1.next())
				{
					rcpTaxAmount = rs1.getDouble("tax_amt");
				}
				rs1.close();
				rs1 = null;
				pstmt1.close();
				pstmt1 = null;

				//******************taxVar1 = rcpTaxAmount;	//amit-13/08/02
				// to get the tax with non_payable = 'Y' as to subtract from the net amt
				//Converted nvl to case by Archiman 12/08/08
				// Commented select nvl(sum(nvl(a.tax_amt,0)),0) into :rcpTaxNP by Archiman
				//Converted nvl(b.non_payable, 'N') to case when statement by Archiman 12/08/08
				//and nvl(b.non_payable, 'N') = 'Y'; 
				sql = "select sum(case when a.tax_amt is null then 0 else a.tax_amt end) as tax_amt "
					+ " from taxtran a, tax b "
					+ " where a.tax_code = b.tax_code "
					+ " and a.tran_code = ? "
					+ " and a.tran_id = ? "
					+ " and a.line_no = ? "
					+ " and (case when b.non_payable is null then 'N' else b.non_payable end) = 'Y' ";
				pstmt1 = conn.prepareStatement(sql);
				pstmt1.setString(1, tranSer);
				pstmt1.setString(2, tranId);
				pstmt1.setString(3, rcpLine);
				rs1 = pstmt1.executeQuery();
				if(rs1.next())
				{
					rcpTaxNP = rs1.getDouble("tax_amt");
				}
				rs1.close();
				rs1 = null;
				pstmt1.close();
				pstmt1 = null;
				rcpNetAmount = rcpNetAmount - rcpTaxNP;
				
				// end shiri
						
				//		if invOnLine = 'Y' then
				//			ls_acct_ph = acctCodePur
				//			// below commented by arif on 21-OCT-2002 as liability a/c cost center code
				//			// should be same as in purchase receipt.
				//			// below comment removed by shiri 07/10/03 as cctr code is becoming blank
				//			// as it is not defined in item acct detr
				//			if (isnull(ls_cctr_ph) or len(trim(ls_cctr_ph)) = 0) and not isnull(cctrCodePur) and len(trim(cctrCodePur)) > 0 then
				//				ls_cctr_ph = cctrCodePur
				//			end if	
				//		end if

				if ("Y".equals(invOnLine))
				{
					// Added by fatema - 04/04/2007 - DI7SUN0018
					if ("Y".equals(postProv))
					{
						acctCodePH = acctCodeProvCR;
						ccrtCodePH = cctrCodeProvCR;
					}
					else // Ended by fatema - 04/04/2007 - DI7SUN0018
					{
						acctCodePH = acctCodeCR;
						ccrtCodePH = cctrCodeCR;
					} // Added by fatema - 04/04/2007 - DI7SUN0018
				}
				else
				{
					acctCodePH = acctCodeDR;
					ccrtCodePH = cctrCodeDR;
				}
				/// changes done  by sachin 22-11-02 checking for the taking tax detail as per variable defined .
				
				taxVar = distCommon.getDisparams("999999","TA_VOUCH",conn);
				//jyoti
				//the tax settings for purchase receipt/purchase return should be based on  
				if (!"NULLFOUND".equals(taxVar)) 
				{
					if (taxVar.indexOf("P-RCP") > -1) 
					{
						taxVar2 = "P-RCP";
					}
					if (taxVar.indexOf("P-RET") > -1) 
					{
						taxVar1 = "P-RET";
					}
				}
			
				if (("P-RCP".equals(taxVar2) && "P-RCP".equals(tranSer)) || ("P-RET".equals(taxVar1) && "P-RET".equals(tranSer)) )//or taxVar1 = 'P-RET' then 
				{
					sql = "SELECT TAX_CLASS,TAX_CHAP,TAX_ENV, RATE__CLG, STD_RATE "
						+ " FROM PORCPDET  "
						+ " WHERE TRAN_ID = ? "
						+ " AND LINE_NO = ? ";
						
					pstmt1 = conn.prepareStatement(sql);
					pstmt1.setString(1, tranId);
					pstmt1.setString(2, rcpLine);
					rs1 = pstmt1.executeQuery();
					if(rs1.next())
					{

						taxClass = rs1.getString("TAX_CLASS");
						taxChap = rs1.getString("TAX_CHAP");
						taxEnv = rs1.getString("TAX_ENV");
						rateClg = rs1.getDouble("RATE__CLG");
						stdRate = rs1.getDouble("STD_RATE");
					}
					rs1.close();
					rs1 = null;
					pstmt1.close();
					pstmt1 = null;
					
					//Added by Shahid 21/11/2007 FI78BAS014
					sql = "SELECT PROJ_CODE "
						+ " FROM PORDDET  "
						+ " WHERE PURC_ORDER = ? "
						+ " AND LINE_NO = ? ";
					pstmt1 = conn.prepareStatement(sql);
					pstmt1.setString(1, purcOrderDet);
					pstmt1.setString(2, lineNoOrd);
					rs1 = pstmt1.executeQuery();
					if(rs1.next())
					{

						projCode  = rs1.getString("PROJ_CODE");
					}
					rs1.close();
					rs1 = null;
					pstmt1.close();
					//Ended by Shahid 21/11/20007 FI78BAS014
				}
				else
				{
					sql = "SELECT TAX_CLASS,TAX_CHAP,TAX_ENV, RATE__CLG, STD_RATE,PROJ_CODE " //Proj_code Added by Shahid 21/11/2007 FI78BAS014
						+ " FROM PORDDET "
						+ " WHERE PURC_ORDER = ? "
						+ " AND LINE_NO = ? ";
						
					pstmt1 = conn.prepareStatement(sql);
					pstmt1.setString(1, purcOrderDet);
					pstmt1.setString(2, lineNoOrd);
					rs1 = pstmt1.executeQuery();
					if(rs1.next())
					{
						
						taxClass = rs1.getString("TAX_CLASS");
						taxChap = rs1.getString("TAX_CHAP");
						taxEnv = rs1.getString("TAX_ENV");
						rateClg = rs1.getDouble("RATE__CLG");
						stdRate = rs1.getDouble("STD_RATE");
						projCode  = rs1.getString("PROJ_CODE");
					}
					rs1.close();
					rs1 = null;
					pstmt1.close();
					pstmt1 = null;
				}

				rcpDiscAmt = (rcpQtyStd * rcpRateStd * rcpDiscount)/100;
				rcpAmount = ((rcpQtyStd * rcpRateStd) - rcpDiscAmt);
				vouchQuantity = rcpQuantity;
				vouchAmount   = rcpQtyStd * rcpRateStd ;
				taxMultiplier = 1;
				if ("P-RET".equals(tranSer))
				{
					rcpDiscAmt = -1 * rcpDiscAmt ;
				rcpTaxAmount = -1 * rcpTaxAmount;
				rcpNetAmount = -1 * rcpNetAmount;
				vouchQuantity = -1 * vouchQuantity;
				vouchAmount = -1 * vouchAmount;
				taxMultiplier = -1;
				rcpQtyStd = -1 * rcpQtyStd;
				// 07-06-04 sign of amount to be changed
				rcpAmount = -1 * rcpAmount;
					// end 07-06-04
				}
				
			
				/// amish 19-03-04 // if posting is standard then paste acct code and cctr code
				/// from item acct detr. PPV A/C
				if ( "S".equals(postType))
				{
					sql = "SELECT ITEM_SER FROM ITEM WHERE ITEM_CODE = ? ";
					pstmt1 = conn.prepareStatement(sql);
					pstmt1.setString(1, rcpItemCode);
					rs1 = pstmt1.executeQuery();
					if(rs1.next())
					{
						
						itemSer = rs1.getString("ITEM_SER");
					}
					rs1.close();
					rs1 = null;
					pstmt1.close();
					pstmt1 = null;
					cctrCodePPV = finCommon.getAcctDetrTtype(rcpItemCode, itemSer, "PPV", pordType, conn);
					if (cctrCodePPV != null && cctrCodePPV.trim().length() > 0)
					{
						acctCodePPV = cctrCodePPV.substring(0,cctrCodePPV.indexOf(","));
						cctrCodePPV = cctrCodePPV.substring(cctrCodePPV.indexOf(",")+1);
					}
				}
				/*// 08/this part is not possible in RCP so commented	
				// changes done by sachin 22-11-02 to pick up minimun line no istead of 1 
					Select min(line_no) into :lineCount_pick from porcpdet where tran_id = :as_tran_id;
				// 1 Added by Vilas as on 14-03-02
				//if trim(rcpLine) <> '1' then
				if trim(rcpLine) <>  trim(lineCount_pick) or tranSer <> 'P-RCP' then
					li_ln = lds_vouchdet.insertrow(0)
					lds_vouchdet.setitem(li_ln,'tran_id',ls_vouch_id)
					lds_vouchdet.setitem(li_ln,'line_no',lineCount)
					lds_vouchdet.setitem(li_ln,'item_code',rcpItemCode )
					lds_vouchdet.setitem(li_ln,'unit',rcpUnit)
					lds_vouchdet.setitem(li_ln,'vouch_qty',vouchQuantity)
					//lds_vouchdet.setitem(li_ln,'quantity',vouchQuantity)//Commented by Shahid 28-06-2007
					lds_vouchdet.setitem(li_ln,'rcp_qty',vouchQuantity)//Added by Shahid 28-06-2007
					lds_vouchdet.setitem(li_ln,'vouch_rate',rcpRate )
					lds_vouchdet.setitem(li_ln,'vouch_amt',vouchAmount)
					lds_vouchdet.setitem(li_ln,'tax_class', taxClass )
					lds_vouchdet.setitem(li_ln,'tax_chap',taxChap )
					lds_vouchdet.setitem(li_ln,'tax_env',taxEnv )
					lds_vouchdet.setitem(li_ln,'discount',rcpDiscount)		// amit - 26/08/02
					lds_vouchdet.setitem(li_ln,'rate__clg',rateClg)//jyoti 23/2/03
					//			lds_vouchdet.setitem(li_ln,'std_rate',stdRate)//rajesh nair 15/03/04			
					//amit-19-06-02
					lds_vouchdet.setitem(li_ln,'purc_order',purcOrderDet)
					lds_vouchdet.setitem(li_ln,'line_no__pord',lineNoOrd)
					
					lds_vouchdet.setitem(li_ln,'prcp_id',as_tran_id)
					lds_vouchdet.setitem(li_ln,'line_no__rcp',rcpLine)
					
					lds_vouchdet.setitem(li_ln,'acct_code__diff',acctCodePPV)
					lds_vouchdet.setitem(li_ln,'cctr_code__diff',cctrCodePPV)
					lds_vouchdet.setitem(li_ln,'dept_code',deptCode) //dept code added for budget calc. sandip 23/07/05
						
					
					// end - amit-19-06-02

					lds_vouchdet.setrow(li_ln)
					// 20-08-04 manoharan like in window instead of vouch_rate, vouch_qty
					// columns vouch_rate__stduom, vouch_qty__stduom columns passed
					rcpTaxAmount  = gf_calc_tax_ds(lds_vouchdet, lds_taxdet,'VOUCH',ls_vouch_id,tranDate,"vouch_rate","vouch_qty" ,0, currCode)
					//committed and modified by prince james on 28-09-04
					//ls_errcode = gf_calc_tax_ds(lds_vouchdet, lds_taxdet,'VOUCH',ls_vouch_id,tranDate,"vouch_rate","vouch_qty" ,0, currCode)
					//rcpTaxAmount = dec(f_get_token(ls_errcode,'~t'))
					
					//ended by prince james on 28-09-04

					lds_vouchdet.setitem(li_ln,'vouch_rate__stduom',rcpRateStd )
					lds_vouchdet.setitem(li_ln,'vouch_qty__stduom',rcpQtyStd)
					//committed and modified by prince james on 28-09-04
					
					rcpTaxAmount  = gf_calc_tax_ds(lds_vouchdet, lds_taxdet,'VOUCH',ls_vouch_id,tranDate,"vouch_rate__stduom","vouch_qty__stduom" ,0, currCode)
					//ls_errcode = gf_calc_tax_ds(lds_vouchdet, lds_taxdet,'VOUCH',ls_vouch_id,tranDate,"vouch_rate__stduom","vouch_qty__stduom" ,0, currCode)
					//rcpTaxAmount = dec(f_get_token(ls_errcode,'~t'))

					//ended by prince james on 28-09-04
					// end 20-08-04 manoharan


					if rcpTaxAmount = -999999999 then
						ls_errcode = "Error calculating tax amount"
						exit
					else
						if lds_taxdet.update() <> 1 then
							ls_errcode = "Error calculating tax amount"
							exit
						end if
					end if
				else
					rcpTaxAmount = 0; 
				end if
				if isnull(rcpTaxAmount) then rcpTaxAmount = 0
				//rcpNetAmount = rcpNetAmount + rcpTaxAmount - taxVar1					//amit-13/08/02
				rcpNetAmount = rcpAmount + rcpTaxAmount
				*/
				//if (cctrCodePPV == null || setnull(cctrCodePPV)//added by zahid 10/06/04
				//if len(trim(acctCodePPV)) = 0 then setnull(acctCodePPV)//added by zahid 10/06/04
				// 1 Added by Vilas as on 14-03-02
				System.out.println("bEFORE******rcpDiscAmt***"+rcpDiscAmt+"    rcpTaxAmount***"+rcpTaxAmount+"    rcpNetAmount***"+rcpNetAmount+ "  vouchQuantity***" + "  vouchAmount***"+vouchAmount+"  rcpQtyStd***"+rcpQtyStd+"  rcpAmount***"+rcpAmount);

				ctr++;

				xmlBuff.append("<Detail3 dbID='' domID=\"1\" objName=\"voucher\" objContext=\"2\">");
				xmlBuff.append("<attribute pkNames=\"\" selected=\"N\" updateFlag=\"A\" status=\"N\" />");
				xmlBuff.append("<tran_id/>");
				xmlBuff.append("<line_no>"+(ctr)+"</line_no>");
				xmlBuff.append("<prcp_id><![CDATA[" + (tranId==null?"":tranId) + "]]></prcp_id>");
				xmlBuff.append("<line_no__rcp><![CDATA[" + (rcpLine==null?"":rcpLine) + "]]></line_no__rcp>");
				xmlBuff.append("<item_code><![CDATA[" + (rcpItemCode==null?"":rcpItemCode) + "]]></item_code>");
				xmlBuff.append("<unit><![CDATA[" + (rcpUnit==null?"":rcpUnit) + "]]></unit>");
				xmlBuff.append("<rcp_qty><![CDATA[" + rcpQuantity + "]]></rcp_qty>");
				xmlBuff.append("<vouch_qty><![CDATA[" + vouchQuantity + "]]></vouch_qty>");
				xmlBuff.append("<rcp_rate><![CDATA[" + rcpRate + "]]></rcp_rate>");
				xmlBuff.append("<vouch_rate><![CDATA[" + rcpRate + "]]></vouch_rate>");
				xmlBuff.append("<purc_order><![CDATA[" + (purcOrderDet==null?"":purcOrderDet) + "]]></purc_order>");
				xmlBuff.append("<line_no__pord><![CDATA[" + (lineNoOrd==null?"":lineNoOrd) + "]]></line_no__pord>");
				xmlBuff.append("<vouch_amt><![CDATA[" + vouchAmount + "]]></vouch_amt>");
				xmlBuff.append("<rcp_amt><![CDATA[" + rcpAmount + "]]></rcp_amt>");
				xmlBuff.append("<tax_class><![CDATA[" + (taxClass==null?"":taxClass) + "]]></tax_class>");
				xmlBuff.append("<tax_chap><![CDATA[" + (taxChap==null?"":taxChap) + "]]></tax_chap>");
				xmlBuff.append("<tax_env><![CDATA[" + (taxEnv==null?"":taxEnv) + "]]></tax_env>");
				xmlBuff.append("<discount><![CDATA[" + rcpDiscount + "]]></discount>");
				xmlBuff.append("<disc_amt><![CDATA[" + rcpDiscAmt + "]]></disc_amt>");
				xmlBuff.append("<tax_amt><![CDATA[" + rcpTaxAmount + "]]></tax_amt>");
				xmlBuff.append("<net_amt><![CDATA[" + rcpNetAmount + "]]></net_amt>");
				xmlBuff.append("<post_diff><![CDATA[I]]></post_diff>");
				sdf = new java.text.SimpleDateFormat(genericUtility.getDBDateTimeFormat());
				xmlBuff.append("<chg_date><![CDATA[" + sdf.format(today) + "]]></chg_date>");
				xmlBuff.append("<chg_user><![CDATA[" + (userId==null?"":userId) + "]]></chg_user>");
				xmlBuff.append("<chg_term><![CDATA[" + (termId==null?"BASE":termId) + "]]></chg_term>");
				xmlBuff.append("<item_ser><![CDATA[" + (itemSer==null?"":itemSer) + "]]></item_ser>");
				xmlBuff.append("<vouch_qty__stduom><![CDATA[" + rcpQtyStd + "]]></vouch_qty__stduom>");
				xmlBuff.append("<vouch_rate__stduom><![CDATA[" + rcpRateStd + "]]></vouch_rate__stduom>");
				xmlBuff.append("<acct_code><![CDATA[" + (acctCodePH==null?"":acctCodePH) + "]]></acct_code>");
				xmlBuff.append("<cctr_code><![CDATA[" + (ccrtCodePH==null?"":ccrtCodePH) + "]]></cctr_code>");
				xmlBuff.append("<rate__clg><![CDATA[" + rateClg + "]]></rate__clg>");
				xmlBuff.append("<std_rate><![CDATA[" + stdRate + "]]></std_rate>");
				xmlBuff.append("<acct_code__diff><![CDATA[" + (acctCodePPV==null?"":acctCodePPV) + "]]></acct_code__diff>");
				xmlBuff.append("<cctr_code__diff><![CDATA[" + (cctrCodePPV==null?"":cctrCodePPV) + "]]></cctr_code__diff>");
				xmlBuff.append("<dept_code><![CDATA[" + (deptCode==null?"":deptCode) + "]]></dept_code>");
				xmlBuff.append("<anal_code><![CDATA[" + (analCode==null?"":analCode) + "]]></anal_code>");
				xmlBuff.append("<proj_code><![CDATA[" + (projCode==null?"":projCode) + "]]></proj_code>");
				xmlBuff.append("</Detail3>");

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

			if (errString != null && errString.trim().length() > 0)
			{
				return errString;
			}

			if ("P-RCP".equals(tranSer.trim()))
			{
				//Updating voucher detail for return
				//		  select porcpdet.tran_id,line_no,porcpdet.purc_order,line_no__ord,item_code,quantity,   
				//  	   	   unit,rate,nvl(porcpdet.discount,0),porcpdet.tax_amt,net_amt,quantity__stduom,rate__stduom,
				//				acct_code__dr,cctr_code__dr,ret_opt
				//   	  from  porcp, porcpdet  
				//		  where porcp.tran_id = porcpdet.tran_id and 
				//		  tran_id__ref = :as_tran_id and porcp.tran_ser = 'P-RET' and 
				//		  confirmed = 'Y' and (vouch_created = 'N' or vouch_created is null) ;

				// shiri 07/10/03 changed acct_code__dr and cctr_code__dr to acct_code__cr and cctr_code__cr
				// as the wrong a/c ie inventory account was getting affected instead of the po receipt a/c
				// In below select-acct_code__prov_dr,cctr_code__prov_dr,acct_code__prov_cr,cctr_code__prov_cr-Added By fatema - 04/04/2007 - DI7SUN0018
				//Converted nvl to case when by Archiman 12/08/08
				//Commented nvl(porcpdet.discount,0) by Archiman 12/08/08
				sql = "select porcpdet.tran_id,line_no,porcpdet.purc_order,line_no__ord,item_code,quantity, "
					+ " unit,rate,(case when porcpdet.discount is null then 0 else porcpdet.discount end) as discount, "
					+ " porcpdet.tax_amt,net_amt,quantity__stduom,rate__stduom, "
					+ "	acct_code__dr,cctr_code__dr,ret_opt,dept_code, acct_code__cr,cctr_code__cr,	"
					+ " acct_code__prov_dr,cctr_code__prov_dr,acct_code__prov_cr,cctr_code__prov_cr "
				    + " from  porcp, porcpdet "
				    + " where porcp.tran_id = porcpdet.tran_id "
					+ " and tran_id__ref = ?  " 
					+ " and porcp.tran_ser = 'P-RET' "
					+ " and confirmed = 'Y' and (vouch_created = 'N' or vouch_created is null)" ;

					// In below fetch-acctCodeProvDR,cctrCodeProvDR,acctCodeProvCR,cctrCodeProvCR-Added By fatema - 04/04/2007 - DI7SUN0018
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, tranId);
				rs = pstmt.executeQuery();
				while(rs.next())
				{

					returnId = rs.getString("TRAN_ID");
					rcpLine = rs.getString("LINE_NO");
					purcOrderDet = rs.getString("PURC_ORDER");
					lineNoOrd = rs.getString("LINE_NO__ORD");
					rcpItemCode = rs.getString("ITEM_CODE");
					rcpQuantity = rs.getDouble("QUANTITY");
					rcpUnit = rs.getString("UNIT");
					rcpRate = rs.getDouble("RATE");
					rcpDiscount = rs.getDouble("DISCOUNT");
					rcpTaxAmount = rs.getDouble("TAX_AMT");
					rcpNetAmount = rs.getDouble("NET_AMT");
					rcpQtyStd = rs.getDouble("QUANTITY__STDUOM");
					rcpRateStd = rs.getDouble("RATE__STDUOM");
					acctCodeDR = rs.getString("acct_code__dr");
					cctrCodeDR = rs.getString("cctr_code__dr");
					deptCode = rs.getString("dept_code");
					acctCodeCR = rs.getString("acct_code__cr");
					cctrCodeCR = rs.getString("cctr_code__cr");
					acctCodeProvDR = rs.getString("acct_code__prov_dr");
					cctrCodeProvDR = rs.getString("cctr_code__prov_dr");
					acctCodeProvCR = rs.getString("acct_code__prov_cr");
					cctrCodeProvCR = rs.getString("cctr_code__prov_cr");

					if ("R".equals(retOpt))
					{
						continue;
					}
						
					//amish 06-08-05 if inv online then 
					if ("Y".equals(invOnLine))
					{
						// Added by fatema - 04/04/2007 - DI7SUN0018
						if ("Y".equals(postProv))
						{
							acctCodePH = acctCodeProvCR;
							ccrtCodePH = cctrCodeProvCR;
						}
						else // Ended by fatema - 04/04/2007 - DI7SUN0018
						{
							acctCodePH = acctCodeCR;
							ccrtCodePH = cctrCodeCR;
						} // Added by fatema - 04/04/2007 - DI7SUN0018
					}
					else
					{
						acctCodePH = acctCodeDR;
						ccrtCodePH = cctrCodeDR;
					}
						
					// amit - 27/08/02
					rateDiscount = getPurcRate(purcOrderDet,rcpItemCode,purcRate,purcDisc, conn);
					purcRate = rateDiscount[0];
					purcDisc = rateDiscount[1];
					if (purcRate != -999)
					{
						purcRateStd = rcpRateStd * purcRate / rcpRate; 
						rcpRate = purcRate;
						rcpRateStd = purcRateStd;
						rcpDiscount = purcDisc;
					}
					/* // manoharan this part is not required in RCP
					// end - amit - 27/08/02
					// added by shiri 20/12/01 as tax should not contain the non_payable = 'Y'
					//Converted nvl to case by Archiman 12/08/08
					//Commented select nvl(sum(nvl(a.tax_amt,0)),0) into :rcpTaxAmount by Archiman 13/08/08  
					sql = "select sum(case when a.tax_amt is null then 0 else a.tax_amt end) as rcpTaxAmount " //into :rcpTaxAmount
						+ " from taxtran a, tax b "
					+ " where a.tax_code = b.tax_code "
					+ " and a.tran_code = 'P-RET' "
					+ " and a.tran_id = ? " 
					+ " and a.line_no = ? "
					+ " and (case when b.non_payable is null then 'N' else b.non_payable end) <>'Y' "
					+ " and b.effect <> 'N' ";
					// start 08/10/11
					pstmt1 = conn.prepareStatement(sql);
					pstmt1.setString(1, returnId);
					pstmt1.setString(2, rcpLine);
					rs1 = pstmt1.executeQuery();
					if(rs1.next())
					{
						
						rcpTaxAmount = rs1.getDouble("rcpTaxAmount");
					}
					rs1.close();
					rs1 = null;
					pstmt1.close();
					pstmt1 = null;

					lc_var3 = rcpTaxAmount					// amit-13/08/02
					// to get the tax with non_payable = 'Y' as to subtract from the net amt
					//Converted nvl to case by Archiman 12/08/08
					//Commented select nvl(sum(nvl(a.tax_amt,0)),0) into :rcpTaxNP by Archiman 12/08/08
					//Commented select nvl(sum(nvl(a.tax_amt,0)),0) into :rcpTaxNP by Archiman 12/08/08
					select sum(case when a.tax_amt is null then 0 else a.tax_amt end) into :rcpTaxNP
					from taxtran a, tax b
					where a.tax_code = b.tax_code
					and a.tran_code = 'P-RET' 
					and a.tran_id = :returnId
					and a.line_no = :rcpLine
					and (case when b.non_payable is null then 'N' else b.non_payable end) ='Y';

					if get_sqlcode() <> 0 then 
							ls_errcode = 'DS000' + string(sqlca.sqldbcode) + '~t' + sqlca.sqlerrtext
							exit
					end if 
					rcpNetAmount = rcpNetAmount - rcpTaxNP
					ctr++;
					/// changes done  by sachin 22-11-02 checking for the taking tax detail as per variable defined .
					///jyoti 27/1/04
					if taxVar1 = 'P-RET' then
						select tax_class,tax_chap,tax_env, rate__clg
						into :taxClass,:taxChap,:taxEnv, :lc_rate__clg
						from porcpdet
						where tran_id = :returnId and line_no = :rcpLine;
					//			else
					//				select tax_class,tax_chap,tax_env
					//				into :taxClass,:taxChap,:taxEnv
					//				from porddet
					//				where purc_order = :purcOrderDet and line_no = :lineNoOrd;
					end if

					//			rcpDiscAmt = 0 - (rcpQtyStd * rcpRateStd * rcpDiscount)/100
					//			rcpTaxAmount    = 0 - rcpTaxAmount
					//		   rcpAmount = ((rcpQtyStd * rcpRateStd) - rcpDiscAmt)
					//			vouchAmount = 0 - (rcpQtyStd * rcpRateStd) 
					//			vouchQuantity = 0 - rcpQuantity
					//			rcpNetAmount 			= 0 - rcpNetAmount
					//	   	totAmount = totAmount + vouchAmount + rcpTaxAmount + rcpDiscAmt
					//		   netAmount = totAmount
					//		   netAmountBase = totAmount * exchRate
					//			taxAmt     = taxAmt + rcpTaxAmount
					//			rcpQtyStd = 0 - rcpQtyStd

					//// amish 04-08-05 
					rcpQtyStd = 0 - rcpQtyStd
					rcpDiscAmt =  (rcpQtyStd * rcpRateStd * rcpDiscount)/100
					rcpTaxAmount    = 0 - rcpTaxAmount
					rcpAmount = ((rcpQtyStd * rcpRateStd) - rcpDiscAmt)
					vouchAmount =  (rcpQtyStd * rcpRateStd) 
					vouchQuantity = 0 - rcpQuantity
					rcpNetAmount 			= 0 - rcpNetAmount
					totAmount = totAmount + vouchAmount + rcpTaxAmount + rcpDiscAmt
					netAmount = totAmount
					netAmountBase = totAmount * exchRate
					taxAmt     = taxAmt + rcpTaxAmount
					
					
					/// commented by amish 04-08-05

					// 2 Added by Vilas as on 14-03-02
					//jyoti 25/1/04
					//if trim(rcpLine) <> '1' then
						li_ln = lds_vouchdet.insertrow(0)
						lds_vouchdet.setitem(li_ln,'tran_id',ls_vouch_id)
						lds_vouchdet.setitem(li_ln,'line_no',lineCount)
						lds_vouchdet.setitem(li_ln,'item_code',rcpItemCode )
						lds_vouchdet.setitem(li_ln,'unit',rcpUnit)
						lds_vouchdet.setitem(li_ln,'vouch_qty',vouchQuantity)
						//lds_vouchdet.setitem(li_ln,'quantity',vouchQuantity)//Commented by Shahid--28-06-2007
						lds_vouchdet.setitem(li_ln,'rcp_qty',vouchQuantity)//Added by Shahid -28-06-2007
						lds_vouchdet.setitem(li_ln,'vouch_rate',rcpRate )
						lds_vouchdet.setitem(li_ln,'vouch_amt',vouchAmount)
						lds_vouchdet.setitem(li_ln,'tax_class', taxClass )
						lds_vouchdet.setitem(li_ln,'tax_chap',taxChap )
						lds_vouchdet.setitem(li_ln,'tax_env',taxEnv )
						lds_vouchdet.setitem(li_ln,'discount',rcpDiscount)
						lds_vouchdet.setitem(li_ln,'rate__clg',lc_rate__clg)	
						lds_vouchdet.setitem(li_ln,'dept_code',deptCode) //dept code added for budget calc. sandip 23/07/05
						
						// amit - 26/08/02
						//jyoti 27/1/04
							
						if taxVar1 = 'P-RET' then
							lds_vouchdet.setitem(li_ln,'prcp_id',returnId)
							lds_vouchdet.setitem(li_ln,'line_no__rcp',rcpLine)
						end if

						///////
						lds_vouchdet.setrow(li_ln)
						// 20-08-04 manoharan like in window instead of vouch_rate, vouch_qty
						// columns vouch_rate__stduom, vouch_qty__stduom columns passed
						/// commented by amish 04-08-05
						//rcpTaxAmount  = gf_calc_tax_ds(lds_vouchdet, lds_taxdet,'VOUCH',ls_vouch_id,tranDate,"vouch_rate","vouch_qty" ,0, currCode) 

					//committed and modified by prince james on 28-09-04
						//ls_errcode =  gf_calc_tax_ds(lds_vouchdet, lds_taxdet,'VOUCH',ls_vouch_id,tranDate,"vouch_rate","vouch_qty" ,0, currCode)
						//rcpTaxAmount = dec(f_get_token(ls_errcode,'~t'))

					//ended by prince james on 28-09-04	
						lds_vouchdet.setitem(li_ln,'vouch_rate__stduom',rcpRateStd )
						lds_vouchdet.setitem(li_ln,'vouch_qty__stduom',rcpQtyStd)

							
						rcpTaxAmount  = gf_calc_tax_ds(lds_vouchdet, lds_taxdet,'VOUCH',ls_vouch_id,tranDate,"vouch_rate__stduom","vouch_qty__stduom" ,0, currCode)

					 //committed and modified by prince james on 28-09-04
					 //ls_errcode = gf_calc_tax_ds(lds_vouchdet, lds_taxdet,'VOUCH',ls_vouch_id,tranDate,"vouch_rate__stduom","vouch_qty__stduom" ,0, currCode)
						 //taxAmt = dec(f_get_token(ls_errcode,'~t'))

					 //ended by prince james on 28-09-04 	 
						// end 20-08-04 manoharan
						if rcpTaxAmount = -999999999 then
							ls_errcode = "Error calculating tax amount"
							exit
						else
							if lds_taxdet.update() <> 1 then
								ls_errcode = "Error calculating tax amount"
								exit
							end if	
						end if
					//			else
					//				rcpTaxAmount = 0 
					//			end if
					if isnull(rcpTaxAmount) then rcpTaxAmount = 0
					//			rcpNetAmount = rcpNetAmount + rcpTaxAmount - lc_var3 				// amit-13/08/02
					//			rcpNetAmount = rcpAmount + rcpTaxAmount

					//rcpNetAmount = 0 - (rcpAmount + rcpTaxAmount)     // sachin 21-01-03
					/// amish 04-08-05 
					rcpNetAmount = (rcpAmount + rcpTaxAmount)     // sachin 21-01-03			
					
					// 2 Added by Vilas as on 14-03-02
					//in below sql Anal_code is added by jasmina 24/09/07 -FI78MJB003
					*/ // not required in RCP

					ctr++;
					
					xmlBuff.append("<Detail3 dbID='' domID=\"1\" objName=\"voucher\" objContext=\"2\">");
					xmlBuff.append("<attribute pkNames=\"\" selected=\"N\" updateFlag=\"A\" status=\"N\" />");
					xmlBuff.append("<tran_id/>");
					xmlBuff.append("<line_no>"+(ctr)+"</line_no>");
					xmlBuff.append("<prcp_id><![CDATA[" + (returnId==null?"":returnId) + "]]></prcp_id>");
					xmlBuff.append("<line_no__rcp><![CDATA[" + (rcpLine==null?"":rcpLine) + "]]></line_no__rcp>");
					xmlBuff.append("<item_code><![CDATA[" + (rcpItemCode==null?"":rcpItemCode) + "]]></item_code>");
					xmlBuff.append("<unit><![CDATA[" + (rcpUnit==null?"":rcpUnit) + "]]></unit>");
					xmlBuff.append("<rcp_qty><![CDATA[" + rcpQuantity + "]]></rcp_qty>");
					xmlBuff.append("<vouch_qty><![CDATA[" + vouchQuantity + "]]></vouch_qty>");
					xmlBuff.append("<rcp_rate><![CDATA[" + rcpRate + "]]></rcp_rate>");
					xmlBuff.append("<vouch_rate><![CDATA[" + rcpRate + "]]></vouch_rate>");
					xmlBuff.append("<purc_order><![CDATA[" + (purcOrderDet==null?"":purcOrderDet) + "]]></purc_order>");
					xmlBuff.append("<line_no__pord><![CDATA[" + (lineNoOrd==null?"":lineNoOrd) + "]]></line_no__pord>");
					xmlBuff.append("<vouch_amt><![CDATA[" + vouchAmount + "]]></vouch_amt>");
					xmlBuff.append("<rcp_amt><![CDATA[" + rcpAmount + "]]></rcp_amt>");
					xmlBuff.append("<tax_class><![CDATA[" + (taxClass==null?"":taxClass) + "]]></tax_class>");
					xmlBuff.append("<tax_chap><![CDATA[" + (taxChap==null?"":taxChap) + "]]></tax_chap>");
					xmlBuff.append("<tax_env><![CDATA[" + (taxEnv==null?"":taxEnv) + "]]></tax_env>");
					xmlBuff.append("<discount><![CDATA[" + rcpDiscount + "]]></discount>");
					xmlBuff.append("<disc_amt><![CDATA[" + rcpDiscAmt + "]]></disc_amt>");
					xmlBuff.append("<tax_amt><![CDATA[" + rcpTaxAmount + "]]></tax_amt>");
					xmlBuff.append("<net_amt><![CDATA[" + rcpNetAmount + "]]></net_amt>");
					xmlBuff.append("<post_diff><![CDATA[I]]></post_diff>");
					xmlBuff.append("<chg_date><![CDATA[" + chgDate + "]]></chg_date>");
					xmlBuff.append("<chg_user><![CDATA[" + (userId==null?"BASE":userId) + "]]></chg_user>");
					xmlBuff.append("<chg_term><![CDATA[" + (termId==null?"BASE":termId) + "]]></chg_term>");
					xmlBuff.append("<item_ser><![CDATA[" + (itemSer==null?"":itemSer) + "]]></item_ser>");
					xmlBuff.append("<vouch_qty__stduom><![CDATA[" + rcpQtyStd + "]]></vouch_qty__stduom>");
					xmlBuff.append("<vouch_rate__stduom><![CDATA[" + rcpRateStd + "]]></vouch_rate__stduom>");
					xmlBuff.append("<acct_code><![CDATA[" + (acctCodePH==null?"":acctCodePH) + "]]></acct_code>");
					xmlBuff.append("<cctr_code><![CDATA[" + (ccrtCodePH==null?"":ccrtCodePH) + "]]></cctr_code>");
					xmlBuff.append("<rate__clg><![CDATA[" + rateClg + "]]></rate__clg>");
					xmlBuff.append("<std_rate><![CDATA[" + stdRate + "]]></std_rate>");
					//xmlBuff.append("<acct_code__diff><![CDATA[" + acctCodePPV + "]]></acct_code__diff>");
					//xmlBuff.append("<cctr_code__diff><![CDATA[" + cctrCodePPV + "]]></cctr_code__diff>");
					xmlBuff.append("<dept_code><![CDATA[" + (deptCode==null?"":deptCode) + "]]></dept_code>");
					xmlBuff.append("<anal_code><![CDATA[" + (analCode==null?"":analCode) + "]]></anal_code>");
					//xmlBuff.append("<proj_code><![CDATA[" + projCode + "]]></proj_code>");
					xmlBuff.append("</Detail3>");

				} // return details
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
			} // P-RCP
			
			//**************************iF P-RET**************
			
			if ("P-RET".equals(tranSer.trim()))
			{
				System.out.println("Insiding*****into P-RET");
				
				rcpDiscAmt = -1 * rcpDiscAmt ;
				rcpTaxAmount = -1 * rcpTaxAmount;
				rcpNetAmount = -1 * rcpNetAmount;
				vouchQuantity = -1 * vouchQuantity;
				vouchAmount = -1 * vouchAmount;
				taxMultiplier = -1;
				rcpQtyStd = -1 * rcpQtyStd;
				// 07-06-04 sign of amount to be changed
				rcpAmount = -1 * rcpAmount;
				
				System.out.println("AFTER******pRET rcpDiscAmt***"+rcpDiscAmt+"    rcpTaxAmount***"+rcpTaxAmount+"    rcpNetAmount***"+rcpNetAmount+ "  vouchQuantity***" + "  vouchAmount***"+vouchAmount+"  rcpQtyStd***"+rcpQtyStd+"  rcpAmount***"+rcpAmount);
				
				
				String tranidRef = "";
				
				sql = "select  tran_id__ref from porcp where tran_id = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, tranId);
				rs = pstmt.executeQuery();
				while(rs.next())
				{
					tranidRef = rs.getString("tran_id__ref");
				}
				rs.close();
				rs=null;
				pstmt.close();
				pstmt=null;
				
				
				
				
				sql = "select porcpdet.tran_id,line_no,porcpdet.purc_order,line_no__ord,item_code,quantity, "
					+ " unit,rate,(case when porcpdet.discount is null then 0 else porcpdet.discount end) as discount, "
					+ " porcpdet.tax_amt,net_amt,quantity__stduom,rate__stduom, "
					+ "	acct_code__dr,cctr_code__dr,ret_opt,dept_code, acct_code__cr,cctr_code__cr,	"
					+ " acct_code__prov_dr,cctr_code__prov_dr,acct_code__prov_cr,cctr_code__prov_cr "
				    + " from  porcp, porcpdet "
				    + " where porcp.tran_id = porcpdet.tran_id "
					+ " and porcp.tran_id = '"+tranidRef+"'  " 
					+ " and porcp.tran_ser = 'P-RCP' "
					+ " and confirmed = 'Y' and (vouch_created = 'N' or vouch_created is null)" ;

					// In below fetch-acctCodeProvDR,cctrCodeProvDR,acctCodeProvCR,cctrCodeProvCR-Added By fatema - 04/04/2007 - DI7SUN0018
				pstmt = conn.prepareStatement(sql);
				//pstmt.setString(1, tranId);
				rs = pstmt.executeQuery();
				while(rs.next())
				{

					returnId = rs.getString("TRAN_ID");
					rcpLine = rs.getString("LINE_NO");
					purcOrderDet = rs.getString("PURC_ORDER");
					lineNoOrd = rs.getString("LINE_NO__ORD");
					rcpItemCode = rs.getString("ITEM_CODE");
					rcpQuantity = rs.getDouble("QUANTITY");
					rcpUnit = rs.getString("UNIT");
					rcpRate = rs.getDouble("RATE");
					rcpDiscount = rs.getDouble("DISCOUNT");
					rcpTaxAmount = rs.getDouble("TAX_AMT");
					rcpNetAmount = rs.getDouble("NET_AMT");
					rcpQtyStd = rs.getDouble("QUANTITY__STDUOM");
					rcpRateStd = rs.getDouble("RATE__STDUOM");
					acctCodeDR = rs.getString("acct_code__dr");
					cctrCodeDR = rs.getString("cctr_code__dr");
					deptCode = rs.getString("dept_code");
					acctCodeCR = rs.getString("acct_code__cr");
					cctrCodeCR = rs.getString("cctr_code__cr");
					acctCodeProvDR = rs.getString("acct_code__prov_dr");
					cctrCodeProvDR = rs.getString("cctr_code__prov_dr");
					acctCodeProvCR = rs.getString("acct_code__prov_cr");
					cctrCodeProvCR = rs.getString("cctr_code__prov_cr");

					if ("R".equals(retOpt))
					{
						continue;
					}
						
					//amish 06-08-05 if inv online then 
					if ("Y".equals(invOnLine))
					{
						// Added by fatema - 04/04/2007 - DI7SUN0018
						if ("Y".equals(postProv))
						{
							acctCodePH = acctCodeProvCR;
							ccrtCodePH = cctrCodeProvCR;
						}
						else // Ended by fatema - 04/04/2007 - DI7SUN0018
						{
							acctCodePH = acctCodeCR;
							ccrtCodePH = cctrCodeCR;
						} // Added by fatema - 04/04/2007 - DI7SUN0018
					}
					else
					{
						acctCodePH = acctCodeDR;
						ccrtCodePH = cctrCodeDR;
					}
						
					// amit - 27/08/02
					rateDiscount = getPurcRate(purcOrderDet,rcpItemCode,purcRate,purcDisc, conn);
					purcRate = rateDiscount[0];
					purcDisc = rateDiscount[1];
					if (purcRate != -999)
					{
						purcRateStd = rcpRateStd * purcRate / rcpRate; 
						rcpRate = purcRate;
						rcpRateStd = purcRateStd;
						rcpDiscount = purcDisc;
					}
					

					ctr++;
					
					xmlBuff.append("<Detail3 dbID='' domID=\"1\" objName=\"voucher\" objContext=\"2\">");
					xmlBuff.append("<attribute pkNames=\"\" selected=\"N\" updateFlag=\"A\" status=\"N\" />");
					xmlBuff.append("<tran_id/>");
					xmlBuff.append("<line_no>"+(ctr)+"</line_no>");
					xmlBuff.append("<prcp_id><![CDATA[" + (returnId==null?"":returnId) + "]]></prcp_id>");
					xmlBuff.append("<line_no__rcp><![CDATA[" + (rcpLine==null?"":rcpLine) + "]]></line_no__rcp>");
					xmlBuff.append("<item_code><![CDATA[" + (rcpItemCode==null?"":rcpItemCode) + "]]></item_code>");
					xmlBuff.append("<unit><![CDATA[" + (rcpUnit==null?"":rcpUnit) + "]]></unit>");
					xmlBuff.append("<rcp_qty><![CDATA[" + rcpQuantity + "]]></rcp_qty>");
					xmlBuff.append("<vouch_qty><![CDATA[" + rcpQuantity + "]]></vouch_qty>");
					xmlBuff.append("<rcp_rate><![CDATA[" + rcpRate + "]]></rcp_rate>");
					xmlBuff.append("<vouch_rate><![CDATA[" + rcpRate + "]]></vouch_rate>");
					xmlBuff.append("<purc_order><![CDATA[" + (purcOrderDet==null?"":purcOrderDet) + "]]></purc_order>");
					xmlBuff.append("<line_no__pord><![CDATA[" + (lineNoOrd==null?"":lineNoOrd) + "]]></line_no__pord>");
					xmlBuff.append("<vouch_amt><![CDATA[" + rcpAmount + "]]></vouch_amt>");
					xmlBuff.append("<rcp_amt><![CDATA[" + rcpNetAmount + "]]></rcp_amt>");
					xmlBuff.append("<tax_class><![CDATA[" + (taxClass==null?"":taxClass) + "]]></tax_class>");
					xmlBuff.append("<tax_chap><![CDATA[" + (taxChap==null?"":taxChap) + "]]></tax_chap>");
					xmlBuff.append("<tax_env><![CDATA[" + (taxEnv==null?"":taxEnv) + "]]></tax_env>");
					xmlBuff.append("<discount><![CDATA[" + rcpDiscount + "]]></discount>");
					xmlBuff.append("<disc_amt><![CDATA[" + rcpDiscAmt + "]]></disc_amt>");
					xmlBuff.append("<tax_amt><![CDATA[" + rcpTaxAmount + "]]></tax_amt>");
					xmlBuff.append("<net_amt><![CDATA[" + rcpNetAmount + "]]></net_amt>");
					xmlBuff.append("<post_diff><![CDATA[I]]></post_diff>");
					xmlBuff.append("<chg_date><![CDATA[" + chgDate + "]]></chg_date>");
					xmlBuff.append("<chg_user><![CDATA[" + (userId==null?"BASE":userId) + "]]></chg_user>");
					xmlBuff.append("<chg_term><![CDATA[" + (termId==null?"BASE":termId) + "]]></chg_term>");
					xmlBuff.append("<item_ser><![CDATA[" + (itemSer==null?"":itemSer) + "]]></item_ser>");
					xmlBuff.append("<vouch_qty__stduom><![CDATA[" + rcpQtyStd + "]]></vouch_qty__stduom>");
					xmlBuff.append("<vouch_rate__stduom><![CDATA[" + rcpRateStd + "]]></vouch_rate__stduom>");
					xmlBuff.append("<acct_code><![CDATA[" + (acctCodePH==null?"":acctCodePH) + "]]></acct_code>");
					xmlBuff.append("<cctr_code><![CDATA[" + (ccrtCodePH==null?"":ccrtCodePH) + "]]></cctr_code>");
					xmlBuff.append("<rate__clg><![CDATA[" + rateClg + "]]></rate__clg>");
					xmlBuff.append("<std_rate><![CDATA[" + stdRate + "]]></std_rate>");
					//xmlBuff.append("<acct_code__diff><![CDATA[" + acctCodePPV + "]]></acct_code__diff>");
					//xmlBuff.append("<cctr_code__diff><![CDATA[" + cctrCodePPV + "]]></cctr_code__diff>");
					xmlBuff.append("<dept_code><![CDATA[" + (deptCode==null?"":deptCode) + "]]></dept_code>");
					xmlBuff.append("<anal_code><![CDATA[" + (analCode==null?"":analCode) + "]]></anal_code>");
					//xmlBuff.append("<proj_code><![CDATA[" + projCode + "]]></proj_code>");
					xmlBuff.append("</Detail3>");

				} // return details
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
			} // P-RET
			
			
			
			
			
			xmlBuff.append("</Header0>");
			xmlBuff.append("</group0>");
			xmlBuff.append("</DocumentRoot>");
			System.out.println("xmlBuff.toString() ["+xmlBuff.toString() + "]");
			if (errString != null && errString.trim().length() > 0)
			{
				return errString;
			}
			masterStatefulLocal = (MasterStatefulLocal)ctx.lookup("ibase/MasterStatefulEJB/local"); 
			
			String retString = masterStatefulLocal.processRequest( authencate, siteCode, true, xmlBuff.toString()); 
			System.out.println("retString.:: "+retString);
			String tranIdVoucher = retString.substring( retString.indexOf("<TranID>")+8, retString.indexOf("</TranID>"));
			System.out.println("tranIdVoucher is :"+tranIdVoucher);

			if( retString.indexOf("Success") != -1 )
			{
				taxMultiplier = -1;
				sql = "update porcp set vouch_created = 'Y' where tran_id = ? "; 
				pstmtUpd = conn.prepareStatement(sql);
				pstmtUpd.setString(1, returnId);
				pstmtUpd.executeUpdate();
			}
			/* // 		
			// 3 Added by Vilas 		
			lds_vouchdet.reset()
			li_cnt = lds_vouchdet.retrieve(ls_vouch_id,1)
			if li_cnt = 1 then
				rcpTaxAmount = 0
				lds_vouchdet.setrow(lineCount)
				if isnull(advTotal) then advTotal = 0			
				lds_vouchdet.setitem(1,'tax_adv_amt',-advTotal)
				// 20-08-04 manoharan like in window instead of vouch_rate, vouch_qty
				// columns vouch_rate__stduom, vouch_qty__stduom columns passed

				//rcpTaxAmount  = gf_calc_tax_ds(lds_vouchdet, lds_taxdet,'VOUCH',ls_vouch_id,tranDate,"vouch_rate","vouch_qty" ,0, currCode)
				rcpTaxAmount  = gf_calc_tax_ds(lds_vouchdet, lds_taxdet,'VOUCH',ls_vouch_id,tranDate,"vouch_rate__stduom","vouch_qty__stduom" ,0, currCode)
				//committed and modified by prince james on 28-09-04
				//ls_errcode = gf_calc_tax_ds(lds_vouchdet, lds_taxdet,'VOUCH',ls_vouch_id,tranDate,"vouch_rate__stduom","vouch_qty__stduom" ,0, currCode)
				//rcpTaxAmount = dec(f_get_token(ls_errcode,'~t'))
				//ended by prince james on 28-09-04 
				// end 20-08-04 manoharan
				
				if rcpTaxAmount = -999999999 then
					ls_errcode = "Error calculating tax amount"
					exit
				else
					decimal {3} lc_var2
					lc_var2 = lds_vouchdet.getitemnumber(1,'vouch_amt')

					// amit - 26/08/02
					rcpDiscount = lds_vouchdet.getitemnumber(1,'disc_amt') 
					if isnull(rcpDiscount) then rcpDiscount = 0
					lc_var2 = lc_var2 - rcpDiscount 
					// end - amit - 26/08/02

					lds_vouchdet.setitem(1,'tax_amt',rcpTaxAmount)
					lds_vouchdet.setitem(1,'net_amt',lc_var2 + rcpTaxAmount)

					if lds_vouchdet.update() <> 1 then				
						ls_errcode = 'DS000'+string(Sqlca.sqlcode)+' ~t'+sqlca.sqlerrtext
						exit					
					else
						if lds_taxdet.update() <> 1 then
							ls_errcode = "Error calculating tax amount"
							exit				
						end if
					end if
				end if	
			else
				rcpTaxAmount = 0 
			end if	
			
			//amish 31-8-05 pickup the net amount for advance adjustment.	
			select sum(tax_amt), sum( vouch_amt - (case when disc_amt is null then 0 else disc_amt end) ), sum(net_amt)
				into :taxAmt, :vouchAmount, :netAmount
				from vouchrcp 
				where tran_id = :ls_vouch_id ;
			if get_sqlcode() < 0  then
				ls_errcode = 'DS000'+string(sqlca.sqlcode)+sqlca.sqlerrtext
				exit
			elseif get_sqlcode() = 100 then
				ls_errcode = 'DS000'+string(sqlca.sqlcode)+sqlca.sqlerrtext		
				exit
			end if
			
			lc_netamt_adj = vouchAmount + taxAmt		
			// amish 
			//amish 31-8-05 pickup the net amount for advance adjustment.	
			/// added or ( tran_ser like 'P-IBC%' ref_ser__org in ('P-ADV', 'LC-ADV', 'M-ADV') )
			
					if lc_netamt_adj > 0 then			
						declare c_adv cursor for
							select tran_ser, ref_no, curr_code, exch_rate, acct_code, 
								cctr_code, tot_amt, tot_amt - adj_amt, sundry_type, sundry_code
								from misc_payables 
								where ( tran_ser in ('P-ADV', 'LC-ADV', 'M-ADV') or ( tran_ser like 'P-IBC%' and ref_ser__org in ('P-ADV', 'LC-ADV', 'M-ADV') ) ) 
								and purc_order = :purcOrder 
								and tot_amt - adj_amt <> 0 and site_code = :siteCode ;
						open c_adv;
						if get_sqlcode() <> 0 then
							ls_errcode = 'DS000' + trim(string(sqlca.sqldbcode)) + ' ~t ' + sqlca.sqlerrtext
							exit
						end if	
						fetch c_adv into :ls_refser, :ls_refno, :ls_curradv, :lc_exchadv, :ls_acctcode,
							:ls_cctrcode, :lc_totamt, :lc_advamt, :ls_stype, :ls_scode;
						do while	sqlca.sqlcode = 0 and netAmount > 0
							if trim(currCode) <> trim(ls_curradv) then
								ls_errcode = 'VTCURRMIS' + ' ~t' + ' curr. : ' + currCode + ' and adv. curr. :' + ls_curradv
								exit
							end if	
							if ls_stype <> 'S' or trim(ls_scode) <> trim(suppCode) then
								ls_errcode = 'VTSUPPMIS' + ' ~t' + ' suppliers : ' + suppCode + ' and adv. suppl.:' + ls_scode
								exit
							end if	
							
							///amish 01-08-05 for ad 
							/// consider the amount automatically adjyusted and 
							/// but voucher not confirmed.
							select sum(adj_amt)
							into 	:lc_advadj
							from voucher a, vouchadv b
							where a.tran_id = b.tran_id
							and	b.ref_ser = :ls_refser
							and	b.ref_no =  :ls_refno
							and   a.confirmed = 'N' 
							and 	a.tran_mode = 'A' ;
							
							if get_sqlcode() <> 0 then
								ls_errcode = 'DS000' + trim(string(sqlca.sqldbcode)) + ' ~t ' + sqlca.sqlerrtext
								exit
							end if	
							
							if isnull(lc_advadj) then  lc_advadj = 0
							
							lc_advamt = lc_advamt - lc_advadj
							
							if lc_advamt <> 0 then 
								ls_refno = trim(ls_refno)
								if abs(lc_advamt) > netAmount then lc_advamt = 0 - netAmount
								lc_diffexch = 0
								lc_diffexch = (lc_advamt * lc_exchadv) - (lc_advamt * exchRate)
								diffTotal = diffTotal + lc_diffexch
								advTotal  = advTotal  + lc_advamt
								insert into vouchadv 
									(tran_id, ref_ser, ref_no, adj_amt, tot_amt, curr_code,
									 exch_rate__vouch, diff_amt__exch, acct_code, cctr_code,auto_generated)
								values
									(:ls_vouch_id, :ls_refser, :ls_refno, :lc_advamt, :lc_totamt, :ls_curradv,
									 :lc_exchadv, :lc_diffexch, :ls_acctcode, :ls_cctrcode,'A'); //zahid 08/04/04 for auto_generated voucher advance);
								if get_sqlcode() <> 0 then
									ls_errcode = 'DS000' + string(sqlca.sqldbcode) + ' ~t' + sqlca.sqlerrtext
									exit
								end if
								netAmount = netAmount + lc_advamt
								lc_netamt_adj = lc_netamt_adj + lc_advamt //amish 31-08-05 									
							end if
							
							fetch c_adv into :ls_refser, :ls_refno, :ls_curradv, :lc_exchadv, :ls_acctcode,
								:ls_cctrcode, :lc_totamt, :lc_advamt, :ls_stype, :ls_scode;
						loop		
						close c_adv;
						if not isnull(ls_errcode) and len(trim(ls_errcode)) > 0 then
							exit
						end if	
					end if
					

				end if // 07-06-04  moved from below
				
				//Added by Vilas
				//Converted nvl to case when by Archiman 12/08/08
				//Commented nvl(disc_amt,0)
				if isnull(taxAmt) then taxAmt = 0
				select sum(tax_amt), sum( vouch_amt - (case when disc_amt is null then 0 else disc_amt end) ), sum(net_amt)
					into :taxAmt, :vouchAmount, :netAmount
					from vouchrcp 
					where tran_id = :ls_vouch_id ;
				if get_sqlcode() < 0  then
					ls_errcode = 'DS000'+string(sqlca.sqlcode)+sqlca.sqlerrtext
					exit
				elseif get_sqlcode() = 100 then
					ls_errcode = 'DS000'+string(sqlca.sqlcode)+sqlca.sqlerrtext		
					exit
				end if
				
				netAmountBase = netAmount * exchRate
				
				/// amish 24-12-05 calculation for reteintion amount 
				//Commented nvl(rel_amt,0)	 , nvl(rel_after,0) by Archiman 12/08/08
				//Converted nvl to case by Archiman 12/08/08
				if len(ls_errcode) > 0 then exit
				
				if gs_database = 'db2' then
					//select amt_type	, nvl(rel_amt,0)	 , nvl(rel_after,0)
					select amt_type	, (case when rel_amt is null then 0 else rel_amt end ), (case when rel_after is null then 0 else rel_after end )
					into 	 :ls_advtype, :ld_advperc, :ll_relafter
					from 	 pord_pay_term
					where  purc_order = :purcOrder
					and 	 rel_agnst  = '04'
					fetch first row only ;		
				else
					//select amt_type	, nvl(rel_amt,0)	 , nvl(rel_after,0)
					select amt_type	, (case when rel_amt is null then 0 else rel_amt end ), (case when rel_after is null then 0 else rel_after end )
					into 	 :ls_advtype, :ld_advperc, :ll_relafter
					from 	 pord_pay_term
					where  purc_order = :purcOrder 
					and 	 rel_agnst  = '04'
					and 	 rownum = 1 ;
				end if

				if get_sqlcode() < 0  then
					ls_errcode = 'DS000'+string(sqlca.sqlcode)+sqlca.sqlerrtext
					exit
				end if
				
				if ls_advtype = '01' then // 'Base'
					//ls_advtype = 'B'
					lc_reten_amt = vouchAmount * ld_advperc / 100
				elseif ls_advtype = '02' then //'Net'
					//ls_advtype = 'P'
					lc_reten_amt = ( vouchAmount + taxAmt ) * ld_advperc / 100		
				elseif ls_advtype = '03' then //'Fixed amt.'
					//ls_advtype = 'F'
					//lc_reten_amt = lc_reten_amt
				end if
				
				lc_netamt_bc = (vouchAmount + taxAmt + advTotal ) * exchRate // Added by fatema - 04/01/2007 -to avoid decimal difference error - Req FI6CHT0034
				netAmount = vouchAmount + taxAmt + advTotal // Added by fatema-25/06/2007-FI78CHT004-To avoid error in db2.
				totAmount = vouchAmount + taxAmt  // Added by fatema-25/06/2007-FI78CHT004-To avoid error in db2.
				update voucher 
					set bill_amt = :vouchAmount, 
						 tax_amt = :taxAmt, 
			//			 tot_amt = :vouchAmount + :taxAmt  // Commented by fatema-25/06/2007-FI78CHT004-Expression moved above update stat To avoid error in db2.
			//			 net_amt = :vouchAmount + :taxAmt + :advTotal // Commented by fatema-25/06/2007-FI78CHT004-Expression moved above update stat To avoid error in db2.
						 tot_amt = :totAmount,  // Added by fatema-25/06/2007-FI78CHT004
						 net_amt = :netAmount,  // Added by fatema-25/06/2007-FI78CHT004
						 adv_amt = :advTotal,
			//			 net_amt__bc = ( :vouchAmount + :taxAmt + :advTotal ) * :exchRate, // Commented by fatema - 04/01/2007 - Expression moved above update stat to store in dec3 variab
						 net_amt__bc = :lc_netamt_bc, // Added by fatema - 04/01/2007
						 diff_amt__exch = :diffTotal ,
						 retention_amt = :lc_reten_amt ,
						 retention_days = :ll_relafter
				where tran_id = :ls_vouch_id;
				if get_sqlcode() < 0 then
					ls_errcode = 'DS000' + string(sqlca.sqldbcode) + ' ~t' + sqlca.sqlerrtext
					exit
				elseif sqlca.sqlnrows <> 1 then
					ls_errcode = 'DS000NR'
					exit
				end if	
				//end if // 07-06-04 
				
				////// added by deepali 19/03/04	
				if autoConfirm = 'Y' then
					//Commented and Added by shahid 12/11/2008 for FI6SUP0047
			//		ls_errcode = nvo_voucher.gbf_retrieve_purc_vou(ls_vouch_id, ls_vouch_id,'Y', 1, as_warning) 
					ls_errcode = nvo_voucher.gbf_retrieve_purc_vou(ls_vouch_id, ls_vouch_id,'Y', 1, as_warning,siteCode) 

					if len(trim(ls_errcode)) > 0 then
						rollback ; // if error then roll back
						// openwithparm(w_msg, ls_errcode) //commented by prasant on 09/07/05 as per monoharanji
						exit
					else			
						commit; // commit if error not found
						// openwithparm(w_msg, 'VTMCONF2') //commented by prasant on 09/07/05 as per monoharanji
					end if	
				end if	
				////// end	
				
				update porcp set vouch_created = 'Y' where tran_id = :as_tran_id;
				if get_sqlcode() < 0 then
					ls_errcode = 'DS000' + string(sqlca.sqldbcode) + ' ~t' + sqlca.sqlerrtext
					exit
				elseif sqlca.sqlnrows <> 1 then
					ls_errcode = 'DS000NR'
					exit
				end if
				ls_errcode = checkVoucherValidity(ls_vouch_id,  conn);
				

			if ai_commit = 2 then
				if len(trim(ls_errcode)) = 0 then
					commit;
				else
					ls_sqlerr = sqlca.sqlerrtext
					rollback;
					sqlca.sqlerrtext = ls_sqlerr
				end if	
			end if	
			*/// cannot be done in script should be through cross update
	
		}//try
		catch(Exception exception)
		{   
			exception.printStackTrace();
			throw new ITMException(exception);
		}
		finally
		{
			try
			{
				if (rs != null)
				{
					rs.close(); rs = null;
				}
				if (pstmt != null)
				{
					pstmt.close(); pstmt = null;
				}
			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
		}
	    return (errString);
	}
	private String addTraceMsg(String errString, String mainMsg, String subMsg) throws ITMException, Exception
	{
		String begPart = "", begDesc = "", endDesc = "";
		try
		{
			begPart = errString.substring(0,errString.indexOf("<message>")+9);
			begDesc = errString.substring(0,errString.indexOf("<description>")+13);
			endDesc = errString.substring(errString.indexOf("</description>"));
		}	
		catch(Exception e)
		{
			throw new ITMException(e);
		}		
		return begPart + mainMsg + "</message><description>" + subMsg + endDesc;
	}
	public java.sql.Timestamp calcDueDate(String crTerm, java.sql.Timestamp recDate, java.sql.Timestamp  effDate, java.sql.Timestamp  billDate, Connection conn) throws ITMException
	{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int noOfDays = 0;
		java.sql.Timestamp dueDate = null;
		String calcDueFrom = "", sql = "";
		UtilMethods utilMethods = UtilMethods.getInstance();
		try
		{
			sql = " select cr_days, start_from  from crterm where cr_term = ?";

			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,crTerm);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				noOfDays = rs.getInt("cr_days");
				calcDueFrom = rs.getString("start_from");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;

			if ("R".equals(calcDueFrom))
			{
				dueDate = utilMethods.RelativeDate(recDate, noOfDays);
			}
			else if ("D".equals(calcDueFrom) || "Q".equals(calcDueFrom))	// 'D','Q' //,'Q' added by zahid 16/07/04 for QC approval date
			{
				dueDate = utilMethods.RelativeDate(effDate, noOfDays);
			}
			else if ("B".equals(calcDueFrom))
			{
				dueDate = utilMethods.RelativeDate(billDate, noOfDays);
			}

		} // try
		catch(Exception e)
		{
			throw new ITMException(e);
		}		
		finally
		{
			try
			{
				if(rs != null){rs.close();rs = null;}
				if(pstmt != null){pstmt.close();pstmt = null;}
			}
			catch(Exception e)
			{
				throw new ITMException(e);
			}
		}	
		return dueDate;
		
	}
	public double[] getPurcRate(String purcOrder,String itemCode,double poRate,double poDisc, Connection conn)  throws ITMException
	{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "";
		String porderNo = "", provNo = "";
		double rateDiscount[] = {0,0}; 
		try
		{
			sql = " select purc_order, (case when provi_tran_id is null then '' else provi_tran_id end) as provi_tran_id"
					+ " from porder where purc_order = ?"; //: ;

			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,purcOrder);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				porderNo = rs.getString("purc_order");
				provNo = rs.getString("provi_tran_id");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;

			if (porderNo.equals(provNo))
			{
				sql = " select rate, discount "
					+ " from porder a, porddet b "
					+ " where a.purc_order = b.purc_order "
					+ " and a.purc_order <>  ? "
					+ " and a.provi_tran_id = ? "
					+ " and b.item_code = ? "
					+ " and a.confirmed = 'Y' ";

				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,purcOrder);
				pstmt.setString(2,purcOrder);
				pstmt.setString(3,itemCode);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					poRate = rs.getDouble("rate");
					poDisc = rs.getDouble("discount");
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
			}
			else
			{
				poRate = -999;
				poDisc = -999;
			}
			rateDiscount[0] = poRate;
			rateDiscount[1] = poDisc;
		} // try
		catch(Exception e)
		{
			throw new ITMException(e);
		}		
		finally
		{
			try
			{
				if(rs != null){rs.close();rs = null;}
				if(pstmt != null){pstmt.close();pstmt = null;}
			}
			catch(Exception e)
			{
				throw new ITMException(e);
			}
		}	
		return rateDiscount;
		
	}
	private String checkVoucherValidity(String tranId, Connection conn)  throws ITMException
	{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "";
		String errCode = "",varValue = "";
		double billAmount = 0, advAmount = 0, taxAmount = 0, netAmount = 0, netAmountBC = 0;
		double taxTranAmount = 0, totAmount = 0, checkValue = 0, checkNetAmountBC = 0, exchRate = 0;
		int count = 0;
		DistCommon distCommon = new DistCommon();
		try
		{

			sql = "SELECT BILL_AMT, ADV_AMT, TAX_AMT, TOT_AMT, NET_AMT, NET_AMT__BC, EXCH_RATE "
				+ " FROM VOUCHER WHERE TRAN_ID = ? "; 
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,tranId);
			rs = pstmt.executeQuery();
			if(rs.next())
			{

				billAmount =  rs.getDouble("BILL_AMT");
				advAmount =  rs.getDouble("ADV_AMT");
				taxAmount =  rs.getDouble("TAX_AMT");
				totAmount =  rs.getDouble("TOT_AMT");
				netAmount =  rs.getDouble("NET_AMT");
				netAmountBC =  rs.getDouble("NET_AMT__BC");
				exchRate =  rs.getDouble("EXCH_RATE");

			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			// vilas round to 3 of netamt*exchrate
			checkNetAmountBC = netAmount * exchRate; // Added by fatema - 03/01/2007
			//In below if stateme,In 3 condn,Replaced round((netAmount * exchRate),3) 
			//with checkNetAmountBC by fatema-03/01/2007 to avoid decimal difference error - Req FI6CHT0034
			if (billAmount + taxAmount != totAmount || totAmount + advAmount != netAmount || checkNetAmountBC != netAmountBC || totAmount == 0 )
			{
					errCode = "VTVOUCAMT";
					return errCode;
			}
			
			// to take linewise total of the taxes - amit 20-11-01
			// shiri 20/12/01 to take only for effect <> 'N'
			//Removed TABLE keyword --Not work in DB2 ISeries -- Supreme --Prince --20-05-06
		//			TABLE( select tran_id, line_no, round(sum(nvl(tax_amt,0)),3) tot_tax
			if ("db2".equalsIgnoreCase(CommonConstants.DB_NAME ))
			{
				sql = "select SUM(tot_tax) as taxTranAmount from  "
					+ " ( select tran_id, line_no, round(sum(nvl(tax_amt,0)),3) tot_tax "
					+ " from taxtran a, tax b "
					+ "	where a.tax_code = b.tax_code "
					+ "	and tran_code = 'VOUCH' "
					+ "	and tran_id = ? " //:as_tranid 
					+ "	and b.effect <> 'N' "
					+ "	group by tran_id, line_no ) MANOHAR ";
			}
			else
			{
				sql = "select SUM(tot_tax) as taxTranAmount from  "
					+ " ( select tran_id, line_no, round(sum(nvl(tax_amt,0)),3) tot_tax "
					+ " from taxtran a, tax b "
					+ "	where a.tax_code = b.tax_code "
					+ "	and tran_code = 'VOUCH' "
					+ "	and tran_id = ? " //:as_tranid 
					+ "	and b.effect <> 'N' "
					+ "	group by tran_id, line_no ) ";
			}
			//// end.
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,tranId);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				taxTranAmount = rs.getDouble("taxTranAmount");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			
			if (taxTranAmount != taxAmount)
			{
				errCode = "VTVOUCAMT";
				return errCode;
			}

			// Ashok on 7/1/03 to get the variance defined in the variable

			varValue = distCommon.getDisparams("999999","RCP_UOM_VARIANCE",conn);
			if ("NULLFOUND".equals(varValue))
			{
				errCode = "VTUOMVARPARM";
				return errCode;
			}

			checkValue = Double.parseDouble(varValue);	
			
			sql = "select count(1) from vouchrcp  where tran_id = ? "
				+ " and abs( (vouch_qty__stduom * vouch_rate__stduom - vouch_qty * vouch_rate) ) > ? ";
			// End of changes on 7/1/03			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,tranId);
			pstmt.setDouble(2,checkValue);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				count = rs.getInt(1);
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			
			if (count > 0)
			{
				errCode = "VTVOUCQTY1";
				return errCode;
			}
		} // try
		catch(Exception e)
		{
			throw new ITMException(e);
		}		
		finally
		{
			try
			{
				if(rs != null){rs.close();rs = null;}
				if(pstmt != null){pstmt.close();pstmt = null;}
			}
			catch(Exception e)
			{
				throw new ITMException(e);
			}
		}	
		return errCode;
	}

}