package ibase.webitm.ejb.mfg;

import java.rmi.RemoteException;
import java.util.*;
import java.text.*;
import java.sql.*;

import javax.ejb.*;
import org.w3c.dom.*;

import ibase.webitm.ejb.*;
import ibase.webitm.ejb.dis.*;
import ibase.webitm.reports.utility.XSDParser;
import ibase.webitm.utility.*;
import ibase.utility.CommonConstants;
import ibase.webitm.utility.ITMException;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.ejb.mfg.MfgCommon;
import ibase.webitm.ejb.mfg.adv.WOIScanConf;
import ibase.system.config.AppConnectParm;
//import ibase.webitm.ejb.MasterStateful;
//import ibase.webitm.ejb.MasterStatefulHome;
import ibase.webitm.ejb.fin.FinCommon;
import javax.naming.InitialContext;
import javax.ejb.Stateless;// added for ejb3

@Stateless
public class WOIssueDetScanPos extends ValidatorEJB implements WOIssueDetScanPosRemote,WOIssueDetScanPosLocal
{
	GenericUtility genericUtility = null;
	String curTranId = ""; // 15/01/11 manoharan
	GenWOIssue genWOIssue =null;  // 15/01/11 manoharan
	/*public void ejbCreate() throws RemoteException, CreateException 
	{
	}
	public void ejbRemove()
	{
	}
	public void ejbActivate() 
	{
	}
	public void ejbPassivate() 
	{
	}*/
	public String postSave() throws RemoteException,ITMException
	{
		return "";
	}

	public String postSaveRec(String xmlString1,String domId,String objContext,String editFlag, String xtraParams, String windowName, Connection conn)throws RemoteException,ITMException
	{
		System.out.println("xmlString1:::::["+xmlString1+"]");
		System.out.println("domId:::::["+domId+"]");
		System.out.println("objContext:::::["+objContext+"]");
		System.out.println("xtraParams:::::["+xtraParams+"]");
		System.out.println("editFlag:::::["+editFlag+"]");
		System.out.println("windowName:::::["+windowName+"]");	
		Document dom = null;
		Node parentNode = null;
		NodeList hdrDom = null;
		try
		{
			if (xmlString1 != null && xmlString1.trim().length() > 0)
			{
				dom = GenericUtility.getInstance().parseString(xmlString1);
				//start added by chandrashekar on 12-12-2014
				hdrDom = dom.getElementsByTagName("Detail1");
				parentNode = hdrDom.item(0);
				String objName = getObjName(parentNode);
				System.out.println("objName=["+objName+"]windowName["+windowName+"]");
				//if( (xmlString1.toUpperCase()).contains("DETAIL2") ||  (xmlString1.toUpperCase()).contains("DETAIL3") )
				// 23/11/14 this will not work for RCP will work only for device interface
				if( "w_woi_scan".equals(objName))
				{
					executePostSaveRec(dom,domId,objContext,editFlag,xtraParams, conn);
				}
				else if("w_disp_item_wiz".equalsIgnoreCase(objName) )
				{
					if((xmlString1.toUpperCase()).contains("DETAIL3") )
					{
						executePostSaveRec(dom,domId,objContext,editFlag,xtraParams, conn);
					}
				}
				// end 23/11/14 this will not work for RCP will work only for device interface

			}		
		}
		catch(Exception e)
		{
			System.out.println("Exception :WOIssueDetScanPosEJB ::" + e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		return "";
	}
	private String executePostSaveRec(Document dom, String domID,String objContext,String editFlag, String xtraParams, Connection conn)throws RemoteException,ITMException
	{
		System.out.println("ExecutePostSaveRec *****************");
		NodeList hdrDom = null;
		Node currDetail = null;
		String errString="";
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;
		String childNodeName = null;
		String tareWeight="",lineNoIssue="",lotSl="",containerCapacityStr="",unit="",itemCode="",tranIdIss="",locCode="",packetNo="",lotNo="";
		String siteCode = "";
		int lineNo=0;
		String tranId="";

		String sql="";
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		String sql1="" ,lotNoTemp="", lineNoSlTemp="",lineNoTemp="",lotSlTemp="";
		PreparedStatement pstmt = null;
		int upd=0;


		try
		{
			genericUtility =GenericUtility.getInstance();

			hdrDom = dom.getElementsByTagName("Detail1");
			parentNode = hdrDom.item(0);
			String objName = getObjName(parentNode);
			System.out.println("objName=["+objName+"]domID["+domID+"]");
			if(("w_disp_item_wiz".equals(objName)))
			{
				hdrDom = dom.getElementsByTagName("Detail2");
			}
			/*
			if(("w_disp_item_wiz".equals(objName)))
			{
				NodeList  Detail2Dom = dom.getElementsByTagName("Detail2");
				NodeList  Detail3Dom = dom.getElementsByTagName("Detail3");

				if(!( Detail2Dom != null  || Detail3Dom  != null ))
				{
					System.out.println("@@@ Detail2Dom and Detail3Dom is null.....");
					return "";
				}

			}
			 */
			/*
			if("w_weighing_wiz".equals(objName))
			{
				int count1 = 0,count2 = 0 ,cnt = 0;
				String scanBarCode = "";
				String sql="",netWeightStr="",grossWeight="",remNetWeightStr="";
				String mcCode="",mcUnit="";
				double mcVarience=0;
				PreparedStatement pstmt=null,pstmt1=null ;
				ResultSet rs=null,rs1=null;

				ArrayList qtyValueTare = new ArrayList();
				hdrDom = dom.getElementsByTagName("Detail2");
				DistCommon disCommon = new DistCommon();
				WOIScanConf woiScanConf = new WOIScanConf();
				genWOIssue = new GenWOIssue(); // 15/01/11 manoharan

				NodeList detail3List = dom.getElementsByTagName("Detail3");
				if(detail3List != null && detail3List.getLength() > 0)
				{

					String tranId = genericUtility.getColumnValueFromNode("tran_id",hdrDom.item(0));
					String userId = getValueFromXTRA_PARAMS(xtraParams,"loginCode");
					this.curTranId = tranId;	// 15/01/11 manoharan			
					System.out.println("tranId --------------->:"+tranId);

					pstmt=conn.prepareStatement("SELECT * FROM workorder_issdet_sl WHERE TRAN_ID=?");
					pstmt.setString(1,tranId);
					rs = pstmt.executeQuery();
					while(rs.next())
					{
						int lineNoDet = 0 ;
						double grossWeightDbl=0;
						double netWeight =0;
						double tareWeightDbl=0;
						double remNetWeight=0;
						double articles=0;
						double qtyPerArticleOrg =0;
						double containerCapacity = 0.0;
						double tareWt = 0; 
						double woQty = 0 ,potencyAdj = 0 ,qtyPerArticle= 0 ,stockQty = 0 ,drumVarience = 0;
						String lotSlFromWOI = "",workOrder = "" ;
						double shortQty= 0,totalWeighQty= 0 ,netwt = 0 ,diff = 0 ;
						String containerCapaStr = "0" ,mcVarienceStr = "0";	

						mcCode=rs.getString("mc_code");	
						netWeightStr=rs.getString("NET_WEIGHT");
						grossWeight=rs.getString("GROSS_WEIGHT");
						tareWeight=rs.getString("TARE_WEIGHT");
						lineNoIssue=rs.getString("LINE_NO__ISS");
						lotSl=rs.getString("LOT_SL");
						containerCapacityStr=rs.getString("CONTAINER_CAPACITY");
						unit = rs.getString("UNIT");
						itemCode = rs.getString("ITEM_CODE");
						tranIdIss=rs.getString("TRAN_ID__ISS");
						locCode=rs.getString("LOC_CODE");
						remNetWeightStr = rs.getString("REMAINING_NET_WT");
						packetNo =  rs.getString("line_no_sl");
						lotNo=rs.getString("LOT_NO");
						lineNoDet = rs.getInt("line_no");


						if(lineNoIssue !=null)
						{
							lineNo =Integer.parseInt(lineNoIssue);
						}

						sql = "select w.unit,m.wt_varience  from machines m,workcntr w where m.mc_code =w.mc_code "
								+" and  m.mc_code='"+mcCode+"' ";

						pstmt1 = conn.prepareStatement(sql);
						rs1 = pstmt1.executeQuery();
						if(rs1.next())
						{
							mcUnit = rs1.getString(1) ;
							mcVarience = rs1.getDouble(2) ;
						}
						rs1.close();
						pstmt1.close();
						rs1 = null;
						pstmt1 = null;
						if (mcVarience == 0) 
						{
							mcVarience = 0.001;
						}
						if((netWeightStr==null || netWeightStr.trim().length() == 0) && grossWeight != null && grossWeight.trim().length() > 0)
						{

							System.out.println( "containerCapacityStr :: " + containerCapacityStr );
							if(containerCapacityStr != null && containerCapacityStr.trim().length() > 0)
							{
								containerCapacity =Double.parseDouble(containerCapacityStr);
							}
							if(grossWeight != null && grossWeight.trim().length() > 0)
							{
								grossWeightDbl = Double.parseDouble(grossWeight);
							}
							else
							{
								grossWeightDbl =0;
							}
							if(tareWeight != null && tareWeight.trim().length() > 0)
							{
								tareWeightDbl = Double.parseDouble(tareWeight);
							}
							else
							{
								tareWeightDbl =0;
							}
							qtyValueTare = disCommon.getConvQuantityFact(mcUnit, unit, itemCode, tareWeightDbl, 0,conn);
							System.out.println("\n tareWeightDbl........................."+ qtyValueTare.get(1).toString());
							netWeight = grossWeightDbl - Double.parseDouble(qtyValueTare.get(1).toString());
							System.out.println("\n netWeight........................."+ netWeight);
							netWeight =getRequiredDecimal(netWeight,3);
							System.out.println("\n netWeight........................."+ netWeight);
							if( qtyValueTare.get(1).toString()  != null &&  qtyValueTare.get(1).toString().trim().length() > 0  )
							{
								tareWt =  Double.parseDouble(qtyValueTare.get(1).toString()) ;
								tareWt =getRequiredDecimal(tareWt,3);
							}
							//Update SQL for tare_weight,net_weight
							sql = "SELECT det.item_code,hdr.site_code,det.loc_code,det.lot_no,det.lot_sl ,hdr.work_order,det.no_art,det.quantity,nvl(det.potency_adj,0) as potency_adj"
									+" FROM  WORKORDER_ISS hdr, WORKORDER_ISSDET det "
									+" WHERE hdr.TRAN_ID = det.TRAN_ID"
									+" and   hdr.TRAN_ID ='"+tranIdIss+"' and det.line_no="+lineNo;
							pstmt1 = conn.prepareStatement(sql);
							rs1 = pstmt1.executeQuery();
							if(rs1.next())
							{
								itemCode = rs1.getString(1) ;
								siteCode = rs1.getString(2) ;
								locCode = rs1.getString(3) ;
								lotNo = rs1.getString(4) ;
								lotSlFromWOI = rs1.getString(5) ;
								workOrder = rs1.getString(6) ;
								articles = rs1.getDouble(7) ;
								woQty =rs1.getDouble(8) ;
								potencyAdj =rs1.getDouble(9) ;
							}
							rs1.close();
							pstmt1.close();
							pstmt1 =null;
							rs1 = null;
							woQty = getRequiredDecimal(woQty + potencyAdj,3);
							System.out.println("woQty ..................................."+woQty);
							String woiQtyStr = genericUtility.getColumnValue("quantity", dom);

							sql = "SELECT qty_per_art,quantity "
									+" FROM  stock "
									+" WHERE item_code ='"+itemCode+"' "
									+ " and  site_code ='"+siteCode+"' "
									+ " and  loc_code ='"+locCode+"' "
									+ " and  lot_no ='"+lotNo+"' "
									+ " and  lot_sl ='"+lotSl+"' ";
							pstmt1 = conn.prepareStatement(sql);
							rs1 = pstmt1.executeQuery();
							if(rs1.next())
							{
								qtyPerArticle = rs1.getDouble(1) ;
								stockQty = rs1.getDouble(2) ;
							}
							System.out.println("qtyPerArticle...."+qtyPerArticle);
							rs1.close();
							pstmt1.close();
							rs1 = null;
							qtyPerArticleOrg =qtyPerArticle;
							//if(stockQty < qtyPerArticle)// 21/03/11 manoharan commented and following condition added
							if(stockQty < qtyPerArticle && stockQty != 0) // 21/03/11 manoharan added condition && stockQty != 0
							{
								qtyPerArticle = stockQty;
							}
							drumVarience=0;
							MfgCommon mfgCommon = new MfgCommon();
							String drumVarienceStr= mfgCommon.getEnvMfg("999999","DRUM_VARIENCE_PERC",conn);
							System.out.println("drumVarienceStr.................. !["+drumVarienceStr+"]");
							if (drumVarienceStr == null || "null".equals(drumVarienceStr))
							{
								drumVarienceStr = "0";
							}

							if(drumVarienceStr.indexOf("NULL") > -1) //added on 18/06/09 NULLFOUNDfg
							{
								drumVarience = 0;
							}
							else
							{
								drumVarience = Double.parseDouble(drumVarienceStr);
								drumVarience = (qtyPerArticle *drumVarience) /100;
								drumVarience =getRequiredDecimal(drumVarience,3);
							}
							System.out.println("drumVarience.................. !"+drumVarience);

							if (remNetWeightStr == null || "null".equals(remNetWeightStr))
							{
								remNetWeightStr = "0";
							}
							if(remNetWeightStr != null)
							{
								remNetWeight =Double.parseDouble(remNetWeightStr);
							}
							// 03/08/10 manoharan

							shortQty = ( netWeight + remNetWeight ) - qtyPerArticle ;
							//totalWeighQty = ( netWeight + remNetWeight + getRequiredDecimal(netQtyPerPacket( dom2, lotNo, lotSl, packetNo.trim(),locCode,genericUtility.getColumnValue("line_no", dom), conn),3));
							totalWeighQty =  netWeight + remNetWeight;
							shortQty = totalWeighQty - qtyPerArticle ;
							woQty = getRequiredDecimal(woQty,3);
							// 03/08/10 manoharan
							containerCapaStr = genericUtility.getColumnValue("container_capacity", dom);
							if (containerCapaStr == null || "null".equals(containerCapaStr))
							{
								containerCapaStr = "0";
							}
							containerCapacity = Double.parseDouble(containerCapaStr);
							// 04/08/10 manoharan
							if (containerCapacity < qtyPerArticle && (woQty > containerCapacity) )
							{
								if (totalWeighQty < woQty)
								{
									shortQty = 0;
								}
							}
							shortQty = getRequiredDecimal(shortQty,3);
							//Update SQL for short_qty in workorder_issdet_sl
							if( containerCapacity == 0.0 )
							{
								//valueXmlString.append("<container_capacity protect=\"0\">").append("<![CDATA["+( qtyPerArticle ) +"]]>").append("</container_capacity>");
								containerCapacity=qtyPerArticle;
								//Update SQL for CONTAINER_CAPACITY in workorder_issdet_sl
							}
							sql ="select wt_varience, varience_type  from machines where mc_code='"+mcCode+"' and mc_type ='W' " ;

							pstmt1 = conn.prepareStatement(sql);
							rs1 = pstmt1.executeQuery();
							if(rs1.next())
							{
								mcVarienceStr =rs1.getString("wt_varience")== null ? "0.001" :rs1.getString("wt_varience");
							}
							rs1.close();
							pstmt1.close();
							rs1 = null;
							pstmt1 = null;
							if (mcVarienceStr == null || "null".equals(mcVarienceStr))
							{
								mcVarienceStr = "0.001";
							}

							mcVarience = Double.parseDouble(mcVarienceStr);
							if("GRM".equalsIgnoreCase(mcUnit) && "KGS".equalsIgnoreCase(unit) )
							{
								mcVarience = getRequiredDecimal(mcVarience /1000,3);
							}
							tareWeightDbl = Double.parseDouble(qtyValueTare.get(1).toString());
							System.out.println("tareWeightDbl ..................................."+tareWeightDbl);
							netwt = getRequiredDecimal(grossWeightDbl - tareWeightDbl,3);
							diff = woQty - netwt;
							diff =getRequiredDecimal(diff,3);
							System.out.println("mcVarience ..................................."+mcVarience);

							System.out.println("diff ..................................."+diff);
							System.out.println("mcVarience ..................................."+mcVarience);
							if(getRequiredDecimal(Math.abs(diff),3) <= mcVarience )
							{
								netWeight = woQty;
								netWeight =getRequiredDecimal(netWeight,3);
								grossWeight= new Double( getRequiredDecimal((woQty+tareWeightDbl) ,3)).toString();
								netWeight=woQty;
								tareWeight= new Double(tareWeightDbl).toString();
								//Update SQL for gross_weight,netWeight,tareWeight in workorder_issdet_sl
								if (remNetWeightStr == null || "null".equals(remNetWeightStr))
								{
									remNetWeightStr = "0";
								}
								if(remNetWeightStr !=null)
								{
									remNetWeight =Double.parseDouble(remNetWeightStr);
								}
								shortQty = ( woQty + remNetWeight ) - qtyPerArticle ;
								totalWeighQty = ( netWeight + remNetWeight + getRequiredDecimal(netQtyPerPacket( dom, lotNo, lotSl, packetNo.trim(),locCode,genericUtility.getColumnValue("line_no", dom), conn),3));
								shortQty = totalWeighQty - qtyPerArticle ;
								woQty = getRequiredDecimal(woQty,3);
								if (containerCapaStr == null || "null".equals(containerCapaStr))
								{
									containerCapaStr = "0";
								}
								containerCapacity = Double.parseDouble(containerCapaStr);
								// 04/08/10 manoharan
								if (containerCapacity < qtyPerArticle && (woQty > containerCapacity) )
								{
									if (totalWeighQty < woQty)
									{
										shortQty = 0;
									}
								}
								shortQty =getRequiredDecimal(shortQty,3);


							}
						}




						sql = "update workorder_issdet_sl set 	short_qty  =  ? ,container_capacity  =  ?,gross_weight = ? ,tare_weight = ? ,net_weight  = ? 	where tran_id = ?  	and line_no = ?   " ;
						pstmt = conn.prepareStatement( sql );
						pstmt.setDouble(1,shortQty);
						pstmt.setDouble(2, containerCapacity);
						pstmt.setDouble(3, Double.parseDouble( grossWeight));
						pstmt.setDouble(4, Double.parseDouble( tareWeight));
						pstmt.setDouble(5, Double.parseDouble( grossWeight) - Double.parseDouble( tareWeight));
						pstmt.setString( 6, tranId );
						pstmt.setInt( 7, lineNoDet );
						pstmt.executeUpdate();
						pstmt.close();
						pstmt = null;
					}
					rs.close();
					rs=null;
					pstmt.close();
					pstmt=null;
					updatingWOIDetSLForRemQty( tranId, dom, conn ,"w_disp_item_wiz");
					errString = updatingWorkorderIssuedet2( tranId,userId,dom,domID,conn,"w_disp_item_wiz");


					sql = "select count (1) from workorder_issdet_sl where tran_id__iss =  ? " ;
					pstmt1 = conn.prepareStatement(sql);
					pstmt1.setString(1, tranIdIss);
					rs1 = pstmt1.executeQuery();
					if(rs1.next())
					{
						count1 = rs1.getInt(1) ;
					}
					rs1.close();
					pstmt1.close();
					pstmt1 =null;
					rs1 = null;

					sql = " select wo_iss_tranid,site_code  from woi_scan_hdr where tran_id = ? " ;
					pstmt1 = conn.prepareStatement(sql);
					pstmt1.setString(1, tranId);
					rs1 = pstmt1.executeQuery();
					if(rs1.next())
					{
						tranIdIss = rs1.getString("wo_iss_tranid") ;
						siteCode = rs1.getString("site_code") ;
					}
					rs1.close();
					pstmt1.close();
					pstmt1 =null;
					rs1 = null;

					sql = "select  item_code, lot_no,lot_sl ,line_no,loc_code,quantity,unit ,nvl(potency_adj,0) AS potency_adj    from workorder_issdet where tran_id = ?   ";
					pstmt = conn.prepareStatement( sql );
					pstmt.setString( 1, tranIdIss );
					rs = pstmt.executeQuery();
					while( rs.next() )
					{
						itemCode =  rs.getString( "item_code");
						lineNo = rs.getInt("line_no");
						sql = " select count(1)  from siteitem where site_code = ? and item_code = ?    ";
						pstmt1 = conn.prepareStatement(sql);
						pstmt1.setString(1, siteCode);
						pstmt1.setString(2, itemCode);
						rs1 = pstmt1.executeQuery();
						if ( rs1.next() )
						{
							cnt = rs1.getInt(1);
						}
						pstmt1.close();
						pstmt1 = null;
						rs1.close();
						rs1 = null;
						if(cnt == 0)
						{
							sql = " select scanned_barcode  from item where item_code = ?  ";
							pstmt1 = conn.prepareStatement(sql);
							pstmt1.setString(1, itemCode);
							rs1 = pstmt1.executeQuery();
							if ( rs1.next() )
							{
								scanBarCode = checkNull(rs1.getString("scanned_barcode"));
							}
							pstmt1.close();
							pstmt1 = null;
							rs1.close();
							rs1 = null;
							if("2".equals(scanBarCode.trim()))
							{
								sql = " select count(*) from WORKORDER_ISSDET where TRAN_ID = ? and line_no  = ? and scanned = 'Y'  ";
								pstmt1 = conn.prepareStatement(sql);
								pstmt1.setString(1, tranIdIss);
								pstmt1.setInt(2, lineNo);
								rs1 = pstmt1.executeQuery();
								if ( rs1.next() )
								{
									cnt = rs1.getInt(1);
								}
								pstmt1.close();
								pstmt1 = null;
								rs1.close();
								rs1 = null;
								if(cnt > 0)
								{
									count2 ++;
								}
							}
							else
							{
								continue;
							}
						}
						else
						{
							sql = " select scanned_barcode  from siteitem where site_code = ? and item_code = ?  ";
							pstmt1 = conn.prepareStatement(sql);
							pstmt1.setString(1, siteCode);
							pstmt1.setString(2, itemCode);
							rs1 = pstmt1.executeQuery();
							if ( rs1.next() )
							{
								scanBarCode = checkNull(rs1.getString("scanned_barcode"));
							}
							pstmt1.close();
							pstmt1 = null;
							rs1.close();
							rs1 = null;

							if("2".equals(scanBarCode.trim()))
							{
								sql = " select count(*) from WORKORDER_ISSDET where TRAN_ID = ? and line_no  = ? and scanned = 'Y'  ";
								pstmt1 = conn.prepareStatement(sql);
								pstmt1.setString(1, tranIdIss);
								pstmt1.setInt(2, lineNo);
								rs1 = pstmt1.executeQuery();
								if ( rs1.next() )
								{
									cnt = rs1.getInt(1);
								}
								pstmt1.close();
								pstmt1 = null;
								rs1.close();
								rs1 = null;
								if(cnt > 0)
								{
									count2 ++;
								}
							}
							else
							{
								continue;
							}

						}

					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					System.out.println("count1 ="+count1+"   count2="+count2);
					if(count1 == count2)
					{
						errString = woiScanConf.confirm(tranIdIss, xtraParams, "",conn,false);
					}
				}

			}
			 */
			//else
			//{	

			//System.out.println("@@@@@@ for test tranid["+genericUtility.getColumnValueFromNode("wo_iss_tranid",hdrDom.item(0))+"]");

			//if("w_disp_item_wiz".equals(objName))
			//{
				//tranId = genericUtility.getColumnValueFromNode("wo_iss_tranid_1",hdrDom.item(0));
			//	tranId = genericUtility.getColumnValueFromNode("wo_iss_tranid",hdrDom.item(0));
			//}
			//else
			//{
				tranId = genericUtility.getColumnValueFromNode("tran_id",hdrDom.item(0));
			//}
			genWOIssue = new GenWOIssue(); // 15/01/11 manoharan
			this.curTranId = tranId;	// 15/01/11 manoharan			
			System.out.println("tranId --------------->:"+tranId); 
			String userId = getValueFromXTRA_PARAMS(xtraParams,"loginCode");
			siteCode = GenericUtility.getInstance().getColumnValueFromNode("site_code",hdrDom.item(0));				
			String operationStr = GenericUtility.getInstance().getColumnValueFromNode("operation",hdrDom.item(0));
			int operation =Integer.parseInt(operationStr);
			System.out.println("operationStr["+operationStr+"]");
			//COMMENTED BY HKUMAR ON 09-02-10
			//added on 12/01/10 for updating flag in workorder_issue
			//updatingWOIDet( tranId, dom, conn );


			//UPDATE REMANING OF PREVIOUS TRANSACTION FOR THE


			//
			updatingWOIDetSLForRemQty( tranId, dom, conn ,objName);
			//SAME LOT NO, LOT SL, EXP LEVEL, LINE_NO_SL

			//ADDED BY HKUMAR IN DADRA AS PER MANOHARAN SIR
			//errString = updatingWorkorderIssuedet( tranId,userId, conn);
			/*
			if("w_disp_item_wiz".equals(objName))
			{

				lotNoTemp = genericUtility.getColumnValue("lot_no", dom);
				lineNoSlTemp = genericUtility.getColumnValue("line_no_sl", dom);
				lineNoTemp = genericUtility.getColumnValue("line_no", dom);
				lotSlTemp  = genericUtility.getColumnValue("lot_sl", dom);
				System.out.println("@@@@@@635: tranId["+tranId+"]lotNoTemp["+lotNoTemp+"]lineNoSlTemp["+lineNoSlTemp+"]lineNoTemp["+lineNoTemp+"]lotSlTemp["+lotSlTemp+"]");

				sql = " select line_no, tran_id__iss , line_no__iss  from WORKORDER_ISSDET_SL " +
						" where TRAN_ID__ISS = ? and lot_no = ? and line_no_sl = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, tranId);
				pstmt.setString(2, lotNoTemp);
				pstmt.setString(3, lineNoSlTemp);
				rs = pstmt.executeQuery();
				while( rs.next() )
				{
					sql1 = " UPDATE WORKORDER_ISSDET_SL SET  SCANNED = 'Y' " +
							" where TRAN_ID__ISS = ? and lot_no = ? and line_no_sl = ? and line_no = ?";							
					pstmt1 = conn.prepareStatement(sql1);
					pstmt1.setString(1, tranId);
					pstmt1.setString(2, lotNoTemp);
					pstmt1.setString(3, lineNoSlTemp);
					pstmt1.setString(4,rs.getString("line_no"));
					upd =	pstmt1.executeUpdate();
					System.out.println("upd....TRAN_ID["+tranId+"]LINE_NO["+rs.getString("line_no")+"]..... ["+upd+"]");
					pstmt1.close();

					sql1 = " UPDATE WORKORDER_ISSDET SET  SCANNED = 'Y'  WHERE TRAN_ID = ? AND line_no = ? ";							
					pstmt1 = conn.prepareStatement(sql1);
					pstmt1.setString(1, rs.getString("tran_id__iss"));
					pstmt1.setString(2, rs.getString("line_no__iss"));
					upd =	pstmt1.executeUpdate();
					System.out.println("upd....TRAN_ID["+tranId+"]LINE_NO["+rs.getString("line_no")+"]..... ["+upd+"]");
					pstmt1.close();

				}
				pstmt.close();
				pstmt = null;
				rs.close();
				rs = null;

			}

			 */
			//	if(!("w_disp_item_wiz".equals(objName)))
			//	{	
			errString = updatingWorkorderIssuedet2( tranId,userId,dom,domID,conn,objName);
			
			System.out.println("@@@@@@@@@ errString ["+errString + "] next report activity----");
			
			//added for report..
			//if(errString == null || errString.trim().length() == 0) // 18/08/14 added by cpatil for report
			if(errString!=null && errString.indexOf("VTCNFSUCC")>-1)//Added by manoj dtd 13/04/2015 errString condition correced
			{
				System.out.println("@@@@@@@@@@@@ report code called next.....");
				// workorder_issdet_sl. remaining_net_wt is null or  workorder_issdet_sl. remaining_net_wt > 0
				// start on 11/08/14 for report 
				DistCommon discommon = new DistCommon();
				String isReportReq = checkNull(discommon.getDisparams("999999","STK_WEIGH_REPORT_REQ",conn));
				System.out.println("@@@@@@@@@@@@ isReportReq=["+isReportReq+"]tranID["+tranId+"]");
				if("Y".equalsIgnoreCase(isReportReq)) // 18/08/14 added by cpatil for report
				{
				
					System.out.println("@@@@@@@@@@@@ cpatil11/08/14 for report start -------");
					if(tranId == null  || tranId.trim().length() == 0)
					{
						tranId = "1";
					}
					long startTime7 = System.currentTimeMillis();

					errString =  generateReport( tranId,siteCode,xtraParams, conn );
					
					long endTime7 = System.currentTimeMillis();
					System.out.println("DIFFERANCE IN TIME FOR GENERATE REPORT IN SECONDS:::["+(endTime7-startTime7)/1000+"]");
					System.out.println("@@@@@@ errString [ "+ errString +"]");
					System.out.println("@@@@@@@@@@@@ cpatil11/08/14 for report end -------");
				// end on 11/08/14 for report
				}
			}
			// end for report
			
			
			//	}
			//}

		}//try end
		catch(Exception e)
		{			
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				//				if(conn!=null)
				//				{
				//					conn.close();
				//				}
				//				conn = null;
			}
			catch(Exception e)
			{
				System.err.println("Exception :WOIssueDetScanPosEJB:\n"+e.getMessage());
				e.printStackTrace();
				throw new ITMException(e);
			}
		}
		return errString;
	}
	

	private String generateReport(String tranId, String siteCode, String xtraParams, Connection conn) throws ITMException
	{
		
		System.out.println("@@@@@@@@ generateReport called.........tranId["+tranId+"]xtraParams["+xtraParams+"]");
		String errString ="";
		String printMode = "";
		String outputFilename = "";
		TreeMap argMap = null;
		String saleOrder = "";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		DistCommon discommon = new DistCommon();
		String sql="",refNo="",printer="",printLabel="",chgUser="",chgTerm="",mcCode="";
		ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
		String workOrder="";
		java.util.Date ordDate=null;
		Timestamp ordDateTS=null;
		SimpleDateFormat sdf=null;
		String ordDateTSstr="";
		try
		{
			
			// sql = " select ref_no, site_code, mc_code from stk_weighing where tran_id = ? ";
			sql = " select   mc_code, tran_id__iss  from workorder_issdet_sl  where tran_id =  ?   ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranId);
			rs = pstmt.executeQuery();
			if( rs.next())
			{
				mcCode = rs.getString("mc_code");
				refNo = rs.getString("tran_id__iss");
			}
			pstmt.close();
			pstmt = null;
			rs.close(); 
			rs = null;
			
			System.out.println("@@@@@ mcCode["+mcCode+"]tran_id__iss["+refNo+"]siteCode["+siteCode+"]");
			
			printLabel = checkNull(discommon.getDisparams("999999","STK_WEIGH_PRINT_LABEL",conn));
			System.out.println("@@@@@@@@ printer["+printer+"]::printLabel["+printLabel+"]");

			if( "Y".equalsIgnoreCase(printLabel))
			{	
				int cnt=0;
				sql = " select udf_str1 from gencodes where fld_name = 'PRINTER_NAME' and mod_name = 'X' and fld_value = ? ";
				//sql="  select printer_name from net_printers where printer_name= ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, mcCode);
				rs = pstmt.executeQuery();
				if( rs.next())
				{
					cnt++;
					printer = rs.getString("udf_str1");
					System.out.println("Printer Found in  gencodes..........["+printer+"]");
				}
				pstmt.close();pstmt = null;
				rs.close(); rs = null;
				
				if(cnt==0)
				{
					errString="INVPRINTER";
					errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);
					return errString;
				}				
			}
			
			GenericUtility genericUtility = GenericUtility.getInstance();
			chgUser = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginCode");
			chgTerm = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "termId");
			

			System.out.println("@@@@@@@ chgUser["+chgUser+"]::chgTerm["+chgTerm+"]");
			

			sql = " select work_order, ord_date from workorder where work_order in ( select work_order from workorder_iss where  tran_id = ? ) ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, refNo );
			rs = pstmt.executeQuery();
			if( rs.next())
			{
				workOrder = rs.getString("work_order");
				ordDateTS = rs.getTimestamp("ord_date");
			}
			pstmt.close();
			pstmt = null;
			rs.close(); 
			rs = null;
			sdf= new SimpleDateFormat(genericUtility.getApplDateFormat());
			ordDateTSstr=sdf.format(ordDateTS);
			//ordDateTS =java.sql.Timestamp.valueOf(sdf.format(ordDate).toString() + " 00:00:00.0");			
			printMode = checkNull(discommon.getDisparams("999999","STK_WEIGH_PRINT_MODE",conn));    // WAVE_PRINT_MODE
			System.out.println(" printMode ->["+printMode+"]");
			if("REPORT".equalsIgnoreCase(printMode))
			{
				//String printerStr  = discommon.getDisparams("999999","WAVE_PRINTER",conn);
				//String reportObjName = discommon.getDisparams("999999","WAVE_REP_OBJ_NAME",conn);
				String reportObjName = discommon.getDisparams("999999","STK_WEIGH_OBJ_NAME",conn);
				//String reportSavetype = discommon.getDisparams("999999","REPORT_SAVE_TYPE",conn);
				String reportSavetype = discommon.getDisparams("999999","STK_WEIGH_REPORT_SAVE_TYPE",conn);
				if( reportSavetype == null || reportSavetype.trim().length() == 0 )
				{
					reportSavetype = "NATIVE";
				}
				System.out.println("Printer *->["+printer.trim()+"]  report_obj_name ["+reportObjName+"] reportSavetype ->["+reportSavetype+"]");

				System.out.println("inside report  generation :: and tranId is ["+tranId+"]");
				ibase.webitm.reports.utility.JasperReportGenerator jrGenerator = new ibase.webitm.reports.utility.JasperReportGenerator();
				String xsdString ="<Arguments>" +
				
										"<Argument id=\"SITE_CODE__FR\" name=\"SITE_CODE__FR\">" +
										"<argType>java.lang.String</argType>" +
										"<value>"+siteCode+"</value>" +
										"</Argument>" +

										"<Argument id=\"QC_ORDER__FR\" name=\"QC_ORDER__FR\">" +
										"<argType>java.lang.String</argType>" +
										"<value>"+workOrder+"</value>" +
										"</Argument>" +

										"<Argument id=\"QC_ORDER__TO\" name=\"QC_ORDER__TO\">" +
										"<argType>java.lang.String</argType>" +
										"<value>"+workOrder+"</value>" +
										"</Argument>" +

										"<Argument id=\"QC_DATE__FR\" name=\"QC_DATE__FR\">" +
										"<argType>java.sql.Timestamp</argType>" +
										"<value>"+ordDateTSstr+"</value>" +
										"</Argument>" +

										"<Argument id=\"QC_DATE__TO\" name=\"QC_DATE__TO\">" +
										"<argType>java.sql.Timestamp</argType>" +
										"<value>"+ordDateTSstr+"</value>" +
										"</Argument>" +

										"<Argument id=\"WORKORDER_ISS__FR\" name=\"WORKORDER_ISS__FR\">" +
										"<argType>java.lang.String</argType>" +
										"<value>"+refNo+"</value>" +
										"</Argument>" +

										"<Argument id=\"WORKORDER_ISS__TO\" name=\"WORKORDER_ISS__TO\">" +
										"<argType>java.lang.String</argType>" +
										"<value>"+refNo+"</value>" +
										"</Argument>" +

										"<Argument id=\"line_no__fr\" name=\"line_no__fr\">" +
										"<argType>java.lang.String</argType>" +
										"<value>"+"00"+"</value>" +
										"</Argument>" +

										"<Argument id=\"line_no__to\" name=\"line_no__to\">" +
										"<argType>java.lang.String</argType>" +
										"<value>"+"ZZ"+"</value>" +
										"</Argument>" +

										"<Argument id=\"x_path\" name=\"x_path\">" +
										"<argType>java.lang.String</argType>" +
										"<value>/DocumentRoot/d_qc_label_material_issue</value>" +   // wave_task_main
										"</Argument>" +

										"<Argument id=\"design_source\" name=\"design_source\">" +
										"<argType>java.lang.String</argType>" +
										"<value>d_qc_label_material_issue.jrxml</value>" +
										"</Argument>" +

										"<Argument id=\"data_source\" name=\"data_source\">" +
										"<argType>java.lang.String</argType>" +
										"<value>SQL</value>" +
										"</Argument>" +

										"<Argument id=\"bind_type\" name=\"bind_type\">" +
										"<argType>java.lang.Integer</argType>" +
										"<value>0</value>" +
										"</Argument>" +

										"<Argument id=\"report_type\" name=\"report_type\">" +
										"<argType>java.lang.String</argType>" +
										"<value>JASPER</value>" +
										"</Argument>" +

										"<Argument id=\"report_save_type\" name=\"report_save_type\">" +
										"<argType>java.lang.String</argType>" +
										"<value>"+reportSavetype+"</value>" +
										"</Argument>" +

										"</Arguments>";
				System.out.println(" XSD parser  **->["+xsdString+"]");
				if( "PDF".equalsIgnoreCase(reportSavetype))
				{
					XSDParser xsdParser = new XSDParser(reportObjName);
					argMap = jrGenerator.createArgMap(xsdString);
					System.out.println("TreeMap:::->"+argMap);
					outputFilename = chgUser + "_" + refNo+"_"+tranId+"_"+ new Random().nextInt();
					jrGenerator.createReport(reportObjName , argMap, xsdParser, chgUser, outputFilename, conn);
				}
				else
				{
					errString = jrGenerator.callRemotePrintServlet(reportObjName, xsdString, chgUser, printer.trim(), 1, conn);
				}			
				System.out.println(" PRINTING task completed--------**------->");
			}
		}
		catch (Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try 
			{
				if( pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
				if( rs != null)
				{
					rs.close();
					rs = null;
				}

			} 
			catch (Exception e2) 
			{
				throw new ITMException(e2);
			}
		}
		return errString;
	}

	
	
