/********************************************************
	Title : DistIssWiz EJB
	Date  : 20 - Aug - 2014
	Author: Deepak Sawant.

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

import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.ejb.ValidatorEJB;
import ibase.webitm.ejb.dis.DistCommon;
import ibase.webitm.ejb.dis.adv.DespatchAct;
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.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.StringTokenizer;

import javax.ejb.Stateless;

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

@Stateless
public class DespatchWiz extends ValidatorEJB implements DespatchWizLocal, DespatchWizRemote {

	GenericUtility genericUtility = GenericUtility.getInstance();
	String userId = null;
	String chgUser = null;
	String chgTerm = null;
	NumberFormat nf = null;
	boolean isError=false;



	public DespatchWiz() 
	{
		System.out.println("^^^^^^^ inside DespatchWiz  Wizard ^^^^^^^");
	}

	public String wfValData(String xmlString, String xmlString1, String xmlString2, String objContext, String editFlag, String xtraParams) throws RemoteException, ITMException {
		System.out.println("^^^^^^^ inside DespatchWiz  Wizard >^^^^^^^");
		Document dom = null;
		Document dom1 = null;
		Document dom2 = null;
		String errString = "";

		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;
		String childNodeName = "";

		try {
			dom = parseString(xmlString);
			dom1 = parseString(xmlString1);
			if (xmlString2.trim().length() > 0) 
			{
				dom2 = parseString("<Root>" + xmlString2 + "</Root>");
			}
			if (objContext != null && Integer.parseInt(objContext) == 1) 
			{
				parentNodeList = dom2.getElementsByTagName("Header0");
				parentNode = parentNodeList.item(1);
				childNodeList = parentNode.getChildNodes();
				for (int x = 0; x < childNodeList.getLength(); x++) 
				{
					childNode = childNodeList.item(x);
					childNodeName = childNode.getNodeName();
					if (childNodeName.equalsIgnoreCase("Detail1")) 
					{
						errString = wfValData(dom, dom1, dom2, "1", editFlag, xtraParams);
						if (errString != null && errString.trim().length() > 0)
							break;
					} else if (childNodeName.equalsIgnoreCase("Detail2")) 
					{
						errString = wfValData(dom, dom1, dom2, "2", editFlag, xtraParams);
						break;
					}
				}
			} else 
			{
				errString = wfValData(dom, dom1, dom2, objContext, editFlag, xtraParams);
			}
		} catch (Exception e) {
			System.out.println("Exception : Inside DocumentMaster wfValData Method ..> " + e.getMessage());
			throw new ITMException(e);
		}
		return (errString);
	}

	public String wfValData(Document dom, Document dom1, Document dom2, String objContext, String editFlag, String xtraParams) throws RemoteException, ITMException {
		System.out.println("----------in wfvalData of DespatchWiz........");
		GenericUtility genericUtility;
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;
		int ctr = 0, currentFormNo = 0, childNodeListLength = 0, cnt = 0;
		String childNodeName = null;
		String errString = "";
		String errCode = "";
		Connection conn = null;
		String userId = "";
		PreparedStatement pstmt = null ;
		ResultSet rs = null;
		String sql = "",locCode ="",invstat="",aval="",avalyn="",status="";
        double qtyConf=0,qtyShip=0,totQty=0,detquantity=0,stkquantity=0,quantity=0,totquantity=0;
		String squantity="",distOrder = "",itemCode="",confirmed="",tranId="",siteCode="",lotSl="",addUser="",tranCode="";
		
		String sorderNo="";
		try {
			genericUtility = GenericUtility.getInstance();
			System.out.println("editFlag------>>["+editFlag+"]");
			System.out.println("xtraParam----->>["+xtraParams+"]");
			System.out.println("DOM---->>["+genericUtility.serializeDom(dom).toString()+"]");
			System.out.println("DOM1----->>["+genericUtility.serializeDom(dom1).toString()+"]");
			System.out.println("DOM2----->>["+genericUtility.serializeDom(dom2).toString()+"]");	

			ConnDriver connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");
			conn.setAutoCommit(false);
			connDriver = null;
			userId = getValueFromXTRA_PARAMS(xtraParams, "loginCode");
			
			if (objContext != null && objContext.trim().length() > 0) {
				currentFormNo = Integer.parseInt(objContext);
			}
			switch (currentFormNo) {
			case 1:
				parentNodeList = dom.getElementsByTagName("Detail1");
				parentNode = parentNodeList.item(0);
				childNodeList = parentNode.getChildNodes();
				childNodeListLength = childNodeList.getLength();

				for (ctr = 0; ctr < childNodeListLength; ctr++) {

					System.out.println("childNodeName------->>["+childNodeName+"]");
					childNode = childNodeList.item(ctr);
					childNodeName = childNode.getNodeName();
					if(childNodeName.equalsIgnoreCase("sale_order_no"))
					{
						int despCount=0;
						sorderNo = checkNull(genericUtility.getColumnValue("sale_order_no",dom));
						System.out.println("sorderNo---->>["+sorderNo+"]");
						if (sorderNo.length() == 0){
						errCode = "VTSORDNNN";
						errString = getErrorString("sale_order_no",errCode,userId);
						break;
						}else{
							cnt=getDBRowCount(conn,"sorder","sale_order",sorderNo);
							System.out.println("cnt1------>>["+cnt+"]");
							if(cnt==0){
								errCode = "VTSORDNND";
								errString = getErrorString("sale_order_no",errCode,userId);
								break;
							}else{
								confirmed=checkNull(getColumnDescr(conn,"confirmed","sorder","sale_order",sorderNo));
								
								System.out.println("Sale order Confirmed---->>["+confirmed+"]");
								sql="select status,confirmed from sorder where sale_order = ? ";
								pstmt=conn.prepareStatement(sql);
								pstmt.setString(1, sorderNo);
								rs=pstmt.executeQuery();
								if(rs.next()){
									status=rs.getString(1);
									confirmed=rs.getString(2);
																		
								}
								if(rs!=null){
								rs.close();
								rs=null;
								}
								if(pstmt!=null){
								pstmt.close();
								pstmt=null;
								}
								confirmed=confirmed == null ? "N" :confirmed.trim();
								status=status == null ? "P" :status.trim();
								despCount=getDBRowCount(conn,"despatch","sord_no",sorderNo);
								System.out.println("Sale order Confirmed---->>["+confirmed+"]");
								System.out.println("Sale order status---->>["+status+"]");
								System.out.println("despCount---->>["+despCount+"]");
								
								if("N".equalsIgnoreCase(confirmed)){
									errCode = "VTSORDNNC";
									errString = getErrorString("sale_order_no",errCode,userId);
									break;
								}else if(! "P".equalsIgnoreCase(status)){
									errCode = "VTSORDSNP";
									errString = getErrorString("sale_order_no",errCode,userId);
									break;
								}else if(despCount > 0){
									errCode = "VTSORDDEF";
									errString = getErrorString("sale_order_no",errCode,userId);
									break;
								}
							}
							
							
						} //end main else

					}

				}
				break;
			case 2:


				

				parentNodeList = dom2.getElementsByTagName("Detail2");
				parentNode = parentNodeList.item(0);
				System.out.println("parentNode >>>{"+parentNode+"}");
				childNodeList = parentNode.getChildNodes();
				childNodeListLength = childNodeList.getLength();


				for(ctr = 0; ctr < childNodeListLength; ctr++)
				{

					childNode = childNodeList.item(ctr);
					childNodeName = childNode.getNodeName();
					System.out.println("value of child node : "+childNode);



					if("E".equalsIgnoreCase(editFlag))
					{
						addUser = genericUtility.getColumnValue("add_user",dom2);
						chgUser = genericUtility.getColumnValue("chg_user",dom2);
						System.out.println("addUser><><"+addUser+"chgUserDD"+chgUser);
						if ((addUser != null && addUser.trim().length() > 0) && (chgUser != null && chgUser.trim().length() > 0))
						{
							if(!addUser.equalsIgnoreCase(chgUser))
							{
								errCode = "DIDOADDUM";
								errString = getErrorString("add_user",errCode,userId);
								break;
							}
						}
					}

					if(childNodeName.equalsIgnoreCase("tran_code"))
					{
						tranCode = genericUtility.getColumnValue("tran_code",dom2);

						System.out.println("tranCode>>"+tranCode);

						if (tranCode == null || tranCode.trim().length() == 0)
						{
							errCode = "DIDOTRCONU";
							errString = getErrorString("tran_code",errCode,userId);
							break;
						}
						else
						{
							sql = "select count(1) from transporter where tran_code = ?";
							pstmt=conn.prepareStatement(sql);
							pstmt.setString(1,tranCode);
							rs = pstmt.executeQuery();
							if(rs.next())
							{
								cnt = rs.getInt(1);
							}
							pstmt.close();
							rs.close();
							pstmt = null;
							rs = null;

							if(cnt == 0)
							{
								errCode = "DIDOTRCONE";
								errString = getErrorString("tran_code",errCode,userId);
								break;
							}
						}
					}


				}
				break;
			case 3:

				parentNodeList = dom2.getElementsByTagName("Detail3");
				parentNode = parentNodeList.item(0);
				childNodeList = parentNode.getChildNodes();
				childNodeListLength = childNodeList.getLength();

				for(ctr = 0; ctr < childNodeListLength; ctr++)
				{
					childNode = childNodeList.item(ctr);
					childNodeName = childNode.getNodeName();
					System.out.println("value of child node : "+childNode);

					if(childNodeName.equalsIgnoreCase("lot_sl"))
					{
						siteCode = genericUtility.getColumnValue("site_code",dom2);
						lotSl = genericUtility.getColumnValue("lot_sl",dom2);
						distOrder = genericUtility.getColumnValue("dist_order",dom2);

						if (lotSl == null || lotSl.trim().length() == 0)
						{
							errCode = "DIDOLSNULL";
							errString = getErrorString("lot_sl",errCode,userId);
							break;
						}
						else
						{
							sql = "select count(1) from stock where site_code = ? AND LOT_SL = ? AND QUANTITY > 0";
							pstmt=conn.prepareStatement(sql);
							pstmt.setString(1,siteCode);
							pstmt.setString(2,lotSl);
							rs = pstmt.executeQuery();
							if(rs.next())
							{
								cnt = rs.getInt(1);
							}
							pstmt.close();
							rs.close();
							pstmt = null;
							rs = null;

							if(cnt == 0)
							{
								errCode = "DIDOLSSTK";
								errString = getErrorString("lot_sl",errCode,userId);
								break;
							}
						}


						
						
						sql = "select qty_confirm,qty_shipped from distorder_det where dist_order = ?";
						pstmt=conn.prepareStatement(sql);
						pstmt.setString(1,distOrder);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							qtyConf = rs.getDouble(1);
							qtyShip = rs.getDouble(2);
						}
						pstmt.close();
						rs.close();
						pstmt = null;
						rs = null;

						totQty = qtyConf - qtyShip;
						
						if(totQty <= 0)
						{
							errCode = "DIDOTQLTZ";
							errString = getErrorString("lot_sl",errCode,userId);
							break;
						}
						
					}
					else if(childNodeName.equalsIgnoreCase("item_code"))
					{
						System.out.println("----------in wfvaldata of item_code............");
						ArrayList <String> itemCodeList=new ArrayList<String>();
						String itemCodeL="";
						sorderNo = checkNull(genericUtility.getColumnValue("sale_order_no", dom1));
						itemCodeL = checkNull(genericUtility.getColumnValue("item_code", dom2,"3"));
						//lotSlL=checkNull(genericUtility.getColumnValue("lot_sl", dom2,"3"));
						
						System.out.println("sorderNo------>>["+sorderNo+"]");
						System.out.println("itemCodeL------>>["+itemCodeL+"]");						
						if (itemCode == null || itemCode.trim().length() == 0)
						{
							errCode = "DIDIICNULL";
							errString = getErrorString("item_code",errCode,userId);
							break;
						}
						else  
						{
							sql = "select item_code from sorddet where sale_order = ?";
							pstmt=conn.prepareStatement(sql);
							pstmt.setString(1,distOrder);
							rs = pstmt.executeQuery();
							while(rs.next())
							{
								itemCodeList.add(checkNull(rs.getString(1)));
							}
							System.out.println("itemCodeList------>>["+itemCodeList+"]");
							pstmt.close();
							rs.close();
							pstmt = null;
							rs = null;

							if(!(itemCodeList.contains(itemCodeL)))
							{
								errCode = "DIDIICNOMA";
								errString = getErrorString("item_code",errCode,userId);
								break;
							}
						}
					}

					else if(childNodeName.equalsIgnoreCase("quantity"))
					{	
						sorderNo = checkNull(genericUtility.getColumnValue("sale_order_no", dom1));
						squantity = genericUtility.getColumnValue("quantity",dom2);
						siteCode = genericUtility.getColumnValue("site_code",dom2);
						lotSl = genericUtility.getColumnValue("lot_sl",dom2);
						itemCode = checkNull(genericUtility.getColumnValue("item_code", dom2,"3"));
                        System.out.println("squantity"+squantity+"workOrder<><><>"+distOrder+">>D"+siteCode+"FDFf"+lotSl);
						if (squantity == null || squantity.trim().length() == 0)
						{
							quantity = 0;
						}
						else
						{
							quantity = Double.parseDouble(squantity);
						}
						
						sql = "select quantity from stock where site_code = ? AND LOT_SL = ? AND QUANTITY > 0";
						pstmt=conn.prepareStatement(sql);
						pstmt.setString(1,siteCode);
						pstmt.setString(2,lotSl);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							stkquantity = rs.getDouble(1);
						}
						pstmt.close();
						rs.close();
						pstmt = null;
						rs = null;
						System.out.println("stkquantity>>"+stkquantity);
						sql = "select sum(quantity) from sorddet where sale_order = ? and item_code = ?";
						pstmt=conn.prepareStatement(sql);
						pstmt.setString(1,sorderNo);
						pstmt.setString(2,itemCode);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							detquantity = rs.getDouble(1);
						}
						pstmt.close();
						rs.close();
						pstmt = null;
						rs = null;
						System.out.println("detquantity------>>["+detquantity+"]");						
						totquantity = detquantity + quantity;
						if(totquantity > stkquantity)
						{
							errCode = "DIDOQUANST";
							errString = getErrorString("quantity",errCode,userId);
							break;
						}
				}
					

					else if(childNodeName.equalsIgnoreCase("loc_code"))
					{
						sorderNo = checkNull(genericUtility.getColumnValue("sale_order_no", dom1));
						locCode = genericUtility.getColumnValue("loc_code",dom2);
						tranId = genericUtility.getColumnValue("tran_id",dom2);
						System.out.println("sorderNo---loc_code---->>["+sorderNo+"]");
						if (locCode == null || locCode.trim().length() == 0)
						{
							errCode = "DIDOLCNULL";
							errString = getErrorString("loc_code",errCode,userId);
							break;
						}else
						{

							sql = "select inv_stat from location where loc_code = ?";
							pstmt=conn.prepareStatement(sql);
							pstmt.setString(1,locCode);
							rs = pstmt.executeQuery();
							if(rs.next())
							{
								invstat =  rs.getString("inv_stat")==null?"":rs.getString("inv_stat").trim();
							}
							pstmt.close();
							rs.close();
							pstmt = null;
							rs = null;
							
							aval=getColumnDescr(conn, "available", "invstat", "inv_stat", invstat);
							if(aval.equalsIgnoreCase("Y")){
							avalyn=getColumnDescr(conn, "available_yn", "sorder", "sale_order", sorderNo);
							if(!aval.equalsIgnoreCase(avalyn)){
								errCode = "VTAVAIL";
								errString = getErrorString("loc_code",errCode,userId);
								break;
								}
							}
						}

					}
				}
				break;
			}
		} catch (Exception e) {
			e.printStackTrace();			
			errString = e.getMessage();
			try {
				conn.rollback();				
			} catch (Exception d) {
				d.printStackTrace();
			}
			throw new ITMException(e);
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				conn = null;
			} catch (Exception d) {
				d.printStackTrace();
			}
		}
		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 {
			System.out.println("currentColumn"+currentColumn);
			System.out.println("editFlag"+editFlag);
			System.out.println("xtraParams"+xtraParams);


			System.out.println("xmlString111>>"+xmlString);
			System.out.println("xmlString222>>"+xmlString1);
			System.out.println("xmlString333>>"+xmlString2);
			dom = parseString(xmlString);
			dom1 = parseString(xmlString1);
			if (xmlString2.trim().length() > 0) {
				dom2 = parseString(xmlString2);
			}
			valueXmlString = itemChanged(dom, dom1, dom2, objContext, currentColumn, editFlag, xtraParams);
		} catch (Exception e) {
			System.out.println("Exception : [itemChanged(String,String)] :==>\n" + e.getMessage());
			throw new ITMException(e);
		}
		return valueXmlString;
	}


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

		StringBuffer valueXmlString = null;
		int currentFormNo = 0, lineNo = 0;
		Connection conn = null;
		double squantity = 0.0;
		PreparedStatement pstmt = null,pstmt1=null;
		ResultSet rs = null ,rs1 = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		ConnDriver connDriver = new ConnDriver();
		SimpleDateFormat simpleDateFormat = null;
		GenericUtility genutility = new GenericUtility();
		String locCode="",lotNo="",itmdesc="",siteCode="",locdesc="";
		ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
		DistCommon disCommon = new DistCommon();
		System.out.println("DOM Elements---->>["+genericUtility.serializeDom(dom).toString()+"]");
		System.out.println("DOM111 Elements---->>["+genericUtility.serializeDom(dom1).toString()+"]");
		System.out.println("DOM222 Elements---->>["+genericUtility.serializeDom(dom2).toString()+"]");
		String distOrder = "",itemCode = "",unit="",packCode="",tranType ="";
		java.sql.Timestamp currDate = null;
		double grossWeight = 0,netWeight=0,tareWeight=0,noOfArt=0;
		SimpleDateFormat sdf = null;
		String currAppdate = "";
		String tranid ="";
		String rate = "";
		double amount = 0;
		int cnt = 0;

		String tranCode = "",tranName="",transMode="";
		StringBuffer detail2xml = new StringBuffer();
		String tranDate = null;

		PreparedStatement pstmt2 =null;
		ResultSet rs2 = null,rs3 = null;


		String unitAlt = null;
		int count = 0;
		double minputQty = 0d, remQuantity = 0d, stockQty = 0d, integralQty = 0d;
		double grossPer = 0d,netPer = 0d,grossWt = 0d,tarePer = 0d,netWt = 0d,tareWt =0d, rateClgVal = 0d, rate2 = 0d;
		double disAmount = 0d, shipperQty = 0d,discount =0;
		int  minShelfLife = 0, noArt1 = 0;
		int mLineNoDist =0;
		double qtyConfirm =0,qtyShipped =0,lcQtyOrderAlt =0,lcFact =0;
		
		
		//Dadaso
		String sql="",currencyCode="",exchRate1="",currCodeFrt="",currCodeIns="",currCodedlv="",stanCode="",custCode="",custCodeDlv="",siteCodeDesc="";
		String stanCodeDlv="",dlvCity="",dlvPin="",countCodeDlv="",stanCodeInit="",status="",statusRemarks="",
				custCodeBil="",dlvAdd1="",dlvAdd2="",dlvAdd3="",despId="",remarksDes="",noArtDes="",sorderNo="",
				siteCodeShip="",availableYn="";
		Date orderDt = null,statusDate=null,mfgDate=null,expiryDate=null;
		try
		{   
			sdf=new SimpleDateFormat(genutility.getApplDateFormat());
			currDate = new java.sql.Timestamp(System.currentTimeMillis()) ;
			currAppdate = sdf.format(currDate);
			conn = connDriver.getConnectDB("DriverITM");
			conn.setAutoCommit(false);
			connDriver = null;
			simpleDateFormat = new SimpleDateFormat(genericUtility.getApplDateFormat());
			userId = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "userId");

			chgUser = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginCode");
			chgTerm = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "termId");

			if (objContext != null && objContext.trim().length() > 0) {
				currentFormNo = Integer.parseInt(objContext);
			}
			System.out.println("FORM NO IS---->>"+currentFormNo);
			System.out.println("currentColumn--------->>["+currentColumn+"]");
			System.out.println("editFlag111--------->>["+editFlag+"]");
			valueXmlString = new StringBuffer("<?xml version = \"1.0\"?> <Root> <header> <editFlag>");
			valueXmlString.append(editFlag).append("</editFlag> </header>");
			userId = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginCode");
			switch (currentFormNo) {

			case 1 :
				break;

			case 2 : 
				System.out.println("DOM1 Elements-->["+genericUtility.serializeDom(dom1).toString()+"]");
				System.out.println("DOM2 Elements-->["+genericUtility.serializeDom(dom2).toString()+"]");
							
				sorderNo = checkNull(genericUtility.getColumnValue("sale_order_no", dom1));
				System.out.println("sorderNo in case2---->>["+sorderNo+"]");	
				
				sql="select count(1) from despatch where CASE WHEN confirmed IS NULL "
						+ "THEN 'N' ELSE confirmed END = 'N' and sord_no = ?";
				pstmt=conn.prepareStatement(sql);
				pstmt.setString(1,sorderNo);
				rs = pstmt.executeQuery();
				if(rs.next()){
				cnt = rs.getInt(1);
				}
				if(rs!=null){
				rs.close();
				rs=null;
				}
				if(pstmt!=null){
				pstmt.close();
				pstmt=null;
				}
				System.out.println("sorderNo cnt123/@-@/>>----->>["+cnt+"]");//GROSS_WEIGHT, TARE_WEIGHT, NETT_WEIGHT		
				
				if(cnt > 0)
				{
					if(currentColumn.trim().equalsIgnoreCase("itm_default"))
					{
						System.out.println("in item default123............$$$@@@");
						sql="select d.desp_id,d.site_code,d.desp_date,d.curr_code,d.exch_rate,d.trans_mode,d.curr_code__frt,d.curr_code__ins,d.stan_code,"
								+ "d.state_code__dlv,d.dlv_city,d.dlv_pin,d.count_code__dlv,d.dlv_add1,d.dlv_add2,d.dlv_add3,"
								+ "d.tran_code,d.stan_code__init,d.status,d.status_remarks,d.remarks,d.no_art,d.status_date,d.cust_code,d.cust_code__dlv,s.site_code__ship"
								+ " from despatch d,sorder s where d.sord_no = ? and d.sord_no = s.sale_order";
						
						pstmt=conn.prepareStatement(sql);
						pstmt.setString(1,sorderNo);
						rs = pstmt.executeQuery();
						if(rs.next()){
							despId=checkNull(rs.getString("desp_id"));
							siteCode = checkNull(rs.getString("site_code"));
							 currencyCode = checkNull(rs.getString("curr_code"));
							exchRate1 = checkNull(rs.getString("exch_rate"));
							transMode = rs.getString("trans_mode") == null ? "R " : rs.getString("trans_mode");
							currCodeFrt = checkNull(rs.getString("curr_code__frt"));
							currCodeIns = checkNull(rs.getString("curr_code__ins"));						
							
							orderDt = rs.getDate("desp_date");						
							
							stanCode = checkNull(rs.getString("stan_code"));							
							stanCodeDlv = checkNull(rs.getString("state_code__dlv"));
							dlvCity = checkNull(rs.getString("dlv_city"));
							dlvPin = checkNull(rs.getString("dlv_pin"));
							countCodeDlv = checkNull(rs.getString("count_code__dlv"));
							tranCode = checkNull(rs.getString("tran_code"));
							stanCodeInit = checkNull(rs.getString("stan_code__init"));
							status = checkNull(rs.getString("status"));
							statusRemarks = checkNull(rs.getString("status_remarks"));
							statusDate = rs.getDate("status_date");
							custCode=checkNull(rs.getString("cust_code"));
							custCodeDlv=checkNull(rs.getString("cust_code__dlv"));
							siteCodeShip=checkNull(rs.getString("site_code__ship"));
							
							dlvAdd1=checkNull(rs.getString("dlv_add1"));
							dlvAdd2=checkNull(rs.getString("dlv_add2"));
							dlvAdd3=checkNull(rs.getString("dlv_add3"));
							
							remarksDes=checkNull(rs.getString("remarks"));
							noArtDes=checkNull(rs.getString("no_art"));
						}
						if(rs!=null){
						rs.close();
						rs=null;
						}
						if(pstmt!=null){
						pstmt.close();
						pstmt=null;
						}
						System.out.println("orderDt----->>["+orderDt+"]");
						System.out.println("custCodeDlv----->>["+custCodeDlv+"]");
						valueXmlString.append("<Detail2 domID='" + count + "'  objContext = '"+currentFormNo+"' selected=\"N\">\r\n");
						valueXmlString.append("<attribute selected=\"Y\" updateFlag=\"E\" status=\"O\" pkNames=\"\"/>\r\n");
						valueXmlString.append("<desp_id><![CDATA["+ despId +"]]></desp_id>");
						if(orderDt != null){
						valueXmlString.append("<desp_date><![CDATA["+ sdf.format(orderDt).toString() +"]]></desp_date>");
						valueXmlString.append("<sord_date><![CDATA["+ sdf.format(orderDt).toString() +"]]></sord_date>");
						}
						valueXmlString.append("<sord_no><![CDATA["+ sorderNo +"]]></sord_no>");
						valueXmlString.append("<cust_code><![CDATA["+ custCode +"]]></cust_code>");
						valueXmlString.append("<cust_code__dlv><![CDATA["+ custCodeDlv +"]]></cust_code__dlv>");
						valueXmlString.append("<cust_code__bil><![CDATA["+ custCodeBil +"]]></cust_code__bil>");
						valueXmlString.append("<stan_code><![CDATA["+ stanCode.trim() +"]]></stan_code>");
						valueXmlString.append("<trans_mode><![CDATA["+ transMode.trim() +"]]></trans_mode>");
						
						//valueXmlString.append("<lr_date><![CDATA["+ lrDateVal.toString() +"]]></lr_date>");
						//valueXmlString.append("<shipment_id><![CDATA["+ shipmentId.trim() +"]]></shipment_id>");
						valueXmlString.append("<curr_code__frt><![CDATA["+ currCodeFrt.trim() +"]]></curr_code__frt>");
						valueXmlString.append("<curr_code__ins><![CDATA["+ currCodeIns.trim() +"]]></curr_code__ins>");
						valueXmlString.append("<curr_code><![CDATA["+ currencyCode.trim() +"]]></curr_code>");
						valueXmlString.append("<exch_rate><![CDATA["+  exchRate1 +"]]></exch_rate>");// CAN NOT BE NULL
						valueXmlString.append("<exch_rate__frt><![CDATA["+  exchRate1 +"]]></exch_rate__frt>");	
						valueXmlString.append("<exch_rate__ins><![CDATA["+ exchRate1 +"]]></exch_rate__ins>");
						
						
						valueXmlString.append("<site_code__ship><![CDATA["+ siteCodeShip +"]]></site_code__ship>");
						if(siteCodeShip.length() > 0){
						siteCodeDesc=checkNull(getColumnDescr(conn, "descr", "site", "site_code", siteCodeShip));
						}
						valueXmlString.append("<site_descr><![CDATA["+ siteCodeDesc+"]]></site_descr>");
						valueXmlString.append("<site_code><![CDATA["+ siteCode +"]]></site_code>");
						if(siteCode.length() > 0){
						siteCodeDesc=checkNull(getColumnDescr(conn, "descr", "site", "site_code", siteCode));	
						}
									
						valueXmlString.append("<descr><![CDATA["+ siteCodeDesc+"]]></descr>");
						valueXmlString.append("<state_code__dlv><![CDATA["+ stanCodeDlv.trim()+"]]></state_code__dlv>");
						
						valueXmlString.append("<dlv_add1><![CDATA["+ dlvAdd1 +"]]></dlv_add1>");
						valueXmlString.append("<dlv_add2><![CDATA["+ dlvAdd2 +"]]></dlv_add2>");
						valueXmlString.append("<dlv_add3><![CDATA["+ dlvAdd3 +"]]></dlv_add3>");
						
						valueXmlString.append("<dlv_city><![CDATA["+ dlvCity +"]]></dlv_city>");				
						valueXmlString.append("<dlv_pin><![CDATA["+ dlvPin +"]]></dlv_pin>");
						valueXmlString.append("<count_code__dlv><![CDATA["+ countCodeDlv +"]]></count_code__dlv>");
						valueXmlString.append("<tran_code><![CDATA["+ tranCode +"]]></tran_code>");
						valueXmlString.append("<stan_code__init><![CDATA["+ stanCodeInit +"]]></stan_code__init>");
						valueXmlString.append("<status_remarks><![CDATA["+ statusRemarks +"]]></status_remarks>");
						valueXmlString.append("<status><![CDATA["+ status +"]]></status>");
						tranName=getColumnDescr(conn,"tran_name" , "transporter", "tran_code", tranCode);
						valueXmlString.append("<tran_name><![CDATA["+ tranName +"]]></tran_name>");
						
						valueXmlString.append("<remarks><![CDATA["+ remarksDes +"]]></remarks>");
						valueXmlString.append("<no_art><![CDATA["+ noArtDes +"]]></no_art>");
						
						valueXmlString.append("</Detail2>");
						
					}
				}else {
					System.out.println("--------no data found in Despatch-------------");
					sql = "select item_ser,site_code,curr_code,exch_rate,tran_code, trans_mode,curr_code__frt,curr_code__ins,cust_code__dlv," +
						    "stan_code,order_date,exch_rate__frt,exch_rate__ins,fob_value,conf_date,chg_date,state_code__dlv,udf__str1,udf__str2," +
							"dlv_city,dlv_pin,count_code__dlv,tran_code,stan_code,stan_code__init,parent__tran_id,rev__tran,status_remarks,"
							+ "spec_reason,dist_route,status,status_date,cust_code,cust_code__dlv,"
							+ "cust_code__bil,site_code__ship,dlv_add1,dlv_add2,dlv_add3"+
							" from sorder where sale_order = ? " ;
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,sorderNo);
					rs = pstmt.executeQuery();
					if( rs.next() )
					{
						siteCode = checkNull(rs.getString("site_code"));
						 currencyCode = checkNull(rs.getString("curr_code"));
						exchRate1 = checkNull(rs.getString("exch_rate"));
						transMode = rs.getString("trans_mode") == null ? "R " : rs.getString("trans_mode");
						currCodeFrt = checkNull(rs.getString("curr_code__frt"));
						currCodeIns = checkNull(rs.getString("curr_code__ins"));
						
						stanCode = checkNull(rs.getString("stan_code"));
						orderDt = rs.getDate("order_date");//$
						stanCodeDlv = checkNull(rs.getString("state_code__dlv"));
						dlvCity = checkNull(rs.getString("dlv_city"));
						dlvPin = checkNull(rs.getString("dlv_pin"));
						countCodeDlv = checkNull(rs.getString("count_code__dlv"));
						tranCode = checkNull(rs.getString("tran_code"));
						stanCodeInit = checkNull(rs.getString("stan_code__init"));
						status = checkNull(rs.getString("status"));
						statusRemarks = checkNull(rs.getString("status_remarks"));
						statusDate = rs.getDate("status_date");
						custCode=checkNull(rs.getString("cust_code"));
						custCodeDlv=checkNull(rs.getString("cust_code__dlv"));
						custCodeBil=checkNull(rs.getString("cust_code__bil"));
						siteCodeShip=checkNull(rs.getString("site_code__ship"));
						
						dlvAdd1=checkNull(rs.getString("dlv_add1"));
						dlvAdd2=checkNull(rs.getString("dlv_add2"));
						dlvAdd3=checkNull(rs.getString("dlv_add3"));
						
					}
					if(rs!=null){
					rs.close();
					rs=null;
					}
					if(pstmt!=null){
					pstmt.close();
					pstmt=null;
					}
					valueXmlString.append("<Detail2 domID='" + count + "'  objContext = '"+currentFormNo+"' selected=\"N\">\r\n");
					valueXmlString.append("<attribute  selected=\"N\" updateFlag=\"A\"  status=\"N\" pkNames=\"\"/>\r\n");
					if(orderDt != null){
					valueXmlString.append("<desp_date><![CDATA["+ sdf.format(orderDt).toString() +"]]></desp_date>");
					valueXmlString.append("<sord_date><![CDATA["+ sdf.format(orderDt).toString() +"]]></sord_date>");
					}
					valueXmlString.append("<sord_no><![CDATA["+ sorderNo +"]]></sord_no>");
					valueXmlString.append("<cust_code><![CDATA["+ custCode +"]]></cust_code>");
					valueXmlString.append("<cust_code__dlv><![CDATA["+ custCodeDlv +"]]></cust_code__dlv>");
					valueXmlString.append("<cust_code__bil><![CDATA["+ custCodeBil +"]]></cust_code__bil>");
					valueXmlString.append("<stan_code><![CDATA["+ stanCode.trim() +"]]></stan_code>");
					valueXmlString.append("<trans_mode><![CDATA["+ transMode.trim() +"]]></trans_mode>");
					
					//valueXmlString.append("<lr_date><![CDATA["+ lrDateVal.toString() +"]]></lr_date>");
					//valueXmlString.append("<shipment_id><![CDATA["+ shipmentId.trim() +"]]></shipment_id>");
					valueXmlString.append("<curr_code__frt><![CDATA["+ currCodeFrt.trim() +"]]></curr_code__frt>");
					valueXmlString.append("<curr_code__ins><![CDATA["+ currCodeIns.trim() +"]]></curr_code__ins>");
					valueXmlString.append("<curr_code><![CDATA["+ currencyCode.trim() +"]]></curr_code>");
					valueXmlString.append("<exch_rate><![CDATA["+  exchRate1 +"]]></exch_rate>");// CAN NOT BE NULL
					valueXmlString.append("<exch_rate__frt><![CDATA["+  exchRate1 +"]]></exch_rate__frt>");	
					valueXmlString.append("<exch_rate__ins><![CDATA["+ exchRate1 +"]]></exch_rate__ins>");
					
					
					valueXmlString.append("<site_code__ship><![CDATA["+ siteCodeShip +"]]></site_code__ship>");
					siteCodeDesc=checkNull(getColumnDescr(conn, "descr", "site", "site_code", siteCodeShip));
					valueXmlString.append("<site_descr><![CDATA["+ siteCodeDesc+"]]></site_descr>");
					valueXmlString.append("<site_code><![CDATA["+ siteCode +"]]></site_code>");
					siteCodeDesc=checkNull(getColumnDescr(conn, "descr", "site", "site_code", siteCode));				
					valueXmlString.append("<descr><![CDATA["+ siteCodeDesc+"]]></descr>");
					valueXmlString.append("<state_code__dlv><![CDATA["+ stanCodeDlv.trim()+"]]></state_code__dlv>");
					
					valueXmlString.append("<dlv_add1><![CDATA["+ dlvAdd1 +"]]></dlv_add1>");
					valueXmlString.append("<dlv_add2><![CDATA["+ dlvAdd2 +"]]></dlv_add2>");
					valueXmlString.append("<dlv_add3><![CDATA["+ dlvAdd3 +"]]></dlv_add3>");
					
					valueXmlString.append("<dlv_city><![CDATA["+ dlvCity +"]]></dlv_city>");				
					valueXmlString.append("<dlv_pin><![CDATA["+ dlvPin +"]]></dlv_pin>");
					valueXmlString.append("<count_code__dlv><![CDATA["+ countCodeDlv +"]]></count_code__dlv>");
					valueXmlString.append("<tran_code><![CDATA["+ tranCode +"]]></tran_code>");
					valueXmlString.append("<stan_code__init><![CDATA["+ stanCodeInit +"]]></stan_code__init>");
					valueXmlString.append("<status_remarks><![CDATA["+ statusRemarks +"]]></status_remarks>");
					valueXmlString.append("<status><![CDATA["+ status +"]]></status>");
					tranName=getColumnDescr(conn,"tran_name" , "transporter", "tran_code", tranCode);
					valueXmlString.append("<tran_name><![CDATA["+ tranName +"]]></tran_name>");
					
					valueXmlString.append("</Detail2>");
				}
				if(currentColumn.trim().equalsIgnoreCase("tran_code"))
				{
					System.out.println(">>>>START tran_code38>>>>");
					tranCode = genericUtility.getColumnValue("tran_code",dom);
					System.out.println("tranCode----->>["+tranCode+"]");
					String currDomStr = genericUtility.serializeDom(dom);

					if(tranCode != null && tranCode.trim().length() > 0)
					{
						tranName=getColumnDescr(conn,"tran_name" , "transporter", "tran_code", tranCode);						
						System.out.println("tranName----->>["+tranName+"]");
						valueXmlString.append("<tran_name protect=\"0\">").append("<![CDATA[" + tranName + "]]>").append("</tran_name>");
						setNodeValue( dom, "tran_name", getAbsString(""+tranName));					
						transMode=getColumnDescr(conn,"trans_mode" , "transporter_mode", "tran_code", tranCode);
						
						valueXmlString.append("<trans_mode protect=\"0\">").append("<![CDATA[" + transMode + "]]>").append("</trans_mode>");
						setNodeValue( dom, "trans_mode", getAbsString(""+transMode)); 						
						currDomStr = currDomStr.replace("</Detail2>", valueXmlString.toString() + "</Detail2>");
						System.out.println("after currDomStr[" + currDomStr + "]");
						valueXmlString.append(currDomStr);
					}
					else
					{
						System.out.println("-------in else of tran mode --------------------");
						valueXmlString.append("<tran_name protect=\"0\">").append("<![CDATA[]]>").append("</tran_name>");
						setNodeValue( dom, "sh_name", getAbsString("")); 

						valueXmlString.append("<trans_mode protect=\"0\">").append("<![CDATA[]]>").append("</trans_mode>");
						setNodeValue( dom, "trans_mode", getAbsString("")); 

						currDomStr = currDomStr.replace("</Detail2>", valueXmlString.toString() + "</Detail2>");
						System.out.println("after currDomStr[" + currDomStr + "]");
						valueXmlString.append(currDomStr);
					}


					System.out.println(">>>>END>>>>>>>>>trancode");

				}


				break;
			case 3 : 
				System.out.println("------Case 3 called or form 3 called@@#------domID123-----------");
				System.out.println("dom in form3---->>["+genericUtility.serializeDom(dom)+"]");
				System.out.println("dom111 in form3---->>["+genericUtility.serializeDom(dom1)+"]");
				System.out.println("dom222 in form3---->>["+genericUtility.serializeDom(dom2)+"]");
				tranDate = getCurrdateAppFormat() ;			
				
				sorderNo = checkNull(genericUtility.getColumnValue("sale_order_no", dom2));
				despId = checkNull(genericUtility.getColumnValue("desp_id", dom2,"2"));
				String domID = checkNull(getColumnValueMy("Detail3", dom2, "3"));
				System.out.println("sorderNo---->>["+sorderNo+"]");
				System.out.println("despID---->>["+despId+"]");
				System.out.println("domID---->>["+domID+"]");
				

				/*sql = "select tran_id from distord_iss where CASE WHEN confirmed IS NULL THEN 'N' ELSE CONFIRMED  END = 'N' and dist_order = ?";
				pstmt1=conn.prepareStatement(sql);
				pstmt1.setString(1,distOrder);
				rs1 = pstmt1.executeQuery();
				if(rs1.next())
				{

					tranid = rs1.getString(1) == null ? "":rs1.getString(1);

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

				

				if(currentColumn.trim().equalsIgnoreCase("itm_default"))
				{
					System.out.println("Form 3 itm_default called.................");
					sql="select despatchdet.desp_id,despatchdet.line_no,despatchdet.sord_no,despatchdet.line_no__sord,"
							+ "despatchdet.exp_lev,despatchdet.item_code__ord,despatchdet.item_code,despatchdet.lot_no, "   
				         +"despatchdet.lot_sl,despatchdet.quantity__ord,despatchdet.quantity,item.descr, "
				         +"despatchdet.loc_code,despatchdet.status,despatchdet.conv__qty_stduom, "   
				         +"despatchdet.unit__std,despatchdet.unit, " 
				         +"despatchdet.quantity__stduom,despatchdet.quantity_real, "   
				         +"despatchdet.rate__stduom,sorddet.tax_class, "   
				         +"sorddet.tax_chap,sorddet.tax_env, "
				         +"sorddet.discount,despatchdet.pack_instr, "
				         +"despatchdet.no_art,sorditem.quantity - sorditem.qty_desp as pending_qty, "   
				         +"sorddet.rate,despatchdet.pack_qty, "   
				         +"despatchdet.exp_date,despatchdet.site_code, "   
				         +"despatchdet.mfg_date,despatchdet.chg_date, "   
				         +"despatchdet.chg_user,despatchdet.chg_term, "   
				         +"despatchdet.site_code__mfg,despatchdet.rate__clg, "   
				         +"despatchdet.gross_weight,despatchdet.tare_weight, "   
				         +"despatchdet.nett_weight,despatchdet.dimension, "   
				         +"despatchdet.tax_amt,despatchdet.disc_amt, "
				         +"despatchdet.conf_diff_amt,despatchdet.rate__std, "   
				         +"despatchdet.cost_rate,'' as qty_details, "  
				         +"despatchdet.line_type,0 as tot_net_amt, "
				         +"despatchdet.conv__rtuom_stduom,despatchdet.pallet_wt, "
				         +"despatchdet.tran_id__invpack,despatchdet.cust_item__ref, "
				         +"fn_custitem_dscr((despatch.cust_code),despatchdet.item_code,despatchdet.cust_item__ref)  as custitem_desc, "   
				         +"despatchdet.retest_date,despatchdet.part_no, "
				         +"despatchdet.disc_schem_billback_amt,despatchdet.disc_schem_offinv_amt, "   
				         +"despatchdet.pallet_no " 
				         +"from despatchdet, "   
				         +"item, "
				         +"sorddet, "   
				         +"sorditem, "   
				         +"despatch "  
				         +"where ( despatchdet.item_code = item.item_code (+)) and "  
				         +"( despatchdet.sord_no = sorddet.sale_order (+)) and "  
				         +"( despatchdet.line_no__sord = sorddet.line_no (+)) and "  
				         +"( despatchdet.sord_no = sorditem.sale_order (+)) and "
				         +"( despatchdet.line_no__sord = sorditem.line_no (+)) and "  
				         +"( despatchdet.exp_lev = sorditem.exp_lev (+)) and "  
				         +"( despatch.desp_id = despatchdet.desp_id ) and "  
				         +"( ( despatchdet.desp_id = ? ))";
					
					pstmt=conn.prepareStatement(sql);
					pstmt.setString(1, despId);
					rs=pstmt.executeQuery();
					while(rs.next()){				
					
					valueXmlString.append("<Detail3 domID='" +rs.getInt("line_no")+ "'  objContext = '"+currentFormNo+"' selected=\"Y\">\r\n");
					valueXmlString.append("<attribute selected=\"Y\" updateFlag=\"E\" status=\"O\" pkNames=\"\"/>\r\n");
					
					
					valueXmlString.append("<desp_id><![CDATA["+ despId +"]]></desp_id>");
					valueXmlString.append("<sord_no><![CDATA["+ checkNull(rs.getString("sord_no")) +"]]></sord_no>");
					valueXmlString.append("<line_no><![CDATA["+ checkNull(rs.getString("line_no")) +"]]></line_no>");					
					valueXmlString.append("<line_no__sord><![CDATA["+ checkNull(rs.getString("line_no__sord")) +"]]></line_no__sord>");					
					valueXmlString.append("<lot_no><![CDATA["+ checkNull(rs.getString("lot_no")) +"]]></lot_no>");					
					valueXmlString.append("<lot_sl><![CDATA["+ checkNull(rs.getString("lot_sl")) +"]]></lot_sl>");					
					valueXmlString.append("<loc_code><![CDATA["+ checkNull(rs.getString("loc_code")) +"]]></loc_code>");					
					valueXmlString.append("<exp_lev><![CDATA["+ checkNull(rs.getString("exp_lev")) +"]]></exp_lev>");
					
					
					//valueXmlString.append("<remarks><![CDATA["+ "generated from sorder "+"]]></remarks>");					
					valueXmlString.append("<rate><![CDATA["+rate +"]]></rate>");					
					valueXmlString.append("<item_code__ord><![CDATA["+ checkNull(rs.getString("item_code__ord")) +"]]></item_code__ord>");					
					valueXmlString.append("<item_code><![CDATA["+ checkNull(rs.getString("item_code"))+"]]></item_code>");					
					valueXmlString.append("<quantity><![CDATA["+ (rs.getDouble("quantity")) +"]]></quantity>");					
					valueXmlString.append("<unit><![CDATA["+ checkNull(rs.getString("unit"))+"]]></unit>");					
					valueXmlString.append("<conv__qty_stduom><![CDATA["+ (rs.getDouble("conv__qty_stduom")) +"]]></conv__qty_stduom>");
					
					
					//valueXmlString.append("<unit__rate><![CDATA["+ (rs.getDouble("unit__rate")) +"]]></unit__rate>");				
					valueXmlString.append("<tax_class><![CDATA["+checkNull(rs.getString("tax_class")) +"]]></tax_class>");				
					valueXmlString.append("<tax_chap><![CDATA["+checkNull(rs.getString("tax_chap")) +"]]></tax_chap>");				
					valueXmlString.append("<tax_env><![CDATA["+ checkNull(rs.getString("tax_env"))+"]]></tax_env>");					
					valueXmlString.append("<rate__clg><![CDATA["+ (rs.getDouble("rate__clg")) +"]]></rate__clg>");					
					//valueXmlString.append("<tot_net_amt><![CDATA["+ netTotAmt.trim()+"]]></tot_net_amt>");					
					valueXmlString.append("<site_code><![CDATA["+ checkNull(rs.getString("site_code"))+"]]></site_code>");					
					valueXmlString.append("<quantity__stduom><![CDATA["+ (rs.getDouble("quantity__stduom"))+"]]></quantity__stduom>");					
					//valueXmlString.append("<pack_code><![CDATA["+  checkNull(rs.getString("pack_code"))+"]]></pack_code>");
					valueXmlString.append("<unit__std><![CDATA["+ checkNull(rs.getString("unit__std"))+"]]></unit__std>");
				
					valueXmlString.append("<no_art><![CDATA["+ (rs.getDouble("no_art")) +"]]></no_art>");
					valueXmlString.append("<rate__stduom><![CDATA["+ (rs.getDouble("rate__stduom")) +"]]></rate__stduom>");
					valueXmlString.append("<conv__rtuom_stduom><![CDATA["+ (rs.getDouble("conv__rtuom_stduom")) +"]]></conv__rtuom_stduom>");
					//valueXmlString.append("<tot_net_amt><![CDATA["+ netAmt +"]]></tot_net_amt>");
					//valueXmlString.append("<quantity_inv><![CDATA["+ quantity +"]]></quantity_inv>");
					valueXmlString.append("<quantity_real><![CDATA["+ (rs.getDouble("quantity_real")) +"]]></quantity_real>");
					
					
					
					//mfg_date,exp_date,pending_qty,quantity__ord,quantity_real
					mfgDate=rs.getDate("mfg_date");
					expiryDate=rs.getDate("exp_date");
					if(mfgDate!=null){
					valueXmlString.append("<mfg_date>").append("<![CDATA[").append(sdf.format(mfgDate).toString()).append("]]>").append("</mfg_date>");
					}
					if(expiryDate !=null){
					valueXmlString.append("<exp_date>").append("<![CDATA[").append(sdf.format(expiryDate).toString()).append("]]>").append("</exp_date>");
					}
					valueXmlString.append("<pending_qty>").append("<![CDATA[").append((rs.getDouble("pending_qty"))).append("]]>").append("</pending_qty>");
					valueXmlString.append("<quantity__ord>").append("<![CDATA[").append((rs.getDouble("quantity__ord"))).append("]]>").append("</quantity__ord>");
					
					valueXmlString.append("<gross_weight>").append("<![CDATA[").append((rs.getDouble("gross_weight"))).append("]]>").append("</gross_weight>");
					valueXmlString.append("<nett_weight>").append("<![CDATA[").append((rs.getDouble("nett_weight"))).append("]]>").append("</nett_weight>");
					valueXmlString.append("<tare_weight>").append("<![CDATA[").append((rs.getDouble("tare_weight"))).append("]]>").append("</tare_weight>");
					valueXmlString.append("<dimension><![CDATA["+ checkNull(rs.getString("dimension")) +"]]></dimension>");
					valueXmlString.append("<pallet_wt><![CDATA["+ (rs.getDouble("pallet_wt")) +"]]></pallet_wt>");
					
					valueXmlString.append("</Detail3>");
					}//end while
					if(rs!=null){
					rs.close();
					rs=null;
					}
					if(pstmt!=null){
					pstmt.close();
					pstmt=null;
					}
				}
				else if(currentColumn.trim().equalsIgnoreCase("itm_default_add"))
				{
					String lineNoAdd="",lnNoSord="",quantity="",rate1="",conQtyStd="",itemDescr="",itemCodeOrd="";
					String unitRate="",taxClass="",taxChap="",taxEnv="",netTotAmt="",quntyStduom="",
							siteCodeDet="",unitStd="", noArt="", convRtuomStduom="",netAmt="",rateStduom="";
					System.out.println("Form 3 itm_default_add called.................");
					int lineNo1=0;
					double rateClg=0;
					
					String str=new DespatchAct().actionDefault(dom2, dom1, objContext, xtraParams);
					System.out.println("str------------->>["+str+"]");
				/*	String xmlString=new DespatchAct().actionDefault(dom1,dom2,objContext,xtraParams);
					System.out.println("return string from actionDefault---->>["+xmlString+"]");
					valueXmlString.append(xmlString);*/
					sql = "select max(line_no)  "
							+ "from despatchdet where sord_no = ?";
					pstmt=conn.prepareStatement(sql);
					pstmt.setString(1,sorderNo);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						lineNoAdd = checkNull(rs.getString(1));						
					}
					
					
					pstmt.close();
					rs.close();
					pstmt = null;
					rs = null;
					 if(lineNoAdd.length() > 0){
					 lineNo1=Integer.parseInt(lineNoAdd);
					 lineNo1++;
					 }else{
					  lineNo1=1;
					 }
					//item_descr
					sql = "select line_no,item_code,item_code__ord,quantity,rate,unit,conv__qty_stduom,unit__rate,tax_class,tax_chap,tax_env,pack_code,rate__clg,quantity__stduom," +
							"net_tot_amt,net_amt,site_code,unit__std,no_art,rate__stduom,conv__rtuom_stduom from sorddet where sale_order = ? order by line_no";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,sorderNo);
					rs = pstmt.executeQuery();
				   if( rs.next() )
				   {
					   lnNoSord = rs.getString("line_no");	
						itemCode = rs.getString("item_code") == null ? " " : rs.getString("item_code");
						itemCodeOrd = rs.getString("item_code__ord") == null ? " " : rs.getString("item_code__ord");
						quantity = rs.getString("quantity") == null ? "0" : rs.getString("quantity")  ;
						rate1 = rs.getString("rate")== null ? " " : rs.getString("rate");
						unit = rs.getString("unit") == null ? " " : rs.getString("unit");
						conQtyStd = rs.getString("conv__qty_stduom") == null ? " " : rs.getString("conv__qty_stduom");
						unitRate = rs.getString("unit__rate") == null ? " " : rs.getString("unit__rate");
						taxClass = rs.getString("tax_class") == null ? " " : rs.getString("tax_class");
						taxChap = rs.getString("tax_chap") == null ? " " : rs.getString("tax_chap");
						taxEnv = rs.getString("tax_env") == null ? " " : rs.getString("tax_env");
						packCode = rs.getString("pack_code") == null ? " " : rs.getString("pack_code");
						rateClg = rs.getDouble("rate__clg");
						quntyStduom = rs.getString("quantity__stduom") == null ? "0" : rs.getString("quantity__stduom");
						netTotAmt = rs.getString("net_tot_amt") == null ? " " : rs.getString("net_tot_amt");
						siteCodeDet = rs.getString("site_code") == null ? " " : rs.getString("site_code");	
						unitStd = rs.getString("unit__std") == null ? " " : rs.getString("unit__std");	
						noArt = rs.getString("no_art");	
						rateStduom = rs.getString("rate__stduom");	
						convRtuomStduom = rs.getString("conv__rtuom_stduom");	
						netAmt = rs.getString("net_amt");	
				   } 
				   pstmt.close();
					rs.close();
					pstmt = null;
					rs = null;
				   itemDescr=checkNull(getColumnDescr(conn, "descr", "item", "item_code", itemCode));
				   
					/*sql = "select line_no,item_code,item_code__ord,quantity,rate,unit,conv__qty_stduom,unit__rate,tax_class,"
							+ "tax_chap,tax_env,pack_code,rate__clg,quantity__stduom,net_tot_amt,net_amt,site_code,"
							+ "unit__std,no_art,rate__stduom,conv__rtuom_stduom from sorddet "
							+ "where sale_order = ? and line_no = ?";*/
					/*pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,sorderNo);
					pstmt.setString(2,lineNoAdd);
					rs = pstmt.executeQuery();
				   if( rs.next() )
				   {
					   lnNoSord = checkNull(rs.getString("line_no"));								

				   }
				   
					rs.close();					
					pstmt.close();
					pstmt = null;
					rs = null;*/
				   System.out.println("lnNoSord-------->>["+lnNoSord+"]");
				  
				   valueXmlString.append("<Detail3 domID='" +lineNo1+ "'  objContext = '"+currentFormNo+"' selected=\"Y\">\r\n");
					valueXmlString.append("<attribute selected=\"Y\" updateFlag=\"A\" status=\"N\" pkNames=\"\"/>\r\n");
					
					
					valueXmlString.append("<desp_id><![CDATA["+ despId +"]]></desp_id>");
					valueXmlString.append("<sord_no><![CDATA["+ sorderNo +"]]></sord_no>");
					valueXmlString.append("<line_no><![CDATA["+ lineNo1 +"]]></line_no>");					
					valueXmlString.append("<line_no__sord><![CDATA["+ lnNoSord +"]]></line_no__sord>");					
					
					valueXmlString.append("<lot_no><![CDATA[]]></lot_no>");					
					valueXmlString.append("<lot_sl><![CDATA[]]></lot_sl>");					
					valueXmlString.append("<loc_code><![CDATA[]]></loc_code>");					
					valueXmlString.append("<exp_lev><![CDATA[]]></exp_lev>");					
					//valueXmlString.append("<remarks><![CDATA["+ "generated from sorder "+"]]></remarks>");					
					valueXmlString.append("<rate><![CDATA["+rate1 +"]]></rate>");					
					valueXmlString.append("<item_code__ord><![CDATA["+ itemCodeOrd +"]]></item_code__ord>");					
					//valueXmlString.append("<item_code><![CDATA["+ itemCode +"]]></item_code>");					
					//valueXmlString.append("<quantity><![CDATA["+ quantity.trim() +"]]></quantity>");					
					valueXmlString.append("<unit><![CDATA["+ unit+"]]></unit>");					
					valueXmlString.append("<conv__qty_stduom><![CDATA["+ conQtyStd +"]]></conv__qty_stduom>");					
					valueXmlString.append("<unit__rate><![CDATA["+ unitRate +"]]></unit__rate>");				
					valueXmlString.append("<tax_class><![CDATA["+taxClass.trim() +"]]></tax_class>");				
					valueXmlString.append("<tax_chap><![CDATA["+taxChap +"]]></tax_chap>");				
					valueXmlString.append("<tax_env><![CDATA["+ taxEnv+"]]></tax_env>");					
					valueXmlString.append("<rate__clg><![CDATA["+ rateClg +"]]></rate__clg>");					
					valueXmlString.append("<tot_net_amt><![CDATA["+ netTotAmt.trim()+"]]></tot_net_amt>");					
					valueXmlString.append("<site_code><![CDATA["+ siteCodeDet.trim()+"]]></site_code>");					
					valueXmlString.append("<quantity__stduom><![CDATA["+ quntyStduom.trim()+"]]></quantity__stduom>");					
					valueXmlString.append("<pack_code><![CDATA["+ packCode.trim()+"]]></pack_code>");
					valueXmlString.append("<unit__std><![CDATA["+ unitStd+"]]></unit__std>");
					//valueXmlString.append("<CHG_USER><![CDATA["+ userId +"]]></CHG_USER>");
					//valueXmlString.append("<CHG_TERM><![CDATA["+ termId +"]]></CHG_TERM>");
					//valueXmlString.append("<CHG_DATE><![CDATA["+ today +"]]></CHG_DATE>");
					valueXmlString.append("<no_art><![CDATA["+ noArt +"]]></no_art>");
					valueXmlString.append("<rate__stduom><![CDATA["+ rateStduom +"]]></rate__stduom>");
					valueXmlString.append("<conv__rtuom_stduom><![CDATA["+ convRtuomStduom +"]]></conv__rtuom_stduom>");
					valueXmlString.append("<tot_net_amt><![CDATA["+ netAmt +"]]></tot_net_amt>");
					valueXmlString.append("<quantity__inv><![CDATA["+ quantity +"]]></quantity__inv>");
					valueXmlString.append("<quantity__real><![CDATA["+ quantity +"]]></quantity__real>");
					// ADDED BY RITESH ON 26/05/13 START
					/*valueXmlString.append("<gross_weight>").append("<![CDATA[").append(grossWeight1).append("]]>").append("</gross_weight>");
					valueXmlString.append("<nett_weight>").append("<![CDATA[").append(netWeight1).append("]]>").append("</nett_weight>");
					valueXmlString.append("<tare_weight>").append("<![CDATA[").append(tareWeight1).append("]]>").append("</tare_weight>");
					valueXmlString.append("<dimension><![CDATA["+ dimension +"]]></dimension>");
					valueXmlString.append("<pallet_wt><![CDATA["+ palletWt +"]]></pallet_wt>");
					valueXmlString.append("<pack_code><![CDATA["+ packCode +"]]></pack_code>");*/
					/*valueXmlString.append("<rate><![CDATA[ ]]></rate>");
					valueXmlString.append("<item_code__ord><![CDATA[ ]]></item_code__ord>");
					valueXmlString.append("<item_code><![CDATA[ ]]></item_code>");
					valueXmlString.append("<quantity><![CDATA[ ]]></quantity>");
					valueXmlString.append("<unit><![CDATA[ ]]></unit>");
					valueXmlString.append("<conv__qty_stduom><![CDATA[ ]]></conv__qty_stduom>");
					
					valueXmlString.append("<tax_class><![CDATA[ ]]></tax_class>");
					valueXmlString.append("<tax_chap><![CDATA[ ]]></tax_chap>");
					valueXmlString.append("<tax_env><![CDATA[ ]]></tax_env>");
					valueXmlString.append("<rate__clg><![CDATA[ ]]></rate__clg>");
					valueXmlString.append("<site_code><![CDATA[ ]]></site_code>");
					valueXmlString.append("<quantity__stduom><![CDATA[ ]]></quantity__stduom>");
					valueXmlString.append("<unit__std><![CDATA[ ]]></unit__std>");
					
					valueXmlString.append("<pallet_wt><![CDATA[ ]]></pallet_wt>");
					valueXmlString.append("<no_art><![CDATA[ ]]></no_art>");
					valueXmlString.append("<rate__stduom><![CDATA[ ]]></rate__stduom>");
					valueXmlString.append("<conv__rtuom_stduom><![CDATA[ ]]></conv__rtuom_stduom>");
					valueXmlString.append("<quantity_real><![CDATA[ ]]></quantity_real>");
					
					valueXmlString.append("<pending_qty><![CDATA[ ]]></pending_qty>");
					valueXmlString.append("<quantity__ord><![CDATA[ ]]></quantity__ord>");
					valueXmlString.append("<gross_weight><![CDATA[ ]]></gross_weight>");
					valueXmlString.append("<tare_weight><![CDATA[ ]]></tare_weight>");
					valueXmlString.append("<dimension><![CDATA[ ]]></dimension>");
					valueXmlString.append("<pallet_wt><![CDATA[ ]]></pallet_wt>");		*/				
					
					
					valueXmlString.append("</Detail3>");
					
					
				}

				else if(currentColumn.trim().equalsIgnoreCase("lot_sl"))
				{
					System.out.println("Form 3 lot_sl item change333333333..............");
					String lotSlL="",siteCodeL="",dimension="",itemDescr="";
					double qtyStk=0,palletWt=0,cAllocQty=0;					
					lotSlL=checkNull(genericUtility.getColumnValue("lot_sl", dom2,"3"));
					System.out.println("lotSlL--->2["+lotSlL+"]");
					siteCodeL=getColumnDescr(conn, "site_code", "sorder", "sale_order", sorderNo);
					
					
					sql = "select LOC_CODE,LOT_NO,ITEM_CODE,QUANTITY,GROSS_WEIGHT,NET_WEIGHT,TARE_WEIGHT,NO_ART from stock where site_code = ? AND LOT_SL = ? AND QUANTITY > 0";
					pstmt1=conn.prepareStatement(sql);
					pstmt1.setString(1,siteCodeL);
					pstmt1.setString(2,lotSlL);

					rs1 = pstmt1.executeQuery();
					if(rs1.next())
					{
						locCode =  rs1.getString("loc_code")==null?"":rs1.getString("loc_code").trim();
						lotNo = rs1.getString("lot_no")==null?"":rs1.getString("lot_no").trim();
						itemCode = rs1.getString("item_code")==null?"":rs1.getString("item_code").trim();
						squantity = rs1.getDouble("quantity");
						grossWeight = rs1.getDouble("gross_weight");
						netWeight = rs1.getDouble("net_weight");
						tareWeight = rs1.getDouble("tare_weight");
						noOfArt = rs1.getDouble("no_art");
						
                    }
					pstmt1.close();
					rs1.close();
					pstmt1 = null;
					rs1 = null;
					//sord_no,line_no__sord,lot_no,no_art,mfg_date,exp_date,item_code__ord,loc_code
					//gross_weight,nett_weight,tare_weight
					itemDescr=getColumnDescr(conn, "descr", "item", "item_code", itemCode);
					String domID1="1";
					//valueXmlString.append("<sord_no><![CDATA["+ itemCode +"]]></sord_no>");
					//valueXmlString.append("<line_no__sord><![CDATA["+ itemCode +"]]></line_no__sord>");
					valueXmlString.append("<Detail3 domID='" +domID1+ "'  objContext = '"+currentFormNo+"' selected=\"Y\">\r\n");
					valueXmlString.append("<item_code protect=\"0\">").append("<![CDATA[" + itemCode + "]]>").append("</item_code>");					
					setNodeValue( dom2, "item_code", getAbsString(""+itemCode));
					
					valueXmlString.append("<item_descr protect=\"0\">").append("<![CDATA[" + itemDescr + "]]>").append("</item_descr>");					
					setNodeValue( dom2, "item_descr", getAbsString(""+itemDescr));
					
					valueXmlString.append("<item_code__ord protect=\"0\">").append("<![CDATA[" + itemCode + "]]>").append("</item_code__ord>");	
					setNodeValue( dom2, "item_code__ord", getAbsString(""+itemCode));
					
					valueXmlString.append("<lot_no protect=\"0\">").append("<![CDATA[" + lotNo + "]]>").append("</lot_no>");	
					setNodeValue( dom2, "lot_no", getAbsString(""+lotNo));
					
					valueXmlString.append("<no_art protect=\"0\">").append("<![CDATA[" + noOfArt + "]]>").append("</no_art>");	
					setNodeValue( dom2, "no_art", getAbsString(""+noOfArt));
					
					valueXmlString.append("<loc_code protect=\"0\">").append("<![CDATA[" + locCode + "]]>").append("</loc_code>");	
					setNodeValue( dom2, "loc_code", getAbsString(""+locCode));
					
					valueXmlString.append("<quantity protect=\"0\">").append("<![CDATA[" + qtyStk + "]]>").append("</quantity>");	
					setNodeValue( dom2, "quantity", getAbsString(""+qtyStk));
					
					valueXmlString.append("<gross_weight protect=\"0\">").append("<![CDATA[" + grossWeight + "]]>").append("</gross_weight>");	
					setNodeValue( dom2, "gross_weight", getAbsString(""+grossWeight));
					
					valueXmlString.append("<nett_weight protect=\"0\">").append("<![CDATA[" + netWeight + "]]>").append("</nett_weight>");	
					setNodeValue( dom2, "nett_weight", getAbsString(""+netWeight));
					
					valueXmlString.append("<tare_weight protect=\"0\">").append("<![CDATA[" + tareWeight + "]]>").append("</tare_weight>");	
					setNodeValue( dom2, "tare_weight", getAbsString(""+tareWeight));
					
					valueXmlString.append("</Detail3>");
					/*String currDomStr="";
					currDomStr = currDomStr.replace("</Detail3>", valueXmlString.toString() + "</Detail3>");
					System.out.println("after currDomStr------>>[" + currDomStr + "]");
					valueXmlString.append(currDomStr);*/
					
				}


				break;

			} //end switch

			/*if(("lot_sl".equalsIgnoreCase(currentColumn)) || ("tran_code".equalsIgnoreCase(currentColumn)))
			{
				System.out.println("CHK VAL");
				String currDomStr = genericUtility.serializeDom(dom);
				System.out.println("currDomStr[" + currDomStr + "]");
				StringBuffer valueXmlStr = new StringBuffer(currDomStr);
				System.out.println("@@@@@@@@@@@ after serialize : valueXmlStr ["+valueXmlStr+"]");
				StringBuffer valueXmlString1 = new StringBuffer("<?xml version=\"1.0\"?><Root><header><editFlag>");
				valueXmlString1.append(editFlag).append("</editFlag></header>");
				valueXmlString1.append(valueXmlStr);
				valueXmlString = valueXmlString1;
			}*/

			valueXmlString.append("</Root>"); 
		}
		catch(Exception e) 
		{
			e.printStackTrace();
			System.out.println("Exception ::"+ e.getMessage()); 
			try {
				conn.rollback();				
			} catch (Exception d) {
				d.printStackTrace();
			}
			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(); 
			}
		}
		System.out.println("Return item_change xml---->>["+valueXmlString.toString()+"]");
		return valueXmlString.toString();
	}


	private static String getAbsString( String str )
	{
		return ( str == null || str.trim().length() == 0 || "null".equalsIgnoreCase( str.trim() ) ? "" : str.trim() );
	}

	private static void setNodeValue( Document dom, String nodeName, String nodeVal ) throws Exception
	{
		Node tempNode = dom.getElementsByTagName( nodeName ).item(0);

		if( tempNode != null )
		{
			if( tempNode.getFirstChild() == null )
			{
				CDATASection cDataSection = dom.createCDATASection( nodeVal );
				tempNode.appendChild( cDataSection );
			}
			else
			{
				tempNode.getFirstChild().setNodeValue(nodeVal);
			}
		}
		tempNode = null;
	}
	private String getDetails(String mSiteCode,int mLineNoDist,String mDistOrder,String lsTranType,Connection conn)throws RemoteException,ITMException
	{//this method will return xml data
		String mItemCode = null,mTaxClass=null,mTaxChap=null,mTaxEnv = null;
		String lsUnitAlt = null,lsUnit = null,lsPackInstr =null;
		double mQty= 0,lcQty= 0,mRate = 0,mDiscount =0;

		String sql = null,lsTranTypeParent ="";
		ResultSet rs = null,rs1 =null;
		PreparedStatement pstmt = null,pstmt1 =null;
		StringBuffer detail2hdr = new StringBuffer("");
		try
		{
			sql="select item_code,((case when qty_confirm is null then 0 else qty_confirm end) - "
					+" (case when qty_shipped is null then 0 else qty_shipped end))	as qty,"
					+" ((case when qty_shipped is null then 0 else qty_shipped end) - "
					+" (case when qty_return is null then 0 else qty_return end)) as lcqty,"
					+" tax_class,tax_chap,tax_env,case when rate is null then 0 else rate end as rate,"
					+" case when discount is null then 0 else discount end as discount,	"
					+" rate__clg  ,UNIT__ALT ,UNIT,CONV__QTY__ALT,pack_instr "
					+" from 	distorder_det "
					+" where dist_order = '"+mDistOrder+"'  "
					+" and 	line_no    = "+mLineNoDist+"" 
					+ " AND   CASE WHEN STATUS IS NULL THEN 'O' ELSE STATUS END<>'C' ";//Added by manoj dtd 24/12/2013 to exclude closed line"

			//System.out.println("[DistIssueItemChangeEJB] sql=>"+sql);

			pstmt   = conn.prepareStatement(sql);
			//	pstmt.setString(1,mDistOrder);
			//pstmt.setInt(2,mLineNoDist);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				mItemCode = rs.getString("item_code")==null?"":rs.getString("item_code");
				mQty = rs.getDouble("qty");
				lcQty = rs.getDouble("lcqty");
				mTaxClass = rs.getString("tax_class")==null?"":rs.getString("tax_class");
				mTaxChap = rs.getString("tax_chap")==null?"":rs.getString("tax_chap");
				mTaxEnv = rs.getString("tax_env")==null?"":rs.getString("tax_env");
				mRate = rs.getDouble("rate");
				mDiscount = rs.getDouble("discount");
				lsUnitAlt = rs.getString("UNIT__ALT")==null?"":rs.getString("UNIT__ALT");
				lsPackInstr = rs.getString("pack_instr")==null?"":rs.getString("pack_instr");

				if(lsUnitAlt.trim().length() == 0)
				{
					lsUnitAlt = lsUnit;
				}

			//	detail2hdr.append("<item_code><![CDATA["+mItemCode+"]]></item_code>");
				detail2hdr.append("<unit><![CDATA["+lsUnit+"]]></unit>");
				detail2hdr.append("<unit__alt><![CDATA["+lsUnitAlt+"]]></unit__alt>");
				detail2hdr.append("<pack_instr><![CDATA["+lsPackInstr+"]]></pack_instr>");


				sql=" select tran_type__parent "
						+" from	distorder_type where  tran_type = '"+lsTranType+"' ";
				//System.out.println("[DistIssueItemChangeEJB] sql=>"+sql);
				pstmt1= conn.prepareStatement(sql);
				rs1 = pstmt1.executeQuery();
				if(rs1.next())
				{
					lsTranTypeParent = rs1.getString("tran_type__parent") == null ? "": rs1.getString("tran_type__parent").trim();
				}
				rs1.close();
				rs1 = null;
				pstmt1.close();
				pstmt1 = null;

				if(! lsTranTypeParent.equalsIgnoreCase(lsTranType.trim()))
				{
					detail2hdr.append("<quantity>"+lcQty+"</quantity>");
					detail2hdr.append("<qty_order__alt>"+lcQty+"</qty_order__alt>");
					lcQty = lcQty;
				}
				else
				{
					detail2hdr.append("<quantity>"+mQty+"</quantity>");
					detail2hdr.append("<qty_order__alt>"+mQty+"</qty_order__alt>");
					lcQty = mQty;
				}
				detail2hdr.append("<tax_class><![CDATA["+mTaxClass+"]]></tax_class>");
				detail2hdr.append("<tax_chap><![CDATA["+mTaxChap+"]]></tax_chap>");
				detail2hdr.append("<tax_env><![CDATA["+mTaxEnv+"]]></tax_env>");
				detail2hdr.append("<rate>"+mRate+"</rate>");
				detail2hdr.append("<discount>"+mDiscount+"</discount>");
				//System.out.println("[CreateDistIssue] xml return ==>"+detail2hdr.toString());
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;

		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		System.out.println("chandni inside detail::"+detail2hdr.toString());
		return detail2hdr.toString();
	}

	private String calcExpiry(String tranDate, int months)
	{
		java.util.Date expDate = new java.util.Date();
		java.util.Date retDate = new java.util.Date();
		String retStrInDate = "";
		//System.out.println("tranDate :"+tranDate+"\nmonths :"+months);
		try
		{
			GenericUtility genericUtility = GenericUtility.getInstance();
			SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
			if (months > 0)
			{
				Calendar  cal = Calendar.getInstance();
				expDate = sdf.parse(tranDate);
				//System.out.println("expDate :"+expDate);
				cal.setTime(expDate);
				cal.add(Calendar.MONTH,months);
				//for last day of the month
				cal.add(Calendar.MONTH,1);
				cal.set(Calendar.DATE,0);
				//sets zero to get the last day of the given date
				retDate = cal.getTime();
				retStrInDate = sdf.format(retDate);
			}
			else
			{
				retStrInDate = tranDate;
			}
		}
		catch(Exception e)
		{
			//System.out.println("The Exception occurs in calcExpiry :"+e);
		}
		//System.out.println("retStrInDate :"+retStrInDate);
		return retStrInDate;
	}
	private double getIntegralQty(String siteCode, String itemCode, String lotNo, String packCode, String checkIntegralQty)
	{
		double integralQty = 0;
		String sql = "";
		ResultSet rs = null;
		Connection conn = null;
		ConnDriver connDriver = new ConnDriver();
		Statement stmt = null;
		try
		{
			conn = connDriver.getConnectDB("DriverITM");
			stmt = conn.createStatement();
			char type = checkIntegralQty.charAt(0);
			//System.out.println("type==>"+type);
			switch (type)
			{
			case 'S':
				sql ="SELECT CASE WHEN SHIPPER_SIZE IS NULL THEN 0 ELSE SHIPPER_SIZE END "
						+"FROM ITEM_LOT_PACKSIZE "
						+"WHERE ITEM_CODE = '"+itemCode+"' "
						+"AND LOT_NO__FROM <= '"+lotNo+"' "
						+"AND LOT_NO__TO   >= '"+lotNo+"' ";
				System.out.println("sql :"+sql);
				rs = stmt.executeQuery(sql);
				if (rs.next())
				{
					integralQty = rs.getDouble(1);
					//System.out.println("integralQty :"+integralQty);
				}
				if (integralQty == 0)
				{
					sql = "SELECT CASE WHEN CAPACITY IS NULL THEN 0 ELSE CAPACITY END "
							+"FROM PACKING WHERE PACK_CODE = '"+packCode+"'";
					System.out.println("sql :"+sql);
					rs = stmt.executeQuery(sql);
					if (rs.next())
					{
						integralQty = rs.getDouble(1);
						//System.out.println("integralQty :"+integralQty);
					}
					if (integralQty == 0)
					{
						sql = "SELECT REO_QTY FROM SITEITEM "
								+"WHERE SITE_CODE = '"+siteCode+"'"+" AND ITEM_CODE = '"+itemCode+"'";
						System.out.println("sql :"+sql);	
						rs = stmt.executeQuery(sql);
						if (rs.next())
						{
							integralQty = rs.getDouble(1);
							//System.out.println("integralQty :"+integralQty);
						}
						if (integralQty == 0)
						{
							sql = "SELECT REO_QTY FROM ITEM "
									+"WHERE ITEM_CODE = '"+itemCode+"'";
							System.out.println("sql :"+sql);	
							rs = stmt.executeQuery(sql);
							if (rs.next())
							{
								integralQty = rs.getDouble(1);
								//System.out.println("integralQty :"+integralQty);
							}
						}
					}
				}

				break;
			case 'P':
				sql = "SELECT CASE WHEN CAPACITY IS NULL THEN 0 ELSE CAPACITY END "
						+"FROM PACKING WHERE PACK_CODE = '"+packCode+"'";
				System.out.println("sql :"+sql);
				rs = stmt.executeQuery(sql);
				if (rs.next())
				{
					integralQty = rs.getDouble(1);
					//System.out.println("integralQty :"+integralQty);
				}

				break;
			case 'I':
				sql = "SELECT INTEGRAL_QTY FROM SITEITEM "
						+"WHERE SITE_CODE = '"+siteCode+"'"+" AND ITEM_CODE = '"+itemCode+"'";
				System.out.println("sql :"+sql);	
				rs = stmt.executeQuery(sql);
				if (rs.next())
				{
					integralQty = rs.getDouble(1);
					//System.out.println("integralQty :"+integralQty);
				}
				if (integralQty == 0)
				{
					sql = "SELECT INTEGRAL_QTY FROM ITEM WHERE ITEM_CODE = '"+itemCode+"'";
					System.out.println("sql :"+sql);
					rs = stmt.executeQuery(sql);
					if (rs.next())
					{
						integralQty = rs.getDouble(1);
						//System.out.println("integralQty :"+integralQty);
					}
				}

			}
			/* sql ="SELECT CASE WHEN SHIPPER_SIZE IS NULL THEN 0 ELSE SHIPPER_SIZE END "
				+"FROM ITEM_LOT_PACKSIZE "
				+"WHERE ITEM_CODE = '"+itemCode+"' "
				+"AND LOT_NO__FROM <= '"+lotNo+"' "
				+"AND LOT_NO__TO   >= '"+lotNo+"' ";
			System.out.println("sql :"+sql);
			rs = stmt.executeQuery(sql);
			if (rs.next())
			{
				integralQty = rs.getDouble(1);
				System.out.println("integralQty :"+integralQty);
			}
			if (integralQty == 0)
			{
				sql = "SELECT CASE WHEN CAPACITY IS NULL THEN 0 ELSE CAPACITY END "
					 +"FROM PACKING WHERE PACK_CODE = '"+packCode+"'";
				System.out.println("sql :"+sql);
				rs = stmt.executeQuery(sql);
				if (rs.next())
				{
					integralQty = rs.getDouble(1);
					System.out.println("integralQty :"+integralQty);
				}
				if (integralQty == 0)
				{
					sql = "SELECT INTEGRAL_QTY FROM SITEITEM "
						 +"WHERE SITE_CODE = '"+siteCode+"'"+" AND ITEM_CODE = '"+itemCode+"'";
					System.out.println("sql :"+sql);	
					rs = stmt.executeQuery(sql);
					if (rs.next())
					{
						integralQty = rs.getDouble(1);
						System.out.println("integralQty :"+integralQty);
					}
					if (integralQty == 0)
					{
						sql = "SELECT INTEGRAL_QTY FROM ITEM WHERE ITEM_CODE = '"+itemCode+"'";
						System.out.println("sql :"+sql);
						rs = stmt.executeQuery(sql);
						if (rs.next())
						{
							 integralQty = rs.getDouble(1);
							System.out.println("integralQty :"+integralQty);
						}
					}
				}
			} */
			conn.close();	
		}
		catch(Exception e)
		{
			System.out.println("the exception occurs in getIntegralQty :"+e);
		}
		System.out.println("integralQty :"+integralQty);
		return integralQty;
	}

	private int getNoArt(String siteCode, String custCode, String itemCode, String packCode, double qty, char type, double shipperQty, double integralQty1)
	{
		String sql = "";
		ResultSet rs = null;
		Connection conn = null;
		ConnDriver connDriver = new ConnDriver();
		Statement stmt = null;
		double reoQty = 0d, capacity = 0d, integralQty = 0d, mod = 0d, noArt3 = 0d;
		double noArt = 0, noArt1 = 0, noArt2 = 0; 
		try
		{
			conn = connDriver.getConnectDB("DriverITM");
			stmt = conn.createStatement();
			System.out.println("type :"+type);
			switch (type)
			{
			case 'S':
				sql = "SELECT CASE WHEN CAPACITY IS NULL THEN 0 ELSE CAPACITY END " 
						+"FROM PACKING WHERE PACK_CODE = '"+packCode+"'";
				rs = stmt.executeQuery(sql);
				if (rs.next())
				{
					capacity = rs.getDouble(1);
					//System.out.println("capacity :"+capacity);
				}
				else
				{
					capacity = 0;
				}
				sql = "SELECT REO_QTY FROM SITEITEM WHERE SITE_CODE = '"+siteCode+"' " 
						+"AND ITEM_CODE = '"+itemCode+"'";
				System.out.println("sql :"+sql);
				rs = stmt.executeQuery(sql);
				if (rs.next())
				{
					reoQty = rs.getDouble(1);
					//System.out.println("reoQty :"+reoQty);
				}
				if( reoQty == 0 )
				{
					sql = "SELECT REO_QTY FROM ITEM WHERE ITEM_CODE = '"+itemCode+"'";
					System.out.println("sql :"+sql);
					rs = stmt.executeQuery(sql);
					if (rs.next())
					{
						reoQty = rs.getDouble(1);
						//System.out.println("reoQty :"+reoQty);
					}
				}
				if (reoQty == 0)
				{
					reoQty = 0;
				}
				if (capacity > 0)
				{
					shipperQty = capacity;
					//System.out.println("shipperQty :"+shipperQty);
				}
				else
				{
					shipperQty = reoQty;
					//System.out.println("shipperQty :"+shipperQty);
				}
				System.out.println("shipperQty :"+shipperQty);
				if (shipperQty > 0)
				{
					mod = qty%shipperQty;
					System.out.println("mod :"+mod);
					noArt = (qty - mod) / shipperQty;
				}
				//System.out.println("noArt :"+noArt);
				break;
			case 'I':
				sql = "SELECT INTEGRAL_QTY FROM CUSTOMERITEM "
						+"WHERE CUST_CODE = '"+custCode+"'"+" AND ITEM_CODE = '"+itemCode+"'";
				System.out.println("sql :"+sql);
				rs = stmt.executeQuery(sql);
				if (rs.next())
				{
					integralQty = rs.getDouble(1);
					//System.out.println("integralQty :"+integralQty);
				}
				if (integralQty == 0)
				{
					sql = "SELECT INTEGRAL_QTY FROM SITEITEM "
							+"WHERE SITE_CODE = '"+siteCode+"'"+" AND ITEM_CODE = '"+itemCode+"'";
					System.out.println("sql :"+sql);
					rs = stmt.executeQuery(sql);
					if (rs.next())
					{
						integralQty = rs.getDouble(1);
						//System.out.println("integralQty :"+integralQty);
					}
					if (integralQty == 0)
					{
						sql = "SELECT INTEGRAL_QTY FROM ITEM WHERE ITEM_CODE = '"+itemCode+"'";
						System.out.println("sql :"+sql);
						rs = stmt.executeQuery(sql);
						if (rs.next())
						{
							integralQty = rs.getDouble(1);
							//System.out.println("integralQty :"+integralQty);
						}
					}
				}
				if (integralQty > 0)
				{
					mod = qty%integralQty;
					System.out.println("mod :"+mod);
					noArt = (qty - mod) / integralQty;
					//System.out.println("noArt :"+noArt);
				}
				break;
			case 'B' :
				sql = "SELECT CASE WHEN CAPACITY IS NULL THEN 0 ELSE CAPACITY END " 
						+"FROM PACKING WHERE PACK_CODE = '"+packCode+"'";
				System.out.println("sql :"+sql);
				rs = stmt.executeQuery(sql);
				if (rs.next())
				{
					capacity = rs.getDouble(1);
					//System.out.println("capacity :"+capacity);
				}
				else
				{
					capacity = 0;
				}
				sql = "SELECT REO_QTY FROM SITEITEM WHERE SITE_CODE = '"+siteCode+"' " 
						+"AND ITEM_CODE = '"+itemCode+"'";
				System.out.println("sql :"+sql);
				rs = stmt.executeQuery(sql);
				if (rs.next())
				{
					reoQty = rs.getDouble(1);
					//System.out.println("reoQty :"+reoQty);
				}
				if( reoQty == 0 )
				{
					sql = "SELECT REO_QTY FROM ITEM WHERE ITEM_CODE = '"+itemCode+"'";
					System.out.println("sql :"+sql);
					rs = stmt.executeQuery(sql);
					if (rs.next())
					{
						reoQty = rs.getDouble(1);
						//System.out.println("reoQty :"+reoQty);
					}
				}
				if (capacity > 0)
				{
					shipperQty = capacity;
					//System.out.println("shipperQty :"+shipperQty);
				}
				else
				{
					shipperQty = reoQty;
					//System.out.println("shipperQty :"+shipperQty);
				}
				if (shipperQty > 0)
				{
					mod = (qty % shipperQty);
					noArt1 = (qty - mod) / shipperQty;
				}
				sql = "SELECT INTEGRAL_QTY FROM CUSTOMERITEM "
						+"WHERE CUST_CODE = '"+custCode+"' "
						+"AND ITEM_CODE = '"+itemCode+"'";
				System.out.println("sql :"+sql);
				rs = stmt.executeQuery(sql);
				if (rs.next())
				{
					integralQty = rs.getDouble(1);
					//System.out.println("integralQty :"+integralQty);
				}
				if (integralQty == 0)
				{
					sql ="SELECT INTEGRAL_QTY FROM SITEITEM "
							+"WHERE SITE_CODE = '"+siteCode+"' " 
							+"AND ITEM_CODE = '"+itemCode+"'";
					System.out.println("sql :"+sql);
					rs = stmt.executeQuery(sql);
					if (rs.next())
					{
						integralQty = rs.getDouble(1);
						//System.out.println("integralQty :"+integralQty);
					}
					if(integralQty == 0)
					{
						sql = "SELECT INTEGRAL_QTY FROM ITEM WHERE ITEM_CODE = '"+itemCode+"'";
						System.out.println("sql :"+sql);
						rs = stmt.executeQuery(sql);
						if (rs.next())
						{
							integralQty = rs.getDouble(1);
							//System.out.println("integralQty :"+integralQty);
						}
					}
				}
				double remainder1 = 0d;
				if (integralQty > 0)
				{
					remainder1 = mod % integralQty;
					System.out.println("remainder1 :"+remainder1);
					noArt3 =(mod - remainder1) / integralQty;
					noArt2 = (int)noArt3;
				}
				if (noArt2 > 0)
				{
					noArt2 = 1;
				}
				noArt  = noArt1 + noArt2;
				System.out.println("noArt :"+noArt);
			}
			conn.close();
			if (noArt == 0)
			{
				noArt = 0;
			}
		}
		catch(Exception e)
		{
			System.out.println("Exception occures in getNoArt :"+e);
		}
		System.out.println("(int)noArt :"+(int)noArt);
		return (int)noArt;
	}
	public String getFormatedValue(double actVal,int prec)throws RemoteException//This method is added by nisar on 11/23/2007
	{//this method is used to return double with appropriate precison
		NumberFormat numberFormat = NumberFormat.getIntegerInstance ();
		Double DoubleValue = new Double (actVal);
		numberFormat.setMaximumFractionDigits(prec);
		String strValue = numberFormat.format(DoubleValue);
		//System.out.println(strValue);
		strValue = strValue.replaceAll(",","");
		return strValue;
	}
	private String getCurrdateAppFormat()
	{
		String s = "";
		GenericUtility genericUtility = GenericUtility.getInstance();
		try
		{
			java.util.Date date = null;
			Timestamp timestamp = new Timestamp(System.currentTimeMillis());
			//System.out.println(genericUtility.getDBDateFormat());

			SimpleDateFormat simpledateformat = new SimpleDateFormat(genericUtility.getDBDateFormat());
			date = simpledateformat.parse(timestamp.toString());
			timestamp = Timestamp.valueOf(simpledateformat.format(date).toString() + " 00:00:00.0");
			s = (new SimpleDateFormat(genericUtility.getApplDateFormat())).format(timestamp).toString();
		}
		catch(Exception exception)
		{
			//System.out.println("Exception in [MPSOrder] getCurrdateAppFormat " + exception.getMessage());
		}
		return s;
	}
	protected int getDBRowCount(Connection conn, String table_name, String whrCondCol, String whrCondVal)
	{
		int count=0;				
		ResultSet rs=null;
		PreparedStatement pstmt = null;
		
		String sql="select count(1) from "+table_name+" where "+whrCondCol+" = ?";
		System.out.println("SQL in getDBRowCount method : "+sql);
		try{
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,whrCondVal);
			rs = pstmt.executeQuery();
			if(rs.next()){
			count = rs.getInt(1);
			}
			if(pstmt!=null){
			pstmt.close();
			pstmt = null;
			}
			if(rs!=null){
			rs.close();
			rs = null;
			}
		}
		
		catch(Exception ex){
			System.out.println("Exception In getDBRowCount method of DespatchWiz Class : "+ex.getMessage());
			ex.printStackTrace();
		}
		System.out.println("Return count from getDBRowCount ----->>["+count+"]");					
		return count;
	}
	private String getColumnDescr(Connection conn, String columnName ,String tableName, String columnName2, String value) 
	{

			PreparedStatement pstmt = null ;
			ResultSet rs = null ; 
			String sql = "";
			String findValue = "";
			try
			{			
				sql = "SELECT " + columnName + " from " + tableName + " where " + columnName2 +"= ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,value);
				rs = pstmt.executeQuery();
				if(rs.next())
				{					
					findValue = rs.getString(1);
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;	        
			}
			catch(Exception e)
			{
				System.out.println("Exception in getColumnDescr ");
				e.printStackTrace();
			}
			System.out.println("returning String from getColumnDescr " + findValue);
			return findValue;
		 
	}
	public String getColumnValueMy(String colName, Document dom, String formNo) 
	{
		Node elementName = null, parentNode = null;
		NodeList elementList = null;
		Element elementAttr = null;
		String columnName = "";
		String columnValue = null;
		boolean continueLoop = true;
		boolean isFormNo = false;
		int ctr;
		try
		{
			elementList = dom.getElementsByTagName(colName);
			
			if (Integer.parseInt(formNo) < 1)//parseInt takes the String type as input and treats the value as a particulas integer value.
			{
				isFormNo = true;
			}
			System.out.println("elementList length--->>"+ elementList.getLength());
			for(ctr = 0; ctr < elementList.getLength(); ctr++)
			{
				elementName = elementList.item(ctr);//item returns the indexed item in the collection
				parentNode = elementName.getParentNode();//get the parent node name
				elementAttr = (Element)parentNode;
				String domId=elementAttr.getAttribute("domID");
				return domId;
			}
		}
		catch(Exception e)
		{
			System.out.println("Exception : [GenericUtility][getColumnValue(2)] :==>\n"+e.getMessage());
			//throw new BaseException(e);
		}
		return columnValue;
	}
	
	public String checkNull(String str)
	{
		if(str == null){
			str="";
		}
		return str.trim();
	}
}
