package ibase.webitm.ejb.dis.adv;

import java.rmi.RemoteException; 
import java.util.*;
import java.sql.*;
import java.text.SimpleDateFormat;
import org.w3c.dom.*;
import javax.ejb.*;
import javax.naming.InitialContext;
import ibase.system.config.*;
import ibase.webitm.utility.ITMException;
import ibase.webitm.ejb.*;
import ibase.webitm.utility.GenericUtility;
import java.text.DecimalFormat; //Gulzar - 22/01/08
import javax.ejb.Stateless; // added for ejb3

@Stateless // added for ejb3
public class DespatchAct extends ActionHandlerEJB implements DespatchActLocal, DespatchActRemote
{
	/*public void ejbCreate() throws RemoteException, CreateException 
	{
	} 

	public void ejbRemove()
	{
	}

	public void ejbActivate() 
	{
	}

	public void ejbPassivate() 
	{
	}*/

    public String actionHandler() throws RemoteException,ITMException
	{
		return "";
	}
	
	public String actionHandler(String actionType, String xmlString, String objContext, String xtraParams) throws RemoteException,ITMException
	{
		String retString = "";
		Document dom = null;
		try
		{
			GenericUtility genericUtility = GenericUtility.getInstance();
			if(xmlString != null && xmlString.trim().length()!=0)
			{
				System.out.println("XML String :"+xmlString);
				dom = genericUtility.parseString(xmlString);
			}
			if (actionType.equalsIgnoreCase("Alloc Item"))
			{
				retString = actionAllocItem(dom,objContext,xtraParams);
			}
		}
		catch(Exception e)
		{
			System.out.println("Exception :Dispatch :actionHandler(String xmlString):" + e.getMessage() + ":");
			throw new ITMException(e);
		}
		return retString;
	}

	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;
		try
		{
			if(xmlString != null && xmlString.trim().length()!=0)
			{
				System.out.println("XML String :"+xmlString);
				dom = GenericUtility.getInstance().parseString(xmlString); 
				System.out.println("dom :"+dom);
			}
			if(xmlString1 != null && xmlString1.trim().length()!=0)
			{
				System.out.println("XML String1 :"+xmlString1);
				dom1 = GenericUtility.getInstance().parseString(xmlString1);
				System.out.println("dom1 :"+dom1);
			}
			System.out.println("actionType:"+actionType+":");
						
			if (actionType.equalsIgnoreCase("Stock"))
			{
				retString = actionStock(dom,dom1,objContext,xtraParams);
			}
			else if (actionType.equalsIgnoreCase("Get Pack List"))
			{
				retString = actionGetPackList(dom,dom1,objContext,xtraParams);
			}
			else if (actionType.equalsIgnoreCase("Default"))
			{
				retString = actionDefault(dom, dom1, objContext, xtraParams);
			}
			else if (actionType.equalsIgnoreCase("Lot No"))
			{
				retString = actionLotNo(dom, dom1, objContext, xtraParams);
			}
			else if (actionType.equalsIgnoreCase("WOPackList"))
			{
				retString = actionWoGetPackList(dom, dom1, objContext, xtraParams);
			}
			else if (actionType.equalsIgnoreCase("Packing"))
			{
				retString = actionPacking(dom, dom1, objContext, xtraParams);
			}
		}
	   	catch(Exception e)
		{
			System.out.println("Exception :Dispatch :actionHandler(String xmlString):" + e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		System.out.println("returning String from Despatch : actionHandler"+retString);
	    return retString;
	}

	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("Alloc Item"))
			{
				retString = allocItemTransform(dom,dom1, objContext, xtraParams, selDataDom);
			}
			if (actionType.equalsIgnoreCase("WOPackList"))
			{
				retString = getWoPackListTransform(dom,dom1, objContext, xtraParams, selDataDom);
			}
			if (actionType.equalsIgnoreCase("PackList"))//Gulzar 23-04-07 - 
			{
				retString = packListTransform(dom,dom1, objContext, xtraParams, selDataDom);
			}
			if (actionType.equalsIgnoreCase("Packing"))//Alka 23-04-07 - 
			{
				retString = actionPackingTransform(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);//Gulzar - 22/01/08
		System.out.println("returning String from Despatch.................."); //Gulzar - 22/01/08
	    return retString;
	}

	private String actionAllocItem(Document dom, String objContext, String xtraParams) throws RemoteException , ITMException
	{
		String saleOrder = "";
		String errCode = "";
		String errString = "";
		String sql = "";
		ResultSet rs = null;
		Connection conn = null;
		Statement stmt = null;
		StringBuffer valueXmlString = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root>\r\n");
		GenericUtility genericUtility = GenericUtility.getInstance();
		ConnDriver connDriver = new ConnDriver();
		ITMDBAccessEJB itmDBAccess = new ITMDBAccessEJB();
		try
		{
			conn = connDriver.getConnectDB("DriverITM");
			saleOrder = genericUtility.getColumnValue("sord_no",dom);	
			System.out.println("saleOrder :"+saleOrder);
			if(saleOrder != null && saleOrder.trim().length() > 0)
			{
				sql="SELECT "
					+"SORDALLOC.LINE_NO, "   
					+"SORDALLOC.ITEM_CODE, "
					+"SORDALLOC.LOC_CODE, "   
					+"SORDALLOC.LOT_NO, "   
					+"SORDALLOC.LOT_SL, "   
					+"ITEM.DESCR, "   
					+"SORDALLOC.QTY_ALLOC "  
					+"FROM SORDALLOC, ITEM "  
					+"WHERE SORDALLOC.ITEM_CODE = ITEM.ITEM_CODE "
					+"AND SORDALLOC.SALE_ORDER = '"+ saleOrder +"'";
				System.out.println("Sorder SQL :="+sql);
				stmt = conn.createStatement();
				rs = stmt.executeQuery(sql);
				while (rs.next())
				{
					valueXmlString.append("<Detail>\r\n");
					valueXmlString.append("<sord_no>").append("<![CDATA[").append(saleOrder).append("]]>").append("</sord_no>\r\n");
					valueXmlString.append("<line_no__sord>").append("<![CDATA[").append(rs.getString(1).trim()).append("]]>").append("</line_no__sord>\r\n");
					valueXmlString.append("<item_code>").append("<![CDATA[").append(rs.getString(2).trim()).append("]]>").append("</item_code>\r\n");
					valueXmlString.append("<lot_no>").append("<![CDATA[").append(rs.getString(4).trim()).append("]]>").append("</lot_no>\r\n");
					valueXmlString.append("<lot_sl>").append("<![CDATA[").append(rs.getString(5).trim()).append("]]>").append("</lot_sl>\r\n");
					valueXmlString.append("<loc_code>").append("<![CDATA[").append(rs.getString(3).trim()).append("]]>").append("</loc_code>\r\n");
					valueXmlString.append("<descr>").append("<![CDATA[").append(rs.getString(6).trim()).append("]]>").append("</descr>\r\n");
					valueXmlString.append("<qty_alloc>").append("<![CDATA[").append(rs.getString(7)).append("]]>").append("</qty_alloc>\r\n");
					valueXmlString.append("</Detail>\r\n");
				}
				stmt.close();
				valueXmlString.append("</Root>\r\n");			
			}
			else
			{
				errCode = "VTNLLSORD";
			}
			if (!errCode.equals(""))
			{
				errString = itmDBAccess.getErrorString("",errCode,"","",conn);
				return errString;
			}
		}
		catch(SQLException e)
		{
			System.out.println("Exception : Dispatch : actionVoucher " +e.getMessage());
			throw new ITMException(e);
		}
		catch(Exception e)
		{
			System.out.println("Exception : Dispatch : actionHandler " +e.getMessage());
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				conn.close();
				conn = null;
			}
			catch(Exception e){}
		}
		System.out.println("valueXmlString.toString() "+valueXmlString.toString());
		return valueXmlString.toString();		
	}

	private String allocItemTransform(Document dom,Document dom1, String objContext, String xtraParams, Document selDataDom)throws ITMException
	{
		double icQtyOrd = 0.0;
		double alocQty = 0.0;
		
		StringBuffer valueXmlString = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root>\r\n");
		
		String icQtyOrdStr = GenericUtility.getInstance().getColumnValue("quantity", dom);
		String lineNoSord = GenericUtility.getInstance().getColumnValue("line_no__sord", dom);
		String expLev = GenericUtility.getInstance().getColumnValue("exp_lev", dom);
		
		System.out.println("icQtyOrdStr["+icQtyOrdStr+"] lineNoSord [ "+lineNoSord+"]  expLev ["+expLev+"]");
			
		if(icQtyOrdStr != null && icQtyOrdStr.trim().length() > 0)
		{
			icQtyOrd = Double.parseDouble(icQtyOrdStr);
		}
		try
		{
			NodeList detailList = selDataDom.getElementsByTagName("Detail");
			int noOfDetails = detailList.getLength();
			System.out.println("No Of Selected Record:::-["+noOfDetails+"]");
			for(int ctr = 0; ctr < noOfDetails; ctr++)
			{
				if (icQtyOrd <= 0){
					break;
				}	
				
				alocQty = 0.0;
						
		 		valueXmlString.append("<Detail>");
				Node currDetail = detailList.item(ctr);
				
				String lineNo = GenericUtility.getInstance().getColumnValueFromNode("line_no", currDetail);
				String lotNo = GenericUtility.getInstance().getColumnValueFromNode("lot_no", currDetail);
				String lotSl = GenericUtility.getInstance().getColumnValueFromNode("lot_sl", currDetail);
				String lotCode = GenericUtility.getInstance().getColumnValueFromNode("loc_code", currDetail);
				String alocQtyStr = GenericUtility.getInstance().getColumnValueFromNode("qty_alloc", currDetail);
				
				System.out.println("lineNo["+lineNo+"] lotNo [ "+lotNo+"]  lotSl ["+lotSl+"] lotCode ["+lotCode+"] alocQtyStr ["+alocQtyStr+"]");
			
				if(alocQtyStr != null && alocQtyStr.trim().length() > 0){
					alocQty = Double.parseDouble(alocQtyStr);
				} 
				if(alocQty > icQtyOrd){
					alocQty = icQtyOrd;
				}
				icQtyOrd = icQtyOrd - alocQty;
				
				valueXmlString.append("<line_no__sord isSrvCallOnChg='1'>").append(lineNoSord).append("</line_no__sord>");
				valueXmlString.append("<exp_lev isSrvCallOnChg='1'>").append(expLev).append("</exp_lev>");
				valueXmlString.append("<loc_code>").append(lotCode).append("</loc_code>"); //Gulzar 08-01-07 shifted to above of lot_no
				valueXmlString.append("<lot_no>").append(lotNo).append("</lot_no>");
				valueXmlString.append("<lot_sl isSrvCallOnChg='1'>").append(lotSl).append("</lot_sl>");				
				valueXmlString.append("<quantity isSrvCallOnChg='1'>").append(alocQty).append("</quantity>");
				valueXmlString.append("</Detail>");				
			}
			valueXmlString.append("</Root>");
		}
		catch(ITMException itme)
		{
			throw itme;
		}
		catch(Exception e)
		{
			throw new ITMException(e);
		}
		return valueXmlString.toString();
	}

	private String actionStock(Document dom,Document dom1, String objContext, String xtraParams) throws RemoteException , ITMException
	{
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		String sql = "";
		String errCode = "";
		String itemCode = "",locCode = "",quantity = "",siteCode = "",available = "",stkItemCode = "";
		String stklotNo = "",stklotSl = "",stkAllocQty = "";
		java.sql.Date stkMfgDate = null;
		String stkMfgDate1 = ""; 
		java.sql.Date stkExpDate = null;
		String stkExpDate1 = "";
		String stkquantity = "";
		String stkLocCode = "";
		String stkNoArt = "" ;
		String stkRate = "";
		String balanceQty = "";
		String trackShelfLife = "";
		GenericUtility genericUtility = GenericUtility.getInstance();
		StringBuffer valueXmlString = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root>\r\n");
		ConnDriver connDriver = new ConnDriver();
		try
		{	
		   	conn = connDriver.getConnectDB("DriverITM");
			stmt = conn.createStatement();
			//Getting values from dom (current form), dom1(form no 1)
			itemCode	= genericUtility.getColumnValue("item_code",dom);
			locCode		= genericUtility.getColumnValue("loc_code",dom);
			quantity	= genericUtility.getColumnValue("quantity",dom);
			siteCode	= genericUtility.getColumnValue("site_code",dom1);
			available	= "Y";
			System.out.println("itemCode:"+itemCode+":locCode:"+locCode+":quantity:"+quantity+":siteCode:"+siteCode+":");
			if(locCode == null || locCode.trim().length() == 0)
			{
			  locCode = "%";
			}
			else
			{
				locCode = locCode.trim() + "%";
			}//end if
			sql="SELECT (CASE WHEN TRACK_SHELF_LIFE IS NULL THEN 'N' ELSE TRACK_SHELF_LIFE END) "+
				" FROM ITEM WHERE ITEM_CODE ='"+itemCode+"'" ;
			rs = stmt.executeQuery(sql);
			if(rs.next())
			{
				trackShelfLife =rs.getString(1);
			}
			if(trackShelfLife.equalsIgnoreCase("N"))
			{
				sql="SELECT STOCK.ITEM_CODE,STOCK.LOT_NO, STOCK.LOT_SL,STOCK.ALLOC_QTY, STOCK.MFG_DATE, "+
					"  STOCK.EXP_DATE, STOCK.QUANTITY,STOCK.QUANTITY - STOCK.ALLOC_QTY AS BALANCE_QTY, " + 
					" STOCK.LOC_CODE,STOCK.NO_ART,STOCK.RATE "+
					"  FROM STOCK,INVSTAT "+
					"  WHERE ( STOCK.INV_STAT = INVSTAT.INV_STAT ) AND  " + 
					" ( ( STOCK.ITEM_CODE = '"+itemCode+"' ) AND " + 
					" ( STOCK.LOC_CODE LIKE '"+locCode+"' ) AND  " + 
					" ( STOCK.SITE_CODE = '"+siteCode+"' ) AND  " + 
					" ( STOCK.QUANTITY > 0 ) AND  " + 
					" ( INVSTAT.AVAILABLE = '"+available+"' )  AND " + 
					" ( INVSTAT.STAT_TYPE = 'M' ) ) " ;
			}
			else
			{
				sql="SELECT STOCK.ITEM_CODE,STOCK.LOT_NO, STOCK.LOT_SL,STOCK.ALLOC_QTY, STOCK.MFG_DATE, "+
					"  STOCK.EXP_DATE, STOCK.QUANTITY,STOCK.QUANTITY - STOCK.ALLOC_QTY AS BALANCE_QTY, " + 
					" STOCK.LOC_CODE,STOCK.NO_ART,STOCK.RATE "+
					"  FROM STOCK,INVSTAT "+
					"  WHERE ( STOCK.INV_STAT = INVSTAT.INV_STAT ) AND  " + 
					" ( ( STOCK.ITEM_CODE = '"+itemCode+"' ) AND " + 
					" ( STOCK.LOC_CODE LIKE '"+locCode+"' ) AND  " + 
					" ( STOCK.SITE_CODE = '"+siteCode+"' ) AND  " + 
					" ( STOCK.QUANTITY > 0 ) AND  " + 
					" ( INVSTAT.AVAILABLE = '"+available+"' )  AND " + 
					" ( INVSTAT.STAT_TYPE = 'M' ) ) ORDER BY STOCK.EXP_DATE ASC ";
			}
			System.out.println("Despatch:actionStock:sql:"+sql);
			rs = stmt.executeQuery(sql);
			SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
			while(rs.next())
			{
				stkItemCode = rs.getString("ITEM_CODE");
				stklotNo	= rs.getString("LOT_NO");
				stklotSl	= rs.getString("LOT_SL");
				stkAllocQty = rs.getString("ALLOC_QTY");
				stkMfgDate	= rs.getDate("MFG_DATE");
				if(stkMfgDate != null)
				{
					stkMfgDate1 = sdf.format(stkMfgDate);
				}
				stkExpDate = rs.getDate("EXP_DATE");
				if(stkExpDate != null)
				{
					stkExpDate1 = sdf.format(stkExpDate); 
				}
				stkquantity = rs.getString("QUANTITY");
				balanceQty = rs.getString("BALANCE_QTY");
				stkLocCode = rs.getString("LOC_CODE");
				stkNoArt = rs.getString("NO_ART");
				stkRate =rs.getString("RATE");
//				if (Integer.parseInt(balanceQty) > 0)
				if (Double.parseDouble(balanceQty) > 0)		
				{	
					valueXmlString.append("<Detail>\r\n");
					valueXmlString.append("<item_code>").append("<![CDATA[").append(stkItemCode.trim()).append("]]>").append("</item_code>\r\n");
					valueXmlString.append("<loc_code>").append("<![CDATA[").append(stkLocCode.trim()).append("]]>").append("</loc_code>\r\n");
					valueXmlString.append("<mfg_date>").append("<![CDATA[").append(stkMfgDate1).append("]]>").append("</mfg_date>\r\n");
					valueXmlString.append("<exp_date>").append("<![CDATA[").append(stkExpDate1).append("]]>").append("</exp_date>\r\n");
					//valueXmlString.append("<quantity>").append("<![CDATA[").append(balanceQty.trim()).append("]]>").append("</quantity>\r\n"); //Gulzar 13/03/07
					valueXmlString.append("<quantity>").append("<![CDATA[").append(stkquantity.trim()).append("]]>").append("</quantity>\r\n"); //Gulzar 13/03/07
					valueXmlString.append("<alloc_qty>").append("<![CDATA[").append(stkAllocQty).append("]]>").append("</alloc_qty>\r\n");
					valueXmlString.append("<balance_qty>").append("<![CDATA[").append(balanceQty).append("]]>").append("</balance_qty>\r\n"); // Un-Commented By Gulzar 13/03/07
					valueXmlString.append("<no_art>").append("<![CDATA[").append(stkNoArt).append("]]>").append("</no_art>\r\n");
					valueXmlString.append("<rate>").append("<![CDATA[").append(stkRate).append("]]>").append("</rate>\r\n");
					valueXmlString.append("<lot_no>").append("<![CDATA[").append(stklotNo.trim()).append("]]>").append("</lot_no>\r\n");
					valueXmlString.append("<lot_sl>").append("<![CDATA[").append(stklotSl.trim()).append("]]>").append("</lot_sl>\r\n");
					valueXmlString.append("</Detail>\r\n");
				}
			}//end While loop
   			valueXmlString.append("</Root>\r\n");
		}
		catch(Exception e)
		{
			System.out.println("Exception :Despatch :" + e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				System.out.println("Closing Connection.....");
				conn.close();
			}catch(Exception e){}
		}
		System.out.println("Despatch:actionStock:Final Value :valueXmlString.toString():"+valueXmlString.toString());
		return valueXmlString.toString();
	}
	/* -- Commented and Changes Below - Gulzar - 18/01/08
	private String stockTransform(Document dom,Document dom1, String objContext, String xtraParams, Document selDataDom)throws ITMException
	{
		StringBuffer valueXmlString = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root>\r\n");
		try
		{
			String icQtyOrdStr = GenericUtility.getInstance().getColumnValue("quantity", dom);
			double icQtyOrd = 0.0;
			if(icQtyOrdStr != null && icQtyOrdStr.trim().length() > 0)
			{
				icQtyOrd = Double.parseDouble(icQtyOrdStr);
			}
			String lineNoSord = GenericUtility.getInstance().getColumnValue("line_no__sord", dom);
			String expLevel = GenericUtility.getInstance().getColumnValue("exp_lev", dom);
			String packInstr = GenericUtility.getInstance().getColumnValue("pack_instr", dom);
			NodeList detailList = selDataDom.getElementsByTagName("Detail");
			int noOfDetails = detailList.getLength();
			for(int ctr = 0; ctr < noOfDetails; ctr++)
			{
				if (icQtyOrd > 0)
				{
					valueXmlString.append("<Detail>");
					Node currDetail = detailList.item(ctr);
					String lotNo = GenericUtility.getInstance().getColumnValueFromNode("lot_no", currDetail);
					String lotSl = GenericUtility.getInstance().getColumnValueFromNode("lot_sl", currDetail);
					System.out.println("Lot No In stockTransform :: " + lotNo);
					System.out.println("Lot Sl In stockTransform :: " + lotSl);
					if((lotSl == null) || (lotSl != null && lotSl.trim().length() == 0))
					{
						lotSl = " ";
					}
					String lotCode = GenericUtility.getInstance().getColumnValueFromNode("loc_code", currDetail);
					String cQtyStr = GenericUtility.getInstance().getColumnValueFromNode("quantity", currDetail);
					System.out.println("Location Code In stockTransform In Curr Detail :: " + lotCode);
					double cQty = 0.0;
					if(cQtyStr != null && cQtyStr.trim().length() > 0)
					{
						cQty = Double.parseDouble(cQtyStr);
					}
					String cAllocQtyStr = GenericUtility.getInstance().getColumnValueFromNode("alloc_qty", currDetail);
					System.out.println("Alloc Quantity [cAllocQtyStr] : In stockTransform In Curr Detail Before Parsing:: " + cAllocQtyStr);
					double cAllocQty = 0.0;
					if(cAllocQtyStr != null && cAllocQtyStr.trim().length() > 0)
					{
						cAllocQty = Double.parseDouble(cAllocQtyStr);
					}
					double balQty = cQty - cAllocQty;
					if (balQty > icQtyOrd)
					{
						balQty  = icQtyOrd;
					}
					valueXmlString.append("<line_no__sord isSrvCallOnChg='1'>").append(lineNoSord).append("</line_no__sord>");
					valueXmlString.append("<exp_lev isSrvCallOnChg='1'>").append(expLevel).append("</exp_lev>");
					valueXmlString.append("<loc_code>").append(lotCode).append("</loc_code>");
					valueXmlString.append("<lot_no>").append(lotNo).append("</lot_no>");
					valueXmlString.append("<lot_sl isSrvCallOnChg='1'>").append(lotSl).append("</lot_sl>");
					valueXmlString.append("<quantity isSrvCallOnChg='1'>").append(balQty).append("</quantity>");
					valueXmlString.append("<pending_qty isSrvCallOnChg='0'>").append(icQtyOrd).append("</pending_qty>");
					valueXmlString.append("</Detail>");
					icQtyOrd = icQtyOrd - balQty;
				}//if (icQtyOrd > 0)
			}
			valueXmlString.append("</Root>");
		}
		catch(ITMException itme)
		{
			throw itme;
		}
		catch(Exception e)
		{
			throw new ITMException(e);
		}
		return valueXmlString.toString();
	}*///End Comment Gulzar - 18/01/08

	private String stockTransform(Document dom,Document dom1, String objContext, String xtraParams, Document selDataDom)throws ITMException
	{
		Connection conn = null;
		Statement stmtTemp = null;
		//Statement stmt = null;
		ResultSet rsTemp = null;
		
		String sqlTemp = "";
		String sordNo = "";	 
		String itemCode = "";
		String itemCodeOrd = "";
		String itemDescr = "";
		String orderedQty = "";
		String siteCodeDet = "";
		String unit1 = "";
		String unitStd1 = "";
		String convQtyStduom = "";
		String itemType = "";
		String netWtUnit = "";
		String rateUnit = "";
		String rateOpt = "";
		String rateUnitSord = "";
		String qtyDetailStr = "";
		String custCode = "";
		String packCode = "";
		String locGroup = "";
		String orderType = "";
		String priceList = "";
		String priceListClg = "";
		String siteCodeMfg = "";
		String suppCodeMfg = "";
		String dimension = "";
		String trackShelfLife = "";
		String taxClass = "";
		String taxChap = "";
		String taxEnv = "";
		String lineNoSord = "";
		String expLevel = "";
		String packInstr = "";
		String lotNo = "";
		String lotSl = "";
		String locCode = "";
		String cQtyStr = "";
		String cAllocQtyStr = "";
		String icQtyOrdStr = "";
		String applyPrice = "";

		double inputQty = 0d ;
		double qtyStk = 0d;
		double conv = 0d; 
		double discount =0d ;
		double rateClg = 0d;
		double quantityStduom = 0d;
		double rateStduom = 0d;
		double grossWeight = 0d;
		double tareWeight = 0d;
		double netWeight = 0d;
		double grossWtPerArt = 0d;
		double tareWtPerArt = 0d;
		double qtyPerArt = 0d;
		double palletWt = 0d;
		double grossPer = 0d;
		double netPer = 0d;
		double tarePer = 0d;
		double noArt = 0d;
		double packQty = 0d;
		double convRtuomStd = 0d;
		double netWtPerArt = 0d ;
		double cQty = 0d;
		double cAllocQty = 0d;
		double balQty = 0d;
		double discAmt = 0d;
		double qtyStd = 0d;
		double packGrossWeight = 0d;
		double packNettWeight = 0d;

		int minShelfLife = 0;
		int maxShelfLife = 0;

		java.sql.Date expDate = null;
		java.sql.Date mfgDate = null;

		ArrayList qtyFact = new ArrayList();

		ITMDBAccessEJB itmDBAccess = new ITMDBAccessEJB();
		GenericUtility genericUtility = GenericUtility.getInstance();

		StringBuffer valueXmlString = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root>\r\n");
		DecimalFormat df = new DecimalFormat("#########.###");

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

			icQtyOrdStr = genericUtility.getColumnValue("quantity", dom);
			double icQtyOrd = 0.0;
			if(icQtyOrdStr != null && icQtyOrdStr.trim().length() > 0)
			{
				icQtyOrd = Double.parseDouble(icQtyOrdStr);
			}

			lineNoSord		= genericUtility.getColumnValue("line_no__sord", dom);
			expLevel		= genericUtility.getColumnValue("exp_lev", dom);
			packInstr		= genericUtility.getColumnValue("pack_instr", dom);
			sordNo			= genericUtility.getColumnValue("sord_no", dom);
			itemCode		= genericUtility.getColumnValue("item_code", dom);
			itemCodeOrd		= genericUtility.getColumnValue("item_code__ord", dom);
			itemDescr		= genericUtility.getColumnValue("item_descr", dom);
			orderedQty		= genericUtility.getColumnValue("quantity__ord", dom);
			siteCodeDet		= genericUtility.getColumnValue("site_code", dom);
			unit1			= genericUtility.getColumnValue("unit", dom);
			unitStd1		= genericUtility.getColumnValue("unit__std", dom);
			convQtyStduom	= genericUtility.getColumnValue("conv__qty_stduom", dom);

			stmtTemp = conn.createStatement();
			sqlTemp ="SELECT SORDDET.SALE_ORDER, "   
					+"SORDDET.LINE_NO, "   
					+"SORDDET.SITE_CODE, "   
					+"SORDITEM.ITEM_CODE, " 
					+"SORDDET.ITEM_FLG, "   
					+"SORDITEM.QUANTITY, "  
					+"SORDDET.UNIT, "   
					+"SORDDET.DSP_DATE, "   
					+"SORDDET.RATE, "   
					+"SORDDET.DISCOUNT, "   
					+"SORDDET.TAX_AMT, "   
					+"SORDDET.TAX_CLASS, "   
					+"SORDDET.TAX_CHAP, "   
					+"SORDDET.TAX_ENV, "   
					+"SORDDET.NET_AMT, "   
					+"SORDDET.REMARKS, "   
					+"SORDDET.STATUS, "   
					+"SORDDET.STATUS_DATE, "   
					+"SORDDET.CHG_DATE, "   
					+"SORDDET.CHG_USER, "   
					+"SORDDET.CHG_TERM, "   
					+"SORDDET.ITEM_DESCR, "   
					+"SORDDET.UNIT__RATE, "   
					+"SORDDET.CONV__QTY_STDUOM, "   
					+"SORDDET.CONV__RTUOM_STDUOM, "   
					+"SORDDET.UNIT__STD, "   
					+"SORDDET.QUANTITY__STDUOM, "   
					+"SORDDET.RATE__STDUOM, "   
					+"SORDDET.NO_ART, "   
					+"SORDDET.PACK_CODE, "   
					+"SORDDET.LINE_NO__CONTR, "   
					+"SORDDET.PACK_INSTR, "   
					+"SORDDET.SPEC_REF, "   
					+"SORDDET.PACK_QTY, "   
					+"SORDDET.ITEM_SER, "   
					+"SORDDET.RATE__CLG, "   
					+"SORDDET.MFG_CODE, "   
					+"SORDDET.CONTRACT_NO, "   
					+"PACKING.DESCR, "   
					+"SORDDET.SPEC_ID, "   
					+"SORDDET.ORD_VALUE, "   
					+"SORDDET.ITEM_SER__PROM, "   
					+"SORDDET.SPECIFIC_INSTR, "   
					+"SORDITEM.ITEM_CODE__ORD, "  
					+"SORDDET.PALLET_CODE, "   
					+"SORDDET.NO_PALLET, "   
					+"SORDDET.OVER_SHIP_PERC, "   
					+"SORDDET.COMM_PERC_1, "   
					+"SORDDET.COMM_PERC_2, "   
					+"SORDDET.COMM_PERC_3, "   
					+"SORDDET.COMM_PERC_ON_1, "   
					+"SORDDET.COMM_PERC_ON_2, "   
					+"SORDDET.COMM_PERC_ON_3, "   
					+"SORDDET.SALES_PERS_COMM_1, "   
					+"SORDDET.SALES_PERS_COMM_2, "   
					+"SORDDET.SALES_PERS_COMM_3, "   
					+"SORDDET.PRICE_LIST__DISC, "   
					+"SORDDET.RATE__STD, "   
					+"SORDER.FIN_SCHEME, "   
					+"SORDITEM.MAX_SHELF_LIFE, "  
					+"SORDITEM.MIN_SHELF_LIFE, "   
					+"SPACE(250) AS ITEM_SPECS, "   
					+"SPACE(250) AS ST_SHRINK, "   
					+"SORDDET.LOC_TYPE, "   
					+"SPACE(250) AS ST_SCHEME,  "
					+"SORDDET.SORDFORM_NO ,  "  
					+"SORDDET.LINE_NO__SFORM ,  "  
					+"FN_GET_ITMSTK(SORDDET.ITEM_CODE,SORDDET.SITE_CODE) STK_QTY,  "  
					+"SORDDET.APPL_SEG ,  "  
					+"UOM.DESCR,  "
					+"SORDITEM.EXP_LEV, " 
					+"SORDITEM.QTY_ALLOC,  " 
					+"SORDER.LOC_GROUP, "
					+"SORDER.ORDER_TYPE, "
					+"SORDER.CUST_CODE, "
					+"SORDER.PRICE_LIST, "
					+"SORDER.PRICE_LIST__CLG "
					+"FROM SORDDET LEFT OUTER JOIN PACKING ON SORDDET.PACK_CODE = PACKING.PACK_CODE LEFT OUTER JOIN UOM ON SORDDET.UNIT = UOM.UNIT,  "
					+"SORDER, "
					+"SORDITEM  "
					+"WHERE SORDER.SALE_ORDER = SORDDET.SALE_ORDER "
					+"AND SORDITEM.SALE_ORDER = SORDDET.SALE_ORDER "
					+"AND SORDITEM.LINE_NO = SORDDET.LINE_NO "
					+"AND SORDITEM.SALE_ORDER = '"+sordNo+"' "
					+"AND SORDDET.LINE_NO = '" + lineNoSord + "' "
					+"AND SORDITEM.LINE_TYPE = 'I' ORDER BY SORDITEM.LINE_NO , SORDITEM.EXP_LEV";
			System.out.println("sql1 :"+sqlTemp);
			rsTemp = stmtTemp.executeQuery(sqlTemp);
			if (rsTemp.next())
			{
				convRtuomStd	= rsTemp.getDouble("CONV__RTUOM_STDUOM"); 
				rateUnitSord	= rsTemp.getString("UNIT__RATE");
				quantityStduom	= rsTemp.getDouble("QUANTITY__STDUOM");
				rateStduom		= rsTemp.getDouble("RATE__STDUOM");
				custCode		= rsTemp.getString("CUST_CODE");
				packCode		= rsTemp.getString("PACK_CODE");
				convRtuomStd	= rsTemp.getDouble("CONV__RTUOM_STDUOM"); 
				rateUnitSord	= rsTemp.getString("UNIT__RATE");
				locGroup		= rsTemp.getString("LOC_GROUP");
				orderType		= rsTemp.getString("ORDER_TYPE");
				minShelfLife	= rsTemp.getInt("MIN_SHELF_LIFE");
				maxShelfLife	= rsTemp.getInt("MAX_SHELF_LIFE");
				priceList		= rsTemp.getString("PRICE_LIST");
				priceListClg	= rsTemp.getString("PRICE_LIST__CLG");

				if (rateUnitSord == null)
				{
					rateUnitSord = "";
				}
				discount	= rsTemp.getDouble("DISCOUNT"); 
				rateClg		= rsTemp.getDouble("RATE__CLG");
				taxClass	= rsTemp.getString("TAX_CLASS");
				taxChap		= rsTemp.getString("TAX_CHAP"); 
				taxEnv		= rsTemp.getString("TAX_ENV"); 
			}
			rsTemp.close();
			rsTemp = null;
			sqlTemp = "SELECT APPLY_PRICE  FROM BOM WHERE BOM_CODE = '"+ itemCode +"' ";
			System.out.println("sql1 :"+sqlTemp);
			rsTemp = stmtTemp.executeQuery(sqlTemp);
			if ( rsTemp.next() )
			{
				applyPrice = rsTemp.getString("APPLY_PRICE");
			}
			//stmtTemp.close();
			//stmtTemp = null;
			rsTemp.close();
			rsTemp = null;
			if ( applyPrice == null )
			{
				discAmt = (discount/100) * ( quantityStduom *  rateStduom ); 
			}
			System.out.println("discAmt :"+discAmt);
			if ( packCode != null && packCode.trim().length() > 0 )
			{
				sqlTemp = "SELECT GROSS_WEIGHT, NETT_WEIGHT FROM PACKING WHERE PACK_CODE = '"+ packCode +"' ";
				System.out.println("sqlTemp :: "+sqlTemp);
				//stmtTemp = conn.createStatement();
				rsTemp = stmtTemp.executeQuery(sqlTemp);
				if ( rsTemp.next() )
				{
					packGrossWeight = rsTemp.getDouble("GROSS_WEIGHT");
					packNettWeight	= rsTemp.getDouble("NETT_WEIGHT");
				}
				stmtTemp.close();
				stmtTemp = null;
				rsTemp.close();
				rsTemp = null;
			}

			NodeList detailList = selDataDom.getElementsByTagName("Detail");
			int noOfDetails = detailList.getLength();
			for(int ctr = 0; ctr < noOfDetails; ctr++)
			{
				if (icQtyOrd > 0)
				{
					Node currDetail = detailList.item(ctr);

					lotNo			= genericUtility.getColumnValueFromNode("lot_no", currDetail);
					lotSl			= genericUtility.getColumnValueFromNode("lot_sl", currDetail);
					locCode			= genericUtility.getColumnValueFromNode("loc_code", currDetail);
					cQtyStr			= genericUtility.getColumnValueFromNode("quantity", currDetail);
					cAllocQtyStr	= genericUtility.getColumnValueFromNode("alloc_qty", currDetail);

					System.out.println("Lot No In stockTransform :: " + lotNo);
					System.out.println("Lot Sl In stockTransform :: " + lotSl);
					System.out.println("Location Code In stockTransform In Curr Detail :: " + locCode);
					System.out.println("Alloc Quantity [cAllocQtyStr] : In stockTransform In Curr Detail Before Parsing:: " + cAllocQtyStr);

					if((lotSl == null) || (lotSl != null && lotSl.trim().length() == 0))
					{
						lotSl = " ";
					}
					
					if(cQtyStr != null && cQtyStr.trim().length() > 0)
					{
						cQty = Double.parseDouble(cQtyStr);
					}
					
					if(cAllocQtyStr != null && cAllocQtyStr.trim().length() > 0)
					{
						cAllocQty = Double.parseDouble(cAllocQtyStr);
					}

					balQty = cQty - cAllocQty;

					if (balQty > icQtyOrd)
					{
						balQty  = icQtyOrd;
					}
					sqlTemp ="SELECT A.EXP_DATE, " 
							+"A.QUANTITY, "
							+"A.SITE_CODE__MFG, "
							+"A.MFG_DATE, "
							+"A.PACK_CODE, " 
							+"A.GROSS_WEIGHT, "
							+"A.TARE_WEIGHT, "
							+"A.NET_WEIGHT, " 
							+"A.DIMENSION, "
							+"A.SUPP_CODE__MFG, " 
							+"A.QTY_PER_ART, "
							+"A.GROSS_WT_PER_ART, " 
							+"A.TARE_WT_PER_ART ,"  
							+"A.PALLET_WT  "
							+"FROM STOCK A " 
							+"WHERE A.ITEM_CODE = '"+itemCode+"' "  
							+"AND A.SITE_CODE = '"+siteCodeDet+"' "  
							+"AND A.LOC_CODE = '"+locCode+"' "  
							+"AND A.LOT_NO = '"+lotNo+"' "   
							+"AND A.LOT_SL = '"+lotSl+"' ";   
					System.out.println("sql2 :"+sqlTemp);
					stmtTemp = conn.createStatement();
					rsTemp = stmtTemp.executeQuery(sqlTemp);
					if (rsTemp.next())
					{
						expDate			= rsTemp.getDate("EXP_DATE");
						qtyStk			= rsTemp.getDouble("QUANTITY");
						mfgDate			= rsTemp.getDate("MFG_DATE");
						siteCodeMfg		= rsTemp.getString("SITE_CODE__MFG");
						grossWeight		= rsTemp.getDouble("GROSS_WEIGHT");
						tareWeight		= rsTemp.getDouble("TARE_WEIGHT");
						netWeight		= rsTemp.getDouble("NET_WEIGHT");
						suppCodeMfg		= rsTemp.getString("SUPP_CODE__MFG");
						dimension		= rsTemp.getString("DIMENSION");
						grossWtPerArt	= rsTemp.getDouble("GROSS_WT_PER_ART");
						tareWtPerArt	= rsTemp.getDouble("TARE_WT_PER_ART");
						qtyPerArt		= rsTemp.getDouble("QTY_PER_ART");
						palletWt		= rsTemp.getDouble("PALLET_WT");
					}
					rsTemp.close();
					rsTemp = null;
					sqlTemp ="SELECT (CASE WHEN TRACK_SHELF_LIFE IS NULL THEN 'N' ELSE TRACK_SHELF_LIFE END) AS TRACK_SHELF_LIFE , DESCR " 
							+"FROM ITEM WHERE ITEM_CODE = '"+itemCode+"' "; 
					System.out.println("sql3  :"+sqlTemp);
					rsTemp = stmtTemp.executeQuery(sqlTemp);
					if (rsTemp.next())
					{
						trackShelfLife = rsTemp.getString("TRACK_SHELF_LIFE");
						System.out.println("trackShelfLife :"+trackShelfLife);
					}
					rsTemp.close();
					rsTemp = null;
					/*-- Commented - and Change below - Gulzar - 31/01/08 - The Calculation done as per case "lot_no of nvo_bo_despatch
					if (qtyStk > 0)
					{
						grossPer = (grossWeight / qtyStk) ;
						grossPer = df.parse(df.format(grossPer)).doubleValue();
						System.out.println("grossPer :"+grossPer);
						netPer 	=  (netWeight / qtyStk);
						netPer = df.parse(df.format(netPer)).doubleValue();
						System.out.println("netPer 	:"+netPer);
						tarePer	=  (tareWeight / qtyStk);
						tarePer = df.parse(df.format(tarePer)).doubleValue();
						System.out.println("tarePer	:"+tarePer);									
						grossWeight = (balQty * grossPer);
						System.out.println("grossWeight :"+grossWeight);
						netWeight = (balQty * netPer);
						System.out.println("netWeight :"+netWeight);
						tareWeight = (balQty * tarePer);
						System.out.println("tareWeight :"+tareWeight);	
					}*/
					//End Comment - Gulzar - 31/01/08
					netWtPerArt = convRtuomStd;
					sqlTemp = "SELECT ITEM_TYPE, UNIT__NETWT, UNIT__RATE FROM ITEM WHERE ITEM_CODE = '" + itemCodeOrd + "'";
					System.out.println("sql4 :"+sqlTemp);
					rsTemp = stmtTemp.executeQuery(sqlTemp);
					if (rsTemp.next())
					{
						itemType = rsTemp.getString("ITEM_TYPE");
						if (itemType == null)
						{
							itemType = "";
						}
						netWtUnit = rsTemp.getString("UNIT__NETWT");
						if (netWtUnit == null)
						{
							netWtUnit = "";
						}
						rateUnit = rsTemp.getString("UNIT__RATE");
						if (rateUnit == null)
						{
							rateUnit = "";
						}
					}
					rsTemp.close();
					rsTemp = null;
					if (itemType.trim().length() > 0)
					{
						sqlTemp = "SELECT RATE_OPT FROM ITEM_TYPE WHERE ITEM_TYPE = '" + itemType + "'" ;
						System.out.println("sql5 :"+sqlTemp);
						rsTemp = stmtTemp.executeQuery(sqlTemp);
						if (rsTemp.next())
						{
							rateOpt = rsTemp.getString("RATE_OPT");
							if (rateOpt == null)
							{
								rateOpt = "";
							}
						}
						rsTemp.close();
						rsTemp = null;
						if (rateOpt == "1")
						{
							if ( netWtUnit.trim().equals(rateUnit.trim()))
							{
								if (!unit1.trim().equals(rateUnitSord.trim()))
								{
									netWtPerArt = grossWtPerArt - tareWtPerArt;
								}
							}
						}
					}

					double shipperQtyNew = 0d;
					double integralQtyNew = 0d;
					double looseQty = 0d;
					double[] noArtInfo1 = getNoArt(siteCodeDet , custCode , itemCode , packCode , balQty , "S" , 0 , 0 , conn); 
					double balQty1 = balQty - ( shipperQtyNew * noArtInfo1[0]);
					double[] noArtInfo2 = getNoArt(siteCodeDet , custCode , itemCode , packCode , balQty1 , "I" , 0 , 0 , conn); 
					integralQtyNew = noArtInfo2[2];
					shipperQtyNew = shipperQtyNew * noArtInfo1[0];
					integralQtyNew = integralQtyNew * noArtInfo2[0];
					looseQty = balQty - ( shipperQtyNew + integralQtyNew );

					qtyDetailStr = "Shipper Quantity = " + shipperQtyNew +"  Integral Quantity = " + integralQtyNew + "  Loose Quantity = " + looseQty ;
					if (!unit1.equals(unitStd1))
					{
						qtyFact = itmDBAccess.getConvQuantityFact(unitStd1, unit1, itemCode, balQty, conv, conn);
						System.out.println("qtyFact.get(1) :"+qtyFact.get(0));
					}
					else
					{
						qtyFact.add(Integer.toString(1));
						qtyFact.add(Double.toString(balQty));
					}
					System.out.println("qtyPerArt::::::::::::: "+qtyPerArt);
					if ( qtyPerArt > 0 )
					{
						inputQty = Double.parseDouble(qtyFact.get(1).toString());
						System.out.println("inputQty::::::::::::: "+inputQty);
						noArt = new Double((Double.parseDouble(qtyFact.get(1).toString())) / qtyPerArt).intValue();
						System.out.println("noArt [qtyFact.get(1).toString())) / qtyPerArt)] ::"+noArt);
						grossWeight = (df.parse(df.format(grossWtPerArt / qtyPerArt)).doubleValue()) * qtyStk;
						tareWeight	= (df.parse(df.format(tareWtPerArt / qtyPerArt)).doubleValue()) * qtyStk;
						netWeight	= df.parse(df.format(grossWeight - tareWeight)).doubleValue();
						System.out.println("grossWeight [if ( qtyPerArt > 0 )] ::"+grossWeight);
						System.out.println("tareWeight [if ( qtyPerArt > 0 )] ::"+tareWeight);
						System.out.println("netWeight [if ( qtyPerArt > 0 )] ::"+netWeight);
					}
					if (noArt > 0)
					{
						packQty = 0d;
						packQty = (Double.parseDouble(qtyFact.get(1).toString())) / noArt;
						packQty = df.parse(df.format(packQty)).doubleValue(); 
					}
					if ( grossWeight == 0 ) //Gulzar - 25/01/08
					{
						grossWeight = packGrossWeight * noArt;
						netWeight = packNettWeight * noArt;
						tareWeight = grossWeight - netWeight;
					}

					grossWeight = df.parse(df.format(grossWeight)).doubleValue(); 	
					netWeight	= df.parse(df.format(netWeight)).doubleValue(); 	
					tareWeight	= df.parse(df.format(tareWeight)).doubleValue(); 	
					balQty		= df.parse(df.format(balQty)).doubleValue(); 				
					icQtyOrd	= df.parse(df.format(icQtyOrd)).doubleValue(); 	
					
					valueXmlString.append("<Detail>");
					valueXmlString.append("<sord_no isSrvCallOnChg='0'>").append("<![CDATA[").append(sordNo).append("]]>").append("</sord_no>\r\n"); 
					valueXmlString.append("<line_no__sord isSrvCallOnChg='1'>").append(lineNoSord).append("</line_no__sord>");
					valueXmlString.append("<exp_lev isSrvCallOnChg='0'>").append(expLevel).append("</exp_lev>");
					valueXmlString.append("<item_code isSrvCallOnChg='0'>").append("<![CDATA[").append(itemCode.trim()).append("]]>").append("</item_code>\r\n");
					valueXmlString.append("<item_code__ord isSrvCallOnChg='0'>").append("<![CDATA[").append(itemCodeOrd).append("]]>").append("</item_code__ord>\r\n");
					valueXmlString.append("<item_descr isSrvCallOnChg='0'>").append("<![CDATA[").append(itemDescr).append("]]>").append("</item_descr>\r\n");
					valueXmlString.append("<quantity__ord isSrvCallOnChg='0'>").append("<![CDATA[").append(orderedQty).append("]]>").append("</quantity__ord>\r\n");
					valueXmlString.append("<site_code isSrvCallOnChg='0'>").append("<![CDATA[").append(siteCodeDet).append("]]>").append("</site_code>\r\n");
					valueXmlString.append("<quantity_real isSrvCallOnChg='0'>").append("<![CDATA[").append(balQty).append("]]>").append("</quantity_real>\r\n");
					valueXmlString.append("<unit isSrvCallOnChg='0'>").append("<![CDATA[").append(unit1).append("]]>").append("</unit>\r\n");
					valueXmlString.append("<unit__std isSrvCallOnChg='0'>").append("<![CDATA[").append(unitStd1).append("]]>").append("</unit__std>\r\n");
					valueXmlString.append("<conv__qty_stduom isSrvCallOnChg='0'>").append("<![CDATA[").append(convQtyStduom).append("]]>").append("</conv__qty_stduom>\r\n");
					valueXmlString.append("<conv__rtuom_stduom isSrvCallOnChg='0'>").append("<![CDATA[").append(netWtPerArt).append("]]>").append("</conv__rtuom_stduom>\r\n"); 
					
					valueXmlString.append("<pallet_wt isSrvCallOnChg='0'>").append("<![CDATA[").append(palletWt).append("]]>").append("</pallet_wt>\r\n");
					//loc_code isSrvCallOnChg='0' to loc_code isSrvCallOnChg='1' by msalam on 051108 as rate__clg was not coming
					valueXmlString.append("<loc_code isSrvCallOnChg='1'>").append(locCode).append("</loc_code>");
					valueXmlString.append("<pack_qty isSrvCallOnChg='0'>").append("<![CDATA[").append(packQty).append("]]>").append("</pack_qty>\r\n"); 
					valueXmlString.append("<quantity__stduom isSrvCallOnChg='0'>").append("<![CDATA[").append(balQty).append("]]>").append("</quantity__stduom>\r\n");
					valueXmlString.append("<qty_details isSrvCallOnChg='0'>").append("<![CDATA[").append(qtyDetailStr).append("]]>").append("</qty_details>\r\n");  
					valueXmlString.append("<discount isSrvCallOnChg='0'>").append("<![CDATA[").append(discount).append("]]>").append("</discount>\r\n");
					valueXmlString.append("<disc_amt isSrvCallOnChg='0'>").append("<![CDATA[").append(discAmt).append("]]>").append("</disc_amt>\r\n");

					valueXmlString.append("<pending_qty isSrvCallOnChg='0'>").append(icQtyOrd).append("</pending_qty>");
					valueXmlString.append("<gross_weight isSrvCallOnChg='0'>").append("<![CDATA[").append(grossWeight).append("]]>").append("</gross_weight>\r\n");
					valueXmlString.append("<nett_weight isSrvCallOnChg='0'>").append("<![CDATA[").append(netWeight).append("]]>").append("</nett_weight>\r\n");
					valueXmlString.append("<tare_weight isSrvCallOnChg='0'>").append("<![CDATA[").append(tareWeight).append("]]>").append("</tare_weight>\r\n");
					valueXmlString.append("<no_art isSrvCallOnChg='0'>").append("<![CDATA[").append(noArt).append("]]>").append("</no_art>\r\n");
					valueXmlString.append("<dimension isSrvCallOnChg='0'>").append("<![CDATA[").append((dimension == null) ? "":dimension).append("]]>").append("</dimension>\r\n"); 
					valueXmlString.append("<site_code__mfg isSrvCallOnChg='0'>").append("<![CDATA[").append(siteCodeMfg).append("]]>").append("</site_code__mfg>\r\n");
					//valueXmlString.append("<rate__stduom isSrvCallOnChg='0'>").append("<![CDATA[").append(rateStduom).append("]]>").append("</rate__stduom>\r\n"); 
					//next line commented as it gets set from lot no item change on 041108 by msalam
					//valueXmlString.append("<rate__clg isSrvCallOnChg='0'>").append("<![CDATA[").append(rateClg).append("]]>").append("</rate__clg>\r\n"); 

					valueXmlString.append("<quantity isSrvCallOnChg='0'>").append(balQty).append("</quantity>");
					valueXmlString.append("<tax_class isSrvCallOnChg='0'>").append("<![CDATA[").append((taxClass == null) ?"":taxClass).append("]]>").append("</tax_class>\r\n");
					valueXmlString.append("<tax_chap isSrvCallOnChg='0'>").append("<![CDATA[").append((taxChap == null) ?"":taxChap).append("]]>").append("</tax_chap>\r\n"); 
					valueXmlString.append("<tax_env isSrvCallOnChg='0'>").append("<![CDATA[").append((taxEnv == null) ?"":taxEnv).append("]]>").append("</tax_env>\r\n"); 
					valueXmlString.append("<lot_sl isSrvCallOnChg='0'>").append(lotSl).append("</lot_sl>");
					valueXmlString.append("<lot_no isSrvCallOnChg='1'>").append(lotNo).append("</lot_no>");
					valueXmlString.append("</Detail>");
					/*
					valueXmlString.append("<line_no__sord isSrvCallOnChg='1'>").append(lineNoSord).append("</line_no__sord>");
					valueXmlString.append("<exp_lev isSrvCallOnChg='1'>").append(expLevel).append("</exp_lev>");
					valueXmlString.append("<loc_code>").append(locCode).append("</loc_code>");
					valueXmlString.append("<lot_no>").append(lotNo).append("</lot_no>");
					valueXmlString.append("<lot_sl isSrvCallOnChg='1'>").append(lotSl).append("</lot_sl>");
					valueXmlString.append("<quantity isSrvCallOnChg='1'>").append(balQty).append("</quantity>");
					valueXmlString.append("<pending_qty isSrvCallOnChg='0'>").append(icQtyOrd).append("</pending_qty>");
					valueXmlString.append("</Detail>");
					*/
					icQtyOrd = icQtyOrd - balQty;
				}//if (icQtyOrd > 0)
			}
			valueXmlString.append("</Root>");
		}
		catch(ITMException itme)
		{
			throw itme;
		}
		catch(Exception e)
		{
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				System.out.println("Closing Connection................");
				if (stmtTemp != null)
				{
					stmtTemp.close();
					stmtTemp = null;
				}
				if (conn != null)
				{
					conn.close();
					conn = null;
				}
			}
			catch(Exception e)
			{
				throw new ITMException(e);
			}
		}
		System.out.println("Print valueXmlString.toString() \n["+valueXmlString.toString()+"]");
		return valueXmlString.toString();
	}
	