	/*
	private String updatingWorkorderIssuedet(String tranId,String userId,  Connection conn)throws RemoteException,ITMException
	{
		System.out.println(" **********  updatingWorkorderIssuedet *************");
//		ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
		PreparedStatement pstmt = null,pstmt1 = null;
		ResultSet rs = null,rs1 = null;
		String tranIdIss="",errString="";
		String retString ="",sql="",scanLotSl="";
		String  siteCode="",itemCode="",locCode="",lotNo="",lotSl="";
		String expLev="",calcPotency="",adjPotency="",matchPotency="",unit="";
		String acctCodeWp="", cctrCodeWp="",reasCode="",qcReqd="",stkOpt="";
		String scannedBarCode = null;
		double minPotencyPerc=0,grossRate=0,tareWeight=0,scanQty=0;
		double quantity=0, potencyAdj=0,woIssQty=0,rate=0;
		int noArt=0,upd =0,newLineNo=0;
		HashMap invallocTraceMap = null;
		int lineNoIss=0;
		String lotSlNew="",workOrder="";
		String tranIDAllocDet = "";
		int lineNoAllocDet = 0;
		HashMap currentMap =new HashMap();
		HashMap updateMap =new HashMap();
		String xmlValues ="",tranIdG="";
		String locCodeNew ="";
		ITMDBAccessEJB itmDBAccessEJB =null;
		try
		{
			ArrayList woiScanAList = new ArrayList(); 
			itmDBAccessEJB = new ITMDBAccessEJB();
//			 sql =" select tran_id__iss,Lot_sl,line_no__iss "
//				  +" from workorder_issdet_sl where tran_id ='"+tranId+"' "
//			      +" AND (SCANNED  IS NULL OR SCANNED <> 'Y') ";			
			sql = " select tran_id__iss,line_no__iss,sum(net_weight) ,lot_sl,exp_lev,lot_sl__new,LOC_CODE "+
				  " from workorder_issdet_sl  "
				 +" where tran_id ='"+tranId+"'  group by lot_sl,tran_id__iss,line_no__iss,exp_lev,lot_sl__new,LOC_CODE ";

			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next())
			{	
				tranIdIss			= rs.getString("tran_id__iss");
				lineNoIss			= rs.getInt("line_no__iss");
				scanLotSl			= rs.getString("Lot_sl");
				scanQty				= rs.getDouble(3);
				lotSlNew			= rs.getString("lot_sl__new");  // added on 16/09/09 by rajendra for update lot_sl where weighing material stock quantity is 0  part11
				locCodeNew			= rs.getString("loc_code");  
				locCodeNew = locCodeNew == null ? "" : locCodeNew;
				HashMap woidethm = new HashMap();
				// Put elements to the map
				woidethm.put("tran_id__iss", tranIdIss);
				woidethm.put("line_no__iss", new Integer(lineNoIss));
				woidethm.put("Lot_sl", scanLotSl);
				woidethm.put("lot_sl__new", lotSlNew);
				woidethm.put("scan_qty", new Double(scanQty)); 
				woidethm.put("loc_code", locCodeNew); 
				woiScanAList.add(woidethm);
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null ;

			for(int ctr=0; ctr < woiScanAList.size(); ctr++)
			{
				currentMap = (HashMap) woiScanAList.get(ctr);
				System.out.println("[current map]--------------currentMap--------------->"+currentMap);
				tranIdIss	=	currentMap.get("tran_id__iss").toString();
				scanLotSl	=	currentMap.get("Lot_sl").toString();
				lotSlNew	=	currentMap.get("lot_sl__new").toString();
				scanQty		=	((Double)currentMap.get("scan_qty")).doubleValue(); 
				lineNoIss	   =((Integer)currentMap.get("line_no__iss")).intValue(); 
			}
			for(int ctr=0; ctr < woiScanAList.size(); ctr++)
			{
				currentMap = (HashMap) woiScanAList.get(ctr);
				System.out.println("[current map]--------------currentMap--------------->"+currentMap);
				tranIdIss	=	currentMap.get("tran_id__iss").toString();
				scanLotSl	=	currentMap.get("Lot_sl").toString();
				lotSlNew	=	currentMap.get("lot_sl__new").toString();
				scanQty		=	((Double)currentMap.get("scan_qty")).doubleValue(); 
				lineNoIss	=   ((Integer)currentMap.get("line_no__iss")).intValue(); 
				locCodeNew  =   currentMap.get("loc_code").toString();



				System.out.println("scanQty..........."+scanQty);
				System.out.println("scanLotSl........."+scanLotSl);
				System.out.println("lineNoIss........."+lineNoIss);
				System.out.println("locCodeNew........."+locCodeNew);

				sql =" select h.site_code,d.item_code,d.loc_code,d.lot_no,d.lot_sl , "
					+" d.quantity,d.potency_adj,d.unit,d.match_potency,d.min_potency_perc,d.adj_potency, "
					+" d.no_art, d.rate,d.qc_reqd,d.reas_code,d.acct_code__wp,d.cctr_code__wp, "
					+" d.stk_opt,d.gross_rate,d.tare_weight,d.calc_potency,d.exp_lev,h.work_order,"   
					+" ( select scanned_barcode from siteitem where site_code = h.site_code and item_code =d.item_code ) scanned_barcd "
					+" from workorder_iss h, workorder_issdet d where h.tran_id=d.tran_id "
					+" and h.tran_id ='"+tranIdIss+"' and d.line_no ="+lineNoIss;

				pstmt1 = conn.prepareStatement(sql);
				 rs1 = pstmt1.executeQuery();
				if (rs1.next())
				{	
					siteCode					= rs1.getString("site_code");
					itemCode					= rs1.getString("item_code");
					locCode						= rs1.getString("loc_code");
					lotNo						= rs1.getString("lot_no");
					lotSl						= rs1.getString("lot_sl");
					quantity					= rs1.getDouble("quantity");
					potencyAdj					= rs1.getDouble("potency_adj");
					unit						= rs1.getString("unit");
					matchPotency				= rs1.getString("match_potency");
					minPotencyPerc				= rs1.getDouble("min_potency_perc");
					adjPotency					= rs1.getString("adj_potency");
					noArt						= rs1.getInt("no_art");
					rate						= rs1.getDouble("rate");
					qcReqd						= rs1.getString("qc_reqd");
					reasCode					= rs1.getString("reas_code");
					acctCodeWp					= rs1.getString("acct_code__wp");
					cctrCodeWp					= rs1.getString("cctr_code__wp");
					stkOpt						= rs1.getString("stk_opt");
					grossRate					= rs1.getDouble("gross_rate");
					tareWeight					= rs1.getDouble("tare_weight");
					//potencyAdj			    = rs1.getDouble("potency_adj");
					calcPotency					= rs1.getString("calc_potency");
					expLev						= rs1.getString("exp_lev");
					workOrder					= rs1.getString("work_order");
					scannedBarCode				= rs1.getString( "scanned_barcd" );
				 }
				 rs1.close();
				 rs1 =null;
				 pstmt1.close();
				 pstmt1 =null;
				 System.out.println("quantity..........."+quantity);
				 System.out.println("potencyAdj..........."+potencyAdj);
				 woIssQty =quantity + potencyAdj;

				if( (!scanLotSl.equals(lotSl) || !locCodeNew.equals(locCode)) && "2".equals(scannedBarCode) ) 
				{	
					xmlValues ="<?xml version=\"1.0\" encoding=\"utf-8\"?><Root>";
					xmlValues = xmlValues + "<Header></Header>";
					xmlValues = xmlValues + "<Detail1>";
					xmlValues = xmlValues +	"<tran_id></tran_id>";
					//xmlValues = xmlValues + "<site_code>"+siteCode.trim()+"</site_code>";
					xmlValues = xmlValues + "</Detail1></Root>";	
					if(scanQty==woIssQty)
					{
//						String stockLoc = null;
//comment by rajendra 28/01/2010 it is not required now 
//						String locSql = "SELECT loc_code  "
//										+"	FROM  stock  "
//										+" WHERE item_code= ? "
//										+"	and  site_code = ? "
//										+"	and  lot_no = ? "
//										+"	and  lot_sl = ? "
//										+"	and quantity - alloc_qty > 0 "  
//										+"	and ( ( alloc_qty > 0 and (quantity - alloc_qty) >= ? ) "
//										+"				 or alloc_qty = 0 ) ";
//						pstmt1 = conn.prepareStatement( locSql );
//						pstmt1.setString( 1, itemCode );
//						pstmt1.setString( 2, siteCode );
//						pstmt1.setString( 3, lotNo );
//						pstmt1.setString( 4, scanLotSl );
//						pstmt1.setDouble( 5, woIssQty );
//						rs1 = pstmt1.executeQuery();
//						if( rs1.next() )
//						{
//							stockLoc = rs1.getString( "loc_code" );
//						}
//						rs1.close();
//						rs1 =null;
//						pstmt1.close();
//						pstmt1 =null;
//						System.out.println("stockLoc :::-........... ["+stockLoc+"]");
						//sql =" update workorder_issdet set lot_sl ='"+scanLotSl+"' where tran_id ='"+tranIdIss+"' and line_no="+lineNoIss;
						//added by hkumar on 28/01/10 start
						//sql =" update workorder_issdet set lot_sl ='"+scanLotSl+"', loc_code = '" + (stockLoc ==null ?"":stockLoc) + "'  where tran_id ='"+tranIdIss+"' and line_no="+lineNoIss;

						sql =" update workorder_issdet set lot_sl ='"+scanLotSl+"', loc_code = '" + (locCodeNew ==null ?"":locCodeNew) + "'  where tran_id ='"+tranIdIss+"' and line_no="+lineNoIss;

						pstmt1 = conn.prepareStatement(sql);
						upd =	pstmt1.executeUpdate();
						System.out.println("upd......... ["+upd+"]");
						pstmt1.close();

						sql =" update stock  set lot_sl__org ='"+lotSl+"'  where site_code ='"+siteCode+"' "
							//alam on 280809
							+" and item_code='"+itemCode+"'  and loc_code ='"+locCode+"'  and lot_no ='"+lotNo+"' and lot_sl ='"+scanLotSl+"' ";
							//+" and item_code='"+itemCode+"'  and loc_code ='"+stockLoc+"'  and lot_no ='"+lotNo+"' and lot_sl ='"+scanLotSl+"' ";

						pstmt1 = conn.prepareStatement(sql);
						upd =	pstmt1.executeUpdate();
						System.out.println("upd......... ["+upd+"]");
						pstmt1.close();

						//added by rajendra on 01/10/09 for checking org lot sl
//						String lotSlOrg ="";
//						locSql = "SELECT LOT_SL__ORG  "
//										+"	FROM  stock  "
//										+" WHERE item_code= ? "
//										+"	and  site_code = ? "
//										+"	and  lot_no = ? "
//										+"	and  loc_code = ? "
//										+"	and  lot_sl = ? ";
//						pstmt1 = conn.prepareStatement( locSql );
//						pstmt1.setString( 1, itemCode );
//						pstmt1.setString( 2, siteCode );
//						pstmt1.setString( 3, lotNo );
//						pstmt1.setString( 4, stockLoc );
//						pstmt1.setString( 5, scanLotSl );
//						rs1 = pstmt1.executeQuery();
//						if( rs1.next() )
//						{
//							lotSlOrg = rs1.getString( "LOT_SL__ORG" );
//						}
//						rs1.close();
//						rs1 =null;
//						pstmt1.close();
//						pstmt1 =null;
//						System.out.println("lotSlOrg......... ["+lotSlOrg+"]");


						tranIdG = generateTranId("T_LOTSL_TRACE",xmlValues,conn);//function to generate NEW transaction id
						if(tranIdG == null || tranIdG.trim().length() ==0)
						{
							errString = itmDBAccessEJB.getErrorString("","VTTRANID",userId);
							return errString;
						}	
						System.out.println("tranIdG..............."+tranIdG);
						sql ="INSERT INTO mfg_lotsl_trace (	tran_id ,	tran_date ,	item_code ,	site_code ,	loc_code ,	lot_no ,	lot_sl__old ,	lot_sl__new ,line_no_iss__old, line_no_iss__new ,qty_old, qty_new,tran_id__iss,PARENT_WOISS)"
							+" values ( ? ,? ,? ,? , ?,  ?, ?, ?,?,?,?,?,?,?)";

						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1,tranIdG); 
						pstmt.setTimestamp(2,new java.sql.Timestamp(System.currentTimeMillis()));
						pstmt.setString(3,itemCode);
						pstmt.setString(4,siteCode);
						pstmt.setString(5,locCode);
						pstmt.setString(6,lotNo);
						pstmt.setString(7,lotSl);
						pstmt.setString(8,scanLotSl);
						pstmt.setInt(9,lineNoIss);
						pstmt.setInt(10,lineNoIss);
						pstmt.setDouble(11,scanQty);
						pstmt.setDouble(12,scanQty);
						pstmt.setString(13,tranIdIss);
						pstmt.setString(14,tranIdIss);
						upd = pstmt.executeUpdate();
						if(upd == 1)
						{
							System.out.println("Record inserted mfg_lotsl_trace:"+upd);
						}
						pstmt.close();

						//if(!scanLotSl.equalsIgnoreCase(lotSlOrg))
						//{
							invallocTraceMap = new HashMap();
							InvAllocTraceBean invallocTraceBean = new InvAllocTraceBean();
							invallocTraceMap.put("ref_ser","D-ALOC");
							invallocTraceMap.put("site_code",siteCode);
							invallocTraceMap.put("item_code",itemCode);
							invallocTraceMap.put("loc_code",locCode);
							invallocTraceMap.put("lot_no",lotNo);
							invallocTraceMap.put("lot_sl",lotSl);
							invallocTraceMap.put("alloc_qty",new Double(-1 * Math.abs(quantity+potencyAdj)));
							System.out.print("DeallocQty = " + (-1*Math.abs(quantity+potencyAdj)));
							invallocTraceMap.put("chg_user",userId);
							invallocTraceMap.put("chg_term",userId);
							invallocTraceMap.put("chg_win","W_WOI_SCAN");
							invallocTraceMap.put("ref_id",tranIdIss);
							invallocTraceMap.put("ref_line",lineNoIss+"");
							errString = invallocTraceBean.updateInvallocTrace(invallocTraceMap,conn);
							if (errString != null && errString.trim().length() > 0)
							{
									System.out.println("errString :::"+ errString );
									return errString;
							}

							invallocTraceMap.put("ref_ser","W-ISS");
							invallocTraceMap.put("site_code",siteCode);
							invallocTraceMap.put("item_code",itemCode);
							//msalam on 290809 updating scanned loc code
							//invallocTraceMap.put("loc_code",locCode);
							//hkumar on 28/01/10 start
							//invallocTraceMap.put("loc_code",stockLoc);
							invallocTraceMap.put("loc_code",locCodeNew);
							//hkumar on 28/01/10 end
							invallocTraceMap.put("lot_no",lotNo);
							invallocTraceMap.put("lot_sl",scanLotSl);

							invallocTraceMap.put("alloc_qty",new Double(scanQty));
							System.out.print("alloc_qty = " + (scanQty));
							invallocTraceMap.put("chg_user",userId);
							invallocTraceMap.put("chg_term",userId);
							invallocTraceMap.put("chg_win","W_WOI_SCAN");
							invallocTraceMap.put("ref_id",tranIdIss);
							invallocTraceMap.put("ref_line",lineNoIss+"");
							errString = invallocTraceBean.updateInvallocTrace(invallocTraceMap,conn);
							if (errString != null && errString.trim().length() > 0)
							{
									System.out.println("errString :::"+ errString );
									return errString;
							}

							//added on 20/09/09 by rajendra for update inv_alloc_det  part11
//							sql =" SELECT tran_id FROM INV_ALLOCATE WHERE WORK_ORDER='"+workOrder+"'";
//							System.out.println("sql..........."+sql);
//							pstmt = conn.prepareStatement(sql);
//							rs = pstmt.executeQuery();
//							if (rs.next())
//							{	
//								tranIDAllocDet	= rs.getString("tran_id");
//							}
//							rs.close();
//							rs =null;
//							pstmt.close();
//							pstmt =null;

							sql ="select b.tran_id "
							+" from inv_alloc_det a, inv_allocate b "
							+" where ( b.tran_id = a.tran_id ) and "
								+"	( a.exp_lev = '"+expLev+"' ) and "
								+"	( a.alloc_qty > 0 ) and "
								+"	a.lot_no ='"+lotNo+"' and "
								+"	a.lot_sl ='"+lotSl+"' and "
								+"	( case when a.deallocated is null then 'N' else a.deallocated end = 'N' ) and  "
								+"	( b.work_order = '"+workOrder+"' ) and  "
								+"	( case when b.confirmed is null then 'N' else b.confirmed end = 'Y' ) and "
								+"	( b.status = 'O' or b.status is null ) " 
							+" order by a.line_no desc ";
							sql =" SELECT tran_id FROM INV_ALLOCATE WHERE WORK_ORDER='"+workOrder+"'";

							pstmt = conn.prepareStatement(sql);
							rs = pstmt.executeQuery();
							if (rs.next())
							{	
								tranIDAllocDet	= rs.getString("tran_id");
							}
							rs.close();
							rs =null;
							pstmt.close();
							pstmt =null;

							sql =" update inv_Alloc_det set quantity = quantity-?,issue_qty =issue_qty-?,Deallocated ='Y' "
								+" where site_code =? and item_code =? " 
								+" and lot_no =?  and lot_sl =?   and loc_code =? "
								+" and exp_lev=? and tran_id =? ";

							pstmt = conn.prepareStatement(sql);
							pstmt.setDouble( 1, scanQty );
							pstmt.setDouble( 2, scanQty );
							pstmt.setString( 3, siteCode );
							pstmt.setString( 4, itemCode );
							pstmt.setString( 5, lotNo );
							pstmt.setString( 6, lotSl );
							//hkumar on 28/01/10 start
							if(locCodeNew.equals(locCode))
								pstmt.setString( 7, locCode );
							else
								pstmt.setString( 7, locCodeNew );
							//hkumar on 28/01/10 end
							pstmt.setString( 8, expLev );
							pstmt.setString( 9, tranIDAllocDet );
							upd =	pstmt.executeUpdate();
							System.out.println("upd......... ["+upd+"]");
							pstmt.close();

							sql =" update inv_Alloc_det set quantity = quantity+?,issue_qty =issue_qty+?,dealloc_qty=dealloc_qty+?,alloc_qty =alloc_qty+? "
								+" where site_code =? and item_code =? " 
								+" and lot_no =?  and lot_sl =?   and loc_code =? "
								+" and exp_lev=? and tran_id =? ";

							pstmt = conn.prepareStatement(sql);
							pstmt.setDouble( 1, scanQty );
							pstmt.setDouble( 2, scanQty );
							pstmt.setDouble( 3, scanQty );
							pstmt.setDouble( 4, scanQty );
							pstmt.setString( 5, siteCode );
							pstmt.setString( 6, itemCode );
							pstmt.setString( 7, lotNo );
							pstmt.setString( 8, scanLotSl );
							//hkumar on 28/01/10 start
							//pstmt.setString( 9, stockLoc );
							pstmt.setString( 9, locCodeNew );
							//hkumar on 28/01/10 end
							pstmt.setString( 10, expLev );
							pstmt.setString( 11, tranIDAllocDet );
							upd =	pstmt.executeUpdate();
							System.out.println("upd......... ["+upd+"]");
							pstmt.close();

							if(upd == 0)
							{

								sql =" select max(line_no) from inv_Alloc_det where  tran_id ='"+tranIDAllocDet+"' ";

								pstmt = conn.prepareStatement(sql);
								rs = pstmt.executeQuery();
								if (rs.next())
								{	
									lineNoAllocDet			= rs.getInt(1);
								}
								rs.close();
								rs =null;
								pstmt.close();
								pstmt =null;
								lineNoAllocDet =lineNoAllocDet+1;
								sql ="INSERT INTO INV_ALLOC_DET (TRAN_ID,LINE_NO,SITE_CODE,ITEM_CODE,LOT_NO,LOT_SL,QUANTITY,REMARKS,REAS_CODE, "
									+" LOC_CODE,EXP_LEV,POTENCY_ADJ,ISSUE_QTY,DEALLOC_QTY,ALLOC_QTY,DEALLOCATED )"
									+" values ( ? ,? ,? ,? , ?, "
									+ " ?, ?, ?, ?, ? ,"
									+ " ?, ?, ?, ?, ? ,? )";

								pstmt1 = conn.prepareStatement(sql);
								pstmt1.setString(1,tranIDAllocDet); 
								pstmt1.setInt(2,lineNoAllocDet);
								pstmt1.setString(3,siteCode);
								pstmt1.setString(4,itemCode);
								pstmt1.setString(5,lotNo);
								pstmt1.setString(6,scanLotSl);
								pstmt1.setDouble(7,scanQty);
								pstmt1.setString(8,"Different Lot sl Scanned");				
								pstmt1.setString(9,reasCode); 
								//hkumar on 28/01/10 start
								//pstmt1.setString(10,locCode);
								if(locCodeNew.equals(locCode))
									pstmt1.setString(10,locCode);
								else
									pstmt1.setString(10,locCodeNew);
								//hkumar on 28/01/10 end								
								pstmt1.setString(11,expLev); 
								pstmt1.setDouble(12,0); 
								pstmt1.setDouble(13,scanQty); 
								pstmt1.setDouble(14,scanQty); 
								pstmt1.setDouble(15,scanQty);
								pstmt1.setString(16,"N");
								upd = pstmt1.executeUpdate();
								if(upd == 1)
								{
									System.out.println("Record inserted in INV_ALLOC_DET.............:"+upd);
								}
								pstmt1.close();
							}
						//}//if(!lotSl.equalsEgnoreCase(lotSlOrg))

						//ended on 20/09/09 by rajendra for update inv_alloc_det  part11

					}
					else
					{						
					 //added by rajendra to check stock qty on 11/09/09 part11
						double stkQty=0.0;
						String stockQty = "SELECT quantity  "
										+"	FROM  stock  "
										+" WHERE item_code = ? "
										+"	and  site_code = ? "
										+"	and  lot_no = ? "
										+"	and  lot_sl = ? "
										+"	and  loc_code = ? ";
						pstmt1 = conn.prepareStatement( stockQty );
						pstmt1.setString( 1, itemCode );
						pstmt1.setString( 2, siteCode );
						pstmt1.setString( 3, lotNo );
						pstmt1.setString( 4, scanLotSl );
						pstmt1.setString( 5, locCode );
						rs1 = pstmt1.executeQuery();
						if( rs1.next() )
						{
							stkQty = rs1.getDouble( 1 );
						}
						rs1.close();
						rs1 =null;
						pstmt1.close();
						pstmt1 =null;
						System.out.println("stkQty..........."+stkQty);
						if(stkQty == 0.0)
						{
							scanLotSl = lotSlNew;
						}
					//ended by rajendra to check stock qty on 11/09/09
					 woIssQty =woIssQty - scanQty;
					 sql =" select max(line_no)  from  workorder_issdet  where  tran_id ='"+tranIdIss+"' ";

					 pstmt1 = conn.prepareStatement(sql);
					 rs1 = pstmt1.executeQuery();
					 if (rs1.next())
					 {	
								newLineNo			= rs1.getInt(1);
					 }
					  rs1.close();
					  rs1 =null;
					  pstmt1.close();
					  pstmt1 =null;
					  newLineNo =newLineNo+1;

						sql ="insert into workorder_issdet (tran_id, line_no, exp_lev,item_code, loc_code, lot_no, lot_sl, quantity,no_art,"
								+" unit, reas_code, unit__alt,cctr_code__wp,acct_code__wp, rate, gross_rate, stk_opt, net_weight, gross_weight, tare_weight,calc_potency, potency_adj,"
								+"  match_potency,min_potency_perc,adj_potency,qc_reqd) "
								+" values ( ? ,? ,? ,? , ?, "
								+ " ?, ?, ?, ?, ? ,"
								+ " ?, ?, ?, ?, ? ,"
								+ " ?, ?, ?, ?, ? ,? ,? ,?,?,?,?)";

						pstmt1 = conn.prepareStatement(sql);
						pstmt1.setString(1,tranIdIss); 
						pstmt1.setInt(2,newLineNo);
						pstmt1.setString(3,expLev);
						pstmt1.setString(4,itemCode);
						pstmt1.setString(5,locCodeNew);//28/01/2010
						pstmt1.setString(6,lotNo);
						pstmt1.setString(7,lotSl);
						pstmt1.setDouble(8,woIssQty);				
						pstmt1.setInt(9,(noArt-1)); 
						pstmt1.setString(10,unit);//unit
						pstmt1.setString(11,reasCode); 
						pstmt1.setString(12,""); 
						pstmt1.setString(13,cctrCodeWp); 
						pstmt1.setString(14,acctCodeWp); 
						pstmt1.setDouble(15,rate);
						pstmt1.setDouble(16,grossRate);
						pstmt1.setString(17,stkOpt);
						pstmt1.setDouble(18,woIssQty);		
						pstmt1.setDouble(19,woIssQty);				
						pstmt1.setDouble(20,0);
						pstmt1.setString(21,calcPotency);
						pstmt1.setDouble(22,0);	  
						pstmt1.setString(23,matchPotency);
						pstmt1.setDouble(24,minPotencyPerc);
						pstmt1.setString(25,adjPotency); 
						pstmt1.setString(26,qcReqd);
						//pstmt.setDouble(22,ExtraQty);	
						upd = pstmt1.executeUpdate();
						if(upd == 1)
						{
							System.out.println("Record inserted:"+upd);
						}
						pstmt1.close();

						sql =" update workorder_issdet set lot_sl ='"+scanLotSl+"', quantity="+(scanQty-potencyAdj)+",no_art=1 "
							+" where tran_id ='"+tranIdIss+"' and line_no="+lineNoIss;

						pstmt1 = conn.prepareStatement(sql);
						upd	   =	pstmt1.executeUpdate();
						System.out.println("upd......... ["+upd+"]");
						pstmt1.close();

						tranIdG = generateTranId("T_LOTSL_TRACE",xmlValues,conn);//function to generate NEW transaction id
						if(tranIdG == null || tranIdG.trim().length() ==0)
						{
							errString = itmDBAccessEJB.getErrorString("","VTTRANID",userId);
							return errString;
						}	
						System.out.println("tranIdG..............."+tranIdG);
						sql ="INSERT INTO mfg_lotsl_trace (	tran_id ,	tran_date ,	item_code ,	site_code ,	loc_code ,	lot_no ,	lot_sl__old ,	lot_sl__new ,line_no_iss__old, line_no_iss__new ,qty_old, qty_new,tran_id__iss,PARENT_WOISS )"
							+" values ( ? ,? ,? ,? , ?,  ?, ?, ?,?,?,?,?,?,?)";

						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1,tranIdG); 
						pstmt.setTimestamp(2,new java.sql.Timestamp(System.currentTimeMillis()));
						pstmt.setString(3,itemCode);
						pstmt.setString(4,siteCode);
						pstmt.setString(5,locCode);
						pstmt.setString(6,lotNo);
						pstmt.setString(7,lotSl);
						pstmt.setString(8,scanLotSl);
						pstmt.setInt(9,lineNoIss);
						pstmt.setInt(10,newLineNo);
						pstmt.setDouble(11,woIssQty+scanQty);
						pstmt.setDouble(12,scanQty);
						pstmt.setString(13,tranIdIss);
						pstmt.setString(14,tranIdIss);
						upd = pstmt.executeUpdate();
						if(upd == 1)
						{
							System.out.println("Record inserted mfg_lotsl_trace:"+upd);
						}
						pstmt.close();


						sql =" update stock  set lot_sl__org ='"+lotSl+"'  where site_code ='"+siteCode+"' "
								+" and item_code='"+itemCode+"'  and loc_code ='"+locCode+"'  and lot_no ='"+lotNo+"' and lot_sl ='"+scanLotSl+"' ";

						pstmt1 = conn.prepareStatement(sql);
						upd =	pstmt1.executeUpdate();
						System.out.println("upd......... ["+upd+"]");
						pstmt1.close();


						//update line_no__iss in workorder_issdet_sl on 16/09/09 by rajendra part11

						sql =" update workorder_issdet_sl  set line_no__iss ="+newLineNo+"  where tran_id ='"+tranId+"' "
							+" and tran_id__iss='"+tranIdIss+"'  and lot_sl ='"+lotSl+"'  and exp_lev ='"+expLev+"'  and line_no__iss ="+lineNoIss ;

						pstmt1 = conn.prepareStatement(sql);
						upd =	pstmt1.executeUpdate();
						System.out.println("upd......... ["+upd+"]");
						pstmt1.close();

						for(int count=0; count < woiScanAList.size(); count++)
						{
							String tranIdIssList="",LotSlList="",lineNoissList="";
							double scanQtyList=0;
							int lineNoIssue=0;
							updateMap = (HashMap) woiScanAList.get(count);
							System.out.println("[count]--------------count--------------->"+count);
							System.out.println("[updateMap map]--------------updateMap--------------->"+updateMap);
							tranIdIssList	=	updateMap.get("tran_id__iss").toString();
							LotSlList		=	updateMap.get("Lot_sl").toString();
							lineNoissList		=	updateMap.get("line_no__iss").toString();
							lineNoIssue =Integer.parseInt(lineNoissList);
							System.out.println("[lineNoIssue]--------------lineNoIssue--------------->"+lineNoIssue);
							scanQtyList		=	((Double)updateMap.get("scan_qty")).doubleValue(); 
//							if(tranIdIss.equalsIgnoreCase(tranIdIssList) && lotSl.equalsIgnoreCase(LotSlList)  &&
//								(scanQtyList == woIssQty))
//							{
								if(tranIdIss.equalsIgnoreCase(tranIdIssList)  && 	(lineNoIss ==lineNoIssue ))
							{
								updateMap.put("line_no__iss", new Integer(newLineNo));
								woiScanAList.set(count,updateMap);
								System.out.println("[newLineNo]--------------updateMap--------------->"+newLineNo);
								System.out.println("[updateMap]--------------updateMap--------------->"+updateMap);
							}

						}
						invallocTraceMap = new HashMap();
						InvAllocTraceBean invallocTraceBean = new InvAllocTraceBean();
						invallocTraceMap.put("ref_ser","D-ALOC");
						invallocTraceMap.put("site_code",siteCode);
						invallocTraceMap.put("item_code",itemCode);
						invallocTraceMap.put("loc_code",locCode);
						invallocTraceMap.put("lot_no",lotNo);
						invallocTraceMap.put("lot_sl",lotSl);
						invallocTraceMap.put("alloc_qty",new Double(-1 * Math.abs(scanQty)));
						System.out.print("DeallocQty = " + (-1*Math.abs(scanQty)));
						invallocTraceMap.put("chg_user",userId);
						invallocTraceMap.put("chg_term",userId);
						invallocTraceMap.put("chg_win","W_WOI_SCAN");
						invallocTraceMap.put("ref_id",tranIdIss);
						invallocTraceMap.put("ref_line",lineNoIss+"");
						errString = invallocTraceBean.updateInvallocTrace(invallocTraceMap,conn);
						if (errString != null && errString.trim().length() > 0)
						{
								System.out.println("errString :::"+ errString );
								return errString;
						}

						invallocTraceMap.put("ref_ser","W-ISS");
						invallocTraceMap.put("site_code",siteCode);
						invallocTraceMap.put("item_code",itemCode);
						invallocTraceMap.put("loc_code",locCodeNew);//28/01/2010
						invallocTraceMap.put("lot_no",lotNo);
						invallocTraceMap.put("lot_sl",scanLotSl);
						invallocTraceMap.put("alloc_qty",new Double(scanQty));
						System.out.print("alloc_qty = " + (scanQty));
						invallocTraceMap.put("chg_user",userId);
						invallocTraceMap.put("chg_term",userId);
						invallocTraceMap.put("chg_win","W_WOI_SCAN");
						invallocTraceMap.put("ref_id",tranIdIss);
						invallocTraceMap.put("ref_line",lineNoIss+"");
						errString = invallocTraceBean.updateInvallocTrace(invallocTraceMap,conn);
						if (errString != null && errString.trim().length() > 0)
						{
								System.out.println("errString :::"+ errString );
								return errString;
						}


						//added on 20/09/09 by rajendra for update inv_alloc_det  part11
						sql =" SELECT tran_id FROM INV_ALLOCATE WHERE WORK_ORDER='"+workOrder+"'";

						pstmt = conn.prepareStatement(sql);
						rs = pstmt.executeQuery();
						if (rs.next())
						{	
							tranIDAllocDet	= rs.getString("tran_id");
						}
						rs.close();
						rs =null;
						pstmt.close();
						pstmt =null;

						sql =" update inv_Alloc_det set quantity = quantity-?,issue_qty =issue_qty-?,Deallocated ='Y' "
							+" where site_code =? and item_code =? " 
							+" and lot_no =?  and lot_sl =?   and loc_code =? "
							+" and exp_lev=? and tran_id =? ";

						pstmt = conn.prepareStatement(sql);
						pstmt.setDouble( 1, scanQty );
						pstmt.setDouble( 2, scanQty );
						pstmt.setString( 3, siteCode );
						pstmt.setString( 4, itemCode );
						pstmt.setString( 5, lotNo );
						pstmt.setString( 6, lotSl );
						pstmt.setString( 7, locCode );
						pstmt.setString( 8, expLev );
						pstmt.setString( 9, tranIDAllocDet );
						upd =	pstmt.executeUpdate();
						System.out.println("upd......... ["+upd+"]");
						pstmt.close();

						upd =0;
						sql =" update inv_Alloc_det set quantity = quantity+?,issue_qty =issue_qty+?,dealloc_qty =dealloc_qty+?,alloc_qty =alloc_qty+? "
							+" where site_code =? and item_code =? " 
							+" and lot_no =?  and lot_sl =?   and loc_code =? "
							+" and exp_lev=? and tran_id =? ";

						pstmt = conn.prepareStatement(sql);
						pstmt.setDouble( 1, scanQty );
						pstmt.setDouble( 2, scanQty );
						pstmt.setDouble( 3, scanQty );
						pstmt.setDouble( 4, scanQty );
						pstmt.setString( 5, siteCode );
						pstmt.setString( 6, itemCode );
						pstmt.setString( 7, lotNo );
						pstmt.setString( 8, scanLotSl );
						pstmt.setString( 9, locCodeNew );//added on 28/01/2010
						pstmt.setString( 10, expLev );
						pstmt.setString( 11, tranIDAllocDet );
						upd =	pstmt.executeUpdate();
						System.out.println("upd......... ["+upd+"]");
						pstmt.close();

						if(upd ==0)
						{
							sql =" select max(line_no) from inv_Alloc_det where  tran_id ='"+tranIDAllocDet+"' ";

							pstmt = conn.prepareStatement(sql);
							rs = pstmt.executeQuery();
							if (rs.next())
							{	
								lineNoAllocDet			= rs.getInt(1);
							}
							rs.close();
							rs =null;
							pstmt.close();
							pstmt =null;
							lineNoAllocDet =lineNoAllocDet+1;
							sql ="INSERT INTO INV_ALLOC_DET (TRAN_ID,LINE_NO,SITE_CODE,ITEM_CODE,LOT_NO,LOT_SL,QUANTITY,REMARKS,REAS_CODE, "
								+" LOC_CODE,EXP_LEV,POTENCY_ADJ,ISSUE_QTY,DEALLOC_QTY,ALLOC_QTY,DEALLOCATED )"
								+" values ( ? ,? ,? ,? , ?, "
								+ " ?, ?, ?, ?, ? ,"
								+ " ?, ?, ?, ?, ? ,? )";

							pstmt1 = conn.prepareStatement(sql);
							pstmt1.setString(1,tranIDAllocDet); 
							pstmt1.setInt(2,lineNoAllocDet);
							pstmt1.setString(3,siteCode);
							pstmt1.setString(4,itemCode);
							pstmt1.setString(5,lotNo);
							pstmt1.setString(6,scanLotSl);
							pstmt1.setDouble(7,scanQty);
							pstmt1.setString(8,"Different Lot sl Scanned");				
							pstmt1.setString(9,reasCode); 
							pstmt1.setString(10,locCodeNew);//added on 28/01/2010
							pstmt1.setString(11,expLev); 
							pstmt1.setDouble(12,0); 
							pstmt1.setDouble(13,scanQty); 
							pstmt1.setDouble(14,scanQty); 
							pstmt1.setDouble(15,scanQty);
							pstmt1.setString(16,"N");
							upd = pstmt1.executeUpdate();
							if(upd == 1)
							{
								System.out.println("Record inserted in INV_ALLOC_DET.............:"+upd);
							}
							pstmt1.close();
						}
						//ended on 20/09/09 by rajendra for update inv_alloc_det  part11

					}//else
				}	
			 }
//			 rs.close();
//			 rs = null;
//			 pstmt.close();
//			 pstmt = null ;
		}//end try
		catch(Exception e)
		{
			   retString  = "ERROR";
			   System.out.println("Exception :  "+e);
			   e.printStackTrace();
			   throw  new ITMException(e);
		}
		return retString;
	}
	 */
	private String updatingWorkorderIssuedet2(String tranId,String userId,Document dom,String currDomId,Connection conn,String objName )throws RemoteException,ITMException
	{		
		PreparedStatement pstmt = null,pstmt1 = null;
		ResultSet rs = null,rs1 = null;
		String tranIdIss="",errString="";
		String retString ="",sql="",scanLotSl="";
		String  siteCode="",itemCode="",locCode="",lotNo="",lotSl="";
		String expLev="",calcPotency="",adjPotency="",matchPotency="",unit="";
		String acctCodeWp="", cctrCodeWp="",reasCode="",qcReqd="",stkOpt="";
		String scannedBarCode = null;
		double minPotencyPerc=0,grossRate=0,tareWeight=0,scanQty=0;
		double quantity=0, potencyAdj=0,woIssQty=0,rate=0;
		int noArt=0,upd =0,newLineNo=0;
		HashMap invallocTraceMap = null;
		int lineNoIss=0;
		String lotSlNew="",workOrder="";
		String tranIDAllocDet = "";
		int lineNoAllocDet = 0;
		//HashMap currentMap =new HashMap();
		//HashMap updateMap =new HashMap();
		String xmlValues ="",tranIdG="",remarks = "";

		String scanLocCode ="";
		ITMDBAccessEJB itmDBAccessEJB =null;
		int lineNo = 0,containerCount =0;
		double remIssQty = 0.0,alreadyScannedQty = 0.0;
		double qtyPerArt = 0;
		double updateQuantity = 0;
		// 31/07/10 manoharan 
		double quantityOriginal = 0, potencyAdjOriginal = 0,updQty = 0,updPotencyQty = 0;
		// end 31/07/10 manoharan
		
		int maxLineNo=0,matchLineNo=0; // cpatil 07/07/14
		
		try
		{
			System.out.println("@@@@@@@@@@@@ updatingWorkorderIssuedet2  calling ");
			//ArrayList woiScanAList = new ArrayList(); 
			itmDBAccessEJB = new ITMDBAccessEJB();
			NodeList parentNodeList = null;
			if("w_disp_item_wiz".equals(objName))
			{
				parentNodeList = dom.getElementsByTagName("Detail3");
				System.out.println("Detail3 parentNodeList.getLength()["+parentNodeList.getLength()+"]");
			}
			else
			{
				parentNodeList = dom.getElementsByTagName("Detail2");
				System.out.println("Detail2 parentNodeList.getLength()["+parentNodeList.getLength()+"]");
			}

			System.out.println("currDomId["+currDomId+"]objName["+objName+"]");

			if("w_disp_item_wiz".equals(objName))
			{
				System.out.println("@@@@@@ inside else....");

				sql =" select max(line_no) from workorder_issdet_sl where  tran_id = ? ";
				pstmt1 = conn.prepareStatement(sql);
				pstmt1.setString(1,tranId);
				rs1 = pstmt1.executeQuery();
				if (rs1.next())
				{	
					maxLineNo	= rs1.getInt(1);
				}
				rs1.close();
				rs1 =null;
				pstmt1.close();
				pstmt1 =null;

				for(int i=0; i<parentNodeList.getLength(); i++ )
				{
					Node parentNode = parentNodeList.item(i);
					String domID = ((Element)parentNode).getAttribute("domID");
					System.out.println("domID["+domID+"]]");
					System.out.println("currDomId----------->["+domID+"]");
					NodeList childNodeList = parentNode.getChildNodes();
					for(int j=0;j<childNodeList.getLength();j++)
					{
						Node childNode = childNodeList.item(j);	
						if(childNode.getNodeName().equals("line_no"))
						{
							if(childNode.getFirstChild() != null)
							{
								lineNo = Integer.parseInt(childNode.getFirstChild().getNodeValue()==null?"0":childNode.getFirstChild().getNodeValue().trim());

								if( lineNo == maxLineNo )
								{
									matchLineNo = i;
									break;
								}
							}
						}
					}
				}
				System.out.println("matchLineNo["+matchLineNo+"]]");
				for(int i=matchLineNo; i<parentNodeList.getLength(); i++ )
				{
					Node parentNode = parentNodeList.item(i);
					String domID = ((Element)parentNode).getAttribute("domID");
					System.out.println("domID["+domID+"]]");
					{
						System.out.println("currDomId----------->["+domID+"]");
						NodeList childNodeList = parentNode.getChildNodes();
						for(int j=0;j<childNodeList.getLength();j++)
						{
							Node childNode = childNodeList.item(j);	

							if(childNode.getNodeName().equals("line_no"))
							{
								if(childNode.getFirstChild() != null)
									lineNo = Integer.parseInt(childNode.getFirstChild().getNodeValue()==null?"0":childNode.getFirstChild().getNodeValue().trim());
									//lineNo = Integer.parseInt(childNode.getFirstChild().getNodeValue());
							}
							else if(childNode.getNodeName().equals("tran_id__iss"))
							{
								if(childNode.getFirstChild() != null)
									tranIdIss = childNode.getFirstChild().getNodeValue();
							}
							else if(childNode.getNodeName().equals("line_no__iss"))
							{
								if(childNode.getFirstChild() != null)
									lineNoIss = Integer.parseInt(childNode.getFirstChild().getNodeValue());
							}
							else if(childNode.getNodeName().equals("net_weight"))
							{
								if(childNode.getFirstChild() != null)
									scanQty = Double.parseDouble(childNode.getFirstChild().getNodeValue());
							}
							else if(childNode.getNodeName().equals("lot_sl"))
							{
								if(childNode.getFirstChild() != null)
									scanLotSl = childNode.getFirstChild().getNodeValue();
							}
							else if(childNode.getNodeName().equals("loc_code"))
							{
								if(childNode.getFirstChild() != null)
									scanLocCode = childNode.getFirstChild().getNodeValue();
							}
							else if(childNode.getNodeName().equals("lot_sl__new"))
							{
								if(childNode.getFirstChild() != null)
									lotSlNew = childNode.getFirstChild().getNodeValue();
							}
							else if(childNode.getNodeName().equals("rem_iss_qty"))
							{
								if(childNode.getFirstChild() != null)
									remIssQty = Double.parseDouble(childNode.getFirstChild().getNodeValue());
							}
							else if(childNode.getNodeName().equals("qty_per_articles"))
							{
								if(childNode.getFirstChild() != null)
									qtyPerArt = Double.parseDouble(childNode.getFirstChild().getNodeValue());
							}
						}
					}
				}



				/*	
			    sql =" select max(line_no) from workorder_issdet_sl where  tran_id = ? ";
				pstmt1 = conn.prepareStatement(sql);
				pstmt1.setString(1,tranId);
				rs1 = pstmt1.executeQuery();
				if (rs1.next())
				{	
							lineNo = rs1.getInt("line_no");
							tranIdIss =rs1.getString("tran_id__iss");
							lineNoIss =rs1.getInt("line_no__iss");
							scanQty = rs1.getDouble("net_weight");
							scanLotSl= rs1.getString("lot_sl");
							scanLocCode = rs1.getString("loc_code");
							lotSlNew = rs1.getString("lot_sl__new");
							remIssQty = rs1.getDouble("rem_iss_qty");
							qtyPerArt = rs1.getDouble("qty_per_articles");
				}
				rs1.close();
				rs1 =null;
				pstmt1.close();
				pstmt1 =null;
				 */

			}
			else
			{
				System.out.println("@@@@@@ inside else....");
				for(int i=0; i<parentNodeList.getLength(); i++ )
				{
					Node parentNode = parentNodeList.item(i);
					String domID = ((Element)parentNode).getAttribute("domID");
					System.out.println("domID["+domID+"]]");
					if(currDomId != null && currDomId.equals(domID))
					{
						System.out.println("currDomId----------->["+domID+"]");
						NodeList childNodeList = parentNode.getChildNodes();
						for(int j=0;j<childNodeList.getLength();j++)
						{
							Node childNode = childNodeList.item(j);	
							//System.out.println("child node name --> "+childNode.getNodeName());
							if(childNode.getNodeName().equals("line_no"))
							{
								if(childNode.getFirstChild() != null)
									lineNo = Integer.parseInt(childNode.getFirstChild().getNodeValue());
							}
							else if(childNode.getNodeName().equals("tran_id__iss"))
							{
								if(childNode.getFirstChild() != null)
									tranIdIss = childNode.getFirstChild().getNodeValue();
							}
							else if(childNode.getNodeName().equals("line_no__iss"))
							{
								if(childNode.getFirstChild() != null)
									lineNoIss = Integer.parseInt(childNode.getFirstChild().getNodeValue());
							}
							else if(childNode.getNodeName().equals("net_weight"))
							{
								if(childNode.getFirstChild() != null)
									scanQty = Double.parseDouble(childNode.getFirstChild().getNodeValue());
							}
							else if(childNode.getNodeName().equals("lot_sl"))
							{
								if(childNode.getFirstChild() != null)
									scanLotSl = childNode.getFirstChild().getNodeValue();
							}
							else if(childNode.getNodeName().equals("loc_code"))
							{
								if(childNode.getFirstChild() != null)
									scanLocCode = childNode.getFirstChild().getNodeValue();
							}
							else if(childNode.getNodeName().equals("lot_sl__new"))
							{
								if(childNode.getFirstChild() != null)
									lotSlNew = childNode.getFirstChild().getNodeValue();
							}
							else if(childNode.getNodeName().equals("rem_iss_qty"))
							{
								if(childNode.getFirstChild() != null)
									remIssQty = Double.parseDouble(childNode.getFirstChild().getNodeValue());
							}
							else if(childNode.getNodeName().equals("qty_per_articles"))
							{
								if(childNode.getFirstChild() != null)
									qtyPerArt = Double.parseDouble(childNode.getFirstChild().getNodeValue());
							}
						}
					}
				}

			} //end else
			// 11/08/10 manoharan total quantity to be compared as can be in multiple lots
			System.out.println("11/08/10 updatingWorkorderIssuedet2 scanQty before [" + scanQty + "]");
			genWOIssue.writeLog(this.curTranId, "11/08/10 updatingWorkorderIssuedet2 scanQty before [" + scanQty + "]");
			// 28/05/13 manoharan count the number of distinct container
			//sql =" select sum(net_weight) as scanqty, count(1) count "
			sql =" select sum(net_weight) as scanqty, count(distinct line_no_sl) count "
					+" from workorder_issdet_sl "
					+" where  tran_id = ? "
					+" and tran_id__iss = ? "   
					+" and line_no__iss = ?";
			pstmt1 = conn.prepareStatement(sql);
			pstmt1.setString(1,tranId);
			pstmt1.setString(2,tranIdIss);
			pstmt1.setInt(3,lineNoIss);
			rs1 = pstmt1.executeQuery();
			if (rs1.next())
			{	
				scanQty	= rs1.getDouble("scanqty");
				containerCount	= rs1.getInt("count");
			}
			rs1.close();
			rs1 =null;
			pstmt1.close();
			pstmt1 =null;
			genWOIssue.writeLog(this.curTranId, "28/05/13 updatingWorkorderIssuedet2 scanQty after [" + scanQty + "] containerCount [" + containerCount + "]");
			System.out.println("28/05/13 scanQty after [" + scanQty + "] containerCount [" + containerCount + "]");

			sql =" select h.site_code,d.item_code,d.loc_code,d.lot_no,d.lot_sl , "
					+" d.quantity,case when d.potency_adj is null then 0 else d.potency_adj end as potency_adj,d.unit,d.match_potency,d.min_potency_perc,d.adj_potency, "
					+" d.no_art, d.rate,d.qc_reqd,d.reas_code,d.acct_code__wp,d.cctr_code__wp, "
					+" d.stk_opt,d.gross_rate,d.tare_weight,d.calc_potency,d.exp_lev,h.work_order,"   
					+" ( select scanned_barcode from siteitem where site_code = h.site_code and item_code =d.item_code ) scanned_barcd "
					+" from "					
					+" workorder_iss h, workorder_issdet d where h.tran_id=d.tran_id "
					+" and h.tran_id = ? and d.line_no = ? ";					
			pstmt1 = conn.prepareStatement(sql);
			pstmt1.setString(1,tranIdIss);
			pstmt1.setDouble(2,lineNoIss);
			rs1 = pstmt1.executeQuery();
			if (rs1.next())
			{	
				siteCode					= rs1.getString("site_code");
				itemCode					= rs1.getString("item_code");
				locCode						= rs1.getString("loc_code");
				lotNo						= rs1.getString("lot_no");
				lotSl						= rs1.getString("lot_sl");
				quantity					= rs1.getDouble("quantity");
				potencyAdj					= rs1.getDouble("potency_adj");
				unit						= rs1.getString("unit");
				matchPotency				= rs1.getString("match_potency");
				minPotencyPerc				= rs1.getDouble("min_potency_perc");
				adjPotency					= rs1.getString("adj_potency");
				noArt						= rs1.getInt("no_art");
				rate						= rs1.getDouble("rate");
				qcReqd						= rs1.getString("qc_reqd");
				reasCode					= rs1.getString("reas_code");
				acctCodeWp					= rs1.getString("acct_code__wp");
				cctrCodeWp					= rs1.getString("cctr_code__wp");
				stkOpt						= rs1.getString("stk_opt");
				grossRate					= rs1.getDouble("gross_rate");
				tareWeight					= rs1.getDouble("tare_weight");
				calcPotency					= rs1.getString("calc_potency");
				expLev						= rs1.getString("exp_lev");
				workOrder					= rs1.getString("work_order");
				scannedBarCode				= rs1.getString( "scanned_barcd" );
			}
			rs1.close();
			rs1 =null;
			pstmt1.close();
			pstmt1 =null;
			System.out.println("quantity..........."+quantity);
			System.out.println("potencyAdj..........."+potencyAdj);
			genWOIssue.writeLog(this.curTranId, "28/05/13a updatingWorkorderIssuedet2 scanQty after [" + scanQty + "] containerCount [" + containerCount + "] noArt [" + noArt + "] scannedBarCode [" + scannedBarCode+ "]");
			System.out.println("28/05/13a updatingWorkorderIssuedet2 scanQty after [" + scanQty + "] containerCount [" + containerCount + "] noArt [" + noArt + "] scannedBarCode [" + scannedBarCode+ "]");
			// 31/07/10 manoharan
			quantityOriginal = quantity;
			potencyAdjOriginal = potencyAdj;
			// end 31/07/10 manoharan
			woIssQty = quantity + potencyAdj;

			scanLotSl = scanLotSl == null ? "" : scanLotSl.trim();
			lotSl = lotSl == null ? "" : lotSl.trim();
			scanLocCode = scanLocCode == null ? "" : scanLocCode.trim();
			locCode = locCode == null ? "" : locCode.trim();
			System.out.println("scannedBarCode..........."+scannedBarCode);
			if("2".equals(scannedBarCode))
			{
				xmlValues ="<?xml version=\"1.0\" encoding=\"utf-8\"?><Root>";
				xmlValues = xmlValues + "<Header></Header>";
				xmlValues = xmlValues + "<Detail1>";
				xmlValues = xmlValues +	"<dummy></dummy>";
				xmlValues = xmlValues +	"<site_code>" + siteCode + "</site_code>";
				xmlValues = xmlValues +	"<tran_id></tran_id>";
				xmlValues = xmlValues + "</Detail1></Root>";

				genWOIssue.writeLog(this.curTranId,"11/08/10 woIssQty-->["+woIssQty +"]--scanQty-->["+scanQty+"]");							
				System.out.println("11/08/10 woIssQty-->["+woIssQty +"]--scanQty-->["+scanQty+"]");							
				woIssQty = getRequiredDecimal(woIssQty,3);
				scanQty = getRequiredDecimal(scanQty,3);
				System.out.println("11/08/10 woIssQty-->["+getRequiredDecimal(woIssQty,3) +"]--scanQty-->["+getRequiredDecimal(scanQty,3)+"]");							
				genWOIssue.writeLog(this.curTranId,"11/08/10 woIssQty-->["+getRequiredDecimal(woIssQty,3) +"]--scanQty-->["+getRequiredDecimal(scanQty,3)+"]");							
				if( getRequiredDecimal(woIssQty,3) == getRequiredDecimal(scanQty,3) )
				{
					System.out.println("woIssQty == scanQty");
					System.out.println("scanLotSl-->["+scanLotSl +"]--lotSl-->["+lotSl+"]--scanLocCode-->["+scanLocCode+"]--locCode-->["+locCode+"]");							
					genWOIssue.writeLog(this.curTranId, "In updatingWorkorderIssuedet2 woIssQty == scanQty woIssQty [" + getRequiredDecimal(woIssQty,3) + "] scanQty[" + getRequiredDecimal(scanQty,3)+"] scanLotSl-->["+scanLotSl +"]--lotSl-->["+lotSl+"]--scanLocCode-->["+scanLocCode+"]--locCode-->["+locCode+"]"); // 15/01/11 manoharan write the log
					if(!scanLotSl.equals(lotSl) || !scanLocCode.equals(locCode))
					{								
						genWOIssue.writeLog(this.curTranId, "In updatingWorkorderIssuedet2 [!scanLotSl.equals(lotSl) || !scanLocCode.equals(locCode)] scanLotSl-->["+scanLotSl +"]--lotSl-->["+lotSl+"]--scanLocCode-->["+scanLocCode+"]--locCode-->["+locCode+"]"); // 15/01/11 manoharan write the log
						//FOR LAST RECORD WHEN USER ENTERS DS THEN POST SAVE IS CALLED AGAIN SO IN SUCH CASE
						//CHECK WHETHER THE EXPECTED TRACE IS GENERATED ALREADY. IF GENERATED THEN EXIT
						//FROM POST SAVE.
						// 19/05/10 manoharan consider the line_no of workorder_issdet_sl
						//sql = " SELECT COUNT(1) FROM MFG_LOTSL_TRACE WHERE TRAN_ID__ISS = ? "
						//	+ " AND LINE_NO_ISS__OLD = ? AND QTY_OLD  = ? AND LOC_CODE = ? ";
						//pstmt1 = conn.prepareStatement(sql);
						//pstmt1.setString(1,tranIdIss);
						//pstmt1.setInt(2,lineNoIss);
						//pstmt1.setDouble(3,woIssQty);
						//pstmt1.setString(4,locCode);
						sql = " SELECT COUNT(1) FROM MFG_LOTSL_TRACE WHERE TRAN_ID__ISS = ? "
								+ " AND LINE_NO_ISS__OLD = ? AND QTY_OLD  = ? AND LOC_CODE = ?  AND LINE_NO__SL = ?";
						pstmt1 = conn.prepareStatement(sql);
						pstmt1.setString(1,tranIdIss);
						pstmt1.setInt(2,lineNoIss);
						pstmt1.setDouble(3,woIssQty);
						pstmt1.setString(4,locCode);
						pstmt1.setInt(5,lineNo);
						rs = pstmt1.executeQuery();
						int cnt = 0;
						if(rs.next())
							cnt = rs.getInt(1);
						pstmt1.close();
						pstmt1 = null;
						rs.close();
						rs = null;
						if ( cnt > 0 )
							return "";


						//1. UPDATE THE WORKORDER_ISSDET WITH THIS NEW SCANNED LOT SL						
						//2. UPDATE  INV ALLOC DET , 
						//3. DEALOCATE  THE STOCK FOR OLD LOT SL
						//4. ALLOCATE THE STOCK FOR NEW SCANNED LOT SL
						//5. INSERT IN TO LOT SL TRACE		
						sql = " SELECT LINE_NO,QUANTITY + case when potency_adj is null then 0 else potency_adj end,SCANNED FROM WORKORDER_ISSDET WHERE "
								+ " TRAN_ID = ? AND EXP_LEV = ? AND ITEM_CODE = ? AND LOT_NO = ? "
								+ " AND LOC_CODE = ? AND LOT_SL = ? AND LINE_NO <> ?";
						pstmt1 = conn.prepareStatement(sql);
						pstmt1.setString(1,tranIdIss);
						pstmt1.setString(2,expLev);
						pstmt1.setString(3,itemCode);
						pstmt1.setString(4,lotNo);	
						pstmt1.setString(5,scanLocCode);
						pstmt1.setString(6,scanLotSl);
						pstmt1.setInt(7,lineNoIss);
						rs1 = pstmt1.executeQuery();
						int alreadyExistLineNo = 0;
						double alreadyExistQty = 0;
						String alreadyScanned = null;
						if(rs1.next())
						{
							alreadyExistLineNo = rs1.getInt(1);
							alreadyExistQty = rs1.getDouble(2);
							alreadyScanned = rs1.getString(3) == null ? "N" : rs1.getString(3).trim();
						}
						int minLine = 0,maxLine = 0;
						if(alreadyExistLineNo > 0)
						{
							// 19/05/10 manoharan in case deleting a line which was added after the current line (end) by system
							// because of change in loc_code or lot_sl, quantity to be increased should be already scanned quantity
							// not the scanned quantity
							updateQuantity = 0;
							System.out.println("17/08/10 before minLine ["+minLine+"] maxLine ["+maxLine + "] updateQuantity [" + updateQuantity + "]");
							genWOIssue.writeLog(this.curTranId, "in postsave updatingWorkorderIssuedet2 before minLine ["+minLine+"] maxLine ["+maxLine + "] updateQuantity [" + updateQuantity + "]");
							if(alreadyExistLineNo < lineNoIss)
							{
								minLine = alreadyExistLineNo;
								maxLine = lineNoIss;
								updateQuantity = scanQty ; // 19/05/10 manoharan
							}
							else
							{

								minLine = lineNoIss;
								maxLine = alreadyExistLineNo;
								updateQuantity = alreadyExistQty ; // 19/05/10 manoharan
							}
							System.out.println("17/08/10 after minLine ["+minLine+"] maxLine ["+maxLine + "] updateQuantity [" + updateQuantity +"]");
							genWOIssue.writeLog(this.curTranId, "in postsave updatingWorkorderIssuedet2 after minLine ["+minLine+"] maxLine ["+maxLine + "] updateQuantity [" + updateQuantity + "]");
							// 31/07/10 manoharan to update quantity and potency_adj propotionately
							//sql = " UPDATE WORKORDER_ISSDET SET LOT_SL = ? ,LOC_CODE = ?,QUANTITY  = QUANTITY + ? , SCANNED = ? "+
							//	  " WHERE TRAN_ID = ? AND LINE_NO = ? ";							
							sql = " UPDATE WORKORDER_ISSDET SET LOT_SL = ? ,LOC_CODE = ?,QUANTITY  = QUANTITY + ?, SCANNED = ?, POTENCY_ADJ = nvl(POTENCY_ADJ,0) + ? "+
									" WHERE TRAN_ID = ? AND LINE_NO = ? ";							
							pstmt1 = conn.prepareStatement(sql);
							pstmt1.setString(1,scanLotSl);
							pstmt1.setString(2,scanLocCode);
							//pstmt1.setDouble(3,scanQty); //  19/05/10 manoharan commented
							// 31/07/10 manoharan to update quantity and potency_adj propotionately
							//pstmt1.setDouble(3,updateQuantity); //  19/05/10 manoharan added
							updQty = updateQuantity / (quantityOriginal + potencyAdjOriginal) * quantityOriginal;
							updPotencyQty = updateQuantity / (quantityOriginal + potencyAdjOriginal) * potencyAdjOriginal;
							pstmt1.setDouble(3,getRequiredDecimal(updQty,3));
							// end 31/07/10 manoharan to update quantity and potency_adj propotionately

							pstmt1.setString(4,alreadyScanned);

							// 31/07/10 manoharan to update quantity and potency_adj propotionately
							//pstmt1.setString(5,tranIdIss);
							//pstmt1.setInt(6,minLine);
							pstmt1.setDouble(5,updPotencyQty);							
							pstmt1.setString(6,tranIdIss);
							pstmt1.setInt(7,minLine);	
							// end 31/07/10 manoharan to update quantity and potency_adj propotionately

							upd =	pstmt1.executeUpdate();
							System.out.println("upd......... ["+upd+"]");
							pstmt1.close();

							sql = " DELETE FROM WORKORDER_ISSDET WHERE TRAN_ID = ? AND LINE_NO = ? ";							
							pstmt1 = conn.prepareStatement(sql);									
							pstmt1.setString(1,tranIdIss);
							pstmt1.setInt(2,maxLine);							
							upd =	pstmt1.executeUpdate();
							System.out.println("upd......... ["+upd+"]");
							pstmt1.close();
							// 15/03/11 manoharan delete records from workorder_issdet in case quantity - alloc_qty becomes 0 after update
							sql = " DELETE FROM WORKORDER_ISSDET "+
									" WHERE TRAN_ID = ? AND QUANTITY +  CASE WHEN POTENCY_ADJ IS NULL THEN 0 ELSE POTENCY_ADJ END = 0 ";
							pstmt1 = conn.prepareStatement(sql);
							pstmt1.setString(1,tranIdIss);
							upd =	pstmt1.executeUpdate();
							System.out.println("delete......... ["+upd+"]");
							pstmt1.close();
							//  end 15/03/11 manoharan delete records from workorder_issdet in case quantity - alloc_qty becomes 0 after update

							sql = " UPDATE WORKORDER_ISSDET_SL SET LINE_NO__ISS = ? "+
									" WHERE TRAN_ID = ? AND LINE_NO__ISS = ? ";
							pstmt1 = conn.prepareStatement(sql);
							pstmt1.setInt(1,minLine);
							pstmt1.setString(2,tranId);
							pstmt1.setInt(3,maxLine);
							upd =	pstmt1.executeUpdate();
							System.out.println("upd......... ["+upd+"]");
							pstmt1.close();
						}
						else
						{
							sql = " UPDATE WORKORDER_ISSDET SET LOT_SL = ? ,LOC_CODE = ?,SCANNED = 'Y' "+
									" WHERE TRAN_ID = ? AND LINE_NO = ? ";							
							pstmt1 = conn.prepareStatement(sql);
							pstmt1.setString(1,scanLotSl);
							pstmt1.setString(2,scanLocCode);
							pstmt1.setString(3,tranIdIss);
							pstmt1.setInt(4,lineNoIss);							
							upd =	pstmt1.executeUpdate();
							System.out.println("upd......... ["+upd+"]");
							pstmt1.close();									

						}
						sql = " UPDATE STOCK SET LOT_SL__ORG = ? WHERE SITE_CODE = ? AND ITEM_CODE = ? "+
								" AND LOC_CODE = ? AND LOT_NO = ? AND LOT_SL = ? ";

						pstmt1 = conn.prepareStatement(sql);
						pstmt1.setString(1,lotSl);
						pstmt1.setString(2,siteCode);
						pstmt1.setString(3,itemCode);
						pstmt1.setString(4,scanLocCode);
						pstmt1.setString(5,lotNo);
						pstmt1.setString(6,scanLotSl);							
						upd =	pstmt1.executeUpdate();
						System.out.println("upd......... ["+upd+"]");
						pstmt1.close();

						tranIdG = generateTranId("T_LOTSL_TRACE",xmlValues,conn);//function to generate NEW transaction id
						if(tranIdG == null || tranIdG.trim().length() ==0)
						{
							errString = itmDBAccessEJB.getErrorString("","VTTRANID",userId);
							return errString;
						}	
						System.out.println("tranIdG..............."+tranIdG);

						sql = " INSERT INTO MFG_LOTSL_TRACE (	TRAN_ID ,	TRAN_DATE ,	ITEM_CODE ,	SITE_CODE ,	"+
								" LOC_CODE ,	LOT_NO ,	LOT_SL__OLD ,	LOT_SL__NEW ,LINE_NO_ISS__OLD, "+
								" LINE_NO_ISS__NEW ,QTY_OLD, QTY_NEW,TRAN_ID__ISS,PARENT_WOISS,LOC_CODE__NEW,REMARKS,TRAN_ID__SL,LINE_NO__SL )"+
								" values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1,tranIdG); 
						pstmt.setTimestamp(2,new java.sql.Timestamp(System.currentTimeMillis()));
						pstmt.setString(3,itemCode);
						pstmt.setString(4,siteCode);
						pstmt.setString(5,locCode);
						pstmt.setString(6,lotNo);
						pstmt.setString(7,lotSl);
						pstmt.setString(8,scanLotSl);
						pstmt.setInt(9,lineNoIss);
						pstmt.setInt(10,lineNoIss);
						pstmt.setDouble(11,getRequiredDecimal(scanQty,3));
						//CHANGED BY HKUMAR ON 18-03-10
						//pstmt.setDouble(12,scanQty);
						// 19/05/10 manoharan if another line for the changed lot_sl / loc_code exists
						// update the after quantity with total scanned quantity (updateQuantity +scanQty )
						//pstmt.setDouble(12,0);
						pstmt.setDouble(12,updateQuantity + scanQty );
						pstmt.setString(13,tranIdIss);
						pstmt.setString(14,tranIdIss);
						pstmt.setString(15,scanLocCode);
						pstmt.setString(16,"Location or Lot sl Changed");
						pstmt.setString(17,tranId);
						pstmt.setInt(18,lineNo);
						upd = pstmt.executeUpdate();
						if(upd == 1)
						{
							System.out.println("Record inserted mfg_lotsl_trace:"+upd);
						}
						pstmt.close();
						invallocTraceMap = new HashMap();
						InvAllocTraceBean invallocTraceBean = new InvAllocTraceBean();
						invallocTraceMap.put("ref_ser","D-ALOC");
						invallocTraceMap.put("site_code",siteCode);
						invallocTraceMap.put("item_code",itemCode);
						invallocTraceMap.put("loc_code",locCode);
						invallocTraceMap.put("lot_no",lotNo);
						invallocTraceMap.put("lot_sl",lotSl);
						//   this should be the same quantity
						invallocTraceMap.put("alloc_qty",new Double(-1 * Math.abs(scanQty)));
						System.out.print("DeallocQty = " + (-1*Math.abs(scanQty)));
						invallocTraceMap.put("chg_user",userId);
						invallocTraceMap.put("chg_term",userId);
						invallocTraceMap.put("chg_win","W_WOI_SCAN");
						invallocTraceMap.put("ref_id",tranIdIss);
						invallocTraceMap.put("ref_line",lineNoIss+"");
						errString = invallocTraceBean.updateInvallocTrace(invallocTraceMap,conn);
						genWOIssue.writeLog(this.curTranId, "in postsave updatingWorkorderIssuedet2 minLine ["+minLine+"] maxLine ["+maxLine + "] updateQuantity [" + updateQuantity + "]");
						if (errString != null && errString.trim().length() > 0)
						{
							System.out.println("errString :::"+ errString );
							return errString;
						}

						sql = " SELECT QUANTITY - ALLOC_QTY FROM STOCK WHERE SITE_CODE = ? "
								+ " AND ITEM_CODE = ? AND LOT_NO = ? AND LOC_CODE = ? AND LOT_SL = ?";
						pstmt = conn.prepareStatement(sql);								
						pstmt.setString(1,siteCode);
						pstmt.setString(2,itemCode);
						pstmt.setString(3,lotNo);
						pstmt.setString(4,scanLocCode);
						pstmt.setString(5,scanLotSl);
						rs = pstmt.executeQuery();
						double remStkQty = 0;
						if (rs.next())
						{	
							remStkQty	= rs.getDouble(1);
						}
						rs.close();
						rs =null;
						pstmt.close();
						pstmt =null;
						genWOIssue.writeLog(this.curTranId, "in postsave updatingWorkorderIssuedet2 remStkQty ["+remStkQty+"] >= scanQty ["+scanQty + "]");
						if( remStkQty >= scanQty)
						{
							invallocTraceMap.put("ref_ser","W-ISS");
							invallocTraceMap.put("site_code",siteCode);
							invallocTraceMap.put("item_code",itemCode);
							invallocTraceMap.put("loc_code",scanLocCode);
							invallocTraceMap.put("lot_no",lotNo);
							invallocTraceMap.put("lot_sl",scanLotSl);						
							invallocTraceMap.put("alloc_qty",new Double(scanQty));							
							invallocTraceMap.put("chg_user",userId);
							invallocTraceMap.put("chg_term",userId);
							invallocTraceMap.put("chg_win","W_WOI_SCAN");
							invallocTraceMap.put("ref_id",tranIdIss);
							invallocTraceMap.put("ref_line",lineNoIss+"");
							errString = invallocTraceBean.updateInvallocTrace(invallocTraceMap,conn);
							if (errString != null && errString.trim().length() > 0)
							{
								System.out.println("errString :::"+ errString );
								return errString;
							}
						}
						else if(remStkQty > 0)
						{
							genWOIssue.writeLog(this.curTranId, "in postsave updatingWorkorderIssuedet2 remStkQty > 0 ["+remStkQty+"]");
							invallocTraceMap.put("ref_ser","W-ISS");
							invallocTraceMap.put("site_code",siteCode);
							invallocTraceMap.put("item_code",itemCode);
							invallocTraceMap.put("loc_code",scanLocCode);
							invallocTraceMap.put("lot_no",lotNo);
							invallocTraceMap.put("lot_sl",scanLotSl);						
							invallocTraceMap.put("alloc_qty",new Double(remStkQty));							
							invallocTraceMap.put("chg_user",userId);
							invallocTraceMap.put("chg_term",userId);
							invallocTraceMap.put("chg_win","W_WOI_SCAN");
							invallocTraceMap.put("ref_id",tranIdIss);
							invallocTraceMap.put("ref_line",lineNoIss+"");
							errString = invallocTraceBean.updateInvallocTrace(invallocTraceMap,conn);
							if (errString != null && errString.trim().length() > 0)
							{
								System.out.println("errString :::"+ errString );
								return errString;
							}
							sql = " SELECT DET.TRAN_ID,DET.LINE_NO FROM WORKORDER_ISS HDR ,WORKORDER_ISSDET DET "
									+ " WHERE HDR.TRAN_ID = DET.TRAN_ID AND HDR.SITE_CODE = ? "
									+ " AND DET.ITEM_CODE = ? AND DET.LOT_NO = ? AND DET.LOC_CODE = ? "
									+ " AND DET.LOT_SL = ? AND DET.QUANTITY >= ? "
									+ " AND (DET.SCANNED IS NULL OR DET.SCANNED<>'Y') ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1,siteCode);
							pstmt.setString(2,itemCode);
							pstmt.setString(3,lotNo);
							pstmt.setString(4,scanLocCode);
							pstmt.setString(5,scanLotSl);
							pstmt.setDouble(6,scanQty-remStkQty);
							rs = pstmt.executeQuery();
							String othWOIss = "";
							int othLineNoIss = 0;
							if (rs.next())
							{	
								othWOIss		= rs.getString(1)==null ? "":rs.getString(1);
								othLineNoIss	= rs.getInt(2);
							}
							rs.close();
							rs =null;
							pstmt.close();
							pstmt =null;
							if(othWOIss != null && othWOIss.trim().length()>0)
							{
								genWOIssue.writeLog(this.curTranId, "in postsave othWOIss ["+othWOIss+"]");
								invallocTraceMap.put("ref_ser","D-ALOC");
								invallocTraceMap.put("site_code",siteCode);
								invallocTraceMap.put("item_code",itemCode);
								invallocTraceMap.put("loc_code",locCode);
								invallocTraceMap.put("lot_no",lotNo);
								invallocTraceMap.put("lot_sl",lotSl);
								//   this should be the same quantity
								invallocTraceMap.put("alloc_qty",new Double(-1 * Math.abs(scanQty-remStkQty)));
								System.out.print("DeallocQty = " + (-1*Math.abs(scanQty-remStkQty)));
								invallocTraceMap.put("chg_user",userId);
								invallocTraceMap.put("chg_term",userId);
								invallocTraceMap.put("chg_win","W_WOI_SCAN");
								invallocTraceMap.put("ref_id",othWOIss);
								invallocTraceMap.put("ref_line",othLineNoIss+"");
								genWOIssue.writeLog(this.curTranId, "in postsave dealocate quantity  ["+ (-1 * Math.abs(scanQty-remStkQty))+"]");
								errString = invallocTraceBean.updateInvallocTrace(invallocTraceMap,conn);
								if (errString != null && errString.trim().length() > 0)
								{
									System.out.println("errString :::"+ errString );
									return errString;
								}

								invallocTraceMap.put("ref_ser","W-ISS");
								invallocTraceMap.put("site_code",siteCode);
								invallocTraceMap.put("item_code",itemCode);
								invallocTraceMap.put("loc_code",scanLocCode);
								invallocTraceMap.put("lot_no",lotNo);
								invallocTraceMap.put("lot_sl",scanLotSl);						
								invallocTraceMap.put("alloc_qty",new Double(scanQty-remStkQty));							
								invallocTraceMap.put("chg_user",userId);
								invallocTraceMap.put("chg_term",userId);
								invallocTraceMap.put("chg_win","W_WOI_SCAN");
								invallocTraceMap.put("ref_id",tranIdIss);
								invallocTraceMap.put("ref_line",lineNoIss+"");
								genWOIssue.writeLog(this.curTranId, "in postsave re-allocate quantity  ["+ (scanQty-remStkQty) +"]");
								errString = invallocTraceBean.updateInvallocTrace(invallocTraceMap,conn);
								if (errString != null && errString.trim().length() > 0)
								{
									System.out.println("errString :::"+ errString );
									return errString;
								}
							}
						}
						else
						{
							genWOIssue.writeLog(this.curTranId, "in postsave else remStkQty  ["+ remStkQty +"]");
							sql = " SELECT DET.TRAN_ID,DET.LINE_NO FROM WORKORDER_ISS HDR ,WORKORDER_ISSDET DET "
									+ " WHERE HDR.TRAN_ID = DET.TRAN_ID AND HDR.SITE_CODE = ? "
									+ " AND DET.ITEM_CODE = ? AND DET.LOT_NO = ? AND DET.LOC_CODE = ? "
									+ " AND DET.LOT_SL = ? AND DET.QUANTITY >= ? "
									+ " AND (DET.SCANNED IS NULL OR DET.SCANNED<>'Y') ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1,siteCode);
							pstmt.setString(2,itemCode);
							pstmt.setString(3,lotNo);
							pstmt.setString(4,scanLocCode);
							pstmt.setString(5,scanLotSl);
							pstmt.setDouble(6,scanQty);
							rs = pstmt.executeQuery();
							String othWOIss = "";
							int othLineNoIss = 0;
							if (rs.next())
							{	
								othWOIss		= rs.getString(1)==null ? "":rs.getString(1);
								othLineNoIss	= rs.getInt(2);
							}
							rs.close();
							rs =null;
							pstmt.close();
							pstmt =null;
							if(othWOIss != null && othWOIss.trim().length()>0)
							{
								invallocTraceMap.put("ref_ser","D-ALOC");
								invallocTraceMap.put("site_code",siteCode);
								invallocTraceMap.put("item_code",itemCode);
								invallocTraceMap.put("loc_code",locCode);
								invallocTraceMap.put("lot_no",lotNo);
								invallocTraceMap.put("lot_sl",lotSl);
								//   this should be the same quantity
								invallocTraceMap.put("alloc_qty",new Double(-1 * Math.abs(scanQty-remStkQty)));
								System.out.print("DeallocQty = " + (-1*Math.abs(scanQty-remStkQty)));
								invallocTraceMap.put("chg_user",userId);
								invallocTraceMap.put("chg_term",userId);
								invallocTraceMap.put("chg_win","W_WOI_SCAN");
								invallocTraceMap.put("ref_id",othWOIss);
								invallocTraceMap.put("ref_line",othLineNoIss+"");
								genWOIssue.writeLog(this.curTranId, "in postsave deallocate  quantity ["+ (-1 * Math.abs(scanQty-remStkQty)) +"]");
								errString = invallocTraceBean.updateInvallocTrace(invallocTraceMap,conn);
								if (errString != null && errString.trim().length() > 0)
								{
									System.out.println("errString :::"+ errString );
									return errString;
								}

								invallocTraceMap.put("ref_ser","W-ISS");
								invallocTraceMap.put("site_code",siteCode);
								invallocTraceMap.put("item_code",itemCode);
								invallocTraceMap.put("loc_code",scanLocCode);
								invallocTraceMap.put("lot_no",lotNo);
								invallocTraceMap.put("lot_sl",scanLotSl);						
								invallocTraceMap.put("alloc_qty",new Double(scanQty-remStkQty));							
								invallocTraceMap.put("chg_user",userId);
								invallocTraceMap.put("chg_term",userId);
								invallocTraceMap.put("chg_win","W_WOI_SCAN");
								invallocTraceMap.put("ref_id",tranIdIss);
								invallocTraceMap.put("ref_line",lineNoIss+"");
								genWOIssue.writeLog(this.curTranId, "in postsave re-allocate  quantity ["+ (scanQty-remStkQty) +"]");
								errString = invallocTraceBean.updateInvallocTrace(invallocTraceMap,conn);
								if (errString != null && errString.trim().length() > 0)
								{
									System.out.println("errString :::"+ errString );
									return errString;
								}
							}
						}

						sql =" SELECT MAX(TRAN_ID) FROM INV_ALLOCATE WHERE WORK_ORDER = ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1,workOrder);							
						rs = pstmt.executeQuery();
						if (rs.next())
						{	
							tranIDAllocDet	= rs.getString(1);
						}
						rs.close();
						rs =null;
						pstmt.close();
						pstmt =null;
						// 31/07/10 manoharan to update quantity and potency_adj propotionately
						//sql =" UPDATE INV_ALLOC_DET SET QUANTITY = QUANTITY-?,ISSUE_QTY = ISSUE_QTY-?,DEALLOCATED = 'Y' "
						//	+" WHERE SITE_CODE = ? AND ITEM_CODE = ? " 
						//	+" AND LOT_NO = ?  AND LOT_SL = ? AND LOC_CODE = ? AND CASE WHEN DEALLOCATED IS NULL THEN 'N' ELSE DEALLOCATED END <> 'Y' "
						//	+" AND EXP_LEV= ? AND TRAN_ID = ? ";
						sql =" UPDATE INV_ALLOC_DET SET QUANTITY = QUANTITY-?,ISSUE_QTY = ISSUE_QTY-?,DEALLOCATED = 'Y', "
								+" POTENCY_ADJ = nvl(POTENCY_ADJ,0) - ? "
								+" WHERE SITE_CODE = ? AND ITEM_CODE = ? " 
								+" AND LOT_NO = ?  AND LOT_SL = ? AND LOC_CODE = ? AND CASE WHEN DEALLOCATED IS NULL THEN 'N' ELSE DEALLOCATED END <> 'Y' "
								+" AND EXP_LEV= ? AND TRAN_ID = ? ";

						updQty = scanQty / (quantityOriginal + potencyAdjOriginal) * quantityOriginal;
						updPotencyQty = scanQty / (quantityOriginal + potencyAdjOriginal) * potencyAdjOriginal;
						pstmt = conn.prepareStatement(sql);
						//pstmt.setDouble( 1, scanQty );
						pstmt.setDouble( 1, updQty );
						pstmt.setDouble( 2, scanQty );
						pstmt.setDouble( 3, updPotencyQty );
						pstmt.setString( 4, siteCode );
						pstmt.setString( 5, itemCode );
						pstmt.setString( 6, lotNo );
						pstmt.setString( 7, lotSl );							
						pstmt.setString( 8, locCode );							
						pstmt.setString( 9, expLev );
						pstmt.setString( 10, tranIDAllocDet );
						upd =	pstmt.executeUpdate();
						System.out.println("upd......... ["+upd+"]");
						pstmt.close();

						// 31/07/10 manoharan to update quantity and potency_adj propotionately
						//sql =" update inv_Alloc_det set quantity = quantity+?,issue_qty =issue_qty+?,dealloc_qty=dealloc_qty+?,alloc_qty =alloc_qty+? "
						//	+" where site_code =? and item_code =? " 
						//	+" and lot_no =?  and lot_sl =?   and loc_code =? "
						//	+" and exp_lev=? and tran_id =? ";
						sql =" update inv_Alloc_det set quantity = quantity+?,issue_qty =issue_qty+?, "
								+ " dealloc_qty=dealloc_qty+?,alloc_qty =alloc_qty+?, potency_adj = nvl(potency_adj,0) + ? "
								+" where site_code =? and item_code =? " 
								+" and lot_no =?  and lot_sl =?   and loc_code =? "
								+" and exp_lev=? and tran_id =? ";

						pstmt = conn.prepareStatement(sql);
						pstmt.setDouble( 1, updQty );
						pstmt.setDouble( 2, scanQty );
						pstmt.setDouble( 3, scanQty );
						pstmt.setDouble( 4, scanQty );
						pstmt.setDouble( 5, updPotencyQty );
						pstmt.setString( 6, siteCode );
						pstmt.setString( 7, itemCode );
						pstmt.setString( 8, lotNo );
						pstmt.setString( 9, scanLotSl );
						pstmt.setString( 10, scanLocCode );
						pstmt.setString( 11, expLev );
						pstmt.setString( 12, tranIDAllocDet );
						upd =	pstmt.executeUpdate();
						System.out.println("upd......... ["+upd+"]");
						pstmt.close();

						//if(upd == 0 ) //  09/12/10 manoharan in case of on-demend there is no need to allocate inv_alloc_det
						if(upd == 0 && tranIDAllocDet != null && tranIDAllocDet.trim().length() > 0)
						{
							sql =" SELECT MAX(LINE_NO) FROM INV_ALLOC_DET WHERE  TRAN_ID ='"+tranIDAllocDet+"' ";

							pstmt = conn.prepareStatement(sql);
							rs = pstmt.executeQuery();
							if (rs.next())
							{	
								lineNoAllocDet			= rs.getInt(1);
							}
							rs.close();
							rs =null;
							pstmt.close();
							pstmt =null;
							lineNoAllocDet =lineNoAllocDet+1;
							sql ="INSERT INTO INV_ALLOC_DET (TRAN_ID,LINE_NO,SITE_CODE,ITEM_CODE,LOT_NO,LOT_SL,QUANTITY,REMARKS,REAS_CODE, "
									+" LOC_CODE,EXP_LEV,POTENCY_ADJ,ISSUE_QTY,DEALLOC_QTY,ALLOC_QTY,DEALLOCATED )"
									+" values ( ? ,? ,? ,? , ?, "
									+ " ?, ?, ?, ?, ? ,"
									+ " ?, ?, ?, ?, ? ,? )";

							pstmt1 = conn.prepareStatement(sql);
							pstmt1.setString(1,tranIDAllocDet); 
							pstmt1.setInt(2,lineNoAllocDet);
							pstmt1.setString(3,siteCode);
							pstmt1.setString(4,itemCode);
							pstmt1.setString(5,lotNo);
							pstmt1.setString(6,scanLotSl);
							//pstmt1.setDouble(7,scanQty); // 31/07/10 manoharan

							pstmt1.setDouble(7,getRequiredDecimal(updQty,3)); // 31/07/10 manoharan
							pstmt1.setString(8,"Different Lot sl Scanned");				
							pstmt1.setString(9,reasCode); 
							pstmt1.setString(10,scanLocCode);								
							pstmt1.setString(11,expLev); 
							//pstmt1.setDouble(12,0); // 31/07/10 manoharan
							pstmt1.setDouble(12,getRequiredDecimal(updPotencyQty,3)); // 31/07/10 manoharan
							pstmt1.setDouble(13,getRequiredDecimal(scanQty,3)); 
							pstmt1.setDouble(14,getRequiredDecimal(scanQty,3)); 
							pstmt1.setDouble(15,getRequiredDecimal(scanQty,3));
							pstmt1.setString(16,"N");
							upd = pstmt1.executeUpdate();
							if(upd == 1)
							{
								System.out.println("Record inserted in INV_ALLOC_DET.............:"+upd);
							}
							pstmt1.close();
						}
					}
					else
					{
						sql = " update workorder_issdet set scanned = 'Y' "
								+ "	where tran_id = ? and line_no = ? ";
						pstmt1 = conn.prepareStatement(sql);
						pstmt1.setString(1,tranIdIss);
						pstmt1.setInt(2,lineNoIss);
						System.out.println("Records updated-------->["+pstmt1.executeUpdate()+"]");
					}
				}
				else
				{
					System.out.println("*****QUANTITY NOT EQUALS****");
					System.out.println("scanLotSl-->["+scanLotSl +"]  lotSl-->["+lotSl+"]  scanLocCode-->["+scanLocCode+"]  locCode-->["+locCode+"]");
					genWOIssue.writeLog(this.curTranId, "in postsave QUANTITY NOT EQUALS scanLotSl ["+scanLotSl +"]  lotSl ["+lotSl+"] scanLocCode ["+scanLocCode+"] locCode ["+locCode+"]");
					if(!scanLocCode.equals(locCode))
					{								
						//FOR LAST RECORD WHEN USER ENTERS DS THEN POST SAVE IS CALLED AGAIN SO IN SUCH CASE
						//CHECK WHETHER THE EXPECTED TRACE IS GENERATED ALREADY. IF GENERATED THEN EXIT
						//FROM POST SAVE.
						// 18/05/10 manoharan there is a problem in case of multiple packets
						// as the following sql is not considering the line_no of 
						// workorder_issdet_sl  following code commented and line number
						// of workorder_issdet_sl considered
						//
						//sql = " SELECT COUNT(1) FROM MFG_LOTSL_TRACE WHERE TRAN_ID__ISS = ? "
						//	+ " AND LINE_NO_ISS__OLD = ? AND QTY_OLD  = ? AND QTY_NEW = ? "
						//	+ " AND LOC_CODE = ? AND LOC_CODE__NEW = ? ";
						//pstmt1 = conn.prepareStatement(sql);
						//pstmt1.setString(1,tranIdIss);
						//pstmt1.setInt(2,lineNoIss);
						//pstmt1.setDouble(3,woIssQty + scanQty);
						//pstmt1.setDouble(4,woIssQty);
						//pstmt1.setString(5,locCode);
						//pstmt1.setString(6,scanLocCode);

						sql = " SELECT COUNT(1) FROM MFG_LOTSL_TRACE WHERE TRAN_ID__ISS = ? "
								+ " AND LINE_NO_ISS__OLD = ? AND QTY_OLD  = ? AND QTY_NEW = ? "
								+ " AND LOC_CODE = ? AND LOC_CODE__NEW = ? AND LINE_NO__SL = ?";
						pstmt1 = conn.prepareStatement(sql);
						pstmt1.setString(1,tranIdIss);
						pstmt1.setInt(2,lineNoIss);
						pstmt1.setDouble(3,woIssQty + scanQty);
						pstmt1.setDouble(4,woIssQty);
						pstmt1.setString(5,locCode);
						pstmt1.setString(6,scanLocCode);
						pstmt1.setInt(7,lineNo);
						// 18/05/10 manoharan

						rs = pstmt1.executeQuery();
						int cnt = 0;
						if(rs.next())
							cnt = rs.getInt(1);
						pstmt1.close();
						pstmt1 = null;
						rs.close();
						rs = null;
						if ( cnt > 0 )
							return "";


						// IF SCAN LOC_CODE AND ORIGINAL LOC CODE DIFFERES THEN 
						//	1. UPDATE WORKORDER ISS DET WITH ORIGINAL QTY - SCANNED QTY.
						//	2. DEALLOCATE SCANNED QTY FROM STOCK FOR OLD LOC CODE.
						//	3. DEDUCT FROM THE INV ALLOC DET WITH ORIGINAL QTY - SCANNED QTY.							
						//	4. INSERT A RECORD INTO WORK ORDER ISS DET WITH THE SCANNED QTY AND NEW LOC
						//	5. ALLOCATE FROM STOCK FOR THIS NEW LOC CODE.
						//	6. INSERT INTO INV ALLOC DET FOR THE NEW OR SCANNED LOC CODE.	


						sql =" SELECT SUM(NET_WEIGHT) FROM WORKORDER_ISSDET_SL WHERE TRAN_ID__ISS = ? AND LINE_NO__ISS = ? "
								+" AND LINE_NO <> ? ";
						pstmt1 = conn.prepareStatement(sql);
						pstmt1.setString(1,tranIdIss);
						pstmt1.setInt(2,lineNoIss);
						pstmt1.setInt(3,lineNo);
						rs1 = pstmt1.executeQuery();
						if (rs1.next())
							alreadyScannedQty = rs1.getDouble(1) ;
						rs1.close();
						rs1 =null;
						pstmt1.close();
						pstmt1 =null;
						System.out.println("alreadyScannedQty-------->"+alreadyScannedQty);
						remIssQty = woIssQty - alreadyScannedQty;		
						//	31/07/10 manoharan to update both quantity and potency_adj
						if(remIssQty == scanQty)
						{
							sql = " UPDATE WORKORDER_ISSDET SET QUANTITY = QUANTITY - ?,POTENCY_ADJ = nvl(POTENCY_ADJ,0) - ?, SCANNED = 'Y' "
									+ " WHERE TRAN_ID = ? AND LINE_NO = ? ";							
						}
						else
						{
							sql = " UPDATE WORKORDER_ISSDET SET QUANTITY = QUANTITY - ?,POTENCY_ADJ = nvl(POTENCY_ADJ,0) - ?, SCANNED = 'N' "
									+ " WHERE TRAN_ID = ? AND LINE_NO = ? ";							
						}
						updQty = scanQty / (quantityOriginal + potencyAdjOriginal) * quantityOriginal;
						updPotencyQty = scanQty / (quantityOriginal + potencyAdjOriginal) * potencyAdjOriginal;

						pstmt1 = conn.prepareStatement(sql);
						//pstmt1.setDouble(1,scanQty);		// 31/07/10 manoharan						
						pstmt1.setDouble(1,getRequiredDecimal(updQty,3));	// 31/07/10 manoharan							
						pstmt1.setDouble(2,getRequiredDecimal(updPotencyQty,3));	// 31/07/10 manoharan							
						pstmt1.setString(3,tranIdIss);
						pstmt1.setInt(4,lineNoIss);							
						upd =	pstmt1.executeUpdate();
						System.out.println("upd......... ["+upd+"]");
						pstmt1.close();
						// 15/03/11 manoharan delete records from workorder_issdet in case quantity - alloc_qty becomes 0 after update
						sql = " DELETE FROM WORKORDER_ISSDET "+
								" WHERE TRAN_ID = ? AND QUANTITY +  CASE WHEN POTENCY_ADJ IS NULL THEN 0 ELSE POTENCY_ADJ END = 0 ";
						pstmt1 = conn.prepareStatement(sql);
						pstmt1.setString(1,tranIdIss);
						upd =	pstmt1.executeUpdate();
						System.out.println("delete......... ["+upd+"]");
						pstmt1.close();
						//  end 15/03/11 manoharan delete records from workorder_issdet in case quantity - alloc_qty becomes 0 after update

						//UPDATE THE WORKORDER_ISSDET_SL LINE NO ISSUE WITH NEW LINE NO
						/*sql = " UPDATE WORKORDER_ISSDET_SL SET LINE_NO__ISS = ? "
							 +" WHERE TRAN_ID = ? AND LINE_NO = ? ";							
						pstmt1 = conn.prepareStatement(sql);
						pstmt1.setInt(1,newLineNo);	
						pstmt1.setString(2,tranId);
						pstmt1.setInt(3,lineNo);							
						upd =	pstmt1.executeUpdate();
						System.out.println("upd......... ["+upd+"]");
						pstmt1.close();*/

						// 21/05/10 manoharan check with count to find whether there is a record in workorder_issdet
						// for the different lot_sl scanned, if exists update else insert
						sql = " SELECT COUNT(1) AS COUNT FROM WORKORDER_ISSDET "
								+ " WHERE TRAN_ID = ? AND LINE_NO <> ? AND ITEM_CODE = ? AND LOT_NO = ? AND LOC_CODE = ? "
								+ " AND LOT_SL = ? AND EXP_LEV = ? ";
						pstmt1 = conn.prepareStatement(sql);
						pstmt1.setString(1,tranIdIss);
						pstmt1.setInt(2,lineNoIss);
						pstmt1.setString(3,itemCode);
						pstmt1.setString(4,lotNo);
						pstmt1.setString(5,scanLocCode);
						pstmt1.setString(6,scanLotSl);								
						pstmt1.setString(7,expLev);
						rs1 = pstmt1.executeQuery();
						int count = 0;
						if (rs1.next())
						{	
							count = rs1.getInt(1) ;
						}
						rs1.close();
						rs1 =null;
						pstmt1.close();
						pstmt1 =null;
						if (count == 1)
						{
							//UPDATE THE WORK ORDER ISSUE WITH NEW QUANTITY.								
							// 31/07/10 manoharan to update quantity as well as potency_adj
							sql = " UPDATE WORKORDER_ISSDET SET QUANTITY = QUANTITY + ?, POTENCY_ADJ = nvl(POTENCY_ADJ,0) + ?, "
									+ " NO_ART = NO_ART + ?,"
									+ " NET_WEIGHT  = NET_WEIGHT + ? , GROSS_WEIGHT = GROSS_WEIGHT + ? "
									+ " WHERE TRAN_ID = ? AND ITEM_CODE = ? AND LOT_NO = ? AND LOC_CODE = ? "
									+ " AND LOT_SL = ? AND EXP_LEV = ? ";

							updQty = scanQty / (quantityOriginal + potencyAdjOriginal) * quantityOriginal;
							updPotencyQty = scanQty / (quantityOriginal + potencyAdjOriginal) * potencyAdjOriginal;

							pstmt1 = conn.prepareStatement(sql);
							//pstmt1.setDouble(1,scanQty); // 31/07/10 manoharan
							pstmt1.setDouble(1,getRequiredDecimal(updQty,3)); // 31/07/10 manoharan
							pstmt1.setDouble(2,getRequiredDecimal(updPotencyQty,3)); // 31/07/10 manoharan
							if(scanQty <= qtyPerArt)
								noArt = 1;
							else
							{									
								double temp = scanQty / qtyPerArt;
								if(temp == (int)temp)
									noArt = (int)temp;
								else
									noArt = (int)temp + 1;
							}
							noArt = 0; // 18/08/10 not to change no_art manoharan/tarun
							pstmt1.setInt(3,noArt);
							pstmt1.setDouble(4,scanQty);
							pstmt1.setDouble(5,scanQty);
							pstmt1.setString(6,tranIdIss);
							pstmt1.setString(7,itemCode);
							pstmt1.setString(8,lotNo);
							pstmt1.setString(9,scanLocCode);
							pstmt1.setString(10,scanLotSl);								
							pstmt1.setString(11,expLev);
							upd = pstmt1.executeUpdate();
							// 15/03/11 manoharan delete records from workorder_issdet in case quantity - alloc_qty becomes 0 after update
							sql = " DELETE FROM WORKORDER_ISSDET "+
									" WHERE TRAN_ID = ? AND QUANTITY +  CASE WHEN POTENCY_ADJ IS NULL THEN 0 ELSE POTENCY_ADJ END = 0 ";
							pstmt1 = conn.prepareStatement(sql);
							pstmt1.setString(1,tranIdIss);
							upd =	pstmt1.executeUpdate();
							System.out.println("delete......... ["+upd+"]");
							pstmt1.close();
							// end 15/03/11 manoharan delete records from workorder_issdet in case quantity - alloc_qty becomes 0 after update
							//if(upd == 1) // 21/05/10 manoharan as per the count not update
							//{
							//UPDATE THE WORKORDER_ISSDET_SL LINE_NO__ISS
							sql = " UPDATE WORKORDER_ISSDET_SL SET LINE_NO__ISS = "+
									" ( SELECT ISS.LINE_NO FROM WORKORDER_ISSDET ISS WHERE ISS.TRAN_ID = ? "+
									"   AND ISS.ITEM_CODE = ? AND ISS.LOT_NO = ? AND ISS.LOC_CODE = ? AND ISS.LOT_SL = ? AND EXP_LEV = ? ) "+
									" WHERE TRAN_ID = ? AND LINE_NO = ?";
							pstmt1 = conn.prepareStatement(sql);									
							pstmt1.setString(1,tranIdIss);
							pstmt1.setString(2,itemCode);
							pstmt1.setString(3,lotNo);
							pstmt1.setString(4,scanLocCode);
							pstmt1.setString(5,scanLotSl);
							pstmt1.setString(6,expLev);
							pstmt1.setString(7,tranId);
							pstmt1.setInt(8,lineNo);									
							upd =	pstmt1.executeUpdate();
							System.out.println("upd......... ["+upd+"]");
							pstmt1.close();

							sql =" SELECT LINE_NO__ISS  FROM  WORKORDER_ISSDET_SL  WHERE  TRAN_ID = ? AND LINE_NO = ?";
							pstmt1 = conn.prepareStatement(sql);
							pstmt1.setString(1,tranId);
							pstmt1.setInt(2,lineNo);
							rs1 = pstmt1.executeQuery();
							if (rs1.next())
							{	
								newLineNo = rs1.getInt(1) ;
							}
							rs1.close();
							rs1 =null;
							pstmt1.close();
							pstmt1 =null;
							remarks = "ROW UPDATED BECAUSE OF LOC_CODE CHANGED";
						}
						else if (count > 1) // 21/05/10 manoharan added this else if
						{
							errString = "ERROR";
							return errString;
						}
						else //if(upd == 0)
						{
							sql =" SELECT MAX(LINE_NO)  FROM  WORKORDER_ISSDET  WHERE  TRAN_ID = ? ";
							pstmt1 = conn.prepareStatement(sql);
							pstmt1.setString(1,tranIdIss);
							rs1 = pstmt1.executeQuery();
							if (rs1.next())
							{	
								newLineNo = rs1.getInt(1) + 1;
							}
							rs1.close();
							rs1 =null;
							pstmt1.close();
							pstmt1 =null;

							//UPDATE THE WORKORDER_ISSDET_SL LINE_NO__ISS							
							sql = " UPDATE WORKORDER_ISSDET_SL SET LINE_NO__ISS = ? "+
									" WHERE TRAN_ID = ? AND LINE_NO = ? ";
							pstmt1 = conn.prepareStatement(sql);
							pstmt1.setInt(1,newLineNo);
							pstmt1.setString(2,tranId);
							pstmt1.setInt(3,lineNo);	
							upd =	pstmt1.executeUpdate();
							System.out.println("upd......... ["+upd+"]");
							pstmt1.close();

							//INSERT A ROW WITH THE NEW LINE NO WHICH WAS RECENTLY SCANNED.
							sql = " INSERT INTO WORKORDER_ISSDET (TRAN_ID, LINE_NO, EXP_LEV,ITEM_CODE, LOC_CODE,"+
									"	LOT_NO, LOT_SL, QUANTITY,NO_ART,UNIT, REAS_CODE, UNIT__ALT,CCTR_CODE__WP,"+
									" ACCT_CODE__WP, RATE, GROSS_RATE, STK_OPT, NET_WEIGHT, GROSS_WEIGHT,"+
									"	TARE_WEIGHT,CALC_POTENCY, POTENCY_ADJ,MATCH_POTENCY,MIN_POTENCY_PERC,"+
									" ADJ_POTENCY,QC_REQD,SCANNED) "
									+" VALUES ( ? ,? ,? ,? , ?, "
									+ " ?, ?, ?, ?, ? ,"
									+ " ?, ?, ?, ?, ? ,"
									+ " ?, ?, ?, ?, ? ,? ,? ,?,?,?,?,'Y')";

							updQty = scanQty / (quantityOriginal + potencyAdjOriginal) * quantityOriginal;
							updPotencyQty = scanQty / (quantityOriginal + potencyAdjOriginal) * potencyAdjOriginal;

							pstmt1 = conn.prepareStatement(sql);
							pstmt1.setString(1,tranIdIss); 
							pstmt1.setInt(2,newLineNo);								
							pstmt1.setString(3,expLev);
							pstmt1.setString(4,itemCode);
							pstmt1.setString(5,scanLocCode);
							pstmt1.setString(6,lotNo);
							pstmt1.setString(7,scanLotSl);
							//pstmt1.setDouble(8,scanQty);	// 31/07/10 manoharan
							pstmt1.setDouble(8,getRequiredDecimal(updQty,3));	// 31/07/10 manoharan
							if(scanQty <= qtyPerArt)
								noArt = 1;
							else
							{									
								double temp = scanQty / qtyPerArt;
								if(temp == (int)temp)
									noArt = (int)temp;
								else
									noArt = (int)temp + 1;
							}
							pstmt1.setInt(9,noArt);
							pstmt1.setString(10,unit);
							pstmt1.setString(11,reasCode); 
							pstmt1.setString(12,""); 
							pstmt1.setString(13,cctrCodeWp); 
							pstmt1.setString(14,acctCodeWp); 
							pstmt1.setDouble(15,rate);
							pstmt1.setDouble(16,grossRate);
							pstmt1.setString(17,stkOpt);
							pstmt1.setDouble(18,scanQty);
							pstmt1.setDouble(19,scanQty);				
							pstmt1.setDouble(20,0);
							pstmt1.setString(21,calcPotency);
							//pstmt1.setDouble(22,0);	  // 31/07/10 manoharan
							pstmt1.setDouble(22,updPotencyQty);	  // 31/07/10 manoharan
							pstmt1.setString(23,matchPotency);
							pstmt1.setDouble(24,minPotencyPerc);
							pstmt1.setString(25,adjPotency); 
							pstmt1.setString(26,qcReqd);

							upd = pstmt1.executeUpdate();
							if(upd == 1)
							{
								System.out.println("Record inserted:"+upd);
							}
							remarks = "NEW ROW ADDED BECAUSE OF LOC_CODE CHANGED";							
						}
						if(pstmt1 != null)
						{
							pstmt1.close();
							pstmt1 = null;
						}
						tranIdG = generateTranId("T_LOTSL_TRACE",xmlValues,conn);//function to generate NEW transaction id
						if(tranIdG == null || tranIdG.trim().length() ==0)
						{
							errString = itmDBAccessEJB.getErrorString("","VTTRANID",userId);
							return errString;
						}	
						System.out.println("tranIdG.....1.........."+tranIdG);

						sql = " INSERT INTO MFG_LOTSL_TRACE (	TRAN_ID ,	TRAN_DATE ,	ITEM_CODE ,	SITE_CODE ,	"+
								" LOC_CODE ,	LOT_NO ,	LOT_SL__OLD ,	LOT_SL__NEW ,LINE_NO_ISS__OLD, "+
								" LINE_NO_ISS__NEW ,QTY_OLD, QTY_NEW,TRAN_ID__ISS,PARENT_WOISS,LOC_CODE__NEW,REMARKS,TRAN_ID__SL,LINE_NO__SL )"+
								" values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
						pstmt = conn.prepareStatement(sql);

						pstmt.setString(1,tranIdG); 
						pstmt.setTimestamp(2,new java.sql.Timestamp(System.currentTimeMillis()));
						pstmt.setString(3,itemCode);
						pstmt.setString(4,siteCode);
						pstmt.setString(5,locCode);
						pstmt.setString(6,lotNo);
						pstmt.setString(7,lotSl);
						pstmt.setString(8,scanLotSl);
						pstmt.setInt(9,lineNoIss);
						pstmt.setInt(10,newLineNo);
						pstmt.setDouble(11,getRequiredDecimal(woIssQty,3));
						pstmt.setDouble(12,getRequiredDecimal(woIssQty - scanQty,3));
						pstmt.setString(13,tranIdIss);
						pstmt.setString(14,tranIdIss);
						pstmt.setString(15,scanLocCode);
						pstmt.setString(16,remarks);
						pstmt.setString(17,tranId);
						pstmt.setInt(18,lineNo);
						upd = pstmt.executeUpdate();								
						if(upd == 1)
						{
							System.out.println("Record inserted mfg_lotsl_trace:"+upd);
						}
						pstmt.close();

						invallocTraceMap = new HashMap();
						InvAllocTraceBean invallocTraceBean = new InvAllocTraceBean();
						invallocTraceMap.put("ref_ser","D-ALOC");
						invallocTraceMap.put("site_code",siteCode);
						invallocTraceMap.put("item_code",itemCode);
						invallocTraceMap.put("loc_code",locCode);
						invallocTraceMap.put("lot_no",lotNo);
						invallocTraceMap.put("lot_sl",lotSl);
						//this should be the same quantity
						invallocTraceMap.put("alloc_qty",new Double(-1 * scanQty));
						System.out.print("DeallocQty = " + (new Double( -1 * scanQty)));
						invallocTraceMap.put("chg_user",userId);
						invallocTraceMap.put("chg_term",userId);
						invallocTraceMap.put("chg_win","W_WOI_SCAN");
						invallocTraceMap.put("ref_id",tranIdIss);
						invallocTraceMap.put("ref_line",lineNoIss+"");
						genWOIssue.writeLog(this.curTranId, "in postsave deallocate scanQty ["+(-1 * scanQty) +"]");
						errString = invallocTraceBean.updateInvallocTrace(invallocTraceMap,conn);
						if (errString != null && errString.trim().length() > 0)
						{
							System.out.println("errString :::"+ errString );
							return errString;
						}

						/*invallocTraceMap = new HashMap();								
						invallocTraceMap.put("ref_ser","D-ALOC");
						invallocTraceMap.put("site_code",siteCode);
						invallocTraceMap.put("item_code",itemCode);
						invallocTraceMap.put("loc_code",scanLocCode);
						invallocTraceMap.put("lot_no",lotNo);
						invallocTraceMap.put("lot_sl",scanLotSl);
						//   this should be the same quantity
						invallocTraceMap.put("alloc_qty",new Double(-1 * scanQty));
						System.out.print("DeallocQty = " + (new Double( -1 * scanQty)));
						invallocTraceMap.put("chg_user",userId);
						invallocTraceMap.put("chg_term",userId);
						invallocTraceMap.put("chg_win","W_WOI_SCAN");
						invallocTraceMap.put("ref_id",tranIdIss);
						invallocTraceMap.put("ref_line",lineNoIss+"");
						errString = invallocTraceBean.updateInvallocTrace(invallocTraceMap,conn);
						if (errString != null && errString.trim().length() > 0)
						{
								System.out.println("errString :::"+ errString );
								return errString;
						}

						invallocTraceMap = new HashMap();								
						invallocTraceMap.put("ref_ser","W-ISS");
						invallocTraceMap.put("site_code",siteCode);
						invallocTraceMap.put("item_code",itemCode);
						invallocTraceMap.put("loc_code",scanLocCode);
						invallocTraceMap.put("lot_no",lotNo);
						invallocTraceMap.put("lot_sl",scanLotSl);						
						invallocTraceMap.put("alloc_qty",new Double(scanQty));							
						invallocTraceMap.put("chg_user",userId);
						invallocTraceMap.put("chg_term",userId);
						invallocTraceMap.put("chg_win","W_WOI_SCAN");
						invallocTraceMap.put("ref_id",tranIdIss);
						invallocTraceMap.put("ref_line",lineNoIss+"");
						errString = invallocTraceBean.updateInvallocTrace(invallocTraceMap,conn);
						if (errString != null && errString.trim().length() > 0)
						{
								System.out.println("errString :::"+ errString );
								return errString;
						}*/


						sql =" SELECT MAX(TRAN_ID) FROM INV_ALLOCATE WHERE WORK_ORDER = ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1,workOrder);							
						rs = pstmt.executeQuery();
						if (rs.next())
						{	
							tranIDAllocDet	= rs.getString(1);
						}
						rs.close();
						rs =null;
						pstmt.close();
						pstmt =null;
						// 31/07/10 manoharan to update quantity and potency_adj
						sql =" UPDATE INV_ALLOC_DET SET QUANTITY = QUANTITY - ?,POTENCY_ADJ = nvl(POTENCY_ADJ,0) - ?,ISSUE_QTY = ISSUE_QTY - ? "
								+" WHERE SITE_CODE = ? AND ITEM_CODE = ? " 
								+" AND LOT_NO = ?  AND LOT_SL = ? AND LOC_CODE = ? AND CASE WHEN DEALLOCATED IS NULL THEN 'N' ELSE DEALLOCATED END <> 'Y' "
								+" AND EXP_LEV= ? AND TRAN_ID = ? ";

						updQty = scanQty / (quantityOriginal + potencyAdjOriginal) * quantityOriginal;
						updPotencyQty = scanQty / (quantityOriginal + potencyAdjOriginal) * potencyAdjOriginal;

						pstmt = conn.prepareStatement(sql);
						//pstmt.setDouble( 1, scanQty ); // 31/07/10 manoharan
						pstmt.setDouble( 1, updQty ); // 31/07/10 manoharan
						pstmt.setDouble( 2, updPotencyQty ); // 31/07/10 manoharan
						pstmt.setDouble( 3, scanQty );
						pstmt.setString( 4, siteCode );
						pstmt.setString( 5, itemCode );
						pstmt.setString( 6, lotNo );
						pstmt.setString( 7, lotSl );							
						pstmt.setString( 8, locCode );							
						pstmt.setString( 9, expLev );
						pstmt.setString( 10, tranIDAllocDet );
						upd =	pstmt.executeUpdate();
						System.out.println("upd......... ["+upd+"]");
						pstmt.close();

						sql =" UPDATE INV_ALLOC_DET SET QUANTITY = QUANTITY+?,POTENCY_ADJ = nvl(POTENCY_ADJ,0)+?,ISSUE_QTY =ISSUE_QTY+?,DEALLOC_QTY=DEALLOC_QTY+?,ALLOC_QTY =ALLOC_QTY+? "
								+" WHERE SITE_CODE = ? AND ITEM_CODE = ? " 
								+" AND LOT_NO = ?  AND LOT_SL = ?   AND LOC_CODE = ? "
								+" AND EXP_LEV= ? AND TRAN_ID = ? ";
						pstmt = conn.prepareStatement(sql);
						//pstmt.setDouble( 1, scanQty ); // 31/07/10 manoharan
						pstmt.setDouble( 1, updQty ); // 31/07/10 manoharan
						pstmt.setDouble( 2, updPotencyQty ); // 31/07/10 manoharan
						pstmt.setDouble( 3, scanQty );
						pstmt.setDouble( 4, scanQty );
						pstmt.setDouble( 5, scanQty );
						pstmt.setString( 6, siteCode );
						pstmt.setString( 7, itemCode );
						pstmt.setString( 8, lotNo );
						pstmt.setString( 9, scanLotSl );
						pstmt.setString( 10, scanLocCode );
						pstmt.setString( 11, expLev );
						pstmt.setString( 12, tranIDAllocDet );
						upd =	pstmt.executeUpdate();
						System.out.println("upd......... ["+upd+"]");
						pstmt.close();

						//if(upd == 0 ) //  09/12/10 manoharan in case of on-demend there is no need to allocate inv_alloc_det
						if(upd == 0 && tranIDAllocDet != null && tranIDAllocDet.trim().length() > 0)
						{
							sql =" SELECT MAX(LINE_NO) FROM INV_ALLOC_DET WHERE  TRAN_ID = ? ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1,tranIDAllocDet);
							rs = pstmt.executeQuery();
							if (rs.next())
							{	
								lineNoAllocDet			= rs.getInt(1);
							}
							rs.close();
							rs =null;
							pstmt.close();
							pstmt =null;
							lineNoAllocDet =lineNoAllocDet+1;
							sql = " INSERT INTO INV_ALLOC_DET (TRAN_ID,LINE_NO,SITE_CODE,ITEM_CODE,LOT_NO,LOT_SL,"
									+ " QUANTITY,REMARKS,REAS_CODE,LOC_CODE,EXP_LEV,POTENCY_ADJ,ISSUE_QTY,DEALLOC_QTY,"
									+ " ALLOC_QTY,DEALLOCATED )"
									+ " values ( ? ,? ,? ,? , ?,  ?, ?, ?, ?, ? , ?, ?, ?, ?, ? ,? )";

							pstmt1 = conn.prepareStatement(sql);
							pstmt1.setString(1,tranIDAllocDet); 
							pstmt1.setInt(2,lineNoAllocDet);
							pstmt1.setString(3,siteCode);
							pstmt1.setString(4,itemCode);
							pstmt1.setString(5,lotNo);
							pstmt1.setString(6,scanLotSl);
							//pstmt1.setDouble(7,scanQty); // 31/07/10 manoharan
							pstmt1.setDouble(7,getRequiredDecimal(updQty,3)); // 31/07/10 manoharan
							pstmt1.setString(8,"Different Lot sl Scanned");				
							pstmt1.setString(9,reasCode); 
							pstmt1.setString(10,scanLocCode);								
							pstmt1.setString(11,expLev); 
							//pstmt1.setDouble(12,0);  // 31/07/10 manoharan
							pstmt1.setDouble(12,getRequiredDecimal(updPotencyQty,3));  // 31/07/10 manoharan
							pstmt1.setDouble(13,getRequiredDecimal(scanQty,3)); 
							pstmt1.setDouble(14,getRequiredDecimal(scanQty,3)); 
							pstmt1.setDouble(15,getRequiredDecimal(scanQty,3));
							pstmt1.setString(16,"N");
							upd = pstmt1.executeUpdate();
							if(upd == 1)
							{
								System.out.println("Record inserted in INV_ALLOC_DET.............:"+upd);
							}
							pstmt1.close();
						}
					}
					else if(!scanLotSl.equals(lotSl))
					{
						//FOR LAST RECORD WHEN USER ENTERS DS THEN POST SAVE IS CALLED AGAIN SO IN SUCH CASE
						//CHECK WHETHER THE EXPECTED TRACE IS GENERATED ALREADY. IF GENERATED THEN EXIT
						//FROM POST SAVE.
						// 18/05/10 manoharan there is a problem in case of multiple packets
						// as the following sql is not considering the line_no of 
						// workorder_issdet_sl  following code commented and line number
						// of workorder_issdet_sl considered
						//sql = " SELECT COUNT(1) FROM MFG_LOTSL_TRACE WHERE TRAN_ID__ISS = ? "
						//	+ " AND LINE_NO_ISS__OLD = ? AND QTY_OLD  = ? AND QTY_NEW = ? "
						//	+ " AND LOT_SL__OLD = ? AND LOT_SL__NEW = ? ";
						//pstmt1 = conn.prepareStatement(sql);
						//pstmt1.setString(1,tranIdIss);
						//pstmt1.setInt(2,lineNoIss);
						//pstmt1.setDouble(3,woIssQty + scanQty );
						//pstmt1.setDouble(4,woIssQty);
						//pstmt1.setString(5,lotSl);
						//pstmt1.setString(6,scanLotSl);

						sql = " SELECT COUNT(1) FROM MFG_LOTSL_TRACE WHERE TRAN_ID__ISS = ? "
								+ " AND LINE_NO_ISS__OLD = ? AND QTY_OLD  = ? AND QTY_NEW = ? "
								+ " AND LOT_SL__OLD = ? AND LOT_SL__NEW = ? AND LINE_NO__SL = ?";
						pstmt1 = conn.prepareStatement(sql);
						pstmt1.setString(1,tranIdIss);
						pstmt1.setInt(2,lineNoIss);
						pstmt1.setDouble(3,woIssQty + scanQty );
						pstmt1.setDouble(4,woIssQty);
						pstmt1.setString(5,lotSl);
						pstmt1.setString(6,scanLotSl);
						pstmt1.setInt(7,lineNo);

						rs = pstmt1.executeQuery();
						int cnt = 0;
						if(rs.next())
							cnt = rs.getInt(1);
						pstmt1.close();
						pstmt1 = null;
						rs.close();
						rs = null;
						if ( cnt > 0 )
							return "";

						// IF SCAN LOT SL AND ORIGINAL LOT SL DIFFERES THEN 
						//	1.  UPDATE THE WORK ORDER ISS DET WITH SCAN QTY AND SCANNED LOT SL
						//	2. INSERT A RECORD AT THE END OF THE ISS DET FOR ORG QTY - SCANNED QTY AND OLD LOT SL
						double stkQty=0.0;
						String stockQty = "SELECT quantity  "
								+"	FROM  stock  "
								+"  WHERE item_code = ? "
								+"	and  site_code = ? "
								+"	and  lot_no = ? "
								+"	and  lot_sl = ? "
								+"	and  loc_code = ? ";
						pstmt1 = conn.prepareStatement( stockQty );
						pstmt1.setString( 1, itemCode );
						pstmt1.setString( 2, siteCode );
						pstmt1.setString( 3, lotNo );
						pstmt1.setString( 4, scanLotSl );
						pstmt1.setString( 5, locCode );
						rs1 = pstmt1.executeQuery();
						if( rs1.next() )
						{
							stkQty = rs1.getDouble( 1 );
						}
						rs1.close();
						rs1 =null;
						pstmt1.close();
						pstmt1 =null;
						System.out.println("stkQty..........."+stkQty);
						if(stkQty == 0.0)
						{
							scanLotSl = lotSlNew;
						}


						//STEP 1. UPDATE THE QUANTITY.								
						//sql = " UPDATE WORKORDER_ISSDET SET QUANTITY = ? ,POTENCY_ADJ = 0,SCANNED = 'N' "+
						//	  " WHERE TRAN_ID = ? AND LINE_NO = ? ";							

						sql =" SELECT SUM(NET_WEIGHT) FROM WORKORDER_ISSDET_SL WHERE TRAN_ID__ISS = ? AND LINE_NO__ISS = ? "
								+" AND LINE_NO <> ? ";
						pstmt1 = conn.prepareStatement(sql);
						pstmt1.setString(1,tranIdIss);
						pstmt1.setInt(2,lineNoIss);
						pstmt1.setInt(3,lineNo);
						rs1 = pstmt1.executeQuery();
						if (rs1.next())
						{	
							alreadyScannedQty = rs1.getDouble(1) ;
						}
						rs1.close();
						rs1 =null;
						pstmt1.close();
						pstmt1 =null;								
						System.out.println("alreadyScannedQty-------------->["+alreadyScannedQty+"]");
						remIssQty = woIssQty - alreadyScannedQty;
						// 31/07/10 manoharan to update quantity as well as potency_adj
						if(remIssQty == scanQty)
						{
							sql = " UPDATE WORKORDER_ISSDET SET QUANTITY = QUANTITY - ?,POTENCY_ADJ = nvl(POTENCY_ADJ,0) - ?,SCANNED = 'Y' "
									+ " WHERE TRAN_ID = ? AND LINE_NO = ? ";
						}
						else
						{
							sql = " UPDATE WORKORDER_ISSDET SET QUANTITY = QUANTITY - ?,POTENCY_ADJ = nvl(POTENCY_ADJ,0) - ?,SCANNED = 'N' "
									+ " WHERE TRAN_ID = ? AND LINE_NO = ? ";
						}

						updQty = scanQty / (quantityOriginal + potencyAdjOriginal) * quantityOriginal;
						updPotencyQty = scanQty / (quantityOriginal + potencyAdjOriginal) * potencyAdjOriginal;

						pstmt1 = conn.prepareStatement(sql);
						//pstmt1.setDouble(1,scanQty);	// 31/07/10 manoharan
						pstmt1.setDouble(1,getRequiredDecimal(updQty,3));	// 31/07/10 manoharan
						pstmt1.setDouble(2,getRequiredDecimal(updPotencyQty,3));	// 31/07/10 manoharan
						pstmt1.setString(3,tranIdIss);
						pstmt1.setInt(4,lineNoIss);	
						upd =	pstmt1.executeUpdate();
						System.out.println("upd. manohar check.......1. ["+upd+"]");
						pstmt1.close();
						// 15/03/11 manoharan delete records from workorder_issdet in case quantity - alloc_qty becomes 0 after update
						sql = " DELETE FROM WORKORDER_ISSDET "+
								" WHERE TRAN_ID = ? AND QUANTITY +  CASE WHEN POTENCY_ADJ IS NULL THEN 0 ELSE POTENCY_ADJ END = 0 ";
						pstmt1 = conn.prepareStatement(sql);
						pstmt1.setString(1,tranIdIss);
						upd =	pstmt1.executeUpdate();
						System.out.println("delete......... ["+upd+"]");
						pstmt1.close();
						// end 15/03/11 manoharan delete records from workorder_issdet in case quantity - alloc_qty becomes 0 after update
						// 19/05/10 manoharan check with count to find whether there is a record in workorder_issdet
						// for the different lot_sl scanned, if exists update else insert
						sql = " SELECT COUNT(1) AS COUNT FROM WORKORDER_ISSDET "
								+ " WHERE TRAN_ID = ? AND LINE_NO <>  ? AND ITEM_CODE = ? AND LOT_NO = ? AND LOC_CODE = ? "
								+ " AND LOT_SL = ? AND EXP_LEV = ? ";
						pstmt1 = conn.prepareStatement(sql);
						pstmt1.setString(1,tranIdIss);
						pstmt1.setInt(2,lineNoIss);
						pstmt1.setString(3,itemCode);
						pstmt1.setString(4,lotNo);
						pstmt1.setString(5,scanLocCode);
						pstmt1.setString(6,scanLotSl);								
						pstmt1.setString(7,expLev);
						rs1 = pstmt1.executeQuery();
						int count = 0;
						if (rs1.next())
						{	
							count = rs1.getInt(1) ;
						}
						rs1.close();
						rs1 =null;
						pstmt1.close();
						pstmt1 =null;
						remarks = ""; // 19/05/10 moved from below
						System.out.println("upd. manohar already exists.......1. ["+count+"]");

						if(count == 1)
						{
							// 31/07/10 manoharan to update quantity as well as potency_adj
							sql = " UPDATE WORKORDER_ISSDET SET QUANTITY = QUANTITY + ?,POTENCY_ADJ = nvl(POTENCY_ADJ,0) + ?,NO_ART = NO_ART + ?,"
									+ " NET_WEIGHT  = NET_WEIGHT + ? , GROSS_WEIGHT = GROSS_WEIGHT + ? "
									+ " WHERE TRAN_ID = ? AND LINE_NO <>  ? AND ITEM_CODE = ? AND LOT_NO = ? AND LOC_CODE = ? "
									+ " AND LOT_SL = ? AND EXP_LEV = ? ";
							pstmt1 = conn.prepareStatement(sql);
							//pstmt1.setDouble(1,scanQty); // 31/07/10 manoharan
							pstmt1.setDouble(1,getRequiredDecimal(updQty,3)); // 31/07/10 manoharan
							pstmt1.setDouble(2,getRequiredDecimal(updPotencyQty,3)); // 31/07/10 manoharan
							if(scanQty <= qtyPerArt)
								noArt = 1;
							else
							{									
								double temp = scanQty / qtyPerArt;
								if(temp == (int)temp)
									noArt = (int)temp;
								else
									noArt = (int)temp + 1;
							}
							noArt = 0; // 18/08/10 not to change no_art manoharan/tarun
							pstmt1.setInt(3,noArt);
							pstmt1.setDouble(4,scanQty);
							pstmt1.setDouble(5,scanQty);
							pstmt1.setString(6,tranIdIss);
							pstmt1.setInt(7,lineNoIss);
							pstmt1.setString(8,itemCode);
							pstmt1.setString(9,lotNo);
							pstmt1.setString(10,scanLocCode);
							pstmt1.setString(11,scanLotSl);								
							pstmt1.setString(12,expLev);
							upd = pstmt1.executeUpdate();
							System.out.println("upd. manohar check.......2. ["+upd+"]");
							// 15/03/11 manoharan delete records from workorder_issdet in case quantity - alloc_qty becomes 0 after update
							sql = " DELETE FROM WORKORDER_ISSDET "+
									" WHERE TRAN_ID = ? AND QUANTITY +  CASE WHEN POTENCY_ADJ IS NULL THEN 0 ELSE POTENCY_ADJ END = 0 ";
							pstmt1 = conn.prepareStatement(sql);
							pstmt1.setString(1,tranIdIss);
							upd =	pstmt1.executeUpdate();
							System.out.println("delete......... ["+upd+"]");
							pstmt1.close();
							// end 15/03/11 manoharan delete records from workorder_issdet in case quantity - alloc_qty becomes 0 after update
							//if(upd == 1) // 19/05/10 manoharan count used to check for existance
							//{
							//UPDATE THE WORKORDER_ISSDET_SL LINE_NO__ISS
							sql = " UPDATE WORKORDER_ISSDET_SL SET LINE_NO__ISS = "+
									" ( SELECT ISS.LINE_NO FROM WORKORDER_ISSDET ISS WHERE ISS.TRAN_ID = ? "+
									"   AND ISS.ITEM_CODE = ? AND ISS.LOT_NO = ? AND ISS.LOC_CODE = ? AND ISS.LOT_SL = ? AND EXP_LEV = ? ) "+
									" WHERE TRAN_ID = ? AND LINE_NO = ?";
							pstmt1 = conn.prepareStatement(sql);									
							pstmt1.setString(1,tranIdIss);
							pstmt1.setString(2,itemCode);
							pstmt1.setString(3,lotNo);
							pstmt1.setString(4,scanLocCode);
							pstmt1.setString(5,scanLotSl);
							pstmt1.setString(6,expLev);
							pstmt1.setString(7,tranId);
							pstmt1.setInt(8,lineNo);

							upd =	pstmt1.executeUpdate();
							System.out.println("upd......... ["+upd+"]");
							pstmt1.close();

							sql =" SELECT LINE_NO__ISS  FROM  WORKORDER_ISSDET_SL  WHERE  TRAN_ID = ? AND LINE_NO = ?";
							pstmt1 = conn.prepareStatement(sql);
							pstmt1.setString(1,tranId);
							pstmt1.setInt(2,lineNo);
							rs1 = pstmt1.executeQuery();
							if (rs1.next())
							{	
								newLineNo = rs1.getInt(1) ;
							}
							rs1.close();
							rs1 =null;
							pstmt1.close();
							pstmt1 =null;
							remarks = "ROW UPDATED BECAUSE OF LOT SL CHANGED";
						}
						else if (count > 1) // 21/05/10 manoharan added this else if
						{
							errString = "ERROR";
							return errString;
						}
						else //if(upd == 0) // 19/05/10 manoharan count used to check for existance
						{
							sql =" SELECT MAX(LINE_NO)  FROM  WORKORDER_ISSDET  WHERE  TRAN_ID = ? ";
							pstmt1 = conn.prepareStatement(sql);
							pstmt1.setString(1,tranIdIss);
							rs1 = pstmt1.executeQuery();
							if (rs1.next())
							{	
								newLineNo = rs1.getInt(1) + 1;
							}
							rs1.close();
							rs1 =null;
							pstmt1.close();
							pstmt1 =null;

							//STEP 2.UPDATE THE WORKORDER_ISSDET_SL LINE_NO__ISS

							sql = " UPDATE WORKORDER_ISSDET_SL SET LINE_NO__ISS = ? "+
									" WHERE TRAN_ID = ? AND LINE_NO = ? ";
							pstmt1 = conn.prepareStatement(sql);
							pstmt1.setInt(1,newLineNo);
							pstmt1.setString(2,tranId);
							pstmt1.setInt(3,lineNo);	
							upd =	pstmt1.executeUpdate();
							System.out.println("upd......... ["+upd+"]");
							pstmt1.close();

							//INSERT A NEW ROW WITH NEW LINE NO.								
							sql = " INSERT INTO WORKORDER_ISSDET (TRAN_ID, LINE_NO, EXP_LEV,ITEM_CODE, LOC_CODE,"+
									"	LOT_NO, LOT_SL, QUANTITY,NO_ART,UNIT, REAS_CODE, UNIT__ALT,CCTR_CODE__WP,"+
									" ACCT_CODE__WP, RATE, GROSS_RATE, STK_OPT, NET_WEIGHT, GROSS_WEIGHT,"+
									"	TARE_WEIGHT,CALC_POTENCY, POTENCY_ADJ,MATCH_POTENCY,MIN_POTENCY_PERC,"+
									" ADJ_POTENCY,QC_REQD,SCANNED) "
									+" VALUES ( ? ,? ,? ,? , ?, "
									+ " ?, ?, ?, ?, ? ,"
									+ " ?, ?, ?, ?, ? ,"
									+ " ?, ?, ?, ?, ? ,? ,? ,?,?,?,?,'Y')";
							pstmt1 = conn.prepareStatement(sql);
							pstmt1.setString(1,tranIdIss); 
							pstmt1.setInt(2,newLineNo);								
							pstmt1.setString(3,expLev);
							pstmt1.setString(4,itemCode);
							pstmt1.setString(5,scanLocCode);
							pstmt1.setString(6,lotNo);
							pstmt1.setString(7,scanLotSl);
							//pstmt1.setDouble(8,scanQty);	// 31/07/10 manoharan
							pstmt1.setDouble(8,getRequiredDecimal(updQty,3));	// 31/07/10 manoharan
							if(scanQty <= qtyPerArt)
								noArt = 1;
							else
							{									
								double temp = scanQty / qtyPerArt;
								if(temp == (int)temp)
									noArt = (int)temp;
								else
									noArt = (int)temp + 1;
							}
							pstmt1.setInt(9,noArt);
							pstmt1.setString(10,unit);
							pstmt1.setString(11,reasCode); 
							pstmt1.setString(12,""); 
							pstmt1.setString(13,cctrCodeWp); 
							pstmt1.setString(14,acctCodeWp); 
							pstmt1.setDouble(15,rate);
							pstmt1.setDouble(16,grossRate);
							pstmt1.setString(17,stkOpt);
							pstmt1.setDouble(18,getRequiredDecimal(scanQty,3));
							pstmt1.setDouble(19,getRequiredDecimal(scanQty,3));				
							pstmt1.setDouble(20,0);
							pstmt1.setString(21,calcPotency);
							//pstmt1.setDouble(22,0);	  // 31/07/10 manoharan
							pstmt1.setDouble(22,getRequiredDecimal(updPotencyQty,3));	  // 31/07/10 manoharan
							pstmt1.setString(23,matchPotency);
							pstmt1.setDouble(24,minPotencyPerc);
							pstmt1.setString(25,adjPotency); 
							pstmt1.setString(26,qcReqd);
							upd = pstmt1.executeUpdate();
							if(upd == 1)
							{
								System.out.println("Record inserted:"+upd);
							}
							remarks = "ROW ADDED BECAUSE OF LOT SL CHANGED";
						}						

						sql = " UPDATE STOCK SET LOT_SL__ORG = ? WHERE SITE_CODE = ? AND ITEM_CODE = ? "+
								" AND LOC_CODE = ? AND LOT_NO = ? AND LOT_SL = ? ";								
						pstmt1 = conn.prepareStatement(sql);
						pstmt1.setString(1,lotSl);
						pstmt1.setString(2,siteCode);
						pstmt1.setString(3,itemCode);
						pstmt1.setString(4,scanLocCode);
						pstmt1.setString(5,lotNo);
						pstmt1.setString(6,scanLotSl);							
						upd =	pstmt1.executeUpdate();
						System.out.println("upd......... ["+upd+"]");
						pstmt1.close();

						tranIdG = generateTranId("T_LOTSL_TRACE",xmlValues,conn);//function to generate NEW transaction id
						if(tranIdG == null || tranIdG.trim().length() ==0)
						{
							errString = itmDBAccessEJB.getErrorString("","VTTRANID",userId);
							return errString;
						}	
						System.out.println("tranIdG.........1......"+tranIdG);

						sql = " INSERT INTO MFG_LOTSL_TRACE (	TRAN_ID ,	TRAN_DATE ,	ITEM_CODE ,	SITE_CODE ,	"+
								" LOC_CODE ,	LOT_NO ,	LOT_SL__OLD ,	LOT_SL__NEW ,LINE_NO_ISS__OLD, "+
								" LINE_NO_ISS__NEW ,QTY_OLD, QTY_NEW,TRAN_ID__ISS,PARENT_WOISS,LOC_CODE__NEW,REMARKS,TRAN_ID__SL,LINE_NO__SL )"+
								" values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1,tranIdG); 
						pstmt.setTimestamp(2,new java.sql.Timestamp(System.currentTimeMillis()));
						pstmt.setString(3,itemCode);
						pstmt.setString(4,siteCode);
						pstmt.setString(5,locCode);
						pstmt.setString(6,lotNo);
						pstmt.setString(7,lotSl);
						pstmt.setString(8,scanLotSl);
						pstmt.setInt(9,lineNoIss);
						pstmt.setInt(10,newLineNo);
						pstmt.setDouble(11,getRequiredDecimal(woIssQty,3));
						pstmt.setDouble(12,getRequiredDecimal(woIssQty - scanQty,3));
						pstmt.setString(13,tranIdIss);
						pstmt.setString(14,tranIdIss);
						pstmt.setString(15,locCode);
						pstmt.setString(16,remarks);
						pstmt.setString(17,tranId);
						pstmt.setInt(18,lineNo);
						upd = pstmt.executeUpdate();
						if(upd == 1)
						{
							System.out.println("Record inserted mfg_lotsl_trace:"+upd);
						}

						/*tranIdG = generateTranId("T_LOTSL_TRACE",xmlValues,conn);//function to generate NEW transaction id
						if(tranIdG == null || tranIdG.trim().length() ==0)
						{
							errString = itmDBAccessEJB.getErrorString("","VTTRANID",userId);
							return errString;
						}	
						System.out.println("tranIdG.........2......"+tranIdG);
						pstmt.setString(1,tranIdG); 
						pstmt.setTimestamp(2,new java.sql.Timestamp(System.currentTimeMillis()));
						pstmt.setString(3,itemCode);
						pstmt.setString(4,siteCode);
						pstmt.setString(5,locCode);
						pstmt.setString(6,lotNo);
						pstmt.setString(7,lotSl);
						pstmt.setString(8,scanLotSl);
						pstmt.setInt(9,lineNoIss);
						pstmt.setInt(10,lineNoIss);
						pstmt.setDouble(11,woIssQty+scanQty);
						pstmt.setDouble(12,scanQty);
						pstmt.setString(13,tranIdIss);
						pstmt.setString(14,tranIdIss);
						pstmt.addBatch();								
						pstmt.executeBatch();*/
						pstmt.close();

						invallocTraceMap = new HashMap();
						InvAllocTraceBean invallocTraceBean = new InvAllocTraceBean();
						invallocTraceMap.put("ref_ser","D-ALOC");
						invallocTraceMap.put("site_code",siteCode);
						invallocTraceMap.put("item_code",itemCode);
						invallocTraceMap.put("loc_code",locCode);
						invallocTraceMap.put("lot_no",lotNo);
						invallocTraceMap.put("lot_sl",lotSl);
						invallocTraceMap.put("alloc_qty",new Double(-1 * scanQty));
						System.out.print("DeallocQty = " + (-1 * scanQty));
						invallocTraceMap.put("chg_user",userId);
						invallocTraceMap.put("chg_term",userId);
						invallocTraceMap.put("chg_win","W_WOI_SCAN");
						invallocTraceMap.put("ref_id",tranIdIss);
						invallocTraceMap.put("ref_line",lineNoIss+"");
						genWOIssue.writeLog(this.curTranId, "in postsave lot_sl not equal deallocate scanQty ["+(-1 * scanQty) +"]");
						errString = invallocTraceBean.updateInvallocTrace(invallocTraceMap,conn);
						if (errString != null && errString.trim().length() > 0)
						{
							System.out.println("errString :::"+ errString );
							return errString;
						}

						/*invallocTraceMap.put("ref_ser","W-ISS");
						invallocTraceMap.put("site_code",siteCode);
						invallocTraceMap.put("item_code",itemCode);
						invallocTraceMap.put("loc_code",scanLocCode);
						invallocTraceMap.put("lot_no",lotNo);
						invallocTraceMap.put("lot_sl",scanLotSl);						
						invallocTraceMap.put("alloc_qty",new Double(scanQty));							
						invallocTraceMap.put("chg_user",userId);
						invallocTraceMap.put("chg_term",userId);
						invallocTraceMap.put("chg_win","W_WOI_SCAN");
						invallocTraceMap.put("ref_id",tranIdIss);
						invallocTraceMap.put("ref_line",lineNoIss+"");
						errString = invallocTraceBean.updateInvallocTrace(invallocTraceMap,conn);
						if (errString != null && errString.trim().length() > 0)
						{
								System.out.println("errString :::"+ errString );
								return errString;
						}*/

						sql =" SELECT MAX(TRAN_ID) FROM INV_ALLOCATE WHERE WORK_ORDER = ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1,workOrder);							
						rs = pstmt.executeQuery();
						if (rs.next())
						{	
							tranIDAllocDet	= rs.getString(1);
						}
						rs.close();
						rs =null;
						pstmt.close();
						pstmt =null;
						// 31/07/10 manoharan to update quantity as well as potency_adj
						sql =" UPDATE INV_ALLOC_DET SET QUANTITY = QUANTITY-?,POTENCY_ADJ = nvl(POTENCY_ADJ,0) -?,ISSUE_QTY = ISSUE_QTY-? "
								+" WHERE SITE_CODE = ? AND ITEM_CODE = ? " 
								+" AND LOT_NO = ?  AND LOT_SL = ? AND LOC_CODE = ? AND CASE WHEN DEALLOCATED IS NULL THEN 'N' ELSE DEALLOCATED END <> 'Y' "
								+" AND EXP_LEV= ? AND TRAN_ID = ? ";

						updQty = scanQty / (quantityOriginal + potencyAdjOriginal) * quantityOriginal;
						updPotencyQty = scanQty / (quantityOriginal + potencyAdjOriginal) * potencyAdjOriginal;

						pstmt = conn.prepareStatement(sql);
						//pstmt.setDouble( 1, scanQty ); // 31/07/10 manoharan
						pstmt.setDouble( 1, updQty ); // 31/07/10 manoharan
						pstmt.setDouble( 2, updPotencyQty ); // 31/07/10 manoharan
						pstmt.setDouble( 3, scanQty );
						pstmt.setString( 4, siteCode );
						pstmt.setString( 5, itemCode );
						pstmt.setString( 6, lotNo );
						pstmt.setString( 7, lotSl );							
						pstmt.setString( 8, locCode );							
						pstmt.setString( 9, expLev );
						pstmt.setString( 10, tranIDAllocDet );
						upd =	pstmt.executeUpdate();
						System.out.println("upd......... ["+upd+"]");
						pstmt.close();

						sql =" UPDATE INV_ALLOC_DET SET QUANTITY = QUANTITY+?,POTENCY_ADJ = nvl(POTENCY_ADJ,0) +?,ISSUE_QTY =ISSUE_QTY+?,DEALLOC_QTY=DEALLOC_QTY+?,ALLOC_QTY =ALLOC_QTY+? "
								+" WHERE SITE_CODE = ? AND ITEM_CODE = ? " 
								+" AND LOT_NO = ?  AND LOT_SL = ?   AND LOC_CODE = ? "
								+" AND EXP_LEV= ? AND TRAN_ID = ? ";
						pstmt = conn.prepareStatement(sql);
						//pstmt.setDouble( 1, scanQty ); // 31/07/10 manoharan
						pstmt.setDouble( 1, updQty ); // 31/07/10 manoharan
						pstmt.setDouble( 2, updPotencyQty ); // 31/07/10 manoharan
						pstmt.setDouble( 3, scanQty );
						pstmt.setDouble( 4, scanQty );
						pstmt.setDouble( 5, scanQty );
						pstmt.setString( 6, siteCode );
						pstmt.setString( 7, itemCode );
						pstmt.setString( 8, lotNo );
						pstmt.setString( 9, scanLotSl );
						pstmt.setString( 10, scanLocCode );
						pstmt.setString( 11, expLev );
						pstmt.setString( 12, tranIDAllocDet );
						upd =	pstmt.executeUpdate();
						System.out.println("upd......... ["+upd+"]");
						pstmt.close();

						//if(upd == 0 ) //  09/12/10 manoharan in case of on-demend there is no need to allocate inv_alloc_det
						if(upd == 0 && tranIDAllocDet != null && tranIDAllocDet.trim().length() > 0)
						{
							sql =" SELECT MAX(LINE_NO) FROM INV_ALLOC_DET WHERE  TRAN_ID = ? ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1,tranIDAllocDet);
							rs = pstmt.executeQuery();
							if (rs.next())
							{	
								lineNoAllocDet			= rs.getInt(1);
							}
							rs.close();
							rs =null;
							pstmt.close();
							pstmt =null;
							lineNoAllocDet =lineNoAllocDet+1;
							sql = " INSERT INTO INV_ALLOC_DET (TRAN_ID,LINE_NO,SITE_CODE,ITEM_CODE,LOT_NO,LOT_SL,"
									+ " QUANTITY,REMARKS,REAS_CODE,LOC_CODE,EXP_LEV,POTENCY_ADJ,ISSUE_QTY,DEALLOC_QTY,"
									+ " ALLOC_QTY,DEALLOCATED )"
									+ " values ( ? ,? ,? ,? , ?,  ?, ?, ?, ?, ? , ?, ?, ?, ?, ? ,? )";

							pstmt1 = conn.prepareStatement(sql);
							pstmt1.setString(1,tranIDAllocDet); 
							pstmt1.setInt(2,lineNoAllocDet);
							pstmt1.setString(3,siteCode);
							pstmt1.setString(4,itemCode);
							pstmt1.setString(5,lotNo);
							pstmt1.setString(6,scanLotSl);
							//pstmt1.setDouble(7,scanQty); // 31/07/10 manoharan
							pstmt1.setDouble(7,getRequiredDecimal(updQty,3)); // 31/07/10 manoharan
							pstmt1.setString(8,"Different Lot sl Scanned");				
							pstmt1.setString(9,reasCode); 
							pstmt1.setString(10,scanLocCode);								
							pstmt1.setString(11,expLev); 
							//pstmt1.setDouble(12,0); // 31/07/10 manoharan
							pstmt1.setDouble(12,getRequiredDecimal(updPotencyQty,3)); // 31/07/10 manoharan
							pstmt1.setDouble(13,getRequiredDecimal(scanQty,3)); 
							pstmt1.setDouble(14,getRequiredDecimal(scanQty,3)); 
							pstmt1.setDouble(15,getRequiredDecimal(scanQty,3));
							pstmt1.setString(16,"N");
							upd = pstmt1.executeUpdate();
							if(upd == 1)
							{
								System.out.println("Record inserted in INV_ALLOC_DET.............:"+upd);
							}
							pstmt1.close();
						}
					}
					/* 26/11/10 manoharan quantity is not equal not to update scanned flag
					else
					{
						sql = " UPDATE WORKORDER_ISSDET SET SCANNED = 'Y' "
							+ "	WHERE TRAN_ID = ? AND LINE_NO = ? "
							+ " AND QUANTITY = (SELECT SUM(NET_WEIGHT) FROM WORKORDER_ISSDET_SL WHERE TRAN_ID__ISS = ? AND LINE_NO__ISS = ?)";
						pstmt1 = conn.prepareStatement(sql);
						pstmt1.setString(1,tranIdIss);
						pstmt1.setInt(2,lineNoIss);
						pstmt1.setString(3,tranIdIss);
						pstmt1.setInt(4,lineNoIss);
						System.out.println("Records updated-------->["+pstmt1.executeUpdate()+"]");
					}*/// end 26/11/10 manoharan quantity is not equal not to update scanned flag

					//ALLOCATION AND DEALLOCATION
					if( (!scanLocCode.equals(locCode)) || (!scanLotSl.equals(lotSl)) )
					{
						//HashMap invallocTraceMap = new HashMap();
						InvAllocTraceBean invallocTraceBean = new InvAllocTraceBean();

						sql = " SELECT QUANTITY - ALLOC_QTY FROM STOCK WHERE SITE_CODE = ? "
								+ " AND ITEM_CODE = ? AND LOT_NO = ? AND LOC_CODE = ? AND LOT_SL = ?";
						pstmt = conn.prepareStatement(sql);								
						pstmt.setString(1,siteCode);
						pstmt.setString(2,itemCode);
						pstmt.setString(3,lotNo);
						pstmt.setString(4,scanLocCode);
						pstmt.setString(5,scanLotSl);
						rs = pstmt.executeQuery();
						double remStkQty = 0;
						if (rs.next())
						{	
							remStkQty	= rs.getDouble(1);
						}
						rs.close();
						rs =null;
						pstmt.close();
						pstmt =null;

						if( remStkQty >= scanQty)
						{
							invallocTraceMap.put("ref_ser","W-ISS");
							invallocTraceMap.put("site_code",siteCode);
							invallocTraceMap.put("item_code",itemCode);
							invallocTraceMap.put("loc_code",scanLocCode);
							invallocTraceMap.put("lot_no",lotNo);
							invallocTraceMap.put("lot_sl",scanLotSl);						
							invallocTraceMap.put("alloc_qty",new Double(scanQty));							
							invallocTraceMap.put("chg_user",userId);
							invallocTraceMap.put("chg_term",userId);
							invallocTraceMap.put("chg_win","W_WOI_SCAN");
							invallocTraceMap.put("ref_id",tranIdIss);
							invallocTraceMap.put("ref_line",lineNoIss+"");
							errString = invallocTraceBean.updateInvallocTrace(invallocTraceMap,conn);
							genWOIssue.writeLog(this.curTranId, "in postsave remStkQty [" + remStkQty+ "] >= scanQty ["+scanQty +"]");
							if (errString != null && errString.trim().length() > 0)
							{
								System.out.println("errString :::"+ errString );
								return errString;
							}
						}
						else if(remStkQty > 0)
						{
							invallocTraceMap.put("ref_ser","W-ISS");
							invallocTraceMap.put("site_code",siteCode);
							invallocTraceMap.put("item_code",itemCode);
							invallocTraceMap.put("loc_code",scanLocCode);
							invallocTraceMap.put("lot_no",lotNo);
							invallocTraceMap.put("lot_sl",scanLotSl);						
							invallocTraceMap.put("alloc_qty",new Double(remStkQty));							
							invallocTraceMap.put("chg_user",userId);
							invallocTraceMap.put("chg_term",userId);
							invallocTraceMap.put("chg_win","W_WOI_SCAN");
							invallocTraceMap.put("ref_id",tranIdIss);
							invallocTraceMap.put("ref_line",lineNoIss+"");
							genWOIssue.writeLog(this.curTranId, "in postsave remStkQty > 0 [" + remStkQty+ "]");
							errString = invallocTraceBean.updateInvallocTrace(invallocTraceMap,conn);
							if (errString != null && errString.trim().length() > 0)
							{
								System.out.println("errString :::"+ errString );
								return errString;
							}
							sql = " SELECT DET.TRAN_ID,DET.LINE_NO FROM WORKORDER_ISS HDR ,WORKORDER_ISSDET DET "
									+ " WHERE HDR.TRAN_ID = DET.TRAN_ID AND HDR.SITE_CODE = ? "
									+ " AND DET.ITEM_CODE = ? AND DET.LOT_NO = ? AND DET.LOC_CODE = ? "
									+ " AND DET.LOT_SL = ? AND DET.QUANTITY >= ? "
									+ " AND (DET.SCANNED IS NULL OR DET.SCANNED<>'Y') ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1,siteCode);
							pstmt.setString(2,itemCode);
							pstmt.setString(3,lotNo);
							pstmt.setString(4,scanLocCode);
							pstmt.setString(5,scanLotSl);
							pstmt.setDouble(6,scanQty-remStkQty);
							rs = pstmt.executeQuery();
							String othWOIss = "";
							int othLineNoIss = 0;
							if (rs.next())
							{	
								othWOIss		= rs.getString(1)==null ? "":rs.getString(1);
								othLineNoIss	= rs.getInt(2);
							}
							rs.close();
							rs =null;
							pstmt.close();
							pstmt =null;
							if(othWOIss != null && othWOIss.trim().length()>0)
							{
								invallocTraceMap.put("ref_ser","D-ALOC");
								invallocTraceMap.put("site_code",siteCode);
								invallocTraceMap.put("item_code",itemCode);
								invallocTraceMap.put("loc_code",scanLocCode);
								invallocTraceMap.put("lot_no",lotNo);
								invallocTraceMap.put("lot_sl",scanLotSl);
								//   this should be the same quantity
								invallocTraceMap.put("alloc_qty",new Double(-1 * Math.abs(scanQty-remStkQty)));
								System.out.print("DeallocQty = " + (-1*Math.abs(scanQty-remStkQty)));
								invallocTraceMap.put("chg_user",userId);
								invallocTraceMap.put("chg_term",userId);
								invallocTraceMap.put("chg_win","W_WOI_SCAN");
								invallocTraceMap.put("ref_id",othWOIss);
								invallocTraceMap.put("ref_line",othLineNoIss+"");
								genWOIssue.writeLog(this.curTranId, "in postsave deallocate othWOIss [" + othWOIss+ "] quantity [" + (-1 * Math.abs(scanQty-remStkQty)) + "]");
								errString = invallocTraceBean.updateInvallocTrace(invallocTraceMap,conn);
								if (errString != null && errString.trim().length() > 0)
								{
									System.out.println("errString :::"+ errString );
									return errString;
								}

								invallocTraceMap.put("ref_ser","W-ISS");
								invallocTraceMap.put("site_code",siteCode);
								invallocTraceMap.put("item_code",itemCode);
								invallocTraceMap.put("loc_code",scanLocCode);
								invallocTraceMap.put("lot_no",lotNo);
								invallocTraceMap.put("lot_sl",scanLotSl);						
								invallocTraceMap.put("alloc_qty",new Double(scanQty-remStkQty));							
								invallocTraceMap.put("chg_user",userId);
								invallocTraceMap.put("chg_term",userId);
								invallocTraceMap.put("chg_win","W_WOI_SCAN");
								invallocTraceMap.put("ref_id",tranIdIss);
								invallocTraceMap.put("ref_line",lineNoIss+"");
								genWOIssue.writeLog(this.curTranId, "in postsave re-allocate othWOIss [" + othWOIss+ "] quantity [" + (scanQty-remStkQty) + "]");
								errString = invallocTraceBean.updateInvallocTrace(invallocTraceMap,conn);
								if (errString != null && errString.trim().length() > 0)
								{
									System.out.println("errString :::"+ errString );
									return errString;
								}
							}
						}
						else
						{
							sql = " SELECT DET.TRAN_ID,DET.LINE_NO FROM WORKORDER_ISS HDR ,WORKORDER_ISSDET DET "
									+ " WHERE HDR.TRAN_ID = DET.TRAN_ID AND HDR.SITE_CODE = ? "
									+ " AND DET.ITEM_CODE = ? AND DET.LOT_NO = ? AND DET.LOC_CODE = ? "
									+ " AND DET.LOT_SL = ? AND DET.QUANTITY >= ? "
									+ " AND (DET.SCANNED IS NULL OR DET.SCANNED<>'Y') ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1,siteCode);
							pstmt.setString(2,itemCode);
							pstmt.setString(3,lotNo);
							pstmt.setString(4,scanLocCode);
							pstmt.setString(5,scanLotSl);
							pstmt.setDouble(6,scanQty);
							rs = pstmt.executeQuery();
							String othWOIss = "";
							int othLineNoIss = 0;
							if (rs.next())
							{	
								othWOIss		= rs.getString(1)==null ? "":rs.getString(1);
								othLineNoIss	= rs.getInt(2);
							}
							rs.close();
							rs =null;
							pstmt.close();
							pstmt =null;
							if(othWOIss != null && othWOIss.trim().length()>0)
							{
								invallocTraceMap.put("ref_ser","D-ALOC");
								invallocTraceMap.put("site_code",siteCode);
								invallocTraceMap.put("item_code",itemCode);
								invallocTraceMap.put("loc_code",scanLocCode);
								invallocTraceMap.put("lot_no",lotNo);
								invallocTraceMap.put("lot_sl",scanLotSl);
								//   this should be the same quantity
								invallocTraceMap.put("alloc_qty",new Double(-1 * Math.abs(scanQty)));
								System.out.print("DeallocQty = " + (-1*Math.abs(scanQty)));
								invallocTraceMap.put("chg_user",userId);
								invallocTraceMap.put("chg_term",userId);
								invallocTraceMap.put("chg_win","W_WOI_SCAN");
								invallocTraceMap.put("ref_id",othWOIss);
								invallocTraceMap.put("ref_line",othLineNoIss+"");
								genWOIssue.writeLog(this.curTranId, "in postsave deallocate othWOIss [" + othWOIss+ "] scanQty [" + (-1 * Math.abs(scanQty)) + "]");
								errString = invallocTraceBean.updateInvallocTrace(invallocTraceMap,conn);
								if (errString != null && errString.trim().length() > 0)
								{
									System.out.println("errString :::"+ errString );
									return errString;
								}

								invallocTraceMap.put("ref_ser","W-ISS");
								invallocTraceMap.put("site_code",siteCode);
								invallocTraceMap.put("item_code",itemCode);
								invallocTraceMap.put("loc_code",scanLocCode);
								invallocTraceMap.put("lot_no",lotNo);
								invallocTraceMap.put("lot_sl",scanLotSl);						
								invallocTraceMap.put("alloc_qty",new Double(scanQty));							
								invallocTraceMap.put("chg_user",userId);
								invallocTraceMap.put("chg_term",userId);
								invallocTraceMap.put("chg_win","W_WOI_SCAN");
								invallocTraceMap.put("ref_id",tranIdIss);
								invallocTraceMap.put("ref_line",lineNoIss+"");
								genWOIssue.writeLog(this.curTranId, "in postsave re-allocate othWOIss [" + othWOIss+ "] scanQty [" + scanQty + "]");
								errString = invallocTraceBean.updateInvallocTrace(invallocTraceMap,conn);
								if (errString != null && errString.trim().length() > 0)
								{
									System.out.println("errString :::"+ errString );
									return errString;
								}
							}
						}
					}
				}

			}
			else if ("1".equals(scannedBarCode)) //  28/07/10 manoharan
			{

				if( containerCount == noArt )
				{
					genWOIssue.writeLog(this.curTranId, "28/05/13b updatingWorkorderIssuedet2 all scanned containerCount [" + containerCount + "] noArt [" + noArt + "] scannedBarCode [" + scannedBarCode+ "]");
					System.out.println("28/05/13b updatingWorkorderIssuedet2 all scanned containerCount [" + containerCount + "] noArt [" + noArt + "] scannedBarCode [" + scannedBarCode+ "]");
					sql = " UPDATE WORKORDER_ISSDET SET SCANNED = 'Y' "
							+ "	WHERE TRAN_ID = ? AND LINE_NO = ? ";
					pstmt1 = conn.prepareStatement(sql);
					pstmt1.setString(1,tranIdIss);
					pstmt1.setInt(2,lineNoIss);
					pstmt1.executeUpdate();
					pstmt1.close();
					pstmt1 = null;
				}
				else
				{
					genWOIssue.writeLog(this.curTranId, "28/05/13c updatingWorkorderIssuedet2 not scanned containerCount [" + containerCount + "] noArt [" + noArt + "] scannedBarCode [" + scannedBarCode+ "]");
					System.out.println("28/05/13c updatingWorkorderIssuedet2 not scanned containerCount [" + containerCount + "] noArt [" + noArt + "] scannedBarCode [" + scannedBarCode+ "]");
				}
			}
			// end 28/07/10 manoharan
		}//end try
		catch(Exception e)
		{
			retString  = "ERROR";
			System.out.println("Exception :  "+e);
			e.printStackTrace();
			throw  new ITMException(e);
		}
		return retString;
	} 

