package ibase.webitm.ejb.dis;

import java.rmi.RemoteException;
//import java.util.logging.Level;
//import java.util.logging.Logger;
import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.*;
//import ibase.webitm.ejb.dis_exp.PurcOrderAmdTWFInvokeConfirm;
import ibase.webitm.ejb.sys.CreateRCPXML;
import org.w3c.dom.*;
import java.sql.*;
import java.text.SimpleDateFormat;

import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;
import javax.ejb.Stateless;
import java.util.Calendar;
//import javax.swing.text.Document;
import java.util.Date;

@Stateless
public class POrderAmdConf extends ActionHandlerEJB implements
POrderAmdConfLocal, POrderAmdConfRemote
{

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

	public String actionHandler(String actionType, String xmlString,
			String objContext, String xtraParams) throws RemoteException,
			ITMException 
			{
		System.out.println("item actionHandler(...) called............");
		String str = "";
		System.out.println("actionType---" + actionType);
		System.out.println("xmlString---" + xmlString);
		System.out.println("objContext---" + objContext);
		System.out.println("xtraParams---" + xtraParams);
		return str;
			}

	public String confirm(String tranId, String xtraParams, String forcedFlag)
			throws RemoteException, ITMException
	{
		System.out.println("POrderAmdConf confirm called..............");
		String confirmed = "";
		String sql = "";
		//String sql1 = "";
		//int status = 0;
		//double frtAmt = 0.0;
		//Date lrDate = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		//PreparedStatement pstmt1 = null;
		ITMDBAccessEJB itmDBAccessEJB = null;
		String errString = null,errCode="";
		ResultSet rs = null;
		//ResultSet rs1 = null;
		String purcOrder = "", poStatus = "", amdNo = "", workflowStatus = "";
		int recCnt = 0, cnt = 0;
		ITMDBAccessEJB itmDBAccessLocal = new ITMDBAccessEJB();
		try 
		{
			itmDBAccessEJB = new ITMDBAccessEJB();
			ConnDriver connDriver = null;
			connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");
			conn.setAutoCommit(false);

			if (errString == null || errString.trim().length() == 0)
			{
				amdNo = tranId;
				if (tranId != null && tranId.trim().length() > 0) 
				{
					purcOrder = setDescription("purc_order", "poamd_hdr","amd_no", tranId, conn);

					if (purcOrder != null && purcOrder.trim().length() > 0)
					{
						poStatus = setDescription("status", "porder","purc_order", purcOrder, conn);

						if ("C".equalsIgnoreCase(poStatus) || "X".equalsIgnoreCase(poStatus) )
						{
							errString = "VTPOCX";
							errString = itmDBAccessLocal.getErrorString("",errString,"");
							return errString;
						}

						sql = "	select confirmed, (case when workflow_status is null then '0' else workflow_status end) "
								+ " from   poamd_hdr where amd_no = ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, amdNo);
						rs = pstmt.executeQuery();
						if (rs.next()) 
						{
							recCnt++;
							confirmed = rs.getString(1);
							workflowStatus = rs.getString(2);
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;

						if (recCnt == 0) 
						{
							errString = "VTMCONF20";
							errString = itmDBAccessLocal.getErrorString("",errString,"");
							return errString;

						} 
						else if ("Y".equalsIgnoreCase(confirmed)) 
						{
							errString = "VTPACONF1";
							errString = itmDBAccessLocal.getErrorString("",errString,"");
							return errString;
						}

						sql = " select count(1) from poamd_det where amd_no = ? and purc_order <> ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, amdNo);
						pstmt.setString(2, purcOrder);
						rs = pstmt.executeQuery();
						if (rs.next())
						{
							cnt = rs.getInt(1);
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						System.out.println("@@@@@@@@111 cnt["+cnt+"]");
						if (cnt > 0)
						{
							errString = "VTPOHRDT";
							errString = itmDBAccessLocal.getErrorString("",errString,"");
							return errString;
						}

						sql = " select count(1) from   poamd_term where amd_no = ? and purc_order <> ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, amdNo);
						pstmt.setString(2, purcOrder);
						rs = pstmt.executeQuery();
						if (rs.next()) 
						{
							cnt = rs.getInt(1);
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						System.out.println("@@@@@@@@222 cnt["+cnt+"]");
						if (cnt > 0) 
						{
							errString = "VTPOHRDT";
							errString = itmDBAccessLocal.getErrorString("",errString,"");
							return errString;
						}

						if (errString == null || errString.trim().length() == 0) 
						{
							errString = RetrievePordAmd(amdNo, xtraParams,conn);
						}
					}
				}
				if (errString == null || errString.trim().length() == 0) 
				{
					errString = "PRCUSUCCES";
					errString = itmDBAccessLocal.getErrorString("",errString,"");
					return errString;
				}

			} // end if errstrng
		} catch (Exception e) 
		{
			e.printStackTrace();
			throw new ITMException(e);
		} 
		finally 
		{
			try 
			{
				if (errString != null && errString.trim().length() > 0	&& !("PRCUSUCCES".equalsIgnoreCase(errString))) 
				{
					conn.rollback();
					System.out.println("Transaction rollback... ");
					conn.close();
					conn = null;
				}
				else 
				{
					conn.commit(); // test
					System.out.println("@@@@ Transaction commit... ");
					conn.close();
					conn = null;
				}
			}
			catch (Exception e) 
			{
				e.printStackTrace();
				throw new ITMException(e);
			}
		}
		System.out.println("errString["+errString+"]:::::::::::errCode["+errCode+"]");
		return errString;
	}

	private String RetrievePordAmd(String amdNo, String xtraParams,
			Connection conn) throws RemoteException, ITMException
			{

		PreparedStatement pstmt = null, pstmt1 = null, pstmtSql = null;
		ResultSet rs = null, rs1 = null;
		int cnt = 0;
		String retString = "", sql = "", sql1 = "", purcOrder = "", lineNoOrd = "", errCode = "";
		String loginEmpCode = "", ediOption = "", dataStr = "";
		double vouchAdv = 0, amdDetTot = 0, poDetTot = 0, poHdrTot = 0;
		Timestamp sysDate = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		ITMDBAccessEJB itmDBAccessLocal = new ITMDBAccessEJB();
		try {
			System.out
			.println("@@@@@@@@@@@@@@@ RetrievePordAmd confirm called..............");

			loginEmpCode =  genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginEmpCode");
			Calendar currentDate = Calendar.getInstance();
			SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
			String sysDateStr = sdf.format(currentDate.getTime());
			System.out.println("Now the date is :=>  " + sysDateStr+":::loginEmpCode["+loginEmpCode+"]");
			sysDate= Timestamp.valueOf(genericUtility.getValidDateString(sysDateStr, genericUtility.getApplDateFormat(),genericUtility.getDBDateFormat()) + " 00:00:00.0");
			

			sql = " select a.tot_amt, case when a.vouch_adv_amt is null then 0 else a.vouch_adv_amt end "
					+ " from porder a, poamd_hdr b "
					+ " where a.purc_order = b.purc_order "
					+ " and b.amd_no = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, amdNo);

			rs = pstmt.executeQuery();
			if (rs.next()) {
				poHdrTot = rs.getDouble(1);
				vouchAdv = rs.getDouble(2);
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;

			if (vouchAdv > 0) // lc_vouchadv > 0 then
			{
				sql = " select purc_order,line_no__ord, tot_amt from poamd_det where amd_no = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, amdNo);
				rs = pstmt.executeQuery();
				while (rs.next()) 
				{
					purcOrder = rs.getString("purc_order");
					lineNoOrd = rs.getString("line_no__ord");
					amdDetTot = rs.getDouble("tot_amt");

					if (lineNoOrd != null && lineNoOrd.trim().length() > 0 && purcOrder != null && purcOrder.trim().length() > 0)
					{
						sql1 = " select tot_amt from porddet where purc_order = ? and line_no = ?  ";
						pstmt1 = conn.prepareStatement(sql1);
						pstmt1.setString(1, purcOrder);
						pstmt1.setString(2, lineNoOrd);
						rs1 = pstmt1.executeQuery();
						if (rs1.next())
						{
							poDetTot = rs1.getDouble("tot_amt");
						}
						rs1.close();
						rs1 = null;
						pstmt1.close();
						pstmt1 = null;
					}
					poHdrTot = poHdrTot - poDetTot + amdDetTot;
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;

				System.out.println("@@@@@@@@@ vouchAdv[" + vouchAdv+ "]:::::poHdrTot[" + poHdrTot + "]");

				if (vouchAdv > poHdrTot)
				{
					errCode = "VTADVEXCES";
					errCode = itmDBAccessLocal.getErrorString("",errCode,"");
					return errCode;
				}
			}

			errCode = ConfirmPordAmd(amdNo, conn);

			if (errCode != null && errCode.trim().length() > 0) 
			{
				conn.rollback();
			}
			else
			{
				sql = "select edi_option from transetup where tran_window = 'w_porderamd' ";
				pstmtSql = conn.prepareStatement(sql);
				rs = pstmtSql.executeQuery();
				if (rs.next()) {
					ediOption = rs.getString("EDI_OPTION");
				}
				rs.close();
				rs = null;
				pstmtSql.close();
				pstmtSql = null;
				System.out.println("@@@@@@@@@@@@@@@ ediOption  called next..............");
				if ("2".equals(ediOption)) 
				{
					CreateRCPXML createRCPXML = new CreateRCPXML("w_porderamd","tran_id");
					dataStr = createRCPXML.getTranXML(amdNo, conn);
					System.out.println("dataStr =[ " + dataStr + "]");
					Document ediDataDom = genericUtility.parseString(dataStr);

					E12CreateBatchLoadEjb e12CreateBatchLoad = new E12CreateBatchLoadEjb();
					retString = e12CreateBatchLoad.createBatchLoad(ediDataDom,
							"w_porderamd", "2", xtraParams, conn);
					createRCPXML = null;
					e12CreateBatchLoad = null;

					if (retString != null && "SUCCESS".equals(retString)) 
					{
						System.out.println("retString from batchload = ["
								+ retString + "]");
					}
				}

				else {

					CreateRCPXML createRCPXML = new CreateRCPXML("w_porderamd",
							"tran_id");
					dataStr = createRCPXML.getTranXML(amdNo, conn);
					System.out.println("dataStr =[ " + dataStr + "]");
					Document ediDataDom = genericUtility.parseString(dataStr);

					E12CreateBatchLoadEjb e12CreateBatchLoad = new E12CreateBatchLoadEjb();
					retString = e12CreateBatchLoad.createBatchLoad(ediDataDom,"w_porderamd", ediOption, xtraParams, conn);
					createRCPXML = null;
					e12CreateBatchLoad = null;

					if (retString != null && "SUCCESS".equals(retString)) 
					{
						System.out.println("retString from batchload = ["+ retString + "]");
					}
				}
				System.out
				.println("@@@@@@@@@@@@@@@ ediOption  called end..............");

			}

			if (errCode == null || errCode.trim().length() == 0) 
			{
				sql = " update poamd_hdr set confirmed = 'Y',conf_date = ?, emp_code__aprv = ? where amd_no = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setTimestamp(1, sysDate);
				pstmt.setString(2, loginEmpCode);
				pstmt.setString(3, amdNo);
				cnt = pstmt.executeUpdate();
				pstmt.close();
				pstmt = null;
				System.out.println("@@@@@@ cnt...[" + cnt + "]");
				if (!(cnt == 1)) 
				{
					errCode = "VTNOAMD";
					errCode = itmDBAccessLocal.getErrorString("",errCode,"");
					return errCode;
				}
			}

			if (errCode != null && errCode.trim().length() > 0) 
			{
				conn.rollback();
			} else 
			{
				conn.commit();
			}
		}
		catch (Exception e) 
		{
			System.out.println("Exception :conf ::" + e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}

		return errCode;
		}

	private String ConfirmPordAmd(String amdNo, Connection conn)
			throws RemoteException, ITMException

	{
		PreparedStatement pstmt = null, pstmt1 = null, pstmt3 = null,pstmtSql = null;
		ResultSet rs = null, rs1 = null,rs3 = null;
		int cnt = 0;
		// Document dom = null;
		String retString = "", sql = "", sql1 = "", sql3 = "",purcOrder = "", lineNoOrd = "", errCode = "";
		//String loginEmpCode = "", ediOption = "", dataStr = "";
		//double pohdrTot = 0, vouchAdv = 0, amdDetTot = 0, poDetTot = 0, poHdrTot = 0;
		Timestamp sysDate = null, taxDate = null, refDate = null, amdDate = null;
		String siteCodeDlv = "", siteCodeOrd = "", siteCodeBill = "", deptCode = "", empCode = "", itemSer = "", taxOpt = "", crTerm = "";
		String currCode = "", taxChapHdr = "", taxClassHdr = "", taxEnvHdr = "", remarks = "", projCode = "", salesPers = "", commPerc = "", commPercOn = "";
		String currCodeComm = "", quotNo = "", tranCode = "", currCodeFrt = "", frtTerm = "", dlvTerm = "", currCodeIns = "", empCodeAprv = "", suppCode = "";
		double ordAmt = 0, taxAmt = 0, totAmt = 0, exchRate = 0, frtAmt = 0, insuranceAmt = 0;
		String siteCode = "", indNo = "", itemCode = "", lineNo = "", discountType = "", taxClass = "";
		double quantity = 0, rate = 0, convQtyStduom = 0, convRtuomStduom = 0, quantityStduom = 0, rateStduom = 0;
		double discount = 0, rateClg = 0;
		String UnitStd = "", unitRate = "";
		Timestamp reqDate = null, dlvDate = null;
		String taxChap = "", taxEnv = "", workOrder = "", packCode = "", packInstr = "", acctCodeDr = "", cctrCodeDr = "";
		String acctCodeCr = "", cctrCodeCr = "";
		double noArt = 0;
		String locCode = "", status = "", specificInstr = "", suppCodeMnfr = "", unit = "";
		String specialInstr = "", benefitType = "", licenceNo = "", dutyPaid = "", formNo = "", lineNoPO = "";

		String lineNoTax = "", taxCode = "", taxBase = "", taxPerc = "", chgStat = "", taxSet = "", effect = "", acctCodeReco = "";
		String cctrCodeReco = "", recoPerc = "", acctCode = "", cctrCode = "", rateType = "", round = "", roundTo = "", taxForm = "";
		String chgUser = "", chgTerm = "", posted = "", payTax = "", orderOpt = "", bom = "", oldItem = "", lastTermLine = "";
		double taxableAmt = 0, recoAmont = 0, dlvQuantity = 0, clgRate = 0, ordQty = 0, totOrdQty = 0, quantStduom = 0;
		Timestamp taxFormDate = null, chgDate = null;
		int updCnt = 0;
		double taxAmtS = 0, totAmtS = 0, ordAmtS = 0;
		double vouchAmt = 0;
		String advance = "";
		String frtType = "";
		double frtRate = 0, frtAmtQty = 0, frtAmtFixed = 0;
		GenericUtility genericUtility = GenericUtility.getInstance();
		ITMDBAccessEJB itmDBAccessLocal = new ITMDBAccessEJB();
		String termCode = "", descr = "", printOpt = "";
		try 
		{
			System.out.println("@@@@@@@@@@@@@@@ ConfirmPordAmd method called next..............");
			Calendar currentDate = Calendar.getInstance();
			SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
			String sysDateStr = sdf.format(currentDate.getTime());
			System.out.println("Now the date is :=>  " + sysDateStr);
			sysDate= Timestamp.valueOf(genericUtility.getValidDateString(sysDateStr, genericUtility.getApplDateFormat(),genericUtility.getDBDateFormat()) + " 00:00:00.0");

			sql = " select purc_order,site_code__dlv,site_code__ord,site_code__bill,dept_code,emp_code,item_ser,tax_opt,"
					+ "	cr_term,ord_amt,tax_amt,tot_amt,curr_code,exch_rate,tax_chap,tax_class,tax_env,remarks,tax_date,"
					+ " proj_code,sales_pers,comm_perc,comm_perc__on,curr_code__comm,quot_no,tran_code,frt_amt,curr_code__frt,"
					+ " frt_term,dlv_term,insurance_amt,curr_code__ins,emp_code__aprv,ref_date,amd_date,supp_code,"
					+ "  FRT_TYPE, FRT_RATE,FRT_AMT__QTY, FRT_AMT__FIXED "
					+ " from poamd_hdr where amd_no = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, amdNo);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				cnt++;
				purcOrder = rs.getString("purc_order");
				siteCodeDlv = rs.getString("site_code__dlv");
				siteCodeOrd = rs.getString("site_code__ord");
				siteCodeBill = rs.getString("site_code__bill");
				deptCode = rs.getString("dept_code");
				empCode = rs.getString("emp_code");
				itemSer = rs.getString("item_ser");
				taxOpt = rs.getString("tax_opt");
				crTerm = rs.getString("cr_term");
				ordAmt = rs.getDouble("ord_amt");
				taxAmt = rs.getDouble("tax_amt");
				totAmt = rs.getDouble("tot_amt");
				currCode = rs.getString("curr_code");
				exchRate = rs.getDouble("exch_rate");
				taxChapHdr = rs.getString("tax_chap");
				taxClassHdr = rs.getString("tax_class");
				taxEnvHdr = rs.getString("tax_env");
				remarks = rs.getString("remarks");
				taxDate = rs.getTimestamp("tax_date");
				projCode = rs.getString("proj_code");
				salesPers = rs.getString("sales_pers");
				commPerc = rs.getString("comm_perc");
				commPercOn = rs.getString("comm_perc__on");
				currCodeComm = rs.getString("curr_code__comm");
				quotNo = rs.getString("quot_no");
				tranCode = rs.getString("tran_code");
				frtAmt = rs.getDouble("frt_amt");
				currCodeFrt = rs.getString("curr_code__frt");
				frtTerm = rs.getString("frt_term");
				dlvTerm = rs.getString("dlv_term");
				insuranceAmt = rs.getDouble("insurance_amt");
				currCodeIns = rs.getString("curr_code__ins");
				empCodeAprv = rs.getString("emp_code__aprv");
				refDate = rs.getTimestamp("ref_date");
				amdDate = rs.getTimestamp("amd_date");
				suppCode = rs.getString("supp_code");
				// added by cpatil
				frtType = rs.getString("FRT_TYPE");
				frtRate = rs.getDouble("FRT_RATE");
				frtAmtQty = rs.getDouble("FRT_AMT__QTY");
				frtAmtFixed = rs.getDouble("FRT_AMT__FIXED");
				// end

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

			if (cnt == 0) 
			{
				errCode = "VTNOAMD";
				errCode = itmDBAccessLocal.getErrorString("",errCode,"");
				return errCode;
			}

			sql = " select count(*) from poamd_det where amd_no = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, amdNo);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				cnt = rs.getInt(1);

			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			System.out.println("@@@@@@ inside cnt " + cnt);
			if (cnt > 0)
			{
				System.out.println("@@@@@@ inside cnt " + cnt);

				sql1 = " select purc_order,line_no__ord,site_code,ind_no,item_code,quantity,rate,tax_amt,tot_amt,req_date,"
						+ "	conv__qty_stduom,conv__rtuom_stduom,quantity__stduom,rate__stduom,line_no,unit,discount,"
						+ " discount_type,tax_class,tax_chap,tax_env,remarks,work_order,pack_code,pack_instr,acct_code__dr,"
						+ "	cctr_code__dr,acct_code__cr,cctr_code__cr,no_art,unit__std,unit__rate,dlv_date,loc_code,status,"
						+ "	specific_instr,supp_code_mnfr,rate__clg,special_instr,benefit_type,licence_no,duty_paid,form_no"
						+ " from   poamd_det where  amd_no = ? ";
				pstmt1 = conn.prepareStatement(sql1);
				pstmt1.setString(1, amdNo);
				rs1 = pstmt1.executeQuery();
				while (rs1.next())
				{
					System.out.println("@@@@@@ inside while ");
					purcOrder = checknull(rs1.getString("purc_order"));
					lineNoOrd = checknull(rs1.getString("line_no__ord"));
					siteCode = checknull(rs1.getString("site_code"));
					indNo = checknull(rs1.getString("ind_no"));
					itemCode = checknull(rs1.getString("item_code"));
					quantity = rs1.getDouble("quantity");
					rate = rs1.getDouble("rate");
					taxAmt = rs1.getDouble("tax_amt");
					totAmt = rs1.getDouble("tot_amt");
					reqDate = rs1.getTimestamp("req_date");
					convQtyStduom = rs1.getDouble("conv__qty_stduom");
					convRtuomStduom = rs1.getDouble("conv__rtuom_stduom");
					quantityStduom = rs1.getDouble("quantity__stduom");
					rateStduom = rs1.getDouble("rate__stduom");
					lineNo = checknull(rs1.getString("line_no"));
					unit = rs1.getString("unit");
					discount = rs1.getDouble("discount");
					discountType = checknull(rs1.getString("discount_type"));
					taxClass = checknull(rs1.getString("tax_class"));
					taxChap = checknull(rs1.getString("tax_chap"));
					taxEnv = checknull(rs1.getString("tax_env"));
					remarks = checknull(rs1.getString("remarks"));
					workOrder = checknull(rs1.getString("work_order"));
					packCode = checknull(rs1.getString("pack_code"));
					packInstr = checknull(rs1.getString("pack_instr"));
					acctCodeDr = checknull(rs1.getString("acct_code__dr"));
					cctrCodeDr = checknull(rs1.getString("cctr_code__dr"));
					acctCodeCr = checknull(rs1.getString("acct_code__cr"));
					cctrCodeCr = checknull(rs1.getString("cctr_code__cr"));
					noArt = rs1.getDouble("no_art");
					UnitStd = rs1.getString("unit__std");
					unitRate = rs1.getString("unit__rate");
					dlvDate = rs1.getTimestamp("dlv_date");
					locCode = checknull(rs1.getString("loc_code"));
					status = checknull(rs1.getString("status"));
					specificInstr = checknull(rs1.getString("specific_instr"));
					suppCodeMnfr = checknull(rs1.getString("supp_code_mnfr"));
					rateClg = rs1.getDouble("rate__clg");
					specialInstr = checknull(rs1.getString("special_instr"));
					benefitType = checknull(rs1.getString("benefit_type"));
					licenceNo = checknull(rs1.getString("licence_no"));
					dutyPaid = checknull(rs1.getString("duty_paid"));
					formNo = checknull(rs1.getString("form_no"));

					System.out.println("@@@@@@@@ indNo [" + indNo + "]");
					if (indNo != null && indNo.trim().length() > 0) 
					{
						sql = " select quantity__stduom,ord_qty from indent where  ind_no = ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, amdNo);
						rs = pstmt.executeQuery();
						if (rs.next()) {
							quantity = rs.getDouble("quantity__stduom");
							ordQty = rs.getDouble("ord_qty");
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;

						// adding ord qty from table + ord qty entered
						sql = "	select quantity__stduom from porddet where purc_order = ? and line_no = ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, purcOrder);
						pstmt.setString(2, lineNoOrd);
						rs = pstmt.executeQuery();
						if (rs.next()) {
							quantStduom = rs.getDouble("quantity__stduom");
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;

						totOrdQty = (ordQty - quantStduom) + quantityStduom;

						if (quantity > totOrdQty)
						{
							sql = " update indent set status = 'O', status_date = ? , ord_qty = ?,"
									+ " unit__ord   = ?  where  ind_no  = ? ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setTimestamp(1, sysDate);
							pstmt.setDouble(2, totOrdQty);
							pstmt.setString(3, UnitStd);
							pstmt.setString(4, indNo);
							updCnt = pstmt.executeUpdate();
							pstmt.close();
							pstmt = null;

						} 
						else // status= 'L' (complete)
						{
							sql = " update indent set status = 'L', status_date = ? , ord_qty = ? ,"
									+ " unit__ord = ? where  ind_no = ? ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setTimestamp(1, sysDate);
							pstmt.setDouble(2, totOrdQty);
							pstmt.setString(3, UnitStd);
							pstmt.setString(4, indNo);
							updCnt = pstmt.executeUpdate();
							pstmt.close();
							pstmt = null;

						}
						if (updCnt == 0) 
						{
							errCode = "VTINDUPD";
							errCode = itmDBAccessLocal.getErrorString("",errCode,"");
							return errCode;
						}
					}

					System.out.println("@@@@@@ lineNoOrd indNo [" + indNo
							+ "]:::lineNoOrd[" + lineNoOrd + "]");

					if (lineNoOrd != null && lineNoOrd.trim().length() > 0) 
					{
						System.out.println("@@@@@@ inside indNo [" + indNo
								+ "]:::lineNoOrd[" + lineNoOrd + "]");

						sql3 = " select item_code from  porddet where purc_order = ? and  line_no = ? ";
						pstmt3 = conn.prepareStatement(sql3);
						pstmt3.setString(1, purcOrder);
						pstmt3.setString(2, lineNoOrd);
						rs3 = pstmt3.executeQuery();
						if (rs3.next()) 
						{
							oldItem = rs3.getString("item_code");
							
							{
								System.out.println("@@@@@@@@@@oldItem["+oldItem+"]::::itemCode["+itemCode+"]");
								if (!( itemCode.equalsIgnoreCase(oldItem) ))
								{
									errCode = "VTITEMDIFF";
									errCode = itmDBAccessLocal.getErrorString("",errCode,"");
									return errCode;
								}
								sql = " update porddet set site_code = ?,quantity = ?,rate = ?,tax_amt = ?,tot_amt = ?,"
										+ " remarks = ?, discount_type = ?,discount = ?,req_date = ?,conv__qty_stduom = ?,"
										+ " conv__rtuom_stduom = ?,quantity__stduom = ?,rate__stduom = ?,tax_class = ?,"
										+ " tax_chap = ?, tax_env= ?,status = ?,status_date = ?, pack_instr = ?, specific_instr= ?,"
										+ " supp_code__mnfr = ?,rate__clg = ?,special_instr = ?,dlv_date = ?,benefit_type = ?,"
										+ " licence_no	= ? where purc_order = ?	and line_no = ? ";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, siteCode);
								pstmt.setDouble(2, quantity);
								pstmt.setDouble(3, rate);
								pstmt.setDouble(4, taxAmt);
								pstmt.setDouble(5, totAmt);
								pstmt.setString(6, remarks);
								pstmt.setString(7, discountType);
								pstmt.setDouble(8, discount);
								pstmt.setTimestamp(9, reqDate);
								pstmt.setDouble(10, convQtyStduom);
								pstmt.setDouble(11, convRtuomStduom);
								pstmt.setDouble(12, quantityStduom);
								pstmt.setDouble(13, rateStduom);
								pstmt.setString(14, taxClass);
								pstmt.setString(15, taxChap);
								pstmt.setString(16, taxEnv);
								pstmt.setString(17, status);
								pstmt.setTimestamp(18, sysDate);
								pstmt.setString(19, packInstr);
								pstmt.setString(20, specificInstr);
								pstmt.setString(21, suppCodeMnfr);
								pstmt.setDouble(22, clgRate);
								pstmt.setString(23, specialInstr);
								pstmt.setTimestamp(24, dlvDate);
								pstmt.setString(25, benefitType);
								pstmt.setString(26, licenceNo);
								pstmt.setString(27, purcOrder);
								pstmt.setString(28, lineNoOrd);

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

								if (!(updCnt == 1)) 
								{
									errCode = "VTORDDT1";
									errCode = itmDBAccessLocal.getErrorString("",errCode,"");
									return errCode;
								}

								sql = " delete from taxtran where tran_code = 'P-ORD' and tran_id = ? and line_no = ? ";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, purcOrder);
								pstmt.setString(2, lineNoOrd);
								pstmt.executeUpdate();
								pstmt.close();
								pstmt = null;

								lineNo = "    " + lineNo;
								lineNo = lineNo.substring(lineNo.length() - 3,
										lineNo.length());

								sql = " select count(*) from taxtran where tran_code = 'P-AMD'"
										+ " and tran_id = ?	and line_no = ? and	 taxable_amt <> 0 and tax_amt <> 0 ";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, amdNo);
								pstmt.setString(2, lineNo);
								rs = pstmt.executeQuery();
								if (rs.next())
								{
									cnt = rs.getInt(1);
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;

								if (cnt > 0)
								{
								
								/*	
									sql = " insert into taxtran ( tran_code,tran_id,line_no,line_no__tax,tax_code,tax_class,tax_chap,tax_base,tax_env," +
										  " taxable_amt,tax_perc,tax_amt,chg_stat,tax_set,effect,acct_code__reco,cctr_code__reco,reco_perc,reco_amount," +
										  " acct_code,cctr_code,rate_type,round,round_to,tax_form,chg_date,chg_user,chg_term,posted,tax_form_date,pay_tax  ) " +
										  " select 'P-ORD',tran_id,line_no,line_no__tax,tax_code,tax_class,tax_chap,tax_base,tax_env,taxable_amt," +
										  " tax_perc,tax_amt,chg_stat,tax_set,effect,acct_code__reco,cctr_code__reco,reco_perc,reco_amount,acct_code,cctr_code," +
										  " rate_type,round,round_to,tax_form,chg_date,chg_user,chg_term,posted	,tax_form_date, pay_tax " +
										  " from taxtran where tran_code = ?	and   tran_id = ? and 	line_no = ? ";
									pstmt = conn.prepareStatement(sql);
									pstmt.setString(1, "P-AMD");
									pstmt.setString(2, amdNo);
									pstmt.setString(3, lineNo);
									int cnt2 = pstmt.executeUpdate();
									pstmt.close();
									pstmt = null;
									
									System.out.println("@@@@1 cnt2["+cnt2+"]");
									
								*/	/**/
									sql = " select line_no__tax,tax_code,"
											+ " tax_class,tax_chap,tax_base,tax_env,taxable_amt,tax_perc,tax_amt,chg_stat,tax_set,effect,"
											+ " acct_code__reco,cctr_code__reco,reco_perc,reco_amount,acct_code,cctr_code,rate_type,round,round_to,"
											+ " tax_form,chg_date,chg_user,chg_term,posted,tax_form_date,pay_tax "
											+ " from taxtran where tran_code = ?  and   tran_id = ? and   line_no = ? ";
									pstmt = conn.prepareStatement(sql);
									pstmt.setString(1, "P-AMD");
									pstmt.setString(2, amdNo);
									pstmt.setString(3, lineNo);
									rs = pstmt.executeQuery();
									if (rs.next()) {
										lineNoTax = rs.getString("line_no__tax");
										taxCode = rs.getString("tax_code");
										taxClass = rs.getString("tax_class");
										taxChap = rs.getString("tax_chap");
										taxBase = rs.getString("tax_base");
										taxEnv = rs.getString("tax_env");
										taxableAmt = rs.getDouble("taxable_amt");
										taxPerc = rs.getString("tax_perc");
										taxAmt = rs.getDouble("tax_amt");
										chgStat = rs.getString("chg_stat");
										taxSet = rs.getString("tax_set");
										effect = rs.getString("effect");
										acctCodeReco = rs.getString("acct_code__reco");
										cctrCodeReco = rs.getString("cctr_code__reco");
										recoPerc = rs.getString("reco_perc");
										recoAmont = rs.getDouble("reco_amount");
										acctCode = rs.getString("acct_code");
										cctrCode = rs.getString("cctr_code");
										rateType = rs.getString("rate_type");
										round = rs.getString("round");
										roundTo = rs.getString("round_to");
										taxForm = rs.getString("tax_form");
										chgDate = rs.getTimestamp("chg_date");
										chgUser = rs.getString("chg_user");
										chgTerm = rs.getString("chg_term");
										posted = rs.getString("posted");
										taxFormDate = rs.getTimestamp("tax_form_date");
										payTax = rs.getString("pay_tax");
									}

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

									sql = " insert into taxtran (tran_code,tran_id,line_no,line_no__tax,tax_code,tax_class,"
											+ " tax_chap,tax_base,tax_env,taxable_amt,tax_perc,tax_amt,chg_stat,tax_set	,	"
											+ " effect,acct_code__reco,	cctr_code__reco,reco_perc,reco_amount,acct_code,"
											+ "	cctr_code,rate_type,round,round_to,tax_form,chg_date,chg_user,chg_term,"
											+ "	posted,tax_form_date,pay_tax) "
											+ " values ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? ) ";
									pstmt = conn.prepareStatement(sql);
									pstmt.setString(1, "P-ORD");
									pstmt.setString(2, purcOrder);
									pstmt.setString(3, lineNoOrd);    //lineNo change by cpatil on 17/10/13
									pstmt.setString(4, lineNoTax);
									pstmt.setString(5, taxCode);
									pstmt.setString(6, taxClass);
									pstmt.setString(7, taxChap);
									pstmt.setString(8, taxBase);
									pstmt.setString(9, taxEnv);
									pstmt.setDouble(10, taxableAmt);
									pstmt.setString(11, taxPerc);
									pstmt.setDouble(12, taxAmt);
									pstmt.setString(13, chgStat);
									pstmt.setString(14, taxSet);
									pstmt.setString(15, effect);
									pstmt.setString(16, acctCodeReco);
									pstmt.setString(17, cctrCodeReco);
									pstmt.setString(18, recoPerc);
									pstmt.setDouble(19, recoAmont);
									pstmt.setString(20, acctCode);
									pstmt.setString(21, cctrCode);
									pstmt.setString(22, rateType);
									pstmt.setString(23, round);
									pstmt.setString(24, roundTo);
									pstmt.setString(25, taxForm);
									pstmt.setTimestamp(26, chgDate);
									pstmt.setString(27, chgUser);
									pstmt.setString(28, chgTerm);
									pstmt.setString(29, posted);
									pstmt.setTimestamp(30, taxFormDate);
									pstmt.setString(31, payTax);

									cnt = pstmt.executeUpdate();
									pstmt.close();
									pstmt = null;
/**/							System.out.println("@@@@1 cnt["+cnt+"]");
								}
								// end if
							}
							
							
						}
						else
//						if (oldItem == null || oldItem.trim().length() == 0) 
						{
							if (cctrCodeDr == null) 
							{
								cctrCodeDr = "";
							}

							if (cctrCodeCr == null) 
							{
								cctrCodeCr = "";
							}

							sql = " insert into porddet ( purc_order,line_no,site_code,ind_no,item_code,quantity,unit,rate,"
									+ "	discount,tax_amt,tot_amt,loc_code,req_date,dlv_date,dlv_qty,status,status_date,"
									+ " tax_class,tax_chap,tax_env,remarks,work_order,unit__rate,conv__qty_stduom,conv__rtuom_stduom,"
									+ "	unit__std,quantity__stduom,rate__stduom,pack_code,no_art,pack_instr,acct_code__dr,"
									+ " cctr_code__dr,acct_code__cr,cctr_code__cr,discount_type,supp_code__mnfr,order_opt,bom_code,"
									+ "	specific_instr,rate__clg,special_instr,benefit_type,licence_no,duty_paid,form_no	) "
									+ " values ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? ) ";

							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, purcOrder);
							pstmt.setString(2, lineNo);
							pstmt.setString(3, siteCode);
							pstmt.setString(4, indNo);
							pstmt.setString(5, itemCode);
							pstmt.setDouble(6, quantity);
							pstmt.setString(7, unit);
							pstmt.setDouble(8, rate);
							pstmt.setDouble(9, discount);
							pstmt.setDouble(10, taxAmt);
							pstmt.setDouble(11, totAmt);
							pstmt.setString(12, locCode);
							pstmt.setTimestamp(13, reqDate);
							pstmt.setTimestamp(14, dlvDate);
							pstmt.setDouble(15, dlvQuantity);
							pstmt.setString(16, status);
							pstmt.setTimestamp(17, sysDate);
							pstmt.setString(18, taxClass);
							pstmt.setString(19, taxChap);
							pstmt.setString(20, taxEnv);
							pstmt.setString(21, remarks);
							pstmt.setString(22, workOrder);
							pstmt.setString(23, unitRate);
							pstmt.setDouble(24, convQtyStduom);
							pstmt.setDouble(25, convRtuomStduom);
							pstmt.setString(26, UnitStd);
							pstmt.setDouble(27, quantityStduom);
							pstmt.setString(28, packCode);
							pstmt.setDouble(29, noArt);
							pstmt.setString(30, packInstr);
							pstmt.setString(31, acctCodeDr);
							pstmt.setString(32, acctCodeCr);
							pstmt.setString(33, discountType);
							pstmt.setString(34, suppCodeMnfr);
							pstmt.setString(35, orderOpt);
							pstmt.setString(36, bom);
							pstmt.setString(37, specificInstr);
							pstmt.setDouble(38, clgRate);
							pstmt.setString(39, specialInstr);
							pstmt.setString(40, benefitType);
							pstmt.setString(41, licenceNo);
							pstmt.setString(42, dutyPaid);
							pstmt.setString(43, formNo);
							cnt = pstmt.executeUpdate();
							pstmt.close();
							pstmt = null;

/*							
							sql = " insert into taxtran ( tran_code,tran_id,line_no,line_no__tax,tax_code,tax_class,tax_chap,tax_base,tax_env," +
									  " taxable_amt,tax_perc,tax_amt,chg_stat,tax_set,effect,acct_code__reco,cctr_code__reco,reco_perc,reco_amount," +
									  " acct_code,cctr_code,rate_type,round,round_to,tax_form,chg_date,chg_user,chg_term,posted,tax_form_date,pay_tax  ) " +
									  " select 'P-ORD',tran_id,line_no,line_no__tax,tax_code,tax_class,tax_chap,tax_base,tax_env,taxable_amt," +
									  " tax_perc,tax_amt,chg_stat,tax_set,effect,acct_code__reco,cctr_code__reco,reco_perc,reco_amount,acct_code,cctr_code," +
									  " rate_type,round,round_to,tax_form,chg_date,chg_user,chg_term,posted	,tax_form_date, pay_tax " +
									  " from taxtran where tran_code = ?	and   tran_id = ? and 	line_no = ? ";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, "P-AMD");
								pstmt.setString(2, amdNo);
								pstmt.setString(3, lineNo);
								cnt = pstmt.executeUpdate();
								pstmt.close();
								pstmt = null;
								System.out.println("@@@@2 cnt["+cnt+"]");
*/							
/**/							{
								sql = " select line_no__tax,tax_code,"
										+ " tax_class,tax_chap,tax_base,tax_env,taxable_amt,tax_perc,tax_amt,chg_stat,tax_set,effect,"
										+ " acct_code__reco,cctr_code__reco,reco_perc,reco_amount,acct_code,cctr_code,rate_type,round,round_to,"
										+ " tax_form,chg_date,chg_user,chg_term,posted,tax_form_date,pay_tax "
										+ " from taxtran where tran_code = 'P-AMD' and   tran_id = ? and   line_no = ? ";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, amdNo);
								pstmt.setString(2, lineNo);
								rs = pstmt.executeQuery();
								if (rs.next()) {
									lineNoTax = rs.getString("line_no__tax");
									taxCode = rs.getString("tax_code");
									taxClass = rs.getString("tax_class");
									taxChap = rs.getString("tax_chap");
									taxBase = rs.getString("tax_base");
									taxEnv = rs.getString("tax_env");
									taxableAmt = rs.getDouble("taxable_amt");
									taxPerc = rs.getString("tax_perc");
									taxAmt = rs.getDouble("tax_amt");
									chgStat = rs.getString("chg_stat");
									taxSet = rs.getString("tax_set");
									effect = rs.getString("effect");
									acctCodeReco = rs.getString("acct_code__reco");
									cctrCodeReco = rs.getString("cctr_code__reco");
									recoPerc = rs.getString("reco_perc");
									recoAmont = rs.getDouble("reco_amount");
									acctCode = rs.getString("acct_code");
									cctrCode = rs.getString("cctr_code");
									rateType = rs.getString("rate_type");
									round = rs.getString("round");
									roundTo = rs.getString("round_to");
									taxForm = rs.getString("tax_form");
									chgDate = rs.getTimestamp("chg_date");
									chgUser = rs.getString("chg_user");
									chgTerm = rs.getString("chg_term");
									posted = rs.getString("posted");
									taxFormDate = rs.getTimestamp("tax_form_date");
									payTax = rs.getString("pay_tax");
								}

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

								sql = " insert into taxtran (tran_code,tran_id,line_no,line_no__tax,tax_code,tax_class,"
										+ " tax_chap,tax_base,tax_env,taxable_amt,tax_perc,tax_amt,chg_stat,tax_set	,	"
										+ " effect,acct_code__reco,	cctr_code__reco,reco_perc,reco_amount,acct_code,"
										+ "	cctr_code,rate_type,round,round_to,tax_form,chg_date,chg_user,chg_term,"
										+ "	posted,tax_form_date,pay_tax) "
										+ " values ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? ) ";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, "P-ORD");
								pstmt.setString(2, purcOrder);
								pstmt.setString(3, lineNoOrd);   //lineNo change by cpatil on 17/10/13
								pstmt.setString(4, lineNoTax);
								pstmt.setString(5, taxCode);
								pstmt.setString(6, taxClass);
								pstmt.setString(7, taxChap);
								pstmt.setString(8, taxBase);
								pstmt.setString(9, taxEnv);
								pstmt.setDouble(10, taxableAmt);
								pstmt.setString(11, taxPerc);
								pstmt.setDouble(12, taxAmt);
								pstmt.setString(13, chgStat);
								pstmt.setString(14, taxSet);
								pstmt.setString(15, effect);
								pstmt.setString(16, acctCodeReco);
								pstmt.setString(17, cctrCodeReco);
								pstmt.setString(18, recoPerc);
								pstmt.setDouble(19, recoAmont);
								pstmt.setString(20, acctCode);
								pstmt.setString(21, cctrCode);
								pstmt.setString(22, rateType);
								pstmt.setString(23, round);
								pstmt.setString(24, roundTo);
								pstmt.setString(25, taxForm);
								pstmt.setTimestamp(26, chgDate);
								pstmt.setString(27, chgUser);
								pstmt.setString(28, chgTerm);
								pstmt.setString(29, posted);
								pstmt.setTimestamp(30, taxFormDate);
								pstmt.setString(31, payTax);

								cnt = pstmt.executeUpdate();
								pstmt.close();
								pstmt = null;
								System.out.println("@@@@2 cnt["+cnt+"]");

							}  // end if
/**/						}
						
						rs3.close();
						rs3 = null;
						pstmt3.close();
						pstmt3 = null;
						}
					else
					{
						sql = " select max(line_no)	 from porddet where purc_order = ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, purcOrder);
						rs = pstmt.executeQuery();
						if (rs.next()) {
							lineNoPO = rs.getString(1);
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;

						System.out.println("@@@@@@lineNoPO1["+lineNoPO+"]");	


						if (lineNoPO == null) 
						{
							lineNoPO = "0";
						}
						int tempId = ( Integer.parseInt(lineNoPO.trim())+ 1);
						lineNoPO = "    " + tempId;
						lineNoPO = lineNoPO.substring(lineNoPO.length() - 3,lineNoPO.length());
						System.out.println("@@@@@@lineNoPO4["+lineNoPO+"]");
						if (cctrCodeDr == null || cctrCodeDr.trim().length() == 0) 
						{
							cctrCodeDr = " ";
						}

						if (cctrCodeCr == null	|| cctrCodeCr.trim().length() == 0) 
						{
							cctrCodeCr = " ";
						}
						
						sql = " insert into porddet ( purc_order,line_no,site_code,ind_no,item_code,quantity,unit,rate,"
								+ "	discount,tax_amt,tot_amt,loc_code,req_date,dlv_date,dlv_qty,status,status_date,"
								+ " tax_class,tax_chap,tax_env,remarks,work_order,unit__rate,conv__qty_stduom,conv__rtuom_stduom,"
								+ "	unit__std,quantity__stduom,rate__stduom,pack_code,no_art,pack_instr,acct_code__dr,"
								+ " cctr_code__dr,acct_code__cr,cctr_code__cr,discount_type,supp_code__mnfr,order_opt,bom_code,"
								+ "	specific_instr,rate__clg,special_instr,benefit_type,licence_no,duty_paid,form_no	) "
								+ " values ( " +
								"?,?,?,?,?,?,?,?,?,?," +
								"?,?,?,?,?,?,?,?,?,?," +
								"?,?,?,?,?,?,?,?,?,?," +
								"?,?,?,?,?,?,?,?,?,?," +
								"?,?,?,?,?,? ) ";

						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, purcOrder);
						pstmt.setString(2, lineNoPO);
						pstmt.setString(3, siteCode);
						pstmt.setString(4, indNo);
						pstmt.setString(5, itemCode);
						pstmt.setDouble(6, quantity);
						pstmt.setString(7, unit);
						pstmt.setDouble(8, rate);
						pstmt.setDouble(9, discount);
						pstmt.setDouble(10, taxAmt);
						pstmt.setDouble(11, totAmt);
						pstmt.setString(12, locCode);
						pstmt.setTimestamp(13, reqDate);
						pstmt.setTimestamp(14, dlvDate);
						pstmt.setDouble(15, dlvQuantity);
						pstmt.setString(16, status);
						pstmt.setTimestamp(17, sysDate);
						pstmt.setString(18, taxClass);
						pstmt.setString(19, taxChap);
						pstmt.setString(20, taxEnv);
						pstmt.setString(21, remarks);
						pstmt.setString(22, workOrder);
						pstmt.setString(23, unitRate);
						pstmt.setDouble(24, convQtyStduom);
						pstmt.setDouble(25, convRtuomStduom);
						pstmt.setString(26, UnitStd);
						pstmt.setDouble(27, quantityStduom);
						pstmt.setDouble(28, rateStduom);
						pstmt.setString(29, packCode);
						pstmt.setDouble(30, noArt);
						pstmt.setString(31, packInstr);
						pstmt.setString(32, acctCodeDr);
						pstmt.setString(33, cctrCodeDr);
						pstmt.setString(34, acctCodeCr);
						pstmt.setString(35, cctrCodeCr);
						pstmt.setString(36, discountType);
						pstmt.setString(37, suppCodeMnfr);
						pstmt.setString(38, orderOpt);
						pstmt.setString(39, bom); // bom_code
						pstmt.setString(40, specificInstr);
						pstmt.setDouble(41, clgRate);
						pstmt.setString(42, specialInstr);
						pstmt.setString(43, benefitType);
						pstmt.setString(44, licenceNo);
						pstmt.setString(45, dutyPaid);
						pstmt.setString(46, formNo);
						cnt = pstmt.executeUpdate();
						pstmt.close();
						pstmt = null;

						if (cnt == 0) 
						{
/*							
							sql = " insert into taxtran ( tran_code,tran_id,line_no,line_no__tax,tax_code,tax_class,tax_chap,tax_base,tax_env," +
									  " taxable_amt,tax_perc,tax_amt,chg_stat,tax_set,effect,acct_code__reco,cctr_code__reco,reco_perc,reco_amount," +
									  " acct_code,cctr_code,rate_type,round,round_to,tax_form,chg_date,chg_user,chg_term,posted,tax_form_date,pay_tax  ) " +
									  " select 'P-ORD' ,tran_id,line_no,line_no__tax,tax_code,tax_class,tax_chap,tax_base,tax_env,taxable_amt," +
									  " tax_perc,tax_amt,chg_stat,tax_set,effect,acct_code__reco,cctr_code__reco,reco_perc,reco_amount,acct_code,cctr_code," +
									  " rate_type,round,round_to,tax_form,chg_date,chg_user,chg_term,posted	,tax_form_date, pay_tax " +
									  " from taxtran where tran_code = ?	and   tran_id = ? and 	line_no = ? ";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, "P-AMD");
								pstmt.setString(2, amdNo);
								pstmt.setString(3, lineNo);
								cnt = pstmt.executeUpdate();
								pstmt.close();
								pstmt = null;
							System.out.println("@@@@3 cnt["+cnt+"]");
*/						/**/
							sql = " select line_no__tax,tax_code,"
									+ " tax_class,tax_chap,tax_base,tax_env,taxable_amt,tax_perc,tax_amt,chg_stat,tax_set,effect,"
									+ " acct_code__reco,cctr_code__reco,reco_perc,reco_amount,acct_code,cctr_code,rate_type,round,round_to,"
									+ " tax_form,chg_date,chg_user,chg_term,posted,tax_form_date,pay_tax "
									+ " from taxtran where tran_code = ?  and   tran_id = ? and   line_no = ? ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, "P-AMD");
							pstmt.setString(2, amdNo);
							pstmt.setString(3, lineNo);
							rs = pstmt.executeQuery();
							if (rs.next())
							{
								lineNoTax = rs.getString("line_no__tax");
								taxCode = rs.getString("tax_code");
								taxClass = rs.getString("tax_class");
								taxChap = rs.getString("tax_chap");
								taxBase = rs.getString("tax_base");
								taxEnv = rs.getString("tax_env");
								taxableAmt = rs.getDouble("taxable_amt");
								taxPerc = rs.getString("tax_perc");
								taxAmt = rs.getDouble("tax_amt");
								chgStat = rs.getString("chg_stat");
								taxSet = rs.getString("tax_set");
								effect = rs.getString("effect");
								acctCodeReco = rs.getString("acct_code__reco");
								cctrCodeReco = rs.getString("cctr_code__reco");
								recoPerc = rs.getString("reco_perc");
								recoAmont = rs.getDouble("reco_amount");
								acctCode = rs.getString("acct_code");
								cctrCode = rs.getString("cctr_code");
								rateType = rs.getString("rate_type");
								round = rs.getString("round");
								roundTo = rs.getString("round_to");
								taxForm = rs.getString("tax_form");
								chgDate = rs.getTimestamp("chg_date");
								chgUser = rs.getString("chg_user");
								chgTerm = rs.getString("chg_term");
								posted = rs.getString("posted");
								taxFormDate = rs.getTimestamp("tax_form_date");
								payTax = rs.getString("pay_tax");
							}

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

							sql = " insert into taxtran (tran_code,tran_id,line_no,line_no__tax,tax_code,tax_class,"
									+ " tax_chap,tax_base,tax_env,taxable_amt,tax_perc,tax_amt,chg_stat,tax_set	,	"
									+ " effect,acct_code__reco,	cctr_code__reco,reco_perc,reco_amount,acct_code,"
									+ "	cctr_code,rate_type,round,round_to,tax_form,chg_date,chg_user,chg_term,"
									+ "	posted,tax_form_date,pay_tax) "
									+ " values (" +
									" ?,?,?,?,?,?,?,?,?,?," +
									" ?,?,?,?,?,?,?,?,?,?," +
									" ?,?,?,?,?,?,?,?,?,?," +
									" ? ) ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, "P-ORD");
							pstmt.setString(2, purcOrder);
							pstmt.setString(3, lineNoOrd);   //lineNo change by cpatil on 17/10/13 
							pstmt.setString(4, lineNoTax);
							pstmt.setString(5, taxCode);
							pstmt.setString(6, taxClass);
							pstmt.setString(7, taxChap);
							pstmt.setString(8, taxBase);
							pstmt.setString(9, taxEnv);
							pstmt.setDouble(10, taxableAmt);
							pstmt.setString(11, taxPerc);
							pstmt.setDouble(12, taxAmt);
							pstmt.setString(13, chgStat);
							pstmt.setString(14, taxSet);
							pstmt.setString(15, effect);
							pstmt.setString(16, acctCodeReco);
							pstmt.setString(17, cctrCodeReco);
							pstmt.setString(18, recoPerc);
							pstmt.setDouble(19, recoAmont);
							pstmt.setString(20, acctCode);
							pstmt.setString(21, cctrCode);
							pstmt.setString(22, rateType);
							pstmt.setString(23, round);
							pstmt.setString(24, roundTo);
							pstmt.setString(25, taxForm);
							pstmt.setTimestamp(26, chgDate);
							pstmt.setString(27, chgUser);
							pstmt.setString(28, chgTerm);
							pstmt.setString(29, posted);
							pstmt.setTimestamp(30, taxFormDate);
							pstmt.setString(31, payTax);

							cnt = pstmt.executeUpdate();
							pstmt.close();
							pstmt = null;
							System.out.println("@@@@3 cnt["+cnt+"]");
/*					*/
							sql = "	update poamd_det set line_no__ord = ?  where amd_no  = ? and line_no = ? ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, lineNoPO);
							pstmt.setString(2, amdNo);
							pstmt.setString(3, lineNo);
							updCnt = pstmt.executeUpdate();
							pstmt.close();
							pstmt = null;

							if (!(updCnt == 1)) 
							{
								errCode = "VTAMDDT";
								errCode = itmDBAccessLocal.getErrorString("",errCode,"");
								return errCode;
							}
						}
					}
				}  // end while
				rs1.close();
				rs1 = null;
				pstmt1.close();
				pstmt1 = null;
			}
				if (errCode == null || errCode.trim().length() == 0) 
				{
					int cntTerm=0;
					sql = " select count(1) from poamd_term where amd_no = ? ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, amdNo);
					rs = pstmt.executeQuery();
					if (rs.next())
					{
						cntTerm = rs.getInt(1);
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					
					System.out.println("@@@@@@@@@trem cntTerm:["+cntTerm+"]");
					if (cntTerm > 0) 
					{
						sql = " select max(line_no) from  pord_term where purc_order = ?  ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, purcOrder);
						rs = pstmt.executeQuery();
						if (rs.next()) 
						{
							lastTermLine = rs.getString(1) == null ? "0": rs.getString(1);
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;

						System.out.println("@@@@@@@@@trem lastTermLine:["+lastTermLine+"]");
						//for (int i = 1; i < Integer.parseInt(lastTermLine); i++) 
						for (int i = 1; i <= cntTerm; i++)
						{
							System.out.println("@@@@@@@@@term for Line:["+i+"]::lineNo["+lineNo+"]");
							// 31/10/13 manoharan print_opt added
							sql = " select purc_order,line_no_ord,term_code,descr, print_opt from poamd_term where amd_no = ? and line_no = ? ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, amdNo);
							//pstmt.setString(2, lineNo);
							pstmt.setString(2, ""+i );
							rs = pstmt.executeQuery();
							if (rs.next()) 
							{
								purcOrder = rs.getString("purc_order");
								lineNoPO = rs.getString("line_no_ord");
								termCode = rs.getString("term_code");
								descr = rs.getString("descr");
								printOpt = rs.getString("print_opt");
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;

							System.out.println("@@@@@@@@@trem lineNoPO:["+lineNoPO+"]");
							if (lineNoPO == null || lineNoPO.trim().length() == 0) 
							{
								System.out.println("@@@@@@@@@trem insert executed.... lineNoPO:["+lineNoPO+"]");
								int temp = Integer.parseInt(lastTermLine) + 1;
								lastTermLine = "" + temp;
							
								lineNoPO = lastTermLine;

								sql = " insert into pord_term ( purc_order, line_no, term_code, descr , print_opt) values (?,?,?,?, ?) ";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, purcOrder);
								pstmt.setString(2, lineNoPO);
								pstmt.setString(3, termCode);
								pstmt.setString(4, descr);
								pstmt.setString(5, printOpt);
								
								cnt = pstmt.executeUpdate();
								pstmt.close();
								pstmt = null;

							}
							else
							{
								System.out.println("@@@@@@@@@trem update executed.... lineNoPO:["+lineNoPO+"]");
								sql = "	update pord_term set term_code = ?, descr = ?, print_opt = ?  where purc_order = ? and   line_no = ? ";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, termCode);
								pstmt.setString(2, descr);
								pstmt.setString(3, printOpt);
								pstmt.setString(4, purcOrder);
								pstmt.setString(5, lineNoPO);
								updCnt = pstmt.executeUpdate();
								pstmt.close();
								pstmt = null;
								if (!(updCnt == 1))
								{
									errCode = "VTORDTERM";
									errCode = itmDBAccessLocal.getErrorString("",errCode,"");
									return errCode;
								}
							}
						}
					}
				}


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

					sql = " select sum(case when tax_amt is null then 0 else tax_amt end), sum(case when tot_amt is null then 0 else tot_amt end), "
							+ " sum(case when tot_amt is null then 0 else tot_amt end  - case when tax_amt is null then 0 else tax_amt end) "
							+ " from  porddet where purc_order = ? ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, purcOrder);
					rs = pstmt.executeQuery();
					if (rs.next()) 
					{
						taxAmtS = rs.getDouble(1);
						totAmtS = rs.getDouble(2);
						ordAmtS = rs.getDouble(3);
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;

					if (errCode == null || errCode.trim().length() == 0)
					{
						sql = "	select sum(case when net_amt is null then 0 else net_amt end)  from	voucher where purc_order = ? "
								+ " and	vouch_type = ? and confirmed  = ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, purcOrder);
						pstmt.setString(2, "A");
						pstmt.setString(3, "Y");
						rs = pstmt.executeQuery();
						if (rs.next())
						{
							vouchAmt = rs.getDouble(1);
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;

						if (totAmtS < vouchAmt) 
						{
							errCode = "ORDLESSVCH";
							errCode = itmDBAccessLocal.getErrorString("",errCode,"");
							return errCode;
						}
					}

				}

				if (errCode == null || errCode.trim().length() == 0)
				{
					sql = " select count(1) from porder where purc_order = ? ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, purcOrder);
					rs = pstmt.executeQuery();
					if (rs.next()) 
					{
						cnt = rs.getInt(1);
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					// 31/10/13 manoharan emp_code__aprv should not be changed to amendment approver
					if (cnt > 0)
					{
						sql = " update porder set amd_no = ?, amd_date=?, site_code__dlv=?, site_code__ord=?,site_code__bill=?,dept_code=?,"
								+ "supp_code=?,emp_code=?,item_ser=?,tax_opt=?,cr_term=?,ord_amt=?,tax_amt=?,tot_amt=?,curr_code=?,exch_rate=?,"
								+ "tax_chap=?,tax_class=?,tax_env=?,remarks=?,tax_date=?,proj_code=?,sales_pers=?,comm_perc=?,comm_perc__on=?,"
								+ "curr_code__comm=?,quot_no=?,tran_code=?,frt_amt=?,curr_code__frt=?,frt_term=?,dlv_term=?,insurance_amt	=?,"
								+ "curr_code__ins	=?,ref_date=?,advance=?, "
								+ "frt_type = ?, frt_rate=?, frt_amt__qty=? , frt_amt__fixed = ? "
								+ " where purc_order = ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, amdNo);
						pstmt.setTimestamp(2, amdDate);
						pstmt.setString(3, siteCodeDlv);
						pstmt.setString(4, siteCodeOrd);
						pstmt.setString(5, siteCodeBill);
						pstmt.setString(6, deptCode);
						pstmt.setString(7, suppCode);
						pstmt.setString(8, empCode);
						pstmt.setString(9, itemSer);
						pstmt.setString(10, taxOpt);
						pstmt.setString(11, crTerm);
						pstmt.setDouble(12, ordAmtS);
						pstmt.setDouble(13, taxAmtS);
						pstmt.setDouble(14, totAmtS);
						pstmt.setString(15, currCode);
						pstmt.setDouble(16, exchRate);
						pstmt.setString(17, taxChapHdr);
						pstmt.setString(18, taxClassHdr);
						pstmt.setString(19, taxEnvHdr);
						pstmt.setString(20, remarks);
						pstmt.setTimestamp(21, taxDate);
						pstmt.setString(22, projCode);
						pstmt.setString(23, salesPers);
						pstmt.setString(24, commPerc);
						pstmt.setString(25, commPercOn);
						pstmt.setString(26, currCodeComm);
						pstmt.setString(27, quotNo);
						pstmt.setString(28, tranCode);
						pstmt.setDouble(29, frtAmt);
						pstmt.setString(30, currCodeFrt);
						pstmt.setString(31, frtTerm);
						pstmt.setString(32, dlvTerm);
						pstmt.setDouble(33, insuranceAmt);
						pstmt.setString(34, currCodeIns);
						//pstmt.setString(35, empCodeAprv);
						pstmt.setTimestamp(35, refDate);
						pstmt.setString(36, advance);
						pstmt.setString(37, frtType);
						pstmt.setDouble(38, frtRate);
						pstmt.setDouble(39, frtAmtQty);
						pstmt.setDouble(40, frtAmtFixed);
						pstmt.setString(41, purcOrder);
						updCnt = pstmt.executeUpdate();
						pstmt.close();
						pstmt = null;

					}
					else 
					{
						errCode = "VTPORD1";
						errCode = itmDBAccessLocal.getErrorString("",errCode,"");
						return errCode;
					}
					
					if( updCnt > 0 )
					{
						double updFrtAmtQty=0;
						System.out.println("@@@@@@@@ for cross update into java.....");
						
						sql = " select frt_type, frt_rate, frt_amt__qty, frt_amt__fixed from porder	where purc_order = ?	 ";
						pstmt =  conn.prepareStatement(sql);
						pstmt.setString(1,purcOrder);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							frtType =  rs.getString("frt_type");
							frtRate =  rs.getDouble("frt_rate");
							frtAmtQty =  rs.getDouble("frt_amt__qty");
							frtAmtFixed =  rs.getDouble("frt_amt__fixed");
						}
						rs.close();
						rs = null;
						pstmt.close(); 
						pstmt = null;
						System.out.println("@@@@frtType["+frtType+"]frtRate["+frtRate+"]frtAmtQty["+frtAmtQty+"]frtAmtFixed["+frtAmtFixed+"]");
						
						if("F".equalsIgnoreCase(frtType))
						{
							sql = "	update porder set frt_amt__fixed = ? ,frt_amt = ? ,frt_amt__qty = 0  where purc_order = ?  ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setDouble(1, ( frtAmtFixed + frtAmtQty) );
							pstmt.setDouble(2, ( frtAmtFixed + frtAmtQty) );
							pstmt.setString(3, purcOrder);
							updCnt = pstmt.executeUpdate();
							pstmt.close();
							pstmt = null;
							
						}
						else if("Q".equalsIgnoreCase(frtType))
						{
							sql = " select quantity from porddet where purc_order = ?	 ";
							pstmt =  conn.prepareStatement(sql);
							pstmt.setString(1,purcOrder);
							rs = pstmt.executeQuery();
							while(rs.next())
							{
								quantity =  rs.getDouble("quantity");
								updFrtAmtQty = updFrtAmtQty + ( quantity * frtRate ) ;
							}
							rs.close();
							rs = null;
							pstmt.close(); 
							pstmt = null;
							System.out.println("@@@@@updFrtAmtQty["+updFrtAmtQty+"]");
							sql = "	update porder set frt_amt__qty = ? , frt_amt= ? , frt_amt__fixed = 0   where purc_order = ?  ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setDouble(1, updFrtAmtQty);
							pstmt.setDouble(2, updFrtAmtQty);
							pstmt.setString(3, purcOrder);
							updCnt = pstmt.executeUpdate();
							pstmt.close();
							pstmt = null;
							
						}
						
					}
				}

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

		return errCode;
		}

	private String checknull(String string)
	{
		if (string == null) 
		{
			string = "";
		}
		return string;
	}

	private String setDescription(String descr, String table, String field,
			String value, Connection conn) throws SQLException
	{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "";
		System.out.println("@@@@@@@@table[" + table + "]:::field[" + field
				+ "]::value[" + value + "]");
		sql = "select " + descr + " from " + table + " where " + field
				+ " = ? ";
		pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, value);
		rs = pstmt.executeQuery();
		if (rs.next()) 
		{
			descr = checknull (rs.getString(1));
		}
		rs.close();
		rs = null;
		pstmt.close();
		pstmt = null;
		System.out.print("::descr[" + descr);
		return descr;
	}

}