/********************************************************
        Title : BillofQuantityIC
        Date  : 20/09/12
        Developer: Akhilesh Sikarwar

 ********************************************************/
package ibase.webitm.ejb.dis;

import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.ValidatorEJB;
import ibase.webitm.ejb.fin.FinCommon;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;

import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

import javax.ejb.Stateless;

import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

@Stateless
public class Customer extends ValidatorEJB

implements CustomerLocal, CustomerRemote {
	GenericUtility genericUtility = GenericUtility.getInstance();
	String winName = null;
	FinCommon finCommon = null;
	ValidatorEJB validator = null;

	public String wfValData(String xmlString, String xmlString1,
			String xmlString2, String objContext, String editFlag,
			String xtraParams) throws RemoteException, ITMException {
		String errString = "";
		Document dom = null;
		Document dom1 = null;
		Document dom2 = null;
		try {
			System.out.println("Val xmlString :: " + xmlString);
			System.out.println("Val xmlString1 :: " + xmlString1);
			System.out.println("Val xmlString2 :: " + xmlString2);
			if (xmlString != null && xmlString.trim().length() > 0) {
				dom = parseString(xmlString);
			}
			if (xmlString1 != null && xmlString1.trim().length() > 0) {
				dom1 = parseString(xmlString1);
			}
			if (xmlString2 != null && xmlString2.trim().length() > 0) {
				dom2 = parseString("<Root>" + xmlString2 + "</Root>");
			}
			errString = wfValData(dom, dom1, dom2, objContext, editFlag,
					xtraParams);
		} catch (Exception e) {
			e.printStackTrace();
			throw new ITMException(e);
		}
		return errString;
	}

	public String wfValData(Document dom, Document dom1, Document dom2,
			String objContext, String editFlag, String xtraParams)
					throws RemoteException, ITMException {
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;
		String childNodeName = null;
		StringBuffer valueXmlString = new StringBuffer();
		String userId = "";
		String sql = "";
		String errCode = "";
		String errorType = "";
		String errString = "";
		String tranId = "";
		String custCode="",ignoreCredit="",ignoreDays="",acctCodeAdv="",cctrCodeAdv="",groupCode="",custCodeBil="",stanCode="",terrCode="";
		String stateCode="",countCode="",crTerm="",currCode="",currCode1="",acctCodeAr="",cctrCodeAr="",taxClass="",taxChap="",priceList="";
		String salesPers="",tranCode="",siteCodeRcp="",siteCode="",channelPartner="",blackListing="",contactCode="",bankCode="",priceListDisc="";
		String salesOption="",dlvTerm="",lossPerc="",adhocReplPerc="",termTableNo="",priceListClg="",salesPers1="",salesPers2="";
		String keyFlag="",active="",lsVal3="",empCodeOrd="",empCodeOrd1="",custCodePd="",custCodeDisc="",sgroupCode="",custCodeAr="",currCodeFrt="";
		String blankVar="";
		String regCode="",validUpto="",regDate="";
		int ctr = 0;
		int currentFormNo = 0;
		int cnt = 0,cnt1=0;
		ArrayList errList = new ArrayList();
		ArrayList errFields = new ArrayList();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ConnDriver connDriver = new ConnDriver();
		StringBuffer errStringXml = new StringBuffer(
				"<?xml version = \"1.0\"?> \r\n <Root> <Errors>");
		try {
			this.finCommon = new FinCommon();
			this.validator = new ValidatorEJB();
			conn = connDriver.getConnectDB("DriverITM");
			userId = getValueFromXTRA_PARAMS(xtraParams, "loginCode");
			if ((objContext != null) && (objContext.trim().length() > 0)) {
				currentFormNo = Integer.parseInt(objContext);
			}
			valueXmlString = new StringBuffer(
					"<?xml version = \"1.0\"?> <Root> <header> <editFlag>");
			valueXmlString.append(editFlag).append("</editFlag> </header>");
			switch (currentFormNo) {
			case 1:
				parentNodeList = dom.getElementsByTagName("Detail1");
				parentNode = parentNodeList.item(0);
				childNodeList = parentNode.getChildNodes();
				valueXmlString.append("<Detail1>");
				int childNodeListLength = childNodeList.getLength();
				tranId = checkNull(this.genericUtility.getColumnValue(
						"tran_id", dom));
				System.out.println("tran id from boqdet --4-->>>>[" + tranId
						+ "]");
				for (ctr = 0; ctr < childNodeListLength; ctr++) {
					childNode = childNodeList.item(ctr);
					childNodeName = childNode.getNodeName();
					System.out.println("CURRENT COLUMN IN  VALIDATION ["+ childNodeName + "]");
					if (childNodeName.equalsIgnoreCase("cust_code")) {
						custCode = this.genericUtility.getColumnValue("cust_code", dom);

						sql = "select key_flag from transetup where tran_window = 'w_customer'";
//						sql = "select key_flag from transetup where tran_window = 'w_customer_test'";
						pstmt = conn.prepareStatement(sql);
						rs = pstmt.executeQuery();
						if (rs.next()) {
							keyFlag = rs.getString("key_flag");
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;

						if(keyFlag==null){
							keyFlag="M";
						}
						System.out.println("Key flag is :-["+keyFlag+"]");
						if(keyFlag.equalsIgnoreCase("M") && (custCode==null || custCode.trim().length()<=0)){
							System.out.print("Throw error....");
							errCode = "VMCODNULL";
							errList.add(errCode);
							errFields.add(childNodeName.toLowerCase());
						}else if(editFlag.equalsIgnoreCase("A")){
							cnt=0;
							sql = "select count(*) as ll_count from customer where cust_code =?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, custCode);
							rs = pstmt.executeQuery();
							if (rs.next()) {
								cnt = rs.getInt("ll_count");
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;

							if(cnt>0){
								errCode = "VMDUPL1";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}
					}else if(childNodeName.equalsIgnoreCase("ignore_credit")){
						ignoreCredit = this.genericUtility.getColumnValue("ignore_credit", dom);

						cnt=0;
						if(ignoreCredit!=null && ignoreCredit.trim().length()>0){
							cnt=Integer.parseInt(ignoreCredit.trim());
							if(cnt<0){
								errCode = "VMIGCRT";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}
					}else if(childNodeName.equalsIgnoreCase("ignore_days")){
						ignoreDays = this.genericUtility.getColumnValue("ignore_days", dom);

						cnt=0;
						if(ignoreDays!=null && ignoreDays.trim().length()>0){
							cnt=Integer.parseInt(ignoreDays.trim());
							if(cnt<0){
								errCode = "VMCRDYS";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}
					}else if(childNodeName.equalsIgnoreCase("acct_code__adv")){
						acctCodeAdv = this.genericUtility.getColumnValue("acct_code__adv", dom);
						
						cnt=0;
						sql = "select count(*) as cnt from accounts where acct_code =?";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, acctCodeAdv);
						rs = pstmt.executeQuery();
						if (rs.next()) {
							cnt = rs.getInt("cnt");
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;

						if(cnt!=0){
							sql = "select active from accounts where acct_code =?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, acctCodeAdv);
							rs = pstmt.executeQuery();
							if (rs.next()) {
								active = rs.getString("active");
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;
							
							if(!active.equalsIgnoreCase("Y")){
								errCode = "VMACCTA";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}else{
							errCode = "VTACCTCD1";
							errList.add(errCode);
							errFields.add(childNodeName.toLowerCase());
						}
					}else if(childNodeName.equalsIgnoreCase("cctr_code__adv")){
						cctrCodeAdv = this.genericUtility.getColumnValue("cctr_code__adv", dom);
						cnt=0;

						sql = "select count(*) as cnt from costctr where cctr_code =?";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, cctrCodeAdv);
						rs = pstmt.executeQuery();
						if (rs.next()) {
							cnt = rs.getInt("cnt");
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;

						if(cnt==0){
							errCode = "VTCCTRCD1";
							errList.add(errCode);
							errFields.add(childNodeName.toLowerCase());
						}
					}else if(childNodeName.equalsIgnoreCase("group_code")){
						groupCode = this.genericUtility.getColumnValue("group_code", dom);
						custCode = this.genericUtility.getColumnValue("cust_code", dom);

						if(!groupCode.equalsIgnoreCase(custCode)){
							cnt=0;
							sql = "select count(*) as cnt from customer where cust_code =?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, groupCode);
							rs = pstmt.executeQuery();
							if (rs.next()) {
								cnt = rs.getInt("cnt");
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;

							sql = "select key_flag from transetup where tran_window = 'w_customer'";
							pstmt = conn.prepareStatement(sql);
							//pstmt.setString(1, groupCode);
							rs = pstmt.executeQuery();
							if (rs.next()) {
								keyFlag = rs.getString("key_flag");
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;

							if(cnt==0 && !(keyFlag.equalsIgnoreCase("A"))){
								errCode = "VMCUST1";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}
					}else if(childNodeName.equalsIgnoreCase("cust_code__bil")){
						custCodeBil = this.genericUtility.getColumnValue("cust_code__bil", dom);
						custCode = this.genericUtility.getColumnValue("cust_code", dom);

						sql = "select key_flag from transetup where tran_window = 'w_customer'";
						pstmt = conn.prepareStatement(sql);
//						pstmt.setString(1, groupCode);
						rs = pstmt.executeQuery();
						if (rs.next()) {
							keyFlag = rs.getString("key_flag");
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;

						if((custCodeBil!=null || custCodeBil.trim().length()>0) && !(keyFlag.equalsIgnoreCase("A"))){
							errCode = "VEBILLTO";
							errList.add(errCode);
							errFields.add(childNodeName.toLowerCase());
						}else if(!custCodeBil.equalsIgnoreCase(custCode)){
							cnt=0;
							sql = "select count(*) as cnt from customer where cust_code =?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, custCodeBil);
							rs = pstmt.executeQuery();
							if (rs.next()) {
								cnt = rs.getInt("cnt");
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;

							if(cnt==0){
								errCode = "VMBILLTO";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}
					}else if(childNodeName.equalsIgnoreCase("stan_code")){
						stanCode = this.genericUtility.getColumnValue("stan_code", dom);
						cnt=0;

						sql = "select count(*) as cnt from station where stan_code =?";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, stanCode);
						rs = pstmt.executeQuery();
						if (rs.next()) {
							cnt = rs.getInt("cnt");
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;

						if(cnt==0){
							errCode = "VTSTAN1";
							errList.add(errCode);
							errFields.add(childNodeName.toLowerCase());
						}
					}else if(childNodeName.equalsIgnoreCase("terr_code")){
						terrCode = this.genericUtility.getColumnValue("terr_code", dom);

						if(terrCode!=null && terrCode.trim().length()>0){
							cnt=0;
							sql = "select count(*) as ll_count from territory where terr_code =?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, terrCode);
							rs = pstmt.executeQuery();
							if (rs.next()) {
								cnt = rs.getInt("ll_count");
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;

							if(cnt==0){
								errCode = "VTTERRCD";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}
					}else if(childNodeName.equalsIgnoreCase("state_code")){
						stateCode = this.genericUtility.getColumnValue("state_code", dom);

						if(stateCode!=null && stateCode.trim().length()>0){
							cnt=0;
							sql = "select count(*) as cnt from state where state_code =?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, stateCode);
							rs = pstmt.executeQuery();
							if (rs.next()) {
								cnt = rs.getInt("cnt");
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;

							if(cnt==0){
								errCode = "VTSTATE1";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}
					}else if(childNodeName.equalsIgnoreCase("count_code")){
						countCode = this.genericUtility.getColumnValue("count_code", dom);

						if(countCode!=null && countCode.trim().length()>0){
							cnt=0;
							sql = "select count(*) as cnt from country where count_code =?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, countCode);
							rs = pstmt.executeQuery();
							if (rs.next()) {
								cnt = rs.getInt("cnt");
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;

							if(cnt==0){
								errCode = "VTCONTCD1";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}
					}else if(childNodeName.equalsIgnoreCase("cr_term")){
						crTerm = this.genericUtility.getColumnValue("cr_term", dom);

						if(crTerm!=null && crTerm.trim().length()>0){
							cnt=0;
							sql = "select count(*) as cnt from crterm where cr_term =?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, crTerm);
							rs = pstmt.executeQuery();
							if (rs.next()) {
								cnt = rs.getInt("cnt");
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;

							if(cnt==0){
								errCode = "VTCRTERM1";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}
					}else if(childNodeName.equalsIgnoreCase("curr_code")){
						currCode = this.genericUtility.getColumnValue("curr_code", dom);
						cnt=0;

						sql = "select count(*) as cnt from currency where curr_code =?";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, currCode);
						rs = pstmt.executeQuery();
						if (rs.next()) {
							cnt = rs.getInt("cnt");
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;

						if(cnt==0){
							errCode = "VTCURRCD1";
							errList.add(errCode);
							errFields.add(childNodeName.toLowerCase());
						}else{
							if(editFlag.equalsIgnoreCase("E")){
								custCode = this.genericUtility.getColumnValue("cust_code", dom);

								sql = "select curr_code from customer where cust_code =?";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, custCode);
								rs = pstmt.executeQuery();
								if (rs.next()) {
									currCode1 = rs.getString("curr_code");
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;

								if(!currCode.equalsIgnoreCase(currCode1) && currCode1!=null){
									lsVal3="C";
									cnt=0;

									sql = "select count(distinct curr_code__ac) as ll_count from sundrybal"
											+ " where sundry_code =?"
											+ " and sundry_type =?"
											+ " and (dr_amt != 0 or cr_amt != 0)";
									pstmt = conn.prepareStatement(sql);
									pstmt.setString(1, custCode);
									pstmt.setString(2, lsVal3);
									rs = pstmt.executeQuery();
									if (rs.next()) {
										cnt = rs.getInt("ll_count");
									}
									rs.close();
									rs = null;
									pstmt.close();
									pstmt = null;

									cnt1=0;
									sql = "select count(*) as cnt from sundrybal"
											+ " where sundry_code = ?"
											+ " and sundry_type = ?"
											+ " and (dr_amt != 0 or cr_amt != 0)"
											+ " and curr_code__ac = ?";
									pstmt = conn.prepareStatement(sql);
									pstmt.setString(1, custCode);
									pstmt.setString(2, lsVal3);
									pstmt.setString(3, currCode1);
									rs = pstmt.executeQuery();
									if (rs.next()) {
										cnt1 = rs.getInt("ll_count");
									}
									rs.close();
									rs = null;
									pstmt.close();
									pstmt = null;

									if(cnt1!=0 && cnt>1){
										errCode = "VXCURRCD1";
										errList.add(errCode);
										errFields.add(childNodeName.toLowerCase());
									}
								}
							}
						}
					}else if(childNodeName.equalsIgnoreCase("acct_code__ar")){
						acctCodeAr = this.genericUtility.getColumnValue("acct_code__ar", dom);

						if(acctCodeAr!=null && acctCodeAr.trim().length()>0){
							cnt=0;
							sql = "select count(*) as cnt from accounts where acct_code = ?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, acctCodeAr);
							rs = pstmt.executeQuery();
							if (rs.next()) {
								cnt = rs.getInt("cnt");
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;

							if(cnt!=0){
								sql = "select active from accounts where acct_code =?";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, acctCodeAr);
								rs = pstmt.executeQuery();
								if (rs.next()) {
									active = rs.getString("active");
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;

								if(!active.equalsIgnoreCase("Y")){
									errCode = "VMACCTA";
									errList.add(errCode);
									errFields.add(childNodeName.toLowerCase());
								}
							}else{
								errCode = "VTACCTCD1";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}
					}else if(childNodeName.equalsIgnoreCase("cctr_code__ar")){
						cctrCodeAr = this.genericUtility.getColumnValue("cctr_code__ar", dom);

						if(cctrCodeAr!=null && cctrCodeAr.trim().length()>0){
							cnt=0;
							sql = "select count(*) as cnt from costctr where cctr_code =?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, cctrCodeAr);
							rs = pstmt.executeQuery();
							if (rs.next()) {
								cnt = rs.getInt("cnt");
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;

							if(cnt==0){
								errCode = "VTCCTRCD1";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}else{
							acctCodeAr = this.genericUtility.getColumnValue("acct_code__ar", dom);
							if(acctCodeAr!=null && acctCodeAr.trim().length()>0){
								valueXmlString.append("<cctr_code__ar >")
								.append("<![CDATA[" + blankVar + "]]>")
								.append("</cctr_code__ar>");
							}
						}
					}else if(childNodeName.equalsIgnoreCase("tax_class")){
						taxClass = this.genericUtility.getColumnValue("tax_class", dom);

						if(taxClass!=null && taxClass.trim().length()>0){
							cnt=0;
							sql = "select count(*) as cnt from taxclass where tax_class =?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, taxClass);
							rs = pstmt.executeQuery();
							if (rs.next()) {
								cnt = rs.getInt("cnt");
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;

							if(cnt==0){
								errCode = "VTTAXCLA1";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}
					}else if(childNodeName.equalsIgnoreCase("tax_chap")){
						taxChap = this.genericUtility.getColumnValue("tax_chap", dom);

						if(taxChap!=null && taxChap.trim().length()>0){
							cnt=0;
							sql = "select count(*) as cnt from taxchap where tax_chap =?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, taxChap);
							rs = pstmt.executeQuery();
							if (rs.next()) {
								cnt = rs.getInt("cnt");
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;

							if(cnt==0){
								errCode = "VTTAXCHAP1";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}
					}else if(childNodeName.equalsIgnoreCase("price_list")){
						priceList = this.genericUtility.getColumnValue("price_list", dom);

						if(priceList!=null && priceList.trim().length()>0){
							cnt=0;
							sql = "select count(*) as cnt from pricelist_mst where price_list =?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, priceList);
							rs = pstmt.executeQuery();
							if (rs.next()) {
								cnt = rs.getInt("cnt");
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;

							if(cnt==0){
								errCode = "VTPLIST1";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}
					}else if(childNodeName.equalsIgnoreCase("price_list__clg")){
						priceListClg = this.genericUtility.getColumnValue("price_list__clg", dom);

						if(priceListClg!=null && priceListClg.trim().length()>0){
							cnt=0;
							sql = "select count(*) as cnt from pricelist_mst where price_list =?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, priceListClg);
							rs = pstmt.executeQuery();
							if (rs.next()) {
								cnt = rs.getInt("cnt");
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;

							if(cnt==0){
								errCode = "VTPLIST1";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}
					}
					else if(childNodeName.equalsIgnoreCase("sales_pers")){
						salesPers = this.genericUtility.getColumnValue("sales_pers", dom);

						if(salesPers!=null && salesPers.trim().length()>0){
							cnt=0;
							sql = "select count(*) as cnt from sales_pers where sales_pers = ?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, salesPers);
							rs = pstmt.executeQuery();
							if (rs.next()) {
								cnt = rs.getInt("cnt");
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;

							if(cnt==0){
								errCode = "VMSLPERS1";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}
					}
					else if(childNodeName.equalsIgnoreCase("sales_pers__1")){
						salesPers1 = this.genericUtility.getColumnValue("sales_pers__1", dom);
						if(salesPers1!=null && salesPers1.trim().length()>0){
							cnt=0;
							sql = "select count(*) as cnt from sales_pers where sales_pers = ?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, salesPers1);
							rs = pstmt.executeQuery();
							if (rs.next()) {
								cnt = rs.getInt("cnt");
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;

							if(cnt==0){
								errCode = "VMSLPERS1";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}
					}
					else if(childNodeName.equalsIgnoreCase("sales_pers__2")){
						salesPers2 = this.genericUtility.getColumnValue("sales_pers__2", dom);
						if(salesPers2!=null && salesPers2.trim().length()>0){
							cnt=0;
							sql = "select count(*) as cnt from sales_pers where sales_pers = ?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, salesPers2);
							rs = pstmt.executeQuery();
							if (rs.next()) {
								cnt = rs.getInt("cnt");
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;
							
							if(cnt==0){
								errCode = "VMSLPERS1";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}
					}
					else if(childNodeName.equalsIgnoreCase("tran_code")){
						tranCode = this.genericUtility.getColumnValue("tran_code", dom);

						if(tranCode!=null && tranCode.trim().length()>0){
							cnt=0;
							sql = "select count(*) as cnt from transporter where tran_code = ?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, tranCode);
							rs = pstmt.executeQuery();
							if (rs.next()) {
								cnt = rs.getInt("cnt");
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;

							if(cnt==0){
								errCode = "VTTRANCD1";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}
					}else if(childNodeName.equalsIgnoreCase("site_code__rcp")){
						siteCodeRcp = this.genericUtility.getColumnValue("site_code__rcp", dom);

						if(siteCodeRcp!=null && siteCodeRcp.trim().length()>0){
							cnt=0;
							sql = "select count(*) as cnt from site where site_code =?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, siteCodeRcp);
							rs = pstmt.executeQuery();
							if (rs.next()) {
								cnt = rs.getInt("cnt");
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;

							if(cnt==0){
								errCode = "VTSITECD1";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}
					}else if(childNodeName.equalsIgnoreCase("site_code")){
						siteCode = this.genericUtility.getColumnValue("site_code", dom);
						channelPartner = this.genericUtility.getColumnValue("channel_partner", dom);
						
						if(channelPartner.equalsIgnoreCase("Y") && (siteCode==null && siteCode.trim().length()<=0)){
							errCode = "VMSITECD1";
							errList.add(errCode);
							errFields.add(childNodeName.toLowerCase());
						}else{
							if(channelPartner.equalsIgnoreCase("N") && (siteCode!=null)){
								errCode = "VNRSITE";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
							if(channelPartner.equalsIgnoreCase("Y") && (siteCode!=null && siteCode.trim().length()>0)){
								cnt=0;
								sql = "select count(*) as cnt from site where site_code =?";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, siteCode);
								rs = pstmt.executeQuery();
								if (rs.next()) {
									cnt = rs.getInt("cnt");
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;

								if(cnt==0){
									errCode = "VTSITECD1";
									errList.add(errCode);
									errFields.add(childNodeName.toLowerCase());
								}
							}
						}
					}else if(childNodeName.equalsIgnoreCase("black_listing")){
						blackListing = this.genericUtility.getColumnValue("black_listing", dom);

						if(!blackListing.equalsIgnoreCase("P")){
							errCode = "VMBLACKLIS";
							errList.add(errCode);
							errFields.add(childNodeName.toLowerCase());
						}
					}else if(childNodeName.equalsIgnoreCase("contact_code")){
						contactCode = this.genericUtility.getColumnValue("contact_code", dom);

						cnt=0;
						sql = "select count(*) as cnt from contact where contact_code =?";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, contactCode);
						rs = pstmt.executeQuery();
						if (rs.next()) {
							cnt = rs.getInt("cnt");
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;

						if(cnt==0){
							errCode = "VMCONTCD1";
							errList.add(errCode);
							errFields.add(childNodeName.toLowerCase());
						}
					}else if(childNodeName.equalsIgnoreCase("bank_code")){
						bankCode = this.genericUtility.getColumnValue("bank_code", dom);
						
						if(bankCode!=null){
							cnt=0;
							sql = "select count(*) as cnt from bank where bank_code =?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, bankCode);
							rs = pstmt.executeQuery();
							if (rs.next()) {
								cnt = rs.getInt("cnt");
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;
							
							if(cnt<=0){
								errCode = "VMBANK1";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}
					}else if(childNodeName.equalsIgnoreCase("price_list__disc")){
						priceListDisc = this.genericUtility.getColumnValue("price_list__disc", dom);

						if(priceListDisc!=null && priceListDisc.trim().length()>0){
//						cnt1=Integer.parseInt(priceListDisc.trim());
//						}
//						if(cnt1>0){
							cnt=0;
							sql = "select count(*) as cnt from pricelist where price_list =?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, priceListDisc);
							rs = pstmt.executeQuery();
							if (rs.next()) {
								cnt = rs.getInt("cnt");
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;

							if(cnt==0){
								errCode = "VTPLIST1";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}
					}else if(childNodeName.equalsIgnoreCase("sales_option")){
						salesOption = this.genericUtility.getColumnValue("sales_option", dom);

						if(salesOption==null || salesOption.trim().length()<=0){
							errCode = "VTSLOPT";
							errList.add(errCode);
							errFields.add(childNodeName.toLowerCase());
						}
					}else if(childNodeName.equalsIgnoreCase("dlv_term")){
						dlvTerm = this.genericUtility.getColumnValue("dlv_term", dom);

						if(dlvTerm!=null || dlvTerm.trim().length()>0){
							cnt=0;
							sql = "select count(*) as cnt from delivery_term where dlv_term =?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, dlvTerm);
							rs = pstmt.executeQuery();
							if (rs.next()) {
								cnt = rs.getInt("cnt");
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;

							if(cnt==0){
								errCode = "VMDLVTERM1";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}
					}else if(childNodeName.equalsIgnoreCase("loss_perc")){
						lossPerc = this.genericUtility.getColumnValue("loss_perc", dom);

						if(lossPerc!=null){
						cnt1=Integer.parseInt(lossPerc);
						}
						if(cnt1<0){
							errCode = "VTLOSSPERC";
							errList.add(errCode);
							errFields.add(childNodeName.toLowerCase());
						}
					}else if(childNodeName.equalsIgnoreCase("adhoc_repl_perc")){
						adhocReplPerc = this.genericUtility.getColumnValue("adhoc_repl_perc", dom);
						double adhocvalue=0.00;

						if(adhocReplPerc!=null||adhocReplPerc.trim().length()>0){
							
							adhocvalue=Double.valueOf(adhocReplPerc.trim());
//							cnt1=Integer.parseInt(adhocReplPerc.trim());
//							if(cnt1<0 || cnt1>100){
								if(adhocvalue<0 || adhocvalue>100){
								errCode = "VTADH";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}
					}else if(childNodeName.equalsIgnoreCase("term_table__no")){
						termTableNo = this.genericUtility.getColumnValue("term_table__no", dom);

						if(termTableNo!=null){
							sql = "select count(1) as cnt from sale_term_table where term_table  = ?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, termTableNo);
							rs = pstmt.executeQuery();
							if (rs.next()) {
								cnt = rs.getInt("cnt");
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;

							if(cnt==0){
								errCode = "VMSALETERM";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}
					}else if(childNodeName.equalsIgnoreCase("emp_code__ord")){
						empCodeOrd = checkNull(this.genericUtility.getColumnValue("emp_code__ord", dom));
						
									
						if(empCodeOrd!=null && empCodeOrd.trim().length()>0){
							cnt=0;
							sql = "select count(*) as cnt from employee where emp_code=?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, empCodeOrd);
							rs = pstmt.executeQuery();
							if (rs.next()) {
								cnt = rs.getInt("cnt");
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;

							if(cnt==0){
								errCode = "VMEMPORD2";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
							
						}
					}else if(childNodeName.equalsIgnoreCase("emp_code__ord1")){
						
						empCodeOrd1 = checkNull(this.genericUtility.getColumnValue("emp_code__ord1", dom));
						if(empCodeOrd1!=null && empCodeOrd1.trim().length()>0){
							cnt=0;
							sql = "select count(*) as cnt from employee where emp_code=?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, empCodeOrd1);
							rs = pstmt.executeQuery();
							if (rs.next()) {
								cnt = rs.getInt("cnt");
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;

							if(cnt==0){
								errCode = "VMEMPORD2";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}
					}
//					else if(childNodeName.equalsIgnoreCase("cust_code__pd")){
//						custCodePd = checkNull(this.genericUtility.getColumnValue(
//								"cust_code__pd", dom));
//						
//						if(custCodePd!=null){
//							cnt=0;
//							sql = "select count(*) as cnt from customer where cust_code__pd=?";
//							pstmt = conn.prepareStatement(sql);
//							pstmt.setString(1, custCodePd);
//							rs = pstmt.executeQuery();
//							if (rs.next()) {
//								cnt = rs.getInt("cnt");
//							}
//							rs.close();
//							rs = null;
//							pstmt.close();
//							pstmt = null;
//
//							if(cnt==0){
//								errCode = "";
//								errList.add(errCode);
//								errFields.add(childNodeName.toLowerCase());
//							}
//						}
//					}
//					else if(childNodeName.equalsIgnoreCase("cust_code__disc")){
//						custCodeDisc = checkNull(this.genericUtility.getColumnValue(
//								"cust_code__disc", dom));
//						
//						if(custCodeDisc!=null){
//							cnt=0;
//							sql = "select count(*) as cnt from customer where cust_code__disc=?";
//							pstmt = conn.prepareStatement(sql);
//							pstmt.setString(1, custCodeDisc);
//							rs = pstmt.executeQuery();
//							if (rs.next()) {
//								cnt = rs.getInt("cnt");
//							}
//							rs.close();
//							rs = null;
//							pstmt.close();
//							pstmt = null;
//
//							if(cnt==0){
//								errCode = "";
//								errList.add(errCode);
//								errFields.add(childNodeName.toLowerCase());
//							}
//						}
//					}
//					else if(childNodeName.equalsIgnoreCase("sgroup_code")){
//						sgroupCode = checkNull(this.genericUtility.getColumnValue(
//								"sgroup_code", dom));
//						
//						if(sgroupCode!=null){
//							cnt=0;
//							sql = "select count(*) as cnt from acctsgrp where sgroup_code=?";
//							pstmt = conn.prepareStatement(sql);
//							pstmt.setString(1, sgroupCode);
//							rs = pstmt.executeQuery();
//							if (rs.next()) {
//								cnt = rs.getInt("cnt");
//							}
//							rs.close();
//							rs = null;
//							pstmt.close();
//							pstmt = null;
//
//							if(cnt==0){
//								errCode = "";
//								errList.add(errCode);
//								errFields.add(childNodeName.toLowerCase());
//							}
//						}
//					}
//					else if(childNodeName.equalsIgnoreCase("cust_code__ar")){
//						custCodeAr = checkNull(this.genericUtility.getColumnValue(
//								"cust_code__ar", dom));
//						
//						if(custCodeAr!=null){
//							cnt=0;
//							sql = "select count(*) as cnt from customer where cust_code__ar=?";
//							pstmt = conn.prepareStatement(sql);
//							pstmt.setString(1, custCodeAr);
//							rs = pstmt.executeQuery();
//							if (rs.next()) {
//								cnt = rs.getInt("cnt");
//							}
//							rs.close();
//							rs = null;
//							pstmt.close();
//							pstmt = null;
//
//							if(cnt==0){
//								errCode = "";
//								errList.add(errCode);
//								errFields.add(childNodeName.toLowerCase());
//							}
//						}
//					}
				}

				valueXmlString.append("</Detail1>");
				break;
			case 2:
				parentNodeList = dom.getElementsByTagName("Detail2");
				parentNode = parentNodeList.item(0);
				childNodeList = parentNode.getChildNodes();
				valueXmlString.append("<Detail2>");
				childNodeListLength = childNodeList.getLength();
				System.out.println("editFlag  -->>[" + editFlag + "]");
				for (ctr = 0; ctr < childNodeListLength; ctr++) {
					childNode = childNodeList.item(ctr);
					childNodeName = childNode.getNodeName();
					if (childNodeName.trim().equalsIgnoreCase("reg_code")) {
						regCode = this.genericUtility.getColumnValue("reg_code", dom);

						if(regCode!=null){
							cnt=0;
							sql = "select count(*) as cnt from reg_requirements where reg_code=?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, regCode);
							rs = pstmt.executeQuery();
							if (rs.next()) {
								cnt = rs.getInt("cnt");
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;
							
							if(cnt==0){
								errCode = "VTRCODEXT";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}else {
							errCode = "VTRCODNULL";
							errList.add(errCode);
							errFields.add(childNodeName.toLowerCase());
						}
					} else if(childNodeName.trim().equalsIgnoreCase("reg_date")){
						regDate = this.genericUtility.getColumnValue("reg_date", dom);
						if(regDate==null || regDate.trim().length()<=0){
							errCode = "VTREGNULL";
							errList.add(errCode);
							errFields.add(childNodeName.toLowerCase());
						}
					}
					else if(childNodeName.trim().equalsIgnoreCase("valid_upto")){
						validUpto = this.genericUtility.getColumnValue("valid_upto", dom);
						regDate = this.genericUtility.getColumnValue("reg_date", dom);

						if(validUpto!=null){
							Timestamp validUptoDate = Timestamp.valueOf(genericUtility.getValidDateString(validUpto,
									genericUtility.getApplDateFormat(),genericUtility.getDBDateFormat()) + " 00:00:00.0");
							Timestamp regDateNew = Timestamp.valueOf(genericUtility.getValidDateString(regDate,
									genericUtility.getApplDateFormat(),genericUtility.getDBDateFormat()) + " 00:00:00.0");

							if(validUptoDate.compareTo(regDateNew)<=0)
							{
								errCode = "VTVALREGDT";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}else{
							errCode = "VTVALNULL";
							errList.add(errCode);
							errFields.add(childNodeName.toLowerCase());
						}				
					}
				}
				valueXmlString.append("</Detail1>");

			}
			int errListSize = errList.size();
			cnt = 0;
			String errFldName = null;
			if ((errList != null) && (errListSize > 0)) {
				for (cnt = 0; cnt < errListSize; cnt++) {
					errCode = (String) errList.get(cnt);
					errFldName = (String) errFields.get(cnt);
					errString = getErrorString(errFldName, errCode, userId);
					errorType = errorType(conn, errCode);
					if (errString.length() > 0) {
						String bifurErrString = errString.substring(
								errString.indexOf("<Errors>") + 8,
								errString.indexOf("<trace>"));
						bifurErrString = bifurErrString
								+ errString.substring(
										errString.indexOf("</trace>") + 8,
										errString.indexOf("</Errors>"));
						errStringXml.append(bifurErrString);
						errString = "";
					}
					if (errorType.equalsIgnoreCase("E")) {
						break;
					}
				}

				errStringXml.append("</Errors> </Root> \r\n");
			} else {
				errStringXml = new StringBuffer("");
			}

		} catch (Exception e) {
			e.printStackTrace();
			errString = e.getMessage();
			throw new ITMException(e);
		} finally {
			try {
				if (conn != null) {
					if (rs != null) {
						rs.close();
						rs = null;
					}
					if (pstmt != null) {
						pstmt.close();
						pstmt = null;
					}

					conn.close();
				}
				conn = null;
			} catch (Exception d) {
				d.printStackTrace();
				throw new ITMException(d);
			}
		}
		errString = errStringXml.toString();
		return errString;
	}

	public String itemChanged(String xmlString, String xmlString1,
			String xmlString2, String objContext, String currentColumn,
			String editFlag, String xtraParams) throws RemoteException,
			ITMException {
		Document dom = null;
		Document dom1 = null;
		Document dom2 = null;
		String valueXmlString = "";
		try {
			dom = parseString(xmlString);
			dom1 = parseString(xmlString1);
			if (xmlString2.trim().length() > 0) {
				dom2 = parseString(xmlString2);
			}
			System.out.println("HELLO1 PRINT");
			valueXmlString = itemChanged(dom, dom1, dom2, objContext,currentColumn, editFlag, xtraParams);
			System.out.println("valueXmlString[" + valueXmlString + "]");
		} catch (Exception e) {
			System.out.println("Exception : [MiscVal][itemChanged( String, String )] :==>\n"+ e.getMessage());
			throw new ITMException(e);
		}
		System.out.println("VALUE HELLO PRINTA[" + valueXmlString + "]");
		return valueXmlString;
	}

	public String itemChanged(Document dom, Document dom1, Document dom2,
			String objContext, String currentColumn, String editFlag,
			String xtraParams) throws RemoteException, ITMException {

		System.out.println("sTART PRINT ");
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;
		String childNodeName = null;
		String sql = "",sql1 = "",sql2 = "";
		Connection conn = null;
		PreparedStatement pstmt = null;
		PreparedStatement pstmt1 = null;
		PreparedStatement pstmt2 = null;
		ResultSet rs = null;
		ResultSet rs1 = null;
		ResultSet rs2 = null;
		String resBKList = "",lsDescr = "",lsBKListed = "";			
		String lsNull = "",nullVarr="",ldDate="";
		String custCodeBill="",custCode="",stanCode="",currCode="",countCode="";
		String lsStateCode="",lsCity="",lsPin="";
		String custName="",chqName="",terrCode="",terrDesc="",salesPers="",spName="",contactCode="",groupCode="";
		String bankCode="",bankName="";
		String channelPartner="";
		String crTerm="",crDays="";
		String rateRound="";
		String name="", shName="", contPers="", contPfx="", addr1="", addr2="",addr3="", stateCode="", 
				tele1="", tele2="", tele3="",teleExt="", fax="", emailAddr="", ediAddr="";
		String contPfx1="",contPers1="",Add1="",emailAddr1="",ediAddr1="",keyFlag="";
		String regCode="",regDescr="",empCodeOrd="",empFname="",empLname="",empCodeOrd1="",deptCode="";
		int ctr = 0;
		int currentFormNo = 0;
		java.util.Date reqDate = null;
		int childNodeListLength = 0;
		java.util.Date statusDate = null;
		ArrayList errList = new ArrayList();
		ArrayList errFields = new ArrayList();
		SimpleDateFormat sdf;
		StringBuffer valueXmlString = new StringBuffer();
		GenericUtility genericUtility = GenericUtility.getInstance();
		ConnDriver connDriver = new ConnDriver();

		try {
			sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());

			conn = connDriver.getConnectDB("DriverITM");
			conn.setAutoCommit(false);
			connDriver = null;
			this.finCommon = new FinCommon();
			SimpleDateFormat simpleDateFormat = new SimpleDateFormat(
					genericUtility.getApplDateFormat());
			if ((objContext != null) && (objContext.trim().length() > 0)) {
				currentFormNo = Integer.parseInt(objContext);
			}
			valueXmlString = new StringBuffer(
					"<?xml version = \"1.0\"?> <Root> <header> <editFlag>");
			valueXmlString.append(editFlag).append("</editFlag> </header>");
			switch (currentFormNo) {
			case 1:
				parentNodeList = dom.getElementsByTagName("Detail1");
				parentNode = parentNodeList.item(0);
				childNodeList = parentNode.getChildNodes();
				valueXmlString.append("<Detail1>");
				childNodeListLength = childNodeList.getLength();
				do {
					childNode = childNodeList.item(ctr);
					childNodeName = childNode.getNodeName();
					if (childNodeName.equals(currentColumn)) {
						childNode.getFirstChild();
					}

					ctr++;
				} while ((ctr < childNodeListLength)&& (!childNodeName.equals(currentColumn)));
				System.out.println("CURRENT COLUMN [" + currentColumn + "]");

				if (currentColumn.trim().equalsIgnoreCase("itm_defaultedit")) {
					resBKList = checkNull(this.genericUtility.getColumnValue("reas_code__bklist", dom));
					sql = "select descr from   gencodes where  fld_name = 'REAS_CODE__BKLIST' "
							+ "and fld_value = ?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, resBKList);
					rs = pstmt.executeQuery();
					if (rs.next()) {
						lsDescr = checkNull(rs.getString("descr"));
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					valueXmlString.append("<bklist_reason ><![CDATA[" + lsDescr + "]]></bklist_reason>");

					lsBKListed = checkNull(this.genericUtility.getColumnValue("black_listed", dom));
					if (lsBKListed.equalsIgnoreCase("N")) {
						valueXmlString.append("<reas_code__bklist protect = \"1\"><![CDATA[" + lsNull + "]]></reas_code__bklist>");
						valueXmlString.append("<bklist_reason ><![CDATA[" + lsNull + "]]></bklist_reason>");
					} else {
						valueXmlString.append("<reas_code__bklist><![CDATA[" + lsNull + "]]></reas_code__bklist>");
					}
				} else if (currentColumn.trim().equalsIgnoreCase("itm_default")) {
//					valueXmlString.append("<emp_code__ord ><![CDATA[" + login_emp_code + "]]></emp_code__ord>");
//					valueXmlString.append("<emp_code__ord1 ><![CDATA[" + login_emp_code + "]]></emp_code__ord1>");
					
					resBKList = checkNull(this.genericUtility.getColumnValue("reas_code__bklist", dom));
					sql = "select descr from   gencodes where  fld_name = 'REAS_CODE__BKLIST' "
							+ "and fld_value = ?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, resBKList);
					rs = pstmt.executeQuery();
					if (rs.next()) {
						lsDescr = checkNull(rs.getString("descr"));
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;

					valueXmlString.append("<bklist_reason ><![CDATA[" + lsDescr + "]]></bklist_reason>");

					lsBKListed = checkNull(this.genericUtility.getColumnValue("black_listed", dom));
					if (lsBKListed.equalsIgnoreCase("N")) {
						valueXmlString.append("<reas_code__bklist protect = \"1\"><![CDATA[" + lsNull + "]]></reas_code__bklist>");
						valueXmlString.append("<bklist_reason ><![CDATA[" + lsNull + "]]></bklist_reason>");
					} else {
						valueXmlString.append("<reas_code__bklist><![CDATA[" + lsNull + "]]></reas_code__bklist>");
					}
				}else if (currentColumn.trim().equalsIgnoreCase("cust_code")) {
					custCode = checkNull(this.genericUtility.getColumnValue("cust_code", dom));
						
					custCodeBill = checkNull(this.genericUtility.getColumnValue(
							"cust_code__bil", dom));
					//					if(custCodeBill==null && custCodeBill.trim().length()<=0){										
						valueXmlString.append("<cust_code__bil><![CDATA[" + custCode + "]]></cust_code__bil>");
						valueXmlString.append("<group_code><![CDATA[" + custCode + "]]></group_code>");
//					}

				} else if (currentColumn.trim().equalsIgnoreCase("stan_code")) 
				{
					stanCode = checkNull(this.genericUtility.getColumnValue("stan_code", dom));
					sql = "select state_code, city, pin from station where stan_code = ?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, stanCode);
					rs = pstmt.executeQuery();
					if (rs.next()) {
						lsStateCode = checkNull(rs.getString("state_code"));
						lsCity = checkNull(rs.getString("city"));
						lsPin = checkNull(rs.getString("pin"));
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;

					if(lsStateCode!=null && lsStateCode.trim().length()>0){
						sql1 = "select count_code from state where state_code =?";
						pstmt1 = conn.prepareStatement(sql1);
						pstmt1.setString(1, lsStateCode);
						rs1 = pstmt1.executeQuery();
						if (rs1.next()) {
							countCode = checkNull(rs1.getString("count_code"));				
						}
						rs1.close();
						rs1 = null;
						pstmt1.close();
						pstmt1 = null;
					}

					if(countCode!=null && countCode.trim().length()>0){
						sql2 = "select curr_code from country where count_code =?";
						pstmt2 = conn.prepareStatement(sql2);
						pstmt2.setString(1, countCode);
						rs2 = pstmt2.executeQuery();
						if (rs2.next()) {
							currCode = checkNull(rs2.getString("curr_code"));				
						}
						rs2.close();
						rs2 = null;
						pstmt2.close();
						pstmt2 = null;
					}

					valueXmlString.append("<state_code><![CDATA[" + lsStateCode + "]]></state_code>");
					valueXmlString.append("<count_code><![CDATA[" + countCode + "]]></count_code>");
					valueXmlString.append("<curr_code><![CDATA[" + currCode + "]]></curr_code>");
				}else if(currentColumn.trim().equalsIgnoreCase("cust_name")){
					custName = checkNull(this.genericUtility.getColumnValue("cust_name", dom));
					chqName=checkNull(this.genericUtility.getColumnValue("chq_name", dom));
					if(chqName==null || chqName.trim().length()<=0){
						valueXmlString.append("<chq_name><![CDATA[" + custName + "]]></chq_name>");
					}
				}else if(currentColumn.trim().equalsIgnoreCase("terr_code")){
					terrCode = checkNull(this.genericUtility.getColumnValue("terr_code", dom));
					
					if(terrCode!=null && terrCode.trim().length()>0){
						sql = "select descr from territory where terr_code =?";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, terrCode);
						rs = pstmt.executeQuery();
						if (rs.next()) {
							terrDesc = checkNull(rs.getString("descr"));
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
					}
					
					valueXmlString.append("<territory_descr><![CDATA[" + terrDesc + "]]></territory_descr>");
				}else if(currentColumn.trim().equalsIgnoreCase("sales_pers")){
					salesPers = checkNull(this.genericUtility.getColumnValue("sales_pers", dom));
					sql = "select sp_name from sales_pers where sales_pers=?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, salesPers);
					rs = pstmt.executeQuery();
					if (rs.next()) {
						spName = checkNull(rs.getString("sp_name"));
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					valueXmlString.append("<sp_name><![CDATA[" + spName + "]]></sp_name>");
				}else if(currentColumn.trim().equalsIgnoreCase("contact_code")){
					contactCode = checkNull(this.genericUtility.getColumnValue("contact_code", dom));

					sql = "select name, sh_name, cont_pers, cont_pfx, addr1, addr2,addr3, city, pin, state_code,"
							+ " count_code, tele1, tele2, tele3,tele_ext, fax, email_addr, edi_addr"
							+ " from contact where contact_code = ?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, contactCode);
					rs = pstmt.executeQuery();
					if (rs.next()) {
						name = checkNull(rs.getString("name"));
						shName = checkNull(rs.getString("sh_name"));
						contPers = checkNull(rs.getString("cont_pers"));
						contPfx = checkNull(rs.getString("cont_pfx"));
						addr1 = checkNull(rs.getString("addr1"));
						addr2 = checkNull(rs.getString("addr2"));
						addr3 = checkNull(rs.getString("addr3"));
						lsCity = checkNull(rs.getString("city"));
						lsPin = checkNull(rs.getString("pin"));
						stateCode = checkNull(rs.getString("state_code"));
						countCode = checkNull(rs.getString("count_code"));
						tele1 = checkNull(rs.getString("tele1"));
						tele2 = checkNull(rs.getString("tele2"));
						tele3 = checkNull(rs.getString("tele3"));
						teleExt = checkNull(rs.getString("tele_ext"));
						fax = checkNull(rs.getString("fax"));
						emailAddr = checkNull(rs.getString("email_addr"));
						ediAddr = checkNull(rs.getString("edi_addr"));
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;

					custName = checkNull(this.genericUtility.getColumnValue("cust_name", dom));
					if(custName==null || custName.trim().length()<=0){
						valueXmlString.append("<cust_name>")
						.append("<![CDATA[" +name+ "]]>")
						.append("</cust_name>");

						valueXmlString.append("<chq_name><![CDATA[" + name + "]]></chq_name>");

						valueXmlString.append("<sh_name><![CDATA[" + shName + "]]></sh_name>");
					}

					contPfx1 = checkNull(this.genericUtility.getColumnValue("cont_pfx", dom));
					
					if(contPfx1==null || contPfx1.trim().length()<=0){
						valueXmlString.append("<cont_pfx><![CDATA[" + contPfx + "]]></cont_pfx>");
					}

					contPers1 = checkNull(this.genericUtility.getColumnValue("cont_pers", dom));
					if(contPers1==null || contPers1.trim().length()<=0){
						valueXmlString.append("<cont_pers><![CDATA[" + contPers + "]]></cont_pers>");
					}

					Add1 = checkNull(this.genericUtility.getColumnValue("addr1", dom));
					if(Add1==null || Add1.trim().length()<=0){
						valueXmlString.append("<addr1><![CDATA[" + addr1 + "]]></addr1>");
						valueXmlString.append("<addr2><![CDATA[" + addr2 + "]]></addr2>");
						valueXmlString.append("<addr3><![CDATA[" + addr3 + "]]></addr3>");

						valueXmlString.append("<city><![CDATA[" + lsCity + "]]></city>");
						valueXmlString.append("<pin><![CDATA[" + lsPin + "]]></pin>");

						valueXmlString.append("<state_code><![CDATA[" + stateCode + "]]></state_code>");
						valueXmlString.append("<count_code><![CDATA[" + countCode + "]]></count_code>");

						valueXmlString.append("<tele1><![CDATA[" + tele1 + "]]></tele1>");
						valueXmlString.append("<tele2><![CDATA[" + tele2 + "]]></tele2>");
						valueXmlString.append("<tele3><![CDATA[" + tele3 + "]]></tele3>");

						valueXmlString.append("<tele_ext><![CDATA[" + teleExt + "]]></tele_ext>");
						valueXmlString.append("<fax ><![CDATA[" + fax + "]]></fax>");
					}

					emailAddr1 = checkNull(this.genericUtility.getColumnValue("email_addr", dom));
					if(emailAddr1==null || emailAddr1.trim().length()<=0){
						valueXmlString.append("<email_addr ><![CDATA[" + emailAddr + "]]></email_addr>");
					}

					ediAddr1 = checkNull(this.genericUtility.getColumnValue("edi_addr", dom));
					if(ediAddr1==null || ediAddr1.trim().length()<=0){
						valueXmlString.append("<edi_addr ><![CDATA[" + ediAddr + "]]></edi_addr>");
					}

					custCode = checkNull(this.genericUtility.getColumnValue("cust_code", dom));
					sql2 = "select key_flag from transetup where tran_window = 'w_customer'";
					pstmt2 = conn.prepareStatement(sql2);
					rs2 = pstmt2.executeQuery();
					if (rs2.next()) {
						keyFlag = checkNull(rs2.getString("key_flag"));				
					}
					rs2.close();
					rs2 = null;
					pstmt2.close();
					pstmt2 = null;

					if(custCode==null && !(keyFlag.equalsIgnoreCase("A"))){
						contactCode = checkNull(this.genericUtility.getColumnValue("contact_code", dom));
						valueXmlString.append("<cust_code ><![CDATA[" + contactCode + "]]></cust_code>");
					}

					custCodeBill = checkNull(this.genericUtility.getColumnValue("cust_code__bil", dom));
					if(custCodeBill==null || custCodeBill.trim().length()<=0){
						contactCode = checkNull(this.genericUtility.getColumnValue("contact_code", dom));
						valueXmlString.append("<cust_code__bil ><![CDATA[" + contactCode + "]]></cust_code__bil>");
						
						sql = "select cust_name from customer where cust_code =?";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, contactCode);
						rs = pstmt.executeQuery();
						if (rs.next()) {
							custName = checkNull(rs.getString("cust_name"));
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;

						if(custName!=null){
						valueXmlString.append("<customer_cust_name_1 ><![CDATA[" + custName + "]]></customer_cust_name_1>");
						}
					}

					groupCode = checkNull(this.genericUtility.getColumnValue(
							"group_code", dom));
					if(groupCode==null || groupCode.trim().length()<=0){
						contactCode = checkNull(this.genericUtility.getColumnValue("contact_code", dom));
						valueXmlString.append("<group_code ><![CDATA[" + contactCode + "]]></group_code>");
						
						sql = "select cust_name from customer where cust_code =?";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, contactCode);
						rs = pstmt.executeQuery();
						if (rs.next()) {
							custName = checkNull(rs.getString("cust_name"));
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;

						if(custName!=null){
						valueXmlString.append("<customer_cust_name ><![CDATA[" + custName + "]]></customer_cust_name>");
						}
					}
				}else if(currentColumn.trim().equalsIgnoreCase("bank_code")){
					bankCode = checkNull(this.genericUtility.getColumnValue("bank_code", dom));

					sql = "select bank_name from bank where bank_code=?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, bankCode);
					rs = pstmt.executeQuery();
					if (rs.next()) {
						bankName = checkNull(rs.getString("bank_name"));
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;

					valueXmlString.append("<bank_name ><![CDATA[" + bankName + "]]></bank_name>");
				}else if(currentColumn.trim().equalsIgnoreCase("channel_partner")){
					channelPartner = checkNull(this.genericUtility.getColumnValue("channel_partner", dom));

					if(channelPartner.equalsIgnoreCase("Y")){
						valueXmlString.append("<site_code protect = \"0\"><![CDATA[]]></site_code>");
						valueXmlString.append("<fin_link protect = \"0\"><![CDATA[]]></fin_link>");
						valueXmlString.append("<dis_link protect = \"0\"><![CDATA[]]></dis_link>");
					}else{
						valueXmlString.append("<site_code protect = \"1\"><![CDATA[" + nullVarr + "]]></site_code>");
						valueXmlString.append("<fin_link protect = \"1\"><![CDATA[" + nullVarr + "]]></fin_link>");
						valueXmlString.append("<dis_link protect = \"1\"><![CDATA[" + nullVarr + "]]></dis_link>");
					}
				}else if(currentColumn.trim().equalsIgnoreCase("black_listed")){
					lsBKListed = checkNull(this.genericUtility.getColumnValue("black_listed", dom));

					System.out.println("black_listed :- "+lsBKListed);
					if(lsBKListed.equalsIgnoreCase("N")){
						valueXmlString.append("<black_listed_date protect = \"1\"><![CDATA[" + ldDate + "]]></black_listed_date>");
						valueXmlString.append("<reas_code__bklist protect = \"1\"><![CDATA[" + lsNull + "]]></reas_code__bklist>");
						valueXmlString.append("<bklist_reason protect = \"1\"><![CDATA[" + lsNull + "]]></bklist_reason>");
					}else{
						valueXmlString.append("<black_listed_date protect = \"0\"><![CDATA[]]></black_listed_date>");
						valueXmlString.append("<reas_code__bklist protect = \"0\"><![CDATA[]]></reas_code__bklist>");
					}
				}else if(currentColumn.trim().equalsIgnoreCase("reas_code__bklist")){
					resBKList = checkNull(this.genericUtility.getColumnValue("reas_code__bklist", dom));

					sql = "select descr from gencodes where  fld_name = 'REAS_CODE__BKLIST' and fld_value =?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, resBKList);
					rs = pstmt.executeQuery();
					if (rs.next()) {
						lsDescr = checkNull(rs.getString("descr"));
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;

					valueXmlString.append("<bklist_reason >")
					.append("<![CDATA[" + lsDescr + "]]>")
					.append("</bklist_reason>");
				}else if(currentColumn.trim().equalsIgnoreCase("cr_term")){
					crTerm = checkNull(this.genericUtility.getColumnValue("cr_term", dom));

					sql = "select cr_days from crterm where cr_term =?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, crTerm);
					rs = pstmt.executeQuery();
					if (rs.next()) {
						crDays = checkNull(rs.getString("cr_days"));
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;

					valueXmlString.append("<credit_prd ><![CDATA[" + crDays + "]]></credit_prd>");
				}else if(currentColumn.trim().equalsIgnoreCase("rate_round")){
					rateRound = checkNull(this.genericUtility.getColumnValue("rate_round", dom));
					
					if(rateRound.equalsIgnoreCase("N")){
						valueXmlString.append("<rate_round_to protect = \"1\"><![CDATA[]]></rate_round_to>");
					}else{
						valueXmlString.append("<rate_round_to protect = \"0\"><![CDATA[]]></rate_round_to>");
					}
				}else if(currentColumn.trim().equalsIgnoreCase("group_code")){
					groupCode = checkNull(this.genericUtility.getColumnValue("group_code", dom));

					sql = "select cust_name from customer where cust_code =?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, groupCode);
					rs = pstmt.executeQuery();
					if (rs.next()) {
						custName = checkNull(rs.getString("cust_name"));
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;

					valueXmlString.append("<customer_cust_name ><![CDATA[" + custName + "]]></customer_cust_name>");
				}else if(currentColumn.trim().equalsIgnoreCase("cust_code__bil")){
					custCodeBill = checkNull(this.genericUtility.getColumnValue("cust_code__bil", dom));

					sql = "select cust_name from customer where cust_code =?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, custCodeBill);
					rs = pstmt.executeQuery();
					if (rs.next()) {
						custName = checkNull(rs.getString("cust_name"));
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;

					
					valueXmlString.append("<customer_cust_name_1 ><![CDATA[" + custName + "]]></customer_cust_name_1>");
				}else if(currentColumn.trim().equalsIgnoreCase("emp_code__ord")){
					empCodeOrd = checkNull(this.genericUtility.getColumnValue("emp_code__ord", dom));
					if(empCodeOrd!=null){
						sql = "select emp_fname,emp_lname,dept_code from employee where emp_code=?";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, empCodeOrd);
						rs = pstmt.executeQuery();
						if (rs.next()) {
							empFname = checkNull(rs.getString("emp_fname"));
							empLname = checkNull(rs.getString("emp_lname"));
							deptCode = checkNull(rs.getString("dept_code"));
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;

						valueXmlString.append("<emp_fname ><![CDATA[" + empFname + "]]></emp_fname>");
						valueXmlString.append("<emp_lname ><![CDATA[" + empLname + "]]></emp_lname>");
						valueXmlString.append("<dept_code ><![CDATA[" + deptCode + "]]></dept_code>");
					}
				}else if(currentColumn.trim().equalsIgnoreCase("emp_code__ord1")){
					empCodeOrd1 = checkNull(this.genericUtility.getColumnValue("emp_code__ord1", dom));
					if(empCodeOrd1!=null){
						sql = "select emp_fname,emp_lname,dept_code from employee where emp_code=?";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, empCodeOrd1);
						rs = pstmt.executeQuery();
						if (rs.next()) {
							empFname = checkNull(rs.getString("emp_fname"));
							empLname = checkNull(rs.getString("emp_lname"));
							deptCode = checkNull(rs.getString("dept_code"));
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;

						valueXmlString.append("<employee_emp_fname ><![CDATA[" + empFname + "]]></employee_emp_fname>");
						valueXmlString.append("<employee_emp_lname ><![CDATA[" + empLname + "]]></employee_emp_lname>");
						valueXmlString.append("<dept_code ><![CDATA[" + deptCode + "]]></dept_code>");
					}
				}
				valueXmlString.append("</Detail1>");
				break;
			case 2:
				parentNodeList = dom.getElementsByTagName("Detail2");
				parentNode = parentNodeList.item(0);
				childNodeList = parentNode.getChildNodes();
				valueXmlString.append("<Detail2>");
				childNodeListLength = childNodeList.getLength();

				do {
					childNode = childNodeList.item(ctr);
					childNodeName = childNode.getNodeName();
					if (childNodeName.equals(currentColumn)) {
						childNode.getFirstChild();
					}

					ctr++;
				} while ((ctr < childNodeListLength)
						&& (!childNodeName.equals(currentColumn)));
				System.out.println("CURRENT COLUMN [" + currentColumn + "]");

				if (currentColumn.trim().equalsIgnoreCase("reg_code")) {
					regCode = checkNull(this.genericUtility.getColumnValue("reg_code", dom));
					
					
					sql = "select descr from reg_requirements where reg_code=?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, regCode);
					rs = pstmt.executeQuery();
					if (rs.next()) {
						regDescr = checkNull(rs.getString("descr"));
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					
					System.out.println("Registration descr from field value $$$$$$$$$$$$$$$444444444444444444444:-["+regDescr+"]");

					valueXmlString.append("<descr ><![CDATA[" + regDescr + "]]></descr>");
				} 
				valueXmlString.append("</Detail2>");

			}
			valueXmlString.append("</Root>");
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("Exception ::" + e.getMessage());
			throw new ITMException(e);
		} finally {
			try {
				if (conn != null) {
					if (pstmt != null)
						pstmt.close();
					if (rs != null)
						rs.close();
					rs = null;
					pstmt = null;
					conn.close();
					conn = null;
				}
			} catch (Exception d) {
				d.printStackTrace();
				throw new ITMException(d);
			}
		}
		return valueXmlString.toString();
	}

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

	}

	private double checkDoubleNull(String str) {
		if (str == null || str.trim().length() == 0) {
			return 0.0;
		} else {
			return Double.parseDouble(str);
		}

	}

	private String errorType(Connection conn, String errorCode)
			throws ITMException {
		String msgType = "";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			String sql = "SELECT MSG_TYPE FROM MESSAGES WHERE MSG_NO = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, errorCode);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				msgType = rs.getString("MSG_TYPE");
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
			} catch (Exception e) {
				e.printStackTrace();
				throw new ITMException(e);
			}
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
			} catch (Exception e) {
				e.printStackTrace();
				throw new ITMException(e);
			}
		}
		return msgType;
	}

}