	void updatingWOIDetSLForRemQty( String tranId, Document dom2, Connection conn ,String objName) throws Exception
	{
		System.out.println("---------------inside updatingWOIDetSLForRemQty");
		String sql = null;
		NodeList parentNodeList=null;
		NodeList childList = null;
		Node parentNode=null;
		Node childNode = null;
		String prvTranIDIssue= null;
		String netWeightStr = "";
		String curLotNo = null;
		String curLotSl = null;
		String curExpLev = null;
		String tranIdDom = null;
		String currPacketNo = null;
		int lineNoSl = 0;
		String prvCurLotNo = null;
		String prvCurLotSl = null;
		String prvCurExpLev = null;
		String prvLineNoSlStr = null;
		String prvLineNoSl = null;
		String lineNoSlStr = null;
		int lineNo = 0;
		String prvLineNo = null;
		sql = " update workorder_issdet_sl set "
				+"		short_qty = 0 "
				+"	where tran_id = ? "
				+"		and line_no = ? ";
		PreparedStatement pstmt = null;

		GenericUtility genericUtility = GenericUtility.getInstance();
		if("w_disp_item_wiz".equals(objName))
		{
			parentNodeList = dom2.getElementsByTagName( "Detail3" );
		}
		else
		{
			parentNodeList = dom2.getElementsByTagName( "Detail2" );
		}
		int childNodeListLength = parentNodeList.getLength();
		for(int ctr = 0; ctr < childNodeListLength; ctr++)
		{
			parentNode = parentNodeList.item( ctr );

			if( ctr == 0 )
			{
				prvCurLotNo = genericUtility.getColumnValueFromNode( "lot_no", parentNode );
				prvCurLotSl = genericUtility.getColumnValueFromNode( "lot_sl", parentNode );
				prvCurExpLev = genericUtility.getColumnValueFromNode( "exp_lev", parentNode );
				prvLineNoSlStr = genericUtility.getColumnValueFromNode( "line_no_sl", parentNode );
				prvLineNo = genericUtility.getColumnValueFromNode( "line_no", parentNode );
				lineNo = Integer.parseInt( prvLineNo == null || prvLineNo.trim().length() == 0 ? "0" : prvLineNo.trim() );
			}
			else
			{
				curLotNo = genericUtility.getColumnValueFromNode( "lot_no", parentNode );
				curLotSl = genericUtility.getColumnValueFromNode( "lot_sl", parentNode );
				curExpLev = genericUtility.getColumnValueFromNode( "exp_lev", parentNode );
				lineNoSlStr = genericUtility.getColumnValueFromNode( "line_no_sl", parentNode );
				// 31/07/10 manoharan not to consider exp_lev
				//if( prvCurLotNo.trim().equals( curLotNo.trim() ) 
				//	&& prvCurLotSl.trim().equals( curLotSl.trim() )
				//	&& prvCurExpLev.trim().equals( curExpLev.trim() )
				//	&& prvLineNoSlStr.trim().equals( lineNoSlStr.trim() ) )
				if( prvCurLotNo.trim().equals( curLotNo.trim() ) 
						&& prvCurLotSl.trim().equals( curLotSl.trim() )
						&& prvLineNoSlStr.trim().equals( lineNoSlStr.trim() ) )
				{
					pstmt = conn.prepareStatement( sql );
					pstmt.setString( 1, tranId );
					pstmt.setInt( 2, lineNo );
					pstmt.executeUpdate();
					pstmt.close();
					pstmt = null;
				}					
				prvCurLotNo = curLotNo;
				prvCurLotSl = curLotSl;
				prvCurExpLev = curExpLev;
				prvLineNoSlStr = lineNoSlStr;
				prvLineNo = genericUtility.getColumnValueFromNode( "line_no", parentNode );
				lineNo = Integer.parseInt( prvLineNo == null || prvLineNo.trim().length() == 0 ? "0" : prvLineNo.trim() );
			}	

		}	
	}
	//added by rajendra on 12/01/10 for update scanned flag after material scanned
	void updatingWOIDet( String tranId, Document dom2, Connection conn ) throws Exception
	{
		System.out.println("---------------inside updatingWOIDet");
		String sql = null;
		NodeList parentNodeList=null;
		NodeList childList = null;
		Node parentNode=null;
		Node childNode = null;
		String tranIdIss="",lineNoIss="";
		int lineNo = 0;
		sql = " update workorder_issdet set "
				+"		scanned = 'Y' "
				+"	where tran_id = ? "
				+"		and line_no = ? ";
		PreparedStatement pstmt = null;

		GenericUtility genericUtility = GenericUtility.getInstance();
		parentNodeList = dom2.getElementsByTagName( "Detail2" );
		int childNodeListLength = parentNodeList.getLength();
		System.out.println("childNodeListLength .........  "+childNodeListLength);
		for(int ctr = 0; ctr < childNodeListLength; ctr++)
		{
			parentNode = parentNodeList.item( ctr );
			System.out.println("ctr .........  "+ctr);
			tranIdIss = genericUtility.getColumnValueFromNode( "tran_id__iss", parentNode );
			lineNoIss = genericUtility.getColumnValueFromNode( "line_no__iss", parentNode );
			if(lineNoIss != null && lineNoIss.trim().length() > 0)
			{
				lineNo = Integer.parseInt(lineNoIss); 
				pstmt = conn.prepareStatement( sql );
				pstmt.setString( 1, tranIdIss );
				pstmt.setInt( 2, lineNo );
				pstmt.executeUpdate();
				pstmt.close();
				pstmt = null;
			}
		}
	} 
	private String generateTranId(String windowName,String xmlValues,Connection conn)
	{
		ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "",errCode ="",errString ="";
		String tranId = null;
		String newKeystring = "";
		boolean found =false;
		try
		{
			sql = "SELECT KEY_STRING, TRAN_ID_COL, REF_SER FROM TRANSETUP WHERE UPPER(TRAN_WINDOW)=UPPER('"+windowName+"')";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			System.out.println("keyString :"+rs.toString());
			String tranSer1 = "";
			String keyString = "";
			String keyCol = "";
			if (rs.next())
			{	
				found =true;
				keyString = rs.getString(1);
				keyCol = rs.getString(2);
				tranSer1 = rs.getString(3);				
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			if(!found)
			{	  
				sql ="SELECT key_string,TRAN_ID_COL, REF_SER from transetup where tran_window = 'GENERAL' ";
				pstmt	= conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
				if (rs.next())
				{	
					keyString = rs.getString(1);
					keyCol = rs.getString(2);
					tranSer1 = rs.getString(3);	
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null ;
			}
			if(keyString ==null || keyString.trim().length() ==0)
			{
				errCode = "VTSEQ";
				System.out.println("errcode......"+errCode);
				errString = itmDBAccessEJB.getErrorString("","VTSEQ","BASE","",conn);
			}
			System.out.println("keyString=>"+keyString);
			System.out.println("keyCol=>"+keyCol);
			System.out.println("tranSer1"+tranSer1);
			System.out.println("xmlValues  :["+xmlValues+"]");
			TransIDGenerator tg = new TransIDGenerator(xmlValues, "BASE", CommonConstants.DB_NAME);
			tranId = tg.generateTranSeqID(tranSer1, keyCol, keyString, conn);
			System.out.println(" new tranId :"+tranId);
			if(rs!=null)
			{
				rs.close();
			}
			if(pstmt!=null)
			{	
				pstmt.close();
			}
		}
		catch(SQLException ex)
		{
			//System.out.println("Exception ::" +sql+ ex.getMessage() + ":");			
			ex.printStackTrace();	
			tranId=null;
		}
		catch(Exception e)
		{
			System.out.println("Exception ::" + e.getMessage() + ":");
			e.printStackTrace();
			tranId=null;
		}
		return tranId;
	}//generateTranTd()
	public double getRequiredDecimal(double actVal, int prec)  throws Exception,ITMException
	{
		/*NumberFormat numberFormat = NumberFormat.getIntegerInstance ();
		int multiply = 1;
		if (actVal < 0 )
		{ 
			multiply = -1;
			actVal =  actVal * multiply;
		}
		Double DoubleValue = new Double (actVal);
		numberFormat.setMaximumFractionDigits(3);
		String strValue = numberFormat.format(DoubleValue);
		strValue = strValue.replaceAll(",","");
		double reqVal = Double.parseDouble(strValue);
		reqVal =  reqVal * multiply;
		return reqVal; */
		return getReqDecimal(actVal, prec);
	}
	public double getReqDecimal(double actVal, int prec)
	{
		String fmtStr = "############0";
		String strValue = null;
		if (prec > 0)
		{
			fmtStr = fmtStr + "." + "000000000".substring(0, prec);
		}
		DecimalFormat decFormat = new DecimalFormat(fmtStr);
		//System.out.println("manohar 03/08/10 post_save DecimalFormat(fmtStr) [" + decFormat + "]");
		//System.out.println("manohar 03/08/10 post_save Double.parseDouble(decFormat.format(actVal)) [" + Double.parseDouble(decFormat.format(actVal)) + "]");
		return Double.parseDouble(decFormat.format(actVal));
	}
	private String getObjName(Node node) throws Exception
	{
		String objName = null;
		NodeList nodeList = null;
		Node detaulNode = null;
		Node detailNode = null;
		nodeList = node.getChildNodes();
		NamedNodeMap attrMap = node.getAttributes();
		objName = attrMap.getNamedItem( "objName" ).getNodeValue();
		/*
        for(int ctr = 0; ctr < nodeList.getLength(); ctr++ )
        {
            detailNode = nodeList.item(ctr);
            if(detailNode.getNodeName().equalsIgnoreCase("attribute") )
            {
                objName = detailNode.getAttributes().getNamedItem("objName").getNodeValue();
            }
        }
		 */
		return "w_" + objName;

	}
	private double netQtyPerPacket( Document dom2, String lotNo, String lotSl, String packetNo, String locCode, String lineNo, Connection conn)   throws Exception,ITMException
	{
		System.out.println("----------- inside netQtyPerPacket ");
		double quantity = 0, totalnetWeight=0, netWeight=0, potencyAdj=0, netWeightSL=0.0;
		String sql = null;
		NodeList parentNodeList=null;
		NodeList childList = null;
		Node parentNode=null;
		Node childNode = null;
		String prvTranIDIssue= null;
		String netWeightStr = "";
		String curLotNo = null;
		String curLotSl = null;
		String tranIdDom = null;
		String currLocCode = null;
		String currPacketNo = null;
		String curLineNo = null;
		try
		{
			GenericUtility genericUtility = GenericUtility.getInstance();
			parentNodeList = dom2.getElementsByTagName( "Detail3" );
			int childNodeListLength = parentNodeList.getLength();

			for(int ctr = 0; ctr < childNodeListLength; ctr++)
			{
				parentNode = parentNodeList.item( ctr );
				curLotNo = genericUtility.getColumnValueFromNode( "lot_no", parentNode );
				curLotSl = genericUtility.getColumnValueFromNode( "lot_sl", parentNode );
				netWeightStr = genericUtility.getColumnValueFromNode( "net_weight", parentNode );
				currPacketNo = genericUtility.getColumnValueFromNode( "line_no_sl", parentNode );
				currLocCode = genericUtility.getColumnValueFromNode( "loc_code", parentNode );
				curLineNo = genericUtility.getColumnValueFromNode( "line_no", parentNode );
				if (netWeightStr == null || "null".equals(netWeightStr) )
				{
					netWeightStr = "0";
				}
				netWeight = Double.parseDouble( netWeightStr.trim() );
				if (netWeight < 0)
				{
					netWeight = 0;
				}

				if( curLotNo.trim().equalsIgnoreCase( lotNo.trim() )
						&& currPacketNo.trim().equalsIgnoreCase( packetNo )
						&& curLotSl.trim().equalsIgnoreCase( lotSl.trim() )
						&& currLocCode.trim().equalsIgnoreCase( locCode.trim() )
						&& !curLineNo.trim().equalsIgnoreCase( lineNo.trim() ))
				{
					totalnetWeight = totalnetWeight + netWeight;
				}
			}
		}
		catch( Exception ex )
		{
			ex.printStackTrace();
			throw new ITMException(ex);
		}
		return totalnetWeight;
	}
	private String checkNull( String input )
	{
		if( input == null )
		{
			input = "";
		}
		else
		{
			input = input.trim();
		}
		return input;
	}

	public String postSave(String winName,String editFlag,String xmlString,  String xtraParams,Connection conn) throws RemoteException,ITMException
	{
		String retString = "",tranId="";
		GenericUtility genericUtility = GenericUtility.getInstance();
		Document dom= null;
		
		try
		{
			System.out.println("@@@@@@ xmlString in postSave ["+xmlString+"]");
			if(xmlString != null && xmlString.trim().length() > 0)
			{
				dom = parseString(xmlString);
			}
				tranId = genericUtility.getColumnValue("tran_id", dom);//Change by chandrashekar 0n 12-mar-2015
			
			/*String[] arrayForTranId = xmlString.split("<tran_id>");
			System.out.println("@@@@arrayForTranId["+arrayForTranId+"]");
			if( arrayForTranId.length > 1)
			{
				int endIndex = arrayForTranId[1].indexOf("</tran_id>");
				tranId = arrayForTranId[1].substring(0,endIndex);
			}*/
			
			System.out.println("@@@@@@ tranId["+tranId+"]");
			
			WOIScanConf woiScanConf = new WOIScanConf();
			//retString = woiScanConf.confirm(tranId, xtraParams, "", conn, false );//Change by chandrashekar 0n 12-mar-2015
			retString = woiScanConf.confirm(tranId, xtraParams, "");
			
			System.out.println("@@@@@ retString["+retString+"]");
			if( retString != null &&  retString.contains("VTCNFSUCC") ) 
			{
				retString = "";
				System.out.println("conn commit successfully.........");
				//conn.commit();
			}
			else
			{
				System.out.println("conn rollback successfully.........");
				conn.rollback();
				
			}
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		return retString;
	}
	
}
