package ibase.webitm.ejb.fin;

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.util.ArrayList;
import java.util.Date;
import java.util.HashMap;

import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.ActionHandlerEJB;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.ejb.ValidatorEJB;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;

public class PurchaseVoucherGltracePreview extends ActionHandlerEJB implements PurchaseVoucherGltracePreviewLocal,PurchaseVoucherGltracePreviewRemote
{
	public String preview(String tranID,String xtraParams, String forcedFlag) throws RemoteException,ITMException
	{
		System.out.println("Preview Method is called for Purchase Voucher");
		String retString = "";
	    boolean adj_amt_flag = true,amt_upd_flag = true,retenamt_flag = true,hold_tax_flag = true;
		Connection conn = null;
		String sql = "";
		PreparedStatement pstmt = null,pstmt1 = null,pstmt2 = null;
		ResultSet rs = null,rs1 = null,rs2 = null;
		FinCommon finCommon = null;
		GenericUtility genericUtility = null;
		ITMDBAccessEJB itmDBAccessEJB = null;
		ValidatorEJB validatorEJB = null;
		
		String errString = "",lineNoDet = "" ;
		
		String loginEmpCode = "",acctCodeDet ="",	lineno = "",cctrCodeDet = "",deptCodeDet = "",empCodeDet = "",analCodeDet = "",analysis1 = "",analysis2 = "",analysis3 = "",basiccurr = "";
	
		double	taxtranamt = 0.0,	taxamtDet  = 0.0,advAmt = 0.0 ,amount = 0.0,vouchAmt = 0.0;	
		
		String acct_ap = "",acct_aphdr = "",vouchtype = "",purc_order= "",vouch_adv_amt= "",cctrcode = "",tran_id = "", proj_code = "";
		String cctr_ap = "",cctr_aphdr = "",refno_det = "",retention_days = "",retdays = "",suppcode = "";
		String refser_det = "",finentity = "",sitecode = "",sundrycode = "", empcode = "",currcode = "",remarks = "";
		double  amtupd = 0.0,retenamt = 0.0,advadj_det = 0.0,net_amt = 0.0,tax_amt = 0.0,retention_amt = 0.0,rndamt = 0.0,holdTax = 0.0,diffamtexch=0.0,exch_h = 0.0;
		double retentiondays = 0.0,retentionamt = 0.0,exch_d = 0.0,adjamt = 0.0;
		double adv_add = 0.0,hold_tax = 0.0, exchratevch= 0.0 , billamt = 0.0 ,taxamt = 0.0;
		String acctcodeadv ="",cctrcodeadv = "",currcodeadv = "", ref_id = "",refser = "",refNo = "",analcode = "";
		String acctreten ="",cctrreten = "",acct_code__cf = "",cctr_code__cf = "",tranMode = "",detcurrcode = "",projCodeDet = "",projCodeDet1 = "";
		HashMap hdrmap = new HashMap();
		Date effDate = null;
		Date tranDate = null;
		Date refDate = null;
		System.out.println("tran id = "+tranID);
		boolean connStatus=false;
		try{
			if ( conn == null )//changed by Gulzar - 25/11/11
			{
				ConnDriver connDriver = null;
				connDriver = new ConnDriver();
				conn = connDriver.getConnectDB("DriverITM");
				conn.setAutoCommit(false);
				connDriver = null;
				connStatus = true;
			}
			
			finCommon = new FinCommon();
			genericUtility = new GenericUtility();
			itmDBAccessEJB = new ITMDBAccessEJB();
			validatorEJB = new ValidatorEJB();
			loginEmpCode = validatorEJB.getValueFromXTRA_PARAMS(xtraParams, "loginEmpCode");
			System.out.println("Printing loginEmpCode---1-----"+loginEmpCode+"--");
	//Header coding ********************************************************************************************
			
			sql = "select  tran_id,tran_date,(case when retention_amt is null then 0 else retention_amt end) as retention_amt,(case when retention_days is null then 0 else retention_days end) as retention_days," 
	              +"eff_date,fin_entity,site_code,supp_code,acct_code,cctr_code,emp_code,curr_code,exch_rate,"
					+"remarks,proj_code,anal_code,rnd_amt,vouch_type,tran_mode,acct_code__cf,cctr_code__cf,net_amt,purc_order,bill_amt,tax_amt,adv_amt from voucher where tran_id = ?";
				
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,tranID);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					
					tran_id = rs.getString("tran_id");
					tranDate = new Timestamp(rs.getDate("tran_Date").getTime());
					effDate = new Timestamp(rs.getDate("eff_date").getTime());
					finentity = rs.getString("fin_entity");
					sitecode = rs.getString("site_code");
					sundrycode = rs.getString("supp_code");
				    empcode = rs.getString("emp_code");
					currcode = rs.getString("curr_code");
					remarks = rs.getString("remarks");
					analcode = rs.getString("anal_code");
					ref_id = rs.getString("tran_id");
					suppcode  = rs.getString("supp_code");
					acct_aphdr = rs.getString("acct_code");
					cctr_aphdr = rs.getString("cctr_code")==null?"":rs.getString("cctr_code");
					proj_code = rs.getString("proj_code");
					exch_h = rs.getDouble("exch_rate");
					rndamt = rs.getDouble("rnd_amt");
					vouchtype = rs.getString("vouch_type");
					acct_code__cf = rs.getString("acct_code__cf");
					cctr_code__cf = rs.getString("cctr_code__cf");
					billamt = rs.getDouble("bill_amt");
					taxamt = rs.getDouble("tax_amt");
					tranMode = rs.getString("tran_mode");
					advAmt = rs.getDouble("adv_amt");
					System.out.println("VOUCHER TYPE>>>>>>>>>>>>>>>>>"+vouchtype);
					if("A".equalsIgnoreCase(vouchtype))
					{
						System.out.println("VOUCHER TYPE>>>>>>>>>>>>>>>>>"+vouchtype);
						amtupd = rs.getDouble("net_amt");
						net_amt = rs.getDouble("net_amt");
						purc_order = rs.getString("purc_order");
						
						System.out.println("AMTUPD..........................."+amtupd);
						sql = "select case when vouch_adv_amt is null then 0 else vouch_adv_amt end as vouch_adv_amt from porder where purc_order = ?";
						pstmt2 = conn.prepareStatement(sql);
						pstmt2.setString(1,purc_order);
						rs2 = pstmt2.executeQuery();
						if(rs2.next())
						{
						vouch_adv_amt = rs2.getString("vouch_adv_amt");
						}
						rs2.close();
						rs2=null;
						pstmt2.close();
						pstmt2=null;
						
						//not written code for updating porder
						
						retenamt = 0.0;//retension amount is set 0 if voucher type is advance
					}
					else{
						System.out.println("VOUCHER TYPE>>>>>>>>>>>>>>>>>"+vouchtype);
						sql = "select (case when sum(tax_amt) is null then 0 else sum(tax_amt) end)  as hold_tax from taxtran where tran_code = 'VOUCH' and tran_id = ?  and pay_tax = 'Y'";
						
						pstmt1 = conn.prepareStatement(sql);
						pstmt1.setString(1,tranID);
						rs1 = pstmt1.executeQuery();
						if(rs1.next())
						{
							holdTax = rs1.getDouble("hold_tax");
						}
						rs1.close();
						rs1=null;
						pstmt1.close();
						pstmt1=null;
						
						amtupd = billamt + taxamt - holdTax;
						retentionamt = rs.getDouble("retention_amt");
						retentiondays = rs.getDouble("retention_days");
						
						amtupd = amtupd - retentionamt;
					}// end of vch_type = A condition
				}	
				rs.close();
				rs=null;
				pstmt.close();
				pstmt=null;
				
					
					
