/********************************************************
	Title : PickOrderIC
	Date  : 12/06/2012
	Developer: Mahesh Patidar
 ********************************************************/

package ibase.webitm.ejb.wms;

import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.ValidatorEJB;
import ibase.webitm.ejb.dis.DistCommon;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;
import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
//Changed by Rohan on 10-04-12 for import package
import java.util.HashMap;

import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

@javax.ejb.Stateless
public class PickOrderIC extends ValidatorEJB implements PickOrderICLocal,PickOrderICRemote
{
	
	DistCommon discommon = new DistCommon();
	public String wfValData() throws RemoteException, ITMException
	{
		return "";
	}
	public String wfValData(String xmlString, String xmlString1, String xmlString2, String objContext, String editFlag, String xtraParams) throws RemoteException, ITMException
	{
		Document dom = null;
		Document dom1 = null;
		Document dom2 = null;
		String errString = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		System.out.println("WFVALDATA 111111");
		try
		{
			System.out.println("xmlString [" + xmlString + "]");
			System.out.println("xmlString1 [" + xmlString1 + "]");
			System.out.println("xmlString2 [" + xmlString2 + "]");
			if(xmlString != null && xmlString.trim().length()!=0)
			{
				dom = genericUtility.parseString(xmlString); 
			}
			if(xmlString1 != null && xmlString1.trim().length()!=0)
			{
				dom1 = genericUtility.parseString(xmlString1); 
			}
			if(xmlString2 != null && xmlString2.trim().length()!=0)
			{
				dom2 = genericUtility.parseString(xmlString2); 
			}
			errString = wfValData( dom, dom1, dom2, objContext, editFlag, xtraParams );
			System.out.println ( "ErrString: " + errString);
		}
		catch(Exception e)
		{
			System.out.println ( "Exception: PickOrderIC: wfValData(String xmlString): " + e.getMessage() + ":" );
			errString = genericUtility.createErrorString(e);
			e.printStackTrace();
			throw new ITMException(e);
		}
		System.out.println ( "Returning from PickOrderIC wfValData" );
		return ( errString ); 
	}
	
