/* 
	Developed by : TaraniseMeher
	Company : Base Information Management Pvt. Ltd
	Window Name : w_bom
	Date :18/09/2006
	Menu Reference:Master-ProductStructure-BillOfMaterial(MFG)
	Button:ViewAltBOM,Stock
	
 */

package ibase.webitm.ejb.mfg.adv;

import java.rmi.RemoteException;
import java.util.*;
import java.io.*;
import java.util.Date;
import java.sql.*;

import org.w3c.dom.*;

import javax.xml.parsers.*;
import javax.ejb.*;
import javax.naming.InitialContext;

import ibase.webitm.utility.ITMException;
import ibase.webitm.ejb.*;
//import ibase.webitm.ejb.mfg.adv.BOMStock.BOMStockDtlParams;
import ibase.webitm.utility.GenericUtility;
import ibase.ejb.*;

import javax.annotation.*;

import ibase.system.config.*;
import ibase.utility.CommonConstants;
import ibase.utility.UserInfoBean;

import java.text.SimpleDateFormat;
import java.text.DecimalFormat;

import javax.ejb.Stateless; // added for ejb3

@Stateless
// added for ejb3
public class BOMAct extends ActionHandlerEJB implements BOMActLocal,
		BOMActRemote // SessionBean
{
	GenericUtility genericUtility = null;
	CommonConstants commonConstants = new CommonConstants();

	/*
	 * @PostConstruct public void ejbCreate() //throws RemoteException//,
	 * CreateException {
	 * System.out.println("Create Method Called[BOMActEJB].................");
	 * try { } catch(Exception e) { e.printStackTrace();
	 * System.out.println("Exception in creating Connection"); } }
	 * 
	 * public void ejbRemove() { }
	 * 
	 * public void ejbActivate() { }
	 * 
	 * public void ejbPassivate() { }
	 */

	public String actionHandler() throws RemoteException, ITMException {
		System.out.println("actionHandler() Method Called....");
		return "";
	}

	public String actionHandlerTransform(String actionType, String xmlString,
			String xmlString1, String objContext, String xtraParams,
			String selDataStr) throws RemoteException, ITMException {
		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);
				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);
			}
			if (actionType.equalsIgnoreCase("ViewAltBOM")) {
				retString = viewAltBOMTransform(dom, dom1, objContext,
						xtraParams, selDataDom);
			}
			// add for CopyBomDet on 28/08/09 ReguestId MF89GIM005
			if (actionType.equalsIgnoreCase("copyBomDet")) {
				retString = actionTransform(dom, dom1, objContext, xtraParams,
						selDataDom);
			}

		} catch (Exception e) {
			System.out
					.println("Exception :Dispatch :actionHandlerTransform(String xmlString):"
							+ e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		System.out
				.println("returning String from Despatch : actionHandlerTransform"
						+ retString);
		return retString;
	}

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

		valueXmlString.append("</Root>");

		return valueXmlString.toString();
	}

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

		valueXmlString.append("</Root>");

		return valueXmlString.toString();
	}

	public String actionHandler(String actionType, String xmlString,
			String xmlString1, String objContext, String xtraParams)
			throws RemoteException, ITMException {
		System.out.println("AdjIssEJB called");
		Document dom = null;
		Document dom1 = null;
		String resString = null;
		try {
			GenericUtility genericUtility = GenericUtility.getInstance();
			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("Stock")) {
				resString = actionStock(dom, dom1, objContext, xtraParams);
			} else if (actionType.equalsIgnoreCase("ViewAltBOM")) {
				resString = actionViewAltBOM(dom, dom1, objContext, xtraParams);
			}
			// add on 28/08/09 copyBomDet
			else if (actionType.equalsIgnoreCase("Default")) {
				resString = actionDefault(dom, dom1, objContext, xtraParams);
			}

			// end of code on 28/08/09 for copyBomDet
		} catch (Exception e) {
			System.out
					.println("Exception :AdjIssEJB :actionHandler(String xmlString):"
							+ e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		System.out.println("returning from action[Stock] actionHandler"
				+ resString);
		return (resString);
	}

	// add on 28/08/09 for CopyBomDet requestId MF89GIM005
	private String actionDefault(Document dom, Document dom1,
			String objContext, String xtraParams) throws RemoteException,
			ITMException {
		GenericUtility genericUtility = GenericUtility.getInstance();
		Connection conn = null;
		StringBuffer valueXmlString = null;
		// valueXmlString = new
		// StringBuffer("<?xml version=\"1.0\" encoding=\"UTF-8\"?>  \r\n<Root>\r\n");
		valueXmlString = new StringBuffer("<?xml version='1.0' encoding='"
				+ commonConstants.ENCODING + "'?> \r\n<Root>\r\n");
		int currentFormNo = 0, cnt = 0;
		String sql = "", sqlPreBom = "", bomCodeHeader = "";
		ResultSet rs = null, rsDescr = null;
		PreparedStatement pstmt = null, pstmtDescr = null;
		try {
			String detCnt = genericUtility.getValueFromXTRA_PARAMS(xtraParams,
					"detCnt");
			String siteCodeLogin = genericUtility.getValueFromXTRA_PARAMS(
					xtraParams, "loginSiteCode");

			if (conn == null) {
				ConnDriver connDriver = new ConnDriver();
				conn = connDriver.getConnectDB("DriverITM");
				connDriver = null;
			}
			if (objContext != null && objContext.trim().length() > 0) {
				currentFormNo = Integer.parseInt(objContext);
			}
			bomCodeHeader = GenericUtility.getInstance().getColumnValue(
					"bom_code", dom1);
			sqlPreBom = "select count(*) from bomdet where bom_code = ? ";
			pstmtDescr = conn.prepareStatement(sqlPreBom);
			pstmtDescr.setString(1, bomCodeHeader);
			rsDescr = pstmtDescr.executeQuery();
			if (rsDescr.next()) {
				cnt = rsDescr.getInt(1);
			}
			rsDescr.close();
			pstmtDescr.close();
			if (cnt == 0) {
				sql = " select  bom_code,descr,item_code , "
						+ " batch_qty,unit from bom where site_code = ? order by bom_code  ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, siteCodeLogin);
				rs = pstmt.executeQuery();
				while (rs.next()) {
					valueXmlString.append("<Detail>");
					valueXmlString
							.append("<bom_code isSrvCallOnChg =\"0\" >")
							.append("<![CDATA[")
							.append(rs.getString(1) == null ? "" : rs
									.getString(1)).append("]]>")
							.append("</bom_code>");
					valueXmlString
							.append("<descr>")
							.append("<![CDATA[")
							.append(rs.getString(2) == null ? "" : rs
									.getString(2)).append("]]>")
							.append("</descr>");
					valueXmlString
							.append("<item_code>")
							.append("<![CDATA[")
							.append(rs.getString(3) == null ? "" : rs
									.getString(3)).append("]]>")
							.append("</item_code>");
					valueXmlString
							.append("<batch_qty>")
							.append("<![CDATA[")
							.append(rs.getString(4) == null ? "" : rs
									.getString(4)).append("]]>")
							.append("</batch_qty>");
					valueXmlString
							.append("<unit>")
							.append("<![CDATA[")
							.append(rs.getString(5) == null ? "" : rs
									.getString(5)).append("]]>")
							.append("</unit>");
					valueXmlString.append("</Detail>\r\n");
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
			}

			valueXmlString.append("</Root>\r\n");
		}// tryBase1 greyd
		catch (SQLException sqx) {
			// System.out.println("The Exception caught from CopyBomDetEJB :"+sqx);
			throw new ITMException(sqx);
		} catch (Exception e) {
			// System.out.println("The Exception caught from CopyBomDetEJB :"+e);
			throw new ITMException(e);
		} finally {
			try {
				conn.close();
				conn = null;

			} catch (Exception e) {
			}
		}
		return valueXmlString.toString();
	}

	// end of code on 28/08/09 for CopyBomDet
	private String actionViewAltBOM(Document dom, Document dom1,
			String objContext, String xtraParams) throws RemoteException,
			ITMException {

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		// StringBuffer valueXmlString = new
		// StringBuffer("<?xml version=\"1.0\" encoding=\"UTF-8\"?>  \r\n<Root>\r\n");
		StringBuffer valueXmlString = new StringBuffer(
				"<?xml version='1.0' encoding='" + commonConstants.ENCODING
						+ "'?> \r\n<Root>\r\n");
		String bomCode = null;
		GenericUtility genericUtility = GenericUtility.getInstance();

		try {
			ConnDriver connDriver = new ConnDriver();

			if (conn == null) {
				conn = connDriver.getConnectDB("DriverITM");
			}
			connDriver = null;

			bomCode = genericUtility.getColumnValue("bom_code", dom1);
			if (bomCode != null) {

				sql = "SELECT ITEM_CODE, ITEM_REF, ITEM_CODE__ALT, QUANTITY, ITEM_CODE_ALT_SEQ "
						+ "FROM BOM_ALT  "
						+ "WHERE BOM_CODE = ?"
						+ "ORDER BY ITEM_CODE_ALT_SEQ";
				System.out.println("Sql Query::::::" + sql);
				System.out.println("Setting Parameter:::" + bomCode);
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, bomCode);
				rs = pstmt.executeQuery();
				while (rs.next()) {
					valueXmlString.append("<Detail>\r\n");
					valueXmlString.append("<item_code>").append("<![CDATA[")
							.append(rs.getString(1).trim()).append("]]>")
							.append("</item_code>\r\n");
					valueXmlString.append("<item_ref>").append("<![CDATA[")
							.append(rs.getString(2).trim()).append("]]>")
							.append("</item_ref>\r\n");
					valueXmlString.append("<item_code__alt>")
							.append("<![CDATA[").append(rs.getString(3).trim())
							.append("]]>").append("</item_code__alt>\r\n");
					valueXmlString.append("<quantity>").append("<![CDATA[")
							.append(rs.getString(4).trim()).append("]]>")
							.append("</quantity>\r\n");
					valueXmlString.append("<item_code_alt_seq>")
							.append("<![CDATA[").append(rs.getString(5).trim())
							.append("]]>").append("</item_code_alt_seq>\r\n");
					valueXmlString.append("</Detail>\r\n");
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
			}
			valueXmlString.append("</Root>\r\n");
		} catch (SQLException se) {
			System.out.println("Exception : BOMActEJB : actionViewAltBOM "
					+ se.getMessage());
			se.printStackTrace();
			System.out.println("SQL : " + sql);
			throw new ITMException(se);
		} catch (Exception e) {
			System.out
					.println("Exception : BOMActEJB : actionViewAltBOM :(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) {
			}
		}
		// stem.out.println("valueXmlString.toString() "+valueXmlString.toString());
		return valueXmlString.toString();
	}

	private String actionStock(Document dom, Document dom1, String objContext,
			String xtraParams) throws RemoteException, ITMException {
		// atement stmt = null;
		ResultSet rs = null;
		Connection conn = null;
		PreparedStatement pstmt = null;

		String sql = null;
		String bomCode = null;
		String trackShelfLife = null;
		String itemCode = null;
		String reqType = null;

		Timestamp currDate = null;

		double batchQtyD = 0.0;
		double batchQty = 0.0;
		double qtyPar = 0.0;
		double minQty = 0.0;
		double reqQty = 0.0;

		StringBuffer valueXmlString = new StringBuffer(
				"<?xml version='1.0' encoding='" + commonConstants.ENCODING
						+ "'?> \r\n<Root>\r\n"); // new
													// StringBuffer("<?xml version=\"1.0\" encoding=\"UTF-8\"?>  \r\n<Root>\r\n");

		GenericUtility genericUtility = GenericUtility.getInstance();
		ConnDriver connDriver = new ConnDriver();
		try {
			if (conn == null) {
				conn = connDriver.getConnectDB("DriverITM");
			}
			connDriver = null;
			bomCode = genericUtility.getColumnValue("bom_code", dom);
			System.out.println("bomCode From Dom::::::" + bomCode);

			batchQtyD = Double.parseDouble(genericUtility.getColumnValue(
					"batch_qty", dom1));
			System.out.println("batchQtyD From Dom::::::" + batchQtyD);

			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 (bomCode != null) {
				sql = "SELECT BOM.ITEM_CODE, BOM.BOM_CODE,BOMDET.ITEM_CODE,ITEM_A.DESCR,BOMDET.REQ_TYPE,"
						+ "ITEM_B.DESCR,SUM(BOMDET.MIN_QTY) AS MIN_QTY,SUM(BOMDET.QTY_PER) AS QTY_PER, MAX(BOM.BATCH_QTY) AS BATCH_QTY,"
						+ "ITEM_B.DESCR,SUM(BOMDET.MIN_QTY) AS MIN_QTY,SUM(BOMDET.QTY_PER) AS QTY_PER, MAX(BOM.BATCH_QTY) BATCH_QTY,"
						+ "0.0 AS STK_QTY,0.0 AS NON_STK_QTY,0.0 AS REQ_QTY,0.0 AS ALLOC_QTY  FROM BOM,BOMDET,ITEM ITEM_A,"
						+ "ITEM ITEM_B "
						+ " WHERE ( BOM.BOM_CODE = BOMDET.BOM_CODE ) AND"
						+ "( BOM.ITEM_CODE = ITEM_A.ITEM_CODE ) AND"
						+ "( BOMDET.ITEM_CODE= ITEM_B.ITEM_CODE ) AND"
						+ "( ( BOM.BOM_CODE = ?) )"
						+ "GROUP BY BOM.BOM_CODE,BOM.ITEM_CODE,BOMDET.ITEM_CODE,BOMDET.REQ_TYPE,ITEM_A.DESCR,ITEM_B.DESCR";
				System.out.println("MainQuery:::" + sql);
				pstmt = conn.prepareStatement(sql);
				System.out.println("Setting BomCode as " + bomCode);
				pstmt.setString(1, bomCode);
				rs = pstmt.executeQuery();

				while (rs.next()) {
					valueXmlString.append("<Detail>\r\n");
					itemCode = rs.getString(3);// BOM.ITEM_CODE
					reqType = rs.getString(5);// BOMDET.REQ_TYPE
					minQty = rs.getDouble(7);// MIN_QTY
					qtyPar = rs.getDouble(8);// QTY_PER;
					batchQty = rs.getDouble(9);// BATCH_QTY;
					valueXmlString.append("<bom_code>").append("<![CDATA[")
							.append(rs.getString(2).trim()).append("]]>")
							.append("</bom_code>\r\n");
					valueXmlString.append("<item_code>").append("<![CDATA[")
							.append(rs.getString(3).trim()).append("]]>")
							.append("</item_code>\r\n");
					valueXmlString.append("<descr>").append("<![CDATA[")
							.append(rs.getString(6).trim()).append("]]>")
							.append("</descr>\r\n");

					if (reqType.equalsIgnoreCase("s")) {
						reqQty = (batchQtyD / batchQty) * qtyPar;
					} else if (reqType.equalsIgnoreCase("P")) {
						reqQty = (qtyPar / batchQty) * batchQtyD;
					} else// f
					{

						reqQty = qtyPar;
					}
					if (reqQty < minQty) {
						reqQty = minQty;
					}

					valueXmlString.append("<quantity>").append("<![CDATA[")
							.append(reqQty).append("]]>")
							.append("</quantity>\r\n");
					pstmt = null;

					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 b.available = 'Y'  and b.stat_type <> 'S'  and (a.quantity - nvl(a.alloc_qty,0))  > 0 ");
					if (trackShelfLife.equalsIgnoreCase("Y")) {
						sb.append("and ( a.exp_date is null or  a.exp_date >?)");
					}
					pstmt = conn.prepareStatement(sb.toString());
					pstmt.setString(1, itemCode);
					if (trackShelfLife.equalsIgnoreCase("Y")) {

						pstmt.setTimestamp(2, currDate);
					}
					rs1 = pstmt.executeQuery();
					while (rs1.next()) {
						valueXmlString.append("<stock>").append("<![CDATA[")
								.append(rs1.getDouble(1)).append("]]>")
								.append("</stock>\r\n");
						valueXmlString.append("<allock_qty>")
								.append("<![CDATA[")
								.append(rs1.getString(2).trim()).append("]]>")
								.append("</allock_qty>\r\n");
					}
					rs1.close();
					rs1 = null;

					valueXmlString.append("</Detail>\r\n");
				}
			}
			valueXmlString.append("</Root>\r\n");

		} catch (SQLException se) {
			System.out.println("Exception : BOMActEJB : actionStock "
					+ se.getMessage());
			se.printStackTrace();
			throw new ITMException(se);
		} catch (Exception e) {
			System.out
					.println("Exception : BOMActEJB : 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) {
			}
		}
		System.out.println("valueXmlString.toString() "
				+ valueXmlString.toString());
		return valueXmlString.toString();
	}

	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;
	}

	private String actionTransform(Document dom, Document dom1,
			String objContext, String xtraParams, Document selDataDom)
			throws ITMException {
		String schemeCode = null;
		String returnString = "";
		Connection conn = null;
		ResultSet rs = null, rsDescr = null;
		Statement stmt = null;
		PreparedStatement pstmt = null, pstmtDescr = null;
		// application specific
		String actionCode = null;
		String roleCode = null;
		String instr = null;
		int cnt = 0;
		NodeList detailList = null;
		int detailListLength = 0;
		StringBuffer valueXmlString = new StringBuffer(
				"<?xml version='1.0' encoding='" + commonConstants.ENCODING
						+ "'?> \r\n<Root>\r\n"); // new
													// StringBuffer("<?xml version=\"1.0\" encoding=\"UTF-8\"?>  \r\n<Root>\r\n");
		ConnDriver connDriver = new ConnDriver();
		ITMDBAccessEJB itmDbAccessEJB = new ITMDBAccessEJB();
		String userId = null, descr;
		String bomCode = "", itemDescr = "", bomCodeHeader = "";
		String sql1 = "", sqlSpec = "", sqlDescr = "", sqlPreBom = "";
		String effFromStr = "", validUptoStr = "", itemCode = "", itemRef = "", qtyPerStr = "", reqType = "", minQty = "", operation = "", reqlt = "", critItem = "", autoOrd = "", appMinQty = "", appMaxQty = "", effCost = "", nature = "", matchPotency = "", minPotencyPerc = "", adjPotency = "", round = "", roundTo = "", usageType = "", allocateType = "", considerPartialAltlocUsageType = "", considerLotNoIssueuseInvstatus = "", dimension = "", minReqQty = "", manualAlt = "", potencyFormula = "", remarks = "", unit = "", suppCodeMfg = "", issVarienceType = "", issVarience = "", unitAlt = "", convQtyStduom = "", qtyPerAlt = "", qtyPerUnit = "", prntFlg = "", backflushType = "", locCodeWip = "", rbfFormula = "", pendStk = "", potencyInput = "", issVarValtype = "", itemCodeOut = "", storeCode = "", specMetadata = "", specDimension = "", mfgRef = "", itemCategory = "", strengthValue = "", strengthUnit = "", itemType = "", itemTypeValue = "", dispContainerSizeitemCodeWip = "", settingMethod = "", qtyCalcFormula = "", itemCodePreOp = "", operationUsed = "";
		String considerPartialAlt = "", locUsageType = "", considerLotNoIssue = "", useInvstatus = "", dispContainerSize = "", itemCodeWip = "";
		Timestamp effFrom = null, validUpto = null;
		double oldBatchQty = 0, curBatchQty = 0, qtyPer = 0; // 14/03/12
																// manoharan
		String batchQtyStr = ""; // 14/03/12 manoharan
		try {

			conn = connDriver.getConnectDB("DriverITM");
			stmt = conn.createStatement();
			detailList = selDataDom.getElementsByTagName("Detail");
			detailListLength = detailList.getLength();
			bomCode = GenericUtility.getInstance().getColumnValueFromNode(
					"bom_code", detailList.item(0));
			bomCodeHeader = GenericUtility.getInstance().getColumnValue(
					"bom_code", dom1);
			// 14/03/12 manoharan
			batchQtyStr = GenericUtility.getInstance().getColumnValue(
					"batch_qty", dom1);
			sqlPreBom = "select batch_qty from bom where bom_code = ? ";
			pstmtDescr = conn.prepareStatement(sqlPreBom);
			pstmtDescr.setString(1, bomCode);
			rsDescr = pstmtDescr.executeQuery();
			if (rsDescr.next()) {
				oldBatchQty = rsDescr.getDouble(1);
			}
			rsDescr.close();
			pstmtDescr.close();
			if (batchQtyStr == null || batchQtyStr.trim().length() == 0) {
				curBatchQty = oldBatchQty;
			} else {
				curBatchQty = Double.parseDouble(batchQtyStr);
			}

			// end 14/03/12 manoharan

			sqlPreBom = "select count(*) from bomdet where bom_code = ? ";
			pstmtDescr = conn.prepareStatement(sqlPreBom);
			pstmtDescr.setString(1, bomCodeHeader);
			rsDescr = pstmtDescr.executeQuery();
			if (rsDescr.next()) {
				cnt = rsDescr.getInt(1);
			}
			rsDescr.close();
			pstmtDescr.close();

			// end 14/03/12/ manoharan
			GenericUtility genericUtility = GenericUtility.getInstance();
			SimpleDateFormat sdf = new SimpleDateFormat(
					genericUtility.getApplDateFormat());

			if (cnt == 0) {
				sql1 = "SELECT "
						+ "	BOMDET.ITEM_CODE , BOMDET.ITEM_REF, BOMDET.QTY_PER , BOMDET.EFF_FROM, "
						+ "	BOMDET.VALID_UPTO, BOMDET.REQ_TYPE, BOMDET.MIN_QTY , BOMDET.OPERATION , BOMDET.REQ_LT,"
						+ "	BOMDET.CRIT_ITEM , BOMDET.AUTO_ORD ,ITEM.DESCR ,"
						+ "	BOMDET.NATURE, BOMDET.MATCH_POTENCY, BOMDET.MIN_POTENCY_PERC, BOMDET.ADJ_POTENCY, BOMDET.ROUND,"
						+ "	BOMDET.ROUND_TO, BOMDET.USAGE_TYPE, BOMDET.ALLOCATE_TYPE, BOMDET.CONSIDER_PARTIAL_ALT, BOMDET.LOC_USAGE_TYPE ,"
						+ "	BOMDET.CONSIDER_LOT_NO__ISSUE, BOMDET.USE_INVSTATUS, BOMDET.DIMENSION , BOMDET.MIN_REQ_QTY, BOMDET.MANUAL_ALT, "
						+ "	BOMDET.POTENCY_FORMULA, BOMDET.REMARKS , ITEM.UNIT , BOMDET.SUPP_CODE__MFG , BOMDET.ISS_VARIENCE_TYPE , "
						+ "	BOMDET.ISS_VARIENCE , BOMDET.UNIT__ALT , BOMDET.CONV__QTY_STDUOM, BOMDET.QTY_PER__ALT , BOMDET.QTY_PER__UNIT, "
						+ "	BOMDET.PRNT_FLG,  BOMDET.BACKFLUSH_TYPE , BOMDET.LOC_CODE__WIP, BOMDET.RBF_FORMULA,"
						+ "	BOMDET.PEND_STK, BOMDET.POTENCY_INPUT, BOMDET.ISS_VAR_VALTYPE,   "
						+ "	BOMDET.SPEC_METADATA, BOMDET.SPEC_DIMENSION , BOMDET.MFG_REF , BOMDET.ITEM_CATEGORY, BOMDET.DISP_CONTAINER_SIZE,  "
						+ "	BOMDET.OPERATION_USED "
						+ "	FROM BOMDET ,ITEM WHERE ( BOMDET.ITEM_CODE = ITEM.ITEM_CODE ) AND "
						+ "	( ( BOMDET.BOM_CODE = ? ) ) ";

				pstmt = conn.prepareStatement(sql1);
				pstmt.setString(1, bomCode);
				rs = pstmt.executeQuery();
				while (rs.next()) {
					itemCode = rs.getString("ITEM_CODE") == null ? "" : rs
							.getString("ITEM_CODE");
					itemRef = rs.getString("ITEM_REF") == null ? "" : rs
							.getString("ITEM_REF");
					qtyPerStr = rs.getString("QTY_PER") == null ? "" : rs
							.getString("QTY_PER");
					// date value
					effFrom = rs.getTimestamp("EFF_FROM");
					if (effFrom != null) {
						effFromStr = new SimpleDateFormat(
								genericUtility.getApplDateFormat()).format(
								effFrom).toString();
					} else {
						effFromStr = "";
					}
					validUpto = rs.getTimestamp("VALID_UPTO");
					if (validUpto != null) {
						validUptoStr = new SimpleDateFormat(
								genericUtility.getApplDateFormat()).format(
								validUpto).toString();
					} else {
						validUptoStr = "";
					}

					// date value end
					reqType = rs.getString("REQ_TYPE") == null ? "" : rs
							.getString("REQ_TYPE");
					// 14/03/12 manoharan
					if (qtyPerStr == null || qtyPerStr.trim().length() == 0) {
						qtyPerStr = "0";
					}
					qtyPer = Double.parseDouble(qtyPerStr);
					if (!"F".equals(reqType)) {
						qtyPer = getReqDecimal(qtyPer / oldBatchQty
								* curBatchQty, 3);
					}
					// end 14/03/12 manoharan
					minQty = rs.getString("MIN_QTY") == null ? "" : rs
							.getString("MIN_QTY");
					operation = rs.getString("OPERATION") == null ? "" : rs
							.getString("OPERATION");
					reqlt = rs.getString("REQ_LT") == null ? "" : rs
							.getString("REQ_LT");

					critItem = rs.getString("CRIT_ITEM") == null ? "" : rs
							.getString("CRIT_ITEM");
					autoOrd = rs.getString("AUTO_ORD") == null ? "" : rs
							.getString("AUTO_ORD");
					descr = rs.getString("DESCR") == null ? "" : rs
							.getString("DESCR");

					nature = rs.getString("NATURE") == null ? "" : rs
							.getString("NATURE");
					matchPotency = rs.getString("MATCH_POTENCY") == null ? ""
							: rs.getString("MATCH_POTENCY");
					minPotencyPerc = rs.getString("MIN_POTENCY_PERC") == null ? ""
							: rs.getString("MIN_POTENCY_PERC");
					adjPotency = rs.getString("ADJ_POTENCY") == null ? "" : rs
							.getString("ADJ_POTENCY");
					round = rs.getString("ROUND") == null ? "" : rs
							.getString("ROUND");

					roundTo = rs.getString("ROUND_TO") == null ? "" : rs
							.getString("ROUND_TO");
					usageType = rs.getString("USAGE_TYPE") == null ? "" : rs
							.getString("USAGE_TYPE");
					allocateType = rs.getString("ALLOCATE_TYPE") == null ? ""
							: rs.getString("ALLOCATE_TYPE");
					considerPartialAlt = rs.getString("CONSIDER_PARTIAL_ALT") == null ? ""
							: rs.getString("CONSIDER_PARTIAL_ALT");
					locUsageType = rs.getString("LOC_USAGE_TYPE") == null ? ""
							: rs.getString("LOC_USAGE_TYPE");

					considerLotNoIssue = rs.getString("CONSIDER_LOT_NO__ISSUE") == null ? ""
							: rs.getString("CONSIDER_LOT_NO__ISSUE");
					useInvstatus = rs.getString("USE_INVSTATUS") == null ? ""
							: rs.getString("USE_INVSTATUS");
					dimension = rs.getString("DIMENSION") == null ? "" : rs
							.getString("DIMENSION");
					minReqQty = rs.getString("MIN_REQ_QTY") == null ? "" : rs
							.getString("MIN_REQ_QTY");
					manualAlt = rs.getString("MANUAL_ALT") == null ? "" : rs
							.getString("MANUAL_ALT");

					potencyFormula = rs.getString("POTENCY_FORMULA") == null ? ""
							: rs.getString("POTENCY_FORMULA");
					remarks = rs.getString("REMARKS") == null ? "" : rs
							.getString("REMARKS");
					unit = rs.getString("UNIT") == null ? "" : rs
							.getString("UNIT");
					suppCodeMfg = rs.getString("SUPP_CODE__MFG") == null ? ""
							: rs.getString("SUPP_CODE__MFG");
					issVarienceType = rs.getString("ISS_VARIENCE_TYPE") == null ? ""
							: rs.getString("ISS_VARIENCE_TYPE");

					issVarience = rs.getString("ISS_VARIENCE") == null ? ""
							: rs.getString("ISS_VARIENCE");
					unitAlt = rs.getString("UNIT__ALT") == null ? "" : rs
							.getString("UNIT__ALT");
					convQtyStduom = rs.getString("CONV__QTY_STDUOM") == null ? ""
							: rs.getString("CONV__QTY_STDUOM");
					qtyPerAlt = rs.getString("QTY_PER__ALT") == null ? "" : rs
							.getString("QTY_PER__ALT");
					qtyPerUnit = rs.getString("QTY_PER__UNIT") == null ? ""
							: rs.getString("QTY_PER__UNIT");

					prntFlg = rs.getString("PRNT_FLG") == null ? "" : rs
							.getString("PRNT_FLG");
					backflushType = rs.getString("BACKFLUSH_TYPE") == null ? ""
							: rs.getString("BACKFLUSH_TYPE");
					locCodeWip = rs.getString("LOC_CODE__WIP") == null ? ""
							: rs.getString("LOC_CODE__WIP");
					rbfFormula = rs.getString("RBF_FORMULA") == null ? "" : rs
							.getString("RBF_FORMULA");

					pendStk = rs.getString("PEND_STK") == null ? "" : rs
							.getString("PEND_STK");
					potencyInput = rs.getString("POTENCY_INPUT") == null ? ""
							: rs.getString("POTENCY_INPUT");
					issVarValtype = rs.getString("ISS_VAR_VALTYPE") == null ? ""
							: rs.getString("ISS_VAR_VALTYPE");

					specMetadata = rs.getString("SPEC_METADATA") == null ? ""
							: rs.getString("SPEC_METADATA");
					specDimension = rs.getString("SPEC_DIMENSION") == null ? ""
							: rs.getString("SPEC_DIMENSION");
					mfgRef = rs.getString("MFG_REF") == null ? "" : rs
							.getString("MFG_REF");
					itemCategory = rs.getString("ITEM_CATEGORY") == null ? ""
							: rs.getString("ITEM_CATEGORY");
					// strengthValue = rs.getString("STRENGTH_VALUE") == null ?
					// "" :rs.getString("STRENGTH_VALUE");
					// strengthUnit = rs.getString("STRENGTH_UNIT") == null ? ""
					// :rs.getString("STRENGTH_UNIT");
					// itemType = rs.getString("ITEM_TYPE") == null ? ""
					// :rs.getString("ITEM_TYPE");
					// itemTypeValue = rs.getString("ITEM_TYPE_VALUE") == null ?
					// "" :rs.getString("ITEM_TYPE_VALUE");
					dispContainerSize = rs.getString("DISP_CONTAINER_SIZE") == null ? ""
							: rs.getString("DISP_CONTAINER_SIZE");
					operationUsed = rs.getString("OPERATION_USED") == null ? ""
							: rs.getString("OPERATION_USED");

					valueXmlString.append("<Detail>\r\n");
					valueXmlString.append("<item_code isSrvCallOnChg =\"1\" >")
							.append("<![CDATA[").append(itemCode.trim())
							.append("]]>").append("</item_code>\r\n");

					valueXmlString.append("<item_ref isSrvCallOnChg =\"0\">")
							.append("<![CDATA[").append(itemRef).append("]]>")
							.append("</item_ref>\r\n");
					valueXmlString.append("<qty_per isSrvCallOnChg =\"0\">")
							.append("<![CDATA[").append(qtyPer).append("]]>")
							.append("</qty_per>\r\n");
					valueXmlString.append("<eff_from isSrvCallOnChg =\"0\" >")
							.append("<![CDATA[").append(effFromStr)
							.append("]]>").append("</eff_from>\r\n");
					valueXmlString.append("<valid_upto isSrvCallOnChg =\"0\">")
							.append("<![CDATA[").append(validUptoStr)
							.append("]]>").append("</valid_upto>\r\n");

					valueXmlString.append("<req_type isSrvCallOnChg =\"0\">")
							.append("<![CDATA[").append(reqType).append("]]>")
							.append("</req_type>\r\n");
					valueXmlString.append("<min_qty isSrvCallOnChg =\"0\">")
							.append("<![CDATA[").append(minQty).append("]]>")
							.append("</min_qty>\r\n");
					valueXmlString.append("<operation isSrvCallOnChg =\"0\">")
							.append("<![CDATA[").append(operation)
							.append("]]>").append("</operation>\r\n");
					valueXmlString.append("<req_lt isSrvCallOnChg =\"0\">")
							.append("<![CDATA[").append(reqlt).append("]]>")
							.append("</req_lt>\r\n");
					valueXmlString.append("<crit_item isSrvCallOnChg =\"0\">")
							.append("<![CDATA[").append(critItem).append("]]>")
							.append("</crit_item>\r\n");
					valueXmlString.append("<auto_ord isSrvCallOnChg =\"0\">")
							.append("<![CDATA[").append(autoOrd).append("]]>")
							.append("</auto_ord>\r\n");
					valueXmlString.append("<nature isSrvCallOnChg =\"0\">")
							.append("<![CDATA[").append(nature).append("]]>")
							.append("</nature>\r\n");
					valueXmlString
							.append("<match_potency isSrvCallOnChg =\"0\">")
							.append("<![CDATA[").append(matchPotency)
							.append("]]>").append("</match_potency>\r\n");
					valueXmlString
							.append("<min_potency_perc isSrvCallOnChg =\"0\">")
							.append("<![CDATA[").append(minPotencyPerc)
							.append("]]>").append("</min_potency_perc>\r\n");
					valueXmlString
							.append("<adj_potency isSrvCallOnChg =\"0\">")
							.append("<![CDATA[").append(adjPotency)
							.append("]]>").append("</adj_potency>\r\n");
					valueXmlString.append("<round isSrvCallOnChg =\"0\">")
							.append("<![CDATA[").append(round).append("]]>")
							.append("</round>\r\n");
					valueXmlString.append("<round_to isSrvCallOnChg =\"0\">")
							.append("<![CDATA[").append(roundTo).append("]]>")
							.append("</round_to>\r\n");
					valueXmlString.append("<usage_type isSrvCallOnChg =\"0\">")
							.append("<![CDATA[").append(usageType)
							.append("]]>").append("</usage_type>\r\n");
					valueXmlString
							.append("<allocate_type isSrvCallOnChg =\"0\">")
							.append("<![CDATA[").append(allocateType)
							.append("]]>").append("</allocate_type>\r\n");
					valueXmlString
							.append("<consider_partial_alt isSrvCallOnChg =\"0\">")
							.append("<![CDATA[").append(considerPartialAlt)
							.append("]]>")
							.append("</consider_partial_alt>\r\n");
					valueXmlString
							.append("<loc_usage_type isSrvCallOnChg =\"0\">")
							.append("<![CDATA[").append(locUsageType)
							.append("]]>").append("</loc_usage_type>\r\n");
					valueXmlString
							.append("<consider_lot_no__issue isSrvCallOnChg =\"0\">")
							.append("<![CDATA[").append(considerLotNoIssue)
							.append("]]>")
							.append("</consider_lot_no__issue>\r\n");
					valueXmlString
							.append("<use_invstatus isSrvCallOnChg =\"0\">")
							.append("<![CDATA[").append(useInvstatus)
							.append("]]>").append("</use_invstatus>\r\n");
					valueXmlString.append("<dimension isSrvCallOnChg =\"0\">")
							.append("<![CDATA[").append(dimension)
							.append("]]>").append("</dimension>\r\n");
					valueXmlString
							.append("<min_req_qty isSrvCallOnChg =\"0\">")
							.append("<![CDATA[").append(minReqQty)
							.append("]]>").append("</min_req_qty>\r\n");
					valueXmlString.append("<manual_alt isSrvCallOnChg =\"0\">")
							.append("<![CDATA[").append(manualAlt)
							.append("]]>").append("</manual_alt>\r\n");
					valueXmlString
							.append("<potency_formula isSrvCallOnChg =\"0\">")
							.append("<![CDATA[").append(potencyFormula)
							.append("]]>").append("</potency_formula>\r\n");
					valueXmlString.append("<remarks isSrvCallOnChg =\"0\">")
							.append("<![CDATA[").append(remarks).append("]]>")
							.append("</remarks>\r\n");
					valueXmlString.append("<unit isSrvCallOnChg =\"0\">")
							.append("<![CDATA[").append(unit).append("]]>")
							.append("</unit>\r\n");
					valueXmlString
							.append("<supp_code__mfg isSrvCallOnChg =\"0\">")
							.append("<![CDATA[").append(suppCodeMfg)
							.append("]]>").append("</supp_code__mfg>\r\n");
					valueXmlString
							.append("<iss_varience_type isSrvCallOnChg =\"0\">")
							.append("<![CDATA[").append(issVarienceType)
							.append("]]>").append("</iss_varience_type>\r\n");

					valueXmlString
							.append("<iss_varience isSrvCallOnChg =\"0\">")
							.append("<![CDATA[").append(issVarience)
							.append("]]>").append("</iss_varience>\r\n");
					valueXmlString.append("<unit__alt isSrvCallOnChg =\"0\">")
							.append("<![CDATA[").append(unitAlt).append("]]>")
							.append("</unit__alt>\r\n");
					valueXmlString
							.append("<conv__qty_stduom isSrvCallOnChg =\"0\">")
							.append("<![CDATA[").append(convQtyStduom)
							.append("]]>").append("</conv__qty_stduom>\r\n");
					valueXmlString
							.append("<qty_per__alt isSrvCallOnChg =\"0\">")
							.append("<![CDATA[").append(qtyPerAlt)
							.append("]]>").append("</qty_per__alt>\r\n");
					valueXmlString
							.append("<qty_per__unit isSrvCallOnChg =\"0\">")
							.append("<![CDATA[").append(qtyPerUnit)
							.append("]]>").append("</qty_per__unit>\r\n");
					valueXmlString.append("<prnt_flg isSrvCallOnChg =\"0\">")
							.append("<![CDATA[").append(prntFlg).append("]]>")
							.append("</prnt_flg>\r\n");
					// valueXmlString.append("<iss_varience_type__o>").append("<![CDATA[").append(issVarienceType).append("]]>").append("</iss_varience_type__o>\r\n");
					valueXmlString
							.append("<backflush_type isSrvCallOnChg =\"0\">")
							.append("<![CDATA[").append(backflushType)
							.append("]]>").append("</backflush_type>\r\n");
					valueXmlString
							.append("<loc_code__wip isSrvCallOnChg =\"0\">")
							.append("<![CDATA[").append(locCodeWip)
							.append("]]>").append("</loc_code__wip>\r\n");
					valueXmlString
							.append("<rbf_formula isSrvCallOnChg =\"0\">")
							.append("<![CDATA[").append(rbfFormula)
							.append("]]>").append("</rbf_formula>\r\n");
					valueXmlString.append("<pend_stk isSrvCallOnChg =\"0\">")
							.append("<![CDATA[").append(pendStk).append("]]>")
							.append("</pend_stk>\r\n");
					valueXmlString
							.append("<potency_input isSrvCallOnChg =\"0\">")
							.append("<![CDATA[").append(potencyInput)
							.append("]]>").append("</potency_input>\r\n");
					valueXmlString
							.append("<iss_var_valtype isSrvCallOnChg =\"0\">")
							.append("<![CDATA[").append(issVarValtype)
							.append("]]>").append("</iss_var_valtype>\r\n");
					// valueXmlString.append("<item_code__out>").append("<![CDATA[").append(itemCodeOut).append("]]>").append("</item_code__out>\r\n");
					// valueXmlString.append("<store_code>").append("<![CDATA[").append(storeCode).append("]]>").append("</store_code>\r\n");
					valueXmlString
							.append("<spec_metadata isSrvCallOnChg =\"0\">")
							.append("<![CDATA[").append(specMetadata)
							.append("]]>").append("</spec_metadata>\r\n");
					valueXmlString
							.append("<spec_dimension isSrvCallOnChg =\"0\">")
							.append("<![CDATA[").append(specDimension)
							.append("]]>").append("</spec_dimension>\r\n");
					valueXmlString.append("<mfg_ref isSrvCallOnChg =\"0\">")
							.append("<![CDATA[").append(mfgRef).append("]]>")
							.append("</mfg_ref>\r\n");
					valueXmlString
							.append("<item_category isSrvCallOnChg =\"0\">")
							.append("<![CDATA[").append(itemCategory)
							.append("]]>").append("</item_category>\r\n");
					// valueXmlString.append("<strength_value isSrvCallOnChg =\"0\">").append("<![CDATA[").append(strengthValue).append("]]>").append("</strength_value>\r\n");
					// valueXmlString.append("<strength_unit isSrvCallOnChg =\"0\">").append("<![CDATA[").append(strengthUnit).append("]]>").append("</strength_unit>\r\n");
					// valueXmlString.append("<item_type isSrvCallOnChg =\"0\">").append("<![CDATA[").append(itemType).append("]]>").append("</item_type>\r\n");
					// valueXmlString.append("<item_type_value isSrvCallOnChg =\"0\">").append("<![CDATA[").append(itemTypeValue).append("]]>").append("</item_type_value>\r\n");
					valueXmlString
							.append("<disp_container_size isSrvCallOnChg =\"0\">")
							.append("<![CDATA[")
							.append(dispContainerSizeitemCodeWip).append("]]>")
							.append("</disp_container_size>\r\n");
					// valueXmlString.append("<item_code__wip>").append("<![CDATA[").append(itemCodeWip).append("]]>").append("</item_code__wip>\r\n");
					// valueXmlString.append("<setting_method>").append("<![CDATA[").append(settingMethod).append("]]>").append("</setting_method>\r\n");
					// valueXmlString.append("<qty_calc__formula>").append("<![CDATA[").append(qtyCalcFormula).append("]]>").append("</qty_calc__formula>\r\n");
					// valueXmlString.append("<item_code__pre_op>").append("<![CDATA[").append(itemCodePreOp).append("]]>").append("</item_code__pre_op>\r\n");
					valueXmlString
							.append("<operation_used isSrvCallOnChg =\"0\">")
							.append("<![CDATA[").append(operationUsed)
							.append("]]>").append("</operation_used>\r\n");

					valueXmlString.append("</Detail>\r\n");
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
			} // end of Edit Mode Check

			// }
			valueXmlString.append("</Root>\r\n");
		} catch (Exception e) {
			// System.out.println("Exception CopyBomDetEJB "+e.getMessage());
			e.printStackTrace();
			throw new ITMException(e);
		} finally {
			try {
				conn.close();
				conn = null;
			} catch (Exception se) {
			}
		}
		return valueXmlString.toString();
	}

	// added by chandni shah - 21-Dec-2011

	public ArrayList getDetails(String bomCode, String batchQtyD)
			throws ITMException {
		PreparedStatement pstmt = null, pstmt1 = null;
		ResultSet rs = null;
		Connection conn = null;
		BOMStockDtlParams thisBean = null;
		ArrayList stkBOMList = new ArrayList();
		String sql = null;
		String trackShelfLife = null;
		String itemCode = null;
		String reqType = null;
		String siteCode = null;
		Timestamp currDate = null;
		double batchQty = 0.0;
		double qtyPar = 0.0;
		double minQty = 0.0;
		double reqQty = 0.0;
		double stkQty = 0.0;
		double nonStkQty = 0.0;
		double allockQty = 0.0;

		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();

			/*
			 * sql =
			 * "SELECT s.GEO_LOC FROM siteitem i, site s where i.site_code = s.site_code "
			 * +
			 * " and i.bom_code = ? and case when i.active is null then 'Y' else i.active end  = 'Y' "
			 * ; pstmt = conn.prepareStatement(sql); pstmt.setString(1,bomCode);
			 * rs = pstmt.executeQuery(); if(rs.next()) { siteCode =
			 * rs.getString(1);
			 * 
			 * } System.out.println("Current Date::::::"+currDate); rs.close();
			 * pstmt.close();
			 */

			if (bomCode != null) {
				sql = "SELECT BOM.ITEM_CODE, BOM.BOM_CODE,BOMDET.ITEM_CODE,ITEM_A.DESCR,BOMDET.REQ_TYPE,"
						+ "ITEM_B.DESCR,SUM(BOMDET.MIN_QTY) AS MIN_QTY,SUM(BOMDET.QTY_PER) AS QTY_PER, MAX(BOM.BATCH_QTY) AS BATCH_QTY,"
						+ "0.0 AS STK_QTY,0.0 AS NON_STK_QTY,0.0 AS REQ_QTY,0.0 AS ALLOC_QTY  FROM BOM,BOMDET,ITEM ITEM_A,"
						+ "ITEM ITEM_B "
						+ "WHERE (BOM.BOM_CODE = BOMDET.BOM_CODE ) AND"
						+ "(BOM.ITEM_CODE = ITEM_A.ITEM_CODE ) AND"
						+ "(BOMDET.ITEM_CODE= ITEM_B.ITEM_CODE ) AND"
						+ "(( BOM.BOM_CODE = ?) )"
						+ "GROUP BY BOM.BOM_CODE,BOM.ITEM_CODE,BOMDET.ITEM_CODE,BOMDET.REQ_TYPE,ITEM_A.DESCR,ITEM_B.DESCR";

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

				while (rs.next()) {
					itemCode = rs.getString(3);
					reqType = rs.getString(5);
					minQty = rs.getDouble(7);
					qtyPar = rs.getDouble(8);
					batchQty = rs.getDouble(9);
					reqQty = rs.getDouble(13);

					thisBean = new BOMStockDtlParams();
					thisBean.itemCode = rs.getString(3);// BOM.ITEM_CODE
														// //change
					thisBean.bomCode = rs.getString(2);
					thisBean.descr = rs.getString(6);
					thisBean.reqType = rs.getString(5);// BOMDET.REQ_TYPE
					thisBean.minQty = rs.getDouble(7);// MIN_QTY
					thisBean.qtyPar = rs.getDouble(8);// QTY_PER;
					thisBean.batchQty = rs.getDouble(9);// BATCH_QTY;
					thisBean.stkQty = rs.getDouble(10);
					thisBean.nonStkQty = rs.getDouble(11);
					thisBean.nonStkQty = rs.getDouble(12);
					thisBean.reqQty = rs.getDouble(13);

					if (reqType != null && reqType.equalsIgnoreCase("S")) {
						reqQty = (Double.parseDouble(batchQtyD) / batchQty)
								* qtyPar;
					} else if (reqType != null && reqType.equalsIgnoreCase("P")) {
						reqQty = (qtyPar / batchQty)
								* Double.parseDouble(batchQtyD);
					} else {
						reqQty = qtyPar;
					}
					if (reqQty < minQty) {
						reqQty = minQty;
					}
					thisBean.reqQty = reqQty;

					pstmt1 = conn
							.prepareStatement("select nvl(track_shelf_life,'N') from item where item_code = ?");
					pstmt1.setString(1, rs.getString(3));
					ResultSet rs1 = pstmt1.executeQuery();
					if (rs1.next()) {
						trackShelfLife = rs1.getString(1);
					}
					rs1.close();
					rs1 = null;
					pstmt1.close();
					pstmt1 = null;
					StringBuffer sb = new StringBuffer();
					sb.append("select nvl(sum(a.quantity - a.alloc_qty),0), nvl(sum(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 b.available = 'Y' and b.plan_prod = 'Y'  and b.stat_type <> 'S'  and (a.quantity - nvl(a.alloc_qty,0))  > 0 "
							+ " and site_code in ( select site_code from site where geo_loc in (SELECT s.GEO_LOC FROM siteitem i, site s where i.site_code = s.site_code "
							+ " and i.bom_code = ? and case when i.active is null then 'Y' else i.active end  = 'Y' ) )");
					if (trackShelfLife != null
							&& trackShelfLife.equalsIgnoreCase("Y")) {
						sb.append("and ( a.exp_date is null or  a.exp_date >?)");
					}
					pstmt1 = conn.prepareStatement(sb.toString());
					pstmt1.setString(1, rs.getString(3));
					pstmt1.setString(2, bomCode);
					if (trackShelfLife != null
							&& trackShelfLife.equalsIgnoreCase("Y")) {
						pstmt1.setTimestamp(3, currDate);
					}
					rs1 = pstmt1.executeQuery();
					while (rs1.next()) {
						thisBean.stkQty = rs1.getDouble(1);
						thisBean.allockQty = rs1.getDouble(2);
					}
					rs1.close();
					rs1 = null;
					pstmt1.close();
					pstmt1 = null;

					StringBuffer sbn = new StringBuffer();
					sbn.append(" select nvl(sum(a.quantity - a.alloc_qty),0) from stock a, invstat b "
							+ " where a.inv_stat = b.inv_stat  and a.item_code = ? and b.available = 'N' and b.stat_type <> 'S'  and (a.quantity - nvl(a.alloc_qty,0))  > 0 "
							+ " and site_code in ( select site_code from site where geo_loc in(SELECT s.GEO_LOC FROM siteitem i, site s where i.site_code = s.site_code "
							+ " and i.bom_code = ? and case when i.active is null then 'Y' else i.active end  = 'Y' ))");
					if (trackShelfLife.equalsIgnoreCase("Y")) {
						sbn.append("and ( a.exp_date is null or  a.exp_date >?)");
					}
					pstmt1 = conn.prepareStatement(sbn.toString());
					pstmt1.setString(1, rs.getString(3));
					pstmt1.setString(2, bomCode);
					if (trackShelfLife.equalsIgnoreCase("Y")) {
						pstmt1.setTimestamp(3, currDate);
					}
					rs1 = pstmt1.executeQuery();
					if (rs1.next()) {
						thisBean.nonStkQty = rs1.getDouble(1);
						// thisBean.allockQty = rs1.getDouble(2);
					}
					rs1.close();
					rs1 = null;
					pstmt1.close();
					pstmt1 = null;

					// Added by Dadaso pawar [MF3GSUN023] 
					BOMStockBean bomStockBeanObj=new BOMStockBean();
					UserInfoBean userInfoL=bomStockBeanObj.getUserInfo();	
					if(userInfoL !=null){
					String siteCodeL=userInfoL.getSiteCode();
					siteCodeL=siteCodeL== null ? "" :siteCodeL.trim();
					System.out.println("Site code bom----->>>["+siteCodeL+"]");		

					StringBuffer sbnL = new StringBuffer();
					sbnL.append(" select nvl(sum(a.quantity - a.alloc_qty),0) from stock a, invstat b "
							+ " where a.inv_stat = b.inv_stat  and a.item_code = ? and b.available = 'N' and b.stat_type <> 'S'  and (a.quantity - nvl(a.alloc_qty,0))  > 0 "
							+ " and site_code = ?"); 
							
					if (trackShelfLife.equalsIgnoreCase("Y")) {
						sbnL.append("and ( a.exp_date is null or  a.exp_date >?)");
					}
					pstmt1 = conn.prepareStatement(sbnL.toString());
					pstmt1.setString(1, rs.getString(3));
					pstmt1.setString(2, siteCodeL);
					if (trackShelfLife.equalsIgnoreCase("Y")) {
						pstmt1.setTimestamp(3, currDate);
					}
					rs1 = pstmt1.executeQuery();
					if (rs1.next()) {
						thisBean.nonStkQtyL = rs1.getDouble(1);
						// thisBean.allockQty = rs1.getDouble(2);
					}
					rs1.close();
					rs1 = null;
					pstmt1.close();
					pstmt1 = null;			
					
					
					StringBuffer sbL = new StringBuffer();
					sbL.append("select nvl(sum(a.quantity - a.alloc_qty),0), nvl(sum(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 b.available = 'Y' and b.plan_prod = 'Y'  and b.stat_type <> 'S'  and (a.quantity - nvl(a.alloc_qty,0))  > 0 "
							+ " and site_code = ? ");
					if (trackShelfLife != null
							&& trackShelfLife.equalsIgnoreCase("Y")) {
						sbL.append("and ( a.exp_date is null or  a.exp_date >?)");
					}
					pstmt1 = conn.prepareStatement(sbL.toString());
					pstmt1.setString(1, rs.getString(3));
					pstmt1.setString(2, siteCodeL);
					if (trackShelfLife != null
							&& trackShelfLife.equalsIgnoreCase("Y")) {
						pstmt1.setTimestamp(3, currDate);
					}
					rs1 = pstmt1.executeQuery();
					while (rs1.next()) {
						thisBean.stkQtyL = rs1.getDouble(1);
						thisBean.allockQtyL = rs1.getDouble(2);
					}
					rs1.close();
					rs1 = null;
					pstmt1.close();
					pstmt1 = null;
					}
					// end dadaso pawar
					stkBOMList.add(thisBean);
				
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = 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 stkBOMList;
	}

	class BOMStockDtlParams implements Serializable {
		HashMap BOMStockMap = new HashMap();
		String bomCode = null;
		String trackShelfLife = null;
		String itemCode = null;
		String reqType = null;
		String batchQtyD = null;
		String descr = null;

		double allockQty = 0.0;
		double batchQty = 0.0;
		double qtyPar = 0.0;
		double minQty = 0.0;
		double reqQty = 0.0;
		double stkQty = 0.0;
		double nonStkQty = 0.0;
		Timestamp currDate = null;

		// Added by Dadaso Pawar
		double stkQtyL = 0, nonStkQtyL = 0, allockQtyL = 0;

	}
	// Ended by chandni shah - 21-Dec-2011
}