					//retriving data from vouchadv 
					sql="select adj_amt,exch_rate__vouch,curr_code,acct_code,cctr_code,ref_ser,ref_no from vouchadv where tran_id = ? ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,tranID);
					rs = pstmt.executeQuery();
					while(rs.next())
					{
						adjamt = rs.getDouble("adj_amt");
						exchratevch = rs.getDouble("exch_rate__vouch");
						currcodeadv = rs.getString("curr_code");
						acctcodeadv = rs.getString("acct_code");
						cctrcodeadv = rs.getString("cctr_code");
						refser = rs.getString("ref_ser");
						refNo = rs.getString("ref_no");
					}
					rs.close();
					rs=null;
					pstmt.close();
					pstmt=null;
					
					if(acct_aphdr == acctcodeadv && cctr_aphdr == cctrcodeadv && exch_h == exchratevch){
						
					}
					else{
						//hdrmap = new HashMap();
						adv_add = adv_add + adjamt; 
						hdrmap.put("tran_date",tranDate);
						hdrmap.put("eff_date", effDate);
						hdrmap.put("fin_entity",finentity);
						hdrmap.put("site_code",sitecode);
						hdrmap.put("sundry_type",'S');
						hdrmap.put("sundry_code",sundrycode);
						hdrmap.put("acct_code",acctcodeadv);
						hdrmap.put("cctr_code",cctrcodeadv);
						hdrmap.put("emp_code",empcode);
						hdrmap.put("anal_code","");
						hdrmap.put("curr_code", currcodeadv);
						hdrmap.put("exch_rate",exchratevch);
						
					
						if(adjamt > 0.0)
						{
						hdrmap.put("dr_amt", adjamt);
						hdrmap.put("cr_amt",0.0);
						System.out.println("adjamt>>>>>>>"+adjamt);
					
					    }

					else{
						hdrmap.put("dr_amt", 0.0);
						hdrmap.put("cr_amt",0- adjamt);
						System.out.println("adjamt>>>>>>>"+adjamt);
					    }
					
						hdrmap.put("ref_type","F");
						hdrmap.put("ref_ser","VOUCH");
						hdrmap.put("ref_id",ref_id);
						hdrmap.put("remarks",remarks);
						hdrmap.put("proj_code",proj_code);
						String party_doc_ref = finCommon.gfGetPartyDocRef("VOUCH",ref_id,conn);
						hdrmap.put("party_doc_ref",party_doc_ref);
						
						System.out.println("calling Gltrace Update for Header adj_amt*******************");
						errString = finCommon.glTraceUpdate(hdrmap,conn,true); 
						System.out.println("ERROR STRING>>>>>>>>>>>>>>>>>>>>>>["+errString+"]");
						if(errString != null && errString.trim().length() > 0)
						{
							return errString;
						}
						
				      }
			
