package ibase.webitm.ejb.mfg;

import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.ValidatorEJB;
import ibase.webitm.ejb.dis.DistCommon;
//import ibase.webitm.ejb.fin.FinCommon;
//import ibase.webitm.ejb.mfg.MfgCommon;
//import ibase.webitm.ejb.mfg.WorkOrderAmd;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;

import java.text.SimpleDateFormat;
import java.util.*;
import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import javax.ejb.Stateless;
import java.sql.Timestamp;

@Stateless
public class QCTransferIC extends ValidatorEJB implements QCTransferICLocal,
QCTransferICRemote {
	GenericUtility genericUtility = GenericUtility.getInstance();

	// method for validation
	public String wfValData(String xmlString, String xmlString1,
			String xmlString2, String objContext, String editFlag,
			String xtraParams) throws RemoteException, ITMException {
		String errString = "";
		System.out.println("wfValdata() called for QCTransferIC");
		Document dom = null;
		Document dom1 = null;
		Document dom2 = null;
		try {
			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(xmlString2);
			}
			errString = wfValData(dom, dom1, dom2, objContext, editFlag,
					xtraParams);
		} catch (Exception e) {
			throw new ITMException(e);
		}
		return (errString);
	}

	public String wfValData(Document dom, Document dom1, Document dom2,
			String objContext, String editFlag, String xtraParams)
					throws RemoteException, ITMException {
		String childNodeName = null;
		String errString = "";
		String errCode = "";
		String userId = "";
		String sql = "";
		String errorType = "";
		int cnt = 0;
		int ctr = 0;
		int childNodeListLength;
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;
		ArrayList<String> errList = new ArrayList<String>();
		ArrayList<String> errFields = new ArrayList<String>();
		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>");

		int currentFormNo = 0, recCnt = 0;
		int noOfParent = 0, ctr1 = 0;
		double maxQty = 0, minQty = 0;
		String tranDateStr = "", qorderNo = "", refNo = "", tranId = "", itemCode = "", locCode = "", lotNo = "", lotSl = "", siteCode = "";
		String status = "", porcpNo = "", porcpLineNo = "", qcLocCode = "", minQtyStr = "", available = "", keyFlag = "";
		Timestamp date2 = null, tranDate = null;
		String faciLocCode = "", faciSiteCode = "";

		double aprvLeadTime = 0;
		Timestamp qcOrderDate = null,WORecTranDate=null; // added by cpatil on 02/11/13
        Boolean workOrdCntflag = false ;
        String qcOrderItem="";
        int countItem=0;
        String qcOrdItemUnit="",byprodUnit="";
        double qcQty=0,netWeight=0,noArt=0;
		try {
			System.out.println("@@@@@@@@ wfvaldata called");
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			userId = getValueFromXTRA_PARAMS(xtraParams, "loginCode");
			if (objContext != null && objContext.trim().length() > 0) {
				currentFormNo = Integer.parseInt(objContext);
			}
			DistCommon dcommon=new DistCommon();
			switch (currentFormNo) {
			case 1:
				parentNodeList = dom.getElementsByTagName("Detail1");
				parentNode = parentNodeList.item(0);
				childNodeList = parentNode.getChildNodes();
				childNodeListLength = childNodeList.getLength();

				for (ctr = 0; ctr < childNodeListLength; ctr++) {
					childNode = childNodeList.item(ctr);
					childNodeName = childNode.getNodeName();

					if (childNodeName.equalsIgnoreCase("tran_id")) {
						tranId = checkNull(genericUtility.getColumnValue(
								"tran_id", dom));

						System.out.println("@@@@ tran_id validation ");

						keyFlag = setDescription("key_flag", "transetup",
								"tran_window", "w_qc_transfer", conn);
						System.out.println("@@@@ tran_id [" + tranId
								+ "]:::::keyFlag[" + keyFlag + "]");

						if (tranId != null && tranId.length() > 0
								&& "M".equalsIgnoreCase(keyFlag)) {
							sql = " Select count(1) from qc_transfer where tran_id = ? ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, tranId);
							rs = pstmt.executeQuery();
							if (rs.next()) {
								cnt = rs.getInt(1);
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;

							System.out.println("@@@@ cnt[" + cnt + "]");
							if (cnt > 0) {
								errCode = "VMTRANIDEX";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}

						}
					}

					if (childNodeName.equalsIgnoreCase("tran_date")) {
						tranDateStr = checkNull(genericUtility.getColumnValue(
								"tran_date", dom));
						qorderNo = checkNull(genericUtility.getColumnValue(
								"qorder_no", dom));
						siteCode = checkNull(genericUtility.getColumnValue(
								"site_code", dom));
						itemCode = checkNull(genericUtility.getColumnValue(
								"item_code", dom));

						System.out.println("@@@@ tranDate [" + tranDateStr
								+ "]:::qorderNo[" + qorderNo + "]");
						if (qorderNo != null && qorderNo.length() > 0) 
						{
							sql = " Select qorder_date,item_code from qc_order"
									+ "	where  qorder_no = ? ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, qorderNo);
							rs = pstmt.executeQuery();
							if (rs.next()) 
							{
								date2 = rs.getTimestamp("qorder_date");
								qcOrderItem=rs.getString("item_code");
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;

							if (tranDateStr != null && tranDateStr.length() > 0) 
							{
								tranDate = Timestamp.valueOf(genericUtility
										.getValidDateString(tranDateStr,
												genericUtility
												.getApplDateFormat(),
												genericUtility
												.getDBDateFormat())
												+ " 00:00:00.0");
							}

							System.out.println("@@@@ tranDate [" + tranDate
									+ "]:::qorder_date[" + date2 + "]");

							if (tranDate.before(date2)) 
							{
								errCode = "QCTRFDATE";
								// ls_errcode = 'QCTRFDATE~t' + 'QC Transfer
								// Date cannot be less then QC Order Date'
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
							if(itemCode!=null && itemCode.trim().length()>0)
							{
								sql="select count(1) from item where item_code=?";
								pstmt=conn.prepareStatement(sql);
								pstmt.setString(1,itemCode);
								rs=pstmt.executeQuery();
								if(rs.next())
								{
									countItem=rs.getInt(1);
								}
								rs.close();
								rs=null;
								pstmt.close();
								pstmt=null;
								if(countItem==0)
								{
									errCode = "VTINVITEM";
									errList.add(errCode);
									errFields.add(childNodeName.toLowerCase());	
								}
								else
								{
									sql="SELECT UNIT FROM ITEM WHERE ITEM_CODE=?";
									pstmt=conn.prepareStatement(sql);
									pstmt.setString(1,itemCode);
									rs=pstmt.executeQuery();
									if(rs.next())
									{
										byprodUnit=rs.getString("UNIT");
									}
									rs.close();
									rs=null;
									pstmt.close();
									pstmt=null;
									
									sql="SELECT UNIT FROM ITEM WHERE ITEM_CODE=?";
									pstmt=conn.prepareStatement(sql);
									pstmt.setString(1,qcOrderItem);
									rs=pstmt.executeQuery();
									if(rs.next())
									{
										qcOrdItemUnit=rs.getString("UNIT");
									}
									rs.close();
									rs=null;
									pstmt.close();
									pstmt=null;
									
									
									
									
								}
										
							}
							else
							{
								errCode = "VTBLANKITM";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());	
							}
							

							// / added by cpatil on 01/11/13 start
/*
							System.out
							.println("@@@@ tranDate new validation start--------- ");
							
							sql = " Select aprv_lead_time from siteitem where   site_code =  ? and item_code = ? ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, siteCode);
							pstmt.setString(2, itemCode);
							rs = pstmt.executeQuery();
							if (rs.next()) 
							{
								aprvLeadTime = rs.getDouble("aprv_lead_time");
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;

							sql = " select tran_date from WORKORDER_RECEIPT " +
									" where tran_id in ( select porcp_no from qc_order where qorder_no = ? ) ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, qorderNo);
							rs = pstmt.executeQuery();
							if (rs.next())
							{
								workOrdCntflag = true;
								WORecTranDate = rs.getTimestamp("tran_date");
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;

							System.out.println("@@@@ site code[" + siteCode
									+ "]::item code[" + itemCode
									+ "]::aprvLeadTime[" + aprvLeadTime
									+ "]::tranDateStr[" + tranDateStr + "]:::WORecTranDate["+WORecTranDate+"]workOrdCntflag["+workOrdCntflag+"]");
						if( workOrdCntflag == true )	
						{	
							Calendar cal = Calendar.getInstance();
							cal.setTime(WORecTranDate);
							cal.add(Calendar.DAY_OF_WEEK,
									(int) Math.round(aprvLeadTime));
							qcOrderDate = new Timestamp(cal.getTime().getTime());
							System.out.println("@@@@ tranDate tranDate["+ tranDate + "]:::: new date wo receipt date ["+ qcOrderDate + "]:::::");
							
							if( tranDate.before(qcOrderDate)) 
							{
								errCode = "QCTRFDATEI";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
							System.out.println("@@@@ tranDate new validation end---------errCode["+ errCode + "]");
						  }
						*/
						// / added by cpatil on 01/11/13 end
						}
					}

					if (childNodeName.equalsIgnoreCase("qorder_no")) {
						cnt = 0;
						tranId = checkNull(genericUtility.getColumnValue(
								"tran_id", dom));
						qorderNo = checkNull(genericUtility.getColumnValue(
								"qorder_no", dom));
						itemCode = checkNull(genericUtility.getColumnValue(
								"item_code", dom));
						locCode = checkNull(genericUtility.getColumnValue(
								"loc_code", dom));
						lotNo = checkNull(genericUtility.getColumnValue(
								"lot_no", dom));
						lotSl = checkNull(genericUtility.getColumnValue(
								"lot_sl", dom));
						siteCode = checkNull(genericUtility.getColumnValue(
								"site_code", dom));

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

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

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

						sql = "	Select count(1) from qc_order "
								+ " Where  qorder_no   <> ? "
								+ " and 	 site_code	 = ? "
								+ " and	 item_code	 = ? "
								+ " and	 lot_no		 = ? "
								+ " and    loc_code	 = ? "
								+ " and    status = 'U' ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, qorderNo);
						pstmt.setString(2, siteCode);
						pstmt.setString(3, itemCode);
						pstmt.setString(4, lotNo);
						pstmt.setString(5, locCode);
						rs = pstmt.executeQuery();
						if (rs.next()) {
							cnt = rs.getInt(1);
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;

						System.out.println("@@@@@@@@9 cnt[" + cnt
								+ "]:::tranId[" + tranId + "]");
						if (cnt > 0) {
							errCode = "VTINVQN1";
							errList.add(errCode);
							errFields.add(childNodeName.toLowerCase());
						}

						System.out.println("@@@@@@@@ tranId[" + tranId + "]");
						if (tranId == null) {
							tranId = " ";
						}

						sql = "	Select count(1) from  qc_transfer "
								+ " Where  qorder_no = ?  and confirmed = 'N' and tran_id <> ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, qorderNo);
						pstmt.setString(2, tranId);
						rs = pstmt.executeQuery();
						if (rs.next()) {
							cnt = rs.getInt(1);
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						System.out.println("@@@@@@@@8 cnt[" + cnt + "]");
						if (cnt > 0) {
							System.out.println("@@@@@@@@@111QCTRFUCONF:cnt["
									+ cnt + "]");
							errCode = "QCTRFUCONF";
							errList.add(errCode);
							errFields.add(childNodeName.toLowerCase());
						}

						// if gs_runmode <> 'B' then
						//if (errCode == null || errCode.trim().length() == 0) {
							cnt = 0;
							sql = " Select status ,	porcp_no, porcp_line_no from   qc_order "
									+ " where  qorder_no = ? ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, qorderNo);
							rs = pstmt.executeQuery();
							if (rs.next()) {
								cnt++;
								status = rs.getString("status");
								porcpNo = rs.getString("porcp_no");
								porcpLineNo = rs.getString("porcp_line_no");
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;

							if (cnt == 0) {
								// ls_errcode = 'QCTRFORD~t' + 'QC order is not
								// found';
								errCode = "QCTRFORD";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							} else if ("C".equalsIgnoreCase(status)) {
								errCode = "QCTRF0001";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
								// ls_errcode = 'QCTRF0001~t' + 'QC order is
								// Confirmed. You cannot do transfer for QC
								// order no: ' + ls_qorder_no
							}
						//}

						//if (errCode == null || errCode.trim().length() == 0) {
							refNo = checkNull(genericUtility.getColumnValue(
									"porcp_no", dom));
							if ((lotSl != null && lotSl.trim().length() > 0)
									&& (refNo != null && refNo.trim().length() > 0)) {
								// ls_errcode =
								// gf_chk_pck_dtl(ls_sitecode,ls_itemcode,ls_loc,ls_lotno,ls_qorderno,ls_ref_no,ls_lot_sl)
								//gf
								qorderNo = checkNull(genericUtility.getColumnValue(
										"qorder_no", dom));
								sql = " Select item_code from qc_order"
										+ "	where  qorder_no = ? ";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, qorderNo);
								rs = pstmt.executeQuery();
								if (rs.next()) 
								{
									
									qcOrderItem=rs.getString("item_code");
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
								System.out.println("qcOrderItem----"+qcOrderItem);
								if(!qcOrderItem.equalsIgnoreCase(itemCode))
								{
									itemCode=qcOrderItem;
								}
								errCode = chkPckDtl(siteCode, itemCode,
										locCode, lotNo, qorderNo, refNo, lotSl,
										conn);
								if (errCode != null
										&& errCode.trim().length() > 0) {
									System.out.println("@@@@@ errCode["
											+ errCode + "]");
									errList.add(errCode);
									errFields.add(childNodeName.toLowerCase());
								}
							}
						//}
					}

					if (childNodeName.equalsIgnoreCase("lot_sl")) {
						porcpNo = checkNull(genericUtility.getColumnValue(
								"porcp_no", dom));
						itemCode = checkNull(genericUtility.getColumnValue(
								"item_code", dom));
						siteCode = checkNull(genericUtility.getColumnValue(
								"site_code", dom));
						qcLocCode = genericUtility.getColumnValue("loc_code",
								dom);
						lotNo = genericUtility.getColumnValue("lot_no", dom);
						lotSl = genericUtility.getColumnValue("lot_sl", dom);

						if (lotSl == null || lotSl.trim().length() == 0) {
							errCode = "QCTRFLOTSL"; // 'Invalid Lot Sl Please
							// enter Lot Sl'
							errList.add(errCode);
							errFields.add(childNodeName.toLowerCase());
						}

						//if (errCode == null || errCode.trim().length() == 0) {
							qorderNo = checkNull(genericUtility.getColumnValue(
									"qorder_no", dom));
							sql = " Select item_code from qc_order"
									+ "	where  qorder_no = ? ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, qorderNo);
							rs = pstmt.executeQuery();
							if (rs.next()) 
							{
								
								qcOrderItem=rs.getString("item_code");
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;
							
							if(!qcOrderItem.equalsIgnoreCase(itemCode))
							{
								itemCode=qcOrderItem;
							}
							
							sql = " Select Quantity from stock"
									+ " Where item_code = ? and	site_code = ? "
									+ " and loc_code = ? 	and	 lot_no =  ? "
									+ " and lot_sl = ?  ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, itemCode);
							pstmt.setString(2, siteCode);
							pstmt.setString(3, qcLocCode);
							pstmt.setString(4, lotNo);
							pstmt.setString(5, lotSl);

							rs = pstmt.executeQuery();
							if (rs.next()) {
								maxQty = rs.getDouble("Quantity");
								cnt++;
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;

							// If isnull(lc_maxqty) then lc_maxqty = 0
							if (cnt == 0) {
								errCode = "VTQTY51";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						//}
					}

					if (childNodeName.equalsIgnoreCase("quantity")) {
						itemCode = checkNull(genericUtility.getColumnValue(
								"item_code", dom));
						siteCode = checkNull(genericUtility.getColumnValue(
								"site_code", dom));
						qcLocCode = genericUtility.getColumnValue("loc_code",
								dom);
						lotNo = genericUtility.getColumnValue("lot_no", dom);
						lotSl = genericUtility.getColumnValue("lot_sl", dom);
						qorderNo = checkNull(genericUtility.getColumnValue(
								"qorder_no", dom));
						sql = " Select item_code from qc_order"
								+ "	where  qorder_no = ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, qorderNo);
						rs = pstmt.executeQuery();
						if (rs.next()) 
						{
							
							
							qcOrderItem=rs.getString("item_code");
							
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						
						if(!qcOrderItem.equalsIgnoreCase(itemCode))
						{
							itemCode=qcOrderItem;
						}
						
						sql = " Select Quantity from stock"
								+ " Where item_code = ? and	site_code = ? "
								+ "	and	loc_code = ? 	and	lot_no =  ? "
								+ " and	lot_sl = ?  ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, itemCode);
						pstmt.setString(2, siteCode);
						pstmt.setString(3, qcLocCode);
						pstmt.setString(4, lotNo);
						pstmt.setString(5, lotSl);

						rs = pstmt.executeQuery();
						if (rs.next()) {
							maxQty = rs.getDouble("quantity");
							cnt++;
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;

						if (cnt == 0) {
							errCode = "VTQTY51";
							errList.add(errCode);
							errFields.add(childNodeName.toLowerCase());
						}

						// If isnull(lc_maxqty) then lc_maxqty = 0

						//if (errCode == null || errCode.trim().length() == 0) {
							minQtyStr = genericUtility.getColumnValue(
									"quantity", dom);
							System.out.println("minQtyStr----"+minQtyStr);
							qorderNo = checkNull(genericUtility.getColumnValue(
									"qorder_no", dom));
							sql = " Select item_code from qc_order"
									+ "	where  qorder_no = ? ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, qorderNo);
							rs = pstmt.executeQuery();
							if (rs.next()) 
							{
								
								qcOrderItem=rs.getString("item_code");
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;
							
							
							sql="SELECT UNIT FROM ITEM WHERE ITEM_CODE=?";
							pstmt=conn.prepareStatement(sql);
							pstmt.setString(1,checkNull(genericUtility.getColumnValue("item_code", dom)));
							rs=pstmt.executeQuery();
							if(rs.next())
							{
								byprodUnit=rs.getString("UNIT");
							}
							rs.close();
							rs=null;
							pstmt.close();
							pstmt=null;
							
							sql="SELECT UNIT FROM ITEM WHERE ITEM_CODE=?";
							pstmt=conn.prepareStatement(sql);
							pstmt.setString(1,qcOrderItem);
							rs=pstmt.executeQuery();
							if(rs.next())
							{
								qcOrdItemUnit=rs.getString("UNIT");
							}
							rs.close();
							rs=null;
							pstmt.close();
							pstmt=null;
							if(!qcOrderItem.equalsIgnoreCase(itemCode))
							{
								itemCode=qcOrderItem;
							}
							minQty = minQtyStr == null ? 0 : Double
									.parseDouble(minQtyStr);
							
							if(!byprodUnit.equalsIgnoreCase(qcOrdItemUnit))
							{
								
								minQty=dcommon.convQtyFactor(byprodUnit, qcOrdItemUnit, "X", minQty, conn);
								System.out.println("quantity after converting to QC-------"+minQty);
							}
							
							
							if (minQty > maxQty) {
								errCode = "QCTRFQTY"; // 'QC Transfer Quantity
								// cannot be more then
								// Stock Quantity ' +
								// string(lc_maxqty)
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						//}
					}

					if (childNodeName.equalsIgnoreCase("loc_code__trf")) {
						qcLocCode = genericUtility.getColumnValue("loc_code",
								dom);
						locCode = genericUtility.getColumnValue(
								"loc_code__trf", dom);

						if (locCode != null) {
							sql = " Select Available from location a, invstat b "
									+ " Where a.loc_code = ? and a.inv_stat = b.inv_stat ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, locCode);
							rs = pstmt.executeQuery();
							if (rs.next()) {
								available = rs.getString("available");
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;

						}
						if (!("N".equalsIgnoreCase(available))) {
							errCode = "QCTRFLOC"; // "Invalid location, location should not be usable"
							errList.add(errCode);
							errFields.add(childNodeName.toLowerCase());
						}

						//if (errCode == null || errCode.trim().length() == 0) {
							if (qcLocCode.trim().equalsIgnoreCase(
									locCode.trim())) {
								errCode = "QCTRFLOC"; // 'From and To Location
								// Should be different'
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						//}
					}
					// Added by mahesh on 09-07-2014
					if (childNodeName.equalsIgnoreCase("loc_code")) {	
						
						qcLocCode = genericUtility.getColumnValue("loc_code",dom);
						qcLocCode = qcLocCode == null ? "" : qcLocCode.trim();
						
						sql = " Select facility_code from location Where loc_code = ?";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, qcLocCode);
						rs = pstmt.executeQuery();
						if (rs.next()) {
							faciLocCode = rs.getString(1);
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						
						sql = " Select facility_code from site Where site_code = ?";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, siteCode);
						rs = pstmt.executeQuery();
						if (rs.next()) {
							faciSiteCode = rs.getString(1);
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						
						faciLocCode = faciLocCode == null ? "" : faciLocCode.trim();
						faciSiteCode = faciSiteCode == null ? "" : faciSiteCode.trim();
						
						if(faciLocCode.length() > 0 && faciSiteCode.length() > 0){
							if(!faciLocCode.equalsIgnoreCase(faciSiteCode)){
								errCode = "VMFACI2"; // 'Diffrent Loc Code from location and site
								
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}
					}
				
					// End of code added by mahesh
				} // end for
				break; // case 1 end

			}

			int errListSize = errList.size();
			cnt = 0;
			String errFldName = null;
			if (errList != null && errListSize > 0) {
				for (cnt = 0; cnt < errListSize; cnt++) {
					errCode = errList.get(cnt);
					errFldName = errFields.get(cnt);
					System.out.println("errCode .........." + errCode);
					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;
					}
				}
				errList.clear();
				errList = null;
				errFields.clear();
				errFields = null;
				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;
	}// end of validation

	private String chkPckDtl(String siteCode, String itemCode, String locCode,
			String lotNo, String qorderNo, String refNo, String lotSl,
			Connection conn) throws SQLException {

		String sql = "", errCode = "";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int cnt = 0;
		String conf = "";
		double noArt = 0;

		sql = " select no_art from stock "
				+ " where item_code = ? and site_code = ? and loc_code = ? "
				+ " and  lot_no = ?  and lot_sl = ? ";
		pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, itemCode);
		pstmt.setString(2, siteCode);
		pstmt.setString(3, locCode);
		pstmt.setString(4, lotNo);
		pstmt.setString(5, lotSl);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			noArt = rs.getInt("no_art");
		}
		rs.close();
		rs = null;
		pstmt.close();
		pstmt = null;

		System.out.println("@@@@@@ noArt[" + noArt + "]");
		if (noArt > 1) {
			sql = " select confirmed from inv_pack_rcp,inv_pack "
					+ " where inv_pack_rcp.tran_id = inv_pack.tran_id "
					+ " and inv_pack_rcp.order_no = inv_pack.order_no "
					+ " and inv_pack_rcp.order_no = ? "
					+ " and inv_pack_rcp.item_code = ? "
					+ " and inv_pack_rcp.lot_no = ? "
					+ " and case when  inv_pack.status is null then 'U' else inv_pack.status end <> 'X' ";
					//+ " and inv_pack_rcp.loc_code = ? "
					//+ " and inv_pack_rcp.lot_sl = ?  "
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, refNo);
			pstmt.setString(2, itemCode);
			pstmt.setString(3, lotNo);           
			//pstmt.setString(4, locCode);     // commented for remove error by cpatil on 09/10/14
			//pstmt.setString(5, lotSl);         // commented for remove error by cpatil on 09/10/14
			rs = pstmt.executeQuery();
			if (rs.next()) {
				cnt++;
				conf = rs.getString("confirmed");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;

			System.out.println("@@@@@@ cnt[" + cnt + "]::::conf:[" + conf + "]");
			if (cnt == 0) {
				errCode = "VTNOPCKLST";
				return errCode;
			}
			if (!("Y".equalsIgnoreCase(conf))) {
				errCode = "VTPKNOCF";
				return errCode;
			}
		}
		return errCode;

	}

	// method for item change
	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;
		System.out.println("itemChanged() called for QCTransferIC");
		String valueXmlString = "";
		try {
			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(xmlString2);
			}
			valueXmlString = itemChanged(dom, dom1, dom2, objContext,
					currentColumn, editFlag, xtraParams);
		} catch (Exception e) {
			System.out
			.println("Exception : [QCTransferIC][itemChanged( String, String )] :==>\n"
					+ e.getMessage());
		}
		return valueXmlString;
	}

	// method for item change
	public String itemChanged(Document dom, Document dom1, Document dom2,
			String objContext, String currentColumn, String editFlag,
			String xtraParams) throws RemoteException, ITMException {
		String childNodeName = null;
		String sql = "";
		StringBuffer valueXmlString = new StringBuffer();
		int ctr = 0;
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		ConnDriver connDriver = new ConnDriver();

		int currentFormNo = 0;
		String columnValue = "", itemCode = "", itemDescr = "";
		String siteDescr = "", qorderNo = "", loginSiteCode = "", locCode = "", porcpNo = "", lotSl = "", batchNo = "", siteCode = "", lotNo = "";
		String locDescr = "", locCodeTrf = "";
		double quantity = 0;

		DistCommon discommon = new DistCommon();
		double stdQty1=0,convTemp=0,noArt=0,netWeight=0,stdQty2=0;
		ArrayList setNoartWeightQtyList = null,quantityList=null; 
		String quantityStr="",noArtUnit="",unit="",weightUnit="",netWeightStr="",noArtStr="";
		//String qcOrderItem="",qcOrdItemUnit="";
		double qcQty=0;
		double convertedQty=0,convertedNoArt=0,convertedNetWeight=0;
		double qtyPerArt=0.0,grossweightPerArt=0.0,tarWeightPerArt=0.0,noArt1=0.0;


		try {
			Calendar currentDate = Calendar.getInstance();
			SimpleDateFormat sdf = new SimpleDateFormat(
					genericUtility.getApplDateFormat());
			String sysDate = sdf.format(currentDate.getTime());
			System.out.println("Now the date is :=>  " + sysDate);

			conn = connDriver.getConnectDB("DriverITM");
			conn.setAutoCommit(false);
			connDriver = null;
			
			if (objContext != null && objContext.trim().length() > 0) {
				currentFormNo = Integer.parseInt(objContext.trim());
			}

			valueXmlString = new StringBuffer(
					"<?xml version = \"1.0\"?> <Root> <header> <editFlag>");
			valueXmlString.append(editFlag).append("</editFlag> </header>");

			System.out.println("**********ITEMCHANGE FOR CASE" + currentFormNo
					+ "**************");
			switch (currentFormNo) {
			case 1:
				parentNodeList = dom.getElementsByTagName("Detail1");
				parentNode = parentNodeList.item(0);
				childNodeList = parentNode.getChildNodes();
				ctr = 0;
				valueXmlString.append("<Detail1>");
				int childNodeListLength = childNodeList.getLength();
				do {
					childNode = childNodeList.item(ctr);
					childNodeName = childNode.getNodeName();
					if (childNodeName.equals(currentColumn)) {
						if (childNode.getFirstChild() != null) {
							columnValue = childNode.getFirstChild()
									.getNodeValue().trim();
						}
					}
					ctr++;
				} while (ctr < childNodeListLength
						&& !childNodeName.equals(currentColumn));
				System.out.println("[" + currentColumn + "] ==> '"
						+ columnValue + "'");
				qorderNo = checkNull(genericUtility.getColumnValue(
						"qorder_no", dom));
				

				if (currentColumn.trim().equalsIgnoreCase("itm_default")) {
					System.out.println("@@@@@@@@ itm_default called @@@@@@@@");

					loginSiteCode = genericUtility.getValueFromXTRA_PARAMS(
							xtraParams, "loginSiteCode");
					System.out.println("@@@@@@@@loginSiteCode[" + loginSiteCode
							+ "]");
					valueXmlString.append("<tran_date>")
					.append("<![CDATA[" + sysDate + "]]>")
					.append("</tran_date>");
					valueXmlString.append("<site_code>")
					.append("<![CDATA[" + loginSiteCode + "]]>")
					.append("</site_code>");
					siteDescr = setDescription("descr", "site", "site_code",
							loginSiteCode, conn);
					valueXmlString.append("<site_descr>")
					.append("<![CDATA[" + siteDescr + "]]>")
					.append("</site_descr>");
				}

				else if (currentColumn.trim().equalsIgnoreCase("qorder_no")) {
					System.out.println("@@@@@@@@ qorder_no called @@@@@@@@");
					qorderNo = checkNull(genericUtility.getColumnValue(
							"qorder_no", dom));

					sql = " Select quantity, item_code, loc_code, porcp_no, lot_no, lot_sl, batch_no, site_code"
							+ " from 	 qc_order where  qorder_no = ? ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, qorderNo);
					rs = pstmt.executeQuery();
					if (rs.next()) {
						quantity = rs.getDouble("quantity");
						itemCode = checkNull(rs.getString("item_code"));
						locCode = checkNull(rs.getString("loc_code"));
						porcpNo = checkNull(rs.getString("porcp_no"));
						lotNo = checkNull(rs.getString("lot_no"));
						lotSl = checkNull(rs.getString("lot_sl"));
						batchNo = checkNull(rs.getString("batch_no"));
						siteCode = checkNull(rs.getString("site_code"));
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;

					valueXmlString.append("<quantity__o>")
					.append("<![CDATA[" + quantity + "]]>")
					.append("</quantity__o>");
					valueXmlString.append("<item_code>")
					.append("<![CDATA[" + itemCode + "]]>")
					.append("</item_code>");
					valueXmlString.append("<loc_code>")
					.append("<![CDATA[" + locCode + "]]>")
					.append("</loc_code>");
					valueXmlString.append("<porcp_no>")
					.append("<![CDATA[" + porcpNo + "]]>")
					.append("</porcp_no>");
					valueXmlString.append("<lot_no>")
					.append("<![CDATA[" + lotNo + "]]>")
					.append("</lot_no>");
					valueXmlString.append("<lot_sl>")
					.append("<![CDATA[" + lotSl + "]]>")
					.append("</lot_sl>");
					valueXmlString.append("<site_code>")
					.append("<![CDATA[" + siteCode + "]]>")
					.append("</site_code>");
					valueXmlString.append("<batch_no>")
					.append("<![CDATA[" + batchNo + "]]>")
					.append("</batch_no>");

					if (locCode != null && locCode.trim().length() > 0) {
						locDescr = setDescription("descr", "location",
								"loc_code", locCode, conn);
						valueXmlString.append("<location_descr>")
						.append("<![CDATA[" + locDescr + "]]>")
						.append("</location_descr>");
					}

					if (itemCode != null && itemCode.trim().length() > 0) {
						itemDescr = setDescription("descr", "item",
								"item_Code", itemCode, conn);
						valueXmlString.append("<item_descr>")
						.append("<![CDATA[" + itemDescr + "]]>")
						.append("</item_descr>");
					}

				}
				else if(currentColumn.trim().equalsIgnoreCase("item_code"))
				{
					itemCode = checkNull(genericUtility.getColumnValue("item_code", dom));
					if (itemCode != null && itemCode.trim().length() > 0) 
					{
						itemDescr = setDescription("descr", "item","item_Code", itemCode, conn);
						valueXmlString.append("<item_descr>").append("<![CDATA[" + itemDescr + "]]>").append("</item_descr>");
					}
				}

				else if (currentColumn.trim().equalsIgnoreCase("lot_sl")) {
					System.out.println("@@@@@@@@ lot_sl called @@@@@@@@");
					qorderNo = checkNull(genericUtility.getColumnValue(
							"qorder_no", dom));

					if (qorderNo != null && qorderNo.trim().length() > 0) {
						locCode = setDescription("loc_code", "qc_order",
								"qorder_no", qorderNo, conn);
					}

					if (locCode == null || locCode.trim().length() == 0) {
						lotSl = genericUtility.getColumnValue("lot_sl", dom);
						lotNo = genericUtility.getColumnValue("lot_no", dom);
						siteCode = checkNull(genericUtility.getColumnValue(
								"site_code", dom));
						itemCode = checkNull(genericUtility.getColumnValue(
								"item_code", dom));

						sql = " select loc_code from stock "
								+ " where item_code = ? and lot_no = ? and lot_sl = ? and site_code = ? and quantity > 0 ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, itemCode);
						pstmt.setString(2, lotNo);
						pstmt.setString(3, lotSl);
						pstmt.setString(4, siteCode);
						rs = pstmt.executeQuery();
						if (rs.next()) {
							locCode = checkNull(rs.getString("loc_code"));
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;

						if (locCode != null && locCode.trim().length() > 0) {
							valueXmlString.append("<loc_code>")
							.append("<![CDATA[" + locCode + "]]>")
							.append("</loc_code>");
						}
					}
				}

				else if (currentColumn.trim().equalsIgnoreCase("loc_code__trf"))
				{
					System.out.println("@@@@@@@@ loc_code__trf called @@@@@@@@");
					locCodeTrf = checkNull(genericUtility.getColumnValue(
							"loc_code__trf", dom));
					if (locCodeTrf.trim().length() < 8) {
						locCodeTrf = locCodeTrf + "         ";
						locCodeTrf = locCodeTrf.substring(0, 8);
					}
					locDescr = setDescription("descr", "location", "loc_code",
							locCodeTrf, conn);
					valueXmlString.append("<location_transfer>")
					.append("<![CDATA[" + locDescr + "]]>")
					.append("</location_transfer>");
				}

				// // added by cpatil on 12-11-13 start
				else if (currentColumn.trim().equalsIgnoreCase("quantity")) 
				{
					System.out.println("@@@@@@@@ quantity called @@@@@@@@");
					quantityStr = checkNull(genericUtility.getColumnValue("quantity", dom));
					itemCode = checkNull(genericUtility.getColumnValue("item_code", dom));
					unit = setDescription("unit", "item", "item_code", itemCode, conn);
					quantity = quantityStr==null?0:Double.parseDouble(quantityStr);
					
					siteCode = checkNull(genericUtility.getColumnValue("site_code", dom));
					locCode = checkNull(genericUtility.getColumnValue("loc_code", dom));
					lotSl = genericUtility.getColumnValue("lot_sl", dom);
					lotNo = genericUtility.getColumnValue("lot_no", dom);
					/*sql = " Select item_code from qc_order"
							+ "	where  qorder_no = ? ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, qorderNo);
					rs = pstmt.executeQuery();
					if (rs.next()) 
					{
						qcOrderItem=rs.getString("item_code");
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					sql="SELECT UNIT FROM ITEM WHERE ITEM_CODE=?";
					pstmt=conn.prepareStatement(sql);
					pstmt.setString(1,qcOrderItem);
					rs=pstmt.executeQuery();
					if(rs.next())
					{
						qcOrdItemUnit=rs.getString("UNIT");
					}
					rs.close();
					rs=null;
					pstmt.close();
					pstmt=null;
					
					if(!unit.equalsIgnoreCase(qcOrdItemUnit))
					{
						
						quantity=dcommon.convQtyFactor(unit, qcOrdItemUnit, "X", quantity, conn);
						System.out.println("quantity after converting to QC-------"+quantity);
					}
					*/
					//Added by Manoj dtd 17/06/2014 to set values from qcorder
					sql = " Select Quantity, net_weight, no_art from qc_order"
							+ "	where  qorder_no = ? ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, qorderNo);
					rs = pstmt.executeQuery();
					if (rs.next()) 
					{
						
						
						
						qcQty= rs.getDouble("Quantity");
						netWeight= rs.getDouble("net_weight");
						noArt= rs.getDouble("no_art");
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					
					System.out.println("@@@@ qc order Qty[qcQty]["+qcQty+"]qc transfer qty[qty]["+quantity+"]netWeight["+netWeight+"]noArt["+noArt+"]");
					convertedNoArt = noArt * quantity / qcQty ;
					convertedNetWeight = netWeight * quantity / qcQty ;
					//Start added by chandrashekar on 27-Jan-2015
					if( netWeight == 0 && noArt == 0)
					{
						sql = " Select qty_per_art,gross_wt_per_art,tare_wt_per_art from  Stock" +
								" Where item_code = ? and site_code = ? and loc_code = ? and lot_no = ? and lot_Sl = ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, itemCode);	
						pstmt.setString(2, siteCode);
						pstmt.setString(3, locCode);
						pstmt.setString(4, lotNo);
						pstmt.setString(5, lotSl);
						rs = pstmt.executeQuery();
						if (rs.next())
						{
							qtyPerArt = Double.parseDouble(rs.getString("qty_per_art") == null ? "0" : rs.getString("qty_per_art"));
							grossweightPerArt = Double.parseDouble(rs.getString("gross_wt_per_art") == null ? "0" : rs.getString("gross_wt_per_art"));
							tarWeightPerArt = Double.parseDouble(rs.getString("tare_wt_per_art") == null ? "0" : rs.getString("tare_wt_per_art"));
						}
						rs.close();
						rs = null;
						pstmt.close(); 
						pstmt = null;
						
						noArt=quantity/qtyPerArt;
						System.out.println("noArt>>>>"+noArt);
						convertedNoArt=Math.ceil(noArt);
						convertedNetWeight=(grossweightPerArt-tarWeightPerArt)*noArt;
					}
					//End added by chandrashekar on 27-Jan-2015
					/*convertedQty =   quantity * qcQty  / netWeight ;
					
					//convertedNetWeight =  netWeight ;
					convertedNetWeight =  netWeight * convertedQty / qcQty  ;
					
					convertedNoArt =  noArt * quantity  / netWeight ;*/
					/*
					convertedNoArt = noArt * quantity / qcQty ;
					convertedNetWeight = netWeight * quantity / qcQty ;*///commented by chandrashekar
					
					System.out.println("@@@@convertedQty["+convertedQty+"]convertedNetWeight["+convertedNetWeight+"]convertedNoArt["+convertedNoArt+"]");
					valueXmlString.append("<no_art>").append("<![CDATA[" + convertedNoArt + "]]>").append("</no_art>");
					valueXmlString.append("<net_weight>").append("<![CDATA[" + convertedNetWeight + "]]>").append("</net_weight>");
					//Commented by Manoj dtd 17/06/2014 to set values from qcorder
					/*System.out.println("@@@@@@ unit["+unit+"]:::::quantity["+quantity+"]");
					setNoartWeightQtyList = setNoartWeightQty("Q",quantity,unit,itemCode, conn);
					if(setNoartWeightQtyList != null  && setNoartWeightQtyList.size() > 0 )
					{
						stdQty1 = Double.parseDouble( setNoartWeightQtyList.get(0).toString() );
					}
					valueXmlString.append("<no_art>").append("<![CDATA[" + stdQty1 + "]]>").append("</no_art>");
					if(setNoartWeightQtyList != null  && setNoartWeightQtyList.size() > 1 )
					{
						stdQty2 =  Double.parseDouble( setNoartWeightQtyList.get(1).toString() );
					}
					valueXmlString.append("<net_weight>").append("<![CDATA[" + stdQty2 + "]]>").append("</net_weight>");
					*/
				}

				else if (currentColumn.trim().equalsIgnoreCase("no_art")) 
				{
					System.out.println("@@@@@@@@ no_art called @@@@@@@@");
					noArtStr = checkNull(genericUtility.getColumnValue("no_art", dom));
					itemCode = checkNull(genericUtility.getColumnValue("item_code", dom));
					unit = setDescription("unit", "item", "item_code", itemCode, conn);
					System.out.println("@@@@@@ unit["+unit+"]:::::noArt["+noArt+"]");
					noArt = noArtStr==null?0:Double.parseDouble(noArtStr);

					setNoartWeightQtyList = setNoartWeightQty("A",noArt,unit,itemCode, conn);
					if(setNoartWeightQtyList != null  && setNoartWeightQtyList.size() > 0 )
					{
						stdQty1 = Double.parseDouble( setNoartWeightQtyList.get(0).toString() );
					}
					valueXmlString.append("<quantity>").append("<![CDATA[" + stdQty1 + "]]>").append("</quantity>");

					if(setNoartWeightQtyList != null  && setNoartWeightQtyList.size() > 1 )
					{
						stdQty2 =  Double.parseDouble( setNoartWeightQtyList.get(1).toString() );
					}
					valueXmlString.append("<net_weight>").append("<![CDATA[" + stdQty2 + "]]>").append("</net_weight>");
				}

				else if (currentColumn.trim().equalsIgnoreCase("net_weight")) 
				{
					System.out.println("@@@@@@@@ net_weight called @@@@@@@@");
					netWeightStr = checkNull(genericUtility.getColumnValue("net_weight", dom));
					itemCode = checkNull(genericUtility.getColumnValue("item_code", dom));
					unit = setDescription("unit", "item", "item_code", itemCode, conn);
					System.out.println("@@@@@@ unit["+unit+"]:::::netWeight["+netWeight+"]");
					netWeight = netWeightStr==null?0:Double.parseDouble(netWeightStr);

					setNoartWeightQtyList = setNoartWeightQty("W",netWeight,unit,itemCode, conn);
					if(setNoartWeightQtyList != null  && setNoartWeightQtyList.size() > 0 )
					{
						stdQty1 = Double.parseDouble( setNoartWeightQtyList.get(0).toString() );
					}
					valueXmlString.append("<quantity>").append("<![CDATA[" + stdQty1 + "]]>").append("</quantity>");

					if(setNoartWeightQtyList != null  && setNoartWeightQtyList.size() > 1 )
					{
						stdQty2 =  Double.parseDouble( setNoartWeightQtyList.get(1).toString() );
					}
					valueXmlString.append("<no_art>").append("<![CDATA[" + stdQty2 + "]]>").append("</no_art>");
				}


				// // added by cpatil on 12-11-13 end

				valueXmlString.append("</Detail1>");
				break;

			} // case 1 end
			valueXmlString.append("</Root>");
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("Exception ::" + 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();
			}
		}
		return valueXmlString.toString();
	}

	private ArrayList setNoartWeightQty(String flag,Double quantity,String unit,String itemCode,Connection conn) throws ITMException, Exception 
	{

		System.out.println("@@@@@@@@ setNoartWeightQty() called ["+flag+"]@@@@@@@@");
		String sql = "";
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		ArrayList retValue = new ArrayList();
		ArrayList convQtyFactorList = new ArrayList();
		MfgCommon mfgcommon = new MfgCommon();
		String mfgAutoConv = "", phyAattributeArt = "", phyAttributeWeight = "", noArtUnit = "", weightUnit = "";
		double stdQty1=0,convTemp=0,stdQty2=0;
		DistCommon discommon= new DistCommon();

		mfgAutoConv = mfgcommon.getEnvMfg("999999", "MFG_AUTO_CONV", conn);

		if (mfgAutoConv == null || "NULLFOUND".equalsIgnoreCase(mfgAutoConv) ||  mfgAutoConv.trim().length() == 0) 
		{
			mfgAutoConv = "N";
			return retValue;
		}

		phyAattributeArt = mfgcommon.getEnvMfg("999999", "MFG_NO_ART_UNIT",conn);
		phyAattributeArt = phyAattributeArt == null ? "" : phyAattributeArt.trim();

		phyAttributeWeight = mfgcommon.getEnvMfg("999999", "MFG_WEIGHT_UNIT",conn);
		phyAttributeWeight = phyAattributeArt == null ? "" : phyAttributeWeight.trim();

		if (phyAattributeArt == null || "NULLFOUND".equalsIgnoreCase(phyAattributeArt) || phyAattributeArt.trim().length() == 0) 
		{
			phyAattributeArt = "";
		}
		else
		{
			if ("Y".equalsIgnoreCase(mfgAutoConv)) 
			{
				sql = "SELECT " + phyAattributeArt + " FROM ITEM WHERE ITEM_CODE = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, itemCode);
				rs = pstmt.executeQuery();
				if (rs.next()) 
				{
					noArtUnit = checkNull(rs.getString(1));
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
			}
		}

		if (phyAttributeWeight == null || "NULLFOUND".equalsIgnoreCase(phyAttributeWeight) || phyAttributeWeight.trim().length() == 0) 
		{
			phyAttributeWeight = "";
		}
		else
		{
			if ("Y".equalsIgnoreCase(mfgAutoConv)) 
			{
				sql = "SELECT " + phyAttributeWeight + " FROM ITEM WHERE ITEM_CODE = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, itemCode);
				rs = pstmt.executeQuery();
				if (rs.next()) 
				{
					weightUnit = checkNull(rs.getString(1));
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
			}
		}

		System.out.println("@@@@@@@@@ noArtUnit["+noArtUnit+"]:::::weightUnit["+weightUnit+"]");

		if( noArtUnit != null && noArtUnit.trim().length() > 0  )	
		{	if("Q".equalsIgnoreCase(flag))
		{
			convQtyFactorList = discommon.convQtyFactor(unit,noArtUnit,itemCode, quantity, convTemp, conn);
		}
		else if("A".equalsIgnoreCase(flag))
		{
			convQtyFactorList = discommon.convQtyFactor(noArtUnit,unit,itemCode, quantity, convTemp, conn);
		}
		else if("W".equalsIgnoreCase(flag))
		{
			convQtyFactorList = discommon.convQtyFactor(weightUnit,unit,itemCode, quantity, convTemp, conn);
		}

		stdQty1 = Double.parseDouble( convQtyFactorList.get(1).toString() );
		retValue.add(stdQty1);		

		convTemp = 0;

		if("Q".equalsIgnoreCase(flag))
		{
			convQtyFactorList = discommon.convQtyFactor(unit,weightUnit,itemCode, quantity, convTemp, conn);
		}
		else if("A".equalsIgnoreCase(flag))
		{
			convQtyFactorList = discommon.convQtyFactor(noArtUnit,weightUnit,itemCode, quantity, convTemp, conn);
		}
		else if("W".equalsIgnoreCase(flag))
		{
			convQtyFactorList = discommon.convQtyFactor(weightUnit,noArtUnit,itemCode, quantity, convTemp, conn);
		}

		stdQty2 =  Double.parseDouble( convQtyFactorList.get(1).toString() );
		retValue.add(stdQty2);
		}
		System.out.println("stdQty1["+stdQty1+"]::::stdQty2["+stdQty2+"]");
		return retValue;
	}

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

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

	private String errorType(Connection conn, String errorCode) {
		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 = checkNull(rs.getString("MSG_TYPE"));
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return msgType;
	}

}