	public String wfValData(Document dom, Document dom1, Document dom2, String objContext,String editFlag, String xtraParams ) throws RemoteException, ITMException
	{
		String errString = "";
		String siteCode = "";
		ResultSet rs = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
        GenericUtility genericUtility = GenericUtility.getInstance();
		
		StringBuffer errStringXml = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root><Errors>");
		String errorType = "", errCode = "";
		ArrayList<String> errList = new ArrayList<String>();
		ArrayList<String> errFields = new ArrayList<String>();
		NodeList parentList = null;
		NodeList childList = null;
		int noOfChilds ;
		String childNodeName = "";
		Node childNode =null;
		String userId = "";

		int cnt = 0;
		String columnValue = "";
		String columnValue1 = "";
		String updateStatus = "";
		String lotNo = "";
		String locCode = "";
		String sql = "";
		String lotSl = "";
		//Changed by Rohan on 10/8/12 to define variables.start
		/*
		int caseSize = 0; 
		int caseQty = 0;
		*/
		double caseSize = 0.0; 
		double caseQty = 0.0;
		double qty = 0.0;
		//Changed by Rohan on 10/8/12 to define variables.end
		String pickType = "";
		String pickOrder = "";
		String pickLineNo = "";
		String waveId = "";
		String status = "";
		String sqlHold = "";
		String statusHold = "";
		String casePickLoc = "";
		String itemCode = "";
		//Changed by sumit on 02/10/12  created variable cancel
		String cancel ="";
		DistCommon discommon = new DistCommon();	
		//Changed by Rohan on 10/8/12 to define variables.end
		int currentFormNo = 0;		
		ConnDriver connDriver = null;
		
		try
		{
			connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			
			userId = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginCode" );

			if ( objContext != null && objContext.trim().length() > 0 )
			{
				currentFormNo = Integer.parseInt( objContext );
			}
			
			switch(currentFormNo)
			{
				
				//Changed by Rohan on 11/8/12 to do not allow to change pick order in scan is complete.start
				case 1:
				{
					parentList = dom.getElementsByTagName( "Detail1" );
					for(int i =0; i< parentList.getLength();i++)
                    {
                        updateStatus = getCurrentUpdateFlag(dom.getElementsByTagName("Detail1").item(i));
                    }
					childList = parentList.item( 0 ).getChildNodes();
					noOfChilds = childList.getLength();
					
					if(updateStatus.trim().equals("A") && editFlag.trim().equals("E"))
					{
						errList.add( "VMADDNAL" );
						errFields.add( childNodeName.toLowerCase() );
					}
					for (int ctr = 0; ctr < noOfChilds; ctr++)
					{	
						
						childNode = childList.item( ctr );
						childNodeName = childNode.getNodeName();
			 			if ( childNode != null && childNode.getFirstChild() != null )
						{
							columnValue = childNode.getFirstChild().getNodeValue();
						}
						
						
						if(childNodeName.equalsIgnoreCase("wave_id"))
						{
							waveId = genericUtility.getColumnValue("wave_id", dom);
							
							//Changed By Pragyan Not Required this validation.start
							/*sql = "SELECT STATUS FROM WAVE_TASK_DET WHERE WAVE_ID = ? ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, waveId);
							
							rs = pstmt.executeQuery();
							
							while( rs.next() )
							{
								status = rs.getString("STATUS");
								if("Y".equalsIgnoreCase(status))
								{
									errList.add( "VTSACN" );
									errFields.add( childNodeName.toLowerCase() );
									break;
								}
							}
							
							if(rs != null)
							{	
								rs.close();
								rs = null;
							}
							if(pstmt != null)
							{	
								pstmt.close();
								pstmt = null;
							}*/
							//Changed by sumit on 02/10/12 getting value of cancel
							//Changed By Pragyan Not Required this validation.end
							//sqlHold = "SELECT HOLD_STATUS FROM WAVE_TASK WHERE WAVE_ID = ?";
							sqlHold = "SELECT HOLD_STATUS, CANCEL FROM WAVE_TASK WHERE WAVE_ID = ?";
							pstmt = conn.prepareStatement(sqlHold);
							pstmt.setString(1, waveId);
							
							rs = pstmt.executeQuery();
							
							if( rs.next() )
							{
								statusHold = rs.getString("HOLD_STATUS");
								//Changed by sumit on 02/10/12 
								cancel = rs.getString("CANCEL");
							}
							//Changed by sumit on 02/10/12 adding contition of cancel
							//if("Y".equalsIgnoreCase(statusHold))
							if("Y".equalsIgnoreCase(statusHold) || "Y".equalsIgnoreCase(cancel))
							{
								errList.add( "VTHOLDTASK" );
								errFields.add( childNodeName.toLowerCase() );
							}
							if(rs != null)
							{	
								rs.close();
								rs = null;
							}
							if(pstmt != null)
							{	
								pstmt.close();
								pstmt = null;
							}
						}
					}
					//Changed by Rohan on 11/8/12 to do not allow to change pick order in scan is complete.end
				}
				break;
				//Changed by Rohan on 11/8/12 to do not allow to change pick order in scan is complete.end
      			case 2 :
				{
					parentList = dom.getElementsByTagName( "Detail2" );
					for(int i =0; i< parentList.getLength();i++)
                    {
                        updateStatus = getCurrentUpdateFlag(dom.getElementsByTagName("Detail2").item(i));
                    }
					childList = parentList.item( 0 ).getChildNodes();
					noOfChilds = childList.getLength();
					System.out.println("EDIT FLAg IS["+updateStatus+"]");
					/*if(updateStatus.trim().equals("A") && editFlag.trim().equals("E"))
					{
						errList.add( "VMADDNAL" );
						errFields.add( childNodeName.toLowerCase() );
					}*/
					for (int ctr = 0; ctr < noOfChilds; ctr++)
					{	
						
						childNode = childList.item( ctr );
						childNodeName = childNode.getNodeName();
			 			if ( childNode != null && childNode.getFirstChild() != null )
						{
							columnValue = childNode.getFirstChild().getNodeValue();
						}
						
						if ( childNodeName.equalsIgnoreCase("item_code") )
						{
							columnValue = genericUtility.getColumnValue("item_code", dom);
							columnValue1 = genericUtility.getColumnValue("site_code", dom);
							System.out.println("ITEM CODE["+columnValue+"]");
							if(columnValue != null && columnValue.trim().length() > 0)
							{
								System.out.println("ITEM CODE["+columnValue+"]");
								sql = "select count(*) from stock where item_code = ? and site_code = ? ";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, columnValue);
								pstmt.setString(2, columnValue1);
								rs = pstmt.executeQuery();
								if( rs.next() )
								{
									cnt = rs.getInt(1);
									if(cnt == 0)
									{
										errList.add( "VTINVITM" );
										errFields.add( childNodeName.toLowerCase() );	
									}
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
							}
							else
							{
								errList.add( "VMITEMCD" );
								errFields.add( childNodeName.toLowerCase() );
							}
						} 
						
						else if ( childNodeName.equalsIgnoreCase("no_art") )
						{
							columnValue = genericUtility.getColumnValue("no_art", dom);
							System.out.println("no art["+columnValue+"]");
							if(columnValue == null || columnValue.trim().length() == 0)
							{
								errList.add( "VTARTNULL" );
								errFields.add( childNodeName.toLowerCase() );
							}
						}
						
						else if ( childNodeName.equalsIgnoreCase("loc_code") )
						{
							columnValue = genericUtility.getColumnValue("loc_code", dom);
							siteCode = genericUtility.getColumnValue("site_code", dom);
							//Changed by Rohan on 11/8/12 to define variables properly
							columnValue1 = genericUtility.getColumnValue("item_code", dom);
							itemCode = genericUtility.getColumnValue("item_code", dom);
							lotSl = genericUtility.getColumnValue("lot_sl", dom);
							lotNo = genericUtility.getColumnValue("lot_no", dom);
							System.out.println("loc CODE["+columnValue+"]");
							if(columnValue != null && columnValue.trim().length() > 0)
							{
								System.out.println("loc CODE["+columnValue+"]");
								//Changed by Rohan on 10/08/12 to get Pick order.start
								pickOrder = genericUtility.getColumnValue("pick_order", dom);
								pickLineNo = genericUtility.getColumnValue("line_no", dom);
								
								locCode = getStockLocCode(pickOrder,pickLineNo,columnValue,itemCode,siteCode,lotNo,lotSl,conn);
								//Changed by Rohan on 10/08/12 to get Pick order.end
								System.out.println("Loc Code4"+locCode);
								sql = "select count(*) from stock where item_code = ? and site_code = ? and loc_code = ? ";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, itemCode);
								pstmt.setString(2, siteCode);
								//Changed by Rohan on 10/8/12 to set loc code
								
								//pstmt.setString(3, columnValue);
								pstmt.setString(3, locCode);
								
								rs = pstmt.executeQuery();
								if( rs.next() )
								{
									cnt = rs.getInt(1);
									if(cnt == 0)
									{
										errList.add( "VXSTKLOC" );
										errFields.add( childNodeName.toLowerCase() );	
									}
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
							}
							else
							{
								errList.add( "VMLOC4" );
								errFields.add( childNodeName.toLowerCase() );
							}								
						}
						
						else if ( childNodeName.equalsIgnoreCase("lot_no") )
						{
							columnValue = genericUtility.getColumnValue("lot_no", dom);
							siteCode = genericUtility.getColumnValue("site_code", dom);
							//Changed by Rohan on 11/8/12 to define variables properly
							//columnValue1 = genericUtility.getColumnValue("item_code", dom);
							itemCode = genericUtility.getColumnValue("item_code", dom);
							lotSl = genericUtility.getColumnValue("lot_sl", dom);
							lotNo = genericUtility.getColumnValue("lot_no", dom);
							locCode = genericUtility.getColumnValue("loc_code", dom);
							System.out.println("lot no["+columnValue+"]");
							if(columnValue != null && columnValue.trim().length() > 0)
							{
								//Changed by Rohan on 10/08/12 to get Pick order.start
								pickOrder = genericUtility.getColumnValue("pick_order", dom);
								pickLineNo = genericUtility.getColumnValue("line_no", dom);
								locCode = getStockLocCode(pickOrder,pickLineNo,locCode,itemCode,siteCode,columnValue,lotSl,conn);
								//Changed by Rohan on 10/08/12 to get Pick order.end
								System.out.println("Loc Code3"+locCode);
								System.out.println("lot no["+columnValue+"]");
								sql = "select count(*) from stock where item_code = ? and site_code = ? and lot_no = ? ";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, itemCode);
								pstmt.setString(2, siteCode);
								pstmt.setString(3, columnValue);
								rs = pstmt.executeQuery();
								if( rs.next() )
								{
									cnt = rs.getInt(1);
									if(cnt == 0)
									{
										errList.add( "VTINVLOT" );
										errFields.add( childNodeName.toLowerCase() );	
									}
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
							}
							else
							{
								errList.add( "VTLOTNO" );
								errFields.add( childNodeName.toLowerCase() );
							}								
						}
						
						else if ( childNodeName.equalsIgnoreCase("lot_sl") )
						{
							columnValue = genericUtility.getColumnValue("lot_sl", dom);
							lotNo = genericUtility.getColumnValue("lot_no", dom);
							locCode = genericUtility.getColumnValue("loc_code", dom);
							siteCode = genericUtility.getColumnValue("site_code", dom);
							itemCode = genericUtility.getColumnValue("item_code", dom);
							System.out.println("lot sl["+columnValue+"]");
							
							
							
							if(columnValue != null && columnValue.trim().length() > 0)
							{
								//Changed by Rohan on 10/08/12 to get Pick order.start
								pickOrder = genericUtility.getColumnValue("pick_order", dom);
								pickLineNo = genericUtility.getColumnValue("line_no", dom);
								locCode = getStockLocCode(pickOrder,pickLineNo,locCode,itemCode,siteCode,lotNo,columnValue,conn);
								//Changed by Rohan on 10/08/12 to get Pick order.end
								System.out.println("Loc Code2"+locCode);
								System.out.println("lot sl["+columnValue+"]");
								sql = "select count(*) from stock where item_code = ? and site_code = ? and loc_code = ? and lot_no = ? and lot_sl = ? ";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, columnValue1);
								pstmt.setString(2, siteCode);
								pstmt.setString(3, locCode);
								pstmt.setString(4, lotNo);
								pstmt.setString(5, columnValue);
								rs = pstmt.executeQuery();
								if( rs.next() )
								{
									cnt = rs.getInt(1);
									if(cnt == 0)
									{
										errList.add( "VTINVLOTSL" );
										errFields.add( childNodeName.toLowerCase() );	
									}
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
							}
							else
							{
								errList.add( "VTLOTSL" );
								errFields.add( childNodeName.toLowerCase() );
							}								
						}
						
						else if ( childNodeName.equalsIgnoreCase("sale_order") )
						{
							columnValue = genericUtility.getColumnValue("sale_order", dom);
							
							
							
							if(columnValue != null && columnValue.trim().length() > 0)
							{
								//Changed by Rohan on 10/08/12 to get Pick order.start
								pickOrder = genericUtility.getColumnValue("pick_order", dom);
								pickLineNo = genericUtility.getColumnValue("line_no", dom);
								sql = "select count(*) from pick_ord_det where sale_order <> ? and pick_order = ? ";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, columnValue);
								pstmt.setString(2, pickOrder);
								rs = pstmt.executeQuery();
								if( rs.next() )
								{
									cnt = rs.getInt(1);
									if(cnt > 0)
									{
										errList.add( "VTSORDMM" );
										errFields.add( childNodeName.toLowerCase() );	
									}
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
							}
							else
							{
								errList.add( "VTLOTSL" );
								errFields.add( childNodeName.toLowerCase() );
							}								
						}
						
						else if ( childNodeName.equalsIgnoreCase("quantity") )
						{
							
							columnValue = genericUtility.getColumnValue("quantity", dom);
							System.out.println("qty["+columnValue+"]");
							if(columnValue == null || columnValue.trim().length() == 0)
							{
								errList.add( "COQNTYN" );
								errFields.add( childNodeName.toLowerCase() );
							}
							else
							{
								//Changed by Rohan on 04-03-12 for chnging quanity to parse double
								//int qty = Integer.parseInt(columnValue);
								qty = Double.parseDouble(columnValue);
								
								System.out.println("qty["+qty+"]");
								
								//Changed by Rohan on 11/08/12 to define variables properly
								//columnValue = genericUtility.getColumnValue("lot_sl", dom);
								lotSl = genericUtility.getColumnValue("lot_sl", dom);
								lotNo = genericUtility.getColumnValue("lot_no", dom);
								locCode = genericUtility.getColumnValue("loc_code", dom);
								siteCode = genericUtility.getColumnValue("site_code", dom);
								
								//Changed by Rohan on 11/08/12 to define variables properly
								//columnValue1 = genericUtility.getColumnValue("item_code", dom);
								itemCode = genericUtility.getColumnValue("item_code", dom);
								
								//Changed by Rohan on 10/8/12 to check qty in multiple of case size.start
								pickType = genericUtility.getColumnValue("pick_type", dom2);
								
								pickOrder = genericUtility.getColumnValue("pick_order", dom);
								pickLineNo = genericUtility.getColumnValue("line_no", dom);
								
								if(!"A".equalsIgnoreCase(pickType))
								{	
									
									caseSize = getShipperSize(itemCode,conn);

									caseQty = qty % caseSize;
					
									if(caseQty > 0)
									{
										errList.add( "VTCASEQTY" );
										errFields.add( childNodeName.toLowerCase() );	
									}
								}	
								//Changed by Rohan on 10/8/12 to check qty in multiple of case size.end
								
								//Changed by Rohan on 10/8/12 to check stock if repalinshment present
								locCode = getStockLocCode(pickOrder,pickLineNo,locCode,itemCode,siteCode,lotNo,lotSl,conn);
								
								System.out.println("Loc Code1"+locCode);
								//Chnged by Rohan on to check alloacte qty in case of quantity
								//sql = "select count(*) from stock where item_code = ? and site_code = ? and loc_code = ? and lot_no = ? and lot_sl = ? and quantity >= ?";
								sql = "select count(*) from stock where item_code = ? and site_code = ? and loc_code = ? and lot_no = ? and lot_sl = ?";// and alloc_qty >= ?";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, itemCode);
								pstmt.setString(2, siteCode);
								pstmt.setString(3, locCode);
								pstmt.setString(4, lotNo);
								pstmt.setString(5, lotSl);
								//pstmt.setInt(6, qty);
								rs = pstmt.executeQuery();
								if( rs.next() )
								{
									cnt = rs.getInt(1);
									if(cnt == 0)
									{
										errList.add( "VTQTYA1" );
										errFields.add( childNodeName.toLowerCase() );	
									}
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
								
							}
						}
						
						else if ( childNodeName.equalsIgnoreCase("exp_lev") )
						{
							columnValue = genericUtility.getColumnValue("exp_lev", dom);
							if(columnValue == null || columnValue.trim().length() == 0)
							{
								errList.add( "VTEXP4" );
								errFields.add( childNodeName.toLowerCase() );
							}
						}
						else if ( childNodeName.equalsIgnoreCase("pick_order") )
						{
							int pickQty = 0;
							String updateQty = "";
							String replOrder = "";
							String replLineNo = "";
							String confirm = "N";
							pickOrder = genericUtility.getColumnValue("pick_order", dom);
							pickLineNo = genericUtility.getColumnValue("line_no", dom);
							updateQty = genericUtility.getColumnValue("quantity", dom);
							
							sql = "SELECT REPL_ORDER,LINE_NO__REPL FROM PICK_ORD_DET WHERE PICK_ORDER = ? AND LINE_NO = ?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, pickOrder);
							pstmt.setString(2, pickLineNo);
							rs = pstmt.executeQuery();
							if( rs.next() )
							{
								replOrder = rs.getString("REPL_ORDER");
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;
							
							if(replOrder != null && replOrder.length() > 0)
							{
								sql = "SELECT STATUS FROM WAVE_TASK_DET WHERE REF_ID = ?";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, replOrder);
								//pstmt.setString(2, pickLineNo);
								rs = pstmt.executeQuery();
								if( rs.next() )
								{
									confirm = rs.getString("STATUS");
								}
								
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
								
								if(confirm.equalsIgnoreCase("N"))
								{
									errList.add( "REPLSCANF" );
									errFields.add( childNodeName.toLowerCase() );
								}
							}
							
							
							/*if(Integer.parseInt(updateQty) > pickQty)
							{
								errList.add( "VTQTYOVR" );
								errFields.add( childNodeName.toLowerCase() );
							}*/
							
							
						}
						//Changed by rohan on 11/8/12 to check active Replanishement Quantity.start
						/*else if ( childNodeName.equalsIgnoreCase("pick_order") )
						{
							int pickQty = 0;
							String updateQty = "";
							
							pickOrder = genericUtility.getColumnValue("pick_order", dom);
							pickLineNo = genericUtility.getColumnValue("line_no", dom);
							updateQty = genericUtility.getColumnValue("quantity", dom);
							
							sql = "SELECT QUANTITY FROM PICK_ORD_DET WHERE PICK_ORDER = ? AND LINE_NO = ?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, pickOrder);
							pstmt.setString(2, pickLineNo);
							rs = pstmt.executeQuery();
							if( rs.next() )
							{
								pickQty = rs.getInt("QUANTITY");
								
							}
							if(Integer.parseInt(updateQty) > pickQty)
							{
								errList.add( "VTQTYOVR" );
								errFields.add( childNodeName.toLowerCase() );
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;
							
						}*/
						//Changed by rohan on 11/8/12 to check active Replanishement Quantity.end
						
						/*
						//Changed by Rohan on 11/8/12 to do not allow to change pick order in scan is complete.start
						else if(childNodeName.equalsIgnoreCase("pick_order"))
						{
	
							pickOrder = genericUtility.getColumnValue("pick_order", dom);
							waveId = genericUtility.getColumnValue("wave_id", dom2);
							pickType = genericUtility.getColumnValue("pick_type", dom2);
							
							sql = "SELECT STATUS FROM WAVE_TASK_DET WHERE WAVE_ID = ? ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, waveId);
							
							rs = pstmt.executeQuery();
							
							while( rs.next() )
							{
								status = rs.getString("STATUS");
								if("Y".equalsIgnoreCase(status))
								{
									errList.add( "VTSACN" );
									errFields.add( childNodeName.toLowerCase() );
									break;
								}
							}
							
							if(rs != null)
							{	
								rs.close();
								rs = null;
							}
							if(pstmt != null)
							{	
								pstmt.close();
								pstmt = null;
							}
							sqlHold = "SELECT HOLD_STATUS FROM WAVE_TASK WHERE WAVE_ID = ?";
							pstmt = conn.prepareStatement(sqlHold);
							pstmt.setString(1, waveId);
							
							rs = pstmt.executeQuery();
							
							if( rs.next() )
							{
								statusHold = rs.getString("HOLD_STATUS");
							}
							if("Y".equalsIgnoreCase(statusHold))
							{
								errList.add( "VTHOLDTASK" );
								errFields.add( childNodeName.toLowerCase() );
							}
							if(rs != null)
							{	
								rs.close();
								rs = null;
							}
							if(pstmt != null)
							{	
								pstmt.close();
								pstmt = null;
							}
						}
						*/
						//Changed by Rohan on 11/8/12 to do not allow to change pick order in scan is complete.end
					}
				}
			}
			int errListSize = errList.size();
			cnt =0;
			String errFldName = null;
			if ( errList != null && errListSize > 0 )
			{
				for (cnt = 0; cnt < errListSize; cnt++ )
				{
					errCode = (String)errList.get(cnt);
					errFldName = (String)errFields.get(cnt);
					System.out.println("errCode .........."+errCode);
					//String errMsg = hashMap.get(errCode)!=null ? hashMap.get(errCode).toString():"";
					//System.out.println("errMsg .........."+errMsg);
					errString = getErrorString( errFldName, errCode, userId );
					errorType =  errorType( conn , errCode );
					if ( errString.length() > 0)
					{
						String bifurErrString = errString.substring( errString.indexOf("<Errors>") + 8,errString.indexOf("<trace>"));
						bifurErrString =bifurErrString+errString.substring( errString.indexOf("</trace>") + 8,errString.indexOf("</Errors>"));
						errStringXml.append(bifurErrString);
						System.out.println("errStringXml .........."+errStringXml);
						errString = "";
					}
					if ( errorType.equalsIgnoreCase("E"))
					{
						break;
					}
				}
				errList.clear();
				errList = null;
				errFields.clear();
				errFields = null;
				
				errStringXml.append("</Errors></Root>\r\n");
			}
			else
			{
				errStringXml = new StringBuffer( "" );
			}
			errString = errStringXml.toString();
		}//END TRY
		catch(Exception e)
		{
			System.out.println("Exception in PickOrderIC  == >");
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if( conn != null && !conn.isClosed() )
				{
					if( rs != null )
					{
						rs.close();
						rs = null;
					}
					if( pstmt != null )
					{
						pstmt.close();
						pstmt = null;
					}
					conn.close();
				}
			}
			catch(Exception e)
			{
				System.out.println( "Exception :PickOrderIC:wfValData :==>\n"+e.getMessage());
				throw new ITMException(e);
			}
		}
		return errString;
	}
	private String errorType( Connection conn , String errorCode ) throws ITMException
	{
		String msgType = "";
		PreparedStatement pstmt = null ; 
		ResultSet rs = null;
		try
		{			
			String  sql = " SELECT MSG_TYPE FROM MESSAGES WHERE MSG_NO =   ? ";
			
			pstmt = conn.prepareStatement( sql );			
			pstmt.setString(1, errorCode);			
			rs = pstmt.executeQuery();
			while( rs.next() )
			{
				msgType = rs.getString("MSG_TYPE");
			}			
		}
		catch (Exception ex)
		{
			ex.printStackTrace();
			throw new ITMException(ex);
		}		
		finally
		{
			try
			{
				if ( rs != null )
				{
					rs.close();
					rs = null;
				}
				if ( pstmt != null )
				{
					pstmt.close();
					pstmt = null;
				}
			}
			catch ( Exception e )
			{
				e.printStackTrace();
				throw new ITMException(e);
			}
		}		
		return msgType;
	}

