package ibase.webitm.ejb.wms;

import java.rmi.RemoteException;
import java.util.*;
import java.sql.*;
import org.w3c.dom.*;

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

import ibase.webitm.ejb.dis.*;
import ibase.webitm.ejb.*;
import ibase.webitm.utility.ITMException;
import ibase.webitm.utility.GenericUtility;
import ibase.system.config.*;
import javax.ejb.Stateless; // added for ejb3


//public class DespatchPrsEJB extends ValidatorEJB implements SessionBean
@Stateless // added for ejb3
public class PickOrderPrs extends ValidatorEJB implements PickOrderPrsLocal, PickOrderPrsRemote
{
	

    public String preSaveRec() throws RemoteException,ITMException
	{
		return "";
	}

    public String preSaveRec(String xmlString1,String domId,String objContext,String editFlag, String xtraParams, Connection conn)throws RemoteException,ITMException
	{

		System.out.println("Pick order Pre Save CAlled******************************************");
		GenericUtility genericUtility = GenericUtility.getInstance();
		Document dom = null;
		try
		{
			System.out.println("xmlString1 in ReplenishmentPrs :: preSaveRec \n"+xmlString1);			
			System.out.println("domId in ReplenishmentPrs :: preSaveRec \n"+domId);			
			if (xmlString1 != null && xmlString1.trim().length() > 0)
			{
				//dom = GenericUtility.getInstance().parseString(xmlString1);
				dom = genericUtility.parseString(xmlString1); 
				System.out.println("Dom FOUND"+dom);
				executepreSaveRec(dom,domId,objContext,editFlag,xtraParams,conn);
			}			
		}
	   	catch(Exception e)
		{
			System.out.println("Exception :ReplenishmentPrs ::" + e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		return "";
	}

	private String executepreSaveRec(Document dom, String domID,String objContext,String editFlag, String xtraParams, Connection conn)throws RemoteException,ITMException
	{
		Statement stmt = null,stmt1 = null;
		ResultSet rs = null, rs1 = null;
		PreparedStatement pstmt = null;
		String siteCode = "", itemCode = "", lineNo = "",locCode = "", lotNo = "", lotSl = "";
		String expLev = "", itemCodeOrd = "", sordNo = "",lineNoSord = "", sql = "";
		HashMap strAllocate = new HashMap();
		String sqlSordAlloc = "", sqlSord = "", sqlSordItem = "", line = "";
		double qtyAlloc = 0d, qtyStduom = 0d;
		int updateCnt = 0, retVal = 0, count =0;
		java.sql.Date tranDate = null;		   	
		GenericUtility genericUtility = GenericUtility.getInstance();
		DistStkUpdLocal distStkUpd = null;
		String tranId = "",qtyStduomStr = "";
		NodeList hdrDom = null;
		Node currDetail = null; 
		String updateStatus = "";
		String sqlRepl = "";
		String replOrd = "";
		String lineNorepl = "";
		String sqlPick = "";
		String sqlItem = "";
		String sqlPickUpt = "";
		String invStat = "";
		String casePickLoc = "";
		String resrvLoc = "";
		DistCommon discommon = new DistCommon();
	    double domQty = 0.0;
	    double caseSize = 0.0;
		String sqlReplUpt = "";
		String sqlReplCase = "";
		
		try
		{
			stmt = conn.createStatement();
			stmt1 = conn.createStatement();
			distStkUpd = getComponent("DistStkUpd");
			//Changed by Rohan on 11/8/12 to get value from disparam
			casePickLoc  = discommon.getDisparams("999999","CASE_PICK_INVSTAT",conn);
			resrvLoc  = discommon.getDisparams("999999","RESERV_LOCATION",conn);
			
			//System.out.println("TranId ::"+tranId);
			tranDate = new java.sql.Date(System.currentTimeMillis());
			System.out.println("\n tranDate :"+tranDate);
			System.out.println("Dom FOUND&&&&&&&&&&&&&&&&&&&&&&&&77"+dom);
			hdrDom = dom.getElementsByTagName("Detail1");
			System.out.println("HDR DOMS"+hdrDom);
			siteCode = GenericUtility.getInstance().getColumnValueFromNode("site_code",hdrDom.item(0));
			//tranId = GenericUtility.getInstance().getColumnValueFromNode("desp_id",hdrDom.item(0));
		    //Changed bt Rohna
			tranId = GenericUtility.getInstance().getColumnValueFromNode("pick_order",hdrDom.item(0));
			System.out.println("pick_order@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@"+tranId);
			System.out.println("Site Code :: "+siteCode);
			System.out.println("Tran ID :: "+tranId);
			
			//sql = "SELECT SITE_CODE FROM DESPATCH WHERE DESP_ID = '"+tranId+"'";
			//System.out.println("sql :"+sql);
			//rs = stmt.executeQuery(sql);
			//if (rs.next())
			//{
			//	siteCode = rs.getString(1);
			//	System.out.println("siteCode :"+siteCode);
			//}


			//sql = "SELECT ITEM_CODE, LINE_NO, LOC_CODE, LOT_NO, LOT_SL, QUANTITY__STDUOM, "
			//	  +"EXP_LEV, ITEM_CODE__ORD, SORD_NO, LINE_NO__SORD "
			//	  +"FROM DESPATCHDET WHERE DESP_ID = '"+tranId+"'";
			//System.out.println("sql :"+sql);
			//rs = stmt.executeQuery(sql);
            System.out.println("dom****************"+dom);
            System.out.println("domID****************"+domID);
			currDetail	 = getCurrentDetailFromDom(dom,domID);
			 System.out.println("currDetail****************"+currDetail);
			updateStatus = getCurrentUpdateFlag(currDetail);
			
			//System.out.println("*************** Update Status *************** "+updateStatus);
			System.out.println("DomID :: "+domID+"\n Update Status ::"+updateStatus);
			System.out.println("*************** Update Status *************** ");
		
			
			if (currDetail != null  && !updateStatus.equalsIgnoreCase("A")) // 22/02/10 manoharan if allocation found in sordalloc the deallocate
			{
				itemCode		= GenericUtility.getInstance().getColumnValueFromNode("item_code",currDetail);
				lineNo			= GenericUtility.getInstance().getColumnValueFromNode("line_no",currDetail);
				locCode			= GenericUtility.getInstance().getColumnValueFromNode("loc_code",currDetail);
				lotNo			= GenericUtility.getInstance().getColumnValueFromNode("lot_no",currDetail);
				lotSl			= GenericUtility.getInstance().getColumnValueFromNode("lot_sl",currDetail);
				//qtyStduomStr	= GenericUtility.getInstance().getColumnValueFromNode("quantity__stduom",currDetail);
				qtyStduomStr	= GenericUtility.getInstance().getColumnValueFromNode("quantity",currDetail);
				expLev			= GenericUtility.getInstance().getColumnValueFromNode("exp_lev",currDetail);
				itemCodeOrd		= itemCode ;//GenericUtility.getInstance().getColumnValueFromNode("item_code__ord",currDetail);
				//sordNo			= GenericUtility.getInstance().getColumnValueFromNode("sord_no",currDetail);
				sordNo			= GenericUtility.getInstance().getColumnValueFromNode("sale_order",currDetail);
				lineNoSord		= GenericUtility.getInstance().getColumnValueFromNode("line_no__sord",currDetail);
				// 04/04/12 manoharan
				
				lineNoSord = "   " + lineNoSord; 
				lineNoSord = lineNoSord.substring(lineNoSord.length()-3);
				
				qtyStduom = Double.parseDouble(qtyStduomStr);
				domQty = qtyStduom;
				// 22/02/10 manoharan if allocation found in sordalloc the deallocate
				if (updateStatus.equalsIgnoreCase("A"))
				{
					//sql = "SELECT COUNT(1) AS COUNT FROM SORDALLOC " // 04/04/12 manoharan
					sql = "SELECT QTY_ALLOC AS COUNT FROM SORDALLOC " // 04/04/12 manoharan
						  +"WHERE SALE_ORDER = ? "
						  +"AND LINE_NO = ? "
						  +"AND EXP_LEV = ? "
						  +"AND ITEM_CODE__ORD = ? "
						  +"AND ITEM_CODE = ? "
						  +"AND LOT_NO = ? "
						  +"AND LOT_SL = ? "
						  +"AND LOC_CODE = ? ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,sordNo);
					pstmt.setString(2,lineNoSord);
					pstmt.setString(3,expLev);
					pstmt.setString(4,itemCodeOrd);
					pstmt.setString(5,itemCode);
					pstmt.setString(6,lotNo);
					pstmt.setString(7,lotSl);
					pstmt.setString(8,locCode);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						//count = rs.getInt(1); // 
						qtyAlloc = rs.getDouble(1);
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;

					// 22/02/10 manoharan if allocation found in sordalloc then deallocate
					//if (count > 0 ) // 04/04/12 manoharan
					if (qtyAlloc > 0) // 04/04/12 manoharan
					{
						if (qtyAlloc - qtyStduom <= 0)
						{
							sqlSord = "DELETE FROM SORDALLOC WHERE SALE_ORDER = ? "
								 +"AND LINE_NO = ? "
								 +"AND EXP_LEV = ? " 
								 +"AND ITEM_CODE__ORD = ? " 
								 +"AND ITEM_CODE = ? " 
								 +"AND LOT_NO = ? " 
								 +"AND LOT_SL = ? "
								 +"AND LOC_CODE = ? ";
							System.out.println("Delete sql sqlSord :"+sqlSord);
							pstmt = conn.prepareStatement(sqlSord);
							pstmt.setString(1,sordNo);
							pstmt.setString(2,lineNoSord);
							pstmt.setString(3,expLev);
							pstmt.setString(4,itemCodeOrd);
							pstmt.setString(5,itemCode);
							pstmt.setString(6,lotNo);
							pstmt.setString(7,lotSl);
							pstmt.setString(8,locCode);
							updateCnt = pstmt.executeUpdate();
							pstmt.close();
							pstmt = null;
							System.out.println("Deleted the no of records are : updateCnt :"+updateCnt);
						}
						else
						{
							sqlSord = "UPDATE SORDALLOC SET QTY_ALLOC = QTY_ALLOC - ? "
								  +"WHERE SALE_ORDER = ? "
								  +"AND LINE_NO = ? "
								  +"AND EXP_LEV = ? "
								  +"AND ITEM_CODE__ORD = ? "
								  +"AND ITEM_CODE = ? "
								  +"AND LOT_NO = ? "
								  +"AND LOT_SL = ? "
								  +"AND LOC_CODE = ? ";
							System.out.println("Update sql sqlSord :"+sqlSord);
							pstmt = conn.prepareStatement(sqlSord);
							pstmt.setDouble(1,qtyStduom);
							pstmt.setString(2,sordNo);
							pstmt.setString(3,lineNoSord);
							pstmt.setString(4,expLev);
							pstmt.setString(5,itemCodeOrd);
							pstmt.setString(6,itemCode);
							pstmt.setString(7,lotNo);
							pstmt.setString(8,lotSl);
							pstmt.setString(9,locCode);
							updateCnt = pstmt.executeUpdate();
							pstmt.close();
							pstmt = null;
							System.out.println("Updated the no of records are : updateCnt :"+updateCnt);
						}
						sqlSordItem = "UPDATE SORDITEM SET QTY_ALLOC = QTY_ALLOC - ? "
									 +"WHERE SALE_ORDER = ? "
									 +"AND LINE_NO = ? "
									 +"AND EXP_LEV = ? ";
						System.out.println("Update sql sqlSordItem :"+sqlSordItem);
						pstmt = conn.prepareStatement(sqlSordItem);
						pstmt.setDouble(1,qtyStduom);
						pstmt.setString(2,sordNo);
						pstmt.setString(3,lineNoSord);
						pstmt.setString(4,expLev);
						updateCnt = pstmt.executeUpdate();
						pstmt.close();
						pstmt = null;
						System.out.println("Updated the no of records : updateCnt :"+updateCnt);

						line = "   " + lineNo;
						System.out.println("line :"+line);
						
						//Changed by Rohan on 10/8/12 to give proper loc code.start

						
						//Changed by Rohan on 10/8/12 to give proper loc code.end
						strAllocate.put("tran_date",tranDate);
						strAllocate.put("ref_ser","P-TASK");
						strAllocate.put("ref_id",tranId);
						strAllocate.put("ref_line",line.substring(line.length()-3));
						strAllocate.put("site_code",siteCode);
						strAllocate.put("item_code",itemCode);
						System.out.println("Updated loc code*********************************************"+locCode);
						strAllocate.put("loc_code",locCode);
						strAllocate.put("lot_no",lotNo);
						strAllocate.put("lot_sl",lotSl);
						strAllocate.put("alloc_qty",new Double(-1*qtyStduom)); 
						strAllocate.put("chg_user",genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginCode"));
						strAllocate.put("chg_term",genericUtility.getValueFromXTRA_PARAMS(xtraParams,"termId"));
						strAllocate.put("chg_win","W_PICK_ORD");
						//Calling DistStkUpdEJB
						System.out.println("Calling DistStkUpdEJB.....");
						if (distStkUpd.updAllocTrace(strAllocate, conn) > 0)
						{
							System.out.println("distStkUpd.UpdAllocTrace(hashMap) : Sucessuful!");
						}
					} //  end 22/02/10 manoharan

				}
				// end 22/02/10 manoharan
				else if (updateStatus.equalsIgnoreCase("E") || updateStatus.equalsIgnoreCase("D"))	//if codn added by jiten 19/05/06
				{
					//Commented And Changes Below - Gulzar 10/05/07
					//sql = "SELECT QUANTITY__STDUOM FROM DESPATCHDET WHERE DESP_ID = '"+tranId+"' AND LINE_NO = '"+lineNo+"'"; //Gulzar - 10/05/07
					/*sql = "SELECT LINE_NO__SORD, EXP_LEV, ITEM_CODE__ORD, ITEM_CODE, LOC_CODE, LOT_NO, LOT_SL, QUANTITY__STDUOM  " 
						 +"FROM DESPATCHDET WHERE DESP_ID = '"+tranId+"' AND LINE_NO = '"+lineNo+"'";*/
					sql = "SELECT LINE_NO__SORD, EXP_LEV,  ITEM_CODE, LOC_CODE, LOT_NO, LOT_SL, QUANTITY,REPL_ORDER,LINE_NO__REPL  " 
							 +"FROM PICK_ORD_DET WHERE PICK_ORDER = '"+tranId+"' AND LINE_NO = '"+lineNo+"'";
					System.out.println("SQL ::"+sql);
					rs = stmt.executeQuery(sql);
					if (rs.next())
					{
						lineNoSord	 = rs.getString("LINE_NO__SORD");  //Gulzar 10/05/07	
						expLev		 = rs.getString("EXP_LEV");		   //Gulzar 10/05/07	
						itemCode	 = rs.getString("ITEM_CODE");	   //Gulzar 10/05/07
						itemCodeOrd	 = itemCode;                       //Gulzar 10/05/07	
						locCode		 = rs.getString("LOC_CODE");	   //Gulzar 10/05/07			
						lotNo		 = rs.getString("LOT_NO");		   //Gulzar 10/05/07		
						lotSl		 = rs.getString("LOT_SL");		   //Gulzar 10/05/07
						lineNoSord = "   " + lineNoSord; 
						lineNoSord = lineNoSord.substring(lineNoSord.length()-3);
						
						
						//qtyStduomStr = rs.getString("QUANTITY__STDUOM");	
						qtyStduomStr = rs.getString("QUANTITY");
						//End Changes - Gulzar 10/05/07
						
						qtyStduom = Double.parseDouble(qtyStduomStr);
						
						/*replOrd = rs.getString("REPL_ORDER");
						lineNorepl = rs.getString("LINE_NO__REPL");
						
						if(pstmt != null)
						{	
							pstmt.close();
							pstmt = null;
						}	
						
						if(rs != null)
						{	
							rs.close();
							rs = null;
						}
						//sqlRepl = "SELECT LOC_CODE FROM REPL_ORD_DET WHERE REPL_ORDER = ? AND LINE_NO = ?";
						sqlRepl = "SELECT R.LOC_CODE LOC_CODE,L.INV_STAT INV_STAT FROM REPL_ORD_DET R,LOCATION L "
								 +" WHERE R.LOC_CODE = L.LOC_CODE AND REPL_ORDER = ? AND LINE_NO = ?";
								
						pstmt = conn.prepareStatement(sqlRepl);
						
						pstmt.setString(1,replOrd);
						pstmt.setString(2,lineNorepl);
					
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							locCode = rs.getString("LOC_CODE");
							invStat = rs.getString("INV_STAT");
						}
						
						pstmt.close();
						pstmt = null;
						
						rs.close();
						rs = null;
						//Changed by Rohan on 10-04-12 for bug fixing.start
						
						sqlReplCase = "SELECT R.LOC_CODE LOC_CODE,L.INV_STAT INV_STAT FROM REPL_ORD_DET R,LOCATION L" 
							 +" WHERE R.LOC_CODE = L.LOC_CODE AND  R.ITEM_CODE = ? AND R.SITE_CODE = ?"
						      +" AND R.LOT_NO = ? AND R.LOT_SL = ? AND R.LOC_CODE__TO = ?";
						pstmt = conn.prepareStatement(sqlReplCase);
						//Changed by Rohan on 10-04-12 for bug fixing.end
						if(casePickLoc.equalsIgnoreCase(invStat))
						{
							
							System.out.println("Enter in active Repalinshement CASE PICK PSO");
							//Changed by Rohan on 10-04-12 for bug fixing.start
							
							sqlReplCase = "SELECT R.LOC_CODE LOC_CODE,L.INV_STAT INV_STAT FROM REPL_ORD_DET R,LOCATION L" 
										 +" WHERE R.LOC_CODE = L.LOC_CODE AND  R.ITEM_CODE = ? AND R.SITE_CODE = ?"
									      +" AND R.LOT_NO = ? AND R.LOT_SL = ? AND R.LOC_CODE__TO = ?";
							pstmt = conn.prepareStatement(sqlReplCase);
							
							//Changed by Rohan on 10-04-12 for bug fixing.end
							pstmt.setString(1,itemCode);
							pstmt.setString(2,siteCode );
							pstmt.setString(3,lotNo );
							pstmt.setString(4,lotSl );
							pstmt.setString(5,locCode );
							
						
							rs = pstmt.executeQuery();
							if(rs.next())
							{
								locCode = rs.getString("LOC_CODE");
								invStat = rs.getString("INV_STAT");
								System.out.println("LOC CODE PSO"+locCode);
								
							}
							
						
							if(rs != null)
							{	
								rs.close();
								rs = null;
							}
								
							
						}
						if(invStat.startsWith("PND"))
						{
							
							System.out.println("Enter in active Repalinshement  DSO");
							
							pstmt.setString(1,itemCode);
							pstmt.setString(2,siteCode );
							pstmt.setString(3,lotNo );
							pstmt.setString(4,lotSl );
							pstmt.setString(5,locCode );
							
							rs = pstmt.executeQuery();
							if(rs.next())
							{
								locCode = rs.getString("LOC_CODE");
								System.out.println("LOC CODE DSO"+locCode);
								
							}
							if(pstmt != null)
							{	
								pstmt.close();
								pstmt = null;
							}
							if(rs != null)
							{	
								rs.close();
								rs = null;
							}	
						}
						if(pstmt != null)
						{	
							pstmt.close();
							pstmt = null;
						}
						sqlItem = "SELECT SHIPPER_SIZE FROM ITEM_LOT_PACKSIZE WHERE ITEM_CODE = ?";
						
						pstmt = conn.prepareStatement(sqlItem);
						
						pstmt.setString(1,itemCode);
						
						
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							caseSize = rs.getDouble("SHIPPER_SIZE");
						}
						
						pstmt.close();
						pstmt = null;
						
						rs.close();
						rs = null;
						
						
						sqlReplUpt = "UPDATE REPL_ORD_DET SET ACTUAL_QTY = ACTUAL_QTY + ?" 
							 	+" WHERE REPL_ORDER = ? AND LINE_NO = ?";
				
						pstmt = conn.prepareStatement(sqlReplUpt);
						
						System.out.println("pickQty"+qtyStduom+"domQty"+domQty);
						
						if(updateStatus.equalsIgnoreCase("D"))
						{						
							pstmt.setDouble(1,-qtyStduom);
						}
						else
						{
							pstmt.setDouble(1,(qtyStduom - domQty));
						}
						pstmt.setString(2,replOrd);
						pstmt.setString(3,lineNorepl);
						
						int cnt = pstmt.executeUpdate();
						
						
						if (cnt > 0)
						{
							System.out.println("update actual qty succesfully");
						}
						if(pstmt != null)
						{	
							pstmt.close();
							pstmt = null;
						}
						*/
						
						sqlItem = "SELECT SHIPPER_SIZE FROM ITEM_LOT_PACKSIZE WHERE ITEM_CODE = ?";
						
						pstmt = conn.prepareStatement(sqlItem);
						pstmt.setString(1,itemCode);
						
						
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							caseSize = rs.getDouble("SHIPPER_SIZE");
						}
						
						pstmt.close();
						pstmt = null;
						
						rs.close();
						rs = null;
						
						sqlPickUpt = "UPDATE PICK_ORD_DET SET NO_ART = ?" 
							 	+" WHERE PICK_ORDER = ? AND LINE_NO = ?";
				
						pstmt = conn.prepareStatement(sqlPickUpt);
						
						System.out.println("domQty"+domQty+"caseSize"+caseSize);
						pstmt.setDouble(1,(domQty/caseSize));
						pstmt.setString(2,tranId);
						pstmt.setString(3,lineNo);
						
						//+tranId+"' AND LINE_NO = '"+lineNo+"
						int cnt1 = pstmt.executeUpdate();
						
						
						if (cnt1 > 0)
						{
							System.out.println("update no of art qty succesfully");
						}
						if(pstmt != null)
						{	
							pstmt.close();
							pstmt = null;
						}
						System.out.println("Updated loc code@@@@@@@@@@@@@@@@@@@@@@@@@@@@"+locCode);
						//Changed by Rohan on 10/8/12 to give proper loc code.end
						

						
						sqlSordAlloc = "SELECT QTY_ALLOC FROM SORDALLOC WHERE SALE_ORDER = '"+sordNo+"' "
										+"AND LINE_NO =	'"+lineNoSord+"' "
										+"AND EXP_LEV =	'"+expLev+"' "
										+"AND ITEM_CODE__ORD = '"+itemCodeOrd+"' "
										+"AND ITEM_CODE	= '"+itemCode+"' "
										+"AND LOC_CODE = '"+locCode+"' "
										+"AND LOT_NO = '"+lotNo+"' "
										+"AND LOT_SL = '"+lotSl+"' ";
									//	+"AND ALLOC_MODE = 'A' "; //Commented by Jiten 10/10/06 as Commented in PB - manual allocation should also get deallocated.
						System.out.println("Select SQL sqlSordAlloc :"+sqlSordAlloc);
						rs1 = stmt1.executeQuery(sqlSordAlloc);
						if (rs1.next())
						{
							qtyAlloc = rs1.getDouble(1);
							System.out.println("qtyAlloc :"+qtyAlloc);

							if (String.valueOf(qtyAlloc) == null)
							{
								qtyAlloc = 0;
							}
							// 22/02/10 manoharan if allocation found in sordalloc then deallocate
							if (qtyAlloc - qtyStduom <= 0)
							{
								sqlSord = "DELETE FROM SORDALLOC WHERE SALE_ORDER = ? "
									 +"AND LINE_NO = ? "
									 +"AND EXP_LEV = ? " 
									 +"AND ITEM_CODE__ORD = ? " 
									 +"AND ITEM_CODE = ? " 
									 +"AND LOT_NO = ? " 
									 +"AND LOT_SL = ? "
									 +"AND LOC_CODE = ? ";
								System.out.println("Delete sql sqlSord :"+sqlSord);
								pstmt = conn.prepareStatement(sqlSord);
								pstmt.setString(1,sordNo);
								pstmt.setString(2,lineNoSord);
								pstmt.setString(3,expLev);
								pstmt.setString(4,itemCodeOrd);
								pstmt.setString(5,itemCode);
								pstmt.setString(6,lotNo);
								pstmt.setString(7,lotSl);
								pstmt.setString(8,locCode);
								updateCnt = pstmt.executeUpdate();
								pstmt.close();
								pstmt = null;
								System.out.println("Deleted the no of records are : updateCnt :"+updateCnt);
							}
							else
							{
								sqlSord = "UPDATE SORDALLOC SET QTY_ALLOC = QTY_ALLOC - ? "
									  +"WHERE SALE_ORDER = ? "
									  +"AND LINE_NO = ? "
									  +"AND EXP_LEV = ? "
									  +"AND ITEM_CODE__ORD = ? "
									  +"AND ITEM_CODE = ? "
									  +"AND LOT_NO = ? "
									  +"AND LOT_SL = ? "
									  +"AND LOC_CODE = ? ";
								System.out.println("Update sql sqlSord :"+sqlSord);
								pstmt = conn.prepareStatement(sqlSord);
								pstmt.setDouble(1,qtyStduom);
								pstmt.setString(2,sordNo);
								pstmt.setString(3,lineNoSord);
								pstmt.setString(4,expLev);
								pstmt.setString(5,itemCodeOrd);
								pstmt.setString(6,itemCode);
								pstmt.setString(7,lotNo);
								pstmt.setString(8,lotSl);
								pstmt.setString(9,locCode);
								updateCnt = pstmt.executeUpdate();
								pstmt.close();
								pstmt = null;
								System.out.println("Updated the no of records are : updateCnt :"+updateCnt);
							}
							sqlSordItem = "UPDATE SORDITEM SET QTY_ALLOC = QTY_ALLOC - ? "
										 +"WHERE SALE_ORDER = ? "
										 +"AND LINE_NO = ? "
										 +"AND EXP_LEV = ? ";
							System.out.println("Update sql sqlSordItem :"+sqlSordItem);
							pstmt = conn.prepareStatement(sqlSordItem);
							pstmt.setDouble(1,qtyStduom);
							pstmt.setString(2,sordNo);
							pstmt.setString(3,lineNoSord);
							pstmt.setString(4,expLev);
							updateCnt = pstmt.executeUpdate();
							pstmt.close();
							pstmt = null;
							System.out.println("Updated the no of records : updateCnt :"+updateCnt);
						}
						rs1.close();
						rs1 = null;
						stmt1.close();
						stmt1 = null;

					}
					if(rs != null)
					{	
						rs.close();
						rs = null;
					}
					if(stmt != null)
					{	
						stmt.close();
						stmt = null;
					}	
					
					line = "   " + lineNo;
					System.out.println("line :"+line);
					
					strAllocate.put("tran_date",tranDate);
					strAllocate.put("ref_ser","P-TASK");
					strAllocate.put("ref_id",tranId);
					strAllocate.put("ref_line",line.substring(line.length()-3));
					strAllocate.put("site_code",siteCode);
					strAllocate.put("item_code",itemCode);
					System.out.println("^^^^^^^^^^^^^^^^^^^^"+locCode);
					strAllocate.put("loc_code",locCode);
					strAllocate.put("lot_no",lotNo);
					strAllocate.put("lot_sl",lotSl);
					strAllocate.put("alloc_qty",new Double(-1*qtyStduom)); 
					strAllocate.put("chg_user",genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginCode"));
					strAllocate.put("chg_term",genericUtility.getValueFromXTRA_PARAMS(xtraParams,"termId"));
					strAllocate.put("chg_win","W_PICK_ORD");
					//Calling DistStkUpdEJB
					System.out.println("Calling DistStkUpdEJB.....");
					if (distStkUpd.updAllocTrace(strAllocate, conn) > 0)
					{
						System.out.println("distStkUpd.UpdAllocTrace(hashMap) : Sucessuful!");
					}
				}
			}			
		}//try end
		catch (SQLException sqx)
		{
			System.out.println("The SQLException occurs in ReplenishmentPrs :"+sqx);
			sqx.printStackTrace();
			throw new ITMException(sqx);
		}
		catch(Exception e)
		{
			System.out.println("The SQLException occurs in ReplenishmentPrs :"+e);			
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if(stmt != null)
				{
					stmt.close();
					stmt = null;
				}
				if (stmt1 != null)
				{
					stmt1.close();
					stmt1 = null;
				}
				if (pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
			}
			catch(Exception e)
			{
				System.err.println("Exception :DBAccessEJB :getITMVersion :\n"+e.getMessage());
				e.printStackTrace();
				throw new ITMException(e);
			}
		}
		return "";
	}

	private Node getCurrentDetailFromDom(Document dom,String domId)
	{
		NodeList detailList = null;
		Node currDetail = null,reqDetail = null;
		String currDomId = "";
		int	detailListLength = 0;

		detailList = dom.getElementsByTagName("Detail2");
		System.out.println("detailList**********"+detailList);
		detailListLength = detailList.getLength();
		for (int ctr = 0;ctr < detailListLength;ctr++)
		{
			currDetail = detailList.item(ctr);
			System.out.println("currDetail**********"+currDetail);
			currDomId = currDetail.getAttributes().getNamedItem("domID").getNodeValue();
			System.out.println("currDomId**********"+currDomId);
			if (currDomId.equals(domId))
			{
				reqDetail = currDetail;
				break;
			}			
		}
		System.out.println("reqDetail**********"+reqDetail);
		return reqDetail;
	}

	private String getCurrentUpdateFlag(Node currDetail)
	{
		NodeList currDetailList = null;
		String updateStatus = "",nodeName = "";
		int currDetailListLength = 0;
		
		System.out.println("currDetail************$$$$$$$$$$$$$$$$$$$$$$$4**********"+currDetail);
		
		currDetailList = currDetail.getChildNodes();
		currDetailListLength = currDetailList.getLength();
		for (int i=0;i< currDetailListLength;i++)
		{
			nodeName = currDetailList.item(i).getNodeName();
			if (nodeName.equalsIgnoreCase("Attribute"))
			{
				updateStatus = currDetailList.item(i).getAttributes().getNamedItem("updateFlag").getNodeValue();
				break;
			}
		}
		return updateStatus;		
	}	

	/*private DistStkUpd getComponent(String componentName)throws Exception
    {
		DistStkUpd comp = null;
        try
        {
			System.out.println("Looking For Component.... :: " + componentName);
			AppConnectParm appConnect = new AppConnectParm();
			java.util.Properties p = appConnect.getProperty();
			InitialContext ctx = new InitialContext(p);
			DistStkUpdHome distStkUpdHome = (DistStkUpdHome)ctx.lookup(componentName);
			comp = distStkUpdHome.create();
			System.out.println(componentName+" Found.....");			
        }
        catch(Exception e)
        {
			throw e;
        }
		return comp;
	}*/
	private DistStkUpdLocal getComponent(String componentName)throws Exception
    {
		DistStkUpdLocal comp = null;
        try
        {
			System.out.println("Looking For Component.... :: " + componentName);
			AppConnectParm appConnect = new AppConnectParm();
			java.util.Properties p = appConnect.getProperty();
			InitialContext ctx = new InitialContext(p);
			 comp = (DistStkUpdLocal)ctx.lookup("ibase/DistStkUpd/local"); // for ejb3
			//comp = distStkUpdHome.create();
			System.out.println(componentName+" Found.....");			
        }
        catch(Exception e)
        {
			throw e;
        }
		return comp;
	}
}