/*--- Commented And Changes Below For Making as Service Handler 4 - Gulzar - 23-04-07
	private String actionGetPackList(Document dom, Document dom1, String objContext, String xtraParams) throws RemoteException, ITMException
	{
		String saleOrder = "",despId = "",sqlOrderNoSo = "",sqlOrderNoPo = "",sqlOrderNoDo = "";
		String orderNo = "",orderNoSo = "",orderNoPo = "",orderNoDo = "",invOrderNo = "";		
		String sql1 = "", sql = "";
		String orderType = "",spaces = "",expLev = "",saleOrd = "";
		String taxClass = "", taxChap = "", taxEnv = "", unit = "",unitStd = "";  
		String lineNoOrd ="", itemCode = "", locCode = "", lotNo = "", lotSL = "", packInstr  = "", dimension = "";   
		double quantity = 0, grossWeight = 0, tareWeight = 0, netWeight = 0, conv = 0, qtyStd = 0; 
		double detQty = 0;
		long   noArt = 0;
		ArrayList qty = new ArrayList();
		ArrayList orderNoArrList = new ArrayList();
		ResultSet rs = null;
		String detailCnt = "0";
		ResultSet rs1 = null;
		Connection conn = null;
		Statement stmt = null;
		Statement stmt1 = null;
		StringBuffer valueXmlString = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root>\r\n");
		GenericUtility genericUtility = GenericUtility.getInstance();
		ConnDriver connDriver = new ConnDriver();
		ITMDBAccessEJB itmDBAccess = new ITMDBAccessEJB();
		String origInvOrderNo = "",sorder = "N";
		try
		{
			//detailCnt = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"detCnt");
			//if(detailCnt.equals("0"))
			//{
				conn = connDriver.getConnectDB("DriverITM");
				stmt = conn.createStatement();
				stmt1 = conn.createStatement();

				saleOrder = genericUtility.getColumnValue("sord_no",dom);
				despId = genericUtility.getColumnValue("desp_id",dom1);
				System.out.println("saleOrder :"+saleOrder);
				System.out.println("despId :"+despId);

				sqlOrderNoSo = "SELECT ORDER_NO FROM INV_PACK WHERE ORDER_NO ='"+saleOrder+"'"+" AND "
							  +"ORDER_TYPE IN ('S','I') AND CONFIRMED ='Y'";
				System.out.println("sqlOrderNoSo :"+sqlOrderNoSo);
				rs = stmt.executeQuery(sqlOrderNoSo);
				while (rs.next())
				{
					orderNoSo = rs.getString(1);
					if(orderNoSo != null){
						orderNoArrList.add(orderNoSo);
					}
					System.out.println("orderNoSo :"+orderNoSo);
				}
				stmt.close();
				stmt = null;
				sqlOrderNoPo = "SELECT ORDER_NO FROM INV_PACK WHERE ORDER_NO IN (SELECT PURC_ORDER FROM PORDER WHERE " 
							  +"SALE_ORDER ='"+saleOrder+"'"+") AND ORDER_TYPE = 'P' AND CONFIRMED = 'Y'";
				System.out.println("sqlOrderNoPo :"+sqlOrderNoPo);
				stmt = conn.createStatement();
				rs = stmt.executeQuery(sqlOrderNoPo);
				while (rs.next())
				{
					orderNoPo = rs.getString(1);
					if(orderNoPo != null){
						orderNoArrList.add(orderNoPo);
					}
					System.out.println("orderNoPo :"+orderNoPo);
				}
				stmt.close();
				stmt = null;
				sqlOrderNoDo = "SELECT ORDER_NO FROM INV_PACK WHERE ORDER_NO IN (SELECT DIST_ORDER FROM DISTORDER "
							  +"WHERE SALE_ORDER ='"+saleOrder+"' ) AND ORDER_TYPE ='D' AND CONFIRMED = 'Y'";	
				System.out.println("sqlOrderNoDo :"+sqlOrderNoDo);
				stmt = conn.createStatement();
				rs = stmt.executeQuery(sqlOrderNoDo);
				if (rs.next())
				{
					orderNoDo = rs.getString(1);
					if(orderNoDo != null){
						orderNoArrList.add(orderNoDo);
					}
					System.out.println("orderNoDo :"+orderNoDo);
				}
				stmt.close();
				stmt = null;
				int orderNoListSize = orderNoArrList.size();
				String orderNoTemp = "";
				for(int ctr = 0;ctr < orderNoListSize;ctr++){
					orderNoTemp = orderNoArrList.get(ctr).toString();
					orderNo	= "'".concat(orderNoTemp).concat("',");
				}
				
				System.out.println("orderNo :: "+orderNo);
				
				if(orderNo != null && orderNo.indexOf(",") != -1){
					orderNo = orderNo.substring(0,orderNo.length()-1);
				}else{
					orderNo = "''";
				}
			
				System.out.println("orderNo :: "+orderNo);				
				//orderNo = "'".concat(orderNoSo).concat("'").concat(",").concat("'").concat(orderNoPo).concat("'").concat(",").concat("'").concat(orderNoDo).concat("'");
				
				sql = "SELECT INV_PACK_RCP.TRAN_ID, "   
						 +"INV_PACK_RCP.LINE_NO, "   
						 +"INV_PACK_RCP.ORDER_NO, "   
						 +"INV_PACK_RCP.LINE_NO__ORD, "   
						 +"INV_PACK_RCP.ITEM_CODE, "   
						 +"INV_PACK_RCP.LOC_CODE, "   
						 +"INV_PACK_RCP.LOT_NO, "   
						 +"INV_PACK_RCP.LOT_SL, "   
						 +"INV_PACK_RCP.QUANTITY, "   
						 +"INV_PACK_RCP.UNIT, "   
						 +"INV_PACK_RCP.PACK_CODE, "   
						 +"INV_PACK_RCP.PACK_INSTR, "   
						 +"INV_PACK_RCP.GROSS_WEIGHT, "   
						 +"INV_PACK_RCP.TARE_WEIGHT, "   
						 +"INV_PACK_RCP.NET_WEIGHT, "   
						 +"INV_PACK_RCP.NO_ART, "   
						 +"INV_PACK_RCP.DIMENSION, "   
						 +"'Y' AS PROCESS_YN "  
					+"FROM INV_PACK, INV_PACK_RCP, STOCK "  
					+"WHERE INV_PACK.TRAN_ID = INV_PACK_RCP.TRAN_ID "
					+"AND INV_PACK_RCP.ITEM_CODE = STOCK.ITEM_CODE "
					+"AND INV_PACK.SITE_CODE = STOCK.SITE_CODE "
					+"AND INV_PACK_RCP.LOC_CODE = STOCK.LOC_CODE "
					+"AND INV_PACK_RCP.LOT_NO = STOCK.LOT_NO "
					+"AND INV_PACK_RCP.LOT_SL = STOCK.LOT_SL "
					+"AND INV_PACK.ORDER_NO IN(" +orderNo+") "
					+"AND INV_PACK.CONFIRMED  = 'Y' "
					+"AND STOCK.QUANTITY >= INV_PACK_RCP.QUANTITY "   
					+"ORDER BY INV_PACK_RCP.LINE_NO ASC";

				System.out.println("sql :"+sql);
				
				stmt = conn.createStatement();
				rs = stmt1.executeQuery(sql);
				while (rs.next())// here in PB it opens a window and prompts the user to select records and then the procssing continues for the selected records
				{
					invOrderNo = rs.getString(3);
					origInvOrderNo = invOrderNo;
					System.out.println("invOrderNo :"+invOrderNo);
					sql1 = "SELECT ORDER_TYPE FROM INV_PACK WHERE ORDER_NO ='"+invOrderNo+"'";
					System.out.println("sql1 :"+sql1);
					rs1 = stmt.executeQuery(sql1);
					if(rs1.next())
					{
						orderType = rs1.getString(1);
					}
					System.out.println("orderType :"+orderType);
					if (orderType != null && orderType.equalsIgnoreCase("D"))
					{
						sql1 = "SELECT SALE_ORDER FROM DISTORDER WHERE DIST_ORDER = '"+invOrderNo+"'";
						System.out.println("sql1 :"+sql1);
						rs1 = stmt.executeQuery(sql1);
						if(rs1.next())
						{
							saleOrd = rs1.getString(1);
							System.out.println("saleOrd :"+saleOrd);
						}
						invOrderNo = saleOrd;
					}
					else if (orderType != null && orderType.equalsIgnoreCase("P"))
					{
						sql1 = "SELECT SALE_ORDER FROM PORDER WHERE PURC_ORDER = '"+invOrderNo+"'";
						System.out.println("sql1 :"+sql1);
						rs1 = stmt.executeQuery(sql1);
						if(rs1.next())
						{
							saleOrd = rs1.getString(1);
							System.out.println("saleOrd :"+saleOrd);
						}
						invOrderNo = saleOrd;
					}
					else if(orderType != null && orderType.equalsIgnoreCase("I"))
					{
						sql1 = "SELECT SALE_ORDER FROM DISTORDER WHERE DIST_ORDER = '"+invOrderNo+"'";
						System.out.println("sql1 :"+sql1);
						rs1 = stmt.executeQuery(sql1);
						if(rs1.next())
						{
							saleOrd = rs1.getString(1);
							System.out.println("saleOrd :"+saleOrd);
						}
						if(saleOrd != null && saleOrd.trim().length() > 0){
							invOrderNo = saleOrd;
							sorder = "Y";
						}						
						System.out.println("invOrderNo :"+invOrderNo);
					}
					 
					lineNoOrd = rs.getString(4);
					System.out.println("lineNoOrd :"+lineNoOrd);
					itemCode = rs.getString(5);
					System.out.println("itemCode :"+itemCode);
					locCode = rs.getString(6);
					System.out.println("locCode :"+locCode);
					lotNo = rs.getString(7);
					System.out.println("lotNo :"+lotNo);
					lotSL = rs.getString(8);
					System.out.println("lotSL :"+lotSL);
					quantity = rs.getDouble(9);
					System.out.println("quantity :"+quantity);
					grossWeight = rs.getDouble(13);
					System.out.println("grossWeight :"+grossWeight);
					tareWeight = rs.getDouble(14);
					System.out.println("tareWeight :"+tareWeight);
					netWeight = rs.getDouble(15);
					System.out.println("netWeight :"+netWeight);
					packInstr = rs.getString(12);
					System.out.println("packInstr :"+packInstr);
					noArt = rs.getLong(16);
					System.out.println("noArt :"+noArt);
					dimension = rs.getString(17);
					System.out.println("dimension :"+dimension);
					
					if(sorder == "Y"){
						sql = "select sale_order, line_no__sord from distorder_det where dist_order = '"+origInvOrderNo+"' and line_no = '"+lineNoOrd+"'";
						rs1 = stmt.executeQuery(sql);
						if(rs1.next()){
							invOrderNo = rs1.getString("sale_order");
							lineNoOrd = rs1.getString("line_no__sord");
						}
					}
					System.out.println("lineNoOrd ::"+lineNoOrd);
					if (lineNoOrd.trim().length() == 1)
					{
						spaces = "  ";
					}
					if (lineNoOrd.trim().length() == 2)
					{
						spaces = " ";
					}
					System.out.println("spaces :"+spaces);
					lineNoOrd = spaces.concat(lineNoOrd.trim());
					
					System.out.println("lineNoOrd :"+lineNoOrd);

					sql1 = "SELECT EXP_LEV FROM SORDITEM WHERE SALE_ORDER ='"+invOrderNo+"'"
						  +"AND LINE_NO ='"+lineNoOrd+"'"+" AND LINE_TYPE = 'I'";
					System.out.println("sql1 :"+sql1);
					rs1 = stmt.executeQuery(sql1);
					if(rs1.next())
					{
						expLev = rs1.getString(1);
					}
					System.out.println("expLev :"+expLev);
					sql1 = "SELECT TAX_CLASS,TAX_CHAP,TAX_ENV,UNIT,UNIT__STD FROM SORDDET "
						  +"WHERE SALE_ORDER = '"+invOrderNo+"'"+" AND LINE_NO = '"+lineNoOrd+"'"; 
					System.out.println("sql1 :"+sql1);
					rs1 = stmt.executeQuery(sql1);
					if(rs1.next())
					{
						taxClass = rs1.getString(1);
						System.out.println("taxClass :"+taxClass);
						taxChap = rs1.getString(2);
						System.out.println("taxChap :"+taxChap);
						taxEnv = rs1.getString(3);
						System.out.println("taxEnv :"+taxEnv);
						unit = rs1.getString(4);
						System.out.println("unit :"+unit);
						unitStd = rs1.getString(5);
						System.out.println("unitStd :"+unitStd);
					}
					valueXmlString.append("<Detail>\r\n");
					valueXmlString.append("<desp_id>").append("<![CDATA[").append(despId).append("]]>").append("</desp_id>\r\n");
					valueXmlString.append("<sord_no>").append("<![CDATA[").append(invOrderNo).append("]]>").append("</sord_no>\r\n");
					valueXmlString.append("<line_no__sord isSrvCallOnChg='1'>").append("<![CDATA[").append(lineNoOrd.trim()).append("]]>").append("</line_no__sord>\r\n");
					valueXmlString.append("<exp_lev isSrvCallOnChg='1'>").append("<![CDATA[").append(expLev.trim()).append("]]>").append("</exp_lev>\r\n");
					valueXmlString.append("<unit>").append("<![CDATA[").append(unit).append("]]>").append("</unit>\r\n");
					valueXmlString.append("<unit__std>").append("<![CDATA[").append(unitStd).append("]]>").append("</unit__std>\r\n");
					valueXmlString.append("<unit__std>").append("<![CDATA[").append(unitStd).append("]]>").append("</unit__std>\r\n");
					qtyStd = quantity;
					conv = 0;
					if (!unit.trim().equals(unitStd.trim()))  //REMOVED NOT SYMBOL
					{
						qty = itmDBAccess.getConvQuantityFact(unitStd, unit, itemCode, qtyStd, conv, conn);
						System.out.println("qty :"+qty);
					}
					else
					{
						qty.add(0, new Double(qtyStd));
						qty.add(1,new Double(1));
					}
					detQty = ((Double)qty.get(0)).doubleValue();
					conv = ((Double)qty.get(1)).doubleValue();
					System.out.println("conv :"+conv);
					valueXmlString.append("<quantity>").append("<![CDATA[").append(qty.get(0)).append("]]>").append("</quantity>\r\n");
					valueXmlString.append("<quantity_real>").append("<![CDATA[").append(qtyStd).append("]]>").append("</quantity_real>\r\n");
					valueXmlString.append("<quantity__stduom>").append("<![CDATA[").append(qtyStd).append("]]>").append("</quantity__stduom>\r\n");
					valueXmlString.append("<conv__qty_stduom>").append("<![CDATA[").append(1 / Double.parseDouble(qty.get(1).toString())).append("]]>").append("</conv__qty_stduom>\r\n");
					valueXmlString.append("<pack_qty>").append("<![CDATA[").append(quantity / noArt).append("]]>").append("</pack_qty>\r\n");
					valueXmlString.append("<loc_code>").append("<![CDATA[").append(locCode.trim()).append("]]>").append("</loc_code>\r\n");
					valueXmlString.append("<lot_no>").append("<![CDATA[").append(lotNo.trim()).append("]]>").append("</lot_no>\r\n");
					if (lotSL.equals(""))
					{
						lotSL = " ";
					}
					valueXmlString.append("<lot_sl>").append("<![CDATA[").append(lotSL.trim()).append("]]>").append("</lot_sl>\r\n");
					valueXmlString.append("<status>").append("<![CDATA[").append(" ").append("]]>").append("</status>\r\n");
					valueXmlString.append("<pack_instr>").append("<![CDATA[").append(packInstr).append("]]>").append("</pack_instr>\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("<nett_weight>").append("<![CDATA[").append(netWeight).append("]]>").append("</nett_weight>\r\n");
					valueXmlString.append("<no_art>").append("<![CDATA[").append(noArt).append("]]>").append("</no_art>\r\n");
					valueXmlString.append("<dimension>").append("<![CDATA[").append(dimension).append("]]>").append("</dimension>\r\n");
					valueXmlString.append("<tax_class>").append("<![CDATA[").append((taxClass == null) ? "":taxClass).append("]]>").append("</tax_class>\r\n");
					valueXmlString.append("<tax_chap>").append("<![CDATA[").append((taxChap == null) ? "":taxChap).append("]]>").append("</tax_chap>\r\n");
					valueXmlString.append("<tax_env>").append("<![CDATA[").append((taxEnv == null) ? "":taxEnv).append("]]>").append("</tax_env>\r\n");
					valueXmlString.append("</Detail>\r\n");

					/////////Deallocate	not required as per Manoharan Sir 10/02/07
					/*String siteCode = "",itemCodeOrd = "",itemCode = "";
					double qtyAlloc = 0;
					HashMap strAllocate = new HashMap();
					String sql = "select site_code from sorddet where sale_order ='"+invOrderNo+"' and line_no = '"+lineNoOrd+"'";
					rs1 = stmt.executeQuery(sql);
					if(rs1.next){
						siteCode = rs1.getString("site_code");
					}
					sql = "select item_code__ord,item_code from sorditem where sale_order = '"+invOrderNo+"' and line_no = '"+lineNoOrd+"' and site_code = '"+siteCode+"' and exp_lev = '"+expLev+"'";
					rs1 = stmt.executeQuery(sql);
					if(rs1.next()){
						itemCodeOrd = rs1.getString("item_code__ord");
						itemCode = rs1.getString("item_code");
					}
					sql = "select qty_alloc from sordalloc where sale_order = '"+invOrderNo+"' and line_no = '"+lineNoOrd+"' "+
							"and exp_lev = '"+expLev+"' and item_code__ord = '"+itemCodeOrd+"' and item_code = '"+itemCode+"' loc_code = '"+locCode+"' "+
							"and lot_no = '"+lotNo+"' and lot_sl = '"+lotSL+"'";
					
					rs1 = stmt.executeQuery(sql);
					if(rs1.next()){
						qtyAlloc = rs1.getdouble("qty_alloc");
					}
					int upd = 0;	
					if(qtyAlloc >= detQty){
						if(qtyAlloc - detQty <= 0){
							sql = "delete from sordalloc where sale_order = '"+invOrderNo+"' and line_no = '"+lineNoOrd+"' "+
									"and exp_lev = '"+expLev+"' item_code__ord = '"+itemCodeOrd+"' item_code = '"+itemCode+"' and loc_code = '"+locCode+"' "+
									"and lot_no = '"+lotNo+"' and lot_sl = '"+lotSL+"'";
							upd = stmt.executeUpdate(sql);
							System.out.println("Records Deleted :: "+upd);
						}
						else{
							sql = "update sordalloc set qty_alloc = qtyAlloc - "+detQty+" where "+
									"sale_order = '"+invOrderNo+"' and line_no = '"+lineNoOrd+"' and exp_lev = '"+expLev+"' "+
									"and item_code__ord = '"+itemCodeOrd+"' and item_code = '"+itemCode+"' and loc_code = '"+locCode+"' "
									"and lot_no = '"+lotNo+"' lot_sl = '"+lotSL+"'";
							upd = stmt.executeUpdate(sql);
							System.out.println("Records Updated :: "+upd);
						}
						sql = "update sorditem set qty_alloc = qty_alloc - "+detQty+" where sale_order = '"+invOrderNo+"' and "+
								"line_no = '"+lineNoOrd+"' and exp_lev = '"+expLev+"'";
						strAllocate.put("ref_ser",);
						strAllocate.put("ref_id",);
						strAllocate.put("ref_line",);
						strAllocate.put("item_code",);
						strAllocate.put("site_code",);
						strAllocate.put("loc_code",);
						strAllocate.put("lot_no",);
						strAllocate.put("lot_sl",);
						strAllocate.put("alloc_qty",);
						strAllocate.put("chg_win",);
						strAllocate.put("chg_user",);
						strAllocate.put("chg_term",);  
					//}
					////////////
				}//while end
				valueXmlString.append("</Root>\r\n");			
			//}//end if(detailCount)
		}//try end
		catch(Exception e)
		{
			System.out.println("Exception :Despatch :" + e.getMessage() + ":");
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				System.out.println("Closing Connection.....");
				conn.close();
			}catch(Exception e){}
		}
		System.out.println("valueXmlString.toString() "+valueXmlString.toString());
		return valueXmlString.toString();		
	}
	*///End Comment Gulzar - 23-04-07 - 
	//Added Changes - Gulzar - 23-04-07
	private String actionGetPackList(Document dom, Document dom1, String objContext, String xtraParams) throws RemoteException, ITMException
	{
		String saleOrder = "",despId = "",sqlOrderNoSo = "",sqlOrderNoPo = "",sqlOrderNoDo = "";
		String orderNo = "",orderNoSo = "",orderNoPo = "",orderNoDo = "",invOrderNo = "";		
		String sql1 = "", sql = "";
		String orderType = "",spaces = "",expLev = "",saleOrd = "";
		String taxClass = "", taxChap = "", taxEnv = "", unit = "",unitStd = "";  
		String lineNoOrd ="", itemCode = "", locCode = "", lotNo = "", lotSL = "", packInstr  = "", dimension = "";   
		double quantity = 0, grossWeight = 0, tareWeight = 0, netWeight = 0, conv = 0, qtyStd = 0; 
		double detQty = 0;
		long   noArt = 0;
		ArrayList qty = new ArrayList();
		ArrayList orderNoArrList = new ArrayList();
		ResultSet rs = null;
		String detailCnt = "0";
		ResultSet rs1 = null;
		Connection conn = null;
		Statement stmt = null;
		Statement stmt1 = null;
		StringBuffer valueXmlString = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root>\r\n");
		GenericUtility genericUtility = GenericUtility.getInstance();
		ConnDriver connDriver = new ConnDriver();
		ITMDBAccessEJB itmDBAccess = new ITMDBAccessEJB();
		String origInvOrderNo = "",sorder = "N";
		try
		{
			//detailCnt = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"detCnt");
			//if(detailCnt.equals("0"))
			//{
				conn = connDriver.getConnectDB("DriverITM");
				stmt = conn.createStatement();

				saleOrder = genericUtility.getColumnValue("sord_no",dom);
				despId = genericUtility.getColumnValue("desp_id",dom1);
				System.out.println("saleOrder :"+saleOrder);
				System.out.println("despId :"+despId);

				sqlOrderNoSo = "SELECT ORDER_NO FROM INV_PACK WHERE ORDER_NO ='"+saleOrder+"'"+" AND "
							  +"ORDER_TYPE IN ('S','I') AND CONFIRMED ='Y'";
				System.out.println("sqlOrderNoSo :"+sqlOrderNoSo);
				rs = stmt.executeQuery(sqlOrderNoSo);
				while (rs.next())
				{
					orderNoSo = rs.getString(1);
					if(orderNoSo != null){
						orderNoArrList.add(orderNoSo);
					}
					System.out.println("orderNoSo :"+orderNoSo);
				}
				stmt.close();
				stmt = null;
				sqlOrderNoPo = "SELECT ORDER_NO FROM INV_PACK WHERE ORDER_NO IN (SELECT PURC_ORDER FROM PORDER WHERE " 
							  +"SALE_ORDER ='"+saleOrder+"'"+") AND ORDER_TYPE = 'P' AND CONFIRMED = 'Y'";
				System.out.println("sqlOrderNoPo :"+sqlOrderNoPo);
				stmt = conn.createStatement();
				rs = stmt.executeQuery(sqlOrderNoPo);
				while (rs.next())
				{
					orderNoPo = rs.getString(1);
					if(orderNoPo != null){
						orderNoArrList.add(orderNoPo);
					}
					System.out.println("orderNoPo :"+orderNoPo);
				}
				stmt.close();
				stmt = null;
				sqlOrderNoDo = "SELECT ORDER_NO FROM INV_PACK WHERE ORDER_NO IN (SELECT DIST_ORDER FROM DISTORDER "
							  +"WHERE SALE_ORDER ='"+saleOrder+"' ) AND ORDER_TYPE ='D' AND CONFIRMED = 'Y'";	
				System.out.println("sqlOrderNoDo :"+sqlOrderNoDo);
				stmt = conn.createStatement();
				rs = stmt.executeQuery(sqlOrderNoDo);
				if (rs.next())
				{
					orderNoDo = rs.getString(1);
					if(orderNoDo != null){
						orderNoArrList.add(orderNoDo);
					}
					System.out.println("orderNoDo :"+orderNoDo);
				}
				stmt.close();
				stmt = null;
				int orderNoListSize = orderNoArrList.size();
				String orderNoTemp = "";
				for(int ctr = 0;ctr < orderNoListSize;ctr++){
					orderNoTemp = orderNoArrList.get(ctr).toString();
					orderNo	= "'".concat(orderNoTemp).concat("',");
				}
				
				System.out.println("orderNo :: "+orderNo);
				
				if(orderNo != null && orderNo.indexOf(",") != -1){
					orderNo = orderNo.substring(0,orderNo.length()-1);
				}else{
					orderNo = "''";
				}
			
				System.out.println("orderNo :: "+orderNo);				
				//orderNo = "'".concat(orderNoSo).concat("'").concat(",").concat("'").concat(orderNoPo).concat("'").concat(",").concat("'").concat(orderNoDo).concat("'");
				
				sql = "SELECT INV_PACK_RCP.TRAN_ID, "   
						 +"INV_PACK_RCP.LINE_NO, "   
						 +"INV_PACK_RCP.ORDER_NO, "   
						 +"INV_PACK_RCP.LINE_NO__ORD, "   
						 +"INV_PACK_RCP.ITEM_CODE, "   
						 +"INV_PACK_RCP.LOC_CODE, "   
						 +"INV_PACK_RCP.LOT_NO, "   
						 +"INV_PACK_RCP.LOT_SL, "   
						 +"INV_PACK_RCP.QUANTITY, "   
						 +"INV_PACK_RCP.UNIT, "   
						 +"INV_PACK_RCP.PACK_CODE, "   
						 +"INV_PACK_RCP.PACK_INSTR, "   
						 +"INV_PACK_RCP.GROSS_WEIGHT, "   
						 +"INV_PACK_RCP.TARE_WEIGHT, "   
						 +"INV_PACK_RCP.NET_WEIGHT, "   
						 +"INV_PACK_RCP.NO_ART, "   
						 +"INV_PACK_RCP.DIMENSION, "   
						 +"'Y' AS PROCESS_YN "  
					+"FROM INV_PACK, INV_PACK_RCP, STOCK "  
					+"WHERE INV_PACK.TRAN_ID = INV_PACK_RCP.TRAN_ID "
					+"AND INV_PACK_RCP.ITEM_CODE = STOCK.ITEM_CODE "
					+"AND INV_PACK.SITE_CODE = STOCK.SITE_CODE "
					+"AND INV_PACK_RCP.LOC_CODE = STOCK.LOC_CODE "
					+"AND INV_PACK_RCP.LOT_NO = STOCK.LOT_NO "
					+"AND INV_PACK_RCP.LOT_SL = STOCK.LOT_SL "
					+"AND INV_PACK.ORDER_NO IN(" +orderNo+") "
					+"AND INV_PACK.CONFIRMED  = 'Y' "
					+"AND STOCK.QUANTITY >= INV_PACK_RCP.QUANTITY "   
					+"ORDER BY INV_PACK_RCP.LINE_NO ASC";

				System.out.println("sql :"+sql);
				
				stmt = conn.createStatement();
				rs = stmt.executeQuery(sql);
				while (rs.next())// here in PB it opens a window and prompts the user to select records and then the procssing continues for the selected records
				{
					valueXmlString.append("<Detail>\r\n");
					valueXmlString.append("<tran_id>").append("<![CDATA[").append(rs.getString(1)).append("]]>").append("</tran_id>\r\n");
					valueXmlString.append("<line_no>").append("<![CDATA[").append(rs.getInt(2)).append("]]>").append("</line_no>\r\n");
					valueXmlString.append("<order_no>").append("<![CDATA[").append(rs.getString(3)).append("]]>").append("</order_no>\r\n");
					valueXmlString.append("<line_no__ord>").append("<![CDATA[").append(rs.getInt(4)).append("]]>").append("</line_no__ord>\r\n");
					valueXmlString.append("<item_code>").append("<![CDATA[").append(rs.getString(5)).append("]]>").append("</item_code>\r\n");
					valueXmlString.append("<loc_code>").append("<![CDATA[").append(rs.getString(6)).append("]]>").append("</loc_code>\r\n");
					valueXmlString.append("<lot_no>").append("<![CDATA[").append(rs.getString(7)).append("]]>").append("</lot_no>\r\n");
					valueXmlString.append("<lot_sl>").append("<![CDATA[").append(rs.getString(8)).append("]]>").append("</lot_sl>\r\n");
					valueXmlString.append("<quantity>").append("<![CDATA[").append(rs.getDouble(9)).append("]]>").append("</quantity>\r\n");
					valueXmlString.append("<unit>").append("<![CDATA[").append(rs.getString(10)).append("]]>").append("</unit>\r\n");
					valueXmlString.append("<pack_code>").append("<![CDATA[").append(rs.getString(11)).append("]]>").append("</pack_code>\r\n");
					valueXmlString.append("<pack_instr>").append("<![CDATA[").append(rs.getString(12)).append("]]>").append("</pack_instr>\r\n");
					valueXmlString.append("<gross_weight>").append("<![CDATA[").append(rs.getDouble(13)).append("]]>").append("</gross_weight>\r\n");
					valueXmlString.append("<tare_weight>").append("<![CDATA[").append(rs.getDouble(14)).append("]]>").append("</tare_weight>\r\n");
					valueXmlString.append("<net_weight>").append("<![CDATA[").append(rs.getDouble(15)).append("]]>").append("</net_weight>\r\n");
					valueXmlString.append("<no_art>").append("<![CDATA[").append(rs.getInt(16)).append("]]>").append("</no_art>\r\n");
					valueXmlString.append("<dimension>").append("<![CDATA[").append(rs.getString(17)).append("]]>").append("</dimension>\r\n");
					valueXmlString.append("<process_yn>").append("<![CDATA[").append(rs.getString(18)).append("]]>").append("</process_yn>\r\n");
					valueXmlString.append("</Detail>\r\n");
				}//while end
				valueXmlString.append("</Root>\r\n");			
			//}//end if(detailCount)
		}//try end
		catch(Exception e)
		{
			System.out.println("Exception :[Despatch][actionGetPackList]: " + e.getMessage() + ":");
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				System.out.println("Closing Connection.....");
				conn.close();
			}catch(Exception e){}
		}
		System.out.println("valueXmlString.toString() "+valueXmlString.toString());
		return valueXmlString.toString();		
	}
	private String packListTransform(Document dom,Document dom1, String objContext, String xtraParams, Document selDataDom)throws ITMException
	{
		String saleOrder = "",despId = "",sqlOrderNoSo = "",sqlOrderNoPo = "",sqlOrderNoDo = "";
		String orderNo = "",orderNoSo = "",orderNoPo = "",orderNoDo = "",invOrderNo = "";		
		String sqlStr = "", origInvOrderNo = "", sorder = "N";
		String orderType = "",spaces = "",expLev = "",saleOrd = "";
		String taxClass = "", taxChap = "", taxEnv = "", unit = "",unitStd = "";  
		String lineNoOrd ="", itemCode = "", locCode = "", lotNo = "", lotSL = "", packInstr  = "", dimension = "";   
		String grossWt = "", tareWt = "", netWt = "", noArt1 = "";
		double quantity = 0, grossWeight = 0, tareWeight = 0, netWeight = 0, conv = 0, qtyStd = 0; 
		double detQty = 0, noArt = 0;
		NodeList detailList = null;
		Node currDetail = null;
		int detailListLength = 0;
		ArrayList qty = new ArrayList();

		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		ConnDriver connDriver = new ConnDriver();
		StringBuffer valueXmlString = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root>\r\n");
		GenericUtility genericUtility = GenericUtility.getInstance();
		ITMDBAccessEJB itmDBAccess = new ITMDBAccessEJB();
		try
		{
			conn = connDriver.getConnectDB("DriverITM");
			stmt = conn.createStatement();
			
			despId = genericUtility.getColumnValue("desp_id",dom1);

			detailList = selDataDom.getElementsByTagName("Detail");
			detailListLength = detailList.getLength(); 
			System.out.println("DetailLength ::"+detailListLength);
			for (int ctr = 0;ctr < detailListLength; ctr++)
			{
				currDetail = detailList.item(ctr);
				invOrderNo = genericUtility.getColumnValueFromNode("order_no",currDetail);
				origInvOrderNo = invOrderNo;
				System.out.println("invOrderNo :"+invOrderNo);
				sqlStr = "SELECT ORDER_TYPE FROM INV_PACK WHERE ORDER_NO ='"+invOrderNo+"'";
				System.out.println("sqlStr :"+sqlStr);
				rs = stmt.executeQuery(sqlStr);
				if(rs.next())
				{
					orderType = rs.getString("ORDER_TYPE");
				}
				System.out.println("orderType :"+orderType);
				if (orderType != null && orderType.equalsIgnoreCase("D"))
				{
					sqlStr = "SELECT SALE_ORDER FROM DISTORDER WHERE DIST_ORDER = '"+invOrderNo+"'";
					System.out.println("sqlStr :"+sqlStr);
					rs = stmt.executeQuery(sqlStr);
					if(rs.next())
					{
						saleOrd = rs.getString("SALE_ORDER");
						System.out.println("saleOrd :"+saleOrd);
					}
					invOrderNo = saleOrd;
				}
				else if (orderType != null && orderType.equalsIgnoreCase("P"))
				{
					sqlStr = "SELECT SALE_ORDER FROM PORDER WHERE PURC_ORDER = '"+invOrderNo+"'";
					System.out.println("sqlStr :"+sqlStr);
					rs = stmt.executeQuery(sqlStr);
					if(rs.next())
					{
						saleOrd = rs.getString("SALE_ORDER");
						System.out.println("saleOrd :"+saleOrd);
					}
					invOrderNo = saleOrd;
				}
				else if(orderType != null && orderType.equalsIgnoreCase("I"))
				{
					sqlStr = "SELECT SALE_ORDER FROM DISTORDER WHERE DIST_ORDER = '"+invOrderNo+"'";
					System.out.println("sqlStr :"+sqlStr);
					rs = stmt.executeQuery(sqlStr);
					if(rs.next())
					{
						saleOrd = rs.getString("SALE_ORDER");
						System.out.println("saleOrd :"+saleOrd);
					}
					if(saleOrd != null && saleOrd.trim().length() > 0){
						invOrderNo = saleOrd;
						sorder = "Y";
					}						
					System.out.println("invOrderNo :"+invOrderNo);
				}
				lineNoOrd = genericUtility.getColumnValueFromNode("line_no__ord",currDetail);
				if(sorder == "Y")
				{
					sqlStr = "SELECT SALE_ORDER, LINE_NO__SORD FROM DISTORDER_DET WHERE DIST_ORDER = '"+origInvOrderNo+"' AND LINE_NO = '"+lineNoOrd+"'";
					rs = stmt.executeQuery(sqlStr);
					if(rs.next()){
						invOrderNo	= rs.getString("SALE_ORDER");
						lineNoOrd	= rs.getString("LINE_NO__SORD");
					}
				}
				System.out.println("lineNoOrd ::"+lineNoOrd);
				if (lineNoOrd.trim().length() == 1)
				{
					spaces = "  ";
				}
				if (lineNoOrd.trim().length() == 2)
				{
					spaces = " ";
				}
				System.out.println("spaces :"+spaces);
				lineNoOrd = spaces.concat(lineNoOrd.trim());
				System.out.println("lineNoOrd :"+lineNoOrd);
				sqlStr = "SELECT EXP_LEV FROM SORDITEM WHERE SALE_ORDER ='"+invOrderNo+"'"
					  +"AND LINE_NO ='"+lineNoOrd+"'"+" AND LINE_TYPE = 'I'";
				System.out.println("sqlStr :"+sqlStr);
				rs = stmt.executeQuery(sqlStr);
				if(rs.next())
				{
					expLev = rs.getString("EXP_LEV");
				}
				System.out.println("expLev :"+expLev);
				sqlStr = "SELECT TAX_CLASS,TAX_CHAP,TAX_ENV,UNIT,UNIT__STD FROM SORDDET "
					  +"WHERE SALE_ORDER = '"+invOrderNo+"'"+" AND LINE_NO = '"+lineNoOrd+"'"; 
				System.out.println("sqlStr :"+sqlStr);
				rs = stmt.executeQuery(sqlStr);
				if(rs.next())
				{
					taxClass = rs.getString("TAX_CLASS");
					System.out.println("taxClass :"+taxClass);
					taxChap = rs.getString("TAX_CHAP");
					System.out.println("taxChap :"+taxChap);
					taxEnv = rs.getString("TAX_ENV");
					System.out.println("taxEnv :"+taxEnv);
					unit = rs.getString("UNIT");
					System.out.println("unit :"+unit);
					unitStd = rs.getString("UNIT__STD");
					System.out.println("unitStd :"+unitStd);
				}
				valueXmlString.append("<Detail>\r\n");
				valueXmlString.append("<desp_id>").append("<![CDATA[").append((despId == null) ? "":despId).append("]]>").append("</desp_id>\r\n");
				valueXmlString.append("<sord_no>").append("<![CDATA[").append(invOrderNo).append("]]>").append("</sord_no>\r\n");
				valueXmlString.append("<line_no__sord isSrvCallOnChg='1'>").append("<![CDATA[").append(lineNoOrd).append("]]>").append("</line_no__sord>\r\n");
				valueXmlString.append("<exp_lev isSrvCallOnChg='1'>").append("<![CDATA[").append(expLev).append("]]>").append("</exp_lev>\r\n");
				valueXmlString.append("<unit>").append("<![CDATA[").append(unit).append("]]>").append("</unit>\r\n");
				valueXmlString.append("<unit__std>").append("<![CDATA[").append(unitStd).append("]]>").append("</unit__std>\r\n");
				valueXmlString.append("<unit__std>").append("<![CDATA[").append(unitStd).append("]]>").append("</unit__std>\r\n");

				itemCode = genericUtility.getColumnValueFromNode("item_code",currDetail);
				quantity = Double.parseDouble(genericUtility.getColumnValueFromNode("quantity",currDetail));
				qtyStd = quantity;
				conv = 0;
				if (!unit.trim().equals(unitStd.trim()))  //REMOVED NOT SYMBOL
				{
					qty = itmDBAccess.getConvQuantityFact(unitStd, unit, itemCode, qtyStd, conv, conn);
					System.out.println("qty :"+qty);
				}
				else
				{
					qty.add(0, new Double(qtyStd));
					qty.add(1,new Double(1));
				}
				detQty = ((Double)qty.get(0)).doubleValue();
				conv = ((Double)qty.get(1)).doubleValue();
				System.out.println("conv :"+conv);

				locCode		= genericUtility.getColumnValueFromNode("loc_code",currDetail);
				lotNo		= genericUtility.getColumnValueFromNode("lot_no",currDetail);
				lotSL		= genericUtility.getColumnValueFromNode("lot_sl",currDetail);
				packInstr	= genericUtility.getColumnValueFromNode("pack_instr",currDetail);
				grossWt		= genericUtility.getColumnValueFromNode("gross_weight",currDetail);
				tareWt		= genericUtility.getColumnValueFromNode("tare_weight",currDetail);
				netWt		= genericUtility.getColumnValueFromNode("nett_weight",currDetail);
				noArt1		= genericUtility.getColumnValueFromNode("no_art",currDetail);
				if (grossWt != null)
				{
					grossWeight	= Double.parseDouble(grossWt);
				}
				else 
				{
					grossWeight = 0;
				}
				if (tareWt != null)
				{
					tareWeight	= Double.parseDouble(tareWt);
				}
				else 
				{
					tareWeight = 0;
				}
				if (netWt != null)
				{
					netWeight	= Double.parseDouble(netWt);
				}
				else 
				{
					netWeight = 0;
				}
				if (noArt1 != null)
				{
					noArt	= Double.parseDouble(noArt1);
				}
				else 
				{
					noArt = 0;
				}
				
				dimension	= genericUtility.getColumnValueFromNode("dimension",currDetail);

				valueXmlString.append("<quantity>").append("<![CDATA[").append(qty.get(0)).append("]]>").append("</quantity>\r\n");
				valueXmlString.append("<quantity_real>").append("<![CDATA[").append(qtyStd).append("]]>").append("</quantity_real>\r\n");
				valueXmlString.append("<quantity__stduom>").append("<![CDATA[").append(qtyStd).append("]]>").append("</quantity__stduom>\r\n");
				valueXmlString.append("<conv__qty_stduom>").append("<![CDATA[").append(1 / Double.parseDouble(qty.get(1).toString())).append("]]>").append("</conv__qty_stduom>\r\n");
				valueXmlString.append("<pack_qty>").append("<![CDATA[").append(quantity / noArt).append("]]>").append("</pack_qty>\r\n");
				valueXmlString.append("<loc_code>").append("<![CDATA[").append(locCode).append("]]>").append("</loc_code>\r\n");
				valueXmlString.append("<lot_no>").append("<![CDATA[").append(lotNo).append("]]>").append("</lot_no>\r\n");
				if (lotSL.equals(""))
				{
					lotSL = " ";
				}
				valueXmlString.append("<lot_sl>").append("<![CDATA[").append(lotSL).append("]]>").append("</lot_sl>\r\n");
				valueXmlString.append("<status>").append("<![CDATA[").append(" ").append("]]>").append("</status>\r\n");
				valueXmlString.append("<pack_instr>").append("<![CDATA[").append((packInstr == null) ? "":packInstr).append("]]>").append("</pack_instr>\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("<nett_weight>").append("<![CDATA[").append(netWeight).append("]]>").append("</nett_weight>\r\n");
				valueXmlString.append("<no_art>").append("<![CDATA[").append(noArt).append("]]>").append("</no_art>\r\n");
				valueXmlString.append("<dimension>").append("<![CDATA[").append((dimension == null) ? "":dimension).append("]]>").append("</dimension>\r\n");
				valueXmlString.append("<tax_class>").append("<![CDATA[").append((taxClass == null) ? "":taxClass).append("]]>").append("</tax_class>\r\n");
				valueXmlString.append("<tax_chap>").append("<![CDATA[").append((taxChap == null) ? "":taxChap).append("]]>").append("</tax_chap>\r\n");
				valueXmlString.append("<tax_env>").append("<![CDATA[").append((taxEnv == null) ? "":taxEnv).append("]]>").append("</tax_env>\r\n");
				valueXmlString.append("</Detail>\r\n");
			}
			valueXmlString.append("</Root>\r\n");
		}
		catch (Exception e)
		{
			System.out.println("Exception :[Despatch][packListTransform] :" + e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		return valueXmlString.toString();
	}
	//End Changes Gulzar 23-04-07
	/*--Commented and changes below - Gulzar - 23/01/08
	private String actionDefault(Document dom, Document dom1, String objContext, String xtraParams) throws RemoteException, ITMException
	{
		PreparedStatement pstmt = null;
		String unit1 = "", unitStd1 = "";
		String sordNo = "", locCode = "", lotSl = "", lotNo = "", siteCode = "", errCode = "", errString = "";
		String lineNo = "", expLev = "", itemCode = "", itemCodeOrd = "", itemCodeOld = ""; 
		String sql = "", sql1 = "", sql2 = "", sql3 = "";
		java.util.Date expDate1 = null, chkDate4 = null, chkDate3 = null;
		Statement stmt = null, stmt1 = null, stmt2 = null, stmt3 = null;
		Connection conn = null;
		ResultSet rs = null, rs1 = null, rs2 = null, rs3 = null;
		StringBuffer valueXmlString = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root>\r\n");
		double qtyStk = 0d, allocQty = 0d, inputQty = 0d, quantityStduom = 0d, convQtyStduom = 0d;
		double grossPer = 0d,netPer = 0d, tarePer = 0d, qtyStd = 0d;
		double grossWeight = 0d, tareWeight = 0d, netWeight = 0d, conv = 0;
		java.sql.Date expDate = null, mfgDate = null;
		String dimension = "", suppCodeMfg = "", despDt = "", trackShelfLife = "", orderType = "", siteCodeMfg = "";
		int minShelfLife = 0, maxShelfLife = 0, noOfItems = 0, cntItemCode = 0, updateCnt = 0, counter = 0; 
 		ArrayList qtyFact = new ArrayList();
		String locCode1 = "", lotSl1 = "", locGroup = "", lineNoSord = "";
		String chkDate1 = "", chkDate2 = "",detCnt = "0",lineNoOrd = ""; 
		ConnDriver connDriver = new ConnDriver();
		GenericUtility genericUtility = GenericUtility.getInstance();
		ITMDBAccessEJB itmDBAccess = new ITMDBAccessEJB();
		HashMap qtyMap = new HashMap();
		Double qtyDbl = new Double(0.0);
		double qtyOrd = 0.0, qtyPerArt = 0.00;
		int noArt = 0;
		String updateStatus = "";
		try
		{
			detCnt = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"detCnt"); //Will be Uncommented later - Jiten
			if (detCnt == null || detCnt.trim().length() == 0)
			{
				detCnt = "0";
			}
			if (dom != null)
			{
				updateStatus = getCurrentUpdateFlag(dom);
				System.out.println("updateStatus :: "+updateStatus);
			}
			//Added Changes By Gulzar 28-02-07
			if ((Integer.parseInt(detCnt) > 1) || (!updateStatus.equals("A")))
			{
				errCode = "VTDESPD1";
				errString = itmDBAccess.getErrorString("",errCode,"");
				return errString;
			}
			//if (detCnt.equals("0")) //Gulzar 27-02-07
			else //End Changes Gulzar 28-02-08
			{
				conn = connDriver.getConnectDB("DriverITM");
				stmt = conn.createStatement();
				stmt1 = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
				stmt2 = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
				stmt3 = conn.createStatement();

				sordNo = genericUtility.getColumnValue("sord_no",dom1);	 
				System.out.println("sordNo :"+sordNo);
				SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
				if (sordNo == null && sordNo.trim().length() == 0)
				{
					errCode = "VTPCK1";
					errString = itmDBAccess.getErrorString("",errCode,"","",conn);
					return errString;
				}
				if (dom != null)
				{
					locCode1 = genericUtility.getColumnValue("loc_code",dom);	 
					lotSl1	 = genericUtility.getColumnValue("lot_sl",dom);	 
				}
				siteCode = genericUtility.getColumnValue("site_code",dom1);
				System.out.println("locCode :"+locCode1);
				System.out.println("lotSl :"+lotSl1);
				System.out.println("siteCode :"+siteCode);

				sql = "SELECT NO_OF_ITEMS FROM DOC_NO_ITEMS " 
					 +"WHERE SITE_CODE = '"+siteCode+"' "
					 +"AND MOD_NAME = UPPER('W_DESPATCH')";
				System.out.println("sql :"+sql);					
				rs = stmt.executeQuery(sql);
				if (rs.next())
				{
					noOfItems = rs.getInt(1);
					System.out.println("noOfItems :"+noOfItems);
				}
				else
				{
					noOfItems = 0;
					System.out.println("noOfItems :"+noOfItems);
				}
				/*-- Commented And Changes Below as There is a Changes in PB logic
				sql ="SELECT SORDDET.SALE_ORDER, "   
						 +"SORDDET.LINE_NO, "   
						 +"SORDDET.SITE_CODE, "   
						 +"SORDDET.ITEM_CODE, "   
						 +"SORDDET.ITEM_FLG, "   
						 +"SORDDET.QUANTITY, "   
						 +"SORDDET.UNIT, "   
						 +"SORDDET.DSP_DATE, "   
						 +"SORDDET.RATE, "   
						 +"SORDDET.DISCOUNT, "   
						 +"SORDDET.TAX_AMT, "   
						 +"SORDDET.TAX_CLASS, "   
						 +"SORDDET.TAX_CHAP, "   
						 +"SORDDET.TAX_ENV, "   
						 +"SORDDET.NET_AMT, "   
						 +"SORDDET.REMARKS, "   
						 +"SORDDET.STATUS, "   
						 +"SORDDET.STATUS_DATE, "   
						 +"SORDDET.CHG_DATE, "   
						 +"SORDDET.CHG_USER, "   
						 +"SORDDET.CHG_TERM, "   
						 +"SORDDET.ITEM_DESCR, "   
						 +"SORDDET.UNIT__RATE, "   
						 +"SORDDET.CONV__QTY_STDUOM, "   
						 +"SORDDET.CONV__RTUOM_STDUOM, "   
						 +"SORDDET.UNIT__STD, "   
						 +"SORDDET.QUANTITY__STDUOM, "   
						 +"SORDDET.RATE__STDUOM, "   
						 +"SORDDET.NO_ART, "   
						 +"SORDDET.PACK_CODE, "   
						 +"SORDDET.LINE_NO__CONTR, "   
						 +"SORDDET.PACK_INSTR, "   
						 +"SORDDET.SPEC_REF, "   
						 +"SORDDET.PACK_QTY, "   
						 +"SORDDET.ITEM_SER, "   
						 +"SORDDET.RATE__CLG, "   
						 +"SORDDET.MFG_CODE, "   
						 +"SORDDET.CONTRACT_NO, "   
						 +"PACKING.DESCR, "   
						 +"SORDDET.SPEC_ID, "   
						 +"SORDDET.ORD_VALUE, "   
						 +"SORDDET.ITEM_SER__PROM, "   
						 +"SORDDET.SPECIFIC_INSTR, "   
						 +"SORDDET.ITEM_CODE__ORD, "   
						 +"SORDDET.PALLET_CODE, "   
						 +"SORDDET.NO_PALLET, "   
						 +"SORDDET.OVER_SHIP_PERC, "   
						 +"SORDDET.COMM_PERC_1, "   
						 +"SORDDET.COMM_PERC_2, "   
						 +"SORDDET.COMM_PERC_3, "   
						 +"SORDDET.COMM_PERC_ON_1, "   
						 +"SORDDET.COMM_PERC_ON_2, "   
						 +"SORDDET.COMM_PERC_ON_3, "   
						 +"SORDDET.SALES_PERS_COMM_1, "   
						 +"SORDDET.SALES_PERS_COMM_2, "   
						 +"SORDDET.SALES_PERS_COMM_3, "   
						 +"SORDDET.PRICE_LIST__DISC, "   
						 +"SORDDET.RATE__STD, "   
						 +"SORDER.FIN_SCHEME, "   
						 +"SORDDET.MAX_SHELF_LIFE, "   
						 +"SORDDET.MIN_SHELF_LIFE, "   
						 +"SPACE(250) AS ITEM_SPECS, "   
						 +"SPACE(250) AS ST_SHRINK, "   
						 +"SORDDET.LOC_TYPE, "   
						 +"SPACE(250) AS ST_SCHEME,  "
						 +"SORDDET.SORDFORM_NO ,  "  
						 +"SORDDET.LINE_NO__SFORM ,  " 
						 +"FN_GET_ITMSTK(SORDDET.ITEM_CODE,SORDDET.SITE_CODE) STK_QTY,  "  
						 +"SORDDET.APPL_SEG ,  "  
						 +"UOM.DESCR  "  
				 +"FROM SORDDET, PACKING, SORDER  "
				 +"WHERE SORDDET.PACK_CODE = PACKING.PACK_CODE " 
				 +"AND SORDER.SALE_ORDER = SORDDET.SALE_ORDER "
				 +"AND SORDDET.SALE_ORDER = '"+sordNo+"'";
				 // End Changes Gulzar 12/03/07
				 //Added By Gulzar 12/03/07 - Added join with SORDITEM as the default was not working  For Scheme item
				 sql ="SELECT SORDDET.SALE_ORDER, "   
						 +"SORDDET.LINE_NO, "   
						 +"SORDDET.SITE_CODE, "   
					//	 +"SORDDET.ITEM_CODE, " //Gulzar 14/03/07 - As it should take from SORDITEM
						 +"SORDITEM.ITEM_CODE, " //Gulzar 14/03/07
						 +"SORDDET.ITEM_FLG, "   
					//	 +"SORDDET.QUANTITY, "   //Gulzar 14/03/07 - As it should take from SORDITEM
						 +"SORDITEM.QUANTITY, "  //Gulzar 14/03/07
						 +"SORDDET.UNIT, "   
						 +"SORDDET.DSP_DATE, "   
						 +"SORDDET.RATE, "   
						 +"SORDDET.DISCOUNT, "   
						 +"SORDDET.TAX_AMT, "   
						 +"SORDDET.TAX_CLASS, "   
						 +"SORDDET.TAX_CHAP, "   
						 +"SORDDET.TAX_ENV, "   
						 +"SORDDET.NET_AMT, "   
						 +"SORDDET.REMARKS, "   
						 +"SORDDET.STATUS, "   
						 +"SORDDET.STATUS_DATE, "   
						 +"SORDDET.CHG_DATE, "   
						 +"SORDDET.CHG_USER, "   
						 +"SORDDET.CHG_TERM, "   
						 +"SORDDET.ITEM_DESCR, "   
						 +"SORDDET.UNIT__RATE, "   
						 +"SORDDET.CONV__QTY_STDUOM, "   
						 +"SORDDET.CONV__RTUOM_STDUOM, "   
						 +"SORDDET.UNIT__STD, "   
						 +"SORDDET.QUANTITY__STDUOM, "   
						 +"SORDDET.RATE__STDUOM, "   
						 +"SORDDET.NO_ART, "   
						 +"SORDDET.PACK_CODE, "   
						 +"SORDDET.LINE_NO__CONTR, "   
						 +"SORDDET.PACK_INSTR, "   
						 +"SORDDET.SPEC_REF, "   
						 +"SORDDET.PACK_QTY, "   
						 +"SORDDET.ITEM_SER, "   
						 +"SORDDET.RATE__CLG, "   
						 +"SORDDET.MFG_CODE, "   
						 +"SORDDET.CONTRACT_NO, "   
						 +"PACKING.DESCR, "   
						 +"SORDDET.SPEC_ID, "   
						 +"SORDDET.ORD_VALUE, "   
						 +"SORDDET.ITEM_SER__PROM, "   
						 +"SORDDET.SPECIFIC_INSTR, "   
					//	 +"SORDDET.ITEM_CODE__ORD, "   //Gulzar 14/03/07 - As it should take from SORDITEM
						 +"SORDITEM.ITEM_CODE__ORD, "  //Gulzar 14/03/07
						 +"SORDDET.PALLET_CODE, "   
						 +"SORDDET.NO_PALLET, "   
						 +"SORDDET.OVER_SHIP_PERC, "   
						 +"SORDDET.COMM_PERC_1, "   
						 +"SORDDET.COMM_PERC_2, "   
						 +"SORDDET.COMM_PERC_3, "   
						 +"SORDDET.COMM_PERC_ON_1, "   
						 +"SORDDET.COMM_PERC_ON_2, "   
						 +"SORDDET.COMM_PERC_ON_3, "   
						 +"SORDDET.SALES_PERS_COMM_1, "   
						 +"SORDDET.SALES_PERS_COMM_2, "   
						 +"SORDDET.SALES_PERS_COMM_3, "   
						 +"SORDDET.PRICE_LIST__DISC, "   
						 +"SORDDET.RATE__STD, "   
						 +"SORDER.FIN_SCHEME, "   
						 +"SORDDET.MAX_SHELF_LIFE, "   
						 +"SORDDET.MIN_SHELF_LIFE, "   
						 +"SPACE(250) AS ITEM_SPECS, "   
						 +"SPACE(250) AS ST_SHRINK, "   
						 +"SORDDET.LOC_TYPE, "   
						 +"SPACE(250) AS ST_SCHEME,  "
						 +"SORDDET.SORDFORM_NO ,  "  
						 +"SORDDET.LINE_NO__SFORM ,  "  
						 +"FN_GET_ITMSTK(SORDDET.ITEM_CODE,SORDDET.SITE_CODE) STK_QTY,  "  
						 +"SORDDET.APPL_SEG ,  "  
						 +"UOM.DESCR,  "
						 +"SORDITEM.EXP_LEV, " //Gulzar 14/03/07
						 +"SORDITEM.QTY_ALLOC  " //Gulzar 14/03/07
				 +"FROM SORDDET LEFT OUTER JOIN PACKING ON SORDDET.PACK_CODE = PACKING.PACK_CODE LEFT OUTER JOIN UOM ON SORDDET.UNIT = UOM.UNIT,  "
				 +"SORDER, "
				 +"SORDITEM  "
				 +"WHERE SORDER.SALE_ORDER = SORDDET.SALE_ORDER "
				 +"AND SORDITEM.SALE_ORDER = SORDDET.SALE_ORDER "
				 +"AND SORDITEM.LINE_NO = SORDDET.LINE_NO "
				 +"AND SORDITEM.SALE_ORDER = '"+sordNo+"'"
				 +"AND SORDITEM.LINE_TYPE = 'I' ";
				 //End Changes Gulzar 12/03/07
				 System.out.println("sql :"+sql);
				 rs = stmt.executeQuery(sql);
				 while (rs.next())
				 {
					if (noOfItems > 0 && counter >= noOfItems)
					{	
						errCode = "VTITMGRZRO";
						errString = itmDBAccess.getErrorString("",errCode,"","",conn);
						return errString;
					}
					lotSl = lotSl1; // Re-set with Picked-up from DOM.
					locCode = locCode1; // Re-set with Picked-up from DOM.
					//lineNo = rs.getString("LINE_NO");
					lineNoOrd = rs.getString("LINE_NO");
					System.out.println("lineNo :"+lineNoOrd);
					siteCode = genericUtility.getColumnValue("site_code",dom1);	
					//lineNoOrd = genericUtility.getColumnValue("line_no__sord",dom); //Commented as line_no from despatch is to be used
					System.out.println("siteCode :"+siteCode);

					/*-- Commented By Gulzar 14/03/07 - As the same is retrived from main query
					sql1 = "SELECT ITEM_CODE__ORD, QUANTITY,EXP_LEV, ITEM_CODE, QTY_ALLOC "
						  +"FROM SORDITEM WHERE SALE_ORDER = '"+sordNo+"' " // sordNo of Header is Used whereas in PB it taken from detail
						  +"AND LINE_NO = '"+lineNoOrd+"'"+" AND LINE_TYPE = 'I'"; 
					System.out.println("sql1 :"+sql1);
					rs1 = stmt1.executeQuery(sql1);
					if (rs1.next())
					{
						itemCodeOrd = rs1.getString(1);
						System.out.println("itemCodeOrd :"+itemCodeOrd);
						expLev = rs1.getString(3);
						System.out.println("expLev :"+expLev);
						itemCode = rs1.getString(4);
						System.out.println("itemCode :"+itemCode);
					}
					stmt1.close();
					stmt1 = null;
					//End Comment Gulzar 14/03/07

					//Added By Gulzar 14/03/07
					itemCodeOrd = rs.getString("ITEM_CODE__ORD");
					System.out.println("itemCodeOrd :"+itemCodeOrd);
					expLev = rs.getString("EXP_LEV");
					System.out.println("expLev :"+expLev);
					itemCode = rs.getString("ITEM_CODE");
					System.out.println("itemCode :"+itemCode);
					//End changes Gulzar 14/03/07

					sql1 = "SELECT UNIT__STD, CONV__QTY_STDUOM, UNIT, PACK_INSTR, QUANTITY__STDUOM, "
						  +"CASE WHEN NO_ART IS NULL THEN 0 ELSE NO_ART END "
						  +"FROM SORDDET WHERE SALE_ORDER = '"+sordNo+"'"+" AND LINE_NO ='"+lineNoOrd+"'";
					System.out.println("sql1 :"+sql1);
					stmt1 = conn.createStatement();
					rs1 = stmt1.executeQuery(sql1);
					if (rs1.next())
					{
						unit1 = rs1.getString("UNIT");
						unitStd1 = rs1.getString("UNIT__STD");
						convQtyStduom = rs1.getDouble(2);
						System.out.println("convQtyStduom :"+convQtyStduom);
						quantityStduom = rs1.getDouble(5);
						qtyOrd = quantityStduom;
						System.out.println("quantityStduom :"+quantityStduom);
					}
					stmt1.close();
					stmt1 = null;
					if (quantityStduom <= 0)
					{
						continue;
					}
					sql1 = "SELECT LOC_CODE,QUANTITY, EXP_LEV, ITEM_CODE, "
						  +"LOT_NO, LOT_SL, UNIT__STD, CONV__QTY_STDUOM, UNIT "
						  +"FROM SORDALLOC WHERE SALE_ORDER ='"+sordNo+"'"+" AND LINE_NO ='"+lineNoOrd+"'";
					System.out.println("sql1 :"+sql1);
					stmt1 = conn.createStatement();
					rs1 = stmt1.executeQuery(sql1);
					if (rs1.next())
					{
						if (locCode == null || locCode.trim().length() == 0)
						{
							//locCode = rs.getString(1); //Commented - Gulzar - 14/03/07 - As it should take from rs1 not rs 
							locCode = rs1.getString(1);
						}						
						System.out.println("locCode :"+locCode);
						lotNo = rs1.getString(5);
						System.out.println("lotNo :"+lotNo);
						lotSl = rs1.getString(6);
						System.out.println("lotSl :"+lotSl);
					}					
					stmt1.close();
					stmt1 = null;
					sql1 = "SELECT SORDALLOC.LOT_NO, "   
							 +"SORDALLOC.LOT_SL, "   
							 +"SORDALLOC.QTY_ALLOC, "   
							 +"SORDALLOC.DATE_ALLOC, "   
							 +"SORDALLOC.STATUS, "   
							 +"SORDALLOC.ITEM_GRADE, "   
							 +"SORDALLOC.EXP_DATE, "   
							 +"SORDALLOC.ALLOC_MODE, "   
							 +"SORDALLOC.SITE_CODE, "   
							 +"SORDALLOC.LOC_CODE, "   
							 +"SORDALLOC.SALE_ORDER, "   
							 +"SORDALLOC.LINE_NO, "   
							 +"SORDALLOC.EXP_LEV, "   
							 +"SORDALLOC.ITEM_CODE__ORD, "   
							 +"SORDALLOC.ITEM_CODE, "   
							 +"SORDALLOC.ITEM_REF, "   
							 +"SORDALLOC.QUANTITY, "   
							 +"SORDALLOC.UNIT, "   
							 +"LOCATION.DESCR, "   
							 +"SORDALLOC.CONV__QTY_STDUOM, "   
							 +"SORDALLOC.UNIT__STD, "   
							 +"SORDALLOC.QUANTITY__STDUOM, "   
							 +"SORDALLOC.MFG_DATE, "   
							 +"SORDALLOC.SITE_CODE__MFG "   
						+"FROM SORDALLOC, LOCATION "  
						+"WHERE SORDALLOC.LOC_CODE = LOCATION.LOC_CODE "
						+"AND SORDALLOC.SALE_ORDER = '"+sordNo+"' "
						+"AND SORDALLOC.LINE_NO = '"+lineNoOrd+"' "
						+"AND SORDALLOC.EXP_LEV = '"+expLev+"' "
						+"AND SORDALLOC.ITEM_CODE__ORD = '"+itemCodeOrd+"' "
						+"AND SORDALLOC.ITEM_CODE = '"+itemCode+"' "
						+"AND CASE WHEN SORDALLOC.STATUS IS NULL THEN ' ' ELSE SORDALLOC.STATUS END  <> 'D' ";
					System.out.println("sql1 :"+sql1);
					stmt1 = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
					rs1 = stmt1.executeQuery(sql1);
					if(!rs1.next())
					{
						if(locCode == null || locCode.trim().length() == 0)
						{
							locCode = "%";
							System.out.println("locCode :"+locCode);
						}
						else
						{
							locCode = locCode.trim() + "%";
							System.out.println("locCode :"+locCode);
						}
						if (lotSl == null || lotSl.trim().length() == 0)
						{
							lotSl = "%";
							System.out.println("lotSl :"+lotSl);
						}
						else
						{
							lotSl = lotSl.trim() + "%";
							System.out.println("lotSl :"+lotSl);

						}
						sql2 = "SELECT LOC_GROUP FROM SORDER "
								+"WHERE SALE_ORDER ='"+sordNo+"'";
						System.out.println("sql2 :"+sql2);
						rs2 = stmt2.executeQuery(sql2);
						if (rs2.next())
						{
							locGroup = rs2.getString(1);
							System.out.println("locGroup :"+locGroup);
						}
						if (locGroup == null || locGroup.trim().length() == 0)
						{
							//sql2 = "SELECT COUNT(A.ITEM_CODE) FROM STOCK A, INVSTAT B ,LOCATION C "
							sql2 = "SELECT COUNT(*) FROM STOCK A, INVSTAT B ,LOCATION C "
										+"WHERE A.INV_STAT  = B.INV_STAT "
										+"AND C.LOC_CODE = A.LOC_CODE "
										+"AND A.ITEM_CODE = '"+itemCode+"' "
										+"AND A.SITE_CODE = '"+siteCode+"' "
										+"AND A.LOC_CODE  LIKE '"+locCode+"' "
										+"AND A.LOT_SL    LIKE '"+lotSl+"' "
										+"AND B.AVAILABLE = 'Y' "
										+"AND B.USABLE = 'Y' "
										+"AND A.QUANTITY  > 0 ";
							System.out.println("sql2 :"+sql2);
						}
						else
						{
							//sql2 = "SELECT COUNT(A.ITEM_CODE) FROM STOCK A, INVSTAT B ,LOCATION C "
							sql2 = "SELECT COUNT(*) FROM STOCK A, INVSTAT B ,LOCATION C "
										+"WHERE A.INV_STAT  = B.INV_STAT "
										+"AND C.LOC_CODE = A.LOC_CODE "
										+"AND C.LOC_GROUP = '"+locGroup+"' "
										+"AND A.ITEM_CODE = '"+itemCode+"' "
										+"AND A.SITE_CODE = '"+siteCode+"' "
										+"AND A.LOC_CODE  LIKE '"+locCode+"' "
										+"AND A.LOT_SL    LIKE '"+lotSl+"' " 
										+"AND B.AVAILABLE = 'Y' "
										+"AND B.USABLE = 'Y' "
										+"AND A.QUANTITY  > 0 "; 
							System.out.println("sql2 :"+sql2);
						}
						rs2 = stmt2.executeQuery(sql2);
						if (rs2.next())
						{
							cntItemCode = rs2.getInt(1);
							System.out.println("cntItemCode :"+cntItemCode);
						}
						if (cntItemCode == 0)
						{
							continue;
						}
						else
						{
							if(itemCodeOld.indexOf(itemCode) == -1)
							{
								counter = counter + 1;
								System.out.println("Counter :"+counter);
							}
						}
						if (locGroup == null || locGroup.trim().length() == 0)
						{
							sql2 = "SELECT A.LOT_NO,A.LOT_SL, "
										+"A.QUANTITY,A.EXP_DATE, " 
										+"A.SITE_CODE__MFG, "
										+"A.MFG_DATE, "
										+"A.ALLOC_QTY, "
										+"A.PACK_CODE, " 
										+"A.LOC_CODE, "
										+"A.GROSS_WEIGHT, "
										+"A.TARE_WEIGHT, "
										+"A.NET_WEIGHT, " 
										+"A.DIMENSION, "
										+"A.SUPP_CODE__MFG, " 
										+"A.QTY_PER_ART "
								   +"FROM STOCK A,INVSTAT B " 
										+"WHERE A.INV_STAT = B.INV_STAT " 
										+"AND A.ITEM_CODE = '"+itemCode+"' "  
										+"AND A.SITE_CODE = '"+siteCode+"' "  
										+"AND A.LOC_CODE LIKE '"+locCode+"' "  
										+"AND A.LOT_SL LIKE '"+lotSl+"' "   
										+"AND B.AVAILABLE = 'Y' " 
										+"AND B.USABLE = 'Y' " 
										+"AND A.QUANTITY - A.ALLOC_QTY > 0 " 
										+"ORDER BY A.EXP_DATE,A.CREA_DATE, A.LOT_NO, A.LOT_SL ";
							System.out.println("sql2 :"+sql2);
						}
						else
						{
							sql2 = "SELECT A.LOT_NO, "
										+"A.LOT_SL, "
										+"A.QUANTITY, "
										+"A.EXP_DATE, " 
										+"A.SITE_CODE__MFG, "
										+"A.MFG_DATE, "
										+"A.ALLOC_QTY, "
										+"A.PACK_CODE, " 
										+"A.LOC_CODE, "
										+"A.GROSS_WEIGHT, "
										+"A.TARE_WEIGHT, "
										+"A.NET_WEIGHT, " 
										+"A.DIMENSION, "
										+"A.SUPP_CODE__MFG, " 
										+"A.QTY_PER_ART "
									+"FROM STOCK A,INVSTAT B,LOCATION C " 
										+"WHERE A.INV_STAT = B.INV_STAT " 
										+"AND C.LOC_CODE = A.LOC_CODE " 
										+"AND C.LOC_GROUP = '"+locGroup+"' " 
										+"AND A.ITEM_CODE = '"+itemCode+"' " 
										+"AND A.SITE_CODE = '"+siteCode+"' " 
										+"AND A.LOC_CODE  LIKE '"+locCode+"' " 
										+"AND A.LOT_SL    LIKE '"+lotSl+"' "   
										+"AND B.AVAILABLE = 'Y' " 
										+"AND B.USABLE = 'Y' " 
										+"AND A.QUANTITY - A.ALLOC_QTY > 0 " 
										+"ORDER BY A.EXP_DATE, A.CREA_DATE, A.LOT_NO, A.LOT_SL ";
							System.out.println("sql2 :"+sql2);
						}
						rs2 = stmt2.executeQuery(sql2);
						if (!rs2.next())
						{
							continue;
						}
						else
						{
							rs2.beforeFirst();
							while(rs2.next())
							{
								lotNo = rs2.getString(1);
								System.out.println("lotNo :"+lotNo);
								lotSl = rs2.getString(2);
								System.out.println("lotSl :"+lotSl);
								qtyStk = rs2.getDouble(3);
								System.out.println("qtyStk :"+qtyStk);
								expDate = rs2.getDate(4);
								System.out.println("expDate :"+expDate);
								siteCodeMfg = rs2.getString(5);
								System.out.println("siteCodeMfg :"+siteCodeMfg);
								mfgDate = rs2.getDate(6);
								System.out.println("mfgDate :"+mfgDate);
								allocQty = rs2.getDouble(7);
								System.out.println("allocQty :"+allocQty);
								locCode = rs2.getString(9);
								System.out.println("locCode :"+locCode);
								grossWeight = rs2.getDouble(10);
								System.out.println("grossWeight :"+grossWeight);
								tareWeight = rs2.getDouble(11);
								System.out.println("tareWeight :"+tareWeight);
								netWeight = rs2.getDouble(12);
								System.out.println("netWeight :"+netWeight);
								dimension = rs2.getString(13);
								System.out.println("dimension :"+dimension);
								suppCodeMfg = rs2.getString(14);
								System.out.println("suppCodeMfg :"+suppCodeMfg);
								despDt = genericUtility.getColumnValue("desp_date",dom1);	
								System.out.println("despDt :"+despDt);
								qtyPerArt = rs2.getDouble(15);
								
								sql3 = "SELECT CASE WHEN TRACK_SHELF_LIFE IS NULL THEN 'N' ELSE TRACK_SHELF_LIFE END " 
									  +"FROM ITEM WHERE ITEM_CODE = '"+itemCode+"' ";
								System.out.println("sql3  :"+sql3);
								rs3 = stmt3.executeQuery(sql3);
								if (rs3.next())
								{
									trackShelfLife = rs3.getString(1);
									System.out.println("trackShelfLife :"+trackShelfLife);
								}
								if (trackShelfLife.equals("Y"))
								{
									sql3 = "SELECT ORDER_TYPE FROM SORDER WHERE SALE_ORDER = '"+sordNo+"'";
									System.out.println("sql3 :"+sql3);
									rs3 = stmt3.executeQuery(sql3);
									if (rs3.next())
									{
										orderType = rs3.getString(1);
										System.out.println("orderType :"+orderType);
									}
									if (orderType.equals("NE"))
									{
										sql3 = "SELECT MIN_SHELF_LIFE,MAX_SHELF_LIFE FROM SORDITEM "
											  +"WHERE SALE_ORDER = '"+sordNo+"' "
											  +"AND LINE_NO = '"+lineNoOrd+"' "
											  +"AND EXP_LEV = '"+expLev+"'";
										System.out.println("sql3 :"+sql3);
										rs3 = stmt3.executeQuery(sql3);
										if (rs3.next())
										{
											minShelfLife = rs3.getInt(1);
											System.out.println("minShelfLife :"+minShelfLife);
											maxShelfLife = rs3.getInt(2);
											System.out.println("maxShelfLife :"+maxShelfLife);
										}

										chkDate1 = calcExpiry(despDt, minShelfLife + 1);
										System.out.println("chkDate1 :"+chkDate1);

										chkDate3 = sdf.parse(chkDate1);
										System.out.println("chkDate3 :"+chkDate3);

										chkDate2 = calcExpiry(despDt, maxShelfLife);
										System.out.println("chkDate2 :"+chkDate2);

										chkDate4 = sdf.parse(chkDate2);
										System.out.println("chkDate4 :"+chkDate4);
										if (expDate != null)
										{
											expDate1 = new java.util.Date(expDate.getTime());
											System.out.println("expDate1 :"+expDate1);

											if (!(expDate1.compareTo(chkDate3) >= 0 && expDate1.compareTo(chkDate4) <= 0))  
											{
												continue;
											}
										}										
									}
									else
									{
										sql3 = "SELECT MIN_SHELF_LIFE FROM SORDITEM "
												+"WHERE SALE_ORDER = '"+sordNo+"' " 
												+"AND LINE_NO = '"+lineNoOrd+"' " 
												+"AND EXP_LEV = '"+expLev+"'";
										System.out.println("sql3 :"+sql3);
										rs3 = stmt3.executeQuery(sql3);
										if (rs3.next())
										{
											minShelfLife = rs3.getInt(1);
											System.out.println("minShelfLife :"+minShelfLife);
										}
										chkDate1 = calcExpiry(despDt, minShelfLife);
										System.out.println("chkDate1 :"+chkDate1);

										chkDate4 = sdf.parse(chkDate1);
										System.out.println("chkDate4 :"+chkDate4);
										if (expDate != null)
										{
											expDate1 = new java.util.Date(expDate.getTime());
											System.out.println("expDate1 :"+expDate1);

											if (chkDate4.compareTo(expDate1) > 0) 
											{
												continue;
											}
										}										
									}//end else
								}//end if
								//Below section not present in PB code
								String qtyKeyStr = itemCode + siteCode + locCode + lotNo + lotSl;
								if(qtyMap.containsKey(qtyKeyStr))
								{
									qtyDbl = (Double)qtyMap.get(qtyKeyStr);
								}
								//qtyDbl = subtraction to be done on qtyDbl.doubleValue();
								//qtyMap.put(qtyKeyStr, new Double(subtracted value);
								allocQty = allocQty + qtyDbl.doubleValue();
								if (qtyStk - allocQty <= 0)
								{
									continue;
								}
								//End Section
								if (qtyStk - allocQty <= quantityStduom)
								{
									inputQty = qtyStk - allocQty;
									System.out.println("inputQty :"+inputQty);
									quantityStduom = quantityStduom - inputQty;
									System.out.println("quantityStduom :"+quantityStduom);
								}
								else
								{
									inputQty = quantityStduom;
									System.out.println("inputQty :"+inputQty);
									quantityStduom = quantityStduom - inputQty;
									System.out.println("quantityStduom :"+quantityStduom);
								}

								if (inputQty > 0)
								{
									if (qtyStk > 0)
									{
										grossPer = (grossWeight / qtyStk) ;
										System.out.println("grossPer :"+grossPer);
										netPer 	=  (netWeight / qtyStk);
										System.out.println("netPer 	:"+netPer);
										tarePer	=  (tareWeight / qtyStk);
										System.out.println("tarePer	:"+tarePer);
										
										grossWeight = (inputQty * grossPer);
										System.out.println("grossWeight :"+grossWeight);
										netWeight = (inputQty * netPer);
										System.out.println("netWeight :"+netWeight);
										tareWeight = (inputQty * tarePer);
										System.out.println("tareWeight :"+tareWeight);	
									}
									/*valueXmlString.append("<Detail>\r\n");
									valueXmlString.append("<loc_code>").append("<![CDATA[").append(locCode.trim()).append("]]>").append("</loc_code>\r\n");
									valueXmlString.append("</Detail>\r\n");
									//End Comment

									//unit1 = genericUtility.getColumnValue("unit",dom);	
									System.out.println("unit :"+unit1);
									//unitStd1 = genericUtility.getColumnValue("unit__std",dom);	
									System.out.println("unitStd :"+unitStd1);

									qtyStd = inputQty;
									System.out.println("qtyStd :"+qtyStd);
									if (!unit1.equals(unitStd1))
									//if (unit1 == null)
									{
										System.out.println("Calling getConvQuantity...........");
										System.out.println("unitStd1 :"+unitStd1+" \nunit1 :"+unit1+" \nitemCode :"+itemCode+" \nqtyStd :"+qtyStd+" \nconv :"+conv); 
										qtyFact = itmDBAccess.getConvQuantityFact(unitStd1, unit1, itemCode, qtyStd, conv, conn);
										System.out.println("qtyFact.get(1) :"+qtyFact.get(1));
										System.out.println("qtyFact.get(1) :"+qtyFact.get(2));
									}
									else
									{
										qtyFact.add(Integer.toString(1));
										qtyFact.add(Double.toString(qtyStd));
									}
									System.out.println("qtyFact.size() :"+qtyFact.size());
									conv = (Double.parseDouble(qtyFact.get(0).toString()));
									noArt = new Double((Double.parseDouble(qtyFact.get(1).toString())) / qtyPerArt).intValue();
									System.out.println("conv :"+conv);
									valueXmlString.append("<Detail>\r\n");
									valueXmlString.append("<line_no__sord isSrvCallOnChg='1'>").append("<![CDATA[").append(lineNoOrd.trim()).append("]]>").append("</line_no__sord>\r\n");
									valueXmlString.append("<item_code isSrvCallOnChg='0'>").append("<![CDATA[").append(itemCode.trim()).append("]]>").append("</item_code>\r\n");
									valueXmlString.append("<loc_code isSrvCallOnChg='1'>").append("<![CDATA[").append(locCode.trim()).append("]]>").append("</loc_code>\r\n"); //made isSrvCallOnChg='1' - Gulzar 14/03/07 - For seting of mfg_date and exp_date
									valueXmlString.append("<quantity isSrvCallOnChg='1'>").append("<![CDATA[").append(qtyFact.get(1).toString()).append("]]>").append("</quantity>\r\n");  //made isSrvCallOnChg='1'  - Gulzar 14/03/07
									valueXmlString.append("<exp_lev isSrvCallOnChg='1'>").append("<![CDATA[").append(expLev.trim()).append("]]>").append("</exp_lev>\r\n"); //Un-Commented and made isSrvCallOnChg='1' - Gulzar 14/03/07
									//valueXmlString.append("<quantity_real isSrvCallOnChg='0'>").append("<![CDATA[").append(qtyStd).append("]]>").append("</quantity_real>\r\n");
									//valueXmlString.append("<quantity__stduom isSrvCallOnChg='0'>").append("<![CDATA[").append(qtyStd).append("]]>").append("</quantity__stduom>\r\n");
									//valueXmlString.append("<conv__qty_stduom isSrvCallOnChg='0'>").append("<![CDATA[").append(1 / conv).append("]]>").append("</conv__qty_stduom>\r\n");
									valueXmlString.append("<pending_qty isSrvCallOnChg='0'>").append("<![CDATA[").append(qtyOrd).append("]]>").append("</pending_qty>\r\n");
									valueXmlString.append("<lot_no isSrvCallOnChg='1'>").append("<![CDATA[").append(lotNo.trim()).append("]]>").append("</lot_no>\r\n");
									valueXmlString.append("<lot_sl isSrvCallOnChg='1'>").append("<![CDATA[").append(lotSl.trim()).append("]]>").append("</lot_sl>\r\n");
									valueXmlString.append("<gross_weight isSrvCallOnChg='0'>").append("<![CDATA[").append(grossWeight).append("]]>").append("</gross_weight>\r\n");
									valueXmlString.append("<nett_weight isSrvCallOnChg='0'>").append("<![CDATA[").append(netWeight).append("]]>").append("</nett_weight>\r\n");
									valueXmlString.append("<tare_weight isSrvCallOnChg='0'>").append("<![CDATA[").append(tareWeight).append("]]>").append("</tare_weight>\r\n");
									valueXmlString.append("<no_art isSrvCallOnChg='0'>").append("<![CDATA[").append(noArt).append("]]>").append("</no_art>\r\n");
									//valueXmlString.append("<dimension isSrvCallOnChg='0'>").append("<![CDATA[").append(dimension).append("]]>").append("</dimension>\r\n"); //Gulzar 14/03/07
									valueXmlString.append("<dimension isSrvCallOnChg='0'>").append("<![CDATA[").append((dimension == null) ? "":dimension).append("]]>").append("</dimension>\r\n"); //Gulzar 14/02/07
									valueXmlString.append("<site_code__mfg isSrvCallOnChg='0'>").append("<![CDATA[").append(siteCodeMfg).append("]]>").append("</site_code__mfg>\r\n");
	//								valueXmlString.append("<mfg_date>").append("<![CDATA[").append(mfgDate).append("]]>").append("</mfg_date>\r\n");
	//								valueXmlString.append("<expDate>").append("<![CDATA[").append(mfgDate).append("]]>").append("</expDate>\r\n");
									valueXmlString.append("</Detail>\r\n");
									qtyOrd = qtyOrd - qtyStd;
									grossWeight = 0;
									tareWeight = 0;
									netWeight = 0;

									allocQty = allocQty + qtyStd;
									qtyMap.put(qtyKeyStr, new Double(qtyDbl.doubleValue() + qtyStd));
									qtyFact.clear();//Added on 24/01/06
								}
	/*							if(wf_val_data)
								{
								//Here is some code is to be ask to Jiten
								}
	//End Comment
							}
						}
					}
					else
					{
						if(itemCodeOld.indexOf(itemCode) == -1)
						{
							counter = counter + 1;
						}
						rs1.beforeFirst();
						while(rs1.next())
						{
							inputQty = rs1.getDouble("QTY_ALLOC");
							System.out.println("inputQty :"+inputQty);
							if (inputQty > 0)
							{
								expLev = rs1.getString("exp_lev");
								System.out.println("expLev :"+expLev);
								itemCodeOrd	= rs1.getString("item_code__ord");
								System.out.println("itemCodeOrd :"+itemCodeOrd);
								itemCode = rs1.getString("item_code");
								System.out.println("itemCode :"+itemCode);
								locCode = rs1.getString("loc_code");
								System.out.println("locCode :"+locCode);
								lotNo	= rs1.getString("lot_no");
								System.out.println("lotNo :"+lotNo);
								lotSl	= rs1.getString("lot_sl");
								System.out.println("lotSl :"+lotSl);
								mfgDate =	rs1.getDate("mfg_date");
								System.out.println("mfgDate :"+mfgDate);
								expDate =	rs1.getDate("exp_date");
								System.out.println("expDate :"+expDate);
								siteCodeMfg = rs1.getString("site_code__mfg");
								System.out.println("siteCodeMfg :"+siteCodeMfg);

								valueXmlString.append("<Detail>\r\n");
								valueXmlString.append("<line_no__sord isSrvCallOnChg='1'>").append("<![CDATA[").append(lineNoOrd.trim()).append("]]>").append("</line_no__sord>\r\n");
								valueXmlString.append("<item_code isSrvCallOnChg='0'>").append("<![CDATA[").append(itemCode.trim()).append("]]>").append("</item_code>\r\n");
								valueXmlString.append("<exp_lev isSrvCallOnChg='1'>").append("<![CDATA[").append(expLev.trim()).append("]]>").append("</exp_lev>\r\n"); //Un-Commented and made isSrvCallOnChg='1' - Gulzar 15/03/07
								valueXmlString.append("<loc_code isSrvCallOnChg='1'>").append("<![CDATA[").append(locCode.trim()).append("]]>").append("</loc_code>\r\n"); //Made isSrvCallOnChg='1' - Gulzar 15/03/07 - For seting of mfg_date and exp_date
								valueXmlString.append("<quantity isSrvCallOnChg='1'>").append("<![CDATA[").append(inputQty).append("]]>").append("</quantity>\r\n");
								valueXmlString.append("<quantity_real isSrvCallOnChg='0'>").append("<![CDATA[").append(inputQty).append("]]>").append("</quantity_real>\r\n");
								valueXmlString.append("<quantity__stduom isSrvCallOnChg='0'>").append("<![CDATA[").append(inputQty * convQtyStduom).append("]]>").append("</quantity__stduom>\r\n");
								valueXmlString.append("<lot_no isSrvCallOnChg='1'>").append("<![CDATA[").append(lotNo.trim()).append("]]>").append("</lot_no>\r\n");
								valueXmlString.append("<lot_sl isSrvCallOnChg='1'>").append("<![CDATA[").append(lotSl.trim()).append("]]>").append("</lot_sl>\r\n");
								valueXmlString.append("<site_code__mfg isSrvCallOnChg='0'>").append("<![CDATA[").append(siteCodeMfg).append("]]>").append("</site_code__mfg>\r\n");
	//							valueXmlString.append("<mfg_date>").append("<![CDATA[").append(mfgDate).append("]]>").append("</mfg_date>\r\n");
	//							valueXmlString.append("<exp_date>").append("<![CDATA[").append(expDate).append("]]>").append("</exp_date>\r\n");
								valueXmlString.append("</Detail>\r\n");

								//Update statement commented by Jiten as per Piyush Sir - 04/11/06
								/*sql2 = "UPDATE SORDALLOC " 
											+"SET QTY_ALLOC	= QTY_ALLOC - ? "
											+"WHERE SALE_ORDER = ? "
											+"AND LINE_NO =	? "
											+"AND EXP_LEV =	? "
											+"AND ITEM_CODE__ORD = ? "
											+"AND ITEM_CODE	= ? "
											+"AND LOC_CODE = ? "
											+"AND LOT_NO = ? "
											+"AND LOT_SL = ? ";
								System.out.println("The Update sql :"+sql2);
								pstmt = conn.prepareStatement(sql2);							
								pstmt.setDouble(1, inputQty);
								pstmt.setString(2, sordNo);
								pstmt.setString(3, lineNoOrd);
								pstmt.setString(4, expLev);
								pstmt.setString(5, itemCodeOrd);
								pstmt.setString(6, itemCode);
								pstmt.setString(7, locCode);
								pstmt.setString(8, lotNo);
								pstmt.setString(9, lotSl);
								updateCnt = pstmt.executeUpdate();
								System.out.println("update the no of records in sordalloc :"+updateCnt);
								sql2 = "UPDATE SORDITEM "
									  +"SET QTY_ALLOC =	QTY_ALLOC -"+inputQty
									  +"WHERE SALE_ORDER = '"+sordNo+"' "
									  +"AND LINE_NO	= '"+lineNoOrd+"' "
									  +"AND EXP_LEV	= '"+expLev+"' ";

								System.out.println("The update Sql :"+sql2);	
								pstmt = conn.prepareStatement(sql2);							
								updateCnt = pstmt.executeUpdate();
								System.out.println("update the no of records in sorditem :"+updateCnt);
								//End Comment
							}
						}//while end
					}//end else
					itemCodeOld = itemCodeOld+ " "+itemCode;
					System.out.println("itemCodeOld :"+itemCodeOld);
				}// while end
				//valueXmlString.append("</Root>\r\n");			
			}
			valueXmlString.append("</Root>\r\n");			
		}//try end
		catch(SQLException sqx)
		{
			System.out.println("The Exception caught from Despatch(Default) :"+sqx);
			throw new ITMException(sqx);
		}
		catch(Exception e)
		{
			System.out.println("The Exception caught from Despatch(Default) :"+e);
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				conn.close();
				conn = null;
				qtyMap = null;
			}catch(Exception e){}
		}
		System.out.println("valueXmlString.toString() :"+valueXmlString.toString());
		return valueXmlString.toString();
	}*///End Comment Gulzar - 23/01/08
	//Added Changes - Gulzar - 23/01/08
	private String actionDefault(Document dom, Document dom1, String objContext, String xtraParams) throws RemoteException, ITMException
	{
		PreparedStatement pstmt = null;
		String unit1 = "", unitStd1 = "";
		String sordNo = "", locCode = "", lotSl = "", lotNo = "", siteCode = "", errCode = "", errString = "";
		String lineNo = "", expLev = "", itemCode = "", itemCodeOrd = "", itemCodeOld = ""; 
		String sql = "", sql1 = "", sql2 = "", sql3 = "";
		java.util.Date expDate1 = null, chkDate4 = null, chkDate3 = null;
		Statement stmt = null, stmt1 = null, stmt2 = null, stmt3 = null;
		Connection conn = null;
		ResultSet rs = null, rs1 = null, rs2 = null, rs3 = null;
		StringBuffer valueXmlString = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root>\r\n");
		double qtyStk = 0d, allocQty = 0d, inputQty = 0d, quantityStduom = 0d, convQtyStduom = 0d;
		double grossPer = 0d,netPer = 0d, tarePer = 0d, qtyStd = 0d;
		double grossWeight = 0d, tareWeight = 0d, netWeight = 0d, conv = 0;
		java.sql.Date expDate = null, mfgDate = null;
		String dimension = "", suppCodeMfg = "", despDt = "", trackShelfLife = "", orderType = "", siteCodeMfg = "";
		int minShelfLife = 0, maxShelfLife = 0, noOfItems = 0, cntItemCode = 0, updateCnt = 0, counter = 0; 
 		ArrayList qtyFact = new ArrayList();
		String locCode1 = "", lotSl1 = "", locGroup = "", lineNoSord = "";
		String chkDate1 = "", chkDate2 = "",detCnt = "0",lineNoOrd = ""; 
		ConnDriver connDriver = new ConnDriver();
		GenericUtility genericUtility = GenericUtility.getInstance();
		ITMDBAccessEJB itmDBAccess = new ITMDBAccessEJB();
		HashMap qtyMap = new HashMap();
		Double qtyDbl = new Double(0.0);
		double qtyOrd = 0.0, qtyPerArt = 0.00;
		int noArt = 0;
		String updateStatus = "";
		// Added - Gulzar - 23/01/08
		String siteCodeDet = "", itemDescr = "";
		String applyPrice = "";
		String itemType = "";
		String netWtUnit = "";
		String rateUnit = "";
		String rateOpt = "";
		String rateUnitSord = "";
		String custCode = "";
		String packCode = "";
		String sqlTemp ="",qtyDetailStr = "";
		String despatchId = "";
		String taxClass = "";
		String taxChap = "";
		String taxEnv = "";

		double discount = 0d;
		double shipperQtyNew = 0d;
		double integralQtyNew = 0d;
		double balQty = 0d;
		double looseQty = 0d;
		double grossWeight1 = 0d;
		double discAmt =0d ;
		double netWeight1 = 0d;
		double orderedQty = 0d;
		double despatchedQty = 0d; 
		double convRtuomStd = 0d; 
		double grossWtPerArt = 0d; 
		double tareWtPerArt = 0d;
		double rateStduom = 0d;
		double packQty = 0d;
		double netWtPerArt = 0d;
		double packGrossWeight = 0d;
		double packNettWeight = 0d;
		double rateClg = 0d;
		double palletWt = 0d;
		double OrdQty = 0;
		ResultSet rsTemp =null;
		DecimalFormat df = new DecimalFormat("#########.###");
		Statement stmtTemp = null;
		// End Addition - Gulzar - 23/01/08
		try
		{
			detCnt = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"detCnt"); //Will be Uncommented later - Jiten
			if (detCnt == null || detCnt.trim().length() == 0)
			{
				detCnt = "0";
			}
			if (dom != null)
			{
				updateStatus = getCurrentUpdateFlag(dom);
				System.out.println("updateStatus :: "+updateStatus);
			}
			//Added Changes By Gulzar 28-02-07
			if ((Integer.parseInt(detCnt) > 1) || (!updateStatus.equals("A")))
			{
				errCode = "VTDESPD1";
				errString = itmDBAccess.getErrorString("",errCode,"");
				return errString;
			}
			//if (detCnt.equals("0")) //Gulzar 27-02-07
			else //End Changes Gulzar 28-02-08
			{
				conn = connDriver.getConnectDB("DriverITM");
				stmt = conn.createStatement();
				stmt1 = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
				stmt2 = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
				stmt3 = conn.createStatement();
				stmtTemp = conn.createStatement(); // Added - Gulzar - 23/01/08 

				sordNo = genericUtility.getColumnValue("sord_no",dom1);	 
				System.out.println("sordNo :"+sordNo);
				SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
				if (sordNo == null && sordNo.trim().length() == 0)
				{
					errCode = "VTPCK1";
					errString = itmDBAccess.getErrorString("",errCode,"","",conn);
					return errString;
				}
				if (dom != null)
				{
					locCode1 = genericUtility.getColumnValue("loc_code",dom);	 
					lotSl1	 = genericUtility.getColumnValue("lot_sl",dom);	 
				}
				siteCode = genericUtility.getColumnValue("site_code",dom1);
				System.out.println("locCode :"+locCode1);
				System.out.println("lotSl :"+lotSl1);
				System.out.println("siteCode :"+siteCode);

				sql = "SELECT NO_OF_ITEMS FROM DOC_NO_ITEMS " 
					 +"WHERE SITE_CODE = '"+siteCode+"' "
					 +"AND MOD_NAME = UPPER('W_DESPATCH')";
				System.out.println("sql :"+sql);					
				rs = stmt.executeQuery(sql);
				if (rs.next())
				{
					noOfItems = rs.getInt(1);
					System.out.println("noOfItems :"+noOfItems);
				}
				else
				{
					noOfItems = 0;
					System.out.println("noOfItems :"+noOfItems);
				}
				rs.close();
				rs = null;
				// Added - Gulzar - 23/01/08
				despatchId = genericUtility.getColumnValue("desp_id",dom1);	 
				System.out.println("Despatch Id :"+despatchId);
				if (despatchId == null)
				{
					despatchId = "";
				}
				// End Addition - Gulzar - 23/01/08
				/*-- Commented And Changes Below as There is a Changes in PB logic
				sql ="SELECT SORDDET.SALE_ORDER, "   
						 +"SORDDET.LINE_NO, "   
						 +"SORDDET.SITE_CODE, "   
						 +"SORDDET.ITEM_CODE, "   
						 +"SORDDET.ITEM_FLG, "   
						 +"SORDDET.QUANTITY, "   
						 +"SORDDET.UNIT, "   
						 +"SORDDET.DSP_DATE, "   
						 +"SORDDET.RATE, "   
						 +"SORDDET.DISCOUNT, "   
						 +"SORDDET.TAX_AMT, "   
						 +"SORDDET.TAX_CLASS, "   
						 +"SORDDET.TAX_CHAP, "   
						 +"SORDDET.TAX_ENV, "   
						 +"SORDDET.NET_AMT, "   
						 +"SORDDET.REMARKS, "   
						 +"SORDDET.STATUS, "   
						 +"SORDDET.STATUS_DATE, "   
						 +"SORDDET.CHG_DATE, "   
						 +"SORDDET.CHG_USER, "   
						 +"SORDDET.CHG_TERM, "   
						 +"SORDDET.ITEM_DESCR, "   
						 +"SORDDET.UNIT__RATE, "   
						 +"SORDDET.CONV__QTY_STDUOM, "   
						 +"SORDDET.CONV__RTUOM_STDUOM, "   
						 +"SORDDET.UNIT__STD, "   
						 +"SORDDET.QUANTITY__STDUOM, "   
						 +"SORDDET.RATE__STDUOM, "   
						 +"SORDDET.NO_ART, "   
						 +"SORDDET.PACK_CODE, "   
						 +"SORDDET.LINE_NO__CONTR, "   
						 +"SORDDET.PACK_INSTR, "   
						 +"SORDDET.SPEC_REF, "   
						 +"SORDDET.PACK_QTY, "   
						 +"SORDDET.ITEM_SER, "   
						 +"SORDDET.RATE__CLG, "   
						 +"SORDDET.MFG_CODE, "   
						 +"SORDDET.CONTRACT_NO, "   
						 +"PACKING.DESCR, "   
						 +"SORDDET.SPEC_ID, "   
						 +"SORDDET.ORD_VALUE, "   
						 +"SORDDET.ITEM_SER__PROM, "   
						 +"SORDDET.SPECIFIC_INSTR, "   
						 +"SORDDET.ITEM_CODE__ORD, "   
						 +"SORDDET.PALLET_CODE, "   
						 +"SORDDET.NO_PALLET, "   
						 +"SORDDET.OVER_SHIP_PERC, "   
						 +"SORDDET.COMM_PERC_1, "   
						 +"SORDDET.COMM_PERC_2, "   
						 +"SORDDET.COMM_PERC_3, "   
						 +"SORDDET.COMM_PERC_ON_1, "   
						 +"SORDDET.COMM_PERC_ON_2, "   
						 +"SORDDET.COMM_PERC_ON_3, "   
						 +"SORDDET.SALES_PERS_COMM_1, "   
						 +"SORDDET.SALES_PERS_COMM_2, "   
						 +"SORDDET.SALES_PERS_COMM_3, "   
						 +"SORDDET.PRICE_LIST__DISC, "   
						 +"SORDDET.RATE__STD, "   
						 +"SORDER.FIN_SCHEME, "   
						 +"SORDDET.MAX_SHELF_LIFE, "   
						 +"SORDDET.MIN_SHELF_LIFE, "   
						 +"SPACE(250) AS ITEM_SPECS, "   
						 +"SPACE(250) AS ST_SHRINK, "   
						 +"SORDDET.LOC_TYPE, "   
						 +"SPACE(250) AS ST_SCHEME,  "
						 +"SORDDET.SORDFORM_NO ,  "  
						 +"SORDDET.LINE_NO__SFORM ,  " 
						 +"FN_GET_ITMSTK(SORDDET.ITEM_CODE,SORDDET.SITE_CODE) STK_QTY,  "  
						 +"SORDDET.APPL_SEG ,  "  
						 +"UOM.DESCR  "  
				 +"FROM SORDDET, PACKING, SORDER  "
				 +"WHERE SORDDET.PACK_CODE = PACKING.PACK_CODE " 
				 +"AND SORDER.SALE_ORDER = SORDDET.SALE_ORDER "
				 +"AND SORDDET.SALE_ORDER = '"+sordNo+"'";*/// End Changes Gulzar 12/03/07
				 //Added By Gulzar 12/03/07 - Added join with SORDITEM as the default was not working  For Scheme item
				 sql ="SELECT SORDDET.SALE_ORDER, "   
						 +"SORDDET.LINE_NO, "   
						 +"SORDDET.SITE_CODE, "   
					//	 +"SORDDET.ITEM_CODE, " //Gulzar 14/03/07 - As it should take from SORDITEM
						 +"SORDITEM.ITEM_CODE, " //Gulzar 14/03/07
						 +"SORDDET.ITEM_FLG, "   
					//	 +"SORDDET.QUANTITY, "   //Gulzar 14/03/07 - As it should take from SORDITEM
						 +"SORDITEM.QUANTITY - SORDITEM.QTY_DESP AS QUANTITY, "  //Gulzar 14/03/07
						 +"SORDDET.UNIT, "   
						 +"SORDDET.DSP_DATE, "   
						 +"SORDDET.RATE, "   
						 +"SORDDET.DISCOUNT, "   
						 +"SORDDET.TAX_AMT, "   
						 +"SORDDET.TAX_CLASS, "   
						 +"SORDDET.TAX_CHAP, "   
						 +"SORDDET.TAX_ENV, "   
						 +"SORDDET.NET_AMT, "   
						 +"SORDDET.REMARKS, "   
						 +"SORDDET.STATUS, "   
						 +"SORDDET.STATUS_DATE, "   
						 +"SORDDET.CHG_DATE, "   
						 +"SORDDET.CHG_USER, "   
						 +"SORDDET.CHG_TERM, "   
						 +"SORDDET.ITEM_DESCR, "   
						 +"SORDDET.UNIT__RATE, "   
						 +"SORDDET.CONV__QTY_STDUOM, "   
						 +"SORDDET.CONV__RTUOM_STDUOM, "   
						 +"SORDDET.UNIT__STD, "   
						 +"SORDDET.QUANTITY__STDUOM, "   
						 +"SORDDET.RATE__STDUOM, "   
						 +"SORDDET.NO_ART, "   
						 +"SORDDET.PACK_CODE, "   
						 +"SORDDET.LINE_NO__CONTR, "   
						 +"SORDDET.PACK_INSTR, "   
						 +"SORDDET.SPEC_REF, "   
						 +"SORDDET.PACK_QTY, "   
						 +"SORDDET.ITEM_SER, "   
						 +"SORDDET.RATE__CLG, "   
						 +"SORDDET.MFG_CODE, "   
						 +"SORDDET.CONTRACT_NO, "   
						 +"PACKING.DESCR, "   
						 +"SORDDET.SPEC_ID, "   
						 +"SORDDET.ORD_VALUE, "   
						 +"SORDDET.ITEM_SER__PROM, "   
						 +"SORDDET.SPECIFIC_INSTR, "   
					//	 +"SORDDET.ITEM_CODE__ORD, "   //Gulzar 14/03/07 - As it should take from SORDITEM
						 +"SORDITEM.ITEM_CODE__ORD, "  //Gulzar 14/03/07
						 +"SORDDET.PALLET_CODE, "   
						 +"SORDDET.NO_PALLET, "   
						 +"SORDDET.OVER_SHIP_PERC, "   
						 +"SORDDET.COMM_PERC_1, "   
						 +"SORDDET.COMM_PERC_2, "   
						 +"SORDDET.COMM_PERC_3, "   
						 +"SORDDET.COMM_PERC_ON_1, "   
						 +"SORDDET.COMM_PERC_ON_2, "   
						 +"SORDDET.COMM_PERC_ON_3, "   
						 +"SORDDET.SALES_PERS_COMM_1, "   
						 +"SORDDET.SALES_PERS_COMM_2, "   
						 +"SORDDET.SALES_PERS_COMM_3, "   
						 +"SORDDET.PRICE_LIST__DISC, "   
						 +"SORDDET.RATE__STD, "   
						 +"SORDER.FIN_SCHEME, "   
						 /* // Remarked and taken from sorditem  - Gulzar - 23/01/08
						 +"SORDDET.MAX_SHELF_LIFE, "   
						 +"SORDDET.MIN_SHELF_LIFE, "   
						 // End Remark - Gulzar - 23/01/08
						 */
						 +"SORDITEM.MAX_SHELF_LIFE, "   // Taken from sorditem - Gulzar - 23/01/08
						 +"SORDITEM.MIN_SHELF_LIFE, "   // Taken from sorditem - Gulzar - 23/01/08
						 +"SORDDET.MAX_SHELF_LIFE, "   
						 +"SORDDET.MIN_SHELF_LIFE, "   
						 +"SPACE(250) AS ITEM_SPECS, "   
						 +"SPACE(250) AS ST_SHRINK, "   
						 +"SORDDET.LOC_TYPE, "   
						 +"SPACE(250) AS ST_SCHEME,  "
						 +"SORDDET.SORDFORM_NO ,  "  
						 +"SORDDET.LINE_NO__SFORM ,  "  
						 +"FN_GET_ITMSTK(SORDDET.ITEM_CODE,SORDDET.SITE_CODE) STK_QTY,  "  
						 +"SORDDET.APPL_SEG ,  "  
						 +"UOM.DESCR,  "
						 +"SORDITEM.EXP_LEV, " //Gulzar 14/03/07
						 +"SORDITEM.QTY_ALLOC,  " //Gulzar 14/03/07
						 +"SORDER.LOC_GROUP, "//Added by Gulzar - 23/01/08
						 +"SORDER.ORDER_TYPE, "//Added by Gulzar - 23/01/08
						 +"SORDER.CUST_CODE, "//Added by Gulzar - 23/01/08
						 +"SORDDET.QUANTITY AS ORD_QTY "
				 +"FROM SORDDET LEFT OUTER JOIN PACKING ON SORDDET.PACK_CODE = PACKING.PACK_CODE LEFT OUTER JOIN UOM ON SORDDET.UNIT = UOM.UNIT,  "
				 +"SORDER, "
				 +"SORDITEM  "
				 +"WHERE SORDER.SALE_ORDER = SORDDET.SALE_ORDER "
				 +"AND SORDITEM.SALE_ORDER = SORDDET.SALE_ORDER "
				 +"AND SORDITEM.LINE_NO = SORDDET.LINE_NO "
				 +"AND SORDITEM.SALE_ORDER = '"+sordNo+"'"
				 +"AND SORDITEM.LINE_TYPE = 'I' ";
				 //End Changes Gulzar 12/03/07
				 System.out.println("sql for all data ::"+sql);
				 rs = stmt.executeQuery(sql);
				 while (rs.next())
				 {
					if (noOfItems > 0 && counter >= noOfItems)
					{	
						errCode = "VTITMGRZRO";
						errString = itmDBAccess.getErrorString("",errCode,"","",conn);
						return errString;
					}
					lotSl = lotSl1; // Re-set with Picked-up from DOM.
					locCode = locCode1; // Re-set with Picked-up from DOM.
					//lineNo = rs.getString("LINE_NO");
					lineNoOrd = rs.getString("LINE_NO");
					System.out.println("lineNo :"+lineNoOrd);
					siteCode = genericUtility.getColumnValue("site_code",dom1);	
					//lineNoOrd = genericUtility.getColumnValue("line_no__sord",dom); //Commented as line_no from despatch is to be used
					System.out.println("siteCode :"+siteCode);

					/*-- Commented By Gulzar 14/03/07 - As the same is retrived from main query
					sql1 = "SELECT ITEM_CODE__ORD, QUANTITY,EXP_LEV, ITEM_CODE, QTY_ALLOC "
						  +"FROM SORDITEM WHERE SALE_ORDER = '"+sordNo+"' " // sordNo of Header is Used whereas in PB it taken from detail
						  +"AND LINE_NO = '"+lineNoOrd+"'"+" AND LINE_TYPE = 'I'"; 
					System.out.println("sql1 :"+sql1);
					rs1 = stmt1.executeQuery(sql1);
					if (rs1.next())
					{
						itemCodeOrd = rs1.getString(1);
						System.out.println("itemCodeOrd :"+itemCodeOrd);
						expLev = rs1.getString(3);
						System.out.println("expLev :"+expLev);
						itemCode = rs1.getString(4);
						System.out.println("itemCode :"+itemCode);
					}
					stmt1.close();
					stmt1 = null;
					*///End Comment Gulzar 14/03/07

					//Added By Gulzar 14/03/07
					itemCodeOrd = rs.getString("ITEM_CODE__ORD");
					System.out.println("itemCodeOrd :"+itemCodeOrd);
					expLev = rs.getString("EXP_LEV");
					System.out.println("expLev :"+expLev);
					itemCode = rs.getString("ITEM_CODE");
					System.out.println("itemCode :"+itemCode);
					//End changes Gulzar 14/03/07

					//Start - Added by Gulzar - 23/01/08
					sql1 = "SELECT DESCR FROM ITEM WHERE ITEM_CODE = '"+itemCode+"'";
					stmt1 = conn.createStatement();
					rs1 = stmt1.executeQuery(sql1);
					if (rs1.next())
					{
						itemDescr = rs1.getString("DESCR");
					}
					rs1.close();
					rs1 = null;
					rateClg = rs.getDouble("RATE__CLG");
					orderedQty = rs.getDouble("QUANTITY");
					OrdQty = rs.getDouble("ORD_QTY");
					System.out.println("orderedQty :"+orderedQty);
					siteCodeDet = rs.getString("SITE_CODE");
					System.out.println("siteCodeDet :"+siteCodeDet);
					unit1 = rs.getString("UNIT");
					unitStd1 = rs.getString("UNIT__STD");
					convQtyStduom = rs.getDouble("CONV__QTY_STDUOM");
					System.out.println("convQtyStduom :"+convQtyStduom);
					quantityStduom = rs.getDouble("QUANTITY__STDUOM");
					rateStduom = rs.getDouble("RATE__STDUOM");
					custCode = rs.getString("CUST_CODE");
					packCode = rs.getString("PACK_CODE");

					convRtuomStd = rs.getDouble("CONV__RTUOM_STDUOM"); 
					rateUnitSord = rs.getString("UNIT__RATE");
					locGroup = rs.getString("LOC_GROUP");
					System.out.println("locGroup :"+locGroup);
					orderType = rs.getString("ORDER_TYPE");
					System.out.println("orderType :"+orderType);
					minShelfLife = rs.getInt("MIN_SHELF_LIFE");
					System.out.println("minShelfLife :"+minShelfLife);

					maxShelfLife = rs.getInt("MAX_SHELF_LIFE");
					System.out.println("maxShelfLife :"+maxShelfLife);
					if (rateUnitSord == null)
					{
						rateUnitSord = "";
					}
					sql1 = "SELECT SUM(QUANTITY__STDUOM) AS QUANTITY__STDUOM FROM DESPATCHDET WHERE SORD_NO = '" + sordNo + "'" + 
							" AND LINE_NO__SORD = '" + lineNoOrd + "'" + 
							" AND EXP_LEV = '" + expLev + "'" + 
							" AND DESP_ID <> '" + despatchId + "'";
					//stmt1 = conn.createStatement();
					rs1 = stmt1.executeQuery(sql1);
					despatchedQty = 0d;
					if (rs1.next())
					{
						despatchedQty = rs1.getDouble("QUANTITY__STDUOM");
					}
					sql1 = "";
					rs1.close();
					stmt1 = null;
					quantityStduom = quantityStduom - despatchedQty; 

					if (quantityStduom <= 0)
					{
						continue;
					}

					///qtyOrd = quantityStduom; // commented by manoharan 27/12/08
					qtyOrd = orderedQty; // added by manoharan 27/12/08
					System.out.println("quantityStduom :"+quantityStduom);
					//End - Added by Gulzar - 23/01/08


					// Commented - Gulzar - 23/01/08
					/**
					sql1 = "SELECT UNIT__STD, CONV__QTY_STDUOM, UNIT, PACK_INSTR, QUANTITY__STDUOM, "
						  +"CASE WHEN NO_ART IS NULL THEN 0 ELSE NO_ART END "
						  +"FROM SORDDET WHERE SALE_ORDER = '"+sordNo+"'"+" AND LINE_NO ='"+lineNoOrd+"'";
					System.out.println("sql1 :"+sql1);
					stmt1 = conn.createStatement();
					rs1 = stmt1.executeQuery(sql1);
					if (rs1.next())
					{
						unit1 = rs1.getString("UNIT");
						unitStd1 = rs1.getString("UNIT__STD");
						convQtyStduom = rs1.getDouble(2);
						System.out.println("convQtyStduom :"+convQtyStduom);
						quantityStduom = rs1.getDouble(5);
						qtyOrd = quantityStduom;
						System.out.println("quantityStduom :"+quantityStduom);
					}
					stmt1.close();
					stmt1 = null;
					
					
					if (quantityStduom <= 0)
					{
						continue;
					}
					sql1 = "SELECT LOC_CODE,QUANTITY, EXP_LEV, ITEM_CODE, "
						  +"LOT_NO, LOT_SL, UNIT__STD, CONV__QTY_STDUOM, UNIT "
						  +"FROM SORDALLOC WHERE SALE_ORDER ='"+sordNo+"'"+" AND LINE_NO ='"+lineNoOrd+"'";
					System.out.println("sql1 :"+sql1);
					stmt1 = conn.createStatement();
					rs1 = stmt1.executeQuery(sql1);
					if (rs1.next())
					{
						if (locCode == null || locCode.trim().length() == 0)
						{
							//locCode = rs.getString(1); //Commented - Gulzar - 14/03/07 - As it should take from rs1 not rs 
							locCode = rs1.getString(1);
						}						
						System.out.println("locCode :"+locCode);
						lotNo = rs1.getString(5);
						System.out.println("lotNo :"+lotNo);
						lotSl = rs1.getString(6);
						System.out.println("lotSl :"+lotSl);
					}					
					stmt1.close();
					stmt1 = null;
					// End Comment - Gulzar - 23/01/08
					***/

					sql1 = "SELECT SORDALLOC.LOT_NO, "   
							 +"SORDALLOC.LOT_SL, "   
							 +"SORDALLOC.QTY_ALLOC, "   
							 +"SORDALLOC.DATE_ALLOC, "   
							 +"SORDALLOC.STATUS, "   
							 +"SORDALLOC.ITEM_GRADE, "   
							 +"SORDALLOC.EXP_DATE, "   
							 +"SORDALLOC.ALLOC_MODE, "   
							 +"SORDALLOC.SITE_CODE, "   
							 +"SORDALLOC.LOC_CODE, "   
							 +"SORDALLOC.SALE_ORDER, "   
							 +"SORDALLOC.LINE_NO, "   
							 +"SORDALLOC.EXP_LEV, "   
							 +"SORDALLOC.ITEM_CODE__ORD, "   
							 +"SORDALLOC.ITEM_CODE, "   
							 +"SORDALLOC.ITEM_REF, "   
							 +"SORDALLOC.QUANTITY, "   
							 +"SORDALLOC.UNIT, "   
							 +"LOCATION.DESCR, "   
							 +"SORDALLOC.CONV__QTY_STDUOM, "   
							 +"SORDALLOC.UNIT__STD, "   
							 +"SORDALLOC.QUANTITY__STDUOM, "   
							 +"SORDALLOC.MFG_DATE, "   
							 +"SORDALLOC.SITE_CODE__MFG "   
						+"FROM SORDALLOC, LOCATION "  
						+"WHERE SORDALLOC.LOC_CODE = LOCATION.LOC_CODE "
						+"AND SORDALLOC.SALE_ORDER = '"+sordNo+"' "
						+"AND SORDALLOC.LINE_NO = '"+lineNoOrd+"' "
						+"AND SORDALLOC.EXP_LEV = '"+expLev+"' "
						+"AND SORDALLOC.ITEM_CODE__ORD = '"+itemCodeOrd+"' "
						+"AND SORDALLOC.ITEM_CODE = '"+itemCode+"' "
						+"AND CASE WHEN SORDALLOC.STATUS IS NULL THEN ' ' ELSE SORDALLOC.STATUS END  <> 'D' ";
					System.out.println("sql1 :"+sql1);
					stmt1 = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
					rs1 = stmt1.executeQuery(sql1);
					if(!rs1.next())
					{
						if(locCode == null || locCode.trim().length() == 0)
						{
							locCode = "%";
							System.out.println("locCode :"+locCode);
						}
						else
						{
							locCode = locCode.trim() + "%";
							System.out.println("locCode :"+locCode);
						}
						if (lotSl == null || lotSl.trim().length() == 0)
						{
							lotSl = "%";
							System.out.println("lotSl :"+lotSl);
						}
						else
						{
							lotSl = lotSl.trim() + "%";
							System.out.println("lotSl :"+lotSl);

						}

						/***
						// Commented - Gulzar - 23/01/08
						sql2 = "SELECT LOC_GROUP FROM SORDER "
								+"WHERE SALE_ORDER ='"+sordNo+"'";
						System.out.println("sql2 :"+sql2);
						rs2 = stmt2.executeQuery(sql2);
						if (rs2.next())
						{
							locGroup = rs2.getString(1);
							System.out.println("locGroup :"+locGroup);
						}
						if (locGroup == null || locGroup.trim().length() == 0)
						{
							//sql2 = "SELECT COUNT(A.ITEM_CODE) FROM STOCK A, INVSTAT B ,LOCATION C "
							sql2 = "SELECT COUNT(*) FROM STOCK A, INVSTAT B ,LOCATION C "
										+"WHERE A.INV_STAT  = B.INV_STAT "
										+"AND C.LOC_CODE = A.LOC_CODE "
										+"AND A.ITEM_CODE = '"+itemCode+"' "
										+"AND A.SITE_CODE = '"+siteCode+"' "
										+"AND A.LOC_CODE  LIKE '"+locCode+"' "
										+"AND A.LOT_SL    LIKE '"+lotSl+"' "
										+"AND B.AVAILABLE = 'Y' "
										+"AND B.USABLE = 'Y' "
										+"AND A.QUANTITY  > 0 ";
							System.out.println("sql2 :"+sql2);
						}
						else
						{
							//sql2 = "SELECT COUNT(A.ITEM_CODE) FROM STOCK A, INVSTAT B ,LOCATION C "
							sql2 = "SELECT COUNT(*) FROM STOCK A, INVSTAT B ,LOCATION C "
										+"WHERE A.INV_STAT  = B.INV_STAT "
										+"AND C.LOC_CODE = A.LOC_CODE "
										+"AND C.LOC_GROUP = '"+locGroup+"' "
										+"AND A.ITEM_CODE = '"+itemCode+"' "
										+"AND A.SITE_CODE = '"+siteCode+"' "
										+"AND A.LOC_CODE  LIKE '"+locCode+"' "
										+"AND A.LOT_SL    LIKE '"+lotSl+"' " 
										+"AND B.AVAILABLE = 'Y' "
										+"AND B.USABLE = 'Y' "
										+"AND A.QUANTITY  > 0 "; 
							System.out.println("sql2 :"+sql2);
						}
						rs2 = stmt2.executeQuery(sql2);
						if (rs2.next())
						{
							cntItemCode = rs2.getInt(1);
							System.out.println("cntItemCode :"+cntItemCode);
						}
						if (cntItemCode == 0)
						{
							continue;
						}
						else
						{
							if(itemCodeOld.indexOf(itemCode) == -1)
							{
								counter = counter + 1;
								System.out.println("Counter :"+counter);
							}
						}
						// End Comment - Gulzar - 23/01/08
						***/

						if (locGroup == null || locGroup.trim().length() == 0)
						{
							sql2 = "SELECT A.LOT_NO,A.LOT_SL, "
										+"A.QUANTITY,A.EXP_DATE, " 
										+"A.SITE_CODE__MFG, "
										+"A.MFG_DATE, "
										+"A.ALLOC_QTY, "
										+"A.PACK_CODE, " 
										+"A.LOC_CODE, "
										+"A.GROSS_WEIGHT, "
										+"A.TARE_WEIGHT, "
										+"A.NET_WEIGHT, " 
										+"A.DIMENSION, "
										+"A.SUPP_CODE__MFG, " 
										+"A.QTY_PER_ART, "
										+"A.GROSS_WT_PER_ART, " // Gulzar - 23/01/08
										+"A.TARE_WT_PER_ART, "  // Gulzar - 23/01/08
										+"A.PALLET_WT " //Gulzar - 30/01/08
								   +"FROM STOCK A,INVSTAT B " 
										+"WHERE A.INV_STAT = B.INV_STAT " 
										+"AND A.ITEM_CODE = '"+itemCode+"' "  
										+"AND A.SITE_CODE = '"+siteCode+"' "  
										+"AND A.LOC_CODE LIKE '"+locCode+"' "  
										+"AND A.LOT_SL LIKE '"+lotSl+"' "   
										+"AND B.AVAILABLE = 'Y' " 
										+"AND B.USABLE = 'Y' " 
										+"AND A.QUANTITY - A.ALLOC_QTY > 0 " 
										+"ORDER BY A.EXP_DATE,A.CREA_DATE, A.LOT_NO, A.LOT_SL ";
							System.out.println("sql2 :"+sql2);
						}
						else
						{
							sql2 = "SELECT A.LOT_NO, "
										+"A.LOT_SL, "
										+"A.QUANTITY, "
										+"A.EXP_DATE, " 
										+"A.SITE_CODE__MFG, "
										+"A.MFG_DATE, "
										+"A.ALLOC_QTY, "
										+"A.PACK_CODE, " 
										+"A.LOC_CODE, "
										+"A.GROSS_WEIGHT, "
										+"A.TARE_WEIGHT, "
										+"A.NET_WEIGHT, " 
										+"A.DIMENSION, "
										+"A.SUPP_CODE__MFG, " 
										+"A.QTY_PER_ART, "
										+"A.GROSS_WT_PER_ART, " // Gulzar - 23/01/08
										+"A.TARE_WT_PER_ART, "  // Gulzar - 23/01/08
										+"A.PALLET_WT " //Gulzar - 30/01/08
									+"FROM STOCK A,INVSTAT B,LOCATION C " 
										+"WHERE A.INV_STAT = B.INV_STAT " 
										+"AND C.LOC_CODE = A.LOC_CODE " 
										+"AND C.LOC_GROUP = '"+locGroup+"' " 
										+"AND A.ITEM_CODE = '"+itemCode+"' " 
										+"AND A.SITE_CODE = '"+siteCode+"' " 
										+"AND A.LOC_CODE  LIKE '"+locCode+"' " 
										+"AND A.LOT_SL    LIKE '"+lotSl+"' "   
										+"AND B.AVAILABLE = 'Y' " 
										+"AND B.USABLE = 'Y' " 
										+"AND A.QUANTITY - A.ALLOC_QTY > 0 " 
										+"ORDER BY A.EXP_DATE, A.CREA_DATE, A.LOT_NO, A.LOT_SL ";
							System.out.println("sql2 :"+sql2);
						}
						rs2 = stmt2.executeQuery(sql2);
						if (!rs2.next())
						{
							continue;
						}
						else
						{
							//Start-Added by Gulzar - 23/01/08
							if(itemCodeOld.indexOf(itemCode) == -1)
							{
								counter = counter + 1;
								System.out.println("Counter :"+counter);
							}
							//End-Added by Gulzar - 23/01/08
							rs2.beforeFirst();
							while(rs2.next())
							{
								// 27/12/08 manoharan bug fix reinitilise
								qtyFact = null;
								qtyFact = new ArrayList();
								// end 27/12/08 manoharan bug fix reinitilise
								lotNo = rs2.getString(1);
								System.out.println("lotNo :"+lotNo);
								lotSl = rs2.getString(2);
								System.out.println("lotSl :"+lotSl);
								qtyStk = rs2.getDouble(3);
								System.out.println("qtyStk :"+qtyStk);
								expDate = rs2.getDate(4);
								System.out.println("expDate :"+expDate);
								siteCodeMfg = rs2.getString(5);
								System.out.println("siteCodeMfg :"+siteCodeMfg);
								mfgDate = rs2.getDate(6);
								System.out.println("mfgDate :"+mfgDate);
								allocQty = rs2.getDouble(7);
								System.out.println("allocQty :"+allocQty);
								locCode = rs2.getString(9);
								System.out.println("locCode :"+locCode);
								grossWeight = rs2.getDouble(10);
								System.out.println("grossWeight :"+grossWeight);
								tareWeight = rs2.getDouble(11);
								System.out.println("tareWeight :"+tareWeight);
								netWeight = rs2.getDouble(12);
								System.out.println("netWeight :"+netWeight);
								dimension = rs2.getString(13);
								System.out.println("dimension :"+dimension);
								suppCodeMfg = rs2.getString(14);
								System.out.println("suppCodeMfg :"+suppCodeMfg);
								despDt = genericUtility.getColumnValue("desp_date",dom1);	
								System.out.println("despDt :"+despDt);
								qtyPerArt = rs2.getDouble(15);
								grossWtPerArt = rs2.getDouble(16); // Added - Gulzar - 23/01/08
								tareWtPerArt = rs2.getDouble(17); // Added - Gulzar - 23/01/08
								palletWt	= rs2.getDouble("PALLET_WT"); //Added - Gulzar - 30/01/08

								/*
								// Commented - Gulzar - 23/01/08
								sql3 = "SELECT CASE WHEN TRACK_SHELF_LIFE IS NULL THEN 'N' ELSE TRACK_SHELF_LIFE END " 
									  +"FROM ITEM WHERE ITEM_CODE = '"+itemCode+"' ";
								// End Comment - Gulzar - 23/01/08
								*/
								
								// Added - Gulzar - 23/01/08
								sql3 = "SELECT CASE WHEN TRACK_SHELF_LIFE IS NULL THEN 'N' ELSE TRACK_SHELF_LIFE END , DESCR " 
									  +"FROM ITEM WHERE ITEM_CODE = '"+itemCode+"' "; 
								// End Add - Gulzar - 23/01/08

								System.out.println("sql3  :"+sql3);
								rs3 = stmt3.executeQuery(sql3);
								if (rs3.next())
								{
									trackShelfLife = rs3.getString(1);
									System.out.println("trackShelfLife :"+trackShelfLife);
								}
								if (trackShelfLife.equals("Y"))
								{
									/*
									// Commented - Gulzar - 23/01/08
									sql3 = "SELECT ORDER_TYPE FROM SORDER WHERE SALE_ORDER = '"+sordNo+"'";
									System.out.println("sql3 :"+sql3);
									rs3 = stmt3.executeQuery(sql3);
									if (rs3.next())
									{
										orderType = rs3.getString(1);
										System.out.println("orderType :"+orderType);
									}
									*/
									// End Comment - Gulzar - 23/01/08

									if (orderType.equals("NE"))
									{
										/*
										// Commented - Gulzar - 23/01/08
										sql3 = "SELECT MIN_SHELF_LIFE,MAX_SHELF_LIFE FROM SORDITEM "
											  +"WHERE SALE_ORDER = '"+sordNo+"' "
											  +"AND LINE_NO = '"+lineNoOrd+"' "
											  +"AND EXP_LEV = '"+expLev+"'";
										System.out.println("sql3 :"+sql3);
										rs3 = stmt3.executeQuery(sql3);
										if (rs3.next())
										{
											minShelfLife = rs3.getInt(1);
											System.out.println("minShelfLife :"+minShelfLife);
											maxShelfLife = rs3.getInt(2);
											System.out.println("maxShelfLife :"+maxShelfLife);
										}
										// End Comment - Gulzar - 23/01/08
										*/


										chkDate1 = calcExpiry(despDt, minShelfLife + 1);
										System.out.println("chkDate1 :"+chkDate1);

										chkDate3 = sdf.parse(chkDate1);
										System.out.println("chkDate3 :"+chkDate3);

										chkDate2 = calcExpiry(despDt, maxShelfLife);
										System.out.println("chkDate2 :"+chkDate2);

										chkDate4 = sdf.parse(chkDate2);
										System.out.println("chkDate4 :"+chkDate4);
										if (expDate != null)
										{
											expDate1 = new java.util.Date(expDate.getTime());
											System.out.println("expDate1 :"+expDate1);

											if (!(expDate1.compareTo(chkDate3) >= 0 && expDate1.compareTo(chkDate4) <= 0))  
											{
												continue;
											}
										}										
									}
									else
									{
										/*
										// Commented - Gulzar - 23/01/08
										sql3 = "SELECT MIN_SHELF_LIFE FROM SORDITEM "
												+"WHERE SALE_ORDER = '"+sordNo+"' " 
												+"AND LINE_NO = '"+lineNoOrd+"' " 
												+"AND EXP_LEV = '"+expLev+"'";
										System.out.println("sql3 :"+sql3);
										rs3 = stmt3.executeQuery(sql3);
										if (rs3.next())
										{
											minShelfLife = rs3.getInt(1);
											System.out.println("minShelfLife :"+minShelfLife);
										}
										// End Comment - Gulzar - 23/01/08
										*/

										chkDate1 = calcExpiry(despDt, minShelfLife);
										System.out.println("chkDate1 :"+chkDate1);

										chkDate4 = sdf.parse(chkDate1);
										System.out.println("chkDate4 :"+chkDate4);
										if (expDate != null)
										{
											expDate1 = new java.util.Date(expDate.getTime());
											System.out.println("expDate1 :"+expDate1);

											if (chkDate4.compareTo(expDate1) > 0) 
											{
												continue;
											}
										}										
									}//end else
								}//end if
								//Below section not present in PB code
								String qtyKeyStr = itemCode + siteCode + locCode + lotNo + lotSl;
								if(qtyMap.containsKey(qtyKeyStr))
								{
									qtyDbl = (Double)qtyMap.get(qtyKeyStr);
								}
								//qtyDbl = subtraction to be done on qtyDbl.doubleValue();
								//qtyMap.put(qtyKeyStr, new Double(subtracted value);
								allocQty = allocQty + qtyDbl.doubleValue();
								if (qtyStk - allocQty <= 0)
								{
									continue;
								}
								//End Section

								// if (qtyStk - allocQty <= quantityStduom) // Remarked - Gulzar - 23/01/08
								if (qtyStk - allocQty <= qtyOrd) // Changed - Gulzar - 23/01/08
								{
									inputQty = qtyStk - allocQty;
									System.out.println("inputQty :"+inputQty);
									//quantityStduom = quantityStduom - inputQty; // Remarked - Gulzar - 23/01/08
									//System.out.println("quantityStduom :"+quantityStduom); // Remarked - Gulzar - 23/01/08
								}
								else
								{
									//inputQty = quantityStduom;  // Remarked - Gulzar - 23/01/08
									inputQty = qtyOrd;  // Changed - Piyush - 24/12/07
									System.out.println("inputQty :"+inputQty);
									//quantityStduom = quantityStduom - inputQty; // Remarked - Gulzar - 23/01/08
									//System.out.println("quantityStduom :"+quantityStduom); // Remarked - Gulzar - 23/01/08
								}


								if (inputQty > 0)
								{
									/* -- Commented and Changes Below - Gulzar - 31/01/08 - The below calculation will be done as per case "lot_no" of nvo_bo_despatch
									if (qtyStk > 0)
									{
										grossPer = (grossWeight / qtyStk) ;
										grossPer = df.parse(df.format(grossPer)).doubleValue();
										System.out.println("grossPer :"+grossPer);
										netPer 	=  (netWeight / qtyStk);
										netPer = df.parse(df.format(netPer)).doubleValue();
										System.out.println("netPer 	:"+netPer);
										tarePer	=  (tareWeight / qtyStk);
										tarePer = df.parse(df.format(tarePer)).doubleValue();
										System.out.println("tarePer	:"+tarePer);
										
										grossWeight = (inputQty * grossPer);
										System.out.println("grossWeight :"+grossWeight);
										netWeight = (inputQty * netPer);
										System.out.println("netWeight :"+netWeight);
										tareWeight = (inputQty * tarePer);
										System.out.println("tareWeight :"+tareWeight);	
									}
									*/
									//End Comment - Gulzar - 31/01/08
									//Added - Gulzar - 23/01/08
									netWtPerArt = convRtuomStd;
									sql3 = "SELECT ITEM_TYPE, UNIT__NETWT, UNIT__RATE FROM ITEM WHERE ITEM_CODE = '" + itemCodeOrd + "'";
									rs3 = stmt3.executeQuery(sql3);
									if (rs3.next())
									{
										itemType = rs3.getString(1);
										if (itemType == null)
										{
											itemType = "";
										}
										netWtUnit = rs3.getString(2);
										if (netWtUnit == null)
										{
											netWtUnit = "";
										}
										rateUnit = rs3.getString(3);
										if (rateUnit == null)
										{
											rateUnit = "";
										}
									}
									rs3.close();
									rs3 = null;
									if (itemType.trim().length() > 0)
									{
										sql3 = "SELECT RATE_OPT FROM ITEM_TYPE WHERE ITEM_TYPE = '" + itemType + "'" ;
										rs3 = stmt3.executeQuery(sql3);
										if (rs3.next())
										{
											rateOpt = rs3.getString(1);
											if (rateOpt == null)
											{
												rateOpt = "";
											}
										}
										rs3.close();
										rs3 = null;
										if (rateOpt == "1")
										{
											if ( netWtUnit.trim().equals(rateUnit.trim()))
											{
												if (!unit1.trim().equals(rateUnitSord.trim()))
												{
													netWtPerArt = grossWtPerArt - tareWtPerArt;
												}
											}
										}
									}
									// End Addition  - Gulzar - 23/01/08

									/*valueXmlString.append("<Detail>\r\n");
									valueXmlString.append("<loc_code>").append("<![CDATA[").append(locCode.trim()).append("]]>").append("</loc_code>\r\n");
									valueXmlString.append("</Detail>\r\n");*/

									//unit1 = genericUtility.getColumnValue("unit",dom);	
									System.out.println("unit :"+unit1);
									//unitStd1 = genericUtility.getColumnValue("unit__std",dom);	
									System.out.println("unitStd :"+unitStd1);

									qtyStd = inputQty;
									System.out.println("qtyStd :"+qtyStd);
									if (!unit1.equals(unitStd1))
									//if (unit1 == null)
									{
										System.out.println("Calling getConvQuantity...........");
										System.out.println("unitStd1 :"+unitStd1+" \nunit1 :"+unit1+" \nitemCode :"+itemCode+" \nqtyStd :"+qtyStd+" \nconv :"+conv); 
										qtyFact = itmDBAccess.getConvQuantityFact(unitStd1, unit1, itemCode, qtyStd, conv, conn);
										System.out.println("qtyFact.get(1) :"+qtyFact.get(1));
										System.out.println("qtyFact.get(1) :"+qtyFact.get(2));
									}
									else
									{
										qtyFact.add(Integer.toString(1));
										qtyFact.add(Double.toString(qtyStd));
									}
									System.out.println("qtyFact.size() :"+qtyFact.size());
									conv = (Double.parseDouble(qtyFact.get(0).toString()));
									convQtyStduom = (Double.parseDouble(qtyFact.get(0).toString())); //Gulzar - 23/01/08
									System.out.println("conv :"+conv);

									// Commented - Gulzar - 23/01/08
									//noArt = new Double((Double.parseDouble(qtyFact.get(1).toString())) / qtyPerArt).intValue();
									// End Comment - Gulzar - 23/01/08

									// Added - Gulzar - 23/01/08
									inputQty = (Double.parseDouble(qtyFact.get(1).toString()));
									qtyStd = inputQty;
									System.out.println("bal Qty  (double) :: "+Double.parseDouble(qtyFact.get(1).toString()));
									double[] noArtInfo = getNoArt(siteCodeDet , custCode , itemCode , packCode , Double.parseDouble(qtyFact.get(1).toString()) , "B" , 0 , 0 , conn); 
									System.out.println("getNoArt :: "+noArtInfo[0]+" "+noArtInfo[1]+" "+noArtInfo[2]);
									/*-- Temporarly Commented  - Gulzar - 30/01/08
									if (noArtInfo[0] == 0 && qtyPerArt > 0)
									{
										System.out.println("qtyPerArt ::"+qtyPerArt);
										noArt = new Double((Double.parseDouble(qtyFact.get(1).toString())) / qtyPerArt).intValue();
										System.out.println("noArt [qtyFact.get(1).toString())) / qtyPerArt)] ::"+noArt);
									}*/
									//End Temporarly Commented  - Gulzar - 30/01/08
									System.out.println("qtyPerArt ::"+qtyPerArt);
									if ( qtyPerArt > 0 )
									{
										noArt = new Double((Double.parseDouble(qtyFact.get(1).toString())) / qtyPerArt).intValue();
										System.out.println("noArt [qtyFact.get(1).toString())) / qtyPerArt)] ::"+noArt);
										grossWeight = (df.parse(df.format(grossWtPerArt / qtyPerArt)).doubleValue()) * qtyStk;
										tareWeight	= (df.parse(df.format(tareWtPerArt / qtyPerArt)).doubleValue()) * qtyStk;
										netWeight	= df.parse(df.format(grossWeight - tareWeight)).doubleValue();
										System.out.println("grossWeight [if ( qtyPerArt > 0 )] ::"+grossWeight);
										System.out.println("tareWeight [if ( qtyPerArt > 0 )] ::"+tareWeight);
										System.out.println("netWeight [if ( qtyPerArt > 0 )] ::"+netWeight);
									}
									//End Add - Gulzar - 30/01/08
									packQty = 0d; 
									if (noArt > 0)
									{
										packQty = 0d;
										packQty = (Double.parseDouble(qtyFact.get(1).toString())) / noArt;
										packQty = df.parse(df.format(packQty)).doubleValue();
									}

									shipperQtyNew = noArtInfo[1];
									integralQtyNew = noArtInfo[2];

									System.out.println("bal Qty  (double) :: "+Double.parseDouble(qtyFact.get(1).toString()));
									double[] noArtInfo1 = getNoArt(siteCodeDet , custCode , itemCode , packCode , Double.parseDouble(qtyFact.get(1).toString()) , "S" , 0 , 0 , conn); 
									balQty = Double.parseDouble(qtyFact.get(1).toString()) - ( shipperQtyNew * noArtInfo1[0]);
									double[] noArtInfo2 = getNoArt(siteCodeDet , custCode , itemCode , packCode , balQty , "I" , 0 , 0 , conn); 
									integralQtyNew = noArtInfo2[2];

									shipperQtyNew = shipperQtyNew * noArtInfo1[0];
									integralQtyNew = integralQtyNew * noArtInfo2[0];

									looseQty = Double.parseDouble(qtyFact.get(1).toString()) - ( shipperQtyNew + integralQtyNew );

									qtyDetailStr = "Shipper Quantity = " + shipperQtyNew +"  Integral Quantity = " + integralQtyNew + "  Loose Quantity = " + looseQty ;

									if ( packCode != null && packCode.trim().length() > 0 )
									{
										sqlTemp = "	SELECT GROSS_WEIGHT,NETT_WEIGHT FROM  PACKING 	WHERE  PACK_CODE = '"+ packCode +"' ";
										System.out.println("sqlTemp ::"+sqlTemp);
										rsTemp = stmtTemp.executeQuery(sqlTemp);
										if ( rsTemp.next() )
										{
											packGrossWeight = rsTemp.getDouble("GROSS_WEIGHT");
											packNettWeight = rsTemp.getDouble("NETT_WEIGHT");
										}
										rsTemp.close();
										rsTemp = null;
										System.out.println("packGrossWeight ::"+packGrossWeight);
										System.out.println("packNettWeight ::"+packNettWeight);
										System.out.println("noArt ::"+noArt);

										packGrossWeight = packGrossWeight * noArt;
										packNettWeight = packNettWeight * noArt;
										if ( grossWeight == 0 )
										{
											grossWeight = packGrossWeight;
											netWeight	= packNettWeight;
											tareWeight = grossWeight - netWeight;
										}
										//End Gulzar - 29/01/08
									}

									sqlTemp = "SELECT APPLY_PRICE  FROM BOM WHERE BOM_CODE = '"+ itemCode +"' ";
									System.out.println("sqlTemp ::"+sqlTemp);
									rsTemp = stmtTemp.executeQuery(sqlTemp);
									if ( rsTemp.next() )
									{
										applyPrice = rsTemp.getString("APPLY_PRICE");
									}
									rsTemp.close();
									rsTemp = null;
									if ( applyPrice == null )
									{
										sqlTemp = "SELECT DISCOUNT FROM SORDDET WHERE SALE_ORDER = '"+ sordNo +"' AND "
										+" LINE_NO = '"+ lineNoOrd +"' ";
										System.out.println("sqlTemp ::"+sqlTemp);
										rsTemp = stmtTemp.executeQuery(sqlTemp);
										if ( rsTemp.next() )
										{
											discAmt = rsTemp.getDouble("DISCOUNT");
										}
										rsTemp.close();
										rsTemp = null;

										discAmt = (discAmt/100) * ( qtyStd *  rateStduom ); 
									}
									
									grossWeight = df.parse(df.format(grossWeight)).doubleValue();
									netWeight	= df.parse(df.format(netWeight)).doubleValue();
									tareWeight	= df.parse(df.format(tareWeight)).doubleValue();

									discount = rs.getDouble("DISCOUNT"); 
									taxClass = rs.getString("TAX_CLASS");
									taxChap	 = rs.getString("TAX_CHAP"); 
									taxEnv	 = rs.getString("TAX_ENV"); 

									//End - Add - Gulzar - 23/01/08
									/* -- Commented Changes Below - Gulzar - 23/01/08
									valueXmlString.append("<Detail>\r\n");
									valueXmlString.append("<line_no__sord isSrvCallOnChg='1'>").append("<![CDATA[").append(lineNoOrd.trim()).append("]]>").append("</line_no__sord>\r\n");
									valueXmlString.append("<item_code isSrvCallOnChg='0'>").append("<![CDATA[").append(itemCode.trim()).append("]]>").append("</item_code>\r\n");
									valueXmlString.append("<loc_code isSrvCallOnChg='1'>").append("<![CDATA[").append(locCode.trim()).append("]]>").append("</loc_code>\r\n"); //made isSrvCallOnChg='1' - Gulzar 14/03/07 - For seting of mfg_date and exp_date
									valueXmlString.append("<quantity isSrvCallOnChg='1'>").append("<![CDATA[").append(qtyFact.get(1).toString()).append("]]>").append("</quantity>\r\n");  //made isSrvCallOnChg='1'  - Gulzar 14/03/07
									valueXmlString.append("<exp_lev isSrvCallOnChg='1'>").append("<![CDATA[").append(expLev.trim()).append("]]>").append("</exp_lev>\r\n"); //Un-Commented and made isSrvCallOnChg='1' - Gulzar 14/03/07
									//valueXmlString.append("<quantity_real isSrvCallOnChg='0'>").append("<![CDATA[").append(qtyStd).append("]]>").append("</quantity_real>\r\n");
									//valueXmlString.append("<quantity__stduom isSrvCallOnChg='0'>").append("<![CDATA[").append(qtyStd).append("]]>").append("</quantity__stduom>\r\n");
									//valueXmlString.append("<conv__qty_stduom isSrvCallOnChg='0'>").append("<![CDATA[").append(1 / conv).append("]]>").append("</conv__qty_stduom>\r\n");
									valueXmlString.append("<pending_qty isSrvCallOnChg='0'>").append("<![CDATA[").append(qtyOrd).append("]]>").append("</pending_qty>\r\n");
									valueXmlString.append("<lot_no isSrvCallOnChg='1'>").append("<![CDATA[").append(lotNo.trim()).append("]]>").append("</lot_no>\r\n");
									valueXmlString.append("<lot_sl isSrvCallOnChg='1'>").append("<![CDATA[").append(lotSl.trim()).append("]]>").append("</lot_sl>\r\n");
									valueXmlString.append("<gross_weight isSrvCallOnChg='0'>").append("<![CDATA[").append(grossWeight).append("]]>").append("</gross_weight>\r\n");
									valueXmlString.append("<nett_weight isSrvCallOnChg='0'>").append("<![CDATA[").append(netWeight).append("]]>").append("</nett_weight>\r\n");
									valueXmlString.append("<tare_weight isSrvCallOnChg='0'>").append("<![CDATA[").append(tareWeight).append("]]>").append("</tare_weight>\r\n");
									valueXmlString.append("<no_art isSrvCallOnChg='0'>").append("<![CDATA[").append(noArt).append("]]>").append("</no_art>\r\n");
									//valueXmlString.append("<dimension isSrvCallOnChg='0'>").append("<![CDATA[").append(dimension).append("]]>").append("</dimension>\r\n"); //Gulzar 14/03/07
									valueXmlString.append("<dimension isSrvCallOnChg='0'>").append("<![CDATA[").append((dimension == null) ? "":dimension).append("]]>").append("</dimension>\r\n"); //Gulzar 14/02/07
									valueXmlString.append("<site_code__mfg isSrvCallOnChg='0'>").append("<![CDATA[").append(siteCodeMfg).append("]]>").append("</site_code__mfg>\r\n");
	//								valueXmlString.append("<mfg_date>").append("<![CDATA[").append(mfgDate).append("]]>").append("</mfg_date>\r\n");
	//								valueXmlString.append("<expDate>").append("<![CDATA[").append(mfgDate).append("]]>").append("</expDate>\r\n");
									valueXmlString.append("</Detail>\r\n");
									*/
									//End  Comment Gulzar - 23/01/08
									valueXmlString.append("<Detail>\r\n");
									valueXmlString.append("<sord_no isSrvCallOnChg='0'>").append("<![CDATA[").append(sordNo).append("]]>").append("</sord_no>\r\n"); 
									//commented by rajendra as per subrato sir 
									//valueXmlString.append("<line_no__sord isSrvCallOnChg='0'>").append("<![CDATA[").append(lineNoOrd).append("]]>").append("</line_no__sord>\r\n");
									
									valueXmlString.append("<line_no__sord isSrvCallOnChg='1'>").append("<![CDATA[").append(lineNoOrd).append("]]>").append("</line_no__sord>\r\n");
									valueXmlString.append("<exp_lev isSrvCallOnChg='1'>").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("<item_code__ord isSrvCallOnChg='0'>").append("<![CDATA[").append(itemCodeOrd).append("]]>").append("</item_code__ord>\r\n");
									//valueXmlString.append("<quantity__ord isSrvCallOnChg='0'>").append("<![CDATA[").append(orderedQty).append("]]>").append("</quantity__ord>\r\n");
									valueXmlString.append("<quantity__ord isSrvCallOnChg='0'>").append("<![CDATA[").append(OrdQty).append("]]>").append("</quantity__ord>\r\n");
									valueXmlString.append("<site_code isSrvCallOnChg='0'>").append("<![CDATA[").append(siteCodeDet).append("]]>").append("</site_code>\r\n");
									//valueXmlString.append("<quantity_real isSrvCallOnChg='0'>").append("<![CDATA[").append(qtyFact.get(1).toString()).append("]]>").append("</quantity_real>\r\n");
									valueXmlString.append("<quantity_real isSrvCallOnChg='0'>").append("<![CDATA[").append(OrdQty).append("]]>").append("</quantity_real>\r\n");
									valueXmlString.append("<unit isSrvCallOnChg='0'>").append("<![CDATA[").append(unit1).append("]]>").append("</unit>\r\n");
									valueXmlString.append("<unit__std isSrvCallOnChg='0'>").append("<![CDATA[").append(unitStd1).append("]]>").append("</unit__std>\r\n");
									valueXmlString.append("<conv__qty_stduom isSrvCallOnChg='0'>").append("<![CDATA[").append(convQtyStduom).append("]]>").append("</conv__qty_stduom>\r\n");
									valueXmlString.append("<conv__rtuom_stduom isSrvCallOnChg='0'>").append("<![CDATA[").append(netWtPerArt).append("]]>").append("</conv__rtuom_stduom>\r\n"); 
									//for test by msalam
									valueXmlString.append("<loc_code isSrvCallOnChg='1'>").append("<![CDATA[").append(locCode).append("]]>").append("</loc_code>\r\n"); 
									valueXmlString.append("<pack_qty isSrvCallOnChg='0'>").append("<![CDATA[").append(packQty).append("]]>").append("</pack_qty>\r\n");  
									valueXmlString.append("<quantity__stduom isSrvCallOnChg='0'>").append("<![CDATA[").append(qtyFact.get(1).toString()).append("]]>").append("</quantity__stduom>\r\n");
									//System.out.println( "RATE__STDUOM :: " + rateStduom );
									//commented by smalam on 041108 as this gets set in lot no item change
									//valueXmlString.append("<rate__stduom isSrvCallOnChg='0'>").append("<![CDATA[").append(rateStduom).append("]]>").append("</rate__stduom>\r\n");  
									//next line commented as it gets set from lot no item change on 041108 by msalam
									//valueXmlString.append("<rate__clg isSrvCallOnChg='0'>").append("<![CDATA[").append(rateClg).append("]]>").append("</rate__clg>\r\n"); 
									valueXmlString.append("<qty_details isSrvCallOnChg='0'>").append("<![CDATA[").append(qtyDetailStr).append("]]>").append("</qty_details>\r\n");  
									valueXmlString.append("<disc_amt isSrvCallOnChg='0'>").append("<![CDATA[").append(discAmt).append("]]>").append("</disc_amt>\r\n");  									
									valueXmlString.append("<pending_qty isSrvCallOnChg='0'>").append("<![CDATA[").append(qtyOrd).append("]]>").append("</pending_qty>\r\n");
									valueXmlString.append("<gross_weight isSrvCallOnChg='0'>").append("<![CDATA[").append(grossWeight).append("]]>").append("</gross_weight>\r\n");
									valueXmlString.append("<nett_weight isSrvCallOnChg='0'>").append("<![CDATA[").append(netWeight).append("]]>").append("</nett_weight>\r\n");
									valueXmlString.append("<tare_weight isSrvCallOnChg='0'>").append("<![CDATA[").append(tareWeight).append("]]>").append("</tare_weight>\r\n");
									valueXmlString.append("<pallet_wt isSrvCallOnChg='0'>").append("<![CDATA[").append(palletWt).append("]]>").append("</pallet_wt>\r\n");
									valueXmlString.append("<no_art isSrvCallOnChg='0'>").append("<![CDATA[").append(noArt).append("]]>").append("</no_art>\r\n");
									valueXmlString.append("<dimension isSrvCallOnChg='0'>").append("<![CDATA[").append((dimension == null) ? "":dimension).append("]]>").append("</dimension>\r\n"); 
									valueXmlString.append("<site_code__mfg isSrvCallOnChg='0'>").append("<![CDATA[").append(siteCodeMfg).append("]]>").append("</site_code__mfg>\r\n");
						
									valueXmlString.append("<quantity isSrvCallOnChg='1'>").append("<![CDATA[").append(qtyFact.get(1).toString()).append("]]>").append("</quantity>\r\n");  
									System.out.println( "lot No 1 :: " + lotNo );
									valueXmlString.append("<lot_no isSrvCallOnChg='1'>").append("<![CDATA[").append(lotNo).append("]]>").append("</lot_no>\r\n");
									//valueXmlString.append("<lot_sl isSrvCallOnChg='0'>").append("<![CDATA[").append(lotSl).append("]]>").append("</lot_sl>\r\n");
									//changed for item change as per subrto sir 
									System.out.println( "lot_sl :: " + lotSl );
									valueXmlString.append("<lot_sl isSrvCallOnChg='1'>").append("<![CDATA[").append(lotSl).append("]]>").append("</lot_sl>\r\n");
									valueXmlString.append("<discount isSrvCallOnChg='0'>").append(discount).append("</discount>"); //Gulzar - 21/01/08
									valueXmlString.append("<tax_class isSrvCallOnChg='0'>").append("<![CDATA[").append((taxClass == null) ?"":taxClass).append("]]>").append("</tax_class>\r\n");
									valueXmlString.append("<tax_chap isSrvCallOnChg='0'>").append("<![CDATA[").append((taxChap == null) ?"":taxChap).append("]]>").append("</tax_chap>\r\n"); 
									valueXmlString.append("<tax_env isSrvCallOnChg='0'>").append("<![CDATA[").append((taxEnv == null) ?"":taxEnv).append("]]>").append("</tax_env>\r\n"); 
									
									valueXmlString.append("</Detail>\r\n");
									// End Addition - Piyush - 24/12/07
									System.out.println( "Ic Str shb :: " + valueXmlString.toString() );
									qtyOrd = qtyOrd - qtyStd;
									qtyOrd = df.parse(df.format(qtyOrd)).doubleValue();// Added - Gukzar - 23/01/08 
									System.out.println("Quentity Pending: " + qtyOrd); // Added - Gukzar - 23/01/08
									System.out.println("Quentity Standard: " + qtyStd);// Added - Gukzar - 23/01/08
									grossWeight = 0;
									tareWeight = 0;
									netWeight = 0;

									allocQty = allocQty + qtyStd;
									qtyMap.put(qtyKeyStr, new Double(qtyDbl.doubleValue() + qtyStd));
									qtyFact.clear();//Added on 24/01/06
								}
	/*							if(wf_val_data)
								{
								//Here is some code is to be ask to Jiten
								}
	*/
							}
						}
					}
					else
					{
						if(itemCodeOld.indexOf(itemCode) == -1)
						{
							counter = counter + 1;
						}
						rs1.beforeFirst();
						while(rs1.next())
						{
							inputQty = rs1.getDouble("QTY_ALLOC");
							System.out.println("inputQty :"+inputQty);
							if (inputQty > 0)
							{
								expLev = rs1.getString("exp_lev");
								System.out.println("expLev :"+expLev);
								itemCodeOrd	= rs1.getString("item_code__ord");
								System.out.println("itemCodeOrd :"+itemCodeOrd);
								itemCode = rs1.getString("item_code");
								System.out.println("itemCode :"+itemCode);
								locCode = rs1.getString("loc_code");
								System.out.println("locCode :"+locCode);
								lotNo	= rs1.getString("lot_no");
								System.out.println("lotNo :"+lotNo);
								lotSl	= rs1.getString("lot_sl");
								System.out.println("lotSl :"+lotSl);
								mfgDate =	rs1.getDate("mfg_date");
								System.out.println("mfgDate :"+mfgDate);
								expDate =	rs1.getDate("exp_date");
								System.out.println("expDate :"+expDate);
								siteCodeMfg = rs1.getString("site_code__mfg");
								System.out.println("siteCodeMfg :"+siteCodeMfg);
								
								/* -- Commented And Changes Below - Gulzar - 23/01/08
								valueXmlString.append("<Detail>\r\n");
								valueXmlString.append("<line_no__sord isSrvCallOnChg='1'>").append("<![CDATA[").append(lineNoOrd.trim()).append("]]>").append("</line_no__sord>\r\n");
								valueXmlString.append("<item_code isSrvCallOnChg='0'>").append("<![CDATA[").append(itemCode.trim()).append("]]>").append("</item_code>\r\n");
								valueXmlString.append("<exp_lev isSrvCallOnChg='1'>").append("<![CDATA[").append(expLev.trim()).append("]]>").append("</exp_lev>\r\n"); //Un-Commented and made isSrvCallOnChg='1' - Gulzar 15/03/07
								valueXmlString.append("<loc_code isSrvCallOnChg='1'>").append("<![CDATA[").append(locCode.trim()).append("]]>").append("</loc_code>\r\n"); //Made isSrvCallOnChg='1' - Gulzar 15/03/07 - For seting of mfg_date and exp_date
								valueXmlString.append("<quantity isSrvCallOnChg='1'>").append("<![CDATA[").append(inputQty).append("]]>").append("</quantity>\r\n");
								valueXmlString.append("<quantity_real isSrvCallOnChg='0'>").append("<![CDATA[").append(inputQty).append("]]>").append("</quantity_real>\r\n");
								valueXmlString.append("<quantity__stduom isSrvCallOnChg='0'>").append("<![CDATA[").append(inputQty * convQtyStduom).append("]]>").append("</quantity__stduom>\r\n");
								valueXmlString.append("<lot_no isSrvCallOnChg='1'>").append("<![CDATA[").append(lotNo.trim()).append("]]>").append("</lot_no>\r\n");
								valueXmlString.append("<lot_sl isSrvCallOnChg='1'>").append("<![CDATA[").append(lotSl.trim()).append("]]>").append("</lot_sl>\r\n");
								valueXmlString.append("<site_code__mfg isSrvCallOnChg='0'>").append("<![CDATA[").append(siteCodeMfg).append("]]>").append("</site_code__mfg>\r\n");
	//							valueXmlString.append("<mfg_date>").append("<![CDATA[").append(mfgDate).append("]]>").append("</mfg_date>\r\n");
	//							valueXmlString.append("<exp_date>").append("<![CDATA[").append(expDate).append("]]>").append("</exp_date>\r\n");
								valueXmlString.append("</Detail>\r\n");
								*/
								//End Comment Gulzar - 23/01/08
								//Added - Gulzar - 23/01/08
								valueXmlString.append("<Detail>\r\n");
								//next line changed by msalam on 311008 for itemchange on lot_no as per discussion with danish and modified line added
								//valueXmlString.append("<lot_no isSrvCallOnChg='0'>").append("<![CDATA[").append(lotNo.trim()).append("]]>").append("</lot_no>\r\n");
								//System.out.println( "lotNo :: " +  lotNo.trim() );
								
								valueXmlString.append("<line_no__sord isSrvCallOnChg='1'>").append("<![CDATA[").append(lineNoOrd.trim()).append("]]>").append("</line_no__sord>\r\n");
								
								valueXmlString.append("<item_code isSrvCallOnChg='0'>").append("<![CDATA[").append(itemCode.trim()).append("]]>").append("</item_code>\r\n");
								
								valueXmlString.append("<exp_lev isSrvCallOnChg='1'>").append("<![CDATA[").append(expLev.trim()).append("]]>").append("</exp_lev>\r\n"); //Un-Commented and made isSrvCallOnChg='1' - Gulzar 15/03/07
								//for test by msalam
								valueXmlString.append("<loc_code isSrvCallOnChg='1'>").append("<![CDATA[").append(locCode.trim()).append("]]>").append("</loc_code>\r\n"); //Made isSrvCallOnChg='1' - Gulzar 15/03/07 - For seting of mfg_date and exp_date
								valueXmlString.append("<quantity_real isSrvCallOnChg='0'>").append("<![CDATA[").append(inputQty).append("]]>").append("</quantity_real>\r\n");
								valueXmlString.append("<quantity__stduom isSrvCallOnChg='0'>").append("<![CDATA[").append(inputQty * convQtyStduom).append("]]>").append("</quantity__stduom>\r\n");
								
								//valueXmlString.append("<lot_sl isSrvCallOnChg='1'>").append("<![CDATA[").append(lotSl.trim()).append("]]>").append("</lot_sl>\r\n");
								//changed for item change as per subrto sir
								//valueXmlString.append("<lot_sl isSrvCallOnChg='1'>").append("<![CDATA[").append(lotSl.trim()).append("]]>").append("</lot_sl>\r\n");
								//changed by msalam for test on 251008
								//System.out.println( "LOt Sl For test :: " + lotSl.trim() );
								valueXmlString.append("<quantity isSrvCallOnChg='1'>").append("<![CDATA[").append(inputQty).append("]]>").append("</quantity>\r\n");
								//
								valueXmlString.append("<lot_no isSrvCallOnChg='1'>").append("<![CDATA[").append(lotNo).append("]]>").append("</lot_no>\r\n");								
								valueXmlString.append("<lot_sl isSrvCallOnChg='1'>").append("<![CDATA[").append(lotSl).append("]]>").append("</lot_sl>\r\n");
								
								valueXmlString.append("<site_code__mfg isSrvCallOnChg='0'>").append("<![CDATA[").append(siteCodeMfg).append("]]>").append("</site_code__mfg>\r\n");

								
								/*
								valueXmlString.append("<rate__stduom isSrvCallOnChg='0'>").append("<![CDATA[").append(rateStduomrt).append("]]>").append("</rate__stduom>\r\n");
								*/
								
								valueXmlString.append("</Detail>\r\n");

								//End Added - Gulzar - 23/01/08

								//Update statement commented by Jiten as per Piyush Sir - 04/11/06
								/*sql2 = "UPDATE SORDALLOC " 
											+"SET QTY_ALLOC	= QTY_ALLOC - ? "
											+"WHERE SALE_ORDER = ? "
											+"AND LINE_NO =	? "
											+"AND EXP_LEV =	? "
											+"AND ITEM_CODE__ORD = ? "
											+"AND ITEM_CODE	= ? "
											+"AND LOC_CODE = ? "
											+"AND LOT_NO = ? "
											+"AND LOT_SL = ? ";
								System.out.println("The Update sql :"+sql2);
								pstmt = conn.prepareStatement(sql2);							
								pstmt.setDouble(1, inputQty);
								pstmt.setString(2, sordNo);
								pstmt.setString(3, lineNoOrd);
								pstmt.setString(4, expLev);
								pstmt.setString(5, itemCodeOrd);
								pstmt.setString(6, itemCode);
								pstmt.setString(7, locCode);
								pstmt.setString(8, lotNo);
								pstmt.setString(9, lotSl);
								updateCnt = pstmt.executeUpdate();
								System.out.println("update the no of records in sordalloc :"+updateCnt);
								sql2 = "UPDATE SORDITEM "
									  +"SET QTY_ALLOC =	QTY_ALLOC -"+inputQty
									  +"WHERE SALE_ORDER = '"+sordNo+"' "
									  +"AND LINE_NO	= '"+lineNoOrd+"' "
									  +"AND EXP_LEV	= '"+expLev+"' ";

								System.out.println("The update Sql :"+sql2);	
								pstmt = conn.prepareStatement(sql2);							
								updateCnt = pstmt.executeUpdate();
								System.out.println("update the no of records in sorditem :"+updateCnt);*/
							}
						}//while end
					}//end else
					itemCodeOld = itemCodeOld+ " "+itemCode;
					System.out.println("itemCodeOld :"+itemCodeOld);
				}// while end
				//valueXmlString.append("</Root>\r\n");			
			}
			valueXmlString.append("</Root>\r\n");			
		}//try end
		catch(SQLException sqx)
		{
			System.out.println("The Exception caught from Despatch(Default) :"+sqx);
			throw new ITMException(sqx);
		}
		catch(Exception e)
		{
			System.out.println("The Exception caught from Despatch(Default) :"+e);
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				conn.close();
				conn = null;
				qtyMap = null;
			}catch(Exception e){}
		}
		System.out.println("valueXmlString.toString() :"+valueXmlString.toString());
		return valueXmlString.toString();
	}

	//End Changes - Gulzar - 23/01/08

	private String actionLotNo(Document dom,Document dom1, String objContext, String xtraParams) throws RemoteException, ITMException	
	{
		String siteCode = "", sordNo = "", sql1 = "", sql = "", errCode = "", errString = "", itemChngXmlString = "";
		String itemCode = "", quantity = "", convQqtyStduom = "", locCode = "", noArt = "", locCode1 = "";
		String lotNo = "", lotSl = "", siteCodeMfg = "", packCode = "",	dimension = "", suppCodeMfg = "";
		double quantityStk = 0d, allocQty = 0d,	grossWeight = 0d, tareWeight = 0d;
		double netPer = 0d, tarePer = 0d, netWeight = 0d, quantity1 = 0d, inputQty = 0d, grossPer = 0d;
		java.sql.Date expDate = null, mfgDate = null;
		int count = 0;
		StringBuffer valueXmlString = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root>\r\n");
		Connection conn = null;
		Statement stmt = null, stmt1 = null;
		ResultSet rs = null, rs1 = null;
		ConnDriver connDriver = new ConnDriver();
		GenericUtility genericUtility = GenericUtility.getInstance();
		ITMDBAccessEJB itmDBAccess = new ITMDBAccessEJB();
		Document domItmChng = null;
		ValidatorLocal validator = null;
			
		try
		{
			conn = connDriver.getConnectDB("DriverITM");
			stmt = conn.createStatement();
			stmt1 = conn.createStatement();
			siteCode = genericUtility.getColumnValue("site_code",dom1);
			sordNo = genericUtility.getColumnValue("sord_no",dom1);
			lotNo = genericUtility.getColumnValue("lot_no",dom);
			sql = "SELECT LINE_NO,EXP_LEV FROM SORDITEM WHERE SALE_ORDER ='"+sordNo+"' AND LINE_TYPE = 'I'";
			System.out.println("sql :"+sql);
			rs = stmt.executeQuery(sql);
			while (rs.next())
			{
				AppConnectParm appConnect = new AppConnectParm();
				Properties props = appConnect.getProperty();
				InitialContext ctx = new InitialContext(props);
				//DespatchHome despatchHome = (DespatchHome)ctx.lookup("Despatch");
				//Despatch despatchRemote = despatchHome.create();
				//ValidatorHome validatorHome = (ValidatorHome)ctx.lookup("Despatch");
				 validator = (ValidatorLocal)ctx.lookup("ibase/Despatch/local"); // for ejb3 
				//Validator validator = validatorHome.create();
				
				System.out.println("Despatch Created");

				//itemChngXmlString = validator.itemChanged(xmlString, xmlString1, "", objContext, "exp_lev", "E", xtraParams);
				itemChngXmlString = validator.itemChanged(dom, dom1, null, objContext, "exp_lev", "E", xtraParams);
				System.out.println("itemChngXmlString :"+itemChngXmlString);
				domItmChng = genericUtility.parseString(itemChngXmlString); 

				itemCode = genericUtility.getColumnValue("item_code",domItmChng);
				System.out.println("itemCode :"+itemCode);
				quantity = genericUtility.getColumnValue("quantity",domItmChng);
				System.out.println("quantity :"+quantity);
				convQqtyStduom = genericUtility.getColumnValue("conv__qty_stduom",domItmChng);
				System.out.println("convQqtyStduom :"+convQqtyStduom);
				locCode = genericUtility.getColumnValue("loc_code",dom);
				System.out.println("locCode :"+locCode);
				if (locCode == null || locCode.trim().length() == 0)
				{
					locCode = "%";
				}
				else
				{
					locCode1 = locCode;
					locCode = locCode.trim() + "%";
				}
				//sql1 = "SELECT COUNT(A.ITEM_CODE) FROM STOCK A, INVSTAT B "
				sql1 = "SELECT COUNT(*) FROM STOCK A, INVSTAT B "
							  +"WHERE A.INV_STAT  = B.INV_STAT "
							  +"AND A.ITEM_CODE = '"+itemCode+"' "
							  +"AND A.SITE_CODE = '"+siteCode+"' "
							  +"AND A.LOC_CODE  LIKE '"+locCode+"' " 
							  +"AND B.AVAILABLE = 'Y' "
							  +"AND B.USABLE = 'Y' "
							  +"AND A.QUANTITY  > 0 ";
				System.out.println("sql1 :"+sql1);
				rs1 = stmt1.executeQuery(sql1);
				if (rs1.next())
				{
					count = rs1.getInt(1);
					System.out.println("count :"+count);
				}
				if (count == 0)
				{
					//errCode = "";//Commented code by Jiten 09/10/06 - if count is zero then break from loop.
					break;
				}
				sql1 = "SELECT A.LOT_NO, A.LOT_SL, A.QUANTITY, A.EXP_DATE,A.SITE_CODE__MFG, A.MFG_DATE, "
					  +"A.ALLOC_QTY, A.PACK_CODE, A.LOC_CODE, A.GROSS_WEIGHT, A.TARE_WEIGHT, "
					  +"A.NET_WEIGHT, A.DIMENSION,A.SUPP_CODE__MFG FROM STOCK A, INVSTAT B "
					  +"WHERE A.INV_STAT  = B.INV_STAT "
					  +"AND A.ITEM_CODE = '"+itemCode+"' "
					  +"AND A.SITE_CODE = '"+siteCode+"' "
					  +"AND A.LOC_CODE  LIKE '"+locCode+"' "
					  +"AND B.AVAILABLE = 'Y' "
					  +"AND B.USABLE = 'Y' "
					  +"AND A.QUANTITY - A.ALLOC_QTY > 0 "
					  +"AND A.LOT_NO = '"+lotNo+"' "
					  +"ORDER BY A.EXP_DATE, A.LOT_NO, A.LOT_SL ";
				System.out.println("sql1 :"+sql1);
				rs1 = stmt1.executeQuery(sql1);
				if (rs1.next())
				{
					lotNo = rs1.getString(1);
					System.out.println("lotNo :"+lotNo);
					lotSl = rs1.getString(2);
					System.out.println("lotSl :"+lotSl);
					quantityStk = rs1.getDouble(3);
					System.out.println("quantity :"+quantity);
					expDate = rs1.getDate(4);
					System.out.println("expDate :"+expDate);
					siteCodeMfg = rs1.getString(5);
					System.out.println("siteCodeMfg :"+siteCodeMfg);
					mfgDate = rs1.getDate(6);
					System.out.println("mfgDate :"+mfgDate);
					allocQty = rs1.getDouble(7);
					System.out.println("allocQty :"+allocQty);
					packCode = rs1.getString(8);
					System.out.println("packCode :"+packCode);
					locCode = rs1.getString(9);
					System.out.println("locCode :"+locCode);
					grossWeight = rs1.getDouble(10);
					System.out.println("grossWeight :"+grossWeight);
					tareWeight = rs1.getDouble(11);
					System.out.println("tareWeight :"+tareWeight);
					netWeight = rs1.getDouble(12);
					System.out.println("netWeight :"+netWeight);
					dimension = rs1.getString(13);
					System.out.println("dimension :"+dimension);
					suppCodeMfg = rs1.getString(14);
					System.out.println("suppCodeMfg :"+suppCodeMfg);
					quantity1 = Double.parseDouble(quantity);
					if (quantityStk - allocQty <= quantity1)
					{
						inputQty = quantityStk - allocQty;
						System.out.println("inputQty :"+inputQty);
						quantity1 = quantity1 - inputQty;
						System.out.println("quantity1 :"+quantity1);
					}
					else
					{
						inputQty = quantity1;
						System.out.println("inputQty :"+inputQty);
						quantity1 = quantity1 - quantityStk - allocQty;
						System.out.println("quantity1 :"+quantity1);						
					}
					if (inputQty > 0)
					{
						if (quantityStk > 0)
						{
							grossPer = (grossWeight/quantityStk);
							System.out.println("grossPer  :"+grossPer);
							netPer = (netWeight/quantityStk);
							System.out.println("netPer :"+netPer);
							tarePer = (tareWeight/quantityStk);
							System.out.println("tarePer :"+tarePer);

							grossWeight = (inputQty * grossPer);
							System.out.println("grossWeight :"+grossWeight);
							netWeight = (inputQty * netPer);
							System.out.println("netWeight :"+netWeight);
							tareWeight = (inputQty * tarePer);
							System.out.println("tareWeight :"+tareWeight);
						}
						valueXmlString.append("<Detail>\r\n");
						valueXmlString.append("<loc_code>").append("<![CDATA[").append(locCode1).append("]]>").append("</loc_code>\r\n");
						valueXmlString.append("<quantity>").append("<![CDATA[").append(inputQty).append("]]>").append("</quantity>\r\n");
						valueXmlString.append("<quantity_real>").append("<![CDATA[").append(inputQty).append("]]>").append("</quantity_real>\r\n");
						valueXmlString.append("<quantity__stduom>").append("<![CDATA[").append(inputQty * Double.parseDouble(convQqtyStduom)).append("]]>").append("</quantity__stduom>\r\n");
						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");
						valueXmlString.append("<gross_weight>").append("<![CDATA[").append(grossWeight).append("]]>").append("</gross_weight>\r\n");
						valueXmlString.append("<nett_weight>").append("<![CDATA[").append(netWeight).append("]]>").append("</nett_weight>\r\n");
						valueXmlString.append("<tare_weight>").append("<![CDATA[").append(tareWeight).append("]]>").append("</tare_weight>\r\n");
						valueXmlString.append("<dimension>").append("<![CDATA[").append(dimension).append("]]>").append("</dimension>\r\n");
						valueXmlString.append("<site_code__mfg>").append("<![CDATA[").append(suppCodeMfg).append("]]>").append("</site_code__mfg>\r\n");
						valueXmlString.append("<mfg_date>").append("<![CDATA[").append(mfgDate).append("]]>").append("</mfg_date>\r\n");
						valueXmlString.append("<exp_date>").append("<![CDATA[").append(expDate).append("]]>").append("</exp_date>\r\n");
						valueXmlString.append("</Detail>\r\n");

						grossWeight = 0;
						tareWeight = 0;
						netWeight = 0;
					}//end if
				}// end if
			}// while end
			valueXmlString.append("</Root>\r\n");
		}
		catch (SQLException sqx)
		{
			System.out.println("The SQLException occurs in DespatchAct :"+sqx);
		}
		catch (Exception e)
		{
			System.out.println("The Exception occurs in DespatchAct :"+e);
		}
		System.out.println("valueXmlString :"+valueXmlString.toString());
		return valueXmlString.toString();
	}

	private String calcExpiry(String tranDate, int months)
	{
		java.util.Date expDate = new java.util.Date();
		java.util.Date retDate = new java.util.Date();
		String retStrInDate = "";
		System.out.println("tranDate :"+tranDate+"\nmonths :"+months);
		try
		{
			GenericUtility genericUtility = GenericUtility.getInstance();
			SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
			if (months > 0)
			{
				Calendar  cal = Calendar.getInstance();
				expDate = sdf.parse(tranDate);
				System.out.println("expDate :"+expDate);
				cal.setTime(expDate);
				cal.add(Calendar.MONTH,months);
				//for last day of the month
				cal.add(Calendar.MONTH,1);
				cal.set(Calendar.DATE,0);
				//sets zero to get the last day of the given date
				retDate = cal.getTime();
				retStrInDate = sdf.format(retDate);
			}
			else
			{
			   retStrInDate = tranDate;
			}
		}
		catch(Exception e)
		{
			System.out.println("The Exception occurs in calcExpiry :"+e);
		}
		System.out.println("retStrInDate :"+retStrInDate);
		return retStrInDate;
	}
	private String getCurrentUpdateFlag(Document dom)
	{
		NodeList detailList = null;
		Node currDetail = null;
		NodeList currDetailList = null;
		String updateStatus = "",nodeName = "";
		int currDetailListLength = 0;
		int	detailListLength = 0;

		detailList = dom.getElementsByTagName("Detail2");
		detailListLength = detailList.getLength();
		for (int ctr = 0;ctr < detailListLength;ctr++)
		{
			currDetail = detailList.item(ctr);
		}
		currDetailList = currDetail.getChildNodes();
		currDetailListLength = currDetailList.getLength();
		for (int i=0;i< currDetailListLength;i++)
		{
			nodeName = currDetailList.item(i).getNodeName();
			if (nodeName.equalsIgnoreCase("Attribute"))
			{
				updateStatus = currDetailList.item(i).getAttributes().getNamedItem("updateFlag").getNodeValue();
				break;
			}
		}
		System.out.println("updateStatus in [getCurrentUpdateFlag()] :: " + updateStatus);
		return updateStatus;
	}

	private String actionWoGetPackList(Document dom, Document dom1, String objContext, String xtraParams) throws RemoteException, ITMException
	{
		ResultSet rs = null;
		Connection conn = null;
		Statement stmt = null;
		
		StringBuffer valueXmlString = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		ConnDriver connDriver = new ConnDriver();
		ITMDBAccessEJB itmDBAccess = new ITMDBAccessEJB();
		String sql=null;
		boolean isFound=false;
		String errString="";
		String errCode="";
        String saleOrder = "";
        ArrayList itemCodeList = new ArrayList();
        String itemCodeStr = "";
        HashMap itemMap = new HashMap();
		
		try
		{
			conn = connDriver.getConnectDB("DriverITM");
			stmt = conn.createStatement();
            
            saleOrder = genericUtility.getColumnValue("sord_no",dom);
            
            sql = "select sorddet.line_no,sorddet.item_code,"+
                "sorddet.quantity,case when despatchdet.quantity is null " +
                "then 0 else despatchdet.quantity end from sorder,sorddet left outer join despatchdet "+ 
                "on sorddet.sale_order = despatchdet.sord_no and sorddet.line_no = despatchdet.line_no__sord "+ 
                "where sorder.sale_order = sorddet.sale_order "+
                "and sorder.confirmed = 'Y' and sorddet.status <> 'C' and " +
                "case when sorddet.quantity is null then 0 else sorddet.quantity end - " +
                "case when despatchdet.quantity is null then 0 else despatchdet.quantity end > 0 "+ 
                "and sorder.sale_order = '"+saleOrder+"'";
            
            System.out.println("SQL :"+sql);
            rs = stmt.executeQuery(sql);
            while(rs.next()){
                itemCodeList.add(rs.getString(2));
                itemMap.put(rs.getString(2),rs.getString(1));
            }
            rs.close();
            for(int i = 0;i < itemCodeList.size(); i++){
                itemCodeStr = itemCodeStr+"'"+itemCodeList.get(i)+"',";
            }
            
            if(itemCodeStr.trim().length() > 0){
                itemCodeStr = itemCodeStr.substring(0,itemCodeStr.length()-1);
                System.out.println("itemCodeStr "+itemCodeStr);
            }else{
                itemCodeStr = "''";
            }
            
            
            sql="SELECT INV_PACK_RCP.TRAN_ID,INV_PACK_RCP.LINE_NO,"
			+" INV_PACK_RCP.ORDER_NO,INV_PACK_RCP.LINE_NO__ORD,INV_PACK_RCP.ITEM_CODE,"
			+" INV_PACK_RCP.LOC_CODE,INV_PACK_RCP.LOT_NO,INV_PACK_RCP.LOT_SL,"
			+" STOCK.QUANTITY,INV_PACK_RCP.UNIT,"
			+" INV_PACK_RCP.PACK_CODE,INV_PACK_RCP.PACK_INSTR,"
			+" INV_PACK_RCP.GROSS_WEIGHT,INV_PACK_RCP.TARE_WEIGHT,"
			+" INV_PACK_RCP.NET_WEIGHT,INV_PACK_RCP.NO_ART,INV_PACK_RCP.DIMENSION,"
			+" 'Y' AS PROCESS_YN "
			+" FROM INV_PACK, INV_PACK_RCP, STOCK "
			+" WHERE INV_PACK.TRAN_ID = INV_PACK_RCP.TRAN_ID "
            +" AND INV_PACK_RCP.ITEM_CODE IN ("+itemCodeStr+") "
			+" AND INV_PACK_RCP.ITEM_CODE = STOCK.ITEM_CODE "
            +" AND INV_PACK.SITE_CODE = STOCK.SITE_CODE "
			+" AND INV_PACK_RCP.LOC_CODE = STOCK.LOC_CODE "
			+" AND INV_PACK_RCP.LOT_NO = STOCK.LOT_NO "
			+" AND INV_PACK_RCP.LOT_SL = STOCK.LOT_SL "
			+" AND INV_PACK.CONFIRMED  = 'Y' "
			+" AND INV_PACK.ORDER_TYPE = 'W'"
            +" AND (STOCK.QUANTITY - STOCK.ALLOC_QTY) > 0 "                    
			+" AND STOCK.PACK_REF IS NOT NULL "
			+" ORDER BY INV_PACK_RCP.LINE_NO ASC";

            System.out.println("SQL "+sql);
            
			rs = stmt.executeQuery(sql);
			valueXmlString=new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root>\r\n");
			while (rs.next())
			{				
				valueXmlString.append("<Detail>\r\n");
				valueXmlString.append("<tran_id>").append("<![CDATA[").append(blanknull(rs.getString(1))).append("]]>").append("</tran_id>\r\n");
				valueXmlString.append("<order_no>").append("<![CDATA[").append(blanknull(saleOrder)).append("]]>").append("</order_no>\r\n");
                valueXmlString.append("<line_no__sord>").append("<![CDATA[").append(itemMap.get(rs.getString(5)).toString()).append("]]>").append("</line_no__sord>\r\n");
				valueXmlString.append("<item_code>").append("<![CDATA[").append(blanknull(rs.getString(5))).append("]]>").append("</item_code>\r\n");
				valueXmlString.append("<loc_code>").append("<![CDATA[").append(blanknull(rs.getString(6))).append("]]>").append("</loc_code>\r\n");
				valueXmlString.append("<lot_no>").append("<![CDATA[").append(blanknull(rs.getString(7))).append("]]>").append("</lot_no>\r\n");
				valueXmlString.append("<lot_sl>").append("<![CDATA[").append(blanknull(rs.getString(8))).append("]]>").append("</lot_sl>\r\n");
				valueXmlString.append("<quantity>").append("<![CDATA[").append(rs.getDouble(9)).append("]]>").append("</quantity>\r\n");
				valueXmlString.append("<unit>").append("<![CDATA[").append(blanknull(rs.getString(10))).append("]]>").append("</unit>\r\n");
				valueXmlString.append("<pack_code>").append("<![CDATA[").append(blanknull(rs.getString(11))).append("]]>").append("</pack_code>\r\n");
				valueXmlString.append("<pack_instr>").append("<![CDATA[").append(blanknull(rs.getString(12))).append("]]>").append("</pack_instr>\r\n");
				valueXmlString.append("<gross_weight>").append("<![CDATA[").append(rs.getDouble(13)).append("]]>").append("</gross_weight>\r\n");
				valueXmlString.append("<tare_weight>").append("<![CDATA[").append(rs.getDouble(14)).append("]]>").append("</tare_weight>\r\n");	
				valueXmlString.append("<net_weight>").append("<![CDATA[").append(rs.getDouble(15)).append("]]>").append("</net_weight>\r\n");
				valueXmlString.append("<no_art>").append("<![CDATA[").append(rs.getString(16)).append("]]>").append("</no_art>\r\n");
				valueXmlString.append("<dimensions>").append("<![CDATA[").append(blanknull(rs.getString(17))).append("]]>").append("</dimensions>\r\n");

				valueXmlString.append("</Detail>\r\n");
				isFound=true;
				System.out.println("[DespatchActforWo]SQL :"+sql);
			}//end of while
			valueXmlString.append("</Root>\r\n");		
			rs.close();
			rs=null;
			stmt.close();
			stmt = null;
			System.out.println("[DespatchActforWo]valueXmlString=>"+valueXmlString.toString());		
			System.out.println("[DespatchActforWo]isFound=>"+isFound);	
			if(!isFound)
			{
				errCode="VTPORD9";
				errString = itmDBAccess.getErrorString("",errCode,"","",conn);
				return errString;
			}
		}//try end
		catch(Exception e)
		{
			System.out.println("Exception :Despatch :" + e.getMessage() + ":");
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if(rs!=null)
				{
					rs.close();
					rs=null;
				}
				if(stmt!=null)
				{
					stmt.close();
					stmt=null;
				}
				System.out.println("Closing Connection.....");
				conn.close();
			}catch(Exception e){}
		}
		System.out.println("valueXmlString.toString() "+valueXmlString.toString());
		return valueXmlString.toString();		
	}	

	private String getWoPackListTransform(Document dom,Document dom1, String objContext, String xtraParams, Document selDataDom)throws ITMException
	{
		StringBuffer valueXmlString = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root>\r\n");
		String orderNo ="";
		String quantity ="0.0";
		String itemCode="";
		String itemDescr="";
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		Connection conn=null;
		String sql=null;
        String lineNo = "";
        String locCode = "",lotNo = "",lotSl = "";

		try
		{
			NodeList detailList = selDataDom.getElementsByTagName("Detail");
			int noOfDetails = detailList.getLength();
			ConnDriver connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");

			for(int ctr = 0; ctr < noOfDetails; ctr++)
			{
				valueXmlString.append("<Detail>");
				Node currDetail = detailList.item(ctr);
				
				orderNo = GenericUtility.getInstance().getColumnValueFromNode("order_no", currDetail);
				quantity = GenericUtility.getInstance().getColumnValueFromNode("quantity", currDetail);
				itemCode = GenericUtility.getInstance().getColumnValueFromNode("item_code", currDetail);
                lineNo = GenericUtility.getInstance().getColumnValueFromNode("line_no__sord", currDetail);
                locCode = GenericUtility.getInstance().getColumnValueFromNode("loc_code", currDetail);
                lotNo = GenericUtility.getInstance().getColumnValueFromNode("lot_no", currDetail);
                lotSl = GenericUtility.getInstance().getColumnValueFromNode("lot_sl", currDetail);
                
				sql="select descr from item where item_code='"+itemCode+"'";
			
				pstmt=conn.prepareStatement(sql);
				rs=pstmt.executeQuery();
				if(rs.next())
				{
					itemDescr=rs.getString(1);
				}
				rs.close();
				rs=null;
				pstmt.close();
				pstmt=null;
				
				System.out.println("sql in getPackListTransform :: "+sql);
				System.out.println("orderNo in getPackListTransform :: " + orderNo);
				System.out.println("quantity In getPackListTransform :: " + quantity);
				System.out.println("itemCode In getPackListTransform :: " + itemCode);
					
				valueXmlString.append("<sord_no isSrvCallOnChg='1'>").append(orderNo).append("</sord_no>");
                valueXmlString.append("<line_no__sord isSrvCallOnChg='1'><![CDATA[").append(lineNo).append("]]></line_no__sord>\r\n");
				valueXmlString.append("<item_code isSrvCallOnChg='1'><![CDATA[").append(itemCode).append("]]></item_code>");
				valueXmlString.append("<item_descr isSrvCallOnChg='0'>").append(itemDescr).append("</item_descr>");				
				valueXmlString.append("<quantity isSrvCallOnChg='1'>").append(quantity).append("</quantity>");
                valueXmlString.append("<loc_code>").append("<![CDATA[").append(locCode).append("]]>").append("</loc_code>\r\n");
                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");
				valueXmlString.append("</Detail>");								
			}
			valueXmlString.append("</Root>");
		}
		catch(ITMException itme)
		{
			throw itme;
		}
		catch(Exception e)
		{
			throw new ITMException(e);
		}
		finally
		{
			try
			{				
				if(conn!=null)
					conn.close();	
			}
			catch (Exception e)
			{
				throw new ITMException(e);
			}
		}
		return valueXmlString.toString();
	}
	
	public String blanknull(String s)
	{
		if(s==null)
			return "";
		else
			return s.trim();
	}

	private String actionPacking(Document dom,Document dom1, String objContext, String xtraParams) throws RemoteException , ITMException
	{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "";
		String tranIdInvpack = "", siteCode = "";
		StringBuffer valueXmlString = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root>\r\n");
		ConnDriver connDriver = new ConnDriver();
		GenericUtility genericUtility = GenericUtility.getInstance();
		try
		{	
		   	conn = connDriver.getConnectDB("DriverITM");
			siteCode = genericUtility.getColumnValue("site_code",dom1);

			sql = " SELECT DISTINCT B.TRAN_ID FROM INV_PACK A, INV_PACK_RCP B, STOCK C "
				+ " WHERE A.TRAN_ID = B.TRAN_ID AND A.CONFIRMED = 'Y' AND A.ORDER_TYPE = 'N' "
				+ " AND A.SITE_CODE = '" + siteCode + "'AND (A.STATUS IS NULL OR A.STATUS <> 'X') "
				+ " AND B.ITEM_CODE = C.ITEM_CODE AND A.SITE_CODE = C.SITE_CODE "
				+ " AND B.LOC_CODE = C.LOC_CODE AND B.LOT_NO=C.LOT_NO AND B.LOT_SL=C.LOT_SL "
				+ " AND C.QUANTITY-C.ALLOC_QTY >0 AND C.PACK_REF IS NOT NULL";
			System.out.println("sql for popup:::" + sql);
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next())
			{
				tranIdInvpack = rs.getString("TRAN_ID");
//				orderType = rs.getString("ORDER_TYPE");
//				orderNo = rs.getString("ORDER_NO");
				valueXmlString.append("<Detail>");
				valueXmlString.append("<inv_pack_id>").append("<![CDATA[").append(tranIdInvpack).append("]]>").append("</inv_pack_id>\r\n");
				valueXmlString.append("<site_code>").append("<![CDATA[").append(siteCode).append("]]>").append("</site_code>\r\n");
				valueXmlString.append("</Detail>");
			}//end While loop
   			valueXmlString.append("</Root>\r\n");
		}
		catch(Exception e)
		{
			System.out.println("Exception :Despatch :" + e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				System.out.println("Closing Connection.....");
				conn.close();
			}catch(Exception e){}
		}
		System.out.println("Despatch:actionStock:Final Value :valueXmlString.toString():"+valueXmlString.toString());
		return valueXmlString.toString();
	}

	private String actionPackingTransform(Document dom, Document dom1, String objContext, String xtraParams, Document selDataDom) throws ITMException
	{
		Connection conn = null;
		PreparedStatement pstmt = null, pstmtData = null, pstmtUpdate = null;
		ResultSet rs = null, rsData = null;
		NodeList detailList = null;
		String sql = "", errString = "", newMessage = ""; 
		String tranIdInvpack = "", custCode = "", itemCode = "", siteCode = "", locCode = "", lotSl = "", lotNo = "";
		String saleOrder = "", lineNoSord = "", expLevel = "", detailCnt = "";
		double invPackQty = 0, pendQty = 0, despQty = 0, qtyAlloc = 0, availQty = 0, stkAllocQty = 0;
		int detCnt = 0;
		StringBuffer valueXmlString = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root>\r\n");
		GenericUtility genericUtility = GenericUtility.getInstance();
		ITMDBAccessEJB itmDBAccess = new ITMDBAccessEJB();
		ConnDriver connDriver = new ConnDriver();
		try
		{
			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();
			}
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			conn.setAutoCommit(false);
			custCode = genericUtility.getColumnValue("cust_code",dom1);
			siteCode = genericUtility.getColumnValue("site_code",dom1);
			detailList = selDataDom.getElementsByTagName("Detail");
			int noOfDetails = detailList.getLength();
//			for(int ctr = 0; ctr < noOfDetails; ctr++)
//			{
//				Node currDetail = detailList.item(ctr);
//				tranIdInvpack = genericUtility.getColumnValueFromNode("inv_pack_id", currDetail);
//				tranIdInvpackStr = tranIdInvpackStr +"'"+tranIdInvpack+"',";
//            }
			
			for(int ctr = 0; ctr < noOfDetails; ctr++)
			{
				Node currDetail = detailList.item(ctr);
				tranIdInvpack = genericUtility.getColumnValueFromNode("inv_pack_id", currDetail);
				System.out.println("tranIdInvpack In packingTransform :: " + tranIdInvpack + ":");
				
//				sql = " SELECT A.QUANTITY - A.ALLOC_QTY, A.ITEM_CODE, A.LOT_NO, A.LOT_SL, A.LOC_CODE "
//					+ " FROM STOCK A WHERE (A.ITEM_CODE,A.SITE_CODE,A.LOC_CODE,A.LOT_NO,A.LOT_SL) IN "
//					+ " (SELECT DISTINCT C.ITEM_CODE,C.SITE_CODE,C.LOC_CODE,C.LOT_NO,C.LOT_SL "
//					+ " FROM INVTRACE C WHERE C.REF_SER = 'I-PKR' AND "
//					+ " C.REF_ID IN ("+ tranIdInvpackStr +") "
//					+ " AND A.QUANTITY - A.ALLOC_QTY > 0 AND (A.PACK_REF IS NULL  OR LENGTH(TRIM(A.PACK_REF)) = 0 ) ";
				sql = " SELECT B.ITEM_CODE, B.QUANTITY, B.LOC_CODE, B.LOT_NO, B.LOT_SL "
					+ " FROM INV_PACK A, INV_PACK_RCP B, ITEM C "
					+ " WHERE A.TRAN_ID = B.TRAN_ID AND B.ITEM_CODE = C.ITEM_CODE AND A.CONFIRMED = 'Y' "
					+ " AND A.ORDER_TYPE = 'N' AND A.TRAN_ID = ? AND A.SITE_CODE = ?";
				System.out.println("sql :"+sql);
				pstmtData = conn.prepareStatement(sql);
				pstmtData.setString(1, tranIdInvpack);
				pstmtData.setString(2, siteCode);
				rsData = pstmtData.executeQuery();
				while (rsData.next())
				{
					itemCode = rsData.getString("ITEM_CODE");
					invPackQty = rsData.getDouble("QUANTITY");
					locCode = rsData.getString("LOC_CODE");
					lotNo = rsData.getString("LOT_NO");
					lotSl = rsData.getString("LOT_SL");
					
					sql = " SELECT QUANTITY, ALLOC_QTY FROM STOCK WHERE SITE_CODE = ? AND ITEM_CODE = ? AND  LOC_CODE = ?"
						+ " AND LOT_NO = ? AND LOT_SL = ? AND PACK_REF IS NOT NULL";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, siteCode);
					pstmt.setString(2, itemCode);
					pstmt.setString(3, locCode);
					pstmt.setString(4, lotNo);
					pstmt.setString(5, lotSl);
					System.out.println("sql in retrieving Stock Details :: "+sql);
					System.out.println("Setting Params :: siteCode ==> :" + siteCode + ": itemCode ==> :" + itemCode + ":");
					System.out.println("Setting Params :: locCode ==> :" + locCode + ": lotNo ==> :" + lotNo + ":");
					System.out.println("Setting Params :: lotSl ==> :" + lotSl + ":");
					rs = pstmt.executeQuery();
					if (rs.next())
					{
						availQty = rs.getDouble(1);
						stkAllocQty = rs.getDouble(2);
					}
					rs.close(); rs = null;
					pstmt.close(); pstmt = null;
					System.out.println("STOCK ========== availQty :" + availQty + ":: stkAllocQty :" + stkAllocQty + "::invPackQty ==> :" + invPackQty + ":");
					if ((availQty-stkAllocQty) < invPackQty)
					{
						errString = itmDBAccess.getErrorString("", "VTSTKINS", "", "", conn);
						newMessage = "STOCK FOR ITEM CODE ::: " + itemCode + " NOT SUFFICIENT IN SITE ::: " + siteCode;
						errString = updateMessage(errString, newMessage);
						return errString;
					}
					
					sql = " SELECT A.SALE_ORDER, A.LINE_NO, A.QTY_ORD-A.QTY_DESP-A.QTY_ALLOC, A.EXP_LEV, "
						+ " A.QTY_ALLOC FROM SORDITEM A, SORDER B "
						+ " WHERE A.SALE_ORDER = B.SALE_ORDER AND B.STATUS = 'P' "
						+ " AND A.QTY_ORD-A.QTY_DESP-A.QTY_ALLOC > 0 AND A.ITEM_CODE__ORD = ? "
						+ " AND B.CUST_CODE = ? AND A.SITE_CODE = ? "
						+ " ORDER BY B.DUE_DATE, A.SALE_ORDER, A.LINE_NO ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, itemCode);
					pstmt.setString(2, custCode);
					pstmt.setString(3, siteCode);
					System.out.println("sql in retrieving Sorder Details :: "+sql);
					System.out.println("Setting Params :: itemCode ==> :" + itemCode + ": custCode ==> :" + custCode + ":");
					System.out.println("Setting Params :: siteCode ==> :" + siteCode + ":");
					rs = pstmt.executeQuery();
					while (rs.next() && invPackQty > 0)
					{
						saleOrder = rs.getString(1);
						lineNoSord = rs.getString(2);
						pendQty = rs.getDouble(3);
						expLevel = rs.getString(4);
						qtyAlloc = rs.getDouble(5);
						System.out.println("invPackQty ==> :" + invPackQty + ": qtyAlloc ==> :" + qtyAlloc + ": pendQty ==> :" + pendQty + ":");
						System.out.println(" saleOrder ==> :" + saleOrder + ": lineNoSord ==> :" + lineNoSord + ":");
						
						if (invPackQty > pendQty)
						{
							despQty = pendQty;
							invPackQty = invPackQty - despQty;
						}
						else if (invPackQty <= pendQty)
						{
							despQty = invPackQty;
							invPackQty = 0;
						}
						valueXmlString.append("<Detail>");
						valueXmlString.append("<sord_no isSrvCallOnChg='0'>").append("<![CDATA[").append(saleOrder).append("]]>").append("</sord_no>");
						valueXmlString.append("<line_no__sord isSrvCallOnChg='1'>").append("<![CDATA[").append(lineNoSord).append("]]>").append("</line_no__sord>");
						valueXmlString.append("<exp_lev isSrvCallOnChg='0'>").append("<![CDATA[").append(expLevel).append("]]>").append("</exp_lev>");
						valueXmlString.append("<loc_code>").append("<![CDATA[").append(locCode).append("]]>").append("</loc_code>");
						valueXmlString.append("<lot_no>").append("<![CDATA[").append(lotNo).append("]]>").append("</lot_no>");
						valueXmlString.append("<lot_sl isSrvCallOnChg='1'>").append("<![CDATA[").append(lotSl).append("]]>").append("</lot_sl>");
						valueXmlString.append("<quantity protect = \"1\" isSrvCallOnChg='1'>").append("<![CDATA[").append(despQty).append("]]>").append("</quantity>");
						valueXmlString.append("<tran_id__invpack isSrvCallOnChg='0'>").append("<![CDATA[").append(tranIdInvpack).append("]]>").append("</tran_id__invpack>");
						valueXmlString.append("</Detail>");
						
						sql = " UPDATE SORDITEM SET QTY_ALLOC = ? WHERE SALE_ORDER = ? AND LINE_NO = ? "
							+ " AND EXP_LEV = ? ";
						pstmtUpdate = conn.prepareStatement(sql);
						System.out.println("qtyAlloc + despQty :: " + (qtyAlloc + despQty) + " for saleorder ::" + saleOrder + ": lineNo ::" + lineNoSord + ":");
						pstmtUpdate.setDouble(1, qtyAlloc + despQty);
						pstmtUpdate.setString(2, saleOrder);
						pstmtUpdate.setString(3, lineNoSord);
						pstmtUpdate.setString(4, expLevel);
						int update = pstmtUpdate.executeUpdate();
						System.out.println("Records updated in Sorditem for qty alloc :: "+update);
						
						pstmtUpdate.close(); pstmtUpdate = null;
						
						sql = " UPDATE STOCK SET ALLOC_QTY = ? WHERE SITE_CODE = ? AND ITEM_CODE = ? AND  LOC_CODE = ?"
							+ " AND LOT_NO = ? AND LOT_SL = ? ";
						pstmtUpdate = conn.prepareStatement(sql);
						pstmtUpdate.setDouble(1, stkAllocQty + despQty);
						pstmtUpdate.setString(2, siteCode);
						pstmtUpdate.setString(3, itemCode);
						pstmtUpdate.setString(4, locCode);
						pstmtUpdate.setString(5, lotNo);
						pstmtUpdate.setString(6, lotSl);
						update = pstmtUpdate.executeUpdate();
						System.out.println("Records updated in Stock for alloc_qty :: "+update);
					}
					rs.close(); rs=null;
					pstmt.close(); pstmt=null;
				}//END WHILE
				rsData.close(); rsData = null;
				pstmtData.close(); pstmtData = null;
				
			}
			valueXmlString.append("</Root>");
		}
		catch(ITMException itme)
		{
			throw itme;
		}
		catch(Exception e)
		{
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if(rs!=null)
				{
					rs.close(); rs = null;
				}
				if(pstmt!=null)
				{	
					pstmt.close(); pstmt = null;
				}
				if (rsData != null)
				{
					rsData.close(); rsData = null;
				}
				if (pstmtData != null)
				{
					pstmtData.close(); pstmtData = null;
				}
				if (pstmtUpdate != null)
				{
					pstmtUpdate.close(); pstmtUpdate = null;
				}
				if (conn != null)
				{
					conn.rollback();
					conn.close(); conn = null;
				}
			}
			catch(Exception e)
			{
				errString = e.getMessage();
				return errString;
			}
		}
		System.out.println("valueXmlString from CaseNoTransform :::" + valueXmlString.toString());
		return valueXmlString.toString();
	}
	
	private String updateMessage(String resultString,String message)
    {
        StringBuffer stbf = new StringBuffer();
        try{
            System.out.println("resultString : "+resultString);
            stbf.append(resultString.substring(0,resultString.indexOf("<trace>")));
            if(message != null && message.trim().length() > 0){
                stbf.append("<trace>"+message+"</trace>");
                stbf.append(resultString.substring(resultString.indexOf("</trace>")+8));
            }else{
                stbf.append(resultString.substring(resultString.indexOf("<trace>")));
            }
            System.out.println("Resulting String : "+stbf.toString());
            
        }catch(Exception e){
            System.out.println("Exception in updateMessage : "+e);
            e.printStackTrace();
        }
     return stbf.toString();   
    }
	//Added By Gulzar - 23/01/08
	private double[] getNoArt(String siteCode , String custCode , String itemCode , String packCode , double qty , String qtyType , double shipperQty , double integralQty, Connection conn)
	{
		String tempSql = "";
		String tempSql1 = "";
		String tempSql2 = "";
		ResultSet rsTemp = null;
		ResultSet rsTemp1 = null;
		ResultSet rsTemp2 = null;

		Statement stmt = null;
		double capacity = 0d;
		double reoQty = 0d;
		double shipperQty1 = 0d;
		double remainder = 0d;
		double integralQty1 = 0d;
		double result[] = new double[3];
		double noArt1 = 0d, noArt2 = 0d, noArt = 0d;
		try
		{
			stmt = conn.createStatement();
			if (qtyType.equals("S"))
			{
				tempSql = "SELECT (CASE WHEN CAPACITY IS NULL THEN 0 ELSE CAPACITY END) AS CAPACITY FROM PACKING WHERE PACK_CODE = '" + packCode + "'";
				System.out.println("SQL :: "+tempSql);
				rsTemp = stmt.executeQuery(tempSql);
				if (rsTemp.next())
				{
					capacity = rsTemp.getDouble("CAPACITY");
				}
				else
				{
					capacity = 0d;
				}
				tempSql = "";
				rsTemp.close();

				tempSql = "SELECT  REO_QTY " + 
							" FROM SITEITEM WHERE SITE_CODE = '" + siteCode + "' " +  //by jaimin 
							" AND ITEM_CODE = '" + itemCode + "' ";
				System.out.println("SQL :: "+tempSql);
				rsTemp = stmt.executeQuery(tempSql);
				if (rsTemp.next())
				{
					reoQty = rsTemp.getDouble("REO_QTY");
				}
				else
				{
					tempSql1 = "SELECT REO_QTY FROM ITEM  WHERE ITEM_CODE = '"+ itemCode +"' ";
					System.out.println("SQL :: "+tempSql1);
					rsTemp1 = stmt.executeQuery(tempSql1);
					if (rsTemp1.next())
					{
						reoQty = rsTemp1.getDouble("REO_QTY");
					}
					rsTemp1.close();
					tempSql1 = "";
				}
				tempSql = "";
				rsTemp.close();

				if ( capacity > 0 )
				{
					shipperQty1 = capacity;
				}
				else
				{
					shipperQty1 = reoQty;
				}
				if ( shipperQty1 > 0 )
				{
					noArt = (qty - (qty % shipperQty1)) / shipperQty1;
				}
			}		
			else if ( qtyType.equals("I") )
			{
				tempSql = "SELECT INTEGRAL_QTY FROM CUSTOMERITEM WHERE CUST_CODE = '"+ custCode +"' "
							 +" AND ITEM_CODE = '"+ itemCode +"' ";
				System.out.println("SQL :: "+tempSql);
				rsTemp = stmt.executeQuery(tempSql);
				if (rsTemp.next())
				{
					integralQty1 = rsTemp.getDouble("INTEGRAL_QTY");
				}
				else
				{
					tempSql1 = "SELECT INTEGRAL_QTY FROM SITEITEM WHERE SITE_CODE = '"+ siteCode +"' AND "
									+" ITEM_CODE = '"+ itemCode +"' ";
					System.out.println("SQL :: "+tempSql1);
					rsTemp1 = stmt.executeQuery(tempSql1);
					if (rsTemp1.next())
					{
						integralQty1 = rsTemp1.getDouble("INTEGRAL_QTY");
					}
					else
					{
						tempSql2 = "SELECT INTEGRAL_QTY FROM ITEM WHERE ITEM_CODE = '"+ itemCode +"' ";
						System.out.println("SQL :: "+tempSql2);
						rsTemp2 = stmt.executeQuery(tempSql2);
						if ( rsTemp2.next() )
						{
							integralQty1 = rsTemp2.getDouble("INTEGRAL_QTY");
						}
						rsTemp2.close();
						tempSql2 = "";
					}
					rsTemp1.close();
					tempSql1 = "";
				}
				tempSql = "";
				rsTemp.close();
				
				if ( integralQty1 > 0 )
				{
					noArt = (qty - (qty % integralQty1)) / integralQty1;
				}
			}
			else if ( qtyType.equals("B") )
			{
				tempSql = "SELECT (CASE WHEN CAPACITY IS NULL THEN 0 ELSE CAPACITY END) AS CAPACITY FROM PACKING WHERE PACK_CODE = '"+ packCode +"'";
				System.out.println("SQL :: "+tempSql);
				rsTemp = stmt.executeQuery(tempSql);
				if (rsTemp.next())
				{
					capacity = rsTemp.getDouble("CAPACITY");
				}
				else
				{
					capacity = 0d;
				}
				tempSql = "";
				rsTemp.close();

				tempSql = "SELECT REO_QTY "
							 +" FROM SITEITEM "
							 +" WHERE SITE_CODE = '"+ siteCode +"' " //jaimin
							 +" AND ITEM_CODE = '"+ itemCode +"' ";
				System.out.println("SQL :: "+tempSql);
				rsTemp = stmt.executeQuery(tempSql);
				if (rsTemp.next())
				{
					reoQty = rsTemp.getDouble("REO_QTY");
				}
				else
				{
					tempSql1 = "SELECT REO_QTY FROM ITEM WHERE ITEM_CODE = '"+ itemCode +"' ";
					System.out.println("SQL :: "+tempSql1);
					rsTemp1 =stmt.executeQuery(tempSql1);
					if (rsTemp1.next())
					{
						reoQty = rsTemp1.getDouble("REO_QTY");
					}
					rsTemp1.close();
					tempSql1 = "";
				}
				tempSql = "";
				rsTemp.close();


				if ( capacity > 0 )
				{
					shipperQty1 = capacity;
				}
				else
				{
					shipperQty1 = reoQty;
				}
				if ( shipperQty1 > 0 )
				{
					noArt1 = (qty - (qty % shipperQty1)) / shipperQty1;
					remainder = ( qty % shipperQty1 );
				}

				tempSql = "SELECT INTEGRAL_QTY FROM CUSTOMERITEM WHERE CUST_CODE = '"+ custCode +"' "
							+" AND ITEM_CODE = '"+ itemCode +"' ";
				System.out.println("SQL :: "+tempSql);
				rsTemp = stmt.executeQuery(tempSql);
				if (rsTemp.next())
				{
					integralQty1 = rsTemp.getDouble("INTEGRAL_QTY");
				}
				else
				{
					tempSql1 =  "SELECT INTEGRAL_QTY "
						+"  FROM SITEITEM WHERE SITE_CODE = '"+ siteCode +"' "
						+" 	AND ITEM_CODE = '"+ itemCode +"' ";
					System.out.println("SQL :: "+tempSql1);
					rsTemp1 = stmt.executeQuery(tempSql1);
					if ( rsTemp1.next() )
					{
						integralQty1 = rsTemp1.getDouble("INTEGRAL_QTY");
					}
					else
					{
						tempSql2 = "SELECT INTEGRAL_QTY FROM ITEM WHERE ITEM_CODE = '"+ itemCode +"' ";
						System.out.println("SQL :: "+tempSql2);
						rsTemp2 = stmt.executeQuery(tempSql1);
						if ( rsTemp2.next() )
						{
							integralQty1 = rsTemp2.getDouble("INTEGRAL_QTY");
						}
						tempSql2 = "";
						rsTemp2.close();
					}
					tempSql1 = "";
					rsTemp1.close();
				}
				if ( integralQty1 > 0 )
				{
					noArt2 = (remainder - (remainder % integralQty1)) / integralQty1;
				}
				if ( noArt2 > 0 )
				{
					noArt2 = 1;
				}
				noArt = noArt1 + noArt2;
			}
			else
			{
				noArt = 0;
			}			
		}
		catch (Exception e)
		{
			System.out.println("Exception in getNoArt :: "+e.getMessage());
			e.printStackTrace();
		}
		result[0] = noArt;
		result[1] = shipperQty1;
		result[2] = integralQty1;
		return result;
	}
	// End addition - Gulzar - 23/01/08
}