					//hdrmap = new HashMap();
				         	amtupd = amtupd + adjamt;
							System.out.println("AMT_UPD>>>>>>>> >>>>"+amtupd);
							hdrmap.put("tran_date",tranDate);
							hdrmap.put("eff_date", effDate);
							hdrmap.put("fin_entity",finentity);
							hdrmap.put("site_code",sitecode);
							hdrmap.put("sundry_type",'S');
							hdrmap.put("sundry_code",sundrycode);
							hdrmap.put("acct_code",acct_aphdr);//from header
							hdrmap.put("cctr_code",cctr_aphdr);//from header
							hdrmap.put("emp_code",empcode);
							hdrmap.put("anal_code",analcode);
							hdrmap.put("curr_code",currcode );
							hdrmap.put("exch_rate",exch_h);
							
							if(amtupd > 0)
							{
								hdrmap.put("dr_amt",0.0);
								hdrmap.put("cr_amt",amtupd + rndamt);
								System.out.println("amtupd>>>>>>>"+amtupd);
							
							    }

							else{
								hdrmap.put("dr_amt", 0.0 -(amtupd + rndamt) );
								hdrmap.put("cr_amt",0.0);
								System.out.println("amtupd>>>>>>>"+amtupd);
							    }
							hdrmap.put("ref_type","F");
							hdrmap.put("ref_ser","VOUCH");
							hdrmap.put("ref_id",ref_id);
							hdrmap.put("remarks",remarks);
							hdrmap.put("proj_code",proj_code);
			
							String party_doc_ref = finCommon.gfGetPartyDocRef("VOUCH",ref_id,conn);
							hdrmap.put("party_doc_ref",party_doc_ref);
							
							System.out.println("calling Gltrace Update for Header amtupd*******************");
							
