package ibase.webitm.ejb.mfg.adv;

import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.ActionHandlerEJB;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.ejb.mfg.MfgCommon;
import ibase.utility.CommonConstants;
// import ibase.webitm.ejb.mfg.adv.WOStock.woStockDtlParams;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;

import java.io.ByteArrayOutputStream;
import java.io.Serializable;
import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.StringTokenizer;
import java.text.DecimalFormat;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;

import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import javax.ejb.Stateless; // added for ejb3

@Stateless // added for ejb3

public class WOrderIssueAct extends ActionHandlerEJB implements  WOrderIssueActLocal , WOrderIssueActRemote //SessionBean
{
	/*public void ejbCreate() throws RemoteException, CreateException {
	}

	public void ejbRemove() {
	} 

	public void ejbActivate() {
	}

	public void ejbPassivate() {
	}*/
	CommonConstants commonConstants = new CommonConstants();
	@Override
	public String actionHandler() throws RemoteException, ITMException {
		return "";
	}

	@Override
	public String actionHandler(String actionType, String xmlString,
			String xmlString1, String objContext, String xtraParams)
					throws RemoteException, ITMException {
		Document dom = null;
		Document dom1 = null;

		String retString = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		try {
			if (xmlString != null && xmlString.trim().length() != 0) {
				System.out.println("XML String :" + xmlString);
				dom = genericUtility.parseString(xmlString);
			}
			if (xmlString1 != null && xmlString1.trim().length() != 0) {
				System.out.println("XML String1 :" + xmlString1);
				dom1 = genericUtility.parseString(xmlString1);
			}

			System.out.println("actionType:" + actionType + ":");

			if (actionType.equalsIgnoreCase("All Items")) {
				retString = actionAllItems(dom, dom1, xtraParams);
			}
			if (actionType.equalsIgnoreCase("Stock")) {
				retString = actionStock(dom, dom1, xtraParams);
			}
			if (actionType.equalsIgnoreCase("Allocate")) {
				retString = actionAllocate(dom, dom1, xtraParams);
			}
		} catch (Exception e) {
			System.out
			.println("Exception :WOrderIssueActEJB :actionHandler(String xmlString):"
					+ e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		System.out
		.println("returning String from WOrderIssueActEJB : actionHandler"
				+ retString);
		return retString;
	}

	@Override
	public String actionHandlerTransform(String actionType, String xmlString,
			String xmlString1, String objContext, String xtraParams,
			String selDataStr) throws RemoteException, ITMException {
		System.out.println("actionHandlerTransform is calling.............");
		Document dom = null;
		Document dom1 = null;
		Document selDataDom = null;

		String retString = null;
		try {
			if (xmlString != null && xmlString.trim().length() != 0) {
				dom = GenericUtility.getInstance().parseString(xmlString);
			}
			if (xmlString1 != null && xmlString1.trim().length() != 0) {
				dom1 = GenericUtility.getInstance().parseString(xmlString1);
			}
			if (selDataStr != null && selDataStr.length() > 0) {
				selDataDom = GenericUtility.getInstance().parseString(
						selDataStr);
			}

			System.out.println("actionType:" + actionType + ":");

			if (actionType.equalsIgnoreCase("Stock")) {
				retString = stockTransform(dom, dom1, objContext, xtraParams,
						selDataDom);
			}
		} catch (Exception e) {
			System.out
			.println("Exception :WOrderIssueActEJB :actionHandlerTransform(String xmlString):"
					+ e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		System.out
		.println("returning String from WOrderIssueActEJB : actionHandlerTransform"
				+ retString);
		return retString;
	}

	private String actionAllItems(Document dom, Document dom1, String xtraParams)
			throws RemoteException, ITMException {
		String tranType = null, tranIdReq = null, expLev = null, itemCode = null; 
		double qtyReq = 0, totalQtyIss = 0;

		String sql = "";
		PreparedStatement pstmt = null, pstmt1 = null;
		ResultSet rs = null, rs1 = null;
		Connection conn = null;

		StringBuffer valueXmlString = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root>\r\n");
		String allocateStr = "";
		String detailCnt = ""; 
		int detCnt = 0;
		String errCode = "", errString = "";  //ADDED BY ALKA 19/07/07 TO DISPLAY MESSAGE THAT THE ALL ITEMS BUTTON WORKS ON INCASE OF "D" OR "O"
		ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();//ADDED BY ALKA 19/07/07 TO DISPLAY MESSAGE THAT THE ALL ITEMS BUTTON WORKS ON INCASE OF "D" OR "O"

		GenericUtility genericUtility = GenericUtility.getInstance();
		detailCnt = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "detCnt");
		if (detailCnt != null) {
			detCnt = Integer.parseInt(detailCnt);
			System.out.println("detCnt...........:: " + detCnt);
		}
		if (dom == null || detCnt > 1) 
		{
			valueXmlString.append("</Root>\r\n");
			return valueXmlString.toString();
		}

		ConnDriver connDriver = new ConnDriver();
		try 
		{
			conn = connDriver.getConnectDB("DriverITM");
			tranType = genericUtility.getColumnValue("tran_type", dom1);
			tranIdReq = genericUtility.getColumnValue("tran_id__req", dom1);
			//			System.out.println("tranType before null :::: " + tranType + "tranIdReq :::: " + tranIdReq); //COMMENTED BY ALKA 19/07/07 AS NOT REQUIRED
			if (tranIdReq == null) tranIdReq = "";
			if (tranType == null) tranType = "";

			System.out.println("tranType :::: " + tranType + "tranIdReq :::: " + tranIdReq);
			tranType = tranType.trim();

			if ((tranType.equalsIgnoreCase("D") || tranType.equalsIgnoreCase("O")) && tranIdReq.trim().length() > 0)
			{
				sql = "SELECT EXP_LEV, ITEM_CODE, QUANTITY FROM WORKORDER_ISSDET_REQ WHERE TRAN_ID = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,tranIdReq);
				rs = pstmt.executeQuery();
				while (rs.next())
				{
					expLev = rs.getString(1);
					itemCode = rs.getString(2);
					qtyReq = rs.getDouble(3);
					System.out.println("expLev :: " + expLev + " itemCode :: " + itemCode);
					System.out.println("qtyReq :: " + qtyReq);

					//					DEDUCT THE ISSUED QUANTITY FOR THIS REQ
					sql = "SELECT SUM(B.QUANTITY) FROM WORKORDER_ISS A, WORKORDER_ISSDET B " 
							+ " WHERE (A.TRAN_ID = B.TRAN_ID) AND (A.CONFIRMED = 'Y' ) AND  (A.TRAN_TYPE = ?) " 
							+ " AND  (A.TRAN_ID__REQ = ?) AND (B.ITEM_CODE = ?) AND (B.EXP_LEV = ?)";

					pstmt1 = conn.prepareStatement(sql);
					pstmt1.setString(1, tranType);
					pstmt1.setString(2, tranIdReq);
					pstmt1.setString(3, itemCode);
					pstmt1.setString(4, expLev);
					System.out.println("sql ::: "+ sql);
					rs1 = pstmt1.executeQuery();

					if (rs1.next())
					{
						totalQtyIss = rs1.getDouble(1);
						System.out.println("totalQtyIss ::: " + totalQtyIss);
					}

					if ((qtyReq - totalQtyIss) > 0)
					{
						getItemChanged(dom, dom1, expLev, conn);
						allocateStr = actionAllocate(dom, dom1, xtraParams);
						System.out.println("allocateStr ::: " + allocateStr);
						if (allocateStr.indexOf("<Errors>") > 0) 
						{
							return allocateStr;
						}
						valueXmlString.append(allocateStr.substring(allocateStr.indexOf("<Detail>"), allocateStr.indexOf("</Root>")));
					}
				}
				pstmt.close();
				rs.close();
			}
			//			ADDED BY ALKA 19/07/07 TO DISPLAY MESSAGE THAT THE ALL ITEMS BUTTON WORKS ON INCASE OF "D" OR "O"
			else
			{
				errCode = "VTTRANTY";
				errString = itmDBAccessEJB.getErrorString(null, errCode, null, "", conn);
				System.out.println("errString :" + errString);
				return errString;
			}
			//			ADDITION ENDED BY ALKA 19/07/07 TO DISPLAY MESSAGE THAT THE ALL ITEMS BUTTON WORKS ON INCASE OF "D" OR "O"
			valueXmlString.append("</Root>\r\n");

		} catch (SQLException sqx) {
			System.out.println("The sqlException occure in WOrderIssueActEJB :" + sqx);
			throw new ITMException(sqx);
		} catch (Exception e) {
			System.out.println("The Exception occure in WOrderIssueActEJB :" + e);
			throw new ITMException(e);
		} finally {
			try {
				conn.close();
				conn = null;
			} catch (Exception e) {
			}
		}
		System.out.println("valueXmlString return from WOrderIssueActEJB :" + valueXmlString.toString());
		return valueXmlString.toString();
	}

	private String actionStock(Document dom, Document dom1, String xtraParams) throws RemoteException, ITMException 
	{
		/*		 
		 * ALLOWS THE USER TO SELECT STOCK FROM DIFF. LOC_CODE, LOT_NO, LOT_SL
		 GETS ALL STOCK FROM STOCK TABLE FOR FOLLOWING CONDITIONS:
		 	SITE_CODE = HEADER SITE_CODE ---- ITEM_CODE = ITEM CODE ENTERED
		 	INVSTAT.AVAILABLE = 'Y' ---	EXPIRY DATE IS NULL OR EXPIRY DATE IS > HEADER EFF_DATE 
		 	RETEST_DATE > HEADER EFF_DATE ---- 	QUANTITY - ALLOC_QTY > 0
		 IF QC_REQD = 'Y' THEN ONLY EXPIRY DATE AND	RETEST DATE TO BE CONSIDERED
		 */
		String siteCode = null, itemCode = null, dimension = null, bomCode = null, expLev = null;
		String errCode = null, errString = null, sql = null;
		String expDate1 = null, retestDate1 = null, retestDt = null, effDate = null, itemCodeMain = null;
		String workOrder = null, fgItem = null, trackShelfLife = null, useInvStatus = null;
		String qcReqd = null, autoQcReqd = null, itemCodeRef = null;
		double trimWidth = 0;
		int countItemCode = 0, operation = 0, cntQ = 0;
		Timestamp expDate = null, retestDate = null;
		boolean flag1 = false, flag2 = false;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Connection conn = null;
		String lotNo = "", lotSl = "", lotNoIssue = "", considerLotNoIssue = "N", locGroup = "", locUsageType = "";
		StringBuffer sqlSB = new StringBuffer();
		StringBuffer valueXmlString = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root>\r\n");

		if (dom == null) {
			valueXmlString.append("</Root>\r\n");
			return valueXmlString.toString();
		}

		GenericUtility genericUtility = GenericUtility.getInstance();
		ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
		ConnDriver connDriver = new ConnDriver();
		MfgCommon mfgCommon = new MfgCommon();
		try {
			conn = connDriver.getConnectDB("DriverITM");

			effDate = genericUtility.getColumnValue("eff_date", dom1);
			siteCode = genericUtility.getColumnValue("site_code", dom1);
			itemCode = genericUtility.getColumnValue("item_code", dom);
			workOrder = genericUtility.getColumnValue("work_order", dom1);
			expLev = genericUtility.getColumnValue("exp_lev", dom);
			operation = Integer.parseInt(genericUtility.getColumnValue("operation", dom1));
			bomCode = genericUtility.getColumnValue("bom_code", dom1);
			locGroup = genericUtility.getColumnValue("loc_group", dom1); // 26/03/12 manoharan
			// 18/02/12 manoharan
			sql = "select w.LOT_NO__ISSUE,b.CONSIDER_LOT_NO__ISSUE,b.LOC_USAGE_TYPE from workorder w, workorder_bill b "
					+ " where w.work_order = b.work_order "
					+ " and w.work_order = ? "
					+ " and b.exp_lev = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, workOrder);
			pstmt.setString(2, expLev);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				lotNoIssue = rs.getString("LOT_NO__ISSUE");
				considerLotNoIssue = rs.getString("CONSIDER_LOT_NO__ISSUE");
				locUsageType = rs.getString("LOC_USAGE_TYPE");
			}
			pstmt.close();
			rs.close();
			lotNoIssue = lotNoIssue==null?" ":lotNoIssue;
			considerLotNoIssue = considerLotNoIssue==null?" ":considerLotNoIssue;
			locUsageType = locUsageType==null?" ":locUsageType;
			
			
			if ("Y".equals(considerLotNoIssue) && lotNoIssue != null && !"null".equals(lotNoIssue) && lotNoIssue.trim().length() > 0)
			{
				String lotNoList[];
				String lotNoIssueTemp = "";

				lotNoList = lotNoIssue.split(",");
				lotNoIssue = "";
				for(int ctr = 0;ctr < lotNoList.length;ctr++)
				{
					lotNoIssueTemp = lotNoList[ctr];
					if (lotNoIssue.trim().length() > 0)
					{
						lotNoIssue	= lotNoIssue + ",'".concat(lotNoIssueTemp).concat("'");
					}
					else
					{
						lotNoIssue	= lotNoIssue + "'".concat(lotNoIssueTemp).concat("'");
					}

				}

			}

			// end 18/02/12 manoharan

			//			for qc_reqd to be checked from first siteitem & then item			
			qcReqd = mfgCommon.chkQcReqd(siteCode, itemCode, conn);
			autoQcReqd = mfgCommon.chkAutoQcReqd(siteCode, itemCode, conn);

			sql = "SELECT (CASE WHEN TRACK_SHELF_LIFE IS NULL THEN 'N' ELSE TRACK_SHELF_LIFE END) " 
					+ " FROM ITEM WHERE ITEM_CODE = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, itemCode);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				trackShelfLife = rs.getString(1);
			}
			pstmt.close();
			rs.close();

			sql = "SELECT ITEM_CODE, ITEM_CODE__REF FROM WORKORDER_BILL WHERE WORK_ORDER = ? AND OPERATION  = ? AND EXP_LEV = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, workOrder);
			pstmt.setInt(2, operation);
			pstmt.setString(3, expLev);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				itemCodeMain = rs.getString(1);
				itemCodeRef = rs.getString(2);
			}
			pstmt.close();
			rs.close();

			sql = "SELECT DIMENSION,USE_INVSTATUS FROM BOMDET WHERE BOM_CODE = ? AND ITEM_CODE = ? AND ITEM_REF  = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, bomCode);
			pstmt.setString(2, itemCodeMain);
			pstmt.setString(3, itemCodeRef);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				dimension = rs.getString(1)==null?"":rs.getString(1);
				useInvStatus = rs.getString(2)==null?"U":rs.getString(2);
			}
			else
			{
				dimension = "";
				useInvStatus = "U";
			}
			pstmt.close();
			rs.close();
			pstmt = null;
			sql = "";
				useInvStatus = useInvStatus==null?" ":useInvStatus;
				dimension = dimension==null?" ":dimension;
				trackShelfLife = trackShelfLife==null?" ":trackShelfLife;
				autoQcReqd = autoQcReqd==null?" ":autoQcReqd;
				//trimWidth = trimWidth==null?" ":trimWidth;
				qcReqd = qcReqd==null?" ":qcReqd;

				System.out.println("dimension :: " + dimension + " trimWidth :: " + trimWidth);
			System.out.println("qcRecd :: " + qcReqd + " autoQcReqd :: " + autoQcReqd);
			System.out.println("trackShelfLife :: " + trackShelfLife + " useInvStatus :: " + useInvStatus);
			if (dimension.trim().length() == 0)
			{
				//				 check whether any valid record in stock exist for the item
				//				TO CHECK USE_INVSTATUS FROM BOMDET -- IF IT IS B THEN CHECK STAT_TYPE FROM INVSTAT
				//				to check all condition for auto_reqc 
				//				Added Location code join to invstat
				if (qcReqd.equalsIgnoreCase("Y") && autoQcReqd.equalsIgnoreCase("Y") && trackShelfLife.equalsIgnoreCase("Y") && useInvStatus.equalsIgnoreCase("U"))
				{
					sql = " SELECT COUNT(A.ITEM_CODE) FROM   STOCK A, LOCATION B, INVSTAT  C "
							+ " WHERE  A.LOC_CODE  = B.LOC_CODE AND	 B.INV_STAT  = C.INV_STAT AND "
							+ " A.ITEM_CODE = ? AND A.SITE_CODE = ? AND C.AVAILABLE = 'Y' "
							+ " AND ((CASE WHEN A.QUANTITY IS NULL THEN 0 ELSE A.QUANTITY END) - "
							+ " (CASE WHEN A.ALLOC_QTY IS NULL THEN 0 ELSE A.ALLOC_QTY END)) > 0 "
							+ " AND ( A.EXP_DATE > ? OR A.EXP_DATE IS NULL) AND A.RETEST_DATE > ?";
					// 18/02/12 manoharan
					if ("Y".equals(considerLotNoIssue) && lotNoIssue != null && !"null".equals(lotNoIssue) && lotNoIssue.trim().length() > 0)
					{
						sql = sql + " AND A.LOT_NO IN (" + lotNoIssue + ") ";
					}
					// end 18/02/12 manoharan
					// 26/03/12 manoharan
					if (locGroup != null && locGroup.trim().length() > 0 && "A".equals(locUsageType) )
					{
						sql = sql + " AND b.loc_group = '" + locGroup + "' ";
					}
					// end 26/03/12 manoharan
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, itemCode);
					pstmt.setString(2, siteCode);
					pstmt.setTimestamp(3, Timestamp.valueOf(genericUtility.getValidDateString(effDate, genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat()) + " 00:00:00"));
					pstmt.setTimestamp(4, Timestamp.valueOf(genericUtility.getValidDateString(effDate, genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat()) + " 00:00:00"));
				}
				else if (qcReqd.equalsIgnoreCase("Y") && autoQcReqd.equalsIgnoreCase("N") && trackShelfLife.equalsIgnoreCase("Y") && useInvStatus.equalsIgnoreCase("U"))
				{
					sql = "SELECT COUNT(A.ITEM_CODE) FROM STOCK A, LOCATION B, INVSTAT C "
							+ " WHERE A.LOC_CODE = B.LOC_CODE	AND	 B.INV_STAT  = C.INV_STAT " 
							+ " AND A.ITEM_CODE = ? AND A.SITE_CODE = ? AND C.AVAILABLE = 'Y' " 
							+ " AND ((CASE WHEN A.QUANTITY IS NULL THEN 0 ELSE A.QUANTITY END) - "
							+ " (CASE WHEN A.ALLOC_QTY IS NULL THEN 0 ELSE A.ALLOC_QTY END)) > 0 "
							+ " AND (A.EXP_DATE > ? OR A.EXP_DATE IS NULL)";
					// 18/02/12 manoharan
					if ("Y".equals(considerLotNoIssue) && lotNoIssue != null && !"null".equals(lotNoIssue) && lotNoIssue.trim().length() > 0)
					{
						sql = sql + " AND A.LOT_NO IN (" + lotNoIssue + ") ";
					}
					// end 18/02/12 manoharan
					// 26/03/12 manoharan
					if (locGroup != null && locGroup.trim().length() > 0 && "A".equals(locUsageType) )
					{
						sql = sql + " AND b.loc_group = '" + locGroup + "' ";
					}
					// end 26/03/12 manoharan
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, itemCode);
					pstmt.setString(2, siteCode);
					pstmt.setTimestamp(3, Timestamp.valueOf(genericUtility.getValidDateString(effDate, genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat()) + " 00:00:00"));
				}
				else if (qcReqd.equalsIgnoreCase("N") && trackShelfLife.equalsIgnoreCase("Y") && useInvStatus.equalsIgnoreCase("U"))
				{
					sql = "SELECT COUNT(A.ITEM_CODE) FROM STOCK A, LOCATION B, INVSTAT C " 
							+ "WHERE A.LOC_CODE = B.LOC_CODE	AND	 B.INV_STAT  = C.INV_STAT AND A.ITEM_CODE = ? " 
							+ "AND A.SITE_CODE = ? AND C.AVAILABLE = 'Y' " 
							+ "AND ((CASE WHEN A.QUANTITY IS NULL THEN 0 ELSE A.QUANTITY END) - "
							+ "(CASE WHEN A.ALLOC_QTY IS NULL THEN 0 ELSE A.ALLOC_QTY END)) > 0 "
							+ "AND (A.EXP_DATE > ? OR A.EXP_DATE IS NULL) ";
					// 18/02/12 manoharan
					if ("Y".equals(considerLotNoIssue) && lotNoIssue != null && !"null".equals(lotNoIssue) && lotNoIssue.trim().length() > 0)
					{
						sql = sql + " AND A.LOT_NO IN (" + lotNoIssue + ") ";
					}
					// end 18/02/12 manoharan
					// 26/03/12 manoharan
					if (locGroup != null && locGroup.trim().length() > 0 && "A".equals(locUsageType) )
					{
						sql = sql + " AND b.loc_group = '" + locGroup + "' ";
					}
					// end 26/03/12 manoharan
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, itemCode);
					pstmt.setString(2, siteCode);
					pstmt.setTimestamp(3, Timestamp.valueOf(genericUtility.getValidDateString(effDate, genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat()) + " 00:00:00"));
				}
				else if (qcReqd.equalsIgnoreCase("Y") && autoQcReqd.equalsIgnoreCase("Y") && trackShelfLife.equalsIgnoreCase("N") && useInvStatus.equalsIgnoreCase("U"))
				{
					sql = " SELECT COUNT(A.ITEM_CODE) FROM STOCK A, LOCATION B, INVSTAT  C " 
							+ " WHERE A.LOC_CODE  = B.LOC_CODE AND	 B.INV_STAT  = C.INV_STAT AND A.ITEM_CODE = ? "
							+ " AND A.SITE_CODE = ? AND C.AVAILABLE = 'Y' " 
							+ " AND ((CASE WHEN A.QUANTITY IS NULL THEN 0 ELSE A.QUANTITY END) - "
							+ " (CASE WHEN A.ALLOC_QTY IS NULL THEN 0 ELSE A.ALLOC_QTY END)) > 0 "
							+ " AND A.RETEST_DATE > ? "; 
					// 18/02/12 manoharan
					if ("Y".equals(considerLotNoIssue) && lotNoIssue != null && !"null".equals(lotNoIssue) && lotNoIssue.trim().length() > 0)
					{
						sql = sql + " AND A.LOT_NO IN (" + lotNoIssue + ") ";
					}
					// end 18/02/12 manoharan
					// 26/03/12 manoharan
					if (locGroup != null && locGroup.trim().length() > 0 && "A".equals(locUsageType) )
					{
						sql = sql + " AND b.loc_group = '" + locGroup + "' ";
					}
					// end 26/03/12 manoharan
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, itemCode);
					pstmt.setString(2, siteCode);
					pstmt.setTimestamp(3, Timestamp.valueOf(genericUtility.getValidDateString(effDate, genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat()) + " 00:00:00"));
				}
				else if (qcReqd.equalsIgnoreCase("Y") && autoQcReqd.equalsIgnoreCase("N") && trackShelfLife.equalsIgnoreCase("N") && useInvStatus.equalsIgnoreCase("U"))
				{
					sql = " SELECT COUNT(A.ITEM_CODE) FROM STOCK A, LOCATION B, INVSTAT C " 
							+ " WHERE  A.LOC_CODE  = B.LOC_CODE AND	 B.INV_STAT  = C.INV_STAT AND A.ITEM_CODE = ? " 
							+ " AND A.SITE_CODE = ? AND C.AVAILABLE = 'Y' "
							+ " AND ((CASE WHEN A.QUANTITY IS NULL THEN 0 ELSE A.QUANTITY END) - "
							+ " (CASE WHEN A.ALLOC_QTY IS NULL THEN 0 ELSE A.ALLOC_QTY END)) > 0 ";
					// 18/02/12 manoharan
					if ("Y".equals(considerLotNoIssue) && lotNoIssue != null && !"null".equals(lotNoIssue) && lotNoIssue.trim().length() > 0)
					{
						sql = sql + " AND A.LOT_NO IN (" + lotNoIssue + ") ";
					}
					// end 18/02/12 manoharan
					// 26/03/12 manoharan
					if (locGroup != null && locGroup.trim().length() > 0 && "A".equals(locUsageType) )
					{
						sql = sql + " AND b.loc_group = '" + locGroup + "' ";
					}
					// end 26/03/12 manoharan
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, itemCode);
					pstmt.setString(2, siteCode);
				}
				else if (qcReqd.equalsIgnoreCase("N") && trackShelfLife.equalsIgnoreCase("N") && useInvStatus.equalsIgnoreCase("U"))
				{
					sql = " SELECT COUNT(A.ITEM_CODE) FROM STOCK A, LOCATION B, INVSTAT C " 
							+ " WHERE  A.LOC_CODE  = B.LOC_CODE AND	 B.INV_STAT  = C.INV_STAT AND A.ITEM_CODE = ? " 
							+ " AND A.SITE_CODE = ? AND C.AVAILABLE = 'Y' "
							+ " AND ((CASE WHEN A.QUANTITY IS NULL THEN 0 ELSE A.QUANTITY END) - "
							+ " (CASE WHEN A.ALLOC_QTY IS NULL THEN 0 ELSE A.ALLOC_QTY END)) > 0 ";
					// 18/02/12 manoharan
					if ("Y".equals(considerLotNoIssue) && lotNoIssue != null && !"null".equals(lotNoIssue) && lotNoIssue.trim().length() > 0)
					{
						sql = sql + " AND A.LOT_NO IN (" + lotNoIssue + ") ";
					}
					// end 18/02/12 manoharan
					// 26/03/12 manoharan
					if (locGroup != null && locGroup.trim().length() > 0 && "A".equals(locUsageType) )
					{
						sql = sql + " AND b.loc_group = '" + locGroup + "' ";
					}
					// end 26/03/12 manoharan
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, itemCode);
					pstmt.setString(2, siteCode);
				}
				else if (qcReqd.equalsIgnoreCase("N") && trackShelfLife.equalsIgnoreCase("N") && useInvStatus.equalsIgnoreCase("B"))
				{
					sql = " SELECT COUNT(A.ITEM_CODE) FROM STOCK A, LOCATION B, INVSTAT C " 
							+ " WHERE  A.LOC_CODE  = B.LOC_CODE AND	 B.INV_STAT  = C.INV_STAT AND A.ITEM_CODE = ? " 
							+ " AND A.SITE_CODE = ? AND C.STAT_TYPE <> 'S' " 
							+ " AND ((CASE WHEN A.QUANTITY IS NULL THEN 0 ELSE A.QUANTITY END) - "
							+ " (CASE WHEN A.ALLOC_QTY IS NULL THEN 0 ELSE A.ALLOC_QTY END)) > 0 "
							+ " AND DDF_COMPARE_DIMENSION(CASE WHEN A.DIMENSION IS NULL THEN ' ' ELSE A.DIMENSION END, ?, ?) = 'Y'";
					// 18/02/12 manoharan
					if ("Y".equals(considerLotNoIssue) && lotNoIssue != null && !"null".equals(lotNoIssue) && lotNoIssue.trim().length() > 0)
					{
						sql = sql + " AND A.LOT_NO IN (" + lotNoIssue + ") ";
					}
					// end 18/02/12 manoharan
					// 26/03/12 manoharan
					if (locGroup != null && locGroup.trim().length() > 0 && "A".equals(locUsageType) )
					{
						sql = sql + " AND b.loc_group = '" + locGroup + "' ";
					}
					// end 26/03/12 manoharan
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, itemCode);
					pstmt.setString(2, siteCode);
					pstmt.setString(3, dimension);
					pstmt.setDouble(4, trimWidth);
				}
				else if (trackShelfLife.equalsIgnoreCase("N") && useInvStatus.equalsIgnoreCase("B"))
				{
					sql = " SELECT COUNT(A.ITEM_CODE) FROM STOCK A, LOCATION B, INVSTAT C " 
							+ " WHERE  A.LOC_CODE  = B.LOC_CODE AND	 B.INV_STAT  = C.INV_STAT AND A.ITEM_CODE = ? " 
							+ " AND A.SITE_CODE = ? AND C.STAT_TYPE <> 'S' "
							+ " AND ((CASE WHEN A.QUANTITY IS NULL THEN 0 ELSE A.QUANTITY END) - "
							+ " (CASE WHEN A.ALLOC_QTY IS NULL THEN 0 ELSE A.ALLOC_QTY END)) > 0 ";
					// 18/02/12 manoharan
					if ("Y".equals(considerLotNoIssue) && lotNoIssue != null && !"null".equals(lotNoIssue) && lotNoIssue.trim().length() > 0)
					{
						sql = sql + " AND A.LOT_NO IN (" + lotNoIssue + ") ";
					}
					// end 18/02/12 manoharan
					// 26/03/12 manoharan
					if (locGroup != null && locGroup.trim().length() > 0 && "A".equals(locUsageType) )
					{
						sql = sql + " AND b.loc_group = '" + locGroup + "' ";
					}
					// end 26/03/12 manoharan
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, itemCode);
					pstmt.setString(2, siteCode);
				}
				else if (trackShelfLife.equalsIgnoreCase("Y") && useInvStatus.equalsIgnoreCase("B"))
				{
					sql = " SELECT COUNT(A.ITEM_CODE) FROM STOCK A, LOCATION B, INVSTAT C " 
							+ " WHERE  A.LOC_CODE  = B.LOC_CODE AND	 B.INV_STAT  = C.INV_STAT AND A.ITEM_CODE = ? " 
							+ " AND A.SITE_CODE = ? AND C.STAT_TYPE <> 'S' " 
							+ " AND ((CASE WHEN A.QUANTITY IS NULL THEN 0 ELSE A.QUANTITY END) - "
							+ " (CASE WHEN A.ALLOC_QTY IS NULL THEN 0 ELSE A.ALLOC_QTY END)) > 0 "
							+ " AND (A.EXP_DATE > ? OR A.EXP_DATE IS NULL) ";
					// 18/02/12 manoharan
					if ("Y".equals(considerLotNoIssue) && lotNoIssue != null && !"null".equals(lotNoIssue) && lotNoIssue.trim().length() > 0)
					{
						sql = sql + " AND A.LOT_NO IN (" + lotNoIssue + ") ";
					}
					// end 18/02/12 manoharan
					// 26/03/12 manoharan
					if (locGroup != null && locGroup.trim().length() > 0 && "A".equals(locUsageType) )
					{
						sql = sql + " AND b.loc_group = '" + locGroup + "' ";
					}
					// end 26/03/12 manoharan
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, itemCode);
					pstmt.setString(2, siteCode);
					pstmt.setTimestamp(3, Timestamp.valueOf(genericUtility.getValidDateString(effDate, genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat()) + " 00:00:00"));
				}
			}
			else
			{
				fgItem = genericUtility.getColumnValue("item_code", dom1);

				sql = "SELECT A.TRIM_WIDTH FROM ITEM_TYPE A, ITEM B WHERE  A.ITEM_TYPE = B.ITEM_TYPE AND B.ITEM_CODE = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, fgItem);
				rs = pstmt.executeQuery();
				if (rs.next())
				{
					trimWidth = rs.getDouble(1);
				}
				pstmt.close();
				rs.close();
				System.out.println("dimension :: " + dimension + " trimWidth :: " + trimWidth);
				//				TO CHECK USE_INVSTATUS FROM BOMDET
				//				IF IT IS B THEN CHECK STAT_TYPE FROM INVSTAT
				if (qcReqd.equalsIgnoreCase("Y") && autoQcReqd.equalsIgnoreCase("Y") && trackShelfLife.equalsIgnoreCase("Y") && useInvStatus.equalsIgnoreCase("U"))
				{
					sql = " SELECT COUNT(A.ITEM_CODE) FROM   STOCK A, LOCATION B, INVSTAT  C "
							+ " WHERE  A.LOC_CODE  = B.LOC_CODE AND	 B.INV_STAT  = C.INV_STAT AND "
							+ " A.ITEM_CODE = ? AND A.SITE_CODE = ? AND C.AVAILABLE = 'Y' "
							+ " AND ((CASE WHEN A.QUANTITY IS NULL THEN 0 ELSE A.QUANTITY END) - "
							+ " (CASE WHEN A.ALLOC_QTY IS NULL THEN 0 ELSE A.ALLOC_QTY END)) > 0 "
							+ " AND ( A.EXP_DATE > ? OR A.EXP_DATE IS NULL) AND A.RETEST_DATE > ?"
							+ " AND DDF_COMPARE_DIMENSION(CASE WHEN A.DIMENSION IS NULL THEN ' ' ELSE A.DIMENSION END, ?, ?) = 'Y'";
					// 18/02/12 manoharan
					if ("Y".equals(considerLotNoIssue) && lotNoIssue != null && !"null".equals(lotNoIssue) && lotNoIssue.trim().length() > 0)
					{
						sql = sql + " AND A.LOT_NO IN (" + lotNoIssue + ") ";
					}
					// end 18/02/12 manoharan
					// 26/03/12 manoharan
					if (locGroup != null && locGroup.trim().length() > 0 && "A".equals(locUsageType) )
					{
						sql = sql + " AND b.loc_group = '" + locGroup + "' ";
					}
					// end 26/03/12 manoharan
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, itemCode);
					pstmt.setString(2, siteCode);
					pstmt.setTimestamp(3, Timestamp.valueOf(genericUtility.getValidDateString(effDate, genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat()) + " 00:00:00"));
					pstmt.setTimestamp(4, Timestamp.valueOf(genericUtility.getValidDateString(effDate, genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat()) + " 00:00:00"));
					pstmt.setString(5, dimension);
					pstmt.setDouble(6, trimWidth);
				}
				else if (qcReqd.equalsIgnoreCase("Y") && autoQcReqd.equalsIgnoreCase("N") && trackShelfLife.equalsIgnoreCase("Y") && useInvStatus.equalsIgnoreCase("U"))
				{
					sql = " SELECT COUNT(A.ITEM_CODE) FROM STOCK A, LOCATION B, INVSTAT C "
							+ " WHERE A.LOC_CODE = B.LOC_CODE	AND	 B.INV_STAT  = C.INV_STAT " 
							+ " AND A.ITEM_CODE = ? AND A.SITE_CODE = ? AND C.AVAILABLE = 'Y' " 
							+ " AND ((CASE WHEN A.QUANTITY IS NULL THEN 0 ELSE A.QUANTITY END) - "
							+ " (CASE WHEN A.ALLOC_QTY IS NULL THEN 0 ELSE A.ALLOC_QTY END)) > 0 "
							+ " AND (A.EXP_DATE > ? OR A.EXP_DATE IS NULL)"
							+ " AND DDF_COMPARE_DIMENSION(CASE WHEN A.DIMENSION IS NULL THEN ' ' ELSE A.DIMENSION END, ?, ?) = 'Y'";

					// 18/02/12 manoharan
					if ("Y".equals(considerLotNoIssue) && lotNoIssue != null && !"null".equals(lotNoIssue) && lotNoIssue.trim().length() > 0)
					{
						sql = sql + " AND A.LOT_NO IN (" + lotNoIssue + ") ";
					}
					// end 18/02/12 manoharan
					// 26/03/12 manoharan
					if (locGroup != null && locGroup.trim().length() > 0 && "A".equals(locUsageType) )
					{
						sql = sql + " AND b.loc_group = '" + locGroup + "' ";
					}
					// end 26/03/12 manoharan
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, itemCode);
					pstmt.setString(2, siteCode);
					pstmt.setTimestamp(3, Timestamp.valueOf(genericUtility.getValidDateString(effDate, genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat()) + " 00:00:00"));
					pstmt.setString(4, dimension);
					pstmt.setDouble(5, trimWidth);
				}
				else if (qcReqd.equalsIgnoreCase("N") && trackShelfLife.equalsIgnoreCase("Y") && useInvStatus.equalsIgnoreCase("U"))
				{
					sql = " SELECT COUNT(A.ITEM_CODE) FROM STOCK A, LOCATION B, INVSTAT C " 
							+ " WHERE A.LOC_CODE = B.LOC_CODE	AND	 B.INV_STAT  = C.INV_STAT AND A.ITEM_CODE = ? " 
							+ " AND A.SITE_CODE = ? AND C.AVAILABLE = 'Y' " 
							+ " AND ((CASE WHEN A.QUANTITY IS NULL THEN 0 ELSE A.QUANTITY END) - "
							+ " (CASE WHEN A.ALLOC_QTY IS NULL THEN 0 ELSE A.ALLOC_QTY END)) > 0 "
							+ " AND (A.EXP_DATE > ? OR A.EXP_DATE IS NULL) "
							+ " AND DDF_COMPARE_DIMENSION(CASE WHEN A.DIMENSION IS NULL THEN ' ' ELSE A.DIMENSION END, ?, ?) = 'Y'";
					// 18/02/12 manoharan
					if ("Y".equals(considerLotNoIssue) && lotNoIssue != null && !"null".equals(lotNoIssue) && lotNoIssue.trim().length() > 0)
					{
						sql = sql + " AND A.LOT_NO IN (" + lotNoIssue + ") ";
					}
					// end 18/02/12 manoharan
					// 26/03/12 manoharan
					if (locGroup != null && locGroup.trim().length() > 0 && "A".equals(locUsageType) )
					{
						sql = sql + " AND b.loc_group = '" + locGroup + "' ";
					}
					// end 26/03/12 manoharan
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, itemCode);
					pstmt.setString(2, siteCode);
					pstmt.setTimestamp(3, Timestamp.valueOf(genericUtility.getValidDateString(effDate, genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat()) + " 00:00:00"));
					pstmt.setString(4, dimension);
					pstmt.setDouble(5, trimWidth);
				}
				else if (qcReqd.equalsIgnoreCase("Y") && autoQcReqd.equalsIgnoreCase("Y") && trackShelfLife.equalsIgnoreCase("N") && useInvStatus.equalsIgnoreCase("U"))
				{
					sql = " SELECT COUNT(A.ITEM_CODE) FROM STOCK A, LOCATION B, INVSTAT  C " 
							+ " WHERE  A.LOC_CODE  = B.LOC_CODE AND	 B.INV_STAT  = C.INV_STAT AND A.ITEM_CODE = ? "
							+ " AND A.SITE_CODE = ? AND C.AVAILABLE = 'Y' " 
							+ " AND ((CASE WHEN A.QUANTITY IS NULL THEN 0 ELSE A.QUANTITY END) - "
							+ " (CASE WHEN A.ALLOC_QTY IS NULL THEN 0 ELSE A.ALLOC_QTY END)) > 0 "
							+ " AND A.RETEST_DATE > ? " 
							+ " AND DDF_COMPARE_DIMENSION(CASE WHEN A.DIMENSION IS NULL THEN ' ' ELSE A.DIMENSION END, ?, ?) = 'Y'";
					// 18/02/12 manoharan
					if ("Y".equals(considerLotNoIssue) && lotNoIssue != null && !"null".equals(lotNoIssue) && lotNoIssue.trim().length() > 0)
					{
						sql = sql + " AND A.LOT_NO IN (" + lotNoIssue + ") ";
					}
					// end 18/02/12 manoharan
					// 26/03/12 manoharan
					if (locGroup != null && locGroup.trim().length() > 0 && "A".equals(locUsageType) )
					{
						sql = sql + " AND b.loc_group = '" + locGroup + "' ";
					}
					// end 26/03/12 manoharan
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, itemCode);
					pstmt.setString(2, siteCode);
					pstmt.setTimestamp(3, Timestamp.valueOf(genericUtility.getValidDateString(effDate, genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat()) + " 00:00:00"));
					pstmt.setString(4, dimension);
					pstmt.setDouble(5, trimWidth);
				}
				else if (qcReqd.equalsIgnoreCase("Y") && autoQcReqd.equalsIgnoreCase("N") && trackShelfLife.equalsIgnoreCase("N") && useInvStatus.equalsIgnoreCase("U"))
				{
					sql = " SELECT COUNT(A.ITEM_CODE) FROM STOCK A, LOCATION B, INVSTAT C " 
							+ " WHERE  A.LOC_CODE  = B.LOC_CODE AND	 B.INV_STAT  = C.INV_STAT AND A.ITEM_CODE = ? " 
							+ " AND A.SITE_CODE = ? AND C.AVAILABLE = 'Y' "
							+ " AND ((CASE WHEN A.QUANTITY IS NULL THEN 0 ELSE A.QUANTITY END) - "
							+ " (CASE WHEN A.ALLOC_QTY IS NULL THEN 0 ELSE A.ALLOC_QTY END)) > 0 "
							+ " AND DDF_COMPARE_DIMENSION(CASE WHEN A.DIMENSION IS NULL THEN ' ' ELSE A.DIMENSION END, ?, ?) = 'Y'";
					// 18/02/12 manoharan
					if ("Y".equals(considerLotNoIssue) && lotNoIssue != null && !"null".equals(lotNoIssue) && lotNoIssue.trim().length() > 0)
					{
						sql = sql + " AND A.LOT_NO IN (" + lotNoIssue + ") ";
					}
					// end 18/02/12 manoharan
					// 26/03/12 manoharan
					if (locGroup != null && locGroup.trim().length() > 0 && "A".equals(locUsageType) )
					{
						sql = sql + " AND b.loc_group = '" + locGroup + "' ";
					}
					// end 26/03/12 manoharan
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, itemCode);
					pstmt.setString(2, siteCode);
					pstmt.setString(3, dimension);
					pstmt.setDouble(4, trimWidth);
				}
				else if (qcReqd.equalsIgnoreCase("N") && trackShelfLife.equalsIgnoreCase("N") && useInvStatus.equalsIgnoreCase("U"))
				{
					sql = " SELECT COUNT(A.ITEM_CODE) FROM STOCK A, LOCATION B, INVSTAT C " 
							+ " WHERE  A.LOC_CODE  = B.LOC_CODE AND	 B.INV_STAT  = C.INV_STAT AND A.ITEM_CODE = ? " 
							+ " AND A.SITE_CODE = ? AND C.AVAILABLE = 'Y' "
							+ " AND ((CASE WHEN A.QUANTITY IS NULL THEN 0 ELSE A.QUANTITY END) - "
							+ " (CASE WHEN A.ALLOC_QTY IS NULL THEN 0 ELSE A.ALLOC_QTY END)) > 0 "
							+ " AND DDF_COMPARE_DIMENSION(CASE WHEN A.DIMENSION IS NULL THEN ' ' ELSE A.DIMENSION END, ?, ?) = 'Y'";
					// 18/02/12 manoharan
					if ("Y".equals(considerLotNoIssue) && lotNoIssue != null && !"null".equals(lotNoIssue) && lotNoIssue.trim().length() > 0)
					{
						sql = sql + " AND A.LOT_NO IN (" + lotNoIssue + ") ";
					}
					// end 18/02/12 manoharan
					// 26/03/12 manoharan
					if (locGroup != null && locGroup.trim().length() > 0 && "A".equals(locUsageType) )
					{
						sql = sql + " AND b.loc_group = '" + locGroup + "' ";
					}
					// end 26/03/12 manoharan
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, itemCode);
					pstmt.setString(2, siteCode);
					pstmt.setString(3, dimension);
					pstmt.setDouble(4, trimWidth);
				}
				else if (qcReqd.equalsIgnoreCase("N") && trackShelfLife.equalsIgnoreCase("N") && useInvStatus.equalsIgnoreCase("B"))
				{
					sql = " SELECT COUNT(A.ITEM_CODE) FROM STOCK A, LOCATION B, INVSTAT C " 
							+ " WHERE  A.LOC_CODE  = B.LOC_CODE AND	 B.INV_STAT  = C.INV_STAT AND A.ITEM_CODE = ? " 
							+ " AND A.SITE_CODE = ? AND C.STAT_TYPE <> 'S' " 
							+ " AND ((CASE WHEN A.QUANTITY IS NULL THEN 0 ELSE A.QUANTITY END) - "
							+ " (CASE WHEN A.ALLOC_QTY IS NULL THEN 0 ELSE A.ALLOC_QTY END)) > 0 "
							+ " AND DDF_COMPARE_DIMENSION(CASE WHEN A.DIMENSION IS NULL THEN ' ' ELSE A.DIMENSION END, ?, ?) = 'Y'";
					// 18/02/12 manoharan
					if ("Y".equals(considerLotNoIssue) && lotNoIssue != null && !"null".equals(lotNoIssue) && lotNoIssue.trim().length() > 0)
					{
						sql = sql + " AND A.LOT_NO IN (" + lotNoIssue + ") ";
					}
					// end 18/02/12 manoharan
					// 26/03/12 manoharan
					if (locGroup != null && locGroup.trim().length() > 0 && "A".equals(locUsageType) )
					{
						sql = sql + " AND b.loc_group = '" + locGroup + "' ";
					}
					// end 26/03/12 manoharan
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, itemCode);
					pstmt.setString(2, siteCode);
					pstmt.setString(3, dimension);
					pstmt.setDouble(4, trimWidth);
				}
				else if (trackShelfLife.equalsIgnoreCase("N") && useInvStatus.equalsIgnoreCase("B"))
				{
					sql = " SELECT COUNT(A.ITEM_CODE) FROM STOCK A, LOCATION B, INVSTAT C " 
							+ " WHERE  A.LOC_CODE  = B.LOC_CODE AND	 B.INV_STAT  = C.INV_STAT AND A.ITEM_CODE = ? " 
							+ " AND A.SITE_CODE = ? AND C.STAT_TYPE <> 'S' " 
							+ " AND ((CASE WHEN A.QUANTITY IS NULL THEN 0 ELSE A.QUANTITY END) - "
							+ " (CASE WHEN A.ALLOC_QTY IS NULL THEN 0 ELSE A.ALLOC_QTY END)) > 0 "
							+ " AND DDF_COMPARE_DIMENSION(CASE WHEN A.DIMENSION IS NULL THEN ' ' ELSE A.DIMENSION END, ?, ?) = 'Y'";
					// 18/02/12 manoharan
					if ("Y".equals(considerLotNoIssue) && lotNoIssue != null && !"null".equals(lotNoIssue) && lotNoIssue.trim().length() > 0)
					{
						sql = sql + " AND A.LOT_NO IN (" + lotNoIssue + ") ";
					}
					// end 18/02/12 manoharan
					// 26/03/12 manoharan
					if (locGroup != null && locGroup.trim().length() > 0 && "A".equals(locUsageType) )
					{
						sql = sql + " AND b.loc_group = '" + locGroup + "' ";
					}
					// end 26/03/12 manoharan
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, itemCode);
					pstmt.setString(2, siteCode);
					pstmt.setString(3, dimension);
					pstmt.setDouble(4, trimWidth);
				}
				else if (trackShelfLife.equalsIgnoreCase("Y") && useInvStatus.equalsIgnoreCase("B"))
				{
					sql = " SELECT COUNT(A.ITEM_CODE) FROM STOCK A, LOCATION B, INVSTAT C " 
							+ " WHERE  A.LOC_CODE  = B.LOC_CODE AND	 B.INV_STAT  = C.INV_STAT AND A.ITEM_CODE = ? " 
							+ " AND A.SITE_CODE = ? AND C.STAT_TYPE <> 'S' " 
							+ " AND ((CASE WHEN A.QUANTITY IS NULL THEN 0 ELSE A.QUANTITY END) - "
							+ " (CASE WHEN A.ALLOC_QTY IS NULL THEN 0 ELSE A.ALLOC_QTY END)) > 0 "
							+ " AND (A.EXP_DATE > ? OR A.EXP_DATE IS NULL) "
							+ " AND DDF_COMPARE_DIMENSION(CASE WHEN A.DIMENSION IS NULL THEN ' ' ELSE A.DIMENSION END, ?, ?) = 'Y'";
					// 18/02/12 manoharan
					if ("Y".equals(considerLotNoIssue) && lotNoIssue != null && !"null".equals(lotNoIssue) && lotNoIssue.trim().length() > 0)
					{
						sql = sql + " AND A.LOT_NO IN (" + lotNoIssue + ") ";
					}
					// end 18/02/12 manoharan
					// 26/03/12 manoharan
					if (locGroup != null && locGroup.trim().length() > 0 && "A".equals(locUsageType) )
					{
						sql = sql + " AND b.loc_group = '" + locGroup + "' ";
					}
					// end 26/03/12 manoharan
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, itemCode);
					pstmt.setString(2, siteCode);
					pstmt.setTimestamp(3, Timestamp.valueOf(genericUtility.getValidDateString(effDate, genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat()) + " 00:00:00"));
					pstmt.setString(4, dimension);
					pstmt.setDouble(5, trimWidth);
				}
			}

			System.out.println("SQL :" + sql);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				countItemCode = rs.getInt(1);
				System.out.println("countItemCode :" + countItemCode);
			}
			if (countItemCode == 0) {
				errCode = "VTSTOCK1";
				errString = itmDBAccessEJB.getErrorString(null, errCode, null, "", conn);
				System.out.println("errString :" + errString);
				return errString;
			}

			sql = "SELECT CASE WHEN TRACK_SHELF_LIFE IS NULL THEN 'N' ELSE TRACK_SHELF_LIFE END " 
					+ " FROM ITEM WHERE  ITEM_CODE = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, itemCode);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				trackShelfLife = rs.getString(1);
			}
			pstmt.close();
			rs.close();
			System.out.println("qcReqd ::: " + qcReqd + "trackShelfLife ::: " + trackShelfLife );
			System.out.println("useInvStatus ::: " + useInvStatus + "dimension ::: " + dimension );

			retestDt = effDate;

			if (qcReqd.equalsIgnoreCase("N") || autoQcReqd.equalsIgnoreCase("N"))
			{
				retestDt = genericUtility.getValidDateString("01/01/1910","dd/mm/yy", genericUtility.getApplDateFormat());
			}

			if (trackShelfLife.equalsIgnoreCase("N"))
			{
				effDate = genericUtility.getValidDateString("01/01/1910","dd/mm/yy",genericUtility.getApplDateFormat());
			}

			if (useInvStatus == null)
			{
				useInvStatus = "U";
			}

			if (useInvStatus == null || useInvStatus.trim().length()==0 || useInvStatus.equalsIgnoreCase("U"))
			{
				sqlSB.append(" SELECT A.ITEM_SER, A.ITEM_CODE, A.UNIT, A.LOC_CODE, A.LOT_NO, A.LOT_SL, "); 
				sqlSB.append(" A.QUANTITY - A.ALLOC_QTY AS QUANTITY, A.SITE_CODE, A.EXP_DATE, A.RETEST_DATE, ");
				sqlSB.append(" A.DIMENSION, A.RATE, A.CREA_DATE, "); 
				sqlSB.append(" CASE WHEN LENGTH(RTRIM(A.LOT_SL)) = 1 THEN '0' || RTRIM(A.LOT_SL) ELSE RTRIM(A.LOT_SL) END "); 
				sqlSB.append(" FROM STOCK A, INVSTAT B, LOCATION C WHERE ( A.LOC_CODE = C.LOC_CODE ) AND  ");
				sqlSB.append(" ( C.INV_STAT = B.INV_STAT ) AND (A.ITEM_CODE = ?) AND (A.SITE_CODE = ?) AND ");
				sqlSB.append(" (A.QUANTITY - A.ALLOC_QTY > 0) AND (B.AVAILABLE = 'Y') AND (B.STAT_TYPE <> 'S') ");

				if (qcReqd.equalsIgnoreCase("Y") && autoQcReqd.equalsIgnoreCase("Y"))
				{
					sqlSB.append("AND (A.RETEST_DATE IS NULL OR A.RETEST_DATE > ?)");
				}
			}
			else if (useInvStatus.equalsIgnoreCase("B"))
			{
				sqlSB.append(" SELECT A.ITEM_SER, A.ITEM_CODE, A.UNIT, A.LOC_CODE, A.LOT_NO, A.LOT_SL, ");
				sqlSB.append(" A.QUANTITY - A.ALLOC_QTY AS QUANTITY, A.SITE_CODE, A.EXP_DATE, A.RETEST_DATE, "); 
				sqlSB.append(" A.DIMENSION, A.RATE, A.CREA_DATE, ");
				sqlSB.append(" CASE WHEN LENGTH(RTRIM(A.LOT_SL)) = 1 THEN '0' || RTRIM(A.LOT_SL) ELSE RTRIM(A.LOT_SL) END ");
				sqlSB.append(" FROM STOCK A, INVSTAT B, LOCATION C WHERE (C.LOC_CODE = C.LOC_CODE) AND (C.INV_STAT = B.INV_STAT) AND ( A.ITEM_CODE = ?) "); 
				sqlSB.append(" AND (A.SITE_CODE = ? ) AND (A.QUANTITY - A.ALLOC_QTY > 0 ) AND ( B.STAT_TYPE <> 'S' ) "); 
				sqlSB.append(" AND  NOT EXISTS (SELECT 1  FROM QC_ORDER C WHERE 	A.SITE_CODE = C.SITE_CODE  "); 
				sqlSB.append(" AND A.ITEM_CODE = C.ITEM_CODE  AND	A.LOT_NO = C.LOT_NO AND A.LOC_CODE = C.LOC_CODE ");
				sqlSB.append(" AND CASE WHEN C.STATUS IS NULL THEN 'U' ELSE C.STATUS END) = 'U' " );
				sqlSB.append(" AND (A.LOT_SL = C.LOT_SL OR C.LOT_SL IS NULL))");
			}

			if (trackShelfLife.equalsIgnoreCase("Y"))
			{
				sqlSB.append(" AND (A.EXP_DATE IS NULL OR A.EXP_DATE > ?)");
			}

			if (dimension.trim().length() > 0)
			{
				sqlSB.append("AND DDF_COMPARE_DIMENSION(CASE WHEN A.DIMENSION IS NULL THEN ' ' ELSE A.DIMENSION END, ?, 0) = 'Y'");
			}

			sql = sqlSB.toString();
			// 18/02/12 manoharan
			if ("Y".equals(considerLotNoIssue) && lotNoIssue != null && !"null".equals(lotNoIssue) && lotNoIssue.trim().length() > 0)
			{
				sql = sql + " AND A.LOT_NO IN (" + lotNoIssue + ") ";
			}
			// end 18/02/12 manoharan
			// 26/03/12 manoharan
			if (locGroup != null && locGroup.trim().length() > 0 && "A".equals(locUsageType) )
			{
				sql = sql + " AND C.loc_group = '" + locGroup + "' ";
			}
			// end 26/03/12 manoharan
			// 02/04/12 manoharan sorting to be doen as per PB logic
			//changes done by cpandey additin condition for track shelf life  on 20/08/12
			if("N".equalsIgnoreCase(trackShelfLife))
			{
				sql = sql + " order by partial_used,decode('N','P',decode(rtrim(c.loc_group),rtrim(''),'A','B'),'0'), " ;
				System.out.println("sql If track self life is N  ["+sql+"]>>");
			}
			if("Y".equalsIgnoreCase(trackShelfLife))
			{
				sql = sql + " order by partial_used,decode('N','P',decode(rtrim(c.loc_group),rtrim(''),'A','B'),'0'), " ;
				System.out.println("sql sql If track self life is Y--["+sql+"]>>");
			}
			//end of changes on 20/08/12
			//commented by cpandey 
			//sql = sql + " order by partial_used, (case when '" + locUsageType + "' = 'P' then (case when rtrim(c.loc_group) = rtrim('" + locGroup + "') Then 'A' else 'B' end) else '0' end), " ;

			if ("db2".equalsIgnoreCase(CommonConstants.DB_NAME))
			{
				sql = sql + " nvl(a.exp_date,a.crea_date),a.crea_date,a.lot_no ";
				System.out.println("after sql db converted --["+sql+"]->.");
			}
			else
			{	

				sql = sql + " nvl(a.exp_date,a.crea_date),a.crea_date,a.lot_no, "
						+ " decode(length(decode( substr(rtrim(a.lot_sl), length(rtrim(a.lot_sl))) ," +
						" 'S',(substr(rtrim(a.lot_sl),1,length(rtrim(a.lot_sl)) - 1)) ,rtrim(a.lot_sl)) ), " 
						+ " 1, '00' || decode( substr(rtrim(a.lot_sl), length(rtrim(a.lot_sl))) , " +
						"'S',(substr(rtrim(a.lot_sl),1,length(rtrim(a.lot_sl)) )) ,rtrim(a.lot_sl)), "        
						+" decode(length(decode( substr(rtrim(a.lot_sl), length(rtrim(a.lot_sl))) , 'S',(substr(rtrim(a.lot_sl),1,length(rtrim(a.lot_sl)) - 1)) ,rtrim(a.lot_sl)) ), "
						+ " 2, '0' || decode( substr(rtrim(a.lot_sl), length(rtrim(a.lot_sl))) , 'S',(substr(rtrim(a.lot_sl),1,length(rtrim(a.lot_sl)) )) ,rtrim(a.lot_sl)), "         
						+" decode( substr(rtrim(a.lot_sl), length(rtrim(a.lot_sl))) ," +
						" 'S',(substr(rtrim(a.lot_sl),1,length(rtrim(a.lot_sl)) - 1)) ,rtrim(a.lot_sl)) )) ";
			}  
			// end 02/04/12 manoharan


			System.out.println("sqlSB converted ::: "+ sql);
			StringTokenizer st = new StringTokenizer(sql, "?");
			cntQ = st.countTokens();
			pstmt = conn.prepareStatement(sql);
			flag1 = false; flag2 = false;
			pstmt.setString(1, itemCode);
			pstmt.setString(2, siteCode);
			System.out.println("itemCode :: " + itemCode + " siteCode :: " + siteCode);

			for (int i=3; i<cntQ; i++)
			{
				if ((useInvStatus == null || useInvStatus.trim().length()==0 || useInvStatus.equalsIgnoreCase("U")) && flag1 == false)
				{
					if (qcReqd.equalsIgnoreCase("Y") && autoQcReqd.equalsIgnoreCase("Y"))
					{
						pstmt.setTimestamp(i, Timestamp.valueOf(genericUtility.getValidDateString(retestDt, genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat())+ " 00:00:00"));
						System.out.println ("retestDate set");
						flag1 = true;
						continue;
					}
				}

				if (trackShelfLife.equalsIgnoreCase("Y") && flag2 == false)
				{
					pstmt.setTimestamp(i, Timestamp.valueOf(genericUtility.getValidDateString(effDate, genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat()) + " 00:00:00"));
					System.out.println ("effDate set");
					flag2 = true;
					continue;

				}
				if (dimension.trim().length() > 0)
				{
					pstmt.setString(i, dimension);
					pstmt.setDouble(i+1, trimWidth);
					break;
				}
			}
			rs = pstmt.executeQuery();
			SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
			while (rs.next())
			{
				expDate = rs.getTimestamp(9);
				if (expDate != null)
				{
					expDate1 = sdf.format(expDate);
					System.out.println("expDate1 :"+expDate1);
				}
				retestDate = rs.getTimestamp(10);
				if (retestDate != null)
				{
					retestDate1 = sdf.format(retestDate); 
					System.out.println("retestDate1 :"+retestDate1);
				}

				valueXmlString.append("<Detail>\r\n");
				valueXmlString.append("<item_ser>").append("<![CDATA[").append(rs.getString(1).trim()).append("]]>").append("</item_ser>\r\n");			
				valueXmlString.append("<item_code>").append("<![CDATA[").append(rs.getString(2).trim()).append("]]>").append("</item_code>\r\n");			
				valueXmlString.append("<unit>").append("<![CDATA[").append(rs.getString(3)).append("]]>").append("</unit>\r\n");			
				valueXmlString.append("<loc_code>").append("<![CDATA[").append(rs.getString(4).trim()).append("]]>").append("</loc_code>\r\n");			
				// 22/04/10 manoharan in case of empty column there is problem of box char
				//valueXmlString.append("<lot_no>").append("<![CDATA[").append(rs.getString(5).trim()).append("]]>").append("</lot_no>\r\n");			
				//valueXmlString.append("<lot_sl>").append("<![CDATA[").append(rs.getString(6).trim()).append("]]>").append("</lot_sl>\r\n");			
				lotNo = rs.getString(5);
				lotSl = rs.getString(6);
				lotNo = (lotNo == null ? "":lotNo) ;
				lotSl = (lotSl == null ? "":lotSl) ;
				valueXmlString.append("<lot_no>").append("<![CDATA[").append(lotNo).append("]]>").append("</lot_no>\r\n");			
				valueXmlString.append("<lot_sl>").append("<![CDATA[").append(lotSl).append("]]>").append("</lot_sl>\r\n");			
				// end 22/04/10 manoharan in case of empty column there is problem of box char
				valueXmlString.append("<quantity>").append("<![CDATA[").append(rs.getDouble(7)).append("]]>").append("</quantity>\r\n");
				valueXmlString.append("<site_code>").append("<![CDATA[").append(rs.getString(8).trim()).append("]]>").append("</site_code>\r\n");
				valueXmlString.append("<exp_date>").append("<![CDATA[").append(expDate1).append("]]>").append("</exp_date>\r\n");			
				valueXmlString.append("<retest_date>").append("<![CDATA[").append(retestDate1).append("]]>").append("</retest_date>\r\n");
				valueXmlString.append("<dimension>").append("<![CDATA[").append(rs.getString(11) == null?"":rs.getString(11).trim()).append("]]>").append("</dimension>\r\n");			
				valueXmlString.append("<rate>").append("<![CDATA[").append(rs.getDouble(12)).append("]]>").append("</rate>\r\n");
				valueXmlString.append("<crea_date>").append("<![CDATA[").append(rs.getString(13).trim()).append("]]>").append("</crea_date>\r\n");						
				valueXmlString.append("</Detail>\r\n");

			}
			pstmt.close();
			rs.close();
			valueXmlString.append("</Root>\r\n");
		}//try end
		catch (SQLException sqx) {
			System.out.println("The SQLException occurs in WOrderIssueActEJB for Packet button :" + sqx);
			throw new ITMException(sqx);
		} catch (Exception e) {
			System.out.println("The Exception occure in WOrderIssueActEJB  for Packet button:" + e);
			throw new ITMException(e);
		} finally {
			try {
				conn.close();
				conn = null;
			} catch (Exception e) {
			}
		}
		System.out.println("valueXmlString return from actionStock :"	+ valueXmlString.toString());
		return valueXmlString.toString();
	}

	private String stockTransform(Document dom, Document dom1, String objContext, String xtraParams, Document selDataDom) throws ITMException 
	{
		System.out.println("stockTransform is calling.............");
		StringBuffer valueXmlString = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root>\r\n");

		String sql = null, errCode = "", errString = "";
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String woIss = null, expLev = null, itemCode = null, itemDescr = null,  unit = null;
		String stkLocCode = null,stkLotNo = "", stkLotSl = "", reasCode = null, issCriteria = null;
		String siteCode = null, tranType = null, noArtStr = null, retNoArt = null, inputQtyStr= null;
		double perArtQty = 0, noArt = 0;
		String grossWeight = null, tareWeight = null, netWeight = null;
		ArrayList noArtDetArrLst = new ArrayList();
		double quantity = 0, remainingQty = 0, inputQty = 0, stkQty = 0, gWeight = 0;
		Node currDetail = null, currDetail1 = null;

		ConnDriver connDriver = new ConnDriver();
		ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
		GenericUtility genericUtility = GenericUtility.getInstance();
		try {
			conn = connDriver.getConnectDB("DriverITM");

			woIss = genericUtility.getColumnValue("tran_id", dom1);
			siteCode = genericUtility.getColumnValue("site_code", dom1);
			itemCode = genericUtility.getColumnValue("item_code", dom);
			itemDescr = genericUtility.getColumnValue("item_descr", dom);
			quantity = Double.parseDouble(genericUtility.getColumnValue("quantity", dom));
			expLev = genericUtility.getColumnValue("exp_lev", dom);
			System.out.println("expLev ::: " + expLev);
			reasCode = genericUtility.getColumnValue("reas_code", dom);
			tranType = genericUtility.getColumnValue("tran_type", dom1);


			sql = "SELECT ISS_CRITERIA FROM ITEM WHERE ITEM_CODE = ?";
			System.out.println("sql :" + sql);
			System.out.println("itemCode :: " + itemCode);
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, itemCode);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				issCriteria = rs.getString(1);
				System.out.println("issCriteria  :" + issCriteria);
			}

			NodeList detailList = selDataDom.getElementsByTagName("Detail");
			int noOfDetails = detailList.getLength();
			for (int ctr = 0; ctr < noOfDetails; ctr++) {
				currDetail = detailList.item(ctr);
				stkQty = stkQty + Double.parseDouble((GenericUtility.getInstance().getColumnValueFromNode("quantity",currDetail)));
			}
			System.out.println("stkQty  :" + stkQty);

			//			if (stkQty < quantity)
			if (quantity != 0 && stkQty < quantity) //CHANGED BY ALKA 19/07/07 FOR HANDLING NO QUANTITY ENTERED.
			{
				errCode = "VTSTOCK1";
				errString = itmDBAccessEJB.getErrorString("", errCode, "", "", conn);
				conn.close();
				conn = null;
				return errString;
			}
			//			remainingQty = quantity;  //COMMENTED BY ALKA AS THE FOLLOWING LOGIC IS ADDED
			//			ADDED BY ALKA ON 19/07/07 FOR SELECTED STOCK TO BE ENTERED IN THE DETAIL IF THE QUANTITY IS 0
			if (quantity == 0)
			{
				remainingQty = stkQty;
			}
			else
			{
				remainingQty = quantity;
			}
			System.out.println("quantity at the start of the forloop :::: " + quantity);
			System.out.println("remainingQty at the start of the forloop :::: " + remainingQty);
			//			ADDITION ENDED BY ALKA 19/07/07
			if (noOfDetails > 0) 
			{
				for (int ctr = 0; ctr < noOfDetails && remainingQty > 0; ctr++) 
				{
					valueXmlString.append("<Detail>\r\n");
					currDetail1 = detailList.item(ctr);
					stkLocCode = genericUtility.getColumnValueFromNode("loc_code", currDetail1);
					stkLotNo = genericUtility.getColumnValueFromNode("lot_no", currDetail1);
					stkLotSl = genericUtility.getColumnValueFromNode("lot_sl", currDetail1);
					// 22/04/10 manoharan empty value gives box char
					stkLotNo = (stkLotNo == null ? "" : stkLotNo);
					stkLotSl = (stkLotSl == null ? "" : stkLotSl);
					// end 22/04/10 manoharan empty value gives box char
					unit = genericUtility.getColumnValueFromNode("unit", currDetail1);
					stkQty = Double.parseDouble(genericUtility.getColumnValueFromNode("quantity", currDetail1));
					System.out.println("stkQty==>  :" + stkQty);

					if (stkQty >= remainingQty)
					{
						if (issCriteria != null && issCriteria.equalsIgnoreCase("W"))
						{
							inputQty = stkQty;
						}
						else
						{
							inputQty = remainingQty;
						}
						remainingQty = 0;
					}
					else if (stkQty < remainingQty) 
					{
						inputQty = stkQty;
						remainingQty = remainingQty - inputQty;
					}
					System.out.println("inputQty ::::::::::::: " + inputQty);

					valueXmlString.append("<tran_id isSrvCallOnChg='0'>").append("<![CDATA[").append(woIss).append("]]>").append("</tran_id>\r\n");
					valueXmlString.append("<exp_lev isSrvCallOnChg='0'>").append("<![CDATA[").append(expLev).append("]]>").append("</exp_lev>\r\n");
					valueXmlString.append("<item_code isSrvCallOnChg='0'>").append("<![CDATA[").append(itemCode).append("]]>").append("</item_code>\r\n");
					valueXmlString.append("<item_descr isSrvCallOnChg='0'>").append("<![CDATA[").append(itemDescr).append("]]>").append("</item_descr>\r\n");
					valueXmlString.append("<quantity isSrvCallOnChg='0'>").append("<![CDATA[").append(inputQty).append("]]>").append("</quantity>\r\n");
					valueXmlString.append("<unit isSrvCallOnChg='0'>").append("<![CDATA[").append(unit).append("]]>").append("</unit>\r\n");
					valueXmlString.append("<unit__alt isSrvCallOnChg='0'>").append("<![CDATA[").append(unit).append("]]>").append("</unit__alt>\r\n");
					valueXmlString.append("<conv__qty__alt isSrvCallOnChg='0'>").append("<![CDATA[").append("1.0").append("]]>").append("</conv__qty__alt>\r\n");
					valueXmlString.append("<quantity__alt isSrvCallOnChg='0'>").append("<![CDATA[").append(inputQty).append("]]>").append("</quantity__alt>\r\n");
					valueXmlString.append("<loc_code isSrvCallOnChg='0'>").append("<![CDATA[").append(stkLocCode).append("]]>").append("</loc_code>\r\n");
					valueXmlString.append("<lot_no isSrvCallOnChg='0'>").append("<![CDATA[").append(stkLotNo).append("]]>").append("</lot_no>\r\n");
					valueXmlString.append("<lot_sl isSrvCallOnChg='0'>").append("<![CDATA[").append(stkLotSl).append("]]>").append("</lot_sl>\r\n");
					valueXmlString.append("<reas_code isSrvCallOnChg='0'>").append("<![CDATA[").append(reasCode).append("]]>").append("</reas_code>\r\n");
					if (inputQty > 0)
					{
						sql = " SELECT ROUND((CASE WHEN QUANTITY IS NULL THEN 0 ELSE QUANTITY END) / " 
								+ " (CASE WHEN NO_ART IS NULL THEN 1 ELSE NO_ART END),3) FROM INV_PACK_RCP "
								+ " WHERE ITEM_CODE = ? AND LOC_CODE = ? AND LOT_NO = ? AND LOT_SL = ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, itemCode);
						pstmt.setString(2, stkLocCode);
						pstmt.setString(3, stkLotNo);
						pstmt.setString(4, stkLotSl);
						rs = pstmt.executeQuery();

						if (rs.next())
						{
							perArtQty = rs.getDouble(1);
							System.out.println("perArtQty ::: " + perArtQty);
							if (perArtQty == 0)
							{
								perArtQty = 1; // TO AVOID DIVIDE BY ZERO ERROR
							}
							noArt = inputQty/perArtQty;

							valueXmlString.append("<no_art isSrvCallOnChg='0'>").append("<![CDATA[").append(Math.round(noArt)).append("]]>").append("</no_art>\r\n");							
						}
						pstmt.close();
						rs.close();
					}
					if ((siteCode != null) && (itemCode != null) && (stkLocCode != null) && (stkLotNo != null) &&	(stkLotSl != null) && 
							//							(expLev != null) && (quantity != 0) && (tranType != null) )
							(expLev != null) && (inputQty != 0) && (tranType != null) )  //CHANGED BY ALKA 19/07/07 AS WEIGHT DATA WERE NOT DISPLAYED IN THE DETAIL
					{
						gWeight = Double.parseDouble(genericUtility.getColumnValue("gross_weight", dom));
						retNoArt = setNoArt(siteCode, itemCode, stkLocCode, stkLotNo, stkLotSl, expLev, inputQty,tranType, gWeight, conn);

						noArtDetArrLst = genericUtility.getTokenList(retNoArt, ":");
						System.out.println("noArtDetArrLst.size :"+noArtDetArrLst.size());
						System.out.println("noArtDetArrLst.get(1) :"+(String)noArtDetArrLst.get(1));
						grossWeight = (String)noArtDetArrLst.get(0); 
						System.out.println("grossWeight :"+grossWeight);
						tareWeight = (String)noArtDetArrLst.get(1);
						System.out.println("tareWeight :"+tareWeight);
						netWeight = (String)noArtDetArrLst.get(2);
						System.out.println("netWeight :"+netWeight);
						noArtStr = (String)noArtDetArrLst.get(3);
						System.out.println("noArtStr :"+noArtStr);
						inputQtyStr = (String)noArtDetArrLst.get(4);   
						System.out.println("inputQtyStr :"+inputQtyStr);


						valueXmlString.append("<quantity isSrvCallOnChg='0'>").append("<![CDATA[").append(inputQtyStr).append("]]>").append("</quantity>\r\n");
						valueXmlString.append("<gross_weight isSrvCallOnChg='0'>").append("<![CDATA[").append(grossWeight).append("]]>").append("</gross_weight>\r\n");
						valueXmlString.append("<tare_weight isSrvCallOnChg='0'>").append("<![CDATA[").append(tareWeight).append("]]>").append("</tare_weight>\r\n");
						valueXmlString.append("<net_weight isSrvCallOnChg='0'>").append("<![CDATA[").append(netWeight).append("]]>").append("</net_weight>\r\n");
						valueXmlString.append("<no_art isSrvCallOnChg='0'>").append("<![CDATA[").append(noArtStr).append("]]>").append("</no_art>\r\n");
					}
					valueXmlString.append("</Detail>");	
					System.out.println("remainingQty at the end of the forloop :::: " + remainingQty);  //SOP ADDED BY ALKA TO CHECK THE REMAINING STOCK 19/07/07
					if (remainingQty == 0)
					{
						break;
					}
				}// for end
			}// end if
			valueXmlString.append("</Root>");
		}// end try
		catch (ITMException itme) {
			throw itme;
		} catch (Exception e) {
			throw new ITMException(e);
		} finally {
			try {
				System.out.println("Closing Connection...");
				conn.close();
				conn = null;
			} catch (Exception e) {
			}
		}
		System.out.println("valueXmlString from :" + valueXmlString.toString());
		return valueXmlString.toString();
	}

	private String actionAllocate(Document dom, Document dom1, String xtraParams)throws RemoteException, ITMException
	{
		String woIss = null, workOrder = null, effDate = null, siteCode = null, itemCode = null, itemDescr = null;
		String locCode = null, lotNo = null, lotSl = null, expLev = null, reasCode = null, calcPotency = null;
		String tranType = null, unitAlt = null, convQtyAlt = null, qtyAlt = null, bomCode = null;
		String itemCodeMain = null, itemCodeRef = null, dimension = null, useInvStatus = null;
		String stkOpt = null, autoOrd = null, mfgLoc = null, unit = null, fgItem = null, potencyAdjQtyS = null;
		String errCode = "", errString = "", sql = "", loginCode = null;
		String qcReqd = null, autoQcReqd = null, criteria = null, trackShelfLife = null;
		StringBuffer sqlSb = new StringBuffer();
		StringBuffer sqlSb1 = new StringBuffer();
		int cntQ = 0;
		double trimWidth = 0, noArt = 0, quantity = 0, potencyAdjQty = 0, operation = 0, gWeight = 0;
		String stkLocCode = null, stkLotNo = null, stkLotSl = null, expDate = null;
		String itemSer = null, siteCodeMfg = null, mfgDate = null, potencyPerc = null, allocQty = null, packCode = null;
		double perArtQty = 0;

		double remainingQty = 0d, inputQty = 0d, stkQtyLot = 0d;
		String grossWeight = null, tareWeight = null, netWeight = null, noArtStr = null, inputQtyStr = null;
		double stockQuantity = 0d;


		String retNoArt = null, locGroup = "", locUsageType = "";
		ArrayList noArtDetArrLst = new ArrayList();
		PreparedStatement pstmt = null, pstmt1 = null, cpstmt1 = null;
		Connection conn = null;
		ResultSet rs = null, rs1 = null, crs1 = null;
		ConnDriver connDriver = new ConnDriver();
		StringBuffer valueXmlString = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root>\r\n");

		if (dom == null)
		{
			valueXmlString.append("</Root>\r\n");
			return valueXmlString.toString();
		}

		GenericUtility genericUtility = GenericUtility.getInstance();
		ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
		loginCode = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginCode");
		MfgCommon mfgCommon = new MfgCommon();

		try
		{
			conn = connDriver.getConnectDB("DriverITM");

			System.out.println("[Allocating .....]");

			woIss = genericUtility.getColumnValue("tran_id", dom1);
			workOrder = genericUtility.getColumnValue("work_order", dom1);
			effDate = genericUtility.getColumnValue("eff_date", dom1);
			siteCode = genericUtility.getColumnValue("site_code", dom1);
			itemCode = genericUtility.getColumnValue("item_code", dom);
			itemDescr = genericUtility.getColumnValue("item_descr", dom);
			locCode = genericUtility.getColumnValue("loc_code",dom);
			lotNo = genericUtility.getColumnValue("lot_no", dom);
			lotSl =	genericUtility.getColumnValue("lot_sl", dom);
			quantity = Double.parseDouble(genericUtility.getColumnValue("quantity", dom));
			System.out.println("Manohar 14/03/12 quantity [" + quantity + "]");
			expLev = genericUtility.getColumnValue("exp_lev", dom);
			System.out.println("expLev ::: " + expLev);
			reasCode = genericUtility.getColumnValue("reas_code", dom);
			calcPotency = genericUtility.getColumnValue("calc_potency", dom);
			tranType = genericUtility.getColumnValue("tran_type", dom1);
			// 26/03/12 manoharan
			locGroup = genericUtility.getColumnValue("loc_group", dom1); 
			sql = "SELECT loc_usage_type FROM WORKORDER_bill WHERE WORK_ORDER = ? and exp_lev = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, workOrder);
			pstmt.setString(2, expLev);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				locUsageType = rs.getString(1);
			}
			pstmt.close();
			rs.close();

			if (tranType.equalsIgnoreCase("F"))
			{
				unitAlt = genericUtility.getColumnValue("unit__alt", dom);
				convQtyAlt = genericUtility.getColumnValue("conv__qty__alt", dom);
				qtyAlt = genericUtility.getColumnValue("quantity__alt", dom);
			}
			operation = Double.parseDouble(genericUtility.getColumnValue("operation", dom1));
			bomCode = genericUtility.getColumnValue("bom_code", dom1);

			//			VAL ADDED NOT TO ALLOW SAVE IF EXP_LEV IS NULL
			if (expLev == null || expLev.trim().length() == 0)
			{
				errCode = "VTEXP1";
				errString = itmDBAccessEJB.getErrorString("exp_lev", errCode, loginCode);
				return errString;
			}

			//			TRIM WIDTH FOR DIMENSION VALIDATION
			sql = "SELECT ITEM_CODE FROM WORKORDER WHERE WORK_ORDER = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, workOrder);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				fgItem = rs.getString(1);
			}
			pstmt.close();
			rs.close();

			sql = "SELECT A.TRIM_WIDTH FROM ITEM_TYPE A , ITEM B WHERE A.ITEM_TYPE = B.ITEM_TYPE AND B.ITEM_CODE = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, fgItem);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				trimWidth = rs.getDouble(1);
			}
			pstmt.close();
			rs.close();

			sql = "SELECT ITEM_CODE, ITEM_CODE__REF FROM WORKORDER_BILL WHERE WORK_ORDER = ? AND OPERATION  = ? AND EXP_LEV = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, workOrder);
			pstmt.setDouble(2, operation);
			pstmt.setString(3, expLev);
			System.out.println("Sql ::: " + sql);
			System.out.println("Setting param ::: workOrder :: " + workOrder + "operation :: " + operation + "expLev :: " + expLev);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				itemCodeMain = rs.getString(1);
				itemCodeRef = rs.getString(2);
				sql = "SELECT DIMENSION, USE_INVSTATUS FROM BOMDET WHERE BOM_CODE = ? AND ITEM_CODE = ? AND ITEM_REF  = ?";
				pstmt1 = conn.prepareStatement(sql);
				pstmt1.setString(1, bomCode);
				pstmt1.setString(2, itemCodeMain);
				pstmt1.setString(3, itemCodeRef);
				System.out.println("Sql ::: " + sql);
				System.out.println("Setting param ::: bomCode :: " + bomCode + "itemCodeMain :: " + itemCodeMain + "itemCodeRef :: " + itemCodeRef);

				rs1 = pstmt1.executeQuery();
				if (rs1.next())
				{
					dimension = rs1.getString(1)==null?"":rs1.getString(1);
					useInvStatus = rs1.getString(2)==null?"U":rs1.getString(2);
				}
				pstmt1.close();
				rs1.close();
			}
				useInvStatus = useInvStatus==null?" ":useInvStatus;
				dimension = dimension==null?" ":dimension;
			
			pstmt.close();
			rs.close();

			//			NOT TO BE INCLUDED AS RND NOT CONSIDERED -- ALKA
			//			sql = "SELECT ORDER_TYPE FROM WORKORDER WHERE WORK_ORDER = ?";
			//			pstmt = conn.prepareStatement(sql);
			//			pstmt.setString(1, workOrder);
			//			rs = pstmt.executeQuery();
			//			if (rs.next())
			//			{
			//				orderType = rs.getString(1);
			//			}
			//			pstmt.close();
			//			rs.close();
			//			
			//			if (orderType.equalsIgnoreCase("T")
			//			{
			//				stkOpt = chkStkOptRnd(itemCode, siteCode);
			//			}
			//			else
			//			{
			//				stkOpt = chkStkOpt(itemCode, siteCode);
			//			}

			stkOpt = mfgCommon.chkStkOpt(siteCode,itemCode,conn);

			sql = "SELECT AUTO_ORD FROM BOMDET WHERE BOM_CODE = ? AND ITEM_CODE = ? AND ITEM_REF = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, bomCode);
			pstmt.setString(2, itemCode);
			pstmt.setString(3, itemCodeRef);
			System.out.println("Sql ::: " + sql);
			System.out.println("Setting param ::: bomCode :: " + bomCode + "itemCodeMain :: " + itemCodeMain + "itemCodeRef :: " + itemCodeRef);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				autoOrd = rs.getString(1)==null?"N":rs.getString(1);
			}
			pstmt.close();
			rs.close();
			System.out.println("stkOpt ::: " + stkOpt);
			if (stkOpt.equalsIgnoreCase("0"))
			{
				System.out.println("autoOrd ::: " + autoOrd);
				if (autoOrd.equalsIgnoreCase("N"))
				{
					mfgLoc = mfgCommon.getEnvMfg("999999","NON_STOCK", conn);
					System.out.println("mfgLoc ::: " + mfgLoc);
					if (mfgLoc.equalsIgnoreCase("NULLFOUND"))
					{
						errCode = "VTNOLOC";
						errString = itmDBAccessEJB.getErrorString("",errCode, loginCode);
						return errString;
					}

					sql = "SELECT UNIT FROM ITEM WHERE ITEM_CODE = ?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, itemCode);
					rs = pstmt.executeQuery();
					if (rs.next())
					{
						unit = rs.getString(1);
					}
					pstmt.close();
					rs.close();

					valueXmlString.append("<Detail>\r\n");
					valueXmlString.append("<tran_id>").append("<![CDATA[").append(woIss).append("]]>").append("</tran_id>\r\n");
					valueXmlString.append("<exp_lev>").append("<![CDATA[").append(expLev).append("]]>").append("</exp_lev>\r\n");
					valueXmlString.append("<item_code>").append("<![CDATA[").append(itemCode).append("]]>").append("</item_code>\r\n");
					valueXmlString.append("<item_descr>").append("<![CDATA[").append(itemDescr).append("]]>").append("</item_descr>\r\n");
					valueXmlString.append("<quantity>").append("<![CDATA[").append(quantity).append("]]>").append("</quantity>\r\n");
					valueXmlString.append("<unit>").append("<![CDATA[").append(unit).append("]]>").append("</unit>\r\n");
					valueXmlString.append("<loc_code>").append("<![CDATA[").append(mfgLoc).append("]]>").append("</loc_code>\r\n");
					valueXmlString.append("<lot_no>").append("<![CDATA[").append("              ").append("]]>").append("</lot_no>\r\n");
					valueXmlString.append("<lot_sl>").append("<![CDATA[").append("     ").append("]]>").append("</lot_sl>\r\n");
					valueXmlString.append("<stk_opt>").append("<![CDATA[").append(1).append("]]>").append("</stk_opt>\r\n");
					valueXmlString.append("<reas_code>").append("<![CDATA[").append(reasCode).append("]]>").append("</reas_code>\r\n");
					if (tranType.equalsIgnoreCase("F"))
					{
						valueXmlString.append("<unit__alt>").append("<![CDATA[").append(unitAlt).append("]]>").append("</unit__alt>\r\n");
						valueXmlString.append("<conv__qty__alt>").append("<![CDATA[").append(convQtyAlt).append("]]>").append("</conv__qty__alt>\r\n");
						valueXmlString.append("<quantity__alt>").append("<![CDATA[").append(qtyAlt).append("]]>").append("</quantity__alt>\r\n");
						valueXmlString.append("<lot_sl>").append("<![CDATA[").append("1S").append("]]>").append("</lot_sl>\r\n");
						valueXmlString.append("<no_art>").append("<![CDATA[").append("1").append("]]>").append("</no_art>\r\n");
					}
				}
				valueXmlString.append("</Detail>\r\n");
			}
			else 
			{
				//			depending on calc_potency flag potency_adj considered
				calcPotency = genericUtility.getColumnValue("calc_potency", dom);
				potencyAdjQtyS = genericUtility.getColumnValue("potency_adj", dom);
				if (potencyAdjQtyS != null)
				{
					potencyAdjQty = Double.parseDouble(potencyAdjQtyS);
				}
				System.out.println("calcPotency ::: " + calcPotency);
				if (calcPotency != null)
				{
					if (calcPotency.equalsIgnoreCase("Y"))
					{
						quantity = quantity + potencyAdjQty;
					}
				}
				System.out.println("Manohar quantity 2 [" + quantity + "]" );
				if (lotNo != null && lotNo.trim().length() > 0)
				{
					if (lotSl == null && lotSl.trim().length() == 0)
					{
						errCode = "VTSELLOT";
						errString = itmDBAccessEJB.getErrorString("", errCode, loginCode);
						return errString;
					}
				}

				//			WHILE ISSUING PRINTED/PLAIN FOIL TO PRODUCTION...TAKE WHOLE LOTSL QUANTITY.
				//			FOR QC_REQD TO BE CHECKED FROM FIRST SITEITEM & THEN ITEM
				qcReqd = mfgCommon.chkQcReqd(siteCode, itemCode, conn);
				autoQcReqd = mfgCommon.chkAutoQcReqd(siteCode, itemCode, conn);

				sql = "SELECT ISS_CRITERIA, TRACK_SHELF_LIFE FROM ITEM WHERE ITEM_CODE = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, itemCode);
				rs = pstmt.executeQuery();
				if (rs.next())
				{
					criteria = rs.getString(1);
					trackShelfLife = rs.getString(2) == null?"N":rs.getString(2);
				}
				pstmt.close();
				rs.close();

				System.out.println("locCode ::: " + locCode + "trackShelfLife ::: " + trackShelfLife );
				System.out.println("useInvStatus ::: " + useInvStatus + "dimension ::: " + dimension );
				useInvStatus = useInvStatus==null?" ":useInvStatus;
				dimension = dimension==null?" ":dimension;

				if ((locCode == null || locCode.trim().length() == 0) || (lotNo == null || lotNo.trim().length() == 0))
				{
					sqlSb.append("SELECT (CASE WHEN SUM(A.QUANTITY - A.ALLOC_QTY) IS NULL THEN 0 ELSE SUM(A.QUANTITY - A.ALLOC_QTY) END) ");
					sqlSb.append("FROM STOCK A, INVSTAT B, LOCATION C ");
					sqlSb.append("WHERE A.LOC_CODE = C.LOC_CODE AND C.INV_STAT  = B.INV_STAT ");
					sqlSb.append("AND A.ITEM_CODE = ? ");
					sqlSb.append("AND A.SITE_CODE = ? ");
					sqlSb.append("AND (A.QUANTITY - (CASE WHEN A.ALLOC_QTY IS NULL THEN 0 ELSE A.ALLOC_QTY END)) > 0 ");
					sqlSb.append("AND B.STAT_TYPE <> 'S'");

					if (trackShelfLife.equalsIgnoreCase("Y"))
					{
						sqlSb.append(" AND ( A.EXP_DATE > ? OR A.EXP_DATE IS NULL) ");
					}

					if (useInvStatus.equalsIgnoreCase("U"))
					{
						sqlSb.append(" AND B.AVAILABLE = 'Y' ");
						if (qcReqd.equalsIgnoreCase("Y") && autoQcReqd.equalsIgnoreCase("Y"))
						{
							sqlSb.append(" AND ( A.RETEST_DATE > ? ) ");
						}
					}

					if (dimension.trim().length() > 0)
					{
						sqlSb.append(" AND DDF_COMPARE_DIMENSION(CASE WHEN A.DIMENSION IS NULL THEN ' ' ELSE A.DIMENSION END, ?, ?) = 'Y'");
					}
					sql = sqlSb.toString();
					// 26/03/12 manoharan
					if (locGroup != null && locGroup.trim().length() > 0 && "A".equals(locUsageType) )
					{
						sql = sql + " AND c.loc_group = '" + locGroup + "' ";
					}
					// added by cpatil start on 9/11/12
					//sql = sql + "order by (CASE WHEN SUM(A.QUANTITY - A.ALLOC_QTY) IS NULL THEN 0 ELSE SUM(A.QUANTITY - A.ALLOC_QTY) END) "; 
							// " order by sum( (case when quantity is null then 0 else quantity end) " +
			 				// " - (case when alloc_qty is null then 0 else alloc_qty end) " + 
							// " - (case when hold_qty is null then 0 else hold_qty end) )";
					// end by cpatil
					// end 26/03/12 manoharan
					System.out.println("sqlSB converted ::: "+ sql);
					StringTokenizer st = new StringTokenizer(sql, "?");
					cntQ = st.countTokens();
					System.out.println ("cntQ ::: " + cntQ);
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, itemCode);
					pstmt.setString(2, siteCode);
					boolean flag1 = false, flag2 = false;
					for (int i=3; i<cntQ; i++)
					{
						if (trackShelfLife.equalsIgnoreCase("Y") && flag1 == false)
						{
							pstmt.setTimestamp(i, Timestamp.valueOf(genericUtility.getValidDateString(effDate, genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat()) + " 00:00:00"));
							System.out.println("expdate date set");
							flag1 = true;
							continue;
						}
						if (useInvStatus.equalsIgnoreCase("U") && flag2 == false)
						{
							if (qcReqd.equalsIgnoreCase("Y") && autoQcReqd.equalsIgnoreCase("Y"))
							{
								pstmt.setTimestamp(i, Timestamp.valueOf(genericUtility.getValidDateString(effDate, genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat()) + " 00:00:00"));
								System.out.println("retest date set");
								flag2 = true;
								continue;
							}
						}
						if (dimension.trim().length() > 0)
						{
							pstmt.setString(i, dimension);
							pstmt.setDouble(i+1, trimWidth);
							break;
						}
					}
					System.out.println("Setting parameters ::: itemCode ::: " + itemCode + " siteCode ::: " + siteCode);
					System.out.println("effDate ::: " + effDate + " dimension ::: " + dimension + " trimWidth ::: " + trimWidth);
					rs = pstmt.executeQuery();
					if (rs.next())
					{
						stockQuantity = rs.getDouble(1);
						System.out.println("Manohar stockQuantity ["+stockQuantity + "] quantity [" + quantity + "]" );
						if (stockQuantity == 0 || quantity > stockQuantity)
						{
							errCode = "VTSTOCK1";
							errString = itmDBAccessEJB.getErrorString("",errCode,"","",conn);
							System.out.println("errString :"+errString);
							return errString;
						}	
					}
					else
					{
						System.out.println("Manohar no Stock" );
						errCode = "VTSTOCK1";
						errString = itmDBAccessEJB.getErrorString("",errCode, loginCode);
						return errString;
					}

					pstmt.close();
					rs.close();

					sqlSb1.append("SELECT A.LOC_CODE, A.LOT_NO, A.LOT_SL,(A.QUANTITY - A.ALLOC_QTY), A.EXP_DATE, A.UNIT, ");
					sqlSb1.append("A.ITEM_SER, A.SITE_CODE__MFG, A.MFG_DATE, A.POTENCY_PERC, A.ALLOC_QTY, "); 
					sqlSb1.append("A.PACK_CODE ");
					sqlSb1.append("FROM STOCK A, INVSTAT B, LOCATION C ");
					sqlSb1.append("WHERE A.LOC_CODE = c.LOC_CODE AND B.INV_STAT  = C.INV_STAT ");
					sqlSb1.append("AND A.ITEM_CODE = ? ");
					sqlSb1.append("AND A.SITE_CODE = ? ");
					sqlSb1.append(" AND ((CASE WHEN A.QUANTITY IS NULL THEN 0 ELSE A.QUANTITY END) - ");
					sqlSb1.append(" (CASE WHEN A.ALLOC_QTY IS NULL THEN 0 ELSE A.ALLOC_QTY END)) > 0 ");
					sqlSb1.append("AND B.STAT_TYPE <> 'S'");

					if (trackShelfLife.equalsIgnoreCase("Y"))
					{
						sqlSb1.append(" AND ( A.EXP_DATE > ? OR A.EXP_DATE IS NULL) ");
					}

					if (useInvStatus.equalsIgnoreCase("U"))
					{
						sqlSb1.append(" AND B.AVAILABLE = 'Y' ");
						if (qcReqd.equalsIgnoreCase("Y") && autoQcReqd.equalsIgnoreCase("Y"))
						{
							sqlSb1.append(" AND ( A.RETEST_DATE > ? ) ");
						}
					}

					if (dimension.trim().length() > 0)
					{
						sqlSb1.append(" AND DDF_COMPARE_DIMENSION(CASE WHEN A.DIMENSION IS NULL THEN ' ' ELSE A.DIMENSION END, ?, ?) = 'Y'");
					}
					sql = sqlSb1.toString();
					// 26/03/12 manoharan
					if (locGroup != null && locGroup.trim().length() > 0 && "A".equals(locUsageType) )
					{
						sql = sql + " AND c.loc_group = '" + locGroup + "' ";
					}
					// 22/12/12 manoharan
					if ("db2".equalsIgnoreCase(CommonConstants.DB_NAME))
					{
						sql = sql + " nvl(a.exp_date,a.crea_date),a.crea_date,a.lot_no ";
						//System.out.println("after sql db converted --["+sql+"]->.");
					}
					else
					{	

						sql = sql + " order by a.partial_used,nvl(a.exp_date,a.crea_date),a.crea_date,a.lot_no, "
								+ " decode(length(decode( substr(rtrim(a.lot_sl), length(rtrim(a.lot_sl))) ," +
								" 'S',(substr(rtrim(a.lot_sl),1,length(rtrim(a.lot_sl)) - 1)) ,rtrim(a.lot_sl)) ), " 
								+ " 1, '00' || decode( substr(rtrim(a.lot_sl), length(rtrim(a.lot_sl))) , " +
								"'S',(substr(rtrim(a.lot_sl),1,length(rtrim(a.lot_sl)) )) ,rtrim(a.lot_sl)), "        
								+" decode(length(decode( substr(rtrim(a.lot_sl), length(rtrim(a.lot_sl))) , 'S',(substr(rtrim(a.lot_sl),1,length(rtrim(a.lot_sl)) - 1)) ,rtrim(a.lot_sl)) ), "
								+ " 2, '0' || decode( substr(rtrim(a.lot_sl), length(rtrim(a.lot_sl))) , 'S',(substr(rtrim(a.lot_sl),1,length(rtrim(a.lot_sl)) )) ,rtrim(a.lot_sl)), "         
								+" decode( substr(rtrim(a.lot_sl), length(rtrim(a.lot_sl))) ," +
								" 'S',(substr(rtrim(a.lot_sl),1,length(rtrim(a.lot_sl)) - 1)) ,rtrim(a.lot_sl)) )) ";
					}  
							
					// added by cpatil start on 9/11/12
					//sql = sql + " order by (A.QUANTITY - A.ALLOC_QTY) "; 
							//" order by sum( (case when quantity is null then 0 else quantity end) " +
			 				//" - (case when alloc_qty is null then 0 else alloc_qty end) " + 
							// " - (case when hold_qty is null then 0 else hold_qty end) )";
					// end by cpatil 
					// end 26/03/12 manoharan

					System.out.println("sqlSB1 converted ::: "+ sql);
					StringTokenizer st1 = new StringTokenizer(sql, "?");
					cntQ = st1.countTokens();
					pstmt = conn.prepareStatement(sql);
					flag1 = false; flag2 = false;
					pstmt.setString(1,itemCode);
					pstmt.setString(2,siteCode);
					for (int i=3; i<cntQ; i++)
					{
						if (trackShelfLife.equalsIgnoreCase("Y") && flag1 == false)
						{
							pstmt.setTimestamp(i, Timestamp.valueOf(genericUtility.getValidDateString(effDate, genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat()) + " 00:00:00"));
							flag1 = true;
							continue;
						}
						if (useInvStatus.equalsIgnoreCase("U") && flag2 == false)
						{
							if (qcReqd.equalsIgnoreCase("Y") && autoQcReqd.equalsIgnoreCase("Y"))
							{
								pstmt.setTimestamp(i, Timestamp.valueOf(genericUtility.getValidDateString(effDate, genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat()) + " 00:00:00"));
								flag2 = true;
								continue;
							}
						}
						if (dimension.trim().length() > 0)
						{
							pstmt.setString(i,dimension);
							pstmt.setDouble(i+1, trimWidth);
							break;
						}
					}
					System.out.println("Setting parameters ::: itemCode ::: "+itemCode+"siteCode ::: " +siteCode);
					System.out.println("effDate ::: " +effDate+ "dimension ::: " +dimension+ "trimWidth ::: " +trimWidth);

					rs = pstmt.executeQuery();
					remainingQty = quantity;
					while (rs.next())
					{
						stkLocCode = rs.getString(1);
						stkLotNo = rs.getString(2);
						stkLotSl = rs.getString(3);
						stkQtyLot = rs.getDouble(4);
						expDate = rs.getString(5);
						unit = rs.getString(6);
						itemSer = rs.getString(7);
						siteCodeMfg = rs.getString(8);
						mfgDate = rs.getString(9);
						potencyPerc = rs.getString(10);
						allocQty = rs.getString(11);
						packCode = rs.getString(12);


						System.out.println("manohar stkQtyLot [" + stkQtyLot + "] remainingQty [" + remainingQty + "] criteria [" + criteria + "]");
						if (stkQtyLot >= remainingQty)
						{
							if (criteria != null && criteria.equalsIgnoreCase("W"))
							{
								inputQty = stkQtyLot;
							}
							else
							{
								inputQty = remainingQty;
							}
							remainingQty = 0;
						}
						else if (stkQtyLot < remainingQty)
						{
							inputQty = stkQtyLot;
							remainingQty = remainingQty - inputQty;
						}
						System.out.println("inputQty :: " + inputQty);

						valueXmlString.append("<Detail>\r\n");
						valueXmlString.append("<tran_id>").append("<![CDATA[").append(woIss).append("]]>").append("</tran_id>\r\n");
						valueXmlString.append("<exp_lev>").append("<![CDATA[").append(expLev).append("]]>").append("</exp_lev>\r\n");
						valueXmlString.append("<item_code>").append("<![CDATA[").append(itemCode).append("]]>").append("</item_code>\r\n");
						valueXmlString.append("<item_descr>").append("<![CDATA[").append(itemDescr).append("]]>").append("</item_descr>\r\n");
						valueXmlString.append("<quantity>").append("<![CDATA[").append(inputQty).append("]]>").append("</quantity>\r\n");
						valueXmlString.append("<unit>").append("<![CDATA[").append(unit).append("]]>").append("</unit>\r\n");
						valueXmlString.append("<unit__alt>").append("<![CDATA[").append(unit).append("]]>").append("</unit__alt>\r\n");
						valueXmlString.append("<conv__qty__alt>").append("<![CDATA[").append("1.0").append("]]>").append("</conv__qty__alt>\r\n");
						valueXmlString.append("<quantity__alt>").append("<![CDATA[").append(inputQty).append("]]>").append("</quantity__alt>\r\n");
						valueXmlString.append("<loc_code>").append("<![CDATA[").append(stkLocCode).append("]]>").append("</loc_code>\r\n");
						// 22/04/10 manoharan empty value gives box char
						stkLotNo = (stkLotNo == null ? "" : stkLotNo);
						stkLotSl = (stkLotSl == null ? "" : stkLotSl);
						// end 22/04/10 manoharan empty value gives box char
						valueXmlString.append("<lot_no>").append("<![CDATA[").append(stkLotNo).append("]]>").append("</lot_no>\r\n");
						valueXmlString.append("<lot_sl>").append("<![CDATA[").append(stkLotSl).append("]]>").append("</lot_sl>\r\n");
						valueXmlString.append("<reas_code>").append("<![CDATA[").append(reasCode).append("]]>").append("</reas_code>\r\n");
						if (inputQty > 0)
						{
							sql = " SELECT ROUND((CASE WHEN QUANTITY IS NULL THEN 0 ELSE QUANTITY END) / " 
									+ " (CASE WHEN NO_ART IS NULL THEN 1 ELSE NO_ART END),3) FROM INV_PACK_RCP "
									+ " WHERE ITEM_CODE = ? AND LOC_CODE = ? AND LOT_NO = ? AND LOT_SL = ? ";
							cpstmt1 = conn.prepareStatement(sql);
							cpstmt1.setString(1, itemCode);
							cpstmt1.setString(2, stkLocCode);
							cpstmt1.setString(3, stkLotNo);
							cpstmt1.setString(4, stkLotSl);

							System.out.println("sql ::: " + sql);
							System.out.println("Setting parameters ::: itemCode ::: " + itemCode + "stkLocCode ::: " + stkLocCode);
							System.out.println("stkLotNo ::: " + stkLotNo + "stkLotSl ::: " + stkLotSl );
							crs1 = cpstmt1.executeQuery();
							if (crs1.next())
							{
								perArtQty = crs1.getDouble(1);
								System.out.println("perArtQty ::: " + perArtQty);
							}
							if (perArtQty == 0)
							{
								perArtQty = 1; // TO AVOID DIVIDE BY ZERO ERROR
							}
							noArt = inputQty/perArtQty;

							valueXmlString.append("<no_art>").append("<![CDATA[").append(Math.round(noArt)).append("]]>").append("</no_art>\r\n");									


							crs1.close(); crs1 = null;
							cpstmt1.close(); cpstmt1 = null;
						}
						if ((siteCode != null) && (itemCode != null) && (locCode != null) && (lotNo != null) &&	(lotSl != null) && 
								//								(expLev != null) && (quantity != 0) && (tranType != null) )
								(expLev != null) && (inputQty != 0) && (tranType != null) ) //CHANGED BY ALKA 19/07/07 AS WEIGHT DATA IS NOT DISPLAYED IN THE DETAIL
						{
							gWeight = Double.parseDouble(genericUtility.getColumnValue("gross_weight", dom));
							retNoArt = setNoArt(siteCode, itemCode, locCode, lotNo, lotSl, expLev, inputQty,tranType, gWeight, conn);

							noArtDetArrLst = genericUtility.getTokenList(retNoArt, ":");
							System.out.println("noArtDetArrLst.size :"+noArtDetArrLst.size());
							System.out.println("noArtDetArrLst.get(0) :"+(String)noArtDetArrLst.get(0));
							grossWeight = (String)noArtDetArrLst.get(0); 
							System.out.println("grossWeight :"+grossWeight);
							tareWeight = (String)noArtDetArrLst.get(1);
							System.out.println("tareWeight :"+tareWeight);
							netWeight = (String)noArtDetArrLst.get(2);
							System.out.println("netWeight :"+netWeight);
							noArtStr = (String)noArtDetArrLst.get(3);
							System.out.println("noArtStr :"+noArtStr);
							inputQtyStr = (String)noArtDetArrLst.get(4);
							System.out.println("inputQtyStr :"+inputQtyStr);


							valueXmlString.append("<quantity>").append("<![CDATA[").append(inputQtyStr).append("]]>").append("</quantity>\r\n");
							valueXmlString.append("<gross_weight>").append("<![CDATA[").append(grossWeight).append("]]>").append("</gross_weight>\r\n");
							valueXmlString.append("<tare_weight>").append("<![CDATA[").append(tareWeight).append("]]>").append("</tare_weight>\r\n");
							valueXmlString.append("<net_weight>").append("<![CDATA[").append(netWeight).append("]]>").append("</net_weight>\r\n");
							valueXmlString.append("<no_art>").append("<![CDATA[").append(noArtStr).append("]]>").append("</no_art>\r\n");
						}
						valueXmlString.append("</Detail>\r\n");
						if (remainingQty ==  0)
						{
							break;
						}
					}//end of while
					rs.close();
					pstmt.close();


					if (remainingQty > 0)
					{
						System.out.println("Manohar remainingQty ["+remainingQty + "]" );
						errCode = "VTSTOCK1";
						errString = itmDBAccessEJB.getErrorString("",errCode, loginCode);
						return errString;
					}
				}
				else
				{
					//				IF LOC_CODE AND LOT NO AND LOT_SL ARE ENTERED BY USER
					//				TO CHECK ALL CONDITIONS RELATED QC_REQD & TRACKSHELFLIFE
					sqlSb.append(" SELECT (CASE WHEN SUM(A.QUANTITY-A.ALLOC_QTY) IS NULL THEN 0 ELSE SUM(A.QUANTITY-A.ALLOC_QTY) END)");
					sqlSb.append(" FROM STOCK A, INVSTAT B, LOCATION C ");
					sqlSb.append(" WHERE A.INV_STAT  = B.INV_STAT AND A.ITEM_CODE = ? AND A.SITE_CODE = ? ");
					sqlSb.append(" AND A.LOC_CODE = ? AND A.LOT_NO = ? AND A.LOT_SL = ? ");
					sqlSb.append(" AND (A.QUANTITY - A.ALLOC_QTY) >= ? AND B.STAT_TYPE <> 'S' ");

					if (trackShelfLife.equalsIgnoreCase("Y"))
					{
						sqlSb.append(" AND ( A.EXP_DATE > ? OR A.EXP_DATE IS NULL) ");
					}

					if (useInvStatus.equalsIgnoreCase("U"))
					{
						sqlSb.append(" AND B.AVAILABLE = 'Y' ");
						if (qcReqd.equalsIgnoreCase("Y") && autoQcReqd.equalsIgnoreCase("Y"))
						{
							sqlSb.append(" AND ( A.RETEST_DATE > ? ) ");
						}
					}

					if (dimension.trim().length() > 0)
					{ 
						sqlSb.append(" AND DDF_COMPARE_DIMENSION(CASE WHEN A.DIMENSION IS NULL THEN ' ' ELSE A.DIMENSION END, ?, ?) = 'Y'");
					}

					sql = sqlSb.toString();
					// 26/03/12 manoharan
					if (locGroup != null && locGroup.trim().length() > 0 && "A".equals(locUsageType) )
					{
						sql = sql + " AND c.loc_group = '" + locGroup + "' ";
					}
					if ("db2".equalsIgnoreCase(CommonConstants.DB_NAME))
					{
						sql = sql + " order by a.partial_used,nvl(a.exp_date,a.crea_date),a.crea_date,a.lot_no ";
						//System.out.println("after sql db converted --["+sql+"]->.");
					}
					else
					{	

						sql = sql + " order by a.partial_used,nvl(a.exp_date,a.crea_date),a.crea_date,a.lot_no, "
								+ " decode(length(decode( substr(rtrim(a.lot_sl), length(rtrim(a.lot_sl))) ," +
								" 'S',(substr(rtrim(a.lot_sl),1,length(rtrim(a.lot_sl)) - 1)) ,rtrim(a.lot_sl)) ), " 
								+ " 1, '00' || decode( substr(rtrim(a.lot_sl), length(rtrim(a.lot_sl))) , " +
								"'S',(substr(rtrim(a.lot_sl),1,length(rtrim(a.lot_sl)) )) ,rtrim(a.lot_sl)), "        
								+" decode(length(decode( substr(rtrim(a.lot_sl), length(rtrim(a.lot_sl))) , 'S',(substr(rtrim(a.lot_sl),1,length(rtrim(a.lot_sl)) - 1)) ,rtrim(a.lot_sl)) ), "
								+ " 2, '0' || decode( substr(rtrim(a.lot_sl), length(rtrim(a.lot_sl))) , 'S',(substr(rtrim(a.lot_sl),1,length(rtrim(a.lot_sl)) )) ,rtrim(a.lot_sl)), "         
								+" decode( substr(rtrim(a.lot_sl), length(rtrim(a.lot_sl))) ," +
								" 'S',(substr(rtrim(a.lot_sl),1,length(rtrim(a.lot_sl)) - 1)) ,rtrim(a.lot_sl)) )) ";
					}  
					// added by cpatil start on 9/11/12
					//sql = sql + " order by (CASE WHEN SUM(A.QUANTITY-A.ALLOC_QTY) IS NULL THEN 0 ELSE SUM(A.QUANTITY-A.ALLOC_QTY) END) "; 
							 //" order by sum( (case when quantity is null then 0 else quantity end) " +
			 				 //" - (case when alloc_qty is null then 0 else alloc_qty end) " + 
							 //" - (case when hold_qty is null then 0 else hold_qty end) )";
					// end by cpatil
					// end 26/03/12 manoharan

					System.out.println("sqlSB converted ::: "+ sql);
					StringTokenizer st1 = new StringTokenizer(sql, "?");
					cntQ = st1.countTokens();
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, itemCode);
					pstmt.setString(2, siteCode);
					pstmt.setString(3, locCode);
					pstmt.setString(4, lotNo);
					pstmt.setString(5, lotSl);
					pstmt.setDouble(6, quantity);
					boolean flag1 = false, flag2 = false; 
					for (int i=7; i<cntQ; i++)
					{

						if (trackShelfLife.equalsIgnoreCase("Y") && flag1 == false)
						{
							pstmt.setTimestamp(i, Timestamp.valueOf(genericUtility.getValidDateString(effDate, genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat()) + " 00:00:00"));
							flag1 = true;
							continue;
						}
						if (useInvStatus.equalsIgnoreCase("U") && flag2 == false)
						{
							if (qcReqd.equalsIgnoreCase("Y") && autoQcReqd.equalsIgnoreCase("Y"))
							{
								pstmt.setTimestamp(i, Timestamp.valueOf(genericUtility.getValidDateString(effDate, genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat()) + " 00:00:00"));
								flag2 = true;
								continue;
							}
						}
						if (dimension.trim().length() > 0)
						{
							pstmt.setString(i, dimension);
							pstmt.setDouble(i+1, trimWidth);
							break;
						}
					}
					rs = pstmt.executeQuery();
					if (rs.next())
					{
						stockQuantity = rs.getDouble(1);
						System.out.println("stockQuantity :"+stockQuantity);
						if (stockQuantity == 0 || quantity > stockQuantity)
						{
							errCode = "VTSTOCK1";
							errString = itmDBAccessEJB.getErrorString("",errCode,"","",conn);
							System.out.println("errString :"+errString);
							return errString;
						}	
					}
					else
					{
						errCode = "VTSTOCK1";
						errString = itmDBAccessEJB.getErrorString("",errCode, loginCode);
						return errString;
					}
					pstmt.close();
					rs.close();
					if ("W".equals(criteria))
					{
						valueXmlString.append("<quantity>").append("<![CDATA[").append(stockQuantity).append("]]>").append("</quantity>\r\n");
					}
					else
					{
						valueXmlString.append("<quantity>").append("<![CDATA[").append(quantity).append("]]>").append("</quantity>\r\n");
					}
					if (quantity > 0)
					{
						sql = " SELECT ROUND((CASE WHEN QUANTITY IS NULL THEN 0 ELSE QUANTITY END) / " 
								+ " (CASE WHEN NO_ART IS NULL THEN 1 ELSE NO_ART END),3) FROM INV_PACK_RCP "
								+ " WHERE ITEM_CODE = ? AND LOC_CODE = ? AND LOT_NO = ? AND LOT_SL = ? ";
						pstmt1 = conn.prepareStatement(sql);
						pstmt1.setString(1, itemCode);
						pstmt1.setString(2, stkLocCode);
						pstmt1.setString(3, stkLotNo);
						pstmt1.setString(4, stkLotSl);
						rs1 = pstmt1.executeQuery();
						if (rs1.next())
						{
							perArtQty = rs1.getDouble(1);
							if (perArtQty == 0)
							{
								perArtQty = 1; // TO AVOID DIVIDE BY ZERO ERROR
							}
							if ("W".equals(criteria))
							{
								noArt = stockQuantity/perArtQty;
							}
							else
							{
								noArt = quantity/perArtQty;
							}

							valueXmlString.append("<no_art>").append("<![CDATA[").append(Math.round(noArt)).append("]]>").append("</no_art>\r\n");							
						}
						pstmt1.close();
						rs1.close();
					}
					valueXmlString.append("</Detail>\r\n");
				}

			}

			valueXmlString.append("</Root>\r\n");
		}//try end
		catch (SQLException sqx)
		{
			System.out.println("The SQLException occurs in WOrderIssueActEJB :(Allocate) Button :"+sqx);
			sqx.printStackTrace();
			throw new ITMException(sqx);
		}
		catch (Exception e)
		{
			System.out.println("The Exception occurs in WOrderIssueActEJB : (Allocate) Button :"+e);
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				conn.close();
				conn = null;
			}
			catch (Exception e){}
		}
		System.out.println("Manoharan 23/03/12 valueXmlString return from WOrderIssueActEJB.actionAllocate ["+valueXmlString.toString() + "]");
		return valueXmlString.toString();
	}	

	private String serializeDom(Node dom) throws ITMException {
		String retString = null;
		try {
			ByteArrayOutputStream out = new ByteArrayOutputStream();
			Transformer serializer = TransformerFactory.newInstance()
					.newTransformer();
			serializer
			.setOutputProperty(OutputKeys.OMIT_XML_DECLARATION, "yes");
			serializer.transform(new DOMSource(dom),
					new javax.xml.transform.stream.StreamResult(out));
			retString = out.toString();
			out.flush();
			out.close();
			out = null;
		} catch (Exception e) {
			System.out.println("Exception : MasterStatefulEJB : serializeDom :"
					+ e);
			throw new ITMException(e);
		}
		return retString;
	}

	private void getItemChanged(Document dom, Document dom1, String expLev, Connection conn) throws Exception 
	{
		PreparedStatement pstmt = null, pstmt1 = null;
		ResultSet rs = null, rs1 = null;
		String sql = null;
		String workOrder = null, bomCode = null, orderType = null, mfgLoc = null;
		String siteCode = null, tranId = null, tranIdReq = null, itemCodeRef = null, itemCode = null;
		String matchPotency = null, adjWith = null, unitAlt = null, tranType = null;//, stkOpt = null;
		String itemDescr = null, unit = null, lotNo = null, lotSl = null, qcReqd = null;
		String reason = null, autoQcReqd = null, lineNo = null;
		double operation = 0, quantity = 0, allocQty = 0, adjQty = 0, convQtyAlt = 0, qtyAlt = 0, returnQty = 0;
		double billQty = 0, woQty = 0, rcpQty = 0, issuedQty = 0, qtyReq = 0, totQtyIss = 0;
		int cnt=0;

		try 
		{
			GenericUtility genericUtility = GenericUtility.getInstance();
			MfgCommon mfgCommon = new MfgCommon();
			workOrder = genericUtility.getColumnValue("work_order", dom1);
			sql = "SELECT BOM_CODE, ORDER_TYPE FROM WORKORDER WHERE WORK_ORDER = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, workOrder);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				bomCode = rs.getString(1);
				orderType = rs.getString(2);
			}
			pstmt.close();
			rs.close();

			if ("T".equals(orderType))
			{
				mfgLoc = mfgCommon.getEnvMfg("999999", "NON_STOCK", conn);
				setValueInDom(dom1, "loc_code", mfgLoc);
				setValueInDom(dom1, "lot_sl", "1S");
				setValueInDom(dom1, "no_art", "1");
			}
			else
			{
				setValueInDom(dom1, "loc_code", "");
			}

			operation = Double.parseDouble(genericUtility.getColumnValue("operation", dom1));
			siteCode = genericUtility.getColumnValue("site_code", dom1);
			tranId = genericUtility.getColumnValue("tran_id", dom1);
			tranIdReq = genericUtility.getColumnValue("tran_id__req", dom1);

			sql = " SELECT ITEM_CODE__REF, ITEM_CODE, QUANTITY,	ALLOC_QTY, MATCH_POTENCY, POTENCY_ADJ, "
					+ " ADJ_POTENCY,	UNIT__ALT, CONV__QTY__ALT, QUANTITY__ALT FROM WORKORDER_BILL " 
					+ " WHERE WORK_ORDER = ? AND OPERATION  = ? AND EXP_LEV = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, workOrder);
			pstmt.setDouble(2, operation);
			pstmt.setString(3, expLev);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				itemCodeRef = rs.getString(1);
				itemCode = rs.getString(2);
				quantity = rs.getDouble(3);
				allocQty = rs.getDouble(4);
				matchPotency = rs.getString(5);
				adjQty = rs.getDouble(6);
				adjWith = rs.getString(7);
				unitAlt = rs.getString(8);
				convQtyAlt = rs.getDouble(9);
				qtyAlt = rs.getDouble(10);
			}
			pstmt.close();
			rs.close();

			tranType = genericUtility.getColumnValue("tran_type", dom1);

			sql = "SELECT DESCR, UNIT FROM ITEM WHERE  ITEM_CODE = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, itemCode);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				itemDescr = rs.getString(1);
				unit = rs.getString(2);
			}
			pstmt.close();
			rs.close();

			setValueInDom(dom, "exp_lev", expLev);
			setValueInDom(dom, "item_code", itemCode);
			setValueInDom(dom, "item_descr", itemDescr);

			if ("R".equals(tranType))
			{
				//				previous return quantity to consider also.
				quantity = getIssueReturnQty(workOrder, expLev, itemCode, itemCodeRef, tranType, operation, bomCode, conn);
				returnQty = getIssueReturnQty(workOrder, expLev, itemCode, itemCodeRef, tranType, operation, bomCode, conn);

				//				RETURN QTY MAY BE IN -VE HENCE ABS IT BEFORE DEDUCTING FROM ISSUE
				quantity = quantity - Math.abs(returnQty);

				//				QUANTITY TO BE SET BASED ON PRODUCTION ALREADY DONE AND BALANCE LEFT
				sql = " SELECT QUANTITY + CASE WHEN POTENCY_ADJ IS NULL THEN 0 ELSE POTENCY_ADJ END FROM WORKORDER_BILL " 
						+ " WHERE WORK_ORDER = ? AND  OPERATION = ? AND EXP_LEV = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, workOrder);
				pstmt.setDouble(2, operation);
				pstmt.setString(3, expLev);

				rs = pstmt.executeQuery();
				if (rs.next())
				{
					billQty = rs.getDouble(1);
				}
				pstmt.close();
				rs.close();

				sql = "SELECT CURRENT_BATCH_QTY FROM WORKORDER WHERE WORK_ORDER = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, workOrder);
				rs = pstmt.executeQuery();
				if (rs.next())
				{
					woQty = rs.getDouble(1);
				}
				pstmt.close();
				rs.close();

				sql = "SELECT SUM(QUANTITY) FROM WORKORDER_RECEIPT WHERE WORK_ORDER = ? AND TRAN_TYPE = 'F'";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, workOrder);
				rs = pstmt.executeQuery();
				if (rs.next())
				{
					rcpQty = rs.getDouble(1);
				}
				pstmt.close();
				rs.close();

				if (rcpQty >= woQty)
				{
					quantity = 0;
				}
				else if (rcpQty < woQty)
				{
					quantity = (billQty/woQty * (woQty - rcpQty)) - Math.abs(returnQty);
				}

				setValueInDom(dom, "quantity", Double.toString(quantity*(-1)));
				setValueInDom(dom, "potency_adj", "0");
			}
			else
			{
				//				THE FUNCTION FOR PENDING QUANTITY IS RETURNING THE PENDING QUANTITY 
				//				WITHOUT CONSIDERING THE CURRENT TRANSACTION QUANTITY
				sql = " SELECT SUM(B.QUANTITY) FROM WORKORDER_ISS A, WORKORDER_ISSDET B " 
						+ " WHERE A.TRAN_ID = B.TRAN_ID AND A.TRAN_ID = ? AND A.OPERATION = ? AND B.EXP_LEV = ? "
						+ " AND B.ITEM_CODE = ?" ;
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, tranId);
				pstmt.setDouble(2, operation);
				pstmt.setString(3, expLev);
				pstmt.setString(4, itemCode);
				rs = pstmt.executeQuery();
				if (rs.next())
				{
					issuedQty = rs.getDouble(1);
				}
				pstmt.close();
				rs.close();

				quantity = getPendingQty(workOrder, operation, expLev, conn);

				if ("F".equals(tranType))
				{
					setValueInDom(dom, "quantity", Double.toString(quantity));
					setValueInDom(dom, "unit__alt", unitAlt);
					setValueInDom(dom, "conv__qty__alt", Double.toString(convQtyAlt));
					setValueInDom(dom, "quantity__alt", Double.toString(qtyAlt));
				}
				else
				{
					setValueInDom(dom, "quantity", Double.toString(quantity));
				}

				setValueInDom(dom, "match_potency", matchPotency);
				setValueInDom(dom, "potency_adj", Double.toString(adjQty));
				setValueInDom(dom, "adj_potency", adjWith);
			}

			setValueInDom(dom1, "unit", unit);

			if ("R".equals(tranType))
			{
				sql = " SELECT COUNT(*) FROM WORKORDER_ISS A,WORKORDER_ISSDET B " 
						+ " WHERE A.TRAN_ID = B.TRAN_ID AND A.WORK_ORDER = ?  "  
						+ " AND A.TRAN_TYPE IN ('I','O','D','F') AND B.EXP_LEV = ? AND A.STATUS <> 'X'";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, workOrder);
				pstmt.setString(2, expLev);
				rs = pstmt.executeQuery();
				if (rs.next())
				{
					cnt = rs.getInt(1);
				}
				pstmt.close();
				rs.close();

				if (cnt == 1)
				{
					sql = " SELECT B.LOT_NO,B.LOT_SL FROM WORKORDER_ISS A,WORKORDER_ISSDET B " 
							+ " WHERE A.TRAN_ID = B.TRAN_ID AND  A.WORK_ORDER = ? AND B.EXP_LEV = ?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, workOrder);
					pstmt.setString(2, expLev);
					rs = pstmt.executeQuery();
					if (rs.next())
					{
						lotNo = rs.getString(1);
						lotSl = rs.getString(2);
					}
					pstmt.close();
					rs.close();

					setValueInDom(dom, "lot_no", lotNo);
					setValueInDom(dom, "lot_sl", lotSl);
				}
			}

			//			IF W/O RETURN AND ITEM.QC_REQD = 'Y' --  DON'T ALLOW TO CHANGE QC_REQD 
			//			IF WORK ORDER TYPE IS PACKING SET QC_REQD = 'N' EVEN THOUGH RETURN
			itemCode = genericUtility.getColumnValue("item_code", dom1);
			if ("R".equals(tranType) || "P".equals(orderType))
			{
				setValueInDom(dom, "qc_reqd", "N");
			}
			else
			{
				//				IF AUTO_REQC IS 'YES' IN SITEITEM THEN SET QC_REQD = 'YES'
				sql = "SELECT CASE WHEN AUTO_REQC IS NULL THEN 'N' ELSE AUTO_REQC END 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())
				{
					qcReqd = rs.getString(1);
				}
				else
				{
					sql = "SELECT CASE WHEN QC_REQD IS NULL THEN 'N' ELSE QC_REQD END FROM ITEM WHERE ITEM_CODE = ?";
					pstmt1 = conn.prepareStatement(sql);
					pstmt1.setString(1, itemCode);
					rs1 = pstmt1.executeQuery();
					if (rs1.next())
					{
						qcReqd = rs.getString(1);
					}
					pstmt1.close();
					rs1.close();
				}
				pstmt.close();
				rs.close();
				setValueInDom(dom, "qc_reqd", qcReqd);
			}

			//			IF DEVIATED ISSUE IS FROM REQUEST THEN SELECT THE QUANTITY.
			if (tranIdReq == null) 
			{
				tranIdReq = "";
			}
			if (("D".equals(tranType) || "O".equals(tranType)) && tranIdReq.trim().length() > 0)
			{
				sql = " SELECT QUANTITY, ITEM_CODE, UNIT, REAS_CODE FROM WORKORDER_ISSDET_REQ " 
						+ " WHERE TRAN_ID = ? AND EXP_LEV = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, tranIdReq);
				pstmt.setString(2, expLev);
				rs = pstmt.executeQuery();
				if (rs.next())
				{
					qtyReq = rs.getDouble(1);
					itemCode = rs.getString(2);
					unit = rs.getString(3);
					reason = rs.getString(4);
				}
				pstmt.close();
				rs.close();

				//				DEDUCT THE ISSUED QUANTITY FOR THIS REQ
				sql = " SELECT SUM(B.QUANTITY) FROM WORKORDER_ISS A, WORKORDER_ISSDET B " 
						+ " WHERE (A.TRAN_ID = B.TRAN_ID ) AND  (A.CONFIRMED = 'Y' ) AND  (A.TRAN_TYPE = ?) " 
						+ " AND  (A.TRAN_ID__REQ = ?) AND (B.ITEM_CODE = ?) AND (B.EXP_LEV = ?)";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, tranType);
				pstmt.setString(2, tranIdReq);
				pstmt.setString(3, itemCode);
				pstmt.setString(4, expLev);
				rs = pstmt.executeQuery();
				if (rs.next())
				{
					totQtyIss = rs.getDouble(1);
				}
				pstmt.close();
				rs.close();
				System.out.println("Manohar Deviated/Ondemand qtyReq [" + qtyReq + "] totQtyIss [" + totQtyIss + "] reason [" + reason + "]");
				setValueInDom(dom, "quantity", Double.toString(qtyReq - totQtyIss));

				//				SET THE ITEM FROM REQUEST
				if (itemCode != null && itemCode.trim().length() > 0)
				{
					setValueInDom(dom, "item_code", itemCode);
					setValueInDom(dom1, "unit", unit);
					//					REASON CODE SHUD COME FROM ISSUE REQUEST IN CASE OF DEVI AND ONDEMAND ISSUE
					setValueInDom(dom, "reas_code", reason);
				}
				if (reason != null &&reason.trim().length() > 0)
				{
					setValueInDom(dom, "reas_code", reason);
				}
			}
			//ITEMCHANGE FOR ITEM_CODE BEGINS
			itemCode = genericUtility.getColumnValue("item_code", dom);
			orderType = genericUtility.getColumnValue("order_type", dom1).trim();

			sql = "SELECT DESCR,UNIT FROM ITEM WHERE ITEM_CODE = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, itemCode);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				itemDescr = rs.getString(1);
				unit = rs.getString(2);
			}
			pstmt.close();
			rs.close();

			setValueInDom(dom, "item_descr", itemDescr);
			setValueInDom(dom, "unit", unit);

			//				IF WORK ORDER TYPE IS PACKING SET QC_REQD = 'N' EVEN THOUGH RETURN
			sql = " SELECT CASE WHEN AUTO_REQC IS NULL THEN 'N' ELSE AUTO_REQC END, "
					+ " CASE WHEN QC_REQD IS NULL THEN 'N' ELSE QC_REQD END 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())
			{
				autoQcReqd = rs.getString(1);
				qcReqd = rs.getString(2);
			}
			pstmt.close();
			rs.close();

			if (autoQcReqd != null && autoQcReqd.equalsIgnoreCase("Y"))
			{
				qcReqd = "Y";
			}
			setValueInDom(dom, "qc_reqd", qcReqd);

			if ((tranType != null && !tranType.equalsIgnoreCase("R")) || (orderType != null && orderType.equalsIgnoreCase("P")))
			{
				setValueInDom(dom, "qc_reqd", "N");
			}
			else
			{
				//					IF AUTO_REQC IS 'YES' IN SITEITEM THEN SET QC_REQD = 'YES'
				sql = "SELECT CASE WHEN AUTO_REQC IS NULL THEN 'N' ELSE AUTO_REQC END " 
						+ " 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())
				{
					qcReqd = rs.getString(1);
				}
				else
				{
					sql = "SELECT CASE WHEN QC_REQD IS NULL THEN 'N' ELSE QC_REQD END" 
							+ " FROM ITEM WHERE ITEM_CODE = ?";
					pstmt1 = conn.prepareStatement(sql);
					pstmt1.setString(1, itemCode);
					rs1 = pstmt1.executeQuery();
					if (rs1.next())
					{
						qcReqd = rs.getString(1);
					}
					pstmt1.close();
					rs1.close();
				}
				pstmt.close();
				rs.close();
				setValueInDom(dom, "qc_reqd", qcReqd);
			}
			if ("R".equals(tranType))
			{
				lineNo = genericUtility.getColumnValue("line_no", dom);
				sql = " SELECT SUM(B.QUANTITY) FROM WORKORDER_ISS A, WORKORDER_ISSDET B "
						+ " WHERE A.TRAN_ID = B.TRAN_ID AND A.WORK_ORDER = ? AND A.OPERATION = ? " 
						+ " AND B.EXP_LEV = ? AND B.ITEM_CODE = ? " 
						+ " AND ((B.TRAN_ID <> ?) OR (B.TRAN_ID = ? AND B.LINE_NO <> ?))";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, workOrder);
				pstmt.setDouble(2, operation);
				pstmt.setString(3, expLev);
				pstmt.setString(4, itemCode);
				pstmt.setString(5, tranId);
				pstmt.setString(6, tranId);
				pstmt.setString(7, lineNo);
				rs = pstmt.executeQuery();
				if (rs.next())
				{
					issuedQty = rs.getDouble(1);
				}
				pstmt.close();
				rs.close();

				setValueInDom(dom, "quantity", Double.toString(issuedQty));
			}
		} 
		catch (Exception e) {
			System.out.println("Exception [WOrderIssueActEJBAct][getItemChanged] :"	+ e);
			e.printStackTrace();
			throw e;
		}
	}

	private void setValueInDom(Document dom,String field,String value) throws Exception
	{
		NodeList nodeList = null;
		Node childNode = null;
		try
		{
			System.out.println("==================Dom b4 setting Value================");
			System.out.println("Dom b4 setting Value : "+serializeDom(dom));
			System.out.println("setting Value For Field : ["+field+"] with value ["+value+"]");
			nodeList = dom.getElementsByTagName(field);			
			if (nodeList != null)
			{
				childNode = nodeList.item(0);
				if (childNode != null && value != null)
				{
					if (childNode.getFirstChild() != null)
					{
						childNode.getFirstChild().setNodeValue(value);
						System.out.println("After setting Value : childNode.getFirstChild() != null : "+ childNode.getFirstChild().getNodeValue());
					}
					else
					{
						childNode.appendChild(dom.createCDATASection(value));
						System.out.println("After setting Value : childNode.getFirstChild() == null : "+ childNode.getFirstChild().getNodeValue());
					}
				}
			}
			System.out.println("==================Dom After setting Value================");
			System.out.println("Dom After setting Value : "+serializeDom(dom));
		}
		catch(Exception e)
		{
			System.out.println("Exception "+e);
			e.printStackTrace();
			throw e;
		}
	}

	private double getIssueReturnQty(String workOrder, String expLev, String itemCode, String itemCodeRef, String tranType, double operation, String bomCode, Connection conn)
	{
		//		GETS AND RETURNS THE ISSUED/RETURNED QUANTITY FOR THE 
		//		WORK ORDER / EXP_LEV IN THE PROPORTION OF THE MAIN ITEM
		String sql = "", iCode = null, tranId = null;
		double qty = 0, issueReturnQty = 0;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try
		{
			if (tranId == null)
			{
				tranId = "@";
			}

			if ("D".equals(tranType))
			{
				sql = " SELECT A.ITEM_CODE, SUM(CASE WHEN A.QUANTITY IS NULL THEN 0 ELSE A.QUANTITY END + "
						+ " CASE WHEN A.POTENCY_ADJ IS NULL THEN 0 ELSE A.POTENCY_ADJ END) "
						+ " FROM WORKORDER_ISSDET A, WORKORDER_ISS B WHERE ( B.TRAN_ID = A.TRAN_ID ) " 
						+ " AND ( ( RTRIM(LTRIM(A.EXP_LEV)) = ? ) AND ( B.TRAN_ID <> ? ) AND ( B.TRAN_TYPE <> 'D' ) " 
						+ " AND ( B.STATUS <> 'X' ) AND ( B.WORK_ORDER = ? ) )GROUP BY A.ITEM_CODE";
			}
			else
			{
				sql = " SELECT A.ITEM_CODE, SUM(CASE WHEN A.QUANTITY IS NULL THEN 0 ELSE A.QUANTITY END + "
						+ " CASE WHEN A.POTENCY_ADJ IS NULL THEN 0 ELSE A.POTENCY_ADJ END) "
						+ " FROM WORKORDER_ISSDET A, WORKORDER_ISS B WHERE ( B.TRAN_ID = A.TRAN_ID ) " 
						+ " AND ( ( RTRIM(LTRIM(A.EXP_LEV)) = ? ) AND ( B.TRAN_ID <> ? ) AND ( B.TRAN_TYPE <> 'R' ) " 
						+ " AND ( B.STATUS <> 'X' ) AND ( B.WORK_ORDER = ? ) )GROUP BY A.ITEM_CODE";
			}
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, expLev);
			pstmt.setString(2, tranId);
			pstmt.setString(3, workOrder);
			rs = pstmt.executeQuery();
			while (rs.next())
			{
				iCode =rs.getString(1);
				qty = rs.getDouble(2);
				if (qty == 0)
				{
					continue;
				}
				qty = Math.abs(qty);
				if (iCode.trim() != itemCode.trim())
				{
					qty = getProportionateQty(bomCode, operation, itemCode, itemCodeRef, iCode, qty, conn);
				}
				issueReturnQty += qty;
			}			
		}
		catch (Exception e)
		{
			System.out.println("Exception in getIssueReturnQty ::: "+e);
			e.printStackTrace();
		}
		return issueReturnQty;
	}

	private double getPendingQty(String workOrder, double operation, String expLev, Connection conn)
	{
		String sql = "";
		String bomCode = null, itemCodeRef = null, itemCode = null;
		double qty = 0,pendingQty = 0, issuedQty = 0, returnedQty = 0, issuedQtyDev = 0;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try
		{
			sql = "SELECT BOM_CODE FROM WORKORDER WHERE WORK_ORDER = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, workOrder);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				bomCode = rs.getString(1);
			}
			pstmt.close();
			rs.close();
			sql = " SELECT ITEM_CODE__REF, ITEM_CODE, (CASE WHEN QUANTITY IS NULL THEN 0 ELSE QUANTITY END + "
					+ " CASE WHEN POTENCY_ADJ IS NULL THEN 0 ELSE POTENCY_ADJ END) FROM WORKORDER_BILL "
					+ " WHERE WORK_ORDER = ? AND OPERATION = ? AND RTRIM(LTRIM(EXP_LEV)) = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, workOrder);
			pstmt.setDouble(2, operation);
			pstmt.setString(3, expLev);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				itemCodeRef = rs.getString(1);
				itemCode = rs.getString(2);
				qty = rs.getDouble(3);
			}
			pstmt.close();
			rs.close();

			issuedQty = getIssueReturnQty(workOrder, expLev, itemCode, itemCodeRef, "I", operation, bomCode, conn);
			returnedQty = getIssueReturnQty(workOrder, expLev, itemCode, itemCodeRef, "I", operation, bomCode, conn);
			issuedQtyDev = getIssueReturnQty(workOrder, expLev, itemCode, itemCodeRef, "I", operation, bomCode, conn);

			pendingQty = qty + issuedQtyDev - issuedQty + returnedQty;

		}
		catch(Exception e)
		{
			System.out.println("Exception in getPendingQty ::: "+e);
			e.printStackTrace();
		}
		return pendingQty;
	}

	private double getProportionateQty(String bomCode,double operation,String itemCode,String itemCodeRef,String altItem,double balanceQtyAlt, Connection conn)
	{
		double proportateQty = 0, qtyPerBom = 0, qtyPerAlt = 0;
		String sql = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try
		{
			sql = " SELECT QTY_PER FROM BOMDET WHERE BOM_CODE = ? AND OPERATION = ? " 
					+ " AND ITEM_CODE = ? AND ITEM_REF = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, bomCode);
			pstmt.setDouble(2, operation);
			pstmt.setString(3, itemCode);
			pstmt.setString(4, itemCodeRef);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				qtyPerBom = rs.getDouble(1);
			}
			pstmt.close();
			rs.close();

			sql = " SELECT QUANTITY FROM BOM_ALT WHERE BOM_CODE = ? AND ITEM_CODE = ? AND ITEM_REF = ? " 
					+ " AND ITEM_CODE__ALT = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, bomCode);
			pstmt.setString(2, itemCode);
			pstmt.setString(3, itemCodeRef);
			pstmt.setString(4, altItem);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				qtyPerAlt = rs.getDouble(1);
			}
			pstmt.close();
			rs.close();

			if (Math.abs(qtyPerAlt) > 0 && Math.abs(balanceQtyAlt) > 0)
			{
				proportateQty = qtyPerBom / qtyPerAlt * balanceQtyAlt;
			}
			else
			{
				proportateQty = balanceQtyAlt;
			}
		}
		catch (Exception e)
		{
			System.out.println("Exception in  getProportionateQty ::: "+e);
			e.printStackTrace();
		}
		return proportateQty;
	}
	private String setNoArt(String siteCode, String itemCode, String locCode, String lotNo, String lotSl, String expLev, double quantity, String tranType, double gWeight, Connection conn)
	{
		//		 WEIGHT TO BE SET IN DETAIL --  1. GET FROM PACKING DETAILS  --	 2. IF NO PACKING GET FROM STOCK
		long stkNoArt = 0;
		double inputQty = 0, stockQty = 0, stkGrossWt = 0, stkNetWt = 0, stkTareWt = 0, grossWeight = 0;
		double tareWeight = 0, netWeight = 0, noArt = 0;
		double tareWtPerArt = 0, qtyPerArt = 0, grossWtPerArt = 0;
		String issCriteria = null;
		PreparedStatement pstmt = null, pstmt1 = null;
		ResultSet rs = null, rs1 = null;
		String sql = "";
		StringBuffer retString = new StringBuffer();

		try
		{
			sql = "SELECT CASE WHEN ISS_CRITERIA IS NULL THEN 'I' ELSE ISS_CRITERIA END FROM ITEM WHERE ITEM_CODE = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, itemCode);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				issCriteria = rs.getString(1);
			}
			pstmt.close();
			rs.close();
			//			BASED ON NEW COLUMN VALUES IN STOCK TO BE CALCULATED
			if (!tranType.equalsIgnoreCase("R"))
			{
				sql = " SELECT QUANTITY, GROSS_WEIGHT, NET_WEIGHT, TARE_WEIGHT, NO_ART, 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);
				System.out.println("sql ::: " + sql);
				System.out.println("Setting params ---- ");
				System.out.println("itemCode :: " + itemCode + " siteCode :: " + siteCode);
				System.out.println("locCode :: " + locCode + " lotNo :: " + lotNo);
				System.out.println("lotSl :: " + lotSl);

				rs = pstmt.executeQuery();
				if (rs.next())
				{
					stockQty = rs.getDouble(1);
					stkGrossWt = rs.getDouble(2);
					stkNetWt = rs.getDouble(3);
					stkTareWt = rs.getDouble(4);
					stkNoArt = rs.getLong(5);
					qtyPerArt = rs.getDouble(6);
					grossWtPerArt = rs.getDouble(7);
					tareWtPerArt = rs.getDouble(8);
				}
				pstmt.close();
				rs.close();
				System.out.println("stockQty :: " + stockQty + " stkGrossWt :: " + stkGrossWt);
				System.out.println("stkNetWt :: " + stkNetWt + " stkTareWt :: " + stkTareWt);
				System.out.println("qtyPerArt :: " + qtyPerArt + " grossWtPerArt :: " + grossWtPerArt);
				System.out.println("tareWtPerArt :: " + tareWtPerArt);

				inputQty = quantity;
				System.out.println("inputQty if trantype not \"R\" = " + inputQty);

				if (issCriteria.equalsIgnoreCase("W"))
				{
					noArt = stkNoArt;
					tareWeight = stkTareWt;
					if (stockQty > 0)
					{
						netWeight = stkNetWt / stockQty * inputQty;
					}
					grossWeight =  netWeight + tareWeight;
				}
				else
				{
					if (qtyPerArt == 0)
					{
						qtyPerArt = stockQty / stkNoArt;
					}
					else
					{
						noArt = inputQty/qtyPerArt;
						tareWeight = tareWtPerArt*noArt;
						netWeight = (grossWtPerArt - tareWtPerArt) * Math.ceil(noArt);
						grossWeight = netWeight + tareWeight;
						noArt = Math.ceil(noArt);
						if (noArt <= 0) noArt = 1;
					}
				}
				System.out.println("inputQty if trantype not \"R\" = " + inputQty);
			}
			else
			{
				//				GET THE WEIGHT/ARTICLE DETAILS FROM THE CORRESPONDING ISSUE	AND CALCULATE ACCORDINGLY
				sql = " SELECT A.LOC_CODE FROM WORKORDER_ISSDET A, WORKORDER_ISS B " 
						+ " WHERE A.TRAN_ID = B.TRAN_ID AND B.SITE_CODE = ? AND A.ITEM_CODE = ? AND A.LOT_NO = ? " 
						+ " AND A.LOT_SL = ? AND A.EXP_LEV = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, siteCode);
				pstmt.setString(2, itemCode);
				pstmt.setString(3, lotNo);
				pstmt.setString(4, lotSl);
				pstmt.setString(5, expLev);
				System.out.println("sql ::: " + sql);
				System.out.println("Setting params ---- ");
				System.out.println("siteCode :: " + siteCode + "itemCode :: " + itemCode );
				System.out.println("lotNo :: " + lotNo + "lotSl :: " + lotSl );
				System.out.println("expLev :: " + expLev);

				rs = pstmt.executeQuery();
				if (rs.next())
				{
					locCode = rs.getString(1);
				}
				else
				{
					sql = " SELECT A.LOC_CODE FROM WORKORDER_ISSDET A, WORKORDER_ISS B " 
							+ " WHERE A.TRAN_ID = B.TRAN_ID AND B.SITE_CODE = ? AND A.ITEM_CODE = ? " 
							+ " AND A.LOT_NO = ? AND A.EXP_LEV = ?";
					pstmt1 = conn.prepareStatement(sql);
					pstmt1.setString(1, siteCode);
					pstmt1.setString(2, itemCode);
					pstmt1.setString(3, lotNo);
					pstmt1.setString(4, lotSl);
					pstmt1.setString(5, expLev);
					System.out.println("sql ::: " + sql);
					System.out.println("Setting params ---- ");
					System.out.println("siteCode :: " + siteCode + "itemCode :: " + itemCode );
					System.out.println("lotNo :: " + lotNo + "lotSl :: " + lotSl );
					System.out.println("expLev :: " + expLev);
					rs1 = pstmt1.executeQuery();
					if (rs1.next())
					{
						locCode = rs1.getString(1);
					}
				}
				pstmt.close();
				rs.close();

				sql = " SELECT QUANTITY, GROSS_WEIGHT, NET_WEIGHT, TARE_WEIGHT, NO_ART, 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);
				System.out.println("sql ::: " + sql);
				System.out.println("Setting params ---- ");
				System.out.println("itemCode :: " + itemCode + " siteCode :: " + siteCode);
				System.out.println("locCode :: " + locCode + " lotNo :: " + lotNo);
				System.out.println("lotSl :: " + lotSl);

				rs = pstmt.executeQuery();
				if (rs.next())
				{
					stockQty = rs.getDouble(1);
					stkGrossWt = rs.getDouble(2);
					stkNetWt = rs.getDouble(3);
					stkTareWt = rs.getDouble(4);
					stkNoArt = rs.getLong(5);
					qtyPerArt = rs.getDouble(6);
					grossWtPerArt = rs.getDouble(7);
					tareWtPerArt = rs.getDouble(8);
				}
				System.out.println("stockQty :: " + stockQty + " stkGrossWt :: " + stkGrossWt);
				System.out.println("stkNetWt :: " + stkNetWt + " stkTareWt :: " + stkTareWt);
				System.out.println("qtyPerArt :: " + qtyPerArt + " grossWtPerArt :: " + grossWtPerArt);
				System.out.println("tareWtPerArt :: " + tareWtPerArt);

				pstmt.close();
				rs.close();

				if (issCriteria.equalsIgnoreCase("W"))
				{
					grossWeight = gWeight; 
					noArt = stkNoArt;
					tareWeight = noArt * tareWtPerArt;
					netWeight = grossWeight - tareWeight;
					inputQty = -netWeight; 
					System.out.println("inputQty if trantype is \"R\" & issueCriteria W = " + inputQty);
				}
				else
				{
					if (qtyPerArt == 0)
					{
						if (stkNoArt == 0) stkNoArt = 1;
						qtyPerArt = stockQty / stkNoArt;
					}
					if (qtyPerArt > 0)
					{
						noArt = Math.ceil(Math.abs(inputQty)/qtyPerArt);
						tareWeight = tareWtPerArt * noArt;
						netWeight = (grossWtPerArt - tareWtPerArt) * noArt;
						grossWeight = netWeight + tareWeight;
					}
				}
				System.out.println("inputQty if trantype is \"R\" = " + inputQty);
			}
			retString.append(grossWeight + " :");
			retString.append(tareWeight + " :");
			retString.append(netWeight + " :");
			retString.append(noArt + " :");
			retString.append(inputQty);
		}
		catch(Exception e)
		{
			System.out.println("Exception in setNoArt ::: " + e.getMessage());
			e.printStackTrace();
		}
		System.out.println("retString ::: " + retString);
		return retString.toString();
	}
	// added by chandni shah - 21-Dec-2011

	public double getReqDecimal(double actVal, int prec)
	{
		String fmtStr = "############0";
		String strValue = null;
		double retVal = 0;
		if (prec > 0)
		{
			fmtStr = fmtStr + "." + "000000000".substring(0, prec);
		}
		DecimalFormat decFormat = new DecimalFormat(fmtStr);
		retVal = Double.parseDouble(decFormat.format(actVal));
		if (Math.abs(retVal) == 0)
		{
			retVal = 0;
		}
		return retVal;
	}
	@Override
	public ArrayList getDetails(String woCode) throws ITMException
	{
		PreparedStatement pstmt = null, pstmt1 = null;
		ResultSet rs = null;
		Connection conn = null;
		woStockDtlParams thisBean = null;
		ArrayList stkWoList = new ArrayList();
		String sql = null;
		String trackShelfLife = null;
		String itemCode = null,siteCode = null;
		Timestamp currDate =null;
		double qtyPer = 0, minQty = 0, woQty = 0, batchQty = 0;
		String reqType = "";
		try
		{
			ConnDriver connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM"); 
			conn.setAutoCommit(false);
			connDriver = null;

			sql = "SELECT FN_SYSDATE() FROM DUAL";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				currDate = rs.getTimestamp(1);
			}
			System.out.println("Current Date::::::"+currDate);
			rs.close();
			pstmt.close();

			if(woCode!=null)
			{
				sql =" select b.item_code,b.descr,a.site_code,c.req_type, sum(c.qty_per) qty_per, "
						+ " sum(c.min_qty), a.quantity,d.batch_qty from workorder a ,item b, bomdet c, bom d " 
						+" where  c.item_code = b.item_code " 
						+ " and c.bom_code = a.bom_code "
						+ " and d.bom_code = a.bom_code "
						+" and  a.work_order = ? "
						+ " group by b.item_code,b.descr,a.site_code,c.req_type, a.quantity,d.batch_qty "; 

				System.out.println("MainQuery:::"+sql);
				pstmt1 = conn.prepareStatement(sql);
				pstmt1.setString(1,woCode);
				rs = pstmt1.executeQuery();  

				while(rs.next())
				{
					itemCode = rs.getString(1);

					thisBean = new woStockDtlParams();
					thisBean.itemCode = rs.getString(1);
					thisBean.descr = rs.getString(2);
					siteCode = rs.getString(3);
					reqType = rs.getString(4);
					thisBean.qtyPer = rs.getDouble(5);
					minQty = rs.getDouble(6);
					woQty = rs.getDouble(7);
					batchQty = rs.getDouble(8);

					if ("S".equals(reqType))
					{
						thisBean.qtyPer = woQty / batchQty * rs.getDouble(5);
					}
					else if ("P".equals(reqType))
					{
						thisBean.qtyPer = rs.getDouble(5) / batchQty * woQty;
					}
					if (thisBean.qtyPer < minQty)
					{
						thisBean.qtyPer = minQty;
					}
					thisBean.qtyPer = getReqDecimal(thisBean.qtyPer, 3);
					pstmt = conn.prepareStatement("select nvl(track_shelf_life,'N') from item where item_code = ?") ;
					pstmt.setString(1,itemCode);
					ResultSet rs1 = pstmt.executeQuery();
					if(rs1.next())
					{
						trackShelfLife = rs1.getString(1);
					}
					rs1.close();
					StringBuffer sb = new StringBuffer();
					sb.append("select nvl(sum(a.quantity - a.alloc_qty),0), nvl(sum(a.alloc_qty),0) from stock a, invstat b "
							+" where a.inv_stat = b.inv_stat  and a.item_code = ? and a.site_code in (select site_code from site where geo_loc in ( select geo_loc from site where site_code  = ? )) and b.available = 'Y'  and b.stat_type <> 'S'  and b.plan_prod = 'Y' and (a.quantity - nvl(a.alloc_qty,0))  > 0 " );
					if(trackShelfLife != null && trackShelfLife.equalsIgnoreCase("Y"))
					{
						sb.append("and ( a.exp_date is null or  a.exp_date >?)");
					}
					pstmt = conn.prepareStatement(sb.toString());
					pstmt.setString(1,itemCode);
					pstmt.setString(2,siteCode);
					if(trackShelfLife != null && trackShelfLife.equalsIgnoreCase("Y"))
					{
						pstmt.setTimestamp(3,currDate);
					}	
					rs1 = pstmt.executeQuery();
					while(rs1.next())  
					{ 
						thisBean.stkQty = rs1.getDouble(1);
						thisBean.allockQty = rs1.getString(2);
					}
					rs1.close();
					rs1 = null;

					StringBuffer sbn = new StringBuffer();
					sbn.append(" select nvl(sum(a.quantity - a.alloc_qty),0), nvl(sum(a.alloc_qty),0) from stock a, invstat b "
							+" where a.inv_stat = b.inv_stat  and a.item_code = ? and a.site_code in (select site_code from site where geo_loc in ( select geo_loc from site where site_code  = ? )) and b.available = 'N'  and b.stat_type <> 'S'  and (a.quantity - nvl(a.alloc_qty,0))  > 0 " );
					if(trackShelfLife.equalsIgnoreCase("Y"))
					{
						sbn.append("and ( a.exp_date is null or  a.exp_date >?)");
					}
					pstmt = conn.prepareStatement(sbn.toString());
					pstmt.setString(1,itemCode);
					pstmt.setString(2,siteCode);

					if(trackShelfLife.equalsIgnoreCase("Y"))
					{
						pstmt.setTimestamp(3,currDate);
					}	
					rs1 = pstmt.executeQuery();
					while(rs1.next())  
					{
						thisBean.nonStkQty = rs1.getDouble(1);
						thisBean.allockQty = rs1.getString(2); 
					}
					rs1.close();
					rs1 = null;

					stkWoList.add(thisBean);
				}
				pstmt1.close();
				pstmt1 = null;
				rs.close();
				rs = null;
			}
		}
		catch (SQLException se)
		{
			System.out.println("Exception : BOMStockEJB : actionStock " +se.getMessage());
			se.printStackTrace();
			throw new ITMException(se);
		}
		catch(Exception e)
		{
			System.out.println("Exception : BOMStockEJB : actionStock :(Document dom)" +e.getMessage());
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try{
				if (conn != null)
				{
					conn.close();
					conn = null;
				}
				if (pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
			}catch(Exception e){}
		}
		return stkWoList;
	}

	class woStockDtlParams implements Serializable
	{
		HashMap BOMStockMap = new HashMap();
		String woCode=null;
		String trackShelfLife = null;
		String itemCode = null;
		String allockQty = null;
		String descr = null;
		double stkQty = 0.0;
		double nonStkQty = 0.0;
		Timestamp currDate =null;
		double qtyPer = 0;
	}
	// Ended by chandni shah - 21-Dec-2011
}