package ibase.webitm.ejb.wms;
import ibase.system.config.AppConnectParm;
import ibase.webitm.utility.ITMException;
import java.io.Serializable;
import java.rmi.RemoteException;
import javax.naming.InitialContext;

import ibase.system.config.AppConnectParm;
import ibase.webitm.utility.ITMException;
import java.io.Serializable;
import java.rmi.RemoteException;
import javax.naming.InitialContext;


import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.ValidatorEJB;
import ibase.webitm.utility.ITMException;

import javax.ejb.Stateless;

/**
 * Session Bean implementation class LocationStockOccupancy
 */
@Stateless
public class ReplTaskShowDetail extends ValidatorEJB implements ReplTaskShowDetailRemote, ReplTaskShowDetailLocal
{
	/**
	 * Default constructor.
	 */
	public ReplTaskShowDetail()
	{
	}

	public String getTaskDetails() throws RemoteException, ITMException
	{

		String sql = "";
		ResultSet rs = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ConnDriver connDriver = new ConnDriver();
		StringBuffer xmlData = null;
		int replCreatecount =0;
		int replVerifycount = 0;
		int replPendingcount = 0;
		int pickCreatecount = 0;
		int pickVerifycount = 0;
		int pickPendingcount = 0;
		int activeCreatecount = 0;
		int activeVerifycount = 0;
		int activePendingcount = 0;	
        int mpackCreatecount = 0;
        int mpackVerifycount = 0;
        int mpackPendingcount = 0;
        int activereplCreatecount = 0;
        int activereplVerifycount = 0;
        int activereplPendingcount = 0; 
        int mpickCreatecount = 0;
        int mpickPendingcount = 0;
        int mpickVerifycount = 0;
        int hazmetcreateCount = 0;
        int hazmetpendingCount = 0;
        int hazmetverifyCount = 0;
        int count = 0;
		try
		{
			System.out.println("*************testing task deatils***************");
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			//replenishment start..
			sql =  "  SELECT COUNT(DISTINCT RH.REPL_ORDER) AS COUNT FROM WAVE_TASK W, WAVE_TASK_DET WT, REPL_ORD_HDR RH,  REPL_ORD_DET RT  " +   //created
					" WHERE W.WAVE_ID = WT.WAVE_ID AND WT.REF_ID = RH.REPL_ORDER AND WT.WAVE_STATUS ='C' AND WT.STATUS ='N' AND RH.REPL_ORDER = RT.REPL_ORDER " +
					" AND RH.REPL_ORDER = RT.REPL_ORDER AND W.CANCEL ='N' AND (RT.CANCEL_MODE ='N' OR RT.CANCEL_MODE IS NULL) AND RH.ORDER_TYPE NOT IN ('E','Q','T','P','I') ";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				replCreatecount = rs.getInt("COUNT");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;

			sql =   " SELECT COUNT (DISTINCT RH.REPL_ORDER) AS COUNT FROM REPL_ORD_HDR RH, REPL_ORD_DET RT, WAVE_TASK W, WAVE_TASK_DET WT, " +  //verified
					" REPL_ISS_HDR RIH, REPL_ISS_DET RIT WHERE RH.REPL_ORDER = RT.REPL_ORDER AND W.WAVE_ID = WT.WAVE_ID AND RH.REPL_ORDER = RIT.REPL_ORDER "+
					" AND RH.REPL_ORDER = RIH.REPL_ORDER AND RH.REPL_ORDER = WT.REF_ID AND RIH.CONFIRMED ='Y' AND WT.WAVE_STATUS ='V' " +
					" AND W.CANCEL ='N' AND (RT.CANCEL_MODE ='N' OR RT.CANCEL_MODE IS NULL) AND RH.ORDER_TYPE NOT IN ('E','Q','T','P','I') ";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				replVerifycount = rs.getInt("COUNT");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;

			sql = " SELECT COUNT (DISTINCT RH.REPL_ORDER) AS COUNT FROM REPL_ORD_HDR RH, WAVE_TASK W, WAVE_TASK_DET WT, REPL_ORD_DET RT" +
			      " WHERE W.WAVE_ID = WT.WAVE_ID AND RH.REPL_ORDER = WT.REF_ID AND WT.STATUS = 'N' "+
				  " AND WT.WAVE_STATUS = 'W' AND RT.REPL_ORDER = RH.REPL_ORDER AND W.CANCEL = 'N' AND (RT.CANCEL_MODE ='N' OR RT.CANCEL_MODE IS NULL) " +
				  " AND RH.ORDER_TYPE NOT IN ('E','Q','T','P','I') " ;
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				replPendingcount = rs.getInt("COUNT");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			//replenishment end..
			// active replenishment start..
			
			sql =   " SELECT COUNT(RH.REPL_ORDER) AS COUNT FROM WAVE_TASK W, WAVE_TASK_DET WT, REPL_ORD_HDR RH, REPL_ORD_DET RT " +   //created
					" WHERE W.WAVE_ID = WT.WAVE_ID AND WT.REF_ID = RH.REPL_ORDER AND RH.REPL_ORDER = RT.REPL_ORDER AND ( RT.CANCEL_MODE = 'N' OR RT.CANCEL_MODE IS NULL ) " +
					" AND W.CANCEL ='N' AND WT.STATUS ='N' AND WT.WAVE_STATUS ='C' AND RH.ORDER_TYPE IN ('E','Q','T','P','I') ";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				activereplCreatecount = rs.getInt("COUNT");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;

			sql =   " SELECT COUNT (RH.REPL_ORDER) AS COUNT FROM REPL_ORD_HDR RH, REPL_ORD_DET RT, WAVE_TASK W, WAVE_TASK_DET WT, " +  //verified
					" REPL_ISS_HDR RIH, REPL_ISS_DET RIT WHERE RH.REPL_ORDER = RT.REPL_ORDER AND W.WAVE_ID = WT.WAVE_ID AND RH.REPL_ORDER = RIT.REPL_ORDER "+
					" AND RH.REPL_ORDER = RIH.REPL_ORDER AND RH.REPL_ORDER = WT.REF_ID AND RIH.CONFIRMED ='Y' AND WT.WAVE_STATUS ='V' " +
					" AND (RT.CANCEL_MODE ='N' OR RT.CANCEL_MODE IS NULL) AND W.CANCEL ='N' AND RH.ORDER_TYPE IN ('E','Q','T','P','I') ";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				activereplVerifycount = rs.getInt("COUNT");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;

			sql = " SELECT COUNT (RH.REPL_ORDER) AS COUNT FROM REPL_ORD_HDR RH, WAVE_TASK W, WAVE_TASK_DET WT, REPL_ORD_DET RT" +
				  " WHERE W.WAVE_ID = WT.WAVE_ID AND RH.REPL_ORDER = WT.REF_ID AND RH.REPL_ORDER = RT.REPL_ORDER AND WT.STATUS = 'N' " +
				  " AND (RT.CANCEL_MODE ='N' OR RT.CANCEL_MODE IS NULL) AND W.CANCEL ='N' AND WT.WAVE_STATUS = 'W' AND RH.ORDER_TYPE IN ('E','Q','T','P','I') " ;
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				activereplPendingcount = rs.getInt("COUNT");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			// active replenishment end..
			
			//pick start
			sql =  " SELECT COUNT (PH.PICK_ORDER) AS COUNT FROM WAVE_TASK W, WAVE_TASK_DET WT, PICK_ORD_HDR PH, PICK_ORD_DET PT, ITEM I WHERE W.WAVE_ID = WT.WAVE_ID "+
				   " AND WT.REF_ID = PH.PICK_ORDER AND PT.ITEM_CODE = I.ITEM_CODE AND PT.PICK_ORDER = WT.REF_ID AND WT.SALE_ORDER = PT.SALE_ORDER AND (I.HAZARDOUS ='N' OR I.HAZARDOUS IS NULL )  AND WT.WAVE_STATUS ='C' AND WT.STATUS ='N' " +
				   " AND W.CANCEL ='N' AND WT.REF_SER IN('C-PICK','M-PICK','P-PICK') ";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				pickCreatecount = rs.getInt("COUNT");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;

			sql =   " SELECT COUNT (PH.PICK_ORDER) AS COUNT FROM  PICK_ORD_HDR PH, PICK_ORD_DET PT, WAVE_TASK W, WAVE_TASK_DET WT, PICK_ISS_HDR PIH, PICK_ISS_DET PIT, ITEM I " +
					" WHERE W.WAVE_ID = WT.WAVE_ID AND PH.PICK_ORDER = PIT.PICK_ORDER AND PH.PICK_ORDER = PIH.PICK_ORDER AND PH.PICK_ORDER = WT.REF_ID " +
					" AND PT.ITEM_CODE = I.ITEM_CODE AND WT.SALE_ORDER = PT.SALE_ORDER AND (I.HAZARDOUS ='N' OR I.HAZARDOUS IS NULL) AND PIH.CONFIRMED ='Y' AND W.CANCEL ='N' AND WT.REF_SER IN('C-PICK','M-PICK','P-PICK') AND WT.WAVE_STATUS = 'V' " ;

			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				pickVerifycount = rs.getInt("COUNT");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;

			sql =  " SELECT COUNT (PH.PICK_ORDER) AS COUNT FROM PICK_ORD_HDR PH, PICK_ORD_DET PT, WAVE_TASK W, WAVE_TASK_DET WT, ITEM I WHERE W.WAVE_ID = WT.WAVE_ID " +
				   " AND PH.PICK_ORDER = WT.REF_ID AND PT.ITEM_CODE = I.ITEM_CODE AND PT.PICK_ORDER = WT.REF_ID AND WT.SALE_ORDER = PT.SALE_ORDER AND (I.HAZARDOUS ='N' OR I.HAZARDOUS IS NULL) AND WT.STATUS = 'N' AND W.CANCEL ='N' " +
				   " AND WT.WAVE_STATUS = 'W' AND WT.REF_SER IN('C-PICK','M-PICK','P-PICK') ";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				pickPendingcount = rs.getInt("COUNT");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			//pick end

			//Active pick start 
			sql =  " SELECT COUNT (PH.PICK_ORDER) AS COUNT FROM WAVE_TASK W, WAVE_TASK_DET WT, PICK_ORD_HDR PH, PICK_ORD_DET PT WHERE W.WAVE_ID = WT.WAVE_ID "+
				   " AND WT.REF_ID = PH.PICK_ORDER AND PH.PICK_ORDER = PT.PICK_ORDER AND W.CANCEL ='N' AND WT.STATUS ='N' AND WT.WAVE_STATUS ='C' AND WT.REF_SER IN('A-PICK') ";

			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				activeCreatecount = rs.getInt("COUNT");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;

			sql =   " SELECT COUNT (PH.PICK_ORDER) AS COUNT FROM PICK_ORD_HDR PH, PICK_ORD_DET PT, WAVE_TASK W, WAVE_TASK_DET WT, PICK_ISS_HDR PIH, PICK_ISS_DET PIT " +
					" WHERE PH.PICK_ORDER = PT.PICK_ORDER AND W.WAVE_ID = WT.WAVE_ID AND PH.PICK_ORDER = PIT.PICK_ORDER AND PH.PICK_ORDER = PIH.PICK_ORDER AND PH.PICK_ORDER = WT.REF_ID " +
					" AND W.CANCEL ='N' AND PIH.CONFIRMED ='Y' AND WT.REF_SER IN ('A-PICK') AND WT.WAVE_STATUS = 'V' " ;

			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				activeVerifycount = rs.getInt("COUNT");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;

			sql =  " SELECT COUNT (PH.PICK_ORDER) AS COUNT FROM PICK_ORD_HDR PH, WAVE_TASK W, WAVE_TASK_DET WT,PICK_ORD_DET PT WHERE W.WAVE_ID = WT.WAVE_ID AND PH.PICK_ORDER = WT.REF_ID " +
				   " AND PH.PICK_ORDER = PT.PICK_ORDER AND W.CANCEL ='N' AND WT.STATUS = 'N' AND WT.WAVE_STATUS = 'W' AND WT.REF_SER IN ('A-PICK') " ;
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				activePendingcount = rs.getInt("COUNT");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			// Active pick end
			
		/*	//Master pick start 
			sql =  " SELECT COUNT (DISTINCT PH.PICK_ORDER) AS COUNT FROM WAVE_TASK W, WAVE_TASK_DET WT, PICK_ORD_HDR PH WHERE W.WAVE_ID = WT.WAVE_ID "+
				   " AND WT.REF_ID = PH.PICK_ORDER AND WT.WAVE_STATUS ='C' AND WT.STATUS = 'N' AND WT.REF_SER IN('M-PICK') ";

			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				mpickCreatecount = rs.getInt("COUNT");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;

			sql = " SELECT COUNT (DISTINCT PH.PICK_ORDER) AS COUNT FROM  PICK_ORD_HDR PH, PICK_ORD_DET PT, WAVE_TASK W, WAVE_TASK_DET WT, PICK_ISS_HDR PIH, PICK_ISS_DET PIT " +
				  " WHERE PH.PICK_ORDER = PT.PICK_ORDER AND W.WAVE_ID = WT.WAVE_ID AND PH.PICK_ORDER = PIT.PICK_ORDER AND PH.PICK_ORDER = PIH.PICK_ORDER AND PH.PICK_ORDER = WT.REF_ID " +
				  " AND PIH.CONFIRMED ='Y' AND WT.REF_SER IN ('M-PICK') AND WT.WAVE_STATUS = 'V' " ;

			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				mpickVerifycount = rs.getInt("COUNT");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;

			sql =  " SELECT COUNT (DISTINCT PH.PICK_ORDER) AS COUNT FROM PICK_ORD_HDR PH, WAVE_TASK W, WAVE_TASK_DET WT WHERE W.WAVE_ID = WT.WAVE_ID AND PH.PICK_ORDER = WT.REF_ID " +
				   " AND WT.STATUS = 'N' AND WT.WAVE_STATUS = 'W' AND WT.REF_SER IN ('M-PICK') " ;
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				mpickPendingcount = rs.getInt("COUNT");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			// Master pick end
*/			
			//Master Pack start.
			sql =  " SELECT COUNT (DISTINCT PH.TRAN_ID) AS COUNT FROM WAVE_TASK W, WAVE_TASK_DET WT, PACK_HDR PH WHERE W.WAVE_ID = WT.WAVE_ID AND WT.REF_ID = PH.TRAN_ID  " +
			       " AND WT.STATUS ='N' AND W.CANCEL ='N' AND WT.REF_SER IN ('M-PACK') AND WT.WAVE_STATUS ='C' " ;
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				mpackCreatecount = rs.getInt("COUNT");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			
			sql = " SELECT COUNT(DISTINCT WT.REF_ID) AS COUNT FROM WAVE_TASK W, WAVE_TASK_DET WT, PACK_HDR PH WHERE W.WAVE_ID = WT.WAVE_ID AND WT.REF_ID = PH.TRAN_ID " +
				  " AND WT.REF_SER IN ('M-PACK') AND WT.STATUS ='Y' AND W.CANCEL ='N' AND WT.WAVE_STATUS ='V' " ;
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				mpackVerifycount = rs.getInt("COUNT");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			
			sql = " SELECT COUNT(DISTINCT WT.REF_ID) AS COUNT FROM WAVE_TASK W, WAVE_TASK_DET WT, PACK_HDR PH WHERE W.WAVE_ID = WT.WAVE_ID AND WT.REF_ID = PH.TRAN_ID " +
				  " AND WT.REF_SER IN ('M-PACK') AND WT.STATUS ='N' AND W.CANCEL ='N' AND WT.WAVE_STATUS ='W' " ;
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				mpackPendingcount = rs.getInt("COUNT");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			//Master Pack end.
			//Hazmet Case Picking start
			sql =   " SELECT COUNT (PH.PICK_ORDER) AS COUNT FROM WAVE_TASK W, WAVE_TASK_DET WT, PICK_ORD_HDR PH,ITEM I,PICK_ORD_DET PT WHERE W.WAVE_ID = WT.WAVE_ID " +
					" AND WT.REF_ID = PH.PICK_ORDER AND PH.PICK_ORDER = PT.PICK_ORDER AND PT.ITEM_CODE = I.ITEM_CODE AND I.HAZARDOUS ='Y' AND WT.WAVE_STATUS ='C' " + 
					" AND WT.STATUS = 'N' AND WT.REF_SER IN('C-PICK') " ;
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				hazmetcreateCount = rs.getInt("COUNT");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;

			sql =   " SELECT COUNT (PH.PICK_ORDER) AS COUNT FROM WAVE_TASK W, WAVE_TASK_DET WT, PICK_ORD_HDR PH,ITEM I,PICK_ORD_DET PT WHERE W.WAVE_ID = WT.WAVE_ID " +
					" AND WT.REF_ID = PH.PICK_ORDER AND PH.PICK_ORDER = PT.PICK_ORDER AND PT.ITEM_CODE = I.ITEM_CODE AND I.HAZARDOUS ='Y' AND WT.WAVE_STATUS ='W' " + 
					" AND WT.STATUS ='N' AND WT.REF_SER IN('C-PICK') " ;
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				hazmetpendingCount = rs.getInt("COUNT");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;

			sql =   " SELECT COUNT (PH.PICK_ORDER) AS COUNT FROM WAVE_TASK W, WAVE_TASK_DET WT, PICK_ORD_HDR PH,ITEM I,PICK_ORD_DET PT WHERE W.WAVE_ID = WT.WAVE_ID " +
					" AND WT.REF_ID = PH.PICK_ORDER AND PH.PICK_ORDER = PT.PICK_ORDER AND PT.ITEM_CODE = I.ITEM_CODE AND I.HAZARDOUS ='Y' AND WT.WAVE_STATUS ='V' " + 
					" AND WT.REF_SER IN('C-PICK') " ;
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				hazmetverifyCount = rs.getInt("COUNT");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			//Hazmet Case Picking end
			
            int totalRepltasks = replCreatecount + replVerifycount + replPendingcount ;
            int totalpicktasks = pickCreatecount + pickVerifycount + pickPendingcount ;
            int totalactiveTasks = activeCreatecount + activeVerifycount + activePendingcount ; 
            int totalmpacktasks = mpackCreatecount + mpackVerifycount + mpackPendingcount ;
            int totalactiverepltasks = activereplCreatecount + activereplVerifycount + activereplPendingcount ;
            //int totalmpicktasks = mpickCreatecount + mpickVerifycount + mpickPendingcount ;
            int totalhazmetcasepicks = hazmetcreateCount + hazmetverifyCount + hazmetpendingCount ;
  
			xmlData = new StringBuffer("<?xml version='1.0'?> <Root>");
			xmlData.append("<Detail>");
			//xmlData.append("<Detail domID="+"\""+count+"\">");
			xmlData.append("<Replenishments>");
			xmlData.append( "<repl_create_count><![CDATA[" ).append(replCreatecount).append( "]]></repl_create_count>\r\n" );
			xmlData.append( "<repl_pending_count><![CDATA[" ).append(replPendingcount).append( "]]></repl_pending_count>\r\n" );
			xmlData.append( "<repl_verify_count><![CDATA[" ).append(replVerifycount).append( "]]></repl_verify_count>\r\n" );
			xmlData.append( "<total_repl_tasks><![CDATA[" ).append(totalRepltasks).append( "]]></total_repl_tasks>\r\n" );
			xmlData.append("</Replenishments>");
			xmlData.append("<Activereplenishments>");
			xmlData.append( "<activerepl_create_count><![CDATA[" ).append(activereplCreatecount).append( "]]></activerepl_create_count>\r\n" );
			xmlData.append( "<activerepl_pending_count><![CDATA[" ).append(activereplPendingcount).append( "]]></activerepl_pending_count>\r\n" );
			xmlData.append( "<activerepl_verify_count><![CDATA[" ).append(activereplVerifycount).append( "]]></activerepl_verify_count>\r\n" );
			xmlData.append( "<total_activerepl_tasks><![CDATA[" ).append(totalactiverepltasks).append( "]]></total_activerepl_tasks>\r\n" );
			xmlData.append("</Activereplenishments>");	
			xmlData.append("<pickings>");
			xmlData.append( "<pick_create_count><![CDATA[" ).append(pickCreatecount).append( "]]></pick_create_count>\r\n" );
			xmlData.append( "<pick_pending_count><![CDATA[" ).append(pickPendingcount).append( "]]></pick_pending_count>\r\n" );
			xmlData.append( "<pick_verify_count><![CDATA[" ).append(pickVerifycount).append( "]]></pick_verify_count>\r\n" );
			xmlData.append( "<total_pick_tasks><![CDATA[" ).append(totalpicktasks).append( "]]></total_pick_tasks>\r\n" );
			xmlData.append("</pickings>");
			xmlData.append("<Activepickings>");
			xmlData.append( "<active_create_count><![CDATA[" ).append(activeCreatecount).append( "]]></active_create_count>\r\n" );
			xmlData.append( "<active_pending_count><![CDATA[" ).append(activePendingcount).append( "]]></active_pending_count>\r\n" );
			xmlData.append( "<active_verify_count><![CDATA[" ).append(activeVerifycount).append( "]]></active_verify_count>\r\n" );
			xmlData.append( "<total_active_tasks><![CDATA[" ).append(totalactiveTasks).append( "]]></total_active_tasks>\r\n" );
			xmlData.append("</Activepickings>");
			xmlData.append("<Masterpackings>");
			xmlData.append( "<mpack_create_count><![CDATA[" ).append(mpackCreatecount).append( "]]></mpack_create_count>\r\n" );
			xmlData.append( "<mpack_pending_count><![CDATA[" ).append(mpackPendingcount).append( "]]></mpack_pending_count>\r\n" );
			xmlData.append( "<mpack_verify_count><![CDATA[" ).append(mpackVerifycount).append( "]]></mpack_verify_count>\r\n" );
			xmlData.append( "<total_mpack_tasks><![CDATA[" ).append(totalmpacktasks).append( "]]></total_mpack_tasks>\r\n" );
			xmlData.append("</Masterpackings>");
		/*	xmlData.append("<Masterpickings>");
			xmlData.append( "<mpick_create_count><![CDATA[" ).append(mpickCreatecount).append( "]]></mpick_create_count>\r\n" );
			xmlData.append( "<mpick_pending_count><![CDATA[" ).append(mpickPendingcount).append( "]]></mpick_pending_count>\r\n" );
			xmlData.append( "<mpick_verify_count><![CDATA[" ).append(mpickVerifycount).append( "]]></mpick_verify_count>\r\n" );
			xmlData.append( "<total_mpick_tasks><![CDATA[" ).append(totalmpicktasks).append( "]]></total_mpick_tasks>\r\n" );
			xmlData.append("</Masterpickings>");*/
			xmlData.append("<Hazmetpickings>");
			xmlData.append( "<hazmet_create_count><![CDATA[" ).append(hazmetcreateCount).append( "]]></hazmet_create_count>\r\n" );
			xmlData.append( "<hazmet_pending_count><![CDATA[" ).append(hazmetpendingCount).append( "]]></hazmet_pending_count>\r\n" );
			xmlData.append( "<hazmet_verify_count><![CDATA[" ).append(hazmetverifyCount).append( "]]></hazmet_verify_count>\r\n" );
			xmlData.append( "<total_hazmet_tasks><![CDATA[" ).append(totalhazmetcasepicks).append( "]]></total_hazmet_tasks>\r\n" );
			xmlData.append("</Hazmetpickings>");
			xmlData.append("</Detail>");
			xmlData.append("</Root>");		
		}
		catch (Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		} 
		finally
		{
			try
			{
				if (conn != null)
				{
					if (rs != null)
					{
						rs.close();
						rs = null;
					}						
					if (pstmt != null)
					{
						pstmt.close();
						pstmt = null;
					}
				
					conn.close();
					conn = null;
				}
				conn = null;
			}
			catch (Exception d)
			{
				d.printStackTrace();
				throw new ITMException(d);
			}
		}
		return xmlData.toString();
	}
}