							errString = finCommon.glTraceUpdate(hdrmap,conn,true); 
							System.out.println("ERROR STRING>>>>>>>>>>>>>>>>>>>>>>["+errString+"]");
							if(errString != null && errString.trim().length() > 0)
							{
								return errString;
							}
			          //Retention amt
							sql = "select acct_code__reten as acctreten, cctr_code__reten as cctrreten from supplier where supp_code = ?" ;
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1,suppcode);
							rs = pstmt.executeQuery();
							while(rs.next())
							{
								acctreten = rs.getString("acctreten");
								cctrreten = rs.getString("cctrreten");
							}
							rs.close();
							rs=null;
							pstmt.close();
							pstmt=null;
							
							if ( retentionamt != 0.0)
							{
								hdrmap.put("sundry_type",'S');
								hdrmap.put("sundry_code",suppcode);
								hdrmap.put("acct_code",acctreten);
								hdrmap.put("cctr_code",cctrreten);
								
								if(retentionamt > 0.0)
								{
									hdrmap.put("dr_amt",0.0);
									hdrmap.put("cr_amt",retentionamt);
									System.out.println("retentionamt>>>>>>>"+retentionamt);
								
							    }

								else{
									hdrmap.put("dr_amt",0 - retentionamt);
									hdrmap.put("cr_amt",0.0);
									System.out.println("retentionamt>>>>>>>"+retentionamt);
								    }
								
								System.out.println("calling Gltrace Update for Header retentionamt*******************");
								errString = finCommon.glTraceUpdate(hdrmap,conn,true); 
								System.out.println("ERROR STRING>>>>>>>>>>>>>>>>>>>>>>["+errString+"]");
								if(errString != null && errString.trim().length() > 0)
								{
									return errString;
								}
							}
			//Hold Tax
			        if(holdTax != 0.0)
			        {
			        	hdrmap.put("tran_date",tranDate);
						hdrmap.put("eff_date", effDate);
						hdrmap.put("fin_entity",finentity);
						hdrmap.put("site_code",sitecode);
						hdrmap.put("sundry_type",'S');
						hdrmap.put("sundry_code",sundrycode);
						hdrmap.put("acct_code",acct_aphdr);//from header
						hdrmap.put("cctr_code",cctr_aphdr);//from header
						hdrmap.put("emp_code",empcode);
						hdrmap.put("anal_code",analcode);
						hdrmap.put("curr_code",currcode );
						hdrmap.put("exch_rate",exch_h);
						
						if(holdTax  > 0.0)
						{
							hdrmap.put("dr_amt", 0.0);
							hdrmap.put("cr_amt",holdTax);
							System.out.println("holdTax>>>>>>>"+holdTax);
						}
						else{
							
							hdrmap.put("dr_amt",0.0 -  holdTax);
							hdrmap.put("cr_amt",0.0);
							System.out.println("holdTax>>>>>>>"+holdTax);
						}
						
						hdrmap.put("ref_type","F");
						hdrmap.put("ref_ser","VOUCH");
						hdrmap.put("ref_id",ref_id);
						hdrmap.put("remarks",remarks);
						hdrmap.put("proj_code",proj_code);
		
						party_doc_ref = finCommon.gfGetPartyDocRef("VOUCH",ref_id,conn);
						hdrmap.put("party_doc_ref",party_doc_ref);
						System.out.println("calling Gltrace Update for hold tax*******************");
						errString = finCommon.glTraceUpdate(hdrmap,conn,true); 
						System.out.println("ERROR STRING>>>>>>>>>>>>>>>>>>>>>>["+errString+"]");
							if(errString != null && errString.trim().length() > 0)
							{
								return errString;
							}
			        }
			     // update gltrace for currency fluctuation
			        
			        if( diffamtexch != 0.0)
					{
			        	System.out.println("diffamtexch>>>>>>>"+diffamtexch);
						
			        	hdrmap.put("tran_date",tranDate);
						hdrmap.put("eff_date", effDate);
						hdrmap.put("fin_entity",finentity);
						hdrmap.put("site_code",sitecode);
						
						hdrmap.put("sundry_type",'O');
						hdrmap.put("sundry_code","");
						hdrmap.put("acct_code",acct_code__cf);//from header
						hdrmap.put("cctr_code",cctr_code__cf);//from header
						
						if(cctrcode == null || cctrcode.trim().length() == 0)
						{
							cctrcode = "";
						}
						hdrmap.put("emp_code","");
						hdrmap.put("anal_code","");
						hdrmap.put("curr_code",currcode);
						hdrmap.put("exch_rate",1);
						
						if(diffamtexch  > 0.0)
						{
							hdrmap.put("dr_amt", 0.0);
							hdrmap.put("cr_amt",diffamtexch);
							System.out.println("diffamtexch>>>>>>>"+diffamtexch);
						}
						else{
							
							hdrmap.put("dr_amt",0.0 - diffamtexch);
							hdrmap.put("cr_amt",0.0);
							System.out.println("diffamtexch>>>>>>>"+diffamtexch);
						}
						
						hdrmap.put("ref_type","F");
						hdrmap.put("ref_ser","VOUCH");
						hdrmap.put("ref_id",ref_id);
						hdrmap.put("remarks",remarks);
						hdrmap.put("proj_code",proj_code);
		
						party_doc_ref = finCommon.gfGetPartyDocRef("VOUCH",ref_id,conn);
						hdrmap.put("party_doc_ref",party_doc_ref);
						System.out.println("calling Gltrace Update for diff_amt_exch*******************");
					   errString = finCommon.glTraceUpdate(hdrmap,conn,true); 
						System.out.println("ERROR STRING>>>>>>>>>>>>>>>>>>>>>>["+errString+"]");
							if(errString != null && errString.trim().length() > 0)
							{
								return errString;
							}
					}
			
			//Detail Coding*******
			        sql = "select line_no from vouchdet where tran_id = ?";
			        pstmt1 = conn.prepareStatement(sql);
					pstmt1.setString(1,tranID);
					rs1 = pstmt1.executeQuery();
					while(rs1.next())
					{
			        lineNoDet = rs1.getString("line_no");
			        System.out.println("POSTING DETAIL!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!"+rs1.getRow());
			        sql = "SELECT finent.curr_code  as basecurr  FROM finent  WHERE finent.fin_entity  = ? " ;
			    	pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,finentity);
					rs = pstmt.executeQuery();
					while(rs.next())
					{
						detcurrcode = rs.getString("basecurr");
					}
					rs.close();
					rs=null;
					pstmt.close();
					pstmt=null;
			        
					 sql = "SELECT proj_code,line_no,tax_amt,acct_code,amount,cctr_code,dept_code,emp_code,anal_code,analysis1,analysis2,analysis3,basic_curr FROM vouchdet  WHERE tran_id = ? and line_no = ? " ;
				    	pstmt = conn.prepareStatement(sql);
						pstmt.setString(1,tranID);
						pstmt.setString(2,lineNoDet);
						rs = pstmt.executeQuery();
						while(rs.next())
						{
							projCodeDet1 = rs.getString("proj_code");
							lineno = rs.getString("line_no");
							taxamtDet = rs.getDouble("tax_amt");
							acctCodeDet = rs.getString("acct_code");
							amount = rs.getDouble("amount");
							cctrCodeDet = rs.getString("cctr_code");
							deptCodeDet = rs.getString("dept_code");
							empCodeDet = rs.getString("emp_code");
							analCodeDet = rs.getString("anal_code");
							analysis1 = rs.getString("analysis1");
							analysis2 = rs.getString("analysis2");
							analysis3 = rs.getString("analysis3");
							basiccurr = rs.getString("basic_curr");
							//vouchAmt = rs.getDouble("vouch_amt");
						}
						rs.close();
						rs=null;
						pstmt.close();
						pstmt=null;
				        
					// for each detail do the posting
				
				if(tranMode == "M")
				{
					projCodeDet = projCodeDet1;
				}
				else{
					projCodeDet = proj_code;
				}
				
				lineno = "    " + lineno;
				lineno = lineno.substring(lineno.length() - 3,lineno.length());
				hdrmap.put("tran_date",tranDate);
				hdrmap.put("eff_date", effDate);
				hdrmap.put("fin_entity",finentity);
				hdrmap.put("site_code",sitecode);
				hdrmap.put("exch_rate",exch_h);
				if(vouchtype == "A" && advAmt != 0.0 &&  acctCodeDet.trim().length() == acct_aphdr.trim().length())
				{
					hdrmap.put("sundry_type",'S');
					hdrmap.put("sundry_code",suppcode);
					sql = "select (case when sum(tax_amt) is null then 0 else sum(tax_amt) end)  as taxtranamt from taxtran where tran_code = 'VOUCH' and tran_id = ? and line_no = ? and pay_tax = 'Y'";
					
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,tranID);
					pstmt.setString(2,lineno);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						taxtranamt = rs.getDouble("taxtranamt");
					}
					rs.close();
					rs=null;
					pstmt.close();
					pstmt=null;
					if(amount > 0)
					{
						hdrmap.put("dr_amt",(amount - taxtranamt));
						hdrmap.put("cr_amt",0.0);
						System.out.println("amount>>>>>>>"+amount);
					
					    }

					else{
						hdrmap.put("dr_amt", 0.0  );
						hdrmap.put("cr_amt",0.0 - (amount - taxtranamt) );
						System.out.println("amount>>>>>>>"+amount);
					    }
					
				}
				else{
					hdrmap.put("sundry_type",'O');
					hdrmap.put("sundry_code","");
					if(amount > 0)
					{
						hdrmap.put("dr_amt",amount);
						hdrmap.put("cr_amt",0.0);
						System.out.println("amount>>>>>>>"+amount);
					
				    }

					else{
						hdrmap.put("dr_amt", 0.0);
						hdrmap.put("cr_amt",0.0 - amount);
						System.out.println("amount>>>>>>>"+amount);
					    }
					
				}
				
				hdrmap.put("acct_code",acctCodeDet);
				hdrmap.put("cctr_code",cctrCodeDet);
				hdrmap.put("dept_code",deptCodeDet);
				hdrmap.put("emp_code",empCodeDet);
				hdrmap.put("anal_code",analCodeDet);
				hdrmap.put("analysis1",analysis1);
				hdrmap.put("analysis2",analysis2);
				hdrmap.put("analysis3",analysis3);
				/// if posting is in basic currency then put the exchange rate 1	
				
				if(basiccurr == "Y")
				{
					hdrmap.put("curr_code",basiccurr);
					hdrmap.put("exch_rate",1);
				}
				else 
				{
					hdrmap.put("curr_code",currcode );
					hdrmap.put("exch_rate",exch_h);
				}
				hdrmap.put("ref_type","F");
				hdrmap.put("ref_ser","VOUCH");
				hdrmap.put("ref_id",ref_id);
				hdrmap.put("remarks",remarks);
				hdrmap.put("proj_code",proj_code);

				 party_doc_ref = finCommon.gfGetPartyDocRef("VOUCH",ref_id,conn);
				hdrmap.put("party_doc_ref",party_doc_ref);
				
				System.out.println("calling Gltrace Update for detail 1*******************");
				
				errString = finCommon.glTraceUpdate(hdrmap,conn,true); 
				System.out.println("ERROR STRING>>>>>>>>>>>>>>>>>>>>>>["+errString+"]");
				if(errString != null && errString.trim().length() > 0)
				{
					return errString;
				}
				
			      if(taxtranamt != 0.0)
			      {
			    	    hdrmap.put("tran_date",tranDate);
						hdrmap.put("eff_date", effDate);
						hdrmap.put("fin_entity",finentity);
						hdrmap.put("site_code",sitecode);
						hdrmap.put("sundry_type",'S');
						hdrmap.put("sundry_code",suppcode);
			    	    if(taxtranamt > 0.0)
			    	    {
			    	    	hdrmap.put("dr_amt",0.0);
							hdrmap.put("cr_amt",taxtranamt);
							System.out.println("taxtranamt>>>>>>>"+taxtranamt);
						
					    }

						else{
							hdrmap.put("dr_amt", 0.0 - taxtranamt);
							hdrmap.put("cr_amt",0.0);
							System.out.println("taxtranamt>>>>>>>"+taxtranamt);
						    }
			    	    hdrmap.put("acct_code",acctCodeDet);
						hdrmap.put("cctr_code",cctrCodeDet);
						hdrmap.put("dept_code",deptCodeDet);
						hdrmap.put("emp_code",empCodeDet);
						hdrmap.put("anal_code",analCodeDet);
						hdrmap.put("analysis1",analysis1);
						hdrmap.put("analysis2",analysis2);
						hdrmap.put("analysis3",analysis3);
						hdrmap.put("curr_code",currcode );
						hdrmap.put("exch_rate",exch_h);
						hdrmap.put("ref_type","F");
						hdrmap.put("ref_ser","VOUCH");
						hdrmap.put("ref_id",ref_id);
						hdrmap.put("remarks",remarks);
						hdrmap.put("proj_code",proj_code);

						/* party_doc_ref = finCommon.gfGetPartyDocRef("VOUCH",ref_id,conn);
						hdrmap.put("party_doc_ref",party_doc_ref);
						*/
						System.out.println("calling Gltrace Update for taxtranamt*******************");
						
						errString = finCommon.glTraceUpdate(hdrmap,conn,true); 
						System.out.println("ERROR STRING>>>>>>>>>>>>>>>>>>>>>>["+errString+"]");
						if(errString != null && errString.trim().length() > 0)
						{
							return errString;
						}
						
			      }
			      
					}
					
					rs1.close();
					rs1=null;
					pstmt1.close();
					pstmt1=null;
			      
			      //DI7SUN0018-To reverse the inventory entry for items whose qc ord is not made*********************PROVISIONAL
			/*   String porcpOld = "",porcp = "",post_prov = "", transer = "",tran_id__ref = "",itemCd = "",lineNoRcp = "",lineno1 = "",porcp1 = "";
			      int cnt = 0,qcordcnt = 0;
			      Double amt = 0.0;
		    	  String acctcodedr = "",cctrcodedr = "" ,acctcodeprov = "",cctrcodeprov = "";
			      ArrayList porcplst = new ArrayList();
			      
			      sql = "select count(*) as cnt from vouchrcp where tran_id = ? ";
			      pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,tranID);
					rs = pstmt.executeQuery();
					while(rs.next())
					{
						cnt = rs.getInt("cnt");
					}
					rs.close();
					rs=null;
					pstmt.close();
					pstmt=null;
			      
				for(int i = 0;i <= cnt;i++ )
				{
					sql = "select * from vouchrcp where tran_id= ?";
				      pstmt = conn.prepareStatement(sql);
						pstmt.setString(1,finentity);
						rs = pstmt.executeQuery();
						while(rs.next())
						{
							porcp = rs.getString("prcp_id");
							lineNoRcp = rs.getString("line_no__rcp");
						}
						rs.close();
						rs=null;
						pstmt.close();
						pstmt=null;
				      
					 if(porcpOld != porcp )
				      {
				    	  porcpOld =  porcp;
				    	  post_prov = "";
				    	  transer = "";
				    	  tran_id__ref = "";
				      
				      
				      sql  = "select post_prov , tran_ser, tran_id__ref,item_code  from	porcp where tran_id = ?";
				      pstmt = conn.prepareStatement(sql);
						pstmt.setString(1,finentity);
						rs = pstmt.executeQuery();
						while(rs.next())
						{
							post_prov = rs.getString("post_prov");
							transer = rs.getString("tran_ser");
							tran_id__ref = rs.getString("tran_id__ref");
							itemCd  = rs.getString("item_code");
						}
						rs.close();
						rs=null;
						pstmt.close();
						pstmt=null;
						
						if(post_prov == null || post_prov.trim().length() == 0 )
						{
							post_prov = "N"	;
						}
						if(post_prov == "N" ){//DOUBT
							  porcpOld =  porcp;
								continue;
						}
						porcpOld =  porcp;
						lineNoRcp = "    " + lineNoRcp;
						lineNoRcp = lineNoRcp.substring(lineNoRcp.length() - 3,lineNoRcp.length());
						lineno1 = "";
						porcp1 = "";
						if(transer == "P-RET") // IF Purch return then consider reference tranid & lineno for checking if qc ord exist's
						{
							sql  = "select line_no__ord as lineno1 from porcpdet where	tran_id = ? and	line_no = ?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1,porcp);
							pstmt.setString(2,lineNoRcp);
							rs = pstmt.executeQuery();
							while(rs.next())
							{
								lineno1 = rs.getString("lineno1");
							}
							rs.close();
							rs=null;
							pstmt.close();
							pstmt=null;
							porcp1 = tran_id__ref;
						}
						else{
							porcp1 = porcp;
							lineno1 = lineNoRcp;
						}
						lineno1 = "    " + lineno1;
						lineno1 = lineno1.substring(lineno1.length() - 3,lineno1.length());
						
						sql ="select count(1) as qcord_cnt from	qc_order where porcp_no = ? and	porcp_line_no = ? and	item_code = ?";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1,porcp1);
						pstmt.setString(2,lineno1);
						pstmt.setString(3,itemCd);
						rs = pstmt.executeQuery();
						while(rs.next())
						{
							qcordcnt = rs.getInt("qcord_cnt");
						}
						rs.close();
						rs=null;
						pstmt.close();
						pstmt=null;
						
						if(qcordcnt > 0)
						{
							continue;
						}
						else{
							sql ="select acct_code__dr as acctcode, cctr_code__dr as cctrcode, acct_code__prov_dr as prov_acct, cctr_code__prov_dr as prov_cctr from porcpdet"
		                        +"where tran_id = ? and line_no = ? ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1,porcp1);
							pstmt.setString(2,lineno1);
							
							rs = pstmt.executeQuery();
							while(rs.next())
							{
								acctcodedr = rs.getString("acctcode");
								cctrcodedr = rs.getString("cctrcode");
								acctcodeprov = rs.getString("prov_acct");
								cctrcodeprov = rs.getString("prov_cctr");
							}
							rs.close();
							rs=null;
							pstmt.close();
							pstmt=null;
							
							amount = vouchAmt;
							cnt = porcplst.size();
							boolean foundRow;
							if(cnt > 0)
							{
								foundRow = porcplst.contains("A");
							}
							
							if(cnt == 0)
							{
								porcplst.add(1,"A");
								porcplst.add(2,acctcodedr);//acctcode
								porcplst.add(3,cctrcodedr);//cctrcode
								porcplst.add(4,amount);//amount
							}
							
							
						}
						
						
			      if(errString == null && errString.trim().length() == 0)
					{
			    	    hdrmap.put("tran_date",tranDate);
						hdrmap.put("eff_date", effDate);
						hdrmap.put("fin_entity",finentity);
						hdrmap.put("site_code",sitecode);
						hdrmap.put("sundry_type",'O');
						hdrmap.put("sundry_code","");
						hdrmap.put("emp_code","");
						hdrmap.put("anal_code","");
						hdrmap.put("curr_code",basiccurr);
						hdrmap.put("exch_rate",1);
						hdrmap.put("ref_type","D");
						hdrmap.put("ref_ser","VOUCH");
						hdrmap.put("ref_id",ref_id);
						hdrmap.put("remarks","Voucher : "+ref_id+"Dt."+tranDate);
						
						for(int j = 0;j<porcplst.size() ;j++)
						{
							hdrmap.put("acct_code",porcplst.get(2));
							hdrmap.put("cctr_code",porcplst.get(3));
							amt = Double.parseDouble(porcplst.get(4).toString());
							System.out.println("AMOUNT>>>>>>"+amt);
							if(amt != 0.0)
							{
								if(porcplst.get(1).equals("A"))
								{
									hdrmap.put("dr_amt",amt);
									hdrmap.put("cr_amt",0.0);
								}
							}
						}
					}*/
		}
		catch( Exception e)
		{
			if(conn!=null)
			{
				try {
					conn.rollback();
				} catch (SQLException ex) {

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

				}
			}
			System.out.println("Exception : "+e);
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			
				try
				{
					
					if(errString != null && errString.trim().length() > 0)
					{
						if( conn != null && !conn.isClosed() && connStatus ) 
						{
							if(errString.indexOf("VTCICONF3") > -1)
							{
								conn.commit();
							}
							else
							{
								conn.rollback();
							}
							conn.close();
							conn = null;
						}
					}
					if(rs != null)
					{
						rs.close();
						rs = null;
					}
					if(pstmt != null)
					{
						pstmt.close();
						pstmt = null;
					}
					//conn.close();
				}
				catch(Exception e)
				{
					System.out.println("Exception : "+e);e.printStackTrace();
					throw new ITMException(e);
				}
			
		}
     
	    return retString;
	}
}
/*
 * SELECT VOUCHRCP.TRAN_ID,VOUCHRCP.LINE_NO,VOUCHRCP.PRCP_ID,VOUCHRCP.LINE_NO__RCP,VOUCHRCP.ITEM_CODE,VOUCHRCP.UNIT,VOUCHRCP.RCP_QTY,VOUCHRCP.VOUCH_QTY,VOUCHRCP.RCP_RATE,VOUCHRCP.VOUCH_RATE,VOUCHRCP.PURC_ORDER,VOUCHRCP.LINE_NO__PORD,VOUCHRCP.VOUCH_AMT,VOUCHRCP.TAX_CLASS,VOUCHRCP.TAX_CHAP,VOUCHRCP.TAX_ENV,VOUCHRCP.DISCOUNT,VOUCHRCP.DISC_AMT,VOUCHRCP.TAX_AMT,VOUCHRCP.NET_AMT,VOUCHRCP.RCP_AMT,VOUCHRCP.POST_DIFF,VOUCHRCP.CHG_DATE,VOUCHRCP.CHG_USER,VOUCHRCP.CHG_TERM,VOUCHRCP.ITEM_SER,ITEM.DESCR,VOUCHRCP.ACCT_CODE__DIFF,VOUCHRCP.CCTR_CODE__DIFF,VOUCHRCP.VOUCH_QTY__STDUOM,VOUCHRCP.VOUCH_RATE__STDUOM,VOUCHRCP.ACCT_CODE,VOUCHRCP.CCTR_CODE,ACCOUNTS.DESCR,VOUCHRCP.TAX_ADV_AMT,VOUCHRCP.RATE__CLG,VOUCHRCP.STD_RATE,VOUCHRCP.DEPT_CODE,VOUCHRCP.ANAL_CODE,ANALYSIS.DESCR,COSTCTR.DESCR,VOUCHRCP.PROJ_CODE,VOUCHRCP.ANALYSIS1,VOUCHRCP.ANALYSIS2,VOUCHRCP.ANALYSIS3,FN_GET_BUDGET_AMT('VOUCH',"VOUCHER"."SITE_CODE","VOUCHRCP"."ACCT_CODE","VOUCHRCP"."CCTR_CODE",VOUCHRCP.ANAL_CODE ,"VOUCHRCP"."DEPT_CODE",'A') BUDGET_AMT_ANAL,FN_GET_CONS_AMT('VOUCH',"VOUCHER"."SITE_CODE","VOUCHRCP"."ACCT_CODE","VOUCHRCP"."CCTR_CODE",VOUCHRCP.ANAL_CODE,"VOUCHRCP"."DEPT_CODE",'A') CONSUMED_AMT_ANAL,(CASE WHEN (FN_GET_CONS_AMT('VOUCH',VOUCHER.SITE_CODE,VOUCHRCP.ACCT_CODE,VOUCHRCP.CCTR_CODE,VOUCHRCP.ANAL_CODE,VOUCHRCP.DEPT_CODE,'A')) > 0 THEN (FN_GET_BUDGET_AMT('VOUCH',VOUCHER.SITE_CODE,VOUCHRCP.ACCT_CODE,VOUCHRCP.CCTR_CODE,VOUCHRCP.ANAL_CODE ,VOUCHRCP.DEPT_CODE,'A') - FN_GET_CONS_AMT('VOUCH',VOUCHER.SITE_CODE,VOUCHRCP.ACCT_CODE,VOUCHRCP.CCTR_CODE,VOUCHRCP.ANAL_CODE,VOUCHRCP.DEPT_CODE,'A')) ELSE 0 END)  BUDGET_AMT FROM VOUCHRCP  VOUCHRCP,ITEM  ITEM,ACCOUNTS  ACCOUNTS,ANALYSIS  ANALYSIS,COSTCTR  COSTCTR,VOUCHER  VOUCHER WHERE ( VOUCHRCP.ITEM_CODE      = ITEM.ITEM_CODE   ) AND ( VOUCHER.TRAN_ID      = VOUCHRCP.TRAN_ID   ) AND ( VOUCHRCP.ACCT_CODE=ACCOUNTS.ACCT_CODE(+)) AND ( VOUCHRCP.ANAL_CODE=ANALYSIS.ANAL_CODE(+)) AND ( VOUCHRCP.CCTR_CODE=COSTCTR.CCTR_CODE(+)) AND VOUCHRCP.TRAN_ID    =      ?*/