	public String itemChanged() throws RemoteException, ITMException
	{
		return "";
	}
	public String itemChanged(String xmlString, String xmlString1, String xmlString2, String objContext, String currentColumn, String editFlag, String xtraParams) throws RemoteException, ITMException
	{
		Document dom = null;
		Document dom1 = null;
		Document dom2 = null;
		String errString = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		try
		{
			if (xmlString != null && xmlString.trim().length()!=0)
			{
				dom = genericUtility.parseString(xmlString); 
			}
			if (xmlString1 != null && xmlString1.trim().length()!=0)
			{
				dom1 = genericUtility.parseString(xmlString1); 
			}
			if (xmlString2 != null && xmlString2.trim().length()!=0)
			{
				dom2 = genericUtility.parseString(xmlString2); 
			}
			errString = itemChanged( dom, dom1, dom2, objContext, currentColumn, editFlag, xtraParams );
			System.out.println ( "ErrString :" + errString);
		}
		catch (Exception e)
		{
			System.out.println ( "Exception :PickOrderIC :itemChanged(String,String):" + e.getMessage() + ":" );
			errString = genericUtility.createErrorString(e);
			throw new ITMException(e);
		}
		System.out.println ( "returning from PickOrderIC itemChanged" );
		return errString;
	}
	public String itemChanged( Document dom, Document dom1, Document dom2, String objContext, String currentColumn, String editFlag, String xtraParams ) throws RemoteException,ITMException
	{
		int currentFormNo = 0;
		int childNodeListLength = 0;
		int ctr = 0;
		String sql = "";
		String columnValue = "";
		String childNodeName = "", siteCode = "",saleOrder ="",lineNoSord = "";
		String descr = "";
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		//Changed by Rohan to define variables.start
		String itemCode = "";
		String quantity = "";
		//Changed by Rohan to define variables.end
		/*
		int caseSize = 0;
		int noArt = 0;
		*/
		double caseSize = 0.0;
		double noArt = 0.0;
		
		//Changed by Rohan to define variables.end
		//Changed by Rohan on 05-03-13 to get quantity in double.start
		double qty = 0.0;
		
		//Changed by Rohan on 05-03-13 to get quantity in double.end
		//Changed by Rohan on 10-03-12 to define variable.start
		String locCodeTo = "";
		HashMap getTranInfo = null;
		String docLocation = "";
		String tranCode = "";
		NodeList parentNodeList2 = null;
		//Chnaged by rohan on 10-03-12 to define variables.end
		StringBuffer valueXmlString = new StringBuffer();
		GenericUtility genericUtility = GenericUtility.getInstance();
		try
		{
			System.out.println("Calling item change*****************");
			columnValue = genericUtility.getColumnValue( currentColumn, dom );
			ConnDriver connDriver = null;
			connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverValidator");
			if( objContext != null && objContext.trim().length() > 0 )
			{
				currentFormNo = Integer.parseInt( objContext );
			}
			valueXmlString = new StringBuffer( "<?xml version=\"1.0\"?>\r\n<Root>\r\n<Header>\r\n<editFlag>" );
			valueXmlString.append( editFlag ).append( "</editFlag>\r\n</Header>\r\n" );
	
			switch ( currentFormNo )
			{
				case 2:
				{
					parentNodeList = dom.getElementsByTagName("Detail2");
					parentNode = parentNodeList.item(0);
					childNodeList = parentNode.getChildNodes();
					valueXmlString.append("<Detail2>");
					childNodeListLength = childNodeList.getLength();
					
					//Changed by Rohan on 10-03-12 to get record form dom2.start
					parentNodeList2 = dom2.getElementsByTagName("Detail2");
					int parentNodeListLength = parentNodeList2.getLength();
					//Changed by Rohan on 10-03-12 to get record form dom2.end
					
					do
					{   
						childNode = childNodeList.item(ctr);
						childNodeName = childNode.getNodeName();
						ctr ++;
					}while(ctr < childNodeListLength  && ! childNodeName.equals(currentColumn));
                    
					if( currentColumn.trim().equalsIgnoreCase( "itm_default" ) )
					{
						columnValue = genericUtility.getColumnValue("pick_order", dom1);
						saleOrder = genericUtility.getColumnValue("sale_order", dom1);
						siteCode = genericUtility.getColumnValue("site_code", dom1);
						valueXmlString.append( "<pick_order><![CDATA[" ).append(columnValue).append( "]]></pick_order>\r\n" );
						valueXmlString.append( "<sale_order><![CDATA[" ).append(saleOrder).append( "]]></sale_order>\r\n" );
						valueXmlString.append( "<site_code><![CDATA[" ).append(siteCode).append( "]]></site_code>\r\n" );
						//Changed by Rohan on 10-03-12 to set loc code to.start	
						for(int row = 0; row < parentNodeListLength; row++)
						{	
							locCodeTo = checkNull(GenericUtility.getInstance().getColumnValueFromNode("loc_code__to",dom2.getElementsByTagName("Detail2").item(row)));
							
							if(!"".equalsIgnoreCase(locCodeTo))
							{
								break;
								
							}
						}	
					
						if(locCodeTo == null || ("".equalsIgnoreCase(locCodeTo) && locCodeTo.length() == 0 ))
						{
							locCodeTo = getDocLoc(columnValue,conn);
						}
						
						valueXmlString.append( "<loc_code__to><![CDATA[" ).append(locCodeTo).append( "]]></loc_code__to>\r\n" );
						//Changed by Rohan on 10-03-12 to set loc code to.end
					}
					else if( currentColumn.trim().equalsIgnoreCase( "item_code" ) )
					{
						columnValue = genericUtility.getColumnValue("item_code", dom);
						sql = "select descr from item where item_code = ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, columnValue);
						rs = pstmt.executeQuery();
						if( rs.next() )
						{
							descr = checkNull(rs.getString(1));
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						valueXmlString.append( "<descr><![CDATA[" ).append(descr).append( "]]></descr>\r\n" );
					}
					//Changed by Rohan on 10/8/12 to change no of art depending on quantity.start
					else if( currentColumn.trim().equalsIgnoreCase( "exp_lev" ) )
					{
						columnValue = genericUtility.getColumnValue("exp_lev", dom);
						saleOrder = genericUtility.getColumnValue("sale_order", dom);
						lineNoSord = genericUtility.getColumnValue("line_no__sord", dom);
						sql = "select lpad(?,3,' ') from dual ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, checkNull(lineNoSord).trim());
						rs = pstmt.executeQuery();
						if( rs.next() )
						{
							lineNoSord = checkNull(rs.getString(1));
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						
						sql = "select item_code from sorditem where sale_order = ? and line_no = ? and exp_lev = ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, saleOrder);
						pstmt.setString(2, lineNoSord);
						pstmt.setString(3, columnValue);
						rs = pstmt.executeQuery();
						if( rs.next() )
						{
							itemCode = checkNull(rs.getString(1));
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						valueXmlString.append( "<item_code><![CDATA[" ).append(itemCode).append( "]]></item_code>\r\n" );
					}
					else if( currentColumn.trim().equalsIgnoreCase("quantity" ) )
					{
						quantity = checkNull(genericUtility.getColumnValue("quantity", dom));
						itemCode = checkNull(genericUtility.getColumnValue("item_code", dom));
						
						caseSize = getShipperSize(itemCode,conn);
						//Changed by Rohan on 05-03-12 to get quantity in double
						/*
						if(Integer.valueOf(quantity) > 0)
						{	
							noArt = Integer.valueOf(quantity)/caseSize;
						}	
						*/
						if(Double.parseDouble(quantity) > 0)
						{	
							noArt = Double.parseDouble(quantity)/caseSize;
						}	
						if(noArt < 1.0)
						{
							noArt = 1.0;
						}
						
						System.out.print("No of Art"+noArt);

						valueXmlString.append( "<no_art><![CDATA[" ).append((String.valueOf(noArt))).append( "]]></no_art>\r\n" );
						
					}
					//Changed by Rohan on 10/8/12 to change no of art depending on quantity.end
					
					//Changed by Rohan on 10/8/12 to change no of art depending on quantity.start
					else if( currentColumn.trim().equalsIgnoreCase("no_art" ) )
					{
						String noArtStr = "";
						//Changed by Rohan on 05-03-12 to get quantity in double
						//int qty = 0;
						qty = 0.0;
						
						noArtStr = checkNull(genericUtility.getColumnValue("no_art", dom));
						itemCode = checkNull(genericUtility.getColumnValue("item_code", dom));
						
						caseSize = getShipperSize(itemCode,conn);
						//Changed by Rohan on 05-03-12 to get quantity in double
						//qty = Integer.parseInt(noArtStr) * caseSize;
						qty = Double.parseDouble(noArtStr) * caseSize;
						
						
						System.out.print("Quantity"+qty);
                       
						valueXmlString.append( "<quantity><![CDATA[" ).append(String.valueOf(qty)).append( "]]></quantity>\r\n" );
						
					}
					//Changed by Rohan on 10/8/12 to change no of art depending on quantity.end
					valueXmlString.append( "</Detail2>\r\n" );
					System.out.println("------------------------COMPLETE DESCRIPTION-------------------");
					break;
				}
			}
		}
		catch(Exception e)
		{
			System.out.println( "Exception :PickOrder :itemChanged(Document,String):" + e.getMessage() + ":" );
			valueXmlString = valueXmlString.append( genericUtility.createErrorString( e ) );
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if( conn != null && ! conn.isClosed() )
				{
					if( rs != null )
					{
						rs.close();
						rs = null;
					}
					if( pstmt != null )
					{
						pstmt.close();
						pstmt = null;
					}
					
					conn.close();
				}
				//System.out.println("------------------------INSIDE FINALLY-------------------");
			}
			catch(Exception e)
			{
				System.out.println( "Exception :PickOrder:itemChanged :==>\n"+e.getMessage());
				throw new ITMException(e);
			}
		}
		valueXmlString.append( "</Root>\r\n" );	
		System.out.println( "\n****ValueXmlString :" + valueXmlString.toString() + ":********" );
		return valueXmlString.toString();
	}
	
	private String checkNull( String input )
	{
		if ( input == null )
		{
			input = "";
		}
		return input;
	}
	private String getCurrentUpdateFlag(Node currDetail)
    {
        NodeList currDetailList = null;
        String updateStatus = "",nodeName = "";
        int currDetailListLength = 0;

        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;
    }
	//Changed by Rohan on 10/08/12 to Check shipper size of an item.start
	public double getShipperSize(String itemCode,Connection conn) throws Exception
	{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
	    String sql = "";
	    //Changed by Rohan on 05-03-12 to get quantity in double
		//int caseSize = 0;
	    double caseSize = 0;
		try
		{
			sql = "SELECT SHIPPER_SIZE FROM ITEM_LOT_PACKSIZE WHERE ITEM_CODE = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, itemCode);
			rs = pstmt.executeQuery();
			if( rs.next() )
			{
				caseSize = rs.getInt("SHIPPER_SIZE");
			}
			
        }
		catch(Exception e)
		{
			throw e;
		}
		finally
		{
			if(rs != null)
			{
				rs.close();
				rs = null;
			}
			if(pstmt != null)
			{
				pstmt.close();
				pstmt = null;
			}
			
		}
		return caseSize;
	}
	//Changed by Rohan on 10/08/12 to Check shipper size of an item.end
	//Changed by Rohan to 10/08/12 to return stock Loc code in case of Repalnishement
	public String getStockLocCode(String pickOrder,String lineNo,String locCode,String itemCode,String siteCode,String lotNo,String lotSl,Connection conn) throws Exception
	{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
	    String sqlPick = "";
	    String replOrd = "";
	    String lineNorepl = "";
	    String sqlRepl = "";
	    String invStat = "";
	    String casePickLoc = "";
	    String resrvLoc = "";
	    String pndInvStat = "";
	    String sqlReplCase = "";
		int caseSize = 0;
		
		try
		{
			casePickLoc  = discommon.getDisparams("999999","CASE_PICK_INVSTAT",conn);
			resrvLoc  = discommon.getDisparams("999999","RESERV_LOCATION",conn);
			pndInvStat   = discommon.getDisparams("999999","PND_INVSTAT",conn);
			
			sqlPick = "SELECT REPL_ORDER,LINE_NO__REPL " 
					 +"FROM PICK_ORD_DET WHERE PICK_ORDER = ? AND LINE_NO = ?";
		
			pstmt = conn.prepareStatement(sqlPick);
			
			pstmt.setString(1,pickOrder);
			pstmt.setString(2,lineNo);
			
			rs = pstmt.executeQuery();
			
			
			if (rs.next())
			{
				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())
			{
				System.out.println("Loc COde Returing "+rs.getString("LOC_CODE"));
				locCode = rs.getString("LOC_CODE");
				invStat = rs.getString("INV_STAT");
				invStat = invStat.trim();
				//return locCode;
			}
			
			pstmt.close();
			pstmt = null;
			
			
			rs.close();
			rs = null;
			

			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);
			
			if(invStat.equalsIgnoreCase(casePickLoc.trim()))
			{
				
				System.out.println("Enter in active Repalinshement CASE PICK PSO");
				/*
				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);*/
				
				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");
					invStat = invStat.trim();
					System.out.println("LOC CODE PSO"+locCode);
					
				}
				
				if(rs != null)
				{	
					rs.close();
					rs = null;
				}
					
				
			}
			if(invStat.equalsIgnoreCase(pndInvStat.trim()))
			{
				
				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;
				}	
			}
        }
		catch(Exception e)
		{
			throw e;
		}
		finally
		{
			if(rs != null)
			{
				rs.close();
				rs = null;
			}
			if(pstmt != null)
			{
				pstmt.close();
				pstmt = null;
			}
			
		}
		return locCode;
	}
	
	
	
	//Changed by Rohan on 10-03-12 to get sorder info.start
	private String getDocLoc(String pickOrder,Connection conn) throws ITMException
	{
		String sql = "",docLoc = "";
		ResultSet rs = null;

		PreparedStatement pstmt = null;
		
		String custCode = "",stanCode = "",siteCodeShip = "",stateCodeDlv = "";
		
		try
		{
									
			//changed by sankara on 22/09/14 for site transporter start.
			/*sql = "SELECT T.LOC_CODE__DOCK FROM WAVE_TASK_DET W,DESPATCH D,TRANSPORTER T WHERE" 
				+" W.PTCN = (SELECT PTCN FROM WAVE_TASK_DET WHERE REF_ID =?)" 
				+" AND W.REF_SER='S-DSP'"
				+" AND D.DESP_ID = W.REF_ID"
				+" AND T.TRAN_CODE = D.TRAN_CODE";

			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,pickOrder);
			rs = pstmt.executeQuery();

			if(rs.next())
			{
				docLoc = rs.getString(1);			
			}*/

			sql = " SELECT S.SITE_CODE__SHIP FROM SORDER S, PICK_ORD_HDR P WHERE P.SALE_ORDER = S.SALE_ORDER " +
					" AND P.PICK_ORDER = ? ";
			pstmt = conn.prepareStatement( sql );
			pstmt.setString( 1, pickOrder );
			rs = pstmt.executeQuery();
			if ( rs.next() )
			{
				siteCodeShip = checkNull(rs.getString(1));
				System.out.println("siteCodeShip::::::"+siteCodeShip);
			}
			rs.close();rs = null;
			pstmt.close();pstmt = null;

			sql = "SELECT ST.LOC_CODE__DOCK FROM WAVE_TASK_DET W,DESPATCH D,SITE_TRANSPORTER ST WHERE" 
					+" W.PTCN = (SELECT PTCN FROM WAVE_TASK_DET WHERE REF_ID = ?)" 
					+" AND W.REF_SER='S-DSP'"
					+" AND D.DESP_ID = W.REF_ID"
					+" AND ST.TRAN_CODE = D.TRAN_CODE"
					+" AND ST.SITE_CODE = ? ";

			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,pickOrder);
			pstmt.setString(2,siteCodeShip);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				docLoc = rs.getString(1);		
				System.out.println("docLoc:::::::"+docLoc);
			}
			else
			{
				sql = "SELECT T.LOC_CODE__DOCK FROM WAVE_TASK_DET W,DESPATCH D,TRANSPORTER T WHERE" 
						+" W.PTCN = (SELECT PTCN FROM WAVE_TASK_DET WHERE REF_ID =?)" 
						+" AND W.REF_SER='S-DSP'"
						+" AND D.DESP_ID = W.REF_ID"
						+" AND T.TRAN_CODE = D.TRAN_CODE";	
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,pickOrder);
				rs = pstmt.executeQuery();				
				if(rs.next())
				{
					docLoc = rs.getString(1);		
				}
			}

			if( rs != null )
			{
				rs.close();
				rs = null;
			}
			if( pstmt != null )
			{
				pstmt.close();
				pstmt = null;
			}
			//changed by sankara on 22/09/14 for site transporter end.
		}
		catch(Exception e)
		{
			System.out.println("Exception in  == >"+e);
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if( rs != null )
				{
					rs.close();
					rs = null;
				}
				if( pstmt != null )
				{
					pstmt.close();
					pstmt = null;
				}
			}
			catch (Exception e1)
			{
			}
		}
	 return docLoc;	
	}
	//Changed by Rohan on 10-03-12 to get sorder info.end
	//Changed by Rohan on 10-03-12 to get stan code info.start
	private HashMap getStanCodeInfo(String siteCodeShip,Connection conn) throws ITMException
	{
		String sql = "";
		String sqlShipStanCode = "";
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		String strShipStanCode = "",strStateCodeShip = "";
		HashMap stationMap = new HashMap();
		try
		{
										
			
			sql = "SELECT A.STAN_CODE AS STAN_CODE,A.STATE_CODE AS STATE_CODE  FROM SITE A,STATION B "
				+ " WHERE A.STAN_CODE = B.STAN_CODE "
				+ " AND A.SITE_CODE = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, siteCodeShip);
			rs = pstmt.executeQuery();

			while (rs.next())
			{
				strShipStanCode  = rs.getString("STAN_CODE");
				strStateCodeShip = rs.getString("STATE_CODE");
			}
			
			if( rs != null )
			{
				rs.close();
				rs = null;
			}
			if( pstmt != null )
			{
				pstmt.close();
				pstmt = null;
			}
			stationMap.put("STAN_CODE",strShipStanCode);
			stationMap.put("STATE_CODE",strStateCodeShip);
			
		
		}
		catch(Exception e)
		{
			System.out.println("Exception in  == >"+e);
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if( rs != null )
				{
					rs.close();
					rs = null;
				}
				if( pstmt != null )
				{
					pstmt.close();
					pstmt = null;
				}
			}
			catch (Exception e1)
			{
			}
		}
	 return stationMap;	
	}
	//Changed by Rohan on 10-03-12 to get stan code info.end
	
	//Changed by Rohan on 10-03-12 to get pin site.start
	private String getPinSite(String siteCode,Connection conn) throws ITMException  //method add by Kunal Mandhre on 04/07/12 for get pin as per S Manoharan instruction 
	{
		String sql = "";
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		String pin = "";
		try
		{


			sql = "select pin from site where site_code = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,siteCode);
			rs = pstmt.executeQuery();

			while (rs.next())
			{
				pin = rs.getString("pin") == null ?"":rs.getString("pin");
			}
			
			if(rs != null)
			{
				rs.close();
				rs = null;
			}
			if(pstmt != null)
			{
				pstmt.close();
				pstmt = null;
			}

		}
		catch(Exception e)
		{
			System.out.println("Exception in  == >"+e);
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if(rs != null)
				{
					rs.close();
					rs = null;
				}
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
			}
			catch(Exception e)
			{
				e.printStackTrace();
				throw new ITMException(e);
			}
		}
		return pin;	
	}
	//Changed by Rohan on 10-03-12 to get pin site.end
	//Changed by Rohan on 10-03-12 to get pin.start
	private String getPinCust(String custCode,Connection conn) throws ITMException 
	{
		String sql = "";
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		String pin = "";
		try
		{


			sql = "select pin from customer where cust_code = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,custCode);
			rs = pstmt.executeQuery();

			while (rs.next())
			{
				pin = rs.getString("pin") == null ?"":rs.getString("pin");
			}
			if(rs != null)
			{
				rs.close();
				rs = null;
			}
			if(pstmt != null)
			{
				pstmt.close();
				pstmt = null;
			}

		}
		catch(Exception e)
		{
			System.out.println("Exception in  == >"+e);
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if(rs != null)
				{
					rs.close();
					rs = null;
				}
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
			}
			catch(Exception e)
			{
				throw new ITMException(e);
			}
		}
		return pin;	
	}
	//Changed by Rohan on 10-03-12 to get pin.end

	
}